MySQL汇总数据及基本函数

聚合函数

SELECT 
    MAX(invoice_date) AS latest_date,  
    MIN(invoice_total) AS lowest,
    AVG(invoice_total) AS average,
    SUM(invoice_total * 1.1) AS total,
    COUNT(invoice_total) AS number_of_invoices, 
    COUNT(*) AS total_records,
    -- 包括null 
    COUNT(DISTINCT client_id) AS number_of_distinct_clients
    -- DISTINCT去重，会得到不同顾客数
FROM invoices
WHERE invoice_date >= '2019-07-01'  

GROUP BY子句

SELECT 
    date, 
    pm.name AS payment_method,
    SUM(amount) AS total_payments
FROM payments p
JOIN payment_methods pm
    ON p.payment_method = pm.payment_method_id
GROUP BY date, payment_method

HAVING子句

SELECT 
    client_id,
    SUM(invoice_total) AS total_sales,
    COUNT(*/invoice_total/invoice_date) AS number_of_invoices
FROM invoices
GROUP BY client_id WITH ROLLUP
-- WITH ROLLUP自动汇总
HAVING total_sales > 500 AND number_of_invoices > 5

当 HAVING 筛选的是聚合函数时，该聚合函数可以不在SELECT里显性出现。

数值函数

SELECT ROUND(5.7365, 2)  -- 四舍五入
SELECT TRUNCATE(5.7365, 2)  -- 截断
SELECT CEILING(5.2)  -- 天花板函数，大于等于此数的最小整数
SELECT FLOOR(5.6)  -- 地板函数，小于等于此数的最大整数
SELECT ABS(-5.2)  -- 绝对值
SELECT RAND()  -- 随机函数，0到1的随机值

查看MySQL全部数值函数可点击这里​​​​​​

字符串函数

SELECT LENGTH('sky')  -- 字符串字符个数/长度（LENGTH）
SELECT UPPER('sky')  -- 转大写
SELECT LOWER('Sky')  -- 转小写

-- 删除空格
SELECT LTRIM('  Sky')
SELECT RTRIM('Sky  ')
SELECT TRIM(' Sky ')

-- 切片
SELECT LEFT('Kindergarden', 4)  -- 取左边4个字符
SELECT RIGHT('Kindergarden', 6)  -- 取右边6个字符
SELECT SUBSTRING('Kindergarden', 7, 6)  
-- 取中间从第7个开始的长度为6的子串
-- 省略第3参数（子串长度）则一直截取到最后

--定位
SELECT LOCATE('gar', 'Kindergarden')  -- 定位首次出现的位置
-- 没有的话返回0，不区分大小写

-- 替换
SELECT REPLACE('Kindergarten', 'garten', 'garden')

--连接
USE sql_store;

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

 查看MySQL全部字符串函数可点击这里

日期函数

-- 当前时间
SELECT NOW()  -- 日期加时间
SELECT CURDATE()  -- 日期
SELECT CURTIME()  -- 时间

-- 提取时间中的元素
SELECT YEAR(NOW())  
-- 还有MONTH DAY HOUR MINUTE SECOND
SELECT DAYNAME(NOW())  -- 返回星期几
SELECT MONTHNAME(NOW())  -- 返回月份
-- 返回均为字符串

-- 标准SQL语言
SELECT EXTRACT(YEAR FROM NOW())
-- EXTRACT(单位 FROM 日期时间对象)

 格式化日期和时间

SELECT DATE_FORMAT(NOW(), '%M %d, %Y')  -- January 14, 2022
-- 格式说明符里，大小写不同
SELECT TIME_FORMAT(NOW(), '%H:%i %p')  -- 11:43 AM

以下是所有格式说明符

 计算日期和时间

SELECT DATE_ADD(NOW(), INTERVAL -1 DAY)
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR)
-- 月数、年数、小时数同理

--也可以不用函数
NOW() - INTERVAL 1 DAY
NOW() + INTERVAL 1 YEAR 

--计算日期差
SELECT DATEDIFF('2019-01-01 09:00', '2019-01-05')  
-- 会忽略时间部分，只算日期差异

SELECT TIME_TO_SEC('09:00')  -- 32400
SELECT TIME_TO_SEC('09:00') - TIME_TO_SEC('09:02')  -- -120

 IFNULL和COALESCE函数

SELECT 
    order_id,
    IFNULL(shipper_id, 'Not Assigned') AS shipper,
    -- 替换null
    COALESCE(shipper_id, comments, 'Not Assigned') AS shipper
    -- 替换为comments，若comments也为空则替换为Not Assigned
FROM orders

IF函数

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

CASE函数

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 'Achived'
        ELSE 'Future'  
    END AS 'category'
FROM orders