【MySQL】视图

基本概念

介绍：

• 视图（view）是一个虚拟表，非真实存在，本质是根据 SQL 语句获取动态的数据集并命名，用户使用时只需用视图名称获取结果集，可当作表使用。
• 数据库中只存放视图的定义，不存放视图中的数据，数据存于原来的表中。
• 使用视图查询时，数据库从原表取数据，视图数据依赖原表，原表数据改变，视图数据也会改变。

作用：

• 简化代码，将重复使用的查询封装成视图重复用，让复杂查询更易理解和使用。
• 出于安全考虑，若表中部分数据不希望所有人看到，可用视图设定显示内容，针对不同用户设置不同视图，如社会保险基金表，通过视图只显示姓名、地址，不显示社会保险号和工资数等。

视图的操作

下面的视图操作基于下表：

代码示例：

-- 创建部门表
create table dept (deptno int,dname varchar(50),loc varchar(50)
);
-- 插入数据
insert into dept (deptno, dname, loc) values(10, '教研部', '北京'),(20, '学工部', '上海'),(30, '销售部', '广州'),(40, '财务部', '武汉');
-- 创建员工表
create table emp (empno int,ename varchar(50),job varchar(50),mgr int,hiredate date,sal decimal(10, 2),comm decimal(10, 2),deptno int
);
-- 插入数据
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values(1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, null, 20),(1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30),(1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30),(1004, '刘备', '经理', 1009, '2001-04-02', 29750.00, null, 20),(1005, '谢逊', '销售员', 1006, '2001-09-28', 12500.00, 14000.00, 30),(1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, null, 10),(1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, null, 10),(1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, null, 20),(1009, '曾阿牛', '董事长', null, '2001-11-17', 50000.00, null, 10),(1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30),(1011, '周泰', '文员', 1008, '2007-05-23', 11000.00, null, 20),(1012, '程普', '文员', 1006, '2001-12-03', 9500.00, null, 30),(1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, null, 20),(1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, null, 10);
-- 创建工资等级表
create table salgrade (grade int,losal decimal(10, 2),hisal decimal(10, 2)
);
-- 插入数据
insert into salgrade (grade, losal, hisal) values(1, 7000, 12000),(2, 12010, 14000),(3, 14010, 20000),(4, 20010, 30000),(5, 30010, 99990);

结果如下：

创建视图

语法格式：

create [or replace] [algorithm = {undefined|merge|temptable}]
view view_name [(column_list)]
as select_statement
[with [cascaded|local] check option];

参数说明：

• algorithm：可选项，表示视图选择的算法
• view_name：表示要创建的视图名称
• column_list：可选项，指定视图中各个属性的名词，默认情况下与 SELECT 语句中的查询的属性相同
• select_statement：表示一个完整的查询语句，将查询记录导入视图中
• with [cascaded|local] check option：可选项，表示更新视图时要保证在该视图的权限范围内

代码示例：

create or replace
view view_emp
as select ename,job from emp;

结果如下：

可以将视图理解成 C语言、C++、Java等高级程序语言中的函数或者方法，假如需要查上图中的信息，虽然也可以用 select ename,job from emp; ，但是每次都要写这个语句太繁琐了，就可以像C语言、C++、Java等高级程序语言中的函数或者方法那样，将该语句包装成视图，提高了复用性以及简化代码

查看表和视图：

代码示例：

show full tables;

结果如下：

修改视图

修改视图指修改数据库中已存在的表的定义，当基本表字段改变时，通过修改视图保持视图与基本表一致。MySQL 中通过 CREATE OR REPLACE VIEW 语句和 ALTER VIEW 语句修改视图

格式如下：

alter view 视图名 as select语句

代码示例：

alter view view_emp
as
select dept.deptno,dname,loc,ename,sal from dept, empwhere emp.deptno = dept.deptno;

结果如下：

更新视图

某些视图是可更新的，即能在 UPDATE、DELETE 或 INSERT 等语句中使用以更新基表内容，前提是视图中的行和基表中的行需具有一对一关系。若视图包含以下任意结构，则不可更新：

• 聚合函数（SUM ()、MIN ()、MAX ()、COUNT () 等）
• DISTINCT
• GROUP BY
• HAVING
• UNION 或 UNION ALL
• 位于选择列表中的子查询
• JOIN
• FROM 子句中的不可更新视图
• WHERE 子句中的子查询（引用 FROM 子句中的表）
• 仅引用文字值（无要更新的基本表）

此外，视图更新数据存在诸多限制。一般情况下，建议将视图作为查询数据的虚拟表，而非用于更新数据。因为使用视图更新数据时，若未全面考虑相关限制，可能导致数据更新失败。更新数据时最好直接操作原表

重命名视图

格式如下：

rename table 视图名 to 新视图名;

代码示例：

rename table view_emp to new_view_emp;

结果如下：

删除视图

格式如下：

drop view 视图名;

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

代码示例：

drop view new_view_emp;

结果如下：