第四章 sql语言.ppt

1、第四章 SQL语言,SQL语言的基本特征 SQL数据定义功能 索引的建立和删除,SQL的查询功能 SQL数据操纵功能 嵌入式SQL,SQL语言的基本特征,一体化的特点 两种使用方式，统一的语法格式 高度非过程化 语言简洁、易学易用 SQL语言也支持关系数据库三级模式体系结构 外模式：视图+一些基本表 模式：基本表 内模式：存储文件(逻辑结构）,第五章 SQL语言组成部分,数据定义语言DDL(Data Definition Language) 定义关系模式、删除/修改关系模式 交互式数据操纵语言DML(Data Manipulation Language) 查询、插入、删除、修改 索引与视图定义

2、功能 事务控制功能 嵌入式SQL 完整性 权限管理,数据库三级模式,外模式1,外模式2,外模式n,模式,内模式,数据库,应用A,应用B,应用C,应用D,应用E,视图,表,索引,样板模式,Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date),样板数据库,Sid sname rating age sid bid day 22 Du

3、stin 7 45 22 101 10/10/98 29 Brutus 1 33 22 102 10/10/98 31 Lubber 8 55 22 103 10/8/98 32 Andy 8 25 22 104 10/7/98 58 Rusty 10 35 31 102 11/10/98 64 Horatio 7 35 31 103 11/6/98 71 Zorba 10 16 31 104 11/12/98 74 Horatio 9 40 64 101 9/5/98 85 Art 3 25 64 102 9/8/98 95 Bob 3 63 74 103 9/8/98,Bid bname

4、color Interlake blue Interlake red Clipper green Marine red,Sailors,Boats,Reserves,SQL数据定义功能,创建表Sailors Create table Sailors Create table Sailors ( sid char(11), ( sid char(11) primary key， sname char(20), sname char(20), rating int, rating int, age int, age int） primary key ( sid ) ),表级约束,列级约束,SQL数

5、据定义功能,创建表Boats Create table Boats ( bid char(10), bname char(20), color char(10), primary key ( bid ) ),表级约束,SQL数据定义功能,创建表Reserves Create table Reserves ( sid char(11), bid char(10), day datetime, primary key ( sid, bid ), foreign key(sid) references Sailors, foreign key(bid) references Boats ) 数据类型

6、（p89）,表级约束,基本表的修改与删除,修改 Alter table Sailors add sex char(3) 删除 Drop table Sailors 把一个基本表的定义连同表上的所有记录、索引以及由此基本表到出的所有视图都删除，并释放相应的存储空间,索引,当关系很大时，通过扫描所有关系中的所有元组来找出符合条件的元组 代价太高 例如： select * from Student where Sage 22 and Sdept = IS Student关系中可能存在5000个元组，只有200个元组是大于22岁的。 代价大的方法：逐个评价5000个元组 改进的方法：直接评价200个元

7、组,索引,首先：Students记录按Sage来排序 简单的改进方法：二分法查找文件中的记录 问题：如果文件很大，二分法查找仍会带来巨大的执行代价。如何改善？ 建立一个索引文件,索引,索引文件: 索引页的格式,一级索引结构,P0 K1 P1 K2 P2 Kn Pn,Page 1 Page 2 Page 3 Page n,数据文件,索引文件,索引,一级索引结构,P0 16 P1 22 P2 25 Pn,Page 1 Page 2 Page 3 Page n,数据文件,索引文件,age,因为索引文件比数据文件小得多 在索引文件上进行二分法搜索比在数据文件上搜索要快得多,索引的优点,关系属性A上的索

8、引可以提高 查找在A上具有某个特性值的 元组的效率,索引-index,索引可以加快查找 索引的代价（时间、空间） 索引的维护和使用（系统） SQL支持用户建立索引 索引建立策略 如果一个（组）属性经常在查询条件中出现 如果一个属性经常作为min或max等集函数的参数 在连接属性上,索引,唯一索引：unique 每个索引值只对应一个唯一的数据记录 聚簇索引：cluster 索引项的顺序和表中数据记录的物理顺序一致,学号 95001 95002 95003 95004,学生 95002 王 18 CS 95004 李 17 IS 95001 张 17 MA 95003 赵 18 CS,学号 950

9、01 95002 95003 95004,学生 95001 张 17 MA 95002 王 18 CS 95003 赵 18 CS 95004 李 17 IS,创建索引,给关系Students的Sage属性创建一个索引 CREATE INDEX ageIndex ON Students(Sage); DBMS允许对多个属性创建一个索引 CREATE INDEX keyIndex ON SC(sno, cno);,索引的建立,在表Sailors的属性sid上建立一个唯一索引 create unique index in_u_sid on Sailors( sid ) 在表Boats的属性bid上

10、建立一个聚簇索引 create cluster index in_c_bid on Boats( bid ),创建索引,在表Sailors的属性sname上建立一个索引 create index xsname on Sailors( sname ) 在表Boats的属性bname上建立一个索引 create index xbname on Boats( bname ) 在表Reverse的属性(bid,sid)上建立一个索引(组合索引） create index xbidsid on Reverse( bid asc, sid desc ) asc为升序,desc为降序, 缺省为升序,索引的删

11、除,索引的删除: Drop index 索引名 Drop index xsname 索引可以加快查找，但不是索引越多越好 对某个属性使用索引能极大地提高对该属性上值的检索效率，使用到该属性时，可以加快连接操作。 对某个属性使用索引会使得对关系的插入、删除、修改变得复杂和费时。 访问和更新索引结构本身也需要磁盘操作。,SQL的查询功能,SQL查询语句的基本结构 SELECT DISTINCT 目标列 FROM 基本表 (视图) 范围变量名,. WHERE 条件表达式 Group by 列名1 having 分组表达式 Order By 列名2 asc | desc ;,基本结构,三个子句 Sel

12、ect: 对应关系代数的投影运算，列出查询结果中的属性 From: 对应关系代数的笛卡尔集运算，列出表达式求值中需扫描的关系 Where:对应关系代数的选择谓词，包括一个作用在from子句中关系属性上的谓词。,Select A1, A2, , An From r1, r2, , rm Where P,样板模式,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade) 用SQL创建以上三个表,单表查询,查询内容仅涉及一个表 例子：查询全体学生的学号和姓名

13、select Sno, Sname 顺序无关性 from Students;,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),单表查询,例子：查询全体学生的基本情况 select * from Students; 例子：查询全体学生的姓名及出生年份 select Sname, 2004-Sage from Students;,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cna

