【MySQL】视图

目录

1.什么是视图

2.视图的基本使用

2.1.准备工作

2.2.创建视图

3.视图与基表的相互影响

3.1.修改视图影响基表

3.2.修改基表影响视图

3.3.通过视图不能修改基表的几种情况

 4.替换同名视图

5.修改视图

6.删除视图

 7.关于视图

---

1.什么是视图

        视图(VIEW)也被称作虚表，即虚拟的表。视图没有实体

        视图就是将select的结果像表一样保留下来的虚表。

       视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。

        视图是一种虚拟表，其内容由查询（通常是SQL查询）定义。视图本身不存储数据，而是基于一个或多个基表（实际存储数据的表）生成。当访问视图时，数据库系统会自动执行定义视图的查询，并返回结果集。

特性

1. 虚拟性：视图不存储实际数据，只存储查询定义。因此，视图在数据库中占用的空间非常小。
2. 数据独立性：视图提供了一个逻辑层，使得应用程序可以独立于基表结构的变化。例如，即使基表的列名或结构发生变化，只要视图的查询定义保持不变，应用程序仍然可以通过视图访问数据。
3. 安全性：视图可以用于限制用户访问基表中的敏感数据。通过定义视图，可以仅暴露用户需要访问的列和行，从而保护数据的机密性。
4. 简化复杂查询：视图可以封装复杂的SQL查询，使得用户可以通过简单的查询语句访问复杂的数据集。

视图与基表的关系

• 数据变化的影响：
  • 当视图中的数据发生变化时（例如通过INSERT、UPDATE或DELETE操作），这些变化会反映到基表中。但是，这种变化受到视图定义和数据库权限的限制。
  • 同样地，当基表中的数据发生变化时，这些变化也会反映到基于该基表的视图中。
• 更新限制：
  • 并非所有视图都是可更新的。例如，如果视图包含聚合函数（如SUM、AVG）、DISTINCT关键字、GROUP BY子句或子查询，则该视图通常是不可更新的。
  • 某些数据库系统允许通过视图进行更新操作，但会施加额外的限制以确保数据的一致性和完整性。

2.视图的基本使用

2.1.准备工作

我们先创建一个文件，在它里面填入下面这些内容

vim scott_data.sql

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `scott`;

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);


DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);


DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等级',
  `losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
  `hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);


insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

我们会发现其里面都是SQL记录，对于MySQL我们备份其数据库时，其实备份的全部都是一条条有效的SQL记录，通过重新执行这些SQL，我们便能够得到和原来一摸一样数据库。 

接下来我们就可以在mysql中将这个数据库给创建出来了： 

source 该文件的绝对路径;

 例如我这里是：

source /home/zs_108/scott_data.sql

然后我们查询我们的数据库，发现数据库中多了一个scott的数据库： 

 我们进去看看 

use scott;
show tables;

 这三张表是

• EMP员工表
• DEPT部门表
• SALGRADE工资等级表

我们先分别查看一下表结构和表内容：

• EMP员工表

desc emp;
select * from emp;

 

•  DEPT部门表

desc dept;

select * from dept;

• SALGRADE工资等级表 

desc salgrade;
select * from salgrade;

接下来我们的查询都会基于上面这一个数据库。

2.2.创建视图

与其说是“创建"，但是视图没有实体，倒不如说是”定义视图“

语法：

create view 视图名 as select 列名 from 表名 where 条件

说明：

• 创建视图时会先执行select语句，然后用查询得到的结果来创建视图。

---

        例如下面的一个场景，当我们要查询每个员工及其对应的部门名称时，需要使用员工表和部门表进行多表查询，并筛选出员工的部门号等于部门的部门号的记录。如下：

select ename, dname from emp inner join dept on emp.deptno=dept.deptno;

         当您执行这个查询时，数据库会查找emp表和dept表中所有deptno字段值相匹配的行，并返回这些行的ename和dname字段值。

        例如，如果emp表中有一个员工其deptno为10，而dept表中有一个部门其deptno也为10，那么这个员工的名字和该部门的名字就会被一起返回作为查询结果的一部分。

  

 如果该查询结果会被频繁用到，那我们就可以给上述查询结果创建视图，这样我们以后就可以直接访问该视图，而不用每次都先对表进行内连接，然后进行筛选。

那么在创建视图之前呢，我们先去mysql目录下看看实体

 

