第三章关系数据库标准语言SQL课件.ppt

1、数据定义、查询、数据更新数据定义、查询、数据更新视图视图数据控制数据控制嵌入式嵌入式SQL3.1 SQL 概述概述3.1.1 SQL 的特点的特点 SQL是一个介于关系代数与关系演算之间的是一个介于关系代数与关系演算之间的结构化查询语言，其功能不只是查询。结构化查询语言，其功能不只是查询。SQL是一是一个通用的、功能极强的个通用的、功能极强的关系关系数据库查询语言。数据库查询语言。1.一体化特点；一体化特点；2.高度非过程化；高度非过程化；3.3.面向集合的操作方式面向集合的操作方式;4.对待不同的工作方式采用统一的语法结构；对待不同的工作方式采用统一的语法结构；5.语言简洁，易学易用；语言简

2、洁，易学易用；表表 3.1 SQL语言的动词语言的动词SQL 功功 能能动动 词词数数 据据 定定 义义CREATE，DROP，ALTER数数 据据 查查 询询SELECT数数 据据 操操 纵纵INSERT，UPDATEDELETE数数 据据 控控 制制GRANT，REVOKE6.视图数据结构视图数据结构3.1.2 SQL语言的基本概念语言的基本概念 SQL语言支持关系数据库三级模式结构语言支持关系数据库三级模式结构视图、基本表和视图、基本表和存储文件。存储文件。SQL视图1视图2基本表1基本表2基本表3基本表4存储文件1存储文件2外模式外模式 模式模式内模式内模式利用利用SQL进行数据库操作

3、可分三个阶段进行：进行数据库操作可分三个阶段进行：定义数据库结构，定义关系模式；定义数据库结构，定义关系模式；向已定义的数据库中添加、删除和修改数据；向已定义的数据库中添加、删除和修改数据；对已存在数据库进行各种查询与统计。对已存在数据库进行各种查询与统计。3.2 数据定义数据定义表表3.2 SQL的的数数据据定定义义语语句句操操 作作 方方 式式操操 作作 对对象象创创 建建删删 除除修修 改改表表CREATET ABLEDROPT ABLEALTERT ABLE视视 图图CREATEVIEWDROP VIEW索索 引引CREATEINDEXDROPINDEX注：注：视图是基于基本表的虚表，

4、索引是依附于基本表的，因视图是基于基本表的虚表，索引是依附于基本表的，因此此SQL通常不提供修改视图定义和修改索引的操作。通常不提供修改视图定义和修改索引的操作。3.2.1 定义、删除与修改基本表定义、删除与修改基本表一、定义基本表一、定义基本表学学 号号Sno姓姓 名名Sname性性 别别Ssex年年 龄龄Sage所所 在系在系Sdept95001李勇李勇男男20CS95002刘晨刘晨女女19IS95003王敏王敏女女18MA95004张立张立男男19IS Student命令格式：命令格式：CREATE TABLE （，）：所要定义的基：所要定义的基本表的名字本表的名字说明：说明：q：组成该

