数据库原理：表中数据查询

数据表中的数据查询可以为用户提供单表和多表的查询服务。本章介绍单关系（表）数据查询、多关系（表）数据查询、子查询和集合运算查询的语法结构及使用方法。

本章学习目标：掌握单关系数据查询结构、常用聚合函数查询、分组查询、查询结果排序和限制查询结果数量； 掌握多关系查询结构、内连接查询、外连接查询、交叉连接查询和自连接查询；掌握普通子查询和相关子查询； 掌握集合运算查询。

一、单关系数据查询

1.1、单关系数据查询结构

数据查询是数据库中最常用的操作。SQL提供SELECT语句，用户通过查询操作可得到所需的信息。关系（表）的SELECT语句的一般语法格式如下。

SELECT [ALL|DISTINCT] <字段名> [AS 别名] [{,<字段名> [AS 别名]}]
FROM <表名或者视图名> [[AS] 表别名]
[WHERE <检索条件>]
[GROUP BY <字段名> [HAVING <条件表达式>]]
[ORDER BY <字段名> [ASC|DESC]]
[LIMIT子句]

对上述格式的相关说明如下。

(1) SELECT子句从列的角度进行投影操作，指定要在查询结果中显示的字段名。用户也可以用关键字AS为字段名指定别名（字段名和别名之间的AS也可以省略），这样，别名会代替字段名显示在查询结果中。关键字ALL表示所有元组，关键字DISTINCT表示消除查询结果中的重复元组。

(2) FROM子句指定要查询的表名或视图名，如果有多个表或视图，它们之间用逗号隔开。

(3) WHERE子句从行的角度进行选取操作，其中的检索条件是用来约束元组的，只有满足检索条件的元组才会出现在查询结果中。

(4) GROUP BY子句将查询结果按照其后的＜字段名＞的值进行分组。

(5) HAVING子句不能单独存在，如果需要的话，它必须在GROUP BY子句之后。这种情况下，只输出在分组查询之后满足HAVING条件的元组。

(6) ORDER BY子句用于对查询结果进行排序，ASC代表升序，DESC代表降序。默认情况下，如果在ORDER BY子句中没有显示指定排序方式，则表示对查询结果按照指定字段名进行升序排序。

(7) LIMIT子句限制查询结果的行数。

1.2、无条件查询

无条件查询是指只包含"SELECT···FROM···"的查询，也称作投影查询。投影查询相当于关系代数中的投影运算，需要注意的是，在关系代数中，投影运算之后自动消去重复行；而SQL中必须使用关键字DISTINCT才会消去重复行。

示例，查询数据库teaching中课程表的全部内容。

在课程表c中共有3列，字段名分别是cno（课程号）、cn （课程名）和ct（课时），本例要求查询课程表c中的全部内容，可以使用以下两种方法。

(1) SELECT后面列出表中的全部字段名

SELECT cno, cn, ct
FROM c;

本例的SQL代码在DOS窗口或者MysQL Shell窗口中的查询结果如图6-1所示。

注意：本例及后续的许多例子都使用了数据库teaching中的各个基本表，所以，如果是第一次使用该数据库，在执行查询任务之前，需要先执行语句"USE teaching;“。此外，在DOS窗口或者MySQL Shell窗口中，最后一行sQL语句后面的”;"不能省略。

为了方便展示，后续的例子将不再提供查询任务在DOS窗口、MySQL Shell窗口和MySQL Workbench窗口中的查询结果。以例6-1为例，只提供表6-1所示的查询结果。

此外，我们可以为字段名指定别名，在查询结果中，别名会代替字段名进行显示。示例如下。

SELECT cno AS 课程号, cn AS 课程号, ct AS 课时
FROM c;

查询结果如表6-2所示。

(2)用"*"表示表中的全部字段名

SELECT *
FROM c;

示例：查询讲授课程的教师的教师号。

SELECT DISTINCT tno
FROM tc;

查询结果如表6-3所示。

表6-3中查询结果去掉了重复元组。如果在查询语句中去掉关键字DISTINCT，如下所示。

SELECT tno
FROM tc;

这种情况下，查询结果将无法消除重复元组。

示例，查询前3位学生的姓名、学号和专业。

SELECT sn, sno, maj
FROM s
LIMIT 3;

查询结果如表6-4所示。

从本例可以看出，查询结果中字段名的顺序是由SELECT后面所列字段名的顺序决定的，例如，在查询结果中，字段sn在sno之前，这和学生表s中字段的排列顺序是不同的。

此外，LIMIT子句控制查询结果中元组的数量，其详细用法将在6.1.7小节进行介绍。

1.3、条件查询

条件查询需使用WHERE子句指定查询条件。查询条件中，字段名与字段名之间，或者字段名与常数之间，通常使用比较运算符连接。常用的比较运算符如表6-5所示。

1.3.1、比较大小

示例，查询成绩在90分及以上的选课信息。

SELECT *
FROM sc
WHERE score>=90;

查询结果如表6-6所示。

示例，查询职称为“教授”的教师的教师号、姓名和专业。

SELECT tno, tn, maj
FROM t
WHERE prof='教授';

查询结果如表6-7所示。

1.3.2、多重条件查询

示例，查询专业是“计算机”和“数学”的学生信息。