可以看到，每一张表都是以单独的文件进行存储的，我们等会创建视图之后，看看视图是不是也会有这样一个载体

create view v_ename_dname as select ename, dname from emp inner join dept on emp.deptno=dept.deptno;

 这是是一个创建视图的命令，该视图名为v_ename_dname，它基于emp（员工）表和dept（部门）表的INNER JOIN结果。这个视图将展示每个员工的名字（ename）和他们所属部门的名字（dname）。

  

然后我们可以通过show tables命令就能看到这个视图。如下：

show tables;

   

创建了这张v_ename_dname视图后,我们就可以直接通过查询视图，来查看每个员工及其对应的部门名称了。如下：

select * from v_ename_dname;

创建视图之后，我们还是去刚刚那个目录下面看，发现没有任何新文件进行添加，这也是视图没有实体的一种体现

在 Linux 的 MySQL 数据目录中，视图没有物理文件载体，其本质是存储在系统表中的查询逻辑定义。

3.视图与基表的相互影响

视图与基表之间的相互影响主要体现在数据和结构的动态依赖上。

• 基表作为实际存储数据的实体，其任何变化都会直接反映在视图中。

例如，当基表中的某条记录被修改或删除时，基于该基表的视图在查询时会立即显示更新后的结果，因为视图本质上是通过实时执行保存的查询逻辑来生成数据的。

这种动态性使得视图始终与基表保持同步，但也意味着一旦基表的结构发生变动，比如某个字段被重命名或删除，而视图中又引用了该字段，视图的查询就会因找不到对应列而报错，需要重新调整视图的定义才能恢复使用。

• 反过来，视图的操作也可能对基表产生间接影响。

对于简单的视图，如果设计时满足特定条件（如不涉及聚合函数或多表关联），用户可以通过视图直接修改基表的数据。

例如，通过一个仅包含员工姓名和编号的视图更新某位员工的姓名，实际上会直接修改基表中对应的记录。

然而，复杂的视图（如包含多表连接或计算字段）通常无法直接修改数据，因为这种操作可能导致数据逻辑混乱或无法准确定位到具体的基表记录。

此外，频繁查询复杂视图可能对基表的性能造成压力，尤其是当视图的底层查询涉及大量数据或复杂的计算时，每次访问视图都需要重新执行这些操作，可能拖慢基表的响应速度。

• 权限管理也是两者互动的重要方面。

用户需要同时拥有视图的访问权限和基表的相应权限才能通过视图操作数据。

例如，即使某用户被授予了视图的查询权限，但如果缺乏基表的读取权限，仍然无法通过视图获取数据。这种权限的分离性要求在设计数据库权限时需综合考虑视图与基表的关系，避免因权限配置不当导致功能受限或安全隐患。

• 最后，视图与基表的依赖关系需要谨慎维护。

如果基表被删除或结构发生重大调整，依赖它的视图会立即失效，可能导致依赖这些视图的应用程序出现错误。

因此，在设计数据库时，需定期检查和更新视图的定义，确保其与基表的变动保持一致，从而维持系统的稳定性和可靠性。这种相互依赖的特性要求开发者在利用视图简化操作的同时，必须充分理解其背后的数据逻辑和潜在影响。

3.1.修改视图影响基表

例如下面我们要在视图中修改员工SMITH的名称为小写：

update v_ename_dname set ename='smith' where ename='SMITH';

 然后我们现在去emp表中查看这个更改是否生效了。

select * from emp;

 确实生效了，说明修改视图会影响基表！

3.2.修改基表影响视图

参看部门表：

select * from dept;

假设我们现在要将accounting部门改为HR部门。

update dept set dname='HR' where deptno=10;

然后我们查看视图v_ename_dname:

select * from v_ename_dname;

发现视图中的数据被改变了！说明修改基表会影响视图。

3.3.通过视图不能修改基表的几种情况

根据MySQL的文档，如果一个视图包含以下情况之一，则不可更新基表：

视图不可更新基表的主要情况

• 1.使用聚合函数（如 SUM、COUNT、AVG 等）

说的通俗一点就是，视图用了“总和”或“计数”

想象你有一张表格记录每个人的销售额，而视图显示的是整个团队的总销售额。这时候如果你想通过总销售额这个视图，直接给某个成员增加销售额，系统会陷入困惑——总销售额是所有人的数据相加的结果，它无法知道应该调整哪个人的原始数据。这就是为什么使用聚合函数（如 SUM、COUNT）的视图无法直接修改数据。

