【ONE·MySQL || 复合查询】

总言

  主要内容：主要介绍一些复杂的查询，涉及多表查询、交叉连接、内连接、外连接、子查询、合并查询等。
  
  

  
  
  
  
  
  

1、基本查询回顾

1.0、博文说明

  演示使用的雇员信息表来自oracle 9i的经典测试表： 该表具体数据见基本查询章节5.2。

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

  
  
  
  

1.1、一些例题

1.1.1、查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J

  

mysql> select * from emp where (sal>500 or job='MANAGER') and (ename like 'J%');
mysql> select * from emp where (sal>500 or job='MANAGER') and substring(ename,1,1)='J';

  
  
  

1.1.2、按照部门号升序而雇员的工资降序排序（order子句）

  

mysql> select * from emp order by deptno asc, sal desc;

  
  
  

1.1.3、使用年薪进行降序排序（NULL值说明、ifnull函数使用）

  
  一个问题说明：大多数MySQL函数和运算在处理NULL时会返回NULL。

  
  写法如下：

mysql> select *,sal*12 + ifnull(comm,0.00) as '年薪' from emp order by 年薪 desc;

  
  
  

1.1.4、显示工资最高的员工的名字和工作岗位（引入子查询）

  一个问题说明：在MySQL中，聚合函数（如 SUM(), COUNT(), AVG(), MAX(), MIN() 等）通常用于对一组值进行计算。这些函数在处理数据时，会忽略非聚合列，不能与其他的列属性一起在SELECT语句中使用，否则会报错。（原因：处理数据时的目的和方式不同。聚合函数用于对一组值进行计算，而其他的列属性则用于选择特定的数据列。）

  如果希望在查询结果中同时显示聚合函数的结果和其他列属性的值，则可以使用子查询或者联接查询来实现。

mysql> select ename,job from emp where sal=(select max(sal) from emp);

  
  
  

1.1.5、显示工资高于平均工资的员工信息（引入子查询）

  预备演示：有了上述1.1.4的基础，这里情况一致。

  使用子查询，结果如下：

mysql> select * from emp where sal > (select avg(sal) from emp);

  
  
  

1.1.6、显示每个部门的平均工资和最高工资（group by子句）

  关于group by子句在先前的基本查询中介绍过，这里不再详述。

mysql> select deptno,avg(sal) as '平均工资', max(sal) as '最高工资' from emp group by deptno;

  
  
  

1.1.7、显示平均工资低于2000的部门号和它的平均工资（group by子句中having条件筛选）

  同样在之前的基本查询中介绍过，不再赘述。

mysql> select deptno,avg(sal) as '平均工资', max(sal) as '最高工资' from emp group by deptno having 平均工资<2000;

  
  
  

1.1.8、显示每种岗位的雇员总数，平均工资

  

mysql> select job,count(job) as 人数 ,avg(sal) as 平均工资 from emp group by job;

  
  
  
  
  
  

2、多表查询/关联查询

2.1、基本介绍

2.1.1、是什么和为什么

  
  1）、理解多表关系
  在数据表中，各表结构之间存在着各种关系（一对一、一对多、多对多）。
  
  一对一关系： 一个表中的记录与另一个表中的唯一记录相关联。
  示例： 学生与学生基本信息的关系。一个学生对应拥有一个基本信息情况，一个学生基本信息也只能反应一个学生的情况。
  
  一对多关系： 一个表中的记录可以与另一个表中的多个记录相关联。
  示例： 学生与班级的关系。一个班级对应多个学生，一个学生只能对应一个班级。
  
  多对多关系： 一个表中的记录可以与另一个表中的多个记录相关联，反之亦然。这通常通过中间表来实现。
  示例： 学生与课程的关系。一个学生可以选修多门课程，一门课程也可以给多个学生选择。
  
  
  2）、多表查询和多表连接
  实际开发中往往数据来自不同的表，所以需要多表查询。
  
  多表查询是指在一个查询中涉及多个表，需要通过一定的条件，将不同表中的数据组合起来，从而获得所需的结果。 它是从多个表中检索数据的过程，可能涉及到多个表的聚合、连接、过滤和排序等操作，主要用于处理复杂的数据关系，将分散在多个表中的数据整合在一起，以完成特定的业务需求。
  
  而为了进行多表查询，往往需要将两个或多个表根据某些关联条件连接起来，形成一个更大的逻辑表。 多表连接是基于表中某些列的相似值，将不同表中的行组合在一起的过程，可以形成一个更完整的数据视图。多表连接是数据库系统提供的一种强大的功能，能够方便地处理复杂的数据关系，提高数据查询的效率和准确性。
  
  
  以下为一个例子：

  
  
  

