SQL 单表操作全解

SQL 单表操作全解本文所有语法和实例均基于开发最常用的users用户表表结构完全符合生产规范后续所有操作均围绕此表展开CREATETABLEIFNOTEXISTSusers(idINTUNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT用户ID主键,usernameVARCHAR(50)NOTNULLCOMMENT用户名,emailVARCHAR(100)DEFAULTCOMMENT用户邮箱,passwordCHAR(32)NOTNULLCOMMENT加密密码,ageTINYINTUNSIGNEDDEFAULT0COMMENT年龄,cityVARCHAR(20)DEFAULTCOMMENT所在城市,genderTINYINTDEFAULT0COMMENT性别0-未知1-男2-女,is_deleteTINYINTNOTNULLDEFAULT0COMMENT是否删除0-未删除1-已删除逻辑删除,create_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT创建时间,update_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT更新时间,-- 单表索引后续优化章节详解PRIMARYKEY(id),UNIQUEKEYuk_username(username),KEYidx_city_age(city,age),KEYidx_create_time(create_time))ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT用户表;单表操作的本质就是对一张二维表的两个维度的精准控制以及基于筛选结果的附加加工列维度垂直筛选通过SELECT控制要查询的字段只提取需要的列行维度水平筛选通过WHERE控制要查询的记录只过滤符合条件的行结果加工对筛选后的结果做排序、分页、去重等格式化处理数据计算对筛选后的结果做聚合、分组、统计等数值计算数据操纵对表中的数据进行新增、更新、删除操作。列维度筛选SELECT 语法全解SELECT是单表操作的入口核心作用是精准提取需要的字段生产环境有一条铁律严禁使用SELECT *查询全字段仅查询业务需要的列避免敏感字段泄露、性能损耗和表结构变更带来的业务报错。基础指定字段查询-- 语法SELECT 字段1, 字段2, 字段3 FROM 表名;-- 实例查询用户的非敏感基础信息忽略密码等敏感字段SELECTid,username,email,age,cityFROMusers;字段别名AS用于简化字段名、解决字段重名、自定义返回列名提升结果可读性别名支持中文生产建议用英文。-- 语法SELECT 字段 AS 别名 FROM 表名;-- 实例给字段起业务别名SELECTidASuser_id,usernameASuser_name,emailASuser_email,ageASuser_ageFROMusers;去重查询DISTINCT用于剔除查询结果中的重复行核心规则DISTINCT对查询的所有字段组合去重而非单个字段。-- 语法SELECT DISTINCT 字段1, 字段2 FROM 表名;-- 实例1查询表中所有不重复的城市单字段去重SELECTDISTINCTcityFROMusers;-- 实例2查询城市年龄的不重复组合多字段组合去重SELECTDISTINCTcity,ageFROMusers;常量列与计算列用于给结果补充固定值或对字段做简单的数值计算无需修改表结构即可实现业务需求。-- 实例1补充常量列给所有用户标记固定的用户类型SELECTid,username,正式用户ASuser_type-- 常量列所有行均为该固定值FROMusers;-- 实例2计算列对数值字段做简单计算SELECTid,username,age,age1ASnext_year_age,-- 计算用户明年的年龄2026-ageASbirth_year-- 计算用户出生年份FROMusers;行维度筛选WHERE 语法全解WHERE是单表精准查询的核心作用是按条件过滤符合业务规则的行避免全表扫描是开发中使用频率最高的语法之一。常用条件运算符运算符类型具体运算符作用说明开发高频度比较运算符、、、、、!/数值、字符串、日期的等值 / 不等值比较★★★★★逻辑运算符AND、OR、NOT多条件组合AND优先级高于OR可用括号调整优先级★★★★★范围运算符BETWEEN ... AND ...、IN区间匹配、集合匹配★★★★☆模糊匹配LIKE模糊查询%匹配任意字符_匹配单个字符★★★★☆空值判断IS NULL、IS NOT NULL空值判断NULL 不能用/!判断★★★★☆-- 1. 基础等值查询查询指定ID的用户SELECTid,username,emailFROMusersWHEREid100;-- 2. 多条件组合查询查询北京的成年、未删除的男性用户-- 注意AND优先级高于OR复杂条件必须用括号明确执行顺序SELECTid,username,age,cityFROMusersWHEREage18ANDcity北京ANDgender1ANDis_delete0;-- 3. 范围查询查询年龄在18-30岁之间的用户SELECTid,username,ageFROMusersWHEREageBETWEEN18AND30;-- 4. 集合匹配查询北上广深四个城市的用户SELECTid,username,cityFROMusersWHEREcityIN(北京,上海,广州,深圳);-- 5. 模糊查询查询姓张的用户前缀匹配可命中索引SELECTid,usernameFROMusersWHEREusernameLIKE张%;-- 6. 模糊查询查询用户名包含「三」的用户中缀匹配无法命中索引大表慎用SELECTid,usernameFROMusersWHEREusernameLIKE%三%;-- 7. 空值判断查询未填写邮箱的用户SELECTid,usernameFROMusersWHEREemailISNULLORemail;-- 8. 取反查询查询除北京之外的用户SELECTid,username,cityFROMusersWHERENOTcity北京;必避坑NULL值判断必须用IS NULL/IS NOT NULLNULL 、NULL NULL的结果均为UNKNOWN不会匹配到任何行AND优先级高于OR例如WHERE a1 OR b2 AND c3实际执行逻辑是a1 OR (b2 AND c3)如需调整顺序必须用括号包裹模糊查询%xxx%中缀匹配无法命中索引大表查询会触发全表扫描生产环境慎用。结果集加工排序、分页、去重对SELECT WHERE筛选后的结果做格式化处理满足业务的展示、分页、排序需求是列表类接口的必用语法。排序ORDER BY用于对结果集按指定字段排序ASC为升序默认值可省略DESC为降序支持多字段组合排序。-- 语法SELECT 字段 FROM 表名 [WHERE 条件] ORDER BY 字段1 ASC/DESC, 字段2 ASC/DESC;-- 实例1按用户创建时间倒序查询最新注册的用户SELECTid,username,create_timeFROMusersWHEREis_delete0ORDERBYcreate_timeDESC;-- 实例2多字段排序先按城市升序同城市内按年龄降序SELECTid,username,city,ageFROMusersWHEREis_delete0ORDERBYcityASC,ageDESC;分页LIMIT用于限制返回的行数避免一次性查询全表海量数据导致内存溢出是后台列表、前端分页接口的核心语法。-- 语法LIMIT 偏移量, 每页条数 偏移量计算公式(页码-1) * 每页条数SELECT字段FROM表名[WHERE条件][ORDERBY排序]LIMIT偏移量,条数;-- 实例用户列表第2页每页10条数据按注册时间倒序SELECTid,username,city,create_timeFROMusersWHEREis_delete0ORDERBYcreate_timeDESCLIMIT10,10;-- 第2页(2-1)*1010偏移量10取10条-- 简化语法查询前10条数据无需偏移量SELECTid,usernameFROMusersLIMIT10;分页优化提示大表深分页如LIMIT 100000, 10性能极差优化方案用主键过滤替代偏移量示例-- 优化前深分页需扫描100010行数据SELECTid,usernameFROMusersORDERBYidDESCLIMIT100000,10;-- 优化后主键过滤仅扫描10行数据SELECTid,usernameFROMusersWHEREid100000ORDERBYidDESCLIMIT10;单表聚合与分组统计用于对筛选后的结果做数值计算、分组统计是报表、数据看板、业务统计类需求的核心语法。常用聚合函数聚合函数会对多行数据做计算最终返回单个结果聚合函数会自动忽略NULL值的行。函数核心作用开发高频度COUNT(*)统计结果集的总行数包含NULL值行、空值行★★★★★COUNT(字段)统计指定字段非空的行数排除NULL值行★★★★☆SUM(字段)对数值字段求和非数值字段返回 0★★★★☆AVG(字段)对数值字段求平均值排除NULL值行★★★★☆MAX(字段)求字段的最大值支持数值、日期、字符串★★★★☆MIN(字段)求字段的最小值支持数值、日期、字符串★★★★☆-- 实例1统计平台总用户数未删除SELECTCOUNT(*)AStotal_userFROMusersWHEREis_delete0;-- 实例2统计北京用户的平均年龄、最大年龄、最小年龄SELECTAVG(age)ASavg_age,MAX(age)ASmax_age,MIN(age)ASmin_ageFROMusersWHEREcity北京ANDis_delete0;-- 实例3统计已填写邮箱的用户数量SELECTCOUNT(email)ASemail_user_countFROMusersWHEREemailISNOTNULLANDemail!;分组统计GROUP BY将结果集按指定字段分组对每个分组单独做聚合计算实现「分维度统计」的业务需求。MySQL 5.7 默认开启ONLY_FULL_GROUP_BY模式SELECT后的非聚合字段必须全部出现在GROUP BY的分组字段中否则会直接报错。-- 语法SELECT分组字段,聚合函数FROM表名[WHERE行过滤条件]GROUPBY分组字段[ORDERBY排序];-- 实例1统计每个城市的用户数量按用户数倒序SELECTcity,COUNT(*)ASuser_countFROMusersWHEREis_delete0GROUPBYcityORDERBYuser_countDESC;-- 实例2多字段分组统计每个城市性别的用户数量SELECTcity,gender,COUNT(*)ASuser_countFROMusersWHEREis_delete0GROUPBYcity,genderORDERBYcityASC;分组过滤HAVING用于对GROUP BY分组后的结果做过滤是新手最容易和WHERE搞混的语法核心区别如下特性WHEREHAVING执行时机分组前执行先过滤行再分组分组后执行先分组聚合再过滤组聚合函数不支持不能在 WHERE 中使用聚合函数支持核心作用就是过滤聚合结果作用对象表中的行按行过滤分组后的结果集按组过滤-- 语法SELECT分组字段,聚合函数FROM表名[WHERE行过滤条件]GROUPBY分组字段HAVING分组后过滤条件[ORDERBY排序];-- 实例统计每个城市的用户数仅保留用户数大于100的城市按用户数倒序SELECTcity,COUNT(*)ASuser_countFROMusersWHEREis_delete0-- 先过滤掉已删除的用户行过滤GROUPBYcityHAVINGuser_count100-- 再过滤掉用户数不足100的城市组过滤ORDERBYuser_countDESC;-- 实例2统计平均年龄大于25岁的城市SELECTcity,AVG(age)ASavg_ageFROMusersWHEREis_delete0GROUPBYcityHAVINGavg_age25;单表查询完整执行顺序理解执行顺序就能彻底搞懂WHERE和HAVING的区别单表查询的完整执行顺序如下FROM 表名 → 确定要操作的表 WHERE 行过滤条件 → 过滤符合条件的行 GROUP BY 分组字段 → 对过滤后的行进行分组 聚合函数计算 → 对每个分组做聚合计算 HAVING 分组过滤条件 → 对分组后的结果做过滤 SELECT 字段/别名 → 提取最终要返回的列 ORDER BY 排序 → 对最终结果排序 LIMIT 分页 → 限制返回的行数单表数据操纵增删改INSERT/UPDATE/DELETE核心是对单表中的数据进行写入、修改、删除操作生产环境有严格的使用规范必须重点关注。新增数据INSERT单行插入生产环境必须指定插入的字段避免表结构变更如新增字段导致插入报错。-- 语法INSERTINTO表名(字段1,字段2,字段3)VALUES(值1,值2,值3);-- 实例新增单个用户INSERTINTOusers(username,email,password,age,city,gender)VALUES(张三,zhangsanexample.com,e10adc3949ba59abbe56e057f20f883e,25,北京,1);批量插入高性能推荐一次性插入多条数据远高于循环单行插入的性能减少数据库 IO 次数是批量导入数据的首选。-- 语法INSERTINTO表名(字段1,字段2,字段3)VALUES(值1,值2,值3),(值4,值5,值6),(值7,值8,值9);-- 实例批量新增3个用户INSERTINTOusers(username,email,password,age,city,gender)VALUES(李四,lisiexample.com,e10adc3949ba59abbe56e057f20f883e,28,上海,1),(王五,wangwuexample.com,e10adc3949ba59abbe56e057f20f883e,22,广州,2),(赵六,zhaoliuexample.com,e10adc3949ba59abbe56e057f20f883e,30,深圳,1);幂等插入INSERT IGNORE插入时如果唯一键如 username、id重复不会报错直接忽略本次插入避免重复插入导致的业务异常。-- 实例用户名重复则忽略不报错INSERTIGNOREINTOusers(username,email,password,age)VALUES(张三,zhangsan_newexample.com,e10adc3949ba59abbe56e057f20f883e,26);插入或更新ON DUPLICATE KEY UPDATE开发高频语法插入时如果唯一键重复则执行更新操作不重复则执行插入完美实现「有则更新无则新增」的业务需求。-- 实例用户存在则更新邮箱和年龄不存在则新增INSERTINTOusers(id,username,email,password,age)VALUES(1,张三,zhangsan_updateexample.com,e10adc3949ba59abbe56e057f20f883e,26)ONDUPLICATEKEYUPDATEemailVALUES(email),ageVALUES(age),update_timeNOW();更新数据UPDATEUPDATE 必须加 WHERE 条件严禁无 WHERE 的全表更新执行更新前先用SELECT语句确认 WHERE 条件匹配的行数避免误更新大表批量更新必须拆分避免长事务锁表。-- 语法UPDATE表名SET字段1值1,字段2值2[WHERE条件];-- 实例1更新指定ID用户的邮箱和年龄UPDATEusersSETemailzhangsan_newexample.com,age26,update_timeNOW()WHEREid1;-- 实例2批量更新将北京的未成年用户的性别重置为0未知UPDATEusersSETgender0,update_timeNOW()WHEREcity北京ANDage18;删除数据DELETE 逻辑删除DELETE 必须加 WHERE 条件严禁无 WHERE 的全表删除执行删除前先用SELECT语句确认 WHERE 条件匹配的行数避免误删生产环境优先使用逻辑删除而非物理删除避免数据无法恢复。物理删除DELETE直接从表中删除数据删除后无法直接恢复。-- 语法DELETEFROM表名[WHERE条件];-- 实例删除指定ID的用户DELETEFROMusersWHEREid100;逻辑删除生产推荐不实际删除数据而是通过is_delete字段标记数据为已删除状态查询时过滤掉已删除的数据保证数据可追溯、可恢复是互联网业务的通用规范。-- 实例逻辑删除指定ID的用户更新is_delete字段为1UPDATEusersSETis_delete1,update_timeNOW()WHEREid1;-- 业务查询时固定过滤is_delete0只查询未删除的数据SELECTid,usernameFROMusersWHEREis_delete0ANDid1;清空全表TRUNCATETRUNCATETABLEusers;TRUNCATE与DELETE的核心区别特性DELETETRUNCATE条件过滤支持 WHERE不支持直接清空全表事务回滚支持不支持自增主键不重置重置为初始值执行效率大表慢逐行记录日志大表极快触发器会触发不会触发单表操作性能核心索引优化单表查询的性能瓶颈99% 都来自于索引使用不当这里聚焦单表场景下的索引核心知识完全贴合单表操作的优化需求。单表常用索引类型索引类型核心作用单表适用场景主键索引基于主键创建唯一、非空一张表只能有一个主键 id 查询精准定位单行数据普通索引最基础的索引仅提升查询速度WHERE、ORDER BY、GROUP BY 后的高频查询字段唯一索引保证字段值唯一查询性能极高用户名、邮箱等唯一标识字段联合索引基于多个字段创建的索引性能最优多字段组合查询的场景如WHERE city? AND age?单表索引基础操作-- 1. 创建普通索引CREATEINDEXidx_ageONusers(age);-- 2. 创建唯一索引CREATEUNIQUEINDEXuk_emailONusers(email);-- 3. 创建联合索引高区分度字段放最左侧CREATEINDEXidx_city_age_genderONusers(city,age,gender);-- 4. 查看表中所有索引SHOWINDEXFROMusers;-- 5. 删除索引DROPINDEXidx_ageONusers;单表场景索引生效核心规则最左前缀原则联合索引会按创建时的字段顺序从左到右依次匹配遇到范围查询会停止匹配。例如联合索引idx_city_age_gender(city, age, gender)✅ 生效WHERE city北京、WHERE city北京 AND age25、WHERE city北京 AND age25 AND gender1❌ 失效WHERE age25、WHERE gender1、WHERE city LIKE 北% AND age25ORDER BY/GROUP BY的字段尽量匹配索引避免文件排序提升性能精准查询优先使用主键索引、唯一索引性能远高于普通索引。单表查询高频索引失效场景索引字段使用函数、表达式计算WHERE YEAR(create_time) 2026索引字段发生隐式类型转换WHERE phone [phone]phone 是 VARCHAR 类型模糊查询以%开头WHERE username LIKE %三使用NOT、!、反向查询优化器评估全表扫描更快时会放弃索引OR连接非索引字段WHERE username张三 OR age25age 无索引使用IS NOT NULL对低区分度字段查询单表索引最佳实践优先为WHERE、ORDER BY、GROUP BY后的字段创建索引联合索引优先把高区分度字段放在最左侧如 username、city区分度远高于 gender、is_delete单表索引数量控制在 5 个以内避免过多索引影响增删改性能小表数据量 1000 行无需创建索引全表扫描比索引查询更快避免创建冗余索引如已有联合索引idx_city_age(city,age)无需再为 city 单独建索引上线前必须用EXPLAIN查看 SQL 执行计划确认索引是否生效。单表操作规范查询规范- 严禁使用SELECT *必须指定业务需要的字段 - 大表查询必须加 WHERE 条件严禁无过滤的全表查询 - 避免使用SELECT COUNT(*) FROM 大表统计总行数可使用统计表替代 - 禁用SELECT ... FOR UPDATE行锁操作除非强一致性场景且必须控制锁范围。增删改规范- UPDATE、DELETE必须加 WHERE 条件执行前先用 SELECT 确认条件 - 生产环境优先使用逻辑删除而非物理删除 - 大表批量操作必须拆分避免长事务锁表如批量删除 10 万条数据分 10 次每次删除 1 万条 - 严禁在循环中执行单条 INSERT/UPDATE必须使用批量操作。语法规范- 避免在 WHERE 条件中对索引字段做函数计算、类型转换 - 多条件组合时用括号明确执行优先级避免 AND/OR 优先级混乱 - NULL 值判断必须用 IS NULL/IS NOT NULL禁止用 /! 判断 NULL - GROUP BY 语句严格遵守ONLY_FULL_GROUP_BY规则非聚合字段必须出现在 GROUP BY 中。