数据基本特性（数据操作语言）

5.1　数据查询功能

本章所有查询均在Student（学生）、Course（课程）和SC（选课）表上进行，这三张表的结构如表5-1~表5-3所示。对Student表，为了简化操作，描述学生年龄的字段（sage）选用的是整型类型，而没有选择更符合实际情况、更易于维护的出生日期（日期类型）。

假设这三张表中已有如表5-4~表5-6所示的数据。

5.1.1 查询语句的基本结构

查询就是从数据库中检索满足条件的数据。可以查询一张表中的数据，也可以同时查询多张表中的数据。查询的结果是一个由0行（没有满足条件的数据）或多行数据组成的记录集合，而且在查询过程中还可以对查询的结果进行排序、汇总等。

查询语句的基本结构可描述为：

SELECT <目标列名序列>　　　　　-- 需要哪些列

　FROM <表名>　　　　　　　　 -- 来自哪张（或哪些）表

　[WHERE <行选择条件>]　　　　-- 根据什么条件

　[GROUP BY <分组依据列>]

　[HAVING <组选择条件>]

　[ORDER BY <排序依据列>]

其中SELECT子句表示要查看哪些列的内容；FROM子句用于指定这些列都来自哪张（或哪些）表；WHERE子句用于指定选择数据的条件；GROUP BY子句用于指定用于进行分组统计的列；HAVING子句用于指定对分组统计结果的筛选条件；ORDER BY子句用于指定查询结果的排列顺序。在这些子句中，SELECT子句和FROM子句是必须的，其他子句都是可选的。

SELECT子句部分可对应的是关系代数表达式中的投影运算，WHERE子句对应的是关系代数表达式中的选择运算。

5.1.2 单表查询

单表查询，即数据只涉及一张表的查询。所有的查询结果按SQL Server 2019数据库管理系统的形式显示。

5.1.2.1 选择表中若干列

1. 查询指定的列

在很多情况下，用户可能只对表中的部分列感兴趣，这时可通过在SELECT子句的<目标列名序列>中指定要查询的列来实现。

例1. 查询全体学生的学号和姓名。

SELECT Sno, Sname FROM Student

查询结果如图5-1所示。

图5-1　例1的查询结果

例2. 查询全体学生的姓名、学号和所在系。

SELECT Sname, Sno, Sdept FROM Student

查询结果如图5-2所示。

注意：目标列的选择顺序可以与表中定义的字段的顺序不一致。

图5-2 例2的查询结果

2. 查询全部列

如果要查询表中的全部列，可以使用两种方法：一种是在<目标列名序列>中列出所有的列名；另一种是如果列的显示顺序与其在表中定义的顺序相同，则可以在<目标列名序列>中写星号“*”。

例3．查询全体学生的详细记录。

SELECT Sno, Sname, Ssex, Sage, Sdept FROM Student

等价于：

SELECT * FROM Student

3. 查询经过计算的列

SELECT子句中的<目标列名序列>可以是表中存在的列，也可以是表达式、常量或者函数。

例4．含表达式的列：查询全体学生的姓名及出生年份。

在Student表中只记录了学生的年龄，而没有记录学生的出生年份，但用使用当前年份减去年龄，就可以得到出生年份。因此实现此功能的查询语句为：

SELECT Sname, 2021 - Sage

　FROM Student

查询结果如图5-3所示。

图5-3 例4的查询结果

图5-4 例5的查询结果

例5．含字符串常量的列：查询全体学生的姓名和出生年份，并在出生年份列前加入一个新列，此列的每行数据均为“出生年份”常量值。

SELECT Sname, '出生年份', 2021 - Sage

　FROM Student

查询结果如图5-4所示。

从图5-3和5-4可以看到，经过计算的表达式列、常量列的显示结果都没有列标题[图中显示为“(无列名)”]，通过为列指定别名的方法可以改变查询结果显示的列标题，这对于含算术表达式、常量、函数名的目标列尤为有用。

改变显示的列标题的语法格式为：

[列名 | 表达式] [ AS ] 列标题

或

列标题 = [列名 | 表达式]

例如，例4的代码可写成：

SELECT Sname 姓名, 2021 - Sage 年份

　FROM Student

5.1.2.2 选择表中的若干元组

1. 消除取值相同的行

从关系数据库理论上说，关系表中不允许存在取值全都相同的数据行，但在进行了对列的选择后，就有可能在查询结果中出现取值完全相同的数据行。取值相同的行在结果中是没有意义的，因此，可消除这些行。

例6．查询选修了课程的学生学号。

如果写成：

SELECT Sno FROM SC

则查询结果的部分数据如图5-5（a）所示。从这个结果可看到其中有许多重复的行（实际上一个学生选了多少门课，其学号就在结果中重复多少次）。

SQL中的DISTINCT关键字可以去掉结果中的重复行。DISTINCT关键字写在SELECT词的后边、目标列名序列的前。

去掉上述查询结果中重复行的语句为：

SELECT DISTINCT Sno FROM SC

其查询结果如图5-5（b）所示。

图5-5　DISTINCT的作用

2. 查询满足条件的元组

查询满足条件的元组是通过WHERE子句实现的。WHERE子句常用的查询条件及谓词如表5-7所示。

（1）比较。比较的谓词有：=（等于）、>（大于）、>=（大于或者等于）、<=（小于或者等于）、<（小于）、< >（不等于）、!=（不等于）。

