SQL Server 数据库基本操作入门篇【5】

本文主讲数据查询中的 连接查询 和 嵌套查询（部分），欢迎阅读~

---

一、连接查询

连接查询： 同时涉及两个及以上的表的查询

• 连接条件： 用来连接两个表的条件
  [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
比如：Student.Sno = SC.Sno
• 连接字段： 连接条件中的列名称
  如：Sno就是上面例子中的连接字段
  （ps：连接字段类型必须是可比的，但名字不必相同）

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

当连接运算符为=时，称为等值连接；使用其他运算符称为非等值连接。

等值连接： 连接运算符为 =
????来看例子： 查询每个学生及其选修课程的情况：

SELECT Student.*, SC.*
FROM   Student, SC
WHERE  Student.Sno = SC.Sno;

• 关系代数中的等值连接：Student$\underset{Student.Sno = SC.Sno}\Join$Student.Sno=SC.Sno⋈​SC

（这里为了省事儿SELECT Student.*, SC.*可简写为SELECT *）
如图等值连接即直接把两个表拼在一起了，会有重复的列，如图中的Sno，一会儿要写的自然连接就是去掉了重复列。

• 这里如果不写条件即没有WHERE条件表达式，则打印出的是两个表的笛卡尔积（如下图，列数即两表的列数之和，行数即两表的行数之积，所以有10*9=90个元组，5+3=8个属性列）
  

自然连接
????来看例子： 用自然连接完成上面的那个例子：（即去掉重复列Sno）

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

一条SQL语句可以同时完成选择和连接查询
????来看例子： 查询选修2号课程且成绩在80分以上的所有学生的学号和姓名：

SELECT Student.Sno, Sname
FROM   Student, SC
WHERE  Student.Sno = SC.Sno AND    		               
       SC.Cno = '2' AND SC.Grade > 80;
       
SELECT *
FROM   Student, SC
WHERE  Student.Sno = SC.Sno;  /*把总的打印出来对照*/  

· 执行过程：
先从SC中选择出Cno='2’且Grade>90的元组形成一个中间关系，再和Student中满足连接条件的元组进行连接得到最终的结果（这里讲解操作为主，具体的一些执行过程、方法等我将会在【吐血整理】系列里详细介绍）
（ps：连接操作的执行方法有嵌套循环法、排序合并法等等，不同的DBMS执行方法可能会有所区别，不过出发点都是因为连接查询比较费时间，通过采用先进的高效的方法来优化，提高效率）

2. 自身连接

自身连接： 一个表与其自己进行连接
· 需要给表起别名以示区别（这里只是给它起别名来当作两个或多个表，当实际上它仍然只是一个表，我们仍然只是在对同一个表进行操作）
· 所有属性名都是同名属性，因此必须使用“别名”
????来看例子： 查询每一门课的间接先修课（即先修课的先修课）：

SELECT FIRST.Cno, SECOND.Cpno
FROM   Course FIRST, Course SECOND
WHERE  FIRST.Cpno = SECOND.Cno;

该题求间接先修课，拿下图中的 1 数据库 课程举例：它的先修课Cpno是 5 数据结构，所以数据结构的课程号Cno是等于数据库的Cpno的，而数据结构的先修课Cpno是 7 Pascal语言，所以 1 数据库 的间接先修课就是 7 Pascal语言

3. 外连接

外连接与普通连接的区别：

• ⋈ 普通连接操作只输出满足连接条件的元组
• 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出

外连接分三类：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）、全外连接（FULL OUTER JOIN）
（ps：OUTER可省略）
⟕ 左外连接： 列出左边关系中所有的元组 （即还返回左表中不符合连接条件只符合查询条件的数据行）
⟖ 右外连接： 列出右边关系中所有的元组 （即还返回右表中不符合连接条件只符合查询条件的数据行）
⟗ 全外连接：不仅返回左表中不符合连接条件只符合查询条件的数据行，并且还返回右表中不符合连接条件只符合查询条件的数据行（全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即全外连接 = 左外连接 UNION 右外连接）

????来看例子： 改写上面等值连接的例子：查询每个学生及其选修课程的情况：
1）左外连接

SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM   Student LEFT OUTER JOIN SC ON  (Student.Sno = SC.Sno);  /*LEFT OUTER JOIN可简写为LEFT JOIN*/

SELECT *
FROM   Student, SC
WHERE  Student.Sno = SC.Sno;   /*对照等值连接*/

对比等值连接，这里的左外连接把Student学生表中Cno课程号为空和Sdept为空（不符合连接条件只符合查询条件的数据行）的也都打印了出来，即没选课的学生也在其查询范围内。
（ps：左外连接的查询结果更适用于班主任（了解全班学生的选课情况），而科任老师更喜欢等值连接的结果，因为课程空的学生就是没选他课的学生，不属于自己操心的范围）

2）右外连接

SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM   Student RIGHT OUTER JOIN SC ON  (Student.Sno = SC.Sno);  /*RIGHT OUTER JOIN可简写为RIGHT JOIN*/

SELECT *
FROM   Student, SC
WHERE  Student.Sno = SC.Sno;   /*对照等值连接*/

可见SC表中没有不符合连接条件只符合查询条件的数据行，即SC表的所有元组都符合连接条件
3）全外连接

SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM   Student FULL OUTER JOIN SC ON  (Student.Sno = SC.Sno);  /*FULL OUTER JOIN可简写为FULL JOIN*/

SELECT *
FROM   Student, SC
WHERE  Student.Sno = SC.Sno;   /*对照等值连接*/

因为右表（即SC表）没有不符合连接条件只符合查询条件的数据行，所以这里的全外连接结果等于左外连接。

4. 多表连接

多表连接即 两个以上的表进行连接

????直接来看例子： 查询每个学生的学号、姓名、选修的课程名及成绩：

SELECT Student.Sno, Sname, Cname, Grade
FROM   Student, SC, Course    /*多表连接*/
WHERE  Student.Sno = SC.Sno 
        AND SC.Cno = Course.Cno;

SELECT *
FROM   Student, SC, Course  
WHERE  Student.Sno = SC.Sno 
        AND SC.Cno = Course.Cno;   /*老规矩，打印粗来对照*/

该题姓名只有Student表有，选修的课程名只有Course表有，成绩只有SC表有，所以需要用到三张表

---

二、嵌套查询

嵌套查询：
· 一个SELECT-FROM-WHERE语句称为一个查询块
· 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
例如 如下形式：

SELECT Sname	/*外层查询/父查询*/
FROM   Student
WHERE  Sno IN
          ( SELECT Sno        /*内层查询/子查询*/
            FROM   SC
            WHERE  Cno= '2');

SQL语言允许多层嵌套查询： 即一个子查询中还可以嵌套其他子查询
子查询的限制： 不能使用ORDER BY子句

• 不相关子查询： 子查询的查询条件不依赖于父查询
  由里向外 逐层处理： 即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件
  例如：

SELECT Sno, Sname, Sdept
FROM   Student
WHERE  Sdept IN
            ( SELECT Sdept
              FROM   Student
              WHERE  Sname = '刘晨');

• 相关子查询： 子查询的查询条件依赖于父查询
  首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表
  然后再取外层表的下一个元组
  重复这一过程，直至外层表全部检查完为止
  （如下代码块所示：从外层查询中取出SC的一个元组x，将元组x的Sno值传送给内层查询，执行内层查询，得到值AVG（Grade），用该值代替内层查询，得到外层查询）

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

1. 带有IN谓词的子查询

????直接来看例子： 查询与“刘晨 ”在同一个系学习的学生：
（这里的题意为：知道刘晨，但不知道她在哪个系，所以找与她在同一个系的学生需要先知道她是那一个系的）
方法一：分步完成
① 确定“刘晨”所在系名

SELECT Sdept  
FROM   Student                            
WHERE  Sname = '刘晨';

查询结果为：CS，用作第二步的查询条件
② 查找所有在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 = '刘晨');

