乐趣区

关于数据库:数据库系统概论王珊第三章关系数据库标准语言SQL第四节数据查询

jiezi
  • pdf 下载：明码 7281
  • 专栏目录首页：【专栏必读】（考研复试）数据库系统概论第五版（王珊）专栏学习笔记目录导航及课后习题答案详解
  • 对于数据库如何装置，表如何建设这里不再介绍，请移步：（数据库系统概论 | 王珊）第三章关系数据库规范语言 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}$”。带有 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;
退出移动版