SQL查询写法顺序SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT执行顺序FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT一、基础查询语句/函数作用用法示例SELECT查询指定列SELECT 列1, 列2 FROM 表SELECT customer_name, city FROM customersDISTINCT去重SELECT DISTINCT 列 FROM 表SELECT DISTINCT city FROM customersLIMIT限制返回行数... LIMIT 数字SELECT * FROM orders LIMIT 3WHERE条件筛选WHERE 列 运算符 值WHERE amount 1000AND / OR多条件组合WHERE 条件1 AND 条件2WHERE city 北京 AND amount 1000IN匹配多个值之一WHERE 列 IN (值1, 值2)WHERE city IN (北京, 上海)BETWEEN范围匹配含边界WHERE 列 BETWEEN 小值 AND 大值WHERE amount BETWEEN 100 AND 1000LIKE模糊匹配WHERE 列 LIKE %关键词%WHERE customer_name LIKE 张%IS NULL/IS NOT NULL判断是否为空WHERE 列 IS NULLWHERE salary IS NULL⚠️ 易错提醒IS只能配 NULL等值判断用不用IS如city 北京√city IS 北京×OR两侧都要写完整条件city 北京 OR city 广州√city 北京 OR 广州×BETWEEN包含两端边界值二、排序与分组聚合语句/函数作用用法示例ORDER BY排序ORDER BY 列 ASC/DESCORDER BY amount DESCGROUP BY分组GROUP BY 列1, 列2GROUP BY customer_idCOUNT(*)统计行数COUNT(*)SELECT COUNT(*) FROM ordersCOUNT(DISTINCT 列)去重统计COUNT(DISTINCT 列)COUNT(DISTINCT customer_id)SUM(列)求和SUM(amount)SUM(amount)AVG(列)求平均AVG(salary)AVG(salary)MAX(列) / MIN(列)最大/最小值MAX(order_date)MAX(order_date)HAVING分组后筛选HAVING 聚合条件HAVING SUM(amount) 1000⚠️ 易错提醒GROUP BY 规则SELECT 中每个非聚合列都必须放进 GROUP BY记法「每个XX的…」→ GROUP BY XX「的」后面的是聚合结果不是分组依据别名能用在哪ORDER BY✅、HAVING✅、GROUP BY❌子句顺序FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMITCOUNT(*)不会返回 NULL不需要包 IFNULL只要用聚合函数就要分组且SELECT 中每个非聚合列都必须放进分组WHERE不能放聚合函数 → 用HAVINGORDER BY 只能写一次多个排序字段用逗号分隔三、连接查询语句作用用法示例INNER JOIN只返回匹配的行表A JOIN 表B ON 条件orders o JOIN customers c ON o.customer_id c.customer_idLEFT JOIN左表全保留右表无匹配为 NULL表A LEFT JOIN 表B ON 条件customers c LEFT JOIN orders o ON c.customer_id o.customer_id⚠️ 易错提醒JOIN后面写ON→ON c.customer_id o.customer_id√不要用WHERE×左表选择从主体出发查询所有客户→ customers 放左边四、子查询类型作用用法示例WHERE 子查询·单值返回一个值供比较WHERE 列 (SELECT AVG(...))WHERE amount (SELECT AVG(amount) FROM orders)WHERE 子查询·列表返回一列值供 IN 判断WHERE 列 IN (SELECT ...)WHERE customer_id IN (SELECT customer_id FROM orders)FROM 子查询把查询结果当临时表FROM (SELECT ...) AS 别名FROM (SELECT SUM(amount) FROM orders GROUP BY customer_id) AS t⚠️ 易错提醒FROM 子查询必须起别名FROM (子查询) AS tFROM 子查询可以继续 JOIN 其他表customers c LEFT JOIN (子查询) t ON ...嵌套子查询从内往外写先确认内部结果对不对五、条件分支语句作用用法示例CASE WHENSQL 里的 if-elseCASE WHEN 条件 THEN 结果 ELSE 默认 ENDCASE WHEN amount 5000 THEN 高 ELSE 低 ENDCASE WHEN GROUP BY分组后的条件判断CASE WHEN SUM(列) 值 THEN ...CASE WHEN SUM(amount) 5000 THEN VIP ELSE 普通 END⚠️ 易错提醒CASE WHEN 条件从严格到宽松VIP 普通 低消有 GROUP BY 时条件里用WHEN SUM(amount) 5000不是WHEN amount 5000别名只写在END后面... END AS 等级√AS 等级 ... END×CASE WHEN 前一个条件不满足时自动去匹配下一条件直接case when salary5000 then 结果 when salary2000 then 结果 else 结果六、数值函数函数作用用法示例ROUND(数值, 位数)四舍五入ROUND(列, 小数位数)ROUND(AVG(salary), 2)ROUND 负数精度整数位四舍五入ROUND(列, -位数)ROUND(5678, -2)→ 5700⚠️ 易错提醒先 AVG 再 ROUNDROUND(AVG(...), 2)√AVG(ROUND(...))×ORDER BY 里可以用别名ORDER BY 平均工资√GROUP BY 里不可以用别名七、日期函数函数作用用法示例DATE_FORMAT(日期, 格式)日期格式化DATE_FORMAT(order_date, %Y-%m)DATE_FORMAT(order_date, %Y-%m)→ 2026-01NOW()当前时间NOW()WHERE order_date DATE_SUB(NOW(), INTERVAL 30 DAY)DATE_SUB(日期, INTERVAL N 单位)日期加减DATE_SUB(NOW(), INTERVAL 30 DAY)当前时间往前推30天格式符%Y四位年份、%m两位月份、%d两位日期、%m月中文格式也可用八、NULL 处理函数作用用法示例IFNULL(值, 默认)NULL 替换两个参数IFNULL(列, 0)IFNULL(SUM(amount), 0)COALESCE(值1, 值2, ...)返回第一个非 NULL多个参数COALESCE(列1, 列2, 保底)COALESCE(salary, bonus, 0)九、字符串函数函数作用用法示例CONCAT(a, b, ...)拼接字符串CONCAT(列, 文本, 列)CONCAT(customer_name, 来自, city)SUBSTRING(字符串, 起始, 长度)截取子串SUBSTRING(email, 1, 5)SUBSTRING(email, 1, LOCATE(, email)-1)REPLACE(字符串, 旧, 新)替换字符REPLACE(email, qq.com, company.com)REPLACE(email, qq.com, company.com)LENGTH(字符串)字符数LENGTH(你好)→ 2LENGTH(customer_name)UPPER(字符串) / LOWER(字符串)大小写转换UPPER(email)UPPER(SUBSTRING(email, 1, 3))TRIM(字符串)去除首尾空格TRIM( abc )→ abcTRIM(city)LOCATE(要找的, 从哪里找)返回位置LOCATE(, email)→ 9LOCATE(, email)⚠️ 易错提醒列名不加引号文本加引号CONCAT(customer_name, 文本, city)√函数可以嵌套从内往外执行UNION 核心总结语句作用注意UNION合并 SELECT 结果 去重列数必须相同性能比 UNION ALL 稍差UNION ALL合并 SELECT 结果不去重性能更好不需要去重时优先用固定文本 AS 列名给每组数据打标签SELECT 高消费 AS 等级UNION 的主要使用场景合并不同表的结构相同的数据给每组数据打不同标签再合并分类报表不能用一个 WHERE 或 JOIN 简单替代时才值得用