数据库原理 第三章：关系数据库标准语言SQL,授课教师：王哲,复习上节课内容,连接查询 语法结构（多表查询） 使用表的别名 表自身连接 外连接 嵌套查询 集合查询,连接查询,一般格式： select all|distinct , from , where 一般表示为： . .,连接查询 等值和非等值连接 使用表的别名 自身连接 以JOIN关键字指定的外连接,嵌套查询,T-SQL允许SELECT多层嵌套使用，即一个子查询中还可以嵌套子子查询，用来表示复杂的查询，从而增强SQL的查询能力。 子查询通常与IN 、比较运算符及EXISTS谓词结合使用。 带有In谓词的子查询 带有比较运算符的子查询 带有EXISTS谓词的子查询,集合查询,集合操作的种类 并操作 UNION 交操作 INTERSECT 差操作 EXCEPT 参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。,问题：,SELECT student.sno,sname FROM Student,sc WHERE 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_cs AS SELECT sno,sname,sage,ssex from student Where sdept=CS,CREATE VIEW stu_info AS SELECT sno,sname,sage From student,例3：创建信息系男学生基本信息视图stu_is，包括学生的学号、姓名及年龄,并要求进行修改和插入操作时仍需保证该视图只有信息系的学生;,CREATE VIEW stu_is AS SELECT sno,sname, sage from student Where sdept = IS and ssex=男 WITH CHECK OPTION,例4：创建年龄大于20的各学生的学号、姓名及年龄的视图stu_age，并保证对视图文本的修改都要符合年龄大于20这个条件。,CREATE VIEW stu_age AS SELECT sno,sname,sage from student where sage20 WITH 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_c1 AS SELECT Student. Sno,Sname,Grade FROM Student,SC WHERE Sdept= IS AND SC.Cno= 1 AND Student.Sno=SC.Sno,CREATE VIEW stu_is_c1(学号,姓名,成绩) AS SELECT Student. Sno,Sname,Grade FROM Student,SC WHERE Sdept= IS AND Student.Sno=SC.Sno AND SC.Cno= 1,例2：创建学生选修课程详细情况视图stu_sc；,CREATE VIEW stu_sc AS SELECT s.sno,sname,ssex, sage，sdept，c.cno，cname，grade From student s，sc，course c Where s.sno=sc.sno and c.cno=sc.cno,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_cs WHERE 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_info Values(200515006,王无,20),等价于： Insert into student（sno，sname，sage） Values(200515026,王无,20),例2:向视图stu_is中插入一个新的学生记录，学号为200515027，姓名为王唔，年龄为20； Insert into stu_is Values(200515027,王唔,20) 等价于：,Insert into student（sno，sname，sage，ssex,sdept） Values(200515026,王无,20 ,男 ,IS),例3:向视图stu_is_c1中插入一个新的学生记录，学号为200515027，姓名为王唔，成绩为60； Insert into stu_is_c1 Values(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_info set sname=张山 where sno=200515001 等价于： update student set sname=张山 where sno=200515001,例2：将视图stu_is中学号为“200515004”的学生姓名改为“张珊” update stu_is set sname=张珊 where sno=200515004 等价于： update student set sname=张珊 where sno=20051500