第3章 数据库语言及访问接口

1、3.1 SQL简介简介3.2 SQL的数据定义的数据定义3.3 SQL的数据查询的数据查询3.4 SQL的数据更新的数据更新3.5 SQL中的视图中的视图3.6 数据库访问技术数据库访问技术n SQLSQLStructured Query LanguageStructured Query Language（结构化查询语言），是通（结构化查询语言），是通用的，功能极强的关系用的，功能极强的关系DBDB语言。语言。1. SQL的主要标准的主要标准n SQL-86SQL-86。SQLSQL的第一个标准是的第一个标准是19861986年年1010月由美国国家标准化组织月由美国国家标准化组织(ANSI)

2、(ANSI)公布的。公布的。n SQL-89SQL-89。ANSIANSI以后通过对以后通过对SQL-86SQL-86的不断修改和完善，于的不断修改和完善，于19891989年年第二次公布了第二次公布了SQLSQL标准，即标准，即SQL-89SQL-89，该标准增强了完整性的语言特，该标准增强了完整性的语言特征。征。n SQL-92SQL-92(SQL2)(SQL2)。19921992年又公布了年又公布了SQL-92SQL-92标准，该标准增加了支标准，该标准增加了支持对远程数据库的访问，扩充了数据类型、操作类型、动态持对远程数据库的访问，扩充了数据类型、操作类型、动态SQLSQL等等许多新的

3、特征。许多新的特征。n SQL-99SQL-99(SQL3)(SQL3)。完成于。完成于19991999年的年的SQL-99SQL-99修订本具有更高级的特修订本具有更高级的特征。引入了支持对象征。引入了支持对象- -关系关系DBMSDBMS模型的模型的SQLSQL，扩展了对象、递归、，扩展了对象、递归、触发等许多新的特征，支持用户自定义函数、自定义数据类型。触发等许多新的特征，支持用户自定义函数、自定义数据类型。2. SQL2. SQL的功能、特点的功能、特点n 功能：功能：数据定义数据定义数据查询数据查询 数据操纵数据操纵数据控制数据控制 特点：特点： 综合统一综合统一 SQLSQL语言集

4、数据定义、操纵和控制功语言集数据定义、操纵和控制功能于一体，语言风格统一，可以独立能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，完成数据库生命周期中的全部活动，包括定义关系模式、录入数据以建立包括定义关系模式、录入数据以建立数据库、查询、更新、维护、数据库数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操重构、数据库安全性控制等一系列操作要求。作要求。 高度非过程化高度非过程化 非关系数据模型的数据操纵语言是面向过程的语言，用其完成某非关系数据模型的数据操纵语言是面向过程的语言，用其完成某项请求，必须指定存取路径。而用项请求，必须指定存取路径。而用SQLSQL语

5、言进行数据操作，用户只语言进行数据操作，用户只需提出需提出“做什么做什么”，而不必指明，而不必指明“怎么做怎么做”，因此用户无需了解存，因此用户无需了解存取路径，存取路径的选择以及取路径，存取路径的选择以及SQLSQL语句的操作过程由系统自动完成。语句的操作过程由系统自动完成。这不但大大减轻了用户负担，而且有利于提高数据独立性这不但大大减轻了用户负担，而且有利于提高数据独立性。 灵活的使用方式灵活的使用方式nSQLSQL语言既是自含式语言，又是嵌入式语言。语言既是自含式语言，又是嵌入式语言。 n作为自含式语言，它能够独立地用于联机交互的使用方式，作为自含式语言，它能够独立地用于联机交互的使用方

6、式，用户可以在终端键盘上直接键入用户可以在终端键盘上直接键入SQLSQL命令对数据库进行操作。命令对数据库进行操作。作为嵌入式语言，作为嵌入式语言，SQLSQL语句能够嵌入到高级语言（例如语句能够嵌入到高级语言（例如C C、COBOLCOBOL、FORTRANFORTRAN、PL/1PL/1）程序中，供程序员设计程序时使用。）程序中，供程序员设计程序时使用。而在两种不同的使用方式下，而在两种不同的使用方式下，SQLSQL语言的语法结构基本上是一语言的语法结构基本上是一致的。致的。 简洁、通用、功能强简洁、通用、功能强 SQLSQL语言功能极强，设计巧妙，语言简洁，完成数据定义、数语言功能极强，

7、设计巧妙，语言简洁，完成数据定义、数据操纵、数据控制的核心功能只用了据操纵、数据控制的核心功能只用了9 9个动词，如表个动词，如表3-13-1所示。而所示。而且且SQLSQL语言语法简单，接近英语口语，因此容易学习，容易使用。语言语法简单，接近英语口语，因此容易学习，容易使用。 3. SQL3. SQL的基本组成的基本组成 （1 1）SQLSQL数据库层次结构数据库层次结构 存储文件存储文件1存储文件存储文件2SQL视图视图1视图视图2基本表基本表1 基本表基本表2基本表基本表3术语对照：术语对照：一般关系模型一般关系模型 SQLn 外模式外模式-视图（视图（VIEWVIEW) )n 模式模式

