MySQL存储过程相关指令和函数集

　　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实现不支持，可能产生模糊的错误信息。
聚集函数效率很高，它们返回的结果一般比在客户端应用程序中计算要快得多。

posted @ 2020-04-28 23:59 晒太阳的兔子很忙阅读(334) 评论(0) 编辑收藏举报

刷新页面返回顶部

Bien94

MySQL存储过程相关指令和函数集

公告