当前位置：首页 > news >正文

【MySQL】--- 表的CRUD

news 来源：原创 2025/7/14 9:57:40

Welcome to 9ilk's Code World

(๑•́ ₃ •̀๑) 个人主页: 9ilk

(๑•́ ₃ •̀๑) 文章专栏： MySQL

CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除)。

🏠 插入C

🧷 基本语法

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 (sn,name,qq) values (123,'张飞','4567890');
//指定列单行插入
insert into students values (10,124,'关羽','4567891');
//全列插入
insert  students values (11,125,'刘备','4567892');
//into可以省略

测试结果:

插入单行数据时values左侧是列属性,右侧是列所匹配的内容，左侧属性和右侧内容要一一对应。
如果左侧列属性用户列举出来，则是指定列插入；如果用户忽略，则是全列插入。
插入语句中的into可以省略。

🧷 多行数据+全列/指定列插入

insert into students values (12,126,'曹操','4567893'),(13,127,'许攸','4567894'),(14,128,'诸葛亮','4567895');
//多行 + 全列

测试结果：

insert into students (sn,name,qq) values (129,'曹冲','4567896'),(130,'孙权','4567897'),(131,'孙尚香','4567898');
//多行 + 指定列

测试结果：

一次批量插入多行数据时，每行要插入的数据可以用逗号分隔开。

🧷 插入否则更新

我们插入时可能会因为主键和唯一键约束而插入失败：

insert into students (id,sn,name) values (17,132,'吕布'); //主键冲突
insert into students (sn,name) values (131,'吕布'); //唯一键冲突

测试结果：

Q：如果我们想遇到有主键或唯一键的字段插入时，如果不冲突则插入，冲突则修改数据，该怎么做呢？

我们可以选择性地使用同步更新操作语法：

📖 插入替换1

语法：

INSERT ... ON DUPLICATE KEY UPDATE column = value [, column = value] ...

示例：

insert into students values (13,132,'xuyou','111111') on duplicate key update sn=132,name='xuyou',qq='111111';
//要插入的主键冲突

测试结果：

insert into students values (18,131,'sushangxiang','121212') on duplicate key update id=18,name='sushangxiang',qq='121212';
//唯一键冲突

测试结果：

注：当你在更新时要保证更新的值不能和其他记录的唯一键和主键冲突

更新结果1：0 row affected，表示表中有冲突数据，但冲突数据的值和 update 的值相等。

更新结果2：1 row affected，表示表中没有冲突数据，数据被插入。

更新结果3：2 row affected，表示表中有冲突数据，并且数据已经被更新。

📖 row_count()

通过 MySQL 函数获取受到影响的数据行数 :

📖 插入替换2

语法：

-- 主键 或者 唯一键 没有冲突，则直接插入；
-- 主键 或者 唯一键 如果冲突，则删除后再插入
REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');

示例：

更新结果1：1 row affected: 表中没有冲突数据，数据被插入。
更新结果2：2 row affected: 表中有冲突数据，删除后重新插入。

注：插入替换2是删除后再重新插入，与插入替换1直接修改不同。

🏠 查找R

🧷 基本select

（1）基本语法

SELECT[DISTINCT] {* | {column [, column] ...}[FROM table_name][WHERE ...][ORDER BY column [ASC | DESC], ...]LIMIT ...// DISTINCT关键字:表示去重 通配符/列表// ORDEER BY:表示排序

先准备一个学生成绩表方便测试：

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);

（2）全列查询

select * from exam_result;

测试结果：

* ：通配符，常用来代表column列表做全列查询。
通常情况下不建议使用*来进行全列查询：1. 查询的列越多，意味着要传输的数据量越大。2.可能会影响到索引的使用。

（3）指定列查询

select id,math from exam_result;
select id,math,name from exam_result;
select name,id from exam_result;
//多个列用逗号分隔开

测试结果：

指定列查询在select后跟要查询的列，若有多个列则用逗号隔开。
指定列的顺序不需要按定义表的顺序来。

（4）查询字段为表达式

查询数据时，column列表中除了能罗列表中存在的列名外，我们也可以将表达式罗列到column列表中：

