数据库原理第三部分关系数据库标准语言SQL.ppt

《数据库原理第三部分关系数据库标准语言SQL.ppt》由会员分享，可在线阅读，更多相关《数据库原理第三部分关系数据库标准语言SQL.ppt（43页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库原理第三部分关系数据库标准语言SQL Still waters run deep.流静水深流静水深,人静心深人静心深 Where there is life,there is hope。有生命必有希望。有生命必有希望复习上节课内容复习上节课内容v连接查询语法结构（多表查询）使用表的别名表自身连接外连接v嵌套查询v集合查询连接查询连接查询v一般格式：select all|distinct,from,where v一般表示为：.v连接查询等值和非等值连接使用表的别名自身连接以JOIN关键字指定的外连接嵌套查询嵌套查询vT-SQL允许SELECT多层嵌套使用，即一个子查询中还可以嵌套子子查询，

2、用来表示复杂的查询，从而增强SQL的查询能力。v子查询通常与IN、比较运算符比较运算符及及EXISTS谓词结合使用。谓词结合使用。带有In谓词的子查询带有比较运算符的子查询带有EXISTS谓词的子查询集合查询集合查询v集合操作的种类并操作 UNION交操作 INTERSECT差操作 EXCEPTv参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。问题：问题：SELECT student.sno,snameFROM Student,scWHERE cno=2 and Student.Sno=SC.Sno1 1、查询选修课程号为、查询选修课程号为2 2的学号、学生姓名；的学号、学

3、生姓名；SELECT sno,sname FROM Student,sc WHERE cno=2注意：注意：加上表名前缀是为了避免重复字段混淆；加上表名前缀是为了避免重复字段混淆；应将两个表中同一学生的元组连起来；应将两个表中同一学生的元组连起来；本节课内容本节课内容v认识视图v视图的相关操作创建视图查询更新修改删除一、视图一、视图v创建视图（使用界面及T-SQL语言）v查询视图v更新视图v修改视图v删除视图1、认识视图、认识视图v视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表虚表，视图的结构和数据是对数据表进行查询的结果；v只存放视图的定义，不存放视图对应的数据；v基表中的数

4、据发生变化，从视图中查询出的数据也随之改变。使用视图的注意事项v只能在当前数据库中创建视图；v视图的命名必须遵循标识符命名规则，不可与表同名；v如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义名称。v不能在临时表上创建视图也不能创建临时视图。v定义视图时的查询语句通常不允许含有order by子句或是into等关健字。2、使用、使用T-SQL语句创建视图语句创建视图用CREATE VIEW语句创建视图，其表示形式为：CREATE VIEW 视图名(列名1,列名2,n)AS WITH CHECK OPTIONv查询语句查询语句：用来创建视图的SELECT语句。但对

5、SELECT语句有以下的限制：定义视图的用户必须对所参照的表或视图有查询权限，即可执行SELECT语句。不能使用COMPUTE或COMPUTE BY子句。不能使用ORDER BY子句。不能使用INTO子句。不能在临时表或表变量上创建视图。vWITH CHECK OPTION：指出在视图上所进行的修改都要符合查询语句所指定的限制条件，这样可以确保数据修改后仍可通过视图看到修改的数据。例例2 2：创建计算机系学生基本信息视图创建计算机系学生基本信息视图stu_csstu_cs；例例1 1：创建所有学生学号、姓名及年龄的信息视图：创建所有学生学号、姓名及年龄的信息视图stu_infostu_info

6、；CREATE VIEW stu_csAS SELECT sno,sname,sage,ssex from studentWhere sdept=CS CREATE VIEW stu_infoAS SELECT sno,sname,sage From student 例例3：创建信息系男学生基本信息视图：创建信息系男学生基本信息视图stu_is，包括学生的学号、姓名及年龄，包括学生的学号、姓名及年龄,并要求进行修改和插入操作时仍需保证该视图只有信息系的学生并要求进行修改和插入操作时仍需保证该视图只有信息系的学生;CREATE VIEW stu_isAS SELECT sno,sname,sag

7、e from studentWhere sdept=IS and ssex=男WITH CHECK OPTION 例例4：创建创建年龄大于年龄大于20的各学生的各学生的的学号学号、姓名及、姓名及年龄的视图年龄的视图stu_age，并，并保证保证对视图文本对视图文本的的修改修改都要符合年龄大于都要符合年龄大于20这个条件这个条件。CREATE VIEW stu_ageAS SELECT sno,sname,sage from student where sage20WITH CHECK OPTIONWith check option对Stu_IS及stu_age视图的更新操作：v修改操作：自动加