5、表的各个属性（列）：组成该表的各个属性（列）q：涉及相应属性列的完整性约：涉及相应属性列的完整性约束条件束条件q：涉及一个或多个属性列的完：涉及一个或多个属性列的完整性约束条件整性约束条件 例例1 建立一个建立一个“学生学生”表表Student，它由学号，它由学号Sno、姓名、姓名Sname、性别、性别Ssex、年龄、年龄Sage、所在系、所在系Sdept五个属性组成。五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATE TABLE Student (Sno CHAR(5)NOT NULL UNIQUE，Sname CH

6、AR(20)UNIQUE，Ssex CHAR(1)，Sage INT，Sdept CHAR(15)常用完整性约束：常用完整性约束：唯一性约束：唯一性约束：UNIQUE非空值约束：非空值约束：NOT NULL参照完整性约束参照完整性约束主码约束：主码约束：PRIMARY KEYSC学学 号号课课 程程 号号成成 绩绩SnoCnoGrade9500119295001285950013889500229095002380 例例2 建立一个建立一个“学生选课学生选课”表表SC，它由学号它由学号Sno、课程号、课程号Cno，修课成，修课成绩绩Grade组成。组成。CREATE TABLE SC(Sno

7、CHAR(5),Cno CHAR(3),Grade int)二、修改扩充基本表二、修改扩充基本表ALTER TABLE ADD 完整性约束完整性约束 DROP MODIFY ；说明：说明：q：要修改的基本表：要修改的基本表q ADD子句：增加新列和新的完整性约束条件子句：增加新列和新的完整性约束条件q DROP子句：删除指定的完整性约束条件子句：删除指定的完整性约束条件q MODIFY子句：用于修改列名和数据类型子句：用于修改列名和数据类型命令格式：命令格式：例例4 将年龄的数据类型改为半字长整数。将年龄的数据类型改为半字长整数。ALTER TABLE Student MODIFY Sage

8、SMALLINT注：注：修改原有的列定义有可能会破坏已有数据。修改原有的列定义有可能会破坏已有数据。例例5 删除学生姓名必须取唯一值的约束。删除学生姓名必须取唯一值的约束。ALTER TABLE Student DROP UNIQUE(Sname)例例3 向向Student表增加表增加“入学时间入学时间”列，其数据类型为日期型。列，其数据类型为日期型。ALTER TABLE Student ADD Scome DATE 注：注：不论基本表中原来是否已有数据，新增加的列一律为空。不论基本表中原来是否已有数据，新增加的列一律为空。三、删除基本表三、删除基本表DROP TABLE 命令格式：命令格式

9、：说明：说明：删除基本表及其数据，释放相应的存储空间，同时系统也删除基本表及其数据，释放相应的存储空间，同时系统也自动删除在此表上建立的各种索引，也删除了在该表上授自动删除在此表上建立的各种索引，也删除了在该表上授予的操作权限。予的操作权限。表上的视图往往仍然保留，但无法引用。表上的视图往往仍然保留，但无法引用。例例7 删除删除Student表表 DROP TABLEStudent3.2.2 建立与删除索引建立与删除索引建立索引是加快查询速度的有效手段建立索引是加快查询速度的有效手段建立索引建立索引 DBA或表的属主（即建立表的人）根据需要建立或表的属主（即建立表的人）根据需要建立 有些有些D

10、BMS自动建立以下列上的索引自动建立以下列上的索引 PRIMARY KEY UNIQUE维护索引（维护索引（DBMS自动完成）自动完成）使用索引使用索引 DBMS自动选择是否使用索引以及使用哪些索引自动选择是否使用索引以及使用哪些索引一、建立索引一、建立索引 命令格式：命令格式：CREATE UNIQUE CLUSTER INDEX ON(,)；说明：说明：q 用用指定要建索引的基本表名字指定要建索引的基本表名字q 索引可以建立在该表的一索引可以建立在该表的一列列或多列上，各列名之间用或多列上，各列名之间用逗号分隔逗号分隔q 用用指定索引值的排列次序，升序：指定索引值的排列次序，升序：ASC，

11、降序：，降序：DESC。缺省值：。缺省值：ASCq UNIQUE表明此索引的每一个索引值只对应唯一的数表明此索引的每一个索引值只对应唯一的数据记录据记录q CLUSTER表示要建立的索引是聚簇索引表示要建立的索引是聚簇索引 所谓所谓聚簇索引聚簇索引是指索引项的顺序与表中记录的物理顺序一致是指索引项的顺序与表中记录的物理顺序一致的索引组织。的索引组织。例例8 为学生为学生-课程数据库中的课程数据库中的Student，Course，SC三个三个表建立索引。其中表建立索引。其中Student表按学号升序建唯一索引，表按学号升序建唯一索引，Course表按课程号升序建唯一索引，表按课程号升序建唯一索引

12、，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)说明：说明：（1）唯一值索引）唯一值索引对于已含重复值的属性列不能建对于已含重复值的属性列不能建UNIQUE索引索引对某个列建立对某个列建立UNIQUE索引后，插入新记录时索引后，插入新记录时DBMS会自会自动检查新记录在该列上是否取了重复值。这相当于增加了动检

