1 数值函数

SELECT ROUND(5.7355, 2) 		-- 保留小数点后2位, 四舍五入: 5.74
SELECT TRUNCATE(5.7355, 2) 		-- 保留小数点后2位: 5.73
SELECT CEILING(5.2)				-- 6 (上取整)
SELECT FLOOR(5.7)				-- 5 (下取整)
SELECT ABS(-5.2)				-- 求绝对值
SELECT RAND()					-- 得到0~1的随机值

2 字符串函数

1
2
3

SELECT LENGTH('sky') 				-- 3
SELECT UPPER('sky') 				-- SKY
SELECT LOWER('Sky')					-- sky

1
2
3

SELECT LTRIM('     Sky')			-- 去除左边的空格：Sky
SELECT RTRIM('Sky     ')			-- 去除右边的空格：Sky
SELECT TRIM('    Sky  ')			-- 去除两边的空格：Sky

子串

1
2
3

SELECT LEFT('Kindergarten', 4)			-- Kind
SELECT RIGHT('Kindergarten', 6)			-- garten
SELECT SUBSTRING('Kindergarten', 3, 5)	-- nderg

查找位置

1 2	SELECT LOCATE('N', 'Kindergarten') -- 3 不区分大小写，若不在字符串中则返回0 SELECT LOCATE('garten', 'Kindergarten') -- 7

替换

1	SELECT REPLACE('Kindergarten', 'garten', 'garden') -- Kindergarden

拼接

1	SELECT CONCAT('first', 'last') -- firstlast

USE sql_store;

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers

3 日期

3.1 日期函数

1	SELECT NOW(), CURDATE(), CURTIME()

NOW()	CURDATE()	CURTIME()
2021-05-19 19:08:56	2021-05-19	19:08:56

获取指定的时间构成元素

SELECT YEAR(NOW())		-- 2021
SELECT MONTH(NOW())		-- 5
SELECT DAY(NOW())		-- 19
SELECT HOUR(NOW())		-- 19
SELECT MINUTE(NOW())	-- 08
SELECT SECOND(NOW())	-- 56

获取时间的字符串

1	SELECT DAYNAME(NOW()), MONTHNAME(NOW())

DAYNAME(NOW())	MONTHNAME(NOW())
Wednesday	May

EXTRACT

1	SELECT EXTRACT(DAY FROM NOW()) -- 19

3.2 格式化日期和时间

1	SELECT DATE_FORMAT(NOW(), '%M %d %Y')

May 19 2021

1	SELECT DATE_FORMAT(NOW(), '%H:%i %p')

19:17 PM

3.3 计算日期和时间

1
2
3

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY)		-- 	2021-05-20 19:21:32
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR)		-- 	2022-05-19 19:21:32
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR)		-- 	2020-05-19 19:21:32

1	SELECT DATEDIFF('2021-01-05', '2021-01-01') -- 4

1	SELECT TIME_TO_SEC('09:00') - TIME_TO_SEC('09:02') -- -120 (单位:秒)

4 IFNULL 和 COALESCE

IFNULL

USE sql_store;

SELECT
    order_id,
    IFNULL(shipper_id, 'Not assigned') AS shipper
FROM orders

IFNULL(shipper_id, ‘Not assigned’) 即如果shipper_id为NULL, 就改为’Not assigned’

输出:

order_id	shipper
1	Not assigned
3	Not assigned
8	Not assigned
9	1
2	4

COALESCE

USE sql_store;

SELECT
    order_id,
    COALESCE(shipper_id, comments, 'Not assigned') AS shipper
FROM orders

如果shipper_id为空,就返回comments中的值,如果comments也为空,则返回’Not assigned’.

5 IF 函数

格式:

IF(expression, first, second)
如果expression为真,返回first, 为假则返回second

SELECT
    order_id,
    order_date,
    IF(
        YEAR(order_date) = YEAR(DATE_SUB(NOW(), INTERVAL 2 YEAR)),
        'Active',
        'Archived') AS category
FROM orders

当前年份为2021, 所以2019的为’Active’ (Mosh制作视频时为2019), 其他都是’Archived’:

order_id	order_date	category
1	2019-01-30	Active
2	2018-08-02	Archived
3	2017-12-01	Archived
4	2017-01-22	Archived
5	2017-08-25	Archived

6 CASE 运算符

SELECT
    order_id,
    CASE
        WHEN YEAR(order_date) = YEAR(NOW()) - 2 THEN 'Active'
        WHEN YEAR(order_date) = YEAR(NOW()) - 3 THEN 'Last Year'
        WHEN YEAR(order_date) < YEAR(NOW()) - 3 THEN 'Archived'
        ELSE 'Future'
	END AS category
FROM orders

输出:

order_id	category
1	Active
2	Last Year
3	Archived
4	Archived
5	Archived
6	Last Year
7	Last Year
8	Last Year
9	Archived
10	Last Year