例7．查询计算机系全体学生的姓名。

　SELECT Sname FROM Student WHERE Sdept='计算机系'

例8．查询所有年龄在20岁以下的学生的姓名及年龄。

　SELECT Sname, Sage FROM Student WHERE Sage < 20

例9．查询考试成绩有不及格的学生的学号。

SELECT DISTINCT Sno FROM SC WHERE Grade < 60

注意：考试成绩为NULL的记录（即还未考试的课程）并不满足“Grade < 60”条件，因为NULL不能和确定的值进行比较运算。我们在后边“涉及空值的查询”部分再详细讨论。

（2）确定范围。

BETWEEN … AND和NOT BETWEEN … AND是逻辑运算符，可以用来查找属性值在（或不在）指定范围内的元组，其中BETWEEN后边指定范围的下限，AND后边指定范围的上限。BETWEEN … AND的格式为：

列名 | 表达式 [ NOT ] BETWEEN 下限值 AND 上限值

BETWEEN … AND中列名或表达式的类型要与下限值或上限值的类型相同。

“BETWEEN下限值AND上限值”的含义是：如果列或表达式的值在下限值和上限值范围内（包括边界值），则结果为True，表明此记录符合查询条件。

“NOT BETWEEN 下限值AND上限值”的含义正好相反：如果列或表达式的值在下限值和上限值范围内（不包括边界值），则结果为False，表明此记录不符合查询条件。

例10．查询年龄在20～23岁的学生的姓名、所在系和年龄。

SELECT Sname, Sdept, Sage FROM Student

　WHERE Sage BETWEEN 20 AND 23

此句等价于：

SELECT Sname, Sdept, Sage FROM Student

　WHERE Sage >= 20 AND Sage <= 23

例11．查询年龄不在20～23岁的学生姓名、所在系和年龄。

SELECT Sname, Sdept, Sage FROM Student

　WHERE Sage NOT BETWEEN 20 AND 23

此句等价于：

SELECT Sname, Sdept, Sage FROM Student

　WHERE Sage < 20 OR Sage > 23

例12. 对于日期类型的数据也可以使用基于范围的查找。例如，假设有教师表（Teachers），其中包含教师号（Tid）、姓名（Tname）和出生日期（BirthDate）列，现要查询1970年1月1日至1979年12月31日出生的教师的信息，则语句如下：

SELECT Tid, Tname, BirthDate FROM Teachers

　WHERE BirthDate BETWEEN '1970/1/1' and '1979/12/31'

（3）确定集合。

可以使用IN运算符来查找属性值属于指定集合的元组。IN的使用格式为：

列名[ NOT ] IN (常量1,常量2, … ,常量n)

“IN”运算符的含义为：当列中的某个值与IN中的某个常量值相等时，则结果为True，表明此记录为符合查询条件的记录。

“NOT IN”运算符的含义正好相反：当列中的某个值与某个常量值相等时，则结果为False，表明此记录为不符合查询条件的记录。

例13. 查询信息管理系、通信工程系和计算机系学生的姓名和性别。

SELECT Sname, Ssex FROM Student

　WHERE Sdept IN ('信息管理系','通信工程系','计算机系')

此句等价于：

SELECT Sname, Ssex FROM Student

　WHERE Sdept = '信息管理系' OR Sdept = '通信工程系' OR Sdept = '计算机系'

例14. 查询不在第2、4、6学期开设的课程的课程名和开课学期。

SELECT Cname, Semester FROM Course

　WHERE Semester NOT IN (2,4,6)

此句等价于：

SELECT Cname, Semester FROM Course

　WHERE Semester != 2 AND Semester != 4 AND Semester != 6

（4）字符串匹配。

LIKE用于查找指定列中与匹配串匹配的元组。匹配串是一种特殊的字符串，其特殊之处在于它不仅可以包含普通字符，还可以包含通配符。通配符用于表示任意的字符或字符串。在实际应用中，如果需要从数据库中检索记录，但又不能给出精确的字符查询条件，就可以使用LIKE运算符和通配符来实现模糊查询。在LIKE运算符前也可以使用NOT运算符，表示对结果取反。

LIKE运算符的一般使用形式为：

列名 [NOT ] LIKE <匹配串>

匹配串中可包含如下4种通配符：

● _：匹配任意一个字符。

● %：匹配零个或多个字符。

● [ ]：匹配[ ]中的任意一个字符。如[acdg]表示匹配a、c、d、g中的任何一个。若要比较的字符是连续的，则可以用连字符“-”表达，例如，若要匹配b、c、d、e中的任何一个字符，可以表达为[b-e]。

● [ ^ ]：不匹配[ ]中的全部字符。如[^acdg]表示不匹配a、c、d、g。同样，若要比较的字符是连续的，也可以用连字符“-”表达，例如，若不匹配b、c、d、e中的全部字符，则可以表达为[^b-e]。

例15. 查询全部姓“王”的学生的详细信息。

SELECT * FROM Student WHERE Sname LIKE '王%'

例16. 查询姓“王”且名字是3个字的学生姓名。

SELECT * FROM Student WHERE Sname LIKE '王__'

例17. 查询姓“张”、姓“李”和姓“刘”的学生详细信息。

SELECT * FROM Student WHERE Sname LIKE '[张李刘] %'

