08_MySQL_第七课_视图、事务、索引 ——录于2018.9.16

• 视图
• 事务
• 索引

一、视图 view

• 视图是一张虚拟表。
  • 它表示一张表的部分或多张表的综合的结构。
• 视图仅仅是表结构，没有表数据。
  • 视图的结构和数据建立在表的基础上。
• 因为视图是一个表结构，所以创建视图后，会在数据库文件夹中多一个与视图同名的.frm文件。
• 简化代码，隐藏字段。提高安全性。

  1、创建视图

语法：

create [or replace] view 视图的名称
as
select 语句

案例：

select stuName,stuSex,writtenExam,labExam from stuInfo inner join stuMarks using(stuNo);  # 经典语句

create view vw_stu
as
select stuName,stuSex,writtenExam,labExam from stuInfo inner join stuMarks using(stuNo);  # 创建出一张虚拟表，隐藏部分字段。使用多张表。

2、查询视图

• 视图是一张虚拟表，视图的用法和表的用法一样。

update vw_stu set writtenExam=88 where stuName='ljk'; # 只能操作部分字段

3、查看视图的结构

desc 视图名;

4、查看创建视图的语法

show create view 视图名

show create view vw_stu\G

show tables;

show table status where comment='view' \G   #查找备注是视图的

5、更改视图

alter view 视图名
as
select 语句

alter view vw_stu             # 更改视图，相当于重新创建了张虚拟表
as
select stuId from stuInfo;

6、删除视图

drop view [if exists] 视图1,视图2,...

drop view vw_stu;

7、视图的作用

• 筛选数据，防止未经许可访问敏感数据
• 隐藏表结构
• 降低SQL语句的复杂度

8、视图的算法

• 子查询和视图不一样？？

• merge: 合并算法

  • 将视图的语句和外层的语句合并后再执行。

• temptable: 临时表算法

  • 将视图生成一个临时表，再执行外层语句。

• undefined: 未定义

  • MySQL到底用的是merge还是temptable由MySQL决定。
  • 这是一个默认的算法，一般视图都会选择merge算法，因为merge效率高。

• 案例：找出语文成绩最高的男生和女生

select * from (selcet * from stu order by ch desc) as t group by stuSex; # 查询1

create view vw_stu
as
select * from stu order by ch desc;     # 将子查询的内容 作为视图

select * from vw_stu group by stuSex;   # 结果和查询1 不一样，原因是视图的算法造成的

解决：在创建视图的时候指定视图的算法

show create view vw_stu \G

create algorithm=temptable view 视图名
as
select 语句

select * from vw_stu group by stuSex;   # 结果和查询1 一样

二、事务 transaction

• 一个功能的使用，同时执行多个sql语句时，适合使用事务。
• 事务是一个不可分割的执行单元
• 事务作为一个整体要么一起执行，要么一起回滚。
• Delimiter //
• 只有innodb和BDB才支持事务，myisam不支持事务。

1、事务操作

开始事务：start transaction 或 begin [work]
提交事务：commit
回滚事务：rollback

案例：

create table bank(
  carId char(4) primary key,
  money int
)

delimiter # 更改定界符

start transaction;  # 开启事务
update bank set money=money-100 where carId='10001';
update bank set money=money+100 where carId='10002';

commit   # 提交事务

rollback # 回滚事务

2、设置事务的回滚点

语法：

设置回滚点：savepoint 回滚点名
回滚到回滚点： rollback to 回滚点

案例：

start transaction;
insert into bank values('10003',1000);
savepoint aa; # 设置回滚点 aa
insert into bank values('10004',500);
savepoint bb; # 设置回滚点 bb
rollback to aa; 
commit; # 提交事务

3、事务的特性（ACID）

• 原子性（ Atomicty ）： 事务是一个整体，不可以再分，要么一起执行，要么一起不执行。
• **一致性（ Consistency ）: ** 事务完成时，数据必须处于一致的状态。
• 隔离性（ Isolation ）: ** ** 每个事务都是相互隔离的。———抢购：两个事务操作的时候，一个操作，另一个被限制。 锁表了！ 慎用！！！！
• **永久性（ Durability ）: ** 事务完成后，对数据的修改是永久性的。

三、索引 index

• 根据索引键查找定位数据行
• MySQL中的数据也是按页（4kb）存放
• 索引：是MySQL编排数据的内部方法。它为MySQL提供一种方法来编排查询数据。
• 索引页：数据库中存储索引的数据页；索引页类似于汉语子（词）典中按拼音或笔画顺序的目录页。
• 索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。

1、索引的优缺点

• 索引的优点：查询速度快
• 索引的缺点：
  • 增、删、改（数据操作语句）效率低了
  • 索引占用空间

2、索引的类型

• 普通索引
• 唯一索引（唯一键）
• 主键索引：只要主键就自动创建主键索引，不需要手动创建
• 全文索引，搜索引擎使用，MySQL不支持中文的全文索引，我们通过sphinx去解决中文的全文索引。

3.1、创建普通索引

语法：

create index 索引名 on 表名 (字段名)                # 创建索引

alter table 表名 add index [索引的名称] （列名）    # 修改表的时候创建索引

案例：

create index ix_stuName on stuInfo(stuName);

alter table stuInfo add index ix_address (stuAddress);

create table temp(
id int,
name varchar(10),
index ix_name(name)                                   # 创建表的时候添加索引
)

3.2、创建唯一索引

语法：

create unique index 索引名 on 表名 （字段名）              # 添加 unique

alter table 表名 add unique index [索引的名称]（列名）     # 添加 unique

创建表的时候添加唯一索引，和创建唯一键是一样的。

案例：

create unique index UQ_stuName on stuInfo(stuName);

alter table stuInfo add unique UQ_address(stuAddress);

create table stu2(
id int,
name varcha(20),
unique UQ_name (stuName)    # 唯一键指定名字
);

4、删除索引

语法：

drop index 索引名 on 表名

案例：

drop index ix_stuName on stuInfo; 

5、创建索引的原则

• 该列用于频繁搜索
• 该列用于排序
• 公共字段要创建索引
• 如果表中的数据很少，不需要创建索引。
  • MySQL搜索索引的时间比逐条搜索数据的时间要长。—–此时没必要建索引。
• 如果一个字段上的数据只有几个不同的值，改字段不合适做索引，比如性别。——此时也没有必要建索引。

赏

谢谢你请我吃糖果

支付宝

微信

• 本文作者： 梁俊可
• 本文链接： http://ljk3d.com/2021/10/19/mySqlNote/08_MySQL_第七课_视图、事务、索引 ——录于2018.9.16/
• 版权声明： 梁俊可工作室