mysql常量，变量，循环语句，存储过程，触发器，分隔符_mysql常量与变量的运用

常量：参与运算给变量赋值

字符串：用成对的单引号/双引号括起来
数值型：二进制，十进制，八进制，十六进制
日期时间：年-月-日-时-分-秒-微妙
布尔值：ture/false【1/0】
null：未知，待定，没有值，无数据

变量：记录存放某一个时段的状态值

自定义变量/系统变量
自定义会话变量：断开连接前可用，断开连接后自动释放
自定义局部变量：在封装的储存程序内部可用，调用结束后释放。

会话变量定义：

set @var1 =值1;
set @var1 =值1,@var2=值2;

select 值1 into var1
select 值2 into var1 ,值2 into var2;
select min(book_price) into para1,max(book_price) into para2,avg(book_price) into para3;

局部变量定义:

declare var1
declare var1,var2
数据类型[default默认值]
作用:定义1-n个指定类型的局部变量,并且设置初始值
示例:
declare num int default 0;
declare只能用于begin…end的开头部分定义局部变量

全局系统变量
mysql启动时会根据系统配置定义全局系统变量，
并初始化为默认值(约393个全局系统变量)

生存期:从mysql启动时直到停止或重启
作用范围是整个系统,修改了它的值会影响整个系统。

程序结构
顺序，分支，循环

循环语句

创建存储过程

create procedure procedure_name([procedure_parameter[,...]])
[characteristic...] routine_body
1
2

procedure_name：存储名字
procedure_parameter：存储过程的参数
characteristic：存储过程的特性
routine_body：存储过程的sql语句代码，可用begin...end来标志sql语句的开始和结束。
1
2
3
4

查看存储过程状态信息

show procedure status like 'procedure_name'
1

查看存储过程定义信息

show create procedure procedure_name;
1

修改存储过程：用于修改储存过程的某些特征【读写权限】

alter procedure procedure_name [characteristic...];
1

修改储存过程的内容可先删除改储存过程，重新创建。

删除储存过程

drop procedure procedure_name;
1

分隔符delemiter

存储过程的语句结构

delimiter $ 
begin... 
end $ 
delimiter ;
1
2
3
4

实例：

创建一个存储过程，不带参数
创建存储过程：查看表里面的信息|

delimiter $
create procedure p1()
comment"查看所有table_e表的信息"
begin
select *
from table_e;
end $
delimiter ;
1
2
3
4
5
6
7
8

等同于执行

select * from table_e;
1

实例：

in 向过程传入值
向数据表里固定字段获取数据
创建一个有参数的存储过程

out 输出参数，过程向调用者传出值（返回变量中的数据，可以是多个）
向数据表里面获取固定字段数据，然后加工
创建有输出的存储过程

inout输入输出参数，向过程传入值，然后过程再返回来值，这些值是变量
输入字段里可选择的数据，然后加工

数据表里面的参考数据:table_e
r1,r2,r3,r4
a1,女,1,岁
a2,女,2,岁
a3,女,18,周岁
a4,男,4,岁
a5,男,5,岁
a6,男,6,岁
a7,男,7,岁

触发器：trigger

在数据表中发生了某个事件(擦痕入操作，删除操作，更新操作)
mysql数据库系统会会自动触发预先编写好的若干条sql语句

触发器的特点及作用
具有原子性，即所有的sql语句作为一个整体，要么全部不执行，要么全部执行
触发器类型
插入触发器，删除触发器，更新触发器

语法：

create trigger 触发名字 触发时间
触发事件
on 表名 for each row [触发]
触发体

create trigger terigger_name trigger_time
trigger_event
on table_name for each row [trigger_order]
trigger_body
1
2
3
4
5
6
7
8
9

触发事件：after before
触发事件：insert update delete
for each row:表示任意一条
on 表:表示作用于哪张表
触发体：表示触发器的要执行的具体的sql语句
查看触发器：

show triggers#查看所有触发器

show create trigger 触发名;#查看具体的触发器
1
2
3

删除触发器：

drop trigger 触发名;
drop trigger teigger_log_insert;
drop trigger teigger_log_update;
drop trigger teigger_log_delete;
1
2
3
4

实例

#创建表记录t_log
create table t_log
(
    id int auto_increment primary key,
    username varchar(20),
    logtime datetime
);
#创建数据表table_luichun
create table table_luichun(
    id int auto_increment primary key,
    username varchar(20),
    password varchar(20)
);

#创建触发器
create trigger teigger_log_insert
after insert
on table_luichun for each row
insert into t_log(username,logtime) values('插入了数据',now());
#创建触发器
create trigger teigger_log_delete
after delete
on table_luichun for each row
insert into t_log(username,logtime) values('删除了数据',now());
#创建触发器
create trigger teigger_log_update
after update
on table_luichun for each row
insert into t_log(username,logtime) values('更改了数据',now());

#插入数据
insert into table_luichun(username,password) values("帐号",'密码');
#删除数据
delete from table_luichun where username='帐号';
#更改 myda数据库下的tbale_luichun表中的信息，这里的主键是id
#mysql更改数据表里面的信息或数据
#方法一：
update table_luichun set username = '帐号1201' where(id='2');
#方法二：指定数据库名字
update myda.table_luichun set username = '帐号01' where(id='1');
#方法三：添加`符号
update `myda`.`table_luichun` set `username` = '帐号01' where(`id`='1');
#方式四：大写
UPDATE `myda`.`table_luichun` SET `username` = '帐号2' WHERE (`id` = '1');

##如果更新的where条件指定的步是主键则会抛出1175异常
##Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

select * from t_log;
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

实例2：
创建三个表
库存表stock
销售表sale
订单表order
当订单表添加一条数据时，销售表加1，库存表减1

#创建三个表
create table stock(
    id int auto_increment primary key,#用于自增长的商品号
    name varchar(20),
    number int default 0
);
create table sale(
    id int,
    name varchar(20),
    number int default 0
);
create table table_order(
    id int auto_increment primary key,#用于自增长的订单号
    stock_id int,
    name varchar(20),
    number int,
    logtime datetime default now()
);
#添加外键
#销售表外键用于当订单表有数据插入时，销售表可以通过该外键来查找
alter table sale add constraint fk_sale_id foreign key(id) references stock(id);
#订单表的外键，用于当订单表有数据插入时，库存表可以通过该外键来查找
alter table table_order add constraint fk_stock_id foreign key(stock_id) references stock(id);


#创建触发器,当向库存表条加了数据则在销售表添加数据
delimiter $
create trigger add_sale
after insert
on stock for each row
begin
	insert into sale(id,name,number) values(new.id,new.name,0);
end $
delimiter ;

#创键触发器,当向订单表中添加数据，销售表销量+1，库存表数量-1
delimiter $
create trigger add_order
after insert
on table_order for each row
begin
	update stock set number=number-new.number where id=new.stock_id;
	update sale set number=number+new.number where id=new.stock_id;
end $
delimiter ;

#插入数据
#向库存表中插入一条数据
insert into stock(name,number) values('热狗器',10);
#向订单表中添加一条数据
insert into table_order(name,number,stock_id) values('小龙虾','5','1');
#查看触发器
show triggers;
#删除触发器
drop trigger add_order;
drop trigger add_sale;
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

更新触发器
1：更新相当于删除旧数据，插入新数据
2：旧数据存储到old表中
3：新数据存储到new表中
案例
创建触发器[table_luichun]作用于数据表[t_stock],当数据表[t_stock]中的某一条信息被更新后，将会检查某字段[name]在更新后是否与更新前一致,用变量@check来记录

# 创建table_luichun 数据表
create table table_luichun(
    id int auto_increment primary key,
    name varchar(20)
);

#创建触发器
delimiter $
create trigger check_update
after update
on table_luichun for each row
begin
    if(new.name=old.name) then select '相同' into @check;
    else
    select '不同' into @check;
    end if;
end $
delimiter ;


#插入数据
insert into table_luichun(name) values('大佬');
#更新数据
update table_luichun set name='大佬俊' where id='1';
#查看变量
select @check;
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

创建删除触发器

#创建两个数据表
create table table_luichun01(
    id int auto_increment primary key,#用于自增长的商品号
    name varchar(20),
    number int default 0
);
create table table_luichun02(
    id int,
    name varchar(20),
    number int default 0
);
#创建外键
alter table table_luichun02 add constraint fk_luichun01_id foreign key(id) references table_luichun01(id);

#创建自动创建table_luichun02数据的触发器
delimiter $
create trigger add_luichun02
after insert
on table_luichun01 for each row
begin
    insert into table_luichun02 values(new.id,new.name,0);
end $
delimiter ;

#创建删除触发器
delimiter $
create trigger delete_luichun01
before delete#因为设置了外键，需要先删除外键关联的信息再进行删除主键中的信息
on table_luichun01 for each row
begin
    delete from table_luichun02 where (table_luichun02.id=old.id);
end $
delimiter ;
drop trigger delete_luichun01;

#向table_luichun01插入数据
insert into table_luichun01(name,number) values("小龙虾",10);
insert into table_luichun01(name,number) values("红烧肉",10);

#测试，删除tbale_luichun01里面的数据
delete from table_luichun01 where id='1';

#查看表
select * from table_luichun01;
select * from table_luichun02;
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