关系数据库标准语言SQL——第二卷(两万字,望得您一个赞)
通过第二卷要学会什么东西呢?
通过使用select语句
- 查询满足一定条件的元组
- 查询某些属性的值
- 使用表别名和列别名
- 利用DISTINCT去掉查询结果中的重复行
- 通过在WHERE子句中放入连接条件,进行多表连接查询
- 利用GROUP BY进行分组统计
- 利用ORDER BY对查询结果按要求排序
数据的查询
基本语法
SELECT [ALL|DISTINCT]〈目标列表达式〉[,〈目标列表达式>] …
FROM 〈表名或视图名〉[,〈表名或视图名〉] …
[WHERE <条件表达式>]
[GROUP BY 〈列名〉[, <列名>] …[HAVING <内部函数表达式>] ]
[ORDER BY 〈列名〉 [ASC│DESC] [,〈列名〉[ASC│DESC]]…]
子句功能
- SELECT子句与FROM子句是必选子句
- SELECT子句:指定要显示的属性列
- FROM子句:指定查询对象(基本表或视图)
- WHERE子句:指定查询条件
- GROUP BY子句:对查询结果按指定列的值分组,该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。
- HAVING短语:只有满足指定条件的组才予以输出(来限制分组滞后于的条件的,结合groupby使用)
- ORDER BY子句:对查询结果表按指定列值的升序或降序排序
SELECT语句的含义
- 根据WHERE子句中的条件表达式,从FROM子句中的基本表或视图中找出满足条件的元组
- 按SELECT子句中的目标字段,选出元组中的分量形成结果表
- GROUP BY子句将结果按字段分组,每个组产生结果表中的一个元组
- 通常在每组中作用库函数,分组的附加条件用HAVING短语给出只有满足内部函数表达式的组才予输出
- 如果有ORDER BY子句,则结果表要根据指定的字段按升序或降序排列
查询仅涉及一个表
查询指定列
- [例3.16] 查询全体学生的学号与姓名。
SELECT Sno,Sname
FROM Student;
- [例3.17] 查询全体学生的姓名、学号、所在系。
SELECT Sname,Sno,Sdept
FROM Student;
查询全部列
-
选出所有属性列:
在SELECT关键字后面列出所有列名
将<目标列表达式>指定为 * -
[例3.18] 查询全体学生的详细记录
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student;
或
SELECT *
FROM Student;
查询经过计算的值
- SELECT子句的<目标列表达式>不仅可以为表中的属性列,也可以是表达式。
- [例3.19] 查全体学生的姓名及其出生年份
SELECT Sname,2022-Sage
FROM Student;
输出结果:
SELECT Sname,2022 – Sage Birthday
FROM Student;
注意: 这个里面的Birthday是给2011-Sage这个列起的一个别名(当然啦,可以起也可以不起)!!!
常量或函数
- <目标列表达式>不仅可以是算术表达式,还可以是字符串常量、函数等。
- [例]在每个学生的姓名后面显示字符串 2019
SELECT Sname,'2019' Enter
FROM Student;
输出结果:
- [例]查询全体学生的人数
SELECT count(Sname) 学生人数
FROM Student;
注意:这里的“学生人数也是一个别名哦!”
消除取值重复的行
如果没有指定DISTINCT关键词,则缺省为ALL
下图为SC学生选课表:
- [例3.21] 查询选修了课程的学生学号。
SELECT Sno FROM SC;
--等价于:
SELECT ALL Sno FROM SC;
执行上面的SELECT语句后,结果为:
而当指定了distinct后
SELECT DISTINCT Sno
FROM SC;
执行结果:
查询满足条件的元组
where子句常用的查询条件
再吧student表搬过来方便大家观看
- [例3.22] 查询计算机科学系(CS)全体学生的名单。
SELECT Sname
FROM Student
WHERE Sdept=‘CS’;
- [例3.23]查询所有年龄在20岁以下的学生姓名及其年龄。
SELECT Sname,Sage
FROM Student
WHERE Sage < 20;
- [例3.24]查询考试成绩有不及格的学生的学号。
这个就要考虑一下是不是一名学生可能好几门成绩不合格(希望不是你哦),所以要对查询出来的表进行去重操作,distinct
SELECT DISTINCT Sno
FROM SC
WHERE Grade<60;
练习一下
- 查询性别为女的学生的学号、姓名
- 查询学分为4学分的课程的名字
- 查询成绩在85分以上的学生的学号
select sno,sname from student where ssex='女';
select cname from course where ccredit=4;
select distinct sno from sc where grade>85;
确定范围
谓词: BETWEEN … AND … 查找属性值在指定范围内的元祖
NOT BETWEEN … AND … 查找属性值不在指定范围内的元祖
- [例3.25] 查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;
--或者
--where Sage>=20 and Sage<=23;
--根据个人喜好来吧
- [例3.26] 查询年龄不在20~23岁之间的学生姓名、系别和年龄
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;
确定集合
谓词:IN <值表> 查找属性值属于指定集合的元祖
NOT IN <值表> 查找属性值不属于指定集合的元祖
<值表>:用逗号分隔的一组取值
- [例3.27]查询计算机科学系(CS)、数学系(MA)和信息系(IS)学生的姓名和性别。
SELECT Sname, Ssex
FROM Student
WHERE Sdept IN ('CS','MA’,'IS' );
- [例3.28]查询既不是计算机科学系、数学系,也不是信息系的学生的姓名和性别。
SELECT Sname, Ssex
FROM Student
WHERE Sdept NOT IN ('IS','MA’,'CS' );
字符匹配
谓词: [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]
<匹配串>可以是一个完整的字符串,也可以含有通配符%和 _
- % (百分号) 代表任意长度(长度可以为0)的字符串
- 例如a%b表示以a开头,以b结尾的任意长度的字符串
- _ (下横线) 代表任意单个字符。
- 例如a_b表示以a开头,以b结尾的长度为3的任意字符串
匹配串为固定字符串
- [例3.29] 查询学号为202015121的学生的详细情况。
SELECT *
FROM Student
WHERE Sno LIKE ‘202015121';
等价于:
SELECT *
FROM Student
WHERE Sno = ‘202015121';
匹配串为含通配符的字符串
- [例3.30] 查询所有姓刘学生的姓名、学号和性别。
SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname LIKE '刘%';
- [例3.31] 查询姓"欧阳"且全名为三个汉字的学生的姓名。
SELECT Sname
FROM Student
WHERE Sname LIKE '欧阳__’;
匹配串为含通配符的字符串
- [例3.32] 查询名字中第2个字为"阳"字的学生的姓名和学号。
SELECT Sname,Sno
FROM Student
WHERE Sname LIKE '__阳%';
- [例3.33] 查询所有不姓刘的学生姓名、学号和性别。
SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname NOT LIKE '刘%';
使用换码字符 ESCAPE 将通配符转义为普通字符
- [例3.34] 查询DB_Design课程的课程号和学分。
SELECT Cno,Ccredit
FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE '\ ' ;
- [例3.35] 查询以"DB_"开头,且倒数第3个字符为 i的课程的详细情况。
SELECT *
FROM Course
WHERE Cname LIKE 'DB\_%i_ _' ESCAPE '\ ' ;
ESCAPE ‘\’ 表示“ \” 为换码字符
涉及空值的查询
谓词: IS NULL 或 IS NOT NULL
“IS” 不能用 “=” 代替
- [例3.36] 某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。
SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL;
- [例3.37] 查所有有成绩的学生学号和课程号。
SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL;
多重条件查询
逻辑运算符:AND和 OR来连接多个查询条件
AND的优先级高于OR
可以用括号改变优先级
- [例3.38] 查询计算机系年龄在20岁以下的学生姓名。
SELECT Sname
FROM Student
WHERE Sdept= 'CS' AND Sage<20;
IN 谓词实际上是多个 OR 运算符的缩写
- [例3.27] 查询计算机科学系(CS)、数学系(MA)和信息系(IS)学生的姓名和性别。
SELECT Sname, Ssex
FROM Student
WHERE Sdept IN ('CS ','MA ','IS')
--可改写为:
SELECT Sname, Ssex
FROM Student
WHERE Sdept= ' CS' OR Sdept= ' MA' OR Sdept= 'IS ';
就跟集合取并集似的,是这三个系的学生所以要是并集,所以用or哦
order by 子句
ORDER BY子句
- 可以按一个或多个属性列排序
- 升序:ASC; 降序:DESC; 缺省值为升序
对于空值,排序时显示的次序由具体系统实现来决定
当按多个属性排序时
-
首先根据第一个属性排序,如果在该属性上有多个相同值时,则按第二个属性排序,以此类推
-
[例3.39]查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。
SELECT Sno, Grade
FROM SC
WHERE Cno= ' 3 '
ORDER BY Grade DESC;
- [例3.40]查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
SELECT *
FROM Student
ORDER BY Sdept, Sage DESC;
聚集函数
- 统计元组个数 COUNT(*)
- 统计一列中值的个数 COUNT([DISTINCT|ALL] <列名>)
- 计算一列值的总和(此列必须为数值型)SUM([DISTINCT|ALL] <列名>)
- 计算一列值的平均值(此列必须为数值型)AVG([DISTINCT|ALL] <列名>)
- 求一列中的最大值 MAX([DISTINCT|ALL] <列名>)
- 求一列中的最小值 MIN([DISTINCT|ALL] <列名>)
统计元组个数 COUNT(*)
-
直接count(*),适用于得到结果的行数,并不是列中不重复值的个数
-
使用count(distinct 列名) 是得到列中不重复值的个数
-
[例3.41] 查询学生总人数。
SELECT COUNT(*)
FROM Student;
- [例3.42] 查询选修了课程的学生人数。
SELECT COUNT(DISTINCT Sno)
FROM SC;
考虑到同一个学生可能会选择好几门课程(比如说这么爱学习的你),所以统计数量时候要去重
- [例3.43] 计算1号课程的学生平均成绩。
SELECT AVG(Grade)
FROM SC;
where Cno='1';
- [例3.44] 查询选修1号课程的学生最高分数。
SELECT MAX(Grade)
FROM SC
WHERE Cno='1’;
- [例3.45 ] 查询学生202015012选修课程的总学分数。
SELECT SUM(Ccredit)
FROM SC,Course
WHERE Sno=‘202015012' AND SC.Cno=Course.Cno;
主要的聚集函数
- DISTINCT短语:在计算时要取消指定列中的重复值
- ALL短语:缺省值,不取消重复值
group by子句
将查询结果按某一列或多列的值分组,值相等的为一组
作用:细化聚集函数的作用对象,先分组,然后分别对每个组使用聚集函数
-
如果未对查询结果分组,聚集函数将作用于整个查询结果
-
分组后聚集函数将分别作用于每个组,即每一组都有一个函数值
-
group by 必须和聚集函数一起使用
-
PS:如果有聚集函数的select语句,列名部分只能包含分组列和聚合列。
-
[例3.46] 求各个课程号及相应的选课人数。
SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno;
查询结果:
如分组后还要按一定条件对组进行筛选,最终只输出满足指定条件的组,则使用 HAVING 短语指定筛选条件。
- [例3.47] 查询选修了3门以上课程的学生学号。
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) >3;
- [例3.48 ]查询平均成绩大于等于90分的学生学号和平均成绩
SELECT Sno, AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90 GROUP BY Sno;
--错误,因为WHERE子句中不能用聚集函数作为条件表达式
正确的查询语句应该是:
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;
HAVING 短语与 WHERE 子句的区别:
- 作用对象不同
- WHERE子句作用于基表或视图,从中选择满足条件的元组,选择条件不能有聚集函数
- HAVING短语作用于组,从中选择满足条件的组。选择条件必须有聚集函数
练习
- 查询计算机系(CS)姓刘的学生信息,按照学号大小排序
- 按系并区分男女统计各系学生的人数,并按照人数降序排序
--查询计算机系姓刘的同学,按学号生序排序
select * from student where sdept='cs' and sname like '刘%' order by sno;
--按系并区分男女统计学生的人数,并按照人数降序排列
select sdept,ssex,count(sno) from student group by sdept,ssex order by count(sno) desc;
连接查询
- 同时涉及多个表的查询称为连接查询,若没有连接条件,代表笛卡尔积
- 用来连接两个表的条件称为连接条件或连接谓词,其一般格式为:
[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
比较运算符:=、>、<、>=、<=、!=
连接字段
- 连接谓词中的列名称为连接字段
- 连接条件中的各连接字段类型必须是可比的,但不必是相同的
一种可能执行步骤
-
首先在表1中找到第一个元组,然后从头开始扫描表2,逐一查找满足连接条件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组
-
表2全部查找完后,再找表1中第二个元组,然后再从头开始扫描表2,逐一查找满足连接条件的元组,找到后就将表1中的第二个元组与该元组拼接起来,形成结果表中一个元组
-
重复上述操作,直到表1中的全部元组都处理完毕
-
若连接运算符为 = 时,称为等值连接
-
使用其他运算符时,称为非等值连接
-
在等值连接中,去掉目标列中的重复属性则为自然连接
学生表:student
课程表:course:
学生选课表:SC:
[例 3.49] 查询每个学生及其选修课程的情况。
SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno = SC.Sno;
[例 3.50] 对[例 3.49]用自然连接完成。
SELECT Student.Sno, Sname , Ssex , Sage , Sdept , Cno , Grade
FROM Student , SC
WHERE Student.Sno = SC.Sno;
WHERE子句中含多个连接条件时,称为复合条件连接
[例3.51] 查询选修2号课程且成绩在90分以上的所有学生的学号、姓名。
SELECT Student.Sno, student.Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno AND /* ‘=’连接谓词*/
SC.Cno= ‘ 2 ’ AND /*‘AND’选择谓词*/
SC.Grade > 90; /* 其他限定条件 */
一个表与其自己进行连接,称为表的自身连接
- 需要给表起别名以示区别
- 由于所有属性名都是同名属性,因此必须使用别名前缀
[例 3.52] 查询每一门课的间接先修课(即先修课的先修课)。
SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;
FIRST表:(course表)
SECOND表(course表)
查询结果:
外连接与普通连接的区别
- 普通连接操作只输出满足连接条件的元组
- 外连接操作以指定表为连接主体,将主体表中不满足连接条件的元组一并输出
[例 3. 53] 查询每个学生及其选修课程的情况包括没有选修课程的学生----用外连接操作。
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC
ON Student.Sno = SC.Sno;
[例] 用外连接、左连接、右连接完成。
外连接:
SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST FULL OUTER JOIN Course SECOND
ON FIRST.Cpno = SECOND.Cno;
左连接:
SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST LEFT OUTER JOIN Course SECOND
ON FIRST.Cpno = SECOND.Cno;
右连接:
SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST RIGHT OUTER JOIN Course SECOND
ON FIRST.Cpno = SECOND.Cno;
外连接:
- 在表名后面加外连接操作符指定主体表
- 非主体表有一“万能”的虚行,该行全部由空值组成
- 虚行可以和主体表中所有不满足连接条件的元组进行连接
- 由于虚行各列全部是空值,因此与虚行连接的结果中,来自非主体表的属性值全部是空值
左外连接
- 左外连接符为left outer join
- 列出左边关系中所有的元组
外连接
- 外连接符为full outer join
- 列出左右两边关系中所有的元组
右外连接
- 右外连接符为right outer join
- 列出右边关系中所有的元组
多表连接:两个以上的表进行连接
[例3.54] 查询每个学生的学号、姓名、选修的课程名及成绩。
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;
嵌套查询(IN谓词,即把一个查询的结果用于另一个查询的条件)
- 一个SELECT-FROM-WHERE语句称为一个查询块
- 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
- 子查询的限制:不能使用ORDER BY子句
- 层层嵌套方式反映了 SQL语言的结构化
- 有些嵌套查询可以用连接运算替代
SELECT Sname /*外层查询/父查询*/
FROM Student
WHERE Sno IN
(SELECT Sno /*内层查询/子查询*/
FROM SC
WHERE Cno= ' 2 ');
通俗的意思就是外部查询要A,那么我内部查询就去查A,然后交给外部查询
不相关子查询(子查询可以独立执行)
- 子查询的查询条件不依赖于父查询
- 是由里向外逐层处理。即每个子查询在上一级查询处理之前求解,子查询的结果用于建立其父查询的查找条件。
相关子查询(自查询不能独立执行)
- 子查询的查询条件依赖于父查询
- 首先取外层查询中表的第一个元组,根据它与内层查询相关的属性值处理内层查询,若WHERE子句返回值为真,则取此元组放入结果表;
- 然后再取外层表的下一个元组;
- 重复这一过程,直至外层表全部检查完为止。
子查询的谓词
- 带有IN谓词的子查询
- 带有比较运算符的子查询
- 带有ANY或ALL谓词的子查询
- 带有EXISTS谓词的子查询
带有IN谓词的子查询
[例 3.55] 查询与“刘晨”在同一个系学习的学生。
查询要求可以分步来完成
第一步: 确定“刘晨”所在系名
SELECT Sdept
FROM Student
WHERE Sname= ' 刘晨 ';
第二步:查找所有在CS系学习的学生
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept= ' CS ';
构造嵌套查询:
- 将第一步查询嵌入到第二步查询的条件中
- 此查询为不相关子查询。DBMS求解该查询时也是分步去做的。
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname= ‘ 刘晨 ’);
[例 3.56] 查询选修了课程名为“信息系统”的学生学号和姓名。
SELECT Sno,Sname
FROM Student
WHERE Sno IN
--最后在Student关系中取出Sno和Sname
(
--然后在SC关系中找出选修了3号课程的学生学号
SELECT Sno
FROM SC
WHERE Cno IN
(
--首先在Course关系中找出“信息系统”的课程号,结果为3号
SELECT Cno
FROM Course
WHERE Cname= ‘信息系统’));
当能确切知道内层查询返回单个值时,可用比较运算符(>,<,=,>=,<=,!=或< >)。
与ANY或ALL谓词配合使用
[例3.57] 找出每个学生超过他选修课程平均成绩的课程号。
SELECT Sno, Cno
FROM SC x
WHERE Grade >
(SELECT AVG(Grade)
FROM SC y
WHERE y.Sno = x.Sno);
可能的执行过程:
- S1:从外层查询中取出SC的一个元组x,将元组x的Sno值(202015121)传送给内层查询。
SELECT AVG(Grade)
FROM SC y
WHERE y.Sno=‘202015121';
- S2:执行内层查询,得到值88(近似值),用该值代替内层查询,得到外层查询:
SELECT Sno, Cno
FROM SC x
WHERE Grade >=88;
-
S3:执行这个查询,得到
(202015121,1)
(202015121,3) -
S4:外层查询取出下一个元组重复做上述1至3步骤,直到外层的SC元组全部处理完毕。结果为:
(202015121,1)
(202015121,3)
(202015122,2)
- 内层结果返回属性值的集合,需要用ANY或ALL谓词
谓词语义
- ANY:任意一个值
- ALL:所有值
- 需要配合使用的运算符
[例3.58] 查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄。
SELECT Sname,Sage
FROM Student
WHERE Sage < ANY (等同于<max)
(SELECT Sage
FROM Student
WHERE Sdept= ' IS ')
AND Sdept <> ' IS ' ; /* 注意这是父查询块中的条件 */
[例3.59] 查询其他系中比计算机科学系所有学生年龄小的学生姓名和年龄
SELECT Sname,Sage
FROM Student
WHERE Sage < ALL
(SELECT Sage
FROM Student
WHERE Sdept= ' CS ')
AND Sdept <> ' CS ' ;
带有EXISTS谓词的子查询
EXISTS 谓词—用在 where 后面,满足条件的元组留下,剩下的去掉
- 存在量词
- 带有 EXISTS 谓词的子查询不返回任何数据,只产生逻辑值“true”或 “false ”
若内层查询结果非空,则外层的 WHERE 子句返回真值
若内层查询结果为空,则外层的 WHERE 子句返回假值 - 由 EXISTS 引出的子查询,其目标列表达式通常都用 * ,因为带 EXISTS 的子查询只返回真值或假值,给出列名无实际意义
[例3.60] 查询所有选修了1号课程的学生姓名。
思路分析:
- 本查询涉及Student和SC关系。
- 在Student中依次取每个元组的Sno值,用此值去检查SC关系。
- 若SC中存在这样的元组,其Sno值等于此Student.Sno值,并且其Cno= ‘1’,则取此Student.Sname送入结果关系。
SELECT Sname --选择满足条件的学生姓名
FROM Student
WHERE EXISTS --什么条件?
(SELECT * --student的学生,选修1号课程
FROM SC /*相关子查询*/
WHERE Sno=Student.Sno AND Cno= '1');
NOT EXISTS谓词
- 若内层查询结果非空,则外层的WHERE子句返回假值
- 若内层查询结果为空,则外层的WHERE子句返回真值
[例3.61] 查询没有选修1号课程的学生姓名。
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno = Student.Sno
AND Cno='1');
不同形式的查询间的替换
- 一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
- 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。
[例3.55] 查询与“刘晨”在同一个系学习的学生
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname= ‘ 刘晨 ’);
SELECT Sno,Sname,Sdept
FROM Student S1
WHERE EXISTS
(SELECT *
FROM Student S2
WHERE S2.Sdept = S1.Sdept AND
S2.Sname = '刘晨 ';
用 EXISTS/NOT EXISTS 实现全称量词
- SQL语言中没有全称量词
(For all) - 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词
[例3.62] 查询选修了全部课程的学生姓名。
SELECT Sname --找学生
FROM Student
WHERE NOT EXISTS --满足什么条件的学生?
(SELECT * --找课程
FROM Course
WHERE NOT EXISTS -- 满足什么条件的课程?
(SELECT * --找满足的选课记录
FROM SC
WHERE Sno= Student.Sno AND --某个学生选择某个课程
Cno= Course.Cno)
);
用EXISTS/NOT EXISTS实现逻辑蕴函
-
SQL语言中没有蕴函逻辑运算
-
可以利用谓词演算将逻辑蕴函谓词等价转换为:
p成立,q就成立 => p不成立或者q成立
[例3.63] 查询至少选修了学生202015122选修的全部课程的学生号码。
解题思路:
- 查询学号为x的学生,对所有的课程y,只要202015122学生选修了课程y,则x也选修了y。
- 形式化表示:
用P表示谓词 “学生202015122选修了课程y”
用q表示谓词 “学生x选修了课程y”
则上述查询为:
等价变换:
- 变换后语义:不存在这样的课程y,学生202015122选修了y,而学生x没有选。
- 用NOT EXISTS谓词表示
[例3.63] 查询至少选修了学生202015122选修的全部课程的学生号码。
SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
(SELECT *
FROM SC SCY
WHERE SCY.Sno = ‘ 202015122 ' AND
NOT EXISTS
(SELECT *
FROM SC SCZ
WHERE SCZ.Sno=SCX.Sno AND
SCZ.Cno=SCY.Cno));
集合查询
并操作
形式:
<查询块>
UNION
<查询块>
- 参加UNION操作的各结果表的列数必须相同;对应项的数据类型也必须相同
- UNION:将多个查询结果合并起来时,系统自动去掉重复元组。
- UNION ALL:将多个查询结果合并起来时,保留重复元组
[例3.64] 查询计算机科学系的学生及年龄不大于19岁的学生。
方法一:
SELECT *
FROM Student
WHERE Sdept= 'CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;
方法二:
SELECT DISTINCT *
FROM Student
WHERE Sdept= 'CS' OR Sage<=19;
[例3.66] 查询计算机科学系的学生与年龄不大于19岁的学生的交集(INTERSECT) 。
SELECT *
FROM Student
WHERE Sdept='CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19
请使用连接查询写出等价脚本
SELECT *
FROM Student
WHERE Sdept= 'CS' AND
Sage<=19;
[例3.68] 查询计算机科学系的学生与年龄不大于19岁的学生的差集。
SELECT *
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT *
FROM Student
WHERE Sage <=19;
请使用连接查询写出等价脚本
SELECT *
FROM Student
WHERE Sdept= 'CS' AND
Sage>19;
- ORDER BY子句只能用于对最终查询结果排序,不能对中间结果排序
- 任何情况下,ORDER BY子句只能出现在最后
- 对集合操作结果排序时,ORDER BY子句中用数字指定排序属性
SELECT *
FROM Student
WHERE Sdept= 'CS'
ORDER BY Sno
UNION
SELECT *
FROM Student
WHERE Sage<=19
ORDER BY Sno;
SELECT *
FROM Student
WHERE Sdept= 'CS‘
UNION
SELECT *
FROM Student
WHERE Sage<=19
ORDER BY Sno;
SELECT [ALL|DISTINCT]
<目标列表达式> [别名] [ ,<目标列表达式> [别名]] …
FROM <表名或视图名> [别名] [ ,<表名或视图名> [别名]] …
[WHERE <条件表达式>]
[GROUP BY <列名1>[,<列名1’>] …
[HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC] [,<列名2’> [ASC|DESC] ] … ];
整条语句的含义:
- 根据WHERE子句的条件表达式,从FROM子句指定的基本表或视图中找出满足条件的元组,再按SELECT子句中的目标列表达式,选出元组中的属性值形成结果表。
- 如果有GROUP子句,则将结果按<列名1>的值进行分组,该属性列值相等的元组为一个组,每个组产生结果表中的一条记录,通常会在每组中使用集函数。如果GROUP子句带HAVING短语,则只有满足指定条件的组才输出。如果有ORDER子句,则结果表还要按<列名2>的值的升序或降序排列。