MySQL CRUD 查询、插入、更新、删除全实战指南

insert [into] table_name
	[(column [, column] ...)]
	values (value_list) [, (value_list)] ...
value_list: value, [, value] ...

create table students (
  id int unsigned primary key auto_increment,
  sn int not null unique comment '学号',
  name varchar(20) not null,
  qq varchar(20)
);
-- 插入测试数据
insert into students values 
(100, 10000, '唐三藏', null),
(101, 10001, '孙悟空', '11111'),
(102, 20001, '曹孟德', null),
(103, 20002, '孙仲谋', null);

create table exam_result (
  id int unsigned primary key auto_increment,
  name varchar(20) not null comment '同学姓名',
  chinese float default 0.0 comment '语文成绩',
  math float default 0.0 comment '数学成绩',
  english float default 0.0 comment '英语成绩'
);
-- 插入测试数据
insert into exam_result (name, chinese, math, english) values 
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30);

-- 全列插入（指定id）
insert into students values (104, 20003, '鲁智深', '22222');
-- 省略自增主键（自动生成id）
insert into students (sn, name, qq) values (20004, '林冲', '33333');

insert into students (sn, name) values 
(20005, '武松'),
(20006, '杨志');

-- 主键冲突（id=100已存在），执行更新
insert into students (id, sn, name) values (100, 10010, '唐大师')
on duplicate key update sn = 10010, name = '唐大师'; // 同步更新语法
-- 唯一键冲突（sn=20001已存在），执行更新
insert into students (sn, name) values (20001, '曹阿瞒')
on duplicate key update name = '曹阿瞒';

-- sn=20002已存在，删除原记录后插入新数据
replace into students (sn, name) values (20002, '孙伯符');

-- 创建空表（结构与students一致）
create table students_copy like students;
-- 将students的去重数据插入新表
insert into students_copy select distinct * from students;

select 
  [distinct] {* | column [, column] ...}
from table_name
[where ...]
[order by column [asc | desc], ...]
limit ...

-- 全列查询（数据量大时性能差，不建议在生产环境使用）
-- 1. 查询的列越多，意味着需要传输的数据量越大
-- 2. 可能会影响到索引的使用。(索引待后面我们再进行理解)
select * from exam_result;

-- 查询姓名、语文、数学成绩
select name, chinese, math from exam_result;

-- 常量表达式
select id, name, 10 from exam_result;
-- 单字段运算（英语成绩+10）
select id, name, english + 10 from exam_result;
-- 多字段运算（总分）
select id, name, chinese + math + english from exam_result;

select 
  id, 
  name, 
  chinese + math + english as 总分 
from exam_result;

-- 去重查询数学成绩 distinct
select distinct math from exam_result;

-- 1. 英语不及格（<60）
select name, english from exam_result where english < 60;
-- 2. 语文成绩在[80, 90]之间（between...and）
select name, chinese from exam_result where chinese between 80 and 90;
-- 3. 数学成绩是58、59、98、99中的一个（in）
select name, math from exam_result where math in (58, 59, 98, 99);
-- 4. 姓孙的同学（like %）
select name from exam_result where name like '孙%';
-- 5. 姓名是两个字且姓孙（like _）
select name from exam_result where name like '孙_';
-- 6. 语文成绩好于英语成绩
select name, chinese, english from exam_result where chinese > english;
-- 7. 总分低于200分（表达式作为条件）
select name, chinese + math + english as 总分 
from exam_result 
where chinese + math + english < 200; // 不能直接用总分 < 20,因为执行这里的时候还没执行前面的部分
-- 8. 语文>80且不姓孙（and + not）
select name, chinese from exam_result where chinese > 80 and name not like '孙%';
-- 9. qq号不为空（is not null）
select name, qq from students where qq is not null;
-- 10. null安全比较（<=>）
select name, qq from students where qq <=> null;

-- 1. 按数学成绩升序
select name, math from exam_result order by math;
-- 2. 按数学降序，英语升序，语文升序
select name, math, english, chinese 
from exam_result 
order by math desc, english, chinese;
-- 3. 按总分降序（表达式排序）
select name, chinese + math + english as 总分 
from exam_result 
order by 总分 desc;
-- 4. null排序（null视为最小值，升序在最前）
select name, qq from students order by qq;
select name, qq from students order by qq desc;

