《数据库课件第三章》由会员分享，可在线阅读，更多相关《数据库课件第三章（338页珍藏版）》请在金锄头文库上搜索。
1、第3章关系数据库标准语言SQL3.1SQL概述3.2学生课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.7小结关系数据库标准语言SQL(续)结构化查询语言，是关系数据库的标准语言是一个通用的、功能极强的关系数据库语言第3章关系数据库标准语言SQL3.1SQL概述3.2学生课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.7小结3.1SQL概述3.1.1SQL的产生与发展3.1.2SQL的特点3.1.3SQL的基本概念3.1SQL概述3.1.1SQL的产生与发展3.1.2SQL的特点3.1.3SQL的基本概念3.1.1SQL的产生与发展SQL语言(Structu
2、redQueryLanguage)1974年由Boyce和Chamberlin提出1975年1979年IBM公司在SystemR原型系统上实现是关系数据库的标准语言，是数据库领域中一个主流语言SQL的产生与发展(续)SQL标准SQL-86第一个SQL标准由美国国家标准局（AmericanNationalStandardInstitute，简称ANSI）公布1987年 国 际 标 准 化 组 织 （ InternationalOrganizationforStandardization，简称ISO）通过SQL-89SQL-92SQL99SQL20033.1SQL概述3.1.1SQL的产生与发展3
3、.1.2SQL的特点3.1.3SQL的基本概念3.1.2SQL的特点综合统一2.高度非过程化3.面向集合的操作方式4.同一种语法结构提供多种使用方式5.语言简捷，易学易用综合统一SQL语言集数据查询、数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体非关系模型的数据语言模式数据定义语言（模式DDL）外模式数据定义语言（外模式DDL或子模式DDL）与数据存储有关的描述语言（DSDL）数据操纵语言（DML）2.高度非过程化用户只需提出“做什么”，而不必指明“怎么做”存取路径的选择以及SQL语句的操作过程由系统自动完成。大大减轻了用户负担，而且有利于提高数据独立性。3.面向集合的
4、操作方式SQL语言采用集合操作方式 操作对象、查找结果可以是元组的集合 一次插入、删除、更新操作的对象可以是元组的集合非关系数据模型采用的是面向记录的操作方式，操作对象是一条记录。4.同一种语法结构提供多种使用方式独立语言能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作嵌入式语言能够嵌入到高级语言（例如C，COBOL，FORTRAN，PL/1）程序中，供程序员设计程序时使用。两种不同使用方式下，SQL语言的语法结构基本一致5.语言简捷，易学易用3.1SQL概述3.1.1SQL的产生与发展3.1.2SQL的特点3.1.3SQL的基本概念3.1.3SQL语言
5、的基本概念SQL语言的基本概念（续）用户用SQL语言对基本表和视图进行操作基本表本身独立存在的表，一个关系对应一个表一个（或多个）基本表对应一个存储文件一个表可以带若干索引，索引也存放在存储文件中存储文件存储文件的逻辑结构组成了关系数据库的内模式存储文件的物理结构是任意的，对用户是透明的视图从一个或几个基本表或视图导出的表是虚表，只存放视图的定义而不存放对应数据第3章关系数据库标准语言SQL3.1SQL概述3.2学生课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.7小结3.2学生课程数据库学生课程数据库中包括以下3个表：-学生表：Student(Sno,Sname,Ssex,
6、Sage,Sdept)-课程表：Course(Cno,Cname,Cpno,Ccredit)-学生选课表：SC(Sno,Cno,Grade)关系的主码加下划线表示。学生课程数据库（续）学生表：学号学号Sno姓名姓名Sname性别性别Ssex年龄年龄Sage所在系所在系Sdept200215121李勇李勇男男20CS200215122刘晨刘晨女女19CS200215123王敏王敏女女18MA200215125张立张立男男19IS学生课程数据库（续）课程表：课程号课程号Cno课程名课程名Cname先行课先行课Cpno学分学分Ccredit1数据库数据库542数学数学23信息系统信息系统144操作系
7、统操作系统635数据结构数据结构746数据处理数据处理27PACAL语言语言64学生课程数据库（续）学生选课表：学号学号Sno课程号课程号Cno成绩成绩Grade200215121192200215121285200215121388200215122290200215122380第3章关系数据库标准语言SQL3.1SQL概述3.2学生课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.7小结数据定义概述SQL的数据定义功能定义模式创建模式删除模式定义表创建表删除表修改表定义数据定义概述(续)SQL的数据定义功能（续）定义视图(外模式)创建视图删除视图间接修改视图定义：删除+创建
8、定义索引(内模式)创建索引删除索引间接修改索引定义：删除+创建数据定义概述(续)表3.2SQL的数据定义语句操作操作对象对象操作方式操作方式创建创建删除删除修改修改模式模式CREATE SCHEMADROP SCHEMA表表CREATE TABLEDROP TABLEALTER TABLE视图视图CREATE VIEWDROP VIEW索引索引CREATE INDEXCREATE INDEXDROP INDEX3.3数据定义3.3.1模式的定义与删除3.3.2基本表的定义、删除与修改3.3.3索引的建立与删除3.3数据定义3.3.1模式的定义与删除3.3.2基本表的定义、删除与修改3.3.3索
9、引的建立与删除3.3.1模式的定义与删除1.定义模式2.删除模式3.3.1模式的定义与删除1.定义模式2.删除模式一、定义模式模式定义语句为：CREATESCHEMAAUTHORIZATION-若没有指定，则隐含为-要创建模式，调用该命令的用户必须拥有DBA权限，或者获得了DBA授予的CREATESCHEMA的权限定义模式（续）例1定义一个学生课程模式STCREATESCHEMA“S-T”AUTHORIZATIONWANG;为用户WANG定义了一个模式S-T例2CREATESCHEMAAUTHORIZATIONWANG;语句没有指定，则隐含为用户名WANG定义模式（续）定义模式实际上定义了一个
10、命名空间，在这个空间中可以进一步定义该模式包含的数据库对象，例如基本表、视图、索引等目前，在CREATESCHEMA中可以接受CTEATETABLE,CREATEVIEWTGRANT子句。即可以在创建模式的同时在这个模式定义中进一步创建基本表、视图，定义授权CREATESCHEMAAUTHORIZATION定义模式（续）例3CREATESCHEMATESTAUTHORIZATIONZHANGCREATETABLETAB1(COL1SMALLINT,COL2INT,COL3CHAR(20),COL4NUMERIC(10,3)COL5DECIMAL(5,2);为用户ZHANG创建了一个模式TEST
11、，并且在其中定义了一个表TAB1.3.3.1模式的定义与删除1.定义模式2.删除模式二、删除模式删除模式语句如下：DROPSCHEMA-选择了CASCADE（级联），表示在删除模式的同时把该模式中所有的数据库对象全部一起删除。-选择了RESTRICT（限制），表示若该模式中已经定义了下属的数据库对象，则拒绝该删除语句的执行，只有当该模式中没有任何下属的对象时才能执行删除语句。删除模式（续）例4DROPSCHEMAZHANGCASCADE该语句删除了模式ZHANG，同时该模式中已经定义的表TAB1也被删除了。3.3数据定义3.3.1模式的定义与删除3.3.2基本表的定义、删除与修改3.3.3索引
12、的建立与删除3.3.2基本表的定义、删除与修改一、定义基本表二、数据类型三、模式与表四、修改基本表五、删除基本表3.3.2基本表的定义、删除与修改一、定义基本表二、数据类型三、模式与表四、修改基本表五、删除基本表一、定义基本表语句格式CREATETABLE（，）；：所要定义的基本表的名字：组成该表的各个属性（列）：涉及相应属性列的完整性约束条件：涉及一个或多个属性列的完整性约束条件定义基本表（续）表级完整性约束与列级完整性约束常用完整性约束主码约束：PRIMARYKEY参照完整性约束：FOREIGNKEY属性列名REFERENCES表名（列名）唯一性约束：UNIQUE非空值约束：NOTNULL
13、取值约束：CHECK例题例5 建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。 CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，SnameCHAR(20)UNIQUE，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20)；例题（续）例6 建立一个“课程”表Course,由课程号Cno,课程名Cname,先行课Cpno和学分Ccredit组成，Cno为主码。 CREATE TABLE Course (Cno CHAR
14、(4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), Ccredit SMALLINT, FOREIGN KEY Cpno REFERENCES Course(Cno);例题（续）例7 建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno, Cno)为主码。CREATE TABLE SC( Sno CHAR(9) , Cno CHAR(4) , Grade SMALLINT, PRIMARY KEY (Sno, Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno), FO
15、REIGN KEY (Cno) REFERENCES Course(Cno);3.3.2基本表的定义、删除与修改一、定义基本表二、数据类型三、模式与表四、修改基本表五、删除基本表二、数据类型关系模型中每一个属性来自一个域，它的取值必须是域中的值SQL中域的概念用数据类型来实现。定义表的各个属性时需要指明其数据类型及长度一个属性选用哪种数据类型要根据实际情况来决定，般要从两个方面考虑，一是数值范围，二是要做哪些运算数据类型（续）SQL提供的主要数据类型，如下所示：数据类型含义CHAR(n)长度为n的定长字符串VARCHAR(n)最大长度为n的变长字符串INT长整数（也可以写作INTEGER)SM
16、ALLINT短整数NUMERIC(p,d)定点数，由p位数字组成，小数后面有d位数字REAL取决于机器精度的浮点数DoublePrecision取决于机器精度的双精度浮点数FLOAT(n)浮点数，精度至少为n位数字DATE日期，包含年、月、日，格式为YYYY-MM-DDTIME时间，包含一日的时、分、秒，格式为HH:MM:SS数据类型（续）数据类型不同的数据库系统支持的数据类型不完全相同IBMDB2SQL支持的数据类型SMALLINT半字长二进制整数。INTEGER或INT全字长二进制整数。DECIMAL(p,q)压缩十进制数，共p位，其中小数或DEC(p,q)点后有q位。0qp15，q=0时
17、可以省略不写。FLOAT双字长浮点数。数据类型（续）数据类型（续）CHARTER(n)长度为n的定长字符串。或CHAR(n)VARCHAR(n)最大长度为n的变长字符串。GRAPHIC(n)长度为n的定长图形字符串。VARGRAPHIC(n)最大长度为n的变长图形字符串。DATE日期型，格式为YYYY-MM-DD。TIME时间型，格式为HH.MM.SS。TIMESTAMP日期加时间。数据类型（续）ORACLESQL支持的数据类型VARCHAR2(n)CHAR(n)NUMBER(p,q)DATELONGRAW或LONGRAW数据类型（续）KingBaseISQL支持的数据类型字符串类型CHARA
18、CTER(长度)最大长度为256个字符CHAR(长度)CHARACTIC的同义词CHARACTERVARYING(n)VARCHAR(n)TEXT大文本数据类型（续）KingBaseISQL支持的数据类型精确数值类型NUMERIC(精度,标度)标度值应小于精度值标度缺省为0,精度缺省为10DECIMAL(精度,标度)DEC(精度,标度)DECIMAL的同义词INTEGERINTINTEGER的同义词SMALLINT数据类型（续）KingBaseISQL支持的数据类型近似数值类型FLOAT(精度)可选精度浮点数REAL单精度浮点数DOUBLEPRECISION双精度浮点数DOUBLE同义词数据类
19、型（续）KingBaseISQL支持的数据类型日期时间类型DATE日期TIMEWITHTIMEZONE时间TIMETZTIMEWITHOUTTIMEZONETIMETIMESTAMPWITHTIMEZONE日期和时间TIMESTAMPTZTIMESTAMPWITHOUTTIMEZONETIMESTAMPINTERVAL通用的时间间隔数据类型（续）KingBaseISQL支持的数据类型位串BIT(n)定长位串BITVARYING(n)变长位串BINARYLARGEOBJECT二进制大对象BLOBBINARYLARGEOBJECT的同义词BYTEA二进制位串布尔型BOOLEAN逻辑布尔量BOOLB
20、OOLEAN的同义词3.3.2基本表的定义、删除与修改一、定义基本表二、数据类型三、模式与表四、修改基本表五、删除基本表三、模式与表每一个基本表都属于某一个模式，一个模式包含多个基本表定义基本表时定义它所属的模式有三种方法：-方法一：在表名中明显地给出模式名。如：CREATETABLE“S-T”.Student(); - 方法二：在创建模式语句中同时创建表，如例3所示。 - 方法三：设置所属的模式，这样在创建表时表名中不必给出模式名模式与表（续）当用户创建基本表时若没有指定模式，系统根据搜索路径来确定该对象所属的模式搜索路径包含一组模式列表，RDBMS会使用模式列表中第一个存在的模式作为数据库
21、对象的模式名显示当前搜索路径的语句：SHOWsearch_path模式与表（续）搜索路径的当前默认值是：$user,PUBLIC。含义是首先搜索与用户名相同的模式名，若该模式名不存在，则使用PUBLIC模式DBA用户也可以设置搜索路径，例如：SETsearch_pathTO“S-T”,PUBLIC3.3.2基本表的定义、删除与修改一、定义基本表二、数据类型三、模式与表四、修改基本表五、删除基本表四、修改基本表语句格式ALTERTABLEADD完整性约束DROPALTERCOLUMN；：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的完整性约束条件ALTERCOLU
22、MN子句：用于修改原有的列定义，包括修改列名和数据类型例题例8 向Student表增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDS_entranceDATE；不论基本表中原来是否已有数据，新增加的列一律为空值。如果基本表中原来已有数据，新增列不可有NOTNULL约束例题例9将年龄的数据类型由字符型改为整数。ALTERTABLEStudentALTERCOLUMNSageINT；注：修改原有的列定义有可能会破坏已有数据。例题例10增加课程名称必须取唯一值的约束条件。ALTERTABLECourseADDUNIQUE(Cname)；3.3.2基本表的定义、删除与修
23、改一、定义基本表二、数据类型三、模式与表四、修改基本表五、删除基本表五、删除基本表语句格式DROPTABLERESTRICTCASCADE;选择RESTRICT:则该表的删除是有限制条件的。欲删除的基本表不能被其他表的约束所引用，不能有视图，不能有触发器，不能有存储过程或函数等若选择CASCADE:则该表的删除没有限制条件。在删除表的同时，相关的依赖对象都将被一起删除缺省情况是RESTRICT例题例11删除Student表。DROPTABLEStudent;基本表定义一旦被删除，不仅表中的数据和此表的定义将被删除，而且此表上建立的索引、视图、触发器等有关对象一般也都将被删除。3.3数据定义3.
24、3.1模式的定义与删除3.3.2基本表的定义、删除与修改3.3.3索引的建立与删除3.3.3索引的建立与删除建立索引是加快查询速度的有效手段建立索引DBMS自动建立PRIMARYKEYUNIQUEDBA或表的属主（即建立表的人）根据需要建立维护索引DBMS自动完成使用索引DBMS自动选择是否使用索引以及使用哪些索引一、建立索引语句格式CREATEUNIQUECLUSTERINDEXON(,)；用指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录
25、CLUSTER表示要建立的索引是聚簇索引建立索引（续）唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束。建立索引（续）聚簇索引建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致。例13：CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放建立索引（续）
26、在一个基本表上最多只能建立一个聚簇索引聚簇索引的用途：对于某些类型的查询，可以提高查询效率聚簇索引的适用范围很少对基表进行增删操作很少对其中的变长列进行修改操作例题例14为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；二、
27、删除索引语句格式DROPINDEX；删除索引时，系统会从数据字典中删去有关该索引的描述。例题例15删除Student表的Stusname索引。DROPINDEXStusname；第3章关系数据库标准语言SQL3.1SQL概述3.2学生课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.7小结3.4数据查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.3.4集合查询3.4.5SELECT语句的一般格式查询（续）语句格式SELECTALL|DISTINCT，FROM，WHEREGROUPBYHAVINGORDERBYASC|DESC；语句格式SELECT子句：指定要显示的
28、属性列FROM子句：指定查询对象(基本表或视图)WHERE子句：指定查询条件GROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。HAVING短语：筛选出满足指定条件的组ORDERBY子句：对查询结果表按指定列值的升序或降序排序3.4数据查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.3.4集合查询3.4.5SELECT语句的一般格式3.4.1单表查询单表查询查询仅涉及一个表，是一种最简单的查询操作选择表中的若干列选择表中的若干元组ORDERBY子句聚集函数GROUPBY子句一、选择表中的若干列属投影运算变化方式主要表现在SELE
29、CT子句的上 查询指定列 查询全部列 查询经过计算的值1.查询指定列方法在SELECT子句的中指定要查询的属性 中各个列的先后顺序可以与表中的逻辑顺序不一致。即用户可以根据应用的需要改变列的显示顺序例题例1查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；例2查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；2.查询全部列方法在SELECT关键字后面列出所有列名当列的显示顺序与其在基表中的顺序相同时，也可以简单地将指定为*例题例3查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，Sdept
30、FROMStudent；或SELECT*FROMStudent；3.查询经过计算的值方法SELECT子句的为表达式算术表达式字符串常量函数列别名例题例4查全体学生的姓名及其出生年份。SELECTSname，2004-SageFROMStudent；输出结果：Sname2004-Sage-李勇1984刘晨1985王敏1986张立1985例题（续）例5查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示所在系名。SELECTSname，YearofBirth:，2004-Sage，LOWER(Sdept)FROMStudent；例题（续）输出结果：SnameYearofBirth:2002
31、-SageISLOWER(Sdept)-李勇YearofBirth:1984cs刘晨YearofBirth:1985cs王敏YearofBirth:1986ma张立YearofBirth:1985is例题（续）可以通过指定列别名改变查询结果的列标题SELECTSnameNAME，YearofBirth:BIRTH，2002-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROMStudent；输出结果：NAMEBIRTHBIRTHDAYDEPARTMENT-李勇YearofBirth:1984cs刘晨YearofBirth:1985cs王敏YearofBirth:1
32、986ma张立YearofBirth:1985is二、选择表中的若干元组消除取值重复的行查询满足条件的元组1.消除取值重复的行方法在SELECT子句中使用DISTINCT短语例题例6查询选修了课程的学生学号。(1)SELECTSnoFROMSC;结果：Sno-200215121200215121200215121200215122200215122例题（续）(2) 若 想 去 掉 结 果 表 中 的 重 复 行 ， 必 须 指 定DISTINCT关键词SELECTDISTINCTSnoFROMSC;结果：Sno-200215121200215122例题（续）注意DISTINCT短语的作用范围是
33、所有目标列例：查询选修课程的各种成绩错误的写法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正确的写法SELECTDISTINCTCno，GradeFROMSC;2.查询满足条件的元组属选择运算通过WHERE子句实现 比较大小 确定范围 确定集合 字符匹配 涉及空值的查询 多重条件查询查询满足条件的元组（续）WHERE子句常用的查询条件子句常用的查询条件(1)比较大小方法在WHERE子句的中使用比较运算符=，=，=，!=或，!，!，逻辑运算符NOT+含上述比较运算符的表达式例题例7查询计算机系全体学生的名单。SELECTSnameFROMStudentWHERES
34、dept=CS；例题例8查询所有年龄在20岁以下的学生姓名及其年龄。SELECTSname，SageFROMStudentWHERESage=20；例题（续）例9查询考试成绩有不及格的学生的学号。SELECTDISTINCTSnoFROMSCWHEREGrade60；(2)确定范围方法使用谓词BETWEENANDNOTBETWEENANDBETWEEN后：范围的下限（即低值）AND后：范围的上限（即高值）用多重条件查询实现例题例10查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageBETW
35、EEN20AND23；例题（续）例11查询年龄不在2023岁之间的学生姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；(3)确定集合l方法使用谓词INNOTIN：用逗号分隔的一组取值用多重条件查询实现例题例12查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN(IS，MA，CS);例题例13查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname，SsexFROMStuden
36、tWHERESdeptNOTIN(IS，MA，CS);(4)字符匹配方法使用谓词LIKE或NOTLIKENOTLIKEESCAPE：指定匹配模板w匹配模板：固定字符串或含通配符的字符串w当匹配模板为固定字符串时，可以用=运算符取代LIKE谓词，用!=或运算符取代NOTLIKE谓词字符匹配(续)例题：匹配模板为固定字符串例14查询学号为200215121的学生的详细情况。SELECT*FROMStudentWHERESnoLIKE200215121；等价于：SELECT*FROMStudentWHERESno=200215121；字符匹配(续)w通配符%(百分号)代表任意长度（长度可以为0）的字
37、符串。例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串。_(下横线)代表任意单个字符。例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串。字符匹配(续)例题：匹配模板为含通配符的字符串例15查询所有姓刘学生的姓名、学号和性别。SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE刘%；字符匹配(续)匹配模板为含通配符的字符串（续）例16查询姓欧阳且全名为三个汉字的学生的姓名。SELECTSnameFROMStudentWHERESnameLIKE欧阳_；字符匹配(续)匹配模板
38、为含通配符的字符串（续）例17查询名字中第2个字为阳字的学生的姓名和学号。SELECTSname，SnoFROMStudentWHERESnameLIKE_阳%；字符匹配(续)匹配模板为含通配符的字符串（续）例18查询所有不姓刘的学生姓名。SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE刘%；字符匹配(续)ESCAPE短语：当用户要查询的字符串本身就含有%或_时，要使用ESCAPE短语对通配符进行转义。字符匹配(续)例题：使用换码字符将通配符转义为普通字符例19查询DB_Design课程的课程号和学分。SELECTCno，CcreditFROMC
39、ourseWHERECnameLIKEDB_Design字符匹配(续)使用换码字符将通配符转义为普通字符(续)例19(续)SELECTCno，CcreditFROMCourseWHERECnameLIKEDB_DesignESCAPE字符匹配(续)使用换码字符将通配符转义为普通字符(续)例20查询以DB_开头，且倒数第3个字符为i的课程的详细情况。SELECT*FROMCourseWHERECnameLIKEDB_%i_ESCAPE；(5)涉及空值的查询方法使用谓词ISNULL或ISNOTNULL“ISNULL”不能用“=NULL”代替例题例21某些学生选修课程后没有参加考试，所以有选课记录，
40、但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECTSno，CnoFROMSCWHEREGradeISNULL；例题(续)例22查所有有成绩的学生学号和课程号。SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；(6)多重条件查询方法用逻辑运算符AND和OR来联结多个查询条件AND的优先级高于OR可以用括号改变优先级可用来实现多种其他谓词NOTINNOTBETWEENAND例题例23查询计算机系年龄在20岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept=CSANDSage=20ANDSage=23；三、ORDERBY子
41、句方法 使用ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序 当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示例题例24查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECTSno，GradeFROMSCWHERECno=3ORDERBYGradeDESC；例题例25查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。SELECT*FROMStudentORDERBYSdept，SageDESC；四、聚集函数方法5类主要聚集函数计数COUNT（DISTINCT|ALL*
42、）COUNT（DISTINCT|ALL）计算总和SUM（DISTINCT|ALL）计算平均值AVG（DISTINCT|ALL）聚集函数（续）5类主要聚集函数(续)求最大值MAX（DISTINCT|ALL）求最小值MIN（DISTINCT|ALL）DISTINCT短语：在计算时要取消指定列中的重复值ALL短语：不取消重复值ALL为缺省值例题例26查询学生总人数。SELECTCOUNT(*)FROMStudent；例27查询选修了课程的学生人数。SELECTCOUNT(DISTINCTSno)FROMSC；注：用DISTINCT以避免重复计算学生人数例题例28计算1号课程的学生平均成绩。SELEC
43、TAVG(Grade)FROMSCWHERECno=1；例29查询选修1号课程的学生最高分数。SELECTMAX(Grade)FROMSCWHERCno=1；五、GROUPBY子句用途 细化聚集函数的作用对象未对查询结果分组，集函数将作用于整个查询结果对查询结果分组后，集函数将分别作用于每个组GROUPBY子句（续）方法（参照后面例题）使用GROUPBY子句分组分组方法：按指定的一列或多列值分组，值相等的为一组使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性和集函数GROUPBY子句的作用对象是查询的中间结果表例题例30求各个课程号及相应的选课人数。SELECTCno，C
44、OUNT(Sno)FROMSCGROUPBYCno；结果CnoCOUNT(Sno)122234344433548例题例31求各个课程号及相应的课程成绩在90分以上的学生人数。SELECTCno，COUNT(Sno)FROMSCWHEREGrade=90GROUPBYCno；结果CnoCOUNT(Sno)113274358GROUPBY子句（续）方法(续)使用HAVING短语筛选最终输出结果只有满足HAVING短语指定条件的组才输出HAVING短语与WHERE子句的区别：作用对象不同WHERE子句作用于基表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。例题例32
45、查询选修了3门以上课程的学生学号。SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)3；3.4数据查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.3.4集合查询3.4.5SELECT语句的一般格式3.4.2连接查询概述同时涉及多个表的查询称为连接查询连接条件用来连接两个表的条件称为连接条件或连接谓词常用格式.比较运算符：=、=、=、!=.BETWEEN.AND.连接查询（续）连接字段连接谓词中的列名称为连接字段连接条件中的各连接字段类型必须是可比的，但不必是相同的连接查询（续）连接操作的执行过程嵌套循环法(NESTED-LOOP)首先在表1中找到第一个
46、元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕为止。连接查询（续）排序合并法(SORT-MERGE)：常用于=连接首先按连接属性对表1和表2排序对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组
47、时，对表2的查询不再继续连接查询（续）排序合并法(续)找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续重复上述操作，直到表1或表2中的全部元组都处理完毕为止连接查询（续）索引连接(INDEX-JOIN)对表2按连接字段建立索引对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组连接查询（续）SQL中连接查询的主要类型广义笛卡尔积 等值连接
48、(含自然连接) 非等值连接查询 自身连接查询 外连接查询 复合条件连接查询连接查询（续）一、广义笛卡尔积二、等值与非等值连接查询三、自身连接查询四、外连接查询五、复合条件连接查询一、广义笛卡尔积不带连接谓词的连接很少使用例：SELECTStudent.*,SC.*FROMStudent,SC二、等值与非等值连接查询等值连接自然连接非等值连接二、等值与非等值连接查询等值连接连接运算符为=的连接操作.=.任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。等值与非等值连接查询（续）例33查询每个学生及其选修课程的情况。SELECTStudent.*，S
49、C.*FROMStudent，SCWHEREStudent.Sno=SC.Sno；等值与非等值连接查询（续）结果表Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade200215121李勇男20CS200215121192200215121李勇男20CS200215121285200215121李勇男20CS200215121388200215122刘晨女19IS200215122290200215122刘晨女19IS200215122380等值与非等值连接查询（续）自然连接等值连接的一种特殊情况，把目标列中重复的属性列去掉。.=.SELECT语句不能直接实现
50、自然连接例34对例33用自然连接完成。SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；等值与非等值连接查询（续）非等值连接 连接运算符不为=的连接操作.比较运算符：、=、=、!=.BETWEEN.AND.三、自身连接一个表与其自己进行连接，称为表的自身连接表示方法需要给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀自身连接（续）例34查询每一门课的间接先修课（即先修课的先修课）。SELECTFIRST.Cno，SECOND.CpnoFROMCourse
51、FIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；自身连接（续）结果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数据库数据库 5 4 2数学数学 2 3信息系统信息系统 1 4 4操作系统操作系统 6 3 5数据结构数据结构 7 4 6数据处理数据处理 2 7PA
52、SCAL语言语言 6 4自身连接（续）查询结果 173556cnocpno四、外连接（OutJoin）外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出外连接（续）外连接操作非主体表有一“万能”的虚行，该行全部由空值组成虚行可以和主体表中所有不满足连接条件的元组进行连接由于虚行各列全部是空值，因此与虚行连接的结果中，来自非主体表的属性值全部是空值外连接（续）外连接操作的种类 左外连接(LEFTOUTJOIN)外连接符出现在连接条件的左边 右外连接(RIGHTOUTJOIN)外连接符出现在连接条件的右边外连接（续）例36
53、：用左外连接操作改写例33SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudentLEFTOUTJOINSCONStudent.Sno=SC.Sno；也可以使用USING来去掉结果中的重复值：FROMStudentLEFTOUTJOINSCUSING(Sno);外连接（续）结果：Student.SnoSnameSsexSageSdeptCnoGrade200215121李勇男20CS192200215121李勇男20CS285200215121李勇男20CS388200215122刘晨女19IS290200215122刘晨女19I
54、S380200215123王敏女18MANULLNULL200215125张立男19ISNULLNULL五、复合条件连接WHERE子句中含多个连接条件时，称为复合条件连接复合条件连接的类型 两表按多个属性连接 自身按多个属性连接 多表连接复合条件连接（续）例37 查询选修2号课程且成绩在90分以上的所有学生。SELECTStudent.Sno,SnameFROMStudent，SCWHEREStudent.Sno=SC.Sno/*连接谓词*/ANDSC.Cno=2/*其他限定条件*/ANDSC.Grade90；/*其他限定条件*/复合条件连接（续）例38查询每个学生的学号、姓名、选修的课程名及
55、成绩。SELECTStudent.Sno，Sname，Cname，GradeFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoandSC.Cno=Course.Cno；结果：Student.SnoSnameCnameGrade200215121李勇数据库92200215121李勇数学85200215121李勇信息系统88200215122刘晨数学90200215122刘晨信息系统803.4数据查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.3.4集合查询3.4.5SELECT语句的一般格式3.4.3嵌套查询嵌套查询概述嵌套查询分类嵌套查询求解
56、方法引出子查询的谓词嵌套查询(续)嵌套查询概述一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询嵌套查询(续)例SELECTSname外层查询/父查询FROMStudentWHERESnoIN（SELECTSno内层查询/子查询FROMSCWHERECno=2）；嵌套查询(续)子查询的限制不能使用ORDERBY子句层层嵌套方式反映了SQL语言的结构化有些嵌套查询可以用连接运算替代嵌套查询(续)嵌套查询分类不相关子查询子查询的查询条件不依赖于父查询相关子查询子查询的查询条件依赖于父查询嵌套查询(续)嵌
57、套查询求解方法不相关子查询是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。嵌套查询(续)嵌套查询求解方法（续）相关子查询首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止。嵌套查询(续)引出子查询的谓词带有IN谓词的子查询带有比较运算符的子查询带有ANY(SOME)或ALL谓词的子查询带有EXISTS谓词的子查询一、带有IN谓词的子查询例39查询与“刘晨”在同一个系学习的学生。此查询要求可以分步来完成确定“
58、刘晨”所在系名SELECTSdeptFROMStudentWHERESname=刘晨；结果为：SdeptCS带有IN谓词的子查询（续）查找所有在CS系学习的学生。SELECTSno，Sname，SdeptFROMStudentWHERESdept=CS；结果为：SnoSnameSdept200215121李勇CS200215122刘晨CS带有IN谓词的子查询（续）构造嵌套查询将第一步查询嵌入到第二步查询的条件中SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=刘晨)；此查询为不相关子查询。
59、DBMS求解该查询时也是分步去做的。带有IN谓词的子查询（续）用自身连接完成本查询要求SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1，StudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname=刘晨；带有IN谓词的子查询（续）父查询和子查询中的表均可以定义别名SELECTSno，Sname，SdeptFROMStudentS1WHERES1.SdeptIN(SELECTSdeptFROMStudentS2WHERES2.Sname=刘晨)；带有IN谓词的子查询（续）例40查询选修了课程名为“信息系统”的学生学号和姓名嵌套查询SE
60、LECTSno，Sname最后在Student关系中FROMStudent取出Sno和SnameWHERESnoIN(SELECTSno然后在SC关系中找出选FROMSC修了3号课程的学生学号WHERECnoIN(SELECTCno首先在Course关系中找出“信FROMCourse息系统”的课程号，结果为3号WHERECname=信息系统);带有IN谓词的子查询（续）连接查询SELECTStudent.Sno，SnameFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=信息系统；二、
61、带有比较运算符的子查询使用范围当能确切知道内层查询返回单值时，可用比较运算符（，=，=，!=或）。与ANY或ALL谓词配合使用带有比较运算符的子查询（续）在例39中，由于一个学生只可能在一个系学习，即内查询的结果是一个值，因此可以用=代替IN：SELECTSno，Sname，SdeptFROMStudentWHERESdept=（SELECTSdeptFROMStudentWHERESname=刘晨）；带有比较运算符的子查询（续）子查询一定要跟在比较符之后错误的例子：SELECTSno，Sname，SdeptFROMStudentWHERE(SELECTSdeptFROMStudentWHER
62、ESname=刘晨)=Sdept；带有比较运算符的子查询（续）例41找出每个学生超过他选修课程平均成绩的课程号SELECTSno,CnoFROMSCxWHEREGrade=(SELECTAVG(Grade)FROMSCyWHEREy.Sno=x.Sno);三、带有ANY(SOME)或ALL谓词的子查询谓词语义ANY(SOME)：任意一个值ALL：所有值带有ANY或ALL谓词的子查询（续）需要配合使用比较运算符ANY大于子查询结果中的某个值ALL大于子查询结果中的所有值ANY小于子查询结果中的某个值=ANY大于等于子查询结果中的某个值=ALL大于等于子查询结果中的所有值=ANY小于等于子查询结果
63、中的某个值=ALL小于等于子查询结果中的所有值=ANY等于子查询结果中的某个值=ALL等于子查询结果中的所有值（通常没有实际意义）!=（或）ANY不等于子查询结果中的某个值!=（或）ALL不等于子查询结果中的任何一个值例题例42查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄SELECTSname，SageFROMStudentWHERESageANY(SELECTSageFROMStudentWHERESdept=CS)ANDSdeptCS;/*注意这是父查询块中的条件*/例题结果SnameSage王敏18张立19执行过程DBMS执行此查询时，首先处理子查询，找出IS系中所有学生的年
64、龄，构成一个集合(19，18)。然后处理父查询，找所有不是IS系且年龄小于19或18的学生。带有ANY(SOME)或ALL谓词的子查询（续）ANY和ALL谓词有时可以用集函数实现ANY与ALL与集函数的对应关系 = 或或!= =ANY IN - MAXMIN= MINALL - NOT IN MINMAX= MAX带有ANY(SOME)或ALL谓词的子查询（续）用集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数带有ANY或ALL谓词的子查询（续）例42：用聚集函数实现例42SELECTSname，SageFROMStudentWHERESage(SELECT
65、MAX(Sage)FROMStudentWHERESdept=CS)ANDSdeptCS;例题例43查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。方法一：用ALL谓词SELECTSname，SageFROMStudentWHERESageALL(SELECTSageFROMStudentWHERESdept=CS)ANDSdeptCS;例题方法二：用聚集函数SELECTSname，SageFROMStudentWHERESage(SELECTMIN(Sage)FROMStudentWHERESdept=CS)ANDSdeptCS;四、带有EXISTS谓词的子查询1.EXISTS谓
66、词2.NOTEXISTS谓词3.不同形式的查询间的替换4.相关子查询的效率5.用EXISTS/NOTEXISTS实现全称量词6.用EXISTS/NOTEXISTS实现逻辑蕴函带有EXISTS谓词的子查询(续)1. EXISTS谓词代表存在量词 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则返回真值若内层查询结果为空，则返回假值由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义2. NOT EXISTS谓词带有EXISTS谓词的子查询(续)例44 查询所有选
67、修了1号课程的学生姓名。 思路分析： 本查询涉及Student和SC关系。 在Student中依次取每个元组的Sno值，用此值去检查SC关系。 若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= 1，则取此Student.Sname送入结果关系。带有EXISTS谓词的子查询(续)用嵌套查询 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= 1 )； 带有EXISTS谓词的子查询(续)用连接运算SELECT SnameFROM Student
68、, SCWHERE Student.Sno=SC.Sno AND SC.Cno= 1;带有EXISTS谓词的子查询(续)例45 查询没有选修1号课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=1)；带有EXISTS谓词的子查询(续)3. 不同形式的查询间的替换一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。 带有
69、EXISTS谓词的子查询(续)例39可以用带EXISTS谓词的子查询替换： SELECT Sno，Sname，Sdept FROM Student S1 WHERE EXISTS (SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = 刘晨 )；带有EXISTS谓词的子查询(续)4. 相关子查询的效率由于带EXISTS量词的相关子查询只关心内层查询是否有返回值，并不需要查具体值，因此其效率并不一定低于其他形式的查询。不相关子查询的效率高于相关子查询的效率带有EXISTS谓词的子查询(续)相关子查询的效率可能高于连接查
70、询例：查询选修了课程的学生姓名法一： SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno)；带有EXISTS谓词的子查询(续)法二： SELECT Sname FROM Student，SC WHERE Student.Sno=SC.Sno；带有EXISTS谓词的子查询(续)法三： SELECT Sname FROM Student WHERE sno in (SELECT distinct sno FROM SC)；带有EXISTS谓词的子查询(续)5.用EXISTS/NOT EXIST
71、S实现全称量词(难点)SQL语言中没有全称量词 （For all）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词： (x)P ( x( P) 带有EXISTS谓词的子查询(续)例46 查询选修了全部课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno)；带有EXISTS谓词的子查询(续)6.用EXISTS/NOTEXISTS实现逻辑蕴函(难
72、点)SQL语言中没有蕴函(Implication)逻辑运算可以利用谓词演算将逻辑蕴函谓词等价转换为：pqpq(y)P q ( y( (P q) ( y( (pq) ( y(pq)带有EXISTS谓词的子查询(续)例47 查询至少选修了学生200215122选修的全部课程的学生号码。解题思路：用逻辑蕴函表达：查询学号为x的学生，对所有的课程y，只要200215122学生选修了课程y，则x也选修了y。形式化表示：用P表示谓词 “学生200215122选修了课程y”用q表示谓词 “学生x选修了课程y”则上述查询为: (y) p q 带有EXISTS谓词的子查询(续)变换后语义：不存在这样的课程y，学
73、生200215122选修了y，而学生x没有选。带有EXISTS谓词的子查询(续)用NOTEXISTS谓词表示：SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno=200215122ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno)；3.4数据查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.3.4集合查询3.4.5SELECT语句的一般格式3.4.4 集合查询标准SQL直接支持的集合操作种类并操作(UNION)
74、一般商用数据库支持的集合操作种类并操作(UNION)交操作(INTERSECT)差操作(MINUS)1并操作形式UNION参加UNION操作的各结果表的列数必须相同；对应项的数据类型也必须相同并操作（续）例48查询计算机科学系的学生及年龄不大于19岁的学生。方法一：SELECT*FROMStudentWHERESdept=CSUNIONSELECT*FROMStudentWHERESage=19；并操作（续）方法二：SELECTDISTINCT*FROMStudentWHERESdept=CSORSage=19；并操作（续）例49查询选修了课程1或者选修了课程2的学生。方法一：SELECTSn
75、oFROMSCWHERECno=1UNIONSELECTSnoFROMSCWHERECno=2；并操作（续）方法二：SELECTDISTINCTSnoFROMSCWHERECno=1ORCno=2；2交操作标准SQL中没有提供集合交操作，但可用其他方法间接实现。2交操作例50查询计算机科学系的学生与年龄不大于19岁的学生的交集本例实际上就是查询计算机科学系中年龄不大于19岁的学生SELECT*FROMStudentWHERESdept=CSANDSage=19；或SELECT*FROMStudentWHERESdept=CSINTERSECTSELECT*FROMStudentWHERESag
76、e19；或SELECT*FROMStudentWHERESdept=CSEXCEPTSELECT*FROMStudentWHERESage=19;4.对集合操作结果的排序ORDERBY子句只能用于对最终查询结果排序，不能对中间结果排序任何情况下，ORDERBY子句只能出现在最后对集合操作结果排序时，ORDERBY子句中用数字指定排序属性new对集合操作结果的排序（续）例53错误写法SELECT*FROMStudentWHERESdept=CSORDERBYSnoUNIONSELECT*FROMStudentWHERESage=19ORDERBYSno；new对集合操作结果的排序（续）正确写法S
77、ELECT*FROMStudentWHERESdept=CSUNIONSELECT*FROMStudentWHERESage=19ORDERBY1；new3.4数据查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.3.4集合查询3.4.5SELECT语句的一般格式3.3.5小结SELECT语句的一般格式SELECTALL|DISTINCT别名，别名FROM别名，别名WHEREGROUPBYHAVINGORDERBYASC|DESC；小结（续）目标列表达式目标列表达式格式(1).*(2).，.：由属性列、作用于属性列的集函数和常量的任意算术运算（+，-，*，/）组成的运算公式。小结（
78、续）集函数格式COUNTSUMAVG(DISTINCT|ALL)MAXMINCOUNT(DISTINCT|ALL*)小结（续）条件表达式格式（1）ANY|ALL(SELECT语句)小结（续）（2）NOTBETWEENAND (SELECT(SELECT语句)语句)小结（续）（3）(，)NOTIN(SELECT语句)小结（续）(4)NOTLIKE(5)ISNOTNULL(6)NOTEXISTS(SELECT语句)小结（续）（7）ANDANDOROR第3章关系数据库标准语言SQL3.1SQL概述3.2学生课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.7小结3.5数据更新3.5.
79、1插入数据3.5.2修改数据3.5.3删除数据3.5数据更新3.5.1插入数据3.5.2修改数据3.5.3删除数据3.5.1插入数据两种插入数据方式插入单个元组插入子查询结果1.插入单个元组语句格式INSERTINTO(，)VALUES(，)功能将新元组插入指定表中。插入单个元组（续）INTO子句指定要插入数据的表名及属性列属性列的顺序可与表定义中的顺序不一致没有指定属性列：表示要插入的是一条完整的元组，且属性列顺序与表定义中的顺序一致指定部分属性列：插入的元组在其余属性列上取空值VALUES子句提供的值必须与INTO子句匹配值的个数值的类型插入单个元组（续）DBMS在执行插入语句时会检查所插
80、元组是否破坏表上已定义的完整性规则实体完整性参照完整性用户定义的完整性对于有NOTNULL约束的属性列是否提供了非空值对于有UNIQUE约束的属性列是否提供了非重复值对于有值域约束的属性列所提供的属性值是否在值域范围内插入单个元组（续）例1将一个新学生记录（学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。INSERTINTOStudentVALUES(200215128，陈冬，男，IS，18)；或INSERTINTOStudent（Sno,Sname,Ssex,Sdept,Sage)VALUES(200215128，陈冬，男，IS，18)；
81、插入单个元组（续）例2将学生张成民的信息插入到Student表中。INSERTINTOStudentVALUES(200215126,张成民，男，18，CS）；插入单个元组（续）例3插入一条选课记录(200215128，1)。INSERTINTOSC(Sno，Cno)VALUES(200215128，1)；新插入的记录在Grade列上取空值或INSERTINTOSCVALUES(200215128，1，NULL)；2.插入子查询结果语句格式INSERTINTO(，)子查询；功能将子查询结果插入指定表中插入子查询结果（续）例4对每一个系，求学生的平均年龄，并把结果存入数据库。第一步：建表CREA
82、TETABLEDept_age(SdeptCHAR(15)/*系名*/Avg_ageSMALLINT)/*学生平均年龄*/插入子查询结果（续）第二步：插入数据INSERTINTODept_age(Sdept，Avg_age)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；3.5数据更新3.5.1插入数据3.5.2修改数据3.5.3删除数据3.5.2修改数据语句格式UPDATESET=，=WHERE；功能修改指定表中满足WHERE子句条件的元组修改数据（续）SET子句指定修改方式要修改的列修改后取值WHERE子句指定要修改的元组缺省表示要修改表中的所有元
83、组修改数据（续）DBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则实体完整性一些DBMS规定主码不允许修改用户定义的完整性NOTNULL约束UNIQUE约束值域约束修改数据（续）三种修改方式修改某一个元组的值修改多个元组的值带子查询的修改语句1.修改某一个元组的值例5将学生200215121的年龄改为22岁。UPDATEStudentSETSage=22WHERESno=200215121；2.修改多个元组的值例6将所有学生的年龄增加1岁。UPDATEStudentSETSage=Sage+1；3.带子查询的修改语句例7将计算机科学系全体学生的成绩置零。UPDATESCSET
84、Grade=0WHERECS=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；带子查询的修改语句（续）例7解法2UPDATESCSETGrade=0WHERESNOin(SELETESnoFROMStudentWHERESdept=CS)；3.5数据更新3.5.1插入数据3.5.2修改数据3.5.3删除数据3.5.3删除数据l语句格式DELETEFROMWHERE；功能w删除指定表中满足WHERE子句条件的元组WHERE子句w指定要删除的元组w缺省表示要修改表中的所有元组删除数据(续)lDBMS在执行删除语句时会检查所删元组是否破坏表上已定义的完
85、整性规则参照完整性不允许删除级联删除删除数据（续）三种删除方式删除某一个元组的值删除多个元组的值带子查询的删除语句1.删除某一个元组的值例8删除学号为200215128的学生记录。DELETEFROMStudentWHERESno=200215128；2.删除多个元组的值例9删除2号课程的所有选课记录。DELETEFROMSC；WHERECno=2；例10删除所有的学生选课记录。DELETEFROMSC；3.带子查询的删除语句例11删除计算机科学系所有学生的选课记录。DELETEFROMSCWHERECS=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.S
86、no)；带子查询的删除语句（续）例11解法2DELETEFROMSCWHERESNOin(SELETESnoFROMStudentWHERESdept=CS)；第3章关系数据库标准语言SQL3.1SQL概述3.2学生课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.7小结3.6视图视图的特点虚表，是从一个或几个基本表（或视图）导出的表只存放视图的定义，不会出现数据冗余基表中的数据发生变化，从视图中查询出的数据也随之改变基于视图的操作定义视图(DDL)建立定义基于该视图的新视图删除查询(DML)受限更新(DML)3.6视图3.6.1定义视图3.6.2查询视图3.6.3更新视图3.
87、6.4视图的作用3.6视图3.6.1定义视图3.6.2查询视图3.6.3更新视图3.6.4视图的作用3.6.1定义视图1.建立视图2.删除视图1.建立视图语句格式CREATEVIEW(，)ASWITHCHECKOPTION；建立视图（续）组成视图的属性列名或全部省略或全部指定省略视图的各个属性列名，则隐含该视图由子查询中SELECT子句目标列中的诸字段组成。必须明确指定组成视图的所有列名的情形(1)某个目标列不是单纯的属性名，而是集函数或列表达式(2)目标列为*(3)多表连接时选出了几个同名列作为视图的字段(4)需要在视图中为某个列启用新的更合适的名字建立视图（续）子查询不含ORDERBY子句
88、和DISTINCT短语的SELECT语句WITHCHECKOPTION透过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）建立视图（续）DBMS执行CREATEVIEW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句。只是在对视图查询时，才按视图的定义从基本表中将数据查出。建立视图（续）常见的视图形式行列子集视图WITHCHECKOPTION的视图基于多个基表的视图基于视图的视图带表达式的视图分组视图建立视图（续）行列子集视图从单个基本表导出只是去掉了基本表的某些行和某些列，但保留了码建立视图（续）例1建立信息系学生的视图。CREATEVIEWIS
89、_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept=IS；行列子集视图视图IS_Student由Sno，Sname，Sage三列组成建立视图（续）WITHCHECKOPTION的视图例2建立信息系学生的视图，并要求透过该视图进行的更新操作只涉及信息系学生。CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept=ISWITHCHECKOPTION；建立视图（续）对IS_Student视图的更新操作修改操作：DBMS自动加上Sdept=IS的条件删除操作：DBMS自动加上Sd
90、ept=IS的条件插入操作：DBMS自动检查Sdept属性值是否为IS如果不是，则拒绝该插入操作如果没有提供Sdept属性值，则自动定义Sdept为IS建立视图（续）基于多个基表的视图例3建立信息系选修了1号课程的学生视图。CREATEVIEWIS_S1(Sno，Sname，Grade)ASSELECTStudent.Sno，Sname，GradeFROMStudent，SCWHERESdept=ISANDStudent.Sno=SC.SnoANDSC.Cno=1；由于视图IS_S1的属性列中包含了Student表与SC表的同名列Sno，所以必须在视图名后面明确说明视图的各个属性列名。建立视图
91、（续）基于视图的视图例4建立信息系选修了1号课程且成绩在90分以上的学生的视图。CREATEVIEWIS_S2ASSELECTSno，Sname，GradeFROMIS_S1WHEREGrade=90；视图IS_S2建立在视图IS_S1之上建立视图（续）带表达式的视图在设计数据库时，为了减少数据冗余，基本表中只存放基本数据，由基本数据经过各种计算派生出的数据一般是不存储的。视图中的数据并不实际存储，所以定义视图时可以根据应用的需要，设置一些派生属性列，以方便应用程序的编制。派生属性称为虚拟列。带虚拟列的视图称为带表达式的视图。带表达式的视图必须明确定义组成视图的各个属性列名建立视图（续）例5定
92、义一个反映学生出生年份的视图。CREATEVIEWBT_S(Sno，Sname，Sbirth)ASSELECTSno，Sname，2004-Sage FROMStudent；建立视图（续）分组视图用带聚集函数和GROUPBY子句的查询来定义的视图称为分组视图分组视图必须明确定义组成视图的各个属性列名建立视图（续）例6将学生的学号及他的平均成绩定义为一个视图。假设SC表中“成绩”列Grade为数字型CREATVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；建立视图（续）一类不易扩充的视图以SELECT*方式创建的视图可扩充性差，应尽
93、可能避免建立视图（续）例7将Student表中所有女生记录定义为一个视图CREATEVIEWF_Student1(stdnum， name， sex， age，dept)ASSELECT*FROMStudentWHERESsex=女；修改基表Student的结构后，Student表与F_Student1视图的映象关系被破坏，导致该视图不能正确工作。建立视图（续）CREATEVIEWF_Student2(stdnum，name，sex，age，dept)ASSELECTSno，Sname，Ssex，Sage，SdeptFROMStudentWHERESsex=女；为基表Student增加属性列不
94、会破坏Student表与F_Student2视图的映象关系。2.删除视图语句格式DROPVIEWCASCADE；该语句从数据字典中删除指定的视图定义若该视图上还导出了其他视图，则使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除删除基表时，由该基表导出的所有视图定义都必须显式删除删除视图(续）例8删除视图IS_S1DROPVIEWIS_S1；执行此语句时由于IS_S1视图上还导出了IS_S2视图，所以该语句被拒绝执行。若确定要删除，则使用级联删除语句：DROPVIEWIS_S1CASCADE;3.6视图3.6.1定义视图3.6.2查询视图3.6.3更新视图3.6.4视图的作用
95、3.6.2查询视图从用户角度而言，查询视图与查询基本表的方法相同DBMS实现视图查询的方法视图实体化法（ViewMaterialization）进行有效性检查，检查所查询的视图是否存在。如果存在，则从数据字典中取出视图的定义执行视图定义，将视图临时实体化，生成临时表将查询视图转换为查询临时表查询完毕删除被实体化的视图(临时表)查询视图（续）视图消解法（ViewResolution）进行有效性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询执行修正后的查询查询视图（续）例9在信息系学生的视图中找出年龄小
96、于20岁的学生。SELECTSno，SageFROMIS_StudentWHERESage20；IS_Student视图的定义(视图定义例1)：CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept=IS；查询视图（续）视图消解法转换后的查询语句为：SELECTSno，SageFROMStudentWHERESdept=ISANDSage=90；查询视图（续）S_G视图定义：CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；查询视图（续）转换后的查询
97、：SELECTSno，AVG(Grade)FROMSCWHEREAVG(Grade)=90GROUPBYSno；因为WHERE子句中是不能用聚集函数作为条件表达式的，因此执行此修正后的查询将会出现语法错误。查询视图（续）正确转换：SELECTSno，AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)=90；3.6视图3.6.1定义视图3.6.2查询视图3.6.3更新视图3.6.4视图的作用3.6.3更新视图从用户角度而言，更新视图与更新基本表的方法相同DBMS实现视图更新的方法视图实体化法（ViewMaterialization）视图消解法（ViewResol
98、ution）更新视图（续）定义视图时指定WITHCHECKOPTION子句后，DBMS在更新视图时会进行检查，防止用户通过视图对数据进行增加、删除、修改时，操作不属于视图范围内的基本表数据更新视图（续）例12将信息系学生视图IS_Student中学号为200215122的学生姓名改为“刘辰”。UPDATEIS_StudentSETSname=刘辰WHERESno=200215122；更新视图（续）视图实体化法视图消解法转换后的查询语句为：UPDATEStudentSETSname=刘辰WHERESno=95002ANDSdept=IS；更新视图（续）例13向信息系学生视图IS_Student中
99、插入一个新的学生记录，其中学号为200215129，姓名为赵新，年龄为20岁。INSERTINTOIS_StudentVALUES(200215129，赵新，20)；更新视图（续）转换为对基本表的更新：INSERTINTOStudent(Sno，Sname，Sage，Sdept)VALUES(200215129，赵新，20，IS)；更新视图（续）例14删除信息系学生视图IS_Student中学号为200215129的记录DELETEFROMIS_StudentWHERESno=200215129；更新视图（续）转换为对基本表的更新：DELETEFROMStudentWHERESno=20021
100、5129ANDSdept=IS；更新视图（续）DBMS对视图更新的限制一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新(对两类方法均如此)例：视图S_G为不可更新视图。CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；更新视图（续）S_G“平均成绩”Gavg属性列为导出列对于如下更新语句：UPDATES_GSETGavg=90WHERESno=200215121；无论实体化法还是消解法都无法将其转换成对基本表SC的更新的更新视图（续）视图的可更新性行列子集视图是可更新的。除行列子集视图
101、外，还有些视图理论上是可更新的，但它们的确切特征还是尚待研究的课题。还有些视图从理论上是不可更新的。更新视图（续）不可更新的视图与不允许更新的视图是两个不同的概念实际系统对视图更新的限制允许对行列子集视图进行更新对其他类型视图的更新不同系统有不同限制DB2对视图更新的限制：(1)若视图是由两个以上基本表导出的，则此视图不允许更新。更新视图（续）(2)若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。(3)若视图的字段来自聚集函数，则此视图不允许更新。(4)若视图定义中含有GROUPBY子句，则此视图不允许更新。(5)若视图定义中含
102、有DISTINCT短语，则此视图不允许更新。更新视图（续）(6)若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。例：视图GOOD_SC(修课成绩在平均成绩之上的元组)CREATEVIEWGOOD_SCASSELECTSno，Cno，GradeFROMSCWHEREGrade(SELECTAVG(Grade)FROMSC)；更新视图（续）(7)一个不允许更新的视图上定义的视图也不允许更新。3.6视图3.6.1定义视图3.6.2查询视图3.6.3更新视图3.6.4视图的作用3.6.4视图的作用视图最终是定义在基本表之上的，对视图的一切操作最终
103、也要转换为对基本表的操作。而且对于非行列子集视图进行查询或更新时还有可能出现问题。视图的作用（续）合理使用视图能够带来许多好处1.视图能够简化用户的操作2.视图使用户能以多种角度看待同一数据3.视图对重构数据库提供了一定程度的逻辑独立性4.视图能够对机密数据提供安全保护5.适当的利用视图可以更清晰的表达查询1.视图能够简化用户的操作当视图中数据不是直接来自基本表时，定义视图能够简化用户的操作基于多张表连接形成的视图基于复杂嵌套查询的视图含导出属性的视图2.视图使用户能以多种角度看待同一数据视图机制能使不同用户以不同方式看待同一数据，适应数据库共享的需要3.视图对重构数据库提供了一定程度的逻辑独
104、立性物理独立性与逻辑独立性的概念视图在一定程度上保证了数据的逻辑独立性视图对重构数据库提供了一定程度的逻辑独立性（续）例：数据库逻辑结构发生改变将学生关系Student(Sno，Sname，Ssex，Sage，Sdept)“垂直”地分成两个基本表：SX(Sno，Sname，Sage)SY(Sno，Ssex，Sdept)视图对重构数据库提供了一定程度的逻辑独立性（续）通过建立一个视图Student：CREATEVIEWStudent(Sno，Sname，Ssex，Sage，Sdept)ASSELECTSX.Sno，SX.Sname，SY.Ssex，SX.Sage，SY.SdeptFROMSX，S
105、YWHERESX.Sno=SY.Sno；使用户的外模式保持不变，从而对原Student表的查询程序不必修改视图对重构数据库提供了一定程度的逻辑独立性（续）视图只能在一定程度上提供数据的逻辑独立性由于对视图的更新是有条件的，因此应用程序中修改数据的语句可能仍会因基本表结构的改变而改变。4.视图能够对机密数据提供安全保护对不同用户定义不同视图，使每个用户只能看到他有权看到的数据通过WITHCHECKOPTION对关键数据定义操作时间限制5.适当的利用视图可以更清晰的表达查询若经常要执行这样的查询“对每个同学找出他获得最高成绩的课程号”，可以如下做：先定义一个视图：CREATEVIEWVMGRADEASSELECTSno,MAX(Grade)MgradeFROMSCGROUPBYSno;再用查询语句完成查询：SELECTSC.Sno,CnoFROMSC,VMGRADEWHERESC.Sno=VMGRADE.SnoANDSC.Grade=VMGRADE.Mgrade;第3章关系数据库标准语言SQL3.1SQL概述3.2学生课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.7小结3.7小结SQL的特点综合统一2.高度非过程化3.面向集合的操作方式4.同一种语法结构提供两种使用方式5.语言简捷，易学易用小结（续）交互式SQL数据定义查询数据更新数据控制小结（续）
