SQL核心操作手册：从增删改查到查询优化

数据库和SQL这两个词听起来好像只有后端程序员才要碰但你只要在任何一个网站、后台系统、甚至一个小工具箱项目里做过数据存取就会明白它们才是真正的“地基”。数据库负责把数据规规矩矩地存下来SQL则是你跟它对话的唯一通用语言。今天这篇不打算罗列所有语法而是把最核心的增删改查、查询统计、常见坑位和解决思路串起来相当于一份“SQL核心操作上手手册”。无论你是刚入门的新人、在赶数据库课程设计的学生还是在项目里临时被拉去写报表的测试/运维同学按这条路径走至少能保证日常80%的SQL操作不出岔子。1. 学SQL前先想清楚的三件事1.1 数据库到底在解决什么问题很多人一上来就急着敲命令结果建了两张表就开始卡壳因为不知道自己在干什么。数据库的本质就是一个专门管数据的仓库。它解决的不只是“存下来”而是“存得下、找得到、多个人同时用也不乱”。你可以把数据库想象成一个五星级的快递仓库表是货架行是包裹列是包裹上的标签编号、名称、日期。如果没有仓库数据散落在一堆Excel文件里改了一个忘了一个如果有了仓库则要遵守一套收发货规矩。SQL就是这套规矩的表达方式。所以学习SQL时脑海里始终要挂两个问题这句话涉及哪张表这句话会动到哪些行想明白这两点你就不会把DELETE和UPDATE搞混也不会在写JOIN时不知道自己在联什么。1.2 选哪个数据库练手MySQL、SQLite、SQL Server还是达梦数据库产品很多但核心SQL语法是相通的。我建议入门阶段别贪多选一个主流的练手先把“感觉”找对。数据库适合场景上手难度备注MySQLWeb应用最普及资料多免费低推荐作为第一个练手的库SQLite手机App、嵌入式、本地小工具极低文件即数据库免安装SQL Server企业传统系统、Windows环境中Express版免费常用于老系统PostgreSQL数据分析、复杂查询、开源爱好中功能强近几年热度高达梦数据库国产化项目、政企客户中语法整体贴近Oracle/SQL Server如果你是零基础直接装MySQL就行。要存储临时测试数据的话SQLite更香因为它就是一个文件不需要启动服务很多集成工具里也能直接调用。如果你在公司里碰到的是SQL Server也别慌增删改查那套语法基本一致区别只在分页写法、函数名这些细节上。另外现在很多团队喜欢用“托管数据库服务”也就是云RDS一类底层还是MySQL/PostgreSQL但你不用管安装、备份、扩容厂商帮你看管。学习阶段自己装社区版就能模拟大部分场景以后接托管服务只是连接方式变了一下而已。1.3 学习SQL的核心路径CRUD到查询优化我见过很多人学SQL就是刷了一堆语法结果真拿到一个业务问题不知道先SELECT还是先JOIN。这里我建议按下面这个顺序走建库建表理解数据类型、约束、主键外键。CRUD增INSERT、删DELETE、改UPDATE、查SELECT这是基本功。条件查询WHERE、LIKE、排序ORDER BY、分组GROUP BY。多表操作JOIN、子查询、联合查询。索引与事务理解为什么慢、怎么保证一致。常见故障排查连接失败、慢SQL、锁等待。把这个路径吃透等于把SQL核心操作的骨架搭起来了。后面遇到再冷门的函数你也能顺着文档自己查。2. 表结构设计与基本操作建库、建表、改表2.1 建库时最容易忽略的编码与引擎我辅导过不少课程设计项目一上来就帮你把库建好然后第二天发现中文全变成问号十有八九是字符集没设对。MySQL里最稳妥的做法是统一使用utf8mb4它不仅能存中文还能存emoji和前几年流行的生僻字。别用老掉牙的utf8那个在MySQL里经常会坑你一手。建库语句很简单CREATE DATABASE shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;引擎我建议InnoDB它是MySQL默认引擎支持事务和外键数据安全性高。某些老教程让新手用MyISAM除非是只读的统计表否则不要学一个断电可能就把表搞坏了。2.2 建表必懂的数据类型和约束建表是SQL里最见功力的地方因为字段类型和约束一旦定下来后面想改特别麻烦。重点记住这几类整数INT、BIGINT主键雪崩时记得看范围。小数金额用DECIMAL(10,2)别用FLOAT否则会出现0.10.2不等于0.3的尴尬。字符串定长的CHAR和变长的VARCHAR一般用户名、手机号用VARCHAR。日期时间DATETIME/TIMESTAMP记录创建时间非常常用。约束层面每张表必须想清楚主键。自增INT最常见分布式系统会用UUID或雪花ID。SQL Server里设置默认值GUID时这样写CREATE TABLE Users ( Id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY, UserName NVARCHAR(50) NOT NULL, CreatedAt DATETIME DEFAULT GETDATE() );MySQL对应写法是DEFAULT (UUID())注意这是8.0以后的语法。新手常犯的问题是“每张表都要有主键”这一条没做到。没有主键后续数据更新、去重、性能优化都会很别扭。2.3 修改表结构ALTER、ADD、MODIFY、DROP表建完之后需求变了要加字段这时候就要动ALTER TABLE。-- MySQL ALTER TABLE users ADD COLUMN age INT DEFAULT 0; -- 改字段类型 ALTER TABLE users MODIFY COLUMN username VARCHAR(100); -- 删除字段 ALTER TABLE users DROP COLUMN age;SQL Server里写法略有差别MODIFY COLUMN要改成ALTER COLUMN。不管哪个库生产环境改表都要挑低峰期执行因为大表加列时很多数据库会锁表或者重建表线上请求容易超时。如果是自己在本地折腾随便加但至少心里要有这层警惕。2.4 增删改查INSERT、UPDATE、DELETE、SELECT增删改查四个字英文缩写CRUD是所有应用的核心。别小看它们80%的初级开发事故都出在UPDATE或DELETE忘了带WHERE条件。-- 插入 INSERT INTO users (username, age) VALUES (张三, 20); -- 更新一定带WHERE UPDATE users SET age 21 WHERE username 张三; -- 删除一定带WHERE DELETE FROM users WHERE username 张三; -- 查询 SELECT * FROM users;这里有个新手容易混淆的点DELETE FROM users是删除数据表还在DROP TABLE users是连表一起干掉TRUNCATE TABLE users则是快速清空数据但不可按行回滚。日常操作中尤其要记住UPDATE不带WHERE就是把整张表都改了DELETE不带WHERE就是把整张表都删了。3. 查询才是SQL的主战场过滤、去重、聚合、连接3.1 SELECT与WHERE的常见陷阱很多人的SQL入门就止步于SELECT * FROM tabel实际上业务逻辑全在WHERE和各个函数里。我挑几个高频问题说一下。第一NULL值的判断。SQL里NULL不等于空字符串也不等于0它代表“未知”。常见错误是把判断写成WHERE column NULL这样永远查不到数据必须写成IS NULL。相应的SQL去除空值可以这样SELECT * FROM users WHERE age IS NOT NULL;第二模糊匹配的LIKE。%代表任意长度字符_代表单个字符。比如查姓张的人就是WHERE username LIKE 张%。如果字段本身包含通配符要用ESCAPE转义不过这个用得少。第三条件顺序。SQL语句逻辑上先执行FROM再WHERE再GROUP BY再HAVING最后SELECT。理解这个顺序你就能明白为什么WHERE里不能直接用SELECT里取的别名也能理解ON和WHERE在JOIN时的区别。3.2 排序、去重与分页排序用ORDER BY默认升序ASC要倒序加DESC。去重用DISTINCT。这两个组合起来能解决很多数据清洗需求。-- 查询所有用户年龄去掉重复值 SELECT DISTINCT age FROM users ORDER BY age DESC;注意DISTINCT后面跟多列时是“多列联合不重复”不是分别去重。比如SELECT DISTINCT city, age是城市和年龄都相同才算重复。分页在面试里几乎是必考。不同数据库写法不太一样这点经常让切库的人抓狂MySQL、SQLite、PostgreSQLLIMIT 10 OFFSET 20;表示跳过20条取10条。SQL Server老版本用TOP配合NOT IN或ROW_NUMBER()新版本支持OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY。Oracle、达梦用ROWNUM或官方推荐的FETCH FIRST。分页查询性能也是老话题深分页时OFFSET很大容易慢后面章节会聊优化。3.3 聚合统计与分组统计场景是SQL价值最直观的体现。聚合函数主要有COUNT、SUM、AVG、MAX、MIN配合GROUP BY做分组统计。SELECT city, COUNT(*) AS user_count, AVG(age) AS avg_age FROM users GROUP BY city;这里有个小坑COUNT(*)统计的是行数包括所有列都为NULL的行COUNT(column)则忽略该列为NULL的行。如果你只是想数全表行数用COUNT(*)最直接。GROUP BY之后如果要过滤分组不能再用WHERE得用HAVING。比如筛选用户数大于100的城市SELECT city, COUNT(*) AS user_count FROM users GROUP BY city HAVING COUNT(*) 100;我刚学的时候一直不理解HAVING是干嘛的后来记住一句话WHERE是过滤行HAVING是过滤组。这就够了。3.4 多表连接与子查询JOIN到底怎么用单表查询练熟之后就必须面对多表了。业务数据很少只放一张表订单表要关联用户表明细表要关联商品表。JOIN常见的三种INNER JOIN只取两个表都匹配得上的行。LEFT JOIN左表全保留右表没有匹配时补NULL。RIGHT JOIN右表全保留左表没有匹配时补NULL使用频率比LEFT少。举个例子查询每个订单和下单用户SELECT o.order_id, u.username FROM orders o INNER JOIN users u ON o.user_id u.id;如果订单表里有的user_id在用户表找不到INNER JOIN会把这些订单丢掉如果你想知道哪些订单是“幽灵订单”就得用LEFT JOIN再查右表的字段是不是NULL。子查询就是查询里嵌套查询比如查“年龄大于平均年龄的用户”SELECT * FROM users WHERE age (SELECT AVG(age) FROM users);子查询能解决临时逻辑但过多嵌套会让性能难看。能改写为JOIN的时候优先用JOIN。3.5 原生SQL与ORM什么时候自己写SQL现在很多团队用ORM比如Java圈的MyBatis-Plus、Node圈的Prisma、Python圈的SQLAlchemy。ORM最大的优点是帮你生成模板化SQL少写很多样板代码。不过ORM不是万能的到了复杂报表、多表大查询、批量更新的时候你往往还是要自己写原生SQL。比如Prisma里也支持直接执行查询用$queryRaw方法。我见过不少同学只会ORM不会原生SQL结果一排查慢查询就无从下手。所以别排斥写原生SQL。反过来讲使用ORM时也要知道它生成的SQL长什么样否则看起来是调了个方法实际可能全表扫描。真正的高手是两条腿走路CRUD用ORM复杂查询和优化直接拿SQL说话。4. 实操从零搭一个迷你订单库4.1 业务场景与表结构设计光讲概念太飘我带你实际搭一个小系统。假设我们要做一个“电商订单”场景最少需要四张表用户表存用户信息。商品表存商品名称和价格。订单表记录下单用户、下单时间、总金额。订单明细表记录每一单里包含哪个商品、买了几个。这种设计就是典型的一对多关系一个用户有多张订单一张订单有多条明细。理解了这个关系JOIN查询才有落点。4.2 建库建表SQL完整示例下面这段SQL可以直接在MySQL里执行其他数据库微调即可CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, age INT ); CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(100) NOT NULL, price DECIMAL(10,2) NOT NULL ); CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ); CREATE TABLE order_items ( id INT PRIMARY KEY AUTO_INCREMENT, order_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL DEFAULT 1, FOREIGN KEY (order_id) REFERENCES orders(id), FOREIGN KEY (product_id) REFERENCES products(id) );插入几条测试数据INSERT INTO users (username, age) VALUES (张三, 25), (李四, 30), (王五, 22); INSERT INTO products (product_name, price) VALUES (键盘, 199.00), (鼠标, 89.00); INSERT INTO orders (user_id) VALUES (1), (1), (2); INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 1), (2, 2, 2), (3, 1, 1);注意我把“总金额”放在了订单表里但实际写的时候更推荐在查询时通过明细实时汇总避免冗余字段不一致。这个设计思路叫“反范式”和“范式”之间的取舍入门阶段先理解表结构即可。4.3 用核心查询验证业务逻辑下面这些查询是实际工作里最高频的直接抄作业就行。查询张三下了多少单SELECT COUNT(*) FROM orders WHERE user_id (SELECT id FROM users WHERE username 张三);统计每个用户的订单总金额SELECT u.username, SUM(p.price * oi.quantity) AS total_spent FROM users u LEFT JOIN orders o ON u.id o.user_id LEFT JOIN order_items oi ON o.id oi.order_id LEFT JOIN products p ON oi.product_id p.id GROUP BY u.id, u.username;查询商品销量排行SELECT p.product_name, SUM(oi.quantity) AS sold_quantity FROM order_items oi INNER JOIN products p ON oi.product_id p.id GROUP BY p.id, p.product_name ORDER BY sold_quantity DESC;这三条SQL基本覆盖了JOIN、聚合、分组排序的常见组合。建议你手动敲一遍不要复制粘贴体会一下每一步的结果变化。4.4 分页与统计场景的完整SQL订单表在真实业务里会越来越大分页是刚需。MySQL里查第2页每页5条SELECT * FROM orders ORDER BY id LIMIT 5 OFFSET 5;如果你想看每个用户最近一笔订单的时间可以这样SELECT user_id, MAX(created_at) AS last_order_time FROM orders GROUP BY user_id;如果还要把用户信息带出来用子查询或JOIN都行。我自己习惯先把单表查清楚再加JOIN避免一上来就写一长串把自己绕晕。5. 常见问题排查与优化连接失败、慢SQL、数据安全5.1 连不上数据库先按这五步排查SQL写得再溜连不上数据库也白搭。我处理过各种“无法连接到SQL Server”“sqlplus登录Oracle缓慢”的报错基本上按照下面这个顺序排查能解决大部分问题。第一确认服务启动了。Windows上SQL Server服务经常因为更新或密码策略而停止报错“找不到数据库引擎启动句柄”时先去服务管理器里看SQL Server服务状态。密码到期也是高频问题尤其SQL Server 2012以后如果启用了密码策略账号密码到期后连接会直接失败。第二确认网络通不通。能ping通不代表端口通要用telnet IP 1433SQL Server默认端口或tnspingOracle测试。云服务器上的数据库还要注意安全组和防火墙规则。第三确认账号权限。很多软件报“用户名或密码错误”其实是密码里带特殊字符没转义或者服务账号被锁定。第四看连接池。如果应用用的是MySQL连接池突然出现大量获取连接超时可能是连接池最大连接数被打满也可能是有慢SQL把连接占着不还。这时重启应用只是临时解药根源要查慢查询。第五找驱动问题。比如Office的Access数据库引擎64位版本和32位版本经常打架。常见报错是“请先安装Access数据库64位系统驱动程序”或者“64位引擎不支持DBC数据只支持Access数据”。解决办法就是把Office组件和你的应用位数对齐别一个64位一个32位。5.2 慢SQL优化先看执行计划再加索引SQL慢最直观的现象就是接口超时、数据库CPU飙升。优化之前先找到慢在哪MySQL里开慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;找到慢SQL之后用EXPLAIN看执行计划。重点看type列如果是ALL就是全表扫描基本可以确定缺索引。常见的优化手段是给WHERE、ORDER BY、JOIN关联字段加索引。避免SELECT *只取需要的列减少回表。避免在索引列上做函数运算否则索引会失效。比如WHERE DATE(created_at) 2024-01-01很难走索引应该改成范围条件。分页深的查询可以先用覆盖索引定位主键再回表取数据。现在数据库优化器越来越智能但你的SQL逻辑写得差优化器也救不回来。行话叫“并行SQL优化”实际上就是让复杂查询拆解或利用多核并行执行这个后面深入学再说入门先把索引用好。5.3 数据类型与空值处理的坑跨数据库写SQL函数差异最让人头疼。比如DB2里要判断一个字符串是不是纯数字不能用MySQL的REGEXP直接套得借助DB2的TRANSLATE函数把数字字符替换掉再判断剩余字符串是否为空。不同库的解决方案不同这也是为什么你在网上搜一个函数总能看到“Oracle版”“SQL Server版”“DB2版”好几份答案。空值的处理也容易踩雷。NULL和空字符串是两个概念统计时最好统一。比如用COALESCE把NULL转成默认值SELECT username, COALESCE(age, 0) AS age FROM users;还有去重时如果字段里有NULLDISTINCT会保留一个NULL并不会自动去掉。数据清洗时要根据业务决定是保留还是填充。5.4 安全底线SQL注入与权限控制SQL注入是老生常谈但每年还在不断出事。它的原理就是攻击者把恶意SQL代码拼接进参数然后代码执行了。最典型的“万能密码绕过”就是注入的一种表现所以绝对不能把用户输入直接拼进SQL字符串。安全的做法永远是用参数化查询。ORM和大多数编程语言驱动都支持比如JDBC的PreparedStatement、Python的?占位符、Prisma的原生SQL用$queryRaw传参。权限上只给应用账号最小权限能查不能删就绝不放开。尤其在生产环境root账号不要裸奔一定要设强密码和IP白名单。5.5 数据库同步与备份小工具数据不备份等于拿业务开玩笑。入门阶段可能用不到太复杂的同步方案但要了解两个方向一是备份恢复二是同步复制。单机MySQL常用mysqldump导出SQL文件企业里主库写、从库读就是主从同步靠二进制日志实现。市面上也有很多“数据库同步工具”和GUI管理软件可以让跨库同步、结构对比变得省力。比如有些小型管理工具能同时连接多种数据库一键对比表结构。这些工具适合快速操作但底层原理还是那几条SQL和日志机制。我的建议是工具可以提升效率但核心概念越早自己动手理解越好。6. 我的几条建议最后说点实在的。我见过太多人学SQL卡在“背语法”上其实SQL是练出来的手艺最好的方式就是拿一份真实数据哪怕是自己瞎编的订单数据反复做筛选、分组、关联把每一个查询结果都跟业务对一遍。遇到报错别慌先把错误信息复制到搜索引擎看前几个词是什么意思再看官方文档。很多所谓疑难杂症其实只是端口没开、服务没起、驱动版本不对。把这三板斧练熟你已经超过不少人了。还有个小技巧建表的时候给字段写注释尤其是字段含义、单位、取值范围。当时偷懒不写三个月后自己都会忘。我在实际项目里吃过太多这种亏后来养成了“数据库字典随手记”的习惯真的能救命。