作者:whisper
链接:http://proprogrammar.com:443/article/559
声明:请尊重原作者的劳动,如需转载请注明出处
语句格式
SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …
FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)
[AS]<别名>
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];
查询指定列
[例3.16] 查询全体学生的学号与姓名。
SELECT Sno,Sname
FROM Student;
查询全部列
选出所有属性列:
[例3.18] 查询全体学生的详细记录
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student;
或
SELECT *
FROM Student;
查询经过计算的值
“虚” 列
SELECT子句的<目标列表达式>不仅可以为表中的属性列, 也可以是表达式
[例3.19] 查全体学生的姓名及其出生年份。
SELECT Sname,2016-Sage
FROM Student;
[例3.20] 查询全体学生的姓名、 出生年份和所在的院系, 要求用小写字母表示系名。
SELECT Sname,'Year of Birth: ',2014-Sage,LOWER(Sdept)
FROM Student;
输出结果:
使用列别名改变查询结果的列标题:
SELECT Sname NAME,'Year of Birth:' BIRTH,
2014-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT
FROM Student;
输出结果:
消除取值重复的行
如果没有指定DISTINCT关键词,则缺省为ALL
[例3.21] 查询选修了课程的学生学号。
SELECT Sno FROM SC;
结果为:
指定DISTINCT关键词,去掉表中重复的行
SELECT DISTINCT Sno
FROM SC;
执行结果:
(2)查询满足条件的元组
① 比较大小
[例3.22] 查询计算机科学系全体学生的名单。
SELECT Sname
FROM Student
WHERE Sdept=‘CS’;
[例3.23]查询所有年龄在20岁以下的学生姓名及其年龄。
SELECT Sname,Sage
FROM Student
WHERE Sage < 20;
[例3.24]查询考试成绩有不及格的学生的学号。
SELECT DISTINCT Sno
FROM SC
WHERE Grade<60;
② 确定范围
谓词: BETWEEN … AND …
NOT BETWEEN … AND …
[例3.25] 查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage BETWEEN 20 AND 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 ‘ <换码字符>’]
<匹配串>可以是一个完整的字符串, 也可以含有通配符%和 _
例如a_b表示以a开头, 以b结尾的长度为3的任意字符串
匹配串为固定字符串
[例3.29] 查询学号为201215121的学生的详细情况。
SELECT *
FROM Student
WHERE Sno LIKE ‘201215121';
等价于:
SELECT *
FROM Student
WHERE Sno = ' 201215121 ';
匹配串为含通配符的字符串
[例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 '刘%';
使用换码字符将通配符转义为普通字符
[例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来连接多个查询条件
[例3.38] 查询计算机系年龄在20岁以下的学生姓名。
SELECT Sname
FROM Student
WHERE Sdept= 'CS' AND Sage<20;
改写[例3.27]
[例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 ';
ORDER BY子句
对于空值, 排序时显示的次序由具体系统实现来决定
[例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] <列名>)
[例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 ] 查询学生201215012选修课程的总学分数。
SELECT SUM(Ccredit)
FROM SC,Course
WHERE Sno='201215012' AND SC.Cno=Course.Cno;
GROUP BY子句分组:
细化聚集函数的作用对象
[例3.46] 求各个课程号及相应的选课人数。
SELECT Cno, COUNT(Sno)
FROM SC
GROUP BY Cno;
查询结果可能为:
[例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子句的区别:
[练习1] 列出计算机系姓刘的同学的信息,按照学号大小排序
SELECT *
FROM Student
WHERE Sdept=‘CS’ AND Sname LIKE ‘刘%’
ORDER BY Sno;
[练习2] 按系并区分男女统计各系学生的人数、并按照人数降序排序
SELECT Sdept, Ssex,COUNT(Sno)
FROM Student
GROUP BY Sdept,Ssex
ORDER BY COUNT(Sno) DESC;
不像关系代数中“连接”是用一个特殊符号来表达的, 在SQL中“连接”是用“连接条件”来表达的。连接条件或连接谓词:用来连接两个表的条件
一般格式:
连接字段:连接谓词中的列名称
1.等值与非等值连接查询
2.自身连接
3.外连接
4.多表连接
等值连接:连接运算符为 “=”
[例 3.49] 查询每个学生及其选修课程的情况
SELECT Student.*, SC.*
FROM Student, SC
WHERE Student.Sno = SC.Sno;
查询结果:
自然连接
采用在SELECT中去掉重复字段的方式实施
[例 3.50] 对[例 3.49]用自然连接完成。
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno = SC.Sno;
(1) 嵌套循环法(NESTED-LOOP)
(2) 排序合并法(SORT-MERGE)
(3) 索引连接(INDEX-JOIN)
[例 3.51 ]查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。
一条SQL语句可以同时完成选择和连接查询,这时WHERE子句是由连接谓词和选择谓词组成的复合条件。
自身连接: 一个表与其自己进行连接,是一种特殊的连接
需要给表起别名以示区别
由于所有属性名都是同名属性,因此必须使用别名前缀
[例 3.52]查询每一门课的直接先修课的名称
SELECT FIRST.Cname , SECOND.Cname
FROM Course FIRST, Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;
查询结果:
外连接与普通连接的区别
列出左边关系中所有的元组
列出右边关系中所有的元组
[例 3. 53] 改写[例 3.49]
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUT JOIN SC ON
(Student.Sno=SC.Sno);
有些商业系统的表达更简单:
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student , SC
WHERE Student.Sno(+) =SC.Sno;
执行结果:
多表连接:两个以上的表进行连接
[例3.54]查询每个学生的学号、 姓名、 选修的课程名及成绩
SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course /*多表连接*/
WHERE Student.Sno = SC.Sno
AND SC.Cno = Course.Cno;
嵌套查询概述
◼ 一个SELECT-FROM-WHERE语句称为一个查询块
◼ 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
◼ 上层的查询块称为外层查询或父查询
◼ 下层查询块称为内层查询或子查询
◼ SQL语言允许多层嵌套查询
◼ 子查询的限制
⚫ 不能使用ORDER BY子句
嵌套查询求解方法
不相关子查询:
子查询的查询条件不依赖于父查询
◼ 由里向外 逐层处理。即每个子查询在上一级查询处理之前求解,子查询的结果用于建立其父查询的查找条件。
相关子查询:子查询的查询条件依赖于父查询
◼ 首先取外层查询中表的第一个元组,根据它与内层查询相关的属性值处理内层查询,若WHERE子句返回值为真,则取此元组放入结果表
◼ 然后再取外层表的下一个元组
◼ 重复这一过程,直至外层表全部检查完为止
1.带有IN谓词的子查询
2.带有比较运算符的子查询
3.带有ANY(SOME)或ALL谓词的子查询
4.带有EXISTS谓词的子查询
[例 3.55] 查询与“刘晨”在同一个系学习的学生。
此查询要求可以分步来完成
① 确定“刘晨”所在系名
SELECT Sdept
FROM Student
WHERE Sname= ' 刘晨 ';
结果为: CS
② 查找所有在CS系学习的学生。
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept= ' CS ';
结果为:
将第一步查询嵌入到第二步查询的条件中
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname= ' 刘晨 ');
此查询为不相关子查询。
用自身连接完成[例 3.55]查询要求
SELECT S1.Sno, S1.Sname,S1.Sdept
FROM Student S1,Student S2
WHERE S1.Sdept = S2.Sdept AND
S2.Sname = '刘晨';
[例 3.56]查询选修了课程名为“信息系统”的学生学号和姓名
用连接查询实现[例 3.56] :
SELECT Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno = SC.Sno AND
SC.Cno = Course.Cno AND
Course.Cname='信息系统';
当能确切知道内层查询返回单值时,可用比较运算符(>, <, =, >=, <=, !=或< >)。
在[例 3.55]中,
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept = /*由于一个学生只可能在一个系学习 , 用=代替*/
(SELECT Sdept
FROM Student
WHERE Sname= '刘晨');
[例 3.57 ]找出每个学生超过他选修课程平均成绩的课程号。
可能的执行过程
◼ 从外层查询中取出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;
◼ 执行该查询,得到结果:
然后外层查询取出下一个元组重复做上述①至③步骤,直到外层的SC元组全部处理完毕。
结果为:
(201215121,1)
(201215121,3)
(201215122,2)
在谓词逻辑中,还有存在量词和全称量词的概念,在SQL中并没有对应的表达,统一采用“谓词”来表达
方法一:引入ANY和ALL谓词,其对象为某个查询结果,表示其中任意一个值或者全部值
方法二:引入EXIST谓词,其对象也是某个查询结果,但表示这个查询结果是否为空,返回真值。
使用ANY或ALL谓词时必须同时使用比较运算
语义为:
[例 3.58] 查询非计算机科学系中比计算机科学系任意一个
学生年龄小的学生姓名和年龄
SELECT Sname,Sage
FROM Student
WHERE Sage < ANY (SELECT Sage
FROM Student
WHERE Sdept= ' CS ')
AND Sdept <> ‘CS ' ; /*父查询块中的条件 */
结果:
执行过程:
(1) 首先处理子查询,找出CS系中所有学生的年龄,构成一个集合(20,19)
(2) 处理父查询,找所有不是CS系且年龄小于20 或 19的学生
用聚集函数实现[例 3.58]
SELECT Sname,Sage
FROM Student
WHERE Sage <
(SELECT MAX(Sage)
FROM Student
WHERE Sdept= 'CS ')
AND Sdept <> ' CS ';
[例 3.59] 查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
方法一:用ALL谓词
SELECT Sname,Sage
FROM Student
WHERE Sage < ALL
(SELECT Sage
FROM Student
WHERE Sdept= ' CS ')
AND Sdept <> ' CS ’;
方法二:用聚集函数
SELECT Sname,Sage
FROM Student
WHERE Sage <
(SELECT MIN(Sage)
FROM Student
WHERE Sdept= ' CS ')
AND Sdept <>' CS ';
表3.7 ANY(或SOME), ALL谓词与聚集函数、 IN谓词的等价转换关系
EXISTS谓词
◼ 带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”。
⚫ 若内层查询结果非空,则外层的WHERE子句返回真值
⚫ 若内层查询结果为空,则外层的WHERE子句返回假值
◼ 由EXISTS引出的子查询,其目标列表达式通常都用 * ,因为带EXISTS的子查询只返回真值或假值,给出列名无实际意义。
[例 3.60]查询所有选修了1号课程的学生姓名。
思路分析:
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno= ' 1 ');
[例 3.61] 查询没有选修1号课程的学生姓名。
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno = Student.Sno AND Cno='1');
❖ 用EXISTS代替其他谓词
◼ 所有带IN谓词、比较运算符、 ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换
❖ 用EXISTS/NOT EXISTS实现全称量词(难点)
❖ 用EXISTS实现逻辑蕴含
◼ 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词:(x) P ≡ ( x( P))
[例 3.55]查询与“刘晨”在同一个系学习的学生。
可以用带EXISTS谓词的相关子查询替换:
SELECT Sno,Sname,Sdept
FROM Student S1
WHERE EXISTS
(SELECT *
FROM Student S2
WHERE S2.Sdept = S1.Sdept AND
S2.Sname = '刘晨');
[例 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语言中没有蕴涵(Implication) 逻辑运算
◼ 可以利用谓词演算将逻辑蕴涵谓词等价转换为:
[例 3.63]查询至少选修了学生201215122选修的全部课程的学生号码。
解题思路:
◼ 用逻辑蕴涵表达该查询:
用P表示谓词 “学生201215122选修了课程y”
用q表示谓词 “学生x选修了课程y”
则上述查询为: 对
(所有的课程y,只要201215122学生选修了课程y,则x也选修了y)
◼ 等价变换:
◼ 变换后语义:不存在这样的课程y,学生201215122选修了y,而学生x没有选。
集合操作的种类
参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同
[例 3.64] 查询计算机科学系的学生及年龄不大于19岁的学生。
SELECT *
FROM Student
WHERE Sdept= 'CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;
[例 3.65] 查询选修了课程1或者选修了课程2的学生。
SELECT Sno
FROM SC
WHERE Cno=' 1 '
UNION
SELECT Sno
FROM SC
WHERE Cno= ' 2 ';
[例3.66] 查询计算机科学系的学生与年龄不大于19岁的学生的交集。
SELECT *
FROM Student
WHERE Sdept='CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19
[例 3.66] 实际上就是查询计算机科学系中年龄不大于19岁的学生。
SELECT *
FROM Student
WHERE Sdept= 'CS' AND Sage<=19;
[例 3.67]查询既选修了课程1又选修了课程2的学生。
SELECT Sno
FROM SC
WHERE Cno=' 1 '
INTERSECT
SELECT Sno
FROM SC
WHERE Cno='2 ';
[例3.67]也可以表示为:
SELECT Sno
FROM SC
WHERE Cno=' 1 ' AND Sno IN
(SELECT Sno
FROM SC
WHERE Cno=' 2 ');
[例 3.68] 查询计算机科学系的学生与年龄不大于19岁的学生的差集。
SELECT *
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT *
FROM Student
WHERE Sage <=19;
[例3.68]实际上是查询计算机科学系中年龄大于19岁的学生
SELECT *
FROM Student
WHERE Sdept= 'CS' AND Sage>19;
SELECT [ALL|DISTINCT]
<目标列表表达式>[别名][,<目标列表达式>[别名]]…
FROM <表名或视图名>[别名]
[, <表名或视图名>[别名]]…
|(<SELECT语句>)[AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING<条件表达式>]]
[ORDER BY <列名2>[ASC|DESC]];
目标列表达式格式
(1) *
(2) <表名>.*
(3) COUNT([DISTINCT|ALL]* )
(4) [<表名>.]<属性列名表达式>[,<表名>.]<属性列名表达式>…
其中<属性列名表达式>可以是由属性列、作用于属性列的聚集函数和常量的任意算术运算(+, -, *, /)组成的运算公式
亲爱的读者:有时间可以点赞评论一下
全部评论