Mariadb学习总结（十一）：视图VIEW

视图VIEW

首先，一个问题，视图（VIEW）是什么？
视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询，简单而言，视图是复杂SELECT的抽象，将复杂SELECT语句抽象成一张虚拟的表，当对这个表执行简单查询时，实质上就是对复杂SELECT语句的执行。

有关例子，我这里想用Mariadb的KB库中的一篇文章，Let's GO！

准备环境

创建雇员（Employees）表：

CREATE TABLE `Employees` (
  `ID` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `First_Name` VARCHAR(25) NOT NULL,
  `Last_Name` VARCHAR(25) NOT NULL,
  `Position` VARCHAR(25) NOT NULL,
  `Home_Address` VARCHAR(50) NOT NULL,
  `Home_Phone` VARCHAR(12) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM;

向雇员表中插入测试数据：

INSERT INTO `Employees` (`First_Name`, `Last_Name`, `Position`, `Home_Address`, `Home_Phone`)
VALUES
  ('Mustapha', 'Mond', 'Chief Executive Officer', '692 Promiscuous Plaza', '326-555-3492'),
  ('Henry', 'Foster', 'Store Manager', '314 Savage Circle', '326-555-3847'),
  ('Bernard', 'Marx', 'Cashier', '1240 Ambient Avenue', '326-555-8456'),
  ('Lenina', 'Crowne', 'Cashier', '281 Bumblepuppy Boulevard', '328-555-2349'),
  ('Fanny', 'Crowne', 'Restocker', '1023 Bokanovsky Lane', '326-555-6329'),
  ('Helmholtz', 'Watson', 'Janitor', '944 Soma Court', '329-555-2478'); 

创建签到（Hours）表，记录了所有雇员的上班时间和下班时间的信息：

CREATE TABLE `Hours` (
  `ID` TINYINT(3) UNSIGNED NOT NULL,
  `Clock_In` DATETIME NOT NULL,
  `Clock_Out` DATETIME NOT NULL
) ENGINE=MyISAM;

插入测试记录：

INSERT INTO `Hours`
VALUES ('1', '2005-08-08 07:00:42', '2005-08-08 17:01:36'),
  ('1', '2005-08-09 07:01:34', '2005-08-09 17:10:11'),
  ('1', '2005-08-10 06:59:56', '2005-08-10 17:09:29'),
  ('1', '2005-08-11 07:00:17', '2005-08-11 17:00:47'),
  ('1', '2005-08-12 07:02:29', '2005-08-12 16:59:12'),
  ('2', '2005-08-08 07:00:25', '2005-08-08 17:03:13'),
  ('2', '2005-08-09 07:00:57', '2005-08-09 17:05:09'),
  ('2', '2005-08-10 06:58:43', '2005-08-10 16:58:24'),
  ('2', '2005-08-11 07:01:58', '2005-08-11 17:00:45'),
  ('2', '2005-08-12 07:02:12', '2005-08-12 16:58:57'),
  ('3', '2005-08-08 07:00:12', '2005-08-08 17:01:32'),
  ('3', '2005-08-09 07:01:10', '2005-08-09 17:00:26'),
  ('3', '2005-08-10 06:59:53', '2005-08-10 17:02:53'),
  ('3', '2005-08-11 07:01:15', '2005-08-11 17:04:23'),
  ('3', '2005-08-12 07:00:51', '2005-08-12 16:57:52'),
  ('4', '2005-08-08 06:54:37', '2005-08-08 17:01:23'),
  ('4', '2005-08-09 06:58:23', '2005-08-09 17:00:54'),
  ('4', '2005-08-10 06:59:14', '2005-08-10 17:00:12'),
  ('4', '2005-08-11 07:00:49', '2005-08-11 17:00:34'),
  ('4', '2005-08-12 07:01:09', '2005-08-12 16:58:29'),
  ('5', '2005-08-08 07:00:04', '2005-08-08 17:01:43'),
  ('5', '2005-08-09 07:02:12', '2005-08-09 17:02:13'),
  ('5', '2005-08-10 06:59:39', '2005-08-10 17:03:37'),
  ('5', '2005-08-11 07:01:26', '2005-08-11 17:00:03'),
  ('5', '2005-08-12 07:02:15', '2005-08-12 16:59:02'),
  ('6', '2005-08-08 07:00:12', '2005-08-08 17:01:02'),
  ('6', '2005-08-09 07:03:44', '2005-08-09 17:00:00'),
  ('6', '2005-08-10 06:54:19', '2005-08-10 17:03:31'),
  ('6', '2005-08-11 07:00:05', '2005-08-11 17:02:57'),
  ('6', '2005-08-12 07:02:07', '2005-08-12 16:58:23');

//所以，这张表记录了2015-08-08-2015-08-12的一个工作周的打卡记录

视图的作用

现在，我们要考察这一周哪位雇员迟到了（超过7:00:59），可以写出如下SQL语句：

MariaDB [world]> SELECT E.First_Name,E.Last_Name,H.Clock_IN,H.Clock_Out
    ->     FROM Employees AS E
    ->     INNER JOIN Hours AS H ON E.ID = H.ID
    ->     WHERE DATE_FORMAT(H.Clock_In,'%Y-%m-%d') >= '2005-08-08'
    ->     AND DATE_FORMAT(H.Clock_In,'%Y-%m-%d') < = '2005-08-12' ->     AND DATE_FORMAT(H.Clock_In,'%H:%i:%S') > '07:00:59';
+------------+-----------+---------------------+---------------------+
| First_Name | Last_Name | Clock_IN            | Clock_Out           |
+------------+-----------+---------------------+---------------------+
| Mustapha   | Mond      | 2005-08-09 07:01:34 | 2005-08-09 17:10:11 |
| Mustapha   | Mond      | 2005-08-12 07:02:29 | 2005-08-12 16:59:12 |
| Henry      | Foster    | 2005-08-11 07:01:58 | 2005-08-11 17:00:45 |
| Henry      | Foster    | 2005-08-12 07:02:12 | 2005-08-12 16:58:57 |
| Bernard    | Marx      | 2005-08-09 07:01:10 | 2005-08-09 17:00:26 |
| Bernard    | Marx      | 2005-08-11 07:01:15 | 2005-08-11 17:04:23 |
| Lenina     | Crowne    | 2005-08-12 07:01:09 | 2005-08-12 16:58:29 |
| Fanny      | Crowne    | 2005-08-09 07:02:12 | 2005-08-09 17:02:13 |
| Fanny      | Crowne    | 2005-08-11 07:01:26 | 2005-08-11 17:00:03 |
| Fanny      | Crowne    | 2005-08-12 07:02:15 | 2005-08-12 16:59:02 |
| Helmholtz  | Watson    | 2005-08-09 07:03:44 | 2005-08-09 17:00:00 |
| Helmholtz  | Watson    | 2005-08-12 07:02:07 | 2005-08-12 16:58:23 |
+------------+-----------+---------------------+---------------------+
12 rows in set (0.00 sec)

但是，公司有规定，迟到的人在下班时把迟到的时间补出来就可以既往不咎。

以下需要使用一个函数TIMESTAMPDIFF，这个函数返回两个时间的差值。

语法如下：

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

unit有如下值：
 MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR

MariaDB [world]> SELECT TIMESTAMPDIFF(HOUR,'2018-01-01','2018-01-02');
+-----------------------------------------------+
| TIMESTAMPDIFF(HOUR,'2018-01-01','2018-01-02') |
+-----------------------------------------------+
|                                            24 |
+-----------------------------------------------+
1 row in set (0.00 sec)

所以，我们要查询迟到的员工，即下班打开时间-上班打开时间＜600分钟的人，便可以认为是迟到的员工，可写出下列SQL语句：

MariaDB [world]> SELECT 
    ->      E.First_Name,E.Last_Name,H.Clock_In,H.Clock_Out
    ->      ,TIMESTAMPDIFF(MINUTE,H.Clock_In,H.Clock_Out)-600 AS DIFFERENCE
    ->      FROM Employees AS E
    ->      INNER JOIN Hours AS H
    ->      ON E.ID = H.ID
    ->      WHERE DATE_FORMAT(H.Clock_In , '%H:%i:%S') > '07:00:59'
    ->      AND TIMESTAMPDIFF(MINUTE,H.Clock_In,H.Clock_Out) < 600;
+------------+-----------+---------------------+---------------------+------------+
| First_Name | Last_Name | Clock_In            | Clock_Out           | DIFFERENCE |
+------------+-----------+---------------------+---------------------+------------+
| Mustapha   | Mond      | 2005-08-12 07:02:29 | 2005-08-12 16:59:12 |         -4 |
| Henry      | Foster    | 2005-08-11 07:01:58 | 2005-08-11 17:00:45 |         -2 |
| Henry      | Foster    | 2005-08-12 07:02:12 | 2005-08-12 16:58:57 |         -4 |
| Bernard    | Marx      | 2005-08-09 07:01:10 | 2005-08-09 17:00:26 |         -1 |
| Lenina     | Crowne    | 2005-08-12 07:01:09 | 2005-08-12 16:58:29 |         -3 |
| Fanny      | Crowne    | 2005-08-11 07:01:26 | 2005-08-11 17:00:03 |         -2 |
| Fanny      | Crowne    | 2005-08-12 07:02:15 | 2005-08-12 16:59:02 |         -4 |
| Helmholtz  | Watson    | 2005-08-09 07:03:44 | 2005-08-09 17:00:00 |         -4 |
| Helmholtz  | Watson    | 2005-08-12 07:02:07 | 2005-08-12 16:58:23 |         -4 |
+------------+-----------+---------------------+---------------------+------------+
9 rows in set (0.00 sec)

这个查询我们可能会经常用到，毕竟要知道谁迟到了不是？但是查询又比较复杂难写，这时候，视图就可以帮助到我们了。

我们可以将此查询创建为一个视图，当对视图进行查询时，实际上就是查询了这个复杂的操作，创建视图如下：

MariaDB [world]> CREATE SQL SECURITY INVOKER VIEW Employee_LateList AS
    -> SELECT 
    ->      E.First_Name,E.Last_Name,H.Clock_In,H.Clock_Out
    ->      ,TIMESTAMPDIFF(MINUTE,H.Clock_In,H.Clock_Out)-600 AS DIFFERENCE
    ->      FROM Employees AS E
    ->      INNER JOIN Hours AS H
    ->      ON E.ID = H.ID
    ->      WHERE DATE_FORMAT(H.Clock_In , '%H:%i:%S') > '07:00:59'
    ->      AND TIMESTAMPDIFF(MINUTE,H.Clock_In,H.Clock_Out) < 600; 
Query OK, 0 rows affected (0.01 sec) 
MariaDB [world]> SHOW TABLES;
+-------------------+
| Tables_in_world   |
+-------------------+
| Employee_LateList |  //可以看到这里多了一张表
| Employees         |
| Hours             |
| city              |
| country           |
| countrylanguage   |
| department        |
| user              |
+-------------------+
8 rows in set (0.06 sec)

现在，我们有了视图，查询一下试试~

MariaDB [world]> SELECT * FROM Employee_LateList;
+------------+-----------+---------------------+---------------------+------------+
| First_Name | Last_Name | Clock_In            | Clock_Out           | DIFFERENCE |
+------------+-----------+---------------------+---------------------+------------+
| Mustapha   | Mond      | 2005-08-12 07:02:29 | 2005-08-12 16:59:12 |         -4 |
| Henry      | Foster    | 2005-08-11 07:01:58 | 2005-08-11 17:00:45 |         -2 |
| Henry      | Foster    | 2005-08-12 07:02:12 | 2005-08-12 16:58:57 |         -4 |
| Bernard    | Marx      | 2005-08-09 07:01:10 | 2005-08-09 17:00:26 |         -1 |
| Lenina     | Crowne    | 2005-08-12 07:01:09 | 2005-08-12 16:58:29 |         -3 |
| Fanny      | Crowne    | 2005-08-11 07:01:26 | 2005-08-11 17:00:03 |         -2 |
| Fanny      | Crowne    | 2005-08-12 07:02:15 | 2005-08-12 16:59:02 |         -4 |
| Helmholtz  | Watson    | 2005-08-09 07:03:44 | 2005-08-09 17:00:00 |         -4 |
| Helmholtz  | Watson    | 2005-08-12 07:02:07 | 2005-08-12 16:58:23 |         -4 |
+------------+-----------+---------------------+---------------------+------------+
9 rows in set (0.00 sec)
//加个条件再查询下，查询2005-08-12当天迟到的人

MariaDB [world]> SELECT * FROM Employee_LateList                                        
    ->     WHERE DATE_FORMAT(Clock_In,'%Y-%m-%d') = '2005-08-12';
+------------+-----------+---------------------+---------------------+------------+
| First_Name | Last_Name | Clock_In            | Clock_Out           | DIFFERENCE |
+------------+-----------+---------------------+---------------------+------------+
| Mustapha   | Mond      | 2005-08-12 07:02:29 | 2005-08-12 16:59:12 |         -4 |
| Henry      | Foster    | 2005-08-12 07:02:12 | 2005-08-12 16:58:57 |         -4 |
| Lenina     | Crowne    | 2005-08-12 07:01:09 | 2005-08-12 16:58:29 |         -3 |
| Fanny      | Crowne    | 2005-08-12 07:02:15 | 2005-08-12 16:59:02 |         -4 |
| Helmholtz  | Watson    | 2005-08-12 07:02:07 | 2005-08-12 16:58:23 |         -4 |
+------------+-----------+---------------------+---------------------+------------+
5 rows in set (0.00 sec)
//这个DATE_FORMAT(Clock_In,'%Y-%m-%d') = '2005-08-12'查询条件会以AND 直接拼接到我们的复杂SQL后面

以上，便是视图的一个直观例子，来自MariaDB的KB库。

创建视图

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW [IF NOT EXISTS] view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

当视图已经存在时，CREATE OR REPLACE相当于ALTER VIEW，视图创建时会将*变为具体的列引用，所以对BASE表（被视图引用的表）增加新的列时，在视图中不会显示，请看如下例子：

CREATE OR REPLACE SQL SECURITY INVOKER VIEW test_view  AS  SELECT * FROM city;
MariaDB [world]> SELECT * FROM test_view LIMIT 3;
+----+----------+-------------+----------+------------+
| ID | Name     | CountryCode | District | Population |
+----+----------+-------------+----------+------------+
|  1 | Kabul    | AFG         | Kabol    |    1780000 |
|  2 | Qandahar | AFG         | Qandahar |     237500 |
|  3 | Herat    | AFG         | Herat    |     186800 |
+----+----------+-------------+----------+------------+
3 rows in set (0.00 sec)
//为city表增加一个字段
MariaDB [world]> ALTER TABLE city
    -> ADD COLUMN Test INT(3);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0
//从city表检索一条数据
MariaDB [world]> SELECT * FROM city LIMIT 1;
+----+-------+-------------+----------+------------+------+
| ID | Name  | CountryCode | District | Population | Test |
+----+-------+-------------+----------+------------+------+
|  1 | Kabul | AFG         | Kabol    |    1780000 | NULL |
+----+-------+-------------+----------+------------+------+
1 row in set (0.00 sec)
//从视图中检索一条数据
MariaDB [world]> SELECT * FROM test_view LIMIT 1;
+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
|  1 | Kabul | AFG         | Kabol    |    1780000 |
+----+-------+-------------+----------+------------+
1 row in set (0.01 sec)

[DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }] 来定义该视图的定义者，默认为当前用户

