SQL server 数据查询

本章内容

6.1 基本查询

6.2 嵌套查询

6.3 连接查询

6.1 基本查询

SQL数据查询语句是SELECT语句。该语句的基本框架是SELECT-FROM-WHERE，它包含输出字段、数据来源和查询条件等基本子句。在这种固定格式中，可以不要WHERE，但是SELECT和FROM是必备的。SELECT语句的子句很多，理解了这条语句各项的含义，就能从数据库中查询出各种数据。

6.1 基本查询

简单查询

语法格式：

SELECT [ALL|DISTINCT]

[TOP n [PERCENT]] select_list FROM

table_name

(1) ALL：表示输出所有记录，包括重复记录。

(2)select_list：所要查询的选项的集合，多个选项之间用逗号分开。

(3)table_name：要查询的表。

6.1 基本查询

例6-1 分别显示Sales数据库中的员工表employee、商品表goods、销售表sell_order表和部门表department中的所有记录。

SELECT * FROM employee

SELECT * FROM goods

SELECT * FROM sell_order

SELECT * FROM department

6.1 基本查询

例6-2 显示employee表中全部员工的姓名和年龄，去掉重名。

SELECT DISTINCT employee_name AS 姓名，YEAR(GETDATE())-YEAR(birth_date) AS 年龄

FROM employee

6.1 基本查询

例6-3 对employee表，分别查询公司的员工总数和公司员工的平均收入。

SELECT COUNT(*) AS 总数 FROM employee

SELECT AVG(wages) AS 平均收入 FROM employee

6.1 基本查询

带条件查询

语法格式：

WHERE search_condition

例6-4 对employee表，列出月工资在2000以上的员工记录。

SELECT * FROM employee WHERE wages>2000

6.1 基本查询

例6-5 对employee表，求出男员工的平均工资。

SELECT AVG(wages) as 平均工资 FROM employee WHERE sex='男„

例6-6 对employee表，列出市场部和销售部的员工名单。

SELECT ment_name, ee_name

FROM employee e INNER JOIN department d ON ment_id = ment_id

WHERE ment_name IN ('市场部', '销售部')

语句中的WHERE子句还有等价的形式：

WHERE (ment_name = '市场部') OR (ment_name = '销售部')

6.1 基本查询

例6-7 对employee表，列出月工资在2000到3000之间的员工名单。

SELECT * FROM employee WHERE wages BETWEEN 2000 AND 3000

语句中的WHERE子句还有等价的形式：

WHERE wages>=2000 AND wages<=3000

6.1 基本查询

例6-8 对employee表，列出所有的姓“张”的员工名单。

SELECT * FROM employee WHERE employee_name LIKE '张%'

语句中的WHERE子句还有等价的形式：

WHERE LEFT(employee_name,1)= '张„

例6-9 对employee表，列出所有工资为空值的员工编号和姓名。

SELECT employee_id,employee_name FROM employee WHERE wages IS NULL

6.1 基本查询

查询结果处理

1. 排序输出(ORDER BY)

语法格式：

ORDER BY order_by_expression1[ASC|DESC]

[,order_by_expression2[ASC|DESC]] [,…]]

6.1 基本查询

例6-10 对employee表，按性别顺序列出员工的编号、姓名、性别、部门编号及工资，性别相同的再先按部门后按工资由高到低排序。

SELECT employee_id,employee_name,sex,department_id,wages FROM employee

ORDER BY sex,department_id,wages DESC

6.1 基本查询

2. 重定向输出(INTO)

语法格式：

INTO new_table

例6-11 对部门表department和员工表employee,查询出“市场部”所有员工的信息，并将结果存入testtable表中。

SELECT employee.* INTO testtable

FROM employee INNER JOIN department

ON ment_id = ment_id

WHERE ment_name = '市场部'

6.1 基本查询

3. 输出合并(UNION)

语法格式：

[UNION [ALL] ]

合并的规则是：

(1)不能合并子查询的结果。

(2)两个SELECT语句必须输出同样的列数。

(3)两个表各相应列的数据类型必须相同，数字和字符不能合并。

(4)仅最后一个SELECT语句中可以用ORDER BY子句，且排序选项必须依据第一个SELECT列表中的列。

