03_关系数据库标准语言SQL

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,数据库,课件,第三章 关系数据库标准语言,SQL,本章重点：,SQL,的特点和内涵及相关概念,SQL,语言的构成,SQL,的应用,3.1 SQL,概述,SQL,的发展,(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 SQL,概述,SQL,定义,结构化查询语言,SQL,（,Structured Query Language,）是一种包含关系代数与逻辑运算的语言，其功能包括查询、操纵、定义和控制四个方面，是一个通用的、功能极强的关系数据库语言。目前已成为关系数据库的标准语言。,SQL,包括：数据查询,(Data Query),、数据操纵,(Data Manipulation),、数据定义,(Data Definition),和数据控制,(Data Control),，分别简称为,DQL,、,DML,、,DDL,、,DCL,。,3.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 SQL,的特点之二、三,高度非过程化,面向集合的操作方式,我想在数据库中,收到输入的命令。,数据库操作,一个二维表,3.1.1 SQL,的特点之四,以同一种语法结构提供两种使用方式,SQL,语言既是自含式语言，又是嵌入式语言。,SQL,语句,高级语言程序,数据库,3.1.1 SQL,的特点之五,语言简洁，易学易用,SQL,语言功能极强，但由于设计巧妙，语言十分简洁，完成数据定义、数据操纵、数据控制的核心功能只用了,9,个动词。如表下所示。而且,SQL,语言语法简单，接近英语口语，因此容易学习，容易使用。,数据查询,Select,数据定义,Create, Drop, Alter,数据操纵,Insert, Update, Delete,数据控制,Grant, Revoke,3.1.2 SQL,语言的基本概念,基本表是本身独立存在的表，在,SQL,中一个关系就对应一个表。一些基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。,存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理文件结构是任意的。,3.1.2 SQL,语言的基本概念,视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。 用户可以用,SQL,语言对视图和基本表进行查询。在用户眼中，视图和基本表都是关系，而存储文件对用户是透明的。,SQL,对关系数据库模式的支持,SQL,语言支持关系数据库三级模式结构。其中外模式对应于视图（,View,）和部分基本表（,Base Table,），模式对应于基本表，内模式对应于存储文件。,SQL,对关系数据库模式的支持,基本表,B1,视图,V1,基本表,B3,基本表,B4,存储文件,S1,存储文件,S2,基本表,B2,视图,V2,SQL,外层,/,外模式,概念层,/,概念模式,内层,/,存储模式,注 意,本课程所讲授的,SQL,语言遵循,SQL 92,标准。,对于不同的数据系统，有不同的,SQL,书写方式和功能，在应用前，请阅读相应的,SQL,手册。,目前,SQL SERVER,等著名数据库都有很强的,SQL,语言扩充（,T-SQL,）。,关系数据库由模式、外模式和内模式组成，即关系数据库的基本对象是表、视图和索引。因此，,SQL,的数据定义功能包括,定义表,定义视图,定义索引,3.2,数据定义,3.2.1,定义、删除和修改基本表,CREATE TABLE ,(, , ,),给出要创建的基本表的名称；,给出列名或字段名；, , , ,1,定义基本表,为列指定数据类型及其数据宽度；,关系数据库支持非常丰富的数据类型，不同的数据库管理系统支持的数据类型基本是一样的，右表列出了常用的数据类型。,数据类型,用于定义列或字段一级的完整性约束，一般包括：,NOT NULL,和,NULL,约束,PRIMARY KEY,约束,UNIQUE,约束,FOREIGN KEY,约束,DEFAULT,定义,CHECK,约束,列级完整性约束,用于定义表一级的完整性约束，一般包括：,PRIMARY KEY,约束（复合属性构成的主码说明）,FOREIGN KEY,约束（外码及参照关系说明）,CHECK,约束（同时涉及到多个属性的域完整性约束）,表级完整性约束,不是,SQL,的标准选项，一般用于与物理存储有关的说明，不同的数据库管理系统定义的方式肯定不同，另外该项参数一般也不是必需的。,其他参数,例题,例,1,建立一个,“,学生,”,表,Student,，它由学号,Sno,、姓名,Sname,、性别,Ssex,、年龄,Sage,、所在系,Sdept,五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。,CREATE TABLE Student,(,Sno CHAR(5) NOT NULL UNIQUE,，,Sname CHAR(20) UNIQUE,，,Ssex CHAR(1),，,Sage INT,，,Sdept CHAR(15),),；,2,修改基本表,2,修改基本表,说明：其中,指定需要修改的基本表，,ADD,子句用于增加新列和新的完整性约束条件，,DROP,子句用于删除指定的完整性约束条件，,MODIFY,子句用于修改原有的列定义。,ALTER TABLE ,ADD |,DROP |,MODIFY ,数据类型,例题,例,2,向,Student,表增加,“,入学时间,”,列，其数据类型为日期型。,ALTER TABLE Student ADD Scome DATE,；,不论基本表中原来是否已有数据，新增加的列一律为空值。,语句格式（续）,删除属性列,直接,/,间接删除,把表中要保留的列及其内容复制到一个新表中,删除原表,再将新表重命名为原表名,直接删除属性列,:(,新,),例,3,：,ALTER TABLE Student Drop Scome,；,属性的默认值,用户不提供某属性的值时，默认值被使用,初始值,TimeStamp,例,4,：,Alter Table student Add ID integer Default AutoIncrement;,DDL,Default Value,3.2.2,建立与删除索引,建立索引是加快查询速度的有效手段,建立索引,DBA,或表的属主（即建立表的人）根据需要建立,有些,DBMS,自动建立以下列上的索引,PRIMARY KEY,UNIQUE,维护索引,DBMS,自动完成,使用索引,DBMS,自动选择是否使用索引以及使用哪些索引,一、建立索引,语句格式,CREATE,UNIQUE CLUSTER,INDEX ON (, ,),；,用,指定要建索引的基本表名字,索引可以建立在该表的一,列,或多列上，各列名之间用逗号分隔,用,指定索引值的排列次序，升序：,ASC,，降序：,DESC,。缺省值：,ASC,UNIQUE,表明此索引的每一个索引值只对应唯一的数据记录,CLUSTER,表示要建立的索引是聚簇索引,例题,例,5,为学生,-,课程数据库中的,Student,，,Course,，,SC,三个表建立索引。其中,Student,表按学号升序建唯一索引，,Course,表按课程号升序建唯一索引，,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),；,建立索引 （续）,唯一值索引,对于已含重复值的属性列不能建,UNIQUE,索引,对某个列建立,UNIQUE,索引后，插入新记录时,DBMS,会自动检查新记录在该列上是否取了重复值。这相当于增加了一个,UNIQUE,约束,建立索引 （续）,聚簇索引,建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致,例,6,：,CREATE CLUSTER INDEX Stusname ON,Student(Sname),；,在,Student,表的,Sname,列上建立一个聚簇索引，而且,Student,表中的记录将按照,Sname,值的升序存放,建立索引 （续）,在一个基本表上最多只能建立一个聚簇索引,聚簇索引的用途：对于某些类型的查询，可以提高查询效率,聚簇索引的适用范围,很少对基表进行增删操作,很少对其中的变长列进行修改操作,二、删除索引,DROP INDEX ,；,删除索引时，系统会从数据字典中删去有关该索引的描述。,例,7,删除,Student,表的,Stusname,索引。,DROP INDEX Stusname,；,3.3,数据操纵,SQL,的数据操纵功能包括：,查询：,SELECT,更新,(,插入,),：,INSERT,更新,(,删除,),：,DELETE,更新,(,修改,),：,UPDATE,3.3.1 SQL,查询语句,语句格式,SELECT ALL|DISTINCT *|,FROM ,WHERE ,GROUP BY , HAVING ,ORDER BY ASC|DESC , ASC|DESC,COMPUTE ,示例数据库,学生,-,课程数据库,学生表：,Student(Sno,，,Sname,，,Ssex,，,Sage,，,Sdept),课程表：,Course(Cno,，,Cname,，,Cpno,，,Ccredit),学生选课表：,SC(Sno,，,Cno,，,Grade),例,8,建立,“,学生,”,表,Student,，学号是主码，姓名取值唯一。,CREATE TABLE Student,( Sno CHAR(9),PRIMARY KEY,，,/*,列级完整性约束条件*,/,Sname CHAR(20),UNIQUE,，,/* Sname,取唯一值*,/,Ssex CHAR(2),，,Sage SMALLINT,，,Sdept CHAR(20) ),；,主码,例,9,建立一个,“,课程,”,表,Course,CREATE TABLE Course,( Cno CHAR(4) PRIMARY KEY,，,Cname CHAR(40),，,Cpno CHAR(4),，,Ccredit SMALLINT,，,FOREIGN KEY (Cpno) REFERENCES Course(Cno),);,先修课,Cpno,是外码,被参照表是,Course,被参照列是,Cno,例,10,建立一个,“,学生选课,”,表,SC,CREATE TABLE SC,(Sno CHAR(9),，,Cno CHAR(4),，,Grade SMALLINT,，,PRIMARY KEY (Sno,，,Cno),，,/*,主码由两个属性构成，必须作为表级完整性进行定义*,/,FOREIGN KEY (Sno) REFERENCES Student(Sno),，,/*,表级完整性约束条件，,Sno,是外码，被参照表是,Student */,FOREIGN KEY (Cno) REFERENCES Course(Cno),/*,表级完整性约束条件，,Cno,是外码，被参照表是,Course*/,);,单表查询,查询仅涉及一个表，是一种最简单的查询操作,一、选择表中的若干列,二、选择表中的若干元组,三、对查询结果排序,四、使用集函数,五、对查询结果分组,例,11,从职工关系中检索所有工资值,SELECT,工资,FROM,职工,结果是：,1220,1210,1250,1230,1250,SELECT DISTINCT,工资,FROM,职工,结果是：,1220,1210,1250,1230,例,12,使用列,别名,改变查询结果的列标题,SELECT Sname,NAME,，,Year of Birth:,BIRTH,，,2008-Sage,BIRTHDAY,，,ISLOWER(Sdept),DEPARTMENT,FROM Student,；,输出结果：,NAME BIRTH BIRTHDAY DEPARTMENT,- - - -,李勇,Year of Birth: 1976 cs,刘晨,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,ALL,与,DISTINCT,例,13,查询选修了课程的学生学号。,(1) SELECT Sno,FROM SC;,或,(,默认,ALL),SELECT ALL Sno,FROM SC;,结果：,Sno,-,95001,95001,95001,95002,95002,例题（续）,(2) SELECT DISTINCT Sno,FROM SC;,结果：,Sno,-,95001,95002,2.,查询满足条件的元组,WHERE,子句常用的查询条件,(1),比较大小,在,WHERE,子句的,中使用比较运算符,=,，,，,=,，,，,!,，,逻辑运算符,NOT +,比较运算符,例,14,查询所有年龄在,20,岁以下的学生姓名及其年龄。,SELECT Sname,，,Sage,FROM Student,WHERE Sage = 20,；,(2),确定范围,使用谓词,BETWEEN,AND,NOT BETWEEN,AND,例,15,查询年龄在,2023,岁（包括,20,岁和,23,岁）之间的学生的姓名、系别和年龄。,SELECT Sname,，,Sdept,，,Sage,FROM Student,WHERE Sage BETWEEN 20 AND 23,；,例题（续）,例,16,查询年龄不在,2023,岁之间的学生姓名、系别和年龄。,SELECT Sname,，,Sdept,，,Sage,FROM Student,WHERE Sage NOT BETWEEN 20 AND 23,；,(3),确定集合,使用谓词,IN , NOT IN ,：用逗号分隔的一组取值,例,17,查询信息系（,IS,）、数学系（,MA,）和计,算机科学系（,CS,）学生的姓名和性别。,SELECT Sname,，,Ssex,FROM Student,WHERE Sdept IN ( IS,，,MA,，,CS );,(3),确定集合,例,18,查询既不是信息系、数学系，也不是计算,机科学系的学生的姓名和性别。,SELECT Sname,，,Ssex,FROM Student,WHERE Sdept NOT IN ( IS,，,MA,，,CS );,(4),字符串匹配,NOT LIKE ESCAPE ,：指定匹配模板,匹配模板：固定字符串或含通配符的字符串,当匹配模板为固定字符串时，,可以用,=,运算符取代,LIKE,谓词,用,!=,或,运算符取代,NOT LIKE,谓词,通配符,% (,百分号,),代表任意长度（长度可以为,0,）的字符串,例,19,：,a%b,表示以,a,开头，以,b,结尾的任意长度的字符串。如,acb,，,addgb,，,ab,等都满足该匹配串,_ (,下横线,),代表任意单个字符,例,20,：,a_b,表示以,a,开头，以,b,结尾的长度为,3,的任意字符串。如,acb,，,afb,等都满足该匹配串,ESCAPE,短语：,当用户要查询的字符串本身就含有,%,或,_,时，要使用,ESCAPE ,短语对通配符进行转义。,例题,1),匹配模板为固定字符串,例,21,查询学号为,95001,的学生的详细情况。,SELECT *,FROM Student,WHERE Sno LIKE 95001,；,等价于：,SELECT *,FROM Student,WHERE Sno = 95001,；,例题（续）,2),匹配模板为含通配符的字符串,例,22,查询所有姓刘学生的姓名、学号和性别。,SELECT Sname,，,Sno,，,Ssex,FROM Student,WHERE Sname LIKE ,刘,%,；,例题（续）,匹配模板为含通配符的字符串（续）,例,23,查询姓,欧阳,且全名为三个汉字的学生的姓名。,SELECT Sname,FROM Student,WHERE Sname LIKE ,欧阳,_,；,例题（续）,匹配模板为含通配符的字符串（续）,例,24,查询名字中第,2,个字为,阳,字的学生的姓名和学号。,SELECT Sname,，,Sno,FROM Student,WHERE Sname LIKE _,阳,%,；,例题（续）,匹配模板为含通配符的字符串（续）,例,25,查询所有不姓刘的学生姓名。,SELECT Sname,，,Sno,，,Ssex,FROM Student,WHERE Sname NOT LIKE ,刘,%,；,例题（续）,3),使用换码字符将通配符转义为普通字符,例,26,查询,DB_Design,课程的课程号和学分。,SELECT Cno,，,Ccredit,FROM Course,WHERE Cname LIKE DB,_Design,ESCAPE ,例题（续）,使用换码字符将通配符转义为普通字符,(,续,),例,27,查询以,“DB_”,开头，且倒数第,3,个字符为,i,的课程的详细情况。,SELECT *,FROM Course,WHERE Cname LIKE DB,_%i_ _,ESCAPE ,；,(5),涉及空值的查询,使用谓词,IS NULL,或,IS NOT NULL,“IS NULL”,不能用 “,= NULL”,代替,例,28,某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。,SELECT Sno,，,Cno,FROM SC,WHERE Grade IS NULL,；,例题,(,续,),例,29,查所有有成绩的学生学号和课程号。,SELECT Sno,，,Cno,FROM SC,WHERE Grade IS NOT NULL,；,(6),多重条件查询,用逻辑运算符,AND,和,OR,来联结多个查询条件,AND,的优先级高于,OR,可用括号改变优先级,可用来实现多种其他谓词,NOT IN,NOT BETWEEN AND ,例题,例,30,查询计算机系年龄在,20,岁以下的学生姓名。,SELECT Sname,FROM Student,WHERE Sdept= CS AND Sage=20 AND Sage=23,；,三、对查询结果排序,使用,ORDER BY,子句,可以按一个或多个属性列排序,升序：,ASC,；降序：,DESC,；缺省值为升序,当排序列含空值时,ASC,：排序列为空值的元组最后显示,DESC,：排序列为空值的元组最先显示,对查询结果排序（续）,例,33,查询选修了,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,对查询结果排序（续）,例,34,查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。,SELECT *,FROM Student,ORDER BY Sdept,，,Sage DESC,；,四、使用集函数,5,类主要集函数,计数,COUNT,（,DISTINCT|,ALL, *,）,COUNT,（,DISTINCT|,ALL, ,）,计算总和,SUM,（,DISTINCT|,ALL, ,）,计算平均值,AVG,（,DISTINCT|,ALL, ,）,使用集函数（续）,求最大值,MAX,（,DISTINCT|,ALL, ,）,求最小值,MIN,（,DISTINCT|,ALL, ,）,DISTINCT,短语：在计算时要取消指定列中的重复值,ALL,短语：不取消重复值,ALL,为缺省值,使用集函数 （续）,例,35,查询学生总人数。,SELECT COUNT(*),FROM Student,；,例,36,查询选修了课程的学生人数。,SELECT COUNT(DISTINCT Sno),FROM SC,；,注：用,DISTINCT,以避免重复计算学生人数,使用集函数 （续）,例,37,计算,1,号课程的学生平均成绩。,SELECT AVG(Grade),FROM SC,WHERE Cno= 1 ,；,例,38,查询选修,1,号课程的学生最高分数。,SELECT MAX(Grade),FROM SC,WHER Cno= 1 ,；,五、对查询结果分组,使用,GROUP BY,子句分组,细化集函数的作用对象,未对查询结果分组，集函数将作用于整个查询结果,对查询结果分组后，集函数将分别作用于每个组,使用,GROUP BY,子句分组,例,39,求各个课程号及相应的选课人数。,SELECT Cno,，,COUNT(Sno),FROM SC,GROUP BY Cno,；,结果,Cno COUNT(Sno),1 22,2 34,3 44,4 33,5 48,对查询结果分组 （续）,GROUP BY,子句的作用对象是查询的中间结果表,分组方法：按指定的一列或多列值分组，值相等的为一组,使用,GROUP BY,子句后，,SELECT,子句的列名列表中只能出现分组属性和集函数,使用,HAVING,短语筛选最终输出结果,例,40,查询选修了,3,门以上课程的学生学号。,SELECT Sno,FROM SC,GROUP BY Sno,HAVING COUNT(*) 3,；,例题,例,41,查询有,3,门以上课程是,90,分以上的,学生的学号及（,90,分以上的）课程数,SELECT Sno, COUNT(*),FROM SC,WHERE Grade=90,GROUP BY Sno,HAVING COUNT(*)=3;,使用,HAVING,短语筛选最终输出结果,只有满足,HAVING,短语指定条件的组才输出,HAVING,短语与,WHERE,子句的区别：作用对象不同,WHERE,子句作用于基表或视图，从中选择满足条件的元组。,HAVING,短语作用于组，从中选择满足条件的组。,连接查询,连接查询：同时涉及多个表的查询,连接条件或连接谓词：用来连接两个表的条件,一般格式：,.,.,.,BETWEEN,.,AND,.,连接字段：连接谓词中的列名称,连接条件中的各连接字段类型必须是可比的，但名字不必是相同的,连接查询（续）,一、等值与非等值连接查询,二、自身连接,三、外连接,四、复合条件连接,一、等值与非等值连接查询,等值连接：连接运算符为,=,例,42,查询每个学生及其选修课程的情况,SELECT Student.*,，,SC.*,FROM Student,，,SC,WHERE Student.Sno = SC.Sno,；,等值与非等值连接查询（续）,Student.Sno,Sname,Ssex,Sage,Sdept,SC.Sno,Cno,Grade,200215121,李勇,男,20,CS,200215121,1,92,200215121,李勇,男,20,CS,200215121,2,85,200215121,李勇,男,20,CS,200215121,3,88,200215122,刘晨,女,19,CS,200215122,2,90,200215122,刘晨,女,19,CS,200215122,3,80,查询结果：,等值与非等值连接查询（续）,自然连接：,例,43,用自然连接完成。,SELECT,Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade,FROM Student,，,SC,WHERE Student.Sno = SC.Sno,；,连接查询（续）,一、等值与非等值连接查询,二、自身连接,三、外连接,四、复合条件连接,二、自身连接,自身连接：一个表与其自己进行连接,需要给表起别名以示区别,由于所有属性名都是同名属性，因此必须使用别名前缀,例,44,查询每一门课的间接先修课（即先修课的先修课）,SELECT FIRST.Cno,，,SECOND.Cpno,FROM Course,FIRST,，,Course,SECOND,WHERE FIRST.Cpno = SECOND.Cno,；,自身连接（续）,FIRST,表（,Course,表）,Cno,Cname,Cpno,Ccredit,1,数据库,5,4,2,数学,2,3,信息系统,1,4,4,操作系统,6,3,5,数据结构,7,4,6,数据处理,2,7,PASCAL,语言,6,4,自身连接（续）,Cno,Cname,Cpno,Ccredit,1,数据库,5,4,2,数学,2,3,信息系统,1,4,4,操作系统,6,3,5,数据结构,7,4,6,数据处理,2,7,PASCAL,语言,6,4,SECOND,表（,Course,表）,自身连接（续）,查询结果：,Cno,Pcno,1,7,3,5,5,6,连接查询（续）,一、等值与非等值连接查询,二、自身连接,三、外连接,四、复合条件连接,三、外连接,外连接与普通连接的区别,普通连接操作只输出满足连接条件的元组,外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出,例,45,改写上例,SELECT Student.Sno,，,Sname,，,Ssex,，,Sage,，,Sdept,，,Cno,，,Grade,FROM Student LEFT OUT JOIN SC ON (Student.Sno=SC.Sno),；,外连接（续,）,执行结果：,Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade,200215121,李勇,男,20,CS,1,92,200215121,李勇,男,20,CS,2,85,200215121,李勇,男,20,CS,3,88,200215122,刘晨,女,19,CS,2,90,200215122,刘晨,女,19,CS,3,80,200215123,王敏,女,18,MA,NULL,NULL,200215125,张立,男,19,IS,NULL,NULL,外连接（续）,左外连接,列出左边关系（如本例,Student,）中所有的元组,右外连接,列出右边关系中所有的元组,连接查询（续）,一、等值与非等值连接查询,二、自身连接,三、外连接,四、复合条件连接,四、复合条件连接,复合条件连接：,WHERE,子句中含多个连接条件,例,46,查询选修,2,号课程且成绩在,90,分以上的所有学生,SELECT Student.Sno, Sname,FROM Student, SC,WHERE Student.Sno = SC.Sno AND,/*,连接谓词*,/,SC.Cno=,2,AND SC.Grade 90,；,/*,其他限定条件 *,/,复合条件连接（续）,例,47,查询每个学生的学号、姓名、选修的课程名及成绩,SELECT Student.Sno,，,Sname,，,Cname,，,Grade,FROM Student,，,SC,，,Course,/*,多表连接*,/,WHERE Student.Sno = SC.Sno,and SC.Cno = Course.Cno,；,嵌套查询,(,续,),嵌套查询概述,一个,SELECT-FROM-WHERE,语句称为一个,查询块,将一个查询块嵌套在另一个查询块的,WHERE,子句或,HAVING,短语的条件中的查询称为,嵌套查询,嵌套查询,(,续,),SELECT Sname /*,外层查询,/,父查询*,/,FROM Student,WHERE Sno IN,（,SELECT Sno /*,内层查询,/,子查询*,/,FROM SC,WHERE Cno= 2 ,）；,嵌套查询,(,续,),子查询的限制,不能使用,ORDER BY,子句,层层嵌套方式反映了,SQL,语言的结构化,有些嵌套查询可以用连接运算替代,嵌套查询求解方法,不相关子查询：,子查询的查询条件不依赖于父查询,由里向外 逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。,嵌套查询求解方法（续）,相关子查询：子查询的查询条件依赖于父查询,首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若,WHERE,子句返回值为真，则取此元组放入结果表,然后再取外层表的下一个元组,重复这一过程，直至外层表全部检查完为止,嵌套查询,一、带有,IN,谓词的子查询,二、 带有比较运算符的子查询,三、 带有,ANY,（,SOME,）或,ALL,谓词的子查询,四、 带有,EXISTS,谓词的子查询,一、带有,IN,谓词的子查询,例,49,查询与,“,刘晨,”,在同一个系学习的学生。,此查询要求可以分步来完成,确定,“,刘晨,”,所在系名,SELECT Sdept,FROM Student,WHERE Sname= ,刘晨,；,结果为：,CS,带有,IN,谓词的子查询（续）,查找所有在,CS,系学习的学生。,SELECT Sno,，,Sname,，,Sdept,FROM Student,WHERE Sdept= CS ,；,结果为：,Sno,Sname,Sdept,200215121,李勇,CS,200215122,刘晨,CS,带有,IN,谓词的子查询（续）,将第一步查询嵌入到第二步查询的条件中,SELECT Sno,，,Sname,，,Sdept,FROM Student,WHERE Sdept,IN,(SELECT Sdept,FROM Student,WHERE Sname=,刘晨,),；,此查询为不相关子查询。,带有,IN,谓词的子查询（续）,用自身连接完成查询要求,SELECT,S1,.Sno,，,S1,.Sname,，,S1,.Sdept,FROM Student,S1,，,Student,S2,WHERE,S1,.Sdept =,S2,.Sdept AND,S2,.Sname = ,刘晨,；,带有,IN,谓词的子查询（续）,例,50,查询选修了课程名为,“,信息系统,”,的学生学号和姓名,SELECT Sno,，,Sname,最后在,Student,关系中,FROM Student,取出,Sno,和,Sname,WHERE Sno IN,(,SELECT Sno,然后在,SC,关系中找出选,FROM SC,修了,3,号课程的学生学号,WHERE Cno IN,(SELECT Cno,首先在,Course,关系中找出,FROM Course,“,信息系统,”,的课程号，为,3,号,WHERE Cname=,信息系统,),),;,带有,IN,谓词的子查询（续）,用连接查询实现,SELECT Sno,，,Sname,FROM Student,，,SC,，,Course,WHERE Student.Sno = SC.Sno AND,SC.Cno = Course.Cno AND,Course.Cname=,信息系统,；,嵌套查询,一、带有,IN,谓词的子查询,二、 带有比较运算符的子查询,三、 带有,ANY,（,SOME,）或,ALL,谓词的子查询,四、 带有,EXISTS,谓词的子查询,二、带有比较运算符的子查询,当能确切知道内层查询返回单值时，可用比较运算符（,，,=,，,=(SELECT AVG(Grade),FROM SC y,WHERE y.Sno=x.Sno);,相关子查询,带有比较运算符的子查询（续）,可能的执行过程：,1.,从外层查询中取出,SC,的一个元组,x,，将元组,x,的,Sno,值（,200215121,）传送给内层查询。,SELECT AVG(Grade),FROM SC y,WHERE y.Sno=200215121;,2.,执行内层查询，得到值,88,（近似值），用该值代替内层查询，得到外层查询：,SELECT Sno,，,Cno,FROM SC x,WHERE Grade =88,；,带有比较运算符的子查询（续）,3.,执行这个查询，得到,（,200215121,，,1,）,（,200215121,，,3,）,4.,外层查询取出下一个元组重复做上述,1,至,3,步骤，直到外层的,SC,元组全部处理完毕。结果为,:,（,200215121,，,1,）,（,200215121,，,3,）,（,200215122,，,2,）,嵌套查询,一、带有,IN,谓词的子查询,二、 带有比较运算符的子查询,三、 带有,ANY,（,SOME,）或,ALL,谓词的子查询,四、 带有,EXISTS,谓词的子查询,三、带有,ANY,（,SOME,）或,ALL,谓词的子查询,谓词语义,ANY,：任意一个值,ALL,：所有值,带有,ANY,（,SOME,）或,ALL,谓词的子查询 （续）,需要配合使用比较运算符, ANY,大于子查询结果中的某个值, ALL,大于子查询结果中的所有值, ANY,小于子查询结果中的某个值,= ANY,大于等于子查询结果中的某个值,= ALL,大于等于子查询结果中的所有值,= ANY,小于等于子查询结果中的某个值,= ALL,小于等于子查询结果中的所有值,= ANY,等于子查询结果中的某个值,=ALL,等于子查询结果中的所有值（通常没有实际意义）,!=,（或,）,ANY,不等于子查询结果中的某个值,!=,（或,）,ALL,不等于子查询结果中的任何一个值,带有,ANY,（,SOME,）或,ALL,谓词的子查询 （续）,例,53,查询其他系中比计算机科学某,一,学生年龄小的学生姓名和年龄,SELECT Sname,，,Sage,FROM Student,WHERE Sage ,ANY,(SELECT Sage,FROM Student,WHERE Sdept= CS ),AND Sdept ,CS ,; /*,父查询块中的条件 *,/,带有,ANY,（,SOME,）或,ALL,谓词的子查询 （续）,结果：,执行过程：,1.RDBMS,执行此查询时，首先处理子查询，找出,CS,系中所有学生的年龄，构成一个集合,(20,，,19),2.,处理父查询，找所有不是,CS,系且年龄小于,20,或,19,的学生,Sname,Sage,王敏,18,张立,19,带有,ANY,（,SOME,）或,ALL,谓词的子查询 （续）,用聚集函数实现,SELECT Sname,，,Sage,FROM Student,WHERE Sage ,(SELECT,MAX(Sage),FROM Student,WHERE Sdept=,CS ),AND Sdept CS,;,带有,ANY,（,SOME,）或,ALL,谓词的子查询 （续）,例,54,查询其他系中比计算机科学系,所有,学生年龄都小的学生姓名及,年龄。,方法一：用,ALL,谓词,SELECT Sname,，,Sage,FROM Student,WHERE Sage ALL,(SELECT Sage,FROM Student,WHERE Sdept= CS ),AND Sdept CS,;,带有,ANY,（,SOME,）或,ALL,谓词的子查询 （续）,方法二：用聚集函数,SELECT Sname,，,Sage,FROM Student,WHERE Sage ,(SELECT,MIN(Sage),FROM Student,WHERE Sdept= CS ),AND Sdept CS,;,带有,ANY,（,SOME,）或,ALL,谓词的子查询 （续）,表,ANY,（或,SOME,），,ALL,谓词与聚集函数、,IN,谓词,的等价转换关系,=,或,!=,=,ANY,IN,-,MAX,MIN,= MIN,ALL,-,NOT IN,MIN,MAX,= MAX,嵌套查询,一、带有,IN,谓词的子查询,二、 带有比较运算符的子查询,三、 带有,ANY,（,SOME,）或,ALL,谓词的子查询,四、 带有,EXISTS,谓词的子查询,带有,EXISTS,谓词的子查询,(,续）,1. EXISTS,谓词,存在量词,带有,EXISTS,谓词的子查询不返回任何数据，只产生逻辑真值,“,true,”,或逻辑假值,“,false,”,。,若内层查询结果非空，则外层的,WHERE,子句返回真值,若内层查询结果为空，则外层的,WHERE,子句返回假值,由,EXISTS,引出的子查询，其目标列表达式通常都用* ，因为带,EXISTS,的子查询只返回真值或假值，给出列名无实际意义,2. NOT EXISTS,谓词,若内层查询结果非空，则外层的,WHERE,子句返回假值,若内层查询结果为空，则外层的,WHERE,子句返回真值,带有,EXISTS,谓词的子查询,(,续）,例,55,查询所有选修了,1,号课程的学生姓名。,思路分析：,本查询涉及,Student,和,SC,关系,在,Student,中依次取每个元组的,Sno,值，用此值去检查,SC,关系,若,SC,中存在这样的元组，其,Sno,值等于此,Student.Sno,值，并且其,Cno= 1,，则取此,Student.Sname,送入结果关系,带有,EXISTS,谓词的子查询,(,续）,用嵌套查询,SELECT Sname,FROM,Student,WHERE EXISTS,(SELECT,*,FROM SC,WHERE Sno=,Student.Sno,AND Cno= 1 ),；,带有,EXISTS,谓词的子查询,(,续）,用连接运算,SELECT S