视图
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'