数据库系统概论期末复习二：SQL语句（二）

第二部分：数据查询（必考）

以学生-课程数据库为例

学生表：Student(Sno,Sname,Ssex,Sage, Sdept)

课程表：Course(Cno,Cname,Cpno, Ccredit)

学生选课表：SC(Sno,Cno,Grade)

数据查询语句格式：

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

一.单表查询

仅涉及一个表的查询

①选择表中的若干列

②选择表中的若干元组

③ORDER BY子句

④聚集函数 (aggregation function)

⑤GROUP BY子句

1.选择表中的若干列

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

SELECT Sno, Sname
FROM Student;

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

SELECT Sname, Sno, Sdept
FROM Student;

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

SELECT Sno，Sname，Ssex，Sage，Sdept
FROM Student;
或
SELECT *
FROM Student;

注：选出所有属性列时：

① 在SELECT关键字后面列出所有列名

② 将<目标列表达式>指定为*

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

出生年份可以用本年年份-学生年龄

SELECT Sname，2021-Sage
FROM Student;

注：

SELECT子句的<目标列表达式>不仅可以是表中属性列，也可以是表达式：

① 算术表达式

② 字符串常量

③ 函数

④ 列别名

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

SELECT Sname，‘Year of Birth: '，2021-Sage，LOWER(Sdept)
FROM Student;

使用列别名改变上述题目查询结果的列标题

SELECT Sname NAME，‘Year of Birth:’ BIRTH，2018-Sage BIRTHDAY，LOWER(Sdept) DEPARTMENT
FROM Student;

结果为：

2.选择表中的若干元组

ALL：所有的

DISTINCT：所有的取值不重复的

说明：如果没有指定DISTINCT关键词， 则缺省为ALL

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

SELECT Sno FROM SC;
//等价于：
SELECT ALL Sno FROM SC;

SELECT DISTINCT Sno FROM SC;//指定DISTINCT关键词，去掉表中重复行

接下来是课本上满足查询条件的元组的查询例题：

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

SELECT Sname
FROM Student
WHERE Sdept=‘CS’;

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

SELECT Sname，Sage
FROM Student
WHERE Sage < 20;

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

SELECT DISTINCT Sno
FROM SC
WHERE Grade<60;

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

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

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

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

例12. 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别

SELECT Sname,Ssex
FROM Student
WHERE Sdept IN ( 'IS', 'MA', 'CS' );

例13. 查询既不是信息系、数学系，也不是计算机科学系的学生姓名和性别

SELECT Sname，Ssex
FROM Student
WHERE Sdept NOT IN ( 'IS','MA','CS' );

例14. 查询学号为200215121学生的详细情况

SELECT * FROM Student
WHERE Sno LIKE '200215121';
//等价于：
SELECT * FROM Student
WHERE Sno = '200215121';

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

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

例16. 查询姓“欧阳”且全名为三个汉字的学生的姓名

SELECT Sname
FROM Student
WHERE Sname LIKE '欧阳_ _';

一个“_”一个字节，一个汉字占两个字节。

例17. 查询DB_Design课程的课程号和学分

SELECT Cno,Ccredit
FROM Course
WHERE Cname LIKE 'DB\_Design'
ESCAPE '\';

注：ESCAPE ‘＼’ 表示“ ＼” 为换码字符

匹配串中紧跟在“ ＼” 后的字符“_”不再具有通配符的含义，转义为普通的“_”字符

例18. 查询以“DB_”开头，且倒数第3个字符为 i 的课程的详细情况。

SELECT *
FROM Course
WHERE Cname LIKE 'DB\_%i_ _' ESCAPE '\';

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

SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL;

例20. 查询所有有成绩的学生学号和课程号

SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL;

例21. 查询计算机系年龄在20岁以下的学生姓名

SELECT Sname
FROM Student
WHERE Sdept= 'CS' AND Sage<20;

注：

逻辑运算符：AND和OR来连接多个查询条件

