SQL连接查询和嵌套查询详解
连接查询
若一个查询同时涉及两个或两个以上的表,则称之为连接查询。连接查询是数据库中最最要的查询,
包括:
1、等值连接查询
2、自然连接查询
3、非等值连接查询
4、自身连接查询
5、外连接查询
6、复合条件查询
等值与非等值连接查询:
比较运算符主要有=、>、<、>=、<=、!=(或<>)等。
下面来看一个例子:
假设有一个学生数据库,其中有三张表,即学生信息表(Student)、课程表(Course)、选课表(Study),三张表中的信息如下:
![](https://images2018.cnblogs.com/blog/1328384/201805/1328384-20180523103609014-794395936.png)
![](https://images2018.cnblogs.com/blog/1328384/201805/1328384-20180523103636420-879028850.png)
![](https://images2018.cnblogs.com/blog/1328384/201805/1328384-20180523103654683-1647614057.png)
例1:要求查询选修了课程的学生的信息
很显然,需要用连接查询,学生的情况存放在student表中,学生的选课情况存放在Study表中,所以查询实际涉及Student和Study这两个表。这两个表之间的联系是通过公共属性Sno实现的。
考虑下列等值连接查询语句
SELECT Student.*,Study.* FEOM Student,Study WHERE Student.Sno=Study.Sno /*将Student与Study中同一学生的元祖连接起来*/
得到的结果:
我们发现,上述查询语句按照把两个表中学号相等的元祖连接起来。
系统执行的连接过程:首先在表Student中找到一个元祖,然后从头开始扫描Study表,逐一查找与Student第一个元祖的Sno相等的元祖,找到后就将Student表中的第一个元祖与该元祖拼接起来,形成结果表中的一个元祖,Stdudy表全部查找完后,再找Student中的第二个元祖,重复上述过程,直至Student表中的全部元祖处理完。
自然连接:在等值连接中把目标中重复的属性列去掉的连接查询
下面考虑用自然连接实现上述例子:
SELECT Student.Sno,SName,SSex,Sdept,Cno,Grade FROM Student,Study WHERE Student.Sno=Study.Sno
结果:
自身连接查询:当查询的结果涉及同一个表中两个或以上的列时,考虑用自身连接查询
例2:查询每一门课的间接先行课(即先行课)
SELECT C1.Cpno FEOM Course AS C1,Course AS C2 <span style="white-space:pre;"> </span>--为Course表起两个别名C1、C2 WHERE C1.Pcno=C2.Cno --两个Course表的连接
结果:
外连接查询:
分为左外连接,右外连接,
左外连接:根据左表的记录,在被连接的右表中找出符合条件的记录与之匹配,找不到匹配的,用null填充
右连接:根据右表的记录,在被连接的左表中找出符合条件的记录与之匹配,找不到匹配的,用null填充
例3:查询缺少成绩的的学生号和课程号:
SELECT Student.Sno,Cno FROM Student LEFT JOIN Study ON Student.Sno=Study.Sno WHERE Grade IS NULL
结果:
例4:查询所有学生的学号姓名、成绩
--左外连接
SELECT Student.Sno AS 学号,SName AS 姓名, Grade AS 成绩 FROM Student LEFT JOIN Study<span style="white-space:pre;"> </span> ON Student.Sno=Study.Sno
相当于:
--右外连接
SELECT Student.Sno AS 学号,SName AS 姓名, Grade AS 成绩 FROM Study RIGHT JOIN Student ON Study.Sno=Student.Sno
结果:
左外连接列出左边关系,右外连接列出右外关系中所有的元祖
多表连接查询:
--1、WHRER 语句
--2、INNER JOIN.. 语句
--1、WHRER 语句
--2、INNER JOIN.. 语句
例:查询选修了C601号课程的学生姓名、分数、课程名
这个查询三个涉及了表学生表、课程表和学习表’
SELECT Student.SName AS 学生姓名,Grade AS 成绩,CName AS 课程名 FROM Student INNER JOIN Study ON Student.Sno=Study.Sno INNER JOIN Course ON Study.Cno=Course.Cno WHERE Course.Cno='C601'
相当于自然连接查询:
SELECT Student.SName AS 学生姓名,Grade AS 成绩,CName AS 课程名
FROM Student,Course,Study
WHERE Student=Study.Sno AND Study.cno=Course.Cno ADN Course.Cno=C601
嵌套查询
嵌套查询又称子查询,是指在父查询的where条件语句中再插入一个子查询语句,连接查询都可以用子查询完成,反之不然。
例1:找出至少一门课程的成绩在90分以上的女学生的姓名
分析:已知的是分数大于90分这个条件,通过这个条件找出Study表中大于90分所对应的Sno,再通过连接查询Study表中对应Sno的SName
SELECT SName FROM Student WHERE Sex='女' AND Sno NOT IN ( SELECT Sno FROM Stduy WHERE Grade<90 )
注意:这里子查询返回的Sno可能有多个,所以要用到谓词 IN,如果用 =,则报错,因为 = 表示子查询的返回值是唯一的。
子查询的一个原则:根据已知得出未知
例2:查询选修了课程名为 ‘’高等数学” 的学生学号和姓名
根据Course表中的高等数学得到课程号,再在Study表中找到选修了该课程号的学号,最后根据学号Sno在Student表中找出对应的学生的姓名。一层层嵌套,由已知得到未知。
SELECT Sno,SName FROM Student WHERE Sno IN ( SELECT Sno FROM Study WHERE Cno IN ( SELECT Cno FROM Course WHERE CName='高等数学' ) )
相当于连接查询:
SELECT Student.Sno,SName FROM Student,Course,Study WHERE Student.Sno=Study.Sno AND Course.Cno=Study.Cno AND Course.CName='高等数学'
结果:
例3:找出至少学了C601和C602两门课程的学生姓名。
这里涉及到两门课程,都来自Course表,涉及到同一个表中两个或以上的元祖,考虑子查询用自身连,子查询根据课程号返回学号,父查询再根据学号查询姓名。
SELECT SName FROM Student WHERE Sno IN ( SELECT Study1.Sno FROM Study AS Study1 JOIN Study AS Study2 ON Study1.Sno=Study2.Sno WHERE Study1.Cno='C601' AND Study2.Cno='C602' )
结果: