第3章 数据库关系数据库标准语言SQL (更新与视图)(2)

1、 数数 据据 更更 新新 1 插入数据插入数据2 修改数据修改数据3 删除数据删除数据 插入数据插入数据v两种插入数据方式两种插入数据方式1. 插入元组2. 插入子查询结果可以一次插入多个元组 一、插入元组一、插入元组v语句格式语句格式INSERTINTO (，)VALUES ( ， )v功能功能n将新元组插入指定表中插入元组（续）插入元组（续）v INTO子句子句n属性列的顺序可与表定义中的顺序不一致n没有指定属性列，则插入的元组在每列上都有值n指定部分属性列,没出现的列取空值v VALUES子句子句n 提供的值必须与INTO子句匹配值的个数值的类型字符串常数要用单引号（英文符号）括起来插入

2、元组（续）插入元组（续）例例1 将一个新学生元组（学号：将一个新学生元组（学号：201215128；姓名：陈；姓名：陈冬；性别：男；所在系：冬；性别：男；所在系：IS；年龄：；年龄：18岁）插入到岁）插入到Student表中。表中。 INSERT INTO Student (Sno，Sname，Ssex，Sdept，Sage) VALUES (201215128，陈冬陈冬，男男，IS，18)；插入元组（续）插入元组（续）例例2 将学生张成民的信息插入到将学生张成民的信息插入到Student表中。表中。 INSERT INTO Student VALUES (201215126，张成民张成民，男

3、男，18，CS); 只有表名，没有属性名，只有表名，没有属性名，所有列都要有值，所有列都要有值，并与表中属性顺序一致并与表中属性顺序一致插入元组（续）插入元组（续）例例3 插入一条选课记录插入一条选课记录( 200215128，1 )。 INSERT INTO SC(Sno，Cno) VALUES ( 200215128 ， 1 )； 没有指定值的没有指定值的Grade列将自动地赋空值列将自动地赋空值。 或者：或者： INSERT INTO SC VALUES ( 200215128 ， 1 ，NULL)；没有指定属性名，且某个列没有指定值，则要明确给出空值没有指定属性名，且某个列没有指定值，

4、则要明确给出空值二、插入子查询结果二、插入子查询结果v语句格式语句格式 INSERT INTO ( ， ) 子查询；子查询；v功能功能 将子查询结果插入指定表中将子查询结果插入指定表中nSELECT子句目标列必须与INTO子句匹配值的个数值的类型插入子查询结果（续）插入子查询结果（续）例例4 对每一个系，求学生的平均年龄，并把结果对每一个系，求学生的平均年龄，并把结果存入数据库。存入数据库。第一步：建表第一步：建表 CREATE TABLE Dept_age (Sdept CHAR(15) /* 系名系名*/ Avg_age SMALLINT)； /*学生平均年龄学生平均年龄*/ 插入子查询结

5、果（续）插入子查询结果（续）第二步：插入数据第二步：插入数据 INSERT INTO Dept_age(Sdept，Avg_age) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；插入子查询结果（续）插入子查询结果（续）RDBMS在执行插入语句时会检查所插元组是在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则否破坏表上已定义的完整性规则 实体完整性 参照完整性 用户定义的完整性NOT NULL约束UNIQUE约束值域约束3.5 数数 据据 更更 新新 1、 插入数据插入数据2 、 修改数据修改数据3 、 删除数据删除数据 修改

6、数据修改数据v语句格式语句格式 UPDATE SET =，= WHERE ；v功能功能n修改指定表中满足WHERE子句条件的元组修改数据（续）修改数据（续）nSET子句指定修改方式要修改的列修改后取值nWHERE子句指定要修改的元组缺省表示要修改表中的所有元组修改数据（续）修改数据（续）v三种修改方式三种修改方式1. 修改某一个元组的值2. 修改多个元组的值3. 带子查询的修改语句1. 修改某一个元组的值修改某一个元组的值例例5 将学生将学生201215121的年龄改为的年龄改为22岁岁 UPDATE Student SET Sage=22 WHERE Sno= 201215121 ； 2.