此查询为 不相关子查询
方法三：用自身连接（关键在于起别名儿）

SELECT S1.Sno, S1.Sname, S1.Sdept
FROM   Student S1, Student S2
WHERE  S1.Sdept = S2.Sdept AND S2.Sname = '刘晨';

这个连接查询的意思可理解为： 列出Sdept系别相同的所有学生，且刘晨是这个系别的
（ps：对于这个题来说，嵌套查询比连接查询在逻辑上要更清晰一些）

????Another one： 查询选修了课程名为“信息系统”的学生学号和姓名：
利用带有IN谓词的子查询：

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

用连接查询实现：

SELECT Student.Sno, Sname    /*注意！！采用连接查询时这里Sno一定要写清楚表头是哪个表！！！*/
FROM   Student, SC, Course
WHERE  Student.Sno = SC.Sno AND
               SC.Cno = Course.Cno AND
               Course.Cname = '信息系统';

· 3层及以上的查询采用连接查询会更简洁一些，但是理解起来带IN谓词的嵌套查询要容易理解一些，看情况而用吧。

2. 带有比较运算符的子查询

当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!= 或 <>均表示不等于）

????来看例子： 同上面的 查询与“刘晨”在同一个系的学生：
（由于一个学生只可能在一个系学习，则可以用 = 代替IN ）

