数据库的查询和视图.ppt

《数据库的查询和视图.ppt》由会员分享，可在线阅读，更多相关《数据库的查询和视图.ppt（126页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库的查询和视图 Still waters run deep.流静水深流静水深,人静心深人静心深 Where there is life,there is hope。有生命必有希望。有生命必有希望2011劳东青主要内容主要内容4.1 关系运算关系运算4.2 数据库的查询数据库的查询4.3 视图视图4.4 游标游标2011劳东青4.1 关系运算p什么是关系运算？什么是关系运算？教材P2中提到有种数据模型为关系模型。其特点为：以二维表格（即关系表）的形式组织数据库中的数据。因此，可将关系运算理解为表的运算。p关系运算的特点特点：运算的对象和结果都是表。p关系运算的分类关系运算的分类：传统的集合运

2、算传统的集合运算：并、差、交等专门的关系运算专门的关系运算：选择、投影、连接等2011劳东青4.1 关系运算p选选 择（择（selectionselection）又称限制，是一种单目运算。选择运算用于按给定的条件，从表中选出满足条件的从表中选出满足条件的行行（即记录），形成一个新表作为运算结果。记为：F F(R)(R)只涉及到单个只涉及到单个操作数的运算操作数的运算选择运算符选择运算符条件表达式条件表达式被操作的表被操作的表2011劳东青4.1 关系运算p例，假设存在表Tp例：例：假设要在假设要在T T表中找出表中找出T120T120的行（或记录）形成一个新的行（或记录）形成一个新表，则运算式

3、为表，则运算式为？T120T120(T)(T)2011劳东青4.1 关系运算p投投 影（影（ProjectionProjection）单目运算。用于从表中选出指定的属性值组成一个新表。投影操作主要是从列的角度进行运算。记为：A(R)投影运算符投影运算符属性名（即属性名（即列名）列名）被操作的表被操作的表2011劳东青4.1 关系运算p例，假设存在表Tp例：假设要查询例：假设要查询T T表中表中T1T1、T2T2、T5T5的值，即要求在的值，即要求在T T表中对表中对T1T1、T2T2、T5T5进行投影，则运算式为进行投影，则运算式为？T1,T2,T5(T)T1T2T51A1M2B1N3A2O5

4、DP20FQ100A3N2011劳东青4.1 关系运算p连连 接（接（JOIN）也称为连接，是一种双目运算。用于把两个表中的行按照给定的条件进行拼接而形成新表。记为：RSF是条件是条件R、S是被操是被操作的表作的表F2011劳东青4.1 关系运算p两类常用连接运算两类常用连接运算等值连接等值连接：要求两个表的某些列值相等的连接，记为：RS自然连接自然连接：特殊的等值连接特殊的等值连接，要求连接的两个表必须具有共同的属性（列），并且必须在结果中把重复的属性列去掉。记为：RSA=B2011劳东青4.1 关系运算p例，假设存在关系R，关系S一般连接一般连接、等值连接等值连接、自然连接自然连接2011

5、劳东青4.1 关系运算p一般连接一般连接将关系R与S连接起来，要求关系R中C列的值小于小于关系S中E列的值。CERS2011劳东青4.1 关系运算p等值连接等值连接将关系R与S连接起来，要求关系R中B列的值等于等于关系S中B列的值。R.B=S.BRS2011劳东青4.1 关系运算p自然连接自然连接将关系R与S连接起来，要求关系R中B列的值等于等于关系S中B列的值。RS2011劳东青2022/11/1314主要内容主要内容4.1 关系运算关系运算4.2 数据库的查询数据库的查询4.3 视图视图4.4 游标游标2011劳东青4.2 数据库的查询pSelectSelect的语法格式：的语法格式：SE

6、LECT，INTO FROM，WHERE GROUP BY HAVING ORDER BY ASC|DESC ；2011劳东青4.2 数据库的查询pSELECTSELECT子句子句：指定要显示的属性列pFROMFROM子句子句：指定查询对象(基本表或视图)pWHEREWHERE子句子句：指定查询条件pGROUP BYGROUP BY子句子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。pHAVINGHAVING短语短语：筛选出只有满足指定条件的组，只能跟在GROUP BY子句后。pORDER BYORDER BY子句子句：对查询结果表按指定列值的升序或降

