阅读数:90 评论数:0

一、数字函数

1、abs(x) 返回x的绝对值。

2、avg(expression)  返回一个表达式的平均值，expressioni是一个字段

如： select avg(age) from students;

3、ceil(x)、ceiling(x)  向上取整。

4、floor(x)  向下取整。

5、exp(x)  $$e^x$$

6、greatest(expr1,expr2,expr3,...)  返回列表中最大的值

7、least(expr1,expr2,expr3,...)  返回列表中最小的值

8、ln(x)  $$ln_x$$

9、log(x)  $$log_x$$

10、max(expression) 返回最大值

11、min(expression)  返回最小值

12、pow(x,y)、power(x,y)  $$x^y$$

13、rand()  返回0到1的随机数

14、round(x)  四舍五入

15、sign(x)  返回x的符号（负数-1，0是0，正数是1）

16、sqrt(x)  返回x的平方根

17、sum(expression)  返回指定字段的总和

18、truncate(x,y) 返回数值x保留小数点后的y位

二、字符串函数

1、ascii(str)  返回字符串的第一个字符的ASCII码

2、length(str)、char_length(str)、character_length(x)  返回字符串的字符数

3、concat(s1,s2,...sn)  将多个字符串合并成一个字符串

concat_ws()和concat()一样，但可以指定分隔符

SELECT concat_ws(',',ID,NAME,SCORE) stuInfo from t where id=4
-- 输入结果：
-- 1,小明,93

group_concat(s1,s2,...sn) 与group by一起用

4、find_in_set(s1,s2)  

s1是要查询的字符串，s2是目标字段，注意s2是以逗号分隔的。

SELECT FIND_IN_SET('b', 'a,b,c,d');
// 结果：2
// 因为 b 在strlist集合中2的位置, a是位置1
 
select FIND_IN_SET('1', '1');
// 结果：1 
// 这时候的strlist集合有点特殊，只有一个字符串
 
select FIND_IN_SET('2', '1，2'); 
// 结果：2
 
select FIND_IN_SET('6', '1'); 
// 结果：0 strlist中不存在str，所以返回0。

（1）与in的区别

例子1
SELECT * FROM USER WHERE ID IN (1,2,3)
例子2
String str= "1,2,3";
SELECT * FROM USER WHERE ID IN (str)
例子3
String str= "1,2,3";
SELECT * FROM  USER WHERE find_in_set(id,str)

例2会失败，因为in后面不能拼接变量，简而言之，find_in_set后可用变量，而in只能用常量。in比较是否相等 ，可以利用索引，find_in_set比较是否包含，是全表扫描。

5、format(x,n)  将数字x进行格式化"#,###.##"，将x保留到小数点后n位，最后一位四舍五入。

6、insert(x1,x,len,s2)  字符串s2替换s1的x位置开始长度为len的字符串

SELECT INSERT("google.com", 1, 6, "runnob");
---- 返回runoob.com

7、locate(s1,s)  从字符串s中获取s1的开始位置

SELECT LOCATE('st','myteststring');
---- 返回5

8、lcase(x)、lower(s)  将字符串s的所有字母变成小写字母

9、ucase、upper(s)  将字符串s的所有字母变成大写字母

10、trim(s)  去掉字符串s开始和结尾处的空格

11、ltrim(s)  去掉字符串s开始处的空格

12、rtrim(x)  去年字符串s结尾处的空格

13、substr(s,start,length)、substring(s,start,length)  从字符串s的start位置截取长度为length的子字符串

SELECT SUBSTR/SUBSTRING("RUNOOB", 2, 3);
---- 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符，返回UNO

14、position(s1 in s)  从字符串s中获取s1开始的位置

（1）locate、position和instr

三者差不多，只是参数的位置不同，同时locate多一个起始位置的参数。

15、repeat(s,n)  将字符串s重复n次

SELECT REPEAT('runoob',3);
---- 返回runoobrunoobrunoob

16、reverse(s)  将字符串s的顺序反过来

SELECT REVERSE('abc');
---- 返回cba

17、strcmp(s1,s2)  比较字符串，如果相等返回0，如果s1>s2返回1，如果s1<s2，返回-1

三、日期函数

1、curdate()、current_date()  返回当前日期，格式：YYYY-MM-DD

SELECT CURDATE();
SELECT CURRENT_DATE();
---- 返回2019-02-19

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

SELECT CURRENT_TIME();
---- 返回11:40:45

