MySQL视图

11.1 视图简述

• 含义：视图是一个虚拟表，是从数据库中一个或多个表中导出的表。通过视图看到的数据只是存放在基本表中的数据。当对通过视图看到的数据进行修改时，相应的基本表数据也会修改；基本表数据变化视图也会发生变化。
• 作用：
  • 简单化：看到的就是需要的。
  • 安全性：通过视图，用户只能查询和修改自己能看到的。

11.2 创建视图

• 在单表上创建

mysql> CREATE VIEW view_p AS SELECT id,name FROM person;
Query OK, 0 rows affected (0.02 sec)

+----+------+
| id | name |
+----+------+
|  1 | zs   |
|  2 | ls   |
|  3 | we   |
|  4 | ld   |
|  5 | xd   |
|  6 | xm   |
+----+------+

mysql> CREATE VIEW view_p2 (pid,pname) AS SELECT id,name FROM person WHERE id>2;
Query OK, 0 rows affected (0.02 sec)
-----+
| pid | pname |
+-----+-------+
|   3 | we    |
|   4 | ld    |
|   5 | xd    |
|   6 | xm    |
+-----+-------+

• 在多表上创建

mysql> CREATE VIEW person_fruits (ID,NAME) AS SELECT person.id,fruits.name
    -> FROM person ,fruits WHERE person.id=fruits.id;
Query OK, 0 rows affected (0.02 sec)

若不指定where子句，会发生笛卡尔积现象。

+----+-------+
| ID | NAME  |
+----+-------+
|  1 | apple |
|  2 | pear  |
|  3 | malon |
+----+-------+
3 rows in set (0.01 sec)

11.3 查看视图

• DESC查看

mysql> desc view_p;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | 0       |       |
| name  | varchar(11) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

• SHOW TABLE STATUS语句查看

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

• 通过SHOW CREATE VIEW查看

mysql> SHOW CREATE VIEW view_p\G
*************************** 1. row ***************************
                View: view_p
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_p` AS select `person`.`id` AS `id`,`person`.`name` AS `name` from `person`
character_set_client: gbk
collation_connection: gbk_chinese_ci
1 row in set (0.00 sec)

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

mysql> SELECT * FROM information_schema.views\G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: myl
          TABLE_NAME: person_fruits
     VIEW_DEFINITION: select `myl`.`person`.`id` AS `ID`,`myl`.`fruits`.`name` AS `NAME` from `myl`.`person` join `myl`.`fruits` where (`myl`.`person`.`id` = `myl`.`fruits`.`id`)
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: gbk
COLLATION_CONNECTION: gbk_chinese_ci

上述操作在实际操作中，一共查出了110条记录（3条记录是我添加的记录）

11.4 修改视图

• 使用CREATE OR REPLEACE VIEW

mysql> CREATE OR REPLACE VIEW view_p AS SELECT * FROM person;
Query OK, 0 rows affected (0.02 sec)

• 使用ALTER

mysql> ALTER VIEW view_p AS SELECT id,age FROM person WHERE id<3;
Query OK, 0 rows affected (0.02 sec)

11.5 更新视图

更新视图指通过视图来插入、更新、删除表中的数据。

• 插入：向基本表中插入数据，视图也会变化

mysql> select * from fruits;
+------+-------+
| id   | name  |
+------+-------+
|    1 | apple |
|    2 | pear  |
|    3 | malon |
+------+-------+
3 rows in set (0.00 sec)

mysql> insert into fruits values(4,'water');
Query OK, 1 row affected (0.01 sec)

mysql> select * from fruits;
+------+-------+
| id   | name  |
+------+-------+
|    1 | apple |
|    2 | pear  |
|    3 | malon |
|    4 | water |
+------+-------+
4 rows in set (0.00 sec)

• 更新（修改）：

mysql> UPDATE view_f SET name='banana' WHERE id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

• 删除：

mysql> DELETE FROM view_f WHERE id=2;
Query OK, 1 row affected (0.01 sec)

mysql> select * from view_f;
+------+-------+
| id   | name  |
+------+-------+
|    1 | apple |
|    3 | malon |
|    4 | water |
+------+-------+
3 rows in set (0.00 sec)

视图中包含以下内容时，视图的更新操作将不能被执行：

• 不包含基表中被定义为非空的列。
• 在定义视图的SELECT语句后的字段列表中使用了数学表达式，聚合函数，DISTINCT,UNION,TOP,GROUP BY ,HAVING子句。

11.6 删除视图

mysql> DROP VIEW IF EXISTS view_f,view_p,person_fruits,p;
Query OK, 0 rows affected, 1 warning (0.00 sec)