6.1 基本查询

例6-12 对employee表，列出部门编号为“D001”或“D002”的所有员工姓名。

SELECT employee_name,department_id FROM employee WHERE

department_id='D001'

UNION

SELECT employee_name,department_id FROM employee WHERE department_id='D002'

6.1 基本查询

4. 分组统计(GROUP BY)与筛选(HAVING)

语法格式：

GROUP BY group_by_expression1 [,group_by_expression2][,…]

例6-13 对employee表，分别统计男女员工人数。

SELECT sex,COUNT(sex) as 人数 FROM employee GROUP BY sex

6.1 基本查询

例6-14 对employee表，分别统计各部门男女员工的人数。

SELECT department_id, sex,COUNT(*) as 人数 FROM employee

GROUP BY department_id,sex

例6-15 对employee表，列出部门平均工资大于2000的部门编号。

SELECT department_id,AVG(wages) AS 平均工资 FROM employee

GROUP BY department_id HAVING AVG(wages)>=2000

6.1 基本查询

5. 使用COMPUTE和COMPUTE BY子句汇总

语法格式：

COMPUTE row_aggregate(column_name)[,row_aggregate(colornn_name)...]

[BY column_name[, column_name. . . ]]

例6-16 对employee表中部门编号为“D001”的员工工资，按照其部门编号生成汇总行和明细行。

SELECT department_id, wages FROM employee

WHERE department_id = 'D001' ORDER BY department_id COMPUTE sum(wages)

6.1 基本查询

例6-17 对employee表中部门编号为“D001”或“D002”的员工工资，按照其部门编号生成分组汇总行和明细行。

SELECT department_id, wages FROM employee

WHERE department_id = 'D001' OR department_id = 'D002'

ORDER BY department_id

COMPUTE sum(wages) BY department_id

6.2 嵌套查询

SQL Server允许多层嵌套查询。嵌套查询一般的查询方法是由里向外进行处理，即每个子查询在上一级查询处理之前处理，子查询的结果用于建立其父查询的查找条件。子查询中所存取的表可以是父查询没有存取的表，子查询选出的记录不显示。

6.2 嵌套查询

单值嵌套查询

1. 返回单值的子查询

子查询的返回结果是一个值的嵌套查询称为单值嵌套查询。

例6-18 对Sales数据库，列出“市场部”的所有员工的编号。

SELECT employee_id FROM employee

WHERE department_id=(SELECT department_id

FROM department

WHERE department_name='市场部')

6.2 嵌套查询

多值嵌套查询

子查询的返回结果是一列值的嵌套查询称为多值嵌套查询。

(1)ANY运算符的用法

例6-19 对Sales数据库，列出D001号部门中工资比D002号部门的员工最低工资高的员工编号和工资。

SELECT employee_id,wages FROM employee

WHERE department_id='D001' AND wages>ANY

(SELECT wages FROM employee

WHERE department_id='D002')

6.2 嵌套查询

(2)ALL运算符的用法

例6-20 对Sales数据库，列出部门编号为“D001”的员工，这些员工的工资比部门为“D002”的员工的最高工资还要高的员工的编号和工资。

SELECT employee_id,wages FROM employee

WHERE department_id='D001' AND wages>ALL

(SELECT wages FROM employee

WHERE department_id='D002')

6.2 嵌套查询

(3)IN运算符的用法

例6-21 对Sales数据库，列出部门为“市场部”或“销售部”的所有员工的编号。

SELECT employee_id FROM employee

WHERE department_id IN

(SELECT department_id FROM department

WHERE department_name='市场部' OR department_name='销售部')

6.3 连接查询

连接概述

通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其他类型数据库管理系统的一个标志。

连接可以在SELECT语句的WHERE子句中建立。

6.3 连接查询

例6-22 对Sales数据库输出所有员工的销售单，要求给出员工编号、姓名、商品编号、商品名和销售数量。

SELECT ee_id, ee_name, _id,

_name, sell__num

FROM employee,sell_order,goods

WHERE ee_id = sell_ee_id and

sell__id = _id

6.3 连接查询

连接也可以在FROM子句中建立，而且在FROM子句中指出连接时有助于将连接操作与WHERE子句中的搜索条件区分开来。所以，在Transact-SQL中推荐使用这种方法。

