1.概述
- 连接查询：同时涉及多个表的查询
连接条件或连接谓词：用来连接两个表的条件
[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>一般格式：
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>
连接字段：连接谓词中的列名称
连接条件中的各连接字段类型必须是可比的，但名字不必是相同的
2.等值和非等值连接
等值连接：连接运算符为=
[例33] 查询每个学生及其选修课程的情况
SELECT Student.*，SC.*
FROM Student，SC
WHERE Student.Sno = SC.Sno；1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20select student.*,sc.* from student,sc where Student.Sno = sc.Sno;
+-------+-----------+------+------+-------+-------+-----+-------+
| Sno | Sname | Ssex | Sage | Sdept | Sno | Cno | Grade |
+-------+-----------+------+------+-------+-------+-----+-------+
| 95001 | 张三 | 男 | 21 | CS | 95001 | 1 | 93 |
| 95001 | 张三 | 男 | 21 | CS | 95001 | 2 | 95 |
| 95001 | 张三 | 男 | 21 | CS | 95001 | 3 | 91 |
| 95001 | 张三 | 男 | 21 | CS | 95001 | 4 | 98 |
| 95002 | 李四 | 男 | 23 | IS | 95002 | 2 | 90 |
| 95002 | 李四 | 男 | 23 | IS | 95002 | 3 | 80 |
| 95003 | 王五 | 男 | 19 | MA | 95003 | 2 | NULL |
| 95004 | 马六 | 女 | 20 | CS | 95004 | 3 | NULL |
| 95004 | 马六 | 女 | 20 | CS | 95004 | 4 | 88 |
| 95005 | 苏三 | 女 | 20 | IS | 95005 | 1 | 90 |
| 95005 | 苏三 | 女 | 20 | IS | 95005 | 2 | 98 |
| 95005 | 苏三 | 女 | 20 | IS | 95005 | 3 | 90 |
| 95005 | 苏三 | 女 | 20 | IS | 95005 | 4 | 89 |
| 95011 | 张成民 | 男 | 19 | CS | 95011 | 1 | NULL |
+-------+-----------+------+------+-------+-------+-----+-------+
14 rows in set (0.01 sec)此时发现表中有两竖列的值是相同的，所以是冗余的。
自然连接：
[例34] 对上例用自然连接完成。
SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade
FROM Student，SC
WHERE Student.Sno = SC.Sno；1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20select student.Sno,Sname,Ssex,Sage,Sdept,cno,Grade from student,sc where Student.Sno=sc.sno;
+-------+-----------+------+------+-------+-----+-------+
| Sno | Sname | Ssex | Sage | Sdept | cno | Grade |
+-------+-----------+------+------+-------+-----+-------+
| 95001 | 张三 | 男 | 21 | CS | 1 | 93 |
| 95001 | 张三 | 男 | 21 | CS | 2 | 95 |
| 95001 | 张三 | 男 | 21 | CS | 3 | 91 |
| 95001 | 张三 | 男 | 21 | CS | 4 | 98 |
| 95002 | 李四 | 男 | 23 | IS | 2 | 90 |
| 95002 | 李四 | 男 | 23 | IS | 3 | 80 |
| 95003 | 王五 | 男 | 19 | MA | 2 | NULL |
| 95004 | 马六 | 女 | 20 | CS | 3 | NULL |
| 95004 | 马六 | 女 | 20 | CS | 4 | 88 |
| 95005 | 苏三 | 女 | 20 | IS | 1 | 90 |
| 95005 | 苏三 | 女 | 20 | IS | 2 | 98 |
| 95005 | 苏三 | 女 | 20 | IS | 3 | 90 |
| 95005 | 苏三 | 女 | 20 | IS | 4 | 89 |
| 95011 | 张成民 | 男 | 19 | CS | 1 | NULL |
+-------+-----------+------+------+-------+-----+-------+
14 rows in set (0.00 sec)这里一定要注意选择的时候要用Student.Sno，不能单独只写一个sno，因为连接的两个表都有sno，这时区分不开到底是哪个。
3.自身连接
自身连接：一个表与其自己进行连接
需要给表起别名以示区别
由于所有属性名都是同名属性，因此必须使用别名前缀[例35]查询每一门课的间接先修课（即先修课的先修课）
SELECT FIRST.Cno，SECOND.Cpno FROM Course FIRST，Course SECOND WHERE FIRST.Cpno = SECOND.Cno；1
2
3
4
5
6
7
8select First.Cno,Second.Cpno from Course First,Course Second where first.cpno = second.cno;
+-----+------+
| Cno | Cpno |
+-----+------+
| 2 | 4 |
| 3 | NULL |
+-----+------+
2 rows in set (0.01 sec)
4.外连接
外连接与普通连接的区别
普通连接操作只输出满足连接条件的元组
外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
[例 36] 改写[例35]
SELECT FIRST.Cno, SECOND.Cpno FROM Course FIRST LEFT JOIN Course SECOND ON (FIRST.Cpno=SECOND.Cno);1
2
3
4
5
6
7
8
9
10select First.Cno,Second.Cpno from Course First LEFT JOIN Course Second ON(first.cpno = second.cno);
+-----+------+
| Cno | Cpno |
+-----+------+
| 2 | 4 |
| 3 | NULL |
| 1 | NULL |
| 4 | NULL |
+-----+------+
4 rows in set (0.00 sec)左外连接LEFT JOIN
列出左边关系中所有的元组
右外连接RIGHT JOIN
列出右边关系中所有的元组
SELECT * FROM Course FIRST RIGHT JOIN Course SECOND ON (FIRST.Cpno=SECOND.Cno);A left join B: 是以A表的记录为基础的,A的每一条记录依次和B连接，A的记录将会全部表示出来,而B只会显示符合连接条件的记录.
A right join B: 是以B表的记录为基础的,B的每一条记录依次和A连接，B的记录将会全部表示出来,而A只会显示符合连接条件的记录1
2
3
4
5
6
7
8
9
10select First.Cno,Second.Cpno from Course First RIGHT JOIN Course Second ON(first.cpno = second.cno);
+------+------+
| Cno | Cpno |
+------+------+
| 2 | 4 |
| 3 | NULL |
| NULL | NULL |
| NULL | 3 |
+------+------+
4 rows in set (0.00 sec)
5.复合条件连接
复合条件连接：WHERE子句中含多个连接条件
[例37]查询选修2号课程且成绩在90分以上的所有学生的学号和姓名SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno = SC.Sno AND /* 连接谓词*/ Cno= ‘2’ AND Grade > 90； /* 其他限定条件 */1
2
3
4
5
6
7
8select Student.sno,sname from student,sc where student.Sno = sc.Sno AND Cno='2' AND Grade >90;
+-------+--------+
| sno | sname |
+-------+--------+
| 95001 | 张三 |
| 95005 | 苏三 |
+-------+--------+
2 rows in set (0.01 sec)该查询的过程是，先从SC表中挑选出Cno=’2’并且Grade>90 的元组形成一个中间关系，再和Student中满足连接条件的元组进行连接得到最终的结果关系。
[例38]查询每个学生的学号、姓名、选修的课程名及成绩
SELECT Student.Sno，Sname，Cname，Grade
FROM Student，SC，Course /多表连接/
WHERE Student.Sno = SC.Snoand SC.Cno = Course.Cno；1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno AND sc.cno=course.cno;
+-------+-----------+-----------+-------+
| sno | sname | cname | grade |
+-------+-----------+-----------+-------+
| 95001 | 张三 | MATH | 93 |
| 95005 | 苏三 | MATH | 90 |
| 95011 | 张成民 | MATH | NULL |
| 95001 | 张三 | DB_DESIGN | 95 |
| 95002 | 李四 | DB_DESIGN | 90 |
| 95003 | 王五 | DB_DESIGN | NULL |
| 95005 | 苏三 | DB_DESIGN | 98 |
| 95001 | 张三 | P_DESIGN | 91 |
| 95002 | 李四 | P_DESIGN | 80 |
| 95004 | 马六 | P_DESIGN | NULL |
| 95005 | 苏三 | P_DESIGN | 90 |
| 95001 | 张三 | OS | 98 |
| 95004 | 马六 | OS | 88 |
| 95005 | 苏三 | OS | 89 |
+-------+-----------+-----------+-------+
7.嵌套查询
嵌套查询概述
一个SELECT-FROM-WHERE语句称为一个查询块
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询子查询的限制
不能使用ORDER BY子句，order by 子句只能对最终查询结果进行排序
层层嵌套方式反映了 SQL语言的结构化
有些嵌套查询可以用连接运算替代SELECT Sname /外层查询/父查询/
FROM Student
WHERE Sno IN
（SELECT Sno /内层查询/子查询/
FROM SC
WHERE Cno= ‘ 2 ‘）；1
2
3
4
5
6
7
8
9select Sname from student where sno IN (select sno from sc where cno='2');
+--------+
| Sname |
+--------+
| 张三 |
| 李四 |
| 王五 |
| 苏三 |
+--------+先查询选修课程号为2的学生学号，之后再在student中查询满足学号的学生姓名
不相关子查询：
查询的查询条件不依赖于父查询由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。
相关子查询：子查询的查询条件依赖于父查询
首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表
然后再取外层表的下一个元组
重复这一过程，直至外层表全部检查完为止
A 带有IN谓词的子查询
[例39] 查询与“刘三姐”在同一个系学习的学生。
此查询要求可以分步来完成① 确定“刘三姐”所在系名
SELECT Sdept FROM Student WHERE Sname= ‘ 刘三姐 '； 结果为： IS② 查找所有在IS系学习的学生。
SELECT Sno，Sname，Sdept FROM Student WHERE Sdept= ‘ IS’；将第一步查询嵌入到第二步查询的条件中
SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= ‘ 刘三姐 ’)； 此查询为不相关子查询。1
2
3
4
5
6
7
8
9
10
11select Sno,Sname,Sdept from Student where Sdept IN (select Sdept from Student where Sname ='刘三姐');
+-------+-----------+-------+
| Sno | Sname | Sdept |
+-------+-----------+-------+
| 95002 | 李四 | IS |
| 95005 | 苏三 | IS |
| 95006 | 刘七 | IS |
| 95007 | 刘三姐 | IS |
| 95010 | 陈东 | IS |
+-------+-----------+-------+
5 rows in set (0.00 sec)[例40]查询选修了课程名为“DB_DESIGN”的学生学号和姓名
SELECT Sno，Sname ③ 最后在Student关系中
FROM Student 取出Sno和Sname
WHERE Sno IN(SELECT Sno ② 然后在SC关系中找出选 FROM SC 修了2号课程的学生学号 WHERE Cno IN (SELECT Cno ① 首先在Course关系中找出 FROM Course “DB_DESIGN”的课程号，为2号 WHERE Cname= ‘DB_DESIGN’ ) );1
2
3
4
5
6
7
8
9
10
11select Sno,Sname from Student where Sno IN (select Sno from sc where Cno IN (select Cno from Course where cname='DB_DES
IGN'));
+-------+--------+
| Sno | Sname |
+-------+--------+
| 95001 | 张三 |
| 95002 | 李四 |
| 95003 | 王五 |
| 95005 | 苏三 |
+-------+--------+
4 rows in set (0.00 sec)
B 带有比较运算符的子查询
当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或< >）。
与ANY或ALL谓词配合使用［例41］找出每个学生超过他选修课程平均成绩的课程号和学号。
SELECT Sno， Cno
FROM SC x
WHERE Grade >(SELECT AVG(Grade)FROM SC y WHERE y.Sno=x.Sno);子查询一定要跟在比较符之后
1
2
3
4
5
6
7
8
9
10select Sno,Cno from sc x where Grade >(select AVG(Grade) from sc y where y.sno=x.sno);
+-------+-----+
| Sno | Cno |
+-------+-----+
| 95001 | 2 |
| 95001 | 4 |
| 95002 | 2 |
| 95005 | 2 |
+-------+-----+
4 rows in set (0.00 sec)- 从外层查询中取出SC的一个元组x，将元组x的Sno值（95001）传送给内层查询。
SELECT AVG(Grade)
FROM SC y
WHERE y.Sno=‘95001’; - 执行内层查询，得到值93（近似值），用该值代替内层查询，得到外层查询：
SELECT Sno， Cno
FROM SC x
WHERE Grade >93； - 执行这个查询，得到
（95001，2）
（95001，4） - 外层查询取出下一个元组重复做上述1至3步骤，直到外层的SC元组全部处理完毕。
- 从外层查询中取出SC的一个元组x，将元组x的Sno值（95001）传送给内层查询。
C.带有ANY（SOME）或ALL谓词的子查询
谓词语义
ANY：任意一个值（只要有一个值）
ALL：所有值需要配合使用比较运算符
>ANY 大于子查询结果中的某个值
>ALL 大于子查询结果中的所有值
< ANY 小于子查询结果中的某个值
< ALL 小于子查询结果中的所有值
>= ANY 大于等于子查询结果中的某个值
>= ALL 大于等于子查询结果中的所有值
<= ANY 小于等于子查询结果中的某个值
<= ALL 小于等于子查询结果中的所有值
= ANY 等于子查询结果中的某个值
=ALL 等于子查询结果中的所有值（通常没有实际意义）
!=（或<>）ANY 不等于子查询结果中的某个值
!=（或<>）ALL 不等于子查询结果中的任何一个值[例42] 查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄
SELECT Sname，Sage FROM Student WHERE Sage < ANY (SELECT Sage FROM Student WHERE Sdept= ' CS ') AND Sdept <> ‘CS ' ; /*父查询块中的条件 */1
2
3
4
5
6
7
8
9
10select Sname,Sage from student where Sage < ANY (select Sage from student where Sdept ='CS') AND Sdept <>'CS';
+--------+------+
| Sname | Sage |
+--------+------+
| 王五 | 19 |
| 苏三 | 20 |
| 刘七 | 19 |
| 陈东 | 19 |
+--------+------+
4 rows in set (0.00 sec)执行过程：
1.RDBMS执行此查询时，首先处理子查询，找出
CS系中所有学生的年龄，构成一个集合(21，19)- 处理父查询，找所有不是CS系且年龄小于
21 或 19的学生
- 处理父查询，找所有不是CS系且年龄小于
用聚集函数实现[例42]
SELECT Sname，Sage
FROM Student
WHERE Sage < (SELECT MAX(Sage)FROM Student WHERE Sdept= ‘CS ') AND Sdept <> ' CS ';1
2
3
4
5
6
7
8
9
10select Sname,Sage from student where Sage <(select MAX(Sage) from student where Sdept ='CS') AND Sdept <>'CS';
+--------+------+
| Sname | Sage |
+--------+------+
| 王五 | 19 |
| 苏三 | 20 |
| 刘七 | 19 |
| 陈东 | 19 |
+--------+------+
4 rows in set (0.00 sec)[例43] 查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
方法一：用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 ’;
D 带有EXISTS谓词的子查询
EXISTS谓词
存在量词
带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
若内层查询结果非空，则外层的WHERE子句返回真值
若内层查询结果为空，则外层的WHERE子句返回假值
由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义NOT EXISTS谓词
若内层查询结果非空，则外层的WHERE子句返回假值
若内层查询结果为空，则外层的WHERE子句返回真值[例44]查询所有选修了1号课程的学生姓名。
思路分析：
本查询涉及Student和SC关系
在Student中依次取每个元组的Sno值，用此值去检查SC关系
若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= ‘1’，则取此Student.Sname送入结果关系用嵌套查询
SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= ' 1 ')；1
2
3
4
5
6
7
8
9select Sname from Student where EXISTS(select * from sc where Sno=student.sno AND Cno='1');
+-----------+
| Sname |
+-----------+
| 张三 |
| 苏三 |
| 张成民 |
+-----------+
3 rows in set (0.00 sec)用连接做
SELECT Sname
FROM Student, SC
WHERE Student.Sno=SC.Sno AND SC.Cno= ‘1’;[例45] 查询没有选修1号课程的学生姓名。
SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno='1')；1
2
3
4
5
6
7
8
9
10
11
12
13select Sname from student where NOT EXISTS(select * from sc where sno=student.sno AND Cno='1');
+--------------+
| Sname |
+--------------+
| 李四 |
| 王五 |
| 马六 |
| 刘七 |
| 刘三姐 |
| 欧阳锋 |
| 欧阳大侠 |
| 陈东 |
+--------------+
- 不同形式的查询间的替换
一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换
用EXISTS/NOT EXISTS实现全称量词(难点)
SQL语言中没有全称量词 （For all）
可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：
例：[例39]查询与“刘三姐”在同一个系学习的学生。
可以用带EXISTS谓词的子查询替换：SELECT Sno，Sname，Sdept
FROM Student S1
WHERE EXISTS(SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = ‘刘三姐’)；1
2
3
4
5
6
7
8
9
10
11
12select Sno,Sname,Sdept from Student S1 where exists (select * from student S2 where
S1.sdept=S2.sdept AND S2.Sname='刘三姐');
+-------+-----------+-------+
| Sno | Sname | Sdept |
+-------+-----------+-------+
| 95002 | 李四 | IS |
| 95005 | 苏三 | IS |
| 95006 | 刘七 | IS |
| 95007 | 刘三姐 | IS |
| 95010 | 陈东 | IS |
+-------+-----------+-------+
6 rows in set (2.33 sec)[例46] 查询选修了全部课程的学生姓名。
查询这样的学生，没有一门课是他不选的。SELECT Sname FROM Student WHERE NOT EXISTS -----不存在 （SELECT * FROM Course ----有这样一条记录（有这样一门课）—-这个学生没有选这门课（不存在这样一门课，这个学生选了的）
WHERE NOT EXISTS ----不存在 (SELECT * FROM SC WHERE Sno= Student.Sno ---这个学生 AND Cno= Course.Cno ---这门课 ） ）；
1 | select Sname from student where NOT EXISTS(select * from COurse where NOT EXISTS (select * from sc where Sno=student.sno AND CNO=course.Cno)); |
- [例47]查询至少选修了学生95002选修的全部课程的学生号码。（除运算）
解题思路：
用逻辑蕴函表达：查询学号为x的学生，对所有的课程y，只要95002学生选修了课程y，则x也选修了y。
形式化表示：用p表示谓词 “学生95002选修了课程y” 用q表示谓词 “学生x选修了课程y” - 变换后语义：不存在这样的课程y，学生95002选修了y，而学生x没有选。
用NOT EXISTS谓词表示：
SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS ——-不存在
(SELECT
FROM SC SCY ——有95002选的一门课
WHERE SCY.Sno = ‘ 95002 ‘ AND
—-这个学生没有选95002选的这门课
NOT EXISTS ——-不存在
(SELECT
FROM SC SCZ ——有这样一条选课记录
WHERE SCZ.Sno=SCX.Sno —-这个学生
AND SCZ.Cno=SCY.Cno))；—-这门课
首先选出x的第一行，和y的95002的数据的第一行，来判断第三张表z中的课程是否和y的第一行课程一样，如果一样则表明存在，NOT EXISTS为假，继续判断95002的第二行在第三张表中是否存在，如果不存在，那就为真，上面的NOT EXISTS就为假，这个学号不输出
1 | select DISTINCT SNO from SC scx where NOT EXISTS(select * from sc scy where scy.Sno |
8.集合查询
集合操作的种类
并操作UNION
交集
差
笛卡尔积
参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同[例48] 查询计算机科学系的学生及年龄不大于19岁的学生。
方法一：SELECT * FROM Student WHERE Sdept= 'CS' UNION SELECT * FROM Student WHERE Sage<=19；UNION：将多个查询结果合并起来时，系统自动去掉重复元组。
UNION ALL：将多个查询结果合并起来时，保留重复元组1
2
3
4
5
6
7
8
9
10
11
12
13
14select * from student where sdept ='CS' UNION select * from student where Sage<=19;
+-------+-----------+------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept |
+-------+-----------+------+------+-------+
| 95001 | 张三 | 男 | 21 | CS |
| 95004 | 马六 | 女 | 20 | CS |
| 95011 | 张成民 | 男 | 19 | CS |
| 95003 | 王五 | 男 | 19 | MA |
| 95006 | 刘七 | 女 | 19 | IS |
| 95010 | 陈东 | 男 | 19 | IS |
+-------+-----------+------+------+-------+
6 rows in set (0.00 sec)方法二：用多重条件查询
SELECT DISTINCT *
FROM Student
WHERE Sdept= ‘CS’ OR Sage<=19；1
2
3
4
5
6
7
8
9
10
11
12
13select DISTINCT * from Student where sdept ='CS' OR Sage<=19;
+-------+-----------+------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept |
+-------+-----------+------+------+-------+
| 95001 | 张三 | 男 | 21 | CS |
| 95003 | 王五 | 男 | 19 | MA |
| 95004 | 马六 | 女 | 20 | CS |
| 95006 | 刘七 | 女 | 19 | IS |
| 95010 | 陈东 | 男 | 19 | IS |
| 95011 | 张成民 | 男 | 19 | CS |
+-------+-----------+------+------+-------+
6 rows in set (0.00 sec)[例49] 查询选修了课程1或者选修了课程2的学生。
SELECT Sno
FROM SC
WHERE Cno=’ 1 ‘
UNION
SELECT Sno
FROM SC
WHERE Cno= ‘ 2 ‘；1
2
3
4
5
6
7
8
9
10
11
12select Sno from SC where Cno='1' UNION select Sno from SC where Cno='2';
+-------+
| Sno |
+-------+
| 95001 |
| 95005 |
| 95011 |
| 95002 |
| 95003 |
+-------+
5 rows in set (0.00 sec)[例50] 查询计算机科学系的学生与年龄不大于19岁的学生的交集
SELECT * FROM Student WHERE Sdept=’CS’ AND Sage<=19;
1
2
3
4
5
6
7
8select * from student where sdept = 'CS' AND Sage<=19;
+-------+-----------+------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept |
+-------+-----------+------+------+-------+
| 95011 | 张成民 | 男 | 19 | CS |
+-------+-----------+------+------+-------+
1 row in set (0.00 sec)这里实际上要用到交集INTERSECT，但是和前面多个条件查询一样所以就没有用INTERSECT关键词。
[例51] 查询选修课程1的学生集合与选修课程2的学生集合的交集
SELECT Sno FROM SC WHERE Cno=1
AND Sno IN (SELECT Sno FROM SC
WHERE Cno=2);1
2
3
4
5
6
7
8
9select Sno from sc where cno='1' and sno IN(select sno from sc where cno='2');
+-------+
| Sno |
+-------+
| 95001 |
| 95005 |
+-------+
2 rows in set (0.00 sec)[例52] 查询计算机科学系的学生与年龄不大于19岁的学生的差集。
SELECT * FROM Student WHERE Sdept=’CS’ AND Sage>19;
1
2
3
4
5
6
7
8
9select * from student where sdept ='CS' AND sage>19;
+-------+--------+------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept |
+-------+--------+------+------+-------+
| 95001 | 张三 | 男 | 21 | CS |
| 95004 | 马六 | 女 | 20 | CS |
+-------+--------+------+------+-------+
2 rows in set (0.00 sec)[例52] 查询学生表与课程表的笛卡尔积。
SELECT * FROM Student CROSS JOIN Course;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51select * from student CROSS JOIN course;
+-------+--------------+------+------+-------+-----+-----------+------+---------+
| Sno | Sname | Ssex | Sage | Sdept | Cno | Cname | Cpno | Ccredit |
+-------+--------------+------+------+-------+-----+-----------+------+---------+
| 95001 | 张三 | 男 | 21 | CS | 1 | MATH | NULL | 6 |
| 95001 | 张三 | 男 | 21 | CS | 2 | DB_DESIGN | 3 | 2 |
| 95001 | 张三 | 男 | 21 | CS | 3 | P_DESIGN | 4 | 3 |
| 95001 | 张三 | 男 | 21 | CS | 4 | OS | NULL | 2 |
| 95002 | 李四 | 男 | 23 | IS | 1 | MATH | NULL | 6 |
| 95002 | 李四 | 男 | 23 | IS | 2 | DB_DESIGN | 3 | 2 |
| 95002 | 李四 | 男 | 23 | IS | 3 | P_DESIGN | 4 | 3 |
| 95002 | 李四 | 男 | 23 | IS | 4 | OS | NULL | 2 |
| 95003 | 王五 | 男 | 19 | MA | 1 | MATH | NULL | 6 |
| 95003 | 王五 | 男 | 19 | MA | 2 | DB_DESIGN | 3 | 2 |
| 95003 | 王五 | 男 | 19 | MA | 3 | P_DESIGN | 4 | 3 |
| 95003 | 王五 | 男 | 19 | MA | 4 | OS | NULL | 2 |
| 95004 | 马六 | 女 | 20 | CS | 1 | MATH | NULL | 6 |
| 95004 | 马六 | 女 | 20 | CS | 2 | DB_DESIGN | 3 | 2 |
| 95004 | 马六 | 女 | 20 | CS | 3 | P_DESIGN | 4 | 3 |
| 95004 | 马六 | 女 | 20 | CS | 4 | OS | NULL | 2 |
| 95005 | 苏三 | 女 | 20 | IS | 1 | MATH | NULL | 6 |
| 95005 | 苏三 | 女 | 20 | IS | 2 | DB_DESIGN | 3 | 2 |
| 95005 | 苏三 | 女 | 20 | IS | 3 | P_DESIGN | 4 | 3 |
| 95005 | 苏三 | 女 | 20 | IS | 4 | OS | NULL | 2 |
| 95006 | 刘七 | 女 | 19 | IS | 1 | MATH | NULL | 6 |
| 95006 | 刘七 | 女 | 19 | IS | 2 | DB_DESIGN | 3 | 2 |
| 95006 | 刘七 | 女 | 19 | IS | 3 | P_DESIGN | 4 | 3 |
| 95006 | 刘七 | 女 | 19 | IS | 4 | OS | NULL | 2 |
| 95007 | 刘三姐 | 女 | 23 | IS | 1 | MATH | NULL | 6 |
| 95007 | 刘三姐 | 女 | 23 | IS | 2 | DB_DESIGN | 3 | 2 |
| 95007 | 刘三姐 | 女 | 23 | IS | 3 | P_DESIGN | 4 | 3 |
| 95007 | 刘三姐 | 女 | 23 | IS | 4 | OS | NULL | 2 |
| 95008 | 欧阳锋 | 男 | 24 | MA | 1 | MATH | NULL | 6 |
| 95008 | 欧阳锋 | 男 | 24 | MA | 2 | DB_DESIGN | 3 | 2 |
| 95008 | 欧阳锋 | 男 | 24 | MA | 3 | P_DESIGN | 4 | 3 |
| 95008 | 欧阳锋 | 男 | 24 | MA | 4 | OS | NULL | 2 |
| 95009 | 欧阳大侠 | 男 | 23 | MA | 1 | MATH | NULL | 6 |
| 95009 | 欧阳大侠 | 男 | 23 | MA | 2 | DB_DESIGN | 3 | 2 |
| 95009 | 欧阳大侠 | 男 | 23 | MA | 3 | P_DESIGN | 4 | 3 |
| 95009 | 欧阳大侠 | 男 | 23 | MA | 4 | OS | NULL | 2 |
| 95010 | 陈东 | 男 | 19 | IS | 1 | MATH | NULL | 6 |
| 95010 | 陈东 | 男 | 19 | IS | 2 | DB_DESIGN | 3 | 2 |
| 95010 | 陈东 | 男 | 19 | IS | 3 | P_DESIGN | 4 | 3 |
| 95010 | 陈东 | 男 | 19 | IS | 4 | OS | NULL | 2 |
| 95011 | 张成民 | 男 | 19 | CS | 1 | MATH | NULL | 6 |
| 95011 | 张成民 | 男 | 19 | CS | 2 | DB_DESIGN | 3 | 2 |
| 95011 | 张成民 | 男 | 19 | CS | 3 | P_DESIGN | 4 | 3 |
| 95011 | 张成民 | 男 | 19 | CS | 4 | OS | NULL | 2 |
+-------+--------------+------+------+-------+-----+-----------+------+---------+
48 rows in set (0.00 sec)插入-子查询
更新-子查询
删除-子查询
INTO子句(与插入元组类似)
子查询
SELECT子句目标列必须与INTO子句匹配
值的个数
值的类型
[例53] 对每一个系，求学生的平均年龄，并把结果存入数据库。
第一步：建表
CREATE TABLE Dept_age (Sdept CHAR(15) /* 系名*/ Avg_age SMALLINT)； /*学生平均年龄*/第二步：插入数据
INSERT INTO Dept_age(Sdept，Avg_age) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；[例54] 将计算机科学系全体学生的成绩加1。
UPDATE SC SET Grade=Grade+1 WHERE 'CS'= (SELECT Sdept FROM Student WHERE Student.Sno = SC.Sno);[例55] 删除计算机科学系中没有成绩的学生的选课记录。
DELETE FROM SC WHERE ‘CS’=(SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno)
and grade is null;
9.结尾
- 本篇文章主要讲了多表查询的操作，其中EXSIST语句最不好搞，作者也没想到好的描述方法，如果有好的方法可以评论共同探讨，如果觉得有帮助的话，请点赞鼓励我更上一层楼！