数据库开发设计

1、3.3.3索引的建立和删除目的建立索引是加快查询速度的有效手段。索引的特点建立与删除索引由数据库管理员DBA或表的属主（owner），即建立表的人负责完成。系统在存取时会自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引。内容：一、建立索引二、删除索引1一、建立索引（1）2建立索引的一般格式：CREATE UNIQUE CLUSTER INDEX ON (，);表名：要建立索引的基本表的名字列名：索引可以建立在一列或多列上，各列名之间用逗号 分隔。次序：指索引值排序的次序。 ASC：升序；DESC：降序。UNIQUE：表示此索引的每一个索引值只对应唯一的数据记 录CLUSTER：表示

2、要建的索引是聚簇索引。 所谓聚簇索引是指索引项的顺序与表中记录的 物理顺序一致的索引组织。一、建立索引（2）3【例13】 CREATE CLUSTER INDEX Stusname ON Student(Sname); 将会在Student表的Sname列上建立一个聚簇索引。 Student表中的记录将按照Sname的值升序存放。 注意： （1）用户可以再最经常查询的列上建立聚簇索引以提高查询 效率。 （2）在一个基本表上最多只能建立一个聚簇索引。 （3）建立聚簇索引之后，更新该索引列上的数据时，往往导 致表中记录的物理顺序的变更，代价较大，因此对于经 常变更的列不宜建立聚簇索引。一、建立索引

3、（3）4【例14】为学生-课程数据库中student，Course，SC三 个表建立索引。其中： （1）为Student按照学号升序建立唯一索引 （2）为Course按照课程号建立唯一索引 （3）为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); 二、删除索引（1）索引的注意事项：索引一经建成，就由系统来维护它，不需用户干预。建立

4、索引是为了减少查询操作的时间，但是如果数据增删改频繁，系统会花很多时间来维护索引，从而降低查询效率。这时，可以删除一些不必要的索引。5删除索引的格式为：DROP INDEX ；二、删除索引（2）6【例15】删除student表的stusname索引。 DROP INDEX Stusname； 删除索引时，系统会同时从数据字典中删除有关索引的描述。 RDBMS中索引技术：（1）B+树 具有动态平衡的特点（2）HASH索引 具有查找速度快的特点 用户使用CREATE INDEX创建索引的时候，可以定义索引是 唯一索引，非唯一索引或聚簇索引。 至于某一索引是采用B+树，还是HASH索引则由具体的 R

5、DBMS来决定。3.4数据查询7查询是数据库的核心操作。SQL提供SELECT语句进行数据库查询，该语句具有灵活的使用方式和丰富的功能。Select语句的一般格式：SELECT ALL | DISTINCT ，FROM ，WHERE GROUP BYHAVING ORDER BY |;Select语句的含义：（1）根据WHERE子句的条件表达式，从FROM子句指定的基本表或视 图中找出满足条件的元组，再按SELECT子句的目标列表达式，选 出元组中的属性值形成结果表。（2）如果有GROUP BY子句，则将结果按进行分组，该属性 值相同的元组为一个组。通常会在每组中使用聚集函数。如果 GROUP

6、 BY子句带有HAVING短语，则只有满足条件的组才予以 输出。（3）如果有ORDER BY子句，其结果还需要按照的值进行排序。内容3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询83.4.1单表查询单表查询是仅涉及一个表的查询内容：一、选择表中的若干列二、选择表中的若干元组三、ORDER BY子句四、聚集函数（aggregate functions）五、GROUP BY子句9一、选择表中的若干列选择表中的全部列或部分列，这就是关系代数的投影操作。内容：（1）查询指定列（2）查询全部列（3）查询经过计算的列10（1）查询指定列11很多情况下，用户只对表中一部分属性感兴趣

7、，这时可以通过select子句的中指定要查询的属性列。【例1】查询全体学生的学号和姓名 SELECT Sno，Sname FROM Student;【例2】查询全体学生的姓名、学号和所在系 SELECT Sname，Sno，Sdept FROM Student; 目标列表达式的各个列的先后顺序可以与表中的顺序不一致。用户可以根据需要改变列的现实顺序。（2）查询全部列12将表中的所有属性列都选出来，可以有两种方法：（1）在SELECT关键字后面列出所有表名（2）如果列的显示顺序与其在表中的顺序一致，也可以简单 地将指定为*【例3】查询全体学生的详细记录 SELECT * FROM student

