图源：
简单的说，视图就是“固化的SQL查询”。
这里看一个简单示例，我们有一个表，保存学生信息：
mysql> select * from student limit 10; +-----+---------------+-----------+----------+ | id | average_score | level | name | +-----+---------------+-----------+----------+ | 573 | 27 | FRESH_MAN | Tom | | 574 | 76 | JUNIOR | Icexmoon | | 575 | 2 | JUNIOR | BrusLee | | 576 | 56 | SOPHOMORE | Harry | | 577 | 44 | JUNIOR | JackChen | | 578 | 96 | JUNIOR | Jimmy | | 579 | 73 | JUNIOR | LiLei | | 580 | 4 | SENIOR | XiaoMing | | 581 | 21 | SOPHOMORE | Adam | | 582 | 40 | JUNIOR | Alex | +-----+---------------+-----------+----------+
level字段表示年级，如果我们需要查询大一的学生：
mysql> select * from student where level='FRESH_MAN'; +-----+---------------+-----------+--------+ | id | average_score | level | name | +-----+---------------+-----------+--------+ | 573 | 27 | FRESH_MAN | Tom | | 586 | 61 | FRESH_MAN | Blake | | 591 | 6 | FRESH_MAN | Bruce | | 599 | 90 | FRESH_MAN | Xiaoli | +-----+---------------+-----------+--------+
假设我们还需要统计大一学生的总平均分：
mysql> select sum(average_score)/count(id) as sum_average_score, sum(average_score) as total_average_score from student where level='FRESH_MAN'; +-------------------+---------------------+ | sum_average_score | total_average_score | +-------------------+---------------------+ | 46.0000 | 184 | +-------------------+---------------------+
如果要频繁编写类似的查询语句，查询某某年级的学生的总平均分就很麻烦。
我们可以换个思路，能否创建一个“固化的查询结果”，内容是每个年级的统计结果，这样我们的查询语句就很简单了，只要针对这个统计结果按照年级查询即可。
首先我们看怎么分组统计不同年级的均分：
mysql> select `level`,count(id) as num,sum(average_score) as total_score, sum(average_score)/count(id) as sum_average_score -> from student -> group by `level`; +-----------+-----+-------------+-------------------+ | level | num | total_score | sum_average_score | +-----------+-----+-------------+-------------------+ | FRESH_MAN | 4 | 184 | 46.0000 | | JUNIOR | 11 | 526 | 47.8182 | | SOPHOMORE | 6 | 328 | 54.6667 | | SENIOR | 6 | 190 | 31.6667 | +-----------+-----+-------------+-------------------+
创建视图
用 SQLyog 创建视图会出现类似下面的模版 SQL：
CREATE
/*[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]*/
VIEW `jpa`.`student_stat_view`
AS
(SELECT * FROM ...);
student_stat_view是视图名称，视图名称必须在库中唯一（包括表名）。
我们只需要在AS之后添加SELECT语句即可完成视图创建语句：
CREATE
VIEW `jpa`.`student_stat_view`
AS
SELECT `level`,COUNT(id) AS num,SUM(average_score) AS total_score, SUM(average_score)/COUNT(id) AS sum_average_score
FROM student
GROUP BY `level`;
查询视图
视图创建好后可以在 SQLyog 中看到，也可以使用以下命令查询：
show table status where comment='view';
要查看视图创建时的 SQL 语句：
show create view student_stat_view;
我们可以像查询普通表那样查询视图：
mysql> select * from student_stat_view;
+-----------+-----+-------------+-------------------+
| level | num | total_score | sum_average_score |
+-----------+-----+-------------+-------------------+
| FRESH_MAN | 4 | 184 | 46.0000 |
| JUNIOR | 11 | 526 | 47.8182 |
| SOPHOMORE | 6 | 328 | 54.6667 |
| SENIOR | 6 | 190 | 31.6667 |
+-----------+-----+-------------+-------------------+
这样就很容易查询出我们需要年级的均分情况：
mysql> select * from student_stat_view where level='FRESH_MAN';
+-----------+-----+-------------+-------------------+
| level | num | total_score | sum_average_score |
+-----------+-----+-------------+-------------------+
| FRESH_MAN | 4 | 184 | 46.0000 |
+-----------+-----+-------------+-------------------+
如果不使用视图，我们可能需要这么查询：
select `level`,count(id) as num,sum(average_score) as total_score, sum(average_score)/count(id) as sum_average_score
from student
group by `level` having `level`='FRESH_MAN'
视图的优缺点
可以从上面这个示例中看出，视图具有以下优点：
-
简化 SQL 查询。
-
让查询更直观。
视图也有缺点，视图本身并不保存数据，视图的数据都来自查询关联的基本表，视图本身只是一条查询 SQL。所以每次查询视图意味着执行了一条视图相关的 SQL，如果创建大量视图，且包含视图关联视图、视图嵌套视图、视图关联表等复杂查询语句，就可能造成性能问题。
简化多表联查
上面的示例演示了如何用视图简化聚合数据查询，视图还常用于简化多表联查。
假设我们有以下表：
mysql> select * from school;
+----+------------------+
| id | name |
+----+------------------+
| 2 | 布斯巴顿魔法学校 |
| 1 | 霍格沃茨魔法学校 |
+----+------------------+
mysql> select * from student;
+----+----------+-----------+
| id | name | school_id |
+----+----------+-----------+
| 1 | Harry | 1 |
| 2 | icexmoon | 1 |
| 3 | JackChen | 2 |
+----+----------+-----------+
mysql> select * from email;
+----+----------+-----------+------------+
| id | account | domain | student_id |
+----+----------+-----------+------------+
| 1 | harry | qq.com | 1 |
| 2 | harry | gmail.com | 1 |
| 3 | icexmoon | qq.com | 2 |
| 4 | 111 | tom.com | 2 |
| 5 | 123 | gmail.com | 3 |
| 6 | jack | qq.com | 3 |
+----+----------+-----------+------------+
假设我们需要查询使用了某种邮箱的学校，可能需要这么查询：
mysql> SELECT DISTINCT sc.id,sc.name FROM
-> school AS sc
-> LEFT JOIN student AS s
-> ON s.school_id=sc.id
-> LEFT JOIN email AS e
-> ON e.student_id=s.id
-> WHERE e.domain='gmail.com';
+----+------------------+
| id | name |
+----+------------------+
| 1 | 霍格沃茨魔法学校 |
| 2 | 布斯巴顿魔法学校 |
+----+------------------+
不要问为什么会有这么古怪的要求...
可以用视图简化这个过程，我们可以创建一个表示学校和电子邮件的视图：
CREATE
VIEW `jpa`.`school_email_view`
AS
SELECT sc.id AS school_id,sc.name AS school_name,e.id AS email_id, e.account AS email_account, e.domain AS email_domain FROM
school AS sc
LEFT JOIN student AS s
ON s.school_id=sc.id
LEFT JOIN email AS e
ON e.student_id=s.id;
这个视图直接体现了学校和学校学生拥有的电邮地址之间的关系：
mysql> select * from school_email_view;
+-----------+------------------+----------+---------------+--------------+
| school_id | school_name | email_id | email_account | email_domain |
+-----------+------------------+----------+---------------+--------------+
| 2 | 布斯巴顿魔法学校 | 6 | jack | qq.com |
| 2 | 布斯巴顿魔法学校 | 5 | 123 | gmail.com |
| 1 | 霍格沃茨魔法学校 | 2 | harry | gmail.com |
| 1 | 霍格沃茨魔法学校 | 1 | harry | qq.com |
| 1 | 霍格沃茨魔法学校 | 4 | 111 | tom.com |
| 1 | 霍格沃茨魔法学校 | 3 | icexmoon | qq.com |
+-----------+------------------+----------+---------------+--------------+
利用视图可以很容易实现之前的查询：
mysql> select distinct school_id,school_name from school_email_view where email_domain='gmail.com';
+-----------+------------------+
| school_id | school_name |
+-----------+------------------+
| 1 | 霍格沃茨魔法学校 |
| 2 | 布斯巴顿魔法学校 |
+-----------+------------------+
这样做还有个好处，我们可以无需理解三张表的联结关系，只需要按照需求查询视图即可。
简化字段计算
视图的另一个常见用途是对字段进行重新计算和处理，比如在上边的email表中，电子邮件帐号和网站是单独存放的字段，如果我们想要获取类似123@gmail.com这样的电邮地址，就需要在查询语句中字符串拼接，或者在代码端处理。
完全可以用视图简化这个过程：
CREATE
VIEW `jpa`.`email_address_view`
AS
select id as email_id,concat(account,'@',domain) as email_address
from email
order by id;
查询视图：
mysql> select * from email_address_view;
+----------+-----------------+
| email_id | email_address |
+----------+-----------------+
| 1 | harry@qq.com |
| 2 | harry@gmail.com |
| 3 | icexmoon@qq.com |
| 4 | 111@tom.com |
| 5 | 123@gmail.com |
| 6 | jack@qq.com |
+----------+-----------------+
相比维护一个冗余字段存储完整电邮地址，这样做的好处在于如果有帐号或者domain修改，email_address也会改变，无需我们重新维护。但这样是有代价的，是牺牲性能带来的。所以这是个取舍的问题，如果电子邮件改变的不频繁，并且需要我们进行性能优化，我们就可以考虑使用冗余字段存储，并且为添加和更新语句创建，来解决维护冗余字段的问题。
视图和表联查
视图可以和表或其它视图联结查询：
mysql> select e.*,ev.`email_address`
-> from email as e
-> left join email_address_view as ev
-> on e.id=ev.`email_id`;
+----+----------+-----------+------------+-----------------+
| id | account | domain | student_id | email_address |
+----+----------+-----------+------------+-----------------+
| 1 | harry | qq.com | 1 | harry@qq.com |
| 2 | harry | gmail.com | 1 | harry@gmail.com |
| 3 | icexmoon | qq.com | 2 | icexmoon@qq.com |
| 4 | 111 | tom.com | 2 | 111@tom.com |
| 5 | 123 | gmail.com | 3 | 123@gmail.com |
| 6 | jack | qq.com | 3 | jack@qq.com |
+----+----------+-----------+------------+-----------------+
视图的限制
视图也存在一些限制，比如视图不能使用索引。
这很容易理解，视图本身并不存储内容，只是一条查询语句。而索引是创建在列上的，并且会创建一个B+树结构的存储数据。
修改 & 删除
用 SQLyog 修改视图会出现类似下面的模版：
DELIMITER $$
ALTER ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `school_email_view` AS
select
`sc`.`id` AS `school_id`,
`sc`.`name` AS `school_name`,
`e`.`id` AS `email_id`,
`e`.`account` AS `email_account`,
`e`.`domain` AS `email_domain`
from ((`school` `sc`
left join `student` `s`
on ((`s`.`school_id` = `sc`.`id`)))
left join `email` `e`
on ((`e`.`student_id` = `s`.`id`)))$$
DELIMITER ;
所以可以使用ALTER ... VIEW ... AS语句修改视图。
此外也可以先删除视图：
drop view school_email_view;
再重新创建。
参考资料
-
《MySQL 必知必会》
文章评论