SELECT *
FROM s
WHERE maj='计算机' OR maj='数学';

查询结果如表6-8所示。

上述语句中的逻辑运算符"OR"，也可以用"||"代替，但一般建议使用OR。

示例，查询年龄在30~40岁的教师的教师号、姓名和职称。

SELECT tno AS 教师号, tn AS 姓名, prof AS 职称
FROM t
WHERE age>=30 AND age<=40;

查询结果如表6-9所示。

上述语句中的逻辑运算符"AND"，也可以用"&&"代替，但一般建议使用AND。

示例，查询年龄不在30~40岁的教师的教师号、姓名和职称。

SELECT tno AS 教师号, tn AS 姓名, prof AS 职称
FROM t
WHERE NOT (age>=30 and age<=40)

查询结果如表6-10所示。

上述语句等价于以下语句。

SELECT tno AS 教师号, tn AS 姓名, prof AS 职称
FROM t
WHERE age<30 or age>40;

示例，查询讲授课程号为"cl"或考"c2"且开课日期在2021年9月1日及之后的教师号、课程号和开课日期。

SELECT tno,cno,tcdate
FROM tc
WHERE (cno='c1' or cno='c2') AND tcdata>='2021-09-01';

查询结果如表6-11所示。

1.3.3、确定范围

利用"BETWEEN AND"或者"NOT BETWEEN AND"可以查询字段值属于或者不属于指定连续取值区间的元组。

示例，查询课时在30~40课时的课程的课程号、课程名和课时。

SELECT cno,cn,ct
FROM c
WHERE ct BETWEEN 30 AND 40;

查询结果如表6-12所示。

上述语句等价于以下语句。

SELECT cno,cn,ct
FROM c
WHERE ct>=30 AND ct<=40;

此外，由于SELECT后面列出的字段名集合为课程表c中的所有字段，所以，字段名集合也可以用"*"代替。

示例，查询课时不在30~40课时的课程的课程号、课程名和课时。

SELECT cno,cn,ct
FROM c
WHERE ct NOT BETWEEN 30 AND 40;

上述语句等价于以下语句。

SELECT *
FROM c
WHERE ct<30 OR ct>40;

查询结果如表6-13所示。

1.3.4、确定集合

利用"IN"或者"NOT IN"可以查询字段值属于或者不属于指定集合的元组。

示例，查询课程号为"c4"和"c6"的选课信息，包括学号、课程号和成绩。

SELECT sno, cno, score
FROM sc
WHERE cno IN ('c4','c6');

查询结果如表6-14所示。

上述语句等价于以下语句。

SELECT sno,cno,score
FROM sc
WHERE cno='c4' OR cno='c6';

此外，由于SELECT后面列出的字段名集合为选课表sc中的所有字段，所以，字段名集合也可以用"*"代替。

示例，查询除课程号"c4"和"c6"之外其他课程的选课信息，包括学号、课程号和成绩。

SELECT sno, cno, score
FROM sc
WHERE cno NOT IN ('c4','c6');

本例查询结果中不包括cno为"c4"和"c6"的选课记录，即从选课表sc中去掉例6-12的查询结果。

上述语句等价于以下语句。

SELECT sno, cno, score
FROM sc
WHERE cno<>'c4' AND cno<>'c6';

1.3.5、部分匹配查询

查询时，如果不知道完全精确的值，可以使用LIKE或NOT LIKE进行部分匹配查询（也称模糊查询）。LIKE语句的一般格式如下。

<字段名> LIKE <字符串常量>

其中，字段名必须为字符型，字符串常量中可以包含通配符。利用通配符，可以进行模糊查询。字符串常量中可以含有的通配符及其功能如下表所示。

通配符	功能	实例
%	代表0个或多个字符	‘ab%’, "ab"后可接任意字符
_(下画线)	代表一个字符	‘a_b’，"a"与"b"之间可有一个字符
[]	表示在某一范围的字符	[0-9]，0~9之间的字符
[^]	表示不在某一范围的字符	[^0-9]，不在0~9之间的字符

示例，查询课程名中包含“程序”的课程的课程号、课程名和课时。

SELECT cno AS 课程号, cn AS 课程名, ct AS 课时
FROM c
WHERE cn LIKE '%程序%';

查询结果如表6-16所示。

示例，查询课程名以“程序”开头的课程的课程号、课程名和课时。

SELECT cno AS 课程号, cn AS 课程名, ct AS 课时
FROM c
WHERE cn LIKE '程序%';

查询结果如表6-17所示。

示例，查询课程名不是以“数据”开头的课程信息。

SELECT *
FROM c
WHERE cn NOT LIKE '数据%';

查询结果如表6-18所示。

示例，查询课程名中第二个字符是“据”的课程信息。

SELECT *
FROM c
WHERE cn LIKE '_据%';

查询结果如表6-19所示。

1.3.6、空值查询

某个字段没有值称为具有空值(NULL)。通常没有为一个元组的某个字段输入值时，该字段的值就是空值。空值不同于零和空格，它不占任何存储空间。例如，某些学生选修了课程但没有参加考试，这会造成数据表中有选课记录，但没有考试成绩。考试成绩为空值与考试成绩为0分是不同的。