select不仅能够用来查询数据，还可以用来计算某些表达式或执行某些函数。

如果我们将表达式罗列到column列表，那么每当一条记录被筛选出来时就会执行这个表达式，然后将表达式的计算结果作为这条记录的一个列值进行显示。

表达式不包含字段：

select id,name,10 from exam_result;

测试结果：

表达式包含一个字段：

select id,name,english+10 from exam_result;

测试结果：

表达式包含多个字段：

SELECT id, name, chinese + math + english FROM exam_result;

测试结果：

（5）为查询结果指定别名

语法：

SELECT column [AS] alias_name [...] FROM table_name;

案例：用as重命名计算的总成绩

SELECT id, name, chinese + math + english as total FROM exam_result;

测试结果：

注：在重命名的SQL语句中，as是可以省略的。

（6）结果去重

如果我们想要查询的结果去掉重复的结果，那我们可以在要查询的列列表前加上distinct关键字:

select distinct math from exam_result;

测试结果：

注：如果distinct后面跟多个列，是把多个列视为一个整体，如果表中记录有多个选择的列的数据都是一样的,才去重。

🧷 where子句

如果在查询时没有指定where子句,则会直接将表中所有的记录作为数据来源来依次执行select语句。
如果在查询时指定where子句，那么在查询数据时会先根据where子句筛选出符合条件的记录，然后符合条件的记录作为数据源来依次执行select子句。

（1）运算符

where子句当中可以指明一个或多个筛选条件，多个筛选条件之间以及单个筛选条件就可以使用逻辑运算符和比较运算符来连接。

比较运算符：

运算符	说明
>,>=,<,<=	大于，大于等于，小于，小于等于
=	等于，NULL不安全
<=>	等于，NULL安全
!=,<>	不等于
BETWEEN a0 AND a1	范围匹配,[a0,a1],如果a0<=value<=a1,返回TRUE(1)
IN (option,...)	如果是option中的任意一个，返回TRUE(1)
IS NULL	是NULL
IS NOT NULL	不是NULL
LIKE	模糊匹配，%表示任意多个(包括多个)字符；_表示任意一个字符。

注：

1. MySQL中判断整数，字符串相等是用=而不是==，而且=不能用来判断NULL。

2. !=,<>对于NULL也是不安全

3. NULL判断一般更喜欢用IS NULL 和 IS NOT NULL

逻辑运算符：

运算符	说明
AND	多个条件必须都为TRUE(1),结果才是TRUE(1)
OR	任意一个条件为TRUE(1),结果为TRUE
NOT	条件为TRUE，结果为FALSE

（2）案例

1. 英语不及格的同学及其英语成绩(< 60)

select name,english from exam_result where english<60;

测试结果：

select后面跟的是要显示的是哪些列，where子句影响的是你要拿的列所匹配的行信息中符合条件的。

2. 语文成绩在[80,90]分的同学及语文成绩

select name 名字,chinese 语文成绩 from exam_result where chinese>=80 and chinese <= 90;

也可以使用between(左闭右闭区间)：

select name 名字,chinese 语文成绩 from exam_result where chinese between 80 and 90;

测试结果：

3. 数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

可以列举出来用or连接：

select name 姓名,math 数学成绩 from exam_result where math=58 or math=59 or math=98 or math=99;

也可以用看是math否在集合里(in)：

select name 姓名,math 数学成绩 from exam_result where math in (58,59,98,99);

测试结果：

4. 找姓孙的同学以及孙某同学

分析：姓孙的同学指的是姓孙后面可能是一个字或多个字；而孙某同学是姓孙但后面只有一个字。我们可以使用like来查找姓孙后面不完全匹配(类似)的数据。

%：匹配 0 或更多字符（包括空字符串）：

select name from exam_result where name like '孙%';
//%是代表任意多个,包括0个

测试结果：

select name from exam_result where name like '孙_';
// _表示一个字符

测试结果：

5. 查找语文成绩好于英语成绩的同学

在where子句中指明筛选条件为语文成绩大于英语成绩，在select的column列表中指明要查询的列为姓名、语文成绩和英语成绩。

