mysql中的视图表

视图（View）是数据库中的一种对象，它是基于 SQL 语句的结果集的可视化的表。视图包含行和列，类似于一个真实的表，但它并不在数据库中以存储的数据值集形式存在。视图的内容由查询定义，可以来自单个表或多个表，甚至可以来自其他视图。视图的创建和删除只影响视图本身，不影响对应的基表，但是对视图中的数据进行增加、删除和修改操作时，会相应地影响基表中的数据。

1. 视图表的特点

• 视图是数据的特定子集，是从其他表里提取出数据而形成的虚拟表，也可以说是临时表。其内容由查询定义，不占用物理存储空间。
• 创建视图表需依赖一个查询。
• 视图是不会自己消失的，一定要手动删除。
• 视图有时对提高效率有帮助，临时表不会对性能有帮助，是资源的消耗者。
• 视图一般随该数据库存放在一起，临时表永远都是在tempdb里生成。
• 视图适合多表连接浏览时使用，不适合增删改，这样可以提高执行效率。
• 视图表的名称一般以v-为前缀，可以与正常表进行区分。
• 对原表的修改会影响到视图中的数据，对视图表的修改也会影响到原表的数据，原表和视图表是联动的。
• 可以简化代码，可以把重复使用的查询封装成视图重复使用，同时可以使复杂的查询易于理解和使用。
• 安全，如果一张表中有很多列数据，一些列的信息不希望让所有人看到，此时可以使用视图表，如员工信息表，可以用视图只显示姓名、id，而不显示工资和奖金，可以对不同的用户设定不同的视图。

2. 视图表的创建和删除语法

• 创建

create [or replace] view view_name as
select column_name(s)
from table_name
where condtion

• 示例：

  – 创建数据库demo4

  create database demo4;
  use demo4;
  

  – 创建部门表，并插入数据

  create table dept(dno varchar(8) primary key,dname varchar(8),local varchar(8)
  )charset=utf8;insert into dept values(10, '研发部', '北京'),(20,'生产部','上海'),(30,'销售部','广州'),(40,'财务部','武汉');
  

  – 创建员工表，并插入数据

  create table emp(eno varchar(8) primary key,ename varchar(8),job varchar(16),mgr_id varchar(8),hiredate date,salary double,bonus double,e_dno varchar(8),foreign key (e_dno) references dept (dno)
  )charset=utf8;insert into emp values('1001','阿紫','文员','1013','2000-12-17',8000,null,'20'),
  ('1002','阿朱','销售员','1006','2001-02-20',16000,3000,'30'),
  ('1003','段誉','销售员','1006','2001-02-22',12500,5000,'30'),
  ('1004','乔峰','经理','1009','2001-04-02',29750,null,'20'),
  ('1005','诸葛亮','销售员','1006','2001-09-28',12500,14000,'30'),
  ('1006','洪七公','经理','1009','2001-05-01',28500,null,'30'),
  ('1007','黄药师','经理','1009','2001-09-01',24500,null,'10'),
  ('1008','黄蓉','分析师','1004','2007-04-19',30000,null,'20'),
  ('1009','成吉思汗','董事长',null,'2001-11-17',50000,null,'10'),
  ('1010','周瑜','销售员','1006','2001-09-08',15000,0,'30'),
  ('1011','黄盖','文员','1008','2007-05-23',11000,null,'20'),
  ('1012','黄忠','文员','1006','2001-12-03',9500,null,'30'),
  ('1013','鲁肃','分析师','1004','2001-12-03',30000,null,'20'),
  ('1014','蒋干','文员','1007','2002-01-23',13000,null,'10');
  

  – 创建工资等级表并插入数据

  create table grade(grade int(8),l_salary double,h_salary double
  )charset=utf8;insert into grade values(1,7000,12000),(1,12010,14000),
  (3,14010,20000),(4,20010,30000),(5,30010,99990);
  

  – 创建视图

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

  – 查看视图表

  show full tables; -- 比show tables多了一列table_type
  

