关系数据库标准语言SQL-new简化.ppt

《关系数据库标准语言SQL-new简化.ppt》由会员分享，可在线阅读，更多相关《关系数据库标准语言SQL-new简化.ppt（145页珍藏版）》请在三一办公上搜索。

1、第四章 SQL语言,第四章 SQL语言,4.1 SQL概述4.2 示例数据库4.3 SQL数据定义功能4.4 SQL数据查询功能4.5 SQL数据修改功能4.6 SQL数据控制功能4.7 嵌入式SQL及存储过程4.8 ODBC 编程,4.1 SQL概述,历史1974年，由Boyce和Chamber提出。1975-1979年，在System R上实现，由IBM的San Jose研究室研制，称为Sequel，现在称为SQL(Struceured Query Languang),结构化查询语言，是关系数据库的标准语言;SQL是一个通用的、功能极强的关系数据库语言。,4.1 SQL概述,有关标准SQL

2、-86：“数据库语言SQL”，1986.10SQL-89：“具有完整性增强的数据库语言SQL”，增加了对完整性约束的支持。1989年SQL-92：“数据库语言SQL”，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。1992年SQL-3：又称SQL-99，增加对面向对象模型的支持。1999年SQL2003 2003年,4.1 SQL概述,SQL语言的特点1.综合统一集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。可以独立完成数据库生命周期中的全部活动：定义关系模式，插入数据，建立数据库；对数据库中的数据进

3、行查询和更新；数据库重构和维护 数据库安全性、完整性控制等用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据的运行。单一的结构-关系，数据操作符统一,4.1 SQL概述,2.高度非过程化非关系数据模型的数据操纵语言“面向过程”，必须制定存取路径用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径。存取路径的选择以及SQL的操作过程由系统自动完成。,4.1 SQL概述,3.面向集合的操作方式非关系数据模型采用面向记录的操作方式，操作对象是一条记录SQL采用集合操作方式 操作对象、查找结果可以是元组的集合 一次插入、删除、更新操作的对象可以是元组的集合,4.1 SQL概述,4.以

4、同一种语法结构提供多种使用方式SQL是独立的语言 能够独立地用于联机交互的使用方式SQL又是嵌入式语言 SQL能够嵌入到高级语言（例如C，C+，Java）程序中，供程序员设计程序时使用,4.1 SQL概述,5.语言简洁，易学易用 SQL功能极强，完成核心功能只用了9个动词。,4.1 SQL概述,SQL语言的基本概念 1.SQL支持关系数据库三级模式结构。,4.1 SQL概述,2.基本表本身独立存在的表SQL中一个关系就对应一个基本表一个(或多个)基本表对应一个存储文件一个表可以带若干索引3.存储文件逻辑结构组成了关系数据库的内模式物理结构是任意的，对用户透明4.视图从一个或几个基本表导出的表数

5、据库中只存放视图的定义而不存放视图对应的数据视图是一个虚表用户可以在视图上再定义视图,4.2 示例数据库,DEPT(DNO,DNAME,DEAN)系表S(SNO,SNAME,SEX,AGE,DNO,ADRESS)学生表COURSE(CNO,CN,PCNO,CREDIT,GRADE)-课程表SC(SNO,CNO,SCORE)-学生选课表PROF(PNO,PNAME,AGE,DNO,SAL)-教师表PC(PNO,CNO)-教师授课表,学生成绩及教师任课数据库,4.3 SQL数据定义功能,4.3.1 数据库的建立与撤消4.3.2 域定义4.3.3 基本表的定义4.3.4 索引的定义4.3.5 数据库

6、的建立与撤消4.3.6 SQL数据定义特点,4.3 SQL数据定义功能,SQL的数据定义功能:域定义、表定义、视图和索引的定义。,4.3.1 数据库的建立与撤消,有的数据库系统支持多库。建立一个新数据库 create database 数据库名示例:create database student指定当前数据库 database 数据库名关闭当前数据库 close database 数据库名撤消一个数据库 drop database 数据库名示例:drop database student,域定义,域类型（SQL-92）SQL中域的概念用数据类型来实现，定义表的属性时需要指明其数据类型及长度。c

