小徐带你了解mysql中视图的使用全网唯一详解

视图含义

视图时一个虚拟表,和普通表一样使用。

Mysql5.1版本出现的新特性,是通过表动态生成的数据

视图:

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

应用场景:
	一:多个地方用到同样的查询结构。
	二:该查询结果使用的sql语句较复杂
	
	示例:
	     create		view		my_v1
	     as
	     select		studentname,majorname
	     from students
	     inner join	major	m
	     on 
	     s.majorid=m.majorid
	     where s.majorid=1;

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

select	stuname,majorname
from students s
inner join major m
on s.majorid=m.id
where
s.studentname like '%张%';

create view v1
as
select stuname,majorname
from stuinfo s
inner join major m
on s.majorid=m.id;

select * from v1 where stuname like '%张%';

创建视图

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

案例:

查询姓名中包含e字符的员工名,部门名和工种信息

#创建
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%';

查询各部门的平均工资级别

#创建视图查看各部门的平均工资
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;

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

SELECT
	* 
FROM
	myv2 
ORDER BY
	ag 
	LIMIT 1;

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

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;

视图的好处

重写sql语句

简化复杂的sql操作,不必知道它的查询细节

保护数据,提高安全性。

视图的修改

方式一:

create	or		replace	view		视图名
as
查询语句;
SELECT
	* 
FROM
	myv3;
	
CREATE 
	OR REPLACE myv3 AS SELECT
	avg( salary ),
	job_id 
FROM
	employees 
GROUP BY
	job_id;

方式二:

语法:
alter	view		视图名
as
查询语句;
ALTER VIEW myv3 AS SELECT
* 
FROM
	employees 
WHERE
	last_name LIKE '%a%';

删除视图

语法:
drop	view	视图名,视图名,.......;
DROP VIEW myv1,
myv3;

查看视图

DESC myv2;
SHOW CREATE VIEW myv2;

创建视图的练习题

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

CREATE VIEW emp_v1 AS SELECT
last_name,
salary,
email 
FROM
	employees 
WHERE
	phone_number LIKE '011%';

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

CREATE VIEW emp_v2 AS SELECT
max( salary ) max_dep,
department_id 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	max( salary )> 12000;

SELECT
	d.*,
	m.max_dep 
FROM
	departments d
	JOIN emp_v2 m ON m.department_id = d.department_id;

视图的更新

CREATE 
	OR REPLACE VIEW myv1 AS SELECT
	last_name,
	email,
	salary * 12 *(
		1+IFNULL ( commission_pct, 0 )) annual_salary 
FROM
	employees;

select * from myv1;

插入:

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

修改

update	myv1 set last_name='刘备' where last_name='张飞';

删除

delete	from myv1 where last_name='刘备';

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

一:包含一下关键字的sql语句:分组函数,distinct,group by,having,union,或者union all
	CREATE 
    OR REPLACE VIEW myv1 AS SELECT
    max( salary ) m,
 	department_id 
 	employees 
    GROUP BY
    department_id;
	
	select * from myv1;
	update myv1 set m=4000 where department_id=10;    更新失败,不允许更新 分组函数
	
二:常量视图
	create or replace view myv2
	as
	select 'john' name;
	select * from myv2;

	update myv2 set name 'liubei';  #常量视图不允许更新

三:select中包含子查询

	create or replace view myv3
	as
	select (select max(salary) from employees) 最高工资;

	select * from myv3;

	update myv3 set 最高工资=100000;    #select中包含子查询的不允许更新

四:join
	
		create or replace view myv4
		as
		select last_name,department_name
		from employees e
		join departments d
		on e.department_id=d.department_id;

		select * from myv4;

		update myv4 set last_name='张飞' where last_name='Whalen';
         insert into myv4 values('程真','xxxx');      #不允许插入
		
五:from一个不能更新的视图
	
		create or replace view myv5
		as
		select * from myv3;

		update myv5 set 最高工资=100000 where department_id=60;

六:	where子句的子查询引用了from子句中的表

		create or replace view myv6
		as
		select last_name,email,salary
		from employees
		where employee_id in(
		select manager_id
		from employees
		where manager_id is not null);

		update myv6 set salary = 10000 where last_name='k_ing';

视图和表的对比

在这里插入图片描述
delete和truncate在事务使用时的区别
在这里插入图片描述

练习题

创建表book

CREATE TABLE book (
	bid INT PRIMARY KEY,
	bname VARCHAR ( 20 ) UNIQUE NOT NULL,
	price FLOAT DEFAULT 10,
	btypeId INT,
	FOREIGN KEY ( btypeId ) REFERENCES bookType ( id ) 
);

开启事务向表中插入一行数据并结束

SET autocommit = 0;
SAVEPOINT a;
INSERT INTO book ( bid, bname, price, btypeId )
VALUES
	( 1, '钢铁是怎样练成的', 100, 1 );
COMMIT;

创建视图,实现查询价格大于100的书名和书类型

CREATE 
	OR REPLACE VIEW myvi AS SELECT
	bname,
NAME 
FROM
	book b
	JOIN bookType t ON b.btypeId = t.id 
WHERE
	price > 100;

修改视图,实现查询价格在90-120之间的书名和价格

CREATE 
	OR REPLACE VIEW myvi AS SELECT
	bname,
	price 
FROM
	book 
WHERE
	price BETWEEN 90 
	AND 120;

删除视图

DROP VIEW myvi;
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值