数据库-SQL练习4

连接查询

【例3.49】
连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为 表名1.列名1<比较运算符>表名2.列名2 其中的比较运算符主要有=、>、<、>=、<=、!=（或<>）等。
连接字段:连接条件中的列名，注意连接字段的类型必须是可比的，名字不必相同。
运算符为=时称为等值连接，使用其他运算符称为非等值连接。

SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno;
/*等价于*/
SELECT *
FROM Student,SC
WHERE Student.Sno=SC.Sno;

本例中，SELECT子句与WHERE子句中的属性名前都加上了表名前缀，这是为了避免混淆。如果属性名在参加连接的各表中是唯一的，则可以省略表名前缀。
运行本质就是这两个表先进行笛卡尔积，再选出满足条件Student.Sno=SC.Sno的元组。（笛卡尔积 行相乘，列相加）

/*进行笛卡尔积*/
SELECT Student.*,SC.*
FROM Student,SC

下面了解执行过程的3种方法：嵌套循环连接法、排序合并法和索引连接。
嵌套循环连接法：先在Student表中找到第一个元组，然后从头开始扫描SC表，逐一查找与Student表中第一个元组的Sno相等的SC元组，找到就把这两个元组拼接起来，形成结果表中一个元组。重复上述操作直到Student表的所有元组都处理完为止。
排序合并法：先按连接属性对两个表排序（比如是由小到大），对表1的第一个元组从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将这两个元组连接起来，形成结果表的一个元组，直到表2的第一条大于表1连接字段值的元组为止，然后找到表1第二条元组，从中断点处继续顺序扫描，找到满足条件的就连接，形成结果表的一个元组，直到表2又大于表1连接字段值为止。重复上述操作，直到表1或表2中的所有元组都处理完为止。
索引连接：对表2按连接字段建立索引，对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就按表1中的第一个元组与该元组拼接起来，形成结果表中的一个元组。
【例3.50】
若在等值连接中把目标列中重复的列去掉则为自然连接。

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno;

本例中，由于Sname,Ssex,Sage,Sdept,Cno,Grade属性列在Student,SC表中是唯一的，因此引用时可以不加表名前缀；而Sno在两个表中都出现了，因此引用时必须加表名前缀（加Student.或SC.都可以）。
【例3.51】
一条SQL语句可以同时完成选择和连接查询，这是WHERE子句是由连接谓词（AND）和选择谓词组成的复合条件。

SELECT Student.Sno,Sname
FROM Student,SC
WHERE Student.Sno=SC.Sno AND SC.Cno='2' AND SC.Grade>90;

该查询的一种优化（高效）的执行过程是，先从SC表中挑选出Cno='2’并且Grade>90的元组形成一个中间关系，再和Student中满足连接条件的元组进行连接得到最终的结果关系。
【例3.52】查询每一门课的间接先修课（即先修课的先修课）。
连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接。
注意！需要给表起别名以示区别；所有属性名都是同名属性，因此必须使用“别名”。

SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND		/*其实是一张表，起了两个名字，当成两个表用*/
WHERE FIRST.Cpno=SECOND.Cno;

外连接

***外连接与普通连接的区别***普通连接操作只输出满足连接条件的元组；外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出。
【例3.53】

/*左外连接*/
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON(Student.Sno=SC.Sno);

左外连接列出左边关系中所有的元组。

/*右外连接*/
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student RIGHT OUTER JOIN SC ON(Student.Sno=SC.Sno);

右外连接列出右边关系中所有的元组。

【例3.54】查询每个学生的学号、姓名、选修的课程名及成绩。
本查询涉及三张表，学号姓名在Student表，课程名在Course表，成绩在Grade表。

SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

在执行多表连接时，通常是先进行两个表的连接操作，再将其连接结果与第三个表进行连接。

嵌套查询

SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。
上层查询块称为外层查询或父查询，下层查询块称为内层查询或子查询。
SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。
子查询的限制：不能使用ORDER BY子句
1.带有IN谓词的子查询
【例3.55】查询与“刘晨”在同一个系学习的学生
先分步来完成此查询，然后再构造嵌套查询。
1.确定“刘晨”所在系名
SELECT Sdept
FROM Student
WHERE Sname=‘刘晨’；
结果为CS。
2.查找所有在CS系学习的学生。
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept=‘CS’；
将第一步查询嵌入到第二步查询的条件中，构造嵌套查询如下：

/*嵌套查询*/
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
  (SELECT Sdept
  FROM Student
  WHERE Sname='刘晨'
  );
  
  /*比较运算符子查询（用=代替IN）*/
  SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept =
  (SELECT Sdept
  FROM Student
  WHERE Sname='刘晨'
  );
  
  
  /*这个查询用自身连接也能完成*/
