第6章 查询技术
SELECT 主要子句
SELECT [ ALL | DISTINCT ]
[TOP expression [PERCENT] [WITH TIES ]]
< select_list >
[ INTO new_table ]
[ FROM { <table_source> } [ ,...n ] ]
[ WHERE <search_condition> ]
[ GROUP BY [ ALL ] group_by_expression [ ,...n ]
[ WITH { CUBE | ROLLUP } ]
[ HAVING < search_condition > ]
[ORDER BY order_expression [ASC|DESC]]
[ COMPUTE {{AVG|COUNT|MAX|MIN|SUM} (expression)} [ ,...n ]
[ BY expression [ ,...n ] ]
SELECT 主要子句
参数说明如下：
• SELECT子句用于指定所选择的要查询的特定表中的列，它可以是星号（*）、表达式、列表、变量等。
• INTO子句用于指定所要生成的新表的名称。
• FROM子句用于指定要查询的表或者视图，最多可以指定16个表或者视图，用逗号相互隔开。
• WHERE子句用来限定查询的范围和条件。
• GROUP BY子句是分组查询子句。
• HAVING子句用于指定分组子句的条件。
• GROUP BY子句、HAVING子句和集合函数一起可以实现对每个组生成一行和一个汇总值。
• ORDER BY子句可以根据一个列或者多个列来排序查询结果，在该子句中，既可以使用列名，也可以使用相对列号。
• ASC表示升序排列，DESC表示降序排列。
• COMPUTE子句使用集合函数在查询的结果集中生成汇总行。
• COMPUTE BY子句用于增加各列汇总行。
6.1 基本SELECT语句
6.1.1 投影查询
6.1.2 条件查询
6.1.1 投影查询
• 最基本的 SELECT 语句仅有两个部分：要返回的列，和这些列源于的表。也就是说查询均为不使用WHERE子句的无条件查询，也称作投影查询。
• 例6-1 查询全体学生的学号、姓名和年龄。
程序清单如下：
SELECT SNO, SN, AGE FROM S
• 例6-2 查询学生的全部信息。
程序清单如下：
SELECT * FROM S
• 注意：用‘ * ’表示表的全部列名，而不必逐一列出。
6.1.1 投影查询
• 例6-3 查询选修了课程的学生号。
程序清单如下：
SELECT DISTINCT SNO FROM SC
注意：应用DISTINCT消除查询结果以某列为依据的重复行。上例中，sc表中相同学号（SNO）的纪录只保留第一行，余下的具有相同学号的记录将从查询结果中清除。也就是每个同学保留一条选课纪录。
• 另外，利用投影查询可控制列名的顺序，并可通过指定别名改变查询结果的列标题的名字，如下例。
• 例6-4 查询全体学生的姓名、学号和年龄。
程序清单如下：
SELECT SN NAME, SNO, AGE FROM S
• 注意：NAME为SN的别名，这里我们改变了列的显示顺序。
6.1.2 条件查询
• 当要在表中找出满足某些条件的行时，则需使用WHERE子句指定查询条件。WHERE子句中，条件通常通过三部分来描述：列名；比较运算符；列名、常数。
• 条件查询又可分为以下几方面内容：
1、比较大小和确定范围；
2、部分匹配查询；
3、空值查询；
4、查询的排序
6.1.2 条件查询
1、比较大小和确定范围
• 例6-5 查询选修课程号为‘C1’的学生的学号和成绩。
程序清单如下：
SELECT SNO,SCORE FROM SC WHERE CNO=’C1’
• 例6-6 查询成绩高于85分的学生的学号、课程号和成绩。
程序清单如下：
SELECT SNO,CNO,SCORE FROM SC WHERE SCORE>85
6.1.2 条件查询
1、比较大小和确定范围
• 当WHERE子句需要指定一个以上的查询条件时，则需要使用逻辑运算符AND、OR和NOT将其连结成复合的逻辑表达式。其优先级由高到低为：NOT、AND、OR，用户可以使用括号改变优先级。
• 例6-7 查询选修C1或C2且分数大于等于85分学生的的学号、课程号和成绩。
程序清单如下：
SELECT SNO，CNO，SCORE FROM SC
WHERE（CNO=’C1’ OR CNO=’C2’） AND SCORE>=85
SQL语句中也有一个特殊的 BETWEEN 运算符，用于检查某个值是否在两个值之间（包括等于两端的值）。
• 例6-8 查询工资在1000至1500之间的教师的教师号、姓名及职称。
程序清单如下：
SELECT TNO,TN,PROF FROM T
WHERE SAL BETWEEN 1000 AND 1500
• 上面SQL语句等价于以下语句：
SELECT TNO,TN,PROF FROM T
WHERE SAL>=1000 AND SAL<=1500
6.1.2 条件查询
1、比较大小和确定范围
• 注意：在SELECT语句中可利用“IN”操作来查询属性值属于指定集合的元组。利用“NOT IN”可以查询指定集合外的元组。如下面两个例子。
• 例6-10 查询选修C1或C2的学生的学号、课程号和成绩。
程序清单如下：
SELECT SNO, CNO, SCORE
FROM SC
WHERE CNO IN(‘C1’, ‘C2’)
• 此语句也可以使用逻辑运算符“OR”实现。相应的程序清单如下：
SELECT SNO, CNO, SCORE
FROM SC
WHERE CNO=‘C1’ OR CNO= ‘C2’
6.1.2 条件查询
2、部分匹配查询
• 当不知道完全精确的値时，用户还可以使用LIKE或NOT LIKE进行部分匹配查询（也称模糊查询）。LIKE运算使我们可以使用通配符来执行基本的模式匹配。
• 使用LIKE运算符的一般格式为：
<属性名> LIKE <字符串常量>
字符串常量的字符可以包含如表6-2所示的通配符。
6.1.2 条件查询
2、部分匹配查询
• 例6-12 查询所有姓张的教师的教师号和姓名。
程序清单如下：
SELECT TNO, TN
FROM T
WHERE TN LIKE ‘张%’
• 例6-13 查询姓名中第二个汉字是“力”的教师号和姓名。
程序清单如下：
SELECT TNO, TN
FROM T
WHERE TN LIKE ‘_力%’
6.1.2 条件查询
3、空值查询
• 某个字段没有值称之为具有空值（NULL）。通常没有为一个列输入值时，该列的值就是空值。空值不同于零和空格，它不占任何存储空间。例如，某些学生选课后没有参加考试，有选课记录，但没有考试成绩，考试成绩为空值，这与参加考试，成绩为零分的不同。
• 例6-15 查询没有考试成绩的学生的学号和相应的课程号。
程序清单如下：
SELECT SNO, CNO
FROM SC
WHERE SCORE IS NULL
• 注意：这里的空值条件为IS NULL，不能写成SCORE=NULL。
6.1.2 条件查询
4．查询的排序
• 当需要对查询结果排序时，应该在SELECT语句中使用ORDER BY子句。ORDER BY 子句包括了一个或多个用于指定排序顺序的列名，排序方式可以指定，DESC为降序，ASC为升序，缺省时为升序。ORDER BY子句必须出现在其他子句之后。
• ORDER BY 子句支持使用多列。可以使用以逗号分隔的多个列作为排序依据：查询结果将先按指定的第一列进行排序，然后再按指定的下一列进行排序。
• 例6-16 查询选修C1 的学生学号和成绩，并按成绩降序排列。
程序清单如下：
SELECT SNO, SCORE
FROM SC
WHERE CNO='C1'
ORDER BY SCORE DESC
6.2 分组查询
6.2.1 聚合函数和GROUP BY子句
6.2.2 GROUP BY 和 WHERE 子句、HAVING 子句
6.2.1 聚合函数和GROUP BY子句
GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的聚合值。如果聚合函数没有使用 GROUP BY 子句，则只为 SELECT 语句报告一个聚合值。常用的聚合函数，如表6-3所示。
6.2.1 聚合函数和GROUP BY子句
•例6-18 通过查询求学号为S1学生的总分和平均分。
程序清单如下：
SELECT SUM(SCORE) AS TotalScore, AVG(SCORE) AS AveScore
FROM SC
WHERE SNO = 'S1'
•注意：函数SUM和AVG只能对数值型字段进行计算。
•例6-19 通过查询求选修C1号课程的最高分、最低分及之间相差的分数
程序清单如下：
SELECT MAX(SCORE) AS MaxScore, MIN(SCORE) AS MinScore,
MAX(SCORE)- MIN(SCORE) AS Diff
FROM SC
WHERE (CNO = 'C1')
6.2.1 聚合函数和GROUP BY子句
•例6-20 通过查询求管理系学生的总数。
程序清单如下：
SELECT COUNT(SNO) FROM S
WHERE DEPT='管理'
•例6-21 通过查询求学校中共有多少个系。
程序清单如下：
SELECT COUNT(DISTINCT DEPT) AS DeptNum
FROM S
•注意：加入关键字DISTINCT后表示消去重复行，可计算字段“DEPT“不同值的数目。COUNT函数对空值不计算，但对零进行计算。
•例6-22 统计有成绩的学生的人数。
程序清单如下：
SELECT COUNT (SCORE)
FROM SC
•注意：上例中成绩为零的同学计算在内，没有成绩（即为空值）的不计算。
6.2.1 聚合函数和GROUP BY子句
•例6-23 利用特殊函数COUNT(*)求计算机系学生的总数
程序清单如下：
SELECT COUNT(*) FROM S WHERE DEPT=‘计算机’
•注意：上例中，COUNT（*）用来统计元组的个数。此函数不消除重复行，也不允许使用DISTINCT关键字。
•在分组查询中，只要表达式中不包括聚合函数，就可以按该表达式分组。如下例所示。
•例6-24 查询每位学生的学号及其选课的门数。
程序清单如下：
SELECT CNO,COUNT(*) AS C_NUM FROM SC GROUP BY CNO
• GROUP BY子句按CNO的值分组，所有具有相同CNO的元组为一组，对每一组使用函数COUNT进行计算，统计出各位学生选课的门数。
•例6-25统计各年度出生的雇员人数
程序清单如下。
Use adventureworks
SELECT DATEPART(year, birthdate ) AS Year,
COUNT(*) AS NumberOfemployees
FROM humanresources.employee
GROUP BY DATEPART(year, birthdate)
6.2.2 GROUP BY 和 WHERE 子句、HAVING 子句
• 可以在包含 GROUP BY 子句的查询中使用 WHERE 子句。在完成任何分组之前，将消除不符合 WHERE 子句中的条件的行。若在分组后还要按照一定的条件进行筛选，则需使用HAVING子句。
• 例6-26 在分组查询中使用WHERE条件，查询计算机系的学生学号及平均成绩。
程序清单如下：
SELECT sno, AVG(score) AS 'AverageScore'
FROM sc
WHERE sno=(select sno from s where dept=’计算机’)
GROUP BY sno
ORDER BY sno
GO
6.2.2 GROUP BY 和 WHERE 子句、HAVING 子句
• 例6-27 在分组查询中使用HAVING条件，查询平均成绩大于85的学生学号及平均成绩。
程序清单如下：
SELECT sno, AVG(score) AS 'AverageScore'
FROM sc
GROUP BY sno
HAVING AVG(score) >85
GO
• 注意：如果 HAVING 中包含多个条件，那么这些条件将通过 AND、OR 或 NOT 组合在一起
6.2.2 GROUP BY 和 WHERE 子句、HAVING 子句
•例6-28 查询选课在三门以上且各门课程均及格的学生的学号及其总成绩，查询结果按总成绩降序列出。
程序清单如下：
SELECT SNO,SUM(SCORE) AS TotalScore
FROM SC
WHERE SCORE>=60
GROUP BY SNO
HAVING COUNT(*)>=3
ORDER BY SUM(SCORE) DESC
6.3 连接查询
数据表之间的联系是通过表的字段值来体现的，这种字段称为连接字段。连接操作的目的就是通过加在连接字段的条件将多个表连接起来，以便从多个表中查询数据。前面的查询都是针对一个表进行的，当查询同时涉及两个以上的表时，称为连接查询。
6.3.1 等值连接与非等值连接
6.3.2自身连接
6.3.1 等值连接与非等值连接
•连接条件的一般格式为：
[<表名1>.] <列名1> <比较运算符> [<表名2>.] <列名2>
•其中，比较运算符主要有：＝、＞、＜、＞＝、＜＝、！＝。当比较运算符为“＝”时，称为等值连接，其他情况为非等值连接。
•例6-29 查询张飞同学所选修的课程。
SELECT S.SNO ,SN,CNO
FROM S,SC
WHERE (S.SNO = SC. SNO) AND (SN=‘张飞’)
6.3.1 等值连接与非等值连接
•例6-30 查询所有选课学生的学号、姓名、选课名称及成绩。
程序清单如下：
SELECT S.SNO,SN,CN,SCORE
FROM S,C,SC
WHERE S.SNO=SC.SNO
AND SC.CNO=C.CNO
•注意：本例涉及三个表，WHERE子句中有两个连接条件。当有两个以上的表进行连接时，称为多表连接。
6.3.2 自身连接
•当一个表与其自已进行连接操作时，称为表的自身连接。要查询的内容均在同一表中，可以将表分别取两个别名，一个是X，一个是Y。将X， Y中满足查询条件的行连接起来。这实际上是同一表的自身连接。
•例6-31 查询所有比李明工资高的教师姓名、性别、工资和刘伟的工资。
程序清单如下：
SELECT X.TN,X.SAL AS SAL_a,Y.SAL AS SAL_b
FROM T AS X ,T AS Y
WHERE X.SAL>Y.SAL AND Y.TN='李明'
6.3.2 自身连接
例6-32 检索所有学生姓名，年龄和选课名称。
程序清单如下：
SELECT SN,AGE,CN
FROM S,C,SC
WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO
6.4 子查询
在WHERE子句中包含一个形如SELECT-FROM-WHERE的查询块，此查询块称为子查询或嵌套查询，包含子查询的语句称为父查询或外部查询。
6.4.1返回一个值的子查询
6.4.1返回一个值的子查询
6.4.1 返回一个值的子查询
•当子查询的返回值只有一个时，可以使用比较运算符（=, >, <, >=, <=, !=）将父查询和子查询连接起来。
•例6-33 查询与李明教师职称相同的教师号、姓名。
程序清单如下：
SELECT TNO,TN
FROM T
WHERE PROF=(SELECT PROF
FROM T
WHERE TN='李明')
6.4.2 返回一组值的子查询
如果子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以在比较运算符和子查询之间插入ANY或ALL。
1. 使用ANY
2. 使用IN
3. 使用ALL
4. 使用EXISTS
6.4.2 返回一组值的子查询
•使用ANY
例6-34 查询讲授课程号为C5的教师姓名。
程序清单如下：
SELECT TN
FROM T
WHERE TNO=ANY
(SELECT TNO
FROM TC
WHERE CNO='C5')
6.4.2 返回一组值的子查询
2. 使用IN
可以使用IN代替“=ANY”。
例6-36 查询讲授课程号为C5的教师姓名
程序清单如下：
SELECT TN
FROM T
WHERE TNO IN
(SELECT TNO
FROM TC
WHERE CNO='C5')
6.4.2 返回一组值的子查询
3. 使用ALL
ALL的含义为全部。
例6-37 查询其他系中比电力系所有教师工资都高的教师的姓名和工资。
程序清单如下：
SELECT TN,SAL
FROM T
WHERE SAL>ALL
(SELECT SAL
FROM T
WHERE DEPT='电力')
AND DEPT!= ‘电力’
6.4.2 返回一组值的子查询
4. 使用EXISTS
EXISTS表示存在量词，带有EXISTS的子查询不返回任何实际数据，它只得到逻辑值“真”或“假”。当子查询的的查询结果集合为非空时，外层的WHERE子句返回真值，否则返回假值。
•例6-38 查询讲授课程号为C5的教师姓名
程序清单如下：
SELECT TN
FROM T
WHERE EXISTS
(SELECT *
FROM TC
WHERE TNO=T.TNO
AND CNO='C5')