【MySQL基础】MySQL单表操作详解

序号	系列文章
2	【MySQL基础】MySQL基本操作详解
3	【MySQL基础】MySQL基本数据类型
4	【MySQL基础】MySQL表的七大约束
5	【MySQL基础】字符集与校对集详解

---

---

前言

大家好，我是小杨！今天我将详细的为大家介绍MySQL中单表操作的相关知识，希望大家能够从中收获多多！

---

MySQL单表操作

1，数据操作

1.1，复制表结构和数据

1）复制已有的表结构

在开发时，需要创建一个与已有数据表相同的表结构的数据表时，可以通过下述语法来完成表结构的复制。

基本语法格式：

#复制已有的表结构法1：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名 LIKE 旧表名; 

#复制已有的表结构法2：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名(LIKE 旧表名); 

语法说明：

• 该方法仅能从旧表名中复制一份相同的表结果，而不会复制表中保存的数据。
• 完成创建步骤后可以利用 “SHOW CREATE TABLE 表名” 查看新数据表的表结构。

---

为了更好理解已有的表结构复制操作，现举例说明：

---

2）复制已有的表数据

数据复制也可称为蠕虫复制，是新增数据的一种方式，它是从已有的数据表中获取数据，并将此数据插入到对应的数据表中。

此种方式获取数据与插入数据的数据表的表结构要相同，否则可能会出现插入不成功的情况。

复制已有的表数据的语法格式：

INSERT [INTO] 数据表名1 [(字段列表)] SELECT [(字段列表)] FROM 数据表名2;

语法说明：

• 数据表名1是数据复制到的数据表（目的地）；数据表名2是复制的数据的数据来源（出发点）。
• 数据表1和数据表2通常使用同一个数据表，从而在短期内快速添加表的数据量，测试表的压力及效率等。
• 若数据表中含有主键，而主键具有唯一性，所以在数据复制时还要考虑主键冲突问题。若重复添加数据，系统会报主键重复的错误。

---

为了更好理解已有的表数据复制操作，现举例说明：

---

3）扩展：临时表的使用

临时表指的是一种仅在当前会话中可见，并在当前会话关闭时自动删除的数据表，主要用于临时存储数据。

临时表创建的语法格式：

#创建临时表方法1：
CREATE TEMPORARY TABLE 表名(字段名 字段类型);

#创建临时表方法2：
CREATE TEMPORARY TABLE 表名 SELECT (字段列表) FROM 已有数据表的表名;

语法说明：

• 创建临时表时指定的数据库可以是MySQL服务器中存在的数据库，也可以是不存在的数据库。
• 若数据库不存在，操作临时表时必须使用“数据库.临时表”指定临时表所在的数据库。
• 临时表中的数据操作与普通表相同，都可以进行SELECT，INSERT，UPDATE和DELETE操作。
• SHOW TABLES 不能查看指定数据数据库中有哪些临时表，并且临时表的表名必须使用ALTER TABLE 修改，而不能使用RENAME TABLE …

---

为了更好理解临时表的使用，现举例说明：

---

1.2，解决主键冲突

1）主键冲突

当你在数据库表中设置一字段为主键时，这意味着该字段的值是唯一的，不能重复。如果你试图在表中插入一条新记录，并且主键字段的值已经存在，就会发生主键冲突，进而导致你试图插入的新记录无法成功插入。

例如，假设你有一张学生信息表，其中有个名为"id"的字段被设为主键时，那么在表中不能有两条记录的id值相同。如果你试图插入一条新记录，其id值已经存在于表中，就会发生主键冲突，进而导致记录插入失败。

---

为了更好理解何为主键冲突，现举例说明：

1，学生表准备

2，主键冲突演示

3，主键冲突的解决

如果你在尝试向数据库表中插入一条新记录时遇到主键冲突问题，若要解决这问题，可以使用MySQL所提供了两种方式，即为主键冲突更新和主键冲突替换。

---

2）主键冲突更新

主键冲突更新是指当插入数据时发生主键冲突，"更新"操作会在表中查找发生主键冲突的记录，然后用新记录中的值更新该记录中的值。

