MySQL之视图

1、含义

含义：

①虚拟表，和普通表一样使用

②视图create view只是保存了sql逻辑增删改查，只是一般不能增 删改

③表create table保存了数据增删改查

案例：

查询姓张的学生名和专业名 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 m ON s.majorid= m.id;

SELECT * FROM v1 WHERE stuname LIKE '张%';

二、创建视图

1、语法：

create view 视图名
as
查询语句;

①创建

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

三、视图的修改

1、方式一：

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

例题：

CREATE OR REPLACE VIEW v_zxy AS SELECT AVG(age),tid FROM emp GROUP BY tid;

 2、方式二：

语法：alter view 视图名 as 查询语句;

例题：

 ALTER VIEW v_zxy AS SELECT * FROM emp;

 三、删除视图

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

例题：

DROP VIEW v_zxy ,v_z1,v_z2;

四、查看视图

语法：DESC 视图名;

SHOW CREATE VIEW v_zxy;

五、视图的更新

语法：

CREATE OR REPLACE VIEW 视图名

AS

SELECT 列名,列名 FROM 表名;

 1.插入

INSERT INTO myv1 VALUES('张飞','zf@qq.com');

2、修改

UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';

3、删除

DELETE FROM myv1 WHERE last_name = '张无忌';

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

①包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all

例如：

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

SELECT * FROM myv1;

更新语法

UPDATE myv1 SET m=9000 WHERE department_id=10;

 ②常量视图

CREATE OR REPLACE VIEW myv2 AS

SELECT 'john' NAME;

更新语法

UPDATE myv2 SET NAME='lucy';

 ③Select中包含子查询

CREATE OR REPLACE VIEW myv3 AS

SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资 FROM departments;

更新语法

SELECT * FROM myv3; UPDATE myv3 SET 最高工资=100000;

 ④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;

更新语法

SELECT * FROM myv4; UPDATE myv4 SET last_name = '张飞' WHERE last_name='Whalen'; INSERT INTO myv4 VALUES('陈真','xxxx');

 ⑤from一个不能更新的视图

CREATE OR REPLACE VIEW myv5 AS

SELECT * FROM myv3;

更新语法

SELECT * FROM myv5;

UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;

 ⑥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 );

更新语法

SELECT * FROM myv6; UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';