sql函数的用法

函数

单行函数

函数	示例	结果	描述
upper	select upper('sdfd');	SDFD	将字母转换为大写
lower	select lower('ABc');	abc	将字母转换为小写
concat	select concat('hello','world');	'hello world'	字符串连接
substr/substring	select substr('hello world',2,4);	'ello'	截取字符串
length	select length('hello world');	11	获取字符串长度
instr	select instr('hello world','world');	7	获取子字符串在父字符串中的索引
trim	select trim(' hello ');	'hello'	去除两端空格
ltrim	select ltrim(' hello');	'hello'	去掉左端的空格
rtrim	select rtrim('hello ');	'hello'	去掉右端的空格
replace	select replace('hello java','java','world');	'hello world'	替换文本

**注：**这里的字符串是从1开始的（不是0）

数学函数

函数	示例	结果	作用
round(x, [y])	select round(5.64,1);	5.6	对指定的值进行四舍五入，可指定保留的小数位数y
truncate(x, [y])	select truncate(5.6,0);	5	对指定的数值进行截取操作，可指定保留的小数位数y
ceil(x)	select ceil(4.56);	5	返回不小于指定值x的最小整数，向上取整
floor(x)	select floor(8.5);	8	返回不大于指定值x的最大整数，向下取整
abs(x)	select abs(-12);	12	取指定数值x的绝对值

日期函数

函数	示例	结果	作用
current_timestamp()	select current_timestamp();	2019-11-07 20:53:47	获取当前时间戳
current_date()/CURDATE()	select current_date();	2019-11-07	获取当前日期
current_time()/CURTIME()	select current_time();	20:56:00	获取当前时间
now()	select now();	2019-11-07 20:57:15	获取当前时间
ADDDATE(d,n)	SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);	2017-06-25	计算起始日期d加上n天的日期
ADDTIME(t,n)	SELECT ADDTIME('2011-11-11 11:11:11', 5);	2011-11-11 11:11:16	将时间t加上时间表达式n
DATEDIFF(d1,d2)	SELECT DATEDIFF('2001-01-01','2001-02-02');	-32	计算日期d1与d2之间相隔的天数
DATE_ADD(d, INTERVAL expr type)	SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY);	2017-06-25	计算起始日期d加上一个时间段后的日期
DATE_SUB(date,INTERVAL expr type)	SELECT DATE_SUB("2017-06-15", INTERVAL 10 DAY);	2017-06-05	计算起始日期d减去一个时间段后的日期
DATE_FORMAT(d,f)	SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r');	2011-11-11 11:11:11 AM	按要求的表达式f显示日期d
DAY(d)	SELECT DAY("2017-06-15");	15	返回日期值d的日期部分
DAYNAME(d)	SELECT DAYNAME('2011-11-11 11:11:11');	Friday	返回日期d属于星期几的名称
DAYOFMONTH(d)	SELECT DAYOFMONTH('2011-11-11 11:11:11');	11	计算日期d是本月的第几天
DAYOFWEEK(d)	SELECT DAYOFWEEK('2011-11-11 11:11:11');	6	计算日期d是星期几，返回值1表示星期日，2表示星期一，以此类推
DAYOFYEAR(d)	SELECT DAYOFYEAR('2011-11-11 11:11:11');	315	计算日期d是本年的第几天
EXTRACT(type FROM d)	SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11');	11	从日期d中获取指定的值，type指定返回的值
FROM_DAYS(n)	SELECT FROM_DAYS(1111);	0003-01-16	计算从0000年1月1日开始n天后的日期
HOUR(t)	SELECT HOUR('1:2:3');	1	返回t中的小时值
LAST_DAY(d)	SELECT LAST_DAY("2017-06-20");	2017-06-30	返回给定日期的那个月份的最后一天
MAKEDATE(year, day-of-year)	SELECT MAKEDATE(2017, 3);	2017-01-03	基于给定的年份year和所在年中的天数序号day-of-year返回一个日期
MINUTE(t)	SELECT MINUTE('1:2:3');	2	返回t中的分钟值
MONTHNAME(d)	SELECT MONTHNAME('2011-11-11 11:11:11');	November	返回日期当中的月份名称
MONTH(d)	SELECT MONTH('2011-11-11 11:11:11');	11	返回日期d中的月份值，1到12
QUARTER(d)	SELECT QUARTER('2011-11-11 11:11:11');	4	返回日期 d 是第几季节，返回1到4
SECOND(t)	SELECT SECOND('1:2:3');	3	返回 t 中的秒钟值
SUBDATE(d,n)	SELECT SUBDATE('2011-11-11 11:11:11', 1);	2011-11-10 11:11:11	日期 d 减去 n 天后的日期
WEEK(d)	SELECT WEEK('2011-11-11 11:11:11');	45	计算日期 d 是本年的第几个星期，范围是0到53
WEEKDAY(d)	SELECT WEEKDAY("2017-06-15");	3	日期 d 是星期几，0 表示星期一，1 表示星期二
WEEKOFYEAR(d)	SELECT WEEKOFYEAR('2011-11-11 11:11:11');	45	计算日期 d 是本年的第几个星期，范围是0到53
YEARWEEK(date, mode)	SELECT YEARWEEK("2017-06-15");	201724	返回年份及第几周（0到53），mode 中 0 表示周天，1 表示周一，以此类推

