《数据库系统原理》第3章 关系数据库标准语言SQLppt课件

《《数据库系统原理》第3章 关系数据库标准语言SQLppt课件》由会员上传分享，免费在线阅读，更多相关内容在PPT专区-天天文库。

计算机科学系2017版厦门大学第3章关系数据库标准语言SQL（2017版）《数据库系统原理》

1提纲3.1SQL概述3.2学生-课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图

23.1SQL概述3.1.1SQL的产生与发展3.1.2SQL的特点3.1.3SQL的基本概念

33.1.1SQL的产生与发展1974年，由Boyce和Chamberlin提出，并在IBM公司研制的关系数据库管理系统原型SystemR上实现1986年10月，美国国家标准局的数据库委员会X3H2批准了SQL作为关系数据库语言的美国标准，并公布了SQL标准文本1987年，国际标准化组织通过这一标准

43.1.2SQL的特点SQL的特点1.综合统一2.高度非过程化3.面向集合的操作方式4.以同一种语法结构提供两种使用方法5.语言简洁，易学易用

51、综合统一（操纵三级模式）SQL视图1视图2基表1基表2基表3基表4存储文件1存储文件2外模式模式内模式

65、语言简捷，易学易用表语言的动词SQL功能动词数据定义CREATE，DROP，ALTER数据查询SELECT数据操纵数据控制GRANT，REVOKE3.1SQLINSERT，UPDATE，DELETE

7第3章关系数据库标准语言SQL3.1SQL概述3.2学生-课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图

83.2学生-课程数据库学生-课程数据库学生表：Student(Sno，Sname，Ssex，Sage，Sdept)课程表：Course(Cno，Cname，Cpno，Ccredit)学生选课表：SC(Sno，Cno，Grade)

93.2学生-课程数据库学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19ISStudent(a)

103.2学生-课程数据库课程号课程名先行课学分CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64Course(b)

113.2学生-课程数据库学号课程号成绩SnoCnoGrade9500119295001285950013889500229095002380SC(c)

12提纲3.1SQL概述3.2学生-课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图

133.3数据定义3.3.1模式的定义与删除3.3.2基本表的定义、删除与修改3.3.3索引的建立与删除

143.3数据定义

153.3数据定义3.3.1模式的定义与删除3.3.2基本表的定义、删除与修改3.3.3索引的建立与删除

163.3.1模式的定义与删除一、定义模式CREATESCHEMA<模式名>AUTHORIZATION<用户名>Ps:1、若没有指定<模式名>，那么<模式名>隐含为<用户名>2、要创建模式，调用该命令的用户必须具有DBA权限，或者获得了DBA授予的CREATESCHEMA的权限

17例题[例1]定义一个学生-课程模式S-TCREATESCHEMA“S-T”AUTHORIZATIONWANG;为用户“WANG”定义了一个模式S-T

183.3.1模式的定义与删除二、删除模式DROPSCHEMA<模式名>其中CASCADE和RESTRICT两者必选其一1、CASCADE（级联），表示在删除模式同时把该模式中所有的数据库对象全部一起删除2、RESTRICT（限制），表示若该模式下已定义了下属的数据库对象，则拒绝删除

19例题[例4]删除模式ZHANGDROPSCHEMAZHANGCASCADE

203.3数据定义3.3.1模式的定义与删除3.3.2基本表的定义、删除与修改3.3.3索引的建立与删除

213.3.2基本表的定义、删除与修改一、定义基本表<表名>：所要定义的基本表的名字<列名>：组成该表的各个属性（列）<列级完整性约束条件>：涉及相应属性列的完整性约束条件<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件CREATETABLE<表名>（<列名><数据类型>[<列级完整性约束条件>][，<列名><数据类型>[<列级完整性约束条件>]]…[，<表级完整性约束条件>]）；

22例题[例1]建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号是主键，并且姓名取值也唯一。CREATETABLEStudent(SnoCHAR(5)primarykey,SnameCHAR(8)UNIQUE,SsexCHAR(2),SageINT,SdeptCHAR(10));

23例题（续）SnoSnameSsexSageSdept↑↑↑　　　↑↑字符型字符型字符型整数字符型长度为5长度为8长度为2长度为10不能为空值

24定义基本表（续）常用完整性约束主码约束：PRIMARYKEY唯一性约束：UNIQUE非空值约束：NOTNULL参照完整性约束PRIMARYKEY与UNIQUE的区别？

25例题（续）[例2]建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。CREATETABLESC(SnoCHAR(5),CnoCHAR(3),Gradeint,Primarykey(Sno,Cno));

26二、修改基本表ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>][ALTERCOLUMN<列名><数据类型>][DROPCOLUMN<列名><数据类型>]；<表名>：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的完整性约束条件ALTERCOLUMN子句：用于修改列名和数据类型DROPCOLUMN子句：用于删除列

27例题[例2]向Student表增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDScomeDATETIME;不论基本表中原来是否已有数据，新增加的列一律为空值。

28例题[例3]将入学日期的数据类型改为字符型。ALTERTABLEStudentALTERCOLUMNScomechar(8);注：修改原有的列定义有可能会破坏已有数据

29语句格式（续）删除属性列[例4]将入学日期列删除掉。ALTERTABLEStudentDROPCOLUMNScome

30三、删除基本表DROPTABLE<表名>;基本表删除数据、表上的索引自动都删除

31例题[例5]删除SC表DROPTABLESC;

323.3数据定义3.3.1模式的定义与删除3.3.2基本表的定义、删除与修改3.3.3索引的建立与删除

333.3.3索引的建立与删除建立索引是加快查询速度的有效手段建立索引DBA或表的属主（即建立表的人）根据需要建立有些DBMS自动建立以下列上的索引PRIMARYKEYUNIQUE维护索引DBMS自动完成使用索引DBMS自动选择是否使用索引以及使用哪些索引

34一、建立索引语句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)；用<表名>指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTER表示要建立的索引是聚簇索引

35建立索引（续）唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束

36建立索引（续）聚簇索引建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致例：CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放

37建立索引（续）在一个基本表上最多只能建立一个聚簇索引聚簇索引的用途：对于某些类型的查询，可以提高查询效率聚簇索引的适用范围很少对基表进行增删操作很少对其中的变长列进行修改操作聚簇索引的不适用情形表记录太少经常插入、删除、修改的表数据分布平均的表字段

38建立索引（续）在下列三种情况下，有必要建立簇索引：(1)查询语句中采用该字段作为排序列(2)需要返回局部范围的大量数据(3)表格中某字段内容的重复性比较大例如，student表中dno(系号)一列有大量重复数据，当在dno列上建立了簇索引后，下面的连接查询速度会加快。

