数据库原理第三部分关系数据库标准语言SQL.ppt
数据库原理 第三章:关系数据库标准语言SQL,授课教师:王哲,复习上节课内容,连接查询语法结构(多表查询)使用表的别名表自身连接外连接嵌套查询集合查询,连接查询,一般格式:select all|distinct,from,where 一般表示为:.,连接查询等值和非等值连接使用表的别名自身连接以JOIN关键字指定的外连接,嵌套查询,T-SQL允许SELECT多层嵌套使用,即一个子查询中还可以嵌套子子查询,用来表示复杂的查询,从而增强SQL的查询能力。子查询通常与IN、比较运算符及EXISTS谓词结合使用。带有In谓词的子查询带有比较运算符的子查询带有EXISTS谓词的子查询,集合查询,集合操作的种类并操作 UNION交操作 INTERSECT差操作 EXCEPT参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同。,问题:,SELECT student.sno,snameFROM Student,scWHERE cno=2 and Student.Sno=SC.Sno,1、查询选修课程号为2的学号、学生姓名;SELECT sno,sname FROM Student,sc WHERE cno=2,注意:加上表名前缀是为了避免重复字段混淆;应将两个表中同一学生的元组连起来;,本节课内容,认识视图视图的相关操作创建视图查询更新修改删除,一、视图,创建视图(使用界面及T-SQL语言)查询视图更新视图修改视图删除视图,1、认识视图,视图是一种数据库对象,是从一个或者多个数据表或视图中导出的虚表,视图的结构和数据是对数据表进行查询的结果;只存放视图的定义,不存放视图对应的数据;基表中的数据发生变化,从视图中查询出的数据也随之改变。,使用视图的注意事项,只能在当前数据库中创建视图;视图的命名必须遵循标识符命名规则,不可与表同名;如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同,则必须为列定义名称。不能在临时表上创建视图也不能创建临时视图。定义视图时的查询语句通常不允许含有order by子句或是into等关健字。,2、使用T-SQL语句创建视图,用CREATE VIEW语句创建视图,其表示形式为:CREATE VIEW 视图名(列名1,列名2,n)AS WITH CHECK OPTION,查询语句:用来创建视图的SELECT语句。但对SELECT语句有以下的限制:定义视图的用户必须对所参照的表或视图有查询权限,即可执行SELECT语句。不能使用COMPUTE或COMPUTE BY子句。不能使用ORDER BY子句。不能使用INTO子句。不能在临时表或表变量上创建视图。WITH CHECK OPTION:指出在视图上所进行的修改都要符合查询语句所指定的限制条件,这样可以确保数据修改后仍可通过视图看到修改的数据。,例2:创建计算机系学生基本信息视图stu_cs;,例1:创建所有学生学号、姓名及年龄的信息视图stu_info;,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,sage 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 OPTION,With check option,对Stu_IS及stu_age视图的更新操作:修改操作:自动加上Sdept=IS的条件删除操作:自动加上Sdept=IS的条件插入操作:自动检查Sdept属性值是否为IS 如果不是,则拒绝该插入操作如果没有提供Sdept属性值,则自动定义Sdept为IS,2)创建基于多个基表的视图,例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.Sno,CREATE 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=o,3)创建基于视图的视图,例1:建立信息系选修了1号课程且成绩在90分以上的学生的视图;CREATE VIEW stu_is_grade AS SELECT Sno,Sname,Grade FROM stu_is WHERE Grade=90;,4)创建带表达式的视图,例1:定义一个反映学生出生年份的视图;CREATE VIEW stu_year(Sno,Sname,Sbirth)AS SELECT Sno,Sname,出生年份=2007-Sage FROM Student,5)创建分组视图,例1:将学生的学号及其平均成绩定义为一个视图;CREATE VIEW S_G(Sno,Gavg)AS SELECT Sno,AVG(Grade)FROM SC GROUP BY Sno,3、查询视图,视图定义后,就可以像查询基本表那样对视图进行查询。如果与视图相关联的表或视图被删除,则该视图将不能再使用。使用视图查询时,若其关联的基本表中添加了新字段,则必须重新创建或者修改视图才能查询到新字段。查看视图的T-SQL定义:sp_helptext实现视图查询的方法视图消解法(View Resolution)进行有效性检查,转换成等价的对基本表的查询执行修正后的查询,例1:查询计算机系年龄大于20的学生;,SELECT*From stu_csWHERE sage=20,视图消解转换后的查询语句为:SELECT Sno,Sname,Sage FROM Student WHERE Sdept=CS AND Sage=20,例2:查询选修了1号课程的信息系学生;SELECT*FROM stu_is_c1,视图消解转换后的查询语句为:,SELECT Student.Sno,Sname FROM Student,SC WHERE Student.Sno=SC.Sno AND SC.Cno=1 AND sdept=CS,4、更新视图,更新视图是指通过视图来插入(Insert)、修改(update)和删除(delete)数据;由于视图是虚表,因此对视图的更新最终要转换为对基本表的更新;为了防止用户对不属于视图范围内的基本表数据进行操作,可在定义视图时加上 with check option子句。,在关系数据库中,并不是所有的视图都是可更新的,因为有些视图的更新并不能有意义地转换成相应表的查询。所以要通过视图更新表数据,必须保证视图是可更新视图。对视图进行更新操作时,还要注意基本表对数据的各种约束和规则要求。,可更新视图的条件,创建视图的select 语句中没有聚合函数,且没有top、group by、having及distinct 关键字;创建视图的select 语句的各列必须来自于基表(视图)的列,不能是表达式;视图定义必须是一个简单的SELECT语句,不能带连接、集合操作。即SELECT语句的FROM子句中不能出现多个表,也不能有 JOIN、EXCEPT、UNION、INTERSECT;,对于视图:stu_info、stu_cs、stu_is、stu_age、stu_is_c1、stu_sc、stu_is_grade、stu_year、S_G、stu_nv 中完全不可更新视图有:stu_sc、stu_year、S_G,1)在视图中插入数据,使用insert 语句通过视图向基本表插入数据。由于视图不一定包括表中的所有字段,所以在插入记录时可能会遇到问题。视图中那些没有出现的字段无法显式插入数据,假如这些字段不接受系统指派的null值,那么插入操作将失败。,例1:向视图stu_info中插入一个新的学生记录,学号为200515006,姓名为王无,年龄为20;Insert into stu_infoValues(200515006,王无,20),等价于: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中插入一个新的学生记录,学号为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),系统将发出错误信息:“视图或函数e_view 不可更新,因为它包含聚合函数。,2)通过视图更新数据,使用UPDATE语句可以通过视图修改基本表的数据。例1:将视图stu_info中学号为“200515001”的学生姓名改为“张山”update stu_infoset sname=张山where sno=200515001等价于:update studentset sname=张山where sno=200515001,例2:将视图stu_is中学号为“200515004”的学生姓名改为“张珊”update stu_isset sname=张珊where sno=200515004等价于:update studentset sname=张珊where sno=200515004 and sdept=IS and ssex=男,若更新视图时只影响其中一个表,同时新数据值中含有主键字,系统将接受这个修改操作。例3:将视图stu_is_c1中学号为“200515006”的学生成绩改为75;Update stu_is_c1 Set grade=75Where sno=200515006 等价于:Update scSet grade=75Where sno=200515006 and cno=1,3)通过视图删除数据,使用DELETE语句可以通过视图删除基本表的数据。但对于依赖于多个基本表的视图,不能使用DELETE语句。例1:删除视图stu_is中学号为“200515020”的学生记录DELETEFROM stu_isWHERE Sno=200215020 等价于:DELETEFROM StudentWHERE Sno=200215020 AND Sdept=IS AND ssex=男,5、修改视图,格式: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=IS,6、删除视图,删除视图的T-SQL语句是DROP VIEW,格式为:DROP VIEW view,n 例1:同时删除视图stu_is和s_g。drop view stu_is,s_g,视图的优点(P125):,1.视图能够简化用户的操作;2.视图使用户能以多种角度看待同一数据;3.视图对重构数据库提供了一定程度的逻辑独立性;4.视图能够对机密数据提供安全保护;5.适当的利用视图可以更清晰的表达查询。,总结,本次课学习内容管理视图创建、查询、更新、编辑及删除,