MySQL基础进阶：基本函数

1. 数值函数

主要介绍最常用的几个数值函数：ROUND、TRUNCATE、CEILING、FLOOR、ABS、RAND

SELECT ROUND(5.7365, 2)  -- 四舍五入，保留2位小数 5.74
SELECT TRUNCATE(5.7365, 2)  -- 截断，保留2位小数 5.73
SELECT CEILING(5.2)  -- 向上取整 6
SELECT FLOOR(5.9)  -- 向下取整 5
SELECT ABS(-5.9)  -- 绝对值 5.9
SELECT RAND() -- 0-1之间的随机数

2. 字符串函数

SELECT LENGTH('SKY')  -- 计算字符长度 3
SELECT UPPER('sky')  -- 转换为大写 SKY
SELECT LOWER('SKY')  -- 转换为小写 sky
SELECT LTRIM('   Sky')  -- 移除字符串左侧的空格 Sky
SELECT RTRIM('   Sky    ')-- 移除字符串右侧的空格 Sky
SELECT TRIM('   Sky    ') -- 移除字符串左右两侧的空格 Sky
SELECT LEFT('kindergarden', 4) -- 得到左侧的4个字符 kind
SELECT RIGHT('kindergarden', 6) -- 得到左侧的6个字符 garden
SELECT SUBSTRING('kindergarden', 4, 7) -- 得到从第4个字符开始的共7个字符 dergard
SELECT SUBSTRING('kindergarden', 4) -- 得到从第4个字符开始的所有字符 dergarden
SELECT LOCATE('D', 'kindergarden') -- 返回需要搜索的字符或字符串第一次出现位置4，若没有返回0
SELECT REPLACE('kindergarten', 'garten', 'garden') -- 字符串替换，用后面替换前面字符。不区分大小写
SELECT CONCAT('first',' ', 'last') -- 合并字符串

3. 日期函数

SELECT NOW(), CURDATE(),CURTIME()
-- 获取当前时间，获取当前日期，获取当前时刻
SELECT YEAR(NOW()), MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())
-- 获取当前时间然后返回年、月、日、小时、分钟、秒
SELECT DAYNAME(NOW()), MONTHNAME(NOW())
-- 获取当前时间然后返回星期名和月份名
SELECT EXTRACT(YEAR FROM NOW())
-- SQL标准提取函数 EXTRACT(单位 FROM 日期时间对象) 

--实例
SELECT *
FROM orders
WHERE YEAR(order_date) > YEAR(NOW())
-- 不会输出结果，没有满足条件的结果

3.1 格式化日期和时间

1. DATE_FORMAT(date, 【format】) 将date根据format字符串进行格式化。
2. TIME_FORMAT(time, format) 类似于DATE_FORMAT函数，但这里format字符串只能包含用于小时，分钟，秒和微秒的格式说明符。其他说明符产生一个NULL值或0。
   日期事件格式化函数应该只是转换日期时间对象的显示格式（另外始终铭记日期时间本质是数值）。

Note:搜索mysql date format functions可以查看官方文档。用软件里的帮助功能，如workbench里的HELP INDEX打开官方文档查询或者右侧栏的 automatic comtext help

SELECT DATE_FORMAT(NOW(), '%M %d, %Y') -- September 12, 2020
-- 【格式说明符里，大小写是不同的，这是目前SQL里第一次出现大小写不同的情况】
SELECT TIME_FORMAT(NOW(), '%H:%i %p') -- 11:07 AM

3.2 计算日期和时间

SELECT DATE_SUB(NOW(), INTERVAL -1 DAY)
-- 对日期时间对象进行运算，增加一定时间、日期、年、月等
-- 增加用正数如1  减少用负数如-1. DATE_ADD/DATE_SUB
SELECT DATEDIFF(NOW(), '2022-12-05')
-- 计算两个日期的差距天数
SELECT TIME_TO_SEC('9:00')
-- 该函数计算从零点开始的秒数  32600
SELECT TIME_TO_SEC('9:00') - TIME_TO_SEC('9:02')
-- 该函数计算两个时间之间秒差 -120

4. IFNULL和COALESCE函数

1. IFNULL：用来替换空值，将两个值中的首个非空值进行赋值。
2. COALESE：用来返回一系列中的首个非空值。

SELECT
    order_id,
    IFNULL(shipper_id, 'Not Assigned') AS shipper
    -- 若shipper_id为空，则用Not Assigned进行填充
FROM orders

SELECT
    order_id,
    COALESCE(shipper_id, comments,'Not Assigned') AS shipper
    -- 若shipper_id为空，则先用comments中的值填充，若还为空则用Not Assigned进行填充
FROM orders

5. IF函数

条件判断语句：满足条件返回第一个值，不满足返回第二个值.

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

SELECT
    order_id,
    order_date,
    IF(
        YEAR(order_date) = 2019,
        'Active',
        'Archived'
    ) AS category
FROM orders

SELECT
    product_id,
    name,
    COUNT(*) AS orders,
    -- 聚合函数与GROUP BY 结合起来使用
    IF(COUNT(*)= 1, 'Once', 'Many times') AS Frequency
    -- SELECT orders报错，似乎 “SELECT 引用同级列别名” 这种特殊的子查询不能用于分组聚合函数
FROM products
JOIN order_itemS USING (product_id)
GROUP BY product_id, name

6. CASE运算符

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

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

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

Note:也可以用IF来嵌套
-- 以下查询效果相同
SELECT
    CONCAT(first_name, ' ', last_name) AS customer,
    points,
    CASE
        WHEN points > 3000 THEN 'Gold'
        WHEN points BETWEEN 2000 AND 3000 THEN 'Silver'
        WHEN points < 2000 THEN 'Bronze'
        ELSE null
    END AS category
FROM customers
ORDER BY points DESC

SELECT
    CONCAT(first_name, ' ', last_name) AS customer,
    points,
    CASE
        WHEN points < 2000 THEN 'Bronze'
        WHEN points <= 3000 THEN 'Silver'
        ELSE 'Gold'
    END AS category
FROM customers
ORDER BY points DESC

-- 逐条判断语句
SELECT
    CONCAT(first_name, ' ', last_name) AS customer,
    points,
    IF(points < 2000, 'Bronze',
        IF(points <= 3000, 'Silver', 'Gold')) AS category
FROM customers
ORDER BY points DESC
-- 逐层判断，IF嵌套