MySQL视图详解

最新推荐文章于 2023-04-26 10:08:38 发布

康春楠

最新推荐文章于 2023-04-26 10:08:38 发布

阅读量222

点赞数 1

分类专栏： MySQL8教程文章标签： mysql sql

本文链接：https://blog.csdn.net/huntpython/article/details/108801005

版权

MySQL8教程专栏收录该内容

1 篇文章 0 订阅

订阅专栏

系列文章目录

文章目录

系列文章目录
一、概念
二、创建视图
三、修改视图
- 1.使用CREATE OR REPLACE
- 2.使用 ALTER
四、更新视图
- UPDATE、INSERT、DELETE
五、删除视图
六、查看视图

一、概念

视图是由实际存在于数据库中一个或多个表导出的虚拟表。身为表，虽然是虚拟的，但也可以使用SELECT语句查询语句，使用INSERT、UPDATE、DELETE语句修改记录。下面举个例子来更具体化的了解视图的作用

假设我现在的数据库中存在着如下的表：
customers表：c_id(每位顾客的唯一编号列）、emial(顾客的邮箱列)
products表：p_id(产品的唯一编号列)、c_id(订购过该产品的客户id)、price(产品的价格列)
假设我们每天必须有一项业务是查询每位定了编号是00x产品的客户的邮箱，进行回访，那么我们每天都要编写这样一条查询语句

SELECT c.id,emial from customers as c,products as p where p_id = 00x and c.c_id = p.c_id;

每查询一遍我们就需要编写这么一条SELECT语句，而有了视图，我们只需要根据上诉的查询语句生成一个视图（也就是虚拟表）我们只需要在该虚拟表里SELECT * FROM 就可以做到和上诉那么冗长的操作一样的效果了。

二、创建视图

1创建视图的语法形式

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINEDD|MERGE|TEMPTABLE}]
VIEW view_name(view_column_name) AS SELECT_statement
[WITH{CASCADED|LOCAL} CHECK OPTION]

下面我们逐一解析该语法

CREATE [OR REPLACE]
使用CREATE语句创建视图或者使用REPLACE语句替换已经创建的视图

[ALGORITHM = {UNDEFINEDD|MERGE|TEMPTABLE}] 可选的SQL语句
ALGORITHM：算法，可选参数有三个，
UNDEFINED(不定义算法)
MERGE(合并：将使用的视图语句和视图定义合并起来)
TEMPTABLE(临时表：表示将视图的结果存入临时表然后用临时表执行语句)

VIEW view_name(view_column_name) AS SELECT_statement
VIEW关键字：指明创建类型为视图
view_name：指明视图的名字
view_column_name：定义视图里显示的各列名称，不指定则MySQL自动选择列名 AS
SELECT_statement：具体的查询规则

[WITH{CASCADED|LOCAL} CHECK OPTION] 可选的SQL语句
WITH CASCADED CHECK OPTION：带有串联的检查选项指明更新视图时需要满足所有相关视图和表的条件
WITH LOCAL CHECK OPTION：带有本地检查选项指明更新视图时满足该视图本身的定义条件即可

一般创建视图会在当前数据库创建，如果要在给定的数据库中明确创建视图，创建的时候应将名称指定为da_name.view_name.

2.实例

1.在单表上创建视图

为方便操作，先创建如下表：

CREATE TABLE products(prod_id INT,price INT,quantity INT,cust_id VARCHAR(10));
INSERT INTO products VALUES(1,10.2,15,10001);
SELECT * FROM products;
+---------+-------+----------+---------+
| prod_id | price | quantity | cust_id |
+---------+-------+----------+---------+
|       1 |    10 |       15 | 10001   |
+---------+-------+----------+---------+

创建视图，不指定视图列名

CREATE VIEW view_1 AS SELECT price,quantity,price*quantity FROM products;
SELECT * FROM view_1;
+-------+----------+----------------+
| price | quantity | price*quantity |
+-------+----------+----------------+
|    10 |       15 |            150 |
+-------+----------+----------------+

创建视图，并指定视图列名

CREATE VIEW view_2(v_price,v_quantity,v_total) AS SELECT price,quantity,price*quantity FROM products;
SELECT * FROM view_2;
+---------+------------+---------+
| v_price | v_quantity | v_total |
+---------+------------+---------+
|      10 |         15 |     150 |
+---------+------------+---------+

