基础Sql语句之三

使用存储过程

• 使用存储过程的好处:简单、安全、高性能
• 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;

• 创建用户 create user szn identified by '123456'
• 重命名 rename user szn to slz
• 删除用户 drop user slz
• 查看用户权限 show grants for szn
• 设置用户权限关键字为grant，其反操作为revoke
• 新建的用户，未赋予任何权限也可以以管理员的身份访问test开头的数据库，这是mysql的隐含规则
• 权限分配 https://blog.csdn.net/sunyloveyun/article/details/51675399