Mysql 视图

来源：互联网发布：自动整点报时软件编辑：程序博客网时间：2024/04/30 09:14

视图

一、视图 view

如果某个查询结果出现的非常频繁，也就拿这个结果进行子查询出现的非常频繁
干脆真的将这个临时表建立一个表

定义：视图是由查询结果形成的一张虚拟表。

创建语法：
create view 视图名 as select 语句;

删除视图
drop view 视图名;

视图修改
alter view as select...;

为什么要视图？
1、可以简化查询
如果某个查询结果出现的非常频繁，也就拿这个结果进行子查询出现的非常频繁，干脆真的将这个临时表建立一个表。

2、可以进行一些权限控制
把表的权限封闭，但是开放视图相应的权限，视图里只开放部分数据。视图也可以隐藏一部分敏感数据不对外公开。

3、大数据分表时可以用到
比如，表的行数超过200w时，就会变慢，可以把一张表的数据拆成4张表来存放，当把表拆分后，可以用视图来将这四张表union在一起，方便查询。

二、视图与表的关系

视图是表的一个查询结果，表的数据改变，会影响视图的结果。

那么如果视图改变了呢？
1、视图的增删改也会影响表，但是视图并不是总是能增删改的。

2、视图的数据与表的数据具有严格的一一对应关系时，是能够增删改的。否则，视图是不能增删改的。

mysql> create view avgofcat as select cat_id,avg(shop_price) as pingjun from goods group by cat_id;Query OK, 0 rows affected (0.00 sec)

mysql> select * from avgofcat;+--------+-------------+| cat_id | pingjun     |+--------+-------------+|      2 |  823.330000 ||      3 | 1746.066667 ||      4 | 2222.250000 ||      5 | 3700.000000 ||      8 |   75.333333 ||     11 |   31.000000 ||     13 |   33.500000 ||     14 |   54.000000 ||     15 |   70.000000 |+--------+-------------+9 rows in set (0.00 sec)

</pre><pre name="code" class="sql">mysql> update avgofcat set pingjun = 888.888 where cat_id = 2;ERROR 1288 (HY000): The target table avgofcat of the UPDATE is not updatable

因为这个平均值每个栏目下商品价格的平均值，是我们用视图创建的一张临时表，你去update 数据，原始的goods table不知道该怎么改，这时候就是没法修改的。

3、对于视图insert时，还要注意视图必须包含表中没有默认值的列。

三、视图的算法 algorithm

视图的algorithm有三种
algorithm = merge / temptable/undefined
merge:当引用视图时，引用视图的语句与定义视图的语句合并
temptable：当利用视图时，根据视图建立的创建语句建立一个临时表
undefined：未定义，自动，让系统帮你选

1、merge：意味着视图只是一个语句规则，当查询视图时，把查询视图的的语句（如where，group,order...）与创建时的语句where子句合并、分析后形成一条select语句。

例如：我们查询每个栏目下最贵的商品：

创建视图的语句：
mysql> create view g3 as select goods_id,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc;
Query OK, 0 rows affected (0.00 sec)

