-- 假設(shè)有一張 products 表
-- 總產(chǎn)品數(shù)
SELECT COUNT(*) AS total_products FROM products;
?
-- 有效庫存記錄數(shù)（stock 列非 NULL）
SELECT COUNT(stock) AS stock_count FROM products;
?
-- 不同類別的數(shù)量
SELECT COUNT(DISTINCT category) AS category_count FROM products;
?
-- 總庫存
SELECT SUM(stock) AS total_stock FROM products;
?
-- 平均價格
SELECT AVG(price) AS avg_price FROM products;
?
-- 最高價格
SELECT MAX(price) AS max_price FROM products;
?
-- 最低價格
SELECT MIN(price) AS min_price FROM products;

-- 示例對比
SELECT 
    COUNT(1)         AS all_rows,       -- 所有行
    COUNT(*)         AS all_rows_too,   -- 所有行（同上）
    COUNT(email)     AS has_email,      -- 郵件不為空的行
    COUNT(DISTINCT department) AS dept_count  -- 部門數(shù)量
FROM employees;

-- 將每個部門的人員姓名合并
SELECT 
    department,
    GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS members
FROM employees
GROUP BY department;
?
-- 結(jié)果類似：IT | Alice, Bob, Charlie

-- 將分組數(shù)據(jù)聚合為 JSON 數(shù)組
SELECT department, JSON_ARRAYAGG(name) AS names FROM employees GROUP BY department;
?
-- 將分組數(shù)據(jù)聚合為 JSON 對象
SELECT department, JSON_OBJECTAGG(emp_id, name) AS emp_map FROM employees GROUP BY department;

-- 拼接姓名
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
-- 結(jié)果：Alice Smith
?
-- 用分隔符拼接
SELECT CONCAT_WS('-', '2024', '01', '15') AS date_str;
-- 結(jié)果：2024-01-15
?
-- 截取子字符串（pos 從 1 開始）
SELECT SUBSTRING('Hello World', 1, 5) AS result;
-- 結(jié)果：Hello
?
-- 手機號脫敏
SELECT CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS masked_phone FROM users;
-- 結(jié)果：138****1234

SELECT UPPER('mysql') AS result;         -- MYSQL
SELECT LOWER('MYSQL') AS result;         -- mysql
SELECT TRIM('  hello  ') AS result;      -- hello
SELECT TRIM(LEADING '0' FROM '00123') AS result;  -- 123
SELECT TRIM(TRAILING 'x' FROM 'abcxxx') AS result; -- abc

-- 左側(cè)補零（常用于生成工號）
SELECT LPAD('123', 8, '0') AS emp_id;
-- 結(jié)果：00000123
?
-- 右側(cè)填充
SELECT RPAD('MySQL', 10, '*') AS result;
-- 結(jié)果：MySQL*****
?
-- 重復(fù)
SELECT REPEAT('Go', 3) AS result;
-- 結(jié)果：GoGoGo

-- 查找位置（不區(qū)分大小寫）
SELECT LOCATE('world', 'Hello World') AS position;  -- 7
SELECT INSTR('Hello World', 'world') AS position;    -- 7
?
-- SQL 標準寫法
SELECT POSITION('world' IN 'Hello World') AS position; -- 7
?
-- 替換
SELECT REPLACE('Hello World', 'World', 'MySQL') AS result;
-- Hello MySQL
?
-- 指定位置替換
SELECT INSERT('Hello World', 7, 5, 'CSDN') AS result;
-- Hello CSDN

SELECT 
    CHAR_LENGTH('Hello')    AS char_len,   -- 5（字符數(shù)）
    LENGTH('Hello')         AS byte_len,   -- 5（字節(jié)數(shù)）
    CHAR_LENGTH('你好')      AS cn_char_len,-- 2（字符數(shù)）
    LENGTH('你好')           AS cn_byte_len; -- 6（UTF-8 每中文字符 3 字節(jié)）

SELECT NOW();          -- 2024-01-15 10:30:45
SELECT CURDATE();      -- 2024-01-15
SELECT CURTIME();      -- 10:30:45
SELECT UTC_TIMESTAMP();-- 2024-01-15 02:30:45（UTC 時間）

-- 加 3 天
SELECT DATE_ADD('2024-01-15', INTERVAL 3 DAY) AS result;
-- 2024-01-18
?
-- 減 1 個月
SELECT DATE_SUB('2024-01-15', INTERVAL 1 MONTH) AS result;
-- 2023-12-15
?
-- 日期差
SELECT DATEDIFF('2024-03-01', '2024-02-15') AS days_diff;
-- 15
?
-- 時間差
SELECT TIMESTAMPDIFF(HOUR, '2024-01-15 08:00:00', '2024-01-15 17:30:00') AS hours;
-- 9
?
-- 計算年齡
SELECT TIMESTAMPDIFF(YEAR, '1990-05-20', CURDATE()) AS age;

