mysql14-视图

测试数据

create database hh;
use hh;

create table tb1(
id int primary key auto_increment,
name char(15),
sex enum('男','女')
);

create table tb2(
id int primary key auto_increment,
name char(15)
);

insert into tb1(name,sex) values('张三','男'),('李四','男'),('王二','男'),('麻子','男'),('翠花','女'),('小红','女');

测试数据

1、视图概述

1、视图的含义

视图是从一个或者多个表中导出的，视图的行为与表非常相似，但视图是一个虚拟表。
视图一经定义便存储在数据库中，与其相对应的数据并没有像表那样在数据库中再存储份，通过视图看到的数据只是存放在基本表中的数据。
同真实的表一样，视图包含一系列带有名称的行和列数据。
- 行和列数据来自由定义视图查询所引用的表，并且在引用视图时动态生成。
- 在视图中用户可以使用SELECT语句查询数据，以及使用INSERT、UPDATE和DELETE修改记录。和操作真正的表几乎一样。
当对通过视图看到的数据进行修改时，相应的基本表的数据也会发生变化；同时，若基本表的数据发生变化，则这种变化也可以自动地反映到视图中。
从MySQL 5.0开始可以使用视图，视图可以使用户操作方便，而且可以保障数据库系统的安全。

2、视图的作用

1、简单化

视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

2、安全性

通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。通过视图，用户可以被限制在数据的不同子集上:
- (1)使用权限可被限制在基表的行的子集上。
- (2)使用权限可被限制在基表的列的子集上。
- (3)使用权限可被限制在基表的行和列的子集上。
- (4)使用权限可被限制在多个基表的连接所限定的行上。
- (5)使用权限可被限制在基表中的数据的统计汇总上。
- (6)使用权限可被限制在另一视图的一个子集上，或是一些视图和基表合并后的子集上。

3、逻辑数据独立性

视图可帮助用户屏蔽真实表结构变化带来的影响。

2、创建视图

基本语法格式如下：

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

- CREATE VIEW语句创建一个新视图，如果给定OR REPLACE子句，则替换现有视图。
  - 如果该视图不存在，则CREATE OR REPLACE VIEW与CREATE VIEW相同。
  - 如果该视图存在，CREATE OR REPLACE view将替换它。
- ALGORITHM表示视图选择的算法。
  - UNDEFINED表示MySQL将自动选择算法。
  - MERGE表示将使用的视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部分。
  - TEMPTABLE表示将视图的结果存入临时表，然后用临时表来执行语句。
- view_name为视图的名称。
- view_column为视图的自定义字段名，要与select语句显示的字段数量相同。
- SELECT_statement表示SELECT语句。
- WITH [CASCADED | LOCAL] CHECK OPTION参数表示视图在更新时保证在视图的权限范围之内。
  - CASCADED表示更新视图时要满足所有相关视图和表的条件，CASCADED为默认值。
  - LOCAL表示更新视图时满足该视图本身定义的条件即可。
该语句要求具有针对视图的CREATE VIEW权限，以及针对由SELECT语句选择的每一列上的某些权限。对于在SELECT语句中其他地方使用的列，必须具有SELECT权限。如果还有OR REPLACE子句，必须在视图上具有DROP权限。
视图属于数据库。在默认情况下，将在当前数据库创建新视图。明确指定数据库时应将名称指定为dbname.view_name。
默认情况下创建的视图和基本表的字段是一样的，也可以通过指定视图字段的名称来创建视图。

示例1：单表视图，视图字段默认

CREATE
    VIEW view1
    AS select name,sex from tb1;

mysql> select * from view1;
+------+------+
| name | sex  |
+------+------+
| 张三 | 男   |
| 李四 | 男   |
| 王二 | 男   |
| 麻子 | 男   |
| 翠花 | 女   |
| 小红 | 女   |
+------+------+

示例2：单表视图，视图字段自定义

CREATE
    VIEW view2(v_name,v_sex)
    AS select name,sex from tb1;

mysql> select * from view2;
+--------+-------+
| v_name | v_sex |
+--------+-------+
| 张三   | 男    |
| 李四   | 男    |
| 王二   | 男    |
| 麻子   | 男    |
| 翠花   | 女    |
| 小红   | 女    |
+--------+-------+

示例3：多表视图

CREATE
    VIEW view3(id1,name1,sex1,id2)
    AS select tb1.id,tb1.name,tb1.sex,tb2.id from tb1,tb2 where tb1.name = tb2.name;

mysql> select * from view3;
+-----+-------+------+-----+
| id1 | name1 | sex1 | id2 |
+-----+-------+------+-----+
|   6 | 小红  | 女   |   2 |
+-----+-------+------+-----+

3、查看视图

1、使用DESC查看视图的基本信息

基本语法格式如下：

DESC 视图名;     --等价于“DESCRIBE 视图名;”

示例：

mysql> DESC view1;
+-------+-----------------+------+-----+---------+-------+
| Field | Type            | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+-------+
| name  | char(15)        | YES  |     | NULL    |       |
| sex   | enum('男','女') | YES  |     | NULL    |       |
+-------+-----------------+------+-----+---------+-------+

