5SQL更新定义与安全控制.ppt

swufe2.1关系数据库标准语言关系数据库标准语言关系数据库标准语言关系数据库标准语言SQLSQLn数据更新n数据定义n数据控制n嵌入式SQLswufe2.2新增数据新增数据n新增一门课程：(编号：10，名称：运筹学，学分：3)n“is系所有同学选修10号课程swufe2.3添加添加 数数 据据 命令命令 n插入数据：向指定表中插入一条或多条元组；nINSERTINTO()nVALUES()|子查询;nINTO子句n属性列的顺序可与表定义中的顺序不一致n可以只指定局部属性列必须包含主属性n如果不指定属性列：表示要插入的是一条完整的元组，且属性列顺序与表定义中的顺序一致不建议使用nVALUES子句：用于插入知道具体属性值的单条元组；n子查询：用于插入从数据库中查询得到的多条元组；nVALUES子句提供的值n子查询SELECT子句的目标列必须与INTO子句匹配：值的个数值的类型swufe2.4添加数据例题续添加数据例题续添加数据例题续添加数据例题续现有一个新建的学生平均成绩表stu_avg(空表，用于存放每个学生的学号和平均成绩。SnoCHAR(8),Avg_gradenumber(5,1)请向表中插入数据：INSERTINTOstu_avg(Sno，Avg_grade)SELECTsno，AVG(grade)FROMScGROUPBYSno；swufe2.5修改数据修改数据修改数据修改数据将所有学生的年龄增加1岁将10“课程名改为运筹学原理及应用，同时学分改为2将“IS系全体学生的成绩折算为总分值60分的成绩。swufe2.6修改数据命令修改数据命令修改指定表中满足WHERE子句条件的元组的指定列；UPDATE SET =，=WHERE ；注意：如果条件中涉及其他表的属性，应该在注意：如果条件中涉及其他表的属性，应该在WHERE中中用子查询。用子查询。swufe2.7删除数据删除数据删除年龄大于30岁的学生根本信息。删除“IS系的学生的选课记录。实际系统中删除信息不一定是真正的删除swufe2.8删除数据命令删除数据命令删除指定表中的元组DELETEFROMWHERE；如果无WHERE子句，那么表示修改或删除表中的所有元组；如果WHERE条件中涉及其他表中的属性，使用子查询;swufe2.9更新数据与完整性约束更新数据与完整性约束更新数据与完整性约束更新数据与完整性约束DBMS在执行数据更新语句时会检查操作是否破坏表上已定义的完整性规那么：实体完整性：主属性不能插入空值；主码取值必须唯一。参照完整性：更新操作不能违背参照完整性；在删除某一外码引用的候选码值所在的行时，可能存在以下三种处理情况：不允许删除级联删除设为空值用户定义的完整性对于有NOTNULL约束的属性列是否提供了非空值对于有UNIQUE约束的属性列是否提供了非重复值对于有值域约束的属性列所提供的属性值是否在值域范围内swufe2.10练习练习练习练习n将成绩在55到60之间的学生的成绩增加5分n将选修了学分大于3的课程的成绩增加5%n删除成绩为空的选修记录n删除学分为0的课程对应的选修信息n将“200215010，“张明，“20，“CS插入STUDENT表的SNO，SNAME，SAGE，SDEPTn200215010同学选修了所有课程swufe2.11数数 据据 定定 义义 swufe2.12定义根本表定义根本表CREATETABLE，；表名：所要定义的根本表的名字可以是模式名.表名列名：组成该表的各个属性列名列级完整性约束条件：涉及相应属性列的完整性约束条件表级完整性约束条件：涉及一个或多个属性列的完整性约束条件swufe2.13SQL2标准中的常用数据类型标准中的常用数据类型n字符型字符型nchar(n)固定长度字符串固定长度字符串nvarchar(n)变长长度字符串变长长度字符串 ORACLE:VARCHAR2(N)n数值型数值型nInt 整型整型 NUMBER(P,D)nsmallint.短整型短整型(半字长整数半字长整数)nReal 双精度浮点型双精度浮点型nfloat(n)单精度浮点型，单精度浮点型，n为有效数位为有效数位nnumeric(p,d)/decimal(p,d 定点小数，定点小数，p为有效数位，为有效数位，d为小数位数。为小数位数。n日期时间型日期时间型ndate.日期型日期型4位年。一般格式：位年。一般格式：yyyy-mm-dd 例如：例如：2004-7-27ntime.时间型。一般格式：时间型。一般格式：hh-mi-ss 例如：例如：09:00:30.75ntimestamp:时间戳。例如：时间戳。例如：.2001-7-27 09:00:30.75nBig object type(in oracle)nBlob;clob,nclob;BfilenRowid:nRowid,urowid数据类型确实定？数据类型确实定？swufe2.14常用完整性约束条件常用完整性约束条件n常用列级完整性约束条件nNOTNULLnPRIMARYKEY仅适用于单属性主码nUNIQUEnCHECK定义域n常用表级完整性约束条件nPRIMARYKEY(主码)nCHECK(条件)nFOREIGNKEY(外码)REFERENCES表名与外码对应的主码nUNIQUE属性n数据约束与数据操作约束swufe2.15完整性约束处理完整性约束处理n定义n定义子句n命名：constraint名称定义子句n检查n数据更新语句增、删、改执行后或事务提交时n违约处理n拒绝操作NOACTION)n级联操作CASCADE)n设置为空SETNULLswufe2.16缺省值的设置缺省值的设置n列定义后跟：DEFAULT值n例如：createtablesc(gradenumber(3,1)default0);swufe2.17定义根本表例题定义根本表例题 建立一个“学生表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号为主码，并且姓名取值非空。增加约束：Ssex取值为0表示男或1表示女;设置缺省值为0CREATETABLEStudent(SnoCHAR(5)PRIMARYKEY，SnameCHAR(20)constraints_nameNOTNULL，SsexCHAR(1)，SageINT，SdeptCHAR(15)；SsexCHAR(1)default0check(ssex=0orssex=1)swufe2.18例题例题 续续建立一个“学生选课表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。Grade取值0-100，Sno为外码，删除学生根本信息时，同时删除其选修信息。CREATE TABLE SC(Sno CHAR(5),Cno CHAR(3),Grade int check(grade=0 and grade=1ANDCREDIT=4);练习：建立一个“课程表course，它由课程号Cno，课程名cname、先修课pcno、学分credit组成，其中Cno为主码，cname 非空，学分为14，隐含为2。swufe2.20根据查询结果创立新表根据查询结果创立新表nCREATETABLE表名(属性列表)AS子查询；e.gcreatetablesasselect*fromstudentwheresdept=is;swufe2.21修改根本表修改根本表ALTERTABLEADD或DROPcolumn|constraintMODIFY；：要修改的根本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的列或完整性约束条件很多数据库系统不支持删除列MODIFY子句：用于修改列的定义约束的重命名与禁止和激活swufe2.22修改根本表例题修改根本表例题 n向Student表增加“SCOME入学时间列，其数据类型为日期型。n不管根本表中原来是否已有数据，新增加的列一律为空值n对SC增加CNO的参照完整性约束n将学生姓名的长度减少为10个字符n注：修改原有的列定义有可能会破坏已有数据n删除属性列很多数据库系统不支持该功能:swufe2.23删除根本表删除根本表 DROPTABLE;系统从数据字典中删去有关该根本表的描述删除Student表DROPTABLEStudent;swufe2.24索引索引 n建立索引是加快查询速度的有效手段n索引分类：聚簇索引ClusteredIndexn非聚簇索引：唯一索引、B树索引等n建立索引nDBA或表的属主即建立表的人根据需要建立n很多DBMS自动建立具有以下约束的列上的索引nPRIMARYKEYnUNIQUEn维护和使用索引：DBMS自动完成swufe2.25建立和删除索引建立和删除索引 CREATEUNIQUECLUSTEREDINDEXON(,)；索引可以建立在该表的一列或多列上，各列名之间用逗号分隔次序指索引值的排列次序，升序：ASC缺省值，降序：DESC。DROPINDEX；删除索引时，系统会从数据字典中删去有关该索引的描述ALTERindexrebuild;swufe2.26定义练习定义练习建立以下表和索引：1、书BOOK：书号BNO主码、书名BNAME非空、出版社PUBLISHER2、读者READER：学号SNO主码、姓名SNAME非空、班级SCLASS、可借书数量SCOUNT取值为1-10，缺省为33、借书BORROW：学号SNO、书号BNO、借阅日期BDATE其中，学号SNO、书号BNO是主码学号SNO、书号BNO是两个外码4、为表BOOK按出版社升序建立索引IDX_BOOK5、在读者READER表中增加一个属性：性别SGENDER，取值为F女或M男6、将索引IDX_BOOK更名为IDX_PUBLISHER7、删除索引IDX_PUBLISHERswufe2.27视视视视 图图图图n视图的特点视图的特点n虚表，是从一个或几个根本表或视图导出的表虚表，是从一个或几个根本表或视图导出的表n数据库中只存放视图的定义，不会出现数据冗余数据库中只存放视图的定义，不会出现数据冗余n基表中的数据发生变化，从视图中查询出的数据也随之改变基表中的数据发生变化，从视图中查询出的数据也随之改变n什么时候需要建立和使用视图？什么时候需要建立和使用视图？swufe2.28建立视图建立视图建立视图建立视图n语句格式CREATE VIEW ()AS WITH CHECK OPTION；省略视图的属性列：视图由子查询中SELECT目标列中的诸属性组成以下情况下需要明确指定视图的所有属性列：某个目标列是集函数或列表达式多表非自然连接时选出了同名属性需要在视图中为某个列启用新的更适宜的名字WITHCHECKOPTION带有WITHCHECKOPTION选项，表示通过视图进行增删改操作时，不能破坏视图定义中子查询的条件表达式DBMS创立视图时只是把视图的定义存入数据字典，并不执行其中的SELECT语句。swufe2.29视图定义例题视图定义例题建立IS系学生的视图IS_Student。CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERESdept=IS；或CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept=IS WITH CHECK OPTION ；对修改操作、删除操作，DBMS自动加上Sdept=IS的条件在ORACLE中，不能执行插入操作，因为该视图中不存在SDEPT属性，不能满足建立视图时的条件swufe2.30视图定义例题续视图定义例题续建立学生平均成绩的视图Stu_avg，包括学号、姓名、平包括学号、姓名、平均成绩均成绩。CREATE VIEW stu_avg(sno,sname,avg_grade)AS SELECT s.Sno，Sname，avg(Grade)FROM student s,sc WHERE s.sno=sc.sno GROUP BY S.SNO,SNAME；swufe2.31行列子集视图行列子集视图n从单个根本表导出n保存了根本表的主码n只是去掉了根本表的某些行和某些列n行列子集视图是可以更新的视图swufe2.32删除视图删除视图nDROPVIEW；H该语句从数据字典中删除指定的视图定义H由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式删除H删除基表时，由该基表导出的所有视图定义都要显式删除n例如：删除视图IS_S1 DROPVIEWIS_S1；swufe2.33 查询和更新视图查询和更新视图n从用户角度：查询和更新视图与根本表相同，对视图的查询和更新最终转换为对根本表的操作。n定义视图时如果使用了WITHCHECKOPTION子句，DBMS在更新视图时会进行条件检查n一些视图是不可更新的，因为对这些视图的更新不能转换成对相应根本表的更新，一般只允许对行列子集视图进行更新swufe2.34视图的作用视图的作用视图的作用视图的作用n视图能够简化用户的操作n用户可以直接对视图进行操作，不用对多张根本表进行复杂的查询；n视图使用户能以多种角度看待同一数据n例如：student表中的sage，通过建立不同的视图对不同用户可以得到不同的信息：年龄、出生年n视图对重构数据库提供了一定程度的逻辑独立性n例：将学生关系Student(Sno，Sname，Ssex，Sage，Sdept)“垂直地分成两个根本表：nSX(Sno，Sname，Sage)nSY(Sno，Ssex，Sdept)n通过建立视图维持原有的对student表的操作n视图能够对机密数据提供平安保护n用户只能看到自己需要的数据，对数据的更新可以通过withcheckoption进行限制swufe2.35六、六、六、六、数据控制数据控制数据控制数据控制SQL语言提供了数据控制功能，能够在一定程度上保证数据的:完整性定以及本表时可以指定完整性约束：主码与外码取值唯一取值非空其他条件的约束平安性控制用户只能存取授权范围内的数据，主要包括授权和收回权限并发控制及恢复提供事务开始、结束、提交、回滚、重做等概念和操作swufe2.36DB 平安性平安性n用户身份识别nUserid&passwordn存取控制nDiscretionaryaccesscontrol(DAC自主存取控制)nAuthorization授权&权限检查nMandatoryaccesscontrol(MAC强制存取控制)n密级对数据&许可证级别对用户:ntopsecret绝密,secret机密,confidential可信,public公开n视图n审计n加密swufe2.37权限管理权限管理权限管理权限管理n授予权限：GRANTGRANT ,.ON .ON TO TO WITH GRANT OPTION;WITH GRANT OPTION;n回收权限：REVOKEREVOKE ,.ON .ON FROMFROM ;nWITH GRANT OPTION 表示被授权用户可以将获得的权限再授予其他用户；n用户名PUBLIC表示全体用户n权限：常用对象常用对象权限基本表、视图select、update(属性名)、insert、delete、all系统权限createtable、altertable、droptable、createview、dropview、swufe2.38权限管理例题权限管理例题n把对表SC的查询权限授予所有用户GRANT SELECT ON SC TO PUBLIC;n把查询Student表和修改学生年龄的权限授给用户u2和u3GRANT UPDATE(Sage),SELECT ON Student TO u2,u3;n把建立表的权限授予用户U8GRANT CREATE TABLE TO U8;n把对表SC的INSERT权限授予用户U5，并允许他再将此权限授予其他用户 GRANT INSERT ON SC TO U5 WITH GRANT OPTION;n收回用户U4修改student表sage属性的权限REVOKE UPDATE(Sage)ON Student FROM U4;n收回所有用户对表SC的查询权限REVOKE SELECT ON SC FROM PUBLIC;swufe2.39练习练习练习练习建立成绩在90分以上的学生选修信息的视图将对表SC的查询、删除数据权限授予用户st01将创立表的权限授予用户st01；创立根本表AVGGRADE,属性包括SNO,GRADE；并将每个学生的平均成绩插入AVGGRADE表swufe2.40角色角色角色角色n什么是角色？有何作用？什么是角色？有何作用？ncreate role teacher create role managerngrant select on student to teacher grant update(grade)on sc to teachergrant all on sc to managerngrant teacher to managerswufe2.41七、七、嵌嵌 入入 式式 SQLnSQL语言提供了两种不同的使用方式：交互式、嵌入式n嵌入式SQL的一般形式n区分SQL语句与主语言语句：需要为SQL语句指定n前缀：EXECSQLn结束标志：随主语言的不同而不同，常用主语言，例如c、powerbuilder等都以“;结束nn例：EXECSQLDROPTABLEStudent;swufe2.42主语言与主语言与SQL之间的通信之间的通信1.SQL通信区SQLCommunicationArea，SQLCA向主语言传递SQL语句的执行状态信息，使主语言能够据此控制程序流程2.主语言向SQL语句传递数据即参数通过主变量3.SQL语句向主语言传递数据即查询结果1SQL语句查询结果为单个元组时，使用主变量2SQL语句查询结果为多个元组时，使用游标，解决集合性操作语言与过程性操作语言的不匹配swufe2.43SQLCA的使用方法的使用方法n定义SQLCAnEXECSQLINCLUDESQLCAn使用SQLCAnSQLCA中有一个变量SQLCODE，用来存放最近执行的SQL语句的返回代码；n如果SQLCODE等于预定义的常量SUCCESS或0，那么表示SQL语句成功，否那么表示出错n应用程序每执行完一条SQL语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理n例如：以主语言C语言为例。nEXECSQLINCLUDESQLCA;/*说明性语句*/nEXECSQLGRANTSELECTONStudentTOU1;nif(sqlca.sqlcode=0)nprintf(“thesqlstatementsucceedn);nelsenprintf(“thesqlstatementerrorn);nexecsqldroptablecourse;swufe2.442.主变量主变量n在SQL语句中使用的主语言变量简称为主变量HostVariablen主变量的类型n输入主变量：由主语言赋值，在SQL语句中引用的变量。用于主语言向SQL语句传递参数n输出主变量：由SQL语句赋值，在主语言中引用的变量。用于SQL语句向主语言传递结果.一般出现在SQL语句的SELECT子句中.n注意：一个主变量有可能既是输入主变量又是输出主变量n主变量的使用n1)声明主变量execsqlBEGINDECLARESECTIONn.(主变量定义)nexecsqlENDDECLARESECTIONn2)使用主变量n为了与数据库对象名特别是属性名相区别，SQL语句中的主变量名前要加冒号:作为标志；n例如：SELECT属性1，属性2，into:主变量1，:主变量2,n主语言语句中可以直接引用主变量，不必加冒号swufe2.45主变量的使用主变量的使用例如：在c语言中使用sql：execsqlincludesqlca;/*说明性语句*/execsqlbegindeclaresection;/*说明性语句，定义主变量*/charv_sno8;charv_cno3charv_sname20;floatv_grade;execsqlenddeclaresection;v_cno=1;/*主语言中为主变量赋值*/v_sno=95001;execsqlselectsname,gradeinto:v_sname，:v_grade/*查询语句*/fromstudent,scwherestudent.sno=sc.snoandsc.sno=:v_snoandcno=:v_cno;if(sqlca.sqlcode!=0)printf(“sqlerrorn);return；printf(“thestudentsnameis:%s,scoreis:%5.2fn,v_sname,v_grade);swufe2.463.游标游标n游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，用于实现将sql查询语句的多元组结果数据传给主语言。n每个游标区都有一个名字n用户可以从游标中逐条获取记录，并赋给主变量，交由主语言进一步处理n使用游标的步骤n1.说明游标：开辟缓冲区nEXECSQLDECLARECURSORFOR;n2.翻开游标：EXECSQLOPEN;n实际上是执行相应的SELECT语句，把所有满足查询条件的记录从指定表取到缓冲区中，游标指针指向查询结果集中第一条记录之前。n3.移动游标指针，取当前记录。nEXECSQLFETCHINTO,.;nFETCH语句通常用在一个循环结构中，逐条取出结果集中的元组或称记录进行处理n4.关闭游标：释放结果集占用的缓冲区及其他资源nEXECSQLCLOSE;swufe2.47游标举例游标举例EXECSQLDECLAREc_scCURSORFORselectsno,gradefromscwherecno=1orderbysno;EXECSQLOPENc_sc;for(introws=0;rows+)EXECSQLFETCHc_scINTO:v_sno,:v_grade;if(sqlca.sqlcode=0)printf(“%s:%dn,v_sno,v_grade);elseprintf(“theendn);break;EXECSQLCLOSEc_sc;swufe2.48修改数据修改数据n不通过游标修改数据EXECSQL语句n通过游标修改数据 declare c cursor for select*from sc wherecno=1 for update;修改每一条纪录：修改每一条纪录：update student setsage=sage+1where current of c;swufe2.49动态动态SQLSQLn1.什么是动态嵌入式SQLn动态SQL方法允许在程序运行过程中临时“组装SQL语句。n2.应用范围:在预编译时SQL语句不能确定n3.动态SQL的形式 n整个sql语句可变:临时构造完整的SQL语句 n条件可变:临时组成 WHERE子句或HAVING短语中的条件 n 数据库对象、查询条件均可变 nSELECT子句中的列名 nFROM子句中的表名或视图名 nWHERE子句中的条件 n HAVING短语中的条件swufe2.50动态动态SQLSQL续续n4.常用动态SQL语句 nEXECUTE IMMEDIATE nPREPARE nEXECUTEnDESCRIBEn 使用动态SQL技术更多的是涉及程序设计方面的知识，而不是SQL语言本身n例如：nchar account 10=“A-101;char*sqlsource=“update account set balance=balance*1.05 where account_number=?;EXEC SQL prepare sqlprep from:sqlsource;EXEC SQL execute sqlprep using:account;