mysql日期函数 简书_MySQL：常见函数

1. 概念

类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名

2. 优点

隐藏了实现细节

提高了代码的重用性

3. 调用

SELECT

函数名 (实参列表)

FROM

表名 ;

4. 分类

单行函数：如concat，length，ifnull等

分组函数(统计函数，聚合函数)，做统计使用

5. 实例

字符函数

length：获取参数值的字节个数

-- 4

SELECT

LENGTH('dudu') ;

-- 10 utf-8编码，一个汉字长度为3

SELECT

LENGTH('费渡dudu') ;

concat：拼接字符串

SELECT

CONCAT(`last_name`, '_', `first_name`) AS '姓名'

FROM

`employees` ;

upper和lower

-- DUDU

SELECT

UPPER('dudu') ;

-- dudu

SELECT

LOWER('Dudu') ;

案例：将姓变成大写，名变成小写，然后拼接

SELECT

CONCAT(

LOWER(`last_name`),

'_',

UPPER(`first_name`)

) AS '姓名'

FROM

`employees` ;

substr(substring)：截取字符

-- 喜欢骆大爷！(4代表起始位置)

SELECT

SUBSTR('费嘟嘟喜欢骆大爷！', 4) ;

-- 嘟嘟喜欢(2代表起始位置，4代表截取的长度)

SELECT

SUBSTR('费嘟嘟喜欢骆大爷！', 2, 4 ) ;

案例：将姓名中首字符大写，其他字符小写，然后拼接

SELECT

CONCAT(

UPPER(SUBSTR(`last_name`, 1, 1)),

'_',

LOWER(SUBSTR(`last_name`, 2))

) AS '姓名'

FROM

`employees` ;

instr：返回子串在字符串中第一次出现的索引，不存在则返回0

-- 1

SELECT

INSTR(

'费嘟嘟喜欢骆大爷',

'费嘟嘟'

) AS out_put ;

trim：去掉首尾的空格

SELECT

LENGTH(TRIM(' du du ')) AS out_put ;

-- 可以指定要去掉的字符

SELECT

TRIM('&' FROM '&&du&du&&&&') AS out_put ;

lpad：用指定的字符实现左填充指定长度

-- *******费嘟嘟

SELECT

LPAD('费嘟嘟', 10, '*') AS out_put ;

-- 费嘟

SELECT

LPAD('费嘟嘟', 2, '*') AS out_put ;

rpad：用指定的字符实现右填充指定长度

-- 费嘟嘟adadadada

SELECT

RPAD('费嘟嘟', 12, 'ad') AS out_put ;

-- 费嘟

SELECT

RPAD('费嘟嘟', 2, 'ad') AS out_put ;

replace：替换所有出现过的字符串

SELECT

REPLACE(

'费嘟嘟费嘟嘟',

'嘟嘟',

'渡'

) AS out_put ;

数学函数

round：四舍五入

-- 3

SELECT

ROUND(3.48) AS out_put ;

-- 3.5 1代表小数点后尾数

SELECT

ROUND(3.48, 1) AS out_put ;

ceil：向上取整，返回大于等于该参数的最小整数

-- 4

SELECT

CEIL(3.48) AS out_put ;

floor：向下取整，返回小于等于该参数的最大整数

-- 3

SELECT

FLOOR(3.48) AS out_put ;

truncate：截断

-- 3.489

SELECT

TRUNCATE(3.48907857, 3) AS out_put ;

mod：取余(a-a/b*b)

-- 1

SELECT

MOD(10, 3) AS out_put ;

-- 4

SELECT

10 % 6 AS out_put ;

rand：取随机数

--生成不同的随机数

SELECT

RAND() ;

--生成相同的随机数

SELECT

RAND(12) ;

日期函数

now：获取当前系统的日期+时间

-- 2020-09-21 14:02:20

SELECT

NOW() AS out_put ;

curdate：获取当前系统的日期

-- 2020-09-21

SELECT

CURDATE() AS out_put ;

curtime：获取当前系统时间

--15:36:41

SELECT

CURTIME() AS out_put ;

获取指定的部分

SELECT

YEAR(NOW()) AS '年',

MONTH(NOW()) AS '月',

MONTHNAME(NOW()) AS '月-英文' ;

str_to_date：将字符串转换成日期，需要提供字符串的格式

-- 2013-09-13

SELECT

STR_TO_DATE('9-13-2013', '%m-%d-%Y') AS out_put ;

格式符包括：

字符

含义

%Y

四位的年份

%y

两位的年份

%m

月份(01,02,03...11,12)

