回复
MySQL视图介绍
Only1You1
发布于 2022-8-22 15:59
浏览
0收藏
视图定义
视图是一种有结构的虚拟表，本身不存放数据，视图中数据来源于真实的表，真实的表也被称之为基表。
视图作用
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'
分类
标签
已于2022-8-22 15:59:15修改
赞
收藏
回复
相关推荐
