Mysql视图

视图就是为了简化查询而提出的。我们可以将view视为table来用。视图可以与其他表进行join。视图就像一张虚拟表，但是视图不存储数据，数据存储在table中，我们做的只是把视图提供给了基础表。创建视图后只是保存的代码逻辑，只有对视图表执行select后，视图表中的代码逻辑才会运作，到相应的表按照逻辑抽取数据。

创建视图

1
2
3
4
5
6
7
8
9
10
USE sql_invoicing;

CREATE VIEW sales_by_client AS
SELECT 
		c.client_id,
		c.name,
		SUM(invoice_total) as total_sales
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id, name

运行代码后可以发现，在navicate的视图一栏下面可以看到我们定义的视图表。

查看终端数据库表，可以发现，在mysql中，视图也是以表的形式存储，但要注意视图里没有数据。

1
2
3
4
5
6
7
8
9
10
11
mysql> show tables;
+-------------------------+
| Tables_in_sql_invoicing |
+-------------------------+
| clients                 |
| invoices                |
| payment_methods         |
| payments                |
| sales_by_client <--     |
+-------------------------+
5 rows in set (0.00 sec)

由于视图是在sql_invoicing数据库下创建的，所以我们可以用”数据库名.视图名”来调用查看视图。

1
2
3
4
5
6
7
8
9
10
mysql> SELECT * FROM sql_invoicing.sales_by_client;
+-----------+-------------+-------------+
| client_id | name        | total_sales |
+-----------+-------------+-------------+
|         2 | Myworks     |      101.79 |
|         5 | Topiclounge |      980.02 |
|         3 | Yadel       |      705.90 |
|         1 | Vinte       |      802.89 |
+-----------+-------------+-------------+
4 rows in set (0.00 sec)

可以对视图执行普通表的join和筛选操作：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
mysql> SELECT *
    -> FROM sql_invoicing.sales_by_client
    -> ORDER BY total_sales DESC;
+-----------+-------------+-------------+
| client_id | name        | total_sales |
+-----------+-------------+-------------+
|         5 | Topiclounge |      980.02 |
|         1 | Vinte       |      802.89 |
|         3 | Yadel       |      705.90 |
|         2 | Myworks     |      101.79 |
+-----------+-------------+-------------+
4 rows in set (0.00 sec)

mysql> SELECT *
    -> FROM sql_invoicing.sales_by_client
    -> WHERE total_sales > 500;
+-----------+-------------+-------------+
| client_id | name        | total_sales |
+-----------+-------------+-------------+
|         5 | Topiclounge |      980.02 |
|         3 | Yadel       |      705.90 |
|         1 | Vinte       |      802.89 |
+-----------+-------------+-------------+
3 rows in set (0.00 sec)

mysql> SELECT *
    -> FROM sql_invoicing.sales_by_client v
    -> JOIN clients USING (client_id);
+-----------+-------------+-------------+-------------+------------------------+---------------+-------+--------------+
| client_id | name        | total_sales | name        | address                | city          | state | phone        |
+-----------+-------------+-------------+-------------+------------------------+---------------+-------+--------------+
|         1 | Vinte       |      802.89 | Vinte       | 3 Nevada Parkway       | Syracuse      | NY    | 315-252-7305 |
|         2 | Myworks     |      101.79 | Myworks     | 34267 Glendale Parkway | Huntington    | WV    | 304-659-1170 |
|         3 | Yadel       |      705.90 | Yadel       | 096 Pawling Parkway    | San Francisco | CA    | 415-144-6037 |
|         5 | Topiclounge |      980.02 | Topiclounge | 0863 Farmco Road       | Portland      | OR    | 971-888-9129 |
+-----------+-------------+-------------+-------------+------------------------+---------------+-------+--------------+
4 rows in set (0.00 sec)

练习

