MySQL 视图

视图

简介：
视图是一个虚拟表，是从数据库中一个或多个表中导出来的表。视图还可以从已经存在的视图的基础上定义。视图一经定义便存储在数据库中，与其相对应的数据并没有像表那样在数据库中再存储一份，通过视图看到的数据只是存放在基本表中的数据。对视图的操作与对表的操作一样，可以对其进行查询、修改和删除。当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化；同时，若基本表的数据发生变化，则这种变化也可以自动反映到视图中。
优点：

简单化：看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化它们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
安全性：通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。
逻辑数据独立性：视图可帮助用户屏蔽真实表结构变化带来的影响。

创建视图

创建视图使用CREATE VIEW语句，语法：CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGEI TEMPTABLE} ] VIEW view_ name [ (column_ list) ] AS SELECT statement [WITH [CASCADED | LOCAL] CHECK OPTION]。

CREATE表示创建新的视图；
REPLACE表示替换已经创建的视图；
ALGORITHM表示视图选择的算法，UNDEFINED表示MySQL将自动选择算法；MERGE表示将使用的视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部分；TEMPTABLE表示将视图的结果存入临时表，然后用临时表来执行语句。
view_name为视图的名称，
column_list为属性列；
SELECT_statement表示SELECT语句；
WITH [CASCADED | LOCAL]，CASCADED与LOCAL为可选参数，CASCADED为默认值，表示更新视图时要满足所有相关视图和表的条件；LOCAL表示更新视图时满足该视图本身定义的条件即可。

CHECK OPTION参数表示视图在更新时保证在视图的权限范围之内。

