拓展阅读

Mysql View

丑话说在前面，对于企业级项目是不推荐使用视图的。但是东西用在合适的地方就是最好的。

参考资料：

视图简介

一、为何使用视图

安全

用户权限与视图绑定。实际上可以通过shiro或者spring-security控制。

查询性能提高
虚拟表不用修改表结构，可以完成某些业务需求。这一点比较实用。

二、视图的工作机制

视图的工作机制

当调用视图的时候，才会执行视图中的sql，进行取数据操作。视图的内容没有存储，而是在视图被引用的时候才派生出数据。这样不会占用空间，由于是即时引用，视图的内容总是与真实表的内容是一致的。

如此设计的优点

节省空间。维护好真实表的内容，就可保证视图的完整性。

CRUD

为了测试。创建一个user表。DDL如下：

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键,自增',
  `mobile` varchar(20) NOT NULL COMMENT '手机号',
  `password` varchar(64) NOT NULL COMMENT '密码',
  `salt` varchar(128) NOT NULL COMMENT '密码盐',
  `nickname` varchar(32) NOT NULL DEFAULT '' COMMENT '昵称',
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除 {0:未删除, 1:已删除}',
  `created_time` datetime NOT NULL COMMENT '创建时间',
  `updated_time` datetime NOT NULL COMMENT '最后更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `mobile_UNIQUE` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表'

数据初始化：

INSERT INTO `user` (mobile, password, salt, nickname, is_deleted, created_time, updated_time) VALUES ('13062666053', 'a7097b4e5fa3c1d1165e66b2d72a2d060f288d64', '649f6afc93874cf8', '某小宝', 0, '2016-09-17 14:08:38', '2016-09-17 14:08:38');
INSERT INTO `user` (mobile, password, salt, nickname, is_deleted, created_time, updated_time) VALUES ('13012345678', '67917009d2faccc292171bb16084d7410616cdcb', '7c3186569813f1c3', '某宝', 0, '2016-09-17 14:08:38', '2016-09-17 14:08:38');
INSERT INTO `user` (mobile, password, salt, nickname, is_deleted, created_time, updated_time) VALUES ('13111111111', '847100b326a0574c0cdcc22483e13cb39accdefd', '48c3e9cfac9ac472', '11', 0, '2016-09-17 18:29:25', '2016-09-17 18:29:25');
INSERT INTO `user` (mobile, password, salt, nickname, is_deleted, created_time, updated_time) VALUES ('13111111112', '1d4fe693d850b2e5de5f348c69bfe15a78249022', 'c1d2a40cd8db1a88', '12', 0, '2016-09-17 18:30:10', '2016-09-17 18:30:10')

Create

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

假设我们只关心用户的信息，如下：

CREATE VIEW v_user_info AS SELECT nickname, mobile, is_deleted FROM `user`;

Read

视图创建成功之后，可以像普通表一样对待。

mysql> select * from v_user_info;
+-----------+-------------+------------+
| nickname  | mobile      | is_deleted |
+-----------+-------------+------------+
| 某小宝    | 13062666053 |          0 |
| 某宝      | 13012345678 |          0 |
| 11        | 13111111111 |          0 |
| 12        | 13111111112 |          0 |
+-----------+-------------+------------+
4 rows in set (0.00 sec)

mysql> desc v_user_info;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| nickname   | varchar(32) | NO   |     |         |       |
| mobile     | varchar(20) | NO   |     | NULL    |       |
| is_deleted | tinyint(4)  | NO   |     | 0       |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Update

对视图的数据修改会直接反应在真实表之上。

