MySQL深入--事务、变量、触发器、分支结构函数和存储过程

1
2
3
4
5
6
7
8
9
10
11
use test2;

create table account(
id int not null primary key auto_increment,
u_id varchar(3) not null unique key,
name varchar(20) not null,
money int default 0);

insert into account values(default, '100', 'tom', 1000),
(default, '101', 'jack', 2000);

1
2
set global 变量=值;

1
2
3
4
5
6
7
delimiter $$
create trigger 名称  after/before  insert/delete/update  on  for each row
begin
	代码
end
delimiter ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
use test2;

drop table if exists items;
drop table if exists stock;

-- 库存
create table stock(
id int not null primary key auto_increment,
item_id varchar(3) not null unique key,
name varchar(20) not null,
stocks int default 0);

-- 上架商品
create table items(
id int not null primary key auto_increment,
item_id varchar(3) not null,
count int default 0,
key key1 (item_id),
foreign key (item_id) references stock(item_id));

insert into stock values(default, '101', 'banana', 20),
(default, '102', 'apple', 20),
(default, '103', 'orange', 20);

insert into items values(default, '101', 3),
(default, '102', 3),
(default, '103', 3);

1
2
3
4
5
6
if  判断条件 then
	满足条件的执行代码
else
	不满足条件的执行代码
end if;

1
2
3
4
5
6
7
循环名：while 条件 do
	满足条件的执行代码
	if 条件
		leave 循环名（相当于break） 或者  iterator 循环名（相当于continue）
	end if
end while;

1
2
3
4
5
6
7
8
delimiter $$
create function 函数名[(参数列表)] returns 返回类型
begin
	代码
end
$$
delimiter ;

1
2
3
4
5
6
7
8
delimiter $$
create procedure 过程名称([参数列表])
begin
	代码
end
$$
delimiter ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
# 1. 事务

use test2;

create table account(
id int not null primary key auto_increment,
u_id varchar(3) not null unique key,
name varchar(20) not null,
money int default 0);

insert into account values(default, '100', 'tom', 1000),
(default, '101', 'jack', 2000);


start transaction;
savepoint initial_state;

update account set money=money-500 where u_id='101';
update account set money=money+500 where u_id='100';

select * from account;


update account set name='tomm' where name='tom';

commit;


start transaction;
savepoint initial_state;

update account set money=money-500 where u_id='101';
update account set money=money+500 where u_id='100';

select * from account;

rollback to initial_state;

show variables like '%autocommit%';

############################################################################


# 2. 变量

set @v1 = 'hello';


select @v1=name from account where u_id='100';

select @v1:=name from account where u_id='100';

select @1;

select @v1:=name from account;


############################################################################


# 3. 触发器

use test2;

drop table if exists items;
drop table if exists stock;

-- 库存
create table stock(
id int not null primary key auto_increment,
item_id varchar(3) not null unique key,
name varchar(20) not null,
stocks int default 0);

-- 上架商品
create table items(
id int not null primary key auto_increment,
item_id varchar(3) not null,
count int default 0,
key key1 (item_id),
foreign key (item_id) references stock(item_id));

insert into stock values(default, '101', 'banana', 20),
(default, '102', 'apple', 20),
(default, '103', 'orange', 20);

insert into items values(default, '101', 3),
(default, '102', 3),
(default, '103', 3);

drop trigger if exists add_items;

-- 触发器
delimiter $$
create trigger add_items before update on items for each row
begin
    -- 触发器代码
    -- 判断是否需要补充货物
    if old.count = 1 then
        update stock set stocks=stocks-3 where item_id=new.item_id;
        set new.count=3;
    end if;
end
$$
delimiter ;


update items set count=count-1 where item_id='101';

select * from items;
select * from stock;



############################################################################


# 4. 分支结构函数
if 分支
if  判断条件 then
    满足条件的执行代码
else
    不满足条件的执行代码
end if;


while 循环结构（其他循环请百度）
循环名：while 条件 do
    满足条件的执行代码
    if 条件
        leave 循环名（相当于break） 或者  iterator 循环名（相当于continue）
    end if
end while;


select min(stocks),max(stocks) from stock;


select char_length('hello mysql');


语法
create function 函数名[(参数列表)] returns 返回类型
begin
    代码
end


drop function if exists get_stocks;

delimiter $$
create function get_stocks(id varchar(3)) returns int
begin
    set @s = (select stocks from stock where item_id=id);
    return @s;
end
$$
delimiter ;


show function status like 'get_%'G

select get_stocks('101');



############################################################################


# 5. 存储过程

语法
delimiter $$
create procedure 过程名称([参数列表])
begin
    代码
end
$$
delimiter ;


delimiter $$
create procedure pro1(in a1 int, out a2 int, inout a3 int)
begin
    select a1, a2, a3;
end
$$
delimiter ;


call pro1(@v1, @v2, @v3);

set @v1 = 1;
set @v2 = 2;
set @v3 = 3;



delimiter $$
create procedure pro2(in a1 int, out a2 int, inout a3 int)
begin
    select a1, a2, a3;
    set a1 = 11;
    set a2 = 12;
    set a3 = 13;
    select a1, a2, a3;
end
$$
delimiter ;


set @v1 = 1;
set @v2 = 2;
set @v3 = 3;
call pro2(@v1, @v2, @v3);

select @v1, @v2, @v3;