[SQL SECURITY { DEFINER | INVOKER }] 定义权限检查，默认为DEFINER，这里比较有意思，所以需要解释一下：

假设我们的视图这样select_statement为SELECT * FROM city;且有新用户test@'%'仅对该视图有SELECT权限
当SQL SECURITY为DEFINER时，test检索此视图时，访问city表的用户实际上是definer用户
当SQL SECURITY为INVOKER时，test检索此视图时，访问city表的用户时test，而test对该表没有检索权限，所以会报错。

AS select_statement 中的select_statement就是视图的定义了，select_statement可以是从其他视图中检索数据。

select_statement有如下限制：

1. SELECT中的FROM子句不能包含子查询
2. 不能引用系统变量和用户变量
3. 不能用预编译SQL语句
4. 无法将触发器和视图关联
5. 可以使用ORDER BY，但对视图使用ORDER BY时，原定义中的ORDER BY会被替换

视图算法

[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]->选择Mysql对视图的处理方法，默认为UNDEFINED，请看下列详解：

UNDEFINED：由Mysql自动选择处理办法，更倾向于MERGE。

MERGE：将对视图的检索子句和定义中的子句合并后查询，例如：

//创建视图如下
CREATE OR REPLACE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW test_view  AS  SELECT * FROM city ORDER BY Id;
//查询视图
SELECT * FROM test_view WHERE ID < 5;
//以上查询等同于下面的查询
SELECT ID,Name,CountryCode,District,Population FROM city WHERE ID < 5;

也就是SELECT的子句会直接拼接（合并）到视图定义中的SELECT语句中去。

需要注意的是，当使用以下子句定义视图时，视图无法使用MERGE：

• HAVING
• LIMIT
• GROUP BY
• DISTINCT
• UNION
• UNION ALL
• 聚合函数，例如MAX(), MIN(), SUM()， COUNT()
• 子查询

TEMPTABLE：执行视图中定义的SQL语句，并将结果集存入临时表，而从视图检索时定义的子句会被作用于这张临时表，所以性能会有所损耗。

删除视图

语法格式如下：

DROP VIEW [IF EXISTS]
    view_name [, view_name] ...

修改视图

语法格式如下：

ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

跟CREATE VIEW一样的，所以就不过多解释了。

查看视图

语法也很简单，如下：

SHOW CREATE VIEW view_name;

有关视图的数据更新

除非有必要，真的不建议使用向视图中插入、更新、删除数据，除了诸多限制以为，我感觉还违背了视图的存在原则；视图，英文为VIEW，而VIEW本身就是一种观点，一种角度，还是用来看的比较好。