【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服务器中存在的数据库也可以是不存在的数据库。
• 若数据库不存在操作临时表时必须使用“数据库.临时表”指定临时表所在的数据库。
• 临时表中的数据操作与普通表相同都可以进行SELECTINSERTUPDATE和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添加记录操作

4TRUNCATE与DELETE操作结果对比

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

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

5TRUNCATE与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.1COUNT函数

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.2MIN和MAX函数

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

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

注意点说明

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

---

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

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

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

---

5.3SUM和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.4GROUP_CONCAT函数

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

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

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

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

---

5.5JSON_ARRAYAGG函数

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

---

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

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

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

---

5.6JSON_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表设置别名为sclass表设置别名为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中的单表操作的相关知识有了全新地认识和理解吧如果有什么其他的问题无法自己解决可以在评论区留言哦

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

---