学习笔记mysql进阶篇

MySQL事务

一、事务的语法

1.start transaction;/begin
2.commit; 使得当前的修改确认
3.rolback;使得当前的修改被放弃

二、事务的ACID特性

1.原子性Atomicty

事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执⾏过程中出错，
会回滚到事务开始前的状态，所有的操作就像没有发⽣⼀样。也就是说事务是⼀个不可分割的整体，
就像化学中学过的原⼦，是物质构成的基本单位。

2.一致性(Consistency)

事务执行不会破坏数据库的完整性和一致性，
⽐如：如果从A账户转账到B账户，不可能因为A账户扣了钱，⽽B账户没有加钱。

3.隔离性(lsolation)

并发环境中事务之间相互隔离，每个事务操作相同的数据时，每个数据都有各自的完整的数据空间
隔离性分为4个级别

4.持久性（Duration）

事务提交后，数据库中的数据必须被永久保存下来。即使服务器系统崩溃或服
务器宕机等故障。只要数据库重新启动，那么⼀定能够将其恢复到事务成功结束后的状态。

三，事务并发问题

1.脏读：读取到没有提交的数据。A事务读取到B中没有提交的数据；
2.不可重复读: 同一条命令返回不同的结果（更新)，B在A多次读是过程中，更改数据并且提交，导致A多次读取不一致
3.幻读：重复查询的过程中，数据就发送了量的变化

四，事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交READ_UNCOMITTED	允许	允许	允许
读已提交READ_COMMITTED	禁止	允许	允许
可重复读REPEATABLE_READ	禁止	禁止	允许
顺序读SERIALIZABLE	禁止	禁止	禁止

查看当前会话中事务的隔离级别

select @@tx_isolation

设置当前会话中的事务隔离级别

set session transaction isolation level read uncommitted;

1.读未提交

读未提交，该隔离级别允许脏读取，其隔离级别是最低的。换句话说，如果⼀个事务正在处理某⼀数
据，并对其进⾏了更新，但同时尚未完成事务，因此还没有提交事务；⽽以此同时，允许另⼀个事务也能够访问该数据。
实例：

2.读已提交

读已提交是不同的事务执⾏的时候只能获取到已经提交的数据。 这样就不会出现上⾯的脏读的情况了。
但是在同⼀个事务中执⾏同⼀个读取,结果不⼀致

不可重读示例

事务A其实除了查询两次以外，其它什么事情都没做，结果钱就从1000变成0了，这就是不可重复读的
问题。

3.可重复读

可重复读就是保证在事务处理过程中，多次读取同⼀个数据时，该数据的值和事务开始时刻是⼀致的。

幻读：幻读就是指同样的事务操作，在前后两个时间段内执⾏对同⼀个数据项的读取，可能出现不⼀致的结果。
示例

4.顺序读

事务只能一个一个处理，不能并发

5，不同隔离级别的锁的情况

1. 读未提交（RU）: 有⾏级的锁，没有间隙锁。它与RC的区别是能够查询到未提交的数据。
2. 读已提交（RC）：有⾏级的锁，没有间隙锁，读不到没有提交的数据。
3. 可重复读（RR）：有⾏级的锁，也有间隙锁，每次读取的数据都是⼀样的，并且没有幻读的情况。
4. 序列化（S）：有⾏级锁，也有间隙锁，读表的时候，就已经上锁了

六，隐式提交

DQL:查询语句
DML:写操作(添加,删除,修改)
DDL:定义语句(建库,建表,修改表,索引操作,存储过程,视图)
DCL:控制语⾔(给⽤户授权,或删除授权)
DDL（Data Defifine Language）：都是隐式提交。
隐式提交：执⾏这种语句相当于执⾏commit; DDL

索引

什么是索引

索引类似图书目录。可以提高数据检索效率。降低数据库的IO成本
是帮助MySQL高效获取的数据结构
快速查找排好的一种数据结构

索引的分类有哪些

1.主键索引

根据主键，建立索引，不允许重复，不允许空值
如果表中没有定义主键，InnoDB会选择一个唯一的非空检索代替

2.唯一索引

建立索引的列必须是唯一的，允许空值

