SQL练习6 - INSERT / UPDATE / DELETE / NULL / VIEW

一、插入数据
两种插入数据方式
①、插入元组
②、插入子查询结果：可以一次插入多个元组
格式：
INSERT
INTO <表名> [(<属性列1> [,<属性列2>… )]
子查询;
子查询SELECT子句目标列必须与INTO子句匹配
值的个数
值的类型
[例3.72] 对每一个系，求学生的平均年龄，并把结果存入数据库
第一步：建表

CREATE  TABLE  Dept_age
      ( Sdept     CHAR(15)                     /*系名*/
        Avg_age SMALLINT);          	/*学生平均年龄*/

第二步：插入数据

  INSERT
   INTO  Dept_age(Sdept,Avg_age)
          SELECT  Sdept，AVG(Sage)
          FROM     Student
          GROUP BY Sdept;

RDBMS在执行插入语句时会自动检查所插元组是否破坏表上已定义的完整性规则
二、修改数据
语句格式
UPDATE <表名>
SET <列名>=<表达式>[,<列名>=<表达式>]…
[WHERE <条件>];
功能：
修改指定表中满足WHERE子句条件的元组
SET子句给出<表达式>的值用于取代相应的属性列
如果省略WHERE子句，表示要修改表中的所有元组
[例3.73] 将学生201215121的年龄改为22岁

 UPDATE  Student
 SET Sage=22
 WHERE  Sno=' 201215121 '; 

[例3.74] 将所有学生的年龄增加1岁。

UPDATE Student
         		SET Sage= Sage+1;

[例3.75] 将计算机科学系全体学生的成绩置零。

 UPDATE SC
        SET     Grade=0
        WHERE Sno  IN
               (SELETE Sno
                FROM     Student
                WHERE  Sdept= 'CS' );

三、删除数据
语句格式
DELETE
FROM <表名>
[WHERE <条件>];
功能：
删除指定表中满足WHERE子句条件的元组

WHERE子句：
指定要删除的元组
缺省表示要删除表中的全部元组，表的定义仍在字典中
[例3.76] 删除学号为201215128的学生记录。

 DELETE
     FROM Student
     WHERE Sno= 201215128 ';

删除前：

删除后：

要删去Student表中的学号，要先删去SC表中的学号才能运行。
[例3.77] 删除所有的学生选课记录。

DELETE
    FROM SC;

[例3.78] 删除计算机科学系所有学生的选课记录。

DELETE
		FROM  SC
		WHERE  Sno  IN
			(SELECT  Sno
		            FROM   Student
		            WHERE  Sdept= 'CS') ;

四、空值的处理

空值就是“不知道”或“不存在”或“无意义”的值。
一般有以下几种情况：
①、该属性应该有一个值，但目前不知道它的具体值
②、该属性不应该有值
③、由于某种原因不便于填写

空值具有不确定性。
[例 3.79]向SC表中插入一个元组，学生号是”201215126”，课程号是”1”，成绩为空。

 INSERT INTO SC(Sno,Cno,Grade)
 VALUES('201215126 ','1',NULL); 

或

 INSERT INTO SC(Sno,Cno)
 VALUES(' 201215126 ','1');   

[例3.80] 将Student表中学生号为”201215200”的学生所属的系改为空值。

UPDATE Student
	SET Sdept = NULL
	WHERE Sno='201215200';

判断一个属性的值是否为空值，用IS NULL或IS NOT NULL来表示。
[例 3.81] 从Student表中找出漏填了数据的学生信息

SELECT  *
	FROM Student
	WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL;

不能取空值的三种情况：
①、有NOT NULL约束条件的不能取空值
②、加了UNIQUE限制的属性不能取空值
③、码属性不能取空值
[例3.82] 找出选修1号课程的不及格的学生。

SELECT Sno
   FROM SC
   WHERE Grade < 60 AND Cno='1';

查询结果不包括缺考的学生， 因为他们的Grade值为null。

[例 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);

第二种情况相当于对第一种代码进行了合并。

五、视图
视图的特点：
①、虚表，是从一个或几个基本表（或视图）导出的表
②、只存放视图的定义，不存放视图对应的数据
③、基表中的数据发生变化，从视图中查询出的数据也随之改变。
语句格式
CREATE VIEW
<视图名> [(<列名> [,<列名>]…)]
AS <子查询>
[WITH CHECK OPTION];
（WITH CHECK OPTION：对视图进行更新操作时自动添加子查询中的条件）
组成视图的属性列名：全部省略或全部指定
关系数据库管理系统执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。
[例3.84] 建立信息系学生的视图。

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

视图已经成功建成。
[例3.85]建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生 。

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

带有WITH CHECK OPTION子句，对该视图进行插入、修改和删除操作时，RDBMS会自动加上Sdept='IS’的条件。

若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，我们称这类视图为行列子集视图

[例3.86] 建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）。

CREATE VIEW IS_S1(Sno,Sname,Grade)
    AS 
    SELECT Student.Sno,Sname,Grade
    FROM  Student,SC
    WHERE  Sdept= 'IS' AND
                   Student.Sno=SC.Sno AND
                   SC.Cno= '1';

基于视图的视图：
[例3.87] 建立信息系选修了1号课程且成绩在90分以上的学生的视图。

CREATE VIEW IS_S2
    AS
    SELECT Sno,Sname,Grade
    FROM  IS_S1
    WHERE  Grade>=90;

[例3.88] 定义一个反映学生出生年份的视图。（带表达式的视图）

  CREATE  VIEW BT_S(Sno,Sname,Sbirth)
    AS 
    SELECT Sno,Sname,2014-Sage
    FROM  Student;

[例3.89] 将学生的学号及平均成绩定义为一个视图（分组视图）

  CREATE  VIEW S_G(Sno,Gavg)
         AS  
         SELECT Sno,AVG(Grade)
         FROM  SC
         GROUP BY Sno;

[例3.90]将Student表中所有女生记录定义为一个视图

 CREATE VIEW F_Student(F_Sno,name,sex,age,dept)
  AS
  SELECT  *                        /*没有指定属性列*/
  FROM  Student
  WHERE Ssex=‘女’;

建的视图如下所示：

删除视图：
语句的格式：
DROP VIEW <视图名>[CASCADE];
该语句从数据字典中删除指定的视图定义，如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除 ；删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除 。

[例3.91 ] 删除视图BT_S和IS_S1

DROP VIEW BT_S;	/*成功执行*/
		DROP VIEW IS_S1;	/*拒绝执行*/

注意：
要删除IS_S1，需使用级联删除，同时删除IS_S2：

DROP VIEW IS_S1 CASCADE; 

查询视图与查询基本表相同
RDBMS实现视图查询的方法：
视图消解法（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;

有些情况下，视图消解法不能生成正确的查询
例如：
[例3.94]在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩

SELECT *
FROM   S_G
WHERE  Gavg>=90;

   S_G视图定义： 

    CREATE VIEW S_G (Sno,Gavg)
            AS 
SELECT  Sno,AVG(Grade)
FROM  SC
GROUP BY Sno;

错误：
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.94]可以用如下SQL语句完成

