1 / 43

数据库原理 第三章：关系数据库标准语言 SQL

数据库原理 第三章：关系数据库标准语言 SQL. 授课教师：王哲. 复习上节课内容. 连接查询 语法结构（多表查询） 使用表的别名 表自身连接 外连接 嵌套查询 集合查询. 连接查询. 一般格式： select [all|distinct] < 目标列表达式 >[,< 目标列表达式 >]… from < 表名 1>[,< 表名 2>]… [where < 条件表达式 >] < 条件表达式 > 一般表示为： [< 表名 1>.]< 列名 1> < 比较运算符 > [< 表名 2>.]< 列名 2>. 连接查询 等值和非等值连接

seven
Download Presentation

数据库原理 第三章：关系数据库标准语言 SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 数据库原理 第三章：关系数据库标准语言SQL 授课教师：王哲

  2. 复习上节课内容 连接查询 语法结构（多表查询） 使用表的别名 表自身连接 外连接 嵌套查询 集合查询

  3. 连接查询 • 一般格式： select [all|distinct] <目标列表达式>[,<目标列表达式>]… from <表名1>[,<表名2>]… [where <条件表达式>] • <条件表达式>一般表示为： [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

  4. 连接查询 • 等值和非等值连接 • 使用表的别名 • 自身连接 • 以JOIN关键字指定的外连接

  5. 嵌套查询 • T-SQL允许SELECT多层嵌套使用，即一个子查询中还可以嵌套子子查询，用来表示复杂的查询，从而增强SQL的查询能力。 • 子查询通常与IN 、比较运算符及EXISTS谓词结合使用。 • 带有In谓词的子查询 • 带有比较运算符的子查询 • 带有EXISTS谓词的子查询

  6. 集合查询 • 集合操作的种类 • 并操作 UNION • 交操作 INTERSECT • 差操作 EXCEPT • 参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。

  7. 问题： 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' • 注意： • 加上表名前缀是为了避免重复字段混淆； • 应将两个表中同一学生的元组连起来；

  8. 本节课内容 认识视图 视图的相关操作 创建视图 查询 更新 修改 删除

  9. 一、视图 创建视图（使用界面及T-SQL语言） 查询视图 更新视图 修改视图 删除视图

  10. 1、认识视图 视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图的结构和数据是对数据表进行查询的结果； 只存放视图的定义，不存放视图对应的数据； 基表中的数据发生变化，从视图中查询出的数据也随之改变。

  11. 使用视图的注意事项 • 只能在当前数据库中创建视图； • 视图的命名必须遵循标识符命名规则，不可与表同名； • 如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义名称。 • 不能在临时表上创建视图也不能创建临时视图。 • 定义视图时的查询语句通常不允许含有order by子句或是into等关健字。

  12. 2、使用T-SQL语句创建视图 用CREATE VIEW语句创建视图，其表示形式为： CREATE VIEW 视图名[(列名1,列名2[,…n])] AS <查询语句> [WITH CHECK OPTION]

  13. 查询语句：用来创建视图的SELECT语句。但对SELECT语句有以下的限制：① 定义视图的用户必须对所参照的表或视图有查询权限，即可执行SELECT语句。② 不能使用COMPUTE或COMPUTE BY子句。③ 不能使用ORDER BY子句。④ 不能使用INTO子句。⑤ 不能在临时表或表变量上创建视图。 • WITH CHECK OPTION：指出在视图上所进行的修改都要符合查询语句所指定的限制条件，这样可以确保数据修改后仍可通过视图看到修改的数据。

  14. 例1：创建所有学生学号、姓名及年龄的信息视图stu_info；例1：创建所有学生学号、姓名及年龄的信息视图stu_info； CREATE VIEW stu_info AS SELECT sno,sname,sage From student 例2： 创建计算机系学生基本信息视图stu_cs； CREATE VIEW stu_cs AS SELECT sno,sname,sage,ssex from student Where sdept=‘CS’

  15. CREATE VIEW stu_is AS SELECT sno,sname, sage from student Where sdept = ‘IS’ and ssex=‘男’ WITH CHECK OPTION 例3：创建信息系男学生基本信息视图stu_is，包括学生的学号、姓名及年龄,并要求进行修改和插入操作时仍需保证该视图只有信息系的学生; 例4：创建年龄大于20的各学生的学号、姓名及年龄的视图stu_age，并保证对视图文本的修改都要符合年龄大于20这个条件。 CREATE VIEW stu_age AS SELECT sno,sname,sage from student where sage>20 WITH CHECK OPTION

  16. With check option 对Stu_IS及stu_age视图的更新操作： • 修改操作：自动加上Sdept= 'IS'的条件 • 删除操作：自动加上Sdept= 'IS'的条件 • 插入操作：自动检查Sdept属性值是否为'IS' • 如果不是，则拒绝该插入操作 • 如果没有提供Sdept属性值，则自动定义Sdept为'IS’

  17. 2）创建基于多个基表的视图 例1:建立信息系选修了1号课程的学生视图stu_is_c1; CREATE VIEW stu_is_c1 AS SELECT Student. Sno,Sname,Grade FROM Student,SC WHERE Sdept= ‘IS’ ANDSC.Cno= '1' ANDStudent.Sno=SC.Sno

  18. 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'

  19. 例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

  20. 3）创建基于视图的视图 例1:建立信息系选修了1号课程且成绩在90分以上的学生的视图; CREATE VIEW stu_is_grade AS SELECT Sno，Sname，Grade FROM stu_is WHERE Grade>=90；

  21. 4）创建带表达式的视图 例1: 定义一个反映学生出生年份的视图; CREATE VIEW stu_year(Sno，Sname，Sbirth) AS SELECT Sno，Sname，出生年份=2007-Sage FROM Student

  22. 5）创建分组视图 例1:将学生的学号及其平均成绩定义为一个视图; CREATE VIEW S_G(Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno

  23. 3、查询视图 • 视图定义后，就可以像查询基本表那样对视图进行查询。 • 如果与视图相关联的表或视图被删除，则该视图将不能再使用。 • 使用视图查询时，若其关联的基本表中添加了新字段，则必须重新创建或者修改视图才能查询到新字段。 • 查看视图的T-SQL定义：sp_helptext • 实现视图查询的方法——视图消解法（View Resolution） • 进行有效性检查，转换成等价的对基本表的查询 • 执行修正后的查询

  24. 例1：查询计算机系年龄大于20的学生； SELECT * From stu_cs WHERE sage>=20 视图消解转换后的查询语句为： SELECT Sno，Sname，Sage FROM Student WHERE Sdept= ‘CS' AND Sage>=20

  25. 例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’

  26. 4、更新视图 • 更新视图是指通过视图来插入（Insert）、修改（update）和删除（delete）数据； • 由于视图是虚表，因此对视图的更新最终要转换为对基本表的更新； • 为了防止用户对不属于视图范围内的基本表数据进行操作，可在定义视图时加上 with check option子句。

  27. 在关系数据库中，并不是所有的视图都是可更新的，因为有些视图的更新并不能有意义地转换成相应表的查询。在关系数据库中，并不是所有的视图都是可更新的，因为有些视图的更新并不能有意义地转换成相应表的查询。 • 所以要通过视图更新表数据，必须保证视图是可更新视图。 • 对视图进行更新操作时，还要注意基本表对数据的各种约束和规则要求。

  28. 可更新视图的条件 • 创建视图的select 语句中没有聚合函数，且没有top、group by、having及distinct 关键字； • 创建视图的select 语句的各列必须来自于基表（视图）的列，不能是表达式； • 视图定义必须是一个简单的SELECT语句，不能带连接、集合操作。即SELECT语句的FROM子句中不能出现多个表，也不能有 JOIN、EXCEPT、UNION、INTERSECT ；

  29. 对于视图：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

  30. 1）在视图中插入数据 • 使用insert 语句通过视图向基本表插入数据。 • 由于视图不一定包括表中的所有字段，所以在插入记录时可能会遇到问题。 • 视图中那些没有出现的字段无法显式插入数据，假如这些字段不接受系统指派的null值，那么插入操作将失败。

  31. 例1:向视图stu_info中插入一个新的学生记录，学号为200515006，姓名为王无，年龄为20；例1:向视图stu_info中插入一个新的学生记录，学号为200515006，姓名为王无，年龄为20； Insert into stu_info Values('200515006','王无',20) 等价于： Insert into student（sno，sname，sage） Values('200515026','王无',20)

  32. 例2:向视图stu_is中插入一个新的学生记录，学号为200515027，姓名为王唔，年龄为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’)

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

  34. 例4:向视图S_G中插入一个新的学生记录，学号为200515027，平均成绩为60；例4:向视图S_G中插入一个新的学生记录，学号为200515027，平均成绩为60； Insert into S_G Values(‘200515027’ , 60) 系统将发出错误信息：“视图或函数‘e_view’ 不可更新，因为它包含聚合函数。

  35. 2）通过视图更新数据 • 使用UPDATE语句可以通过视图修改基本表的数据。 例1：将视图stu_info中学号为“200515001”的学生姓名改为“张山” update stu_info set sname='张山' where sno='200515001‘ 等价于： update student set sname='张山' where sno='200515001‘

  36. 例2：将视图stu_is中学号为“200515004”的学生姓名改为“张珊”例2：将视图stu_is中学号为“200515004”的学生姓名改为“张珊” update stu_is set sname=‘张珊' where sno='200515004‘ 等价于： update student set sname=‘张珊' where sno=‘200515004‘ and sdept=‘IS’ and ssex=‘男’

  37. 若更新视图时只影响其中一个表，同时新数据值中含有主键字，系统将接受这个修改操作。若更新视图时只影响其中一个表，同时新数据值中含有主键字，系统将接受这个修改操作。 例3：将视图stu_is_c1中学号为“200515006”的学生成绩改为75； Update stu_is_c1 Set grade=75 Where sno='200515006‘ 等价于： Update sc Set grade=75 Where sno='200515006‘ and cno=‘1’

  38. 3）通过视图删除数据 • 使用DELETE语句可以通过视图删除基本表的数据。但对于依赖于多个基本表的视图，不能使用DELETE语句。 例1：删除视图stu_is中学号为“200515020”的学生记录 DELETE FROM stu_is WHERE Sno= ' 200215020 ‘ 等价于： DELETE FROM Student WHERE Sno= ‘ 200215020 ’ AND Sdept= ‘IS‘ AND ssex=‘男’

  39. 5、修改视图 • 格式： ALTER VIEW view_name [(column[,...n])] ASselect_statement [ WITH CHECK OPTION ]

  40. 例1：将stu_info视图修改为只包含学生学号、姓名。例1：将stu_info视图修改为只包含学生学号、姓名。 Alter view stu_info AS Select sno，sname from student 例2：将stu_is视图修改为只包含信息系学生学号、姓名及年龄。Alter view stu_is AS Select sno，sname，sage from student Where sdept =‘IS’

  41. 6、删除视图 • 删除视图的T-SQL语句是DROP VIEW，格式为： DROP VIEW { view } [ , …n ] 例1：同时删除视图stu_is和s_g。 drop view stu_is , s_g

  42. 视图的优点（P125）： • 1. 视图能够简化用户的操作； • 2. 视图使用户能以多种角度看待同一数据； • 3. 视图对重构数据库提供了一定程度的逻辑独立性； • 4. 视图能够对机密数据提供安全保护； • 5. 适当的利用视图可以更清晰的表达查询。

  43. 总结 本次课学习内容 • 管理视图 • 创建、查询、更新、编辑及删除

More Related