2.在多表上创建视图

我们在创建一张客户表

CREATE TABLE customers(cust_id VARCHAR(10),cust_name VARCHAR(25),cust_address VARCHAR(255));
INSERT INTO customers VALUES(10001,'康春楠','北京市东25环');
SELECT * FROM customers;
+---------+-----------+--------------+
| cust_id | cust_name | cust_address |
+---------+-----------+--------------+
| 10001   | 康春楠    | 北京市东25环 |
+---------+-----------+--------------+

现在我们要通过products和customers两张表创建一个视图，只包含客户的姓名，购买产品的编号，购买产品的价格三列，如下：

CREATE VIEW view_3 AS SELECT cust_name,prod_id,price FROM customers,products;
SELECT * FROM view_3;
+-----------+---------+-------+
| cust_name | prod_id | price |
+-----------+---------+-------+
| 康春楠    |       1 |    10 |
+-----------+---------+-------+

这样我们就完成了在单表上和多表上创建视图，可以设想如果我们是数据库管理人员，我们只给用户开放了视图权限，那么用户就不用去了解实际表的结构，接触不到实际表中的数据，这样就大大提高了数据库的安全性，这也是视图另外一个重要的作用。

三、修改视图

1.使用CREATE OR REPLACE

语法为：

CREATE OR REPLACE [ALGORITHM = {UNDEFINEDD|MERGE|TEMPTABLE}]
VIEW view_name(view_column_name) AS SELECT_statement
[WITH{CASCADED|LOCAL} CHECK OPTION]

在上面的创建语句上加入 OR REPLACE其他不变。修改view_1 ，如下

CREATE OR REPLACE VIEW view_1 AS SELECT * FROM products;
SELECT * FROM view_1;
+---------+-------+----------+---------+
| prod_id | price | quantity | vend_id |
+---------+-------+----------+---------+
|       1 |    10 |       15 | 10001   |
+---------+-------+----------+---------+

2.使用 ALTER

语法为：

ALTER [ALGORITHM = {UNDEFINEDD|MERGE|TEMPTABLE}]
VIEW view_name(view_column_name) AS SELECT_statement
[WITH{CASCADED|LOCAL} CHECK OPTION]

使用ALTER关键字代替CREATE OR REPLACE,其他不变。

ALTER VIEW view_1 AS SELECT price FROM products;
SELECT * FROM view_1;
+-------+
| price |
+-------+
|    10 |
+-------+

四、更新视图

更新视图是指通过视图来插入、更新、删除表中的数据，因为视图是一个虚拟表，其中没有数据，所以视图更新时的操作都是转移到实际表上进行的，即对视图的更新数据，也会相应的修改实际表中的数据。

UPDATE、INSERT、DELETE

先查看目前数据库里的视图view_1.view_2以及基本表products

SELECT * FROM view_1 ;
+-------+
| price |
+-------+
|    10 |
+-------+
SELECT * FROM view_2;
+---------+------------+---------+
| v_price | v_quantity | v_total |
+---------+------------+---------+
|      10 |         15 |     150 |
+---------+------------+---------+
SELECT * FROM products;
+---------+-------+----------+---------+
| prod_id | price | quantity | vend_id |
+---------+-------+----------+---------+
|       1 |    10 |       15 | 10001   |
+---------+-------+----------+---------+

我们现在更新视图view_1的价格为100：

UPDATE view_1 SET price = 100;
SELECT * FROM view_1;
+-------+
| price |
+-------+
|   100 |
+-------+
SELECT * FROM products;
+---------+-------+----------+---------+
| prod_id | price | quantity | vend_id |
+---------+-------+----------+---------+
|       1 |   100 |       15 | 10001   |
+---------+-------+----------+---------+
SELECT * FROM view_2;
+---------+------------+---------+
| v_price | v_quantity | v_total |
+---------+------------+---------+
|     100 |         15 |    1500 |
+---------+------------+---------+

可以看到更改了视图view_1的单价后，基本表的内容也发生了改变，同时因为view_2也基于基本表的相同字段，所以该视图也发生了相应的变化。
同理INSERT、DELETE也如此：