8、-基本表基本表( (TABLETABLE) )n 内模式内模式-存储文件存储文件- -索引索引n 元组元组-行行( (ROWROW) )n 属性属性-列列( (COLUMNCOLUMN) )n基本表是本身独立存在的表，在基本表是本身独立存在的表，在SQLSQL中一个关系就对应一个表。中一个关系就对应一个表。一些基本表对应一个存储文件，一个表可以带若干索引，索引一些基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。也存放在存储文件中。 n存储文件的逻辑结构组成了关系数据库的内模式。存储文件的存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理文件结构是任意的。物理文件结

9、构是任意的。 n视图是从基本表或其他视图中导出的表，它本身不独立存储在视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。图是一个虚表。 用户可以用用户可以用SQLSQL语言对视图和基本表进行查询。语言对视图和基本表进行查询。在用户眼中，视图和基本表都是关系，而存储文件对用户是透在用户眼中，视图和基本表都是关系，而存储文件对用户是透明的。明的。 （2 2）SQLSQ

10、L语言的组成语言的组成 n数据定义语言（数据定义语言（DDLDDL）n数据操纵语言（数据操纵语言（DMLDML） n数据控制语言（数据控制语言（DCLDCL） n嵌入与会话规则嵌入与会话规则 （3 3）SQLSQL的语句类型的语句类型 nSQLSQL模式语句模式语句 nSQLSQL数据语句数据语句 nSQLSQL事务与控制语句事务与控制语句 nSQLSQL连接、会话及诊断语句连接、会话及诊断语句 4 4、SQLSQL的数据类型的数据类型 预定义数据类型预定义数据类型 int int、realreal、doubledouble、charchar、varcharvarchar、datedate、b

11、ooleanboolean等等 构造数据类型构造数据类型 array array、refref、rowrow等。等。 用户定义数据类型（用户定义数据类型（UDT，User Defined Type）5 5、SQLSQL环境环境（1 1）SQLSQL模式与目录模式与目录 SQL模式：模式：基本表、视图、角色等的集合。基本表、视图、角色等的集合。 好处：好处：允许在不同的允许在不同的SQLSQL模式中出现同名的基表名或模式中出现同名的基表名或 视图名。视图名。 目录：目录：SQLSQL环境中所有模式的集合。环境中所有模式的集合。 定位基表的方式：定位基表的方式： （2 2）SQLSQL环境环境 n

12、 设置默认的目录和模式设置默认的目录和模式 n 设置用户身份设置用户身份 1. SQLSQL模式的定义与撤销模式的定义与撤销（1 1）SQL模式的定义模式的定义 （P.68P.68） CREATE SCHEMA CREATE SCHEMA 模式名模式名 AUTHORIZATION AUTHORIZATION 用户名用户名 CREATE DOMAINCREATE DOMAIN子句子句| |CREATE TABLECREATE TABLE子句子句| |CREATE VIEW CREATE VIEW | | n其中：其中：表示其中的成分为任选项。：表示其中的成分为任选项。 ：表示其中的成分由用户具体

13、给定。：表示其中的成分由用户具体给定。 | |： 表示其中并列的成分只能择一。表示其中并列的成分只能择一。例：例：CREATE SCHEMA Teaching_db AUTHORIZATION HangCREATE SCHEMA Teaching_db AUTHORIZATION Hang；（2 2）数据库模式的删除）数据库模式的删除 DROP SCHEMA DROP SCHEMA 模式名模式名 CASCADE | RESTRICTCASCADE | RESTRICT CASCADECASCADE：级联式级联式 RESTRICTRESTRICT：约束式（受限式）约束式（受限式） 2. 2. 表

14、的建立和删除表的建立和删除 （1）表的建立表的建立 命令格式：命令格式： CREATE TABLE 模式名模式名. ( ，)；n例例：CREATE TABLE Student ( sno CHAR(5) NOT NULL UNIQUE， sname CHAR(8) NOT NULL ， sex CHAR(2)， age INT ， dept CHAR(20) )；主主键键n完整性约束条件涉及到该表的多个属性列，则完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可定义在列级，也必须定义在表级上，否则既可定义在列级，也可以定义在表级。可以定义在表级。注意：注意： 例：例：定义学生定