FROM子句建立连接的语法格式是：

FROM join_table [join_type] JOIN join_table ON join_condition

6.3 连接查询

内连接

内连接分3种：等值连接、不等值连接和自然连接。

1．等值连接

在连接条件中使用等号(=)运算符比较被连接列的列值，按对应列的共同值将一个表中的记录与另一个表中的记录相连接，包括其中的重复列。

例6-23 Sales数据库中部门表department和员工表employee的等值连接。

SELECT * FROM department INNER JOIN

employee ON ment_id =

ment_id

6.3 连接查询

2．不等值连接

在连接条件中使用除等于(=)运算符以外的其他比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。

例6-24 对Sales数据库，列出销售“G00001”产品的员工中，销售数量大于编号为“E001”的员工销售该类产品销售数量的那些员工的编号和销售数量。

SELECT ee_id, _num

FROM sell_order a INNER JOIN

sell_order b ON _num > _num AND _id = _id

WHERE (_id = 'G00001') AND (ee_id = 'E001')

6.3 连接查询

3．自然连接

在连接条件中使用等于(=)运算符比较被连接列的列值，它使用选择列表方式来指出查询结果集合中所包括的列，并删除连接表中的重复列。

例6-25 Sales数据库中部门表department和员工表employee的自然连接。

SELECT ment_name, b.*

FROM department a INNER JOIN

employee b ON ment_id = ment_id

6.3 连接查询

外连接

在内连接查询时，返回查询结果集合中的仅是符合查询条件(WHERE搜索条件或 HAVING条件)和连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个连接表(全外连接)中的所有数据行。

6.3 连接查询

1．使用左外连接

左外连接通过左向外连接引用左表的所有行。

例6-26 员工信息表employee左外连接销售信息表sell_order。

SELECT ee_id, ee_name, _id, _num, _date

FROM employee a LEFT OUTER JOIN

sell_order b ON ee_id = ee_id

6.3 连接查询

2．使用右外连接

右外连接通过右向外连接引用右表的所有行。

例6-27 员工信息表employee右外连接销售信息表sell_order。

为了说明方便，先在sell_order表中插入一条销售信息。

INSERT INTO sell_order (order_id1, goods_id, employee_id, customer_id, transporter_id,

order_num, discount, order_date, send_date, arrival_date, cost)

VALUES ('S00006', 'G00005', ' ', 'C0006', 'T002', 21, 0.5,

GETDATE(), GETDATE(), GETDATE(),100)

SELECT ee_id, ee_name, _id, _num,

_date, _id1

FROM employee a RIGHT OUTER JOIN

sell_order b ON ee_id = ee_id

6.3 连接查询

3．使用全外连接

全外连接返回两个表的所有行。不管两个表的行是否满足连接条件，均返回查询结果集。对不满足连接条件的记录，另一个表相对应字段用NULL代替。

例6-28 员工信息表employee全外连接销售信息表sell_order。

SELECT ee_id, ee_name, _id, _num, _date,

_id1

FROM employee a FULL OUTER JOIN

sell_order b ON ee_id = ee_id

6.3 连接查询

交叉连接

交叉连接不带WHERE子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。例如，部门信息表department中有4个部门，而员工信息表employee中有7名员工，则下列交叉连接检索到的记录数将等于4×7=28行。

SELECT ment_id, ment_name, ee_id, ee_name

FROM department a CROSS JOIN

employee b

本章小结

(1)SQL数据查询语句基本结构：基本框架为SELECT-FROM-WHERE，它包含输出字段、数据来源、查询条件等基本子句。

(2)带条件查询：WHERE子名中指定的查询条件可以是单表的条件表达式，也可以是多表之间的条件表达式。

(3)查询结果处理：使用SELECT语句完成查询工作后，所查询的结果默认显示在屏幕上，若需要对这些查询结果进行处理，则需要SELECT的其他子句配合操作。

(4)嵌套查询：在一个SELECT语句的WHERE子句中出现另一个SELECT语句，这种查询称为嵌套查询。

(5)连接查询：连接分为内连接、外连接和交叉连接。内连接分为等值连接、非等值连接和自然连接。外连接又分为左外连接、右外连接和全外连接。