示例，查询没有考试成绩的选课信息，要求显示学号和课程号。

SELECT sno,cno
FROM sc
WHERE score IS NULL;

查询结果如表6-20所示。

示例，查询有考试成绩的选课信息，要求显示学号和课程号。

SELECT sno,cno
FROM sc
WHERE score IS NOT NULL;

本例的查询结果是首先对选课表sc的前两列sno和cno进行投影，之后，从中去掉例6-18的查询结果。

1.4、聚合函数查询

SQL提供了许多实用的聚合函数，增强了基本查询能力。常用的聚合函数及其功能如下表所示。

函数名称	功能
AVG	按列计算平均值
SUM	按列计算值的总和
MAX	求一列中的最大值
MIN	求一列中的最小值
COUNT	按列值统计个数

示例，查询学号为"s2"的学生的总分和平均分。

SELECT SUM(score), AVG(score)
FROM sc
WHERE sno='s2';

查询结果如表6-22所示。

从上述查询结果可以看出，在使用聚合函数进行查询时，查询结果中的字段名是聚合函数的函数名。如果要更清楚地表示查询内容的含义，可以为聚合函数指定别名。示例如下。

SELECT SUM(score) AS 总分, AVG(score) AS 平均分
FROM sc
WHERE sno='s2';

查询结果如表6-23所示。

请注意，学号为"s2"的学生共选修了4门课程（见表1-4)但是其中1门课程的成绩为空值，聚合函数SUM和AVG对其成绩进行计算时，只考虑了有效成绩，没有将空值计算在内。

示例，查询课程的最高课时、最低课时和最大课时差。

SELECT MAX(ct) AS 最高课时, MIN(ct) AS 最低课时, MAX(ct)-MIN(ct) AS 最大课时差
FROM c;

查询结果如表6-24所示。

示例，查询学号为"s1"的学生的选课门数。

SELECT sno, COUNT(cno) AS 选课门数
FROM sc
WHERE sno='s1';

查询结果如表6-25所示。

上述语句中的COUNT (cno)也可以写为COUNT (sno)、COUNT (score)或者COUNT (*)。但是，如果查询学号为"s2"或"s4"的学生的选课门数，由于其选课成绩包含空值，则不能使用COUNT (score)，因为COUNT只对有效成绩进行计数。

示例，查询学生表s中的专业数量。

SELECT COUNT(DISTINCT maj) AS 专业数量
FROM s;

查询结果如表6-26所示。

注意：上述语句中的关键字DISTINCT不能省略，它的作用是消除重复元组。

示例，查询“信息学院”的教师数量。

SELECT dept,COUNT(*) as 教师数量
FROM t
WHERE dept='信息学院';

查询结果如表6-27所示。

上述语句中的COUNT (*)用来统计元组的数量，不消除重复元组，不允许使用DISTINCT关键字。此外，上述语句中的"*"可以用教师表中的任一字段名进行替换。

1.5、分组查询

GROUP BY子句可以将查询结果按字段列或字段列的组合在行的方向上进行分组，每组在字段列或字段列的组合上具有相同的值。

示例，查询选课表sc中每门课程的课程号及其选课人数。

SELECT cno AS 课程号, COUNT(*) AS 选课人数
FROM sc
GROUP BY cno;

查询结果如表6-28所示。

示例，查询选修3门以上（含3门）课程的学生的学号和选课门数。

SELECT sno AS 学号, COUNT(*) AS 选课门数
FROM sc
GROUP BY sno
HAVING COUNT(*)>=3;

查询结果如表6~29所示。

GROUP BY子句按学号字段sno的值分组，所有具有相同学号的元组为一组，对每一组使用函数COUNT进行计算，统计出每个学生的选课门数。HAVING子句去掉不满足COUNT (*) >=3的组。

当在一个SQL查询中同时使用WHERE子句、GROUP BY子句和HAVING子句时，其顺序是WHERE、GROUP BY和HAVING。WHERE与HAVING子句的根本区别在于作用对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组；HAVING子句作用于组，选择满足条件的组，必须用在GROUP BY子句之后，但GROUP BY子句之后可以没有HAVING子句。

1.6、查询结果排序

当需要对查询结果排序时，应该使用ORDER BY子句。排序方式可以指定，DESC为降序，ASC为升序，默认为升序。此外，在一个查询任务中，如果用到ORDER BY子句，该子句一定要放在最后一行。

示例，查询学号为"s2"的学生的选课信息，要求显示学号、课程号和成绩，并且按照成绩的降序排列。

SELECT sno,cno,score
FROM sc
WHERE sno='s2'
ORDER BY score DESC;

查询结果如表6-30所示。

上述语句中，SELECT子句包含了选课表sc中的所有字段，这些字段可以整体用"*"代替。

示例，查询课程信息，并且按照课时的降序排列。

SELECT *
FROM c
ORDER BY ct DESC;

查询结果如表6-31所示。

本例的查询结果中，课程信息按照课时"ct"的降序进行排列；对于相同课时的课程，默认按照课程号"cno"的升序进行排列。

示例，查询课程信息，按照课时的降序排列，课时相同的课程再按照课程名降序排列。

SELECT *
FROM c
ORDER BY ct DESC, cn DESC;

查询结果如表6-32所示。