mysql> create view g3 as select goods_id,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc;Query OK, 0 rows affected (0.00 sec)mysql> select * from g3;+----------+--------+------------------------------+------------+| goods_id | cat_id | goods_name                   | shop_price |+----------+--------+------------------------------+------------+|       16 |      2 | 恒基伟业G101                       |     823.33 ||       22 |      3 | 多普达Touch HD                    |    5999.00 ||       32 |      3 | 诺基亚N85                         |    3010.00 ||       17 |      3 | 夏新N7                           |    2300.00 ||        9 |      3 | 诺基亚E66                         |    2298.00 ||       24 |      3 | P806                         |    2000.00 ||       21 |      3 | 金立 A30                         |    2000.00 ||       31 |      3 | 摩托罗拉E8                          |    1337.00 ||       10 |      3 | 索爱C702c                        |    1328.00 ||       13 |      3 | 诺基亚5320 XpressMusic            |    1311.00 ||       11 |      3 | 索爱C702c                        |    1300.00 ||       12 |      3 | 摩托罗拉A810                        |     983.00 ||       19 |      3 | 三星SGH-F258                     |     858.00 ||       15 |      3 | 摩托罗拉A810                        |     788.00 ||        8 |      3 | 飞利浦9@9v                         |     399.00 ||       20 |      3 | 三星BC01                         |     280.00 ||       18 |      4 | 夏新T5                           |    2878.00 ||       14 |      4 | 诺基亚5800XM                      |    2625.00 ||       33 |      4 | iphone 6 plus                |    1998.00 ||        1 |      4 | KD876                        |    1388.00 ||       23 |      5 | 诺基亚N96                         |    3700.00 ||        7 |      8 | 诺基亚N85原装立体声耳机HS-82                  |     100.00 ||        3 |      8 | 诺基亚原装5800耳机                      |      68.00 ||        4 |      8 | 诺基亚N85原装充电器                       |      58.00 ||        6 |     11 | 胜创KINGMAX内存卡                  |      42.00 ||        5 |     11 | 索爱原装M2卡读卡器                         |      20.00 ||       25 |     13 | 小灵通/固话50元充值卡                     |      48.00 ||       26 |     13 | 小灵通/固话20元充值卡                     |      19.00 ||       29 |     14 | 移动100元充值卡                       |      90.00 ||       30 |     14 | 移动20元充值卡                        |      18.00 ||       27 |     15 | 联通100元充值卡                       |      95.00 ||       28 |     15 | 联通50元充值卡                        |      45.00 |+----------+--------+------------------------------+------------+32 rows in set (0.05 sec)

查询视图的语句
mysql> select * from g3 group by cat_id;

mysql> select * from g3 group by cat_id;+----------+--------+-----------------------+------------+| goods_id | cat_id | goods_name            | shop_price |+----------+--------+-----------------------+------------+|       16 |      2 | 恒基伟业G101                |     823.33 ||        8 |      3 | 飞利浦9@9v                  |     399.00 ||        1 |      4 | KD876                 |    1388.00 ||       23 |      5 | 诺基亚N96                  |    3700.00 ||        4 |      8 | 诺基亚N85原装充电器                |      58.00 ||        5 |     11 | 索爱原装M2卡读卡器                  |      20.00 ||       25 |     13 | 小灵通/固话50元充值卡              |      48.00 ||       29 |     14 | 移动100元充值卡                |      90.00 ||       27 |     15 | 联通100元充值卡                |      95.00 |+----------+--------+-----------------------+------------+9 rows in set (0.00 sec)

我们会发现，这条语句失效了，并没有取出每个栏目下最贵的商品。原因是
实际作用的语句是这样的，两条语句合并：
select goods_id,cat_id,goods_name,shop_price from goods group by cat_id order by cat_id asc,shop_price desc;

mysql> select goods_id,cat_id,goods_name,shop_price from goods group by cat_id order by cat_id asc,shop_price desc;+----------+--------+-----------------------+------------+| goods_id | cat_id | goods_name            | shop_price |+----------+--------+-----------------------+------------+|       16 |      2 | 恒基伟业G101                |     823.33 ||        8 |      3 | 飞利浦9@9v                  |     399.00 ||        1 |      4 | KD876                 |    1388.00 ||       23 |      5 | 诺基亚N96                  |    3700.00 ||        4 |      8 | 诺基亚N85原装充电器                |      58.00 ||        5 |     11 | 索爱原装M2卡读卡器                  |      20.00 ||       25 |     13 | 小灵通/固话50元充值卡              |      48.00 ||       29 |     14 | 移动100元充值卡                |      90.00 ||       27 |     15 | 联通100元充值卡                |      95.00 |+----------+--------+-----------------------+------------+9 rows in set (0.00 sec)

