《数据库系统原理第3章 关系数据库标准语言SQL》由会员分享，可在线阅读，更多相关《数据库系统原理第3章 关系数据库标准语言SQL（272页珍藏版）》请在金锄头文库上搜索。
1、数据库系统原理厦门大学计算机科学系林子雨2017版计算机科学系 2017版林子雨林子雨厦门大学厦门大学第第3章章 关系数据库标准语言关系数据库标准语言SQL（2017版）版）厦门大学计算机科学系本科生课程厦门大学计算机科学系本科生课程数据库系统原理数据库系统原理数据库系统原理厦门大学计算机科学系林子雨2017版提纲n3.1 SQL概述概述n3.2 学生学生-课程数据库课程数据库n3.3 数据定义数据定义n3.4 数据查询数据查询n3.5 数据更新数据更新n3.6 视图视图数据库系统原理厦门大学计算机科学系林子雨2017版3.1SQL概述3.1.1 SQL的产生与发展的产生与发展3.1.2 SQ
2、L的特点的特点3.1.3 SQL的基本概念的基本概念数据库系统原理厦门大学计算机科学系林子雨2017版3.1.1SQL的产生与发展1974年，由Boyce和Chamberlin提出，并在IBM公司研制的关系数据库管理系统原型SystemR上实现1986年10月，美国国家标准局的数据库委员会X3H2批准了SQL作为关系数据库语言的美国标准，并公布了SQL标准文本1987年，国际标准化组织通过这一标准数据库系统原理厦门大学计算机科学系林子雨2017版3.1.2SQL的特点SQL的特点的特点n1.综合统一n2.高度非过程化n3.面向集合的操作方式n4.以同一种语法结构提供两种使用方法n5.语言简洁，
3、易学易用数据库系统原理厦门大学计算机科学系林子雨2017版1、综合统一（操纵三级模式）SQL视图1视图2基表1基表2基表3基表4存储文件1存储文件2外模式模式内模式数据库系统原理厦门大学计算机科学系林子雨2017版5、语言简捷，易学易用表语言的动词SQL 功能动词数据定义CREATE，DROP，ALTER数据查询SELECT数据操纵数据控制GRANT，REVOKE3.1SQLINSERT，UPDATE，DELETE数据库系统原理厦门大学计算机科学系林子雨2017版第3章关系数据库标准语言SQL3.1 SQL概述概述3.2 学生学生-课程数据库课程数据库3.3 数据定义数据定义3.4 数据查询数
4、据查询3.5 数据更新数据更新3.6 视图视图数据库系统原理厦门大学计算机科学系林子雨2017版3.2学生-课程数据库学生学生-课程数据库课程数据库学生表：学生表：Student(Sno，Sname，Ssex，Sage，Sdept)课程表：课程表：Course(Cno，Cname，Cpno，Ccredit)学生选课表：学生选课表：SC(Sno，Cno，Grade) 数据库系统原理厦门大学计算机科学系林子雨2017版3.2学生-课程数据库学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19IS
5、Student(a)数据库系统原理厦门大学计算机科学系林子雨2017版3.2学生-课程数据库课程号课程名先行课学分CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64Course(b)数据库系统原理厦门大学计算机科学系林子雨2017版3.2学生-课程数据库学号课程号成绩SnoCnoGrade9500119295001285950013889500229095002380SC(c)数据库系统原理厦门大学计算机科学系林子雨2017版提纲3.1 SQL概述概述3.2 学生学生-课程数据库课程数据库3.3 数据定义数据定
6、义3.4 数据查询数据查询3.5 数据更新数据更新3.6 视图视图数据库系统原理厦门大学计算机科学系林子雨2017版3.3数据定义3.3.1 模式的定义与删除模式的定义与删除3.3.2 基本表的定义、删除与修改基本表的定义、删除与修改3.3.3 索引的建立与删除索引的建立与删除数据库系统原理厦门大学计算机科学系林子雨2017版3.3数据定义数据库系统原理厦门大学计算机科学系林子雨2017版3.3数据定义3.3.1 模式的定义与删除模式的定义与删除3.3.2 基本表的定义、删除与修改基本表的定义、删除与修改3.3.3 索引的建立与删除索引的建立与删除数据库系统原理厦门大学计算机科学系林子雨201
7、7版3.3.1模式的定义与删除一、定义模式一、定义模式 CREATE SCHEMA AUTHORIZATION Ps:1、若没有指定、若没有指定，那么，那么隐含为隐含为2、要创建模式，调用该命令的用户必须具有、要创建模式，调用该命令的用户必须具有DBA权限，或者权限，或者获得了获得了DBA授予的授予的CREATE SCHEMA的权限的权限数据库系统原理厦门大学计算机科学系林子雨2017版例题例例1 定义一个学生定义一个学生-课程模式课程模式S-TCREATE SCHEMA “S-T” AUTHORIZATION WANG;为用户为用户“WANG”定义了一个模式定义了一个模式S-T数据库系统原理
8、厦门大学计算机科学系林子雨2017版3.3.1模式的定义与删除二、删除模式二、删除模式 DROP SCHEMA 其中其中CASCADE和和RESTRICT两者必选其一两者必选其一1、CASCADE（级联），表示在删除模式同时把该模式中所（级联），表示在删除模式同时把该模式中所有的数据库对象全部一起删除有的数据库对象全部一起删除2、RESTRICT（限制），表示若该模式下已定义了下属的数（限制），表示若该模式下已定义了下属的数据库对象，则拒绝删除据库对象，则拒绝删除数据库系统原理厦门大学计算机科学系林子雨2017版例题例例4 删除模式删除模式ZHANGDROP SCHEMA ZHANG CASC
9、ADE数据库系统原理厦门大学计算机科学系林子雨2017版3.3数据定义3.3.1 模式的定义与删除模式的定义与删除3.3.2 基本表的定义、删除与修改基本表的定义、删除与修改3.3.3 索引的建立与删除索引的建立与删除数据库系统原理厦门大学计算机科学系林子雨2017版3.3.2基本表的定义、删除与修改一、定义基本表一、定义基本表 ：所要定义的基本表的名字：所要定义的基本表的名字：组成该表的各个属性（列）：组成该表的各个属性（列）：涉及相应属性列的完整性约束条件：涉及相应属性列的完整性约束条件：涉及一个或多个属性列的完整性约束条件：涉及一个或多个属性列的完整性约束条件CREATE TABLE （
10、 ， ， ）；数据库系统原理厦门大学计算机科学系林子雨2017版例题例例1 建建立立一一个个“学学生生”表表Student，它它由由学学号号Sno、姓姓名名Sname、性性别别Ssex、年年龄龄Sage、所所在在系系Sdept五五个个属属性性组组成成。其其中中学学号号是是主主键键，并并且且姓姓名取值也唯一。名取值也唯一。 CREATE TABLE Student (Sno CHAR(5) primary key, Sname CHAR(8) UNIQUE, Ssex CHAR(2) , Sage INT, Sdept CHAR(10); 数据库系统原理厦门大学计算机科学系林子雨2017版例题（
11、续）SnoSnameSsexSageSdept 字符型 字符型 字符型 整数 字符型长度为5 长度为8 长度为2 长度为10不能为空值数据库系统原理厦门大学计算机科学系林子雨2017版定义基本表（续）常用完整性约束常用完整性约束主码约束：主码约束： PRIMARY KEY唯一性约束：唯一性约束：UNIQUE非空值约束：非空值约束：NOT NULL参照完整性约束参照完整性约束PRIMARY KEY与与 UNIQUE的区别？的区别？数据库系统原理厦门大学计算机科学系林子雨2017版例题（续）例例2 建立一个建立一个“学生选课学生选课”表表SC，它，它由学号由学号Sno、课程号、课程号Cno，修课成
12、绩，修课成绩Grade组成，其中组成，其中(Sno, Cno)为主码。为主码。CREATE TABLE SC( Sno CHAR(5) , Cno CHAR(3) , Grade int, Primary key (Sno, Cno);数据库系统原理厦门大学计算机科学系林子雨2017版二、修改基本表ALTER TABLE ADD 完整性约束完整性约束 DROP ALTER COLUMN DROP COLUMN ；n：要修改的基本表nADD子句：增加新列和新的完整性约束条件nDROP子句：删除指定的完整性约束条件nALTER COLUMN子句：用于修改列名和数据类型nDROP COLUMN子句：
13、用于删除列数据库系统原理厦门大学计算机科学系林子雨2017版例题例例2 向向Student表表增增加加“入入学学时时间间”列列，其其数据类型为日期型。数据类型为日期型。 ALTER TABLE Student ADD Scome DATETIME;n不论基本表中原来是否已有数据，新增加的列一律为空值。数据库系统原理厦门大学计算机科学系林子雨2017版例题例例3 将入学日期的数据类型改为字符型。将入学日期的数据类型改为字符型。ALTER TABLE Student ALTER COLUMN Scome char(8);n注：修改原有的列定义有可能会破坏已有数据数据库系统原理厦门大学计算机科学系林
14、子雨2017版语句格式（续）删除属性列删除属性列例例4 将入学日期列删除掉。将入学日期列删除掉。 ALTER TABLE Student DROP COLUMN Scome 数据库系统原理厦门大学计算机科学系林子雨2017版三、删除基本表DROP TABLE ; 基本表删除基本表删除数据、表上的索引数据、表上的索引 自动都删除自动都删除数据库系统原理厦门大学计算机科学系林子雨2017版例题例例5 删除删除SC表表DROP TABLE SC;数据库系统原理厦门大学计算机科学系林子雨2017版3.3数据定义3.3.1 模式的定义与删除模式的定义与删除3.3.2 基本表的定义、删除与修改基本表的定义
15、、删除与修改3.3.3 索引的建立与删除索引的建立与删除数据库系统原理厦门大学计算机科学系林子雨2017版3.3.3索引的建立与删除建立索引是加快查询速度的有效手段建立索引是加快查询速度的有效手段建立索引建立索引DBA或表的属主（即建立表的人）根据需要建立或表的属主（即建立表的人）根据需要建立有些有些DBMS自动建立以下列上的索引自动建立以下列上的索引 PRIMARY KEY UNIQUE维护索引维护索引 DBMS自动完成自动完成使用索引使用索引 DBMS自动选择是否使用索引以及使用哪些索引自动选择是否使用索引以及使用哪些索引数据库系统原理厦门大学计算机科学系林子雨2017版一、建立索引语句格
16、式语句格式CREATE UNIQUE CLUSTER INDEX ON (, )；nn用指定要建索引的基本表名字n索引可以建立在该表的一列或多列上，各列名之间用逗号分隔n用指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCnUNIQUE表明此索引的每一个索引值只对应唯一的数据记录nCLUSTER表示要建立的索引是聚簇索引数据库系统原理厦门大学计算机科学系林子雨2017版建立索引（续）唯一值索引唯一值索引对于已含重复值的属性列不能建对于已含重复值的属性列不能建UNIQUE索引索引对对某某个个列列建建立立UNIQUE索索引引后后，插插入入新新记记录录时时DBMS会会自自动动检检查查
17、新新记记录录在在该该列列上上是是否否取取了了重重复复值。这相当于增加了一个值。这相当于增加了一个UNIQUE约束约束数据库系统原理厦门大学计算机科学系林子雨2017版建立索引（续）聚簇索引聚簇索引建建立立聚聚簇簇索索引引后后，基基表表中中数数据据也也需需要要按按指指定定的的聚聚簇簇属属性性值值的的升升序序或或降降序序存存放放。也也即即聚聚簇簇索索引引的索引项顺序与表中记录的物理顺序一致的索引项顺序与表中记录的物理顺序一致例：例：CREATE CLUSTER INDEX Stusname ON Student(Sname)；在在Student表的表的Sname（姓名）列上建立一个聚簇索引，而（姓
18、名）列上建立一个聚簇索引，而且且Student表中的记录将按照表中的记录将按照Sname值的升序存放值的升序存放 数据库系统原理厦门大学计算机科学系林子雨2017版建立索引（续）在一个基本表上最多只能建立一个聚簇索引在一个基本表上最多只能建立一个聚簇索引聚簇索引的用途：对于某些类型的查询，可聚簇索引的用途：对于某些类型的查询，可以提高查询效率以提高查询效率聚簇索引的适用范围聚簇索引的适用范围 很少对基表进行增删操作很少对基表进行增删操作 很少对其中的变长列进行修改操作很少对其中的变长列进行修改操作聚簇索引的不适用情形聚簇索引的不适用情形表记录太少经常插入、删除、修改的表数据分布平均的表字段数据
19、库系统原理厦门大学计算机科学系林子雨2017版建立索引（续）在下列三种情况下，有必要建立簇索引：(1)查询语句中采用该字段作为排序列(2)需要返回局部范围的大量数据(3)表格中某字段内容的重复性比较大例如，student表中dno(系号)一列有大量重复数据，当在dno列上建立了簇索引后，下面的连接查询速度会加快。数据库系统原理厦门大学计算机科学系林子雨2017版建立索引（续）多列索引和多个单列索引考虑两种不同的建立索引方式：case 1:对c1,c2,c3三列按此顺序添加一个多列索引;case 2: 对c1,c2,c3分别建立三个单列索引;问题1:按c1搜索时，哪种索引效率快?答：case2问
20、题2:按C2搜索时，哪种索引效率快?答：case2,并且，case1的索引无效问题3：按C1,C2,C3搜索哪种效率快?答：case1问题4：按C2,C3,C1搜索时哪种效率快?答：case2,因为没有按多列索引的顺序搜索，case1的索引没有使用到。覆盖查询简单的说就是所有查询列被所使用的索引覆盖的查询覆盖查询简单的说就是所有查询列被所使用的索引覆盖的查询数据库系统原理厦门大学计算机科学系林子雨2017版建立索引（续）如何去建立一个多列索引，最重要的一个问题是如何安排列的顺序是至关重要的比如需要对一个表tb里面的两个字段foo,bar建一个索引，那么索引的顺序是（foo,bar)还是(bar
21、,foo)呢假设tb表有1700条记录，foo字段有750个不同的记录，那么foo字段上的cardinality是750。总规则可以说是cardinality越大的字段应该排在索引的第一位就是说索引的位置是(foo,bar)，因为cardinality越大那么第一次取出来的记录集就越小，再进行第二次查询的次数就越少了。数据库系统原理厦门大学计算机科学系林子雨2017版建立索引（续）当在同一表格中建立簇索引和非簇索引时，先建立簇索引后建非簇索引比较好。因为如先建非簇索引的话，当建立簇索引时，SQLServer会自动将非簇索引删除，然后重新建立非簇索引。每个表仅可以有一个簇索引，最多可以有249个
22、非簇索引。它们均允许以一个或多个字段作为索引关键字(IndexKey)，但最多只能有16个字段。SQLServer只对那些能加快数据查询速度的索引才能被选用。如果利用索引检索还不如顺序扫描速度快，SQLServer仍用扫描方法检索数据。建立不能被采用的索引只会增加系统的负担，降低检索速度。因此，可利用性是建立索引的首要条件。数据库系统原理厦门大学计算机科学系林子雨2017版例题 例例6 为为学学生生-课课程程数数据据库库中中的的Student，Course，SC三三个个表表建建立立索索引引。其其中中Student表表按按学学号号升升序序建建唯唯一一索索引引，Course表表按按课课程程号号升升
23、序序建建唯唯一一索索引引，SC表表按学号升序和课程号降序建唯一索引。按学号升序和课程号降序建唯一索引。CREATE UNIQUE INDEX Stusno ON Student(Sno);CREATE UNIQUE INDEX Coucno ON Course(Cno);CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC); 数据库系统原理厦门大学计算机科学系林子雨2017版二、删除索引DROP INDEX ；删除索引时，系统会从数据字典中删去有关删除索引时，系统会从数据字典中删去有关该索引的描述。该索引的描述。例例7 删除删除Student表的表
24、的Stusname索引。索引。DROP INDEX Student.Stusname；数据库系统原理厦门大学计算机科学系林子雨2017版第3章关系数据库标准语言SQL3.1 SQL概述概述3.2 学生学生-课程数据库课程数据库3.3 数据定义数据定义3.4 数据查询数据查询3.5 数据更新数据更新3.6 视图视图数据库系统原理厦门大学计算机科学系林子雨2017版3.4查询n3.4.1 单表查询单表查询n3.4.2 连接查询连接查询n3.4.3 嵌套查询嵌套查询n3.4.4 集合查询集合查询n3.4.5 SELECT 语句的一般格式语句的一般格式 数据库系统原理厦门大学计算机科学系林子雨2017
25、版3.4查询语句格式语句格式SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ；数据库系统原理厦门大学计算机科学系林子雨2017版3.4查询SELECT子句子句：指定要显示的属性列：指定要显示的属性列FROM子句子句：指定查询对象：指定查询对象(基本表或视图基本表或视图)WHERE子句子句：指定查询条件：指定查询条件 GROUP BY子子句句：对对查查询询结结果果按按指指定定列列的的值值分分组组，该该属属性性列列值值相相等等的的元元组组为为一一个个组组。通常会在每组中作用集函数。通常会在每组中作用集函数。H
26、AVING短短语语：筛筛选选出出只只有有满满足足指指定定条条件件的的组组ORDER BY子句子句：对查询结果表按指定列：对查询结果表按指定列值的升序或降序排序值的升序或降序排序 数据库系统原理厦门大学计算机科学系林子雨2017版3.4查询n3.4.1 单表查询单表查询n3.4.2 连接查询连接查询n3.4.3 嵌套查询嵌套查询n3.4.4 集合查询集合查询n3.4.5 SELECT 语句的一般格式语句的一般格式 数据库系统原理厦门大学计算机科学系林子雨2017版3.4.1单表查询 查查询询仅仅涉涉及及一一个个表表，是是一一种种最最简简单单的的查查询询操操作作一、选择表中的若干列一、选择表中的若
27、干列二、选择表中的若干元组二、选择表中的若干元组三、对查询结果排序三、对查询结果排序四、使用集函数四、使用集函数五、对查询结果分组五、对查询结果分组 数据库系统原理厦门大学计算机科学系林子雨2017版查询指定列例例1 查询全体学生的学号与姓名。查询全体学生的学号与姓名。SELECT Sno, SnameFROM Student;例例2 查询全体学生的姓名、学号、所在系。查询全体学生的姓名、学号、所在系。SELECT Sname，Sno，SdeptFROM Student；数据库系统原理厦门大学计算机科学系林子雨2017版查询全部列例例3 查询全体学生的详细记录。查询全体学生的详细记录。SELE
28、CT Sno，Sname，Ssex，Sage，Sdept FROM Student； 或或SELECT *FROM Student； 数据库系统原理厦门大学计算机科学系林子雨2017版3.查询经过计算的值SELECT子句的子句的为表达式为表达式算术表达式算术表达式字符串常量字符串常量函数函数列别名列别名等等 数据库系统原理厦门大学计算机科学系林子雨2017版3.查询经过计算的值例例4 查全体学生的姓名及其出生年份。查全体学生的姓名及其出生年份。SELECT Sname,2011-SageFROM Student;数据库系统原理厦门大学计算机科学系林子雨2017版3.查询经过计算的值例例5 查查
29、询询全全体体学学生生的的姓姓名名、出出生生年年份份和和所所在在系系。在在出出生生年年份份前前面面增增加加一一个个说说明明，在在系系名名称称后后面面增增加加一一个个“系系”作作为为表示表示SELECT Sname, 出生年份出生年份: , 2011-Sage, Sdept + 系系FROM Student;数据库系统原理厦门大学计算机科学系林子雨2017版例5.1使用列别名改变查询结果的列标题SELECT Sname 姓姓名名, Year of Birth: 生日标识生日标识, 2011-Sage 生日生日, Sdept+系系 系名系名FROM Student;数据库系统原理厦门大学计算机科学系
30、林子雨2017版二、选择表中的若干元组消除取值重复的行消除取值重复的行查询满足条件的元组查询满足条件的元组 数据库系统原理厦门大学计算机科学系林子雨2017版1.消除取值重复的行在在SELECT子句中使用子句中使用DISTINCT短语短语假设假设SC表中有下列数据表中有下列数据 Sno Cno Grade - - - 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80数据库系统原理厦门大学计算机科学系林子雨2017版ALL与DISTINCT例例6 查询选修了课程的学生学号。查询选修了课程的学生学号。(1) SELECT Sno FROM
31、 SC;或或(默认默认 ALL) SELECT ALL Sno FROM SC;(2) SELECT DISTINCT Sno FROM SC;数据库系统原理厦门大学计算机科学系林子雨2017版例题（续）注意注意 DISTINCT短语的作用范围是所有目标列短语的作用范围是所有目标列例：查询选修课程的各种成绩例：查询选修课程的各种成绩错误的写法错误的写法SELECT DISTINCT Cno，DISTINCT GradeFROM SC;正确的写法正确的写法 SELECT DISTINCT Cno，Grade FROM SC;数据库系统原理厦门大学计算机科学系林子雨2017版2.查询满足条件的元组
32、表表3.3常用的查询条件常用的查询条件查查询询条条件件谓谓 词词比比较较=，=，=，!=，!，!；NOT + 上述比较运算符上述比较运算符确定范围确定范围BETWEENAND，NOTBETWEENAND确定集合确定集合IN，NOTIN字符匹配字符匹配LIKE，NOTLIKE空空值值ISNULL，ISNOTNULL多重条件多重条件AND，ORWHERE子句常用的查询条件子句常用的查询条件数据库系统原理厦门大学计算机科学系林子雨2017版(1)比较大小在在WHERE子句的子句的中使用比较运算符中使用比较运算符l=，=，=，!= 或或 ，!，!， l逻辑运算符逻辑运算符NOT + 比较运算符比较运算
33、符例例8 查询所有年龄在查询所有年龄在20岁以下的学生姓名及其年龄。岁以下的学生姓名及其年龄。SELECT Sname, Sage FROM Student WHERE Sage = 20； 数据库系统原理厦门大学计算机科学系林子雨2017版(2)确定范围使用谓词使用谓词 BETWEEN AND NOT BETWEEN AND 例例10 查询年龄在查询年龄在2023岁（包括岁（包括20岁和岁和23岁）岁）之间的学生的姓名、系别和年龄之间的学生的姓名、系别和年龄。 SELECT Sname, Sdept, SageFROM StudentWHERE Sage BETWEEN 20 AND 23;
34、 数据库系统原理厦门大学计算机科学系林子雨2017版例题（续）例例11 查查询询年年龄龄不不在在2023岁岁之之间间的的学学生生姓姓名名、系别和年龄。系别和年龄。SELECT Sname，Sdept，SageFROM StudentWHERE Sage NOT BETWEEN 20 AND 23；数据库系统原理厦门大学计算机科学系林子雨2017版(3)确定集合使用谓词使用谓词 IN , NOT IN ：用逗号分隔的一组取值用逗号分隔的一组取值例例12查询信息系（查询信息系（IS）和计算机科学系）和计算机科学系（CS）学生的姓名和性别。）学生的姓名和性别。SELECT Sname, SsexFR
35、OM StudentWHERE Sdept IN ( IS, CS );数据库系统原理厦门大学计算机科学系林子雨2017版(3)确定集合例例13查询既不是信息系又不是计算查询既不是信息系又不是计算 机科学系的学生的姓名和性别。机科学系的学生的姓名和性别。SELECT Sname，SsexFROM Student WHERE Sdept NOT IN ( IS，CS );数据库系统原理厦门大学计算机科学系林子雨2017版(4)字符串匹配 NOT LIKE ESCAPE ：指定匹配模板：指定匹配模板 匹配模板：固定字符串或含通配符的字符串匹配模板：固定字符串或含通配符的字符串 当匹配模板为固定字符
36、串时，当匹配模板为固定字符串时， 可以用可以用 = 运算符取代运算符取代 LIKE 谓词谓词 用用 != 或或 运算符取代运算符取代 NOT LIKE 谓词谓词数据库系统原理厦门大学计算机科学系林子雨2017版通配符w% (百分号百分号) 代表任意长度（长度可以为代表任意长度（长度可以为0）的字符串）的字符串例：例：a%b表示以表示以a开头，以开头，以b结尾的任意长度的字符结尾的任意长度的字符串。如串。如acb，addgb，ab 等都满足该匹配串等都满足该匹配串_ (下横线下横线) 代表任意单个字符代表任意单个字符例：例：a_b表示以表示以a开头，以开头，以b结尾的长度为结尾的长度为3的任意字
37、的任意字符串。如符串。如acb，afb等都满足该匹配串等都满足该匹配串数据库系统原理厦门大学计算机科学系林子雨2017版ESCAPE短语：当用户要查询的字符串本身就含有当用户要查询的字符串本身就含有 % 或或 _ 时，要使用时，要使用ESCAPE 短语对通配符进行转义。短语对通配符进行转义。数据库系统原理厦门大学计算机科学系林子雨2017版例题1) 匹配模板为固定字符串匹配模板为固定字符串 例例14 查询学号为查询学号为95001的学生的详细情况。的学生的详细情况。 SELECT * FROM Student WHERE Sno LIKE 95001;等价于：等价于： SELECT * FRO
38、M Student WHERE Sno = 95001；数据库系统原理厦门大学计算机科学系林子雨2017版例题（续）2) 匹配模板为含通配符的字符串匹配模板为含通配符的字符串例例15 查询所有姓刘学生的姓名、学号和性别。查询所有姓刘学生的姓名、学号和性别。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname LIKE 刘刘%；数据库系统原理厦门大学计算机科学系林子雨2017版例题（续）匹配模板为含通配符的字符串（续）匹配模板为含通配符的字符串（续）例例16 查询姓查询姓“刘刘且全名为三个汉字的且全名为三个汉字的学生的姓名。学生的姓名。 SELECT S
39、name FROM Student WHERE Sname LIKE 刘刘_；备注：这里有两个_数据库系统原理厦门大学计算机科学系林子雨2017版关于SQLServer2008字符集SELECTCOLLATIONPROPERTY(Chinese_PRC_Stroke_CI_AI_KS_WS,CodePage)查询结果：936简体中文GBK950繁体中文BIG5437美国/加拿大英语932日文949韩文866俄文65001unicodeUFT-8数据库字符集为ASCII时，一个汉字需要两个_；当数据库字符集为GBK时，一个汉字只需要一个_数据库系统原理厦门大学计算机科学系林子雨2017版例题（续
40、）匹配模板为含通配符的字符串（续）匹配模板为含通配符的字符串（续）例例17 查询名字中第查询名字中第2个字为个字为“敏敏”字的学生字的学生的姓名和学号。的姓名和学号。 SELECT Sname，Sno FROM Student WHERE Sname LIKE _敏敏%；数据库系统原理厦门大学计算机科学系林子雨2017版例题（续）例例18 查询所有不姓刘的学生姓名。查询所有不姓刘的学生姓名。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname NOT LIKE 刘刘%；数据库系统原理厦门大学计算机科学系林子雨2017版例题（续）3) 使用换码字符将通配
41、符转义为普通字符使用换码字符将通配符转义为普通字符 例例19 查询课程名称中包含查询课程名称中包含“面向对象面向对象_C+课程课程”的课程号和学分。的课程号和学分。 SELECT Cno，Ccredit FROM Course WHERE Cname LIKE %面向对象面向对象_C+% 数据库系统原理厦门大学计算机科学系林子雨2017版例题（续） SELECT Cno，Ccredit FROM Course WHERE Cname LIKE %面向对象面向对象 _C+ % ESCAPE 数据库系统原理厦门大学计算机科学系林子雨2017版例题（续）使用换码字符将通配符转义为普通使用换码字符将通
42、配符转义为普通字符字符(续续)例例20 查询以查询以DB_开头，且倒数第开头，且倒数第3个个字符为字符为 i的课程的详细情况。的课程的详细情况。 SELECT * FROM Course WHERE Cname LIKE DB_%i_ _ ESCAPE ；数据库系统原理厦门大学计算机科学系林子雨2017版(5)涉及空值的查询 使用谓词使用谓词 IS NULL 或或 IS NOT NULL “IS NULL” 不能用不能用 “= NULL” 代替代替例例21 有些课没有先修课程。查询没有先修课有些课没有先修课程。查询没有先修课程的课程名称。程的课程名称。Select CnameFrom Cour
43、seWhere Cpno IS NULL数据库系统原理厦门大学计算机科学系林子雨2017版(6)多重条件查询用逻辑运算符用逻辑运算符AND和和 OR来联结多个查询条件来联结多个查询条件 AND的优先级高于的优先级高于OR 可以用括号改变优先级可以用括号改变优先级可用来实现多种其他谓词可用来实现多种其他谓词 NOT IN NOT BETWEEN AND 数据库系统原理厦门大学计算机科学系林子雨2017版例题例例23 查询计算机系年龄在查询计算机系年龄在20岁以下的学生姓名。岁以下的学生姓名。 SELECT Sname FROM Student WHERE Sdept= CS AND Sage=2
44、0 AND Sage=23；数据库系统原理厦门大学计算机科学系林子雨2017版三、对查询结果排序使用使用ORDER BY子句子句 可以按一个或多个属性列排序可以按一个或多个属性列排序 升序：升序：ASC；降序：；降序：DESC；缺省值为升序；缺省值为升序当排序列含空值时当排序列含空值时空值的显示顺序由具体系统实现来决定空值的显示顺序由具体系统实现来决定例如按升序排，含空值的元组最后显示例如按升序排，含空值的元组最后显示按降序排，空值的元组最先显示按降序排，空值的元组最先显示各个系统的实现可以不同，只要保持一致即可各个系统的实现可以不同，只要保持一致即可数据库系统原理厦门大学计算机科学系林子雨2
45、017版对查询结果排序（续）例例24 查查询询选选修修了了3号号课课程程的的学学生生的的学学号及其成绩，查询结果按分数降序排列。号及其成绩，查询结果按分数降序排列。 SELECT Sno,Grade FROM SC WHERE Cno= 3 ORDER BY Grade DESC;数据库系统原理厦门大学计算机科学系林子雨2017版对查询结果排序（续）例例25 查查询询全全体体学学生生情情况况，查查询询结结果果按按所所在在系系的的系系号号升升序序排排列列，同同一一系系中中的的学学生按年龄降序排列。生按年龄降序排列。 SELECT * FROM Student ORDER BY Sdept, Sa
46、ge DESC; 数据库系统原理厦门大学计算机科学系林子雨2017版四、使用集函数5类主要聚集函数类主要聚集函数计数计数COUNT（DISTINCT|ALL *）COUNT（DISTINCT|ALL ）计算总和计算总和SUM（DISTINCT|ALL ） 计算平均值计算平均值AVG（DISTINCT|ALL ）数据库系统原理厦门大学计算机科学系林子雨2017版使用集函数（续）求最大值求最大值MAX（DISTINCT|ALL ） 求最小值求最小值MIN（DISTINCT|ALL ）DISTINCT短短语语：在在计计算算时时要要取取消消指指定定列列中中的重复值的重复值ALL短语：不取消重复值短语：
47、不取消重复值ALL为缺省值为缺省值数据库系统原理厦门大学计算机科学系林子雨2017版使用集函数（续）例例26 查询学生总人数。查询学生总人数。例例27 查询选修了课程的学生人数。查询选修了课程的学生人数。 SELECT COUNT(DISTINCT Sno) FROM SC；注：用注：用DISTINCT以避免重复计算学生人数以避免重复计算学生人数 SELECT COUNT(*) FROM Student；数据库系统原理厦门大学计算机科学系林子雨2017版使用集函数（续）例例28 计算计算2号课程的学生平均成绩。号课程的学生平均成绩。例例29 查询选修查询选修3号课程的学生最高分数。号课程的学生
48、最高分数。 SELECT AVG(Grade) FROM SC WHERE Cno= 2 ； SELECT MAX(Grade) FROM SC WHER Cno= 3； 数据库系统原理厦门大学计算机科学系林子雨2017版五、对查询结果分组使用使用GROUP BY子句分组子句分组 细化聚集函数的作用对象细化聚集函数的作用对象 未未对对查查询询结结果果分分组组，聚聚集集函函数数将将作作用用于于整整个查询结果个查询结果 对查询结果分组后，聚集函数将分别作用对查询结果分组后，聚集函数将分别作用于每个组于每个组 数据库系统原理厦门大学计算机科学系林子雨2017版使用GROUPBY子句分组例例30 求各
49、个课程号及相应的选课人数。求各个课程号及相应的选课人数。 SELECT Cno，COUNT(Sno) FROM SC GROUP BY Cno；数据库系统原理厦门大学计算机科学系林子雨2017版使用GROUPBY子句分组O_IdOrderDateOrderPriceCustomer12008/12/291000Bush22008/11/231600Carter32008/10/05700Bush42008/09/28300Bush52008/08/062000Adams62008/07/21100Carter我们拥有下面这个Orders表：现在，我们希望查找每个客户的总金额（总订单）。语句1：
50、SELECTCustomer,SUM(OrderPrice)FROMOrders语句2：SELECTCustomer,SUM(OrderPrice)FROMOrdersGROUPBYCustomer课堂作业课堂作业数据库系统原理厦门大学计算机科学系林子雨2017版使用GROUPBY子句分组语句1：SELECTCustomer,SUM(OrderPrice)FROMOrdersCustomerSUM(OrderPrice)Bush5700Carter5700Bush5700Bush5700Adams5700Carter5700数据库系统原理厦门大学计算机科学系林子雨2017版使用GROUPBY子
51、句分组语句2：SELECTCustomer,SUM(OrderPrice)FROMOrdersGROUPBYCustomerCustomerSUM(OrderPrice)Bush2000Carter1700Adams2000数据库系统原理厦门大学计算机科学系林子雨2017版对查询结果分组（续）GROUP BY子句的作用对象是查询的中间结果表子句的作用对象是查询的中间结果表分分组组方方法法：按按指指定定的的一一列列或或多多列列值值分分组组，值值相相等等的的为为一一组组; group by 是先排序后分组是先排序后分组使使用用GROUP BY子子句句后后，SELECT子子句句的的列列名名列列表表中
52、中只只能能出出现现分分组组属属性性和和聚聚集集函函数数，或或者者说说，select 后后面面的的所所有有列列中中,没没有有使使用用聚聚合合函函数数的的列列,必须出现在必须出现在 group by 后面后面如如果果要要用用到到group by 一一般般用用到到的的就就是是“每每”这这个个字字,例例如如：每每个个部部门门有有多少人多少人 就要用到分组的技术就要用到分组的技术数据库系统原理厦门大学计算机科学系林子雨2017版对查询结果分组（续）selectSno,CnofromSCgroupbySno-将会出现错误-消息8120，级别16，状态1，第1行选择列表中的列SC.Cno无效，因为该列没有包
53、含在聚合函数或GROUPBY子句中。这就是我们需要注意的一点，如果在返回集字段中，这些字段要么就要包含在GroupBy语句的后面，作为分组的依据；要么就要被包含在聚合函数中。selectSnoas学号,COUNT(*)as选课门数fromSCgroupbySno数据库系统原理厦门大学计算机科学系林子雨2017版使用HAVING短语筛选最终输出结果例例31 查询选修了查询选修了2门及以上课程的学生学号。门及以上课程的学生学号。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 2； 数据库系统原理厦门大学计算机科学系林子雨2017版例题例例32 查询
54、有查询有2门及以上课程是门及以上课程是85分以上的分以上的 学生的学号及（学生的学号及（85分以上的）课程数分以上的）课程数 SELECT Sno, COUNT(*) FROM SC WHERE Grade=85 GROUP BY Sno HAVING COUNT(*)=2;数据库系统原理厦门大学计算机科学系林子雨2017版使用HAVING短语筛选最终输出结果只有满足只有满足HAVING短语指定条件的组才输出短语指定条件的组才输出HAVING短短语语与与WHERE子子句句的的区区别别：作作用用对对象不同象不同WHERE子子句句作作用用于于基基表表或或视视图图，从从中中选选择择满足条件的元组。满
55、足条件的元组。HAVING短短语语作作用用于于组组，从从中中选选择择满满足足条条件件的组的组。 数据库系统原理厦门大学计算机科学系林子雨2017版3.4查询n3.4.1 单表查询单表查询n3.4.2 连接查询连接查询n3.4.3 嵌套查询嵌套查询n3.4.4 集合查询集合查询n3.4.5 SELECT 语句的一般格式语句的一般格式 数据库系统原理厦门大学计算机科学系林子雨2017版3.4.2连接查询同时涉及多个表的查询称为连接查询同时涉及多个表的查询称为连接查询用来连接两个表的条件称为连接条件或连接谓词用来连接两个表的条件称为连接条件或连接谓词 一般格式：一般格式：. . 比较运算符：比较运算
56、符：=、=、=、!=. BETWEEN . AND .数据库系统原理厦门大学计算机科学系林子雨2017版连接查询（续）连接字段连接字段连接谓词中的列名称为连接字段连接谓词中的列名称为连接字段连接条件中的各连接字段类型必须是连接条件中的各连接字段类型必须是可比的，但不必是相同的可比的，但不必是相同的数据库系统原理厦门大学计算机科学系林子雨2017版连接操作的执行过程嵌套循环法嵌套循环法(NESTED-LOOP)首首先先在在表表1中中找找到到第第一一个个元元组组，然然后后从从头头开开始始扫扫描描表表2，逐逐一一查查找找满满足足连连接接件件的的元元组组，找找到到后后就就将将表表1中中的的第第一一个个
57、元元组组与与该该元元组组拼拼接起来，形成结果表中一个元组。接起来，形成结果表中一个元组。表表2全全部部查查找找完完后后，再再找找表表1中中第第二二个个元元组组，然然后后再再从从头头开开始始扫扫描描表表2，逐逐一一查查找找满满足足连连接接条条件件的的元元组组，找找到到后后就就将将表表1中中的的第第二二个个元组与该元组拼接起来，形成结果表中一个元组。元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表重复上述操作，直到表1中的全部元组都处理完毕中的全部元组都处理完毕 数据库系统原理厦门大学计算机科学系林子雨2017版嵌套循环连接基于元组的嵌套循环连接Result=;/*初始化结果集合*
58、/ForeachtuplesinSForeachtuplerinRIfr.B=s.Bthen/*元组r和元组s满足连接条件*/Joinrandsastuplet;OutputtintoResult;/*输出连接结果元组*/EndifEndforEndforReturnResult数据库系统原理厦门大学计算机科学系林子雨2017版嵌套循环连接上面基于元组的嵌套循环连接算法中，对于循环外层的关系，通常称为外关系，而对循环内层的关系称为内关系。在执行嵌套循环连接时，仅对外关系进行1次读取操作，而对内关系则需要进行反复读取操作。如果不进行优化的话，这种基于元组的执行代价很大，以磁盘IO计算最多可能达到
59、Card(R)*Card(S)。因此，通常对这种算法进行修改，以减少嵌套循环连接的磁盘IO代价。一种方法是使用连接属性上的索引，以减少参与连接元组的数量，称为“索引嵌套循环连接”数据库系统原理厦门大学计算机科学系林子雨2017版排序合并法(SORT-MERGE)常用于常用于=连接连接首先按连接属性对表首先按连接属性对表1和表和表2排序排序对对表表1的的第第一一个个元元组组，从从头头开开始始扫扫描描表表2，顺顺序序查查找找满满足足连连接接条条件件的的元元组组，找找到到后后就就将将表表1中中的的第第一一个个元元组组与与该该元元组组拼拼接接起起来来，形形成成结结果果表表中中一一个个元元组组。当当遇遇
60、到到表表2中中第第一一条条大大于于表表1连连接字段值的元组时，对表接字段值的元组时，对表2的查询不再继续的查询不再继续数据库系统原理厦门大学计算机科学系林子雨2017版排序合并法找找到到表表1的的第第二二条条元元组组，然然后后从从刚刚才才的的中中断断点点处处继继续续顺顺序序扫扫描描表表2，查查找找满满足足连连接接条条件件的的元元组组，找找到到后后就就将将表表1中中的的第第二二个个元元组组与与该该元元组组拼拼接接起起来来，形形成成结结果果表表中中一一个个元元组组。直直接接遇遇到到表表2中中大大于于表表1连连接接字段值的元组时，对表字段值的元组时，对表2的查询不再继续的查询不再继续重重复复上上述述
61、操操作作，直直到到表表1或或表表2中中的的全全部部元元组组都都处处理理完毕为止完毕为止 数据库系统原理厦门大学计算机科学系林子雨2017版连接查询（续）SQL中连接查询的主要类型中连接查询的主要类型 广义笛卡尔积广义笛卡尔积 等值连接等值连接(含自然连接含自然连接) 非等值连接查询非等值连接查询 自身连接查询自身连接查询 外连接查询外连接查询 复合条件连接查询复合条件连接查询数据库系统原理厦门大学计算机科学系林子雨2017版一、广义笛卡尔积不带连接谓词的连接不带连接谓词的连接 很少使用很少使用例：例： SELECT Student.* , SC.* FROM Student, SC数据库系统原
62、理厦门大学计算机科学系林子雨2017版二、等值与非等值连接查询等值连接、自然连接、非等值连接等值连接、自然连接、非等值连接例例32 查询每个学生及其选修课程的情况。查询每个学生及其选修课程的情况。SELECT Student.*，SC.*FROM Student，SCWHERE Student.Sno = SC.Sno；数据库系统原理厦门大学计算机科学系林子雨2017版等值连接连接运算符为连接运算符为 = 的连接操作的连接操作 . = .任任何何子子句句中中引引用用表表1和和表表2中中同同名名属属性性时时，都都必必须须加加表表名名前前缀缀。引引用用唯唯一一属属性性名名时时可可以以加加也也可可以
63、以省省略略表表名前缀。名前缀。 数据库系统原理厦门大学计算机科学系林子雨2017版自然连接等等值值连连接接的的一一种种特特殊殊情情况况，把把目目标标列列中中重复的属性列去掉。重复的属性列去掉。例例33 对对例例32用自然连接完成。用自然连接完成。 SELECT Student.Sno，Sname，Ssex，Sage， Sdept，Cno，Grade FROM Student，SC WHERE Student.Sno = SC.Sno；数据库系统原理厦门大学计算机科学系林子雨2017版非等值连接查询连接运算符连接运算符 不是不是 = 的连接操作的连接操作 .比较运算符：比较运算符：、=、=、!=
64、 . BETWEEN . AND . 数据库系统原理厦门大学计算机科学系林子雨2017版三、自身连接一个表与其自己进行连接，称为表的一个表与其自己进行连接，称为表的自身连接自身连接需要给表起别名以示区别需要给表起别名以示区别由于所有属性名都是同名属性，因此必须使用由于所有属性名都是同名属性，因此必须使用别名前缀别名前缀数据库系统原理厦门大学计算机科学系林子雨2017版自身连接（续）例例34 查查询询每每一一门门课课的的间间接接先先修修课课（即先修课的先修课）（即先修课的先修课） SELECT FIRST.Cno，SECOND.Cpno FROM Course FIRST，Course SECO
65、ND WHERE FIRST.Cpno = SECOND.Cno； 数据库系统原理厦门大学计算机科学系林子雨2017版四、外连接（OuterJoin）外连接与普通连接的区别外连接与普通连接的区别普通连接操作只输出满足连接条件的元组普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出中不满足连接条件的元组一并输出数据库系统原理厦门大学计算机科学系林子雨2017版外连接（续） 例例 33 查询每个学生及其选修课程的情况查询每个学生及其选修课程的情况包括没有选修课程的学生包括没有选修课程的学生-用外连接操作用外
66、连接操作SELECT Student.Sno，Sname，Ssex， Sage，Sdept，Cno，Grade FROM Student LEFT JOIN SC ON (Student.Sno=SC.Sno)数据库系统原理厦门大学计算机科学系林子雨2017版五、复合条件连接WHERE子子句句中中含含多多个个连连接接条条件件时时，称称为为复复合合条条件连接件连接例例35查询选修查询选修2号课程且成绩在号课程且成绩在86分以上的所有分以上的所有学生的学号、姓名学生的学号、姓名SELECT Student.Sno, student.SnameFROM Student, SCWHERE Studen
67、t.Sno = SC.Sno /* 连接谓词连接谓词*/AND SC.Cno= 2 /* 其他限定条件其他限定条件 */AND SC.Grade 86； /* 其他限定条件其他限定条件 */数据库系统原理厦门大学计算机科学系林子雨2017版多表连接例例36 查查询询每每个个学学生生的的姓姓名名、选选修修课程名及成绩。课程名及成绩。SELECT Sname，Cname，Grade FROM Student，SC，Course WHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno；数据库系统原理厦门大学计算机科学系林子雨2017版3.4查询n3.4.
68、1 单表查询单表查询n3.4.2 连接查询连接查询n3.4.3 嵌套查询嵌套查询n3.4.4 集合查询集合查询n3.4.5 SELECT 语句的一般格式语句的一般格式 数据库系统原理厦门大学计算机科学系林子雨2017版3.4.3嵌套查询n嵌套查询概述嵌套查询概述n嵌套查询分类嵌套查询分类n嵌套查询求解方法嵌套查询求解方法n引出子查询的谓词引出子查询的谓词 数据库系统原理厦门大学计算机科学系林子雨2017版嵌套查询(续)嵌套查询概述嵌套查询概述一个一个SELECT-FROM-WHERE语句称为一个语句称为一个查询块查询块将一个查询块嵌套在另一个查询块的将一个查询块嵌套在另一个查询块的WHERE子
69、句或子句或HAVING短语的条件中的查短语的条件中的查询称为嵌套查询询称为嵌套查询 数据库系统原理厦门大学计算机科学系林子雨2017版嵌套查询(续) SELECT Sname外层查询外层查询/父查询父查询 FROM StudentWHERE Sno IN （SELECT Sno 内层查询内层查询/子查子查询询 FROM SC WHERE Cno= 2 ）；）；例例 查询选修查询选修2号课程的学生信息。号课程的学生信息。数据库系统原理厦门大学计算机科学系林子雨2017版嵌套查询(续)子查询的限制子查询的限制不能使用不能使用ORDER BY子句子句层层嵌套方式反映了层层嵌套方式反映了 SQL语言的
70、结构化语言的结构化有些嵌套查询可以用连接运算替代有些嵌套查询可以用连接运算替代数据库系统原理厦门大学计算机科学系林子雨2017版AnIntroductiontoDatabaseSystem嵌套查询分类不相关子查询不相关子查询子查询的查询条件不依赖于父查询子查询的查询条件不依赖于父查询相关子查询相关子查询子查询的查询条件依赖于父查询子查询的查询条件依赖于父查询数据库系统原理厦门大学计算机科学系林子雨2017版嵌套查询求解方法不相关子查询不相关子查询是由里向外逐层处理。即每个子查询在是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果上一级查询处理之前求解，子查询的结果用于建立其
71、父查询的查找条件。用于建立其父查询的查找条件。数据库系统原理厦门大学计算机科学系林子雨2017版嵌套查询求解方法（续）相关子查询相关子查询首先取外层查询中表的第一个元组，根据它与首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结子句返回值为真，则取此元组放入结果表；果表；然后再取外层表的下一个元组；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止。重复这一过程，直至外层表全部检查完为止。数据库系统原理厦门大学计算机科学系林子雨2017版引出子查询的谓词带有带有ININ谓词的
72、子查询谓词的子查询带有比较运算符的子查询带有比较运算符的子查询带有带有ANYANY或或ALLALL谓词的子查询谓词的子查询带有带有EXISTSEXISTS谓词的子查询谓词的子查询数据库系统原理厦门大学计算机科学系林子雨2017版一、带有IN谓词的子查询例例37 查询与查询与“刘晨刘晨”在同一个系学习的在同一个系学习的学生。学生。 此查询要求可以分步来完成此查询要求可以分步来完成 确定确定“刘晨刘晨”所在系名所在系名 SELECT Sdept FROM Student WHERE Sname= 刘晨刘晨 结果为：结果为：Sdept IS数据库系统原理厦门大学计算机科学系林子雨2017版带有IN谓
73、词的子查询（续） 查找所有在查找所有在IS系学习的学生。系学习的学生。 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept= IS ； 结果为：结果为：Sno Sname Sdept95001 刘晨刘晨 IS95004 张立张立 IS数据库系统原理厦门大学计算机科学系林子雨2017版构造嵌套查询将第一步查询嵌入到第二步查询的条件中将第一步查询嵌入到第二步查询的条件中 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= 刘晨
74、刘晨 )；此查询为不相关子查询。此查询为不相关子查询。DBMS求解该查询时也是分步求解该查询时也是分步去做的。去做的。数据库系统原理厦门大学计算机科学系林子雨2017版带有IN谓词的子查询（续） 用自身连接完成本查询要求用自身连接完成本查询要求 SELECT S1.Sno，S1.Sname，S1.Sdept FROM Student S1，Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname = 刘晨刘晨；数据库系统原理厦门大学计算机科学系林子雨2017版带有IN谓词的子查询（续）父查询和子查询中的表均可以定义别名父查询和子查询中的表均可以定义别名
75、 SELECT Sno，Sname，Sdept FROM Student S1 WHERE S1.Sdept IN (SELECT Sdept FROM Student S2 WHERE S2.Sname= 刘晨刘晨 )；数据库系统原理厦门大学计算机科学系林子雨2017版带有IN谓词的子查询（续）例例38查询选修了课程名为查询选修了课程名为“信息系统信息系统”的学生的学生学号和姓名学号和姓名SELECT Sno，Sname 最后在最后在Student关系中关系中 FROM Student 取出取出Sno和和Sname WHERE Sno IN (SELECT Sno 然后在然后在SC关系中找出
76、选关系中找出选 FROM SC 修了修了3号课程的学生学号号课程的学生学号 WHERE Cno IN (SELECT Cno 首先在首先在Course关系中找出关系中找出“信信 FROM Course 息系统息系统”的课程号，结果为的课程号，结果为3号号 WHERE Cname= 信息系统信息系统);数据库系统原理厦门大学计算机科学系林子雨2017版带有IN谓词的子查询（续）用连接查询用连接查询 SELECT Sno，Sname FROM Student，SC，Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Cours
77、e.Cname=信息系统信息系统；数据库系统原理厦门大学计算机科学系林子雨2017版二、带有比较运算符的子查询 当能确切知道内层查询返回单值时，可用当能确切知道内层查询返回单值时，可用比较运算符（比较运算符（，=，=，!=或或）。）。与与ANY或或ALL谓词配合使用谓词配合使用数据库系统原理厦门大学计算机科学系林子雨2017版带有比较运算符的子查询（续）例：假设一个学生只可能在一个系学习，并且必须例：假设一个学生只可能在一个系学习，并且必须属于一个系，则在属于一个系，则在例例37可以可以用用 = 代替代替IN ： SELECT Sno，Sname，Sdept FROM Student WHER
78、E Sdept = (SELECT Sdept FROM Student WHERE Sname= 刘晨刘晨 )；数据库系统原理厦门大学计算机科学系林子雨2017版带有比较运算符的子查询（续）例：找出每个学生超过他自己选修课程平均成绩的课程号Select Sno, CnoFrom SC xWhere Grade = (select avg(Grade) from SC ywhere y.Sno=x.Sno);相关子查询相关子查询数据库系统原理厦门大学计算机科学系林子雨2017版三、带有ANY或ALL谓词的子查询谓词语义谓词语义ANY：任意一个值：任意一个值ALL：所有值：所有值数据库系统原理厦
79、门大学计算机科学系林子雨2017版带有ANY或ALL谓词的子查询（续）需要配合使用比较运算符需要配合使用比较运算符 ANY大于子查询结果中的某个值大于子查询结果中的某个值 ALL大于子查询结果中的所有值大于子查询结果中的所有值 ANY小于子查询结果中的某个值小于子查询结果中的某个值 = ANY大于等于子查询结果中的某个值大于等于子查询结果中的某个值 = ALL大于等于子查询结果中的所有值大于等于子查询结果中的所有值= ANY小于等于子查询结果中的某个值小于等于子查询结果中的某个值 = ALL小于等于子查询结果中的所有值小于等于子查询结果中的所有值= ANY等于子查询结果中的某个值等于子查询结果
80、中的某个值 =ALL等于子查询结果中的所有值（通常没有实际意义）等于子查询结果中的所有值（通常没有实际意义）!=（或（或）ANY不等于子查询结果中的某个值不等于子查询结果中的某个值!=（或（或）ALL不等于子查询结果中的任何一个值不等于子查询结果中的任何一个值数据库系统原理厦门大学计算机科学系林子雨2017版带有ANY或ALL谓词的子查询（续）例例39 查询其他系中比信息系某查询其他系中比信息系某一一个个(其中某其中某一一个个)学生年龄小的学生姓名和年龄学生年龄小的学生姓名和年龄SELECT Sname，Sage FROM Student WHERE Sage ANY (SELECT Sage
81、 FROM Student WHERE Sdept= IS ) AND Sdept IS ; /* 注意这是父查询块中的条件注意这是父查询块中的条件 */数据库系统原理厦门大学计算机科学系林子雨2017版带有ANY或ALL谓词的子查询（续）结果结果Sname Sage 王敏王敏 18执行过程执行过程1.DBMS执行此查询时，首先处理子查询，找出执行此查询时，首先处理子查询，找出 IS系中所有学生的年龄，构成一个集合系中所有学生的年龄，构成一个集合(19，18)2. 处理父查询，找所有不是处理父查询，找所有不是IS系且年龄小于系且年龄小于 19 或或 18的学生的学生数据库系统原理厦门大学计算机
82、科学系林子雨2017版带有ANY或ALL谓词的子查询（续）ANY和和ALL谓词有时可以用集函数实现谓词有时可以用集函数实现ANY与与ALL与集函数的对应关系与集函数的对应关系=或或!=ANYIN-MAXMIN=MINALL-NOTINMINMAX=MAX数据库系统原理厦门大学计算机科学系林子雨2017版带有ANY或ALL谓词的子查询（续）用集函数实现子查询通常比直接用用集函数实现子查询通常比直接用ANY或或ALL查询效率要高，因为前者通常能够减查询效率要高，因为前者通常能够减少比较次数少比较次数数据库系统原理厦门大学计算机科学系林子雨2017版带有ANY或ALL谓词的子查询（续）例例40 查询
83、其他系中比信息系查询其他系中比信息系所有所有学生年龄学生年龄都都小小的学生姓名及年龄。的学生姓名及年龄。方法一：用方法一：用ALL谓词谓词 SELECT Sname，Sage FROM Student WHERE Sage ALL (SELECT Sage FROM Student WHERE Sdept= IS ) AND Sdept IS ;数据库系统原理厦门大学计算机科学系林子雨2017版带有ANY或ALL谓词的子查询（续） 方法二：用集函数方法二：用集函数 SELECT Sname，Sage FROM Student WHERE Sage (SELECT MIN(Sage) FROM
84、Student WHERE Sdept= IS ) AND Sdept IS ;数据库系统原理厦门大学计算机科学系林子雨2017版补充：分页语句利用NOTIN和SELECTTOP实现分页selectTOP2*FROMStudentwhere(SnoNOTIN(selecttop4SnofromStudentorderbySno)orderbySnoselectTOP页大小*FROMStudentwhere(SnoNOTIN(selecttop页大小*（页数-1）SnofromStudentorderbySno)orderbySno假设每页大小为2，显示第3页，可以采用以下SQL语句：数据库系统
85、原理厦门大学计算机科学系林子雨2017版补充：分页语句selecttop2*fromStudentwhere(Sno(selectMAX(Sno)from(Selecttop4Snofromstudentorderbysno)asT)orderbysnoselecttop页大小*fromStudentwhere(Sno(selectMAX(Sno)from(Selecttop页大小*（页数-1）Snofromstudentorderbysno)asT)orderbysno数据库系统原理厦门大学计算机科学系林子雨2017版四、带有EXISTS谓词的子查询1. EXISTS谓词谓词2. NOT E
86、XISTS谓词谓词3. 不同形式的查询间的替换不同形式的查询间的替换4. 相关子查询的效率相关子查询的效率5. 用用EXISTS/NOT EXISTS实现全称量词实现全称量词6. 用用EXISTS/NOT EXISTS实现逻辑蕴函实现逻辑蕴函数据库系统原理厦门大学计算机科学系林子雨2017版带有EXISTS谓词的子查询(续）1. EXISTS1. EXISTS谓词谓词存在量词存在量词 带有带有EXISTSEXISTS谓词的子查询不返回任何数据，只产生谓词的子查询不返回任何数据，只产生逻辑真值逻辑真值“truetrue”或逻辑假值或逻辑假值“falsefalse”。若内层查询结果非空，则返回真值
87、若内层查询结果非空，则返回真值若内层查询结果为空，则返回假值若内层查询结果为空，则返回假值由由EXISTSEXISTS引出的子查询，其目标列表达式通常都用引出的子查询，其目标列表达式通常都用* * ，因为带，因为带EXISTSEXISTS的子查询只返回真值或假值，给的子查询只返回真值或假值，给出列名无实际意义出列名无实际意义2. NOT EXISTS2. NOT EXISTS谓词谓词数据库系统原理厦门大学计算机科学系林子雨2017版带有EXISTS谓词的子查询(续） 例例41 41 查询所有选修了查询所有选修了2 2号课程的学生姓名。号课程的学生姓名。数据库系统原理厦门大学计算机科学系林子雨2
88、017版带有EXISTS谓词的子查询(续） 思路分析：思路分析： 本查询涉及本查询涉及StudentStudent和和SCSC关系。关系。 在在StudentStudent中依次取每个元组的中依次取每个元组的SnoSno值，用此值去值，用此值去检查检查SCSC关系。关系。 若若SCSC中存在这样的元组，其中存在这样的元组，其SnoSno值等于此值等于此Student.SnoStudent.Sno值，并且其值，并且其Cno= Cno= 22，则取此，则取此Student.SnameStudent.Sname送入结果关系。送入结果关系。数据库系统原理厦门大学计算机科学系林子雨2017版用嵌套查询用
89、嵌套查询 SELECT SnameSELECT Sname FROM FROM StudentStudent WHERE EXISTS WHERE EXISTS (SELECT (SELECT * * FROM SC /* FROM SC /*相关子查询相关子查询* */ / WHERE Sno= WHERE Sno=Student.SnoStudent.Sno AND Cno= AND Cno= 2)2)；带有带有EXISTS谓词的子查询谓词的子查询(续）续）数据库系统原理厦门大学计算机科学系林子雨2017版带有EXISTS谓词的子查询(续）用连接运算用连接运算SELECT SnameSEL
90、ECT SnameFROM Student, SCFROM Student, SCWHERE Student.Sno=SC.Sno AND WHERE Student.Sno=SC.Sno AND SC.Cno= SC.Cno= 2;2;数据库系统原理厦门大学计算机科学系林子雨2017版带有EXISTS谓词的子查询(续） 例例42 42 查询没有选修查询没有选修1 1号课程的学生姓名。号课程的学生姓名。 SELECT SnameSELECT Sname FROM FROM StudentStudent WHERE NOT EXISTS WHERE NOT EXISTS (SELECT * (S
91、ELECT * FROM SC FROM SC WHERE Sno = WHERE Sno = Student.Student.Sno AND Sno AND Cno=Cno=1)1)；数据库系统原理厦门大学计算机科学系林子雨2017版带有EXISTS谓词的子查询(续）3. 3. 不同形式的查询间的替换不同形式的查询间的替换一些一些带带EXISTSEXISTS或或NOT EXISTSNOT EXISTS谓词的子查询不能被谓词的子查询不能被其他形式的子查询等价替换其他形式的子查询等价替换所有所有带带ININ谓词、比较运算符、谓词、比较运算符、ANYANY和和ALLALL谓词的子谓词的子查询都能用
92、带查询都能用带EXISTSEXISTS谓词的子查询等价替换。谓词的子查询等价替换。数据库系统原理厦门大学计算机科学系林子雨2017版带有EXISTS谓词的子查询(续）例：例： 例例3737查询与查询与“刘晨刘晨”在同一个系学习的学在同一个系学习的学生。生。可以用带可以用带EXISTSEXISTS谓词的子查询替换：谓词的子查询替换：SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= 刘晨刘晨 )；数据库系统原理厦门大学计算机科学系林子雨2017版带有EXISTS谓词的
93、子查询(续）SELECT SnoSELECT Sno，SnameSname，SdeptSdeptFROM Student S1FROM Student S1WHERE EXISTSWHERE EXISTS(SELECT * FROM Student S2(SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND WHERE S2.Sdept = S1.Sdept AND S2.Sname = S2.Sname = 刘晨刘晨 ) )；例：例： 例例3737查询与查询与“刘晨刘晨”在同一个系学习的学在同一个系学习的学生。生。可以用带可以用带EXI
94、STSEXISTS谓词的子查询替换：谓词的子查询替换：数据库系统原理厦门大学计算机科学系林子雨2017版带有EXISTS谓词的子查询(续）为什么在嵌套连接中，EXISTS会比IN效率高？数据库系统原理厦门大学计算机科学系林子雨2017版带有EXISTS谓词的子查询(续）5.5.用用EXISTS/NOT EXISTSEXISTS/NOT EXISTS实现全称量词实现全称量词SQLSQL语言中没有全称量词语言中没有全称量词 （For allFor all）可以把带有全称量词的谓词转换为等价的带有可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：存在量词的谓词： ( ( x)P x)P ( (
95、 x( x( P) P) 数据库系统原理厦门大学计算机科学系林子雨2017版带有EXISTS谓词的子查询(续） 例例43 43 查询选修了全部课程的学生姓名。查询选修了全部课程的学生姓名。SELECT SnameSELECT Sname FROM Student FROM Student WHERE NOT EXISTS WHERE NOT EXISTS （SELECT * FROM CourseSELECT * FROM Course WHERE NOT EXISTS WHERE NOT EXISTS (SELECT * FROM SC (SELECT * FROM SC WHERE Sno
96、= Student.Sno WHERE Sno= Student.Sno AND Cno= Course.Cno AND Cno= Course.Cno）；）；SQL语句写法：查询这样的学生，没有一门课程是他不选修的语句写法：查询这样的学生，没有一门课程是他不选修的数据库系统原理厦门大学计算机科学系林子雨2017版带有EXISTS谓词的子查询(续）select * from sc where cno = o and select * from sc where cno = o and sno=student.snosno=student.sno是查询出所有已经选择过课程的学生是查询出所有已经选
97、择过课程的学生及相应课程及相应课程select * from course where not exists select * from course where not exists 则是所有则是所有没有被选择的课程没有被选择的课程在这个基础上的在这个基础上的 select sname from student where select sname from student where not exists not exists 则是选取所有没有未选择课程的学生，即则是选取所有没有未选择课程的学生，即选择了所有课程的学员名称选择了所有课程的学员名称数据库系统原理厦门大学计算机科学系林子雨20
98、17版带有EXISTS谓词的子查询(续）方法二：SelectSnameFromstudentWhereSnoIN(selectSnofromSCGroupbySnoHavingcount(*)=(selectcount(*)fromcourse)数据库系统原理厦门大学计算机科学系林子雨2017版带有EXISTS谓词的子查询(续） 6. 用用EXISTS/NOT EXISTS实现逻辑蕴函实现逻辑蕴函SQL语言中没有蕴函语言中没有蕴函(Implication)逻辑运算逻辑运算可以利用谓词演算将逻辑蕴函谓词等价转换为：可以利用谓词演算将逻辑蕴函谓词等价转换为： p q p q 数据库系统原理厦门大学
99、计算机科学系林子雨2017版带有EXISTS谓词的子查询(续） 例例44 44 查询至少选修了学生查询至少选修了学生9500295002选修的全部选修的全部课程的学生号码。课程的学生号码。解题思路：解题思路：用逻辑蕴函表达：查询学号为用逻辑蕴函表达：查询学号为x x的学生，对所有的课程的学生，对所有的课程y y，只要，只要9500295002学生选修了课程学生选修了课程y y，则，则x x也选修了也选修了y y。形式化表示：形式化表示：用用P P表示谓词表示谓词 “学生学生9500295002选修了课程选修了课程y y”用用q q表示谓词表示谓词 “学生学生x x选修了课程选修了课程y y”则
100、上述查询为则上述查询为: (: ( y) p y) p q q 数据库系统原理厦门大学计算机科学系林子雨2017版带有EXISTS谓词的子查询(续）等价变换：等价变换： ( y)p q ( y ( (p q ) ( y ( ( p q) y(p q)变换后语义：不存在这样的课程变换后语义：不存在这样的课程y，学生，学生95002选选修了修了y，而学生，而学生x没有选。没有选。数据库系统原理厦门大学计算机科学系林子雨2017版带有EXISTS谓词的子查询(续）用用NOT EXISTS谓词表示：谓词表示： SELECT * from Student where not exists( select
101、 * from SC SC1 where SC1.Sno=95002 and not exists (select * from SC SC2 where SC1.Cno=SC2.Cno and SC2.Sno=Student.Sno)数据库系统原理厦门大学计算机科学系林子雨2017版课堂作业题目：题目：查询选修了查询选修了“以数据库作为先行课以数据库作为先行课”的课程的学生姓名和学号的课程的学生姓名和学号要求：要求：（1）第一种方法：使用多表连接）第一种方法：使用多表连接（2）第二种方法：使用嵌套查询）第二种方法：使用嵌套查询数据库系统原理厦门大学计算机科学系林子雨2017版课堂作业（1）第
102、一种方法：使用多表连接）第一种方法：使用多表连接selectStudent.Sno,student.snamefromCoursefirst,Coursesecond,SC,studentwherefirst.cpno=oandsecond.Cname=数据库andSC.Cno=first.CnoandSC.Sno=Student.sno数据库系统原理厦门大学计算机科学系林子雨2017版课堂作业（2）第二种方法：使用嵌套查询）第二种方法：使用嵌套查询selectStudent.Sno,Student.SnamefromStudentwhereStudent.Snoin(selectSC.Sno
103、fromSCwhereSC.Cnoin(selectfirst.CnofromCoursefirst,Coursesecondwherefirst.cpno=oandsecond.Cname=数据库)数据库系统原理厦门大学计算机科学系林子雨2017版3.4查询n3.4.1 单表查询单表查询n3.4.2 连接查询连接查询n3.4.3 嵌套查询嵌套查询n3.4.4 集合查询集合查询n3.4.5 SELECT 语句的一般格式语句的一般格式 数据库系统原理厦门大学计算机科学系林子雨2017版3.4.4 集合查询集合操作种类集合操作种类并操作并操作(UNION)(UNION)交操作交操作(INTERSE
104、CT)(INTERSECT)差操作差操作(EXCEPT)(EXCEPT)数据库系统原理厦门大学计算机科学系林子雨2017版1并操作形式形式 UNION 参加参加UNION操作的各结果表的列数必须相同；操作的各结果表的列数必须相同；对应项的数据类型也必须相同对应项的数据类型也必须相同数据库系统原理厦门大学计算机科学系林子雨2017版并操作（续）例例45 查询计算机科学系的学生及年龄不大于查询计算机科学系的学生及年龄不大于19岁的学生。岁的学生。方法一：方法一： SELECT * FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WH
105、ERE Sage=19；数据库系统原理厦门大学计算机科学系林子雨2017版并操作（续）方法二：方法二： SELECT DISTINCT * FROM Student WHERE Sdept= CS OR Sage=19；数据库系统原理厦门大学计算机科学系林子雨2017版并操作（续）例例46 查询选修了课程查询选修了课程1或者选修了课程或者选修了课程2的学生的学生号码。号码。数据库系统原理厦门大学计算机科学系林子雨2017版并操作（续）方法二：方法二： SELECT DISTINCT Sno FROM SC WHERE Cno= 1 OR Cno= 2 ；方法一：方法一： SELECT Sno
106、FROM SC WHERE Cno= 1 UNION SELECT Sno FROM SC WHERE Cno= 2 ；数据库系统原理厦门大学计算机科学系林子雨2017版并操作（续）例例47 查询学校中所有师生的姓名。查询学校中所有师生的姓名。 SELECT Sname FROM Student UNION SELECT Tname FROM Teacher;数据库系统原理厦门大学计算机科学系林子雨2017版2交操作标准标准SQL中没有提供集合交操作，但可用其中没有提供集合交操作，但可用其他方法间接实现。他方法间接实现。数据库系统原理厦门大学计算机科学系林子雨2017版2交操作例例48 查询计
107、算机科学系的学生与年龄不大于查询计算机科学系的学生与年龄不大于19岁的学生的交集岁的学生的交集本例实际上就是查询计算机科学系中年龄不大于本例实际上就是查询计算机科学系中年龄不大于19岁的学生岁的学生 SELECT * FROM Student WHERE Sdept= CS AND Sage19；数据库系统原理厦门大学计算机科学系林子雨2017版差操作（续）例例52 查询学生姓名与教师姓名的差集查询学生姓名与教师姓名的差集 本例实际上是查询学校中未与教师同名的学生姓名本例实际上是查询学校中未与教师同名的学生姓名 SELECT DISTINCT Sname FROM Student WHERE
108、Sname NOT IN (SELECT Tname FROM Teacher);数据库系统原理厦门大学计算机科学系林子雨2017版4.对集合操作结果的排序ORDER BY子句只能用于对最终查询结果子句只能用于对最终查询结果排序，不能对中间结果排序排序，不能对中间结果排序任何情况下，任何情况下，ORDER BY子句只能出现在子句只能出现在最后最后对集合操作结果排序时，对集合操作结果排序时，ORDER BY子句子句中用数字指定排序属性中用数字指定排序属性数据库系统原理厦门大学计算机科学系林子雨2017版对集合操作结果的排序（续）例例53 错误写法错误写法 SELECT * FROM Studen
109、t WHERE Sdept= CS ORDER BY Sno UNION SELECT * FROM Student WHERE Sage=19 ORDER BY Sno；数据库系统原理厦门大学计算机科学系林子雨2017版对集合操作结果的排序（续）正确写法正确写法 SELECT * FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WHERE Sage=19 ORDER BY 1；数据库系统原理厦门大学计算机科学系林子雨2017版3.4查询n3.4.1 单表查询单表查询n3.4.2 连接查询连接查询n3.4.3 嵌套查询嵌套查询n
110、3.4.4 集合查询集合查询n3.4.5 SELECT 语句的一般格式语句的一般格式 数据库系统原理厦门大学计算机科学系林子雨2017版3.4.5SELECT语句的一般格式SELECT ALL|DISTINCT 别名别名 ， 别名别名 FROM 别名别名 ， 别名别名 WHERE GROUP BY ， .HAVING ORDER BY ASC|DESC ， ASC|DESC ；数据库系统原理厦门大学计算机科学系林子雨2017版第3章关系数据库标准语言SQL3.1 SQL概述概述3.2 学生学生-课程数据库课程数据库3.3 数据定义数据定义3.4 查询查询3.5 数据更新数据更新3.6 视图视图
111、数据库系统原理厦门大学计算机科学系林子雨2017版3.5数据更新3.5.1 插入数据插入数据3.5.2 修改数据修改数据3.5.3 删除数据删除数据数据库系统原理厦门大学计算机科学系林子雨2017版3.5.1插入数据两种插入数据方式两种插入数据方式 插入单个元组插入单个元组 插入子查询结果插入子查询结果数据库系统原理厦门大学计算机科学系林子雨2017版1.插入单个元组语句格式语句格式INSERTINTO (，)VALUES ( ， )功能功能 将新元组插入指定表中。将新元组插入指定表中。数据库系统原理厦门大学计算机科学系林子雨2017版插入单个元组（续）例例1 将一个新学生记录将一个新学生记录
112、（学号：（学号：95020；姓名：陈冬；性别：男；所在系：；姓名：陈冬；性别：男；所在系：IS；年龄：年龄：18岁）插入到岁）插入到Student表中。表中。 INSERT INTO Student VALUES (95020，陈冬陈冬，男男，IS，18)；数据库系统原理厦门大学计算机科学系林子雨2017版插入单个元组（续）例例2 插入一条选课记录插入一条选课记录( 95020，1 )。 INSERT INTO SC(Sno，Cno) VALUES ( 95020 ， 1 )； 新插入的记录在新插入的记录在Grade列上取空值列上取空值数据库系统原理厦门大学计算机科学系林子雨2017版插入单个
113、元组（续） INTO子句子句指定要插入数据的表名及属性列指定要插入数据的表名及属性列属性列的顺序可与表定义中的顺序不一致属性列的顺序可与表定义中的顺序不一致没有指定属性列：表示要插入的是一条完整的元组，没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致且属性列属性与表定义中的顺序一致指定部分属性列：插入的元组在其余属性列上取空指定部分属性列：插入的元组在其余属性列上取空值值 VALUES子句子句 提供的值必须与提供的值必须与INTO子句匹配子句匹配值的个数值的个数值的类型值的类型数据库系统原理厦门大学计算机科学系林子雨2017版2.插入子查询结果语句格式语句格式 I
114、NSERT INTO ( ， ) 子查询子查询；功能功能 将子查询结果插入指定表中将子查询结果插入指定表中数据库系统原理厦门大学计算机科学系林子雨2017版插入子查询结果（续）例例3 对每一个系，求学生的平均年对每一个系，求学生的平均年龄，并把结果存入数据库。龄，并把结果存入数据库。第一步：建表第一步：建表 CREATE TABLE Deptage (Sdept CHAR(15) /* 系名系名*/ Avgage INT)； /*学生平均年龄学生平均年龄*/ 数据库系统原理厦门大学计算机科学系林子雨2017版插入子查询结果（续）第二步：插入数据第二步：插入数据 INSERT INTO Dept
115、age(Sdept，Avgage) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；数据库系统原理厦门大学计算机科学系林子雨2017版插入子查询结果（续）INTO子句子句(与插入单条元组类似与插入单条元组类似)指定要插入数据的表名及属性列指定要插入数据的表名及属性列属性列的顺序可与表定义中的顺序不一致属性列的顺序可与表定义中的顺序不一致没有指定属性列：表示要插入的是一条完整的元组没有指定属性列：表示要插入的是一条完整的元组指定部分属性列：插入的元组在其余属性列上取空值指定部分属性列：插入的元组在其余属性列上取空值 子查询子查询SELECT子
116、句目标列必须与子句目标列必须与INTO子句匹配子句匹配值的个数值的个数值的类型值的类型数据库系统原理厦门大学计算机科学系林子雨2017版插入子查询结果（续）DBMS在执行插入语句时会检查所插元组是在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则否破坏表上已定义的完整性规则实体完整性实体完整性参照完整性参照完整性用户定义的完整性用户定义的完整性对于有对于有NOT NULL约束的属性列是否提供了非空值约束的属性列是否提供了非空值 对于有对于有UNIQUE约束的属性列是否提供了非重复值约束的属性列是否提供了非重复值 对于有值域约束的属性列所提供的属性值是否在值域范围内对于有值域约束的属性
117、列所提供的属性值是否在值域范围内数据库系统原理厦门大学计算机科学系林子雨2017版3.5数据更新3.5.1 插入数据插入数据3.5.2 修改数据修改数据3.5.3 删除数据删除数据 数据库系统原理厦门大学计算机科学系林子雨2017版3.5.2修改数据语句格式语句格式 UPDATE SET =，= WHERE ；功能功能修改指定表中满足修改指定表中满足WHERE子句条件的元组子句条件的元组数据库系统原理厦门大学计算机科学系林子雨2017版修改数据（续）三种修改方式三种修改方式修改某一个元组的值修改某一个元组的值修改多个元组的值修改多个元组的值带子查询的修改语句带子查询的修改语句数据库系统原理厦门
118、大学计算机科学系林子雨2017版1.修改某一个元组的值例例4 将学生将学生95001的年龄改为的年龄改为22岁。岁。 UPDATE Student SET Sage=22 WHERE Sno= 95001 ； 数据库系统原理厦门大学计算机科学系林子雨2017版2.修改多个元组的值例例5 将所有学生的年龄增加将所有学生的年龄增加1岁。岁。UPDATE Student SET Sage= Sage+1 数据库系统原理厦门大学计算机科学系林子雨2017版修改多个元组的值(续)例例6 将信息系所有学生的年龄增加将信息系所有学生的年龄增加1岁。岁。 UPDATE Student SET Sage= Sa
119、ge+1 WHERE Sdept= IS ；数据库系统原理厦门大学计算机科学系林子雨2017版3.带子查询的修改语句例例7 将计算机科学系全体学生的成绩置零将计算机科学系全体学生的成绩置零UPDATE SC SET Grade=0 WHERE CS= (SELECT Sdept FROM Student WHERE Student.Sno = SC.Sno)；数据库系统原理厦门大学计算机科学系林子雨2017版修改数据（续）SET子句子句 指定修改方式指定修改方式 要修改的列要修改的列 修改后取值修改后取值WHERE子句子句指定要修改的元组指定要修改的元组缺省表示要修改表中的所有元组缺省表示要修
120、改表中的所有元组数据库系统原理厦门大学计算机科学系林子雨2017版修改数据（续）DBMS在执行修改语句时会检查修改操作在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则是否破坏表上已定义的完整性规则实体完整性实体完整性参照完整性参照完整性用户定义的完整性用户定义的完整性 NOT NULL约束约束 UNIQUE约束约束 值域约束值域约束数据库系统原理厦门大学计算机科学系林子雨2017版3.5数据更新3.5.1 插入数据插入数据3.5.2 修改数据修改数据3.5.3 删除数据删除数据 数据库系统原理厦门大学计算机科学系林子雨2017版3.5.3删除数据 DELETE FROM WHERE
121、 ；功能功能w删除指定表中满足删除指定表中满足WHERE子句条件的子句条件的元组元组WHERE子句子句w指定要删除的元组指定要删除的元组w缺省表示要删除表中的所有元组缺省表示要删除表中的所有元组数据库系统原理厦门大学计算机科学系林子雨2017版删除数据（续）三种删除方式三种删除方式删除某一个元组的值删除某一个元组的值删除多个元组的值删除多个元组的值带子查询的删除语句带子查询的删除语句数据库系统原理厦门大学计算机科学系林子雨2017版1.删除某一个元组的值例例8 删除学号为删除学号为95003的学生记录。的学生记录。 DELETE FROM Student WHERE Sno=95003；数据库
122、系统原理厦门大学计算机科学系林子雨2017版2.删除多个元组的值例例9 删除删除2号课程的所有选课记录。号课程的所有选课记录。例例10 删除所有的学生选课记录。删除所有的学生选课记录。 DELETE FROM SC； WHERE Cno=2；DELETE FROM SC；数据库系统原理厦门大学计算机科学系林子雨2017版3.带子查询的删除语句例例11 删除信息系所有学生的选课记录。删除信息系所有学生的选课记录。 DELETE FROM SC WHERE IS= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno)；数据库系统原理厦门大学计算机
123、科学系林子雨2017版删除数据(续)DBMS在执行删除语句时会检查所插元组在执行删除语句时会检查所插元组是否破坏表上已定义的完整性规则是否破坏表上已定义的完整性规则参照完整性参照完整性不允许删除不允许删除级联删除级联删除数据库系统原理厦门大学计算机科学系林子雨2017版更新数据与数据一致性DBMS在执行插入、删除、更新语句时必在执行插入、删除、更新语句时必须保证数据库一致性须保证数据库一致性l必须有事务的概念和原子性必须有事务的概念和原子性l完整性检查和保证完整性检查和保证数据库系统原理厦门大学计算机科学系林子雨2017版第3章关系数据库标准语言SQL3.1 SQL概述概述3.2 学生学生-课
124、程数据库课程数据库3.3 数据定义数据定义3.4 查询查询3.5 数据更新数据更新3.6 视图视图数据库系统原理厦门大学计算机科学系林子雨2017版3.6视图视图的特点视图的特点虚表，是从一个或几个基本表（或视图）虚表，是从一个或几个基本表（或视图）导出的表导出的表只存放视图的定义，不会出现数据冗余只存放视图的定义，不会出现数据冗余基表中的数据发生变化，从视图中查询基表中的数据发生变化，从视图中查询出的数据也随之改变出的数据也随之改变数据库系统原理厦门大学计算机科学系林子雨2017版3.6视图基于视图的操作基于视图的操作 查询查询 删除删除 受限更新受限更新定义基于该视图的新视图定义基于该视图
125、的新视图数据库系统原理厦门大学计算机科学系林子雨2017版3.6视图3.6.1 定义视图定义视图3.6.2 查询视图查询视图3.6.3 更新视图更新视图3.6.4 视图的作用视图的作用数据库系统原理厦门大学计算机科学系林子雨2017版1.建立视图语句格式语句格式 CREATE VIEW ( ，) AS WITH CHECK OPTION；数据库系统原理厦门大学计算机科学系林子雨2017版行列子集视图 例例1 建立信息系学生的视图。建立信息系学生的视图。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdep
126、t= IS；从单个基本表导出从单个基本表导出只是去掉了基本表的某些行和某些列保留了码只是去掉了基本表的某些行和某些列保留了码数据库系统原理厦门大学计算机科学系林子雨2017版建立视图（续）DBMS执行执行CREATE VIEW语句时只是把语句时只是把视图的定义存入数据字典，并不执行其中视图的定义存入数据字典，并不执行其中的的SELECT语句。语句。在对视图查询时，按视图的定义从基本表在对视图查询时，按视图的定义从基本表中将数据查出。中将数据查出。数据库系统原理厦门大学计算机科学系林子雨2017版插入子查询结果（续）例例 对每一个系，求学生的平均年龄，对每一个系，求学生的平均年龄，并把结果存入数
127、据库。并把结果存入数据库。1.创建表，存入数据创建表，存入数据2.通过视图通过视图数据库系统原理厦门大学计算机科学系林子雨2017版创建基表存数据 CREATE TABLE Deptage (Sdept CHAR(15) /* 系名系名*/ Avgage INT)； /*学生平均年龄学生平均年龄*/ INSERT INTO Deptage(Sdept，Avgage) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；数据库系统原理厦门大学计算机科学系林子雨2017版直接创建视图 Create view VSA(Sdept，Avgage) as
128、 SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；数据库系统原理厦门大学计算机科学系林子雨2017版组成视图的属性列名全部省略或全部指定全部省略或全部指定省略省略: 由子查询中由子查询中SELECT目标列中的诸字段组成目标列中的诸字段组成明确指定视图的所有列名明确指定视图的所有列名:(1)某个目标列是集函数或列表达式某个目标列是集函数或列表达式(2)多表连接时选出了几个同名列作为视图的字段多表连接时选出了几个同名列作为视图的字段(3) 需要在视图中为某个列启用新的更合适的名字需要在视图中为某个列启用新的更合适的名字数据库系统原理厦门大学计算
129、机科学系林子雨2017版建立视图（续）WITH CHECK OPTION透过视图进行增删改操作时，不得破坏视透过视图进行增删改操作时，不得破坏视图定义中的谓词条件图定义中的谓词条件（即子查询中的条件表达式）（即子查询中的条件表达式）数据库系统原理厦门大学计算机科学系林子雨2017版WITHCHECKOPTION的视图例例2 建立信息系学生的视图，并要求透过该视建立信息系学生的视图，并要求透过该视图进行的更新操作只涉及信息系学生。图进行的更新操作只涉及信息系学生。CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE
130、Sdept= IS WITH CHECK OPTION数据库系统原理厦门大学计算机科学系林子雨2017版对IS_Student视图的更新操作修改操作：修改操作：DBMS自动加上自动加上Sdept= IS的条件的条件插入操作：插入操作：DBMS自动检查自动检查Sdept属性值是否为属性值是否为IS 如果不是，则拒绝该插入操作如果不是，则拒绝该插入操作删除操作：删除操作：DBMS自动加上自动加上Sdept= IS的条件的条件数据库系统原理厦门大学计算机科学系林子雨2017版基于多个基表的视图例例4 建立选修了建立选修了1号课程的学生视图号课程的学生视图(包括学号、包括学号、姓名、成绩姓名、成绩)。
131、 CREATE VIEW IS_S1(Sno，Sname，Grade) AS SELECT Student.Sno，Sname，Grade FROM Student，SC WHERE Student.Sno=SC.Sno AND SC.Cno= 1；数据库系统原理厦门大学计算机科学系林子雨2017版基于视图的视图例例5 建立选修了建立选修了1号课程且成绩在号课程且成绩在90分以上分以上的学生的视图。的学生的视图。 CREATE VIEW IS_S2 AS SELECT Sno，Sname，Grade FROM IS_S1 WHERE Grade=90；数据库系统原理厦门大学计算机科学系林子雨2
132、017版带表达式的视图例例6 定义一个反映学生出生年份的视图定义一个反映学生出生年份的视图（包括学号、姓名、出生年份）。（包括学号、姓名、出生年份）。 CREATE VIEW BT_S(Sno，Sname，Sbirth) AS SELECT Sno，Sname，2005-Sage FROM Student设置一些派生属性列设置一些派生属性列, 也也称为虚拟列称为虚拟列-Sbirth 带表达式的视图必须明确定义组成视图的各个属性列名带表达式的视图必须明确定义组成视图的各个属性列名数据库系统原理厦门大学计算机科学系林子雨2017版建立分组视图例例7 将学生的学号及他的平均成绩定义为一个将学生的学号
133、及他的平均成绩定义为一个视图视图 CREAT VIEW S_G(Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno；数据库系统原理厦门大学计算机科学系林子雨2017版建立视图（续）一类不易扩充的视图一类不易扩充的视图以以 SELECT * 方式创建的视图可扩充性差，应方式创建的视图可扩充性差，应尽可能避免尽可能避免数据库系统原理厦门大学计算机科学系林子雨2017版建立视图（续）例例8将将Student表表中中所所有有女女生生记记录录定定义义为为一一个个视图视图 CREATE VIEW F_Student1(stdnum， name，
134、sex， age，dept) AS SELECT * FROM Student WHERE Ssex=女女；数据库系统原理厦门大学计算机科学系林子雨2017版建立视图（续）缺点：修改基表缺点：修改基表Student的结构后，的结构后，Student表与表与F_Student1视图的映象关系被破视图的映象关系被破坏，导致该视图不能正确工作。坏，导致该视图不能正确工作。数据库系统原理厦门大学计算机科学系林子雨2017版建立视图（续） CREATE VIEW F_Student2 (stdnum， name， sex， age，dept) AS SELECT Sno，Sname，Ssex，Sage，
135、Sdept FROM Student WHERE Ssex=女女；为基表为基表Student增加属性列不会破坏增加属性列不会破坏Student表表与与F_Student2视图的映象关系。视图的映象关系。数据库系统原理厦门大学计算机科学系林子雨2017版常见的视图形式行列子集视图行列子集视图WITH CHECK OPTION的视图的视图基于多个基表的视图基于多个基表的视图基于视图的视图基于视图的视图带表达式的视图带表达式的视图分组视图分组视图数据库系统原理厦门大学计算机科学系林子雨2017版2.删除视图DROP VIEW ；该语句从数据字典中删除指定的视图定义该语句从数据字典中删除指定的视图定义
136、由该视图导出的其他视图定义仍在数据字典中，由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式删除但已不能使用，必须显式删除删除基表时，由该基表导出的所有视图定义都删除基表时，由该基表导出的所有视图定义都必须显式删除必须显式删除数据库系统原理厦门大学计算机科学系林子雨2017版删除视图(续） 例例9 删除视图删除视图IS_S1 DROP VIEW IS_S1； 数据库系统原理厦门大学计算机科学系林子雨2017版3.6视图3.6.1 定义视图定义视图3.6.2 查询视图查询视图3.6.3 更新视图更新视图3.6.4 视图的作用视图的作用数据库系统原理厦门大学计算机科学系林子雨2017
137、版3.6.2查询视图从用户角度：查询视图与查询基本表相同从用户角度：查询视图与查询基本表相同DBMS实现实现视图查询的方法视图查询的方法实体化视图（实体化视图（View Materialization）有效性检查：检查所查询的视图是否存在有效性检查：检查所查询的视图是否存在执行视图定义，将视图临时实体化，生成临时表执行视图定义，将视图临时实体化，生成临时表查询视图转换为查询临时表查询视图转换为查询临时表查询完毕删除被实体化的视图查询完毕删除被实体化的视图(临时表临时表)数据库系统原理厦门大学计算机科学系林子雨2017版查询视图（续）视图消解法（视图消解法（View Resolution）进行有
138、效性检查，检查查询的表、视图等是否存进行有效性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义在。如果存在，则从数据字典中取出视图的定义把视图定义中的子查询与用户的查询结合起来，把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询转换成等价的对基本表的查询执行修正后的查询执行修正后的查询数据库系统原理厦门大学计算机科学系林子雨2017版查询视图（续）例例1 在信息系学生的视图中找出年龄小于在信息系学生的视图中找出年龄小于20岁的学生。岁的学生。 SELECT Sno，Sage FROM IS_Student WHERE Sage20；IS_Studen
139、t视图的定义视图的定义 (视图定义例视图定义例1)： CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS；数据库系统原理厦门大学计算机科学系林子雨2017版查询视图（续）视图实体化法视图实体化法视图消解法视图消解法转换后的查询语句为：转换后的查询语句为：SELECT Sno，Sage FROM StudentWHERE Sdept= IS AND Sage=90WHERE Gavg=90；S_G视图定义：视图定义： CREATE VIEW S_G (Sno，Gavg) AS SELECT Sn
140、o，AVG(Grade)FROM SCGROUP BY Sno；数据库系统原理厦门大学计算机科学系林子雨2017版查询转换错误：错误：SELECT Sno，AVG(Grade)FROM SCWHERE AVG(Grade)=90GROUP BY Sno；正确：正确：SELECT Sno，AVG(Grade)FROM SCGROUP BY SnoHAVING AVG(Grade)=90；数据库系统原理厦门大学计算机科学系林子雨2017版3.6视图3.6.1 定义视图定义视图3.6.2 查询视图查询视图3.6.3 更新视图更新视图3.6.4 视图的作用视图的作用数据库系统原理厦门大学计算机科学系林
141、子雨2017版3.6.3更新视图用户角度：更新视图与更新基本表相同用户角度：更新视图与更新基本表相同DBMS实现视图更新的方法实现视图更新的方法视图实体化法（视图实体化法（View Materialization）视图消解法（视图消解法（View Resolution）指定指定WITH CHECK OPTION子句后子句后 DBMS在更新视图时会进行检查，防止用户通过视图在更新视图时会进行检查，防止用户通过视图对对不属于视图范围内不属于视图范围内的基本表数据进行更新的基本表数据进行更新数据库系统原理厦门大学计算机科学系林子雨2017版更新视图（续）例例1 将信息系学生视图将信息系学生视图IS_
142、Student中学号中学号95002的学生姓名改为的学生姓名改为“刘辰刘辰”。UPDATE IS_Student SET Sname= 刘辰刘辰WHERE Sno= 95002；转换后的语句：转换后的语句：UPDATE Student SET Sname= 刘辰刘辰WHERE Sno= 95002 AND Sdept= IS；数据库系统原理厦门大学计算机科学系林子雨2017版更新视图（续）例例2 向信息系学生视图向信息系学生视图IS_S中插入一个新的学生中插入一个新的学生记录：记录：95029，赵新，赵新，20岁岁INSERTINTO IS_StudentVALUES(95029，赵新赵新，2
143、0)；转换为对基本表的更新：转换为对基本表的更新：INSERTINTO Student(Sno，Sname，Sage，Sdept)VALUES(95029，赵新赵新，20，IS )；数据库系统原理厦门大学计算机科学系林子雨2017版更新视图（续）例例3 删除视图删除视图CS_S中学号为中学号为95029的记录的记录DELETEFROM IS_StudentWHERE Sno= 95029；转换为对基本表的更新：转换为对基本表的更新：DELETEFROM StudentWHERE Sno= 95029 AND Sdept= IS；数据库系统原理厦门大学计算机科学系林子雨2017版更新视图的限制一
144、些视图是不可更新的，因为对这些视图的更一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的新不能唯一地有意义地转换成对相应基本表的更新更新(对两类方法均如此对两类方法均如此)例：视图例：视图S_G为不可更新视图。为不可更新视图。 CREATE VIEW S_G (Sno，Gavg) AS SELECT Sno，AVG(Grade)FROM SCGROUP BY Sno；数据库系统原理厦门大学计算机科学系林子雨2017版更新视图（续）对于如下更新语句：对于如下更新语句：UPDATE S_GSET Gavg=90WHERE Sno= 95001；无论实体化法还是消解法都
145、无法将其转换成对无论实体化法还是消解法都无法将其转换成对基本表基本表SC的更新的更新数据库系统原理厦门大学计算机科学系林子雨2017版3.6视图3.6.1 定义视图定义视图3.6.2 查询视图查询视图3.6.3 更新视图更新视图3.6.4 视图的作用视图的作用数据库系统原理厦门大学计算机科学系林子雨2017版1.视图能够简化用户的操作当视图中数据不是直接来自基本表时，定义视图能够简当视图中数据不是直接来自基本表时，定义视图能够简化用户的操作化用户的操作基于多张表连接形成的视图基于多张表连接形成的视图基于复杂嵌套查询的视图基于复杂嵌套查询的视图含导出属性的视图含导出属性的视图数据库系统原理厦门大
146、学计算机科学系林子雨2017版2.视图使用户能以多种角度看待同一数据视图机制能使不同用户以不同方式看待同视图机制能使不同用户以不同方式看待同一数据，适应数据库共享的需要一数据，适应数据库共享的需要数据库系统原理厦门大学计算机科学系林子雨2017版3.视图对重构数据库提供了一定程度的逻辑独立性例：数据库逻辑结构发生改变例：数据库逻辑结构发生改变学生关系学生关系Student(Sno，Sname，Ssex，Sage，Sdept) “垂直垂直”地分成两个基本表：地分成两个基本表： SX(Sno，Sname，Sage) SY(Sno，Ssex，Sdept)数据库系统原理厦门大学计算机科学系林子雨201
147、7版3.视图对重构数据库提供了一定程度的逻辑独立性通过建立一个视图通过建立一个视图Student：CREATE VIEW Student(Sno，Sname，Ssex，Sage，Sdept)AS SELECT SX.Sno，SX.Sname，SY.Ssex，SX.Sage，SY.SdeptFROM SX，SYWHERE SX.Sno=SY.Sno；使用户的外模式保持不变，从而对原使用户的外模式保持不变，从而对原Student表表的查询程序不必修改的查询程序不必修改数据库系统原理厦门大学计算机科学系林子雨2017版3.视图对重构数据库提供了一定程度的逻辑独立性视图在一定程度上保证了数据的逻辑独立
148、性视图在一定程度上保证了数据的逻辑独立性视图只能在一定程度上提供数据的逻辑独立性视图只能在一定程度上提供数据的逻辑独立性由于对视图的更新是有条件的，因此应用程序中修改由于对视图的更新是有条件的，因此应用程序中修改数据的语句可能仍会因基本表结构的改变而改变。数据的语句可能仍会因基本表结构的改变而改变。对不同用户定义不同视图，使每个用户只能看到对不同用户定义不同视图，使每个用户只能看到他有权看到的数据他有权看到的数据数据库系统原理厦门大学计算机科学系林子雨2017版作业教材（第教材（第5版）版）130页页 第第4、5题题数据库系统原理厦门大学计算机科学系林子雨2017版附录：主讲教师单位：厦门大学计算机科学系E-mail:个人网页：http:/ of Computer Science, Xiamen University, 2017
