MySQL之视图详解

共计 3984 个字符，预计需要花费 10 分钟才能阅读完成。

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'}]