5分钟学会！MySQL常用函数速查表

💡 收藏这篇就够了！最全的MySQL函数速查，新手必备！

大家好呀！我是数据库小学妹👋

写SQL的时候，是不是经常遇到这种情况：

• 想把两个字段的内容拼在一起？→ 找函数
• 想把字符串转成大写？→ 找函数
• 想算两个日期的天数差？→ 找函数

每次都要去搜，太浪费时间了。今天我把自己整理的MySQL常用函数速查表分享出来，建议收藏！

一、字符串函数：处理文本数据

💡 常用字符串函数速查

💡 字符串函数实战案例

-- 1. 拼接姓名和邮箱
SELECT CONCAT(first_name, ' ', last_name, ' <', email, '>') as full_info
FROM users;

-- 2. 提取邮箱域名
SELECT email, SUBSTRING(email, INSTR(email, '@') + 1) as domain
FROM users;

-- 3. 格式化电话号码
SELECT CONCAT_WS('-', LEFT(phone, 3), SUBSTRING(phone, 4, 4), RIGHT(phone, 4)) as formatted_phone
FROM users;

-- 4. 统计用户名长度
SELECT username, LENGTH(username) as username_length
FROM users
ORDER BY username_length DESC;

-- 5. 替换敏感信息
SELECT CONCAT(LEFT(email, 3), '***', SUBSTRING(email, INSTR(email, '@'))) as masked_email
FROM users;

二、数学函数

💡 常用数学函数速查

💡 数学函数实战案例

-- 1. 计算订单折扣价
SELECT 
    product_name,
    price,
    discount_rate,
    ROUND(price * (1 - discount_rate), 2) as discounted_price
FROM products;

-- 2. 统计订单金额分布
SELECT 
    FLOOR(amount / 100) * 100 as amount_range,
    COUNT(*) as order_count
FROM orders
GROUP BY FLOOR(amount / 100)
ORDER BY amount_range;

-- 3. 随机抽取10个用户
SELECT * FROM users 
ORDER BY RAND() 
LIMIT 10;

-- 4. 计算商品评分（四舍五入到1位小数）
SELECT 
    product_name,
    AVG(rating) as avg_rating,
    ROUND(AVG(rating), 1) as avg_rating_rounded
FROM product_reviews
GROUP BY product_id;

-- 5. 判断订单金额奇偶
SELECT 
    id,
    amount,
    CASE WHEN MOD(amount, 2) = 0 THEN '偶数' ELSE '奇数' END as parity
FROM orders;

三、日期时间函数（写报表必备）

💡 常用日期函数速查

💡 日期函数实战案例

-- 1. 查询最近7天的订单
SELECT * FROM orders 
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

-- 2. 统计每月订单数
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') as month,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month DESC;

-- 3. 计算用户注册天数
SELECT 
    username,
    created_at,
    DATEDIFF(CURDATE(), created_at) as days_since_register
FROM users;

-- 4. 格式化生日
SELECT 
    name,
    birthday,
    DATE_FORMAT(birthday, '%m月%d日') as birthday_formatted
FROM users;

-- 5. 查询本周订单
SELECT * FROM orders 
WHERE YEARWEEK(order_date, 1) = YEARWEEK(CURDATE(), 1);

四、聚合函数

💡 常用聚合函数速查

💡 聚合函数实战案例

-- 1. 统计每个用户的订单数和总金额
SELECT 
    user_id,
    COUNT(*) as order_count,
    SUM(amount) as total_amount,
    AVG(amount) as avg_amount,
    MAX(amount) as max_amount,
    MIN(amount) as min_amount
FROM orders
GROUP BY user_id;

-- 2. 统计每个类别的商品数量和平均价格
SELECT 
    category_id,
    COUNT(*) as product_count,
    AVG(price) as avg_price,
    MAX(price) as max_price,
    MIN(price) as min_price
FROM products
GROUP BY category_id;

-- 3. 拼接每个用户的订单ID
SELECT 
    user_id,
    GROUP_CONCAT(order_id ORDER BY order_date DESC SEPARATOR ', ') as order_ids
FROM orders
GROUP BY user_id;

-- 4. 统计每个年龄段的用户数
SELECT 
    FLOOR(age / 10) * 10 as age_group,
    COUNT(*) as user_count
FROM users
GROUP BY FLOOR(age / 10)
ORDER BY age_group;

五、条件函数

💡 常用条件函数速查

💡 条件函数实战案例

-- 1. 根据分数评级
SELECT 
    student_name,
    score,
    CASE 
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN '良好'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END as grade
FROM scores;

-- 2. 处理空值
SELECT 
    username,
    IFNULL(email, '未填写') as email,
    IFNULL(phone, '未填写') as phone
FROM users;

-- 3. 计算订单状态
SELECT 
    id,
    amount,
    status,
    IF(status = 'paid', '已支付', '未支付') as status_text
FROM orders;

-- 4. 优先使用第一个非空联系方式
SELECT 
    username,
    COALESCE(phone1, phone2, phone3, '无联系方式') as contact_phone
FROM users;

-- 5. 计算折扣（满1000打9折）
SELECT 
    product_name,
    price,
    IF(price >= 1000, price * 0.9, price) as final_price
FROM products;

六、类型转换函数

💡 常用类型转换函数速查

💡 类型转换实战案例

-- 1. 字符串转数字
SELECT 
    product_name,
    price_str,
    CAST(price_str AS DECIMAL(10,2)) as price_num
FROM products;

-- 2. 数字转字符串
SELECT 
    id,
    amount,
    CONCAT('¥', CAST(amount AS CHAR)) as amount_text
FROM orders;

-- 3. 日期字符串转日期类型
SELECT 
    order_date_str,
    CAST(order_date_str AS DATE) as order_date
FROM orders_temp;

-- 4. 时间戳转日期时间
SELECT 
    timestamp_col,
    FROM_UNIXTIME(timestamp_col) as datetime_col
FROM logs;

-- 5. 处理混合类型比较
SELECT * FROM products 
WHERE CAST(price AS DECIMAL(10,2)) > 1000;

七、新手避坑指南（⚠️常见错误）

八、函数速查表完整版

九、学习心得

📝 核心总结（三句话）：

1️⃣ 函数是SQL的灵魂：掌握常用函数，让查询更强大

2️⃣ 分类记忆更高效：按功能分类，快速查找

3️⃣ 实践出真知：多写多练，熟能生巧

👋 我是数据库小学妹一个用设计师思维学数据库的转行人。我们一起，把复杂的技术变得简单有趣！💕—

本文为个人学习整理，函数均基于​MySQL​​ 8.0版本，其他数据库可能略有差异。