SQL语句 之 SELECT用法一锅炖_sql select用法

一般格式：

	SELECT [ ALL | DISTINCT ] <目标列表达式> [,<目标列表达式>] ...
	FROM <表名或视图名> [,<表名或视图名> … ] | (<SELECT语句>) [AS] <别名>
	[ WHERE <条件表达式> ]
	[ GROUP BY <列名1>  [ HAVING <条件表达式>] ] 
	[ ORDER BY <列名2> [ ASC | DESC ] ] ;
1
2
3
4
5

• 整个 SELECT 语句的含义是，根据 WHERE 子句的条件表达式从 FROM 子句指定的基本表、视图或派生表中找出满足条件的元组，再按 SELECT 子句中的目标列表达式选出元组中的属性值形成结果表。
• 如果有 GROUPBY 子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。如果GROUP BY子句带HAVING短语，则只有满足指定条件的组才予以输出。
• 如果有ORDER BY子句，则结果表还要按<列名2>的值的升序或降序排序。
• SELECT语句既可以完成简单的单表查询，也可以完成复杂的连接查询和嵌套查询。

总结：

1.取别名 —— AS

	SELECT  <列名> [AS] <别名>
1

例如：将表达式 2020-Sage 取别名为 BIRTHDAY

	SELECT  2020-Sage BIRTHDAY
1

• AS可有可无；
• 在原名后面打个空格再加上别名即可；
• 相关例子 5

2.消除重复 —— DISTINCT

	SELECT DISTINCT <列名>
1

例如：消除在查询时候重复出现的 Sno

	SELECT DISTINCT Sno
1

• 在列名的前面加上一个 DISTINCT 将相同重复的行消除掉。
• 一般不加 DISTINCT 的话是默认为 ALL
• 相关例子 6

3.确定数据范围 —— (NOT) BETWEEN … AND …

	BETWEEN “A” AND “B”   		--其范围就是[ A, B ];
	NOT BETWEEN “A” AND “B”		--其范围就是( -∞, A ) ∩ ( B, +∞ );
1
2

比如： 查询年龄在20~23岁之内与之外

	WHERE Sage BETWEEN 20 AND 23;			--年龄在20~23之间
	WHERE Sage NOT BETWEEN 20 AND 23;		--年龄不在20~23之间
1
2

• 相关例子10

4.确认集合查询 —— IN

• 谓词 IN 可以用来查找属性值属于指定集合的元组。

比如：查询数学系(MA)和信息系(IS)学生

	WHERE Sdept IN ('MA', 'IS')
1

• 相关例子 12

5.字符匹配 —— LIKE & ESCAPE

	[NOT] LIKE '<匹配串>' [ESCAPE '<转码字符>']
1

其含义是查找指定的属性列值与 <匹配串> 相匹配的元组。<匹配串> 可以是一个完整的字符串，也可以含有通配符%和 _ 。 其中:

• % (百分号)代表任意长度(长度可以为0)的字符串。
  • 例如 a%b 表示以 a 开头，以 b 结尾的任意长度的字符串。
  • 如acb、addgb、 ab 等都满足该匹配串。
• _ (下横线)代表任意单个字符。
  • 例如a_b表示以a开头，以b结尾的长度为3的任意字符串。
  • 如acb、afb 等都满足该匹配串。

比如：查询以“DB_”开头，且倒数第二个字符为“设”的课程的详细情况。

	WHERE Cname LIKE 'DB\_%设_' ESCAPE'\';		--“设”后面只有一个 _ (下横线);
1

• 注意：数据库字符集为ASCII时一个汉字需要两个_ ；当字符集为GBK时只需要一个_ 。

• 如果用户要查询的字符串本身就含有通配符 % 或 _ (下横线)，这时就要使用 ESCAPE ‘<换码字符>’ 短语对通配符进行转义了。

• 相关例子 14~20 即 七、字符匹配

6.多重查询 —— OR & AND

• 逻辑运算符 AND 和 OR 可用来连接多个查询条件。
• AND 的优先级高于 OR ，但用户可以用括号改变优先级。
• IN 谓词实际上是多个 OR 运算符的所缩写；

比如：查询计算机科学系年龄在21岁以下的学生姓名。

	WHERE Sdept = 'CS' AND Sage < 21
1

• 相关例子：23~24

7.排序显示 —— ORDER BY

• 用户可以用 ORDER BY 子句对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列，默认值为升序。

比如：查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

	ORDER BY Sdept, Sage DESC;		-- Sdept 升序；  Sage 降序
1

• 相关例子：25~26

