数据库基础SQL语言.ppt

1、数 据 库 基 础第三章 SQL语言,主讲人:范昭赋 中山大学计算机科学系 声明:本课件在汤娜老师课件基础上改编,3.1 SQL语言概况 3.2 SQL数据定义语言 3.3 SQL数据查询语言 3.3.1 单表查询 3.3.2 连接查询 3.3.3 嵌套查询 3.3.4 select的一般格式 3.3.5 总结,3.1 SQL语言概况,SQL简介 结构化查询语言SQL（Structured Query Language）是一种介于关系代数与关系演算之间的语言，其功能包括查询、操纵、定义和控制四个方面，是一个通用的、功能极强的关系数据库语言。目前已成为关系数据库的标准语言。 SQL语言的版本包括

2、：SQL-89，SQL-92， SQL3。 SQL特点 SQL语言之所以能够为用户和业界所接受，成为国际标准，是因为它是一个综合的、通用的、功能极强同时又简洁易学的语言。 SQL语言集数据查询（data query）、数据操纵（data manipulation）、数据定义（data definition）和数据控制（data control）功能于一体，充分体现了关系数据语言的特点和优点。,3.1 SQL语言概况,SQL的基本概念 SQL语言支持关系数据库三级模式结构。其中外模式对应于视图（View），模式对应于基本表，内模式对应于存储文件。 基本表是本身独立存在的表，在SQL中一个关系就对

3、应一个表。一些基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。 存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理文件结构是任意的。 视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。 用户可以用SQL语言对视图和基本表进行查询。在用户眼中，视图和基本表都是关系，而存储文件对用户是透明的。,3.1 SQL语言概况,SQL的基本功能 （1）数据定义功能 基本表的建立、取消与更改。 索引的建立与取消。 视图的创建与取消。 （2）数据查询功能（包

4、括数据表和视图） （3）数据更新功能 数据插入、删除、修改功能。 （4）数据控制功能 数据库保护功能（安全性和完整性保护）。 事务管理功能（数据库故障恢复和并发事务处理）。,SQL的其他一些功能： 与主语言的接口：SQL提供4条游标语句（见3.7节）用于解决SQL与主语言之间因数据不匹配所引起的接口问题。 存储过程：SQL还提供远程调用功能，在远程方式下客户机中的应用可通过网络调用服务器数据库中的存储过程。存储过程是一个由SQL语句所组成的过程，该过程在被应用程序调用后就执行SQL的语句系列，最后将结果返回应用。存储过程可为多个应用所共享。,3.1 SQL语言概况,3.2 SQL数据定义语言,

5、3.2 SQL数据定义语言,定义基本表 一般格式如下：CREATE TABLE ( 列级完整性约束条件 ， 列级完整性约束条件.) ,;其中是所要定义的基本表的名字，它可以由一个或多个属性（列）组成。建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由DBMS自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。,3.2 SQL数据定义语言,定义基本表(续) 下面我们以一个“学生课程”数据库为例说明Sql语句的各种用法。“学生课程”数据库中包括

6、三个表： “学生”表Student由学号（Sno）、姓名（Sname）、性别（Ssex）、年龄（Sage）、所在系（Sdept）五个属性组成，可记为： Student(Sno,Sname,Ssex,Sage,Sdept) Sno “课程”表Course由课程号（Cno）、课程名（Cname）、先修课号（Cpno）、老师（Teacher）四个属性组成，可记为： Course(Cno,Cname,Cpno,teacher) Cno “学生选课”表SC由学号（Sno）、课程号（Cno）、成绩（Grade）三个属性组成，可记为： SC(Sno,Cno,Grade) (SNO, CNO),3.2 SQL

7、数据定义语言,定义基本表(续) 例1： 建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成，其中学号属性不能为空，并且其值是唯一的。 CREATETABLE Student ( Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20), Ssex CHAR(1), Sage INT, Sdept CHAR(15);,3.2 SQL数据定义语言,定义基本表(续) 定义表的各个属性时需要指明其数据类型及长度。不同的数据库系统支持的数据类型不完全相同，例如IBM DB2 SQL主要支持以下数据类型：

