【MYSQL表的增删改查（进阶）】

一、新增

语法：

insert into 表名[(字段1,字段2,...)]select(字段1,字段2,...)from 表名;

示例：
创建一个student表(id, name, sex, age, email);

创建一个test表(id, name, sex, age, email);并且给student表中插入两组数据，通过新增，把student表中的数据复制到test表中。

二、查询

2.1 聚合查询

2.1.1 聚合函数

常见的有以下六种：

count（）

count()用来统计表中的行数。

select count(*) from 表名;//推荐大家使用这种方式，这种写法是SQL标准中规定的
select count(常数) from 表名;//这种方式也可以统计出表中的行数

也可以指定某一列进行统计。

select count(列名) from 表名;

count(列名)，如果说列中有NULL值，则不会被统计在内！、

sum（）

sum（列名）求和，把查询结果中所有行中的指定列进行相加
注意：列中的数据类型必须是数值型，不能是日期或字符…。如果不是对非数值类型的列进行计算，会得到警告信息。

select sum(列名) from 表名;

1.之前我们提起过，NULL与任何值运行的结果都是NULL。但通过这里发现，sum()求和NULL 值不参与运算。
2.这里的结果是在一个临时表中，结果不受表中字段长度约束。

AVG（）

对所有行的指定列进行平均值运算。

MAX（），MIN（）

求所有行中指定列的最大值，最小值

查出数学的最大值和最小值：

同一列可以使用不同的聚合函数

不同的一列可以使用不同的聚合函数

GROUP_CONCAT()

将来自多个记录的列值连接成一个字符串结果。通常与GROUP BY语句一起使用，以将多个行的值合并为一个逗号分隔的字符串。
示例：这里创建了一个employees表，其中包含员工的 id、department_id 和 name。并且给employees表中存储四组数据。

我们想要获取每个部门中所有员工的名字列表，可以使用 GROUP_CONCAT() 如下：

如果我们还想要按名字排序，并改变分隔符为分号（;），我们可以这样做：

注意事项：

1.GROUP_CONCAT() 默认的结果长度受到 group_concat_max_len 系统变量的限制，其默认值是 1024。如果你预期会有大量的数据被合并，可能需要调整这个值。
2.如果你在一个没有 GROUP BY 语句和其他聚合函数的查询中仅使用 GROUP_CONCAT()，MySQL 可能会将整个结果集视为一个大的组，并尝试将所有行的相关列值连接成一个单一的字符串。这通常不是预期的行为，因为它可能会导致生成一个非常长的字符串，特别是当查询结果集很大时。

2.1.2 GROUP BY 子句

GROUP BY 子句：SELECT中使用GROUP BY 子句可以对指定列进行分组查询。然后，对于每个组，它会计算聚合函数的值。如果查询中没有指定聚合函数，但使用了 GROUP BY，则每个组将只返回一行，通常是分组列的值（尽管这取决于选择的列和数据库的具体实现）。

语法：

select 列名(分组的列名),聚合函数（列名）...from 表名 group by 列名(分组的列名),列名(分组的列名);

示例：假设我们有一个名为 sales 的表，包含列 year, product_id, 和 amount。

然后我们根据product_id进行分组，查看一下它的销售总额。

group by 后面可以跟order by子句

2.1.3 HAVING 子句

GROUP BY 子句进行分组之后，需要对分组结果进行条件过滤时，不能使用WHERE 语句，而需要使用HAVING；
示例：这里为了大家更方便简单观察到，这里我又插入了6组数据。

这里我们需要通过HAVING子句过滤掉销售量小于800的产品。

2.2 联合查询

联合查询（也称为联结查询或JOIN查询）是一种通过两个或多个表中的列之间的关系来查询、组合或合并表中的数据的方法。联合查询是SQL中一个非常强大的功能，它允许你根据需要在不同的表中检索相关信息。

联合查询是如何执行的？

1.取多张表的笛卡尔积。

举例：创建一张class表，包含列id，name；创建一张student表，包含列class_id，name,sex。

并且分别给两张表中插入数据！
生成笛卡尔积：

2.通过连接条件过滤掉无效数据

3.能通过指定列查询，来精简结果集

2.2.1 内连接

内连接（INNER JOIN）是SQL查询中使用的一种连接类型，用于返回两个或多个表中符合连接条件的记录。内连接仅返回那些在连接条件上有匹配的行。如果在一个表中有行在另一个表中没有匹配的连接条件，那么这些行就不会出现在查询结果中。

语法：

select 字段 from 表1 as 别名1 [inner] join 表名2 as 别名2 on 连接条件 and 其他条件;//标准的内连接写法

select 字段 from 表1 as 别名1 , 表名2 as 别名2 where 连接条件 and 其他条件;

示例：查询班级编号，班级名称，学生姓名

注意：当你使用别名时，那么select 后面的字段也必须是别名.字段名，否则则无法识别；

2.2.2 外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接，右侧的表完全显示我们就说是右外连接。

语法：
左外连接：表1全显示

select 字段名 from 表名1 left join 表名2 on 连接条件;

右外连接：表2全显示

select 字段名 from 表名1 right join 表名2 on 连接条件;

示例：假设class表为表1，student表为表2。

左外连接：

右外连接：

外连接：通过right和left确认基准表，没有与基准表匹配的记录，用null填充。

2.2.3 自连接

自连接（Self-Join）是一种特殊的连接查询，它指的是表与其自身进行连接。自连接通常用于查询表中存在层次或递归关系的数据。

案例：查询比‘雨欣’数学成绩高的学生。

1.去笛卡尔积

2.观察结果集，确认条件

也可以用join on 语句进行自连接查询

 select * from score s1 join score s2 on s1.name='雨欣' and s1.math<s2.math;

2.2.4 子查询

子查询是指嵌入在其他sql语句中select语句，也叫嵌套查询。由于嵌套查询没有固定的限制，多层嵌套的效率是不可控的，所以谨慎使用。

单行查询：返回一行子查询记录

案例：查询数学成绩为69，语文成绩为66的学生的信息。

多行查询：返回多行记录的子查询

返回一个集合，使用关键字IN

关键字：[NOT]EXISTS

select * from where exists(select * from 表名);

解释：exists后面括号中的查询语句，如果有结果返回，则执行外层查询，如果返回值为空，则不执行外层查询。

from子句使用子查询生成临时表

在from子句中使用子查询：子查询语句出现from子句中，这里要用到数据查询的技巧，把一个子查询当成一个临时表来使用。

2.2.5 合并查询

合并查询通常指的是将多个查询的结果集合并成一个结果集。这可以通过 UNION 或 UNION ALL 操作符来实现。使用 UNION 或 UNION ALL 时，前后查询的结果集中，字段需要一致。

UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集，并自动去除重复的记录，只保留唯一的记录。每个 SELECT语句必须拥有相同数量的列，且对应列的数据类型也必须兼容。

在单表中可以去用逻辑运算符去连接不同的查询条件；但多表中，就没办法用逻辑运算符，如果最终结果从多表中获取的，必须要用UNION来进行合并。
案例：自动去除结果集中的重复行！

列名不匹配，返回的结果值就是错误的，这种查询就是没有意义的！

UNION ALL

与 UNION 类似，UNION ALL 也用于合并两个或多个 SELECT 语句的结果集，但它会包含所有的重复记录。也就是说，它不会去除任何重复的行。这通常在你需要包含所有行，包括重复行时非常有用。

案例：不会去除结果集中的重复行！