MySQL聚合查询&分组查询&联合查询

#对应代码练习

-- 创建考试成绩表
DROP TABLE IF EXISTS exam;
CREATE TABLE exam (
id bigint,
name VARCHAR(20),
chinese DECIMAL(3,1),
math DECIMAL(3,1),
english DECIMAL(3,1)
);
-- 插入测试数据
INSERT INTO exam (id,name, chinese, math, english) VALUES
(1,'唐三藏', 67, 98, 56),
(2,'孙悟空', 87.5, 78, 77),
(3,'猪悟能', 88, 98, 90),
(4,'曹孟德', 82, 84, 67),
(5,'刘玄德', 55.5, 85, 45),
(6,'孙权', 70, 73, 78.5),
(7,'宋公明', 75, 65, 30);

1.聚合函数

函数说明count()返回查到数据的数量sum()返回查到数据的总和avg()返回查到数据的平均值max()返回查到数据的最大值min()返回查到数据的最小值
注释：不是数字没有意义，聚合函数只能对数字型进行运算

1.1count函数：统计所有的行

select count(*) from 表名;

select count(1) from 表名;

select count(指定列) from 表名;

注释：在日常工作中，推荐大家使用count（*），这种写法是sql中规定

NULL 值不参与统计

1.2sum函数：求和

把查询结果中所有行中的指定列进行相加

注意：列的数据必须是数值型，不能是字符型，日期型等等，如果对非数值型计算，会报警告！

select sum(指定列) from 表名;

示例：所有学生语文成绩的总和

NULL值不参与运算

1.3avg函数：求平均值

select avg(指定列/表达式) as 别名 from 表名;

示例：所有学生语文成绩的总和 的平均值

示例：所有语文，英语，数学三门成绩总和的平均分

1.4max函数，min函数：求最大值，最小值

select max(指定列) as 别名，min(指定列) as 别名 from 表名;

示例：语文最高分，英语最低分

注释：同一列可以用不同的聚合函数

2.分组查询：group by子句

select 中使用group by子句可以对指定列进行分组查询。需要满足：使用group by子句进行分组长训时，select 指定的字段必须是“分组依据字段（需要分组的列）”，其他列想出现，必须包含在聚合函数中

#相关代码练习

create table emp (
id bigint primary key auto_increment,
name varchar(20) not null,
role varchar(20) not null,
salary decimal(10, 2) not null
);

insert into emp values (null, '马云', '老板', 1500000.00);
insert into emp values (null, '马化腾', '老板', 1800000.00);
insert into emp values (null, 'a哥', '讲师', 10000.00);
insert into emp values (null, 'b哥', '讲师', 12000.00);
insert into emp values (null, 'c姐', '学管', 9000.00);
insert into emp values (null, 'd姐', '学管', 8000.00);
insert into emp values (null, '猪悟能', '游戏角色', 700.5);
insert into emp values (null, '沙和尚', '游戏角色', 333.3);

语法：select 分组的列名，聚合函数(指定列),... from 表名 group by 分组的列;

示例：计算不同角色工资的平均值

注意：round(数值，小数点位数)

示例：round(avg(salary),2)

注意：group by之后可以跟order by子句

3.having 关键字

group by子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用where语句，而是用having语句

where是对表每一行的真实数据进行过滤，where在from之后

having是对分组后，计算出来的结果进行过滤的，having在group by之后

示例：每种角色的平均工资大于1万小于10万

4.联合查询（MySQL中重点内容）

#相关代码练习