8、 SMALLINT 半字长二进制整数。 INTEGER或INT 全字长二进制整数。 DECIMAL(p,q)或DEC(p,q) 压缩十进制数，共p位，其中小 数点后有q位。0qp15，q=0时可以省略。 FLOAT 双字长浮点数。 CHARTER(n)或CHAR(n) 长度为n的定长字符串。 VARCHAR(n) 最大长度为n的变长字符串。 GRAPHIC(n) 长度为n的定长图形字符串。VARGRAPHIC(n) 最大长度为n的变长图形字符串。 DATE 日期型，格式为YYYY-MM-DD。 TIME 时间型，格式为HH.MM.SS。 DATETIME 日期加时间。 TIMESTAMP 时间

9、戳。,3.2 SQL数据定义语言,修改基本表 一般格式为： ALTER TABLE ADD 完整性约束DROP CONSTRAINT MODIFY ; 其中指定需要修改的基本表，ADD子句用于增加新列和新的完整性约束条件，DROP 子句用于删除指定的完整性约束条件，MODIFY子句用于修改原有的列定义。,3.2 SQL数据定义语言,修改基本表(续) 例2 向Student表增加“入学时间”列，其数据类型为日期型 ALTER TABLE Student ADD Scome DATE; 不论基本表中原来是否已有数据，新增加的列一律为空值。 例3 将年龄的数据类型改为半字长整数 ALTER TABL

10、E Student MODIFY Sage SMALLINT; 修改原有的列定义有可能会破坏已有数据。 例4 删除关于学号必须取唯一值的约束 ALTER TABLE Student DROP UNIQUE(Sno); 说明 ： SQL没有提供删除属性列的语句，用户只能间接实现这一功能，即先原表中要保留的列及其内容复制到一个新表中，然后删除原表，并将新表重命名为原表名。但sql server2000提供了删除列,3.2 SQL数据定义语言,删除基本表 一般格式为：DROP TABLE 例5 删除Student表 DROP TABLEStudent 基本表定义一旦删除，表中的数据、在此表上建立的索

11、引都将自动被删除掉，而建立在此表上的视图虽仍然保留，但已无法引用。因此执行删除操作一定要格外小心。,3.2 SQL数据定义语言,建立索引 一般格式为：CREATE UNIQUE CLUSTER INDEX ON (,.); 其中，指定要建索引的基本表的名字。索引可以建在该表的一列或多列上，各列名之间用逗号分隔。每个后面还可以用指定索引值的排列次序，包括ASC（升序）和DESC（降序）两种，缺省值为ASC。,3.2 SQL数据定义语言,建立索引(续) 例6 为学生-课程数据库中的Student、Course、SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序

12、建唯一索引，Sno、Cno表按学号升序和课程号降序建唯一索引。 CREATE UNIQUE INDEX Stusno ON Student(Sno);CREATE UNIQUE INDEX Coucno ON Course(Cno); CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC); 删除索引 一般格式 DROP INDEX ,3.3 SQL数据查询语言,SELECT语句 一般格式为：SELECT ALL|DISTINCT,. FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESC;整个SELECT语句的含

13、义是 将FROM子句指定的基本表或视图做笛卡尔集， 再根据WHERE子句的条件表达式，从中找出满足条件的元组； 如果有GROUP子句，则将结果按的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。 再根据SELECT子句中的目标列表达式形成结果表。 如果有ORDER子句，则结果表还要按的值的升序或降序排序。 Select语句的强大体现在where子句中。,3.3 SQL数据查询语言(1)（单表查询）,一、选择表中的若干列 1). 查询指定列 例1: 查询全体学生的学号与姓名 SELECT Sno,Sna

14、me FROM Student; 说明： 中各个列的先后顺序可以与表中的顺序不一致。也就是说，用户在查询时可以根据应用的需要改变列的显示顺序。 例2: 查询全体学生的姓名、学号、所在系 SELECT Sname, Sno, Sdept FROM Student; 说明：这时结果表中的列的顺序与基表中不同，是按查询要求，先列出姓名属性，然后再列学号属性和所在系属性。,3.3 SQL数据查询语言(1) 单表查询(续),2). 查询全部列 例3：查询全体学生的详细记录 SELECT *FROM Student; 说明：该SELECT语句实际上是无条件地把Student表的全部信息都查询出来，所以也称

