MySQL 视图的详解
一、视图简介：
1.概念:
视图是SELECT查询语句所定义的一个虚拟表，是查看数据的一种非常有效的方式。视图包含一系列有名称的数据列和数据行，但视图中的数据并不是实际存在数据库中，视图返回的是查询集。
简单说：视图是由查询结果形成的一张虚拟表。
2.视图优点：
1.可以简化查询：
- 若数据不是直接来源基表，可以通过定义视图，使数据库看起来结构简单、清晰、并且可以简化用户数据库查询操作。比如：要获取多个表中的数据，而这个查询语句又是常用的，这时就可以将多表的查询语句定义为视图，进行简化查询。
2.可以进行权限控制：
- 把表的权限封闭，但是开放相应的视图权限，视图里仅开放部分数据。
3.大数据分表时可以用到：
- 比如：表的行数超过600万行，就会变慢，我们想到的解决方案就是拆表，给它拆成6张表来存放。使用视图，把6张表形成一张视图进行查询。
select * from table1 union select * from table2.......
二、视图操作：
1.创建视图：
语法：create view [视图名] (列1，列2....) as select (列1，列2.....) from [表名]
举个栗子：
# 首先查看下当前数据库中有哪些表
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| basecourse |
| class |
| course |
| money |
| new_user |
| str |
| student |
| student_info |
| student_score |
+-------------------+
# 查看下stundet_info 表中的信息:
mysql> select * from student_info;
+------+--------+------+
| id | name | sex |
+------+--------+------+
| 1 | 亚索 | 男 |
| 2 | 小鱼儿 | 中性 |
| 3 | 发条 | 女 |
| 4 | 安妮 | 女 |
| 5 | 男刀 | 男 |
+------+--------+------+
5 rows in set (0.06 sec)
# 给student_info 表创建视图：
mysql> create view test_view as select * from student_info;
Query OK, 0 rows affected (0.01 sec)
# 查看当前数据库中的表:
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| basecourse |
| class |
| course |
| money |
| new_user |
| str |
| student |
| student_info |
| student_score |
| test_view | # 可以看到新创建的视图会出现在当前库中
+-------------------+
# 查看新创建的视图：
mysql> select * from test_view;
+------+--------+------+
| id | name | sex |
+------+--------+------+
| 1 | 亚索 | 男 |
| 2 | 小鱼儿 | 中性 |
| 3 | 发条 | 女 |
| 4 | 安妮 | 女 |
| 5 | 男刀 | 男 |
+------+--------+------+
5 rows in set (0.01 sec)
注意：操作查询视图跟操作表相同，因为视图就是一张虚拟表，但是不能在一张由多张关联表连接而成的视图上做同时修改两张表的操作，视图与表是一对一关系情况：如果没有其它约束，可以进行增删改数据操作。
2.修改视图：
语法：alter view [视图名] as select * from [表名]
概念：视图是表的一个影子，表与视图数据变化时会相互影响。
举个栗子：
1.给表添加一条数据，视图也会发生改变：
# 给表插入数据
mysql> insert into student_info values(6, '火男', '男');
Query OK, 1 row affected (0.02 sec)
# 查看发现, 视图也会跟的改变
mysql> select * from test_view;
+------+--------+------+
| id | name | sex |
+------+--------+------+
| 1 | 亚索 | 男 |
| 2 | 小鱼儿 | 中性 |
| 3 | 发条 | 女 |
| 4 | 安妮 | 女 |
| 5 | 男刀 | 男 |
| 6 | 火男 | 男 |
+------+--------+------+
6 rows in set (0.00 sec)
注意：视图必须包含表中没有默认值的列。
2.通过视图，修改表中的数据：
# 修改视图中的数据
mysql> update test_view set name = '杰斯' where id = '6';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 查询发现, 视图中的基表也会发生改变：
mysql> select * from student_info;
+------+--------+------+
| id | name | sex |
+------+--------+------+
| 1 | 亚索 | 男 |
| 2 | 小鱼儿 | 中性 |
| 3 | 发条 | 女 |
| 4 | 安妮 | 女 |
| 5 | 男刀 | 男 |
| 6 | 杰斯 | 男 |
+------+--------+------+
6 rows in set (0.00 sec)
3.若定义中的视图，跟基表中的数据不对应，是否可以修改？
# 先看一下，准备好的课程表
mysql> select * from course;
+----+----------------+--------+----------+
| id | name | price | class_id |
+----+----------------+--------+----------+
| 1 | 上海财经大学 | 99.99 | 1 |
| 2 | 北京大学 | 199.99 | 1 |
| 3 | 天津大学 | 299.99 | 1 |
| 4 | 哈尔滨工业大学 | 399.99 | 2 |
| 5 | 交通大学 | 499.99 | 2 |
| 6 | 同济大学 | 599.99 | 3 |
| 7 | 清华大学 | 699.99 | 3 |
| 8 | 华北大学 | 699.99 | 0 |
+----+----------------+--------+----------+
8 rows in set (0.02 sec)
# 通过class_id 进行分组，进行每个班级的课程平均价格
mysql> create view price_view as select class_id, avg(price) from course group by class_id;
Query OK, 0 rows affected (0.01 sec)
# 查看视图已经创建
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| basecourse |
| class |
| course |
| money |
| new_user |
| price_view | # 新创建的视图
| str |
| student |
| student_info |
| student_score |
| test_view |
+-------------------+
11 rows in set (0.00 sec)
# 查看新创建的视图
mysql> select * from price_view;
+----------+------------+
| class_id | avg(price) |
+----------+------------+
| 0 | 699.990000 |
| 1 | 199.990000 |
| 2 | 449.990000 |
| 3 | 649.990000 |
+----------+------------+
4 rows in set (0.00 sec)
# 这时要修改视图中class_id=0的班级，课程的评价价格，看是否能修改
mysql> update price_view set price=10000 where class_id = 1;
ERROR 1288 (HY000): The target table price_view of the UPDATE is not updatable
# 为什么会报错呢？因为price列的值变了，映射到基表course中，class_id=0的，不知道到底修改哪行？
总结：视图某种情况下，是可以修改的。要求视图的数据和表的数据一 一对应，就像函数的映射一样。
3.查看视图的信息：
语法：describe [视图名]
# 查看price_view的视图信息
mysql> describe price_view;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| class_id | int(11) | NO | | NULL | |
| avg(price) | decimal(12,6) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
4.查看所有表和视图：
语法：show tables;
# 可以看到当前数据库中的表与视图
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| basecourse |
| class |
| course |
| money |
| new_user |
| price_view |
| str |
| student |
| student_info |
| student_score |
| test_view |
+-------------------+
11 rows in set (0.00 sec)
注意：视图并不会占数据库的空间。
5.删除视图：
语法：drop view [视图名]
# 删除price_view视图
mysql> drop view price_view;
Query OK, 0 rows affected (0.00 sec)
# 查看表和视图，发现price_view视图已经被删除
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| basecourse |
| class |
| course |
| money |
| new_user |
| str |
| student |
| student_info |
| student_score |
| test_view |
+-------------------+
10 rows in set (0.00 sec)
三、视图的algorithm参数：
1.Merge：
概念：当引用视图时，引用视图的语句与定义视图的语句合并。意味着视图只是一个规则，语句规则，当查询视图时，把查询视图的语句与创建时的语句where子句等合并，分析形成一条select 语句。
举个栗子：
# 首先查询一下course表中的数据:
mysql> select * from course;
+----+----------------+--------+----------+
| id | name | price | class_id |
+----+----------------+--------+----------+
| 1 | 上海财经大学 | 99.99 | 1 |
| 2 | 北京大学 | 199.99 | 1 |
| 3 | 天津大学 | 299.99 | 1 |
| 4 | 哈尔滨工业大学 | 399.99 | 2 |
| 5 | 交通大学 | 499.99 | 2 |
| 6 | 同济大学 | 599.99 | 3 |
| 7 | 清华大学 | 699.99 | 3 |
| 8 | 华北大学 | 699.99 | 0 |
+----+----------------+--------+----------+
8 rows in set (0.02 sec)
# 创建视图的语句：
mysql> create view course_view as select name, price from course where price > 300;
Query OK, 0 rows affected (0.01 sec)
# 查询视图的语句：
mysql> select * from course_view where price < 600;
+----------------+--------+
| name | price |
+----------------+--------+
| 哈尔滨工业大学 | 399.99 |
| 交通大学 | 499.99 |
| 同济大学 | 599.99 |
+----------------+--------+
3 rows in set (0.00 sec)
# 最终执行的语句:
mysql> select name, price from course where price > 300 and price < 600;
+----------------+--------+
| name | price |
+----------------+--------+
| 哈尔滨工业大学 | 399.99 |
| 交通大学 | 499.99 |
| 同济大学 | 599.99 |
+----------------+--------+
3 rows in set (0.00 sec)
2.Temptable：
概念：当引用视图时，根据视图的创建语句建立一个临时表，然后查询视图的语句从该临时表查数据。
举个栗子：
# 创建视图, 并进行查询
mysql> create algorithm=temptable view t_course as select name, price from course where price > 300;
Query OK, 0 rows affected (0.01 sec)
# 查看新创建的视图：
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| basecourse |
| class |
| course |
| course_view |
| money |
| new_user |
| str |
| student |
| student_info |
| student_score |
| t_course |
| test_view |
+-------------------+
12 rows in set (0.00 sec)
# 新创建视图的数据：
mysql> select * from t_course;
+----------------+--------+
| name | price |
+----------------+--------+
| 哈尔滨工业大学 | 399.99 |
| 交通大学 | 499.99 |
| 同济大学 | 599.99 |
| 清华大学 | 699.99 |
| 华北大学 | 699.99 |
+----------------+--------+
5 rows in set (0.00 sec)
3.Undefined:
概念：未定义，自动的让系统帮你选。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
