MySQL学习笔记（四）：内置函数和自定义函数

一、常见内置函数

从文档列表可知，MySQL内置函数涵盖范围非常广：控制流函数、字符串函数、数值函数、时间日期函数、全文搜索函数映射函数、XML函数、位函数、加密和压缩函数、信息函数、空间分析函数、Json函数、企业加密函数等等。在此只列举开发中常用的函数。

聚合函数

这些函数与其它函数有些不同：它们一般作用在多条记录上，一般与GROUP BY等关键字组合使用。

AVG([DISTINCT] expr)：返回平均值

mysql> SELECT student_name, AVG(test_score)
    ->        FROM student
    ->        GROUP BY student_name;

COUNT(expr)：返回记录数

mysql> SELECT student.student_name,COUNT(*)
    ->        FROM student,course
    ->        WHERE student.student_id=course.student_id
    ->        GROUP BY student_name;
#
# COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, 
# whether or not they contain NULL values.`

COUNT(DISTINCT expr,[expr...])：Returns a count of the number of rows with different non-NULL expr values.

mysql> SELECT COUNT(DISTINCT results) FROM student;

MAX([DISTINCT] expr)：返回表达式中的最大值

mysql> SELECT student_name, MIN(test_score), MAX(test_score)
    ->        FROM student
    ->        GROUP BY student_name;

MIN([DISTINCT] expr) ：返回表达式的最小值

mysql> SELECT student_name, MIN(test_score), MAX(test_score)
    ->        FROM student
    ->        GROUP BY student_name;

SUM([DISTINCT] expr)
：返回表达式的和

字符串函数

CONCAT()
：返回连接字符串（如果其中任何一个参数为NULL，则返回NULL）

mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3'

INSERT(str,pos,len,newstr) ：将str的从pos位置开始的len个字符替换为newstr（如果其中任何一个参数为NULL，则返回NULL）

mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
        -> 'QuWhattic'
mysql> SELECT INSERT('Quadratic', -1, 4, 'What');
        -> 'Quadratic'
mysql> SELECT INSERT('Quadratic', 3, 100, 'What');
        -> 'QuWhat'

LOWER(str)和UPPER(str) ：将字符串str转换成小写/大写
LEFT(str,len)和RIGHT(str,len) ：分别返回str最左边/最右边的n个字符（如果其中任何一个参数为NULL，则返回NULL）
TRIM(str)和RTRIM(str)以及LTRIM(str)：去除字符串str左右空格/右空格/左空格
REPLACE(str,from_str,to_str)：将字符串str中所有出现的from_str字符串替换为to_str

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com'

SUBSTRING(str,pos,len)：返回字符串str中从pos起，len个字符长度的字符串

mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
        -> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
        -> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
        -> 'ki'

数值函数

ABS(x)：返回x的绝对值

mysql> SELECT ABS(2);
        -> 2
mysql> SELECT ABS(-32);
        -> 32

CEILING(x)或者CEIL(x)：返回大于x的最小整数
FLOOR(x) ：返回小于x的最大整数

mysql> SELECT CEILING(1.23);
        -> 2
mysql> SELECT CEILING(-1.23);
        -> -1
mysql> SELECT FLOOR(1.23), FLOOR(-1.23);
        -> 1, -2

MOD(N,M) 或者 N % M 或者 N MOD M：取模

mysql> SELECT MOD(234, 10);
        -> 4
mysql> SELECT 253 % 7;
        -> 1
mysql> SELECT MOD(29,9);
        -> 2
mysql> SELECT 29 MOD 9;
        -> 2

RAND()
用法1：返回[0 ~1.0)之间的随机数

//要得到i <= R < j的随机数： FLOOR(i + RAND() * (j−i))
//比如，要得到5~10之间的随机数：FLOOR(5 + RAND()*5)

用法2：SELECT * FROM tbl_name ORDER BY RAND();：retrieve rows in random order

ROUND(X)或者ROUND(X,D)：对X四舍五入，含有D位小数的值，D值默认为0

mysql> SELECT ROUND(-1.23);
        -> -1
mysql> SELECT ROUND(-1.58);
        -> -2
mysql> SELECT ROUND(1.58);
        -> 2
mysql> SELECT ROUND(1.298, 1);
        -> 1.3
mysql> SELECT ROUND(1.298, 0);
        -> 1
mysql> SELECT ROUND(23.298, -1);
        -> 20

TRUNCATE(X,D)：返回数字X被截断为D位小数的数值（与ROUND()类似，只是不四舍五入）

mysql> SELECT TRUNCATE(1.223,1);
        -> 1.2
mysql> SELECT TRUNCATE(1.999,1);
        -> 1.9
mysql> SELECT TRUNCATE(1.999,0);
        -> 1
mysql> SELECT TRUNCATE(-1.999,1);
        -> -1.9
mysql> SELECT TRUNCATE(122,-2);
       -> 100
mysql> SELECT TRUNCATE(10.28*100,0);
       -> 1028

时间日期函数

CURDATE()
：返回当前日期，格式为'YYYY-MM-DD'或者YYYYMMDD，取决于该函数的使用场景

