SQL学习笔记系列（六）视图的讲解

视图的介绍

定义

理解成一张虚拟的表(和普通表一样使用)
是通过表动态生成的数据，只保存了sql逻辑，不保存查询结果。

#案例：查询姓张的同学的学生名和专业名
CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo a
INNER JOIN major m ON s.'majorid'=m.'id'
WHERE s.'stuname' LIKE '张%';

SELECT *
FROM v1
WHERE stuname LIKE '张%';

视图和表的区别

	    使用方式	    占用物理空间

视图	完全相同	不占用，仅仅保存的是sql逻辑

表	    完全相同	占用

视图的好处

1、sql语句提高重用性，效率高
2、和表实现了分离，提高了安全性

视图的创建

#语法
CREATE VIEW  视图名
AS
查询语句;#简单语句不需要视图

#案例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：查询平均工资最低的部门信息
CREATE VIEW myv2
AS
SELECT AVG(salary) ag ,department_id
FROM employees
GROUP BY department_id;


SELECT *
FROM departments
WHERE department_id=(
	SELECT department_id
	FROM myv2
	GROUP 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 d.`department_id`=m.`department_id`;

视图的修改

语法

#方式一
create or replace view 视图名
as
查询语句；

#案例一：修改myv3
create or replace view myv3
as
select avg(salary),job_id
from employees
group by job_id;

#方式二
alter view 视图名
as
查询语句；

#案例2：修改myv3
ALTER VIEW myv3
AS
SELECT *
FROM employees;

删除视图

语法

DROP VIEW 视图名，视图名，...;

#案例1
DROP VIEW myv1,myv2,myv3;

查看视图

DESC myv3;
SHOW CREATE VIEW test_v7;

案例讲解

#案例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_v2,要求查询部门的最高工资高于12000的部门信息。
CREATE VIEW emp_v2
AS
SELECT MAX(salary) mx_dep,department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000;


SELECT d.*,m.`mx_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
FROM employees;

INSERT INTO myv1 VALUES('张飞','zf@qq.com');

SELECT *
FROM myv1;#也会对原始表进行更新

修改

UPDATE myv1 SET last_name='张无忌'
WHERE last_name='张飞';
#修改视图对应的基表引用字段集

删除

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

某些视图不能更新

1.包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union all

2.常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;

UPDATE myv2
SET NAME='lucy';

3.Select中包含子查询

4.join（连接的都算）
 
5.from一个不能更新的视图

6.where子句的子查询引用了from子句中的表

视图和表的对比

1.视图

• 创建语法的关键字：create view
• 是否实际占用物理空间：否（但保存了逻辑）
• 使用：增删改查，一般不能增删改

2.表

• 创建语法的关键字：create table
• 是否实际占用物理空间：是
• 使用：增删改查