select name 姓名,chinese 语文成绩,english 英语成绩 from exam_result where chinese > english;

我们可以看到where子句在编写时，既可以一个列和一个常数比较，也可以一个列和字符串比较，一个列和字符串模糊匹配，也可以一个列和另一个列比较。

6. 查找总分在200分以下的同学

在select的column列表中添加表达式查询，查询的表达式为语文、数学和英语成绩之和，为了方便观察可以将表达式对应的列指定别名为“总分”，在where子句中指明筛选条件为三科成绩之和小于200。

select name 姓名,chinese+english+math total from exam_result where  chinese+english+math  < 200;

测试结果：

注：在where子句中不能使用select中指定的别名

select name 姓名,chinese+english+math total from exam_result where  total < 200;

测试结果：

原因：

1. 整个SQL的执行顺序：先执行from(from exam_result)，其次带着筛选条件(where total < 200),最后再实际筛选(select name,...)。

2. 也就是说这里查询数据时是先根据where子句筛选出符合条件的记录，然后再将符合条件的记录作为数据源来依次执行select语句。当执行where子句的时候还没有重命名所以会报错。

3. 理解：对我们的列做重命名相当于是把前面的筛选工作都做完了，最后再临门一脚修改列名，属于是最后一步，你不能在筛选条件这里重命名，语法不支持，你只能在where子句用表达式筛选。

7. 语文成绩 > 80 且不姓孙的同学

我们需要保证在where子句设置条件语文成绩>80以及不姓孙(like模糊匹配和NOT保证)，两个筛选条件之间使用AND连接，同时我们可以在select的column列指定name和chinese方便我们验证查询结果。

select name 姓名, chinese 语文成绩 from exam_result where chinese > 80 and  not name  like '孙%';
select name 姓名, chinese 语文成绩 from exam_result where chinese > 80 and  name not like '孙%';

测试结果：

NOT可以放在name前也可以放在name后。

8. 孙某同学，否则要求总成绩 > 200 并且语文成绩 < 数学成绩并且英语成绩 > 80

where子句要满足两个筛选条件：要么同学姓孙，要么成绩有相关要求；对于成绩要求需要多个逻辑运算符连接，同时成绩要求这个整体又和前面的姓孙要用OR连接，我们可以用括号将后面的成绩要求括起来。

select name,chinese,math,english,chinese+math+english total from exam_result where name like '孙_' or ( chinesenese < math and english > 80 and chinese+math+english > 200) ;

测试结果：

MySQL中也可以用括号来把条件视为一个单元整体。

9. NULL的查询

用 IS NULL 或 IS NOT NULL查询NULL

//先建表create table test (id int,name varchar(20)
);
insert into test (id,name) values (1,'张三');
insert into test (id,name) values (null,'张三');
insert into test (id,name) values (1,null);
insert into test (id,name) values (null,null);//NULL查询
select * from test where name is null;

测试结果：

NULL和空串是不一样的。

insert into test values (1,' '); //插入空串
select * from test where name is null;

测试结果：

NULL和NULL的比较，=和<=>的区别。

🧷 结果排序 order by

（1）语法

如果我们想对查询到的结果进行排序，那么我们可以使用order by：

SELECT ... FROM table_name [WHERE ...]  ORDER BY column [ASC|DESC], [...];

说明：ASC为升序(从小到大)，DESC为降序(从大到小)，不指定则默认为ASC。
注：没有ORDER BY子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。

（2）案例

1. 同学及数学成绩，按数学成绩升序显示

select name,math from exam_result order by math;

测试结果：

2. 查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示

select name,chinese,math,english from exam_result order by math desc, english asc,chinese asc;

测试结果：

注：多列排序遵循的原则是先按第一列排序要求来，第一列相同时看第二列排序要求，以此类推。

3. 查询同学及总分，由高到低

select name,chinese+math+english total from exam_result order by chinese+math+english desc;

测试结果：

Q ：order by后面可不可以用别名来排序呢？

select name,chinese+math+english total from exam_result order by total  desc

测试结果：

Q：为什么这里order by后面可以使用别名，而之前wherre哪里不可以？

