MySQL 视图

视图

employees 表

departments 表

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

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

如何创建视图

语法:

create view 视图名
as 查询语句;

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

①创建

CREATE VIEW myview
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  myview WHERE last_name LIKE '%a%';

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

①创建视图

CREATE VIEW my_salary_level
AS
SELECT AVG(salary) ag,department_id 
FROM employees e GROUP BY department_id; 

②使用

SELECT my_salary_level.`ag`,g.grade_level 
FROM my_salary_level  
JOIN  job_grades g
ON my_salary_level.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

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

SELECT * FROM my_salary_level ORDER BY 	ag LIMIT 1;

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

①创建视图

CREATE VIEW myview3
AS
SELECT * FROM my_salary_level ORDER BY 	ag LIMIT 1;

②使用

SELECT d.`department_name`,m.`ag` FROM myview3 m JOIN departments d 
ON m.`department_id`=d.`department_id`

二、视图的修改

方式一

create or replace view 视图名
as
查询语句;

例如：

CREATE OR REPLACE VIEW myview3
AS
SELECT AVG(salary),job_id 
FROM employees 
GROUP BY job_id;

方式二
语法：

alter view 视图名
as
查询语句;

例如：

ALTER VIEW myview3
AS
SELECT * FROM employees;

SELECT * FROM myview3;

三、删除视图

语法：

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

例如：

DROP VIEW myview3;

四、查看视图
方式一

DESC myview3;

方式二

SHOW CREATE VIEW myview3;

五、视图的更新

CREATE OR REPLACE VIEW myemp
AS
SELECT last_name,email,salary FROM employees ;

查询视图

SELECT * FROM myemp;

查询原表

SELECT * FROM employees;

1.插入

INSERT INTO myemp VALUES('李春芳','2281876565@qq.com',58000);

2.修改

UPDATE myemp SET last_name ='张学林' WHERE last_name ='李春芳';

3.删除

DELETE FROM myemp WHERE last_name='张学林';

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

①包含以下关键字的SQL语句：分组函数、DISTINC、GROUP BY 、HAVING、UNION 、UNION ALL

CREATE OR REPLACE VIEW myview1
AS
SELECT MAX(salary) max_salary,department_id
FROM employees 
GROUP BY department_id;

更新报错：不允许更新 The target table myview1 of the UPDATE is not updatable

UPDATE myview1 SET max_salary = 9200 WHERE department_id =10;

②常量视图

CREATE OR REPLACE VIEW myview2
AS
SELECT 'RayLine' NAME;

更新 不允许更新 The target table myview2 of the UPDATE is not updatable

UPDATE myview2 SET NAME='125';

③select 中包含子查询

CREATE OR REPLACE VIEW myview3
AS
SELECT (SELECT MAX(salary) FROM  employees) 最高工资;

更新 不允许更新 The target table myview3 of the UPDATE is not updatable

UPDATE myview3 SET 最高工资=5200;

④join

CREATE OR REPLACE VIEW myview4
AS
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

更新 可以更新

UPDATE myview4 SET last_name ='李如意' WHERE last_name='Chen'

不能插入

INSERT INTO myview4 VALUE ('严嵩亮','ling');

⑤from 一个不能更新的视图

CREATE OR REPLACE VIEW myview5
AS
SELECT  * FROM myview5;

更新

UPDATE  myview5 SET 最高工资=15200 WHERE department_id = 10;

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

CREATE OR REPLACE VIEW myview6
AS 
SELECT last_name,email,salary 
FROM employees 
WHERE employee_id IN(
	SELECT manager_id
	FROM employees
	WHERE manager_id IS NOT NULL 
);

更新 不允许更新 The target table myview6 of the UPDATE is not updatable

UPDATE myview6 SET salary =5200 WHERE last_name='Vollman'