数据库的查询和视图

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,2011,劳东青,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,2011,劳东青,2024/9/12,126,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,第,4,章 数据库的查询和视图,2011,年,4,月,1,主要内容,4.1,关系运算,4.2,数据库的查询,4.3,视图,4.4,游标,2,4.1,关系运算,什么是关系运算？,教材,P2,中提到有种数据模型为关系模型。其特点为：以二维表格（即关系表）的形式组织数据库中的数据。,因此，可将关系运算理解为表的运算。,关系运算的,特点,：,运算的对象和结果都是表,。,关系运算的分类,：,传统的集合运算,：,并、差、交,等,专门的关系运算,：,选择、投影、连接,等,3,4.1,关系运算,选 择（,selection,）,又称限制，是一种,单目,运算。,选择运算用于按给定的条件，,从表中选出满足条件的行,（即记录），形成一个新表作为运算结果。,记为：,F,(R),只涉及到单个操作数的运算,选择运算符,条件表达式,被操作的表,4,4.1,关系运算,例，假设存在表,T,例：,假设要在,T,表中找出,T120,的行（或记录）形成一个新表，则运算式为 ？,T120,(T),5,4.1,关系运算,投 影（,Projection,）,单目,运算。,用于从表中,选出指定的属性值,组成一个新表。,投影操作主要是,从列的角度,进行运算。,记为：,A,(R),投影运算符,属性名（即列名）,被操作的表,6,4.1,关系运算,例，假设存在表,T,例：假设要查询,T,表中,T1,、,T2,、,T5,的值，即要求在,T,表中对,T1,、,T2,、,T5,进行投影，则运算式为 ？,T1,T2,T5,(T),T1,T2,T5,1,A1,M,2,B1,N,3,A2,O,5,D,P,20,F,Q,100,A3,N,7,4.1,关系运算,连 接（,JOIN,）,也称为,连接,，是一种,双目,运算。,用于,把两个表中的行按照给定的条件进行拼接而形成新表,。,记为,：,RS,F,是条件,R,、,S,是被操作的表,F,8,4.1,关系运算,两类常用连接运算,等值连接,：要求两个表的,某些列值相等,的连接，记为：,RS,自然连接,：,特殊的等值连接,，要求连接的两个表必须具有,共同的属性,（列），并且必须在结果中,把重复的属性列去掉,。记为：,RS,A=B,9,4.1,关系运算,例，假设存在关系,R,，关系,S,一般连接,、,等值连接,、,自然连接,10,4.1,关系运算,一般连接,将关系,R,与,S,连接起来，要求关系,R,中,C,列的值,小于,关系,S,中,E,列的值,。,CE,RS,11,4.1,关系运算,等值连接,将关系,R,与,S,连接起来，要求关系,R,中,B,列的值,等于,关系,S,中,B,列的值,。,R.B=S.B,RS,12,4.1,关系运算,自然连接,将关系,R,与,S,连接起来，要求关系,R,中,B,列的值,等于,关系,S,中,B,列的值,。,RS,13,主要内容,4.1,关系运算,4.2,数据库的查询,4.3,视图,4.4,游标,14,4.2,数据库的查询,Select,的语法格式：,SELECT,，,INTO, ,FROM,，, ,WHERE, ,GROUP BY, ,HAVING, ,ORDER BY, ASC|DESC ,；,15,4.2,数据库的查询,SELECT,子句,：指定要显示的属性列,FROM,子句,：指定查询对象,(,基本表或视图,),WHERE,子句,：指定查询条件,GROUP BY,子句,：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。,HAVING,短语,：筛选出只有满足指定条件的组，只能跟在,GROUP BY,子句后。,ORDER BY,子句,：对查询结果表按指定列值的升序或降序排序。,INTO,子句,：创建新表，并将查询结果插入新表。,16,4.2,数据库的查询,17,（一）简单单表查询,简单单表查询语句只包括,SELECT,子句和,FROM,子句。,1.,选择表中的若干列,。不同列之间要用,英文逗号,隔开。,【,例,1】,查询数据库,XSCJ,的学生表中所有学生的学号、姓名、出生日期。,Use,xscj,Select,学号,姓名,出生日期,From,学生表,18,（一）简单单表查询,2.,选择表中所有列。,在,SELECT,关键字后面列出所有列名,使用“,*,”表示选择所有列,【,例,2】,查询数据库,XSCJ,的成绩表中所有数据。,法一,：,use,xscj,select,学号,课程号,成绩,备注,from,成绩表,法二,：,use,xscj,select,*,from,成绩表,19,（一）简单单表查询,【,例,3】,查询数据库,xscj,的学生表中涉及的学院有哪些？,Use,xscj,Select,学院编号,From,学生表,存在问题,：太多重复的行。,3.,消除结果集中的重复行（,P81,）,通过在,select,后面增加关键字,Distinct,来消除重复行。,Use,xscj,Select,distinct,学院编号,From,学生表,20,（一）简单单表查询,【,例,4】,查询数据库,XSCJ,的学生表中学生的姓名及性别。,Use,xscj,select,姓名,性别,from,学生表,存在问题,：“,1,”、“,0,”分别代表什么？有没有办法让显示的结果用“男”、“女”来代替“,1,”或“,0,”？,4.,替换查询结果中数据（,P80,）,通过,CASE,表达式替换数据。其语法格式如下：,Case,When,条件,1,then,表达式,1,When,条件,2,then,表达式,2,Else,表达式,end,21,（一）简单单表查询,【,例,4】,查询数据库,XSCJ,的学生表中学生的姓名及性别。,Use,xscj,Select,姓名,性别,=,Case,When,性别,=1,then,男,When,性别,=0,then,女,End,From,学生表,22,（一）简单单表查询,【,例,5】,查询学生表中所有学生的学号、姓名和总学分，对其总学分按以下规则进行替换：若总学分为空值，则替换为“尚未选课”；若总学分小于,50,，则替换为“不及格”；若总学分在,50,与,52,之间，则替换为“合格”；若总学分大于,52,，则替换为“优秀”。列标题更改为“等级”。,USE XSCJ,SELECT,学号,姓名,等级,=,CASE,WHEN,总学分,IS NULL THEN ,尚未选课,WHEN,总学分,=50 and,总学分,50,【,例,11】,求选修了课程的学生总数。,SELECT COUNT(DISTINCT,学号,),FROM,成绩表,30,（一）简单单表查询,8.,限制结果集返回函数,当返回的结果集行数很多时，可用,top,语句限制返回的行数。其语法格式如下：,TOP,n,percent,返回,N,行或,n%,行记录。,【,例,12】,查询数据库,xscj,的学生表中的学生信息，分别返回前,5,个和前,5%,个学生的信息。,Use,xscj,Select top,5 *,from,学生表,Use,xscj,Select top,5,percent,*,from,学生表,31,（二）复杂查询,32,（二）复杂查询,条件查询,WHERE,子句,用于指定查询条件，,因而进行条件查询，实际上就是在简单单表查询的基础上，增加,where,子句，限制返回的行的搜索条件。,（,1,）比较两个表达式的值的查询,9,个比较运算符：,=,（等于）、,（小于）、,（大于）、,=,（大于等于）、,（不等于）、,!=,（不等于）、,!,（不大于）,【,例,13】,查询数据库,xscj,的学生表中总学分不大于,50,的学生信息。,Use,xscj,select,*,from,学生表,where,总学分,!,50,33,（二）复杂查询,条件查询,（,2,）空值比较查询,Is null,，用于指定一个表达式的值为空值。,Is not null,，用于指定一个表达式的值不为空值。,【,例,14】,查询数据库,xscj,的学生表中备注不为空的学生信息。,Use,xscj,select,*,from,学生表,Where,备注,is,not null,34,（二）复杂查询,条件查询,（,3,）模糊查询,Like,，,用于查询与给定字符串,匹配,的记录。,Not like,，,用于查询与给定字符串,不匹配,的记录。,notlike,表达式中的给定字符串通常带通配符。,通,配,符,说,明,%,代表,0,个或,多个,字符,_,（下划线）,代表,单个,字符, ,指定范围（如,a-f,、,0-9,、,09,、,012345,）或集合（如,abcdef,、,赵钱孙李,）中的任何,单个,字符,指定不属于范围（如,a-f,、,0-9,）或集合（如,abcdef,）的任何,单个,字符,35,（二）复杂查询,条件查询,（,3,）模糊查询,【,例,15】,查询数据库,xscj,的学生表中姓,“,王,”,的学生的学号、姓名、总学分。,Use,xscj,select,学号,姓名,总学分,from,学生表,Where,姓名,like,王,%,【,例,16】,查询数据库,xscj,的学生表中学号倒数第,3,个数字为,1,，且倒数第,1,个数在,1,5,之间的学生信息。,Use,xscj,select,*,from,学生表,Where,学号,like,%1_12345, /*1-5*/,36,（二）复杂查询,条件查询,（,4,）使用,AND,和,OR,运算符,AND,，用于查询同时满足,AND,连接的两个条件的行。,OR,，用于查询满足其中任意一个条件的行。,共同点,：用来联结多个查询条件。,【,例,17】,查询学生表中总学分在,50,以上的女学生的姓名、学分。,Select,姓名,学分,from,学生表,Where,性别,=0 and,总学分,50,【,例,18】,查询学生表中总学分在,50,以上或性别为女的学生的姓名、学分。,Select,姓名,学分,from,学生表,Where,性别,=0 or,总学分,50,37,（二）复杂查询,条件查询,（,5,）确定范围,Between,，在两者之间。多用于数值数据的范围比较。,Not Between,，不在,之间。多用于数值数据类型的范围比较。,Not Between,表达式,1 and,表达式,2,【,例,19】,查询学生表中总学分在,50,到,53,之间的学生信息。,Select,*,from,学生表,where,总学分,between,50,and,53,【,例,20】,查询学生表中总学分大于,53,或小于,50,的学生信息。,Select,*,from,学生表,where,总学分,not between,50,and,53,38,（二）复杂查询,条件查询,（,6,）确定集合,In,，在,集合之内。多用于非数值数据类型的范围比较。,In(,表达式,1,表达式,2,表达式,n,),Not In,，不在,集合之内。用于非数值数据的范围比较。,Not In (,表达式,1,表达式,2,表达式,n),【,例,21】,查询考生信息表中信息学院、经管学院、生命学院的考生信息。,Select,*,from,学生表,where,学员名,in,(,信息学院,经管学院,生命学院,),【,例22,】,查询考生信息表中学院名不为信息学院、经管学院、生命学院的考生信息信息。,Select,*,from,学生表,where,学院名,not in,(,信息学院,经管学院,生命学院,),39,（二）复杂查询,条件查询,Contains,安装全文索引组建的参考资料,创建全文索引的参考资料,40,（二）复杂查询,41,（二）复杂查询,分组查询,GROUP BY,子句：,作用对象,：,查询的中间结果表,作用：,细化聚集函数的作用对象,未对查询结果分组,，聚集函数将作用于整个查询结果,对查询结果分组后,，聚集函数将分别作用于每个组,分组方法：按指定的,一列或多列,值分组，值相等的为一组,使用,GROUP BY,子句后，,SELECT,子句的列名列表中,只能出现分组属性和聚集函数,42,（二）复杂查询,分组查询,GROUP,BY,的语法格式,：,group by,分组表达式,with,rollup,|,cube,with rollup,：当按照,N,列的值分组时，返回,N+1,个分组列的统计行。,cube,：当按照,N,列的值分组时，返回分组列,各组合,的统计行。,43,（二）复杂查询,分组查询,【,例,23,】,分别统计各门课程的选课人数。,use,xscj,select,课程号,count(,*,),from,成绩表,group by,课程号,44,（二）复杂查询,分组查询,【,例,24,】,分别统计数据库xscj的学生表中男学生和女学生的总数。,use,xscj,select,性别=,case,when,性别=1,then,男,else,女,end,count(,*,),from,学生表,group by,性别,45,（二）复杂查询,分组查询,【,例,25,】,统计数据库xscj的学生表中各个专业的男生人数、女生人数及学生总数,use,xscj,select,专业,性别,count(,*,) AS,人数,from,学生表,group by,专业,性别,with rollup,46,（二）复杂查询,分组查询,【,例,26,】,统计数据库xscj的学生表中各个专业的男生人数、女生人数、学生总数，及男生总数、女生总数、学生总人数,use,xscj,select,专业,性别,count(,*,) AS,人数,from,学生表,group by,专业,性别,with cube,47,（二）复杂查询,分组查询,Having子句,用于对分组数据进行进一步的筛选，用法与where字句类似,HAVING,子句,与,WHERE,子句的,区别,：,所处位置不同：,having,子句必须在,group by,语句之后，,where,子句必须在,group by,语句之前。,作用对象不同,：,WHERE,子句作用于基表或视图，从中选择满足条件的,行；,HAVING,子句,作用于组，从中选择满足条件的组。,Having,子句可包含,聚合函数,，,Where,子句不可以。,48,（二）复杂查询,分组查询,【,例,2,7,】,统计数据库,xscj,的学生表中,“工商管理”,专业的男生人数、女生人数、学生总数,。,use xscj,select,专业,性别,count(*) AS,人数,from,学生表,group by,专业,性别,with,rollup,having,专业=工商管理,49,（二）复杂查询,50,（二）复杂查询,排序,ORDER BY语句,可以按一个或多个属性列排序,Order by,排序表达式,ASC|DESC,分组表达式：,列名、表达式、正整数,（映射表中对应位置上的列）,ASC,升序；,DESC,降序；缺省值为升序,当排序列,含空值,时,ASC,：排序列为空值的元组最后显示,DESC,：排序列为空值的元组最先显示,51,（二）复杂查询,排序,【,例,2,8,】,将学生表中的学生按出生时间顺序排序。,use xscj select * from 学生表,order by,出生时间,【,例,2,9,】,将,成绩,表,中,的学生按,课程及成绩从高到低,排序。,use xscj select * from,成绩,表,order by,课程号,成绩,52,4.2,数据库的查询,53,（三）多表查询,54,（三）多表查询,连接查询,连接查询,：同时涉及多个表的查询，结果通常是含有参加连接运算的两个表（或多个表）的指定列的表。,连接条件中的,各连接字段类型必须是可比,的，但,名字不必是相同的,。,连接查询包括,：等值连接、自然连接、一般连接、内连接、外连接、左连接、右连接等。,连接查询中的,连接条件通过,WHERE,子句表达,，连接条件和元组选择条件之间用,AND,（与）操作符衔接。,在连接查询中，用来连接两个表的条件称为,连接条件,或,连接谓词,。,55,（三）多表查询,连接查询,1,、等值连接和非等值连接,一般格式：,.,.,比较运算符有：,=,、,、,=,、,80,57,（三）多表查询,连接查询,2,、自然连接,当等值连接中的连接列相同，并且,在,SELECT,子句中去除了重复列,时，则该连接操作为,自然连接,。,【,例,32】,选修了课程的学生信息及其选课信息。,SELECT,学号,姓名,性别,出生时间,总学分,备注,课程号,成绩,备注,FROM,学生表，成绩表,WHERE,学生表,.,学号,=,成绩表,.,课程号,58,（三）多表查询,连接查询,3,、自连接,:,一个表与其自己进行连接,需要,给表起别名,以示区别,由于所有属性名都是同名属性，因此必须,使用别名前缀,【,例,33】,查询选修了,101,号课程和,102,号课程的学生学号,SELECT a.,学号,FROM,成绩表,a,成绩表,b,WHERE a.,学号,=b.,学号,AND,a.,课程号,=101 AND,b.,课程号,=102,59,（三）多表查询,连接查询,4,、内连接,用,INNER,关键字指定的连接。,按照,ON,指定的连接条件，,返回满足条件的行,。,SELECT,FROM,Inner,JOIN,ON,连接条件,【,例,34】,在学生表与成绩表之间通过学号做内连接，显示已选课的学生的基本信息和他们的选课信息。,SELECT *,FROM,学生表,JOIN,成绩表,ON,学生表,.,学号,=,成绩表,.,学号,60,（三）多表查询,连接查询,5,、外连接,外连接操作,以指定表为连接主体,，,将主体表中满足条件、不满足连接条件的元组一并输出,。,包括,:,左外连接、右外连接、完全外连接,SELECT,FROM,LEFT|RIGHT|FULL OUTER,JOIN,ON,连接条件,61,（三）多表查询,连接查询,左外连接、右外连接、完全外连接的,区别,共同点,：,结果表中都包括满足条件的行。,左外连接（,LEFT OUTER JOIN,）：,还包括左表所有的行,右外连接（,RIGHT OUTER JOIN,）：,还包括右表所有的行,完全外连接（,FULL OUTER JOIN,）：,还包括两个表的所有行,62,（三）多表查询,连接查询,5,、外连接,【,例,35】,查询每个学生及其选修课程的情况包括没有选修课程的学生,SELECT,学生表,.,学号，姓名，性别，出生时间，总学分,课程号，成绩,FROM,学生表,LEFT OUT JOIN,成绩表,ON,(,学生表,.,学号,=,成绩表,.,学号,),等值连接中的内外连接也可在,where,子句中实现：,内连接,Where,学生表,.,学号,=,成绩表,.,学号,左外连接,Where,学生表,.,学号,*,=,成绩表,.,学号,右外连接,Where,学生表,.,学号,=,*,成绩表,.,学号,63,（三）多表查询,64,（三）多表查询,嵌套,查询,嵌套查询概述,（,P91,）,一个,SELECT-FROM-WHERE,语句称为一个,查询块,将一个查询块嵌套在另一个查询块的,WHERE,子句或,HAVING,短语,的条件中的查询称为,嵌套查询,SELECT,姓名,/*,外层查询,/,父查询*,/,FROM,学生表,WHERE,学号,IN,（,SELECT,学号,/*,内层查询,/,子查询*,/,FROM,成绩表,WHERE,课程号,= 103,）；,65,（三）多表查询,嵌套,查询,子查询即可嵌套在SELECT语句中使用，也可嵌套在INSERT、UPDATE及DELETE语句中使用。,子查询中，通常不允许使用ORDER BY语句。,子查询通常与IN谓词、比较运算符、ANY、ALL、EXIST等谓词结合使用。,66,（三）多表查询,嵌套,查询,1、带有IN谓词的子查询,IN子查询用于进行一个给定值是否在子查询结果集中的判断，格式为：,expression NOT ,IN,( 子查询 ),【例36】查找选修了课程号为206的课程的学生情况。,use xscj select * from 学生表,where 学号,in,(select 学号 from 成绩表 where 课程号=206),等价于,select 学生表.* from 学生表,成绩表 where 课程号=206 and 学生表.学号=成绩表.学号,67,（三）多表查询,嵌套,查询,【例,37,】查找未选修,离散数学,的学生姓名。,思路分析：,select * from 学生表 where 学号 not in,(,select 学号 from 成绩表 where 课程号 in,(,select 课程号 from 课程表,where 课程名=离散数学,),),蓝色模块等价于：,select 学号 from 成绩表,课程表,where,课程名=离散数学,and,课程表.课程号=成绩表.课程号,68,（三）多表查询,嵌套,查询,2,、带有比较运算符的子查询,当能确切知道内层查询,返回单值,时，可用比较运算符（,，,=,，, ANY,大于子查询结果中的某个值, ALL,大于子查询结果中的所有值, ANY,小于子查询结果中的某个值,= ANY,大于等于子查询结果中的某个值,= ALL,大于等于子查询结果中的所有值,= ANY,小于等于子查询结果中的某个值,= ALL,小于等于子查询结果中的所有值,= ANY,等于子查询结果中的某个值,=ALL,等于子查询结果中的所有值（通常没有实际意义）,!=,（或,）,ANY,不等于子查询结果中的某个值,!=,（或,）,ALL,不等于子查询结果中的任何一个值,72,（三）多表查询,嵌套,查询,ANY,（或,SOME,），,ALL,谓词与聚集函数、,IN,谓词的等价转换关系,=,或,!=,=,ANY,IN,-,MAX,MIN,= MIN,ALL,-,NOT IN,MIN,MAX,= MAX,73,（三）多表查询,嵌套,查询,【例,39,】查找比所有信息学院的学生年龄都大的学生。,Use xscj,Select * from,学生表,where,出生时间, all,(,Select,出生时间,From,学生表,where,学院编号,=(,select,学院编号,from,学院表,where,学院名,=,信息学院,),),74,（三）多表查询,嵌套,查询,【例,40,】查找比信息学院的某一学生年龄大的学生。,Use xscj,Select * from,学生表,where,出生时间, any,(,Select,出生时间,From,学生表,where,学院编号,=(,select,学院编号,from,学院表,where,学院名,=,信息学院,),),等价于：,Select * from,学生表,where,出生年月,(Select,max,(,出生年月,)From,学生表,where,学院编号,=(select,学院编号,from,学院表,where,学院名,=,信息学院,),75,（三）多表查询,嵌套,查询,带有,EXISTS,谓词的子查询,1. EXISTS,谓词,带有,EXISTS,谓词的子查询,不返回任何数据,，只产生逻辑真值“,true”,或逻辑假值“,false”,。,若内层查询结果非空，则外层的,WHERE,子句返回真值,若内层查询结果为空，则外层的,WHERE,子句返回假值,由,EXISTS,引出的子查询，其目标列表达式,通常都用*,，因为带,EXISTS,的子查询只返回真值或假值，给出列名无实际意义,2. NOT EXISTS,谓词,若内层查询结果非空，则外层的,WHERE,子句返回假值,若内层查询结果为空，则外层的,WHERE,子句返回真值,76,（三）多表查询,嵌套,查询,【例,41,】查找选修,206,号课程的学生姓名。,思路分析,：,本查询涉及学生表和成绩表,在学生表中依次取每个元组的学号值，用此值去检查成绩表,若成绩表中存在这样的元组，其学号值等于此学生表中的学号值，并且其课程号,= 206,，则取此学生表的姓名值送入结果关系,SELECT,姓名,FROM,学生表,WHERE EXISTS(,SELECT *,FROM,成绩表,WHERE,学号,=,学生表,.,学号,AND,课程号,= 206,),77,（三）多表查询,嵌套,查询,【例,4,2,】,查找选修了全部课程的同学的姓名。,解决思路,：将问题,“查找选修了全部课程的同学”,，转变成,“,查找没有一门课不选修的同学,”,。,分析：,1.,所有,未选过的课程,的数据集：,select * from,课程表,where not exists( select * from,成绩表,where,课程号,=,课程,.,课程号,),2.,增加一个条件,：,select * from,课程表,where not exists( select * from,成绩表,where,学号,=,学号,and,课程号,=,课程表,.,课程号,),所有,没被某位,学号为,学号 的,学生选过的课程,的记录集（,学号学生的未选课程）：,78,（三）多表查询,嵌套,查询,3.,遍历每一个主查询的学号，每一个学号都按第二筛选方法筛选出：没有未选课程的学生的学号。（不包括在第二步中查询出的“有未学课程的学号的记录集”中的记录。,),所以，【例,4,2,】的查询语句如下：,SELECT,姓名,FROM,学生表,WHERE,NOT EXISTS,(,SELECT * FROM,课程表,WHERE,NOT EXISTS,(,SELECT * FROM,成绩表,WHERE,学号,=,学生表,.,学号,AND,课程号,=,课程表,.,课程号,),),79,（三）多表查询,80,（三）多表查询,集合查询,集合操作的种类,并操作,UNION,交操作,INTERSECT,差操作,EXCEPT,语法格式：,查询,union all | intersect | except,查询,注意：,参加集合操作的各查询结果的,列数必须相同,；,对应项的数据类型也必须相同,81,（三）多表查询,集合查询,1,、并操作,UNION,用于将两个或多个,SELECT,查询的结果合并成一个结果集。,UNION,：将多个查询结果合并起来时，系统自动,去掉重复元组,。,UNION,ALL,：将多个查询结果合并起来时，,保留重复元组,。,【例,4,3,】查找学号为,081101,和学号为,081210,的两位同学的信息。,SELECT * FROM,学生表,WHERE,学号,= 081101,UNION ALL,SELECT * FROM,学生表,WHERE,学号,= 081210,82,（三）多表查询,集合查询,【例,4,4,】查询学院编号为,01,的学生及年龄不大于,19,岁的学生,。,SELECT * FROM,学生表,WHERE,学院编号,= 01,UNION,SELECT * FROM,学生表,WHERE year(getdate()-year(,出生时间,)42,INTERSECT,SELECT * FROM,学生表,WHERE,性别,=1,86,（三）多表查询,集合查询,【例,4,8,】,查询既选修课程,101,，又选修课程,102,的学生名单。,Use xscj,Select,学号,from,成绩表,where,课程号,=101,Intersect,Select,学号,from,成绩表,where,课程号,=102,87,（三）多表查询,88,（三）多表查询,Into,子句,使用,INTO,子句可以,将,SELECT,查询所得的结果保存到一个新建的表中,。,INTO,子句的格式为：, INTO,新表名,包含,INTO,子句的,SELECT,语句执行后,所创建的表的结构由,SELECT,所选择的列决定,，新创建的,表中的记录由,SELECT,的查询结果决定,，若,SELECT,的,查询结果为空，则创建一个只有结构而没有记录的空表,。,89,（三）多表查询,Into,子句,【例,4,9,】,由学生表创建“信息学院学生”表，包括学号和姓名。,SELECT,学号,姓名,INTO,信息学院学生,FROM,学生表,WHERE,学院编号,=,（,select,学院编号,from,学院表,where,学院名,=,信息学院,）,90,主要内容,4.1,关系运算,4.2,数据库的查询,4.3,视图,4.4,游标,91,4.3,视图,概念,：,视图是保存在数据库中的,SELECT,查询。,特点,：,不是真实存在的基本表，而是从一个或几个基本表（或视图）导出的虚拟的表（简称,虚表,）。,只存放视图的定义，不存放视图对应的数据。视图中的数据在引用视图时，由定义视图的查询动态生成。,基表中的数据发生变化，从视图中查询出的数据也随之改变。,92,视图的操作,视图的创建,视图的修改,视图的查询,视图的更新,定义基于该视图的新视图,93,视图的创建,视图的创建方法有两种：,使用企业管理器创建视图,使用,CREATE VIEW,语句创建视图,（一）使用企业管理器创建视图,在企业管理器中，展开指定数据库，用鼠标右击“视图”目录，在弹出的快捷菜单中选择“新建视图”，打开“视图设计器”窗口。在此窗口中创建视图。,94,视图的创建,（二）使用,CREATE VIEW,语句创建视图,CREATE VIEW,的,语法格式,：,CREATE VIEW,视图名, (,列名, ,.n ) , WITH ENCRYPTION ,AS,SELECT,语句,WITH CHECK OPTION,95,视图的创建,参数说明：,视图名,：视图的名称，必须符合标识符的命名规则。,列名,：视图中的列名称，,要么省略，要么全部指定。,省略,时,，则采用SELECT语句产生的列名,作为,视图的列。,当列是从算术表达式、函数或常量派生的，两个或更多的列可能会具有相同的名称（通常是因为连接），或者视图中的某列被需要赋予了不同于派生来源列的名称时，需要指定列名。,96,视图的创建,参数说明：,WITH ENCRYPTION,：对包含在系统表syscomments内的CREATE VIEW语句文本进行加密。,SELECT语句,：用于创建视图的SELECT语句，利用SELECT语句可以从表或视图中选择列构成新视图的列。,With check option,：,表示对视图进行,update,、,insert,和,delete,操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）,97,视图的创建,【例5,0】,建立信息,学院,学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息,学院,的学生 。,Create View,信息学生,VIEW,As,Select,* from,学生表,where,学院编号,in,（,select,学员编号,from,学院表,where,学院名称,=,信息学院）,WITH CHECK OPTION,98,视图的创建,【例5,1】,创建工商管理专业,学生的平均成绩,视图,KC_AVG,，包括学号（在视图中列名为,Snum,）和平均成绩（在视图中列名为,score_avg,）。,Create View,KC_AVG ( Snum, score_avg),As,Select,学号,avg(,成绩,) from,成绩表,Group by,学号,99,视图的修改,视图结构的修改有两种方法：,通过企业管理器。右键单击视图，在快捷菜单中选择“设计”菜单，进入视图修改窗口。（操作与创建相似。）,通过,ALTER VIEW,命令。,ALTER VIEW,语法格式如下：,ALTER VIEW,视图名,(,列名,.n),WITH ENCRYPTION,AS,SELECT,语句,100,视图的修改,【例5,1】,将信息学生,VIEW,修改为只包含计算机专业学生的学号、姓名和总成绩。,Create view,信息简,VIEW,As,select,学号,from,信息学生,VIEW,where,专业,=,计算机,101,视图的删除,视图的删除有两种方法：,通过,企业管理器,。右键单击视图，在快捷菜单中选择“删除,”,菜单。,通过,DROP VIEW,命令。,ALTER VIEW,语法格式如下：,DROP VIEW,视图名,(,列名,.n),【,例,52】,使用,DROP VIEW,命令删除视图,KC_AVG,。,Drop,view kc_avg,102,视图的查询,视图定义后，即可像查询基本表那样进行查询。,【例5,3】,查找工商管理专业平均成绩在,80,分以上的学生的学号和平均成绩。,Select * from,KC_AVG,where score_avg,80,注意：,在使用视图查询时，若其关联的基本表中添加了新字段，则必须重新创建视图才能查询到新字段。,如果与视图相关联的表或视图被删除，则该视图将不能再使用。,103,视图的更新,视图的更新操作包括数据的,插入、修改和删除,。,要通过视图更新基本表数据，必须保证视图是可更新视图。一个可更新视图可以是以下情形之一：,（,1,）满足以下条件的视图：,创建视图的,SELECT,语句中没有聚合函数，且没有,TOP,、,GROUP BY,、,UNION,子句及,DISTINCT,关键字。,创建视图的,SELECT,语句中不包含从基本表列通过计算所得的列。,创建视图的,SELECT,语句的,FROM,子句中至少要包含一个基本表。,104,视图的更新,一个可更新视图可以是以下情形之一：,（,2,）可更新的分区视图,在实现分区视图之前，必须先实现水平分区表。原始表被分成若干个较小的成员表，每个成员表包含与原始表相同数量的列，并且每一列具有与原始表中的相应列同样的特性（如数据类型、大小、排序规则）。,（,3,）通过,INSTEAD OF,触发器创建的可更新视图。,INSTEAD OF,触发器将在第,7,章介绍。,105,视图的更新,插入数据,施用,Insert,语句通过视图向基本表插入数据。,【例5,4】,向信息学生,VIEW,插入以下记录,：（,081115,李敏,计算机,0,1988-3-2,50,NULL,01,）,INSERT INTO,信息学生,VIEW,VALUES,(081115, ,李敏,计算机,0,1998-3-2, 50,NULL,01),注意：,当视图所依赖的基本表有多个时，不能向该视图插入数据，因为这将会影响多个基表。,106,视图的更新,修改数据,使用,UPDATE,语句通过视图修改基本表的数据。,【例5,5】,将信息学生,VIEW,视图中所有学生的总学分增加,8,。,UPDATE,信息学生,VIEW,SET,总学分,=,总学分,+3,注意：,若一个视图依赖于多个基本表，则依次修改该视图只能变动一个基本表的数据。,107,视图的更新,删除数据,使用,DELETE,语句通过视图删除基本表数据。,注意：,对于依赖于多个基本表的视图，不能使用,DELETE,语句。,【例5,6】,删除