实验室刚把MySQL 8.0装好下午就要用我趁着上午第34节把“创建数据库和运行各类SQL”这一整块过了一遍。从最基础的CREATE DATABASE到平时写报表最常用的分组聚合再到面试基本必问的事务和存储过程一条线走下来人一下子就通透了不少。这篇笔记不是简单的把语法罗列一遍更多是我实操时的真实手感、踩坑记录和反复对比后的理解适合刚入门还没把SQL串起来的同学也适合那种“会写增删改查但总觉得少点体系”的人看完可以直接对着抄。1. 环境准备先把“菜地”整明白写SQL之前先得有MySQL环境。很多教程上来就甩给你一条命令但装完连不上、字符集不对、密码策略卡壳这些破事能白白耗掉你半小时。我这次用的是MySQL 8.0.44的社区版Linux环境用rpm批量安装Windows环境直接用安装引导两者套路差别不大核心关注点是一致的。1.1 版本选择与安装要点实测选版本这事别追新也别抱老。目前生产环境里5.7和8.0是绝对主力但8.0在功能上确实领先不少默认字符集是utf8mb4、支持窗口函数、CTE公用表表达式、原子DDL、还有更好的性能表现。尤其是窗口函数写排名、占比、累计值的时候比自连接和子查询优雅太多我在后面进阶SQL的章节会专门举例。如果现阶段刚入门建议直接用8.0别在5.7上花费太多精力省得过两年还得再迁移一次。安装时有两个坑是高频出现。第一个是8.0初始化时会对root密码有强度要求你设个“123456”它直接拒绝至少得大小写字母加数字混着来。第二个是root默认允许本地登录远程登录被禁用是正常的想远程连的话需要创建一个专属账号并授权而不是去改root的host那样既不安全也容易出问题。我实际测试下来Linux下rpm安装mysql-community-server后用bash systemctl start mysqld启动再通过bash grep temporary password /var/log/mysqld.logWindows端相对省心安装引导里选Server Only一路Next就行。但注意Configure Type选好端口默认3306一般不用动如果机器上装了其他数据库占用了端口改成3307、3308都行改完记得防火墙放行。 #### 1.2 客户端连接方式选择 SQL写得好不好跟工具有关系但也没那么大关系。命令行mysql客户端最干净能逼你把语法记得滚瓜烂熟但查个带中文的数据还得调编码体验确实糙。Navicat这类GUI工具胜在直观表结构、查询结果、ER图一眼能看明白适合日常开发维护。 我个人的建议是两条腿走路学习阶段多敲命令行真正干活时用Navicat提高效率。不过有一点要提醒网上关于Navicat破解版、激活码的内容满天飞我的建议是走官方试用或者选择开源免费的DBeaver。把时间省下来多练几条SQL比折腾那点激活步骤划算得多。用破解工具连接数据库这件事风险不小犯不上为了省那点钱把自己放在一个不确定的位置。 连接时最容易报的错是“SSL connection error”。有些老版本客户端或者特定网络环境下默认开启TLS反而连不上。解决办法很简单连接时把SSL选项关掉或者用参数ssl-modeDISABLEDNavicat里在连接的SSL页签取消勾选即可。这个问题在5.7升8.0之后特别常见因为8.0默认要求caching_sha2_password插件老客户端不认识这个认证方式。遇到连接报错Authentication plugin caching_sha2_password cannot be loaded要么在MySQL里把用户改成mysql_native_password插件要么升级客户端驱动我更推荐后者一劳永逸。 ### 2. 创建数据库语法拆解与字符集选择 数据库是SQL操作的地基说白了就是一块划分好的存储区域。CREATE DATABASE这条语句本身不难难点在于参数选择尤其是字符集和排序规则很多初学者在这上面吃过大亏。 #### 2.1 CREATE DATABASE完整语法说明 标准语法长这样 sql CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET utf8mb4] [COLLATE utf8mb4_0900_ai_ci];不用IF NOT EXISTS的话库已经存在时会直接报错加上之后MySQL会抛一个Warning而不是Error脚本批量执行的时候就友好得多。我习惯在自动化部署脚本里统一加上这个保护避免重复执行时中断后续流程。语法本身一句话但选对参数才是真正有讲究的。CHARACTER SET就是字符集COLLATE是排序规则前者决定你存什么字符后者决定你怎么排序和比较。常见组合我整理了一张表字符集排序规则示例适用场景踩坑点utf8mb4utf8mb4_0900_ai_ci绝大多数业务含中文、表情符号没选它emoji直接变成问号utf8utf8_general_ci老项目遗留库不够存生僻字和emojilatin1latin1_swedish_ci纯英文、历史遗留查中文时乱码重灾区gbkgbk_chinese_ci港澳台繁体、某些国内老系统跟utf8不一致跨库JOIN容易出问题我的建议是从一开始就直接上一套组合utf8mb4 utf8mb4_0900_ai_ci。理由很简单MySQL 8.0默认就是这套通用性好中文、日文、韩文、emoji全都能放排序时大小写不敏感符合大多数业务场景。有些老项目用了utf8后来发现存不了emoji又要改库又要改连接串麻烦得很不如一步到位。2.2 字符集与排序规则的实际影响理解了字符集还要理解它带来的连锁反应。数据的存储和读取、比较大小、分组排序、索引命中每一步都绕不开字符集。最经典的案例是你表里有一个字段叫name查询条件WHERE name abc如果排序规则是utf8mb4_bin那么abc和ABC是两个完全不同的值如果是_ai_ci结尾的规则那它俩就被认为是相等的。这个差异直接决定了查询结果是否符合预期。连接层也有一层字符集设置。登录后执行SET NAMES utf8mb4;等价于同时设置客户端、连接、返回结果的字符集。命令行里不执行这条插入中文后查出来是一堆乱码别怀疑SQL写错了十有八九是连接字符集没对齐。PHP的PDO连接串、Java的jdbc:mysql://...?useUnicodetruecharacterEncodingutf8、Navicat里的编码设置本质都是在干这件事。还有一个容易被忽略的点修改数据库字符集不会自动修改已有表的字符集。sql ALTER DATABASE db_name CHARACTER SET utf8mb4;只是改了默认值对已存在的表毫无影响。想把整库都转过来得对每张表单独执行ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4或者用ALTER TABLE t DEFAULT CHARACTER SET utf8mb4只改默认行为。两者区别在于CONVERT会重写现有数据更彻底但耗时更长表大了要挑业务低峰期操作。3. 数据表设计从建表到字段规范数据库建好了接下来就是设计表。表设计水平直接决定后续所有SQL好不好写字段类型选大了浪费存储、拖慢性能选小了数据存不下、业务跑不起来。这一节把建表语句和设计思路展开讲。3.1 常见数据类型选择与业务匹配先上一段建表样例以一张用户表为例CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(50) NOT NULL COMMENT 用户名, gender TINYINT NOT NULL DEFAULT 0 COMMENT 性别0未知1男2女, balance DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 账户余额, birthday DATE DEFAULT NULL COMMENT 生日, last_login DATETIME DEFAULT NULL COMMENT 最后登录时间, profile TEXT COMMENT 个人简介, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常0禁用, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_username (username), KEY idx_status (status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT用户表;这条语句把DDL里最关键的东西都带出来了。逐个字段看id用INT UNSIGNED AUTO_INCREMENT范围足够大配合主键索引查询效率最高username用VARCHAR(50)长度留有余地又不浪费gender这类有限枚举值的字段用TINYINT比CHAR(1)存男/女省空间扩展也方便balance必须用DECIMAL这是金钱字段的硬性要求用FLOAT或DOUBLE会出现精度丢失这是金融系统的大忌profile这种可变长文本用TEXT不要用VARCHAR撑到几千的长度存储引擎处理起来完全不是一个量级。类型选择的总原则就三条够用原则、贴合语义原则、考虑到未来扩展但不过度设计。INT够用就不用BIGINTVARCHAR能装下就用VARCHAR别老想着TEXT日期用DATE/DATETIME而不是用字符串硬存时间戳字段用TIMESTAMP还能省2个字节存储空间。3.2 约束与索引建表里最见功力的部分建表语句中的约束比字段本身更能看出一个人有没有经验。PRIMARY KEY保证每行唯一NOT NULL杜绝空值的业务歧义DEFAULT提供兜底行为UNIQUE KEY防止重复注册AUTO_INCREMENT让自增主键省心。索引这块我在idx_status上建了一个普通索引因为用户表最常见的筛选条件就是按状态过滤。但索引不是越多越好每个索引在写入时都要额外维护写多读少的表乱加索引就是在给自己制造麻烦。索引设计的核心原则是区分度高的字段建索引比如username区分度低的要谨慎比如status只有两三个值单独建索引效果有限组合索引才有价值。关于created_at和updated_at这两个字段设计上有个很妙的点TIMESTAMP类型配合DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP插入时自动写当前时间更新时自动刷新完全不用在业务代码里管时间字段。这算是我个人觉得最实用的设计之一省掉了很多手动维护的麻烦。4. 增删改查最常用DML的实战套路表建好之后进入核心环节——跑SQL。SELECT、INSERT、UPDATE、DELETE这四个操作占据了日常开发里90%以上的SQL编写量。这里我不打算抄官方文档而是把真正高频的组合用法串一遍。4.1 INSERT写入单条、批量与更新插入最简单的是单条插入INSERT INTO user (username, gender, balance, birthday, profile, status) VALUES (zs, 1, 100.00, 1995-05-20, 全栈工程师, 1);通常建议加上字段列表别用INSERT INTO user VALUES (...)这种省略字段的写法。原因很直白一旦表结构发生变化比如增加了一列省略字段的SQL立刻报错而加了字段列表的SQL可以完全不受影响。这个习惯在工作里能省好多排查时间。批量插入的语法更是日常利器INSERT INTO user (username, gender, balance, birthday, profile, status) VALUES (ls, 0, 200.00, 1993-01-12, 产品经理, 1), (ww, 1, 300.00, 1997-03-08, 设计师, 1), (zl, 0, 400.00, 1990-11-23, 运营专员, 1);一条语句插多行比一行一行INSERT快一个数量级减少网络往返和日志刷盘次数。我做数据初始化时经常一次插几百行效果立竿见影。还有一个高频场景数据存在就更新不存在就插入。MySQL提供了专有的写法INSERT INTO user (username, balance) VALUES (zs, 500.00) ON DUPLICATE KEY UPDATE balance VALUES(balance);这在幂等写入场景下特别实用。比如日志表或者统计表同一主键被重复上报时重复数据直接覆盖更新不会报错也不会产生脏数据。不过MySQL 8.0.20之后VALUES()函数被标记为废弃建议改用新语法AS new ON DUPLICATE KEY UPDATE balance new.balance更能表达意图。4.2 SELECT查询条件、排序、去重与分页SELECT是SQL里的重头戏。基本结构是SELECT字段 FROM表 WHERE条件 ORDER BY排序 LIMIT限制。逐个拆开讲。条件过滤里除了等值比较范围查询也很常用SELECT id, username, balance FROM user WHERE balance BETWEEN 100 AND 1000 AND status 1 AND birthday IS NOT NULL ORDER BY balance DESC LIMIT 10;注意NULL的判断一定是IS NULL或IS NOT NULL用 NULL是永远查不出数据的这算是新手最容易犯的错误之一因为NULL比较的结果是UNKNOWN而不是TRUE。排序主要有两个细节。第一个是多字段排序业务规则往往不是单维度的比如先按余额降序余额一样再按注册时间升序ORDER BY balance DESC, created_at ASC第二个是NULL值的排序位置。默认升序时NULL在最前降序时NULL在最后想控制NULL的位置要单独写ORDER BY balance IS NULL, balance DESC。比如做排行榜没登录过的用户last_login为NULL就应该排最后。去重用DISTINCT或者GROUP BY都行。单纯对一列去重SELECT DISTINCT status FROM user;多列去重时DISTINCT后面跟多个字段它是按照字段组合去重的不是只取第一列去重。这个细节经常有人搞混排重时写成SELECT DISTINCT username FROM user查出来没问题一旦加上别的字段行为就变了。个性签名凑数。分页用LIMIT语法是LIMIT offset, row_count。第一页写LIMIT 0, 20第二页LIMIT 20, 20这就是经典的offset分页。但数据量大到百万级别时offset越来越大查询越来越慢因为数据库要把之前翻过的行全扫一遍。深翻页场景我更推荐用子查询或JOIN限定id区间的方式效果完全不是一个量级。4.3 UPDATE与DELETE改数据和删数据的正确姿势更新操作核心就一个字准。绝不能在UPDATE或DELETE上省略WHERE条件。我曾经手滑写过一次不带条件的DELETE整张表瞬间清空那种酸爽不想再体验第二次。单表更新基本套路UPDATE user SET balance balance - 100, updated_at CURRENT_TIMESTAMP WHERE id 1001 AND balance 100;这里用了两个条件id精确锁定目标行balance 100防止扣出负数。这种带业务校验的写法比单独WHERE id 1001安全得多靠SQL本身就把边界条件守住。DELETE操作再补充一个考量点物理删除和逻辑删除的分歧。业务系统里用户数据、订单数据、财务数据通常不建议真删而是用status字段标记为禁用或删除状态这样数据可追溯、统计报表不会断层。真删一般只用于临时表、测试数据和一些明确的清理任务。如果确实要清空全表又想让自增ID归零可以用TRUNCATE TABLE user但TRUNCATE不能加WHERE且会重置AUTO_INCREMENT线上要谨慎使用。5. 进阶SQL聚合、连接与窗口函数基础DML熟练之后分析报表和复杂查询就轮到聚合和连接上场了。数据从几万条涨到几十万条时聚合函数和JOIN就是日常工具不会的话根本没法干活。5.1 聚合函数与GROUP BY实现分组统计聚合函数是SQL统计的基石。COUNT、SUM、AVG、MAX、MIN配合GROUP BY可以回答像“每个状态下有多少用户”“各时段订单金额合计”这类业务问题。一个典型例子SELECT status, COUNT(*) AS user_count, AVG(balance) AS avg_balance FROM user GROUP BY status HAVING user_count 0;需要注意HAVING和WHERE的区别WHERE在分组前过滤原始行HAVING在分组后过滤聚合结果。想查“余额大于100的用户里各个status的人数”先用WHERE把原始数据过滤掉再GROUP BY想查“分组之后人数超过10的状态”只能用HAVING。很多新手在这两个地方混用导致统计结果跟预期不一致。COUNT还有一个容易踩的类型问题。COUNT(*)统计所有行COUNT(字段)统计该字段非NULL的个数。比如统计有生日的用户数应该用COUNT(birthday)如果心里想的是“有多少人有生日信息”却写了COUNT(*)结果会偏大逻辑完全不对。5.2 JOIN连接查询内连接、左连接与业务实例多表查询是SQL进阶的必修课。业务系统几乎不会把所有数据塞在一张表里用户表、订单表、商品表各司其职查询时需要把它们关联起来。经典的场景查每个订单对应的用户名和订单金额。SELECT o.order_id, u.username, o.amount FROM orders o INNER JOIN user u ON o.user_id u.id WHERE o.status 1 ORDER BY o.create_time DESC;INNER JOIN只取两张表中都匹配得上的行。如果还想把没有下过单的用户也查出来就要用LEFT JOIN左表数据全保留右表没匹配到的字段显示为NULLSELECT u.id, u.username, o.order_id, o.amount FROM user u LEFT JOIN orders o ON o.user_id u.id;这条SQL执行后从没下过单的用户order_id和amount都是NULL这就是LEFT JOIN的核心语义。RIGHT JOIN用的场景少很多绝大多数时候改写成LEFT JOIN更易读比如把左右表互换就行。JOIN时还有一个常见坑ON的条件写错导致结果爆炸。比如一对多关联时如果左表某条记录在右表匹配到多条结果集会多行而不是一行COUNT会翻倍SUM也会重复累加。写JOIN前先想清楚这个关联是不是一对多心里有个预期不然查出来的数据对不上账排查会浪费时间。5.3 窗口函数排名与分组计算的效率解法MySQL 8.0引入窗口函数是SQL写法的分水岭。以前想算“各状态下余额排名第一的用户”要么写子查询做自连接要么用临时表拼接代码又臭又长。现在一行ROW_NUMBER就搞定SELECT username, status, balance, ROW_NUMBER() OVER (PARTITION BY status ORDER BY balance DESC) AS rn FROM user;PARTITION BY把数据按status分组ORDER BY决定组内排序ROW_NUMBER给组内每行一个从1开始的序号。想取每组第一名在外面套一层子查询过滤rn 1即可。除了ROW_NUMBER还有RANK、DENSE_RANK区别在于并发并列时的序号是否跳过面试时经常拿这个当考点。窗口函数本质是“不减少行数的聚合”和GROUP BY相对理解了这个概念写起来就顺手了。6. 事务与存储过程让SQL可靠又高效SQL能写、能查、能统计之后就该考虑数据操作的可靠性和复用性了。事务保证多步操作要么全部成功要么全部失败存储过程把一堆SQL封装成一个可复用的程序块这两个都是企业级开发免不了的东西。6.1 事务四大特性与实操演示事务的ACID特性——原子性、一致性、隔离性、持久性——背概念容易真正干活时才知道它的价值。比如转账操作从A账户扣1000往B账户加1000如果先执行扣款、后执行加款中间的意外宕机会导致钱凭空消失。用事务包起来就是START TRANSACTION; UPDATE account SET balance balance - 1000 WHERE user_id 1; UPDATE account SET balance balance 1000 WHERE user_id 2; COMMIT;两条UPDATE之间如果出现任何异常执行ROLLBACK就能把数据恢复到事务开始前的状态。这个能力在很多关键业务里是底线要求缺了它账根本没法算。事务还有个隔离级别的概念默认的REPEATABLE READ在MySQL里已经能解决大部分并发问题但处理高并发业务时要了解读已提交和可重复读在锁和性能上的差异。这块不用一次吃透先清楚COMMIT和ROLLBACK怎么用再去研究隔离性过期不迟。6.2 存储过程把重复SQL封装成可复用代码存储过程就是把一组SQL预编译保存在数据库里客户端调用时一句CALL就行。比如批量给指定状态的用户加积分写成存储过程DELIMITER $$ CREATE PROCEDURE add_points(IN p_status TINYINT, IN p_points INT) BEGIN UPDATE user SET balance balance p_points WHERE status p_status; END$$ DELIMITER ;调用就是CALL add_points(1, 100)。开发中我最喜欢用存储过程的场景是复杂的统计报表、定时任务、数据归档逻辑。它们往往包含很多临时表和中间计算把这些逻辑封装进去之后客户端代码只需要一行调用逻辑改动只动数据库不发版。但存储过程不能乱用。逻辑简单的事用普通SQL更直观过度封装会让业务规则散落在数据库层排查和测试都变麻烦。还有调试体验问题存储过程报错信息不如应用程序代码友好。我的原则是聚合查询能写SQL就写SQL只有“多条SQL、有循环或分支、有中间结果集”这类场景才用存储过程。7. 课堂实录那些年踩过的坑与排查技巧光看语法和案例还不够把实际操作中容易翻车的细节单拎出来讲一遍这些才是真正的经验值。7.1 字段与数据层面的经典报错字段类型不匹配是最常见的报错之一。比如WHERE username 12345MySQL会做隐式类型转换把字符串字段转成数字再比结果很可能匹配不上任何数据或者匹配到一堆意料之外的行。索引也会因此失效查询一下慢好几倍。我排查慢查询时第一个习惯就是检查条件字段有没有被隐式转换。“Data too long for column”这个错也高频出现。往VARCHAR(20)里塞超过20个字符的内容或往TINYINT里塞超过127的数字MySQL会直接报错。这个错在测试环境不容易遇到一旦线上数据超长就会冒出来所以设计字段时宁可留充裕也别卡得太死。去重相关的坑上次我们提了DISTINCT和GROUP BY的差异这次再补充一个去重之后还想排序DISTINCT和ORDER BY混用时排序字段必须出现在SELECT列表里否则会报错。比如SELECT DISTINCT balance FROM user ORDER BY username是不允许的因为username没有出现在SELECT列表数据库排不了序。7.2 连接、字符集与慢查询的坑远程连接报错这种事我在这节课结束后又遇到一次。现象是Navicat连MySQL 8.0提示认证插件不兼容。原因前面讲过8.0默认密码插件是caching_sha2_password老版本客户端不认识。解决方式是更新驱动或者临时把用户的认证插件改回mysql_native_password。但要注意改插件只是兼容老生态的权宜之计新环境直接用新驱动才是正路。字符集相关的问题几乎每次上课都有人踩。最大规模的一次是整库乱码排查发现库、表、连接串全都是utf8唯独字段是latin1。这种隐形的错位很恶心查错方向不对能浪费一晚上。排查乱码问题时我的固定动作是先查SHOW VARIABLES LIKE character_set%把客户端、连接、数据库、服务器四个层级全看一遍哪一级不一致就改哪一级。慢查询优化这块我是按这个顺序执行的先看有没有索引、再看有没有隐式转换、再看是不是深翻页了、最后看能不能改写SQL。比如SELECT *这种写法没有覆盖索引时会把整行数据捞出来白白消耗IO和内存改成只查需要的字段查询响应时间经常能快一倍。MySQL用EXPLAIN可以看执行计划type列从ALL变成ref或range就说明索引生效了这是判断慢SQL优化是否到位的标准。7.3 实操建议先跑通再优化最后说点学习方法上的建议。SQL这玩意看一百遍不如坐到电脑前敲一遍。我学这一节的时候先是把建库建表跑通然后造了五六十条测试数据按课程里的SELECT、UPDATE、DELETE逐条演练再把同样的逻辑自己换个业务场景写一遍。比如课程里用USER表练手我就用订单表自己写了一个“按月份统计销售额只保留销售额大于1万的月份”的查询。脑子里过一遍和亲手跑一遍记忆留存完全不一样。课程最后还讲了一个思想SQL能做的事尽量用SQL做少在程序代码里用循环凑。比如要统计每天的用户活跃数一条GROUP BY语句就搞定没必要查出明细后在代码里循环计数。数据库最擅长的就是集合运算让专业的人干专业的事系统的整体效率反而最高。这一节从基础的建库建表一直串到窗口函数和存储过程覆盖的面比较广。按我个人经验后面再学习SQL时不会再单独啃某个语法点了而是带着问题来想看排行就看窗口函数想统计就看GROUP BY想合并数据就看JOIN。语法不用记全遇到场景时知道有这个方案再回头查具体写法就行。