14、me, Cpno, Credit) SC ( Sno, Cno, Grade),单表查询,例子：查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所有系名 select Sname, Year of birth: , 2004-Sage, islower(Sdept) from Students;,Sname Year of birth: 2004-Sage islower(Sdept)李勇 Year of birth: 1976 cs 刘辰 Year of birth: 1977 is 王敏 Year of birth: 1978 ma 张立 Year of birth: 1978 i

15、s,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),单表查询,用户可以通过指定别名来改变查询结果的列标题 select Sname NAME, Year of birth BIRTH, 2004-Sage BIRTHDAY, islower(Sdept) DEPARTMENT from Students;,NAME BIRTH BIRTHDAY DEPARTMENT 李勇 Year of birth: 1976 cs 刘辰 Year of bi

16、rth: 1977 is 王敏 Year of birth: 1978 ma 张立 Year of birth: 1978 is,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),单表查询,消除取值重复的行 查询选修了课程的学生学号 select Sno from SC; 消除重复行 select distinct Sno from SC;,Sno 95001 95001 95001 95002 95002,Sno 95001 95002,Stu

17、dents ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),单表查询,查询满足条件的元组 常用查询条件 查询条件 谓词 比较 =, , =, , !, ! not + 上述比较运算符 确定范围 between and, not between and 确定集合 in, not in 字符匹配 like, not like 空值 is null, is not null 多重条件 and, or,单表查询,一般查询 查询年龄在20岁以下的学生姓名及其年龄 sele

18、ct Sname, Sage from Sduents where Sage 20; 查询年龄在2023岁之间的学生姓名、所在系，年龄 select Sname, Sdept, Sage from Students where Sage between 20 and 23;,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),单表查询,查询信息系、数学系和计算机系学生的姓名和性别 select Sname, Ssex from Students w

19、here Sdept in (IS, MA, CS); 查询所有姓刘的学生的姓名、学号和性别 select Sname, Sno, Ssex from Students where Sname like 刘%；,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),单表查询,查询姓欧阳且全名为三个汉字的学生姓名 select Sname from Students where Sname like 欧阳_; 查询DB_Design课程号和学分 sel

