1. 含义

虚拟的表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据
只保存了sql逻辑，不保存查询结果

2. 应用场景

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

3. 视图的创建和使用

查询部门名为IT的员工的相关信息

CREATE VIEW v1 AS 
SELECT 
  `employee_id`,
  `last_name`,
  d.`department_name`,
  `salary`,
  `commission_pct` 
FROM
  `employees` AS e 
  INNER JOIN `departments` AS d 
    ON e.`department_id` = d.`department_id` ;

SELECT 
  *
FROM
  v1 
WHERE `department_name` = 'IT' ;

查询姓名中包含a字符的员工名，部门名，和工种信息

CREATE VIEW myv1 AS 
SELECT 
  `last_name`,
  `department_name`,
  `job_title` 
FROM
  `departments` AS d 
  INNER JOIN `employees` AS e 
    ON d.`department_id` = e.`department_id` 
  INNER JOIN `jobs` AS j 
    ON e.`job_id` = j.`job_id` ;

SELECT 
  * 
FROM
  myv1 
WHERE last_name LIKE '%a%' ;

查询各部门的平均工资级别

CREATE VIEW myv2 AS 
SELECT 
  `department_id`,
  AVG(`salary`) AS avg_salary 
FROM
  `employees` 
GROUP BY `department_id` ;

SELECT 
  v2.`department_id`,
  `grade_level` 
FROM
  myv2 AS v2 
  INNER JOIN `job_grades` AS jg 
    ON v2.avg_salary BETWEEN `lowest_sal` 
    AND `highest_sal` ;

查询平均工资最低的部门信息

SELECT 
  * 
FROM
  `myv2` 
ORDER BY avg_salary 
LIMIT 1 ;

查询平均工资最低的部门名和工资

CREATE VIEW myv3 AS 
SELECT 
  * 
FROM
  `myv2` 
ORDER BY avg_salary 
LIMIT 1 ;

SELECT 
  d.* 
FROM
  `departments` AS d 
  INNER JOIN myv3 AS m 
    ON d.`department_id` = m.`department_id` ;

4. 视图的修改

方式一

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

方式二

ALTER VIEW myv3 
  AS 
  SELECT 
    * 
  FROM
    `employees` ;

5. 视图的更新

视图的增删改操作与表语法一样，但以下视图无法更新：

• 包含分组函数，distinct，group by，having，union或者union all关键字的sql语句
• 常量视图
• select中包含子查询
• join
• from一个不能更新的视图
• where子句的子查询引用了from子句中的表

6. 视图的其他操作

删除视图

DROP VIEW v1,
vi ;

查看视图

DESC `myv1` ;
-- 在sqlyog客户端效果不太详细
SHOW CREATE VIEW `myv1` ;

总结：

可以重用sql语句
简化复杂的sql操作，无需知道查询细节
保护数据，提高安全性