7. 数值函数

举出了一部份常用的，遇到不会的再上网搜就可以

常用的数值函数

- ROUND(double,num)     -- 下取整，保留几位小数
- CEILING(double,num)   -- 上取整，保留几位小数
- FLOOR(double)         -- 下取整
- ABS(double)           -- 绝对值
- RAND()                -- 返回介于0-1的随机数

MySQL数值函数 - MySQL教程 (yiibai.com)

字符串函数

- LENGTH(string)        -- 返回字符串长度
- UPPER(string)			-- 转换字符串为大写
- LOWER(string)			-- 转换字符串为小写
- LTRIM(string)			-- 移除出字符串左侧的空格
- RTRIM(string)			-- 移除字符串右侧的空格
- TRIM(string)			-- 移除字符串前后的空格
- LEFT(string,num)		-- 得到字符串前num个字符
- RIGHT(string,num)		-- 移除字符串后num个字符
- SUBSTRING(string,start,lenth) -- c++中的同样用法
- LOCATE(string,string)	-- 无视大小写，在右边字符串找到以一个匹配左边字符串的下标（用1开始，没找的返回0）
- REPLACE(string,string,string) -- 替换第2个字符为第3个
- CONCAT(string,string,...) -- 连接多个字符串

日期函数

- NOW()
- CURDATE()
- CURTIME()
- YEAR(time)
- MONTH(time)
- DAY(time)
- HOUR(time)
- MINUTE(time)
- DAYNAME(time)
- MONTHNAME(time)

SELECT EXTRACT(YEAR FROM NOW())

日期函数可以让查询更简洁，更易读

USE sql_store;

SELECT * 
FROM orders
WHERE YEAR(order_date) = YEAR(now())

日期格式化

SELECT DATE_FORMAT(NOW(), '%M %d, %Y')  -- September 12, 2020

SELECT TIME_FORMAT(NOW(), '%H:%i %p')  -- 11:07 AM

格式说明符里，大小写是不同的，这是目前SQL里第一次出现大小写不同的情况

计算日期和格式化

有时需要对日期事件对象进行运算，如增加一天或算两个时间的差值之类，介绍一些最有用的日期时间计算函数：

1. DATE_ADD, DATE_SUB
2. DATEDIFF
3. TIME_TO_SEC

增加或减少一定的天数、月数、年数、小时数等等

SELECT DATE_ADD(NOW(), INTERVAL -1 DAY)
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR)
SELECT DATE_DIFF(NOW(), '2019-01-01 09:00')
SELECT TIME_TO_SEC('09:00') - TIME_TO_SEC('09:02')    -- -120

IFNULL和COALESCE

USE sql_store;

SELECT 
    order_id,
    IFNULL(shipper_id, 'Not Assigned') AS shipper
    /* If expr1 is not NULL, IFNULL() returns expr1; 
    otherwise it returns expr2. */
FROM orders

将 orders 里 shipper.id 中的空值替换为 comments，若 comments 也为空则替换为 'Not Assigned'（未分配）

USE sql_store;

SELECT 
    order_id,
    COALESCE(shipper_id, comments, 'Not Assigned') AS shipper
    /* Returns the first non-NULL value in the list, 
    or NULL if there are no non-NULLvalues. */
FROM orders

COALESCE 函数是返回一系列值中的首个非空值，更灵活 （coalesce vi. 合并；结合；联合）

IF函数

根据是否满足条件表达式返回不同的值:

IF(条件表达式, 返回值1, 返回值2) 返回值可以是任何东西，数值 文本 日期时间 空值null

USE sql_store;

SELECT 
    *,
    IF(YEAR(order_date) = YEAR(NOW()),
       'Active',
       'Archived') AS category
FROM orders

CASE

当分类多余两种时，可以用IF嵌套，也可以用CASE语句，后者可读性更好

CASE语句结构：

CASE 
    WHEN …… THEN ……
    WHEN …… THEN ……
    WHEN …… THEN ……
    ……
    [ELSE ……] （ELSE子句是可选的）
END

USE sql_store;

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

ELSE 'Future' 是可选的，实验发现若分类不完整，比如只写了今年和去年的两个分类条件，则不在这两个分类的记录的 category 字段会是 null.