sql视图

概述

视图，可以让我们看数据库更简单，视图提供了一种可以让我们只关注基本表的某一个方面的机制，将基本表在逻辑上重新组织，让用户看着更简单，然后在这上面去做查询和其他操作。

视图是外模式的概念，可以从用户角度重新组织数据库，让用户使用起来更方便，而从视图到基本表之间存在映射。

定义视图

基本语法

CREATE VIEW 
	< 视图名 > [(< 列名 > [ , < 列名 >]…)] 
AS < 子查询 > 
[WITH CHECK OPTION];

组成视图属性列名：全部指定或全部省略

子查询不允许有ORDER BY和DISTINCT语句

建立信息系学生的视图

CREATE VIEW IS_Student
AS
SELECT Sno, Sname, Sage
FROM student
WHERE Sdept='IS';

建立信息系学生的视图，并要求进行修改和插入操 作时仍需保证该视图只有信息系的学生

CREATE VIEW IS_Student
AS
SELECT Sno, Sname, Sage
FROM student
WHERE Sdept='IS'
WITH CHECK OPTION;

添加WITH CHECK OPTION

对视图IS_Student更新操作：

• 修改操作：自动加上Sdept=‘IS’条件
• 删除操作：自动加上Sdept=‘IS’条件
• 插入操作：自动检查Sdept属性是否为‘IS’
  • 若不是，拒绝插入
  • 若没有提供Sdept值，则自动定义Sdept=‘IS’

建立信息系选修了 1 号课程的学生视图

基于多个表的视图

CREATE VIEW IS_S1
AS
SELECT student.`Sno`, Sname, Grade
FROM student, sc
WHERE student.`Sdept`='IS' AND student.`Sno`=sc.`Sno` AND sc.`Cno`='1';

建立信息系选修了 1 号课程且成绩在 90 分以上的 学生的视图

基于视图的视图

CREATE VIEW IS_S2
AS
SELECT Sno, Sname, Grade
FROM IS_S1
WHERE Grade>=90;

定义一个反映学生出生年份的视图

带表达式的视图

CREATE VIEW BT_S(Sno, Sname, Sbirth)
AS
SELECT Sno, Sname, 2022-Sage
FROM student;

将学生的学号及他的平均成绩定义为一个视图

分组视图

CREATE VIEW S_G(Sno, Gavg)
AS
SELECT Sno, AVG(Grade)
FROM sc
GROUP BY (Sno);

删除视图

DROP VIEW <视图名>

使用CASCADE删除，删除时连同由该视图导出的视图一起删除

查询视图

在信息系学生的视图中找出年龄小于 20 岁 的学生

SELECT Sno, Sname
FROM IS_Student
WHERE Sage<20;

视图消解转换后的查询语句

SELECT Sno, Sname
FROM student
WHERE Sdept='IS' AND Sage<20;

查询选修了 1 号课程的信息系学生

SELECT IS_Student.`Sno`, Sname
FROM IS_Student, sc
WHERE sc.`Cno`='1' AND IS_Student.`Sno`=sc.`Sno`;

在 S_G 视图中查询平均成绩在 90 分以上的学生学号 和平均成绩

SELECT Sno, Gavg
FROM S_G
WHERE Gavg>85;

视图消解查询转换

SELECT Sno, AVG(Grade)
FROM sc
GROUP BY Sno
HAVING AVG(Grade)>85;

更新视图

将信息系学生视图 IS_Student 中学号 200215122 的 学生姓名改为“刘辰”

UPDATE IS_Student
SET Sname='刘辰'
WHERE Sno='200215122';

视图消解转换

UPDATE Student
SET Sname='刘辰'
WHERE Sdept='IS' AND Sno='200215122';

向信息系学生视图 IS_S 中插入一个新的学生记录 ： 200215129 ，赵新， 20 岁

INSERT INTO 
IS_Student(Sno, Sname, Sage)
VALUES('200215129', '赵新', 20);

转换为对基本表的更新

INSERT INTO
Student(Sno, Sname, Sage, Sdept)
VALUES('200215129', '赵新', 20, 'IS');

删除信息系学生视图 IS_Student 中学号为 200215129 的记录

DELETE FROM IS_Student
WHERE Sno='200215129';

转换为对基本表的更新

DELETE
FROM student
WHERE Sno='200215129' AND Sdept='IS';

更新视图的限制：一些视图是不可更新的，因为对这些视 图的更新不能唯一地有意义地转换成对相应基本表的更新

视图的作用

1. 能够简化用户操作

2. 视图使用户能以多种角度看待同一数据

3. 视图对重构数据库提供了一定程度的逻辑独立性

4. 视图能够对机密数据提供安全保护

5. 适当的利用视图可以更清晰的表达查询