39建立索引（续）多列索引和多个单列索引考虑两种不同的建立索引方式：case1:对c1,c2,c3三列按此顺序添加一个多列索引;case2:对c1,c2,c3分别建立三个单列索引;问题1:按c1搜索时，哪种索引效率快?答：case2问题2:按C2搜索时，哪种索引效率快?答：case2,并且，case1的索引无效问题3：按C1,C2,C3搜索哪种效率快?答：case1问题4：按C2,C3,C1搜索时哪种效率快?答：case2,因为没有按多列索引的顺序搜索，case1的索引没有使用到。覆盖查询简单的说就是所有查询列被所使用的索引覆盖的查询

40建立索引（续）如何去建立一个多列索引，最重要的一个问题是如何安排列的顺序是至关重要的比如需要对一个表tb里面的两个字段foo,bar建一个索引，那么索引的顺序是（foo,bar)还是(bar,foo)呢假设tb表有1700条记录，foo字段有750个不同的记录，那么foo字段上的cardinality是750。总规则可以说是cardinality越大的字段应该排在索引的第一位就是说索引的位置是(foo,bar)，因为cardinality越大那么第一次取出来的记录集就越小，再进行第二次查询的次数就越少了。

41建立索引（续）当在同一表格中建立簇索引和非簇索引时，先建立簇索引后建非簇索引比较好。因为如先建非簇索引的话，当建立簇索引时，SQLServer会自动将非簇索引删除，然后重新建立非簇索引。每个表仅可以有一个簇索引，最多可以有249个非簇索引。它们均允许以一个或多个字段作为索引关键字(IndexKey)，但最多只能有16个字段。SQLServer只对那些能加快数据查询速度的索引才能被选用。如果利用索引检索还不如顺序扫描速度快，SQLServer仍用扫描方法检索数据。建立不能被采用的索引只会增加系统的负担，降低检索速度。因此，可利用性是建立索引的首要条件。

42例题[例6]为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno);CREATEUNIQUEINDEXCoucnoONCourse(Cno);CREATEUNIQUEINDEXSCnoONSC(SnoASC,CnoDESC);

43二、删除索引DROPINDEX<索引名>；删除索引时，系统会从数据字典中删去有关该索引的描述。[例7]删除Student表的Stusname索引。DROPINDEXStudent.Stusname；

44第3章关系数据库标准语言SQL3.1SQL概述3.2学生-课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图

453.4查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询3.4.5SELECT语句的一般格式

463.4查询语句格式SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…FROM<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；

473.4查询SELECT子句：指定要显示的属性列FROM子句：指定查询对象(基本表或视图)WHERE子句：指定查询条件GROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。HAVING短语：筛选出只有满足指定条件的组ORDERBY子句：对查询结果表按指定列值的升序或降序排序

483.4查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询3.4.5SELECT语句的一般格式

493.4.1单表查询查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用集函数五、对查询结果分组

50查询指定列[例1]查询全体学生的学号与姓名。SELECTSno,SnameFROMStudent;[例2]查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；

51查询全部列[例3]查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；

523.查询经过计算的值SELECT子句的<目标列表达式>为表达式算术表达式字符串常量函数列别名等

533.查询经过计算的值[例4]查全体学生的姓名及其出生年份。SELECTSname,2011-SageFROMStudent;

543.查询经过计算的值[例5]查询全体学生的姓名、出生年份和所在系。在出生年份前面增加一个说明，在系名称后面增加一个“系”作为表示SELECTSname,出生年份:',2011-Sage,Sdept+'系'FROMStudent;

55[例5.1]使用列别名改变查询结果的列标题SELECTSname'姓名','YearofBirth:''生日标识',2011-Sage'生日',Sdept+'系''系名'FROMStudent;

56二、选择表中的若干元组消除取值重复的行查询满足条件的元组

571.消除取值重复的行在SELECT子句中使用DISTINCT短语假设SC表中有下列数据SnoCnoGrade---------------------9500119295001285950013889500229095002380

58ALL与DISTINCT[例6]查询选修了课程的学生学号。(1)SELECTSnoFROMSC;或(默认ALL)SELECTALLSnoFROMSC;(2)SELECTDISTINCTSnoFROMSC;

59例题（续）注意DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正确的写法SELECTDISTINCTCno，GradeFROMSC;

602.查询满足条件的元组表3.3常用的查询条件查询条件谓词比较=，>，<，>=，<=，!=，<>，!>，!<；NOT+上述比较运算符确定范围BETWEENAND，NOTBETWEENAND确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重条件AND，ORWHERE子句常用的查询条件

61(1)比较大小在WHERE子句的<比较条件>中使用比较运算符=，>，<，>=，<=，!=或<>，!>，!<，逻辑运算符NOT+比较运算符[例8]查询所有年龄在20岁以下的学生姓名及其年龄。SELECTSname,SageFROMStudentWHERESage<20;或SELECTSname，SageFROMStudentWHERENOTSage>=20；

62(2)确定范围使用谓词BETWEEN…AND…NOTBETWEEN…AND…[例10]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23;

63例题（续）[例11]查询年龄不在20~23岁之间的学生姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；

64(3)确定集合使用谓词IN<值表>,NOTIN<值表><值表>：用逗号分隔的一组取值[例12]查询信息系（IS）和计算机科学系（CS）学生的姓名和性别。SELECTSname,SsexFROMStudentWHERESdeptIN('IS','CS');

65(3)确定集合[例13]查询既不是信息系又不是计算机科学系的学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptNOTIN('IS'，'CS');

66(4)字符串匹配[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]<匹配串>：指定匹配模板匹配模板：固定字符串或含通配符的字符串当匹配模板为固定字符串时，可以用=运算符取代LIKE谓词用!=或<>运算符取代NOTLIKE谓词

67通配符%(百分号)代表任意长度（长度可以为0）的字符串例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串_(下横线)代表任意单个字符例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串

68ESCAPE短语：当用户要查询的字符串本身就含有%或_时，要使用ESCAPE'<换码字符>'短语对通配符进行转义。

69例题1)匹配模板为固定字符串[例14]查询学号为95001的学生的详细情况。SELECT*FROMStudentWHERESnoLIKE'95001';等价于：SELECT*FROMStudentWHERESno='95001'；

70例题（续）2)匹配模板为含通配符的字符串[例15]查询所有姓刘学生的姓名、学号和性别。SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE‘刘%’；

71例题（续）匹配模板为含通配符的字符串（续）[例16]查询姓“刘"且全名为三个汉字的学生的姓名。SELECTSnameFROMStudentWHERESnameLIKE‘刘__'；备注：这里有两个_

