[大数据]mysql之视图

一、创建视图

语法：

create view 视图名

as

查询语句;

USE myemployees;

视图的优势：

在建立连多表查询语句时，很容易有大量重复代码，创建视图可以有效避免重复代码，使用起来非常便捷，看起来不乱，很舒适

#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

查询语句;

三、删除视图

?

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

?

DROP VIEW emp_v1,emp_v2,myv3;

?

#四、查看视图

DESC myv3;

SHOW CREATE VIEW myv3;

#五、视图的更新

CREATE OR REPLACE VIEW myv1

AS

SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"

FROM employees;

?

CREATE OR REPLACE VIEW myv1

AS

SELECT last_name,email

FROM employees;

#1.插入

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

#2.修改

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

#3.删除

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