7、序排序。pINTOINTO子句子句：创建新表，并将查询结果插入新表。2011劳东青4.2 数据库的查询2011劳东青（一）简单单表查询p简单单表查询语句只包括SELECT子句和FROM子句。1.1.选择表中的若干列选择表中的若干列。不同列之间要用英文逗号隔开。【例1】查询数据库XSCJ的学生表中所有学生的学号、姓名、出生日期。Use xscjSelect 学号学号,姓名姓名,出生日期出生日期From 学生表2011劳东青（一）简单单表查询2.2.选择表中所有列。选择表中所有列。在SELECT关键字后面列出所有列名 使用“*”表示选择所有列【例2】查询数据库XSCJ的成绩表中所有数据。法一法一：

8、use xscjselect 学号,课程号,成绩,备注from 成绩表法二法二：use xscjselect*from 成绩表2011劳东青（一）简单单表查询【例3】查询数据库xscj的学生表中涉及的学院有哪些？Use xscjSelect 学院编号From 学生表p存在问题存在问题：太多重复的行。3.3.消除结果集中的重复行（消除结果集中的重复行（P81P81）通过在select后面增加关键字 Distinct Distinct 来消除重复行。Use xscjSelect distinct 学院编号From 学生表2011劳东青（一）简单单表查询【例4】查询数据库XSCJ的学生表中学生的姓名

9、及性别。Use xscj select 姓名,性别 from 学生表p存在问题存在问题：“1”、“0”分别代表什么？有没有办法让显示的结果用“男”、“女”来代替“1”或“0”？4.4.替换查询结果中数据（替换查询结果中数据（P80P80）通过 CASE 表达式替换数据。其语法格式如下：CaseWhen 条件1 then 表达式1When 条件2 then 表达式2Else 表达式end2011劳东青（一）简单单表查询【例4】查询数据库XSCJ的学生表中学生的姓名及性别。Use xscjSelect 姓名,性别=CaseWhen 性别=1 then 男When 性别=0 then 女EndFro

10、m 学生表2011劳东青（一）简单单表查询【例5】查询学生表中所有学生的学号、姓名和总学分，对其总学分按以下规则进行替换：若总学分为空值，则替换为“尚未选课”；若总学分小于50，则替换为“不及格”；若总学分在50与52之间，则替换为“合格”；若总学分大于52，则替换为“优秀”。列标题更改为“等级”。USE XSCJSELECT 学号学号,姓名姓名,等级等级=CASE WHEN 总学分总学分 IS NULL THEN 尚未选课尚未选课WHEN 总学分总学分=50 and 总学分总学分50【例11】求选修了课程的学生总数。SELECT COUNT(DISTINCT 学号学号)FROM 成绩表201

11、1劳东青（一）简单单表查询8.8.限制结果集返回函数限制结果集返回函数p当返回的结果集行数很多时，可用top语句限制返回的行数。其语法格式如下：TOP TOP n n percent percentp返回 N 行或 n%行记录。【例12】查询数据库xscj的学生表中的学生信息，分别返回前5个和前5%个学生的信息。Use xscj Select top 5*from 学生表Use xscj Select top 5 percent*from 学生表2011劳东青（二）复杂查询2011劳东青（二）复杂查询条件查询pWHEREWHERE子句子句用于指定查询条件，用于指定查询条件，因而进行条件查询，实

12、际上就是在简单单表查询的基础上，增加where子句，限制返回的行的搜索条件。（1 1）比较两个表达式的值的查询）比较两个表达式的值的查询p9个比较运算符：=（等于）、（等于）、（小于）、（小于）、=（大于）、（大于）、=（大于等于）、（大于等于）、（不等于）、（不等于）、!=!=（不等（不等于）、于）、!（不大于）（不大于）【例13】查询数据库xscj的学生表中总学分不大于50的学生信息。Use xscj select*from 学生表 where 总学分!502011劳东青（二）复杂查询条件查询（2 2）空值比较查询）空值比较查询pIs nullIs null，用于指定一个表达式的值为空值。