1
2
3
4
5
6
7
8
9
CREATE VIEW client_balance AS
SELECT
		c.client_id,
		c.name,
		-- groupby 要注意使用聚合函数
		SUM(invoice_total - payment_total) AS balance
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id, name;
1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT *
    -> FROM client_balance;
+-----------+-------------+---------+
| client_id | name        | balance |
+-----------+-------------+---------+
|         2 | Myworks     |  101.79 |
|         5 | Topiclounge |  841.63 |
|         3 | Yadel       |  557.46 |
|         1 | Vinte       |  728.34 |
+-----------+-------------+---------+
4 rows in set (0.00 sec)

更改或删除视图

删除视图

1.删除视图重新创建

1
DROP VIEW VIEW_NAME;

2.CREATE OR REPLACE VIEW

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
mysql> CREATE VIEW client_balance AS
    -> SELECT
    ->          c.client_id,
    ->          c.name,
    ->          -- groupby 要注意使用聚合函数
    ->          SUM(invoice_total - payment_total) AS balance
    -> FROM clients c
    -> JOIN invoices i USING (client_id)
    -> GROUP BY client_id, name;
ERROR 1050 (42S01): Table 'client_balance' already exists <--

mysql> CREATE OR REPLACE VIEW client_balance AS
    -> SELECT
    ->          c.client_id,
    ->          c.name,
    ->          -- groupby 要注意使用聚合函数
    ->          SUM(invoice_total - payment_total) AS balance
    -> FROM clients c
    -> JOIN invoices i USING (client_id)
    -> GROUP BY client_id, name;
Query OK, 0 rows affected (0.01 sec) <--可以看到这种方式的确可以覆盖视图

mysql> SHOW TABLES;
+-------------------------+
| Tables_in_sql_invoicing |
+-------------------------+
| client_balance          |
| clients                 |
| invoices                |
| payment_methods         |
| payments                |
| sales_by_client         |
+-------------------------+
6 rows in set (0.00 sec)

更改视图

一般我们会将视图的sql代码给保存起来上传给别人，这样别人就可以通过视图sql代码新建一个一样的视图。此外，我们还可以去设计视图表查看视图的源码：

1
2
3
4
5
6
7
8
9
10
select 
		`c`.`client_id` AS `client_id`,
		`c`.`name` AS `name`,
		sum((`i`.`invoice_total` - `i`.`payment_total`)) AS `balance` 
		
from (`clients` `c` 
			join `invoices` `i` 
			on((`c`.`client_id` = `i`.`client_id`))) 
			
group by `c`.`client_id`,`c`.`name`

可以看到，mysql在我们的视图逻辑代码增添了一些且打上了反引号``，这样是为了防止列名的重复和冲突。我们可以在视图表的设计表中的逻辑代码来更新视图，比如这里我们加上个降序排序：

1
2
3
4
5
6
7
8
9
10
11
select 
		`c`.`client_id` AS `client_id`,
		`c`.`name` AS `name`,
		sum((`i`.`invoice_total` - `i`.`payment_total`)) AS `balance` 
		
from (`clients` `c` 
			join `invoices` `i` 
			on((`c`.`client_id` = `i`.`client_id`))) 
			
group by `c`.`client_id`,`c`.`name`
order by balance desc

可更新视图:

更新视图

视图是可更新视图的前提是，视图里不含有distinct、聚合函数、group by、having、union这类。

我们拿之前的client_balance视图来进行测试（含有group by）

1
2
3
mysql> DELETE FROM client_balance
    -> WHERE client_id = 1;
ERROR 1288 (HY000): The target table client_balance of the DELETE is not updatable

可以发现，无法更新。