8.聚集函数 —— COUNT、SUM、AVG、MAX、MIN

COUNT(*)								--统计元组个数
COUNT( [DISTINCT | ALL] <列名>)			--统计一列中值的个数
SUM( [DISTINCT | ALL] <列名>)			--计算一-列值的总和(此列必须是数值型)
AVG( [DISTINCT | ALL] <列名>)			--计算一-列值的平均值(此列必须是数值型)
MAX( [DISTINCT | ALL] <列名>)			--求一列值中的最大值
MIN( [DISTINCT | ALL] <列名>)			--求一列值中的最小值
1
2
3
4
5
6

• 如果指定 DISTINCT 短语，则表示在计算时要取消指定列中的重复值。
• 如果不指定 DISTINCT 短语或指定 ALL 短语(ALL 为默认值)，则表示不取消重复值。
• 相关例子：第十一大点的27~34

9.连接查询 —— 将多个表左右连接起来

• 连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为

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

• 其中比较运算符主要有 =、>、<、>= 、<= 、!= (或<>) 等。
• 此外连接谓词还可以使用下面形式:

	[<表名1>.] <列名1> BETWEEN [<表名2>.] <列名2> AND [<表名2>.] <列名3>
1

• 当连接运算符为 = 时，称为等值连接。使用其他运算符称为非等值连接。
• 连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但名字不必相同。
• 相关例子：第十二大点连接查询

10.嵌套查询 —— 在WHERE中嵌套一个完整的子查询

• 其中包括

  • 带有 ANY (SOME) 或 ALL 谓词的子查询
  • 带有 IN 谓词的子查询
  • 带有EXISTS谓词的子查询

• 子查询返回单值时可以用比较运算符，但返回多值时要用 ANY (有的系统用SOME) 或 ALL 谓词修饰符。而使用 ANY 或 ALL 谓词时则必须同时使用比较运算符。其语义如下所示:

	> ANY				大于子查询结果中的某个值
	> ALL				大于子查询结果中的所有值
	< ANY				小于子查询结果中的某个值
	< ALL				小于子查询结果中的所有值
	>= ANY				大于等于子查询结果中的某个值
	>= ALL				大于等于子查询结果中的所有值
	<= ANY				小于等于子查询结果中的某个值
	<= ALL				小于等于子查询结果中的所有值
	= ANY				等于子查询结果中的某个值
	= ALL				等于子查询结果中的所有值(通常没有实际意义)
	!=(或<>) ANY 		不等于子查询结果中的某个值
	!=(或<>) ALL		不等于子查询结果中的任何一个值
1
2
3
4
5
6
7
8
9
10
11
12

• EXISTS 代表存在量词 ∃ 。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值 “true” 或逻辑假值 “false”。
• 可以利用 EXISTS 来判断 x∈S、S⊆R、S=R、S∩R 非空等是否成立。

ANY(或SOME)、ALL谓词与聚集函数、IN谓词的等价转换关系

• 相关例子：第十三大点

11.集合查询 —— UNION、INTERSECT、EXCEPT

• SELECT 语句的查询结果是元组的集合，所以多个 SELECT 语句的结果可进行集合操作。集合操作主要包括并操作 UNION、交操作 INTERSECT 和差操作 EXCEPT。
• 注意：参加集合操作的各查询结果的列数必须相同；对应的属于类型也必须相同。

	SELECT ……		--第一个查询
	……
	WHERE ……
	UNION			--可以换成 INTERSECT、EXCEPT
	SELECT ……		--第二个查询
	……
	WHERE ……
1
2
3
4
5
6
7

• 集合查询也可以理解为将多个列相同的表进行上下连接

举例展示：

三个关系表

关系 Student：

关系 Course：

关系 Report：

不要吐槽为何都要考计算机相关知识，知识举个栗子而已，切勿当真。

一、查询指定列

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

	SELECT Sno, Sname
	FROM Student;
1
2

• 将要查询的列名放在 select 后面即可，中间用英式逗号隔开；

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

	SELECT Sname, Sno, Sdept
	FROM Student;
1
2

• <目标列表达式>中各个列的先后顺序可以与表中的顺序不一致。用户可以根据应用的需要改变列的显示顺序。
• 本例中先列出姓名，再列出学号和所在系。

3. 查询全体学生的详细记录

	SELECT *
	FROM Student;
1
2

等价于

	SELECT Sno, Sname ,Ssex, Sage, Sdept
	FROM Student;
1
2

• 一般都会使用第一种方式在显示全表；

二、查询经过计算的值

