mysql函数（字符串函数、日期函数、数学函数、系统级函数、聚合函数）

Ⅰ、字符串函数
1、char_length(str) 计算字符串字符个数。
mysql> select length(‘text’);
　　-> 4
mysql> select octet_length(‘text’);
　　-> 4

2、length(str) 返回值为字符串str的长度，单位为字节。(汉字3个字节，字母一个)

3、concat(s1,s2,…) (+不可以拼接了)返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL。
mysql> select concat(‘my’, ‘s’, ‘ql’);
　　-> ‘mysql’
mysql> select concat(‘my’, null, ‘ql’);
　　-> null
mysql> select concat(14.3);
　　-> ‘14.3’
　　
4、concat_ws(x,s1,s2,…) 返回多个字符串拼接之后的字符串，每个字符串之间有一个x。
group_concat(distinct FIFLD_NAME ordr by FIELD_NAME[DESC]) 配合分组查询进行行转列

5、insert(s1,x,len,s2) 返回字符串s1，其子字符串起始于位置x，被字符串s2取代len个字符。#插入，删除，替换
mysql> select instr(‘foobarbar’, ‘bar’);
　　-> 4
mysql> select instr(‘xbar’, ‘foobar’);
　　-> 0

6、lower(str) 将str中的字母全部转换成小写。

7、upper(str) 将字符串中的字母全部转换成大写。

8、left(s,n)、right(s,n)、mid(s,n,len) left返回字符串s从最左边开始的n个字符，right返回字符串s从最右边开始的n个字符，mid返回字符串s从n开始len长度的字符串。
mysql> select left(‘foobarbar’, 5);
　　-> ‘fooba’

9、lpad(s1,len,s2) 前者返回s1，其左边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符；
mysql> select lpad(‘hi’,4,’??’);
　　-> ‘??hi’