唯一索引:

Create table t1(
Id int unique,
)

Create table t1(
Id int,
Unique key uni_name (id)
)

表创建好之后添加唯一索引:

alter table s1 add unique key  u_name(id);

删除:

Alter table s1 drop index u_name;

3.普通索引

普通索引:
创建:
Create table t1(
Id int,
Index index_name(id)
)
Alter table s1 add index index_name(id);
Create index index_name on s1(id);

4.全文索引

对大文本对象的列构建的索引

5.组合索引

-PRIMARY KEY(id,name):联合主键索引
-UNIQUE(id,name):联合唯一索引
-INDEX(id,name):联合普通索引

最左原则：不可越过左边使用

6.索引原理

B+树

1.b+树只有叶子节点存储数据
2非叶子节点起到了索引的作业
3.所有叶子节点使用链表相连

使用b+树的好处：

1.降低磁盘读写的代价
2.顺序I/O提高效率
3.查询速度更稳定

聚簇索引和非聚簇索引

是否主键索引 --> 主索引 和 辅助索引

InnoDB中索引即数据，聚集索引的那颗B+数的叶子节点中已经把所有完整的用户记录都包含了，MyLSAM的索引方案虽然也使用形结构,但是却将索引和数据分开存储：

MyISAM的索引方案虽然也使用树形结构，单是却将索引和数据分开储存：索引文件
MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的数据，而是主键加行号的组合，即回行，索引MyISAM中的所有的索引都是非聚簇索引

MyISAM和InnoDB的区别

1.数据存储方式：
InnoDB:表结构+数据和索引
MyISAM：表结构+数据+索引

2.索引方式：
都是基于B+Tree的数据结构建立
InnoDB中主键索引为聚集索引，辅助索引是非聚集索引
MyISAM中数据和索引存在不同的文件中，因此都是非聚集索引

3.事务指出：
InnoDB支持事务
MyISAM不支持事务

存储过程-触发器-视图

1.存储过程

什么是存储过程？

a.存储过程简单来说，就是为以后的使⽤⽽保存 的⼀条或多条MySQL语句的集合。
b.储存过程是⼀组为了完成特定功能的SQL语句集，经过编译之后存储在数据库中，在需要时直接调⽤。
c.存储过程就像脚本语⾔中函数定义⼀样。

怎样定义存储过程

\d // 修改MySQL默认的语句结尾符 ; ，改为 // 。
create procedure 创建语句
BEGIN和END语句⽤来限定存储过程体

– 定义存储过程

\d //
create procedure p1()
begin
set @i=10;
while @i<90 do
insert into users values(null,concat('user:',@i),@i,0);
set @i=@i+1;
end while;
end;
//

查看存储过程

show create procedure pi\G

删除存储过程

drop procedure p1

MySQL触发器

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

说明：
trigger_name：触发器名称
 trigger_time:触发时间，可取值：BEFORE或AFTER
 trigger_event：触发事件，可取值：INSERT、UPDATE或DELETE。
 tb1_name：指定在哪个表上
 trigger_stmt：触发处理SQL语句。

– 查看所有的 触发器

show triggers\G;

– 删除触发器

drop trigger trigger_name;

在INSERT触发器代码内，可引⽤⼀个名为NEW的虚拟表，访问被 插⼊的⾏;
在DELETE触发器代码内，可以引⽤⼀个名为OLD的虚拟表，访问被删除的⾏;
	OLD中的值全都是只读的，不能更新。
在AFTER DELETE的触发器中⽆法获取OLD虚拟表
在UPDATE触发器代码中
	可以引⽤⼀个名为OLD的虚拟表访问更新以前的值
	可以引⽤⼀个名为NEW的虚拟表访问新 更新的值;

视图的语法

创建视图:

create view v_users as select id,name,age from users where age >= 25 and age= 35;

view视图的帮助信息:

mysql >?  view
ALTER VIEW
CREATE VIEW
DROP VIEW

查看当前库中所有的视图

show tables; 

–可以查看到所有的表和视图

show table status where comment='view'; 

–只查看当前库中的所有视图删除视图v_t1:

mysql> drop view v_t1;