从表6-32可以看出，课时"ct"是主排序字段，课程名"cn"是次排序字段，首先按照课时降序排列，课时相同的课程再按照课程名降序排列。

1.7、限制查询结果数量

LIMIT子句用来限制查询结果的元组数量，其语法格式如下。

LIMIT [OFFSET,]row_count|row_count OFFSET offset; 

其中，OFFSET是非负整型常量，用于指定查询结果的第一行的偏移量，默认为0，表示查询结果的第1行。OFFSET的值为1时，表示查询结果的第2行，以此类推；row _ count是非负整型常量，用来指定查询结果的行数，如果row count的值大于实际查询结果的行数，则返回实际行数；row_count OFFSET后面的offset也是非负整型常量；row_count OFFSET offset表示查询结果从offset+1行开始，返回row _ count行。

示例，查询从第2位教师开始的3位教师的教师号、姓名和职称。

SELECT tno,tn,prof
FROM t
LIMIT 1,3;

查询结果如表6-33所示。

上述语句等价于以下语句。

SELECT tno,tn,prof
FROM t
LIMIT 3 OFFSET 1;

示例，查询选课表sc中每门课程的课程号及其选课人数，按照选课人数降序排列，并且显示前3行。

SELECT cno AS 课程号, COUNT(*) AS 选课人数
FROM sc
GROUP BY cno
ORDER BY 选课人数 DESC
LIMIT 3

查询结果如表6-34所示。

上述语句中，LIMIT中的"3"，也可以写作"0, 3"或"3 OFFSET 0"。

二、多关系数据查询

进行数据查询时，往往需要用多个表中的数据来组合、提炼出所需要的信息。如果一个查询任务需要对多个表进行操作，就称为多关系数据查询。多关系数据查询是通过各个表之间共同字段的关联性来查询数据的，这种字段称为连接字段。多关系数据查询的目的是通过加在连接字段上的条件将多个表连接起来，以便从多个表中查询数据。

2.1、多关系查询结构

表的连接方法有以下两种。

(1)表之间满足一定条件的行进行连接时，FROM子句指明进行连接的表名，WHERE子句指明连接的列名及其连接条件。语法格式如下。

SELECT [ALL|DISTINCT] [TOP N [PERCENT] [WITH TIES]] <字段名> [AS 别名1][{,<字段名> [AS 别名2]}]
FROM <表名1> [[AS] 表1别名][{,<表名2> [[AS] 表2别名,...]}]
[WHERE <检索条件>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];

(2)利用关键字JOIN进行连接。语法格式如下。

SELECT [ALL|DISTINCT] [TOP N [PERCENT] [WITH TIES]] 字段名1 [AS 别名1][,字段名2 [AS 别名2]...]
FROM 表名1 [[AS] 表1别名] [INNER|[LEFT|RIGHT|FULL|[OUTER]]|CORSS] JOIN 表名2 [[AS] 表2别名]
ON 条件;

相关说明如下。

INNER JOIN称为内连接，用于显示符合条件的记录，此为默认值。

LEFT [OUTER] JOIN称为左（外）连接，用于显示符合条件的记录以及左边表中不符合条件的记录（此时右边表记录会以NULL来显示）。

RIGHT [OUTER] JOIN称为右（外）连接，用于显示符合条件的记录以及右边表中不符合条件的记录（此时左边表记录会以NULL来显示）。

FULL [OUTER] JOIN称为全（外）连接，用于显示符合条件的记录以及左边表和右边表中不符合条件的记录（此时缺乏数据的记录会以nULL来显示）。目前MySQL暂不支持全外连接，但可通过左外连接和右外连接联合实现。

CROSS JOIN称为交叉连接，用于将一个表的每个记录和另一个表的每个记录匹配成新的记录。

当将JOIN关键词放于FROM子句中时，应有关键词ON与之对应，以表明连接的条件。

2.2、内连接查询

下面通过一些具体的例子来介绍内连接查询。

示例，查询学号为"s5"的学生的选课信息，要求列出学号、姓名和课程号。

(1)方法1

SELECT s.sno, sn, cno
FROM s,sc
WHERE s.sno='s5' AND s.sno=sc.sno;

查询结果如表6-35所示。

上述语句中的学号"sno"需要加上表名前缀，这是因为学生表s和选课表sc中都有学号sno"，必须用表名前缀来确切说明该字段属于哪个表，以避免二义性。如果字段名是唯一的，例如本例中的学生姓名"sn"和课程号"cno"，就不必加前缀。此外，上述语句中的学号"sno"的表前缀也可以写为选课表sc。

上述语句的执行过程是将学生表s中的学号"sno"和选课表sc中的学号"sno"进行等值连接，同时选取学号为"s5"的行，然后对学号"sno"、学生姓名"sn"和课程号"cno"进行投影操作，即可得到查询结果。

(2)方法2

SELECT s.sno, sn, cno
FROM S INNER JOIN sc
ON s.sno=sc.sno AND s.sno='s5';

示例，查询所有授课教师的教师号、姓名和讲授的课程名，并且按照教师号升序排列。

(1)方法1

SELECT t.tno,tn,cn
FROM t,tc,c
WHERE t.tno=tc.tno AND tc.cno=c.cno
ORDER BY tno;