mysql> select * from user where nickname='11';
+----+-------------+------------------------------------------+------------------+----------+------------+---------------------+---------------------+
| id | mobile      | password                                 | salt             | nickname | is_deleted | created_time        | updated_time        |
+----+-------------+------------------------------------------+------------------+----------+------------+---------------------+---------------------+
|  5 | 13111111111 | 847100b326a0574c0cdcc22483e13cb39accdefd | 48c3e9cfac9ac472 | 11       |          0 | 2016-09-17 18:29:25 | 2016-09-17 18:29:25 |
+----+-------------+------------------------------------------+------------------+----------+------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> update v_user_info set mobile='10123456789' where nickname='11';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user where nickname='11';
+----+-------------+------------------------------------------+------------------+----------+------------+---------------------+---------------------+
| id | mobile      | password                                 | salt             | nickname | is_deleted | created_time        | updated_time        |
+----+-------------+------------------------------------------+------------------+----------+------------+---------------------+---------------------+
|  5 | 10123456789 | 847100b326a0574c0cdcc22483e13cb39accdefd | 48c3e9cfac9ac472 | 11       |          0 | 2016-09-17 18:29:25 | 2016-09-17 18:29:25 |
+----+-------------+------------------------------------------+------------------+----------+------------+---------------------+---------------------+
1 row in set (0.00 sec)

我们也可以修改视图的结构，就像修改表结构一样。比如我们不想关心is_deleted了。

mysql> alter view v_user_info AS SELECT mobile, nickname FROM user;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_user_info;
+-------------+-----------+
| mobile      | nickname  |
+-------------+-----------+
| 13062666053 | 某小宝    |
| 13012345678 | 某宝      |
| 10123456789 | 11        |
| 13111111112 | 12        |
+-------------+-----------+
4 rows in set (0.00 sec)

Drop

DROP VIEW VIEW_NAME;

比如我们不想要这张视图了

mysql> drop view v_user_info;
Query OK, 0 rows affected (0.00 sec)

视图的增删改查

View

何为视图

视图是存放数据的一个接口，也可以说是虚拟的表。这些数据可以是从一个或几个基本表（或视图）的数据。也可以是用户自已定义的数据。其实视图里面不存放数据的，数据还是放在基本表里面，基本表里面的数据发生变动时，视图里面的数据随之变动。

作用

1、视图可以让查询变得很清楚

2、保护数据库的重要数据，给不同的人看不同的数据

3、对于视图能完成的事情，查询语句代码本身也能完成。视图的优点直接修改数据库即可，不用修改源代码。

视图的类型

mysql的视图有三种类型：MERGE、TEMPTABLE、UNDEFINED。如果没有ALGORITHM子句，默认算法是UNDEFINED（未定义的）。算法会影响MySQL处理视图的方式。

1、MERGE，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。

2、TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。

3、UNDEFINED，MySQL将选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。

Create View

Create view 用于创建一个视图。

命令

mysql> ? CREATE VIEW
Name: 'CREATE VIEW'
Description:
Syntax:
CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

The CREATE VIEW statement creates a new view, or replaces an existing view if the OR REPLACE clause is given. If the view does not exist, CREATE OR REPLACE VIEW is the same as CREATE VIEW.

Query View

查询就相当于查询普通的表。

Alter View

Alter View 用于修改视图。

命令

mysql> ? alter view
Name: 'ALTER VIEW'
Description:
Syntax:
ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

This statement changes the definition of a view, which must exist. The syntax is similar to that for CREATE VIEW and the effect is the same as for CREATE OR REPLACE VIEW. See [HELP CREATE VIEW]. This statement requires the CREATE VIEW and DROP privileges for the view, and some privilege for each column referred to in the SELECT statement. ALTER VIEW is permitted only to the definer or users with the SUPER privilege.

Drop View

Drop View 用于删除一个视图。

mysql> ? drop view
Name: 'DROP VIEW'
Description:
Syntax:
DROP VIEW [IF EXISTS]
    view_name [, view_name] ...
    [RESTRICT | CASCADE]

DROP VIEW removes one or more views. You must have the DROP privilege for each view. If any of the views named in the argument list do not exist, MySQL returns an error indicating by name which nonexisting views it was unable to drop, but it also drops all of the views in the list that do exist.

The IF EXISTS clause prevents an error from occurring for views that don’t exist. When this clause is given, a NOTE is generated for each nonexistent view. See [HELP SHOW WARNINGS].

RESTRICT and CASCADE, if given, are parsed and ignored.

Sth. Limit

视图虽然和表很类似，但还是有区别。

拓展阅读
Mysql View
视图简介
CRUD
视图的增删改查
- View
- Create View
- Query View
- Alter View
- Drop View
- Sth. Limit

MySQL-00-View 视图