8、；等价于 SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student;（3）查询经过计算的列（1）13SELECT子句的不仅可以是表中的属性列，也可以是表达式。【例4】查询全体学生的姓名及其出生年份。 SELECT Sname，2012-Sage FROM Student;SnameSname2012-Sage2012-Sage李勇1984刘晨1985王敏1986张立1984（3）查询经过计算的列（2）14不仅可以是算术表达式，还可以是字符串常量、函数等。【例5】查询全体学生的姓名、出生年份和所在院系，要求用小写字母表示所有系名。 SELECT Sname，Ye

9、ar Of Birth:，2012-Sage，LOWER(Sdept) FROM Student;SnameSnameYear OfYear Of Birth: Birth:2012-Sage2012-SageLOWER(LOWER(SdeptSdept) )李勇Year Of Birth:1984cs刘晨Year Of Birth:1985cs王敏Year Of Birth:1986ma张立Year Of Birth:1984is（3）查询经过计算的列（3）15用户可以通过指定别名来改变查询结果的列标题，这对于算术表达式，常量，函数名的目标列表达式尤为有用。对于上例 SELECT Sname

10、 NAME， Year Of Birth:BIRTH， 2012-Sage BIRTHDAY， LOWER(Sdept) DEPARTMENT FROM Student;SnameSnameBIRTHBIRTHBIRTHDAYBIRTHDAYDEPARTMENTDEPARTMENT李勇Year Of Birth:1984cs刘晨Year Of Birth:1985cs王敏Year Of Birth:1986ma张立Year Of Birth:1984is二、选择表中的若干元组内容（1）消除取值重复的行（2）查询满足条件的元组16（1）消除取值重复的行17两个本来并不完全相同的元组，投影到指定的

11、某些列上后，可能变成相同的行了，可以用DISTINCT取消它们。【例6】查询选修了课程的学生学号。 SELECT Sno FROM SC；SnoSno200215121200215121200215121200215121200215122SELECT DISTINCT Sno FROM SC；SnoSno200215121200215122（2）查询满足条件的元组(1)18查询满足指定条件的元组通过WHERE子句实现。WHERE子句常用的查询条件如下表：查询条件查询条件谓词谓词比较=，=，=，!=，!NOT+上述比较运算符确定范围BETWEEN AND，NOT BETWEEN AND确定集合

12、IN，NOT IN字符匹配LIKE，NOT LIKE空值IS NULL，IS NOT NULL多重条件（逻辑运算）AND，OR，NOT（2）查询满足条件的元组(2)19【例23】查询计算科学系年龄在20岁以下 的学生姓名 SELECT Sname FROM Student WHERE Sdept = CS AND Sage20;三、ORDER BY子句（1）20用户可以用ORDER BY子句对查询结果按照一个或多个属性列的升序或降序进行排列。缺省为升序。【例24】查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。 SELECT Sno，Grade FROM SC WHERE C

13、no=3 ORDER BY Grade DESC; 对于空值： 如按升序排，含空值的元组排在最后。 降序排，放在最前面。三、ORDER BY子句（2）21【例25】查询全体学生情况，查询结果按照所在系 号的升序排列，同一系中的学生按年龄的 降序排列。 SELECT * FROM Student ORDER BY Sdept ASC ， Sage DESC;四、聚集函数（aggregate functions）（1）22为了进一步方便用户，增强检索功能，SQL提供了许多聚集函数，主要有：COUNT（DISTINCT|ALL *） 统计元组个数COUNT（DISTINCT|ALL ） 统计一列中元

14、组个数SUM （DISTINCT|ALL ） 计算一列值的总和AVG （DISTINCT|ALL ） 计算一列值的平均值MAX（DISTINCT|ALL ） 计算一列值的最大值MIN（DISTINCT|ALL ） 计算一列值的最小值指定DISTINCT短语，则表示计算时要取消指定列中的重复项。如果不指定DISTINCT短语或指定ALL短语（ALL为缺省值），则表示不取消重复项。四、聚集函数（aggregate functions）（2）23【例26】查询学生总人数 SELECT COUNT（*） FROM Student；【例27】查询选修了课程的学生人数 SELECT COUNT（DISTI

15、NCT Sno） FROM SC 学生每修一门课，在SC中都有一条相应的记录。一个学生要选修多门课程，为了避免重复计算学生人数，必须在COUNT函数中用DISTINCT短语。注意，是否有DISTINCT，其含义有所不同。没有DISTINCT，表示多少人选修了课，有DISTINCT表示选课的人次。四、聚集函数（aggregate functions）（3）24【例28】计算1号课程的学生平均成绩。 SELECT AVG（Grade） FROM SC WHERE Cno=1;【例29】查询选修1号课程的学生的最高分数。 SELECT MAX（Grade） FROM SC WHERE Cno=1;【

