数据库之视图
- 定义视图
- 建立视图
- 删除视图
- 视图的使用
- 视图的查询
- 视图的更新
定义视图
建立视图
SQL语言用CREATE VIEW命令建立视图,其中的格式如下
CREATE VIEW <视图名> [(<列名>[,<列名>]]
AS <子查询>
[WITH CHECK OPTION]
1,其中子查询是任意的select语句,可以包含GROUP BY等语句,不过这得看实际情况。
2,WITH CHECK OPTION 表示在执行更新,删除,插入得操作时,被执行的行,首先要满足子查询中的谓语条件(即条件表达式)
3,指定视图的列名只能全部省略或者是全部指定,但是有几种情况是一定要指定视图中的列名。
1,创建视图时,在select语句中出现不是基本表中的列名,而是表达式或者是聚集函数,比如AVG(score),2019-year
2,如果使用多张表创建视图,但是其中两张表中有相同的列名。
4,视图的建立不同于基本表的建立,在建立视图后,系统只是将视图的定义的语句放入到数据字典中,当我们要查询的时候,就从数据字典中取出来并执行。执行更新等操作的时候,也是一个原理,只不过在视图定义的基础上加了更新的语句,详见请看后面。
5,视图的建立可以基于多张表,也可以基于视图进行建立。如果一个视图是基于一个基本表创建的并且包含了主键,只是去除了某些行和列,在这里我们称为行列子集视图
6,视图是是基于基本表所创建的,视图的列名和基本表中的列名具有一定的映射关系,因此当我们对基本表的结构进行该表的时候,可能会破坏这种映射的关系。因此当我们在涉及对表的结构进行修改的时候,我们应该删除原来的视图,创建新的视图。
CREATE VIEW S(NAME,SEX,SCORE)
AS
SELECT Name,Sex,Score
FROM STUDENT
//比如我们删除Sex这个列,那么它们之间的映射关系就会被破坏,系统就不能正常的工作
删除视图
删除视图的格式如下
DROP VIEW <视图名> [CASCADE]
DROP 命令将视图的定义从数据字典中删除,如果在这个视图上导出了其他的视图,我们应该使用级联删除语句CASCADE 将在这基础上的视图一起删除。
DROP VIEW S2 //视图s1是在s2基础上导出的视图,因此执行不成功
DROP VIEW S1 //执行成功
视图的使用
视图的查询
1,在用户使用视图进行相应的查询的时候,我们应该先检测有效性,即检测相关的表和视图是否存在。然后再从数据字典中取出视图的定义,并将视图定义中的子查询和用户定义的查询结合。转化为等价的查询语句,然后执行这种方式称为“视图消解”。
CREATE VIEW S
AS
SELECT ID,NAME,SEX
FROM STUDENT
WHERE SCORE>80
//这是这个视图的定义
当我们查询一个ID=”20198990“时
//等价转化的语句
SELECT ID,NAME,SEX
FEOM STUDENT
WHERE SCORE>80 AND ID="20198990"
2,视图的查询在行列子集视图都能进行正确的等价的转化,但是涉及到有表达式,或者是聚集函数的表达式时,我们应该基于基本表进行修改。
CREATE VIEW S_C (SNO,GAVG)
AS
SELECT SNO,AVG(GRADE)
FROM SC
GROUP BY SNO
//当我添加查询平均分大于90分时
SELECT SNO,GAVG
FROM S_C
WHERE GAVG>90
//等价转化为
SELECT SNO,AVG(GRADE)
FROM SC
WHERE AVG(GRADE)>90
GROUP BY SNO
//聚集函数出现在where语句,这显示是错误的。我们应该转化为这种形式
SELECT SNO,AVG(GRADE)
FROM SC
GROUP BY SNO
HAVING AVG(GRADE)>90
3,派生表和视图的区别,派生表出现在语句当中,当所有的语句执行完毕的时候就立即被删除。视图一经定义就被存放在数据字典之中,当我们要使用该视图的时候,就从数据字典中取出并执行。
视图的更新
1,更新视图是指插入,修改,删除数据,视图的更新操作和视图的查询一样,通过视图消解,将用户给的条件表达式同视图中子查询的条件表达式转化为等价的select语句。
CREATE VIEW STUDENT
AS
SELECT SNO,SNAME
FROM S
WHERE SDEPT='CS'
WITH CHECK OPTION
//我们修改计算机学院,学号为2019780的学生的信息
UPDATE STUDENT
SET SNAME='张三'
WHERE SNO='2019780'
//等价转化为等价的语句
UPDATE S
SET SNAME='张三'
WHERE SNO='2019780' AND SDEPT='CS'
2,加入 WITH CHECK OPTION 语句可以防止视图对视图之外的数据进行更改
根据上列的视图
INSERT INTO S VALUES('2019860','李四')
//此时我们可以观察到其他的值并没有给定,因此对了除条件表达式出现的列名,其他的列值是为空的
3,并不是所有的视图是可以更新的,但是行列子集视图是可更新的,当时包含聚集函数和其他表达式的是不可更新的,因为它们映射到基本表的基本属性,是没法对应的。
UPDATE S_G
SET GAVG=90
WHERE SNO='2019780'
//这个语句是没办法转化为,对基本表的操作,因为聚集函数的更新,没办法修改各科的成绩