变量
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