数据库知识整理 - 关系数据库标准语言SQL（三）- 数据更新与视图

主要内容

• 数据更新

1. 插入数据

2. 修改数据

3. 删除数据

• 视图

1. 定义视图

2. 删除视图

3. 查询视图

4. 更新视图

5. 视图的作用

---

 

数据更新

数据操纵包括数据查询和数据更新，数据更新又分为三类：插入、修改和删除。

 

1. 插入数据

SQL的数据插入语句INSERT通常有两种形式，一种是插入单个元组，另一种是插入子查询结果（即元组的集合）。

（1）插入单个元组

//将一个新学生元组（学号：20170330，姓名：圈毛，性别：男，年龄：未婚）插入Student表中：

INSERT

INTO Student(Sno,Sname,Ssex,Sage)           /*INSERT INTO表示插入到...表*/

VALUES ('20170330','圈毛','男','未婚');            /*VALUES表示赋值，这SQL语言真是良心到发指*/

<分析> 

1）INTO子句中将Student表中的所有属性列写出，这样相当于 INTO Student

2）若将INTO子句中的属性列打乱，只要VALUE子句中的赋值一一对应，就不会出错

3）例子中出现了一个赋值问题，Sage是整型数值，VALUE子句却赋了字符型数据，这是会出错的

4）还有一个注意点，我们没有在INTO子句中写出Sdept属性列，也没有给它赋值，这样系统会自动取NULL。如果没有写出任何属性列，我们需要在赋值时明确地给出NULL。

 

（2）插入子查询结果

将子查询嵌套在INSERT语句中。

//对每一个系的学生就平均年龄，并把结果存入数据库：

首先建一个新表，用于存放每一个系的平均年龄：（可回顾“基本表的定义”）

CREATE TABLE Dept_avgage

                       (Sdept CHAR(20) PRIMARY KEY,

                        Avg_age SMALLINT);

接着将数据插入新表：

INSERT

INTO Dept_avgage

SELECT Sdept,AVG(Sage)         /*整个子查询结果*/

FROM Student

GROUP BY Sdept

 

2. 修改数据

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

（1）修改单个元组

//将学号为20170330的学生年龄改为20：

UPDATE Student                   /*选择要更新的表*/

SET Sage = '20'                     /*设置新数据*/

WHERE Sno = '20170303'     /*选择条件确定修改的元组*/

 

（2）修改多个元组

//将所有学生年龄+1岁：

UPDATE Student

SET Sage = Sage+1;              /*没有WHERE子句就表示全员修改*/

 

（3）带子查询的修改

//将所有计算机科学系的学生成绩归零：

<分析> 成绩信息在SC表，学生院系信息在Student表，可以用子查询连接两个表

UPDATE SC

SET Grade = 0

WHERE Sno IN

             (SELECT Sno

              FROM Student

               WHERE Sdept = 'CS');

 

3. 删除数据

删除语句的语法和修改语句类似。但是要注意的是，删除语句只删除表中的数据，不可能删除表在数据字典中的定义。

（1）删除单个元组

//删除学号为20170330的学生的学生记录：

DELETE 

FROM Student

WHERE Sno = '20170330';

 

（2）删除多个元组:

//删除所有学生选课记录：

DELETE

FROM SC

 

（3）带子查询的删除

//删除所有计算机科学系学生的选课记录

DELETE

FROM SC

WHERE Sno IN

             (SELECT Sno

              FROM Student

               WHERE Sdept = 'CS');

 

*对某个基本表的增、删、改操作都有可能破坏参照完整性，这部分内容会在之后的篇章做介绍。

---

 

视图

视图可以从一个或几个基本表（或视图）中导出，而且与基本表不同，它是一个虚表。

数据库中只存放视图的定义，而不存放它的数据。准确地说，它没有数据。

