高级特性

变量

深入MySQL用户自定义变量

1、用户变量

以“@”开始，形式为“@变量名”

SET @test_name = 666;

用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效。

2、系统变量

全局变量

在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。
show global variables查看所有的全局变量

会话变量

会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。（也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。）
　　全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。
show session variables查看所有的会话变量。

3、局部变量

局部变量一般用在sql语句块中,如存储过程或函数内定义
作用范围在begin到end语句块之间，在该语句块里设置的变量。
DECLARE语句专门用于定义局部变量。set语句是设置不同类型的变量

-- 语法
DECLARE var_name [, var_name] ... type [DEFAULT value]
-- 实例
DECLARE v INT DEFAULT 1;
-- 赋值
-- 方式一
SET var_name=value;
-- 方式二 把某个select的查询结果赋给局部变量 
SELECT col_name[,...] INTO var_name[,...] table_expr [WHERE...];
-- 展示值
SELECT var_name

---

控制结构

1、IF

IF只作用于存储过程或函数定义内，即begin和end之间

IF 条件 THEN
....
END IF;

2、CASE

CASE num
 WHEN 1 THEN
   SELECT "大于0";
 WHEN -1 THEN
   SELECT "小于等于0";
END CASE;

3、循环，WHILE和 REPEAT

WHILE
while i<=100 DO
SET total = total +i;
SET i = i+1;
END WHILE;

REPEAT 
SET total = total +i;
SET i = i+1;
UNTIL i>100
END REPEAT;

---

存储过程

简单来说存储过程就是为以后的使用而保存的一条或多条MySQL语句的集合。类似批处理
创建存储过程

CREATE PROCEDURE pro_name;

执行存储过程

CALL pro_name;

带参数的存储过程

CREATE PROCEDURE pro_in_out(
IN uid INT ,
OUT name VARCHAR(20) )
BEGIN
......
END

---

触发器

触发器是MySQL为了响应表的更改时自动执行的一组sql语句
触发器只响应insert、update、delete
只有表才支持触发器。视图不支持（临时表也不支持）
触发器失败

如果befor触发器失败，mysql将取消操作。此外如果befor触发器或sql语句失败，之后的after也不会执行

创建触发器

CREATE TRIGGEN tri_name [befor|after] 
[insert|update|delete]
ON EACH ROW
BEGIN
...
END

删除触发器

DROP TRIGGEN tri_name

---

事务处理

MyISAM并不支持明确的事务处理。
事务处理是一种机制，用来管理必须成批执行的sql语句，来保证一组操作不会中途停止，它们作为整体执行，或完全不执行。如果sql语句没有发生错误，整组语句就提交给数据表。如果发生错误则进行回退以恢复数据库到某个已知且安全的状态。
关键字
事务：指一组sql语句
回退：指撤销指定sql语句的过程
提交：指将未存储的sql语句结果写入数据库表中
保留点：指事务处理中设置的临时占位符，你可以对它发布退回（与退回整个事务处理不同）

开始事务

START TRANSACTION

回滚事务

ROLLBACK

提交事务

COMMIT;

保留点

SAVEPOINT delete1;

ROLLBACK delete1;

更改默认提交行为
MySQL行为是自动提交的，换句话你执行的一句sql语句，实际都是针对表执行，且立即生效

SET aotucommit=0;
autocommit=0 即 为假是不自动提交，直到值为真。
这个设置针对每个连接而不是服务器。

---

游标

创建游标

CREATE PROCEGURE pro
BEGIN
DECLARE orde CURSOR FOR
SELECT * FROM XXX;
END

打开游标

OPEN orde;

关闭游标

CLOSE oder

游标的使用
一次只能获取一行数据，可利用循环获取全部数据
–>在利用循环获取数据时可利用sql的异常处理

-- 获取游标里面的一行数据 
-- FETCH cursor_user INTO username;
-- SELECT username;

-- 游标 
-- 用游标获取所有用户的数据，并逐行输出

CREATE PROCEDURE pro_cursor()
BEGIN
DECLARE error_code INT  DEFAULT 0;
-- 定义变量，用于保存游标里面的 数据
DECLARE username VARCHAR(20) ;

-- 定义游标
DECLARE cursor_user  CURSOR FOR  SELECT name FROM  user;

-- 定义条件处理器 
DECLARE CONTINUE  HANDLER  FOR  NOT found  SET  error_code = 1;

-- 游标使用方式 
-- 打开游标
OPEN cursor_user;

-- 获取游标里面的一行数据 
-- FETCH cursor_user INTO username;
-- SELECT username;
-- 利用while获取所有数据
outs:WHILE TRUE  DO
FETCH cursor_user INTO username;
IF error_code =0 THEN
SELECT username;
ELSE
 LEAVE outs;
END IF;
END WHILE outs;


-- 关闭游标
close cursor_user;

END
DROP PROCEDURE pro_cursor;
CALL pro_cursor;

函数

函数中无法使用sql语句，但好像可以使用insert，update。

CREATE FUNCTION fun_name(参数1，参数2)
RETURNS INT
BEGIN
...
return 0;
END