![[MySQL] MySQL复合查询（多表查询、子查询）](images/news1.jpg)
前面我们学习了MySQL简单的单表查询。但是我们发现在很多情况下单表查询并不能很好的满足我们的查询需求。本篇文章会重点讲解MySQL中的多表查询、子查询和一些复杂查询。希望本篇文章会对你有所帮助。文章目录一、基本查询回顾二、多表查询2、1 笛卡尔积2、2 多表查询练习三、自连接四、子查询4、1 单行子查询4、2 多行子查询4、3 多列子查询4、4?在from子句中使用子查询五、合并查询??♂作者??♂??专栏MySQL????标题MySQL复合查询??寄语与其忙着诉苦不如低头赶路奋路前行终将遇到一番好风景在对本篇文章学习之前首先说明一下本篇文章所用到表的结构和内容。具体如下员工表emp部门表dept薪水表salgrade:一、基本查询回顾查询工资高于500或岗位为MANAGER的雇员同时还要满足他们的姓名首字母为大写的****J首先确定上述所需筛选的信息都在一行表中。其次分析出工资 500 or job MANAGER。我们先来查询出满足工资 500 or job MANAGER 的员工。具体如下同时我们还需要满足所查询到的员工的姓名首字母为大写的****J很明显是模糊查询。具体如下图按照部门号升序而雇员的工资降序排序这个需求就是简单的排序即可。注意所需排序的先后顺序。具体如下图使用年薪进行降序排序首先我们需要计算出来年薪。年薪 月薪sal*12 年终奖comm。那么我们直接就对其进行排序即可。但是需要注意的是NULL并不能参与计算这时候需要内置函数ifnull来进行判断其是否为NULL如果为NULL直接加0即可。具体如下显示工资最高的员工的名字和工作岗位我们可以很容易的查找到最高工资是多少然后再根据最高工资去找对应的员工的名字和工作岗位。具体如下图上述用了两条SQL语句确实能够查询出我们想要的结果。但是好像不太优雅。能不能用一条语句将所需结果查询出来呢答案是可以的。我们可以用子查询。什么是子查询呢在 MySQL 中子查询是指在一个查询语句中嵌套另一个查询语句。子查询可以用于过滤结果集、作为计算字段的数据源、与外部查询进行比较等多种情况。下面我们用子查询来解决这个需求。具体如下显示工资高于平均工资的员工信息这个题目的需求与上一个题目的需求很相似。我们可以先获取平均工资在查询比平均工资高的员工一样是用子查询。具体如下显示每个部门的平均工资和最高工资我们看到需求是每个部门那么首先肯定要按部门号进行分组。其次我们再查询每个部门的平均工资和最高工资。具体如下图显示平均工资低于2000的部门号和它的平均工资首先我们很容易可以找到各个部门的平均工资然后只需要再增加一个条件判断即可。具体如下显示每种岗位的雇员总数平均工资注意是每种岗位所以需要根据job进行分组查询。具体如下图二、多表查询2、1 笛卡尔积在MySQL中多表查询的笛卡尔积Cartesian Product是指在没有使用任何条件或连接的情况下将两个或多个表中的所有行进行组合的结果集。这种情况通常是在没有明确指定连接条件或者WHERE子句的情况下进行的查询但在实际应用中很少需要或者希望获得笛卡尔积结果。以下是一个简单的说明以及一个示例来解释笛卡尔积笛卡尔积的性质 笛卡尔积将参与查询的每个表的所有可能组合都返回即第一个表的每一行都会与第二个表的每一行进行组合生成的结果集的行数为各个表行数的乘积。示例我们现在将员工表和部门表进行笛卡尔积。具体如下其实我们也不难看出规律就是如下图但是往往我们用笛卡尔积所获取的表有很多的数据冗余。因为它会产生大量的冗余数据并且效率低下。为了避免得到笛卡尔积我们需要正确地使用连接条件例如使用where条件来筛选掉无用信息来明确指定表之间的关联关系。例如在对上述的员工表和部门表进行笛卡尔积时一个员工不可能会有多个部门号所以只有部门号相同的才算是有效的信息。最终有效结果如下图2、2 多表查询练习显示部门号为10的部门名员工名和工资我们发现员工表中并没有我们想要的部门名所以我们需要进行多表查询。需要将员工表和部门表进行合并查询。然后在查询部门号为10的部门名、员工名和工资。具体如下这里再说明一下上述SQL语句中 from 后 的 t1 和 t2 是对 emp 和 dept 表进行了重命名后续都可以用我们重命名的名字去代替表名字。其次是当我们将两张表拼接到一块后表中会有两个deptno所以我们在使用deptno时需要指定是那个表的。显示各个员工的姓名工资及工资级别我们发现工资等级只有在薪资表中有所以我们需要进行多表查询。当我们将员工表与薪水表进行笛卡尔积后发现很多数据是冗余的。只有薪资符合它所在的等级区间才是有效的。所以我们的查寻结果如下三、自连接我们上述讲解的是两张不同的表进行连接。那么可以自己与自己的表进行连接吗答案是可以的MySQL中的自连接是指在同一张表中进行连接操作。这种连接通常用于将表中的数据与自身进行比较或者组合。自连接可以通过将表与自身进行别名来实现从而使得查询可以使用表中的不同行进行比较和操作。我们看如下例子通过上图我们发现当进行自连接时如果不对表进行取别名那么将不能够进行自连接。必须对表进行取别名。自连接的使用场景是什么呢我们看如下例子。显示员工FORD的上级领导的编号和姓名mgr是员工领导的编号–empno员工是在emp表中上级领导也是员工也在emp表中。我们可能首先会想到用子查询来解决相对简单。具体如下但是我们也不难发现要查询的两个条件都是在emp表中那么我们就可以对emp表进行自连接。我们现在把两张表想象成一张表是员工表另一张表是领导表。我们现在需要的有效信息是员工表中的mgr 领导表中的empno即可。筛选出有效信息后在选择员工表中的员工为FORD。具体如下四、子查询子查询的概念在上文中已经解释过这里就不再解释。在子查询的子句中子句查询出的结果可能不止是一行记录也有可能是多行记录还有就是多列的情况。下面我们一一来分析一下。4、1 单行子查询显示SMITH同一部门的员工首先将SMITH的部门号查出然后再将该部门的所有员工筛选出即可。具体如下4、2 多行子查询查询和10号部门的工作岗位相同的雇员的名字岗位工资部门号但是不包含10自己的我们可以先查询出10号部门的工作岗位具体如下然后我们再进行筛选与上图中岗位相同的雇员的信息。当我们想用子查询时发现上图的岗位并不是一个那该怎么办呢这时候可以用到in关键字。in关键字用于检查某个值是否在一组值中。刚好符合我们的需求。具体如下显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号题目的要求找出比30号部门所有员工工资都好的员工信息。也就是比30号部门最高工资还要高的部门。我们首先找出30号部门的员工最高工资再筛选出薪资比它大的即可。具体如下我们也可以使用all关键字。all关键字用于比较外部查询和子查询返回的所有值。当使用all关键字时外部查询的值必须满足子查询返回的所有值的条件才会被选中。具体如下显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号包含自己部门的员工注意题目中的任意员工是指的只要有比部门30中的员工工资高的即满足条件。通俗理解找出比 部门号30的员工中最低工资 高的员工。这时可以用any关键字。any关键字用于比较外部查询和子查询返回的任意一个值。 当使用 any 时外部查询的值只需要满足子查询返回的任意一个值的条件即可被选中。具体如下4、3 多列子查询单行子查询是指子查询只返回单列单行数据多行子查询是指返回单列多行数据都是针对单列而言的而多列子查询则是指查询返回多个列数据的子查询语句。下面我们来看一个例子。查询和SMITH的部门和岗位完全相同的所有雇员不含SMITH本人我们可以先查询出来SMITH的部门和岗位。如下图我们发现要和SMITH的部门和岗位完全相同是多列的情况。这该怎么办呢我们看如下但是题目中还要求了不能包含SMITH本人。所以再把SMITH本人去掉即可。结果如下4、4在from子句中使用子查询我们之前学到的from后都是跟的表的名字。在from子句中使用子查询怎么理解呢使用子查询无非就是一个查询语句中嵌套了一个语句。我们就称之为子句。那么子句查询出来的结果我们也可看成一张表可与其他物理上实力存在的表进行连接。这就是在from子句中使用子查询的意思**。**下面我们结合实际例子来理解一下。显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资我们可以很容易得到每个部门的平均工资具体如下我们可以把上述所查询出来的结果当作一个表再与emp表进行连接即可。具体如下对我们来说有用的信息就是emp.deptno tmp.deptno。那么查询出来的结果如下现在我们只需要emp.sal tmp.平均工资 avgsal即可就是题目所要求的答案具体如下显示每个部门的信息部门名编号地址和人员数量我们发现部门名和地址都在部门表中而我们想要统计每个部门的人员数量还需要在emp表中统计。我们先来统计每个部门的人员数量具体如下我们再将上述查询的结果与部门dept表进行连接得到有用信息如下图此时我们在获取题目中的所需要的信息就相当容易了。具体如下图查找每个部门工资最高的人的姓名、工资、部门、最高工资首先我们可以很容易的得到每个部门的最高工资如下图但是怎么获取工资最高的人的信息呢这时候可以将我们查询的结果与emp表连接再获取该人的信息就可以了。具体如下五、合并查询在MySQL中合并查询指的是将多个查询结果合并成一个结果集的操作。这可以通过使用union、unionall等操作符来实现。以下是对每种操作符的详细解释unionunion操作符用于将两个或多个select语句的结果合并为一个结果集并自动去重。unionall与union类似但不会自动去重。下面我们来看几个实际例子来理解一下。将工资大于2500或职位是MANAGER的人找出来这个例子我们前面已经做过类似的不再过多解释直接看下图我们也可以先将工资大于2500的人找出来如下再找出来职位是MANAGER的。如下图最后用union将他们两个合并即可。具体如下我们再来用union all 将他们合并试试。具体如下图从上述的对比中我们也能看出来union是合并并且去重union all就只是合并。注意两个select合并的前提是必须所查询出来的列数是相同的。实际中union并不常用我们只是了解一下即可。
[MySQL] MySQL复合查询（多表查询、子查询）
前面我们学习了MySQL简单的单表查询。但是我们发现在很多情况下单表查询并不能很好的满足我们的查询需求。本篇文章会重点讲解MySQL中的多表查询、子查询和一些复杂查询。希望本篇文章会对你有所帮助。文章目录一、基本查询回顾二、多表查询2、1 笛卡尔积2、2 多表查询练习三、自连接四、子查询4、1 单行子查询4、2 多行子查询4、3 多列子查询4、4?在from子句中使用子查询五、合并查询??♂作者??♂??专栏MySQL????标题MySQL复合查询??寄语与其忙着诉苦不如低头赶路奋路前行终将遇到一番好风景在对本篇文章学习之前首先说明一下本篇文章所用到表的结构和内容。具体如下员工表emp部门表dept薪水表salgrade:一、基本查询回顾查询工资高于500或岗位为MANAGER的雇员同时还要满足他们的姓名首字母为大写的****J首先确定上述所需筛选的信息都在一行表中。其次分析出工资 500 or job MANAGER。我们先来查询出满足工资 500 or job MANAGER 的员工。具体如下同时我们还需要满足所查询到的员工的姓名首字母为大写的****J很明显是模糊查询。具体如下图按照部门号升序而雇员的工资降序排序这个需求就是简单的排序即可。注意所需排序的先后顺序。具体如下图使用年薪进行降序排序首先我们需要计算出来年薪。年薪 月薪sal*12 年终奖comm。那么我们直接就对其进行排序即可。但是需要注意的是NULL并不能参与计算这时候需要内置函数ifnull来进行判断其是否为NULL如果为NULL直接加0即可。具体如下显示工资最高的员工的名字和工作岗位我们可以很容易的查找到最高工资是多少然后再根据最高工资去找对应的员工的名字和工作岗位。具体如下图上述用了两条SQL语句确实能够查询出我们想要的结果。但是好像不太优雅。能不能用一条语句将所需结果查询出来呢答案是可以的。我们可以用子查询。什么是子查询呢在 MySQL 中子查询是指在一个查询语句中嵌套另一个查询语句。子查询可以用于过滤结果集、作为计算字段的数据源、与外部查询进行比较等多种情况。下面我们用子查询来解决这个需求。具体如下显示工资高于平均工资的员工信息这个题目的需求与上一个题目的需求很相似。我们可以先获取平均工资在查询比平均工资高的员工一样是用子查询。具体如下显示每个部门的平均工资和最高工资我们看到需求是每个部门那么首先肯定要按部门号进行分组。其次我们再查询每个部门的平均工资和最高工资。具体如下图显示平均工资低于2000的部门号和它的平均工资首先我们很容易可以找到各个部门的平均工资然后只需要再增加一个条件判断即可。具体如下显示每种岗位的雇员总数平均工资注意是每种岗位所以需要根据job进行分组查询。具体如下图二、多表查询2、1 笛卡尔积在MySQL中多表查询的笛卡尔积Cartesian Product是指在没有使用任何条件或连接的情况下将两个或多个表中的所有行进行组合的结果集。这种情况通常是在没有明确指定连接条件或者WHERE子句的情况下进行的查询但在实际应用中很少需要或者希望获得笛卡尔积结果。以下是一个简单的说明以及一个示例来解释笛卡尔积笛卡尔积的性质 笛卡尔积将参与查询的每个表的所有可能组合都返回即第一个表的每一行都会与第二个表的每一行进行组合生成的结果集的行数为各个表行数的乘积。示例我们现在将员工表和部门表进行笛卡尔积。具体如下其实我们也不难看出规律就是如下图但是往往我们用笛卡尔积所获取的表有很多的数据冗余。因为它会产生大量的冗余数据并且效率低下。为了避免得到笛卡尔积我们需要正确地使用连接条件例如使用where条件来筛选掉无用信息来明确指定表之间的关联关系。例如在对上述的员工表和部门表进行笛卡尔积时一个员工不可能会有多个部门号所以只有部门号相同的才算是有效的信息。最终有效结果如下图2、2 多表查询练习显示部门号为10的部门名员工名和工资我们发现员工表中并没有我们想要的部门名所以我们需要进行多表查询。需要将员工表和部门表进行合并查询。然后在查询部门号为10的部门名、员工名和工资。具体如下这里再说明一下上述SQL语句中 from 后 的 t1 和 t2 是对 emp 和 dept 表进行了重命名后续都可以用我们重命名的名字去代替表名字。其次是当我们将两张表拼接到一块后表中会有两个deptno所以我们在使用deptno时需要指定是那个表的。显示各个员工的姓名工资及工资级别我们发现工资等级只有在薪资表中有所以我们需要进行多表查询。当我们将员工表与薪水表进行笛卡尔积后发现很多数据是冗余的。只有薪资符合它所在的等级区间才是有效的。所以我们的查寻结果如下三、自连接我们上述讲解的是两张不同的表进行连接。那么可以自己与自己的表进行连接吗答案是可以的MySQL中的自连接是指在同一张表中进行连接操作。这种连接通常用于将表中的数据与自身进行比较或者组合。自连接可以通过将表与自身进行别名来实现从而使得查询可以使用表中的不同行进行比较和操作。我们看如下例子通过上图我们发现当进行自连接时如果不对表进行取别名那么将不能够进行自连接。必须对表进行取别名。自连接的使用场景是什么呢我们看如下例子。显示员工FORD的上级领导的编号和姓名mgr是员工领导的编号–empno员工是在emp表中上级领导也是员工也在emp表中。我们可能首先会想到用子查询来解决相对简单。具体如下但是我们也不难发现要查询的两个条件都是在emp表中那么我们就可以对emp表进行自连接。我们现在把两张表想象成一张表是员工表另一张表是领导表。我们现在需要的有效信息是员工表中的mgr 领导表中的empno即可。筛选出有效信息后在选择员工表中的员工为FORD。具体如下四、子查询子查询的概念在上文中已经解释过这里就不再解释。在子查询的子句中子句查询出的结果可能不止是一行记录也有可能是多行记录还有就是多列的情况。下面我们一一来分析一下。4、1 单行子查询显示SMITH同一部门的员工首先将SMITH的部门号查出然后再将该部门的所有员工筛选出即可。具体如下4、2 多行子查询查询和10号部门的工作岗位相同的雇员的名字岗位工资部门号但是不包含10自己的我们可以先查询出10号部门的工作岗位具体如下然后我们再进行筛选与上图中岗位相同的雇员的信息。当我们想用子查询时发现上图的岗位并不是一个那该怎么办呢这时候可以用到in关键字。in关键字用于检查某个值是否在一组值中。刚好符合我们的需求。具体如下显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号题目的要求找出比30号部门所有员工工资都好的员工信息。也就是比30号部门最高工资还要高的部门。我们首先找出30号部门的员工最高工资再筛选出薪资比它大的即可。具体如下我们也可以使用all关键字。all关键字用于比较外部查询和子查询返回的所有值。当使用all关键字时外部查询的值必须满足子查询返回的所有值的条件才会被选中。具体如下显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号包含自己部门的员工注意题目中的任意员工是指的只要有比部门30中的员工工资高的即满足条件。通俗理解找出比 部门号30的员工中最低工资 高的员工。这时可以用any关键字。any关键字用于比较外部查询和子查询返回的任意一个值。 当使用 any 时外部查询的值只需要满足子查询返回的任意一个值的条件即可被选中。具体如下4、3 多列子查询单行子查询是指子查询只返回单列单行数据多行子查询是指返回单列多行数据都是针对单列而言的而多列子查询则是指查询返回多个列数据的子查询语句。下面我们来看一个例子。查询和SMITH的部门和岗位完全相同的所有雇员不含SMITH本人我们可以先查询出来SMITH的部门和岗位。如下图我们发现要和SMITH的部门和岗位完全相同是多列的情况。这该怎么办呢我们看如下但是题目中还要求了不能包含SMITH本人。所以再把SMITH本人去掉即可。结果如下4、4在from子句中使用子查询我们之前学到的from后都是跟的表的名字。在from子句中使用子查询怎么理解呢使用子查询无非就是一个查询语句中嵌套了一个语句。我们就称之为子句。那么子句查询出来的结果我们也可看成一张表可与其他物理上实力存在的表进行连接。这就是在from子句中使用子查询的意思**。**下面我们结合实际例子来理解一下。显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资我们可以很容易得到每个部门的平均工资具体如下我们可以把上述所查询出来的结果当作一个表再与emp表进行连接即可。具体如下对我们来说有用的信息就是emp.deptno tmp.deptno。那么查询出来的结果如下现在我们只需要emp.sal tmp.平均工资 avgsal即可就是题目所要求的答案具体如下显示每个部门的信息部门名编号地址和人员数量我们发现部门名和地址都在部门表中而我们想要统计每个部门的人员数量还需要在emp表中统计。我们先来统计每个部门的人员数量具体如下我们再将上述查询的结果与部门dept表进行连接得到有用信息如下图此时我们在获取题目中的所需要的信息就相当容易了。具体如下图查找每个部门工资最高的人的姓名、工资、部门、最高工资首先我们可以很容易的得到每个部门的最高工资如下图但是怎么获取工资最高的人的信息呢这时候可以将我们查询的结果与emp表连接再获取该人的信息就可以了。具体如下五、合并查询在MySQL中合并查询指的是将多个查询结果合并成一个结果集的操作。这可以通过使用union、unionall等操作符来实现。以下是对每种操作符的详细解释unionunion操作符用于将两个或多个select语句的结果合并为一个结果集并自动去重。unionall与union类似但不会自动去重。下面我们来看几个实际例子来理解一下。将工资大于2500或职位是MANAGER的人找出来这个例子我们前面已经做过类似的不再过多解释直接看下图我们也可以先将工资大于2500的人找出来如下再找出来职位是MANAGER的。如下图最后用union将他们两个合并即可。具体如下我们再来用union all 将他们合并试试。具体如下图从上述的对比中我们也能看出来union是合并并且去重union all就只是合并。注意两个select合并的前提是必须所查询出来的列数是相同的。实际中union并不常用我们只是了解一下即可。
