MySQL中的常用函数

一、字符串处理函数

1、REPLACE()字符串替换

语法：

REPLACE(str,old_str,new_str);

含义：

将str中的old_str替换为new_str字符串。

注意：当搜索要替换的文本时，MySQL使用区分大小写匹配来执行要替换的字符串搜索。

示例：

将"hello world!"中hello字符串替换为hi。

SELECT REPLACE('hello world!','hello','hi');

运行结果：hi world!

2、INSERT(STR1，INDEX,LEN,STR2)

语法：

insert(str1,index,len,str2)

含义：

用str2从str1的index位置替换str1的len个元素

示例：

SELECT INSERT('hello world',7,5,'tom');

运行结果：hello tom

3、SUBSTRING()从字符串中提取子字符串

语法：

SUBSTRING(string,position);

SUBSTRING(string FROM position);

SUBSTRING(string,position,length);

SUBSTRING(string FROM position FOR length);

含义：

从特定位置开始的字符串返回一个给定长度的子字符串。

包含几个参数：

1>string参数是要提取子字符串的字符串。

2>position参数是一个整数，用于指定子串的起始字符，position可以是正或负整数。

如果position为正，则SUBSTRING函数从字符串的开始处提取子字符串。

3>length是一个正整数，用于指定子字符串长度。

示例：

SELECT SUBSTRING('hello mysql', 6);

运行结果：mysql

SELECT SUBSTRING('hello mysql' FROM 6);

运行结果：mysql

SELECT SUBSTRING('hello world!',1,5);

运行结果：hello

SELECT SUBSTRING('hello world!' FROM 1 FOR 5);

运行结果：hello

SELECT SUBSTRING('hello world!' FROM -6 FOR 5);

运行结果：world

4、SUBSTRING_INDEX(str,"/",-1)

语法：

SUBSTRING_INDEX(str,delim,count)；

含义：

在定界符delim及count出现前，从字符串str返回自字符串。若count为正值,则返回最终定界符(从左边开始)  ，若为-1则是从后往前截取；

示例：

SELECT substring_index('hello/world!', '/', 1);

运行结果：hello

SELECT substring_index('hello/world!', '/', -1);

运行结果：world!

5、substr()函数

语法：

substr(string str,num start,num length);

含义：

返回从字符串str中指定位置start开始截取指定长度length的字符串，start从1开始；

示例：

SELECT substr('hello world!',1,5);

运行结果：hello

6、LOCATE(substr,str) 字符串匹配

语法：

1）LOCATE(str1,str) 

2）LOCATE(str1,str,pos) 

含义：

1）函数返回字符串str1在str中第一次出现的位置。如果str1在str中不存在，返回0。

2）函数返回字符串str1在str中的第pos位置后第一次出现的位置。如果str1在str中不存在，返回0。

这个函数大小写不敏感。 

示例：

SELECT LOCATE('hel', 'hello world');

运行结果：1

SELECT LOCATE('helx', 'hello world');

运行结果：0

SELECT LOCATE('hel', 'hello world',1);

运行结果：1

SELECT LOCATE('hel', 'hello world',2);

运行结果：0

7、POSITION(str1 IN str2)

语法：

position(str1 IN str2)

含义：

返回子串str1在字符串str2中的位置

示例：

SELECT POSITION('abc' IN 'testabc124');

运行结果：5

8、FIND_IN_SET(str,strlist)函数

语法：

FIND_IN_SET(str,strlist)

含义：

str是要查询的字符串；

strlist是字段集合列，参数以”,”分隔，如 (1,2,3,4)；

查询字段strlist中包含str的结果，返回结果为null或记录；

Find_IN_SET是精确匹配，字段值以英文”,”分隔；

示例：

SELECT FIND_IN_SET('1','1,2,3,4');

运行结果：1

SELECT FIND_IN_SET('12','1,2,3,4');

运行结果：0

9、INSTR(str1,str2)

语法：

instr(str1,str2)

含义：

返回子串str2在字符串str1中的位置

示例：

SELECT INSTR('testabc124','abc');

运行结果：5

10、field(str,str1,str2,str3…)

语法：

field(str,str1,str2,str3…)

含义：

与find_in_set类似，但str2由一个类似列表的字符串变成了多个字符串，返回str在str1,str2,str3…中的位置。

示例：

SELECT FIELD('ab','hel','lo','ab','cd');

运行结果：3

11、concat(str1,str2,str3…)合并字符串

语法：

