数据库SQL知识（3）

3.5 数据更新

 3.5.1  插入数据

两种插入数据方式

Ⅰ 插入元组

（1）语句格式

  INSERT

  INTO <表名> [(<属性列1>[,<属性列2 >…)]

  VALUES (<常量1> [,<常量2>]… );

（2）功能：将新元组插入指定表中

（3） INTO子句

n指定要插入数据的表名及属性列

n属性列的顺序可与表定义中的顺序不一致

n没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致

n指定部分属性列：插入的元组在其余属性列上取空值

（4）VALUES子句

n 提供的值必须与INTO子句匹配

• 值的个数
• 值的类型

[例 1.1] 插入一条选课记录（ '200215128','1 '）。

    INSERT

    INTO SC(Sno,Cno)

    VALUES ('201215128 ',' 1 ');

  注意： 关系数据库管理系统将在新插入记录的Grade列上自动地赋空值。

   或者：

    INSERT

    INTO SC

    VALUES (' 201215128 ',' 1 ',NULL);

[例 1.2]将学生张成民的信息插入到Student表中。

     INSERT

      INTO  Student       //或者   INTO  Student (Sno,Sname,Ssex,Sdept,Sage)

      VALUES ('201215126','张成民','男’,18,'CS');

Ⅱ 插入子查询结果——可以一次插入多个元组 

（1）语句格式

    INSERT

     INTO <表名>  [(<属性列1> [,<属性列2>…  )]

   子查询;

 （2）INTO子句的子查询

SELECT子句目标列必须与INTO子句匹配

Ø值的个数

Ø值的类型

[例2]  对每一个系，求学生的平均年龄，并把结果存入数据库

第一步：建表

      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;

3.5.2  修改数据

（1）语句格式

    UPDATE  <表名>

    SET  <列名>=<表达式>[,<列名>=<表达式>]…

    [WHERE <条件>];

（2)功能

n修改指定表中满足WHERE子句条件的元组

nSET子句给出<表达式>的值用于取代相应的属性列

n如果省略WHERE子句，表示要修改表中的所有元组

(3)三种修改方式

n修改某一个元组的值

[例3]  将学生201215121的年龄改为22岁

         UPDATE  Student

         SET Sage=22

         WHERE  Sno=' 201215121 ';

n 修改多个元组的值

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

           UPDATE Student

           SET Sage= Sage+1;

n 带子查询的修改语句

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

        UPDATE SC

        SET     Grade=0

        WHERE Sno  IN

               (SELECT Sno

                FROM     Student

                WHERE  Sdept= 'CS' );

 3.5.3  删除数据

(1)语句格式

        DELETE

       FROM     <表名>

       [WHERE <条件>];

(2)功能:删除指定表中满足WHERE子句条件的元组

(3)WHERE子句

n指定要删除的元组

n缺省表示要删除表中的全部元组，表的定义仍在字典中

(4)三种删除方式

n删除某一个元组的值

    [例6]  删除学号为201215128的学生记录。

        DELETE

         FROM Student

         WHERE Sno= 201215128 ';

n 删除多个元组的值

    [例7]  删除所有的学生选课记录。

        DELETE

        FROM SC;

n带子查询的删除语句

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

  DELETE

  FROM  SC

  WHERE  Sno  IN

              (SELECT  Sno

              FROM   Student

              WHERE  Sdept= 'CS') ;

3.6 空值的处理

 (1)空值就是“不知道”或“不存在”或“无意义”的值。

(2)一般有以下几种情况：

n该属性应该有一个值，但目前不知道它的具体值

n该属性不应该有值

n由于某种原因不便于填写

(3)空值的判断：用IS NULL或IS NOT NULL来表示。 

（4）空值的约束条件

在属性定义（或者域定义）中

• 有NOT NULL约束条件的不能取空值
• 加了UNIQUE限制的属性不能取空值
• 码属性不能取空值

（5）空值的算术运算、比较运算和逻辑运算 

• 空值与另一个值（包括另一个空值）的算术运算的结果为空值
• 空值与另一个值（包括另一个空值）的比较运算的结果为UNKNOWN。
• 有UNKNOWN后，传统二值（TRUE，FALSE）逻辑就扩展成了三值逻辑
•        T表示TRUE，F表示FALSE，U表示UNKNOWN

[例 9]  选出选修1号课程的不及格的学生以及缺考的学生。 

SELECT Sno

FROM SC

WHERE Cno='1' AND (Grade<60 OR Grade IS NULL);

3.7 视图

视图的特点

n虚表，是从一个或几个基本表（或视图）导出的表

n只存放视图的定义，不存放视图对应的数据

n基表中的数据发生变化，从视图中查询出的数据也随之改变

3.7.1  定义视图

1.建立视图

(1)语句格式

       CREATE  VIEW

             <视图名>  [(<列名>  [,<列名>]…)]

       AS  <子查询>

       [WITH  CHECK  OPTION];

(2)WITH CHECK OPTION

对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）

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

         CREATE VIEW IS_Student

        AS

        SELECT Sno,Sname,Sage

        FROM  Student

        WHERE  Sdept= 'IS'

        WITH CHECK OPTION;

 基于多个基表的视图

[例10.2]  建立信息系选修了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';

基于视图的视图

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

        CREATE VIEW IS_S2

        AS

        SELECT Sno,Sname,Grade

        FROM  IS_S1      //已建立的视图

        WHERE  Grade>=90;

分组视图

[例10.4]  将学生的学号及平均成绩定义为一个视图

         CREAT  VIEW S_G(Sno,Gavg)

             AS 

             SELECT Sno,AVG(Grade)

             FROM  SC

             GROUP BY Sno;

2.删除视图

（1）语句的格式：

  DROP  VIEW  <视图名>[CASCADE];

n该语句从数据字典中删除指定的视图定义

n如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除

n删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除

3.7.2  查询视图

v查询视图与查询基本表相同

v关系数据库管理系统实现视图查询的方法——视图消解法

[例 11 ]  在信息系学生的视图中找出年龄小于20岁的学生。

       SELECT   Sno,Sage

       FROM      IS_Student

       WHERE   Sage<20;

视图消解转换后的查询语句为：

 SELECT  Sno,Sage      

 FROM  Student

 WHERE  Sdept= 'IS'  AND  Sage<20;

3.7.3  更新视图 

[例 12.1]  将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”刘辰”。

UPDATE  IS_Student

SET  Sname= '刘辰'

WHERE  Sno= ' 201215122 ';

[例12.2]  向信息系学生视图IS_S中插入一个新的学生记录，其中学号为”201215129”，姓名为”赵新”，年龄为20岁

INSERT

INTO IS_Student

VALUES(‘201215129’,’赵新’,20);

[例12.3]  向信息系学生视图IS_S中插入一个新的学生记录，其中学号为”201215129”，姓名为”赵新”，年龄为20岁

INSERT

INTO IS_Student

VALUES(‘201215129’,’赵新’,20);

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

例：例3.89定义的视图S_G为不可更新视图。

UPDATE  S_G

SET          Gavg=90

WHERE  Sno= '201215121';    //这个对视图的更新无法转换成对基本表SC的更新

（2）DB2对视图更新的限制：

n若视图是由两个以上基本表导出的，则此视图不允许更新。

n若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。

n若视图的字段来自集函数，则此视图不允许更新。

n若视图定义中含有GROUP BY子句，则此视图不允许更新。

n若视图定义中含有DISTINCT短语，则此视图不允许更新。

n若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。

 3.7 小结

Ⅰ SQL可以分为数据定义、数据查询、数据更新、数据控制四大部分

Ⅱ求解过程

(1) FROM：指明操作对象

(2) WHERE：选取

(3) GROUP：分组

(4) HAVING：选组

(5) SELECT：投影

(6) ORDER：排序