• 视图
    • > Mysql 视图

    Mysql 视图

    之前一直对mysql的视图过程了解一点点，但没有完整的了解过，所以有了这篇博客，这是这一序列的第一篇。。事实上这是对mysql视图的一个基本认识

    视图

    MariaDB [oldboy]> select id,name from test;
+----+--------------+
| id | name         |
+----+--------------+
|  5 | Amy          |
|  6 | dad          |
|  4 | david        |
|  1 | jack         |
|  2 | 周†       |
+----+--------------+
5 rows in set (0.01 sec)
    创建视图
    create view test_view as select id,age,name from test;
select * from test_view;
MariaDB [oldboy]> select * from test_view;
+----+------+--------------+
| id | age  | name         |
+----+------+--------------+
|  1 | NULL | jack         |
|  2 |   18 | 周†       |
|  4 | NULL | david        |
|  5 | NULL | Amy          |
|  6 |   18 | dad          |
+----+------+--------------+
5 rows in set (0.00 sec)

#指定视图的字段名
create view test2_view(id,ag,na) as select id, age, name from test;
MariaDB [oldboy]> select * from test2_view;
+----+------+--------------+
| id | ag   | na           |
+----+------+--------------+
|  1 | NULL | jack         |
|  2 |   18 | 周†       |
|  4 | NULL | david        |
|  5 | NULL | Amy          |
|  6 |   18 | dad          |
+----+------+--------------+
5 rows in set (0.00 sec)

    视图只是查询语句，当数据库数据改变时，视图查询出来的数据也会跟着改变 下面将test表中id=2的的乱码数据更改，可以看到更改数据后，视图查出来的数据也改变了

    MariaDB [oldboy]> update test set name = 'mary' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [oldboy]> select id,name from test where id=2;
+----+------+
| id | name |
+----+------+
|  2 | mary |
+----+------+
1 row in set (0.00 sec)

MariaDB [oldboy]> select * from test_view;
+----+------+-------+
| id | age  | name  |
+----+------+-------+
|  1 | NULL | jack  |
|  2 |   18 | mary  |
|  4 | NULL | david |
|  5 | NULL | Amy   |
|  6 |   18 | dad   |
+----+------+-------+
5 rows in set (0.00 sec)

    总结： 创建视图主要通过： create view 视图名 As 查询语句来实现

    查看视图
    desc test_view;
MariaDB [oldboy]> desc test_view;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   |     | 0       |       |
| age   | int(4)   | YES  |     | NULL    |       |
| name  | char(32) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

#show table status like "test_view" \G;
MariaDB [oldboy]> show table status like "test_view" \G;
*************************** 1. row ***************************
           Name: test_view
         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,说明它是一个虚表，下面看看test表，比较下不同
MariaDB [oldboy]> show table status like 'test' \G;
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 7
    Create_time: 2020-03-26 11:19:40
    Update_time: 2020-03-26 17:22:16
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

# show create view test_view \G;
MariaDB [oldboy]> show create view test_view \G;
*************************** 1. row ***************************
                View: test_view
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `test_view` AS select `test`.`id` AS `id`,`test`.`age` AS `age`,`test`.`name` AS `name` from `test`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

#查看所有的视图的信息
MariaDB [oldboy]> select * from information_schema.views \G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: oldboy
          TABLE_NAME: test2_view
     VIEW_DEFINITION: select `oldboy`.`test`.`id` AS `id`,`oldboy`.`test`.`age` AS `ag`,`oldboy`.`test`.`name` AS `na` from `oldboy`.`test`
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: root@%
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
           ALGORITHM: UNDEFINED
*************************** 2. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: oldboy
          TABLE_NAME: test_view
     VIEW_DEFINITION: select `oldboy`.`test`.`id` AS `id`,`oldboy`.`test`.`age` AS `age`,`oldboy`.`test`.`name` AS `name` from `oldboy`.`test`
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: root@%
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
           ALGORITHM: UNDEFINED
2 rows in set (0.02 sec)

    总结 ： 查看视图有三种方式： * desc 视图名; * show table status like "视图名"; * show create view 视图名;

    修改视图
    alter view test_view as select id, name from test;
MariaDB [oldboy]> alter view test_view as select id, name from test;
Query OK, 0 rows affected (0.01 sec)

