视图定义
视图是一种有构造的虚构表，自身不存放数据，视图中数据来源于实在的表，实在的表也被称之为基表。
视图作用
- 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: utf8collation_connection: utf8_general_ci1 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: utf8collation_connection: utf8_general_ci1 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 公布！