mysql数据库的基本操作(事务、变量、触发器、函数、存储过程)
目录:
1.事务
2.事务原理
3.事务--回滚点
4.自动事务
5.事务特性ACID
6.变量
7.触发器
8.创建触发器
9.查看触发器
10.使用触发器
11.触发器记录
12.if分支
13.while循环
14.函数
15.自定义函数
16.查看函数
17.删除函数
18.函数的参数
19.函数--作用域
20.存储过程
21.存储过程--参数
1.事务transaction <--返回目录
* 需求:有一张银行账户表,有A用户给B用户转账:A账户先减少,B账户增加,但是A操作完
之后断电了。
* 解决方案:A减少钱,但是不要立即修改数据表,B收到钱之后,同时修改数据表。
* 事务安全
- 事务:一系列连续的操作。这些操作要么全部成功,要么全部失败。
- 事务安全:一种保护连续操作同时满足的机制
- 事务安全的意义:保证数据操作的完整性
* 注意:引擎myisam不支持事务,也不支外键;innodb支持事务,也支持外键。
-- 创建表时指定引擎
drop table if exists tb_account;
create table tb_account(
number varchar(20) not null unique comment '账户',
name varchar(20) not null comment '户主名',
money decimal(10,2) default 0.0 comment '账户余额'
)charset utf8 engine innodb;
insert into tb_account values(null,'0001','张三',1000.00),(null,'0002','李四',1000.00);
* 手动开启事务:start transaction;
update tb_account set money = money -100 where id = 1;
update tb_account set money = money +100 where id = 2;
* 提交事务 commit;
* 回滚事务 rollback;
2.事务原理 <--返回目录
* 事务操作原理:事务开启之后,所有的操作都会临时保存到事务日志,事务日志只有在得到commit命令
才会同步到数据表,其他任何情况都会清空(rollback,断电,断开连接)
3.事务--回滚点 <--返回目录
* 设置回滚点:savepoint 回滚点名;
* 回到回滚点:rollback to 回滚点名;
4.自动事务 <--返回目录
* 在mysql中,默认的都是自动事务处理,用户操作完会立即同步到数据表中
* 自动事务,系统通过autocommit变量控制
show variables like 'autocommit'; select @@autocommit;
* 关闭自动事务处理 set autocommit = off/0;
* 自动事务处理关闭后需要手动提交或回滚
* 通常,我们都会使用自动事务, 开启 set autocommit = on/1;
5.事务特性ACID <--返回目录
* atomic[əˈtɒmɪk]:原子性,事务的整个操作是一个整体,不可分割,要么全部成功,要么全部失败
* consistency[kənˈsɪstənsi]:一致性,事务操作的前后,数据表的数据没有变化
* isolation:隔离性,事务操作时相互隔离,不受影响
* durability:持久性,数据一旦提交,不可改变,永久的改变数据表数据
* 锁机制:innodb默认是行锁,但是如果在事务操作过程中,没有使用到索引,那么系统会自动全表检索数据,
自动升级为表锁
6.变量 <--返回目录
* 系统变量:系统定义好的变量,用来控制服务器的表现的,如autocommit等
* show variables; -- 查看所有变量 variable[ˈveriəbl]
* 查看mysql的版本:select @@version; show variables like 'version';
* 修改系统变量:会话级别和全局级别
会话级别 set [@@]变量名 = 修改的值;比如:set autocommit = off/0;
全局级别 set global 变量名 = 值;
* 自定义变量 set @自定义变量=值;
select @自定义变量;
* 在mysql中,"="会默认的当成比较符号处理,mysql重写定义了赋值":="
- set @age := 20;
* mysql允许从数据表中获取数据,然后赋值给变量
方法1:边赋值,边查看结果
select @变量名 := 字段名 from 表名; -- 最后一个字段值赋值给变量
方法2:只有赋值,不看结果;数据记录最多只允许获取一条,mysql不支持数组
select 字段列表 from 表名 where id = 1 into @变量名1,@变量名2; -- 通过where条件限定只获取一条记录
* 所有自定义变量都是会话级别:当前客户端当次连接有效。
- 变量与当前用户关联,与数据库无关
7.触发器 <--返回目录
* 需求:有两张表,一张订单表,一张商品表,每生成一个订单,意味着商品的库存要减少
* 触发器:trigger[ˈtrɪgə(r)] ,事先为某张表绑定一段代码,当表中的某些内容发生改变的时候(增删改)
系统自动触发代码执行
* 触发器:事件类型,触发时间,触发对象
事件类型:增删改 insert delete update
触发时间:前后,before after
触发对象:表中的每一条记录
* 一张表中只能拥有一种触发时间的一种类型的触发器:一张表最多有6个触发器(增删改*前后)
8.创建触发器 <--返回目录
* 在mysql高级结构中,没有大括号,都是用对应的字符符号代替
* 临时修改语句结束符 delimiter 自定义符号
* 将临时修改修正过来 delimiter ;
* 触发器基本语法:
create trigger 触发器名 触发时间 触发类型 on 表名 for each row
begin -- 代表(
end -- 代码)
* 例子:
-- 创建表 tb_goods
create table tb_goods(
id int primary key auto_increment,
name varchar(20) not null,
price decimal(10,2) default 1,
inv int comment '库存数量'
)charset utf8 engine innodb;
insert into tb_goods values(null,'iphone6s',5288,100),(null,'iphone8',6288,100),(null,'iphone10',7288,100);
-- 创建表 tb_order
create table tb_order(
id int primary key auto_increment,
good_id int not null comment '商品id',
g_number int comment '商品数量'
)charset utf8 engine innodb;
=======================创建触发器 start ===========================
-- 临时修改语句结束符
delimiter $$
-- 触发器,订单生成一个,商品库存减少
create trigger after_order after insert on tb_order for each row
begin
update tb_goods set inv = inv -1 where id = 2;
-- select count(*) from tb_order; -- 出错,不允许返回一个结果
end
$$ -- 结束符号
-- 将临时修改修正过来
delimiter ;
=======================创建触发器 end ===========================
* 注意:delimiter前面不能有空格等空白。所有复制以上代码到sqlyog时去掉delimiter前面的空白
9.查看触发器 <--返回目录
show triggers [like];
show create trigger 触发器名;
10.使用触发器 <--返回目录
* 不需要手动调用,而是当某种情况发生时自动触发(上面的例子:当订单表插入记录自动触发)
* 触发器删除
触发器不能修改,只能先删除后更新
drop trigger 触发器名;
11.触发器记录 <--返回目录
* 触发器记录:不管触发器是否触发了,只要当某种操作准备执行,系统就会将当前要操作的
记录的当前状态和即将执行之后新的状态分别保留下来,供触发器使用;其中要操作的当前状态
保存到old中,操作之后的可能形态保存到new
* old代表的是旧记录,new代表的是新记录
- 删除的时候是没有new的,插入的时候是没有old
- 使用方式:old.字段名/new.字段名
* update tb_goods set inv = inv - new.g_number where id = new.good_id;
12.if分支 <--返回目录
* 需求:判断商品库存是否足够;如果订单的数量大于库存,insert失败
delimiter %%
create trigger before_order before insert on tb_order for each row
begin
-- 判断商品库存是否足够
-- 获取商品库存:商品库存在表中
select inv from tb_goods where id = new.good_id into @inv;
-- 比较库存
if @inv < new.g_number then
-- 库存不够:触发器没有提供能够阻止事件发生的能力,只能暴力终止
insert into XXX values(XXX);
end if
end
%%
delimiter ;
13.while循环 <--返回目录
* 语法
循环名:while 条件判断 do
-- 执行代码
-- 变更循环条件
-- 循环控制leave/iterate 循环名;
end while;
* 循环控制
mysql中没有对应的continue和break;
iterate:迭代,类似continue,后面的代码不执行,循环重新
leave:类似break
14.函数 <--返回目录
* 函数:将一段代码块封装到一个结构中,在需要执行代码块的时候,调用结构执行即可(代码复用)
* 函数分为:系统函数、自定义函数
* 系统函数:系统定义好的函数,直接调用即可。任何函数都有返回值,因此函数的调用是通过select调用
* 字符串相关
- substring函数:
set @cn = '世界你好';
select substring(@cn,1,2); -- 结果是输出'世界'
-- 字符串下标从1开始;substring函数操作单位是字符
- char_length:字符长度
- length:字节长度
- select char_lenght(@cn),length(@cn);
- instr:判断字符串是否在某个具体的字符串存在,存在返回位置,不存在返回0
select instr(@cn,'你好'); -- 3
- lpad(str,len,str2):左填充,将字符串str2填充到str左边,填充后总共len位字符
select lpad(@cn,8,'欢迎'); -- 欢迎欢迎世界你好
select lpad(@cn,7,'欢迎'); -- 欢迎欢世界你好
- insert:替换
select insert(@cn,3,1,'您');-- 从下标3开始,长度1,替换成'您';结果:世界您好
- strcmp:字符串比较 相等返回0,小返回-1,大返回1;看校对集,默认是不区分大小写
select strcmp(@str1,@str2);
- upper(字符串):将字符串全部变成大写
- lower(字符串):将字符串全部变成小写
* 日期时间相关:
select now(); -- 2018-10-07 01:30:54
select curdate(); -- 2018-10-07
select curtime(); -- 01:29:10
select month(curdate()); -- 返回当前日期的月份,1~12
select curdate(),curtime(),month(birthday字段) from tb_stu;
* 数学函数
set @x=9;
abs(@x) 求绝对值
sqrt(@x)求平方根
mod(@x,@y)取模,求余
* 加密函数
password(str)
md5(str)
encode(需要加密的字符串, 密钥字符串):加密,结果是二进制,需要blob类型存储
decode(需要加密的字符串, 密钥字符串):解密
15.自定义函数 <--返回目录
* 创建函数
DELIMITER $$
create function 函数名([形参列表]) returns 数据类型
begin
-- 函数体
-- 返回值 return 类型;
end
$$
DELIMITER ;
* 例子:
create function display() returns int
return 100;
- 自定义函数的调用 select display();
* 例子:
delimiter $$
create function fun_book (bookId int) returns varchar(20)
begin
return (select bookName from tb_book where bookTypeId=bookId);
end
$$
delimiter ;
-- 调用存储过程
select fun_book(1);
16.查看函数 <--返回目录
* show function status[like '模糊匹配'] [\G];
* 函数属于指定数据库,说明要在对应数据库下才可以调用。
* 查看函数的创建语句
show create function 函数名;
17.删除函数 <--返回目录
* drop function 函数名;
18.函数的参数 <--返回目录
* 函数定义时的参数是形参,调用时的参数是实参
* 需求:计算1-指定数之间的和
delimiter $$
create function display1(int_1 int) returns int
begin
set @i = 1; -- 定义回话级别的全局变量
set @res = 0; -- 定义回话级别的全局变量
while @i <= int_i do
set @res = @res + @i;
set @i = @i +1;
end while;
return @res;
end
$$
delimiter ;
-- 函数调用
select display(10); select @res,@i;
* 在函数内部@定义的变量在函数外部也可以访问
19.函数--作用域 <--返回目录
* mysql中的作用域与js中的作用域完全一样
- 全局变量可以在任何地方使用;局部变量只能在函数内部使用;
* 全局变量:set @变量名;定义的为全局变量
* 局部变量:使用declare [dɪˈkleə(r)]声明,并且必须在函数体开始之前定义;
delimiter $$
create function display2(int_1 int) returns int
begin
declare i int default 1; -- 定义局部变量i
declare res int default 0; -- 定义局部变量res
while i <= int_i do
set res = res + i;
set i = i +1;
end while;
return res;
end
$$
delimiter ;
20.存储过程 <--返回目录
* 存储过程简称过程procedure,是一种用来处理数据的方式。
存储过程是一种没有返回值的函数
* 创建过程
create procedure 过程名字([形参列表])
begin
过程体;
end;
* 查看过程与查看函数方法一样
* 调用过程 call 过程名();
* 删除过程 drop procedure 过程名;
* 例子:
delimiter $$
create procedure pro_book (in bt int, out count_num int)
reads sql data
begin
select count(*) from tb_book where bookTypeId=bt;
end
$$
delimiter ;
-- 调用存储过程
call pro_book(1,@total);
21.存储过程--参数 <--返回目录
* 函数的参数需要指定数据类型,过程比函数更严格
* 过程有自己的类型限定:三种类型
in:数据只是从外部传入到内部使用(值传递),可以是数值也可以是变量
out:
inout:
MySQL的参数模式可以被定义为IN、OUT和INOUT。
IN:这是缺省模式,它说明参数可以被传入存储程序内部,但是任何对于该参数的修改都不会被返回给调用它的程序。
OUT:这个模式意味着存储程序可以对参数复制(修改参数的值),并且这个被修改的值会被返回给它的调用程序。
INOUT:这个模式意味着程序既可以读取传入的参数,而且任何对于该参数的修改对于它的调用程序而言都是可见的。
---
posted on 2020-07-11 00:21 wenbin_ouyang 阅读(408) 评论(0) 编辑 收藏 举报