视图相当于一个窗口，我们可以透过它看到基本表（或另一个视图）中的数据。一旦基本表中的数据发生改变，从视图中查询到的数据也会发生变化。

 

1. 定义视图

（1）基于单个基本表定义视图

//建立信息系学生的视图：

CREATE VIEW IS_Student            /*省略了视图IS_Student的列名，隐含着视图的属性列由SELECT子句确定*/

AS

SELECT Sno,Sname,Sage

FROM Student

WHERE Sdept = 'IS'

WITH CHECK OPTION;

<分析>

1）关系数据库管理系统执行CREATE VIE语句的结果只是把视图的定义存入数据字典，只有在查询视图时才会执行代码中的SELECT语句。

2）加上WITH CHECK OPTION子句后，若对该视图进行插入、修改和删除操作，关系数据库管理系统会自动加上Sdept = 'IS'的条件。

 

（2）基于多个基本表定义视图

//建立信息系选修了1号课程的学生的视图。

CREATE VIEW IS_S1(Sno,Sname,Sage)         /*视图的属性列要么全部写上，要么全部省略，不然会和SELECT语句产生矛盾*/

AS                                                                        /*这里由于Student表和SC表出现同名属性列Sno，所以必须写上*/

SELECT Student.Sno,Sname,Sage

FROM Student,SC

WHERE Sdept = 'IS' AND

               Student.Sno = SC.Sno AND

               SC.Cno = '1';

 

（3）定义功能性视图

定义基本表时，为了减少数据库中的冗余数据，表中只存放基本数据，由基本数据派生的数据一般是不存储的。这时候视图就发挥它的作用了，我们虽然不存储，但是可以在显示数据的时候把派生数据计算出来鸭_(:з」∠)_

//将学生的学号及平均成绩定义为一个视图：

CREATE VIEW S_avg(Sno,Savg)

AS

SELECT Sno,AVG(Grade)            /*除了用聚集函数，这里还可以写算术表达式*/

FROM SC

GROUP BY Sno

 

2. 删除视图

删除视图后，视图的定义会从数据字典抹去，但如果视图导出了其他视图，系统将会拒绝执行删除操作。要成功删除视图，需要加上CASCADE关键字，连同其他被导出的视图一起删除。

基本表被删除后，由它导出的所有视图都会失效，但视图的定义不会自动被删除，我们还需要手动删除。

//删除视图S_avg：

DROP VIEW S_avg CASCADE;

 

3. 查询视图

查询视图时，关系数据库管理系统会先检查视图是否存在，若存在，则从数据字典中将视图的定义取出，进而找到最原始的基本表，再进行数据查询。这个过程称为视图消解。

查询视图与查询基本表的语法基本相同，本篇不再赘述。

 

*需要注意的是，并不是所有视图都能被查询出结果。例如上面的功能性视图S_avg，如果查询视图中平均成绩超过90分的学生学号，它在关系数据库管理系统中经过转化后会得到查询语句：

SELECT Sno

FROM SC

WHERE AVG(Grade) >= 90;

GROUP BY Sno;

然而这样的转化是错误的，因为WHERE子句中不能使用聚集函数（可回顾“聚集函数”）。所以视图S_avg中的平均成绩我们只能看，而不能通过SQL语句去查，想要查也只能在SC表上老实地写一个查询语句了。

 

目前只有行列子集视图能正确地转换。行列子集视图是指仅从单个基本表中导出，且所有属性列来自基本表的视图。

 

4. 更新视图

更新视图除了语法与查询视图不同，道理一样。

 

5. 视图的作用

1）简化用户的操作。

2）使用户能以多种角度看待同一数据。

3）对重构数据库提供了一定程度的逻辑独立性。

4）能够对机密数据提供安全保护。

5）用视图辅助基本表查询。

---

 

路过的圈毛君：“SQL分为数据定义、数据查询、数据更新和数据控制四个部分，到现在前三部分已经介绍完了，之后将会介绍数据控制。”