SQL 数据查询

1单关系查询

示例数据库

1.1投影查询

SELECT [DISTINCT] 属性名列表 FROM 关系名; 从一个关系中选出指定的列.

查询结果中属性的顺序与属性名列表给出的属性顺序相同;
不加DISTINCT,则不去除结果中的重复元组;加上DISTINCT,则去除结果中的重复元组;
若要返回关系中所有的列,可将属性名列表简写为*.

例: 查询学生的学号和姓名
sql
SELECT Sno, Sname FROM Student;
查询所有系名
sql
SELECT DISTINCT Sdept FROM Student;
查询全部学生信息
sql
SELECT * FROM Student;

可将投影查询中的属性名替换为表达式,做更复杂的投影操作. SELECT [DISTINCT] 表达式列表 FROM 关系名;

表达式可以是常量,算数表达式,函数表达式,逻辑表达式;
查询结果中表达式列的名称就是该表达式的字符串,可以用表达式 AS 属性名将表达式列重命名.

例: 查询学生的学号和姓名(姓名全大写)
sql
SELECT Sno, UPPER(Sname) FROM Student;
查询学生的姓名和出生年份
sql
SELECT Sname, (2021 - Sage) AS bd FROM Student;

1.2选择查询

SELECT [DISTINCT] 表达式列表 FROM 关系名 WHERE 选择条件; 从一个关系中选择满足给定条件的元组.

例: 查询计算机系(CS)全体学生的学号和姓名
sql
SELECT Sno, Sname FROM Student WHERE Sdept = 'CS';
查询计算机系(CS)全体男同学的学号和姓名
sql
SELECT Sno, Sname FROM Student WHERE Sdept = 'CS' AND Ssex = 'M';
查询计算机系(CS)和数学系(Math)全体学生的学号和姓名
sql
SELECT Sno, Sname FROM Student WHERE Sdept = 'CS' OR Sdept = 'Math';

1.2.1选择查询条件

选择查询条件	语法	功能
表达式比较	`表达式1 比较运算符表达式2`	比较运算符: `=`, `<`, `<=`, `>`, `>=`, `!=`, `<>`
范围比较	`表达式1 [NOT] BETWEEN 表达式2 AND 表达式3`	判断表达式1的值是否(不)在表达式2和表达式3之间
集合元素判断	`表达式1 [NOT] IN (表达式2, ..., 表达式n)`	判断表达式1的值是否(不)在表达式2, …,表达式n的值构成的集合中
字符串匹配	`字符串表达式 [NOT] LIKE 模式 [ESCAPE 转义字符]`	判断字符串表达式的值是否匹配给定的含有通配符的模式通配符`_`:匹配单个字符通配符`%`:匹配任意长度的字符串(含空串) 可通过`ESCAPE`子句指定转义字符,默认转义字符`\`
字符串正则表达式匹配	`字符串表达式[NOT] REGEXP	RLIKE模式`¹
空值(NULL)判断	`属性名 IS [NOT] NULL`	判断属性值是否(不)为空错误写法: ~~`属性名 = NULL`~~, ~~`属性名 <> NULL`~~, ~~`属性名 != NULL`~~. 这些错误写法结果均为UNKNOWN.
逻辑运算	逻辑运算符: `AND`, `OR`, `NOT`	只有使查询条件为真的元组才能出现在查询结果中
集合操作	求并: `查询语句1 UNION [ALL] 查询语句2` 求交²: `查询语句1 INTERSECT 查询语句2` 求差³: `查询语句1 MINUS/EXCEPT 查询语句2`	求两个查询语句结果的并,交,差查询语句1的结果的属性名将作为集合操作结果的属性名; 若使用关建词`ALL`,则并集不去重; 即使两个查询语句的结果都是有序的,集合操作的结果也未必有序.

例: 查询姓名首字母为E的学生的学号和姓名(字符串匹配)
sql
SELECT Sno, Sname FROM Student WHERE Sname LIKE 'E%';
查询姓名为4个字母且首字母为E的学生的学号和姓名(字符串匹配)
sql
SELECT Sno, Sname FROM Student WHERE Sname LIKE 'E___';
查询姓名首字母为E或F的学生的学号和姓名(正则表达式)
sql
SELECT Sno, Sname FROM Student WHERE Sname REGEXP '^[EF].*';
查询选了课但还未取得成绩的学生(空值判断)
sql
SELECT Sno FROM SC WHERE Grade IS NULL;
查询选修了1002号或3006号课的学生的选课信息(集合操作)
sql
SELECT * FROM SC WHERE Cno = '1002' UNION ALL
SELECT * FROM SC WHERE Cno = '3006';
查询选修了1002号或3006号课的学生的学号(集合操作)
sql
SELECT Sno FROM SC WHERE Cno = '1002' UNION
SELECT Sno FROM SC WHERE Cno = '3006';

1.2.2查询结果排序

在查询语句的后面加上OTDER BY 属性名1 [ASC|DESC], ..., 属性名n [ASC|DESC].

按照(属性1, …,属性n)的字典序进行排序,对查询结果关系中的元组进行排序.
ASC表示升序(默认),DESC表示降序.
若排序属性含空值, ASC:属性值为空的元组排在最前; DESC:属性值为空的元组拍在最后.
通常用ORDER BY子句对最终查询结果排序,而不对中间结果排序.

例: 查询计算机系(CS)全体学生的学号和姓名,并按学号升序排列
sql
SELECT Sno, Sname FROM Student WHERE Sdept = 'CS' ORDER BY Sno;
查询全体学生的信息,结果按所在系升序排列,同一个系的学生按年龄降序排列
sql
SELECT * FROM Student ORDER BY Sdept ASC, Sage DESC;

1.2.3限制查询结果数量

在查询语句的后面加上LIMIT [偏移量,] 结果数量或者LIMIT 结果数量 [OFFSET 偏移量]⁴.

限制查询结果中元组的数量(不是标准SQL的内容)位置.
从偏移量(默认是0)位置的元组开始,返回指定数量的元组.

例: 查询3006号课得分最高的前2名学生的学号和成绩
sql
SELECT Sno, Grade FROM SC WHERE Cno = '3006' 
ORDER BY Grade DESC LIMIT 2;

1.3聚集(Aggregation)查询

SELECT 聚集函数([DISTINCT] 表达式) FROM...WHERE... 计算一个关系上某表达式所有值的聚集值(值的个数COUNT,最大值MAX,最小值MIN,总和SUM,平均值AVG)⁵.

语法	功能
`COUNT(*)`	所有元组的数量
`COUNT(表达式)`	非空表达式值的数量
`COUNT(DISTINCT 表达式)`	不同的非空表达式的数量
`MAX([DISTINCT] 表达式)`	表达式的最大值
`MIN([DISTINCT] 表达式)`	表达式的最小值
`SUM(表达式)`	表达式值的和
`SUM(DISTINCT 表达式)`	不同表达式值的和
`AVG(表达式)`	表达式值的平均值
`AVG(DISTINCT 表达式)`	不同表达式值的平均值

例: 查询计算机系全体学生的数量
sql
SELECT COUNT(*) FROM Student WHERE Sdept = 'CS';
查询计算机系学生的最大年龄
sql
SELECT MAX(Sage) FROM Student WHERE Sdept = 'CS';

聚集函数不能出现在WHERE子句中!

例: 查询年龄最大的学生的学号
sql
SELECT Sno FROM Student WHERE Sage = MAX(Sage);
写法错误,正确的做法是使用嵌套查询!

1.4分组(Group By)查询

SELECT 分组属性列表, 聚集函数表达式列表 FROM 关系名 WHERE 选择条件 GRUP BY 分组属性列表;

根据指定的分组属性,对一个关系中的元组进行分组,分组属性值相同元组的分一组.
对每个组中元组的非分组属性的值进行聚集.
分组查询语句中不能包含分组属性及聚集函数表达式以外的其他表达式.

例: 统计每门课的选课人数和平均成绩
sql
SELECT Cno, COUNT(*), AVG(Grade) FROM SC GROUP BY Cno;
统计每个系的男生人数和女生人数
sql
SELECT Sdept, Ssex, COUNT(*) FROM Student GROUP BY Sdept, Ssex;

分组完成后,经常需要安装组内元组的统计信息对分组进行筛选 SELECT 分组属性列表, 聚集函数表达式列表 FROM 关系名 WHERE 选择条件 GROUP BY 分组属性列表 HAVING 分组筛选条件;

例: 查询选修了2门以上课程的学生的学号和选课数
sql
SELECT Sno, COUNT(*) FROM SC GROUP BY Sno HAVING COUNT(*) >= 2;
查询2门以上课程得分超过80的学生的学号及这些课程的平均分
sql
SELECT Sno, AVG(Grade) FROM SC WHERE Grade > 80 
GROUP BY Sno HAVING COUNT(*) >= 2;

注意事项:

SELECT子句的目标列中只能包含分组属性和聚集函数⁶.
WHERE子句的查询条件中不能出现聚集函数.
HAVING子句的分组筛选条件中可以使用聚集函数.
WHERE,GROUP BY和HAVING的执行顺序
1. 按照WHERE子句给出的条件,从关系中选出满足条件的元组;
2. 按照GROUP BY子句指定的分组属性,对元组进行分组;
3. 按照HAVING子句给出的条件,对分组进行筛选.

2连接查询

2.1内连接

SELECT ... FROM 关系名1 [INNER] JOIN 关系名2 ON 连接条件; 按照给定的连接条件,对两个关系做内连接.

例: 查询学生及其选课情况,列出学号,姓名,课号,得分
sql
SELECT Student.Sno, Sname, Cno, Grade
FROM Student JOIN SC ON (Student.Sno = SC.Sno);

当内连接是等值连接,且连接属性同名时,可使用如下语法 关系名1 [INNER] JOIN 关系名2 USING (连接属性列表)

例: 查询学生及其选课情况,列出学号,姓名,课号,得分
sql
SELECT Student.Sno, Sname, Cno, Grade
FROM Student JOIN SC USING (Sno);

2.2自然连接

关系名1 NATURAL JOIN 关系名2 两个关系做自然连接.

例: 查询学生及其选课情况,列出学号,姓名,课号,得分
sql
SELECT Student.Sno, Sname, Cno, Grade
FROM Student NATURAL JOIN SC;

2.3自连接

一个关系与其自身进行连接.

参与连接的关系在物理上时同一个关系,但在逻辑上看作两个关系,因此用AS必须重命名.
当为关系取了别名后,但凡用到该关系时都必须使用其别名,不能再使用原关系名.
属性名前必须加别名做前缀.

例: 查询和Elsa在同一个系学习的学生的学号和姓名

SELECT S2.Sno, S2.Sname
FROM Student AS S1 JOIN Student AS S2
ON S1.Sdept = S2.Sdept AND S1.Sno != S2.Sno
WHERE S1.Sname = 'Elsa';

2.4外连接

两个关系做外连接.

左外连接: 关系名1 LEFT [OUTER] JOIN 关系名2 ON 连接条件 右外连接: 关系名1 RIGHT [OUTER] JOIN 关系名2 ON 连接条件 全外连接: 关系名1 FULL [OUTER] JOIN 关系名2 ON 连接条件⁷ 全自然外连接: 关系名1 NATURAL LEFT|RIGHT [OUTER] JOIN 关系名2

当外连接时等值连接,且连接属性同名时,可使用USING (连接属性列表)声明连接条件.

例: 查询没有选课的学生的学号和姓名
sql
SELECT Student.Sno, Sname
FROM Student LEFT JOIN SC ON (Student.Sno = SC.Sno)
WHERE Cno IS NULL;

3嵌套查询

查询块:一个SELECT-FROM-WHERE语句称为一个查询块(block).

嵌套查询:将一个查询块嵌套在另一个查询块中得到的查询称为嵌套查询(nested query),内层查询块称为子查询(subquery).

子查询的类型:

不相关子查询(independent subquery):子查询不依赖与外层查询;
相关子查询(correlated subquery):子查询依赖于外层查询.

例: 查询和Elsa在同一个系学习的学生的学号和姓名(含Elsa).
使用不相关子查询
sql
SELECT Sno, Sname FROM Student WHERE Sdept =
(SELECT Sdept FROM Student WHERE Sname = 'Elsa');
使用相关子查询
sql
SELECT Sno, Sname FROM Student AS S WHERE EXISTS
(SELECT * FROM Student AS T
WHERE T.Sname = 'Elsa' AND T.Sdept = S.Sdept);

嵌套查询的写法:

在集合判断条件中使用子查询:使用[NOT] IN
在比较条件中使用子查询:使用比较运算符
在存在性测试条件中使用子查询:使用[NOT] EXISTS
子查询结果作为派生关系

例: 查询和Elsa在同一个系学习的学生的学号和姓名(含Elsa)
在集合判断条件中使用子查询
sql
SELECT Sno, Sname FROM Student WHERE Sdept IN
(SELECT Sdept FROM Student WHERE Sname = 'Elsa');
在比较条件中使用子查询
sql
SELECT Sno, Sname FROM Student WHERE Sdept =
(SELECT Sdept FROM Student WHERE Sname = 'Elsa');
在存在性测试条件中使用子查询
sql
SELECT Sno, Sname FROM Student AS S WHERE EXISTS
(SELECT * FROM Student AS T
WHERE T.Sname = 'Elsa' AND T.Sdept = S.Sdept);

3.1在集合判断条件中使用子查询

表达式 [NOT] IN (子查询) 判断表达式的值是否(不)属于子查询的结果. 先执行子查询,后执行父查询.

例: 查询和Elsa在同一个系学习的学生的学号和姓名(含Elsa)
sql
SELECT Sno, Sname FROM Student WHERE Sdept IN
(SELECT Sdept FROM Student WHERE Sname = 'Elsa');
查询选修了 “Database Systems” 的学生的学号和姓名
sql
SELECT Sno, Sname FROM Student WHERE Sno IN
(SELECT Sno FROM SC WHERE Cno IN
(SELECT Cno FROM Course
WHERE Cname = 'Database Systems'));

3.2在比较条件中使用子查询

表达式比较运算符 [ALL|ANY|SOME] (子查询) 将表达式的值与子查询的结果进行比较.先执行子查询,后执行父查询.

ALL:当表达式的值与子查询结果中任意的值都满足比较条件时,返回真;否则返回假;
ANY或SOME:当表达式的值与子查询结果中某个值满足比较条件时,返回真;否则返回假;
如果子查询结果应仅包含单个值,则无需在比较运算符后面加ALL,ANY或 SOME.

例: 查询和Elsa在同一个系学习的学生的学号和姓名(含Elsa)
sql
SELECT Sno, Sname FROM Student WHERE Sdept =
(SELECT Sdept FROM Student WHERE Sname = 'Elsa');
查询年龄最大的学生的学号
sql
SELECT Sno FROM Student WHERE Sage = 
(SELECT MAX(Sage) FROM Student);
查询比计算机系(CS)全体学生年龄都大的学生的学号
sql
SELECT Sno FROM Student WHERE Sage > ALL
(SELECT Sage FROM Student WHERE Sdept = 'CS');
查询学生平均年龄比全校学生平均年龄大的系
sql
SELECT Sdept FROM Student GROUP BY Sdept
HAVING AVG(Sage) > (SELECT AVG(Sage) FROM Student);

3.3在存在性测试条件中使用子查询

[NOT] EXISTS (子查询) 判断子查询结果是否(不)为空. 子查询的SELECT后无需列出目标列,只需用SELECT *,因为我们只判断子查询结果是否为空,并不需要使用子查询结果.

查询和Elsa在同一个系学习的学生的学号和姓名(含Elsa)
sql
SELECT Sno, Sname FROM Student AS S
WHERE EXISTS (SELECT * FROM Student AS T
WHERE T.Sname = 'Elsa' AND T.Sdept = S.Sdept);
查询执行过程:

从 $Student$ 关系中依次取出每条元组 $t$ ,设 $t$ 的 $Sdept$ 属性值为 $t[Sdept]$ ;

将 $t[Sdept]$ 代入子查询后,执行子查询;

若子查询的结果不为空,则将 $t$ 投影到 $Sno$ 和 $Sname$ 属性上,并输出投影后的元组.

用EXISTS实现全称量词 $\forall$ 功能: SQL不支持全称量词 $\forall$ (for all),用EXISTS实现全称量词,因为 $\forall x(P(x)) = \nexists x(\neg{P(x)})$

例: 查询选修了全部课程的学生的学号
sql
SELECT Sno FROM Student WHERE NOT EXISTS (
SELECT * FROM Course WHERE NOT EXISTS (
SELECT * FROM SC
WHERE SC.Sno = Student.Sno AND SC.Cno = Course.Cno));
思路:设 $t \in Student$ 是某个选修了全部课程的学生的元组, 则 $\forall c \in Course$ ,必 $\exists s \in SC$ ,使 $s[Sno] = t[Sno] \wedge s[Cno] = c[Cno]$ 这等价于: $\nexists c \in Course$ ,使得 $\nexists s \in SC$ ,使 $s[Sno] = t[Sno] \wedge s[Cno] = c[Cno]$ .

用EXISTS实现逻辑蕴含 $\rightarrow$ 功能: SQL不支持逻辑蕴含 $rightarrow$ (implication),可以用EXISTS实现逻辑蕴含,因为 $x \rightarrow y = \neg{x} \vee y$ .

例: 查询至少选修了CS-001号学生选修的全部课程的学生的学号

SELECT Sno FROM Student WHERE NOT EXISTS (
SELECT * FROM SC AS SC1
WHERE SC1.Sno = 'CS-001' AND NOT EXISTS (
SELECT * FROM SC AS SC2
WHERE SC2.Sno = Student.Sno AND SC2.Cno = SC1.Cno));

3.4子查询结果作为派生关系

FROM(子查询)

派生表(derived table):将子查询的结果当作关系放在外层查询的FROM子句中使用,称为派生表;
子查询必须是独立子查询;
派生表必须重命名.

例: 查询选修了2门以上课程的学生的学号和选课数
sql
SELECT Sno, T.Amt FROM
(SELECT Sno, COUNT(*) AS Amt FROM SC GROUP BY Sno) AS T
WHERE T.amt >= 2;

MySQL使用关建词REGEXP或RLIKE;
Oracle使用关建词REGEXP _LIKE;
MS SQL Server使用关建词LIKE. ↩
Oracle和MS SQL Server支持INTERSECT, MySQL不支持INTERSECT. ↩
Oracle用MINUS, MS SQL Server用EXCEPT, MySQL两者都不支持. ↩
Oracle和MS SQL Server使用不同的语法. ↩
MySQL还支持其他聚集函数,如GROUP_CONCAT(拼接), VARIANCE (方差), STD (标准差) https://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html. ↩
单纯出于性能考虑, MySQL允许某些非分组属性出现在目标列中https://dev.mysql.com/doc/refman/5.5/en/group-by-handling.html. ↩
Oracle和MS SQL Server支持FULL OUTER JOIN, MySQL不支持FULL OUTER JOIN. ↩