【MySQL 07】表的增删查改 (带思维导图)

于 2024-08-19 05:00:00 发布
阅读量546 收藏 13
点赞数 15
分类专栏： MySQL 文章标签： mysql linux 服务器 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/shangguanxiu/article/details/141200518
版权

文章目录

🌈 一、insert 添加数据

INSERT [INTO] 表名 [(列名1 [, 列名2, ..., 列名n])] VALUES (值1, 值2, ..., 值n) [, (值1, 值2, ..., 值n)];

参数说明

  • 虽然 MySQL 不区分大小写，但此处还是要用大写清楚的表示哪些是关键字。
  • 方括号 [ ] 括起来的是可选项。

准备工作

  • 为了方便之后的操作，现在创建一张名为 students 的学生表。
    • 表中包含自增长的主键 id、非空且唯一键的学号 sn、非空的姓名 name 和唯一键的 qq 号这四个字段。

image-20240813095953029

⭐ 1. 单行数据 + 全列插入

INSERT [INTO] 表名 VALUES (给第1列的值) [, (给第2列的值, ..., 给第n列的值)];
  • 每次向表中插入一行数据，在插入数据时不指定字段名，表示按照表中默认的字段顺序进行全列插入，插入的数据的类型要和表中对应字段一致。
    • 插入单行数据时，可以对指定列进行插入，也可以进行全列插入，这里就只演示全列插入。

image-20240813101446703

⭐ 2. 多行数据 + 指定列插入

INSERT [INTO] 表名 (列名1 [, 列名2, ..., 列名n])] VALUES (值1, 值2, ..., 值n) [, (值1, 值2, ..., 值n)];
  • 可以一次性向表中插入多条数据，插入的多条数据之间使用逗号隔开。
    • 插入多行数据时，可以对指定列进行插入，也可以进行全列插入，这里就只演示对指定的 sn、name、qq 这三列进行插入。
  • 注：在对指定列插入数据时，只有允许为空的字段和设置了自增长属性的字段能不指定值插入，不允许为空的字段必须指定值插入。

image-20240813101646435

⭐ 3. 插入否则更新

  • 在往向表中插入数据时，如果主键或唯一键的值与已有数据发生了冲突，会导致本次操作被 MySQL 拦截。

image-20240813103452815

  • 此时就需要使用插入否则更新的 sql 语句出马了。

1. 插入否则更新的语法格式

INSERT ... ON DUPLICATE KEY UPDATE 列名1 = 值1 [, 列名2 = 值2, ..., 列明n = 值n];
// 语句中的 字段=值，表示当插入数据时，如果出现冲突则需要更新的字段值。
  • 如果表中 未 发生数据冲突，则插入数据到表中。
  • 如果表中 有 发生数据冲突，则更新表中的数据。

2. 插入否则更新的使用示例

  • 向表中插入数据时，如果发生了主键冲突，则直接更新表中学号 sn 字段和姓名 name 字段的值，QQ 号就不更新了。

image-20240813105304041

3. 判读数据的插入情况

  • 执行插入否则更新的语句之后，能通过反映回来的受影响的数据行数来判断数据的插入情况。
    • 0 row affected：表中发生数据冲突，但冲突数据的值和更新的值相等。
    • 1 row affected：表中没有发生数据冲突，数据直接被插入。
    • 2 rows affected：表中发生数据冲突，并且数据已经被更新。

⭐4. 插入否则替换

REPLACE INTO 表名 [(列名1 [, 列名2, ..., 列名n])] VALUES (值1, 值2, ..., 值n) [, (值1, 值2, ..., 值n)];
// 只是将 插入数据  语法中的 INSERT 替换成了 REPLACE 而已
  • 当 未 和表中现有的主键或唯一键字段的数据发生冲突时，会直接将数据插入到表中。
  • 当 有 和表中现有的主键或唯一键字段的数据发生冲突时，会先将表中发生冲突的数据删除，然后再插入新的数据。

1. 插入否则替换的使用示例

  • 不和现有数据发生冲突，执行的仅仅是插入功能。

image-20240813111724107

  • 和现有数据发生冲突，执行的是替换功能。

image-20240813112117517

2. 判断数据替换的情况

  • 执行插入否则替换的语句之后，能通过反映回来的受影响的数据行数来判断数据的插入情况。
    • 1 row affected：表中没有发生数据冲突，数据直接被插入。
    • 2 rows affected：表中发生了数据冲突，表中的冲突数据被删除后插入了新的数据。

🌈 二、select 查询数据

SELECT [DISTINCT] {* 或 {列名1 [, 列名2, ..., 列名n] ...}} FROM 表名 [WHERE ...] [ORDER BY ...] [LIMIT ...];

准备工作

  • 为了方便之后的操作演示，先创建一张名为 exam_result 的学生成绩表。
    • 表中包含自增长的主键 id，非空的学生姓名 name，语文成绩 chinese、数学成绩 math 和英语成绩 engilsh 这五个字段。

image-20240813114442145

  • 创建完表之后，再插入几条测试数据，方便之后进行查询操作。

image-20240813115239883

⭐ 1. select 列

🌙 1.1 全列查询

  • 全列查询表示的是要将信息全部读取出来。
SELECT [DISTINCT] * FROM 表名 [WHERE ...] [ORDER BY ...] [LIMIT ...];

image-20240813115358551

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

  1. 被查询到的数据需要通过网络从 MySQL 服务器传输到本主机，查询的列数越多，意味着需要传输的数据量越大。

  2. 使用全列查询可能还会影响到索引的使用。

🌙 1.2 指定列查询

  • 在查询数据时也可以只对指定的某些列进行查询。
SELECT [DISTINCT] 字段1 [, 字段2, ..., 字段n] FROM 表名 [WHERE ...] [ORDER BY ...] [LIMIT ...];
  • 指定查询成绩表 exam_result 中的姓名 name 字段和数学 math 字段。表示当前只想查看所有学生的数学成绩。

image-20240813120159871

  • 指定查询 id、name、english 这三列的内容。

image-20240813120304736

🌙 1.3 查询字段为表达式

  • select 是个很特殊的关键字，它可以是 select 自带的各种子句、指定表的字段名、普通表达式等。
    • select 不仅能够用来查询数据，还可以用来计算某些表达式的值或执行某些函数。

image-20240813161554127

  • 列表中的表达式也可以包含多个表中已有的字段，可以通过表达式计算这些字段获得其他特别的数据。
    • 求每名同学的语数英三科成绩的总分。

image-20240813161836296

🌙 1.4 为查询结果指定别名

  • 可以给表的某一列取个别名，用来更好的分辨某一列的功能。
  • 对列做重命名属于显示的范畴，是最后一步已经拿完数据了，只是做个改名操作而已，只能在 select 语句这里进行重命名。
    • 执行顺序在 select 之前的子句无法对列取别名。
SELECT 指定列名 [AS] 指定列的别名 [...] FROM 表名;
  • 将成绩表中每名同学的语数英成绩加起来，并对该表达式起个名为总分的列别名。
    • 虽然 as 可带可不带，但是为了更好的阅读体验建议还是带上。

image-20240813161952113

  • 还可以为表中自带的字段取别名。

image-20240813162704331

🌙 1.5 结果去重

SELECT DISTINCT 字段列表 from 表名;
  • 在进行查询时，可能会有重复的数据出现。

image-20240813163355669

  • 可以在 select 关键字的后面加上 distinct 关键字，当要筛选的那一列出现重复的数据时，只保留一份数据。

image-20240813163445663

⭐ 2. where 条件查询

  • 在查询时，也不是每次都要查询表中所有的行的数据，还需要根据一些筛选条件查看指定行的数据。where 筛选影响的是显示出来的行数。
  • 在查询数据时如果使用了 where 子句，则会先根据 where 子句筛选出符合条件的行数据，然后将符合条件的行数据作为数据源依次执行 select 语句，从而找出符合条件的列数据。

🌙 2.1 运算符介绍

  • where 子句可以指定 1/ n 个筛选条件，where 使用特定的比较运算符和逻辑运算符类决定如何进行筛选。

1. 比较运算符

比较运算符说明
>，>=，<，<=大于，大于等于，小于，小于等于
=等于，NULL 不安全，如：NULL = NULL 的结果是 NULL
<=>等于，NULL 是安全的，如：NULL <=> NULL 的结果是 TRUE
!=，<>不等于
BETWEEN 值1 AND 值2在 [值1, 值2] 这个范围之内取值 (含最小值和最大值)
IN (…)从 IN 之后的多个值之中，进行多选一
IS NULL是 NULL
IS NOT NULL不是 NULL
LIKE 占位符模糊匹配，占位符如果是下划线 _ 表示任意一个字符；如果是百分号 % 表示任意 0 ~ n 个字符

2. 逻辑运算符

  • MySQL 支持通过逻辑运算符将多条筛选语句组合起来。
逻辑运算符说明
AND 或 &&并且 (多个条件需要同时成立)
OR 或 ||或者 (多个条件任意一个成立)
NOT 或 !非 (条件为真，结果为假；条件为假，结果为真)

🌙 2.2 where 使用案例

  • 当前准备了如下的 where 子句的使用案例
  1. 查询英语不及格的同学及其英语成绩。
  2. 查询语文成绩在 [80, 90] 分之间的同学及其语文成绩。
  3. 查询数学成绩是 58 或者是 59 或者是 98 或者是 99 分的同学及其数学成绩。
  4. 查询姓赵的同学以及赵某同学。
  5. 查询语文成绩优于英语成绩的同学。
  6. 查询总分在 200 分以下的所有同学的信息。
  7. 查询语文成绩 > 80 并且不姓李的同学。
  8. 查询重某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80 分。
  9. NULL 的查询。

1. 查询英语不及格的同学及其英语成绩

  • 在 where 子句中指定筛选条件为 english < 60，在 select 的字段列表中指明要查询的字段为 name 和 english。

image-20240813171444674

2. 查询语文成绩在 [80, 90] 分之间的同学及其语文成绩

  • 在 where 子句中指定筛选条件为 chinese >= 80 && chinese <= 90，在 select 的字段列表中指明要查询的列为 name 和 chinese。

