举个例子:A给B转账,如果中途断电了?或者有其他操作修改了A的账目?操作被打断,中间的过程如果不可控,双方造成损失
-- 那就创建个账户表
create table my_account (id int primary key auto_increment,
account char(16) not null comment '银行卡号',
name varchar(20) not null comment '用户名',
money decimal(10,2) default 0.0 comment '钱 ')charset utf8 engine=innodb;
-- 如果你的数据库版本<=5.5,记得配置或者像这样指定存储引擎!
-- 基础篇部分用的MySQL版本是5.5,现在到了8.0,略有不同!
-- MyISAM不支持事务
insert into my_account values(default, '0000000000000001', 'Roy', 1234567);
insert into my_account values(default, '0000000000000002', 'Allen', 123459);
insert into my_account values(default, '0000000000000003', 'Tina', 15569);
开启事务
start transaction
或begin
-- 更新Roy的钱数
start transaction
update my_account set money = money-1000 where id=1;
-- 同时开两个client查看
提交
commit
回滚:清空事务日志,相当于操作执行失败
rollback
可以看出,连接中断、commit、rollback都会将事务日志清空
只有commit会对数据表产生影响
一次事务,某个操作成功完成后,后续的操作可能成功也可能失败,但如果失败,可以不回滚到原点,而是退回到已经成功的位置后!
很合理对吧?这个成功的位置就叫回滚点
-- Roy加工资
update my_account set money = money + 10000 where id = 1;
savepoint act1; -- 添加回滚点
-- Allen扣工资
update my_account set money = money - 1000 where id = 1; -- 误操作
-- 退回到回滚点
rollback to act1;
-- 重新扣钱!
update my_account set money = money - 1000 where id = 2;
-- 查看结果
select * from my_account;
-- 如果成功,提交结果
commit;
show variables like 'autocommit';
set autocommit = off;
为了解决这些并发问题,实现事务的四大特性,数据库定义了4种不同的事务隔离级别
隔离级别为可重复读时,如果检索条件有索引(包括主键索引)的时候,默认加锁方式是next-key锁
串行化,最高隔离级别,所有事务依次执行,读写数据都会锁住整张表,不可能互相干扰(否:就是不会出现)
事务隔离级别 | 名称 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|---|
read-uncommitted | 读未提交,也叫脏读 | 是 | 是 | 是 |
read-committed | 不可重复读,也叫读已提交 | 否 | 是 | 是 |
repeatable-read | 可重复读,默认级别 | 否 | 否 | 否 |
serializable | 串行化 | 否 | 否 | 否 |
查看隔离级别:show variables like 'transaction_isolation';
学完了事务,详细了解下MySQL的锁机制,这个锁并不是只针对事务完整性的,而是应用在任何操作数据的场景中
在关系型数据库中,可以按照锁的粒度把数据库锁分为
InnoDB默认行锁(row-level locking)
共享锁: 又叫做读锁
排他锁: 又叫做写锁
InnoDB是基于索引来完成行锁,在使用主键索引的情况下,例如:
start transaction;
-- 索引机制后续介绍(我还不会)
update my_account set money = money + 1000 where id = 1;
也可以通过如下方式实现行锁
select * from tab_with_index where id = 1 for update;
如果没有使用主键索引,则自动开始全表索引,即会形成表锁,例如
update my_account set money = money + 1000 where name = 'Roy';
-- 然后再开始另一个事务,会发现其在等待
-- 说白了,就不能并发了!
-- 当然,我们可以给name字段加index
所以说,一定要有索引,并使用索引加锁
表已锁,相当于到了serializable隔离级别,只能等待第一个事务解锁:Lock wait timeout exceeded; try restarting transaction
又是隔离级别又是锁的(人家心好乱),什么关系呢?他们是有对应关系的
系统定义好的控制服务器的变量,一般不会修改
-- 查看变量名
show variables; -- 查看所有,大概300个
-- 查看version这个变量值
select @@var_name;-- 必须使用 @@
-- 也可具体查看:
-- 查看会话变量
select @@session.var_name;
show session variables like "%var%";
-- 查看全局变量
select @@global.var_name;
show global variables like "%var%";
修改系统变量分为会话级别和全局修改
-- 会话级别修改
set autocommit = 0;
set @@autocommit = 0;
-- 全局级别
set global autocommit = 0; -- 一经修改,永久有效
persist
,可直接将设置持久化在文件为了与系统变量区分,必须使用一个@
符号
set @name = 'Roy';
-- 查看
select @name;
在mysql中=
默认用来比较而不是赋值,所以也可以使用:=
赋值,以示区分
set @name := 'Allen';-- 由于使用set,系统知道是赋值,可以使用 =
MySQL也允许从表中获取数据复制 给变量
-- 方案一:
select @height := height from my_stu where id =1;-- 如果不使用where条件查询到多条,则会将最后一条赋值给变量
-- 方案二:
select height sex from my_stu where id = 1 into @height, @sex;-- 只能查到一条进行赋值,不然报错:Result consisted of more than one row
select @height, @sex;
所有自定义的变量都是会话级别(在内存中),作用域与生命周期均限于当前客户端连接,但不区分数据库
上面是会话变量,还有局部变量
drop procedure if exists add;
create procedure add
(
in a int,
in b int
)
begin
declare c int default 0;
set c = a + b;
select c as c;
end;
一般用于SQL的语句块中,后面的SQL编程中会说到
需求:订单表和库存表,每生成一个订单,库存对应减少
触发器(trigger):事先为某张表绑定一段代码,当表中的内容发生改变时,执行代码!
-- 库存表
create table my_goods(id int primary key auto_increment,
name varchar(20) not null,
price decimal default 1,
inv int comment '库存数量')charset utf8 engine=innodb;
insert into my_goods values(1, 'p30', 6666, 30), (2, 'iPhone', 8888, 50);
-- 订单表
create table my_order(id int primary key auto_increment,
g_id int comment '商品id',
g_num int comment '商品数量')charset utf8 engine=innodb;
触发事件类型:增删改
触发时间:before/after
触发对象:表中的每一行记录
一张表中只能有一种触发时间的一种触发类型的触发器,即一张表中最多有2x3=6个!
MySQL的高级结构中没有大括号,使用符号代替
-- 临时修改语句结束符,否则触发内容给会被分号中断
delimiter $$
-- after_order是触发器名;after insert组合,表示在插入之后触发,需要使用关键字on
-- 这行别使用分号分割,以下是一整条指令!!!
create trigger after_order after insert on my_order for each row
-- begin/end代表{}
begin
-- 触发以下代码执行:
update my_goods set inv = inv - 1 where id = 1; -- 这里先指定id;分号起到的作用是分隔指令,不再代表输入结束
end$$ -- 触发器创建结束
delimiter ; -- 修改回来
可以查看所有触发器或者其创建语句
show triggers\G -- 查看所有
show create trigger after_order\G -- 查看创建语句
通过系统变量查看
select * from information_schema.triggers\G;
创建的触发器限定了id=1
,即不是动态的
insert into my_order values(default, 1, 'iPhone');
触发器不能直接修改,只能删除再创建
drop trigger after_order;
无论是否触发,只要当相关操作准备执行,系统就会将当前要操作记录的当前状态和执行后的新状态分别保留下来,供触发器使用
当前状态保存在old中,之后的状态保存在new中(看到上面的红框框了吗?)
类似事务,还没更新到数据表,但是可以查看操作完成后的状态
删除的时候没用new,插入的时候没有old
重新创建我们的触发器,修改id问题
delimiter $$
create trigger after_order after insert on my_order for each row;
begin
-- 插入没有old,看清楚下面的调用方式
-- 插入后的订单记录保存在new中
update my_goods set inv = inv - new.g_num where id = new.g_id
end
$$
delimiter ;
如果触发器中只有一条命令,可以省略大括号(begin/end)
-- 测试一下吧!
insert into my_order values(default, 2, 5);
ifelse
、loop
、recursion
-- if语句
-- 判断库存够不够
delimiter $$
create trigger after_order after insert on my_order for each row;
begin
select inv from my_goods where id = new.g_id into @inv; -- 使用变量
if @inv < new.g_num then -- 库存不够
insert into XXX values(XXX);-- 暴力报错
end
$$
delimiter ;
-- 也会阻止订单的产生
-- while循环,mysql没有for循环
-- 举例:累加函数
delimiter //
create function accumulation(a int) returns int
begin
set @i = 1; -- set定义的@标志的变量是全局的
set @res = 0; -- 返回值
while @i <= a do -- 循环
set @res = @res + @i; -- mysql 没有 += ++
set @i = @i + 1; -- 修改变量必须使用set
end while; -- 结束循环
return @res;
end
//
delimiter ;
select
调用-- substring() 字符串截取
set @cn = '杨瑞';
set @en = 'Roy';
select substring(@cn, 1, 1); -- 目标字符串 起始位置(从1开始) 长度(以字符为单位)
select substring(@en, 1, 1);
-- 可以在定义变量前设置字符集,set names gbk;
-- 一般均以字符为最小单位分割,英文字符站1字节,中文字符占3字节而已
-- char_length() 字符长度
-- length() 字节长度
select char_length(@cn), char_length(@en), length(@cn), length(@en);
-- instr() 判断字符串在某个字符串的具体位置
select instr(@cn, '瑞'), instr(@en, 'o'), instr(@cn, 'fuc');-- 如果没有返回 0
-- lpad() 左填充,使用给出的字符填满到定义的长度
select lpad(@cn, 10, '谁最帅?'),lpad(@en, 10, 'Great');
-- insert() 替换
select insert(@en, 1, 3, 'f');-- 从1到3 的位置换为一个f
-- strcmp() 比较,大于返回1,小于返回-1, 相等返回0
set @a = 'abc';
set @b = 'abd';
set @c = 'ABD';
select strcmp(@a, @b), strcmp(@b, @a), strcmp(@b, @c);
定义:所有的块定义都是用begin
和end
包起来
-- 比较特殊,先指定返回值,使用returns
delimiter //
create function say() returns int
begin -- 可省略
return 100;
end
//
delimiter ;
create function test(a int) returns int
return a;
全局级别,除非手动删除
查看函数
show function status\G -- 可以使用模糊匹配 like %_
show create function test\G
删除函数
-- 函数不能直接修改,只能先删除后新增
drop function test;
作用域
-- MySQL中的作用域赫尔js完全相同
-- 全局变量:使用set定义,@标志
-- 局部变量:使用declare定义
-- 举例:定义累加器,不加6的倍数
delimiter //
create function accumulation1(b int) returns int
begin
declare i int default 1; -- 允许有属性
declare res int default 0;
while_case: while i <= b do
if i%6 = 0 then -- 这里的等号是比较
set i = i + 1; -- 所有变量修改都是用set
iterate while_case; -- 迭代,作用是结束此次循环,相当于continue
end if;
set res = res + i;
set i = i + 1;
end while;
return res;
end
//
delimiter ;
可以理解为没有返回值的函数,使用procedure
关键字
创建
create procedure pro()
select * from my_class; -- 省略大括号
查看:类似函数,替换关键字即可
show procedure status\G
show create procedure pro\G
调用: 没有返回值了,select不能用,有个专门的关键字call
call pro();
删除
drop procedure pro; -- 同样的,不能直接修改
参数:函数的参数需要指定类型,过程的参数还需要限定,限定分为三种
out和inout都是引用传递,内部修改会影响外部
delimiter //
create procedure pro1(in a int, out b int, inout c int)
begin
select a, b, c; -- 局部变量
end
//
delimiter ;
set @a = 1;
set @b = 2;
set @c = 3;
select @a, @b, @c;
call pro1(@a, @b, @c);
select @a, @b, @c; -- 观察结果
在过程执行完后,out
和inout
限定的局部变量会把值更新到对应的全局变量
delimiter //
create procedure pro2(in a int, out b int, inout c int)
begin
set a = 4;
set b = 5;
set c = 6;
select a, b, c; -- 修改局部变量
select @a, @b, @c;
set @a = 10;
set @b = 100;
set @c = 1000; -- 修改全局变量
select @a, @b, @c;
end
//
delimiter ;
存储过程因为会将SQL语句预编绎,运行的速度比较快
因为类似于函数,所以也具有函数的部分性质
微信文件传输助手是微信电脑版与手机微信之间相互传输图片等文件的好工具,但很...
本文将研究 ES6 的 for ... of 循环。 旧方法 在过去,有两种方法可以遍历 javas...
ADO对象: Connection Command Recordset Record Stream ASP支持的对象很多,可...
【排序算法】之lowb三人组冒泡、插入、选择 什么是lowb三人组 冒泡排序bubble so...
前言 相信大家都知道在IDE中代码的智能提示几乎都是标配,虽然一些文本编辑器也...
一、正则表达式概述 二、正则表达式在VBScript中的应用 三、正则表达式在VavaScr...
计算属性computed: 支持缓存,只有依赖数据发生改变,才会重新进行计算 不支持...
歌词编辑器 歌词编辑器 第一步:选择要播放的歌曲并播放 第二步:填写全部的歌词...
一石激起千层浪,继中国区浩浩荡荡的大裁员告一段落之后,甲骨文并未因此收起手...
vbs:把一段文字中指定字符颜色变成红色的正则 functionc(Tstr,Word) Dimre Setre...