第03章-关系数据库标准语言SQL(3)

数据库系统概论AnIntroductiontoDatabaseSystem第三章关系数据库标准语言SQL（3）第三章

关系数据库标准语言SQL3.1SQL概述3.2学生-课程数据库3.3数据定义3.4数据查询3.5数据更新3.6空值的处理3.7视图3.8小结3.5数据更新3.5.1插入数据3.5.2修改数据3.5.3删除数据3.5.1插入数据SQL中使用INSERT语句向表中插入新元组：提供两种插入数据方式插入元组一次只插入一个元组插入子查询结果可以一次插入多个元组一、插入元组插入元组的语句

INSERTINTO<表名>[(<属性列1>[，<属性列2>…)]

VALUES(<常量1>[，<常量2>]…)将一个新元组插入指定表中。INTO子句用于指定表名称及属性列<表名>：指定要插入的表<属性列1>、<属性列2>、…：指定接受数据的属性列VALUES子句用于提供数据<常量1>、<常量2>、…：为属性列提供的值插入元组（续）若INTO子句中有属性列表属性列的顺序可与表定义中的顺序不一致VALUES子句提供的值应与INTO子句中的属性列相匹配值的个数与属性的个数相同顺序与类型一致若INTO子句中无属性列表VALUES子句提供的值应与表定义中定义的属性列相匹配插入元组（续）[例3.69]将一个新学生元组（201215128，陈冬，男，IS，18岁）插入到Student表中。

INSERTINTOStudent(Sno，Sname，Ssex，Sdept，Sage)VALUES('201215128'，'陈冬'，'男'，'IS'，18)；INTO子句中属性列个数与顺序可任意VALUES字句应与之对应字符数据要用单引号括起来插入元组（续）[例3.70]将学生张成民的信息插入到Student表中。

INSERTINTOStudent VALUES(‘201215126’，‘张成民’，‘男’，18，'CS');INTO子句中没有指出列名称一般应为表中全部列提供值顺序、个数、类型与CREATETABLEtudent中的顺序一致插入元组（续）对于未提供值的列，RDBMS将填入默认值或设为空值［例3.71］插入一条选课记录('201215128'，'1')。

INSERTINTOSC(Sno，Cno)VALUES(‘201215128’，‘1’)；RDBMS将在新插入记录的Grade列上自动地赋空值。如果定义了默认值，则设置为默认值或者：

INSERTINTOSCVALUES(‘201215128’，‘1’，NULL)；VALUES子句中用NULL代表空值如果列定义中有约束NOTNULL，必须为其提供非空值二、插入子查询结果INSERT语句中可以使用子查询，将子查询结果插入指定表中：格式：INSERTINTO<表名>[(<属性列1>[，<属性列2>…)]

子查询；子查询用于生成要插入的批量数据。SELECT子句目标列必须与INTO子句匹配插入子查询结果（续）[例3.72]对每一个系，求学生的平均年龄，并把结果存入数据库。建立保存结果的表：

CREATETABLEDept_age(SdeptCHAR(15) /*系名*/Avg_ageSMALLINT)； /*学生平均年龄*/

执行插入：

INSERTINTODept_age(Sdept，Avg_age)

SELECT

Sdept，AVG(Sage)

FROMStudentGROUPBYSdept；

插入子查询结果（续）RDBMS在执行插入、修改或删除元组时会检查元组是否破坏表上已定义的完整性规则实体完整性参照完整性用户定义的完整性NOTNULL约束UNIQUE约束值域约束数据发生变化后，同时需要维护索引3.5数据更新3.5.1插入数据3.5.2修改数据3.5.3删除数据3.4.2修改数据SQL中使用UPDATE语句修改表中的元组

UPDATE<表名>SET

<列名>=<表达式>[，<列名>=<表达式>]…[WHERE<条件>]；<表名>指定要修改的元组所在的表<列名>指定要修改的列<表达式>提供给列的新值WHERE子句指定要修改的元组，省略时为全部元组修改数据（续）修改某一个（些）元组的值：[例3.73]将学生201215121的年龄改为22岁

UPDATEStudentSETSage=22

WHERESno=‘201215121’；

根据表中的数据，可能修改0、1或多个元组中的数据修改数据（续）修改全部元组的值

[例3.74]将所有学生的年龄增加1岁

UPDATEStudentSETSage=Sage+1；使用过程中一定要注意WHERE子句的条件省略：将修改全部元组若不恰当不能正确的定位元组修改数据（续）带子查询的修改语句[例3.75]将计算机科学系全体学生的成绩置零。

UPDATESCSETGrade=0WHERESnoin(SELETESnoFROMStudentWHERESdept=‘SC’)；或：UPDATESCSETGrade=0WHERE'CS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；此子查询为相关子查询UPDATE中的子查询用于构造修改的条件表中的每一行将依据子查询确定是否修改3.5数据更新3.5.1插入数据3.5.2修改数据3.5.3删除数据3.5.3删除数据SQL中使用DELETE语句从表中删除元组

DELETEFROM<表名>[WHERE<条件>]；<表名>指定要操作的表WHERE子句指定要删除的元组，省略将删除表中的全部元组删除数据（续）删除某一个（些）元组

[例3.76]删除学号为201215128的学生记录。

DELETEFROMStudentWHERESno=‘201215128‘；根据表中的数据，可能删除0、1或多个元组删除数据（续）删除所有的元组

[例3.77]删除所有的学生选课记录。

DELETEFROMSC；使用过程中一定要注意WHERE子句的条件省略：将删除全部元组若不恰当将删除有用的元组删除任何数据前应首先确认！3.带子查询的删除语句带子查询的删除语句

[例3.78]删除计算机科学系所有学生的选课记录。DELETEFROMSCWHERESnoin(SELETESnoFROMStudentWHERESdept=‘SC’)；或：DELETEFROMSCWHERE'CS‘=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；DELETE中的子查询用于构造删除的条件表中的每一行将依据子查询确定是否删除第三章

关系数据库标准语言SQL3.1SQL概述3.2学生-课程数据库3.3数据定义3.4数据查询3.5数据更新3.6空值的处理3.7视图3.8小结3.6空值空值是指“不知道”、“不存在”或“无意义”的值，一般有以下几种情况：该属性应该有一个值，但目前不知道该属性不应该有值由于某种原因不便于填写，或不关心该属性值。空值具有不确定性，因此，运算时需要注意第三章

关系数据库标准语言SQL3.1SQL概述3.2学生-课程数据库3.3数据定义3.4数据查询3.5数据更新3.6空值的处理3.7视图3.8小结3.7视图视图是从一个或几个基本表（或视图）导出的表它是一个虚表数据库中只存放视图的定义视图对应的数据存放在它的基表中视图只是基表的一个透视窗口基表中的数据发生变化，从视图中查询出的数据也随之改变3.7视图视图是用户查看数据库中数据的一种方式，视图以另一种方式表示一个或多个表中的数据。605040302010603010基表视图3.7视图3.7.1定义视图3.7.2查询视图3.7.3更新视图3.7.4视图的作用一、建立视图建立视图：

CREATEVIEW<视图名>[(<列名>[，<列名>]…)]

AS

<子查询>[WITHCHECKOPTION]；<视图名>为新建视图的名称<列名>指定视图的列名称，可省略子查询可以是任意复杂的SELECT语句，但不允许含有ORDERBY子句和DISTINCT短语建立视图（续）[例3.84]建立信息系学生的视图。

CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept=‘IS’；查询语句定义了视图视图的结构由Sno，Sname，Sage视图的数据为Student中Sdept=‘IS’的元组没有指定视图的列名时，视图的结构与查询语句的结果集的结构相同建立视图（续）有时需要指定视图的列名：SELECT中的目标列不是属性名，而是表达式时多表连接的结果集中有几个同名列时需要使用一个更合适的名称实现模式与子模式之间的映射例：CREATEVIEWIS_Student(学号，姓名，年龄)ASSELECTSno，Sname，SageFROMStudentWHERESdept=‘IS’；注意视图的列数应与查询结果集的结构一致建立视图（续）WITHCHECKOPTION规定对视图所执行的所有数据修改操作必须遵守视图定义中SELECT语句所设置的条件。

[例3.85]建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。

CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudent

WHERESdept='IS'

WITHCHECKOPTION；建立视图（续）CREATEVIEW语句用于定义视图该语句执行时首先检查语法是否正确引用的对象是否存在然后把视图的定义存入数据字典并不执行其中的SELECT语句只有对视图操作时，才按视图的定义对表中数据进行操作建立视图（续）视图建立在单个基表上，且保留主码，称为行列子集视图视图可建立在多个基表上

[例3.86]建立信息系选修了1号课程的学生视图。

CREATEVIEWIS_S1(学号，姓名，成绩)ASSELECTStudent.Sno，Sname，GradeFROMStudent，SCWHERESdept='IS'ANDStudent.Sno=SC.SnoANDSC.Cno='1'；建立视图（续）视图可以建立在视图上[例3.87]建立信息系选修了1号课程且成绩在90分以上的学生的视图。

CREATEVIEWIS_S2ASSELECTSno，Sname，GradeFROMIS_S1WHEREGrade>=90；建立视图（续）带表达式的视图（也称带虚拟列的视图）[例3.88]定义一个反映学生出生年份的视图。

CREATEVIEWBT_S(Sno，Sname，Sbirth)ASSELECTSno，Sname，2014-SageFROMStudent；查询中使用了表达式，须指定列名称建立视图（续）分组视图：视图由带有聚集函数和GROUPBY子句的查询来定义

[例3.89]将学生的学号及他的平均成绩定义为一个视图

CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSC

GROUPBYSno；

建立视图（续）[例3.90]将Student表中所有女生记录定义为一个视图

CREATEVIEWF_Student(F_Sno，name，sex，age，dept)ASSELECT*

FROMStudentWHERESsex=‘女’；查询语句中没有指定属性列名称缺点：修改基表Student的结构后，Student表与F_Student视图的映象关系被破坏，导致该视图不能正确工作。二、删除视图语句的格式：

DROPVIEW<视图名>[CASCADE]；该语句从数据字典中删除指定的视图定义如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除删除基表时，由该基表导出的所有视图定义都必须显式地使用DROPVIEW语句删除

删除视图(续）[例3.91]删除视图BT_S：

DROPVIEWBT_S；将从数据库中将视图的定义去除删除视图IS_S1：

DROPVIEWIS_S1；由于由视图依赖它，拒绝执行使用级联删除：

DROPVIEWIS_S1CASCADE;将连同由它导出的视图一起删除

3.7视图3.7.1定义视图3.7.2查询视图3.7.3更新视图3.7.4视图的作用3.7.2查询视图视图一经定义后，就可以像表一样被查询、修改、删除和更新。

[例3.92]在信息系学生的视图中找出年龄小于20岁的学生。

SELECTSno，SageFROMIS_StudentWHERESage<20；3.7.2查询视图RDBMS实现视图查询的方法首先进行有效性检查然后转换成等价的对基本表的查询执行修正后的查询，此过程称为视图消解如上例中的查询语句可能转换后为：

SELECTSno，SageFROMStudentWHERESdept=‘IS’ANDSage<20；用户不必关心如何进行转换查询视图（续）[例3.93]查询选修了1号课程的信息系学生SELECTIS_Student.Sno，SnameFROMIS_Student，SCWHEREIS_Student.Sno=SC.SnoANDSC.Cno=‘1’；查询使用了两个数据源：一个视图和一个基表查询视图（续）[例3.94]在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩SELECT*FROMS_GWHEREGavg>=90；比较与如下方法的区别：CREATETABLES_G0(Snochar(9),Gavgreal);INSERTINTOS_G0SELECTSno,Avg(Grade)FROMSCGROUPBYSno;SELECT*FROMS_G0WHEREGavg>=90;3.7视图3.7.1定义视图3.7.2查询视图3.7.3更新视图3.7.4视图的作用3.7.3更新视图视图可以通过INSERT、DELETE和UPDATE语句修改其中的数据与表不同，对表的操作是直接对表中的数据进行更新对视图的更新则要转换为对基表的更新因此可能存在一些限制更新视图（续）[例3.95]将信息系学生视图IS_Student中学号201215122的学生姓名改为“刘辰”。UPDATEIS_StudentSETSname='刘辰'WHERESno='201215122'；转换后的语句：UPDATEStudentSETSname='刘辰'WHERESno='201215122'ANDSdept='IS'；更新视图（续）[例3.96]向信息系学生视图IS_S中插入一个新的学生记录：

INSERTINTOIS_StudentVALUES(‘201215129’，‘赵新’，20)；[例3.97]删除信息系学生视图IS_Student中学号为201215129的记录

DELETEFROMIS_StudentWHERESno='201215129'；转换为对基本表的更新：

DELETEFROMStudentWHERESno='201215129'ANDSdept='IS'；更新视图（续）通过视图修改行时，WITHCHECKOPTION可确保提交修改后，仍可通过视图看到修改的数据。CREATEVIEWIS_Student1(Sno,Sname,Sage,Sdept)ASSELECTSno,Sname,Sage,Sdept

FROMStudent

WHERESdept='IS'WITHCHECKOPTION例:insertintoIS_Student1values('1001','zhang',20,'IS')

√

insertintoIS_Student1values('1002','wang',19,'MA')

╳更新视图（续）更新视图的限制：一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新允许对行列子集视图进行更新具有计算列或多表视图等不可更新例：视图S_G为不可更新视图。UPDATES_GSETGavg=90WHERESno=‘201215121’；

Grav中的值是通过运算得来，更新时不能找到其唯一来源更新视图（续）数据来自多个基表：学号姓名…………学号课