CRUD：create（创建）、retrieve（读取）、update（更新）、delete（删除）

1. 插入：

INSERT [INTO] table_name
[(column [, column] ...)] # 列字段
VALUES (value_list) [, (value_list)] ... # 列字段的内容

value_list: value, [, value] ...
1.1 单行数据：全列插入 + 指定列插入

指定列插入：

列字段和列字段的内容一定要一一匹配。

全列插入：

有两种方式：一种是全部省略，另一种是全部指定。

也可以省略 INTO。

1.2 多行数据：全列插入 + 指定列插入

指定列多行插入：

全列多行插入：

1.3 插入否则更新

当主键或唯一键对应的值已经存在时，插入会失败。如果希望先判断数据是否已存在于数据库中：不存在就插入，存在则执行后面的修改语句，可以选择性地进行同步更新操作。

INSERT ... ON DUPLICATE KEY UPDATE

column = value [, column = value] ...

如果数据不存在就插入；如果存在并发生主键或唯一键冲突，则不报错，直接执行后面的修改语句。

注意：更新的值不能与其他主键和唯一键冲突，否则无法更新。

0 row affected：表中有冲突数据，但冲突数据的值和 update 的值相等。

1 row affected：表中没有冲突数据，数据被插入。

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

可以通过 MySQL 指令获取受影响的数据行数：

select row_count();

1.4 替换

主键或唯一键没有冲突时，直接插入；主键或唯一键如果冲突，则先删除再插入。

REPLACE INTO students (sn,name,qq) values (542210, '孙悟空', 102);

1 row affected：表中没有冲突数据，数据被插入。

2 row affected：表中有冲突数据，删除后重新插入。

从 id 就可以看出是删除后插入的：因为 id 是自增的，刚才是 1，现在是 9。

2. 查找：

SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name] # 从哪个表筛选
[WHERE ...] # 筛选条件
[ORDER BY column [ASC | DESC], ...] # 对筛选结果排序
LIMIT ... # 限定筛选出来的条数

distinct：对内容进行去重
*：全列查询
column，column…：指定列查询

2.1 select 列

全列查询：

通常情况下不建议使用 * 进行全列查询

查询列的越多，意味着需要传输的数据量越大；

可能会影响到索引的使用。（索引后面再说）

指定列查询：

指定列的顺序不需要按定义表的顺序来

查询字段为表达式：

select 非常特殊，后面可以跟select 自带的子句，筛选条件等，也可以跟表达式

为查询结果指定别名：

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

结果去重：

2.2 where条件：

之前对表的整体信息做筛选，但是实际在做查询的时候一定有筛选条件。按条件筛选影响的是未来显示出来信息的条目数或者说是行数，以前是按列位单位把全部行都拿出来了。如果一列想取哪些行由where条件来决定。

where是筛选子句，后面可以跟特定的比较运算符 来决策我们应该如何进行筛选，where就有点像C/C++里面的if语句，根据后面条件进行判断。

<,<=,>,>= 通常用来比较整数和浮点数，判断条件是否满足

= 判断字符串 和数组是否相等，另外 = 判断是否等于null是不安全的，而且null和0表示的是不同含义，null真的就是null什么都没有，0就是0。

= 时两侧进行null值比较，是不能参与运算的

如果想判断null是否相等 使用 <=>

一般使用is null判断

总之： = 只能进行数字和字符串的比较，不能用数字和null或者两个null是否相等比较，判断一个值是否等于null 建议用 IS NULL。

!= 也只能用来数字和字符串比较，不能和null进行比较，并且<>也是一样的，建议用 IS NOT NULL

between a0 and a1 要求范围在[a0，a1]
in(option，…) 要求结果必须在(option，…) 这个范围
like 后面具体例子再说

逻辑运算符：

and 条件都要满足，or 满足一个条件就可以，not 不满足条件为真

下面以具体案例熟悉上面操作：

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

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

使用and对条件进行连接

使用between...and...条件

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

使用or进行条件连接，满足任意一个就为真

使用in条件，满足()里任意一个就为真

