使用存储过程
- 使用存储过程的好处:简单、安全、高性能
-
delimiter | 更改命令行语句分隔符，除\外的任何字符都可作为分隔符
- 所有mysql的变量均要以@开始
- mysql的参数赋值语句必须是只能够选出一行，所以若有需要则加上 limit 1
- mysql支持 in(传递给存储过程)，out(从存储过程传出)，inout(对存储过程传入传出)
- crate procedure语句可以包含一个comment值，其会在 show procedure status中显示，
show procedure status like 'FunTest' 来查询存储过程FunTest的信息
-
show create procedure FunTest可以查看存储过程的具体信息
使用游标
- 游标是一个存储在mysql上的数据库查询，他不是一条select语句，而是被该语句检索出来的结果集。mysql中的游标只能用于存储过程
- 使用游标的步骤:先进行定义，然后打开(此过程进行实际的数据检索)，然后使用检索出的数据，最后关闭
- declare定义局部变量必须在定义任意游标或句柄前，句柄的定义必须在游标后
使用触发器
- mysql触发器响应delete insert update而自动执行
- 只有表才支持触发器，视图不支持(临时表也不支持)
- 每个表的每个事件每次只允许一个触发器(对于一张表最多能定义6种触发器)
- 如果before触发器失败，则mysql将不执行请求的操作
- 如果before触发器或语句本身失败，则mysql不执行after触发器
- 删除触发器
drop trigger tg
- insert触发器:
A:在insert中可以引用名为new的虚拟表，来访问插入的行
B:在before insert触发器中，new中的值可被改变
C:对于auto_increment列，new在insert执行之前包含0，在insert执行后包含新的自动生成的值
- delete触发器:
A:在delete触发器代码内，可以引用名为old的虚拟表来访问被删除的行
B:old中的值都是只读的
- update触发器:
A:在update触发器代码中，可以引用new和old虚拟表
B:before update中，new中的值可以被改变
C:old中的值是只读的
- mysql的触发器内不支持call语句
delimiter |
create table if not exists t1 (id int auto_increment, dealstr
varchar(1024), oldvalue int default null, newvalue int default null,
primary key(id))|
delete from t0|
create trigger tg0 before insert on t0 for each row
begin
set new.value = new.value + 1;
insert into t1(dealstr, newvalue) values("before insert", new.value);
end|
create trigger tg1 after insert on t0 for each row insert into t1(dealstr,
newvalue) values("after insert", new.value)|
insert into t0(name, value) values ("szn", 11)|
create trigger tg2 before update on t0 for each row
begin
set new.value = new.value + 2;
insert into t1(dealstr, oldvalue, newvalue) values("before update",
old.value, new.value);
end|
create trigger tg3 after update on t0 for each row insert into t1(dealstr,
oldvalue, newvalue) values("after update", old.value, new.value)|
update t0 set value = 20 where name = "szn"|
create trigger tg4 before delete on t0 for each row insert into t1(dealstr,
oldvalue) values("before delete", old.value)|
create trigger tg5 after delete on t0 for each row insert into t1(dealstr,
oldvalue) values("after delete", old.value)|
delete from t0 where name = "szn"|
drop trigger tg0|
drop trigger tg1|
drop trigger tg2|
drop trigger tg3|
drop trigger tg4|
drop trigger tg5|
select * from t1|
drop table t1|
delimiter ;
管理事务处理
- 事务处理可以维护数据库的完整性，他保证成批的mysql操作要么完全执行，要么完全不执行
- 事务开始
start transaction
- mysql使用rollback命令来撤销mysql语句，rollback只能在事务中使用。rollback只对insert、update、delete有效，对于create、drop无法进行撤销，对于select无意义
create table if not exists t0(id int auto_increment, name varchar(100),
value int, primary key(id)) engine = innodb;
start transaction;
insert into t0 (name, value) values("szn", 1);
rollback;
select * from t0;
-- 结果是空的(注释格式:-- 空格必须打)
drop table t0;
- 一般的mysql语句都是隐含提交的，但是事务处理中，提交必须用commit。当commit或rollback后，事务会自动关闭
- 使用保留点
savepoint sp
- 保留点在事务完成后自动释放，也可以使用
release savepoint sp 进行手动释放
- 更改默认的提交行为
set autocommit = 0 通过以上设置autocommit = 0，则用户将一直处于某个事务中，直到执行一条commit提交或rollback语句才会结束当前事务重新开始一个新的事务。
create table if not exists t0(id int auto_increment, name varchar(100),
value int, primary key(id)) engine = innodb;
start transaction;
insert into t0 (name, value) values("szn", 1);
insert into t0 (name, value) values("szn", 2);
commit;
select * from t0;
-- 返回两条新插入的行
drop table t0;
create table if not exists t0(id int auto_increment, name varchar(100),
value int, primary key(id)) engine = innodb;
start transaction;
insert into t0 (name, value) values("szn", 1);
savepoint sp;
insert into t0 (name, value) values("szn", 2);
rollback to sp;
select * from t0;
-- 只有第一次inser生效
drop table t0;
全球化与本地化
- 查看所支持的字符集列表以及每个字符集的描述和默认校对
show character set
- 查看所支持的校对
show collation (校对规定字符如何比较)
- 设置表的字符集
create table if not exists t0(id int auto_increment, name varchar(500), primary key(id)) engine = innodb default character set utf8
- 更改表的字符集
alter table t0 default character set gbk;
- 设置列的字符集
create table if not exists t0(id int auto_increment, name varchar(500) character set utf8, primary key(id))
- 确定使用的字符集和校对:
show variables like 'character%';
show variables like 'collation%';
安全管理
use mysql;
select user, host from user;