查询结果如表6-36所示。

(2)方法2

SELECT t.tno,tn,cn
FROM t INNER JOIN tc INNER JOIN c
ON t.tno=tc.tno AND tc.cno=c.no
ORDER BY tno;

上述语句等价于以下语句。

SELECT t.tno,tn,cn
FROM t INNER JOIN tc ON t.tno=tc.tno
       INNER JOIN c ON tc.cno=c.cno
ORDER BY tno;

示例，查询选课人数在3人及以上的课程的课程号、课程名和选课人数。

(1)方法1

SELECT c.cno,cn,COUNT(sc.sno) AS 选课人数
FROM c,sc
WHERE c.cno=sc.cno
GROUP BY c.cno,cn
HAVING 选课人数>=3;

查询结果如表6-37所示。

上述语句中，HAVING子句中使用了聚合函数的别名“选课人数”，也可以直接使用聚合函数"COUNT (sc.sno)"。

(2)方法2

SELECT c.cno,cn,COUNT(sc.sno) AS 选课人数
FROM c INNER JOIN sc
ON c.cno=sc.cno
GROUP BY c.cno,cn
HAVING COUNT(sc.sno)>=3;

2.3、外连接查询

在内连接查询中，不满足连接条件的元组不能作为查询结果输出。例如，例6-33的查询结果只包括有授课记录的教师信息，而没有教师号为"t6"的张刚老师信息。而在外连接查询中，参与连接的表有主从之分，以主表的每行数据去匹配从表的数据列。符合连接条件的数据将直接返回到结果集中；对于那些不符合连接条件的列，将被填上NULL值后，再返回到结果集中。

外连接查询分为左外连接查询和右外连接查询两种。以主表所在的方向区分外连接查询，主表在左边，则称为左外连接查询；主表在右边，则称为右外连接查询。

示例，查询所有教师的教师号、姓名和授课程名，并且按照教师号升序排列（没有授课的教师的授课信息显示为空）。

SELECT t.tno, tn, cn
FROM t LEFT OUTER JOIN tc ON t.tno=tc.tno
       LEFT OUTER JOIN c ON tc.cno=c.cno
ORDER BY tno;

查询结果如表6-38所示。

由表6-38可以看出，与例6-33的查询结果相比，本例的查询结果包括所有的教师，没有授课的张刚老师的授课名称显示为空。

示例，查询所有学生的学号、姓名、课程号和成绩（没有选课的学生的选课信息显示为空）。

SELECT s.sno,sn,cno,score
FROM s LEFT OUTER JOIN sc
ON sc.sno=s.sno;

上述语句用了左外连接查询，其中主表是学生表s，从表是选课表sc。本例中的查询任务也可以用右外连接查询，如下所示。

SELECT s.sno,sn,cno,score
FROM sc RIGHT OUTER JOIN s
ON sc.sno=s.sno;

查询结果中，除显示选课学生的信息外，还显示未选课的学号为"s6"的学生信息，其选课课程号和成绩为空。

2.4、交叉连接查询

交叉连接查询对连接查询的表没有特殊的要求，任何表都可以进行交叉连接查询操作。

示例，对教师表和课程表进行交叉连接查询。

SELECT *
FROM t CROSS JOIN c;

上述语句是将教师表t中的每一个元组和课程表c的每一个元组匹配生成新的数据行，查询结果的行数是两个表行数的乘积，列数是两个表列数的和。

2.5、自连接查询

当一个表与其自身进行连接查询操作时，称为表的自连接查询。

示例，查询课时比“程序设计基础”高的课程的课程号、课程名和课时。

要查询的内容均在同一个课程表c中，可以为课程表c分别取两个别名，一个是x，另一个是y。将y中满足课时比“程序设计基础”高的行与x中的“程序设计基础”课程行连接起来，这实际上是同一课程表c的大于连接。

(1)方法1

SELECT x.cno AS 课程号, x.cn AS 课程名, x.ct AS 课时
FROM c AS x, c AS y
WHERE x.ct>y.ct AND y.cn='程序设计基础';

(2)方法2

SELECT x.cno AS, x.cn AS, x.ct AS
FROM c AS x INNER JOIN c AS y
ON x.ct>y.ct AND y.cn='程序设计基础';

查询结果如表6-39所示。

示例，查询与学生“王彤”专业相同的学生的学号和姓名。

本例的查询任务实际上是同一学生表s的等值连接。

(1)方法1

SELECT x.sno, x.sn
FROM s AS x, s AS y
WHERE x.maj=y.maj AND y.sn='王彤';

(2)方法2

SELECT x.sno, x.sn
FROM s AS x INNER JOIN s as y
ON x.maj=y.maj AND y.sn="王彤";

三、子查询

WHERE子句中包含一个形如SELECT……FROM……WHERE的查询块，此查询块称为子查询或嵌套查询，包含子查询的语句称为父查询或外部查询。嵌套查询可以将一系列简单查询构成复杂查询，增强查询能力。

3.1、普通子查询

普通子查询的执行顺序：首先执行子查询，然后把子查询的结果代入父查询的查询条件中。普通子查询只执行一次，而父查询所涉及的所有记录行都与其查询结果进行比较以确定查询结果集合。