72关于SQLServer2008字符集SELECTCOLLATIONPROPERTY('Chinese_PRC_Stroke_CI_AI_KS_WS','CodePage')查询结果：936简体中文GBK950繁体中文BIG5437美国/加拿大英语932日文949韩文866俄文65001unicodeUFT-8数据库字符集为ASCII时，一个汉字需要两个_；当数据库字符集为GBK时，一个汉字只需要一个_

73例题（续）匹配模板为含通配符的字符串（续）[例17]查询名字中第2个字为“敏”字的学生的姓名和学号。SELECTSname，SnoFROMStudentWHERESnameLIKE'_敏%'；

74例题（续）[例18]查询所有不姓刘的学生姓名。SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE'刘%'；

75例题（续）3)使用换码字符将通配符转义为普通字符[例19]查询课程名称中包含“面向对象_C++课程”的课程号和学分。SELECTCno，CcreditFROMCourseWHERECnameLIKE'%面向对象_C++%'

76例题（续）SELECTCno，CcreditFROMCourseWHERECnameLIKE‘%面向对象\_C++%'ESCAPE'\'

77例题（续）使用换码字符将通配符转义为普通字符(续)[例20]查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况。SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\'；

78(5)涉及空值的查询使用谓词ISNULL或ISNOTNULL“ISNULL”不能用“=NULL”代替[例21]有些课没有先修课程。查询没有先修课程的课程名称。SelectCnameFromCourseWhereCpnoISNULL

79(6)多重条件查询用逻辑运算符AND和OR来联结多个查询条件AND的优先级高于OR可以用括号改变优先级可用来实现多种其他谓词[NOT]IN[NOT]BETWEEN…AND…

80例题[例23]查询计算机系年龄在20岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20;

