[日期:2017-09-25] | [字体:大 中 小] |
MySQL从5.0.1版本开始提供视图的功能,本次博客就来介绍一下视图,本次博客基于的版本是mysql-5.7.19。
视图(view)是一种虚拟存在的表,视图可以理解为是一个容器,表通过条件查询之后,将查询后的结果放入这个容器内,然后给容器命名后即为视图。
视图相对于表的优势:
1.简单,使用视图的用户不必关系后面的表,只需要使用过滤好的内容就行了;
2.安全,因为对表的全新不能限制到表的行或者是列,所以可以通过视图来限制用户对表的访问权限;
3.数据独立,确定了视图的结构之后,如果给原来的表增加了列,并不会影响视图,增加行,视图的相对于的行也会增加,如果源表的列名称发生了改变,可以通过修改视图来解决。
语法:
- CREATE VIEW view_name AS sql
现在有表如下:
- mysql> select * from userinfo;
- +----+----------+--------+
- | 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 from userinfo;
- 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: NULL
- Version: NULL
- Row_format: NULL
- Rows: NULL
- Avg_row_length: NULL
- Data_length: NULL
- Max_data_length: NULL
- Index_length: NULL
- Data_free: NULL
- Auto_increment: NULL
- Create_time: NULL
- Update_time: NULL
- Check_time: NULL
- Collation: NULL
- Checksum: NULL
- Create_options: NULL
- Comment: VIEW
- 1 row in set (0.00 sec)
- ERROR:
- No query specified
如果想要查看某个视图的定义,也可以使用如下命令查看:
- mysql> show create view user_view \G;
- *************************** 1. row ***************************
- View: user_view
- Create 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_ci
- 1 row in set (0.00 sec)
- ERROR:
- No query specified
先来看这个例子:
查看视图user_view的内容:
- mysql> select * from user_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.00 sec)
- mysql> select * from user_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.01 sec) Records: 0 Duplicates: 0 Warnings: 0
修改视图的语法如下:
- ALTER VIEW view_name AS sql
修改视图user_view而且增加了id列:
- mysql> alter view user_view as select id,uname from userinfo;
- Query OK, 0 rows affected (0.01 sec)
- mysql> select * from user_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.00 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
- mysql> select * from user_view;
- +----+--------+
- | id | uname |
- +----+--------+
- | 1 | alex |
- | 2 | frank |
- | 3 | rose |
- | 4 | tom |
- | 5 | jack |
- | 6 | coco |
- | 7 | lancer |
- +----+--------+
- 7 rows in set (0.00 sec)
- mysql> select * from userinfo;
- +----+--------+--------+
- | 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.00 sec)
- mysql> drop view passwd_view1;
- Query OK, 0 rows affected (0.00 sec)
- mysql> show tables;
- +---------------+
- | Tables_in_db2 |
- +---------------+
- | passwd_view2 |
- | user_view_2 |
- | userinfo |
- +---------------+
- 3 rows in set (0.00 sec)
操作视图其实和操作表是一样的,来看例子:
- 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 * FROM passwd_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'}]
来源: http://www.linuxidc.com/Linux/2017-09/147062.htm