-- 语法1：limit 条数（取前n条）
select * from exam_result order by id limit 3;
-- 语法2：limit 起始下标, 条数（从s(下标从0开始)开始取n条）
select * from exam_result order by id limit 3, 3;
-- 语法3：limit 条数 offset 起始下标（推荐，更清晰）
select * from exam_result order by id limit 3 offset 6;
-- 分页示例：每页3条，第1-3页
select * from exam_result order by id limit 3 offset 0; -- 第1页
select * from exam_result order by id limit 3 offset 3; -- 第2页
select * from exam_result order by id limit 3 offset 6; -- 第3页

-- 1. 统计学生总数（count(*)不受null影响）
select count(*) as 学生总数 from students;
-- 2. 统计qq号非空的学生数（null不计入）
select count(qq) as qq已收集人数 from students;
-- 3. 统计数学成绩总分和去重后总分
select sum(math) as 数学总分, sum(distinct math) as 去重数学总分 from exam_result;
-- 4. 统计语文成绩平均分
select avg(chinese) as 语文平均分 from exam_result;
-- 5. 英语最高分和最低分
select max(english) as 英语最高分, min(english) as 英语最低分 from exam_result;
-- 6. 统计70分以上的数学最低分
select min(math) as 70+数学最低分 from exam_result where math > 70;

-- 准备雇员表（经典测试表）
create table emp (
  empno int primary key,
  ename varchar(20),
  job varchar(20),
  deptno int,
  sal float
);
insert into emp values
(7369, 'smith', 'clerk', 20, 800),
(7499, 'allen', 'salesman', 30, 1600),
(7521, 'ward', 'salesman', 30, 1250),
(7566, 'jones', 'manager', 20, 2975),
(7654, 'martin', 'salesman', 30, 1250),
(7698, 'blake', 'manager', 30, 2850),
(7782, 'clark', 'manager', 10, 2450),
(7788, 'scott', 'analyst', 20, 3000);
-- 1. 按部门分组，统计每个部门的平均工资和最高工资
select deptno, avg(sal) as 平均工资, max(sal) as 最高工资 from emp group by deptno;
-- 2. 按部门和岗位分组，统计平均工资和最低工资
select deptno, job, avg(sal) as 平均工资, min(sal) as 最低工资 from emp group by deptno, job;
-- 3. 筛选平均工资低于2000的部门（having筛选分组结果）
select deptno, avg(sal) as 平均工资 from emp group by deptno having avg(sal) < 2000;

update table_name set column = expr [, column = expr ...]
[where ...] [order by ...] [limit ...]

-- 1. 更新单字段（孙悟空数学成绩改为80）
update exam_result set math = 80 where name = '孙悟空';
-- 2. 更新多字段（曹孟德数学60、语文70）
update exam_result set math = 60, chinese = 70 where name = '曹孟德';
-- 3. 按表达式更新（所有同学语文成绩翻倍）
update exam_result set chinese = chinese * 2;
-- 4. 结合排序和limit（总分倒数前三的同学数学+30）
update exam_result 
set math = math + 30 
order by chinese + math + english 
limit 3;
-- 5. 条件更新（英语<60的同学英语+10）
update exam_result set english = english + 10 where english < 60;

-- 1. 删除孙悟空的考试成绩
delete from exam_result where name = '孙悟空';
-- 2. 删除英语<60的同学成绩
delete from exam_result where english < 60;

-- 删除for_delete表所有数据（自增id不重置）
create table for_delete (
  id int primary key auto_increment,
  name varchar(20)
);
insert into for_delete (name) values ('a'), ('b'), ('c');
delete from for_delete;
-- 插入新数据，自增id从4开始
insert into for_delete (name) values ('d');
select * from for_delete; -- id=4

-- 截断表（自增id重置为1）
create table for_truncate (
  id int primary key auto_increment,
  name varchar(20)
);
insert into for_truncate (name) values ('a'), ('b'), ('c');
truncate for_truncate;
-- 插入新数据，自增id从1开始
insert into for_truncate (name) values ('d');
select * from for_truncate; -- id=1

from → on → join → where → group by → with → having → select → distinct → order by → limit