数据库的查询和视图.ppt

《数据库的查询和视图.ppt》由会员分享，可在线阅读，更多相关《数据库的查询和视图.ppt（126页珍藏版）》请在三一办公上搜索。

1、第4章 数据库的查询和视图,劳东青信息工程学院2011年4月,主要内容,4.1 关系运算,4.2 数据库的查询,4.3 视图,4.4 游标,4.1 关系运算,什么是关系运算？教材P2中提到有种数据模型为关系模型。其特点为：以二维表格（即关系表）的形式组织数据库中的数据。因此，可将关系运算理解为表的运算。关系运算的特点：运算的对象和结果都是表。关系运算的分类：传统的集合运算：并、差、交等专门的关系运算：选择、投影、连接等,4.1 关系运算,选 择（selection）又称限制，是一种单目运算。选择运算用于按给定的条件，从表中选出满足条件的行（即记录），形成一个新表作为运算结果。记为：F(R),只

2、涉及到单个操作数的运算,选择运算符,条件表达式,被操作的表,4.1 关系运算,例，假设存在表T,例：假设要在T表中找出T120的行（或记录）形成一个新表，则运算式为？T120(T),4.1 关系运算,投 影（Projection）单目运算。用于从表中选出指定的属性值组成一个新表。投影操作主要是从列的角度进行运算。记为：A(R),投影运算符,属性名（即列名）,被操作的表,4.1 关系运算,例，假设存在表T,例：假设要查询T表中T1、T2、T5的值，即要求在T表中对T1、T2、T5进行投影，则运算式为？T1,T2,T5(T),4.1 关系运算,连 接（JOIN）也称为连接，是一种双目运算。用于把两

3、个表中的行按照给定的条件进行拼接而形成新表。记为：RS,F是条件,R、S是被操作的表,F,4.1 关系运算,两类常用连接运算等值连接：要求两个表的某些列值相等的连接，记为：RS自然连接：特殊的等值连接，要求连接的两个表必须具有共同的属性（列），并且必须在结果中把重复的属性列去掉。记为：RS,A=B,4.1 关系运算,例，假设存在关系R，关系S,一般连接、等值连接、自然连接,4.1 关系运算,一般连接将关系R与S连接起来，要求关系R中C列的值小于关系S中E列的值。,4.1 关系运算,等值连接将关系R与S连接起来，要求关系R中B列的值等于关系S中B列的值。,4.1 关系运算,自然连接将关系R与S连

4、接起来，要求关系R中B列的值等于关系S中B列的值。,RS,主要内容,4.1 关系运算,4.2 数据库的查询,4.3 视图,4.4 游标,4.2 数据库的查询,Select的语法格式：SELECT，INTO FROM，WHERE GROUP BY HAVING ORDER BY ASC|DESC；,4.2 数据库的查询,SELECT子句：指定要显示的属性列FROM子句：指定查询对象(基本表或视图)WHERE子句：指定查询条件GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。HAVING短语：筛选出只有满足指定条件的组，只能跟在GROUP B

5、Y子句后。ORDER BY子句：对查询结果表按指定列值的升序或降序排序。INTO子句：创建新表，并将查询结果插入新表。,4.2 数据库的查询,（一）简单单表查询,简单单表查询语句只包括SELECT子句和FROM子句。1.选择表中的若干列。不同列之间要用英文逗号隔开。【例1】查询数据库XSCJ的学生表中所有学生的学号、姓名、出生日期。Use xscjSelect 学号,姓名,出生日期From 学生表,（一）简单单表查询,2.选择表中所有列。在SELECT关键字后面列出所有列名 使用“*”表示选择所有列【例2】查询数据库XSCJ的成绩表中所有数据。法一：use xscjselect 学号,课程号,

6、成绩,备注from 成绩表法二：use xscjselect*from 成绩表,（一）简单单表查询,【例3】查询数据库xscj的学生表中涉及的学院有哪些？Use xscjSelect 学院编号From 学生表存在问题：太多重复的行。3.消除结果集中的重复行（P81）通过在select后面增加关键字 Distinct 来消除重复行。Use xscjSelect distinct 学院编号From 学生表,（一）简单单表查询,【例4】查询数据库XSCJ的学生表中学生的姓名及性别。Use xscj select 姓名,性别 from 学生表存在问题：“1”、“0”分别代表什么？有没有办法让显示的结果