姓孙的同学

有时候匹配并不给具体值的更细节的字段含义，可能就只给一个模糊搜索的关键字，反正条件不给全就给一个模糊的条件。可以用like模糊匹配

% 匹配任意多个（包括 0 个）任意字符

注意mysql可以用 ‘ ’ 或者 “ “表示字符串

_ 匹配严格的一个任意字符

语文成绩好于英语成绩的同学

where条件即可以是一列和常数、一列和字符串、一列模糊匹配，一列跟另一个列比较

总分在 200 分以下的同学

曾经不是说过可以对列进行重命名吗，那where后面还用写这么一大堆吗

我们发现它报错了，未知列total，我们不是做过重命名吗。这个total不是已经有了吗，怎么这里报不知道total呢？

这里我们就需要讨论一些东西了
问：from这个表，where这个条件判断和select指明要显示的列，整个sql语句的执行顺序是什么？

说明一下sql这里语句执行顺序非常重要，后面还有order by、limit等，你怎么知道谁先执行呢？所以这个顺序一定要搞清楚。

那这里为什么where 后面无法使用重命名的别名呢？

很简单，我们一定是先执行from，在执行where，然后在执行select。

因为是从1中带着2去3中筛选，也就是说要先告诉从那个表筛选数据，然后给筛选条件，拿着筛选条件去筛选。

这里就是先执行1，在执行2，在执行3，而执行3的时候才有重命名，在执行第2的时候就没有这个别名，所以不认识。

而且在where这里重命名也是不行的，对列重命名是属于显示的范畴，相当于是临门一脚把数据都处理完了，只是把列名字改一改，属于最后一步了。不能在筛选的时候就重命名，语法不支持。

所以这个重命名一定是数据已经筛选出来了，列信息已经拿到了然后在对列进行重命名。

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

and与not的使用

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

要么就是孙某同学，要么就得满足后面的一堆要求，总共就两个条件，在mysql如果条件很多可以用（）把这个条件括起来，表示一个单元

NULL的查询：

这里也可以说明 null 和 ’ ’ 不是一个东西

2.3结果排序：

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

ASC 为升序（从小到大）(ascending)

DESC 为降序（从大到小）(descding)

默认为 ASC

注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖那个顺序

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

asc可以省略，默认是asc升序

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

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

每个同学的各门成绩都要查，首先按照数学降序排序，如果数学成绩相同就按英语升序排序，如果数学和英语成绩都相同就按语文升序排序。

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

order by中可以使用表达式

为什么在order by这里可以使用别名进行排序，而where后面没有办法使用别名？

你要对表结构的数据进行排序，一定是得先有数据！有人可能说不是表结构不就天然有数据吗我直接把表结构数据全排完，然后在选行不行。当然可以了。可是数据天然没有经过筛选而直接先排序，排完序在筛，这在技术上肯定可以实现。但你会愿意这样做吗？因为你没有筛选，在排序的时候一定有大批数据其实是不需要排序的，而对这些数据排序本身就是浪费空间和时间，有时会过多的做无用功。

肯定是先把要的数据先给你，然后才进行排序。

在表中拿着筛选条件对表指定列筛选，然后对筛选结果进行排序，所以能不能用别名，完全是取决于当前sql中子句的执行顺序！

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

2.4筛选分页结果：

什么是分页呢？

如果一个表中数据量太大，这个时候如果全列查询就有一大堆，不便于查看分析，有时候不想一次显示这么多，因此就可以对结果进行limit分页。

-- 起始下标为 0

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

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

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

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

默认从0下标开始，筛选多条记录

也可以指定下标开始筛选后面跟的是步长，从指定位置开始，连续读取多条记录。

limit 后面跟的是筛选几行，offset 后面跟的是从那行开始

limit 可以进行分页，就比如数据多就可以这样进行分页读

总成绩排序后只想拿第一名同学

3.更新

UPDATE table_name SET column = expr [, column = expr ...]

[WHERE ...] [ORDER BY ...] [LIMIT ...]