image-20240813171758706

  • 也可以在 where 子句中使用 between 80 and 90 查询 [80, 90] 分的同学的信息。

image-20240813180220632

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

  • 在 where 子句中指定筛选条件为数学成绩为 58 || 59 || 98 || 99，在 select 的字段列表指定要查询的列为 name 和 math。

image-20240813172133773

  • 除了用或的方式筛选之外，还可以使用 in(58, 59, 98, 99) 从这 4 个值中任选一个。

image-20240813172443675

4. 查询姓赵的同学以及赵某同学

  • 查询姓赵的同学：即不管姓名有几个字，只要姓赵即可。在 where 子句中可用 name like ‘赵%’，% 能匹配任意多个字符。

image-20240813173351544

  • 查询赵某同学，即查询姓赵且名字个数为 2 的同学。在 where 子句中可使用 name like ‘赵_’ 来匹配，_只能匹配一个字符。
    • 由于表中只有一个赵六姓赵，所以查询效果会看着和使用 % 没什么区别。

image-20240813173327920

5. 查询语文成绩优于英语成绩的同学

  • 在 where 子句中指定筛选条件为 chinese > english，在 select 的字段列表中指定要查询的列为 name、chinese 和 english。

image-20240813173633139

6. 查询总分在 200 分以下的所有同学的信息

  • 在 where 子句中的筛选条件为 chinese + math + english < 200，在 select 的字段列表中指定要查询的列为 name 和三科总分。

image-20240813173943150

7. 查询语文成绩 > 80 并且不姓李的同学

  • 在 where 子句中，指定筛选调教为 chinese > 80 并且 name 非 like ‘李%’。

image-20240813174258009

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

  • 被查询的人要么是重某，要么是总成绩 > 200 并且 chinese < math 并且 english > 80 的同学。
    • 重八满足条件 1，王五满足条件 2，都符合 或者 的定义，因此是这两个人被查询出来了。

image-20240813174912699

9. NULL 的查询

  • 为了方便演示，此处使用在添加数据那里使用的 students 学生表进行查询。

image-20240813183303475

  • 查询 qq 号已知的同学：即查询 qq 号 is not null 不为空的同学。

image-20240813183458568

  • 查询 qq 号未知的同学，即查询 qq 号 is null 为空的同学。

image-20240813183608623

⭐ 3. order by 结果排序

  • 由于查询的结果是个表结构，可能会有多行信息，通常将一行数据称之为记录，此时就需要对这些筛选出来的记录进行排序。

🌙 3.1 order by 语法格式

SELECT ... FROM 表名 [WHERE ...] ORDER BY 字段1 [ASC 或 DESC], [...];
  • ASC 表示升序，DESC 表示降序，默认为升序 ASC。
  • 如果查询语句中没有 order by 子句，则返回的顺序是未定义的。
  • 可以根据多个字段进行排序，当根据字段 1 的值排序完之后出现了重复值，则再根据字段 2 的值对重复的部分排序，以此类推。

🌙 3.2 order by 使用案例

  • 当前准备了如下 order by 子句的使用案例。
  1. 查询所有的同学及其数学成绩，查询结果按 数学成绩 升序显示。
  2. 查询所有的同学及其 qq 号，查询结果按 qq 号排序显示。
  3. 查询同学的各门成绩，查询结果依次按 数学降序、英语升序、语文升序 的方式进行显示。
  4. 查询所有同学的成绩总分，查询结果按 总分 降序显示。
  5. 查询姓 赵 的的同学或者姓 王 的同学的数学成绩，查询结果按 数学成绩 降序显示。

1. 查询所有的同学及其数学成绩，查询结果按 数学成绩 升序显示

  • 在成绩表中查询：在 select 的字段列表中指定要查询的列为 name 和 math，在 order by 子句中指定按照 math 进行 asc 排序。

image-20240813190338817

2. 查询所有的同学及其 qq 号，查询结果按 qq 号排序显示

  • 在学生表中查询：排升序，在 select 的字段列表中指定要查询的列为 name 和 qq，在 order by子句中按照 qq 号进行 asc 排序。
    • 注：由于 qq 的数据类型是 varchar，因此采用 ASCII 码进行比较。

image-20240813190826088

  • 在学生表中查询：排降序，在 select 的字段列表中指定要查询的列为 name 和 qq，在 order by子句中按照 qq 号进行 desc 排序。

image-20240813191021461

3. 查询同学的各门成绩，查询结果依次按 数学降序、英语升序、语文升序 的方式进行显示

  • 在成绩表中查询，在 select 的字段列表中指明要查询的列为 name、math、english 和 chinese，在 order by 子句中指明依次按照 math desc, english asc, chinese asc 进行排序。
    • 首先按照数学成绩进行降序排序的，在相同的数学成绩之间按照英语进行升序排序。

image-20240813191817685

4. 查询所有同学的成绩总分，查询结果按 总分 降序显示

  • 在成绩表中查询：在 select 的字段列表中指中指定要查询的列为 name 和 总分 (表达式查询)，在 order by 子句中指明按照 chinese + math + english 进行 desc 排序。

