主流数据库之视图

文章目录

目录

文章目录

前言

视图

视图概述

创建视图

格式

注意问题：

示例

查看视图的方法

DESCRIBE语句查看视图基本信息

SHOW TABLES语句查看视图基本信息

查询information schema.views表，可以查看到数据库中所有视图的详细信息

修改视图

CREATE OR REPLACE VIEW语句修改视图

使用ALTER语句修改视图

视图操作

使用SQL语句更新视图

基本表数据新增后视图自动更新

删除视图中的数据

删除视图

---

前言

大家好，我是ice三分颜色。

个人主页：ice三分颜色的博客

本文讲了视图的相关知识，创建查看修改删除等。

走过路过的小伙伴们点个赞和关注再走吧，欢迎评论区交流，努力什么时候开始都不算晚，那不如就从这篇文章开始！

大家一起成长呀！笔芯

---

视图

视图概述

1.视图(View）是一个存储指定查询语句的虚拟表，视图中数据来源于由定义视图所引用的表，并且能够实现动态引用，即表中数据发生变化，视图中的数据随之变化。（其本质是来说，是查询语句，起了名称的查询语句，一般我们在查询中写得普通的查询语句，查询完后关掉就没有了。但可以作为视图存下来）

2.视图是一种数据库对象，是从一个或者多个数据表或视图（视图也可以从视图里进行查询哦）中导出的虚拟表，在数据库中只有视图的定义，而并没有存放视图中的数据，因为视图并没有真实存在的数据，真实的数据还在表中。

3.浏览视图时所对应数据的行和列数据来自定义视图查询所引用的表，并且在引用视图时动态生成。通过视图可以实现对基表数据的查询与修改。（不能直接进行插入操作哦）

4.视图让数据库用户使用的更方便了，主要包括：

简化数据查询和处理。视图可以为用户集中多个表中的数据，简化用户对数据的查询和处理。

屏蔽数据库的复杂性。数据库表的更改不影响用户对数据库的使用，用户也不必了解复杂的数据库中的表结构。（对于定义了若干张表连接的视图，则将表与表之间的连接操作对用户隐蔽起来。）

安全性。如果要使用户只能查询或修改用户有权限访问的数据，可以只授予用户访问视图的权限，而不授予访问表的权限，这样来提高数据库的安全性。

创建视图

格式

视图可以建立在一张表上，也可以建立在多张表或既有视图上。格式如下：

CREATE [OR REPLACE] VIEW 视图名[(column_list)]AS select查询语句

[ WITH [ CASCADED|LOCAL] CHECK OPTION] ;其中:

REPLACE表示替换已经创建的视图; WITH[CASCADED|LOCAL]CHECK OPTION表示视图在更新时保证在视图的权限范围之内CHECK OPTION 也叫做强制视图，不建议。CASCADED为默认值，表示更新视图时要满足所有相关视图和表的条件;LOCAL表示更新视图时满足该视图本身定义的条件即可，但一般不被允许。

视图属于数据库，默认情况下将在当前数据库创建视图，若想在给定数据库常见视图，创建时应将名称指定为 库的名字.视图名字。

创建视图要求用户具有针对视图的CREATE VIEW权限，同时具有查询设计的列的SELECT权限。

例：通过SELECT语句查询user表获取权限信息（其中Select priv表示用户是否具有SELECT权限，Create_view表示用户是否具有CREATE VIEW权限）

SELECT Select_priv,Create_view_priv

FROM mysql.user WHERE user='root';

注意问题：

使用CREATE VIEW语句创建视图时需要注意：

1.在视图的from子句中不能使用子查询。

2.在视图的select语句中不能引用系统或用户变量。

3.在视图的select语句中不能引用预处理语句参数。

4.在视图定义中允许使用order by，但是，如果从特定视图进行了选择，而该视图使用了具有自己order by的语句，它将被忽略。

5.在定义中引用的表或视图必须存在。可使用check table语句检查视图定义是否存在。

6.在定义中不能引用temporary表，不能创建temporary视图。

7.不能将触发程序与视图关联在一起。

示例

创建一个包含员工编号、姓名、职位和部门编号，并按员工编号升序排序的员工基本信息视图

CREATE VIEW v_emp_base

AS SELECT empno,ename,job,deptno FROM employee ORDER BY empno;

创建一个包含多表连接，以及分组查询的视图（视图的SELECT查询可以包含函数、数据组或经过运算得到的数据，也可以是从多个表中获取数据。）

CREATE OR REPLACE VIEW v_dept_sal(name,minsal,maxsal,avgsal)

AS

SELECT d.dname,MIN(e.sal),MAX(e.sal),AVG(e.sal)

FROM department d,employee e

WHERE d.deptno = e.deptno GROUP BY d.dname;

查询这个视图

SELECT *from v_dept_sal;

【示例】创建一个工资大于2000的员工年薪信息的视图（如果视图中的SELECT子句包含限定条件，创建视图时可以使用WITHCHECK OPTION选项。）

-- 表示在更新该视图时，记录数据要满足工资大于2000的条件

CREATE VIEW v_emp_salary

AS

SELECT empno,ename,sal*12 salary

FROM employee

WHERE sal>2000 WITH CHECK OPTION;

查看视图的方法

describe、show tables、show table status、show create view语句、查询information_schema数据库下的views表等。

DESCRIBE语句查看视图基本信息

DESCRIBE | DESC 视图名;

演示如下：

DESCRIBE v_emp_salary;

SHOW TABLES语句查看视图基本信息

从MySQL5.1开始，执行SHOW TABLES语句时不仅会显示表的名字，同时也会显示视图的名字。

SHOW TABLES;

演示如下：

show tables;

查询information schema.views表，可以查看到数据库中所有视图的详细信息

MySQL数据库中，所有视图的定义都存在information_schema数据库下的views表中。

SELECT * FROM information_schema.views WHERE 表名='视图名';

例：查询v_emp_salary视图定义

SELECT * FROM information_schema.views WHERE table_name='v_emp_salary';

修改视图

修改数据库中存在的视图，当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表的一致。

MySQL中通过CREATE OR REPLACE语句和ALTER语句来修改视图

CREATE OR REPLACE VIEW语句修改视图

CREATE OR REPLACE VIEW 视图名 [[(字段列表)]As select查询语句

[ WITH [ CASCADED|LOCAL] CHECK OPTION] ;

Create or replace view vw_dept

AS

Select *from dept;

查看视图

当视图已经存在时，修改语句对视图进行修改;当视图不存在时，创建视图。

使用ALTER语句修改视图

ALTER VIEW 视图名字 [(字段列表)]As select查询语句

[ WITH [ CASCADED|LOCAL] CHECK OPTION];

修改v_emp_base视图将deptno列改为sal列显示

ALTER VIEW v_emp_base

AS SELECT empno,ename,job,sal FROM employee ORDER BY empno;

视图操作

在视图上也可以进行修改数据的DML操作(INSERT、UPDATE和DELETE)。因为视图是“虚表”，因此对视图的操作最终会转换为对基本表的操作。

视图上的DML操作会有如下限制:

1.若一个视图依赖于多张基表，则一次只能修改一个基表的数据，不能同时修改多个基本表的数据;

2.如果修改违反了基表的约束条件或视图的with check option条件，则无法更新视图;

3.如果视图包含连接操作符、DISTINCT关键字、集合操作符（UNION) .聚合函数(COUNT、SUM、MAX等)、GROUP BY、ORDER BY、HAVING子句，则将无法更新视图;

4.如果视图包含SELECT列的子查询、WHERE子句中的子查询、FROM子句中的子查询，则无法更新视图。

使用SQL语句更新视图

通过对视图的更新操作来更新基本表数据

--根据emloyee表创建empview表

CREATE TABLE empview AS SELECT * FROM employee;

--创建视图v_emp_update

CREATE OR REPLACE VIEW v_emp_update

AS SELECT empno,ename,sal,deptno FROM empview WHERE deptno=20 WITH CHECK OPTION;

--操作视图v_emp_update更新数据

UPDATE v_emp_update SET sal=1000 WHERE ename='SMITH';

--查看基本表empview中数据的变化

SELECT empno,ename,sal,deptn o FROM empview WHERE ename='SMITH';

最后结果如下：

基本表数据新增后视图自动更新

向基本表中插入数据，视图数据相应更新--向基本表empview中插入一条记录

INSERT INTO empview VALUES(7777,'TEST','CLERK',7902,2020-12-17,800,NULL,20);

--查询视图v_emp_update的数据变化

SELECT * FROMv_emp_update;

删除视图中的数据

使用DELETE操作视图来更新基本表数据--删除视图中的数据

DELETE FROM v_emp_update WHERE empno=7777;

--查询基本表中数据的变化

SELECT* FROM empview;

删除视图

删除视图时，只能删除视图的定义，不会删除数据。

MySQL数据库中，用户必须拥有drop权限才能使用drop view语句来删除视图。

drop view命令可以删除多个视图，各视图名之间用逗号分隔。

DROP VIEW [if exists] 视图名 [,视图名]

删除视图v_emp_update

DROP VIEW IF EXISTS v_emp_update;

视图就没有啦