10、rpad(s1,len,s2) 前者返回s1，其右边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符。
mysql> select rpad(‘hi’,5,’?’);
　　-> ‘hi???’
、
11、ltrim(s)、rtrim(s) 前者返回字符串s，其左边所有空格被删除；后者返回字符串s，其右边所有空格被删除。
mysql> select ltrim(’ barbar’);
　　-> ‘barbar’
mysql> select rtrim('barbar ');
　　-> ‘barbar’
　　
12、rtrim(s) 返回字符串s删除了两边空格之后的字符串。
mysql> select rtrim('barbar ‘);
　　-> ‘barbar’
　　
13、trim(s1 FROM s) 删除字符串s两端所有子字符串s1，未指定s1的情况下则默认删除空格。
mysql> select trim(’ bar ');
　　-> ‘bar’
mysql> select trim(leading ‘x’ from ‘xxxbarxxx’);
　　-> ‘barxxx’
mysql> select trim(both ‘x’ from ‘xxxbarxxx’);
　　-> ‘bar’
mysql> select trim(trailing ‘xyz’ from ‘barxxyz’);
　　-> ‘barx’

14、repeat(s,n) 返回一个由重复字符串s组成的字符串，字符串s的数目等于n。
mysql> select repeat(‘mysql’, 3);
　　-> ‘mysqlmysqlmysql’

15、space(n) 返回一个由n个空格组成的字符串。
mysql> select space(6);
　　-> ’ ’

16、replace(s,s1,s2) 返回一个字符串，用字符串s2替代字符串s中所有的字符串s1。
mysql> select replace(‘www.mysql.com’, ‘w’, ‘ww’);
　　-> ‘wwwwww.mysql.com’

17、strcmp(s1,s2) 若s1和s2中所有的字符串都相同，则返回0；根据当前分类次序，第一个参数小于第二个则返回-1，其他情况返回1。

18、substring(s,n,len)、MID(s,n,len) 两个函数作用相同，从字符串s中返回一个第n个字符开始、长度为len的字符串。
mysql> select substring(‘quadratically’,5);
　　-> ‘ratically’
mysql> select substring(‘foobarbar’ from 4);
　　-> ‘barbar’

19、locate(str1,str)、position(str1 IN str)、INSTR(str,str1) 三个函数作用相同，返回子字符串str1在字符串str中的开始位置（从第几个字符开始）。
mysql> select locate(‘bar’, ‘foobarbar’);
　　-> 4
mysql> select locate(‘xbar’, ‘foobar’);
　　-> 0
mysql> select locate(‘bar’, ‘foobarbar’,5);
　　-> 7
20、reverse(s) 将字符串s反转。

21、elt(N,str1,str2,str3,str4,…) 返回第N个字符串。若位置不存在返回NULL
mysql> select elt(1, ‘ej’, ‘heja’, ‘hej’, ‘foo’);
　　-> ‘ej’
mysql> select elt(4, ‘ej’, ‘heja’, ‘hej’, ‘foo’);
　　-> ‘foo’

22、field(s,s1,s2,…) 返回第一个与字符串s匹配的字符串的位置。不存在返回0
mysql> select field(‘ej’, ‘hej’, ‘ej’, ‘heja’, ‘hej’,
‘foo’);
　　-> 2
mysql> select field(‘fo’, ‘hej’, ‘ej’, ‘heja’, ‘hej’,
‘foo’);
　　-> 0

23、find_in_set(s1,s2) 返回在字符串s2中与s1匹配的字符串的位置。s2是以逗号分隔的字符串模拟的集合。不存在返回0
mysql> select find_in_set(‘b’,‘a,b,c,d’);
　　-> 2

24、make_set(x,s1,s2,…) #x转化为二进制后，保留和set集合元素数量位低位值，按x的二进制数从s1，s2…，sn中选取字符串
mysql> select make_set(1,‘a’,‘b’,‘c’);
　　-> ‘a’
mysql> select make_set(1 | 4,‘hello’,‘nice’,‘world’);
　　-> ‘hello,world’
mysql> select make_set(0,‘a’,‘b’,‘c’);
　　-> ‘’

Ⅱ、数学函数
1、abs(X) 返回X的绝对值
mysql> select abs(2);
　　-> 2
mysql> select abs(-32);
　　-> 32

2、floor(X) 返回不大于X的最大整数
mysql> select floor(1.23);
　　-> 1
mysql> select floor(-1.23);
　　-> -2

3、ceil(X)、ceiling(X)返回不小于X的最小整数
mysql> select ceiling(1.23);
　　-> 2
mysql> select ceiling(-1.23);
　　-> -1

4、truncate(X,D)返回数值X保留到小数点后D位的值，截断时不进行四舍五入。

5、round(X)返回离X最近的整数，截断时要进行四舍五入
mysql> select round(-1.23);
　　-> -1
mysql> select round(-1.58);
　　-> -2
mysql> select round(1.58);
　　-> 2

6、round(X,D)保留X小数点后D位的值，截断时要进行四舍五入
mysql> select round(1.298, 1);
　　-> 1.3
mysql> select round(1.298, 0);
　　-> 1

7、rand()返回0~1的随机数
mysql> select rand();
　　-> 0.5925
mysql> select rand(20);
　　-> 0.1811
mysql> select rand(20);
　　-> 0.1811
mysql> select rand();
　　-> 0.2079
mysql> select rand();
　　-> 0.7888

8、sign(X)返回X的符号(负数，零或正)对应-1，0或1
mysql> select sign(-32);
　　-> -1
mysql> select sign(0);
　　-> 0
mysql> select sign(234);
　　-> 1

9、PI()返回圆周率的值。默认的显示小数位数是7位。

10、pow(x,y)、power(x,y)返回x的y次乘方的值。
mysql> select pow(2,2);
　　-> 4.000000
mysql> select pow(2,-2);
　　-> 0.250000

11、sqrt(x)返回非负数的x的二次方根
mysql> select sqrt(4);
　　-> 2.000000
mysql> select sqrt(20);
　　-> 4.472136

12、exp(x)返回e的x乘方后的值
mysql> select exp(2);
　　-> 7.389056
mysql> select exp(-2);
　　-> 0.135335

13、mod(N,M)返回N除以M以后的余数
mysql> select mod(234, 10);
　　-> 4
mysql> select 234 % 10;
　　-> 4
mysql> select mod(29,9);
　　-> 2

14、log(x)=ln(x)返回x的自然对数，x相对于基数e的对数。
mysql> select log(2);
　　-> 0.693147
mysql> select log(-2);
　　-> null

15、log10(x)返回x的基数为10的对数。
mysql> select log10(2);
　　-> 0.301030
mysql> select log10(100);
　　-> 2.000000
mysql> select log10(-100);
　　-> null

16、radians(x)返回x由角度转化为弧度的值。
mysql> select radians(90);
　　-> 1.570796

17、degrees(x)返回x由弧度转化为角度的值。
mysql> select degrees(pi());
　　-> 180.000000

18、sin(x)、asin(x)前者返回x的正弦，其中x为给定的弧度值；后者返回x的反正弦值，x为正弦。
mysql> select sin(pi());
　　-> 0.000000
mysql> select asin(0.2);
　　-> 0.201358
mysql> select asin(‘foo’);
　　-> 0.000000

19、cos(x)、acos(x)前者返回x的余弦，其中x为给定的弧度值；后者返回x的反余弦值，x为余弦。
mysql> select cos(pi());
　　-> -1.000000
mysql> select acos(1);
　　-> 0.000000
mysql> select acos(1.0001);
　　-> null
mysql> select acos(0);
　　-> 1.570796

20、tan(x)、atan(x)前者返回x的正切，其中x为给定的弧度值；后者返回x的反正切值，x为正切。
mysql> select tan(pi()+1);
　　-> 1.557408
mysql> select atan(2);
　　-> 1.107149
mysql> select atan(-2);
　　-> -1.107149
atan2(x,y)
　返回2个变量x和y的反正切(类似y/x的反正切,符号决定象限)
mysql> select atan(-2,2);
　　-> -0.785398
mysql> select atan(pi(),0);
　　-> 1.570796

21、cot(x)返回给定弧度值x的余切。
mysql> select cot(12);
　　-> -1.57267341
mysql> select cot(0);
　　-> null

Ⅲ、日期函数
1、curdate()、CURRENT_DATE() 返回当前日期，格式：yyyy-MM-dd。

2、curtime()、CURRENT_TIME() 返回当前时间，格式：HH:mm:ss。

3、now()、CURRENT_TIMESTAMP()、LOCALTIME()、SYSDATE()、LOCALTIMESTAMP() 返回当前日期和时间，格式：yyyy-MM-dd HH:mm:ss。

4、unix_timestamp() 返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数。

5、unix_timestamp(‘yyyy-MM-dd’) 返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数。

6、from_unixtime(date) 和UNIX_TIMESTAMP互为反函数，把UNIX时间戳转换为普通格式的时间。

7、month(d) 返回日期d中的月份值，范围是1~12。

8、dayname(d) 返回日期d是星期几，如：Monday、Tuesday等。

9、dayofweek(d) 返回日期d是星期几，如：1表示星期日，2表示星期一等。
mysql> select dayofweek(‘1998-02-03’);
　　-> 3

12、weekofyear(d) 计算日期d是本年的第几个星期，范围是1~53。

13、dayofyear(d) 计算日期d是本年的第几天。
mysql> select dayofyear(‘1998-02-03’);
　　-> 34

14、dayofmonth(d) 计算日期d是本月的第几天。
mysql> select dayofmonth(‘1998-02-03’);
　　-> 3

15、year(d) 返回日期d中的年份值。
mysql> select year(‘1998-02-03’);
　　-> 1998
16、quarter(d) 返回日期d是第几季度，范围是1~4。
mysql> select quarter(‘98-04-01’);
　　-> 2

17、hour(t) 返回时间t中的小时值。
mysql> select hour(‘10:05:03’);
　　-> 10

18、minute(t) 返回时间t中的分钟值。
mysql> select minute(‘98-02-03 10:05:03’);
　　-> 5

19、second(t) 返回时间t中的秒钟值。
mysql> select second(‘10:05:03’);
　　-> 3

20、DATEDIFF(d1,d2)计算日期d1与d2之间相隔的天数。

21、**ADDDATE(d,n) 计算起始日期d加上n天的日期。

22、**ADDDATE(d,INTERVAL± year|quarter|month|day|hour|year_month|)

adddate(‘2020-11-01’,interval ‘1:5’ year_month)计算起始日期d加上一个时

间段后的日期。

例：求本周第一天的日期：

select adddate(now(),interval concat(’-’,dayofweek(now()))+2 Day);

例:求指定日期所在本季度的第一天日期

select

concat_ws(’-’,year(now()),(quarter(now())-1)*3+1,‘1’);

23、date_add(d,INTERVAL expr type) 同ADDDATE(d,INTERVAL expr type)

24、subdate(d,n) 计算起始日期d减去n天的日期。

25、subdate(d,INTERVAL expr type) 计算起始日期d减去一个时间段后的日期。

26、addtime(t,n) 计算起始时间t加上n秒的时间。

27、subtime(t,n) 计算起始时间t减去n秒的时间。

28、date_format(d,’%Y-%m-%d %H:%i:%s’) 按照表达式 f 的要求显示日期d。

Ⅳ、系统级函数

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

2、ifnull(fieldV,defaultV)如果fieldV不为空，则显示fieldV的值；否则显示defaultV的值

3、case when expr1 then v1 [when expr2 then v2 …][else vn] end
case表示函数开始，end表示函数结束。如果表达式expr1成立，则返回v1的值；如果表达式expr2成立，则返回v2的值。依次类推，最后遇到else时，返回vn的值。

4、case @a when e1 then v1 [when e2 then v2 …][else vn] end
case表示函数开始，end表示函数结束。如果表达式expr取值为e1，则返回v1的值；如果表达式expr取值为e2，则返回v2的值，依次类推，最后遇到else，则返回vn的值

5、database();

6、select @@identity

7、password(src_str)

8、md5(concat(pass_str,src_str,pass_str));

9、encode(src_str,pass_str)

10、decode(crypt_str,pass_str) 使用字符串pass_str来为crypt_str解密

Ⅴ、聚合函数

min(NUMBER_FIELD_NAME)

max(NUMBER_FIELD_NAME)

sum(NUMBER_FIELD_NAME)

avg(NUMBER_FIELD_NAME)

count(1/distinct FIELD_NAME)