SQL语句（2）

最新推荐文章于 2023-06-24 10:44:17 发布

Flora_M

最新推荐文章于 2023-06-24 10:44:17 发布

阅读量1.3k

点赞数 1

分类专栏：数据库原理

本文链接：https://blog.csdn.net/Flora_SM/article/details/84475570

版权

数据库原理专栏收录该内容

15 篇文章 14 订阅

订阅专栏

3.4 数据查询

3.4.2 连接查询

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

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

         SELECT Student.*, SC.* //等值连接

        FROM     Student, SC

         WHERE Student.Sno = SC.Sno;

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade //自然连接——去掉重复的列

FROM     Student,SC

WHERE Student.Sno = SC.Sno;

注意：一条SQL语句可以同时完成选择和连接查询，这时WHERE子句是由连接谓词和选择谓词组成的复合条件。

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

    SELECT Student.Sno, Sname

    FROM     Student, SC

    WHERE Student.Sno=SC.Sno AND  SC.Cno=' 2 ' AND SC.Grade>90;

2.自身连接

v自身连接：一个表与其自己进行连接

v需要给表起别名以示区别

v由于所有属性名都是同名属性，因此必须使用别名前缀

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

    SELECT FIRST.Cno, SECOND.Cpno

     FROM Course FIRST, Course SECOND

     WHERE FIRST.Cpno = SECOND.Cno;

3.外连接

外连接与普通连接的区别

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

（1）左外连接：列出左边关系中所有的元组

（2）右外连接：列出右边关系中所有的元组

改写[例1]

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade

FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);

4.多表连接

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

SELECT Student.Sno, Sname, Cname, Grade //多表连接时相同属性要加表名前缀

FROM Student, SC, Course /*多表连接*/

WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno; //3个表两个连接条件

3.4.3 嵌套查询

1.概述：

一个SELECT-FROM-WHERE语句称为一个查询块
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
子查询的限制：不能使用ORDER BY子句

SELECT Sname                            /*外层查询/父查询*/

     FROM Student

     WHERE Sno IN

                        ( SELECT Sno        /*内层查询/子查询*/

                          FROM SC

                          WHERE Cno= ' 2 ');

2.求解方法

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

（2）相关子查询：子查询的查询条件依赖于父查询

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

1.带有IN谓词的子查询

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

    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 = '刘晨';

因此，哪种方法较为简便就用哪种

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

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

在[例 5]中，由于一个学生只可能在一个系学习，则可以用 = 代替IN

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

   SELECT Sno, Cno

    FROM    SC x

    WHERE Grade >=(SELECT AVG（Grade）

FROM SC y

                                   WHERE y.Sno=x.Sno); //相关子查询

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

使用ANY或ALL谓词时必须同时使用比较运算

语义为：

> ANY 大于子查询结果中的某个值（大于最小值）

> ALL 大于子查询结果中的所有值（大于最大值）

< ANY 小于子查询结果中的某个值（小于最大值）

< ALL 小于子查询结果中的所有值（小于最小值）

!=（或<>）

······

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

    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 ';

【例8】若查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄

①将上面的谓词ANY换成ALL

②将上面的聚集函数改成MIN（Sage)

4.带有EXISTS谓词的子查询

Ⅰ：（1）EXISTS谓词

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

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

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

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

（2）NOT EXISTS谓词

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

[例 9]查询所有选修了1号课程的学生姓名.

     SELECT Sname

     FROM Student

     WHERE EXISTS

                   (SELECT *

                    FROM SC

                    WHERE Sno=Student.Sno AND Cno= ' 1 ');

Ⅱ：不同形式的查询间的替换

一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换

Ⅲ：用EXISTS/NOT EXISTS实现全称量词（难点）

SQL语言中没有全称量词
可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：

[例 10] 查询选修了全部课程的学生姓名。

        SELECT Sname

        FROM Student

        WHERE NOT EXISTS

                      (SELECT *

                        FROM Course

                        WHERE NOT EXISTS

                                      (SELECT *

                                       FROM SC

                                       WHERE Sno= Student.Sno AND Cno= Course.Cno

                                      )

                       );

Ⅳ 用EXISTS/NOT EXISTS实现逻辑蕴涵（难点）

        [例 11] 查询至少选修了学生201215122选修的全部课程的学生号码。

用NOT EXISTS谓词表示：

       SELECT DISTINCT Sno

       FROM SC SCX

       WHERE NOT EXISTS

                     (SELECT *

                      FROM SC SCY

                      WHERE SCY.Sno = ' 201215122 ' AND

                                    NOT EXISTS

                                    (SELECT *

                                     FROM SC SCZ

                                     WHERE SCZ.Sno=SCX.Sno AND

                                                   SCZ.Cno=SCY.Cno));

3.4.4 集合查询

集合操作的种类

n并操作UNION

n交操作INTERSECT

n差操作EXCEPT

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

[例 12] 查询计算机科学系的学生及年龄不大于19岁的学生。

        SELECT *

        FROM Student

        WHERE Sdept= 'CS'

        UNION

        SELECT *

        FROM Student

        WHERE Sage<=19;

UNION：将多个查询结果合并起来时，系统自动去掉重复元组
UNION ALL：将多个查询结果合并起来时，保留重复元组

3.4.5基于派生表的查询

Ⅰ子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表（Derived Table）成为主查询的查询对象

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

    SELECT Sno, Cno

    FROM SC, (SELECT Sno, Avg(Grade)

                        FROM SC

GROUP BY Sno)

                        AS   Avg_sc(avg_sno,avg_grade) //形成一个派生表

    WHERE SC.Sno = Avg_sc.avg_sno and SC.Grade >=Avg_sc.avg_grade

Ⅱ 如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其缺省属性。

[例14]查询所有选修了1号课程的学生姓名，可以用如下查询完成：

    SELECT Sname

    FROM     Student,

                   (SELECT Sno FROM SC WHERE Cno=' 1 ') AS SC1

    WHERE Student.Sno=SC1.Sno;