MariaDB [oldboy]> select * from test_view;
+----+-------+
| id | name  |
+----+-------+
|  5 | Amy   |
|  6 | dad   |
|  4 | david |
|  1 | jack  |
|  2 | mary  |
+----+-------+
5 rows in set (0.00 sec)

# create or replace 
create or replace view test_view as select id, age, name from test;
MariaDB [oldboy]> create or replace view test_view as select id, age, name from test;
Query OK, 0 rows affected (0.00 sec)

MariaDB [oldboy]> select * from test_view;
+----+------+-------+
| id | age  | name  |
+----+------+-------+
|  1 | NULL | jack  |
|  2 |   18 | mary  |
|  4 | NULL | david |
|  5 | NULL | Amy   |
|  6 |   18 | dad   |
+----+------+-------+
5 rows in set (0.00 sec)
    更新视图

    更新视图是指通过视图来插入（Insert）、更新（Update）和删除（Delete）表中的数据。因为视图是一个虚拟表，其中没有数据。通过视图更新时，都是转换到基本表来更新

    # 先查看视图信息
MariaDB [oldboy]> select * from test_view;
+----+------+-------+
| id | age  | name  |
+----+------+-------+
|  1 | NULL | jack  |
|  2 |   18 | mary  |
|  4 | NULL | david |
|  5 | NULL | Amy   |
|  6 |   18 | dad   |
+----+------+-------+
5 rows in set (0.01 sec)
#更新
update test_view set age =20 where id=1;
MariaDB [oldboy]> update test_view set age =20 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [oldboy]> select * from test_view;
+----+------+-------+
| id | age  | name  |
+----+------+-------+
|  1 |   20 | jack  |
|  2 |   18 | mary  |
|  4 | NULL | david |
|  5 | NULL | Amy   |
|  6 |   18 | dad   |
+----+------+-------+
5 rows in set (0.00 sec)

#此时查看原表，可以看到原表数据也更改了，当视图中包含多张表时这非常危险
MariaDB [oldboy]> select * from test;
+----+------+-------+
| id | age  | name  |
+----+------+-------+
|  1 |   20 | jack  |
|  2 |   18 | mary  |
|  4 | NULL | david |
|  5 | NULL | Amy   |
|  6 |   18 | dad   |
+----+------+-------+
5 rows in set (0.00 sec)

#insert 
insert into test_view values(3,21,'Amyli');
MariaDB [oldboy]> insert into test_view values(3,21,'Amyli');
Query OK, 1 row affected (0.01 sec)

MariaDB [oldboy]> select * from test_view;
+----+------+-------+
| id | age  | name  |
+----+------+-------+
|  1 |   20 | jack  |
|  2 |   18 | mary  |
|  3 |   21 | Amyli |
|  4 | NULL | david |
|  5 | NULL | Amy   |
|  6 |   18 | dad   |
+----+------+-------+
6 rows in set (0.00 sec)

#delete
delete from test_view where id=4;
MariaDB [oldboy]> delete from test_view where id=4;
Query OK, 1 row affected (0.01 sec)

MariaDB [oldboy]> select * from test_view;
+----+------+-------+
| id | age  | name  |
+----+------+-------+
|  1 |   20 | jack  |
|  2 |   18 | mary  |
|  3 |   21 | Amyli |
|  5 | NULL | Amy   |
|  6 |   18 | dad   |
+----+------+-------+
5 rows in set (0.00 sec)

    注意：下面这些情况下视图是不可更新的  聚合函数；  DISTINCT 关键字；  GROUP BY 子句；  ORDER BY 子句；  HAVING 子句；  UNION 运算符；  位于选择列表中的子查询；  FROM 子句中包含多个表；  SELECT 语句中引用了不可更新视图；  WHERE 子句中的子查询，引用FROM 子句中的表；

    总结 ： 视图更新主要有：删除时有from关键字 * update test_view set age =20 where id=1; * insert into test_view values(3,21,'Amyli'); * delete from test_view where id=4;

    删除视图
    drop view test_view2;
MariaDB [oldboy]> drop view test2_view;
Query OK, 0 rows affected (0.00 sec)
drop view if exist test_view; # 如果存在的话删除
MariaDB [oldboy]> drop view if exists test2_view;
Query OK, 0 rows affected, 1 warning (0.00 sec)

    ref: 《mysql网络数据库开发》 《mysql5.7 从入门到精通》

    上一篇：Pymysql maridb sqlalchemy packet sequence number wrong - got 1 expecte

    下一篇：Mysql 存储过程