MySQL CRUD 详解：表数据插入、更新与删除，附生产避坑指南

一、前言回顾 DDL 与 DML 区别DDL操作的对象库、表结构例如增create/删drop/改alter/查show数据表、对数据表的操作添加字段…DML操作的对象表里面的数据例如增insert/删delete/改update/查select数据前置说明本篇所有 SQL 基于上一篇创建好的测试表方便读者连贯复现 上篇博客链接数据库和数据表的创建重要提醒UPDATE、DELETE 是高危语句生产务必谨慎二、INSERT 插入数据INSERT [INTO] table_name [(column [, column] ...)] VALUES (value_list) [, (value_list)] ... value_list: value, [, value] ...2.1 单行插入指定字段指定要插入的数据的字段值既可以指定一个字段也可以指定多个字段也可以指定全部字段。示例 SQL创建一个学生表后续基于这个表操作CREATE table student (id BIGINT,name VARCHAR(15)) ;插入指定字段语句INSERT INTO student(name) VALUES(王五);插入结果SQL意义插入一条name为王五的数据。特点只插入一条数据字段顺序可随意调整字段赋值可以省略但要注意该字段前提为允许为空或有默认值DEFAULT 2.2 单行插入省略字段列表该SQL语句虽省略了列表但是在赋值的时候一定要和表字段的顺序一一对应并且和字段数量一一匹配语法示例-指定字段列表顺序为idnameINSERT INTO student(id,name) VALUES (2,lisi);插入结果SQL意义插入一条id为2name为lisi的数据。2.3 一次性多行插入推荐写法特点不再像上述的语句一般需要一条条插入数据可以同时插入多条数据语法示例-同时插入两条数据INSERT INTO student(id,name) VALUES(3,zhangsan),(4,zhaoqian);插入结果SQL意义同时插入id为3name为‘zhangsan’和id为4name为‘zhoaqian’两条数据优点一次进行多条数据插入减少网络 IO插入性能优于多条单独一条数据的 insert2.4 将查询结果插入表INSERT ... SELECT查询库中其他表的数据或者本表的数据将查询到的内容插入到该表中。场景适用于表之间数据迁移或进行复制示例 SQLINSERT INTO student(id,name) SELECT * from student where id 2插入结果SQL意义查询student表中id为2的数据并将其插入student中。注意如果查询的数据不存在虽然结果不会报错但数据不会被插入进去因为该数据并不存在。2.5 Insert 常见问题主键重复报错数据约束中主键是唯一且非空的是该表的唯一身份标识若insert时主键的值在表中已存在会因为身份标识重复而辨别不了数据的身份所以会出错。示例设置主键为id-注意字段为id的值不能为空也不能重复ALTER TABLE student ADD CONSTRAINT pk_student_id PRIMARY KEY (id);实验重复id现在我们插入一个id为1的数据查看结果很明显报错信息提示id为1已经有数据了不能插入。字段类型不匹配字段有各自的类型赋值时需一一对应否则会报错。示例将字段为id的值设为aaid 的类型为BIGINT明显的是报错信息提示aa不符合id的类型。值得注意的是如果字段类型为varchar如果输入的值为数字mysql会进行隐型转换会将数字转换成字符串的形式存储在字段中varchar的本质其实就是字符串所以不会报错。非空字段没有赋值NOT NULL字段不能为空示例设置name字段为NOT NULL插入id为11name为空的数据显而易见name设为NOT NULL 的时候不能设置为NULL重点空字符串和NULL是不一样的NOT NULL 只约束NULL并不约束空字符串NULL VS 空字符串NOT NULL 表示未知不确定性空字符串表示一个空的字符串有结果是合法数据。三、UPDATE 修改数据3.1 基础语法UPDATE[LOW_PRIORITY] [IGNORE]table_referenceSETassignment[,assignment] ...[WHEREwhere_condition][ORDER BY...][LIMITrow_count]3.2 带 WHERE 条件更新查询表中符合条件的数据进行修改示例修改id为5的数据将name改成小明UPDATE student SET name 小明 WHERE id 5;修改成功3.3 ⚠️重点大坑没有对修改数据这一行为设置where条件查询则会进行全表的修改一般很少会进行全表的修改在修改时要注意是否需要查询条件。生产规范执行 update 前先用 select 校验 where 条件是否正确3.4 同时更新多个字段更新操作可以进行多个字段的修改示例将id为5name为小明修改为id为13name为小张UPDATE student SET id13,name 小张 WHERE id 5;修改成功3.5 UPDATE 常见坑总结1、使用where条件进行更新操作要保证该条件有符合的数据否则就会更新失败而报错。2、和insert语句一样如果修改的字段为主键需要注意不能和其他字段重复一般不会去更新主键字段四、DELETE 删除数据4.1 DELETE 基础语法带 WHEREDELETE FROMtbl_name[WHEREwhere_condition] [ORDER BY...] [LIMITrow_count]示例删除id为5的数据DELETE from student WHERE id5;4.2 ⚠️重点进行DELETE操作时必须带有where条件查询否则就会将整个表的数据全部删除在实际业务中这是一个非常可怕的事情4.3 DELETE VS TRUNCATE 对比面试高频对比项DELETETRUNCATE作用删除表中数据保留表结构清空全表数据保留表结构日志记录日志可以事务回滚不记录日志无法回滚自增主键自增序号继续往下走重置自增主键触发触发器会触发不会触发DML/DDLDMLDDL注意TRUNCATE 不是 DML属于 DDL五、实操小技巧与生产规范写 UPDATE/DELETE先写 WHERE先用 SELECT 验证查询结果再改 delete/update生产环境关闭自动提交事务包裹确认无误再 commit禁止线上直接执行不带 where 的 update、delete重要数据删除前先备份六、总结INSERT新增记录UPDATE修改已有记录DELETE删除记录DML 操作的核心安全点WHERE 条件预告下一篇MySQL DQL 单表查询SELECT