16、例30】查询学生200215012选修课程的总学分数。 SELECT SUM（Ccredit） FROM SC，Course WHERE Sno=200215012 AND SC.Cno=Course.Cno;在聚集函数遇到空值时，除count（*）外，都跳过空值只处理非空值。五、GROUP BY子句(1)25GROUP BY子句将查询结果按某一列或多列的值分组，值相等的为一组。对查询的结果分组的目的是为了细化聚集函数的作用对象。如果未对查询结果分组，聚集函数将作用于整个查询结果。分组后聚集函数作用于每一个组，即每组都有一个函数值。【例31】求各个课程号及相应的人数。SELECT Cno，CO

17、UNT(Sno)FROM SCGROUP BY Cno;CnoCnoCOUNTCOUNT(Sno)(Sno)122234344433548五、GROUP BY子句(2)26如果分组后还要求按照一定的条件对这些组进行筛选，最终只输出满足条件的组，则可以使用HAVING短语指定筛选条件。【例32】查询选修了3门以上课程的学生学号。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)3;这里先用GROUPBY子句按照Sno分组，再用聚集函数COUNT对每一组计数。HAVING短语给出选择组的条件，只有满足条件的组才会被选出来。五、GROUP BY子句(3)

18、27WHERE子句和HAVING 短语的区别在于作用对象不同。（1）WHERE子句作用于基本表或视图，从中 选出满足条件的元组。（2）HAVING短语作用于组，从中选出满足条 件的组。3.4.2连接查询定义如果一个查询同时涉及两个以上的表，则称之为连接查询。连接查询时关系数据库中最主要的查询，包括：等值连接查询自然连接查询非等值连接查询自身连接查询外连接查询复合条件连接查询28内容一、等值与非等值查询二、自身连接三、外连接四、复合条件连接29一、等值与非等值查询（1）30连接查询的WHERE子句中用来连接两个表的条件称为或。(1)格式1 . . 比较运算符： = = = != (或) (2)格

19、式2 . BETWEEN . AND . 当连接运算符为=时，称为。 使用其他运算符为。 连接谓词中的列名为一、等值与非等值查询（2）31【例33】查询每个学生及其选修课程的情况【分析】学生情况放在Student表中，学生选课情况放在SC表中，所以本查询实际上涉及Student和SC两个表。这两个表的联系通过公共属性Sno实现的。 SELECT Student.*，SC.* FROM Student，SC WHERE Student.Sno=SC.Sno;Student.SnoStudent.SnoSnameSnameSsexSsexSageSageSdeptSdeptSC.SnoSC.Sno

20、CnoCnoGradeGrade200215121李勇男20CS200215121192200215121李勇男20CS200215121285200215121李勇男20CS200215121388200215122刘晨女19CS200215122290200215122刘晨女19CS200215122380一、等值与非等值查询（3）RDBMS在执行该连接操作的一种可能过程为：首先在表Student中找到第一个元组然后从头扫描SC表，逐一查找与Student第一个元组的Sno相等的SC元组找到后将Student的第一个元组与该元组拼接起来，形成结果表中一个元组SC全部查完之后，再找Stude

21、nt表中的下一个元组重复上述操作，直到Student中的元组全部处理完毕为止说明这是嵌套循环算法的基本思想如果在SC表Sno建立了索引的话，就不用每次全扫描SC表了，而是根据Sno值通过索引找到相应的SC元组。用索引查询SC中满足条件的元组一般会比全扫描快。32一、等值与非等值查询（4）33【例34】对【例33】用自然连接完成SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student，SC WHERE Student.Sno=SC.Sno;【解释】（1）由于Sname，Ssex，Sage，Sdept，Cno，Grade在Stu

22、dent表与SC表中是唯一的，因此引用时去掉了表名前缀。（2）Sno在两个表都出现了，因此引用时必须加上表名前缀。 二、自身连接（1）34连接操作不仅可以再两个表之间进行，也可以在一个表与其自己进行连接，称为表的自身连接。【例35】查询一门课的间接选修课（即先修课的先修课【分析】（1）在Course表中，只有每门课的直接选修课，没有先修 课的先修课。要得到这个信息，必须对一门课找到 其先修课，再按此先修课的课程号，查找它的先修 课称。这就要求将Course表与其自身连接。（2）为此，要为Course表取两个别名，一个是FIRST，另 一个是SECOND。二、自身连接（2）课程课程号号CnoCn