7、用“男”、“女”来代替“1”或“0”？4.替换查询结果中数据（P80）通过 CASE 表达式替换数据。其语法格式如下：CaseWhen 条件1 then 表达式1When 条件2 then 表达式2Else 表达式end,（一）简单单表查询,【例4】查询数据库XSCJ的学生表中学生的姓名及性别。Use xscjSelect 姓名,性别=CaseWhen 性别=1 then 男When 性别=0 then 女EndFrom 学生表,（一）简单单表查询,【例5】查询学生表中所有学生的学号、姓名和总学分，对其总学分按以下规则进行替换：若总学分为空值，则替换为“尚未选课”；若总学分小于50，则替换为“

8、不及格”；若总学分在50与52之间，则替换为“合格”；若总学分大于52，则替换为“优秀”。列标题更改为“等级”。,USE XSCJSELECT 学号,姓名,等级=CASE WHEN 总学分 IS NULL THEN 尚未选课WHEN 总学分=50 and 总学分=52 THEN 合格ELSE 优秀ENDFROM 学生表,（一）简单单表查询,【例6】查询数据库XSCJ的学生表中学生的姓名及年龄。存在问题：学生表中仅有出生日期列，没有年龄列。5.计算列值（P81）Use xscjSelect 姓名,year(2011-1-1)-year(出生日期)From 学生表可使用的算术运算符：+（加）、-（

9、减）、*（乘）、/（除）、%（取余）。除%不适用与money和smallmoney之外，其他运算符皆适用于数值数据类型（int、float等）、money和smallmoney数据类型。(P81),Year函数用于提取日期数据中的年份。见P94。,（一）简单单表查询,例6中存在问题：结果集中产生的新列的列标题不直观。6.定义列别名（P78）通过 AS 关键字指定列标题名：列名 AS 别名计算列中，通过赋值号=来指定列标题名：别名=表达式【例7】查询数据库XSCJ的学生表中学生的姓名及年龄，并指定年龄列的列标题为“年龄”。法一：Select 姓名,2011-year(出生日期)AS 年龄 fro

10、m 学生表法二：Select 姓名,年龄=2011-year(出生日期)from 学生表,（一）简单单表查询,7.聚合函数（P82）目标列表达式中，除了可以进行加减乘除等运算之外，还可以包含所谓的聚合函数。,（一）简单单表查询,（1）SUM和AVG函数SUM总和；AVG平均值语法格式：SUM/AVG(expression)Expression：常量、列、函数或表达式。数据类型：数值数据类型、货币数据类型。SUM/AVG忽略NULL值。【例8】求数据库XSCJ的成绩表中学生的成绩总和以及平均值。Use xscjSelect sum(成绩)总和,avg(成绩)平均值From 成绩表,（一）简单单表

11、查询,（2）MAX和MIN函数MAX最大值；MIN最小值语法格式：max/min(expression)Expression：常量、列、函数或表达式。数据类型：数值、时间、字符数据类型。MIN/MAX忽略NULL值。【例8】求数据库XSCJ的成绩表中学生成绩的最高分以及最低分。Use xscjSelect max(成绩)最大值,min(成绩)最小值From 成绩表,（一）简单单表查询,（3）COUNT函数用于统计满足条件的行（记录）数或总行（记录）数。语法格式：COUNT(ALL|DISTINCT expression|*)Expression：表达式。数据类型是除text、image或nte

12、xt之外的任何类型。COUNT忽略NULL值。【例8】求数据库XSCJ的学生表中学生的总人数。Use xscjSelect count(*)总人数,count(学号)总数From 成绩表,（一）简单单表查询,【例9】统计备注不为空的学生数。SELECT COUNT(备注)AS 备注不为空的学生数 FROM 学生表;【例10】统计总学分在50分以上的人数。SELECT COUNT(总学分)AS 总学分在50分以上的人数 FROM 学生表 WHERE 总学分50【例11】求选修了课程的学生总数。SELECT COUNT(DISTINCT 学号)FROM 成绩表,（一）简单单表查询,8.限制结果集返

