JavaWeb基础入门——（二）MySQL数据库基础（7-视图）

十、视图

10.1 视图的概念

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

10.2 视图的作用

• 安全性：如果我们直接将数据表授权给用户操作，那么用户可以CRUD数据表中所有数据，加入我们想要对数据表中的部分数据进行保护，可以将公开的数据生成视图，授权用户访问视图；用户通过查询视图可以获取数据表中公开的数据，从而达到将数据表中的部分数据对用户隐藏。
• 简单性：如果我们需要查询的数据来源于多张数据表，可以使用多表连接查询来实现；我们通过视图将这些连表查询的结果对用户开放，用户则可以直接通过查询视图获取多表数据，操作更便捷。

10.3 创建视图

语法

create view <view_name>
AS
select_statement

实例

• 实例1：

-- 创建视图实例1：将学生表中性别为男的学生生成一个视图
create view view_test1
AS
select * from students where stu_gender='男';
-- 查询视图
select * from view_test1;

• 示例2：

-- 创建视图示例2：查询班级编号为2的班级的学员信息
create view view_test2
AS
select s.stu_num,s.stu_name,s.stu_gender,s.stu_age,c.class_name
from classes c right join students s
on c.class_id=s.cid
where c.class_id=2;
-- 查询视图
select * from view_test2;

10.4 视图数据的特性

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

• 查询操作：如果在数据表中添加了新的数据，而且这个数据满足创建视图时查询语句的条件，通过查询视图也可以查询出新增的数据；当删除原表中满足查询条件的数据时，也会从视图中删除。
• **新增数据：**如果在视图中添加数据，数据会被添加到原数据表
• **删除数据：**如果从视图删除数据，数据也将从原表中删除
• **修改操作：**如果通过修改数据，则也将修改原数据表中的数据视图的使用建议 : 对复杂查询简化操作，并且不会对数据进行修改的情况下可以使用视图。

10.5 查询视图结构

-- 查询视图结构
desc view_test2;

10.6 修改视图

-- 方式1
OR REPLACE（替换）
create OR REPLACE view view_test1
AS
select * from students where stu_gender='女';
-- 方式2
alter（直接进行修改）
alter view view_test1
AS
select * from students where stu_gender='男';

10.7 删除视图

• 删除数据表时会同时删除数据表中的数据，删除视图时不会影响原数据表中的数据

-- 删除视图
drop view view_test1;

十一、索引

数据库是用来存储数据，在互联网应用中数据库中存储的数据可能会很多(大数据)， 数据表中数据的查询速度会随着数据量的增长逐渐变慢 ，从而导致响应用户请求的速度变慢——用户体验差，我们如何提高数据库的查询效率呢？

11.1 数据准备

create table tb_testindex(
fid int primary key,
sid int unique,
tid int,
name varchar(20),
remark varchar(20)
);
create procedure proc_readydata()
begin
    declare i int default 1;
    while i<=5000000 do
        insert into tb_testindex(fid,sid,tid,name,remark)
        values(i,i,i,'test_name','text_remark');
        set i = i+1;
    end while;
end;

11.2 索引的介绍

索引，就是用来提高数据表中数据的查询效率的。

索引，就是将数据表中某一列/某几列的值取出来构造成便于查找的结构进行存储，生成数据表的 目录

当我们进行数据查询的时候，则先在 目录 中进行查找得到对应的数据的地址，然后再到数据表中根据地址快速的获取数据记录，避免全表扫描

11.3索引的分类

MySQL中的索引，根据创建索引的列的不同，可以分为：

• 主键索引：在数据表的主键字段创建的索引，这个字段必须被primary key修饰，每张表只能有一个主键
• 唯一索引：在数据表中的唯一列创建的索引(unique)，此列的所有值只能出现一次，可以为NULL
• 普通索引：在普通字段上创建的索引，没有唯一性的限制
• 组合索引：两个及以上字段联合起来创建的索引

说明 :

1. 在创建数据表时，将字段声明为主键（添加主键约束），会自动在主键字段创建主键索引；
2. 在创建数据表时，将字段声明为唯一键（添加唯一约束），会自动在唯一字段创建唯一索引；

11.4 创建索引

11.4.1 唯一索引

-- 创建唯一索引: 创建唯一索引的列的值不能重复
-- create unique index <index_name> on 表名(列名);
create unique index index_test1 on tb_testindex(tid);

11.4.2 普通索引

-- 创建普通索引: 不要求创建索引的列的值的唯一性
-- create index <index_name> on 表名(列名);
create index index_test2 on tb_testindex(name);

11.4.3 组合索引

-- 创建组合索引
-- create index <index_name> on 表名(列名1,列名2...);
create index index_test3 on tb_testindex(tid,name);

11.4.4 全文索引(了解即可)

MySQL 5.6 版本新增的索引，可以通过此索引进行全文检索操作，因为MySQL全文检索不支持中文，因此这个全文索引不被开发者关注，在应用开发中通常是通过搜索引擎（数据库中间件）实现全文检索

create fulltext index <index_name> on 表名(字段名);

11.5 索引使用

索引创建完成之后无需调用，当根据创建索引的列进行数据查询的时候，会自动使用索引；

组合索引需要根据创建索引的所有字段进行查询时触发（例如：tid=250000 andname=‘aaa’）。

• 在 命令行窗口中可以查看查询语句的查询规划：

explain select * from tb_testindex where tid=250000\G;

