数据库学习——数据查询（续）

数据查询

1.单表查询

在我的上一篇文章中已经记录完毕
数据库学习1

2.连接查询

• 连接查询：同时设计两个以上的表的查询
• 连接条件或连接谓词：用来连接两个表的条件一般格式：

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

• 连接字段：连接谓词中的列名称
  连接条件中的各连接字段类型必须是可比的，但名字不必相同

2.1 等值与非等值连接查询

等值连接：连接运算符为=

[例 3.49]  查询每个学生及其选修课程的情况
		         SELECT  Student.*, SC.*
		         FROM     Student, SC
		         WHERE  Student.Sno = SC.Sno;

自然连接：在等值连接中删除目标列中的重复属性列

[例 3.50]  对[例 3.49]用自然连接完成。
 SELECT  Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
 FROM     Student,SC
 WHERE  Student.Sno = SC.Sno;

一条sql语句可以同时完成选择和连接查询，这时，where子句是由连接谓词和选择谓词组成的复合条件

[例 3.51 ]查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。
    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中满足连接条件的元组进行连接得到最终的结果关系

2.2 自身连接

• 自身连接：一个表与自己进行连接
• 需要给表起别名以示区别
• 由于所有属性名都是同名属性，因此必须使用别名前缀

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

如图所示，先给同一张course分别起名字first和second，便于后面的自身连接，要找出每一门课的先修课，就是让表first中的Cpno属性与表second中的cno属性进行连接

2.3 外连接

外连接与普通连接的区别

• 普通连接的操作只输出满足连接条件的元组
• 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
• 左外连接：列出左边关系中所有元组
• 右外连接：列出右边关系中所有的元组

[例 3. 53]     查询每个学生及其选修课程的情况
SELECT  Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
 FROM     Student,SC
 WHERE  Student.Sno = SC.Sno;

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

[补充]：
左外连接
是以左表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分。左连接全称为左外连接，是外连接的一种。右外连接同理

2.4多表连接

[例3.54]查询每个学生的学号、姓名、选修的课程名及成绩
  SELECT Student.Sno, Sname, Cname, Grade
   FROM    Student, SC, Course    /*多表连接*/
   WHERE Student.Sno = SC.Sno 
                  AND SC.Cno = Course.Cno;

3.嵌套查询

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

• 上层的查询块称为外层查询或父查询
• 下层查询块称为内层查询或子查询
• SQL语言允许多层嵌套查询
  即一个子查询中还可以嵌套其他子查询
• 子查询的限制
  不能使用ORDER BY子句
• 不相关子查询：子查询的查询条件不依赖于父查询
  由里向外，逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件
• 相关子查询：子查询的查询条件依赖于父查询
  • 首先取外层查询中的表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若where子句返回值为真，则取此元组放入结果表
  • 然后再取外层表的下一个元组
  • 重复这一过程，直至外层表全部检查完为止

3.1 带有in谓词的子查询

[例 3.55]  查询与“刘晨”在同一个系学习的学生。
         此查询要求可以分步来完成
    ① 确定“刘晨”所在系名             
         SELECT  Sdept  
         FROM     Student                            
         WHERE  Sname= ' 刘晨 ';
	      结果为： CS
	 ② 查找所有在CS系学习的学生。    
        SELECT   Sno, Sname, Sdept     
        FROM      Student                 
        WHERE   Sdept= ' CS '; 
带有in谓词的子查询
将第一步查询嵌入到第二步查询的条件中
    SELECT Sno, Sname, Sdept
    	FROM Student
   	WHERE Sdept  IN
                  (SELECT Sdept
                   FROM Student
                   WHERE Sname= ' 刘晨 ');
    此查询为不相关子查询。

用自身连接完成[例 3.55]查询要求
     SELECT  S1.Sno, S1.Sname,S1.Sdept
      FROM     Student S1,Student S2
      WHERE  S1.Sdept = S2.Sdept  AND
                      S2.Sname = '刘晨';

同时也可以连接查询

SELECT Sno,Sname
      FROM    Student,SC,Course
      WHERE Student.Sno = SC.Sno  AND
                     SC.Cno = Course.Cno AND
                     Course.Cname='信息系统';

3.2 带有比较运算符的子查询

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

在[例 3.55]中，由于一个学生只可能在一个系学习，则可以用 = 代替IN ：
     SELECT Sno,Sname,Sdept
     FROM    Student
     WHERE Sdept   =
                   (SELECT Sdept
                    FROM    Student
                    WHERE Sname= '刘晨');

[例 3.57 ]找出每个学生超过他选修课程平均成绩的课程号。
SELECT Sno,Cno
FROM SC x
WHERE Grade>=(SELECT AVG(Grade)
				FROM SC y
				WHERE y.Sno=x.Sno)

[例3.57]为相关子查询

• 从外层查询中取出SC的一个元组x，将元组x的Sno值（201215121）传送给内层查询。
  SELECT AVG(Grade)
  FROM SC y
  WHERE y.Sno='201215121‘;
