MySQL表的增查（进阶）

1.插入查询结果

2.查询

2.1聚合查询

2.1.1聚合函数

2.1.2GROUP BY子句

2.1.3HAVING

2.2联合查询

2.2.1内连接

2.2.2外连接

2.2.3自连接

2.3子查询

2.4合并查询

1.插入查询结果

在一张表中插入另一张表的查询结果。

语法为：

insert into 表名 (列名，列名……) select 列名，列名…… from 被引用的表名；

本质：把查询的临时数据转换成了永久数据。

注意：

（1）插入的列名与查询的列名是否一致不影响。

（2）插入的列数与类型要与查询的列数与类型一致。

（3）varchar(n)中的n不需要一致。

案例：创建一张用户表，设计有 name 姓名、 email 邮箱、 sex 性别、 mobile 手机号字段。需要把已有的学生数据复制进来，可以复制的字段为name。

-- 创建用户表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
id INT primary key auto_increment,
name VARCHAR(20) comment '姓名',
age INT comment '年龄',
email VARCHAR(20) comment '邮箱',
sex varchar(1) comment '性别',
mobile varchar(20) comment '手机号'
);

演示：

展示student

生数据复制进来，可以复制的字段为name

2.查询

2.1聚合查询

2.1.1聚合函数

常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有：

COUNT

（1）select count(id) from test_user;

（2）select count(id) as 'id的总数' from test_user;

（3）select count(*) from test_user;

（4）select count(email) from test_user;

对于上述4个的演示：

（1）（2）

（3）

（4）

总结：

count(*)与 count(列名)

count(列名)：如果查询结果带有null，则null的那一行不会被记录计算。

但count(*)：即使全列为null也会被记录计算。

以下的聚合函数使用同理，不做过多解释。

-- 统计数学成绩总分
SELECT SUM(math) FROM exam_result;

-- 不及格 < 60 的总分，没有结果，返回 NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;

-- 统计平均总分
SELECT AVG(chinese + math + english) as 平均总分 FROM exam_result;

-- 返回英语最高分
SELECT MAX(english) FROM exam_result;

-- 返回 > 70 分以上的数学最低分
SELECT MIN(math) FROM exam_result WHERE math > 70;

重点：

使用聚合函数的时候，列与列之间的顺序已经被打散了。

正常来说，一行数据，每个列都是对应的。

如果查询中包含聚合函数与非聚合函数的列，则每一列的信息各自是各自的。

2.1.2GROUP BY子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中

语法：

select 列名，聚合函数（列名）之类……form 表名group by 列名……；

接下来我们用一个案例进行说明

比如：需要按照岗位进行查询与统计。

准备测试表及数据：职员表，有id（主键）、name（姓名）、role（角色）、salary（薪水）

create table emp(
id int primary key auto_increment,
	name varchar(20) not null,
	role varchar(20) not null,
	salary numeric(11,2)
);

insert into emp(name, role, salary) values
	('马云','服务员', 1000.20),
	('马化腾','游戏陪玩', 2000.99),
	('孙悟空','游戏角色', 999.11),
	('猪无能','游戏角色', 333.5),
	('沙和尚','游戏角色', 700.33),
	('隔壁老王','董事长', 12000.66);

查询每个角色的最高工资、最低工资和平均工资

select role,max(salary),min(salary),avg(salary) from emp group by role;

演示：

role（角色）有相同部分可以总结，固有上述操作。

但换作其他就不行，比如name。

各不相同，故全部展示。

但如果还把role（角色）分为一组，查询name

游戏角色只展示了孙悟空。

2.1.3HAVING

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING

案例如下：（1）对上述emp表显示平均工资低于1500的角色和它的平均工资

select role,max(salary),min(salary),avg(salary) from emp group by role
having avg(salary)<1500;

查询结果如下：

（2）对上述emp表显示工资低于1500的角色的平均工资

select role,max(salary),min(salary),avg(salary) from emp where salary<1500 group by role;

总结：

（1）分组之前的条件筛选用where。

（2）分组之后的条件筛选用having。

2.2联合查询

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积什么时笛卡尔积呢？请看下图

解释：

笛卡尔积得到的是一个更大的表

（1）新表的列数是之前两个表之和。

（2）新表的行数是之前两个表之积。

我们需要注意的是：关联查询可以对关联表使用别名

首先我们先初始化一些数据，方便后续查询

create table classes (
	id int primary key auto_increment,
	name varchar(20),
	desc1 varchar(100)
);
	
create table student (
	id int primary key auto_increment, 
	sn varchar(20),
	name varchar(20), 
	qq_mail varchar(20),
	classes_id int
);

create table course(
	id int primary key auto_increment,
 	name varchar(20)
);

create table score(
	score decimal(3,1),
	student_id int,
	course_id int
);

接下来我们插入一些数据：