-- 格式化
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i:%s') AS formatted;
-- 2024年01月15日 10:30:45
?
-- 字符串轉(zhuǎn)日期
SELECT STR_TO_DATE('2024-01-15', '%Y-%m-%d') AS date_val;
-- 2024-01-15
?
-- 時間格式化
SELECT TIME_FORMAT('10:30:45', '%H時%i分%s秒') AS time_str;
-- 10時30分45秒

-- 假設(shè)有 scores 表：student, subject, score
SELECT 
    student, subject, score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK()       OVER (ORDER BY score DESC) AS rank_val,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_val
FROM scores;
?
-- 結(jié)果對比（分數(shù)相同時）：
-- student | score | row_num | rank_val | dense_rank_val
-- Alice   | 95    | 1       | 1        | 1
-- Bob     | 95    | 2       | 1        | 1
-- Charlie | 90    | 3       | 3        | 2

-- LAG/LEAD 計算環(huán)比
SELECT 
    order_date,
    daily_amount,
    LAG(daily_amount, 1) OVER (ORDER BY order_date) AS prev_day_amount,
    daily_amount - LAG(daily_amount, 1) OVER (ORDER BY order_date) AS diff
FROM daily_sales;

SELECT 
    student, score,
    NTILE(4) OVER (ORDER BY score DESC) AS quartile,
    PERCENT_RANK() OVER (ORDER BY score DESC) AS pct_rank
FROM scores;

function_name([expr]) OVER (
    [PARTITION BY expr, ...]    -- 分區(qū)
    [ORDER BY expr [ASC|DESC]]  -- 排序
    [frame_clause]              -- 窗口幀范圍
)

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- 默認
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING            -- 前后各一行
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING    -- 當前行到末尾
RANGE BETWEEN INTERVAL 1 DAY PRECEDING AND CURRENT ROW -- 時間范圍

SELECT ROUND(88.234, -1);   -- 90
SELECT ROUND(88.234, -2);   -- 100

-- [a, b) 之間的隨機數(shù)：RAND() * (b - a) + a
-- 生成 [5, 10) 的隨機數(shù)
SELECT RAND() * 5 + 5;
?
-- 生成 [1, 100] 的隨機整數(shù)
SELECT FLOOR(RAND() * 100) + 1;

-- IF 簡單判斷
SELECT name, IF(score >= 60, '通過', '未通過') AS result FROM exams;
?
-- IFNULL 處理 NULL
SELECT IFNULL(avatar_url, '/images/default.png') AS avatar FROM users;
?
-- NULLIF 避免除零錯誤
SELECT amount / NULLIF(quantity, 0) AS unit_price FROM orders;
?
-- COALESCE 優(yōu)先取值
SELECT COALESCE(mobile, phone, email, '無聯(lián)系方式') AS contact FROM users;
?
-- CASE 多條件判斷
SELECT name, score,
    CASE 
        WHEN score >= 90 THEN '優(yōu)秀'
        WHEN score >= 80 THEN '良好'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END AS grade
FROM students;

-- 假設(shè) data 列存儲：{"name":"Alice","address":{"city":"Beijing"}}
?
-- 提取字段
SELECT data->'$.name' AS name FROM users;         -- "Alice"（帶引號）
SELECT data->>'$.name' AS name FROM users;        -- Alice（文本）
?
-- 嵌套取值
SELECT data->>'$.address.city' AS city FROM users; -- Beijing
?
-- 提取數(shù)組元素
SELECT JSON_EXTRACT('[1, 2, 3]', '$[0]');         -- 1

-- 將 JSON 數(shù)組轉(zhuǎn)為表格數(shù)據(jù)
SELECT * FROM JSON_TABLE(
    '[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]',
    '$[*]' COLUMNS (
        id INT PATH '$.id',
        name VARCHAR(50) PATH '$.name'
    )
) AS jt;
?
-- 結(jié)果：
-- +------+-------+
-- | id   | name  |
-- +------+-------+
-- | 1    | Alice |
-- | 2    | Bob   |
-- +------+-------+

-- 字符串轉(zhuǎn)整數(shù)
SELECT CAST('123' AS SIGNED) + 1;          -- 124
?
-- 整數(shù)轉(zhuǎn)字符串
SELECT CONCAT('編號：', CAST(123 AS CHAR)); -- 編號：123
?
-- 字符串轉(zhuǎn)日期
SELECT CAST('2024-01-15' AS DATE);          -- 2024-01-15
?
-- 轉(zhuǎn)為 JSON
SELECT CAST('{"id":1}' AS JSON);
?
-- 大小寫敏感比較
SELECT 'ABC' = 'abc';                        -- 1（不區(qū)分大小寫）
SELECT BINARY 'ABC' = 'abc';                 -- 0（區(qū)分大小寫）

-- 如果 quantity 為 0，NULLIF 返回 NULL，除 NULL 結(jié)果為 NULL
SELECT amount / NULLIF(quantity, 0) AS unit_price FROM orders;