mysql 视图

视图

1、什么是视图

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

2、视图应用场景

多个地方用到同样的查询结果该查询结果使用的sql语句较复杂

4、视图的好处

使用简单安全数据独立

5、创建或者修改视图

创建视图的语法：

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

修改视图的语法

alter view view_nameAs select_statement [with|cascaded|local|check option]

6、视图更新性

视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的。（1）包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all（2）常量视图（3）Select中包含子查询（4）join（5）from一个不能更新的视图（6）where子句的子查询引用了from子句中的表

7、删除视图的语法

用户可以一次删除一个或者多个视图，前提是必须有该视图的drop权限。

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

8、查看视图结构的语法

show tables;如果需要查询某个视图的定义，可以使用show create view命令进行查看

#视图

#创建或者修改视图

create view myview1

AS

SELECT * from beauty

where id > 4

select * from myview1

#二、使用视图

#1.查询

select * from myview1

#2.插入

insert into myview1 values(16,'wod','男',now(),12312121,null,null)

#3.更新

update myview1 set name = 'girl'

where id = 13

#4 .删除

DELETE from myview1 WHERE id =16

#① 关键字

create view myview2

AS

SELECT count(*),boyfriend_id

from beauty

group by boyfriend_id

#③select中包含子查询

create view myview3

AS

select (

SELECT max(boyfriend_id)

from beauty

) a;

SELECT * from myview3

create view myview4

AS

SELECT b.`name`,b.sex,o.boyName,b.phone from beauty b

INNER JOIN boys o on b.boyfriend_id = o.id

where o.boyName = '黄晓明'

SELECT * from myview4

drop view if exists myview2

#五、查看视图结构

desc myview1

show create view myview1

use myemployees

#视图实操练习

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

create or replace view myv1

as

select last_name,salary,email from employees

where phone_number like'011%'

SELECT * from myv1

#三、向emp_v1插入一条记录，是否可以？

#可以

insert into myv1 values('bod',12222,'wdwdw')

#四、修改刚才记录中的电话号码为‘119’

update myv1 set phone_number = 119

where last_name = 'bod'

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

SELECT max(salary),department_id from employees

where department_id is not null

GROUP BY department_id

SELECT department_id from (

SELECT max(salary) m,department_id from employees

where department_id is not null

GROUP BY department_id)a

where a.m > 12000

SELECT * from departments

where department_id in (

  SELECT department_id from (

  SELECT max(salary) m,department_id from employees

  where department_id is not null

  GROUP BY department_id)a

where a.m > 12000

)

create view myv2

AS

SELECT * from departments

where department_id in (

  SELECT department_id from (

  SELECT max(salary) m,department_id from employees

  where department_id is not null

  GROUP BY department_id)a

  where a.m > 12000

) # 这样不行

create view myv2

AS

SELECT * from departments

where department_id in(

SELECT department_id from employees

group by department_id

having max(salary)>12000

)

**