set后面跟的是要重新设定的值，可以是多列。一般在update的时候必须采用对应where子句进行条件筛选，如果没有的话会把这个表中指定的列全部都更新，这是不合理的。

对查询到的结果进行列值更新

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

将曹孟德同学的数学成绩变更为 99 分，语文成绩变更为 99 分

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

更新值为原值基础上变更
注意据更新，不支持 math += 30 这种语法

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

4.删除

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

一般都是根据条件去删除，不加条件就是把整表的内容删除，不过表结构还在，删表结构drop。

删除孙悟空同学的考试成绩

删除整张表数据

注意：删除整表操作要慎用！

mysql表分为表和表里面的数据，delete重点是删表里面的数据，表结构不受影响，drop才是删表结构。

建表，插入数据

表的内容已经没有了，但是查看创建表结构这个语句还在，并且自增长也还在。

说明delete from清空表的方式不会将自增值置0。

清空表还有一种做法叫做截断表。在效果上和delete一模一样，但是在细节和原理是有差别的。

截断表：

TRUNCATE [TABLE] table_name

注意：这个操作慎用

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

实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚

会重置 AUTO_INCREMENT 项

可以看到表结构还在，但是内容已经被清空了。但是这里值得注意的一点是，之前自增长是4，现在truncata清空表后自增已经没有了。

truncate会重置自增长，而delete并不会。

实际上，TRUNCATE 和 DELETE 在底层机制上有显著差异。TRUNCATE 属于 DDL（数据定义语言），它通过直接释放数据页来清空表数据，并隐式提交，不支持事务回滚。而 DELETE 属于 DML（数据操作语言），它的执行过程会被包装在事务中，交由 MySQL 处理。

包不包事务有什么影响呢？
这与 MySQL 的日志系统息息相关。MySQL 运行中会产生几种重要的日志：undo.log、redo.log 和 bin.log。
undo.log（回滚日志）：配合事务使用，用于事务回滚和 MVCC（多版本并发控制）。
redo.log（重做日志）：为了保证持久化机制（即宕机时不丢数据），它记录的是数据页的物理修改，确保在系统崩溃后能快速恢复已提交的数据。
bin.log（二进制日志）：记录历史上优化后的逻辑操作（如具体的 SQL 语句），主要用于数据备份和主从同步（将一个数据库的变更同步给另一个，保持数据一致）。注：在 MySQL 5.7 及以前版本默认关闭，但 MySQL 8.0 之后默认开启。

为什么 TRUNCATE 比较快？
TRUNCATE 之所以比 DELETE 快得多，是因为它少做了很多事情：它不走完整的事务机制，不产生逐行删除的 undo 日志，也不像 DELETE 那样把逐行的操作痕迹详细记录在日志中（虽然 TRUNCATE 如果开启了 binlog 也会记录一条 DDL 操作，但它不涉及数据行的变更记录）。TRUNCATE 仅仅是单纯地重建/清空表结构，极大地减少了日志开销，因此速度极快。

5.插入查询结果：
INSERT INTO table_name [(column [, column ...])] SELECT ...

可以将select和insert组合，把数据从其他表里面筛选出来，然后插入到另一个表里面。

可能会想到直接把重复信息delete from删除，信息都是一样的，删就会一起删掉。

可能还会想到前面select查的时候，不是有一个对结果进行去重复吗，对这样确实是去重，但是这是查的时候去重，但表本身没有变。

1.创建一个和原表一样结构的空表des_table
2.从原始表中把去重之后的结果筛选出来插入到des_table这个表就是不重复的
3.然后对src_table重命名，des_table改名字为src_table，最终就完成了src_table去重

现在有两个表，一个src_table，一个des_table，然后对这两个表做一下重命名

这里有个细节问题，为什么最后是通过rename方式进行的？