例18. 查询名字中第2个字为“小”或“大”的学生姓名和学号。

SELECT Sname, Sno FROM Student WHERE Sname LIKE '_[小大]%'

例19. 查询所有不姓“刘”的学生姓名。

SELECT Sname FROM Student WHERE Sname NOT LIKE '刘%'

（5）涉及空值的查询。空值（NULL）在数据库中有特殊的含义，它表示不确定的值。例如，某些学生选课后还没有考试，则这些学生就只有选课记录，而没有考试成绩，因此考试成绩就是空值。判断某个值是否为NULL，不能使用普通的比较运算符（=、!=等），只能使用专门的判断NULL的子句来完成。

判断列值是否为空的子句为：

列名 IS NOT NULL

例20. 查询没有考试成绩的学生的学号和相应的课程号。

SELECT Sno, Cno FROM SC WHERE Grade IS NULL

例21. 查询所有有考试成绩的学生的学号、课程号和成绩。

SELECT Sno, Cno,Grade FROM SC WHERE Grade IS NOT NULL

（6）多重条件查询。在WHERE子句中可以使用逻辑运算符AND和OR来组成多条件查询。

使用AND谓词的语法格式为：

布尔表达式1 AND布尔表达式2 AND … AND 布尔表达式n

用AND连接的条件表示只有当全部的布尔表达式均为True时，整个表达式的结果才为True。只要有一个布尔表达式的结果为False，则整个表达式结果即为False。

使用OR谓词的语法格式为：

布尔表达式1 OR布尔表达式2 OR … OR 布尔表达式n

用OR连接的条件表示只要其中一个布尔表达式为True，则整个表达式的结果即为True，只有当全部布尔表达式的结果均为False时，整个表达式的结果才为False。

例22. 查询计算机系年龄在20岁以下的学生的姓名和年龄。

SELECT Sname,Sage FROM Student

　WHERE Sdept ='计算机系' AND Sage < 20

例23. 查询计算机系和信息管理系学生中年龄在18到20岁的学生的学号、姓名、所在系和年龄。

SELECT Sno, Sname, Sdept, Sage FROM Student

　WHERE (Sdept ='计算机系' OR Sdept ='信息管理系')

　AND Sage between 18 and 20

注意：OR运算符的优先级小于AND，要改变运算的顺序可以通过添加括号的方式实现。

例23的查询也可以写为：

SELECT Sno, Sname, Sdept, Sage FROM Student

　WHERE Sdept in ('计算机系', '信息管理系')

　AND Sage between 18 and 20

5.1.2.3 对查询结果进行排序

有时，我们希望查询的结果能按一定的顺序显示出来，如按考试成绩从高到低排列学生的考试情况。SQL语句具有按用户指定的列进行排序的功能，而且查询结果既可以按一列排序，也可以按多列进行排序，排序可以是从小到大（升序），也可以是从大到小（降序）。排序子句的格式为：

ORDER BY <列名> [ASC | DESC ] [ ，… n ]

其中<列名>为排序的依据列，可以是列名或列的别名。ASC表示按列对结果集进行升序排序，DESC表示按列对结果集进行降序排序。如果没有指定排序方式，则默认的排序方式为升序排序。

如果在ORDER BY子句中指定了多个排序依据列，则这些列在该子句中出现的顺序决定了对查询结果集进行排序的方式。当指定多个排序依据列时，系统首先按排在第一位的列进行排序，如果排序后存在两个或两个以上列值相同的记录，则将值相同的记录再依据排在第二位的列进行排序，依此类推。

例24．将全体学生按年龄升序排序。

SELECT * FROM Student ORDER BY Sage ASC

例25．查询选了“C002”课程的学生学号及其成绩，查询结果按成绩降序排列。

SELECT Sno, Grade FROM SC

　WHERE Cno='C002' ORDER BY Grade DESC

例26．查询全体学生信息，查询结果按所在系的系名升序排列，同系的学生按年龄降序排列。

SELECT * FROM Student ORDER BY Sdept, Sage DESC

5.1.2.4 使用聚合函数汇总数据

聚合函数也称为集合函数或统计函数，其作用是对一组值进行计算并返回一个单值。SQL提供的聚合函数主要有：

● COUNT(*)：统计数据的行数。

● COUNT( [DISTINCT] <列名>)：统计列中除空值外的值的个数，DISTINCT选项表示去掉列的重复值后再统计。

● SUM(<列名>)：计算列值总和（必须是数值型列）。

● AVG(<列名>)：计算列值平均值（必须是数值型列）。

● MAX(<列名>)：求列值最大值。

● MIN(<列名>)：求列值最小值。

上述函数中除COUNT(*)外，其他函数在计算过程中均忽略NULL值。

聚合函数的计算范围既可以是满足WHERE子句条件的记录（如果对整个表进行计算），也可以满足条件的组（如果进行了分组）。

例27. 统计学生总人数。

SELECT COUNT(*) FROM Student

例28. 统计选修了课程的学生的人数。

SELECT COUNT(DISTINCT Sno) FROM SC

由于一个学生可选多门课程，为避免重复计算这样的学生，加DISTINCT去掉重复值。

例29. 统计“202111101”学生的考试总成绩。

SELECT SUM(Grade) FROM SC WHERE Sno = '202111101'

例30. 统计“C001”课程的考试平均成绩。

