数据库课件-第3章关系数据库标准语言SQL_2

2019/12/25数据库技术与应用DatabaseTechnologyandApplication2013年4月第三章关系数据库标准语言SQL3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.6数据控制3.7小结2019/12/253.4数据更新3.4.1插入数据3.4.2修改数据3.4.3删除数据2019/12/253.4.1插入数据两种插入数据方式1.插入元组2.插入子查询结果可以一次插入多个元组2019/12/25一、插入元组语句格式INSERTINTO表名[(属性列1[，属性列2…)]VALUES(常量1[，常量2]…)功能将新元组插入指定表中2019/12/25插入元组（续）INTO子句指定要插入数据的表名及属性列属性列的顺序可与表定义中的顺序不一致没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致指定部分属性列：插入的元组在其余属性列上取空值VALUES子句提供的值必须与INTO子句匹配值的个数值的类型2019/12/25插入元组（续）［例1］将一个新学生元组（学号：0903128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage)VALUES('0903128'，'陈冬'，'男'，'IS'，18);2019/12/25[例2］将学生张成民的信息插入到Student表中INSERTINTOStudentVALUES(‘0903126’,‘张成民’,‘男’，18，'CS');插入元组（续）［例3］插入一条选课记录('0903128'，'1')。INSERTINTOSC(Sno，Cno)VALUES(‘0903128’，‘1’)；RDBMS将在新插入记录的Grade列上自动地赋空值。或者：INSERTINTOSCVALUES('0903128'，'1'，NULL)；2019/12/25二、插入子查询结果语句格式INSERTINTO表名[(属性列1[，属性列2…)]]子查询；功能将子查询结果插入指定表中2019/12/25插入子查询结果（续）INTO子句(与插入元组类似)子查询SELECT子句目标列必须与INTO子句匹配值的个数值的类型2019/12/25插入子查询结果（续）[例4]对每一个系，求学生的平均年龄，并把结果存入数据库。第一步：建表CREATETABLEDept_age(SdeptCHAR(15)/*系名*/Avg_ageSMALLINT)；/*学生平均年龄*/2019/12/25插入子查询结果（续）第二步：插入数据INSERTINTODept_age(Sdept，Avg_age)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；2019/12/25插入子查询结果（续）RDBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则:实体完整性参照完整性用户定义的完整性NOTNULL约束UNIQUE约束值域约束2019/12/253.4数据更新3.4.1插入数据3.4.2修改数据3.4.3删除数据2019/12/253.4.2修改数据语句格式UPDATE表名SET列名=表达式[，列名=表达式]…[WHERE条件]；功能修改指定表中满足WHERE子句条件的元组2019/12/25修改数据（续）SET子句指定修改方式（3种）修改某一个元组的值修改多个元组的值带子查询的修改语句要修改的列修改后取值WHERE子句指定要修改的元组缺省表示要修改表中的所有元组2019/12/251.修改某一个元组的值[例5]将学生0903121的年龄改为22岁UPDATEStudentSETSage=22WHERESno='0903121'；2019/12/252.修改多个元组的值[例6]将所有学生的年龄增加1岁UPDATEStudentSETSage=Sage+1；2019/12/253.带子查询的修改语句[例7]将计算机科学系全体学生的成绩置零。UPDATESCSETGrade=0WHERE'CS'=(SELECTSdeptFROMStudentWHEREStudent.Sno=SC.Sno)；或：UPDATESCSETGrade=0WHERESC.SnoIN(SELECTSnoFROMStudentWHERESdept=‘CS’)；2019/12/25修改数据（续）RDBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则实体完整性主码不允许修改用户定义的完整性NOTNULL约束UNIQUE约束值域约束2019/12/253.4数据更新3.4.1插入数据3.4.2修改数据3.4.3删除数据2019/12/253.4.3删除数据语句格式DELETEFROM表名[WHERE条件]；功能删除指定表中满足WHERE子句条件的元组WHERE子句指定要删除的元组缺省表示要删除表中的全部元组，表的定义仍在字典中2019/12/25删除数据（续）三种删除方式:1.删除某一个元组的值2.删除多个元组的值3.带子查询的删除语句2019/12/251.删除某一个元组的值[例8]删除学号为0903128的学生记录。DELETEFROMStudentWHERESno=‘0903128'；2019/12/252.删除多个元组的值[例9]删除所有的学生选课记录。DELETEFROMSC；2019/12/253.带子查询的删除语句[例10]删除计算机科学系所有学生的选课记录。DELETEFROMSCWHERE'CS'=(SELECTSdeptFROMStudentWHEREStudent.Sno=SC.Sno);2019/12/25删除数据(续)DBMS在执行删除语句时会检查所删除元组是否破坏表上已定义的完整性规则，参照完整性，若破坏，则•不允许删除•级联删除2019/12/25第三章关系数据库标准语言SQL3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.6数据控制3.7小结2019/12/253.5视图视图的特点虚表，是从一个或几个基本表（或视图）导出的表数据库中只存放视图的定义，不存放视图对应的数据基表中的数据发生变化，从视图中查询出的数据也随之改变2019/12/253.5视图基于视图的操作查询删除受限更新定义基于该视图的新视图2019/12/253.5视图3.5.1定义视图3.5.2查询视图3.5.3更新视图3.5.4视图的作用2019/12/253.5.1定义视图建立视图删除视图2019/12/25一、建立视图语句格式CREATEVIEW视图名[(列名[，列名]…)]AS子查询[WITHCHECKOPTION]；组成视图的属性列名：全部省略或全部指定子查询不允许含有ORDERBY子句和DISTINCT短语2019/12/25组成视图的属性列名属性列名可全部省略或全部指定全部省略:由子查询中SELECT目标列中的诸字段组成全部指定所有列名的情况:(1)某个目标列是集函数或列表达式(2)多表连接时选出了几个同名列作为视图的字段(3)需要在视图中为某个列启用新的更合适的名字2019/12/25建立视图（续）RDBMS执行CREATEVIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。在对视图查询时，按视图的定义从基本表中将数据查出。2019/12/25建立视图（续）[例1]建立信息系学生的视图。CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'；2019/12/25建立视图（续）[例2]建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'WITHCHECKOPTION；2019/12/25建立视图（续）对IS_Student视图的更新操作：修改操作：自动加上Sdept='IS'的条件删除操作：自动加上Sdept='IS'的条件插入操作：自动检查Sdept属性值是否为'IS'如果不是，则拒绝该插入操作如果没有提供Sdept属性值，则自动定义Sdept为'IS'2019/12/25建立视图（续）基于多个基表的视图[例3]建立信息系选修了1号课程的学生视图。CREATEVIEWIS_S1(Sno，Sname，Grade)ASSELECTStudent.Sno，Sname，GradeFROMStudent，SCWHERESdept='IS'ANDStudent.Sno=SC.SnoANDSC.Cno='1'；2019/12/25建立视图（续）基于视图的视图[例4]建立信息系选修了1号课程且成绩在90分以上的学生的视图。CREATEVIEWIS_S2ASSELECTSno，Sname，GradeFROMIS_S1WHEREGrade=90；2019/12/25建立视图（续）带表达式的视图[例5]定义一个反映学生出生年份的视图。CREATEVIEWBT_S(Sno，Sname，Sbirth)ASSELECTSno，Sname，2012-SageFROMStudent；2019/12/25建立视图（续）分组视图[例6]将学生的学号及他的平均成绩定义为一个视图（假设SC表中“成绩”列Grade为数字型）。CREATVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；2019/12/25建立视图（续）不指定属性列[例7]将Student表中所有女生记录定义为一个视图CREATEVIEWF_Student(F_Sno,name,sex,age,dept)ASSELECT*FROMStudentWHERESsex=‘女’；缺点：以SELECT*方式创建的视图可扩充性差，应尽可能避免。原因：修改基表Student的结构后，Student表与F_Student视图的映象关系被破坏，导致该视图不能正确工作。2019/12/25建立视图（续）CREATEVIEWF_Student2(stdnum,name,sex,age,dept)ASSELECTSno，Sname，Ssex，Sage，SdeptFROMStudentWHERESsex='女'；为基表Student增加属性列不会破坏Student表与F_Student2视图的映象关系。2019/12/25二、删除视图语句的格式：DROPVIEW视图名；该语句从数据字典中删除指定的视图定义如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除删除基表时，由该基表导出的所有视图定义都必须显式地使用DROPVIEW语句删除2019/12/25删除视图(续）［例8］删除视图BT_S：DROPVIEWBT_S；删除视图IS_S1：DROPVIEWIS_S1；级联删除：DROPVIEWIS_S1CASCADE;2019/12/253.5视图3.5.1定义视图3.5.2查询视图3.5.3更新视图3.5.4视图的作用2019/12/253.5.2查询视图用户角度：查询视图与查询基本表相同RDBMS实现视图查询的方法通过视图消解法（ViewResolution）完成进行有效性检查，检查所查询的视图是否存在若视图存在，将其定义中的子查询和用户查询结合起来，转换成等价的对基本表的查询执行修正后的查询2019/12/25查询视图（续）[例9]在信息系学生的视图中找出年龄小于20