13、pIs not nullIs not null，用于指定一个表达式的值不为空值。【例14】查询数据库xscj的学生表中备注不为空的学生信息。Use xscj select*from 学生表Where 备注 is not null2011劳东青（二）复杂查询条件查询（3 3）模糊查询）模糊查询pLikeLike，用于查询与给定字符串用于查询与给定字符串匹配匹配的记录。的记录。pNot likeNot like，用于查询与给定字符串用于查询与给定字符串不匹配不匹配的记录。的记录。pnotlike表达式中的给定字符串通常带通配符。通配符说明%代表0个或多个字符_（下划线）代表单个字符指定范围（如a-

14、f、0-9、09、012345）或集合（如abcdef、赵钱孙李）中的任何单个字符指定不属于范围（如a-f、0-9）或集合（如abcdef）的任何单个字符2011劳东青（二）复杂查询条件查询（3 3）模糊查询）模糊查询【例15】查询数据库xscj的学生表中姓“王”的学生的学号、姓名、总学分。Use xscj select 学号,姓名,总学分 from 学生表Where 姓名 like 王%【例16】查询数据库xscj的学生表中学号倒数第3个数字为1，且倒数第1个数在15之间的学生信息。Use xscj select*from 学生表Where 学号 like%1_12345 /*1-5*/20

15、11劳东青（二）复杂查询条件查询（4 4）使用）使用 AND AND 和和 OR OR 运算符运算符pANDAND，用于查询同时满足AND连接的两个条件的行。pOROR，用于查询满足其中任意一个条件的行。p共同点共同点：用来联结多个查询条件。【例17】查询学生表中总学分在50以上的女学生的姓名、学分。Select 姓名,学分 from 学生表Where 性别=0 and 总学分50【例18】查询学生表中总学分在50以上或性别为女的学生的姓名、学分。Select 姓名,学分 from 学生表Where 性别=0 or 总学分502011劳东青（二）复杂查询条件查询（5 5）确定范围）确定范围pB

16、etweenBetween，在两者之间。多用于数值数据的范围比较。pNot BetweenNot Between，不在之间。多用于数值数据类型的范围比较。Not Between Not Between 表达式表达式1 and 1 and 表达式表达式2 2【例19】查询学生表中总学分在50到53之间的学生信息。Select*from 学生表 where 总学分 between 50 and 53【例20】查询学生表中总学分大于53或小于50的学生信息。Select*from 学生表 where 总学分 not between 50 and 532011劳东青（二）复杂查询条件查询（6 6）确定