20、ect Cno, Ccredit from Course where Cname like DB_Design;,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),单表查询,查询以C开始和结束，并且有至少3个字母组成的系的学生的学号 select Sno from Student where Sdept like C_%C; 查询缺考学生的学号和课号 select Sno, Cno from SC where Grade is null;,Stu

21、dents ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),单表查询,查询计算机系年龄在20岁以下的学生的姓名 select Sname from Students where Sdept=CS and Sage20; 查询信息系、数学系和计算机系学生的姓名和性别 select Sname, Ssex from Students where Sdept=CS or Sdept=IS or Sdept=MA,Students ( Sno, Sname, Ssex,

22、 Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),单表查询,对查询结果排序 例子：查询选修了3号课程的学生学号和成绩，要求查询结果按成绩降序排列 select Sno, Grade from SC where Cno=3 order by Grade desc;,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),单表查询,例子：查询全体学生的情况，查询结

23、果按系号升序排列，同一系的学生按年龄降序排列 select * from SC order by Sdept asc, Sage desc; 使用集函数 例子: 查询学生总数 select count(*) from Students;,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),单表查询,例子: 查询选修了课程的学生人数 select count (distinct Sno) from SC; 求选修1号课程的学生的平均成绩 select

24、 avg(Grade) from SC where Cno=1;,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),单表查询,SQL提供的主要集函数(聚合函数) count( distinct|all *) count( distinct|all ) sum( distinct|all ) avg( distinct|all ) max ( distinct|all ) min ( distinct|all ),单表查询,对查询结果分组: 将查询

25、结果按一列或多列值分组, 目的是将集函数作用到组上(例如: 小计) 例子: 求每门课的课号及其选课人数 select Cno, count(Sno) from SC group by Cno; 例子: 求选课人数超过10人的课程号及其人数 select Cno, count(Sno) from SC group by Cno having count(Sno)10;,组内条件,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),多个关系上的查询,集

26、合操作 多个SQL的查询结果可以进行集合操作 多关系的连接操作,多个关系上的查询,集合操作主要有: union、intersect和except 例子：查询计算机系的学生以及年龄不大于19岁的学生 select * from Students where Sdept=CS union select * from Students where Sage = 19;,多个关系上的查询,不用union，如何实现该查询？ 例子：查询计算机系的学生与年龄不大于19岁的学生的交集 select * from Students where Sdept=CS intersect select * from S

27、tudents where Sage=19; 不用intersect，如何实现该查询？,多个关系上的查询,例子：查询计算机系的学生与年龄不大于19岁的学生的差集 select * from Students where Sdept=CS except select * from Students where Sage=19; 不用except，如何实现该查询？,多个关系上的查询,关于消除重复元组的讨论 在select中 缺省情况下：保留重复元组 使用distinct消除重复元组 在union, intersect, except中 缺省情况下：消除重复元组 在union, intersect,

28、 或except后加上all，保留重复元组,多表连接查询,等值与非等值连接查询 查询每个学生及其选修课的情况 select Students.*, SC.* from Students, SC where Students.Sno=SC.Sno; Students.Sno Sname SC.Sno Cno 95001 李勇 95001 1 ,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),多表连接查询,自然连接 select Students.

29、*, Cno, Grade from Students, SC where Students.Sno=SC.sno; 自身连接 查询每门课的课号及其间接先修课的课号 C1: Courses ( Cno, Cname, Cpno, Credit) C2: Courses ( Cno, Cname, Cpno, Credit) select C1.Cno, C2.Cpno from Course C1, Course C2 where C1.Cpno=C2.Cno;,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cp

30、no, Credit) SC ( Sno, Cno, Grade),多表连接查询,外连接: 在一般连接中,只有满足条件的元组才可以作为结果输出, 外连接则不同(左、右外连接) 查询学校内学生及雇员的情况。 (内)连接 既是学生，又是雇员。 左外连接 是学生，可以不是雇员。 右外连接 可以不是学生，但，是雇员。 全外连接 可以不是学生，可以不是雇员。,Students(name, dept) Employee(name, salary),多表连接查询,左外连接:是学生，可以不是雇员。,Students(name, dept) Employee(name, salary),Students NAT