insert into classes(name, desc1) values
	('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
	('中文系2019级3班','学习了中国传统文学'),
	('自动化2019级5班','学习了机械自动化');

insert into student(sn, name, qq_mail, classes_id) values
	('09982','黑旋风李逵','xuanfeng@qq.com',1),
	('00835','菩提老祖',null,1),
	('00391','白素贞',null,1),
	('00031','许仙','xuxian@qq.com',1),
	('00054','不想毕业',null,1),
	('51234','好好说话','say@qq.com',2),
	('83223','tellme',null,2),
	('09527','老外学中文','foreigner@qq.com',2);
	
insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');

insert into score(score, student_id, course_id) values
	-- 黑旋风李逵
	(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
	-- 菩提老祖
	(60, 2, 1),(59.5, 2, 5),
	-- 白素贞
	(33, 3, 1),(68, 3, 3),(99, 3, 5),
	-- 许仙
	(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
	-- 不想毕业
	(81, 5, 1),(37, 5, 5),
	-- 好好说话
	(56, 6, 2),(43, 6, 4),(79, 6, 6),
	-- tellme
	(80, 7, 2),(92, 7, 6);

展示：

2.2.1内连接

语法：

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

依旧采用案例来讲解：

查询“许仙”同学的成绩

select sco.score from student stu inner join score sco on stu.id=sco.student_id
 and stu.name='许仙';

-- 或者

select sco.score from student stu, score sco where stu.id=sco.student_id and
 stu.name='许仙';

查询结果为：

查询所有同学的总成绩，及同学的个人信息

-- 成绩表对学生表是多对1关系，查询总成绩是根据成绩表的同学id来进行分组的
SELECT
	stu.sn,
	stu.NAME,
	stu.qq_mail,
	sum(sco.score)
FROM
	student stu
	JOIN score sco ON stu.id = sco.student_id
GROUP BY
	sco.student_id;

查询结果为：

查询所有同学的成绩，及同学的个人信息

-- 学生表、成绩表、课程表3张表关联查询
SELECT
 stu.id,
 stu.sn,
 stu.NAME,
 stu.qq_mail,
 sco.score,
 sco.course_id,
 cou.NAME
 FROM
 student stu
 JOIN score sco ON stu.id = sco.student_id
 JOIN course cou ON sco.course_id = cou.id
 ORDER BY
 stu.id;

查询结果为：

重要：

查询出来的都是有成绩的同学， “ 老外学中文 ” 同学没有显示

select * from student stu join score sco on stu .id =sco .student_id ;

查询结果为：

对于内连接来说，得到的结果，必须是在两个表中都存在的数据。

2.2.2外连接

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

解释（以左外连接为例）：

左外连接，就是以左表为主，保证左侧的表的每一个记录都体现在最终表里。如果这个记录在右表中没有匹配，就把对应的列自动填充为空。

语法：

-- 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;

-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

案例：

查询出来的都是有成绩的同学

进行左连接

select * from student stu left join score sco on stu.id=sco.student_id;

查询结果为：

2.2.3自连接

自连接是指在同一张表连接自身进行查询

想要进行条件查询，条件一定是列与列之间的。但有的时候就想指定行与行之间的条件，此时可以通过自连接把行关系转换成列关系。

举个例子吧

查询显示所有“计算机原理”成绩比“Java”成绩高的成绩信息

-- 先查询“计算机原理”和“Java”课程的id
select id,name from course where name='Java' or name='计算机原理';

-- 也可以使用join on 语句来进行自连接查询
SELECT
 s1.* 
FROM
 score s1
 JOIN score s2 ON s1.student_id = s2.student_id
 AND s1.score < s2.score
 AND s1.course_id = 1
 AND s2.course_id = 3;

-- 再查询成绩表中，“计算机原理”成绩比“Java”成绩好的信息

以上查询只显示了成绩信息，并且是分布执行的。要显示学生及成绩信息，并在一条语句显示：

就可以使用以下内连接来进行查询：

SELECT
 stu.*,
 s1.score Java,
 s2.score 计算机原理
 FROM
 score s1
 JOIN score s2 ON s1.student_id = s2.student_id
 JOIN student stu ON s1.student_id = stu.id
 JOIN course c1 ON s1.course_id = c1.id
 JOIN course c2 ON s2.course_id = c2.id
 AND s1.score < s2.score
 AND c1.NAME = 'Java'
 AND c2.NAME = '计算机原理';

查询结果为：

2.3子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

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

查询与“不想毕业” 同学的同班同学

select * from student where classes_id=(select classes_id from student where
name='不想毕业');

查询结果为：

2.4合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。

union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

案例如下：

查询id小于3，或者名字为“英文”的课程

select * from course where id<3
 union
 select * from course where name='英文';

-- 或者使用or来实现
select * from course where id<3 or name='英文';

查询结果为：

注意：

union左右两侧sql可以查询两个不同的表，但用or不行。

union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

以上为我个人的小分享，如有问题，欢迎讨论！！！

都看到这了，不如关注一下，给个免费的赞