3.1.1、返回一个值的普通子查询

当子查询的返回值只有一个时，可以使用比较运算符将父查询和子查询连接起来。

示例，查询比学生“赵琳琳”年龄大的学生的学号、姓名和年龄。

SELECT sno,sn,age
FROM s
WHERE age>(SELECT age 
           FROM s
           WHERE sn='赵琳琳');

查询结果如表6-41所示。

上述语句相当于将查询分成两个查询块来执行。首先，执行以下子查询。

SELECT age
from s
WHERE sn='赵琳琳';

子查询向父查询返回一个值，即学生“赵琳琳”的年龄"19"，此值被代入父查询的查询条件中。

其次，执行父查询，查询所有年龄大于"19"的学生的学号、姓名和年龄。

本例的查询任务也可以用自连接查询实现，语句如下。

SELECT x.sno,x.sn,x.age
FROM s AS x, s AS y
WHERE x.age>y.age AND y.sn='赵琳琳';

示例，查询与教师“顾伟”职称不同的教师的教师号、姓名和职称。

SELECT tno,tn,prof
FROM t
WHERE prof<>(SELECT prof
             FROM t
             WHERE tn='顾伟');

查询结果如表6-42所示。

本例的查询任务也可以用自连接查询实现，语句如下。

SELECT x.tno,x.tn,x.prof
FROM t AS x, t AS y
WHERE x.prof<>y.prof AND y.tn='顾伟';

例6-40中的父查询和子查询都源自于同一个基本表，即学生表s。同理，例6-41中的父查询和子查询都源自于教师表t。因此，以上两例可以使用子查询，也可以使用自连接查询。当然，父查询和子查询也可以源于不同的基本表，如例6-42所示。

示例，查询讲授“程序设计基础”课程的教师的教师号，以及该课程的课程号和开课日期。

SELECT tno,cno,tcdate
FROM tc
WHERE cno=(SELECT cno
           FROM c
           WHERE cn='程序设计基础');

查询结果如表6-43所示。

本例的查询任务也可以使用内连接查询实现，语句如下。

SELECT tno,tc,cno,tcdate
FROM c,tc
WHERE c.cno=tc.cno AND c.cn='程序设计基础';

或者

SELECT tno,tc.cno,tcdate
FROM c INNER JOIN tc
ON c.cno=tc.cno AND c.cn='程序设计基础';

3.1.2、返回一组值的普通子查询

如果子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以在比较运算符和子查询之间插入ANY、IN或ALL。

(1)使用ANY

示例，查询学号为"s2"的学生选修的课程的课程号、课程名和课时。

SELECT cno,cn,ct
FROM c
WHERE cno=ANY (SELECT cno
               FROM sc
               WHERE sno='s2');

查询结果如表6~44所示。

上述语句的执行过程：首先，执行子查询，查询学号为"s2"的学生选修的课程的课程号，返回的课程号为一组值构成的集合{c4, c5, c6, c7} ；其次，执行父查询，其中ANY的含义为任意一个。

本例的查询任务也可以使用内连接查询实现，语句如下。

SELECT c.cno,cn,ct
FROM c,sc
WHERE c.cno=sc.cno AND sno='s2';

或者

SELECT c.cno,cn,ct
FROM c INNER JOIN sc
ON c.cno=sc.cno AND sno='s2';

示例，查询其他专业中比“计算机”专业某一教师工资高的教师的教师号、姓名、专业和工资。

SELECT tno,tn,maj,sal
FROM t
WHERE (sal>ANY (SELECT sal
                FROM t
                WHERE maj='计算机'))
      AND maj<>'计算机';

查询结果如表6-45所示。

上述语句的执行过程：首先，执行子查询，返回“计算机”专业中所有教师的工资集合{3610.5, 3145} ；其次，执行父查询，查询所有不是“计算机”专业且工资高于3145元的教师的教教师号、姓名、专业和工资。

本例的查询任务也可以用以下语句实现。

SELECT tno,tn,maj,sal
FROM t
WHERE (sal>(SELECT MIN(sal)
            FROM t
            WHERE maj='计算机'))
      AND maj<>'计算机';

上述语句的执行过程：首先，执行子查询，利用聚合函数MIN找到“计算机”专业中所有教师的最低工资3145元；其次，执行父查询，查询所有不是“计算机”专业且工资高于3145元的教师的教师号、姓名、专业和工资。

此外，由于本例使用的普通子查询中，父查询和子查询源自于同一个基本表，即教师表t,所以本例还可以使用自连接查询，语句如下。

SELECT DISTINCT x.tno,x.tn,x.maj,x.sal
FROM t AS x, t AS y
WHERE x.sal>y.sal AND x.maj<>'计算机' AND y.maj='计算机';

(2)使用IN

可以使用IN代替"=ANY"。

示例，查询学号为“s2”的学生选修的课程的课程号、课程名和课时（使用IN）。

SELECT con,cn,ct
FROM c
WHERE cno IN (SELECT cno
              FROM sc
              WHERE sno='s2');

本例的查询任务和例6-43相同，例6-43中的父查询与子查询之间使用了“=ANY”，本例使用“IN”代替“=ANY”，作用是相同的。

