MySQL函数大全(字符串函数，数学函数，日期函数，系统级函数，聚合函数)

目录

字符串函数

数学函数

日期函数

系统级函数

聚合函数

字符串函数

1.char_length(' '):计算字符串字符的长度

select char_length('abcd我');

运行结果如下：

2.length(' ')：计算字符串的长度。这里需要注意的是，一个汉字长度是3。

select length('abcd我');

运行结果如下：

3.concat(' ',' '...):字符串拼接

select concat('我','ni');

运行结果如下：

4.concat_WS('分隔符',' ',' '...):用指定的符号拼接

select concat_WS('-','ni','我')

运行结果如下：

5.group_concat(distinct FIELD_NAME order by FIELD_NAME[DESC]) :配合分组查询，进行行转列

举例说明：比如在一张成绩表中，查询学生学了哪些课程。

select student_id，course_id from score;

这句话会将所有学生所有学的课程列出来，导致会有很多行。

如果使用group_concat，按照student_id分组,将每个学生的课程行转列，变成一行来显示，就会看得一目了然。

6.insert(s1,x,len,s2):从字符串s1的x位置开始，被字符串s2取代len个字符。

select insert('我爱你',2,2,'哈哈哈');

运行结果如下：

7.lower(' '):转小写

select lower('A我A');

运行结果如下：

8.upper(''):转大写

select upper('a我a');

 运行结果如下：

9.left('',n):字符串从最左边开始的n个字符

select left('abcd',3);

运行结果如下：

10.right('',n):字符串从最右边开始的n个字符

select right('efgh',3);

运行结果如下：

11.mid('',n,len):从字符串的第n个位置开始，取len个字符

select mid('efgh',2,2);

运行结果如下：

12.lpad(s1,len,s2):左填充

select lpad('abc',5,'cd');

运行结果如下：

13.rpad(s1,len,s2):右填充

select rpad('abc',5,'cd');

运行结果如下：

14.ltrim(''):左去空

select ltrim('   abc');

运行结果如下：

15.rtrim(''):右去空

select rtrim('abc   ');

运行结果如下：

16.trim(''):两端去空

select trim('  abc   ');

运行结果如下：

17.trim(s1 from s):删除s两端的所有s1

select trim('ab' from 'bcdabefhab');

运行结果如下：

18.repeat('',n):让字符串重复n次

select repeat('abc',3);

 运行结果如下：

19.space(n):返回由n个空格组成的字符串

select concat('我爱你',space(4),'他爱她');

  运行结果如下：

20.replace('1','2','3'):将字符串1包含字符串2的部分由参数3替代

select replace('abcd','bc','-');

   运行结果如下：

21.strcmp(s1,s2):比大小，返回0,1,-1。若为-1，则s1<s2;若为1，则s1>s2;若为0，则s1=s2

select strcmp('ab','ad');

 运行结果如下：

select strcmp('afce','adcd');

运行结果如下：

select strcmp('ad','ad');

 运行结果如下：

22.locate('1','2'):判断2中是否有1，有则返回其第一次出现的位置

select locate('ab','bcdabefgab');

  运行结果如下：

23.reverse(''):反转字符串

select reverse('abc');

   运行结果如下：

24.elt(N,'',''...):返回第N个字符串。根据参数1指定的位置，提取以参数2为1起，之后的对应位置的值，若位置不存在返回null

select elt(2,'a','v','c','d');

    运行结果如下：

25.field('1','2','3',...):返回2,3之后的所有字符串中第一次出现与1匹配的位置,没有返回0。

select field('ab','cd','ab');

     运行结果如下：

26.find_in_set('1',',,,...'):返回1在后面很多字符串(以逗号分割的字符串)中能匹配上的位置，匹配不上，返回0

select find_in_set('b','abbcd,abc,b');

      运行结果如下：

27.make_set(x,'1','2'...):根据参数1转换成二进制后的结果，保留和集合元素数量位低位值，然后逆序匹配提取子集合，1就提取，0不提取

select make_set(25,'aa','bb','cc','dd','ee');

   运行结果如下：

因为25转成二进制为11001,将二进制转一下为10011，所以结果为aa,dd,ee。

28.substring_index('str','分隔符','截取位置')：截取字符串

从左边开始截取到第三个逗号

select substring_index('我,你,他,她,它',',',3);

    运行结果如下：