7、har（n）：固定长度的字符串。varchar（n）：可变长字符串。int：整数。smallint：小整数类型。numeric（p，d）：定点数，小数点左边p位，右边q位。real：浮点数，取决于机器精度。double precision：双精度浮点数，取决于机器精度。date：日期（年、月、日）。time：时间（小时、分、秒）。interval：两个date或time类型数据之间的差。,域定义,域定义格式 create domain 域名 数据类型示例create domain person-name char（20）类似C语言中：typedef ADDRESS_LISTchar name1

8、0;char telephone20;char location20char email20;ADDRESS_LIST tom;,4.3.3 基本表的定义,基本表的定义（CREATE）create table 表名（列名 数据类型 default 缺省值 not null，列名 数据类型 default 缺省值 not null，primary key（列名，列名），foreign key（列名，列名）references 表名（列名，列名），check（条件）,4.3.3 基本表的定义,示例1 建立“学生”表S，学号是主码，姓名取值唯一。CREATE TABLE S(Sno CHAR(9)P

9、RIMARY KEY，/*列级完整性约束条件*/Sname CHAR(20)UNIQUE，/*Sname取唯一值*/sex CHAR(2)，age SMALLINT，DNO CHAR(20)；,4.3.3 基本表的定义,示例2 create domain person_name char(20)create table PROF(PNO char(10),PNAME person_name not null,SAL int,AGE int,DNO char(10),primary key(PNO),foreign key(DNO)references DEPT(DNO),check(SAL 0

10、),4.3.3 基本表的定义,示例3 建立一个“学生选课”表SCCREATE TABLE SC(Sno CHAR(9)，Cno CHAR(4)，SCORE SMALLINT，PRIMARY KEY(Sno，Cno)，/*主码由两个属性构成，必须作为表级完整性进行定义*/FOREIGN KEY(Sno)REFERENCES S(Sno)，/*表级完整性约束条件，Sno是外码，被参照表是S*/FOREIGN KEY(Cno)REFERENCES Course(Cno)/*表级完整性约束条件，Cno是外码，被参照表是Course*/);,4.3.3 基本表的定义,修改基本表定义（ALTER）格式：A

11、LTER TABLE ADD 完整性约束-增加新列 DROP-删除列 ALTER COLUMN；-修改列定义,4.3.3 基本表的定义,示例1 向prof表增加“location”列，字符类型为字符型。alter table PROFadd LOCATION char(30)不论基本表中原来是否已有数据，新增加的列的值一律为空值。示例2 将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。ALTER TABLE S ALTER COLUMN age INT；示例3 增加课程名称必须取唯一值的约束条件。ALTER TABLE Course ADD UNIQUE(Cname);,4.3

12、.3 基本表的定义,撤消基本表定义（drop）格式drop table 表名 RESTRICT|CASCADE；RESTRICT：删除表是有限制的。欲删除的基本表不能被其他表的约束所引用如果存在依赖该表的对象，则此表不能被删除CASCADE：删除该表没有限制。在删除基本表的同时，相关的依赖对象一起删除 危险 撤消基本表后，基本表的定义、表中数据、索引、以及由此表导出的视图的定义都被删除。,4.3.3 基本表的定义,示例1 删除S表 DROP TABLE Student CASCADE;基本表定义被删除，数据被删除表上建立的索引、视图、触发器等一般也将被删除 示例2 若表上建有视图，选择REST

13、RICT时表不能删除 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM S WHERE Sdept=IS；DROP TABLE Student RESTRICT;-ERROR:cannot drop table Student because other objects depend on it,4.3.3 基本表的定义,示例3 如果选择CASCADE时可以删除表，视图也自动被删除 DROP TABLE S CASCADE;-NOTICE:drop cascades to view IS_Student SELECT*FROM IS_St

14、udent;-ERROR:relation IS_Student does not exist,4.3.4 索引的定义,建立索引的目的：加快查询速度谁可以建立索引DBA 或 表的属主（即建立表的人）DBMS一般会自动建立以下列上的索引 PRIMARY KEY UNIQUE谁 维护索引 DBMS自动完成使用索引 DBMS自动选择是否使用索引以及使用哪些索引CREATE INDEX语句定义索引时，可以定义索引是唯一、非唯一或聚簇索引,索引的定义,索引的定义格式 create unique|distinct cluster index 索引名 on 表名(列名 asc|desc,列名asc|desc

15、)unique|distinct：唯一性索引，不允许表中不同的行在索引列上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引。系统并拒绝违背唯一性的插入、更新。cluster：聚集索引，表中元组按索引项的值排序并物理地聚集在一起。一个基本表上只能建一个聚集索引。asc|desc：索引表中索引值的排序次序，缺省为asc。,索引的定义,示例1:CREATE CLUSTER INDEX Stusname ON S(Sname)；-在S表的Sname（姓名）列上建立一个聚簇索引示例2 为学生-课程数据库中的S，Course，SC三个表建立索引。CREATE UNIQUE INDEX Stusn

16、o ON S(Sno)；CREATE UNIQUE INDEX Coucno ON Course(Cno)；CREATE UNIQUE INDEX SCno ON SC(Sno ASC，Cno DESC)；-S表按学号升序建唯一索引-Course表按课程号升序建唯一索引-SC表按学号升序和课程号降序建唯一索引,索引的定义,索引的删除格式：drop index 索引名删除索引时，系统会从数据字典中删去有关该索引的描述。示例1：删除S表的Stusname索引 DROP INDEX Stusname；,索引的定义,索引的有关说明可以动态地定义索引，即可以随时建立和删除索引。不允许用户在数据操作中引用

17、索引。索引如何使用完全由系统决定，这支持了数据的物理独立性。在最经常查询的列上建立聚簇索引以提高查询效率。经常更新的列不宜建立聚簇索引。应该在使用频率高的、经常用于连接的列上建索引。一个表上可建多个索引。索引可以提高查询效率，但索引过多耗费空间，且降低了插入、删除、更新的效率。,4.3.5 SQL数据定义特点,SQL中，任何时候都可以执行一个数据定义语句，随时修改数据库结构。数据库定义不断增长（不必一开始就定义完整）。数据库定义随时修改（不必一开始就完全合理）。可进行增加索引、撤消索引的实验，检验其对效率的影响。,4.4 SQL数据查询功能,4.4.1 SQL数据查询基本结构4.4.2 单表查

18、询4.4.3 连接查询4.4.4 嵌套查询4.4.5 集合查询4.4.6 Select语句的一般形式,4.4 SQL数据查询功能,语句格式 SELECT ALL|DISTINCT，FROM，WHERE GROUP BY HAVING ORDER BY ASC|DESC；,4.4.1 SQL数据查询基本结构,基本结构select A1,A2,Anfrom r1,r2,rmwhere P A1,A2,An(p(r1 r2 rm)示例给出所有老师的姓名。select PNAME from PROF,?,4.4.2 单表查询,查询仅涉及一个表：一、选择表中的若干列二、From子句三、选择表中的若干元组

19、四、ORDER BY子句五、聚集函数六、GROUP BY子句七、更名运算,4.4.2 单表查询-选择表中的若干列,目标列形式：可以为指定列名，所有属性列(*)，目标列表达式。查询指定列,例2 查询全体学生的姓名、学号、所在系代码。,例1 查询全体学生的学号与姓名。SELECT Sno，SnameFROM S；,SELECT Sname，Sno，DnoFROM S；,4.4.2 单表查询-选择表中的若干列,查询所有属性列：在SELECT关键字后面列出所有列名 将指定为*，表示“所有的属性”例3 查询全体学生的详细记录。SELECT Sno，Sname，sex，age，Dno FROM S；,或

20、SELECT*FROM S；两者完全等价。,4.4.2 单表查询-选择表中的若干列,例1查询全体学生的姓名、出生年份和所有地址，要求用小写字母表示所有地址SELECT Sname，Year of Birth:，2007-age，LOWER(adress)FROM S；-LOWER():为SQL SERVER函数输出结果：Sname 无列名 无列名 无列名 李勇 Year of Birth:1984 abc刘晨 Year of Birth:1985 bcv王敏 Year of Birth:1986 mmm张立 Year of Birth:1985 kkk,目标列表达式可以为：算术表达式(带，)字

21、符串常量函数列别名,使用更名运算old_name as new_name为关系和属性重新命名，可出现在select和from子句中。As 可省略。SELECT Sname，Year of Birth:as BIRTH，2007 age BIRTHDAY，LOWER(adress)AdressFROM S；-LOWER():为SQL SERVER函数输出结果：Sname BIRTH BIRTHDAY Adress李勇 Year of Birth:1984 abc刘晨 Year of Birth:1985 bcv王敏 Year of Birth:1986 mmm张立 Year of Birth:1

22、985 kkk,4.4.2 单表查询,查询仅涉及一个表：一、选择表中的若干列二、From子句三、选择表中的若干元组四、ORDER BY子句五、聚集函数六、GROUP BY子句七、更名运算,4.4.2 单表查询from子句,说明 from子句列出查询的对象表。当目标列取自多个表时，在不混淆的情况下可以不用显式指明来自哪个关系。示例例：找出工资低于500的职工的姓名、工资、系别。select PNAME,SAL,DNAME from PROF,DEPT where SAL 500 and PROF.DNO=DEPT.DNO,4.4.2 单表查询,查询仅涉及一个表：一、选择表中的若干列二、From子

23、句三、选择表中的若干元组四、ORDER BY子句五、聚集函数六、GROUP BY子句七、更名运算,4.4.2 单表查询-选择表中的若干元组,1.重复元组的处理 如果没有指定Distinct或Unique关键词，则缺省为ALL。例1 查询选修了课程的学生学号。SELECT Sno FROM SC；等价于：SELECT ALL Sno FROM SC；执行上面的SELECT语句后，结果为：Sno200215121200215121200215121200215122200215122,指定DISTINCT关键词，去掉表中重复的行。SELECT DISTINCT Sno FROM SC；执行结果：S

24、no 200215121 200215122,4.4.2 单表查询-选择表中的若干元组,2.查询满足条件的元组-where子句,表4.4 常用的查询条件,4.4.2 单表查询-选择表中的若干元组,.比较大小 比较运算符：=，=，!，!；NOT+上述比较运算符例1 查询计算机科学系全体学生的姓名。SELECT Sname FROM S WHERE Dno=CS；,例2 查询考试成绩有不及格的学生的学号。,SELECT DISTINCT SnoFROM SCWHERE Grade60；,4.4.2 单表查询-选择表中的若干元组,（2）.确定范围格式:BETWEEN AND NOT BETWEEN

25、AND 例1 查询年龄在2023岁(包括20岁和23岁)之间的学生的姓名、系别和年龄 SELECT Sname，Dno，age FROM SWHERE age BETWEEN 20 AND 23；,例2 查询年龄不在2023岁之间的学生姓名、系别和年龄,?,SELECT Sname，Dno，age FROM S WHERE age NOT BETWEEN 20 AND 23；,4.4.2 单表查询-选择表中的若干元组,(3).确定集合格式：表达式 IN,表达式 NOT IN 例1查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECT Sname，sexFROM

26、SWHERE Dno IN(IS，MA，CS);,例2查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。,?,SELECT Sname，SsexFROM Student WHERE Sdept NOT IN(IS，MA，CS),4.4.2 单表查询-选择表中的若干元组,（4）.字符串的处理格式：列名 NOT LIKE ESCAPE 匹配串为固定字符串例1 查询学号为200215121的学生的详细情况。SELECT*FROM S WHERE Sno LIKE 200215121；等价于：SELECT*FROM Student WHERE Sno=200215121；,4.4.2 单

27、表查询-选择表中的若干元组,2)匹配串为含通配符的字符串匹配规则：“%”：匹配零个或多个字符。“”：匹配任意单个字符。例2 查询所有姓刘学生的姓名、学号和性别。SELECT Sname，Sno，sex FROM S WHERE Sname LIKE 刘%；例3 查询姓“欧阳”且全名为 三个汉字的学生的姓名。SELECT Sname FROM SWHERE Sname LIKE 欧阳_ _,例4 查询名字中第2个字为“阳”字的学生的姓名和学号SELECT Sname，SnoFROM SWHERE Sname LIKE _ _阳%；例5 查询所有不姓刘的学生姓名。SELECT Sname，Sno，

28、sexFROM SWHERE Sname NOT LIKE 刘%；,4.4.2 单表查询-选择表中的若干元组,3)使用换码字符将通配符转义为普通字符 定义转义字符，以去掉特殊字符的特定含义，使其被作为普通字符看待。如escape“”，是定义了 作为转义字符，则可用%去匹配%，用去匹配，用 去匹配。ESCAPE 表示“”为换码字符 例6 查询DB_Design课程的课程号和学分。SELECT Cno，credit FROM Course WHERE Cname LIKE DB Design ESCAPE；例7 查询以DB_开头，且倒数第3个字符为 i的课程的详细情况。SELECT*FROM Co

29、urse WHERE Cname LIKE DB%I ESCAPE；,4.4.2 单表查询-选择表中的若干元组,（5）.空值的处理格式：表达式 IS NULL 或 表达式 IS NOT NULL 测试指定列的值是否为空值。“IS”不能用“=”代替 例1 某些学生选修课程后没有参加考试，所以有选课记录，但没 有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECT Sno，Cno FROM SC WHERE Grade IS NULL,例2 查所有有成绩的学生学号和课程号。,SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL；,4.4.2 单表查

30、询-选择表中的若干元组,注意事项除is not null之外，空值不满足任何查找条件。如果null参与算术运算，则该算术表达式的值为null。如果null参与比较运算，则结果可视为false。在SQL-92中可看成unknown。如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略null。例：select sum(SAL)from PROF 例：select count(*)from PROF,4.4.2 单表查询-选择表中的若干元组,（6）.多条件查询逻辑运算符：AND和 OR来联结多个查询条件 AND的优先级高于OR 可以用括号改变优先级可用来实现多种其他谓词 NOT I

31、N NOT BETWEEN AND 例1 查询计算机系年龄在20岁以下的学生姓名。SELECT Sname FROM S WHERE dno=CS AND Sage20；,4.4.2 单表查询-选择表中的若干元组,例2 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。,可改写为：SELECT Sname，sexFROM SWHERE dno=IS OR dno=MA OR dno=CS；,SELECT Sname，sexFROM SWHERE dno IN(IS，MA，CS),4.4.2 单表查询,查询仅涉及一个表：一、选择表中的若干列二、From子句三、选择表中的若

32、干元组四、ORDER BY子句五、聚集函数六、GROUP BY子句七、更名运算,4.4.2 单表查询-ORDER BY子句,格式：order by 列名 asc|descORDER BY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时（一般来说）ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示,例1 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECT Sno，Grade FROM SC WHERE Cno=3 ORDER BY Grade DESC；,4.4.2 单表查询-ORDER BY子句,例2 查询全

33、体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。,SELECT*FROM S ORDER BY dno，Sage DESC；,4.4.2 单表查询,查询仅涉及一个表：一、选择表中的若干列二、From子句三、选择表中的若干元组四、ORDER BY子句五、聚集函数六、GROUP BY子句七、更名运算,4.4.2 单表查询-聚集函数,聚集函数：计数COUNT（DISTINCT|ALL*）COUNT（DISTINCT|ALL）计算总和SUM（DISTINCT|ALL）计算平均值AVG（DISTINCT|ALL）最大最小值 MAX（DISTINCT|ALL）MIN（DISTIN

34、CT|ALL）,4.4.2 单表查询-聚集函数,例1 查询学生总人数。SELECT COUNT(*)FROM S；,例2 查询选修了课程的学生人数。,SELECT COUNT(DISTINCT Sno)FROM SC；,例3 计算1号课程的学生平均成绩。,SELECT AVG(Grade)FROM SC WHERE Cno=1；,4.4.2 单表查询-聚集函数,例4 查询选修1号课程的学生最高分数。,SELECT MAX(Grade)FROM SC WHER Cno=1；,例5查询学生200215012选修课程的总学分数。,SELECT SUM(credit)FROM SC，Course WH

35、ER Sno=200215012 AND SC.Cno=Course.Cno;,4.4.2 单表查询,查询仅涉及一个表：一、选择表中的若干列二、From子句三、选择表中的若干元组四、ORDER BY子句五、聚集函数六、GROUP BY子句七、更名运算,4.4.2 单表查询-GROUP BY子句,分组命令group by 列名 having 条件表达式 group by将表中的元组按指定一列或多列上的值相等的原则分组，然后在每一分组上使用聚集函数，得到单一值。having则对分组进行选择，只将聚集函数作用到满足条件的分组上。例1 求各个课程号及相应的选课人数。SELECT Cno，COUNT(S

36、no)FROM SC GROUP BY Cno；,查询结果：Cno COUNT(Sno)1 22 2 34 3 44 4 33 5 48,4.4.2 单表查询-GROUP BY子句,未对查询结果分组，聚集函数将作用于整个查询结果 对查询结果分组后，聚集函数将分别作用于每个组 作用对象是查询的中间结果表按指定的一列或多列值分组，值相等的为一组having 子句对 group by 子句所确定的行组进行控制,having 子句条件中只允许涉及常量,聚组函数或group by 子句中的列.,4.4.2 单表查询-GROUP BY子句,例2 列出所有老师的最高、最低、平均工资。select max(S

37、AL)，min(SAL)，avg(SAL)from PROF,例3 列出各系的老师的最高、最低、平均工资。select DNO，max(SAL)，min(SAL)，avg(SAL)from PROF group by DNO,4.4.2 单表查询-GROUP BY子句,例4 查询选修了3门以上课程的学生学号。,HAVING短语与WHERE子句的区别：作用对象不同WHERE子句作用于基表或视图，从中选择满足条件的元组HAVING短语作用于组，从中选择满足条件的组。,例6 列出所有同学的及格课程的平均成绩。,select SNO,avg(grade)from SC where grade=60 g

38、roup by SNO,例5 列出所有课程均及格的学生的平均成绩。,SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)3；,select SNO，avg(SCORE)from SC group by SNO having min(SCORE)=60,4.4.2 单表查询-GROUP BY子句,?求选修了课程的学生人数。select count(SNO)from SC select PNAME，max(SAL)from PROF select DNO，avg(SAL)from PROF group by DNO where AGE 60,4.4.2 单

39、表查询,查询仅涉及一个表：一、选择表中的若干列二、From子句三、选择表中的若干元组四、ORDER BY子句五、聚集函数六、GROUP BY子句七、更名运算,4.4.2 单表查询-更名运算,格式old_name as new_name为关系和属性重新命名，可出现在select和from子句中。示例属性更名 例：给出所有老师的姓名、所纳税额及税后工资额。select PNAME，SAL0.05 as taxi，SAL*0.95 as incomingfrom PROF,4.4.2 单表查询-更名运算,关系更名 找出工资比所在系主任工资高的老师姓名及工资。select P1.PNAME，P1.SA

40、Lfrom PROF as P1，PROF as P2，DEPT where P1.DNO=DEPT.DNOand DEPT.DEAN=P2.PNO and P1.SAL P2.SAL 注：as可选。,4.4.3 连接查询,一、等值与非等值连接查询 二、自身连接三、外连接四、复合条件连接,4.4.3 连接查询,连接查询：同时涉及多个表的查询。连接条件或连接谓词：用来连接两个表的条件一般格式：.BETWEEN.AND.连接字段：连接谓词中的列名称连接条件中的各连接字段类型必须是可比的，但名字不必是相同的,4.4.3 连接查询,4.4.3 连接查询,一、等值与非等值连接查询 二、自身连接三、外连接

41、四、复合条件连接,4.4.3 连接查询-等值与非等值连接查询,等值连接：连接运算符为=例1 查询每个学生及其选修课程的情况 SELECT S.*，SC.*FROM S，SCWHERE S.Sno=SC.Sno；,4.4.3 连接查询-等值与非等值连接查询,自然连接：例2 对例1用自然连接完成。SELECT S.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM S，SC WHERE S.Sno=SC.Sno；,4.4.3 连接查询,一、等值与非等值连接查询 二、自身连接三、外连接四、复合条件连接,4.4.3 连接查询-自身连接,自身连接：一个表与其自己进行连接需要

42、给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀例1查询每一门课的间接先修课（即先修课的先修课）SELECT FIRST.Cno，SECOND.CpnoFROM Course FIRST，Course SECONDWHERE FIRST.Cpno=SECOND.Cno；,FIRST表（Course表）,FIRST表（Course表）,SELECT FIRST.Cno，SECOND.CpnoFROM Course FIRST，Course SECONDWHERE FIRST.Cpno=SECOND.Cno；,结果为:,4.4.3 连接查询-自身连接,4.4.3 连接查询,一、等

43、值与非等值连接查询 二、自身连接三、外连接四、复合条件连接,4.4.3 连接查询外连接,例：列出老师的有关信息，包括姓名、工资、所教授的课程P#,PN,SAL,C#,CN(PROF)PC C),问题：有关P03号职工的姓名和工资信息没有显示出来,4.4.3 连接查询外连接,外连接为避免自然连接时因失配而发生的信息丢失，可以假定往参与连接的一方表中附加一个取值全为空值的行，它和参与连接的另一方表中的任何一个未匹配上的元组都能匹配，称之为外连接外连接=自然连接+失配的元组外连接的形式：左外连接、右外连接、全外连接 左外连接=自然连接+左侧表中失配的元组右外连接=自然连接+右侧表中失配的元组全外连接

44、=自然连接+两侧表中失配的元组,4.4.3 连接查询外连接,4.4.3 连接查询外连接,所有课程的信息,4.4.3 连接查询外连接,所有老师和课程的信息,4.4.3 连接查询外连接,外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出,例查询每个学生及其选修课程的情况SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student LEFT OUT JOIN SC ON(Student.Sno=SC.Sno),4.4.3 连接查询复合条件连接,复合条件连接：W

45、HERE子句中含多个连接条件例1 查询选修2号课程且成绩在90分以上的所有学生SELECT S.Sno,SnameFROM S,SCWHERE S.Sno=SC.Sno AND/*连接谓词*/SC.Cno=2 AND SC.Grade 90；/*其他限定条件*/,4.4.3 连接查询复合条件连接,例2查询每个学生的学号、姓名、选修的课程名及成绩,SELECT S.Sno，Sname，Cname，Grade FROM S，SC，Course/*多表连接*/WHERE S.Sno=SC.Sno and SC.Cno=Course.Cno；,4.4.4 嵌套查询,一、带有IN谓词的子查询 二、带有比

46、较运算符的子查询 三、带有ANY（SOME）或ALL谓词的子查询 四、带有EXISTS谓词的子查询,4.4.4 嵌套查询,嵌套查询概述一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。如下例:SELECT Sname FROM S WHERE Sno IN（SELECT Sno FROM SC WHERE Cno=2）；,子查询,父查询,说明:子查询的限制不能使用ORDER BY子句层层嵌套方式反映了 SQL语言的结构化有些嵌套查询可以用 连接运算替代,4.4.4 嵌套查询,嵌套查询求解方法不相关

47、子查询：子查询的查询条件不依赖于父查询 由里向外 逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。相关子查询：子查询的查询条件依赖于父查询首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表然后再取外层表的下一个元组重复这一过程，直至外层表全部检查完为止,4.4.4 嵌套查询-带有IN谓词的子查询,in 子查询-不相关子查询表达式 not in（子查询）判断表达式的值是否在子查询的结果中。例1：查询与“刘晨”在同一个系学习的学生。,4.4.4 嵌套查询-带有IN谓词的子查询,查询与“刘

48、晨”在同一个系学习的学生。,确定“刘晨”所在系名,查找所有在IS系学习的学生,4.4.4 嵌套查询-带有IN谓词的子查询,也可以用自身连接完成例1查询要求：SELECT S1.Sno，S1.Sname，S1.Sdept FROM S S1，S S2 WHERE S1.Sdept=S2.Sdept AND S2.Sname=刘晨；,4.4.4 嵌套查询-带有IN谓词的子查询,例2：查询选修了课程名为“信息系统”的学生学号和姓名。,SELECT Sno，Sname 最后在Student关系中 FROM S 取出Sno和Sname WHERE Sno IN(SELECT Sno 然后在SC关系中找出

49、选 FROM SC 修了3号课程的学生学号 WHERE Cno IN(SELECT Cno 首先在Course关系中找出 FROM Course“信息系统”的课程号，为3号 WHERE Cname=信息系统);,4.4.4 嵌套查询-带有IN谓词的子查询,也可以用连接查询实现例2 SELECT Sno，Sname FROM S，SC，Course WHERE S.Sno=SC.Sno AND SC.Cno=Course.Cno AND Course.Cname=信息系统；,4.4.4 嵌套查询-带有比较运算符的子查询,当能确切知道内层查询返回单值时，可用比较运算符（，=，），子查询一定要跟在比

50、较符之后。与ANY或ALL谓词配合使用假设一个学生只可能在一个系学习，并且必须属于一个系，在查询与“刘晨”在同一个系学习的学生时可以用=代替IN：SELECT Sno，Sname，Sdept FROM S WHERE Sdept=(SELECT Sdept FROM S WHERE Sname=刘晨)；,4.4.4 嵌套查询-带有比较运算符的子查询,例3：找出每个学生超过他选修课程平均成绩的课程号。SELECT Sno，Cno FROM SC x WHERE Grade=(SELECT AVG(Grade)FROM SC y WHERE y.Sno=x.Sno);,相关子查询,可能的执行过程：