concat(str1,str2,str3…)

含义：

将多个字符串合并成一个字符串，如果传入的值中有null，那么最终结果是null；

如果想要在多个字符串合并结果中将每个字符串都分隔一下，可以使用concat_ws(分隔符，str1,str2,str3…)，如果传入的分隔符为null，那么最终结果是null；

示例：

SELECT CONCAT('hello',' ','world');

运行结果：hello world

SELECT CONCAT_WS(' ','hello','world');

运行结果：hello world

12、strcmp(str1,str2)比较字符串大小

语法：

strcmp(str1,str2)

含义：

比较两个字符串的大小。左边大于右边时返回1，左边等于右边时返回0，，左小于于右时返回-1。

示例：

SELECT STRCMP('a','b');

运行结果：-1

SELECT STRCMP('d','b');

运行结果：1

SELECT STRCMP('b','b');

运行结果：0

13、length(str)获取字符串字节数

语法：

length(str)

含义：

获取字符串字节长度（返回字节数，要注意字符集）

示例：

SELECT LENGTH('hello');

运行结果：5

SELECT LENGTH('你好');

运行结果：6

14、char_length(str)获取字符串字符数

语法：

char_length(str)

含义：

获取字符串长度

示例：

SELECT CHAR_LENGTH('hello');

运行结果：5

SELECT CHAR_LENGTH('你好');

运行结果：2

15、upper(x)，ucase(x)，lower(x)，lcase(x) 字母转换大小写

语法：

upper(x)

ucase(x)

lower(x)

lcase(x)

含义：

upper(x)，ucase(x)用于将字母转成大写，x可以是单个字母也可以是字符串；

lower(x)，lcase(x)用于将字母转成小写，x可以是单个字母也可以是字符串；

示例：

SELECT UPPER('hello');

SELECT UCASE('Hello');

运行结果：HELLO

SELECT LOWER('HELLO');

SELECT LCASE('Hello');

运行结果：hello

16、elt(index,str1,str2,str3…)

语法：

elt(index,str1,str2,str3…)

含义：

返回指定index位置的字符串

示例：

SELECT ELT(2,'hel','lo','ab','cd');

运行结果：2

17、left(str,n)

语法：

left(str,n)

含义：

截取str左边n个字符

示例：

SELECT LEFT('hello',2);

运行结果：he

18、right(str,n)

语法：

right(str,n)

含义：

截取str右边n个字符

示例：

SELECT RIGHT('hello',2);

运行结果：lo

19、ltrim(str)，rtrim(str)，trim()字符串去空

语法：

1）ltrim(str)

2）rtrim(str)

3）trim()

含义：

1）去除字符串str左边的空格

2）去除字符串str右边的空格

3）去除字符串str两边的空格

示例：

SELECT LTRIM('  hello');

SELECT RTRIM('hello  ');

SELECT TRIM('  hello  ');

运行结果：hello

运行结果：hello

运行结果：hello

二、聚合函数

1、avg(字段)函数

语法：

avg(str) 

含义：

返回指定字段的数据的平均值，avg() 函数忽略列值为NULL的行；

示例：

SELECT AVG(age)

FROM(

    select 10 as age

    UNION

    select 20 as age

    UNION

    select 30 as age

) t

运行结果：20.0000

2、count(字段)函数

语法：

count(str)

含义：

返回指定字段的数据行数（记录的数量）

示例：

SELECT count(*)

FROM(

    select 10 as age

    UNION

    select 20 as age

    UNION

    select 30 as age

) t

运行结果：3

3、sum(字段)函数

语法：

sum(str)

含义：

返回指定字段的数据之和，sum()函数忽略列值为null的行。

示例：

SELECT sum(age)

FROM(

    select 10 as age

    UNION

    select 20 as age

    UNION

    select 30 as age

) t

运行结果：60

4、max(字段)函数

语法：

max(str)

含义：

返回指定字段的数据的最大值，max() 函数忽略列值为null的行，如果指定字段的数据类型为字符串类型，先按字符串比较，然后返回最大值。

示例：

SELECT max(age)

FROM(

    select 10 as age

    UNION

    select 20 as age

    UNION

    select 30 as age

) t

运行结果：30

5、min(字段)函数

语法：

min(str)

含义：

返回指定字段的数据的最小值，min()函数忽略列值为null的行，如果指定字段的数据类型为字符串类型，先按字符串比较，然后返回最小值。

示例：

SELECT min(age)

