数据库第三章.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第三章,关系数据库标准语言,SQL,语言,主要内容,3.1,SQL,概述,3.2 查询语句,3.3 更新语句,3.4,SQL DDL,3.5,SQL DCL,3.6 视图,3.7 嵌入式,SQL,3.1,SQL,概述,一、,SQL,的发展及现状,二、,SQL,数据库的体系结构,三、,SQL,的功能,四、,SQL,的形式,五、,SQL,的特点,3.1,SQL,概述,一、,SQL,的发展及现状,1974年，由,Boyce,和,Chamberlin,提出,19751979，,IBM San Jose Research Lab,的关系,数据库管理系统原型,System R,实施了这种语言,SQL-86,是第一个,SQL,标准,SQL-89、SQL-92(SQL2)、SQL-99(SQL3),大部分,DBMS,产品都支持,SQL，,成为操作数据库的,标准语言,二、,SQL,数据库的体系结构,SQL,用户,Base Table,B1,View V1,View V2,Base Table,B2,Base Table,B3,Base Table,B4,Stored File,S1,Stored File,S1,Stored File,S1,Stored File,S1,外模式,模式,内模式,SQL,语言支持的关系数据库的三级模式结构,二、,SQL,数据库的体系结构,基本概念：,1,、用户可以用,SQL,语言对视图(,View),和基本表,(,Base Table),进行查询等操作，在用户观点里，视图和表一样，都是关系。,2,、,视图,是从一个或多个基本表中导出的表，本身不存储在数据库中，只有其定义，可以将其理解为一个虚表。,3,、,基本表,是本身独立存在的表，每个基本表对应一个存储文件，一个表可以带若干索引，存储文件及索引组成了关系数据库的内模式。,SQL,用户,Base Table,B1,View V1,View V2,Base Table,B2,Base Table,B3,Base Table,B4,Stored File,S1,Stored File,S1,Stored File,S1,Stored File,S1,外模式,模式,内模式,三、,SQL,的功能,数据定义（,DDL）,定义、删除、修改关系模式（基本表）,定义、删除视图（,View）,定义、删除索引（,Index）,数据操纵（,DML）,数据查询,数据增、删、改,数据控制（,DCL）,用户访问权限的授予、收回,四、,SQL,的组成(分类),DATABASE,TABLE,CREATE,VIEW,INDEX,SQL DDL：,ALTER,DROP,SQL QUERY：SELECT FROM,INSERT,SQL SQL DML：UPDATE,DELETE,GRANT,SQL DCL：REVOKE,AUDIT,嵌入式,SQL：,五、,SQL,的形式,交互式,SQL,一般,DBMS,都提供联机交互工具,用户可直接键入,SQL,命令对,数据库进行操作,由,DBMS,来进行解释,嵌入式,SQL,能将,SQL,语句嵌入到高级语言（宿主语言）,使应用程序充分利用,SQL,访问数据库的能力、宿主 语言的过程处理能力,一般需要预编译，将嵌入的,SQL,语句转化为宿主语言编译器能处理的语句,六、,SQL,的特点,4.具有查询、操作、定义和控制四种语言一体化的特点。,它只向用户提供一种语言，但该语言具有上述多种功能，且每种操作只需一种操作符。,高度非过程化的语言：,用户只需提出,“,干什么,”,，,至于,“,怎么干,”,由,DBMS,解决；用户只需要早查询语句中提出需,要什么，,DBMS,即可按路径存取，并把结果返回给用户。,2.面向集合的语言：,每一个,SQL,的操作对象是一个或多个关 系，操作的结果也是一个关系。,3.,一种语法结构，两种使用方式：,即可独立使用，又可嵌入到 宿主语言中使用，具有自主型和宿主型两种特点。,五、,SQL,的特点,5,.语言简洁、易学易用：核心功能只有,9,个动词，,语法简单，,接近英语。,SQL,功能 动词,数据库查询,SELECT,数据定义,CREATE，DROP，ALTER,数据操纵INSERT，UPDATE，DELETE,数据控制,GRANT，REVOKE,(一)数据类型,1、字符型：,CHAR(n)，VAR CHAR,2、,数字型：,INT,SMALLINT,REAL,3、,日期型：,DATE,TIME,(二)函数,数字函数:,ABS(X),SQRT(X),RAND(X),LOG(X),字符函数：,LENGTH(X$),LOWER(X$),UPPER(X$),SUBSTRING(,expression,start,length,),分组函数：,COUNT(),MAX(X),MIN(X),AVG(X),SUM(X),(三)运算符,句法操作符,：&,数值操作符,：+,逻辑操作符,：,=,=、=、,not+,确定范围：,Between A and B、Not Between A and B,确定集合：,IN、NOT IN,字符匹配：,LIKE，NOT LIKE,空值：,IS NULL、IS NOT NULL,多重条件：,AND、OR、NOT,用,WHERE,子句实现复杂查询条件,例:,求年龄在1822 (含18,22)之间的学生名及年龄,（或不在1822之间),SELECT SN,SA,FROM S,WHERE SA BETWEEN 18 AND 22；,(WHERE SA=18 AND SA=22),；,(WHERE SA NOT BETWEEN 18 AND 22)；,(WHERE SA22)；,例：,求计算机系年龄小于19的姓名及年龄,SELECT SN,SA,FROM S,WHERE SD=CS AND SA5,AND MIN(GR)60；,Having,与,Where,的区别,Where,决定哪些元组被选择参加运算，作用于关系中的元组,Having,决定哪些分组符合要求，作用于分组,4、排序,用,ORDER BY,子句对查询结果按照一个或多个列的值进行升/降排列输出,升序为,ASC；,降序,为,DESC,空值将作为最大值排序,例1：,对选修,C5,课程的学生按成绩降序排列，同分数者按学号升序排列,SELECT S#,GR,FROM SC,WHERE C#=C5,ORDER BY GR DESC，S#ASC；,例2：,求每个学生的总分并按总分降序排列,学号升序,SELECT S#，SUM(ALL GR),FROM SC,GROUP BY S#,ORDER BY 2 DESC，S#ASC；,求95级学生,中选修课程,在 5 门以上,且都及格的,学生号及总,平均分，并,按平均成绩,排序。,A,1,.,A,n,(,p,(R,1,.,R,m,),Select,A,1,A,2,.,A,n,From,R,1,R,2,.,R,m,Where P,一、单表查询,1.选择表中的列,2.选择表中的行,3.,分组与组函数,4.排 序,主要内容,二、连接查询,多表连接查询、单表连接查询、,外连接查询、复合条件连接查询.,三、嵌套查询,一、单表查询,返回单个值的子查询,返回一组值的子查询,多重子查询,二、连接查询,多表连接查询、单表连接查询、,外连接查询、复合条件连接查询.,*示范表,EMP(,EMPNO,ENAME,MGR,JOB,SAL,DEPTNO,),DEPT(,DEPTNO,DNAME,CITY,),SALGRADE(,GRADE,LOSAL,HISAL,),1、多表连接：,连接条件一：,表名1.列名1,比较运算符,表名2.列名2,连接条件二：,表名1.列名1,BETWEEN,表名2.列名2,AND,表名2.列名3,连接条件中的列名称为连接字段，其各,字段应是可比的。,执行过程：,学号 课程号 学习成绩,S#C#GRADE,S1 C1 A,S1 C2 A,S1 C3 A,S1 C5 B,S2 C1 B,S2 C2 C,.,SC,学,号 学生姓名 所属系名 学生年龄,S#SNAME SD SA,S1 A CS,20,S2 B CS 21,S3 C MA 19,S4 D CI 19,S5 E MA,20,.,S,在表1中找到第一个元组，然后从头开始扫描表2，查找到,满足条件的元组即进行串接并存入结果表中；再继续扫描表2，,依次类推，直到表2末尾。再从表1中取第二个元组，重复上述,的操作，直到表1中的元组全部处理完毕。,例题,SELECT,ENAME,SAL,GRADE,FROM,EMP,SALGRADE,WHERE,SAL,BETWEEN,LOSAL,AND,HISAL；,SELECT,S.S#，SN，CN，G,FROM,S，C，SC,WHERE,S.S#=SC.S#,AND,C.C#=SC.C#；,例2,：,求每个职工的工资级别,例1：,求选课情况，要求输出学号、姓名、课,程名与成绩,2、单表连接,：,用表别名把一个表定义为两个不同的表进行连接。,SELECT,W,.ENAME，,W,.SAL，,M,.ENAME，,M,.SAL,FROM,EMP,W,，EMP,M,WHERE,W,.MGR=,M,.EMPNO,AND,W,.SAL,M,.SAL；,例1：,求比本部门职工工资低的部门负责人,例题,SELECT,FIRST,.C#，,THIRD,.CNAME,FROM,C,FIRST,，C,SECOND,C,THIRD,WHERE,FIRST,.PC#=,SECOND,.C#,AND,SECOND,.PC#=,THIRD,.C#,例2,：,求每门课的间接先修课名（即先修课的,先修课）,3、外连接,：,在连接条件的某侧加上()或(+)，表示该侧所对应的表中可形成一个,各数据项均为空值的万能替代行，用来与另一侧对应的表中所有不满足条,件的元组进行连接。外连接符()或（+）出现在左侧称为右外连接、出现,在右侧称为左连接、两侧都出现的称为全外连接。,SELECT,DNAME，ENAME，JOB,FROM,DEPT，EMP,WHERE,DEPT.DEPTNO=EMP.DEPTNO(*)；,例1：,求各部门名及职工名、工种，要求输出,无职工的部门（职工以空值出现）,例题,SELECT,DNAME，ENAME，JOB,FROM,DEPT，EMP,WHERE,DEPT.DEPTNO,(*),=EMP.DEPTNO；,例2：,求各部门名及职工名、工种，要求输出,未分配部门的职工（部门以空值出现）,例题,SELECT,DNAME，ENAME，JOB,FROM,DEPT，EMP,WHERE,DEPT.DEPTNO),=EMP.DEPTNO,(*),；,例3：,求各部门名及职工名、工种，要求输出,无职工的部门和未分配部门的职工,4、复合条件连接,：,WHERE,子句中除了连接条件，还有其它限制条件。,SELECT,SN,GR,FROM,S,SC,WHERE,S.S#=SC.S#,AND,SC.C#=C6,AND,SC.GR90；,例1：,求选修,C6,课程且成绩超过,90,分的学生名,与成绩,连接条件,限制条件,三、,嵌套查询,在,SELECT FROM WHERE,语句结构的,WHERE,子句中可嵌入一个,SELECT,语句块,其上层,查询称为外层查询或父查询,其下层,查询称为内层查询或子查询,SQL,语言允许使用多重嵌套查询,在,子查询中不允许使用,ORDER BY,子句,嵌套查询的实现一般是从里到外，即先进行,子查询，再把其结果用于父查询作为条件,1、返回单个值的子查询,：,方法一：,SELECT,SN,SA,FROM,S,WHERE,SD,=,(,SELECT,SD,FROM,S,WHERE,SN=,刘力）；,例1：,求与刘力同一个系的学生名，年龄,1、返回单个值的子查询,：,方法二：,SELECT,FIRST.SN,FIRST.SA,FROM,S FIRST,S SECOND,WHERE,FIRST.SD=SECOND.SD,AND,SECOND.SN=,刘力；,例1：,求与刘力同一个系的学生名，年龄,2、返回一组值的子查询,：,SELECT,*,FROM,S,WHERE,S#,IN,(,SELECT,S#,FROM,SC,WHERE,C#=C6,AND,GR90)；,例1：,求选修,C6,课程且成绩超过90分的学生,方法一：,例题,SELECT,S.*,FROM,S，SC,WHERE,S.S#=SC.S#,AND,GR90,AND,C#=C6；,例1：,求选修,C6,课程且成绩超过90分的学生,方法二（,连接查询,）：,例题,方法一：,SELECT,*,FROM,S,WHERE,SD!=CS,AND,SA,ANY,(,SELECT,SA,FROM,S,WHERESD=CS)；,例2：,求比计算机系中某一学生年龄小的其他,系的学生,例题,方法二：,SELECT,*,FROM,S,WHERE,SD!=CS,AND,SA,(,SELECT,MAX(SA),FROM,S,WHERE,SD=CS)；,例2：,求比计算机系中某一学生年龄小的其他,系的学生,3、,多重子查询,：,例1：,求10#部门中工种与,SALES,部门中任一工,种相同的职工,例题,SELECT,ENAME，JOB，SAL,FROM,EMP,WHERE,DEPTNO=10,AND,JOB,IN,(,SELECT,JOB,FROM,EMP,WHERE,DEPTNO,=,（,SELECT,DEPTNO,FROM,DEPT,WHERE,DNAME=SALES)；,例题,SELECT,*,FROM,EMP,WHERE,SAL,=,(,SELECT,MIN(SAL),FROM,EMP,WHERE,ENAME,IN,(CLARK，MILLER),AND,SAL,=,(,SELECT,MAX(SAL),FROM,EMP,WHERE,ENAME,IN,（CLARK,MILLER)；,例2：,求工资介于,CLARK,与,MILLER,两个之间的职工,例题,SELECT,DEPTNO,ENAME,JOB,SAL,FROM,EMP,WHERE,JOB,IN,(,SELECT,JOB,FROM,EMP,WHERE,DEPTNO,IN,(,SELECT,DEPTNO,FROM,DEPT,WHERE,CITY,=,NEW YORK),；,例3：,求工种与在纽约的职工的工种相同的职工,总 结,连接查询,多表连接查询、单表连接查询、,外连接查询、复合条件连接查询,嵌套查询,返回单个值的子查询,返回一组值的子查询,多重子查询,四、带有,EXISTS,的相关子查询,不相关子查询,：子查询的查询条件不依赖于,父查询的称为不相关子查询。,相关子查询,：子查询的查询条件依赖于外层父,查询的某个属性值的称为相关子查询,(,Correlated,Subquery,),带,EXISTS,的子查询就,是相关子查询,EXISTS,表示存在量词,带有,EXISTS,的子查询不返回任何记录的数据，,只返回逻辑值,True,或,False,例题,不相关子查询：,SELECT,SN,FROM,S,WHERE,S#,IN,(,SELECT,S#,FROM,SC,WHERE,C#=C1)；,例1：,求所有选修了,C1,课程的学生名。,例题,相关子查询：,SELECT,SN,FROM,S,WHERE,EXISTS,(,SELECT,*,FROM,SC,WHERE,S.S#=SC.S#,AND,C#=C1)；,例1：,求所有选修了,C1,课程的学生名。,执行过程：,学号 课程号 学习成绩,S#C#GRADE,S1 C1 AS1 C2 A,S1 C3 A,S1 C5 B,S2 C1 B,S2 C2 C,.,SC,学,号,学生姓名 所属系名 学生年龄,S#,SNAME SD SA,S1,A CS,20,S2,B CS 21,S3,C MA 19,S4,D CI 19,S5,E MA,20,.,S,先在外层查询中取,S,表的第一个元组（记录），用该记录的,相关的属性值（在内层,WHERE,子句中给定的）处理内层查询，若,外层的,WHERE,子句返回,TRUE,值，则此元组送入结果的表中。然,后再取下一个元组；重复上述过程直到外层表的记录全部遍历,一次为止。,说明：,不关心子查询的具体内容，因此用,SELECT,*,Exists,+,子查询用来判断该子查询是否返回元组,当子查询的结果集非空时，,Exists,为,True,当子查询的结果集为空时，,Exists,为,False,NOT EXISTS,：,若子查询结果为空，返回,TRUE,值，,否则返回,FALSE,例题,SELECT,SN,FROM,S,WHERE,NOT EXISTS,(,SELECT,*,FROM,C,WHERE,S.S#=SC.S#,AND,C#=C1)；,例2：,列出没有选,C1,课程的学生的学号、姓名,例题,SELECT,SN,FROM,S,WHERE,NOT EXISTS,(,SELECT,*,FROM,C,WHERE,NOT EXISTS,(,SELECT,*,FROM,SC,WHERE,S.S#=SC.S#,AND,C.C#=SC.C#)；,例3：,查询选修了所有课程的学生的姓名,这门课他没选,这样的课是不存在的,例题,例4：,查询至少选修了,S1,所,选的全部课程的学生名,P:S1,选修了课程,CXQ:,学生,SX,选修了课程,CX,则:,CX,(P,Q),表示学生,SX,选修了,S1,所选的全部课程,(,P,Q)=,P,Q,X,(P,Q)=,(,X,(,(,P,Q,),),),=,(,X,(,(,P,Q,),),),=,(,X,(,P,Q,),),即：,X,(P,Q)=,(,X,(,P,Q,),),SELECT,SN,FROM,S,WHERE,NOT EXISTS,(,X,(,SELECT,*,FROM,SC SCX,S1,选课,WHERE,SCX.S#=S1,P,AND,NOT EXISTS,(,SELECT,*,FROM,SCSCY,WHERE,S.S#=SCY.S#,AND,SCX.C#=SCY.C#)；,Q,FROM,语句中使用子查询(,SQL2),在,FROM,语句中使用子查询，对查询结果定义表名及列名,例：,求平均成绩超过80分的学号及平均成绩,SELECTS#，AVG_G,FROM,(SELECTS#,AVG(ALL G),FROMSC,GROUPBYS#),ASRA(S#,AVG_G),WHEREAVG_G80;,SELECTS#,AVG(ALL G),FROMSC,GROUPBYS#,HAVINGAVG(ALL G)80,五、,SQL,的集合操作,属性个数必须一致,对应的类型必须一致,属性名无关,最终结果集采用第一个结果的属性名,缺省为自动去除重复元组,除非显式说明,ALL,Order By,放在整个语句的最后,SQL,的集合操作并,例1：查询计算机系的学生,或者,年龄不大于19岁的学生，,并按年龄倒排序。,SELECT,*,FROM,S,WHERE,SD=CS,UNION,SELECT,*,FROM,S,WHERE,AGE=19,ORDER BY,AGE DESC,SQL,的集合操作交,例2：查询计算机系的学生,并且,年龄不大于19岁的学生，,并按年龄倒排序。,(,SELECT,*,FROM,S,WHERE,SD=CS,),INTERSEC,(,SELECT,*,FROM,S,WHERE,AGE80,),；,二、删除操作,格式：,DELETE FROM,表名,WHERE,条件；,只能对整个元组操作，不能只删除某些属性上,的值,只能对一个关系起作用，若要从多个关系中删,除元组，则必须对每个关系分别执行删除命令,从关系,r,中删除满足,P,的元组，只是删除数据，,而不是定义,例1：,删除学号为95019的学生。,1、删除单个元组：,二、删除操作,DELETE FROM,SC,WHERE,S#=95019;,DELETE FROM,S,WHERE,S#=95019;,例2：,删除选课但无成绩的学生的选课信息,2、删除多个元组：,二、删除操作,DELETE FROM,SC,WHERE,GRADE,IS NULL,;,DELETE FROM,SC,清空,SC,表,例3：,删除选修,C4,且成绩小于该课程的平均成绩的记录,3、带子查询的删除语句：,二、删除操作,DELETE FROM,SC,WHERE,C#=,C4,AND,G 80,),二、删除基本表,格式：,DROP TABLE,表名,CASCADE,|,RESTRICT,；,删除一个表，及与该表相关的索引、视图、码,和外部码。,CASCADE,连同引用该表的视图、完整,性约束一起自动撤消,RESTRICT,无引用时，才可撤消,三、修改基本表,改变表名,增加列,改变列的数据类型,删除列的约束,删除列,改变列名,基本表的修改操作：,三、修改基本表,格式：,ALTER TABLE,表名,ADD,新列名 类型(长度),NOT NULL,列约束,MODIFY,旧列名 类型(长度),DROP,列约束,RENAME,旧表名,TO,新表名,增加新列，修改列，删除列的完整性约束；,改变表名及列名,。,例题,例1：,在,S,表中增加一个入学时间，为日期型,ALETR,TABLE,S,ADD,SCOME DATE；,例2：,把,SA,列的类型改为半字长整数,ALETR TABLE,S,MODIFY,SA SMALLINT；,例3：,删除对,SN,列的唯一约束,ALETR TABLE,S,DROP,UNIQUE(SN)；,删除列、改变列名,对,删除列、改变列名,的操作，可采取如下,步骤实现：,CREATE TABLE,AS,(,子查询)建一新表,删除旧表,改变表名,四、定义索引,格式：,CREATE,UNIQUECLUSTER,INDEX,索引名,ON,表名(列名,ASC|DESC,)；,对指定的表的列建立索引.,UNIQUE,表示索引值唯一,CLUSTER,表示索引是聚簇索引,索引一旦建立，交由系统使用和维护,例题,CREATE,UNIQUE,INDEX,SIDX,ON,S(S#ASC)；,CREATE,CLUSTER,INDEX,SNCDX,ON,S(SNAME ASC)；,CREATE,UNIQUE,INDEX,CIDX,ON,C(C#ASC)；,CREATE,UNIQUE,INDEX,SCIDX,ON,SC(S#ASC,C#DESC)；,五、删除索引,格式：,DROP,INDEX,索引名；,例,：,DROP INDEX CIDX；,3.5 数据控制,一、授 权,GRANT,二、收回权限,REVOKE,一、授 权,对象,对象类型,操作权限,属性列,TABLE,SELECT,INSERT,UPDATE,DELETE，ALL PRIVILEGES,视图,TABLE,SELECT,INSERT,UPDATE,DELETE，ALL PRIVILEGES,基本表,TABLE,SELECT,INSERT,UPDATE,ALTER,INDEX,DELETE ALL PRIVILEGES,数据库,DATABASE,CREATETAB,1、,DATABASE：,GRANT,CREATETAB,ON,DATABASE,数据库名,TO,用户名,|,PUBLIC,WITH GRANT OPTION；,授予用户建立表的权限,由,DBA,授权，只有,CREATETAB,一种权限,WITH GRANT OPTION,允许用户转授权(权限传播),PUBLIC,表示所有用户,格式：,2、,TABLE,级的基本表,GRANT,SELECT,|,INSERT,|,UPDATE,|,DELETE,|,ALTER,|,INDEX,|,ALL PRIVILEGES,ON,TABLE,表名,TO,用户名,|,PUBLIC,WITH GRANT OPTION；,授予用户,对表的操作权限,由,拥有该表的用户授权,格式：,3、,TABLE,级的视图(,VIEW),GRANT,SELECT,|,INSERT,|,UPDATE,|,DELETE,|,ALL,PRIVILEGES,ON,TABLE,视图名,TO,用户名,|,PUBLIC,WITH GRANT OPTION,；,授予,用户对视图的操作权限,由,拥有该视图的用户授权,格式：,4、,TABLE,级的列,GRANT,SELECT,|,INSERT,|,UPDATE,|,DELETE,|,ALL,PRIVILEGES,(,列名,),ON,TABLE,表名,|,视图名,TO,用户名,|,PUBLIC,WITH GRANT OPTION,；,授予,用户对列的操作权限,由,拥有该表或视图的用户授权,格式：,例题,例1：,GRANT,CREATETAB,ON,DATABASE,教学系统,TO,USER1；,例2：,GRANT,SELECT,INSERT,ON,TABLE,SC,TO,USER1,USER2,USER5,WITH GARNT OPTION；,例3：,GRANT,ALL,PRIVILEGES,ON,TABLE,SC,TO,USER3,USER4,USER7；,例题,例4：,把查询,Stu,表和修改学生学号的,权限授给用户,U4,GRANT,UPDATE,(,Sno,),SELECT,ON,TABLE,Stu,TO,U4;,二、,ORACLE,授权机制,在,ORACLE,中对用户的授权分为,系统级、表级、视图级,1、,系统级：,格式：,GRANT CONNECT,RESOURCE,DBA,TO,用户名,|,PUBLIC,IDENTIFIED BY,口令，,；,作用：,授予用户对,ORACLE,系统的操作权限,CONNECT,：,允许用户登录数据库，在获得表级或视图级授权后可作相应操作，但不能建立表(,CREATE TABLE),RESOURCE,：,允许建表,DBA,：,超级用户,2、表级：格式与标准,SQL,类似,3、表级：格式与标准,SQL,类似,例：,GRANT CONNECT,RESOURCE TO XUNS；,GRANT TO USER6 IDENTIFIED BY QWE,；,三、收回授权,由,DBA,或授于,DBA,权限的用户对其他用户进行权限回收。,1、,格式：,REVOKE CREATETAB,ON DATABASE,数据库名,FROM ,用户名,|,PUBLIC；,2、,格式：,REVOKE ,权限列表,ON TABLE,表名|视图名,FROM ,用户名,|,PUBLIC；,3、,格式：,REVORE,权限列表(列名,),ON TABLE,表名|视图名,FROM ,用户名,|,PUBLIC；,4、,格式：,REVOKE(DBA,RESOURCE,CONNECT),FROM ,用户名,|,PUBLIC；,例题,例1 把用户,U4,修改学生学号的权限收回,REVOKE,UPDATE,(,Sno,),ON,TABLE,Stu,FROM,U4;,例2 收回所有用户对表,SC,的查询权限,REVOKE,SELECT,ON,TABLE,SC,FROM,PUBLIC;,例3 把用户,U5,对,SC,表的,INSERT,权限收回,REVOKE,INSERT,ON,TABLE,SC,FROM,U5;,四、审计(,AUDIT),以,ORACLE,为例，分为系统级审计和用户级审计两种,1、系统级审计：,由,DBA,进行，可监测登录要求、,GRANT、REVOKE,及其他数据库级的操作,2、用户级审计：,由用户设置，主要针对用户自己创建的表或视图进行审计，监测对表或视图的访问及其他操作。,3、设置审计：,格式：,AUDIT,操作列表,ON,表名|视图名|,SYSTEM,4、,取消审计：,格式：,NOAUDIT,操作列表,ON,表名|视图名|,SYSTEM,5、,审计结果存放：,内容在数据字典表,SYS.AUDIT_TRAIL,中；,可用,SELECT*FROM SYS.AUDIT_TRAIL；,查看。,3.6 视图,视图是从一个或几个表(或视图)导出的一个特殊的表。,视图是一个虚表,数据库中只存放视图的定义,视图对应的数据仍存放在原来的表中,随着表中数据的变化，视图的数据随之改变。,对视图的查询与基本表一样,对视图的更新将受到一定的限制,视图,一、视图概念,二、定义、删除视图,三、查询视图,四、更新视图,五、视图的作用,一、视图概念,视 图,3.6,视图是一个虚表,数据库中只存放视图的定义视图对应的数据仍存放在原来的表中,随着表中数据的变化，视图的数据随之改变。,对视图的查询与基本表一样,对视图的更新将受到一定的限制,基本表,1,基本表,2,视图,视图概念示意图,二、视图定义,视 图,3.6,1、建立视图,格式：,CREATE VIEW,视图名(列名,),AS,子查询,WITH CHECK OPTION|READ ONLY；,表示视图,不可更新,下述必须指定全部列名：,某个目标列是组函数或表达式,多表连接时，目标列中出现同名列,不用原基本表的列名作为视图名,在定义视图时要么指定全部视图列，要么全部省略不写；如果省略了视图的属性列名，则视图的列名与子查询列名相同。,子查询中通常不包含,ORDER BY,和,DISTINCT,子句。,虚拟列：经过各种计算派生出的数据所设置的派生属性列。,二、视图定义,视 图,3.6,2、视图分类,行列子集视图：,从单个基本表导出，保留基本表的码，但去掉其它的,某些列和部分行的视图。,表 达 式 视 图,：带虚拟列的视图。,分 组 视 图,：子查询目标表带有组函数或子查询带有,GROUP BY,子句的视图。,例1：,建立计算机系学生视图,CREATE VIEW CS_S,AS(SELECT S#，SN，SEX，SA,FROM S,WHERE SD=CS),WITH CHECK OPTION；,(,行列子集视图),例2：,建立计算机系选修计算机体系结构课程的学生视图,CREATE VIEWCS_SCA(S#,NAME,GR),AS SELECT S.S#,SN,GR,FROM S,SC,C,WHERE S.S#=SC.S#,AND C.C#=SC.C#,AND S.SD=CS,AND CN=,计算机体系结构；,例3：,建立学生出生年份的视图,CREATE VIEW BT_S(SNO,NAME,BIRTH),AS SELECT S#,SN,2003-SA,FROM S；,(,表达式视图),(虚拟,列),例4：,建立学生平均成绩视图,CREATE VIEW S_AVG_G(SNO，AVG_G),AS SELECT S#，,AVG(ALL GR),FROM SC,GROUP BY S#,；,(,分组视图,),三、查询视图,视 图,3.6,视图消解(,View Resolution),在对视图查询时，,DBMS,将进行有效性检查,（表及视图）。若存在，则从数据字典中,取出视,图定义,，并把定义中的子查询与用户查询结合起,来,转换为等价的对基本表的查询,，然后再执行。,例1：,求计算机系年龄小于20的学生,SELECT S#，SN,FROM CS_S,WHERE SA20；,视图,消解,SELECT S#，SN,FROM S,WHERE SD=CS AND SA=90；,(,系统转换后),SELECT S#,，,AVG,（,ALL GR,）,FROM SC,WHERE AVG,(,ALL GR,),=90,GROUP BY S#；,SELECT S#,AVG(ALL GR),FROM SC,GROUP BY S#,HAVING AVG(ALL GR)=90；,(,正确的转换结果),四、更新视图,视 图,3.6,1、更新视图即通过视图插入(,INSERT,)、,删除(,DELETE,),和修改(,UPDATE,),数据，实质上转换为对基本表的更新。,2、为了防止用户对超出视图范围的基本表的数据进行操作，在定义视图时，应加上,WITH CHECK OPTION,子句，则在视图上更新数据时，,DBMS,将检查视图定义中的条件，不满足将拒绝执行。,例1：,SC_S,视图的刘茜的年龄改为20,转换前：,UPDATE CS_S,SET SA=20,WHERE SN=,刘茜,；,转换后：,UPDATE,S,SET SA=20,WHERE SN=,刘茜,AND SD=CS；,例2：,在,CS_S,中插入990075,吴迪,女,19的学生记录,转换前：,INSERT INT CS_S,VALUES(990075,吴迪,女,19)；,转换后：,INSERT INTO,S,VALUES(990075,吴迪,19,CS,女)；,例3：,删除,CS_S,中年龄大于23的学生,转换前：,DELETE FROM CS_S,WHERE SA23；,转换后：,DELETE FROM,S,WHERE SA23,AND SD=CS,；,例4：,修改990075学生平均成绩为90,转换前：,UPDATE S_AVG_G,SET AVG_G=90,WHERE SNO=990075；,不可转换。,左边程序操作失败。,3、,一般情况下，行列子集视图是可更新的，所以各,RDBS,均只允许对行列子集视图进行更新。,4、,不可更新的视图（各系统不太一致）,由多个表导出的视图，不可更新,视图的列来自表达式或常数，不可插、改、可删,视图列是来自组函数，不可更新,视图定义中含有,GROUP BY,子句，不可更新,视图定义中内层嵌套的表与查询目标同一个表，不可更新,在不允许更新的视图上定义的视图，不可更新,五、视图优点,视 图,3.6,提供数据的逻辑独立性,提供数据的安全保护功能,简化用户的操作,（对系统构成的视图，用户不必关心各表间的联系）,同一数据多种用法,3.7,嵌入,式,SQL,语句,3.7.1 嵌入式,SQL,的一般形式,3.7.2 嵌入式,SQL,语句与主语言之间的通信,3.7.3 不用游标的,SQL,语句,3.7.4 使用游标的,SQL,语句,3.7.5 动态,SQL,简介,SQL,语言提供了两种不同的使用方式：,交互式,嵌入式,为什么要引入嵌入式,SQL,SQL,语言是非过程性语言,事务处理应用需要高级语言,这两种方式细节上有差别，在程序设计的环境下，,SQL,语句要做某些必要的扩充,3.7 嵌入,式,SQL,语句,嵌入式,SQL,将,SQL,语言嵌入到某种高级语言中使用，利用高级语言的过程性结构来弥补,SQL,语言实现复杂应用方面的不足。,这种方式下使用的,SQL,语言称为嵌入式,SQL(Embedded SQL)。,嵌入,SQL,的高级语言称为主语言或宿主语言。,嵌入式,SQL,的一般形式,3.7.1,为了区分,SQL,语句与主语言语句，需要:,前缀：,EXEC SQL,结束标志：随主语言的不同而不同,以,C,为主语言的嵌入式,SQL,语句的一般形式,EXEC SQL,;,例：,EXEC SQL DROP TABLE Student,;,以,COBOL,作为主语言的嵌入式,SQL,语句的一般形式,EXEC SQL,END-EXEC,例：,EXEC SQL DROP TABLE Student END-EXEC,嵌入式,SQL,的一般形式,3.7.1,说明性语句,嵌入,SQL,语句 数据定义,可执行语句 数据控制,数据操纵,允许出现可执行的高级语言语句的地方，都可以写可执行,SQL,语句,允许出现说明语句的地方，都可以写说明性,SQL,语句,嵌入式,SQL,语句与主语言之间的通信,3.7.2,1.,SQL,通信区,向主语言传递,SQL,语句的执行状态信息,主语言能够据此控制程序流程,2.主变量,