一.函数
1.常用函数
max() 获取最大值
min() 获取最小值
sum() 获取总数和
count() 获取总行数
avg() 获取平均数
select last_insert_id() 获取最后插入的数据的id
select uuid() 获得随机的字符串
select now() 获得当前时间 2017-12-13 16:46:09
select curdate() 获取当前日期 2017-12-13
select curtime() 获取当前时分秒 16:46:09
select max(id) from 表名 获取最大主键id
DATE(parame) 返回日期/时间表达式的日期部分 如:2017-10-12 19:15:54 则返回2017-10-12
2.IFNULL(parameter1,parameter2)函数
IFNULL函数:如果参数1是null,则返回参数2
算出每个员工的工资+奖金 如果奖金是null使用ifnull函数转成0再和工资相加
SELECT SUM(IFNULL(bonus,0) + salary) FROM `user` GROUP BY id
3.coalesce(value,value...)函数
这个函数和IFNULL函数很相似,但是,它里面可以写多个表达式, 返回参数中的第一个非空表达式(从左向右依次类推)
这个参数使用的场合为:假如某个字段默认是null,你想其返回的不是null,而是比如0或其他值,可以使用这个函数
SELECT
COALESCE(field_name,0) as value from table;
4.日期转字符串 DATE_FORMAT(date,format)
select date_format('2013-01-01 01:21:01','%Y-%m-%d %H:%i:%s');
返回年
SELECT DATE_FORMAT(o.create_time,'%Y') FROM lottery_order o
返回月
SELECT DATE_FORMAT(o.create_time,'%m') FROM lottery_order o
返回日
SELECT DATE_FORMAT(o.create_time,'%d') FROM lottery_order o
可以使用的格式有:
格式 | 描述 |
---|---|
%a | 缩写星期名 |
%b | 缩写月名 |
%c | 月,数值 |
%D | 带有英文前缀的月中的天 |
%d | 月的天,数值(00-31) |
%e | 月的天,数值(0-31) |
%f | 微秒 |
%H | 小时 (00-23) |
%h | 小时 (01-12) |
%I | 小时 (01-12) |
%i | 分钟,数值(00-59) |
%j | 年的天 (001-366) |
%k | 小时 (0-23) |
%l | 小时 (1-12) |
%M | 月名 |
%m | 月,数值(00-12) |
%p | AM 或 PM |
%r | 时间,12-小时(hh:mm:ss AM 或 PM) |
%S | 秒(00-59) |
%s | 秒(00-59) |
%T | 时间, 24-小时 (hh:mm:ss) |
%U | 周 (00-53) 星期日是一周的第一天 |
%u | 周 (00-53) 星期一是一周的第一天 |
%V | 周 (01-53) 星期日是一周的第一天,与 %X 使用 |
%v | 周 (01-53) 星期一是一周的第一天,与 %x 使用 |
%W | 星期名 |
%w | 周的天 (0=星期日, 6=星期六) |
%X | 年,其中的星期日是周的第一天,4 位,与 %V 使用 |
%x | 年,其中的星期一是周的第一天,4 位,与 %v 使用 |
%Y | 年,4 位 |
%y | 年,2 位 |
5.字符串转日期 STR_TO_DATE(str,format)
select STR_TO_DATE('2018-01-02 12:25:25','%Y-%m-%d %H:%i:%s')
6.时间转换为秒,和秒转换为时间,和时间转换为字符串
时间转化秒函数:time_to_sec
select time_to_sec('01:01:01')
3661
秒转化时间函数:sec_to_time
select sec_to_time('3661')
01:01:01
时间转换为字符串函数:time_format
select time_format('01:21:01','%H%i%s');
UNIX_TIMESTAMP(date)
SELECT UNIX_TIMESTAMP(NOW());
它会将参数值以'1970-01-01 00:00:00' GMT后的秒数的形式返回。
date 可以是一个 DATE字符串、一个 DATETIME字符串、一个 TIMESTAMP或一个当地时间的YYMMDD 或YYYMMDD格式的数字。
7.WEEKDAY(date)
返回日期的星期索引(0=星期一,1=星期二, ……6= 星期天)。
select weekday('2018-01-02');
1
8.WEEK(date[,mode])
返回表达式是一年的第几周
select week('2018-10-25');
42
模式 | 星期的第一天 | 范围 | 星期 1 是第一天 |
0 | Sunday | 0-53 | 一年中多一个星期天 |
1 | Monday | 0-53 | 一年多3天 |
2 | Sunday | 1-53 | 一年中多一个星期天 |
3 | Monday | 1-53 | 一年多3天 |
4 | Sunday | 0-53 | 一年多3天 |
5 | Monday | 0-53 | 一年中多一个星期一 |
6 | Sunday | 1-53 | 一年多3天 |
7 | Monday | 1-53 | 一年中多一个星期一 |
9.QUARTER(date) 返回日期的一年中的季度,范围为1到4
select quarter('2018-01-26');
1
10.dayofweek(date) ,dayofmonth(date) ,dayofyear(date)
返回表达式中是一个星期的第几天 星期日:1, 星期一:2 星期二:3 以此类推
select dayofweek('2018-01-30');
3
返回表达式是一个月的第几天
select dayofmonth('2018-01-30');
30
返回表达式是一年的第几天
select dayofyear('2018-10-22');
295
11.MONTH(date)
返回表达式的月份
SELECT MONTH('2018-03-05')
3
MONTH
函数接受一个DATE或DATETIME值的参数。 它返回1
到12
之间的整数,范围从1
到12
。
如果通过零日期,例如0000-00-00
,则MONTH
函数返回0
,如果日期为NULL
,则MONTH
函数返回NULL
。
12.日期加减运算
DATE_ADD(date,INTERVAL expr type) DATE_SUB(date,INTERVAL expr type)
date:参数是合法的日期表达式。
expr:(希望添加的时间间隔)是一个字符串;对于负值的时间间隔,它可以以一个 ‘-’开头。
例子:
select date_add('2013-05-16 01:01:01',interval 1 second);
2013-05-16 01:01:02
复合型type例子
对应复合型的type,需要使用引号对两个参数进行引用起来,中间用任何非数字字符作为间隔即可
date_add('2013-01-18', interval '1 2' YEAR_MONTH)
2014-03-18
type格式:
MICROSECOND | 间隔单位:毫秒 |
SECOND | 间隔单位:秒 |
MINUTE | 间隔单位:分钟 |
HOUR | 间隔单位:小时 |
DAY | 间隔单位:天 |
WEEK | 间隔单位:星期 |
MONTH | 间隔单位:月 |
QUARTER | 间隔单位:季度 |
YEAR | 间隔单位:年 |
SECOND_MICROSECOND | 复合型,间隔单位:秒、毫秒,expr可以用两个值来分别指定秒和毫秒 |
MINUTE_MICROSECOND | 复合型,间隔单位:分、毫秒 |
MINUTE_SECOND | 复合型,间隔单位:分、秒 |
HOUR_MICROSECOND | 复合型,间隔单位:小时、毫秒 |
HOUR_SECOND | 复合型,间隔单位:小时、秒 |
HOUR_MINUTE | 复合型,间隔单位:小时分 |
DAY_MICROSECOND | 复合型,间隔单位:天、毫秒 |
DAY_SECOND | 复合型,间隔单位:天、秒 |
DAY_MINUTE | 复合型,间隔单位:天、分 |
DAY_HOUR | 复合型,间隔单位:天、小时 |
YEAR_MONTH | 复合型,间隔单位:年、月 |
13..DATEDIFF(expr, expr2) 计算两个日期的相差天数 只有日期参与运算,只能计算天数
select datediff('2013-05-16 06:01:01', '2013-05-15 01:01:01');
结果: 1
注意:使用的时候前面的时间最好大于后面的时间否则差值可能为负数
例如
select datediff('2013-05-15 06:01:01', '2013-05-16 01:01:01');
结果: -1
14.TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)。
返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其结果的单位由interval 参数给出。该参数必须是以下值的其中一个:
SECOND。秒
MINUTE。分钟
HOUR。小时
DAY。天
WEEK。星期
MONTH。月
QUARTER。季度
YEAR。年
例子:
SELECT TIMESTAMPDIFF(DAY,'2018-12-12','2018-12-14')
结果: 2
TIMESTAMPADD(interval,int_expr,datetime_expr)
将整型表达式int_expr 添加到日期或日期时间表达式 datetime_expr中
。式中的interval和上面的取值是一样的
例子:
SELECT TIMESTAMPADD(MINUTE,8820,'2012-08-24 09:00:00')
结果: 2012-08-30 12:00:00
15.TIMEDIFF(expr1, expr2)
如果任一参数为
NULL
,TIMEDIFF
函数将返回NULL
。如果传递两个不同类型的参数,一个是DATETIME
,另一个是TIME
,TIMEDIFF
函数也返回NULL
SELECT TIMEDIFF('2018-12-15 10:25:30','2018-12-13 10:25:20')
结果: 48:00:10 返回的是相差的时间
16.DATEDIFF(expr1, expr2)
返回两个日期相减(expr1 − expr2 )相差的天数
SELECT DATEDIFF('2018-12-15 10:25:30','2018-12-13 10:25:20')
结果: 2 返回的是相差的天数