FROM(

    select 10 as age

    UNION

    select 20 as age

    UNION

    select 30 as age

) t

运行结果：10

三、处理数值的函数

1、abs(x) 绝对值

语法：

abs(x)

含义：

返回x的绝对值

示例：

SELECT ABS(-10);

运行结果：10

2、ceil(x)向上取整

语法：

ceil(x)

含义：

返回x的向上取整的整数

示例：

SELECT CEIL(1.4);

运行结果：2

SELECT CEIL(-1.4);

运行结果：-1

3、floor(x)向下取整

语法：

floor(x)

含义：

返回x的向下取整的整数

示例：

SELECT FLOOR(1.4);

运行结果：1

SELECT FLOOR(-1.4);

运行结果：-2

4、mod(x)取模

语法：

mod(x)

含义：

返回x mod y的结果

示例：

SELECT mod(10,2);

运行结果：0

SELECT mod(9,2);

运行结果：1

5、rand()随机数

语法：

rand()

含义：

返回0-1内的随机数

示例：

SELECT RAND();

运行结果：0.7218787462654326

6、round(x,y)四舍五入

语法：

round(x,y)

含义：

返回数值x带有y为小数结果的数值（四舍五入）

示例：

SELECT ROUND(2.155,2);

运行结果：2.16

SELECT ROUND(2.150,2);

运行结果：2.15

7、truncate(x,y)数值截取

语法：

truncate(x,y)

含义：

返回数值x截取y位小数的结果（不四舍五入）

示例：

SELECT TRUNCATE(2.155,2);

运行结果：2.15

SELECT TRUNCATE(2.150,2);

运行结果：2.15

四、处理时间日期的函数

1、curdate()，current_date()

语法：

curdate()

current_date()

含义：

返回当前日期

示例：

SELECT CURDATE();

SELECT CURRENT_DATE();

运行结果：2019-05-29

2、curtime()，current_time()

语法：

curtime()

current_time()

含义：

返回当前时间

示例：

SELECT CURTIME();

SELECT CURRENT_TIME();

运行结果：20:17:25

3、now()

语法：

now()

含义：

返回当前日期时间

示例：

SELECT NOW();

运行结果：2019-05-29 20:18:46

4、month(date)，monthname(date)

语法：

month(date)

monthname(date)

含义：

从日期中选择出月份数

示例：

SELECT MONTH(CURDATE());

运行结果：5

SELECT MONTHNAME(CURDATE());

运行结果：May

5、week(date)

语法：

week(date)

含义：

从日期中选择出周数

示例：

SELECT WEEK(CURDATE());

运行结果：21

6、year(date)

语法：

year(date)

含义：

从日期中选择出年份

示例：

SELECT YEAR(CURDATE());

运行结果：2019

7、hour(date)

语法：

hour(date)

含义：

从日期中选择出小时数

示例：

SELECT HOUR(CURTIME());

运行结果：20

8、minute(time)

语法：

minute(time)

含义：

从日期中选择出分钟数

示例：

SELECT MINUTE(CURTIME());

运行结果：25

9、weekday(date)，dayname(date)

语法：

weekday(date)

dayname(date)

含义：

从日期中选择出今天是周几

示例：

SELECT WEEKDAY(CURDATE());

运行结果：2

SELECT DAYNAME(CURDATE());

运行结果：Wednesday

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

推荐阅读更多精彩内容

  • 2018-06-14
    在C语言中,五种基本数据类型存储空间长度的排列顺序是: A)char B)char=int<=float C)ch...
    夏天再来阅读 3,320评论 0赞 2
  • (004)mysql中的常用函数
    概述 mysql提供了很多内建函数，其中常用的函数有： 字符串函数 日期函数 数值函数 我一般建议，能在后台语言（...
    林湾村龙猫阅读 508评论 0赞 3
  • MySQL函数讲解(MySQL函数大全)
    转自 https://www.jb51.net/article/42906.htmMySQL函数大全和函数讲解，管...
    缘起一面阅读 746评论 0赞 13
  • MySQL 字符串函数
    ASCII(str) 返回字符串 str 中最左边字符的 ASCII 代码值。如果该字符串为空字符串，则返回0。如...
    JavaEdge阅读 460评论 0赞 1
  • 安全知识小课堂
    小朋友，安全知识你知道多少呢？你会保护自己吗？本周，结合孩子们在活动区的切实需要我们的警察家长作为志愿者来给孩子们...
    郝老师高实幼阅读 1,646评论 0赞 2