折腾了一上午终于把第34节课的内容吃透了创建数据库然后把各种SQL语句挨个跑了一遍。说实话这节课在教程里排得不算靠后但对于刚装好MySQL准备练基本功的人来说恰恰是最容易“卡壳”的地方——不是语法背不下来而是你不知道为什么这样写、换了环境怎么调、报错该从哪里排查。这篇笔记把我从“建库”到“各种SQL都能跑通”的完整过程记录下来适合刚装完MySQL、准备跟着教程敲命令的同学也适合想系统性复盘一遍建库、建表、增删改查、索引事务的老手。这篇笔记里没有花哨的东西全是命令行里实测过的语句以及我在第34节课上踩过的坑。你跟着敲一遍基本就能把MySQL的日常操作串起来了。1. 环境准备装好MySQL才能谈创建数据库1.1 安装版本选型与安装方式实测先说版本。现在新入门我建议直接装MySQL 8.0系列包括最新的8.0.44等小版本而不是还在网上大量流传的5.7.26。原因不复杂8.0自带utf8mb4默认字符集、窗口函数、公用表表达式CTE这些现代SQL能力而且默认认证插件是caching_sha2_password整体更安全。5.7不是不能用但官方已经逐步走向寿命末期新项目再踩那个坑没必要。安装方式上Windows环境直接下载MySQL Installer的exe你搜到的mysql 50616版本exe、windows安装mysql 8都是这类一路Next选Developer Default顺手把MySQL Workbench装上。要注意安装完有一个很关键的细节MySQL 8.0初始化时会自动给root生成一个临时密码放在数据目录下的xxx.err文件里第一次登录必须用这个临时密码登录后立刻改掉。很多人卡在第一步就是没去找这个单次临时密码。Linux环境用rpm安装mysql是比较常规的操作我用CentOS实测过流程大致是下载对应系统的rpm包然后rpm -ivh安装再用mysqld --initialize初始化数据目录。初始化完成后同样会生成临时密码别急着启动服务先grep temporary password /var/log/mysqld.log看一眼再systemctl start mysqld。装完验证一下命令行执行mysql --version能输出版本号就说明安装成功。这一步虽然简单但我见过不少同学装完直接双击图标发现“服务未启动”所以建议第一个命令永远先确认服务状态。1.2 命令行连接与图形化工具的选择装好后第一件事就是连上去。命令行最直接mysql -u root -p输入密码后进入mysql提示符这时候一个完整的服务器端交互环境就起来了。如果提示ERROR 1045 (28000): Access denied for user rootlocalhost多半是密码不对或者你的root账户只允许本机连接这个后面第5章详细说。图形化工具方面很多人习惯用Navicat for MySQL我也承认它界面确实顺手但正版要钱破解版咱不谈。我个人的建议是学习阶段能用命令行就命令行必要看图的时候装一个DBeaver Community或者VS Code的Database插件一样能跑SQL、看表结构。命令行的好处是让你真正理解“SQL在服务器上执行”这件事而不是在表格框里点点点。连接时还有一个高频问题mysql ssl连接错误。8.0默认开了SSL认证有些老客户端或者特殊网络环境下会握手失败报SSL connection error。排查思路是先确认服务器端SSL配置再检查客户端连接参数是否需要调整比如某些驱动要求显式关闭或指定SSL模式。这里我不建议一上来就禁SSL先看服务端是否正常开启再看证书路径对不对。1.3 先把概念分清楚实例、库、表第34节课一上来老师就强调了概念层级一个MySQL服务器可以同时运行多个数据库实例通常我们说的实例就是mysqld进程一个实例下可以创建多个数据库Database一个数据库里可以建多张表表里才是真正按行和列组织的数据。我用个生活化的类比数据库实例就像一栋写字楼数据库是一个个公司租的楼层表是楼层里的工位区行就是工位上的一个人列就是工位牌上的各项属性。创建数据库本质上是在这栋楼里给新公司划一块地盘。这个类比能帮你理顺后面所有操作CREATE DATABASE在“楼层”层面创建空间CREATE TABLE在“楼层”里摆工位。我们常说的“cmd导出sql”其实也是围绕这个概念走的——用mysqldump把整个“楼层”的结构和数据打包成一份sql脚本到另一栋楼里还原。2. 创建数据库从CREATE DATABASE开始2.1 CREATE DATABASE完整语法与字符集决策创建数据库的SQL本身很简单但里面藏着两个最容易出错的决定。标准语法长这样CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;IF NOT EXISTS是容错保护如果库已经存在MySQL不会报错而是给一条warning。我第一次写的时候没加第二次重复执行直接ERROR 1007 (HY000): Cant create database school; database exists所以现在我的习惯是凡是建库建表都带上这句。真正的关键决策是字符集。MySQL里的utf8其实只是utf8mb3只能存3字节以内的UTF-8字符这意味着emoji表情和部分生僻字会直接变成乱码甚至报错。所以新库一律用utf8mb4这个才是完整的四字节UTF-8。我见过太多次“客户端显示正常、服务器端乱码”的怪问题追到最后全是建库时图省事用了默认latin1或者老utf8。排序规则COLLATE作为配套也要选对。8.0默认的utf8mb4_0900_ai_ci就够用大小写不敏感、口音不敏感适合绝大多数中文和英文场景。如果是5.7环境常见选择是utf8mb4_general_ci。排序规则决定了字符串比较和ORDER BY的最终顺序选错不会报错但排序结果可能跟你预期不一致。建完库顺手做三件事SHOW DATABASES;看库列表USE school;切进去SELECT DATABASE();确认当前选的库。这三条命令我每节课都至少跑一遍已经成肌肉记忆了。2.2 创建表数据类型、主键与自增建库只是划地盘建表才真正定义数据格式。第34节课的示例表我敲了好几遍最后定稿是这样CREATE TABLE IF NOT EXISTS student ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, name VARCHAR(50) NOT NULL COMMENT 姓名, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, score DECIMAL(5,2) DEFAULT 0.00 COMMENT 成绩, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_email (email) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生表;几个设计要点我逐个说INT UNSIGNED配合AUTO_INCREMENT做主键是MySQL最经典的ID方案。UNSIGNED把负数空间让给正数理论上限翻倍。VARCHAR是变长字符串必须给长度。name VARCHAR(50)表示最多存50个字符但实际占用按内容长度走而不是固定50字节。这是和CHAR最大的区别。score DECIMAL(5,2)表示总长5位小数占2位最大999.99。涉及金额、分数这种精确数值绝对不用FLOAT或DOUBLE二进制浮点会有精度丢失。created_at DATETIME DEFAULT CURRENT_TIMESTAMP让数据库自动填当前时间应用层不用管。这是管理后台系统里利用率极高的默认值写法。UNIQUE KEY uk_email (email)给邮箱加了唯一索引。注意唯一索引会让重复值插入直接报错你把字段当业务唯一标识时才能这么干。建完表用DESC student;看结构用SHOW CREATE TABLE student\G看完整定义。后者会把你建表语句原样返回还能看到MySQL自动补充的默认值是排查“为什么表结构和我想的不一样”的利器。2.3 修改表结构ALTER、TRUNCATE与DROP的边界表建完之后经常要改。ALTER TABLE的常用场景ALTER TABLE student ADD COLUMN phone VARCHAR(20) AFTER email; ALTER TABLE student MODIFY COLUMN name VARCHAR(100) NOT NULL; ALTER TABLE student DROP COLUMN phone; ALTER TABLE student RENAME TO student_info;ADD COLUMN加列MODIFY COLUMN改类型或约束DROP COLUMN删列RENAME TO改表名。里面细节不少MODIFY会覆盖列的完整定义你写的时候必须把NOT NULL、DEFAULT这些约束重新声明一遍否则就被重置了。我刚学时经常只改一个类型结果其他约束丢了。清理表数据时你会面对三个选择DROP TABLE是连表带定义一起删除TRUNCATE TABLE是清空数据但保留表结构DELETE FROM student是逐行删除且可以配合WHERE。TRUNCATE不能回滚它通过重建表实现清理速度极快DELETE逐行删可以走事务回滚。表操作删错是灾难级的我的习惯是先SHOW TABLES;列出所有表名确认目标后再执行删除类语句。DROP DATABASE的操作更是要反复确认库名生产环境里一条DROP DATABASE误执行就可能让整个业务的底层数据清零。3. 运行各类SQLDML与查询实操3.1 增删改INSERT、UPDATE、DELETE的避坑要点先说插入。单行插入最直白INSERT INTO student (name, email, score) VALUES (张三, zhangsanexample.com, 88.5);多行插入就用逗号把每一行VALUES连接起来我实测过一次插几百行比一条条执行快一个量级。还有一种进阶写法INSERT INTO ... SELECT把查询结果直接灌进另一张表做数据备份或临时表很方便INSERT INTO student_bak (id, name, score) SELECT id, name, score FROM student WHERE score 60;更新和删除最大的坑都是同一个忘了加WHERE。UPDATE student SET score 100;会改全表所有行这在学习环境里无所谓生产环境就是事故。我现在养成的习惯是任何UPDATE或DELETE先写WHERE条件再回头检查一遍条件字段有没有索引、是不是真的只想动这几行。还可以先用SELECT跑一遍同样的条件确认命中的行数符合预期再改成UPDATE或DELETE执行。如果只想清空表且重置自增IDTRUNCATE TABLE student;比DELETE FROM student更彻底。但注意它不能加WHERE是整表清空也别指望事务回滚。3.2 查询核心三件套过滤、排序、去重查询是所有SQL操作里出镜率最高的第34节课花了大量篇幅在这里。最基础的骨架是SELECT column1, column2 FROM table WHERE condition;先说WHERE过滤。支持、!、、、、也支持逻辑组合AND和OR。记得加括号控制优先级否则AND和OR混在一起很容易查错范围。排序用ORDER BY对应热搜词里的mysql排序。语法是SELECT name, score FROM student ORDER BY score DESC, name ASC;先按成绩降序成绩相同时再按姓名升序。默认是ASC升序想从高到低就显式写DESC。排序字段最好有索引覆盖否则数据量大时会拖慢查询。去重用DISTINCT对应热搜里的sql语句去重SELECT DISTINCT name FROM student;这里有个新手经常误会的点DISTINCT作用于后面所有选中列的组合不是只作用于第一列。SELECT DISTINCT name, score FROM student得到的是name score组合不重复的结果如果两行name相同但score不同两条都会被显示。想真正对单列去重只能用子查询或GROUP BY。分页查询是实际项目里几乎每天都用的LIMIT关键字。LIMIT 10取前10行LIMIT 10, 20表示跳过10行取20行等价于LIMIT 20 OFFSET 10。分页的坑在深翻页偏移量越大越慢后面慢SQL优化那节再展开。3.3 进阶过滤空值、模糊匹配与范围查询三大高频过滤场景我一个个说。空值处理对应热搜里的sql去除空值。SQL里NULL代表“未知”它不是空字符串也不是数字0。判断空值必须用IS NULL或IS NOT NULL写WHERE email NULL永远查不到数据因为NULL NULL的结果也是NULL条件不成立。SELECT * FROM student WHERE email IS NOT NULL; SELECT * FROM student WHERE COALESCE(email, 无邮箱) 无邮箱;COALESCE函数把NULL替换成默认值是个很实用的小技巧在SELECT结果里展示时不至于满屏NULL。模糊匹配用LIKE配合%和_两个通配符。%代表任意长度的任意字符_代表单个任意字符SELECT * FROM student WHERE name LIKE 张%; -- 张三、张伟、张无忌 SELECT * FROM student WHERE name LIKE 张_; -- 只匹配两个字的名字比如张三注意一点%开头比如%张%会导致索引失效全表扫描。数据量小无所谓大了就慢。真到了那一步可以考虑全文索引或者搜索引擎方案。范围查询用BETWEEN AND和INSELECT * FROM student WHERE score BETWEEN 60 AND 90; SELECT * FROM student WHERE name IN (张三, 李四);BETWEEN AND是闭区间包含两端值。IN后面跟的是一个列表匹配其中任意一个即可。这两招写起来简洁可读性也比一堆OR好很多。3.4 聚合与分组COUNT、SUM、GROUP BY与HAVING聚合函数不处理具体行它们把多行压成一个结果。最常用的是SELECT COUNT(*) FROM student; -- 总行数 SELECT AVG(score), MAX(score), MIN(score) FROM student; SELECT SUM(score) FROM student;COUNT(*)数所有行COUNT(1)没有本质区别而COUNT(email)只数非NULL的行。如果你想统计“有多少人填了邮箱”必须用后者否则NULL会被忽略这个语义就体现不出来。分组统计是数据分析的基础操作SELECT name, COUNT(*), AVG(score) FROM student GROUP BY name;GROUP BY把相同字段值的行合成一组然后对每组做聚合。这里最容易踩的坑是ONLY_FULL_GROUP_BY模式MySQL 5.7以后默认开启SELECT里出现的非聚合列必须出现在GROUP BY里否则直接报错。也就是说SELECT id, name, COUNT(*) ... GROUP BY name里如果带上id而id不在GROUP BY里就会报错。分组后的条件过滤不能用WHERE得用HAVINGWHERE过滤原始行HAVING过滤分组后的结果。SELECT name, AVG(score) AS avg_score FROM student GROUP BY name HAVING AVG(score) 80;这个场景我第34节课犯的错就是拿WHERE avg_score 80去跑结果报错说找不到avg_score列——因为别名在WHERE阶段还没生成。4. 运行SQL的进阶玩法索引、事务与存储过程4.1 索引原理与EXPLAIN使用入门索引这块内容课上可能不会一步讲透但你要是只学建库建表而不懂索引后面遇到慢sql优化热搜词之一必然会懵。我先给个通俗解释没有索引的表MySQL查一行数据只能从头到尾一行行扫像在一本没有目录的书里找一句话这叫全表扫描有了索引就相当于书末尾多了个按拼音排序的关键词目录查起来直接翻页。创建索引常用两种方式CREATE INDEX idx_score ON student(score); ALTER TABLE student ADD INDEX idx_name_score (name, score);第二种是联合索引两个字段按顺序排列。联合索引有“最左前缀原则”查询条件必须从索引最左列开始跳过后面的列索引用不上。比如idx_name_score能加速WHERE name 张三但如果只写WHERE score 90这个联合索引基本白建。如何判断一条SQL有没有用上索引用EXPLAINEXPLAIN SELECT * FROM student WHERE name 张三;看结果里的type列const或ref说明走了索引非常快ALL就代表全表扫描需要优化。还要看rows列MySQL预估扫描了多少行数字越小越好。我刚学的时候对所有可疑SQL都过一遍EXPLAIN写慢查询分析报告很快就能上手。索引不是越多越好。每个索引都要占存储空间写入时还要同步维护写多读少的表加一堆索引反而拖慢INSERT和UPDATE。4.2 事务ACID与转账场景实操事务是MySQL中保证数据一致性的核心机制对应热搜里的mysql事务处理。简单说事务把多条SQL打包成一个“要么全成功、要么全失败”的整体。标准操作是START TRANSACTION; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT;如果第二步执行出错执行ROLLBACK;就把第一步的扣款撤销两边账户都不会出现“钱扣了但对方没到账”的问题。事务有四个特性ACID原子性Atomicity对应上面的“全成或全败”一致性Consistency保证数据约束不被破坏隔离性Isolation让并发事务互不干扰持久性Durability保证提交后数据不丢。MySQL默认autocommit1也就是你单独执行一条INSERT或UPDATE会自动提交只有当显式START TRANSACTION后多步操作才真正在一个事务里。有一点必须注意CREATE DATABASE、DROP DATABASE、ALTER TABLE这些DDL语句会在执行时隐式提交当前事务想靠ROLLBACK救回DDL是不可能的。这也是为什么删表删库操作要格外谨慎。4.3 存储过程与连接池从单条SQL到工程化存储过程是把一段逻辑固化在数据库里比如DELIMITER // CREATE PROCEDURE get_student_count(OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM student; END// DELIMITER ; CALL get_student_count(c); SELECT c;定义存储过程时用DELIMITER //临时把分隔符改成//因为存储过程内部包含多个分号不改的话MySQL会在第一个分号处就截断语法直接报错。存储过程适合高度稳定的、不想让业务层接触底层表的统计类逻辑但过度使用也会让业务逻辑下沉到数据库后期维护很痛苦。连接池则是工程向的一个重要概念搜索热词里的mysql的数据库连接池就是这么回事。JavaWeb项目对应热搜javaweb项目完整案例mysql里应用启动时会提前创建一批到MySQL的连接放池子里谁要用谁取用完归还而不是每次请求都重新握手建立连接。建立数据库连接的握手开销很大池化是标配。常见的连接池有HikariCP、Druid面试基本必问理解了“连接复用”四个字就不会慌。C、Java这些语言后续要连MySQL本质也是走一套客户端协议。C可以用MySQL Connector/CJava用JDBC但上层都会覆盖一层连接池避免频繁建连。第34节课能跑通命令行SQL后面接编程语言的思路其实是同一套建立连接、执行语句、处理结果、释放连接。5. 学习笔记里的常见问题速查5.1 连接与认证类问题我在第34节课前后积累了一批高频报错第一个就是ERROR 1045 Access denied。这个报错原因有三种用户不存在、密码错误、该用户不允许从当前IP连接。排查顺序先试mysql -u root -p是不是本地密码错再看用户表SELECT user, host FROM mysql.user;host是localhost表示只能本机连远程连接需要单独建user%这样带通配符的账户并授权。第二个高频问题是SSL连接报错对应热搜里的mysql ssl连接错误。MySQL 8.0默认启用SSL客户端连接时如果服务端证书配置异常、或者客户端协议版本过老就可能握手失败。不用一上来就禁SSL先确认服务端SHOW VARIABLES LIKE %ssl%状态正常再排查客户端驱动版本。第三个是Windows服务启动失败比如错误码e0434352。这类.NET运行时异常通常和系统运行库、服务权限有关。排查方向确认MySQL服务账户有数据目录读写权限检查Windows事件查看器里的具体异常堆栈必要时重装对应VC运行库。别一上来就重装MySQL先看日志。密码过期也是个典型的坑只要开启了default_password_lifetime用户密码到期后客户端连接会报ERROR 1862 ... password has expired。处理办法ALTER USER rootlocalhost IDENTIFIED BY 新密码;或者直接修改全局生命周期配置。这也是热搜里sql server 2012密码到期类似场景的MySQL版本原理相同认证过期后必须重设。5.2 执行SQL时的报错排查与乱码问题SQL执行时报错最经典的一种是提示字段不存在类似Unknown column test_url in field list跟搜索热词里那条SQLite报错“no such column: test_url”是同一个套路。原因一般有三个表结构里真没这个字段、字段名拼错、或者这张表和你以为的表不是同一个。排查的顺序是先DESC 表名;确认字段存在再检查SQL里的字段名是否完全一致最后确认当前连接的库用SELECT DATABASE();有没有选对库。语法错误的报错是ERROR 1064 (42000)它会提示出错的SQL位置但位置不一定就是真正问题所在。我的排查技巧是把一大段SQL拆成小片段逐段执行先去掉所有WHERE条件再一层层加回来定位到第一个让MySQL报错的片段。第34节课上有一次我只漏了个逗号找了好几分钟就是因为整段执行没分段。乱码问题也是高频现场。确认三处字符集一致数据库字符集、连接字符集、客户端字符集。命令行里可以执行SET NAMES utf8mb4;把当前会话的character_set_client、character_set_connection、character_set_results一次设好。如果数据已经乱码通常是写入时客户端字符集不对先检查是不是从一开始就统一用了utf8mb4再用ALTER DATABASE或ALTER TABLE统一库表和列字符集。字符集底子不好后面全是泪。5.3 慢SQL优化思路与自检清单搜索热词里有一条是慢sql优化这里给一套我在学习阶段总结的自检清单。第一步打开慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;凡是执行超过1秒的SQL都会被记录下来Linux下日志默认在数据目录的-slow.log文件里。第二步拿到慢SQL后立刻EXPLAIN重点看type列和rows列。type是ALL基本就是在全表扫rows估算值越大越危险。优化方向按性价比排序优先看是不是缺索引WHERE和ORDER BY字段要不要建索引再看是不是频繁使用%关键字%这种前置模糊搜索再看有没有SELECT *按理只取需要的列能减少网络传输和临时表开销。深分页问题在数据量大时尤其明显LIMIT 1000000, 10会扫描前面100万行再丢掉优化手段一般是改写成基于上次最大ID的“键集分页”keyset pagination类似WHERE id 1000000 ORDER BY id LIMIT 10。学习阶段能把上面这套自查跑完慢SQL优化就算入了门。以后接触更为复杂的场景比如用Flink把MySQL同步到ClickHouse之类的实时数仓方案本质依然是对MySQL日志和数据的深度利用前提还是对基础SQL烂熟于心。我个人上完这节课最大的体会是SQL这玩意儿看十遍不如手敲一遍。今天建库时我就吃过一次字符集的亏表结构设计也返工了两轮但这些错早犯比晚犯好。建议你课后自己建一个练习库把今天涉及的所有语句都跑一遍再去试试修改表结构、跑聚合分组顺便用EXPLAIN看看自己写的查询性能如何。真到了能默写常用SQL的程度再看索引和事务会豁然开朗。最后一个小技巧建库建表语句一定要存成sql脚本文件放在项目里别只在命令行敲完就关后面迁移环境时这就是你的救命稻草。
折腾了一上午终于把第34节课的内容吃透了创建数据库然后把各种SQL语句挨个跑了一遍。说实话这节课在教程里排得不算靠后但对于刚装好MySQL准备练基本功的人来说恰恰是最容易“卡壳”的地方——不是语法背不下来而是你不知道为什么这样写、换了环境怎么调、报错该从哪里排查。这篇笔记把我从“建库”到“各种SQL都能跑通”的完整过程记录下来适合刚装完MySQL、准备跟着教程敲命令的同学也适合想系统性复盘一遍建库、建表、增删改查、索引事务的老手。这篇笔记里没有花哨的东西全是命令行里实测过的语句以及我在第34节课上踩过的坑。你跟着敲一遍基本就能把MySQL的日常操作串起来了。1. 环境准备装好MySQL才能谈创建数据库1.1 安装版本选型与安装方式实测先说版本。现在新入门我建议直接装MySQL 8.0系列包括最新的8.0.44等小版本而不是还在网上大量流传的5.7.26。原因不复杂8.0自带utf8mb4默认字符集、窗口函数、公用表表达式CTE这些现代SQL能力而且默认认证插件是caching_sha2_password整体更安全。5.7不是不能用但官方已经逐步走向寿命末期新项目再踩那个坑没必要。安装方式上Windows环境直接下载MySQL Installer的exe你搜到的mysql 50616版本exe、windows安装mysql 8都是这类一路Next选Developer Default顺手把MySQL Workbench装上。要注意安装完有一个很关键的细节MySQL 8.0初始化时会自动给root生成一个临时密码放在数据目录下的xxx.err文件里第一次登录必须用这个临时密码登录后立刻改掉。很多人卡在第一步就是没去找这个单次临时密码。Linux环境用rpm安装mysql是比较常规的操作我用CentOS实测过流程大致是下载对应系统的rpm包然后rpm -ivh安装再用mysqld --initialize初始化数据目录。初始化完成后同样会生成临时密码别急着启动服务先grep temporary password /var/log/mysqld.log看一眼再systemctl start mysqld。装完验证一下命令行执行mysql --version能输出版本号就说明安装成功。这一步虽然简单但我见过不少同学装完直接双击图标发现“服务未启动”所以建议第一个命令永远先确认服务状态。1.2 命令行连接与图形化工具的选择装好后第一件事就是连上去。命令行最直接mysql -u root -p输入密码后进入mysql提示符这时候一个完整的服务器端交互环境就起来了。如果提示ERROR 1045 (28000): Access denied for user rootlocalhost多半是密码不对或者你的root账户只允许本机连接这个后面第5章详细说。图形化工具方面很多人习惯用Navicat for MySQL我也承认它界面确实顺手但正版要钱破解版咱不谈。我个人的建议是学习阶段能用命令行就命令行必要看图的时候装一个DBeaver Community或者VS Code的Database插件一样能跑SQL、看表结构。命令行的好处是让你真正理解“SQL在服务器上执行”这件事而不是在表格框里点点点。连接时还有一个高频问题mysql ssl连接错误。8.0默认开了SSL认证有些老客户端或者特殊网络环境下会握手失败报SSL connection error。排查思路是先确认服务器端SSL配置再检查客户端连接参数是否需要调整比如某些驱动要求显式关闭或指定SSL模式。这里我不建议一上来就禁SSL先看服务端是否正常开启再看证书路径对不对。1.3 先把概念分清楚实例、库、表第34节课一上来老师就强调了概念层级一个MySQL服务器可以同时运行多个数据库实例通常我们说的实例就是mysqld进程一个实例下可以创建多个数据库Database一个数据库里可以建多张表表里才是真正按行和列组织的数据。我用个生活化的类比数据库实例就像一栋写字楼数据库是一个个公司租的楼层表是楼层里的工位区行就是工位上的一个人列就是工位牌上的各项属性。创建数据库本质上是在这栋楼里给新公司划一块地盘。这个类比能帮你理顺后面所有操作CREATE DATABASE在“楼层”层面创建空间CREATE TABLE在“楼层”里摆工位。我们常说的“cmd导出sql”其实也是围绕这个概念走的——用mysqldump把整个“楼层”的结构和数据打包成一份sql脚本到另一栋楼里还原。2. 创建数据库从CREATE DATABASE开始2.1 CREATE DATABASE完整语法与字符集决策创建数据库的SQL本身很简单但里面藏着两个最容易出错的决定。标准语法长这样CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;IF NOT EXISTS是容错保护如果库已经存在MySQL不会报错而是给一条warning。我第一次写的时候没加第二次重复执行直接ERROR 1007 (HY000): Cant create database school; database exists所以现在我的习惯是凡是建库建表都带上这句。真正的关键决策是字符集。MySQL里的utf8其实只是utf8mb3只能存3字节以内的UTF-8字符这意味着emoji表情和部分生僻字会直接变成乱码甚至报错。所以新库一律用utf8mb4这个才是完整的四字节UTF-8。我见过太多次“客户端显示正常、服务器端乱码”的怪问题追到最后全是建库时图省事用了默认latin1或者老utf8。排序规则COLLATE作为配套也要选对。8.0默认的utf8mb4_0900_ai_ci就够用大小写不敏感、口音不敏感适合绝大多数中文和英文场景。如果是5.7环境常见选择是utf8mb4_general_ci。排序规则决定了字符串比较和ORDER BY的最终顺序选错不会报错但排序结果可能跟你预期不一致。建完库顺手做三件事SHOW DATABASES;看库列表USE school;切进去SELECT DATABASE();确认当前选的库。这三条命令我每节课都至少跑一遍已经成肌肉记忆了。2.2 创建表数据类型、主键与自增建库只是划地盘建表才真正定义数据格式。第34节课的示例表我敲了好几遍最后定稿是这样CREATE TABLE IF NOT EXISTS student ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, name VARCHAR(50) NOT NULL COMMENT 姓名, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, score DECIMAL(5,2) DEFAULT 0.00 COMMENT 成绩, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_email (email) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生表;几个设计要点我逐个说INT UNSIGNED配合AUTO_INCREMENT做主键是MySQL最经典的ID方案。UNSIGNED把负数空间让给正数理论上限翻倍。VARCHAR是变长字符串必须给长度。name VARCHAR(50)表示最多存50个字符但实际占用按内容长度走而不是固定50字节。这是和CHAR最大的区别。score DECIMAL(5,2)表示总长5位小数占2位最大999.99。涉及金额、分数这种精确数值绝对不用FLOAT或DOUBLE二进制浮点会有精度丢失。created_at DATETIME DEFAULT CURRENT_TIMESTAMP让数据库自动填当前时间应用层不用管。这是管理后台系统里利用率极高的默认值写法。UNIQUE KEY uk_email (email)给邮箱加了唯一索引。注意唯一索引会让重复值插入直接报错你把字段当业务唯一标识时才能这么干。建完表用DESC student;看结构用SHOW CREATE TABLE student\G看完整定义。后者会把你建表语句原样返回还能看到MySQL自动补充的默认值是排查“为什么表结构和我想的不一样”的利器。2.3 修改表结构ALTER、TRUNCATE与DROP的边界表建完之后经常要改。ALTER TABLE的常用场景ALTER TABLE student ADD COLUMN phone VARCHAR(20) AFTER email; ALTER TABLE student MODIFY COLUMN name VARCHAR(100) NOT NULL; ALTER TABLE student DROP COLUMN phone; ALTER TABLE student RENAME TO student_info;ADD COLUMN加列MODIFY COLUMN改类型或约束DROP COLUMN删列RENAME TO改表名。里面细节不少MODIFY会覆盖列的完整定义你写的时候必须把NOT NULL、DEFAULT这些约束重新声明一遍否则就被重置了。我刚学时经常只改一个类型结果其他约束丢了。清理表数据时你会面对三个选择DROP TABLE是连表带定义一起删除TRUNCATE TABLE是清空数据但保留表结构DELETE FROM student是逐行删除且可以配合WHERE。TRUNCATE不能回滚它通过重建表实现清理速度极快DELETE逐行删可以走事务回滚。表操作删错是灾难级的我的习惯是先SHOW TABLES;列出所有表名确认目标后再执行删除类语句。DROP DATABASE的操作更是要反复确认库名生产环境里一条DROP DATABASE误执行就可能让整个业务的底层数据清零。3. 运行各类SQLDML与查询实操3.1 增删改INSERT、UPDATE、DELETE的避坑要点先说插入。单行插入最直白INSERT INTO student (name, email, score) VALUES (张三, zhangsanexample.com, 88.5);多行插入就用逗号把每一行VALUES连接起来我实测过一次插几百行比一条条执行快一个量级。还有一种进阶写法INSERT INTO ... SELECT把查询结果直接灌进另一张表做数据备份或临时表很方便INSERT INTO student_bak (id, name, score) SELECT id, name, score FROM student WHERE score 60;更新和删除最大的坑都是同一个忘了加WHERE。UPDATE student SET score 100;会改全表所有行这在学习环境里无所谓生产环境就是事故。我现在养成的习惯是任何UPDATE或DELETE先写WHERE条件再回头检查一遍条件字段有没有索引、是不是真的只想动这几行。还可以先用SELECT跑一遍同样的条件确认命中的行数符合预期再改成UPDATE或DELETE执行。如果只想清空表且重置自增IDTRUNCATE TABLE student;比DELETE FROM student更彻底。但注意它不能加WHERE是整表清空也别指望事务回滚。3.2 查询核心三件套过滤、排序、去重查询是所有SQL操作里出镜率最高的第34节课花了大量篇幅在这里。最基础的骨架是SELECT column1, column2 FROM table WHERE condition;先说WHERE过滤。支持、!、、、、也支持逻辑组合AND和OR。记得加括号控制优先级否则AND和OR混在一起很容易查错范围。排序用ORDER BY对应热搜词里的mysql排序。语法是SELECT name, score FROM student ORDER BY score DESC, name ASC;先按成绩降序成绩相同时再按姓名升序。默认是ASC升序想从高到低就显式写DESC。排序字段最好有索引覆盖否则数据量大时会拖慢查询。去重用DISTINCT对应热搜里的sql语句去重SELECT DISTINCT name FROM student;这里有个新手经常误会的点DISTINCT作用于后面所有选中列的组合不是只作用于第一列。SELECT DISTINCT name, score FROM student得到的是name score组合不重复的结果如果两行name相同但score不同两条都会被显示。想真正对单列去重只能用子查询或GROUP BY。分页查询是实际项目里几乎每天都用的LIMIT关键字。LIMIT 10取前10行LIMIT 10, 20表示跳过10行取20行等价于LIMIT 20 OFFSET 10。分页的坑在深翻页偏移量越大越慢后面慢SQL优化那节再展开。3.3 进阶过滤空值、模糊匹配与范围查询三大高频过滤场景我一个个说。空值处理对应热搜里的sql去除空值。SQL里NULL代表“未知”它不是空字符串也不是数字0。判断空值必须用IS NULL或IS NOT NULL写WHERE email NULL永远查不到数据因为NULL NULL的结果也是NULL条件不成立。SELECT * FROM student WHERE email IS NOT NULL; SELECT * FROM student WHERE COALESCE(email, 无邮箱) 无邮箱;COALESCE函数把NULL替换成默认值是个很实用的小技巧在SELECT结果里展示时不至于满屏NULL。模糊匹配用LIKE配合%和_两个通配符。%代表任意长度的任意字符_代表单个任意字符SELECT * FROM student WHERE name LIKE 张%; -- 张三、张伟、张无忌 SELECT * FROM student WHERE name LIKE 张_; -- 只匹配两个字的名字比如张三注意一点%开头比如%张%会导致索引失效全表扫描。数据量小无所谓大了就慢。真到了那一步可以考虑全文索引或者搜索引擎方案。范围查询用BETWEEN AND和INSELECT * FROM student WHERE score BETWEEN 60 AND 90; SELECT * FROM student WHERE name IN (张三, 李四);BETWEEN AND是闭区间包含两端值。IN后面跟的是一个列表匹配其中任意一个即可。这两招写起来简洁可读性也比一堆OR好很多。3.4 聚合与分组COUNT、SUM、GROUP BY与HAVING聚合函数不处理具体行它们把多行压成一个结果。最常用的是SELECT COUNT(*) FROM student; -- 总行数 SELECT AVG(score), MAX(score), MIN(score) FROM student; SELECT SUM(score) FROM student;COUNT(*)数所有行COUNT(1)没有本质区别而COUNT(email)只数非NULL的行。如果你想统计“有多少人填了邮箱”必须用后者否则NULL会被忽略这个语义就体现不出来。分组统计是数据分析的基础操作SELECT name, COUNT(*), AVG(score) FROM student GROUP BY name;GROUP BY把相同字段值的行合成一组然后对每组做聚合。这里最容易踩的坑是ONLY_FULL_GROUP_BY模式MySQL 5.7以后默认开启SELECT里出现的非聚合列必须出现在GROUP BY里否则直接报错。也就是说SELECT id, name, COUNT(*) ... GROUP BY name里如果带上id而id不在GROUP BY里就会报错。分组后的条件过滤不能用WHERE得用HAVINGWHERE过滤原始行HAVING过滤分组后的结果。SELECT name, AVG(score) AS avg_score FROM student GROUP BY name HAVING AVG(score) 80;这个场景我第34节课犯的错就是拿WHERE avg_score 80去跑结果报错说找不到avg_score列——因为别名在WHERE阶段还没生成。4. 运行SQL的进阶玩法索引、事务与存储过程4.1 索引原理与EXPLAIN使用入门索引这块内容课上可能不会一步讲透但你要是只学建库建表而不懂索引后面遇到慢sql优化热搜词之一必然会懵。我先给个通俗解释没有索引的表MySQL查一行数据只能从头到尾一行行扫像在一本没有目录的书里找一句话这叫全表扫描有了索引就相当于书末尾多了个按拼音排序的关键词目录查起来直接翻页。创建索引常用两种方式CREATE INDEX idx_score ON student(score); ALTER TABLE student ADD INDEX idx_name_score (name, score);第二种是联合索引两个字段按顺序排列。联合索引有“最左前缀原则”查询条件必须从索引最左列开始跳过后面的列索引用不上。比如idx_name_score能加速WHERE name 张三但如果只写WHERE score 90这个联合索引基本白建。如何判断一条SQL有没有用上索引用EXPLAINEXPLAIN SELECT * FROM student WHERE name 张三;看结果里的type列const或ref说明走了索引非常快ALL就代表全表扫描需要优化。还要看rows列MySQL预估扫描了多少行数字越小越好。我刚学的时候对所有可疑SQL都过一遍EXPLAIN写慢查询分析报告很快就能上手。索引不是越多越好。每个索引都要占存储空间写入时还要同步维护写多读少的表加一堆索引反而拖慢INSERT和UPDATE。4.2 事务ACID与转账场景实操事务是MySQL中保证数据一致性的核心机制对应热搜里的mysql事务处理。简单说事务把多条SQL打包成一个“要么全成功、要么全失败”的整体。标准操作是START TRANSACTION; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT;如果第二步执行出错执行ROLLBACK;就把第一步的扣款撤销两边账户都不会出现“钱扣了但对方没到账”的问题。事务有四个特性ACID原子性Atomicity对应上面的“全成或全败”一致性Consistency保证数据约束不被破坏隔离性Isolation让并发事务互不干扰持久性Durability保证提交后数据不丢。MySQL默认autocommit1也就是你单独执行一条INSERT或UPDATE会自动提交只有当显式START TRANSACTION后多步操作才真正在一个事务里。有一点必须注意CREATE DATABASE、DROP DATABASE、ALTER TABLE这些DDL语句会在执行时隐式提交当前事务想靠ROLLBACK救回DDL是不可能的。这也是为什么删表删库操作要格外谨慎。4.3 存储过程与连接池从单条SQL到工程化存储过程是把一段逻辑固化在数据库里比如DELIMITER // CREATE PROCEDURE get_student_count(OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM student; END// DELIMITER ; CALL get_student_count(c); SELECT c;定义存储过程时用DELIMITER //临时把分隔符改成//因为存储过程内部包含多个分号不改的话MySQL会在第一个分号处就截断语法直接报错。存储过程适合高度稳定的、不想让业务层接触底层表的统计类逻辑但过度使用也会让业务逻辑下沉到数据库后期维护很痛苦。连接池则是工程向的一个重要概念搜索热词里的mysql的数据库连接池就是这么回事。JavaWeb项目对应热搜javaweb项目完整案例mysql里应用启动时会提前创建一批到MySQL的连接放池子里谁要用谁取用完归还而不是每次请求都重新握手建立连接。建立数据库连接的握手开销很大池化是标配。常见的连接池有HikariCP、Druid面试基本必问理解了“连接复用”四个字就不会慌。C、Java这些语言后续要连MySQL本质也是走一套客户端协议。C可以用MySQL Connector/CJava用JDBC但上层都会覆盖一层连接池避免频繁建连。第34节课能跑通命令行SQL后面接编程语言的思路其实是同一套建立连接、执行语句、处理结果、释放连接。5. 学习笔记里的常见问题速查5.1 连接与认证类问题我在第34节课前后积累了一批高频报错第一个就是ERROR 1045 Access denied。这个报错原因有三种用户不存在、密码错误、该用户不允许从当前IP连接。排查顺序先试mysql -u root -p是不是本地密码错再看用户表SELECT user, host FROM mysql.user;host是localhost表示只能本机连远程连接需要单独建user%这样带通配符的账户并授权。第二个高频问题是SSL连接报错对应热搜里的mysql ssl连接错误。MySQL 8.0默认启用SSL客户端连接时如果服务端证书配置异常、或者客户端协议版本过老就可能握手失败。不用一上来就禁SSL先确认服务端SHOW VARIABLES LIKE %ssl%状态正常再排查客户端驱动版本。第三个是Windows服务启动失败比如错误码e0434352。这类.NET运行时异常通常和系统运行库、服务权限有关。排查方向确认MySQL服务账户有数据目录读写权限检查Windows事件查看器里的具体异常堆栈必要时重装对应VC运行库。别一上来就重装MySQL先看日志。密码过期也是个典型的坑只要开启了default_password_lifetime用户密码到期后客户端连接会报ERROR 1862 ... password has expired。处理办法ALTER USER rootlocalhost IDENTIFIED BY 新密码;或者直接修改全局生命周期配置。这也是热搜里sql server 2012密码到期类似场景的MySQL版本原理相同认证过期后必须重设。5.2 执行SQL时的报错排查与乱码问题SQL执行时报错最经典的一种是提示字段不存在类似Unknown column test_url in field list跟搜索热词里那条SQLite报错“no such column: test_url”是同一个套路。原因一般有三个表结构里真没这个字段、字段名拼错、或者这张表和你以为的表不是同一个。排查的顺序是先DESC 表名;确认字段存在再检查SQL里的字段名是否完全一致最后确认当前连接的库用SELECT DATABASE();有没有选对库。语法错误的报错是ERROR 1064 (42000)它会提示出错的SQL位置但位置不一定就是真正问题所在。我的排查技巧是把一大段SQL拆成小片段逐段执行先去掉所有WHERE条件再一层层加回来定位到第一个让MySQL报错的片段。第34节课上有一次我只漏了个逗号找了好几分钟就是因为整段执行没分段。乱码问题也是高频现场。确认三处字符集一致数据库字符集、连接字符集、客户端字符集。命令行里可以执行SET NAMES utf8mb4;把当前会话的character_set_client、character_set_connection、character_set_results一次设好。如果数据已经乱码通常是写入时客户端字符集不对先检查是不是从一开始就统一用了utf8mb4再用ALTER DATABASE或ALTER TABLE统一库表和列字符集。字符集底子不好后面全是泪。5.3 慢SQL优化思路与自检清单搜索热词里有一条是慢sql优化这里给一套我在学习阶段总结的自检清单。第一步打开慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;凡是执行超过1秒的SQL都会被记录下来Linux下日志默认在数据目录的-slow.log文件里。第二步拿到慢SQL后立刻EXPLAIN重点看type列和rows列。type是ALL基本就是在全表扫rows估算值越大越危险。优化方向按性价比排序优先看是不是缺索引WHERE和ORDER BY字段要不要建索引再看是不是频繁使用%关键字%这种前置模糊搜索再看有没有SELECT *按理只取需要的列能减少网络传输和临时表开销。深分页问题在数据量大时尤其明显LIMIT 1000000, 10会扫描前面100万行再丢掉优化手段一般是改写成基于上次最大ID的“键集分页”keyset pagination类似WHERE id 1000000 ORDER BY id LIMIT 10。学习阶段能把上面这套自查跑完慢SQL优化就算入了门。以后接触更为复杂的场景比如用Flink把MySQL同步到ClickHouse之类的实时数仓方案本质依然是对MySQL日志和数据的深度利用前提还是对基础SQL烂熟于心。我个人上完这节课最大的体会是SQL这玩意儿看十遍不如手敲一遍。今天建库时我就吃过一次字符集的亏表结构设计也返工了两轮但这些错早犯比晚犯好。建议你课后自己建一个练习库把今天涉及的所有语句都跑一遍再去试试修改表结构、跑聚合分组顺便用EXPLAIN看看自己写的查询性能如何。真到了能默写常用SQL的程度再看索引和事务会豁然开朗。最后一个小技巧建库建表语句一定要存成sql脚本文件放在项目里别只在命令行敲完就关后面迁移环境时这就是你的救命稻草。