8、上Sdept=IS的条件v删除操作：自动加上Sdept=IS的条件v插入操作：自动检查Sdept属性值是否为IS 如果不是，则拒绝该插入操作如果没有提供Sdept属性值，则自动定义Sdept为IS2）创建基于多个基表的视图）创建基于多个基表的视图例例1:1:建立信息系选修了建立信息系选修了1 1号课程的学生视图号课程的学生视图stu_is_c1;CREATE VIEW stu_is_c1AS SELECT Student.Sno,Sname,GradeFROM Student,SCWHERE Sdept=IS AND SC.Cno=1 AND Student.Sno=SC.SnoCREATE

9、VIEW stu_is_c1(学号,姓名,成绩)AS SELECT Student.Sno,Sname,GradeFROM Student,SCWHERE Sdept=IS AND Student.Sno=SC.Sno AND SC.Cno=1例例2：创建学生选修课程详细情况视图：创建学生选修课程详细情况视图stu_sc；CREATE VIEW stu_scAS SELECT s.sno,sname,ssex,sage，sdept，o，cname，gradeFrom student s，sc，course cWhere s.sno=sc.sno and o=o3）创建基于视图的视图）创建基于视

10、图的视图例例1:1:建立信息系选修了建立信息系选修了1 1号课程且成绩在号课程且成绩在9090分以上的学生的视图分以上的学生的视图;CREATE VIEW stu_is_grade AS SELECT Sno，Sname，Grade FROM stu_is WHERE Grade=90；4）创建带表达式的视图）创建带表达式的视图例例1:1:定义一个反映学生出生年份的视图定义一个反映学生出生年份的视图;CREATE VIEW stu_year(Sno，Sname，Sbirth)AS SELECT Sno，Sname，出生年份=2007-Sage FROM Student5 5）创建分组视图）创建

11、分组视图例例1:1:将学生的学号及其平均成绩定义为一个视图将学生的学号及其平均成绩定义为一个视图;CREATE VIEW S_G(Sno，Gavg)AS SELECT Sno，AVG(Grade)FROM SC GROUP BY Sno3 3、查询视图、查询视图v视图定义后，就可以像查询基本表那样对视图进行查询。v如果与视图相关联的表或视图被删除，则该视图将不能再使用。v使用视图查询时，若其关联的基本表中添加了新字段，则必须重新创建或者修改视图才能查询到新字段。v查看视图的T-SQL定义：sp_helptextv实现视图查询的方法视图消解法（View Resolution）进行有效性检查，转换

12、成等价的对基本表的查询执行修正后的查询例例1 1：查询计算机系年龄大于：查询计算机系年龄大于2020的学生；的学生；SELECT*From stu_csWHERE sage=20视图消解转换后的查询语句为：视图消解转换后的查询语句为：SELECT Sno，Sname，Sage FROM Student WHERE Sdept=CS AND Sage=20例例2 2：查询选修了：查询选修了1 1号课程的信息系学生；号课程的信息系学生；SELECT *FROM stu_is_c1视图消解转换后的查询语句为：视图消解转换后的查询语句为：SELECT Student.Sno，Sname FROM St

13、udent，SC WHERE Student.Sno=SC.Sno AND SC.Cno=1 AND sdept=CS4 4、更新视图、更新视图v更新视图是指通过视图来插入（Insert）、修改（update）和删除（delete）数据；v由于视图是虚表，因此对视图的更新最终要转换为对基本表的更新；v为了防止用户对不属于视图范围内的基本表数据进行操作，可在定义视图时加上 with check option子句。v在关系数据库中，并不是所有的视图都是可更新的，因为有些视图的更新并不能有意义地转换成相应表的查询。v所以要通过视图更新表数据，必须保证视图是可更新视图。v对视图进行更新操作时，还要注意

14、基本表对数据的各种约束和规则要求。可更新视图的条件可更新视图的条件v创建视图的select 语句中没有聚合函数，且没有top、group by、having及distinct 关键字；v创建视图的select 语句的各列必须来自于基表（视图）的列，不能是表达式；v视图定义必须是一个简单的SELECT语句，不能带连接、集合操作。即SELECT语句的FROM子句中不能出现多个表，也不能有 JOIN、EXCEPT、UNION、INTERSECT；v对于视图：stu_info、stu_csstu_cs、stu_is、stu_age、stu_is_c1、stu_sc、stu_is_grade、stu_y

