mysql函数 不低于5个_（五）MySQL函数

自定义函数

函数可以无参数，但必须有返回值

函数创建\删除\调用：

-- 创建函数

CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name type,...])

RETURNS {STRING|INTEGER|REAL}

runtime_body

-- 翻译

CREATE FUNCTION 函数名称(参数列表)

RETURNS 返回值类型

函数体

-- 删除函数

DROP FUNCTION function_name;

-- 调用函数

SELECT function_name(parameter_value,...);

函数体：

在函数体中,如果包含多条语句,我们需要把多条语句放到BEGIN...END语句块中

CREATE FUNCTION deleteById(targetId SMALLINT UNSIGNED)

RETURNS VARCHAR(20)

BEGIN

DELETE FROM table_name WHERE id = targetId;

RETURN (SELECT COUNT(*) FROM table_name);

END

修改默认的结束符语法：

DELIMITER //

CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED)

RETURNS VARCHAR(20)

BEGIN

DELETE FROM son WHERE id = uid;

RETURN (SELECT COUNT(id) FROM son);

END//

函数sql语法

声明函数变量

作用域为 BEGIN...END 代码块中

DECLARE var_name[,varname]...date_type [DEFAULT VALUE];

DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值];

-- example

DECLARE a, b SMALLINT UNSIGNED DEFAULT 10;

变量赋值

-- 赋已知的值

SET parameter_name = value[,parameter_name = value...]

-- example

SET a = x, b = y;

-- 赋未知值

SELECT INTO parameter_name

-- example

SELECT COUNT(id) FROM tdb_name INTO x;

SELECT group_concat(pid) INTO sTempPar FROM treenodes where pid<>id

声明用户变量(可以理解成全局变量)

作用域：作用域只为当前用户的客户端有效

SET @allParam = 100;

SELECT @allParam;

流程控制

MySQL中可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。

IF语句：

search_condition参数表示条件判断语句；

statement_list参数表示不同条件的执行语句

IF search_condition

THEN

statement_list

[ELSEIF search_condition THEN statement_list] ...

[ELSE statement_list]

END IF

Example：

IF age > 20

THEN

SET @count1=@count1+1;

ELSEIF age = 20

THEN

SET @count2=@count2+1;

ELSE

SET @count3=@count3+1;

END IF;

CASE语句：

CASE语句也用来进行条件判断，其可以实现比IF语句更复杂的条件判断

case_value参数表示条件判断的变量；

when_value参数表示变量的取值；

statement_list参数表示不同when_value值的执行语句

CASE case_value

WHEN when_value

THEN

statement_list

[WHEN when_value THEN statement_list] ...

[ELSE statement_list]

END CASE

example

CASE age

WHEN 20

THEN

SET @count1=@count1+1;

ELSE

SET @count2=@count2+1;

END CASE;

CASE语句2：

search_condition参数表示条件判断语句；

statement_list参数表示不同条件的执行语句；

CASE

WHEN search_condition THEN statement_list

[WHEN search_condition THEN statement_list] ...

[ELSE statement_list]

END CASE

example

CASE

WHEN age=20

THEN

SET @count1=@count1+1;

ELSE

SET @count2=@count2+1;

END CASE;

LOOP语句 LEAVE语句：

LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。

但是LOOP语句本身没有停止循环的语句，必须是遇到LEAVE语句等才能停止循环。

add_num: LOOP

SET @count=@count+1;

IF @count=100 THEN

LEAVE add_num;

END LOOP add_num;

ITERATE语句：

ITERATE语句也是用来跳出循环的语句。但是，ITERATE语句是跳出本次循环，然后直接进入下一次循环。

ITERATE语句只可以出现在LOOP、REPEAT、WHILE语句内。

add_num: LOOP

SET @count=@count+1;

IF @count=100 THEN

LEAVE add_num;

ELSE IF MOD(@count,3)=0

THEN

ITERATE add_num;

SELECT * FROM employee;

END LOOP add_num;

REPEAT语句：

REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句

[begin_label:] REPEAT

statement_list

UNTIL search_condition

END REPEAT [end_label]

example

REPEAT

SET

@count=@count+1;

UNTIL @count=100

END REPEAT ;

WHILE语句：

WHILE语句也是有条件控制的循环语句。但WHILE语句和REPEAT语句是不一样的。

WHILE语句是当满足条件时，执行循环内的语句。

[begin_label:] WHILE search_condition DO

statement_list

END WHILE [end_label]

example

WHILE @count<100 DO

SET @count=@count+1;

END WHILE ;

MySQL常用内置函数

字符串

1、字符串函数

-- ASCII('a')：返回ASCII码值，空串返回0

mysql> SELECT ASCII('a');

+------------+