15、义学生_选课数据库中的三个表结构，并指定相应的数据完选课数据库中的三个表结构，并指定相应的数据完整性约束条件。整性约束条件。分析分析外外键键：（sno,cno） 主主键键：sno姓名：非空姓名：非空性别：男、女两值性别：男、女两值Student表：表：Course表：表：主主键键：cno课程名：非空课程名：非空外外键键：pcnoSC表：表：主主键键：(sno,cno)成绩：成绩：0100CREATE TABLE StudentCREATE TABLE Student ( ( sno CHAR (5),sno CHAR (5), sname CHAR (8) NOT NULL, sname CH

16、AR (8) NOT NULL, sex CHAR (2), sex CHAR (2), age SMALLINT, age SMALLINT, dept CHAR (20), dept CHAR (20), PRIMARY KEY(sno), PRIMARY KEY(sno), CHECK sex IN ( CHECK sex IN (男男,女女) ) ) ); ;列级完整性约束条件列级完整性约束条件实体完整性约束条件实体完整性约束条件用户自定义完整性约束条件用户自定义完整性约束条件CREATE TABLE CourseCREATE TABLE Course ( ( cno CHAR (4)

17、,cno CHAR (4), cname CHAR (10) NOT NULL, cname CHAR (10) NOT NULL, pcno CHAR (4), pcno CHAR (4), credit SMALLINT, credit SMALLINT, PRIMARY KEY (cno), PRIMARY KEY (cno), FOREIGN KEY (pcno) REFERENCES Course(cno) FOREIGN KEY (pcno) REFERENCES Course(cno) ) ); ;参照完整性约束条件参照完整性约束条件CREATE TABLE SCCREATE T

18、ABLE SC ( ( sno CHAR (5),sno CHAR (5), cno CHAR (4) , cno CHAR (4) , grade SMALLINT, grade SMALLINT, PRIMARY KEY (sno,cno), PRIMARY KEY (sno,cno), FOREIGN KEY (sno)REFERENCES Student(sno), FOREIGN KEY (sno)REFERENCES Student(sno), FOREIGN KEY (cno)REFERENCES Course(cno), FOREIGN KEY (cno)REFERENCES

19、Course(cno), CHECK CHECK ( (grade IS NULL) OR (grade IS NULL) OR (grade BETWEEN 0 AND 100) (grade BETWEEN 0 AND 100) ) ) ); ;（2 2）表的删除）表的删除 格式：格式：DROP TABLE DROP TABLE CASCADE | RESTRICT CASCADE | RESTRICTn基本表定义一旦删除，表中的数据、在此表上建立的视基本表定义一旦删除，表中的数据、在此表上建立的视图 、 索 引 、 触 发 器 、 断 言 都 将 自 动 被 删 除 掉 。图 、 索 引

20、 、 触 发 器 、 断 言 都 将 自 动 被 删 除 掉 。RESTRICT确保只有不具有相关对象的表才能被撤销。确保只有不具有相关对象的表才能被撤销。例：例：DROP TABLE St-quit CASCADEDROP TABLE St-quit CASCADE； 3.3.表的扩充和修改表的扩充和修改n一般格式为：一般格式为： ALTER TABLE ALTER TABLE ADD ADD 完整性约完整性约束束DROPDROP MODIFY MODIFY ;n其中其中 指定需要修改的基本表，指定需要修改的基本表，ADDADD子句用于增加新列和子句用于增加新列和新的完整性约束条件，新的完整

