3-3关系数据库标准语言SQL.ppt

《3-3关系数据库标准语言SQL.ppt》由会员分享，可在线阅读，更多相关《3-3关系数据库标准语言SQL.ppt（40页珍藏版）》请在三一文库上搜索。

1、数据库系统概论 An Introduction to Database System 第三章 关系数据库标准语言SQL (续2）,第三章 关系数据库标准语言SQL,3.1 SQL概述 3.2 学生-课程数据库 3.3 数据定义 3.4 数据查询 3.5 数据更新 3.6 视图 3.7 小结,3.5 数 据 更 新,3.5.1 插入数据 3.5.2 修改数据 3.5.3 删除数据,3.5.1 插入数据,一、插入数据记录 插入数据是把新的记录插入到一个存在的表中。插入数据使用语句INSERT INTO，可分为以下几种情况。 1 插入一行新记录 语法格式为： INSERT INTO (,) VALU

2、ES() 列名的排列顺序不一定要和表定义时的顺序一致。 但当指定列名和VALUES子句值的排列顺序必须做到顺序一致，个数相等，数据类型一一对应。 必须用逗号将各个数据分开，字符型数据要用单引号括起来。 INTO子句中没有指定列名，则新插入的记录必须在每个属性列上均有值，且VALUES子句中值的排列顺序要和表中各属性列的排列顺序一致。,插入元组（续）,例1 将一个新学生元组插入到Student表中。 INSERT INTO Student (Sno，Sname，Ssex，Sdept，Sage) VALUES (200215128，陈冬，男，IS，18)； 例2 将学生张成民的信息插入到Stude

3、nt表中。 INSERT INTO Student VALUES (200215126， 张成民， 男，18，CS);,插入元组（续）,2 插入一行的部分数据值 例3 插入一条选课记录( 200215128，1 )。 INSERT INTO SC(Sno，Cno) VALUES ( 200215128 ， 1 )； RDBMS将在新插入记录的Grade列上自动地赋空值。 或者： INSERT INTO SC VALUES ( 200215128 ， 1 ，NULL)； 将VALUES子句中的值按照INTO子句中指定列名的顺序插入到表中 对于INTO子句中没有出现的列，则新插入的记录在这些列上将

4、取空值，如上例的SCORE即赋空值。 但在表定义时有NOT NULL约束的属性列不能取空值。,二、插入子查询结果,3 插入多行记录 用于表间的拷贝，将一个表中的数据抽取数行插入另一表中，可以通过子查询来实现。 插入数据的命令语法格式为： INSERT INTO (,) 子查询 说明：SELECT子句目标列必须与INTO子句匹配 值的个数 值的类型,插入子查询结果（续）,例4 对每一个系，求学生的平均年龄，并把结果存入数据库。 第一步：建表 CREATE TABLE Dept_age (Sdept CHAR(15) /* 系名*/ Avg_age SMALLINT)； /*学生平均年龄*/ 第二

5、步：插入数据 INSERT INTO Dept_age(Sdept，Avg_age) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；,3.5 数 据 更 新,3.5.1 插入数据 3.5.2 修改数据 3.5.3 删除数据,3.4.2 修改数据,语句格式 UPDATE SET =，= WHERE ； 功能 修改指定表中满足WHERE子句条件的元组 其中： 是指要修改的表 SET子句给出要修改的列及其修改后的值 WHERE子句指定待修改的记录应当满足的条件，WHERE子句省略时，则修改表中的所有记录。,修改数据（续）,三种修改方式 1. 修

6、改某一个元组的值 2. 修改多个元组的值 3. 带子查询的修改语句,1. 修改某一个元组的值,例5 将学生200215121的年龄改为22岁 UPDATE Student SET Sage=22 WHERE Sno= 200215121 ；,2. 修改多个元组的值,例6 将所有学生的年龄增加1岁 UPDATE Student SET Sage= Sage+1；,3. 带子查询的修改语句,例7 将计算机科学系全体学生的成绩置零。 UPDATE SC SET Grade=0 WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno = SC.Sn

7、o)；,3.5 数 据 更 新,3.5.1 插入数据 3.5.2 修改数据 3.5.3 删除数据,3.5.3 删除数据,语句格式 DELETE FROM WHERE ； 功能 删除指定表中满足WHERE子句条件的元组 WHERE子句 指定要删除的元组 缺省表示要删除表中的全部元组，表的定义不会删除,删除数据（续）,三种删除方式 1.删除某一个元组的值 例8 删除学号为200215128的学生记录。 DELETE FROM Student WHERE Sno= 200215128 2. 删除多个元组的值 例9 删除所有的学生选课记录。 DELETE FROM SC； 3. 带子查询的删除语句 例

