MySQL: MySQL内置函数大全与实战应用

-- 统一电话格式
SELECT name, REPLACE(phone, '-', '') AS clean_phone FROM users;

-- 统一日期格式
SELECT name, STR_TO_DATE(created_at, '%Y/%m/%d') AS clean_date FROM users;

-- 计算折扣价并四舍五入
SELECT 
    product_name,
    price,
    ROUND(price * 0.85, 2) AS sale_price
FROM products;

-- 计算页数（向上取整）
SELECT CEIL(103 / 10) AS total_pages;  -- 11 页

-- 随机排序
SELECT * FROM users ORDER BY RAND() LIMIT 5;

-- 拼接全名
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

-- 清理电话号码
SELECT REPLACE(REPLACE(phone, '-', ''), ' ', '') AS clean_phone FROM users;

-- 截取邮箱域名
SELECT 
    email,
    SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM users;

-- 生成订单号
SELECT CONCAT('ORD', LPAD(id, 6, '0')) AS order_no FROM orders;

+-------------+------------+
| email       | domain     |
+-------------+------------+
| a@gmail.com | gmail.com  |
| b@163.com   | 163.com    |
+-------------+------------+

-- 查询今天的数据
SELECT * FROM orders WHERE DATE(created_at) = CURDATE();

-- 查询最近 7 天
SELECT * FROM orders WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 按月统计
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY month
ORDER BY month;

-- 计算年龄
SELECT 
    name,
    birth_date,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM users;

+---------+-------------+--------------+
| month   | order_count | total_amount |
+---------+-------------+--------------+
| 2026-01 |         120 |     45000.00 |
| 2026-02 |         135 |     52000.00 |
| 2026-03 |         148 |     58000.00 |
+---------+-------------+--------------+

-- 常用格式
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');           -- 2026-07-03
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');       -- 2026年07月03日
SELECT DATE_FORMAT(NOW(), '%H:%i:%s');           -- 10:30:00
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y');     -- Friday, July 03, 2026

-- IF 判断
SELECT 
    name,
    score,
    IF(score >= 60, 'Pass', 'Fail') AS result
FROM students;

-- IFNULL 处理 NULL
SELECT 
    name,
    IFNULL(phone, 'No phone') AS phone
FROM users;

-- CASE 多条件
SELECT 
    name,
    score,
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END AS grade
FROM students;

+-------+-------+-------+
| name  | score | grade |
+-------+-------+-------+
| Alice |    95 | A     |
| Bob   |    82 | B     |
| Carol |    67 | D     |
| Dave  |    45 | F     |
+-------+-------+-------+