mysql 赋值语句 三元操作_基础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的隐含规则