⭐️前言⭐️
博客主页： 【如风暖阳】
精品Java专栏【JavaSE】、【备战蓝桥】、【JavaEE初阶】、【MySQL】、【数据结构】
欢迎点赞 收藏 ⭐留言评论 私信必回哟本文由 【如风暖阳】 原创，首发于 CSDN
博主将持续更新学习记录收获，友友们有任何问题可以在评论区留言
博客中涉及源码及博主日常练习代码均已上传码云(gitee)、GitHub
内容导读
- MySQL进阶查询
- 1.新增查询
- 2.聚合查询
- 2.1 聚合函数
- 2.2 group by子句
- 2.3 having
- 3.联合查询
- 3.1 笛卡尔积
- 3.2 内连接
- 3.3 外连接
- 3.4 自连接
- 3.5 子查询
- 3.6 合并查询
- 4.导图总结
insert语句可以插入查询结果
drop table if exists student; create table student(id int primary key auto_increment,name varchar(20)); insert into student values (null,'张三'),(null,'李四'); drop table if exists student2; create table student2(id int primary key auto_increment,name varchar(20)); insert into student2 select *from student;
必须要保证查询的结果的列要和插入的表的列相匹配（列的名字无所谓，但是列的个数和类型需要匹配）
2.聚合查询 2.1 聚合函数常见的聚合函数：
| 函数 | 说明 |
|---|---|
| COUNT([DISTINCT] expr) | 返回查询到的数据的 数量 |
| SUM([DISTINCT] expr) | 返回查询到的数据的 总和，不是数字没有意义 |
| AVG([DISTINCT] expr) | 返回查询到的数据的 平均值，不是数字没有意义 |
| MAX([DISTINCT] expr) | 返回查询到的数据的 最大值，不是数字没有意义 |
| MIN([DISTINCT] expr) | 返回查询到的数据的 最小值，不是数字没有意义 |
案例：
初始化测试数据
-- 创建考试成绩表
DROp TABLE IF EXISTS exam_result;
CREATE TABLE exam_result (
id INT,
name VARCHAr(20),
chinese DECIMAL(3,1),
math DECIMAL(3,1),
english DECIMAL(3,1)
);
-- 插入测试数据
INSERT INTO exam_result (id,name, chinese, math, english) VALUES
(1,'唐三藏', 67, 98, 56),
(2,'孙悟空', 87.5, 78, 77),
(3,'猪悟能', 88, 98.5, 90),
(4,'曹孟德', 82, 84, 67),
(5,'刘玄德', 55.5, 85, 45),
(6,'孙权', 70, 73, 78.5),
(7,'宋公明', 75, 65, 30),(null,null,null,null,null);
- count：
统计结果共有多少行
count(列名)，空值不会记录
- sum:
统计数学成绩总分
- avg：
统计平均分
- max
返回英语最高分
- min
2.2 group by子句返回七十分以上的数学最低分
group by子句能够根据查询结果，进行分组，把值相同的记录分成一组，然后可以针对每一组分别进行聚合。
测试数据初始化：
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);
查询每个角色的最高工资、最低工资和平均工资：
注意：
在上图按照角色分组的查询结果中，每个记录都是相同角色的第一条记录；
说明在进行分组查询的时候，只有用来分组的这一列可以直接进行查询，其他列必须搭配聚合函数来查询。
在分组查询中，也可以进行条件筛选。
若在分组前指定条件，使用where语句，但若在分组后进行指定条件，就需要使用having语句。
- 查询除了猪无能以外每个岗位的平均薪资（分组前）
- 查询平均薪资大于1000的岗位（分组后）
实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：
假定两张表
其笛卡尔积结果就是两张表进行排列组合的结果
笛卡尔积结果的列数为两个表的列数之和，行数为两表行数之积。
在笛卡尔积的结果中我们可以再上图中发现，只有绿框部分的记录才是有效记录，这是因为笛卡尔积就是两张表直接进行排列组合的结果，并没设置条件，所以需要设置连接条件再进行查询，这样查询出来的结果才是有效记录。
create table student(id int,name varchar(20),classID int); insert into student values (1,'张三',1),(2,'李四',1),(3,'王五',2),(4,'赵六',2); create table class(classID int,name varchar(20)); insert into class values (1,'java100'),(2,'java101'); select * from student,class where student.classID=class.classID;
以下测试数据初始化：
drop table if exists classes;
drop table if exists student;
drop table if exists course;
drop table if exists score;
create table classes (id int primary key auto_increment, name varchar(20), `desc` 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, `desc`) 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);
3.2 内连接
语法：
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件; select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
案例：
(1)查询“许仙”同学的 成绩
1.计算student表和score表的笛卡尔积
2.给笛卡尔积加连接条件
3.根据许仙这个名字进行筛选
4.对查询结果的列进行精简，保留关注的，去掉不用的
如果使用join on的方式，一样可以实现相同的效果
(2)查询所有同学的总成绩，及同学的个人信息
需要进行分组聚合
1.计算student表和score表的笛卡尔积
2.给笛卡尔积加连接条件
3.按照人来分组（可以使用id、sn或者name）
4.求总成绩，即聚合函数sum
(3)查询所有同学的各科成绩及个人信息
需要三张表进行联合查询
1.对student表、course表、score表进行笛卡尔积
2.加连接条件
分数表为中间表，需要学生表和分数表的学生id相同，课程表和分数表的课程id相同
3.对查询结果进行列精简
总结：
由上边的例子可以总结出，复杂的SQL很难将其一步到位，需要一步步来完成，一般遵循以下步骤。
1)笛卡尔积
2)指定连接条件
3)加上其他的条件
4)对查询的列进行结果精简
外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。
当两张表中的数据一一对应时，内连接和外连接结果相同(join on内连接)；但若不一一对应时，就会有很大的差别。
语法：
-- 左外连接，表1完全显示 select 字段名 from 表名1 left join 表名2 on 连接条件; -- 右外连接，表2完全显示 select 字段 from 表名1 right join 表名2 on 连接条件;
若下图为两张表的关系
则内连接为
左连接为
右连接为
自连接是指在同一张表连接自身进行查询。(自己与自己笛卡尔积)
案例：
显示所有“计算机原理”成绩比“Java”成绩高的成绩信息
1.自身笛卡尔积
2.加限定条件，对应两门课程
3.继续加条件，“计算机原理”成绩比“Java”成绩高的成绩信息
3.5 子查询本质上就是把多个查询语句组合成一个查询语句
- 单行子查询：返回一行记录的子查询
案例：查询与“不想毕业” 同学的同班同学：
先找出不想毕业同学的班级id
根据班级id找到对应的同学
- 多行子查询：返回多行记录的子查询
案例：查询“语文”或“英文”课程的成绩信息
3.6 合并查询先找到语文和英文的课程id
再在score表中根据课程id找到对应记录
在实际应用中，为了合并多个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 all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
案例：查询id小于3，或者名字为“Java”的课程
-- 可以看到结果集中出现重复数据Java select * from course where id<3 union all select * from course where name='英文';4.导图总结
⭐️最后的话⭐️
总结不易，希望uu们不要吝啬你们的哟(＾Ｕ＾)ノ~ＹＯ！！如有问题，欢迎评论区批评指正
