MySQL从5.0.1版本开始提供视图的功能,本次博客就来介绍一下视图,本次博客基于的版本是mysql-5.7.19。
什么是视图?
视图(view)是一种虚拟存在的表,视图可以理解为是一个容器,表通过条件查询之后,将查询后的结果放入这个容器内,然后给容器命名后即为视图。
视图相对于表的优势:
1.简单,使用视图的用户不必关系后面的表,只需要使用过滤好的内容就行了;
2.安全,因为对表的全新不能限制到表的行或者是列,所以可以通过视图来限制用户对表的访问权限;
3.数据独立,确定了视图的结构之后,如果给原来的表增加了列,并不会影响视图,增加行,视图的相对于的行也会增加,如果源表的列名称发生了改变,可以通过修改视图来解决。
创建和查看视图
语法:
CREATE VIEW view_name AS sql
现在有表如下:
mysql> select * fromuserinfo;+----+----------+--------+
| id | username | passwd |
+----+----------+--------+
| 1 | alex | 123 |
| 2 | frank | 123 |
| 3 | rose | 312 |
| 4 | tom | qqq |
| 5 | jack | qwer |
+----+----------+--------+
5 rows in set (0.00 sec)
来创建视图:
mysql> create view user_view as select username fromuserinfo;
Query OK,0 rows affected (0.01 sec)
show tables 命令不仅可以显示表的名字,也可以显示视图的名字:
mysql>show tables;+---------------+
| Tables_in_db2 |
+---------------+
| user_view |
| userinfo |
+---------------+
2 rows in set (0.00 sec)
可以通过一下命令显示视图的信息:
mysql> show table status like 'user_view'\G;*************************** 1. row ***************************Name: user_view
Engine:NULLVersion:NULLRow_format:NULLRows:NULLAvg_row_length:NULLData_length:NULLMax_data_length:NULLIndex_length:NULLData_free:NULLAuto_increment:NULLCreate_time:NULLUpdate_time:NULLCheck_time:NULLCollation:NULLChecksum:NULLCreate_options:NULLComment:VIEW
1 row in set (0.00sec)
ERROR:
No query specified
如果想要查看某个视图的定义,也可以使用如下命令查看:
mysql> show create viewuser_view \G;*************************** 1. row ***************************
View: user_viewCreate View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `user_view` AS select `userinfo`.`username` AS `username` from`userinfo`
character_set_client: utf8
collation_connection: utf8_general_ci1 row in set (0.00sec)
ERROR:
No query specified
修改视图
先来看这个例子:
查看视图user_view的内容:
mysql> select * fromuser_view;+----------+
| username |
+----------+
| alex |
| frank |
| rose |
| tom |
| jack |
+----------+
5 rows in set (0.00 sec)
在原表里面插入行:
mysql> insert into userinfo(username,passwd) values('coco','123');
Query OK,1 row affected (0.00sec)
mysql> select * fromuser_view;+----------+
| username |
+----------+
| alex |
| frank |
| rose |
| tom |
| jack |
| coco |
+----------+
6 rows in set (0.00 sec)
说明视图是可以被原始表更新的。
如果现在把原表的username列的名称改为uname,那么user_view视图的就会出错了,这个时候就需要修改视图了。
mysql> alter table userinfo change username uname varchar(20);
Query OK,0 rows affected (0.01sec)
Records:0 Duplicates: 0 Warnings: 0
修改视图的语法如下:
ALTER VIEW view_name AS sql
修改视图user_view而且增加了id列:
mysql> alter view user_view as select id,uname fromuserinfo;
Query OK,0 rows affected (0.01sec)
mysql> select * fromuser_view;+----+-------+
| id | uname |
+----+-------+
| 1 | alex |
| 2 | frank |
| 3 | rose |
| 4 | tom |
| 5 | jack |
| 6 | coco |
| 7 | saber |
+----+-------+
7 rows in set (0.00 sec)
那么视图的数据是否可以修改呢?视图数据修改后是否会影响原表呢?下面通过一个例子看一下:
mysql> update user_view set uname='lancer' where id=7;
Query OK,1 row affected (0.00sec)
Rows matched:1 Changed: 1 Warnings: 0mysql> select * fromuser_view;+----+--------+
| id | uname |
+----+--------+
| 1 | alex |
| 2 | frank |
| 3 | rose |
| 4 | tom |
| 5 | jack |
| 6 | coco |
| 7 | lancer |
+----+--------+
7 rows in set (0.00sec)
mysql> select * fromuserinfo;+----+--------+--------+
| id | uname | passwd |
+----+--------+--------+
| 1 | alex | 123 |
| 2 | frank | 123 |
| 3 | rose | 312 |
| 4 | tom | qqq |
| 5 | jack | qwer |
| 6 | coco | 123 |
| 7 | lancer | 123 |
+----+--------+--------+
7 rows in set (0.00 sec)
通过以上例子说明。修改视图中的数据是可行的而且原表中的数据也会被改变。
删除视图
用户可以一次性的删除一个或者多个视图,语法如下:
DROP VIEW view_name1.view_name2...
来看下面的例子:
mysql>show tables;+---------------+
| Tables_in_db2 |
+---------------+
| passwd_view1 |
| passwd_view2 |
| user_view_2 |
| userinfo |
+---------------+
4 rows in set (0.00sec)
mysql> drop viewpasswd_view1;
Query OK,0 rows affected (0.00sec)
mysql>show tables;+---------------+
| Tables_in_db2 |
+---------------+
| passwd_view2 |
| user_view_2 |
| userinfo |
+---------------+
3 rows in set (0.00 sec)
使用pymysql操作视图
操作视图其实和操作表是一样的,来看例子:
import pymysql
config={
"host":"127.0.0.1",
"user":"root",
"password":"LBLB1212@@",
"database":"db2",
"charset":"utf8"
}
db= pymysql.connect(**config)with db.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
sql="SELECT * FROMpasswd_view2"cursor.execute(sql)
res= cursor.fetchall()print(res)cursor.close()
db.close()
#运行结果[{'uname': 'alex', 'passwd': '123'}, {'uname': 'frank', 'passwd': '123'}, {'uname': 'coco', 'passwd': '123'}, {'uname': 'lancer', 'passwd': '123'}]