关系数据库标准语言SQL1.ppt

L o g o 第三章第三章 关关系系数数据据库标库标准准语语言言SQLSQL 本章重点：本章重点： 1. 1. SQLSQL的特点和内涵及相关概念的特点和内涵及相关概念 2. 2. SQLSQL语言的构成语言的构成 3. 3. SQLSQL的应用的应用 数据数据库库课课件件 3.1 SQL3.1 SQL概述概述SQLSQL的发展的发展 (1). SQL在1974年由Boyce和Chamberlin提出； (2). 197579年，IBM在System R中实现了这种语言； (3). 1986年10月，ANSI的数据库委员会X3H2将SQL作为美国标准； (4). 1987年，ISO将SQL确立为国际标准； (5). 1989年，ANSI公布SQL-89标准； (6). 1992年，ANSI公布SQL-92标准； 目前，SQL已被所有关系型数据库产品所采用，关有 所发展，如SQL SERVER的T-SQL，ORACLE的 SQL*NET等，都在原来的SQL标准的基础上有了相当大 的变化。 数据数据库库课课件件 3.1 SQL3.1 SQL概述概述SQLSQL定义定义 结构化查询语言结构化查询语言SQLSQL（Structured Query Structured Query LanguageLanguage）是一种包含关系代数与逻辑运算的语）是一种包含关系代数与逻辑运算的语 言，其功能包括查询、操纵、定义和控制四个方面言，其功能包括查询、操纵、定义和控制四个方面 ，是一个通用的、功能极强的关系数据库语言。目，是一个通用的、功能极强的关系数据库语言。目 前已成为关系数据库的标准语言。前已成为关系数据库的标准语言。 SQLSQL包括：数据查询包括：数据查询(Data Query)(Data Query)、数据操纵、数据操纵 (Data Manipulation)(Data Manipulation)、数据定义、数据定义(Data Definition)(Data Definition) 和数据控制和数据控制(Data Control)(Data Control)，分别简称为，分别简称为DQLDQL、 DMLDML、DDLDDL、DCLDCL。 数据数据库库课课件件 3.1.1 SQL3.1.1 SQL的特点之一的特点之一 综合统一综合统一 SQL DDL ： DML: DCL: CREATE a Object (database or table) Update (Insert, Update or Delete) a Object Control a Object (users) DQL: Get (Select) a Data Set SQL语句 的结构是 什么？ 数据数据库库课课件件 3.1.1 SQL3.1.1 SQL的特点之二、三的特点之二、三 高度非过程化 面向集合的操作方式 我想在数 据库中 收到输入 的命令。 数据库操作 一个二维表 数据数据库库课课件件 3.1.1 SQL3.1.1 SQL的特点之四的特点之四 以同一种语法结构提供两种使用方式 SQLSQL语言既是自含式语言，又是嵌入式语言。语言既是自含式语言，又是嵌入式语言。 SQL语句 高级语言程序 数据库 数据数据库库课课件件 3.1.1 SQL3.1.1 SQL的特点之五的特点之五 言简洁，易学易用言简洁，易学易用 SQLSQL语言功能极强，但由于设计巧妙，语言十分简洁，语言功能极强，但由于设计巧妙，语言十分简洁， 完成数据定义、数据操纵、数据控制的核心功能只用了完成数据定义、数据操纵、数据控制的核心功能只用了 9 9个动词。如表下所示。而且个动词。如表下所示。而且SQLSQL语言语法简单，接近语言语法简单，接近 英语口语，因此容易学习，容易使用。英语口语，因此容易学习，容易使用。 数据查询Select 数据定义Create, Drop, Alter 数据操纵Insert, Update, Delete 数据控制Grant, Revoke 数据数据库库课课件件 3.1.2 SQL3.1.2 SQL语言的基本概念语言的基本概念 基本表是本身独立存在的表，在基本表是本身独立存在的表，在SQLSQL中一个关系就对应一中一个关系就对应一 个表。一些基本表对应一个存储文件，一个表可以带若干个表。一些基本表对应一个存储文件，一个表可以带若干 索引，索引也存放在存储文件中。索引，索引也存放在存储文件中。 存储文件的逻辑结构组成了关系数据库的内模式。存储文存储文件的逻辑结构组成了关系数据库的内模式。存储文 件的物理文件结构是任意的。件的物理文件结构是任意的。 视图是从基本表或其他视图中导出的表，它本身不独立存视图是从基本表或其他视图中导出的表，它本身不独立存 储在数据库中，也就是说数据库中只存放视图的定义而不储在数据库中，也就是说数据库中只存放视图的定义而不 存放视图对应的数据，这些数据仍存放在导出视图的基本存放视图对应的数据，这些数据仍存放在导出视图的基本 表中，因此视图是一个虚表。表中，因此视图是一个虚表。 用户可以用用户可以用SQLSQL语言对视语言对视 图和基本表进行查询。在用户眼中，视图和基本表都是关图和基本表进行查询。在用户眼中，视图和基本表都是关 系，而存储文件对用户是透明的。系，而存储文件对用户是透明的。 数据数据库库课课件件 SQLSQL对关系数据库模式的支持对关系数据库模式的支持 SQLSQL语言支持关系数据库三级模式结构。其中外模式对语言支持关系数据库三级模式结构。其中外模式对 应于视图（应于视图（ViewView）和部分基本表（）和部分基本表（Base TableBase Table），模式对），模式对 应于基本表，内模式对应于存储文件。应于基本表，内模式对应于存储文件。 基本表是本身独立存在的表，在基本表是本身独立存在的表，在SQLSQL中一个关系就对应一中一个关系就对应一 个表。一些基本表对应一个存储文件，一个表可以带若干个表。一些基本表对应一个存储文件，一个表可以带若干 索引，索引也存放在存储文件中。索引，索引也存放在存储文件中。 存储文件的逻辑结构组成了关系数据库的内模式。存储文存储文件的逻辑结构组成了关系数据库的内模式。存储文 件的物理文件结构是任意的。件的物理文件结构是任意的。 视图是从基本表或其他视图中导出的表，它本身不独立存视图是从基本表或其他视图中导出的表，它本身不独立存 储在数据库中，也就是说数据库中只存放视图的定义而不储在数据库中，也就是说数据库中只存放视图的定义而不 存放视图对应的数据，这些数据仍存放在导出视图的基本存放视图对应的数据，这些数据仍存放在导出视图的基本 表中，因此视图是一个虚表。表中，因此视图是一个虚表。 用户可以用用户可以用SQLSQL语言对视图语言对视图 和基本表进行查询。在用户眼中，视图和基本表都是关系和基本表进行查询。在用户眼中，视图和基本表都是关系 ，而存储文件对用户是透明的。，而存储文件对用户是透明的。 数据数据库库课课件件 SQLSQL对关系数据库模式的支持对关系数据库模式的支持 基本表B1 视图V1 基本表B3基本表B4 存储文件S1存储文件S2 基本表B2 视图V2 SQL 外层/ 外模式 概念层/ 概念模式 内层/ 存储模式 数据数据库库课课件件 注注 意意 本课程所讲授的本课程所讲授的SQLSQL语言遵循语言遵循SQL 89SQL 89标准。标准。 对于不同的数据系统，有不同的对于不同的数据系统，有不同的SQLSQL书写方式和功书写方式和功 能，在应用前，请阅读相应的能，在应用前，请阅读相应的SQLSQL手册。手册。 目前目前SQL SERVERSQL SERVER等著名数据库都有很强的等著名数据库都有很强的SQLSQL 语言扩充（语言扩充（T-SQLT-SQL）。）。 数据数据库库课课件件 关系数据库由模式、外模式和内模式组成，即关系 数据库的基本对象是表、视图和索引。因此，SQL 的数据定义功能包括 定义表定义表 定义视图定义视图 定义索引定义索引 3.2 3.2 数据定义数据定义 数据数据库库课课件件 3.2.1 3.2.1 定义、删除和修改基本表定义、删除和修改基本表 CREATE TABLE ( , , , ) 给出要创建的基本表的名称 ； 给出列名或字段名； 1 1 定义基本表定义基本表 数据数据库库课课件件 为列指定数据类为列指定数据类 型及其数据宽度型及其数据宽度 ； 关系数据库支持关系数据库支持 非常丰富的数据非常丰富的数据 类型，不同的数类型，不同的数 据库管理系统支据库管理系统支 持的数据类型基持的数据类型基 本是一样的，右本是一样的，右 表列出了常用的表列出了常用的 数据类型。数据类型。 数据类型数据类型 数据数据库库课课件件 用于定义列或字段一级的完整性约束，一般包括用于定义列或字段一级的完整性约束，一般包括 ： NOT NULLNOT NULL和和NULLNULL约束约束 PRIMARY KEYPRIMARY KEY约束约束 UNIQUEUNIQUE约束约束 FOREIGN KEYFOREIGN KEY约束约束 DEFAULTDEFAULT定义定义 CHECKCHECK约束约束 列级完整性约束列级完整性约束 数据数据库库课课件件 用于定义表一级的完整性约束，一般包括：用于定义表一级的完整性约束，一般包括： PRIMARY KEYPRIMARY KEY约束（复合属性构成约束（复合属性构成 的主码说明）的主码说明） FOREIGN KEYFOREIGN KEY约束（外码及参照关约束（外码及参照关 系说明）系说明） CHECKCHECK约束（同时涉及到多个属性约束（同时涉及到多个属性 的域完整性约束）的域完整性约束） 表级完整性约束表级完整性约束 数据数据库库课课件件 不是不是SQLSQL的标准选项，一般用于与物理的标准选项，一般用于与物理 存储有关的说明，不同的数据库管理系存储有关的说明，不同的数据库管理系 统定义的方式肯定不同，另外该项参数统定义的方式肯定不同，另外该项参数 一般也不是必需的。一般也不是必需的。 其他参数其他参数 数据数据库库课课件件 例题例题 例例1 1 建立一个建立一个“ “学生学生” ”表表StudentStudent，它由学号，它由学号SnoSno、 姓名姓名SnameSname、性别、性别SsexSsex、年龄、年龄SageSage、所在系、所在系SdeptSdept 五个属性组成。其中学号不能为空，值是唯一的五个属性组成。其中学号不能为空，值是唯一的 ，并且姓名取值也唯一。，并且姓名取值也唯一。 CREATE TABLE Student CREATE TABLE Student ( ( Sno CHAR(5) NOT NULL UNIQUE Sno CHAR(5) NOT NULL UNIQUE， Sname CHAR(20) UNIQUESname CHAR(20) UNIQUE， Ssex CHAR(1) Ssex CHAR(1) ， Sage INTSage INT， Sdept CHAR(15) Sdept CHAR(15) ) )； 数据数据库库课课件件 2 2 修改基本表修改基本表 2 2 修改基本表修改基本表 说明：其中说明：其中 指定需要修改的基本表，指定需要修改的基本表，ADDADD子句子句 用于增加新列和新的完整性约束条件，用于增加新列和新的完整性约束条件，DROPDROP子句用于子句用于 删除指定的完整性约束条件，删除指定的完整性约束条件，MODIFYMODIFY子句用于修改原子句用于修改原 有的列定义。有的列定义。 ALTER TABLE ADD | | DROP | | MODIFY ON ON (, ) )； 用用 指定要建索引的基本表名字指定要建索引的基本表名字 索引可以建立在该表的一索引可以建立在该表的一列列或多列上，各列名之或多列上，各列名之 间用逗号分隔间用逗号分隔 用用 指定索引值的排列次序，升序：指定索引值的排列次序，升序：ASCASC， 降序：降序：DESCDESC。缺省值：。缺省值：ASCASC UNIQUEUNIQUE表明此索引的每一个索引值只对应唯一表明此索引的每一个索引值只对应唯一 的数据记录的数据记录 CLUSTERCLUSTER表示要建立的索引是聚簇索引表示要建立的索引是聚簇索引 数据数据库库课课件件 例题例题 例例5 5 为学生为学生- -课程数据库中的课程数据库中的StudentStudent，CourseCourse，SCSC三个表建三个表建 立索引。其中立索引。其中StudentStudent表按学号升序建唯一索引，表按学号升序建唯一索引，CourseCourse表按表按 课程号升序建唯一索引，课程号升序建唯一索引，SCSC表按学号升序和课程号降序建唯表按学号升序和课程号降序建唯 一索引。一索引。 CREATE UNIQUE INDEX Stusno ON Student(Sno)CREATE UNIQUE INDEX Stusno ON Student(Sno)； CREATE UNIQUE INDEX Coucno ON Course(Cno)CREATE UNIQUE INDEX Coucno ON Course(Cno)； CREATE UNIQUE INDEX SCno ON SC(Sno ASCCREATE UNIQUE INDEX SCno ON SC(Sno ASC，Cno DESC)Cno DESC)； 数据数据库库课课件件 建立索引建立索引 （续）（续） 唯一值索引唯一值索引 对于已含重复值的属性列不能建对于已含重复值的属性列不能建UNIQUEUNIQUE索索 引引 对某个列建立对某个列建立UNIQUEUNIQUE索引后，插入新记录索引后，插入新记录 时时DBMSDBMS会自动检查新记录在该列上是否取了会自动检查新记录在该列上是否取了 重复值。这相当于增加了一个重复值。这相当于增加了一个UNIQUEUNIQUE约束约束 数据数据库库课课件件 建立索引建立索引 （续）（续） 聚簇索引聚簇索引 建立聚簇索引后，基表中数据也需要按指定的聚建立聚簇索引后，基表中数据也需要按指定的聚 簇属性值的升序或降序存放。也即聚簇索引的索簇属性值的升序或降序存放。也即聚簇索引的索 引项顺序与表中记录的物理顺序一致引项顺序与表中记录的物理顺序一致 例例66： CREATE CLUSTER INDEX Stusname ONCREATE CLUSTER INDEX Stusname ON Student(Sname) Student(Sname)； 在在StudentStudent表的表的SnameSname列上建立一个聚簇索引，而且列上建立一个聚簇索引，而且 StudentStudent表中的记录将按照表中的记录将按照SnameSname值的升序存放值的升序存放 数据数据库库课课件件 建立索引建立索引 （续）（续） 在一个基本表上最多只能建立一个聚簇索引在一个基本表上最多只能建立一个聚簇索引 聚簇索引的用途：对于某些类型的查询，可聚簇索引的用途：对于某些类型的查询，可 以提高查询效率以提高查询效率 聚簇索引的适用范围聚簇索引的适用范围 很少对基表进行增删操作很少对基表进行增删操作 很少对其中的变长列进行修改操作很少对其中的变长列进行修改操作 数据数据库库课课件件 二、删除索引二、删除索引 DROP INDEX ； 删除索引时，系统会从数据字典中删去有关删除索引时，系统会从数据字典中删去有关 该索引的描述。该索引的描述。 例例7 7 删除删除StudentStudent表的表的StusnameStusname索引。索引。 DROP INDEX StusnameDROP INDEX Stusname； 数据数据库库课课件件 3.3 3.3 数据操纵数据操纵 SQLSQL的数据操纵功能包括：的数据操纵功能包括： 查询：查询：SELECTSELECT 更新更新( (插入插入) )：INSERTINSERT 更新更新( (删除删除) )：DELETEDELETE 更新更新( (修改修改) )：UPDATEUPDATE 数据数据库库课课件件 3.3.1 SQL3.3.1 SQL查询语句查询语句 语句格式语句格式 SELECT ALL|DISTINCT *|, FROM , , WHERE GROUP BY , HAVING ORDER BY ASC|DESC , ASC|DESC COMPUTE COMPUTE 数据数据库库课课件件 示例数据库示例数据库 学生学生- -课程数据库课程数据库 学生表：学生表：Student(SnoStudent(Sno，SnameSname，SsexSsex，SageSage，Sdept)Sdept) 课程表：课程表：Course(CnoCourse(Cno，CnameCname，CpnoCpno，Ccredit)Ccredit) 学生选课表：学生选课表：SC(SnoSC(Sno，CnoCno，Grade) Grade) 数据数据库库课课件件 例例8 8 建立建立“ “学生学生” ”表表StudentStudent，学号是主码，姓名取值唯一。，学号是主码，姓名取值唯一。 CREATE TABLE Student CREATE TABLE Student ( Sno CHAR(9) ( Sno CHAR(9) PRIMARY KEYPRIMARY KEY， /* /* 列级完整性约束条件列级完整性约束条件* */ / Sname CHAR(20) Sname CHAR(20) UNIQUEUNIQUE， /* Sname/* Sname取唯一值取唯一值* */ / Ssex CHAR(2) Ssex CHAR(2)， Sage SMALLINTSage SMALLINT， Sdept CHAR(20) )Sdept CHAR(20) )； 主码 数据数据库库课课件件 例例9 9 建立一个建立一个“课程课程”表表CourseCourse CREATE TABLE Course CREATE TABLE Course ( Cno CHAR(4) PRIMARY KEY ( Cno CHAR(4) PRIMARY KEY， Cname CHAR(40)Cname CHAR(40)， Cpno CHAR(4) Cpno CHAR(4) ， Ccredit SMALLINTCcredit SMALLINT， FOREIGN KEY (Cpno) REFERENCES Course(Cno) FOREIGN KEY (Cpno) REFERENCES Course(Cno) ); ); 先修课 Cpno是外码 被参照表是Course 被参照列是Cno 数据数据库库课课件件 例例10 10 建立一个建立一个“学生选课学生选课”表表SCSC CREATE TABLE SCCREATE TABLE SC (Sno CHAR(9)(Sno CHAR(9)， Cno CHAR(4)Cno CHAR(4)， Grade SMALLINTGrade SMALLINT， PRIMARY KEY (SnoPRIMARY KEY (Sno，Cno)Cno)， /* /* 主码由两个属性构成，必须作为表级完整性进行定义主码由两个属性构成，必须作为表级完整性进行定义* */ / FOREIGN KEY (Sno) REFERENCES Student(Sno)FOREIGN KEY (Sno) REFERENCES Student(Sno)， /* /* 表级完整性约束条件，表级完整性约束条件，SnoSno是外码，被参照表是是外码，被参照表是Student */Student */ FOREIGN KEY (Cno) REFERENCES Course(Cno)FOREIGN KEY (Cno) REFERENCES Course(Cno) /* /* 表级完整性约束条件，表级完整性约束条件， CnoCno是外码，被参照表是是外码，被参照表是Course*/Course*/ ); ); 数据数据库库课课件件 单表查询单表查询 查询仅涉及一个表，是一种最简单的查询操作查询仅涉及一个表，是一种最简单的查询操作 一、选择表中的若干列一、选择表中的若干列 二、选择表中的若干元组二、选择表中的若干元组 三、对查询结果排序三、对查询结果排序 四、使用集函数四、使用集函数 五、对查询结果分组五、对查询结果分组 数据数据库库课课件件 例例1111从职工关系中检索所有工资值从职工关系中检索所有工资值 SELECT SELECT 工资工资 FROM FROM 职工职工 结果是： 1220 1210 1250 1230 1250 SELECT DISTINCT工资 FROM 职工 结果是： 1220 1210 1250 1230 数据数据库库课课件件 例例12 12 使用列使用列别名别名改变查询结果的列标题改变查询结果的列标题 SELECT Sname SELECT Sname NAMENAME，Year of Birth: Year of Birth: BIRTH BIRTH， 2008-Sage 2008-Sage BIRTHDAYBIRTHDAY，ISLOWER(Sdept) ISLOWER(Sdept) DEPARTMENTDEPARTMENT FROM StudentFROM Student； 输出结果：输出结果： NAME BIRTH BIRTHDAY DEPARTMENTNAME BIRTH BIRTHDAY DEPARTMENT - - - - - - - - 李勇李勇 Year of Birth: 1976 csYear of Birth: 1976 cs 刘晨刘晨 Year of Birth: 1977 isYear of Birth: 1977 is 王名王名 Year of Birth: 1978 maYear of Birth: 1978 ma 张立张立 Year of Birth: 1977 isYear of Birth: 1977 is 数据数据库库课课件件 二、选择表中的若干元组二、选择表中的若干元组 消除取值重复的行消除取值重复的行 查询满足条件的元组查询满足条件的元组 数据数据库库课课件件 1. 1. 消除取值重复的行消除取值重复的行 在在SELECTSELECT子句中使用子句中使用DISTINCTDISTINCT短语短语 假设假设SCSC表中有下列数据表中有下列数据 Sno Cno GradeSno Cno Grade - - - - - - 95001 1 92 95001 1 92 95001 2 85 95001 2 85 95001 3 88 95001 3 88 95002 2 90 95002 2 90 95002 3 80 95002 3 80 数据数据库库课课件件 ALL ALL 与与 DISTINCT DISTINCT 例例13 13 查询选修了课程的学生学号。查询选修了课程的学生学号。 (1) SELECT Sno(1) SELECT Sno FROM SC; FROM SC; 或或( (默认默认 ALL)ALL) SELECT ALL Sno SELECT ALL Sno FROM SC; FROM SC; 结果：结果： Sno Sno - - 95001 95001 95001 95001 95001 95001 95002 95002 95002 95002 数据数据库库课课件件 例题（续）例题（续） (2) SELECT DISTINCT Sno(2) SELECT DISTINCT Sno FROM SC; FROM SC; 结果：结果： Sno Sno - - 95001 95001 95002 95002 数据数据库库课课件件 2.2.查询满足条件的元组查询满足条件的元组 WHERE子句常用的查询条件 数据数据库库课课件件 (1) (1) 比较大小比较大小 在在WHEREWHERE子句的子句的 中使用比较运算符中使用比较运算符 = =， ，=，!，!= 20WHERE NOT Sage = 20； 数据数据库库课课件件 (2) (2) 确定范围确定范围 使用谓词使用谓词 BETWEEN BETWEEN AND AND NOT BETWEEN NOT BETWEEN AND AND 例例15 15 查询年龄在查询年龄在20232023岁（包括岁（包括2020岁和岁和2323岁）之间的岁）之间的 学生的姓名、系别和年龄。学生的姓名、系别和年龄。 SELECT SnameSELECT Sname，SdeptSdept，SageSage FROM StudentFROM Student WHERE Sage BETWEEN 20 AND 23WHERE Sage BETWEEN 20 AND 23； 数据数据库库课课件件 例题（续）例题（续） 例例16 16 查询年龄不在查询年龄不在20232023岁之间的学生姓名、系别和年龄。岁之间的学生姓名、系别和年龄。 SELECT SnameSELECT Sname，SdeptSdept，SageSage FROM StudentFROM Student WHERE Sage NOT BETWEEN 20 AND 23WHERE Sage NOT BETWEEN 20 AND 23； 数据数据库库课课件件 (3) (3) 确定集合确定集合 使用谓词使用谓词 IN , NOT IN ：用逗号分隔的一组取值：用逗号分隔的一组取值 例例1717查询信息系（查询信息系（ISIS）、数学系（）、数学系（MAMA）和计）和计 算机科学系（算机科学系（CSCS）学生的姓名和性别。）学生的姓名和性别。 SELECT SnameSELECT Sname，SsexSsex FROM StudentFROM Student WHERE Sdept IN ( ISWHERE Sdept IN ( IS，MAMA，CS );CS ); 数据数据库库课课件件 (3) (3) 确定集合确定集合 例例1818查询既不是信息系、数学系，也不是计算查询既不是信息系、数学系，也不是计算 机科学系的学生的姓名和性别。机科学系的学生的姓名和性别。 SELECT SnameSELECT Sname，SsexSsex FROM StudentFROM Student WHERE Sdept NOT IN ( IS WHERE Sdept NOT IN ( IS，MAMA，CS );CS ); 数据数据库库课课件件 (4) (4) 字符串匹配字符串匹配 NOT LIKE ESCAPE ：指定匹配模板：指定匹配模板 匹配模板：固定字符串或含通配符的字符串匹配模板：固定字符串或含通配符的字符串 当匹配模板为固定字符串时，当匹配模板为固定字符串时， 可以用可以用 = = 运算符取代运算符取代 LIKE LIKE 谓词谓词 用用 != != 或或 运算符取代运算符取代 NOT LIKE NOT LIKE 谓词谓词 数据数据库库课课件件 通配符通配符 w w % (% (百分号百分号) ) 代表任意长度（长度可以为代表任意长度（长度可以为0 0）的字符串）的字符串 例例1919：a%ba%b表示以表示以a a开头，以开头，以b b结尾的任意长度的字符串。如结尾的任意长度的字符串。如 acbacb，addgbaddgb，ab ab 等都满足该匹配串等都满足该匹配串 _ (_ (下横线下横线) ) 代表任意单个字符代表任意单个字符 例例2020：a_ba_b表示以表示以a a开头，以开头，以b b结尾的长度为结尾的长度为3 3的任意字符串。的任意字符串。 如如acbacb，afbafb等都满足该匹配串等都满足该匹配串 数据数据库库课课件件 ESCAPE ESCAPE 短语：短语： 当用户要查询的字符串本身就含有当用户要查询的字符串本身就含有 % % 或或 _ _ 时，要使用时，要使用ESCAPE 短语对通短语对通 配符进行转义。配符进行转义。 数据数据库库课课件件 例题例题 1) 1) 匹配模板为固定字符串匹配模板为固定字符串 例例2121 查询学号为查询学号为9500195001的学生的详细情况。的学生的详细情况。 SELECT * SELECT * FROM Student FROM Student WHERE Sno LIKE 95001 WHERE Sno LIKE 95001； 等价于：等价于： SELECT * SELECT * FROM Student FROM Student WHERE Sno = 95001 WHERE Sno = 95001； 数据数据库库课课件件 例题（续）例题（续） 2) 2) 匹配模板为含通配符的字符串匹配模板为含通配符的字符串 例例22 22 查询所有姓刘学生的姓名、学号和性别。查询所有姓刘学生的姓名、学号和性别。 SELECT SnameSELECT Sname，SnoSno，SsexSsex FROM Student FROM Student WHERE Sname LIKE WHERE Sname LIKE 刘刘%； 数据数据库库课课件件 例题（续）例题（续） 匹配模板为含通配符的字符串（续）匹配模板为含通配符的字符串（续） 例例23 23 查询姓查询姓“ “欧阳欧阳“ “且全名为三个汉字的学生的姓名。且全名为三个汉字的学生的姓名。 SELECT SnameSELECT Sname FROM Student FROM Student WHERE Sname LIKE WHERE Sname LIKE 欧阳欧阳_； 数据数据库库课课件件 例题（续）例题（续） 匹配模板为含通配符的字符串（续）匹配模板为含通配符的字符串（续） 例例24 24 查询名字中第查询名字中第2 2个字为个字为“ “阳阳“ “字的学生的姓名和学号。字的学生的姓名和学号。 SELECT SnameSELECT Sname，SnoSno FROM Student FROM Student WHERE Sname LIKE _ WHERE Sname LIKE _阳阳%； 数据数据库库课课件件 例题（续）例题（续） 匹配模板为含通配符的字符串（续）匹配模板为含通配符的字符串（续） 例例25 25 查询所有不姓刘的学生姓名。查询所有不姓刘的学生姓名。 SELECT SnameSELECT Sname，SnoSno，SsexSsex FROM Student FROM Student WHERE Sname NOT LIKE WHERE Sname NOT LIKE 刘刘%； 数据数据库库课课件件 例题（续）例题（续） 3) 3) 使用换码字符将通配符转义为普通字符使用换码字符将通配符转义为普通字符 例例26 26 查询查询DB_DesignDB_Design课程的课程号和学分。课程的课程号和学分。 SELECT CnoSELECT Cno，CcreditCcredit FROM Course FROM Course WHERE Cname LIKE DB WHERE Cname LIKE DB _Design _Design ESCAPE ESCAPE 数据数据库库课课件件 例题（续）例题（续） 使用换码字符将通配符转义为普通字符使用换码字符将通配符转义为普通字符( (续续) ) 例例27 27 查询以查询以“DB_”“DB_”开头，且倒数第开头，且倒数第3 3个字符为个字符为 i i的课程的课程 的详细情况。的详细情况。 SELECT *SELECT * FROM Course FROM Course WHERE Cname LIKE DB WHERE Cname LIKE DB _%i_ _ _%i_ _ ESCAPE ESCAPE ； 数据数据库库课课件件 (5) (5) 涉及空值的查询涉及空值的查询 使用谓词使用谓词 IS NULL IS NULL 或或 IS NOT NULLIS NOT NULL “IS NULL” “IS NULL” 不能用不能用 “ “= NULL” = NULL” 代替代替 例例28 28 某些学生选修课程后没有参加考试，所以有选某些学生选修课程后没有参加考试，所以有选 课记录，但没有考试成绩。查询缺少成绩的学生的课记录，但没有考试成绩。查询缺少成绩的学生的 学号和相应的课程号。学号和相应的课程号。 SELECT SnoSELECT Sno，CnoCno FROM SC FROM SC WHERE Grade IS NULL WHERE Grade IS NULL； 数据数据库库课课件件 例题例题( (续续) ) 例例29 29 查所有有成绩的学生学号和课程号查所有有成绩的学生学号和课程号 。 SELECT SnoSELECT Sno，CnoCno FROM SC FROM SC WHERE Grade IS NOT NULL WHERE Grade IS NOT NULL； 数据数据库库课课件件 (6) (6) 多重条件查询多重条件查询 用逻辑运算符用逻辑运算符ANDAND和和 OROR来联结多个查询条件来联结多个查询条件 ANDAND的优先级高于的优先级高于OROR 可用括号改变优先级可用括号改变优先级 可用来实现多种其他谓词可用来实现多种其他谓词 NOT INNOT IN NOT BETWEEN AND NOT BETWEEN AND 数据数据库库课课件件 例题例题 例例30 30 查询计算机系年龄在查询计算机系年龄在2020岁以下的学生姓名。岁以下的学生姓名。 SELECT SnameSELECT Sname FROM Student FROM Student WHERE Sdept= CS AND Sage=20 AND Sage=20 AND Sage ） 计算总和计算总和 SUMSUM（DISTINCT|DISTINCT|ALLALL ） 计算平均值计算平均值 AVGAVG（DISTINCT|DISTINCT|ALLALL ） 数据数据库库课课件件 使用集函数（续）使用集函数（续） 求最大值求最大值 MAXMAX（DISTINCT|DISTINCT|ALLALL ） 求最小值求最小值 MINMIN（DISTINCT|DISTINCT|ALLALL ） DISTINCTDISTINCT短语：在计算时要取消指定列短语：在计算时要取消指定列 中的重复值中的重复值 ALLALL短语：不取消重复值短语：不取消重复值 ALLALL为缺省值为缺省值 数据数据库库课课件件 使用集函数使用集函数 （续）（续） 例例35 35 查询学生总人数。查询学生总人数。 SELECT COUNT(*)SELECT COUNT(*) FROM Student FROM Student； 例例36 36 查询选修了课程的学生人数。查询选修了课程的学生人数。 SELECT COUNT(DISTINCT Sno)SELECT COUNT(DISTINCT Sno) FROM SC FROM SC； 注：用注：用DISTINCTDISTINCT以避免重复计算学生人数以避免重复计算学生人数 数据数据库库课课件件 使用集函数使用集函数 （续）（续） 例例37 37 计算计算1 1号课程的学生平均成绩。号课程的学生平均成绩。 SELECT AVG(Grade)SELECT AVG(Grade) FROM SC FROM SC WHERE Cno= 1 WHERE Cno= 1 ； 例例38 38 查询选修查询选修1 1号课程的学生最高分数。号课程的学生最高分数。 SELECT MAX(Grade)SELECT MAX(Grade) FROM SC FROM SC WHER Cno= 1 WHER Cno= 1 ； 数据数据库库课课件件 五、对查询结果分组五、对查询结果分组 使用使用GROUP BYGROUP BY子句分组子句分组 细化集函数的作用对象细化集函数的作用对象 未对查询结果分组，集函数将作用于整个查询未对查询结果分组，集函数将作用于整个查询 结果结果 对查询结果分组后，集函数将分别作用于每个对查询结果分组后，集函数将分别作用于每个 组组 数据数据库库课课件件 使用使用GROUP BYGROUP BY子句分组子句分组 例例39 39 求各个课程号及相应的选课人数。求各个课程号及相应的选课人数。 SELECT CnoSELECT Cno，COUNT(Sno)COUNT(Sno) FROM SC FROM SC GROUP BY Cno GROUP BY Cno； 结果结果 Cno COUNT(Sno)Cno COUNT(Sno) 1 221 22 2 342 34 3 443 44 4 334 33 5 485 48 数据数据库库课课件件 对查询结果分组对查询结果分组 （续）（续） GROUP BYGROUP BY子句的作用对象是查询的中间结果表子句的作用对象是查询的中间结果表 分组方法：按指定的一列或多列值分组，值相等的分组方法：按指定的一列或多列值分组，值相等的 为一组为一组 使用使用GROUP GROUP BYBY子句后，子句后，SELECTSELECT子句的列名列子句的列名列 表中只能出现分组属性和集函数表中只能出现分组属性和集函数 数据数据库库课课件件 使用使用HAVINGHAVING短语筛选最终输出结果短语筛选最终输出结果 例例40 40 查询选修了查询选修了3 3门以上课程的学生学号。门以上课程的学生学号。 SELECT SnoSELECT Sno FROM SC FROM SC GROUP BY Sno GROUP BY Sno HAVING COUNT(*) 3 HAVING COUNT(*) 3； 数据数据库库课课件件 例题例题 例例41 41 查询有查询有3 3门以上课程是门以上课程是9090分以上的分以上的 学生的学号及（学生的学号及（9090分以上的）课程数分以上的）课程数 SELECT Sno, COUNT(*)SELECT Sno, COUNT(*) FROM SC FROM SC WHERE Grade=90 WHERE Grade=90 GROUP BY Sno GROUP BY Sno HAVING COUNT(*)=3; HAVING COUNT(*)=3; 数据数