MySQL 基础讲解（二）：数据定义与数据操作核心

1. 引言在上一篇文章中我们介绍了 MySQL 的基本概念、安装配置以及常用客户端工具的使用。本篇将深入 MySQL 的核心操作——数据定义语言DDL与数据操作语言DML带你掌握建库、建表、增删改查等最常用的技能。无论你是刚接触数据库的新手还是希望系统梳理基础知识的开发者本文都会用清晰的示例和通俗的解释帮你快速上手 MySQL 的数据定义与数据操作。2. 数据定义语言DDL基础数据定义语言Data Definition LanguageDDL用于定义和管理数据库对象主要包括数据库、表、索引、视图等。DDL 的核心操作可以概括为「增删改查」四个字创建CREATE、删除DROP、修改ALTER和查看SHOW / DESCRIBE。2.1 数据库的创建与删除创建数据库使用CREATE DATABASE语句可以同时指定字符集和排序规则-- 创建数据库指定 utf8mb4 字符集CREATEDATABASEIFNOTEXISTSmydbDEFAULTCHARACTERSETutf8mb4DEFAULTCOLLATEutf8mb4_general_ci;删除数据库使用DROP DATABASE操作前请务必确认因为该操作不可恢复DROPDATABASEIFEXISTSmydb;查看当前实例下有哪些数据库SHOWDATABASES;2.2 表的创建表是数据库中存储数据的基本单位。创建表时需要定义字段名、数据类型以及约束条件CREATETABLEIFNOTEXISTSstudent(idINTUNSIGNEDAUTO_INCREMENTCOMMENT主键ID,nameVARCHAR(50)NOTNULLCOMMENT姓名,ageTINYINTUNSIGNEDDEFAULT0COMMENT年龄,emailVARCHAR(100)UNIQUECOMMENT邮箱,created_atDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT创建时间,PRIMARYKEY(id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT学生表;常用数据类型速查数据类型说明典型用途INT / BIGINT整数主键、计数VARCHAR(n)变长字符串姓名、地址CHAR(n)定长字符串固定长度编码DECIMAL(p,s)精确小数金额DATE / DATETIME日期 / 日期时间生日、创建时间TEXT长文本文章内容BOOLEAN / TINYINT(1)布尔状态标记2.3 修改表结构使用ALTER TABLE可以动态调整表结构常见操作如下-- 新增字段ALTERTABLEstudentADDCOLUMNphoneVARCHAR(20)COMMENT电话;-- 修改字段类型ALTERTABLEstudentMODIFYCOLUMNageSMALLINTUNSIGNEDDEFAULT0COMMENT年龄;-- 重命名字段ALTERTABLEstudent CHANGECOLUMNemail contact_emailVARCHAR(100)COMMENT联系邮箱;-- 删除字段ALTERTABLEstudentDROPCOLUMNphone;2.4 删除表DROPTABLEIFEXISTSstudent;注意DROP TABLE会连同表结构和数据一起删除执行前务必做好备份。3. 数据操作语言DML核心数据操作语言Data Manipulation LanguageDML用于对表中的数据进行增、删、改、查是日常开发中使用最频繁的 SQL 语句。3.1 插入数据INSERT单行插入INSERTINTOstudent(name,age,email)VALUES(张三,20,zhangsanexample.com);多行批量插入INSERTINTOstudent(name,age,email)VALUES(李四,22,lisiexample.com),(王五,21,wangwuexample.com),(赵六,23,zhaoliuexample.com);插入时也可以省略字段列表但必须按表定义的字段顺序提供全部值不推荐易出错INSERTINTOstudentVALUES(NULL,孙七,24,sunqiexample.com,NOW());3.2 查询数据SELECT查询是 DML 中使用频率最高的操作。基础查询语法-- 查询所有字段SELECT*FROMstudent;-- 查询指定字段SELECTid,name,ageFROMstudent;-- 带条件查询SELECTid,name,ageFROMstudentWHEREage20;-- 排序SELECTid,name,ageFROMstudentORDERBYageDESC;-- 分页SELECTid,name,ageFROMstudentORDERBYidLIMIT10OFFSET0;常用条件与运算符运算符说明示例 / !等于 / 不等于WHERE age 20 / / / 比较WHERE age 20BETWEEN … AND …区间WHERE age BETWEEN 20 AND 25IN (…)在集合中WHERE name IN (张三,李四)LIKE模糊匹配WHERE name LIKE 张%AND / OR逻辑与 / 或WHERE age 20 AND name LIKE 张%IS NULL / IS NOT NULL空值判断WHERE email IS NULL3.3 更新数据UPDATE更新数据时务必带上WHERE条件否则会更新全表数据-- 更新单条记录UPDATEstudentSETage21WHEREid1;-- 更新多条记录UPDATEstudentSETageage1WHEREage20;-- 更新多个字段UPDATEstudentSETage22,emailnewexample.comWHEREid2;安全提示生产环境中执行UPDATE前建议先用相同条件的SELECT确认影响范围。3.4 删除数据DELETE删除数据同样需要谨慎使用WHERE-- 删除指定记录DELETEFROMstudentWHEREid3;-- 清空表数据保留表结构TRUNCATETABLEstudent;DELETE与TRUNCATE的区别对比项DELETETRUNCATE是否可带 WHERE可以不可以是否记录日志逐行记录不逐行记录速度较慢很快自增 ID 是否重置不重置重置是否可回滚事务内可以不可以4. 事务与 ACID 特性事务Transaction是一组逻辑上不可分割的数据库操作要么全部成功要么全部失败。它保证了多个 DML 操作作为一个整体执行是保证数据一致性的重要机制。4.1 事务的四大特性ACID特性英文说明原子性Atomicity事务内的所有操作要么全部提交成功要么全部回滚不存在「执行了一半」的状态一致性Consistency事务执行前后数据库始终处于合法状态约束主键、外键、唯一等始终被满足隔离性Isolation多个事务并发执行时互不干扰一个事务未提交的修改对其他事务不可见持久性Durability事务一旦提交其修改将永久保存即使系统崩溃也不会丢失4.2 事务的基本操作BEGIN / COMMIT / ROLLBACKMySQL 中默认每条 DML 语句是自动提交的。如果需要把多条操作放进同一个事务需要显式开启事务-- 开启事务BEGIN;-- 执行一组 DML 操作INSERTINTOstudent(name,age,email)VALUES(钱八,25,qianbaexample.com);UPDATEstudentSETageage1WHEREid1;-- 全部成功提交事务COMMIT;如果事务中途出错可以使用ROLLBACK撤销本事务内所有未提交的修改BEGIN;-- 第一条操作成功INSERTINTOstudent(name,age,email)VALUES(孙九,26,sunjiuexample.com);-- 第二条操作失败例如违反唯一约束INSERTINTOstudent(name,age,email)VALUES(周十,27,sunjiuexample.com);-- ERROR 1062: Duplicate entry ...-- 回滚撤销本事务内的所有修改ROLLBACK;提示COMMIT之后事务结束修改永久生效ROLLBACK之后事务结束修改全部撤销。事务内未提交的修改对其他连接不可见。4.3 DML 操作在事务中的行为INSERT / UPDATE / DELETE都属于 DML都可以在事务内执行并随COMMIT/ROLLBACK一起生效或撤销SELECT默认不加锁读取的是已提交的数据在默认隔离级别下DDL 语句如 CREATE、ALTER、DROP会自动提交当前事务因此不要在事务中间夹杂 DDL否则会导致事务提前结束TRUNCATE属于 DDL无法在事务内回滚这也是它与DELETE的重要区别之一。4. 综合实战示例下面通过一个完整的示例串联本篇文章介绍的核心操作。4.1 建库建表-- 创建数据库CREATEDATABASEIFNOTEXISTSshopDEFAULTCHARSETutf8mb4;USEshop;-- 创建商品表CREATETABLEIFNOTEXISTSproduct(idINTUNSIGNEDAUTO_INCREMENTCOMMENT商品ID,nameVARCHAR(100)NOTNULLCOMMENT商品名称,priceDECIMAL(10,2)NOTNULLCOMMENT价格,stockINTUNSIGNEDDEFAULT0COMMENT库存,statusTINYINTDEFAULT1COMMENT状态1上架 0下架,created_atDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT创建时间,PRIMARYKEY(id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT商品表;4.2 数据操作-- 插入商品INSERTINTOproduct(name,price,stock)VALUES(手机,2999.00,100),(耳机,199.00,500),(充电器,59.00,1000);-- 查询价格大于 100 的商品按价格降序SELECTid,name,price,stockFROMproductWHEREprice100ORDERBYpriceDESC;-- 给所有商品涨价 10%UPDATEproductSETpriceprice*1.1;-- 删除库存为 0 的商品DELETEFROMproductWHEREstock0;4.3 操作流程示意下面是本示例的完整操作流程创建数据库 shop创建商品表 product插入商品数据条件查询商品更新商品价格删除无效商品5. 常见问题与注意事项5.1 忘记 WHERE 条件UPDATE和DELETE不带WHERE会作用于全表这是新手最容易犯的错误。建议养成先SELECT后UPDATE/DELETE的习惯。5.2 字符集不一致导致乱码建库、建表时统一使用utf8mb4连接串中也应指定characterEncodingutf8避免中文乱码。5.3 主键与自增主键建议使用无符号整数自增INT UNSIGNED AUTO_INCREMENT避免业务字段作为主键带来的耦合。5.4 备份意识5.5 常见 SQL 错误与排查新手在编写 SQL 时经常遇到各类报错下面列出 5 个高频错误并给出错误提示、原因分析、解决方案与正确示例。错误 1字段名拼写错误错误提示示例SELECTid,name,ageFROMstudentWHEREag20;ERROR 1054 (42S22): Unknown column ag in where clause原因分析字段名拼写错误ag应为ageMySQL 无法在表中找到该列。解决方案使用DESCRIBE student;或SHOW COLUMNS FROM student;查看表结构确认字段名后再编写 SQL。正确 SQLSELECTid,name,ageFROMstudentWHEREage20;错误 2数据类型不匹配错误提示示例INSERTINTOstudent(name,age,email)VALUES(张三,二十,zhangsanexample.com);ERROR 1366 (HY000): Incorrect integer value: 二十 for column age at row 1原因分析age字段是整数类型却传入了字符串二十MySQL 无法完成类型转换。解决方案严格按字段数据类型传值数字类型传数字日期类型传合法日期格式。正确 SQLINSERTINTOstudent(name,age,email)VALUES(张三,20,zhangsanexample.com);错误 3违反唯一约束错误提示示例INSERTINTOstudent(name,age,email)VALUES(李四,22,zhangsanexample.com);ERROR 1062 (23000): Duplicate entry zhangsanexample.com for key student.email原因分析email字段设置了UNIQUE唯一约束插入的邮箱与已有记录重复。解决方案插入前先查询确认该值是否已存在或使用INSERT IGNORE、ON DUPLICATE KEY UPDATE处理冲突。正确 SQL-- 先检查是否已存在SELECTidFROMstudentWHEREemailzhangsanexample.com;-- 或使用 INSERT IGNORE 忽略重复INSERTIGNOREINTOstudent(name,age,email)VALUES(李四,22,zhangsanexample.com);错误 4外键约束失败错误提示示例INSERTINTOstudent(name,age,dept_id)VALUES(王五,21,999);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails原因分析dept_id引用了dept表的主键但dept表中不存在id 999的部门记录外键校验失败。解决方案先确认被引用表中存在对应记录再插入子表数据或检查外键字段是否允许为NULL。正确 SQL-- 先确认部门存在SELECTidFROMdeptWHEREid999;-- 部门存在后再插入INSERTINTOstudent(name,age,dept_id)VALUES(王五,21,999);错误 5字符集不一致导致乱码错误提示示例INSERTINTOstudent(name)VALUES(张三);ERROR 1366 (HY000): Incorrect string value: \xE5\xBC\xA0\xE4\xB8\x89 for column name原因分析客户端连接字符集与表字符集不一致如连接使用latin1表使用utf8mb4中文写入时被截断或报错。解决方案连接时统一指定字符集建库建表统一使用utf8mb4。正确 SQL-- 连接时指定字符集SETNAMES utf8mb4;-- 建表时指定字符集CREATETABLEIFNOTEXISTSstudent(nameVARCHAR(50)NOTNULLCOMMENT姓名)ENGINEInnoDBDEFAULTCHARSETutf8mb4;执行DROP、TRUNCATE等不可逆操作前务必确认数据已备份。7. 索引与约束基础索引与约束是 MySQL 中提升查询性能、保证数据完整性的两大核心机制。本节将介绍常用索引的创建方式、常见约束的作用以及使用时的注意事项。7.1 索引的创建索引用于加速数据检索可以基于一个或多个字段创建。常用创建方式有两种CREATE INDEX和ALTER TABLE ADD INDEX。普通索引单列-- 使用 CREATE INDEX 创建普通索引CREATEINDEXidx_student_nameONstudent(name);-- 使用 ALTER TABLE 添加普通索引ALTERTABLEstudentADDINDEXidx_student_age(age);唯一索引保证字段值不重复CREATEUNIQUEINDEXidx_student_emailONstudent(email);ALTERTABLEstudentADDUNIQUEINDEXidx_student_email(email);联合索引多个字段组合遵循最左前缀原则CREATEINDEXidx_name_ageONstudent(name,age);ALTERTABLEstudentADDINDEXidx_name_age(name,age);提示联合索引中字段的顺序很重要查询条件应尽量匹配索引的最左前缀才能充分利用索引。7.2 常见约束约束用于保证数据的完整性与一致性可以在建表时定义也可以通过ALTER TABLE添加。约束类型作用示例主键约束PRIMARY KEY唯一标识一行非空且唯一PRIMARY KEY (id)外键约束FOREIGN KEY保证表间引用完整性FOREIGN KEY (dept_id) REFERENCES dept(id)唯一约束UNIQUE保证字段值不重复email VARCHAR(100) UNIQUE非空约束NOT NULL字段不允许为空name VARCHAR(50) NOT NULL默认值约束DEFAULT未提供值时使用默认值age TINYINT DEFAULT 0在建表时定义约束CREATETABLEIFNOTEXISTSstudent(idINTUNSIGNEDAUTO_INCREMENTCOMMENT主键ID,nameVARCHAR(50)NOTNULLCOMMENT姓名,ageTINYINTUNSIGNEDDEFAULT0COMMENT年龄,emailVARCHAR(100)UNIQUECOMMENT邮箱,dept_idINTUNSIGNEDCOMMENT部门ID,created_atDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT创建时间,PRIMARYKEY(id),CONSTRAINTfk_student_deptFOREIGNKEY(dept_id)REFERENCESdept(id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT学生表;通过ALTER TABLE添加约束-- 添加唯一约束ALTERTABLEstudentADDUNIQUE(email);-- 添加外键约束ALTERTABLEstudentADDCONSTRAINTfk_student_deptFOREIGNKEY(dept_id)REFERENCESdept(id);7.3 索引与约束对比对比项索引约束主要目的提升查询性能保证数据完整性是否影响写入速度会写入时需维护索引会写入时需校验是否可重复创建可创建多个主键/唯一等有唯一性要求是否占用存储空间是额外索引文件部分约束会隐式创建索引典型场景高频查询字段主键、外键、唯一、非空7.4 索引使用注意事项避免索引失效在索引列上使用函数、隐式类型转换、前置通配符如LIKE %张都会导致索引失效应尽量避免。不要过度索引索引并非越多越好每个索引都会占用存储空间并拖慢写入速度只为高频查询字段建立索引。遵循最左前缀原则使用联合索引时查询条件应从最左侧字段开始否则无法命中索引。区分度优先优先为区分度高取值多样的字段建索引如邮箱、手机号而不是性别这类取值极少的字段。定期维护数据频繁增删改后可考虑重建或优化索引保持查询效率。8. 总结本文系统讲解了 MySQL 的数据定义语言DDL与数据操作语言DML核心内容DDL数据库与表的创建、修改、删除以及常用数据类型DML数据的插入、查询、更新、删除以及常用条件与运算符实战通过商品表示例串联全部核心操作。掌握这些基础后你已经具备了日常开发中最常用的 MySQL 操作能力。下一篇文章我们将继续深入索引、约束与事务等进阶主题敬请期待。9. 参考资料与延伸阅读为了帮助你进一步巩固 MySQL 基础、深入理解底层原理这里整理了一些官方文档、经典书籍和社区博客供不同学习阶段参考。9.1 官方文档MySQL 官方文档https://dev.mysql.com/doc/最权威、最完整的语法参考。当你对某个 SQL 语句的细节用法、数据类型或函数不确定时优先查阅官方文档适合作为日常开发的「字典」随时翻阅。9.2 经典书籍《高性能 MySQL》High Performance MySQLMySQL 领域的经典之作深入讲解索引原理、查询优化、复制、备份与高可用架构。适合已经掌握基础语法、希望进阶性能调优与架构设计的读者。《MySQL 必知必会》Sams Teach Yourself MySQL in 10 Minutes短小精悍的入门读物以大量短小示例快速覆盖建表、查询、更新等常用操作适合刚入门、想快速上手的朋友。9.3 推荐阅读的 CSDN 博客MySQL 索引原理与优化实战[占位链接可替换为具体博客地址]系统梳理 B 树索引结构、最左前缀原则与索引失效场景配合本文第 7 节内容阅读效果更佳。MySQL 事务隔离级别与锁机制详解[占位链接可替换为具体博客地址]适合在掌握 DDL/DML 之后进一步理解并发控制与数据一致性。MySQL 慢查询分析与优化案例[占位链接可替换为具体博客地址]通过真实案例演示如何定位慢 SQL 并优化适合进阶学习查询调优。提示以上博客链接为占位符你可以根据实际阅读体验替换为更合适的文章地址。建议优先选择带完整示例、结论清晰的博客并结合官方文档交叉验证。