mysql视图与深入学习

视图面试题

首先看下面一道面试题：

为了更好的能答出这道题，让我们先熟悉位图相关的一些知识。

位图的概念

在对表的查询中产生的结果集充当虚拟表的角色，视图最终的操作都是基于表的( 在原来的表基础上再产生一个虚拟的表保存的是原表的一个快照数据）数据库只存放视图定义，但并不会为视图分配物理空间,所以原表数据发送改变，从视图中查询的数据也会发送变化。

视图实战

1. 创建一个view_employee表

create database interview;
use interview;
create table view_employee (
  id     int(11) primary key not null auto_increment,
  name   varchar(15)          not null unique,
  age    int(3)              not null,
  salary decimal(10, 2)      not null
)

2. 向view_employee表添加数据

INSERT INTO interview.view_employee
VALUES (1, '张三', 23, 5000.3);
INSERT INTO `interview`.`view_employee` (`name`, `age`, `salary`)
VALUES ('王五', 25, 10000.55);
INSERT INTO `interview`.`view_employee` (`name`, `age`, `salary`)
VALUES ('九龙?', 35, 18000);
INSERT INTO `interview`.`view_employee` (`name`, `age`, `salary`)
VALUES ('?厉害', 27, 9000.00);

执行插入语句发现mysql报错，如下所示：

首先第一反应是字符集的问题，导致插入数据库数据错误，查看当前数据库字符集配置。

show variables like '%character%';

发现数据库character_set_database默认值是latin1，如下图所示：

这里可以有两种修改字符集方式：一是直接修改当前database的表字符集，另一种就是建表就指定字符集。

# 第一种修改表的字符集
alter table view_employee charset = utf8mb4;
alter table view_employee collate = utf8mb4_unicode_ci;

## 第二种建表就设置字符集
create table view_employee (
  id     int(11) primary key not null auto_increment,
  name   varchar(15)          not null unique,
  age    int(3)              not null,
  salary decimal(10, 2)      not null
)
  engine = Innodb
  default charset = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

再次插入数据成功。
原因分析
让我们查看一下表情所占用的字节大小如下图所示：

普通的一个中文占用三个字节一般数据库字符集为utf8即可解决存储问题，但是表情包占4个字节原来的utf8就不够用了，所以用了utf8mb4解决这个问题。

3. 创建员工年龄大于30的视图

## 创建员工年龄大于30的视图my_view
create view my_view as (select *
                       from view_employee
                       where age > 30);
# 查询视图数据
select * from my_view;

查询my_view视图：

4. 修改原表view_employee数据

# 更改?厉害的年龄
update interview.view_employee set age=34 where name='?厉害';
# 再次查询视图数据
select * from my_view;

查询视图数据如下所示：

发现修改了原表view_employee的数据，视图的数据也发送了改变。
5. 修改视图my_view的数据

# 修改九龙?的视图数据
update interview.my_view set age=18 where name='九龙?';
# 查询原表的数据
select * from interview.view_employee;
# 查询视图的数据
select * from my_view;

原表view_employee的数据查询如下：

视图的数据查询如下：

发现修改视图中的数据，导致原表的数据与视图的数据均发送了变化，视图即可以进行查询也可以进行视图的修改，视图也是可以删除的，所以A、C的说法是错误的。

6. 删除原表view_employee

## 删除表
drop view my_view;
## 查询所有的当前database的表
show tables;

发现还有一条记录，即这个基于view_employee的视图还是存在的。

查询视图数据：

# 查询视图的数据
select * from my_view;

发现提示位图无效如下图所示：

检查当前视图的状态：

check table my_view;

所以B的陈述错误，当删除原表的时候，基于原表的视图变得无效且不能进行数据查询。
6. 在视图my_view创建视图my_view
首先将删除的数据进行复原后，进行两个视图的创建。

#再次创建视图my_view：
drop view if exists my_view;
create view my_view as (select *
                       from view_employee
                       where age > 20);

#基于视图my_view再次创建视图my_view_son
drop view if exists my_view_son;
create view my_view_son as (select *
                            from my_view
                            where salary> 8000);

查询表 show tables，可以看到有2个视图：

向my_view_son视图添加数据：

insert interview.my_view_son (name, age, salary) values ('萧炎', 30, 6666)；

查询my_view_son视图如下图所示：

查询my_view视图数据如下：

此时删除视图my_view,并检查视图my_view_son，如果删除或重命名视图所基于的表，则MySQL不会发出任何错误。但是，MySQL会使视图无效。 可以使用CHECK TABLE语句来检查视图是否有效。

# 删除视图`my_view`,
drop view my_view;
check table my_view_son;

发现删除了my_view，而基于my_view视图生成的my_view_son视图无效，查看当前database的表如下图所示：

show tables;

其实D选项说法不太准确删除了my_view视图，my_view_son视图数据还是存在只是无效不能查询罢了。

位图知识补充

在MySQL中，视图不仅是可查询的，而且是可更新的。也就是增删改查的操作你都可以去做，但是创建这样的位图有前提条件限制，在以下创建位图是不可更新的。

• 聚合函数，如：MIN，MAX，SUM，AVG，COUNT等。
• DISTINCT子句
• GROUP BY子句
• HAVING子句
• UNION或UNION ALL子句
• 左连接或外连接。
• 常量视图
• SELECT子句中的子查询或引用该表的WHERE子句中的子查询出现在FROM子句中。
  除此之外如果需要只能显示或更新通过视图可见的数据，则在创建或修改视图时使用WITH CHECK OPTION。

# 创建视图
drop view if exists my_view_son;
create view my_view_son as (select *
                            from my_view
                            where salary > 8000) with check option ;

此时在添加数据如下所示：

insert interview.my_view_son (name, age, salary)
values ('美杜莎', 25, 3500)

添加美杜莎数据失败了，因为她的薪资没有超过8000失败，将薪资改为8500发现数据插入成功。

总结

视图是一条预编译的SQL语句，并不保存实际数据。它是基于原表生成的一个虚拟的表不占用物理空间，而且对于位图的操作最终都会体现在表的操作上。所以对位图数据进行增删改，原表的数据也随之发送改变，同理对原表的数据操作也会影响视图数据。