（四）数据库笔记：SQL_数据查询

本笔记是我在学习《数据库系统概论（第5版）》所记，提取课本重点，加上我自己的理解

---

---

数据查询(P₈₉)

一般格式如下：

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

可见，涉及SELECT、FROM、WHERE、GROUP BY…[HAVING]、ORDER BY等关键字

SELECT：选择目的属性列

FROM：从被选择的属性表/视图

WHERE：选择的条件

GROUP BY：为选择后的结果分组，HAVING：分组的依据

ORDER BY：排序

ALL：全部显示（不删除重复）

DISTINCT：删除重复的

默认ALL

ASC：升序

DESC：降序

默认ASC

1.单表查询(P₉₀)

1.1 SELECT基础用法(P₉₀)

从Student表中选择出Sno,Sname两项

SELECT Sno, Sname
FROM Student;

• 多个选项用逗号","隔开

从Student表中选出学生出生年份（表中只有年龄Sage，无年份）

SELECT ‘Birth’, 2021-Sage Birthday
FROM Student;

• SELECT后允许接表达式，输出表中的结果就是表达式的值
• SELECT后允许接常量，输出表中的结果就是每个元组此列都是这个值
• SELECT可以为列设置别名：设置"2021-Sage"别名为"Birthday"：2021-Sage Birthday全写为2021-Sage AS Birthday，AS可省略

从Student表中选出全部列

SELECT
FROM Student;

• * 表示全部

SELECT DISTINCT Sno, ALL Sname
FROM Student;

• SELECT DISTINCT Sno, ALL Sname，DISTINCT删除重复列

---

1.2 WHERE设置查询条件(P₉₃)

查询条件	谓词
比较	=, >, <, >=, <=, !=, <>, !>, !<; NOT+上述比较运算符
确定范围	BETWEEN AND, NOT BETWEEN AND
确定集合	IN, NOT IN
字符匹配	LIKE, NOT LIKE
空值	IS NULL, IS NOT NULL
多重条件(逻辑运算)	AND, OR, NOT

查询计算机科学系（CS）的全体学生

SELECT Sname
FROM Student
WHERE Sdept=‘CS’；

• 用法如上，其他也是，同之前关系代数的用法

查询年龄在20~30的全体学生

SELECT Sname
FROM Student
WHERE Sage BETWEEN 20 AND 30；

• 用法如上，并且BETWEEN包括两边

查询计算机科学系（CS）、数学系（MA）和信息系（IS）的全体学生

SELECT Sname
FROM Student
WHERE Sdept IN (‘CS’, ‘MA’, ‘IS’)；

• 用法如上，IN用于集合

查询姓刘的学生、学号和性别

SELECT Sname, Sno, Ssex
FROM Student
WHERE LIKE ‘刘%’；

• 用法如上，LIKE是匹配，有匹配符可以用，不同于’=’
• 匹配符：_和%。%表示任意字符，_表示单个字符
• 注：数据库字符集为ASCII是，一个汉字需要两个_；字符集为GBK时只需要一个

查询课程名为"DB_Design"的课程名、课程号和学分

SELECT Cname, Cno, Ccredit
FROM Course
WHERE LIKE ‘DB\_Design’ ESCAPE ‘\’；

• 若需查询的与匹配符冲突，使用转义字符\，记得加 ESCAPE ‘\’，意为让机器视’ \ '作转义字符

---

1.3 聚集函数(P₉₇)

函数名	作用
COUNT(*)	统计元组个数
COUNT( [DISTINCT|ALL] <列名>)	统计一列中值的个数
SUM( [DISTINCT|ALL] <列名>)	计算一列值的总和（此列必须是数值型）
AVG( [DISTINCT|ALL] <列名>)	计算一列值的平均值（此列必须是数值型）
MAX( [DISTINCT|ALL] <列名>)	求一列值中的最大值
MIN( [DISTINCT|ALL] <列名>)	求一列值中的最小值

• 不允许在WHERE语句中使用，但可以在HAVING中使用
• 大多时候与GROUP BY 语句是对应使用的，GROUP BY 聚集数据进行分组，所以此叫聚集函数

