数据库作业9：SQL练习6 - INSERT / UPDATE / DELETE / NULL / VIEW

数据更新

插入数据

两种插入数据方式：
1.插入元组
2.插入子查询结果
*可以一次插入多个元组

语句：

INSERT INTO <表名> [(<属性列1>[,<属性列2 >…)]
VALUES (<常量1> [,<常量2>]… );

插入元祖

【3.69】
将一个新学生元组（学号：201215133;姓名：陈东;性别：男;所在系：IS;年龄：18岁）插入到Student表中。

INSERT
INTO Student(Sno,Sname,Ssex,Sdept,Sage)
VALUES('201215133','陈东','男','IS',18);

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

INSERT
INTO Student
VALUES ('201215121','张成民','男',18,'CS');

INTO 不加属性名会按表中属性顺序插入

[例3.71] 插入一条选课记录（ ‘200215128’,'1 '）

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

其他属性会自动赋予空值
也可用下面方式

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

插入子查询结果

语句格式

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
        (SELECT Sno
         FROM     Student
         WHERE  Sdept= 'CS' );

注意：RDBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则

实体完整性
主码不允许修改
用户定义的完整性

删除数据

语句格式

DELETE
FROM     <表名>
[WHERE <条件>];

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

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

三种删除方式
①删除某一个元组的值
②删除多个元组的值
③带子查询的删除语句
[例3.76] 删除学号为201215128的学生记录。

DELETE
FROM Student
WHERE Sno= '201215128';

会发现有错误，这是因为有外键的约束，我们可以删除约束条件

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

DELETE
FROM SC;

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

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

空值的处理

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

一般有以下几种情况：

1.该属性应该有一个值，但目前不知道它的具体值
2.该属性不应该有值
3.由于某种原因不便于填写

空值是一个很特殊的值，含有不确定性。对关系运算带来特殊的问题，需要做特殊的处理。

空值的产生
[例 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 < 95 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
是对视图进行更新操作时自动添加子查询中的条件

组成视图的属性列名：全部省略或全部指定
全部省略:
由子查询中SELECT目标列中的诸字段组成
全部指定:（明确指定所有列名）
某个目标列是聚集函数或列表达式
多表连接时选出了几个同名列作为视图的字段
需要在视图中为某个列启用新的更合适的名字

关系数据库管理系统执行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
ROM  Student
WHERE  Sdept= 'IS'
WITH CHECK OPTION;

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

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

IS_Student视图就是一个行列子集视图。

基于多个基表的视图
[例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视图中查询平均成绩在60分以上的学生学号和平均成绩

S_G视图定义：

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

SELECT *
FROM   S_G
WHERE  Gavg>=60;

※错误：
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';

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

---

以上就是一些关于SQL数据更新的基础操作概念，还是比较基础，我们应多加掌握。