MySQL 视图详细指南

1. 视图概述

视图是一种虚拟表，行和列的数据来自定义视图查询使用的表，并在使用视图时动态生成。简单来说，视图就是执行一个SELECT语句后返回的结果集。创建视图的主要工作是编写对应的SQL查询语句。

视图的优势

简单：使用视图的用户无需关心后端表的结构、关联条件或筛选条件，用户直接看到过滤后的复合条件结果集。

安全：用户只能访问被允许查询的结果集，通过视图可以轻松实现对表的权限管理。

数据独立：一旦视图结构确定，用户对表结构的变化无需再进行调整。即使源表修改了列名或增加了列，视图也可以通过修改查询语句来解决。

2. 创建或修改视图

创建视图的语法

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

示例

假设我们有两个表 city 和 country，其中 country_id 是 city 表的外键。我们想展示国家和城市之间的信息，因此需要使用连接查询：

SELECT c.*, t.country_name FROM city c, country t WHERE c.country_id = t.country_id;

创建视图

mysql> CREATE VIEW view_city_country AS SELECT c.*, t.country_name FROM city c, country t WHERE c.country_id = t.country_id;Query OK, 0 rows affected (0.05 sec)

修改视图

语法相同，修改时可以选择不同的算法：

ALTER VIEW view_name [...] AS ...;

3. 查看视图

查看视图列表

mysql> SHOW TABLES;+-------------------+ | Tables_in_demo_01 |+-------------------+| city             || country           || view_city_country |+-------------------+3 rows in set (0.00 sec)

查看视图定义

mysql> SHOW CREATE VIEW view_city_country;+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | View              | Create View                                                                                                                                                                                                                                                                                                                    | character_set_client | collation_connection |+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | view_city_country | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_city_country` AS select `c`.`city_id` AS `city_id`,`c`.`city_name` AS `city_name`,`c`.`country_id` AS `country_id`,`t`.`country_name` AS `country_name` from (`city` `c` join `country` `t`) where (`c`.`country_id` = `t`.`country_id`) |+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ 1 row in set (0.00 sec)

4. 删除视图

语法

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

示例

mysql> DROP VIEW view_city_country;Query OK, 0 rows affected (0.00 sec)

5. 实际操作示例

数据库结构

create database demo_01 default charset=utf8mb4;use demo_01;CREATE TABLE `city` (    `city_id` int(11) NOT NULL AUTO_INCREMENT,    `city_name` varchar(50) NOT NULL,    `country_id` int(11) NOT NULL,    PRIMARY KEY (`city_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `country` (    `country_id` int(11) NOT NULL AUTO_INCREMENT,    `country_name` varchar(100) NOT NULL,    PRIMARY KEY (`country_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `city` (`city_id`, `city_name`, `country_id`) VALUES(1, '西安', 1),(2, 'NewYork', 2),(3, '北京', 1),(4, '上海', 1);INSERT INTO `country` (`country_id`, `country_name`) VALUES(1, 'China'),(2, 'America'),(3, 'Japan'),(4, 'UK');

查看 city 表

mysql> SELECT * FROM city;+---------+-----------+------------+| city_id | city_name | country_id |+---------+-----------+------------+|       1 | 西安      |          1 ||       2 | NewYork   |          2 ||       3 | 北京      |          1 ||       4 | 上海      |          1 |+---------+-----------+------------+4 rows in set (0.00 sec)

查看 country 表

mysql> SELECT * FROM country;+------------+--------------+| country_id | country_name |+------------+--------------+|          1 | China        ||          2 | America      ||          3 | Japan        ||          4 | UK           |+------------+--------------+4 rows in set (0.00 sec)

创建视图

mysql> CREATE VIEW view_city_country AS SELECT c.*, t.country_name FROM city c, country t WHERE c.country_id = t.country_id;Query OK, 0 rows affected (0.05 sec)

更新视图

mysql> UPDATE view_city_country SET city_name='西安市' WHERE city_id=1;Query OK, 1 row affected (0.04 sec)Rows matched: 1  Changed: 1  Warnings: 0

查看更新后的视图

mysql> SELECT * FROM view_city_country;+---------+-----------+------------+--------------+| city_id | city_name | country_id | country_name |+---------+-----------+------------+--------------+|       1 | 西安市    |          1 | China        ||       3 | 北京      |          1 | China        ||       4 | 上海      |          1 | China        ||       2 | NewYork   |          2 | America      |+---------+-----------+------------+--------------+4 rows in set (0.00 sec)

删除视图

mysql> DROP VIEW view_city_country;Query OK, 0 rows affected (0.00 sec)

总结

通过以上步骤，我们可以清晰地看到视图在数据库中的实际应用场景。视图能够有效地帮助用户快速获取所需数据，同时也为数据库管理提供了更高的灵活性。

转载地址：http://ghdfk.baihongyu.com/

你可能感兴趣的文章

MySQL调大sort_buffer_size，并发量一大，查询排序为啥又会变慢