MySQL实战入门：一份源码吃透建库、表设计到存储过程

简介这是一份MySQL数据库基础实例教程第三版微课版配套源代码压缩包面向希望系统掌握数据库基础操作与项目实战的读者配合教材完成从例题到综合项目的完整练习。压缩包内共有五个文件包含四个SQL脚本与一个说明文档分别对应书中例题、书店商业案例、图书馆综合实训、学校实战演练等模块可直接导入数据库逐项运行。资源包体积仅二十一KB轻量精简便于快速下载和本地实验。目前已有115人参与学习代码按章节与案例命名层次清晰读者既能通过基础例题巩固增删改查、表结构设计等核心技能也能在实训与实战项目中理解业务表建模、数据迁移和性能优化思路是提升MySQL应用能力的高性价比辅助资料适合按需查阅对照实践。1. 用一份源码吃透MySQL四个项目把数据库从建库到实战全串起来打开这份《MySQL数据库基础实例教程第3版微课版》的配套源代码文件清单很清爽bookstore_书中例题源代码.sql 对应例题petstore_商业实例源代码.sql 对应案例librarydb_综合实训源代码.sql 对应实训SchoolDB_实战演练源代码.sql 对应实战。对自学MySQL的人来说最难的不是看懂某一条SQL而是缺少一套能反复折腾的完整脚本——从建库、建表、插数据到写联表查询和存储过程。这份资源恰好把一个知识体系拆成了四个递进阶段先跟着例题把数据库增删改查练熟再通过宠物商店接触真实业务表设计接着在图书馆系统里看视图和存储过程的封装最后用学校教务系统体验项目从零到一的全过程。适合刚入门MySQL、正在做课程设计或准备数据库方向毕设的读者。2. 源码包结构与库表设计先把四个项目的边界和关系看清楚拿到源码别急着往MySQL里灌。我习惯先打开每个SQL文件扫一遍开头的CREATE DATABASE和CREATE TABLE搞清楚这套东西的边界。书里四个项目的建库脚本能直接用但它们对应的业务场景完全不同表结构设计思路也不一样先看清再动手能省掉后面一大半排错时间。2.1 四个SQL文件分别是什么场景从例题到实战的递进关系用一张表把这四个文件的关系理清楚文件项目类型业务场景适合阶段bookstore_书中例题源代码.sql例题图书商城跟着书上章节边学边敲petstore_商业实例源代码.sql案例宠物商店含订单、库存、会员学完基础后练习联表与事务librarydb_综合实训源代码.sql实训图书馆借阅管理系统综合训练存储过程、视图、触发器SchoolDB_实战演练源代码.sql实战学校教务系统含学生、课程、成绩课程设计或毕设参考很多人分不清“例题”和“实战”的区别。例题库表少、查询简单目的是让你在命令行里反复敲把SQL语法练成肌肉记忆实战库表多、关系复杂是为了模拟真实开发的节奏数据表之间有关联、有约束、有业务逻辑。所以复现的时候别一开始就直奔SchoolDB按顺序来才有效果。2.2 建表语句里的关键选择字符集、存储引擎、外键怎么定打开bookstore脚本建表语句通常是这样开头的我以书中脚本最常见的写法为例CREATE TABLE IF NOT EXISTS t_book ( book_id INT AUTO_INCREMENT PRIMARY KEY, book_name VARCHAR(100) NOT NULL, price DECIMAL(10,2) NOT NULL, stock INT DEFAULT 0, publish_date DATE, INDEX idx_book_name (book_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT图书表;这段建表语句值得逐行拆开看。ENGINEInnoDB指定存储引擎它支持事务、外键和行级锁是OLTP场景下的默认选择。有些人图查询快把引擎改成MyISAM结果遇到跨表更新时既没有事务回滚也没法用外键数据一乱就要手动修属于典型的给自己挖坑。DEFAULT CHARSETutf8mb4是字符集设置utf8mb4兼容完整的Unicode中文、生僻字、emoji都不会乱码比老的utf8更稳。price字段用DECIMAL(10,2)而不是FLOAT因为金额这种数据对精度敏感浮点数在累加时会出0.10.20.30000000000000004这种问题。AUTO_INCREMENT做主键自增INDEX idx_book_name给book_name加了普通索引后面按书名查会快很多。我一般建议初学者把脚本里的ENGINE和CHARSET当作模板不要随意改动。存储引擎决定了行为边界字符集决定了数据能不能正确落库这两项在导入数据之后再改代价非常高。2.3 导入前必做的三件事版本、工具、编码第一确认MySQL版本。这份教程基于较新的MySQL编写导入前先看服务端版本避免脚本里用了新语法但本地是老版本mysql -u root -p -e SELECT VERSION();第二确认导入工具。命令行mysql客户端最稳定脚本文件再大也不怕Navicat和MySQL Workbench也能导入但要注意它们默认的字符集可能和脚本不一致导入后中文变问号多半就是工具设置的问题。如果你还在纠结mysql安装配置教程里那些初始化步骤其实只要让服务能正常启动、root能登录就够了。第三确认文件编码。SQL文件必须是UTF-8Windows下用记事本另存为容易存成ANSI或带BOM的UTF-8导入后中文全是乱码。我一般用VS Code打开文件看右下角编码再执行一次Reopen with Encoding确认是UTF-8。再补一个常见检查列表检查项操作异常表现MySQL版本SELECT VERSION()5.5以下很多语法不支持文件编码VS Code右下角查看中文乱码或首行报错数据库是否已存在SHOW DATABASES重复导入报库已存在导入顺序同样重要。因为外键约束的存在先导入被引用的主表再导入引用外键的从表。书里的脚本文件名已经体现了顺序实际操作中如果报外键错误优先怀疑是不是跨文件导错了顺序。提示如果导入过程中报错先在脚本里搜索“SET NAMES”确认是否指定了utf8mb4。没指定的话导入前加一句SET NAMES utf8mb4;再重跑。3. 从例题到案例的复现路径SQL脚本怎么跑、参数怎么调这一章解决的核心问题是“脚本到手后怎么让它跑起来并且跑得明白”。光看着SQL文件不动手永远只能停留在眼熟的阶段。我把四个项目按顺序串成一条复现路径每一步都给出命令、参数含义和常见的调整方式。3.1 用命令行导入bookstore例题库完整命令与参数拆解bookstore是最简单的库适合用来跑通整个导入流程。我习惯分两步走先建库再导数据# 创建数据库指定字符集和排序规则 mysql -u root -p -e CREATE DATABASE IF NOT EXISTS bookstore DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; # 导入SQL文件到bookstore库 mysql -u root -p bookstore bookstore_书中例题源代码.sql这里的参数都是日常用得最多的-u指定登录用户-p让命令提示输入密码-e用于执行单条SQL而不进入交互界面重定向符把整个SQL文件的内容喂给mysql客户端。DEFAULT CHARACTER SET和COLLATE把字符集和排序规则一起定死比只写字符集更严谨。Windows的PowerShell对重定向支持偶尔会出问题我一般切到cmd执行或者进到MySQL交互界面用source命令导入mysql -u root -p source D:/sql/bookstore_书中例题源代码.sqlsource后面的路径用正斜杠Windows下反斜杠容易触发转义问题。导入完成后验证一下USE bookstore; SHOW TABLES; SELECT COUNT(*) FROM t_book;SHOW TABLES能看到建了几张表COUNT(*)能确认数据有没有进去。如果表数量比书上章节少说明中间有语句报错被跳过了回头翻导入日志比对着猜效率高得多。3.2 petstore案例库联表查询与事务的练功房petstore是宠物商店场景涉及用户、商品、订单、订单明细、库存。这个库跑起来之后先别急着看数据我一般做三件事数清每张表的行数确认真实数据量找到订单表和订单明细表的关联字段执行一条联表查询验证关系。以常见的订单场景为例具体表名以脚本为准petstore里通常会有订单与订单明细相关的表-- 查询未支付订单的买家、金额按订单号倒序取前10条 SELECT o.order_id, u.username, o.total_amount FROM orders o JOIN users u ON o.user_id u.user_id WHERE o.status unpaid ORDER BY o.order_id DESC LIMIT 10;JOIN是内连接只返回两表都匹配的行WHERE o.status unpaid筛选未支付订单ORDER BY加DESC按订单号倒序LIMIT 10限制返回条数。这套组合是MySQL里最常写的联表模板面试时也会反复出现。接下来是事务。下单这个动作涉及扣库存和生成订单记录必须保证原子性START TRANSACTION; UPDATE products SET stock stock - 1 WHERE product_id 1; INSERT INTO order_items (order_id, product_id, quantity) VALUES (1001, 1, 1); COMMIT;START TRANSACTION开启事务两条写操作执行完毕后COMMIT提交。如果UPDATE成功但INSERT失败整个事务回滚库存不会被扣。这就是MySQL里事务存在的意义也是面试题里“什么场景必须用事务”的标准答案。可以自己把COMMIT改成ROLLBACK跑一遍观察数据变化这个实验比背概念管用。3.3 librarydb综合实训库存储过程和视图跟着抄就能学会打开librarydb脚本你会发现前面两个库里少有的东西视图、存储过程、触发器。这部分是典型的“照着抄就能学会”的内容但抄之前得知道每个语法是干嘛的。先看视图定义-- 创建借阅信息视图把三张表的联表结果固化成虚拟表 CREATE VIEW v_borrow_info AS SELECT r.reader_name, b.book_name, br.borrow_date FROM borrow br JOIN readers r ON br.reader_id r.reader_id JOIN books b ON br.book_id b.book_id;视图的本质是虚拟表把一段联表查询结果固化下来。查询的时候直接SELECT * FROM v_borrow_info不用每次都写JOIN。接下来是存储过程借书这个动作涉及扣库存和插入借阅记录适合封装成一个过程DELIMITER $$ CREATE PROCEDURE sp_borrow_book(IN p_reader_id INT, IN p_book_id INT) BEGIN UPDATE books SET stock stock - 1 WHERE book_id p_book_id; INSERT INTO borrow(reader_id, book_id, borrow_date) VALUES (p_reader_id, p_book_id, CURDATE()); END$$ DELIMITER ;DELIMITER $$这条特殊语句的作用是临时把分隔符改成$$因为存储过程体内部有分号不改分隔符的话mysql会在第一个分号处截断导致语法错误。IN p_reader_id INT定义入参CALL sp_borrow_book(1, 10)就能调用它完成一次借书操作。存储过程的语法在不同版本里有细微差别MySQL 8.0支持完好5.7也支持。如果报错先确认版本再用兼容写法。stored procedure这个词值得熟悉它既是项目里常用的封装手段也是面试题里出现频率最高的知识点。4. SchoolDB实战库的完整推演从建库到数据初始化的项目节奏SchoolDB是四个文件里最接近真实项目的一个。我把它当作一个完整项目来推演从需求分析到表结构落地再到数据初始化和性能优化这样才能看出书里的实战项目到底想教什么。4.1 学校教务系统的表结构从需求到ER模型的落地SchoolDB里通常会涉及学生、课程、教师、选课、成绩这几类表。看建表语句时重点看主外键关系怎么设计-- 学生表学号为主键入学年份用YEAR类型节省存储 CREATE TABLE t_student ( student_id INT PRIMARY KEY, student_name VARCHAR(50) NOT NULL, gender CHAR(1) DEFAULT M, enroll_year YEAR ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;enroll_year用YEAR类型而不是DATE因为只需要存年份YEAR占用1个字节比DATE的3个字节省空间。选课表是整个教务系统的关键它同时关联学生和课程解决多对多关系-- 选课表复合主键两个外键分别引用学生表和课程表 CREATE TABLE t_course_selection ( student_id INT NOT NULL, course_id INT NOT NULL, score DECIMAL(5,2), PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES t_student(student_id), FOREIGN KEY (course_id) REFERENCES t_course(course_id) );复合主键保证同一个学生同一门课只能有一条记录两个FOREIGN KEY保证引用完整性。这段建表逻辑在很多数据库课程设计里都能直接套用尤其是桥表的设计思路。4.2 数据初始化与迁移INSERT脚本的执行策略书里的SQL文件除了建表还有大量INSERT语句。我一般分三批执行先插基础数据包括学生、教师、课程再插关联数据包括选课、授课最后插业务流水比如成绩、考勤。这么分的原因很简单外键约束会让乱序插入直接报错——比如先插入选课记录但学生表里还没有这个学号FOREIGN KEY约束会立刻拦住你。如果你想把这份数据迁移到另一台机器的数据库我的经验是别用数据库同步软件直接拖。跨库导数据最怕表结构有细微差异字段类型对不上就中断还不好排查。更稳的做法是导出脚本再重放# 导出整个SchoolDB库为SQL脚本 mysqldump -u root -p SchoolDB school_backup.sqlmysqldump是MySQL自带的逻辑备份工具导出的文件就是一组SQL语句在目标机器上执行source就能恢复。注意备份时要确认字符集参数否则导出的脚本可能把中文转成乱码。4.3 性能优化索引、EXPLAIN与慢查询日志实战项目的数据量一大全表扫描的问题就暴露了。先看执行计划-- 查看这条联表查询的执行计划重点看type列 EXPLAIN SELECT s.student_name, c.course_name FROM t_course_selection cs JOIN t_student s ON cs.student_id s.student_id JOIN t_course c ON cs.course_id c.course_id WHERE s.enroll_year 2022;EXPLAIN输出里的type列是关键。如果出现ALL说明是全表扫描数据量上来就慢如果出现ref或index说明索引生效。发现全表扫描后在WHERE条件用到的列上建索引-- 给入学年份加索引加速按年份过滤的查询 CREATE INDEX idx_student_enroll_year ON t_student(enroll_year);还有更全局的排查手段——慢查询日志。开启后执行时间超过阈值的SQL会被记录下来mysql -u root -p -e SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;slow_query_log开启慢查询日志long_query_time设置阈值为1秒。日志文件位置用SHOW VARIABLES LIKE slow_query_log_file查看。这套组合拳在真实项目里排查性能问题时同样适用先定位慢SQL再EXPLAIN分析最后补索引。5. 复现这套源码的避坑指南五条常见问题的排查记录这一章写的是我在复现这类教程源码时踩过、或者看别人踩过的坑。每一条都是真实会遇到的按“现象、原因、解决”三个步骤记录。5.1 现象导入时报ERROR 1064语法错误脚本停在第100行原因多半是MySQL版本太老。这份教程基于较新的MySQL编写脚本里可能用了窗口函数或WITH语法这些在MySQL 5.6及以下不支持。另一个常见原因是SQL文件被BOM污染文件开头多了一个不可见字符导致第一条语句就报1064。解决时先执行SELECT VERSION()确认版本建议直接使用8.0以上的版本。如果是BOM问题用VS Code打开文件另存为UTF-8 without BOM重新导入。5.2 现象中文数据全部变成问号或乱码原因有两个方向。一是SQL文件编码不对Windows记事本另存为ANSI后脚本里的中文字符被转成GBK导入到utf8mb4的库里自然乱码。二是客户端连接和数据库三方编码不一致。解决方式是把SQL文件统一转成UTF-8导入前在mysql命令行里执行SET NAMES utf8mb4;保证客户端、连接、数据库三方编码一致。如果已经导入了直接DROP掉对应库重新导入别想着UPDATE修数据得不偿失。5.3 现象导入到一半报ERROR 1451外键约束失败原因是导入顺序错了。比如选课表先导入但学生表里还没有对应的学号记录外键约束就拒绝了这条插入。解决方法是严格按照主表在前、从表在后的顺序导入。如果分不清谁主谁从就在脚本里搜索FOREIGN KEY被引用的表先导。已经失败的情况删掉当前库按正确顺序从头跑一遍。5.4 现象mysql命令行导入大文件时报lost connection原因通常是max_allowed_packet设置太小单个SQL语句包超过限制被服务端拒绝也有可能是执行时间超过默认的timeout。解决方法是导入时显式调大这两个参数# 调大单次数据包上限和网络缓冲再执行导入 mysql -u root -p --max_allowed_packet128M --net_buffer_length8192 bookstore bookstore_书中例题源代码.sql--max_allowed_packet128M允许更大的数据包--net_buffer_length8192设置网络缓冲初始大小。这个方法在处理几百MB的初始化脚本时特别有用。5.5 现象MySQL 8.0连接时报Authentication plugin caching_sha2_password错误原因是MySQL 8.0默认认证插件改成了caching_sha2_password而老版本的客户端或图形工具不认识这个插件。解决方式两条路升级客户端到支持8.0的版本或者把用户认证插件改回mysql_native_password-- 把root用户的认证插件切换为兼容旧客户端的模式 ALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY 你的密码;注意这个改法只针对MySQL 8.x5.7及以下版本没有这个问题。每次跑完一个SQL文件用SHOW TABLES;核对一下表数量和书上章节对照。少表说明中途有语句报错被跳过了回头翻日志定位别硬往下走。6. 把这套源码改造成自己的毕设项目三步替换法加上一条验证路径四个项目跑通之后这套源码最大的剩余价值是当毕设或课程设计的底子。我用它改过一个二手书交易平台核心逻辑就三步。第一步换表名换字段。把bookstore里t_book改成t_second_hand_book增加seller_id和status两个字段。用编辑器做全局替换时记得把注释里的旧表名也检查一遍用SQL关键字当表名的尤其要小心比如order是MySQL里的保留字改表名时要写成t_order语句里也要用反引号包裹。跑完一遍SHOW TABLES确认没有旧表遗留。第二步把借书存储过程改成下单存储过程。这一步是答辩时能讲出东西的关键面试官大概率会问“你写过存储过程吗”。改造时把原脚本的入参换成买家、商品、价格-- 创建下单存储过程扣减图书状态并生成订单 DELIMITER $$ CREATE PROCEDURE sp_create_order( IN p_buyer_id INT, IN p_book_id INT, IN p_price DECIMAL(10,2) ) BEGIN UPDATE t_second_hand_book SET status sold WHERE book_id p_book_id; INSERT INTO t_order(buyer_id, book_id, price, create_time) VALUES (p_buyer_id, p_book_id, p_price, NOW()); END$$ DELIMITER ;调用它模拟下单再查一次t_order和t_second_hand_book验证业务闭环。注意status字段在更新前最好加一道校验防止对已售图书重复下单比如UPDATE ... WHERE book_id p_book_id AND status on_sale然后判断ROW_COUNT()来决定要不要插入订单。第三步把视图改造成报表接口。原来的借阅信息视图改成订单汇总视图前端页面直接查视图就行。如果项目要接Spring Boot连接池参数别照抄网上教程HikariCP的maximumPoolSize建议设置为数据库max_connections的50%~60%压测之后再微调。改造完成后用mysqldump导出一份干净的初始化脚本在另一台开发机或新环境重新导入验证一次确保队友clone代码后执行一个source命令就能把环境拉起来。说一个我自己的教训以前做毕设时直接拿书里的库原样用表名一个没改答辩时老师一眼看出是模板项目。从那以后我每次改造数据库脚本都强制先做三步换掉业务表名、删掉用不到的功能表、加一个自己设计的核心字段。希望帮到你。本文还有配套的精品资源点击获取