这是一个关于原子性的经典细节。假设我们要往 Linux 上上传一个 1G 的文件，需要 10 分钟。我们通常不会直接上传到目标业务目录，为什么？因为上传是一个很慢的写入过程，在此期间文件处于非原子状态（如果别的程序来读，会读到半截文件）。
所以，我们一般会先把文件上传到临时目录。等全部上传成功后，再把它 move 到目标目录。直接 move 这个动作，底层其实就是 rename，它是原子的。
同理，文件重命名也是基于这个道理。我们用 rename 方式，就是想等所有数据都准备就绪了，再瞬间把它“统一放入、更新并生效”。和冗长的其他动作（比如 I/O 读写）相比，rename 只是修改文件系统的元数据，动作非常轻量。要么成功，要么失败，绝不会让外界看到中间状态。

6.聚合函数

这里是为select分组查询做准备的，聚合函数是以查出来的记录为单位帮我们进行数据聚合统计的。这种聚合统计方式通常是产出一个期望的结果，如个数、和、平均值、最大值、最小值。

mysql中其实也是有函数的，这个函数可以被直接调用，我们可以在mysql直接使用聚合函数直接对一组结果进行聚合统计。

聚合函数（）里面可以是全列，可以是指定列

为什么1也行呢？
实际上在做select的时候，select 1 就是这个表有多少行就拼多少1，只不过只是显示1

统计班级数学成绩有多少

统计班级去重后数学成绩有多少
COUNT(DISTINCT math) 统计的是去重成绩数量

统计数学成绩总分

统计平均总分

统计数学成绩小于60分的个数

返回英语最高分

返回 > 99 分以上的数学最低分

这里为什么不能把name带上呢

聚合统计有一个基本前提：你要查询的列，必须是被允许聚合的。
举个简单的例子，MIN(score) 算出来只有一个数字（全表最低分），但 name 每个人都不一样，它是一个多行维度的数据。如果你直接把 name 和 MIN(score) 放在一起查，数据库就不知道该怎么展示了——到底是把这个最低分挂在哪个 name 上？这就没法聚合。

所以 SQL 的规则是：普通列不能和聚合函数直接混用。你想查每个人的名字，又想查他们对应的最低成绩，就必须先分组。通过 GROUP BY name，把数据分成一个个逻辑组，这样每个 name 对应一个组，然后再对每个组内部执行 MIN(score)，这时候出来的结果才是明确且合法的。

7.分组查询

分组是对表中的数据进行分组，分完组之后，再对表中每一组进行相关聚合统计。分组的目的是为了方便进行聚合统计。 如班级里有男生女生，我们相对男生女生成绩分别统计，所以可以对性别进行分组然后在进行成绩的聚合统计。

在select中使用group by子句可以对指定列进行分组查询。我们也可以把数据筛选之后再进行分组然后再聚合统计。

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

如何显示每个部门的平均工资和最高工资

注意说的是每个部门！我们未来是要分组的，凭什么分组，是由需求来决定的。这里很明显说的是每个部门，公司员工信息全部都在员工表里，部门号不同员工一定隶属于不同部门，所以我们要显示每一个部门的平均和最高工资。

以前我们找公司最高工资和平均工资。我们是把这一张表当作一个大的组，这一张表在我看来就是一个整体的组，我要统计的就是全公司最高工资和平均工资。

但现在需求是按照组来统计的，根据的emo表中deptno列来进行分组。

group by后面跟的是按什么分组

哪个部门的最高工资和平均工资

首先强调，GROUP BY 并不是随便就能用的，必须结合具体的业务需求。它的核心作用是为了进行分组聚合统计，因此，你必须先理清需求，将其与 GROUP BY 的功能对应起来。
在进行分组统计时，GROUP BY 后面指定了列名，指明了我们要按哪一列（或多列）分组。但底层的实际分组逻辑，是根据该列在不同行中的数据是否相同来划分的。当我们完成分组后，分组条件列（如 deptno）在同一个组内的值必定是完全相同的，因此它可以被安全地“压缩”（作为聚合的维度）。

那么，什么是分组？分组本质上就是按条件将一个全集拆分为多个组，然后进行各组内的统计。 你可以把分组理解为在逻辑上将一张表按条件拆成了多个“子表”，然后分别对每个子表进行聚合统计。我们之前学过的单表聚合，相当于只有一张表；现在的分组，只是在查询前先做了逻辑拆分。只要掌握了单表查询，在此基础上先做好分组，后续的聚合思路和之前是一模一样的。
例如：“显示每个部门的每种岗位的平均工资和最低工资”。

