本文通过经典的学生-课程模式 S-T 数据库带大家学习 SQL 常用的多表查询 :
- 学生表:Student( Sno,Sname,Ssex,Sage,Sdept )
- 课程表:Course( Cno,Cname,Cpno,Ccredit)
- 学生选课表:SC( Sno,Cno,Grade)
下面的操作以这张 3 张表为例:
建表语句如下:
等值连接:连接运算符为=
查询每个学生及其选修课程的情况:
SELECT Student.*, SC.*
FROM Student,
SC
WHERE Student.Sno = SC.Sno;
也可以这样写:
# 使用内连接
select *
from student
inner join
sc
on student.Sno = sc.Sno;
内连接可以这样定义:
另外在 MySQL 中,inner join 可以简写成 join
下面我们来分析一下连接操作的执行过程:
第一种、嵌套循环法
(NESTED-LOOP):
第二种、排序合并法
(SORT-MERGE):
这在算法中很好理解,对排好序的数组进行遍历时间复杂度肯定降低。
第三种、索引连接
(INDEX-JOIN)
另外一条 SQL 语句可以同时完成选择和连接查询,这时 WHERE 子句是由连接谓词和选择谓词组成的复合条件:
查询选修 2 号课程且成绩在 90 分以上的所有学生的学号和姓名。
执行过程分析:
SC
中挑选出Cno='2'
并且Grade>90
的元组形成一个中间关系;Student
中满足连接条件的元组进行连接得到最终的结果关系;查询每一门课的间接先修课(即先修课的先修课):
select c1.Cno first, c2.Cpno second
from course c1, course c2
where c1.Cpno = c2.Cno
外连接和普通连接很像,但是还是有区别的,外连接与普通连接的区别:
还是之前的那个例子,查询每个学生及其选修课程的情况:
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student
LEFT OUTER JOIN SC
ON
(Student.Sno = SC.Sno);
左外链接:
右外连接:
left outer join 可以简写成 left join
而且我们在执行左外连接的时候还可以添加条件:
select *
from student
left join sc
on student.Sno = sc.Sno
where Cno = 3;
这里就相当于这样:
同样的道理也适用于右外连接:
多表连接是将两个以上的表进行连接:
查询每个学生的学号、姓名、选修的课程名及成绩:
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
短语的条件中的查询称为嵌套查询;SELECT Sname /*外层查询/父查询*/
FROM Student
WHERE Sno IN
(SELECT Sno /*内层查询/子查询*/
FROM SC
WHERE Cno = ' 2 ');
ORDER BY
子句查询与“刘晨”在同一个系学习的学生。
① 确定“刘晨”所在系名:
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 = '刘晨');
这一步称为不相关子查询。
也可以使用自身连接来实现:
SELECT S1.Sno, S1.Sname, S1.Sdept
FROM Student S1,
Student S2
WHERE S1.Sdept = S2.Sdept
AND S2.Sname = '刘晨';
再例如,查询选修了课程名为“信息系统”的学生学号和姓名:
select Sno, Sname
from student
where Sno in (
select sc.Sno
from sc
where Cno in (
select course.Cno
from course
where Cname = '信息系统'
)
)
用连接查询也可以实现:
# 使用连接查询实现上述功能
select student.Sno, Sname
from student,
sc,
course
where student.Sno = sc.Sno
and sc.Cno = course.Cno
and Cname = '信息系统';
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept =
(SELECT Sdept
FROM Student
WHERE Sname = '刘晨');
举例:找出每个学生超过他选修课程平均成绩的课程号。
# 找出每个学生超过他选修课程平均成绩的课程号。
select x.Sno, x.Cno
from sc x
where x.Grade >= (
select avg(y.Grade)
from sc y
where x.Sno = y.Sno
);
执行过程分析:
SELECT AVG(Grade)
FROM SC y
WHERE y.Sno='201215121';
SELECT Sno,Cno
FROM SC x
WHERE Grade >=88;
(201215121,1)
(201215121,3)
(201215121,1)
(201215121,3)
(201215122,2)
使用 ANY 或 ALL 谓词时必须同时使用比较运算
谓词 | 语义 |
---|---|
| 大于子查询结果中的某个值 |
| 大于子查询结果中的所有值 |
< ANY | 小于子查询结果中的某个值 |
< ALL | 小于子查询结果中的所有值 |
| 大于等于子查询结果中的某个值 |
| 大于等于子查询结果中的所有值 |
<= ANY | 小于等于子查询结果中的某个值 |
<= ALL | 小于等于子查询结果中的所有值 |
= ANY | 等于子查询结果中的某个值 |
=ALL | 等于子查询结果中的所有值(通常没有实际意义) |
!=(或<>)ANY | 不等于子查询结果中的某个值 |
!=(或<>)ALL | 不等于子查询结果中的任何一个值 |
例子:查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄。
SELECT Sname, Sage
FROM Student
WHERE Sage < ANY (SELECT Sage
FROM Student
WHERE Sdept = 'CS')
AND Sdept <> 'CS';
执行过程分析:
(20,19)
用聚集函数实现:
# 用聚集函数实现
SELECT Sname, Sage
FROM Student
WHERE Sage <
(SELECT MAX(Sage)
FROM Student
WHERE Sdept = 'CS')
AND Sdept <> 'CS';
查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
有两种实现方式,第一种方式是使用谓词:
# 查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
select student.Sname, student.Sage
from student
where Sage < all (
select Sage
from student
where Sdept = 'CS'
)
and Sdept <> 'CS';
第二种方式是使用聚合函数:
# 使用聚合函数
select student.Sname, student.Sage
from student
where Sage < (
select min(sage)
from student
where Sdept = 'CS'
)
and Sdept <> 'CS';
.
例如:查询所有选修了 1 号课程的学生姓名。
分析:
# 查询所有选修了1号课程的学生姓名。
select Sname
from student
where exists(
select *
from sc
where sc.Sno = student.Sno
and Cno = '1');
也可以使用谓词 IN:
select Sname
from student
where Sno in (select sc.Sno
from sc
where Cno = '1');
再例如:查询没有选修1号课程的学生姓名。
select Sname
from student
where not exists(
select *
from sc
where Cno = '1'
and sc.Sno = student.Sno
);
通过上面的例子我们可以看到,不同的查询之间可以使用不同的谓词来替换,下面罗列一下替换的规则:
集合操作的种类
参加集合操作的各查询结果的列数必须相同。
对应项的数据类型也必须相同。
例子:查询计算机科学系的学生及年龄不大于 19 岁的学生。
SELECT *
FROM Student
WHERE Sdept = 'CS'
UNION
SELECT *
FROM Student
WHERE Sage <= 19;
参数解释:
UNION
:将多个查询结果合并起来时,系统自动去掉重复元组;
UNION ALL
:将多个查询结果合并起来时,保留重复元组。
例子:查询选修了课程 1 或者选修了课程 2 的学生。
SELECT Sno
FROM SC
WHERE Cno = '1'
UNION
SELECT Sno
FROM SC
WHERE Cno = '2';
例子:查询计算机科学系的学生与年龄不大于 19 岁的学生的交集。
实际上就是查询计算机科学系中年龄不大于19岁的学生。
SELECT *
FROM Student
WHERE Sdept = 'CS'
AND Sage <= 19;
# 查询每个学生及其选修课程的情况
SELECT Student.*, SC.*
FROM Student,
SC
WHERE Student.Sno = SC.Sno;
# 使用内连接
select *
from student
inner join
sc
on student.Sno = sc.Sno;
# 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。
SELECT Student.Sno, Sname
FROM Student,
SC
WHERE Student.Sno = SC.Sno
AND SC.Cno = '2'
AND SC.Grade > 90;
# 查询每一门课的间接先修课(即先修课的先修课)
select c1.Cno first, c2.Cpno second
from course c1,
course c2
where c1.Cpno = c2.Cno;
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student
LEFT OUTER JOIN SC
ON
(Student.Sno = SC.Sno);
select *
from student
left join sc
on student.Sno = sc.Sno
where Cno = 3;
# 查询每个学生的学号、姓名、选修的课程名及成绩
SELECT Student.Sno, Sname, Cname, Grade
FROM Student,
SC,
Course /*多表连接*/
WHERE Student.Sno = SC.Sno
AND SC.Cno = Course.Cno;
SELECT Sname /*外层查询/父查询*/
FROM Student
WHERE Sno IN
(SELECT Sno /*内层查询/子查询*/
FROM SC
WHERE Cno = ' 2 ');
# 查询与“刘晨”在同一个系学习的学生 的3种实现方式
# 第一种
SELECT Sdept
FROM Student
WHERE Sname = '刘晨';
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept = 'CS';
# 第二种
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept =
(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 = '刘晨';
# 查询选修了课程名为“信息系统”的学生学号和姓名
select Sno, Sname
from student
where Sno in (
select sc.Sno
from sc
where Cno in (
select course.Cno
from course
where Cname = '信息系统'
)
);
# 使用连接查询实现上述功能
select student.Sno, Sname
from student,
sc,
course
where student.Sno = sc.Sno
and sc.Cno = course.Cno
and Cname = '信息系统';
SELECT *
FROM Student
WHERE Sdept = 'CS'
UNION
SELECT *
FROM Student
WHERE Sage <= 19;
SELECT Sno
FROM SC
WHERE Cno = '1'
UNION
SELECT Sno
FROM SC
WHERE Cno = '2';
# 找出每个学生超过他选修课程平均成绩的课程号。
select x.Sno, x.Cno
from sc x
where x.Grade >= (
select avg(y.Grade)
from sc y
where x.Sno = y.Sno
);
# 查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄。
SELECT Sname, Sage
FROM Student
WHERE Sage < ANY (SELECT Sage
FROM Student
WHERE Sdept = 'CS')
AND Sdept <> 'CS';
# 用聚集函数实现
SELECT Sname, Sage
FROM Student
WHERE Sage <
(SELECT MAX(Sage)
FROM Student
WHERE Sdept = 'CS')
AND Sdept <> 'CS';
# 查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
select student.Sname, student.Sage
from student
where Sage < all (
select Sage
from student
where Sdept = 'CS'
)
and Sdept <> 'CS';
# 使用聚合函数
select student.Sname, student.Sage
from student
where Sage < (
select min(sage)
from student
where Sdept = 'CS'
)
and Sdept <> 'CS';
# 查询所有选修了1号课程的学生姓名。
select Sname
from student
where exists(
select *
from sc
where sc.Sno = student.Sno
and Cno = '1');
# 使用谓词IN实现
select Sname
from student
where Sno in (select sc.Sno
from sc
where Cno = '1');
# 查询没有选修1号课程的学生姓名。
select Sname
from student
where not exists(
select *
from sc
where Cno = '1'
and sc.Sno = student.Sno
);
SELECT *
FROM Student
WHERE Sdept = 'CS'
AND Sage <= 19;
SELECT Sno
FROM SC
WHERE Cno = '1'
and Cno = '2'
;
SELECT Sno
FROM SC
WHERE Cno = '1'
AND Sno IN
(SELECT Sno
FROM SC
WHERE Cno = '2');