4. 查询全体学生的姓名及其出生年份

	SELECT Sname, 2020-Sage 			/* 查询结果的第2列是一个算术表达式*/
	FROM Student;
1
2

• 查询结果中第2列不是列名而是一个计算表达式，是用当时的年份(假设为2020年)减去学生的年龄。这样所得的即是学生的出生年份。
• <目标列表达式>不仅可以是算术表达式，还可以是字符串常量、函数等。

5. 查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示系名。

	SELECT Sname , 'Year of Birth: ', 2020-Sage , LOWER(Sdept)
	FROM Student;
1
2

• 用户可以通过指定别名来改变查询结果的列标题，这对于含算术表达式、常量、函数名的目标列表达式尤为有用。
• 例如对于例5可以定义如下列别名:

	SELECT Sname NAME, 'Year of Birth:' BIRTH, 2020 - Sage BIRTHDAY, LOWER(Sdept) DEPARTMENT
	FROM Student;
1
2

• 在原名后面加上一个空格再加上别名，即 原名+“空格”+别名；

三、消除取值重复的行

DISTINCT

• 消除取值重复的行
• 两个本来并不完全相同的元组在投影到指定的某些列上后，可能会变成相同的行。可以用 DISTINCT 消除它们。

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

未去重版：

	SELECT Sno
	FROM Report;
1
2

等价于

	SELECT ALL Sno
	FROM Report;
1
2

• 如果没有指定DISTINCT关键词，则默认为ALL,即保留结果表中取值重复的行。

去重版：

	SELECT DISTINCT Sno
	FROM Report;
1
2

• 该查询结果里包含了许多重复的行。如想去掉结果表中的重复行，必须指定DISTINCT

←未去重版 去重版→

7. 查询计算机科学系全体学生的名单。

	SELECT Sname
	FROM Student
	WHERE Sdept = 'CS';
1
2
3

• 关系数据库管理系统执行该查询的一种可能过程是：对 Student 表进行全表扫描，取出一个元组，检查该元组在Sdept列的值是否等于"CS’，如果相等，则取出 Sname 列的值形成一个新的元组输出；否则跳过该元组，取下一个元组。重复该过程，直到处理完Student表的所有元组。

四、比较大小

• 用于进行比较的运算符一般包括 = (等于)，> (大于)，< (小于)， >= (大于等于)，<= (小于等于)， != 或 <> (不等于)， !> (不大于)， !< (不小于)。

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

	SELECT Sname,Sage
	FROM Student
	WHERE Sage < 20;
1
2
3

9. 查询考试成绩不及格的学生的学号。

	SELECT DISTINCT Sno
	FROM Report
	WHERE Grade < 60;
1
2
3

• 这里使用了DISTINCT短语，当一个学生有多门课程不及格，他的学号也只列一次。

五、确定范围

BETWEEN…AND…

谓词BETWEEN…AND…和 NOT BETWEEN…AND…可以用来查找属性值在( 或不在)指定范围内的元组，其中BETWEEN后是范围的下限(即低值)，AND后是范围的上限(即高值)，即 从…到… ，其范围是包括了低值和高值。

• BETWEEN “A” AND “B”，其范围就是[ A, B ];
• NOT BETWEEN “A” AND “B”，其范围就是( -∞, A ) ∩ ( B, +∞ );

10. 查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。

	SELECT Sname,Sdept,Sage
	FROM Student
	WHERE Sage BETWEEN 20 AND 23;
1
2
3

11. 查询年龄不在20~23岁之间的学生姓名、系别和年龄。

	SELECT Sname,Sdept,Sage
	FROM Student
	WHERE Sage NOT BETWEEN 20 AND 23;
1
2
3

六、确定集合

IN

• 谓词 IN 可以用来查找属性值属于指定集合的元组。

12. 查询数学系(MA)和信息系(IS)学生的姓名和性别。

	SELECT Sname,Ssex
	FROM Student
	WHERE Sdept IN ('MA', 'IS');
1
2
3

13. 查询既不是数学系，也不是信息系的学生的姓名和性别。

	SELECT Sname, Ssex
	FROM Student
	WHERE Sdept NOT IN ('MA', 'IS');
1
2
3

七、字符匹配

LIKE

谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下:

	 [NOT] LIKE '<匹配串>'  [ESCAPE '<换码字符>' ]
1

其含义是查找指定的属性列值与 <匹配串> 相匹配的元组。<匹配串> 可以是一个完整的字符串，也可以含有通配符%和 _ 。 其中:

• % (百分号)代表任意长度(长度可以为0)的字符串。
  • 例如 a%b 表示以 a 开头，以 b 结尾的任意长度的字符串。
  • 如acb、addgb、 ab 等都满足该匹配串。
• _ (下横线)代表任意单个字符。
  • 例如a_b表示以a开头，以b结尾的长度为3的任意字符串。
  • 如acb、afb 等都满足该匹配串。

14. 查询学号S02的学生的详细情况。

	SELECT *
	FROM Student
	WHERE Sno LIKE 'S02';
1
2
3

等价于

	SELECT * 
	FROM Student
	WHERE Sno = 'S02';
1
2
3

• 如果LIKE后面的匹配串中不含通配符，则可以用 = (等于) 运算符取代 LIKE 谓词，用 != 或 <> (不等于)运算符取代 NOT LIKE 谓词。

15. 查询所有姓刘的学生的姓名、学号和性别。

	SELECT Sname, Sno, Ssex
	FROM Student
	WHERE Sname LIKE '刘%';
1
2
3

16. 查询姓“王”且全名为两个汉字的学生的姓名。

	SELECT Sname
	FROM Student
	WHERE Sname LIKE '王_';	--只有一个_(下横线)
1
2
3

• 注意：数据库字符集为ASCII时一个汉字需要两个_ ；当字符集为GBK时只需要一个_ 。

17. 查询名字中第二个字为“五”的学生的姓名和学号。

	SELECT  Sname, Sno
	FROM Student
	WHERE Sname LIKE '_五%';	--只有一个_(下横线)
1
2
3

18. 查询所有不姓刘的学生的姓名、学号和性别。

	SELECT Sname, Sno, Ssex
	FROM  Student
	WHERE Sname NOT LIKE '刘%';
1
2
3

ESCAP

• 如果用户要查询的字符串本身就含有通配符 % 或 _ (下横线)，这时就要使用 ESCAPE ‘<换码字符>’ 短语对通配符进行转义了。

19. 查询 DB_设计 课程的课程号和学分。

	SELECT Cno, Ccredits
	FROM Course
	WHERE Cname LIKE 'DB\_设计' ESCAPE'\';
1
2
3

• ESCAPE’’ 表示 “ \ ” 为换码字符。这样匹配串中紧跟在 “ \ ” 后面的字符 “ _ ” 不再具有通配符的含义，转义为普通的 “ _ ” 字符。

20. 查询以“DB_”开头，且倒数第二个字符为“设”的课程的详细情况。

	SELECT *
	FROM Course
	WHERE Cname LIKE 'DB\_%设_' ESCAPE'\';		--“设”后面只有一个 _ (下横线);
1
2
3

• 这里的匹配串为 “DB_%设_”。第一个 _ 前面有换码字符 \ ，所以它被转义为普通的 _ 字符。
• 而 “设” 后面的一个_ 的前面没有换码字符 \ ，所以它们仍作为通配符。

八、涉及空值的查询

21. 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

	SELECT Sno, Cno
	FROM Report
	WHERE Grade IS NULL;	--分数Grade是空值null
1
2
3

• 注意这里的 “IS" 不能用等号( = )代替。

22. 查所有有成绩的学生学号和课程号。

	SELECT Sno, Cno
	FROM Report
	WHERE Grade IS NOT NULL;
1
2
3

九、多重条件查询

AND OR

• 逻辑运算符 AND 和 OR 可用来连接多个查询条件。
• AND 的优先级高于 OR ，但用户可以用括号改变优先级。

23. 查询计算机科学系年龄在21岁以下的学生姓名。

	SELECT Sname
	FROM Student
	WHERE Sdept = 'CS' AND Sage < 21;
1
2
3

24. 查询数学系(MA)和信息系(IS)学生的姓名和性别。

在例12中的 IN 谓词实际上是多个OR运算符的缩写，因此该例中的查询也可以用OR运算符写成如下等价形式:

	SELECT Sname, Ssex
	FROM Student
	WHERE Sdept = 'MA' OR Sdept = 'IS';
1
2
3

等价于：

	SELECT Sname,Ssex
	FROM Student
	WHERE Sdept IN ('MA', 'IS');
1
2
3

十、排序显示

ORDER BY

• 用户可以用 ORDER BY 子句对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列，默认值为升序。

25. 查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。

	SELECT Sno, Grade
	FROM Report
	WHERE Cno = 'C03'
	ORDER BY Grade DESC;
1
2
3
4

• 对于空值，排序时显示的次序由具体系统实现来决定。
• 例如按升序排，含空值的元组最后显示；
• 按降序排，空值的元组则最先显示。
• 各个系统的实现可以不同，只要保持一致就行。