13、回函数当返回的结果集行数很多时，可用top语句限制返回的行数。其语法格式如下：TOP n percent返回 N 行或 n%行记录。【例12】查询数据库xscj的学生表中的学生信息，分别返回前5个和前5%个学生的信息。Use xscj Select top 5*from 学生表Use xscj Select top 5 percent*from 学生表,（二）复杂查询,（二）复杂查询条件查询,WHERE子句用于指定查询条件，因而进行条件查询，实际上就是在简单单表查询的基础上，增加where子句，限制返回的行的搜索条件。（1）比较两个表达式的值的查询9个比较运算符：=（等于）、（大于）、=（大于

14、等于）、（不等于）、!=（不等于）、!（不大于）【例13】查询数据库xscj的学生表中总学分不大于50的学生信息。Use xscj select*from 学生表 where 总学分!50,（二）复杂查询条件查询,（2）空值比较查询Is null，用于指定一个表达式的值为空值。Is not null，用于指定一个表达式的值不为空值。【例14】查询数据库xscj的学生表中备注不为空的学生信息。Use xscj select*from 学生表Where 备注 is not null,（二）复杂查询条件查询,（3）模糊查询Like，用于查询与给定字符串匹配的记录。Not like，用于查询与给定字符

15、串不匹配的记录。notlike表达式中的给定字符串通常带通配符。,（二）复杂查询条件查询,（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*/,（二）复杂查询条件查询,（4）使用 AND 和 OR 运算符AND，用于查询同时满足AND连接的两个条件的行。OR，用

16、于查询满足其中任意一个条件的行。共同点：用来联结多个查询条件。【例17】查询学生表中总学分在50以上的女学生的姓名、学分。Select 姓名,学分 from 学生表Where 性别=0 and 总学分50【例18】查询学生表中总学分在50以上或性别为女的学生的姓名、学分。Select 姓名,学分 from 学生表Where 性别=0 or 总学分50,（二）复杂查询条件查询,（5）确定范围Between，在两者之间。多用于数值数据的范围比较。Not Between，不在之间。多用于数值数据类型的范围比较。Not Between 表达式1 and 表达式2【例19】查询学生表中总学分在50到53

17、之间的学生信息。Select*from 学生表 where 总学分 between 50 and 53【例20】查询学生表中总学分大于53或小于50的学生信息。Select*from 学生表 where 总学分 not between 50 and 53,（二）复杂查询条件查询,（6）确定集合In，在集合之内。多用于非数值数据类型的范围比较。In(表达式1,表达式2表达式n)Not In，不在集合之内。用于非数值数据的范围比较。Not In(表达式1,表达式2,表达式n)【例21】查询考生信息表中信息学院、经管学院、生命学院的考生信息。Select*from 学生表 where 学员名 in(

18、信息学院,经管学院,生命学院)【例22】查询考生信息表中学院名不为信息学院、经管学院、生命学院的考生信息信息。Select*from 学生表 where 学院名 not in(信息学院,经管学院,生命学院),（二）复杂查询条件查询,Contains安装全文索引组建的参考资料http:/,（二）复杂查询,（二）复杂查询分组查询,GROUP BY子句：作用对象：查询的中间结果表作用：细化聚集函数的作用对象未对查询结果分组，聚集函数将作用于整个查询结果对查询结果分组后，聚集函数将分别作用于每个组 分组方法：按指定的一列或多列值分组，值相等的为一组使用GROUP BY子句后，SELECT子句的列名列表

19、中只能出现分组属性和聚集函数,（二）复杂查询分组查询,GROUP BY的语法格式：group by 分组表达式 with rollup|cubewith rollup：当按照N列的值分组时，返回N+1个分组列的统计行。cube：当按照N列的值分组时，返回分组列各组合的统计行。,（二）复杂查询分组查询,【例23】分别统计各门课程的选课人数。use xscjselect 课程号,count(*)from 成绩表group by 课程号,（二）复杂查询分组查询,【例24】分别统计数据库xscj的学生表中男学生和女学生的总数。use xscjselect 性别=casewhen 性别=1 then 男

