3. Mysql数据库-视图
image-20210213101959946
3.1 视图概述
3.1.1 视图介绍
# 视图介绍
1). 视图（View）是一种虚拟存在的表。
2). 视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。(视图只保存sql的逻辑,不保存表数据)
3). 通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
# 举个例子
普通班级: 张三 李四 王五 马六... 表
奥赛班(虚拟,临时,用的时候才有的): 数据来源班级 -> 张三 王五... 视图
奥赛班 : 查询普通班级而诞生
3.1.2 视图的优缺点
# 视图的优缺点
1. 优点
1). 简单化: 数据所见即所得
使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集
2). 安全性:用户只能查询或修改他们所能见到得到的数据
使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能具体限制到某个行某个列，但是通过视图就可以简单的实现
3). 逻辑独立性: 可以屏蔽真实表结构变化带来的影响
一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响
2. 缺点:
1). 性能较差
视图是在使用过程中动态生成的，所以查询比较慢
2). 增删改不方便
当用户试图修改视图的某些行时，数据库软件必须把它转化为对基本表的某些行的修改。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能修改不了。
3. 总得来说, 视图比普通查询要慢一些, 以牺牲性能为代价,提高数据的安全性和代码的复用性
# 视图的应用场景
1. 多个地方用到同样的查询结果
此时使用视图, 可以简化sql查询，提高开发效率
2. 安全性需要
如果源表中部分数据需要对外保密, 那么可以使用视图屏蔽这些数据
合理利用视图则可以减少很多授权工作和保证数据安全性
3.2 创建和查看视图
3.2.1 数据准备
-- 准备数据
-- 创建contry表
create table country(
id int primary key auto_increment,
name varchar(20)
);
-- 添加数据
insert into country values(null,'中国'),(null,'美国'),(null,'俄罗斯');
-- 创建city表
create table city(
id int primary key auto_increment,
name varchar(30),
cid int,
constraint cc_fk01 foreign key (cid) references country(id)
);
-- 添加数据
insert into city values(null,'北京',1),(null,'上海',1),(null,'纽约',2),(null,'莫斯科',3);
查询准备好的数据如下：
mysql> select * from country;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 中国 |
| 2 | 美国 |
| 3 | 俄罗斯 |
+----+-----------+
3 rows in set (0.00 sec)
mysql> select * from city;
+----+-----------+------+
| id | name | cid |
+----+-----------+------+
| 1 | 北京 | 1 |
| 2 | 上海 | 1 |
| 3 | 纽约 | 2 |
| 4 | 莫斯科 | 3 |
+----+-----------+------+
4 rows in set (0.00 sec)
mysql>
3.2.2 视图创建
# 视图创建
/*
create [or replace] view 视图名称[(列名列表)] as 查询语句
*/
-- 创建city_country视图,保存城市和国家的信息
-- 注意: 创建视图的多张表中存在同名字段,那么视图必须取列名
create or replace view city_country(city_id,city_name,country_name) as
select c1.id,c1.name,c2.name
from city c1, country c2
where c1.cid = c2.id;
执行如下：
image-202102131019599463.2.3 视图查询
# 视图查询
/*
select * from 视图名称;
-- 查看视图数据
show tables;
-- 查看表,如果有视图, 也显示视图
show create view 视图名;
-- 查看视图的定义
*/
-- 查询视图数据
select * from city_country;
-- 查看表 和 视图
show tables;
-- 查看视图的创建信息
show create view city_country;
3.3 修改和删除视图
3.3.1 视图修改
# 视图修改
/*
update 视图名称 set 列名=值 where 条件;
-- 注意 : 修改视图数据后,源表数据也会随之修改
*/
-- 修改视图数据,将city_id为1的城市修改成深圳
update city_country set city_name='深圳' where city_id = 1;
3.3.2 视图删除
# 视图删除
/*
drop view [if exists] 视图名称
*/
drop view if exists city_country;
3.4 视图扩展案例
/*
# 扩展: 视图在很多情况下,是无法更新的,
所以视图一般用来查询, 推荐增删改操作
例如:
group by 分组查询 就无法修改
*/
-- 1. 创建视图: 国家id,国家name和对应的城市数量
create or replace view country_citynumber(country_id,country_name,city_number) as
select c2.id,c2.name,count(*)
from city c1, country c2
where c1.cid = c2.id
group by c2.id;
-- 2. 查询此视图数据
select * from country_citynumber;
-- 执行如下：
mysql> select * from country_citynumber;
+------------+--------------+-------------+
| country_id | country_name | city_number |
+------------+--------------+-------------+
| 1 | 中国 | 2 |
| 2 | 美国 | 1 |
| 3 | 俄罗斯 | 1 |
+------------+--------------+-------------+
3 rows in set (0.00 sec)
-- 3. 修改国家id=2的国家名字
-- 错误代码：1288
-- The target table country_citynumber of the UPDATE is not updatable
update country_citynumber set contry_name = '英国' where country_id = 2;
-- 执行如下：
mysql> update country_citynumber set contry_name = '英国' where country_id = 2;
ERROR 1288 (HY000): The target table country_citynumber of the UPDATE is not updatable