23、o课程名课程名CnameCname先行先行课课CpnoCpno学分学分CcreditCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言6435课程课程号号CnoCno课程名课程名CnameCname先行先行课课CpnoCpno学分学分CcreditCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64FIRST表（Course表）SECOND表（Course表）SELECT FIRST.Cno，SECOND.CpnoFROM Course FIRST，Course SECONDWHE

24、RE FIRST.cpno=SECOND.CnoCnoCnoPcnoPcno173556三、外连接(1)36外连接的概念： 在通常的连接操作中，只有满足连接条件的元组才能作为结果输出。 有时想以Student表为主题列出每个学生的基本情况及选课情况。若 某个学生没有选课，仍把舍弃的Student元组保存在结果关系中，而 在SC表的属性上填空值，这时就需要外连接。【例36】 SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student LEFT OUT JOIN SC ON（Student.Sno=SC.Sno）可以用USING来

25、去掉结果中的的重复项： SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student LEFT OUT JOIN SC USING(Sno); 三、外连接(2)37Student.SnoStudent.SnoSnameSnameSsexSsexSageSageSdeptSdeptCnoCnoGradeGrade200215121李勇男20CS192200215121李勇男20CS285200215121李勇男20CS388200215122刘晨女19CS290200215122刘晨女19CS380200215123王敏女18MA

26、NULLNULL200215125张立男19ISNULLNULL执行结果如下：左外连接列出左边关系中的所有元组，右外连接列出右边关系中所有元组。四、复合条件连接（1）38上述各个连接查询中，WHERE子句中只有一个条件，即为连接谓词。WHERE子句中可以有多个连接条件，称为复合条件连接。【例37】查询选修2号课程且成绩在90分以上的所有学生。 SELECT Student.Sno，Sname FROM Student，SC WHERE Student.Sno=SC.Sno AND SC.Cno=2 AND SC.Grade90;【说明】 该查询的一个优化执行过程是先从SC中选出Cno=2并且G

27、rade90的元组形成一个中间关系，再和Student中满足连接条件的元组进行连接得到最终的结果关系。四、复合条件连接（2）39连接操作除了可以两表连接，一个表与自身连接，还可以是两个以上的表进行连接，后者称为多表连接多表连接。【例38】查询每个学生的学号、姓名、选修的课程名及成绩 SELECT Student.Sno，Sname，Cname，Grade FROM Student，SC，Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;3.4.3嵌套查询40SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块查

28、询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询为嵌套查询嵌套查询。例如：SELECT SnameFROM Student /*外查询或父查询*/WHERE Sno IN ( SELECT Sno FROM SC /*内查询或子查询*/ WHERE Cno=2);注意：（1）SQL允许多层嵌套查询，即一个子查询中还可以嵌套其他 子查询。（2）子查询中的SELECT语句不能使用ORDER BY子句。ORDER BY子句只能对最终查询结果排序。（3）嵌套查询允许用多个简单查询构成复杂的查询，从而增 加SQL的查询能力。这正是结构化的含义所在。3.4.3嵌套查询（

29、内容）一、带有IN谓词的子查询二、带有比较运算符的子查询三、带有ANY（SOME）或ALL谓词的子查询四、带有EXISTS谓词的子查询41一、带有IN谓词的子查询（1）42在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词。【例39】查询与“刘晨”在同一个系 学习的学生【分析】先分步来完成此查询，然后再构造嵌套查询（1）确定“刘晨”所在的系名 结果为：CS（2）查找所有在CS系学习的学生 结果为：（3）将第一步查询嵌套在第二步查询 的条件中，构造嵌套查询。SELECT SdeptFROM StudentWHERE Sname=刘晨SELECT Sno，Sname

30、，Sdept FROM Student WHERE Sdept=CSSnoSnoSnameSnameSdeptSdept200215121李勇CS200215122刘晨CSSELECT Sno，Sname，SdeptFROM StudentWHERE Sdept IN ( SELECT Sdept FROM Student WHERE Sname=刘晨);一、带有IN谓词的子查询（2）不相关子查询子查询的查询条件不依赖于父查询，称为不相关子查询。该不相关子查询的求解办法：由里向外处理。先执行子查询，子查询的结果用于建立父查询的查找条件，然后再执行父查询。43本例也可以用自身连接来完成：SELE