13、查新记录在该列上是否取了重复值。这相当于增加了一个一个UNIQUE约束约束（2）聚簇索引聚簇索引建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致。顺序一致。例：例：CREATE CLUSTER INDEX Stusname ON Student(Sname)在在Student表的表的Sname（姓名）列上建立一个聚簇索引，（姓名）列上建立一个聚簇索引，而且而且Student表中的记录将按照表中的记录将按照Sname值的升

14、序存放值的升序存放（3）在一个基本表上最多只能建立一个聚簇索引；在一个基本表上最多只能建立一个聚簇索引；（4）聚簇索引的适用范围聚簇索引的适用范围 很少对基表进行增删操作很少对基表进行增删操作 很少对其中的变长列进行修改操作很少对其中的变长列进行修改操作 二、删除索引二、删除索引DROP INDEX 例例9 删除删除Student表的表的Stusname索引。索引。DROP INDEX Stusname；注意：注意：建立索引是为了减少查询操作时间，但如果数据增加删建立索引是为了减少查询操作时间，但如果数据增加删改频繁，系统会花费许多时间来维护索引。改频繁，系统会花费许多时间来维护索引。命令格式

15、：命令格式：3.3 查查 询询语句格式语句格式SELECT ALL|DISTINCT ，FROM，WHERE GROUP BY HAVING ORDER BY ASC|DESC 功能：根据功能：根据WHERE子句中的条件，从子句中的条件，从FROM后面列出的表后面列出的表名中找出满足条件的元组，按名中找出满足条件的元组，按SELECT子句后面给出的列名子句后面给出的列名1，列名列名2等目标列的顺序，选出元组中的分量形成结果表。等目标列的顺序，选出元组中的分量形成结果表。说明：说明：SELECT子句子句：指定要显示的属性列：指定要显示的属性列 FROM子句：子句：指定查询对象指定查询对象(基本表

16、或视图基本表或视图)WHERE子句：子句：指定查询条件指定查询条件 GROUP BY子句：子句：对查询结果按指定列的值分组，该对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集属性列值相等的元组为一个组。通常会在每组中作用集函数。函数。HAVING短语：短语：筛选出只有满足指定条件的筛选出只有满足指定条件的组组 ORDER BY子句：子句：对查询结果表按指定列值的升序对查询结果表按指定列值的升序（ASC）或降序（）或降序（DESC）排序）排序 示例数据库示例数据库学生学生-课程数据库课程数据库学生表：学生表：Student(Sno，Sname，Ssex，Sage，S

17、dept)课程表：课程表：Course(Cno，Cname，Cpno，Ccredit)学生选课表：学生选课表：SC(Sno，Cno，Grade)3.3.1 单表查询单表查询 查询仅涉及一个表，是一种最简单的查询操作查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列一、选择表中的若干列二、选择表中的若干元组二、选择表中的若干元组三、对查询结果排序三、对查询结果排序四、使用集函数四、使用集函数五、对查询结果分组五、对查询结果分组 一、选择表中的若干列一、选择表中的若干列例例1 查询全体学生的查询全体学生的学号学号与与姓名姓名。1.查询指定列：查询指定列：SELECT Sno，Sname

18、FROM Student例例2 查询全体学生的查询全体学生的姓名、学号、所在系姓名、学号、所在系。SELECT Sname，Sno，Sdept FROM Student例例3 查询全体学生的详细记录。查询全体学生的详细记录。SELECT Sno，Sname，Ssex，Sage，SdeptFROM Student或或 SELECT *FROM Student2.查询全部列：查询全部列：3.查询经过计算的值：查询经过计算的值：SELECT子句的子句的为表达式为表达式 算术表达式算术表达式 字符串常量字符串常量 函数函数 列别名列别名例例4 查全体学生的查全体学生的姓名姓名及其及其出生年份出生年份。

