尧图网站建设 
1. 先搞清楚SQL到底在学什么1.1 你以为的SQL和实际要用的SQL可能不是一回事很多人一听到“SQL基本操作”脑子里浮现的是大学数据库课本里那一堆CREATE、DROP、GRANT、REVOKE。真到了工作中才发现日常打交道最多的其实是查询、插入、更新、删除这四类操作也就是常说的增删改查。至于授权、备份、恢复、调参这些事大部分时候有DBA或运维同事顶着普通开发、数据分析、产品运营能把查询写好就已经能解决工作中绝大多数问题了。我用一个比较直白的分类来帮大家建立整体认知。SQL语言按功能大致可以分成四类分类全称代表命令日常工作占比DQL数据查询语言SELECT最高超过一半DML数据操作语言INSERT、UPDATE、DELETE较高DDL数据定义语言CREATE、ALTER、DROP偶尔用到DCL数据控制语言GRANT、REVOKE很少用到这个表不是想让你背概念而是想告诉你学习精力要花在刀刃上。先把SELECT练得滚瓜烂熟再掌握增删改的基本姿势建表改表了解常见用法权限控制交给专业的人。这跟我带新人的思路一致——我会让新人先花一周时间把各种查询写明白而不是上来就研究怎么建存储过程、怎么调索引。1.2 数据库选型MySQL、SQL Server、SQLite到底学哪个看热搜词就能发现大家的关注点分布在MySQL、SQL Server、SQLite这几个方向上。这里我想先给一个结论SQL基本操作的语法在主流数据库里七八成是通用的。标准SQL的SELECT、WHERE、GROUP BY、ORDER BY、JOIN在MySQL、PostgreSQL、SQL Server、SQLite里写法几乎一样。真正不同的地方主要在函数名、分页写法、自增主键的定义方式这些细节上。比如分页查询MySQL用LIMITSQL Server用OFFSET FETCH或TOPSQLite也支持LIMIT比如字符串拼接MySQL用CONCATSQL Server用加号SQLite用||。这些差异确实烦人但如果你把标准SQL的部分打扎实换数据库也就是查一下函数手册的事。我平时用得最多的是MySQL线上生产环境也是MySQL为主偶尔用SQLite做本地数据处理。所以这篇文章的示例统一用MySQL语法来写涉及SQL Server或SQLite有差异的地方我会单独标注说明。这样不管你在哪个数据库上练习思路都能跟得上。2. 建库建表所有SQL操作的地基2.1 建库前先想清楚字符集和排序规则我第一次接手项目的时候上来就CREATE DATABASE连字符集都没指定结果存中文一切正常后来做模糊查询时发现明明条件是对的就是查不出来。排查了半天发现是建库时用了默认的latin1字符集中文被存成了乱码。从那以后我建库一定显式指定字符集。以MySQL为例一个稳妥的建库语句长这样CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;utf8mb4是utf8的超集能存emoji也能存生僻字。虽然你可能觉得用不到但线上环境一旦定了字符集后期想改牵连很大所以一步到位更省心。COLLATE里的ci是case insensitive的缩写意思是排序和比较时不区分大小写这也是比较符合业务直觉的配置。如果你在SQL Server上对应的概念是实例排序规则比如Chinese_PRC_CI_AS思路是一样的。2.2 用学生、课程、成绩三张表走通所有基本操作学SQL基本操作最忌讳的就是只看不练。为了让你后面每个案例都有场景可以跑我设计一套特别经典的三张表学生表、课程表、成绩表。这套表我在不同公司带新人时用过很多次知识点覆盖很全面。学生表保存学生基础信息CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 学生ID, stu_no VARCHAR(20) NOT NULL UNIQUE COMMENT 学号, stu_name VARCHAR(50) NOT NULL COMMENT 姓名, gender CHAR(1) DEFAULT 1 COMMENT 性别1男 2女, birth_date DATE NULL COMMENT 出生日期, phone VARCHAR(20) NULL COMMENT 手机号, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间 ) COMMENT 学生表;课程表保存课程信息CREATE TABLE course ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 课程ID, course_no VARCHAR(20) NOT NULL UNIQUE COMMENT 课程编号, course_name VARCHAR(100) NOT NULL COMMENT 课程名称, credit DECIMAL(3,1) DEFAULT 0 COMMENT 学分 ) COMMENT 课程表;成绩表保存学生每门课的考试成绩CREATE TABLE score ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 主键, stu_id INT NOT NULL COMMENT 学生ID, course_id INT NOT NULL COMMENT 课程ID, score DECIMAL(5,1) NOT NULL COMMENT 成绩满分100, exam_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 考试时间, UNIQUE KEY uk_stu_course (stu_id, course_id) ) COMMENT 成绩表;可能有人问为什么要建三个表不是两个甚至一个就够了吗。直接建一张大宽表把学生信息和成绩放一起写起来确实简单。但这样做会有两个问题一是学生信息重复存储一个学生选了五门课姓名性别就存了五遍浪费空间不说改一次要改多行二是数据更新容易不一致改了一行漏了一行后面对账就头疼。拆成三张表是关系型数据库的基本设计套路也是你后面理解JOIN连接的重要铺垫。2.3 主键、唯一键、索引的第一步认知建表语句里出现了PRIMARY KEY、UNIQUE KEY这里我讲得浅一点但你要建立正确认知。主键是每行数据的唯一标识你可以把它理解为身份证号一表只能有一个主键且主键不允许为空。唯一键保证某一列或某几列的组合值不重复比如成绩表里的(stu_id, course_id)就是确保同一个学生同一门课只能录一次成绩。至于索引现在你只需要知道它是数据库为了加速查询而维护的一种数据结构可以类比成书的目录。没有索引的查询是全表扫描相当于从第一页翻到最后一页有索引的查询相当于先查目录再翻到对应页。我建议刚入门的人先不要把注意力放在索引原理上但要养成一个习惯作为查询条件的字段尤其是经常出现在WHERE和JOIN条件里的字段尽量加上索引。等后面遇到慢SQL了再深入研究执行计划那是进阶的事。3. 增删改数据别把线上库当测试环境3.1 INSERT插入数据的三种姿势插入数据是最容易理解的操作但写法上有不少细节值得说。第一种是单条插入指定字段名和值INSERT INTO student (stu_no, stu_name, gender, birth_date, phone) VALUES (2024001, 张伟, 1, 2000-01-15, 13800001111);第二种是一次插入多条VALUES后面跟多个括号INSERT INTO student (stu_no, stu_name, gender, birth_date, phone) VALUES (2024002, 李娜, 2, 2001-03-22, 13800002222), (2024003, 王强, 1, 2000-07-08, 13800003333), (2024004, 赵敏, 2, 2001-11-30, 13800004444);第三种是从查询结果直接插入常用于数据处理和表结构迁移INSERT INTO student_archive (stu_no, stu_name, gender, birth_date, phone) SELECT stu_no, stu_name, gender, birth_date, phone FROM student WHERE create_time 2024-01-01;第三种用法我工作中用到过很多次。比如要临时把线上某些数据导到分析库或者做一张报表中间表直接从业务表里查出来灌进去比先导成文件再导进去要高效得多。要注意的是INSERT SELECT不会检查目标表和源表的数据是否有约束冲突如果目标表有唯一键插入重复数据会直接报错。3.2 UPDATE更新数据血泪教训就是WHERE更新操作我见过太多翻车现场包括我自己刚入行时也干过。有一次要给一批用户改等级写UPDATE的时候忘了加WHERE条件执行完后发现全表用户的等级都被改了。当时真的冷汗直流还好是测试环境如果线上那后果不是写个检讨就能了事的。正确的更新语句必须带WHEREUPDATE student SET phone 13900001111 WHERE stu_no 2024001;这里有个很重要的习惯要培养UPDATE和DELETE语句里WHERE条件一定要先写再回头写SET和表名。我写更新语句的习惯是先写UPDATE student SET phone 13900001111 WHERE stu_no 2024001;然后执行之前再反复看一遍WHERE确认它圈定的范围就是自己真正想改的范围。还有一点如果一条UPDATE要改多个字段SET后面用逗号分隔就行UPDATE student SET phone 13900001111, gender 2 WHERE stu_no 2024001;有些人会问如果不小心忘带了WHERE有没有办法回滚。答案取决于是否开启了事务以及数据变更是否已经提交。这就要引出事务的概念。3.3 DELETE、TRUNCATE和DROP的区别删数据前先冷静删除操作有三兄弟长得像但脾气完全不同。DELETE是逐行删除可以带WHERE条件只删符合条件的行。DELETE执行后不会重置自增ID也就是说你删了最后一行再插入一条新数据ID会继续往下跳。而且DELETE操作可以通过事务回滚在InnoDB引擎下如果还没COMMIT有机会恢复。TRUNCATE是把整张表清空不能带WHERE条件执行效率比DELETE高很多原理是直接丢弃表数据页再重新分配。TRUNCATE之后自增ID会归零而且不能通过事务回滚在MySQL中隐式提交。DROP更狠直接删除整个表的结构和数据神仙难救。操作能否带WHERE能否回滚自增IDDELETE能能事务内不重置TRUNCATE不能基本不能重置DROP不能不能表都没了我的实操建议是删除数据之前先SELECT一遍同样的WHERE条件确认删的就是目标数据。如果可能先备份再删比如CREATE TABLE student_bak_20250101 AS SELECT * FROM student WHERE 条件。这一步多花十秒能避免很多无法挽回的事故。3.4 事务与提交为什么推荐手动开启事务的概念我用一个生活场景来解释转账。A给B转100块数据库要执行两步操作A的账户扣100B的账户加100。这两步要么都成功要么都失败不能出现只扣钱不加钱的情况。事务就是保证一系列操作作为一个整体来执行。MySQL的InnoDB引擎下事务的ACID特性是数据库可靠性的基础。对于多行更新或删除建议手动控制事务START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;如果执行过程中发现某一步出错了可以ROLLBACK回滚让所有修改都不生效。我个人的习惯是涉及金额、库存、状态变更等多表联动的数据操作一定会包上事务。查数据虽然也可以开事务但不那么必要默认的自动提交模式足够了。4. 查询基本功解决80%的日常取数需求4.1 SELECT执行顺序越早理解越少走弯路查询绝对是SQL的重头戏。很多人写SQL能跑出结果但一旦报错就懵了尤其是涉及多个子句组合的时候往往是因为没搞懂SELECT语句的执行顺序。一条完整的查询大概是这样的SELECT stu_name, gender FROM student WHERE create_time 2024-01-01 GROUP BY stu_name, gender HAVING COUNT(*) 1 ORDER BY create_time DESC LIMIT 10;你可能以为SQL引擎是从SELECT开始读的实际上执行顺序完全不是这样。逻辑上的执行顺序大概是FROM确定数据来源先找到表WHERE过滤行级条件GROUP BY按字段分组HAVING过滤分组后的条件SELECT确定要输出的列ORDER BY排序LIMIT分页截取理解这个顺序最大的好处是抓SQL报错时能快速定位是哪一步出了问题。比如你写了WHERE COUNT(*) 1数据库会报错因为WHERE在GROUP BY之前执行此时聚合结果还不存在所以聚合条件必须放在HAVING里。这种错误新手经常遇到理解了执行顺序就可以避免。4.2 WHERE条件与空值处理的三个大坑WHERE是查询中最常用的子句除了常规的、、、、、比较运算符之外还有几个高频场景要注意。第一个坑是NULL的判断。NULL在SQL里表示“未知”或“没有值”它不等于空字符串也不等于0。判断某列是否为NULL必须用IS NULL或IS NOT NULL不能用 NULL或! NULL。曾经有新人写了个查询想找没填手机号的用户条件是WHERE phone ! 结果该用户手机号是NULL这条记录怎么都查不出来。要查出没填手机号的用户正确写法是SELECT * FROM student WHERE phone IS NULL OR phone ;第二个坑是LIKE模糊查询。%表示任意长度的任意字符_表示单个任意字符。比如查询姓张的学生SELECT * FROM student WHERE stu_name LIKE 张%;注意如果数据量很大LIKE %关键词%这种写法即使有索引也未必能命中因为前导百分号会导致索引失效。这个细节在后面慢SQL排查部分我再展开。第三个坑是IN和NOT IN。IN后面跟一个值列表或子查询结果比如查一班和二班的学生SELECT * FROM student WHERE class_id IN (1, 2);用NOT IN的时候要特别小心如果列表里包含NULL结果可能不符合直觉。因为NULL参与比较时结果是UNKNOWN不是TRUE所以带NULL的NOT IN子查询可能会返回空结果。建议用NOT EXISTS替代SELECT * FROM student s WHERE NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.stu_id s.id );4.3 去重的两种方式DISTINCT和GROUP BY别混用去重是个高频需求。热搜词里“sql语句去重查询”、“清洗---sql语句去重”都指向这个问题。最直接的是DISTINCTSELECT DISTINCT gender FROM student;这条语句返回学生表里所有不重复的性别。DISTINCT可以作用于多列意思是多列组合起来不重复SELECT DISTINCT class_id, gender FROM student;另一种去重方式是GROUP BY它本身的功能是分组聚合但也可以实现去重效果SELECT class_id, gender FROM student GROUP BY class_id, gender;两条语句返回结果很相似但语义不同。DISTINCT强调的是“结果集去重”GROUP BY强调的是“按字段分组”如果你还需要统计每组的数量只能用GROUP BY。如果是单纯去重DISTINCT就够了别画蛇添足。还要讲一个实用场景删除表里的重复数据。比如成绩表因为重复导入产生了重复记录思路是先找出每组保留的最小ID再删除不在最小ID列表里的记录DELETE FROM score WHERE id NOT IN ( SELECT MIN(id) FROM score GROUP BY stu_id, course_id );这里要提醒一下MySQL里DELETE的子查询不能直接引用目标表会报“You cant specify target table for update in FROM clause”的错误。解决办法是套一层临时表DELETE FROM score WHERE id NOT IN ( SELECT * FROM ( SELECT MIN(id) FROM score GROUP BY stu_id, course_id ) tmp );4.4 排序与分页LIMIT的偏移问题要注意ORDER BY用于排序默认为升序ASC降序要写DESC。多字段排序时从左到右依次作为主次关键字比如先按班级排再按成绩从高到低排SELECT stu_id, course_id, score FROM score ORDER BY course_id, score DESC;分页常用LIMITMySQL和SQLite都支持。LIMIT的两种写法SELECT * FROM student ORDER BY id LIMIT 10; -- 返回前10行 SELECT * FROM student ORDER BY id LIMIT 10 OFFSET 20; -- 跳过20行返回10行也可以简写成LIMIT 20, 10表示从第21行开始取10行。这里有一个性能陷阱当OFFSET值很大的时候比如LIMIT 100000, 10数据库仍然要先把前面10万行找出来再丢弃效率很低。更优的做法是记住上一页最后一条记录的ID或排序字段值用WHERE条件来定位SELECT * FROM student WHERE id 上一页最后一条的ID ORDER BY id LIMIT 10;这种“键集分页”方式不管翻到第几页查询效率都稳定。在做后台列表分页时我优先用这种方式。5. 聚合分组从明细数据到统计报表5.1 五个聚合函数COUNT、SUM、AVG、MAX、MIN聚合函数是让SQL从“查数据”升级为“算数据”的关键。常用的就五个COUNT统计行数SUM求和AVG求平均值MAX求最大值MIN求最小值比如统计学生总数、成绩总分、平均分、最高分、最低分SELECT COUNT(*) AS total_students, COUNT(phone) AS has_phone_students FROM student;COUNT(*)统计的是所有行数COUNT(phone)统计的是phone不为NULL的行数这个差别非常实用。再比如统计某门课的情况SELECT COUNT(*) AS exam_count, SUM(score) AS total_score, AVG(score) AS avg_score, MAX(score) AS max_score, MIN(score) AS min_score FROM score WHERE course_id 1;5.2 GROUP BY分组统计新手最容易报错的地方分组统计的原理就是按指定的列把数据切成不同的组然后对每组进行聚合计算。比如统计每个学生的选课数量SELECT stu_id, COUNT(*) AS course_count FROM score GROUP BY stu_id;这里有一个高频报错点也就是网上经常说的“only_full_group_by mode”。在MySQL 5.7及以上版本默认开启这个模式要求SELECT列表中的非聚合列必须出现在GROUP BY中。比如下面这条就是典型错误-- 错误的写法 SELECT stu_name, COUNT(*) FROM score JOIN student ON score.stu_id student.id GROUP BY score.stu_id;报错提示大概是“which isnt in GROUP BY”因为stu_name并没有被GROUP BY。正确写法是把stu_name加进GROUP BY-- 正确的写法 SELECT student.stu_name, COUNT(*) FROM score JOIN student ON score.stu_id student.id GROUP BY student.stu_name;或者如果你想统计的是“每个学生且每门课”的组合那GROUP BY可以写多个字段。理解这个报错的关键还是回到SELECT执行顺序GROUP BY先执行SELECT后执行。分组之后每个组内的stu_name并不是唯一的数据库不知道选哪个值输出干脆报错让你自己做决定。5.3 HAVING和WHERE的边界一句话讲明白WHERE在分组前过滤数据HAVING在分组后过滤分组。什么时候用哪个记住一句话过滤条件里如果涉及聚合结果必须用HAVING普通行级过滤用WHERE。比如找出平均分大于80分的学生SELECT stu_id, AVG(score) AS avg_score FROM score GROUP BY stu_id HAVING AVG(score) 80;如果想把不及格的成绩先排除掉再统计平均分就用WHERE先过滤SELECT stu_id, AVG(score) AS avg_score FROM score WHERE score 60 GROUP BY stu_id HAVING AVG(score) 80;注意WHERE和HAVING同时出现时执行的先后顺序先WHERE过滤掉不及格的数据再GROUP BY分组最后HAVING过滤平均分。5.4 分组统计实战课程成绩分析把前面这些知识合起来做一个综合的统计任务统计每门课的选课人数、平均分、最高分、最低分、及格率。SELECT c.course_name, COUNT(*) AS exam_count, ROUND(AVG(s.score), 2) AS avg_score, MAX(s.score) AS max_score, MIN(s.score) AS min_score, ROUND(SUM(CASE WHEN s.score 60 THEN 1 ELSE 0 END) / COUNT(*), 4) AS pass_rate FROM score s JOIN course c ON s.course_id c.id GROUP BY c.course_name;其中SUM(CASE WHEN ... THEN 1 ELSE 0 END)这个写法很实用它能把满足条件的行数统计出来。这里也可以用IF函数简写MySQL写法是SUM(IF(s.score 60, 1, 0))效果一样。这种条件聚合我几乎每周都会用到。6. 多表连接从单表查询走向关联分析6.1 连接的本质笛卡尔积与关联条件单表查询解决了大部分基础需求但业务数据往往分散在多张表里想把学生姓名和成绩放在一起看就必须用连接。连接的本质可以理解为先把两张表做笛卡尔积再根据关联条件筛选出有效组合。所谓笛卡尔积就是第一张表的每一行都去匹配第二张表的每一行。比如学生表有4行成绩表有8行笛卡尔积就是32行其中很多是没有意义的组合。加上关联条件ON之后就只保留两边匹配上的行。6.2 INNER JOIN、LEFT JOIN、RIGHT JOIN怎么选最常用的连接类型是INNER JOIN和LEFT JOIN。INNER JOIN只返回两表都匹配上的行。比如查询有成绩记录的学生及其成绩SELECT s.stu_name, c.course_name, sc.score FROM score sc JOIN student s ON sc.stu_id s.id JOIN course c ON sc.course_id c.id;LEFT JOIN以左表为主返回左表的全部行右表没有匹配的就补NULL。比如查询所有学生的选课情况即使没选过课的学生也要列出来SELECT s.stu_name, sc.score FROM student s LEFT JOIN score sc ON sc.stu_id s.id;没选课的学生score字段会显示NULL。RIGHT JOIN和LEFT JOIN是镜像关系以右表为主。因为LEFT JOIN已经能满足绝大多数场景所以我个人基本不写RIGHT JOIN完全可以用交换两表顺序的LEFT JOIN替代。有一个容易搞混的点ON条件里除了关联条件还能写过滤条件。比如LEFT JOIN时想把某些无效成绩过滤掉可以写在ON里但这时候要小心。ON条件在关联阶段用WHERE条件在关联完成之后用两者的结果可能不同。以LEFT JOIN student和score为例ON里加score.deleted 0左表未匹配的行依然会保留WHERE里加score.deleted 0未匹配的行会被过滤掉LEFT JOIN就变成了INNER JOIN的效果。6.3 JOIN和子查询什么时候用哪个子查询就是嵌套在查询里的查询常见的是WHERE后面的标量子查询和FROM后面的派生表。比如查没有考试成绩的学生SELECT * FROM student WHERE id NOT IN ( SELECT DISTINCT stu_id FROM score );那什么时候用JOIN什么时候用子查询呢。结合我自己的经验给出几条实用建议。如果子查询只是用来做条件过滤不涉及返回子查询里的字段子查询可读性更好比如上面的NOT IN和NOT EXISTS场景。如果子查询返回的字段也要输出到结果集那直接JOIN更合适。比如查学生及其最高分用子查询反而别扭JOIN分组更直接。MySQL的查询优化器在大多数情况下会把子查询改写成连接方式执行性能差距不会特别大所以优先考虑可读性。这里还想多说一句子查询也可以放在SELECT后面当标量用。比如查询每个学生信息及其平均分SELECT s.stu_name, (SELECT AVG(sc.score) FROM score sc WHERE sc.stu_id s.id) AS avg_score FROM student s;这种方式在数据量大时可能性能不佳因为每一行都要执行一次子查询相当于循环查库。能改写成JOIN聚合的场景尽量改写成JOIN。6.4 多表统计实战每个学生的总成绩与选课数来看一个实际需求统计每个学生的姓名、选课数、总成绩并按总成绩降序排列。SELECT s.stu_name, COUNT(sc.course_id) AS course_count, ROUND(SUM(sc.score), 2) AS total_score FROM student s LEFT JOIN score sc ON sc.stu_id s.id GROUP BY s.id, s.stu_name ORDER BY total_score DESC;这里有个细节如果学生没有选课SUM函数的结果是NULL而不是0所以ORDER BY时NULL会被排在最前面还是最后面不同数据库表现不一样。如果想统一处理可以用IFNULL或COALESCE把NULL转成0SELECT s.stu_name, COUNT(sc.course_id) AS course_count, COALESCE(ROUND(SUM(sc.score), 2), 0) AS total_score FROM student s LEFT JOIN score sc ON sc.stu_id s.id GROUP BY s.id, s.stu_name ORDER BY total_score DESC;COALESCE是标准SQL函数返回参数列表中第一个非NULL的值比IFNULL通用性更好。7. 进阶操作窗口函数与慢SQL排查7.1 窗口函数入门ROW_NUMBER、RANK、SUM OVER窗口函数是SQL进阶的一道分水岭也是这轮热搜里的高频词。它本质上是在不改变结果集行数的前提下对每一行计算一个聚合值或排名值。和GROUP BY最大的区别是GROUP BY会把多行合并成一行窗口函数不会。最常用的场景是分组排名。比如给每个学生的成绩排名SELECT stu_id, course_id, score, ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY score DESC) AS rn FROM score;这条SQL按照课程分组组内按成绩降序排名。ROW_NUMBER是连续排名哪怕成绩相同排名也不同RANK是跳跃排名成绩相同排名一样但下一个排名会跳号比如1、2、2、4DENSE_RANK是连续排名成绩相同并列为2下一个继续是3。还有一个很实用的窗口函数场景是累计求和。比如统计每个学生的累计成绩SELECT stu_id, course_id, score, SUM(score) OVER (PARTITION BY stu_id ORDER BY exam_time) AS cumulative_score FROM score;窗口函数刚开始上手会觉得语法别扭但只要记住固定套路聚合函数/排名函数 OVER(PARTITION BY 分组字段 ORDER BY 排序字段)大部分需求都能套进去。它特别适合解决“分组内TOP N”、“分组内排名”、“累计值计算”这类问题用了窗口函数SQL会简洁很多。7.2 常用字符串与日期处理函数写SQL时字符串和日期的处理是日常高频操作这里我整理几个最常用的足以覆盖大部分场景。字符串处理常用的有CONCAT拼接、SUBSTRING截取、UPPER/LOWER大小写转换、LENGTH/LEN长度计算、REPLACE替换、TRIM去空格。比如查询学生的姓名和姓名长度SELECT stu_name, CHAR_LENGTH(stu_name) AS name_length FROM student;日期处理方面DATE_FORMAT格式化、DATE_ADD日期加、DATEDIFF日期差这三个最常用。比如查询2024年注册的学生按月统计数量SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS cnt FROM student WHERE create_time 2024-01-01 AND create_time 2025-01-01 GROUP BY DATE_FORMAT(create_time, %Y-%m) ORDER BY month;日期处理的注意事项是区间过滤。查某一天的数据更稳妥的写法是用 当天零点 AND 第二天零点而不是用BETWEEN。因为BETWEEN包含两个端点如果字段是DATETIME类型且有时间部分BETWEEN 2024-01-01 AND 2024-01-31会把1月31日写TIME的23:59:59之后的数据排除掉。写成 2024-01-01 AND 2024-02-01就不会有这种问题。7.3 慢SQL排查的基本思路EXPLAIN和索引慢SQL优化是热搜词里比较进阶的话题我把新手能上手的排查思路讲一下。遇到一条SQL跑得特别慢第一步不是猜而是用EXPLAIN看执行计划EXPLAIN SELECT * FROM score WHERE stu_id 1;不同数据库的EXPLAIN输出格式不同MySQL主要看几个关键列type、key、rows。type列表示访问类型从好到差大致是const、eq_ref、ref、range、index、ALL。ALL就是全表扫描通常意味着没走索引是需要警惕的。key表示实际用到的索引。rows是预估扫描的行数越小越好。慢SQL的常见原因我整理成一张速查表慢SQL症状常见原因调整方向WHERE条件查得慢没建索引给WHERE字段建索引JOIN查得慢关联字段没索引给关联字段建索引LIKE %关键词%扫全表前导百分号导致索引失效考虑全文索引或ESOR条件导致索引失效多个条件无法合并用UNION拆分分页翻得越多越慢OFFSET过大键集分页返回列太多SELECT *只查需要的列我实测过很多次简单的WHERE字段加完索引查询从几百毫秒降到几毫秒效果立竿见影。索引不是越多越好写多写少都有代价但一开始先把WHERE和JOIN的高频字段索引建起来性价比最高。7.4 新手常见的几个低级错误我都踩过最后分享几个我自己和带过的同学经常犯的错误希望能帮你省下一些排查时间。第一个是字符串不用引号。写WHERE stu_no 2024001如果stu_no是VARCHAR类型有些数据库会做隐式转换索引可能失效。规则很简单字符串比较一定加单引号。第二个是UPDATE和DELETE忘写WHERE。这类错误前面已经反复强调我这里再换个角度它往往发生在你“赶时间”的时候。越急越要冷静写完后默读一遍条件再回车。第三个是混淆“空字符串”和“NULL”。这两种状态在数据库里不同前端传空值到后端时尤其容易踩坑。统一规范比较好比如手机号没填就存NULL不要既存在NULL又存在的情况不然查询时写条件很麻烦。第四个是SELECT *滥用。查几条数据没感觉但线上大表SELECT *会把不需要的大字段也读出来拖慢网络传输和内存占用。我建议只写需要的字段。8. 写在最后8.1 我日常写SQL的一些小习惯这篇文章基本把SQL基本操作的主干过了一遍。最后不讲大道理分享几个我实际写SQL时的习惯。在确认环境的前提下我总是先想清楚要的数据长什么样再动手写。数据来自几张表过滤条件有哪些是否需要分组输出按什么排序。把这几件事想明白SQL写起来基本一气呵成。拿到一个查数需求我不会急着敲键盘而是在草稿纸上画出表关系标注清楚关联字段这个小习惯救过我很多次。另外凡是修改操作我都会先查一遍目标数据再执行变更改完后再查一遍确认结果符合预期。8.2 给新手最后的一个建议如果你现在还在大学阶段或者刚接触SQL我建议不要只停留在看懂这篇文章的程度而是把文中的三张表建出来把每个示例都亲手跑一遍再多给自己出几个变体题目。比如“查每个学生选课数超过2门的学生姓名和选课数”、“查每门课程排名前三的学生”、“查和某个学生同一天出生的同学”。把这些问题用SQL解决之后你的SQL基础就算真的扎实了后面无论是数据分析、后端开发还是DBA方向都会有很顺的开局。 SQL基本操作全解析：从建库建表到窗口函数实战指南
1. 先搞清楚SQL到底在学什么1.1 你以为的SQL和实际要用的SQL可能不是一回事很多人一听到“SQL基本操作”脑子里浮现的是大学数据库课本里那一堆CREATE、DROP、GRANT、REVOKE。真到了工作中才发现日常打交道最多的其实是查询、插入、更新、删除这四类操作也就是常说的增删改查。至于授权、备份、恢复、调参这些事大部分时候有DBA或运维同事顶着普通开发、数据分析、产品运营能把查询写好就已经能解决工作中绝大多数问题了。我用一个比较直白的分类来帮大家建立整体认知。SQL语言按功能大致可以分成四类分类全称代表命令日常工作占比DQL数据查询语言SELECT最高超过一半DML数据操作语言INSERT、UPDATE、DELETE较高DDL数据定义语言CREATE、ALTER、DROP偶尔用到DCL数据控制语言GRANT、REVOKE很少用到这个表不是想让你背概念而是想告诉你学习精力要花在刀刃上。先把SELECT练得滚瓜烂熟再掌握增删改的基本姿势建表改表了解常见用法权限控制交给专业的人。这跟我带新人的思路一致——我会让新人先花一周时间把各种查询写明白而不是上来就研究怎么建存储过程、怎么调索引。1.2 数据库选型MySQL、SQL Server、SQLite到底学哪个看热搜词就能发现大家的关注点分布在MySQL、SQL Server、SQLite这几个方向上。这里我想先给一个结论SQL基本操作的语法在主流数据库里七八成是通用的。标准SQL的SELECT、WHERE、GROUP BY、ORDER BY、JOIN在MySQL、PostgreSQL、SQL Server、SQLite里写法几乎一样。真正不同的地方主要在函数名、分页写法、自增主键的定义方式这些细节上。比如分页查询MySQL用LIMITSQL Server用OFFSET FETCH或TOPSQLite也支持LIMIT比如字符串拼接MySQL用CONCATSQL Server用加号SQLite用||。这些差异确实烦人但如果你把标准SQL的部分打扎实换数据库也就是查一下函数手册的事。我平时用得最多的是MySQL线上生产环境也是MySQL为主偶尔用SQLite做本地数据处理。所以这篇文章的示例统一用MySQL语法来写涉及SQL Server或SQLite有差异的地方我会单独标注说明。这样不管你在哪个数据库上练习思路都能跟得上。2. 建库建表所有SQL操作的地基2.1 建库前先想清楚字符集和排序规则我第一次接手项目的时候上来就CREATE DATABASE连字符集都没指定结果存中文一切正常后来做模糊查询时发现明明条件是对的就是查不出来。排查了半天发现是建库时用了默认的latin1字符集中文被存成了乱码。从那以后我建库一定显式指定字符集。以MySQL为例一个稳妥的建库语句长这样CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;utf8mb4是utf8的超集能存emoji也能存生僻字。虽然你可能觉得用不到但线上环境一旦定了字符集后期想改牵连很大所以一步到位更省心。COLLATE里的ci是case insensitive的缩写意思是排序和比较时不区分大小写这也是比较符合业务直觉的配置。如果你在SQL Server上对应的概念是实例排序规则比如Chinese_PRC_CI_AS思路是一样的。2.2 用学生、课程、成绩三张表走通所有基本操作学SQL基本操作最忌讳的就是只看不练。为了让你后面每个案例都有场景可以跑我设计一套特别经典的三张表学生表、课程表、成绩表。这套表我在不同公司带新人时用过很多次知识点覆盖很全面。学生表保存学生基础信息CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 学生ID, stu_no VARCHAR(20) NOT NULL UNIQUE COMMENT 学号, stu_name VARCHAR(50) NOT NULL COMMENT 姓名, gender CHAR(1) DEFAULT 1 COMMENT 性别1男 2女, birth_date DATE NULL COMMENT 出生日期, phone VARCHAR(20) NULL COMMENT 手机号, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间 ) COMMENT 学生表;课程表保存课程信息CREATE TABLE course ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 课程ID, course_no VARCHAR(20) NOT NULL UNIQUE COMMENT 课程编号, course_name VARCHAR(100) NOT NULL COMMENT 课程名称, credit DECIMAL(3,1) DEFAULT 0 COMMENT 学分 ) COMMENT 课程表;成绩表保存学生每门课的考试成绩CREATE TABLE score ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 主键, stu_id INT NOT NULL COMMENT 学生ID, course_id INT NOT NULL COMMENT 课程ID, score DECIMAL(5,1) NOT NULL COMMENT 成绩满分100, exam_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 考试时间, UNIQUE KEY uk_stu_course (stu_id, course_id) ) COMMENT 成绩表;可能有人问为什么要建三个表不是两个甚至一个就够了吗。直接建一张大宽表把学生信息和成绩放一起写起来确实简单。但这样做会有两个问题一是学生信息重复存储一个学生选了五门课姓名性别就存了五遍浪费空间不说改一次要改多行二是数据更新容易不一致改了一行漏了一行后面对账就头疼。拆成三张表是关系型数据库的基本设计套路也是你后面理解JOIN连接的重要铺垫。2.3 主键、唯一键、索引的第一步认知建表语句里出现了PRIMARY KEY、UNIQUE KEY这里我讲得浅一点但你要建立正确认知。主键是每行数据的唯一标识你可以把它理解为身份证号一表只能有一个主键且主键不允许为空。唯一键保证某一列或某几列的组合值不重复比如成绩表里的(stu_id, course_id)就是确保同一个学生同一门课只能录一次成绩。至于索引现在你只需要知道它是数据库为了加速查询而维护的一种数据结构可以类比成书的目录。没有索引的查询是全表扫描相当于从第一页翻到最后一页有索引的查询相当于先查目录再翻到对应页。我建议刚入门的人先不要把注意力放在索引原理上但要养成一个习惯作为查询条件的字段尤其是经常出现在WHERE和JOIN条件里的字段尽量加上索引。等后面遇到慢SQL了再深入研究执行计划那是进阶的事。3. 增删改数据别把线上库当测试环境3.1 INSERT插入数据的三种姿势插入数据是最容易理解的操作但写法上有不少细节值得说。第一种是单条插入指定字段名和值INSERT INTO student (stu_no, stu_name, gender, birth_date, phone) VALUES (2024001, 张伟, 1, 2000-01-15, 13800001111);第二种是一次插入多条VALUES后面跟多个括号INSERT INTO student (stu_no, stu_name, gender, birth_date, phone) VALUES (2024002, 李娜, 2, 2001-03-22, 13800002222), (2024003, 王强, 1, 2000-07-08, 13800003333), (2024004, 赵敏, 2, 2001-11-30, 13800004444);第三种是从查询结果直接插入常用于数据处理和表结构迁移INSERT INTO student_archive (stu_no, stu_name, gender, birth_date, phone) SELECT stu_no, stu_name, gender, birth_date, phone FROM student WHERE create_time 2024-01-01;第三种用法我工作中用到过很多次。比如要临时把线上某些数据导到分析库或者做一张报表中间表直接从业务表里查出来灌进去比先导成文件再导进去要高效得多。要注意的是INSERT SELECT不会检查目标表和源表的数据是否有约束冲突如果目标表有唯一键插入重复数据会直接报错。3.2 UPDATE更新数据血泪教训就是WHERE更新操作我见过太多翻车现场包括我自己刚入行时也干过。有一次要给一批用户改等级写UPDATE的时候忘了加WHERE条件执行完后发现全表用户的等级都被改了。当时真的冷汗直流还好是测试环境如果线上那后果不是写个检讨就能了事的。正确的更新语句必须带WHEREUPDATE student SET phone 13900001111 WHERE stu_no 2024001;这里有个很重要的习惯要培养UPDATE和DELETE语句里WHERE条件一定要先写再回头写SET和表名。我写更新语句的习惯是先写UPDATE student SET phone 13900001111 WHERE stu_no 2024001;然后执行之前再反复看一遍WHERE确认它圈定的范围就是自己真正想改的范围。还有一点如果一条UPDATE要改多个字段SET后面用逗号分隔就行UPDATE student SET phone 13900001111, gender 2 WHERE stu_no 2024001;有些人会问如果不小心忘带了WHERE有没有办法回滚。答案取决于是否开启了事务以及数据变更是否已经提交。这就要引出事务的概念。3.3 DELETE、TRUNCATE和DROP的区别删数据前先冷静删除操作有三兄弟长得像但脾气完全不同。DELETE是逐行删除可以带WHERE条件只删符合条件的行。DELETE执行后不会重置自增ID也就是说你删了最后一行再插入一条新数据ID会继续往下跳。而且DELETE操作可以通过事务回滚在InnoDB引擎下如果还没COMMIT有机会恢复。TRUNCATE是把整张表清空不能带WHERE条件执行效率比DELETE高很多原理是直接丢弃表数据页再重新分配。TRUNCATE之后自增ID会归零而且不能通过事务回滚在MySQL中隐式提交。DROP更狠直接删除整个表的结构和数据神仙难救。操作能否带WHERE能否回滚自增IDDELETE能能事务内不重置TRUNCATE不能基本不能重置DROP不能不能表都没了我的实操建议是删除数据之前先SELECT一遍同样的WHERE条件确认删的就是目标数据。如果可能先备份再删比如CREATE TABLE student_bak_20250101 AS SELECT * FROM student WHERE 条件。这一步多花十秒能避免很多无法挽回的事故。3.4 事务与提交为什么推荐手动开启事务的概念我用一个生活场景来解释转账。A给B转100块数据库要执行两步操作A的账户扣100B的账户加100。这两步要么都成功要么都失败不能出现只扣钱不加钱的情况。事务就是保证一系列操作作为一个整体来执行。MySQL的InnoDB引擎下事务的ACID特性是数据库可靠性的基础。对于多行更新或删除建议手动控制事务START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;如果执行过程中发现某一步出错了可以ROLLBACK回滚让所有修改都不生效。我个人的习惯是涉及金额、库存、状态变更等多表联动的数据操作一定会包上事务。查数据虽然也可以开事务但不那么必要默认的自动提交模式足够了。4. 查询基本功解决80%的日常取数需求4.1 SELECT执行顺序越早理解越少走弯路查询绝对是SQL的重头戏。很多人写SQL能跑出结果但一旦报错就懵了尤其是涉及多个子句组合的时候往往是因为没搞懂SELECT语句的执行顺序。一条完整的查询大概是这样的SELECT stu_name, gender FROM student WHERE create_time 2024-01-01 GROUP BY stu_name, gender HAVING COUNT(*) 1 ORDER BY create_time DESC LIMIT 10;你可能以为SQL引擎是从SELECT开始读的实际上执行顺序完全不是这样。逻辑上的执行顺序大概是FROM确定数据来源先找到表WHERE过滤行级条件GROUP BY按字段分组HAVING过滤分组后的条件SELECT确定要输出的列ORDER BY排序LIMIT分页截取理解这个顺序最大的好处是抓SQL报错时能快速定位是哪一步出了问题。比如你写了WHERE COUNT(*) 1数据库会报错因为WHERE在GROUP BY之前执行此时聚合结果还不存在所以聚合条件必须放在HAVING里。这种错误新手经常遇到理解了执行顺序就可以避免。4.2 WHERE条件与空值处理的三个大坑WHERE是查询中最常用的子句除了常规的、、、、、比较运算符之外还有几个高频场景要注意。第一个坑是NULL的判断。NULL在SQL里表示“未知”或“没有值”它不等于空字符串也不等于0。判断某列是否为NULL必须用IS NULL或IS NOT NULL不能用 NULL或! NULL。曾经有新人写了个查询想找没填手机号的用户条件是WHERE phone ! 结果该用户手机号是NULL这条记录怎么都查不出来。要查出没填手机号的用户正确写法是SELECT * FROM student WHERE phone IS NULL OR phone ;第二个坑是LIKE模糊查询。%表示任意长度的任意字符_表示单个任意字符。比如查询姓张的学生SELECT * FROM student WHERE stu_name LIKE 张%;注意如果数据量很大LIKE %关键词%这种写法即使有索引也未必能命中因为前导百分号会导致索引失效。这个细节在后面慢SQL排查部分我再展开。第三个坑是IN和NOT IN。IN后面跟一个值列表或子查询结果比如查一班和二班的学生SELECT * FROM student WHERE class_id IN (1, 2);用NOT IN的时候要特别小心如果列表里包含NULL结果可能不符合直觉。因为NULL参与比较时结果是UNKNOWN不是TRUE所以带NULL的NOT IN子查询可能会返回空结果。建议用NOT EXISTS替代SELECT * FROM student s WHERE NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.stu_id s.id );4.3 去重的两种方式DISTINCT和GROUP BY别混用去重是个高频需求。热搜词里“sql语句去重查询”、“清洗---sql语句去重”都指向这个问题。最直接的是DISTINCTSELECT DISTINCT gender FROM student;这条语句返回学生表里所有不重复的性别。DISTINCT可以作用于多列意思是多列组合起来不重复SELECT DISTINCT class_id, gender FROM student;另一种去重方式是GROUP BY它本身的功能是分组聚合但也可以实现去重效果SELECT class_id, gender FROM student GROUP BY class_id, gender;两条语句返回结果很相似但语义不同。DISTINCT强调的是“结果集去重”GROUP BY强调的是“按字段分组”如果你还需要统计每组的数量只能用GROUP BY。如果是单纯去重DISTINCT就够了别画蛇添足。还要讲一个实用场景删除表里的重复数据。比如成绩表因为重复导入产生了重复记录思路是先找出每组保留的最小ID再删除不在最小ID列表里的记录DELETE FROM score WHERE id NOT IN ( SELECT MIN(id) FROM score GROUP BY stu_id, course_id );这里要提醒一下MySQL里DELETE的子查询不能直接引用目标表会报“You cant specify target table for update in FROM clause”的错误。解决办法是套一层临时表DELETE FROM score WHERE id NOT IN ( SELECT * FROM ( SELECT MIN(id) FROM score GROUP BY stu_id, course_id ) tmp );4.4 排序与分页LIMIT的偏移问题要注意ORDER BY用于排序默认为升序ASC降序要写DESC。多字段排序时从左到右依次作为主次关键字比如先按班级排再按成绩从高到低排SELECT stu_id, course_id, score FROM score ORDER BY course_id, score DESC;分页常用LIMITMySQL和SQLite都支持。LIMIT的两种写法SELECT * FROM student ORDER BY id LIMIT 10; -- 返回前10行 SELECT * FROM student ORDER BY id LIMIT 10 OFFSET 20; -- 跳过20行返回10行也可以简写成LIMIT 20, 10表示从第21行开始取10行。这里有一个性能陷阱当OFFSET值很大的时候比如LIMIT 100000, 10数据库仍然要先把前面10万行找出来再丢弃效率很低。更优的做法是记住上一页最后一条记录的ID或排序字段值用WHERE条件来定位SELECT * FROM student WHERE id 上一页最后一条的ID ORDER BY id LIMIT 10;这种“键集分页”方式不管翻到第几页查询效率都稳定。在做后台列表分页时我优先用这种方式。5. 聚合分组从明细数据到统计报表5.1 五个聚合函数COUNT、SUM、AVG、MAX、MIN聚合函数是让SQL从“查数据”升级为“算数据”的关键。常用的就五个COUNT统计行数SUM求和AVG求平均值MAX求最大值MIN求最小值比如统计学生总数、成绩总分、平均分、最高分、最低分SELECT COUNT(*) AS total_students, COUNT(phone) AS has_phone_students FROM student;COUNT(*)统计的是所有行数COUNT(phone)统计的是phone不为NULL的行数这个差别非常实用。再比如统计某门课的情况SELECT COUNT(*) AS exam_count, SUM(score) AS total_score, AVG(score) AS avg_score, MAX(score) AS max_score, MIN(score) AS min_score FROM score WHERE course_id 1;5.2 GROUP BY分组统计新手最容易报错的地方分组统计的原理就是按指定的列把数据切成不同的组然后对每组进行聚合计算。比如统计每个学生的选课数量SELECT stu_id, COUNT(*) AS course_count FROM score GROUP BY stu_id;这里有一个高频报错点也就是网上经常说的“only_full_group_by mode”。在MySQL 5.7及以上版本默认开启这个模式要求SELECT列表中的非聚合列必须出现在GROUP BY中。比如下面这条就是典型错误-- 错误的写法 SELECT stu_name, COUNT(*) FROM score JOIN student ON score.stu_id student.id GROUP BY score.stu_id;报错提示大概是“which isnt in GROUP BY”因为stu_name并没有被GROUP BY。正确写法是把stu_name加进GROUP BY-- 正确的写法 SELECT student.stu_name, COUNT(*) FROM score JOIN student ON score.stu_id student.id GROUP BY student.stu_name;或者如果你想统计的是“每个学生且每门课”的组合那GROUP BY可以写多个字段。理解这个报错的关键还是回到SELECT执行顺序GROUP BY先执行SELECT后执行。分组之后每个组内的stu_name并不是唯一的数据库不知道选哪个值输出干脆报错让你自己做决定。5.3 HAVING和WHERE的边界一句话讲明白WHERE在分组前过滤数据HAVING在分组后过滤分组。什么时候用哪个记住一句话过滤条件里如果涉及聚合结果必须用HAVING普通行级过滤用WHERE。比如找出平均分大于80分的学生SELECT stu_id, AVG(score) AS avg_score FROM score GROUP BY stu_id HAVING AVG(score) 80;如果想把不及格的成绩先排除掉再统计平均分就用WHERE先过滤SELECT stu_id, AVG(score) AS avg_score FROM score WHERE score 60 GROUP BY stu_id HAVING AVG(score) 80;注意WHERE和HAVING同时出现时执行的先后顺序先WHERE过滤掉不及格的数据再GROUP BY分组最后HAVING过滤平均分。5.4 分组统计实战课程成绩分析把前面这些知识合起来做一个综合的统计任务统计每门课的选课人数、平均分、最高分、最低分、及格率。SELECT c.course_name, COUNT(*) AS exam_count, ROUND(AVG(s.score), 2) AS avg_score, MAX(s.score) AS max_score, MIN(s.score) AS min_score, ROUND(SUM(CASE WHEN s.score 60 THEN 1 ELSE 0 END) / COUNT(*), 4) AS pass_rate FROM score s JOIN course c ON s.course_id c.id GROUP BY c.course_name;其中SUM(CASE WHEN ... THEN 1 ELSE 0 END)这个写法很实用它能把满足条件的行数统计出来。这里也可以用IF函数简写MySQL写法是SUM(IF(s.score 60, 1, 0))效果一样。这种条件聚合我几乎每周都会用到。6. 多表连接从单表查询走向关联分析6.1 连接的本质笛卡尔积与关联条件单表查询解决了大部分基础需求但业务数据往往分散在多张表里想把学生姓名和成绩放在一起看就必须用连接。连接的本质可以理解为先把两张表做笛卡尔积再根据关联条件筛选出有效组合。所谓笛卡尔积就是第一张表的每一行都去匹配第二张表的每一行。比如学生表有4行成绩表有8行笛卡尔积就是32行其中很多是没有意义的组合。加上关联条件ON之后就只保留两边匹配上的行。6.2 INNER JOIN、LEFT JOIN、RIGHT JOIN怎么选最常用的连接类型是INNER JOIN和LEFT JOIN。INNER JOIN只返回两表都匹配上的行。比如查询有成绩记录的学生及其成绩SELECT s.stu_name, c.course_name, sc.score FROM score sc JOIN student s ON sc.stu_id s.id JOIN course c ON sc.course_id c.id;LEFT JOIN以左表为主返回左表的全部行右表没有匹配的就补NULL。比如查询所有学生的选课情况即使没选过课的学生也要列出来SELECT s.stu_name, sc.score FROM student s LEFT JOIN score sc ON sc.stu_id s.id;没选课的学生score字段会显示NULL。RIGHT JOIN和LEFT JOIN是镜像关系以右表为主。因为LEFT JOIN已经能满足绝大多数场景所以我个人基本不写RIGHT JOIN完全可以用交换两表顺序的LEFT JOIN替代。有一个容易搞混的点ON条件里除了关联条件还能写过滤条件。比如LEFT JOIN时想把某些无效成绩过滤掉可以写在ON里但这时候要小心。ON条件在关联阶段用WHERE条件在关联完成之后用两者的结果可能不同。以LEFT JOIN student和score为例ON里加score.deleted 0左表未匹配的行依然会保留WHERE里加score.deleted 0未匹配的行会被过滤掉LEFT JOIN就变成了INNER JOIN的效果。6.3 JOIN和子查询什么时候用哪个子查询就是嵌套在查询里的查询常见的是WHERE后面的标量子查询和FROM后面的派生表。比如查没有考试成绩的学生SELECT * FROM student WHERE id NOT IN ( SELECT DISTINCT stu_id FROM score );那什么时候用JOIN什么时候用子查询呢。结合我自己的经验给出几条实用建议。如果子查询只是用来做条件过滤不涉及返回子查询里的字段子查询可读性更好比如上面的NOT IN和NOT EXISTS场景。如果子查询返回的字段也要输出到结果集那直接JOIN更合适。比如查学生及其最高分用子查询反而别扭JOIN分组更直接。MySQL的查询优化器在大多数情况下会把子查询改写成连接方式执行性能差距不会特别大所以优先考虑可读性。这里还想多说一句子查询也可以放在SELECT后面当标量用。比如查询每个学生信息及其平均分SELECT s.stu_name, (SELECT AVG(sc.score) FROM score sc WHERE sc.stu_id s.id) AS avg_score FROM student s;这种方式在数据量大时可能性能不佳因为每一行都要执行一次子查询相当于循环查库。能改写成JOIN聚合的场景尽量改写成JOIN。6.4 多表统计实战每个学生的总成绩与选课数来看一个实际需求统计每个学生的姓名、选课数、总成绩并按总成绩降序排列。SELECT s.stu_name, COUNT(sc.course_id) AS course_count, ROUND(SUM(sc.score), 2) AS total_score FROM student s LEFT JOIN score sc ON sc.stu_id s.id GROUP BY s.id, s.stu_name ORDER BY total_score DESC;这里有个细节如果学生没有选课SUM函数的结果是NULL而不是0所以ORDER BY时NULL会被排在最前面还是最后面不同数据库表现不一样。如果想统一处理可以用IFNULL或COALESCE把NULL转成0SELECT s.stu_name, COUNT(sc.course_id) AS course_count, COALESCE(ROUND(SUM(sc.score), 2), 0) AS total_score FROM student s LEFT JOIN score sc ON sc.stu_id s.id GROUP BY s.id, s.stu_name ORDER BY total_score DESC;COALESCE是标准SQL函数返回参数列表中第一个非NULL的值比IFNULL通用性更好。7. 进阶操作窗口函数与慢SQL排查7.1 窗口函数入门ROW_NUMBER、RANK、SUM OVER窗口函数是SQL进阶的一道分水岭也是这轮热搜里的高频词。它本质上是在不改变结果集行数的前提下对每一行计算一个聚合值或排名值。和GROUP BY最大的区别是GROUP BY会把多行合并成一行窗口函数不会。最常用的场景是分组排名。比如给每个学生的成绩排名SELECT stu_id, course_id, score, ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY score DESC) AS rn FROM score;这条SQL按照课程分组组内按成绩降序排名。ROW_NUMBER是连续排名哪怕成绩相同排名也不同RANK是跳跃排名成绩相同排名一样但下一个排名会跳号比如1、2、2、4DENSE_RANK是连续排名成绩相同并列为2下一个继续是3。还有一个很实用的窗口函数场景是累计求和。比如统计每个学生的累计成绩SELECT stu_id, course_id, score, SUM(score) OVER (PARTITION BY stu_id ORDER BY exam_time) AS cumulative_score FROM score;窗口函数刚开始上手会觉得语法别扭但只要记住固定套路聚合函数/排名函数 OVER(PARTITION BY 分组字段 ORDER BY 排序字段)大部分需求都能套进去。它特别适合解决“分组内TOP N”、“分组内排名”、“累计值计算”这类问题用了窗口函数SQL会简洁很多。7.2 常用字符串与日期处理函数写SQL时字符串和日期的处理是日常高频操作这里我整理几个最常用的足以覆盖大部分场景。字符串处理常用的有CONCAT拼接、SUBSTRING截取、UPPER/LOWER大小写转换、LENGTH/LEN长度计算、REPLACE替换、TRIM去空格。比如查询学生的姓名和姓名长度SELECT stu_name, CHAR_LENGTH(stu_name) AS name_length FROM student;日期处理方面DATE_FORMAT格式化、DATE_ADD日期加、DATEDIFF日期差这三个最常用。比如查询2024年注册的学生按月统计数量SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS cnt FROM student WHERE create_time 2024-01-01 AND create_time 2025-01-01 GROUP BY DATE_FORMAT(create_time, %Y-%m) ORDER BY month;日期处理的注意事项是区间过滤。查某一天的数据更稳妥的写法是用 当天零点 AND 第二天零点而不是用BETWEEN。因为BETWEEN包含两个端点如果字段是DATETIME类型且有时间部分BETWEEN 2024-01-01 AND 2024-01-31会把1月31日写TIME的23:59:59之后的数据排除掉。写成 2024-01-01 AND 2024-02-01就不会有这种问题。7.3 慢SQL排查的基本思路EXPLAIN和索引慢SQL优化是热搜词里比较进阶的话题我把新手能上手的排查思路讲一下。遇到一条SQL跑得特别慢第一步不是猜而是用EXPLAIN看执行计划EXPLAIN SELECT * FROM score WHERE stu_id 1;不同数据库的EXPLAIN输出格式不同MySQL主要看几个关键列type、key、rows。type列表示访问类型从好到差大致是const、eq_ref、ref、range、index、ALL。ALL就是全表扫描通常意味着没走索引是需要警惕的。key表示实际用到的索引。rows是预估扫描的行数越小越好。慢SQL的常见原因我整理成一张速查表慢SQL症状常见原因调整方向WHERE条件查得慢没建索引给WHERE字段建索引JOIN查得慢关联字段没索引给关联字段建索引LIKE %关键词%扫全表前导百分号导致索引失效考虑全文索引或ESOR条件导致索引失效多个条件无法合并用UNION拆分分页翻得越多越慢OFFSET过大键集分页返回列太多SELECT *只查需要的列我实测过很多次简单的WHERE字段加完索引查询从几百毫秒降到几毫秒效果立竿见影。索引不是越多越好写多写少都有代价但一开始先把WHERE和JOIN的高频字段索引建起来性价比最高。7.4 新手常见的几个低级错误我都踩过最后分享几个我自己和带过的同学经常犯的错误希望能帮你省下一些排查时间。第一个是字符串不用引号。写WHERE stu_no 2024001如果stu_no是VARCHAR类型有些数据库会做隐式转换索引可能失效。规则很简单字符串比较一定加单引号。第二个是UPDATE和DELETE忘写WHERE。这类错误前面已经反复强调我这里再换个角度它往往发生在你“赶时间”的时候。越急越要冷静写完后默读一遍条件再回车。第三个是混淆“空字符串”和“NULL”。这两种状态在数据库里不同前端传空值到后端时尤其容易踩坑。统一规范比较好比如手机号没填就存NULL不要既存在NULL又存在的情况不然查询时写条件很麻烦。第四个是SELECT *滥用。查几条数据没感觉但线上大表SELECT *会把不需要的大字段也读出来拖慢网络传输和内存占用。我建议只写需要的字段。8. 写在最后8.1 我日常写SQL的一些小习惯这篇文章基本把SQL基本操作的主干过了一遍。最后不讲大道理分享几个我实际写SQL时的习惯。在确认环境的前提下我总是先想清楚要的数据长什么样再动手写。数据来自几张表过滤条件有哪些是否需要分组输出按什么排序。把这几件事想明白SQL写起来基本一气呵成。拿到一个查数需求我不会急着敲键盘而是在草稿纸上画出表关系标注清楚关联字段这个小习惯救过我很多次。另外凡是修改操作我都会先查一遍目标数据再执行变更改完后再查一遍确认结果符合预期。8.2 给新手最后的一个建议如果你现在还在大学阶段或者刚接触SQL我建议不要只停留在看懂这篇文章的程度而是把文中的三张表建出来把每个示例都亲手跑一遍再多给自己出几个变体题目。比如“查每个学生选课数超过2门的学生姓名和选课数”、“查每门课程排名前三的学生”、“查和某个学生同一天出生的同学”。把这些问题用SQL解决之后你的SQL基础就算真的扎实了后面无论是数据分析、后端开发还是DBA方向都会有很顺的开局。