数据库总结（三）

关系数据库标准语言SQL

3.1  数据定义 
SQL的数据定义功能: 
模式定义
表定义
视图和索引的定义 

3.2数据查询
语句格式
       SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …
       FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)      
                   [AS]<别名>
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];
SELECT子句：指定要显示的属性列
FROM子句：指定查询对象（基本表或视图）
WHERE子句：指定查询条件
GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。
HAVING短语：只有满足指定条件的组才予以输出
ORDER BY子句：对查询结果表按指定列值的升序或降序排序 

连接查询 
连接查询：同时涉及两个以上的表的查询
连接条件或连接谓词：用来连接两个表的条件
     一般格式：
[<表名1>.]<列名1>  <比较运算符>  [<表名2>.]<列名2>
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>
连接字段：连接谓词中的列名称
连接条件中的各连接字段类型必须是可比的，但名字不必相同

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

[例3.54]查询每个学生的学号、姓名、选修的课程名及成绩
  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 Sname                               /*外层查询/父查询*/
     FROM Student
     WHERE Sno IN
                        ( SELECT Sno        /*内层查询/子查询*/
                          FROM SC
                          WHERE Cno= ' 2 ');

带有IN谓词的子查询
将第一步查询嵌入到第二步查询的条件中
    SELECT Sno, Sname, Sdept
        FROM Student
       WHERE Sdept  IN
                  (SELECT Sdept
                   FROM Student
                   WHERE Sname= ' 刘晨 ');
    此查询为不相关子查询。

带有比较运算符的子查询
可能的执行过程（续） 
执行内层查询，得到值88（近似值），用该值代替内层查询，得到外层查询：
           SELECT Sno,Cno
           FROM     SC x
          WHERE  Grade >=88

带有ANY（SOME）或ALL谓词的子查询 
使用ANY或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 ' ;           /*父查询块中的条件 */

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

NOT EXISTS谓词
若内层查询结果非空，则外层的WHERE子句返回假值
若内层查询结果为空，则外层的WHERE子句返回真值
[例 3.61]  查询没有选修1号课程的学生姓名。
     SELECT Sname
     FROM     Student
     WHERE NOT EXISTS
                   (SELECT *
                    FROM SC
                    WHERE Sno = Student.Sno AND Cno='1')

集合查询
[例 3.64]  查询计算机科学系的学生及年龄不大于19岁的学生。
        SELECT *
        FROM Student
        WHERE Sdept= 'CS'
        UNION
        SELECT *
        FROM Student
        WHERE Sage<=19;

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

WHERE子句的条件表达式的可选格式
（1）
                            <属性列名>
<属性列名> θ      <常量>
                        [ANY|ALL] （SELECT语句）
 
（2）                         
                        <属性列名>                    <属性列名>  
<属性列名> [NOT] BETWEEN   <常量>               AND   <常量>      
                                       （SELECT语句）          （SELECT语句）
（3）                       （<值1>[，<值2> ] …）
 <属性列名> [NOT] IN                    
                                   （SELECT语句）
 
  （4）   <属性列名> [NOT] LIKE <匹配串>
  （5）  <属性列名> IS [NOT] NULL
  （6）  [NOT] EXISTS （SELECT语句）
（7）
                  AND                        AND
 <条件表达式>                <条件表达式>                   <条件表达>    …
                  OR                          OR

x       y	x   AND   y	x    OR     y	NOT    x
T      T	T	T	F
T      U	U	T	F
T      F	F	T	F
U     T	U	T	U
U     U	U	U	U
U     F	F	U	U
F     T	F	T	T
F     U	F	U	T
F     F	F	F	T

 

[例 3.83]  选出选修1号课程的不及格的学生以及缺考的学生
SELECT Sno
FROM SC
WHERE Grade < 60 AND Cno='1'
UNION
SELECT Sno
FROM SC
WHERE Grade IS NULL AND Cno='1'
或者
SELECT Sno
FROM SC
WHERE Cno='1' AND (Grade<60 OR Grade IS NULL);

3.7  视图视图的特点
虚表，是从一个或几个基本表（或视图）导出的表
只存放视图的定义，不存放视图对应的数据
基表中的数据发生变化，从视图中查询出的数据也随之改变
1. 建立视图
语句格式
       CREATE  VIEW 
             <视图名>  [(<列名>  [,<列名>]…)]
       AS  <子查询>
       [WITH  CHECK  OPTION];
WITH CHECK OPTION
对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）
子查询可以是任意的SELECT语句，是否可以含有ORDER BY子句和DISTINCT短语，则决定具体系统的实现。

[例3.84]  建立信息系学生的视图。

        CREATE VIEW IS_Student
        AS 
        SELECT Sno,Sname,Sage
        FROM     Student
        WHERE  Sdept= 'IS';

2. 删除视图
语句的格式：
        DROP  VIEW  <视图名>[CASCADE];
该语句从数据字典中删除指定的视图定义
如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除 
删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除 
   [例3.91 ] 删除视图BT_S和IS_S1
        DROP VIEW BT_S;    /*成功执行*/
        DROP VIEW IS_S1;    /*拒绝执行*/
          
           要删除IS_S1，需使用级联删除：
           DROP VIEW IS_S1 CASCADE;            
3.7.2  查询视图
用户角度：查询视图与查询基本表相同
关系数据库管理系统实现视图查询的方法
视图消解法（View Resolution）
进行有效性检查
转换成等价的对基本表的查询
执行修正后的查询
[例3.92]  在信息系学生的视图中找出年龄小于20岁的学生。
       SELECT   Sno,Sage
       FROM      IS_Student
       WHERE   Sage<20;

视图消解转换后的查询语句为：
 SELECT  Sno,Sage       
 FROM  Student
 WHERE  Sdept= 'IS'  AND  Sage<20;
错误：
SELECT Sno，AVG(Grade)
FROM     SC
WHERE  AVG(Grade)>=90
GROUP BY Sno;

正确：
SELECT  Sno,AVG(Grade)
FROM  SC
GROUP BY Sno
HAVING AVG(Grade)>=90;

更新视图
[例3.95]  将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”刘辰”。
UPDATE  IS_Student
SET  Sname= '刘辰'
WHERE  Sno= ' 201215122 ';
转换后的语句：
UPDATE  Student
SET Sname= '刘辰'
WHERE Sno= ' 201215122 ' AND Sdept= 'IS';

3.7.4  视图的作用
视图能够简化用户的操作
视图使用户能以多种角度看待同一数据 
视图对重构数据库提供了一定程度的逻辑独立性 
视图能够对机密数据提供安全保护
适当的利用视图可以更清晰的表达查询