INSERT [INTO]  表名称 [字段列表] {VALUE|VALUES} (值列表) 
ON DUPLICATE KEY UPDATE 字段名1 = 新值1 [,字段名2 = 新值2, ...];

---

3）主键冲突替换

主键冲突替换是指当插入数据时发生主键冲突，”替换“操作会在表中查找发生主键冲突的记录，然后将该记录完全删除，并用新记录代替

REPLACE [INTO] 表名称 [字段列表] {VALUE|VALUES} (值列表);

注意：两种操作都可以用来解决主键冲突，但是使用"更新"操作时，可以保留原有记录中的部分值，而使用"替换"操作时会完全删除原有记录。因此，在选择哪种操作时，需要考虑自己的需求。

---

1.3，清空数据

MySQL中不仅可以使用DELETE语句进行删除数据，还可以使用TRUNCATE清空指定表的全部数据，其基本语法格式如下：

#清空数据法1:
DELETE FROM 表名称;

#清空数据法2:
TRUNCATE [TABLE] 表名称;

---

为了更好理解这两种清空数据方法的使用，现举例说明：

1，操作表准备

2，删除数据操作

---

扩展：TRUNCATE与DELETE的区别

尽管TRUNCATE操作与DELETE操作在使用来清空数据时非常相似，但这两者之间存在本质区别。

区别归纳如下：

1. 实现方式不同：TRUNCATE本质上先执行删除（DROP）数据表的操作，然后再根据有效的表结构文件（.frm）重新创建数据表的方式来实现数据清空操作；而DELETE语句则是逐条的删除数据表中保存的记录。
2. 执行效率不同：在针对大型数据表（如千万级的数据记录）时，TRUNCATE清空数据的实现方式，决定了它比DELETE语句删除数据的方式执行效率更高。
3. 对自增约束的字段影响不同：TRUNCATE清空数据后，再次向表中添加数据，自动增长字段会从默认的初始值重新开始，而使用DELETE语句删除表中的记录时，则不影响自动增长值。
4. 删除数据的范围不同：TRUNCATE语句只能用于清空表中的所有记录，而DELETE语句可通过WHERE指定删除满足条件的部分记录。
5. 返回值含义不同：TRUNCATE操作的返回值一般是无意义的，而DELETE语句则会返回符合条件被删除的记录数。
6. 所属SQL语言的不同组成部分：DELETE语句属于DML数据操作语句，而TRUNCATE通常被认为是DDL数据定义语句。

---

为了更好的理解这两者的区别，实例如下：

1，操作表准备

2，删除数据操作

3，添加记录操作

4，TRUNCATE与DELETE操作结果对比

TRUNCATE与DELETE语句在删除数据的区别具体如下：

操作	返回值	id(插入字段增长值)	执行效率
TRUNCATE	0 rows affected	1	0.01s
DELETE	10rows affected	11	0.00s

5，TRUNCATE与DELETE操作说明

• TRUNCATE的返回值表示有0条记录受影响，而DELETE的返回值表示有10条记录受影响；说明这两种方法的实现操作不同。
• 删除数据记录后，再次新增数据记录，经TRUNCATE操作删除的表id从1开始，而经DELETE操作删除的表id从11开始,说明它们对自增约束的字段影响不同。
• TRUNCATE与DELETE执行清空数据操作的时间不同，说明这两种方法的执行效率不同。
  • 一般情况下，针对大型数据表（如千万级的数据记录）时，使用TRUNCATE清空数据的执行效率高于使用DELETE清空数据的执行效率。
  • 一般情况下，针对小型数据表（如上述实例的数据记录）时，使用DELETE清空数据的执行效率高于使用TRUNCATE清空数据的执行效率。
  • 因此，在实际开发中具体使用何种方式进行删除数据操作，需要根据实际需求进行合理的选择。

---

1.4，去除重复记录

有时出于对数据记录的分析需求，需要去除查询记录中的重复记录，例如，想查看班级学生的民族种类数，就需要去除重复的民族记录。

MySQL中提供使用SELECT 语句的选项进行去除重复记录操作，其基本语法如下：

SELECT select选项 字段列表 from 表名称;

语法说明：

1. select选项默认为ALL，表示保存所有查询到的数据记录。
2. 当select选项设置为DISTINCT时，表示去除重复数据记录，只保留一条数据记录。
3. 当查询记录的字段有多个时，必须所有的字段的值完全相同时才会被认为是重复记录。

---

为了更好地理解去除重复记录操作的使用，举例如下：

---

2，排序

在实际开发时，为了使查询到的数据结果满足用户的需求，通常会对查询到的数据进行升序或者降序的排序方式。

例如，用户在进行网络购物的时候，通常会对想买的商品数据进行排序的处理，例如以商品销量或者以商品的综合评价进行排序，让满足要求的商品数据放在前面，方便用户进一步操作。

在MySQL中，针对不同的开发需求提供了两种排序的方式，分别为单字段排序和多字段排序。

---

2.1，单字段排序

单字段排序指的是在查询时仅按照一个指定字段对查询到的数据进行升序或者降序排序。其基本语法如下：

SELECT * | {字段列表} FROM 表名称 ORDER BY 字段名 [ASC|DESC];

语法注意事项：

1. ASC 表示 升序 ，DESC 表示 降序，而ORDER BY 默认为 ASC ，即对表数据进行排序操作，默认升序。

---

为了更好地理解单字段排序，实例如下：

---

2.2，多字段排序

多字段排序指的是在查询时按照多个指定字段对查询到的数据进行升序或者降序排序。其基本语法如下：

SELECT * | {字段列表} FROM 表名称 ORDER BY 字段名1 [ASC|DESC],字段名2 [ASC|DESC], ...;

语法注意事项：

1. 多字段排序先按照字段1对数据进行排序操作，当表中存在字段1值相同的记录时，再按照字段2对这些记录进行排序，依次类推。
2. 在按照指定字段进行排序操作时，如果某条记录的字段值为NULL，那么系统会将NULL看作是最小的值，从而将其显示在查询结果中的首条位置或者末尾位置。

---

为了更好地理解多字段排序，实例如下：

---

扩展：中文字段排序

1，面临问题

在默认情况下 ，MySQL 使用的字符集是拉丁字符集，因此对于中文字符串的排序，默认不会按照中文拼音的顺序排序，而是以区位码来进行排序中文字符串，进而出现不同于你所期望的情况。

2，问题缘由

区位码可以用来对中文字符串进行排序，但并不是每个字符的区位码都是按照字母顺序排列的。

在 Unicode 编码系统中，中文字符的区位码是按照笔画顺序排列的，而不是按照字母顺序。

例如，字符 的 的区位码是 U+7684，字符 一 的区位码是 U+4E00，所以在使用区位码排序中文字符串时，的 会排在 一 的前面。

3，解决方案

为了避免这种情况，你可以使用 utf8mb4 字符集，或者使用拼音排序算法。

在不改变数据表结构的前提下，可以使用 MySQL 的 ORDER BY CONVERT(字段名 USING gbk) 函数来强制让指定的字段按照中文拼音顺序进行排序。

---

3，限量

3.1，数据查询的排序与限量

对于一次性查询出的大量数据记录，不仅不便于阅读查看，还会浪费系统资源。

为了解决上述问题，MySQL中提供了一个关键字LIMIT，不仅可以限定记录的数据，还可以指定查询记录从哪条记录开始。

SELECT [select选项] {*|字段列表} FROM 表名 [WHERE 条件表达式] [ORDER BY 字段 ASC|DESC] LIMIT [OFFEST,] 记录数;  

语法说明：

1. 记录数表示限定获取的最大记录数量，就相当于在记录数大于数据表中符合要求的实际记录数时，以实际记录数为准。
2. OFFSET表示偏移量，用于设置从哪条数据记录开始获取。MySQL中默认第1条数据的偏移量值为0，依次类推。
3. LIMIT后仅含记录数，无OFFSET偏移量时，表示从数据表中的第1条数据开始获取。

---

为了更好地理解数据查询时的排序与限量使用，实例如下

1，成绩表准备

---

实例：查找该学生成绩表中的成绩前三名的学生的全部信息。

2，限量记录数

SELECT * FROM student ORDER BY score DESC LIMIT 3;

---

3，获取指定区间记录

SELECT * FROM student ORDER BY score DESC LIMIT 0,3;

---

3.2，数据更新的排序与限量

数据更新的排序与限量，基本语法：

UPDATE 表名称 SET 字段 = 新值,... [WHERE 条件表达式] ORDER BY 字段 [ASC|DESC] LIMIT [OFFEST,] 记录数;  

语法说明：

1. 数据更新操作时使用限量，只会对限量的数据进行更新操作，其它的数据不进行更新操作。

---

为了更好地理解数据更新时的排序与限量使用，实例如下

实例：将前三名的成绩都减少5，求成绩更新后成绩为前三的学生信息

UPDATE student SET SCORE = SCORE - 5 ORDER BY score DESC LIMIT 3;

UPDATE student SET SCORE = SCORE - 5 ORDER BY score DESC LIMIT 0,3;

数据更新前后的表数据（降序排序）区别：

---

3.3，数据删除的排序与限量

数据删除的排序与限量，基本语法：

DELETE FROM 表名称 [WHERE 条件表达式] ORDER BY 字段 [ASC|DESC] LIMIT [OFFEST,] 记录数;  

语法说明：

1. 数据删除操作时使用限量，只会对限量的数据进行删除操作，其它的数据不进行删除操作。

---

为了更好地理解数据删除时的排序与限量使用，实例如下

实例：将前三名的成绩删除，求成绩删除后成绩为前三的学生信息

数据删除前后的表数据（降序排序）区别：

---

4，分组

在MySQL中，可以对数据记录进行分组操作。也就是可根据一个字段或者多个字段对记录进行分组，字段值相同的记录为一组。

为了能够更好地理解分组的相关操作及使用，接下来将对其进行逐一讲解。

1，操作表的准备

---

4.1，分组统计

在查询数据时，在WHERE条件后添加GROUP BY就可以根据指定的字段对记录进行分组操作，基本语法如下：

SELECT [select选项] 字段列表 FROM 表名称 [WHERE 条件表达式] GROUP BY 字段名;

语法注意事项：

1. SELECT获取的字段列表只能是GROUP BY 分组的字段，或使用了聚合函数的非分组字段。
2. 若在获取非分组字段时未使用聚合函数，MySQL会发报错信息。
3. 在一些老版本中，分组后获取的字段列表，若非分组字段未使用聚合函数，默认情况下只保留每组中的第一条记录。

---

为了更好地理解分组统计的使用，实例如下：

实例：查询每个成绩分值的所占人数

SELECT score,COUNT(*) FROM score GROUP BY score;

---

4.2，分组排序

在MySQL中，默认情况下为分组操作的字段提供了升序排序的功能，在对记录进行分组操作时，可以为指定的字段进行升序和降序排序，其基本语法如下：

SELECT [select选项] 字段列表 FROM 表名称 [WHERE 条件表达式] GROUP BY 字段名 [ASC|DESC];

语法注意事项：

1. GROUP BY 分组排序的实现不需要使用ORDER BY，直接在分组字段后添加ASC(升序，默认可省略)，DESC(降序)即可。

---

为了更好地理解分组排序的使用，实例如下：

实例：查看各个成绩段的学生信息，成绩由高到低排序

SELECT score.GROUP_CONCAT(name) FROM score GROUP BY score DESC;

---

4.3，多分组统计

在对数据进行分组统计时，MySQL还支持数据按照某一个字段进行分组后，对已经分组的数据进行再次分组的操作，从而实现对数据的多分组统计。其基本语法如下：

SELECT [select选项] 字段列表 FROM 表名称 [WHERE 条件表达式] GROUP BY 字段名1 [ASC|DESC],字段名2 [ASC|DESC],...;

语法注意事项：

1. 查询出的数据首先按照字段1进行分组排序，再将字段1相同的记录再按照字段2进行分组排序，依次类推。

---

为了更好地理解多分组统计的使用，实例如下：

实例：查看各组学生的成绩，成绩由高到低，小组号由低到高

SELECT group_id,score FROM score GROUP BY group_id ASC, score DESC;

---

4.4，回溯统计