15、为全表查询，这是最简单的一种查询。 也可以不用*用列名来改变列的顺序 3).查询经过计算的值 SELECT子句的不仅可以是表中的属性列，也可以是有关表达式，即可以将查询出来的属性列经过一定的计算后列出结果。,3.3 SQL数据查询语言(1) 单表查询(续),例4:查全体学生的姓名及其出生年份 SELECT Sname, 2004-Sage FROM Student; 说明：本例中，中第二项不是通常的列名，而是一个计算表达式，是用当前的年份（假设为2004年）减去学生的年龄，这样，所得的即是学生的出生年份。输出的结果为： Sname 2004 -Sage 李勇 1976 刘晨 1977 王名 1

16、978 张立 1978 不仅可以是算术表达式，还可以是字符串常量、函数等,3.3 SQL数据查询语言(1) 单表查询(续),例5：查全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名 SELECT Sname, Year of Birth:, 2004 -Sage, ISLOWER(Sdept) FROM Student 结果为： Sname Year of Birth: 2004-Sage ISLOWER(Sdept) 李勇 Year of Birth: 1976 cs 刘晨 Year of Birth: 1977 if 王名 Year of Birth: 1978 ma 张立 Y

17、ear of Birth: 1978 if,3.3 SQL数据查询语言(1) 单表查询(续),用户可以通过指定别名来改变查询结果的列标题，这对于含算术表达式、常量、函数名的目标列表达式尤为有用。例如对于上例，可以如下定义列别名 SELECT Sname NAME, Year of Birth: BIRTH, 2004 -Sage BIRTHDAY, ISLOWER(Sdept) DEPARTMENT FROM Student; 结果为： Sname BIRTH BIRTHDAY DEPARTMENT 李勇 Year of Birth: 1976 cs 刘晨 Year of Birth: 197

18、7 if 王名 Year of Birth: 1978 ma 张立 Year of Birth: 1978 if,3.3 SQL数据查询语言(1) 单表查询(续),二、选择表中的若干元组 1). 消除取值重复的行 例6: 查所有选修过课的学生的学号 SELECT Sno FROM SC; 假设SC表中有下列数据 Sno Cno Grade- - - 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80,3.3 SQL数据查询语言(1) 单表查询(续),例6: (续)执行上面的SELECT语句后，结果为：Sno - 95001 950019

19、5001 95002 95002 该查询结果里包含了许多重复的行。如果想去掉结果表中的重复行，必须指定DISTINCT短语： SELECT DISTINCT Sno FROM SC; 执行结果为： Sno - 95001 95002,3.3 SQL数据查询语言(1) 单表查询(续),2).查询满足条件的元组 查询满足指定条件的元组可以通过WHERE子句实现。WHERE子句常用的查询条件如表3-3所示。 表3-3 常用的查询条件 查询条件 谓 词 - 比较 上述比较运算符 确定范围 BETWEEN AND, NOT BETWEEN AND 确定集合 IN, NOT IN 字符匹配 LIKE, N

20、OT LIKE 空值 IS NULL, IS NOT NULL 多重条件 NOT,AND, OR,3.3 SQL数据查询语言(1) 单表查询(续),(a) 比较 例7: 查计算机系全体学生的名单 SELECT Sname FROM Student WHERE Sdept = CS; 例8: 查所有年龄在20岁以下的学生姓名及其年龄 SELECT Sname, Sage FROM Student WHERE Sage = 20;,3.3 SQL数据查询语言(1) 单表查询(续),例9:查考试成绩有不及格的学生的学号 SELECT DISTINCT Sno FROM SC WHERE Grade