26. 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

	SELECT *
	FROM Student
	ORDER BY Sdept, Sage DESC;
1
2
3

十一、聚集函数

为了进一步方便用户，增强检索功能，SQL提供了许多聚集函数，主要有:

COUNT(*)								--统计元组个数
COUNT( [DISTINCT | ALL] <列名>)			--统计一列中值的个数
SUM( [DISTINCT | ALL] <列名>)			--计算一-列值的总和(此列必须是数值型)
AVG( [DISTINCT | ALL] <列名>)			--计算一-列值的平均值(此列必须是数值型)
MAX( [DISTINCT | ALL] <列名>)			--求一列值中的最大值
MIN( [DISTINCT | ALL] <列名>)			--求一列值中的最小值
1
2
3
4
5
6

• 如果指定 DISTINCT 短语，则表示在计算时要取消指定列中的重复值。
• 如果不指定 DISTINCT 短语或指定 ALL 短语(ALL 为默认值)，则表示不取消重复值。

COUNT

27. 查询学生总人数

	SELECT COUNT(*)
	FROM Student;
1
2

28. 查询选修了课程的学生人数

	SELECT COUNT(DISTINCT Sno)
	FROM Report;
1
2

• 学生每选修一门课，在 Report 中都有一条相应的记录。一个学生要选修多门课程，为避免重复计算学生人数，必须在COUNT函数中用DISTINCT短语。

AVG

29. 计算选修1号课程的学生平均成绩。

	SELECT AVG(Grade)
	FROM Report
	WHERE Cno = 'C01';
1
2
3

MAX

30. 查询选修1号课程的学生最高分数。

	SELECT MAX(Grade)
	FROM Report
	WHERE Cno = 'C01';
1
2
3

SUM

31. 查询学生S01选修课程的总学分数。

	SELECT SUM(Ccredit)
	FROM Report, Course
	WHERE Sno = 'S01' AND Report.Cno = Course.Cno;
1
2
3

• 当聚集函数遇到空值时，除COUNT ( * ) 外，都跳过空值而只处理非空值。
• COUNT ( * )是对元组进行计数，某个元组的一个或部分列取空值不影响COUNT的统计结果。
• 注意，WHERE 子句中是不能用聚集函数作为条件表达式的。
• 聚集函数只能用于 SELECT 子句和 GROUP BY 中的 HAVING 子句。

GROUP BY

• GROUP BY 子句将查询结果按某列或多列的值分组，值相等的为一组。
• 对查询结果分组的目的是为了细化聚集函数的作用对象。
• 如果未对查询结果分组，聚集函数将作用于整个查询结果，如前面的例27~31。 分组后聚集函数将作用于每一个组，即每一组都有一个函数值。

32. 求各个课程号及相应的选课人数。

	SELECT Cno, COUNT(Sno)
	FROM Report
	GROUP BY Cno
1
2
3

• 该语句对查询结果按 Cno 的值分组，所有具有相同 Cno 值的元组为一组，然后对每一组作用聚集函数 COUNT 进行计算，以求得该组的学生人数。

HAVING

33. 查询选修了两门以上课程的学生学号。

	SELECT Sno
	FROM Report
	GROUP BY Sno
	HAVING COUNT(*) > 2;
1
2
3
4

• 这里先用 GROUP BY子句按 Sno 进行分组，再用聚集函数 COUNT 对每一组计数 : HAVING 短语给出了选择组的条件，只有满足条件(即元组个数 > 2，表示此学生选修的课超过2门)的组才会被选出来。
• WHERE 子句与 HAVING 短语的区别在于作用对象不同。WHERE 子句作用于基本表或视图，从中选择满足条件的元组。HAVING 短语作用于组，从中选择满足条件的组。

34. 查询平均成绩大于等于85分的学生学号和平均成绩。

	SELECT Sno, AVG(Grade)
	FROM Report
	GROUP BY Sno
	HAVING AVG(Grade) >= 85;
1
2
3
4

• WHERE 子句中不能用聚集函数作为条件表达式

十二、连接查询

等值与非等值连接查询

• 连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为

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

• 其中比较运算符主要有 =、>、<、>= 、<= 、!= (或<>) 等。
• 此外连接谓词还可以使用下面形式:

 [<表名1>.] <列名1> BETWEEN [<表名2>.] <列名2> AND [<表名2>.] <列名3>
1

• 当连接运算符为 = 时，称为等值连接。使用其他运算符称为非等值连接。
• 连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但名字不必相同。

