登录
首页 >  数据库 >  MySQL

MySQL数据库复合查询操作实战

来源:脚本之家

时间:2023-05-12 14:10:14 257浏览 收藏

珍惜时间,勤奋学习!今天给大家带来《MySQL数据库复合查询操作实战》,正文内容主要涉及到语句、mysql复合查询等等,如果你正在学习数据库,或者是对数据库有疑问,欢迎大家关注我!后面我会持续更新相关内容的,希望都能帮到正在学习的大家!

1.基本查询回顾

准备工作,创建一个雇员信息表:(来自oracle 9i的经典测试表)

EMP员工表 DEPT部门表 SALGRADE工资等级表

image-20221019195007702

案例1:查询工资高于500或岗位为MANAGER的雇员,同时还要满足雇员的姓名首字母为大写的J

第一步:查询工资高于500或者岗位为MANAGER的雇员

image-20221021194636657

第二步:在上面筛选之后的条件下:还要满足姓名首字母为大写的J的雇员 ,此时需要利用到substring截取字符,判断第一个字符是否是j

image-20221021194622724

substring(ename,1,1) :从第1个字符开始往后截取,截取1个字符, 得到的就是姓名的首字母, (因为默认从1开始

案例2:按照部门号升序而雇员的工资降序排序

默认的order by 排序就是升序的(asc), 如果想要降序:desc

image-20221021194902982

先按部门号排序, 部门号相同的按照工资降序排序

案例3:使用年薪进行降序排序

第一步:先算出每个人的年薪, 年薪=工资*12 + 奖金, 但是我们可以发现,有的人是没有奖金的,其奖金为NULL

所以这里我们可以使用ifnull函数

  • IFNULL() 函数用于判断第一个表达式是否为 NULL,如果为 NULL 则返回第二个参数的值,如果不为 NULL 则返回第一个参数的值
  • ifnull(奖金,0) :如果奖金选项不是空,就返回0, 否则返回奖金

image-20221021195332225

第二步:按照年薪降序排序 , 因为此时是已经拿到了数据之后才能进行排序, 所以排序的地方可以使用别名

image-20221021195439784

案例4:显示工资最高的员工的名字和工作岗位

写法1:先拿到公司最高工资, 可能多个人的工资都是最高工资, 然后按照这个最高工资进行筛选人

image-20221021195705885

写法2:可以直接使用子查询, select里面套select, 先执行后面的子查询,它的执行结果作为下一个select的查询条件

image-20221021200106471

案例5:显示工资高于平均工资的员工信息

方法1:先拿到平均工资,然后按照这个平均工资进行筛选人

image-20221021200418110

方法2:使用子查询

image-20221021200513272

案例6:显示每个部门的平均工资和最高工资

做法:首先需要对每个部门做分组,然后求出每个部门的平均工资和最高工资

image-20221021201233987

先从员工表emp当中拿到数据,然后按照部门编号deptno做分组, 然后针对每一组聚合求平均工资和最高工资

当然了,如果我们想平均工资只显示后面的2位小数: 可以使用format聚合函数控制格式: 四舍五入

image-20221021201358760

案例7:显示平均工资低于2000的部门号和它的平均工资

含义就是:先把平均工资低于2000的部门,然后求出这个部门的平均工资

做法:先分组,再聚合求出每一组的平均工资, 然后再按条件:,注意:这里不能使用where,可以使用having

image-20221021202524803

  • 不能使用where的原因:按照平均工资进行筛选的前提是:我们已经把每一组的平均工资算出来了,也就是我们的聚合操作已经完成了, 数据已经被提取出来了, 而where是在筛选数据的阶段帮我们对数据进行筛选的,是在分组前进行的, 我们这里已经把数据筛选出来做了分组了
  • 执行顺序:from -> where -> group by ->having -> select -> distinct -> order by -> limit

关于where, group by having

**where:**数据库中常用的是where关键字,用于在初始表中筛选查询

**group by:**对select查询出来的结果集按照某个字段或者表达式进行分组,获得一组组的集合

**having:**用于对where和group by查询出来的分组进行过滤,查出满足条件的分组结果

案例8:显示每种岗位的雇员总数,平均工资

做法:先按照岗位进行分组,然后对每一组数据进行分组聚合

image-20221021202701326

2.多表查询 (重要)

实际开发中往往数据来自不同的表,所以需要多表查询

例子:emp表和dept表进行联合查询:

image-20221021202906941

什么叫笛卡尔积:

简单来说:就是排列组合, 把两张表的记录放在一起进行排列组合的所有情况, 全排列!一般而言,我们所进行的后续多表查询,都应该是笛卡尔积形成的新表的子集

  • 笛卡尔积的列数就是两个表的列数之和,行数则是两个表的行数之积,我们在进行多表查询的时候(计算笛卡尔积的过程),如果两个表数据很大,就会非常低效

如果是三个表的话,那么就是先将两个表进行笛卡尔积运算,再用这个表与另外一个表进行笛卡尔积操作

因为毕竟笛卡尔积只是简单的将他们进行排列组合(并没有进行筛选有效信息,我们将有效信息这一筛选的过程称为:连接条件 ,通常是存在 主外键约束 条件的多表建立的, 连接条件中两个字段通过 = 建立等值关系, 例如上面的例子当中, 连接条件就是: emp.deptno = dept.deptno

需要注意的是:笛卡尔积之后的新表,如果有相同的列名,就要通过表名.列名的方式区分,如果不用则会报错

即:当多表查询有重名的列时,必须在列名前加上表名【一般用别名】作为前缀

如何看待多表查询:

我们认为,在我心中永远只有一张表,将来所有的多表查询都可以认为是单表查询, 我们认为select查询出来的"记录",都可以把它看作"表"

多表查询步骤

  • 先把多表查询转化为单表查询
  • 筛去排列组合产生的无意义数据
  • 然后再根据要求进一步筛选
  • 选定好需要展示的字段

案例1:显示雇员名,雇员工资以及所在部门的名字

雇员名,工资在emp表里面有,而部门的名字只在dept表里面有,上面的数据来自EMP和DEPT表,因此要进行多表查询

image-20221021203936321

我们首先需要根据emp表的外键deptno和主表dept的key做级联 -> 过滤非法数据,

image-20221021204116654

需要注意的是:如果合并之后,列名在表结构当中唯一存在,就可以直接使用,如果不是唯一存在,就在前面加一个列名表示使用的是原来那一张表的 表名.列名

image-20221021204443068

案例2:显示部门号为10的部门名,员工名和工资

员工名和工资在员工表里面有, 部门名只在部门表有,所以需要进行多表查询

做法:把两个表进行笛卡尔积,把数据穷举到一起, 然后根据连接条件:员工表的部门编号=部门表的编号, 把合法数据筛选出来, 然后根据条件筛选数据

image-20221021210127247

注意:笛卡尔积之后的表,deptno列名不唯一,所以需要指定表名访问

案例3:显示各个员工的姓名,工资,及工资级别

工资级别 :在工资表, 员工的姓名和工资:在员工表 所以这里是多表查询

image-20221021212405617

问:此时什么是非法的数据? 工资不在对应的等级范围!

做法:先根据工资判断其是否在[losal,hisal]范围内,如果在,说明就是合法数据,否则是非法数据,

image-20221021212705956

因为此处sal losal hisal都是笛卡尔积之后的新表当中唯一的列名,所以不需要带表名区分

我们可以发现:上面多表查询做题的精髓是: 先确定要的数据在哪些表,然后把这些表进行笛卡尔积,整合在一起, 多表就变成了一张表, 然后再根据连接条件对数据做清洗,过滤掉非法的数据, 然后再按条件进行筛选

3.自连接

自连接是指在同一张表连接查询,一张表可以和别人笛卡尔积,当然也可以和自己笛卡尔,自连接时要对表名进行重命名,否则会出现名字冲突的问题.

因为表名字不能相同,所以我们需要对表名取别名

image-20221021212926469

案例1:显示员工FORD的上级领导的编号和姓名

做法1:单表查询: 先找到这个员工FORD的领导的编号,然后根据编号找到这个领导是谁

image-20221021213356261

做法2:改成子查询 :先找到员工FORD的领导编号,然后用这个查出来的员工号,在员工表里面找这个编号

也是单表查询

image-20221021213621642

做法3:多表查询,自连接

image-20221021213755256

因为笛卡尔积之后的表太大了,建立使用limit查询笛卡尔积之后的结果!, 这里因为自连接是两个同名字的表进行笛卡尔积,因为表名字不能相同,区分不开,需要取别名 把其中一张表起名为员工表,另一种为领导表

这里的连接条件是什么? 即:以什么条件过滤非法数据 员工表中自己的领导编号 = 领导表中自己的员工编号!

image-20221021214203056

领导也是员工! (打工人) 这里的mgr就是员工对应的领导的编号, empno就是员工自己的编号

然后再根据条件筛选数据: 员工名字为’FORD’

image-20221021214429467

我们只要我们需要的数据:

image-20221021214509043

4.子查询

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

1)单行子查询 (子查询的结果是单行)

单行子查询是指子查询只返回单列,单行数据

案例1:显示SMITH同一部门的员工

做法1:先拿到SMITH的部门号,然后再在EMP表里面筛选在SMITH所属部门的员工

image-20221021215503115

做法2:直接写成子查询:

image-20221021215650169

案例2:显示工资最高的员工的名字和工作岗位

做法:最高工资的可能有一个或者多个, 先找出emp表中最高的工资,然后在查找时,找工资为最高工资的员工

image-20221021225433041

案例3:显示工资高于平均工资的员工信息

做法:先求出emp表中的平均工资,然后在查找时找工资高于平均工资的员工

image-20221021225552262

(2)多行子查询

多行子查询是指子查询的结果返回单列多行数据.

in关键字 :只要在多行单列的结果中,则条件满足.

案例:查询和10号部门的工作岗位相同的雇员的名字,岗位,工资,部门号,但是不包含10号部门自己的员工

第一步:先拿到10号部门的岗位,如果有重复的话,还可以去重

image-20221021222049994

第二步:使用in关键字,在员工表当中找到在上面的这些岗位的人的信息

image-20221021222401033

第三步:再根据条件筛选:不包含10号部门自己的员工

image-20221021222454508

all关键字 :需要满足多行单列结果当中的所有,条件才满足

案例:显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

第一步:先把30号部门的员工的工资列出来,可能存在相同的,要进行去重

image-20221021222754770

第二步:根据条件在员工表筛选: 比部门30的所有员工的工资高的员工

错误写法:

image-20221021222945244

原因:后面的select子查询得到的是多条的记录

正确写法:使用all ,因为选出的是比30号部门所有人工资都要高的员工,所以最后的结果肯定没有30号部门的人

image-20221021223041882

写法2:题目的本质其实就是找到工资>30号部门的最高工资的员工

image-20221021223255046

其实可以直接使用>,是因为后面子查询得到的只有一条记录

any 关键字 :只要满足多行单列结果当中的任一一个,则条件满足

案例:显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号(包含自己部门的员工)

第一步:先拿出30号部门的员工的工资,可能存在相同的,要进行去重

select distinct sal from emp where deptno=30

第二步:找出比30号部门任意一个员工工资都要高的人, 此时需要使用any关键字

image-20221021223724235

如果此时还要加上一个条件:要在20号部门当中选出呢?

image-20221021223955874

写法2:题目的本质其实就是找到工资>30号部门的最低工资的员工

image-20221021223848784

所以30号部门的人也会被显示上

in:我是否属于你们的一员 all:我比你们都怎么样 any:我比你们任意一个人怎么样

(3)多列子查询

多行子查询是指返回单列多行数据,都是针对单列而言的,而多列子查询则是指查询返回多个列数据的子查询语句

案例:查询和SMITH这个员工的部门和岗位完全相同的所有雇员,不含SMITH本人

第一步:先拿到SMITH的部门和岗位,

image-20221021224324320

我们需要同时找到deptno和job两列数据,上面的多行子查询都只是包含一列数据, 此时得到的是单行多列的数据

第二步:进行筛选:,前面的得到的就是和SMITH在同一个部门同一个岗位的人, 然后用and条件再把SMITH筛选走

image-20221021224649459

可以认为,()就是表示MySQL内部实现的集合

在from子句中使用子查询

子查询语句出现在from子句中,这里要用到数据查询的技巧,把一个子查询当做一个临时表使用

案例1:显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

做法:要拿自己员工表的工资和平均工资作比较, 首先需要分组求出每个部门的平均工资

image-20221022093936985

可以把上面查到的内容当成一张表,它里面放着就是部门和它的平均工资,然后把这张表和员工表做笛卡尔积

image-20221022094139426

然后再过滤出非法的数据, 必须保证:员工的部门编号=平均工资表的部门编号才有意义, 子查询是先被执行的,先有的avg_tb表,然后才进行非法数据过滤,所以可以用别名

因为笛卡尔积穷举的时候,有多信息是无效的,需要进行过滤 部门号匹配的才是有效数据

image-20221022094336123

然后再根据条件筛选:员工的工资要比它所在部门的工资高 就是拿员工表的工资和平均工资表的平均工资比较,筛选出工资要高于自己部门平均工资的员工

image-20221022094601787

我们只想要某些信息:

image-20221022094754337

在上面的基础上.如果我们想把部门也显示出来呢?

把上面的表和部门表dept做笛卡尔积!然后再根据部门号要相等进行非法数据过滤

image-20221022095638591

案例2:查找每个部门工资最高的人的姓名、工资、部门、最高工资

先根据部门号分组,求出每个部门的最高工资,然后形成的这张表和员工表进行笛卡尔积, 根据 员工表的部门编号=最高工资表的部门编号进行过滤非法数据, 然后找到每个部门工资最高的人,可能有1个或者多个 (只要员工的工资=部门表的最高工资,该员工就是它部门的最高工资的人)

image-20221022100336868

案例3:显示每个部门的信息(部门名,编号,地址)和人员数量

第一步:先根据部门分组,统计每个部门的人数->需要使用count函数,然后得到的内容作为新表 和部门表做笛卡尔积, 根据: 新表的部门编号=部门表的部门编号进行过滤非法数据, 然后需要什么信息就显示什么信息

image-20221022100929604

做法2:直接把员工表和部门表做笛卡尔积, 然后根据部门编号要相同过滤非法数据, 然后按照部门进行分组,需要什么就显示什么

image-20221022101638091

5.合并查询

在实际应用中,为了合并多个select的执行结果,可以使用集合操作符 union,union all

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

案例:将工资大于2500或者职位是MANAGER的人找出来

工资和职位的信息都早员工表里面有,所以就是单表查询

写法1:直接根据条件在员工表进行筛选

image-20221022101806300

写法2:求两个表的并集

image-20221022102056340

2)union all 该操作符用于取得两个结果集的并集,当使用该操作符时,不会去掉结果集中的重复行,

案例: 将工资大于2500或者职位是MANAGER的人找出来

image-20221022102503338

信息列必须一样,否则会出问题

image-20221022102931787

关键字解释
union取并集,将多个 select 结果合并到一起,自动去掉重复行
union all取并集,将多个 select 结果合并到一起,但不去重

总结:

  • 子查询可以出现在两个地方(常规,重要)
  • 1. where字句中,作为筛选条件使用
  • 2. from字句中,用来和特定的表做笛卡尔积

以上就是《MySQL数据库复合查询操作实战》的详细内容,更多关于mysql的资料请关注golang学习网公众号!

声明:本文转载于:脚本之家 如有侵犯,请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>