关系数据库标准语言SQL.ppt

第三章 关系数据库标准语言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语言语法简单，接近英语口语，因此容易学习，容易使用。,3.1.2 SQL语言的基本概念,基本表是本身独立存在的表，在SQL中一个关系就对应一个表。一些基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。 存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理文件结构是任意的。 视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。 用户可以用SQL语言对视图和基本表进行查询。在用户眼中，视图和基本表都是关系，而存储文件对用户是透明的。,SQL对关系数据库模式的支持,SQL语言支持关系数据库三级模式结构。其中外模式对应于视图（View）和部分基本表（Base Table），模式对应于基本表，内模式对应于存储文件。 基本表是本身独立存在的表，在SQL中一个关系就对应一个表。一些基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。 存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理文件结构是任意的。 视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。 用户可以用SQL语言对视图和基本表进行查询。在用户眼中，视图和基本表都是关系，而存储文件对用户是透明的。,SQL对关系数据库模式的支持,基本表B1,视图V1,基本表B3,基本表B4,存储文件S1,存储文件S2,基本表B2,视图V2,SQL,外层/ 外模式,概念层/ 概念模式,内层/ 存储模式,注 意,本课程所讲授的SQL语言遵循SQL 89标准。 对于不同的数据系统，有不同的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子句的中使用比较运算符 =，=，!，!= 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 Sage20；,查询示例,例31 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。 SELECT Sname，Ssex FROM Student WHERE Sdept IN ( IS，MA，CS ) 可改写为： SELECT Sname，Ssex FROM Student WHERE Sdept= IS OR Sdept= MA OR Sdept= CS ；,查询示例,例32 查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。 SELECT Sname，Sdept，Sage FROM Student WHERE Sage BETWEEN 20 AND 23； 可改写为： SELECT Sname，Sdept，Sage FROM Student WHERE 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；,等值与非等值连接查询（续）,查询结果：,等值与非等值连接查询（续）,自然连接： 例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表）,自身连接（续）,SECOND表（Course表）,自身连接（续）,查询结果：,连接查询（续）,一、等值与非等值连接查询 二、自身连接 三、外连接 四、复合条件连接,三、外连接,外连接与普通连接的区别 普通连接操作只输出满足连接条件的元组 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出 例45 改写上例 SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student LEFT OUT JOIN SC ON (Student.Sno=SC.Sno)；,外连接（续）,执行结果：,外连接（续）,左外连接 列出左边关系（如本例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 ； 结果为：,带有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谓词的子查询,二、带有比较运算符的子查询,当能确切知道内层查询返回单值时，可用比较运算符（，=，）。 与ANY或ALL谓词配合使用,带有比较运算符的子查询（续）,例51假设一个学生只可能在一个系学习，并且必须属于一个系，则在例39可以用 = 代替IN ： SELECT Sno，Sname，Sdept FROM Student WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname= 刘晨)；,带有比较运算符的子查询（续）,子查询一定要跟在比较符之后 错误的例子： SELECT Sno，Sname，Sdept FROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname= 刘晨 ) = Sdept；,带有比较运算符的子查询（续）,例52找出每个学生超过他选修课程平均成绩的课程号。 SELECT Sno， Cno FROM SC x WHERE Grade =(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大于等于子查询结果中的某个值 = ALL大于等于子查询结果中的所有值 ）ANY不等于子查询结果中的某个值 !=（或）ALL不等于子查询结果中的任何一个值,带有ANY（SOME）或ALL谓词的子查询 （续）,例53 查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄 SELECT Sname，Sage FROM Student WHERE Sage CS ; /*父查询块中的条件 */,带有ANY（SOME）或ALL谓词的子查询 （续）,结果： 执行过程： 1.RDBMS执行此查询时，首先处理子查询，找出 CS系中所有学生的年龄，构成一个集合(20，19) 2. 处理父查询，找所有不是CS系且年龄小于 20 或 19的学生,带有ANY（SOME）或ALL谓词的子查询 （续）,用聚集函数实现 SELECT Sname，Sage FROM Student WHERE Sage CS ;,带有ANY（SOME）或ALL谓词的子查询 （续）,例54 查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。 方法一：用ALL谓词 SELECT Sname，Sage FROM Student WHERE Sage CS ;,带有ANY（SOME）或ALL谓词的子查询 （续）,方法二：用聚集函数 SELECT Sname，Sage FROM Student WHERE Sage CS ;,带有ANY（SOME）或ALL谓词的子查询 （续）,表 ANY（或SOME），ALL谓词与聚集函数、IN谓词的等价转换关系,嵌套查询,一、带有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 Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno= 1;,带有EXISTS谓词的子查询(续）,例56 查询没有选修1号课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=1)；,带有EXISTS谓词的子查询(续）,不同形式的查询间的替换 一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换 用EXISTS/NOT EXISTS实现全称量词(难点) SQL语言中没有全称量词 （For all） 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词： (x)P ( x( P),带有EXISTS谓词的子查询(续）,例57：查询与“刘晨”在同一个系学习的学生。 可以用带EXISTS谓词的子查询替换： SELECT Sno，Sname，Sdept FROM Student S1 WHERE EXISTS (SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = 刘晨)；,带有EXISTS谓词的子查询(续）,例57 查询选修了全部课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno ） ）；,带有EXISTS谓词的子查询(续）,用EXISTS/NOT EXISTS实现逻辑蕴函(难点) SQL语言中没有蕴函(Implication)逻辑运算 可以利用谓词演算将逻辑蕴函谓词等价转换为： p q pq,带有EXISTS谓词的子查询(续）,例58查询至少选修了学生200215122选修的全部课程的学生号码。 解题思路： 用逻辑蕴函表达：查询学号为x的学生，对所有的课程y，只要200215122学生选修了课程y，则x也选修了y。 形式化表示： 用P表示谓词 “学生200215122选修了课程y” 用q表示谓词 “学生x选修了课程y” 则上述查询为: (y) p q,带有EXISTS谓词的子查询(续）,等价变换： (y)p q (y (p q ) (y ( p q) ) y(pq) 变换后语义：不存在这样的课程y，学生200215122选修了y，而学生x没有选。,带有EXISTS谓词的子查询(续）,用NOT EXISTS谓词表示： SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno = 200215122 AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)；,集合查询,集合操作的种类 并操作UNION 交操作INTERSECT 差操作EXCEPT 参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同,集合查询（续）,例60 查询计算机科学系的学生及年龄不大于19岁的学生。 方法一： SELECT * FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WHERE Sage=19； UNION：将多个查询结果合并起来时，系统自动去掉重复元组。 UNION ALL：将多个查询结果合并起来时，保留重复元组,集合查询（续）,方法二： SELECT DISTINCT * FROM Student WHERE Sdept= CS OR Sage=19；,集合查询（续）,例61 查询选修了课程1或者选修了课程2的学生。 SELECT Sno FROM SC WHERE Cno= 1 UNION SELECT Sno FROM SC WHERE Cno= 2 ；,集合查询（续）,例62 查询计算机科学系的学生与年龄不大于19岁的学生的交集 SELECT * FROM Student WHERE Sdept=CS INTERSECT SELECT * FROM Student WHERE Sage=19,集合查询（续）,例63 实际上就是查询计算机科学系中年龄不大于19岁的学生 SELECT * FROM Student WHERE Sdept= CS AND Sage=19；,集合查询（续）,例64 查询选修课程1的学生集合与选修课程2的学生集合的交集 SELECT Sno FROM SC WHERE Cno= 1 INTERSECT SELECT Sno FROM SC WHERE Cno=2 ；,集合查询（续）,例65实际上是查询既选修了课程1又选修了课程2的学生 SELECT Sno FROM SC WHERE Cno= 1 AND Sno IN (SELECT Sno FROM SC WHERE Cno= 2 )；,集合查询（续）,例66 查询计算机科学系的学生与年龄不大于19岁的学生的差集。 SELECT * FROM Student WHERE Sdept=CS EXCEPT SELECT * FROM Student WHERE Sage =19;,集合查询（续）,例67实际上是查询计算机科学系中年龄大于19岁的学生 SELECT * FROM Student WHERE Sdept= CS AND Sage19；,SELECT语句的一般格式,SELECT ALL|DISTINCT 别名 ， 别名 FROM 别名 ， 别名 WHERE GROUP BY HAVING ORDER BY ASC|DESC,数 据 更 新,插入数据 修改数据 删除数据,插入数据,两种插入数据方式 1. 插入元组 2. 插入子查询结果 可以一次插入多个元组,一、插入元组,语句格式 INSERT INTO (，) VALUES ( ， ) 功能 将新元组插入指定表中,INTO子句 属性列的顺序可与表定义中的顺序不一致 没有指定属性列 指定部分属性列 VALUES子句 提供的值必须与INTO子句匹配 值的个数 值的类型,例1 将一个新学生元组（学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。 INSERT INTO Student (Sno，Sname，Ssex，Sdept，Sage) VALUES (200215128，陈冬，男，IS，18)；,例2 将学生张三丰的信息插入到Student表中。 INSERT INTO Student VALUES (200215126， 张三丰， 男，18，CS);,例3 插入一条选课记录( 200215128，1 )。 INSERT INTO SC(Sno，Cno) VALUES ( 200215128 ， 1 )； RDBMS将在新插入记录的Grade列上自动地赋空值。 或者： INSERT INTO SC VALUES ( 200215128 ， 1 ，NULL)；,二、插入子查询结果,语句格式 INSERT INTO ( ， ) 子查询； 功能 将子查询结果插入指定表中,INTO子句(与插入元组类似) 子查询 SELECT子句目标列必须与INTO子句匹配 值的个数 值的类型,例4 对每一个系，求学生的平均年龄，并把结果存入数据库。 第一步：建表 CREATE TABLE Dept_age (Sdept CHAR(15) /* 系名*/ Avg_age SMALLINT)； /*学生平均年龄*/,第二步：插入数据 INSERT INTO Dept_age(Sdept，Avg_age) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；,RDBMS在执行插入语句时会检查所插元组是 否破坏表上已定义的完整性规则 实体完整性 参照完整性 用户定义的完整性 NOT NULL约束 UNIQUE约束 值域约束,修改数据,语句格式 UPDATE SET =，= WHERE ； 功能 修改指定表中满足WHERE 子句条件的元组,SET子句 指定修改方式 要修改的列 修改后取值 WHERE子句 指定要修改的元组 缺省表示要修改表中的所有元组,修改数据（续）,三种修改方式 1. 修改某一个元组的值 2. 修改多个元组的值 3. 带子查询的修改语句,1. 修改某一个元组的值,例5 将学生200215121的年龄改为22岁 UPDATE Student SET Sage=22 WHERE Sno= 200215121 ；,2. 修改多个元组的值,例6 将所有学生的年龄增加1岁 UPDATE Student SET Sage= Sage+1；,3. 带子查询的修改语句,例7 将计算机科学系全体学生的成绩置零。