3、current_timestamp()  返回当前日期和时间，格式：YYYY-MM-DD HH:mm:ss

SELECT CURRENT_TIMESTAMP();
---- 返回2019-02-19 11:41:32

4、adddate(d,n)  计算起始日期d加上n天的日期

SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);
---- 返回2017-06-25

5、addtime(t,n) 时间t加上n秒的时间

SELECT ADDTIME('2011-11-11 11:11:11', 5);
---- 返回2011-11-11 11:11:16

6、date()  从日期或日期时间表达式中提取日期值

SELECT DATE("2017-06-15 11:11:16");
---- 返回2017-06-15

7、day(d)  返回日期值d的日期部分

SELECT DAY("2017-06-15"); 
---- 返回15

8、datediff(d1,d2) 计算日期d1-d2之间相隔的天数

SELECT DATEDIFF('2001-01-01','2001-02-02'); 
---- 返回-32

9、date_format 

SELECT DATE_FORMAT('2011.11.11 11:11:11','%Y-%m-%d %r');
---- 返回2011-11-11 11:11:11 AM

10、dayname(d)  返回日期d是星期几

SELECT DAYNAME('2011-11-11 11:11:11');
---- 返回Friday

11、dayofmonth(d)  日期d是本月的第几天

SELECT DAYOFMONTH('2011-11-11 11:11:11');
---- 返回11

12、dayofweek(d)  日期d是星期几，1是星期日，2是星期一，依此类推。

13、dayofyear(d)  日期d是本所的第几天

14、extract(type from d)  从日期d中获取指定的值，type指定返回的值。

type可取值为：
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

15、unix_timestamp()得到时间戳（10位）

SELECT UNIX_TIMESTAMP('2019-2-19');
---- 返回1550505600

16、from_unixtime() 时间戳转日期

SELECT FROM_UNIXTIME(1550505600);
---- 返回2019-02-19 00:00:00
SELECT FROM_UNIXTIME(1550505600, '%Y-%m-%d');
---- 返回2019-02-19

17、now() 获取当前具体的日期和时间，格式：YYYY-MM-DD HH:mm:ss

2019-11-13 16:38:20

18、date_add('某个日期时间',interval 1 时间种类名)

select date_add(now(), interval 1 day); -- 加1天
select date_add(now(), interval 1 hour); -- 加1小时
select date_add(now(), interval 1 minute); -- 加1分钟
select date_add(now(), interval 1 second); -- 加1秒
select date_add(now(), interval 1 microsecond); -- 加1毫秒
select date_add(now(), interval 1 week); -- 加1周
select date_add(now(), interval 1 month); -- 加1月
select date_add(now(), interval 1 quarter); -- 加1季
select date_add(now(), interval 1 year); -- 加1年

mysql的adddate()、addtime()函数可以用date_add()来替代。

19、date_sub

用法与date_add类似，mysql的subdate、subtime函数可以用date_sub来替代。

20、timediff(time1,time2)

select timediff('2019-06-03 12:30:00', '2019-06-03 12:29:30')    -- 00:00:30
等价于
select timediff('12:30:00', '12:29:30')    -- 00:00:30

21、timestampdiff

TIMESTAMPDIFF(YEAR,DATE_ADD(NOW(), INTERVAL - 800 DAY),NOW()) AS 相差年;
TIMESTAMPDIFF(MONTH,DATE_ADD(NOW(), INTERVAL - 800 DAY),NOW()) AS 相差月;
 TIMESTAMPDIFF(WEEK,DATE_ADD(NOW(), INTERVAL - 800 DAY),NOW()) AS 相差周;
TIMESTAMPDIFF(DAY,DATE_ADD(NOW(), INTERVAL - 800 DAY),NOW()) AS 相差日;

22、str_to_date，返回对应的日期或日期类型

SELECT STR_TO_DATE('2022-04-04 22:50:17','%Y-%m-%d');
SELECT STR_TO_DATE('2022-04-04 22:50:17','%Y-%m-%d %H');

23、其它

四、其它函数

1、if(expr,v1,v2)  如果表达式expr成立，返回结果v1，否则返回v2

SELECT IF(1>0,'yes','no');
---- 返回yes

2、conv(x,a,b) 把x由a进制转为b进制

SELECT CONV(13,10,2);
---- 返回1101

3、ifnull(expr1,expr2)

v如果expr1不为null，则返回expr1，否则返回expr2