数据库3.2 查询语句

4、自身连接

连接操作不仅可以在两个表之间进行，也可以是一个表与自己进行连接，称为表的自身连接。

定义第一课程，第二课程，限制条件，第一课程的先行课等于第二课程，查询的是第一课程和第二课程的先行课。即可查询课程的先行课

查询和B学生和其他学生同院系的学生信息，其他院系用A学生代替，查询A学生的学号，姓名，在学生A和学生B中查询，限制条件学生A的院系与学生B的院系相同，而且学生B姓名为李勇。

--查询课程的间接先修课，即先修课的先修课
SELECT  FIRST.Cno, SECOND.Pre_Cno
FROM  Course  FIRST, Course  SECOND
WHERE FIRST.Pre_Cno = SECOND.Cno;

--查询和李勇同一院系的学生学号及姓名
SELECT  A.Sno, A.Sname
FROM  Student  A, Student  B
WHERE A.Sdept = B.Sdept
AND B.Sname='李勇';

查询选修了1号和5号课程的学生学号，此查询在3.1出现过，这里给出了使用内连接的方法。把A加入到B中，只需要查询A即可。查询选修了两门课也是把A加入到B中，只查询A即可。

--查询选修了1号和5号课程的学生学号
SELECT A.Sno
FROM SC A JOIN SC B
ON A.Sno=B.Sno
AND A.Cno='1'
AND B.Cno='5';
--查询至少选修了两门课的学生学号
SELECT DISTINCT A.Sno
FROM SC A JOIN SC B
ON A.Sno=B.Sno
AND A.Cno<>B.Cno;

5.外连接

查询所有学生学号，姓名，选课记录，将学生表与课程表通过LEFT OUTER JOIN连接起来，都放在Student.Sno表中

--查询所有学生的学号、姓名及选课记录
SELECT Student.Sno,Sname,Cno,Grade
FROM Student LEFT OUTER JOIN SC
ON Student.Sno=SC.Sno
ORDER BY Student.Sno;

--查询所有学生、课程、及选课信息
SELECT Student.Sno,Sname,
Course.Cno,Cname,Credits,Grade
FROM Student LEFT OUTER JOIN SC
ON Student.Sno=SC.Sno
FULL OUTER JOIN Course
ON SC.Cno=Course.Cno
ORDER BY Course.Cno;

• 嵌套查询

一个SELECT-FROM-WHERE语句称为一个查询块。

将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。上层的查询块称为外层查询或父查询，下层查询块称为内层查询或子查询。

SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。

子查询不能使用ORDER BY子句。

1、不相关子查询

子查询的查询条件不依赖于父查询，由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。子查询是被嵌套的，子查询不依赖于父查询，子查询只是父查询的查找条件，查询的是学号和姓名，条件是和李勇在同一院系。查询的是学生学号，条件是选修了数据库这门课。

--查询和李勇同一院系的学生学号及姓名
SELECT Sno,Sname
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname= '李勇');

--查询选修了“数据库”这门课的学生学号和姓名
SELECT Sno,Sname FROM Student 
WHERE Sno  IN
(SELECT Sno FROM SC
WHERE Cno IN
(SELECT Cno FROM Course
WHERE Cname= '数据库')
);

--查询选修了1号课程且成绩最高的学生学号和分数
SELECT Sno,Grade
FROM SC
WHERE Grade>=ALL
(SELECT Grade
FROM SC
WHERE Cno='1')
AND Cno='1';

SELECT Sno,Grade
FROM SC
WHERE Grade>=
(SELECT Max(Grade)
FROM SC
WHERE Cno='1')
AND Cno='1';
以上两种写法功能相同。

--查询选修了1号课程且成绩不是垫底的学生学号和分数
SELECT Sno,Grade
FROM SC
WHERE Grade>ANY
(SELECT Grade
FROM SC
WHERE Cno='1')
AND Cno='1'
ORDER BY Grade DESC;

SELECT Sno,Grade
FROM SC
WHERE Grade>
(SELECT Min(Grade)
FROM SC
WHERE Cno='1')
AND Cno='1'
ORDER BY Grade DESC;
以上两种写法功能相同。

2、相关子查询

子查询的查询条件依赖于父查询，首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组，重复这一过程，直至外层表全部检查完为止。

查询课程号和具体分数，从Y表中查询满足条件的分数

--查询学生超过他选修课程平均分的课程号和具体分数。
SELECT Sno, Cno,Grade
FROM SC X
WHERE Grade >
(SELECT AVG(Grade) 
FROM  SC Y
WHERE Y.Sno=X.Sno);

--查询成绩比该课程平均分低的学生学号和分数
SELECT Sno, Cno,Grade
FROM SC X
WHERE Grade <
(SELECT AVG(Grade) 
FROM  SC Y
WHERE Y.Cno=X.Cno);

EXISTS谓词

存在量词，带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“TRUE”或逻辑假值“FALSE”。

若内层查询结果非空，则外层的WHERE子句返回真值。

若内层查询结果为空，则外层的WHERE子句返回假值。

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

EXISTS之后是嵌套查询，内查询语句为真返回TRUE,为假返回FALSE

内查询的查询带*查询所有的内容。

--查询选修了1号课程的学生学号和姓名
SELECT Sno,Sname
FROM Student
WHERE EXISTS
(SELECT * FROM SC
WHERE Sno=Student.Sno 
AND Cno='1');

--查询和李勇同一院系的学生学号及姓名
SELECT Sno,Sname 
FROM Student S1
WHERE EXISTS
(SELECT * FROM Student S2
WHERE S2.Sdept = S1.Sdept 
AND S2.Sname = '李勇

--查询没有学生选修的课程号和课程名
SELECT Cno,Cname
FROM Course
Where NOT EXISTS
(SELECT *
From Student
WHERE EXISTS
(SELECT *
From SC
WHERE SC.Sno=Student.Sno 
AND SC.Cno=Course.Cno));

--查询选修了17203号学生选修的所有课程的学生学号
SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
(SELECT *
FROM SC SCY
WHERE SCY.Sno = '17203'  
AND NOT EXISTS
(SELECT *
FROM SC SCZ
WHERE SCZ.Sno=SCX.Sno AND
SCZ.Cno=SCY.Cno));

• 集合查询

• 并操作UNION
• 交操作INTERSECT
• 差操作EXCEPT

参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同

集合操作，和用交INTERSECT,或用并UNION，在两个并列查询语句之间使用。

--查询选修了1号和5号课程的学生学号
SELECT SNO FROM SC
WHERE CNO='1'
INTERSECT 
SELECT SNO FROM SC
WHERE CNO='5';

--查询选修了1号或5号课程的学生学号
SELECT SNO FROM SC
WHERE CNO='1'
UNION
SELECT SNO FROM SC
WHERE CNO='5';

--查询选修了1号但没有选修5号课程的学生学号
SELECT SNO FROM SC
WHERE CNO='1'
EXCEPT
SELECT SNO FROM SC
WHERE CNO='5';

--查询计算机系年龄不大于20岁的学生信息
  SELECT *
  FROM Student
  WHERE Sdept='计算机'
  EXCEPT
  SELECT *
  FROM Student
  WHERE Sage>20;