可以看出实际执行的是这条语句

所以这种情况下要想继续使用视图，我们可以将algorithm修改为 temptable(临时表)

2、temptable：是根据创建语句瞬间创建一张临时表然后查询视图的语句从该
临时表查数据

mysql> create algorithm=temptable view g4 as select goods_id,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc;Query OK, 0 rows affected (0.01 sec)mysql> select * from g4;+----------+--------+------------------------------+------------+| goods_id | cat_id | goods_name                   | shop_price |+----------+--------+------------------------------+------------+|       16 |      2 | 恒基伟业G101                       |     823.33 ||       22 |      3 | 多普达Touch HD                    |    5999.00 ||       32 |      3 | 诺基亚N85                         |    3010.00 ||       17 |      3 | 夏新N7                           |    2300.00 ||        9 |      3 | 诺基亚E66                         |    2298.00 ||       24 |      3 | P806                         |    2000.00 ||       21 |      3 | 金立 A30                         |    2000.00 ||       31 |      3 | 摩托罗拉E8                          |    1337.00 ||       10 |      3 | 索爱C702c                        |    1328.00 ||       13 |      3 | 诺基亚5320 XpressMusic            |    1311.00 ||       11 |      3 | 索爱C702c                        |    1300.00 ||       12 |      3 | 摩托罗拉A810                        |     983.00 ||       19 |      3 | 三星SGH-F258                     |     858.00 ||       15 |      3 | 摩托罗拉A810                        |     788.00 ||        8 |      3 | 飞利浦9@9v                         |     399.00 ||       20 |      3 | 三星BC01                         |     280.00 ||       18 |      4 | 夏新T5                           |    2878.00 ||       14 |      4 | 诺基亚5800XM                      |    2625.00 ||       33 |      4 | iphone 6 plus                |    1998.00 ||        1 |      4 | KD876                        |    1388.00 ||       23 |      5 | 诺基亚N96                         |    3700.00 ||        7 |      8 | 诺基亚N85原装立体声耳机HS-82                  |     100.00 ||        3 |      8 | 诺基亚原装5800耳机                      |      68.00 ||        4 |      8 | 诺基亚N85原装充电器                       |      58.00 ||        6 |     11 | 胜创KINGMAX内存卡                  |      42.00 ||        5 |     11 | 索爱原装M2卡读卡器                         |      20.00 ||       25 |     13 | 小灵通/固话50元充值卡                     |      48.00 ||       26 |     13 | 小灵通/固话20元充值卡                     |      19.00 ||       29 |     14 | 移动100元充值卡                       |      90.00 ||       30 |     14 | 移动20元充值卡                        |      18.00 ||       27 |     15 | 联通100元充值卡                       |      95.00 ||       28 |     15 | 联通50元充值卡                        |      45.00 |+----------+--------+------------------------------+------------+32 rows in set (0.03 sec)mysql> select * from g4 group by cat_id;+----------+--------+------------------------------+------------+| goods_id | cat_id | goods_name                   | shop_price |+----------+--------+------------------------------+------------+|       16 |      2 | 恒基伟业G101                       |     823.33 ||       22 |      3 | 多普达Touch HD                    |    5999.00 ||       18 |      4 | 夏新T5                           |    2878.00 ||       23 |      5 | 诺基亚N96                         |    3700.00 ||        7 |      8 | 诺基亚N85原装立体声耳机HS-82                  |     100.00 ||        6 |     11 | 胜创KINGMAX内存卡                  |      42.00 ||       25 |     13 | 小灵通/固话50元充值卡                     |      48.00 ||       29 |     14 | 移动100元充值卡                       |      90.00 ||       27 |     15 | 联通100元充值卡                       |      95.00 |+----------+--------+------------------------------+------------+9 rows in set (0.00 sec)

这样就可以顺利得到结果了。

0 0