第三章 关系数据库标准语言SQL-2.ppt

第三章 关系数据库标准语言SQL,3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 嵌入式SQL,3.4 数据更新,一、插入数据 二、修改数据 三、删除数据,一、插入数据,(1) 插入单个元组新元组插入指定表中。 语句格式： INSERT INTO (，) VALUES ( ， ) ; INTO子句 指定要插入数据的表名及属性列 属性列的顺序可与表定义中的顺序不一致 没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致 指定部分属性列：插入的元组在其余属性列上取空值 VALUES子句 提供的值的个数和值的类型必须与INTO子句匹配,一、插入数据,例1 将一个新学生记录 ( 学号: 95020; 姓名: 陈冬; 性别: 男; 所在系: IS; 年龄: 18岁 ) 插入到Student表中。 INSERT INTO Student VALUES ( '95020' , '陈冬' , '男' , 'IS' , 18) ; 例2 插入一条选课记录( 95020，1 )。 INSERT INTO SC(Sno，Cno) VALUES ('95020 '，'1') ; 注：新插入的记录在Grade列上取空值。,一、插入数据,(2) 插入子查询结果 语句格式： INSERT INTO ( ， ) 子查询； INTO子句(与插入单条元组类似) 指定要插入数据的表名及属性列 属性列的顺序可与表定义中的顺序不一致 没有指定属性列：表示要插入的是一条完整的元组 指定部分属性列：插入的元组在其余属性列上取空值 子查询 SELECT子句目标列属性的个数和类型必须与INTO子句匹配。,一、插入数据,例3 对每一个系，求学生的平均年龄，并把结果存入数据库。 第一步：建表 CREATE TABLE Deptage ( Sdept CHAR(15) ， /* 系名*/ Avgage SMALLINT ) ; /*学生平均年龄*/ 第二步：插入数据 INSERT INTO Deptage( Sdept, Avgage) SELECT Sdept, AVG(Sage) FROM Student GROUP BY Sdept ;,一、插入数据,DBMS在执行插入语句时会检查欲插入的元组是否会破坏表上已定义的完整性规则。如果破坏，系统会提示，且该记录不会被插入到数据库中。 实体完整性 对于插入的记录主码不为空且不可以与表中已有记录主码相同。 参照完整性 外码必须取空值或被参照关系中对应属性的值。 用户定义的完整性 对于有NOT NULL约束的属性列是否提供了非空值 对于有UNIQUE约束的属性列是否提供了非重复值 对于有值域约束的属性列所提供的属性值是否在值域范围内,二、修改数据,语句格式： UPDATE SET =, = WHERE ； 功能： 修改指定表中满足WHERE子句条件的元组。 注： SET子句指定修改方式，要修改的列和修改后取值 WHERE子句 指定要修改的元组 缺省表示要修改表中的所有元组,二、修改数据,(1) 用WHERE子句指定修改单个记录 例4 将学生95001的年龄改为22岁。 UPDATE Student SET Sage = 22 WHERE Sno = '95001' ； (2) 用WHERE子句(或不用)指定修改多个记录 例5 将所有学生的年龄增加1岁。 UPDATE Student SET Sage = Sage+1； 例6 将信息系所有学生的年龄增加1岁。 UPDATE Student SET Sage = Sage+1 WHERE Sdept = 'IS' ；,二、修改数据,(3) 用子查询指定要修改的记录 例7 将计算机科学系全体学生的成绩置零。 UPDATE SC SET Grade=0 WHERE 'CS'= ( SELECT Sdept FROM Student WHERE Student.Sno = SC.Sno) ;,二、修改数据,DBMS在执行修改语句时会检查欲修改的元组是否会破坏表上已定义的完整性规则。如果破坏，系统会提示，且该记录不会被修改。 实体完整性 对于修改的记录主码不为空且不可以与表中已有记录主码相同。 参照完整性 外码必须取空值或被参照关系中对应属性的值。 用户定义的完整性 对于有NOT NULL约束的属性列是否提供了非空值 对于有UNIQUE约束的属性列是否提供了非重复值 对于有值域约束的属性列所提供的属性值是否在值域范围内,三、删除数据,语句格式： DELETE FROM WHERE ； 功能 删除指定表中满足WHERE子句条件的元组 注： WHERE子句 指定要删除的元组 缺省表示要修改表中的所有元组,三、删除数据,(1) 用WHERE子句指定删除单个记录 例8 删除学号为95019的学生记录。 DELETE FROM Student WHERE Sno = '95019' ； (2) 用WHERE子句(或不用)指定删除多个记录 例9 删除2号课程的所有选课记录。 DELETE FROM SC WHERE Cno = '2' ; 例10 删除所有的学生选课记录。 DELETE FROM SC ;,三、删除数据,(3) 用子查询指定要删除的记录 例11 删除计算机科学系所有学生的选课记录。 DELETE FROM SC WHERE 'CS' = ( SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno ) ; 或 DELETE FROM SC WHERE Sno IN ( SELECT Sno FROM Student WHERE Student.Sno = 'CS' ) ;,三、删除数据,DBMS在执行删除语句时会检查所删除的元组是否会破坏表上已定义的完整性规则。如果破坏，系统会提示，且该记录不会被删除。 参照完整性 不允许删除 级联删除(用CASCADE参数指定) 例：删除学号为95019的学生记录。 DELETE FROM Student WHERE Sno = '95019' ； 则此时在SC表中依然有学号为95019的学生的选课记录，破坏了参照完整性。需要采用级联删除的方法或是用显式的SQL语句删除SC表中的数据。,3.4 数据更新,DBMS在执行插入、删除、修改语句时 必须保证数据库的完整性和一致性。 必须以事务处理的方式进行数据更新 完整性检查和保证 例：从帐户A向帐户B转帐100元，使用的处理方式分为两步： (1) 从帐户A减去100元； (2) 给帐户B加上100元。 要求这两步要么不作，要么全部执行。否则会引起数据库的不一致性。需要用事务处理程序实现这一转帐操作。 例：删除某一学生记录，必须删除该学生的选课记录，需要两条显式的删除语句时，也要使用事务处理的方法实现。这种情况通常采用一种特殊的事务触发器，当删除学生记录时由触发器调用删除该学生选课记录的程序来实现。,第三章 关系数据库标准语言SQL,3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 嵌入式SQL,3.5 视图,什么是视图(View)？ 视图是从一个或几个基本表（或视图）导出的表，它与基本表不同，是一个虚表。 在数据字典中只存放视图的定义，不会出现数据冗余。 基表中的数据发生变化，从视图中查询出的数据也随之改变。 视图一经定义，就可以和基本表一样被查询和删除，并且可以在视图之上再定义新的视图。 视图的更新(增加、删除、修改)操作会受到一定的限制。 视图对应三级模式体系结构中的外模式。,一、视图定义,(1) 建立视图 语句格式： CREATE VIEW ( ，) AS WITH CHECK OPTION； CREATE VIEW 子句中的列名可以省略，此时视图的属性由子查询中SELECT目标列中的诸字段组成。但在下列情况下明确指定视图的所有列名： (1) 某个目标列是集函数或列表达式 (2) 多表连接时选出了几个同名列作为视图的字段 (3) 需要在视图中为某个列启用新的更合适的名字 子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDER BY子句和DISTINCT短语。 WITH CHECK OPTION表示对视图进行更新操作的数据必须满足视图定义的谓词条件(子查询的条件表达式)。,一、视图定义,DBMS执行CREATE VIEW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句。在对视图查询时，按视图的定义从基本表中将数据查出。 例1 建立信息系学生的视图。 CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept= 'IS' ; 若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了码，这类视图称为行列子集视图。 上例所建立的视图即为行列子集视图。,例2 建立信息系学生的视图，并要求透过该视图进行的更新操作只涉及信息系学生。 CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept= 'IS' WITH CHECK OPTION ; 针对此视图，当进行以下操作时， 修改操作：DBMS自动加上Sdept= 'IS'的条件 删除操作：DBMS自动加上Sdept= 'IS'的条件 插入操作：DBMS自动检查Sdept属性值是否为'IS' (1) 如果不是，则拒绝该插入操作 (2) 如果没有提供Sdept属性值，则自动定义Sdept为'IS',一、视图定义,例3 建立信息系选修了1号课程的学生视图。 CREATE VIEW IS_S1( Sno, Sname, Grade ) AS SELECT Student.Sno, Sname, Grade FROM Student, SC WHERE Sdept= 'IS' AND Student.Sno=SC.Sno AND SC.Cno= '1' ; 例4 建立信息系选修了1号课程且成绩在90分以上的学生的视图。 CREATE VIEW IS_S2 AS SELECT Sno, Sname, Grade FROM IS_S1 WHERE Grade=90；,一、视图定义,例5 定义一个反映学生出生年份的视图。 CREATE VIEW BT_S(Sno, Sname, Sbirth) AS SELECT Sno, Sname, 2000-Sage FROM Student ; 当子查询中有由表达式构成的派生属性列(也称为虚拟列)时，必须明确定义组成视图的各个属性列名。 例6 将学生的学号及他的平均成绩定义为一个视图 CREATE VIEW S_G (Sno, Gavg) AS SELECT Sno, AVG (Grade) FROM SC GROUP BY Sno ;,一、视图定义,例7 将Student表中所有女生记录定义为一个视图 CREATE VIEW F_Student1 (stdnum, name, sex, age, dept) AS SELECT * FROM Student WHERE Ssex=女; 存在问题： 修改基表Student的结构后(在非末尾增加一列)，Student表与F_Student1视图的映象关系会被破坏，导致该视图不能正确工作。 处理方法： 在子查询的SELECT子句中明确指出各属性列的名称，可以避免对基本表的属性列增加而破坏与视图间的映象关系，但不能解决修改列名的问题，因此对基本表修改后采用重建视图的方法。,一、视图定义,(2) 删除视图 语句格式： DROP VIEW ； 注： 该语句从数据字典中删除指定的视图定义 由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式删除 删除基表时，由该基表导出的所有视图定义都必须显式删除 例8 删除视图IS_S1 DROP VIEW IS_S1 ; 由于有在视图IS_S1之上建立的视图IS_S2，在IS_S1被删除后IS_S2已经无法使用，这时应该使用下列语句删除IS_S1： DROP VIEW IS_S2 ;,二、查询视图,从用户角度：查询视图与查询基本表相同 DBMS实现视图查询的方法 实体化视图（View Materialization） (1) 有效性检查：检查所查询的视图是否存在 (2) 执行视图定义，将视图临时实体化，生成临时表 (3) 查询视图转换为查询临时表 (4) 查询完毕删除被实体化的视图(临时表) 视图消解法（View Resolution） (1) 进行有效性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义 (2) 把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询 (3) 执行修正后的查询,例1 在信息系学生的视图中找出年龄小于20岁的学生。 信息系学生的视图定义 (视图定义例1)： CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept = 'IS' ; 查询语句： SELECT Sno, Sage FROM IS_Student WHERE Sage 20 ; 视图实体化法：通过视图定义建立视图结构下的临时表并对临时表进行查询，在查询结束后删除临时表。 视图消解法：根据视图定义将对视图的查询转换为对基本表的查询，转换后的查询语句如下： SELECT Sno, Sage FROM Student WHERE Sdept= 'IS' AND Sage20；,二、查询视图,例2 查询信息系选修了1号课程的学生 SELECT Sno, Sname FROM IS_Student, SC WHERE IS_Student.Sno = SC.Sno AND SC.Cno= '1' ; 注：此例的执行是将虚表和基本表联结来完成的。,例3 在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩。,S_G视图定义： CREATE VIEW S_G (Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno；,对视图的查询语句： SELECT * FROM S_G WHERE Gavg=90；,使用视图消解转换的查询： SELECT Sno，AVG(Grade) FROM SC WHERE AVG(Grade)=90 GROUP BY Sno；,正确转换的查询： SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade)=90；,注：由于对定义中含有集函数的视图不能完全正确地转换为对基本表的查询，这类查询应当直接对基本表进行。,三、更新视图,(1) 用户角度：更新视图与更新基本表相同 (2) DBMS实现视图更新的方法 视图实体化法（View Materialization） 视图消解法（View Resolution） (3) 指定WITH CHECK OPTION子句后，DBMS在更新视图时会进行检查，防止用户通过视图对不属于视图范围内的基本表数据进行更新。,三、更新视图,例1 将信息系学生视图IS_Student中学号95002的学生姓名改为“刘辰”。 UPDATE IS_Student SET Sname= '刘辰' WHERE Sno= '95002'； 转换后的语句： UPDATE Student SET Sname= '刘辰' WHERE Sno= '95002' AND Sdept= 'IS'；,三、更新视图,例2 向信息系学生视图IS_S中插入一个新的学生记录：( 95029，赵新，20岁) 。 INSERT INTO IS_Student VALUES( '95029', '赵新', 20)； 转换为对基本表的更新： INSERT INTO Student(Sno, Sname, Sage, Sdept) VALUES('95029'，'赵新'，20，'IS' )； 例3 删除视图CS_S中学号为95029的记录。 DELETE FROM IS_Student WHERE Sno= '95029'； 转换为对基本表的更新： DELETE FROM Student WHERE Sno= '95029' AND Sdept= 'IS'；,三、更新视图,一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新(对两类方法均如此)。 例：视图S_G的定义如下： CREATE VIEW S_G (Sno, Gavg) AS SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno ; 对于如下更新语句： UPDATE S_G SET Gavg=90 WHERE Sno= '95001'； 注：无论实体化法还是消解法都无法将其转换成对基本表SC的更新。,三、更新视图,实际系统对视图更新的限制 从理论上讲，对其更新能够唯一转换为对应基本表更新的视图是可更新的。 实际使用中的商品化RDBMS系统只允许对可更新视图的更新操作。 通常都只允许对行列子集视图进行更新。,四、视图的作用,(1) 视图能够简化用户的操作 (2) 视图使用户能以多种角度看待同一数据 (3) 视图对重构数据库提供了一定程度的逻辑独立性 (4) 视图能够对机密数据提供安全保护,(3) 视图对重构数据库提供了一定程度的逻辑独立性 例：现有学生关系Student(Sno, Sname, Ssex, Sage, Sdept) ，并在此关系上开发了应用程序，当对学生关系进行如下重构(改变关系的结构)，将其“垂直”地分成两个基本表： SX(Sno, Sname, Sage) SY(Sno, Ssex, Sdept) 这时原关系Student已经不存在，应用程序也就无法使用。 可以通过建立以下视图： CREATE VIEW Student(Sno, Sname, Ssex, Sage, Sdept) AS SELECT SX.Sno, SX.Sname, SY.Ssex, SX.Sage, SY.Sdept FROM SX, SY WHERE SX.Sno = SY.Sno ; 使用户的外模式保持不变，从而对原Student表的查询程序不必修改，但更新程序必须修改。(即一定程度),第三章 关系数据库标准语言SQL,3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 嵌入式SQL,3.6 数据控制,数据控制是指DBMS用来保证数据库正确稳定运行所提供的对数据的管理机制。SQL中的数据控制包括事务管理和数据保护，即数据库的恢复、并发控制、数据库的安全性和完整性控制。 数据库的完整性控制是通过数据库定义过程来指定，在数据更新过程中由系统来进行检查并予以保证；事务处理、数据库的恢复、并发控制会在以后的章节专门讲解；本节只讲述SQL中的安全性控制。 数据库的完整性是用来防止合法的用户对数据库的无意破坏，即防止允许更新的用户不小心对数据的错误更新而破坏数据库；数据库的安全性是用来防止非法用户有意窃取或恶意破坏数据库中的数据。 数据库的安全性控制通过授权机制来实现，即通过赋予用户对数据库的使用权限来保证数据的安全。,3.6 数据控制,授权将数据库中的某些对象的某些操作权限赋予某些用户 授权使用GRANT语句： GRANT ,. ON TO ,. WITH GRANT OPTION; DBA拥有数据库操作的所有权限，他可以将权限赋予其他用户。 建立数据库对象的用户称为该对象的属主(OWNER)，他拥有该对象的所有操作权限。 接受权限的用户可以是一个或多个具体用户，也可以是全体用户(PUBLIC)。 指定了WITH GRANT OPTION子句,获得某种权限的用户还可以把这种权限再授予别的用户；没有指定WITH GRANT OPTION子句，获得某种权限的用户只能使用该权限，不能传播该权限。,3.6 数据控制,权限一览表：,例1 把查询Student表权限授给用户U1。 GRANT SELECT ON TABLE Student TO U1;,3.6 数据控制,例2 把对Student表和Course表的全部权限授予用户U2和U3。 GRANT ALL PRIVILIGES ON TABLE Student, Course TO U2, U3; 例3 把对表SC的查询权限授予所有用户 GRANT SELECT ON TABLE SC TO PUBLIC; 例4 把查询Student表和修改学生学号的权限授给用户U4。 GRANT UPDATE(Sno), SELECT ON TABLE Student TO U4;,3.6 数据控制,例5 把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户。 GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION; 执行例5后，U5不仅拥有了对表SC的INSERT权限，还可以传播此权限： GRANT INSERT ON TABLE SC TO U6 WITH GRANT OPTION; 同样，U6还可以将此权限授予U7： GRANT INSERT ON TABLE SC TO U7; 但U7不能再传播此权限。 U5 U6 U7,3.6 数据控制,例6 DBA把在数据库S_C中建立表的权限授予用户U8。 GRANT CREATETAB ON DATABASE S_C TO U8; 收回权限从指定用户那里收回对指定对象的指定权限。 使用REVOKE语句： REVOKE ,. ON FROM ,.; 例7 把用户U4修改学生学号的权限收回。 REVOKE UPDATE(Sno) ON TABLE Student FROM U4；,3.6 数据控制,例8 收回所有用户对表SC的查询权限。 REVOKE SELECT ON TABLE SC FROM PUBLIC; 例9 把用户U5对SC表的INSERT权限收回 REVOKE INSERT ON TABLE SC FROM U5; 系统将收回直接或间接从U5处获得的对SC表的INSERT权限: U5 U6 U7 收回U5、U6、U7获得的对SC表的INSERT权限: -U5- U6- U7,3.6 数据控制,DBA拥有对数据库中所有对象的所有权限，并可以根据应用的需要将不同的权限授予不同的用户。 用户对自己建立的基本表和视图拥有全部的操作权限，并且可以用GRANT语句把其中某些权限授予其他用户。 被授权的用户如果有“继续授权”的许可，还可以把获得的权限再授予其他用户。 所有授予出去的权力在必要时又都可以用REVOKE语句收回。,第三章 关系数据库标准语言SQL,3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 嵌入式SQL,3.7 嵌入式SQL,什么是嵌入式SQL (Embedded SQL)? SQL语句嵌入到其他高级语言中，这时高级语言被称为(宿)主语言。 为什么要引入嵌入式SQL？ (1) SQL语言是非过程性语言 (2) 事务处理应用需要高级语言 这两种方式细节上有差别，在程序设计的环境下，SQL语句要做某些必要的扩充 如何区分主语言与SQL语句？ 前缀：EXEC SQL 结束标志：随主语言的不同而不同 如：C语言中使用SQL：EXEC SQL ; 例：EXEC SQL DROP TABLE Student ;,3.7 嵌入式SQL,宿主语言 +嵌入式SQL,纯宿主语言,可执行代码,预编译,宿主语言编译器,3.7 嵌入式SQL,嵌入SQL语句的分类： 说明性语句 嵌入SQL语句 数据定义 可执行语句 数据控制 数据操纵,允许出现说明语句的地方，都可以写说明性SQL语句。 允许出现可执行的高级语言语句的地方，都可以写可执行SQL语句。 将SQL嵌入到高级语言中混合编程，程序中会含有两种不同计算模型的语句：SQL语句是描述性的面向集合的语句，负责操纵数据库；高级语言语句是过程性的面向记录的语句， 负责控制程序流程。,3.7 嵌入式SQL,SQL与主语言如何交互？ 1. SQL通信区(SQLCA, SQL Communication Area) 1）向主语言传递SQL语句的执行状态信息 2）主语言能够据此控制程序流程 2. 主变量 1）主语言向SQL语句提供参数 2）将SQL语句查询数据库的结果交主语言进一步处理 3. 游标 SQL语言是面向集合的，一条SQL语句可产生或处理多条记录。而主语言是面向记录的，一组主变量一次只能放一条记录，所以，引入游标，通过移动游标指针来决定获取哪一条记录。,例：带有嵌入式SQL的一小段C程序:查询并打印学生成绩。 /* C语言的头文件、变量声明等 */ EXEC SQL INCLUDE SQLCA; /* (1) 定义SQL通信区 */ EXEC SQL BEGIN DECLARE SECTION; /* (2) 说明主变量 */ CHAR Sno(5); CHAR Cno(3); INT grade; EXEC SQL END DECLARE SECTION; main() EXEC SQL DECLARE C1 CURSOR FOR /* (3) 游标操作(定义游标)*/ SELECT Sno, Cno, grade FROM SC; /* 从SC表查询Sno,Cno,Grade */ EXEC SQL OPEN C1; /* (4) 游标操作（打开游标）*/ for(;) EXEC SQL FETCH C1 INTO :Sno, :Cno, :Grade; /* (5) 游标操作（将当前数据放入主变量并推进游标指针）*/ if (sqlca.sqlcode SUCCESS) /* (6) 利用SQLCA中的状态信息决定何时退出循环 */ break; printf(“ Sno: %s, Cno: %s, Grade:%d“, Sno, Cno, Grade); /*打印查询结果*/ EXEC SQL CLOSE C1; /* (7) 游标操作（关闭游标）*/ ,3.7 嵌入式SQL,使用游标的步骤 1. 说明游标 EXEC SQL DECLARE CURSOR FOR ; 2. 打开游标 EXEC SQL OPEN ; 3. 移动游标指针，然后取当前记录 EXEC SQL FETCH NEXT|PRIOR| FIRST|LAST FROM INTO ,.; 4. 关闭游标 EXEC SQL CLOSE ;,