MySQL知识（十七）——视图

最新推荐文章于 2024-09-04 06:48:07 发布

添仔哥哥

最新推荐文章于 2024-09-04 06:48:07 发布

阅读量1.3k

点赞数 1

分类专栏： MySQL学习笔记文章标签： mysql 视图 view 数据库

本文链接：https://blog.csdn.net/Zen99T/article/details/50755728

版权

MySQL学习笔记专栏收录该内容

24 篇文章 1 订阅

订阅专栏

1 视图概述

1.1 视图的含义

　　视图是从一个或者多个表中导出的，也可以从已经存在的视图基础上定义，视图的行为与表非常相似，但视图是一个虚拟表。在视图中用户可以使用SELECT语句查询数据，以及使用INSERT、UPDATE和DELETE修改记录。视图可以使用户操作方便，而且可以保障数据库系统的安全。
　　视图一经定义便存储在数据库中，与其相应的数据并没有像表那样在数据库中再存储一份，通过视图看到的数据只是存放在基本表中的数据。
　　当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化；同时，若基本表的数据发生变化，则这种变化也可以自动地反映到视图中。

1.2 视图的作用

　　与直接从数据表中读取相比，视图有以下优点：
　　（1）简单化
　　视图呈现的数据就是需要的。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
　　（2）安全性
　　通过视图用户只能查询和修改他们所能见到的数据。
　　（3）逻辑数据独立性
　　视图可以帮助用户屏蔽真实表结构变化带来的影响。

2 创建视图

2.1 语法形式

创建视图使用CREATE VIEW语句：

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

CREATE：创建新的视图
REPLACE：替换已经创建的视图
ALGORITHM：视图选择的算法
view_name：视图名称
column_list：属性列
SELECT_statement：SELECT语句
WITH [CASCADED|LOCAL] CHECK OPTION：视图在更新时保证在视图的权限范围之内

　　ALGORITHM有3个取值：
UNDEFINED：MySQL自动选择算法；
MERGE：将使用的视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部分
TEMPTABLE：将视图的结果存入临时表，然后用临时表来执行语句

　　CASCADED与LOCAL为可选参数：
CASCADED为默认值，表示更新视图时要满足所有相关视图和表的条件；
LOCAL：更新视图满足该视图本省定义的条件即可

2.2 在单表上创建视图

mysql> CREATE TABLE t(quantity INT,price INT);
Query OK, 0 rows affected

mysql> INSERT INTO t VALUES(3,50);
Query OK, 1 row affected

mysql> CREATE VIEW view_t AS SELECT quantity,price,quantity*price FROM t;
Query OK, 0 rows affected

mysql> SELECT * FROM view_t;
+----------+-------+----------------+
| quantity | price | quantity*price |
+----------+-------+----------------+
|        3 |    50 |            150 |
+----------+-------+----------------+
1 row in set

　　可以指定视图字段的名称：

mysql> CREATE VIEW view_t2(qty,price,total) AS SELECT quantity,price,quantity*price FROM t;
Query OK, 0 rows affected

mysql> SELECT * FROM view_t2;
+-----+-------+-------+
| qty | price | total |
+-----+-------+-------+
|   3 |    50 |   150 |
+-----+-------+-------+
1 row in set

2.3 在多表上创建视图

（1）创建student表和stu_info表，并插入数据

mysql> CREATE TABLE student
(
  s_id  INT,
  name  VARCHAR(40)
);
Query OK, 0 rows affected

mysql> CREATE TABLE stu_info
(
  s_id   INT,
  glass  VARCHAR(40),
  addr   VARCHAR(90)
);
Query OK, 0 rows affected

mysql> INSERT INTO student VALUES(1,'wanglin1'),(2,'gaoli'),(3,'zhanghai'); 
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO stu_info VALUES(1, 'wuban','henan'),(2,'liuban','hebei'),(3,'qiban','shandong');
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

（2）创建视图

mysql> CREATE VIEW stu_glass(id,name,glass)
    -> AS SELECT student.s_id,student.name,stu_info.glass
    -> FROM student,stu_info
    -> WHERE student.s_id=stu_info.s_id;
Query OK, 0 rows affected

mysql> SELECT * FROM stu_glass;
+----+----------+--------+
| id | name     | glass  |
+----+----------+--------+
|  1 | wanglin1 | wuban  |
|  2 | gaoli    | liuban |
|  3 | zhanghai | qiban  |
+----+----------+--------+
3 rows in set

3 查看视图

3.1 DESCRIBE或DESC

mysql> DESC view_t;
+----------------+------------+------+-----+---------+-------+
| Field          | Type       | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| quantity       | int(11)    | YES  |     | NULL    |       |
| price          | int(11)    | YES  |     | NULL    |       |
| quantity*price | bigint(21) | YES  |     | NULL    |       |
+----------------+------------+------+-----+---------+-------+
3 rows in set

