乐趣区

关于mysql:MySQL视图介绍

jiezi

视图定义

视图是一种有构造的虚构表，自身不存放数据，视图中数据来源于实在的表，实在的表也被称之为基表。

视图作用

  • 1. 简化 SQL 语句: 比方将一条多表联结查问做成视图，后续间接 SELECT * 视图进行查问即可。
  • 2. 绝对平安的权限管制: 针对不同利用只须要建设对应视图表，提供所须要的字段信息，利用无奈感知到其余字段信息。
  • 3. 保障数据安全: 对视图进行删除操作不会影响基表的数据。

创立视图

根本语法 

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

视图算法

ALGORITHM = UNDEFINED | MERGE | TEMPTABLE

UNDEFINED # 未定义 (默认的), 由零碎自行调整

MERGE # 合并算法: 零碎应该先将视图对应的 select 语句与内部查问视图的 select 语句进行合并, 而后执行 (效率高: 常态)

TEMPTABLE # 长期表算法: 零碎应该先执行视图的 select 语句, 后执行内部查问语句

create view 视图名字 as select 语句

select 语句能够是一般查问、连贯查问、联结查问、子查问等各种类型。

创立单表视图: 基表只有一个 

[root@GreatSQL][test]>create view v_t_name as select * from t_name;
Query OK, 0 rows affected (0.14 sec)

[root@GreatSQL][test]>show create view v_t_name\G;
*************************** 1. row ***************************
                View: v_t_name
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_t_name` AS select `t_name`.`id` AS `id`,`t_name`.`name` AS `name` from `t_name`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.01 sec)

ERROR:
No query specified


[root@GreatSQL][test]>desc v_t_name;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | 0       |       |
| name  | varchar(64) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

创立多表视图: 基表起源至多两个 

[root@GreatSQL][test]>create view v_t_name_user(id,name,email,age) as select t_name.id,t_name.name,t_user.email,t_user.age from t_name,t_user where t_name.id=t_user.id;
Query OK, 0 rows affected (0.04 sec)

[root@GreatSQL][test]>show create view v_t_name_user\G;
*************************** 1. row ***************************
                View: v_t_name_user
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_t_name_user` (`id`,`name`,`email`,`age`) AS select `t_name`.`id` AS `id`,`t_name`.`name` AS `name`,`t_user`.`email` AS `email`,`t_user`.`age` AS `age` from (`t_name` join `t_user`) where (`t_name`.`id` = `t_user`.`id`)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.01 sec)

ERROR:
No query specified


[root@GreatSQL][test]>desc v_t_name_user;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int          | NO   |     | 0       |       |
| name  | varchar(64)  | YES  |     | NULL    |       |
| email | varchar(255) | YES  |     | NULL    |       |
| age   | tinyint      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

应用视图

应用视图次要是为了查问: 将视图当做表一样查问即可 

[root@GreatSQL][test]>select * from v_t_name;
+------------+------+
| id         | name |
+------------+------+
|          1 | chen |
| 2147483646 | wu   |
| 2147483647 | ling |
+------------+------+
3 rows in set (0.00 sec)

批改视图

视图自身不可批改, 然而视图的起源是能够批改的.

批改视图: 批改视图自身的起源语句 (select 语句)

alter view 视图名字 as 新的 select 语句;

[root@GreatSQL][test]>alter view v_t_name as select t_name.name from t_name;
Query OK, 0 rows affected (0.05 sec)

(Wed Dec 15 01:53:44 2021)[root@GreatSQL][test]>select * from v_t_name;
+------+
| name |
+------+
| chen |
| wu   |
| ling |
+------+
3 rows in set (0.00 sec)

删除视图 

[root@GreatSQL][test]>drop view v_t_name;
Query OK, 0 rows affected (0.04 sec)

视图写入数据

单表视图测试写入 

[root@GreatSQL][test]>insert into v_t_name values(5,'long');
Query OK, 1 row affected (0.02 sec)

(Wed Dec 15 03:27:17 2021)[root@GreatSQL][test]>select * from t_name;
+------------+------+
| id         | name |
+------------+------+
|          1 | chen |
|          4 | wang |
|          5 | long |
| 2147483646 | wu   |
| 2147483647 | ling |
+------------+------+
5 rows in set (0.00 sec)

# 局部字段的视图也能够写入
[root@GreatSQL][test]>create view v_t_name2 as select t_name.id from t_name;
Query OK, 0 rows affected (0.04 sec)