对数据进行分组统计时，MySQL还支持回溯统计功能。回溯统计可简单理解为：在根据指定字段进行分组后，系统会自动对分组的字段进行一次新的统计并产生有关新的统计数据，且对应的分组字段值为NULL。其基本语法如下：

SELECT [select选项] 字段列表 FROM 表名称 
[WHERE 条件表达式] GROUP BY 字段名1 [ASC|DESC],字段名2 [ASC|DESC],...WITH ROOLLUP;

语法注意事项：

1. 分组操作根据GROUP BY 后的字段从前往后一次执行，数据分组后系统再进行回溯统计，它与分组操作正好相反。
2. 回溯统计从GROUP BY 后最后一个指定的分组字段开始进行回溯统计，并将结果上报，然后根据上报结果依次向前一个分组字段进行回溯统计。
3. MySQL中的同一个查询语句中回溯统计（WITH ROLLUP）与排序（ORDER BY）仅能出现一个，不能同时存在。

---

为了更好地理解回溯统计的使用，实例如下：

实例：

SELECT score,group_id,COUNT(*) FROM score GROUP BY group_id ASC,score DESC WITH ROLLUP;

---

4.5，统计筛选

在对查询到的数据进行分组统计时，还可以利用HAVING根据条件进行数据的筛选操作。其基本语法为：

SELECT [select选项] 字段列表 FROM 表名称 
[WHERE 条件表达式] GROUP BY 字段名1 [ASC|DESC],字段名2 [ASC|DESC],...[WITH ROOLLUP] HAVING 条件表达式;

语法注意事项：

1. 通常情况下，HAVING与GROUP BY 一起使用，对分组后的结果进行筛选操作。
2. 执行流程为先对查询到的数据进行分组操作，再对分组后的数据进行筛选操作。
3. 虽然WHERE操作和HAVING操作都能起到对数据进行筛选功能，但要知道和理解这两者的区别。
   • WHERE操作是从数据表中获取数据，将数据从磁盘存储到内存中进行操作，而HAVING则是对已存储到内存的数据进行操作。
   • WHERE和HAVING所处位置不同。WHERE位于GROUP BY子句之前，而HAVING位于GROUP BY子句之后。
   • WHERE后不可以使用聚合函数，而HAVING后可以使用聚合函数。

---

为了更好地理解统计筛选的使用，实例如下：

实例：查询小组平均分高于85分的小组

SELECT group_id,AVG(score) FROM score GROUP BY group_id HAVING AVG(score)>=85;

---

5，聚合函数

在MySQL中，MySQL所提供的聚合函数在查询数据时能够起一些特殊功能，查询出更有价值的数据记录。

常用的聚合函数

函数名	描述	运算符小写
COUNT()	返回参数字段的数量，不统计NULL的值	count()
SUM()	返回参数字段之和	sum()
AVG()	返回参数字段的平均值	avg()
MAX()	返回参数字段的最大值	max()
MIN()	返回参数字段的最小值	min()
GROUP_CONCAT()	返回符合条件的参数字段的连续字符串	group_concat()
JSON_ARRAYAGG()	将符合条件的参数字段作为单个JSON数组返回，MySQL5.7.22新增	json_arrayagg()
JSON_OBJECTAGG()	将符合条件的参数字段作为单个JSON对象返回，MySQL5.7.22新增	json_objectagg()

为了能够更好地理解上述各个聚合函数的使用，接下来将对其进行逐一讲解。

1，操作表的准备

---

5.1，COUNT函数

SELECT COUNT(*) FROM 表名称;      //返回表中的总记录数

SELECT COUNT(字段名) FROM 表名称;  //返回表中字段不为null的记录数 

注意点说明：

1. COUNT()函数适用于任何的数据类型。
2. 区分COUNT(1)，COUNT(*)，COUNT(字段名) 三者的区别，前两种直接读取行数，而第一种还要判断记录的字段值是否为NULL。
3. COUNT( *)不能使用COUNT(字段名)代替，COUNT(*)会统计值为NULL的记录，而COUNT(字段名)不会统计该列值为NULL的记录。

---

为了能够更好地理解COUNT函数的使用，实例如下：

实例：查询各个小组的小组成员人数

SELECT group_id,COUNT(*) FROM score GROUP BY group_id;

---

5.2，MIN和MAX函数