现在创建一个只含有join的视图，进行测试：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
mysql> CREATE OR REPLACE VIEW test AS
    -> SELECT
    -> client_id,
    -> invoice_total,
    -> invoice_date,
    -> payment_date,
    -> name
    -> FROM invoices
    -> JOIN clients USING (client_id);
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM test LIMIT 10;
+-----------+---------------+--------------+--------------+---------+
| client_id | invoice_total | invoice_date | payment_date | name    |
+-----------+---------------+--------------+--------------+---------+
|         1 |        157.78 | 2019-01-29   | 2019-01-05   | Vinte   |
|         1 |        189.12 | 2019-05-20   | NULL         | Vinte   |
|         1 |        159.50 | 2019-06-30   | NULL         | Vinte   |
|         1 |        162.02 | 2019-03-30   | NULL         | Vinte   |
|         1 |        134.47 | 2019-11-23   | NULL         | Vinte   |
|         2 |        101.79 | 2019-03-09   | NULL         | Myworks |
|         3 |        152.21 | 2019-03-08   | NULL         | Yadel   |
|         3 |        133.87 | 2019-09-04   | NULL         | Yadel   |
|         3 |        126.15 | 2019-01-07   | 2019-01-13   | Yadel   |
|         3 |        167.29 | 2019-11-25   | 2019-01-17   | Yadel   |
+-----------+---------------+--------------+--------------+---------+
10 rows in set (0.00 sec)

mysql> UPDATE test
    -> SET payment_date = DATE_ADD(payment_date, INTERVAL 2 DAY);
Query OK, 7 rows affected (0.04 sec)
Rows matched: 17  Changed: 7  Warnings: 0

mysql> SELECT * FROM test LIMIT 10;
+-----------+---------------+--------------+--------------+---------+
| client_id | invoice_total | invoice_date | payment_date | name    |
+-----------+---------------+--------------+--------------+---------+
|         1 |        157.78 | 2019-01-29   | 2019-01-07   | Vinte   |
|         1 |        189.12 | 2019-05-20   | NULL         | Vinte   |
|         1 |        159.50 | 2019-06-30   | NULL         | Vinte   |
|         1 |        162.02 | 2019-03-30   | NULL         | Vinte   |
|         1 |        134.47 | 2019-11-23   | NULL         | Vinte   |
|         2 |        101.79 | 2019-03-09   | NULL         | Myworks |
|         3 |        152.21 | 2019-03-08   | NULL         | Yadel   |
|         3 |        133.87 | 2019-09-04   | NULL         | Yadel   |
|         3 |        126.15 | 2019-01-07   | 2019-01-15   | Yadel   |
|         3 |        167.29 | 2019-11-25   | 2019-01-19   | Yadel   |
+-----------+---------------+--------------+--------------+---------+
10 rows in set (0.00 sec)

发现可以更新。此时我们来查看下原表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> SELECT
    -> client_id,
    -> invoice_total,
    -> invoice_date,
    -> payment_date
    -> FROM invoices
    -> ORDER BY client_id;
+-----------+---------------+--------------+--------------+
| client_id | invoice_total | invoice_date | payment_date |
+-----------+---------------+--------------+--------------+
|         1 |        157.78 | 2019-01-29   | 2019-01-07   |
|         1 |        189.12 | 2019-05-20   | NULL         |
|         1 |        159.50 | 2019-06-30   | NULL         |
|         1 |        162.02 | 2019-03-30   | NULL         |
|         1 |        134.47 | 2019-11-23   | NULL         |
|         2 |        101.79 | 2019-03-09   | NULL         |
|         3 |        152.21 | 2019-03-08   | NULL         |
|         3 |        133.87 | 2019-09-04   | NULL         |
|         3 |        126.15 | 2019-01-07   | 2019-01-15   |
|         3 |        167.29 | 2019-11-25   | 2019-01-19   |
|         3 |        126.38 | 2019-07-30   | 2019-01-19   |
|         5 |        175.32 | 2019-06-11   | 2019-02-16   |
|         5 |        147.99 | 2019-07-31   | NULL         |
|         5 |        169.36 | 2019-07-18   | NULL         |
|         5 |        172.17 | 2019-07-09   | NULL         |
|         5 |        135.01 | 2019-06-25   | 2019-01-30   |
|         5 |        180.17 | 2019-05-23   | 2019-01-12   |
+-----------+---------------+--------------+--------------+
17 rows in set (0.00 sec)

