MySQL视图

前言

数据表准备

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `work_id` varchar(32) DEFAULT NULL COMMENT '学号',
  `gender` varchar(2) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk;

CREATE TABLE `book` (
  `book_id` int(11) NOT NULL AUTO_INCREMENT,
  `book_name` varchar(32) DEFAULT NULL,
  `book_author` varchar(32) DEFAULT NULL,
  `book_price` double(4,2) DEFAULT NULL,
  `book_stock` int(11) DEFAULT NULL,
  `book_desc` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`book_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gbk;

CREATE TABLE `records` (
  `rid` int(11) NOT NULL AUTO_INCREMENT,
  `work_id` int(11) DEFAULT NULL,
  `borrow_num` int(3) DEFAULT NULL,
  `is_return` int(1) DEFAULT NULL,
  `borrow_date` date DEFAULT NULL,
  PRIMARY KEY (`rid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gbk;

1.1视图的概念

视图，就是由数据库中一张表或多张表根据特定的条件查询得出的数据构造成得虚拟表。

视图在数据库中不占用空间。

视图是虚拟表。

视图是查询数据表中满足特定条件的数据。

为什么要用视图 ？直接到数据表中查不可以吗 ？

有些例如user表中的邀请码字段并不想让用户查询出来，这个时候就可以用视图去处理，起到数据保护的作用。

如果在图书管理系统中，查询某个用户的借书记录，我们正常的操作是三表联查(book、students、records)，才能查到某个学生具体借阅了多少书。如果我们使用视图，提前将满足特定条件的数据放到视图中去，这样可以非常简单的sql语句的查询视图中的数据，就可以非常便捷查询到学生借阅书籍的情况。

1.2 视图的作用

• 安全性，如果我们直接将书写表授权给用户操作，那么用户可以CRUD数据表中的所有数据，假如我们想要对数据表中的部分数据进行保护，可以将公开的数据生成视图，授权用户访问视图，用户通过查询视图可以获取数据表中公开的数据，从而达到将数据表中的部分数据对用户隐藏。

• 简单性：如果我们需要查询的数据来源于多张数据表我们可以使用多表连接查询来实现；我们通过视图将这些连表查询对用户开放用户则可以直接通过查询视图来获取多表数据操作，操作更便捷。

1.3 创建视图

1.3.1 创建视图的语法

create view view_name
AS 查询语句;

1.3.2 示例1

# 生成视图
create view view_test1
as select * from student where gender = '男';


# 查询视图
select * from view_test1;

生成视图语句指的是拿student表中的性别为男的的数据生成一张虚拟表。

然后像查询普通表一样查询视图就可以得到刚刚保存到虚拟表中的数据了。

# 生成视图
create view view_test1
as select * from student where gender = '男';


# 查询视图
select * from view_test1;


# 创建借书记录表
create table records(
rid int auto_increment,
work_id int(11),
borrow_num int(3),
is_return int(1),
borrow_date date,
primary key(rid)
);

# 插入一点点数据呀
insert into records(work_id,borrow_num,is_return,borrow_date) values(10086,2,0,now());

# 创建book表
create table book(
book_id int auto_increment,
book_name varchar(32),
book_author varchar(32),
book_price double(4,2),
book_stock int(11),
book_desc varchar(32),
primary key(book_id)
);


# 批量插入一点点儿数据 
insert into book(book_name,book_author,book_price,book_stock,book_desc) 
values('Java程序设计','秃头',22.34,5,'一本经典的书籍'),('Java王者之路','熊大',45.65,6,'一本Java成神之路的书籍'),('MySQL权威指南','熊二',56.23,4,'MySQL最具权威的学习指导书');


# 在records表中插入一点点数据
insert into records(work_id,borrow_num,is_return,borrow_date) 
values(100191842,10,1,now());

# 创建视图示例2：查询学生借书的信息(学生名、图书名、借书数量)
select s.name,b.book_name,borrow_num
from book b inner join records r inner join student s 
on b.book_id = r.rid and r.work_id = s.work_id;


# 创建视图
create view view_test2
as 
select s.name,b.book_name,borrow_num
from book b inner join records r inner join student s 
on b.book_id = r.rid and r.work_id = s.work_id;


# 查询视图
select * from view_test2;

# 在records表中再插入一条数据
insert into records(work_id,borrow_num,is_return,borrow_date)
values(100191843,30,0,now());

# 查询视图 --牛啊，新增的借书记录直接保存在视图里了！！！！新增的借书记录也会在视图中更新。
select * from view_test2;

1.4 视图数据的特性

视图是虚拟表，查询视图的数据是来源于数据表的，当对视图数据进行操作时，对原数据表是否有影响呢 ？

查询操作：如果在数据表中添加了新的数据，而且这个数据满足创建视图时，查询语句的条件，通过视图也可以查询出新增的数据；

当删除原表中满足查询条件的数据时，也会从视图中删除。

新增操作：如果在视图中添加数据，数据会被添加到原表中。

insert into view_test1(name,work_id,gender,age)values('胡适',10011,'男',76);
select * from view_test1;

删除操作：如果从视图删除数据，数据也将从原表删除。

-- 从视图中删除数据
delete from view_test1 where name = '胡适';
select * from view_test1;

修改操作：在视图中修改数据，在原表中的数据也会被删除。

-- 从视图中修改数据
update view_test1 set name = '鲁迅' where name = '李四';

1.5 视图的使用建议

视图对复杂查询简化操作，并且不会对数据进行修改的情况下可以使用视图。

我们一般不会对视图进行增删改的操作，视图强调的是查，对数据起到安全保护的作用。

来自多张表的视图增删改是会报错的。

-- 在视图中能否添加、删除、修改来自多张表的视图view_test2呢 ？
insert into view_test2(name,book_name,borrow_num) values('孙中山','同盟会',10);

delete from view_test2 where name = '鲁迅';

update view_test2 set name = '周恩来' where name = '鲁迅';

经过笔者实践，视图来自单张表的时候，增删改查都是可以的；当视图的数据来自多张表的时候，增删会报错，但是可以更新会直接修改的。当然了视图就是强调的查询操作，查询自然也是可以执行的。

1.5 查询视图结构

-- 查询视图结构
desc view_test2;

1.6 修改视图

方式1：对于已经存在的视图 view_test1已经存在了，现在修改这个视图，可以使用 or replace关键字替换。

create or replace view view_test1
as
select * from student where gender = '女';

方式2：可以使用alter修改

alter view  view_test1 as select * from student where gender = '男';

方式1是替代原有的表，方式2是直接修改原有的表，虽然语句不同但是可以达到相同的修改视图的效果。

1.7 删除视图

drop view view_test1;

删除视图并不会影响原数据表中的数据。

经笔者测试，如果删除视图的原表，则视图将无法使用，报如下错误。

文档信息

版权声明：自由转载-非商用-非衍生-保持署名（创意共享3.0许可证）
发表日期：2022年3月15日

订阅