MySQL之视图

含义: 虚拟表,和普通表一样使用,mysql5.1版本出现的新特性,是通过表动态生成的数据 比如: 舞蹈班和普通班级的对比 优点:

重用sql语句简化复杂的sql操作，不必知道它的查询细节保护数据，提高安全性

视图和表的对比:

创建语法是否实际占用物理空间使用视图create view没有,只是保存了sql逻辑增删改查,一般不能增删改表create table占用,保存了数据增删改查

案例:查询姓张的学生名和专业名

use students; drop table if exists stuinfo; create table if not exists stuinfo( id int, stuname varchar(20), gender char(1), age int default 18, seat int, majorid int ) default charset=utf8; drop table if exists major; create table major( id int, majorName varchar(20) ); select stuname,majorname from stuinfo s inner join major m on s.majorid=m.id where s.stuname like '张%'; create view myv1 as select stuname,majorname from stuinfo s inner join major m on s.majorid=m.id; select * from myv1 where stuname like '张%';

一.创建视图

语法: create view 视图名 as 查询语句;

use myemployees;

1.查询姓名中包含a字符的员工名、部门名和工种信息 ①创建

create view myv1 as select last_name,department_name,job_title from employees e join departments d on e.department_id = d.department_id join jobs j on j.job_id=e.job_id;

②使用

select * from myv1 where last_name like '%a%';

2.查询各部门的平均工资级别 ①创建视图查看每个部门的平均工资

create view myv2 as select avg(salary) ag,department_id from employees group by department_id;

②使用

select myv2.ag,g.grade_level from myv2 join job_grades g on myv2.ag between g.lowest_sal and g.highest_sal;

3.查询平均工资最低的部门信息

select * from myv2 order by ag limit 1;

4.查询平均工资最低的部门名和工资

create view myv3 as select * from myv2 order by ag limit 1; select d.*,m.ag from myv3 m join departments d on m.department_id=d.department_id;

二、视图的修改

方式一：

create or replace view 视图名 as 查询语句; use myemployees; select * from myv3; create or replace view myv3 as select avg(salary),job_id from employees group by job_id;

方式二:

alter view 视图名 as 查询语句; alter view myv3 as select * from employees; select * from myv3;

三.删除视图

drop view 视图名,视图名,...;

drop view myv1,myv2,myv3;

四.查看视图

desc myv3; show create view myv3;

五.视图的更新(可以更新原始表)

create or replace view myv1 as select last_name,email from employees; select * from myv1;

1.插入

insert into myv1 values('张飞','zf@qq.com');

2.修改

set SQL_SAFE_UPDATES = 0; update myv1 set last_name ='张无忌' where last_name='张飞';

3.删除

delete from myv1 where last_name='张无忌';

具备以下特点的视图不允许更新

包含以下关键字的sql语句： 分组函数、distinct、group by、having、union或者union all常量视图Select中包含子查询joinfrom一个不能更新的视图where子句的子查询引用了from子句中的表 drop view myv1;

①group by

create or replace view myv1 as select max(salary) m,department_id from employees group by department_id; select * from myv1;

更新

update myv1 set m=9000 where department_id=10; # 失败error1288

②常量视图

create or replace view myv2 as select 'john' name; select * from myv2;

更新

update myv2 set name='lucy'; # 失败error1288

③Select中包含子查询

create or replace view myv3 as select (select max(salary) from employees) 最高工资; ```s 更新 ```sql select * from myv3; update myv3 set 最高工资=10000; # 失败error1288

练习题

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

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

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

create or replace view emp_v2 as select max(salary) mx_dep,department_id from employees group by department_id having max(salary)>12000; select d.*,emp_v2.mx_dep from departments d join emp_v2 on emp_v2.department_id=d.department_id;