Mysql学习笔记(四)——多表查询
文章目录
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；mysql> select 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；mysql> select 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；mysql> select 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);mysql> select 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只会显示符合连接条件的记录mysql> select 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； /* 其他限定条件 */mysql> select 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.Sno
and SC.Cno = Course.Cno；mysql> select 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 '）；mysql> select 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= ‘ 刘三姐 ’)；
此查询为不相关子查询。mysql> select 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’
)
);mysql> select 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);子查询一定要跟在比较符之后
mysql> select 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 ’ ; /*父查询块中的条件 */mysql> select 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 ';mysql> select 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 ')；mysql> select 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’)；mysql> select 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 = ‘刘三姐’)；mysql> select 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 —这门课
）
）；
mysql> select Sname from student where NOT EXISTS(select * from COurse where NOT EXISTS (select * from sc where Sno=student.sno AND CNO=course.Cno));
+--------+
| Sname |
+--------+
| 张三 |
| 苏三 |
+--------+
2 rows in set (0.01 sec)
-
[例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))；—这门课
mysql> select DISTINCT SNO from SC scx where NOT EXISTS(select * from sc scy where scy.Sno
='95002' AND NOT EXISTS(select * from sc scz where scz.Sno=scx.sno AND scz.cno=scy.cno));
+-------+
| SNO |
+-------+
| 95001 |
| 95002 |
| 95005 |
+-------+
3 rows in set (0.01 sec)
8.集合查询
-
集合操作的种类
并操作UNION
交集
差
笛卡尔积
参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同 -
[例48] 查询计算机科学系的学生及年龄不大于19岁的学生。
方法一：
SELECT *
FROM Student
WHERE Sdept= ‘CS’
UNION
SELECT *
FROM Student
WHERE Sage<=19；
UNION：将多个查询结果合并起来时，系统自动去掉重复元组。
UNION ALL：将多个查询结果合并起来时，保留重复元组mysql> select * 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；mysql> select 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 '；mysql> select 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;
mysql> select * 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);mysql> select 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;
mysql> select * 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;
mysql> select * 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语句最不好搞，作者也没想到好的描述方法，如果有好的方法可以评论共同探讨，如果觉得有帮助的话，请点赞鼓励我更上一层楼！
275
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