19、SELECT Sname，2000-Sage FROM Student Sname 2000-Sage 李勇李勇 1976 刘晨刘晨 1977 王名王名 1978 张立张立 1978 输出结果：输出结果：例例5 查询全体学生的姓名、出生年份和所在系，要求用小查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所有系名。写字母表示所有系名。SELECT Sname，Year of Birth:，2000-Sage，ISLOWER(Sdept)FROM Student输出结果：输出结果：Sname Year of Birth:2000-Sage ISLOWER(Sdept)李勇李勇 Year

20、of Birth:1976 cs 刘晨刘晨 Year of Birth:1977 is 王名王名 Year of Birth:1978 ma 张立张立 Year of Birth:1977 is 例例6 使用列使用列别名别名改变查询结果的列标题改变查询结果的列标题 SELECT Sname NAME，Year of Birth:BIRTH，2000-Sage BIRTHDAY，ISLOWER(Sdept)DEPARTMENT FROM Student；输出结果：输出结果：NAME BIRTH BIRTHDAY DEPARTMENT 李勇李勇 Year of Birth:1976 cs 刘晨刘晨

21、 Year of Birth:1977 is 王名王名 Year of Birth:1978 ma 张立张立 Year of Birth:1977 is二、选择表中的若干元组二、选择表中的若干元组1.消除取值重复的行消除取值重复的行 方法：在方法：在SELECT子句中使用子句中使用DISTINCT短语。短语。假设假设SC表中有下列数据表中有下列数据 Sno Cno Grade 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80例例7 查询选修了课程的学生学号。查询选修了课程的学生学号。(1)SELECT Sno FROM SC(2)SE

22、LECT DISTINCT Sno FROM SC结果结果1结果结果2结果结果1：Sno 95001 95001 95001 95002 95002 结果结果2：Sno 95001 95002注意注意:DISTINCT短语的作用范围是所有目标列。短语的作用范围是所有目标列。例：查询选修课程的各种成绩例：查询选修课程的各种成绩错误的写法错误的写法SELECT DISTINCT Cno，DISTINCT Grade FROM SC正确的写法正确的写法 SELECT DISTINCT Cno，Grade FROM SC2.查询满足条件的元组查询满足条件的元组见见P95 表表3.3（1）比较大小：）比

23、较大小：在在WHERE子句的子句的中使用比较运算符中使用比较运算符q=，=，=，!=或或，!，!q 逻辑运算符逻辑运算符NOT +比较运算符比较运算符例例8 查询所有年龄在查询所有年龄在20岁以下的学生姓名及其年龄。岁以下的学生姓名及其年龄。SELECT Sname，Sage FROM StudentWHERE Sage 20例例9 查询考试成绩有不及格的学生的学号查询考试成绩有不及格的学生的学号SELECT DISTINCT Sno FROM SC WHERE Grade 60(2)确定范围：确定范围：使用谓词使用谓词 BETWEEN AND 、NOT BETWEEN AND 例例10 查询

24、年龄在查询年龄在2023岁（包括岁（包括20岁和岁和23岁）之间的学岁）之间的学生的姓名、系别和年龄生的姓名、系别和年龄。SELECT Sname，Sdept，Sage FROM StudentWHERE Sage BETWEEN 20 AND 23年龄不在年龄不在2023之间的学生：之间的学生：Sage NOT BETWEEN 20 AND 23（3）确定集合：）确定集合：使用谓词使用谓词 IN,NOT IN ：用逗号分隔的一组取值：用逗号分隔的一组取值 例例11 查询信息系（查询信息系（IS）、数学系（）、数学系（MA）和计算机科学系）和计算机科学系（CS）学生的姓名和性别。）学生的姓名和

25、性别。SELECT Sname，Ssex FROM StudentWHERE Sdept IN(IS，MA，CS);查询既不是信息系、数学系，也不是计算机科学系的学生：查询既不是信息系、数学系，也不是计算机科学系的学生：Sdept NOT IN(IS，MA，CS)想一想：想一想：确定范围和确定集合查询语句还有没有确定范围和确定集合查询语句还有没有其它的写法？其它的写法？（4）字符匹配：）字符匹配：格式：格式：NOT LIKE ESCAPE 功能：查找指定的属性列值与功能：查找指定的属性列值与相匹配的元组。匹配相匹配的元组。匹配串可以是一个完整的字符串，也可以含有通配符。串可以是一个完整的字符串

