MySql常见复合查询(重点)

红太狼 2024-03-31 08:04 102阅读 0赞

复合查询(重点)

多表查询

实际开发中往往数据来自不同的表,所以需要多表查询。本节我们用一个简单的公司管理系统,有三张表 EMP,DEPT,SALGRADE来演示如何进行多表查询。

  • 显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表,因此要联合查询

在这里插入图片描述

from后面跟两张表,多表查询,实际上是产生两张表的笛卡尔积(排列组合),我们把所有相关的数据,聚合到了一张表,接下来的问题,就变成了一个单表查询!! !;

在这里插入图片描述
其实我们只要emp .deptno = dept.deptno字段的记录;(排列组合中的有效记录)

  1. select EMP.ename, EMP.sal, DEPT.dname from EMP, DEPT where EMP.deptno = DEPT.deptno;
  • 显示部门号为10的部门名,员工名和工资

    select dname,ename,sal from EMP, DEPT where EMP.deptno = DEPT.deptno and EMP=10;

  • 显示 各个 员工的 姓名,工资,及工资级别

工资级别表格式 : SALGRADE :

字段1 grade级别

字段2,3 满足这个级别的区间losal , hisal;

  1. select ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and hisal;

自连接

自连接是指在同一张表连接查询 (同一张表 做笛卡尔积)

  • 显示员工FORD的上级领导的编号和姓名(mgr是员工领导的编号–empno

    — 使用的子查询(select 嵌套,下面介绍):
    select empno,ename from EMP where empno = (select mgr from EMP where ename=’FORD’);

    — 使用多表查询(自查询):
    select t2.empno,t2.ename from EMP t1,EMP t2 where t1.mgr=t2.empno and t1.ename=’FORD’;

子查询

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

单行子查询

返回一行记录的子查询()

  • 显示SMITH同一部门的员工

    select * from EMP where deptno = (select deptno from EMP where ename=’SMITH’);

多行子查询

返回多行记录的子查询()

  • in关键字; 查询和10号部门的工作岗位相同的雇员的名字,岗位,工资,部门号,但是不包含10部门自己的

    select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10) and deptno<>10; — 嵌套进的子select,返回多行

  • all关键字;显示工资比 部门30的 所有 员工的工资高的 员工的姓名、工资和部门号

    select ename,sal,deptno from EMP where sal > all(select sal from EMP where deptno=30);

  • any关键字;显示工资比 部门30的 任意 员工的工资高的员工的姓名、工资和部门号(包含30自己部门的员工)

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

多列子查询

单行子查询 和 多行子查询,返回的都是1列(n行), 多列子查询返回多列多行;

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

  1. select * from EMP where (deptno,job) = all(select depthno,job from EMP where ename = 'SMITH') and ename != 'SMITH'; -- 很明显,多列子查询的话,因为多列(多字段),所以 = 的左边需要上(字段1,字段2) = (select *......);来匹配

在from子句中使用子查询

子查询语句出现在from子句中。这里要用到数据查询的技巧把一个子查询当做一个临时表 来进行 笛卡尔积 多表查询 使用

我们可以理解为MySql每次查询的结果都是一个临时表 “表结构”!,那么这个临时表也能当做一张表用于多表查询;

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

    — 下面这个查询出来的 每个部门平均工资 的临时表 可以当成一张表进行 笛卡尔积 多表查询;
    select depthno agv(sal) as agvs from EMP group by depthno;

  1. select ename,depthno,sal,agvs from EMP , (select depthno agv(sal) as agvs from EMP group by depthno)as tmp where tmp.depthno = EMP.depthno and EMP.sal>tmp.agvs;
  • 查找 每个部门工资最高 的人的姓名、工资、部门、最高工资

    — 下面这个查询出来的 每个部门max工资 的临时表 可以当成一张表进行 笛卡尔积 多表查询;
    select depthno max(sal) as msal from EMP group by depthno;

  1. select ename,sal,depthno,msal from EMP ,(select depthno max(sal) as msal from EMP group by depthno) as tmp where tmp.depthno = EMP.depthno and EMP.sal=tmp.msal;
  • 显示 每个部门的信息(部门名,编号,地址)和 人员数量

    — 方法1:使用多表
    select DEPT.dname,DEPT.depthno,DEPT.loc,count(*) from DEPT,EMP
    where EMP.depthno=DEPT.depthno;
    group by DEPT.dname,DEPT.depthno,DEPT.loc; — 细节:一般group by之后的字段,在select中除了聚合函数以外,需要一一匹配,否则出现歧义,报错

    — 方法1:使用子查询
    — 将下面的 统计每个部门的人数 表看成临时表;
    select count(*) from EMP group by depthno;

    select dname,DEPT.depthno,loc , cnt from DEPT,(select count(*) as cnt from EMP group by depthno)as tmp
    where tmp.depthno=DEPT.depthno;