• 2.使用 GROUP BY 或 HAVING 子句

说的通俗一点就是，视图对数据“分门别类”

假设你有一份员工名单，视图按部门分组显示每个部门的人数。如果你试图通过这个分组视图删除“销售部”的某个员工，系统会不知所措——分组后的视图把多个员工合并成一个统计值，它无法知道具体要删除哪一个员工的记录。这就是使用 GROUP BY 或 HAVING 的视图不可更新的原因。

• 3.使用 UNION 或 UNION ALL

想象你有两张清单：一张是公司正式员工的名单，另一张是实习生的名单。你用胶水把两张清单粘在一起（类似 UNION），合并成一份“所有工作人员”的总表。现在你想通过这份总表，直接添加一个新名字“小明”到“实习生”清单里。但问题是，总表是粘在一起的，数据库不知道应该把“小明”放进哪张原始清单——是正式员工还是实习生？它可能两边都加，也可能报错。这就是为什么合并多表的视图无法直接插入数据，就像你无法通过粘在一起的两张纸直接修改原始清单。

• 4.在 SELECT 列表中包含子查询

假设你的视图里显示每个员工的工资和全公司的平均工资（比如 SELECT 工资, (SELECT AVG(工资) FROM 员工表) AS 平均工资）。现在你想通过这个视图，把某个员工的工资从 8000 改成 10000。但数据库会困惑：改这个员工的工资容易，但“平均工资”是通过子查询动态算出来的，它不是一个真实存在的字段，而是一个计算结果。你无法通过修改计算结果去反向调整原始数据，就像你无法通过修改“班级平均分”这个数字，直接让某个学生的成绩变高。

• 5.涉及多个基表的连接（如 JOIN）

说的通俗一点就是，视图合并了多个表格

比如视图将“员工表”和“部门表”合并，显示员工姓名和所属部门。如果你通过这个视图插入一条新数据（如“小明，技术部”），系统会陷入两难：应该把“小明”插入员工表，还是把“技术部”插入部门表？或者两者都需要插入？这就是涉及多表连接（如 JOIN）的视图不可更新的根本原因。

• 6.使用 DISTINCT 去重

说白了，就是视图隐藏了重复数据

如果视图用 DISTINCT 去重（比如去掉重复的客户名称），你想通过这个视图修改某个客户的名字，系统会不知道应该修改原始数据中的哪一条记录——因为去重后可能对应多条原始记录。这就像你有一叠名片，合并了重复的联系人后，想修改其中一个人的电话，却找不到具体哪张名片需要改。

• 7.视图中包含计算字段或表达式

视图包含“计算后的数值”

例如，视图显示员工工资涨薪10%后的结果。如果你想通过视图把“涨薪后的工资”从11000元改成12000元，系统会困惑：是要把原工资从10000元改成10909元（12000 / 1.1），还是直接覆盖原工资？这就是为什么包含计算字段或表达式的视图不可更新。

• 8.视图引用了其他不可更新的视图

视图引用了另一个“不可靠的镜子”

如果视图A是基于另一个不可更新的视图B创建的，那么视图A自然也无法更新。这就像你通过一面哈哈镜（视图B）看东西，再用另一面镜子（视图A）反射哈哈镜的影像，此时无论怎么调整第二面镜子，都无法改变原始物体的形状。

• 9.基表缺少必要的主键或唯一约束

说白一点就是原始数据缺少“唯一标识”

如果原始表格没有主键或唯一标识字段（比如员工编号），视图就无法准确定位到某一行数据。例如，你想通过视图删除一个叫“张三”的员工，但原始表中有多个“张三”，系统不知道应该删除哪一个。这就是为什么基表缺少主键或唯一约束时，视图不可更新。

• 10.使用外连接（如 LEFT JOIN、RIGHT JOIN）

比如你的视图用 LEFT JOIN 显示所有员工及其部门，包括那些还没分配部门的员工（部门字段显示 NULL）。现在你想通过这个视图，给未分配部门的员工直接插入一个“技术部”。但问题来了：这些员工的部门字段在基表中本来就是空的，数据库不知道应该往哪里插入“技术部”——是去部门表里新增一条记录，还是直接修改员工表的部门字段？如果部门表里本来没有“技术部”，直接插入会导致数据混乱。这就像你在一张借书卡上看到某人“未归还书籍”，但系统不知道这本书是丢了、还是根本没记录，因此无法直接通过借书卡修改图书馆的库存。

