《数据库系统概论第四版王珊Part》由会员分享，可在线阅读，更多相关《数据库系统概论第四版王珊Part（472页珍藏版）》请在金锄头文库上搜索。
1、数据库系统概论数据库系统概论An Introduction to Database System第三章第三章 关系数据库标准语言关系数据库标准语言SQLSQL第三章第三章关系数据库标准语言关系数据库标准语言SQL3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.6数据控制3.7嵌入式SQL3.8小结SQLSQL的产生与发展的产生与发展n1970年，美国IBM研究中心的E.F.Codd连续发表多篇论文，提出关系模型。n1972年，IBM公司开始研制实验型关系数据库管理系统SYSTEM R，配制的查询语言称为SQUARE (Specifying Queries As Relati
2、onal Expression )语言，在语言中使用了较多的数学符号。n1974年，Boyce和Chamberlin把SQUARE修改为SEQUEL (Structured English QUEry Language )语言。后来SEQUEL简称为SQL (Structured Query Language )，即“结构式查询语言”，SQL的发音仍为“sequel”。现在SQL已经成为一个标准 。SQLSQL数据库的体系结构数据库的体系结构用户用户1用户用户2用户用户3用户用户4视图视图1视图视图1基本表基本表1基本表基本表2基本表基本表3基本表基本表4存储文件存储文件1存储文件存储文件2存
3、储文件存储文件3存储文件存储文件4图图3.1 3.1 SQLSQL数数据据库库的的体体系系结结构构SQL用户用户ViewBase tableStored file3.1SQL概述概述nSQL的特点n1.综合统一n2.高度非过程化n3.面向集合的操作方式n4.以同一种语法结构提供两种使用方法n5.语言简洁，易学易用n1.综合统一:nSQL语言综合：数据定义语言DDL、数据操纵语言DML、数据控制语言DCLn2、高度非过程化n“怎么做”n“做什么”n减轻了用户的负担，有利于提高数据独立性n3、面向集合的操作方式n非关系数据模型：面向记录nSQL：集合操作n4、以同一种语法结构提供两种使用方式n既可
4、联机交互操作，n也可以嵌入到高级语言中去5.语言简捷，易学易用语言简捷，易学易用第三章第三章关系数据库标准语言关系数据库标准语言SQL3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.6数据控制3.7嵌入式SQL3.8小结SQLSQL模式（数据库）的创建和撤销模式（数据库）的创建和撤销nSQL模式的创建可用CREATE SCHEMA语句定义，其基本句法如下： CREATE SCHEMA模式名AUTHORIZATION用户名 n删除：DROP语句的句法如下：DROP SCHEMA 模 式 名 CASCADERESTRICTn其方式有两种：CASCADE (级联式)方式。REST
5、RICT (约束式)方式。3.2数数据据定定义义3.2.1定义定义语句格式语句格式CREATETABLE（，）；n：所要定义的基本表的名字n：组成该表的各个属性（列）n：涉及相应属性列的完整性约束条件n：涉及一个或多个属性列的完整性约束条件例题例题例1建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，SnameCHAR(20)UNIQUE，SsexCHAR(1)，SageINT，Sdep
6、tCHAR(15)；例题例题（续）（续）定义基本表（续）定义基本表（续）n常用完整性约束n主码约束：PRIMARYKEYn唯一性约束：UNIQUEn非空值约束：NOTNULLn参照完整性约束:FOREIGNKEYPRIMARYKEY与UNIQUE的区别？nPrimarykey在建立的时候会默认地建立此field的索引，且此primarykey可以作为作为另外的表的foreignkey，n再者primarykey跟unique得区别区别是Primarykey一定是notnull，而unique则没有此限制n例2建立一个“课程”表C，它由课程号Cno，课程名称Cname，教师Teacher组成，其
7、中Cno为主码。nCREATETABLEC(nCnoCHAR(5),nCnameCHAR(10),nTeacherCHAR(10),nPrimarykey(Cno);例题例题（续）（续）n例3建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。nCREATETABLESC(nSnoCHAR(5),nCnoCHAR(3),nGradeint,nPrimarykey(Sno,Cno),nFOREIGNKEY(Sno)REFERENCESS(Sno),nFOREIGNKEY(Cno)REFERENCESC(Cno)n);基本数据类型基本数据
8、类型n(1)数值型（DB2)nSMALLINT半字长二进制整数nINTEGER全字长二进制整数nDECIMAL(P,q）或者DEC（p,q)n压缩十进制数，共p位，其中小数点后q位nFLOAT双字长浮点数n（2)字符串型nCHARTER(n)或CHAR(n)nVARCHAR(n)n(3)时间型nDATEnTIMEn(4)位串型nBIT（n）回顾回顾CREATETABLE（，）；n：所要定义的基本表的名字n：组成该表的各个属性（列）n：涉及相应属性列的完整性约束条件n：涉及一个或多个属性列的完整性约束条件二、修改基本表二、修改基本表ALTERTABLEADD完整性约束DROPMODIFY；n：要
9、修改的基本表nADD子句：增加新列和新的完整性约束条件nDROP子句：删除指定的完整性约束条件nMODIFY子句：用于修改列名和数据类型例题例题例2向Student表增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDScomeDATE；n不论基本表中原来是否已有数据，新增加的列一律为空值。例3将年龄的数据类型改为半字长整数。ALTERTABLEStudentMODIFYSageSMALLINT；n注：修改原有的列定义有可能会破坏已有数据例题例题例4删除学生姓名必须取唯一值的约束。ALTERTABLEStudentDROPUNIQUE(Sname)；语句格式（续）语
10、句格式（续）n删除属性列直接/间接删除n把表中要保留的列及其内容复制到一个新表中n删除原表n再将新表重命名为原表名直接删除属性列:(新)例：ALTERTABLEStudentDropScome；ALTERTABLEStudentDropCOLUMNSdept;(sqlserver)3.2数数据据定定义义三、删除基本表三、删除基本表DROPTABLE;基本表删除后，数据、表上的视图、索引都删除。有的系统如ORACLE，基本表删除后，在表上的视图往往仍然保留，但无法引用。例题例题例5删除Student表DROPTABLEStudent;3.2.2建立与删除索引建立与删除索引n建立索引是加快查询速度
11、的有效手段n建立索引nDBA或表的属主（即建立表的人）根据需要建立n有些DBMS自动建立以下列上的索引nPRIMARYKEYnUNIQUEn维护索引nDBMS自动完成n使用索引nDBMS自动选择是否使用索引以及使用哪些索引n在SQL86和SQL89标准中，基本表没有关键码概念，可以用索引机制来弥补。n索引属于物理存储的路径概念，而不是逻辑的概念。n在定义基本表时，还要定义索引，就把数据库的物理结构和逻辑结构混在一块了。n因此在SQL2中引入了主码（主键）的概念，用户在创建基本表时用主码子句Primarykey直接定义主码。n但至今大多数DBMS仍使用索引机制，有索引创建和撤销语句，其功能仅限于
12、查询时起作用。一、建立索引一、建立索引n语句格式CREATEUNIQUECLUSTERINDEXON(,)；n用指定要建索引的基本表名字n索引可以建立在该表的一列或多列上，各列名之间用逗号分隔n用指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCnUNIQUE表明此索引的每一个索引值只对应唯一的数据记录nCLUSTER表示要建立的索引是聚簇索引nCREATEUNIQUECLUSTERINDEXON(,)；例题例题例6为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号
13、升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；建立索引建立索引（续）（续）n唯一值索引n对于已含重复值的属性列不能建UNIQUE索引n对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束建立索引建立索引（续）（续）n聚簇索引n建建立立聚聚簇簇索索引引后后，基基表表中中数数据据也也需需要要按按指指定定的的聚聚簇
14、簇属属性性值值的的升升序序或或降降序序存存放放。也也即即聚聚簇簇索索引引的的索索引引项项顺顺序序与与表表中中记记录录的的物物理理顺序一致顺序一致例：CREATECLUSTERINDEXStusnameONStudent(Sname)；在在Student表的表的Sname（姓名）列上建立一个聚簇索引，而（姓名）列上建立一个聚簇索引，而且且Student表中的记录将按照表中的记录将按照Sname值的升序存放值的升序存放建立索引建立索引（续）（续）n在一个基本表上最多只能建立一个聚簇索引n聚簇索引的用途：对于某些类型的查询，可以提高查询效率n聚簇索引的适用范围n很少对基表进行增删操作n很少对其中的变
15、长列进行修改操作二、删除索引二、删除索引DROPINDEX；n删除索引时，系统会从数据字典中删去有关该索引的描述。例7删除Student表的Stusname索引。DROPINDEXStusname；索引一经建立，就由系统来维护它，不需要用户干预。建立索引减少查询操作的时间，但如果数据增加删改频繁，系统会花费许多时间来维护索引。所以，这时需要删除一些不必要的索引。3.3查查询询3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4嵌套查询3.3.5集合查询3.3.6小结3.3.1概述概述n语句格式SELECTALL|DISTINCT，FROM，WHEREGROUPBYHAVINGORDER
16、BYASC|DESC；语句格式语句格式nSELECT子句：指定要显示的属性列nFROM子句：指定查询对象(基本表或视图)nWHERE子句：指定查询条件nGROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。nHAVING短语：筛选出只有满足指定条件的组nORDERBY子句：对查询结果表按指定列值的升序或降序排序 SELECT SELECT查询语句查询语句(1)nSELECTFROMWHERE句型在关系代数中最常用的式子是下列表达式： A1,An(F(R1Rm) 这里R1、Rm为关系，F是公式，A1、An为属性。针 对 上 述 表 达 式 ， S
17、QL为 此 设 计 了 SELECTFROMWHERE句型：SELECT A1，AnFROM R1，RmWHERE F 这个句型是从关系代数表达式演变来的，但WHERE子句中的条件表达式F要比关系代数中公式更灵活。示例数据库示例数据库学生-课程数据库n学生表：Student(Sno，Sname，Ssex，Sage，Sdept)n课程表：Course(Cno，Cname，Cpno，Ccredit)n学生选课表：SC(Sno，Cno，Grade)5.语言简捷，易学易用语言简捷，易学易用3.3查查询询3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4嵌套查询3.3.5集合查询3.3.6小结
18、 SELECT SELECT查询语句查询语句(1)nSELECTFROMWHERE句型在关系代数中最常用的式子是下列表达式： A1,An(F(R1Rm) 这里R1、Rm为关系，F是公式，A1、An为属性。针 对 上 述 表 达 式 ， SQL为 此 设 计 了 SELECTFROMWHERE句型：SELECT A1，AnFROM R1，RmWHERE F 这个句型是从关系代数表达式演变来的，但WHERE子句中的条件表达式F要比关系代数中公式更灵活。SELECTSELECT语句完整的句法语句完整的句法(1)n语句格式SELECTALL|DISTINCT，FROM，WHEREGROUPBYHAVI
19、NGORDERBYASC|DESC；语句格式语句格式nSELECT子句：指定要显示的属性列nFROM子句：指定查询对象(基本表或视图)nWHERE子句：指定查询条件nGROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。nHAVING短语：筛选出只有满足指定条件的组nORDERBY子句：对查询结果表按指定列值的升序或降序排序示例数据库示例数据库学生-课程数据库n学生表：Student(Sno，Sname，Ssex，Sage，Sdept)n课程表：Course(Cno，Cname，Cpno，Ccredit)n学生选课表：SC(Sno，Cno，Gr
20、ade)3.3.2单表查询单表查询查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用集函数五、对查询结果分组SELECTSELECT语句完整的句法语句完整的句法n语句格式SELECTALL|DISTINCT，FROM，WHEREGROUPBYHAVINGORDERBYASC|DESC；查询指定列查询指定列例1查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；例2查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；查询全部列查询全部列例3查询全体学生的详细记录。
21、SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；3.查询经过计算的值查询经过计算的值SELECT子句的为表达式n算术表达式n字符串常量n函数:n列别名n等3.查询经过计算的值查询经过计算的值例4查全体学生的姓名及其出生年份。SELECTSname，2000-SageFROMStudent；输出结果：Sname2000-Sage-李勇1976刘晨1977王名1978张立19783.查询经过计算的值查询经过计算的值例5查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname，YearofB
22、irth:，2000-Sage，ISLOWER(Sdept)FROMStudent；例题（续）例题（续）输出结果：SnameYearofBirth:2000-SageISLOWER(Sdept)-李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is例例5.1使用列使用列别名别名改变查询结果的列标题改变查询结果的列标题SELECTSnameNAME，YearofBirth:BIRTH，2000-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROMStudent
23、；输出结果：NAMEBIRTHBIRTHDAYDEPARTMENT-李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is二、选择表中的若干元组二、选择表中的若干元组投影投影n消除取值重复的行n查询满足条件的元组1.消除取值重复的行消除取值重复的行n在SELECT子句中使用DISTINCT短语假设SC表中有下列数据SnoCnoGrade-9500119295001285950013889500229095002380ALL与与DISTINCT例6查询选修了课程的学生学号。(1)SELECT
24、SnoFROMSC;或(默认ALL)SELECTALLSnoFROMSC;结果：Sno-9500195001950019500295002例题（续）例题（续）(2)SELECTDISTINCTSnoFROMSC;结果：Sno-9500195002例题（续）例题（续）n注意DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正确的写法SELECTDISTINCTCno，GradeFROMSC;2.查询满足条件的元组查询满足条件的元组WHERE子句常用的查询条件子句常用的查询条件(1)比较大小比较
25、大小在WHERE子句的中使用比较运算符n=，=，=，!=或，!，!，n逻辑运算符NOT+比较运算符例8查询所有年龄在20岁以下的学生姓名及其年龄。SELECTSname，SageFROMStudentWHERESage=20；(2)确定范围确定范围n使用谓词BETWEENANDNOTBETWEENAND例10查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；例题（续）例题（续）例11查询年龄不在2023岁之间的学生姓名、系别和年龄。SELECTSname，Sd
26、ept，SageFROMStudentWHERESageNOTBETWEEN20AND23；(3)确定集合确定集合使用谓词IN,NOTIN：用逗号分隔的一组取值例12查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN(IS，MA，CS);(3)确定集合确定集合例13查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptNOTIN(IS，MA，CS);(4)字符串匹配字符串匹配nNOTLIKEESCAPE：指定匹
27、配模板匹配模板：固定字符串或含通配符的字符串当匹配模板为固定字符串时，可以用=运算符取代LIKE谓词用!=或运算符取代NOTLIKE谓词通配符通配符w%(百分号)代表任意长度（长度可以为0）的字符串n例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串_(下横线)代表任意单个字符n例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串ESCAPE短语：短语：n当用户要查询的字符串本身就含有%或_时，要使用ESCAPE短语对通配符进行转义。例题例题1)匹配模板为固定字符串例14查询学号为95001的学生的详细情况。SEL
28、ECT*FROMStudentWHERESnoLIKE95001；等价于：SELECT*FROMStudentWHERESno=95001；例题（续）例题（续）2)匹配模板为含通配符的字符串例15查询所有姓刘学生的姓名、学号和性别。SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE刘%；例题（续）例题（续）匹配模板为含通配符的字符串（续）例16查询姓欧阳且全名为三个汉字的学生的姓名。SELECTSnameFROMStudentWHERESnameLIKE欧阳_；例题（续）例题（续）匹配模板为含通配符的字符串（续）例17查询名字中第2个字为阳字的学生的姓名
29、和学号。SELECTSname，SnoFROMStudentWHERESnameLIKE_阳%；例题（续）例题（续）匹配模板为含通配符的字符串（续）例18查询所有不姓刘的学生姓名。SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE刘%；例题（续）例题（续）3)使用换码字符将通配符转义为普通字符例19查询DB_Design课程的课程号和学分。SELECTCno，CcreditFROMCourseWHERECnameLIKEDB_DesignESCAPE例题（续）例题（续）使用换码字符将通配符转义为普通字符(续)例20查询以DB_开头，且倒数第3个字符
30、为i的课程的详细情况。SELECT*FROMCourseWHERECnameLIKEDB_%i_ESCAPE；(5)涉及空值的查询涉及空值的查询n使用谓词ISNULL或ISNOTNULLn“ISNULL”不能用“=NULL”代替例21某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECTSno，CnoFROMSCWHEREGradeISNULL；例题例题(续续)例22查所有有成绩的学生学号和课程号。SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；(6)多重条件查询多重条件查询用逻辑运算符AND和OR来联
31、结多个查询条件nAND的优先级高于ORn可以用括号改变优先级可用来实现多种其他谓词nNOTINnNOTBETWEENAND例题例题例23查询计算机系年龄在20岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept=CSANDSage=20ANDSage=23；SELECTSELECT语句完整的句法语句完整的句法SELECTALL|DISTINCT，FROM，WHEREORDERBYASC|DESC；GROUPBYHAVING三、对查询结果排序三、对查询结果排序使用ORDERBY子句n可以按一个或多个属性列排序n升序：ASC；降序：DESC；缺省值为升序当排序列含空值
32、时nASC：排序列为空值的元组最后显示nDESC：排序列为空值的元组最先显示对查询结果排序（续）对查询结果排序（续）例24查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECTSno，GradeFROMSCWHERECno=3ORDERBYGradeDESC；查询结果查询结果SnoGrade-9501095024950079295003829501082950097595014619500255对查询结果排序（续）对查询结果排序（续）例25查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。SELECT*FROMStudentORDERBYSd
33、ept，SageDESC；四、使用集函数四、使用集函数5类主要集函数n计数COUNT（DISTINCT|ALL*）COUNT（DISTINCT|ALL）n计算总和SUM（DISTINCT|ALL）n计算平均值AVG（DISTINCT|ALL）使用集函数（续）使用集函数（续）求最大值MAX（DISTINCT|ALL）求最小值MIN（DISTINCT|ALL）DISTINCT短语：在计算时要取消指定列中的重复值ALL短语：不取消重复值ALL为缺省值使用集函数使用集函数（续）（续）例26查询学生总人数。SELECTCOUNT(*)FROMStudent；例27查询选修了课程的学生人数。SELECTC
34、OUNT(DISTINCTSno)FROMSC；注：用DISTINCT以避免重复计算学生人数使用集函数使用集函数（续）（续）例28计算1号课程的学生平均成绩。SELECTAVG(Grade)FROMSCWHERECno=1；例29查询选修1号课程的学生最高分数。SELECTMAX(Grade)FROMSCWHERCno=1；五、对查询结果分组五、对查询结果分组使用GROUPBY子句分组细化集函数的作用对象n未对查询结果分组，集函数将作用于整个查询结果n对查询结果分组后，集函数将分别作用于每个组使用使用GROUPBY子句分组子句分组例30求各个课程号及相应的选课人数。SELECTCno，COUN
35、T(Sno)FROMSCGROUPBYCno；结果CnoCOUNT(Sno)122234344433548对查询结果分组对查询结果分组（续）（续）nGROUPBY子句的作用对象是查询的中间结果表n分组方法：按指定的一列或多列值分组，值相等的为一组n使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性和集函数使用使用HAVING短语筛选最终输出结果短语筛选最终输出结果例31查询选修了3门以上课程的学生学号。SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)3；例题例题例32查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数SELECT
36、Sno,COUNT(*)FROMSCWHEREGrade=90GROUPBYSnoHAVINGCOUNT(*)=3;使用使用HAVING短语筛选最终输出结果短语筛选最终输出结果n只有满足HAVING短语指定条件的组才输出nHAVING短语与WHERE子句的区别：作用对象不同nWHERE子句作用于基表或视图，从中选择满足条件的元组。nHAVING短语作用于组，从中选择满足条件的组。SELECTSELECT语句完整的句法语句完整的句法(1)nSELECT语句完整的句法如下： SELECT 目标表的列名或列表达式序列 FROM 基本表名和（或）视图序列 WHERE 行条件表达式 GROUP BY 列
37、名序列 HAVING 组条件表达式 ORDER BY 列名 ASC|DESC ， SELECTSELECT语句完整的句法语句完整的句法(2)(2)n整个语句的执行过程如下：（1）读取FROM子句中基本表、视图的数据，执行笛卡尔积操作。（2）选取满足WHERE子句中给出的条件表达式的元组。（3）按GROUP子句中指定列的值分组，同时提取满足HAVING子句中组条件表达式的那些组。（4）按SELECT子句中给出的列名或列表达式求值输出。（5）ORDER子句对输出的目标表进行排序，按附加说明ASC升序排列，或按DESC降序排列。SELECTSELECT语句完整的句法语句完整的句法(3)n例3.11
38、对教学数据库的基本表S、SC、C中数据进行查询和计算。统计每一年龄选修课程的学生人数 SELECT AGE，COUNT（DISTINCT S.S#） FROM S，SC WHERE S.S#=SC.S# GROUP BY AGE； 由于要统计每一个年龄的学生人数，因此要把满足WHERE子句中条件的查询结果按年龄分组，在每一组中的学生年龄相同。此时的SELECT子句应对每一组分开进行操作，在每一组中，年龄只有一个值，统计的人数是这一组中的学生人数。SELECTSELECT语句完整的句法语句完整的句法(4)n求基本表S中男同学的每一年龄组（超过50人）有多少人？要求查询结果按人数升序排列，人数相同
39、按年龄降序排列。SELECT AGE，COUNT（S#）FROM SWHERE SEX=MGROUP BY AGEHAVING COUNT（*） 50ORDER BY 2，AGE DESC；返回SELECTSELECT语句中的限定语句中的限定(1)n SELECT子句中的规定 SELECT子句描述查询输出的表格结构，即输出值的列名或表达式。其形式如下：SELECT ALL|DISTINCT | *n 条件表达式中的算术比较操作 条件表达式中可出现算术比较运算符（，!），也可以用“BETWEEN AND ”比较运算符限定一个值的范围。n 列和基本表的改名操作SELECTSELECT语句中的限定语
40、句中的限定(2)n字符串的匹配操作条件表达式中字符串匹配操作符是“LIKE”。在表达式中可使用两个通配符：百分号（%）：与零个或多个字符组成的字符串匹配。下划线（_）：与单个字符匹配。n集合的并、交、差操作 当两个子查询结果的结构完全一致时，可以让这两个子查询执行并、交、差操作。并、交、差的运算符为UNION、INTERSECT和EXCEPT。 n空值的比较操作SQL中允许列值为空，空值用保留字NULL表示。SELECTSELECT语句中的限定语句中的限定(3)n集合的比较操作SQL提供SELECT语句的嵌套子查询机制。子查询是嵌套在另一个查询中的SELECT语句。 （1）集合成员资格的比较（
41、2） 集合成员的算术比较（3） 空关系的测试（4） 重复元组的测试n导出表的使用 SQL2允许在FROM子句中使用子查询。如果在FROM子句中使用了子查询，那么要给子查询的结果起个表名和相应的列名。返回下课了。下课了。休息一会儿。休息一会儿。数据库系统概论数据库系统概论An Introduction to Database System第三章第三章 关系数据库标准语言关系数据库标准语言SQLSQL3.3查查询询n3.3.1概述n3.3.2单表查询n3.3.3连接查询n3.3.4嵌套查询n3.3.5集合查询n3.3.6小结3.3.3连接查询连接查询同时涉及多个表的查询称为连接查询用来连接两个表的
42、条件称为连接条件或连接谓词一般格式：n.比较运算符：=、=、=、!=n.BETWEEN.AND.连接查询连接查询（续）（续）n连接字段n连接谓词中的列名称为连接字段n连接条件中的各连接字段类型必须是可比的，但不必是相同的连接操作的执行过程连接操作的执行过程n嵌套循环法(NESTED-LOOP)n首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。n表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组
43、。n重复上述操作，直到表1中的全部元组都处理完毕排序合并法排序合并法(SORT-MERGE)常用于=连接n首先按连接属性对表1和表2排序n对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续排序合并法排序合并法n找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续n重复上述操作，直到表1或
44、表2中的全部元组都处理完毕为止索引连接索引连接(INDEX-JOIN)n对表2按连接字段建立索引n对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组连接查询连接查询（续）（续）SQL中连接查询的主要类型n广义笛卡尔积n 等值连接(含自然连接)n 非等值连接查询n 自身连接查询n 外连接查询n 复合条件连接查询一、广义笛卡尔积一、广义笛卡尔积n不带连接谓词的连接n很少使用例：SELECTStudent.*,SC.*FROMStudent,SC二、等值与非等值连接查询二、等值与非等值连接查询等值连接、自
45、然连接、非等值连接例32查询每个学生及其选修课程的情况。SELECTStudent.*，SC.*FROMStudent，SCWHEREStudent.Sno=SC.Sno；等值连接等值连接n连接运算符为=的连接操作n.=.n任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。等值连接等值连接假设Student表、SC表分别有下列数据：Student表Sno SnameSsexSageSdept95001 李勇李勇 男男 20 CS95002 刘晨刘晨 女女 19 IS95003 王敏王敏 女女 18 MA95004 张立张立 男男 19 IS等值连
46、接等值连接SC表Sno CnoGrade95001 1 9295001 2 85950019500295002 3 2 3 88 90 80等值连接等值连接结果表Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade95001李勇男20CS9500119295001李勇男20CS9500128595001李勇男20CS9500138895002刘晨女19IS9500229095002刘晨女19IS95002380自然连接自然连接n等值连接的一种特殊情况，把目标列中重复的属性列去掉。例33对例32用自然连接完成。SELECTStudent.Sno，Sname，Ss
47、ex，Sage，Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；非等值连接查询非等值连接查询连接运算符不是=的连接操作.比较运算符：、=、=、!=.BETWEEN.AND.三、自身连接三、自身连接n一个表与其自己进行连接，称为表的自身连接n需要给表起别名以示区别n由于所有属性名都是同名属性，因此必须使用别名前缀自身连接（续）自身连接（续）例34查询每一门课的间接先修课（即先修课的先修课）SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECON
48、D.Cno；SELECT FIRST.C#,first.CNAME,SECOND.C#,second.CNAME FROM C FIRST,C SECOND WHERE FIRST.C# = SECOND.CPno;自身连接（续）自身连接（续）FIRST表（Course表）Cno CnameCpnoCcredit 1数据库数据库 5 4 2数学数学 2 3信息系统信息系统 1 4 4操作系统操作系统 6 3 5数据结构数据结构 7 4 6数据处理数据处理 2 7PASCAL语言语言 6 4自身连接（续）自身连接（续）SECOND表（Course表）Cno CnameCpnoCcredit 1数
49、据库数据库 5 4 2数学数学 2 3信息系统信息系统 1 4 4操作系统操作系统 6 3 5数据结构数据结构 7 4 6数据处理数据处理 2 7PASCAL语言语言 6 4自身连接（续）自身连接（续）查询结果 173556四、外连接（四、外连接（OuterJoin）n外连接与普通连接的区别n普通连接操作只输出满足连接条件的元组n外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出外连接（外连接（outer join）ABCabcbbfcadRBCDbcdbceadbefgSABCDabcdabcecadbABCDabcdabcecadbbbfnullABCDabcdabcec
50、adbnullefgSRRRSSABCDabcdabcecadbbbfnullnullefgSR外连接（续）外连接（续）例33查询每个学生及其选修课程的情况包括没有选修课程的学生-用外连接操作SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno(*)；外连接（续）外连接（续）结果：Student.SnoSnameSsexSageSdeptCnoGrade95001李勇男20CS19295001李勇男20CS28595001李勇男20CS38895002刘晨女19IS29095
51、002刘晨女19IS38095003王敏女18MA95004张立男19IS外连接（续）外连接（续）n在表名后面加外连接操作符(*)或(+)指定非主体表n非主体表有一“万能”的虚行，该行全部由空值组成n虚行可以和主体表中所有不满足连接条件的元组进行连接n由于虚行各列全部是空值，因此与虚行连接的结果中，来自非主体表的属性值全部是空值外连接（续）外连接（续）n左外连接n外连接符出现在连接条件的左边n右外连接n外连接符出现在连接条件的右边select S.S#,SNAME,SEX,C#from S right outer join SC on S.S# = SC.S# select S.S#,SNAM
52、E,SEX,C#from S,SCon S.S# *= SC.S# 五、复合条件连接五、复合条件连接WHERE子句中含多个连接条件时，称为复合条件连接例35查询选修2号课程且成绩在90分以上的所有学生的学号、姓名SELECTStudent.Sno,student.SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoAND/*连接谓词*/SC.Cno=2AND/*其他限定条件*/SC.Grade90；/*其他限定条件*/多表连接多表连接例36查询每个学生的学号、姓名、选修的课程名及成绩。SELECTStudent.Sno，Sname，Cname，GradeFROM
53、Student，SC，CourseWHEREStudent.Sno=SC.SnoandSC.Cno=Course.Cno；结果：Student.SnoSnameCnameGrade95001李勇数据库9295001李勇数学8595001李勇信息系统8895002刘晨数学9095002刘晨信息系统803.3查查询询n3.3.1概述n3.3.2单表查询n3.3.3连接查询n3.3.4嵌套查询n3.3.5集合查询n3.3.6小结3.3.4嵌套查询嵌套查询n嵌套查询概述n嵌套查询分类n嵌套查询求解方法n引出子查询的谓词嵌套查询嵌套查询(续续)n嵌套查询概述n一个SELECT-FROM-WHERE语句称
54、为一个查询块n将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询嵌套查询嵌套查询(续续)SELECTSname外层查询/父查询FROMStudentWHERESnoIN（SELECTSno内层查询/子查询FROMSCWHERECno=2）；嵌套查询嵌套查询(续续)n子查询的限制n不能使用ORDERBY子句n层层嵌套方式反映了SQL语言的结构化n有些嵌套查询可以用连接运算替代嵌套查询分类嵌套查询分类n不相关子查询子查询的查询条件不依赖于父查询n相关子查询子查询的查询条件依赖于父查询嵌套查询求解方法嵌套查询求解方法n不相关子查询是由里向外逐层处理。即每个子查
55、询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。嵌套查询求解方法（续）嵌套查询求解方法（续）n相关子查询n首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；n然后再取外层表的下一个元组；n重复这一过程，直至外层表全部检查完为止。引出子查询的谓词引出子查询的谓词n带有IN谓词的子查询n带有比较运算符的子查询n带有ANY或ALL谓词的子查询n带有EXISTS谓词的子查询一、带有一、带有IN谓词的子查询谓词的子查询例37查询与“刘晨”在同一个系学习的学生。此查询要求可以分步来完成确定“刘晨”所在系名SELE
56、CTSdeptFROMStudentWHERESname=刘晨；结果为：SdeptIS带有带有IN谓词的子查询（续）谓词的子查询（续）查找所有在IS系学习的学生。SELECTSno，Sname，SdeptFROMStudentWHERESdept=IS；结果为：SnoSnameSdept95001刘晨IS95004张立IS构造嵌套查询构造嵌套查询将第一步查询嵌入到第二步查询的条件中SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=刘晨)；此查询为不相关子查询。DBMS求解该查询时也是分步去
57、做的。带有带有IN谓词的子查询（续）谓词的子查询（续）用自身连接完成本查询要求SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1，StudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname=刘晨；带有带有IN谓词的子查询（续）谓词的子查询（续）父查询和子查询中的表均可以定义别名SELECTSno，Sname，SdeptFROMStudentS1WHERES1.SdeptIN(SELECTSdeptFROMStudentS2WHERES2.Sname=刘晨)；带有带有IN谓词的子查询（续）谓词的子查询（续）例38查询选修了课程名为“信
58、息系统”的学生学号和姓名SELECTSno，Sname最后在Student关系中FROMStudent取出Sno和SnameWHERESnoIN(SELECTSno然后在SC关系中找出选FROMSC修了3号课程的学生学号WHERECnoIN(SELECTCno首先在Course关系中找出“信FROMCourse息系统”的课程号，结果为3号WHERECname=信息系统);带有带有IN谓词的子查询（续）谓词的子查询（续）结果：Sno Sname - - 95001 李勇 95002 刘晨带有带有IN谓词的子查询（续）谓词的子查询（续）n用连接查询SELECTSno，SnameFROMStuden
59、t，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=信息系统；二、带有比较运算符的子查询二、带有比较运算符的子查询当能确切知道内层查询返回单值时，可用比较运算符（，=，=，!=或）。n与ANY或ALL谓词配合使用带有比较运算符的子查询（续）带有比较运算符的子查询（续）例：假设一个学生只可能在一个系学习，并且必须属于一个系，则在例37可以用=代替IN：SELECTSno，Sname，SdeptFROMStudentWHERESdept=SELECTSdeptFROMStudentWHERESname=刘晨；带
60、有比较运算符的子查询（续）带有比较运算符的子查询（续）子查询一定要跟在比较符之后错误的例子：SELECTSno，Sname，SdeptFROMStudentWHERE(SELECTSdeptFROMStudentWHERESname=刘晨)=Sdept；三、带有三、带有ANY或或ALL谓词的子查询谓词的子查询谓词语义nANY：任意一个值nALL：所有值带有带有ANY或或ALL谓词的子查询（续）谓词的子查询（续）需要配合使用比较运算符需要配合使用比较运算符ANY大于子查询结果中的某个值大于子查询结果中的某个值ALL大于子查询结果中的所有值大于子查询结果中的所有值ANY小于子查询结果中的某个值小于
61、子查询结果中的某个值=ANY大于等于子查询结果中的某个值大于等于子查询结果中的某个值=ALL大于等于子查询结果中的所有值大于等于子查询结果中的所有值=ANY小于等于子查询结果中的某个值小于等于子查询结果中的某个值=ALL小于等于子查询结果中的所有值小于等于子查询结果中的所有值=ANY等于子查询结果中的某个值等于子查询结果中的某个值=ALL等于子查询结果中的所有值（通常没有实际意义）等于子查询结果中的所有值（通常没有实际意义）!=（或（或）ANY不等于子查询结果中的某个值不等于子查询结果中的某个值!=（或（或）ALL不等于子查询结果中的任何一个值不等于子查询结果中的任何一个值带有带有ANY或或A
62、LL谓词的子查询（续）谓词的子查询（续）例39查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄SELECTSname，SageFROMStudentWHERESageANY(SELECTSageFROMStudentWHERESdept=IS)ANDSdeptIS;/*注意这是父查询块中的条件*/带有带有ANY或或ALL谓词的子查询（续）谓词的子查询（续）结果SnameSage王敏18执行过程1.DBMS执行此查询时，首先处理子查询，找出IS系中所有学生的年龄，构成一个集合(19，18)2.处理父查询，找所有不是IS系且年龄小于19或18的学生带有带有ANY或或ALL谓词的
63、子查询（续）谓词的子查询（续）ANY和ALL谓词有时可以用集函数实现ANY与ALL与集函数的对应关系 = 或或!= =ANY IN - MAXMIN= MINALL - NOT IN MINMAX= MAX带有带有ANY或或ALL谓词的子查询（续）谓词的子查询（续）用集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数原因：集函数首先把需要比较的集合计算出来（通常情况下是变小的），缩小了和父查询比较的次数带有带有ANY或或ALL谓词的子查询（续）谓词的子查询（续）例39：用集函数实现例39SELECTSname，SageFROMStudentWHERESage(
64、SELECTMAX(Sage)FROMStudentWHERESdept=IS)ANDSdeptIS;带有带有ANY或或ALL谓词的子查询（续）谓词的子查询（续）例40查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。方法一：用ALL谓词SELECTSname，SageFROMStudentWHERESageALL(SELECTSageFROMStudentWHERESdept=IS)ANDSdeptIS;查询结果为空表。带有带有ANY或或ALL谓词的子查询（续）谓词的子查询（续）方法二：用集函数SELECTSname，SageFROMStudentWHERESage(SELECTMIN(
65、Sage)FROMStudentWHERESdept=IS)ANDSdeptIS;四、带有四、带有EXISTS谓词的子查询谓词的子查询1.EXISTS谓词2.NOTEXISTS谓词3.不同形式的查询间的替换4.相关子查询的效率5.用EXISTS/NOTEXISTS实现全称量词6.用EXISTS/NOTEXISTS实现逻辑蕴函带有带有EXISTS谓词的子查询谓词的子查询(续）续）1. EXISTS谓词存在量词 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则返回真值若内层查询结果为空，则返回假值由EXISTS引出的子查询，其目
66、标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义2. NOT EXISTS谓词带有带有EXISTS谓词的子查询谓词的子查询(续）续）例41 查询所有选修了1号课程的学生姓名。用嵌套查询 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC /*相关子查询*/ WHERE Sno=Student.Sno AND Cno= 1 )； 求解过程 带有带有EXISTS谓词的子查询谓词的子查询(续）续） 思路分析： 本查询涉及Student和SC关系。 在Student中依次取每个元组的Sno值，用此值去检
67、查SC关系。 若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= 1，则取此Student.Sname送入结果关系。相关子查询相关子查询n子查询的查询条件依赖于外层父查询的某个属性值，称为相关子查询。n不能一次将子查询求解出来，然后求解父查询。n内层查询由于和外层查询有关，因此必须反复求值。相关子查询的一般处理过程相关子查询的一般处理过程n首先取外层查询中（Student）表的第1个元组，根据它与内层查询相关的属性值（Sno）处理内层查询，若Where子句返回值为真，则取此元组放入结果表；然后再取（Student）表的下一个元组；重复这一个过程，直至外层（Stud
68、ent）表全部检查完为止。带有带有EXISTS谓词的子查询谓词的子查询(续）续）n用连接运算SELECT SnameFROM Student, SCWHERE Student.Sno=SC.Sno AND SC.Cno= 1;带有带有EXISTS谓词的子查询谓词的子查询(续）续）例42 查询没有选修1号课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=1)；此例用连接运算难于实现 n SNAME(S) SNAMEE(C#=C2(S SC)带有
69、带有EXISTS谓词的子查询谓词的子查询(续）续）3. 不同形式的查询间的替换一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。 EXISTS带IN谓词、比较运算符、ANY和ALL谓词带有带有EXISTS谓词的子查询谓词的子查询(续）续）例：例37查询与“刘晨”在同一个系学习的学生。可以用带EXISTS谓词的子查询替换： SELECT Sno，Sname，Sdept FROM Student S1 WHERE EXISTS SELECT * FROM Student
70、S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = 刘晨 ；带有带有EXISTS谓词的子查询谓词的子查询(续）续）5.用EXISTS/NOT EXISTS实现全称量词(难点)nSQL语言中没有全称量词 （For all）n可以把带有全称量词的谓词转换为等价的带有存在量词的谓词： (x)P ( x( P) 带有带有EXISTS谓词的子查询谓词的子查询(续）续）例43 查询选修了全部课程的学生姓名。没有一门课是他不选修的。 SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM Course WHERE
71、 NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno）； SNAME（S （ S#,C#(SC) C#(C) ）带有带有EXISTS谓词的子查询谓词的子查询(续）续）6.用EXISTS/NOTEXISTS实现逻辑蕴函(难点)nSQL语言中没有蕴函(Implication)逻辑运算n可以利用谓词演算将逻辑蕴函谓词等价转换为：pqpq带有带有EXISTS谓词的子查询谓词的子查询(续）续）例44 查询至少选修了学生95002选修的全部课程的学生号码。解题思路：用逻辑蕴函表达：查询学号为x的学生，对所有的课程y
72、，只要95002学生选修了课程y，则x也选修了y。形式化表示：用P表示谓词 “学生95002选修了课程y”用q表示谓词 “学生x选修了课程y”则上述查询为: (y) p q 带有带有EXISTS谓词的子查询谓词的子查询(续）续）等价变换：(y)pq(y(pq)(y(pq)y(pq)变换后语义：不存在这样的课程y，学生95002选修了y，而学生x没有选。带有带有EXISTS谓词的子查询谓词的子查询(续）续）用NOTEXISTS谓词表示：SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno=95002ANDNOT
73、EXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno)；3.3查查询询3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4嵌套查询3.3.5集合查询3.3.6小结3.3.5 3.3.5 集合查询集合查询标准SQL直接支持的集合操作种类并操作(UNION)一般商用数据库支持的集合操作种类并操作(UNION)交操作(INTERSECT)差操作(MINUS)1并操作并操作n形式UNIONn参加UNION操作的各结果表的列数必须相同；对应项的数据类型也必须相同ABCa1b1c1a1b2c2a2b2c1ABCa1b2c2a1b
74、3c2a2b2c1RS并操作（续）并操作（续）例45查询计算机科学系的学生及年龄不大于19岁的学生。方法一：SELECT*FROMStudentWHERESdept=CSUNIONSELECT*FROMStudentWHERESage=19；并操作（续）并操作（续）方法二：SELECTDISTINCT*FROMStudentWHERESdept=CSORSage=19；并操作（续）并操作（续）例46查询选修了课程1或者选修了课程2的学生。方法一：SELECTSnoFROMSCWHERECno=1UNIONSELECTSnoFROMSCWHERECno=2；并操作（续）并操作（续）方法二：SEL
75、ECTDISTINCTSnoFROMSCWHERECno=1ORCno=2；并操作（续）并操作（续）例47设数据库中有一教师表Teacher(Tno,Tname,.)。查询学校中所有师生的姓名。SELECTSnameFROMStudentUNIONSELECTTnameFROMTeacher;new2交操作交操作标准SQL中没有提供集合交操作，但可用其他方法间接实现。现在已经提供。ABCa1b1c1a1b2c2a2b2c1ABCa1b2c2a1b3c2a2b2c1RS2交操作交操作例48查询计算机科学系的学生与年龄不大于19岁的学生的交集本例实际上就是查询计算机科学系中年龄不大于19岁的学生S
76、ELECT*FROMStudentWHERESdept=CSANDSage19；差操作（续）差操作（续）例52查询学生姓名与教师姓名的差集本例实际上是查询学校中未与教师同名的学生姓名SELECTDISTINCTSnameFROMStudentWHERESnameNOTIN (SELECTTnameFROMTeacher);new4.对集合操作结果的排序对集合操作结果的排序nORDERBY子句只能用于对最终查询结果排序，不能对中间结果排序n任何情况下，ORDERBY子句只能出现在最后n对集合操作结果排序时，ORDERBY子句中用数字指定排序属性new对集合操作结果的排序（续）对集合操作结果的排序
77、（续）例53错误写法SELECT*FROMStudentWHERESdept=CSORDERBYSnoUNIONSELECT*FROMStudentWHERESage=19ORDERBYSno；new对集合操作结果的排序（续）对集合操作结果的排序（续）正确写法SELECT*FROMStudentWHERESdept=CSUNIONSELECT*FROMStudentWHERESage=19ORDERBY1；new3.3.6SELECT语句的一般格式语句的一般格式SELECTALL|DISTINCT别名，别名FROM别名，别名WHEREGROUPBY，.HAVINGORDERBYASC|DESC
78、，ASC|DESC；n整条语句的含义：n根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。n如果有GROUP子句，则将结果按的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录，通常会在每组中使用集函数。如果GROUP子句带HAVING短语，则只有满足指定条件的组才输出。如果有ORDER子句，则结果表还要按的值的升序或降序排列。1、子句基本应用形式、子句基本应用形式n（1)SELECT子句的形式nSELECTSno,SnamenSELECT DISTINCT SnonSE
79、LECT *n SELECT My name is,Sname,2003-Sage,ISLOWER(Sdept) DeptnSELECT AVG(Grade),MAX(Grade),COUNT(*)(2)FROMnFROMStudentnFROM Student,SC,Course(3)WHEREnWHERESage=18n等价于WHERE NOT （Sage=19 AND Sage=22nWHERE Sdept In(IS,MA)n等价于WHERE SdeptIS OR Sdept=MAnWHERE Sdept NOT in (IS,MA)n等价于WHERE SdeptISAND Sdept
80、MAnWHERE Sname NOT LIKE _宇nWHERE Cname LIKE DB_%i_ESCAPE nWHERE Grade IS NOT Null(4)GROUPnGROUPBYCNOnGROUPBYSnoHAVINGCOUNT(*)=3(5)ORDERnORDER BY Grade DESCnORDER BY Sdept,Sage DESC2,连接查询连接查询n非等值连接（）n等值连接（）n自身连接（别名）n外连接（*）n复合条件连接：多个表的连接（多表连接）、多个条件的连接（复合条件连接）3、嵌套查询、嵌套查询n带有IN谓词的子查询n带有比较运算符的子查询（单值比较）n带有
81、ANY或ALL的子查询n带有EXISTS谓词的子查询4、集合查询、集合查询n每一个SELECT语句都能获得一个或一组元组。若要把多个SELECT语句的结果合并为一个结果，可用集合操作来完成。n集合操作用UNION（并）、INTERSECT（交）和MINUS（差）。作业作业n将上次作业的两道题目的关系代数表达式改写成SQL查询语句。数据库系统概论数据库系统概论An Introduction to Database System第三章第三章 关系数据库标准语言关系数据库标准语言SQLSQL第三章第三章关系数据库标准语言关系数据库标准语言SQL3.1SQL概述3.2数据定义3.3查询3.4数据更新3
82、.5视图3.6数据控制3.7嵌入式SQL3.8小结3.4数数据据更更新新3.4.1插入数据3.4.2修改数据3.4.3删除数据3.4.1插入数据插入数据n两种插入数据方式n插入单个元组n插入子查询结果c1b2a2c2b3a1c2b2a1CBASc1b2a21.插入单个元组插入单个元组n语句格式INSERTINTO(，)VALUES(，)n功能将新元组插入指定表中。插入单个元组（续）插入单个元组（续）例1将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。INSERTINTOStudentVALUES(95020，陈冬，男，IS，18)
83、；插入单个元组（续）插入单个元组（续）例2插入一条选课记录(95020，1)。INSERTINTOSC(Sno，Cno)VALUES(95020，1)；新插入的记录在Grade列上取空值nINTO子句n指定要插入数据的表名及属性列n属性列的顺序可与表定义中的顺序不一致n没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致n指定部分属性列：插入的元组在其余属性列上取空值nVALUES子句n提供的值必须与INTO子句匹配值的个数值的类型INSERT INTO SC(Sno，Cno) VALUES ( 95020 ， 1 )；2.插入子查询结果插入子查询结果n语句格式INS
84、ERTINTO(，)子查询；n功能将子查询结果插入指定表中插入子查询结果（续）插入子查询结果（续）例3对每一个系，求学生的平均年龄，并把结果存入数据库。第一步：建表CREATETABLEDeptage(SdeptCHAR(15)/*系名*/AvgageSMALLINT)；/*学生平均年龄*/插入子查询结果（续）插入子查询结果（续）第二步：插入数据INSERTINTODeptage(Sdept，Avgage)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；插入子查询结果（续）插入子查询结果（续）nINTO子句(与插入单条元组类似)n指定要插入数据的表名及
85、属性列n属性列的顺序可与表定义中的顺序不一致n没有指定属性列：表示要插入的是一条完整的元组n指定部分属性列：插入的元组在其余属性列上取空值n子查询nSELECT子句目标列必须与INTO子句匹配n值的个数n值的类型插入子查询结果（续）插入子查询结果（续）DBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则n实体完整性n参照完整性n用户定义的完整性nNOTNULL约束nUNIQUE约束n值域约束3.4数数据据更更新新3.4.1插入数据3.4.2修改数据3.4.3删除数据3.4.2修改数据修改数据n语句格式UPDATESET=，=WHERE；n功能修改指定表中满足WHERE子句条件的
86、元组c1b2a2c2b3a1c2b2a1CBAS修改数据（续）修改数据（续）n三种修改方式n修改某一个元组的值n修改多个元组的值n带子查询的修改语句SnoSnameSsexSageSdept95001李勇李勇男男20CS95002刘晨刘晨女女19IS95003王敏王敏女女18MA95004张立张立男男19IS Student1.修改某一个元组的值修改某一个元组的值例4将学生95001的年龄改为22岁。UPDATEStudentSETSage=22WHERESno=95001；SnoSnameSsexSageSdept95001李勇李勇男男20CS95002刘晨刘晨女女19IS95003王敏王敏
87、女女18MA95004张立张立男男19IS Student2.修改多个元组的值修改多个元组的值例5将所有学生的年龄增加1岁。UPDATEStudentSETSage=Sage+1；修改多个元组的值修改多个元组的值(续续)例6将信息系所有学生的年龄增加1岁。UPDATEStudentSETSage=Sage+1WHERESdept=IS；3.带子查询的修改语句带子查询的修改语句例7将计算机科学系全体学生的成绩置零。UPDATESCSETGrade=0WHERECS=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；修改数据（续）修改数据（续）nSET
88、子句指定修改方式要修改的列修改后取值nWHERE子句指定要修改的元组缺省表示要修改表中的所有元组SET Sage= Sage+1WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno = SC.Sno)；修改数据（续）修改数据（续）DBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则n实体完整性n用户定义的完整性nNOTNULL约束nUNIQUE约束n值域约束3.4数数据据更更新新3.4.1插入数据3.4.2修改数据3.4.3删除数据3.4.3删除数据删除数据DELETEFROMWHERE；功能w删除指定表中满足WHERE子
89、句条件的元组WHERE子句w指定要删除的元组w缺省表示要修改表中的所有元组c1b2a2c2b3a1c2b2a1CBAS删除数据（续）删除数据（续）n三种删除方式n删除某一个元组的值n删除多个元组的值n带子查询的删除语句1.删除某一个元组的值删除某一个元组的值例8删除学号为95019的学生记录。DELETEFROMStudentWHERESno=95019；SnoSnameSsexSageSdept95001李勇李勇男男20CS95002刘晨刘晨女女19IS95003王敏王敏女女18MA95004张立张立男男19IS Student2.删除多个元组的值删除多个元组的值例9删除2号课程的所有选课记
90、录。DELETEFROMSC；WHERECno=2；例10删除所有的学生选课记录。DELETEFROMSC；3.带子查询的删除语句带子查询的删除语句例11删除计算机科学系所有学生的选课记录。DELETEFROMSCWHERECS=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；删除数据删除数据(续续)DBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则参照完整性不允许删除级联删除第三章第三章关系数据库标准语言关系数据库标准语言SQL3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.6数据控制3.7嵌入式SQL3.
91、8小结SQLSQL数据库的体系结构数据库的体系结构用户用户1用户用户2用户用户3用户用户4视图视图1视图视图1基本表基本表1基本表基本表2基本表基本表3基本表基本表4存储文件存储文件1存储文件存储文件2存储文件存储文件3存储文件存储文件4图图3.1 3.1 SQLSQL数数据据库库的的体体系系结结构构SQL用户用户ViewBase tableStored file3.5视视图图视图的特点n虚表，是从一个或几个基本表（或视图）导出的表n只存放视图的定义，不会出现数据冗余n基表中的数据发生变化，从视图中查询出的数据也随之改变3.5视视图图基于视图的操作n查询n删除n更新(受限)n定义基于该视图的新
92、视图3.5视视图图3.5.1定义视图3.5.2查询视图3.5.3更新视图3.5.4视图的作用1.建立视图建立视图n语句格式CREATEVIEW(，)ASWITHCHECKOPTION；建立视图（续）建立视图（续）DBMS执行CREATEVIEW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句。在对视图查询时，按视图的定义从基本表中将数据查出。组成视图的属性列名组成视图的属性列名全部省略或全部指定n省略:由子查询中SELECT目标列中的诸字段组成n明确指定视图的所有列名:(1)某个目标列是集函数或列表达式(2)目标列为*(3)多表连接时选出了几个同名列作为视图的字段(4)需要在
93、视图中为某个列启用新的更合适的名字常见的视图形式常见的视图形式n行列子集视图nWITHCHECKOPTION的视图n基于多个基表的视图n基于视图的视图n带表达式的视图n分组视图行列子集视图行列子集视图例1建立信息系学生的视图。CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept=IS；从单个基本表导出只是去掉了基本表的某些行和某些列保留了码建立视图（续）建立视图（续）nWITHCHECKOPTION透过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）WITHCHECKOPTION的视图的视图例
94、2建立信息系学生的视图，并要求透过该视图进行的更新操作只涉及信息系学生。CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept=ISWITHCHECKOPTION；对对IS_Student视图的更新操作视图的更新操作n修改操作：DBMS自动加上Sdept=IS的条件n删除操作：DBMS自动加上Sdept=IS的条件n插入操作：DBMS自动检查Sdept属性值是否为ISn如果不是，则拒绝该插入操作n如果没有提供Sdept属性值，则自动定义Sdept为IS基于多个基表的视图基于多个基表的视图例4建立信息系选修了1号课程的学生视
95、图。CREATEVIEWIS_S1(Sno，Sname，Grade)ASSELECTStudent.Sno，Sname，GradeFROMStudent，SCWHERESdept=ISANDStudent.Sno=SC.SnoANDSC.Cno=1；基于视图的视图基于视图的视图例5建立信息系选修了1号课程且成绩在90分以上的学生的视图。CREATEVIEWIS_S2ASSELECTSno，Sname，GradeFROMIS_S1WHEREGrade=90；带表达式的视图带表达式的视图例6定义一个反映学生出生年份的视图。CREATEVIEWBT_S(Sno，Sname，Sbirth)ASSELE
96、CTSno，Sname，2000-SageFROMStudent设置一些派生属性列,也称为虚拟列-Sbirth带表达式的视图必须明确定义组成视图的各个属性列名建立分组视图建立分组视图例7将学生的学号及他的平均成绩定义为一个视图假设SC表中“成绩”列Grade为数字型CREATVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；建立视图（续）建立视图（续）n一类不易扩充的视图n以SELECT*方式创建的视图可扩充性差，应尽可能避免建立视图（续）建立视图（续）例8将Student表中所有女生记录定义为一个视图CREATEVIEWF_Stu
97、dent1(stdnum，name，sex，age，dept)ASSELECT*FROMStudentWHERESsex=女；缺点：修改基表Student的结构后，Student表与F_Student1视图的映象关系被破坏，导致该视图不能正确工作。建立视图（续）建立视图（续）CREATEVIEWF_Student2(stdnum，name，sex，age，dept)ASSELECTSno，Sname，Ssex，Sage，SdeptFROMStudentWHERESsex=女；为基表Student增加属性列不会破坏Student表与F_Student2视图的映象关系。常见的视图形式常见的视图形式
98、n行列子集视图nWITHCHECKOPTION的视图n基于多个基表的视图n基于视图的视图n带表达式的视图n分组视图2.删除视图删除视图nDROPVIEW；n该语句从数据字典中删除指定的视图定义n由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式删除n删除基表时，由该基表导出的所有视图定义都必须显式删除删除视图删除视图(续）续）例9删除视图IS_S1DROPVIEWIS_S1；3.5.2查询视图查询视图n从用户角度：查询视图与查询基本表相同nDBMS实现视图查询的方法n实体化视图（ViewMaterialization）n有效性检查：检查所查询的视图是否存在n执行视图定义，将视图临
99、时实体化，生成临时表n查询视图转换为查询临时表n查询完毕删除被实体化的视图(临时表)new查询视图（续）查询视图（续）n视图消解法（ViewResolution）n进行有效性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义n把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询n对基本表执行修正后的查询查询视图（续）查询视图（续）例1在信息系学生的视图中找出年龄小于20岁的学生。SELECTSno，SageFROMIS_StudentWHERESage20；IS_Student视图的定义(视图定义例1)：CREATEVIEWIS_StudentASSEL
100、ECTSno，Sname，SageFROMStudentWHERESdept=IS；查询视图（续）查询视图（续）n视图实体化法n视图消解法转换后的查询语句为：SELECTSno，SageFROMStudentWHERESdept=ISANDSage=90；S_G视图定义：CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；查询转换查询转换错误：SELECTSno，AVG(Grade)FROMSCWHEREAVG(Grade)=90GROUPBYSno；正确：SELECTSno，AVG(Grade)FROMSCGROUPBY
101、SnoHAVINGAVG(Grade)=90；3.5视视图图3.5.1定义视图3.5.2查询视图3.5.3更新视图3.5.4视图的作用3.5.3更新视图更新视图n用户角度：更新视图与更新基本表相同nDBMS实现视图更新的方法n视图实体化法（ViewMaterialization）n视图消解法（ViewResolution）n指定WITHCHECKOPTION子句后DBMS在更新视图时会进行检查，防止用户通过视图对不属于视图范围内的基本表数据进行更新更新视图（续）更新视图（续）例1将信息系学生视图IS_Student中学号95002的学生姓名改为“刘辰”。UPDATEIS_StudentSETS
102、name=刘辰WHERESno=95002；转换后的语句：UPDATEStudentSETSname=刘辰WHERESno=95002ANDSdept=IS；更新视图（续）更新视图（续）例2向信息系学生视图IS_S中插入一个新的学生记录：95029，赵新，20岁INSERTINTOIS_StudentVALUES(95029，赵新，20)；转换为对基本表的更新：INSERTINTOStudent(Sno，Sname，Sage，Sdept)VALUES(95029，赵新，20，IS)；更新视图（续）更新视图（续）例3删除视图CS_S中学号为95029的记录DELETEFROMIS_Student
103、WHERESno=95029；转换为对基本表的更新：DELETEFROMStudentWHERESno=95029ANDSdept=IS；更新视图的限制更新视图的限制n一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新(对两类方法均如此)例：视图S_G为不可更新视图。CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；更新视图（续）更新视图（续）对于如下更新语句：UPDATES_GSETGavg=90WHERESno=95001；无论实体化法还是消解法都无法将其转换成对基本表SC的更新实际
104、系统对视图更新的限制实际系统对视图更新的限制n允许对行列子集视图进行更新n对其他类型视图的更新不同系统有不同限制3.5视视图图3.5.1定义视图3.5.2查询视图3.5.3更新视图3.5.4视图的作用1.视图能够视图能够简化简化用户的操作用户的操作当视图中数据不是直接来自基本表时，定义视图能够简化用户的操作n基于多张表连接形成的视图n基于复杂嵌套查询的视图n含导出属性的视图2.视图使用户能以视图使用户能以多种角度多种角度看待同一数据看待同一数据n视图机制能使不同用户以不同方式看待同一数据，适应数据库共享的需要3.视图对重构数据库提供了一定程度的逻辑独立性视图对重构数据库提供了一定程度的逻辑独立
105、性例：数据库逻辑结构发生改变学生关系Student(Sno，Sname，Ssex，Sage，Sdept)“垂直”地分成两个基本表：SX(Sno，Sname，Sage)SY(Sno，Ssex，Sdept)3.视图对重构数据库提供了一定程度的逻辑独立性视图对重构数据库提供了一定程度的逻辑独立性通过建立一个视图Student：CREATEVIEWStudent(Sno，Sname，Ssex，Sage，Sdept)ASSELECTSX.Sno，SX.Sname，SY.Ssex，SX.Sage，SY.SdeptFROMSX，SYWHERESX.Sno=SY.Sno；使用户的外模式保持不变，从而对原Stu
106、dent表的查询程序不必修改3.视图对重构数据库提供了一定程度的视图对重构数据库提供了一定程度的逻辑独立性逻辑独立性n物理独立性与逻辑独立性的概念n视图在一定程度上保证了数据的逻辑独立性n视图只能在一定程度上提供数据的逻辑独立性n由于对视图的更新是有条件的，因此应用程序中修改数据的语句可能仍会因基本表结构的改变而改变。SQLSQL数据库的体系结构数据库的体系结构用户用户1用户用户2用户用户3用户用户4视图视图1视图视图1基本表基本表1基本表基本表2基本表基本表3基本表基本表4存储文件存储文件1存储文件存储文件2存储文件存储文件3存储文件存储文件4图图3.1 3.1 SQLSQL数数据据库库的的
107、体体系系结结构构SQL用户用户ViewBase tableStored file4.视图能够对机密数据提供安全保护视图能够对机密数据提供安全保护n对不同用户定义不同视图，使每个用户只能看到他有权看到的数据n通过WITHCHECKOPTION对关键数据定义操作时间限制建立视图（续）建立视图（续）例3建立1号课程的选课视图，并要求透过该视图进行的更新操作只涉及1号课程，同时对该视图的任何操作只能在工作时间进行。CREATEVIEWIS_SCASSELECTSno，Cno，GradeFROMSCWHERECno=1ANDTO_CHAR(SYSDATE,HH24)BETWEEN9AND17ANDTO_
108、CHAR(SYSDATE,D)BETWEEN2AND6WITHCHECKOPTION;new数据库系统概论数据库系统概论An Introduction to Database System第三章第三章 关系数据库标准语言关系数据库标准语言SQLSQL第三章第三章关系数据库标准语言关系数据库标准语言SQL3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.6数据控制3.7嵌入式SQL3.8小结3.6数据控制数据控制n概述n3.6.1授权n3.6.2收回权限n小结概述概述数据控制亦称为数据保护，包括数据的：n安全性控制n完整性控制n并发控制n恢复SQL语言的数据控制功能语言的数据控
109、制功能 SQL语言提供了数据控制功能，能够在一定程度上保证数据库中数据的完全性、完整性，并提供了一定的并发控制及恢复能力。1.完整性完整性n数据库的完整性是指数据库中数据的正确性与相容性。nSQL语言定义完整性约束条件CREATETABLE语句ALTERTABLE语句n码n取值唯一的列n参照完整性n其他约束条件2.并发控制并发控制n并发控制:当多个用户并发地对数据库进行操作时，对他们加以控制、协调，以保证并发操作正确执行，保持数据库的一致性。nSQL语言并发控制能力：提供事务、事务开始、事务结束、提交等概念3.恢复恢复n恢复:当发生各种类型的故障导致数据库处于不一致状态时，将数据库恢复到一致状
110、态的功能。nSQL语言恢复功能：提供事务回滚、重做等概念（UNDO、REDO）4.安全性安全性n安全性：保护数据库，防止不合法的使用所造成的数据泄露和破坏。n保证数据安全性的主要措施存取控制：控制用户只能存取他有权存取的数据规定不同用户对于不同数据对象所允许执行的操作n数据库元素类型：数据库、表、属性列、视图n权限：SELECT、INSERT、UPDATE、DELETE、ALTER、INDEX、CREATETAB、ALLPRIVILEGESn用户类型：DBA、属主、普通用户DBMS实现数据安全性保护的过程实现数据安全性保护的过程n用户或DBA把授权决定告知系统nSQL的GRANT和REVOKE
111、nDBMS把授权的结果存入数据字典n当用户提出操作请求时，DBMS根据授权定义进行检查，以决定是否执行操作请求安全性安全性(续续)n谁定义？ DBA和表的建立者（即表的属主）n如何定义？SQL语句：GRANTREVOKE3.6.1授授权权nGRANT语句的一般格式： GRANT ,. ON TO ,. WITH GRANT OPTION;n谁定义？DBA和表的建立者（即表的属主）n功能：将对指定操作对象的指定操作权限授予指定的用户。(1)操作权限操作权限(2)用户的权限用户的权限n建表（CREATETAB）的权限:属于DBAnDBA授予-普通用户n基本表或视图的属主拥有对该表或视图的一切操作权
112、限n接受权限的用户: 一个或多个具体用户 PUBLIC（全体用户）( () WITH GRANT OPTION) WITH GRANT OPTION子句子句n指定了WITH GRANT OPTION子句: 获得某种权限的用户还可以把这种权限再授予别的用户。n没有指定WITH GRANT OPTION子句: 获得某种权限的用户只能使用该权限，不能传播该权限例题例题 例1 把查询Student表权限授给用户U1GRANTSELECTONTABLEStudentTOU1;例题（续）例题（续）例2 把对Student表和Course表的全部权限授予用户U2和U3 GRANT ALL PRIVILIGE
113、S ON TABLE Student, Course TO U2, U3;例例4例题（续）例题（续）例3 把对表SC的查询权限授予所有用户 GRANT SELECT ON TABLE SC TO PUBLIC;例题（续）例题（续）例4 把查询Student表和修改学生学号的权限授给用户U4 GRANT UPDATE(Sno), SELECT ON TABLE Student TO U4;例题（续）例题（续） 例5 把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户 GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION;传播权限传
114、播权限 执行例5后，U5不仅拥有了对表SC的INSERT权限， 还可以传播此权限： GRANT INSERT ON TABLE SC TO U6 WITH GRANT OPTION; 同样，U6还可以将此权限授予U7： GRANT INSERT ON TABLE SC TO U7; 但U7不能再传播此权限。 U5- U6- U7例题（续）例题（续）例6 DBA把在数据库S_C中建立表的权限授予用户U8GRANT CREATETABON DATABASE S_C TO U8;3.6数据控制数据控制n概述n3.6.1授权n3.6.2收回权限n小结SQL收回权限的功能收回权限的功能nREVOKE语句
115、的一般格式为： REVOKE ,. ON FROM ,.;n功能：从指定用户那里收回对指定对象的指定权限例题例题例7 把用户U4修改学生学号的权限收回REVOKE UPDATE(Sno)ON TABLE Student FROM U4;例题（续）例题（续）例8 收回所有用户对表SC的查询权限REVOKE SELECT ON TABLE SC FROM PUBLIC; 例题（续）例题（续）例9 把用户U5对SC表的INSERT权限收回REVOKE INSERT ON TABLE SC FROM U5;权限的级联回收权限的级联回收系统将收回直接或间接从U5处获得的对SC表的INSERT权限:-U5
116、-U6-U7收回U5、U6、U7获得的对SC表的INSERT权限:-U5-U6-U7小结小结:SQLSQL灵活的授权机制灵活的授权机制nDBA拥有对数据库中所有对象的所有权限，并可以根据应用的需要将不同的权限授予不同的用户。n用户对自己建立的基本表和视图拥有全部的操作权限，并且可以用GRANT语句把其中某些权限授予其他用户。n被授权的用户如果有“继续授权”的许可，还可以把获得的权限再授予其他用户。n所有授予出去的权力在必要时又都可以用REVOKE语句收回。数据库系统概论数据库系统概论An Introduction to Database System第三章第三章 关系数据库标准语言关系数据库标
117、准语言SQL SQL (续续4）第三章第三章关系数据库标准语言关系数据库标准语言SQL3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.6数据控制3.7嵌入式SQL3.8小结3.7嵌嵌入入式式SQLnSQL语言提供了两种不同的使用方式：语言提供了两种不同的使用方式：n交互式交互式n嵌入式嵌入式n为什么为什么要引入嵌入式要引入嵌入式SQLnSQL语言是非过程性语言语言是非过程性语言n事务处理应用需要高级语言事务处理应用需要高级语言n这两种方式细节上有差别，在程序设计的环境这两种方式细节上有差别，在程序设计的环境下，下，SQL语句要做某些必要的扩充语句要做某些必要的扩充 宿主语言
118、十嵌入式宿主语言十嵌入式SQLSQL 预处理程序预处理程序 宿主语言十函数调用宿主语言十函数调用 SQLSQL函数定义库函数定义库 宿主语言编译程序宿主语言编译程序 目标程序目标程序 3.7.1嵌入式嵌入式SQL的一般的一般形式形式n为了区分SQL语句与主语言语句，需要:n前缀：EXECSQLn结束标志：随主语言的不同而不同n以C为主语言的嵌入式SQL语句的一般形式EXECSQL;例：EXEC SQL DROP TABLE Student;嵌入嵌入SQL语句语句说明性语句嵌入SQL语句数据定义可执行语句数据控制数据操纵n允许出现可执行的高级语言语句的地方，都可以写可执行SQL语句n允许出现说明
119、语句的地方，都可以写说明性SQL语句3.7.2嵌入式嵌入式SQL语句与主语言之间的语句与主语言之间的通信通信将SQL嵌入到高级语言中混合编程，程序中会含有两种不同计算模型的语句nSQL语句n描述性的面向集合的语句n负责操纵数据库n高级语言语句n过程性的面向记录的语句n负责控制程序流程工作单元之间的通信方式工作单元之间的通信方式1.SQL通信区通信区向主语言传递向主语言传递SQL语句的执行语句的执行状态状态信息信息主语言能够据此控制程序流程主语言能够据此控制程序流程2.主变量主变量1）主语言向）主语言向SQL语句提供语句提供参数参数2）将）将SQL语句查询数据库的结果交主语言进一步语句查询数据库
120、的结果交主语言进一步处理处理3.游标游标解决解决集合集合性操作语言与性操作语言与过程性过程性操作语言的不匹配操作语言的不匹配1.SQL通信区通信区nSQLCA：SQLCommunicationAreanSQLCA是一个数据结构是一个数据结构nSQLCA的用途的用途nDBMSSQL通信区通信区SQLCA应用程序信息应用程序信息SQLCA的内容的内容n与所执行的SQL语句有关n与该SQL语句的执行情况有关SQLCA的使用方法的使用方法n定义SQLCAn用EXECSQLINCLUDESQLCA加以定义n使用SQLCAnSQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE2.主变量主
121、变量n什么是主变量n嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据n在SQL语句中使用的主语言程序变量简称为主变量主变量（续）主变量（续）n主变量的类型n输入输入主变量n由应用程序对其赋值，SQL语句引用n输出输出主变量n由SQL语句赋值或设置状态信息，返回给应用程序n一个主变量有可能既是输入主变量又是输出主变量主变量（续）主变量（续）n主变量的用途n输入主变量n指定向数据库中插入的数据n将数据库中的数据修改为指定值n指定执行的操作n指定WHERE子句或HAVING子句中的条件n输出主变量n获取SQL语句的结果数据n获取SQL语句的执行状态主变量（续）主变量（续）n指示变量n一个主
122、变量可以附带一个指示变量（IndicatorVariable）主变量（续）主变量（续）n在SQL语句中使用主变量和指示变量的方法n1)说明主变量和指示变量BEGINDECLARESECTION.(说明主变量和指示变量).ENDDECLARESECTION主变量（续）主变量（续）n2)使用主变量n说明之后的主变量可以在SQL语句中任何一个能够使用表达式的地方出现n为了与数据库对象名（表名、视图名、列名等）区别，SQL语句中的主变量名前要加冒号（:）作为标志3) 使用指示变量 指示变量前也必须加冒号标志, 必须紧跟在所指主变量之后主变量（续）主变量（续）n在SQL语句之外(主语言语句中)使用主变量
123、和指示变量的方法n可以直接引用，不必加冒号3.游标（游标（cursor）n为什么要使用游标nSQL语言与主语言具有不同数据处理方式nSQL语言是面向集合的，一条SQL语句原则上可以产生或处理多条记录游标（续）游标（续）n主语言是面向记录的，一组主变量一次只能存放一条记录n仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求n嵌入式SQL引入了游标的概念，用来协调这两种不同的处理方式游标（续）游标（续）n什么是游标n游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果n用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理嵌入式嵌入式SQL语句与主语言之间
124、的通信语句与主语言之间的通信(续续)n小结n在嵌入式SQL中，SQL语句与主语言语句分工非常明确nSQL语句：直接与数据库打交道n主语言语句1.控制程序流程2.对SQL语句的执行结果做进一步加工处理嵌入式嵌入式SQL语句与主语言之间的通信语句与主语言之间的通信(续续)nSQL语句用主变量从主语言中接收执行参数，操纵数据库nSQL语句的执行状态由DBMS送至SQLCA中n主语言程序从SQLCA中取出状态信息，据此决定下一步操作n如果SQL语句从数据库中成功地检索出数据，则通过主变量传给主语言做进一步处理nSQL语言和主语言的不同数据处理方式通过游标来协调嵌入式嵌入式SQL语句与主语言之间的通信语
125、句与主语言之间的通信(续续)例：带有嵌入式SQL的一小段C程序.EXECSQLINCLUDESQLCA;/*(1)定义SQL通信区*/EXECSQLBEGINDECLARESECTION;/*(2)说明主变量*/CHARtitle_id(7);CHARtitle(81);INTroyalty;EXECSQLENDDECLARESECTION;嵌入式嵌入式SQL语句与主语言之间的通信语句与主语言之间的通信(续续)main()EXECSQLDECLAREC1CURSORFORSELECTtit_id,tit,royFROMtitles;/*(3)游标操作（定义游标）*/*从titles表中查询ti
126、t_id,tit,roy*/EXECSQLOPENC1;/*(4)游标操作（打开游标）*/嵌入式嵌入式SQL语句与主语言之间的通信语句与主语言之间的通信(续续)for(;)EXECSQLFETCHC1INTO:title_id,:title,:royalty;/*(5)游标操作（将当前数据放入主变量并推进游标指针）*/if(sqlca.sqlcodeSUCCESS)/*(6)利用SQLCA中的状态信息决定何时退出循环*/break;printf(TitleID:%s,Royalty:%d,:title_id,:royalty);printf(Title:%s,:title);/*打印查询结果*
127、/EXECSQLCLOSEC1;/*(7)游标操作（关闭游标）*/3.7嵌嵌入入式式SQL3.7.1嵌入式SQL的一般形式3.7.2嵌入式SQL语句与主语言之间的通信3.7.3不用游标的SQL语句3.7.4使用游标的SQL语句3.7.5动态SQL简介3.7.3不用游标的不用游标的SQL语句语句n不用游标的SQL语句的种类n说明性语句n数据定义语句n数据控制语句n查询结果为单记录的SELECT语句n非CURRENT形式的UPDATE语句n非CURRENT形式的DELETE语句nINSERT语句一、说明性语句一、说明性语句n说明性语句是专为在嵌入式SQL中说明主变量、SQLCA等而设置的n说明主变
128、量1.EXECSQLBEGINDECLARESECTION;2.EXECSQLENDDECLARESECTION;n这两条语句必须配对出现，相当于一个括号，两条语句中间是主变量的说明n说明SQLCA3.EXECSQLINCLUDESQLCA二、数据定义语句二、数据定义语句例1建立一个“学生”表StudentEXECSQLCREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE,SnameCHAR(20),SsexCHAR(1),SageINT,SdeptCHAR(15);三、数据控制语句三、数据控制语句例2把查询Student表权限授给用户U1EXECSQLGRAN
129、TSELECTONTABLEStudentTOU1;四、查询结果为单记录的四、查询结果为单记录的SELECT语句语句n语句格式EXECSQLSELECTALL|DISTINCT,.INTO,.FROM,.WHEREGROUPBYHAVINGORDERBYASC|DESC;查询结果为单记录的查询结果为单记录的SELECT语句（续）语句（续）n对交互式SELECT语句的扩充就是多了一个INTO子句n把从数据库中找到的符合条件的记录，放到INTO子句指出的主变量中去。查询结果为单记录的查询结果为单记录的SELECT语句（续）语句（续）n使用注意事项n1.使用主变量nINTO子句nWHERE子句的条件
130、表达式nHAVING短语的条件表达式查询结果为单记录的查询结果为单记录的SELECT语句（续）语句（续）n2.查询结果为空集n如果数据库中没有满足条件的记录，即查询结果为空，则DBMS将SQLCODE的值置为100n3.查询结果为多条记录n程序出错，DBMS会在SQLCA中返回错误信息查询结果为单记录的查询结果为单记录的SELECT语句（续）语句（续）例3根据学生号码查询学生信息。假设已将要查询的学生的学号赋给了主变量givensnoEXECSQLSELECTSno,Sname,Ssex,Sage,SdeptINTO:Hsno,:Hname,:Hsex,:Hage,:HdeptFROMStud
131、entWHERESno=:givensno;nHsno,Hname,Hsex,Hage,Hdept和givensno均是主变量，并均已在前面的程序中说明过了。查询结果为单记录的查询结果为单记录的SELECT语句（续）语句（续）例4查询某个学生选修某门课程的成绩。假设已将要查询的学生的学号赋给了主变量givensno，将课程号赋给了主变量givencno。EXECSQLSELECTSno,Cno,GradeINTO:Hsno,:Hcno,:Hgrade:GradeidFROMSCWHERESno=:givensnoANDCno=:givencno;查询结果为单记录的查询结果为单记录的SELECT
132、语句（续）语句（续）n从提高应用程序的数据独立性角度考虑，SELECT语句在任何情况下都应该使用游标n对于仅返回一行结果数据的SELECT语句虽然可以不使用游标n但如果以后数据库改变了，该SELECT语句可能会返回多行数据，这时该语句就会出错五、非五、非CURRENT形式的形式的UPDATE语句语句n非CURRENT形式的UPDATE语句n使用主变量nSET子句nWHERE子句n使用指示变量nSET子句n非CURRENT形式的UPDATE语句可以操作多条元组非非CURRENT形式的形式的UPDATE语句（续）语句（续）例5将全体学生1号课程的考试成绩增加若干分。假设增加的分数已赋给主变量Rai
133、seEXECSQLUPDATESCSETGrade=Grade+:RaiseWHERECno=1;非非CURRENT形式的形式的UPDATE语句（续）语句（续）例6修改某个学生1号课程的成绩。假设该学生的学号已赋给主变量givensno，修改后的成绩已赋给主变量newgrade。EXECSQLUPDATESCSETGrade=:newgradeWHERESno=:givensno;非非CURRENT形式的形式的UPDATE语句（续）语句（续）例7将计算机系全体学生年龄置NULL值Sageid=-1;EXECSQLUPDATEStudentSETSage=:Raise:SageidWHERESd
134、ept=CS;非非CURRENT形式的形式的UPDATE语句（续）语句（续）n将指示变量Sageid赋一个负值后，无论主变量Raise为何值，DBMS都会将CS系所有记录的年龄属性置空值。它等价于：EXECSQLUPDATEStudentSETSage=NULLWHERESdept=CS;六、非六、非CURRENT形式的形式的DELETE语句语句n非CURRENT形式的DELETE语句n使用主变量nWHERE子句n非CURRENT形式的DELETE语句可以操作多条元组非非CURRENT形式的形式的DELETE语句（续）语句（续）例8某个学生退学了，现要将有关他的所有选课记录删除掉。假设该学生的
135、姓名已赋给主变量stdnameEXECSQLDELETEFROMSCWHERESno=(SELECTSnoFROMStudentWHERESname=:stdname);七、七、INSERT语句语句n非CURRENT形式的INSERT语句n使用主变量nVALUES子句n使用指示变量nVALUES子句n非CURRENT形式的INSERT语句一次只能输入一条元组INSERT语句（续）语句（续）例9某个学生新选修了某门课程，将有关记录插入SC表假设学生的学号已赋给主变量stdno，课程号已赋给主变量couno。gradeid=-1;EXECSQLINSERTINTOSC(Sno,Cno,Grade)
136、VALUES(:stdno,:couno,:gr:gradeid);n由于该学生刚选修课程，尚未考试，因此成绩列为空。所以本例中用指示变量指示相应的主变量为空值。3.7.4使用游标的使用游标的SQL语句语句n必须使用游标的SQL语句n查询结果为多条记录的SELECT语句nCURRENT形式的UPDATE语句nCURRENT形式的DELETE语句一、一、查询结果为多条记录的查询结果为多条记录的SELECT语句语句n使用游标的步骤n1.说明游标n2.打开游标n3.移动游标指针，然后取当前记录n4.关闭游标1.说明游标说明游标n使用DECLARE语句n语句格式EXECSQLDECLARECURSOR
137、FOR;n功能n是一条说明性语句，这时DBMS并不执行SELECT指定的查询操作。2.打开游标打开游标n使用OPEN语句n语句格式EXECSQLOPEN;n功能n打开游标实际上是执行相应的SELECT语句，把所有满足查询条件的记录从指定表取到缓冲区中n这时游标处于活动状态，指针指向查询结果集中第一条记录之前3.移动游标指针，然后取当前记录移动游标指针，然后取当前记录n使用FETCH语句n语句格式EXECSQLFETCHNEXT|PRIOR|FIRST|LASTFROMINTO,.;移动游标指针，然后取当前记录（续）移动游标指针，然后取当前记录（续）n功能n指定方向推动游标指针，然后将缓冲区中的
138、当前记录取出来送至主变量供主语言进一步处理。nNEXT|PRIOR|FIRST|LAST：指定推动游标指针的方式。nNEXT：向前推进一条记录nPRIOR：向回退一条记录nFIRST：推向第一条记录nLAST：推向最后一条记录n缺省值为NEXT移动游标指针，然后取当前记录（续）移动游标指针，然后取当前记录（续）n说明n(1)主变量必须与SELECT语句中的目标列表达式具有一一对应关系n(2)FETCH语句通常用在一个循环结构中，通过循环执行FETCH语句逐条取出结果集中的行进行处理n(3)为进一步方便用户处理数据，现在一些关系数据库管理系统对FETCH语句做了扩充，允许用户向任意方向以任意步长
139、移动游标指针4.关闭游标关闭游标n使用CLOSE语句n语句格式EXECSQLCLOSE;n功能n关闭游标，释放结果集占用的缓冲区及其他资源n说明n游标被关闭后，就不再和原来的查询结果集相联系n被关闭的游标可以再次被打开，与新的查询结果相联系例题例题例1查询某个系全体学生的信息（学号、姓名、性别和年龄）。要查询的系名由用户在程序运行过程中指定，放在主变量deptname中.EXECSQLINCLUDESQLCA;EXECSQLBEGINDECLARESECTION;例题（续）例题（续）./*说明主变量deptname,HSno,HSname,HSsex,HSage等*/.EXECSQLENDDE
140、CLARESECTION;.gets(deptname);/*为主变量deptname赋值*/.例题（续）例题（续）EXECSQLDECLARESXCURSORFORSELECTSno,Sname,Ssex,SageFROMStudentWHERESDept=:deptname;/*说明游标*/EXECSQLOPENSX/*打开游标*/例题（续）例题（续）WHILE(1)/*用循环结构逐条处理结果集中的记录*/EXECSQLFETCHSXINTO:HSno,:HSname,:HSsex,:HSage;/*将游标指针向前推进一行，然后从结果集中取当前行，送相应主变量*/例题（续）例题（续）if(
141、sqlca.sqlcodeSUCCESS)break;/*若所有查询结果均已处理完或出现SQL语句错误，则退出循环*/*由主语言语句进行进一步处理*/.;例题（续）例题（续）EXECSQLCLOSESX;/*关闭游标*/.例题（续）例题（续）例2查询某些系全体学生的信息。.EXECSQLINCLUDESQLCA;EXECSQLBEGINDECLARESECTION;./*说明主变量deptname,HSno,HSname,HSsex,HSage等*/.例题（续）例题（续）EXECSQLENDDECLARESECTION;.EXECSQLDECLARESXCURSORFORSELECTSno,S
142、name,Ssex,SageFROMStudentWHERESDept=:deptname;/*说明游标*/例题（续）例题（续）WHILE(gets(deptname)!=NULL)/*接收主变量deptname的值*/*下面开始处理deptname指定系的学生信息,每次循环中deptname可具有不同的值*/EXECSQLOPENSX/*打开游标*/例题（续）例题（续）WHILE(1)/*用循环结构逐条处理结果集中的记录*/EXECSQLFETCHSXINTO:HSno,:HSname,:HSsex,:HSage;/*将游标指针向前推进一行，然后从结果集中取当前行，送相应主变量*/例题（续）
143、例题（续）if(sqlca.sqlcodeSUCCESS)break;/*若所有查询结果均已处理完或出现SQL语句错误，则退出循环*/*由主语言语句进行进一步处理*/.;/*内循环结束*/例题（续）例题（续）EXECSQLCLOSESX;/*关闭游标*/;/*外循环结束*/.二、二、CURRENT形式的形式的UPDATE语句和语句和DELETE语句语句nCURRENT形式的UPDATE语句和DELETE语句的用途n非CURRENT形式的UPDATE语句和DELETE语句n面向集合的操作n一次修改或删除所有满足条件的记录二、二、CURRENT形式的形式的UPDATE语句和语句和DELETE语句语
144、句nCURRENT形式的UPDATE语句和DELETE语句的用途（续）n如果只想修改或删除其中某个记录n用带游标的SELECT语句查出所有满足条件的记录n从中进一步找出要修改或删除的记录n用CURRENT形式的UPDATE语句和DELETE语句修改或删除之CURRENT形式的形式的UPDATE语句和语句和DELETE语句（续）语句（续）n步骤n(1)DECLAREn说明游标n(2)OPENn打开游标，把所有满足查询条件的记录从指定表取至缓冲区n(3)FETCHn推进游标指针，并把当前记录从缓冲区中取出来送至主变量CURRENT形式的形式的UPDATE语句和语句和DELETE语句（续）语句（续）
145、n(4)检查该记录是否是要修改或删除的记录，是则处理之n(5)重复第(3)和(4)步，用逐条取出结果集中的行进行判断和处理n(6)CLOSEn关闭游标，释放结果集占用的缓冲区和其他资源CURRENT形式的形式的UPDATE语句和语句和DELETE语句（续）语句（续）n说明游标n为UPDATE语句说明游标n使用带FORUPDATEOF短语的DECLARE语句n语句格式 EXECSQLDECLARECURSORFORFORUPDATEOF;nFORUPDATEOF短语用于指明检索出的数据在指定列上是可修改的，以便DBMS进行并发控制CURRENT形式的形式的UPDATE语句和语句和DELETE语句
146、（续）语句（续）n为DELETE语句说明游标n使用带FORUPDATE短语的DECLARE语句n语句格式EXECSQLDECLARECURSORFORFORUPDATE;nFORUPDATE短语提示DBMS进行并发控制CURRENT形式的形式的UPDATE语句和语句和DELETE语句（续）语句（续）n修改或删除当前记录n经检查缓冲区中记录是要修改或删除的记录，则用UPDATE语句或DELETE语句修改或删除该记录n语句格式nWHERECURRENTOFnWHERECURRENTOFnWHERECURRENTOF子句表示修改或删除的是该游标中最近一次取出的记录例题例题例3对某个系的学生信息，根据
147、用户的要求修改其中某些人的年龄字段。n思路n查询某个系全体学生的信息(要查询的系名由主变量deptname指定）n然后根据用户的要求修改其中某些记录的年龄字段例题（续）例题（续）.EXECSQLINCLUDESQLCA;EXECSQLBEGINDECLARESECTION;./*说明主变量deptname,HSno,HSname,HSsex,HSage,NEWAge等*/.例题（续）例题（续）.EXECSQLENDDECLARESECTION;.gets(deptname);/*为主变量deptname赋值*/.例题（续）例题（续）EXECSQLDECLARESXCURSORFORSELECT
148、Sno,Sname,Ssex,SageFROMStudentWHERESDept=:deptnameFORUPDATEOFSage;/*说明游标*/EXECSQLOPENSX/*打开游标*/例题（续）例题（续）WHILE(1)/*用循环结构逐条处理结果集中的记录*/EXECSQLFETCHSXINTO:HSno,:HSname,:HSsex,:HSage;/*将游标指针向前推进一行，然后从结果集中取当前行，送相应主变量*/例题（续）例题（续）if(sqlca.sqlcodeSUCCESS)break;/*若所有查询结果均已处理完或出现SQL语句错误，则退出循环*/printf(%s,%s,%s
149、,%d,Sno,Sname,Ssex,Sage);/*显示该记录*/printf(UPDATEAGE?);/*问用户是否要修改*/scanf(%c,&yn);例题（续）例题（续）if(yn=yoryn=Y)/*需要修改*/printf(INPUTNEWAGE:);scanf(%d,&NEWAge);/*输入新的年龄值*/EXECSQLUPDATEStudentSETSage=:NEWAgeWHERECURRENTOFSX;/*修改当前记录的年龄字段*/;例题（续）例题（续）.;EXECSQLCLOSESX;/*关闭游标*/.例题（续）例题（续）例4对某个系的学生信息，根据用户的要求删除其中某些
150、人的记录。.EXECSQLINCLUDESQLCA;EXECSQLBEGINDECLARESECTION;./*说明主变量deptname,HSno,HSname,HSsex,HSage等*/例题（续）例题（续）.EXECSQLENDDECLARESECTION;.gets(deptname);/*为主变量deptname赋值*/.例题（续）例题（续）EXECSQLDECLARESXCURSORFORSELECTSno,Sname,Ssex,SageFROMStudentWHERESDept=:deptnameFORUPDATE;/*说明游标*/EXECSQLOPENSX/*打开游标*/例题（
151、续）例题（续）WHILE(1)/*用循环结构逐条处理结果集中的记录*/EXECSQLFETCHSXINTO:HSno,:HSname,:HSsex,:HSage;/*将游标指针向前推进一行，然后从结果集中取当前行，送相应主变量*/例题（续）例题（续）if(sqlca.sqlcodeSUCCESS)break;/*若所有查询结果均已处理完或出现SQL语句错误，则退出循环*/printf(%s,%s,%s,%d,Sno,Sname,Ssex,Sage);/*显示该记录*/例题（续）例题（续）printf(DELETE?);/*问用户是否要删除*/scanf(%c,&yn);if(yn=yoryn=
152、Y)/*需要删除*/EXECSQLDELETEFROMStudentWHERECURRENTOFSX;/*删除当前记录*/例题（续）例题（续）.;EXECSQLCLOSESX;/*关闭游标*/.3.7嵌嵌入入式式SQL3.7.1嵌入式SQL的一般形式3.7.2嵌入式SQL语句与主语言之间的通信3.7.3不用游标的SQL语句3.7.4使用游标的SQL语句3.7.5动态SQL简介3.7.5动态动态SQL简介简介n静态嵌入式SQLn动态嵌入式SQL一、静态一、静态SQL的特点的特点n用户可以在程序运行过程中根据实际需要输入WHERE子句或HAVING子句中某些变量的值。n语句中主变量的个数与数据类型
153、在预编译时都是确定的，只有是主变量的值是程序运行过程中动态输入的。静态静态SQL的不足的不足静态SQL语句提供的编程灵活性在许多情况下仍显得不足，不能编写更为通用的程序。需求需求例，对SC：n任课教师想查选修某门课程的所有学生的学号及其成绩n班主任想查某个学生选修的所有课程的课程号及相应成绩n学生想查某个学生选修某门课程的成绩即：查询条件是不确定的，要查询的属性列也是不确定的二、动态二、动态SQL1.什么是动态嵌入式SQLn动态SQL方法允许在程序运行过程中临时“组装”SQL语句。2.应用范围n在预编译时下列信息不能确定时nSQL语句正文n主变量个数n主变量的数据类型nSQL语句中引用的数据库
154、对象（列、索引、基本表、视图等）动态动态SQL（续）（续）3.动态SQL的形式n语句可变n临时构造完整的SQL语句n条件可变nWHERE子句中的条件nHAVING短语中的条件n数据库对象、查询条件均可变nSELECT子句中的列名nFROM子句中的表名或视图名nWHERE子句中的条件nHAVING短语中的条件动态动态SQL（续）（续）4.常用动态SQL语句nEXECUTEIMMEDIATEnPREPAREnEXECUTEnDESCRIBEn使用动态SQL技术更多的是涉及程序设计方面的知识，而不是SQL语言本身第三章第三章关系数据库标准语言关系数据库标准语言SQL3.1SQL概述3.2数据定义3.
155、3查询3.4数据更新3.5视图3.6数据控制3.7嵌入式SQL3.8小结3.8小小结结SQL的特点n综合统一n2.度非过程化n3.面向集合的操作方式n4.同一种语法结构提供两种使用方式n5.语言简捷，易学易用小结（续）小结（续）交互式SQLn数据定义n查询n数据更新n数据控制小结（续）小结（续）小结（续）小结（续）嵌入式SQLn与主语言的通信方式1.SQL通信区n向主语言传递SQL语句的执行状态信息2.主变量n1）主语言向SQL语句提供参数n2）将SQL语句查询数据库的结果交主语言进一步处理3.游标n解决集合性操作语言与过程性操作语言的不匹配小结（续）小结（续）嵌入式SQL(续)n静态SQLn
156、不用游标n不需要返回结果数据的SQL语句n只返回一条结果的SQL语句n使用游标n说明游标n打开游标n推进游标并取当前记录n关闭游标n动态SQL第二、三章的复习第二、三章的复习n关系数据结构：表关系模型关系操作(关系代数、关系演算、SQL)完整性约束实体完整性、参照完整性用户定义完整性n1、关系中没有行序的原因是_.n2、关系中不允许有重复元组的原因是_n3、关系模型的基本数据结构是_n4、实体完整性是对_（键）的约束，参照完整性是对_的约束。n5、关系代数的基本操作是_。n6、等式RSRS成立的条件是_n7、关系的并、差、交操作，要求两个关系具有_。n8、一般，在关系代数运算中，当查询涉及到“
157、否定”时，就要用到_操作；当查询涉及到“全部值”时，就要用到_操作n9、在关系中，“元数”是指nA、行数B、元组个数C、关系个数D、列数n10、在关系中，“基数”是指（）A、行数B、属性个数C、关系个数D、列数n11、设关系R、S、W各有10个元组，那么这3个关系的自然连接的元组个数有A、10B、30C、1000D、不确定（与计算结果有关11、设WRS（条件为ij），且W、R、S的元组个数分别为p、m、n，那么三者之间满足A、p(m+n)B、P(mn)C、p(mn)D、P(mn)12、设关系R和S的结构相同，且各有10个元组，那么这两个关系的并操作结果的元组个数为A、10B、小于等于10C、2
158、0D、小于等于2013、设关系R和S的属性个数分别为2和3,那么RS（条件为12）等价于A、12(RS）B、14(RS）C、12(RS）D、14（R）表示（）表示（）nA、从、从R中挑选中挑选2的值大于的值大于4个分量值的元组个分量值的元组所组成的关系所组成的关系nB、从、从R中挑选第中挑选第2个分量值大于个分量值大于4的元组所的元组所构成的关系。构成的关系。nC、从、从R中挑选第中挑选第2个分量值大于个分量值大于4个分量值个分量值的元组所构成的关系的元组所构成的关系nD、 24（R）和和R相比，基数不变，元数减少相比，基数不变，元数减少n19、有关系：R（A，B，C），主键A；S（D，A），
159、主键D，外键A，参照R的属性A，关系R和S的元组如下所示。指出关系S中违反关系完整性规则的元组是（）nA、（1,2）B、（2,null）C、（3,3）D、（4,1）nRABC367257DA122null3341nSn20、自然连接是构成新关系的有效方法。自然连接是构成新关系的有效方法。一般情况下，要求一般情况下，要求R和和S含有一个或多个含有一个或多个共有的（）共有的（）nA、元组、元组B、行、行C、记录、记录D、属性、属性n21、设有属性、设有属性A，B，C，D，以下表示中不，以下表示中不是关系的是（）是关系的是（）nA、R（A）B、R（A,B，C，D）nC、R（ABCD）D、R（A，B）
160、n22、设关系设关系R（A，B，C）和）和S（B，C，D），下列各关系代数表达式不成立的），下列各关系代数表达式不成立的是（）是（）nA、A（R） D（S） B、R SnC、 B（R） B（S） D、RSn23、关系模式的任何属性（）、关系模式的任何属性（）nA、不可再分、不可再分B、可再分、可再分nC、命名在该关系模式中可以不唯一、命名在该关系模式中可以不唯一nD、以上都不是、以上都不是n24、试述等值连接和自然连接的区别。、试述等值连接和自然连接的区别。n25、在参照完整性中，外码的属性值能、在参照完整性中，外码的属性值能否为空？什么时候可以为空？否为空？什么时候可以为空？n26、设有如下
161、所示的两个关系E1和E2,其中E2是从E1中经过关系代数运算所形成的结果，试给出该运算的表达式。nE1ABC123456789DA5689nE227、nRABC367257723443ABC345723计算：计算：RUS，RS，RS，RS，3,2(S)，B5(R)，R S，RSnS 2SOME（集合）”的语义是_n7、操作“元组ALL（集合）”的语义是_n8、SQL有两种使用方式：_和_。n9、嵌入式SQL的预处理方式，是指预处理程序先对源程序进行扫描，识别出_，并处理成宿主语言的_形式。n10、为保证嵌入式SQL的实现，通常DBMS制造商提供一个_，供编译时使用。n11、SQL语句在嵌入在C
162、语言程序中时，必须加上前缀标识_和结束标志_n12、在在SQL中，用户可以直接进行查询操中，用户可以直接进行查询操作的是作的是nA、实表和虚表、实表和虚表B、基本表和实表、基本表和实表nC、视图和虚表、视图和虚表D、基本表、基本表n13、SQL中，与中，与“NOTIN”等价的操作是等价的操作是nA、SOMEB、SOMEnC、=ALLD、ALLn14、SQL中，谓词中，谓词EXISTS可用来测试可用来测试一个集合是否一个集合是否nA、有重复元组、有重复元组B、有重复的列名、有重复的列名nC、为非空集合、为非空集合D、有空值、有空值n15、基本表EMP（ENO，ENAME，SALARY，DNO）表
163、示职工的工号、姓名、工资和所在部门的编号。n基本表DEPT（DNO，DNAME）表示部门的编号和部门名。n有一SQL语句：SELECTCOUNT(DISTINCTDNO)FROMEMP;n其等价的查询语句是nA、统计职工的总人数B、统计每一部门的职工人数nC、统计职工服务的部门数目D、统计每一职工服务的部门数目EMP（ENO，ENAME，SALARY，DNO）DEPT（DNO，DNAME）16、SELECTENO,ENAMEFROMEMPWHEREDNONOTIN(SELECTDNOFROMDEPTWHEREDNAME=金工车间）；n其等价的关系代数表达式n17、UPDATEEMPSETSAL
164、ARY=SALARY*1.05WHEREDNO=D6ANDSALARY(SELECTAVG(SALARY)FROMEMP);n其等价的修改语句为A、为工资低于D6部门平均工资的所有职工加薪5%B、为工资低于整个企业平均工资的职工加薪5%C、为在D6部门工资、工资低于整个企业平均工资的职工加薪5%D、为在D6部门工资、工资低于本部门平均工资的职工加薪5%n18、有关嵌入式有关嵌入式SQL的叙述中，不正确的是的叙述中，不正确的是A、宿主语言是指、宿主语言是指C一类高级程序设计语言一类高级程序设计语言B、宿主语言是指、宿主语言是指SQL语言语言C、在程序中要区分、在程序中要区分SQL语句和宿主语言语
165、句语句和宿主语言语句D、SQL有交互式和嵌入式两种使用方式有交互式和嵌入式两种使用方式n19、允许在嵌入的允许在嵌入的SQL语句中，引用宿语句中，引用宿主语言的程序变量，在引用时主语言的程序变量，在引用时nA、直接引用、直接引用nB、这些变量前必须加符号、这些变量前必须加符号*nC、这些变量前必须加符号：、这些变量前必须加符号：nD、这些变量前必须加符号、这些变量前必须加符号&n20、如果嵌入的如果嵌入的SELECT语句的查询结语句的查询结果肯定是单元组，那么嵌入时果肯定是单元组，那么嵌入时nA、肯定不涉及游标机制、肯定不涉及游标机制nB、必须使用游标机制、必须使用游标机制nC、是否使用游标，
166、由程序员决定、是否使用游标，由程序员决定nD、是否使用游标，由、是否使用游标，由DBMS决定。决定。n21、请问请问SQL的的GRANT和和REVOKE命命令属于数据库保护中的（）控制令属于数据库保护中的（）控制nA、完整性、完整性B、安全性、安全性nC、并发控制、并发控制D、恢复机制、恢复机制n22、SQL语句中，实现数据检索的语句语句中，实现数据检索的语句是（）是（）nA、SELECTB、INSERTnC、UPDATED、FINDn23、下列下列SQL语句中，修改表结构的是（）语句中，修改表结构的是（）nA、ALTERB、CREATEnC、UPDATED、DELETEn24、在在SQL中，
167、如果希望将查询结果排序，应在中，如果希望将查询结果排序，应在SELECT语句中使用（）子句语句中使用（）子句nA、ASCB、HAVINGnC、DESCD、ORDERBYn25、在SQL语言中，创建基本表应使用（）语句nA、CREATEVIEWB,CREATESCHEMAnC.CREATEINDEXD,CREATETABLEn26、在SQL语句中，用于测试子查询是否为空的谓词是（）nA、EXISTSB.UNIQUEC.SOMED.ALLn27、在在SQL中，建立唯一索引要用到（）保中，建立唯一索引要用到（）保留字留字nA、PRIMARYB.UNIQUEnC.INDEXD.DISTINCTn28、
168、视图创建后，数据库中存放的是（）视图创建后，数据库中存放的是（）nA、查询语句、查询语句B、查询结果、查询结果nC、视图定义、视图定义D、所引用的基本表的定义、所引用的基本表的定义n29、视图是一个视图是一个“虚表虚表”，视图的构造基于，视图的构造基于（）（）nA、基本表、基本表B、视图、视图nC、基本表或视图、基本表或视图D、数据字典、数据字典n30、在视图上不能完成的操作是（）在视图上不能完成的操作是（）nA、更新视图、更新视图B、查询、查询nC、在视图上定义新的基本表、在视图上定义新的基本表D、在视图上定、在视图上定义新视图义新视图n31、在在WHERE子句的条件表达式中，可以用子句的条
169、件表达式中，可以用（）通配符与所在位置的单个字符相匹配。（）通配符与所在位置的单个字符相匹配。nA）.B）C）？）？D）_n32、向基本表中增加一个新列后，原有元组向基本表中增加一个新列后，原有元组在该列上的值是（）在该列上的值是（）nA）TrueB）FalseC）空值）空值D）不确定）不确定n33、嵌入式嵌入式SQL语句中引用主变量时，必语句中引用主变量时，必须在变量名前加（）标志。须在变量名前加（）标志。nA、逗号、逗号B、冒号、冒号C、分号、分号D、句号、句号n34、在在SELECT语句中，与关系代数中语句中，与关系代数中运算符对应的是（）子句nA、SELECTB、FROMnC、WHER
170、ED、GROUPBYn35、在SELECT语句的下列子句中，通常和HAVING子句同时使用的是（）nA、ORDERBYB、WHEREnC、GROUPBYD、均不需要n36、假定学生关系是S（S#，Sname，Sex，Age），课程关系是C（C#，Cname，TEACHER），学生选课关系是SC（S#，C#，Grade）。要查找选修“数据库系统概论”课程的“男”学生学号，将涉及关系（）nA、SB、SC，CnC、S，SCD、S，SC，Cn37、为什么将SQL的视图称为“虚表”？n38、所有的视图是否都可以更新？为什么？n39、相关子查询和不相关子查询在执行上有什么不同？n40、在嵌入式SQL中是如
171、何区分SQL语句和主语言语句的？n41、在嵌入式SQL中是如何解决数据库工作单元与源程序工作单元之间通信的？n42、试述DELETE语句与DROPTABLE语句的区别。n43、SQL的集合处理方式与宿主语言的单记录处理方式之间是如何协调的？S（S#,SNAME,AGE,SEX)，SC(S#,C#,GRADE),C(C#,CNAME,TEACHER)，n用SQL语句写出下列对S、SC、C的查询要求：n1）统计有学生选修的课程门数n2）求选修C4课程的女学生的平均成绩n3）求LIU老师所授课程的每门课的平均成绩n4）统计每门课程的学生选修人数（超过10人的课程才统计）。要求显示课程号和人数，查询结
172、果按人数降序排列，若人数相同，按课程号升序排列n5)检索学号比WANG同学大，而年龄比他小的学生姓名n6)在表SC中检索成绩为空值的学生学号和课程号n7)检索姓名以L打头的所有学生的姓名和年龄n8)求年龄大于女同学平均年龄的男生姓名和年龄n9)往关系C中插入一个课程元组（C8,VC+,BAO)n10)检索所授每门课程平均成绩均大于80分的教师姓名，并把检索到的值送往另一个已存在的表FACULTY（TNAME）n11)在SC中删除尚无成绩的选课元组n12)把MATHS课不及格的成绩全改为60分n13)在表SC中，当某个成绩低于全部课程的平均成绩时，提高5%。n设有基本表设有基本表R（A，B，C）和）和S（A，B，C），试），试用用SQL语句表示下列关系代数表达式。语句表示下列关系代数表达式。nR U S R R U S R S SnRRS RSS RSnA，B(R）B，C(S）n1,6(3=4(RS)n1,2,3(RS)n8、R C(S）下课了。下课了。休息一会儿。休息一会儿。
