SQL数据查询(二)

连接查询

若一个查询涉及两个或两个以上的表，称之为连接查询。连接查询是数据库的主要查询，主要包括等值连接查询，自然连接查询，非等值连接查询，自身连接查询和复合条件连接查询。

等值连接与非等值连接查询

连接查询的where子句中用以连接两个表的条件称为连接条件或连接谓词，当连接的比较运算符为=时，称为等值连接，其他为非等值连接。
eg:查询每个学生及其选修课的情况。

select Student.*,SC.* from Student,SC
where Student.Sno=SC.Sno;

等值连接查询的结果中存在着重复的列(Student.Sno与SC.Sno的值相同),在此把重复的列去掉就是自然连接。
eg:用自然连接完成

select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student,SC
where Student.Sno=SC.Sno;

自身连接

连接操作不仅可以在不同的表之间进行，而且也可以在同一个表内进行连接，称之为自身连接。
eg:查询每门课的间接先修课(即先修课的先修课)
分析:在Course表中只有每门课的先修课的信息而没有先修课的先修课。要得到该信息，则必须对一门课找到其先修课，再按该先修课的课程号在Course表中查找其他的先修课。这样，可以想象有两个完全相同的Course表，把这两个表按照课程号和先修课进行连接即可得到结果。为此，为Course表分别指定两个别名First和Second。

select First.Cno,Second.Cpno from Course First,Course Second where First.CPno=Second.Cno;

外连接

若想以Student表为主体列出每个学生的基本情况及其选课情况，且没有选课的学生也希望输出其基本信息，这时需要用外连接来实现。
外连接分为左外连接，，右外连接和全外连接。
eg:将上例改为左外连接

select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student left outer join SC
on Student.Sno=SC.Sno;

复合条件查询

WHERE子句有多个连接条件,称为复合条件连接
eg:查询选修了数据库原理课程且成绩在90分以上的学生的学号和姓名。

use JWGL;
select Student.Sno,Sname
from Student,Course,SC
where Student.Sno=SC.Sno and SC.Cno=Course.Cno and Cname='数据库' and Grade>90;

嵌套查询

在SQL中,一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌入到另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。其中上层的查询块称为外层查询或父查询,下层的查询块称为内层查询或子查询。SQL中允许多层嵌套,但是子查询的SELECT语句中不能使用ORDER BY子句,ORDER BY子句只能对最终结果进行排序。

不相关子查询

即内层的查询不依赖于外层的查询。不相关子查询的执行过程是首先执行内查询，内查询得到的结果集不被显示出来，而是传递给外层查询，作为外层查询的条件来使用，然后执行外层查询，并显示查询结果。
eg:查询选修了1号课程的学生的姓名

select Sname from Student
where Sno in 
(select Sno from SC where Cno='1');
该查询也可以用连接查询实现
select Sname from Student,SC
where Student.Sno=SC.Sno and Cno='1';

eg:查询和李大勇在同一个系学习的学生的学号和姓名。

select Sno,Sname from Student
where Sdept=
(select Sdept from Student where Sname='李大勇');

select Sname,Sage from Student
where Sdept <> 'CS' 
      and Sage<ANY (select Sage from Student where Sdept='CS');
该查询等价于以下查询
select Sname,Sage from Student
where Sdept <> 'CS' and Sage<ANY(select MAX(Sage) from Student where Sdept='CS');

相关子查询

在相关子查询中,子查询的执行依赖与外层查询,通常是在子查询的WHERE子句中引用了父查询的表。
eg:查询每个学生超过他所选课程平均成绩的课程号。

select Sno,Cno from SC x
where Grade>=(select AVG(Grade) from SC y where y.Cno=x.Cno);

带有EXISTS谓词的子查询

eg:查询所有选修了3号课程的学生的姓名。

select Sname from Student
where exists (select * from SC where Sno=Student.Sno and Cno='3');

eg:查询选修了全部课程的学生姓名
可以转换为查询这样的学生,没有一门课程是他不选的。

select Sname from Student
where not exists (select * from Course where not exists  
(select * from SC where Sno=Student.Sno and Cno=Course.Cno));

eg:查询至少选修了学生950002选修的全部课程的学生学号。
该查询可以用逻辑蕴含来表达为查询学号为x的学生,对所有的课程y，只要学生950002选修了课程y，则x也选修了y。

不存在这样的课程y,学生950002选修了y，而学生x没有选修。

select distinct Sno from SC SCX
where not exists
(select * from SC SCY
   where SCY.Sno='950002' and not exists
   (select * from SC SCZ 
      where SCZ.Sno=SCX.Sno and SCZ.Cno=SCY.Cno));

集合查询

由于SELECT语句的查询结果是元组的集合，所以在SQL中，可以用union语句把两个或两个以上的查询结果合并为一个结果集。语法格式为:
select_statement
union [all]
select_statement
合并结果有以下几点限制
(1)参加union运算的各查询结果的列数必须相同，且对应列的数据类型也相同。
(2)最后结果集中的列名来自第一个SELECT语句
(3)order by子句只能出现在最后，将对最后的结果集排序
(4)在合并结果时,默认将从最后的结果集中删除重复的元组，除非使用all关键字说明需要保留重复元组。
eg:查询选修了1号课程或2号课程的所有学生的学号。

select Sno
from SC
where Cno='1'
union
select Sno
from SC
where Cno='2';