26、，也可以含有通配符。通配符：通配符：%和和_q%（百分号）：代表任意长度的字符串；（百分号）：代表任意长度的字符串；q _（下横线）：代表任意单个字符。（下横线）：代表任意单个字符。例：例：a%b表示以表示以a开头，以开头，以b结尾的任意长度的字符串。结尾的任意长度的字符串。如如acb，addgb，ab 等都满足该匹配串等都满足该匹配串例：例：a_b表示以表示以a开头，以开头，以b结尾的长度为结尾的长度为3的任意字符串。的任意字符串。如如acb，afb等都满足该匹配串等都满足该匹配串例例12 查询学号为查询学号为95001的学生的详细情况。的学生的详细情况。SELECT*FROM Studen

27、t WHERE Sno LIKE 95001或或 SELECT *FROM Student WHERE Sno=95001；注：注：当匹配模板为固定字符串时，可以用当匹配模板为固定字符串时，可以用=运算符取代运算符取代 LIKE 谓词谓词 用用!=或或 运算符取代运算符取代 NOT LIKE 谓词谓词例例13 查询所有姓刘学生的姓名、学号和性别。查询所有姓刘学生的姓名、学号和性别。SELECT Sname，Sno，Ssex FROM StudentWHERE Sname LIKE 刘%例例14 查询姓查询姓欧阳欧阳且全名为三个汉字的学生的姓名。且全名为三个汉字的学生的姓名。SELECT Sna

28、me FROM StudentWHERE Sname LIKE 欧阳_ 注意：注意：一个汉字占两个字符的位置，所以匹配串需要两个。一个汉字占两个字符的位置，所以匹配串需要两个。例例13 查询所有姓刘学生的姓名、学号和性别。查询所有姓刘学生的姓名、学号和性别。SELECT Sname，Sno，Ssex FROM Student WHERE Sname LIKE 刘%例例14 查询姓查询姓欧阳欧阳且全名为三个汉字的学生的姓名。且全名为三个汉字的学生的姓名。SELECT Sname FROM Student WHERE Sname LIKE 欧阳_例例15 查询名字中第查询名字中第2个字为个字为阳阳

29、字的学生的姓名和学号。字的学生的姓名和学号。SELECT Sname，Sno FROM Student WHERE Sname LIKE _阳%例例16 查询所有不姓刘的学生姓名。查询所有不姓刘的学生姓名。SELECT Sname，Sno，Ssex FROM Student WHERE Sname NOT LIKE 刘%注意：注意：一个汉字占两个字符的位置，所以匹配串需要两个。一个汉字占两个字符的位置，所以匹配串需要两个。注意：注意：当用户要查询的字符串本身就含有当用户要查询的字符串本身就含有%或或 _ 时，要时，要使用使用ESCAPE 短语对通配符进行转义，短语对通配符进行转义，使其转义为普

30、通字符。使其转义为普通字符。例例17 查询查询DB_Design课程的课程号和学分。课程的课程号和学分。SELECT Cno，Ccredit FROM Course WHERE Cname LIKE DB_Design ESCAPE 说明：说明：ESCAPE短语表示短语表示为换码字符，这样匹配串中紧为换码字符，这样匹配串中紧跟在跟在后面的字符后面的字符“_”不再具有通配符的含义，转义为普通不再具有通配符的含义，转义为普通的的“”字符。字符。（5）涉及空值的查询：）涉及空值的查询：使用谓词使用谓词 IS NULL 或或 IS NOT NULL 例例18 某些学生选修课程后没有参加考试，所以有选课

31、记录，某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECT Sno，Cno FROM SC WHERE Grade IS NULL例例19 查所有有成绩的学生学号和课程号。查所有有成绩的学生学号和课程号。SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL注意：注意：“IS NULL”不能用不能用“=NULL”代代替替（6）多重查询条件：）多重查询条件：用逻辑运算符用逻辑运算符AND和和 OR来联结多个查询条件来联结多个查询条件v AN