发现原表的数据也得到更新。说明了在可更新的视图中更新数据，会对原表也进行一个更新。在业务中，有些表我们没有权限看到全量的数据并对其进行更新，就只能通过视图进行插入、更新、删除数据。

此外，我们还可以给视图插入新的一行数据，前提是要求插入的数据有构成视图的基础表所需的所有数据。比如说我们插入一行新的数据给视图，但是其中的（client_id）是空的，此时就会报错无法插入，因为client_id在基础表中是不允许是空的。

with option check point

根据之前的视图：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> CREATE OR REPLACE VIEW client_with_balance AS
    -> SELECT
    ->          client_id,
    ->          number,
    ->          invoice_id,
    ->          invoice_total,
    ->          payment_total,
    ->          invoice_total - payment_total as balance
    -> FROM invoices
    -> ;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT *
    -> FROM client_with_balance
    -> LIMIT 5;
+-----------+-------------+------------+---------------+---------------+---------+
| client_id | number      | invoice_id | invoice_total | payment_total | balance |
+-----------+-------------+------------+---------------+---------------+---------+
|         2 | 91-953-3396 |          1 |        101.79 |          0.00 |  101.79 |
|         5 | 03-898-6735 |          2 |        175.32 |          8.18 |  167.14 |
|         5 | 20-228-0335 |          3 |        147.99 |          0.00 |  147.99 |
|         3 | 56-934-0748 |          4 |        152.21 |          0.00 |  152.21 |
|         5 | 87-052-3121 |          5 |        169.36 |          0.00 |  169.36 |
+-----------+-------------+------------+---------------+---------------+---------+
5 rows in set (0.00 sec)

我们可以知道，这是一个可更新的视图，balance中的数据来自于invoices两列相减，如果我们令client_id=2的

payment_total = invoice_total，我们来观察其变化：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> UPDATE client_with_balance
    -> SET invoice_total = payment_total
    -> WHERE client_id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT *
    -> FROM client_with_balance
    -> LIMIT 5;
+-----------+-------------+------------+---------------+---------------+---------+
| client_id | number      | invoice_id | invoice_total | payment_total | balance |
+-----------+-------------+------------+---------------+---------------+---------+
|         2 | 91-953-3396 |          1 |          0.00 |          0.00 |    0.00 |
|         5 | 03-898-6735 |          2 |        175.32 |          8.18 |  167.14 |
|         5 | 20-228-0335 |          3 |        147.99 |          0.00 |  147.99 |
|         3 | 56-934-0748 |          4 |        152.21 |          0.00 |  152.21 |
|         5 | 87-052-3121 |          5 |        169.36 |          0.00 |  169.36 |
+-----------+-------------+------------+---------------+---------------+---------+
5 rows in set (0.00 sec)

对视图进行修改可能会导致行从视图中删除，可以使用with check option来避免其产生。

1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE VIEW client_with_balance AS
SELECT
		client_id,
		number,
		invoice_id,
		invoice_total,
		payment_total,
		invoice_total - payment_total as balance
FROM invoices
WITH CHECK OPTION;

视图优点

1.简化查询；

2.减小数据库设计变动的影响；

  • 有时候我们对表结构的一列进行删除或者移动到其他列，会影响到涉及该列的所有sql代码的修改，如果我们用的是视图，就只需要对视图代码逻辑进行修改就好，这样如果我们所有的查询都是基于视图表，它就不会收到基础表结构变动的影响，所以视图为数据库表提供了一种抽象化，这种抽象化减少了变动带来的影响；

3.可以用视图表来限制基础表的访问；

  • 用户对于基础表的修改只能通过视图表进行修改，前提是视图是可更新的；