答：排序之前先需要拿到数据！如果你排序前没有筛选，那么在排序过程有一大批数据是不需要的，这样会浪费时间空间，因此正确顺序应该是1.先确定从哪个表中找(from) 2.根据筛选条件(where) 3.根据筛选到的记录作为数据源来执行select语句，拿出要显示的列 4.最后再排序(order by)。因此order by在select之后，此时别名已经指定好了。

4. 查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

我们需要先用where筛选出姓孙的同学或姓曹的同学的数学成绩，再用order by对数学成绩进行降序排序。

 select name,math from exam_result where name like '孙%' or name like '曹%' order by math desc;//注意不能 name like '孙%' or '曹%'这样写！！

测试结果：

（3）不同数据类型排序的依据

1. 数值类型（INT, FLOAT, DECIMAL, 等）

按数值大小排序。
默认升序 (ASC) 从小到大，降序 (DESC) 从大到小。

2. 字符串类型（CHAR, VARCHAR, TEXT）

默认按 字典序（ASCII/UTF-8 顺序） 排序
可以通过 COLLATE 修改排序规则（如忽略大小写）。

3. 日期/时间类型（DATE, DATETIME, TIMESTAMP）

按日期或时间的先后顺序排序。
旧的日期/时间值（如 2000-01-01）排在前面，新的日期/时间值（如 2025-01-01）排在后面。

示例：

CREATE TABLE dates (id INT PRIMARY KEY,event_date DATE
);INSERT INTO dates (id, event_date) VALUES
(1, '2025-01-01'), (2, '2024-01-01'), (3, '2023-01-01');-- 按时间从早到晚排序
SELECT * FROM dates ORDER BY event_date ASC;-- 按时间从晚到早排序
SELECT * FROM dates ORDER BY event_date DESC;

测试结果：

4. 布尔类型（BOOLEAN）

布尔值在 MySQL 中通常存储为 0（FALSE）或 1（TRUE）。
排序时按照数值排序：FALSE < TRUE。

5. NULL

NULL值视为比任何值都要小，降序出现在最下面，升序出现在最上面。

我们可以通过IS NULL将NULL放在最后

CREATE TABLE nulls (id INT PRIMARY KEY,value INT
);INSERT INTO nulls (id, value) VALUES
(1, NULL), (2, 10), (3, 5);-- 强制 NULL 在最后
SELECT * FROM nulls ORDER BY value IS NULL ASC, value ASC;

测试结果：

6.ENUM 类型

按枚举值的索引排序(建表声明enum类型时的常量值顺序，从1开始)。
如果值为空，则按NULL排序规则处理。

CREATE TABLE enums (id INT PRIMARY KEY,priority ENUM('low', 'medium', 'high')
);INSERT INTO enums (id, priority) VALUES
(1, 'medium'), (2, 'low'), (3, 'high'),(4,NULL);-- 按 ENUM 的索引排序
SELECT * FROM enums ORDER BY priority ASC;

测试结果：

说明: low medium high的索引值分别为1,2,3。

7.SET类型

排序时,先按照比较值中的最小索引,升序时最小索引小的值排在前面。
如果最小索引相同，则按照值中的完整字符串进行字典序排序。

CREATE TABLE sets (id INT PRIMARY KEY,preferences SET('coding', 'reading', 'traveling', 'gaming')
);INSERT INTO sets (id, preferences) VALUES
(1, 'reading,coding'),
(2, 'gaming'),
(3, 'coding');- 按 SET 的索引排序
SELECT * FROM sets ORDER BY preferences ASC;

测试结果：

说明：对于“coding,reading"的最小索引为coding代表的1，"gaming”的最小索引为4；而"coding,reading“和"coding”的最小索引相同，但是coding字典序排在其前面。

🧷 筛选分页结果 limit

（1）从0开始,筛选n条结果

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;

示例：

注：n的起始下标为0！

（2）从 s 开始，往后筛选 n 条结果

语法1

-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;

示例：

注：如果从表中筛选出的记录不足n个，则筛选出几个就显示几个。

理解：

语法2

-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
//limit n:代表筛几行 
//OFFSET s:代表从s开始