21、60; 这里使用了DISTINCT短语，当一个学生有多门课程不及格，他的学号也只列一次。,3.3 SQL数据查询语言(1) 单表查询(续),(b) 确定范围 例10: 查询年龄在20至23岁之间的学生的姓名、系别、和年龄 SELECT Sname, Sdept, SageFROM Student WHERE Sage BETWEEN 20 AND 23; 与BETWEEN.AND.相对的谓词是NOT BETWEEN.AND.。 例11:查询年龄不在20至23岁之间的学生姓名、系别和年龄。 SELECT Sname, Sdept, Sage FROM StudentWHERE Sage NOT

22、BETWEEN 20 AND 23;,3.3 SQL数据查询语言(1) 单表查询(续),(c) 确定集合 例12: 查信息系（IS）、数学系（MA）和计算机科学系（CS）的学生的姓名和性别 SELECT Sname, Ssex FROM Student WHERE Sdept IN (IS, MA, CS) 与IN相对的谓词是NOT IN，用于查找属性值不属于指定集合的元组。 例13:查既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别 SELECT Sname, Ssex FROM Student WHERE Sdept NOT IN (IS, MA, CS),3.3 SQL数据查询

23、语言(1) 单表查询(续),(d) 字符匹配 谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下： NOT LIKE ESCAPE 其含义是查找指定的属性列值与相匹配的元组。可以是一个完整的字符串，也可以含有通配符%和_。%(百分号) 代表任意长度（长度可以为0）的字符串。_(下横线) 代表任意单个字符。 例14: 查所有姓刘的学生的姓名、学号和性别 SELECT Sname, Sno, SsexFROM Student WHERE Sname LIKE 刘%;,3.3 SQL数据查询语言(1) 单表查询(续),例15: 查姓“欧阳”且全名为三个汉字的学生的姓名 SELECT Sname

24、 FROM Student WHERE Sname LIKE 欧阳_ _; 注意，由于一个汉字占两个字符的位置，所以匹配串欧阳后面需要跟个_。 例16: 查名字中第二字为“阳”字的学生的姓名和学号 SELECT Sname, Sno FROM Student WHERE Sname LIKE _阳%; 例17: 查所不姓刘的学生姓名 SELECT Sname, Sno, Ssex FROM Student WHERE Sname NOT LIKE 刘%;,3.3 SQL数据查询语言(1) 单表查询(续),如果用户要查询的匹配字符串本身就含有%或_，比如要查名字DB_Design的课程的学分，应

25、如何实现呢？这时就要使用ESCAPE 短语对通配符进行转义了。 例18: 查DB_Design课程的课程号和任课老师 SELECT Cno, teacher FROM Course WHERE Cname LIKE DB_Design ESCAPE 说明：ESCAPE 短语表示为换码字符，这样匹配串中紧跟在后面的字符”_”不再具有通配符的含义，而是取其本身含义，被转义为普通的”_”字符。,3.3 SQL数据查询语言(1) 单表查询(续),例19：查以”DB_”开头，且倒数第三个字符为i的课程的详细情况 SELECT * FROM Course WHERE Cname LIKE DB_%i_ _

26、 ESCAPE ; 说明：注意这里的匹配字符串DB_%i_ _ _。第一个_前面有换码字符，所以它被转义为普通的_字符。而%、第二个_和第三个_前面均没有换码字符，所以它们仍作为通配符。其执行结果为： Cno Cname teacher - 8 DB_Design 王诚 10 DB_Programing 何名名 13 DB_DBMS Design 李桑,3.3 SQL数据查询语言(1) 单表查询(续),(e) 涉及空值的查询 例20:某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面我们来查一下缺少成绩的学生的学号和相应的课程号 SELECT Sno, Cno FROM

27、SCWHERE Grade IS NULL; 注意这里的IS不能用等号（=） 代替。 例21:查所有有成绩的记录的学生学号和课程号 SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL;,3.3 SQL数据查询语言(1) 单表查询(续),(f)多重条件查询 逻辑运算符AND和OR可用来联结多个查询条件。如果这两个运算符同时出现在同一个WHERE条件子句中，则AND的优先级高于OR，但用户可以用括号改变优先级。 例22:查CS系年龄在20岁以下的学生姓名 SELECT Sname FROM Student WHERE Sdept=CS AND Sage2

