[MySQL]视图

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: [MySQL]视图

1. 常见的数据库对象

2. 视图概述

2.1 视图的使用场景

视图一方面可以帮我们使用表的一部分而不是所有的表,另一方面也可以针对不同的用户制定不同的查询视图。

比如,针对一个公司的销售人员,我们只想给他看部分数据,而某些特殊的数据,比如采购的价格,则不会提供给他;人员薪酬是个敏感的字段,只给某个级别以上的人员开放,其他人的查询视图中则不提供这个字段。

2.2 视图的理解

视图是一种 虚拟表 ,本身是 不具有数据 的。视图是建立在已有表的基础上, 视图赖以建立的这些表称为基表。

  • 可以将视图理解为存储起来的 SELECT 语句
  • 视图的创建和删除只影响视图本身,不影响对应的基表。在数据库中,视图不会保存数据,数据真正保存在数据表中。当对视图中的数据进行增加、删除和修改操作时,数据表中的数据会相应地发生变化;反之亦然。
  • 视图,是向用户提供基表数据的另一种表现形式。通常情况下,小型项目的数据库可以不使用视图,但是在大型项目中,以及数据表比较复杂的情况下,视图的价值就凸显出来了,它可以帮助我们把经常查询的结果集放到虚拟表中,提升使用效率。

3. 创建视图

3.1 语法

CREATE [OR REPLACE] # 创建或如果视图已经存在替换视图
# [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)] # 字段列表为查询出来的字段取别名
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]

当使用WITH CHECK OPTION子句创建视图时,MySQL会通过视图检查正在更改的每个行,例如,插入,更新,删除,以使其符合视图的定义。 MySQL允许基于另一个视图创建视图,会检查依赖视图中的规则以保持一致性。为了确定检查的范围,mysql提供了两个选项: CASCADED 和 LOCAL ,默认值为 CASCADED 。

CASCADED:级联检查条件,MySQL会一直往上检查,直到关联到真实的表数据。

LOCAL:MySQL只会检查当前视图的规则

精简版:

CREATE VIEW 视图名称
AS 查询语句

3.2 创建单表视图

3.2.1 基于单表创建视图

CREATE VIEW v_emp1
AS
SELECT employee_id, last_name, salary
FROM employees;
SELECT * FROM v_emp1;

3.2.2 为视图的字段取名

创建视图时,查询语句中字段的别名会作为视图的字段名。

CREATE VIEW v_emp2
AS
SELECT employee_id emp_id, last_name l_name, salary sal
FROM employees;
SELECT * FROM v_emp2;

创建视图时,可以通过指定视图的字段列表为查询出来的字段取别名。

# 字段列表中字段的个数与SELECT中字段的个数相同
CREATE VIEW v_emp3(emp_id, l_name, sal)
AS
SELECT employee_id, last_name, salary
FROM employees;
SELECT * FROM v_emp3;

3.2.3 通过对基表的字段进行运算创建视图

CREATE VIEW v_dep_avg_sal
AS
SELECT department_id, AVG(salary) avg_sal
FROM employees
GROUP BY department_id;
SELECT * FROM v_dep_avg_sal;

3.3 创建多表联合视图

3.3.1 基于多表创建视图

CREATE VIEW v_emp_dep
AS
SELECT e.employee_id, e.department_id, d.department_name
FROM employees e
JOIN department d ON e.department_id = d.department_id;
SELECT * FROM v_emp_dep;

3.3.2 利用视图对数据进行格式化

需要输出某个格式的内容,比如我们想输出员工姓名和对应的部门名,对应格式为emp_name(department_name),就可以使用视图来完成数据格式化的操作。

CREATE VIEW v_emp_dept1
AS
SELECT CONCAT(e.last_name, '(', d.department_name, ')') emp_dept_name
FROM employees e
JOIN department d ON e.department_id = d.department_id;
SELECT * FROM v_emp_dept1;

3.4 基于视图创建视图

CREATE VIEW v_emp4
AS
SELECT employee_id, last_name
FROM v_emp1;
SELECT * FROM v_emp4;

4. 查看视图

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

