拓冰网站定制 
上午9点整我按计划翻到MySQL学习笔记第34节主题是创建数据库与运行各类SQL。原以为这一节很简单无非就是CREATE DATABASE、CREATE TABLE再加上增删改查结果真正动手敲命令的时候发现光是一个字符集设置就能牵扯出一堆历史问题。这一节我全程用命令行操作对比了图形化工具的差异把建库、建表、DML、事务、JOIN查询全部过了一遍也顺手记录了好几个报错现场。这篇笔记适合两种人看一种是刚入门、准备动手写第一条SQL的新手另一种是用惯了Navicat、突然被迫回到命令行但总被报错卡住的老手。我尽量把当天的操作顺序和踩坑细节都写出来你们可以直接照着敲。1. 上午的学习目标为什么要把建库放在第一节先交代一下背景。我在2026年3月4日之前已经把MySQL的安装配置、基本架构、常用客户端工具这些前置内容过了一遍装的是MySQL 8.0系列。当时对SQL的认知停留在“看得懂、写不出”的阶段SELECT * FROM xxx能看明白但真让我建一个库、设计一张表手是僵的。所以第34节的学习目标很明确把数据库生命周期里最常用的一套操作串起来从创建数据库开始到建表、插入数据、查询、修改、删除、提交事务为止。1.1 为什么建议第一条命令在命令行敲我知道很多人习惯打开Navicat鼠标点几下就把库建了、表也建了可视化确实舒服。但我个人强烈建议初学阶段至少在命令行里完整走一遍流程。原因很直接图形化工具把你的操作包装成了“点击”它替你执行了SQL但你完全看不到SQL本身更看不懂报错。一旦生产环境没有图形化客户端或者在服务器上排查问题你连mysql -u root -p都不知道怎么敲那就尴尬了。命令行还有一个好处是“所见即所得”。你输入CREATE DATABASE school;系统回那句Query OK, 1 row affected你会实实在在感觉到“哦命令生效了”。这种正反馈很重要它把抽象操作变得具体。图形化工具当然可以留着用但先会命令行再回去用工具你才知道工具背后到底做了什么。我那天上午至少有四十分钟是在命令行里度过的全程只打开一个终端窗口非常专注。1.2 先确认环境没问题动手之前先确认MySQL服务正常。Windows环境下我习惯用服务管理器看MySQL服务状态Linux下会用systemctl status mysql。如果服务没起来后面所有命令都会报错“Cant connect to MySQL server”那会儿千万别慌不是你SQL写错了是服务端压根没上线。mysql -u root -p输入密码后出现mysql提示符就说明连接成功了。我习惯先用SELECT VERSION();确认版本信息用SELECT NOW();确认当前时间这两个命令不起眼却能在学习过程中反复帮你确认连接没有被断开。版本差异对语法影响真的很大比如MySQL 5.7和8.0在字符集默认值、认证插件上都不一样同样是建库语句在不同版本下默认行为会差一截。2. 创建数据库语法简单细节不少这一节的名字就叫“创建数据库”所以第一节实操内容自然从CREATE DATABASE开始。语法很短就那么一句话但牵扯出来的字符集、排序规则、安全删除等问题全是可以展开讲的素材。2.1 最基本的CREATE DATABASE写法先看一个最干净的命令CREATE DATABASE school;执行完以后用SHOW DATABASES;查看当前实例下有哪些数据库你会发现多了一个school。这里的逻辑和文件系统非常像你可以把MySQL实例理解成一个文件夹下面挂着多个数据库每个数据库里又有若干张表。创建数据库不需要任何权限之外的东西但有个细节很多人会忽略如果这个库已经存在直接执行会报错“ERROR 1007 (HY000): Cant create database school; database exists”。解决办法有两个要么先DROP再CREATE但危险要么用IF NOT EXISTS修饰CREATE DATABASE IF NOT EXISTS school;这样系统会先检查存在就跳过不存在就新建不会报错。我个人的习惯是所有破坏性命令尽量加上类似的安全判断防止误操作。DROP DATABASE同理可以写成DROP DATABASE IF EXISTS school;虽然这个命令我一天也就用那么几次。2.2 字符集和排序规则为什么重要这是整节笔记里最值得展开的部分。在MySQL 8.0之前数据库默认字符集通常是latin1只能存英文和西欧字符存中文会变成问号或乱码。MySQL 8.0开始默认字符集变成了utf8mb4但这只是默认值不代表你可以完全不管。字符集决定了数据库能存哪些字符。utf8mb4支持完整的Unicode包括中文、日文、韩文还包括emoji。要注意的是MySQL以前的utf8实际是utf8mb3它最多支持3字节像emoji这种4字节字符存不进去所以从兼容性角度出发建库时我会明确指定CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;这里有个非常眼熟的参数utf8mb4_unicode_ci。它属于排序规则影响的是比较和排序结果。_ci结尾表示大小写不敏感case insensitive所以SELECT * FROM user WHERE name Alice能匹配到alice。如果你希望区分大小写可以选utf8mb4_bin。排序规则的选择建议新项目直接用utf8mb4_unicode_ci它对多语言的支持更全面如果团队规定用utf8mb4_general_ci也行它在部分老版本里性能稍好但差异已经微乎其微。按照MySQL官方文档的发展趋势老旧的排序规则以后会被逐渐淘汰选新一点的方案更稳妥。2.3 修改与删除数据库时容易忽略的点数据库创建之后可能因为规划失误需要改字符集。千万别用ALTER DATABASE乱改先想清楚影响范围因为改了字符集之后已存在表的字符集不会跟着变它们仍然用建表时指定的字符集很容易出现一张库里的表各用各的字符集查出来全是乱码。ALTER DATABASE school CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;这个命令只影响ALTER之后新建的表对老表不管用。要彻底统一需要逐表执行ALTER TABLE ... CONVERT TO CHARACTER SET这个操作耗时又锁表生产环境务必避开业务高峰期。上午学习时我在一个测试环境里试了一遍深刻体会到“建库时选对字符集”比“建库后改字符集”省事一百倍。删除数据库也有讲究。DROP DATABASE是彻底删除连库带表全没一旦执行错后果直接。所以我反复强调先SHOW DATABASES;看一眼再用USExxx;切进去确认最后才执行DROP。学习阶段没关系生产环境里DROP一条命令可能直接把一个月的业务数据清零没有备份就别碰。3. 建表才是DDL的真正重头戏创建数据库只是开了个头接下来建表才是DDLData Definition Language的重点。表结构设计直接决定后面所有SQL好不好写、查询快不快以及数据会不会出现冗余和异常。3.1 字段类型先选对再谈优化我当天建了一张学生表用来练习。建表之前我把常用字段类型列了一遍对照选型前面几列是设计阶段必须敲定的比如age字段我用了TINYINT UNSIGNED年龄不会超过255用INT纯属浪费空间。price这类字段如果涉及金额很多资料推荐DECIMAL而不是FLOAT因为浮点数有精度误差涉及钱的时候一分都不能差DECIMAL是定点数不会有这种问题。id字段习惯用INT UNSIGNED AUTO_INCREMENT让它自增不用手工维护。CREATE TABLE students ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, gender ENUM(男, 女) DEFAULT 男, age TINYINT UNSIGNED DEFAULT 20, email VARCHAR(100), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里面有四个点值得单独说。第一VARCHAR要设定一个合理的最大长度别一上来就VARCHAR(255)存储引擎实际占用的空间和长度定义有关。第二ENUM类型看起来方便但后期如果要加一个“保密”选项就得改表结构业务紧的时候容易误事所以我个人更建议用TINYINT映射状态灵活性更高。第三DEFAULT CURRENT_TIMESTAMP能省掉很多手填时间的麻烦这个默认值在MySQL 5.6之后就用得很普遍。第四ENGINEInnoDB必须显式指定虽然MySQL 8.0默认就是InnoDB但写得清楚以后看建表语句的人不需要猜。3.2 约束主键、唯一键、非空表里的约束决定了数据的合法性。我最先关注的是主键约束PRIMARY KEY (id)保证每行数据的唯一标识同时自带索引查询走主键速度最快。接着是唯一键约束。如果邮箱不允许重复可以加一个UNIQUE KEY这样插入重复邮箱时直接报错。我在学习时故意插入两条相同email看着系统报出“Duplicate entry”才真正理解了唯一键的作用。非空约束NOT NULL也很常用。业务上要求name必须有值所以建表时直接禁用NULL。但要注意空字符串和NULL是两种概念NULL表示值不存在表示值存在、只是内容为空。很多新手在这里踩坑明明表结构里允许NULL查询结果却总出现意外过滤。我的习惯是能用NOT NULL的字段尽量用NOT NULL配合DEFAULT值能省掉一堆判空逻辑。CREATE TABLE course_selections ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, student_id INT UNSIGNED NOT NULL, course_id INT UNSIGNED NOT NULL, score DECIMAL(5,2), PRIMARY KEY (id), UNIQUE KEY uk_student_course (student_id, course_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这张选课表里UNIQUE KEY (student_id, course_id)很有意思它组成了一个联合唯一约束意思是同一名学生选同一门课只能出现一次。这在业务上非常合理也顺便为下一步练习JOIN查询建好了关系表。3.3 ALTER TABLE修改表结构的几种常用场景建表几乎不可能一次到位后面经常要加字段、改字段。ALTER TABLE是我第34节里练习得最多的命令之一。ALTER TABLE students ADD COLUMN phone VARCHAR(20) AFTER email;ADD COLUMN是加字段AFTER email表示把新字段插到email后面。如果不写AFTER新字段默认加在表末尾。这里有个不值得提倡但很多人用的操作为了调整字段位置而反复ADD和DROP在表数据量大的时候会花很长时间还会产生大量undo日志。ALTER TABLE students MODIFY COLUMN phone VARCHAR(30);MODIFY COLUMN用来改变字段类型或约束。它会把整列重写一遍所以大表上执行时要慎重。如果师只想改字段名用CHANGE COLUMN old_name new_name 类型注意CHANGE必须重新声明类型不能只改名字。ALTER TABLE students DROP COLUMN phone;DROP COLUMN删字段。数据量大的表删除字段时InnoDB会标记该列为“不可用”实际释放空间还要等一段时间中间那段时间如果你执行了OPTIMIZE TABLE会短暂占用额外的磁盘空间。这些细节日常开发时你可能感受不到但在生产环境维护的时候少一个坑就能少一次凌晨三点被叫起来处理问题的经历。4. DML四件套INSERT、SELECT、UPDATE、DELETE数据库建好、表建好接下来的练习重点全部移到DMLData Manipulation Language上。增删改查四个字看起来简单真正写的时候才体会到细节决定成败。4.1 INSERT的几种写法插入数据最基本的写法是INSERT INTO students (name, gender, age, email) VALUES (张伟, 男, 20, zhangweiexample.com);字段列表和VALUES值列表必须一一对应数量、顺序都不能错。我一开始手抖写少了一个值直接报错“Column count doesnt match value count at row 1”这个报错信息其实已经说得很明白了列数对不上。除了单行插入还可以一次插多行INSERT INTO students (name, gender, age, email) VALUES (李娜, 女, 21, linaexample.com), (王强, 男, 19, wangqiangexample.com), (赵敏, 女, 22, zhaominexample.com);一次插多行的效率比循环执行多次高得多因为减少了客户端和服务器之间的网络交互。学习阶段可能感受不到差别但在批量导入数据、比如从Excel清洗完数据再灌入数据库时这个技巧非常实用。另外INSERT语句还支持INSERT INTO ... SELECT ...这种写法把一张表的查询结果直接插入另一张表做数据迁移、备份表时很常用。4.2 SELECT排序、去重与条件过滤查询是SQL里出场率最高的部分。我当天的练习顺序是全表查询→条件查询→排序→去重→分页。SELECT * FROM students;SELECT *不建议在生产环境随便用尤其是表很大、网络带宽有限的时候它会把所有列的数据全捞回来白白消耗资源。我更习惯显式列出需要的字段SELECT id, name, age FROM students WHERE age 20 ORDER BY age DESC LIMIT 10;这条语句包含了三个核心子句WHERE做条件过滤ORDER BY age DESC做倒序排序LIMIT 10限定只返回前10行。执行顺序上MySQL先找到满足WHERE条件的行再排序最后取前10条理解这个顺序对后续优化大有用处。去重用DISTINCT这也是标题里提到的热搜词“sql语句去重”。我特意试了一遍SELECT DISTINCT age FROM students;它能去掉age列的重复值返回唯一的年龄列表。如果DISTINCT后面跟多列它会组合去重比如DISTINCT name, age表示名字和年龄都相同的才去除。注意DISTINCT不能对一部分字段生效、对另一部分不生效它是整体修饰的写法上很容易忽视。分页查询则用LIMIT offset, countSELECT id, name FROM students ORDER BY id LIMIT 0, 10;表示跳过0条、取10条。第二页就是LIMIT 10, 10。数据量小的时候没感觉数据量上了百万级深分页比如LIMIT 100000, 10会越来越慢因为MySQL要扫描前100010行才能丢掉前100000行。关于深分页优化我会留到后面慢SQL优化专题再展开。4.3 UPDATE和DELETE必须带上条件没有WHERE条件的UPDATE是全表更新。我在学习时故意跑了一次UPDATE students SET age age 1;结果所有学生的年龄都加了一这就是“忘了写WHERE”的经典事故。实际业务中这条命令跑在用户表上后果就是全部用户数据被改写想恢复只能靠备份。所以我给自己立了规矩UPDATE和DELETE在写WHERE之前先看一眼条件条件为空直接不执行。DELETE的写法类似DELETE FROM students WHERE id 5;它删除的是满足条件的行表结构和索引都还在。而TRUNCATE TABLE students会清空整张表速度比DELETE快得多但没法按条件删也不能在事务里回滚到删除前的状态。这一块想深入理解需要配合事务和undo日志一起学。4.4 事务让操作要么全成、要么全败第34节后半段我重点练了事务因为在真实业务里一条SQL常常不够多步操作必须绑定在一起全部成功才算成功。START TRANSACTION; UPDATE accounts SET balance balance - 100 WHERE user_id 1; UPDATE accounts SET balance balance 100 WHERE user_id 2; COMMIT;这就是一个转账事务第一步扣钱第二步加钱最后COMMIT提交。如果中间任何一步出错可以执行ROLLBACK回滚两边金额都不会变。我特意模拟了一次“第二步报错后回滚”的场景确认了user_id为1的账户金额没有被扣除这种一致性正是事务的价值。这里补充一个实操细节默认情况下MySQL的每条DML语句都是自动提交的也就是自动开启事务、自动提交事务。一旦你在一个事务里手动执行了多条语句就必须自己写COMMIT或ROLLBACK决定结束方式。如果忘了COMMIT事务会一直挂着连接断开时回滚可能会把数据改没。生产环境里出现过这种事故改了一半数据没提交连接断开数据全部回滚业务方却以为改完了最后排查半天。5. 查询进阶JOIN、聚合与子查询建好students、courses、course_selections三张表之后我开始练习真正的关联查询。这部分直接关系到一个开发者在数据库方面的实战能力是面试必问、开发必用的高频内容。5.1 INNER JOIN连接多张表我需要查出每个学生选了哪些课。单靠查询一张表完不成因为选课关系存放在course_selections表里而课程名称在courses表里。INNER JOIN可以把多张表按关联字段拼起来SELECT s.name, c.course_name FROM students s INNER JOIN course_selections cs ON s.id cs.student_id INNER JOIN courses c ON cs.course_id c.id;这里用了别名s、cs、c分别是三张表的简称写起来简洁。INNER JOIN只返回两边都能匹配上的行如果一个学生没选课他就不会出现在结果里。如果想把没选课的学生也列出来就要用LEFT JOIN。SELECT s.name, c.course_name FROM students s LEFT JOIN course_selections cs ON s.id cs.student_id LEFT JOIN courses c ON cs.course_id c.id;LEFT JOIN返回左表的全部行右表没有匹配时填空值。业务上非常常用比如统计学生选课情况没有选课的学生也要展示出来。5.2 聚合函数与GROUP BY分组统计各班人数、平均分这类需求要动用聚合函数。常见的有COUNT、SUM、AVG、MAX、MIN。我当天用一个练习验证分组SELECT student_id, COUNT(*) AS course_count, AVG(score) AS avg_score FROM course_selections GROUP BY student_id;这条SQL会按学生分组统计每个学生选了几门课、平均分是多少。GROUP BY后面没有出现在聚合函数里的字段在SELECT里也尽量不要单独出现否则在ONLY_FULL_GROUP_BY模式下直接报错。MySQL 8.0默认开ONLY_FULL_GROUP_BY我记得很清楚当时试着SELECT student_id, course_id GROUP BY student_id直接被报错顶回来还说“which isnt in GROUP BY”那一刻才明白文档里那句话到底是什么意思。聚合之后还能用HAVING过滤它和WHERE的差别在于WHERE是在分组前过滤原始行HAVING是在分组后过滤聚合结果。比如想只查平均分大于80的学生SELECT student_id, AVG(score) AS avg_score FROM course_selections GROUP BY student_id HAVING avg_score 80;5.3 子查询的简单用法子查询就是嵌套在另一条SQL里的查询可以出现在SELECT、FROM、WHERE等位置。最典型的场景是查“分数高于平均分的学生”。SELECT name, score FROM course_selections cs JOIN students s ON cs.student_id s.id WHERE score (SELECT AVG(score) FROM course_selections);先执行括号里的子查询算出平均分再拿每个学生的分数去比较。这个逻辑用语言描述很顺畅写成SQL也就这么几行。子查询的效率有时候不如JOIN因为MySQL优化器不一定能把所有子查询都重写成JOIN所以在数据量大的表上我会先看执行计划再定方案。6. 当天踩过的坑与排查实录既然是学习笔记光写顺利的部分没意思我把当天遇到的实际报错也整理成了速查表这些报错在社区热搜里反复出现很值得一次说清。6.1 常见报错速查ERROR 1045 (28000): Access denied for user。登录时用户名或密码不对或者用户没有远程访问权限。我当时遇到过这个问题排查思路是先确认本机用root能不能登再确认是不是密码错误。远程连接报这个错大概率是MySQL用户表里host配置太严只允许localhost。ERROR 2003 (HY000): Cant connect to MySQL server。服务没启动或者端口不是3306。用systemctl status mysql或者Windows服务管理器先确认进程状态。ERROR 1366 (HY000): Incorrect string value。字符集问题存中文或emoji失败。解决方向是统一数据库、表、字段三级字符集为utf8mb4。ERROR 1064 (42000): You have an error in your SQL syntax。语法错误MySQL会把问题点定位在接近引号的那一块。这时候仔细检查关键词拼写和引号是否配对大多数情况下就是逗号、括号少了或多了。ERROR 1062 (23000): Duplicate entry。唯一键重复插入数据违反唯一约束。ERROR 1146 (42S02): Table doesnt exist。表名拼错或者当前选中的库不对。用SHOW TABLES;确认一下表是否存在。SSL连接错误也是热搜里的高频词MySQL 8.0默认开启SSL连接使用客户端连接时如果证书配置不对会报“SSL connection error”。学习环境下可以在命令行加--skip-ssl或者设置useSSLfalse绕过生产环境则要把证书问题解决清楚别轻易关掉SSL。6.2 实操避坑技巧先说一条我看过很多人犯的错用root账户天天搞业务操作。自己学习无所谓但在公司里必须按权限最小化原则创建专用账号只给业务库的增删改查权限不能一把梭给所有权限。学习阶段也要养成这个习惯。CREATE USER study_userlocalhost IDENTIFIED BY your_password; GRANT SELECT, INSERT, UPDATE, DELETE ON school.* TO study_userlocalhost; FLUSH PRIVILEGES;再说一条与连接相关的连接超时或断连。MySQL默认有个wait_timeout超过时间空闲连接会被服务端切断客户端再执行SQL就报“Lost connection”。这往往不是SQL本身的问题是连接状态失效了。程序里要使用连接池自动重连命令行下重连一下就好。还有一条是EXPLAIN也就是执行计划。我在学习JOIN和子查询时遇到的问题实际上都可以用EXPLAIN快速判断是否有全表扫描、是否走索引。这是我检测SQL性能的起步工具遇到变量“慢SQL”线索时第一件事永远是打开EXPLAIN。7. 几个值得坚持的小习惯一节笔记写完我想把当天在实操中验证过的小习惯单独拿出来说两句。它们不复杂但对后续学习很有帮助。第一把常用的SQL草稿收集起来分门别类整理。我电脑里有个纯文本文件专门记录各种场景的SQL模板包括建库、建用户、授权、备份、常见报错处理。遇到重复需求时直接复制模板再改参数比自己重新敲一遍快得多也减少了输入错误。第二每次学习都打开general_log或performance_schema感受一下MySQL内部是怎么执行的。第34节只是入门但如果你早有意识地接触执行计划、慢查询日志、事务隔离级别这些概念后面深入学起来会有一种“原来如此”的感觉。第三别忽视帮助命令。命令行里随时敲HELP CREATE DATABASE; 或者问号\G能快速查看官方语法说明。遇到不确定的语法问一下本地文档比网上搜来的答案更可靠。我记得当天下午验证了一个特别不起眼的点SHOW CREATE TABLE students; 它会回显该表的完整建表语句包括存储引擎、字符集、各种约束。这条命令我之后几乎每天都在用它让我瞬间知道一张表的真实定义和当初建表时的实际语句。如果你们现在只会用SHOW TABLES建议立刻试一下SHOW CREATE TABLE收获会很直接。最后说一句关于学习节奏的体会。MySQL的知识体系很大从建库建表到索引优化、事务隔离、主从复制、高可用架构每一块都能单独开一门课。但不管哪个分支底层都离不开对SQL本身的熟练掌握。先花几天时间把创建数据库和运行各类SQL这些最基础的操作敲熟后面再学别的你会发现所有复杂方案都是建立在简单语句之上的。 MySQL建库建表与SQL实操：从命令行到字符集踩坑指南
上午9点整我按计划翻到MySQL学习笔记第34节主题是创建数据库与运行各类SQL。原以为这一节很简单无非就是CREATE DATABASE、CREATE TABLE再加上增删改查结果真正动手敲命令的时候发现光是一个字符集设置就能牵扯出一堆历史问题。这一节我全程用命令行操作对比了图形化工具的差异把建库、建表、DML、事务、JOIN查询全部过了一遍也顺手记录了好几个报错现场。这篇笔记适合两种人看一种是刚入门、准备动手写第一条SQL的新手另一种是用惯了Navicat、突然被迫回到命令行但总被报错卡住的老手。我尽量把当天的操作顺序和踩坑细节都写出来你们可以直接照着敲。1. 上午的学习目标为什么要把建库放在第一节先交代一下背景。我在2026年3月4日之前已经把MySQL的安装配置、基本架构、常用客户端工具这些前置内容过了一遍装的是MySQL 8.0系列。当时对SQL的认知停留在“看得懂、写不出”的阶段SELECT * FROM xxx能看明白但真让我建一个库、设计一张表手是僵的。所以第34节的学习目标很明确把数据库生命周期里最常用的一套操作串起来从创建数据库开始到建表、插入数据、查询、修改、删除、提交事务为止。1.1 为什么建议第一条命令在命令行敲我知道很多人习惯打开Navicat鼠标点几下就把库建了、表也建了可视化确实舒服。但我个人强烈建议初学阶段至少在命令行里完整走一遍流程。原因很直接图形化工具把你的操作包装成了“点击”它替你执行了SQL但你完全看不到SQL本身更看不懂报错。一旦生产环境没有图形化客户端或者在服务器上排查问题你连mysql -u root -p都不知道怎么敲那就尴尬了。命令行还有一个好处是“所见即所得”。你输入CREATE DATABASE school;系统回那句Query OK, 1 row affected你会实实在在感觉到“哦命令生效了”。这种正反馈很重要它把抽象操作变得具体。图形化工具当然可以留着用但先会命令行再回去用工具你才知道工具背后到底做了什么。我那天上午至少有四十分钟是在命令行里度过的全程只打开一个终端窗口非常专注。1.2 先确认环境没问题动手之前先确认MySQL服务正常。Windows环境下我习惯用服务管理器看MySQL服务状态Linux下会用systemctl status mysql。如果服务没起来后面所有命令都会报错“Cant connect to MySQL server”那会儿千万别慌不是你SQL写错了是服务端压根没上线。mysql -u root -p输入密码后出现mysql提示符就说明连接成功了。我习惯先用SELECT VERSION();确认版本信息用SELECT NOW();确认当前时间这两个命令不起眼却能在学习过程中反复帮你确认连接没有被断开。版本差异对语法影响真的很大比如MySQL 5.7和8.0在字符集默认值、认证插件上都不一样同样是建库语句在不同版本下默认行为会差一截。2. 创建数据库语法简单细节不少这一节的名字就叫“创建数据库”所以第一节实操内容自然从CREATE DATABASE开始。语法很短就那么一句话但牵扯出来的字符集、排序规则、安全删除等问题全是可以展开讲的素材。2.1 最基本的CREATE DATABASE写法先看一个最干净的命令CREATE DATABASE school;执行完以后用SHOW DATABASES;查看当前实例下有哪些数据库你会发现多了一个school。这里的逻辑和文件系统非常像你可以把MySQL实例理解成一个文件夹下面挂着多个数据库每个数据库里又有若干张表。创建数据库不需要任何权限之外的东西但有个细节很多人会忽略如果这个库已经存在直接执行会报错“ERROR 1007 (HY000): Cant create database school; database exists”。解决办法有两个要么先DROP再CREATE但危险要么用IF NOT EXISTS修饰CREATE DATABASE IF NOT EXISTS school;这样系统会先检查存在就跳过不存在就新建不会报错。我个人的习惯是所有破坏性命令尽量加上类似的安全判断防止误操作。DROP DATABASE同理可以写成DROP DATABASE IF EXISTS school;虽然这个命令我一天也就用那么几次。2.2 字符集和排序规则为什么重要这是整节笔记里最值得展开的部分。在MySQL 8.0之前数据库默认字符集通常是latin1只能存英文和西欧字符存中文会变成问号或乱码。MySQL 8.0开始默认字符集变成了utf8mb4但这只是默认值不代表你可以完全不管。字符集决定了数据库能存哪些字符。utf8mb4支持完整的Unicode包括中文、日文、韩文还包括emoji。要注意的是MySQL以前的utf8实际是utf8mb3它最多支持3字节像emoji这种4字节字符存不进去所以从兼容性角度出发建库时我会明确指定CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;这里有个非常眼熟的参数utf8mb4_unicode_ci。它属于排序规则影响的是比较和排序结果。_ci结尾表示大小写不敏感case insensitive所以SELECT * FROM user WHERE name Alice能匹配到alice。如果你希望区分大小写可以选utf8mb4_bin。排序规则的选择建议新项目直接用utf8mb4_unicode_ci它对多语言的支持更全面如果团队规定用utf8mb4_general_ci也行它在部分老版本里性能稍好但差异已经微乎其微。按照MySQL官方文档的发展趋势老旧的排序规则以后会被逐渐淘汰选新一点的方案更稳妥。2.3 修改与删除数据库时容易忽略的点数据库创建之后可能因为规划失误需要改字符集。千万别用ALTER DATABASE乱改先想清楚影响范围因为改了字符集之后已存在表的字符集不会跟着变它们仍然用建表时指定的字符集很容易出现一张库里的表各用各的字符集查出来全是乱码。ALTER DATABASE school CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;这个命令只影响ALTER之后新建的表对老表不管用。要彻底统一需要逐表执行ALTER TABLE ... CONVERT TO CHARACTER SET这个操作耗时又锁表生产环境务必避开业务高峰期。上午学习时我在一个测试环境里试了一遍深刻体会到“建库时选对字符集”比“建库后改字符集”省事一百倍。删除数据库也有讲究。DROP DATABASE是彻底删除连库带表全没一旦执行错后果直接。所以我反复强调先SHOW DATABASES;看一眼再用USExxx;切进去确认最后才执行DROP。学习阶段没关系生产环境里DROP一条命令可能直接把一个月的业务数据清零没有备份就别碰。3. 建表才是DDL的真正重头戏创建数据库只是开了个头接下来建表才是DDLData Definition Language的重点。表结构设计直接决定后面所有SQL好不好写、查询快不快以及数据会不会出现冗余和异常。3.1 字段类型先选对再谈优化我当天建了一张学生表用来练习。建表之前我把常用字段类型列了一遍对照选型前面几列是设计阶段必须敲定的比如age字段我用了TINYINT UNSIGNED年龄不会超过255用INT纯属浪费空间。price这类字段如果涉及金额很多资料推荐DECIMAL而不是FLOAT因为浮点数有精度误差涉及钱的时候一分都不能差DECIMAL是定点数不会有这种问题。id字段习惯用INT UNSIGNED AUTO_INCREMENT让它自增不用手工维护。CREATE TABLE students ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, gender ENUM(男, 女) DEFAULT 男, age TINYINT UNSIGNED DEFAULT 20, email VARCHAR(100), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里面有四个点值得单独说。第一VARCHAR要设定一个合理的最大长度别一上来就VARCHAR(255)存储引擎实际占用的空间和长度定义有关。第二ENUM类型看起来方便但后期如果要加一个“保密”选项就得改表结构业务紧的时候容易误事所以我个人更建议用TINYINT映射状态灵活性更高。第三DEFAULT CURRENT_TIMESTAMP能省掉很多手填时间的麻烦这个默认值在MySQL 5.6之后就用得很普遍。第四ENGINEInnoDB必须显式指定虽然MySQL 8.0默认就是InnoDB但写得清楚以后看建表语句的人不需要猜。3.2 约束主键、唯一键、非空表里的约束决定了数据的合法性。我最先关注的是主键约束PRIMARY KEY (id)保证每行数据的唯一标识同时自带索引查询走主键速度最快。接着是唯一键约束。如果邮箱不允许重复可以加一个UNIQUE KEY这样插入重复邮箱时直接报错。我在学习时故意插入两条相同email看着系统报出“Duplicate entry”才真正理解了唯一键的作用。非空约束NOT NULL也很常用。业务上要求name必须有值所以建表时直接禁用NULL。但要注意空字符串和NULL是两种概念NULL表示值不存在表示值存在、只是内容为空。很多新手在这里踩坑明明表结构里允许NULL查询结果却总出现意外过滤。我的习惯是能用NOT NULL的字段尽量用NOT NULL配合DEFAULT值能省掉一堆判空逻辑。CREATE TABLE course_selections ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, student_id INT UNSIGNED NOT NULL, course_id INT UNSIGNED NOT NULL, score DECIMAL(5,2), PRIMARY KEY (id), UNIQUE KEY uk_student_course (student_id, course_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这张选课表里UNIQUE KEY (student_id, course_id)很有意思它组成了一个联合唯一约束意思是同一名学生选同一门课只能出现一次。这在业务上非常合理也顺便为下一步练习JOIN查询建好了关系表。3.3 ALTER TABLE修改表结构的几种常用场景建表几乎不可能一次到位后面经常要加字段、改字段。ALTER TABLE是我第34节里练习得最多的命令之一。ALTER TABLE students ADD COLUMN phone VARCHAR(20) AFTER email;ADD COLUMN是加字段AFTER email表示把新字段插到email后面。如果不写AFTER新字段默认加在表末尾。这里有个不值得提倡但很多人用的操作为了调整字段位置而反复ADD和DROP在表数据量大的时候会花很长时间还会产生大量undo日志。ALTER TABLE students MODIFY COLUMN phone VARCHAR(30);MODIFY COLUMN用来改变字段类型或约束。它会把整列重写一遍所以大表上执行时要慎重。如果师只想改字段名用CHANGE COLUMN old_name new_name 类型注意CHANGE必须重新声明类型不能只改名字。ALTER TABLE students DROP COLUMN phone;DROP COLUMN删字段。数据量大的表删除字段时InnoDB会标记该列为“不可用”实际释放空间还要等一段时间中间那段时间如果你执行了OPTIMIZE TABLE会短暂占用额外的磁盘空间。这些细节日常开发时你可能感受不到但在生产环境维护的时候少一个坑就能少一次凌晨三点被叫起来处理问题的经历。4. DML四件套INSERT、SELECT、UPDATE、DELETE数据库建好、表建好接下来的练习重点全部移到DMLData Manipulation Language上。增删改查四个字看起来简单真正写的时候才体会到细节决定成败。4.1 INSERT的几种写法插入数据最基本的写法是INSERT INTO students (name, gender, age, email) VALUES (张伟, 男, 20, zhangweiexample.com);字段列表和VALUES值列表必须一一对应数量、顺序都不能错。我一开始手抖写少了一个值直接报错“Column count doesnt match value count at row 1”这个报错信息其实已经说得很明白了列数对不上。除了单行插入还可以一次插多行INSERT INTO students (name, gender, age, email) VALUES (李娜, 女, 21, linaexample.com), (王强, 男, 19, wangqiangexample.com), (赵敏, 女, 22, zhaominexample.com);一次插多行的效率比循环执行多次高得多因为减少了客户端和服务器之间的网络交互。学习阶段可能感受不到差别但在批量导入数据、比如从Excel清洗完数据再灌入数据库时这个技巧非常实用。另外INSERT语句还支持INSERT INTO ... SELECT ...这种写法把一张表的查询结果直接插入另一张表做数据迁移、备份表时很常用。4.2 SELECT排序、去重与条件过滤查询是SQL里出场率最高的部分。我当天的练习顺序是全表查询→条件查询→排序→去重→分页。SELECT * FROM students;SELECT *不建议在生产环境随便用尤其是表很大、网络带宽有限的时候它会把所有列的数据全捞回来白白消耗资源。我更习惯显式列出需要的字段SELECT id, name, age FROM students WHERE age 20 ORDER BY age DESC LIMIT 10;这条语句包含了三个核心子句WHERE做条件过滤ORDER BY age DESC做倒序排序LIMIT 10限定只返回前10行。执行顺序上MySQL先找到满足WHERE条件的行再排序最后取前10条理解这个顺序对后续优化大有用处。去重用DISTINCT这也是标题里提到的热搜词“sql语句去重”。我特意试了一遍SELECT DISTINCT age FROM students;它能去掉age列的重复值返回唯一的年龄列表。如果DISTINCT后面跟多列它会组合去重比如DISTINCT name, age表示名字和年龄都相同的才去除。注意DISTINCT不能对一部分字段生效、对另一部分不生效它是整体修饰的写法上很容易忽视。分页查询则用LIMIT offset, countSELECT id, name FROM students ORDER BY id LIMIT 0, 10;表示跳过0条、取10条。第二页就是LIMIT 10, 10。数据量小的时候没感觉数据量上了百万级深分页比如LIMIT 100000, 10会越来越慢因为MySQL要扫描前100010行才能丢掉前100000行。关于深分页优化我会留到后面慢SQL优化专题再展开。4.3 UPDATE和DELETE必须带上条件没有WHERE条件的UPDATE是全表更新。我在学习时故意跑了一次UPDATE students SET age age 1;结果所有学生的年龄都加了一这就是“忘了写WHERE”的经典事故。实际业务中这条命令跑在用户表上后果就是全部用户数据被改写想恢复只能靠备份。所以我给自己立了规矩UPDATE和DELETE在写WHERE之前先看一眼条件条件为空直接不执行。DELETE的写法类似DELETE FROM students WHERE id 5;它删除的是满足条件的行表结构和索引都还在。而TRUNCATE TABLE students会清空整张表速度比DELETE快得多但没法按条件删也不能在事务里回滚到删除前的状态。这一块想深入理解需要配合事务和undo日志一起学。4.4 事务让操作要么全成、要么全败第34节后半段我重点练了事务因为在真实业务里一条SQL常常不够多步操作必须绑定在一起全部成功才算成功。START TRANSACTION; UPDATE accounts SET balance balance - 100 WHERE user_id 1; UPDATE accounts SET balance balance 100 WHERE user_id 2; COMMIT;这就是一个转账事务第一步扣钱第二步加钱最后COMMIT提交。如果中间任何一步出错可以执行ROLLBACK回滚两边金额都不会变。我特意模拟了一次“第二步报错后回滚”的场景确认了user_id为1的账户金额没有被扣除这种一致性正是事务的价值。这里补充一个实操细节默认情况下MySQL的每条DML语句都是自动提交的也就是自动开启事务、自动提交事务。一旦你在一个事务里手动执行了多条语句就必须自己写COMMIT或ROLLBACK决定结束方式。如果忘了COMMIT事务会一直挂着连接断开时回滚可能会把数据改没。生产环境里出现过这种事故改了一半数据没提交连接断开数据全部回滚业务方却以为改完了最后排查半天。5. 查询进阶JOIN、聚合与子查询建好students、courses、course_selections三张表之后我开始练习真正的关联查询。这部分直接关系到一个开发者在数据库方面的实战能力是面试必问、开发必用的高频内容。5.1 INNER JOIN连接多张表我需要查出每个学生选了哪些课。单靠查询一张表完不成因为选课关系存放在course_selections表里而课程名称在courses表里。INNER JOIN可以把多张表按关联字段拼起来SELECT s.name, c.course_name FROM students s INNER JOIN course_selections cs ON s.id cs.student_id INNER JOIN courses c ON cs.course_id c.id;这里用了别名s、cs、c分别是三张表的简称写起来简洁。INNER JOIN只返回两边都能匹配上的行如果一个学生没选课他就不会出现在结果里。如果想把没选课的学生也列出来就要用LEFT JOIN。SELECT s.name, c.course_name FROM students s LEFT JOIN course_selections cs ON s.id cs.student_id LEFT JOIN courses c ON cs.course_id c.id;LEFT JOIN返回左表的全部行右表没有匹配时填空值。业务上非常常用比如统计学生选课情况没有选课的学生也要展示出来。5.2 聚合函数与GROUP BY分组统计各班人数、平均分这类需求要动用聚合函数。常见的有COUNT、SUM、AVG、MAX、MIN。我当天用一个练习验证分组SELECT student_id, COUNT(*) AS course_count, AVG(score) AS avg_score FROM course_selections GROUP BY student_id;这条SQL会按学生分组统计每个学生选了几门课、平均分是多少。GROUP BY后面没有出现在聚合函数里的字段在SELECT里也尽量不要单独出现否则在ONLY_FULL_GROUP_BY模式下直接报错。MySQL 8.0默认开ONLY_FULL_GROUP_BY我记得很清楚当时试着SELECT student_id, course_id GROUP BY student_id直接被报错顶回来还说“which isnt in GROUP BY”那一刻才明白文档里那句话到底是什么意思。聚合之后还能用HAVING过滤它和WHERE的差别在于WHERE是在分组前过滤原始行HAVING是在分组后过滤聚合结果。比如想只查平均分大于80的学生SELECT student_id, AVG(score) AS avg_score FROM course_selections GROUP BY student_id HAVING avg_score 80;5.3 子查询的简单用法子查询就是嵌套在另一条SQL里的查询可以出现在SELECT、FROM、WHERE等位置。最典型的场景是查“分数高于平均分的学生”。SELECT name, score FROM course_selections cs JOIN students s ON cs.student_id s.id WHERE score (SELECT AVG(score) FROM course_selections);先执行括号里的子查询算出平均分再拿每个学生的分数去比较。这个逻辑用语言描述很顺畅写成SQL也就这么几行。子查询的效率有时候不如JOIN因为MySQL优化器不一定能把所有子查询都重写成JOIN所以在数据量大的表上我会先看执行计划再定方案。6. 当天踩过的坑与排查实录既然是学习笔记光写顺利的部分没意思我把当天遇到的实际报错也整理成了速查表这些报错在社区热搜里反复出现很值得一次说清。6.1 常见报错速查ERROR 1045 (28000): Access denied for user。登录时用户名或密码不对或者用户没有远程访问权限。我当时遇到过这个问题排查思路是先确认本机用root能不能登再确认是不是密码错误。远程连接报这个错大概率是MySQL用户表里host配置太严只允许localhost。ERROR 2003 (HY000): Cant connect to MySQL server。服务没启动或者端口不是3306。用systemctl status mysql或者Windows服务管理器先确认进程状态。ERROR 1366 (HY000): Incorrect string value。字符集问题存中文或emoji失败。解决方向是统一数据库、表、字段三级字符集为utf8mb4。ERROR 1064 (42000): You have an error in your SQL syntax。语法错误MySQL会把问题点定位在接近引号的那一块。这时候仔细检查关键词拼写和引号是否配对大多数情况下就是逗号、括号少了或多了。ERROR 1062 (23000): Duplicate entry。唯一键重复插入数据违反唯一约束。ERROR 1146 (42S02): Table doesnt exist。表名拼错或者当前选中的库不对。用SHOW TABLES;确认一下表是否存在。SSL连接错误也是热搜里的高频词MySQL 8.0默认开启SSL连接使用客户端连接时如果证书配置不对会报“SSL connection error”。学习环境下可以在命令行加--skip-ssl或者设置useSSLfalse绕过生产环境则要把证书问题解决清楚别轻易关掉SSL。6.2 实操避坑技巧先说一条我看过很多人犯的错用root账户天天搞业务操作。自己学习无所谓但在公司里必须按权限最小化原则创建专用账号只给业务库的增删改查权限不能一把梭给所有权限。学习阶段也要养成这个习惯。CREATE USER study_userlocalhost IDENTIFIED BY your_password; GRANT SELECT, INSERT, UPDATE, DELETE ON school.* TO study_userlocalhost; FLUSH PRIVILEGES;再说一条与连接相关的连接超时或断连。MySQL默认有个wait_timeout超过时间空闲连接会被服务端切断客户端再执行SQL就报“Lost connection”。这往往不是SQL本身的问题是连接状态失效了。程序里要使用连接池自动重连命令行下重连一下就好。还有一条是EXPLAIN也就是执行计划。我在学习JOIN和子查询时遇到的问题实际上都可以用EXPLAIN快速判断是否有全表扫描、是否走索引。这是我检测SQL性能的起步工具遇到变量“慢SQL”线索时第一件事永远是打开EXPLAIN。7. 几个值得坚持的小习惯一节笔记写完我想把当天在实操中验证过的小习惯单独拿出来说两句。它们不复杂但对后续学习很有帮助。第一把常用的SQL草稿收集起来分门别类整理。我电脑里有个纯文本文件专门记录各种场景的SQL模板包括建库、建用户、授权、备份、常见报错处理。遇到重复需求时直接复制模板再改参数比自己重新敲一遍快得多也减少了输入错误。第二每次学习都打开general_log或performance_schema感受一下MySQL内部是怎么执行的。第34节只是入门但如果你早有意识地接触执行计划、慢查询日志、事务隔离级别这些概念后面深入学起来会有一种“原来如此”的感觉。第三别忽视帮助命令。命令行里随时敲HELP CREATE DATABASE; 或者问号\G能快速查看官方语法说明。遇到不确定的语法问一下本地文档比网上搜来的答案更可靠。我记得当天下午验证了一个特别不起眼的点SHOW CREATE TABLE students; 它会回显该表的完整建表语句包括存储引擎、字符集、各种约束。这条命令我之后几乎每天都在用它让我瞬间知道一张表的真实定义和当初建表时的实际语句。如果你们现在只会用SHOW TABLES建议立刻试一下SHOW CREATE TABLE收获会很直接。最后说一句关于学习节奏的体会。MySQL的知识体系很大从建库建表到索引优化、事务隔离、主从复制、高可用架构每一块都能单独开一门课。但不管哪个分支底层都离不开对SQL本身的熟练掌握。先花几天时间把创建数据库和运行各类SQL这些最基础的操作敲熟后面再学别的你会发现所有复杂方案都是建立在简单语句之上的。