mysql> SELECT CURDATE();
        -> '2008-06-13'
mysql> SELECT CURDATE() + 0;
        -> 20080613

CURTIME([fsp])：返回当前时间，格式为'HH:MM:SS'或者HHMMSS

mysql> SELECT CURTIME();
        -> '23:50:26'
mysql> SELECT CURTIME() + 0;
        -> 235026.000000

NOW([fsp])：返回当前日期+时间

mysql> SELECT NOW();
        -> '2007-12-15 23:50:26'
mysql> SELECT NOW() + 0;
        -> 20071215235026.000000

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)：返回当前时间/date的unix时间戳

mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1496132631 |
+------------------+
1 row in set
mysql> SELECT UNIX_TIMESTAMP('2017-05-30 16:25:19');
+---------------------------------------+
| UNIX_TIMESTAMP('2017-05-30 16:25:19') |
+---------------------------------------+
|                            1496132719 |
+---------------------------------------+
1 row in set

FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)：把unix时间戳转换格式

mysql> SELECT FROM_UNIXTIME(1447430881);
        -> '2015-11-13 10:08:01'
mysql> SELECT FROM_UNIXTIME(1447430881) + 0;
        -> 20151113100801
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
    ->                      '%Y %D %M %h:%i:%s %x');
        -> '2015 13th November 10:08:01 2015'

DATE_FORMAT(date,format)：将date时间格式化

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'

其他函数

database() ：当前数据库
version()：当前数据库版本
user() ：当前登录用户

mysql> select database(), version(), user();
+------------+------------+----------------+
| database() | version()  | user()         |
+------------+------------+----------------+
| cpgl       | 5.7.17-log | root@localhost |
+------------+------------+----------------+
1 row in set

二、自定义函数

参考：MySql------自定义函数详解

基本语法

创建

CREATE FUNCTION [db_name.]fn_name(func_parameter[,...])
RETURNS type
[characteristic...]
routine_body
#解释
db_name：数据库名，不指明则为当前数据库
type: 任何mysql支持的类型
routine_body: 函数体
characteristic:
LANGUAGE SQL | [NOT]DETERMINISTIC
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}
| COMMENT 'String'

注意：函数中一定要有return返回值语句

删除

DROP FUNCTION  [IF EXISTS]  fn_name;

更改

ALTER FUNCTION fn_name [characteristic...]

查看状态或定义语句

SHOW FUNCTION STATUS [LIKE 'pattern']
SHOW CREATE FUNCTION fn_name;

函数中的变量
定义变量
a.通过DECLARE可以定义一个局部变量，变量的作用范围BEGIN...END块中；
b.全局变量不用声明，可以直接@XXX使用。定义全局变量：set @var_name=value，如：

mysql> set @a=1;
Query OK, 0 rows affected

c.变量语句必须写在复合语句开头，并且在其他语句的前面;
d.一次性可以声明多个变量;
DECLARE var_name[,...] type [DEFAULT value]

为变量赋值
a.直接赋值：SET var_name = expr[,var_name=expr]...
b.通过查询语句赋值：SELECT...INTO...，如：

SELECT 表中某字段 INTO 变量名 FROM 表名 WHERE条件;

这种方式下，SELECT语句的查询结果只能有一个。

其他说明
运行包含DDL语句，允许提交或回滚
可以在函数中调用其它函数或者存储过程
与存储过程类似，可以一块看

实例

例子1

#创建表
CREATE TABLE `t_user_main` (  
  `f_userId` int(10) NOT NULL AUTO_INCREMENT COMMENT '用户id,作为主键',  
  `f_userName` varchar(5) DEFAULT NULL COMMENT '用户名',  
  `f_age` int(3) DEFAULT NULL COMMENT '年龄',  
  PRIMARY KEY (`f_userId`)  
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;  
#插入数据
INSERT INTO t_user_main (f_userName, f_age)   
VALUES('one',24),('two',25),('three',26),('four',27),('five',28),('six',29);  

#创建一个函数  
DELIMITER $$ -- 定界符  
-- 开始创建函数  
CREATE FUNCTION user_main_fn(v_id INT)  
RETURNS VARCHAR(50)  
BEGIN  
  -- 定义变量  
  DECLARE v_userName VARCHAR(50);  
  -- 给定义的变量赋值  
  SELECT f_userName INTO v_userName FROM t_user_main   
  WHERE f_userId = v_id;  
  -- 返回函数处理结果  
  RETURN v_userName;  
END $$ -- 函数创建定界符  
DELIMITER ;

例子2

最后编辑于：2017.12.07 23:12:21

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 159,117评论 4赞 362
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 67,328评论 1赞 293
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 108,839评论 0赞 243
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 44,007评论 0赞 206
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 52,384评论 3赞 287
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,629评论 1赞 219
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 31,880评论 2赞 313
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,593评论 0赞 198
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,313评论 1赞 243
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,575评论 2赞 246
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,066评论 1赞 260
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,392评论 2赞 253
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,052评论 3赞 236
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,082评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,844评论 0赞 195
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 35,662评论 2赞 274
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,575评论 2赞 270