1、含义
含义:
①虚拟表,和普通表一样使用
②视图create view只是保存了sql逻辑增删改查,只是一般不能增 删改
③表create table保存了数据增删改查
案例:
查询姓c的学生名和专业名
SELECT stuname,majorname FROM stuinfo s INNER JOIN major m ON s.majorid= m.id WHERE s.stuname LIKE 'c%';
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 'c%';
二、创建视图
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_csf 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_csf ,v_c1,v_c2;
四、查看视图
语法:DESC 视图名;
DESC v_csf;
五、视图的更新
语法:
CREATE OR REPLACE VIEW 视图名
AS
SELECT 列名,列名 FROM 表名;
1.插入
INSERT INTO myv1 VALUES('张三','zs@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='ccc';
INSERT INTO myv4 VALUES('陈真','xxxx');
⑥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';