（五）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)
