第三章关系数据库标准语言SQL-第四节：数据查询
-
关于数据库如何安装，表如何建立这里不再介绍，请移步：（数据库系统概论|王珊）第三章关系数据库标准语言SQL-第零节：MYSQL环境安装和表的建立以及一些注意事项
-
所用表为（上面文章中也有完整代码）：
★★★★★SELECT语句格式★★★★★
数据库最核心的操作便是数据查询，SQL提供了SELECT语句实现该功能，其使用非常灵活而且有极其丰富的功能。格式如下
SELECT语句含义：根据WHERE子句的条件表达式从FROM子句指定的表、视图中找出满足条件的元组，再按照SELECT子句中的目标列表达式选出元组中的属性值形成结果表。如果有：
- GROUP BY：结果按<列名1>的值进行分组，该属性列值相等的元组为一个组；通常会在每组中作用聚集函数；如果该子句还携带HAVING短语，则只有满足指定条件的组才予以输出
- ORDER BY：结果表还要按<列名2>的值的升序或降序排序
一：单表查询（查询时只涉及一张表）
（1）选择表中的若干列
A：查询指定列
演示：
【例1】查询Student表中的学生及年龄
SELECT Sname,Sage from Student;
B：查询全部列
语法：
*作为通配符表示全部
演示：
【例2】查询Student表中全部列
SELECT * from Student;
C：查询经过计算的值
语法：SELECT子句的<目标列表达式>不仅可以是属性列，还可以是表达式，具体有
- 算数表达式
- 字符串常量
- 函数
- 列别名
①：算数表达式
演示：
【例3】根据年龄计算学生的出生日期
SELECT Sname,2022-Sage from Student;
②：字符串常量及函数
演示：
【例4】使用小写字母展示所在系别
SELECT Sname,LOWER(Sdept) from student;
③：使用列别名改变查询结果标题
演示：
【例5】查询Student表中的Sname和Sage，列标题起别名为“姓名”和年龄
SELECT Sname `姓名`,Sage `年龄` from student;
（2）选择表中的若干行（元组）
A：消除取值重复的行（DISTINCT）
语法：前面说过投影操作可能会导致相同的行出现所以其结果必须消除重复行。可以使用DISTINCT消除
演示：
【例6】：查询SC表的Sno列，然后消除重复学号
SELECT DISTINCT Sno from SC;
B：查询满足条件的元组
语法：通过WHERE子句实现，常用的查询条件如下
①：比较大小
演示：
【例7】在SC表中查询成绩大于85的同学的学号
SELECT Sno,Grade from SC
WHERE Grade > 85;
②：确定范围
演示：
【例8】查血年龄在闭区间[19,20]的学生
SELECT Sname,Sage from student
WHERE Sage BETWEEN 19 AND 20;
【例9】查血年龄不在闭区间[19,20]的学生
SELECT Sname,Sage from student
WHERE Sage NOT BETWEEN 19 AND 20;
③：确定集合
演示：
【例10】查询数字1是否在集合(1,2,3)中
- 由于满足，所以会返回1
SELECT 1 IN (1,2,3);
【例11】查询数学系（MA）和计算机科学系（CS）学生的姓名
SELECT Sname,Sdept from student
WHERE Sdept IN('MA','CS');
【例12】查询非数学系（MA）和非计算机科学系（CS）学生的姓名
SELECT Sname,Sdept from student
WHERE Sdept NOT IN('MA','CS');
④：字符匹配
注意：
- 可以使用
=代替LIKE，使用!=代替NOT LIKE %可以代替多个字符_只能代替一个字符
演示：
【例13】查询所有男生
SELECT Sname from student
WHERE Ssex LIKE '男';
【例14】查询所有姓刘的学生（%代替多个）
SELECT Sname FROM student
WHERE Sname like '刘%';
⑤：转义字符
注意：
ESCAPE '＼'表示将“ ＼” 翻译为转义字符
演示：
【例15】假设Course表中有一门课叫做DB_Design
- 如果不作处理，这里的
_代替某个字符，产生歧义
SELECT Cno,Ccredit
FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE '\';
⑥：涉及空值的查询
演示：
【例16】查询缺少成绩的学生的学号和相应的课程号
- 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩
SELECT Sno Cno FROM SC
WHERE Grade IS NULL;
⑦：多重条件查询
注意：
AND和OR来联结多个查询条件AND的优先级高于OR- 可以用括号改变优先级
演示：
【例17】 查询计算机系年龄在20岁以下的学生姓名
SELECT * FROM student
WHERE Sdept='CS' AND Sage < 20;
（3）ORDER BY子句
语法：ORDER BY子句对查询结果按照一个或多个属性列进行排序
- ASC-升序（默认）
- DESC-降序
演示：
【例18】查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列
SELECT Sno,Grade
from SC
WHERE Cno='3'
ORDER BY Grade DESC;
【例19】查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列
SELECT *
FROM student
ORDER BY Sdept,Sage DESC;
（4）聚集函数
语法：主要有以下几种
演示：
【例20】查询学生的总人数
SELECT COUNT(Sno)
FROM student;
【例21】查询选修了课程的学生人数
SELECT COUNT(DISTINCT Sno) Num
FROM SC;
【例22】 计算2号课程的学生平均成绩
SELECT AVG(Grade)
FROM SC
WHERE Cno = '2';
【例23】查询选修2号课程的学生最高分数
SELECT Sno,MAX(Grade)
FROM SC
WHERE Cno='2';
（5）GROUP BY子句
语法：GROUP BY子句将查询结果按某一列或多列的值分组，值相等的分为一组
- 分组目的是为了细化聚集函数的作用对象：若未分组，聚集函数将会作用于整个查询结果；若分组，聚集函数将会作用于每一个组，也即每一个组都有一个函数值
- 需要注意：WHERE子句作用于整个表或视图，从中选择出满足条件的元组；HAVING短语作用于组，从中选择满足条件的组
相信读完之后大家可能还是有点迷糊，举个例子。比如我要查询“各个课程对应的选课人数”，如果没有GROUP BY子句
SELECT Cno,Count(Sno)
FROM sc;
由于它会作用于整个查询结果，所以直接统计出了记录的条数
如果加入GROUP BY子句，按照课程号分组，那么GROUP BY会按照Cno进行分组，相同的为一组，然后在每组内统计Sno
SELECT Cno,Count(Sno)
FROM sc
GROUP BY Cno;
而如果我只想显示那些选课人数大于1以上的课程号呢，那么就可以使用HAVING短语，在组内进行筛选
SELECT Cno,Count(Sno)
FROM sc
GROUP BY Cno
HAVING Count(Sno) > 1;
演示：
【例24】查询平均成绩大于等于80分的学生学号和平均成绩
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade) >= 80;
二：连接查询（查询时涉及多张表）
（1）等值连接和非等值连接
语法：在WHERE子句中写入连接条件（又叫做连接每谓词），其格式为
其中比较运算符有：=、>、<、>=、<=、!=
- 当运算符为
=时称之为等值连接 - 当运算符不为
=时称之为非等值连接
演示：
【例25】查询每个学生及其选修课程的情况
SELECT student.*,sc.*
FROM student,sc
WHERE student.Sno=sc.Sno;
【例26】使用自然连接（特殊的等值连接）完成【例25】
SELECT student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM student,sc
WHERE student.sno=sc.sno;
【例27】查询选修2号课程且成绩在80分以上的所有学生的学号和姓名
SELECT Student.Sno,Sname
FROM student,sc
WHERE student.Sno=sc.Sno AND //连接条件
Cno='2' AND Grade > 80; //其他限定条件
（2）自身连接
语法：所谓自身连接就是指一个表与自己连接
演示：
【例28】查询每一门课的先修课的先修课
- 在
Course表中有的只是每门课的直接先修课，要想得到先修课的先修课，那么就必须先找到一门课的先修课，然后再按此先修课的课程号查找它的先修课
因此，为Course表取两个别名，分别为ONE和TWO
SELECT ONE.Cno,TWO.Cpno
FROM Course ONE,Course TWO
WHERE ONE.Cpno=TWO.Cno;
当然，还可以继续找先修课的先修课的先修课
SELECT ONE.Cno,THREE.Cpno
FROM Course ONE,Course TWO,course THREE
WHERE ONE.Cpno=TWO.Cno AND TWO.Cpno=THREE.Cno;
（3）连接JOIN
语法：SQL JOIN用于把来自两个或多个表的行结合起来，其格式如下
SELECT column_name(s)
FROM TABLE1//左表
<某某 JOIN>TABLE2//右表
ON TABLE1.column_name=TABLE2.column_name
有如下几类
INNER JOIN(JOIN)LEFT JOIN(LEFT OUTER JOIN)RIGHT JOIN(RIGHT OUTER JOIN)FULL JOIN(FULL OUTER JOIN)
A：INNER JOIN(JOIN)
INNER JOIN(JOIN)：关键字在表中存在至少一个匹配时返回行
演示：
【例29】以sc和course的Cno作为比对标准，将相同连接在一起
SELECT Sno,sc.Cno,Grade,course.Cno,Cname,Cpno,Ccredit
FROM sc INNER JOIN course ON(sc.Cno=course.Cno);
B：LEFT JOIN(LEFT OUTER JOIN)
LEFT JOIN(LEFT OUTER JOIN)：以左表为标准，若右表中无匹配，则填NULL
SELECT Sno,sc.Cno,Grade,course.Cno,Cname,Cpno,Ccredit
FROM sc LEFT JOIN course ON(sc.Cno=course.Cno);
C：RIGHT JOIN(RIGHT OUTER JOIN)
RIGHT JOIN(RIGHT OUTER JOIN)：以右表为标准，若左表中无匹配，则填NULL
SELECT Sno,sc.Cno,Grade,course.Cno,Cname,Cpno,Ccredit
FROM sc RIGHT JOIN course ON(sc.Cno=course.Cno);
D：FULL JOIN(FULL OUTER JOIN)
FULL JOIN(FULL OUTER JOIN)：本质就是结合了LEFT JOIN和RIGHT JOIN
SELECT Sno,sc.Cno,Grade,course.Cno,Cname,Cpno,Ccredit
FROM sc FULL JOIN course ON(sc.Cno=course.Cno);
（4）复合条件连接
语法：没有什么新的东西，就是涉及多张表，多个条件的查询
演示：
【例30】查询每个学生的学号、姓名、选修的课程名及成绩
SELECT student.Sno,Sname,Cname,Grade
FROM student,course,sc
WHERE student.Sno=sc.Sno AND sc.Cno =course.Cno;
三：嵌套查询
在SQL中，一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称之为嵌套查询。比如
在下面的这个例子中，内层循环查出来的是符合Cno=2的Sno集合，外层循环则在该集合内查询是否有满足的Sno，有的话显示Sname即可
SELECT Sname //外层查询
FROM Student
WHERE Sno IN
(
SELECT Sno //内层查询
FROM SC
WHERE Cno='2'
)
需要注意以下几点
- 子查询的SELECT语句不能使用
ORDER BY子句 - 嵌套查询往往可以转换为对应的连接运算
（1）带有IN谓词的子查询
语法：嵌套查询中，子查询的结果往往是一个集合，所以IN在嵌套查询中使用次数最为频繁
演示：
【例31】查询与“刘晨”在同一个系学习的学生
- 考虑时可以由内向外，先查询出刘晨所在的系，然后在该集合中查询满足该集合的学生姓名
SELECT student.Sno,Sname,Sdept FROM student WHERE Sdept IN
(SELECT Sdept FROM student WHERE Sname='刘晨');
当然嵌套查询有时也可以转化为连接完成
SELECT S1.Sno,S1.Sname,S1.Sdept
FROM Student S1,Student S2
WHERE S1.Sdept=S2.Sdept AND S2.Sname='刘晨';
【例32】查询选修了课程名为“信息系统”的学生学号和姓名
- 首先在
Course表中找出“信息系统”的Cno，形成Cno的集合 - 然后在
SC表中找出哪些记录的Cno在Cno集合内，形成Sno集合 - 最后在
Student表中找出哪些记录的Sno在Sno集合内，形成最终的结果
SELECT Sno,Sname FROM Student WHERE Sno IN
(SELECT Sno FROM SC WHERE Cno IN
(SELECT Cno FROM Course WHERE Cname='信息系统')
);
当然本例也可以使用连接完成
SELECT student.Sno,Sname
FROM student,course,sc
WHERE Student.Sno=sc.Sno AND sc.Cno=course.Cno AND Cname='信息系统';
（2）带有比较运算符的子查询
语法：带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单个值时，可以使用>、<、=、>=、<=、!=等比较运算符
演示：
【例33】查询与“刘晨”在同一个系学习的学生
- 和【例31】一样，只不过【例31】使用
IN完成的
SELECT Sno,Sname,Sdept FROM student WHERE Sdept
=
(SELECT Sdept FROM student WHERE Sname='刘晨');
【例34】找出每个学生超过他选修课程平均成绩的课程号
- 首先内层循环要计算该学生的平均成绩
- 外层循环使用
>= - 两个循环要用
Sno关联在一起（相关子查询）
SELECT Sno,Cno FROM sc x WHERE Grade
>=
(SELECT AVG(Grade) from sc WHERE x.Sno=Sno);
√：不相关子查询和相关子查询
不相关子查询：子查询的查询条件不依赖于父查询
- 像【例31】、【例32】这样的都是不相关子查询
查询时由里向外、逐层处理。每个子查询在上一级查询处理之前求解，子查询的结果用于建立父查询的查找条件
相关子查询：子查询的查询条件依赖于父查询
- 像【例34】这样的都是相关子查询
查询时首先会取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回为真，则将此元组放入结果表，然后再取外层表的下一个元组，接着重复上述过程
比如对于【例34】，其处理过程可能是下面这样的
①：从外层查询中取出sc的一个元祖x，将x.Sno的值(201215121)传递给内层查询
SELECT AVG(Grade)
FROM sc y
WHERE y.Sno='201215121';
②：执行内层查询，得到值88（近似），用该值代替内层查询，得到外层查询
SELECT Sno,Cno
FROM SC X
WHERE Grade >= 88
③：执行这个查询，得到
(201215121,1)
(201215121,3)
然后再取下一个元组重复上述过程即可
（3）带有ANY（SOME）或ALL谓词的子查询
语法：内层查询返回单个值时使用比较运算符。如果返回多个值要用ANY（有的是SOME）或ALL，然后同时使用比较运算符
另外ANY或ALL与聚集函数、IN等谓词有等价关系。也就是说在一些情况下，他们的写法效果作用是一样的
- 例如
<ALL意思是小于所有值，那么它的等价含义就是小于最小值，也即<MIN
演示：
【例35】查询其他系比计算机科学系任意一个学生年龄小的学生姓名和年龄
SELECT Sname,Sage FROM student WHERE Sage < ANY
(SELECT Sage FROM student WHERE Sdept='CS')
AND Sdept!='CS';
由于<ANY等价于<MAX，所以其等价写法就是
SELECT Sname,Sage FROM student WHERE Sage <
(SELECT MAX(Sage) FROM student WHERE Sdept='CS')
AND Sdept!='CS';
（4）带有EXISTS谓词的子查询
语法：EXISTS代表“存在量词”。带有EXISTS谓词的子查询不返回任何数据，只返回true或false。另外，由EXISTS引出的子查询，其目标列表达式通常都是*，因为给出列名没有实际意义
- 若内层查询结果非空，则外层WHERE子句返回
true - 若内层查询结果为空，则外层WHERE子句返回
false
与EXISTS相对的便是NOT EXISTS
- 若内层查询结果为空，则外层WHERE子句返回
true - 若内层查询结果非空，则外层WHERE子句返回
false
需要注意的是，一些带有EXISTS和NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换；但是所有带IN谓词，比较运算符，ANY和ALL谓词的子查询都可以用带EXISTS谓词的子查询替换
演示：
【例36】查询所有选修了1号课程的学生姓名
- 处理时，首先会取外层查询中
Student表的第一个元组，根据它与内层查询相关的属性值（Sno）处理内层查询，若WHERE子句返回为true则取外层查询中该元组的Sname放入结果表
SELECT Sname FROM student WHERE
EXISTS
(SELECT * from sc where Sno=student.Sno AND Cno='1');
【例37】查询与“刘晨”在同一个系学习的学生
- 和【例31】一样，这里用EXISTS代替IN
SELECT Sno,Sname Sdept FROM student s1 WHERE
EXISTS
(select * FROM student WHERE Sdept=s1.Sdept AND Sname='刘晨');
【例38】查询选修了全部课程的学生姓名
- 它等价于：查询这样一个学生，没有一门课是它不选的
四：集合查询
语法：SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。主要有：
- 并操作：
UNION - 交操作：
INTERSECT - 差操作：
EXCEPT
需要注意的是查询结果的列数必须相同且对应项的数据类型也必须相同
演示：
【例39】查询计算机科学系的学生及年龄不大于19岁的学生
SELECT Sname,Sage,Sdept FROM student WHERE Sdept='CS'
UNION
SELECT Sname,Sage,Sdept FROM student WHERE Sage<=19;
【例40】查询计算机科学系的学生与年龄不大于19岁的学生的交集
SELECT * FROM student WHERE Sdept='CS'
INTERSECT
SELECT * FROM student WHERE Sage <=19;
数据库系统概论王珊第五版笔记
华为工作强度 835人发布