32、D的优先级高于的优先级高于ORv 可以用括号改变优先级可以用括号改变优先级例例20 查询计算机系年龄在查询计算机系年龄在20岁以下的学生姓名。岁以下的学生姓名。SELECT Sname FROM Student WHERE Sdept=CS AND Sage=20 AND Sage=23三、对查询结果排序三、对查询结果排序使用使用ORDER BY子句子句v 可以按一个或多个属性列排序可以按一个或多个属性列排序v 升序：升序：ASC；降序：；降序：DESC；缺省值为升序；缺省值为升序当排序列含空值时当排序列含空值时v ASC：排序列为空值的元组最后显示：排序列为空值的元组最后显示v DESC：排

33、序列为空值的元组最先显示：排序列为空值的元组最先显示 例例21 查询选修了查询选修了3号课程的学生的学号及其成绩，查询结果号课程的学生的学号及其成绩，查询结果按分数降序排列。按分数降序排列。SELECT Sno，Grade FROM SC WHERE Cno=3 ORDER BY Grade DESC查询结果：查询结果：Sno Grade 95010 95024 95007 92 95003 82 95010 82 95009 75 95014 61 95002 55 例例22 查询全体学生情况，查询结果按所在系的系号升序排查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄

34、降序排列。列，同一系中的学生按年龄降序排列。SELECT *FROM StudentORDER BY Sdept，Sage DESC五、使用集函数五、使用集函数5类主要集函数类主要集函数v计数计数COUNT（DISTINCT|ALL*）COUNT（DISTINCT|ALL）v计算总和计算总和SUM（DISTINCT|ALL v 计算平均值计算平均值AVG（DISTINCT|ALL）统计元组个数统计一列中值的个数此列必须是此列必须是数值型数值型v求最大值求最大值 MAX（DISTINCT|ALL）v求最小值求最小值 MIN（DISTINCT|ALL）DISTINCT短语：在计算时要取消指定列中的

35、重复值短语：在计算时要取消指定列中的重复值 ALL短语：不取消重复值短语：不取消重复值 ALL为缺省值为缺省值例例23 查询学生总人数。查询学生总人数。SELECT COUNT(*)FROM Student例例24 查询选修了课程的学生人数。查询选修了课程的学生人数。SELECT COUNT(DISTINCT Sno)FROM SC；注：注：用用DISTINCT以避免重复计算学生人数以避免重复计算学生人数例例25 计算计算1号课程的学生平均成绩。号课程的学生平均成绩。SELECT AVG(Grade)FROM SC WHERE Cno=1 例例26 查询选修查询选修1号课程的学生最高分数。号课

36、程的学生最高分数。SELECT MAX(Grade)FROM SC WHERE Cno=1 五、对查询结果分组五、对查询结果分组使用使用GROUP BY子句分组子句分组 细化集函数的作用对象细化集函数的作用对象v 未对查询结果分组，集函数将作用于整个查询结果未对查询结果分组，集函数将作用于整个查询结果v 对查询结果分组后，集函数将分别作用于每个组对查询结果分组后，集函数将分别作用于每个组 例例27 求各个课程号及相应的选课人数。求各个课程号及相应的选课人数。SELECT Cno，COUNT(Sno)FROM SCGROUP BY Cno Cno COUNT(Sno)1 22 2 34 3 44

37、 4 33 5 48说明：说明：q GROUP BY子句的作用对象是查询的中间结果表子句的作用对象是查询的中间结果表q 分组方法：按指定的一列或多列值分组，值相等的为一组分组方法：按指定的一列或多列值分组，值相等的为一组q 使用使用GROUP BY子句后，子句后，SELECT子句的列名列表中只能出子句的列名列表中只能出现分组属性和集函数现分组属性和集函数使用使用HAVINGHAVING短语筛选最终输出结果短语筛选最终输出结果例例28 查询选修了查询选修了3门以上课程的学生学号。门以上课程的学生学号。SELECT Sno FROM SC GROUP BY SnoHAVING COUNT(*)3