31、URAL LEFT OUTER JOIN Employee,多表连接查询,全外连接: 可以不是学生，可以不是雇员。,Students(name, dept) Employee(name, salary),Students NATURAL FULL OUTER JOIN Employee,多表连接查询,例： SELECT a.au_fname, a.au_lname, p.pub_name FROM authors AS a RIGHT OUTER JOIN publishers AS p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lnam

32、e ASC, a.au_fname ASC SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a FULL OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC,多表连接查询,思考：Student和SC之间可以做哪种外连接？,Students ( Sno, Sname, Ssex, Sage, Sdept) SC ( Sno, Cno, Grade),多表连接查询,复合条件连接 查询选修2号课

33、程且成绩在90分以上的所有学生 select Students.* from Students, Course where Students.Sno=C2.Sno and Cno=2 and Grade90;,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),多表连接查询,复合条件连接 查询每个学生的学号、姓名、选修的课程名和成绩 select Students.Sno, Sname, Cname, Grade from Students, Co

34、urse, SC where Students.Sno=SC.Sno and SC.Cno=Course.Cno;,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),嵌套查询,查询块(SELECT-FROM-WHERE) 一个查询块可以放在另一个查询块的where子句或having短语的条件中 父查询和子查询 子查询中不可以使用order by 子句，order by只能对最终结果排序 多个简单查询构造一个复杂的查询 相关子查询和不相关子查询,不

35、相关子查询,带有in的子查询：子查询结果为多值 查询与刘晨在一个系学习的学生 要求：不使用连接查询,Students ( Sno, Sname, Ssex, Sage, Sdept),select * from Students where Sdept in (select Sdept from Students where Sname=刘晨); 不相关子查询求解方法由里向外,不相关子查询,带有in的子查询：子查询结果为多值 查询与刘晨在一个系学习的学生 要求：使用连接查询,Students ( Sno, Sname, Ssex, Sage, Sdept),不相关子查询,带有in的子查询：子查

