MySQL基础(十八)——视图

本篇文章主要是对MySQL学习时的一些总结，作为学习笔记记录。

数据来源

数据部分来自于b站尚硅谷MySQL课程

视图

• Mysql从5.0.1版本开始提供视图功能
• 视图是一种虚拟存在的表，行和列的数据来自定义视图的查询中是使用的表，并且在使用视图时动态生成，只保存了sql逻辑，不保存查询结果。

应用场景

• 多个地方使用到相同的查询结果
• 该查询结果使用的sql语句比较复杂

视图和表的对比

	创建关键字	是否占用实际物理空间	使用
视图	create view	只保存sql逻辑	增删改查，只是一般不能增删改
表	create table	保存数据	增删改查

创建视图

语法

CREATE VIEW viewname AS 
SELECT ... ;

示例

# 案例1.查询姓名中包含a字符的员工名、部门名和工种信息
# 创建视图
CREATE VIEW v1 AS 
SELECT 
  e.last_name,
  d.department_name,
  j.job_title 
FROM
  employees AS e 
  INNER JOIN departments AS d 
    ON e.department_id = d.department_id 
  INNER JOIN jobs AS j 
    ON e.job_id = j.job_id ;

# 使用视图
SELECT * FROM v1 WHERE last_name LIKE '%a%';


# 案例2.查询各部门的平均工资级别
# 创建视图
CREATE VIEW v2 AS 
SELECT 
  AVG(salary) AS ag,department_id
FROM
  employees 
GROUP BY department_id ;

# 使用视图
SELECT 
  v2.ag,
  j.grade_level 
FROM
  v2 
  INNER JOIN job_grades AS j 
    ON v2.ag BETWEEN j.`lowest_sal` 
    AND j.`highest_sal` ;



# 案例3.查询平均工资最低的部门信息
SELECT * FROM v2 ORDER BY ag ASC LIMIT 1;


# 案例4.查询平均工资最低的部门名和工资
# 创建视图
CREATE VIEW v3 AS 
SELECT 
  * 
FROM
  v2 
ORDER BY ag ASC 
LIMIT 1 ;


# 使用视图
SELECT 
  v3.ag,
  d.* 
FROM
  v3 
  INNER JOIN departments AS d 
    ON v3.`department_id` = d.`department_id` ;

修改视图

语法

CREATE OR REPLACE VIEW viewname AS 
SELECT ...... ;

或

ALTER VIEW viewname 
  AS 
  SELECT ...... ;

主要操作

CREATE OR REPLACE VIEW v3 AS 
SELECT 
  AVG(salary),
  job_id 
FROM
  employees 
GROUP BY job_id ;


ALTER VIEW v3 
  AS 
  SELECT 
    * 
  FROM
    employees ;

删除视图

DROP VIEW viewname1,viewname2,...... ;

查看视图

SHOW CREATE VIEW viewname;

更新视图

CREATE VIEW v1 AS SELECT id,NAME,sex FROM beauty;

#1.插入
INSERT INTO v1 VALUES(26,'花泽香菜','女');

#2.修改
UPDATE v1 SET NAME = '桥本环奈' WHERE id = 26;

#3.删除
DELETE FROM v1 WHERE id = 26;

但是具有以下特点的视图不允许更新：

• 包含有分组函数、distinct、group by、having、union或union all等关键字的sql语句

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

• 常量视图

CREATE OR REPLACE VIEW v1 AS 
SELECT 
  'abc' AS str ;

• select中含有子查询

CREATE OR REPLACE VIEW v1 AS 
SELECT 
  department_id,
  (SELECT 
    MAX(salary) 
  FROM
    employees) AS max_salary 
FROM
  departments ;

• join

CREATE OR REPLACE VIEW v1 AS 
SELECT 
  e.last_name,
  d.department_name 
FROM
  employees AS e 
  INNER JOIN departments AS d 
    ON e.department_id = d.department_id ;

• from一个不能更新的视图

CREATE OR REPLACE VIEW v1 AS 
SELECT 
  * 
FROM
  v2 ;

• where子句的子查询引用了from子句中的表

CREATE OR REPLACE VIEW v1 AS 
SELECT 
  last_name,
  email,
  salary 
FROM
  employees 
WHERE employee_id IN 
  (SELECT 
    manager_id 
  FROM
    employees 
  WHERE manager_id IS NOT NULL);