CREATE TABLE class (
class_id bigint NOT NULL AUTO_INCREMENT,
name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (class_id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

---

-- Records of class

---

INSERT INTO class VALUES (1, '计算机系2019级1班');
INSERT INTO class VALUES (2, '中文系2019级3班');
INSERT INTO class VALUES (3, '自动化2019级5班');

---

-- Table structure for course

---

CREATE TABLE course (
course_id bigint NOT NULL AUTO_INCREMENT,
name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (course_id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

---

-- Records of course

---

INSERT INTO course VALUES (1, 'Java');
INSERT INTO course VALUES (2, '中国传统文化');
INSERT INTO course VALUES (3, '计算机原理');
INSERT INTO course VALUES (4, '语文');
INSERT INTO course VALUES (5, '高阶数学');
INSERT INTO course VALUES (6, '英文');

---

-- Table structure for student

---

CREATE TABLE student (
student_id bigint NOT NULL AUTO_INCREMENT,
sn varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
mail varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
class_id bigint NULL DEFAULT NULL,
PRIMARY KEY (student_id) USING BTREE,
UNIQUE INDEX sn(sn ASC) USING BTREE,
INDEX class_id(class_id ASC) USING BTREE,
CONSTRAINT student_ibfk_1 FOREIGN KEY (class_id) REFERENCES class (class_id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

---

-- Records of student

---

INSERT INTO student VALUES (1, '09982', '黑旋风李逵', 'xuanfeng@qq.com', 1);
INSERT INTO student VALUES (2, '00835', '菩提老祖', NULL, 1);
INSERT INTO student VALUES (3, '00391', '白素贞', NULL, 1);
INSERT INTO student VALUES (4, '00031', '许仙', 'xuxian@qq.com', 1);
INSERT INTO student VALUES (5, '00054', '不想毕业', NULL, 1);
INSERT INTO student VALUES (6, '51234', '好好说话', 'say@qq.com', 2);
INSERT INTO student VALUES (7, '83223', 'tellme', NULL, 2);
INSERT INTO student VALUES (8, '09527', '老外学中文', 'foreigner@qq.com', 2);

---

-- Table structure for score

---

CREATE TABLE score (
score_id bigint NOT NULL AUTO_INCREMENT,
student_id bigint NULL DEFAULT NULL,
course_id bigint NULL DEFAULT NULL,
score decimal(5, 2) NULL DEFAULT NULL,
PRIMARY KEY (score_id) USING BTREE,
INDEX student_id(student_id ASC) USING BTREE,
INDEX course_id(course_id ASC) USING BTREE,
CONSTRAINT score_ibfk_1 FOREIGN KEY (student_id) REFERENCES student (student_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT score_ibfk_2 FOREIGN KEY (course_id) REFERENCES course (course_id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

---

-- Records of score

---

INSERT INTO score VALUES (1, 1, 1, 70.50);
INSERT INTO score VALUES (2, 1, 3, 98.50);
INSERT INTO score VALUES (3, 1, 5, 33.00);
INSERT INTO score VALUES (4, 1, 6, 98.00);
INSERT INTO score VALUES (5, 2, 1, 60.00);
INSERT INTO score VALUES (6, 2, 5, 59.50);
INSERT INTO score VALUES (7, 3, 1, 33.00);
INSERT INTO score VALUES (8, 3, 3, 68.00);
INSERT INTO score VALUES (9, 3, 5, 99.00);
INSERT INTO score VALUES (10, 4, 1, 67.00);
INSERT INTO score VALUES (11, 4, 3, 23.00);
INSERT INTO score VALUES (12, 4, 5, 56.00);
INSERT INTO score VALUES (13, 4, 6, 72.00);
INSERT INTO score VALUES (14, 5, 1, 81.00);
INSERT INTO score VALUES (15, 5, 5, 37.00);
INSERT INTO score VALUES (16, 6, 2, 56.00);
INSERT INTO score VALUES (17, 6, 4, 43.00);
INSERT INTO score VALUES (18, 6, 6, 79.00);
INSERT INTO score VALUES (19, 7, 2, 80.00);
INSERT INTO score VALUES (20, 7, 6, 92.00);

4.1内连接

语法：

#标准写法
select 列名 from 表名1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;

#个人习惯写法

select 列名 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件

注释：习惯哪种用哪种！

示例：查询“许仙”同学的成绩（分步骤做这道题）

1.首先确定哪几张表参与查询：成绩表和学生表；

select *from student,score;

2.根据表与表之间的主外键关系，确定过滤条件

student_id作为主外键关联字段

select *from student,score where student.student_id=score.student_id;

3.确定过滤条件

在where中添加student.name='许仙'的过滤条件

select *from student,score where student.student_id=score.student_id and student.name='许仙';

4.精简信息

只需要姓名和分数

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

注释：联合查询详细步骤

1.确定查询中涉及到有那些表。2.对目标表取笛卡尔积。3.确定连接条件。4.确定对整个结果集的过滤条件。5.精减查询字段

示例：查询所有同学的总成绩和个人信息

select st.student_id,st.name,sum(sc.score) as 总分 from student st,score sc where st.student_id=sc.student_id group by sc.student_id;

4.2外连接

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

语法：
select*from 表1 left(right) join 表2 on 连接条件;

示例：查询没有考试的同学

select*from student st left join score sc on st.student_id=sc.student_id where sc.score_id is NULL;

4.3自链接

实现行与行之间的比较功能

注意：自连接时，因为同一张表需要用到两次，所以得起不一样的别名，否则会报错

示例:显示所有计算机原理成绩比Java成绩高的信息（分步骤演示）

1.确定涉及的表：课程表和成绩表

2.取笛卡尔积：select*from score sc1,score sc2;

3.连接条件就是student_id相同
select*from score sc1,score sc2 where sc1.student_id=sc2.student_id;

4.观察结果集，确定过滤条件

1 是Java ，3是计算机原理

要么sc1.course_id=1 and sc2.course_id=3

要么sc1.course_id=3 and sc2.course_id=1

5.加入条件

select*from score sc1,score sc2 where sc1.student_id=sc2.student_id and sc1.course_id=3 and sc2.course_id=1 and sc1.score>sc2.score ;

5.子查询（嵌套查询）

5.1单行子查询

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

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

5.2多行子查询

示例：查询语文和英文成绩信息 （使用到in关键词）

select *from score where course_id in (select course_id from course where name='语文' or name='英文');

6.exists关键字

语法：select*from 表名 where exists (查询语句);

exists 后面括号中的查询语句，如果有结果正常返回，则执行外层语句；如果返回空，则不执行

相当于if语句的判断条件，有结果返回true，没结果返回false

1.正常返回，因为学号有1的同学

2.返回为空，因为学号没有100的同学

7.合并查询

关键词 union ，union all

语法：select *from 表名1 union/ union all select *from 表名2;

union 会去重，union all不会去重

在单表查询推荐使用 or，多表查询不能用or ，就必须用union来连接