36、询结果为多值 查询与刘晨在一个系学习的学生 要求：使用连接查询,Students ( Sno, Sname, Ssex, Sage, Sdept),select S2.* from Students S1, Students S2, where S1.Sname = 刘晨 and S1.Sdept = S2.Sdept;,有些嵌套查询可以用连接查询代替,不相关子查询,查询选修了课程名为信息系统的学生学号和姓名 select Sno, Sname from Students where Sno in (select Sno from SC where Cno in (select Cno fro

37、m Courses where Cname=信息系统);,不相关子查询,带有比较运算符的子查询：子查询结果为单值 例子：查询与刘晨在一个系学习的学生 select * from Students where Sdept = (select Sdept from Students where Sname=刘晨);,注意：关系代数不能用=表示值和表间的等值判断,不相关子查询,带有any或all的子查询：必须同比较运算符同用 any all 查询其他系比信息系某一学生年龄小的学生姓名和年龄 select Sname, Sage from Students where Sage any (select

38、 Sage from Students where Sdept=IS) and Sdept!=IS ;,不相关子查询,用集函数实现上述查询(效率比any和all高) select Sname, Sage from Students where Sage (select max(Sage) from Students where Sdept=IS) and Sdept!=IS ; 查询其他系比信息系所有学生年龄都小的学生姓名和年龄(思考题) Any和all和集函数的对应关系p111,相关子查询,带有exists的子查询：子查询结果为true或false 查询所有选修了1号课程的学生姓名 sele

39、ct Sname from Students where exists (select * from SC where Sno=Students.Sno and Cno=1); 相关子查询求解方法由外向里(样板数据库),思考：查询没有选修1号课程的学生姓名,相关子查询,SQL中没有全称量词,但是 x(P)(x(P) 例子: 查询选修了全部课程的学生姓名 select Sname from Students where not exists (select * from Course where not exists (select * from SC where Sno=Students.Sn

40、o and Cno=Course.Cno);,分析: 求学生x, 对于任意一门课y, P: 学生x选修了y, 则: y(P)(y(P),相关子查询,SQL中没有逻辑蕴含运算,但是 (pq) pq 例子: 查询至少选修了学生95002选修的全部课程的学生学号 分析: 查询学号x, 对于任意一门课y, 只要95002选修, 则x必然选修, 如果: p: 95002选修y q: x 选修y 则: y(p q) (y(p q) (y(pq) (y(pq) y(p q),相关子查询,p: 95002选修y, q: x 选修y y(p q) y(p q) select Sno from SC X wher

41、e not exists (select * from SC Y where Sno=95002 and not exists (select * from SC Z where Z.Sno=X.Sno and Z.Cno=Y.Cno);,习题,查询租用过103号船的船员姓名,习题,查找租用过船只的船员编号 问题1：是否需要在Select子句中添加DISTINCT? 问题2：如果将Select子句中的S.sid换成S.sname,是否需要添加DISTINCT?,查找rating7且年龄25的水手编号 使用Intersect来代替and运算,习题,Select S.sid from Sailor

42、s S where S.rating7 and S.age25,Select S.sid from Sailors S where s.rating7 intersect select S2.sid from Sailors S2 where S2.age25,查找租用过红船和绿船的水手编号 错误的写法 正确的写法,Select S.sname from Sailors S, Reverses R, Boats B where S.sid=R.sid and R.bid=B.bid and B.color=“red” and B.color=“green”,习题,Select S.sname

43、from Sailors S, Reverses R1, Boats B1, Reverses R2, Boats B2 where S.sid=R1.sid and R1.bid=b1.bid and S.sid=R2.sid and R2.bid=B2.bid and B1.color=“red” and B2.color=“green”,可以使用Union来代替or运算,查找租用过红船或绿船的水手编号,习题（嵌套查询）,查找租用过103号船只的水手的名字,不相关子查询,问题3：不使用嵌套查询该怎麽做？,嵌套查询,查找租用过103号船只的水手的名字,相关子查询,嵌套查询,查找比Haroti

44、o级别高的水手,使用In查询来改写Intersect查询,查找租用过红船和绿船的水手编号 Intersect查询,Select S.sid from Sailors S, Reverses, R Boats B where S.sid=R.bid and R.bid=B.bid and B.color=“red” intersect select S2.sid from Sailors S2, Reverses R2, Boats B2 where S2.sid=R2.bid and R2.bid=B2.bid and B2.color=“green”,思考：第二部分的from子句和第一部分的

45、from子句是否可以相同？,使用In查询来改写Intersect查询,查找租用过红船和绿船的水手编号 In查询,思考：父子两块查询中的from子句是否可以相同？,使用In查询来改写Intersect查询,查找rating7且年龄25的水手编号 Intersect查询,Select S.sid from Sailors S where s.rating7 intersect select S2.sid from Sailors S2 where S2.age25,使用In查询来改写Intersect查询,查找rating7且年龄25的水手编号 In查询,Select S.sid from Sai

46、lors S where s.rating7 and S.sid in( select S2.sid from Sailors S2 where S2.age25 ),查找最年长的水手的年龄和名字,？,在18岁以上水手中,对于每个rating级别中最少有两个水手以上的组中最年轻水手的年龄,在18岁以上水手中,对于每个rating级别中最少有两个水手以上的组中最年轻水手的年龄,在18岁以上水手中,对于每个rating级别中最少有两个水手以上的组中最年轻水手的年龄(子查询),Having子句中也可以包含子查询 Having子句可以为Count(*) 1,问题4:如果去掉颜色选择条件,查询结果如何?

47、 问题5:如果去掉Sailors和与S.sid相关的条件,情况会怎样?,查找每条红色船只被租用的次数,在三个关系连接之上的一个分组操作,SQL数据操纵功能(更新),数据插入 数据删除 数据修改,数据插入,单记录插入 insert into Students values(95020, 陈东, 男, 18,IS) insert into SC(Sno, Cno) values(95020, 1) 多记录插入(插入子查询结果) 假定数据库中有表: deptage(Sdept char(15), Avgage smallint) insert into deptage ( sdept, avgage

48、 ) select Sdept, avg( Sage ) from Students group by Sdept,数据删除,delete from Students where Sno = 95019 delete from SC 带有子查询的删除 delete from SC where CS = ( select Sdept from Students where Students.Sno = SC.Sno ),思考: 王立同学因病休学,请将王立的选课记录删去,数据修改,update Students set Sage = 22 where Sno= 95001 update Stude

49、nts set Sage = Sage + 1 带有子查询的修改 update SC set G = 0 where MA = ( select Sdept from Students where Sno=SC.Sno ),更新操作与数据库的一致性,更新操作只对一个表操作, 但实际中可能: 例如: 请删除学号为95019的学生, 隐含将其所有选课记录删去 delete from students where Sno=95019; delete from SC where Sno=95019; 如果建表时, 说明SC参照Students存在, 则此删除失败,更新操作与数据库的一致性,数据库提供事

50、务概念处理这类问题 如果建表时, 说明SC参照Students存在, 且说明on delete cascade, 则只要 delete from students where Sno=95019; 便可将95019的选课记录全部删去,视图,视图是用户可以看见的(虚)关系，它不是逻辑模型的一部分。 视图是从一个或几个基本表（视图）导出的表 视图是虚表：数据库中只存放视图的定义(存放于数据字典中)，不存放视图对应的数据 视图也称动态窗口： 视图可以和基本表一样被查询，被删除 视图的更新是有一定限制的 可以基于视图，定义新的视图,视图定义,例子：建立信息系学生的视图 create view IS_S

51、tudents as select * from Students where Sdept=IS create view IS_Students(No, Name, Dept) as select Sno, Sname, Sdept from Students where Sdept=IS 行列子集视图：从单个基本表导出，去掉了一些行和列，但保留了码,?视图的属性名,视图定义,视图的属性或者全部指定，或者全部不指定 换名 某个目标列是集函数或列表达式 多表连接时有几个同名列需要区分 例子：将学生的学号及它的平均成绩定义为一个视图 create view avgGrade(Sno, avgG)

52、as select Sno, avg(Grade) from SC group by Sno,虚拟列,视图定义,例子：建立信息系选修了1号课程且成绩在90分以上的学生的视图 Create view IS_Students(Sno, Sname, Grade) as select Students.Sno, Sname, Grade from Students, SC where Students.Sno=SC.Sno and Cno=1 and Grade90; with check option子句 create view IS_Students as select * from Stude

53、nts where Sdept=IS with check option;,删除视图,语句格式：DROP VIEW ； 将视图定义从数据字典中删除，且由该视图导出的视图均失效，要从数据字典中将实效的视图都删除，以免用户使用错误 例如： DROP VIEW IS_Student; 思考：若删除了基本表，由基本表导出的视图又如何？,查询视图,视图查询执行过程: 把定义中的子查询和用户查询结合起来, 转化成等价的对基本表的查询 例子: 在信息系的学生中找到年龄小于20岁的学生 select * from IS_Students where Sage20; 转换后的查询为(系统完成): select

54、* from Students where Sdept=IS and Sage20; 查询信息系选修了1号课程的学生姓名和成绩 select Sname, Grade from IS_Students, SC where IS_Students.Sno=SC.Sno and Cno=1;,查询视图,对视图的查询有些是不能直接进行转换的 例子: 在avgGrade视图中, 查询平均成绩在90分以上的学生学号和平均成绩 select * from avgGrade where avgG=90; 转换: select Sno, avg(Grade) from SC where avg(Grade90

55、) group by Sno;,?,视图更新,对视图更新最终转换成对基本表的更新 将信息系学生学号为95002的学生姓名改为“刘辰” update IS_Students set Sname=刘辰 where Sno=95002; 通过视图IS_Students插入一信息系的学生记录 insert into IS_Students values(95029, 赵新, 女, 20); with check option子句,视图更新,删除信息系学号为95029的记录 delete from IS_Students where Sno=95029; 行列子集视图是可以更新的, DB2例子p127

56、视图的作用: 简化用户的操作 视图使用户能以多种角度看待同一数据 视图可以对机密数据提供一定的安全保护,视图的作用,视图对数据库重构提供了一定程度的逻辑独立性 例如: Students(Sno, Sname, Ssex, Sage, Sdept) 分为: S1(Sno, Sname, Sage) S2(Sno, Ssex, Sdept) 但在Students上已有开发了多个应用, 为了达到不改变应用程序的目的, 可以建视图Students: create view Students(Sno, Sname, Ssex, Sage, Sdept) as select S1.Sno, S1.Sname, S2.Ssex, S1.Sage, S2.Sdept from S1, S2 where S1.Sno=S2.Sno;,视图更新,由两个以上基本表导出的视图不允许更新； 来自于字段表达式或常数的视图字段不允许进行insert和update操作，但允许delete操作； 不允许更新集函数视图字段； 不允许更新含有group by或distinct视图； 嵌套查询内层的表也是导出该视图的基本表时，不允许更新； 不允许更新的视图上定义的视图也不允许更新。,嵌套查询内层的表也是导出该视图的基本表,例如：CREATE VIEW GOO