2、使用SHOW TABLE STATUS查看视图的基本信息

基本语法格式如下：

SHOW TABLE STATUS LIKE '视图名';

示例：

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

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

3、使用SHOW CREATE VIEW查看视图的定义语句

基本语法格式如下：

SHOW CREATE VIEW 视图名;

示例：

mysql> SHOW CREATE VIEW view1\G
*************************** 1. row ***************************
                View: view1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view1` AS select `tb1`.`name` AS `name`,`tb1`.`sex` AS `sex` from `tb1`
character_set_client: gbk
collation_connection: gbk_chinese_ci

4、在views表中查看视图详细信息

基本语法格式如下：

SELECT * FROM information_schema.views;

示例：

mysql> SELECT * FROM information_schema.views WHERE TABLE_NAME LIKE 'view1'\G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: hh
          TABLE_NAME: view1
     VIEW_DEFINITION: select `hh`.`tb1`.`name` AS `name`,`hh`.`tb1`.`sex` AS `sex` from `hh`.`tb1`
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: gbk
COLLATION_CONNECTION: gbk_chinese_ci

4、修改视图

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

1、使用CREATE OR REPLACE VIEW语句修改视图

修改视图的语句和创建视图的语句是完全一样的。当视图已经存在时，修改语句对视图进行修改；当视图不存在时，创建视图。
基本语法格式如下：

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

示例：

CREATE OR REPLACE
    VIEW view1
    AS select * from tb1;

mysql> select * from view1;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | 张三 | 男   |
|  2 | 李四 | 男   |
|  3 | 王二 | 男   |
|  4 | 麻子 | 男   |
|  5 | 翠花 | 女   |
|  6 | 小红 | 女   |
+----+------+------+

2、使用ALTER语句修改视图

基本语法格式如下：

ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(view_column1, view_column2, ...)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

示例：

ALTER
    VIEW view1(v_name,v_sex)
    AS select name,sex from tb1;

mysql> select * from view1;
+--------+-------+
| v_name | v_sex |
+--------+-------+
| 张三   | 男    |
| 李四   | 男    |
| 王二   | 男    |
| 麻子   | 男    |
| 翠花   | 女    |
| 小红   | 女    |
+--------+-------+

5、更新视图

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

当视图中包含有如下内容时，视图的更新操作将不能被执行:
- (1)视图中不包含基表中被定义为非空的列。
- (2)在定义视图的SELECT语句后的字段列表中使用了数学表达式。
- (3)在定义视图的SELECT语句后的字段列表中使用聚合函数。
- (4)在定义视图的SELECT语句中使用了DISTINCT、UNION、TOP、GROUP BY或HAVING子句。

示例：

mysql> select * from view3;                                 --查看view3视图
+-----+-------+------+-----+
| id1 | name1 | sex1 | id2 |
+-----+-------+------+-----+
|   6 | 小红  | 女   |   2 |
+-----+-------+------+-----+

mysql> insert into view3(name1,sex1) values('狗蛋','男');    --更新view3视图

mysql> show create table view3\G                            --查看view3视图的定义语句
*************************** 1. row ***************************
                View: view3
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view3` AS select `tb1`.`id` AS `id1`,`tb1`.`name` AS `name1`,`tb1`.`sex` AS `sex1`,`tb2`.`id` AS `id2` from (`tb1` join `tb2`) where (`tb1`.`name` = `tb2`.`name`)
character_set_client: gbk
collation_connection: gbk_chinese_ci

mysql> select * from tb1;                                   --注意，视图中的name1和sex1是对应的是tb1中的字段，因此只更新了tb1，没有更新tb2
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | 张三 | 男   |
|  2 | 李四 | 男   |
|  3 | 王二 | 男   |
|  4 | 麻子 | 男   |
|  5 | 翠花 | 女   |
|  6 | 小红 | 女   |
|  7 | 狗蛋 | 男   |
+----+------+------+
mysql> select * from tb2;
+----+--------+
| id | name   |
+----+--------+
|  1 | 小明   |
|  2 | 小红   |
|  3 | 铁柱   |
|  4 | 驴蛋   |
|  5 | 二傻子 |
+----+--------+

6、删除视图

当视图不再需要时，可以将其删除，使用DROP VIEW语句可以删除一个或多个视图。
删除视图必须拥有DROP权限。

基本语法格式如下：

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

- view_name是要删除的视图名称，可以添加多个需要删除的视图名称，各个名称之间使用逗号分隔开。

示例：

mysql> drop view view1;

mysql> drop view view2,view3;

posted @ 2021-09-27 17:32 麦恒阅读(113) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

麦恒

mysql14-视图

1、视图概述

1、视图的含义

2、视图的作用

1、简单化

2、安全性

3、逻辑数据独立性

2、创建视图

3、查看视图

1、使用DESC查看视图的基本信息

2、使用SHOW TABLE STATUS查看视图的基本信息

3、使用SHOW CREATE VIEW查看视图的定义语句

4、在views表中查看视图详细信息

4、修改视图

1、使用CREATE OR REPLACE VIEW语句修改视图

2、使用ALTER语句修改视图

5、更新视图

6、删除视图

公告