总结一下,复合查询还是比一般的查询要复杂,但是也是最重要,最常用的,而且套路很深!

合并查询

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

union

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

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

    select ename, sal, job from EMP where sal>2500 union

    1. select ename, sal, job from EMP where jos='MANAGER';
    2. -- 结果去掉了重复记录;
    3. -- 等价于 select ename, sal, job from EMP where sal>2500 OR jos='MANAGER';
    4. +-------+---------+-----------+
    5. | ename | sal | job |
    6. +-------+---------+-----------+
    7. | JONES | 2975.00 | MANAGER |
    8. | BLAKE | 2850.00 | MANAGER |
    9. | SCOTT | 3000.00 | ANALYST |
    10. | KING | 5000.00 | PRESIDENT |
    11. | FORD | 3000.00 | ANALYST |
    12. | CLARK | 2450.00 | MANAGER |
    13. +-------+---------+-----------+

union all

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

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

    mysql> select ename, sal, job from EMP where sal>2500 union all
    -> select ename, sal, job from EMP where job=’MANAGER’;

    1. -- 结果保留了重复记录;

    +———-+————-+—————-+
    | ename | sal | job |
    +———-+————-+—————-+
    | JONES | 2975.00 | MANAGER |
    | BLAKE | 2850.00 | MANAGER | — BLAKE
    | SCOTT | 3000.00 | ANALYST |
    | KING | 5000.00 | PRESIDENT |
    | FORD | 3000.00 | ANALYST |
    | JONES | 2975.00 | MANAGER |
    | BLAKE | 2850.00 | MANAGER | — BLAKE
    | CLARK | 2450.00 | MANAGER |
    +———-+————-+—————-+

表的内连和外连(重点)

内连接

内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选(筛选出有意义的记录),下面要介绍的join on语法就是简化了我们的使用;

我们前面学习的查询都是内连接,也是在开发过程中使用的最多的连接查询。

语法:

  1. select 字段 from 1 inner join 2 on 连接条件 and 其他条件;

案例:显示SMITH的名字和部门名称

  1. -- 多表 + where写法
  2. select ename,dname from EMP,DEPT where EMP.depthno=DEPT.depthno and ename='SMITH';
  3. -- 用标准的内连接写法
  4. select ename,dname from EMP inner join DEPT on EMP.depthno=DEPT.depthno and ename='SMITH';

外连接

左外连接

如果联合查询,左侧的表完全显示我们就说是左外连接。

语法:

  1. select 字段名 from 表名1 left join 表名2 on 连接条件

原表结构:

在这里插入图片描述

  • 查询所有学生的成绩,!!!如果这个学生没有成绩,也 要 将 学生的个人信息显示出来!!!

    — 当左边表和右边表没有匹配时,也会显示左边表的数据

    select * from stu left join exam on stu.id=exam.id;

(左外连接)左侧的信息保留全

在这里插入图片描述

右外连接

如果联合查询,右侧的表完全显示我们就说是左外连接。

语法:

  1. select 字段名 from 表名1 right join 表名2 on 连接条件

原表结构:
在这里插入图片描述

  • 对stu表和exam表联合查询,!!!把所有的成绩都显示出来,即使这个成绩没有学生与它对应,也要显示出来!!!

    — 当右边表和左边表没有匹配时,也会显示左边表的数据
    select * from stu right join exam on stu.id=exam.id;

在这里插入图片描述

发表评论

表情:
评论列表 (有 0 条评论,102人围观)

还没有评论,来说两句吧...

相关阅读

    相关 MySql常见复合查询(重点)

    复合查询(重点) 多表查询 > 实际开发中往往数据来自不同的表,所以需要多表查询。本节我们用一个简单的公司管理系统,有三张表 EMP,DEPT,SALGRADE来演

    相关 ElasticSearch 复合查询

    使用bool查询 接收以下参数: must:文档必须匹配这些条件才能被包含进来。 must \_not:文档必须不匹配这些条件才能被包含进来。 should:如

    相关 MySQL复合查询

    前面我们讲解的mysql表的查询都是对一张表进行查询,在实际开发中这远远不够。 1.多表查询 实际开发中往往数据来自不同的表,所以需要多表查询。本节我们用一个简单的公司