Mysql 基础学习（十一）---视图学习

视图

含义： 虚拟表，和普通表一样使用。Mysql5.1 版本后出现的新特性。是通过表动态生成的数据。

	创建语法的关键字	是否实际占用物理空间	使用
视图	create view	只是保存了SQL逻辑	增删改查，一半不能增删改
表	create table	保存了数据	增删改查

应用场景：
多个地方使用的相同的SQL语句

案例1：查询姓张地学生名和专业
正常查询的时候

SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.majorid = m.id
WHERE s.stuname LIKE "张%";

使用视图进行查询

CREATE VIEW v1
AS 
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major ON s.majorid = m.id;

SELECT * FROM v1 stuname LIKE "张%";

创建视图：

语法：

CREATE VIEW 视图名
AS
查询语句

案例1： 查询邮箱中包含 a 字符的员工名，部门名和工种信息

CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employee e
JOIN deparments d ON e.deparment_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 * FROM myv2;

案例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 deparments d
ON m.department_id = d.department_id;

视图的好处

1. 重用SQL 语句
2. 简化复杂的SQL操作，不必知道他的查询细节
3. 保护数据，提高安全性

视图的修改

方式一：
语法

CREATE OR replace view 视图名字
AS
查询语句

方式二：
语法

ALTER VIEW myv3
AS
SELECT * FROM employees;

删除视图

语法

DROP VIEW 视图名，视图名。。。

查看视图

语法：

DESC myv3;

SHOW CREATE VIEW myv3;

视图的更新

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

# 1. 插入
INSERT INTO myv1 VALUES ('张飞','zf@qq.com');  # 插入到视图中的数据，也会插入到 employees 中
# 2. 修改
UPDATE myv1 SET last_name = '张无忌' WHERE last_name = '张飞';  # 修改myv1的同时也会修改employees
# 3. 删除
DELETE FROM myv1 WHERE last_name = '张无忌';

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

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

CREAETE OR REPLACE VIEW myv1
AS
SELECT MAX(salary), department_id
FROM employees
GROUP BY department_id

SELECT * FROM myv1;
# 更新报错
UPDATE myv1 SET m = 9000 WHERE department_id = 100;

2. 常量视图

CREATE OR REPLACE VIEW myv2
AS
SELECT 'join' NAME;

SELECT * FROM myv2;

# 更新报错
UPDATE myv2 SET NAME = 'lucy';

3. SELECT 中包含子查询

CREATE OR REPLACE VIEW myv3
AS
SELECT (SELECT MAX(salary) FROM employees) 最高工资;
# 更新报错
UPDATE myv3 SET 最高工资 = 300000;

4. 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;

# 更新成功
UPDATE myv4 SET last_name = '张飞' WHERE last_name = 'Whalan';
# 插入报错
INSERT INTO myv4 VALUES ('陈真'，'xxxx');

5. FROM 一个不能更新的视图

6. 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_king';