视图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本身就是一种观点,一种角度,还是用来看的比较好。

原文来自:https://www.54371.net/linux/mariadb-study-view.html

本文地址:https://www.linuxprobe.com/mariadb-study-view.html编辑:清蒸github,审核员:逄增宝

Linux命令大全:https://www.linuxcool.com/