SELECT Sno, Sname, Sdept
FROM   Student
WHERE  Sdept  =
             ( SELECT Sdept
               FROM   Student
               WHERE  Sname = '刘晨');

????Another one： 找出每个学生超过他选修课程平均成绩的课程号：
（即上面讲相关子查询时候的例子）

SELECT Sno, Cno
FROM   SC x
WHERE  Grade >= ( SELECT AVG (Grade) 
                  FROM   SC y
                  WHERE  y.Sno = x.Sno);
                  SELECT * FROM   SC;
                  
SELECT * FROM  SC;   /*打印出整张表对照*/

SELECT AVG(Grade)
FROM   SC
WHERE  Sno = '201215121';   /*这里我就只打印其中一个学生的平均分叭，要想看其他的换学号就可*/

（执行过程：从外层查询中取出SC的一个元组x，将元组x的Sno值传送给内层查询，执行内层查询，得到值AVG（Grade），用该值代替内层查询，得到外层查询）

3. 带有ANY（SOME）或ALL谓词的子查询

使用ANY或ALL谓词时必须同时使用比较运算
语义为：

• > ANY：大于子查询结果中的某个值（> ANY 表示至少大于一个值，即大于最小值）
  > ALL：大于子查询结果中的所有值（> ALL 表示大于每一个值，即大于最大值）
• < ANY：小于子查询结果中的某个值（< ANY 表示至少小于一个值，即小于最大值）
  < ALL：小于子查询结果中的所有值（< ALL 表示小于每一个值，即小于最小值）
• >= ANY：大于等于子查询结果中的某个值（大于等于最小值）
  >= ALL：大于等于子查询结果中的所有值（大于等于最大值）

如果还是不太清楚的话，赶紧来看看例子叭~
????来看例子： 查询 非 计算机科学系中比计算机科学系 任意一个 学生年龄小的学生姓名和年龄：

SELECT Sname, Sage
FROM   Student
WHERE  Sage < ANY (SELECT Sage
                   FROM   Student
                   WHERE  Sdept = 'CS')
       AND Sdept <> 'CS';       /*这是父查询块中的另一个条件：非CS系*/
       
SELECT Sname, Sage
FROM   Student
WHERE  Sage < 
		   ( SELECT MAX (Sage)
		     FROM   Student
		     WHERE  Sdept = 'CS ')   /*这里题中要求为比任意一个学生小，所以就是小于其最大值*/
       AND Sdept <> 'CS';
 
SELECT * FROM Student

采用ANY谓词嵌套查询的执行过程：
1）首先处理子查询，找出CS系中所有学生的年龄，构成一个集合（20,19）
2）处理父查询，找所有不是CS系且年龄小于20或19（即小于最大值20）的学生
（ps：如果ANY对于你还是不太好理解的话，把它当成SOME应该能好理解一点，SOME 与 ANY 是等效的，即可以在SQL语句中把ANY换成SOME，效果一样。可参考官方文档：用 ANY、SOME 或 ALL 修改的比较运算符）

????Another one： 查询 非 计算机科学系中比计算机科学系 所有 学生年龄都 小 的学生姓名及年龄：

方法一：用ALL谓词

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谓词嵌套查询的执行过程：
1）首先处理子查询，找出CS系中所有学生的年龄，构成一个集合（20,19）
2）处理父查询，找所有不是CS系且年龄小于20和19（即小于最小值19）的学生

????小总结：ANY（或SOME），ALL谓词与聚集函数、IN谓词的等价转换关系 ：

---

那 本文到这里也就结束啦，感谢你耐心地阅读~????，嵌套查询中的重难点：带有EXISTS谓词的子查询将会在下一篇中介绍 ~
这里是一个想把学习过程记录成博客分享给大家的undergraduate，请多关照????
咱们下期 见~

• 上一篇：SQL Server 数据库基本操作入门篇【4】
• 安利一下：【吐血整理】数据库重点知识总结【2】