%c

月份(1,2,3...11,12)

%d

日(01,02,03...)

%H

小时(24小时制)

%h

小时(12小时制)

%i

分钟(00,01,02,03...59)

%s

秒(00,01,02,03...59)

案例：查询入职日期为1992-4-3的员工信息

SELECT

*

FROM

`employees`

WHERE `hiredate` = STR_TO_DATE('4*3 1992', '%m*%d %Y') ;

date_format：将日期转换成字符

-- 2020年09月21日

SELECT

DATE_FORMAT(NOW(), '%Y年%m月%d日') AS out_put;

案例：查询有奖金的员工名和入职日期

SELECT

`last_name`,

`commission_pct`,

DATE_FORMAT(`hiredate`, '%m月/%d日 %Y年') AS `date`

FROM

`employees`

WHERE `commission_pct` IS NOT NULL ;

其他函数

version：查看当前的版本

-- 5.7.28

SELECT

VERSION() ;

user：查看当前的用户

-- root@localhost

SELECT

USER() ;

database：查看当前所在的数据库

-- myemployees

SELECT

DATABASE() ;

password/md5：自动加密

-- *E61178AE691D4ADB63F5FA88A89EBF488B6DDA4D

SELECT

PASSWORD('dudu') ;

-- b247deafa97a5122eef246b489074c5d

SELECT

MD5('dudu') ;

流程控制函数

if函数：实现类似于if-else的效果

-- 相当于三目运算符

SELECT

IF(10 > 5, '大', '小') ;

案例：查询员工是否有奖金

SELECT

`last_name`,

`commission_pct`,

IF(

`commission_pct` IS NULL,

'没奖金嘤嘤嘤',

'有奖金嘿嘿嘿'

) AS '备注'

FROM

`employees` ;

case函数：

case 要判断的字段或表达式

when 常量1 then 要显示的值1或语句1

when 常量2 then 要显示的值2或语句2

...

else 要显示的值n或语句n

end

案例：查询员工的工资，部门号为30，工资为1.1倍，40为1.2倍，50为1.3倍，其他显示为原工资

SELECT

`last_name`,

`department_id`,

`salary` AS '原工资',

CASE

`department_id`

WHEN 30

THEN `salary` * 1.1

WHEN 40

THEN `salary` * 1.2

WHEN 50

THEN `salary` * 1.3

ELSE `salary`

END AS '计算工资'

FROM

`employees` ;

case

when 条件1 then 要显示的值1或语句1

when 条件2 then 要显示的值2或语句2

...

else 要显示的值n或语句n

end

案例：查询员工的工资，如果工资大于20000显示A，15000显示B，10000显示C，否则为D

SELECT

`last_name`,

`department_id`,

`salary` AS '原工资',

CASE

WHEN `salary` > 20000

THEN 'A'

WHEN `salary` > 15000

THEN 'B'

WHEN `salary` > 10000

THEN 'C'

ELSE 'D'

END AS '工资级别'

FROM

`employees` ;

以上为单行函数

分组函数

sum和avg：一般用于处理数值型，null值不参与运算

min和max：可以处理任何类型，null值不参与运算

count：null值不计数

sum 求和

SELECT

SUM(`salary`)

FROM

`employees` ;

avg 求平均值

SELECT

AVG(`salary`)

FROM

`employees` ;

max 求最大值

SELECT

MAX(`salary`)

FROM

`employees` ;

min 求最小值

SELECT

MIN(`salary`)

FROM

`employees` ;

count 求个数

SELECT

COUNT(`salary`)

FROM

`employees` ;

与distinct搭配

SELECT

SUM(`salary`),

SUM(DISTINCT `salary`)

FROM

`employees` ;

SELECT

COUNT(`commission_pct`),

COUNT(DISTINCT `commission_pct`)

FROM

`employees` ;

count函数

MYISAM存储引擎下，COUNT(*)的效率高

INNODB存储引擎下，COUNT(*)和COUNT(1)效率差不多，比COUNT(字段)要高一些

通常情况下会使用COUNT(*)来计数

-- 统计表格中的总行数

SELECT

COUNT(*)

FROM

`employees` ;

-- 可以写任何常量值

SELECT

COUNT(1)

FROM

`employees` ;

和分组函数一同查询的字段要求是group by后的字段

-- 会报错

SELECT

AVG(`salary`),

`employee_id`

FROM

`employees` ;

案例：求员工最大入职时间和最小入职时间相差的天数

SELECT

DATEDIFF(MAX(`hiredate`), MIN(`hiredate`))

FROM

`employees` ;