20、else 女end,count(*)from 学生表group by 性别,（二）复杂查询分组查询,【例25】统计数据库xscj的学生表中各个专业的男生人数、女生人数及学生总数use xscjselect 专业,性别,count(*)AS 人数from 学生表group by 专业,性别 with rollup,（二）复杂查询分组查询,【例26】统计数据库xscj的学生表中各个专业的男生人数、女生人数、学生总数，及男生总数、女生总数、学生总人数use xscjselect 专业,性别,count(*)AS 人数from 学生表group by 专业,性别 with cube,（二）复杂查询分组

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

22、p by 专业,性别 with rolluphaving 专业=工商管理,（二）复杂查询,（二）复杂查询排序,ORDER BY语句可以按一个或多个属性列排序Order by 排序表达式 ASC|DESC分组表达式：列名、表达式、正整数（映射表中对应位置上的列）ASC升序；DESC降序；缺省值为升序当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示,（二）复杂查询排序,【例28】将学生表中的学生按出生时间顺序排序。use xscj select*from 学生表 order by 出生时间【例29】将成绩表中的学生按课程及成绩从高到低排序。use xscj s

23、elect*from 成绩表order by 课程号,成绩,4.2 数据库的查询,（三）多表查询,（三）多表查询连接查询,连接查询：同时涉及多个表的查询，结果通常是含有参加连接运算的两个表（或多个表）的指定列的表。连接条件中的各连接字段类型必须是可比的，但名字不必是相同的。连接查询包括：等值连接、自然连接、一般连接、内连接、外连接、左连接、右连接等。连接查询中的连接条件通过WHERE子句表达，连接条件和元组选择条件之间用AND（与）操作符衔接。在连接查询中，用来连接两个表的条件称为连接条件或连接谓词。,（三）多表查询连接查询,1、等值连接和非等值连接 一般格式：.比较运算符有：=、=、=、!=

24、当连接运算符为=时，该连接操作称为等值连接，否则，为非等值连接。,（三）多表查询连接查询,【例30】查询选修了课程的学生信息及其选课信息。SELECT 学生表.*，成绩表.*FROM 学生表，成绩表WHERE 学生表.学号=成绩表.学号【例31】查找数据库xscj中选修了206号课程，并且成绩在80分以上的学生姓名成绩。SELECT 姓名，成绩 FROM 学生表，成绩表WHERE 学生表.学号=成绩表.学号 and 课程号=206 and 成绩80,（三）多表查询连接查询,2、自然连接当等值连接中的连接列相同，并且在SELECT子句中去除了重复列时，则该连接操作为自然连接。【例32】选修了课程

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

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

27、OUTER JOIN）：还包括左表所有的行右外连接（RIGHT OUTER JOIN）：还包括右表所有的行完全外连接（FULL OUTER JOIN）：还包括两个表的所有行,（三）多表查询连接查询,5、外连接【例35】查询每个学生及其选修课程的情况包括没有选修课程的学生 SELECT 学生表.学号，姓名，性别，出生时间，总学分,课程号，成绩 FROM 学生表 LEFT OUT JOIN 成绩表 ON(学生表.学号=成绩表.学号),等值连接中的内外连接也可在where子句中实现：内连接 Where 学生表.学号=成绩表.学号 左外连接 Where 学生表.学号*=成绩表.学号 右外连接 Wher

28、e 学生表.学号=*成绩表.学号,（三）多表查询,（三）多表查询嵌套查询,嵌套查询概述（P91）一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询,SELECT 姓名/*外层查询/父查询*/FROM 学生表 WHERE 学号 IN（SELECT 学号/*内层查询/子查询*/FROM 成绩表 WHERE 课程号=103）；,（三）多表查询嵌套查询,子查询即可嵌套在SELECT语句中使用，也可嵌套在INSERT、UPDATE及DELETE语句中使用。子查询中，通常不允许使用ORDER BY语句。子查询通

