ReView关系数据库标准语言SQL查询语句

数据库
数据库
发布日期:   2022-03-21

思维导图

表格数据准备

SC表

Sno Cno Grade
200215121 1 92
200215121 2 54
200215121 3 88
200215122 2 57
200215122 3 80

Student表

Sno Sname Ssex Sage Sdept
200215121 李勇 男 20 CS
200215122 刘晨 女 19 CS
200215123 王敏 女 23 MA
200215125 张立 男 19 IS
200215126 张红 女 19 CS

Course表

Cno Cname Cpno Ccredit
1 数据库 5 4
2 数学 NULL 4
3 信息系统 1 4
4 操作系统 6 3
5 数据结构 7 4
6 数据处理 NULL 2
7 PASCAL语言 6 2
8 Visual_Basic NULL 4

代码测试

聚集函数、GROUP BY、HAVING

  • GROUP BY子句将查询结果按一列或多列的值分组，值相等的为一组
  • 对查询结果分组的目的是为了细化聚集函数的作用对象
  • 分组后聚集函数将作用与每一个组，既每一个组都有一个函数值。
SELECT Cno,COUNT(Sno) 选课人数 from SC GROUP BY Cno;
-- 对Cno进行分组，将所有的Cno值的元组为一组，然后对每组作用聚集函数COUNT进行计算
-- 以求得课程号及相应的选课人数
Cno 选课人数
1 1
2 2
3 2 
SELECT Sno FROM SC GROUP BY Sno HAVING Count(*)>=3;
-- 这里先用GROUP BY子句按Sno进行分组，再用聚集函数COUNT对每一组计数；
-- HAVING短语给出了选择组的条件，只有满足了条件的组才能选出来
-- 分组后还要求按一定条件对这些组进行筛选[用HAVING]，最终只输出满足指定条件的组。
Sno
200215121 
-- 例子
SELECT Sno,AVG(Grade) 平均分 FROM SC GROUP BY Sno HAVING AVG(Grade)>55;
-- 先用GROUP BY将Sno进行分组，在用平均函数算出每一组的平均数
-- HAVING短语给出了条件，只有平均值大于55的一组才被查询出来
-- 这个地方不能用where
Sno 平均分
200215121 78
200215122 68

连接查询

等值连接

-- 等值连接
--查询SC表所有在Student表中的信息
SELECT Student.*,SC.* FROM Student,SC WHERE Student.Sno=SC.Sno;
Sno Sname Ssex Sage Sdept Sno Cno Grade
200215121 李勇 男 20 CS 200215121 1 92
200215121 李勇 男 20 CS 200215121 2 54
200215121 李勇 男 20 CS 200215121 3 88
200215122 刘晨 女 19 CS 200215122 2 57
200215122 刘晨 女 19 CS 200215122 3 80

自然连接

-- 自然连接
SELECT Student.*,SC.Cno,SC.Grade FROM Student,SC WHERE Student.Sno=SC.Sno;
Sno Sname Ssex Sage Sdept Cno Grade
200215121 李勇 男 20 CS 1 92
200215121 李勇 男 20 CS 2 54
200215121 李勇 男 20 CS 3 88
200215122 刘晨 女 19 CS 2 57
200215122 刘晨 女 19 CS 3 80

外连接

-- 外连接
SELECT Student.*,SC.Cno,SC.Grade FROM Student LEFT OUTER JOIN SC ON(Student.Sno=SC.Sno);
Sno Sname Ssex Sage Sdept Cno Grade
200215121 李勇 男 20 CS 1 92
200215121 李勇 男 20 CS 2 54
200215121 李勇 男 20 CS 3 88
200215122 刘晨 女 19 CS 2 57
200215122 刘晨 女 19 CS 3 80
200215123 王敏 女 23 MA NULL NULL
200215125 张立 男 19 IS NULL NULL
200215126 张红 女 19 CS NULL NULL

多表连接

-- 多表连接
SELECT Student.Sno,Sname,Cname,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;
Sno Sname Cname Grade
200215121 李勇 数据库 92
200215121 李勇 数学 54
200215121 李勇 信息系统 88
200215122 刘晨 数学 57
200215122 刘晨 信息系统 80

嵌套查询

在SQL语言中，一个SELECT-FROM-WHERE的语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询（nested query）

-- 带有IN的子查询
SELECT Sname FROM Student WHERE Sno IN(SELECT Sno FROM SC WHERE Cno='2'); -- 不相关子查询
-- 此查询语句意味这查出选修课选修了二号课程的学生姓名

先执行内查询SELECT Sno FROM SC WHERE Cno='2'找到选修了二号课程的学生

Sno
200215121
200215122

在通过内查询的结果执行SELECT Sname FROM Student WHERE Sno IN(内查询结果)，查询选修了二号课程学生的姓名

Sname
李勇
刘晨 
-- 带有IN的子查询
SELECT Sno,Sname,Sdept FROM Student WHERE Sdept IN(SELECT Sdept FROM Student WHERE Sname='刘晨'); -- 不相关子查询