示例：

建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死。

（3）应用场景

我们在网站看文章分页展示等。

成绩分等级

认识：

1. 我们需要有数据才能排序，只有数据准备好了你才要显示。

2. limit的本质功能是显示，因此limit执行阶段更靠后，比排序还要后，因为排序也是处于数据准备阶段。

🏠 更新U

🧷 语法

UPDATE table_name SET column = expr [, column = expr ...] [WHERE ...] [ORDER BY ...] [LIMIT ...]

SQL中的column=expr，表示将记录中列名为column的值修改为expr。
在修改数据之前需要先找到待修改的记录，update语句中的where、order by和limit就是用来定位数据的。

🧷 案例

1. 将孙悟空同学的数学成绩变更为 80 分

//查看原数据
select name,math from exam_result where name='孙悟空';
//改孙悟空同学成绩
update exam_result set math=80 where name='孙悟空';

测试结果：

2. 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

update exam_result set math=60,chinese=70 where name='曹孟德';

测试结果：

3. 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

//查看原数据
select name,math+chinese+english total from exam_result order by total limit 3;
//update
update exam_result set math=math+30 order by chinese+math+english limit 3;
//注意math修改后可能它们就不是倒数三名了，此时我们要验证是否math+=30我们需要使用集合
select name,math+chinese+english total from exam_result where name in ('宋公明','刘玄德','曹孟德');

测试结果：

MySQL不支持+=。
update后面也可以跟order by和limit来更新结果。

4. 将所有同学的语文成绩更新为原来的 2 倍

select name,chinese from exam_result;
update exam_result set chinese=chinese*2;

测试结果：

如果没有where子句，则更新全表；更新全表的语句要慎用。
MySQL同样也不支持*=。

🏠 删除D

删除与更新操作之前都要注意。

🧷 语法

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

在删除数据之前需要先找到待删的记录，delete语句中的where,order by和limit就是用来定位数据的。

🧷 案例

1. 删除表中的记录

删除孙悟空同学的成绩

 select * from exam_result where name='孙悟空';delete from exam_result where name='孙悟空';select * from exam_result where name='孙悟空';

测试结果 :

删除总分倒数第一名

//先查看倒数第一是谁
select name,chinese+math+english total from exam_result order by total limit 1;
//删除倒数第一delete from exam_result order by chinese+math+english limit 1;

测试结果 :

2. 删除整张表数据

先准备表和插入数据:

-- 准备测试表
CREATE TABLE for_delete (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20)
);-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');-- 查看测试数据
SELECT * FROM for_delete;

测试结果:

删除表并查看

-- 删除整表数据
DELETE FROM for_delete;-- 查看删除结果
SELECT * FROM for_delete;

测试结果 :

观察自增id变化

-- 再插入一条数据，自增 id 在原值上增长
INSERT INTO for_delete (name) VALUES ('D');-- 查看数据
SELECT * FROM for_delete;

测试结果 :

我们看到用delete删除表后插入新数据id列的自增长是在删除前+1，这说明delete from删除表的方式不会将auto_increment置为0。

注：删除表的操作要慎用！

🧷 截断表

（1）语法

TRUNCATE [TABLE] table_name;

（2）示例

先建表并插入数据：

-- 准备测试表
CREATE TABLE for_truncate (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20)
);-- 插入测试数据
INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');-- 查看测试数据
SELECT * FROM for_truncate;

测试结果：

截断表并查看删除结果

-- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作
TRUNCATE for_truncate;-- 查看删除结果
SELECT * FROM for_truncate;

测试结果：

观察自增长id

-- 再插入一条数据，自增 id 在重新增长
INSERT INTO for_truncate (name) VALUES ('D');-- 查看数据
SELECT * FROM for_truncate;-- 查看表结构，会有 AUTO_INCREMENT=2 项
SHOW CREATE TABLE for_truncate\G

测试结果：

我们发现truncate也能清空整张表，但与delete from不同的是，truncate顺带清空了auto_increment的值。

Q：TRUNCATE一张表之后，为什么显示0行受到影响？

1. 实际上truncate并没有对数据真正操作，而是直接释放整个表的数据页,所以影响行数为0。

