🌌关系数据库标准语言SQL之视图
🌍视图
视图的特点
- 虚表，是从一个或几个基本表（或视图）导出的表
- 只存放视图的定义，不存放视图对应的数据
- 基表中的数据发生变化，从视图中查询出的数据也随之改变
🪐1 定义视图
🌕1.1 建立视图
语句格式
CREATE VIEW
<视图名> [(<列名> [,<列名>]…)]
AS <子查询>
[WITH CHECK OPTION];说明：
- WITH CHECK OPTION
对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）- 子查询可以是任意的SELECT语句，是否可以含有ORDER BY子句和DISTINCT短语，则决定具体系统的实现。
组成视图的属性列名：全部省略或全部指定
- 全部省略: 由子查询中SELECT目标列中的诸字段组成
- 明确指定视图的所有列名:
某个目标列是聚集函数或列表达式
多表连接时选出了几个同名列作为视图的字段
需要在视图中为某个列启用新的更合适的名字注意：
- 关系数据库管理系统执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。
- 在对视图查询时，按视图的定义从基本表中将数据查出。
- 定义IS_Student视图时加上了WITH CHECK OPTION子句，对该视图进行插入、修改和删除操作时，RDBMS会自动加上Sdept='IS'的条件。
- 若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，我们称这类视图为行列子集视图。
【1】建立信息系学生的视图。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS';
【1】建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生 。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS'
WITH CHECK OPTION;
【1】 建立信息系选修了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= '1';
【1】 建立信息系选修了1号课程且成绩在90分以上的学生的视图。
CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Grade>=90;
带表达式的视图
【1】定义一个反映学生出生年份的视图。
CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2014-Sage
FROM Student;
分组视图
【1】将学生的学号及平均成绩定义为一个视图
CREAT VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
【1】将Student表中所有女生记录定义为一个视图
CREATE VIEW F_Student(F_Sno,name,sex,age,dept)
AS
SELECT * /*没有不指定属性列*/
FROM Student
WHERE Ssex=‘女’;
缺点：修改基表Student的结构后，Student表与F_Student视图 的映象关系被破坏，导致该视图不能正确工作。
🌕1.2 删除视图
语句格式：
DROP VIEW <视图名>[CASCADE];
说明：
- 该语句从数据字典中删除指定的视图定义
- 如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除
- 删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除
【1】删除视图BT_S和IS_S1
DROP VIEW BT_S; /*成功执行*/
DROP VIEW IS_S1; /*拒绝执行*/
【1】要删除IS_S1，需使用级联删除：
DROP VIEW IS_S1 CASCADE;
🪐2 查询视图
用户角度：查询视图与查询基本表相同
关系数据库管理系统实现视图查询的方法：视图消解法
- 进行有效性检查
- 转换成等价的对基本表的查询
- 执行修正后的查询
【1】在信息系学生的视图中找出年龄小于20岁的学生。
SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;
视图消解转换后的查询语句为：
SELECT Sno,Sage
FROM Student
WHERE Sdept= 'IS' AND Sage<20;
【1】查询选修了1号课程的信息系学生
SELECT IS_Student.Sno,Sname
FROM IS_Student,SC
WHERE IS_Student.Sno =SC.Sno AND SC.Cno= '1';
【1】在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩
SELECT *
FROM S_G
WHERE Gavg>=90;
S_G视图的子查询定义：
CREATE VIEW S_G (Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
错误：
SELECT Sno，AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90
GROUP BY Sno;
正确：
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;
【1】也可以用如下SQL语句完成
SELECT *
FROM (SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno) AS S_G(Sno,Gavg)
WHERE Gavg>=90;
🪐3 更新视图
【1】将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”刘辰”。
UPDATE IS_Student
SET Sname= '刘辰'
WHERE Sno= ' 201215122 ';
转换后的语句：
UPDATE Student
SET Sname= '刘辰'
WHERE Sno= ' 201215122 ' AND Sdept= 'IS';
【1】向信息系学生视图IS_S中插入一个新的学生记录，其中学号为”201215129”，姓名为”赵新”，年龄为20岁
INSERT
INTO IS_Student
VALUES(‘201215129’,’赵新’,20);
转换为对基本表的更新：
INSERT
INTO Student(Sno,Sname,Sage,Sdept)
VALUES(‘200215129 ','赵新',20,'IS' );
【1】删除信息系学生视图IS_Student中学号为”201215129”的记录
DELETE
FROM IS_Student
WHERE Sno= ' 201215129 ';
转换为对基本表的更新：
DELETE
FROM Student
WHERE Sno= ' 201215129 ' AND Sdept= 'IS';
更新视图的限制：一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新
【1】定义的视图S_G为不可更新视图。
将学生的学号及平均成绩定义为一个视图
CREAT VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
UPDATE S_G
SET Gavg=90
WHERE Sno= '201215121';
这个对视图的更新无法转换成对基本表SC的更新
DB2对视图更新的限制：
- 若视图是由两个以上基本表导出的，则此视图不允许更新。
- 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。
- 若视图的字段来自集函数，则此视图不允许更新。
- 若视图定义中含有GROUP BY子句，则此视图不允许更新。
- 若视图定义中含有DISTINCT短语，则此视图不允许更新。
- 若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。
- 一个不允许更新的视图上定义的视图也不允许更新
【1】将SC中成绩在平均成绩之上的元组定义成一个视图
CREATE VIEW GOOD_SC
AS
SELECT Sno,Cno,Grade
FROM SC
WHERE Grade >
(SELECT AVG(Grade)
FROM SC);
🪐4 视图的作用
- 视图能够简化用户的操作
- 视图使用户能以多种角度看待同一数据
- 视图对重构数据库提供了一定程度的逻辑独立性
- 视图能够对机密数据提供安全保护
- 适当的利用视图可以更清晰的表达查询
📝总结
数据库SQL Server领域就像一片未被勘探的信息大海，引领你勇敢踏入数据科学的神秘领域。这是一场独特的学习冒险，从基本概念到算法实现，逐步揭示更深层次的模式分析、匹配算法和智能模式识别的奥秘。