35. 查询每个学生及其选修课程的情况。

	SELECT *
	FROM Student, Report
	WHERE Student.Sno = Report.Sno;		/*将Student 与 Report中同一学生的元组连接起来*/

1
2
3
4

等价于：

	SELECT Student.*, Report.*
	FROM Student, Report
	WHERE Student.Sno = Report.Sno;
1
2
3

• 该表中有两个Sno，前面的 Sno 是 Student 表中的，而后面的是 Report 表中的；
• 执行该操作的一种可能：在Student 表中选取第一个元组然后在 Report 表中从头至尾匹配一遍，然后换Student表的第二个元组匹配一遍，直至Student表中元组都匹配了一遍；这就是嵌套循环连接算法的基本思想；
• 如果在 Report 表Sno上建立了索引的话，就不用每次全表扫描 Report 表了，而是根据 Sno 值通过索引找到相应的 Report 元组。用索引查询 Report 中满足条件的元组一般会比全表扫描快。

自然连接

• 若在等值连接中把目标列中重复的属性列去掉则为自然连接。

36. 用自然连接查询每个学生及其选修课程的情况。

	SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
	FROM Student, Report
	WHERE Student.Sno = Report.Sno;
1
2
3

• 本例中，由于Sname, Ssex, Sage, Sdept, Cno 和 Grade 属性列在 Student 表与 Report 表中是唯一的，因此引用时可以去掉表名前缀;
• 而 Sno 在两个表都出现了，因此引用时必须加上表名前缀。

37. 查询选修3号课程且成绩在90分以上的所有学生的学号和姓名。

	SELECT Student.Sno, Sname
	FROM Student, Report
	WHERE Student.Sno = Report.Sno		--连接谓词
			AND Report.Cno = 'C03' 		--其他限定条件
			AND Report.Grade > 90;
1
2
3
4
5

自身连接

• 连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接。

38. 查询每一门课的间接先修课(即先修课的先修课)。

	SELECT FIRST.Cno, SECOND.Pre_Cno
	FROM Course FIRST, Course SECOND
	WHERE FIRST.Pre_Cno = SECOND.Cno;
1
2
3

外连接

39. 以Student表为主体列出每个学生的基本情况及其选课情况。若某个学生没有选课，仍把Student的悬浮元组保存在结果关系中，而在 Report 表的属性上填空值NULL。

	SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
	FROM Student LEFT OUTER JOIN Report ON (Student.Sno = Report.Sno);
1
2

数据不是很好，所有人都有选修课。。。NULL的效果看不出来
这里放一张课本上面的图吧，大致差不多，都是用NULL进行填充

• 左外连接列出左边关系(如本例 Student )中所有的元组，右外连接列出右边关系中所有的元组。

多表连接

• 连接操作除了可以是两表连接、一个表与其自身连接外，还可以是两个以上的表进行连接，后者通常称为多表连接。

40. 查询每个学生的学号、姓名、选修的课程名及成绩。

	SELECT Student.Sno, Sname, Cname, Grade
	FROM Student, Course, Report
	WHERE Student.Sno = Report.Sno
			AND Course.Cno = Report.Cno;
1
2
3
4

十三、嵌套查询

• 在 SQL 语言中，一个 SELECT-FROM-WHERE 语句称为一个查询块。
• 将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询称为嵌套查询(nested query)。例如:

	SELECT Sname
	FROM Student
	WHERE Sno IN
			(SELECT Sno
			FROM Report
			WHERE Cno = 'C03');
1
2
3
4
5
6

• 先执行子查询，选择出选修课为 C03 的学生，然在父查询再在这里面进行选择。
• 本例中，下层查询块 SELECT Sno FROM Report WHERE Cno=‘3’ 是嵌套在上层查询块 SELECT Sname FROM Student WHERE Sno IN 的 WHERE 条件中的。上层的查询块称为外层查询或父查询，下 层查询块称为内层查询或子查询。
• SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。需要特别指出的是，子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序。
• 嵌套查询使用户可以用多个简单查询构成复杂的查询，从而增强SQL的查询能力。以层层嵌套的方式来构造程序正是SQL中 “ 结构化 ” 的含义所在。

带有 IN 谓词的子查询

不相关子查询

41. 查询与“刘一”在同一个系学习的学生

	SELECT Sno, Sname, Sdept
	FROM Student
	WHERE Sdept IN (
			SELECT Sdept
			FROM Student
			WHERE Sname = '刘一'		--这后面不能直接加上 ; (分号)
			);
1
2
3
4
5
6
7