• 执行内层查询，得到值88（近似值），用该值代替内层查询，得到外层查询：
  SELECT Sno,Cno
  FROM SC x
  WHERE Grade >=88;
• 执行这个查询，得到
  （201215121,1）
  （201215121,3）
  然后外层查询取出下一个元组重复做上述①至③步骤，直到外层的SC元组全部处理完毕。结果为:
  （201215121,1）
  （201215121,3）
  （201215122,2）

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

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

>ANY 大于子查询结果中的某个值
>ALL 大于子查询结果中的所有值
< ANY 小于子查询结果中的某个值
< ALL 小于子查询结果中的所有值
>= ANY 大于等于子查询结果中的某个值
>= ALL 大于等于子查询结果中的所有值
<= ANY 小于等于子查询结果中的某个值
<= ALL 小于等于子查询结果中的所有值
= ANY 等于子查询结果中的某个值
=ALL 等于子查询结果中的所有值（通常没有实际意义）
!=（或<>）ANY 不等于子查询结果中的某个值
!=（或<>）ALL 不等于子查询结果中的任何一个值

[例 3.58]  查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄
    SELECT Sname,Sage
    FROM    Student
    WHERE Sage < ANY (SELECT  Sage
                                         FROM    Student
                                         WHERE Sdept= ' CS ')
     AND Sdept <> ‘CS ' ;           /*父查询块中的条件 */

执行过程：
（1）首先处理子查询，找出CS系中所有学生的年龄，构成一个集合（20,19）
（2）处理父查询，找所有不是CS系且年龄小于
20 或 19的学生

用聚集函数实现[例 3.58] 

     SELECT Sname,Sage
     FROM   Student
     WHERE Sage < 
                 (SELECT MAX（Sage）  /* 比最大的小*/
                   FROM Student
                   WHERE Sdept= 'CS ')
       AND Sdept <> ' CS ';

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

方法一：用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 ';

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

3.4 带有EXISTS谓词的子查询

• 存在量词 
• 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
• 若内层查询结果非空，则外层的WHERE子句返回真值
• 若内层查询结果为空，则外层的WHERE子句返回假值
  由EXISTS引出的子查询，其目标列表达式通常都用 * ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。

NOT EXISTS

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

连接查询
SELECT  Sname
  FROM     Student, SC
     WHERE  Student.Sno = SC.Sno AND 
                 SC.Cno=‘1’;

[例 3.55]查询与“刘晨”在同一个系学习的学生。
    可以用带EXISTS谓词的子查询替换：
    
 SELECT Sno,Sname,Sdept
     FROM Student S1
      WHERE EXISTS
             　   (SELECT *
                     FROM Student S2
                     WHERE S2.Sdept = S1.Sdept AND
                                   S2.Sname = '刘晨');

4.集合查询

集合操作的种类

• 并操作：UNION
• 交操作：INTERSERT
• 差操作：EXCEPT
  参加集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同
  

[例 3.65]  查询选修了课程1或者选修了课程2的学生。

        SELECT Sno
        FROM SC
        WHERE Cno=' 1 '
        UNION
        SELECT Sno
        FROM SC
        WHERE Cno= ' 2 ';
[例3.66]  查询计算机科学系的学生与年龄不大于19岁的学生	    的交集。

		SELECT *
		FROM Student
		WHERE Sdept='CS' 
		INTERSECT
		SELECT *
		FROM Student
		WHERE Sage<=19 
[例 3.66] 实际上就是查询计算机科学系中年龄不大	于19岁的学生。

		SELECT *
        	FROM Student
        	WHERE Sdept= 'CS' AND  Sage<=19;
[例 3.67]查询既选修了课程1又选修了课程2的学生。
    
	 SELECT Sno
    FROM SC
    WHERE Cno=' 1 ' 
    INTERSECT
    SELECT Sno
    FROM SC
    WHERE Cno='2 ';
[例3.67]也可以表示为：
        
	     SELECT Sno
          FROM    SC
          WHERE Cno=' 1 ' AND Sno IN
            (SELECT Sno
             FROM SC
             WHERE Cno=' 2 ');

差集

[例 3.68]  查询计算机科学系的学生与年龄不大于19岁的学生的差集。

    SELECT *
    FROM Student
    WHERE Sdept='CS'
    EXCEPT
    SELECT  *
    FROM Student
    WHERE Sage <=19;
[例3.68]实际上是查询计算机科学系中年龄大于19岁的学生

        SELECT *
        FROM Student
        WHERE Sdept= 'CS' AND  Sage>19;

5.基于派生表的查询

• 派生表是一种查询表达式派生出虚拟结果表的表达
• 派生表与其他表一样出现在查询的FROM子句中，派生表仅存在于外部查询中
• 派生出来的表必须是一个有效的表，需要遵循以下规则
  所有列必须要有名称
  列名称必须要唯一
  不允许使用ORDER BY(除非指定了TOP)
  

6.SELECT语句的一般格式