2.1.2、理解笛卡尔积（交叉连接）

  笛卡尔乘积： 是一个数学运算。假设有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

  SQL92中，笛卡尔积也称为交叉连接 （CROSS JOIN） 。在 SQL99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。

  因此，对select * from dept,emp;，求其笛卡尔积，如下：

  实际员工记录只有14条，组合结果却为14×4=56条。在SQL中，出现这样的错误称为出现笛卡尔积错误，即：在进行表连接时没有指定明确的关联条件，导致生成了两个表的笛卡尔积。

  笛卡尔积本身是一种运算，并没有错。只是由于其是两个表的所有可能组合，结果集通常会非常大，且存在一些无实际意义或者相矛盾的记录，这可能会导致查询效率低下，甚至耗尽系统资源。

  
  
  
  

2.1.3、解决多表查询的思路（附加：基础演示例子）

  1、先读题，确定都和哪些表有关；
  2、“无脑”组合形成一张表（目的：从多张表转换成一张表）；
  3、将多表查询看做成为一张表的查询。

  
  
  

2.1.3.1、显示雇员名、雇员工资以及所在部门的名字

  

mysql> select emp.ename, emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno;

  
  
  

2.1.3.2、显示部门号为10的部门名，员工名和工资

  

select dept.dname,emp.ename, emp.sal from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;

  
  
  

2.1.3.3、显示各个员工的姓名，工资，及工资级别

  以薪资表、员工表建立笛卡尔积。那么，当前场景中，如何写关联条件？

  分析这两张表的关联，有一个已知的条件，即员工薪资必须在其对应的最低薪资和最高薪资范围内。可根据此作为关联条件进行筛选。

  根据题目要求，查询相应信息。

mysql> select emp.ename,emp.sal,salgrade.grade from emp,salgrade where sal between losal and hisal;

  
  
  
  
  

2.2、连接查询

2.2.1、基本介绍与分类说明

  1）、什么是连接查询？
  连接查询是关系数据库中最主要的查询方式之一，主要用于将两个或多个表中的数据按照一定条件组合起来。通过连接操作，用户可以查询存放在多个表中的不同实体的信息，以获得需要的数据。
  
  2）、常见连接查询有哪些？
  根据功能划分，连接查询一般可分为以下几类:

  交叉连接： 笛卡尔积。

  内连接： 可分为等值连接、非等值连接、自连接。 (还可分为隐式[无join]和显式[有join] )

  外连接： 左外连接(左连接)、右外连接(右连接)、全连接。

  
  
  外连接： 假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表， 一张表是副表，主要查询主表中的数据，捎带着查询副表。当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与匹配。
  
  
  
  
  

2.2.2、内连接（主要介绍等值连接）

2.2.2.1、基本介绍

  1）、介绍内连接
  内连接： 假设A、B两表进行连接，两张表平等，没有主副之分。内连接会对表中条件匹配的项进行组合，在结果中只会出现同时在左表和右表出现的项，即，只返回在两个表中都有匹配的数据。

  语法关键字： INNER JOIN 或 JOIN（JOIN等价于INNER JOIN，这里描述的是等值连接）。

  （实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，我们前面学习的查询都是内连接，也是在开发过程中使用的最多的连接查询。）
  
  
  2）、INNER JOIN子句介绍
  MySQL中，INNER JOIN子句将一个表中的行与其他表中的行进行匹配，用于查询包含两个表中列的行。该子句是SELECT查询中的可选部分，它出现在FROM语句之后。

SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...
WHERE where_conditions; 

---简洁写法：
select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件；

  注意细节：
  1、连接条件出现在INNER JOIN子句的 ON关键字之后 。连接条件是将主表中的行与其他表中的行进行匹配的规则。
  2、可以这样理解，对于t1表中的每一行，INNER JOIN子句将其与t2表的每一行进行比较，以检查它们是否都满足连接条件。当满足连接条件时，INNER JOIN将返回一个新行，该行由两个表t1和t2表组成。如果未找到匹配项，查询将返回空结果集。当使用2个以上的表时，用法一样。
  
  
  

2.2.2.2、显示SMITH的名字和部门名称

  从结果上看，内连接相当于对两个表进行笛卡尔积操作，然后根据连接条件筛选出满足条件的行。

---显示写法
mysql> select ename,dname from emp inner join dept on emp.deptno=dept.deptno where ename='SMITH';

---隐式写法
mysql> select ename,dname from emp,dept where emp.deptno=dept.deptno and ename='SMITH';

  
  
  
  

2.2.3、自连接（内连接的一种特殊形式）

2.2.3.1、基本介绍

  自连接： 内连接的一种特殊形式，将一张表看做两张表， 自己连接自己。实质就是等值连接，只不过是连接表本身。

SELECT A.column1, B.column2  
FROM table AS A, table AS B  
WHERE A.common_field = B.common_field;

  为了区分同一表的不同实例，需要为表指定别名。 如上，将同一个表别名为A和B，并使用WHERE子句指定它们之间的连接条件。通过这种方式，可以将表中的相关行进行比较和组合。
  
  
  

2.2.3.2、显示员工FORD的上级领导的编号和姓名

  

mysql> select e2.empno,e2.ename from emp as e1, emp as e2 where e1.ename='FORD' and e1.mgr=e2.empno;


mysql> select e2.empno,e2.ename from emp as e1, emp as e2 where e1.mgr=e2.empno and e1.ename='FORD';

  
  
  
  
  

2.2.4、外连接（主要介绍左外连接、右外连接）

2.2.4.1、基本介绍

  左外连接： 左边的是主表，左表数据全部显示，右表显示符合0N后的条件的数据，不符合的用NULL代替。语法关键字为left join或left outrer join (outer可以省略)
  
  右外连接： 右边边的是主表，右边表数据全部显示，左边表显示符合ON后的条件的数据，不符合的用NULL代替。语法关键字为right join或right outrer join (outer可以省略)。

  
  

2.2.4.2、对stu表和exam表联合查询

  左外连接会返回左表的所有行，即使右表中没有匹配的行。如果左表的某行在右表中没有匹配行，则在结果集的右表相关列中，这些行的值将为NULL。

  右外连接与左外连接相对，它返回右表的所有行。如果右表的某行在左表中没有匹配行，则结果集中左表相关列的值将为NULL。
  

  
  
  
  
  

2.3、子查询

2.3.1、基本介绍

  子查询指一个查询语句嵌套在另一个查询语句内部的查询。这个特性从MySQL 4.1开始引入，MySQL 支持 SQL 标准支持的所有子查询表单和操作，但它必须用括号括起来。

SELECT column_list (s) FROM  table_name  
WHERE  column_name OPERATOR  
   (SELECT column_list (s)  FROM table_name [WHERE]) 

  包含子查询的查询称为外部查询，子查询称为内部查询。执行的内部查询首先将结果提供给外部查询，然后执行主/外部查询。

  SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。
  
  
  
  

