08-MySQL视图

时间：2024-10-05 07:16:10

08-MySQL视图

  • 08-MySQL视图
    • 1 视图基本概念
    • 2 视图作用
      • 2.1 简单化
      • 2.2 安全性
      • 2.3 逻辑数据独立性
    • 3 创建视图
      • 3.1 在单表上创建视图
      • 3.2 在多表上创建视图
    • 4 查看视图
      • 4.1 使用describe语句查看视图：
      • 4.2 使用show table status 语句查看视图:
      • 4.3 使用show create view 语句查看视图详细信息
    • 5 修改视图
      • 5.1 使用create or replace view 语句修改视图
      • 5.2 使用alter语句修改视图
    • 6 更新视图
    • 7 删除视图
    • 8 Q&A
      • 8.1 MySQL中视图和表的区别以及联系是什么？

1 视图基本概念

​ 视图是一个虚拟表，是从数据库中一个或多个表中导出来的表，视图还可以从已经存在的视图的基础上定义。视图的行为与表非常相似，单视图是一个虚拟表。在视图中用户可以使用SELECT语句查询数据，以及使用INSERT、UPDATE、DELETE修改记录。

​ 视图一经定义遍存储在数据库中，通过视图看到的数据就是存放在基本表中的数据。对视图操作和对表操作一样。

2 视图作用

2.1 简单化

​ 简化用户对数据的理解，简化用户对数据的操作。

2.2 安全性

​ 通过视图用户只能查询和修改他们所能看到的数据。数据库中的其他数据则看不到、无法修改。

2.3 逻辑数据独立性

​ 视图帮助用户评比真实表结构变化带来的影响

3 创建视图

create [ or replace] [algorithm = { undefined | merge | temptable}]
view view_name [(column_list)]
as select_statement
[with [cascaded | local] check option ]
  • 1
  • 2
  • 3
  • 4
参数 备注
create 创建新的视图
replace 替换已经创建的视图
algorithm 视图选择算法
view_name 视图的名称
column_list 属性列
select_statement select语句
with[ cascaded | local] check option 参数表示视图在更新时保证在视图的权限范围之内

algorithm：

algorithm取值 备注
undefined 未定义
merge 表示将使用的视图语句与视图定义合并起来，是的视图定义的某一部分取代语句对应的部分
temptable 表示将视图的结果存入临时表，然后用临时表来执行语句

​ cascaded与local为可选参数，cascaded为默认值，表示更新视图时要满足所有相关视图和表的条件;local表示更新视图时满足该视图本身定义的条件即可。

​ 创建视图需要的权限：

  1. 具有针对视图的create view 权限
  2. 对于select语句中其他地方使用的列，必须具有select权限。如果还有 or replace子句，必须在视图上具有drop权限。

3.1 在单表上创建视图

测试表结构与数据创建

-- 创建测试表结构
mysql> create table t (quantity int, price int);
Query OK, 0 rows affected (0.03 sec)

-- 插入测试数据
mysql> insert into t values(3, 50);
Query OK, 1 row affected (0.02 sec)

-- 查看测试数据
mysql> select * from t;
+----------+-------+
| quantity | price |
+----------+-------+
|        3 |    50 |
+----------+-------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

创建一个视图，共有三个字段，quantity，price , quantity* price

-- 创建一个视图view_t
mysql> create view view_t as select quantity,price,quantity * price from t;
Query OK, 0 rows affected (0.00 sec)

-- 查询视图
mysql> select * from view_t;
+----------+-------+------------------+
| quantity | price | quantity * price |
+----------+-------+------------------+
|        3 |    50 |              150 |
+----------+-------+------------------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

​ 默认情况下视图和基本表的字段是一样的，也可以通过指定视图字段的名称来创建视图：

-- 新建视图
mysql> create view view_2 (qty, price, total) as select quantity, price, quantit
y*price from t;
Query OK, 0 rows affected (0.00 sec)

-- 查看视图
mysql> select * from view_2;
+------+-------+-------+
| qty  | price | total |
+------+-------+-------+
|    3 |    50 |   150 |
+------+-------+-------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

​ 可以看出view_t1和view_t2两个视图中字段名称不同，但是数据确实相同的。因此，在使用视图的时候，可能用户根本就不需要了解表的节骨，更接触不到实际表中的数据，因而保证了数据的安全性。

3.2 在多表上创建视图

