很多人第一次接触到数据库系统时最容易产生一个错觉SQL就是查数据的会写SELECT就够了剩下的都是运维的事。等到真正把一套业务跑起来才发现建表策略不合理、索引乱加、JOIN写得随心所欲线上慢查询一个一个冒出来才回头补基础课。这篇文章是数据库系统系列的第六篇专门讲SQL概述。我不打算复述教材里SQL是结构化查询语言那种定义式开头而是从关系模型、语句分类、查询执行逻辑、约束索引到一套完整实战案例把SQL从认识它到用对它的全过程梳理一遍。内容不追求大而全但保证每个点都能落到底特别是那些教材不会单独拎出来讲的易错环节我会结合自己写SQL、调SQL的真实体验展开。1. SQL统治数据库世界的原因从关系模型到关系数据库1.1 关系模型的提出与SQL的诞生要理解SQL为什么长成今天这样得先回到关系模型。上世纪七十年代一位计算机科学家在一篇开创性论文中提出了关系模型核心思想是把数据抽象成二维表表由行和列组成表与表之间通过共同字段建立联系。这个模型看起来平平无奇但它解决了一个大麻烦此前应用普遍依赖层次模型和网状模型数据访问路径和存储结构紧密耦合业务一改动程序就得跟着改。关系模型提出之后市场上冒出了很多标榜关系的产品但各家查询接口五花八门开发人员学一个系统就要学一套新命令迁移成本极高。于是SQL作为统一的关系数据库语言诞生了。它最初只在某实验室内部使用后来逐渐成为国际标准从SQL-86一路演进到SQL-92、SQL:1999、SQL:2003、SQL:2016。我个人的体会是标准里最核心的语法在几十年前就已经定型后续的版本更多是在补齐类型系统、窗口函数、JSON处理这类能力底层的SELECT/INSERT/UPDATE/DELETE骨架几乎没变过。这也是为什么你现在随便打开一本文科生教材或者理工科选修课讲义讲SQL都是先从SELECT * FROM开始——因为这套语法本身就是为直观描述数据操作而设计的你要查什么列从哪张表取满足什么条件翻译过来就是SELECT、FROM、WHERE三个关键字。1.2 标准在演进但核心语法几十年不变很多同学会问既然SQL是标准为什么我在MySQL里写的语句换到PostgreSQL就报错标准规定了应该有什么但没有规定必须怎么写死。各数据库在日期函数、字符串拼接、分页语法上都做了自己的扩展。比如分页MySQL用的是LIMITSQL Server早期用TOPOracle以前用ROWNUM现在都有标准化的FETCH FIRST语法了但老系统里依然坚挺着各家方言。我在实际项目里的处理方式是核心逻辑尽量用标准SQL写只有没法绕开方言差异时才使用特定语法。因为一旦未来要换数据库底座纯标准SQL的部分可以原样迁移方言部分就得逐条改。这种习惯在维护一个历经三次数据库迁移的系统时帮我省了太多事。学习SQL时也建议先吃透标准再去看目标数据库的差异点这样底子是扎实的。2. SQL的四大组成部分别再以为SQL只是查询2.1 DDL用CREATE/ALTER/DROP管理表结构数据定义语言DDL负责定义数据库对象最常见的就是表。我记得刚学SQL时以为建表就是CREATE TABLE 表名 (列名 类型)这么简单真正写系统才发现建表要纠结的事情太多了。下面是我现在写表结构时比较基础但完整的写法CREATE TABLE customers ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, email VARCHAR(128) NOT NULL UNIQUE, phone VARCHAR(20), level TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP );这里每一个约束都不是随便加的主键保证每一行能被唯一标识UNIQUE约束防止同一个邮箱注册两次DEFAULT让插入时不用每次手写创建时间NOT NULL避免脏数据混进来。ALTER TABLE用来调整结构比如给表增加字段注意生产环境操作前要做好备份或者走变更平台不能像本地练习一样随手执行。ALTER TABLE customers ADD COLUMN city VARCHAR(32) AFTER phone;DROP TABLE是删表这个命令威力很大一旦执行表结构和数据都没了。生产环境中绝大多数团队会禁用开发者的DROP权限防止一条语句毁掉整个业务这一点在数据库系统课程后面讲权限管理时也会反复强调。2.2 DMLINSERT、UPDATE、DELETE增删改数据操作语言是应用和数据库交互的主力。INSERT的基本用法大家都会但有几个细节容易被忽视。第一是多行插入比逐行插入效率高得多INSERT INTO customers (name, email, level) VALUES (张三, zhangsanexample.com, 1), (李四, lisiexample.com, 2);一条语句插入多行相当于一次网络往返处理多条数据在批量导入场景里性能差距非常明显。UPDATE和DELETE比较危险的地方在于WHERE条件写错了会误伤大量数据。我自己处理过最惊险的一次某同事在测试环境执行UPDATE语句时忘了加WHERE整个表的数据全被改成同一个值因为测试环境有快照才没有酿成大祸。从那以后我养成了两个习惯第一UPDATE和DELETE先写WHERE再回头写表名和SET第二执行影响行数较多的操作前先开启事务确认无误后再提交。另一个体现在实际工作中容易踩的坑是DELETE和TRUNCATE的区别。TRUNCATE是DDL它会快速清空整表并且释放存储空间还不会触发逐行的DELETE触发器但正因为它不走逐行删除流程所以无法通过WHERE指定要删除哪些行。如果你只想删部分数据只能用DELETE如果整个表都不要了、要重置自增IDTRUNCATE更合适。2.3 DQLSELECT查询几乎每个业务的核心数据查询语言是SQL里知识点最多、最值得花时间研究的部分。一个典型的查询语句可以包含SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT等几十个关键字组合SELECT也可以嵌套在子查询里甚至可以和JOIN、窗口函数混用组合空间非常大。关于SELECT的精讲我在下一章单独展开。这里先建立一个认知SQL最难的不是记住语法而是能把业务需求翻译成查询语句的思维过程。同样是找出未下过订单的用户有人用NOT IN有人用LEFT JOIN ... WHERE ... IS NULL还有人用NOT EXISTS三种写法结果可能相同性能却天差地别。这个对比后面实战部分会具体分析。2.4 DCL与TCL权限和事务运维才懂它们的价值数据控制语言DCL主要包含GRANT和REVOKE用来控制谁可以读、写、修改数据库对象。实际项目里我们会给应用账号只读权限给后台人员部分表的增删改权限给DBA完整的DDL权限这样即使某个账号泄露损失也被限制在一定范围内。事务控制语言TCL包含COMMIT、ROLLBACK、SAVEPOINT。事务的核心价值是要么全部成功、要么全部失败。转账场景是最典型的例子扣款和入账两个操作必须同时成功任何一个失败都要回滚否则账就对不上了。我在写业务代码时涉及多条写入的请求一律放进事务并在异常分支执行ROLLBACK。SAVEPOINT稍微进阶一些它允许在事务里设置一个回滚点回滚时只回退到指定点而不是撤销全部操作。这个技巧在复杂的批量处理逻辑中很好用不过要控制使用频率SAVEPOINT用得太多本身也是一种性能开销。3. 深入SELECT执行顺序决定一切3.1 书写顺序与执行顺序的不一致是新手第一个坎很多初学者背SELECT语法背得很熟但他们往往不理解为什么WHERE子句里不能用SELECT中定义的别名。这个问题的答案藏在SQL的执行顺序里。书写一个查询时我们习惯按SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY - LIMIT来写。但数据库引擎真正执行的顺序是FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT。也就是说SELECT别名是在WHERE、GROUP BY、HAVING这些子句之后才被算出来的。所以你在WHERE里写WHERE别名 100数据库根本不知道别名是什么因为这一步在时间线上还没发生。相反ORDER BY排在SELECT之后所以ORDER BY里可以用别名。下面用一个简单例子验证一下。假设有张订单表我们要筛选出下单数量大于2的用户找出销售金额最高的三个SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders WHERE status paid GROUP BY user_id HAVING COUNT(*) 2 ORDER BY total_amount DESC LIMIT 3;这个语句在各子句上的执行顺序是FROM orders先确定数据从哪张表取。WHERE status paid过滤掉未支付的订单。GROUP BY user_id把同一个用户的订单聚成一组。HAVING COUNT(*) 2分组之后只保留下单次数大于2的组。SELECT user_id, COUNT(*), SUM(amount)计算各组的总金额。ORDER BY total_amount DESC按总金额从高到低排序。LIMIT 3只取前三行。理解这个顺序最大的好处是排查SQL逻辑错误时会非常快。遇到为什么WHERE里写不了别名为什么GROUP BY之后SELECT里出现非聚合列就报错这类问题只要心里有一张执行顺序图答案立刻就有了。3.2 JOIN的取舍内连接、左连接与表扫描的暧昧关系多表查询是SQL进阶的分水岭。JOIN之所以让人困惑是因为它把行的匹配关系和哪些行会保留搅在一起。我一般用集合来理解内连接是两张表的交集左连接是左表全集加上右表能匹配上的行匹配不上用NULL填充。先看一个内连接SELECT c.name, o.order_no, o.amount FROM customers c INNER JOIN orders o ON c.id o.customer_id;它会返回所有下过订单的客户和对应的订单。如果一个客户从来没下过单他就不会出现在结果里。这在找出活跃用户的时候非常合适。再看一个左连接SELECT c.name, o.order_no, o.amount FROM customers c LEFT JOIN orders o ON c.id o.customer_id;这次不管客户有没有订单他都会出现在结果里没有订单的客户o.order_no和o.amount都是NULL。这个模式常用来找没有订单的客户SELECT c.name FROM customers c LEFT JOIN orders o ON c.id o.customer_id WHERE o.id IS NULL;WHERE o.id IS NULL就是在说左连接之后右表没有匹配上的行。同理用NOT EXISTS也能实现同样的效果而且在大表场景下性能往往更好SELECT name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.id );为什么NOT EXISTS更容易走好索引因为它的逻辑是找到一条就算数数据库引擎往往能提前终止扫描而LEFT JOIN IS NULL可能要先构造出一个很大的中间结果再过滤掉非NULL部分。这只是经验具体哪个快还是要看执行计划和数据分布所以我做SQL优化时不会教条地认定某一种写法一定快。开发中还有一个高频问题多张表JOIN时ON条件里的过滤和WHERE里的过滤区别很大。ON条件决定两表匹配规则WHERE决定JOIN之后结果集的过滤条件。左连接时如果你把左表过滤条件写进ON很可能得到和预期不同的行数排查时很容易一头雾水。我的经验是JOIN关联条件放进ON逻辑过滤条件放进WHERE这个习惯能避免九成以上的关联查询疑难杂症。3.3 GROUP BY与HAVING分组统计的正确姿势分组统计是写报表SQL最常用的能力。GROUP BY会把指定列中值相同的行归成一组然后配合聚合函数COUNT、SUM、AVG、MAX、MIN来计算每组的信息。这里最常见的一个误区是把WHERE和HAVING混用。WHERE是在分组之前对原始行做过滤比如只看已支付的订单必须用WHEREHAVING是在分组之后对聚合结果做过滤比如只看下单次数超过5次的用户只能用HAVING。如果你把HAVING想成分组的WHERE这两个就好区分了。考虑这样一个需求统计每月订单总额只保留月销售额超过一万的月份并按月份倒序排列。SELECT DATE_FORMAT(created_at, %Y-%m) AS month, SUM(amount) AS total_amount FROM orders WHERE status paid GROUP BY DATE_FORMAT(created_at, %Y-%m) HAVING SUM(amount) 10000 ORDER BY month DESC;注意一个细节SELECT里用了DATE_FORMAT(created_at, %Y-%m)这个表达式GROUP BY后面也必须跟同样的表达式不能直接写GROUP BY month。这也是执行顺序决定的因为GROUP BY在SELECT之前执行那时month这个别名还不存在。很多数据库对此校验得很严格但有些方言宽松一些甚至能让你蒙混过关一旦换数据库真实执行时会变得不可控。我建议在一开始就按严格的姿势写别依赖特定数据库的宽容特性。另外如果字段里含有NULL分组时NULL会被单独分到一组。这不算bug但统计结果里猛然出现一个NULL分组时要有这个意识不然会以为数据出了问题。4. 约束与索引写在建表阶段的数据防线4.1 主键、外键、唯一、检查与非空约束约束是数据库自带的数据完整性守卫。什么数据能进表、哪些列必须填、哪些值不能重复这些规则在建表阶段就应该明确而不是等应用代码来兜底。PRIMARY KEY每个表一个主键唯一标识一行。主键列默认非空且唯一索引也自动建立。UNIQUE保证列或列组合的值唯一。比如用户的邮箱、手机号通常都加UNIQUE约束。FOREIGN KEY外键约束关联到另一张表的主键它保证你引用的是一个真实存在的记录。比如订单表的customer_id引用customers表的id如果没有这个约束业务代码写得再小心也可能插入一个不存在的customer_id。NOT NULL禁止该列为NULL。业务含义上必须有值的字段就应该加上比如订单金额、用户姓名。CHECK定义取值范围或格式约束比如金额必须大于0。早期的MySQL对CHECK约束执行不力导致很多人以为它没用其实在功能完善的数据库里CHECK是很有价值的前置防线。外键约束是双刃剑。它可以防止脏数据也会拖慢插入和更新速度因为每写一条数据都要去父表校验一次。某些高并发核心链路会刻意不用外键把完整性校验交给应用层处理换取写入性能。我个人偏好在数据一致性要求极高且写入并发又不疯狂的表上使用外键对于日志类、流水类大表一般不用外键改用索引和程序校验。4.2 索引不是创建得越多越好索引本质上是数据库为了加速数据检索而维护的一种额外数据结构可以类比成书末的目录。没有索引时查询要逐行扫描整张表数据量一大就慢到不可接受有索引后数据库可以直接定位到匹配行所在的存储位置查询速度快几个数量级。但索引不是免费的。每增加一个索引INSERT、UPDATE、DELETE时都要更新它写入性能会下降索引本身也会占用磁盘空间。有些开发者为了查询快把常用WHERE条件列都建了索引结果单表十几个索引写入链路慢得离谱。我常建议的取舍方式是优先给WHERE、JOIN、ORDER BY频繁使用的列建索引。区分度低的列比如性别、状态字段索引价值很低。组合索引要考虑最左前缀原则查询条件里没有包含组合索引最左侧的列索引往往用不上。不要给大文本字段直接建普通索引必要时用前缀索引。还有一类非常经典的索引失效问题在索引列上做函数运算。比如WHERE YEAR(created_at) 2024即使created_at有索引因为包了一层函数数据库也无法直接用索引快速定位只能退化为全表扫描。正确的姿势是用范围条件WHERE created_at 2024-01-01 AND created_at 2025-01-01。这个坑几乎每个做过性能优化的人都遇到过写SQL时多一点意识就能避免。5. 把SQL串起来一个在线订单系统的完整实战5.1 表结构设计与建表语句理论知识铺得差不多了下面用一个虚构的在线商店场景把前面提到的东西串起来。这个场景我有意控制得简单但足够演示建表、关联、分组、子查询这些核心能力。假设我们要支撑一个包含用户、订单、订单明细的在线商店。用户信息放customers表订单主档放orders表每张订单买了哪些商品、数量多少、单价多少放在order_items表。三段式结构是订单系统最常见的建模方式。建表语句如下CREATE TABLE customers ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, email VARCHAR(128) NOT NULL UNIQUE, city VARCHAR(32), created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, order_no VARCHAR(32) NOT NULL UNIQUE, status VARCHAR(16) NOT NULL DEFAULT created, amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ); CREATE TABLE order_items ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, product_name VARCHAR(64) NOT NULL, quantity INT NOT NULL, price DECIMAL(10,2) NOT NULL, CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES orders(id) );amount字段用DECIMAL而不是FLOAT是一个非常重要的细节。金额计算如果用FLOAT可能会因为浮点误差出现0.30000000000000004这种结果DECIMAL是精确小数类型账务场景里是底线要求。quantity用INTprice用DECIMAL为什么数量不用DECIMAL因为商品数量不参与除法计算整数足够而且可以避免小数带来的歧义。5.2 业务数据插入与更新模拟几条数据。先插入两个客户再插入两张订单一张已支付、一张待支付最后给订单加上明细。INSERT INTO customers (name, email, city) VALUES (张三, zhangsanexample.com, 上海), (李四, lisiexample.com, 北京); INSERT INTO orders (customer_id, order_no, status, amount) VALUES (1, ORD202406001, paid, 199.00), (1, ORD202406002, created, 59.90), (2, ORD202406003, paid, 399.00); INSERT INTO order_items (order_id, product_name, quantity, price) VALUES (1, 机械键盘, 1, 199.00), (2, 鼠标垫, 1, 59.90), (3, 显示器, 1, 399.00);注意orders表有amount字段order_items里也有price字段这两处金额存在事实上的冗余。在这个设计里订单金额是明细的总和快照这样报表查询不用每次重新聚合明细。冗余本身不一定是坏的关键是更新时维护好一致性修改明细必须同步重算订单金额否则账会不平。实际系统里还会在订单表上记录下单时的商品名称防止商品改名后历史订单失真这就是经典的快照冗余思路。5.3 四个常用统计查询的SQL拆解第一个需求找出所有下过订单的用户及其订单金额。SELECT c.id, c.name, o.order_no, o.amount FROM customers c INNER JOIN orders o ON c.id o.customer_id;第二个需求统计每个用户的累计消费金额只看已支付订单并按金额倒序。SELECT c.name, SUM(o.amount) AS total_spent FROM customers c INNER JOIN orders o ON c.id o.customer_id WHERE o.status paid GROUP BY c.id, c.name ORDER BY total_spent DESC;GROUP BY c.id之后为什么还要在SELECT里保留c.name这涉及SQL的分组列之外的字段必须被聚合规则不同数据库宽松度不一样但标准的做法是把name也放进GROUP BY或者用ANY_VALUE之类的聚合函数。为了兼容性和清晰的语义我会把非聚合字段都放进GROUP BY。第三个需求找出从未下过单的用户。SELECT c.name, c.email FROM customers c LEFT JOIN orders o ON c.id o.customer_id WHERE o.id IS NULL;如果当前有2个用户但只有1个用户下过单这个查询会返回另一个没下单的用户。这种写法在业务里非常常用理解它的关键是左连接后右表主键为NULL即代表无关联记录。第四个需求统计商品销量排行找出被购买数量最多的商品。SELECT product_name, SUM(quantity) AS total_qty FROM order_items GROUP BY product_name ORDER BY total_qty DESC;这四个查询覆盖了单表查询、多表关联、聚合统计、连接后过滤四类典型场景基本把入门SQL的核心操作都练到了。建议你看着表结构先自己写一遍再对照我这里的写法看差异在哪里。尤其注意执行顺序对WHERE和HAVING位置的影响。6. 我踩过的SQL坑以及给初学者的4条建议6.1 三个容易翻车的SQL细节第一个是COUNT()和COUNT(column)的区别。COUNT()统计结果集的总行数包括NULL行COUNT(column)只统计该列非NULL的行数。有时候你只想统计有效订单数却写了COUNT(amount)恰好有些订单的amount是NULL表面对不上账半天排查不出来。按我现在的习惯统计总行数一律用COUNT(*)统计非空值数才用COUNT(column)。第二个是比较日期时忽略时间部分。一个DATETIME列存的是2024-06-01 12:30:00你写WHERE created_at 2024-06-01在严格模式下是匹配不到的。正确做法是用范围条件created_at 2024-06-01 AND created_at 2024-06-02。同理统计某天数据时只对日期列做函数转换再比较往往会让索引失效能不用函数就尽量不用。第三个是整数除法的精度问题。在部分数据库里SELECT 5 / 2返回2而不是2.5因为两个整数相除结果还是整数。金额计算里这种问题尤其危险单价、数量的除法一不小心就丢精度。解决办法是让至少一个操作数是小数比如5 / 2.0或者用CAST(col AS DECIMAL(10,2))。这个坑在写次数统计、平均值计算时特别常见我在评审同事的SQL时已经不止一次抓到过。6.2 学习SQL的正确路径和避免的低效方法我给初学者的建议一直很固定不要只看教程要上手做。SQL是一门需要肌肉记忆的技能看十遍不如写一遍。搭建一个数据库准备一组模拟数据把增删改查、关联、分组、嵌套查询全部实际操作一遍这个过程走完基础就算扎实了。之后务必学会看执行计划。大多数数据库都提供了EXPLAIN命令它会告诉你一条SQL走了什么索引、扫描了多少行、有没有文件排序。我第一次意识到我的SQL还能再快十倍就是从看执行计划开始的。不看执行计划写SQL就像不看仪表盘开车全凭感觉。还要刻意练习把业务需求翻译成SQL。这里有个很核心的能力就是先画数据流先了解涉及哪些表、表之间怎么关联、结果需要怎么聚合再落笔写SQL。很多复杂需求其实可以拆成几个子查询串起来不用非得一个嵌套套到底。如果一条SQL写了特别多层嵌套、代码难以阅读我的建议是拆开用临时表或公共表表达式可读性和可维护性反而更好。这里多说一句公共表表达式是JOIN和子查询之外很值得掌握的一个能力。它可以把一段复杂的查询块先命名再在后续查询里引用逻辑清晰且便于调试。虽然本文是概述篇没有大篇幅展开但这个点确实值得你在学完基础语法后尽早接触。至于要避免的低效方法第一是死记硬背方言细节。不同数据库的日期函数、分页语法各不相同背得再熟换个数据库就废了不如把标准SQL的执行逻辑和业务建模思路吃透。第二是迷信一条SQL解决所有问题。有些需求用一条嵌套到天荒地老的SQL硬写出来执行效率低还很难维护不如拆成多条简单SQL在应用层做组合整体会清晰很多。SQL这门语言最迷人的地方在于它让你用声明的思维操作数据你告诉数据库我要什么而不是怎么去取。入门时抓住这个思维转变后面所有深入学习都会顺理成章。数据库系统的后续内容里事务隔离级别、存储引擎、执行计划这些主题都会频繁用到本文的基础概念把SQL这一锤子敲实后面听课和实战都会轻松很多。