8、10 删除计算机科学系所有学生的选课记录。 DELETE FROM SC WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno)；,第三章 关系数据库标准语言SQL,3.1 SQL概述 3.2 学生-课程数据库 3.3 数据定义 3.4 数据查询 3.5 数据更新 3.6 视图 3.7 小结,3.6 视 图,视图的特点 虚表，是从一个或几个基本表（或视图）导出的表 只存放视图的定义，不存放视图对应的数据 基表中的数据发生变化，从视图中查询出的数据也随之改变 基于视图的操作 查询 删除 受限更新 定义基于该视图的新视图,3.6

9、 视 图,3.6.1 定义视图 3.6.2 查询视图 3.6.3 更新视图 3.6.4 视图的作用,一、建立视图,语句格式 CREATE VIEW AS WITH CHECK OPTION； 其中： 1.为可选项，省略时，视图的列名由子查询的结果决定。 2.以下两种情况下，视图列名不可省略： 视图由多个表连接得到，在不同的表中存在同名列，则需指定列名； 当视图的列名为表达式或库函数的计算结果时，而不是单纯的属性名时，则需指明列名。 3. WITH CHECK OPTION表示对视图进行UPDATA、INSERT、DELETE操作时必须满足子查询中的条件表达式。 在子查询中不许使用ORDER B

10、Y 子句和DISTINCT短语，如果需要排序，则可在视图定义后，对视图查询时再进行排序。,建立视图（续）,例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； 对IS_St

11、udent视图的更新操作： 修改操作：自动加上Sdept= IS的条件 删除操作：自动加上Sdept= IS的条件 插入操作：自动检查Sdept属性值是否为IS 如果不是，则拒绝该插入操作 如果没有提供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=

12、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 将学生的学号及他的平均成绩定义为一个视图 假设SC表中“成绩”列Grade为数字型 CREAT

13、 VIEW S_G(Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno；,建立视图（续）,不指定属性列 例7将Student表中所有女生记录定义为一个视图 CREATE VIEW F_Student(F_Sno，name，sex，age，dept) AS SELECT * FROM Student WHERE Ssex=女； 缺点： 修改基表Student的结构后，Student表与F_Student视图的映象关系被破坏，导致该视图不能正确工作。,二、删除视图,语句的格式： DROP VIEW ； 该语句从数据字典中删除指定的视图定义

14、 如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除 删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除,3.6 视 图,3.6.1 定义视图 3.6.2 查询视图 3.6.3 更新视图 3.6.4 视图的作用,3.6.2 查询视图,用户角度：查询视图与查询基本表相同 RDBMS实现视图查询的方法 视图消解: 首先进行有效性检查，检查查询中涉及的表或试图是否存在 然后，将试图定义中的子查询和用户查询结合，转换成等价的对基本表的查询 执行修正后的查询,查询视图（续）,例9 在信息系学生的视图中找出年龄小于20岁的学生。

15、 SELECT Sno，Sage FROM IS_Student WHERE Sage20； 视图消解转换后的查询语句为： SELECT Sno，Sage FROM Student WHERE Sdept= IS AND Sage20；,例1 建立信息系学生的视图。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS；,3.6 视 图,3.6.1 定义视图 3.6.2 查询视图 3.6.3 更新视图 3.6.4 视图的作用,更新视图（续）,例12 将信息系学生视图IS_Student中学号20

16、0215122的学生姓名改为“刘辰”。 UPDATE IS_Student SET Sname= 刘辰 WHERE Sno= 200215122 ； 转换后的语句： UPDATE Student SET Sname= 刘辰 WHERE Sno= 200215122 AND Sdept= IS；,更新视图（续）,例13 向信息系学生视图IS_S中插入一个新的学生记录：200215129，赵新，20岁 INSERT INTO IS_Student VALUES(95029，赵新，20)； 转换为对基本表的更新： INSERT INTO Student(Sno，Sname，Sage，Sdept) V

17、ALUES(200215129 ，赵新，20，IS )；,更新视图（续）,例14删除信息系学生视图IS_Student中学号为200215129的记录 DELETE FROM IS_Student WHERE Sno= 200215129 ； 转换为对基本表的更新： DELETE FROM Student WHERE Sno= 200215129 AND Sdept= IS；,更新视图（续）,更新视图的限制：一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新 例：视图S_G为不可更新视图。 UPDATE S_G SET Gavg=90 WHERE Sno= 2

18、00215121； 这个对视图的更新无法转换成对基本表SC的更新,更新视图（续）,一般只允许对行列子集视图进行更新 行列子集视图：在基本表的基础上采用筛选和投影运算得到的并且包含了基本表的主键的视图 一般不允许对下列视图进行更新： 视图来源于多个基本表 不包含基本表主键的视图 使用聚合函数得到的视图。 允许更新的视图在定义时要加上“WITH CHECK OPTION”,3.6 视 图,3.6.1 定义视图 3.6.2 查询视图 3.6.3 更新视图 3.6.4 视图的作用,3.6.4 视图的作用,1. 视图能够简化用户的操作 2. 视图使用户能以多种角度看待同一数据 3. 视图对重构数据库提供了一定程度的逻辑独立性 4. 视图能够对机密数据提供安全保护 5. 适当的利用视图可以更清晰的表达查询,