欢迎来到MySQL系列教程的第4天今天我们将学习条件查询和排序这是SQL中最常用的操作。掌握了WHERE、ORDER BY和LIMIT你就能从海量数据中精准找到需要的信息。让我们开始吧文章目录一、写在前面二、WHERE子句详解2.1 比较运算符2.2 逻辑运算符2.3 IN和NOT IN2.4 BETWEEN范围查询2.5 LIKE模糊查询三、NULL值处理3.1 IS NULL vs NULL3.2 NULL相关函数3.3 NULL值排序四、ORDER BY排序4.1 单列排序4.2 多列排序4.3 FIELD自定义排序4.4 表达式排序五、LIMIT分页5.1 基础分页5.2 带排序的分页5.3 分页公式六、实战电商系统的各种查询场景6.1 用户相关查询6.2 商品相关查询6.3 订单相关查询6.4 综合查询七、踩坑提醒7.1 LIKE %xxx%导致索引失效7.2 类型转换导致索引失效7.3 OR条件导致索引失效八、面试高频考点考点1WHERE和HAVING的区别考点2LIMIT 1000000,10的性能问题考点3如何优化ORDER BY考点4IS NULL和 NULL的区别九、总结十、下一步预告十一、参考资料互动话题一、写在前面在实际工作中我们很少直接查询全表数据而是需要根据各种条件筛选。今天的内容包括WHERE子句精确筛选数据NULL值处理避免常见陷阱ORDER BY排序让数据有序呈现LIMIT分页大数据量的分页显示二、WHERE子句详解WHERE子句用于筛选满足条件的记录是SELECT语句中最常用的子句。2.1 比较运算符-- 等于 SELECT*FROMusersWHEREstatus1;-- 不等于 ! 或 SELECT*FROMusersWHEREstatus!0;SELECT*FROMusersWHEREstatus0;-- 大于、小于SELECT*FROMproductsWHEREprice5000;SELECT*FROMproductsWHEREstock10;-- 大于等于、小于等于SELECT*FROMordersWHEREtotal_amount10000;SELECT*FROMproductsWHEREprice1000;-- 范围查询等同于 AND SELECT*FROMproductsWHEREpriceBETWEEN1000AND5000;2.2 逻辑运算符-- AND同时满足多个条件SELECT*FROMproductsWHEREprice1000ANDstock0ANDstatus1;-- OR满足任一条件SELECT*FROMproductsWHEREcategory手机ORcategory电脑;-- NOT取反SELECT*FROMusersWHERENOTstatus0;-- 混合使用注意优先级NOT AND ORSELECT*FROMproductsWHERE(category手机ORcategory电脑)ANDprice10000ANDstatus1;2.3 IN和NOT IN-- IN在指定集合中SELECT*FROMproductsWHEREcategoryIN(手机,电脑,平板);-- 等同于SELECT*FROMproductsWHEREcategory手机ORcategory电脑ORcategory平板;-- NOT IN不在指定集合中SELECT*FROMordersWHEREstatusNOTIN(3,4);-- 查询未完成的订单排除已完成和已取消-- 子查询配合INSELECT*FROMusersWHEREidIN(SELECTuser_idFROMordersWHEREtotal_amount10000);2.4 BETWEEN范围查询-- 数值范围SELECT*FROMproductsWHEREpriceBETWEEN1000AND5000;-- 日期范围SELECT*FROMordersWHEREcreated_atBETWEEN2024-01-01AND2024-01-31;-- 注意BETWEEN包含边界值-- 等同于SELECT*FROMordersWHEREcreated_at2024-01-01ANDcreated_at2024-01-31;-- NOT BETWEENSELECT*FROMproductsWHEREpriceNOTBETWEEN100AND1000;2.5 LIKE模糊查询-- % 匹配任意多个字符SELECT*FROMproductsWHEREnameLIKE%iPhone%;-- 包含iPhoneSELECT*FROMusersWHEREusernameLIKEzhang%;-- 以zhang开头SELECT*FROMusersWHEREemailLIKE%gmail.com;-- 以gmail.com结尾-- _ 匹配单个字符SELECT*FROMproductsWHEREnameLIKEiPhone _;-- iPhone 一个字符SELECT*FROMusersWHEREusernameLIKEuser00_;-- user001, user002等-- 转义特殊字符SELECT*FROMproductsWHEREnameLIKE%\%%;-- 包含%符号SELECT*FROMproductsWHEREnameLIKE%\_%;-- 包含_符号三、NULL值处理3.1 IS NULL vs NULL这是MySQL中最常见的错误之一-- 错误 NULL 永远返回空结果SELECT*FROMusersWHEREphoneNULL;-- 错误-- 正确使用 IS NULLSELECT*FROMusersWHEREphoneISNULL;-- 正确使用 IS NOT NULLSELECT*FROMusersWHEREphoneISNOTNULL;原因NULL表示未知任何与NULL的比较结果都是NULL假必须使用IS NULL或IS NOT NULL。3.2 NULL相关函数-- IFNULL如果为NULL则返回默认值SELECTusername,IFNULL(phone,未绑定)ASphone_displayFROMusers;-- COALESCE返回第一个非NULL值SELECTusername,COALESCE(phone,email,无联系方式)AScontactFROMusers;-- NULLIF如果相等则返回NULLSELECTNULLIF(1,1);-- 返回NULLSELECTNULLIF(1,2);-- 返回13.3 NULL值排序-- MySQL中NULL默认排在最前面升序SELECT*FROMusersORDERBYphone;-- NULL在前-- 让NULL排在最后SELECT*FROMusersORDERBYphoneISNULL,phone;-- 或者使用IFNULL转换SELECT*FROMusersORDERBYIFNULL(phone,ZZZZZ);四、ORDER BY排序4.1 单列排序-- 升序默认SELECT*FROMproductsORDERBYprice;SELECT*FROMproductsORDERBYpriceASC;-- 降序SELECT*FROMproductsORDERBYpriceDESC;-- 按日期排序SELECT*FROMordersORDERBYcreated_atDESC;-- 最新的在前4.2 多列排序-- 先按状态排序再按价格排序SELECT*FROMproductsORDERBYstatusDESC,priceASC;-- 先按分类排序再按价格降序最后按ID升序SELECT*FROMproductsORDERBYcategory,priceDESC,id;4.3 FIELD自定义排序有时我们需要按照自定义的顺序排序-- 按状态自定义顺序已支付(1) 待支付(0) 已发货(2) 已完成(3) 已取消(4)SELECT*FROMordersORDERBYFIELD(status,1,0,2,3,4);-- 按指定ID顺序排序SELECT*FROMproductsWHEREidIN(3,1,2)ORDERBYFIELD(id,3,1,2);-- 结果按3,1,2的顺序4.4 表达式排序-- 按价格区间排序先显示便宜的SELECT*,CASEWHENprice1000THEN1WHENprice5000THEN2ELSE3ENDASprice_levelFROMproductsORDERBYprice_level,price;五、LIMIT分页5.1 基础分页-- 只返回前10条SELECT*FROMproductsLIMIT10;-- 返回第11-20条第2页每页10条SELECT*FROMproductsLIMIT10OFFSET10;-- 或简写SELECT*FROMproductsLIMIT10,10;-- 从第10条开始取10条5.2 带排序的分页-- 按价格排序后分页SELECT*FROMproductsORDERBYpriceASCLIMIT20OFFSET40;-- 第3页每页20条-- 按创建时间倒序分页最新数据在前SELECT*FROMordersORDERBYcreated_atDESCLIMIT10OFFSET0;-- 第1页5.3 分页公式-- 通用分页公式-- 第page页每页pageSize条-- OFFSET (page - 1) * pageSize-- 第5页每页20条SETpage5;SETpageSize20;SEToffset(page-1)*pageSize;SELECT*FROMproductsORDERBYidLIMITpageSizeOFFSEToffset;六、实战电商系统的各种查询场景6.1 用户相关查询-- 查询已绑定手机号的活跃用户SELECTid,username,phone,emailFROMusersWHEREstatus1ANDphoneISNOTNULLORDERBYcreated_atDESCLIMIT20;-- 查询用户名包含zhang的用户SELECT*FROMusersWHEREusernameLIKE%zhang%;-- 查询注册时间在2024年1月的用户SELECT*FROMusersWHEREcreated_at2024-01-01ANDcreated_at2024-02-01;6.2 商品相关查询-- 查询上架且库存充足的商品按价格排序SELECTid,name,price,stockFROMproductsWHEREstatus1ANDstock0ORDERBYpriceASCLIMIT10;-- 查询价格在1000-5000之间的手机或电脑SELECT*FROMproductsWHEREpriceBETWEEN1000AND5000ANDcategoryIN(手机,电脑)ORDERBYpriceDESC;-- 查询库存不足的商品需要补货SELECT*FROMproductsWHEREstock10ORDERBYstockASC;6.3 订单相关查询-- 查询最近7天的订单SELECT*FROMordersWHEREcreated_atDATE_SUB(NOW(),INTERVAL7DAY)ORDERBYcreated_atDESC;-- 查询金额大于10000的已支付订单SELECT*FROMordersWHEREtotal_amount10000ANDstatus1ORDERBYtotal_amountDESC;-- 查询特定用户的所有订单SELECT*FROMordersWHEREuser_id1ORDERBYcreated_atDESCLIMIT10;-- 查询待支付或已支付的订单按状态优先级排序SELECT*FROMordersWHEREstatusIN(0,1)ORDERBYFIELD(status,1,0),created_atDESC;6.4 综合查询-- 查询高价值用户的订单订单金额大于平均值的订单SELECTo.*,u.usernameFROMorders oJOINusers uONo.user_idu.idWHEREo.total_amount(SELECTAVG(total_amount)FROMorders)ORDERBYo.total_amountDESCLIMIT10;-- 查询每个用户的最新订单SELECTo1.*FROMorders o1WHEREo1.created_at(SELECTMAX(created_at)FROMorders o2WHEREo2.user_ido1.user_id)ORDERBYo1.user_id;七、踩坑提醒7.1 LIKE %xxx%导致索引失效-- 有索引的查询使用索引SELECT*FROMusersWHEREusernamezhangsan;-- 用索引SELECT*FROMusersWHEREusernameLIKEzhang%;-- 用索引前缀匹配-- 索引失效的查询全表扫描SELECT*FROMusersWHEREusernameLIKE%zhang;-- 全表扫描SELECT*FROMusersWHEREusernameLIKE%zhang%;-- 全表扫描解决方案避免使用%xxx%模糊查询使用全文索引FULLTEXT使用搜索引擎Elasticsearch使用反向索引或分词技术7.2 类型转换导致索引失效-- 表结构phone CHAR(11)-- 错误类型转换导致索引失效SELECT*FROMusersWHEREphone13800138001;-- 数字会转换-- 正确使用字符串SELECT*FROMusersWHEREphone13800138001;-- 字符串用索引7.3 OR条件导致索引失效-- 可能不走索引取决于优化器SELECT*FROMproductsWHEREcategory手机ORprice1000;-- 优化方案1使用UNIONSELECT*FROMproductsWHEREcategory手机UNIONALLSELECT*FROMproductsWHEREprice1000;-- 优化方案2使用IN如果条件适合SELECT*FROMproductsWHEREcategoryIN(手机,电脑);八、面试高频考点考点1WHERE和HAVING的区别答案特性WHEREHAVING执行时机分组前过滤分组后过滤作用对象原始数据行分组后的结果可用条件原始列、表达式聚合函数、别名性能更好先过滤稍差后过滤-- WHERE过滤原始行SELECTcategory,AVG(price)ASavg_priceFROMproductsWHEREstatus1-- 先过滤上架商品GROUPBYcategory;-- HAVING过滤分组结果SELECTcategory,AVG(price)ASavg_priceFROMproductsGROUPBYcategoryHAVINGAVG(price)5000;-- 过滤平均价格大于5000的分类考点2LIMIT 1000000,10的性能问题答案深度分页性能很差因为MySQL需要先扫描1000010行再返回最后10行。解决方案-- 低效深度分页SELECT*FROMordersLIMIT1000000,10;-- 优化方案1使用覆盖索引SELECT*FROMorders oJOIN(SELECTidFROMordersLIMIT1000000,10)tmpONo.idtmp.id;-- 优化方案2使用范围查询推荐SELECT*FROMordersWHEREid1000000ORDERBYidLIMIT10;-- 优化方案3记录上次位置SELECT*FROMordersWHEREcreated_at上次最后一条的时间ORDERBYcreated_atDESCLIMIT10;考点3如何优化ORDER BY答案使用索引覆盖排序字段避免使用SELECT *只查询需要的列减少排序的数据量先用WHERE过滤考虑使用内存排序sort_buffer_size-- 有索引的排序filesort在内存中完成SELECTid,name,priceFROMproductsORDERBYprice;-- 优化添加复合索引ALTERTABLEproductsADDINDEXidx_status_price(status,price);-- 查询时使用索引覆盖SELECTid,name,priceFROMproductsWHEREstatus1ORDERBYprice;-- 使用索引排序考点4IS NULL和 NULL的区别答案IS NULL是标准SQL语法用于判断是否为NULL NULL是错误的写法任何值与NULL比较都返回NULL假必须使用IS NULL或IS NOT NULL判断NULL值九、总结今天我们学习了WHERE子句比较运算符、逻辑运算符、IN、BETWEEN、LIKENULL处理IS NULL、IFNULL、COALESCEORDER BY排序单列、多列、FIELD自定义排序LIMIT分页基础分页、深度分页优化实战电商系统的各种查询场景核心要点LIKE %xxx%会导致索引失效必须用IS NULL判断NULL值深度分页需要优化WHERE和HAVING的使用场景不同十、下一步预告Day5MySQL聚合函数与分组我们将学习COUNT/SUM/AVG/MAX/MIN聚合函数GROUP BY分组统计HAVING过滤分组复杂统计查询实战十一、参考资料MySQL 8.0官方文档 - SELECT语句高性能MySQL第4版- 查询性能优化互动话题你在工作中遇到过哪些慢查询问题是怎么优化的你们项目中的分页是怎么实现的有没有遇到深度分页的性能问题对于LIKE模糊查询你们有什么好的解决方案如果觉得有帮助请点赞收藏关注我们Day5见