SELECT AVG(Grade) FROM SC WHERE Cno='C001'

例31. 查询“C001”课程的考试最高分和最低分。

SELECT MAX(Grade) 最高分, MIN(Grade) 最低分

　SELECT MAX(Grade)FROM SC WHERE Cno='C001'

注意：聚合函数不能作为数据筛选条件出现在WHERE子句中。

例如：查询年龄最大学生的姓名，如下写法是错误的：

SELECT Sname FROM Student WHERE Sage = MAX(Sage)

怎样写出这个查询的正确语句我们在子查询部分介绍。

5.1.2.5 对查询结果进行分组计算

在对表单进行查询时，有时需要先对数据进行分组，再针对每组进行统计计算，而不是针对全表进行计算。例如，统计每个学生的平均成绩、每个系的学生人数，这时就需要用到分组子句：GROUP BY 。GROUP BY子句可将统计控制在组一级。分组的目的是细化聚合函数的作用对象。在一个查询语句中，可以使用多列进行分组。需要注意的是，如果使用了分组子句，则查询列表中的每列必须是分组依据列（在GROUP BY 子句中指定的列）或是聚合函数中的一种。

使用GROUP BY子句时，如果在SELECT的查询列表中包含聚合函数，则针对每个组计算出一个汇总值，从而实现对查询结果的分组统计。

分组语句应写在WHERE子句的后边，它的一般形式为：

GROUP BY <分组依据列> [,… n ]

　[ HAVING 组筛选条件 ]

1. 使用GROUP BY子句

例32. 统计每门课程的选课人数，列出课程号和人数。

SELECT Cno AS 课程号, COUNT(Sno) AS 选课人数 FROM SC

　GROUP BY Cno

该语句首先按Cno的值对查询结果进行分组，所有Cno值相同的元组归为一组，然后对每组用COUNT函数进行计算，统计出每组的学生人数。该过程如图5-16所示。

例33. 查询每个学生的选课门数和平均成绩。

SELECT Sno 学号, COUNT(*) 选课门数, AVG(Grade) 平均成绩

　FROM SC GROUP BY Sno

注意：GROUP BY子句中的分组依据列必须是表中存在的列名，不能使用AS 子句指派的列别名。

例如，在例33中，不能将“GROUP BY Sno”写成：“GROUP BY 学号”。

例34. 统计每个系的学生人数和平均年龄。

SELECT Sdept, COUNT(*) AS 学生人数, AVG(Sage) AS 平均年龄

　FROM Student GROUP BY Sdept

例35. 带WHERE子句的分组。统计每个系的女生人数。

SELECT Sdept, COUNT(*) 女生人数 FROM Student

　WHERE Ssex = '女' GROUP BY Sdept

例36. 按多列分组。统计每个系的男生人数和女生人数以及男生的最大年龄和女生的最大年龄。结果按系名的升序排序。

SELECT Sdept, Ssex, COUNT(*) 人数, Max(Sage) 最大年龄

　FROM Student GROUP BY Sdept, Ssex

　ORDER BY Sdept

2. 使用HAVING子句

HAVING子句用于对分组后的统计结果进行筛选，其功能类似于WHERE子句，但它用于组而不是单个记录。在HAVING子句中一般使用聚合函数作为筛选条件，而WHERE子句一般是使用表中的列作为筛选条件。HAVING子句通常与GROUP BY子句一起使用。

例37．查询选课门数超过3门的学生的学号和选课门数。

SELECT Sno, COUNT(*) 选课门数 FROM SC

　GROUP BY Sno HAVING COUNT(*) > 3

此语句的处理过程为：先用GROUP BY按Sno进行分组，再用统计函数COUNT分别对每组进行统计，最后挑选出统计结果满足大于3的组。

例38．查询平均成绩大于或者等于80分的学生的学号、选课门数和平均成绩。

SELECT Sno, COUNT(*)选课门数, AVG(Grade)平均成绩 FROM SC

　GROUP BY Sno HAVING AVG(Grade) >= 80

正确理解WHERE、GROUP BY 和 HAVING 子句的作用和执行顺序，对编写正确、高效的查询语句很有帮助：

●　WHERE子句用于筛选FROM子句中指定的数据源所产生的行数据。

●　GROUP BY子句用于对经WHERE子句筛选后的结果数据进行分组。

●　HAVING子句用于对分组后的统计数据再进行筛选。

对可以在分组操作之前应用的筛选条件，在WHERE子句中指定它们会更有效，这样可以减少参与分组的数据行。在HAVING子句中指定的筛选条件应该是那些必须在执行分组操作之后应用的条件。

5.1.3 多表查询

多表查询是指一个查询同时涉及两个或两个以上的表。它对应的是关系代数表达式的连接运算。多表查询是关系数据库中最主要的查询，多表查询需要对表进行连接操作，连接操作主要包括内连接、外连接和交叉连接等。本书只介绍内连接、外连接及一种特殊的内连接——自连接。

5.1.3.1 内连接

内连接是一种最常用的连接类型。使用内连接时，如果两个表的相关字段满足连接条件，则从这两个表中提取相关数据并组合成新的记录。

在非ANSI标准中，连接条件写在WHERE子句中，在ANSI SQL-92中，连接条件写在JOIN子句中。这些连接方式分别被称为theta连接方式和ANSI连接方式。这里介绍ANSI连接方式。