– 查看视图的数据(可以将视图表当成表来使用)

select * from v1_emp;

3. 视图表的修改

• 语法

  alter view view_name as select语句
  

• 示例

  alter view v1_emp
  as
  select a.dno, a.dname, a.local, b.ename, b.salary from dept as a, emp as b where a.dno = b.e_dno;select * from v1_emp;
  

4. 视图表的更新

• 某些视图表可以使用update、delete、insert等语句更新，实际上更新的是基表的内容。这些视图表中的行和基表中的行之间必须是一对一的关系

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

update v2_emp set ename = '陆逊' where ename = '鲁肃'; --实际上更新的是基表
select * from v2_emp;
select * from emp;

• 如果视图表中包含以下任何一种情况，那么它就不能更新：

  1、视图表是否能被修改，依赖于创建视图表时的算法值ALGORITHM，ALGORITHM有3个可选值：undfined，merge，temptable

  • undfined：默认值，表示视图表允许被修改

  • merge：视图表也允许被修改

  • temptable：视图表不允许被修改

  – 创建视图表时可以指定算法：

create algorithm=temptable view view_name as
select column_name(s)
from table_name
where condtion
-- 此时创建的视图表不能被修改

​ 2、select语句中包含聚合函数（sum()、min()、max()、count()等）

create or replace view v3_emp
as
select count(*) as ct from emp;insert into v3_emp values(100); --报错
update v3_emp set cnt = 100; --报错

3、select语句中包含distinct关键字的

create or replace view v4_emp
as
select distinct job from emp;

4、select语句中包含group by

5、select语句中包含having

6、select语句中包含union或union all

7、select语句中包含子查询

create or replace view v8_emp
as
select eno, ename, salary from emp where salary = (select max(salary) from emp);

8、select语句中包含join

9、from子句中的不可更新视图

10、select语句中包含常量文字值

create or replace view v11_emp
as
select '销售部' dname, '黄蓉' ename;

5、视图表的重命名和删除

• 重命名视图语法

  rename table view_name to new_view_name;
  

• 删除视图：删除视图时只能删除视图的定义，不会删除基表数据

  drop view [if exists] view_name1 [,view_name2...];
  

6、视图表的练习

— 在demo4中查询部门平均薪水最高的部门名称

-- 使用嵌套查询方式
select *
from dept as a, (select *from(select *, rank() over(order by avg_salary desc) rn from(select e_dno, avg(salary) as avg_salary from emp group by e_dno) as t1) as t2where rn = 1) as t3
where a.dno = t3.e_dno; -- 使用视图表方式
create view v_avg_salary 
as
select e_dno, avg(salary) as avg_salary from emp group by e_dno;create view v_rn
as
select *, rank() over(order by avg_salary desc) as rn from v_avg_salary;create view v_rn1
as
select * from v_rn where rn = 1;select * from dept, v_rn1 where dno = e_dno;

– 查询员工比所属领导薪资高的部门名，员工姓名，员工领导编号

--子查询方式
select * from(select a.eno, a.ename, a.salary as a_salary, a.mgr_id, b.salary as b_salary fromemp a join emp b on a.mgr_id = b.eno) t where t.a_salary > t.b_salary;-- 视图表方式
create view v1
as
select a.eno, a.ename, a.salary a_salary, a.mgr_id, b.salary b_salary fromemp a join emp b on a.mgr_id = b.eno;select * from v1 where a_salary > b_salary;

– 查询工资等级为4级，2000年以后入职的工作地点在北京的员工编号，姓名和工资，并查询出薪资在前三名的员工信息

create view v_grade as
select * from dept d join emp e on e.e_dno = d.dno and hiredate > 2000-12-31 and d.local = '上海'join grade g on grade = 4 and (e.salary between g.l_salary and g.h_salary);select *, rank() over(order by salary) as rn from v_grade t where t.rn <= 3;