• 先在子查询中确定 刘一 的所在系，然后父查询再确定与刘一在同一个系中的学生。
• 本例中，子查询的查询条件不依赖于父查询，称为不相关子查询。
• 一种求解方法是由里向外处理，即先执行子查询，子查询的结果用于建立其父查询的查找条件。

42. 查询选修了课程名为“数据库”的学生学号和姓名。

	SELECT Sno, Sname
	FROM Student
	WHERE Sno IN (
			SELECT Sno
			FROM Report
			WHERE Cno IN (
					SELECT Cno
					FROM Course
					WHERE Cname = '数据库'
					)
			);
1
2
3
4
5
6
7
8
9
10
11

• 这里是建议倒着写从内而外，先写子查询，然后再写父查询。
• 首先在 Course 关系中找出“数据库”的课程号，结果为 C03 号
• 然后在 Report 关系中找出选修了 C03 号课程的学生学号
• 最后在 Student 关系中取出 Sno 和 Sname

带有比较运算符的子查询

相关子查询

• 如果子查询的查询条件依赖于父查询，这类子查询称为相关子查询(correlated subquery)， 整个查询语句称为相关嵌套查询 ( correlated
  nested query) 语句。

43. 找出每个学生超过他自已选修课程平均成绩的课程号。

	SELECT Sno, Cno
	FROM Report a
	WHERE grade >= (
			SELECT AVG(grade)		/*某学生的平均成绩*/
			FROM Report b
			WHERE a.Sno = b.Sno);
1
2
3
4
5
6

• a 是表 Report 的别名，又称为元组变量，可以用来表示 Report 的一个元组。内层查询是求一个学生所有选修课程平均成绩的，至于是哪个学生的平均成绩要看参数 a.Sno 的值，而该值是与父查询相关的，因此这类查询称为相关子查询。

• ① 从外层查询中取出 Report 的一个元组 a，将元组 a 的 Sno 值( S01 ) 传送给内层查询。

• ②执行内层查询，得到 S01 这个学生的选修平均成绩，用该值代替内层查询，得到外层查询。

• ③执行这个查询，得出结果。

• 求解相关子查询不能像求解不相关子查询那样一次将子查询求解出来，然后求解父查询。内层查询由于与外层查询有关，因此必须反复求值。

带有 ANY (SOME) 或 ALL 谓词的子查询

• 子查询返回单值时可以用比较运算符，但返回多值时要用 ANY (有的系统用SOME) 或 ALL 谓词修饰符。而使用 ANY 或 ALL 谓词时则必须同时使用比较运算符。其语义如下所示:

	> ANY				大于子查询结果中的某个值
	> ALL				大于子查询结果中的所有值
	< ANY				小于子查询结果中的某个值
	< ALL				小于子查询结果中的所有值
	>= ANY				大于等于子查询结果中的某个值
	>= ALL				大于等于子查询结果中的所有值
	<= ANY				小于等于子查询结果中的某个值
	<= ALL				小于等于子查询结果中的所有值
	= ANY				等于子查询结果中的某个值
	= ALL				等于子查询结果中的所有值(通常没有实际意义)
	!=(或<>) ANY 		不等于子查询结果中的某个值
	!=(或<>) ALL		不等于子查询结果中的任何一个值
1
2
3
4
5
6
7
8
9
10
11
12

44. 查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄。

	SELECT Sname, Sage
	FROM Student
	WHERE Sage < ANY(
			SELECT Sage
			FROM Student
			WHERE Sdept = 'CS')
			AND Sdept <> 'CS';			/*注意这是父查询块中的条件*/

1
2
3
4
5
6
7
8

45. 查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

	SELECT Sname, Sage
	FROM Student
	WHERE Sage < ALL(
			SELECT Sage
			FROM Student
			WHERE Sdept = 'CS')
			AND Sdept <> 'CS';			/*注意这是父查询块中的条件*/
1
2
3
4
5
6
7

emmmmmm似乎和上一个例题的结果差不多。。。数据没设好，把计算机的学生的年龄设的都比较大了点。。。

EXISTS

• EXISTS 代表存在量词 ∃ 。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值 “true” 或逻辑假值 “false”。
• 可以利用 EXISTS 来判断 x∈S、S⊆R、S=R、S∩R 非空等是否成立。

46. 查询所有选修了C01号课程的学生姓名。

	SELECT Sname
	FROM Student
	WHERE EXISTS(
			SELECT *
			FROM Report
			WHERE Sno = Student.Sno
					AND	Cno = 'C01');
1
2
3
4
5
6
7

