mysql视图

cillian_bao

已于 2022-06-30 20:36:53 修改

阅读量63

点赞数

分类专栏： SQL那些事文章标签： mysql 数据库 java

于 2022-06-30 17:59:32 首次发布

本文链接：https://blog.csdn.net/cillian_bao/article/details/125545032

版权

SQL那些事专栏收录该内容

8 篇文章 0 订阅

订阅专栏

前言

为什么需要视图：
视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的价格，则不会提供给他。再比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他人的查询视图中则不提供这个字段。

视图概念理解

视图是一种虚拟表，本身是不具有数据的，占用很少的内存空间，它是 SQL 中的一个重要概念。
视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。
向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句
在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我
们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便

基本语法

在创建视图时，没有在视图名后面指定字段列表，则视图中字段列表默认和SELECT语句中的字段列表一致。如果SELECT语句中给字段取了别名，那么视图中的字段名和别名相同

CREATE VIEW emp_year_salary (ename,year_salary)
AS
SELECT ename,salary*12*(1+IFNULL(commission_pct,0))
FROM t_employee;

多表查询创建视图

CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS
SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;

基于视图创建视图
当我们创建好一张视图之后，还可以在它的基础上继续创建视图。

CREATE VIEW emp_dept_ysalary
AS
SELECT emp_dept.ename,dname,year_salary
FROM emp_dept INNER JOIN emp_year_salary
ON emp_dept.ename = emp_year_salary.ename;

视图查看

语法1：查看数据库的表对象、视图对象

SHOW TABLES;

语法2：描述视图的结构

DESC 视图

语法3：查看视图的属性信息
执行结果显示，注释Comment为VIEW，说明该表为视图，其他的信息为NULL，说明这是一个虚表。

# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称'\G

语法4：查看视图的详细定义信息

SHOW CREATE VIEW 视图名称;

可更新视图/不可更新视图

要使视图可更新，视图中的行和底层基本表中的行之间必须存在一对一的关系。
如下的视图不支持更新：

在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；
视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；
在定义视图的SELECT语句中使用了 JOIN联合查询，视图将不支持INSERT和DELETE操作；
在定义视图的SELECT语句后的字段列表中使用了数学表达式或子查询，视图将不支持INSERT，也不支持UPDATE使用了数学表达式、子查询的字段值；
在定义视图的SELECT语句后的字段列表中使用 DISTINCT 、聚合函数、 GROUP BY 、 HAVING 、UNION 等，视图将不支持INSERT、UPDATE、DELETE；
在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持INSERT、UPDATE、DELETE；
视图定义基于一个不可更新视图；
常量视图。

注意

虽然可以更新视图数据，但总的来说，视图作为虚拟表，主要用于方便查询，不建议更新视图的数据。对视图数据的更改，都是通过对实际数据表里数据的操作来完成的。

修改与创建视图

修改

使用CREATE OR REPLACE VIEW 子句修改视图
方法1

CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;

方法2

ALTER VIEW 视图名称
AS
查询语句

删除视图

删除视图只是删除视图的定义，并不会删除基表的数据。

DROP VIEW IF EXISTS 视图名称;
DROP VIEW IF EXISTS 视图名称1,视图名称2,视图名称3,...;

视图特点

优点：
操作简单
减少数据冗余
数据安全
能够分解复杂的查询逻辑
缺点：
如果我们在实际数据表的基础上创建了视图，那么，如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂，可读性不好，容易变成系统的潜在隐患。因为创建视图的 SQL 查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增加维护的成本。