| ASCII('a') |

+------------+

| 97 |

+------------+

1 row in set (0.01 sec)

-- CONV(n, from_base, to_base):对数字n进制转换,并转换为字串返回(任何参数为null时返回null,进制范围为2-36进制,当to_base是负数时n作为有符号数否则作无符号数,conv以64位点精度工作)

mysql> SELECT CONV(6, 10, 2);

+----------------+

| CONV(6, 10, 2) |

+----------------+

| 110 |

+----------------+

1 row in set (0.00 sec)

-- CONCAT(str1,str2,...):把参数连成一个长字符串并返回(任何参数是null时返回null)

mysql> SELECT CONCAT('aa', 'bb', 'cc');

+--------------------------+

| CONCAT('aa', 'bb', 'cc') |

+--------------------------+

| aabbcc |

+--------------------------+

1 row in set (0.00 sec)

-- length(str):字符串长度

mysql> SELECT LENGTH('MySQL');

+-----------------+

| LENGTH('MySQL') |

+-----------------+

| 5 |

+-----------------+

1 row in set (0.00 sec)

-- locate(substr,str):返回字符串substr在字符串str第一次出现的位置(str不包含substr时返回0)

-- locate(substr,str,pos):返回substr在字符串str的第pos个位置起第一次出现的位置(str不包含substr时返回0)

mysql> SELECT LOCATE('y', 'MySQL');

+----------------------+

| LOCATE('y', 'MySQL') |

+----------------------+

| 2 |

+----------------------+

mysql> SELECT LOCATE('y', 'MySQL', 3);

+-------------------------+

| LOCATE('y', 'MySQL', 3) |

+-------------------------+

| 0 |

+-------------------------+

-- lpad(str,len,padstr):用字符串padStr填充str左侧,直到长度达到len

-- rpad(str,len,padstr):用字符串padStr填充str右侧,直到长度达到len

mysql> SELECT RPAD('MySQL', 10, 'x');

+------------------------+

| RPAD('MySQL', 10, 'x') |

+------------------------+

| MySQLxxxxx |

+------------------------+

1 row in set (0.00 sec)

mysql> SELECT LPAD('MySQL', 10, 'x');

+------------------------+

| LPAD('MySQL', 10, 'x') |

+------------------------+

| xxxxxMySQL |

+------------------------+

1 row in set (0.00 sec)

-- left(str,len):返回字符串str的左端len个字符

-- right(str,len):返回字符串str的右端len个字符

-- substring(str,pos,len):返回字符串str的位置pos起len个字符mysql

-- substring(str,pos):返回字符串str的位置pos起的一个子串

mysql> SELECT LEFT('MySQL', 3);

+------------------+

| LEFT('MySQL', 3) |

+------------------+

| MyS |

+------------------+

1 row in set (0.00 sec)

mysql> SELECT RIGHT('MySQL', 3);

+-------------------+

| RIGHT('MySQL', 3) |

+-------------------+

| SQL |

+-------------------+

1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('MySQL', 2, 2);

+--------------------------+

| SUBSTRING('MySQL', 2, 2) |

+--------------------------+

| yS |

+--------------------------+

1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('MySQL', 2);

+-----------------------+

| SUBSTRING('MySQL', 2) |

+-----------------------+

| ySQL |

+-----------------------+

1 row in set (0.00 sec)

-- trim([[both | leading | trailing] [remstr] from] str):返回前缀或后缀remstr被删除了的字符串str(位置参数默认both,remstr默认值为空格)

mysql> SELECT TRIM(' bar ');

+------------------------------------+

| TRIM(' bar ') |

+------------------------------------+

| bar |

+------------------------------------+

1 row in set (0.00 sec)

mysql> SELECT TRIM(LEADING ' ' FROM ' bar ');

+-----------------------------------------------------+

| TRIM(LEADING ' ' FROM ' bar ') |

+-----------------------------------------------------+

| bar |

+-----------------------------------------------------+

1 row in set (0.00 sec)

mysql> SELECT TRIM(LEADING 'x' FROM 'xxxxxxxxxxfooxxxx');

+--------------------------------------------+

| TRIM(LEADING 'x' FROM 'xxxxxxxxxxfooxxxx') |

+--------------------------------------------+

| fooxxxx |

+--------------------------------------------+

1 row in set (0.00 sec)

mysql> SELECT TRIM(TRAILING 'x' FROM 'xxxxxxxxxxfooxxxx');

+---------------------------------------------+

| TRIM(TRAILING 'x' FROM 'xxxxxxxxxxfooxxxx') |

+---------------------------------------------+

| xxxxxxxxxxfoo |

+---------------------------------------------+

1 row in set (0.00 sec)