2. 在数据库底层，表的数据是以数据页(存储单元，通常16KB一页)为单位存储的。

3.当执行TRUNCATE时，数据库引擎会直接将这些数据页标记为“可用"状态，而不是逐行操作每条数据。这种操作本质上是对元数据(关于数据的数据，比如表结构变化或数据页释放信息)的更新。

（3）TRUNCATE的特点

1. 重置自增列：会重置auto_increment的值。

2. 速度快，效率高：TRUNCATE实际上不对数据操作，而是直接释放整个表数据页，而且它不记录每行的删除操作到事务日志中，在执行速度上有优势。

3. 不可回滚：TRUNCATE是DDL(数据定义语言)操作，并不经过真正的事务，无法回滚，执行后数据无法恢复。

（4）TRUNCATE vs DELETE

补充：事务的影响

事务的使用与否会影响MySQL对信息操作信息的记录方式，MySQL使用自身的日志系统来记录数据操作的信息，主要包括以下几种日志：

Undo Log : 记录旧数据，用于存储回滚段信息(事务回滚)。
Redo Log : 记录新数据，用于崩溃后重做已提交事务，进行崩溃恢复。
Bin Log : 存储经过优化的历史SQL操作，用于主从同步。

补充：持久化方式

持久化方式指的是为了能够在系统崩溃后快速恢复数据库数据的方法。将数据库以文件的形式写入磁盘，通常有两种方式：

记录历史SQL语句。
记录数据本身。

truncate vs delete

1. DELETE清空表是逐行删除并记录日志，它在执行时会被包装进事务中，再由MySQL处理；而TRUNCATE是直接释放整个表的数据页，重建数据块，不会记录，操作不通过事务处理。

2. DELETE不会重置自增列，TRUNCATE会重置自增列。

3. DELETE改变的是表中数据，而TRUNCATE是清空表的所有数据并重置表的一部分属性，表的结构并未改变。

4.TRUNCATE只能对整表操作，不能像 DELETE 一样针对部分数据操作；

5.TRUNCATE无法回滚，速度较快；DELETE速度慢，支持回滚，逐行删除。

🏠 插入查询结果

🧷 语法

INSERT INTO table_name [(column [, column ...])] SELECT ...

SQL的作用是将筛选出来的记录插入到指定的表当中。
如果筛选出来的是指定列则指定列插入，否则是全列。

🧷 案例

Q：如何删除表中的的重复复记录，重复的数据只能有一份？

先建表：

-- 创建原数据表
CREATE TABLE duplicate_table (id int, name varchar(20));-- 插入测试数据
INSERT INTO duplicate_table VALUES
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');

思路如下：

1. 先建立一个跟原表结构一样的新表

2. 然后用select查询出原表去重后的数据，将去重后的数据插入到新表

3. 最后再分别重命名原表和新表。

-- 创建一张空表 no_duplicate_table，结构和 duplicate_table 一样
CREATE TABLE no_duplicate_table LIKE duplicate_table;

创建结构相同的表可以使用like。

-- 将 duplicate_table 的去重数据插入到 no_duplicate_table
INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;-- 通过重命名表，实现原子的去重操作
RENAME TABLE duplicate_table TO old_duplicate_table,no_duplicate_table TO duplicate_table;-- 查看最终结果
SELECT * FROM duplicate_table;

可以用Rename重命名表，也可以用alter等其他方式。

测试结果：

Q：为什么最后是通过rename方式进行的?

1. 我们想上传一个1G的文件到linux中并放到一个目录下，要求是原子性放入，此时我们一般不能直接把文件上传到对应目录下，因为上传过程不一定具有原子性，因为上传速度较慢，是个分阶段的操作(数据从客户端传输到服务器,文件在目标目录中创建并写入数据)。

2. 我们做法通常是文件上传到临时目录，全部上传之后再把文件mv(重命名)到指定目录下。