数学函数

1.abs(v NUMBER):绝对值

select abs(-1);

     运行结果如下：

 

2.floor(v DECIMAL):去掉小数（不四舍五入）

select floor(3.456);

 运行结果如下：

3.ceil(v DECIMAL):有小数就进1

select ceil(0.000001);

 运行结果如下：

4.truncate(v DECIMAL,n INT):截断(不四舍五入)

select truncate(3.35567,1);

  运行结果如下：

5.round(v DECIMAL)：返回离参数最近的整数，四舍五入

select round(3.56);

  运行结果如下：

6.round(v DECIMAL,n INT)：保留几位小数，四舍五入

select round(3.564,1);

  运行结果如下：

7.rand():返回0~1之间的随机小数

select rand();

   运行结果如下：

 

8.sign(v NUMBER):返回数字是否为正数还是负数。正数为1，负数为-1

select sign(3.56);

 运行结果如下：

9.pi()：返回圆周率的值(共7位)

select pi();

运行结果如下： 

10.pow(b NUMBER,p NUMBER):幂运算

select pow(2,3);

运行结果如下： 

11.sqrt(b NUMBER):开平方根

select sqrt(4);

运行结果如下： 

12.exp(n NUMBER):返回e的x乘方后的值

select exp(2);

运行结果如下： 

13.mod(n NUMBER,m NUMBER):n%m取余

select mod(3,5);

运行结果如下： 

14.log2(x):返回x相对于基数2的对数

select log2(16);

运行结果如下： 

15.log10(x):返回x相对于基数10的对数

select log10(10);

运行结果如下： 

16.radians(d NUMBER):角度转弧度

select radians(30);

运行结果如下： 

17.degree(r NUMBER)：弧度转角度

select degrees(0.5235987755982988);

运行结果如下： 

日期函数

1.now():返回当前日期时间

select now();

运行结果如下：  

2.curdate():返回当前日期

select curdate();

运行结果如下：  

3.curtime():返回当前时间

select curtime();

运行结果如下：  

4.unix_timestamp():获取到当前日期的时间的秒数

select unix_timestamp();

运行结果如下：  

5.unix_timestamp(['yyyy-MM-dd hh:mm:ss sss']):获取到指定日期的时间的秒数

select unix_timestamp('2020-2-13');

运行结果如下：  

select UTC_DATE();

运行结果如下：  

6.from_unixtime(v BIGINT):返回秒数对应的日期时间

select from_unixtime(1581523200);

运行结果如下：  

7.UTC_DATE():返回格式为"YYYY-MM-DD"的当前UTC日期值

select UTC_DATE();

运行结果如下：  

8.UTC_TIME():返回格式为"hh:mm:ss"的当前UTC时间值

select UTC_TIME();

运行结果如下：  

9.year('日期'):返回日期中的年份值，范围是1~12

select year('2020-10-3');

运行结果如下：  

10.month('日期')：返回日期中的月份值，范围是1~12

select month('2020-10-3');

运行结果如下：  

11.week('日期')：计算日期是本年的第几个星期，范围是0~53

select week('2020-10-3');

运行结果如下：  

12.weekofyear('日期')：计算日期是本年的第几个星期,1~53

select weekofyear('2020-10-3');

运行结果如下：  

13.day('日期')：返回日期中的天数

select day('2020-10-3');

运行结果如下：  

14.monthname('日期'):返回日期中的月份名称

select monthname('2020-10-3');

运行结果如下：  

15.dayname('日期'):返回日期是星期几

select dayname('2020-10-3');

运行结果如下：  

16.quarter('日期'):日期是第几季度

select quarter('2020-10-3');

运行结果如下：  

17.hour('时间')：时间的小时值

select hour(curtime());

运行结果如下：  

18.minute('时间')：时间的分钟值

select minute(curtime());

运行结果如下：  

19.second('时间')：时间的秒钟值

select second(curtime());

运行结果如下：  

20.extract(type from date):从日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND

select extract(year from curtime());

运行结果如下：  

21.time_to_sec('时间'):将时间转换为秒

select time_to_sec(curtime());

运行结果如下：  

22.sec_to_time(秒数)：将以秒为单位的时间转换为时分秒的格式

select sec_to_time(27524);

运行结果如下：  

23.to_days('日期'):计算日期d至0000年1月1日的天数

select to_days('0001-1-1');