-------------INSERT -------------
INSERT INTO view_1 VALUES(200);
SELECT * FROM view_1;
+-------+
| price |
+-------+
|   100 |
|   200 |
+-------+
SELECT * FROM products;
+---------+-------+----------+---------+
| prod_id | price | quantity | vend_id |
+---------+-------+----------+---------+
|       1 |   100 |       15 | 10001   |
|    NULL |   200 |     NULL | NULL    |
+---------+-------+----------+---------+
SELECT * FROM view_2;
+---------+------------+---------+
| v_price | v_quantity | v_total |
+---------+------------+---------+
|     100 |         15 |    1500 |
|     200 |       NULL |    NULL |
+---------+------------+---------+

--------------DELETE-----------------
DELETE FROM view_1 WHERE price = 200;
SELECT * FROM view_1;
+-------+
| price |
+-------+
|   100 |
+-------+
SELECT * FROM products;
+---------+-------+----------+---------+
| prod_id | price | quantity | vend_id |
+---------+-------+----------+---------+
|       1 |   100 |       15 | 10001   |
+---------+-------+----------+---------+
SELECT * FROM view_2;
+---------+------------+---------+
| v_price | v_quantity | v_total |
+---------+------------+---------+
|     100 |         15 |    1500 |
+---------+------------+---------+

上诉操作均执行成功，但是当时图包含如下操作时，是不能直接更新视图的：
1、视图中不包含基表中被定义为非空的列
2、在定义视图的SELECT语句后的字段列表中，使用了数学表达式
3、在定义视图的SELECT语句后的字段列表中，使用了聚合函数
4、在定义视图的SELECT语句中，使用了DISTINCT、UNION、TOP、GROUP BY或者HAVING子句
5、位于选择列表中的子查询
6、FROM子句中的不可更新视图
7、WHERE子句中的子查询，引用FROM子句中的表。
8、仅引用文字值（在该情况下，没有要更新的基本表）。
9、ALGORITHM = TEMPTABLE（使用临时表总会使视图成为不可更新的）
如下：向view_2视图插入值就会报错

INSERT INTO view_2(v_price,v_quantity) VALUES(200,20);
ERROR 1471 (HY000): The target table view_2 of the INSERT is not insertable-into
#提示该表不可被插入
SELECT * FROM view_2;

五、删除视图

基本语法为：

DROP VIEW [IF EXISTS]
view_name[,view_name2……]
[RESTRICT|CASCADE]

删除视图view_2

DROP VIEW IF EXISTS view_2;
Query OK, 0 rows affected (0.02 sec)
DESC view_2;
ERROR 1146 (42S02): Table 'xuexi.view_2' doesn't exist

六、查看视图

1.DESCRIBE

DESC view_1;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| price | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+

2.SHOW TABLE STATUS LIKE

SHOW TABLE STATUS LIKE 'view_1'\G;
*************************** 1. row ***************************
           Name: view_1
         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: 2020-09-26 09:28:49
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW

其中comment为VIEW，其他信息为NULL表明该表是一个视图，再用同样的语句查看products表

SHOW TABLE STATUS LIKE 'products'\G;
*************************** 1. row ***************************
           Name: products
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-09-26 09:26:24
    Update_time: 2020-09-26 11:19:34
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:

可以看到若对实际表进行这步操作，可以看到储存的引擎类型、创建时间、更新时间等信息。这也说明了视图时虚拟表的本质

3.SHOW CREATE VIEW

SHOW CREATE VIEW view_1\G;
*************************** 1. row ***************************
                View: view_1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_1` AS select `products`.`price` AS `price` from `products`
character_set_client: gbk
collation_connection: gbk_chinese_ci

该操作会显示创建视图的SQL语句。

康春楠

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL视图详解

系列文章目录文章目录系列文章目录一、概念二、创建视图1创建视图的语法形式2.实例1.在单表上创建视图2.在多表上创建视图三、修改视图1.使用CREATE OR REPLACE2.使用 ALTER四、更新视图UPDATE、INSERT、DELETE五、删除视图六、查看视图1.DESCRIBE2.SHOW TABLE STATUS LIKE3.SHOW CREATE VIEW一、概念视图是由实际存在于数据库中一个或多个表导出的虚拟表。身为表，虽然是虚拟的，但也可以使用SELECT语句查询语句，使用INSE
复制链接

扫一扫

专栏目录