MySQL进阶查询

⭐️前言⭐️

🍉博客主页：****🍁【如风暖阳】🍁
🍉精品Java专栏【JavaSE】、【备战蓝桥】、【JavaEE初阶】、【MySQL】、【数据结构】
🍉欢迎点赞 👍 收藏 ⭐留言评论 📝私信必回哟😁

🍉本文由 【如风暖阳】 原创，首发于 CSDN🙉

🍉博主将持续更新学习记录收获，友友们有任何问题可以在评论区留言

🍉博客中涉及源码及博主日常练习代码均已上传码云(gitee)、GitHub

📍内容导读📍

MySQL进阶查询

🍅1.新增查询

insert语句可以插入查询结果

droptableifexists student;createtable student(id intprimarykeyauto_increment,name varchar(20));insertinto student values(null,'张三'),(null,'李四');droptableifexists student2;createtable student2(id intprimarykeyauto_increment,name varchar(20));insertinto student2 select*from student;

必须要保证查询的结果的列要和插入的表的列相匹配（列的名字无所谓，但是列的个数和类型需要匹配）

🍅2.聚合查询

2.1 聚合函数

常见的聚合函数：
函数说明COUNT([DISTINCT] expr)返回查询到的数据的 数量SUM([DISTINCT] expr)返回查询到的数据的 总和，不是数字没有意义AVG([DISTINCT] expr)返回查询到的数据的 平均值，不是数字没有意义MAX([DISTINCT] expr)返回查询到的数据的 最大值，不是数字没有意义MIN([DISTINCT] expr)返回查询到的数据的 最小值，不是数字没有意义
案例：
初始化测试数据

-- 创建考试成绩表DROPTABLEIFEXISTS exam_result;CREATETABLE exam_result (
                             id INT,
                             name VARCHAR(20),
                             chinese DECIMAL(3,1),
                             math DECIMAL(3,1),
                             english DECIMAL(3,1));-- 插入测试数据INSERTINTO 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子句能够根据查询结果，进行分组，把值相同的记录分成一组，然后可以针对每一组分别进行聚合。

测试数据初始化：

createtable emp(
 id intprimarykeyauto_increment,
 name varchar(20)notnull,
 role varchar(20)notnull,
 salary numeric(11,2));insertinto emp(name, role, salary)values('马云','服务员',1000.20),('马化腾','游戏陪玩',2000.99),('孙悟空','游戏角色',999.11),('猪无能','游戏角色',333.5),('沙和尚','游戏角色',700.33),('隔壁老王','董事长',12000.66);

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

注意：


在上图按照角色分组的查询结果中，每个记录都是相同角色的第一条记录；
说明在进行分组查询的时候，只有用来分组的这一列可以直接进行查询，其他列必须搭配聚合函数来查询。

2.3 having

在分组查询中，也可以进行条件筛选。
若在分组前指定条件，使用where语句，但若在分组后进行指定条件，就需要使用having语句。

• 查询除了猪无能以外每个岗位的平均薪资（分组前）
• 查询平均薪资大于1000的岗位（分组后）

🍅3.联合查询

3.1 笛卡尔积

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：
假定两张表

其笛卡尔积结果就是两张表进行排列组合的结果

笛卡尔积结果的列数为两个表的列数之和，行数为两表行数之积。
在笛卡尔积的结果中我们可以再上图中发现，只有绿框部分的记录才是有效记录，这是因为笛卡尔积就是两张表直接进行排列组合的结果，并没设置条件，所以需要设置连接条件再进行查询，这样查询出来的结果才是有效记录。

createtable student(id int,name varchar(20),classID int);insertinto student values(1,'张三',1),(2,'李四',1),(3,'王五',2),(4,'赵六',2);createtable class(classID int,name varchar(20));insertinto class values(1,'java100'),(2,'java101');select*from student,class where student.classID=class.classID;

以下测试数据初始化：

droptableifexists classes;droptableifexists student;droptableifexists course;droptableifexists score;createtable classes (id intprimarykeyauto_increment, name varchar(20),`desc`varchar(100));createtable student (id intprimarykeyauto_increment, sn varchar(20),  name varchar(20), qq_mail varchar(20),
                      classes_id int);createtable course(id intprimarykeyauto_increment, name varchar(20));createtable score(score decimal(3,1), student_id int, course_id int);insertinto classes(name,`desc`)values('计算机系2019级1班','学习了计算机原理、C和Java语言、数据结构和算法'),('中文系2019级3班','学习了中国传统文学'),('自动化2019级5班','学习了机械自动化');insertinto student(sn, name, qq_mail, classes_id)values('09982','黑旋风李逵','[email protected]',1),('00835','菩提老祖',null,1),('00391','白素贞',null,1),('00031','许仙','[email protected]',1),('00054','不想毕业',null,1),('51234','好好说话','[email protected]',2),('83223','tellme',null,2),('09527','老外学中文','[email protected]',2);insertinto course(name)values('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');insertinto 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 别名2on 连接条件 and 其他条件;select 字段 from 表1 别名1,表2 别名2where 连接条件 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)对查询的列进行结果精简

3.3 外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。
当两张表中的数据一一对应时，内连接和外连接结果相同(join on内连接)；但若不一一对应时，就会有很大的差别。

语法：

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

若下图为两张表的关系

则内连接为

左连接为

右连接为

3.4 自连接

自连接是指在同一张表连接自身进行查询。(自己与自己笛卡尔积)
案例：
显示所有“计算机原理”成绩比“Java”成绩高的成绩信息
1.自身笛卡尔积

2.加限定条件，对应两门课程

3.继续加条件，“计算机原理”成绩比“Java”成绩高的成绩信息

3.5 子查询

本质上就是把多个查询语句组合成一个查询语句

• 单行子查询：返回一行记录的子查询 案例：查询与“不想毕业” 同学的同班同学：

先找出不想毕业同学的班级id
根据班级id找到对应的同学

• 多行子查询：返回多行记录的子查询 案例：查询“语文”或“英文”课程的成绩信息

先找到语文和英文的课程id
再在score表中根据课程id找到对应记录

3.6 合并查询

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

• union 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。 案例：查询id小于3，或者名字为“英文”的课程：

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

• union all 该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。 案例：查询id小于3，或者名字为“Java”的课程

-- 可以看到结果集中出现重复数据Javaselect*from course where id<3unionallselect*from course where name='英文';

🍅4.导图总结

⭐️最后的话⭐️
总结不易，希望uu们不要吝啬你们的👍哟(＾Ｕ＾)ノ~ＹＯ！！如有问题，欢迎评论区批评指正😁