• 11.视图定义中使用了临时表或派生表

假设你创建视图时，先用一个子查询生成临时数据（例如 SELECT * FROM (SELECT 姓名, 工资 FROM 员工表) AS 临时表）。这相当于你临时抄了一份员工姓名和工资的笔记，然后基于这份笔记创建视图。现在你想通过视图在这份“笔记”上修改数据，比如把“张三的工资”从 5000 改成 6000。但这份笔记是临时的，改完就会消失，数据库无法知道应该把修改同步到原始员工表里。就像你在草稿纸上修改会议记录，但不会自动更新到正式的会议纪要中。

---

视图的不可更新性，本质上是因为它的“虚拟性”——它只是基于规则动态展示数据，并不实际存储数据。

当这些规则复杂到无法反向映射到原始数据的某一行或某一列时，数据库就无法安全地执行修改操作。就像你无法通过镜子的影像直接修改真实物体的位置一样，某些视图也无法直接修改原始数据。

因此，在需要修改数据时，最可靠的方式是直接操作原始表，而非通过视图。

 4.替换同名视图

当存在同名的视图时，如何对其进行替换呢?下面就来介绍具体的方法。

当执行 CREATE VIEW时，如果已经有同名的视图存在，该命令会报错。

在这种情况下，可以像“CREATE OR REPLACE VIEW …”这样，加上OR REPLACE 替换视图。也就是说，删除已经存在的同名的视图，创建新的视图。

例如，在视图 v1已经存在的情况下执行以下命令。

create or replace view v_ename_dname as select now();

 

视图就被顺利替换了，这个是在不知道是否有同名视图存在的状态下创建视图的办法，这个是很好用的。 

5.修改视图

我们看源表

 修改视图结构的时候，我们通常使用alter view。

alter view 视图名 as select 列名 from 表名;

接下来，我们让当前视图v1中包含tb1中的列name和列age。

alter view v_ename_dname as select name,age from tb1;

6.删除视图

删除视图的SQL如下：

DROP VIEW if exists 视图名;

加上if exists，那么即使这个视图名不存在，也不会报错 

比如将刚才创建的视图删除后，在数据库中就看不到这个视图了。如下：

drop view if exists v_ename_dname;

 7.关于视图

1. 命名唯一性：
   • 与普通表一样，每个视图在数据库中都必须有唯一的名称。这意味着您不能有两个同名的视图，也不能有一个视图和一个表具有相同的名称（在同一个数据库模式下）。
2. 视图数量：
   • 理论上，您可以创建任意数量的视图，但需要注意数据库的性能和资源限制。复杂的视图可能会增加查询处理的开销，特别是在涉及大量数据或复杂连接时。
3. 索引和触发器：
   • 视图本身不支持索引。您不能在视图上直接创建索引，因为视图是基于查询定义的，不存储实际数据。
   • 同样，视图也不能有触发器与之关联。触发器是响应表上的特定事件（如INSERT、UPDATE或DELETE）而自动执行的存储程序。由于视图不直接存储数据，因此无法在其上定义触发器。
4. 安全性：
   • 视图提供了一种提高数据访问安全性的方法。通过创建仅包含用户需要访问的字段和行的视图，您可以限制用户对底层表的直接访问。用户只能看到视图定义的内容，而不能看到或修改其他数据。
   • 要访问视图，用户必须具有足够的权限。这意味着数据库管理员可以控制哪些用户可以查看或修改视图中的数据。
5. ORDER BY子句：
   • 在创建视图时，您可以使用ORDER BY子句来指定视图中行的排序方式。然而，需要注意的是，当从该视图检索数据时，如果查询中也包含ORDER BY子句，那么视图中的排序将被覆盖，以查询中的排序为准。
   • 实际上，在视图中使用ORDER BY子句通常是不推荐的，因为视图的目的是提供一种数据访问的抽象层，而不是对数据进行排序。排序应该在最终查询中根据需要进行。
6. 视图与普通表一起使用：
   • 视图可以像普通表一样在查询中使用。您可以对视图进行单表查询，也可以将视图与其他表或视图进行连接（内连接、外连接等）。
   • 这使得视图成为一种非常强大的工具，可以简化复杂查询的编写和维护。通过创建视图来封装复杂的查询逻辑，您可以使数据库的使用更加直观和方便。