31、CT S1.Sno，S1.Sname，S1.SdeptFROM Student S1，Student S2WHERE S1.Sdept = S2.Sdept AND S2.Sname = 刘晨;说明： 实现一个查询可以有很多方法，不同的方法其执行效率会有差别，甚至差别会很大。一、带有IN谓词的子查询（3）44【例40】查询选修了课程名为“信息系统”的学生学号和姓名。【分析】 本查询涉及学号、姓名和课程名三个属性。学号和姓名存放在 Student表中，课程名存在放在Course，但是student与Course 两个表之间没有直接联系，必须通过SC表建立它们二者之间的 联系。所以本查询实际涉及3

32、个关系。 【结果】 SELECT Sno，Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno IN ( SELECT Cno FROM Course WHERE Cname=信息系统 ) )本查询也可以用连接查询实现：SELECT Student.Sno，SnameFROM Student，SC，Course WHERE Student.Sno=SC.Sno AND SC.Cno = Course.Cno AND Course.Cname = 信息系统一、带有IN谓词的子查询（4）总结：（1）查询涉及多个关系时，用嵌套查

33、询逐步求解，层次清楚，易于构造，具有结构化程序设计的优点。（2）有些嵌套查询可以用连接运算替代，有些是不能替代的。对于可以用连接运算代替的嵌套的，用户可以根据自己的习惯确定。45二、带有比较运算符的子查询（1）定义：带有比较运算符的子查询是指与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值，可以用，=，=，!=，等比较运算符。注意：子查询一定要跟在比较符之后46SELECT Sno，Sname，SdeptFROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname=刘晨) = Sdept；错误！SELECT Sno，

34、Sname，SdeptFROM Student WHERE Sdept = ( SELECT Sdept FROM Student WHERE Sname=刘晨) 正确！二、带有比较运算符的子查询（2）47【例41】找出每个学生超过他选修课程平均成绩的课程号。 某个学生所有科目的平均值SELECT Sno，CnoFROM SC xWHERE Grade = ( SELECT AVG(Grade) FROM SC y WHERE y.Sno = x.Sno);【解释】（1）x是表SC的别名，又称为元组变量，可以用来表示SC的一个元 组。（2）内层查询时求一个学生所有选修课程平均成绩，至于是哪个

35、学生的平均成绩要看参数x.Sno的值，而该值是与父查询相关的， 因此这类查询称为相关子查询相关子查询。（3）注意，如果条件是o=o的含义。班级某个课程的平均值二、带有比较运算符的子查询（3）48这个语句的一种可能的执行过程是：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、执行这个查

36、询，得到： （200215121，1） （200215121，3）然后外层查询取出下一个元组重复做1至3步骤的处理，知道外层的SC元组全部处理完毕。结果是： （200215121，1） （200215121，3） （200215122，2）三、带有ANY（SOME）或ALL谓词的子查询（1）49子查询返回单值时可以用比较运算符，但是返回多值是要用ANY（有的系统用SOME）或ALL谓词修饰符。而是用ANY或ALL谓词时必须同时使用比较运算符。其语义为：其语义为：ANY大于子查询结果中的某个值ALL大于子查询结果中的所有值ANY小于子查询结果中的某个值=ANY大于等于子查询结果中的某个值=ALL

37、大于等于子查询结果中的所有值=ANY小于等于子查询结果中的某个值=ALL小于等于子查询结果中的所有值!=ANY不等于子查询结果中的某个值!=ALL不等于子查询结果中的任何一个值三、带有ANY（SOME）或ALL谓词的子查询（2）50【例42】查询其他系中比计算机科学系某一学 生年龄小的学生姓名和年龄。SELECT Sname，SageFROM StudentWHERE Sage ANY ( SELECT Sage FROM Student WHERE Sdept=CS) AND Sdept CS; /*注意这是父查询块中的条件*/ 结果如下： Sname Sage - 王敏 18 张立 19R

38、DBMS执行查询时，首先查询子查询，找出CS系中所有的学生的年龄，构成一个集合（20，19）。然后处理父查询，找到所有不是CS系且年龄小于20或19的学生。本查询也可以用聚集函数来实现。SELECT Sname，SageFROM StudentWHERE Sage ( SELECT MAX (Sage) FROM Student WHERE Sdept = CS) AND Sdept CS;三、带有ANY（SOME）或ALL谓词的子查询（3）51【例43】查询其他系中比计算机科学系所有学生年龄都小的学生姓名和年 龄。SELECT Sname，SageFROM StudentWHERE Sage

