数值函数

ROUND函数

格式：ROUND（value，parameter）
将value四舍五入到paramet位小数

1
2
SELECT ROUND(5.7542,2)
-- 运行结果：5.75

TRUNCATE函数

格式：TRUNCATE（value，parame）
将value截断到paramet位小数

1
2
SELECT TRUNCATE(5.7552,2)
-- 运行结果：5.75

CEILING函数

格式：CELING（value）
返回大于或等于value的最小整数

1
2
SELECT CEILING(5.76)
-- 运行结果：6

FLOOR函数

格式：FLOOR（value）
返回小于或等于value的最大整数

1
2
SELECT FLOOR(1.52)
-- 运行结果：1

ABS函数

格式：ABS（value）
返回绝对值

1
2
SELECT ABS(-5.2)
-- 运行结果：5.2

RAND函数

生成0-1之间的随机值

1
SELECT RAND()

字符串函数

LENGTH函数

格式：LENGTH（str）
返回字符串的字符个数

1
2
SELECT LENGTH('sky')
-- 运行结果：3

UPPER函数

格式：UPPER（str）
返回大写的字符串

1
2
SELECT UPPER('sky')
-- 运行结果：SKY

LOWER函数

格式：LOWER（str）
返回小写的字符串

1
2
SELECT LOWER('SKY')
-- 运行结果：sky

TRIM函数

分为三种：TRIM，LTRIM，RTRIM
用于删除多余的空格

1
2
3
SELECT TRIM('   SKY   '),  -- 删除SKY前后多余的空格
	LTRIM('   cat'),  -- 删除cat左边多余的空格
    RTRIM('dog  ')  -- 删除dog右边多余的空格

LEFT函数

格式：LEFT（str，parameter）
从左侧开始返回parameter个字符

1
2
SELECT LEFT('Kingdom',4)
-- 运行结果：King

RIGHT函数

格式：RIGHT（str，parameter）
从右侧开始返回parameter个字符

1
2
SELECT RIGHT('Kingdom',3)
-- 运行结果：dom

SUBSTRING函数

格式：SUBSTRING（str，start，end）
截取str的start位置到end位置，若无end则默认到str末尾

1
2
3
4
SELECT SUBSTRING('Kingdom',1,4)
-- 运行结果：King
SELECT SUBSTRING('Kingdom',2)
-- 运行结果：ingdom

LOCATE函数

格式：LOCATE（str1，str2）
返回str1在str2第一次出现的位置

1
2
3
SELECT LOCATE('n','Kingarten')
-- 运行结果：3
-- MySQL的位置从1起

REPLACE函数

格式：REPLACE（str，str1，str2）
将str中的str1替换为str2

1
2
SELECT REPLACE('Kingarten','garten','garden')
-- 运行结果：Kingarden

CONCAT函数

格式：CONCAT（str1，str2，……）
连接字符串

1
2
SELECT CONCAT('John',' ','Smith')
-- 运行结果：John Smith

日期函数

NOW() 返回当前的时间
CURDATE() 返回当前的日期
CURTIME() 返回当前的时刻

EXTRACT函数

1
2
SELECT EXTRACT(YEAR FROM NOW()) -- 返回当前的年份
-- YEAR 可以更换为 MONTH，DAY，HOUR，MINUTE，SECOND等等

日期和时间的计算

增加或减少日期

  • DATE_ADD(日期，INTERVAL 1 DAY) DAY可以替换为MONTH/YEAR
1
2
SELECT DATE_ADD('2020-01-01',INTERVAL 1 DAY)
-- 结果：2020-01-02
  • DATE_SUB(日期，INTERVAL 1 DAY) DAY可以替换为MONTH/YEAR
1
2
SELECT DATE_SUB('2020-01-01',INTERVAL 1 DAY)
-- 结果：2019-12-31
  • DATEDIFF(日期1，日期2)
    以日期1减去日期2，求得两个日期之间的间隔，时间间隔不纳入考虑
1
2
SELECT DATEDIFF('2020-02-01','2020-01-01')
-- 结果：31
  • TIME_TO_SEC(时刻)
    将时刻转换为秒，从而易于计算，从凌晨0点开始计算
1
2
SELECT TIME_TO_SEC('09:00')-TIME_TO_SEC('05:00')
-- 结果：14400

其他函数

IFNULL函数

格式：IFNULL(列名，字符串)
判断列值中是否有空值，若有空值，以字符串内容替换

1
2
3
4
SELECT
	order_id,
	IFNULL(shipper_id,'Not Assigned')
FROM orders

COALESCE函数

格式：COALESCE（列名1，列名2，…，字符串）
若列名1中存在空值，以同行列名2的值进行替换，若还为空则继续替换，直至以字符串内容替换

1
2
3
4
SELECT
	order_id,
	COALESCE(shipper_id,comments,'Not Assigned')
FROM orders

IF函数

格式：IF（表达式，返回值1，返回值2）
表达式成立则得到返回值1，否则得到返回值2

1
2
3
4
5
6
7
8
SELECT 
	oi.product_id,
    p.name,
    COUNT(*) orders,
    IF(COUNT(*)>1,'Many times','Once') frequency
FROM order_items oi
JOIN products p USING(product_id)
GROUP BY oi.product_id,p.name

CASE运算符

格式： CASE WHEN 表达式 THEN 返回值 …… (ELSE) 返回值 END
通过多个测试语句返回不同的结果

1
2
3
4
5
6
7
8
9
10
SELECT 
	CONCAT(first_name,' ',last_name) customer,
    points,
    CASE 
    	WHEN points>3000 THEN 'Gold'
    	WHEN points BETWEEN 2000 AND 3000 THEN 'Sliver'
    	WHEN points<2000 THEN 'Bronze'
    END AS category
FROM customers
ORDER BY points DESC;