1、定义变量
（1）DECLARE关键字用来定义变量，格式如下： DECLARE 变量名 数据类型（数据长度） （2）赋值 DECLARE定义的变量，有两种方式赋值，分别如下： set 变量名 = 值 --对一个变量进行赋值 select ** into ** --对多个变量进行赋值
2、定义游标
DECLARE var_cursor CURSOR FOR select * from table_name; -- 定义游标 并把值存在游标里
当sql需要对查询出来的多条数据进行循环处理时，这时候就需要用游标来处理。
3、if-then-else
create procedure if_test(in param int) begin declare var int; set var = param - 1; if param=0 then update testinfo set age=param where name='Anna'; else update testinfo set age=1 where name='Json'; end if; if var=0 then insert into testinfo(name) values('Jack'); else insert into testinfo(name) values('Tom'); end if; end
4、case
create procedure case_test(in param int) begin case param when 0 then insert into testinfo(name) values('Tom'); when 1 then insert into testinfo(name) values('Jack'); else insert into testinfo(name) values('Ponny'); end case; end
5、while-end while
create procedure test_while(in param int) begin while param<30 do insert into testinfo(name) values('Merry'); set param=param+1; end while; end
6、repeat-end repeat
create procedure repeat(in param int) begin repeat insert into testinfo(name) values('OMG'); set param=param+1; until param>90 end repeat; end
repeat在执行操作后检查结果，而while则是执行前进行检查。repeat相当于do...while，until表示满足后边的条件才继续循环。
7、loop-end loop
create procedure test_loop(in param int) begin test_loop_tmp:loop insert into testinfo(name) values('Loop'); set param=param+1; if param>100 then leave test_loop_tmp; end if; end loop; end
8、LABLES标号
标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。
9、ITERATE
通过引用复合语句的标号重新开始复合语句，类似于continue。
create procedure test_iterate(in param int) begin declare v int; set v=0; LOOP_LABLE:loop if v=9 then set v=v+1; ITERATE LOOP_LABLE; end if; insert into testinfo(name) values('Iterate'); set v=v+1; if v>=10 then leave LOOP_LABLE; end if; end loop; end
10、操作字符串类函数
CHARSET(tmp_str) // 返回字符串tmp_str字符集 CONCAT(tmp_str [,...]) // 拼接字符串 INSTR(tmp_str, sub_tmp_str) // 返回sub_tmp_str首次在tmp_str中出现的位置,不存在返回0 LCASE(tmp_str) // 转换成小写 UCASE(tmp_str) // 转换成大写 LEFT(tmp_str, length) // 从tmp_str中的左边起取length个字符 RIGHT(tmp_str, length) // 取tmp_str最后length个字符 LENGTH(tmp_str) // 获取tmp_str长度 LOAD_FILE(file_name) // 从文件读取内容 LOCATE(sub_tmp_str, tmp_str [, start_position]) // 同INSTR类似，返回sub_tmp_str在tmp_str出现的位置,但可指定开始位置进行查找 LPAD(tmp_str, length, pad) // 重复用pad加在tmp_str开头, 直到字串长度为length RPAD(tmp_str, length, pad) // 在tmp_str后用pad补充,直到长度为length LTRIM(tmp_str) // 去除前端空格
RTRIM(tmp_str) // 去除后端空格 REPEAT(tmp_str, count) // 对字符串或字符tmp_str重复count次 REPLACE(tmp_str, search_str, replace_str) // 在tmp_str中用replace_str替换search_str STRCMP(tmp_str1, tmp_str2) // 逐字符比较两字串大小 SUBSTRING(tmp_str, position [, length]) // 从tmp_str的position开始,取length个字符,注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1 TRIM([[BOTH|LEADING|TRAILING] [padding] FROM] tmp_str) // 去除指定位置的指定字符 SPACE(count)// 生成count个空格
11、数学类函数
ABS(tmp_number) // 取绝对值 BIN(tmp_decimal_number ) // 十进制转二进制 CEILING(tmp_number) // 向上取整 FLOOR(tmp_number) // 向下取整 CONV(tmp_number, from_base, to_base) // 进制转换 HEX(decimal_number ) // 转十六进制,注：HEX()中可传入字符串，则返回其ASC-11码，如HEX('DEF')返回4142143;也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19 FORMAT(tmp_number, decimal_places) // 保留小数位数 LEAST(tmp_number1, tmp_number2 [,...]) // 求最小值 MOD(numerator, denominator) // 求余 POWER(tmp_number, tmp_power) // 求指数 RAND([seed]) // 随机数 ROUND(tmp_number [, decimals]) // 四舍五入,decimals为小数位数 SIGN(tmp_number) // 对一个自然数进行判断,如果为零,返回0,如果为负数,统一返回-1,如果为正数,统一返回1
COS() // 返回一个角度的余弦
EXP() // 返回一个数的指数值
PI() // 返回圆周率
SIN() // 返回一个角度的正弦
SQRT() // 返回一个数的平方根
TAN() // 返回一个角度的正切
在主要DBMS的函数中，数值函数是最一致的、最统一的函数。
12、日前和时间类函数
ADDTIME(tmp_date, time_interval) // 将time_interval加到tmp_date CONVERT_TZ(tmp_datetime, fromTZ, toTZ) // 转换时区 CURRENT_DATE() // 当前日期 CURRENT_TIME() // 当前时间 NOW() // 当前时间 CURRENT_TIMESTAMP() // 当前时间戳 DATE(tmp_datetime) // 返回datetime的日期部分 DATE_ADD(tmp_date, INTERVAL d_value d_type) // 在tmp_date中加上日期或时间，具体用法请参考此链接 DATE_FORMAT(tmp_datetime, format_codes) // 使用format_codes格式显示tmp_datetime DATE_SUB(tmp_date, INTERVAL d_value d_type) // 在tmp_date上减去一个时间，具体用法请参考此链接 DATEDIFF(tmp_date1, tmp_date2) // 求两个日期差 DAY(tmp_date) // 返回日期的天 DAYNAME(tmp_date) // 英文星期 DAYOFWEEK(tmp_date) // 星期(1-7) ,1为星期天 DAYOFYEAR(tmp_date) // 一年中的第几天 EXTRACT(interval_name FROM tmp_date) // 从tmp_date中提取日期的指定部分 MAKEDATE(tmp_year, tmp_day) // 给出年及年中的第几天,生成日期串 MAKETIME(tmp_hour, tmp_minute, tmp_second) // 生成时间串 MONTHNAME(tmp_date) // 英文月份名 SEC_TO_TIME(tmp_seconds) // 秒数转成时间 STR_TO_DATE(tmp_string, tmp_format) // 字串转成时间,以tmp_format格式显示 TIMEDIFF(tmp_datetime1, tmp_datetime2) // 两个时间差 TIME_TO_SEC(tmp_time) // 时间转秒数 WEEK(date_time [, start_of_week]) // 第几周 YEAR(tmp_datetime) // 年份 DAYOFMONTH(tmp_datetime) // 月的第几天 HOUR(tmp_datetime) // 小时 LAST_DAY(tmp_date) // tmp_date的月的最后日期 MICROSECOND(tmp_datetime) // 微秒 MONTH(tmp_datetime) // 月 MINUTE(tmp_datetime) // 分钟
13、聚集函数
聚集函数：是值对某些行运行的函数，计算并返回一个值。
AVG() // 返回某列的平均值，只用于单个列，忽略列值为NULL的行 COUNT() // 返回某列的行数，若指定列名，则会忽略指定列的值为空的行；若是星号*，则不会忽略。 MAX() // 返回某列的最大值，忽略列值为NULL的行，在用于文本数据时，该函数返回按该列排序后的最后一行。 MIN() // 返回某列的最小值，忽略列值为NULL的行，在用于文本数据时，该函数返回该列排序后的最前面一行。 SUM() // 返回某列值之和，忽略列值为NULL的行。
- 使用聚集函数对所有行执行计算时，指定ALL参数或不指定参数（因为ALL是默认行为），如：AVG(ALL column_name)或AVG(column_name)
- 使用聚集函数时只包含不同的值，需要指定DISTINCT参数，如：AVG(DISTINCT column_name)
- DISTINCT不能用于COUNT(*)，若指定了列名，则DISTINCT可以用于COUNT()。
- DISTINCT必须使用列名，不能用于计算或表达式。
- DISTINCT用于MIN()和MAX()函数中没有什么实际价值。
- SELECT语句可以根据需要包含多个聚集函数。
- 当指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名，虽然这样做也算合法，但是许多SQL实现不支持，可能产生模糊的错误信息。
- 聚集函数效率很高，它们返回的结果一般比在客户端应用程序中计算要快得多。