示例，查询学生的学号和姓名，查询条件是学生选修了课程号为“c1”的课程（使用IN）。

SELECT sno,sn
FROM s
WHERE sno IN (SELECT sno
              FROM sc
              WHERE cno='c1');

查询结果如表6-46所示。

上述语句中的"IN"也可以用"=ANY"代替。此外，本例的查询任务也可以使用内连接查询实现，这里不再赘述其具体语句。

(3)使用ALL

示例，查询其他专业中比“计算机”专业所有教师工资高的教师的教师号、姓名、专业和工资。

SELECT tno,tn,maj,sal
FROM t
WHERE (sal>ALL (SELECT sal
                FROM t
                WHERE maj='计算机'))
      AND maj<>'计算机';

查询结果如表6-47所示。

上述语句的执行过程：首先，执行子查询，返回“计算机”专业中所有教师的工资集合{3610.5, 3145} ；其次，执行父查询，查询所有不是“计算机”专业且工资高于3610.5元的教师的教师号、姓名、专业和工资。

本例的查询任务也可以用以下语句实现。

SELECT tno,tn,maj,sal
FROM t
WHERE (sal>(SELECT MAX(sal)
            FROM t
            WHERE maj='计算机'))
      AND maj<>'计算机';

上述语句的执行过程：首先，执行子查询，利用聚合函数MAX找到“计算机”专业中所有教师的最高工资3610.5元；其次，执行父查询，查询所有不是“计算机”专业且工资高于3610.5元的教师的教师号、姓名、专业和工资。．

3.1.3、用于数据操纵的普通查询

示例，求出各学院教师的平均工资，把结果存放在新表avgsal中。

首先，建立新表avgsal，用来存放学院名称和各个学院的平均工资。

USE teaching;
DROP TABLE IF EXISTS 'avgsal';

CREATE TABLE avgsal
  (department VARCHAR(20), 
   average SMALLINT);

然后，利用子查询求出教师表t中各学院的平均工资，把结果存放在新表avgsal中。

INSERT INTO avgsal
SELECT dept,AVG(sal)
FROM t
GROUP BY dept;

执行上述语句之后，可以通过"SELECT * FROM avgsal;"查看新表中的数据。

示例，把教师号为"t1"的教师讲授的课程的课时增加16学时。

UPDATE c
SET ct=ct+16
wHERE cno IN (SELECT cno
              FROM tc
              WHERE tno='t1');

子查询的作用是得到教师号为"t1"的教师讲授的课程的课程号"c1"和"c2"。

执行上述语句之后，使用"SELECT * FROM c;"，即可看到教师号为"t1"的教师讲授的课程的学时在原来学时的基础上增加了16学时。

示例，把所有教师的工资提高到平均工资的1.2倍。

UPDATE t
SET sal=(SELECT 1.2*AVG(sal)
         FROM t);

子查询的作用是得到所有教师的平均工资的1.2倍。

执行上述语句之后，使用"SELECT*FROMt;"，即可看到所有教师的新工资情况。

3.2、相关子查询

在6.3.1小节的普通子查询中，子查询的查询条件不涉及父查询中基本表的属性。但是，有些查询任务中，子查询的查询条件需要引用父查询表中的属性值，这类查询称为相关子查询。

相关子查询的执行顺序：首先，选取父查询表中的第一行记录，子查询利用此行中相关的属性值在子查询涉及的基本表中进行查询；然后，父查询根据子查询返回的结果判断父查询表中的此行是否满足查询条件，如果满足条件，则把该行放入父查询的查询结果集合中，重复执行这一过程，直到处理完父查询表中的每一行数据。

由此可以看出，相关子查询的执行次数是由父查询表的行数决定的。

示例，查询学生的学号和姓名，查询条件是学生选修了课程号为"c1"的课程（使用相关子查询）。

本例的查询任务与例6-46相同，例6-46使用了普通子查询，本例使用相关子查询，语句如下。

SELECT sno,sn
FROM s
WHERE 'c1' IN (SELECT cno
               FROM sc
               WHERE sno=s.sno);

上述语句中"IN"的含义相当于子查询结果中的任何一个值，也可使用"=ANY"代替"IN"。

本例的相关子查询中，子查询的WHERE子句中用到了父查询表（即学生表s)中的属性值。对于学生表s中的每一行（即每个学生记录），都要执行一次子查询，以确定该学生是否选修课程号为"c1"的课程，当"c1"课程是该学生选修的课程时，该学生会被选取到父查询的查询结果中。

示例，查询学生的学号和姓名，查询条件是学生没有选修课程号为"c1"的课程（使用相关子查询）。

SELECT sno,sn
FROM s
WHERE 'c1' NOT IN (SELECT cno
                   FROM sc
                   WHERE sno=s.sno);

查询结果如表6-48所示。

上述语句中"NOT IN"的含义为不等于子查询结果中的任何一个值，也可使用"ALL"代替"NOT IN"。

本例的相关子查询中，子查询的WHERE子句中用到了父查询表，即学生表s中的属性值。对于学生表s中的每一行（即每个学生记录），都要执行一次子查询，以确定该学生是否选修课程号为"c1"的课程，当"c1"课程不是该学生选修的课程时，该学生会被选取到父查询的查询结果中。

本例也可以使用普通子查询实现，语句如下。

