SQL语句基本操作练习(四)

SQL语句基操(四)——查询

本篇文章记录了第七次作业
使用的数据库是SQL Server，使用的数据库管理软件是SQL Server Management Studio.
上一篇文章：SQL语句基本操作练习(三)
上次的文章讲完了单表查询，这一次我们从连接查询开始继续向后学习。

(一)连接查询

 连接查询一般是指同时涉及两个以上的表的查询。它包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合连接查询。我们先从等值与非等值连接查询说起。

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

 对于连接查询，我们需要用WHERE子句来表示连接。用来表示连接的两个表的条件称为连接条件或连接谓词，一般格式如下：

	[<表名1>.]<列名1>  <比较运算符>  [<表名2>.]<列名2>
	/*或者是*/
	[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>

	/*例子*/
	Student.Sno = SC.Sno
	/*其中的Sno叫做连接字段，必须是可比的*/

 比较运算符有=、>、<、>=、<=、!=(<>)等，当运算符是“=”的时候，我们称之为等值连接，否则称之为非等值连接。我们来看看第一个例子。

[例 3.49]查询每个学生及其选修课的情况

 先进行分析，查询学生和选修课需要用到Student表和SC表，同时查询就需要用到两张表中的公共属性Sno。那么我们可以根据Sno来对两者进行查询。这就是之前讲到的关系代数在数据库中的应用（⋈）。SQL语句如下：

	SELECT  Student.*, SC.*		//这里也可以写成SELECT *
	FROM    Student, SC
	WHERE   Student.Sno = SC.Sno;	//如果没有这一句则是单纯的笛卡尔积

执行结果如下：

[题外话]

 关系数据库执行连接操作一般有3种方法，一是嵌套循环法，二是排序合并法，三是建立索引的索引法。嵌套循环就是写了俩for循环，遍历两个表找出符合条件的输出。排序合并法则需要先进行排序，因为数据有序，所以在筛选的时候不需要遍历所有元组。索引与其类似，但建立索引是提前做的，不算在开销内。一般来说索引和排序合并都比嵌套循环效率要高。

[例 3.50] 对[例 3.49]用自然连接完成

 在等值连接中把目标列中重复的属性列去掉则为自然连接。

	SELECT  Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade	
	/*除Sno外其余属性都是唯一的，所以可以去除表名前缀*/
 	FROM    Student,SC
 	WHERE   Student.Sno = SC.Sno;

可以看到重复的Sno被去掉了：

[例 3.51] 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名

 这个例题告诉我们可以用一条SQL语句同时完成选择和连接查询。此处的WHERE子句是由连接谓词和选择为此组成的复合条件。
 这里我们需要先写下连接然后加上限定条件。同之前一样，这里涉及到Student与SC表，所以连接还是要用Sno.

	SELECT Student.Sno, Sname
    FROM   Student, SC
    WHERE  Student.Sno=SC.Sno  
           AND SC.Cno='2' 	/*此处的SC可以去掉*/
           AND SC.Grade>90;	/*限定条件,除了AND还有OR*/

这里的查询会先挑选出满足条件的元组再进行连接，效率会更高。执行结果如下：

2.自身连接

 一个表与自己进行连接称为表的自身链接。通过自身链接我们可以查询比如SC表中的先行课，即在开始学习这节课之前需要先修习的课。
 如果要进行自身链接，为了避免混淆就需要为该表起别名，具体操作可以看下面的例题。

[例 3.52] 查询每一门课的间接先修课（即先修课的先修课）

 为了查询间接先修课我们需要一个跳板，因为我们不能直接查询到某一门的间接先修课。这个跳板需要用“取别名”这一方式来建立，即把这个表变成两份，参照外码的查询方式来做这个查询。
 在这里我们为Course取了两个别名，一个是FIRST,一个是SECOND。

	SELECT  FIRST.Cno, SECOND.Cpno
    FROM  	Course  FIRST, Course  SECOND
    WHERE 	FIRST.Cpno = SECOND.Cno;	
    /*先查询这门课的先修课，再查询这门先修课的先修课*/

结果如下：

3.外连接

&EMSP;在之前的例 3.49中只出现了选了课的同学的信息，这是因为他们没有选课在SC表中没有相应的元组，导致Student中这些元组在连接时被舍弃了。若是我也想看到别的同学的呢？那么我们就需要使用外连接了。
 外连接有3种，左外连接(LEFT OUTER JOIN/LEFT JOIN)，右外连接(RIGHT OUTER JOIN/RIGHT JOIN)以及外连接(FULL OUTER JOIN/OUTER JOIN)，又叫完整外连接。
 使用左外连接的时候，如果运算符左边的表在右表没有匹配行，也就是查询不到结果，那么它在右表中就会表现为空值。与之相反，右外连接则会让左表中找不到匹配行的属性列表现为空值。而完全外连接则会把它们都表现出来。
 我们先来看例题。

[例 3.53] 用外连接改写例3.49

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

	FROM  Student  FULL OUTER JOIN SC ON  (Student.Sno=SC.Sno);	/*完整外连接*/

第一段语句左外连接的执行结果如下：

 而右外连接则会去除掉后四行表现为空值的元组，完全外连接会把他们都显示出来。读者可以在数据库里尝试一下别的数据，看看结果是否跟我说的一致。

4.多表连接

 当两个以上的表进行连接的时候就是多表连接了，基本思路和两表连接没有什么差别。

[例 3.54] 查询每个学生的学号、姓名、选修的课程名及成绩

 学号、姓名是Student表中的，课程名是Course表中的，成绩是SC表中的。这次的查询涉及了3个表。

	SELECT 	Student.Sno, Sname, Cname, Grade
   	FROM    Student, SC, Course    
   	WHERE 	Student.Sno = SC.Sno 
            AND SC.Cno = Course.Cno;	/*这里就是一连串属性都带出来了*/

Result:

 这里系统先将前两个表进行连接，然后再将结果跟第三张表进行连接。通过多表连接我们可以将三张表的内容显示到一张表内，让数据更加直观。

(二)嵌套查询

 在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。而嵌套查询就是指在WHERE子句或者HAVING短语中嵌入另一个查询块的查询。嵌入的查询块称为子查询或内层查询，被嵌入的查询块称为父查询或外层查询。子查询又分为不相关子查询和相关子查询，而且子查询里面还可以嵌套子查询。另外ORDER BY语句不能在子查询中使用。

1.带有IN谓词的子查询

 因为在嵌套查询中，子查询的结果往往是一个集合，所以我们常用IN谓词进行查询。下面来看看例子。

[例 3.55] 查询与“刘晨”在同一个系学习的学生

 对于这个问题，我们可以先查询出刘晨所在的系，然后查询出这个系中所有的学生，这样基本满足了题目需求。

	SELECT  Sdept  //确定刘晨的系
    FROM    Student                            
    WHERE  	Sname= '刘晨';
    /*得到结果是CS*/
    SELECT   Sno, Sname, Sdept     //查找CS系中所有学生
    FROM     Student                 
    WHERE    Sdept= 'CS';
    /*结果是李勇、刘晨、和汪淼*/

 但是这样写起来比较麻烦，而且看起来比较分散。因此我们可以把它改成嵌套查询。

	SELECT Sno, Sname, Sdept	/*父查询*/
    FROM   Student
   	WHERE  Sdept  IN
                  (SELECT Sdept	/*子查询*/
                   FROM Student
                   WHERE Sname= '刘晨');

 上面就是一个典型的不相关子查询的例子，因为他的查询条件不依赖于父查询，也就是没有什么关系，所以称为不相关子查询。它的处理思路就是由里向外，逐层处理。(套娃实锤了)这样书写相对于分散开来有便于理解、结构紧凑和逻辑性强的特点。与之相反依赖父查询的查询我们称为相关子查询。
 同时这个例题也可以使用自身连接查询来完成。同样需要取两个别名，利用Sdept作为桥梁来查询所有的学生。在此不给出详细的SQL语句，感兴趣的同学可以自己试一下。这也说明了一个查询目标可以有多种查询语句来实现，如何让自己的查询语句效率高而且通俗易懂，这点是需要自己在上面下功夫的。

[例 3.56] 查询选修了课程名为“信息系统”的学生学号和姓名

 这个例题涉及到了Student、Course两个表，但是Student表和Course表没有直接的联系，所以实际上是3个表都涉及到了。思路：先找出"信息系统"在Course表中的课程号，再在SC表中找出选这门课的学生的学号，最后在Student表内找出学生的姓名。

	SELECT Sno,Sname     /*Step 3*/            
  	FROM   Student                          
 	WHERE  Sno  IN
          (SELECT Sno    /*Step 2*/                 
           FROM   SC                         
           WHERE  Cno IN
                  (SELECT Cno    /*Step 1*/         
                   FROM Course
                   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.55即查询与刘晨同一专业的同学的问题中，因为一个学生只可能在一个系学习，即内查询结果是一个值，此处我们可以用=号来代替IN。

[例 3.57] 找出每个学生超过他选修课程平均成绩的课程号

 这里比较难理解，我们先看例子。

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

 我们发现除了有运算符的这一区别，同时还有了x,y这样的东西。其实这样的东西我们之前接触过，在自身连接查询中的别名说的就是它。我们可以看到x在外查询和内查询中都有出现，说明它在两个查询中都有被使用，那么这就是我们之前说到的相关子查询，它的查询是与外查询相关的。
 对于这一段SQL语句来说它会先从外层查询的SC中取出一个Sno值传递给内层查询,然后内层查询号平均值返还给外层，外层再进行条件查询获得结果。如此重复直至所有Sno值查询完毕。
 我们据此可以总结出：相关子查询要先从外层传信息到内层中，然后由内层返还的信息再来处理外层查询，二者息息相关。这就有点那个递归的味道。

3.带有ANY(SOME)或ALL谓词的子查询

 刚刚的子查询只能返回单值，若是需要返回多值，我们要再运算符后面加上ANY或者是ALL谓词。

ALL指的是所有值满足查询条件，ANY指的是只要有任意一个值满足条件即可。

[例 3.58] 查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄

SELECT Sname,Sage
    FROM    Student
    WHERE Sage < ANY (SELECT  Sage		/*小于CS系的任意一个人，只要有一个人满足就行*/
                      FROM    Student
                      WHERE Sdept= 'CS')
    AND Sdept <> 'CS' ;  /*父查询块中的条件，表示不是CS系的 */

[例 3.59] 查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄

SELECT Sname,Sage
    FROM Student
    WHERE Sage < ALL	/*只需要改成ALL*/
                (SELECT Sage
                 FROM Student
                 WHERE Sdept= 'CS')
    AND Sdept <> 'CS';

这两题都可以使用聚集函数来解决，推荐使用聚集函数，因为其效率更高。转化成聚集函数只需要把这些谓词转移到子查询里就行了。可以参照下表来进行聚集函数与ANY(SOME)、ALL等词的转换:

 嵌套查询还没说完，我将在下一篇文章中从嵌套查询的EXISTS谓词接着说。谢谢阅读！如果觉得有用就点个👍⑧。

下一篇文章：SQL语句基本操作练习(五)

 

 
参考文献：
[1]萨师煊，王珊，数据库系统概论.5版.北京:高等教育出版社,2014.
[2]David老师的PPT.