sql数据库 连接查询、嵌套查询、集合查询详解

2.连接查询

同时涉及多个表的查询 一般格式 [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

用来连接两个表的条件称为连接条件或连接谓词 一般用比较运算符：=，>,<

连接谓词中的列名称为连接字段，连接字段类型必须是可比的，但不必是相同的

等值连接

使用=时 等值连接

非等值连接

使用其他运算符 非等值连接

自然连接

在等值连接中，去掉目标列重复属性就是自然连接

--查询每个学生及其选修课程的情况
select Student.*, SC.*
from Student, SC
where Student.Sno =SC.Sno;
``

```sql
--对上一个查询进行自然连接完成
select student,sname,ssex,sage,sdept,cno,grade
from student,SC
where student.Sno=SC.Sno;
``

#### 自身连接

一个表与其自己进行连接，称为表的自身连接

 需要给表起别名以示区别，并且由于所有属性名都是同名属性，因此必须使用别名前缀 

```sql
--查询每一门课的先修课的先修课
select first.Cno, second.cpno
from course first, course second
where first.Cpon = second.Cno;

外连接

普通连接只输出满足连接条件的元组，外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出

外连接分为外左连接(left outer join)和外右连接(right outer join)

外连接符 full outer join

select first.Cno ,second.pno
from Course first full outer join Course second 
on first.Cpno=Second.Cno;

左连接

取左边的表的全部，右边的表按条件，符合的显示，不符合则显示null

左外连接符 left outer join

select first.Cno ,second.pno
from Course first left outer join Course second 
on first.Cpno=Second.Cno;

右连接

取右边的表的全部，左边的表按条件，符合的显示，不符合则显示null

右外连接符 right outer join

select first.Cno ,second.Cpno
from Course first right outer join Course second
on first.Cpno = second.Cno;

复合条件连接

where子句猩红包含多个连接条件，称为复合条件连接

--查询选修2号课程且成绩在90分以上的所有学生的学号、姓名。
select student.sno,student.sname
from student,SC
where student.sno = SC.sno and
	  sc.Cno = '2' and
	  SC.Grade >90;

多表连接：两个以上的表进行连接

-- 查询每个学生的学号、姓名、选修的课程名及成绩
select student.Sno,Sname,Cname,Grade
from Student,SC,Course
where Student.Sno = SC.Sno
	  and SC.Cno = Course.Cno;

3.嵌套查询

一个select-from-where语句为一个查询块，多个查询块之前进行嵌套的查询为嵌套查询

外层查询/父查询

内层查询/子查询

子查询的限制

子查询不能使用order by子句

嵌套查询分为不相关子查询和相关子查询

不相关子查询：子查询的查询条件不依赖于父查询

是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的 结果用于建立其父查询的查找条件。

相关子查询：子查询的查询条件依赖于父查询

首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内 层查询，若WHERE子句返回值为真，则取此元组放入结果表, 然后再取外层表的下一个元组

子查询的谓词

带有in谓词

--查询与刘晨在同一个系学习的学生
select Sno,Sname,Sdept
from student
where sdept in
		(select sdept 
        from student
        where Sname='刘晨');

--查询选修了课程名为"信息系统"的学生学号和姓名
select Sno,Sname			--3，最后在student关系中取出Sno和Sname
from Student
where Sno in				--2，然后再SC关系中找出选修该课程的学生学号
	(select Sno
    from SC
    where Cno in			--1，先在course关系中找出信息系统的课程号
    	(select Cno
        from Course
        where Cname='信息系统'));

带比较运算符的子查询

当能确切知道内层查询返回单值时，可用比较运算符（>，<， = ，>=， <=，!=或< >）

可以与any和all谓词配合使用

带有any或all谓词的子查询

any 任意一个值 all 所有值

 > ANY 大于子查询结果中的某个值
 > ALL 大于子查询结果中的所有值
 < ANY 小于子查询结果中的某个值
 < ALL 小于子查询结果中的所有值
 >= ANY 大于等于子查询结果中的某个值
 >= ALL 大于等于子查询结果中的所有值
 <= ANY 小于等于子查询结果中的某个值
 <= ALL 小于等于子查询结果中的所有值
 = ANY 等于子查询结果中的某个值
 =ALL 等于子查询结果中的所有值（通常没有实际意义）
 !=(或<>)ANY 不等于子查询结果中的某个值
 !=(或<>)ALL 不等于子查询结果中的任何一个值

--查询其他系中比信息系任意一个学生年龄小的学生姓名和年龄
select Sname,Sage
from student
where Sage< any
		(select Sage
        from student
        where Sdept = 'IS')
        and sdept <>'IS';

带exists谓词的子查询

exists 存在量词

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值 “false” ， 若内层查询结果非空，则外层的WHERE子句返回真值 ，若内层查询结果为空，则外层的WHERE子句返回假值

由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只 返回真值或假值，给出列名无实际意义

not exists相反

用exists/not exists实现全称量词

用EXISTS/NOT EXISTS实现逻辑蕴函

--全称量词的实现
--查询选修了全部课程的学生姓名
select Sname
from student
where not exists
		(select *
        from Course
        where not exists
        (		select *
        		from SC
        		where Sno=student.Sno and
        			Cno=Course.Cno));
--相当于是拿出一个学生，对任何一个课程，查看该学生是否选修了。如果未选修，返回该课程。如果选修了，则查看下一个课程，最终，返回的所有课程为空的话，说明该学生选修了所有的课程。此时输出该学生的信息

4.集合查询

并操作 union

<查询块>
union
<查询块>

参加union操作的各结果表的列数必须相同；对应项的数据类型也必须相同

union：将多个查询结果合并起来时，系统自动去掉重复元组

union all : 将多个查询结果并起来时，保留重复元素

交操作 intersect

差操作 except

补充： ORDER BY子句只能用于对最终查询结果排序，不能对中间结果排序 ，任何情况下，ORDER BY子句只能出现在最后