"""语法结构 create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row begin sql语句 end """
# 针对插入 create trigger tri_after_insert_t1 after insert on 表名 for each row begin sql代码。。。 end create trigger tri_after_insert_t2 before insert on 表名 for each row begin sql代码。。。 end
# 针对删除 create trigger tri_after_delete_t1 after delete on 表名 for each row begin sql代码。。。 end create trigger tri_after_delete_t2 before delete on 表名 for each row begin sql代码。。。 end
# 针对修改 create trigger tri_after_update_t1 after update on 表名 for each row begin sql代码。。。 end create trigger tri_after_update_t2 before update on 表名 for each row begin sql代码。。。 end
# 案例 CREATE TABLE cmd ( id INT PRIMARY KEY auto_increment, USER CHAR (32), priv CHAR (10), cmd CHAR (64), sub_time datetime, #提交时间 success enum ('yes', 'no') #0代表执行失败 );
CREATE TABLE errlog ( id INT PRIMARY KEY auto_increment, err_cmd CHAR (64), err_time datetime );
delimiter $$ # 将mysql默认的结束符由;换成$$ create trigger tri_after_insert_cmd after insert on cmd for each row begin if NEW.success = 'no' then # 新记录都会被MySQL封装成NEW对象 insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time); end if; end $$ delimiter ; # 结束之后记得再改回来，不然后面结束符就都是$$了
create table user( idint primary key auto_increment, name char(32), balance int );
insert into user(name,balance) values ('jason',1000), ('egon',1000), ('tank',1000);
# 修改数据之前先开启事务操作 start transaction;
# 修改操作 update user set balance=900 where name='jason'; #买支付100元 update user set balance=1010 where name='egon'; #中介拿走10元 update user set balance=1090 where name='tank'; #卖家拿到90元
# 站在python代码的角度，应该实现的伪代码逻辑， try: update user set balance=900 where name='jason'; #买支付100元 update user set balance=1010 where name='egon'; #中介拿走10元 update user set balance=1090 where name='tank'; #卖家拿到90元 except 异常: rollback; else: commit;
delimiter $$ create procedure p2( in m int, # in表示这个参数必须只能是传入不能被返回出去 in n int, out res int# out表示这个参数可以被返回出去 ) begin select tname from teacher where tid > m and tid < n; set res=0; # 用来标志存储过程是否执行 end $$ delimiter ;
# 3、存储过程与事务使用举例(了解) delimiter // create PROCEDURE p5( OUT p_return_code tinyint ) BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR set p_return_code = 1; rollback; END;
DECLARE exit handler for sqlwarning BEGIN -- WARNING set p_return_code = 2; rollback; END;
START TRANSACTION; update user set balance=900 where id =1; update user123 set balance=1010 where id = 2; update user set balance=1090 where id =3; COMMIT;
# if条件语句 delimiter // CREATE PROCEDURE proc_if () BEGIN declare i int default 0; if i = 1 THEN SELECT 1; ELSEIF i = 2 THEN SELECT 2; ELSE SELECT 7; END IF;
END // delimiter ; # while循环 delimiter // CREATE PROCEDURE proc_while () BEGIN
DECLARE num INT ; SET num = 0 ; WHILE num < 10 DO SELECT num ; SET num = num + 1 ; END WHILE ;
#2. 创建存储过程，实现批量插入记录 delimiter $$ #声明存储过程的结束符号为$$ create procedure auto_insert1() BEGIN declare i int default 1; while(i<3000000)do insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy')); set i=i+1; end while; END$$ #$$结束 delimiter ; #重新声明分号为结束符号
#3. 查看存储过程 show create procedure auto_insert1\G
#4. 调用存储过程 call auto_insert1(); # 表没有任何索引的情况下 select * from s1 where id=30000; # 避免打印带来的时间损耗 select count(id) from s1 where id = 30000; select count(id) from s1 where id = 1;
# 给id做一个主键 alter table s1 add primary key(id); # 速度很慢
select count(id) from s1 where id = 1; # 速度相较于未建索引之前两者差着数量级 select count(id) from s1 where name = 'jason'# 速度仍然很慢
""" 范围问题 """ # 并不是加了索引，以后查询的时候按照这个字段速度就一定快 select count(id) from s1 where id > 1; # 速度相较于id = 1慢了很多 select count(id) from s1 where id >1andid < 3; select count(id) from s1 where id > 1andid < 10000; select count(id) from s1 where id != 3;
alter table s1 drop primary key; # 删除主键 单独再来研究name字段 select count(id) from s1 where name = 'jason'; # 又慢了
create index idx_name on s1(name); # 给s1表的name字段创建索引 select count(id) from s1 where name = 'jason'# 仍然很慢！！！ """ 再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分 那这个树其实就建成了“一根棍子” """ select count(id) from s1 where name = 'xxx'; # 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了 select count(id) from s1 where name like 'xxx'; select count(id) from s1 where name like 'xxx%'; select count(id) from s1 where name like '%xxx'; # 慢 最左匹配特性
# 区分度低的字段不能建索引 drop index idx_name on s1;
# 给id字段建普通的索引 create index idx_id on s1(id); select count(id) from s1 where id = 3; # 快了 select count(id) from s1 where id*12 = 3; # 慢了 索引的字段一定不要参与计算
drop index idx_id on s1; select count(id) from s1 where name='jason'and gender = 'male'andid = 3and email = 'xxx'; # 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件 create index idx_name on s1(name); select count(id) from s1 where name='jason'and gender = 'male'andid = 3and email = 'xxx'; # 并没有加速
drop index idx_name on s1; # 给name，gender这种区分度不高的字段加上索引并不难加快查询速度
create index idx_id on s1(id); select count(id) from s1 where name='jason'and gender = 'male'andid = 3and email = 'xxx'; # 快了 先通过id已经讲数据快速锁定成了一条了 select count(id) from s1 where name='jason'and gender = 'male'andid > 3and email = 'xxx'; # 慢了 基于id查出来的数据仍然很多，然后还要去比较其他字段
drop index idx_id on s1
create index idx_email on s1(email); select count(id) from s1 where name='jason'and gender = 'male'andid > 3and email = 'xxx'; # 快 通过email字段一剑封喉
联合索引
1 2 3 4 5 6 7 8 9 10 11 12
select count(id) from s1 where name='jason'and gender = 'male'andid > 3and email = 'xxx'; # 如果上述四个字段区分度都很高，那给谁建都能加速查询 # 给email加然而不用email字段 select count(id) from s1 where name='jason'and gender = 'male'andid > 3; # 给name加然而不用name字段 select count(id) from s1 where gender = 'male'andid > 3; # 给gender加然而不用gender字段 select count(id) from s1 where id > 3;
# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间 create index idx_all on s1(email,name,gender,id); # 最左匹配原则，区分度高的往左放 select count(id) from s1 where name='jason'and gender = 'male'andid > 3and email = 'xxx'; # 速度变快