SELECT sno,sn
FROM s
WHERE sno NOT IN (SELECT sno
                  FROM sc
                  WHERE cno='c1');

此外，使用EXISTS也可以进行相关子查询。EXISTS是表示存在的量词，带有EXISTS的子查询不返回任何实际数据，它只得到逻辑值“真”或“假”。当子查询的查询结果集合为非空时，外层的WHERE子句返回真值，否则返回假值。NOT EXISTS与此相反。

示例，查询学生的学号和姓名，查询条件是学生选修了课程号为"c1"的课程（使用EXISTS)。

本例的查询任务与例6-51相同，查询结果也相同，但是本例使用EXISTS进行相关子查询，语句如下。

SELECT sno,sn
FROM s
WHERE EXISTS (SELECT *
              FROM sc
              WHERE sno=s.sno AND cno='c1');

上述语句的执行过程是，对于父查询中的每一位学生，在子查询中查询其是否选修了课程号为"c1"的课程，如果有选课记录，说明子查询的结果集合为非空，则父查询中WHERE子句中的EXISTS返回逻辑值“真”，从而该学生的信息会被选取到父查询的结果集合中。对父查询表s中的每一位学生重复上述过程，即可完成查询任务。

示例，查询学生的学号和姓名，查询条件是学生没有选修课程号为"c1"的课程（使用NOT EXISTS)。

本例的查询任务与例6-52相同，查询结果也相同，但是本例使用"NOT EXISTS"进行相关子查询，语句如下。

SELECT sno,sn
FROM s
WHERE NOT EXISTS (SELECT *
                  FROM c
                  WHERE sno=s.sno AND cno='c1');

上述语句的执行过程是，对于父查询中的每一位学生，在子查询中查询其是否选修了课程号为"c1"的课程，如果没有选课记录，说明子查询的结果集合为空，则父查询中WHERE子句中的NOT EXISTS返回逻辑值“真”，从而该学生的信息会被选取到父查询的结果集合中。对父查询表s中的每一位学生重复上述过程，即可完成查询任务。

示例，查询教师号为"t2"的教师讲授的课程的课程号、课程名和课时（使用EXISTS)。

SELECT cno,cn,ct
FROM c
WHERE EXISTS (SELECT *
              FROM tc
              WHERE cno=c.cno AND tno='t2');

查询结果如表6-49所示。

上述语句等价于以下相关子查询。

SELECT cno,cn,ct
FROM c
WHERE 't1' IN (SELECT tno
               FROM tc
               WHERE cno=c.cno);

本例的查询任务也可以使用普通子查询实现，语句如下。

SELECT cno,cn,ct
FROM c
WHERE cno IN (SELECT cno
              FROM tc
              WHERE tno='t2');

本例的查询任务还可以使用内连接查询实现，语句如下。

SELECT c.cno,cn,ct
FROM c,tc
WHERE c.cno=tc.cno AND tno='t2';

或者

SELECT c.cno,cn,ct
FROM c INNER JOIN tc
ON c.cno=tc.cno AND tno='t2';

由此可见，对于同样的查询任务，可以从不同角度考虑问题，从而使用不同的查询方法进行实现。在实际查询过程中，读者可以根据需要任意选用。

四、集合运算查询

集合运算查询是使用UNION关键字将来自不同查询的数据组合起来，形成一个具有综合信息的查询结果。使用UNION时，系统会自动将重复的数据行剔除。必须注意的是，参加集合运算查询的各子查询，其查询结果的结构应该相同，即各子查询的查询结果中数据的数目和对应的数据类型都必须相同。

示例，查询“计算机”专业的学生信息，再查询“数学”专业的学生信息，将两个查询结果合并成一个结果集。

SELECT *
FROM s
WHERE maj='计算机'
UNION
SELECT *
FROM s
WHERE maj='数学';

查询结果如表6-50所示。

上述语句等价于以下语句。

SELECT * FROM s
WHERE maj='计算机' or maj='数学';

示例，查询课程号为"c1"的课程的总分和平均分，再查询课程号为"c2"的课程的总分和平均分，将两个查询结果合并成一个结果集。

SELECT cno AS 课程号, SUM(score) AS 总分, AVG(score) AS 平均分
FROM sc
WHERE cno='c1'
GROUP BY cno
UNION
SELECT cno AS 课程号, SUM(score) AS 总分, AVG(score) AS 平均分
FROM sc
WHERE cno='c2';
GROUP BY cno;

查询结果如表6-51所示。

上述语句等价于以下语句。

SELECT cno AS 课程号, SUM(score) AS 总分, AVG(score) AS 平均分
FROM sc
WHERE cno='c1' OR cno='c2'
GROUP BY cno;

五、小结

本章详细介绍了数据表中的数据查询操作，包括单关系数据查询、多关系数据查询、子查询和集合运算查询。通过丰富的查询实例，本章详细讲解了各种查询中SQL语句的使用方法和语法要素。本章的重点是聚合函数查询、分组查询、连接查询和子查询。此外，本章通过实例讲解了同一查询任务会有多种查询方法，读者学习之后可以根据实际情况选择使用。

六、参考

《数据库原理及应用教程（MySQL版）》

《数据库系统原理 自考04735》