• 使用存在量词 EXISTS 后，若内层查询结果非空，则外层的 WHERE 子句返回真值，否则返回假值。
• 由 EXISTS 引出的子查询，其目标列表达式通常都用 * ，因为带 EXISTS 的子查询只返回真值或假值，给出列名无实际意义。

NOT EXISTS

• 与 EXISTS 谓词相对应的是 NOT EXISTS 谓词。使用存在量词 NOT EXISTS 后，若内层查询结果为空，则外层的 WHERE 子句返回真值，否则返回假值。

47. 查询没有选修1号课程的学生姓名。

	SELECT Sname
	FROM Student
	WHERE NOT EXISTS(
			SELECT *
			FROM Report
			WHERE Sno = Student.Sno
					AND	Cno = 'C01');
1
2
3
4
5
6
7

• 一些带 EXISTS 或 NOT EXISTS 谓词的子查询不能被其他形式的子查询等价替换，但所有带 IN 谓词、比较运算符、ANY 和 ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。

• SQL中没有全称量词 (forall)，但是可以把带有全称量词的谓词转换为等价的带有存在量词的谓词:

  (∀x)P ≡ ¬(∃x(¬ P))

• 由于没有全称量词，但可将题目的意思转换成等价的用存在量词的形式。

十四、集合查询

• SELECT 语句的查询结果是元组的集合，所以多个 SELECT 语句的结果可进行集合操作。集合操作主要包括并操作 UNION、交操作 INTERSECT 和差操作 EXCEPT。
• 注意：参加集合操作的各查询结果的列数必须相同；对应的属于类型也必须相同。

UNION

48. 查询计算机科学系的学生及年龄不大于20岁的学生。

	SELECT *
	FROM Student
	WHERE Sdept = 'CS'
	UNION
	SELECT *
	FROM Student
	WHERE Sage <= 20;
1
2
3
4
5
6
7

• 本查询实际上是求计算机科学系的所有学生与年龄不大于19岁的学生的并集。使用 UNION 将多个查询结果合并起来时，系统会自动去掉重复元组。如果要保留重复元组则用 UNION ALL 操作符。

INTERSECT

49. 查询计算机科学系的学生与年龄不大于20岁的学生的交集。

	SELECT *
	FROM Student
	WHERE Sdept = 'CS'
	INTERSECT
	SELECT *
	FROM Student
	WHERE Sage <= 20;
1
2
3
4
5
6
7

这实际上就是查询计算机科学系中年龄不大于19岁的学生。

	SELECT *
	FROM Student
	WHERE Sdept = 'CS' 
			AND Sage <= 20;
1
2
3
4

EXCEPT

50. 查询计算机科学系的学生与年龄不大于20 岁的学生的差集。

	SELECT *
	FROM Student
	WHERE Sdept = 'CS'
	EXCEPT
	SELECT *
	FROM Student
	WHERE Sage <= 20; 
1
2
3
4
5
6
7

也就是查询计算机科学系中年龄大于20岁的学生。

	SELECT *
	FROM Student
	WHERE Sdept = 'CS' 
			AND Sage > 20;
1
2
3
4

十五、基于派生表的查询

• 子查询不仅可以出现在 WHERE 子句中，还可以出现在 FROM 子句中，这时子查询生成的临时派生表(derived table)成为主查询的查询对象。

51. 使用派生表查询例43的找出每个学生超过他自已选修课程平均成绩的课程号。

	SELECT Sno, Cno
	FROM Report, (SELECT Sno, AVG(Grade)
				FROM Report
				GROUP BY Sno
				) AS Avg_report(avg_sno, avg_grade) 
	WHERE Report.Sno = Avg_report.avg_sno
			AND Report.Grade >= Avg_report.avg_grade;
1
2
3
4
5
6
7

• 这里 FROM 子句中的子查询将生成一个派生表Avg_report。 该表由avg_sno 和 avg. grade 两个属性组成，记录了每个学生的学号及平均成绩。

• 主查询将 Report 表与 Avg.report 按学号相等进行连接，选出修课成绩大于其平均成绩的课程号。

• 需要说明的是，通过 FROM 子句生成派生表时，AS 关键字可以省略，但必须为派生关系指定一个别名。而对于基本表，别名是可选择项。

尾语

改文是基于课本例子进行适当的改写；
内容量有点大，个别地方可能有误，发现的话麻烦说一声；
把例子都敲一遍后都差不多能理解很多了——多练

资料参考

• 《数据库系统概论》（第5版 编著：王珊 萨师煊）
• CSDN：SQL 使用别名（使用 AS 关键字）