例子：

计算选修1号课程的学生平均成绩。
SELECT AVG(Grade)
FROM SC
WHERE Cno=‘1’;

查询学生201215012选修课程的总学分数
SELECT SUM(Ccredit)
FROM SC. Course
WHERE Sno=‘201215012’ AND SC.Cno= Course.Cno;

当聚集函数遇到空值时，除 COUNT(*)外，都跳过空值而只处理非空值

---

1.4 GROUP BY分组(P₉₈)

1.4.1 GROUP BY语句

将数据进行分组，相同的分到同一组，细化聚集函数的作用对象

例子：

求各个课程号及相应的选课人数
SELECT Cno, COUNT(Sno)
FROM SC
GROUP BY Cno;

含义：将Cno相同的分为一组，同时将每一组的Sno的个数进行求和

但是：

SELECT Cno, COUNT(Sno), Grade /*错误*/
FROM SC
GROUP BY Cno;

这样就会报错，因为Grade被聚集分组后，一个单元格数据不唯一，Cno是按此分组，数据是一样的，去重后唯一

1.4.2 HAVING语句

HAVING语句是GROUP BY的附属条件，相当于WHERE对于SELECT

HAVING 对已经分好的组进行选择

例如：

查询平均成绩大于等于90分的学生学号和平均成绩

SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;

HAVING支持聚集函数，WHERE不支持，下面的语句就是错误的

SELECT Sno, AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90 /*错误*/
GROUP BY Sno;

---

2. 多表查询(P₉₉)

2.1 多表连接(P₁₀₂)

2.1.2 通过FROM语句

1. 内连接

   使用 INNER JOIN … ON …，ON后接连接条件，

   1. 两张表

      FROM <表1> INNER JOIN <表2> ON <表1>.<字段号>=<表2>.<字段号>

   2. 多张表

      三张表如下，多张表类似，即将两张表先连接，与下一张表连接时看作一张表：

      (FROM <表1> INNER JOIN <表2> ON <表1>.<字段号>=<表2>.<字段号>) INNER JOIN <表3> ON <表1>.<字段号>=<表3>.<字段号>

2. 外连接

   类似于内连接，使用 LEFT/RIGHT OUT JOIN … ON …，ON后接连接条件

   LEFT：以左表为主表去关联右表

   RIGHT：以右表为主表去关联左表

   大多时候使用 LEFT

   FROM <表1> LEFT OUT JOIN <表2> ON <表1>.<字段号>=<表2>.<字段号> LEFT OUT JOIN <表3> ON <表1>.<字段号>=<表3>.<字段号>

可以使用USING去除重复值

比如：

FROM <表1> LEFT OUT JOIN <表2> USING <字段号>

去除指定的<字段号>重复的值

---

2.1.3 通过WHERE语句

例子：

查询每个学生的学号、姓名、选修的课程名及成绩。
本查询涉及三个表,完成该查询的SQL语句如下:
SELECT Student Sno, Sname, Cname, Grade
FROM Student, SC Course
WHERE Student Sno=SC Sno AND SC Cno=Course. Cno

如上，即在WHERE中让两个表字段号相等即可连接

---

2.2 自身连接(P₁₀₁)

同上面的多表连接，但为了区分两个表，必须先要为两张表起别名

---

2.3 嵌套查询(P₁₀₃)(难点)

2.3.1 使用IN

对于有多个条件的查询，就可以使用嵌套：

查询与“刘晨”在同一个系学习的学生。

先分步来完成此查询，然后再构造嵌套查询。（由内到外）：
①确定“刘晨”所在系名
②查找所有在CS系学习的学生

SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN(
SELECT Sdept
FROM Student
WHERE Sname=刘晨)

超过两个条件的，就可以使用多个嵌套：

查询选修了课程名为“信息系统”的学生学号和姓名。
本查询实际上涉及三个关系:（由内到外）

①首先在 Course关系中找出“信息系统”的课程号,结果为3号
②然后在SC关系中找出选修了3号课程的学生学号
③最后在 Student关系中取出Sno和 Sname