-- 创建表结构
mysql> create table student(
    -> s_id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

-- 插入测试数据
mysql> insert into student values(1,'wanglin1'),(2,'gaoli'),(3,'zhanghai');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
-- 创建学生信息表
mysql> create table stu_info (
    -> id int ,
    -> name varchar(20),
    -> glass varchar(20));
Query OK, 0 rows affected (0.02 sec)

-- 插入学生信息
mysql> insert into stu_info values(1,'wuban','henan'),(2,'liuban','heibei'),(3,'
qiban','shandong');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

-- 查看学生信息
mysql> select * from stu_info;
+------+--------+----------+
| id   | name   | glass    |
+------+--------+----------+
|    1 | wuban  | henan    |
|    2 | liuban | heibei   |
|    3 | qiban  | shandong |
+------+--------+----------+
3 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23

创建视图：

-- 创建视图
mysql> create view stu_galss (id, name, glass) 
as select student.s_id, , stu_info.glass 
from  student, stu_info where _id = stu__id;
Query OK, 0 rows affected (0.02 sec)

-- 查看视图
mysql> select * from stu_galss;
+------+----------+----------+
| id   | name     | glass    |
+------+----------+----------+
|    1 | wanglin1 | henan    |
|    2 | gaoli    | heibei   |
|    3 | zhanghai | shandong |
+------+----------+----------+
3 rows in set (0.02 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

4 查看视图

​ 查看视图是查看数据库中已经存在的视图的定义。必须要有show view权限，MySQL数据库下的user表中保存着这个信息。查看视图的方法包括：describe、show table status和show create view 。

4.1 使用describe语句查看视图：

describe 视图名
  • 1
-- 查看视图
mysql> describe 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 (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

4.2 使用show table status 语句查看视图:

show table status like '视图名'
  • 1
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)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

4.3 使用show create view 语句查看视图详细信息

show create view 视图名
  • 1
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)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

5 修改视图

5.1 使用create or replace view 语句修改视图

create [ or replace] [ algorithm = {undefined | merge | temptable}]
view view_name [(column_name)]
as select_statment [with [cascaded | local] check option]
  • 1
  • 2
  • 3
-- 未修改之前
mysql> describe 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 (0.00 sec)

-- 如果存在就使用查询语句替换，如果不存在就创建
mysql> create or replace view  view_t as select * from t;
Query OK, 0 rows affected (0.01 sec)

mysql> desc view_t;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| quantity | int(11) | YES  |     | NULL    |       |
| price    | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23

可以看到少了一条quantity * price的字段。

5.2 使用alter语句修改视图

alter [algorithm = {undefined | merge | temptable}]
    view view_name [(column_list)]
    as select_statement
    [with [cascaded | local] check option]
  • 1
  • 2
  • 3
  • 4
-- 修改前
mysql> desc view_t;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| quantity | int(11) | YES  |     | NULL    |       |
| price    | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

-- 修改
mysql> alter view view_t as select quantity from t;
Query OK, 0 rows affected (0.00 sec)

-- 修改后
mysql> desc view_t;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| quantity | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23

6 更新视图

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

-- 视图修改前
mysql> select * from view_t;
+----------+
| quantity |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

-- 基本表修改前
mysql> select * from t;
+----------+-------+
| quantity | price |
+----------+-------+
|        3 |    50 |
+----------+-------+
1 row in set (0.00 sec)

-- 更新视图
mysql> update view_t set quantity=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 查看基本表，发现被更新
mysql> select * from t;
+----------+-------+
| quantity | price |
+----------+-------+
|        5 |    50 |
+----------+-------+
1 row in set (0.02 sec)

-- 查看视图
mysql> select * from view_t;
+----------+
| quantity |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40

7 删除视图

drop view [if exists]
    view_name [, view_name]...
    [restrict | cascade]
  • 1
  • 2
  • 3

view_name是要删除的视图名称

mysql> drop view if  exists stu_galss;
Query OK, 0 rows affected (0.00 sec)
  • 1
  • 2

8 Q&A

8.1 MySQL中视图和表的区别以及联系是什么？

区别：

  1. 视图是已经编译好的SQL语句，是局域SQL语句的结果集的可视化的表，而表不是。
  2. 视图没有实际的物理记录，而表有。
  3. 表示内容，视图是窗口。
  4. 表占用物理空间而视图不占用物理空间。
  5. 视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能用创建语句来修改。
  6. 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。
  7. 从安全角度来说，视图可以防止用户接触数据表，因而用户不知道表结构。
  8. 表属于全局模式中的表，是实表六视图属于局部模式的表，是虚表。
  9. 视图的建立和修改只影响视图本身，不影响对应的基本表。

联系：

​ 视图是在基本表之上建立的表，它的结构和内容都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和逻辑意义上建立的新关系。

相关文章