(Wed Dec 15 03:29:11 2021)[root@GreatSQL][test]>desc v_t_name2;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | NO   |     | 0       |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

(Wed Dec 15 03:29:17 2021)[root@GreatSQL][test]>insert into v_t_name2 values(6);
Query OK, 1 row affected (0.02 sec)

(Wed Dec 15 03:29:29 2021)[root@GreatSQL][test]>select * from t_name;
+------------+------+
| id         | name |
+------------+------+
|          1 | chen |
|          4 | long |
|          5 | long |
|          6 | NULL |
| 2147483646 | wu   |
| 2147483647 | ling |
+------------+------+
6 rows in set (0.00 sec)

多表视图测试写入 

# 联结视图不能写入数据
(Wed Dec 15 03:54:59 2021)[root@GreatSQL][test]>insert into v_t_name_user values(100,'chen','123456789@qq.com','16');
ERROR 1394 (HY000): Can not insert into join view 'test.v_t_name_user' without fields list

视图更新数据

单表视图更新数据 

[root@GreatSQL][test]>update v_t_name set name='yang' where id='6';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(Wed Dec 15 03:59:12 2021)[root@GreatSQL][test]>select * from v_t_name;
+------------+------+
| id         | name |
+------------+------+
|          1 | chen |
|          4 | long |
|          5 | long |
|          6 | yang |
| 2147483646 | wu   |
| 2147483647 | ling |
+------------+------+
6 rows in set (0.00 sec)

多表视图更新数据 

[root@GreatSQL][test]>select * from v_t_name_user;
+---------+------+------------------+------+
| id      | name | email            | age  |
+---------+------+------------------+------+
| 1000001 | he   | 123456789@qq.com |   12 |
+---------+------+------------------+------+
1 row in set (0.00 sec)

(Wed Dec 15 04:02:22 2021)[root@GreatSQL][test]>update v_t_name_user set name='ma' where id='1000001';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(Wed Dec 15 04:02:55 2021)[root@GreatSQL][test]>select * from v_t_name_user;
+---------+------+------------------+------+
| id      | name | email            | age  |
+---------+------+------------------+------+
| 1000001 | ma   | 123456789@qq.com |   12 |
+---------+------+------------------+------+
1 row in set (0.00 sec)

视图删除数据

单表视图删除数据 

[root@GreatSQL][test]>delete from v_t_name where id=1;
Query OK, 1 row affected (0.02 sec)

(Wed Dec 15 04:07:03 2021)[root@GreatSQL][test]>select * from t_name;
+------------+------+
| id         | name |
+------------+------+
|          4 | long |
|          5 | long |
|          6 | yang |
|     100001 | he   |
|    1000001 | ma   |
| 2147483646 | wu   |
| 2147483647 | ling |
+------------+------+
7 rows in set (0.00 sec)

多表视图删除数据 

# 多表联结视图无奈执行删除操作
[root@GreatSQL][test]>delete from v_t_name_user where id=1000001;
ERROR 1395 (HY000): Can not delete from join view 'test.v_t_name_user'

Enjoy GreatSQL :)

文章举荐：

GreatSQL 季报（2021.12.26）

https://mp.weixin.qq.com/s/FZ…

技术分享 |sysbench 压测工具用法浅析

https://mp.weixin.qq.com/s/m1…

故障剖析 | linux 磁盘 io 利用率高，剖析的正确姿态

https://mp.weixin.qq.com/s/7c…

技术分享 | 闪回在 MySQL 中的实现和改良

https://mp.weixin.qq.com/s/6j…

万答 #20，索引下推如何进行数据过滤

https://mp.weixin.qq.com/s/pt…

对于 GreatSQL

GreatSQL 是由万里数据库保护的 MySQL 分支，专一于晋升 MGR 可靠性及性能，反对 InnoDB 并行查问个性，是实用于金融级利用的 MySQL 分支版本。

Gitee:

https://gitee.com/GreatSQL/Gr…

GitHub:

https://github.com/GreatSQL/G…

Bilibili：

https://space.bilibili.com/13…

微信 &QQ 群：

可搜寻增加 GreatSQL 社区助手微信好友，发送验证信息“加群”退出 GreatSQL/MGR 交换微信群

QQ 群：533341697

微信小助手：wanlidbc

本文由博客一文多发平台 OpenWrite 公布！

退出移动版