7、修改多个元组的值修改多个元组的值例例6 将所有学生的年龄增加将所有学生的年龄增加1岁岁 UPDATE Student SET Sage= Sage+1；3. 带子查询的修改语句带子查询的修改语句例例7 将计算机科学系全体学生的成绩置零。将计算机科学系全体学生的成绩置零。 UPDATE SC SET Grade=0 WHERE CS= (SELECT Sdept FROM Student WHERE Student.Sno = SC.Sno)；修改数据（续）修改数据（续）RDBMS在执行修改语句时会检查修改操作在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则是否破坏表上已定义的完整性

8、规则n实体完整性n主码不允许修改n用户定义的完整性 NOT NULL约束 UNIQUE约束 值域约束3.5 数数 据据 更更 新新 1 、插入数据、插入数据2、 修改数据修改数据3、 删除数据删除数据 删除数据删除数据v 语句格式语句格式 DELETE FROM WHERE ；v 功能功能n删除指定表中满足WHERE子句条件的元组v WHERE子句子句n指定要删除的元组n缺省表示要删除表中的全部元组，表的定义仍在字典中删除数据（续）删除数据（续）v三种删除方式三种删除方式1. 删除某一个元组的值2. 删除多个元组的值3. 带子查询的删除语句1. 删除某一个元组的值删除某一个元组的值例例8 删除

9、学号为删除学号为201215128的学生记录。的学生记录。 DELETE FROM Student WHERE Sno= 201215128 ；2. 删除多个元组的值删除多个元组的值例例9 删除所有的学生选课记录。删除所有的学生选课记录。 DELETE FROM SC；3. 带子查询的删除语句带子查询的删除语句例例10 删除计算机科学系所有学生的选课记录。删除计算机科学系所有学生的选课记录。 DELETE FROM SC WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept=CS)；空值的处理空值的处理v空值就是空值就是“不知道不知道”或或“不存在

10、不存在”或或“无意义无意义”的值。的值。v一般有以下几种情况：一般有以下几种情况： 该属性应该有一个值，但目前不知道它的具体值 该属性不应该有值 由于某种原因不便于填写 v空值是一个很特殊的值，含有不确定性。对关系空值是一个很特殊的值，含有不确定性。对关系运算带来特殊的问题，需要做特殊的处理。运算带来特殊的问题，需要做特殊的处理。空值的产生空值的产生 例例 向向SCSC表中插入一个元组，学号是表中插入一个元组，学号是201215126201215126，课程号是课程号是1 1，成绩为空。，成绩为空。INSERT INTO SC(Sno,Cno,Grade)INSERT INTO SC(Sno,

11、Cno,Grade)VALUES(201215126 ,1,NULL); /VALUES(201215126 ,1,NULL); /* *该学生还没有该学生还没有考试成绩，取空值考试成绩，取空值* */ /或或INSERT INTO SC(Sno,Cno)INSERT INTO SC(Sno,Cno)VALUES( 201215126 ,1); /VALUES( 201215126 ,1); /* *没有赋值的属性，没有赋值的属性，其值为空值其值为空值* */ /空值的产生空值的产生例例 将将Student表中学生号为表中学生号为”201215200”的学生的学生所属的系改为空值。所属的系改为

12、空值。UPDATE StudentSET Sdept = NULLWHERE Sno=201215200;空值的判断空值的判断v判断一个属性的值是否为空值，用判断一个属性的值是否为空值，用IS NULLIS NULL或或IS IS NOT NULLNOT NULL来表示。来表示。 例例 从从StudentStudent表中找出漏填了数据的学生信息表中找出漏填了数据的学生信息SELECT *FROM StudentWHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL; 空值的约束条件空值的约束条件v属性定义（或者

13、域定义）中属性定义（或者域定义）中 有NOT NULL约束条件的不能取空值 加了UNIQUE限制的属性不能取空值 码属性不能取空值 空值的算术运算、比较运算和逻辑运算空值的算术运算、比较运算和逻辑运算v空值与另一个值（包括另一个空值）的算术运算空值与另一个值（包括另一个空值）的算术运算的结果为空值的结果为空值v空值与另一个值（包括另一个空值）的比较运算空值与另一个值（包括另一个空值）的比较运算的结果为的结果为UNKNOWN。v有有UNKNOWN后，传统二值（后，传统二值（TRUE，FALSE）逻辑就扩展成了三值逻辑逻辑就扩展成了三值逻辑 空值的算术运算、比较运算和逻辑运算空值的算术运算、比较运