image-20240813192132848

5. 查询姓 赵 的的同学或者姓 王 的同学的数学成绩，查询结果按 数学成绩 降序显示

  • 在成绩表中查询：在 where 子句中指定筛选条件为姓 赵 / 王，再在 order by 子句中指定为按 math 进行 desc 排序。

image-20240813192725415

⭐ 4. limit 筛选分页结果

  • 对获取出来的结果信息进行分页显示。

🌙 4.1 limit 语法格式

  1. 从第 0 行数据开始，向后筛选出 n 行数据。

    SELECT ... FROM 表名 [WHERE ..] [ORDER BY ...] LIMIT n;

  2. 从第 s 行数据开始，向后筛选出 n 行数据。

    SELECT ... FROM 表名 [WHERE ...] [ORDER BY ...] LIMIT s, n

  3. 从第 s 行数据开始，向后筛选出 n 行数据 (比第二种用法更明确，建议使用)。

    SELECT ... FROM 表名 [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
  • 注意：limit 子句在筛选记录时，记录的下标从 0 开始，即第一行数据的下标为 0。
  • 建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死。

🌙 4.2 limit 使用案例

  • 当前准备了如下 limit 子句的使用案例。
  1. 按 id 对成绩表进行分页，每页 3 行数据，分别显示第 1、2、3 页。
  2. 查询班级总分第一名的学生

1. 按 id 对成绩表进行分页，每页 3 行数据，分别显示第 1、2、3 页

  • 成绩表 exam_result 当前的表中数据如下：

image-20240814091939115

  • 查询第一页：从第 0 行数据开始向后筛选出 3 行数据 (即筛出下标为 0、1、2 这三行数据)。
    • 不要被主键 id 的数字影响。

image-20240814092347876

  • 查询第二页：从下标为 3 的记录开始，往后筛选出 3 条记录。

image-20240814092554933

  • 查询第三页：从下标为 6 的记录开始，向后筛选出 3 条记录。
    • 如果从表中筛选出的记录不足 n 个，则筛选出几个就显示几个。

image-20240814092724189

2. 查询班级总分第一名的学生

  • 在 select 中显示的列为 name 和 总分，在 order by 中按照三科总分降序 desc 排序，再使用 limit 中筛选出第一行数据。
    • 由于 order by 的执行顺序在 select 之后，因此可以使用 select 定义的对三科总分的别名。

image-20240814094502145

🌈 三、update 修改数据

⭐ 1. update 语法格式

UPDATE 表名 SET 列名1 = 值1 [, 列名2 = 值2, ..., 列名n = 值n] [WHERE ...] [ORDER BY ...] [LIMIT ...]
  • where 和 limit 是用来筛选出具体要修改的是哪几行，如果不加筛选条件，则默认是修改所有行的指定列。
  • 慎用能够对全表进行更新的语句。

⭐ 2. update 使用案例

  • 当前准备了如下 update 的使用案例。
  1. 将李四同学的数学成绩变更为 80 分。
  2. 将赵六同学的数学成绩变更为 60 分，语文成绩变更为 70 分。
  3. 将总成绩倒数前三的同学的数学成绩加上 30 分。
  4. 将所有同学的语文成绩变更为原来的 2 倍。

1. 将李四同学的数学成绩变更为 80 分

  • 在 where 子句中使用 name = ‘李四’ 找出该同学，在在 update 中使用 set math = 80 将数学成绩变更为 80 分。
    • 如果不设置像 where 这样的筛选条件，所有人的数学成绩都会被弄成 80。

image-20240814100557135

2. 将赵六同学的数学成绩变更为 60 分，语文成绩变更为 70 分

  • 在 where 子句中使用 name = ‘赵六’，在 update 总使用 set math = 60, chinese = 70。

image-20240814100828239

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

  • 找出倒数前三：在 order by 子句中将总成绩按照升序排序，再使用 limit 3 显示排序后的前 3 行数据.

image-20240814101801004

  • 将这 3 个家伙的数学成绩统统加上 30 分，即在 update 中使用 set math = math + 30。

image-20240814102307300

  • 这 3 个人数学各加了 30 分之后可能就不再是倒数前三了，因此再执行一次查询倒数前三的操作后，显示出来的数据可能会发生变化。

image-20240814102637155

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

  • 不加任何筛选条件，直接在 update 中使用 set chinese = chinese * 2 即可将所有人的语文成绩变成原来的 2 倍。
    • 慎用能够对全表进行更新的语句。

image-20240814103330485

🌈 四、delete 删除数据

⭐ 1. delete 语法格式

DELETE FROM 表名 [WHERE ...] [ORDER BY ...] [LIMIT ...];
  • 在删除数据前需要先找到要删除的的记录，delete 语句中的 where、order by 和 limit 子句就是用筛选要删除的数据的。
    • delete 删除的数据以行为单位。
  • 如果不添加筛选条件的话，默认就是删除整张表的数据。
    • 慎用能够对全表进行删除的语句。

⭐ 2. 删除指定记录

删除李四同学的考试成绩

  • 即将李四同学从 exam_result 成绩表中删除，在 where 子句中使用 name = ‘李四’ 筛选出李四所在的这一行数据。

image-20240814110441122

⭐ 3. 删除全表数据

  • 先创建一张名为 for_delete 的测试删除数据表，表中分别包含 id 和 name 两个字段。

image-20240814110824037

  • 往表中插入一些临时数据，用来后续对其进行删除。

image-20240814111213755

  • 在 delete 语句中只指定要删除数据的表名，而不指定筛选条件，即可删除整张表的数据。
    • 慎用能够对全表进行删除的语句。

image-20240814111415968

  • 如果之后再向该表中插入数据，但不指明自增长字段的值，会发现自增长 id 值是在之前的基础上继续增长的。

image-20240814113015451

  • 在查看 for_delete 的建表语句时会发现，有一个 AUTO_INCREMENT=n 的字段，该字段表示下一次插入数据时自增长字段的值应该为 n。
    • 使用 delete 删除整表数据时，不会重置 AUTO_INCREMENT=n 字段，因此删除整表数据后再插入自增长字段的值会在原基础上递增。

image-20240814113248274

⭐ 4. truncate 截断表

🌙 4.1 truncate 语法格式

TRUNCATE [TABLE] 表名;
  • 属于清空表数据的一种，在效果上和 delete 的清空表数据一致，但细节和原理上有所差别。
  • truncate 只能对整张表进行操作，不能像 delete 一样针对部分数据进行操作。
  • truncate 实际上是不对数据进行操作的，在清空数据方面，会比 delete 更快。
  • truncate 在删除数据的时候，不会经过真正的事务 (即不会将自己的操作记录在日志中)，因此无法对数据进行回滚。
  • truncate 会重置 AUTO_INCREMENT=n 这一项。

🌙 4.2 truncate 使用案例

  • 创建一张名为 for_truncate 的测试截断表，表中包含一个自增长的主键 id 和 name 这两个字段。

image-20240814114034686

  • 往表中插入一些临时数据，用来后续对其进行截断。

image-20240814114138157

  • 在 truncate 语句指定要截断的表名为 for_truncate，即可清空该表的数据。
    • 因为 truncate 不会对数据进行操作，因此执行 truncate 语句后看到影响行数为 0。

image-20240814114518236

  • 由于 truncate 会重置 AUTO_INCREMENT=n，再往表中重新插入数，可以看到自增长字段的值从 1 开始继续递增了。

image-20240814114940118

🌈 五、插入查询结果

⭐ 1. 语法格式

  • MySQL 也支持将对表的查询结果插入到另一张表中。
INSERT [INTO] 表名 [(列1 [, 列2, ..., 列n] ...)] SELECT ... [WHERE ...] [ORDER BY ...] [LIMIT ...];
  • 该语句的作用是将从其他表中筛选出来的数据插入到指定的表中。
  • 其中的列1 ~ 列n 表示将筛选出的记录的各个列插入到表中的指定列。

⭐ 2. 使用案例

  • 案例：删除表中重复的记录，让重复的数据只能有一份。

1. 准备工作

  • 创建一张名为 duplicate_table 的测试用表，表中包含 id 和 name 两个字段。

image-20240814152113815

  • 往表中插入一些包含重复数据的测试数据。

image-20240814152359259

2. 删除表中重复的数据

  • 创建一张临时的空表 no_duplicate_table，其表结构和 duplicate_table 一致。
    • 由于两张表的表结构相同，因此在创建临时表时可以借助 like。

image-20240814152950683

  • 将 duplicate_table 的数据使用 DISTINCT 去重后插入到 no_duplicate_table 表中。
    • 由于两张表的表结构一致，并且 select 进行的是全列查询，因此在插入时不用在表名后指定字段列表。

image-20240814153332563

  • 分别对两张表进行重命名操作，实现原子的去重操作。
    • 将 duplicate_table 测试表重命名为其他名字 (相当于对去重前的数据进行备份，如果不需要可以直接删除)；
    • 将 no_duplicate_table 临时表重命名为 duplicate_table 测试表的名字，此时便完成了对原始表数据的去重操作。

image-20240814154031963

🌈 六、聚合函数

select 函数名(参数) from 表名 [WHERE ...] [ORDER BY ...] [LIMIT ...];
  • 聚合函数能够对一组值进行计算，并返回单一的值。
  • 这些函数以查询出的记录为单位做聚合统计。

⭐ 1. 常见聚合函数

  • 聚合函数可以在 select 语句中使用，select 在每处理一条记录时，都会将对应的参数传递给这些聚合函数。
函数说明
COUNT ( [DISTINCT] expr )返回查询到的数据的数量
SUM ( [DISTINCT] expr )返回查询到的数字的总和，expr 参数如果不是数字则没有意义
AVG ( [DISTINCT] expr )返回查询到的数据的平均值，expr 参数如果不是数字则没有意义
MAX ( [DISTINCT] expr )返回查询到的数据的最大值，expr 参数如果不是数字则没有意义
MIN ( [DISTINCT] expr )返回查询到的数据的最小值，expr 参数如果不是数字则没有意义

⭐ 2. 聚合函数案例

  • 当前准备了如下聚合函数使用案例。
  1. 统计班级共有多少名同学。
  2. 统计班级收集的 qq 号有多少。
  3. 统计本次考试的数据成绩分数个数。
  4. 统计所有同学的数学成绩总分。
  5. 统计所有同学的三科总分的平均值。
  6. 返回英语成绩的最高分。
  7. 返回 > 80 分的的数学的最低分。

1. 统计班级共有多少名同学

  • 使用 * 统计：在 select 语句中，使用 count 函数时，可将 * 作为参数传递给它，用以统计表中有多少行数据。
    • 对学生表进行操作。

image-20240814174716385

  • 使用该表达式统计：在 select 语句中，使用 count 函数时，可将表达式作为参数传递给它，用以统计表中有多少行数据。

image-20240814174937573

  • 使用表达式做统计这种写法相当于在查询表中数据时，临时新增了一列名为对应表达式的列，用 count 函数统计该列中有多少行数据。
    • 在这里只是新增了一列数字 1，然后 count 统计的是这一列的 1 的个数。

image-20240814175230239

2. 统计班级收集的 qq 号有多少

  • 在 select 语句中，使用 count 函数统计 qq 那一列中数据的个数。
    • 由于 count 函数的参数是一个确定的字段名，因此 count 会自动忽略 null 值。

image-20240814175557461

3. 统计本次考试的数据成绩分数个数

  • 统计数学分数的个数：在 select 语句中，使用 count 函数统计 math 那一列中数据的个数。
    • 对成绩表进行操作。

image-20240814180130438

  • 统计数学分数的种类：即要执行去重操作，在传递给 count 函数的参数中，加上一个 distinct 即可。

image-20240814180534419

4. 统计所有同学的数学成绩总分

  • 统计数学成绩总分：在 select 语句中使用 sum 函数统计 math 这一列数据的总和。

image-20240814180805630

  • 统计不及格的数学成绩总分：在 where 子句种指定筛选条件为 math < 60，在 select 语句种使用 sum 函数统计 math 这一列被 where 子句筛选出来的的数据之后。

image-20240814181125486

5. 统计所有同学的三科总分的平均值

  • 在 select 语句中使用 avg 函数计算所有同学的 chinese + math + english 的平均值。

image-20240814181353445

6. 返回英语成绩的最高分

  • 在 select 语句中使用 max 函数 english 这一列中所有数据的最大值。

image-20240814181548165

7. 返回 > 80 分的的数学的最低分

  • 在 where 子句中指定筛选条件为 math > 80，然后在 select 语句中使用 min 函数取 math 这一列被筛选出来的数据的最小值。

image-20240814183633942

🌈 七、group by 分组查询

⭐ 1. 分组概念

  • 分组是指对表中的数据进行分组，分组的目的是为了方便聚合统计。

    • 例：根据性别将成绩分成两组，再对这两组数据分别进行聚合统计。

  • 指定列名，实际分组，是用所指定发的列的不同的行数据来进行分组的。

  • 分组就是将一张表按照指定条件分成了多个组，进行各自组内的统计。

  • 分组也被称为 “分表”，就是将一张表按照指定的条件再逻辑上拆分成了多个子表，然后再分队对各自的子表进行聚合统计。

    • 在 MySQL 中，一切皆表，只要能够处理好对一张表的增删查改，则所有的 sql 场景都能用统一的方式进行。

⭐ 2. group by 语法格式

select 字段列表 from 表名 [where 分组前过滤条件] group by 分组字段名 [having 分组后过滤条件];

⭐ 3. group by 使用案例

🌙 3.1 准备工作

1. 创建数据库

  • 创建一个名为 scott 的数据库，并将其设置成当前操作数据库。
DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott`;

USE `scott`;

2. 创建雇员信息表

  • 创建一张雇员信息表，表中包含三张表：员工表 (emp)、部门表 (dept)、工资等级表 (salgrade)。
  1. 部门表 dept 包含的字段有：部门编号 (deptno)、部门名称 (dname)、部门所在地 (loc)。
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`
(
    `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
    `dname`  varchar(14) DEFAULT NULL COMMENT '部门名称',
    `loc`    varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);
  1. 员工表 emp 包含的字段有：员工编号 (empno)、员工姓名 (ename)、员工职位 (job)、员工领导编号 (mgr)、雇佣时间 (hiredate)、月薪 (sal)、奖金 (comm)、部门编号 (deptno)。
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`
(
    `empno`    int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
    `ename`    varchar(10)   DEFAULT NULL COMMENT '雇员姓名',
    `job`      varchar(9)    DEFAULT NULL COMMENT '雇员职位',
    `mgr`      int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
    `hiredate` datetime      DEFAULT NULL COMMENT '雇佣时间',
    `sal`      decimal(7, 2) DEFAULT NULL COMMENT '工资月薪',
    `comm`     decimal(7, 2) DEFAULT NULL COMMENT '奖金',
    `deptno`   int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);
  1. 工资等级表 salgrade 包含的字段有： 等级 (grade)、该等级所对应的最低工资 (losal)、该等级所对应的最高工资 (hisal)。
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade`
(
    `grade` int(11) DEFAULT NULL COMMENT '等级',
    `losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
    `hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);

3. 插入数据

  • 往部门表 dept 中插入数据：
insert into dept (deptno, dname, loc) values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc) values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc) values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc) values (40, 'OPERATIONS', 'BOSTON');
  • 往员工表 emp 中插入数据：
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);
  • 往 工资等级表 salgrade 中插入数据：
insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

🌙 3.2 使用案例

  • 当前为 group by 准备了如下两种案例
  1. 显示每个部门的平均工资和最高工资。
  2. 显示每个部门的每种岗位的平均工资和最低工资。
  3. 显示不包含员工名为 SMITH 在内的每个部门的每种岗位最高和最低工资。

1. 显示每个部门的平均工资和最高工资

  • 在员工表 emp 中查询：在 group by 子句中指定按照部门编号 deptno 进行分组。在 select 语句中使用 avg 函数和 max 函数，然后查询筛选出来的所有分组的平均工资和最高工资。

image-20240814195709524

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

  • 在员工表 emp 中查询：在 group by 子句中指定按照部门编号 deptno 先分成多个大组，再按照岗位 job 将这些大组划分成多个小组。在 select 语句中使用 avg 函数和 min 函数，然后查询筛选出来的所有小组的平均工资和最低工资。

image-20240814200352287

3. 显示不包含员工名为 SMITH 在内的每个部门的每种岗位最高和最低工资

  • 在 where 子句中指定员工姓名 ename != ‘SMITH’。在 group by 子句中指定按照部门编号 deptno 分大组，再按照岗位 job 分小组。在 sleect 语句中使用 max 和 min 求每个小组的薪水 sal 最大最小值。

image-20240814205933856

⭐ 4. having 条件

SELECT ... FROM table_name [WHERE ...] [GROUP BY ...] [HAVING ...] [order by ...] [LIMIT ...];
  • 在 having 子句中，可以指定 1 ~ n 个筛选条件。

🌙 4.1 having 使用案例

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

  1. 统计每个部门的平均工资：在 group by 子句中指定按照部门编号 deptno 进行分组。在 select 语句中使用 avg 函数查询每个分组的平均工资。
  2. 通过 having 子句筛选出平均工资低于 2000 的部门：在 having 子句中指定筛选条件为 avg(sal) < 2000。

image-20240814203016846

🌙 4.2 where 和 having 的区别

  1. where 子句放在表名之后，而 having 子句必须搭配 group by 子句使用，放在group by 子句之后。

  2. where 子句是具体的任意列进行条件筛选，而 having 子句是对分组聚合后的数据进行条件筛选。

  3. where 子句中不能使用聚合函数和别名，而 having 子句中可以使用聚合函数和别名。

  4. where 的执行顺序在分组之前，而 having 的执行顺序在分组之后。

♬咖啡猫♬
关注
  • 15
    点赞
  • 踩
  • 13
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Spring Boot入门(09)：如何使用MyBatis的XML配置方式实现MySQL的增删改查操作？
**My Coding Family**
04-17 1万+
如何使用MyBatis的XML配置方式访问MySQL实现增删改查，一文教会你...
基于QT+Mysql的欢乐斗地主（源代码+项目思维导图+笔记）-计算机专业精品毕业设计和课程设计
05-21
开发者需要设计合理的数据库结构，创建表单并编写SQL语句进行数据的增删查改操作。同时，为了确保数据安全性和并发处理能力，还需要考虑事务处理和索引优化。 在【斗地主-流程图.pdf】中，可以了解到游戏的整体流程...
【MySQL】表的增删查改
加油，考上985的研究生
05-20 1236
CRUD：Create（创建），Retrieve（读取），Update（更新），Delete（删除）
【MySQL 03】库的操作 (带思维导图)
shangguanxiu的博客
08-10 1146
对数据库的增删查改
MySql之表的增删改查
2401_84048935的博客
04-04 714
1.创建表：create 修改表：alter 删除表：drop2.对表的相关操作DDL语言三、操作数据1：数据操作语言2：数据操作语言：DML Data modify language3:DQL:查询语句DDL：建表相关语句 create alter dropDML:数据操作语言数据插入 insert 数据修改update 数据删除delete4：数据库中的增删改查(CRUD) create read update delete增：insert into删：delete from。
【python教程】保姆版教使用pymysql模块连接MySQL实现增删改查
flyskymood的博客
04-09 8449
文章目录🌈前言 🌈前言 MySQL 是最流行的数据库之一，MySQL 是目前世界上最流行的开源关系数据库，大多应用于互联网行业。比如，在国内，大家所熟知的百度、腾讯、淘宝、京东、网易、新浪等，国外的 Google、Facebook、Twitter、GitHub 等都在使用 MySQL。社交、电商、游戏的核心存储往往也是 MySQL， 本文就简单的介绍python操作MySQL基础内容。 ...
springboot整合mybatis实现增删改查
热门推荐
学Java，找哪吒
10-15 3万+
Java框架总结Spring AOP基础知识总结Spring常用注解（绝对经典）SpringMVC中put和post如何选择@RequestParam、@ModelAttribute、@RequestBody的区别mybatis常用注解（绝对经典）【MyBatis基础知识总结1】SQL注入【MyBatis基础知识总结【MyBatis基础知识总结3】MyBatis一级缓存和二级缓存【MyBatis 基础知识总结 4】动态sql。
一篇文章搞定数据库所有增删改查操作！！
Dong's Blog
07-08 1053
数据库的增删改查操作，一看就会，简单易懂
Python项目：学生管理系统连接MySQL数据库（增删改查、排序、统计、显示所有信息）
Argonaut_的博客
12-28 7308
文章目录一程序功能（Function）1. 功能概述2. 思维导图二.开始实践战前准备定义学生管理系统菜单定义主函数1.录入学生信息2.查找学生信息3.删除学生信息4.修改学生信息5.排序6.统计总人数7.显示学生所有信息 一程序功能（Function） 1. 功能概述 利用Python完成一个关于MySQL数据库的学生信息的增删改查程序，主要涉及知识点：游标的运用、SQL语句以及之前学习的基础知识。 2. 思维导图 想法已有开始实践 二.开始实践 战前准备 你需要先导入sqlite3 模块与pymy
Mysql总结(附思维导图)
总结日常学习内容
03-18 869
总结mysql学习的知识
MySQL个人学习笔记
04-10
囊括了ddl、dml、dcl、dql、函数、约束、事务、索引、各种查询语句，增删查改等等，以及一些概念性的解释和示例使用方法 适合想学习MySQL的初学者或者用来查询想使用的语句格式或者拿来复习温习也是很不错的选择，...
MySQL的初步认识
2301_80706853的博客
08-14 319
MySQL的初步认识
MySQL中处理JSON数据:大数据分析的新方向
最新发布
Chujun123528的博客
08-18 590
灵活性：JSON数据类型允许你在单个列中存储复杂的数据结构，如数组和嵌套对象，这减少了需要创建多个表来存储相关信息的必要性。查询能力：MySQL为JSON数据类型提供了一系列内置的函数，如, , , , , 等，使得查询和修改JSON数据变得非常便捷。索引支持：MySQL支持为JSON文档中的特定路径创建虚拟列（Generated Columns），然后可以在这些虚拟列上创建索引，从而加速基于JSON内容的查询。性能优化：随着MySQL的不断改进，对JSON数据的处理性能也在不断提高。虽然与专门的NoSQL
MySQL源码安装与MySQL基础学习
2401_84618124的博客
08-15 247
​ 本次安装使用的是绿色硬盘版本，无需额外安装依赖环境，比较简单。：条件的筛选，可以跟多个条件，可用and或or连接，：表示0个或多个字符。：修改表名和修改表结构。
MySQL——单表查询（二）按条件查询（5）带 DISTINCT 关键字的查询
PAP
08-17 336
MySQL——单表查询（二）按条件查询（5）带 DISTINCT 关键字的查询
MySQL系统变量innodb_autoinc_lock_mode
zxrhhm的博客
08-14 769
`innodb_autoinc_lock_mode` 是 MySQL InnoDB 存储引擎的一个系统变量，它控制着自增（AUTO_INCREMENT）值的锁定机制。
MySQL中的EXPLAIN的详解
SHI940637335的博客
08-17 728
是一种优化技术，允许 MySQL 在存储引擎层面应用部分。
【MySQL】什么是索引？了解索引的底层原理
yican2580的博客
08-13 1042
详解MySQL中索引的概念，介绍索引的底层原理。
jsp+servlet+jdbc+mysql增删改查思维导图
12-15
JSP是一种用于开发动态Web页面的技术，Servlet是用于处理HTTP请求和生成HTTP响应的Java程序，JDBC是Java语言访问数据库的接口，MySQL是一种常用的关系型数据库管理系统。这四个技术在Web开发中经常会一起使用，用于实现对数据库的增删改查操作。 首先，我们可以用一个主题为“数据库操作”的根节点来概括整个思维导图。接着，以不同的分支分别表示增、删、改、查四种操作。在增加数据的分支下，我们可以列出JSP页面中的表单，Servlet中的后台处理代码，以及JDBC连接数据库并执行插入操作的流程。对于删除数据的分支，可以包括JSP页面上的删除按钮，Servlet中的删除逻辑，以及JDBC执行删除操作的过程。而在修改数据的分支下，可以包括JSP页面上的修改表单，Servlet中的更新逻辑，以及JDBC执行更新操作的过程。最后，在查询数据的分支下，可以列出JSP页面上的查询条件输入框，Servlet中的查询处理逻辑，以及JDBC执行查询操作的流程。 在每个分支的具体操作步骤中，可以再细化为更具体的子步骤，如JSP页面的设计，Servlet中的逻辑处理，以及JDBC对数据库的操作。通过这样的思维导图，我们可以清晰地了解到整个增删改查的流程，以及各个技术在其中的作用和关联。这个思维导图可以帮助开发者更好地理解和掌握使用JSP、Servlet、JDBC和MySQL进行数据库操作的全过程。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值