需求是“每个部门的每种岗位”，这就注定了必须要分组，而且不是单列分组。我们首先按部门分组，然后再在部门内部按岗位进行二次分组。

在我看来每一组都是独立的组，在组内做聚合，无非就是比以前多做一份工作，先分组，再聚合。 一张整表内做聚合和多个子表内做聚合，总之还是在一张表内聚合。

哪个部门的哪个岗位的平均工资和最高工资

哪个部门的那个岗位的谁的平均工资和最高工资

刚才我们故意在 SELECT 列表里加上了 ename 列，结果数据库直接报错了。报错的核心原因是：ename 没有出现在 GROUP BY 的分组条件中。

我们之前提过，分组的本质是组内必定有某些列的值是完全相同的（比如部门号 deptno 相同、岗位 job 相同）。既然它们完全相同，就可以被安全地“压缩”展示。但现在多出了一个 ename，名字每个人都不一样，数据库无法在多行不同的值中给你挑出一个，自然无法进行压缩和聚合，只能报错。

由此可以提炼出 SQL 中非常核心的大原则（即 ONLY_FULL_GROUP_BY 的限制）：
在 SELECT 后面，能直接出现的列只有两类：
在 GROUP BY 中出现的具体列名（因为组内相同，可以保留）；
聚合函数（因为多行数据已经被压缩成了一个值）。
除了这两类，绝不能直接跟其他既不是分组条件、也没有被聚合函数包裹的列。

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

需求是平均工资低于2000的部门，要的是部门，和它的平均工资是多少。

我们统计出来每一个部门的平均工资(结果先聚合出来)

在进行判断(对聚合的结果，进行判断)

要的不是所有部门，要的是平均工资低于2000的部门，此时要对聚合的结果拿出来判断。

having和group by配合使用，对group by结果进行过滤， 先要有数据然后having对这一批数据做判断，筛选出想要的。

having 和 where 区别理解？执行顺序？构建对 “结果” 的理解。

首先having和where都是进行条件筛选，但是它们两个是完全不同的筛选。

比如说这里把having换成where就会报错

虽然这里可以查询出来，但是并不意外，因为having本就是分组查询的，刚开始说过对于组的理解不要死板理解上面是一张整表下面是分组，我们把分组理解成分表。我们把一个表拆成很多表，每一张表内做聚合统计，其实都是之前说的聚合统计。但是如果我们没有做分组这张表宏观上在我们看来也是一组数据，所以having这里可以充当where。一般在select这里严重不推荐这样having。

而上面用where不行，至少说明having和where因为一些特征是无法互相取代的。

SMITH员工不参与统计，显示平均工资低于2000的部门和它的平均工资

having 和 where 区别理解？执行顺序？构建对 “结果” 的理解。

在我们看来where是对具体的任意列进行条件筛选，having对分组聚合之后的结果进行条件筛选。 它们俩的应用场景是完全不同的。换句话说where是先对原始表进行条件过滤，对过滤后的结果在进行分组。

所以这条sql顺序如下：1. 先从那个表拿数据，2. 在这个表拿数据的筛选条件是什么，数据筛选之后，3. 对这些数据进行分组，4. 分组之后结果进行聚合统计以及重命名，5. 数据全都出来了然后才是对结果进行筛选

所以where和having都叫条件筛选，它们的区别在于条件筛选的阶段是不同的。

构建对 “结果” 的理解（小小的建模）
不要单纯的认为，只有磁盘上表结构导入的mysql，真实存在的表，才叫做表。中间筛选出来的，最终结果，全部都是逻辑的表。"MySQL一切皆表"，所以未来只要我们能够处理好单表的CURD，所有的sql场景，我们全部都能用统一的方式进行。

SQL查询中各个关键字的执行先后顺序
from > on> join > where > group by > with > having > select> distinct > order by > limit