28、0; 例12 中的IN谓词实际上是多个OR运算符的缩写，因此例12中的查询也可以用OR运算符写成如下等价形式： SELECT Sname, Ssex FROM Student WHERE Sdept=IS OR Sdept=MA OR Sdept=CS;,3.3 SQL数据查询语言(1) 单表查询(续),(3). 对查询结果排序 如果没有指定查询结果的显示顺序，DBMS将按其最方便的顺序（通常是元组在表中的先后顺序）输出查询结果。用户也可以用ORDER BY子句指定按照一个或多个属性列的升序（ASC）或降序（DESC）重新排列查询结果，其中升序ASC为缺省值。 例23:查询选修了3号课程的学生

29、的学号及其成绩，查询结果按分数的降序排列 SELECT Sno, Grade FROM SC WHERE Cno=3 ORDER BY Grade DESC; 前面已经提到，可能有些学生选修了3号课程后没有参加考试，即成绩列为空值。用ORDER BY子句对查询结果按成绩排序时，若按升序排，成绩为空值的元组将最后显示，若按降序排，成绩为空值的元组将最先显示。,3.3 SQL数据查询语言(1) 单表查询(续),例24: 查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列 SELECT * FROM Student ORDER BY Sdept, Sage DESC;,3.3

30、 SQL数据查询语言(1) 单表查询(续),(4) 使用集函数 为了进一步方便用户，增强检索功能，SQL提供了许多集函数，主要包括：,如何处理null null是一个常量，仅在数值和字符串类型的列中有意义，代表的是没有意义或者是不确定的值。例如，学生选了课程，当成绩没有出来时grade字段的值应该为空；或者工资表中一个行政人员在课时补贴一栏的值为null，因为它不可能有课时补贴的收入,例一 select * from sc where grade =60 (不会选取所有记录，那些grade的值不会选取出来） 例二：有两个关系R,S如下图 select * from R,S where R.B=

31、S.D的结果如下图,对于集合运算 COUNT(列名) NULL值不会计算进去 SUM 不影响 AVG=SUM/COUNT 所以NULL值不会计算进去 MAX MIN 思考题：sql server 中的min函数是否会考虑null值？ Example select count(*) from customers; 5 / 6 select count(cid) from customers; /* null values not counted * / 5 / 6 select count(distinct city) from customers; 3 / 3 select count(cit

32、y) from customers 5 / 5,3.3 SQL数据查询语言(1) 单表查询(续),(4) 使用集函数 如果指定DISTINCT短语，则表示在计算时先要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语（ALL为缺省值），则表示不取消重复值。 只能用于SELECT子句和HAVING子句中 例25:查询学生总人数 SELECT COUNT(*) FROM Student; 例26: 查询选修了课程的学生人数 SELECT COUNT(DISTINCT Sno) FROM SC; 说明：学生每选修一门课，在SC中都有一条相应的记录，而一个学生一般都要选修多门课程，为

33、避免重复计算学生人数，必须在COUNT函数中用DISTINCT短语。,3.3 SQL数据查询语言(1) 单表查询(续),(4) 使用集函数 例27：计算1号课程的学生平均成绩 SELECT AVG(Grade) FROM SC WHERE Cno=1; 例28：查询学习1号课程的学生最高分数 SELECT MAX(Grade)FROM SC WHERE Cno=1; 注意：集合函数只能用在select子句和having子句中 SELECT sno, FROM sc WHERE sc.grade = max(sc.grade ); 语法错误，应写为： SELECT sno, FROM sc WH

34、ERE sc.grade = (select max(sc.grade ) from sc );,3.3 SQL数据查询语言(1) 单表查询(续),(5) 对查询结果分组 GROUP BY子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组。 对查询结果分组的目的是为了细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。 例29：查询各个课程号与相应的选课人数 SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno; 该SELECT语句对SC表按Cno

35、的取值进行分组，所有具有相同Cno值的元组为一组，然后对每一组作用集函数COUNT以求得该组的学生人数。查询结果为： Cno COUNT(Sno) - - 1 22 2 34 3 44 4 33 5 48,3.3 SQL数据查询语言(1) 单表查询(续),(5) 对查询结果分组 如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。 例30：查询选修了3门以上课程的学生的学号 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)3; 说明：查选修课程超过3门的信息系学生的学号，首先基本表中的学

36、生。然后求其中每个学生选修了几门课，为此需要用GROUP BY子句按Sno进行分组，再用集函数COUNT对每一组计数。如果某一组的元组数目大于3，则表示此学生选修的课超过3门，应将他的学生号选出来。HAVING短语指定选择组的条件，只有满足条件（即元组个数3）的组才会被选出来。 WHERE子句与HAVING短语的根本区别在于作用对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。 思考题，请找出总分超过600分的学生,注意：select子句中如果有集合函数，则不允许出现包含其他的字段的表达式，但如果有groupby 子句，则允许g

37、roup by 子句出现的字段。 如果在查询语句中有group by 子句，则select子句则不允许出现包含其他的字段的表达式， 允许group by 子句出现的字段和集合函数表达式。 例1 select sno,sum(grade) from sc /*not valid*/ 例2 select sno,cno,sum(grade) from sc group by sno /*not valid*/ 例3 select sno,sum(grade) from sc group by sno /*valid*/,3.3 SQL数据查询语言（2） 连接查询,连接查询 一个数据库中的多个表之间

38、一般都存在某种内在联系，它们共同提供有用的信息。前面的查询都是针对一个表进行的。若一个查询同时涉及两个以上的表，则称之为连接查询。连接查询主要包括等值连接、非等值连接查询、自身连接查询（连接 二、自身连接）、外连接查询（连接 三、外连接）和复合条件连接查询（连接 四、复合条件连接）。 （1） 等值与非等值连接查询 用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：. . 其中比较运算符主要有：=、=、=、!=、!=,3.3 SQL数据查询语言（2） 连接查询,例32：查询每个学生及其选修课程的情况 SELECT Student.*, SC.* FROM Student, SC WHER

39、E Student.Sno=SC.Sno 连接运算中有两种特殊情况，一种称为卡氏积连接，另一种称为自然连接。卡氏积是不带连接谓词的连接。两个表的卡氏积即是两表中元组的交叉乘积，也即其中一表中的每一元组都要与另一表中的每一元组作拼接，因此结果表往往很大。 如果是按照两个表中的相同属性进行等值连接，且目标列中去掉了重复的属性列，但保留了所有不重复的属性列，则称之为自然连接。 例33：自然连接Student和SC表 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student, SC WHERE Student.Sno=S

40、C.Sno;,3.3 SQL数据查询语言（2） 连接查询,(2) 自连接 连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，这种连接称为表的自连接。 例34:查询每一门课的先修课课名 完成该查询的SQL语句为： SELECT FIRST.Cno, FIRST.Pcno , SECOND. Cname FROM Course FIRST, Course SECOND WHERE FIRST.Pcno=SECOND.Cno; (3) 外连接 在通常的连接操作中，只有满足连接条件的元组才能作为结果输出，如在例32和例33的结果表中没有关于95003和95004两个学生的信息，原因在于

41、他们没有选课，在SC表中没有相应的元组。但是有时我们想以Student表为主体列出每个学生的基本情况及其选课情况，若某个学生没有选课，则只输出其基本情况信息，其选课信息为空值即可，这时就需要使用外连接（Outer Join）。这样，我们就可以如下改写例33：,3.3 SQL数据查询语言（2） 连接查询,SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student JOIN SC ON Student.Sno=SC.Sno 可以有inner join；outer join;left outer join;right o

42、uter join (4) 复合条件连接 上面各个连接查询中，WHERE子句中只有一个条件，即用于连接两个表的谓词。WHERE子句中有多个条件的连接操作，称为复合条件连接。 例35:查询选修2号课程且成绩在90分以上的所有学生 SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno=2 AND SC.Grade90; SELECT Student.Sno, Sname FROM Student inner join SC on Student.Sno=SC.Sno WHERE SC.Cno=2

43、 AND SC.Grade90;,3.3 SQL数据查询语言（2） 连接查询,连接操作除了可以是两表连接，一个表与其自身连接外，还可以是两个以上的表进行连接，后者通常称为多表连接。 例36:查询每个学生及其选修的课程名其及成绩 SELECT Student.Sno, Sname, Course.Cname, SC.Grade FROM Student, SC, Course WHERE Student.Sno=SC.Sno and SC.Cno=Course.Cno;,3.3 SQL数据查询语言（3） 嵌套查询,嵌套查询概述 一个SELECT-FROM-WHERE语句称为一个查询块 将一个查询

44、块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询 基本原理： 基本特征是一个查询语句中WHERE子句的逻辑条件含有另一个查询语句。但查询语句的结果是一张表，表就是元组的集合，因而可以将WHERE子句内的查询语句看作是一个集合。在这种观点之下，WHERE子句所涉及到的逻辑条件就可以转化为“元素x与集合S”或者“集合S1与集合S2”之间的关系表示。,嵌套查询(续),SELECT Sname外层查询/父查询 FROM Student WHERE Sno IN （SELECT Sno 内层查询/子查询 FROM SC WHERE Cno= 2 ）；,嵌套查询(续),子查

45、询的限制 不能使用ORDER BY子句 外层select语句的变量可以用在子查询中，但反之则不行 嵌套查询分类 不相关子查询 子查询的查询条件不依赖于父查询 相关子查询 子查询的查询条件依赖于父查询,不相关子查询的范例: SELECT s1.Sno，s1.Sname，s1.Sdept FROM Student s1 WHERE s1.Sdept IN (SELECT s2.Sdept FROM Student s2 WHERE s2.Sname= 刘晨 )； 相关子查询的范例: 查询选修了c02课程的学生姓名 select sname from student where c02 in (se

46、lect cno from sc where sc.sno=student.sno),嵌套查询求解方法,不相关子查询 是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。 相关子查询 首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表； 然后再取外层表的下一个元组； 重复这一过程，直至外层表全部检查完为止。,引出子查询的谓词,带有IN谓词的子查询 带有比较运算符的子查询 带有ANY或ALL谓词的子查询 带有EXISTS谓词的子查询,一、带有IN谓词的子查询,格式：expr NO

47、T IN (subquery) | expr NOT IN (val ,val ) 例37 查询与“刘晨”在同一个系学习的学生。 此查询要求可以分步来完成 确定“刘晨”所在系名 SELECT Sdept FROM Student WHERE Sname= 刘晨 ； 结果为： Sdept IS,带有IN谓词的子查询（续）, 查找所有在IS系学习的学生。 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept= IS ； 结果为： Sno Sname Sdept 95001 刘晨 IS 95004 张立 IS,构造嵌套查询,将第一步查询嵌入到第二步查询的条件

48、中 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= 刘晨 )； 此查询为不相关子查询。DBMS求解该查询时也是分步去做的。,带有IN谓词的子查询（续）,用自身连接完成本查询要求 SELECT S1.Sno，S1.Sname，S1.Sdept FROM Student S1，Student S2 WHERE S2.Sname = 刘晨 AND S1.Sdept = S2.Sdept ；,带有IN谓词的子查询（续）,父查询和子查询中的表均可以定义别名 SELECT

49、Sno，Sname，Sdept FROM Student S1 WHERE S1.Sdept IN (SELECT Sdept FROM Student S2 WHERE S2.Sname= 刘晨 )；,带有IN谓词的子查询（续）,例38查询选修了课程名为“信息系统”的学生学号和姓名 SELECT Sno，Sname 最后在Student关系中 FROM Student 取出Sno和Sname WHERE Sno IN (SELECT Sno 然后在SC关系中找出选 FROM SC 修了3号课程的学生学号 WHERE Cno IN (SELECT Cno 首先在Course关系中找出“信 FR

50、OM 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=信息系统；,不相关子查询总结 当查询涉及到多个表时，使用嵌套结构逐次求解，就可以将复杂的问题转化为多个相对简单的查询，使得语句层次分明，层层嵌套方式反映了 SQL语言的结构化 有些嵌套查询可以用连接运算替代，但同时对于多表查询来说，嵌套查询的执行

51、效率也比连接查询的笛卡尔乘积效率要高。,相关子查询 查询选修了c02课程的学生姓名， select sname from student where c02 in (select cno from sc where sc.sno=student.sno) 注意：外层select语句的变量可以用在子查询中，但反之则不行 查询选修了c02课程的学生姓名，并且成绩为A的 select sname from student where sc.grade=A AND c02 in (select cno from sc where sc.sno=student.sno) /*not valid*/,二、

52、带有比较运算符的子查询,当能确切知道内层查询返回单值时，可用比较运算符（，=，）。 与ANY或ALL谓词配合使用 格式：expr SOME| ANY | ALL (subquery) is some operator in the set ,=,带有比较运算符的子查询（续）,例：假设一个学生只可能在一个系学习，并且必须属于一个系，则在例37可以用 = 代替IN ： SELECT Sno，Sname，Sdept FROM Student WHERE Sdept =( SELECT Sdept FROM Student WHERE Sname= 刘晨 )；,带有比较运算符的子查询（续）,子查询一定

53、要跟在比较符之后 错误的例子： SELECT Sno，Sname，Sdept FROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname= 刘晨 ) = Sdept；,三、带有ANY或ALL谓词的子查询,谓词语义 SOME/ANY：任意一个值 ALL：所有值,带有ANY或ALL谓词的子查询（续）,需要配合使用比较运算符 ANY大于子查询结果中的某个值 ALL大于子查询结果中的所有值 = ANY大于等于子查询结果中的某个值 = ALL大于等于子查询结果中的所有值 ）ANY不等于子查询结果中的某个值 !=（或）ALL不等于子查询结果中的任何

54、一个值,带有ANY或ALL谓词的子查询（续）,例39 查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄 SELECT Sname，Sage FROM Student WHERE Sage IS ; /* 注意这是父查询块中的条件 */,带有ANY或ALL谓词的子查询（续）,结果 Sname Sage 王敏 18 执行过程 1.DBMS执行此查询时，首先处理子查询，找出 IS系中所有学生的年龄，构成一个集合(19，18) 2. 处理父查询，找所有不是IS系且年龄小于 19 或 18的学生,带有ANY或ALL谓词的子查询（续）,ANY和ALL谓词有时可以用集函数实现 ANY与A

55、LL与集函数的对应关系 用集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数,带有ANY或ALL谓词的子查询（续）,例39：用集函数实现例39 SELECT Sname，Sage FROM Student WHERE Sage IS ;,带有ANY或ALL谓词的子查询（续）,例40 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。 方法一：用ALL谓词 SELECT Sname，Sage FROM Student WHERE Sage IS ; 查询结果为空表。,带有ANY或ALL谓词的子查询（续）,方法二：用集函数 SELECT Sname，Sage

56、FROM Student WHERE Sage IS ;,四、带有EXISTS谓词的子查询,1. EXISTS谓词 2. NOT EXISTS谓词 3. 不同形式的查询间的替换 4.用EXISTS/NOT EXISTS实现全称量词 （除法） 5. 用EXISTS/NOT EXISTS实现逻辑蕴函 6. 用EXISTS/NOT EXISTS实现差,带有EXISTS谓词的子查询(续）,1. EXISTS谓词 存在量词 格式：exists(subsquery) 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。 若内层查询结果非空，则返回真值 若内层查询

57、结果为空，则返回假值 由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义 2. NOT EXISTS谓词,带有EXISTS谓词的子查询(续）,例41 查询所有选修了1号课程的学生姓名。 思路分析： 本查询涉及Student和SC关系。 在Student中依次取每个元组的Sno值，用此值去检查SC关系。 若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= 1，则取此Student.Sname送入结果关系。,带有EXISTS谓词的子查询(续）,用嵌套查询 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM