MySQL视图
一、含义:实际就是一张组合出来的虚拟表,和普通的表一样使用。
二、视图的使用
-
语法:
/* 语法: create view 视图名 as 查询语句; */ #1.查询姓名中包含a字符的员工名、部门名和工种信息 #①创建 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%';
-
视图的修改
#方式一: /* 语法: create or replace view 视图名 as 查询语句; */ CREATE OR REPLACE VIEW myv3 AS SELECT AVG(salary),job_id FROM employees GROUP BY job_id; #方式二: /* 语法: alter view 视图名 as 查询语句; */ ALTER VIEW myv3 AS SELECT * FROM employees; #三、删除视图 /* 语法:drop view 视图名,视图名,...; */ DROP VIEW emp_v1,emp_v2,myv3; #四、查看视图 DESC myv3; SHOW CREATE VIEW myv3; #五、视图的更新 #1.插入 INSERT INTO myv1 VALUES('张飞','zf@qq.com'); #2.修改 UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞'; #3.删除 DELETE FROM myv1 WHERE last_name = '张无忌'; #具备以下特点的视图不允许更新 #①包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all