数据库原理第三章A.ppt

.本章重点：SQL的数据定义、查询、更新、控制功能；视图的概念及其优点。本章难点：SQL的数据查询功能。,第三章 关系数据库标准语言SQL,第三章 关系数据库标准语言SQL 3.1 SQL概述,SQL语言的动词,SQL的数据定义功能包括三部分:定义基本表,定义视图和定义索引。视图是基于基本表的虚表，索引是依附于基本表的。因此SQL通常不提供修改视图和索引定义的操作。SQL 的数据定义语句,第三章 关系数据库标准语言SQL 3.2 数据定义,DB2 SQL 支持以下数据类型;INTEGER或INT 全字长(31 bits 精度)的二进制整数SMALLINT 半字长(精度为15bits)的二进制整数DECIMAL(p,q)或DEC(p,q)压缩十进制数,共p位,小数点后 有q位.15=p=q=0,q=0时可省略.FLOAT 双字长的浮点数CHARTER(N)或CHAR(N)长度为n的定长字符串VARCHAR(N)变长字符串,最大长度为nGRAPHIC（N）长度为n的定长图形字符串VAR GRAPHIC（N）变长图形字符串,最大长度为nDATE 日期型，格式为YYYY-MM-DDTIME 时间型，格式为TIMESTAMP 日期加时间,第三章 关系数据库标准语言SQL 3.2 数据定义,第四章 关系数据库标准语言SQL 4.2 数据定义,例2：CREATE TABLE s(sno char(6)not null,sname char(8)unique,ssex char(2)default 男,sage smallint,sdept char(2),sbirthday date,primary key(sno);,列级完整性约束条件,表级完整性约束条件,第三章 关系数据库标准语言SQL 3.2 数据定义,3.2.1 定义、删除、与修改基本表二、修改基本表 语句格式为:ALTER TABLE ADD 完整性约束 DROP|MODIFY；功能：修改指定的基本表。ADD子句：用于增加新列和新的完整性约束条件；DROP子句：用于删除指定的完整性约束条件；MODIFY子句：用于修改原有的列定义（列名和数据类型）说明：不论基本表中原来是否有数据，新增加的列一律为空值。SQL没有提供删除属性列的语句。,第三章 关系数据库标准语言SQL 3.2 数据定义,例3：向S表增加：“入学时间”列，数据类型为日期型。ALTER TABEL S ADD SCOME DATE；注意：新增加的属性列不能指定为 NOT NULL。将年龄的数据类型改为半字长整数。ALTER TABEL S MODIFY SAGE SMALLINT；注意：修改原有的属性列定义有可能破坏已有数据。删除姓名上的唯一值约束。alter table s drop unique(sname);,第三章 关系数据库标准语言SQL 3.2 数据定义,3.2.1 定义、删除、与修改基本表三、删除基本表 语句格式为：DROP TABLE；功能：删除指定的基本表，同时表中的数据和在此表上建立的索引都将自动被删除掉。建立在此表上的视图虽仍然保留，但已无法引用。注意：执行删除操作一定要格外小心！,第三章 关系数据库标准语言SQL 3.2 数据定义,3.2.2 建立与删除索引 在基本表上建立索引是为了加快查询速度。一、建立索引语句格式为：CREATE UNIQUECLUSTER INDEX 索引名 on 基本表名（列名次序，列名次序.）其他参数;说明：索引可以建在一列或几列上,圆括号内是索引列的顺序说明表。（1）次序，指定了索引值排序的次序。可取ASC(升序)或DESC(降序)，缺省值为升序。（2）UNIQUE 表示每一索引值只对应唯一的数据记录,（3）CLUSTER 表示要建立的索引是聚簇索引，所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。例：CREATE CLUSTER INDEX Stusname on Student(Sname);此类索引可以提高查询效率，聚簇索引可以在最常查询的列上建立，对于经常更新的列不宜建立。一个基本表上最多只能建立一个聚簇索引。,第三章 关系数据库标准语言SQL 3.2 数据定义,例4 为学生-课程数据库中的Student,Couse,SC三个表建立索引。其中Student表按学号升序建唯一索引，Couse 表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATE UNIQUE INDEX Stusno ON Student(Sno);CREATE UNIQUE INDEX Coucno ON Couse(Cno);CREATE UNIQUE INDEX Sno ON SC(Sno ASC,Cno DESC);,第三章 关系数据库标准语言SQL 3.2 数据定义,二、删除索引 语句格式为:DROP INDEX 索引名;功能：删除索引，同时把有关索引的描述也从数据 字典中删去。例5：DROP INDEX XSNO;,第三章 关系数据库标准语言SQL 3.3 数据查询,一、SELECT 语法 语句格式为:SELECT ALL|DISTINCT目标列表达式，目标列表达式 FROM 基本表(或视图)，基本表(或视图)WHERE 条件表达式 GROUP BY 列名1HAVING 内部函数表达式 ORDER BY 列名2ASC|DESC UNION SELECT；功能：在指定的表中查询满足条件的数据，并按目标列表达式方式显示。,第三章 关系数据库标准语言SQL 3.4 数据查询,三、WHERE CLAUSE 条件可以包括：（1）比较(=,=,!=,!)（2）范围(between and,not between and)（3）列表(in,not in)（4）字符匹配(like,not like)（5）空值判断(is null,is not null)（6）连接（多表，单表（表的自连接）（7）嵌套查询（子查询）,第三章 关系数据库标准语言SQL 3.4 数据查询,例：比较(=,=,!=,!)求数学系学生的学号、姓名。SELECT SNO,SName FROM S WHERE Sdept=MA;求数学系年龄在岁以下的学生的学号、姓名。SELECT SNO,SName FROM S WHERE Sdept=MA and sage=19;,第三章 关系数据库标准语言SQL 3.4 数据查询,求数学系或年龄在岁以下的学生的信息。SELECT*FROM S WHERE SDept=MA or sage=19;等价形式为：SELECT*FROM S WHERE SDept=MA union SELECT*FROM S WHERE sage=19;,第三章 关系数据库标准语言SQL 3.4 数据查询,例：范围(between and,not between and)求数学系年龄在岁到岁之间，的学生的信息 SELECT*FROM S WHERE SDept=MA and sage=19 and sage=22;,第三章 关系数据库标准语言SQL 3.4 数据查询,用范围的等价形式为：SELECT*FROM S WHERE SDept=MA and sage between 19 and 22;求年龄不在岁到岁之间，的学生的信息 SELECT*FROM S WHERE sage not between 19 and 22;,第三章 关系数据库标准语言SQL 3.4 数据查询,例：列表(in,not in)求在下列各系的学生：MA（数学系），CS（计算机科学系）。SELECT*FROM S WHERE SDept IN(MA,CS);谓词 IN 实际上是一系列谓词OR的缩写。所以，上面等价的查询语句为：SELECT*FROM S WHERE SDept=MA or SDept=CS;,第三章 关系数据库标准语言SQL 3.4 数据查询,也可以用谓词 NOT IN例如，求不是数学系，计算机科学系的学生。SELECT*FROM S WHERE SDept NOT IN(MA,CS);,第三章 关系数据库标准语言SQL 3.4 数据查询,例：字符匹配(like,not like)LIKE 谓词的一般形式是：列名NOTLIKE 匹配串ESCAPE 常量字符 匹配串可以是一个完整的字符串，也可以含有通配符%和 _ 其中：%匹配任意长度的字符串；_ 匹配单个字符 LIKE可以用=代替；NOT LIKE可以用 或！=代替 ESCAPE 常量字符 表示该常量字符后的一个字符不再具有通配符的含义，而作为一个普通字符匹配。,第三章 关系数据库标准语言SQL 3.4 数据查询,例如：列名 like ACV_HHFJ%ESCAPE 求姓名是以字母D 打头的学生。SELECT*FROM S WHERE SName LIKE D%;,第三章 关系数据库标准语言SQL 3.4 数据查询,例：空值判断(is null,is not null)注意：在计算列中有空值时，要先处理或将空值列置0 查询没有成绩的学生的学号和课程号 SELECT sno,cno FROM SC WHERE grade is null;,第三章 关系数据库标准语言SQL 3.4 数据查询,例：连接（多表，单表（表的自连接）若一个查询同时涉及到两个以上的表，称为连接查询 连接查询包括：等值连接 自然连接 非等值连接 自身连接 外连接 复合条件连接,第三章 关系数据库标准语言SQL 3.4 数据查询,例：（广义笛卡尔积）SELECT s.sno,sname,sc.sno,grade FROM sc,s;例：（等值连接）SELECT s.sno,sname,sc.*FROM sc,s WHERE s.sno=sc.sno;SELECT s.sno,sname,o,cname,grade FROM sc,s,c WHERE s.sno=sc.sno and o=o;,第三章 关系数据库标准语言SQL 3.4 数据查询,例：（自然连接）SELECT s.*,o,sc.grade FROM sc,s WHERE s.sno=sc.sno;例:(自身连接)SELECT Fo,Second.pcno FROM c First,c Second WHERE First.pcno=So;,第三章 关系数据库标准语言SQL 3.4 数据查询,例:（外连接）SELECT s.*,o,sc.grade FROM sc,s WHERE s.sno=sc.sno(*);例:（复合条件连接）SELECT s.*,o,sc.grade FROM sc,s WHERE s.sno=sc.sno and cno=2 and grade 85;,第三章 关系数据库标准语言SQL 3.4 数据查询,（7）嵌套查询（子查询）一个 SELECT-FROM-WHERE语句称为一个查询块 一个查询块出现在 WHERE 子句中或HAVING 短语中的查询称为嵌套查询 SQL语句允许多层嵌套 特别指出：子查询的SELECT 语句中不能使用 ORDER BY 子句 ORDER BY 子句只能对最终查询结果排序 嵌套查询分为：简单子查询 相关子查询,第三章 关系数据库标准语言SQL 3.4 数据查询,例：简单子查询（带有IN谓词的子查询）查询选修了2号课程的学生的姓名 SELECT sname FROM s WHERE sno in(SELECT sno FROM SC WHERE cno=2);等价于：SELECT,sname FROM s WHERE sno in(s1,s3,s4,s5);,第三章 关系数据库标准语言SQL 3.4 数据查询,例：查询选修了数据库系统课程的学生的学号和姓名 SELECT s.sno,sname FROM s WHERE sno in(SELECT sno FROM SC WHERE cno in(SELECT cno FROM C WHERE cname=数据库系统);,第三章 关系数据库标准语言SQL 3.4 数据查询,例：（带有比较运算符的子查询）查询“王明”所在系的学生的学号、姓名和系 SELECT s.sno,sname,sdept FROM s WHERE sdept=(SELECT sdept FROM S WHERE sname=王明);,第三章 关系数据库标准语言SQL 3.4 数据查询,例：（带有ANY 或 ALL谓词的子查询）查询其它系比“CS”系某一学生年龄小的学生姓名和年龄 SELECT sname,sage FROM s WHERE sdept cs and sage any(SELECT sage FROM S WHERE sdept=cs);,第三章 关系数据库标准语言SQL 3.4 数据查询,例：查询其它系比“CS”系任一学生年龄小的学生姓名和年龄 SELECT sname,sage FROM s WHERE sdept cs and sage all(SELECT sage FROM S WHERE sdept=cs);,第三章 关系数据库标准语言SQL 3.4 数据查询,例：相关子查询（使用存在量词 EXISTS 和 NOT EXISTS）EXISTS 代表存在量词。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑值“TRUE”或“FALSE”查询选修了2号课程的学生的学号和姓名 SELECT s.sno,sname FROM s WHERE exists(SELECT*FROM SC WHERE sc.sno=s.sno and cno=2);,第三章 关系数据库标准语言SQL 3.4 数据查询,查询未选修了2号课程的学生的学号和姓名 SELECT s.sno,sname FROM s WHERE not exists(SELECT*FROM SC WHERE sc.sno=s.sno and cno=2);,第三章 关系数据库标准语言SQL 3.4 数据查询,四、聚合函数有 SUM，AVG，MAX，MIN，COUNT，COUNT（*）例：SELECT max(sage)FROM s;SELECT count(*)FROM s;SELECT count(sno)FROM s where sdept=ma;,第三章 关系数据库标准语言SQL 3.4 数据查询,五、结果排序 ORDER BY 子句对查询结果按一个或多个属性的升序（ASC）或降序（DESC）排列 SELECT*FROM sc ORDER BY SNO;SELECT*FROM sc ORDER BY CNO DESC;,第三章 关系数据库标准语言SQL 3.4 数据查询,六、结果分组与 HAVING 子句 例：SELECT ssex FROM s group by ssex;SELECT ssex,avg(sage)FROM s group by ssex;SELECT sdept,avg(sage)aaa FROM s group by sdept having aaa 19;,第三章 关系数据库标准语言SQL 3.4 数据查询,例：统计每个学生选修课程的门数及平均成绩 SELECT sno,count(*)as course_num,avg(grade)as avg_grade FROM sc group by sno;,第三章 关系数据库标准语言SQL 3.4 数据查询,例：统计每门课程的选修人数及平均成绩 SELECT cno,count(*)as student_num,avg(grade)as avg_grade FROM sc group by cno;,第三章 关系数据库标准语言SQL 3.4 数据查询,关于在WHERE 子句中不能使用聚合函数 SELECT sno,max(sage)FROM s GROUP BY sno;?SELECT sno,sage FROM s WHERE sage=max(sage);,第三章 关系数据库标准语言SQL 3.4 数据查询,七、集合查询例 查询计算机科学系的学生及年龄不大于19岁的学生。SELECT*FROM Student WHERE Sdept=CS UNION SELECT*FROM Student WHERE Sage=19;本查询实际上是求计算机科学系的所有学生与年龄不大于19岁的学生的并集。使用UNION将多个查询结果合并起来时，系统会自动去掉重复元组。注意，参加UNION操作的各结果表的列数必须相同；对应项的数据类型也必须相同。,第三章 关系数据库标准语言SQL 3.4 数据查询,例 查询选修了课程1或者选修了课程2的学生。SELECT Sno FROM SC WHERE Cno=1 UNION SELECT Sno FROM SC WHERE Cno=2;标准SQL中没有直接提供集合交操作和集合差操作，但可以用其他方法来实现。例 查询计算机科学系的学生与年龄不大于19岁的学生的交集，这实际上就是查询计算机科学系中年龄不大于19岁的学生。SELECT*FROM Student WHERE Sdept=CS AND Sage=19;,第三章 关系数据库标准语言SQL 3.4 数据查询,例 查询选修课程1的学生集合与选修课程2的学生集合的交集。SELECT Sno FROM SC WHERE Cno=1 AND Sno IN(SELECT Sno FROM SC WHERE Cno=2);例 查询计算机科学系的学生与年龄不大于19岁的学生的差集。SELECT*FROM Student WHERE Sdept=CS AND Sage19;,查询选修了1号课程或2号课程的同学查询选修了1号课程并且没有选修2号课程的同学查询选修了1号课程并且选修2号课程的同学,第三章 关系数据库标准语言SQL 3.3 数据更新,SQL的更新语句包括修改,删除和插入三类语句。3.3.1.插入 一.插入单个元组 语句格式为:INSERT INTO 表名(字段名,字段名.)VALUES(常量,常量.)；功能：将新元组插入指定表中。说明：（1）在INTO中没有出现的属性列，将被置为空值；（2）在表定义时说明了NOT NULL的属性列不能取空值；（3）如果INTO子句没有指明任何列名，则新插入的记录必须在每个属性列上均有值。,第三章 关系数据库标准语言SQL 3.3 数据更新,例6 将一个新学生记录（95020，陈东，男，IS，18）插入到S表 INSERT INTO S VALUES(95020，陈东，男，IS，18);或 INSERT INTO S(sno,sname,ssex,sdept,sage)VALUES(95020，陈东，男，IS，18);空值的插入例7 插入一条学生选课记录(95020，1)到SC表 INSERT INTO SC(sno,cno)VALUES(95020，1);或 INSERT INTO SC(sno,cno,grade)VALUES(95020，1,null);,第三章 关系数据库标准语言SQL 3.3 数据更新,3.3.1.插入 二.插入子查询结果 语句格式为:INSERT INTO 表名(字段名,字段名.)子查询；,第三章 关系数据库标准语言SQL 3.3 数据更新,例8 对于一个系,求学生的平均年龄,并把结果存入数据库中。CREATE TABLE DEPTAGE(SDEPT CHAR(15),AVGAGE SMALLINT);INSERT INTO DEPTAGE(SDEPT,AVGAGE)（SELECT SD,AVG(SA)FROM S GROUP BY SD）;,第三章 关系数据库标准语言SQL 3.3 数据更新,3.3.2.修改数据 语句格式为:UPDATE 表名 SET 字段=表达式,字段=表达式.WHERE 谓词;功能：修改指定表中满足谓词的元组；把这些元组按SET子句中的表达式修改相应字段上的值。,第三章 关系数据库标准语言SQL 3.3 数据更新,一、单记录修改例9.把学生S1的姓名改为 WANGPING UPDATE S SET SName=WANG PING WHERE SNO=S1;,第三章 关系数据库标准语言SQL 3.3 数据更新,例10.多记录修改.把所有学生的年龄增加1岁 UPDATE S SET Sage=Sage+1;,第三章 关系数据库标准语言SQL 3.3 数据更新,二、多表的修改例11.把学生S2的学号改为S9 UPDATE S SET SNO=S9 WHERE SNO=S2;UPDATE SC SET SNO=S9 WHERE SNO=S2;注意：修改多表数据时要保证数据库内容的一致性。,第三章 关系数据库标准语言SQL 3.3 数据更新,三、带子查询的修改 例12.将计算机系全体学生的成绩置零 UPDATE SC SET GRADE=0 WHERE CS=（SELECT SDEPT FROM S WHERE S.SNO=SC.SNO）；,第三章 关系数据库标准语言SQL 3.3 数据更新,3.3.3.删除数据 语句格式为:DELETE FROM 表名 WHERE 谓词;功能：删除指定表中的数据。说明：没有WHERE子句时表示删除此表中的全部记录,但此表的定义仍在数据字典中。,第三章 关系数据库标准语言SQL 3.3 数据更新,一、单记录删除 把学生S9删除 DELETE FROM S WHERE SNO=S9;会产生什么问题吗？,第三章 关系数据库标准语言SQL 3.3 数据更新,二、多记录删除 删除所有学生选课记录 DELETE FROM SC;SC成为一个空表,第三章 关系数据库标准语言SQL 3.3 数据更新,三、带子查询的删除将计算机系全体学生的选课记录 DELETE FROM SC WHERE CS=(SELECT SDEPT FROM STUDENT WHERE STUDENT.SNO=SC.SNO);,第三章 关系数据库标准语言SQL 3.3 数据更新,3.3.4 更新操作与数据库的一致性 增加、删除、修改操作只能对一个表操作，这会带来一些问题。如在对表更新时的数据不一致。解决的办法数据库系统引入了事务（Transaction)的概念。,第三章 关系数据库标准语言SQL 3.4 数据查询,一、SELECT 语法 语句格式为:SELECT ALL|DISTINCT目标列表达式，目标列表达式 FROM 基本表(或视图)，基本表(或视图)WHERE 条件表达式 GROUP BY 列名1HAVING 内部函数表达式 ORDER BY 列名2ASC|DESC UNION SELECT；功能：在指定的表中查询满足条件的数据，并按目标列表达式方式显示。,第三章 关系数据库标准语言SQL 3.5 视图,3.5.1 定义视图 语句格式为：CREATE VIEW 视图名(字段名,字段名.)AS 子查询 WITH CHECK OPTION;说明：（1）WITH CHECK OPTION 选项表示对视图进行UPDATE、INSERT和DELETE操作时要保证更新、插入和删除的行必须满足视图定义的谓词条件（子查询的条件表达式）。（2）子查询不允许含有ORDER BY 子句和DISTINCT短语（3）组成视图的属性列名要么全部指定要么全部省略，没有其它选择.,第三章 关系数据库标准语言SQL 3.5 视图,例:建立“CS”系的学生视图表.CREATE VIEW V_CS AS(SELECT SNO,SNAME,SAGE,SDEPT FROM S WHERE SDEPT=CS);等价为：CREATE VIEW V_CS(SNO,SNAME,SAGE,SDEPT)AS(SELECT SNO,SNAME,SAGE,SDEPT FROM S WHERE SDEPT=CS);,第三章 关系数据库标准语言SQL 3.5 视图,视图V_CS的字段名省略了,隐含为子查询中SELECT子句目标列中的诸字段(SNO,SNAME,SAGE,SDEPT).但是,在下面三种情况下必须明确指定组成视图的所有列名：（1）某个目标列不是单纯的属性列名，而是集函数或列表达式（2）多表连接时出现了几个同名列作为视图的字段（3）需要在视图中为某个列启用新的更合适的名字,第三章 关系数据库标准语言SQL 3.5 视图,例：定义一个反映学生出生年份的视图CREATE VIEW BT_S(Sno,Sname,Sbirth)AS（SELECT Sno,Sname,2002-Sage FROM S;建立学生及其平均成绩视图.CREATE VIEW V_S_G_AVG(SNO,G_AVG)AS(SELECT SNO,AVG(Grade)FROM SC GROUP BY SNO);,第三章 关系数据库标准语言SQL 3.5 视图,CREATE VIEW F_STUDENT（SNO，NAME，AGE）AS（SELECT*FROM S）；由于视图F_STUDENT是由子查询“SELECT*”建立的。如果以后修改了基本表S的结构，则S表与F_STUDENT视图的映象关系被破坏，因而该视图就不能正确工作了。可以通过修改了表结构后删除由该基本表导出的视图，然后重建同名视图的方法解决。,第三章 关系数据库标准语言SQL 3.5 视图,视图不仅可以建立在单个基本表上，也可以建立在多个基本表上。例 建立信息系选修了1号课程的学生的视图 CREATE VIEW IS_S1(Sno,Sname,Grade)AS(SELECT S.Sno,Sname,Grade FROM S,SC WHERE s.Sno=SC.Sno AND Sdept=IS AND SC.Cno=1);,第三章 关系数据库标准语言SQL 3.5 视图,视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图上，或同时建立在基本表与视图上。例：建立信息系选修了1号课程且成绩在90分以上的学生的视图。CREATE VIEW IS_S2 AS(SELECT Sno,Sname,Grade FROM IS_S1 WHERE Grade=90);,第三章 关系数据库标准语言SQL 3.5 视图,若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列但保留了码，我们称这些视图为行列子集视图。定义视图时可以根据应用的需要，设置一些派生属性列。这些派生属性由于在基本表中并不实际存在，所以有时也称他们为虚拟列。带虚拟列的视图我们称为带表达式的视图。还可以用带有集函数和GROUP BY子句的查询来定义视图。这种视图称为分组视图。,第三章 关系数据库标准语言SQL 3.5 视图,比较：视图 V_CS、BT_S及V_S_G_AVGCREATE VIEW V_CS(SNO,SNAME,SAGE,SDEPT)AS(SELECT SNO,SNAME,SAGE,SDEPT FROM S WHERE SDEPT=CS);CREATE VIEW BT_S(Sno,Sname,Sbirth)AS（SELECT Sno,Sname,2002-Sage FROM S);,定义行列子集视图,定义带表达式的视图,第三章 关系数据库标准语言SQL 3.5 视图,比较：视图 V_CS、BT_S及V_S_G_AVG学号及他的平均成绩定义为一个视图CREATE VIEW V_S_G_AVG(SNO,G_AVG)AS(SELECT SNO,AVG(Grade)FROM SC GROUP BY SNO);,定义分组视图,第三章 关系数据库标准语言SQL 3.5 视图,二、删除视图语句格式为:DROP VIEW 视图名；功能：删除视图后，视图的定义将从数据字典中删除。但由该视图导出的其他视图定义仍在数据字典中，不过该视图已失效，要一一删除。例：DROP VIEW IS_S1;,在此基础上定义的视图ISS2失效！,第三章 关系数据库标准语言SQL 3.5 视图,3.5.2 视图的查询视图定义后，用户就可以象对基本表进行查询一样对视图进行查询了。由于视图的查询上是对基本表的查询,因此基本表的变化可以反映到视图上。视图就如同窗口一样,通过视图可以看到基本表的变化.。通常,视图的查询的转换是直接了当的,但有时也会产生问题。,第三章 关系数据库标准语言SQL 3.5 视图,例:SELECT SNO,SAGE FROM V_CS WHERE SAGE20;系统执行此查询时首先进行有效性检查，检查查询的表、视图是否存在。若存在，则从数据字典中取出视图的定义，把定义中的子查询和用户查询结合起来，把它转换成等价的对基本表的查询.形成一个修正的语句,这一过程称为视图消解。,第三章 关系数据库标准语言SQL 3.5 视图,本例的修正后的查询语句中为：SELECT SNO,SAGE FROM S WHERE SDEPT=CS AND SAGE20;,注：CREATE VIEW V_CS(SNO,SNAME,SAGE,SDEPT)AS(SELECT SNO,SNAME,SAGE,SDEPT FROM S WHERE SDEPT=CS);,第三章 关系数据库标准语言SQL 3.5 视图,视图的查询的转换是直接了当的,但有时也会产生问题。例:求平均成绩为90分以上的学生学号和成绩。SELECT*FROM V_S_G_AVG WHERE GAVG=90;执行查询转换后得到一个不正确的查询语句:SELECT SNO,AVG(GRADE)FROM SC WHERE AVG(GRADE)=90;GROUP BY SNO;,第三章 关系数据库标准语言SQL 3.5 视图,WHERE子句中不能用库函数作为条件表达式。正确的查询语句应转换为:SELECT SNO,AVG(GRADE)FROM SC GROUP BY SNO HAVING AVG(GRADE)=90;但有些系统不能作这类查询转换.,第三章 关系数据库标准语言SQL 3.5 视图,3.5.3 视图的更新 更新视图是指通过视图插入（INSERT）、删除（DELETE）和修改（UPDATE）数据。由于视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新。强调指出：（1）对视图的更新最终要转换成对基本表的更新。（2）视图的更新是受限的。,第三章 关系数据库标准语言SQL 3.5 视图,一、通过视图修改数据 例:通过视图修改数据UPDATE V_CSSET SNAME=李勇MMMMWHERE SNO=S1;将转换成对基本表S的更新:UPDATE SSET SNAME=李勇MMMMWHERE SDEPT=CS AND SNO=S1;,“CS”系学生,第三章 关系数据库标准语言SQL 3.5 视图,二、通过视图插入数据例:通过视图插入数据INSERTINTO V_CSVALUES(S12,YAN XI,19,CS);将转换成对基本表的插入:INSERT INTO SVALUES(S12,YAN XI,19,CS);,CREATE VIEW V_CS(SNO,SNAME,SAGE,SDEPT)AS(SELECT SNO,SNAME,SAGE,SDEPT FROM S WHERE SDEPT=CS);,第三章 关系数据库标准语言SQL 3.5 视图,三、通过视图删除数据DELETE FROM V_CS WHERE SNO=S1;将转换成对基本表的删除：DELETE FROM S WHERE SNO=S1 AND SDEPT=CS;,数据被删除,第三章 关系数据库标准语言SQL 3.5 视图,一般地：(1)行列子集视图是可更新的,即可执行UPDATE,DELETE和INSERT 三类操作。(2)有些视图虽然不是行列子集视图,但是从理论上讲仍是可更新的,(3)有些视图是不可更新的。目前各个关系数据库系统一般都只允许对行列子集视图的更新，而各个系统对视图的更新还有进一步的规定。这些规定也不尽相同。,第三章 关系数据库标准语言SQL 3.5 视图,在DB2中,只有从单个基本表导出的视图才允许对它进行更新操作并且有以下具体的限制:1.若视图的字段是来自字段表达式或常数,则不允许对此视图执行INSERT,UPDATE操作,但允许执行DELETE操作.2.若视图的字段是来自库函数,则此视图不允许更新.3.基视图的定义中有GROUP BY 子句,则此视图不允许更新 4.若视图的定义中有DISTINCT任选项,则此视图不允许更新 5.若视图的定义中有嵌套查询,并且嵌套查询的FROM子句中涉及的表也是导出该视图的基本表,则此视图不允许更新。6.若视图是由一个以上基本表导出的则此视图不允许更新。7.一个不允许更新的视图上定义的视图不允许更新。,第三章 关系数据库标准语言SQL 3.5 视图,3.5.4 视图的作用视图的概念有很多优点:1.简化了用户的操作；2 使用户能以不同的方式看待同一数据；3.对重构数据库提供了一定程度的逻辑独立性；4.对机密数据提供了自动的安全保护功能;,第三章 关系数据库标准语言SQL 3.6 数据控制,由DBMS提供统一的数据控制功能是数据库系统的特点之一。数据控制亦称为数据保护，包括数据的安全性控制、完整性控制、并发控制和数据库的恢复。这里主要介绍SQL的安全性控制功能。某个用户对某类数据具有何种操作权利是政策问题，不是技术问题。,第三章 关系数据库标准语言SQL 3.6 数据控制,授权 SQL语言用GRANT语句向用户授予操作权限。语句格式为：GRANT,.ON TO,.WITH GRANT OPTION;功能：将对指定操作对象的指定操作权限授予指定的用户。不同类型的操作对象有不同的操作权限。,第三章 关系数据库标准语言SQL 3.6 数据控制,不同对象类型允许的操作权限表,第三章 关系数据库标准语言SQL 3.6 数据控制,例：把查询 S 表权限授给用户U1 GRANT SELECT ON TABLE S TO U1;例：把对S表和C表的全部权限授予用户U2和U3 GRANT ALL PRIVILIGES ON TABLE S,C TO U2,U3;例：把对表SC的查询权限授予所有用户 GRANT SELECT ON TABLE SC TO PUBLIC;例：把查询S表和修改学生学号的权限授给用户U4 GRANT UPDATE(Sno),SELECT ON TABLE S TO U4;,第三章 关系数据库标准语言SQL 3.6 数据控制,例：把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户 GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION;例：DBA把在数据库S_C中建立表的权限授予用户U8。GRANT CREATETAB ON DATABASE S_C TO U8;,第三章 关系数据库标准语言SQL 3.6 数据控制,3.6.2 收回权限授予的权限可以由DBA或其他授权者用REVOKE语句收回。语句格式为：REVOKE,.ON FROM,.;例：把用户U4修改学生学号的权限收回 REVOKE UPDATE(Sno)ON TABLE S FROM U4;例：收回所有用户对表SC的查询权限 REVOKE SELECT ON TABLE SC FROM PUBLIC;例：把用户U5对SC表的INSERT权限收回 REVOKE INSERT ON TABLE SC FROM U5;,第三章 关系数据库标准语言SQL 3.6 数据控制,小结：SQL提供了非常灵活的授权机制。DBA拥有对数据库中所有对象的所有权限，并可以根据应用的需要将不同的权限授予不同的用户。用户对自己建立的基本表和视图拥有全部的操作权限，并且可以把其中某些权限授予其他用户。被授权的用户如果有“继续授权”的许可，还可以把获得的权限再授予其他用户。所有授予出去的权力在必要时又都可以用REVOKE语句收回。,第