29、常与IN谓词、比较运算符、ANY、ALL、EXIST等谓词结合使用。,（三）多表查询嵌套查询,1、带有IN谓词的子查询IN子查询用于进行一个给定值是否在子查询结果集中的判断，格式为：expression NOT IN(子查询)【例36】查找选修了课程号为206的课程的学生情况。use xscj select*from 学生表where 学号 in(select 学号 from 成绩表 where 课程号=206)等价于select 学生表.*from 学生表,成绩表 where 课程号=206 and 学生表.学号=成绩表.学号,（三）多表查询嵌套查询,【例37】查找未选修离散数学的学生姓名。

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

31、e 课程号=(select 课程号 from 课程表 where 课程名=离散数学),（三）多表查询嵌套查询,【例38】查找比课程号为102的课程最高分还高的其他课程的选课情况。Use xscjSelect*from 成绩表 where 成绩(Select max(成绩)from 成绩表 where 课程号=102),（三）多表查询嵌套查询,3、带有ANY（SOME）或ALL谓词的子查询Any、some、all通常跟比较运算符结合使用。ANY（SOME）：表示表达式只要与子查询结果集中的某个值满足比较的关系，就返回TRUE，否则返回FALSE。ALL：指定表达式要与子查询结果集中的每个值都进行

32、比较，当表达式与每个值都满足比较的关系时，才返回TRUE，否则返回FALSE。,（三）多表查询嵌套查询,比较运算符与ANY、ALL结合使用时的含义 ANY大于子查询结果中的某个值 ALL大于子查询结果中的所有值=ANY大于等于子查询结果中的某个值=ALL大于等于子查询结果中的所有值）ANY不等于子查询结果中的某个值!=（或）ALL不等于子查询结果中的任何一个值,（三）多表查询嵌套查询,ANY（或SOME），ALL谓词与聚集函数、IN谓词的等价转换关系,（三）多表查询嵌套查询,【例39】查找比所有信息学院的学生年龄都大的学生。Use xscjSelect*from 学生表 where 出生时间