3. 注意 : 重命名(mv / Rename)是原子性的，但是上传到临时文件并不是原子性的，只不过我们可以通过临时文件产生的”隔离临时状态“来防止外界直接访问不完整的文件(哪怕上传过程临时文件受干扰，但是此时目标文件名并未出现到文件系统，此时上传未完成时目标文件不可访问，外界就不可能通过目标文件名访问到不完整的数据)。上传到临时文件直接重命名,外界直接看到目标文件上传好了。

4. 我们最后通过rename方式，其实就是想等一切就绪之后，统一放入，更新，生效等，实现了最终结果的原子性，这与其他冗长的动作相比非常轻！

🌰 : 比如你在图书馆要把一本书放到书架上,1.你拿着还没装订好的书一步步装订,直接放到书架上,在装订完成之前,读者可能看到不完整的书。2.如果你先在工作桌上装订，装订完成之后再放到书架上，此时读者永远只会看到完整的书。

🏠 聚合

🧷 聚合统计

（1）聚合函数

（2）案例

统计班级共有多少同学

select count(*) from exam_result;
select count(1) from exam_result;
select count(2) from exam_result;

测试结果：

注：关于count(1) count(2) 原理其实是select 1 from xxx；你的xxx有多少行(数据个数)就拼多少个1 只不过你只显示1。

统计班级数学成绩有多少

select math from exam_result;
select count(math) from exam_result;
//指定一列统计

测试结果：

注：NULL不会计入结果

统计有效数学成绩个数(不重复)

应该先去重再用count统计

统计数学成绩的总分/平均分是多少

方式1：数学运算

select sum(math) from exam_result;
select sum(math)/count(*)  from exam_result;

方式2：AVG聚合函数

 select AVG(math) from exam_result;

统计数学不及格人数

select count(*) from exam_result where math<60;

统计英语不及格的人的平均分(区域统计)

select SUM(english)/count(english) from exam_result where english < 60;

返回70分以上同学的最低分(MIN)

select min(math) from exam_result where math>70;

返回英语最高分(MAX)

select max(english) from exam_result;

Q：能不能select后带上name?

聚合统计前提是拿出要聚合的数据，才能聚合。
做聚合时必须保证你要查出的数据允许被聚合。
最高成绩只有一个，但是每个人名字不同，这是无法聚合的，比如有三个最高分不确定返回谁的名字。

🧷 分组聚合

(1) 语法

select column1, column2, .. from table group by column;

在select中使用group by 子句可以对指定列进行分组查询。
分组的目的是为了进行分组之后,方便进行聚合统计。(比如我们可以根据性别，对不同性别进行聚合统计)。

（2）案例

1. 准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）：

EMP员工表 DEPT部门表 SALGRADE工资等级表

查看scott数据库：

员工表相关字段如下：

部门表相关字段如下：

工资等级表相关字段如下：

2.如何显示每个部门的最高工资和最低工资

group by的使用一定要结合需求,group的核心是为了分组后好进行聚合统计,所以我们需要把需求分析清楚再使用group by。
分组统计时，group by后指定列名，实际分组是用该列的不同的行数据是否相同来进行分组的。
分完组之后，分组的条件，在组内一定是相同的，因此可以被聚合压缩。
这里的需求是每个部门，我们就需要在group by子句中指明按照部门号进行分组，在select语句中使用avg函数和max函数，分别查询每个部门的平均工资和最高工资。

 select deptno 部门,max(sal) 最高,AVG(sal) 平均 from emp group by deptno;

测试结果：

理解:

分组,可以理解为把一组按照条件拆分成多个组,进行各自组内的统计。
分组也可以理解为"分表",把一张表按照条件在逻辑上拆成多个子表,然后分别对各自的子表进行聚合统计，只不过我们之前做的聚合统计是在一张表里进行的。
也就是说，只要掌握在一张表里查询，在查询之前先分好组，后面要进行的操作是和之前在单表上的聚合统计是一样的。

3.显示每个部门的每种岗位的平均工资和最低工资

需求是每个部门的每种岗位,这里要分两次组,先按部门分组,再按岗位分组。
其实每一张表都可以视为一个独立的组,在组内聚合是正常的.现在无非是先分组,再聚合;一张表内做聚合和多个子表内聚合,都是在一张表内做聚合一样的道理。