39、 ALL ( SELECT Sage FROM Student WHERE Sdept = CS) AND Sdept CS;RDBMS执行此查询时，首先处理子查询，找出CS系中所有学生的年龄，构成一个集合（20，19）。然后处理父查询，找出所有不是CS且年龄小于20，也小于19的学生。查询结果为： Sname Sage - 王敏 18本查询同样也可以用聚集函数来实现。SQL语句如下：SELECT Sname，SageFROM StudentWHERE Sage (SELECT MIN(Sage) FROM Student WHERE Sdept=CS) AND Sdept CS;用聚集函数实

40、现子查询比直接用ANY或ALL查询效率要高。三、带有ANY（SOME）或ALL谓词的子查询（4）= =或或！= = = =ANYIN- MAXMIN=MINALL-NOT INMINMAX=MAX52四、带有EXISTS谓词的子查询（1）53EXISTSEXISTSEXISTSSRS=RSR代表存在量词 。带有谓词的子查询不返回任何数据，只产生逻辑真值true或逻辑假值false。可以用来判断xS，非空是否成立。【例44】查询所有选修了1号课程的学生姓名【分析】 本查询涉及Student和SC表。可以在Student中一次取每个元组的Sno值， 用此值去检查SC表。若SC中存在这样的元组，其S

41、no值等于此 Student.Sno值，并且其Cno=1，则取此Student.Sname送入结果表。SELECT SnameFROM StudentWHERE EXISTS ( SELECT * FROM SC WHERE Sno=Student.Sno AND Cno = 1);四、带有EXISTS谓词的子查询（2）54注意：1.由EXISTS引导的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。2.一些带EXIST或NOT EXIST的子查询不能被其他子查询等价替换。所有带有IN、比较运算符、ANY、ALL谓词的子查询都能被带有EXIST

42、S的子查询替换3.由于EXISTS量词的相关子查询只关心内层是否有返回值，并不要求具体值，所以有时是高效的方法。四、带有EXISTS谓词的子查询（3）55与EXISTS谓词相对应的是NOT EXISTS谓词。使用存在量词 NOT EXISTS后，若内查询的结果为空，则外层的WHERE子句返回真值，否则返回为假值。【例45】查询没有选修1号课程的学生姓名。SELECT SnameFROM StudentWHERE NOT EXISTS ( SELECT * FROM SC WHERE Sno = Student.Sno AND Cno = 1);四、带有EXISTS谓词的子查询（4）56一些带E

43、XISTS 或 NOT EXISTS谓词的子查询不能被其他形式的子查询替换，但是所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS的子查询等价替换。【例39】的另一种解法： 查询与“刘晨”在同一个系学习的学生SELECT Sno，Sname，SdeptFROM Student S1WHERE EXISTS ( SELECT * FROM Student S2 WHERE S2.Sdept= S1.Sdept AND S2.Sname = 刘晨);四、带有EXISTS谓词的子查询（5）57由于带EXISTS量词的相关子查询只关心内层查询是否有返回值，并不需要查具体值，因此