33、all(Select 出生时间From 学生表 where 学院编号=(select 学院编号 from 学院表where 学院名=信息学院),（三）多表查询嵌套查询,【例40】查找比信息学院的某一学生年龄大的学生。Use xscjSelect*from 学生表 where 出生时间 any(Select 出生时间From 学生表 where 学院编号=(select 学院编号 from 学院表where 学院名=信息学院),等价于：Select*from 学生表 where 出生年月(Select max(出生年月)From 学生表 where 学院编号=(select 学院编号 from

34、学院表 where 学院名=信息学院),（三）多表查询嵌套查询,带有EXISTS谓词的子查询1.EXISTS谓词带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则外层的WHERE子句返回真值若内层查询结果为空，则外层的WHERE子句返回假值由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义2.NOT EXISTS谓词若内层查询结果非空，则外层的WHERE子句返回假值若内层查询结果为空，则外层的WHERE子句返回真值,（三）多表查询嵌套查询,【例41】查找选修20

35、6号课程的学生姓名。思路分析：本查询涉及学生表和成绩表在学生表中依次取每个元组的学号值，用此值去检查成绩表若成绩表中存在这样的元组，其学号值等于此学生表中的学号值，并且其课程号=206，则取此学生表的姓名值送入结果关系,SELECT 姓名 FROM 学生表 WHERE EXISTS(SELECT*FROM 成绩表WHERE 学号=学生表.学号 AND 课程号=206),（三）多表查询嵌套查询,【例42】查找选修了全部课程的同学的姓名。解决思路：将问题“查找选修了全部课程的同学”，转变成“查找没有一门课不选修的同学”。分析：1.所有未选过的课程的数据集：select*from 课程表 where

36、 not exists(select*from 成绩表 where 课程号=课程.课程号)2.增加一个条件：select*from 课程表 where not exists(select*from 成绩表 where 学号=学号 and 课程号=课程表.课程号)所有没被某位学号为 学号 的学生选过的课程的记录集（学号学生的未选课程）：,（三）多表查询嵌套查询,3.遍历每一个主查询的学号，每一个学号都按第二筛选方法筛选出：没有未选课程的学生的学号。（不包括在第二步中查询出的“有未学课程的学号的记录集”中的记录。)所以，【例42】的查询语句如下：SELECT 姓名 FROM 学生表 WHERE N

37、OT EXISTS(SELECT*FROM 课程表WHERE NOT EXISTS(SELECT*FROM 成绩表WHERE 学号=学生表.学号 AND 课程号=课程表.课程号),（三）多表查询,（三）多表查询集合查询,集合操作的种类并操作UNION交操作INTERSECT差操作EXCEPT语法格式：查询 union all|intersect|except 查询注意：参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同,（三）多表查询集合查询,1、并操作UNION用于将两个或多个SELECT查询的结果合并成一个结果集。UNION：将多个查询结果合并起来时，系统自动去掉重复元组。

38、UNION ALL：将多个查询结果合并起来时，保留重复元组。【例43】查找学号为081101和学号为081210的两位同学的信息。SELECT*FROM 学生表 WHERE 学号=081101UNION ALLSELECT*FROM 学生表 WHERE 学号=081210,（三）多表查询集合查询,【例44】查询学院编号为01的学生及年龄不大于19岁的学生。SELECT*FROM 学生表 WHERE 学院编号=01 UNION SELECT*FROM 学生表 WHERE year(getdate()-year(出生时间)=19,（三）多表查询集合查询,【例45】查询选修了课程101或者选修了课程

39、102的学生。SELECT 学号 FROM 成绩表 WHERE 课程号=101 UNION SELECT 学号 FROM 成绩表 WHERE 课程号=102；,SELECT*FROM 成绩表WHERE 课程号=101 OR 课程号=102),（三）多表查询集合查询,2、交操作Intersect与差操作ExceptEXCEPT和INTERSECT用于比较两个查询的结果，返回非重复值。EXCEPT：从EXCEPT关键字左边的查询中返回右边查询没有找到的所有非重复值。INTERSECT：返回INTERSECT关键字左右两边的两个查询都返回的所有非重复值。EXCEPT或INTERSECT返回的结果集的

40、列名与关键字左侧的查询返回的列名相同。,（三）多表查询集合查询,【例46】查找学院编号为01但性别不为男的学生信息。USE XSCJSELECT*FROM 学生表 WHERE 学院编号=01EXCEPTSELECT*FROM 学生表 WHERE 性别=1【例47】查找总学分大于42且性别为男的学生信息。SELECT*FROM 学生表 WHERE 总学分42INTERSECTSELECT*FROM 学生表 WHERE 性别=1,（三）多表查询集合查询,【例48】查询既选修课程101，又选修课程102的学生名单。Use xscjSelect 学号 from 成绩表 where 课程号=101Int

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

42、号=（select 学院编号 from 学院表 where 学院名=信息学院）,主要内容,4.1 关系运算,4.2 数据库的查询,4.3 视图,4.4 游标,4.3 视图,概念：视图是保存在数据库中的SELECT查询。特点：不是真实存在的基本表，而是从一个或几个基本表（或视图）导出的虚拟的表（简称虚表）。只存放视图的定义，不存放视图对应的数据。视图中的数据在引用视图时，由定义视图的查询动态生成。基表中的数据发生变化，从视图中查询出的数据也随之改变。,视图的操作,视图的创建视图的修改视图的查询视图的更新定义基于该视图的新视图,视图的创建,视图的创建方法有两种：使用企业管理器创建视图使用CREAT

43、E VIEW语句创建视图（一）使用企业管理器创建视图在企业管理器中，展开指定数据库，用鼠标右击“视图”目录，在弹出的快捷菜单中选择“新建视图”，打开“视图设计器”窗口。在此窗口中创建视图。,视图的创建,（二）使用CREATE VIEW语句创建视图CREATE VIEW 的语法格式：CREATE VIEW 视图名(列名,.n)WITH ENCRYPTION AS SELECT语句 WITH CHECK OPTION,视图的创建,参数说明：视图名：视图的名称，必须符合标识符的命名规则。列名：视图中的列名称，要么省略，要么全部指定。省略时，则采用SELECT语句产生的列名作为视图的列。当列是从算术表

44、达式、函数或常量派生的，两个或更多的列可能会具有相同的名称（通常是因为连接），或者视图中的某列被需要赋予了不同于派生来源列的名称时，需要指定列名。,视图的创建,参数说明：WITH ENCRYPTION：对包含在系统表syscomments内的CREATE VIEW语句文本进行加密。SELECT语句：用于创建视图的SELECT语句，利用SELECT语句可以从表或视图中选择列构成新视图的列。With check option：表示对视图进行update、insert和delete操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）,视图的创建,【例50】建立信息学院学

45、生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息学院的学生。Create View 信息学生VIEWAsSelect*from 学生表 where 学院编号 in（select 学员编号 from 学院表 where 学院名称=信息学院）WITH CHECK OPTION,视图的创建,【例51】创建工商管理专业学生的平均成绩视图KC_AVG，包括学号（在视图中列名为Snum）和平均成绩（在视图中列名为score_avg）。Create View KC_AVG(Snum,score_avg)AsSelect 学号,avg(成绩)from 成绩表 Group by 学号,视图的修改,视图

46、结构的修改有两种方法：通过企业管理器。右键单击视图，在快捷菜单中选择“设计”菜单，进入视图修改窗口。（操作与创建相似。）通过 ALTER VIEW 命令。ALTER VIEW 语法格式如下：ALTER VIEW 视图名(列名,.n)WITH ENCRYPTION AS SELECT语句,视图的修改,【例51】将信息学生VIEW修改为只包含计算机专业学生的学号、姓名和总成绩。Create view 信息简VIEW Asselect 学号 from 信息学生VIEW where 专业=计算机,视图的删除,视图的删除有两种方法：通过企业管理器。右键单击视图，在快捷菜单中选择“删除”菜单。通过 DRO

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

48、图更新基本表数据，必须保证视图是可更新视图。一个可更新视图可以是以下情形之一：（1）满足以下条件的视图：创建视图的SELECT语句中没有聚合函数，且没有TOP、GROUP BY、UNION子句及DISTINCT关键字。创建视图的SELECT语句中不包含从基本表列通过计算所得的列。创建视图的SELECT语句的FROM子句中至少要包含一个基本表。,视图的更新,一个可更新视图可以是以下情形之一：（2）可更新的分区视图在实现分区视图之前，必须先实现水平分区表。原始表被分成若干个较小的成员表，每个成员表包含与原始表相同数量的列，并且每一列具有与原始表中的相应列同样的特性（如数据类型、大小、排序规则）。（

49、3）通过INSTEAD OF触发器创建的可更新视图。INSTEAD OF 触发器将在第7章介绍。,视图的更新,插入数据施用Insert语句通过视图向基本表插入数据。【例54】向信息学生VIEW插入以下记录：（081115,李敏,计算机,0,1988-3-2,50,NULL,01）INSERT INTO 信息学生VIEWVALUES(081115,李敏,计算机,0,1998-3-2,50,NULL,01)注意：当视图所依赖的基本表有多个时，不能向该视图插入数据，因为这将会影响多个基表。,视图的更新,修改数据使用UPDATE语句通过视图修改基本表的数据。【例55】将信息学生VIEW视图中所有学生的

50、总学分增加8。UPDATE 信息学生VIEWSET 总学分=总学分+3注意：若一个视图依赖于多个基本表，则依次修改该视图只能变动一个基本表的数据。,视图的更新,删除数据使用DELETE语句通过视图删除基本表数据。注意：对于依赖于多个基本表的视图，不能使用DELETE语句。【例56】删除信息学生VIEW视图中女同学的记录。Delete from 信息学生VIEW where 性别=0,定义基于该视图的新视图,SQL SERVER允许在视图之上创建视图。【例57】根据信息学生VIEW视图，创建一个只含学号、姓名、性别信息的视图。视图名为“信息VIEW”。Create view 信息VIEWAsSe