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

数据更新

一、插入数据
1、插入元组
①如果在INTO子句中指出表名和属性名，那么属性名可以打乱顺序，同时VALUES子句中值要与属性名相对应。
②如果只在INTO子句中指出表名，不指出属性名，那么VALUES子句的值一定要与CREATE TABLE中属性列的顺序一样。

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

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

INSERT
INTO CS(Sno,Cno)
VALUES('201215128','1');/*新插入的Grade列上自动赋空值*/
/*或者*/
INSERT
INTO CS
VALUES('201215128','1','NULL');/*因为没有指出属性名，所以要明确给出空值*/

---

2、插入子查询结果

子查询不仅可以嵌套在SELECT语句中用于构造父查询的条件，也可以嵌套在INSERT语句中用于生成要插入的批量数据。

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;/*再把系名和平均年龄存入新表中*/

查询结果：

---

二、修改数据
1、修改某一个元组的值

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

2、修改多个元组的值

UPDATE Student
SET Sage=Sage+1;

(加上1岁，我又给减回去了，我不允许都老一岁！）
3、带子查询的修改语句

子查询也可以嵌套在UPDATE语句中，用于构造修改的条件。

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

---

三、删除数据
①DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。（如果省略WHERE语句则表示删除表中的全部元组）
②DELETE语句删除的是表中的数据，而不是关于表的定义。
1、删除某一个元组的值

DELETE 
FROM Student
WHERE Sno='201215128';

2、删除多个元组的值

DELETE 
FROM SC;

3、带子查询的删除语句

子查询也可以嵌套在DELETE语句中，用于构造执行删除操作的条件。

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

---

空值的处理

1、空值的产生

INSERT INTO SC(Sno,Cno,Grade)
VALUES('201215126','1',NULL);/*没有考试成绩取空值*/
/*或者*/
INSERT INTO SC(Sno,Cno)
VALUES('201215126','1');/*没有赋值的属性，值为空值*/

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

另外，外连接也会产生空值，空值的关系运算也会产生空值。

---

2、空值的判断

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

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

---

3、空值的约束条件

①属性定义（或者域定义）中NOT NULL约束条件的不能为空值；
②加了UNIQUE的限制的属性不能为空值；
③码属性不能为空值。

---

4、空值的算术运算、比较运算和逻辑运算

①空值与另一个（空）值的算数运算结果为空值；
②空值与另一个（空）值的比较运算结果为UNKNOWN;
③有了UNKNOWN后，传统的逻辑运算中的二值逻辑就扩展成了三值逻辑。

SELECT Sno
FROM SC
WHERE Grade<60 AND Cno='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);

---

视图

一、定义视图
1、建立视图
①组成视图的属性列名或全部省略或者全部指定，没有第三种选择；
②如果省略了视图的各个属性列名，则隐含该视图由子查询中SELECT子句目标列中的诸字段组成；
③在下列情况中必须明确指定组成视图的所有列名：
（1）某个目标列不是单纯的属性名，而是聚集函数或者列表达式；
（2）多表连接时选出了几个同名列作为视图的字段；
（3）需要在视图中为某个列启用新的更合适的名字。

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

CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Ssex
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;/*加上该子句后，以后对该视图进行插入、删除和修改操作时，关系数据库管理系统会自动加上Sdept='IS'的条件*/

若一个视图是从单个基本表导出的，并且只是去除了基本表的某些行和某些列，但保留了主码，则称这类视图为行列子集视图。Sdept='IS’视图就是一个行列子集视图。

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

该例说明视图不仅可以建立在单个基本表上，也可以建立在多个基本表上。

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

该例说明视图还可以建立在一个或多个已定义好的视图上，或建立在基本表与视图上。

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

这里视图BT_S是一个带表达式的视图，视图中的出生年份是通过计算得到的。
还可以用带有聚集函数和GROUP BY的子句的查询来定义视图，这种视图称为分组视图。

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

该例中平均成绩是由聚集函数得到的，所以CREATE VIEW中必须明确定义组成S_G视图的各个属性列名。S_G就是一个分组视图。

CREATE VIEW F_Student(F_sno,name,sex,age,dept)
AS
SELECT *
FROM Student
WHERE Ssex='女';

---

2、删除视图
①如果要删除的视图上还导出了其他视图，则使用CASCADE级联删除语句把该视图和它导出的所有视图一起删除。
②基本表删除后，又该基本表导出的所有视图均无法使用了，但是视图的定义没有从字典中删除。

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

拒绝执行是因为IS_S1还导出了IS_S2视图，如果确定要删除，则使用级联删除语句：

DROP VIEW IS_S1 CASCADE;/*删除了视图IS_S1和它导出的所有视图*/

---

二、查询视图

SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;

视图消解：进行有效性检查后，如果存在则取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询然后再执行修正后的查询。
本例转换后为:

SELECT Sno,Sage
FROM Student
WHERE Sdept='IS' AND Sage<20;

查询结果：

SELECT IS_Student.Sno,Sage
FROM IS_Student,SC
WHERE IS_Student.Sno=SC.Sno AND SC.Cno='1';

查询结果：

SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90
GROUP BY Sno;

出现语法错误：
聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。
修改后为：

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

查询成功！
目前多数关系数据库系统对行列子集视图的查询均能进行正确转换。但对非行列子集视图的查询就不一定能做转换了。
该例也可以用生成派生表的方式：

SELECT *
FROM (SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno) AS S_G(Sno,Gavg)
WHERE Gavg>=90;

派生表只是在语句执行时临时定义，语句执行后该定义即被删除。

---

三、更新视图

UPDATE IS_Student
SET Sname='刘辰'
WHERE Sno='201215122';

转换后的更新语句为：

UPDATE Student
SET Sname='刘辰'
WHERE Sdept='IS' AND Sno='201215122';

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

转换为对基本表的更新：

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

这里系统自动将系名‘IS’放入VALUES子句中。

DELETE
FROM IS_Student
WHERE Sno='201215129';

转换为对基本表的更新：

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

---

四、视图的作用

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