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 SQL的特点 3.1.3 SQL的基本概念数据库系统原理厦门大学计算机科学系林子雨2017版3.1.1SQL的产生与发展 1974年，由Boyce和Chamberlin提出，并在
2、IBM公司研制的关系数据库管理系统原型SystemR上实现 1986年10月，美国国家标准局的数据库委员会X3H2批准了SQL作为关系数据库语言的美国标准，并公布了SQL标准文本 1987年，国际标准化组织通过这一标准数据库系统原理厦门大学计算机科学系林子雨2017版3.1.2SQL的特点SQL的特点n 1.综合统一n 2.高度非过程化n 3.面向集合的操作方式n 4.以同一种语法结构提供两种使用方法n 5.语言简洁，易学易用数据库系统原理厦门大学计算机科学系林子雨2017版1、综合统一（操纵三级模式）SQL视图1视图2基表1基表2基表3基表4存储文件1存储文件2外模式模式内模式数据库系统原理
3、厦门大学计算机科学系林子雨2017版5、语言简捷，易学易用表语言的动词SQL 功能动词数据定义CREATE，DROP，ALTER数据查询SELECT数据操纵数据控制GRANT，REVOKE3.1SQLINSERT，UPDATE，DELETE数据库系统原理厦门大学计算机科学系林子雨2017版第3章关系数据库标准语言SQL 3.1 SQL概述 3.2 学生-课程数据库 3.3 数据定义 3.4 数据查询 3.5 数据更新 3.6 视图数据库系统原理厦门大学计算机科学系林子雨2017版3.2学生-课程数据库学生-课程数据库 学生表：Student(Sno，Sname，Ssex，Sage，Sdept)
4、 课程表：Course(Cno，Cname，Cpno，Ccredit) 学生选课表：SC(Sno，Cno，Grade) 数据库系统原理厦门大学计算机科学系林子雨2017版3.2学生-课程数据库学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19ISStudent(a)数据库系统原理厦门大学计算机科学系林子雨2017版3.2学生-课程数据库课程号课程名先行课学分CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64
5、Course(b)数据库系统原理厦门大学计算机科学系林子雨2017版3.2学生-课程数据库学号课程号成绩SnoCnoGrade9500119295001285950013889500229095002380SC(c)数据库系统原理厦门大学计算机科学系林子雨2017版提纲 3.1 SQL概述 3.2 学生-课程数据库 3.3 数据定义 3.4 数据查询 3.5 数据更新 3.6 视图数据库系统原理厦门大学计算机科学系林子雨2017版3.3数据定义 3.3.1 模式的定义与删除 3.3.2 基本表的定义、删除与修改 3.3.3 索引的建立与删除数据库系统原理厦门大学计算机科学系林子雨2017版3.
6、3数据定义数据库系统原理厦门大学计算机科学系林子雨2017版3.3数据定义 3.3.1 模式的定义与删除 3.3.2 基本表的定义、删除与修改 3.3.3 索引的建立与删除数据库系统原理厦门大学计算机科学系林子雨2017版3.3.1模式的定义与删除一、定义模式 CREATE SCHEMA AUTHORIZATION Ps:1、若没有指定，那么隐含为2、要创建模式，调用该命令的用户必须具有DBA权限，或者获得了DBA授予的CREATE SCHEMA的权限数据库系统原理厦门大学计算机科学系林子雨2017版例题例1 定义一个学生-课程模式S-TCREATE SCHEMA “S-T” AUTHORIZ
7、ATION WANG;为用户“WANG”定义了一个模式S-T数据库系统原理厦门大学计算机科学系林子雨2017版3.3.1模式的定义与删除二、删除模式 DROP SCHEMA 其中CASCADE和RESTRICT两者必选其一1、CASCADE（级联），表示在删除模式同时把该模式中所有的数据库对象全部一起删除2、RESTRICT（限制），表示若该模式下已定义了下属的数据库对象，则拒绝删除数据库系统原理厦门大学计算机科学系林子雨2017版例题例4 删除模式ZHANGDROP SCHEMA ZHANG CASCADE数据库系统原理厦门大学计算机科学系林子雨2017版3.3数据定义 3.3.1 模式的定
8、义与删除 3.3.2 基本表的定义、删除与修改 3.3.3 索引的建立与删除数据库系统原理厦门大学计算机科学系林子雨2017版3.3.2基本表的定义、删除与修改一、定义基本表 ：所要定义的基本表的名字 ：组成该表的各个属性（列） ：涉及相应属性列的完整性约束条件 ：涉及一个或多个属性列的完整性约束条件CREATE TABLE （ ， ， ）；数据库系统原理厦门大学计算机科学系林子雨2017版例题例1 建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号是主键，并且姓名取值也唯一。 CREATE TABLE Stud
9、ent (Sno CHAR(5) primary key, Sname CHAR(8) UNIQUE, Ssex CHAR(2) , Sage INT, Sdept CHAR(10); 数据库系统原理厦门大学计算机科学系林子雨2017版例题（续）SnoSnameSsexSageSdept 字符型 字符型 字符型 整数 字符型长度为5 长度为8 长度为2 长度为10不能为空值数据库系统原理厦门大学计算机科学系林子雨2017版定义基本表（续） 常用完整性约束 主码约束： PRIMARY KEY 唯一性约束：UNIQUE 非空值约束：NOT NULL 参照完整性约束PRIMARY KEY与 UNIQ
10、UE的区别？数据库系统原理厦门大学计算机科学系林子雨2017版例题（续） 例2 建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩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子句：删
11、除指定的完整性约束条件nALTER COLUMN子句：用于修改列名和数据类型nDROP COLUMN子句：用于删除列数据库系统原理厦门大学计算机科学系林子雨2017版例题例2 向Student表增加“入学时间”列，其数据类型为日期型。 ALTER TABLE Student ADD Scome DATETIME;n不论基本表中原来是否已有数据，新增加的列一律为空值。数据库系统原理厦门大学计算机科学系林子雨2017版例题例3 将入学日期的数据类型改为字符型。ALTER TABLE Student ALTER COLUMN Scome char(8);n注：修改原有的列定义有可能会破坏已有数据数据
12、库系统原理厦门大学计算机科学系林子雨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 基本表的定义、删除与修改 3.3.3 索引的建立与删除数据库系统原理厦门大学计算机科学系林
13、子雨2017版3.3.3索引的建立与删除 建立索引是加快查询速度的有效手段 建立索引 DBA或表的属主（即建立表的人）根据需要建立 有些DBMS自动建立以下列上的索引 PRIMARY KEY UNIQUE 维护索引 DBMS自动完成 使用索引 DBMS自动选择是否使用索引以及使用哪些索引数据库系统原理厦门大学计算机科学系林子雨2017版一、建立索引 语句格式CREATE UNIQUE CLUSTER INDEX ON (, )；nn用指定要建索引的基本表名字n索引可以建立在该表的一列或多列上，各列名之间用逗号分隔n用指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCnUNIQU
14、E表明此索引的每一个索引值只对应唯一的数据记录nCLUSTER表示要建立的索引是聚簇索引数据库系统原理厦门大学计算机科学系林子雨2017版建立索引（续） 唯一值索引 对于已含重复值的属性列不能建UNIQUE索引 对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束数据库系统原理厦门大学计算机科学系林子雨2017版建立索引（续） 聚簇索引 建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致例：CREATE CLUSTER INDEX Stusname ON
15、Student(Sname)；在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放 数据库系统原理厦门大学计算机科学系林子雨2017版建立索引（续） 在一个基本表上最多只能建立一个聚簇索引 聚簇索引的用途：对于某些类型的查询，可以提高查询效率 聚簇索引的适用范围 很少对基表进行增删操作 很少对其中的变长列进行修改操作 聚簇索引的不适用情形 表记录太少 经常插入、删除、修改的表 数据分布平均的表字段数据库系统原理厦门大学计算机科学系林子雨2017版建立索引（续）在下列三种情况下，有必要建立簇索引：(1)查询语句中采用该字段作为排序列
16、(2)需要返回局部范围的大量数据(3)表格中某字段内容的重复性比较大例如，student表中dno(系号)一列有大量重复数据，当在dno列上建立了簇索引后，下面的连接查询速度会加快。数据库系统原理厦门大学计算机科学系林子雨2017版建立索引（续）多列索引和多个单列索引考虑两种不同的建立索引方式：case 1:对c1,c2,c3三列按此顺序添加一个多列索引;case 2: 对c1,c2,c3分别建立三个单列索引;问题1:按c1搜索时，哪种索引效率快?答：case2问题2:按C2搜索时，哪种索引效率快?答：case2,并且，case1的索引无效问题3：按C1,C2,C3搜索哪种效率快?答：case
17、1问题4：按C2,C3,C1搜索时哪种效率快?答：case2,因没有按多列索引的序搜索，case1的索引没有使用到。覆盖查询简单的说就是所有查询列被所使用的索引覆盖的查询数据库系统原理厦门大学计算机科学系林子雨2017版建立索引（续）如何去建立一个多列索引，最重要的一个问题是如何安排列的顺序是至关重要的比如需要对一个表tb里面的两个字段foo,bar建一个索引，那么索引的顺序是（foo,bar)还是(bar,foo)呢假设tb表有1700条记录，foo字段有750个不同的记录，那么foo字段上的cardinality是750。总规则可以说是cardinality越大的字段应该排在索引的第一位就
18、是说索引的位置是(foo,bar)，因为cardinality越大那么第一次取出来的记录集就越小，再进行第二次查询的次数就越少了。数据库系统原理厦门大学计算机科学系林子雨2017版建立索引（续）当在同一表格中建立簇索引和非簇索引时，先建立簇索引后建非簇索引比较好。因为如先建非簇索引的话，当建立簇索引时，SQLServer会自动将非簇索引删除，然后重新建立非簇索引。每个表仅可以有一个簇索引，最多可以有249个非簇索引。它们均允许以一个或多个字段作为索引关键字(IndexKey)，但最多只能有16个字段。SQLServer只对那些能加快数据查询速度的索引才能被选用。如果利用索引检索还不如顺序扫描速
19、度快，SQLServer仍用扫描方法检索数据。建立不能被采用的索引只会增加系统的负担，降低检索速度。因此，可利用性是建立索引的首要条件。数据库系统原理厦门大学计算机科学系林子雨2017版例题 例6 为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATE UNIQUE INDEX Stusno ON Student(Sno);CREATE UNIQUE INDEX Coucno ON Course(Cno);CREATE UNIQUE INDEX
20、SCno ON SC(Sno ASC, Cno DESC); 数据库系统原理厦门大学计算机科学系林子雨2017版二、删除索引DROP INDEX ； 删除索引时，系统会从数据字典中删去有关该索引的描述。例7 删除Student表的Stusname索引。DROP INDEX Student.Stusname；数据库系统原理厦门大学计算机科学系林子雨2017版第3章关系数据库标准语言SQL 3.1 SQL概述 3.2 学生-课程数据库 3.3 数据定义 3.4 数据查询 3.5 数据更新 3.6 视图数据库系统原理厦门大学计算机科学系林子雨2017版3.4查询n3.4.1 单表查询n3.4.2 连
21、接查询n3.4.3 嵌套查询n3.4.4 集合查询n3.4.5 SELECT 语句的一般格式 数据库系统原理厦门大学计算机科学系林子雨2017版3.4查询 语句格式SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ；数据库系统原理厦门大学计算机科学系林子雨2017版3.4查询 SELECT子句：指定要显示的属性列 FROM子句：指定查询对象(基本表或视图) WHERE子句：指定查询条件 GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。 HAVING短
22、语：筛选出只有满足指定条件的组 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单表查询 查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用集函数五、对查询结果分组 数据库系统原理厦门大学计算机科学系林子雨2017版查询指定列例1 查询全体学生的学号与姓名。SELECT Sn
23、o, SnameFROM Student;例2 查询全体学生的姓名、学号、所在系。SELECT Sname，Sno，SdeptFROM Student；数据库系统原理厦门大学计算机科学系林子雨2017版查询全部列例3 查询全体学生的详细记录。SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student； 或SELECT *FROM Student； 数据库系统原理厦门大学计算机科学系林子雨2017版3.查询经过计算的值SELECT子句的为表达式 算术表达式 字符串常量 函数 列别名 等 数据库系统原理厦门大学计算机科学系林子雨2017版3.查询经过计算的值例4 查
24、全体学生的姓名及其出生年份。SELECT Sname,2011-SageFROM Student;数据库系统原理厦门大学计算机科学系林子雨2017版3.查询经过计算的值例5 查询全体学生的姓名、出生年份和所在系。在出生年份前面增加一个说明，在系名称后面增加一个“系”作为表示SELECT Sname, 出生年份: , 2011-Sage, Sdept + 系FROM Student;数据库系统原理厦门大学计算机科学系林子雨2017版例5.1使用列别名改变查询结果的列标题SELECT Sname 姓名, Year of Birth: 生日标识, 2011-Sage 生日, Sdept+系 系名FR
25、OM Student;数据库系统原理厦门大学计算机科学系林子雨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 SC;或(默认 ALL) SELE
26、CT 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.查询满足条件的元组表3.3常用的查询条件查询条件谓 词比较=，=，=，!=，!，!；NOT + 上述比较运算符确定范围BETWEENA
27、ND，NOTBETWEENAND确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重条件AND，ORWHERE子句常用的查询条件数据库系统原理厦门大学计算机科学系林子雨2017版(1)比较大小在WHERE子句的中使用比较运算符l =，=，=，!= 或 ，!，!， l 逻辑运算符NOT + 比较运算符例8 查询所有年龄在20岁以下的学生姓名及其年龄。SELECT Sname, Sage FROM Student WHERE Sage = 20； 数据库系统原理厦门大学计算机科学系林子雨2017版(2)确定范围 使用谓词 BETWEEN AND NOT
28、BETWEEN AND 例10 查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。 SELECT Sname, Sdept, SageFROM StudentWHERE Sage BETWEEN 20 AND 23; 数据库系统原理厦门大学计算机科学系林子雨2017版例题（续）例11 查询年龄不在2023岁之间的学生姓名、系别和年龄。SELECT Sname，Sdept，SageFROM StudentWHERE Sage NOT BETWEEN 20 AND 23；数据库系统原理厦门大学计算机科学系林子雨2017版(3)确定集合使用谓词 IN , NOT IN ：用逗号
29、分隔的一组取值例12查询信息系（IS）和计算机科学系（CS）学生的姓名和性别。SELECT Sname, SsexFROM StudentWHERE Sdept IN ( IS, CS );数据库系统原理厦门大学计算机科学系林子雨2017版(3)确定集合例13查询既不是信息系又不是计算 机科学系的学生的姓名和性别。SELECT Sname，SsexFROM Student WHERE Sdept NOT IN ( IS，CS );数据库系统原理厦门大学计算机科学系林子雨2017版(4)字符串匹配 NOT LIKE ESCAPE ：指定匹配模板 匹配模板：固定字符串或含通配符的字符串 当匹配模板
30、为固定字符串时， 可以用 = 运算符取代 LIKE 谓词 用 != 或 运算符取代 NOT LIKE 谓词数据库系统原理厦门大学计算机科学系林子雨2017版通配符w % (百分号) 代表任意长度（长度可以为0）的字符串 例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串 _ (下横线) 代表任意单个字符 例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串数据库系统原理厦门大学计算机科学系林子雨2017版ESCAPE短语：当用户要查询的字符串本身就含有 % 或 _ 时，要使用ESCAPE 短语对通配符进行转义。数
31、据库系统原理厦门大学计算机科学系林子雨2017版例题1) 匹配模板为固定字符串 例14 查询学号为95001的学生的详细情况。 SELECT * FROM Student WHERE Sno LIKE 95001;等价于： SELECT * FROM Student WHERE Sno = 95001；数据库系统原理厦门大学计算机科学系林子雨2017版例题（续）2) 匹配模板为含通配符的字符串例15 查询所有姓刘学生的姓名、学号和性别。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname LIKE 刘%；数据库系统原理厦门大学计算机科学系林子雨2017
32、版例题（续）匹配模板为含通配符的字符串（续）例16 查询姓“刘且全名为三个汉字的学生的姓名。 SELECT Sname 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时，一个汉字需要两个
33、_；当数据库字符集为GBK时，一个汉字只需要一个_数据库系统原理厦门大学计算机科学系林子雨2017版例题（续）匹配模板为含通配符的字符串（续）例17 查询名字中第2个字为“敏”字的学生的姓名和学号。 SELECT Sname，Sno FROM Student WHERE Sname LIKE _敏%；数据库系统原理厦门大学计算机科学系林子雨2017版例题（续）例18 查询所有不姓刘的学生姓名。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname NOT LIKE 刘%；数据库系统原理厦门大学计算机科学系林子雨2017版例题（续）3) 使用换码字符将通配
34、符转义为普通字符 例19 查询课程名称中包含“面向对象_C+课程”的课程号和学分。 SELECT Cno，Ccredit FROM Course WHERE Cname LIKE %面向对象_C+% 数据库系统原理厦门大学计算机科学系林子雨2017版例题（续） SELECT Cno，Ccredit FROM Course WHERE Cname LIKE %面向对象 _C+ % ESCAPE 数据库系统原理厦门大学计算机科学系林子雨2017版例题（续）使用换码字符将通配符转义为普通字符(续)例20 查询以DB_开头，且倒数第3个字符为 i的课程的详细情况。 SELECT * FROM Cour
35、se WHERE Cname LIKE DB_%i_ _ ESCAPE ；数据库系统原理厦门大学计算机科学系林子雨2017版(5)涉及空值的查询 使用谓词 IS NULL 或 IS NOT NULL “IS NULL” 不能用 “= NULL” 代替例21 有些课没有先修课程。查询没有先修课程的课程名称。Select CnameFrom CourseWhere Cpno IS NULL数据库系统原理厦门大学计算机科学系林子雨2017版(6)多重条件查询用逻辑运算符AND和 OR来联结多个查询条件 AND的优先级高于OR 可以用括号改变优先级可用来实现多种其他谓词 NOT IN NOT BETW
36、EEN AND 数据库系统原理厦门大学计算机科学系林子雨2017版例题例23 查询计算机系年龄在20岁以下的学生姓名。 SELECT Sname FROM Student WHERE Sdept= CS AND Sage=20 AND Sage=23；数据库系统原理厦门大学计算机科学系林子雨2017版三、对查询结果排序使用ORDER BY子句 可以按一个或多个属性列排序 升序：ASC；降序：DESC；缺省值为升序当排序列含空值时空值的显示顺序由具体系统实现来决定例如按升序排，含空值的元组最后显示按降序排，空值的元组最先显示各个系统的实现可以不同，只要保持一致即可数据库系统原理厦门大学计算机科学
37、系林子雨2017版对查询结果排序（续）例24 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。 SELECT Sno,Grade FROM SC WHERE Cno= 3 ORDER BY Grade DESC;数据库系统原理厦门大学计算机科学系林子雨2017版对查询结果排序（续）例25 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。 SELECT * FROM Student ORDER BY Sdept, Sage DESC; 数据库系统原理厦门大学计算机科学系林子雨2017版四、使用集函数5类主要聚集函数 计数COUNT（DISTINCT
38、|ALL *）COUNT（DISTINCT|ALL ） 计算总和SUM（DISTINCT|ALL ） 计算平均值AVG（DISTINCT|ALL ）数据库系统原理厦门大学计算机科学系林子雨2017版使用集函数（续）求最大值MAX（DISTINCT|ALL ） 求最小值MIN（DISTINCT|ALL ） DISTINCT短语：在计算时要取消指定列中的重复值 ALL短语：不取消重复值 ALL为缺省值数据库系统原理厦门大学计算机科学系林子雨2017版使用集函数（续）例26 查询学生总人数。例27 查询选修了课程的学生人数。 SELECT COUNT(DISTINCT Sno) FROM SC；注：
39、用DISTINCT以避免重复计算学生人数 SELECT COUNT(*) FROM Student；数据库系统原理厦门大学计算机科学系林子雨2017版使用集函数（续）例28 计算2号课程的学生平均成绩。例29 查询选修3号课程的学生最高分数。 SELECT AVG(Grade) FROM SC WHERE Cno= 2 ； SELECT MAX(Grade) FROM SC WHER Cno= 3； 数据库系统原理厦门大学计算机科学系林子雨2017版五、对查询结果分组使用GROUP BY子句分组 细化聚集函数的作用对象 未对查询结果分组，聚集函数将作用于整个查询结果 对查询结果分组后，聚集函数
40、将分别作用于每个组 数据库系统原理厦门大学计算机科学系林子雨2017版使用GROUPBY子句分组例30 求各个课程号及相应的选课人数。 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我有下面
41、个Orders表：在，我希望找每个客的金（ ）。语句1：SELECTCustomer,SUM(OrderPrice)FROMOrders语句2：SELECTCustomer,SUM(OrderPrice)FROMOrdersGROUPBYCustomer课堂作业数据库系统原理厦门大学计算机科学系林子雨2017版使用GROUPBY子句分组语句1：SELECTCustomer,SUM(OrderPrice)FROMOrdersCustomerSUM(OrderPrice)Bush5700Carter5700Bush5700Bush5700Adams5700Carter5700数据库系统原理厦门大学
42、计算机科学系林子雨2017版使用GROUPBY子句分组语句2：SELECTCustomer,SUM(OrderPrice)FROMOrdersGROUPBYCustomerCustomerSUM(OrderPrice)Bush2000Carter1700Adams2000数据库系统原理厦门大学计算机科学系林子雨2017版对查询结果分组（续）GROUP BY子句的作用对象是查询的中间结果表分组方法：按指定的一列或多列值分组，值相等的为一组; group by 是先排序后分组使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和聚集函数，或者说，select 后面的所有列中,没
43、有使用聚合函数的列,必须出现在 group by 后面如果要用到group by 一般用到的就是“每”这个字,例如：每个部门有多少人 就要用到分组的技术数据库系统原理厦门大学计算机科学系林子雨2017版对查询结果分组（续）selectSno,CnofromSCgroupbySno-将会出现错误-消息8120，级别16，状态1，第1行选择列表中的列SC.Cno无效，因为该列没有包含在聚合函数或GROUPBY子句中。这就是我们需要注意的一点，如果在返回集字段中，这些字段要么就要包含在GroupBy语句的后面，作为分组的依据；要么就要被包含在聚合函数中。selectSnoas学号,COUNT(*)a
44、s选课门数fromSCgroupbySno数据库系统原理厦门大学计算机科学系林子雨2017版使用HAVING短语筛选最终输出结果例31 查询选修了2门及以上课程的学生学号。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 2； 数据库系统原理厦门大学计算机科学系林子雨2017版例题例32 查询有2门及以上课程是85分以上的 学生的学号及（85分以上的）课程数 SELECT Sno, COUNT(*) FROM SC WHERE Grade=85 GROUP BY Sno HAVING COUNT(*)=2;数据库系统原理厦门大学计算机科学系林子雨
45、2017版使用HAVING短语筛选最终输出结果 只有满足HAVING短语指定条件的组才输出 HAVING短语与WHERE子句的区别：作用对象不同 WHERE子句作用于基表或视图，从中选择满足条件的元组。 HAVING短语作用于组，从中选择满足条件的组。 数据库系统原理厦门大学计算机科学系林子雨2017版3.4查询n3.4.1 单表查询n3.4.2 连接查询n3.4.3 嵌套查询n3.4.4 集合查询n3.4.5 SELECT 语句的一般格式 数据库系统原理厦门大学计算机科学系林子雨2017版3.4.2连接查询同时涉及多个表的查询称为连接查询用来连接两个表的条件称为连接条件或连接谓词 一般格式：
46、. . 比较运算符：=、=、=、!= . BETWEEN . AND .数据库系统原理厦门大学计算机科学系林子雨2017版连接查询（续） 连接字段连接谓词中的列名称为连接字段连接条件中的各连接字段类型必须是可比的，但不必是相同的数据库系统原理厦门大学计算机科学系林子雨2017版连接操作的执行过程 嵌套循环法(NESTED-LOOP) 首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。 表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元
47、组与该元组拼接起来，形成结果表中一个元组。 重复上述操作，直到表1中的全部元组都处理完毕 数据库系统原理厦门大学计算机科学系林子雨2017版嵌套循环连接基于元组的嵌套循环连接Result=;/*初始化结果集合*/ForeachtuplesinSForeachtuplerinRIfr.B=s.Bthen/*元组r和元组s满足连接条件*/Joinrandsastuplet;OutputtintoResult;/*输出连接结果元组*/EndifEndforEndforReturnResult数据库系统原理厦门大学计算机科学系林子雨2017版嵌套循环连接上面基于元组的嵌套循环连接算法中，对于循环外层的
48、关系，通常称为外关系，而对循环内层的关系称为内关系。在执行嵌套循环连接时，仅对外关系进行1次读取操作，而对内关系则需要进行反复读取操作。如果不进行优化的话，这种基于元组的执行代价很大，以磁盘IO计算最多可能达到Card(R)*Card(S)。因此，通常对这种算法进行修改，以减少嵌套循环连接的磁盘IO代价。一种方法是使用连接属性上的索引，以减少参与连接元组的数量，称为“索引嵌套循环连接”数据库系统原理厦门大学计算机科学系林子雨2017版排序合并法(SORT-MERGE)常用于=连接 首先按连接属性对表1和表2排序 对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中
49、的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续数据库系统原理厦门大学计算机科学系林子雨2017版排序合并法 找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续 重复上述操作，直到表1或表2中的全部元组都处理完毕为止 数据库系统原理厦门大学计算机科学系林子雨2017版连接查询（续）SQL中连接查询的主要类型 广义笛卡尔积 等值连接(含自然连接) 非等值连接查询 自
50、身连接查询 外连接查询 复合条件连接查询数据库系统原理厦门大学计算机科学系林子雨2017版一、广义笛卡尔积 不带连接谓词的连接 很少使用例： SELECT Student.* , SC.* FROM Student, SC数据库系统原理厦门大学计算机科学系林子雨2017版二、等值与非等值连接查询等值连接、自然连接、非等值连接例32 查询每个学生及其选修课程的情况。SELECT Student.*，SC.*FROM Student，SCWHERE Student.Sno = SC.Sno；数据库系统原理厦门大学计算机科学系林子雨2017版等值连接 连接运算符为 = 的连接操作 . = . 任何子