mysql> SELECT TRIM(BOTH 'x' FROM 'xxxxxxxxxxfooxxxx');

+-----------------------------------------+

| TRIM(BOTH 'x' FROM 'xxxxxxxxxxfooxxxx') |

+-----------------------------------------+

| foo |

+-----------------------------------------+

1 row in set (0.00 sec)

-- replace(str,source_str,target_str):用字符串to_str替换字符串str中的子串from_str并返回

mysql> SELECT REPLACE('MySQL', 'S', 'A');

+----------------------------+

| REPLACE('MySQL', 'S', 'A') |

+----------------------------+

| MyAQL |

+----------------------------+

1 row in set (0.00 sec)

-- reverse(str):颠倒字符串顺序并返回

mysql> SELECT REVERSE('MySQL');

+------------------+

| REVERSE('MySQL') |

+------------------+

| LQSyM |

+------------------+

1 row in set (0.00 sec)

-- find_in_set(str,strlist):返回str在字符串集strlist中的序号(任何参数是null则返回

-- null,如果str没找到返回0,参数1包含","时工作异常)

mysql> SELECT FIND_IN_SET('3', '1,2,3,4,5,6');

+---------------------------------+

| FIND_IN_SET('3', '1,2,3,4,5,6') |

+---------------------------------+

| 3 |

+---------------------------------+

1 row in set (0.00 sec)

2、数学函数

-- abs(n):绝对值

mysql> SELECT ABS(-12);

+----------+

| ABS(-12) |

+----------+

| 12 |

+----------+

1 row in set (0.00 sec)

-- FLOOR(1.23): 1

-- CEILING(1.23): 2

-- ROUND(n,d):返回n的四舍五入值,保留d位小数(d的默认值为0)

mysql> SELECT FLOOR(1.23);

+-------------+

| FLOOR(1.23) |

+-------------+

| 1 |

+-------------+

1 row in set (0.00 sec)

mysql> SELECT CEILING(1.23);

+---------------+

| CEILING(1.23) |

+---------------+

| 2 |

+---------------+

1 row in set (0.00 sec)

mysql> SELECT ROUND(1.23,1);

+---------------+

| ROUND(1.23,1) |

+---------------+

| 1.2 |

+---------------+

1 row in set (0.00 sec)

-- EXP(n):返回e的n次方

-- LOG(n):返回n的自然对数

-- LOG10(n):10为底的对数

-- pow(x,y):返回x的y次幂

-- sqrt(n):返回非负数n的平方根

-- pi():返回圆周率

-- rand(n):返回0-1之间随机浮点值

mysql> SELECT ROUND(RAND() * 100, 0);

+------------------------+

| ROUND(RAND() * 100, 0) |

+------------------------+

| 33 |

+------------------------+

1 row in set (0.01 sec)

mysql> SELECT ROUND(RAND() * 100, 0);

+------------------------+

| ROUND(RAND() * 100, 0) |

+------------------------+

| 64 |

+------------------------+

1 row in set (0.00 sec)

3、时间函数

-- dayofweek(date):返回日期date是星期几(1=星期天,2=星期一,……7=星期六,odbc标准)

-- dayofmonth(date):返回date是一月中的第几日(在1到31范围内)

-- dayofyear(date):返回date是一年中的第几日(在1到366范围内)

-- month(date):返回date中的月份数值

-- dayname(date):返回date是星期几(按英文名返回)

-- monthname(date):返回date是几月(按英文名返回)

-- week(date,first):返回date是一年的第几周(first默认值0,first取值1表示周一是周的开始,0从周日开始)

-- quarter(date):返回date是一年的第几个季度

-- year(date):返回date的年份(范围在1000到9999)

-- hour(time):返回time的小时数(范围是0到23)

-- minute(time):返回time的分钟数(范围是0到59)

-- second(time):返回time的秒数(范围是0到59)

-- now():sysdate():current_timestamp():当前时间

mysql> SELECT NOW();

+---------------------+

| NOW() |

+---------------------+

| 2019-03-18 16:48:23 |

+---------------------+

1 row in set (0.00 sec)

mysql> SELECT SYSDATE();

+---------------------+

| SYSDATE() |

+---------------------+

| 2019-03-18 16:48:38 |

+---------------------+

1 row in set (0.00 sec)

mysql> SELECT CURRENT_TIMESTAMP();

+---------------------+

| CURRENT_TIMESTAMP() |

+---------------------+

| 2019-03-18 16:48:59 |

+---------------------+

1 row in set (0.01 sec)

mysql> SELECT CURRENT_TIMESTAMP() + 0;

+-------------------------+

| CURRENT_TIMESTAMP() + 0 |

+-------------------------+

| 20190318164904 |

+-------------------------+

1 row in set (0.00 sec)