《第3章 关系数据库标准语言SQL.ppt》由会员分享，可在线阅读，更多相关《第3章 关系数据库标准语言SQL.ppt（255页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、数据库原理数据库原理The Theory of Database System第三章第三章 关系数据库标准语言关系数据库标准语言SQLSQLSQL概述数据定义功能本讲主要内容 1974年由Boyce和Chamberlin提出，1975年1979年IBM公司在System R原型系统上实现。3.1.1 SQL3.1.1 SQL的发展的发展 关系数据库的标准语言，是数据库领域中一个主流语言3.1 SQL概述概述SQL/86：第一个SQL标准。由美国国家标准局（American National Standard Institute，简称ANSI）公布，1987年国际标准化组织（Internatio
2、nal Organization for Standardization，简称ISO）通过。SQLSQL标准标准SQL/89SQL/92（简称SQL 2）SQL/99（简称SQL 3）SQL/4 正在进行之中功 能动 词数据库查询SELECT数据定义CREATE，DROPALTER数据操纵INSERT，UPDATE，DELETE数据控制GRANT，REVOKE(4)语言简洁，易学易用 3.1.2 SQL3.1.2 SQL的特点的特点(3)高度非过程化(1)数据描述、操纵、控制等功能一体化(2)两种使用方式，统一的语法结构 3.1 SQL概述概述外模式外模式模模 式式内模式内模式3.1.3 SQ
3、L3.1.3 SQL体系结构体系结构基本表4存储文件2 SQL视图1视图2基本表1存储文件1基本表2基本表3 用 户基本表（Base Table）。简称基表，是数据库中实际存在的关系。视图是从一个或几个基表导出的表，它本身不实际存储在数据库中，只存放对视图的定义信息（没有对应的数据）。因此，视图是一个虚表或虚关系，而基表是一种实关系存储文件。每个基表对应一个存储文件，一个基表还可以带一个或几个索引，存储文件和索引一起构成了关系数据库的内模式。3.2 SQL的定义功能的定义功能 3.2.1 基本表的定义基本表的定义CREATE TABLE （，）；）；例1建立一个“学生”表S，它由学号Sno、姓
4、名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATETABLES(SnoCHAR(5)NOTNULLUNIQUE，SnameCHAR(20)UNIQUE，SsexCHAR(1)，SageINT，SdeptCHAR(15)；例2建立一个“课程”表C，它由课程号Cno、课程名Cname、学分Credit、先修课程Pro四个属性组成。其中课程号不能为空，值是唯一的。CREATETABLEC(CnoCHAR(4)NOTNULLUNIQUE,CnameCHAR(15),CreditINT,ProCHAR(4);定义基本表（
5、续）定义基本表（续）n数据类型n1)定长和变长字符串CHAR(n)VARCHAR(n)n2)定长和变长二进制串 BIT(n)BITVARING(n)n3)整型数 INT SMALLINTn4)浮点数 FLOAT DOUBLE PRECISIONn5)日期型 DATE n6)时间型 TIME n7)时标 TIMESTAMP 定义基本表（续）定义基本表（续）n常用完整性约束n主码约束：PRIMARY KEYn唯一性约束：UNIQUEn非空值约束：NOT NULLn参照完整性约束PRIMARY KEY与 UNIQUE的区别？为学生表和课程表添加主码：为学生表和课程表添加主码：CREATETABLES
6、(SnoCHAR(5)PrimaryKey，SnameCHAR(20)，SsexCHAR(1)，SageINT，SdeptCHAR(15)；CREATETABLEC(CnoCHAR(4)PrimaryKey,CnameCHAR(15),CreditINT,ProCHAR(4);例3 建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。CREATETABLESC(SnoCHAR(5),CnoCHAR(3),GradeINT,Primarykey(Sno,Cno);主关键字的定义主关键字的定义n1）在列出关系模式的属性时,在属性及其类型后
7、加上保留字PRIMARY KEY；n2）在列出关系模式的所有属性后，再附加一个声明：PRIMARY KEY（，）n说明：如果关键字由多个属性构成，则必须使用第二种方法。外部关键字的定义外部关键字的定义1）如果外部关键字只有一个属性，可以在它的属性名和类型后面直接用“REFERENCES”说明它参照了某个表的某些属性，其格式为：REFERENCES()2）在CREATE TABLE语句的属性列表后面增加一个或几个外部关键字说明，其格式为：FOREIGN KEY()REFERENCES()19为学生选课表建立外码：为学生选课表建立外码：CREATE TABLE SC(Sno CHAR(5)REFE
8、RENCES S(Sno),Cno CHAR(3)REFERENCES C(Cno),Grade INT,PRIMARY KEY(Sno,Cno);20CREATE TABLE SC (SNO CHAR(8),CNO CHAR(4),GRADE SMALLINT,PRIMARY KEY(SNO,CNO),FOREIGN KEY(SNO)REFERENCES S(SNO),FOREIGN KEY(CNO)REFERENCES C(CNO);3.2.2 基本表的修改和删除基本表的修改和删除1、修改基本表ALTER TABLE ADD 完整性约束 DROP|ALTER ；n：要修改的基本表nADD子
9、句：增加新列和新的完整性约束条件nDROP子句：删除指定的列或完整性约束条件nALTER子句：用于修改列名和数据类型例4向学生表增加“入学时间”列，其数据类型为日期型。ALTER TABLE S ADD Scome DATE；n不论基本表中原来是否已有数据，新增加的列一律为空值。语句格式（续）语句格式（续）n删除属性列例：删除学生表中“入学时间”属性列。ALTER TABLE S Drop Scome；注注意意：若若一一个个属属性性被被说说明明为为NOT NOT NULLNULL，则则不不允允许修改或删除。许修改或删除。例5 将年龄的数据类型改为半字长整数。ALTER TABLE S ALTE
10、R Sage SMALLINT；n注：修改原有的列定义有可能会破坏已有数据例6 删除学生姓名必须取唯一值的约束。ALTER TABLE S DROP UNIQUE(Sname)；2、删除基本表 DROP TABLE;n删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述 基本表删除数据、表上的索引都删除；表上的视图往往仍然保留，但无法引用例7 删除学生表 DROP TABLE S;关于缺省值关于缺省值(不适用于不适用于ACCESS)ACCESS)n可以在定义属性时增加保留字DEFAULT和一个合适的值。n例如：性别 CHAR（1）DEFAULT 男；年龄 SMALLINT DEFAU
11、LT 1；n修改默认值 ALTER TABLE S ADD CONSTRAINT dd DEFAULT 男 FOR sex;在在SQL SERVER2005 中中CREATE TABLE S(SNO CHAR(8)PRIMARY KEY,SNAME CHAR(8),SEX CHAR(2)DEFAULT 男 CHECK(SEX=男 OR SEX=女),AGE TINYINT DEFAULT 20,DEPT CHAR(30)DEFAULT 计算机学院);3.2.3 索引的建立与删除索引的建立与删除n索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。n索引包含由表或视图中的一列或多
12、列生成的键。这些键存储在一个结构（B 树）中，使数据库可以快速有效地查找与键值关联的行。n在ACCESS中有重复索引与非重复索引Sql Server索引的分类索引的分类n聚集索引n聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。每个表只能有每个表只能有一一个个聚集索引聚集索引。n只有当表包含聚集索引时，表中的数据行才按排序顺序存储。n如果表具有聚集索引，则该表称为聚集聚集表。如果表没有聚集索引，则其数据行存储在一个称为堆堆的无序结构中。Sql Server索引的分类索引的分类n非聚集索引n非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值
13、的数据行的指针。n从非聚集索引中的索引行指向数据行的指针称为行定位器行定位器。Sql Server索引的分类索引的分类n聚集索引和非聚集索引都可以是唯一的。n索引也可以不是唯一的，即多行可以共享同一键值。n在创建 PRIMARY KEY 约束时，如果不存在该表的聚集索引且未指定唯一非聚集索引，则将自动对一列或多列创建唯一聚集索引。主键列不允许空值。n在创建 UNIQUE 约束时，默认情况下将创建唯一非聚集索引，以便强制 UNIQUE 约束。如果不存在该表的聚集索引，则可以指定唯一聚集索引。一、建立索引一、建立索引 n语句格式CREATE UNIQUE CLUSTER INDEX ON(,)；n
14、用指定要建索引的基本表名字n索引可以建立在该表的一列或多列上，各列名之间用逗号分隔n用指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCnUNIQUE表明此索引的每一个索引值只对应唯一的数据记录nCLUSTER表示要建立的索引是聚集索引例8为学生-课程数据库中的S，C，SC三个表建立索引。其中S表按学号升序建唯一索引，C表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATE UNIQUE INDEX Stusno ON S(Sno)；CREATE UNIQUE INDEX Coucno ON C(Cno)；CREATE UNIQUE INDEX SCno
15、 ON SC(Sno ASC，Cno DESC)；建立索引建立索引（续）（续）n唯一值索引n对于已含重复值的属性列不能建UNIQUE索引n对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束。建立索引建立索引（续）（续）n聚簇索引n建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致例：CREATE CLUSTER INDEX Stusname ON S(Sname)；在S表的Sname（姓名）列上建立一个聚簇索引，而且S表中的记录将按照Sname值的升序存
16、放。建立索引建立索引（续）（续）n在一个基本表上最多只能建立一个聚簇索引n聚簇索引的用途：对于某些类型的查询，可以提高查询效率n聚簇索引的适用范围n 很少对基表进行增删操作n 很少对其中的变长列进行修改操作 二、删除索引二、删除索引 DROP INDEX；n删除索引时，系统会从数据字典中删去有关该索引的描述。例9 删除S表的Stusname索引。DROP INDEX Stusname；使用索引的技巧使用索引的技巧n小表不需要索引n数据列中有较多不相同数据时可使用索引n查询要返回的数据很少时可用索引n需要经常更新数据时不宜用索引n只有主索引或聚集索引才会引起数据表中数据的排序数据操纵功能数据操纵
17、功能数据查询数据查询本讲主要内容3.3 查查 询询 3.3.1 概述3.3.2 单表查询3.3.3 连接查询3.3.4 集合查询3.3.5 嵌套查询3.3.6 小结 3.3.1 概述概述 n语句格式SELECT ALL|DISTINCT ，FROM,WHERE GROUP BY HAVING ORDER BY ASC|DESC ；示例数据库示例数据库 学生-课程数据库n学 生 表：S(Sno，Sname，Ssex，Sage，Sdept)n课程表：C(Cno，Cname，Cpno，Ccredit)n 学生选课表：SC(Sno，Cno，Grade)3.3 查询查询 3.3.1 概述3.3.2 单表
18、查询3.3.3 连接查询3.3.4 集合查询3.3.5 嵌套查询3.3.6 小结 3.3.2 单表查询单表查询 查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列（投影）二、选择表中的若干元组（选择）三、对查询结果排序四、使用集函数五、对查询结果分组 查询指定列查询指定列例1 查询全体学生的学号与姓名。SELECT Sno，SnameFROM S；例2 查询全体学生的姓名、学号、所在系。SELECT Sname，Sno，SdeptFROM S；查询全部列查询全部列例3 查询全体学生的详细记录。SELECT Sno,Sname,Ssex,Sage,Sdept FROM S；或 SELE
19、CT *FROM S；3.查询经过计算的值查询经过计算的值 SELECT子句的子句的为表达式为表达式n算术表达式算术表达式n字符串常量字符串常量n函数函数n列别名等列别名等 3.查询经过计算的值查询经过计算的值例4 查询全体学生的姓名及其出生年份。SELECT Sname，year(now()-SageFROM S；输出结果：Sname Expr1001 -李勇 1976 刘晨 1977 王名 1978 张立 1978 3.查询经过计算的值查询经过计算的值使用列使用列别名别名改变查询结果的列标题：改变查询结果的列标题：SELECT Sname,2000-Sage as BirthYearFRO
20、M S；输出结果：Sname BirthYear -李勇 1976 刘晨 1977 王名 1978 张立 1978 二、选择表中的若干元组二、选择表中的若干元组 n消除取值重复的行n查询满足条件的元组 说明说明nSQL查询语句的结果也是一个关系。n关系代数中基于关系是一个集合这样的数学概念，因此，重复的元组不会在关系中出现。但在实践当中，要删除查询结果中的重复元组是相当费时的！所以在商用的数据库产品中，允许在关系和SQL表达式的结果中出现重复元组。1.消除取值重复的行消除取值重复的行n在SELECT子句中使用DISTINCT短语假设SC表中有下列数据 Sno Cno Grade -95001
21、1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80ALL 与与 DISTINCT 例5 查询选修了课程的学生学号。(1)SELECT Sno FROM SC;或 SELECT ALL Sno FROM SC;(默认 ALL)结果：Sno-9500195001950019500295002(2)SELECT DISTINCT Sno FROM SC;结果：Sno -95001 95002 n注意 DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法SELECT DISTINCT Cno，DISTINCT GradeFROM
22、SC;正确的写法 SELECT DISTINCT Cno，Grade FROM SC;2.查询满足条件的元组查询满足条件的元组n带有where子句的查询常用的查询条件常用的查询条件查询条件查询条件 谓谓 词词比较=,=,=算术运算+-*/确定范围Between And,Not Between And确定集合IN ,NOT IN字符匹配Like,Not Like空值IS NULL,IS NOT NULL多重条件AND,OR(1)比较大小比较大小在WHERE子句的中使用比较运算符n=，=，=，!=或，!，!，n逻辑运算符NOT +比较运算符例6查询所有年龄在20岁以下的学生姓名及其年龄。SELEC
23、TSname，SageFROMSWHERESage=20；(2)确定范围确定范围n使用谓词：BETWEEN AND NOT BETWEEN AND 例7 查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECT Sname，Sdept，Sage FROM S WHERE Sage BETWEEN 20 AND 23；例8 查询年龄不在2023岁之间的学生姓名、系别和年龄。SELECT Sname，Sdept，SageFROM SWHERE Sage NOT BETWEEN 20 AND 23；(3)确定集合确定集合n使用谓词：IN,NOT IN ：用逗号分隔的一组取
24、值例9 查询信息系（IS）、数学系（MA）和计 算机科学系（CS）学生的姓名和性别。SELECT Sname，SsexFROM SWHERE Sdept IN(IS，MA，CS);(3)确定集合确定集合例10 查询既不是信息系、数学系，也不是计算 机科学系的学生的姓名和性别。SELECT Sname，SsexFROM S WHERE Sdept NOT IN(IS，MA，CS);(4)字符串匹配字符串匹配n NOT LIKE ESCAPE ：指定匹配模板 匹配模板：固定字符串或含通配符的字符串 当匹配模板为固定字符串时，可以用=运算符取代 LIKE 谓词 用!=或 运算符取代 NOT LIKE
25、 谓词通配符通配符u%(百分号)代表任意长度（长度可以为0）的字符串n例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串。u_(下横线)代表任意单个字符n例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串。ESCAPE 短语短语n当用户要查询的字符串本身就含有%或 _ 时，要使用ESCAPE 短语对通配符进行转义。如：like_%escape 在access中查找通配符用1)匹配模板为固定字符串 例11 查询学号为95001的学生的详细情况。SELECT*FROM S WHERE Sno LIKE 95001；
26、等价于：SELECT *FROM S WHERE Sno=95001；2)匹配模板为含通配符的字符串例12 查询所有姓刘学生的姓名、学号和性别。SELECT Sname，Sno，Ssex FROM S WHERE Sname LIKE 刘%；匹配模板为含通配符的字符串（续）例13 查询姓欧阳且全名为三个汉字的学生的姓名。SELECT Sname FROM S WHERE Sname LIKE 欧阳_ _；匹配模板为含通配符的字符串（续）例14 查询名字中第2个字为阳字的学生的姓名和学号。SELECT Sname，Sno FROM S WHERE Sname LIKE _ _阳%；匹配模板为含通
27、配符的字符串（续）例15 查询所有不姓刘的学生姓名。SELECT Sname，Sno，Ssex FROM S WHERE Sname NOT LIKE 刘%；3)使用转义字符将通配符转义为普通字符 例16 查询DB_Design课程的课程号和学分。SELECT Cno，Ccredit FROM C WHERE Cname LIKE DB_Design ESCAPE;使用换码字符将通配符转义为普通字符(续)例17 查询以DB_开头，且倒数第3个字符为 i的课程的详细情况。SELECT *FROM C WHERE Cname LIKE DB_%i_ _ ESCAPE ；(5)涉及空值的查询涉及空值
28、的查询n 使用谓词 IS NULL 或 IS NOT NULLn“IS NULL”不能用“=NULL”代替例18 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECT Sno，Cno FROM SC WHERE Grade IS NULL；例19 查所有有成绩的学生学号和课程号。SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL；(6)多重条件查询多重条件查询用逻辑运算符AND和 OR来联结多个查询条件n AND的优先级高于ORn 可以用括号改变优先级可用来实现多种其他谓词n NOT IN
29、n NOT BETWEEN AND 例20 查询计算机系年龄在20岁以下的学生姓名。SELECT Sname FROM S WHERE Sdept=CS AND Sage=20ANDSage=23；三、对查询结果排序三、对查询结果排序 使用ORDER BY子句n 可以按一个或多个属性列排序n 升序：ASC；降序：DESC；缺省值为升序当排序列含空值时nASC：排序列为空值的元组最后显示nDESC：排序列为空值的元组最先显示 对查询结果排序（续）对查询结果排序（续）例23 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECT Sno，Grade FROM SC WHERE
30、 Cno=3 ORDER BY Grade DESC；对查询结果排序（续）对查询结果排序（续）例24 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。SELECT *FROM S ORDER BY Sdept，Sage DESC；四、使用集函数四、使用集函数 5类主要集函数n计数COUNT（DISTINCT|ALL*）COUNT（DISTINCT|ALL）n计算总和SUM（DISTINCT|ALL）n 计算平均值AVG（DISTINCT|ALL）使用集函数（续）使用集函数（续）n求最大值MAX（DISTINCT|ALL n求最小值MIN（DISTINCT|ALL
31、 DISTINCT短语：在计算时要取消指定列中的重复值ALL短语：不取消重复值ALL为缺省值例25 查询学生总人数。SELECT COUNT(*)FROM S；例26 查询选修了课程的学生人数。SELECT COUNT(DISTINCT Sno)FROM SC；注：用DISTINCT以避免重复计算学生人数例27 计算1号课程的学生平均成绩。SELECT AVG(Grade)FROM SC WHERE Cno=1；例28 查询选修1号课程的学生最高分数。SELECT MAX(Grade)FROM SC WHER Cno=1；五、对查询结果分组五、对查询结果分组 使用GROUP BY子句分组 细化
32、集函数的作用对象n 未对查询结果分组，集函数将作用于整个查询结果n 对查询结果分组后，集函数将分别作用于每个组 例29 求各个课程号及相应的选课人数。SELECT Cno，COUNT(Sno)FROM SC GROUP BY Cno；结果：Cno COUNT(Sno)1 22 2 34 3 44 4 33 5 48对查询结果分组（续）对查询结果分组（续）nGROUP BY子句的作用对象是查询的中间结果表n分组方法：按指定的一列或多列值分组，值相等的为一组n使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和集函数使用使用HAVING短语筛选最终输出结果短语筛选最终输出结果
33、例30 查询选修了3门以上课程的学生学号。SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)3；例31 查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数 SELECT Sno,COUNT(*)FROM SC WHERE Grade=90 GROUP BY Sno HAVING COUNT(*)=3;使用使用HAVING短语筛选最终输出结果短语筛选最终输出结果n只有满足HAVING短语指定条件的组才输出nHAVING短语与WHERE子句的区别：作用对象不同nWHERE子句作用于基表或视图，从中选择满足条件的元组。nHAVING短语作用
34、于组，从中选择满足条件的组。3.3 查查 询询 n3.3.1 概述n3.3.2 单表查询n3.3.3 连接查询n3.3.4 集合查询n3.3.5 嵌套查询n3.3.6 小结 3.3.3 连接查询连接查询 同时涉及多个表的查询称为连接查询用来连接两个表的条件称为连接条件或连接谓词 一般格式：n.比较运算符：=、=、=、!=连接查询连接查询（续）（续）SQL中连接查询的主要类型n 广义笛卡尔积n 等值连接(含自然连接)n 非等值连接查询n 自身连接查询n 外连接查询n 复合条件连接查询一、广义笛卡尔积一、广义笛卡尔积 n不带连接谓词的连接n 很少使用例：SELECT S.*,SC.*FROM S,
35、SC；二、等值与非等值连接查询二、等值与非等值连接查询 等值连接、自然连接、非等值连接例32 查询学生及其选修课程的情况 等价于：SELECTS.*，SC.*FROMS，SCWHERES.Sno=SC.Sno；SELECTS.*，SC.*FROMSINNERJOINSCONS.Sno=SC.Sno;等值连接等值连接n连接运算符为=的连接操作n.=.n任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。假设S表、SC表分别有下列数据：S表 Sno SnameSsexSageSdept95001 李勇李勇 男男 20 CS95002 刘晨刘晨 女女 1
36、9 IS95003 王敏王敏 女女 18 MA95004 张立张立 男男 19 ISSC表SnoCnoGrade9500119295001285950013889500229095002380等值连接等值连接结果表S.Sno Sname Ssex Sage Sdept SC.Sno Cno Grade95001 李勇 男 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 自然连接自然连接n
37、等值连接的一种特殊情况，把目标列中重复的属性列去掉。例33 查询学生及其选修课程的情况（用自然连接完成）。SELECT S.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM S，SC WHERE S.Sno=SC.Sno；非等值连接查询非等值连接查询连接运算符 不是=的连接操作 .比较运算符：、=、=、!=三、自身连接三、自身连接 n一个表与其自己进行连接，称为表的自身连接；n需要给表起别名以示区别；n由于所有属性名都是同名属性，因此必须使用别名前缀。自身连接（续）自身连接（续）例34 查询每一门课的间接先修课（即先修课的先修课）SELECT FIRST.Cno
38、，SECOND.Cpno FROM C as FIRST，C as SECOND WHERE FIRST.Cpno=SECOND.Cno；FIRST表（C表）Cno CnameCpnoCcredit 1数据库数据库 5 4 2数学数学 2 3信息系统信息系统 1 4 4操作系统操作系统 6 3 5数据结构数据结构 7 4 6数据处理数据处理 2 7PASCAL语言语言 6 4SECOND表（C表）Cno CnameCpnoCcredit 1数据库数据库 5 4 2数学数学 2 3信息系统信息系统 1 4 4操作系统操作系统 6 3 5数据结构数据结构 7 4 6数据处理数据处理 2 7PASC
39、AL语言语言 6 4查询结果 1 7 3 5 5 6 cno cpno四、外连接（四、外连接（Outer Join）n外连接与普通连接的区别n普通连接操作只输出满足连接条件的元组n外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出 例35 查询每个学生及其选修课程的情况包括没有选修课程的学生-用外连接操作 SELECT S.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM S,SC WHERE S.Sno=SC.Sno(*)；或者 SELECT S.Sno,Sname,Ssex,Sage,Sdept,Cno，Grade FROM S LEFT(
40、OUTER)JOIN SC ON S.Sno=SC.Sno；结果：结果：S.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 95004 张立 男 19 IS外连接（续）外连接（续）n左外连接nLEFT(OUTER)JOIN ON;n右外连接nRIGHT(OUTER)JOIN ON;n全外连接nFULL(OUTER)JOI
41、N ON;五、复合条件连接五、复合条件连接WHERE子句中含多个连接条件时，称为复合条件连接例36 查询选修2号课程且成绩在90分以上的所有学生的学号、姓名SELECT S.Sno,S.SnameFROM S,SCWHERE S.Sno=SC.Sno AND /*连接谓词*/SC.Cno=2 AND /*其他限定条件*/SC.Grade 90；/*其他限定条件*/多表连接多表连接例37 查询每个学生的学号、姓名、选修的课程名及成绩。SELECT S.Sno，Sname，Cname，Grade FROM S，SC，C WHERE S.Sno=SC.Sno and SC.Cno=C.Cno；结果：
42、S.Sno Sname Cname Grade 95001 李勇 数据库 92 95001 李勇 数学 85 95001 李勇 信息系统 88 95002 刘晨 数学 90 95002 刘晨 信息系统 80 TOP 谓词谓词例：查询成绩前五名的学生姓名，课程名及成绩。例：查询成绩前五名的学生姓名，课程名及成绩。SELECT TOP 5 SNAME,CNAME,GRADEFROM S,SC,CWHERE S.SNO=SC.SNO AND SC.CNO=C.CNOORDER BY GRADE DESC;3.3 查查 询询 n3.3.1 概述n3.3.2 单表查询n3.3.3 连接查询n3.3.4
43、集合查询n3.3.5 嵌套查询n3.3.6 小结 3.3.4 3.3.4 集合查询集合查询标准SQL直接支持的集合操作种类并操作(UNION)一般商用数据库支持的集合操作种类并操作(UNION)交操作(INTERSECT)差操作(EXCEPT)1并操作并操作n形式 UNION n参加UNION操作的各结果表的列数必须相同；对应项的数据类型也必须相同例38 查询计算机科学系的学生及年龄不大于19岁的学生。方法一：(SELECT*FROM S WHERE Sdept=CS)UNION (SELECT*FROM S WHERE Sage=19)；方法二：SELECT *FROM S WHERE Sd
44、ept=CS OR Sage=19；例39 查询选修了课程1或者选修了课程2的学生。方法一：(SELECT Sno FROM SC WHERE Cno=1)UNION (SELECT Sno FROM SC WHERE Cno=2)；方法二：SELECT DISTINCT Sno FROM SC WHERE Cno=1 OR Cno=2；例40 设数据库中有一教师表Teacher(Tno,Tname,.)。查询学校中所有师生的姓名。(SELECT Sname FROM S)UNION (SELECT Tname FROM Teacher);2交操作交操作n标准SQL中没有提供集合交操作，但可用
45、其他方法间接实现。n有些DBMS支持交运算：形式 INTERSECT 例41 查询既选修了课程1又选修了课程2的学生学号。(SELECT SC.SNO FROM SC WHERE SC.CNO=1)INTERSECT (SELECT SC.SNO FROM SC WHERE SC.CNO=2);3差操作差操作n标准SQL中没有提供集合交操作，但可用其他方法间接实现。n有些DBMS支持交运算：形式 EXCEPT 3差操作差操作例42 查询只选修课程1而没有选修课程2的学生学号。(SELECT SC.SNO FROM SC WHERE SC.CNO=1)EXCEPT (SELECT SC.SNO
46、FROM SC WHERE SC.CNO=2);3.3 查查 询询 3.3.1 概述3.3.2 单表查询3.3.3 连接查询3.3.4 集合查询3.3.5 嵌套查询3.3.6 小结 3.3.5 嵌套查询嵌套查询n嵌套查询概述n嵌套查询分类n嵌套查询求解方法n引出子查询的谓词 嵌套查询嵌套查询(续续)n嵌套查询概述n一个SELECT-FROM-WHERE语句称为一个查询块n将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询嵌套查询嵌套查询(续续)例：查询选修了2号课程的学生姓名。SELECTSnameFROMSWHERESnoINSELECTSnoFROM
47、SCWHERECno=2内层查询/子查询外层查询/父查询（）;例41 查询既选修了课程1又选修了课程2的学生学号。(解法二)SELECT Sno FROM SC WHERE Cno=1 AND Sno IN (SELECT Sno FROM SC WHERE Cno=2)；嵌套查询分类嵌套查询分类n不相关子查询子查询的查询条件不依赖于父查询n相关子查询子查询的查询条件依赖于父查询嵌套查询求解方法嵌套查询求解方法n不相关子查询是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。嵌套查询求解方法（续）嵌套查询求解方法（续）n相关子查询n首先取外层查询中
48、表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；n然后再取外层表的下一个元组；n重复这一过程，直至外层表全部检查完为止。引出子查询的谓词引出子查询的谓词n带有IN谓词的子查询n带有比较运算符的子查询n带有ANY或ALL谓词的子查询n带有EXISTS谓词的子查询一、带有一、带有IN谓词的子查询谓词的子查询例44 查询与“刘晨”在同一个系学习的学生。此查询要求可以分步来完成 确定“刘晨”所在系名 SELECT Sdept FROM S WHERE Sname=刘晨；结果为：SdeptIS带有带有IN谓词的子查询（续）谓词的子查询（续）查找
49、所有在IS系学习的学生。SELECT Sno，Sname，Sdept FROM S WHERE Sdept=IS；结果为：SnoSname Sdept95001刘晨IS95004张立IS 构造嵌套查询构造嵌套查询将第一步查询嵌入到第二步查询的条件中 SELECT Sno，Sname，Sdept FROM S WHERE Sdept IN (SELECT Sdept FROM S WHERE Sname=刘晨)；此查询为不相关子查询。DBMS求解该查询时也是分步去做的。带有带有IN谓词的子查询（续）谓词的子查询（续）用自身连接完成本查询要求 SELECT S1.Sno，S1.Sname，S1.S
50、dept FROM S as S1，S as S2 WHERE S1.Sdept=S2.Sdept AND S2.Sname=刘晨；带有带有IN谓词的子查询（续）谓词的子查询（续）父查询和子查询中的表均可以定义别名 SELECT Sno，Sname，Sdept FROM S as S1 WHERE S1.Sdept IN (SELECT Sdept FROM S as S2 WHERE S2.Sname=刘晨)；带有带有IN谓词的子查询（续）谓词的子查询（续）例45查询选修了课程名为“信息系统”的学生学号和姓名(SELECTCnoFROMCWHERECname=信息系统);(SELECTSno