第3章关系数据库标准语言SQL(4).ppt

1、1/24数据库原理及应用数据库原理及应用Principle and Application of Database第三章第三章 关系数据库标准语言关系数据库标准语言SQL(SQL(续续)2/24学习目标学习目标掌握掌握SQL的数据更新的数据更新理解视图的概念和作用理解视图的概念和作用掌握视图的定义、查询和更新掌握视图的定义、查询和更新3/243.5 数据更新数据更新 插入数据：插入数据：有插入一个元组和插入子查询结果两种方式。有插入一个元组和插入子查询结果两种方式。插入一个元组插入一个元组 INSERT INTO (,)VALUES(,)其功能是将新元组插入指定表中。其中新元组的属性列其功能是

2、将新元组插入指定表中。其中新元组的属性列1的值为的值为常量常量1，属性列，属性列2的值为常量的值为常量2，。INTO子句没有出现的属性子句没有出现的属性列，新元组在这些列上取空值。但必须注意的是，在表定义时列，新元组在这些列上取空值。但必须注意的是，在表定义时说明了说明了NOT NULL的属性列不能取空值，否则会出错。若的属性列不能取空值，否则会出错。若INTO子句没有指明任何属性列名，则新插入的元组必须在每个属性子句没有指明任何属性列名，则新插入的元组必须在每个属性列上均有值。列上均有值。例例1将一个新元组将一个新元组(200215128,陈冬陈冬,男男,18,IS)插入到插入到Studen

3、t表中。表中。INSERT INTO Student VALUES(200215128,陈冬陈冬,男男,18,IS);4/24 例例2将学生张成民的信息插入到将学生张成民的信息插入到Student表中。表中。INSERT INTO Student VALUES(200216126,张成民张成民,男男,18,CS);本例本例INTO子句中只指出表名，没有指出属性名，这表示新元组子句中只指出表名，没有指出属性名，这表示新元组要在表的所有属性列上都指定值，属性列的次序与要在表的所有属性列上都指定值，属性列的次序与CREATE TABLE中的次序相同。中的次序相同。例例3插入一条选课记录插入一条选课记

4、录(200215128,1)。INSERT INTO SC(Sno,Cno)VALUES(200215128,1);新插入的元组在新插入的元组在Grade列上取空值。列上取空值。本例也可以表示为：本例也可以表示为：INSERT INTO SC VALUES(200215128,1,NULL);5/24插入子查询结果：插入子查询结果：INSERT INTO (,)子查询子查询;例例4对每一个系，求学生的平均年龄，并把结果存入数据库。对每一个系，求学生的平均年龄，并把结果存入数据库。首先在数据库中建立一个新表，其中一列存放系名，另一列存首先在数据库中建立一个新表，其中一列存放系名，另一列存入相应的

5、学生平均年龄。入相应的学生平均年龄。CREATE TABLE Dept_age (Sdept CHAR(20),Avg_age INT);然后对然后对Student表按系分组求平均年龄，再把系名和平均年龄表按系分组求平均年龄，再把系名和平均年龄存入新表中。存入新表中。INSERT INTO Dept_age(Sdept,Avg_age)SELECT Sdept,AVG(Sage)FROM Student GROUP BY Sdept;6/24修改数据：修改数据：有修改一个或多个元组、带子查询修改三种方式。有修改一个或多个元组、带子查询修改三种方式。UPDATE SET=,=WHERE;功能是修

6、改指定表中满足功能是修改指定表中满足WHERE子句条件的元组。其中子句条件的元组。其中SET子句给出子句给出的值用于取代相应的属性列值。如果省略了的值用于取代相应的属性列值。如果省略了WHERE子句，则表示要修改表中的所有元组。子句，则表示要修改表中的所有元组。例例5将学生将学生200215121的年龄改为的年龄改为22岁。岁。UPDATE Student SET Sage=22 WHERE Sno=200215121;例例6将所有学生的年龄增加将所有学生的年龄增加1岁。岁。UPDATE Student SET Sage=Sage+1;例例7将计算机科学系全体学生的成绩置零。将计算机科学系全体

7、学生的成绩置零。UPDATE SC SET Grade=0 WHERE CS=(SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno);7/24删删除除数数据据：有有删删除除一一个个或或多多个个元元组组、带带子子查查询询删删除除三三种种方方式式。DELETE FROM WHERE;其其功功能能是是从从指指定定表表中中删删除除满满足足WHERE子子句句条条件件的的所所有有元元组组。如如果果省省略略WHERE子子句句，表表示示删删除除表表中中全全部部元元组组，但但表表的的定定义义仍仍在在数数据据字字典典中中。即即DELETE语语句句删删除除的的是是表

8、表中中的的数数据据，而而不不是是关关于于表表的定义。的定义。例例8删除学号为删除学号为200215128的学生记录。的学生记录。DELETE FROM Student WHERE Sno=200215128;例例9删除所有的学生选课记录。删除所有的学生选课记录。DELETE FROM SC;例例10删除计算机科学系所有学生的选课记录。删除计算机科学系所有学生的选课记录。DELETE FROM SC WHERE CS=(SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno);8/243.6 视图视图视图的概念：视图的概念：是从一个或几个基本表是从一

9、个或几个基本表(或视图或视图)导出的表。它与导出的表。它与基本表不同，是一个虚表。基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍被存放在原来的基本表中。所以基本表中的数据发生变数据仍被存放在原来的基本表中。所以基本表中的数据发生变化，从视图中查询出的数据也随之改变了。即视图就像一个窗化，从视图中查询出的数据也随之改变了。即视图就像一个窗口，用户透过它可以看到自己感兴趣的数据库数据及其变化。口，用户透过它可以看到自己感兴趣的数据库数据及其变化。视图一经定义，就可以和基本表一样被查询、被删除。也可以视图一经

10、定义，就可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的视图，但对视图的更新在一个视图之上再定义新的视图，但对视图的更新(增删改增删改)操操作则有一定的限制。作则有一定的限制。9/24定义视图定义视图建立视图：建立视图：CREATE VIEW (,)AS WITH CHECK OPTION;其中，子查询可以是任意的其中，子查询可以是任意的SELECT语句，但通常不允许含有语句，但通常不允许含有ORDER BY子句和子句和DISTINCT短语。短语。WITH CHECK OPTION表示对视图进行更新操作时要保证增删改的行满足视图定义中表示对视图进行更新操作时要保证增删改的行满足视

11、图定义中的谓词条件的谓词条件(即子查询中的条件表达式即子查询中的条件表达式)。10/24 例例1建立信息系学生的视图。建立信息系学生的视图。CREATE VIEW IS_Student AS SELECT Sno,Sname,Sage FROM Student WHERE Sdept=IS本例省略了视图本例省略了视图IS_Student的列名，表示由子查询中的列名，表示由子查询中SELECT子句中的三个列名组成。子句中的三个列名组成。例例2建立信息系学生的视图，并要求进行修改和插入操作时建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。仍需保证该视图只有信息系的学

12、生。CREATE VIEW IS_Student AS SELECT Sno,Sname,Sage FROM Student WHERE Sdept=IS WITH CHECK OPTION;本例加上了本例加上了WITH CHECK OPTION子句，表示以后对该视图子句，表示以后对该视图进行增删改操作时，系统会自动加上进行增删改操作时，系统会自动加上Sdept=IS的条件。的条件。若一个视图是从单个基本表导出，并且只是去掉了基本表的某若一个视图是从单个基本表导出，并且只是去掉了基本表的某些行和某些列，但保留了主码，称这类视图为行列子集视图。些行和某些列，但保留了主码，称这类视图为行列子集视图

13、。11/24 例例3建立信息系选修了建立信息系选修了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;视图可以建立在一个或多个基本表、视图或基本表与视图上。视图可以建立在一个或多个基本表、视图或基本表与视图上。例例4建立信息系选修建立信息系选修1号课程且成绩在号课程且成绩在90分以上的学生视图。分以上的学生视图。CREATE VIEW IS

14、_S2 AS SELECT Sno,Sname,Grade FROM IS_S1 WHERE Grade=90;例例5定义一个反映学生出生年份的视图。定义一个反映学生出生年份的视图。CREATE VIEW BT_S(Sno,Sname,Sbirth)AS SELECT Sno,Sname,2000-Sage FROM Student;定义视图时可以设置一些派生属性列定义视图时可以设置一些派生属性列(也称虚拟列也称虚拟列)，如本例中，如本例中的的Sbirth，这样的视图称为带表达式的视图。，这样的视图称为带表达式的视图。12/24 例例6将学生的学号及其平均成绩定义为一个视图。将学生的学号及其平

15、均成绩定义为一个视图。CREATE VIEW S_G(Sno,Gavg)AS SELECT Sno,AVG(Grade)FROM SC GROUP BY Sno;带有聚集函数和带有聚集函数和GROUP BY子句查询的视图称为分组视图。子句查询的视图称为分组视图。例例7将将Student表中所有女生记录定义为一个视图。表中所有女生记录定义为一个视图。CREATE VIEW F_Student(F_Sno,name,sex,age,dept)AS SELECT*FROM Student WHERE Ssex=女女;本例视图本例视图F_Student由子查询由子查询SELECT*建立。建立。F_St

16、udent视图视图的属性列与的属性列与Student表一一对应，若修改了基本表表一一对应，若修改了基本表Student的结的结构后，构后，Student表与表与F_Student视图的映象关系就被破坏，该视图的映象关系就被破坏，该视图将不能正确地工作。为避免出现这类问题，最好在修改基视图将不能正确地工作。为避免出现这类问题，最好在修改基本表之后删除由该基本表导出的视图，然后重建这个视图。本表之后删除由该基本表导出的视图，然后重建这个视图。13/24删除视图：删除视图：DROP VIEW CASCADE;视视图图删删除除后后视视图图将将从从数数据据字字典典中中删删除除。如如果果该该视视图图上上还还导导出出了了其其他他视视图图，则则使使用用CASCADE子子句句，将将该该视视图图和和由由它它导导出出的的所所有有视视图图一一起起删删除除。基基本本表表删删除除后后，由由该该基基本本表表导导出出的的所所有有视视图图定定义义没没有有被被删删除除，但但均均已已无无法法使使用用了了，删删除除这这些些视视图图定定义义需需要要显示地使用显示地使用DROP VIEW语句。语句。例例8删除视图删除视图BT_S