Mysql之视图

什么是视图？视图就是一张虚拟的表，视图只保存sql的逻辑，并不保存具体数据，数据是动态生成的

 

为什么要使用视图？1.可以对sql进行一个复用 2.简化了复杂的sql查询 3.提高了数据的安全性

 语法：

CREATE OR REPLACE VIEW 视图名

AS

SELECT语句;

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

CREATE OR REPLACE VIEW v1
AS
SELECT studentname,majorname FROM student s JOIN major m ON s.majorid=m.majorid;
SELECT * FROM v1 WHERE studentname LIKE '张%';

练习：

 

 

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

CREATE OR REPLACE VIEW v2
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 e.job_id = j.job_id WHERE email LIKE '%a%';
SELECT * FROM v2;

 

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

CREATE OR REPLACE VIEW v3
AS
SELECT m.ag_sal,grade_level FROM (SELECT AVG(salary) ag_sal,department_id FROM employees GROUP BY department_id) m,job_grades jg WHERE m.ag_sal BETWEEN lowest_sal AND highest_sal; 
SELECT * FROM v3;

  

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

CREATE OR REPLACE VIEW v4
AS
SELECT d.* FROM departments d WHERE department_id = (SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1);
SELECT * FROM v4;

  

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

CREATE OR REPLACE VIEW v5
AS
SELECT salary,department_name FROM employees e JOIN departments d ON e.department_id=d.department_id 
WHERE  d.department_id =(SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1);
SELECT * FROM v5;

修改视图： 

 

删除视图： 

语法：drop view 视图名,视图名,...;

DROP VIEW v1,v2,v3,v4,v5;

视图的更新：对视图中的数据进行一个增删改统称为更新

CREATE OR REPLACE VIEW v1
AS
SELECT last_name,salary FROM employees;
UPDATE v1 SET salary=24999 WHERE last_name='K_ing';

 对视图的数据的更新，原表中的数据也会进行一个更新，数据不安全，一般视图设置权限为只读

INSERT INTO v1 VALUES('张飞',9999);

 

DELETE FROM v1 WHERE last_name='张飞';

以下情况视图不能更新：

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

CREATE OR REPLACE VIEW v2
AS
SELECT AVG(salary) ag_sal,department_id FROM employees GROUP BY department_id;
SELECT * FROM v2;

 

UPDATE v2 SET  ag_sal=7999 WHERE department_id=10;

 

 2.常量视图

CREATE OR REPLACE VIEW v3
AS
SELECT 'John' NAME;
SELECT * FROM v3;

UPDATE v3 SET NAME='hhhh' WHERE NAME='John';

 

 3.select中包含子查询

CREATE OR REPLACE VIEW v4
AS
SELECT (SELECT last_name FROM employees WHERE employee_id=101) NAME;
SELECT * FROM v4;

UPDATE v4 SET NAME='Kevin' WHERE NAME='Kochhar';

 

 4.join

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

UPDATE v5 SET last_name='Lilei' WHERE employee_id = 200;

DELETE FROM v5 WHERE employee_id = 200;

 

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

CREATE OR REPLACE VIEW v6
AS
SELECT * FROM v2;
SELECT * FROM v6;

 

UPDATE v6 SET ag_sal = 7999 WHERE department_id=10;

 

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

CREATE OR REPLACE VIEW  v7
AS
SELECT d.department_name,d.department_id FROM departments d,(SELECT e.department_id FROM employees e GROUP BY department_id ORDER BY AVG(salary) DESC LIMIT 1) m
WHERE d.department_id = m.department_id;
SELECT * FROM v7;

INSERT INTO v7 VALUES('eee',91); 

 

 

 视图和表的对比：

                        创建语法的关键字              是否实际占用了物理地址               使用

视图                   create view                        保存了sql的执行逻辑              增删改查，只限定查

表                       create table                       保存了真实的数据                    增删改查

 delete在事务中可以回滚，但是truncate在事务中不能回滚

SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;

 

SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;