MySQL数据库视图概念

MySQL数据库视图概念

MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。

创建视图

创建视图的语法：

create [or replace] view view_name
as select_statement
[with|cascaded|local|check option]

修改视图

修改视图的语法：

alter view view_name
As select_statement
 [with|cascaded|local|check option]

删除视图

删除视图的语法：

drop view [if exists]  view_name,view_name …[restrict|cascade]

下面用几个例子演示一下：

1. 创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱。

create or replace view  emp_v1
as
select last_name,salary,email
from employees
where phone_number like '011%';

2. 要求将视图emp_v1修改为查询电话号码以‘011’开头的并且邮箱中包含e字符的员工姓名和邮箱、电话号码。

create or replace  view emp_v1
as
select last_name,email,phone_number
from employees
where phone_number like '011%' and email like '%e%';
select * from employees;

3. 向emp_v1插入一条记录

insert into emp_v1 values('john','sdfe','0112345');

4. 修改刚才记录中的电话号码为‘119’

update emp_v1 set phone_number ='119' where last_name='john';

5. 删除刚才记录

delete from emp_v1 where last_name='';

6. 创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息

select *
from departments 
where department_id in(
	select department_id
	from employees
	group by department_id
	having max(salary)>12000

) 

create or replace view emp_v2
as
	select *
	from departments
	where department_id in(
		select department_id
		from employees
		group by department_id
		having max(salary)>12000

) 
select * from emp_v2;

7. 删除刚才的emp_v2和emp_v1

drop view emp_v2,emp_v1;