44、其效率并不一定低于不相关查询，有时是高效的方法。【例46】查询选修了全部课程的学生姓名【分析】（1）SQL中没有全称量词（FOR ALL）。但是我们可以把带有全称 量词的谓词转换为等价的带有存在量词的谓词。 （ x）P( x( P)（2）由于没有全称量词，可以将意思转换成等价的用存在量词的 形式：查询这样的学生，没有一门课是他不选的。SQL语句为： SELECT Sname FROM Student WHERE NOT EXISTS ( SELECT * FROM Course WHERE NOT EXISTS ( SELECT * FROM SC WHERE Sno=Student.Sno

45、AND Cno = Course.Cno);四、带有EXISTS谓词的子查询（6）58 SELECT Sname FROM Student WHERE NOT EXISTS ( SELECT * FROM Course WHERE NOT EXISTS ( SELECT * FROM SC WHERE Sno=Student.Sno AND Cno = Course.Cno);SELECT * FROM SC WHERE Sno=Student.Sno AND Cno = Course.Cno某个Student选修了某个课程Course( SELECT * FROM Course WHERE

46、NOT EXISTS ( SELECT * FROM SC WHERE Sno=Student.Sno AND Cno = Course.Cno);存在某个课程，学生Student没有选对于某个学生，不存在某个课程他不选，即：选了所有课程的学生四、带有EXISTS谓词的子查询（7）59【例47】查询至少选修了学生200215122选修的全部课程 的学生号码。SQLpq 【分析】 （1）本查询可以用逻辑蕴含来表达：查询学号为x的学生，对所有的 课程y,只要200215122学生选修了课程y，则x也选修了y。（2）形式化表示如下 p表示“200215122学生选修了课程y” q表示“学生x选修了课

47、程y” 则上述查询为：( y)pq（3）中没有蕴含，但是可以利用谓词演算将一个逻辑蕴含等价转换 为：pq（4）该查询可以等pqpq 价转化为： ( y)pq（ y( (pq)）（ y( ()） （ y()（5）它表示的语义为：不存在这样的课程，学生200215122选修了，而 学生x没有选四、带有EXISTS谓词的子查询（8）60上述语义的SQL语言表示如下：SELECT DISTINCT SnoFROM SC SCXWHERE NOT EXISTS ( SELECT * FROM SC SCY WHERE SCY.Sno = 200215122 AND NOT EXISTS ( SELECT

48、 * FROM SC SCZ WHERE SCZ.Sno = SCX.Sno AND SCZ.cno = SCY.Cno);RED：学生200215122选的选课记录GREEN：学生X选修了学生200215122所选课程的选课记录3.4.4集合查询（1）61SELECT 语句的查询结果是元组的集合。所以多个SELECT语句的结果可进行集合操作。集合操作主要包括并操作（UNION）、交操作（INTERSECT）和差操作（EXCEPT）。注意：参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。3.4.4集合查询（2）62【例48】查询计算机科学系的学生及年龄不大 于19岁的学生。

49、SELECT * FROM Student WHERE Sdept = CS UNIONSELECT * FROM Student WHERE Sage=19;注意：（1）系统会自动去掉重复元组（2）如果要保留重复元组，则用UNION ALL3.4.4集合查询（3）63【例49】查询选修了课程1或者选修了课程2的学生SELECT Sno FROM SC WHERE Cno = 1 UNIONSELECT Sno FROM SC WHERE Cno = 2【例50】查询计算机科学系的学生与年龄不大于19岁的学生的交 集。SELECT * FROM Student WHERE Sdept=CS I

50、NTERSECTSELECT * FROM Student WHERE Sage=19等价于：SELECT * FROM Student WHERE Sdept=CS AND Sage=193.4.4集合查询（4）64例51：查询既选修了课程1又选修了课程2的学生。就是选修了课程1和课程2的学生的交集。SELECT SnoFROM SCWHERE Cno=1INTERSECTSELECT SnoFROM SCWHERE Cno=2SELECT SnoFROM SCWHERE Cno=1AND Sno IN (SELECT Sno FROM SC WHERE Cno=2);也可以写成3.4.4集

51、合查询（5）65例52：查询计算机系中的那些年龄不大于19岁的学生的集合。SELECT *FROM StudentWHERE Sdept=CSEXCEPTSELECT *FROM StudentWHERE Sage19；等同于3.4.5SELECT语句的一般格式(1)66SELECT语句的一般格式为：SELECTALL|DISTINCT别名， 别名FROM 别名， 别名WHERE GROUP BY HAVING ORDER BY ASC|DESC;3.4.5SELECT语句的一般格式(2)67目标列表达式的格式：1.*2.*3.COUNT(DISTINCT|ALL*)4.， . 其中可以是属性

52、列、作用于属性列的聚集函数和常量的任意算术运算(+，-，*，/)组成的运算公式。3.4.5SELECT语句的一般格式(3)682、聚集函数的一般格式为：COUNTSUMAVGMAXMIN（DISTINCT|ALL）3.4.5SELECT语句的一般格式(4)693、WHERE子句的条件表达式的格式：|()()()(3)(1,2 .)(SELECT)ANY ALL SELECTANDSELECTSELECT（1）属性列名常量语句（2）属性列名属性列名NOTBETWEEN常量常量语句语句值值NOTIN语句3.4.5SELECT语句的一般格式(5)703、WHERE子句的条件表达式的格式：IS NOT

53、 NULLAND.ORANDOR（4）NOTLIKE（5） （6）NOTEXIST(SELECT语句)(7)3.5数据更新数据更新有三种：向表中插入若干行数据修改表中的数据删除表中若干行在SQL中有相应的三类语句71 内容3.5.1插入数据3.5.2修改数据3.5.3删除数据723.5.1插入数据SQL的数据插入语句INSERT有两种形式：插入一个元组插入子查询的结果后者可以一次插入多个元组73内容一、插入元组二、插入子查询结果74一、插入元组（1）75插入元组的INSERT语句格式为：INSERT INTO (，)VALUES (，);功能：1. 将新的元组插入指定表中。2. 其中新元组的属

54、性列1的值为常量1，属性列2的值为常量23. INTO子句中没有出现的列，新元组的取值为空值。注意：在表的定义中说明了不能为空的列不能取空值。一、插入元组（2）76【例1】将一个新学生元组（学号：200215128； 姓名：陈冬；性别：男；所在系：IS；年 龄：18）插入到Student表中。INSERT INTO Student (Sno，Sname，Ssex，Sdept，Sage) VALUES (200215128，陈冬，男，IS，18);注意：（1）属性的顺序可以和CREATE TABLE中的顺序 不一样。二、插入子查询结果（1）77子查询不仅可以嵌套在SELECT语句中，用以构造父查

55、询的条件，也可以嵌套在INSERT语句中用以生成要插入的批量数据。要插入子查询结果的INSERT语句的格式为：INSERT INTO (，)子查询；二、插入子查询结果（2）78【例4】对每一个系，求学生的平均年龄，并把结果存入数据库。【步骤】第一步：在数据库建立一个表，其中一列存放系名，另一列存放相 应的学生平均年龄。 CREATE TABLE Dept_age (Sdept CHAR(15) Avg_age SMALLINT);第二步：对Student表按系分组求平均年龄，在把系名和平均年龄存 入表中。 INSERT INTO Dept_age (Sdept，Avg_age) SELECT

56、Sdept，AVG(Sage) FROM Student GROUP BY Sdept;3.5.2修改数据（1）79修改操作又称为更新操作，其语句的一般格式为： UPDATE SET =， = WHERE 【功能说明】 （1）修改指定表中满足WHERE子句条件的元组。 （2）SET子句给出的表达式的值取代相应的属性列值。 （3）如果省略WHERE子句，表示修改表中所有的元组。3.5.2修改数据（2）80一、修改某一元组的值【例5】将学生200215121的年龄改为22岁UPDATE StudentSET Sage=22WHERE Sno=2002151213.5.2修改数据（3）81二、修改多

57、个元组的值【例6】将所有学生的年龄增加一岁 UPDATE StudentSET Sage = Sage+1;3.5.2修改数据（4）82三、带子查询的修改语句【例7】将计算机科学系全体学生的成绩 置为零UPDATE SCSET Grade = 0WHERE CS = (SELECT Sdept FROM Student WHERE Student.Sno = SC.Sno);3.5.3删除数据（1）83删除语句的一般格式为：DELETEFROM WHERE ;【功能】1、从指定表中删除满足WHERE子句条件的所有元组。2、如果省略WHERE条件，表示删除表中全部元组，但是 表的定义仍然在字典中

58、。也就是说，DELETE语句删除 的是表中的数据，而不是表的定义。3.5.3删除数据（2）84一、删除某一个元组的值【例8】删除学号为200215128的学生记录 DELETE FROM Student WHERE Sno = 200215128二、删除多个元组的值【例9】删除所有学生的选课记录 DELETE FROM SC三、带子查询的删除语句【例10】删除计算机科学系所有学生的选课记录 DELETE FROM SC WHERE CS = (SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno);3.6视图定义视图是从一个或几个基本表（或视图）

59、导出的表。特点它与基本表不同，是一个虚表。数据库只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以基本表中的数据发生变化，从视图中查询出的数据也随之改变了。视图就是一个窗口，透过视图可以看到数据中自己所感兴趣的数据及其变化。视图一旦定义，就可以和基本表一样被查询、被删除。也可以再一个视图之上再定义新的视图。对视图的更新操作有一定的限制。85内容3.6.1 定义视图3.6.2 查询视图3.6.3 更新视图3.6.4 视图的作用863.6.1 定义视图一、建立视图二、删除视图87一、建立视图（1）88SQL语言用CREATE VIEW命令建立视图，其一般格式为： CRE

60、ATE VIEW (，) AS WITH CHECK OPTION【说明】（1）其中子查询是任意复杂的SELECT语句，但是通常不允许含有ORDER BY子句和DISTINCT短语。（2）WITH CHECK OPTION表示对视图进行 UPDATE，INSERT和DELETE操 作时要保证更新、插入或删除的行满足视图定义的谓词条件（即子查 询中的条件表达式）。（3）组成视图的属性列名或者全部省略或全部指定，没有第三种选择。（4）如果省略了视图的各个属性名，则隐含该视图由子查询中SELECT子句 目标列中的诸字段组成。（5）在下列三种情况下，必须指定组成视图的所有列名：某个目标列不是单纯的属性