SHOW TABLES;

4.2 语法2:查看视图的结构

DESC / DESCRIBE 视图名称;
DESC v_emp1;

4.3 语法3:查看视图的属性信息

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

执行结果显示,注释Comment为VIEW,说明该表为视图,其他的信息为NULL,说明这是一个虚表。

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

SHOW CREATE VIEW 视图名称;
SHOW CREATE VIEW v_emp1;

5. 更新视图的数据

5.1 一般情况可以更新数据

MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时,数据表中的数据也会发生变化,反之亦然。

SELECT * FROM v_emp1;
UPDATE v_emp1
SET salary = 20000
WHERE employee_id = 102;
SELECT * FROM v_emp1;
SELECT *
FROM employees
WHERE employee_id = 102;

5.2 不可更新的视图

要使视图可更新视图中的行和底层基本表中的行之间必须存在 一对一 的关系

  • 当视图定义出现如下情况时,视图不支持更新操作:
  • 在定义视图的时候指定了“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;
  • 视图定义基于一个 不可更新视图 ;
  • 常量视图。

示例:

将每个部门的平均工资修改为2000,Mysql不能进行修改,因为平均工资是由每一行通过计算得到的,修改后不知道值如何进行分配到每一行。

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

6. 修改视图

6.1 通过 CREATE OR REPLACE VIEW 子句修改视图

CREATE OR REPLACE VIEW v_emp1
AS
SELECT employee_id, last_name, salary, email
FROM employees;
DESC v_emp1;

6.2 通过 ALTER VIEW 修改视图

语法:

ALTER VIEW 视图名称
AS
查询语句

代码示例:

ALTER VIEW v_emp1
AS
SELECT employee_id, last_name, salary, email, hire_date
FROM employees;
DESC v_emp1;

7. 删除视图

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

语法:

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

代码示例:

SHOW TABLES;
DROP VIEW IF EXISTS v_emp_dept1;
SHOW TABLES;

注意:基于视图a、b创建了新的视图c,如果将视图a或者视图b删除,会导致视图c的查询失败。这样的视图c需要手动删除或修改,否则影响使用。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
8天前
|
NoSQL 关系型数据库 MySQL
2024Mysql And Redis基础与进阶操作系列(8)作者——LJS[含MySQL 创建、修改、跟新、重命名、删除视图等具体详步骤;注意点及常见报错问题所对应的解决方法]
MySQL 创建、修改、跟新、重命名、删除视图等具体详步骤;举例说明注意点及常见报错问题所对应的解决方法
|
5月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(2)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
5月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(1)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
2月前
|
SQL 存储 关系型数据库
mysql-视图的定义和简单使用
这篇文章介绍了MySQL中视图的定义和简单使用方法,包括视图的创建规则和使用限制。通过一个实际的例子,展示了如何创建视图以及如何使用视图来简化复杂的SQL查询操作。
mysql-视图的定义和简单使用
|
2月前
|
存储 缓存 关系型数据库
MySQL 视图:数据库中的灵活利器
视图是数据库中的虚拟表,由一个或多个表的数据经筛选、聚合等操作生成。它不实际存储数据,而是动态从基础表中获取。视图可简化数据访问、增强安全性、提供数据独立性、实现可重用性并提高性能,是管理数据库数据的有效工具。
|
3月前
|
存储 关系型数据库 MySQL
|
4月前
|
SQL 安全 关系型数据库
MySQL创建视图(CREATE VIEW)13
【7月更文挑战第13天】创建视图是指在已经存在的 MySQL 数据库表上建立视图。视图可以建立在一张表中,也可以建立在多张表中。
67 1
|
5月前
|
缓存 DataWorks 关系型数据库
DataWorks产品使用合集之如何抽取MySQL视图数据
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
95 4
|
4月前
|
存储 数据可视化 关系型数据库
深入理解 MySQL 视图
深入理解 MySQL 视图
40 2
|
5月前
|
存储 SQL 关系型数据库
MySQL数据库进阶第四篇(视图/存储过程/触发器)
MySQL数据库进阶第四篇(视图/存储过程/触发器)