11.6 查看索引

-- 命令行
show create table tb_testindex\G;

-- 查询数据表的索引
show indexes from tb_testindex;
-- 查询索引
show keys from tb_testindex;

11.7 删除索引

-- 删除索引：索引是建立在表的字段上的，不同的表中可能会出现相同名称的索引
-- 因此删除索引时需要指定表名
drop index index_test3 on tb_testindex;

11.8 索引的使用总结

11.8.1 优点

• 索引大大降低了数据库服务器在执行查询操作时扫描的数据量，提高查询效率
• 索引可以避免服务器排序、将随机IO变成顺序IO

11.8.2 缺点

• 索引是根据数据表列的创建的，当数据表中数据发生DML操作时，索引页需要更新；
• 索引文件也会占用磁盘空间；

11.8.3注意事项

• 数据表中数据不多时，全表扫面可能更快吗，不要使用索引；
• 数据量大但是DML操作很频繁时，不建议使用索引；
• 不要在数据重复度 高的列上创建索引（性别）；
• 创建索引之后，要注意查询SQL语句的编写，避免索引失效。

十二、数据库事务

12.1 数据库事务介绍

我们把完成特定的业务的多个数据库DML操作步骤称之为一个事务

事务，就是完成同一个业务的多个DML操作

-- 取钱业务：张三去银行取出1000
-- 操作1：张三的帐号-1000
-- 操作2：张三的拿到+1000

12.2 数据库事务特性

ACID特性，高频面试题

• 原子性（Atomicity）：一个事务中的多个DML操作，要么同时执行成功，要么同时执行失败
• 一致性（Consistency）：事务执行之前和事务执行之后，数据库中的数据是一致的，完整性和一致性不能被破坏
• 隔离性（Isolation）：数据库允许多个事务同时执行(张三去银行取钱的同时允许李四同时取钱),多个并行的事务之间不能相互影响
• 持久性（Durability）：事务完整之后，对数据库的操作是永久的

12.3 MySQL事务管理

12.3.1 自动提交

在MySQL中，默认DML指令的执行时自动提交的，当我们执行一个DML指令之后，自动同步到数据库中

12.3.2 事务管理

开启事务，就是关闭自动提交

• 在开始事务第一个操作之前，执行 start transaction 开启事务
• 依次执行事务中的每个DML操作
• 如果在执行的过程中的任何位置出现异常，则执行 rollback 回滚事务
• 如果事务中所有的DML操作都执行成功，则在最后执行 commit 提交事务

create database db_test3;
use db_test3;
# 库存表
create table stock(
    id int primary key auto_increment,
    name varchar(200),
    num int not null
)
# 订单表
create table order_db(
	id int primary key auto_increment,
    name varchar(200) not null,
    price double,
    num int
);
insert into stock(name,num) values('鼠标',10);
insert into stock(name,num) values('键盘',20);
insert into stock(name,num) values('耳机',30);
# 开启事务
start transaction;
# 操作1：扣减库存
update stock set num = num-1 where name = '鼠标';
select aaa; # 此处会执行失败
# 操作2：新增订单
insert into order_db(name,price,num) values('鼠标',20.5,1);
# 事务回滚：清除缓存中的操作，撤销当前事务已经执行的操作
rollback;
# 提交事务: 将缓存中的操作写入数据文件
commit;

12.4 事务隔离级别

数据库允许多个事务并行，多个事务之间是隔离的、相互独立的；如果事务之间不相互隔离并且操作同一数据时，可能会导致数据的一致性被破坏。

MySQL数据库事务隔离级别：

1. 读未提交
2. 读已提交
3. 可重复读
4. 串行化

读未提交（read uncommitted）

T2可以读取T1执行但未提交的数据；可能会导致出现脏读

脏读，一个事务读取到了另一个事务中未提交的数据

读已提交（read committed）

T2只能读取T1已经提交的数据；避免了脏读，但可能会导致不可重复度（虚读）

不可重复度（虚读）: 在同一个事务中，两次查询操作读取到数据不一致

例如：T2进行第一次查询之后在第二次查询之前，T1修改并提交了数据，T2进行第二次查询时读取到的数据和第一次查询读取到数据不一致。

可重复读（repeatable read）

T2执行第一次查询之后，在事务结束之前其他事务不能修改对应的数据；避免了不可重复读(虚读)，但可能会导致幻读

幻读，T2对数据表中的数据进行修改然后查询，在查询之前T1向数据表中新增了一条数据，就导致T2以为修改了所有数据，但却查询出了与修改不一致的数据（T1事务新增的数据）

串行化(serializable)

同时只允许一个事务对数据表进行操作；避免了脏读、虚读、幻读问题

12.5 设置数据库事务隔离级别

我们可以通过设置数据库默认的事务隔离级别来控制事务之间的隔离性；也可以通过客户端与数据库连接设置来设置事务间的隔离性（在应用程序中设置–Spring）；

MySQL数据库默认的隔离级别为 可重复读

• 查看MySQL数据库默认的隔离级别

-- 在MySQL8.0.3 之前
select @@tx_isolation;
-- 在MySQL8.0.3 之后
select @@transaction_isolation;

• 设置MySQL默认隔离级别

set session transaction isolation level <read committed>;

本笔记参考于[B站千锋教育javaweb开发视频教程]，仅作学习用途，方便随时查看。
参考资料：B站千锋教育javaweb开发视频教程