数据库约束-数据可设置约束图

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

数据操作复制表结构和数据

1）复制已有的表结构

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

基本语法格式：

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

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

语法说明：

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

2）复制已有的表数据

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

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

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

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

语法说明：

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

3）扩展：临时表的使用

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

临时表创建的语法格式：

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

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

语法说明：

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

解决主键冲突

1）主键冲突

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

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

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

1，学生表准备

2，主键冲突演示

3，主键冲突的解决

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

主键冲突更新

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

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

主键冲突替换

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

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

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

清空数据

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

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

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

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

1，操作表准备

2，删除数据操作

扩展：TRUNCATE与DELETE的区别

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

区别归纳如下：

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

1，操作表准备

2，删除数据操作

3，添加记录操作

4，TRUNCATE与DELETE操作结果对比

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

5，TRUNCATE与DELETE操作说明

去除重复记录

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

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

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

语法说明：

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

排序

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

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

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

单字段排序

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

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

语法注意事项：

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

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

多字段排序

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

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

语法注意事项：

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

扩展：中文字段排序

1，面临问题

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

2，问题缘由

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

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

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

3，解决方案

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

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

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

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

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

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

语法说明：

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

1，成绩表准备

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

2，限量记录数

SELECT * FROM student ORDER BY score DESC LIMIT 3;

3数据库约束，获取指定区间记录

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

数据更新的排序与限量

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

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

语法说明：

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

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

实例：将前三名的成绩都减少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;

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

数据删除的排序与限量

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

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

语法说明：

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

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

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

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

分组

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

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

1，操作表的准备

分组统计

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

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

语法注意事项：

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

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

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

分组排序

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

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

语法注意事项：

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

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

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

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

多分组统计

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

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

语法注意事项：

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

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

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

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

回溯统计

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

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

语法注意事项：

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

实例：

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

统计筛选

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

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

语法注意事项：

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

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

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

聚合函数

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

常用的聚合函数

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

操作表的准备

COUNT函数

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

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

注意点说明：

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

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

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

MIN和MAX函数

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

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

注意点说明：

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

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

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

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

SUM和AVG函数

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

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

注意点说明：

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

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

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

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

GROUP_CONCAT函数

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

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

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

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

JSON_ARRAYAGG函数

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

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

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

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

JSON_OBJECTAGG函数

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

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

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

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

扩展:别名

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

字段别名设置及使用

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

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

语法注意事项：

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

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

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

表别名设置及使用

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

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

语法注意事项：

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

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;