文章目录
- 前言
- 数据插入
- 查询数据
-
- where条件
- 结果排序
- 筛选分页结果
- 执行顺序
- 更新数据
- 删除
-
- delete
- truncate
- 插入查询结果
- 聚合函数
- group by
- 作业部分
前言
增删查改是 MySQL 表操作的核心，也是每个开发者必须吃透的基础技能。但实际开发中，很多人写的 SQL 要么效率低，要么逻辑错 —— 比如不懂 insert 冲突处理、忽略 where 和 having 的执行顺序、分不清 delete 和 truncate 的区别。
本文聚焦 MySQL 表 CRUD 的全场景实战：涵盖插入（单条 / 批量 / 冲突处理）、查询（where 条件 / 模糊匹配 / 排序 / 分页）、更新（条件更新 / 批量更新）、删除（delete/truncate）四大核心操作，以及聚合函数、group by 分组查询等进阶用法，以 “语法 + 示例 + 避坑” 的形式，快速帮你掌握高质量的表数据操作技巧。
数据插入
语法:insert [into] 表名 [(列名1[,…])] values(对应字段的值)[,…];
没有指定到的列的话使用默认值;如果采用的是不指定列的话，是用不了默认值这些的
注意:这里加不加into根本就没有影响
举例:
插入一条数据，指定列:insert into students (name, age) values ('张三', 18);
插入一条数据，不指定列:insert into students values (1, '李四', 19);
插入多条数据，指定列:insert into students (name, age) values ('张三', 18),('李四',19);
在插入时，可能会因为唯一键或者主键的值跟表里的数据冲突导致插入失败，这里有两种解决方法:
1.这种解决方法就是在冲突时，把原来在表里但是跟自己插入的数据冲突的那行的值改成update后面的值，其余值还是原样
语法:insert … on duplicate key update 冲突了的话要改成啥;
举例:
insert into students (id, name, age)values (1, '张三_new', 19), (2, '李四_new', 20)on duplicate key update name = values(name),age = values(age);
//这里的value(name)就是前面插入数据但是产生冲突的那个插入数据的name
insert into students (id, name, age)values (1, '张三_new', 19)on duplicate key update name = '张三',age = 19;
2.另一种解决方法就是替换–在发现数据冲突时，把列表里原来那行删了，在那行插入自己的数据
(这种操作会导致自增长++)
语法:replace [into] 表名 [(列名)] values (要插入的数据的值);–当然，也是可以批量插入数据的
批量插入数据:`values`跟`insert`那后面的写法一样
引申:
x rows affected:表示有x行数据受到影响
注意:上面那第一种解决冲突的办法是0 rows affected
引申:select row_count();这个可以复现最后一条语句影响了多少行数据(这个功能对DML才行)
如果返回的是-1的话，说明最后一次不是DML操作
select还能用来进行运算–eg:select 1+1
注意:MySQL里面不支持用复合赋值运算符(比如:+=,%=)
引申:Linux终端按键盘的向上箭头键可以调出上一次执行的语句是啥
查询数据
这里查询用的是select 语法里面选项太多了，这里就用例子的方式领悟
select distinct math from exam_result;
select distinct * from exam_result;//就是所有的列都要
//distinct可以在显示数据的时候去重
select * from exam_result;//就是所有的列都要
select math 数学,math+10 from exam_result;
select math as 数学,math+10 from exam_result;//加不加as都一样
//这里的数学就是重命名(在这条语句里面的步骤都会认得这个重命名)
//math+10的话，select会帮忙运算之后把结果出来
select name from exam_result where name like '孙%';//姓孙 eg:孙某，孙某某
select name from exam_result where name like '孙_';//孙某
select name from exam_result where name not like '孙%';//不是姓孙的
select name,math from exam_result where math in (58,59,60);//把58，59，60的找出来
select name,math from exam_result where math not in(58,59,60);//除了58，59，60的都要
select name,chinese from exam_result where chinese>=80 and chinese <=90;
select name,chinese from exam_result where name like '孙_' and (chinese>=80 and chinese <=90);
//加()后面那就成一个条件了–要语文在80-90的孙某
MySQL里面,被''和""包裹的会被看作字符串常量(MySQL里面''和""是一样的)
–eg:插入的值是字符串的话，需要包裹一下
注意:取的别名不算字符串常量!–如果别名里面有空格，别名需要用``(反引号)来包裹
from那里给表取别名也是可以的(用不用as都行)
where条件
在查询数据的时候需要限制条件的话就得用到where,where支持的运算符分为比较运算符和逻辑运算符
where支持的比较运算符:
>, >=, <, <=
=:等于，但是NULL不安全
<=>:等于，NULL安全
!=, <>:不等于,NULL不安全
between a0 AND a1:范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回true
in(option, …):如果是option中的任意一个，返回true
is null:是null的话返回true
is not null:不是null的话返回true
like:模糊匹配;% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符
not like:就是跟like反起的
总结:判断是不是null的话，自己用is null和is not null
null不安全指的是用这个比较运算符跟null比较的话，不管对方是啥，结果都是null
比如:1 = NULL跟NULL = NULL的返回值都是null
注意:MySQL里面的NULL跟0跟空串""不是一个东西!
where支持的逻辑运算符:
and:所有条件都是true,返回值才是true
or:条件里任意一个为true,返回值就是true
not:条件为true,返回值就是false–其实也就几个运算符需要用到他而已
注意:where里面不支持取别名,这是语法错误
eg:select total from exam_result where chinese+english+math total <200;这是不行的
结果排序
没有order by的影响，返回的结果的顺序是未定义的–不能依赖这种顺序!
语法:select … from 表名 [where…] order by (复合)列名/别名 [asc还是desc] […];
后面的[…]表示还能加eg:(复合)列名/别名 [ASC还是DESC]
asc:升序 desc:降序 如果没指明的话，默认是升序
注意:在排序时,null认为比任何值都小–eg:升序时null排在张三前面
举例:
select name, math, english from exam_result order by math,english;
先比较math，如果math一样的话，再用english比较
筛选分页结果
这个功能常常用来进行分页
limit认为下标是从0开始的
语法:(没有n条的话，就全要)
1.从0开始，筛选n条
select … from 表名 [where…] [order by …] limit n;
2.从s开始，筛选n条
select … from 表名 [where…] [order by …] limit s,n;
或者select … from 表名 [where…] [order by …] limit n offset s;
注意:如果表是未知的，最好先加一句limit 1–防止表中数据太多，导致卡死
执行顺序
select chinese+english+math total from exam_result where total <200;
这是不行的，因为是where比select先执行
select chinese+english+math total from exam_result
where chinese+english+math<200 order by total limit 1;
这是可以的
执行顺序:
先from搞到表再where进行条件筛选的,然后才select计算,然后order by进行排序,最后才是limit
更全的是:
SQL查询中各个关键字的执行先后顺序
from> on> join> where> group by> with> having> select> distinct> order by> limit
引申:关于换行问题(就是哪些语句写的时候可以换行)
只要换行没有导致拆分关键字，列名，字符串啥的就没事
其实也就是本来空格的地方可以搞成换行–字符串内部不行哈
更新数据
这里的更新数据不是插入数据，是在已插入数据上面进行更新
语法:update 表名 set 列名1…(这里就是想要怎么更新数据) [where…] [order by …] [limit…]
如果没有where的话，那就是那列的全体都要改
注意:update时是不支持给列取别名的
举例:
update exam_result set math = 80,chinese = 90 where name = '张三';
就是把张三的数学改成80，语文改成90
uodate exam_result set math=math+30 order bt chinese+math+english limit 3;
把总成绩倒数的前三名的数学成绩加30分
删除
删除分为普通的删除数据(delete)和截断表(truncate)
–这两种删除都会保留表结构
–truncate是先销毁整个表，然后再建立一个结构一样的新表
delete和truncate的区别:
1.truncate只能对整表进行操作，delete可以对部分数据进行操作
2.truncate是直接销毁整个表然后创建一个同名空表(),而delete是对数据进行操作(这样的话会更新日志)–所以truncate不支持回滚，但是delete支持回滚
3.truncate会重置自增长到哪了(从初始开始)，delete不会(之前记录了是多少，现在还是多少–把最大值删了也是不会变的，因为已经记录下来了)
delete
语法:delete from 表名 [where…] [order by…] [limit…]
delete要么是按行删除，要么是整表删除
delete from exam_result;把这个表全删了
delete from exam_result where name = '张三';只把张三的数据删了
delete form exam_result order by english+math+chinese limit 1;把总成绩最高的那个人删了
truncate
语法:truncate [table] 表名 –加不加table都是一样的
插入查询结果
语法:insert [into] 表名 [列名1,…] select…
[列名1,…]:指定要插入的列，其他列用默认值–select返回的列多于或者少于指定列的话，会报错
如果没有指定要插入的列，那就是全部列都需要select返回的值–返回的列多于或者少于全部列的话，都会报错
注意:如果没有指定列的话，select返回的列的顺序必须跟表中列的顺序一样才行–不然就会硬塞
eg:删除表里面的重复数据
创建一张空表，然后把表里数据去重后插入空表，然后再改名就行了
聚合函数
count([distinct] 表达式):返回查询到的数据的数量
sum([distinct] 表达式):返回查询到的数据的总和,不是数字没有意义 avg([distinct] 表达式):返回查询到的数据的平均值，不是数字没有意义
max([distinct] 表达式):返回查询到的数据的最大值，不是数字没有意义
min([distinct] 表达式):返回查询到的数据的最小值，不是数字没有意义
[distinct]:表示要不要对数据先去重
引申:count(*)是统计的数据总行数,行内数据全是null的也算一行–没有count(distinct *)这个东西
–但是一般的count(…)是不把null当作一个数据的，count(*)是特例
举例:
select avg(english+chinese+math) from exam_result;
select count(*) 英语不及格的人数 from exam_result where english<60;
select min(math) from exam_result where math>70;//超过70分的最小数学成绩
使用了聚合函数的话，非聚合字段必须在group by后面才行
eg:select name,max(english) from exam_result;这样是不行的
group by
使用group by可以对指定列进行分组查询
分组的目的就是为了进行分组之后，方便进行聚合统计
语法:select …1 form 表名 group by …2 [having…];
…1:只能填聚合函数(操作的对象可以是group by没出现的)+group by后面出现过的列名及其组合形成的表达式
…2:依据这里面的进行分组，然后from前面那些对组内进行操作
[having …]:就是对聚合之后的进行筛选,作用跟where类型
顺序:先分组再聚合，最后having筛选
having和where的区别:
这俩进行条件筛选的阶段是不同的
where是在分组之前进行筛选 having是在分组之后进行筛选
select deptno,avg(sal) deptavg from emp where ename!='张三' group by deptno having deptavg<2000;
执行顺序:先from,再where,再group by,再select，再having
举例:
select avg(sal) 平均 from emp group by deptno,job;
就是deptno和job一样的才是一组里面的，他们的sal求平均
select deptno,avg(sal) deptavg from emp group by deptno having deptavg<2000;
作业部分
SQL语言允许使用通配符进行字符串匹配的操作,其中‘%’可以表示(D)
A.零个字符
B.1个字符
C.多个字符
D.以上都可以
引申:因为默认校验规则是utf8_general_ci—所以在查询的时候，是不区分大小写的