14、算和逻辑运算逻辑运算符真值表逻辑运算符真值表(T(T表示表示TRUETRUE，F F表示表示FALSEFALSE，U U表示表示UNKNOWN)UNKNOWN)x y x AND yx OR y NOT xT T T T FT U U T FT F F T FU T U T UU U U U UU F F U UF T F T TF U F U TF F F F T空值的算术运算、比较运算和逻辑运算空值的算术运算、比较运算和逻辑运算 例例 找出选修找出选修1 1号课程的不及格的学生。号课程的不及格的学生。SELECT SnoFROM SCWHERE Grade 60 AND Cno=1;查询结

15、果不包括缺考的学生，因为他们的查询结果不包括缺考的学生，因为他们的GradeGrade值值为为nullnull。 例例 选出选修选出选修1号课程的不及格的学生以及缺考的学生。号课程的不及格的学生以及缺考的学生。SELECT SnoFROM SCWHERE Grade 60 AND Cno=1UNIONSELECT SnoFROM SCWHERE Grade IS NULL AND Cno=1或者或者SELECT SnoFROM SCWHERE Cno=1 AND (Grade60 OR Grade IS NULL); 视图视图 定义视图 查询视图 更新视图 视图的作用数据库三级模式数据库三级模

16、式表表表、视图表、视图设计数据库设计数据库：将对象及对象的联系定义为单张表，力求全面将对象及对象的联系定义为单张表，力求全面使用数据库使用数据库：根据应用的需要，将表合成或分解根据应用的需要，将表合成或分解v视图的特点视图的特点 虚表，是从一个或几个基本表（或视图）导出的表 只存放视图的定义，不存放视图对应的数据 基表中的数据发生变化，从视图中查询出的数据也随之改变一、创建视图一、创建视图 CREATE VIEW (,.) AS WITH CHECK OPTION WITH CHECK OPTION表示对视图进行UPDATE、 INSERT和DELETE操作时要保证更新、插入或删除的行满足视图

17、定义中的谓词条件（即子查询中的条件表达式）。 子查询可以是任意的SELECT语句，是否可以含有ORDER BY子句和DISTINCT短语，则取决于具体系统的实现。定义视图定义视图 CREATE VIEW 必须是批查询中的第一条语句。必须是批查询中的第一条语句。v视图中所包含的列名是否需要定义？视图中所包含的列名是否需要定义？ 要么全部省略，要么全部指定。 以下以下三种情况须指定组成视图的所有列名： 某个目标列集函数或列表达式 多表连接时选出了几个同名列作为视图的字段 需要在视图中为某个列启用新的更合适的名字v 如何执行？如何执行？DBMS执行CREATE VIEW语句的结果只是把对视图的定义存

18、入数据字典，不执行其中的SELECT语句。在对视图查询时，才按视图的定义从基本表中将数据查出。 定义视图定义视图视图建立在单张表上：例例1 建立信息系学生的视图。建立信息系学生的视图。 create view is_studentas select sno,sname,sage from student where sdept=is例例2 建立信息系学生的视图，并要求进行修改和插入操作时仍建立信息系学生的视图，并要求进行修改和插入操作时仍须保证该视图只有信息系的学生须保证该视图只有信息系的学生 create view is_student1as select sno,sname,sage fr

19、om student where sdept=iswith check option 定义视图定义视图说明说明(针对针对SQL Server)：1)insert into is_student (sno,sname,sage)values (3001510101,王一,20)2)insert into is_student1 (sno,sname,sage)values (3001510101,王一,20)第2）句会产生错误？更改更改:在视图中加入在视图中加入is系系create view is_studentasselect sno,sname,sage,sdept from student

20、 where sdept=iswith check optioninsert into is_student1 (sno,sname,sage,sdept)values (3001510101,王一,20,is)定义视图定义视图 视图可以建立在多个基本表上 例例3 建立信息系选修了建立信息系选修了001号课程的学生的视图号课程的学生的视图 create view is_s1(sno,sname,grade)as select student.sno,sname,sc.grade from student,scwhere sdept=is and student.sno=sc.sno and o

21、=001 视图建立在视图上 例例4 建立信息系选修了建立信息系选修了1号课程且成绩在号课程且成绩在90分以上的学生分以上的学生的视图的视图 CREATE VIEW IS_S2 AS SELECT sno,sname ,grade FROM IS_S1 WHERE grade=90视图IS_S2就是建立在视图IS_S1之上的。 定义视图定义视图 带表达式的视图:例例5 定义一个反映学生出生年份的视图定义一个反映学生出生年份的视图 CREATE VIEW BT_S(sno,sname,Sbirth) as SELECT sno,sname,2016-sage FROM student此例中，定义视