15、ear、S_G、stu_nv 中v完全不可更新视图有：stu_sc、stu_year、S_G1）在视图中插入数据）在视图中插入数据v使用insert 语句通过视图向基本表插入数据。v由于视图不一定包括表中的所有字段，所以在插入记录时可能会遇到问题。v视图中那些没有出现的字段无法显式插入数据，假如这些字段不接受系统指派的null值，那么插入操作将失败。例例1:向视图向视图stu_info中插入一个新的学生记录，学号为中插入一个新的学生记录，学号为200515006，姓名为王无，年龄为，姓名为王无，年龄为20；Insert into stu_infoValues(200515006,王无,20)等

16、价于：Insert into student（sno，sname，sage）Values(200515026,王无,20)例例2:向视图向视图stu_is中插入一个新的学生记录，学号为中插入一个新的学生记录，学号为200515027，姓名为王唔，年龄为，姓名为王唔，年龄为20；Insert into stu_isValues(200515027,王唔,20)等价于：等价于：Insert into student（sno，sname，sage，ssex,sdept）Values(200515026,王无,20,男,IS)例例3:向视图向视图stu_is_c1中插入一个新的学生记录，学号为中插入一

17、个新的学生记录，学号为200515027，姓名为王唔，成绩为，姓名为王唔，成绩为60；Insert into stu_is_c1Values(200515027,王唔,60)系统将发出错误信息：“视图或函数视图或函数stu_is_c1不可更新，不可更新，因为修改会影响多个基表因为修改会影响多个基表”。在表sc中，只有成绩而主键课程号cno不确定，显然不能把数据插入sc表中。例例4:向视图向视图S_G中插入一个新的学生记录，学号为中插入一个新的学生记录，学号为200515027，平均成绩为，平均成绩为60；Insert into S_G Values(200515027,60)系统将发出错误信息

18、：“视图或函数视图或函数e_view 不可更新不可更新，因为它包含聚合函数。2）通过视图更新数据）通过视图更新数据v使用UPDATE语句可以通过视图修改基本表的数据。例例1 1：将视图：将视图stu_infostu_info中学号为中学号为“200515001”200515001”的学生姓名改为的学生姓名改为“张山张山”update stu_infoset sname=张山where sno=200515001等价于：等价于：update studentupdate studentset sname=set sname=张山张山 where sno=200515001where sno=200

19、515001例例2 2：将视图：将视图stu_isstu_is中学号为中学号为“200515004”200515004”的学生姓名改为的学生姓名改为“张张珊珊”update stu_isset sname=张珊where sno=200515004等价于：等价于：update studentset sname=张珊where sno=200515004 and sdept=IS and ssex=男v若更新视图时只影响其中一个表，同时新数据值中含有主键字，若更新视图时只影响其中一个表，同时新数据值中含有主键字，系统将接受这个修改操作。系统将接受这个修改操作。例例3：将视图将视图stu_is_c

20、1中学号为中学号为“200515006”200515006”的学生成绩改为的学生成绩改为7575；Update stu_is_c1 Set grade=75Where sno=200515006 等价于：等价于：Update scSet grade=75Where sno=200515006 and cno=13）通过视图删除数据）通过视图删除数据v使用DELETE语句可以通过视图删除基本表的数据。但对于依赖于多个基本表的视图，不能使用DELETE语句。例例1：删除视图：删除视图stu_is中学号为中学号为“200515020”的学生记录的学生记录DELETEFROM stu_isWHERE

21、Sno=200215020 等价于：等价于：DELETEFROM StudentWHERE Sno=200215020 AND Sdept=IS AND ssex=男5、修改视图、修改视图v格式：ALTER VIEW view_name(column,.n)ASselect_statement WITH CHECK OPTION 例1：将stu_info视图修改为只包含学生学号、姓名。Alter view stu_infoASSelect sno，sname from student例2：将stu_is视图修改为只包含信息系学生学号、姓名及年龄。Alter view stu_isASSelect sno，sname，sage from studentWhere sdept=IS6、删除视图、删除视图v删除视图的T-SQL语句是DROP VIEW，格式为：DROP VIEW view ,n 例1：同时删除视图stu_is和s_g。drop view stu_is,s_g视图的优点（视图的优点（P125）：）：v1.视图能够简化用户的操作；v2.视图使用户能以多种角度看待同一数据；v3.视图对重构数据库提供了一定程度的逻辑独立性；v4.视图能够对机密数据提供安全保护；v5.适当的利用视图可以更清晰的表达查询。总结总结本次课学习内容v管理视图创建、查询、更新、编辑及删除