运行结果如下：  

24.from_days(n):计算从0000年1月1日开始n天后的日期

select from_days(729669);

运行结果如下：  

25.dayofyear('日期'):本年的第几天

select dayofyear('2020-10-3');

运行结果如下：  

26.dayofmonth('日期'):计算日期是本月的第几天

select dayofmonth('2020-10-3');

运行结果如下：  

27.dayofweek('日期'):本周的第几天。1为星期日

select dayofweek('2020-10-3');

运行结果如下：  

28.weekday('日期')：日期是星期几。0为星期一

select weekday('2020-10-3');

运行结果如下：  

29.datediff(d1,d2):计算d1和d2间隔的天数

select datediff('2020-10-3','2020-10-20');

运行结果如下：  

30.adddate(date,'正负值' YEAR|QUARTER|YEAR_MONTH|MONTH|DAY|...)

select adddate('2020-10-3',interval 1 year);

运行结果如下：  

31.adddate('日期',n)：在日期后加n天后的日期

select adddate('2020-10-3',5);

 运行结果如下：  

32.subtime('时间',n);

select subtime('12:03:45',10);

 运行结果如下：   

33.date_format(now(),'%Y-%m-%d %H:%i:%s'):格式化日期时间

select date_format('2020-10-03','%Y/%m/%d');

 运行结果如下：   

34.time_format('时间','格式')：格式化时间

select time_format(curtime(),'%H:%i:%s');

 运行结果如下：   

35.计算自己的周岁

select floor(datediff(now(),'1995-10-20')/365);

 运行结果如下：   

36.计算本周第一天：select adddate(date,interval concat('-',dayofweek(date))+2 day);

select adddate(curdate(),interval concat('-',dayofweek(curdate()))+2 day);

 运行结果如下：   

37.指定日期所在季度的第一天

select concat_WS('-',year(now()),(quarter(now())-1)*3+1,'1');

 运行结果如下：   

系统级函数

1.if(expr,v1,v2):如果表达式expr成立，执行v1，否则执行v2。

select if(2>1,1,2);

 运行结果如下：    

2.ifnull(v1,v2)：如果v1不为空，则显示v1的值；否则显示v2的值

select ifnull(null,3);

 运行结果如下：    

select ifnull(2,3);

 运行结果如下：    

3.case when expr1 then v1 [when expr2 then v2 …][else vn] end:case表示函数开始，end表示函数结束。

如果表达式expr1成立，则返回v1的值；如果表达式expr2成立，则返回v2的值。依次类推，最后遇到else时，返回vn的值。

举例说明：按各科平均成绩从低到高和及格率的百分数从高到低顺序

select
course_id,avg(score) avg,sum(case when score>60 then 1 else 0 end)/count(1)*100 lv
from
score
group by
course_id
order by
avg,lv DESC;

运行结果如下：

4.case expr when e1 then v1 [when e2 then v2 …][else vn] end:case表示函数开始，end表示函数结束。

如果表达式expr取值为e1，则返回v1的值；如果表达式expr取值为e2，则返回v2的值，依次类推，最后遇到else，则返回vn的值

举例说明：学生成绩表中，按照学生ID，列出其语文数学英语成绩。

select
SC.student_id 学生ID,
max(case C.cname when '语文' then SC.score else 0 end) '语文',
max(case C.cname when '数学' then SC.score else 0 end) '数学',
max(case C.cname when '英语' then SC.score else 0 end) '英语'
from
score SC
left join
course C
on
SC.course_id=C.cid
group by SC.student_id;

运行结果如下：

5.database():查询当前所使用的数据库

select database();

运行结果如下： 

6.password(src_str)：加密

select password('abc');

 运行结果如下：

7.md5(src_str)：单向加密

select md5('abc');

运行结果如下：

8.encode(src_str,pass_str)：用参数2对参数1进行加密

select encode('abc','def');

运行结果如下：

9.decode(crypt_str,pass_str):使用参数2来为参数1解密

select decode(encode('abc','def'),'def');

 运行结果如下：

聚合函数

1.min(NUMBER_FIELD_NAME)：最小值

2.max(NUMBER_FIELD_NAME)：最大值

3.sum(NUMBER_FIELD_NAME)：求和

4.avg(NUMBER_FIELD_NAME)：求平均值

5.count(1/distinct FIELD_NAME)：计数