先执行内查询SELECT Sdept FROM Student WHERE Sname='刘晨'找到刘晨的系别

Sdept
CS

再执行外查询SELECT Sno,Sname,Sdept FROM Student WHERE Sdept IN(内查询结果)，查询再Student表中系别和刘晨系别一样的同学学号，姓名，系别

Sno Sname Sdept
200215121 李勇 CS
200215122 刘晨 CS
200215126 张红 CS 
--- 同理，不再展示结果了
SELECT Sno,Sname FROM Student WHERE Sno IN(SELECT Sno FROM SC WHERE Cno IN(SELECT Cno FROM Course WHERE Cname='信息系统')); -- 不相关子查询
-- 带有比较运算符的子查询

--- 带有比较字符的子查询
SELECT Sno,Sname,Sdept FROM Student WHERE Sdept=(SELECT Sdept FROM Student WHERE Sname='刘晨');
Sno Sname Sdept
200215121 李勇 CS
200215122 刘晨 CS
200215126 张红 CS 
-- 相关子查询
SELECT Sno,Cno FROM SC x WHERE Grade>=(SELECT AVG(Grade) FROM SC y WHERE Y.Sno=x.Sno);
-- 从外层查询中取出SC的一个元组x，将元组x的Sno值（201215121）传送给内层查询
-- 执行内层查询，得到值,用该值代替内层查询，得到外层查询
-- 然后外层查询取出下一个元组重复做上述步骤的处理，直到外层的SC元组全部处理完毕。
Sno Cno
200215121 1
200215121 3
200215122 3 
-- 带有ANY(SOME)或ALL谓词的子查询
SELECT Sname,Sage FROM Student WHERE Sage<ANY(SELECT Sage FROM Student WHERE Sdept='CS') AND Sdept<>'CS';
-- 查询在Student表中大于任意一个CS系同学年龄的一条元组并且，这个同学不能是计科系的
Sname Sage
张立 19 
-- 用聚集函数
SELECT Sname,Sage FROM Student WHERE Sage<(SELECT MAX(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';
-- 第二句表格是空的，因为没有符合条件的数据
Sname Sage
张立 19 
SELECT Sname FROM Student WHERE EXISTS(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1');
-- 查询所有选修1号课程的学生姓名
-- 本查询涉及Student和SC关系
-- 在Student中依次取每个元组的Sno值，用此值去检查SC表
-- 若SC中存在这样的元组,其Sno值等于此Student.Sno值，并且其Cno='1'，则取此Student.Sname送入结果表
Sname
李勇 
-- 查询没有选修1号课程的学生姓名。
SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1');
Sname
刘晨
王敏
张立
张红

EXISTS量词待理解

集合查询

集合查询包括并操作UNION、交操作INTERSECT和差操作EXCEPT

-- UNION
-- 查询计算机科学系的学生及年龄不大于19岁的学生。
SELECT * FROM Student WHERE Sdept='CS' UNION SELECT * FROM Student WHERE Sage<=19;
SELECT * FROM Student WHERE Sdept='CS' OR Sage<=19;
Sno Sname Ssex Sage Sdept
200215121 李勇 男 20 CS
200215122 刘晨 女 19 CS
200215125 张立 男 19 IS
200215126 张红 女 19 CS 
-- UNION
-- 查询选修了课程1或者选修了课程2的学生。
-- 本例即查询选修课程1的学生集合与选修课程2的学生集合的并集
SELECT Sno FROM SC WHERE Cno='1' UNION SELECT Sno FROM SC WHERE Cno='2';
Sno
200215121
200215122 
--INTERSECT
-- 查询计算机科学系的学生与年龄不大于19岁的学生的交集
SELECT * FROM Student WHERE Sdept='CS' INTERSECT SELECT * FROM Student WHERE Sage<=19;
SELECT * FROM Student WHERE Sdept='CS' AND Sage<=19;
Sno Sname Ssex Sage Sdept
200215122 刘晨 女 19 CS
200215126 张红 女 19 CS 
--INTERSECT
-- 查询既选修了课程1又选修了课程2的学生。就是查询选修课程1的学生集合与选修课程2的学生集合的交集
SELECT Sno FROM SC WHERE Cno='1' INTERSECT SELECT Sno FROM SC Where Cno='2';
SELECT Sno FROM SC WHERE Cno='1' AND Sno IN(SELECT Sno FROM SC WHERE Cno='2');
Sno
200215121 
--EXCEPT
-- 查询计算机科学系的学生与年龄不大于19岁的学生的差集
SELECT * FROM Student WHERE Sdept='CS' EXCEPT SELECT * FROM Student WHERE Sage<=19;
-- 也就是查询计算机科学系中年龄大于19岁的学生
SELECT * FROM Student WHERE Sdept='CS' AND Sage>19;
Sno Sname Ssex Sage Sdept
200215121 李勇 男 20 CS

数据库系统概论（第五版） 王珊 萨师煊

侵权必删

文章作者: Jelly
文章链接: https://MttJelly.github.io/2022/03/21/DataBaseReViewSQL-1/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 Jelly !
数据库
赏
  目录