2.3.2、单行单列子查询（标量子查询）

  说明： 子查询（内部查询）返回的结果是单一的值（数字、字符串、日期等），这个单一的值可以用在主查询的WHERE或SELECT子句中作为一个条件或一个值。这种子查询称为标量子查询。
  常用的操作符： = 、<> 、 > 、 >= 、 < 、 <=。
  
  
  PS：上述1.1.5和1.1.6两个例子即这里的单行单列子查询。
  

2.3.2.1、显示与SMITH同一部门的员工

  使用子查询，情况如下：

mysql> select * from emp where deptno=(select deptno from emp where ename='SMITH');

  
  
  使用多表查询（自连接），情况如下：

mysql> select e2.* from emp as e1, emp as e2 where e1.ename='SMITH' and e1.deptno=e2.deptno;

  
  
  
  
  

2.3.3、多行子查询

  MySQL中，多行子查询是在子查询（内部查询）中，返回多行结果，并在主查询中使用这些结果。
  
  常用操作符如下：

  1、IN操作符： 使用时，会与子查询中的每一个值进行比较，与任意一个值相同就会被返回。

  2、ANY操作符： 需要和单行比较操作符一起使用，和子查询返回的某一个值比较。

>any：表示大于子查询所返回的结果集中的任意一个值；
=any：表示等于子查询所返回的结果集中的任意一个值；
<any：表示小于子查询所返回的结果集中的任意一个值；

  3、ALL操作符： 需要和单行比较操作符一起使用， 和子查询返回的所有值比较。

>all：表示大于子查询所返回的结果集中的全部值；
=all：表示等于子查询所返回的结果集中的全部值；
<all：表示小于子查询所返回的结果集中的全部值；

  4、SOME操作符： 实际上是ANY的别名，作用相同，一般常使用ANY
  
  
  
  

2.3.2.1、演示 in 关键字：查询拥有“和10号部门相同工作岗位”的其它部门的信息（包含雇员姓名、岗位、工资，部门号）

  IN操作符用于判断主查询中的某个值是否存在于子查询的结果集中。该操作符实际上是将主查询中的每一行与子查询结果集中的每一行进行比较，如果主查询中的值在子查询结果集中存在，则返回匹配的行。
  

---两种写法：（不同点在于!=和<>）

mysql> select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10) and deptno != 10;

mysql> select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10) and deptno<>10;

  
  

2.3.2.2、演示 all 关键字：显示“工资比30号部门的所有员工工资都高”的员工信息（包含姓名、工资和部门号）

  ALL操作符用于将主查询中的值与子查询结果集中的所有值进行比较。表示主查询中的值必须大于（或小于、等于等）子查询结果集中的所有值，才返回匹配的行。（所有值满足比较条件）
  

---不使用 any运算符子查询方式：使用 max函数
mysql> select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);

---使用 any运算符的子查询方式:
mysql> select ename,sal,deptno from emp where sal > all(select sal from emp where deptno=30);

  
  
  
  

2.3.2.3、演示 any 关键字：显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

  ANY操作符用于将主查询中的值与子查询结果集中的任意一个值进行比较。只要主查询中的值大于（或小于、等于等）子查询结果集中的任意一个值，就返回匹配的行。（任意一个值满足比较条件）
  

mysql> select ename,sal,deptno from emp where sal > any(select sal from emp where deptno=30);

mysql> select ename,sal,deptno from emp where sal > (select min(sal) from emp where deptno=30);

  
  
  
  

2.3.4、多列子查询

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

2.3.4.1、查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人

  
  
  
  

2.3.5、在from子句中使用子查询（派生表子查询）

  在SQL中，子查询不仅可以出现在WHERE、SELECT或HAVING子句中，还可以出现在FROM子句中。当子查询出现在FROM子句中时，它被称为派生表（Derived Table）或内联视图（Inline View）。 派生表子查询允许基于一个查询的结果来执行另一个查询。
  

SELECT column1, column2, ...  
FROM (  
    SELECT columnA, columnB, ...  
    FROM table1  
    WHERE condition  
) AS derived_table_alias  
WHERE another_condition;

  如上，from后跟随的内部查询（子查询）会先执行，并返回一个结果集。这个结果集被当作一个临时表（即派生表） 来使用，并且被赋予了一个别名（derived_table_alias），以便在外部查询中引用。外部查询则基于这个派生表来执行。
  
  注意事项：
  1、派生表是临时的，只存在于包含它的查询执行期间。查询结束后，派生表将被自动释放。
  2、派生表必须有一个别名，以便在外部查询中引用它。
  
  

2.3.5.1、显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

  

  

mysql> select emp.ename,emp.deptno,emp.sal,tmp.平均工资 from emp,(select deptno,avg(sal) as '平均工资' from emp group by deptno) as tmp where emp.deptno=tmp.deptno and emp.sal>平均工资;

---换行便于观察：这里写法风格不定，可根据自己习惯来。
mysql> select emp.ename,emp.deptno,emp.sal,tmp.平均工资 from emp,
    -> (select deptno,avg(sal) as '平均工资' from emp group by deptno) as tmp
    -> where emp.deptno=tmp.deptno and emp.sal>平均工资;

  
  
  
  

2.3.5.2、查找每个部门工资最高的人的姓名、工资、部门、最高工资

  方法同上。

mysql> select ename,sal,emp.deptno,tmp.最高工资 from emp,
    -> (select deptno,max(sal) as '最高工资' from emp group by deptno) as tmp
    -> where emp.deptno=tmp.deptno and sal=最高工资;

  
  将上述内容扩展：在查找到每个部门工资最高的员工后，显示这些员工的薪资等级信息。

----这里换行便于观察理解结构

mysql> select * from salgrade,
    -> (select emp.ename,emp.deptno,emp.sal,tmp.部门最高工资 from emp,
    -> (select deptno,max(sal) as '部门最高工资' from emp group by deptno) as tmp
    -> where emp.deptno=tmp.deptno and sal=部门最高工资) as res
    -> where sal between losal and hisal;


mysql> select * from salgrade,
    -> (select emp.ename,emp.deptno,emp.sal,tmp.部门最高工资 from emp,(select deptno,max(sal) as '部门最高工资' from emp group by deptno) as tmp where emp.deptno=tmp.deptno and sal=部门最高工资) as res 
    -> where sal between losal and hisal;

  
  
  
  

2.3.5.3、显示每个部门的信息（部门名，编号，地址）和人员数量

  使用子查询：

mysql> select dname,dept.deptno,loc,人员数量 from dept,
    -> (select deptno,count(*) as 人员数量 from emp group by deptno) as tmp
    -> where dept.deptno=tmp.deptno;

  
  使用多表查询：

mysql> select dname,dept.deptno,loc,count(*) as '人员数量' from emp,dept 
    -> where emp.deptno=dept.deptno group by dept.deptno,dept.dname,dept.loc;

  
  
  
  

2.4、联合查询、合并查询

  MySQL中的联合查询（UNION）和合并查询（UNION ALL）都是用于组合多个查询结果的操作。
  UNION：会自动去除重复的行，要求每个SELECT语句中的列数和数据类型相匹配。
  UNION ALL：不会自动去除重复的行，如果需要去除重复的行，可以在每个SELECT语句后面使用DISTINCT关键字。
  
  
  

2.4.1、将工资大于2500或职位是MANAGER的人找出来

  可以使用逻辑运算符达成该查询：

mysql> select * from emp where sal>2500 or job='MANAGER';

  
  使用union或union all的结果如下：

mysql> select * from emp where sal > 2500 union select * from emp where job='MANAGER';
mysql> select * from emp where sal > 2500 union all select * from emp where job='MANAGER';

  
  
  

  
  
  

  
  
  

Fin、共勉。