利用 SELECT 语句可以实现关系数据库的所有查询。

上一讲 利用SELECT语句可以实现关系数据库的所有查询。 SELECT select_list FROM TABLE或视图 WHERE条件 GROUP BY … HAVING … ORDER BY … 集函数： COUNT SUM AVG MAX MIN COUNT(*) 数值型

上一讲 自身连接连接查询左外连接外连接右外连接 IN 比较运算符嵌套查询 ANY/ALL EXISTS 集合查询

本讲内容 插入数据数据更新修改数据删除数据定义视图视图查询视图更新视图

3.5 数据更新 • 插入数据 (INSERT) • 修改数据 (UPDATE) • 删除数据 (DELETE)

一、插入数据 1.插入单个新记录 INSERT INTO <表名> [(<属性列1>[,<属性列2>...)] VALUES (<常量1> [,<常量2>]...) ① 属性列列表中包括表中所有列。例1 将一个学生记录插入Student表中，记录为：（学号：95020；姓名：陈冬；性别：男；系：IS；年龄：18）。 INSERT INTO Student VALUES (95020,‘陈冬’,18, '男','IS'); 此时不需要在表名后列出所有列名。

Student(Sno，Sname，Sex，Sage，Sdept) Course(Cno，Cname，Cpno，Credit) SC(Sno，Cno，Grade) ② 属性列列表中不包括表中所有列。例1 将一个学生记录插入Student表中，记录为：（学号：95009；姓名：陈波；所在系：IS；）。 INSERT INTO Student (Sno,Sname,Sdept)VALUES (95009,'陈波', 'IS'); 如果某些属性列在INTO子句中没有出现，则新记录在这些列上将取空值（NULL）。在表定义时说明了NOT NULL的属性列不能取空值，这些列队值如果没有在插入语句中给出来，则会出错。然而，如果定义表时为该列指定了默认值，则新列中未指定值的该列就取得默认值。

Student(Sno，Sname，Sex，Sage，Sdept) Course(Cno，Cname，Cpno，Credit) SC(Sno，Cno，Grade) 例2 插入一条选课记录，其学号为950010，课号为9 。 INSERT INTO SC (Sno,Cno) VALUES (950010,‘9'); 如果Grade属性不允许空，则该语句出错；如果Grade属性允许空，则执行语句后，Grade值为NULL；如果Grade属性指定了默认值0，则执行语句后，Grade值为0。

2.插入子查询结果 INSERT INTO <表名> [(<属性列1>[,<属性列2>...)] 子查询；功能：一次将子查询的结果全部插入指定表中，完成批量插入。例3 对每一个系，求学生的平均年龄，并把结果存入数据库。 ① 建立一个新表，用于存放各系学生的平均年龄。 CREATE TABLE Dept_age (Sdept CHAR(6) ,Avg_age SMALLINT); ② 求各系学生的平均年龄。 SELECT Sdept,AVG(Sage) AS Avg_age FROM StudentGROUP BY Sdept ③ 把各系平均年龄存入数据库中。 INSERT INTO Dept_age(Sdept,Avg_age) (SELECT Sdept,AVG(Sage) AS Avg_age FROM Student GROUP BY Sdept)

二、修改数据 （更新数据） UPDATE <表名> SET <列名>=<表达式>[,<列名>=<表达式>]... [WHERE <条件>]; 功能：修改指定表中满足WHERE子句条件的记录。 SET子句：指定修改方法，即用<表达式>的值取代相应的属性列值。指定即将被修改的记录应满足的条件。 WHERE子句：如果省略WHERE子句，则表示要修改表中的所有记录。 1.修改所有记录的值。例4 将所有学生的考试成绩加上6分。 UPDATE SC SET Grade=6+Grade;

Student(Sno，Sname，Sex，Sage，Sdept) Course(Cno，Cname，Cpno，Credit) SC(Sno，Cno，Grade) 2.修改一个记录的值。例4 数学系学生欧阳庆转到信息系，修改其记录。 UPDATE Student SET Sdept='IS' WHERE Sdept='MS‘ AND Sname='欧阳庆' 3.修改多个记录的值。例5 在身份证数据表 Indentity(no,name,sex,age,home)中，在四川籍女同志身份证号码的第6位数字后加上’19’，并在最后一位后面加上’7’。 UPDATE Indentity SET no=left(no,6)+’19’+right(no,9)+’7’ WHERE sex=‘女’ AND home like’四川%’；

4.带有子查询的修改语句 例6 将计算机科学系全体学生的成绩置零。 UPDATE SC SET grade = 0 WHERE‘CS’ = (SELECTsdeptFROM student where student.sno = sc.sno)

Student(Sno，Sname，Sex，Sage，Sdept) Course(Cno，Cname，Cpno，Credit) SC(Sno，Cno，Grade) 5. 修改操作与数据库的一致性修改操作可能会导致数据库的不一致。例如，把课程表中课号为1的课程的课号改为x1: UPDATE Course SET Cno='x1' WHERE Cno='1'; 为保证数据库的一致性，同时还应修改数据库中课号为1的所有记录中的课号 UPDATE SC SET Cno= 'x1' WHERE Cno='1'; 为了防止这种不一致的发生，数据库系统采取的方法： • 设置外关键字约束 • 设置事务

三、删除数据 DELETE FROM <表名> [WHERE <条件>]; 功能：删除指定表中满足WHERE子句条件的所有记录。 1. 删除满足条件的记录例7 删除2号课成绩低于30分的学生的选课。 DELETEFROM SC WHERE Grade <30 and Cno='2' 2. 删除表中所有记录 DELETE FROM SC 注意：只是删除表中的数据，没有删除表定义，表仍然存在。彻底删除一个表的方法： DROP TABLE SC

Student(Sno，Sname，Sex，Sage，Sdept) Course(Cno，Cname，Cpno，Credit) SC(Sno，Cno，Grade) 3. 带子查询的删除语句把子查询嵌套在DELETE语句中，作为执行删除操作的条件。例8 删除操作系统课程所有学生的选课记录。 DELETEFROM SC WHERE Cno= (SELECT Cno FROM Course WHERE Cname=‘OS')；

3.5 视图 用户1 视图1 用户2 视图2 用户m 视图m 视图是从基表（或视图）中导出的表，对应于三级模式结构中的外模式，是用户观察数据库的窗口。

视图视图是虚表，数据库中只存储视图的定义，不存储视图对应的表。需要视图表时，系统从基表中即时选择数据来产生。定义视图后，用户可以通过视图来操作数据库，执行查询、插入、删除和修改操作，其操作的结果直接作用于直接或间接导出视图的基表。查询、插入查询、插入基表视图删除和修改删除和修改但是，有些视图只能执行查询操作。

一、定义视图 CREATE VIEW <视图名>[(<列名>[,<列名>]...)] AS <子查询> [ WITH CHECK OPTION] 格式：子查询中不能包含ORDER BY子句和DISTINCT短语。但是，在MS SQL SERVER中，可以使用DISTINCT短语。对于ORDER BY子句，不可以随意使用。要使用ORDER BY子句，则需要在视图中指定包含的最大记录数目。用于对视图的更新进行约束。 [WITH CHECK OPTION]：

Student(Sno，Sname，Ssex，Sage，Sdept) Course(Cno，Cname，Cpno，Credit) SC(Sno，Cno，Grade) 如果CREATE VIEW仅指定了视图名，没有指定各个属性列名，则视图中的各个列名由SELECT子句目标列中的诸字段组成。例1 建立学生视图，用于查看学生的学号，姓名和所在的系。 CREATE VIEW V_Student AS SELECT Sno,Sname,Sdept FROM Student 例2 建立课程设置视图，用于表示课程名称和课号。 CREATE VIEW V_Course(课程名称,课号) AS SELECT Cname,Cno FROM Course 行列子集视图——由单个基表导出的视图，视图中保留基表主码，去掉了基表中的某些行和列。

Student(Sno，Sname，Sex，Sage，Sdept) Course(Cno，Cname，Cpno，Credit) SC(Sno，Cno，Grade) 如果某个目标列不是单纯的属性名，而是集函数或列表达式，则必须明确指定组成视图的所有列名。例3 建立系平均年龄视图，用于显示各系的平均年龄。 CREATE VIEW V_age(系,平均年龄) AS SELECT Sdept, AVG(Sage) FROM Student GROUP BY Sdept CREATE VIEW V_age AS SELECT Sdept AS 系, AVG(Sage) AS 平均年龄 FROM Student GROUP BY Sdept 平均年龄为基表中不存在的列，称为虚拟列。带虚拟列的视图称为带有表达式的视图。

Student(Sno，Sname，Sex，Sage，Sdept) Course(Cno，Cname，Cpno，Credit) SC(Sno，Cno，Grade) 视图可以建立在多个基表上。例4 建立学生的学号(Sno)、姓名(Sname)、选修课程名(Cname)及成绩(Grade)的视图。 CREATE VIEW V_Student_CR AS SELECT Student.Sno, Sname, Cname, Grade FROM Student, SC, Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno

视图可以建立在视图上。 例5 建立学生的操作系统选课视图，用于显示选修了操作系统课程的学生的姓名、学号和成绩。 CREATE VIEW V_os AS SELECT Sname, Cname,Grade FROM V_Student_CR WHERE Cname='OS' 若在子查询中使用SELECT *，则当基表中的列顺序发生改变时，视图中对应的列没有意义，视图将无法使用。解决方法： ① 建立视图时明确指明属性列名，而不是简单地用SELECT *。 ② 在修改基本表之后删除原来的视图，然后重建视图。

二、删除视图 格式： DROP VIEW <视图名>; 导出视图的基表或视图被删除后，该视图定义仍然存在，但是已经毫无意义，应该利用删除语句进行删除。例6 删除视图V_course 。 DROPVIEW V_course;

三、查询视图 查询视图的方法与查询基表的方法相同。 DBMS执行视图查询的步骤： ① 检查视图涉及的基表和视图是否存在，如果存在，则执行下一步操作；如果不存在，则停止查询。 ② 从数据字典中取出视图定义时使用的查询定义。 ③ 把从数据字典中取出的查询语句，与用户定义的查询视图语句结合起来，转换成对基表的查询。 ④ 执行上一步中得到的联合查询。把视图查询转换为对基表查询的过程称为对视图的消解。

查询视图 例8 建立学生视图V_student查看学生的学号、姓名和系，并利用该视图，查询信息系选修了1号课程的学生学号和姓名。 ① 建立学生视图： CREATE VIEW V_student AS SELECT Sno,Sname,Sdept FROM Student ② 查询选修了1号课程的学生学号和姓名: SELECT SC.Sno, Sname FROM V_student, SC WHERE V_student.Sno=SC.Sno AND SC.Cno='1' AND Sdept='IS' 实际的查询语句: SELECT SC.Sno,Sname FROM Student,SC WHERE Student.Sno=SC.Sno and SC.Cno='1' AND Sdept='IS'

四、更新视图 通过视图来插入、删除和修改基表中的数据的操作。对视图执行的所有操作，最终都是在对基表进行操作。为防止用户通过视图对数据进行增删改时，无意或故意操作不属于视图范围内的基本表数据，可在定义视图时加上WITH CHECK OPTION子句，这样在视图上增删改数据时，DBMS会进一步检查视图定义中的条件，若不满足条件，则拒绝执行该操作。提交修改后，仍然可以通过视图看到修改后的数据。

更新视图 1.插入视图条件：基表中不允许空值的列都出现在视图中。例9 建立信息系学生视图V_IS，用于显示学生学号，姓名，所在的系;并向视图V_IS中插入一个新的学生记录，其中学号为95029，姓名为赵新，系为信息系IS。 ① 建立视图V_IS: CREATE VIEW V_IS AS SELECT Sno,Sname,Sdept FROM Student WHERE Sdept='IS' ② 插入记录： INSERT INTO V_IS VALUES(95029, '赵新','IS');

更新视图 例9 向信息系学生视图V_IS中插入一个新的学生记录，其中学号为95029，姓名为张林，系为数学系MS。 INSERT INTO V_IS VALUES(95059, '张林','MS'); 插入记录（95059，张林，NULL，NULL，MS）该记录不会出现在信息系学生视图V_IS中。

更新视图 WITH CHECK OPTION的使用： CREATE VIEW V_IS AS SELECT Sno,Sname,Sdept FROM Student WHERE Sdept=‘IS’ WITH CHECK OPTION; 目的：保证不会通过视图向基表中插入非信息系的学生记录。例如： INSERT INTO V_IS VALUES(95099, '张林', ‘MS’); 执行语句时就出现错误，无法插入。

更新视图 2. 修改视图例10 将学生视图V_Student中学号为95002的学生姓名改为“刘辰”。 UPDATE V_Student SET Sname='刘辰' WHERE Sno='95002'; 注意： ① 一般对所有源于一个基表的行列子集视图可以执行插入和删除操作。 ② 如果基表中所有不允许空值的列都出现在行列子集视图中，则可以执行插入记录的操作。 ③ 对于其他一些出现了集函数，或来源于多个基表的视图，是否可以执行更新操作，要视具体的数据库系统而定。

五、视图的用途 用户1 视图1 用户2 视图2 用户m 视图m • 简化用户的操作 • 使用户能以多种角度看待同一数据 • 在一定程度上保证数据库的逻辑独立性 • 在一定程度上保证数据库的安全性

视图的用途 • 简化用户的操作基表1 用户1 数据1 视图1 基表2 视图2 用户2 数据2 基表3 用户m 数据m 视图m 基表m 基表n

视图的用途 • 使用户从多种角度看待同一数据 Student 学生数据1 视图1 Course 视图2 教务员数据2 S C 教师数据m 视图m Teacher

视图的用途 • 对重构数据库提供了一定程度的逻辑独立性 Student Student 学生学生数据1 数据1 视图1 视图1 Course 教务员教务员数据2 数据2 视图2 视图2 S C 教师数据m 视图m Teacher 其他

视图的用途 数据库重构中最常见的是将一个基表垂直分解为多个基本表。例:将学生关系 Student（sno,sname,ssex,sage,sdept）分为SX(sno, sname, sage) 和SY(sno, ssex,sdept)。而原表Student为SX和SY自然连接的结果。如果建立一个视图Student，可以保持应用程序的逻辑独立性： Create VIEW student(sno, sname, ssex, sage, sdept) AS select SX.sno,SX.sname,SY.ssex,SX,sage,SY.sdept FROM SX, SY WHERE SX.sno = SY.sno

视图的用途 • 对数据库提供了一定程度的安全性 ① 通过视图把保密数据对无权存取这些数据的用户隐藏起来。 ② 对不同的视图，赋予不同的权限。对视图权限的设置与对基表权限的设置互相独立。

作业第三章课后作业全做。

利用 SELECT 语句可以实现关系数据库的所有查询。