MySQL表的增删改查(进阶)

目录

一、新增

二、查询

2.1 聚合查询

2.1.1 聚合函数

2.1.2 group by子句

2.1.3 having

2.2 联合查询

2.2.1 内连接

2.2.2 外连接

2.2.3 自连接

2.2.4 子连接

2.2.5 合并查询

---

一、新增

把查询的结果新增到一张表中

示例:

创建一张学生表，包含字段：姓名，学号，班级id；再创建一张班级表，包含字段：班级id，学生学号，学生姓名。现在需要把学生信息复制到班级表中：

-- 创建班级表
create table class(
    id int,
    studentId int,
    studentName varchar(20)
);
-- 创建学生表
create table student(
    id int primary key auto_increment,
    name varchar(20),
    classId int
);

-- 给学生表中插入数据
insert into student values
(null,'张三',1),
(null,'李四',1),
(null,'王五',2),
(null,'赵六',2);

-- 把学生信息复制到班级表中
insert into class(id,studentId,studentName) select classId,id,name from student;

代码运行结果：

注意： 

(1)查询的字段类型和数量要和插入的字段的类型和数量相匹配，否则可能会插入失败；

(2)也可以当前表中查询的结果插入到当前表中。

insert into student(name,classId) select name,classId from student;

代码运行结果：

（当前表中的id是自增主键，不可重复，因此id不可复制） 

二、查询

2.1 聚合查询

2.1.1 聚合函数

常用的聚合函数：

(在SQL中，数字和NULL的运算结果是NULL，所以SUM，AVG等聚合函数查询到NULL时会自动跳过，否则计算结果没有意义)

注意：

COUNT函数中COUNT(*)和COUNT(字段)的区别：

COUNT(*)会返回当前表中行数之和(即使整行数据都为NULL也会被算作一行)；

COUNT(字段)会返回当前表中当前字段的行数之和(如果当前字段中某一行的数据为NULL，则不算作一行)。

示例：

2.1.2 group by子句

group by子句可以对指定列进行分组查询。

示例：通过班级id对学生进行分组

此时，表中显示的是分组之后每组的第一个数据：

2.1.3 having

使用group by进行分组查询时，也可以通过条件筛选进行分组：

分组前进行条件筛选，使用where关键字；

例如：通过班级id对除了张三之外的同学进行分组：

分组后再进行条件筛选的话，需要使用having关键字。

例如：根据学生姓名分组，查询姓名为张三的同学：

注意：

(1)where用在group by之前，having用在group by之后； 

(2)where 和 having 可以同时使用。

2.2 联合查询

实际开发中可能需要通过多张表来查询一组数据，所以需要用到多表联合查询，联合查询是对多张表的数据取笛卡尔积。

2.2.1 内连接

内连接类似于查询两个集合的交集部分，语法为：

select 字段 from 表1，表2 where 条件;

select 字段 from 表1 join 表2 on 条件;

 示例：现在有一张学生表和一张成绩表，查询每个学生的分数：

学生信息和分数在两张不同的表中，但是两张表中都存有学生的id，此时就可以用联合查询：

 

注意：

如果两个表中的字段名相同，需要使用表名.字段名来区分，否则会报错；如果字段名不同，则可用也可不用。

2.2.2 外连接

外连接分为左外连接和右外连接。如果在联合查询时，既要显示两个表中的交集，也要完全显示左侧的表，就是左外连接；同理，既显示交集，也完全显示右侧的表，则为右外连接。

语法：

左外连接：select 字段 from 表1 left join 表2 on 条件;

右外连接：select 字段 from 表1 right join 表2 on 条件;

示例：现在有一张学生表和一张成绩表，查询学生表中每位学生的成绩：

观察学生表可以发现，阿瞒同学可能缺考，没有成绩，但是又要查询阿瞒同学的成绩该怎么办呢？左外连接！

左边表中的信息完全显示，并显示两个表的交集部分，阿瞒在分数表中没有数据，所以为NULL。

注意：

MySQL中没有全外连接，即同时查询两个表的所有信息。 

2.2.3 自连接

自连接就是指一张表连接自己本身进行查询。

语法：

select 字段 from 表名 as 别名1,表名 as 别名2 where 条件;

select 字段 from 表名 as 别名1 join 表名 as 别名2 on 条件;

示例：使用自连接方式查询货物表中利润不低于10的货物：

注意：

自连接需要使用别名，否则会报错。

自连接只有在特殊情况会用到，上述示例如果直接使用条件查询会更简单易懂。

2.2.4 子查询

子查询，解释为两个字就是——套娃，把一次查询的结果作为另一次查询的条件。

示例：使用子查询的方式查询和刘备同班的同学；

注意：

子查询尽量不要写太多层，否则会大大降低代码的可读性。 

2.2.5 合并查询

合并查询可以合并多个select的执行结果，关键字：UNION、UNION ALL。

UNION会取得两个结果集的并集，并且自动去掉结果集中的重复行。

示例：查询id < 5 或者 班级id < 3的学生信息：

 UNION ALL也是取得两个结果集的并集，但不会去掉结果集中的重复行。

UNION 和 OR的区别在于：

UNION可以合并查询不同表的结果，前提是两个表中的字段需要一致；

OR只能用于查询同一个表时的筛选条件。