3.2 SHOW TABLE STATUS

mysql> SHOW TABLE STATUS LIKE 'view_t' \G;
*************************** 1. row ***************************
           Name: view_t
         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)

Comment值为VIEW说明表为视图，其他信息为NULL说明这是一个虚表。

3.3 SHOW CREATE VIEW

mysql> SHOW CREATE VIEW view_t \G;
*************************** 1. row ***************************
                View: view_t
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_t` AS select `t`.`quantity` AS `quantity`,`t`.`price` AS `price`,(`t`.`quantity` * `t`.`price`) AS `quantity*price` from `t`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

3.4 在views表中查看

　　在MySQL中，information_schema数据库下的views表中存储了所有视图的定义。

SELECT * FROM information_schema.views;

4 修改视图

　　修改数据库中存在的视图，当基本表的某些字段发生变化时，可以通过修改视图来保持与基本表的一致性。

4.1 CREATE OR REPLACE VIEW语句

修改视图的语句和创建视图的语句是完全一样的。

mysql> DESC view_t;
+----------------+------------+------+-----+---------+-------+
| Field          | Type       | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| quantity       | int(11)    | YES  |     | NULL    |       |
| price          | int(11)    | YES  |     | NULL    |       |
| quantity*price | bigint(21) | YES  |     | NULL    |       |
+----------------+------------+------+-----+---------+-------+
3 rows in set

mysql> CREATE OR REPLACE VIEW view_t AS SELECT * FROM t;
Query OK, 0 rows affected

mysql> DESC view_t;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| quantity | int(11) | YES  |     | NULL    |       |
| price    | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
2 rows in set

4.2 ALTER语句

语法跟创建视图的语句一样，只是将”CREATE OR REPLACE VIEW”改为”ALTER”。

mysql> ALTER VIEW view_t AS SELECT quantity FROM t;
Query OK, 0 rows affected

mysql> DESC view_t;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| quantity | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
1 row in set

5 更新视图

　　更新视图是指通过视图来插入、更新、删除表中的数据，因为视图是一个虚拟表，其中没有数据。通过视图更新的时候都是转到基本表上进行更新的，如果对视图增加或者删除记录，实际上是对基本表增加或删除记录。

5.1 UPDATE语句

对视图更新，基本表的内容也会更新。

mysql> SELECT * FROM view_t;
+----------+
| quantity |
+----------+
|        3 |
+----------+
1 row in set

mysql> SELECT * FROM t;
+----------+-------+
| quantity | price |
+----------+-------+
|        3 |    50 |
+----------+-------+
1 row in set

mysql> UPDATE view_t SET quantity=5;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM t;
+----------+-------+
| quantity | price |
+----------+-------+
|        5 |    50 |
+----------+-------+
1 row in set

mysql> SELECT * FROM view_t;
+----------+
| quantity |
+----------+
|        5 |
+----------+
1 row in set

mysql> SELECT * FROM view_t2;
+-----+-------+-------+
| qty | price | total |
+-----+-------+-------+
|   5 |    50 |   250 |
+-----+-------+-------+
1 row in set

5.2 INSERT语句

对基本表进行插入数据，视图的内容也会跟着更新。（不能对视图进行插入操作）

mysql> INSERT INTO t VALUES(9,9);
Query OK, 1 row affected

mysql> SELECT * FROM t;
+----------+-------+
| quantity | price |
+----------+-------+
|        5 |    50 |
|        9 |     9 |
+----------+-------+
2 rows in set

mysql> SELECT * FROM view_t2;
+-----+-------+-------+
| qty | price | total |
+-----+-------+-------+
|   5 |    50 |   250 |
|   9 |     9 |    81 |
+-----+-------+-------+
2 rows in set

5.3 DELETE语句

删除视图的数据，相应的基本表的记录也会删除。

mysql> DELETE FROM view_t2 WHERE price=9;
Query OK, 1 row affected

mysql> SELECT * FROM view_t2;
+-----+-------+-------+
| qty | price | total |
+-----+-------+-------+
|   5 |    50 |   250 |
+-----+-------+-------+
1 row in set

mysql> SELECT * FROM t;
+----------+-------+
| quantity | price |
+----------+-------+
|        5 |    50 |
+----------+-------+
1 row in set

5.4 不能执行更新的视图

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

6 删除视图

DROP VIEW [IF EXISTS]
　　view_name[,view_name]...
　　[RESTRICT|CASCADE]

　　例如，删除stu_glass的视图：

mysql> DROP VIEW IF EXISTS stu_glass;
Query OK, 0 rows affected

mysql> SHOW CREATE VIEW stu_glass;
1146 - Table 'test.stu_glass' doesn't exist

7 视图和表的区别

这里写图片描述

添仔哥哥

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录