select deptno 部门, job 岗位,AVG(sal) 平均工资,min(sal) 最低工资 from emp group by deptno,job;

测试结果 :

group by后面可以跟多个列分组，用逗号分隔。
分组越精细，显示数据越多。好比一颗树层数越大，叶子节点就越多。

如果我想查看不同部门不同岗的的平均工资和最高工资对应谁的名字呢?

select ename,deptno 部门, job 岗位,AVG(sal) 平均工资,min(sal) 最低工资 from emp group by deptno,job;

测试结果：

ename没有在分组条件内出现不属于分组条件,因此无法进行聚合压缩。
分组好之后，一定是分组条件相同，因此相同的列可以进行压缩聚合，但是这里一个组内员工姓名人人不同，无法聚合。此时SQL 引擎不知道应该返回该组哪一个值

注：一般情况下，只有在group by中出现的具体的列名称，才可以在select后面具体出现。另一种可以出现的就是聚合函数的结果。

4. 显示平均工资低于2000的部门和它的平均工资

先统计每个部门的平均工资 -> 先分组聚合

select deptno, avg(sal) from emp group by deptno;

测试结果：

通过having子句筛选出平均工资低于2000的部门 -> 对聚合结果进行判断

select deptno, avg(sal) deptavg from emp group by deptno having deptavg<2000;

测试结果：

having和group by搭配使用，对group结果进行过滤。
先有聚合统计的结果再having判断筛选，因此having可以使用重命名。

注：其实逻辑顺序上select是在having之后运行，但MySQl给having这里做了特殊处理，标准SQL是在having中引用聚合函数(比如having avg(sal) < 2000)或子查询完成的。具体可以参考文章 : 为什么mysql having的条件表达式可以直接使用select后的别名

(3) having和where的区别

1. 共性：where和having都能做条件过滤。

2. where子句是对原始数据过滤，是对具体的任意列筛选；having是对分组聚合之后的结果进行条件筛选。二者筛选的阶段不同！

3. where子句中不能使用聚合函数和别名，而having子句中可以使用聚合函数和别名(使用别名是MySQL的特殊处理)。

4. having常搭配group by使用,没有group by时,MySQL将查询的结果理解为一个整体的组,也可以用having进行筛选,但并不推荐这种做法，因为这样可读性较差也会让人误解这是分好组的；group by后不能用where。

Q : 关于SQL执行顺序?

1. FROM : 先确定要从哪个表中拿读取数据。

2. WHERE：在读取数据中根据指定的列进行行级筛选。

3. GROUP BY：原始数据过滤完之后按照指定列分组，分组之后分组条件相同。

4. 聚合函数计算：计算每个分组的聚合结果，注意不是对整个表。

5. HAVING：对分组聚合之后的结果进行进一步筛选。

6. SELECT：根据用户指定的列输出结果。

Q：如果建模理解分组以及表？

MySQL一切皆表！

面试题：SQL查询中各个关键字的执行先后顺序？

答：from > on> join > where > group by > with > having > select > distinct > order by > limit。

对于order by我们需要先有最终确定好的数据才能排序。
而limit是数据的显示，需要数据准备好才能显示。
标准SQL逻辑设计去重在排序之前，一是为了避免对冗余数据排序提高效率，二是保证最终结果与用户设计一致。当然最终需求看用户也可以排序不去重的数据。

完。

Welcome to 9ilk's Code World

(๑•́ ₃ •̀๑) 个人主页: 9ilk

(๑•́ ₃ •̀๑) 文章专栏： MySQL

🏠 插入C

🧷 基本语法

🧷 单行数据+全列/指定列插入

🧷 多行数据+全列/指定列插入

🧷 插入否则更新

📖 插入替换1

📖 row_count()

📖 插入替换2

🏠 查找R

🧷 基本select

🧷 where子句

🧷 结果排序 order by

🧷 筛选分页结果 limit

🏠 更新U

🧷 语法

🧷 案例

🏠 删除D

🧷 语法

🧷 案例

🧷 截断表

🏠 插入查询结果

🧷 语法

🧷 案例

🏠 聚合

🧷 聚合统计

🧷 分组聚合

相关文章：