第三章完整版->有道云笔记

• 连接查询

等值与非等值连接查询

连接查询的WHERE子句用来连接两个表的条件称为连接条件或连接谓词，一般格式为：

    [<表名1>.] <列名 1><比较运算符> [<表名2>.] <列名 2>

连接运算符为=时，称为等值连接。使用其他运算符称为非等值连接。
连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但名字不必相同。

    /* 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
       Sname是唯一的，可省略表名前缀，而Sno必须加上表名前缀，避免混淆 */
    SELECT Student.Sno, Sname
    FROM Student, SC
    WHERE Student.Sno = SC.Sno AND          /*  连接谓词 */
            SC.Cno = '2' AND SC.Grade > 90; /*  其他限定条件（选择谓词） */

自身连接

一个表与自己进行连接，要先为表取别名。

    /* 查询每一门课的间接先修课（即先修课的先修课） */
    SELECT FIRST.Cno, SECOND.Cpno
    FROM Course FIRST, Course SECOND
    WHERE FIRST.Cpno = SECOND.Cno;

FIRST表（Course表）

SECOND表（Course表）

连接结果：

外连接： [<表名1>] LEFT | RIGHT OUTER JOIN [<表名2>] ON 谓词

P55 2.4.2中详解：把悬浮元组也保存在结果关系中，而在其他属性上填空值（NULL）。保留左边关系所有元组为左外连接，保留右边关系中所有元组为右外连接。

例：左外连接，列出每个学生的基本情况及选课情况，若某个学生无选课，SC表属性上填NULL。

    SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
    FROM Student LEFT OUTER JOIN SC ON(Student.Sno = SC.Sno);

多表连接

两个以上的表进行连接。

    /* 查询每个学生的学号、姓名、选修的课程名及成绩 */
    SELECT Student.Sno, Sname, Cname, Grade
    From Student, SC, Course
    WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;

• 嵌套查询

一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。上层查询块称为外层查询或父查询，下层查询块称为内层查询或子查询。子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序。

带有IN谓词的子查询（最常用）

    /* 查询与“刘晨”在同一个系学习的学生
       方法一：嵌套查询-不相关查询
       子查询的查询条件不依赖于父查询，称为不相关子查询 */
    SELECT Sno, Sname, Sdept 
    FROM Student 
    WHERE Sdept IN    /* 内查询结果的是一个值，可以用 = 代替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 = '刘晨'; 

[注]：有些嵌套查询可以用连接运算替代，尽可能采用连接运算。

带有比较运算符的子查询

    // 找出每个学生超过他自己选修课程平均成绩的课程号
    // 子查询的查询条件依赖于父查询，称为相关子查询
    SELECT Sno, Cno
    FROM SC x
    WHERE Grade >= (SELECT AVG(Grade)  // 根据x.Sno求出某学生课程的平均成绩
                    FROM SC y
                    WHERE y.Sno = x.Sno);

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

子查询返回多值时，要用ANY或ALL谓词修饰符。

    <!--查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄-->
    SELECT Sname, Sage
    FROM Student
    WHERE Sage <ANY (SELECT Sage 
                    FROM Student 
                    WHERE Sdept = 'CS')
    AND Sdept <> 'CS';

带有EXISTS谓词的子查询

EXISTS代表存在量词，带有EXISTS谓词的子查询不返回任何数据，且产生逻辑真值“true”或逻辑价值“false”。
由EXISTS引出的子查询，其目标列表达式通常都用*

 /* 查询选修了全部课程的学生姓名 */
    SELECT Sname
    FROM Student
    WHERE NOT EXISTS
        (SELECT *
          FROM Course
          WHERE NOT EXISTS
              (SELECT *
                FROM SC
                WHERE Sno = Student.Sno 
                AND Cno = Course.Cno));

[注]：SQL中没有全称量词，但可以把带有全称量词的谓词转换为等价的带有存在量词的谓词，双重否定=>肯定。

• 集合查询

SELECT语句查询的结果是元组的集合，所以多个SELECT语句的结果可进行集合查询。包括并操作UNION、交操作INTERSECT和差操作EXCEPT。

   /* 查询计算机科学系的学生与年龄不大于19岁的学生的交集 */
   SELECT *
   FROM Student
   WHERE Sdept = 'CS'
   INTERSECT
   SELECT *
   FROM Student
   WHERE Sage <= 19;

[注]：参加集合操作的各查询结果列数、对应项的数据类型必须相同。

• 基于派生表的查询

子查询也可以出现在FROM子句中，此时子查询生成的临时派生表成为主查询的查询对象。

    /* 找出每个学生超过他自己选修课程平均成绩的课程号 */
    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.sno AND SC.Grade >= Avg_sc.avg_grade;

• SELECT语句的一般格式 P114