81改写[例12][例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname,SsexFROMStudentWHERESdeptIN('IS','MA','CS')可改写为：SELECTSname,SsexFROMStudentWHERESdept='IS'ORSdept='MA'ORSdept='CS';

82改写[例10][例10]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；可改写为：SELECTSname，Sdept，SageFROMStudentWHERESage>=20ANDSage<=23；

83三、对查询结果排序使用ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时空值的显示顺序由具体系统实现来决定例如按升序排，含空值的元组最后显示按降序排，空值的元组最先显示各个系统的实现可以不同，只要保持一致即可

84对查询结果排序（续）[例24]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECTSno,GradeFROMSCWHERECno='3'ORDERBYGradeDESC;

85对查询结果排序（续）[例25]查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。SELECT*FROMStudentORDERBYSdept,SageDESC;

86四、使用集函数5类主要聚集函数计数COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）计算总和SUM（[DISTINCT|ALL]<列名>）计算平均值AVG（[DISTINCT|ALL]<列名>）

87使用集函数（续）求最大值MAX（[DISTINCT|ALL]<列名>）求最小值MIN（[DISTINCT|ALL]<列名>）DISTINCT短语：在计算时要取消指定列中的重复值ALL短语：不取消重复值ALL为缺省值

88使用集函数（续）[例26]查询学生总人数。[例27]查询选修了课程的学生人数。SELECTCOUNT(DISTINCTSno)FROMSC；注：用DISTINCT以避免重复计算学生人数SELECTCOUNT(*)FROMStudent；

89使用集函数（续）[例28]计算2号课程的学生平均成绩。[例29]查询选修3号课程的学生最高分数。SELECTAVG(Grade)FROMSCWHERECno='2'；SELECTMAX(Grade)FROMSCWHERCno='3'；

90五、对查询结果分组使用GROUPBY子句分组细化聚集函数的作用对象未对查询结果分组，聚集函数将作用于整个查询结果对查询结果分组后，聚集函数将分别作用于每个组

91使用GROUPBY子句分组[例30]求各个课程号及相应的选课人数。SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；

92使用GROUPBY子句分组O_IdOrderDateOrderPriceCustomer12008/12/291000Bush22008/11/231600Carter32008/10/05700Bush42008/09/28300Bush52008/08/062000Adams62008/07/21100Carter我们拥有下面这个"Orders"表：现在，我们希望查找每个客户的总金额（总订单）。语句1：SELECTCustomer,SUM(OrderPrice)FROMOrders语句2：SELECTCustomer,SUM(OrderPrice)FROMOrdersGROUPBYCustomer课堂作业

93使用GROUPBY子句分组语句1：SELECTCustomer,SUM(OrderPrice)FROMOrdersCustomerSUM(OrderPrice)Bush5700Carter5700Bush5700Bush5700Adams5700Carter5700

94使用GROUPBY子句分组语句2：SELECTCustomer,SUM(OrderPrice)FROMOrdersGROUPBYCustomerCustomerSUM(OrderPrice)Bush2000Carter1700Adams2000

95对查询结果分组（续）GROUPBY子句的作用对象是查询的中间结果表分组方法：按指定的一列或多列值分组，值相等的为一组;groupby是先排序后分组使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性和聚集函数，或者说，select后面的所有列中,没有使用聚合函数的列,必须出现在groupby后面如果要用到groupby一般用到的就是“每”这个字,例如：每个部门有多少人就要用到分组的技术

96对查询结果分组（续）selectSno,CnofromSCgroupbySno--将会出现错误--消息8120，级别16，状态1，第1行选择列表中的列‘SC.Cno’无效，因为该列没有包含在聚合函数或GROUPBY子句中。这就是我们需要注意的一点，如果在返回集字段中，这些字段要么就要包含在GroupBy语句的后面，作为分组的依据；要么就要被包含在聚合函数中。selectSnoas‘学号’,COUNT(*)as‘选课门数'fromSCgroupbySno

97使用HAVING短语筛选最终输出结果[例31]查询选修了2门及以上课程的学生学号。SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>2；

98例题[例32]查询有2门及以上课程是85分以上的学生的学号及（85分以上的）课程数SELECTSno,COUNT(*)FROMSCWHEREGrade>=85GROUPBYSnoHAVINGCOUNT(*)>=2;

99使用HAVING短语筛选最终输出结果只有满足HAVING短语指定条件的组才输出HAVING短语与WHERE子句的区别：作用对象不同WHERE子句作用于基表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。

1003.4查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询3.4.5SELECT语句的一般格式

1013.4.2连接查询同时涉及多个表的查询称为连接查询用来连接两个表的条件称为连接条件或连接谓词一般格式：[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>比较运算符：=、>、<、>=、<=、!=[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>

102连接查询（续）连接字段连接谓词中的列名称为连接字段连接条件中的各连接字段类型必须是可比的，但不必是相同的

103连接操作的执行过程嵌套循环法(NESTED-LOOP)首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕

104嵌套循环连接基于元组的嵌套循环连接Result={};/*初始化结果集合*/ForeachtuplesinSForeachtuplerinRIfr.B=s.Bthen/*元组r和元组s满足连接条件*/Joinrandsastuplet;OutputtintoResult;/*输出连接结果元组*/EndifEndforEndforReturnResult

105嵌套循环连接上面基于元组的嵌套循环连接算法中，对于循环外层的关系，通常称为外关系，而对循环内层的关系称为内关系。在执行嵌套循环连接时，仅对外关系进行1次读取操作，而对内关系则需要进行反复读取操作。如果不进行优化的话，这种基于元组的执行代价很大，以磁盘IO计算最多可能达到Card(R)*Card(S)。因此，通常对这种算法进行修改，以减少嵌套循环连接的磁盘IO代价。一种方法是使用连接属性上的索引，以减少参与连接元组的数量，称为“索引嵌套循环连接”

106排序合并法(SORT-MERGE)常用于=连接首先按连接属性对表1和表2排序对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续

107排序合并法找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续重复上述操作，直到表1或表2中的全部元组都处理完毕为止

108连接查询（续）SQL中连接查询的主要类型广义笛卡尔积等值连接(含自然连接)非等值连接查询自身连接查询外连接查询复合条件连接查询

109一、广义笛卡尔积不带连接谓词的连接很少使用例：SELECTStudent.*,SC.*FROMStudent,SC

110二、等值与非等值连接查询等值连接、自然连接、非等值连接[例32]查询每个学生及其选修课程的情况。SELECTStudent.*，SC.*FROMStudent，SCWHEREStudent.Sno=SC.Sno；

111等值连接连接运算符为=的连接操作[<表名1>.]<列名1>=[<表名2>.]<列名2>任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。

112自然连接等值连接的一种特殊情况，把目标列中重复的属性列去掉。[例33]对[例32]用自然连接完成。SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；

113非等值连接查询连接运算符不是=的连接操作[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>比较运算符：>、<、>=、<=、!=[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>

114三、自身连接一个表与其自己进行连接，称为表的自身连接需要给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀

115自身连接（续）[例34]查询每一门课的间接先修课（即先修课的先修课）SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；

116四、外连接（OuterJoin）外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出

117外连接（续）[例33]查询每个学生及其选修课程的情况包括没有选修课程的学生----用外连接操作SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudentLEFTJOINSCON(Student.Sno=SC.Sno)

118五、复合条件连接WHERE子句中含多个连接条件时，称为复合条件连接[例35]查询选修2号课程且成绩在86分以上的所有学生的学号、姓名SELECTStudent.Sno,student.SnameFROMStudent,SCWHEREStudent.Sno=SC.Sno/*连接谓词*/ANDSC.Cno='2'/*其他限定条件*/ANDSC.Grade>86；/*其他限定条件*/

119多表连接[例36]查询每个学生的姓名、选修课程名及成绩。SELECTSname，Cname，GradeFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoandSC.Cno=Course.Cno；

1203.4查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询3.4.5SELECT语句的一般格式

1213.4.3嵌套查询嵌套查询概述嵌套查询分类嵌套查询求解方法引出子查询的谓词

122嵌套查询(续)嵌套查询概述一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

123嵌套查询(续)SELECTSname外层查询/父查询FROMStudentWHERESnoIN（SELECTSno内层查询/子查询FROMSCWHERECno='2'）；[例]查询选修2号课程的学生信息。

124嵌套查询(续)子查询的限制不能使用ORDERBY子句层层嵌套方式反映了SQL语言的结构化有些嵌套查询可以用连接运算替代

125嵌套查询分类不相关子查询子查询的查询条件不依赖于父查询相关子查询子查询的查询条件依赖于父查询AnIntroductiontoDatabaseSystem

126嵌套查询求解方法不相关子查询是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。

127嵌套查询求解方法（续）相关子查询首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止。

128引出子查询的谓词带有IN谓词的子查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询

129一、带有IN谓词的子查询[例37]查询与“刘晨”在同一个系学习的学生。此查询要求可以分步来完成①确定“刘晨”所在系名SELECTSdeptFROMStudentWHERESname=‘刘晨’结果为：SdeptIS

130带有IN谓词的子查询（续）②查找所有在IS系学习的学生。SELECTSno，Sname，SdeptFROMStudentWHERESdept='IS'；结果为：SnoSnameSdept95001刘晨IS95004张立IS

131构造嵌套查询将第一步查询嵌入到第二步查询的条件中SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=‘刘晨’)；此查询为不相关子查询。DBMS求解该查询时也是分步去做的。

132带有IN谓词的子查询（续）用自身连接完成本查询要求SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1，StudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname='刘晨'；

133带有IN谓词的子查询（续）父查询和子查询中的表均可以定义别名SELECTSno，Sname，SdeptFROMStudentS1WHERES1.SdeptIN(SELECTSdeptFROMStudentS2WHERES2.Sname=‘刘晨’)；

134带有IN谓词的子查询（续）[例38]查询选修了课程名为“信息系统”的学生学号和姓名SELECTSno，Sname③最后在Student关系中FROMStudent取出Sno和SnameWHERESnoIN(SELECTSno②然后在SC关系中找出选FROMSC修了3号课程的学生学号WHERECnoIN(SELECTCno①首先在Course关系中找出“信FROMCourse息系统”的课程号，结果为3号WHERECname=‘信息系统’));

135带有IN谓词的子查询（续）用连接查询SELECTSno，SnameFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=‘信息系统’；

136二、带有比较运算符的子查询当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或<>）。与ANY或ALL谓词配合使用

137带有比较运算符的子查询（续）例：假设一个学生只可能在一个系学习，并且必须属于一个系，则在[例37]可以用=代替IN：SELECTSno，Sname，SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStudentWHERESname='刘晨‘)；

138带有比较运算符的子查询（续）例：找出每个学生超过他自己选修课程平均成绩的课程号SelectSno,CnoFromSCxWhereGrade>=(selectavg(Grade)fromSCywherey.Sno=x.Sno);相关子查询

139三、带有ANY或ALL谓词的子查询谓词语义ANY：任意一个值ALL：所有值

140带有ANY或ALL谓词的子查询（续）需要配合使用比较运算符>ANY大于子查询结果中的某个值>ALL大于子查询结果中的所有值=ANY大于等于子查询结果中的某个值>=ALL大于等于子查询结果中的所有值<=ANY小于等于子查询结果中的某个值<=ALL小于等于子查询结果中的所有值=ANY等于子查询结果中的某个值=ALL等于子查询结果中的所有值（通常没有实际意义）!=（或<>）ANY不等于子查询结果中的某个值!=（或<>）ALL不等于子查询结果中的任何一个值

141带有ANY或ALL谓词的子查询（续）[例39]查询其他系中比信息系某一个(其中某一个)学生年龄小的学生姓名和年龄SELECTSname，SageFROMStudentWHERESage'IS';/*注意这是父查询块中的条件*/

142带有ANY或ALL谓词的子查询（续）结果SnameSage王敏18执行过程1.DBMS执行此查询时，首先处理子查询，找出IS系中所有学生的年龄，构成一个集合(19，18)2.处理父查询，找所有不是IS系且年龄小于19或18的学生

143带有ANY或ALL谓词的子查询（续）ANY和ALL谓词有时可以用集函数实现ANY与ALL与集函数的对应关系=<>或!=<<=>>=ANYIN--MIN>=MINALL--NOTINMAX>=MAX

144带有ANY或ALL谓词的子查询（续）用集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数

145带有ANY或ALL谓词的子查询（续）[例40]查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。方法一：用ALL谓词SELECTSname，SageFROMStudentWHERESage'IS’;

146带有ANY或ALL谓词的子查询（续）方法二：用集函数SELECTSname，SageFROMStudentWHERESage<(SELECTMIN(Sage)FROMStudentWHERESdept='IS')ANDSdept<>'IS’;

147补充：分页语句利用NOTIN和SELECTTOP实现分页selectTOP2*FROMStudentwhere(SnoNOTIN(selecttop4SnofromStudentorderbySno))orderbySnoselectTOP页大小*FROMStudentwhere(SnoNOTIN(selecttop页大小*（页数-1）SnofromStudentorderbySno))orderbySno假设每页大小为2，显示第3页，可以采用以下SQL语句：

148补充：分页语句selecttop2*fromStudentwhere(Sno>(selectMAX(Sno)from(Selecttop4Snofromstudentorderbysno)asT))orderbysnoselecttop页大小*fromStudentwhere(Sno>(selectMAX(Sno)from(Selecttop页大小*（页数-1）Snofromstudentorderbysno)asT))orderbysno

149四、带有EXISTS谓词的子查询1.EXISTS谓词2.NOTEXISTS谓词3.不同形式的查询间的替换4.相关子查询的效率5.用EXISTS/NOTEXISTS实现全称量词6.用EXISTS/NOTEXISTS实现逻辑蕴函

150带有EXISTS谓词的子查询(续）1.EXISTS谓词存在量词带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则返回真值若内层查询结果为空，则返回假值由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义2.NOTEXISTS谓词

151带有EXISTS谓词的子查询(续）[例41]查询所有选修了2号课程的学生姓名。

152带有EXISTS谓词的子查询(续）思路分析：本查询涉及Student和SC关系。在Student中依次取每个元组的Sno值，用此值去检查SC关系。若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno=‘2'，则取此Student.Sname送入结果关系。

153用嵌套查询SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSC/*相关子查询*/WHERESno=Student.SnoANDCno=‘2')；带有EXISTS谓词的子查询(续）

154带有EXISTS谓词的子查询(续）用连接运算SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=‘2';

155带有EXISTS谓词的子查询(续）[例42]查询没有选修1号课程的学生姓名。SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=‘1')；

156带有EXISTS谓词的子查询(续）3.不同形式的查询间的替换一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。

157带有EXISTS谓词的子查询(续）例：[例37]查询与“刘晨”在同一个系学习的学生。可以用带EXISTS谓词的子查询替换：SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=‘刘晨’)；

158带有EXISTS谓词的子查询(续）SELECTSno，Sname，SdeptFROMStudentS1WHEREEXISTS(SELECT*FROMStudentS2WHERES2.Sdept=S1.SdeptANDS2.Sname='刘晨‘)；例：[例37]查询与“刘晨”在同一个系学习的学生。可以用带EXISTS谓词的子查询替换：

159带有EXISTS谓词的子查询(续）为什么在嵌套连接中，EXISTS会比IN效率高？

160带有EXISTS谓词的子查询(续）5.用EXISTS/NOTEXISTS实现全称量词SQL语言中没有全称量词（Forall）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：(x)P≡(x(P))

161带有EXISTS谓词的子查询(续）[例43]查询选修了全部课程的学生姓名。SELECTSnameFROMStudentWHERENOTEXISTS（SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno））；SQL语句写法：查询这样的学生，没有一门课程是他不选修的

162带有EXISTS谓词的子查询(续）select*fromscwherecno=course.cnoandsno=student.sno是查询出所有已经选择过课程的学生及相应课程select*fromcoursewherenotexists则是所有没有被选择的课程在这个基础上的selectsnamefromstudentwherenotexists则是选取所有没有未选择课程的学生，即选择了所有课程的学员名称

163带有EXISTS谓词的子查询(续）方法二：SelectSnameFromstudentWhereSnoIN(selectSnofromSCGroupbySnoHavingcount(*)=(selectcount(*)fromcourse))

164带有EXISTS谓词的子查询(续）6.用EXISTS/NOTEXISTS实现逻辑蕴函SQL语言中没有蕴函(Implication)逻辑运算可以利用谓词演算将逻辑蕴函谓词等价转换为：pq≡p∨q

165带有EXISTS谓词的子查询(续）[例44]查询至少选修了学生95002选修的全部课程的学生号码。解题思路：用逻辑蕴函表达：查询学号为x的学生，对所有的课程y，只要95002学生选修了课程y，则x也选修了y。形式化表示：用P表示谓词“学生95002选修了课程y”用q表示谓词“学生x选修了课程y”则上述查询为:(y)pq

166带有EXISTS谓词的子查询(续）等价变换：(y)pq≡(y((pq))≡(y((p∨q)≡y(p∧q)变换后语义：不存在这样的课程y，学生95002选修了y，而学生x没有选。

167带有EXISTS谓词的子查询(续）用NOTEXISTS谓词表示：SELECT*fromStudentwherenotexists(select*fromSCSC1whereSC1.Sno='95002'andnotexists(select*fromSCSC2whereSC1.Cno=SC2.CnoandSC2.Sno=Student.Sno))

168课堂作业题目：查询选修了“以数据库作为先行课”的课程的学生姓名和学号要求：（1）第一种方法：使用多表连接（2）第二种方法：使用嵌套查询

169课堂作业（1）第一种方法：使用多表连接selectStudent.Sno,student.snamefromCoursefirst,Coursesecond,SC,studentwherefirst.cpno=second.cnoandsecond.Cname='数据库'andSC.Cno=first.CnoandSC.Sno=Student.sno

170课堂作业（2）第二种方法：使用嵌套查询selectStudent.Sno,Student.SnamefromStudentwhereStudent.Snoin(selectSC.SnofromSCwhereSC.Cnoin(selectfirst.CnofromCoursefirst,Coursesecondwherefirst.cpno=second.cnoandsecond.Cname='数据库'))

1713.4查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询3.4.5SELECT语句的一般格式

1723.4.4集合查询集合操作种类并操作(UNION)交操作(INTERSECT)差操作(EXCEPT)

1731．并操作形式<查询块>UNION<查询块>参加UNION操作的各结果表的列数必须相同；对应项的数据类型也必须相同

174并操作（续）[例45]查询计算机科学系的学生及年龄不大于19岁的学生。方法一：SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19；

175并操作（续）方法二：SELECTDISTINCT*FROMStudentWHERESdept='CS'ORSage<=19；

176并操作（续）[例46]查询选修了课程1或者选修了课程2的学生号码。

177并操作（续）方法二：SELECTDISTINCTSnoFROMSCWHERECno='1'ORCno='2'；方法一：SELECTSnoFROMSCWHERECno='1'UNIONSELECTSnoFROMSCWHERECno='2'；

178并操作（续）[例47]查询学校中所有师生的姓名。SELECTSnameFROMStudentUNIONSELECTTnameFROMTeacher;

1792．交操作标准SQL中没有提供集合交操作，但可用其他方法间接实现。

1802．交操作[例48]查询计算机科学系的学生与年龄不大于19岁的学生的交集本例实际上就是查询计算机科学系中年龄不大于19岁的学生SELECT*FROMStudentWHERESdept='CS'ANDSage<=19；

181交操作（续）[例49]查询选修课程1的学生集合与选修课程2的学生集合的交集本例实际上是查询既选修了课程1又选修了课程2的学生SELECTSnoFROMSCWHERECno='1'ANDSnoIN(SELECTSnoFROMSCWHERECno='2')；

182交操作（续）[例50]查询学生姓名与教师姓名的交集本例实际上是查询学校中与教师同名的学生姓名SELECTDISTINCTSnameFROMStudentWHERESnameIN(SELECTTnameFROMTeacher);

1833．差操作标准SQL中没有提供集合差操作，但可用其他方法间接实现。

1843．差操作[例51]查询计算机科学系的学生与年龄不大于19岁的学生的差集。本例实际上是查询计算机科学系中年龄大于19岁的学生SELECT*FROMStudentWHERESdept='CS'ANDSage>19；

185差操作（续）[例52]查询学生姓名与教师姓名的差集本例实际上是查询学校中未与教师同名的学生姓名SELECTDISTINCTSnameFROMStudentWHERESnameNOTIN(SELECTTnameFROMTeacher);

1864.对集合操作结果的排序ORDERBY子句只能用于对最终查询结果排序，不能对中间结果排序任何情况下，ORDERBY子句只能出现在最后对集合操作结果排序时，ORDERBY子句中用数字指定排序属性

187对集合操作结果的排序（续）[例53]错误写法SELECT*FROMStudentWHERESdept='CS'ORDERBYSnoUNIONSELECT*FROMStudentWHERESage<=19ORDERBYSno；

188对集合操作结果的排序（续）正确写法SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19ORDERBY1；

1893.4查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询3.4.5SELECT语句的一般格式

1903.4.5SELECT语句的一般格式SELECT[ALL|DISTINCT]<目标列表达式>[别名][，<目标列表达式>[别名]]…FROM<表名或视图名>[别名][，<表名或视图名>[别名]]…[WHERE<条件表达式>][GROUPBY<列名1>[，<列名1’>]...[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC][，<列名2’>[ASC|DESC]]…]；

191第3章关系数据库标准语言SQL3.1SQL概述3.2学生-课程数据库3.3数据定义3.4查询3.5数据更新3.6视图

1923.5数据更新3.5.1插入数据3.5.2修改数据3.5.3删除数据

1933.5.1插入数据两种插入数据方式插入单个元组插入子查询结果

1941.插入单个元组语句格式INSERTINTO<表名>[(<属性列1>[，<属性列2>…)]VALUES(<常量1>[，<常量2>]…)功能将新元组插入指定表中。

195插入单个元组（续）[例1]将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。INSERTINTOStudentVALUES('95020'，'陈冬'，'男'，'IS'，18)；

196插入单个元组（续）[例2]插入一条选课记录('95020'，'1')。INSERTINTOSC(Sno，Cno)VALUES('95020'，'1')；新插入的记录在Grade列上取空值

197插入单个元组（续）INTO子句指定要插入数据的表名及属性列属性列的顺序可与表定义中的顺序不一致没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致指定部分属性列：插入的元组在其余属性列上取空值VALUES子句提供的值必须与INTO子句匹配值的个数值的类型

1982.插入子查询结果语句格式INSERTINTO<表名>[(<属性列1>[，<属性列2>…)]子查询；功能将子查询结果插入指定表中

199插入子查询结果（续）[例3]对每一个系，求学生的平均年龄，并把结果存入数据库。第一步：建表CREATETABLEDeptage(SdeptCHAR(15)/*系名*/AvgageINT)；/*学生平均年龄*/

200插入子查询结果（续）第二步：插入数据INSERTINTODeptage(Sdept，Avgage)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；

201插入子查询结果（续）INTO子句(与插入单条元组类似)指定要插入数据的表名及属性列属性列的顺序可与表定义中的顺序不一致没有指定属性列：表示要插入的是一条完整的元组指定部分属性列：插入的元组在其余属性列上取空值子查询SELECT子句目标列必须与INTO子句匹配值的个数值的类型

202插入子查询结果（续）DBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则实体完整性参照完整性用户定义的完整性对于有NOTNULL约束的属性列是否提供了非空值对于有UNIQUE约束的属性列是否提供了非重复值对于有值域约束的属性列所提供的属性值是否在值域范围内

2033.5数据更新3.5.1插入数据3.5.2修改数据3.5.3删除数据

2043.5.2修改数据语句格式UPDATE<表名>SET<列名>=<表达式>[，<列名>=<表达式>]…[WHERE<条件>]；功能修改指定表中满足WHERE子句条件的元组

205修改数据（续）三种修改方式修改某一个元组的值修改多个元组的值带子查询的修改语句

2061.修改某一个元组的值[例4]将学生95001的年龄改为22岁。UPDATEStudentSETSage=22WHERESno='95001'；

2072.修改多个元组的值[例5]将所有学生的年龄增加1岁。UPDATEStudentSETSage=Sage+1

208修改多个元组的值(续)[例6]将信息系所有学生的年龄增加1岁。UPDATEStudentSETSage=Sage+1WHERESdept='IS'；

2093.带子查询的修改语句[例7]将计算机科学系全体学生的成绩置零UPDATESCSETGrade=0WHERE'CS'=(SELECTSdeptFROMStudentWHEREStudent.Sno=SC.Sno)；

210修改数据（续）SET子句指定修改方式要修改的列修改后取值WHERE子句指定要修改的元组缺省表示要修改表中的所有元组

211修改数据（续）DBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则实体完整性参照完整性用户定义的完整性NOTNULL约束UNIQUE约束值域约束

2123.5数据更新3.5.1插入数据3.5.2修改数据3.5.3删除数据

2133.5.3删除数据DELETEFROM<表名>[WHERE<条件>]；功能删除指定表中满足WHERE子句条件的元组WHERE子句指定要删除的元组缺省表示要删除表中的所有元组

214删除数据（续）三种删除方式删除某一个元组的值删除多个元组的值带子查询的删除语句

2151.删除某一个元组的值[例8]删除学号为95003的学生记录。DELETEFROMStudentWHERESno='95003'；

2162.删除多个元组的值[例9]删除2号课程的所有选课记录。[例10]删除所有的学生选课记录。DELETEFROMSC；WHERECno='2'；DELETEFROMSC；

2173.带子查询的删除语句[例11]删除信息系所有学生的选课记录。DELETEFROMSCWHERE‘IS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；

218删除数据(续)DBMS在执行删除语句时会检查所插元组是否破坏表上已定义的完整性规则参照完整性不允许删除级联删除

219更新数据与数据一致性DBMS在执行插入、删除、更新语句时必须保证数据库一致性必须有事务的概念和原子性完整性检查和保证

220第3章关系数据库标准语言SQL3.1SQL概述3.2学生-课程数据库3.3数据定义3.4查询3.5数据更新3.6视图

2213.6视图视图的特点虚表，是从一个或几个基本表（或视图）导出的表只存放视图的定义，不会出现数据冗余基表中的数据发生变化，从视图中查询出的数据也随之改变

2223.6视图基于视图的操作查询删除受限更新定义基于该视图的新视图

2233.6视图3.6.1定义视图3.6.2查询视图3.6.3更新视图3.6.4视图的作用

2241.建立视图语句格式CREATEVIEW<视图名>[(<列名>[，<列名>]…)]AS<子查询>[WITHCHECKOPTION]；

225行列子集视图[例1]建立信息系学生的视图。CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'；从单个基本表导出只是去掉了基本表的某些行和某些列保留了码

226建立视图（续）DBMS执行CREATEVIEW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句。在对视图查询时，按视图的定义从基本表中将数据查出。

227插入子查询结果（续）[例]对每一个系，求学生的平均年龄，并把结果存入数据库。1.创建表，存入数据2.通过视图

228创建基表存数据CREATETABLEDeptage(SdeptCHAR(15)/*系名*/AvgageINT)；/*学生平均年龄*/INSERTINTODeptage(Sdept，Avgage)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；

229直接创建视图CreateviewVSA(Sdept，Avgage)asSELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；

230组成视图的属性列名全部省略或全部指定省略:由子查询中SELECT目标列中的诸字段组成明确指定视图的所有列名:某个目标列是集函数或列表达式多表连接时选出了几个同名列作为视图的字段(3)需要在视图中为某个列启用新的更合适的名字

231建立视图（续）WITHCHECKOPTION透过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）

232WITHCHECKOPTION的视图[例2]建立信息系学生的视图，并要求透过该视图进行的更新操作只涉及信息系学生。CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'WITHCHECKOPTION

233对IS_Student视图的更新操作修改操作：DBMS自动加上Sdept='IS'的条件插入操作：DBMS自动检查Sdept属性值是否为'IS'如果不是，则拒绝该插入操作删除操作：DBMS自动加上Sdept='IS'的条件

234基于多个基表的视图[例4]建立选修了1号课程的学生视图(包括学号、姓名、成绩)。CREATEVIEWIS_S1(Sno，Sname，Grade)ASSELECTStudent.Sno，Sname，GradeFROMStudent，SCWHEREStudent.Sno=SC.SnoANDSC.Cno='1'；

235基于视图的视图[例5]建立选修了1号课程且成绩在90分以上的学生的视图。CREATEVIEWIS_S2ASSELECTSno，Sname，GradeFROMIS_S1WHEREGrade>=90；

236带表达式的视图[例6]定义一个反映学生出生年份的视图（包括学号、姓名、出生年份）。CREATEVIEWBT_S(Sno，Sname，Sbirth)ASSELECTSno，Sname，2005-SageFROMStudent设置一些派生属性列,也称为虚拟列--Sbirth带表达式的视图必须明确定义组成视图的各个属性列名

237建立分组视图[例7]将学生的学号及他的平均成绩定义为一个视图CREATVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；

238建立视图（续）一类不易扩充的视图以SELECT*方式创建的视图可扩充性差，应尽可能避免

239建立视图（续）[例8]将Student表中所有女生记录定义为一个视图CREATEVIEWF_Student1(stdnum，name，sex，age，dept)ASSELECT*FROMStudentWHERESsex='女'；

240建立视图（续）缺点：修改基表Student的结构后，Student表与F_Student1视图的映象关系被破坏，导致该视图不能正确工作。

241建立视图（续）CREATEVIEWF_Student2(stdnum，name，sex，age，dept)ASSELECTSno，Sname，Ssex，Sage，SdeptFROMStudentWHERESsex='女'；为基表Student增加属性列不会破坏Student表与F_Student2视图的映象关系。

242常见的视图形式行列子集视图WITHCHECKOPTION的视图基于多个基表的视图基于视图的视图带表达式的视图分组视图

2432.删除视图DROPVIEW<视图名>；该语句从数据字典中删除指定的视图定义由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式删除删除基表时，由该基表导出的所有视图定义都必须显式删除

244删除视图(续）[例9]删除视图IS_S1DROPVIEWIS_S1；

2453.6视图3.6.1定义视图3.6.2查询视图3.6.3更新视图3.6.4视图的作用

2463.6.2查询视图从用户角度：查询视图与查询基本表相同DBMS实现视图查询的方法实体化视图（ViewMaterialization）有效性检查：检查所查询的视图是否存在执行视图定义，将视图临时实体化，生成临时表查询视图转换为查询临时表查询完毕删除被实体化的视图(临时表)

247查询视图（续）视图消解法（ViewResolution）进行有效性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询执行修正后的查询

248查询视图（续）[例1]在信息系学生的视图中找出年龄小于20岁的学生。SELECTSno，SageFROMIS_StudentWHERESage<20；IS_Student视图的定义(视图定义例1)：CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS‘；

249查询视图（续）视图实体化法视图消解法转换后的查询语句为：SELECTSno，SageFROMStudentWHERESdept='IS'ANDSage<20；

250查询视图（续）[例2]查询信息系选修了1号课程的学生SELECTSno，SnameFROMIS_Student，SCWHEREIS_Student.Sno=SC.SnoANDSC.Cno='1'；

251查询视图（续）视图消解法的局限有些情况下，视图消解法不能生成正确查询。采用视图消解法的DBMS会限制这类查询。

252查询视图（续）[例3]在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩SELECT*FROMS_GWHEREGavg>=90；S_G视图定义：CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；

253查询转换错误：SELECTSno，AVG(Grade)FROMSCWHEREAVG(Grade)>=90GROUPBYSno；正确：SELECTSno，AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)>=90；

2543.6视图3.6.1定义视图3.6.2查询视图3.6.3更新视图3.6.4视图的作用

2553.6.3更新视图用户角度：更新视图与更新基本表相同DBMS实现视图更新的方法视图实体化法（ViewMaterialization）视图消解法（ViewResolution）指定WITHCHECKOPTION子句后DBMS在更新视图时会进行检查，防止用户通过视图对不属于视图范围内的基本表数据进行更新

256更新视图（续）[例1]将信息系学生视图IS_Student中学号95002的学生姓名改为“刘辰”。UPDATEIS_StudentSETSname='刘辰'WHERESno='95002'；转换后的语句：UPDATEStudentSETSname='刘辰'WHERESno='95002'ANDSdept='IS'；

257更新视图（续）[例2]向信息系学生视图IS_S中插入一个新的学生记录：95029，赵新，20岁INSERTINTOIS_StudentVALUES(‘95029’，‘赵新’，20)；转换为对基本表的更新：INSERTINTOStudent(Sno，Sname，Sage，Sdept)VALUES('95029'，'赵新'，20，'IS')；

258更新视图（续）[例3]删除视图CS_S中学号为95029的记录DELETEFROMIS_StudentWHERESno='95029'；转换为对基本表的更新：DELETEFROMStudentWHERESno='95029'ANDSdept='IS'；

259更新视图的限制一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新(对两类方法均如此)例：视图S_G为不可更新视图。CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；

260更新视图（续）对于如下更新语句：UPDATES_GSETGavg=90WHERESno='95001'；无论实体化法还是消解法都无法将其转换成对基本表SC的更新

2613.6视图3.6.1定义视图3.6.2查询视图3.6.3更新视图3.6.4视图的作用

2621.视图能够简化用户的操作当视图中数据不是直接来自基本表时，定义视图能够简化用户的操作基于多张表连接形成的视图基于复杂嵌套查询的视图含导出属性的视图

2632.视图使用户能以多种角度看待同一数据视图机制能使不同用户以不同方式看待同一数据，适应数据库共享的需要

2643.视图对重构数据库提供了一定程度的逻辑独立性例：数据库逻辑结构发生改变学生关系Student(Sno，Sname，Ssex，Sage，Sdept)“垂直”地分成两个基本表：SX(Sno，Sname，Sage)SY(Sno，Ssex，Sdept)

2653.视图对重构数据库提供了一定程度的逻辑独立性通过建立一个视图Student：CREATEVIEWStudent(Sno，Sname，Ssex，Sage，Sdept)ASSELECTSX.Sno，SX.Sname，SY.Ssex，SX.Sage，SY.SdeptFROMSX，SYWHERESX.Sno=SY.Sno；使用户的外模式保持不变，从而对原Student表的查询程序不必修改

2663.视图对重构数据库提供了一定程度的逻辑独立性视图在一定程度上保证了数据的逻辑独立性视图只能在一定程度上提供数据的逻辑独立性由于对视图的更新是有条件的，因此应用程序中修改数据的语句可能仍会因基本表结构的改变而改变。对不同用户定义不同视图，使每个用户只能看到他有权看到的数据

267作业教材（第5版）130页第4、5题

268附录：主讲教师单位：厦门大学计算机科学系E-mail:个人网页：数据库实验室网站：主讲教师：林子雨林子雨，男，1978年出生，博士（毕业于北京大学），现为厦门大学计算机科学系助理教授（讲师），曾任厦门大学信息科学与技术学院院长助理、晋江市发展和改革局副局长。中国计算机学会数据库专业委员会委员，中国计算机学会信息系统专业委员会委员，荣获“2016中国大数据创新百人”称号。中国高校首个“数字教师”提出者和建设者，厦门大学数据库实验室负责人，厦门大学云计算与大数据研究中心主要建设者和骨干成员，2013年度厦门大学奖教金获得者。主要研究方向为数据库、数据仓库、数据挖掘、大数据、云计算和物联网，并以第一作者身份在《软件学报》《计算机学报》和《计算机研究与发展》等国家重点期刊以及国际学术会议上发表多篇学术论文。作为项目负责人主持的科研项目包括1项国家自然科学青年基金项目(No.61303004)、1项福建省自然科学青年基金项目(No.2013J05099)和1项中央高校基本科研业务费项目(No.2011121049)，同时，作为课题负责人完成了国家发改委城市信息化重大课题、国家物联网重大应用示范工程区域试点泉州市工作方案、2015泉州市互联网经济调研等课题。中国高校首个“数字教师”提出者和建设者，2009年至今，“数字教师”大平台累计向网络免费发布超过100万字高价值的研究和教学资料，累计网络访问量超过100万次。打造了中国高校大数据教学知名品牌，编著出版了中国高校第一本系统介绍大数据知识的专业教材《大数据技术原理与应用》，并成为京东、当当网等网店畅销书籍；建设了国内高校首个大数据课程公共服务平台，为教师教学和学生学习大数据课程提供全方位、一站式服务，年访问量超过50万次。具有丰富的政府和企业信息化培训经验，厦门大学管理学院EDP中心、浙江大学管理学院EDP中心、厦门大学继续教育学院、泉州市科技培训中心特邀培训讲师，曾给中国移动通信集团公司、福州马尾区政府、福建龙岩卷烟厂、福建省物联网科学研究院、石狮市物流协会、厦门市物流协会、浙江省中小企业家、四川泸州企业家、江苏沛县企业家等开展信息化培训，累计培训人数达3000人以上。扫一扫访问个人主页

269附录：课程助教单位：厦门大学计算机科学系数据库实验室2016级硕士研究生E-mail:助教：魏亮单位：厦门大学计算机科学系数据库实验室2016级硕士研究生E-mail:助教：曾冠华

270附录：班级网站林子雨主讲《数据库系统原理》2017班级主页扫一扫访问班级网站支持手机浏览

271实时主动数据仓库相关问题研究DepartmentofComputerScience,XiamenUniversity,2017