sql视图

2021-12-26 15:13:59

概述

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

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

基本语法

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更新操作：

建立信息系选修了 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';

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