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子句只能出现在最后