① AND的优先级高于OR

② 可以用括号改变优先级

可用来实现多种其他谓词

① [NOT] IN

② [NOT] BETWEEN … AND …

例22.查询信息系 (IS)、数学系(MA)和计算机科学系(CS)学生姓名和性别

SELECT Sname,Ssex
FROM Student
WHERE Sdept= 'IS' OR Sdept= 'MA' OR Sdept= 'CS’;

常用的查询条件：

例23：

1. 查询姓“张”且全名为两个汉字的所有男生的学号和姓名。

2. 查询年龄在20~25岁（包括20和25岁）之间，系别是“IS”的学生的姓名和年龄。

3. 查询考试成绩90分以上的学生学号。

SELECT Sno,Sname
FROM Student
WHERE Sname LIKE '张_ _' AND Ssex = '男';

SELECT Sname,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 25 AND Sdept='CS';

SELECT DISTINCT Sno
FROM SC
WHERE Grade>90;

3.ORDER　BY语句

①可以按一个或多个属性列排序

②ASC (升序, default)

③DESC(降序)

当排序列含空值时

①ASC：排序列为空值的元组最后显示

②DESC：排序列为空值的元组最先显示

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

SELECT Sno，Grade
FROM SC
WHERE Cno= '3'
ORDER BY Grade DESC;

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

SELECT *
FROM Student
ORDER BY Sdept,Sage DESC;

4.聚集函数

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

例26. 查询学生总人数

SELECT COUNT(*)
FROM Student;

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

//注意避免重复
SELECT COUNT(DISTINCT Sno)
FROM SC;

例28. 计算1号课程的学生平均成绩

SELECT AVG(Grade)
FROM SC
WHERE Cno= '1';

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

SELECT MAX(Grade)
FROM SC
WHERE Cno= '1';

例30. 查询学生200215012选修课程的总学分数

SELECT SUM(Ccredit)
FROM SC,Course
WHERE Sno='200215012' AND SC.Cno=Course.Cno;

5.GROUP BY 子句

将查询结果按某一列或多列的值分组，值相等的为一组

对查询结果分组的目的是为了细化聚集函数的作用对象

①未对查询结果分组，聚集函数将作用于整个查询结果

②对查询结果分组后，聚集函数将分别作用于每个组

③作用对象是查询的中间结果表

例32. 查询选修了3门以上课程的学生学号

SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) >3;

注:HAVING短语与WHERE子句的区别:

作用对象不同

①WHERE子句作用于基本表或视图，从中选择满足条件的元组

②HAVING短语作用于组，从中选择满足条件的组

二.连接查询

连接查询：同时涉及多个表的查询

连接条件或连接谓词：连接两个表的条件

一般格式

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

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

1.等值与非等值连接查询

等值连接：连接运算符为=

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

SELECT Student.*,SC.*
FROM Student, SC
WHERE Student.Sno = SC.Sno;

自然连接

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

• 例 34. 对 [ 例 33] 用自然连接完成

SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student, SC
WHERE Student.Sno = SC.Sno;

例 35. 查询选修 2 号课程且成绩在90分以上的所有学生

SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno AND SC.Cno= ‘2’ AND SC.Grade > 90;

2.自身连接

一个表与其自己进行连接

需要给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀

例 36. 查询每一门课的 间接先修课 （即 先修课的先修课 ）

SELECT FIRST.Cno, SECOND.Cpno
FROM Course FIRST, Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;

3.外连接

外连接与普通连接的区别

①普通连接操作只输出满足连接条件的元组

②外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出

分类:

左外连接：列出左边关系所有元组

右外连接：列出右边关系所有元组

例 37. 改写 [ 例 33] 查询每个学生及其选修课程。要将所有学生列出来，没选课的也要列出来。

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

4.多表连接

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

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

SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course /*多表连接*/
WHERE Student.Sno = SC.Sno
AND SC.Cno = Course.Cno;