做数据库这么些年来我最大的体会就是技术圈里从来不缺高级玩法但真正把基本操作练到极致的人反而越来越少。尤其是数据库技术这个体系新手最容易犯的毛病就是一上来就盯着索引优化、分库分表、读写分离这些大招结果连最基础的CREATE TABLE语句都写不规整连一次简单的UPDATE都可能酿成生产事故。这篇文章我想好好聊聊数据库技术里的基本操作到底该怎么学、怎么做。不是给你背概念而是站在一个常年跟SQL打交道的人的角度把建库建表、增删改查、约束设计、索引使用、权限控制这些最底层的能力拆开揉碎讲清楚。这套内容既适合刚准备跨入数据库大门的初学者也适合那些写过几年SQL、但始终觉得底子不够扎实的开发人员。不管你是为了应付三级数据库技术真题还是真想干好手上的活把基本操作夯实了后面的路才走得稳。1. 数据库基本功为什么如此重要数据库的基本操作通俗点说就是一套和数据打交道的规范动作。你往小了看它不过是一句INSERT、一条SELECT往大了看它是整个系统的地基。几乎所有业务系统的复杂度到最后都会映射到数据库的表结构设计和数据操作上。我见过太多翻车现场比如业务上需要男性用户数据结果SELECT语句里过滤条件写错把NULL值也当成合法记录捞了出来直接导致报表数据对不上账。后台误操作一条UPDATE忘记带WHERE条件全表数据被批量覆盖又没有备份只能干瞪眼。一个简单的订单表因为没有合理设置主键和约束出现了大量重复数据整个业务链全被带偏。这些问题有一个共同点都不是因为技术多高深而是因为基本操作没做扎实。数据库的索引、事务、引擎原理这些内容再复杂底层都构建在一系列基本操作之上。你的建表语句有问题后续性能调优就是空谈你的增删改查写得不严谨业务逻辑再完善也会被数据错误击穿。所以我特别建议把基本操作当成一门独立的功夫来练而不是当作简单到不值得花时间的内容。它的核心价值在于建立一套肌肉记忆让你在写任何SQL语句时都能下意识地考虑三件事表结构是否合理条件是否精确影响范围是否可控这种习惯才是区分一个普通写SQL的人和真正的数据库从业者的分水岭。2. 环境准备与工具选型在开始造表、写SQL之前先把环境搭好。这里给大家一套我个人比较推荐的起步方案不需要太复杂但一定要顺手。2.1 数据库产品的选择主流的数据库产品很多MySQL、PostgreSQL、SQL Server、Oracle各有拥趸。对于学习和日常项目来说MySQL是首选。理由很现实资料最全学习成本低遇到问题搜一下基本都有答案。部署简单内存占用相对可控普通笔记本就能跑。商业环境使用率高很多中小型系统和一线互联网公司的业务库都用它学了不亏。PostgreSQL在复杂查询和地理信息处理上更强但作为基本操作的入门载体MySQL已经足够。SQL Server更偏向Windows生态如果你所在的团队明确要用再针对性上手也不迟。我的建议是入门阶段死磕一种数据库把基本操作练到条件反射后面扩展其他产品会非常快。千万不要今天装MySQL明天换PostgreSQL后天又去折腾Oracle最后每个都只会皮毛。2.2 客户端工具推荐有了数据库服务还需要一个趁手的操作界面。很多新手喜欢用命令行甚至把会不会用黑窗口当作衡量技术水平的标准这个观念我不赞成。命令行有它的优势比如在服务器上排查问题但你不可能指望业务人员也用命令行提数。我常用两种方式搭配Navicat图形化工具里的老牌选手操作直观方便调试SQL表结构、索引、数据一览无余。缺点是收费但学习期可以用社区版或试用版顶着。DBeaver免费开源的通用数据库管理工具跨平台支持很好功能不输商业软件对新手非常友好。工具本身不重要重要的是通过工具去理解数据到底存成了什么样。图形化界面能让你建立直观感受比如建表时你能看到字段类型、长度、默认值一个个怎么填这在初期非常重要。2.3 初始化一个练习库环境搭好后先别急着写代码。我强烈建议你手动建一个专用练习库而不是直接拿业务库练手。-- 创建练习数据库 CREATE DATABASE IF NOT EXISTS db_practice DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE db_practice;这里有两个细节值得注意字符集选择utf8mb4很多老教程还在用utf8但它存不了emoji和部分生僻汉字容易踩坑。utf8mb4是utf8的超集表现力更强是当下主流选择。排序规则选utf8mb4_general_ci大小写不敏感适合中英文混合场景。如果你有特殊的排序要求再考虑别的规则。建好库接下来就是重头戏表结构设计与DDL操作。3. 表结构与字段设计是操作的地基在动手增删改查之前必须先把表设计这块搞定。很多初学者一上来就疯狂SELECT却不知道SELECT所依赖的这张表本身可能就设计得不合理。表结构错了后面怎么写SQL都是别扭的。3.1 字段类型选择数据库字段类型五花八门但日常最常用的就那几类。我按实践频率给你们盘点一下整型INT、BIGINT。主键ID、数量、状态值这些都用它。注意INT上限约21.5亿如果是用户表、订单表这类增长极快的数据直接上BIGINT省得将来迁移。小数DECIMAL。价格、金额、百分比等必须用DECIMAL千万别用FLOAT或DOUBLE浮点数会有精度丢失账算不对是大事。字符串VARCHAR。名字、地址、备注等变长数据。重点是长度别拍脑袋按业务最大值上浮一点就行。比如手机号就VARCHAR(20)绰绰有余但别一上来就VARCHAR(5000)浪费存储而且索引效率会降低。日期时间DATETIME、TIMESTAMP。创建时间、更新时间、订单日期等。一般推荐DATETIME范围更大不受时区影响。布尔类型BOOLEAN。用TINYINT(1)就能实现0代表否1代表是。有些团队习惯用CHAR(1)存Y/N也不是不行但统计时不如TINYINT方便。3.2 主键与自增每张表都要有主键这就像每个人的身份证号是唯一识别一条记录的关键。通常我直接用自增主键CREATE TABLE student ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键ID, student_no VARCHAR(20) NOT NULL COMMENT 学号, name VARCHAR(50) NOT NULL COMMENT 姓名, gender TINYINT(1) NOT NULL DEFAULT 0 COMMENT 性别0-未知 1-男 2-女, birth_date DATE DEFAULT NULL COMMENT 出生日期, class_id BIGINT DEFAULT NULL COMMENT 班级ID, created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_student_no (student_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生表;几个值得细说的地方AUTO_INCREMENT做主键优点是自动生成、有序、占用空间小。缺点是分布式场景下会有ID冲突问题但那是中大型系统才需要考虑的事单体应用阶段放心用。唯一约束学号虽然不作为主键但业务上也不允许重复所以加了UNIQUE KEY。这就是天然主键和代理主键的经典组合主键用自增ID业务唯一键用学号来约束。必填字段学生姓名、学号这些加上NOT NULL避免脏数据。自动时间戳created_at和updated_at让代码里少写很多冗余逻辑尤其是更新时间自动更新非常省心。3.3 外键用不用外键是个争议话题。教科书里总是强调外键但在真实开发和维护中越来越多的团队选择不用物理外键只在应用层逻辑上保证参照关系。原因是物理外键会增加数据写入时的校验开销还可能引发锁竞争。比如订单表引用用户表如果加了物理外键每次插入订单都要去用户表检查用户是否存在高并发下这是累赘。但不用物理外键不等于不建模关系。在设计表时像class_id、user_id这些关联字段该留还是要留保持清晰的逻辑关联。具体约束让业务代码去控制数据库层更轻盈。提示如果是做三级数据库技术相关的考试题外键概念必须会因为考察的是理论基础如果是实际项目开发优先权衡性能和可维护性。4. 增删改查背后的门道建好表之后就进入了最核心的DDL和DML操作环节。增删改查CRUD听起来简单但在每一个操作里都藏着一堆小而关键的坑。4.1 INSERT插入数据不能只会一种写法插入数据的常规写法长这样INSERT INTO student (student_no, name, gender, birth_date, class_id) VALUES (20240001, 张三, 1, 2006-03-15, 101);一次插入一条太浪费。批量插入才是实战中的常见姿势INSERT INTO student (student_no, name, gender, birth_date, class_id) VALUES (20240002, 李四, 2, 2006-07-21, 101), (20240003, 王五, 1, 2006-01-11, 102), (20240004, 赵六, 2, 2006-12-02, 102);这里有个非常实用的技巧如果遇到唯一键冲突想保留原记录不报错或者更新部分字段可以用ON DUPLICATE KEY UPDATE。INSERT INTO student (student_no, name, gender, birth_date, class_id) VALUES (20240001, 张三, 1, 2006-03-15, 103) ON DUPLICATE KEY UPDATE name 张三, class_id 103;这个语句的作用是学号20240001已经存在所以不重新插入而是把班级改成103。这在数据同步、重复提交场景下非常实用省得你先查一遍再决定INSERT还是UPDATE。4.2 SELECT查询绝不是SELECT *就完事我见过太多开发人员写线上查询语句一上来就是SELECT *然后把所有字段查出来丢给前端。这在小数据量阶段没问题但表一大了数据多了这种习惯就很危险。正确做法是只查你需要的字段SELECT id, student_no, name FROM student WHERE class_id 101;WHERE条件写的好坏直接决定查询准确性。举几个高频场景-- 使用IN匹配多个值 SELECT * FROM student WHERE class_id IN (101, 102); -- 使用LIKE做模糊匹配注意前置通配符会导致索引失效 SELECT * FROM student WHERE name LIKE 张%; -- 使用范围查询 SELECT * FROM student WHERE birth_date BETWEEN 2006-01-01 AND 2006-12-31; -- 使用AND和OR组合注意括号优先级 SELECT * FROM student WHERE (gender 1 OR class_id 102) AND name ! 王五;在OR条件中一定要加括号不然SQL会按从左到右的运算规则把条件组合成另一种意思很容易踩坑。分组与聚合也是基本操作中的重点比如统计每个班人数SELECT class_id, COUNT(*) AS cnt FROM student GROUP BY class_id HAVING cnt 1;HAVING和WHERE的区别一定要分清楚WHERE是在分组前过滤原始行HAVING是在分组后过滤聚合结果。排序与分页SELECT id, name, class_id FROM student ORDER BY class_id ASC, id DESC LIMIT 10 OFFSET 20;LIMIT后面的两个数字前一个是偏移量后一个是返回条数。OFFSET越大翻页越慢这是后话了。4.3 UPDATE最危险的语句如果说SELECT是查询中最常用的那UPDATE就是最危险的。原因很简单它改数据而且默认情况下不会自动回滚。一个标准且安全的UPDATE长这样UPDATE student SET class_id 105 WHERE id 3301;但下面这种写法就是灾难-- 危险没有WHERE条件全表更新 UPDATE student SET class_id 105;我在培训和带新人的过程中几乎每届都有同学在测试环境中干过这种事。在测试环境还好如果连的是生产库没有提前备份那这些数据就全完了。养成几个好习惯可以避免大部分事故UPDATE之前先用同条件SELECT确认要改的数据范围比如先执行SELECT COUNT(*) FROM student WHERE ...。尽量用主键或唯一索引作为WHERE条件。生产库执行前务必导出相关表备份。有条件就开事务执行后仔细核对影响行数再COMMIT。START TRANSACTION; UPDATE student SET gender 2 WHERE id 3321; -- 核对结束后再执行COMMIT; -- 如果发现不对执行ROLLBACK;这种写法给操作上了一道保险非常建议在非交互式脚本里使用。4.4 DELETE删除数据要讲究策略DELETE的风险跟UPDATE一脉相承没有WHERE条件就是全表删除。不用重复强调这里聊聊更多细节TRUNCATE和DELETE的区别TRUNCATE是清空表速度极快但无法按条件删除且不会逐行记录日志DELETE支持WHERE条件也可以加LIMIT限制删除行数。大批量删除要分批一次性删除几十万条数据可能导致行锁竞争、主从延迟等问题。如果你确实需要清理大量旧数据建议用循环分批删除每批几百条中间sleep几秒。-- 分批删除示例 DELETE FROM operation_log WHERE created_at 2024-01-01 LIMIT 500;循环执行上述语句直到影响行数为0为止。这是运维场景里常用的思路别图省事一条语句删到底。5. 约束、索引与视图的实用技巧建表只是第一关约束和索引才是决定这张表好不好用的关键。好的约束和索引能让数据质量提升一个档次也让你写SQL时少很多麻烦。5.1 推荐的约束组合第一张学生表里已经涉及主键约束、非空约束、唯一约束。围绕实际业务还能有更多组合默认值约束控制字段没有传入值时的兜底行为。比如status字段默认1防止漏填变成NULL。CHECK约束一些数据库对CHECK只是形式上存在MySQL旧版本执行CHECK会报错所以不要依赖数据库做太复杂的业务规则校验。联合唯一约束比如班级内学号不重复可以建联合唯一索引UNIQUE KEY uk_class_student (class_id, student_no)。5.2 索引的建立与失效场景索引的本质是字典的目录有了它按目录查数据快。但乱建索引、对索引列做函数运算、隐式类型转换都会让索引失效。常见失效场景-- 对索引列使用函数 SELECT * FROM student WHERE DATE(created_at) 2024-06-01; -- 隐式类型转换 SELECT * FROM student WHERE student_no 20240001; -- 前置模糊匹配 SELECT * FROM student WHERE name LIKE %张;这些在SQL逻辑上没错但会让数据库放弃走索引转成全表扫描。数据量大时一条看似简单的查询可能把数据库拖垮。关于索引我给一条非常朴素的经验主键索引必须有业务高频条件要建索引区分度不高的字段比如gender性别建索引意义不大。5.3 视图的合理使用视图是保存好的SQL查询结果使用起来像表但它并不真实存储数据普通视图。它在基本操作中的价值是把复杂的、经常重复的查询封装起来对外只暴露一个简单的表名。CREATE VIEW v_student_info AS SELECT s.id, s.student_no, s.name, c.class_name FROM student s LEFT JOIN class c ON s.class_id c.id;之后你只需要查视图SELECT * FROM v_student_info WHERE class_name 302班;这能让代码更优雅。但注意视图会掩盖底层表结构在排查问题时反而可能增加难度别滥用。一般逻辑复杂、复用频率高的查询才值得建视图。6. 事务与权限的基本操作事务和权限是基本操作中容易被忽视但又极其关键的部分。尤其是事务这个概念很多新手只知道有COMMIT和ROLLBACK却不知道为什么必须有它。6.1 事务的ACID特性事务是一组SQL操作要么全部成功要么全部回滚。经典的转账场景START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; UPDATE account SET balance balance 100 WHERE user_id 2; -- 如果两个UPDATE都成功再COMMIT COMMIT; -- 如果中间任何一步异常则ROLLBACK -- ROLLBACK;这个操作背后的核心有四个特性原子性这一组操作不可分割要么都做要么都不做。一致性转账前后总金额保持不变。隔离性多个事务并发执行时互不干扰。持久性事务一旦提交数据就要永久保存。实际开发中隔离级别是决定数据库并发行为的重要参数。MySQL默认的REPEATABLE READ能解决大部分问题但不同的场景可能需要调整隔离级别。提示事务使用不当比如事务内执行了慢查询长时间不COMMIT会锁住大量记录引起线上业务堵塞。保持事务尽量短小精悍是基本操作里很重要的原则。6.2 用户与权限的管控很多初级开发人员对数据库权限漠不关心一上来就用root账户干所有事。这是非常不好的习惯。生产环境中不同角色的数据库账号应该只有完成自己工作所需的最小权限。以MySQL为例常见操作-- 创建用户 CREATE USER app_user% IDENTIFIED BY StrongPassword123; -- 授权只允许操作db_practice库的所有表 GRANT SELECT, INSERT, UPDATE, DELETE ON db_practice.* TO app_user%; -- 撤销权限 REVOKE DELETE ON db_practice.* FROM app_user%; -- 刷新权限 FLUSH PRIVILEGES;权限划分的逻辑其实就是权限最小化原则。通俗地讲只给客户吃饭的筷子别把厨房钥匙交出去。对线上库进行严格权限管控能显著降低误操作和数据泄露风险。7. 数据备份与恢复的基本操作提到基本操作很多人会忽略备份与恢复。但你必须明白数据库里最可怕的事情不是写错一条SQL而是数据没了救不回来。备份这件事平时不做等于白做。7.1 常用备份方式MySQL中常用的逻辑备份工具是mysqldumpmysqldump -u root -p db_practice /backup/db_practice_20241001.sql恢复则用mysql -u root -p db_practice /backup/db_practice_20241001.sql这种方式生成的是SQL文本占空间较大但可读性强适合中小型数据量。对于更复杂的业务线上往往会开启binlog结合全量备份实现时间点恢复Point-in-Time Recovery。这是比较进阶的内容但思想值得了解全量备份加日志回放能让你把数据恢复到任意一个时间点。7.2 备份策略的建议我的建议非常简单数据库每天至少做一次全量备份。备份文件要异地存放不能跟数据库在同一台机器上机器挂了还有退路。定期做恢复演练别等灾难发生才发现备份文件是坏的。备份不是形式主义它是数据库操作者的最后一道安全护栏。很多事故你只要及时恢复了就不再是事故。8. 常见问题与排查技巧实录基本操作写到最后我整理几个高频踩坑现场大家可以对照自查。问题现场根本原因解决建议插入中文乱码数据库连接字符集与表字符集不一致统一使用utf8mb4连接串明确指定characterEncodingutf8UPDATE没带WHERE全表数据被改疏忽或没有养成确认条件习惯操作前先SELECT核对范围脚本里强制开事务查询越来越慢缺少索引或索引失效用EXPLAIN分析执行计划针对性建索引并发写入导致死锁事务过长、加锁顺序不一致缩短事务固定操作表的顺序减少锁竞争误删表后无法恢复没有备份必须建立备份机制重要环境尽量开启binlogCOUNT非常慢数据量大且未做分页不要频繁COUNT全表考虑估算或走汇总表这里重点说一下EXPLAIN的日常用法EXPLAIN SELECT * FROM student WHERE student_no 20240001;看输出结果里的type列如果出现ALL说明走的是全表扫描数据量一大就会慢。如果是const或ref说明能命中主键或普通索引性能尚可。养成在慢查询前加EXPLAIN解释的习惯比盲目加索引可靠得多。还有一个非常容易被忽略的问题字符串与整型比较导致的隐式转换。当字段是VARCHAR传入参数是数字MySQL可能把字段转成数字再比较导致索引失效。上面那张表里student_no是VARCHAR如果你执行WHERE student_no 20240001就会隐式转换索引失效。正确写法是加引号WHERE student_no 20240001。9. 基本操作的进阶演练建议写到这基本操作的主体内容都覆盖了。最后分享一点我自己的实操心得。从三级数据库技术真题的角度看考试和真实工作有一定差异。考试更偏向理论概念和规范书写而实际开发更看重结果正确与性能可接受。但底层是一回事你要能熟练地写出规范的DDL、DML语句能理解表与表之间的关联约束能预判一条SQL会影响到哪些数据。这比会背一堆概念有用得多。我建议你按这个顺序练独立建一套完整的业务表比如简单的学生选课系统包含学生表、课程表、选课表合理设计主外键、索引、字符集。通过INSERT批量填充数据数量最好达到一万条以上。练习各种SELECT统计、分组、多表关联把每一条查询结果都和预期核对一遍。实际执行UPDATE和DELETE练习事务回滚体会数据变化的节奏。执行一条不带WHERE条件的UPDATE然后用备份恢复建立起真正的敬畏心。这些步骤全部走完你对数据库基本操作的理解会上一个台阶。我自己带新人时基本也是这么要求的效果比单纯看书快得多。数据库技术这门学问越往深走越有意思但所有高级操作都建立在基本操作不出错这个前提上。把地基打得扎实一点后面你才能放心大胆地折腾性能优化、分布式架构这些东西。
