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

数据更新

插入数据

两种方式：
（1）插入元组

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

【例 3.69】

不省去属性列

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

【例 3.70】

省去属性列

INSERT INFO Student
VALUES ('201215126','张老三'，'男'，18，'CS');

【例 3.70】

INSERT INFO SC(Sno,Cno)
VALUES('200215128','1');
或
INSERT INFO SC
VALUES('200215128','1'，NULL);

（2）插入子查询结果（多个元组）

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 <条件>];

功能
（1）修改指定表中满足WHERE子句条件的元组
（2）SET子句给出<表达式>的值用于取代相应的属性列
（3）如果省略WHERE子句，表示要修改表中的所有元组
SET子句用来限制属性列，WHERE子句用来限制元组

三种修改方式
（1）修改某一个元组的值
（2）修改多个元组的值
（3）带子查询的修改语句

修改某一个元组
【例 3.73】将学生201215121的年龄改为22岁

需要用WHERE子句限制元组

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');

删除数据
语句格式

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

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

WHERE子句
（1）删除指定表中满足WHERE子句条件的元组
（2）缺省表示要删除表中的全部元组，表的定义仍在字典中（需要慎重啊啊啊！！！不然辛苦建的表就没啦）

三种删除方式
（1）删除某一个元组的值
（2）删除多个元组的值
（3）带子查询的删除语句

删除某一个元组的值
【例 3.76】删除学号为201215128的学生记录

按照模板往上套就可以啦！

DELECT 
FROM Student
WHERE Sno = '201215128';

删除多个元组的值
【例 3.77】删除所有的学生选课记录

不需要添加WHERE子句，即可删除整张表

DELECT 
FROM SC;

带子查询的删除语句
【例 3.78】删除计算机科学系所有学生的选课记录

先需要筛选出计算机科学系所有学生

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

空值的处理

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

空值的产生：
【例 3.79】向SC表中插入一个元组，学生号是”201215126”，课程号是”1”，成绩为空。

//该学生还没有考试成绩，取空值
INSERT INFO SC（Sno,Cno,Grade）
VALUES('201215126','1',NULL);
//或者为没有赋值的属性，其值为空
INSERT INFO SC(Sno,Cno)
VALUES('201215126','1');

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

相当于修改值

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

判断一个属性的值是否为空值，用IS NULL或IS NOT NULL来表示

【例 3.81】从Student表中找出漏填了数据的学生信息

漏填了的数据为：某位学生的Sname、Ssex、Sage、Sdept为空

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

空值的约束条件
（1）有NOT NULL约束条件的不能取空值
（2）加了UNIQUE限制的属性不能取空值
（3）码属性不能取空值

【例 3.82】找出选修1号课程的不及格的学生

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

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

【例 3.83】选出选修1号课程的不及格的学生以及缺考的学生

(1)利用UNION连接词，将两种情况连接起来

SELECT Sno
FROM SC
WHERE Grade < 60 AND Cno = '1'
UNION 
SELECT Sno
FROM SC
WHERE Grade IS NULL AND Cno = '1'

（2）

SELECT Sno
FROM SC
WHERE Cno = '1' AND (Grade < 60 OR Grade IS NULL);

视图

视图的特点：
（1）虚表，是从一个或几个基本表（视图）导出的表
（2）只存放视图的定义，不存放视图对应的数据
（3）基表中的数据发生变化，从视图中查询出的数据也随之改变

1.定义视图

语句格式

CREATE VIEW
    <视图名>[(列名)[,<列名>...)]
AS <子查询>
[WITH CHECK OPTION];

其中，WITH CHECK OPTION对视图进行更新操作时自动添加子查询的条件

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

关系数据库管理系统执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。
在对视图查询时，按视图的定义从基本表中将数据查出。

【例 3.84】建立信息系学生的视图

取出信息部学生的学号、姓名、年龄建立一个名为IS_Student的视图

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

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

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

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

若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，我们称这类视图为行列子集视图。(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分以上的学生的视图

SELECT 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];

(1)该语句从数据字典中删除指定的视图定义
(2)如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除
(3)删除基表时，由该基表导出的所有视图定义都必须显式地使用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实现视图查询的方法
（1）进行有效性检查
（2）转换成等价的对基本表的查询
（3）执行修正后的查询

【例 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;

可以用以下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';

因为Gavg并不存在对应的基本表中的实际数据！！！！

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