21、性约束条件，DROPDROP子句用于删除指定的完整性约束子句用于删除指定的完整性约束条件，条件，MODIFYMODIFY子句用于修改原有的列定义。子句用于修改原有的列定义。（1 1）在现存表中增加新列）在现存表中增加新列 格式：格式：ALTER TABLE ALTER TABLE ADD ( ADD ( ， ) ) 例：例：ALTER TABLE StudentALTER TABLE Student ADD (place CHAR(20) ADD (place CHAR(20)，addr CHAR(20)addr CHAR(20)； （2 2）删除已存在的某个列）删除已存在的某个列 格式：格式

22、：ALTER TABLE ALTER TABLE DROP DROP CASCADE | RESTRICT CASCADE | RESTRICT 例：例：ALTER TABLE StudentALTER TABLE Student DROP addr DROP addr；（3 3）修改原有列的类型）修改原有列的类型 格式：格式：ALTER TABLE ALTER TABLE MODIFY MODIFY ；例：例：ALTER TABLE StudentALTER TABLE Student MODIFY place CHAR(8) MODIFY place CHAR(8)； （4 4）补充定义主

23、）补充定义主键键 格式：格式：ALTER TABLE ALTER TABLE ADD PRIMARY KEY (ADD PRIMARY KEY ( ) )（5 5）删除主）删除主键键 格式：格式：ALTER TABLE ALTER TABLE DROP PRIMARY KEYDROP PRIMARY KEY4.4.域定义域定义 域定义用于建立用户自定义的数据类型。域定义用于建立用户自定义的数据类型。n命令格式：命令格式： CREATE DOMAIN CREATE DOMAIN AS AS DEFAULT DEFAULT 例例3.3.3 3 建立一个建筑公司的数据库模式，由建立一个建筑公司的数据

24、库模式，由3 3个表组成。个表组成。其中：其中： CRETE DOMAIN ITEM_ID NUMBERIC(4) DEFAULT 0CRETE DOMAIN ITEM_ID NUMBERIC(4) DEFAULT 0 CHECK (VALUE IS NOT NULL) CHECK (VALUE IS NOT NULL)5.5.索引的建立与删除索引的建立与删除 n 建立索引的目的：建立索引的目的：基本表上建立一个或多个索引，以提供多种存基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。取路径，加快查找速度。n 命令格式：命令格式： CREATE UNIQUE CLUSTER IND

25、EX ON ( ，)； 次序：次序： 升序（升序（ASC，缺省），缺省） 降序降序 （DESC） UNIQUE: 每一个索引值只对应惟一的数据记录。每一个索引值只对应惟一的数据记录。 CLUSTER: 建立聚簇索引，即索引项的顺序与表中记录的物理顺建立聚簇索引，即索引项的顺序与表中记录的物理顺序一致。序一致。 n注意：注意：在一个基本表上最多只能建立一个聚簇索引。在一个基本表上最多只能建立一个聚簇索引。n经常更新的列不宜建立聚簇索引。经常更新的列不宜建立聚簇索引。n所建索引放何处？所建索引放何处？n例例: 为为Student表按学号升序建惟一聚簇索引。表按学号升序建惟一聚簇索引。 为为SC表按

26、学号表按学号升序升序和课程号和课程号降序降序建惟一索引。建惟一索引。 CREATE UNIQUE CLUSTER INDEX Stno ON Student(Sno)； CREATE UNIQUE INDEX Scno ON SC(Sno ，Cno DESC)；n删除索引一般格式为：删除索引一般格式为： DROP INDEX；n例：例： DROP INDEX Stno ；n删除索引时，系统会同时从删除索引时，系统会同时从数据字典数据字典中删去有关该索引的描述。中删去有关该索引的描述。n基本形式：基本形式：SELECT FROM WHERE ;n查询语句块查询语句块含义含义: : 从表从表 (视

27、图视图) )R中找出满足条件中找出满足条件F的行，的行， 再从中选出目标属性再从中选出目标属性A的的值形成结果表。值形成结果表。查询目标查询目标:为为属性名表属性名表或或表达式表达式或或* *数据来源数据来源:表表或或视图视图选择行选择行( (元组元组) )的条件的条件关系代数关系代数: : A (F (R)n 例例:已知已知R(no,name,sum)求总分大于求总分大于600的学生号和姓名。的学生号和姓名。目标目标 A条件条件 F 来源来源 RSELECT no, nameFROM RWHERE sum600;n 解解: no，name(sum600sum600(R)n学生学生-课程数据库

28、课程数据库: Student(sno，sname，sex，age，dept) Course(cno，cname，credit ，pcno) SC(sno，cno，grade)3.3.1 单表查询单表查询 一、选择表中的列一、选择表中的列n例例: 查询所有学生的姓名、学号、所在院系。查询所有学生的姓名、学号、所在院系。 SELECT sname，sno，dept FROM Student WHERE .T. ;sname sno dept王萧虎王萧虎 200101 信息院信息院来源来源R条件条件F 目标目标An例例：查全体学生的姓名及其出生年份。：查全体学生的姓名及其出生年份。 SELECT s

29、name，2015-age FROM Student ；来源来源R条件条件F目标目标ASname 2015-age王萧虎王萧虎 1988AS Birth别名别名 Birth二、选择表中的行二、选择表中的行（1 1）比较比较 例例: :查考试成绩不及格的学号。查考试成绩不及格的学号。SELECT SnoFROM SCWHERE grade60 ；DISTINCT去掉重复元组去掉重复元组表达式表达式 (2)(2)确定范围确定范围 (BETWEEN AND ) 例例3.53.5 查询选查询选004号课程且成绩在号课程且成绩在85-95的学生号。的学生号。 SELECT sno FROM SC WHE

30、RE cno=004 AND grade BETWEEN 8 85 5 AND 95； 其他表示？其他表示？ (3)(3)字符匹配字符匹配-近似查询，模糊查询近似查询，模糊查询 格式格式1：NOT LIKE 含义：含义：查找指定的属性列值与查找指定的属性列值与相匹配的元组。相匹配的元组。n其中匹配串可含：其中匹配串可含： ：代表任意长度：代表任意长度(可为可为0)的字符串。的字符串。 _：代表任意单个字符。：代表任意单个字符。 例例 查所有姓刘或姓王的学生姓名、学号和性别。查所有姓刘或姓王的学生姓名、学号和性别。 SELECT sname，sno，sex FROM Student WHERE

31、sname LIKE 刘刘 OR sname LIKE 王王 ；n? 查询所有不姓刘或不姓王的学生姓名、学号和性别。查询所有不姓刘或不姓王的学生姓名、学号和性别。NOTNOT 例例 查姓查姓“欧阳欧阳”且全名为三个汉字的学生的姓名且全名为三个汉字的学生的姓名。 SELECT Sname SELECT Sname FROM Student FROM Student WHERE Sname LIKE WHERE Sname LIKE 欧阳欧阳_ _ _; ; n注意，由于一个汉字占两个字符的位置，所以匹配串欧阳后面需注意，由于一个汉字占两个字符的位置，所以匹配串欧阳后面需要跟个要跟个_。 例例 查

32、名字中第二字为查名字中第二字为“阳阳”字的学生的姓名和学号字的学生的姓名和学号。 SELECT Sname, Sno SELECT Sname, Sno FROM Student FROM Student WHERE Sname LIKE WHERE Sname LIKE _ _ _阳阳%; %; 格式格式2： LIKE ESCAPE n若要查的串本身含或若要查的串本身含或_ _ ，用，用ESCAPE ESCAPE 对通配符对通配符进行转义。进行转义。ESCAPE ESCAPE 短语表示短语表示 为换码字符，这样匹配串中为换码字符，这样匹配串中紧跟在紧跟在 后面的字符后面的字符”_”_”不再具

33、有通配符的含义，而是取其本身不再具有通配符的含义，而是取其本身含义，被转义为普通的含义，被转义为普通的”_”_”字符。字符。 例例 查以查以“数据数据_”_”开头，且倒数第开头，且倒数第2 2个汉字为个汉字为 ” ”原原”的课程情况。的课程情况。 SELECT SELECT * * FROM Course FROM Course WHERE cname LIKE WHERE cname LIKE 数据数据 _ _ 原原 _ _ _ ESCAPE ESCAPE ； 若要查以若要查以 “ “ 数据数据 _ _ ” ”开头的呢？开头的呢？ LIKE LIKE 数据数据 * * _ _ ESCAPE

34、ESCAPE * *； (4)(4)确定集合确定集合 (IN) 例：例：查询信息院、数学系和计算机学院学生的姓名和性别。查询信息院、数学系和计算机学院学生的姓名和性别。 SELECT sname，sex FROM Student WHERE dept IN (信息院信息院，数学系数学系，计算机计算机学院学院); ? 查询不是这三个系的学生的姓名和性别。查询不是这三个系的学生的姓名和性别。 NOT n一般形式：一般形式：SELECT FROM WHERE 查询目标查询目标: 为为属性名表属性名表或或表达式表达式或或* *数据来源数据来源: 表表或或视图视图选择行选择行( (元组元组) )的条件的

35、条件将选择结果按将选择结果按 的值进行分组的值进行分组选择满足条件的小组选择满足条件的小组按按 排序查询结果排序查询结果目标目标A来源来源R条件条件F分组分组 选组选组 排序排序GROUP BY HAVING ORDER BY ASC|DESC； 三、对查询结果排序三、对查询结果排序 例例3 3.5.5 查询全体男学生的学号、姓名，查询全体男学生的学号、姓名， 结果按所在的系升序排列，结果按所在的系升序排列， 同一系中的学生按年龄降序排列。同一系中的学生按年龄降序排列。 来源来源 R 条件条件 F目标目标 AStudent表排序二排序二排序一排序一 SELECT sno，sname FROM

36、Student WHERE sex=男男 ORDER BY dept，age DESC； 四、四、表达式与函数的使用表达式与函数的使用 1、使用集函数使用集函数 COUNT(COUNT(DISTINCT | ALLDISTINCT | ALL * * ) ) 统计元组个数统计元组个数 COUNT( COUNT(DISTINCT | ALLDISTINCT | ALL ) ) 统计一列中值的个数统计一列中值的个数 SUM( SUM(DISTINCT | ALLDISTINCT | ALL ) ) 计算一数值型列值的总和计算一数值型列值的总和 AVG( AVG(DISTINCT | ALLDIST

37、INCT | ALL ) ) 计算一数值型列值的平均值计算一数值型列值的平均值 MAX( MAX(DISTINCT | ALLDISTINCT | ALL ) ) 求一列值的最大值求一列值的最大值 MIN( MIN(DISTINCT | ALLDISTINCT | ALL ) ) 求一列值的最小值求一列值的最小值缺省值缺省值无重复值无重复值例例: 查询女学生的总人数和平均年龄。查询女学生的总人数和平均年龄。 SELECT COUNT(SELECT COUNT(* *) AS ) AS 女学生人数女学生人数,AVG(age) AS ,AVG(age) AS 平均年龄平均年龄 FROM Stude

38、nt FROM Student WHERE sex= WHERE sex=女女; 例例3.3.6 6 查询选修了课程的学生人数。查询选修了课程的学生人数。 SELECT COUNT(SELECT COUNT(DISTINCTDISTINCT sno) AS sno) AS 选课学生人数选课学生人数 FROM SC;FROM SC; 例例3.3.6 6 查询选修查询选修001001号课程并及格的学生的最高分数、最低分号课程并及格的学生的最高分数、最低分及总分及总分。 SELECT MAX(grade) AS SELECT MAX(grade) AS 最高分最高分,MIN(grade) AS ,M

39、IN(grade) AS 最低分最低分,SUM(grade) AS ,SUM(grade) AS 总分总分 FROM SC FROM SC WHERE cno=001 and grade=60; WHERE cno=001 and grade=60;2 2、使用表达式使用表达式 例例 查询各课程的学时数。查询各课程的学时数。 SELECT cname AS course_name,SELECT cname AS course_name,creditcredit* *1818 AS course_time AS course_time FROM course; FROM course; 3、对查

40、询结果分组对查询结果分组（GROUP BY GROUP BY 子句子句）n将查询结果表按某一将查询结果表按某一( (多多) )列值分组，值相等的为一组。列值分组，值相等的为一组。n目的：细化集函数的作用对象。目的：细化集函数的作用对象。如果未对查询结果分组，集函数如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。集函数将作用于每一个组，即每一组都有一个函数值。 例例3.3.6 6 求每个学生求每个学生所选课程所选课程的平均成绩。的平均成绩。 SELECT

41、 sno SELECT sno，AVG( grade) AS AVG( grade) AS 平均成绩平均成绩 FROM SC FROM SC GROUP BY sno GROUP BY sno HAVINGHAVING短语：在短语：在各组各组中选择满足条件的中选择满足条件的小组小组。 WHEREWHERE子句子句 ：在：在表表中选择满足条件的中选择满足条件的元组元组。WHERE ?并将其超过并将其超过8888分的输出。分的输出。HAVING AVG( grade) 88HAVING AVG( grade) 88；分组情况分组情况snosnocnocnogradegrade000101c1900

42、00101c285000101c380010101c185010101c275020101c380分组情况及查询结果示意图分组情况及查询结果示意图3 3组组1 1组组2 2组组 sno AVG(AVG(grade) 000101 85 010101 80 例例: :求每个学生的平均求每个学生的平均 成绩。成绩。 SELECT sno,AVG(grade)SELECT sno,AVG(grade) FROM SC FROM SC GROUP BY sno GROUP BY sno ；求每门课程的求每门课程的平均成绩？平均成绩？集函数在成组之前不计算，因此集函数在成组之前不计算，因此不能用于不能用

43、于WHERE子句子句, 一般将简一般将简单条件写入单条件写入WHERE。HAVING子句的条件运算数之一子句的条件运算数之一是一个集函数。是一个集函数。若若HAVING子句无前导子句无前导GROUPBY，选择清单中不能含有非集函数列。选择清单中不能含有非集函数列。【练习】【练习】查询至少选修查询至少选修4门课程的学生学号。门课程的学生学号。 SELECT sno FROM SC GROUP BY sno HAVING COUNT(*)=4 例例3.3.6 6 求学生关系中求学生关系中湖北籍男湖北籍男生的每一年龄组（生的每一年龄组（不不少于少于3 30 0人）人）共共有多少有多少人人，要求查询结

44、果按人数升序排列，要求查询结果按人数升序排列，人数相同时按年龄降序排列。人数相同时按年龄降序排列。 SELECT age,COUNT(sno) numberSELECT age,COUNT(sno) number FROM Student FROM Student WHERE sex= WHERE sex=男男 AND place= AND place=湖北湖北 GROUP BY age GROUP BY age HAVING number HAVING number3 30 0 ORDER BY number,age DESC; ORDER BY number,age DESC;3.3.2

45、多表查询多表查询 1. 子查询（嵌套查询）子查询（嵌套查询）n查询块：查询块：nSELECT nFROM nWHERE n子查询（嵌套查询）：子查询（嵌套查询）： 一个查询块嵌套在另一查询块中作为条件的查询。一个查询块嵌套在另一查询块中作为条件的查询。n上层的查询块又称为外层查询或父查询或主查询，下层上层的查询块又称为外层查询或父查询或主查询，下层查询块又称为内层查询或子查询。查询块又称为内层查询或子查询。 n子查询一般跟在子查询一般跟在ININ、SOMESOME（ANYANY）、）、ALLALL和和EXISTEXIST等谓词等谓词后面。后面。 nSQLSQL语言允许多层嵌套查询。语言允许多层

46、嵌套查询。n嵌套查询的求解方法是嵌套查询的求解方法是由里向外由里向外处理。即每个子查询在处理。即每个子查询在其上一级查询处理之前求解，子查询的结果用于建立其其上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。父查询的查找条件。 n嵌套查询使得可以用一系列简单查询构成复杂的查询，嵌套查询使得可以用一系列简单查询构成复杂的查询，从而明显地增强了从而明显地增强了SQLSQL的查询能力。以层层嵌套的方式的查询能力。以层层嵌套的方式来构造程序正是来构造程序正是 SQL(Structurred Query Language)SQL(Structurred Query Language)中中“

47、结构化结构化”的含义所在。的含义所在。(1) 带有带有IN谓词的子查询谓词的子查询 带有带有IN谓词的子查询是指父查询与子查询之间用谓词的子查询是指父查询与子查询之间用IN进行连接，进行连接，判断某个属性列值是否在子查询的结果中。判断某个属性列值是否在子查询的结果中。例：查询与例：查询与“刘晨刘晨”在同一个系学习的学生的学号、姓名、系在同一个系学习的学生的学号、姓名、系 确定确定“刘晨刘晨”所在系名所在系名 SELECT Sdept FROM StudentWHERE Sname=刘晨刘晨; 查找所有在查找所有在IS系学习的学生。系学习的学生。SELECT Sno, Sname, Sdept

48、FROM Student WHERE Sdept=IS; 子查询实现：将第一步查子查询实现：将第一步查询嵌入到第二步查询中，询嵌入到第二步查询中，用以构造第二步查询的条用以构造第二步查询的条件。件。 WHERE Sdept IN ( S E L E C T ) 例例 查询选修了数据库课程的学生号、查询选修了数据库课程的学生号、成绩。成绩。 SELECT sno， grade FROM SC WHERE cno IN (SELECT cno FROM Course WHERE cname=数据库数据库)； （2 2）带有比较运算符的子查询）带有比较运算符的子查询 例例3 3.7.7 找出年龄超过

49、平均找出年龄超过平均年龄的学生姓名。年龄的学生姓名。 SELECT sname FROM Student WHERE age (SELECT AVG(age) FROM Student)；(3)(3)带带SOME(ANY)SOME(ANY)、ALLALL谓词的子查询谓词的子查询nSOME 大于子查询结果中大于子查询结果中的某个值的某个值nALL 小于子查询结果中小于子查询结果中的所有值的所有值注意：注意：SOME、ALL必须与关系比较符必须与关系比较符同时使用。同时使用。n例：例：查询其他系比信息院某学生年查询其他系比信息院某学生年龄小的学生名、年龄。龄小的学生名、年龄。 SELECT sna

50、meSELECT sname，ageage FROM Student FROM Student WHERE age SOME WHERE age SOME ( SELECT age( SELECT age FROM Student FROM Student WHERE dept= WHERE dept=信息院信息院) ) AND deptAND dept信息信息院院 ；( SELECT MAX(age) = ALL ( SELECT AVG(grade) FROM SC GROUP BY sno)；姓名姓名 ？集合集合一个一个 例例 找出有一门选课成绩在找出有一门选课成绩在9090分以上的学生

51、姓名。分以上的学生姓名。 SELECT sname FROM Student WHERE sno IN ( SELECT DISTINCT sno FROM SC WHERE grade=90)；=SOME SELECT Student. sno，sname，sex，age， dept，cno，grade FROM Student，SC WHERE Student. sno=SC. sno ； 2. 条件连接查询条件连接查询 连接条件连接条件的一般格式为：的一般格式为： . . 当连接运算符为当连接运算符为=时，称为时，称为等值连接等值连接。 例例: 查询每个学生的情况及其选课成绩。查询每个学

52、生的情况及其选课成绩。 例例 找出籍贯为湖北或河北，选课成绩为找出籍贯为湖北或河北，选课成绩为9090分以上的学生分以上的学生的姓名、课号和成绩。的姓名、课号和成绩。 SELECT sname，cno，grade FROM Student，SC WHERE Student.sno=SC.sno AND place IN (湖北湖北，河北河北) AND grade90； 例例3.3.8 8 查询选修了数据库的学生号、成绩查询选修了数据库的学生号、成绩学分学分 ？SELECT sno， gradeFROM SC，CourseWHERE SC. cno = Course . cno AND cnam

53、e=DB ；, credit注意：注意：子查询不能用子查询不能用ORDER BYORDER BY子句子句为什么？为什么？不相关子查询不相关子查询 ( SELECT cno FROM Course WHERE cname=DB)； SELECT sno， grade FROM SC WHERE cno IN =方法方法1 1：方法方法2 2： 例例3.83.8 按平均成绩的降序给出所有课程都及格的学生按平均成绩的降序给出所有课程都及格的学生（号、名）及其平均成绩，其中成绩统计时不包括（号、名）及其平均成绩，其中成绩统计时不包括008008号考查课。号考查课。 SELECT Student.sno

54、 SELECT Student.sno，snamesname，AVG(grade) AS avg_gAVG(grade) AS avg_g FROM Student FROM Student，SCSC WHERE Student.sno=SC.sno AND cno008 WHERE Student.sno=SC.sno AND cno008 GROUP BY GROUP BY Student.Student.snosno,sname,sname HAVING MIN(grade)=60 HAVING MIN(grade)=60 ORDER BY avg_g DESC ORDER BY avg

55、_g DESC ； n自身连接查询自身连接查询 例例3.3.8 8 找出年龄比找出年龄比“王迎王迎”同学大的同学的姓名及年同学大的同学的姓名及年龄。龄。SELECT s1.sname，s1.ageFROM Student AS s1，Student AS s2WHERE s1.age s2.age AND s2.sname =王迎王迎； 其他方法？其他方法？ n练习练习：查每门课的查每门课的间接间接先修课先修课(即先修课的先修课即先修课的先修课)。d2d1003003004001 SELECT o, d2.pcno FROM Course AS d1, Course AS d2 WHERE d

56、1.pcno=o;3、 相关子查询相关子查询n当一个子查询的判断条件涉及到一个来自外部查询的当一个子查询的判断条件涉及到一个来自外部查询的列时，称为列时，称为相关子查询相关子查询。n带存在谓词的子查询：只产生逻辑值带存在谓词的子查询：只产生逻辑值n存在谓词存在谓词EXISTS作用作用: 若内层查询结果非空，则外若内层查询结果非空，则外层的层的WHERE子句返回真值，否则返回假值。子句返回真值，否则返回假值。n求解相关子查询不能象求解不相关子查询那样，一次求解相关子查询不能象求解不相关子查询那样，一次将子查询求解出来，然后求解父查询。相关子查询的将子查询求解出来，然后求解父查询。相关子查询的内层

57、查询由于与外层查询有关，因此必须反复求值。内层查询由于与外层查询有关，因此必须反复求值。 例例3.93.9 查询所有选修了查询所有选修了005005号号课程的学生姓名课程的学生姓名和学号和学号。 SELECT sname SELECT sname,sno,sno FROM Student FROM Student WHERE WHERE EXISTS EXISTS (SELECT (SELECT * * FROM SC FROM SC WHERE WHERE SC.sSC.sno=no=Student.Student.s snono AND cno= AND cno=005005)；n 分析：

58、分析：查询所有选修了查询所有选修了005005号课程的学生姓名涉及号课程的学生姓名涉及StudentStudent关系和关系和SCSC关系，在关系，在StudentStudent关系中依次取每个元关系中依次取每个元组的组的SnoSno值，用此值，用此Student.SnoStudent.Sno值去检查值去检查SCSC关系，若关系，若SCSC中存在中存在这样的元组：其这样的元组：其SC.SnoSC.Sno值等于值等于用来检查的用来检查的Student.SnoStudent.Sno值，值，并且其并且其SC.Cno=SC.Cno=005005，则取，则取此此Student.SnameStudent.

59、Sname送入结果关送入结果关系。系。n 相关子查询的一般处理过程：相关子查询的一般处理过程： 首先取外层查询中首先取外层查询中StudentStudent表的第一个元组，根据它与表的第一个元组，根据它与内层查询相关的属性值（即内层查询相关的属性值（即SnoSno值）处理内层查询，若值）处理内层查询，若WHEREWHERE子句返回值为真（即内层查询结果非空），则取此子句返回值为真（即内层查询结果非空），则取此元组放入结果表；然后再检查元组放入结果表；然后再检查StudentStudent表的下一个元组；表的下一个元组；重复这一过程，直至重复这一过程，直至StudentStudent表全部检查完

60、毕为止。表全部检查完毕为止。 例例3.93.9 查询查询没没选修选修001001号课程号课程的学生的学生学号和姓名学号和姓名。方法方法1 1： SELECT sn SELECT sno,snameo,sname FROM Student FROM Student WHERE WHERE NOT NOT EXISTSEXISTS (SELECT (SELECT * * FROM SC FROM SC WHERE WHERE SC.sSC.sno=no=Student.Student.s snono AND cno= AND cno=001001)；方法方法2 2： SELECT sn SELEC