17、集合）确定集合pInIn，在集合之内。多用于非数值数据类型的范围比较。In(In(表达式表达式1,1,表达式表达式22表达式表达式n n)pNot InNot In，不在集合之内。用于非数值数据的范围比较。Not In(Not In(表达式表达式1,1,表达式表达式2,2,表达式表达式n)n)【例21】查询考生信息表中信息学院、经管学院、生命学院的考生信息。Select*from 学生表 where 学员名 in(信息学院,经管学院,生命学院)【例22】查询考生信息表中学院名不为信息学院、经管学院、生命学院的考生信息信息。Select*from 学生表 where 学院名 not in(信息学

18、院,经管学院,生命学院)2011劳东青（二）复杂查询条件查询pContainsContains安装全文索引组建的参考资料http:/ BYGROUP BY子句：子句：作用对象：查询的中间结果表作用：细化聚集函数的作用对象未对查询结果分组，聚集函数将作用于整个查询结果对查询结果分组后，聚集函数将分别作用于每个组 分组方法：按指定的一列或多列一列或多列值分组，值相等的为一组使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和聚集函数2011劳东青（二）复杂查询分组查询pGROUP GROUP BYBY的语法格式的语法格式：group bygroup by 分组表达式 with

19、 with rolluprollup|cubecubewith rollup：当按照N列的值分组时，返回N+1个分组列的统计行。cube：当按照N列的值分组时，返回分组列各组合的统计行。2011劳东青（二）复杂查询分组查询【例23】分别统计各门课程的选课人数。use xscjselect 课程号,count(*)from 成绩表group by 课程号2011劳东青（二）复杂查询分组查询【例24】分别统计数据库xscj的学生表中男学生和女学生的总数。use xscjselect 性别=casewhen 性别=1 then 男else 女end,count(*)from 学生表group by

20、性别2011劳东青（二）复杂查询分组查询【例25】统计数据库xscj的学生表中各个专业的男生人数、女生人数及学生总数use xscjselect 专业,性别,count(*)AS 人数from 学生表group by 专业,性别 with rollup2011劳东青（二）复杂查询分组查询【例26】统计数据库xscj的学生表中各个专业的男生人数、女生人数、学生总数，及男生总数、女生总数、学生总人数use xscjselect 专业,性别,count(*)AS 人数from 学生表group by 专业,性别 with cube2011劳东青（二）复杂查询分组查询pHavingHaving子句子句

21、用于对分组数据进行进一步的筛选，用法与where字句类似HAVING子句与WHERE子句的区别：所处位置不同：所处位置不同：having子句必须在group by语句之后，where子句必须在group by语句之前。作用对象不同作用对象不同：WHERE子句作用于基表或视图，从中选择满足条件的行；HAVING子句作用于组，从中选择满足条件的组。Having子句可包含聚合函数聚合函数，Where子句不可以。2011劳东青（二）复杂查询分组查询【例27】统计数据库xscj的学生表中“工商管理”专业的男生人数、女生人数、学生总数。use xscjselect 专业,性别,count(*)AS 人数f

22、rom 学生表group by 专业,性别 with rolluphaving 专业=工商管理2011劳东青（二）复杂查询2011劳东青（二）复杂查询排序pORDER BYORDER BY语句语句可以按一个或多个属性列排序Order by 排序表达式 ASC|DESC分组表达式：列名、表达式、正整数（映射表中对应位置上的列）ASC升序；DESC降序；缺省值为升序当排序列含空值时ASCASC：排序列为空值的元组最后显示DESCDESC：排序列为空值的元组最先显示2011劳东青（二）复杂查询排序【例28】将学生表中的学生按出生时间顺序排序。use xscj select*from 学生表 orde

23、r by 出生时间【例29】将成绩表中的学生按课程及成绩从高到低排序。use xscj select*from 成绩表order by 课程号,成绩2011劳东青4.2 数据库的查询2011劳东青（三）多表查询2011劳东青（三）多表查询连接查询p连接查询连接查询：同时涉及多个表的查询，结果通常是含有参加连接运算的两个表（或多个表）的指定列的表。p连接条件中的各连接字段类型必须是可比的，但名字不必是相同的。p连接查询包括连接查询包括：等值连接、自然连接、一般连接、内连接、外连接、左连接、右连接等。p连接查询中的连接条件通过WHERE子句表达，连接条件和元组选择条件之间用AND（与）操作符衔接。

24、p在连接查询中，用来连接两个表的条件称为连接条件连接条件或连连接谓词接谓词。2011劳东青（三）多表查询连接查询1 1、等值连接和非等值连接、等值连接和非等值连接 一般格式：.p比较运算符有：=、=、=802011劳东青（三）多表查询连接查询2 2、自然连接、自然连接当等值连接中的连接列相同，并且在SELECT子句中去除了重复列时，则该连接操作为自然连接自然连接。【例32】选修了课程的学生信息及其选课信息。SELECT 学号学号,姓名姓名,性别性别,出生时间出生时间,总学分总学分,备注备注,课程号课程号,成绩成绩,备注备注 FROM 学生表，成绩表WHERE 学生表.学号=成绩表.课程号201

25、1劳东青（三）多表查询连接查询3 3、自连接、自连接:一个表与其自己进行连接需要给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀【例33】查询选修了101号课程和102号课程的学生学号 SELECT a.学号 FROM 成绩表 a,成绩表 b WHERE a.学号=b.学号 AND a.课程号=101 AND b.课程号=1022011劳东青（三）多表查询连接查询4 4、内连接、内连接用INNER关键字指定的连接。按照ON指定的连接条件，返回满足条件的行返回满足条件的行。SELECT SELECT FROM FROM 1 InnerInner JOINJOIN 2 ON ON

26、 连接条件连接条件【例34】在学生表与成绩表之间通过学号做内连接，显示已选课的学生的基本信息和他们的选课信息。SELECT*FROM 学生表 JOIN 成绩表 ON 学生表.学号=成绩表.学号2011劳东青（三）多表查询连接查询5 5、外连接、外连接外连接操作以指定表为连接主体以指定表为连接主体，将主体表中满足条件、将主体表中满足条件、不满足连接条件的元组一并输出不满足连接条件的元组一并输出。包括:左外连接、右外连接、完全外连接SELECT SELECT FROM FROM LEFT|RIGHT|FULL OUTER LEFT|RIGHT|FULL OUTER JOIN JOIN ONON 连

27、接条件连接条件 2011劳东青（三）多表查询连接查询p左外连接、右外连接、完全外连接的左外连接、右外连接、完全外连接的区别区别z共同点共同点：结果表中都包括满足条件的行。z左外连接（左外连接（LEFT OUTER JOINLEFT OUTER JOIN）：）：还包括左表所有的行z右外连接（右外连接（RIGHT OUTER JOINRIGHT OUTER JOIN）：）：还包括右表所有的行z完全外连接（完全外连接（FULL OUTER JOINFULL OUTER JOIN）：）：还包括两个表的所有行2011劳东青（三）多表查询连接查询5 5、外连接、外连接【例35】查询每个学生及其选修课程的情

28、况包括没有选修课程的学生 SELECT 学生表.学号，姓名，性别，出生时间，总学分,课程号，成绩 FROM 学生表 LEFT OUT JOIN LEFT OUT JOIN 成绩表 ONON (学生表.学号=成绩表.学号)等值连接中的内外连接也可在where子句中实现：内连接 Where 学生表.学号=成绩表.学号 左外连接 Where 学生表.学号*=成绩表.学号 右外连接 Where 学生表.学号=*成绩表.学号 2011劳东青（三）多表查询2011劳东青（三）多表查询嵌套查询p嵌套查询概述嵌套查询概述（P91P91）z一个SELECT-FROM-WHERE语句称为一个查询块查询块z将一个查

29、询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询嵌套查询 SELECT 姓名 /*外层查询/父查询*/FROM 学生表 WHERE 学号 IN （SELECT 学号 /*内层查询/子查询*/FROM 成绩表 WHERE 课程号=103）；2011劳东青（三）多表查询嵌套查询p子查询即可嵌套在SELECT语句中使用，也可嵌套在INSERT、UPDATE及DELETE语句中使用。p子查询中，通常不允许使用ORDER BY语句。p子查询通常与IN谓词、比较运算符、ANY、ALL、EXIST等谓词结合使用。2011劳东青（三）多表查询嵌套查询1 1、带有、带有ININ

30、谓词的子查询谓词的子查询pIN子查询用于进行一个给定值是否在子查询结果集中的判断，格式为：expression NOT IN (子查询)【例36】查找选修了课程号为206的课程的学生情况。use xscj select*from 学生表where 学号 in(select 学号 from 成绩表 where 课程号=206)等价于select 学生表.*from 学生表,成绩表 where 课程号=206 and 学生表.学号=成绩表.学号2011劳东青（三）多表查询嵌套查询【例37】查找未选修离散数学离散数学的学生姓名。思路分析：select*from 学生表 where 学号 not in

31、(select 学号 from 成绩表 where 课程号 in(select 课程号 from 课程表 where 课程名=离散数学)蓝色模块等价于：select 学号 from 成绩表,课程表 where 课程名=离散数学 and 课程表.课程号=成绩表.课程号2011劳东青（三）多表查询嵌套查询2 2、带有比较运算符的子查询、带有比较运算符的子查询p当能确切知道内层查询返回单值时，可用比较运算符（，=，=，!=或）。【例37】查找未选修离散数学离散数学的学生情况。select*from 学生表 where 学号 not in(select 学号 from 成绩表 where 课程号=(s

32、elect 课程号 from 课程表 where 课程名=离散数学)2011劳东青（三）多表查询嵌套查询【例38】查找比课程号为102的课程最高分还高的其他课程的选课情况。Use xscjSelect*from 成绩表 where 成绩(Select max(成绩)from 成绩表 where 课程号=102)2011劳东青（三）多表查询嵌套查询3 3、带有、带有ANYANY（SOMESOME）或）或ALLALL谓词的子查询谓词的子查询pAny、some、all通常跟比较运算符结合使用。pANYANY（SOMESOME）：表示表达式只要与子查询结果集中的某个值满足比较的关系，就返回TRUE，否

33、则返回FALSE。pALLALL：指定表达式要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时，才返回TRUE，否则返回FALSE。2011劳东青（三）多表查询嵌套查询p比较运算符与比较运算符与ANYANY、ALLALL结合使用时的含义结合使用时的含义 ANY大于子查询结果中的某个值 ALL大于子查询结果中的所有值 ANY小于子查询结果中的某个值 =ANY大于等于子查询结果中的某个值 =ALL大于等于子查询结果中的所有值=ANY小于等于子查询结果中的某个值 =ALL小于等于子查询结果中的所有值=ANY等于子查询结果中的某个值 =ALL等于子查询结果中的所有值（通常没有实际

34、意义）!=（或）ANY不等于子查询结果中的某个值!=（或）ALL不等于子查询结果中的任何一个值2011劳东青（三）多表查询嵌套查询pANY（或SOME），ALL谓词与聚集函数、IN谓词的等价转换关系 =或或!=ANY IN -MAXMIN=MINALL -NOT IN MINMAX=MAX2011劳东青（三）多表查询嵌套查询【例39】查找比所有信息学院的学生年龄都大的学生。Use xscjSelect*from 学生表 where 出生时间 all all(Select 出生时间From 学生表 where 学院编号=(select 学院编号 from 学院表where 学院名=信息学院)20

35、11劳东青（三）多表查询嵌套查询【例40】查找比信息学院的某一学生年龄大的学生。Use xscjSelect*from 学生表 where 出生时间 any any(Select 出生时间From 学生表 where 学院编号=(select 学院编号 from 学院表where 学院名=信息学院)等价于：Select*from 学生表 where 出生年月(Select max(出生年月)From 学生表 where 学院编号=(select 学院编号 from 学院表 where 学院名=信息学院)2011劳东青（三）多表查询嵌套查询p带有带有EXISTSEXISTS谓词的子查询谓词的子查

36、询v1.EXISTS谓词n带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则外层的WHERE子句返回真值若内层查询结果为空，则外层的WHERE子句返回假值n由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义v2.NOT EXISTS谓词若内层查询结果非空，则外层的WHERE子句返回假值若内层查询结果为空，则外层的WHERE子句返回真值2011劳东青（三）多表查询嵌套查询【例41】查找选修206号课程的学生姓名。p思路分析思路分析：z本查询涉及学生表和成绩表z在学

37、生表中依次取每个元组的学号值，用此值去检查成绩表z若成绩表中存在这样的元组，其学号值等于此学生表中的学号值，并且其课程号=206，则取此学生表的姓名值送入结果关系SELECT SELECT 姓名姓名 FROM FROM 学生表学生表 WHERE EXISTS(WHERE EXISTS(SELECT *SELECT *FROM FROM 成绩表成绩表WHERE WHERE 学号学号 =学生表学生表.学号学号 AND AND 课程课程号号 =206=206)2011劳东青（三）多表查询嵌套查询【例42】查找选修了全部课程的同学的姓名。p解决思路解决思路：将问题“查找选修了全部课程的同学”，转变成“

38、查找没有一门课不选修的同学”。p分析：分析：1.所有未选过的课程未选过的课程的数据集：select*from 课程表 where not exists(select*from 成绩表 where 课程号=课程.课程号)2.增加一个条件增加一个条件：select*from 课程表 where not exists(select*from 成绩表 where 学号=学号 and 课程号=课程表.课程号)所有没被某位学号为 学号 的学生选过的课程的记录集（学号学生的未选课程）：2011劳东青2022/11/1379（三）多表查询嵌套查询3.遍历每一个主查询的学号，每一个学号都按第二筛选方法筛选出：没

39、有未选课程的学生的学号。（不包括在第二步中查询出的“有未学课程的学号的记录集”中的记录。)p所以，【例42】的查询语句如下：SELECT 姓名 FROM 学生表 WHERE NOT EXISTS(SELECT *FROM 课程表WHERE NOT EXISTS(SELECT *FROM 成绩表WHERE 学号=学生表.学号 AND 课程号=课程表.课程号)2011劳东青（三）多表查询2011劳东青（三）多表查询集合查询p集合操作的种类并操作并操作UNIONUNION交操作交操作INTERSECTINTERSECT差操作差操作EXCEPTEXCEPT语法格式：查询查询 union all|int

40、ersect|except union all|intersect|except 查询查询p注意：参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同 2011劳东青（三）多表查询集合查询p1 1、并操作、并操作UNIONUNION用于将两个或多个SELECT查询的结果合并成一个结果集。UNION：将多个查询结果合并起来时，系统自动去掉重复元组。UNION ALL：将多个查询结果合并起来时，保留重复元组。【例43】查找学号为081101和学号为081210的两位同学的信息。SELECT*FROM 学生表 WHERE 学号=081101UNION ALLSELECT*FROM 学生

41、表 WHERE 学号=0812102011劳东青（三）多表查询集合查询【例44】查询学院编号为01的学生及年龄不大于19岁的学生。SELECT*FROM 学生表 WHERE 学院编号=01 UNION SELECT*FROM 学生表 WHERE year(getdate()-year(出生时间)42INTERSECTSELECT*FROM 学生表 WHERE 性别=12011劳东青（三）多表查询集合查询【例48】查询既选修课程101，又选修课程102的学生名单。Use xscjSelect 学号 from 成绩表 where 课程号=101IntersectSelect 学号 from 成绩表

42、 where 课程号=1022011劳东青（三）多表查询2011劳东青（三）多表查询Into子句p使用INTO子句可以将SELECT查询所得的结果保存到一个新建的表中。INTO子句的格式为：INTO 新表名 p包含INTO子句的SELECT语句执行后所创建的表的结构由SELECT所选择的列决定，新创建的表中的记录由SELECT的查询结果决定，若SELECT的查询结果为空，则创建一个只有结构而没有记录的空表。2011劳东青（三）多表查询Into子句【例49】由学生表创建“信息学院学生”表，包括学号和姓名。SELECT 学号,姓名INTO 信息学院学生FROM 学生表WHERE 学院编号=（sel

43、ect 学院编号 from 学院表 where 学院名=信息学院）2011劳东青2022/11/1391主要内容主要内容4.1 关系运算关系运算4.2 数据库的查询数据库的查询4.3 视图视图4.4 游标游标2011劳东青2022/11/13924.3 视图p概念：z视图是保存在数据库中的SELECT查询。p特点：z不是真实存在的基本表，而是从一个或几个基本表（或视图）导出的虚拟的表（简称虚表）。z只存放视图的定义，不存放视图对应的数据。视图中的数据在引用视图时，由定义视图的查询动态生成。z基表中的数据发生变化，从视图中查询出的数据也随之改变。2011劳东青2022/11/1393视图的操作p

44、视图的创建p视图的修改p视图的查询p视图的更新p定义基于该视图的新视图2011劳东青2022/11/1394视图的创建p视图的创建方法有两种：z使用企业管理器创建视图z使用CREATE VIEW语句创建视图（一）使用企业管理器创建视图z在企业管理器中，展开指定数据库，用鼠标右击“视图”目录，在弹出的快捷菜单中选择“新建视图”，打开“视图设计器”窗口。在此窗口中创建视图。2011劳东青2022/11/1395视图的创建（二）使用CREATE VIEW语句创建视图pCREATE VIEW 的语法格式：CREATE VIEW 视图名 (列名 ,.n )WITH ENCRYPTION AS SELEC

45、TSELECT语句语句 WITH CHECK OPTION2011劳东青2022/11/1396视图的创建p参数说明：参数说明：z视图名：视图的名称，必须符合标识符的命名规则。z列名：视图中的列名称，要么省略，要么全部指定。省略时，则采用SELECT语句产生的列名作为视图的列。当列是从算术表达式、函数或常量派生的，两个或更多的列可能会具有相同的名称（通常是因为连接），或者视图中的某列被需要赋予了不同于派生来源列的名称时，需要指定列名。2011劳东青2022/11/1397视图的创建p参数说明：参数说明：zWITH ENCRYPTION：对包含在系统表syscomments内的CREATE VI

46、EW语句文本进行加密。zSELECT语句：用于创建视图的SELECT语句，利用SELECT语句可以从表或视图中选择列构成新视图的列。zWith check option：表示对视图进行update、insert和delete操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）2011劳东青2022/11/1398视图的创建【例50】建立信息学院学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息学院的学生。Create View 信息学生VIEWAsSelect*from 学生表 where 学院编号 in（select 学员编号 from 学院表 whe

47、re 学院名称=信息学院）WITH CHECK OPTION2011劳东青2022/11/1399视图的创建【例51】创建工商管理专业学生的平均成绩视图KC_AVG，包括学号（在视图中列名为Snum）和平均成绩（在视图中列名为score_avg）。Create View KC_AVG(Snum,score_avg)AsSelect 学号,avg(成绩)from 成绩表 Group by 学号2011劳东青2022/11/13100视图的修改p视图结构的修改有两种方法：z通过企业管理器。右键单击视图，在快捷菜单中选择“设计”菜单，进入视图修改窗口。（操作与创建相似。）z通过 ALTER VIEW

48、 命令。ALTER VIEW 语法格式如下：ALTER VIEW 视图名(列名,.n)WITH ENCRYPTION AS SELECT语句2011劳东青2022/11/13101视图的修改【例51】将信息学生VIEW修改为只包含计算机专业学生的学号、姓名和总成绩。Create view 信息简VIEW Asselect 学号 from 信息学生VIEW where 专业=计算机2011劳东青2022/11/13102视图的删除p视图的删除有两种方法：z通过企业管理器。右键单击视图，在快捷菜单中选择“删除”菜单。z通过 DROP VIEW 命令。ALTER VIEW 语法格式如下：DROP V

49、IEW 视图名(列名,.n)【例52】使用DROP VIEW 命令删除视图KC_AVG。Drop view kc_avg 2011劳东青2022/11/13103视图的查询p视图定义后，即可像查询基本表那样进行查询。【例53】查找工商管理专业平均成绩在80分以上的学生的学号和平均成绩。Select*from KC_AVG where score_avg 80p注意：z在使用视图查询时，若其关联的基本表中添加了新字段，则必须重新创建视图才能查询到新字段。z如果与视图相关联的表或视图被删除，则该视图将不能再使用。2011劳东青2022/11/13104视图的更新p视图的更新操作包括数据的插入、修改

50、和删除。p要通过视图更新基本表数据，必须保证视图是可更新视图。一个可更新视图可以是以下情形之一：（1）满足以下条件的视图：z创建视图的SELECT语句中没有聚合函数，且没有TOP、GROUP BY、UNION子句及DISTINCT关键字。z创建视图的SELECT语句中不包含从基本表列通过计算所得的列。z创建视图的SELECT语句的FROM子句中至少要包含一个基本表。2011劳东青2022/11/13105视图的更新p一个可更新视图可以是以下情形之一：（2）可更新的分区视图z在实现分区视图之前，必须先实现水平分区表。原始表被分成若干个较小的成员表，每个成员表包含与原始表相同数量的列，并且每一列具