SELECT S1.Sno,S1.Sname,S1.Sdept
FROM Student S1,Student S2
WHERE S1.Sdept=S2.Sdept AND S2.Sname='刘晨';

本例中，子查询不依赖父查询，称为不相关子查询，求解方法由里向外处理，即先执行子查询，子查询的结果用于建立其父查询的查找条件。这时就简化成
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN( (‘CS’);
然后执行该语句。
【例3.56】查询选修了课程名为“信息系统”的学生学号和姓名。
本查询涉及学号、姓名和课程名三个属性。学号和姓名存放在Student表中，课程名存放在Course表中，但Student与Course两个表之间没有直接联系，必须通过SC表建立他们二者之间的联系。所以本次查询实际涉及三个关系。

/*嵌套查询*/
SELECT Sno,Sname
FROM Student 			/*3.最后在Student关系中取出Sno和Sname*/
WHERE Sno IN
  (SELECT Sno
  FROM SC			/*2.然后在SC关系中找出选修了3号课程的学生学号*/
  WHERE Cno IN
  (SELECT Cno
  FROM Course			/*1.首先在Course关系中找出“信息系统”的课程号，结果为3号*/
  WHERE Cname='信息系统'
  )
  );

/*连接查询*/
SELECT Student.Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Course.Cname='信息系统';

子查询的查询条件不依赖于父查询，是不相关子查询。
有些嵌套查询可以用连接运算替代，有些是不能替代的。
2.带有比较运算符的子查询
带有比较运算符的子查询指的是父查询与子查询之间用比较运算符进行连接。
【例3.57】找出每个学生超过他自己选修课程平均成绩的课程号

SELECT Sno,Cno
FROM SC x
WHERE Grade>=(	SELECT AVG(Grade)
   		FROM SC y
   		WHERE y.Sno=x.Sno);

子查询里有父查询的x，所以子查询依赖于父查询，这种查询称为相关子查询。
相关子查询方法：首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表中；然后再取外层表的下一个元组，重复这一过程，直至外层表全部检查完为止。
求解相关子查询不能像求解不相关子查询那样一次将子查询求解出来，然后求解父查询。内层查询由于与外层查询有关，因此必须反复求值。
x称为表SC的别名，又称为元组变量，可以用来表示SC的一个元组，内层查询是求一个学生所有选修课程的平均成绩的，至于是哪个学生要看参数x.Sno的值。
执行过程：首先从外层查询中取出SC的一个元组x，将x的Sno值（201215121）传达给内层查询；然后执行内层查询，得到值（近似值），用该值代替内层查询，得到外层查询；执行这个查询得到结果后外层查询取出下一个元组，重复上述操作。
3.带有ANY（SOME）或ALL谓词的子查询
子查询返回单值时可以用比较运算符，但返回多值时要用ANY（有的系统用SOME）或ALL谓词修饰符。使用ANY或ALL谓词时则必须同时使用比较运算符。
【例3.58】查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄。

SELECT Sname,Sage
FROM Student
WHERE Sage<ANY (SELECT Sage
    		FROM Student
    		WHERE Sdept='CS')
AND Sdept<>'CS';   /*父查询的条件*/

/*用聚集函数实现*/
SELECT Sname,Sage
FROM Student
WHERE Sage<
   (SELECT MAX(Sage)
   FROM Student
   WHERE Sdept='CS')
AND Sdept<>'CS';

比计算机科学系任意一个学生年龄小，意思是比计算机科学系的所有学生其中的一个年龄小就可以。
<ANY 小于子查询结果中的某个值 等价于 聚集函数的<MAX
执行此查询时，先处理子查询，找出CS系中所有学生的年龄，构成一个集合，然后处理父查询，找所有不是CS系且年龄小于那个集合的最大值的学生。
【例3.59】查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

SELECT Sname,Sage
FROM Student
WHERE Sage<ALL (SELECT Sage
    FROM Student
    WHERE Sdept='CS'
    )
AND Sdept<>'CS';

/*用聚集函数实现*/
SELECT Sname,Sage
FROM Student
WHERE Sage<
   (SELECT MIN(Sage)
   FROM Student
   WHERE Sdept='CS')
AND Sdept<>'CS';

比计算机科学系所有学生年龄都小，意思是和计算机科学系所有学生相比年龄最小。
<ALL 小于子查询结果中的所有值 等价于 聚集函数中的<MIN
ANY、ALL与聚集函数的对应关系还有
=ANY 等于子查询结果中的某个值 等价于 IN
<>或!=ALL 不等于子查询结果中的任何一个值 等价于 NOT IN

总结：这次写的是连接查询和嵌套查询。连接查询有等值连接，自然连接，自身连接和外连接（左外连接和右外连接）；嵌套查询有包含IN谓词的，包含比较运算符的，包含ANY或ALL的子查询。