mysql> create table user(
  -> id int primary key auto_increment,
  -> name varchar(150)
  -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table user_info(
  -> user_id int,
  -> department varchar(250),
  -> age int,
  -> sex enum('male', 'female')
  -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into user (name) values ('ronie'), ('jack'), ('kelly'), ('lucy');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into user_info values 
  -> (1, 'IT', 23, 'male'),
  -> (2, 'art', 34, 'male'),
  -> (3, 'sale', 33, 'female'),
  -> (4, 'IT', 24, 'female');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----+-------+
| id | name  |
+----+-------+
|  1 | ronie |
|  2 | jack  |
|  3 | kelly |
|  4 | lucy  |
+----+-------+
 4 rows in set (0.00 sec)

mysql> select * from user_info;
+---------+------------+------+--------+
| user_id | department | age  | sex    |
+---------+------------+------+--------+
|       1 | IT         |   23 | male   |
|       2 | art        |   34 | male   |
|       3 | sale       |   33 | female |
|       4 | IT         |   24 | female |
+---------+------------+------+--------+
 4 rows in set (0.00 sec)

mysql> create view userinfo (id, name, department, age, sex) as select user.id, user.name, department, age, sex from user, user_info where user_id=id;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from userinfo;
+----+-------+------------+------+--------+
| id | name  | department | age  | sex    |
+----+-------+------------+------+--------+
|  1 | ronie | IT         |   23 | male   |
|  2 | jack  | art        |   34 | male   |
|  3 | kelly | sale       |   33 | female |
|  4 | lucy  | IT         |   24 | female |
+----+-------+------------+------+--------+
 4 rows in set (0.00 sec)

查看视图

查看视图是查看数据库中已存在的视图的定义。查看视图必须要有SHOW VIEW的权限，MySQL数据库下的user表中保存着这个信息。查看视图的方法包括DESCRIBE、SHOW TABLE STATUS和SHOW CREATE VIEW.

DESCRIBE语句

-- DESCRIBE一般情况下都简写成DESC，输入这个命令的执行结果和输入DESCRIBE的执行结果是一样的。
mysql> desc userinfo;
+------------+-----------------------+------+-----+---------+-------+
| Field      | Type                  | Null | Key | Default | Extra |
+------------+-----------------------+------+-----+---------+-------+
| id         | int                   | NO   |     | 0       |       |
| name       | varchar(150)          | YES  |     | NULL    |       |
| department | varchar(250)          | YES  |     | NULL    |       |
| age        | int                   | YES  |     | NULL    |       |
| sex        | enum('male','female') | YES  |     | NULL    |       |
+------------+-----------------------+------+-----+---------+-------+
 5 rows in set (0.00 sec)

SHOW TABLE STATUS语句

-- 表的说明Comment的值为VIEW，说明该表为视图，其他的信息为NULL，说明这是一个虚表。
mysql> show table status like 'userinfo' \G;
*************************** 1. row ***************************
        Name: userinfo
      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: 2022-10-14 09:47:44
 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

SHOW CREATE VIEW语句

mysql> show create view userinfo \G;
*************************** 1. row ***************************
             View: userinfo
     Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `userinfo` (`id`,`name`,`department`,`age`,`sex`) AS select `user`.`id` AS `id`,`user`.`name` AS `name`,`user_info`.`department` AS `department`,`user_info`.`age` AS `age`,`user_info`.`sex` AS `sex` from (`user` join `user_info`) where (`user_info`.`user_id` = `user`.`id`)
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
 1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

views表中查看

mysql> SELECT * FROM information_schema.views where table_name='userinfo' \G;
*************************** 1. row ***************************
    TABLE_CATALOG: def
     TABLE_SCHEMA: ronie
       TABLE_NAME: userinfo
  VIEW_DEFINITION: select `ronie`.`user`.`id` AS `id`,`ronie`.`user`.`name` AS `name`,`ronie`.`user_info`.`department` AS `department`,`ronie`.`user_info`.`age` AS `age`,`ronie`.`user_info`.`sex` AS `sex` from `ronie`.`user` join `ronie`.`user_info` where (`ronie`.`user_info`.`user_id` = `ronie`.`user`.`id`)
     CHECK_OPTION: NONE
     IS_UPDATABLE: YES
          DEFINER: root@localhost
    SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
 1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

修改视图

MySQL中通过CREATE OR REPLACE VIEW语句和ALTER语句来修改视图。

使用CREATE OR REPLACE VIEW语句

mysql> create or replace view userinfo (name, department, sex) as select  user.name, department, sex from user, user_info where user_id=id;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from userinfo;
+-------+------------+--------+
| name  | department | sex    |
+-------+------------+--------+
| ronie | IT         | male   |
| jack  | art        | male   |
| kelly | sale       | female |
| lucy  | IT         | female |
+-------+------------+--------+
 4 rows in set (0.00 sec)

mysql> desc userinfo;
+------------+-----------------------+------+-----+---------+-------+
| Field      | Type                  | Null | Key | Default | Extra |
+------------+-----------------------+------+-----+---------+-------+
| name       | varchar(150)          | YES  |     | NULL    |       |
| department | varchar(250)          | YES  |     | NULL    |       |
| sex        | enum('male','female') | YES  |     | NULL    |       |
+------------+-----------------------+------+-----+---------+-------+
 3 rows in set (0.00 sec)

使用ALTER语句

mysql> alter view userinfo as select name, sex from user, user_info where id=user_id;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from userinfo;
+-------+--------+
| name  | sex    |
+-------+--------+
| ronie | male   |
| jack  | male   |
| kelly | female |
| lucy  | female |
+-------+--------+
 4 rows in set (0.01 sec)

mysql> desc userinfo;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| name  | varchar(150)          | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
 2 rows in set (0.00 sec)

更新视图

当视图中包含有如下内容时，视图的更新操作将不能被执行：

视图中不包含基表中被定义为非空的列。
在定义视图的SELECT语句后的字段列表中使用了数学表达式。
在定义视图的SELECT语句后的字段列表中使用聚合函数。
在定义视图的SELECT语句中使用了DISTINCT、UNION、TOP、GROUP BY或HAVING子句。

视图的更新操作

使用UPDATE语句

mysql> create or replace view userinfo (name, department, sex) as select  user.name, department, sex from user, user_info where user_id=id;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from userinfo;
+-------+------------+--------+
| name  | department | sex    |
+-------+------------+--------+
| ronie | IT         | male   |
| jack  | art        | male   |
| kelly | sale       | female |
| lucy  | IT         | female |
+-------+------------+--------+
 4 rows in set (0.00 sec)

mysql> select * from user;
+----+-------+
| id | name  |
+----+-------+
|  1 | ronie |
|  2 | jack  |
|  3 | kelly |
|  4 | lucy  |
+----+-------+
 4 rows in set (0.00 sec)

mysql> select * from user_info;
+---------+------------+------+--------+
| user_id | department | age  | sex    |
+---------+------------+------+--------+
|       1 | IT         |   23 | male   |
|       2 | art        |   34 | male   |
|       3 | sale       |   33 | female |
|       4 | IT         |   24 | female |
+---------+------------+------+--------+
 4 rows in set (0.00 sec)

mysql> update userinfo set department='sale' where name='ronie';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 对视图更新后，基本表的内容也会更新，同样当对基本表更新后，视图中的内容也会更新。
mysql> select * from userinfo;
+-------+------------+--------+
| name  | department | sex    |
+-------+------------+--------+
| ronie | sale       | male   |
| jack  | art        | male   |
| kelly | sale       | female |
| lucy  | IT         | female |
+-------+------------+--------+
 4 rows in set (0.00 sec)

mysql> select * from user_info;
+---------+------------+------+--------+
| user_id | department | age  | sex    |
+---------+------------+------+--------+
|       1 | sale       |   23 | male   |
|       2 | art        |   34 | male   |
|       3 | sale       |   33 | female |
|       4 | IT         |   24 | female |
+---------+------------+------+--------+
 4 rows in set (0.00 sec)

-- 通常来说不能向视图删除插入数据, 除非视图中的所有数据都来自同一个表,
mysql> insert into userinfo (name, department, sex) values ('mike', 'IT', 'male');
ERROR 1393 (HY000): Can not modify more than one base table through a join view 'ronie.userinfo'

使用insert语句

mysql> insert into user (name) values ('tom');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+-------+
| id | name  |
+----+-------+
|  1 | ronie |
|  2 | jack  |
|  3 | kelly |
|  4 | lucy  |
|  5 | tom   |
+----+-------+
 5 rows in set (0.00 sec)

mysql> insert into user_info values(5, 'IT', 22, 'male');
Query OK, 1 row affected (0.00 sec)

mysql> select * from userinfo;
+-------+------------+--------+
| name  | department | sex    |
+-------+------------+--------+
| ronie | sale       | male   |
| jack  | art        | male   |
| kelly | sale       | female |
| lucy  | IT         | female |
| tom   | IT         | male   |
+-------+------------+--------+
 5 rows in set (0.00 sec)

删除视图

使用DROP VIEW语句

mysql> drop view if exists userinfo;
Query OK, 0 rows affected (0.00 sec)

mysql> drop view if exists userinfo;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+--------------------------------+
| Level | Code | Message                        |
+-------+------+--------------------------------+
| Note  | 1051 | Unknown table 'ronie.userinfo' |
+-------+------+--------------------------------+
 1 row in set (0.00 sec)

视图

创建视图

查看视图

修改视图

更新视图

删除视图

MySQL 权限管理

MySQL 触发器

Ronie

Comments | NOTHING

视图

创建视图

查看视图

修改视图

更新视图

删除视图

MySQL 权限管理

MySQL 触发器

Ronie

Comments | NOTHING

你想搜索什么...