存储过程框架
DEMILITER $$ -- 重定义符
DROP PROCEDURE IF EXISTS store_procedure$$ -- 如果存在此名的存储过程，先删除
CREATE PROCEDURE store_procedure() -- 创建名为store_procedure的存储过程
BEGIN -- 开始
-- 内容
END$$ -- 结束
DEMILITER ; -- 恢复;为分隔符
在使用复合语句时，必须考虑和解决这样一个问题：复合语句块里的语句必须以分号（;）彼此隔开，但因为分号同时也是mysql程序默认使用的语句分隔符，所以在使用mysql程序定义存储程序时会发生冲突。解决这个问题的办法是使用DELIMITER命令把mysql程序的语句分隔符重定义为另一个字符或字符串。
注释：
两个连字符加一个空格的单行注释，如：-- 注释
类似C语言的多行注释，如：/*注释
*/
变量：
变量声明：
DECLARE 变量名；
示例：DECLARE my_integer INT;
变量声明同时初始化：
DECLARE 变量名 DEFAULT 变量初始化值；
示例：DECLARE my_integer INT DEFAULT 0;
变量赋值：
SET 变量名=值；
示例：SET my_integer=1;
常用的MySQL数据类型
| 数据类型 | 解释 | 相应值的示例 |
| INT, INTEGER | 32位整数。取值范围为-21亿到+21亿，如果是非符号数，值可以达到42亿，但这样做就不能包括负数 | 123，345 -2,000,000,000 |
| BIGINT | 64位整数。取值范围为-9万亿到+9万亿或者非负的0到18万亿 | 9,000,000,000,000,000,000 -9,000,000,000,000,000,000 |
| FLOAT | 32位浮点数。取值范围为1.7e38 to 1.7e38或者非负的0到3.4e38 | 0.00000000000002 17897.890790 -345.8908770 1.7e21 |
| DOUBLE | 64位浮点数。取值范围接近无限（1.7e308） | 1.765e203 -1.765e100 |
| DECIMAL（precision,scale） NUMERIC（precision,scale） | 定点数。存储情况取决于precision，能保存可能出现的数字范围。 NUMERIC通常用来保存重要的十进制数，例如现金 | 78979.00 -87.50 9.95 |
| DATE | 日期类型，没有详述时间 | '1999-12-31' |
| DATETIME | 日期和时间，时间精确到秒 | '1999-12-31 23:59:59' |
| CHAR(length) | 定长字符串。值会被空白填充至制定长度，最大长度为255字节 | 'hello world ' |
| VARCHAR(length) | 最大长度为64K的可变字符串 | 'Hello world' |
| BLOB, TEXT | 最大64K长度，BLOB用来保存2进制数据，TEXT用来保存文本数据 | 任何能想象的内容 |
| LONGBLOB, LONGTEXT | BLOB和TEXT的加长版本，存储能力达4GB | 任何能想象的内容，但比BLOB和TEXT能存放更大的长度 |
用户变量（全局变量）：
可以跨存储过程使用；
DECALRE @g_variable INT DEFAULT 0;
参数：
参数类型分为输入（IN，默认）、输出（OUT）、输入输出（INOUT）三种类型；
存储函数只能用输入（IN）模式，但可以有一个返回值，对于需要返回多个值的功能，只能采用存储过程；
示例：存储过程计算平方根
DEMILITER $$
DROP PROCEDURE sqrt_store_procedure
CREATE PROCEDURE sqrt_store_procedure(IN in_num INT, OUT out_num FLOAT)
BEGIN
SET out_num=SQRT(in_num);
END$$
DEMILITER ;
操作符：
数学操作符
| 操作符 | 描述 | 用例 |
| + | 加 | SET var1=2+2; à 4 |
| - | 减 | SET var2=3-2; à 1 |
| * | 乘 | SET var3=3*2; à 6 |
| / | 除 | SET var4=10/3; à 3.3333 |
| DIV | 整除 | SET var5=10 DIV à3; 3 |
| % | 模 | SET var6=10%3; à 1 |
比较操作符
| 操作符 | 描述 | 示例 | 示例结果 |
| > | 是否大于 | 1>2 | False |
| < | 是否小于 | 2<1 | False |
| <= | 是否小于等于 | 2<=2 | True |
| >= | 是否大于等于 | 3>=2 | True |
| BETWEEN | 是否位于两个值之间 | 5 BETWEEN 1 AND 10 | True |
| NOT BETWEEN | 是否不位于两个值之间 | 5 NOT BETWEEN 1 AND 10 | False |
| IN | 值位于列表中 | 5 IN (1,2,3,4) | False |
| NOT IN | 值不位于列表中 | 5 NOT IN (1,2,3,4) | True |
| = | 等于 | 2=3 | False |
| <>, != | 不等于 | 2<>3 | False |
| <=> | Null安全等于（如果两个值均为Null返回TRUE） | NULL<=>NULL | True |
| LIKE | 匹配简单模式 | "Guy Harrison" LIKE "Guy%" | True |
| REGEXP | 匹配扩展正则表达式 | "Guy Harrison" REGEXP "[Gg]reg" | False |
| IS NULL | 值为空 | 0 IS NULL | False |
| IS NOT NULL | 值不为空 | 0 IS NOT NULL | True |
逻辑操作符
AND操作符比较两个逻辑表达式，并且只在两个表达式都为真是才返回TRUE。
| AND | TRUE | FALSE | NULL |
| TRUE | TRUE | FALSE | NULL |
| FALSE | FALSE | FALSE | NULL |
| NULL | NULL | NULL | NULL |
OR操作符比对两个逻辑表达式，并且只要其中的一个表达式为真即返回TRUE。
| OR | TRUE | FALSE | NULL |
| TRUE | TRUE | TRUE | TRUE |
| FALSE | TRUE | FALSE | NULL |
| NULL | TRUE | NULL | NULL |
XOR操作符只有在两个值不完全为真时才返回TRUE。
| XOR | TRUE | FALSE | NULL |
| TRUE | FALSE | TRUE | NULL |
| FALSE | TRUE | FALSE | NULL |
| NULL | NULL | NULL | NULL |
位操作符
| 操作符 | 用途 |
| | | OR |
| & | AND |
| << | Shift bits to left |
| >> | Shift bits to right |
| ~ | NOT or invert bits |
条件执行：
IF、CASE；
示例：IF
DECLARE count INT DEFAULT 0;
IF （price > 100) THEN
count = 0;
ELSEIF (prict >= 30 AND prict <= 100) THEN
count=1;
ELSE
count=2;
END IF;
示例：CASE
DECLARE count INT DEFAULT 0;
CASE enum_type
WHEN ‘A’ THEN
count=1;
WHEN ‘B’ THEN
count=2;
ELSE -- 未满足’A’、’B’的情况下，执行ELSE流程
count=3;
END CASE;
在没有ELSE的情况下，如果enum_type没有’A’、’B’的情况，会抛出一个异常：ERROR 1339 (20000): Case not found for CASE statement
CASE语句中的WHEN表达式也可以是条件，如：
CASE price
WHEN (price > 200) THEN
count=1;
WHEN ((price > 100) AND (price <= 200)) THEN
count=2;
ELSE
count=3;
END CASE;
循环：
简单循环LOOP、END LOOP；
当条件为真时，继续执行的循环WHILE、END WHILE；
循环直至条件为真：REPEAT、UNTIL；
三种循环都可以用LEAVE子句来终止循环；
示例：LOOP
DECLARE count INT DEFAULT 0;
my_loop: LOOP
SET count=count+1;
IF (count=10)
LEAVE my_loop;
END IF;
SELECT CONCAT(count, “ is select value”);
END LOOP my_loop;
SELECT “I can count to 10”;
ITERATE语句用来重新从循环头部开始执行，而不执行任何在循环中遗留下来的语句。
DECLARE count INT DEFAULT 0;
my_loop: LOOP
SET count=count+1;
IF (count=10)
LEAVE my_loop;
ELSEIF (MOD(count,2)=0)
ITERATE my_loop;
END IF;
SELECT CONCAT(count, “ is select value”);
END LOOP my_loop;
SELECT “I can count to 10 odd num”;
REPEAT……UNTIL循环，REPEAT循环的循环体总是能确保至少运行一次。
DECLARE count INT DEFAULT 0;
my_loop: REPEAT
SET count=count+1;
IF (MOD(count,2)<>0)
SELECT CONCAT(count, “ is select value”);
END IF;
UNTIL count=10
END LOOP my_loop;
SELECT “I can count to 10 odd num”;
WHILE循环，WHILE循环只有在条件为真是才执行循环。
DECLARE count INT DEFAULT 0;
my_loop: WHILE （couunt<10) DO
SET count=count+1;
IF (MOD(count,2)<>0)
SELECT CONCAT(count, “ is select value”);
END IF;
END WHILE my_loop;
SELECT “I can count to 10 odd num”;
MYSql函数：
字符串函数
这些函数主要对字符串变量执行操作，比方说：你可以连接字符串，在字符串中查找字符，得到子串和其他常规操作。
数学函数
这些函数主要对数字进行操作，比方说：你可以进行乘方（平方），三角函数（sin，cos），随机数函数和对数函数等。
日期和时间函数
折现函数主要的操作对象是日期和时间，比方说：你可以得到当前时间，从一个日期上加上或减去一个时间间隔，找出两个日期间的差异，获取某个确定的时间点（比如：得到一天中某时间的小时数）。
其它函数
这些函数包括了所有不容易被分入上面类别中函数。他们包括类型转换函数，流程控制函数（比如：CASE），信息反馈函数（比如服务器版本）和加密函数。
经常被使用的MySQL函数
| 函数 | 描述 |
| ABS(number) | 返回提供数字的绝对值。比方说， ABS(-2.3)=2.3. |
| CEILING(number) | 返回下一个最大整数，比方说， CEILING(2.3)=3. |
| CONCAT(string1[,string2,string3,...]) | 返回所有提供字符串的连接形式的值 |
| CURDATE | 返回当前时间（不带时间） |
| DATE_ADD(date,INTERVAL amount_type) | 给提供的时间值加上一个时间间隔并返回一个新时间。正确的形式有SECOND, MINUTE, HOUR, DAY, MONTH和YEAR |
| DATE_SUB(date,INTERVAL interval_type) | 从提供的时间值上减去一个时间间隔并返回一个新的时间。正确的形式有SECOND, MINUTE, HOUR, DAY, MONTH和YEAR |
| FORMAT(number,decimals) | 返回一个指定精确度的数值，并给与以1000为单位的分割（通常使用“，”） |
| GREATEST(num1,num2[,num3, ... ]) | 返回所有提供参数中的最大数 |
| IF(test, value1,value2) | 测试一个逻辑条件，如果为真则返回value1，如果为假返回value2 |
| IFNULL(value,value2) | 返回第一个值，除非第一个值为空；这样的话返回第二个值 |
| INSERT(string,position,length,new) | 把一个字符串插入到另一个字符串中 |
| INSTR(string,substring) | 返回一个字符串中子串的位置 |
| ISNULL(expression) | 如果参数为空则返回1，否则返回0 |
| LEAST(num1,num2[,num3, ... ]) | 返回参数列表中的最小值 |
| LEFT(string,length) | 返回字符串最左边的部分 |
| LENGTH(string) | 返回字符串中的字节数。CHAR_LENGTH可以被用来返回字符数（这会在你使用多字节字符集是产生差异） |
| LOCATE(substring,string[,number]) | 返回字符串中子串的位置，可选的第三个参数为在父字符串中开始搜索的位置 |
| LOWER(string) | 返回给定字符串的小写形式 |
| LPAD(string,length,padding) | 返回字符串 str, 其左边由字符串padding 填补到length 字符长度，第三个参数为填充字符 |
| LTRIM(string) | 删除所有字符串中的前缀空格 |
| MOD(num1,num2) | 返回第一个数除于第二个数后的模（余数部分） |
| NOW | 返回当前日期和时间 |
| POWER(num1,num2) | 返回num1的num2次方 |
| RAND([seed]) | 返回一个随机数。seed可被用于随机数生成器的种子数 |
| REPEAT(string,number) | 返回一个重复number次string的字符串 |
| REPLACE(string,old,new) | 用new替换所有出现old的地方 |
| ROUND(number[,decimal]) | 舍去给定数值的指定精度的位数 |
| RPAD(string,length,padding) | 返回字符串 str, 其右边由字符串padding 填补到length 字符长度，第三个参数为填充字符 |
| RTRIM(string) | 删除字符串尾部的空格 |
| SIGN(number) | 如果number小于0则返回-1，如果大于0则返回1，如果为0则返回0 |
| SQRT(number) | 返回number的平方根 |
| STRCMP(string1,string2) | 如果两个值相同则返回0，若根据当前分类次序，第一个参数小于第二个，则返回 -1，其它情况返回 1 。 |
| SUBSTRING(string,position,length) | 从字符串指定位置开始返回length个字符 |
| UPPER(string) | 将指定字符串转换为大写 |
| VERSION | 返回MySQL服务器当前版本号的字符串 |
和数据库交互：
将一个SQL语句所返回的单个记录放入本地变量中；
创建一个“游标”来迭代SQL语句所返回的结果集；
执行一个SQL语句，将执行后的结果集返回给它的调用程序；
内嵌一个不返回结果集的SQL语句，如INSERT，UPDATA，DELETE等；
将一个SQL语句所返回的单个记录放入本地变量中：SELECT INTO
示例：
DECLARE num INT;
SELECT NUM(price)
INTO num
FROM tabprice;
SELECT CONCAT(“Total Price is”, num);
游标
游标的声明必须在我们所有的变量声明之后。在的变量之前定义游标会产生一个1337错误。游标总是和SELECT语句配合使用。
DECLARE cur CURSOR FOR SELECT * FROM customers;
创建一个“游标”来迭代SQL语句所返回的结果集：CURSOR
查询多条记录数据；
示例：CURSOR
DECLARE done DEFAULT -1;
DECLARE nprice DEFAULT 0;
DECLARE cur1 CURSOR FOR
SELECT price FROM tabprice;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; -- 如果没有记录，错误处理no data to fetch条件，几乎所有的游标都要用到
SET done=0;
OPEN cur1;
my_loop: LOOP
FETCH cur1 INTO nprice; -- 循环从游标中取值
IF （done=1)
LEAVE my_loop;
END IF;
END LOOP my_loop;
CLOSE cur1;
DEALLOCATE cur1; -- 释放游标
不使用no data to fetch的另一种解决方案@@FETCH_STATUS
@@fetch_status是MSSQL的一个全局变量
其值有以下三种，分别表示三种不同含义：【返回类型integer】
0 FETCH 语句成功
-1 FETCH 语句失败或此行不在结果集中
-2 被提取的行不存在
@@fetch_status值的改变是通过fetch next from实现的
“FETCH NEXT FROM Cursor”
DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName FROM Northwind.dbo.Employees
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
No data to fetch条件捕获在嵌套游标循环中使用比较复杂，有两种方案
1.在内循环结束时重置条件为0
DECLARE done DEFAULT -1;
DECLARE nprice DEFAULT 0;
DECLARE cur1 CURSOR FOR
SELECT price FROM tabprice;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; -- 如果没有记录，错误处理no data to fetch条件，几乎所有的游标都要用到
SET done=0;
OPEN cur1;
my_loop: LOOP
FETCH cur1 INTO nprice; -- 循环从游标中取值
DECLARE cur2 CURSOR FOR SELECT * FROM cumtomers;
OPEN cur2;
my_loop2: LOOP
FETCH cur2 INTO custom;
IF (done=1)
LEAVE my_loop2;
END IF;
END LOOP my_loop2;
CLOSE cur2;
DEALLOCATE cur2; -- 释放游标
SET done=0; -- 重置事件值
IF （done=1)
LEAVE my_loop;
END IF;
END LOOP my_loop;
CLOSE cur1;
DEALLOCATE cur1; -- 释放游标
2.内循环封闭为一个块
DECLARE done DEFAULT -1;
DECLARE nprice DEFAULT 0;
DECLARE cur1 CURSOR FOR
SELECT price FROM tabprice;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; -- 如果没有记录，错误处理no data to fetch条件，几乎所有的游标都要用到
SET done=0;
OPEN cur1;
my_loop: LOOP
FETCH cur1 INTO nprice; -- 循环从游标中取值
cur2begin: BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2=1;
DECLARE cur2 CURSOR FOR SELECT * FROM cumtomers;
OPEN cur2;
my_loop2: LOOP
FETCH cur2 INTO custom;
IF (done2=1)
LEAVE my_loop2;
END IF;
END LOOP my_loop2;
CLOSE cur2;
DEALLOCATE cur2; -- 释放游标
END cur2begin;
IF （done=1)
LEAVE my_loop;
END IF;
END LOOP my_loop;
CLOSE cur1;
DEALLOCATE cur1; -- 释放游标
在存储过程中调用另一个存储过程：
CALL 存储过程名；
存储函数：
函数的参数列表中模式只能为IN。OUT和INOUT参数不被允许。制定IN关键字是被允许也是缺省的；
函数必须返回一个值，它的类型被定义于函数的头部；
函数能被SQL语句所调用；
函数可能不返回任何结果集；
在程序的真正目的是比对值和需要返回值时或者你希望在SQL语句中创建用户自定义函数的时候更多的考虑使用存储函数，而不是存储过程；
框架：
DEMILITER $$ -- 重定义符
DROP FUNCTION IF EXISTS store_function$$ -- 如果存在此名的存储函数，先删除
CREATE FUNCTION store_function() RETURNS INT DETERMINITISTIC
-- 创建名为store_procedure的存储函数
--
| MySQL相较于存储过程，对于存储函数有更严格的规则。函数必须声明不修改SQL（使用NO SQL或者 READS SQL DATA子句）或者声明为DETERMINISTIC（如果服务器被允许开启二进制日志）。这个限制是为了防止当函数返回不确定值 时，数据库同步复制的不一致性，我们的样例例程使用了“deterministic”，这样我们就能确保在提供了相同的参数的情况下返回相同的值 |
BEGIN -- 开始
-- 内容
RETURN(返回值);
END$$ -- 结束
DEMILITER ; -- 恢复;为分隔符