SELECT *
    FROM  (SELECT Sno,AVG(Grade)
		       FROM  SC 
 		       GROUP BY Sno) AS S_G(Sno,Gavg)
    WHERE Gavg>=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.96] 向信息系学生视图IS_S中插入一个新的学生记录，其中学号为”201215129”，姓名为”赵新”，年龄为20岁

INSERT
INTO IS_Student
VALUES(‘201215129’,’赵新’,20);

执行完该语句后。视图中并没有显示该信息，因为前提条件是信息系学生视图，但是该信息并没有说明赵新同学的系别，所以视图中并没有显示，所以应该将该视图的系别默认设置为信息系。

转换为对基本表的更新：

INSERT
INTO   Student(Sno,Sname,Sage,Sdept)
VALUES(‘200215129 ','赵新',20,'IS' );

[例3.97]删除信息系学生视图IS_Student中学号为”201215129”的记录

DELETE
FROM IS_Student
WHERE Sno= ' 201215129 ';

转换为对基本表的更新：

DELETE
FROM Student
WHERE Sno= ' 201215129 ' AND Sdept= 'IS';

一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新

例3.89定义的视图S_G为不可更新视图。
UPDATE  S_G
SET          Gavg=90
WHERE  Sno= '201215121';

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

总结：对这块内容理解的时候不能浅尝辄止，要深入理解它的操作过程以及看懂理解代码的含义，这样才能自己写出代码。