22、图时必须明确定义该视图的各个属性列名。BT_S视图是一个带表达式的视图。 定义视图定义视图定义视图定义视图v分组视图:带有聚集函数和GROUP BY子句的查询 例6 将学生的学号及他的平均成绩定义为一个视图 ？CREATE VIEW S_G(Sno,Gavg)ASSELECT Sno,AVG(Grade)FROM SCGROUP BY Sno;例例7 将将Student表中所有女生记录定义为一个视图表中所有女生记录定义为一个视图 CREATE VIEW F_Student(sno,sname,ssex,sage,sdept) AS SELECT * FROM Student WHERE sse

23、x=女 思考思考：视图F_Student是由子查询“SELECT *”建立，视图一旦建立，Student表构成视图定义的一部分如果修改Student的结构，则Student表与F_Student视图的映像关系受到破坏，视图不能正确工作了。解决方法解决方法： 1)建立视图时明确指明属性列名，而不是用select *。即： CREATE VIEW F_Student(sno,sname,ssex,sage,sdept) AS SELECT sno,sname,ssex,sage,sdept FROM StudentWHERE ssex=女 若Student表增加新列，原视图仍能正常工作，只是新增的

24、列不在视图中而已。 2)最安全的方法：修改基本表之后删除原来的视图，然后重建视图。定义视图定义视图 二、二、 删除视图删除视图 DROP VIEW CASCADE一个视图被删除后，由此视图导出的其他视图也将失效，用户应该使用CASCADE级联删除语句将它们全部删除。 例例8 删除视图删除视图IS_S1 DROP VIEW BT_S; /成功DROP VIEW IS_S1 ; /失败，因为IS_S1还导出了IS_S2DROP VIEW IS_S1 CASCADE; /成功定义视图定义视图查询视图如同查询表：例9 在信息系学生视图中找出年龄小于20岁的学生 SELECT sno,sage FROM

25、 IS_Student WHERE sage20DBMS执行此查询时，将其与IS_Student视图定义中的子查询 :select sno,sname,sage from Student WHERE sdept=IS 结合起来，转换成对基本表Student的查询，修正后的查询语句为：SELECT Sno, Sage FROM Student WHERE Sdept=is AND sage20这一转换过程称为“视图消解”查询视图查询视图v 视图是定义在基本表上的视图是定义在基本表上的虚表虚表v 可以和其他基本表一起使用，可以和其他基本表一起使用，实现连接查实现连接查询或嵌套查询询或嵌套查询。例例

26、10 查询信息系选修了查询信息系选修了1号课程的学生号课程的学生 SELECT sc.sno,sname FROM IS_Student, scWHERE IS_Student.sno=sc.sno AND o=001 本查询涉及虚表本查询涉及虚表IS_Student和基本表和基本表sc，通，通过这两个表的连接来完成用户请求。过这两个表的连接来完成用户请求。 查询视图查询视图v更新视图包括（更新视图包括（INSERT）、）、DELETE）和修改）和修改（UPDATE）三类操作。）三类操作。v对视图的更新最终要对视图的更新最终要转换为对基本表的更新转换为对基本表的更新。v定义视图时加上定义视图时

27、加上WITH CHECK OPTION子句，子句，在视图上增删改数据时，在视图上增删改数据时，DBMS会进一步检查视会进一步检查视图定义中的条件图定义中的条件 例11 将信息系学生视图IS_Student中学号为2001010401的学生姓名改为“李四” UPDATE IS_Student SET sname=李四 WHERE sno=2001010401 更新视图更新视图例例12 向信息系学生视图向信息系学生视图IS_Student中插入一新的学生记中插入一新的学生记录，学号为录，学号为20122140101，姓名为赵新，年龄为，姓名为赵新，年龄为20岁岁 INSERT INTO IS_Student VALUES(20122140101 , 赵新赵新, 20)转换为对基本表的更新： INSERT INTO Student(sno,sname,sage,sdept)VALUES(22, 赵新, 20, null); 例例13 删除计算机系学生视图中学号为删除计算机系学生视图中学号为222的记录的记录 DELETE FROM IS_Student WHERE sno=222 转换为对基本表的更新： DELETE FRO