MySQL课程笔记---第六章：常用函数

 主讲教师：Mosh；课程链接：【【中字】SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！-哔哩哔哩】 https://b23.tv/MqVRzdk

-- 第六章 函数

-- 数值函数：
-- ROUND函数，用来四舍五入取近似值
SELECT ROUND(5.73) -- 导出6
SELECT ROUND(5.7375,2) -- 2代表约到两位小数，导出5.74
-- TRUNCATE函数，用来截断小数，不进行四舍五入操作
SELECT TRUNCATE(5.7375,2) -- 导出5.73
-- CEILING函数，用来向上取整
SELECT CEILING(5.73) -- 导出6
-- FLOOR函数，用来向下取整
SELECT FLOOR(5.73) -- 导出5
-- ABS函数，用来取绝对值
SELECT ABS(-5.73) -- 导出5.73
-- RAND函数，用来取0到1之间的随机浮点数
SELECT RAND()

-- 字符串函数
-- LENGTH函数，用来取字符串的长度
SELECT LENGTH('sky') -- 导出3
-- UPPER函数，用来将字符转化为大写，LOWER函数，用来转小写
SELECT UPPER('sky')
-- 删去空格的函数：1.LTRIM：左修正；2.RTRIM：右修正；3.TRIM：修正（前面，后面都修，注意，该函数不删字符中间的空格）
SELECT TRIM('    s    ky    ') -- 导出‘s    ky’
-- LEFT函数，导出左起固定数量的字符;RIGHT:右起
SELECT LEFT('kindergarten',4) -- 导出'kind'
-- SUBSTRING函数；从指定位置截取指定长度字符
SELECT SUBSTRING('kindergarten',4,2) -- 导出‘de’
SELECT SUBSTRING('kindergarten',1) -- 导出'kindergarten'
-- LOCATE函数，返回指定字符元素在字符串中的第一个位置,查找不区分大小写，若该元素没有，则导出0
SELECT LOCATE('n','kindergarten') -- 导出3，SQL从1开始算起
SELECT LOCATE('b','kindergarten') -- 导出0
SELECT LOCATE('garten','kindergarten') -- 导出7
-- REPLACE函数，替换字符或字符串，REPLACE('总字符串’，‘被替换字符’，‘替换字符’）
SELECT REPLACE('kindergarten','garten','garden') -- 导出'kindergarden'
-- CONCAT函数，连接字符串
SELECT CONCAT('first','last') -- 导出'firstlast'
SELECT CONCAT(first_name,' ',last_name) AS full_name
FROM customers -- 合并名和姓两列，并在中间加上空格

-- 日期函数：
-- NOW函数：导出当前日期和时间
-- CURDATE函数：导出当前日期
-- CURTIME函数：导出当前时间
SELECT NOW(),CURDATE(),CURTIME()
-- YEAR函数：导出年份;MONTH函数：月份；DATE函数：天；HOUR函数：小时;MINUTE函数：分钟;SECOND函数：秒
SELECT YEAR(NOW())
-- DAYNAME函数；导出该日期是周几（字符串形式）
SELECT DAYNAME(NOW()) -- 导出‘Sunday'
-- MONTHNAME函数：导出该日期在几月（字符串形式）
SELECT MONTHNAME(NOW()) -- 导出'July'
-- EXTRACT函数：提取指定年或月或日
SELECT EXTRACT(DAY FROM NOW())

SELECT * -- 练习
FROM orders
WHERE YEAR(order_date) = YEAR(NOW())

-- 格式化日期和时间
SELECT DATE_FORMAT(NOW(),'%M %d %Y')
-- 各种符号的表示意义搜索'mysql date format string'
SELECT TIME_FORMAT(NOW(),'%H:%i %p')

-- 计算时间和日期
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) -- 导出比当前日期多一天的日期和时间
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR) -- 导出比当前日期少一年的日期和时间
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR) -- 导出比当前日期少一年的日期和时间
SELECT DATEDIFF('2019-01-05','2019-01-01') -- 计算两个日期间隔的天数，可以为负
SELECT TIME_TO_SEC('09:00') -- 计算离零点的秒数

-- IFNULL和COALESCE函数
SELECT 
	order_id,
    IFNULL(shipper_id,'Not assigned') AS shipper, -- 当shipper_id为null时，返回'Not assigned'
    COALESCE(shipper_id,comments,'Not assigned') AS shipperr -- COALESCE返回括号中的第一个非空值，当shipper_id为空，而comments非空时
    -- 返回comments值，只有当comments，shipper_id都为空时，才返回'Not assigned'
FROM orders

SELECT -- 练习
	CONCAT(first_name,' ',last_name) AS customer,
    IFNULL(phone,'Unknown') AS phone
FROM customers

-- IF函数
-- IF(判别表达式，真值，假值）：若判别式为真，则返回真值，否则，返回假值
SELECT
	order_id,
    order_date,
    IF(YEAR(order_date) = YEAR(NOW()),'Active','Archived') AS category
FROM orders

SELECT -- 练习
	product_id,
    name,
    COUNT(*) AS orders,
    IF(COUNT(*) > 1,'M','O') AS frequency
FROM products
JOIN order_items USING (product_id)
GROUP BY product_id,name

SELECT -- 练习（子查询版）
	product_id,
    name,
    (SELECT COUNT(product_id) 
    FROM order_items
    WHERE product_id = p.product_id -- 注意，外查询的列名放在后面
    GROUP BY product_id) AS orders,
    IF((SELECT orders) > 1,'Many times','Once') AS frequency
FROM products p

-- CASE运算符
SELECT 
	order_id,
    CASE
		WHEN YEAR(order_date) = YEAR(NOW()) THEN 'Active' -- 这里要加上THEN,满足当前年，则返回’Active'
        WHEN YEAR(order_date) = YEAR(NOW())-1 THEN 'Last year'
        WHEN YEAR(order_date) < YEAR(NOW())-1 THEN 'Archived'
        ELSE 'Future' -- 当前面几个case都不满足时，返回‘Future'
	END AS category
    -- case运算符类似于C语言的switch-case语句
FROM orders

SELECT -- 练习
	CONCAT(first_name,' ',last_name) AS customer,
    points,
    CASE
		WHEN points > 3000 THEN 'Gold'
        WHEN points BETWEEN 2000 AND 3000 THEN 'Sliver' -- 可以写成WHEN points >= 2000 THEN 'Sliver'，因为3000以上已经被筛选过了
        WHEN points < 2000 THEN 'Bronze' -- 也可以直接写 ELSE ‘Bronze'
	END AS category
    FROM customers
    ORDER BY points DESC