SELECT Sno Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM SC
WHERE Cno IN(
SELECT Cno
FROM Course
WHERE Cname=‘信息系统’
)
);

---

2.3.2 带有比较运算符

用法就是比较运算，>、<、=、>=、<=、!=、<>

有些时候就可以替换IN，如上上例中：

查询与“刘晨”在同一个系学习的学生。

SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept = (
SELECT Sdept
FROM Student
WHERE Sname=刘晨)

同样，若子查询返回一个值，就可以使用其他比较运算符

当子查询的条件依赖于父查询时，这类子查询就被称为相关子查询，整个查询语句称为相关嵌套查询

例：

找出每个学生超过他自己选修课程平均成绩的课程号。
SELECT Sno, Cno
FROM SC X
WHERE Grade >=( SELECT AVG(Grade)
FROM SC y
WHERE y.Sno=x.Sno);

---

2.3.3 带有ANY(SOME)或ALL

函数	含义
> ANY	大于子查询结果中的某个值
> ALL	大于子查询结果中的所有值
< ANY	小于子查询结果中的某个值
< ALL	小于子查询结果中的所有值
>= ANY	大于等于子查询结果中的某个值
>= ALL	大于等于子查询结果中的所有值
<= ANY	小于等于子查询结果中的某个值
<= ALL	小于等于子查询结果中的所有值
= ANY	等于子查询结果中的某个值
= ALL	等于子查询结果中的所有值(通常没有实际意义)
!= (或<>) ANY	不等于子查询结果中的某个值
!= (或<>) ALL	不等于子查询结果中的任何一个值

例：

查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
SELECT Sname, Sage
FROM Student
WHERE Sage < ALL
(SELECT Sage
FROM Student
WHERE Sdept='CS")
AND Sdept <> ‘CS’;

很多时候聚集函数都可以完成相关操作，事实上，用聚集函数实现子查询通常比ANY\ALL查询效率更高

ANY ALL与聚集函数对应关系：

	=	<> 或!=	<	<=	>	>=
ANY	IN		< MAX	<= MAX	> MIN	>= MIN
ALL		NOT IN	< MIN	<= MIN	> MAX	>= MAX

---

2.3.3 带有EXISTS

EXISTS代表存在量词彐。

带有 EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“ false”。实际中，使用存在量词EXISTS后，若内层查询结果非空，则外层的 WHERE子句返回真值；否则返回假值

NOT EXISTS与上相反

可以利用 EXISTS来判断x∈S、ScR、S=R、S∩R非空等是否成立。

例：

查询所有选修了1号课程的学生姓名。
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=Student Sno AND Cno=1);

EXISTS子查询只返回逻辑值“TURE”和"FALSE"，给出列名无意义，所以一般都使用*

SQL中没有全称量词( for all)，但是可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：
$（\forall x）P\equiv \neg (\exists x(\neg P)$

---

2.4 集合查询(P₁₁₁)

参与集合操作的各项查询结果列数必须相同，对应项的数据类型也必须相同

基本上使用集合查询的是同一个表

2.4.1 并操作UNION

例子：

查询选修了课程1或者选修了课程2的学生。
本例即查询选修课程1的学生集合与选修课程2的学生集合的并集。
SELECT Sno
FROM SC
WHERE Cno=I’
UNION
SELECT Sno
FROM SO
WHERE Cno=2’;

---

2.4.2 交操作INTERSECT

例子：

查询计算机科学系的学生与年龄不大于19岁的学生的交集。
SELECT *
FROM Student
WHERE Sdept='CS
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19

这实际上就是查询计算机科学系中年龄不大于19岁的学生。
SELECT
FROM Student
WHERE Sdept= AND
Sage<=19

---

2.4.3 差操作EXCEPT

例子：

[例3.68]查询计算机科学系的学生与年龄不大于19岁的学生的差集
SELECT *
FROM Student
WHERE Sdept=CS
EXCEPT
SELECT *
FROM Student
WHERE Sage <=19

也就是查询计算机科学系中年龄大于19岁的学生。
SELECT
FROM Student
WHERE Sdept=‘CS’ AND Sage>19: