数据库原理-3关系数据库标准语言SQL.ppt

数据库系统原理(第3章),四川大学计算机学院 张天庆 2005.9,第三章 关系数据库标准语言SQL,SQL是Structured Query Language的缩写 最新的标准是SQL99，或称SQL3 包含了很多核心SQL以外的包 增加对递归查询、临时视图的支持 增加对触发器的支持 现在商品化DBMS一般都支持SQL92规范的超集/子集。,3.1 SQL概述,SQL介于关系代数与关系演算之间 3.1.1 SQL的特点 SQL集数据查询、数据操纵、数据定义和数据控制功能于一体。 其综合、强大、简洁使其称为国际标准。,综合统一 SQL集DDL、DML、DCL功能于一体。 SQL数据操作符的统一。(查询、插入、删除、修改都只有一种操作符。),二. 高度非过程化 SQL进行数据操作，只要提出“What to do”，无需告诉系统“How to do” 充分体现关系系统的特点和优势 有利于提高数据的独立性,三. 面向集合的操作方式 关系运算“一次一集合”方式的体现。,四. 一种语法结构、两种使用方式 既是自含式语言，又是嵌入式语言。,五. 语言简捷，易学易用 SQL设计巧妙，核心功能只需9个动词。在语言上接近英语。,3.1.2 SQL语言的基本概念 SQL对象与三级模式结构的对应关系 外模式视图、部分基本表 模式 基本表 内模式存储文件,基本表是本身独立存在的表，在SQL中一个关系就对应一个表。 一些DBMS中一个基本表对应一个存储文件，有的DBMS一个数据库中的多个基本表对应一个存储文件 一个表可以带若干索引，索引也存放在存储文件中,存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理文件结构是任意的。,视图是从基本表或其他视图中导出的表 它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。 用户可以用SQL语言对视图和基本表进行查询。在用户眼中，视图和基本表都是关系，而存储文件对用户是透明的。,3.2 数据定义,对基本表、视图、索引的定义、删除和修改(7种组合，不能修改视图和索引),3.2.1 定义、删除和修改基本表,定义基本表 一般格式 CREATE TABLE (列级完整性约束条件 ,列级完整性约束条件 . ,表级完整性约束条件);,例1：书p69给出了IBM DB2定义学生表的方法。 下面的例子一般为标准SQL。 CREATE TABLE student( sno char(5) PRIMARY KEY, sname varchar(20) UNIQUE, sgender char(1), sage int, sdept varchar(15) );,略去course表，下面定义SC表 CREATE TABLE SC( sno char(5), cno char(5), grade int, PRIMARY KEY (sno,cno), FOREIGN KEY (sno) REFERENCES student, FOREIGN KEY (cno) REFERENCES course );,约束更完整的形式： CREATE TABLE SC( sno char(5), cno char(5), grade int, CONSTRAINT PK_sno_cno PRIMARY KEY (sno,cno), CONSTRAINT FK_sno FOREIGN KEY (sno) REFERENCES student, CONSTRAINT FK_cno FOREIGN KEY (cno) REFERENCES course， CONSTRAINT CK_grade CHECK (grade=0 and grade =100) );,上述例子可以看出SQL基本表定义的概貌。 应注意完整性约束的定义 主码：PRIMARY KEY关键字。 外码：FOREIGN KEY关键字。 用户定义：CHECK关键字。,二. 修改基本表 可增加新列，删除列上的完整性约束，修改列名及数据类型 但不能删除属性列！ ALTER TABLE ADD 完整性约束 DROP MODIFY ;,三. 删除基本表 DROP TABLE ; 删除基本表后，引用建立在此表上的视图可能出现问题。,3.2.2 建立和删除索引,作用：提高查询速度。 如从O(n)到O(log2n) 常需要建立索引的属性 常作为连接属性 常出现在查询条件中,一.建立索引 CREATE UNIQUECLUSTERED|NONCLUSTERED INDEX ON ( ,);,UNIQUE(单一索引)： 唯一索引，不允许存在索引值相同的两行,CLUSTERED(聚集索引)：索引项的顺序与表中记录的物理顺序一致。表中如果有多个记录在索引字段上相同，这些记录构成一簇，只有一个索引值。 优点：查询速度快。 缺点：维护成本高，且一个表只能建一个聚簇索引。,NONCLUSTERED(非聚集索引) 作为非聚集索引，行的物理排序独立于索引排序 非聚集索引的叶级包含索引行（B树）,二. 删除索引 DROP INDEX ;,3.3 SQL查询,要求重点掌握 比较困难的是2-3层的嵌套查询。 一般格式： SELECT ALL|DISTINCT,目标列表达式 FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESC;,简单的SQL查询与关系代数的联系： SELECT A1,A2,An FROM T1,T2,Tk WHERE F; 相当于： A1,A2,An F(T1T2 Tk) 其中F中有的可能是连接条件，与后面的广义笛卡儿集构成连接。,含义:根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。如果有GROUP子句，则将结果按的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常会在每组中作用集函数。如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。如果有ORDER子句，则结果表还要按的值的升序或降序排序。,3.3.1 单表查询 比较简单 一、选择表中的若干列 1.查询指定列 例1查询全体学生的学号与姓名。 SELECT Sno,Sname FROM Student; 中各个列的先后顺序可以与表中的顺序不一致。也就是说，用户在查询时可以根据应用的需要改变列的显示顺序。,2.查询全部列 例3 查询全体学生的详细记录 SELECT * FROM Student; 该SELECT语句实际上是无条件地把Student表的全部信息都查询出来，所以也称为全表查询，这是最简单的一种查询。,不仅可以使用列名，还可以使用算术表达式、字符串常量和函数等。 3. 查询经过计算的值 例5 查全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名 SELECT Sname, Year of Birth:, 2004-Sage, LOWER(Sdept) FROM Student;,改变列标题（MS SQL SERVRE)用AS SELECT Sname AS Name, Year of Birth: AS Birth, 2004-Sage AS BirthYear, LOWER(Sdept) AS Department FROM Student;,二、选择表中的若干元组 1.消除取值重复的行(使用DISTINCT) 例6 查所有选修过课的学生的学号 SELECT Sno FROM SC; 结果中有重复的行。 一个学生选多少门课，其学号就出现多少次。,DISTINCT用于消重 SELECT DISTINCT Sno FROM SC; 即可消重。,2. 查询满足条件的元组 查询满足指定条件的元组可以通过WHERE子句实现。WHERE子句常用的查询条件如表3-3所示。,比较大小 例7 查询计算机系全体学生的名单。 SELECT Sname FROM Student WHERE Sdept = CS;,补充例：查询选修2号课程成绩在90分以上(含)的学生的学号。 SELECT Sno FROM SC WHERE Cno = 2 AND Grade=90;,(2) 确定范围 BETWEEN AND 与NOT BETWEEN AND 更接近自然语言，语义更明确。 但都可以表示为用逻辑与连接的两个比较。,例10 查询年龄在2023岁（包括）之间的学生的姓名、系别和年龄。 SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23;,当然也可表示为： SELECT Sname, Sdept, Sage FROM Student WHERE Sage=20 AND Sage =23;,(3) 确定集合 IN与NOT IN 前面的例子还可表示为： SELECT Sname, Sdept, Sage FROM Student WHERE Sage IN (20,21,22,23);,(4)字符匹配 LIKE 通配符%和_。 用法：相当于Windows、dos等中查文件名的*和？ 新的问题：如何表示%和_本身？,表示%和_本身的方法 ESCAPE转义 书上用转义。实际上可以更灵活。如下面的！转义： SELECT c1 FROM tb WHERE c1 LIKE %10-15!% off% ESCAPE ! ;,(5)涉及空值的查询 IS NULL和IS NOT NULL (注意：有的系统允许NULL比较，可以使用和，如MS SQL Server。有的不允许，如Oracle。) 例21 查询缺少成绩的学生的学号及相应课程号。,SELECT Sno,Cno FROM SC WHERE Grade IS NULL;,(6) 逻辑连接查询 WHERE中用AND和OR连接多个条件。 例：查询计算机科学系或数学系年龄在18到23岁的学生的姓名。 SELECT Sname FROM Student WHERE (Sdept=CS OR Sdept=MA) and Sage BETWEEN 18 AND 23;,三. 查询结果排序 如果没有指定查询结果的显示顺序，DBMS将按其最方便的顺序（通常是元组在表中的先后顺序）输出查询结果。 ORDER BY 子句可明确指定结果序。 默认为升序(ASC)，NULL值最大。,例：查询计算机系(CS)学生的学号和姓名，按年龄从大到小排，相同年龄的按学号升序排。 SELECT Sno, Sname FROM Student WHERE Sdept=CS ORDER BY Sage DESC, Sno;,注：这里说“NULL值最大”，仅仅针对NULL值排序的情况。 如果取“NULL值”的字段出现在条件表达式中，将使条件计算为NULL，进而被排除于结果外。 例如，查找成绩在90分以上(含)的学生的学号： SELECT Sno FROM SC WHERE Grade=90； 成绩为NULL的学生的学号并不出现在结果中。,四、使用集函数 为了进一步方便用户，增强检索功能，SQL提供了许多集函数，主要包括： COUNT(DISTINCT|ALL *) 统计元组个数 COUNT(DISTINCT|ALL ) 统计一列中值的个数 SUM(DISTINCT|ALL ) 计算一列值的总和（此列必须是数值型） AVG(DISTINCT|ALL ) 计算一列值的平均值（此列必须是数值型） MAX(DISTINCT|ALL ) 求一列值中的最大值 MIN(DISTINCT|ALL ) 求一列值中的最小值,如果指定DISTINCT短语，则表示在计算时要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语（ALL为缺省值），则表示不取消重复值。 例：查询学生总人数 SELECT COUNT (*) FROM student; 或 SELECT COUNT (Sno) FROM student;,例26 查询选修了课程的学生人数 SELECT COUNT(DISTINCT Sno) FROM SC; 学生每选修一门课，在SC中都有一条相应的记录，而一个学生一般都要选修多门课程，为避免重复计算学生人数，必须在COUNT函数中用DISTINCT短语。,注：NULL值的影响： COUNT(*)总是返回记录的个数 COUNT(字段)返回指定字段值非空的记录个数。 例如，求2号课程总共有多少个得分： SELECT COUNT(*) FROM SC WHERE Cno2； 结果中包含成绩为空的记录，而,SELECT COUNT(Grade) FROM SC WHERE Cno2； 结果中则不包含成绩为空的记录。,五、对查询结果分组 GROUP BY子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组。 对查询结果分组的目的是为了细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。,注：SQL规定，所有带有NULL值的记录在分组时被作为一组。 例子，对选2号课程的学生进行成绩分组，计算每一成绩的人数。 SELECT COUNT(*),Grade FROM SC WHERE Cno=2 GROUP BY Grade;,注：分组后，一些详细信息可能损失，不能出现在SELECT结果中。 例如，下面的查询 SELECT Sno,COUNT(*),Grade FROM SC WHERE Cno=2 GROUP BY Grade; 将出错，想一想，为什么？,一般来说，分组查询的SELECT目标列中只允许出现聚集函数和GROUP BY子句中出现过的列。,HAVING条件针对的是 GROUP分组，WHERE针对的是元组，两者并不矛盾。,HAVING条件针对的是 GROUP分组，WHERE针对的是元组，两者并不矛盾。 例：查询有三科或三科以上成绩在80分以上的学生学号。 SELECT Sno FROM SC WHERE Grade=80 GROUP BY Sno HAVING (COUNT(Cno)=3);,3.3.2 连接查询,一个数据库中的多个表之间一般都存在某种内在联系，它们共同提供有用的信息。前面的查询都是针对一个表进行的。若一个查询同时涉及两个以上的表，则称之为连接查询。连接查询主要包括等值连接、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询。,一、等值与非等值连接查询 用来连接两个表的条件称为连接条件或连接谓词，其一般格式为： . . 在实践中比较运算符为的等值连接使用十分广泛。,连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但不必是相同的。例如，可以都是字符型，或都是日期型；也可以一个是整型，另一个是实型，整型和实型都是数值型，因此是可比的。但若一个是字符型，另一个是整数型就不允许了，因为它们是不可比的类型。,从概念上讲DBMS执行连接操作的过程是，首先在表1中找到第一个元组，然后从头开始顺序扫描或按索引扫描表2，查找满足连接条件的元组，每找到一个元组, 就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部扫描完毕后，再到表1中找第二个元组，然后再从头开始顺序扫描或按索引扫描表2，查找满足连接条件的元组，每找到一个元组, 就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1全部元组都处理完毕为止。(注意：实际上要考虑查询的优化),例32 查询每个学生及其选修课程的情况 学生情况存放在Student表中，学生选课情况存放在SC表中，所以本查询要把Student与SC表的数据通过两个表都具有的属性Sno (外码连接)实现的。这是一个等值连接。完成本查询的SQL语句为： SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno=SC.Sno;,例33自然连接Student和SC表 SELECT Student.Sno, Sname, Sgender, Sage, Sdept, Cno, Grade FROM Student, SC WHERE Student.Sno=SC.Sno;,注：实际上，上述作法只是基本连接形式之一，称为“交叉连接”或“叉积连接”。 除交叉连接外，还有“内连接”和“外连接”两种基本形式。,例如，例32可改为“内连接”形式（以MS SQL Server为例）： SELECT * FROM Student INNER JOIN SC ON Student.Sno=SC.Sno;,二、自身连接 连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，这种连接称为表的自身连接。 例34查询每一门课的间接先修课（即先修课的先修课） 我们先来分析一下，题目要求查询每一门课程的先修课的先修课，在“课程”表即Course关系中，只有每门课的直接先修课信息，而没有先修课的先修课，要得到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号，查找它的先修课程。,我们可以为Course表取两个别名，一个是FIRST，另一个是SECOND，也可以在考虑问题时就把Course表想成是两个完全一样表，一个是FIRST表，另一个是SECOND表。这两个表通过FIRST的Pcno与SECOND的Cno等值连接即可达到查询目的。这相当于将Course表与其自身连接后，取第一个副本的课程号与第二个副本的先修课号做为目标列中的属性。,书上的作法是： SELECT FIRST.Cno, SECOND. Pcno FROM Course FIRST, Course SECOND WHERE FIRST.Pcno=SECOND.Cno;,书上的作法是： SELECT FIRST.Cno, SECOND. Pcno FROM Course FIRST, Course SECOND WHERE FIRST.Pcno=SECOND.Cno; 这一作法实际上有问题：没有先行课的先行课的课程（即某门课的先行课的先行课为NULL）也出现在结果中。 请思考后，自己修改正确。,三、外连接 在通常的连接操作中，只有满足连接条件的元组才能作为结果输出，如在例32和例33的结果表中没有关于95003和95004两个学生的信息，原因在于他们没有选课，在SC表中没有相应的元组。但是有时我们想以Student表为主体列出每个学生的基本情况及其选课情况，若某个学生没有选课，则只输出其基本情况信息，其选课信息为空值即可，这时就需要使用外连接（Outer Join）。,例33的交叉连接解法。 SELECT Student.Sno, Sname, Sgender, Sage, Sdept, Cno, Grade FROM Student, SC WHERE Student.Sno=SC.Sno(*);,这一作法MS SQL Server不支持。 MS SQL Server采用外连接形式： SELECT * FROM Student LEFT JOIN SC ON Student.Sno=SC.Sno;,书上认为：这是右外连接。有分歧。 实际上，SQL对左、右、全外连接的定义是： 左向外联接的结果集返回左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。 右向外联接返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。 全外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。,复杂例：对上例修改，再外连接Course SELECT * FROM Student LEFT JOIN SC ON Student.Sno=SC.Sno FULL JOIN Course ON SC.Cno=Course.Cno;,四、复合条件连接 上面各个连接查询中，WHERE子句中只有一个条件，即用于连接两个表的谓词。WHERE子句中有多个条件的连接操作，称为复合条件连接。 例 查询选修2号课程且成绩在90分以上的信息系或计算机系学生的学号和姓名。 SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND (Student.Sdept=IS OR Student.Sdept=CS) AND SC.Cno=2 AND SC.Grade90;,3. 嵌套查询,在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询或子查询。例如： SELECT Sname FROM Student WHERE Sno IN SELECT Sno FROM SC WHERE Cno=2;,嵌套查询的求解方法是由里向外处理。即每个子查询在其上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。 嵌套查询使得可以用一系列简单查询构成复杂的查询，从而明显地增强了SQL的查询能力。以层层嵌套的方式来构造程序正是 SQL(Structurred Query Language)中“结构化”的含义所在。,一、带有IN谓词的子查询 带有IN谓词的子查询是指父查询与子查询之间用IN进行连接，判断某个属性列值是否在子查询的结果中。由于在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词。 例37 查询与“刘晨”在同一个系学习的学生,查询与“刘晨”在同一个系学习的学生，可以首先确定“刘晨”所在系名，然后再查找所有在该系学习的学生。所以可以分步来完成此查询： 确定“刘晨”所在系名 SELECT Sdept FROM Student WHERE Sname=刘晨; 结果为： Sdept - IS,查找所有在IS系学习的学生。 SELECT Sno, Sname, Sdept FROM Student WHERE Sdept=IS; 结果为： Sno Sname Sdept - - - 95001 刘晨 IS 95004 张立 IS,可表为嵌套查询的形式： SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname=刘晨); 本例中的查询也可以用我们前面学过的表的自身连接查询来完成。,例38 查询选修了课程名为信息系统的学生学号和姓名。 SELECT Sno, Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname=信息系统)；,例37和例38中的各个子查询都只执行一次，其结果用于父查询，子查询的查询条件不依赖于父查询，这类子查询称为不相关子查询。不相关子查询是最简单的一类子查询。,二、带有比较运算符的子查询 带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用、 =、等比较运算符。,（按书上的说法）在例37中，由于一个学生只可能在一个系学习，也就是说内查询刘晨所在系的结果是一个唯一值，因此该查询也可以用比较运算符来实现，其SQL语句如下： SELECT Sno, Sname, Sdept FROM Student WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname=刘晨);,这一作法可能有问题：如果两个或以上系都有叫做“刘晨”的学生，查询将出错。 换一个例子：查询选修2号课程的成绩最高的学生的学号。,SELECT Sno FROM SC WHERE Cno=2 AND Grade = ( SELECT MAX(Grade) FROM SC WHERE Cno=2);,需要注意的是，子查询一定要跟在比较符之后，下列写法是错误的： SELECT Sno, Sname, Sdept FROM Student WHERE (SELECT Sdept FROM Student WHERE Sname=刘晨) = Sdept;,例38中信息系统的课程号是唯一的，但选修该课程的学生并不只一个，所以例38也可以用=运算符和IN谓词共同完成： SELECT Sno, Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno = ( SELECT Cno FROM Course WHERE Cname=信息系统) ；,三、带有ANY或ALL谓词的子查询 子查询返回单值时可以用比较运算符。而使用ANY或ALL谓词时则必须同时使用比较运算符。其语义为： ANY 大于子查询结果中的某个值 = ANY 大于等于子查询结果中的某个值 ANY 不等于子查询结果中的某个值, ALL 大于子查询结果中的所有值 = ALL 大于等于子查询结果中的所有值 ALL 不等于子查询结果中的任何一个值,例39 查询其他系中比IS系任一学生年龄小的学生名单 SELECT Sname, Sage FROM Student WHERE Sage IS ORDER BY Sage DESC;,事实上，用集函数实现子查询通常比直接用ANY或ALL查询效率要高。ANY与ALL与集函数的对应关系如表3-4所示。 四. 带有EXISTS谓词的子查询 EXISTS代表存在量词彐。带有EXISTS谓词的子查询不返回任何实际数据，它只产生逻辑真值“true“或逻辑假值“false“。,例40 查询所有选修了1号课程的学生姓名 查询所有选修了1号课程的学生姓名涉及Student关系和SC关系，我们可以在Student关系中依次取每个元组的Sno值，用此Student.Sno值去检查SC关系，若SC中存在这样的元组，其SC.Sno值等于用来检查的Student.Sno值，并且其SC.Cno=1，则取此Student.Sname送入结果关系。将此想法写成SQL语句就是： SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=1);,使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。 由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名亦无实际意义。 这类查询与我们前面的不相关子查询有一个明显区别，即子查询的查询条件依赖于外层父查询的某个属性值（在本例中是依赖于Student表的Sno值），我们称这类查询为相关子查询.,（Correlated Subquery）。求解相关子查询不能象求解不相关子查询那样，一次将子查询求解出来，然后求解父查询。相关子查询的内层查询由于与外层查询有关，因此必须反复求值。从概念上讲，相关子查询的一般处理过程是： 首先取外层查询中Student表的第一个元组，根据它与内层查询相关的属性值（即Sno值）处理内层查询，若WHERE子句返回值为真（即内层查询结果非空），则取此元组放入结果表；然后再检查Student表的下一个元组；重复这一过程，直至Student表全部检查完毕为止。,本例中的查询也可以用连接运算来实现，读者可以参照有关的例子，自己给出相应的SQL语句。 与EXISTS谓词相对应的是NOT EXISTS谓词。使用存在量词NOT EXISTS后，若内层查询结果为空，则外层的WHERE子句返回真值，否则返回假值。,例41 查询所有未修1号课程的学生姓名 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=1); (要求的学生没有选1号课程),一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换，但所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。例如带有IN谓词的例37可以用如下带EXISTS谓词的子查询替换： SELECT Sno, Sname, Sdept FROM Student S1 WHERE EXISTS (SELECT * FROM Student S2 WHERE S2.Sdept=S1.Sdept AND S2.Sname=刘晨);,由于带EXISTS量词的相关子查询只关心内层查询是否有返回值，并不需要查具体值，因此其效率并不一定低于不相关子查询，甚至有时是最高效的方法。 SQL语言中没有全称量词（For all）。因此必须利用谓词演算将一个带有全称量词的谓词转换为等价的带有存在量词的谓词。,例42 查询选修了全部课程的学生姓名 由于没有全称量词，我们将题目的意思转换成等价的存在量词的形式：查询这样的学生姓名，没有一门课程是他不选的。该查询涉及三个关系，存放学生姓名的Student表，存放所有课程信息的Course表，存放学生选课信息的SC表。其SQL语句为：,SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Student.Sno=SC.Sno AND Course.Cno=Cno);,方法2：用集函数 SELECT Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) = (SELECT COUNT(*) FROM Course);,思考： SELECT Sname FROM Student,SC WHERE Student.Sno=SC.Sno GROUP BY SC.Sno HAVING COUNT(*) = (SELECT COUNT(*) FROM Course); 错在哪里？,SQL语言中也没有蕴函（Implication）逻辑运算。因此也必须利用谓词演算将一个逻辑蕴函的谓词转换为等价的带有存在量词的谓词： 例43 查询至少选修了学生95002选修的全部课程的学生号码。,本题的查询要求可以做如下解释，查询这样的学生，凡是95002选修的课，他都选修了。换句话说，若有一个学号为x的学生，对所有的课程y，只要学号为95002的学生选修了课程y，则x也选修了y；那么就将他的学号选出来。它所表达的语义为：不存在这样的课程y，学生95002选修了y，而学生x没有选。用SQL语言可表示如下：,SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno=95002 AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno);,3.4 数据更新,3.4.1 插入数据 一、插入单个元组 插入单个元组的INSERT语句的格式为： INSERT INTO (,.) VALUES ( ,.),如果某些属性列在INTO子句中没有出现，则新记录在这些列上将取空值。但必须注意的是，在表定义时说明了NOT NULL的属性列不能取空值。 如果INTO子句中没有指明任何列名，则新插入的记录必须在每个属性列上均有值。,例 插入一条选课记录(95020，1) INSERT INTO SC(Sno, Cno) VALUES (95020, 1);,很多系统允许一次插入多条记录，如 INSERT INTO SC(Sno, Cno) VALUES (95003 , 1),(95003 , 2); 新插入的记录在Grade列上取空值。 一般DBMS不支持此方式。,二、插入子查询结果 子查询不仅可以嵌套在SELECT语句中，用以构造父查询的条件，也可以嵌套在INSERT语句中，用以生成要插入的数据。 插入子查询结果的INSERT语句的格式为： INSERT INTO ( ,.) 子查询； 其功能是以批量插入，一次将子查询的结果全部插入指定表中。,例 对每一个系，求学生人数及平均年龄，并把结果存入数据库。 对于这道题，首先要在数据库中建立一个新表，其中一列存放系名，一列存放相应系的学生人数，一列存放学生的平均年龄。 CREATE TABLE Deptage (Sdept CHAR(15)， Num INT, Avgage INT);,然后对数据库的Student表按系分组求平均年龄，再把系名和平均年龄存入新表中。 INSERT INTO Deptage(Sdept,Num, Avgage) SELECT Sdept, COUNT(*),AVG(Sage) FROM Student GROUP BY Sdept;,3.4.2 修改数据 修改操作又称为更新操作，其语句的一般格式为： UPDATE SET =,=. WHERE ; 其功能是修改指定表中满足WHERE子句条件的元组。其中SET子句用于指定修改方法，即用的值取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。,一、修改某一个元组的值 例4 将学生95001的年龄改为22岁 UPDATE Student SET Sage=22 WHERE Sno=95001; 二、修改多个元组的值 例5 将所有学生的年龄增加1岁 UPDATE Student SET Sage=Sage+1; 三、带子查询的修改语句 子查询也可以嵌套在UPDATE语句中，用以构造执行修改操作的条件。,例6 将计算机科学系全体学生的成绩加10分。 UPDATE SC SET grade=grade+10 WHERE CS=(SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno); (处理过程类似于相关子查询) 四、修改操作与数据库的一致性 UPDATE语句一次只能操作一个表。这会带来一些问题。,例如，学号为95007的学生因病休学一年，复学后需要将其学号改为96089，由于Student表和SC表都有关于95007的信息，因此两个表都需要修改，这种修改只能通过两条UPDATE语句进行。 第一条UPDATE语句修改Student表： UPDATE Student SET Sno=96089 WHERE Sno=95007; 第二条UPDATE语句修改SC表： UPDATE SC SET Sno=96089 WHERE Sno=95007;,在执行了第一条UPDATE语句之后，数据库中的数据已处于不一致状态，因为这时实际上已没有学号为95007的学生了，但SC表中仍然记录着关于95007学生的选课信息，即数据的参照完整性受到破坏。只有执行了第二条UPDATE语句之后，数据才重新处于一致状态。但如果执行完一条语句之后，机器突然出现故障，无法再继续执行第二条UPDATE语句，则数据库中的数据将永远处于不一致状态。因此必须保证这两条UPDATE语句要么都做，要么都不做。为解决这一问题，数据库系统通常都引入了事务（Transaction）的概念，将在后面详细介绍。,3.4.3 删除数据 删除语句的一般格式为： DELETE FROM WHERE ; DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句，表示删除表中全部元组，但表的定义仍在字典中。也就是说，DELETE语句删除的是表中的数据，而不是关于表的定义。,一、删除某一个元组的值 例7 删除学号为95019的学生记录 DELETE FROM Student WHERE Sno=95019; 二、 删除多个元组的值 例8 删除所有的学生选课记录 DELETE FROM SC; 这条DELETE语句将使SC成为空表，它删除了SC的所有元组。,三、带子查询的删除语句 子查询同样也可以嵌套在DELETE语句中，用以构造执行删除操作的条件。 例9删除计算机科学系所有学生的选课记录 DELETE FROM SC WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno);,3.5 视图,1.从一个或多个基本表(或视图)导出的表，是虚表； 2. 使用户以不同方式看数据； 3. 数据库中只存视图的定义，不存数据； 4. 对视图的更新有限制。 主要目的：提供数据库保护。,3.5.1 定义视图 一、创建视图 SQL语言用CREATE VIEW命令建立视图，其一般格式为： CREATE VIEW (,.) AS WITH CHECK OPTION; （注：MS SQL Server的T SQL规定CREATE VIEW语句后不要分号。）,其中子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDER BY子句和DISTINCT短语。 WITH CHECK OPTION表示对视图进行UPDATE、 INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。 例1 建立信息系学生的视图。 CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept=IS;,例2 建立信息系学生的视图，并要求进行修改和插入操作时仍须保证该视图只有信息系的学生 CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept=IS WITH CHECK OPTION; WITH CHECK OPTION保证更新时满足视图定义的谓词条件。,行列子集视图：从一个基本表中导出，只是去掉了某些行或列(保留原表的主码)，这样的视图称为行列子集视图。 带表达式的视图，即带虚拟列的视图。 分组视图，子查询带集函数和GROUP BY分组的视图。,二、删除视图 语句的格式为： DROP VIEW ; 一个视图被删除后，由此视图导出的其他视图也将失效，用户应该使用DROP VIEW语句将他们一一删除。 例8 删除视图IS_S1 DROP VIEW IS_S1;,3.5.2 查询视图 DBMS执行对视图的查询时，首先进行有效性检查，检查查询涉及的表、视图等是否在数据库中存在，如果存在，则从数据字典中取出查询涉及的视图的定义，把定义中的子查询和用户对视图的查询结合起来，转换成对基本表的查询，然后再执行这个经过修正的查询。将对视图的查询转换为对基本表的查询的过程称为视图的消解（View Resolution）。,例1 在信息系学生的视图中找出年龄小于20岁的学生 SELECT Sno, Sage FROM IS_Student WHERE Sage20; DBMS执行此查询时，将其与IS_Student视图定义中的子查询 SELECT Sno, Sname, Sage FROM Student WHERE Sdept=“IS“ ;,结合起来，转换成对基本表Student的查询，修正后的查询语句为： SELECT Sno, Sage FROM Student WHERE Sdept=IS AND Sage20;,对分组视图的消解可能有一些问题。误把HAVING 短语作成WHERE 子句。 如书上例3. 但现在主流DBMS（如MS SQL Server）均能正常消解。,3.5.3 更新视图 由于视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新。 为防止用户通过视图对数据进行增删改时，无意或故意操作不属于视图范围内的基本表数据，可在定义视图时加上WITH CHECK OPTION子句，这样在视图上增删改数据时，DBMS会进一步检查视图定义中的条件，若不满足条件，则拒绝执行该操作。,例2 向信息系学生视图IS_S中插入一个新的学生记录，其中学号为95029，姓名为赵新，年龄为20岁 INSERT INTO IS_Student VALUES(95029, 赵新, 20); DBMS将其转换为对基本表的更新： INSERT INTO Student(Sno,Sname,Sage,Sdept) VALUES(95029, 赵新, 20, IS); 这里系统自动将系名IS放入VALUES子句中。,通过视图更新基本表，从理论上来说有的是可以更新的，有的是根本不可能更新的。 一般来说，行列子集视图是可更新的。 不同系统对视图的更新有不同的规定。,3.5.4 视图的作用 视图最终是定义在基本表之上的，对视图的一切操作最终也要转换为对基本表的操作。而且对于非行列子集视图进行查询或更新时还有可能出现问题。既然如此，为什么还要定义视图呢？这是因为合理使用视图能够带来许多好处：,视图的优点,1. 视图能够简化用户的操作 2. 视图使用户能以多种角度看待同一数据 3. 视图对重构数据库提供了一定程度的逻辑独立性 4. 视图能够对机密