38、例例29 查询有查询有3门门以上课程是以上课程是90分以上的学生的学号及分以上的学生的学号及（90分以上的）课程数。分以上的）课程数。SELECT Sno,COUNT(*)FROM SCWHERE Grade=90GROUP BY Sno HAVING COUNT(*)=3说明：说明：只有满足只有满足HAVING短语指定条件的组才输出。短语指定条件的组才输出。HAVINGHAVING短语与短语与WHEREWHERE子句的区别：作用对象不同子句的区别：作用对象不同学号学号 课号课号成绩成绩890237 1890237 18585890237 2890237 27878890237 4890237

39、 47575902783 1902783 17272902783 3902783 38080903829 1903829 18282903829 2903829 28383918327 1918327 18787选课选课每一行都要检查满足每一行都要检查满足与否的条件要用与否的条件要用WHEREWHERE子句表达子句表达每一分组检查满足每一分组检查满足与否的条件要用与否的条件要用HAVINGHAVING子句表达子句表达注意注意:不是每一行不是每一行都检查都检查注：注：HAVINGHAVING子句一定在子句一定在GROUP BYGROUP BY子句中使用。子句中使用。3.3.2 连接查询连接查询

40、同时涉及多个表的查询称为连接查询。同时涉及多个表的查询称为连接查询。1.一般格式：一般格式：F.比较运算符：比较运算符：=、=、=、!=F.BETWEEN.AND.2.连接字段：连接谓词中的列名称为连接字段连接字段：连接谓词中的列名称为连接字段注：注：连接条件中的各连接字段类型必须是可比的，但连接条件中的各连接字段类型必须是可比的，但不必是相同的。不必是相同的。3.连接操作的执行过程连接操作的执行过程（1）首先在表）首先在表1中找到第一个元组，然后从头开始扫描表中找到第一个元组，然后从头开始扫描表2，逐一查找满，逐一查找满足连接件的元组，找到后就将表足连接件的元组，找到后就将表1中的第一个元组

41、与该元组拼接起来，中的第一个元组与该元组拼接起来，形成结果表中一个元组。形成结果表中一个元组。（2）表）表2全部查找完后，再找表全部查找完后，再找表1中第二个元组，然后再从头开始扫描表中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元中的第二个元组与该元组拼接起来，形成结果表中一个元组。组拼接起来，形成结果表中一个元组。（3）重复上述操作，直到表）重复上述操作，直到表1中的全部元组都处理完毕中的全部元组都处理完毕。4.SQL中连接查询的主要类型：中连接查询的主要类型：广义笛卡尔积广义笛卡尔积 等值连接等值

42、连接(含自然连接含自然连接)非等值连接查询非等值连接查询 自身连接查询自身连接查询 外连接查询外连接查询 复合条件连接查询复合条件连接查询n 不带连接谓词的连接不带连接谓词的连接n 很少使用很少使用一、等值与非等值连接查询一、等值与非等值连接查询 等值连接等值连接 自然连接自然连接 非等值连接非等值连接1.等值连等值连:接连接运算符为接连接运算符为“=”的连接操作的连接操作n.=.n任何子句中引用表任何子句中引用表1和表和表2中同名属性时，都必须加表名中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀前缀。引用唯一属性名时可以加也可以省略表名前缀。例例30 查询每个学生及

43、其选修课程的情况查询每个学生及其选修课程的情况SELECT Student.*，SC.*FROM Student，SCWHERE Student.Sno=SC.Sno结果表结果表 Student.Sno Sname Ssex Sage Sdept SC.Sno Cno Grade 95001 李勇李勇 男男 20 CS 95001 1 92 95001 李勇李勇 男男 20 CS 95001 2 85 95001 李勇李勇 男男 20 CS 95001 3 88 95002 刘晨刘晨 女女 19 IS 95002 2 90 95002 刘晨刘晨 女女 19 IS 95002 3 80 Sno

44、SnameSsexSageSdept95001 李勇李勇 男男 20 CS95002 刘晨刘晨 女女 19 IS95003 王敏王敏 女女 18 MA95004 张立张立 男男 19 ISStudent表表 Sno Cno95001 195001 295001 3 95002 2Grade 92 85 88 9095002 3 80SC表表 2.自然连接：自然连接：等值连接的一种特殊情况，把目标列中重复的等值连接的一种特殊情况，把目标列中重复的属性列去掉。属性列去掉。例例31 对例对例30用自然连接完成。用自然连接完成。SELECT Student.Sno，Sname，Ssex，Sage，Sd

45、ept，Cno，Grade FROM Student，SC WHERE Student.Sno=SC.Sno3.非等值连接：连接运算符非等值连接：连接运算符 不是不是=的连接操作的连接操作 .比较运算符：比较运算符：、=、=、!=.BETWEEN.AND.二、自身连接二、自身连接 一个表与其自己进行连接，称为表的一个表与其自己进行连接，称为表的自身连接；自身连接；需要给表起别名以示区别；需要给表起别名以示区别；由于所有属性名都是同名属性，因此必须使用别名前缀。由于所有属性名都是同名属性，因此必须使用别名前缀。例例32 查询每一门课的间接先修课（即先修课的先修课）查询每一门课的间接先修课（即先修

46、课的先修课）SELECT FIRST.Cno，SECOND.CpnoFROM Course FIRST，Course SECONDWHERE FIRST.Cpno=SECOND.Cno别名别名三、外连接三、外连接外连接与普通连接的区别：外连接与普通连接的区别：普通普通连接操作只输出满足连接条件的元组；连接操作只输出满足连接条件的元组；外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出。一并输出。例例 33 查询每个学生及其选修课程的情况包括没有选修课程查询每个学生及其选修课程的情况包括没有选修课程的学生的学生用外连接

47、操作。用外连接操作。SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student，SCWHERE Student.Sno=SC.Sno(*)；结果：结果：Student.Sno Sname Ssex Sage Sdept Cno Grade 95001 李勇李勇 男男 20 CS 1 92 95001 李勇李勇 男男 20 CS 2 85 95001 李勇李勇 男男 20 CS 3 88 95002 刘晨刘晨 女女 19 IS 2 90 95002 刘晨刘晨 女女 19 IS 3 80 95003 王敏王敏 女女 18 MA 9

48、5004 张立张立 男男 19 IS没选课的学生没选课的学生四、复合条件连接四、复合条件连接WHERE子句中含多个连接条件时，称为复合条件连接。子句中含多个连接条件时，称为复合条件连接。例例34 查询选修查询选修2号课程且成绩在号课程且成绩在90分以上的所有学生的分以上的所有学生的学号、姓名学号、姓名SELECT Student.Sno,student.SnameFROM Student,SCWHERE Student.Sno=SC.Sno AND SC.Cno=2 AND SC.Grade 90连接谓连接谓词词其它限定条件其它限定条件例例35 查询每个学生的学号、姓名、选修的课程名及成绩查询

49、每个学生的学号、姓名、选修的课程名及成绩SELECT Student.Sno，Sname，Cname，GradeFROM Student，SC，CourseWHERE Student.Sno=SC.Sno and SC.Cno=Course.Cno多表连接多表连接 结果：结果：Student.Sno Sname Cname Grade 95001 李勇李勇 数据库数据库 92 95001 李勇李勇 数学数学 85 95001 李勇李勇 信息系统信息系统 88 95002 刘晨刘晨 数学数学 90 95002 刘晨刘晨 信息系统信息系统 80 3.3.3 嵌套查询嵌套查询嵌套查询概述嵌套查询概述

50、q一个一个SELECT-FROM-WHERE语句称为一个查询块语句称为一个查询块q将一个查询块嵌套在另一个查询块的将一个查询块嵌套在另一个查询块的WHERE子句或子句或HAVING短语的条件中的查询称为嵌套查询短语的条件中的查询称为嵌套查询q 嵌套查询举例嵌套查询举例 SELECT Sname外层查询外层查询/父查询父查询 FROM Student WHERE Sno IN （SELECT Sno 内层查询内层查询/子查询子查询 FROM SC WHERE Cno=2）q子查询的限制子查询的限制SELECT语句中不能使用语句中不能使用ORDER BY子句，子句，ORDERBY 子句只能对最终结