什么是视图?视图就是一张虚拟的表,视图只保存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;