ANSI的内连接语法格式为：

FROM 表1 [ INNER ] JOIN 表2 ON <连接条件>

在连接条件中指明两个表按什么条件进行连接，连接条件中的比较运算符称为连接谓词。连接条件的一般格式为：

[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

注意：进行比较运算的列必须是语义相同的列。

当比较运算符为等号（=）时，称为等值连接，使用其他运算符的连接称为非等值连接。

从概念上讲，数据库管理系统执行连接操作的过程是：首先取表1中的第1个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第1个元组与该元组拼接起来，形成结果表中的一个元组。表2全部查找完毕后，再取表1中的第2个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第2个元组与该元组拼接起来，形成结果表中的另一个元组。重复这个过程，直到表1中的全部元组都处理完毕为止。

5.1.3.2 自连接

自连接是一种特殊的内连接，它是指相互连接的表在物理上虽为同一张表，但可以通过为表取别名的方法将其在逻辑上分为两张表。

5.1.3.3 外连接

在内连接操作中，只有满足连接条件的元组才能作为结果输出，但有时我们也希望输出那些不满足连接条件的元组信息，例如，查看全部课程的选课情况，包括有学生选的课程和没有学生选的课程。如果用内连接实现（通过SC表和Course表的内连接），则只能找到有学生选的课程，因为内连接的结果首先是要满足连接条件：SC.Cno = Course.Cno。对于在Course表中有，但在SC表中没有的课程（没有人选），由于不满足SC.Cno = Course.Cno条件，因此是查找不出来的。这种情况就需要使用外连接来实现。

外连接是只限制一张表中的数据必须满足连接条件，而另一张表中数据可以不满足连接条件。ANSI方式的外连接语法格式为：

FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON <连接条件>

LEFT [OUTER] JOIN称为左外连接，RIGHT [OUTER] JOIN称为右外连接。左外连接的含义是限制表2中的数据必须满足连接条件，而不管表1中的数据是否满足连接条件，均输出表1中的数据；右外连接的含义是限制表1中的数据必须满足连接条件，而不管表2中的数据是否满足连接条件，均输出表2中的数据。

设有表A与表B两个数据集，内连接与外连接示意图如图5-25所示。

图5-25　内连接与外连接示意图

● 如果表A与表B进行内连接操作：

FROM 表A INNDER JOIN 表B ON 表A. 列名 = 表B. 列名

则结果为两个表中满足连接条件的数据集，即图5-25中数据集C部分。

● 如果表A与表B进行左外连接：

FROM 表A LEFT OUTER JOIN 表B ON 表A. 列名=表B. 列名

则连接后的结果集为图5-25中数据集A1 数据集C。

● 如果表A与表B进行右外连接：

FROM 表A RIGHT OUTER JOIN 表B ON 表A. 列名=表B. 列名

则连接后的结果集为图5-25中数据集B1 数据集C。

外连接通常是在两个表中进行的，但也支持对多个表进行外连接操作。如果是对多个表进行外连接，则数据库管理系统是按连接书写的顺序，从左至右进行连接的。

5.1.4 使用TOP限制结果集

在使用SELECT语句进行查询时，有时我们只希望列出查询结果集中的前几行结果，而不是全部结果。例如，只查询考试前三名学生的成绩，这种查询可以使用TOP谓词来实现。

使用TOP谓词的格式为：

TOP (expression)[ percent ] [WITH TIES ]

其中：

● expression：指定要返回的行数的数值表达式。如果指定percent，expression会隐式转换为float值。否则，expression会转换为bigint。

● percent：指定查询只返回结果集中的前expression%行的数据。小数部分的值向上进入下一个整数值。

● WITH TIES：返回排序后与结果集中的最后一个排序列值相等的两行或多行数据。该参数必须与ORDER BY子句一起使用。WITH TIES可能会返回多于在expression中指定值的行数。例如，如果expression设置为5，而有2个与第5行中ORDER BY列的值相等，则结果集将包含7行数据。

TOP谓词写在SELECT的后边（如果有DISTINCT的话，则TOP写在DISTINCT后边），查询列表的前边。

例52. 查询年龄最大的三个学生的姓名、年龄及所在系。

SELECT TOP (3) Sname, Sage, Sdept FROM Student

　ORDER BY Sage DESC

若要包括年龄并列第三名的学生，则此句可写为：

SELECT TOP (3) WITH TIES Sname, Sage, Sdept

　FROM Student ORDER BY Sage DESC

注意：如果在TOP子句中使用了“WITH TIES”谓词，则必须要使用ORDER BY子句对查询结果进行排序。

例53．查询Java课程考试成绩前三名学生的姓名和成绩。

SELECT TOP (3) WITH TIES Sname, Grade

　FROM Student S JOIN SC on S.Sno = SC.Sno

　JOIN Course C ON C.Cno = SC.Cno

　WHERE Cname = 'Java'

　ORDER BY Grade DESC

例54. 查询选课人数最少的两门课程（不包括没有人选的课程），列出课程号和选课人数。

SELECT TOP (2) WITH TIES Cno, COUNT(*)选课人数

　FROM SC

　GROUP BY Cno

　ORDER BY COUNT(*) ASC

例55. 查询计算机系选课门数超过2门的学生中，考试平均成绩前2名（包括并列的情况）学生的学号、选课门数和平均成绩。

SELECT TOP (2) WITH TIES S.Sno, COUNT(*)选课门数,AVG(Grade)平均成绩

　FROM Student S JOIN SC ON S.Sno = SC.Sno

　WHERE Sdept = '计算机系'

　GROUP BY S.sno

　HAVING COUNT(*) > 2

　ORDER BY AVG(Grade) DESC

5.1.5 CASE表达式

CASE表达式是一种多分支的函数，它可以根据条件列表的值返回多个可能的结果表达式中的一个。

CASE表达式可用在任何允许使用表达式的地方，但它不是一个完整的T-SQL语句，因此不能单独执行，只能作为一个可以单独执行的语句的一部分来使用。

CASE表达式分为简单CASE表达式和搜索CASE表达式两种类型。

5.1.5.1 简单CASE表达式

简单 CASE表达式的语法格式为：

CASE 测试表达式

　WHEN 简单表达式1 THEN 结果表达式1

　WHEN 简单表达式2 THEN 结果表达式2

　…

　WHEN 简单表达式n THEN 结果表达式n

　[ ELSE 结果表达式n 1 ]

END

其中：

●　测试表达式可以是一个变量名、字段名、函数或子查询。

●　简单表达式中不能包含比较运算符，它们给出被比较的表达式或值，其数据类型必须与测试表达式的数据类型相同，或者可以隐式转换为测试表达式的数据类型。

CASE表达式的执行过程为：

1. 先计算测试表达式，然后按从上到下的书写顺序将测试表达式的值与每个WHEN子句的简单表达式进行比较。

2. 如果某个简单表达式的值与测试表达式的值匹配（相等），则返回第一个与之匹配的THEN子句中的结果表达式的值。

3. 如果所有简单表达式的值与测试表达式的值都不匹配，若指定了ELSE子句,则返回ELSE子句中指定的结果表达式的值；若没有指定ELSE子句，则返回NULL。

CASE表达式经常被应用在SELECT语句中，作为不同数据的不同返回值。

5.1.5.2 搜索CASE表达式

搜索CASE表达式的语法格式为：

CASE

　WHEN 布尔表达式1 THEN 结果表达式1

　WHEN 布尔表达式2 THEN 结果表达式2

　…

　WHEN 布尔表达式n THEN 结果表达式n

　[ ELSE 结果表达式n 1 ]

END

搜索CASE表达式中的各个WHEN子句的布尔表达式可以是由比较运算符、逻辑运算符组合起来的复杂的布尔表达式。

搜索CASE表达式的执行过程如下：

1. 按从上到下的书写顺序计算每个WHEN子句的布尔表达式。

2. 返回第一个取值为TRUE的布尔表达式所对应的THEN子句中的结果表达式的值。

3. 如果没有取值为TRUE的布尔表达式，则当指定了ELSE子句时,返回ELSE子句中指定的结果；如果没有指定ELSE子句，则返回NULL。

用搜索CASE表达式，例56的查询可写为：

SELECT S.Sno 学号,Sname 姓名,

　CASE

　　WHEN Sdept = '计算机系' THEN 'CS'

　　WHEN Sdept = '信息管理系' THEN 'IM'

　　WHEN Sdept = '通信工程系' THEN 'COM'

　END AS 所在系, Grade 成绩

　FROM Student S join SC ON S.Sno = SC.Sno

　JOIN Course C ON C.Cno = SC.Cno

　WHERE Cname = 'Java'

5.1.5.3 CASE表达式应用示例

例57. 查询“C001”课程的考试情况，列出学号、成绩和成绩等级，成绩等级的处理如下：

如果成绩大于或者等于90分，则成绩等级为“优”；

如果成绩为80~89分，则成绩等级为“良”；

如果成绩为70~79分，则成绩等级为“中”；

如果成绩为60~69分，则成绩等级为“及格”；

如果成绩小于60分，则成绩等级为“不及格”。

这个查询需要对成绩进行分情况判断，而且是将成绩与一个范围的数值进行比较，因此，需要使用搜索CASE表达式实现。具体如下：

SELECT Sno AS 学号,Grade AS 成绩,

　CASE

　　WHEN Grade >= 90 THEN '优'

　　WHEN Grade BETWEEN 80 AND 89 THEN '良'

　　WHEN Grade BETWEEN 70 AND 79 THEN '中'

　　WHEN Grade BETWEEN 60 AND 69 THEN '及格'

　　WHEN Grade < 60 THEN '不及格'

　END AS 成绩等级

　FROM SC WHERE Cno = 'C001'

例58. 统计每个学生的考试平均成绩，列出学号、考试平均成绩和考试情况。对考试情况的处理为：

如果平均成绩大于或者等于90分，则考试情况为“好”；

如果平均成绩为80～89分，则考试情况为“比较好”；

如果平均成绩为70～79分，则考试情况为“一般”；

如果平均成绩为60～69分，则考试情况为“不太好”；

如果平均成绩低于60分，则考试情况为“比较差”。

这个查询是对考试平均成绩进行分情况处理，而且只能使用搜索CASE表达式。

SELECT Sno 学号, AVG(Grade) 平均成绩,

　CASE

　　WHEN AVG(Grade) >= 90 THEN '好'

　　WHEN AVG(Grade) BETWEEN 80 AND 89 THEN '比较好'

　　WHEN AVG(Grade) BETWEEN 70 AND 79 THEN '一般'

　　WHEN AVG(Grade) BETWEEN 60 AND 69 THEN '不太好'

　　WHEN AVG(Grade) < 60 THEN '比较差'

　END AS 考试情况

　FROM SC GROUP BY Sno

5.1.6 子查询

在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。可以在SELECT、INSERT、UPDATE或DELETE语句中嵌套一个SELECT语句，被嵌套的SELECT语句称为子查询或内层查询，而包含子查询的SELECT语句称为主查询或外层查询。子查询可以嵌套多层。为了与外层查询有所区别，总是把子查询写在圆括号中。与外层查询类似，子查询语句中也必须至少包含SELECT子句和FROM子句，并根据需要选择使用WHERE子句、GROUP BY子句和HAVING子句。

子查询语句可以出现在任何能够使用表达式的地方，但通常情况下，子查询语句用在外层查询的WHERE子句或HAVING子句中，与比较运算符或逻辑运算符一起构成查询条件。

5.1.6.1 使用子查询进行基于集合的测试

使用子查询进行基于集合的测试时，通过运算符IN或NOT IN，将一个表达式的值与子查询返回的结果集进行比较。其形式为：

WHERE <列名> [ NOT ] IN ( 子查询 )

如果在子查询中没有与外层查询的关联，则这种查询形式的SELECT语句是分步骤实现的，即先执行子查询，然后在子查询的结果基础上执行外层查询。子查询返回的结果实际上是一个集合，外层查询就是在这个集合上使用IN运算符进行比较。

注意：当使用子查询进行基于集合的测试时，由该子查询返回的结果集中列的个数、数据类型以及语义必须与外层查询中列的个数、数据类型以及语义相同。当子查询返回结果之后，外层查询将使用这些结果筛选数据。

例59. 查询与刘晨在同一个系学习的学生，列出其学号、姓名和所在系。

SELECT Sno, Sname, Sdept FROM Student

　 WHERE Sdept IN

　　( SELECT Sdept FROM Student WHERE Sname = '刘晨' )

实际的查询过程为：

（1）执行子查询：确定“刘晨”所在的系。

SELECT Sdept FROM Student WHERE Sname = '刘晨'

查询结果为“计算机系”

（2）利用子查询的结果执行外层查询：查找此系的所有学生。

SELECT Sno, Sname, Sdept FROM Student

　WHERE Sdept IN ( '计算机系' )

从查询结果中可以看到其中也包含“刘晨”，如果不希望“刘晨”出现在查询结果中，则可为上述查询语句添加一个条件，如下所示：

SELECT Sno, Sname, Sdept FROM Student

　WHERE Sdept IN

　　(SELECT Sdept FROM Student WHERE Sname = '刘晨')

　AND Sname != '刘晨'

之前曾用自连接实现过此查询，从这个例子可以看出，SQL语言的使用是很灵活的，同样的查询要求可以用多种形式实现。随着学习的深入，我们会对这一点有更多的体会。

例60. 查询所有考试成绩大于90分的学生的学号和姓名。

SELECT Sno, Sname FROM Student

　WHERE Sno IN

　　( SELECT Sno FROM SC

　　　　WHERE Grade > 90 )

此查询也可以用多表连接实现：

SELECT Sno, Sname FROM Student

　　WHERE Student.Sno = SC.Sno AND Grade > 90

例61. 查询选修了Java课程的学生的学号和姓名。

分析：这个查询可以分为以下三个步骤实现：

（1）在Course表中，找出Java课程名对应的课程号。

（2）根据找到的Java课程号，在SC表中找出选修了该课程的学生的学号。

（3）根据得到的学号，在Student表中找出对应的学生姓名。

因此，该查询可通过两个子查询实现：

SELECT Sno, Sname FROM Student

　WHERE Sno IN

　　( SELECT Sno FROM SC　　　　　　　--得到选修了“Java”课程的学生的学号

　　　WHERE Cno IN

　　　　 ( SELECT Cno FROM Course　　 --得到“Java”课程对应的课程号

　　　　　 WHERE Cname = 'Java') )

此查询也可以用多表连接实现：

SELECT Student.Sno, Sname FROM Student

　JOIN SC ON Student.Sno = SC.Sno

　JOIN Course ON Course.Cno = SC.Cno

　WHERE Cname = 'Java'

5.1.6.2 使用子查询进行比较测试

在使用子查询进行比较测试时，通过比较运算符（=、< >、<、>、< =、> =），将一个表达式的值与子查询返回的值进行比较。如果比较的结果为True，则为满足条件的数据。

使用子查询进行比较测试的形式为：

WHERE <列名> 比较运算符 ( 子查询 )

注意：在使用子查询进行比较测试时，要求子查询语句必须是返回单值的查询语句。

同基于集合的子查询一样，用子查询进行比较测试时，也是先执行子查询，再根据子查询的结果执行外层查询。

例62. 查询C004课程的考试成绩高于此课程的考试平均成绩的学生的学号及其C004课程成绩。

分析：首先计算C004课程的平均成绩。

SELECT AVG(Grade) FROM SC

　　WHERE Cno = 'C004'

执行该语句将得到一个结果，这里是：81。

然后，查找所有C004课程中考试成绩高于81的学生的学号和成绩。

SELECT Sno , Grade FROM SC

　WHERE Cno = 'C004'

　　AND Grade > 81

将两个查询语句合起来即为满足要求的查询语句：

SELECT Sno , Grade FROM SC

　WHERE Cno = 'C004' and Grade > (

　　SELECT AVG(Grade) from SC

　　　WHERE Cno = 'C004')

例63. 查询计算机系年龄最大的学生的姓名和年龄。

分析：首先应在Student表中找出计算机系学生的最大年龄（在子查询中实现），然后在Student表中找出计算机系年龄等于该最大年龄的学生（在外层查询实现）。实现语句为：

SELECT Sname, Sage FROM Student

　WHERE Sdept = '计算机系'

　　AND Sage = (

　　　SELECT MAX(Sage) FROM Student

　　　　WHERE Sdept = '计算机系')

该查询也可用下述语句实现：

SELECT TOP 1 WITH TIES Sname, Sage FROM Student

　WHERE Sdept = '计算机系'

　ORDER BY Sage DESC

例64. 查询信息管理系学生中年龄大于该系学生平均年龄的学生姓名和年龄。

SELECT Sname, Sage FROM Student

　WHERE Sdept = '信息管理系'

　　AND Sage > (

　　　SELECT AVG(Sage) FROM Student

　　　　WHERE Sdept = '信息管理系')

在用子查询进行比较测试和进行基于集合的测试时，如果在子查询中不关联外层查询，则都是先执行子查询，再在子查询的结果基础之上执行外层查询。子查询只执行一次，子查询的查询条件不依赖于外层查询，我们将这样的子查询称为不相关子查询或嵌套子查询。

5.1.6.3 使用子查询进行存在性测试

在使用子查询进行存在性测试时，通常使用EXISTS谓词，其形式为：

WHERE [NOT] EXISTS (子查询)

带EXISTS谓词的子查询不返回查询的数据，只产生逻辑真值和逻辑假值。

●　EXISTS的含义是：当子查询中有满足条件的数据时，EXISTS返回真值（True），否则返回假值（False）。

●　NOT EXISTS的含义是：当子查询中有满足条件的数据时，NOT EXISTS返回假值（False），当子查询中不存在满足条件的数据时，NOT EXISTS返回真值（True）。

例65. 查询选修了C002课程的学生姓名。

SELECT Sname FROM Student

　WHERE EXISTS

　　( SELECT * FROM SC

　　　WHERE Sno = Student.Sno AND Cno = 'C002')

使用子查询进行存在性测试时需注意以下问题：

（1）带EXISTS谓词的查询是先执行外层查询，再执行内层查询。由外层查询的值决定内层查询的结果；内层查询的执行次数由外层查询的结果决定。

上述查询语句的处理过程为：

①无条件执行外层查询语句，先在外层查询的结果集中取第一行结果，得到Sno的一个当前值，再根据此Sno值处理内层查询。

②将外层的Sno值作为已知值执行内层查询，如果在内层查询中有满足其WHERE子句条件的记录，则EXISTS返回True，表示在外层查询结果集中的当前行数据为满足要求的一个结果。如果内层查询中不存在满足WHERE子句条件的记录，则EXISTS返回False，表示在外层查询结果集中的当前行数据不是满足要求的结果。

③顺序处理外层表Student表中的第2、3、… 行数据，直到处理完所有行为止。

（2）由于EXISTS的子查询只能返回True或False，因此在子查询中指定列名是没有意义的。所以在有EXISTS的子查询中，其目标列名部分通常都用“*”表示。

带EXISTS的子查询由于在子查询中涉及与外层表数据的关联，因此经常将这种形式的子查询称为相关子查询。

例65的查询等价于：

SELECT Sname FROM Student JOIN SC

　　ON SC.Sno = Student.Sno WHERE Cno = 'C002'

或

SELECT Sname FROM Student

　WHERE Sno IN (

　　SELECT Sno FROM SC WHERE Cno = 'C002' )

由此也可以看到，同一个查询可以通过不同的方式来实现。

在子查询语句前也可以使用NOT。“NOT IN (子查询语句) ”的含义与前面介绍的基于集合的“NOT IN”运算的含义相同；“NOT EXISTS”的含义是当子查询中存在至少一个满足条件的记录时，NOT EXISTS返回False，当子查询中不存在满足条件的记录时，NOT EXISTS返回True。

例66. 查询没选“C001”课程的学生姓名和所在系。

SELECT Sname, Sdept FROM Student

　WHERE Sno NOT IN (

　　SELECT Sno FROM SC

　　　WHERE Cno = 'C001' )

也可以用相关子查询实现：

SELECT Sname, Sdept FROM Student

　WHERE NOT EXISTS (

　　SELECT * FROM SC

　　　WHERE Sno = Student.Sno

　　　　AND Cno = 'C001' )

例67. 查询计算机系没选修Java课程的学生姓名和性别。

分析：对这个查询，首先应该在子查询中查出全部选修了Java课程的学生，然后在外层查询中去掉这些学生（即没有选修Java课程的学生），最后从这个结果中筛选出计算机系的学生。语句如下：

SELECT Sname, Ssex FROM Student

　WHERE Sno NOT IN (

　　SELECT Sno FROM SC JOIN Course

　　　ON SC.Cno = Course.Cno

　　　WHERE Cname = 'Java' )

　AND Sdept = '计算机系'

,