SELECT MAX(字段名) FROM 表名称;   //返回表中该字段的最大值

SELECT MIN(字段名) FROM 表名称;   //返回表中该字段的最小值

注意点说明：

1. MIN()和MAX()函数适用于任何的数据类型。

---

为了能够更好地理解MIN和MAX函数的使用，实例如下：

实例：查询学生成绩的最大值及最小值

SELECT MAX(score),MIN(score) FROM score;

---

5.3，SUM和AVG函数

SELECT SUM(字段名) FROM 表名称;    //返回表中该字段的总和值

SELECT AVG(字段名) FROM 表名称;    //返回表中该字段的平均值

注意点说明：

1. SUM()和AVG()函数只适用于数值型数据类型。

---

为了能够更好地理解SUM和AVG函数的使用，实例如下：

实例：查询各个小组学生成绩的总和及平均值

SELECT group_id,SUM(score),AVG(score) FROM score GROUP BY group_id;

---

5.4，GROUP_CONCAT函数

SELECT GROUP_CONCAT(字段名) FROM 表名称;   //返回符合条件的参数字段的连续字符串

为了能够更好地理解JSON_ARRAYAGG函数的使用，实例如下：

实例：查询各个小组的学生姓名信息

SELECT group_id,GROUP_CONCAT(name) FROM score GROUP BY group_id;

---

5.5，JSON_ARRAYAGG函数

SELECT JSON_ARRAYAGG(字段名) FROM 表名称;           //将符合条件的参数字段作为单个JSON数组返回

---

为了能够更好地理解JSON_ARRAYAGG函数的使用，实例如下：

实例：查询各个小组的学生姓名信息

SELECT group_id,JSON_ARRAYAGG(name) FROM score GROUP BY group_id;

---

5.6，JSON_OBJECTAGG函数

SELECT JSON_OBJECTAGG(键名字段,键值字段) FROM 表名称;            //将符合条件的参数字段作为单个JSON对象返回

---

为了能够更好地理解JSON_ARRAYAGG函数的使用，实例如下：

实例：查询各个小组的学生姓名及成绩信息

SELECT group_id,JSON_OBJECTAGG(name,score) FROM score GROUP BY group_id;

---

6，扩展:别名

在MySQL中，执行查询操作时可以为获取的字段设置别名，从而来缩短字段的名称长度和方便阅读开发。

6.1,字段别名设置及使用

为字段设置别名的基本语法如下：

SELECT 字段1 [AS] 字段别名1, 字段2 [AS] 字段别名2, ... FROM 表名称;

语法注意事项：

1. AS可以省略，用空格代替。
2. AS用于为前面的字段，函数，表达式等设置别名。
3. 字段别名设置后，在操作时仍可以使用原来的字段名称。

---

为了更好地理解字段别名设置及使用，实例如下：

实例：查询各个小组的成绩最高分，将MAX(score)该字段设置别名为max_score

SELECT group_id,MAX(score) max_score FROM score GROUP BY group_id;

---

6.2，表别名设置及使用

为表设置别名的基本语法如下：

SELECT 字段列表 FROM 表名称 [AS] 表别名;

语法注意事项：

1. AS可以省略，用空格代替。
2. 表别名设置后，在操作时仍可以使用原来的表名称。
3. 表别名的设置及使用主要用于多表查询中。

---

为了更好地理解表别名设置及使用，实例如下：

1，操作表准备

2，实例操作

实例：查询学生信息(学号，姓名，班级号)，将student表设置别名为s，class表设置别名为c

SELECT s.id stu_id,s.name stu_name,c.name AS class_name FROM student s,class c WHERE s.class_id = c.id;

---

结语

这就是本期博客的全部内容啦，想必大家已经对MySQL中的单表操作的相关知识有了全新地认识和理解吧，如果有什么其他的问题无法自己解决，可以在评论区留言哦！

最后，如果你觉得这篇文章写的还不错的话或者有所收获的话，麻烦小伙伴们动动你们的小手，给个三连呗（点赞👍，评论✍，收藏📖），多多支持一下！各位的支持是我最大的动力，后期不断更新优质的内容来帮助大家，一起进步。那我们下期见！

---