type 值可以是:

• MICROSECOND 微秒
• SECOND 秒
• MINUTE 分钟
• HOUR 小时
• DAY 天
• WEEK 周
• MONTH 月
• QUARTER 季度
• YEAR 年
• MINUTE_SECOND 分钟:秒
• HOUR_SECOND 小时:分钟 : 秒
• HOUR_MINUTE 小时:分钟
• DAY_SECOND 天 小时:分钟:秒DAY_MINUTE 天 小时:分钟
• DAY_HOUR 天 小时
• YEAR_MONTH 年-月

可以被用在format字符串:

• %M 月名字(January……December)
• %W 星期名字(Sunday……Saturday)
• %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)
• %Y 年, 数字, 4 位
• %y 年, 数字, 2 位
• %a 缩写的星期名字(Sun……Sat)
• %d 月份中的天数, 数字(00……31)
• %e 月份中的天数, 数字(0……31)
• %m 月, 数字(01……12)
• %c 月, 数字(1……12)
• %b 缩写的月份名字(Jan……Dec)
• %j 一年中的天数(001……366)
• %H 小时(00……23)
• %k 小时(0……23)
• %h 小时(01……12)
• %I 小时(01……12)
• %l 小时(1……12)
• %i 分钟, 数字(00……59)
• %r 时间,12 小时(hh:mm:ss [AP]M)
• %T 时间,24 小时(hh:mm:ss)
• %S 秒(00……59)
• %s 秒(00……59)
• %p AM或PM
• %w 一个星期中的天数(0=Sunday ……6=Saturday )
• %U 星期(0……52), 这里星期天是星期的第一天
• %u 星期(0……52), 这里星期一是星期的第一天

IFNULL 用法

MySQL IFNULL 函数是MySQL控制流函数之一，它接受两个参数，如果不是 NULL ，则返回第一个参数。 否则， IFNULL 函数返回第二个参数。

SELECT IFNULL(num, '暂无') FROm cart;
# 如果行数据num为null，查询结果中显示‘暂无’，如果不是null，显示num的值

select last_name,ifnull(manager_id,0) from s_emp;

CASE语句

CASE 语句遍历条件并在满足第一个条件时返回一个值（如 IF-THEN-ELSE 语句）。

因此，一旦条件为真，它将停止读取并返回结果。

如果没有条件为真，它将返回 ELSE 子句中的值。

如果没有ELSE部分且没有条件为真，则返回NULL。

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END
# condition1, condition2, ...conditionN 条件
# result1, result2, ...resultN 条件为真时返回的值
# END 表示结束
SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
WHEN City IS NULL THEN Country
ELSE City
END);
# 如果city是NULL 按照country排序
-- 按照成绩输出等级
SELECT id, score, CASE
WHEN score < 60 THEN 'D'
WHEN score < 70 THEN 'C'
WHEN score < 80 THEN 'B'
END '成绩等级' FROM score;