MySQL 5.7增删改查实战：从建库到查询的SQL入门指南

1. 环境准备先把MySQL 5.7跑起来如果你正在看这篇文章大概率是遇到了这么几件事中的一件课程设计要用数据库建一个系统公司老项目用的MySQL 5.7需要你上手维护或者单纯想系统学一下数据库最基础的操作。不管是哪种数据库、表、记录这三层结构的增删改查就是你绕不开的第一道坎。先说版本选择。虽然现在MySQL 8.0已经发布很久了但5.7依然在大量生产环境中服役很多高校的数据库课程设计指定环境就是5.7一些老系统的部署文档写的也是5.7。而且5.7的语法和8.0的常用增删改查语句基本一致学会了5.7切到8.0无非是注意几个默认认证插件、窗口函数之类的新特性基础操作完全够用。1.1 安装与登录别在第一步翻车安装这块Windows用户直接去官网下载MySQL Installer选5.7版本一路Next即可。需要注意的一点是安装过程中会让你设置root密码这时候顺手选上“Add user to Windows Firewall Exception”免得后面连接的时候被防火墙拦。Linux用户如果是Ubuntu或Debian系用apt安装的话需要先配置MySQL官方APT源因为默认源里的mysql-server可能不是5.7。CentOS 7用户可以直接用yum安装不过也建议先装官方Yum Repository。装好之后验证是否安装成功mysql -u root -p输入密码后看到mysql提示符说明已经进入MySQL命令行客户端。这时候可以先看一下版本号确认没有连到别的实例SELECT VERSION();1.2 客户端工具命令行和图形界面怎么选很多新手一上来就问用Navicat还是DataGrip我的建议是刚开始学习阶段建议先用命令行把增删改查全部敲一遍对SQL语句的执行过程和返回结果建立直观感受。等熟悉了之后再用图形化工具提高效率。命令行方式的好处是任何环境都有服务器上没有图形界面也一样操作。图形化工具在写复杂查询、查看表结构、导出数据时效率更高。Navicat连接MySQL 5.7时如果提示“Client does not support authentication protocol requested by server”是因为5.7默认使用了caching_sha2_password认证插件如果你安装时选了8.0的默认选项需要执行以下命令改成mysql_native_passwordALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY 你的密码; FLUSH PRIVILEGES;这是5.7和8.0之间最常见的兼容性问题提前知道能省不少排查时间。2. 数据库级别的增删改查建库、看库、改库、删库数据库是MySQL最大的容器层级所有的表都存放在数据库里面。这个层面的操作虽然不多但每一条都有讲究尤其是字符集配置后面表的操作、记录的操作都跟它息息相关。2.1 创建数据库字符集和排序规则必须一次配好创建数据库的完整语法是CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT] CHARACTER SET [] 字符集名 [DEFAULT] COLLATE [] 排序规则名;方括号里的内容可以省略但我不建议省略。直接执行CREATE DATABASE student;虽然也能建库成功但会用MySQL的默认配置。在5.7中默认字符集是latin1如果你建库的时候没设字符集后面往表里插入中文时就会出现乱码这是新手最容易踩的坑。正确的建库方式CREATE DATABASE IF NOT EXISTS student DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;为什么要用utf8mb4而不是utf8这是5.7时代非常经典的坑。MySQL的utf8字符集实际只能存3个字节的字符像emoji表情这种4字节字符存不进去会报“Incorrect string value”错误。而utf8mb4是完整的UTF-8实现完全兼容utf8又能存4字节字符。所以现在的主流实践一律用utf8mb4没有任何理由用回utf8。排序规则方面utf8mb4_general_ci是大小写不敏感的通用排序规则ci是case insensitive的缩写适合绝大多数场景。如果你需要严格的Unicode排序规则可以选utf8mb4_unicode_ci它对某些特殊字符的排序更符合Unicode标准但性能上略逊于general_ci。日常学习和一般项目用general_ci就够了。IF NOT EXISTS这个修饰符的作用是如果要创建的数据库已存在MySQL不会报错而是产生一个警告。这在写自动化脚本时特别有用脚本重复执行不会中断。2.2 查看与修改数据库确认配置和改默认选项查看当前服务器上有哪些数据库SHOW DATABASES;查看某个数据库的创建信息和字符集配置SHOW CREATE DATABASE student;这个命令不仅能看到建库语句还能确认库的字符集和排序规则是否是你设置的值。想查看当前正在使用的是哪个库SELECT DATABASE();如果要切换数据库USE student;修改数据库的字符集或排序规则ALTER DATABASE student DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;这里需要说明的是修改数据库级别的字符集只对以后新建的表和字段生效已经存在的表和字段字符集不会被自动修改。所以如果是老库要改字符集还得逐个表、逐个字段去改。2.3 删除数据库手一抖就什么都没了DROP DATABASE student;这条命令会直接删除整个数据库、库里的所有表、表里的所有数据而且MySQL不会弹出任何确认框。执行前务必再三确认数据库名字没有敲错最好先执行SHOW DATABASES;看清楚有哪些库。更稳妥的做法是先把库名改成备份名字观察几天确认没影响再真正删除RENAME DATABASE student TO student_bak_20250101;注意RENAME DATABASE语句在5.7中已经被移除了实测不可用。更通用的做法是在图形化工具中右键重命名或者用mysqldump先逻辑备份再删。没有备份习惯的话DROP之前先做一次mysqldump备份这是底线。删除数据库还有一个DROP DATABASE IF EXISTS student;写法适合脚本场景。3. 表级别的增删改查结构设计决定上层一切表是数据库的核心存储单元表结构设计得合理不合理直接决定后续记录的增删改查好不好写、性能高不高。这一层的操作也是最多最杂的涉及建表、查看表结构、修改列定义、修改约束、重命名、删表等。3.1 创建表字段类型和约束要一起来建表的基本语法CREATE TABLE 表名 ( 列名1 数据类型 [约束条件] [默认值] [注释], 列名2 数据类型 [约束条件] [默认值] [注释], ... [表级约束条件] ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;以一个课程设计里最常见的“学生表”为例CREATE TABLE student ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, student_no VARCHAR(20) NOT NULL COMMENT 学号, name VARCHAR(50) NOT NULL COMMENT 姓名, gender ENUM(男,女) DEFAULT 男 COMMENT 性别, age TINYINT UNSIGNED DEFAULT 0 COMMENT 年龄, phone VARCHAR(20) DEFAULT NULL COMMENT 手机号, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, 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), KEY idx_name (name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生信息表;这个表结构里几乎包含了所有最常用的建表要素拆开来说INT UNSIGNED表示非负整数主键用这个可以避免负数主键的歧义也扩大了正数取值范围。AUTO_INCREMENT是自增列不用手动赋值MySQL会自动分配1、2、3……但要注意如果你删除了某行记录它的自增值不会回填复用。ENUM枚举类型适合性别这种取值固定的字段。它底层用数字存储比字符串省空间但修改枚举值需要通过ALTER TABLE如果枚举值可能经常变化用VARCHAR更灵活。COMMENT是注释在团队协作中非常有用。不写注释的建表语句三个月后你自己都未必记得每个字段是干嘛的。DEFAULT CURRENT_TIMESTAMP是MySQL 5.6.5之后才引入的特性之前的版本DATETIME类型不能自动填充当前时间只能通过程序传入。5.7完全支持放心用。ON UPDATE CURRENT_TIMESTAMP实现“每次更新记录时自动更新时间戳”这是做数据审计的常用手段不需要你在UPDATE语句里手动维护updated_at字段。三个索引主键索引、唯一索引学号不能重复、普通索引按姓名查询优化。数据类型的选择是建表中最重要的决策之一。VARCHAR和CHAR的区别经常被问到VARCHAR(20)是变长字符串实际存多少就占用多少空间外加1-2字节的长度信息CHAR(20)是定长字符串永远占20个字符的空间。短字符串、长度固定的场景如手机号、身份证号可以用CHAR但绝大多数场景VARCHAR更省空间。数值型字段能用TINYINT0-255就不用INT能用INT就不用BIGINT。年龄用TINYINT足够存储一个学生的年龄没必要用BIGINT占8个字节。3.2 查看表结构写查询前先确认字段名查看当前库有哪些表SHOW TABLES;查看表结构DESC student;或用更详细的方式SHOW CREATE TABLE student;DESC展示的是字段名、类型、是否为空、键类型、默认值、额外属性适合快速浏览。SHOW CREATE TABLE展示的是完整的建表语句包含索引、约束、注释、引擎、字符集等所有细节。遇到线上表要写脚本改结构时先执行这个命令复制一份建表语句出来改比手写整条语句安全得多。3.3 修改表结构ALTER TABLE的各种姿势在实际开发中几乎不可能一次把表结构设计完美。后面加字段、改类型、删字段、重命名是家常便饭。添加新字段ALTER TABLE student ADD COLUMN address VARCHAR(200) DEFAULT NULL COMMENT 家庭地址;添加字段时可以指定位置ALTER TABLE student ADD COLUMN nickname VARCHAR(50) DEFAULT NULL COMMENT 昵称 AFTER name;AFTER name表示把新字段放在name字段后面。默认情况下新字段会加在表最后如果要放在最前面可以用FIRST。修改字段类型ALTER TABLE student MODIFY COLUMN phone VARCHAR(20) NOT NULL DEFAULT COMMENT 手机号;MODIFY COLUMN会完全替换字段定义所以写的时候要把原来的类型、约束、默认值全部再写一遍否则没写到的属性可能会丢。修改字段名字和类型ALTER TABLE student CHANGE COLUMN phone mobile VARCHAR(20) DEFAULT NULL COMMENT 手机号;CHANGE COLUMN和MODIFY COLUMN的区别就是前者可以同时改字段名后者改不了名字。实际上CHANGE COLUMN功能上覆盖了MODIFY COLUMN但日常使用中如果不需要改名字用MODIFY写起来更简洁。删除字段ALTER TABLE student DROP COLUMN nickname;重命名表ALTER TABLE student RENAME TO student_info;也可以用RENAME TABLE student TO student_info;经验之谈ALTER TABLE在数据量大的表上执行时会锁表5.7之前的版本尤其严重。如果生产环境有上千万条记录的表要加字段直接用ALTER TABLE会长时间阻塞读写务必在业务低峰期操作或者使用pt-online-schema-change这种在线改表工具。3.4 删除表DROP TABLE比DROP DATABASE好一点但也没好到哪去DROP TABLE student;同样支持一次删多表DROP TABLE IF EXISTS student, teacher, course;DROP TABLE删除的是整张表数据和结构都没了不同于TRUNCATE TABLE清空数据保留结构和DELETE FROM按条件删除保留结构。这三者区别后面会详细讲。4. 记录级别的增删改查日常打交道最多的部分记录级的CRUD是整个MySQL操作中最频繁、最核心的部分具体就是INSERT插入、SELECT查询、UPDATE修改、DELETE删除。这一层的操作灵活度最高同样的需求可以写出效率天差地别的SQL。4.1 INSERT语句单条插入与批量插入最基础的插入语法INSERT INTO student (student_no, name, gender, age, phone, email) VALUES (20250101001, 张三, 男, 20, 13812345678, zhangsanexample.com);几点说明自增主键id不需要写MySQL会自动生成。字段列表可以省略直接INSERT INTO student VALUES (...)但这样要求VALUES里的值必须按表结构的字段顺序一一对应字段一多容易错位。强烈建议手写字段列表可读性强以后表加了新字段也不影响。created_at和updated_at因为设置了DEFAULT CURRENT_TIMESTAMP所以不用手动传值。一条INSERT插入多条记录INSERT INTO student (student_no, name, gender, age, phone) VALUES (20250101002, 李四, 女, 19, 13912345678), (20250101003, 王五, 男, 21, 13712345678), (20250101004, 赵六, 女, 20, 13612345678);批量插入是性能优化的重要手段。如果你是逐条执行三条INSERTMySQL需要三次网络往返、三次日志写入用一条多VALUES的INSERT只需要一次网络往返整体速度可能快好几倍。在执行课程设计的数据初始化脚本时能用批量插入尽量批量。还有一种用法是INSERT ... SET语法INSERT INTO student SET student_no20250101005, name孙七, gender男, age22;这个写法更接近UPDATE语句的风格直观且易读但只支持单条插入。一条一条插的时候用这个格式可读性高。4.2 SELECT查询全表查询、条件过滤、排序分页查询是增删改查里最烧脑的部分基本功先打好。全表查询SELECT * FROM student;按条件查询SELECT * FROM student WHERE gender 男;指定字段查询SELECT student_no, name, age FROM student WHERE age 20;条件组合SELECT * FROM student WHERE gender 男 AND age BETWEEN 18 AND 25 OR phone IS NOT NULL;BETWEEN ... AND ...是闭区间查询等价于age 18 AND age 25语义更清晰。IS NOT NULL不能写成! NULL因为NULL表示“未知”任何与NULL的比较运算结果都是“未知”这是数据库里的经典易错点。排序SELECT * FROM student ORDER BY age DESC, student_no ASC;DESC降序ASC升序默认。多字段排序时先按第一个字段排如果第一个字段值相同再按第二个字段排。分页查询SELECT * FROM student ORDER BY id LIMIT 10 OFFSET 20;它的含义是按id升序排列后跳过前20条从第21条开始取10条也就是第三页的数据。LIMIT后面跟的是返回的最大行数OFFSET后面跟的是跳过的行数。另一种写法是LIMIT 20, 10第一个参数是偏移量第二个参数是行数含义正好和LIMIT 10 OFFSET 20反过来。建议只用一种写法避免混用搞错。聚合查询SELECT gender, COUNT(*) AS cnt, AVG(age) AS avg_age FROM student GROUP BY gender;这条SQL按性别分组统计每组人数和平均年龄。AS是别名让结果集的列名更好读。4.3 UPDATE修改记录最容易出问题的语句基础语法UPDATE student SET phone 13512345678 WHERE student_no 20250101001;这段SQL把学号对应学生的手机号改成新值。WHERE条件的地位再怎么强调都不为过警告如果UPDATE语句不带WHERE条件会更新表中的全部记录。这不是开玩笑新手线上操作第一事故就是这条。某些课程设计里测试环境无所谓但在生产环境执行不带WHERE的UPDATE等于灾难。养成好习惯先写WHERE再写SET或者先SELECT查一下WHERE命中了多少条记录确认无误后再执行UPDATE。一次更新多个字段UPDATE student SET phone 13512345678, email zhangsan_newexample.com WHERE student_no 20250101001;UPDATE的SET后面的多个字段用逗号分隔这个不难但注意不要写成AND分隔那是语法错误。注意MyISAM引擎和InnoDB引擎在执行UPDATE时行为有个差异MyISAM是表级锁UPDATE期间整张表都不可写InnoDB是行级锁UPDATE只锁命中行。如果你在建表时没写ENGINEInnoDBMySQL 5.7的默认引擎就是InnoDB问题不大。但如果是MyISAM老表高并发UPDATE场景下容易锁表堆积所以5.7里新表一律用InnoDB是铁律。4.4 DELETE删除记录同样是最容易出事故的语句DELETE FROM student WHERE id 10;和UPDATE一样不带WHERE会清空全表DELETE FROM student;这和执行TRUNCATE TABLE student;效果类似但原理完全不同。DELETE是逐行删除会记录到binlog可以通过事务回滚TRUNCATE是直接重建表无法回滚也不会逐行触发删除相关的触发器。实际开发中DELETE其实越来越被谨慎使用。很多系统采用“软删除”策略就是给表加一个is_deleted字段0表示正常1表示已删除查询时默认过滤掉is_deleted1的记录。这样做的好处是历史数据不丢失方便回溯审计代价是每次查询都要带条件、表数据量会持续增长。小项目用软删除大项目则需要考虑定期归档清理。4.5 一个完整的操作案例从建库到查询收尾把上面的内容串起来模拟一个图书管理系统的部分操作-- 1. 建库 CREATE DATABASE IF NOT EXISTS library DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci; USE library; -- 2. 建表 CREATE TABLE book ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, isbn VARCHAR(20) NOT NULL, title VARCHAR(100) NOT NULL, author VARCHAR(50) NOT NULL, price DECIMAL(8,2) DEFAULT 0.00, stock INT UNSIGNED DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_isbn (isbn) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 3. 插入记录 INSERT INTO book (isbn, title, author, price, stock) VALUES (9787115428028, MySQL必知必会, Ben Forta, 49.00, 10), (9787111213826, 深入浅出MySQL, 翟振兴, 89.00, 5), (9787115272218, 高性能MySQL, Baron Schwartz, 128.00, 3); -- 4. 查询 SELECT * FROM book WHERE price 100 ORDER BY price DESC; -- 5. 修改 UPDATE book SET stock stock 1 WHERE isbn 9787115428028; -- 6. 删除 DELETE FROM book WHERE id 3; -- 7. 查看最终数据 SELECT id, isbn, title, author, price, stock FROM book;这一步一步执行下来数据库、表、记录三个层面的增删改查就都完整走了一遍。实际课程设计或项目开发80%的日常数据库工作就是这些操作。5. 增删改查高频报错与排查技巧操作多了报错是必然的。把常见的报错整理成速查表遇到问题的时候翻一翻能省不少时间。报错信息原因解决办法ERROR 1044 (42000): Access denied for user当前用户没有对应权限用root登录执行GRANT SELECT, INSERT, UPDATE, DELETE ON 库名.* TO 用户名host;ERROR 1146 (42S02): Table doesnt exist表不存在或没选对数据库先SHOW TABLES;确认表名确认执行过USE 库名;ERROR 1054 (42S22): Unknown column字段名写错了执行DESC 表名;确认字段拼写ERROR 1062 (23000): Duplicate entry违反了唯一索引或主键约束检查是否插入了重复的主键或唯一键值ERROR 1064 (42000): You have an error in your SQL syntaxSQL语法错误检查关键词拼写、逗号是否漏写、引号是否闭合ERROR 1366 (HY000): Incorrect string value字符集问题插入了当前字符集不支持的内容统一表和字段字符集为utf8mb4检查连接字符集是否一致ERROR 1175 (HY000): Safe update mode开启了安全更新模式UPDATE/DELETE不带WHERE被拦截确认条件无误后可以SET SQL_SAFE_UPDATES0;但建议保持开启ERROR 2002 (HY000): Cant connect to local MySQL serverMySQL服务没启动或socket文件路径不对启动MySQL服务检查/tmp/mysql.sock是否存在5.1 中文乱码问题怎么彻底解决写代码时发现插入的中文变成“???”真正常见的根源不是建表建库时字符集不对而是客户端连接字符集与服务端不一致。MySQL 5.7的三层字符集衔接是客户端 → 连接层 → 服务端。排查方法SHOW VARIABLES LIKE character_set%;看到character_set_client、character_set_connection、character_set_results这些变量把它们统一设置成utf8mb4SET NAMES utf8mb4;但注意SET NAMES只对当前会话有效退出重新登录后恢复默认。持久化的办法是在配置文件my.cnf的[mysqld]段添加[mysqld] character-set-server utf8mb4 collation-server utf8mb4_general_ci [client] default-character-set utf8mb4改完重启MySQL服务。之后新建的数据库、表默认就都是utf8mb4了。5.2 忘记WHERE条件修改了全表数据怎么救这是所有MySQL操作里最让人心跳停止的错误之一。如果是误执行了UPDATE student SET phonexxx不带WHERE全表的电话号码都被改了立刻执行-- 查看binlog是否开启 SHOW VARIABLES LIKE log_bin;如果log_bin是ON且binlog_format是ROW可以通过mysqlbinlog工具解析binlog找到误操作位置前后的记录反向恢复。如果log_bin是OFF而且事务已经提交那就基本只能靠备份恢复了。所以预防手段比事后恢复重要得多UPDATE和DELETE语句先带WHERE写再回头补SET或再执行。操作前先SELECT COUNT(*)确认本次影响的行数。生产环境开启binlog。关键表每天做备份。5.3 自增主键不连续心里别慌不少同学执行了DELETE删除某些记录后再插入新数据发现id跳过了已删除的编号。比如表里id最大是5删掉了4和5再插入时新记录id是6不是4。这是InnoDB引擎的正常行为。MySQL 5.7里自增计数器的计数规则是每次插入时取当前最大值加1不会因为删除了最大ID就回退复用。这样做的根本原因是并发安全的考虑——如果允许回退复用在高并发插入时可能出现两个会话分配同一个ID造成主键冲突。如果真的需要紧凑的连续编号只有两种办法手动指定ID插入但容易和自增冲突TRUNCATE TABLE清空表后自增从1重新开始。日常使用完全不必纠结ID空档学生证号、订单号都不会因为ID跳号就出问题。5.4 表锁了怎么办MySQL锁表现在在业务中依然常见尤其是InnoDB发生死锁或者长事务持锁不释放时其他会话的增删改查语句会一直卡住。查看当前锁和事务状态的命令-- 查看当前有哪些锁等待 SELECT * FROM information_schema.innodb_lock_waits; -- 查看当前运行的事务 SELECT * FROM information_schema.innodb_trx;找到长时间运行的事务后用它的trx_mysql_thread_id去杀掉对应线程KILL 线程ID;还有一个经常忽略的点如果在一个事务里执行了UPDATE既没有COMMIT也没有ROLLBACK就放着不管那个事务会一直持有行锁其他对该行的操作全部阻塞。排查慢SQL时除了看SQL本身也务必检查是否有“悬挂事务”。6. 实操心得从课程设计到生产环境这些习惯越早养越好写了这么多年SQL最深的体会是数据库操作的难点从来不是语法记不记得住而是操作习惯和数据安全意识。第一任何一条UPDATE或DELETE都先写WHERE再写SET或DELETE FROM。我在刚接触MySQL的时候也吃过一次亏本意是更新一条记录结果把整张测试表的几百条记录全改了。从那之后我的习惯是先在前面写SELECT * FROM ... WHERE ...看命中范围确认无误再把它改写成UPDATE或DELETE。第二建表时一定要写注释和字符集。COMMENT字段看起来不起眼但到了后期维护、接口对接的时候注释就是最基础的数据字典。字符集更不用说了表建好了再改字符集不光要改表还要改每个字段工作量和风险都会成倍增加。第三DROP和TRUNCATE操作前先备份。备份有两种方式逻辑备份用mysqldump物理备份直接复制数据目录初学者掌握mysqldump这一种就够了mysqldump -u root -p student /tmp/student_backup.sql还原时mysql -u root -p student /tmp/student_backup.sql第四学习阶段建议手写SQL不要依赖图形化工具自动生成。Navicat确实有查询构建器拖拖拽拽能生成SELECT语句但那样你永远不会真正理解表连接、子查询这些底层逻辑。等SQL基础扎实了再回到图形工具里提高效率。最后再分享一个小细节写SQL语句时关键词统一用大写SELECT、INSERT、UPDATE表名字段名用小写。虽然MySQL不区分大小写但团队规范里统一风格之后代码评审和阅读体验会好很多。数据库这条路没有捷径就是把增删改查这些最基础的操作练成肌肉记忆后面学索引优化、事务隔离、主从复制时才不会卡在基础上。