【MySQL】MySQL复合查询--多表查询自连接子查询 - 副本

news/2024/4/19 16:58:52/
文章目录
  • 1.基本查询回顾
  • 2.多表查询
  • 3.自连接
  • 4.子查询
    • 4.1单行子查询
    • 4.2多行子查询
    • 4.3多列子查询
    • 4.4在from子句中使用子查询
    • 4.5合并查询
      • 4.5.1 union
      • 4.5.2 union all

1.基本查询回顾

表的内容如下:

mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)mysql> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
  • 查询工资高于500或岗位为MANAGER的雇员,同时还要满足他们的姓名首字母为大写的J

    // 使用模糊查询
    select * from emp where (sal>500 or job=‘MANAGER’) and ename like ‘J%’;
    // 使用函数
    select * from emp where (sal>500 or job=‘MANAGER’) and substring(ename,1,1)=‘J’;
    mysql> select * from emp where (sal>500 or job=‘MANAGER’) and ename like ‘J%’;
    ±-------±------±--------±-----±--------------------±--------±-----±-------+
    | empno | ename | job | mgr | hiredate | sal | comm | deptno |
    ±-------±------±--------±-----±--------------------±--------±-----±-------+
    | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
    | 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
    ±-------±------±--------±-----±--------------------±--------±-----±-------+
    2 rows in set (0.00 sec)

    mysql> select * from emp where (sal>500 or job=‘MANAGER’) and substring(ename,1,1)=‘J’;
    ±-------±------±--------±-----±--------------------±--------±-----±-------+
    | empno | ename | job | mgr | hiredate | sal | comm | deptno |
    ±-------±------±--------±-----±--------------------±--------±-----±-------+
    | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
    | 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
    ±-------±------±--------±-----±--------------------±--------±-----±-------+
    2 rows in set (0.00 sec)

在这里插入图片描述

  • 按照部门号升序而雇员的工资降序排序

    select * from emp order by deptno asc, sal desc;
    mysql> select * from emp order by deptno asc,sal desc;
    ±-------±-------±----------±-----±--------------------±--------±--------±-------+
    | empno | ename | job | mgr | hiredate | sal | comm | deptno |
    ±-------±-------±----------±-----±--------------------±--------±--------±-------+
    | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
    | 007782 | CLACK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
    | 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
    | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
    | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
    | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
    | 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
    | 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
    | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
    | 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
    | 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
    | 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
    | 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
    | 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
    ±-------±-------±----------±-----±--------------------±--------±--------±-------+

在这里插入图片描述

  • 使用年薪进行降序排序

    年薪等于工资*12+奖金

    需要对奖金进行判断,如果奖金为null,则奖金为0

    select ename, sal*12+ifnull(comm,0) as ‘年薪’ from emp order by 年薪 desc;

    mysql> select ename,sal*12+ifnull(comm,0) as ‘年薪’ from emp order by 年薪 desc;
    ±-------±---------+
    | ename | 年薪 |
    ±-------±---------+
    | SMITH | 9600.00 |
    | ALLEN | 19500.00 |
    | WARD | 15500.00 |
    | JONES | 35700.00 |
    | MARTIN | 16400.00 |
    | BLAKE | 34200.00 |
    | TEST | 29400.00 |
    | SCOTT | 36000.00 |
    | KING | 60000.00 |
    | TURNER | 18000.00 |
    | ADAMS | 13200.00 |
    | JAMES | 11400.00 |
    | FORD | 36000.00 |
    | MILLER | 15600.00 |
    ±-------±---------+
    14 rows in set (0.00 sec)

在这里插入图片描述

  • 显示工资最高的员工的名字和工作岗位

    这里使用分组查询即可,先查出最高的工资,然后查询工资等于最高工资的员工的姓名和工作岗位

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

    mysql> select ename,job from emp where sal = (select max(sal) from emp);
    ±------±----------+
    | ename | job |
    ±------±----------+
    | KING | PRESIDENT |
    ±------±----------+
    1 row in set (0.00 sec)

在这里插入图片描述

  • 显示工资高于平均工资的员工信息

    这里使用分组查询即可

    select ename,sal from emp where sal > (select avg(sal) from emp);

    mysql> select ename,sal from emp where sal > (select avg(sal) from emp);
    ±------±--------+
    | ename | sal |
    ±------±--------+
    | JONES | 2975.00 |
    | BLAKE | 2850.00 |
    | TEST | 2450.00 |
    | SCOTT | 3000.00 |
    | KING | 5000.00 |
    | FORD | 3000.00 |
    ±------±--------+
    6 rows in set (0.00 sec)

在这里插入图片描述

  • 显示每个部门的平均工资和最高工资

    select deptno,avg(sal),max(sal) from emp group by deptno;

    mysql> select deptno,avg(sal),max(sal) from emp group by deptno;
    ±-------±------------±---------+
    | deptno | avg(sal) | max(sal) |
    ±-------±------------±---------+
    | 10 | 2425.000000 | 5000.00 |
    | 20 | 2175.000000 | 3000.00 |
    | 30 | 1690.000000 | 2850.00 |
    ±-------±------------±---------+
    3 rows in set (0.00 sec)

在这里插入图片描述

  • 显示平均工资低于2000的部门号和它的平均工资

    select deptno,avg(sal) as avg_sal from emp group by deptno having avg_sal < 2000;

    mysql> select deptno,avg(sal) as avg_sal from emp group by deptno having avg_sal < 2000;
    ±-------±------------+
    | deptno | avg_sal |
    ±-------±------------+
    | 30 | 1690.000000 |
    ±-------±------------+
    1 row in set (0.00 sec)

在这里插入图片描述

  • 显示每种岗位的雇员总数,平均工资

    select job,count(*), avg(sal) from emp group by job;

    mysql> select job,count(), avg(sal) from emp group by job;
    ±----------±---------±------------+
    | job | count(
    ) | avg(sal) |
    ±----------±---------±------------+
    | ANALYST | 2 | 3000.000000 |
    | CLERK | 4 | 1037.500000 |
    | MANAGER | 3 | 2758.333333 |
    | PRESIDENT | 1 | 5000.000000 |
    | SALESMAN | 4 | 1400.000000 |
    ±----------±---------±------------+
    5 rows in set (0.00 sec)

在这里插入图片描述

2.多表查询

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

表emp,dept,salgrade来演示如何进行多表查询。

案例:

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

在这里插入图片描述

其实我们只要emp表中的deptno = dept表中的deptno字段的记录

select ename,sal,dname from emp,dept where emp.deptno=dept.deptno;
mysql> select ename,sal,dname from emp,dept where emp.deptno=dept.deptno;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| SMITH  |  800.00 | RESEARCH   |
| ALLEN  | 1600.00 | SALES      |
| WARD   | 1250.00 | SALES      |
| JONES  | 2975.00 | RESEARCH   |
| MARTIN | 1250.00 | SALES      |
| BLAKE  | 2850.00 | SALES      |
| CLACK  | 2450.00 | ACCOUNTING |
| SCOTT  | 3000.00 | RESEARCH   |
| KING   | 5000.00 | ACCOUNTING |
| TURNER | 1500.00 | SALES      |
| ADAMS  | 1100.00 | RESEARCH   |
| JAMES  |  950.00 | SALES      |
| FORD   | 3000.00 | RESEARCH   |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+
14 rows in set (0.00 sec)

在这里插入图片描述

  • 显示部门号为10的部门名,员工名和工资

    mysql> select dname,ename,sal from emp,dept where emp.deptno=dept.deptno and dept.deptno=10;
    ±-----------±-------±--------+
    | dname | ename | sal |
    ±-----------±-------±--------+
    | ACCOUNTING | CLACK | 2450.00 |
    | ACCOUNTING | KING | 5000.00 |
    | ACCOUNTING | MILLER | 1300.00 |
    ±-----------±-------±--------+
    3 rows in set (0.00 sec)

在这里插入图片描述

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

    mysql> select ename,sal,grade from emp,salgrade where sal between losal and hisal;
    mysql> select ename,sal,grade from emp,salgrade where sal between losal and hisal;
    ±-------±--------±------+
    | ename | sal | grade |
    ±-------±--------±------+
    | SMITH | 800.00 | 1 |
    | ALLEN | 1600.00 | 3 |
    | WARD | 1250.00 | 2 |
    | JONES | 2975.00 | 4 |
    | MARTIN | 1250.00 | 2 |
    | BLAKE | 2850.00 | 4 |
    | CLACK | 2450.00 | 4 |
    | SCOTT | 3000.00 | 4 |
    | KING | 5000.00 | 5 |
    | TURNER | 1500.00 | 3 |
    | ADAMS | 1100.00 | 1 |
    | JAMES | 950.00 | 1 |
    | FORD | 3000.00 | 4 |
    | MILLER | 1300.00 | 2 |
    ±-------±--------±------+
    14 rows in set (0.00 sec)

在这里插入图片描述

3.自连接

自连接是指在同一张表连接查询

案例:

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

使用的子查询

select ename,empno from emp where empno=(select mgr from emp where ename='FORD');

使用多表查询(自查询)

select e2.ename,e2.empno from emp e1,emp e2 where e1.ename='FORD' and e1.mgr=e2.empno;
mysql> select e1.ename,e2.empno from emp e1,emp e2 where e1.ename='FORD' and e1.mgr=e2.empno;
+-------+--------+
| ename | empno  |
+-------+--------+
| FORD  | 007566 |
+-------+--------+
1 row in set (0.00 sec)

在这里插入图片描述

4.子查询

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

4.1单行子查询

返回一行记录的子查询

  • 显示SMITH同一部门的员工

    select * from emp where deptno=(select deptno from emp where ename=‘SMITH’);
    mysql> select * from emp where deptno=(select deptno from emp where ename=‘SMITH’);
    ±-------±------±--------±-----±--------------------±--------±-----±-------+
    | empno | ename | job | mgr | hiredate | sal | comm | deptno |
    ±-------±------±--------±-----±--------------------±--------±-----±-------+
    | 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
    | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
    | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
    | 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
    | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
    ±-------±------±--------±-----±--------------------±--------±-----±-------+
    5 rows in set (0.00 sec)

在这里插入图片描述

4.2多行子查询

返回多行记录的子查询

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

    select ename,job,sal,deptno from emp where job in(select job from emp where deptno=10) and deptno<>10;
    mysql> select ename,job,sal,deptno from emp where job in(select job from emp where deptno=10) and deptno<>10;
    +-------+---------+---------+--------+
    | ename | job     | sal     | deptno |
    +-------+---------+---------+--------+
    | JONES | MANAGER | 2975.00 |     20 |
    | BLAKE | MANAGER | 2850.00 |     30 |
    | SMITH | CLERK   |  800.00 |     20 |
    | ADAMS | CLERK   | 1100.00 |     20 |
    | JAMES | CLERK   |  950.00 |     30 |
    +-------+---------+---------+--------+
    5 rows in set (0.00 sec)
    
  • all关键字;显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

    // 使用聚合函数
    select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);
    mysql> select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);
    +-------+---------+--------+
    | ename | sal     | deptno |
    +-------+---------+--------+
    | JONES | 2975.00 |     20 |
    | SCOTT | 3000.00 |     20 |
    | KING  | 5000.00 |     10 |
    | FORD  | 3000.00 |     20 |
    +-------+---------+--------+
    4 rows in set (0.01 sec)// 使用all关键子
    select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);
    mysql> select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);
    +-------+---------+--------+
    | ename | sal     | deptno |
    +-------+---------+--------+
    | JONES | 2975.00 |     20 |
    | SCOTT | 3000.00 |     20 |
    | KING  | 5000.00 |     10 |
    | FORD  | 3000.00 |     20 |
    +-------+---------+--------+
    4 rows in set (0.00 sec)
    
  • any关键字;显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号(包含自己部门的员工)

    // 使用聚合函数
    mysql> select ename,sal,deptno from emp where sal > (select min(sal) from emp where deptno=30) and deptno<>30;
    ±-------±--------±-------+
    | ename | sal | deptno |
    ±-------±--------±-------+
    | JONES | 2975.00 | 20 |
    | CLACK | 2450.00 | 10 |
    | SCOTT | 3000.00 | 20 |
    | KING | 5000.00 | 10 |
    | ADAMS | 1100.00 | 20 |
    | FORD | 3000.00 | 20 |
    | MILLER | 1300.00 | 10 |
    ±-------±--------±-------+
    7 rows in set (0.00 sec)
    // 使用any关键字
    mysql> select ename,sal,deptno from emp where sal > any(select sal from emp where deptno=30) and deptno<>30;
    ±-------±--------±-------+
    | ename | sal | deptno |
    ±-------±--------±-------+
    | JONES | 2975.00 | 20 |
    | CLACK | 2450.00 | 10 |
    | SCOTT | 3000.00 | 20 |
    | KING | 5000.00 | 10 |
    | ADAMS | 1100.00 | 20 |
    | FORD | 3000.00 | 20 |
    | MILLER | 1300.00 | 10 |
    ±-------±--------±-------+
    7 rows in set (0.00 sec)

4.3多列子查询

单行子查询是指子查询只返回单列,单行数据;多行子查询是指返回单列多行数据,都是针对单列而言

的,而多列子查询则是指查询返回多个列数据的子查询语句

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

mysql> select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH') and ename<>'SMITH';
mysql> select * from emp where (deptno,job)in(select deptno,job from emp where ename='SMITH') and ename<>'SMITH';
+--------+-------+-------+------+---------------------+---------+------+--------+
| empno  | ename | job   | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+-------+------+---------------------+---------+------+--------+
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL |     20 |
+--------+-------+-------+------+---------------------+---------+------+--------+
1 row in set (0.00 sec)

4.4在from子句中使用子查询

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

案例:

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

答案:

select t1.ename,t1.deptno,t1.sal,t2.myavg from emp t1,(select deptno,avg(sal) myavg from emp group by deptno) t2 where t1.deptno=t2.deptno and t1.ssal > t2.myavg;

步骤:

// 1.根据部门号分组得到每组的平均工资
mysql> select avg(sal) from emp group by deptno;
+-------------+
| avg(sal)    |
+-------------+
| 2916.666667 |
| 2175.000000 |
| 1566.666667 |
+-------------+
3 rows in set (0.00 sec)// 2.根据部门号分组得到每组的平均工资和部门号
mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)// 3.将上面得到的结果与emp表做笛卡尔积
mysql> select * from emp t1,(select deptno,avg(sal) myavg from emp group by deptno) t2 where t1.deptno=t2.deptno;
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+-------------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | myavg       |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+-------------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | 2175.000000 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | 1566.666667 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | 1566.666667 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | 2175.000000 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | 1566.666667 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | 1566.666667 |
| 007782 | CLACK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | 2916.666667 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     20 | 2175.000000 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | 2916.666667 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | 1566.666667 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     20 | 2175.000000 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | 1566.666667 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | 2175.000000 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | 2916.666667 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+-------------+
14 rows in set (0.00 sec)// 5.增加筛选条件 :工资大于平均工资
mysql> select * from emp t1,(select deptno,avg(sal) myavg from emp group by deptno) t2 where t1.deptno=t2.deptno and t1.sal > t2.myavg;
+--------+-------+-----------+------+---------------------+---------+--------+--------+--------+-------------+
| empno  | ename | job       | mgr  | hiredate            | sal     | comm   | deptno | deptno | myavg       |
+--------+-------+-----------+------+---------------------+---------+--------+--------+--------+-------------+
| 007499 | ALLEN | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 |     30 |     30 | 1566.666667 |
| 007566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |   NULL |     20 |     20 | 2175.000000 |
| 007698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |   NULL |     30 |     30 | 1566.666667 |
| 007788 | SCOTT | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |   NULL |     20 |     20 | 2175.000000 |
| 007839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |   NULL |     10 |     10 | 2916.666667 |
| 007902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |   NULL |     20 |     20 | 2175.000000 |
+--------+-------+-----------+------+---------------------+---------+--------+--------+--------+-------------+
6 rows in set (0.00 sec)// 5.根据题目要求得到结果
mysql> select t1.ename,t1.deptno,t1.sal,t2.myavg from emp t1,(select deptno,avg(sal) myavg from emp group by deptno) t2 where t1.deptno=t2.deptno and t1.ssal > t2.myavg;
+-------+--------+---------+-------------+
| ename | deptno | sal     | myavg       |
+-------+--------+---------+-------------+
| ALLEN |     30 | 1600.00 | 1566.666667 |
| JONES |     20 | 2975.00 | 2175.000000 |
| BLAKE |     30 | 2850.00 | 1566.666667 |
| SCOTT |     20 | 3000.00 | 2175.000000 |
| KING  |     10 | 5000.00 | 2916.666667 |
| FORD  |     20 | 3000.00 | 2175.000000 |
+-------+--------+---------+-------------+
6 rows in set (0.00 sec)

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

答案:

select t1.ename,t1.sal,t1.deptno,t2.mymax from emp t1,(select deptno, max(sal) mymax from emp group by deptno) t2 where t1.deptno=t2.deptno and t1..sal=t2.mymax;

步骤:

// 1.得到分组之后的部门号和最高工资
mysql> select deptno, max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
|     30 |  2850.00 |
+--------+----------+
3 rows in set (0.01 sec)// 2.与emp表进行笛卡尔积并进行t1.sal=t2.mymax的筛选(工资等于最高工资)
mysql> select * from emp t1,(select deptno, max(sal) mymax from emp group by deptno) t2 where t1.deptno=t2.deptno and t1.sal=t2.mymax;
+--------+-------+-----------+------+---------------------+---------+------+--------+--------+---------+
| empno  | ename | job       | mgr  | hiredate            | sal     | comm | deptno | deptno | mymax   |
+--------+-------+-----------+------+---------------------+---------+------+--------+--------+---------+
| 007698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |     30 | 2850.00 |
| 007788 | SCOTT | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |     20 | 3000.00 |
| 007839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |     10 | 5000.00 |
| 007902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |     20 | 3000.00 |
+--------+-------+-----------+------+---------------------+---------+------+--------+--------+---------+
4 rows in set (0.00 sec)// 3.根据题目要求选择需要筛选的内容
mysql> select t1.ename,t1.sal,t1.deptno,t2.mymax from emp t1,(select deptno, max(sal) mymax from emp group by deptno) t2 where t1.deptno=t2.deptno and t1..sal=t2.mymax;
+-------+---------+--------+---------+
| ename | sal     | deptno | mymax   |
+-------+---------+--------+---------+
| BLAKE | 2850.00 |     30 | 2850.00 |
| SCOTT | 3000.00 |     20 | 3000.00 |
| KING  | 5000.00 |     10 | 5000.00 |
| FORD  | 3000.00 |     20 | 3000.00 |
+-------+---------+--------+---------+
4 rows in set (0.00 sec

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

答案:

select t1.deptno,t1.dname,t1.loc,t2.num from dept t1,(select deptno,count(*) num from emp group by deptno) t2 where t1.deptno=t2.deptno;

步骤:

// 1.分组得到每一组的人数
mysql> select deptno,count(*) num from emp group by deptno;
+--------+-----+
| deptno | num |
+--------+-----+
|     10 |   3 |
|     20 |   5 |
|     30 |   6 |
+--------+-----+
3 rows in set (0.00 sec)// 2.和部门表进行笛卡尔积,然后进行条件筛选
mysql> select * from dept t1,(select deptno,count(*) num from emp group by deptno) t2 where t1.deptno=t2.deptno;
+--------+------------+----------+--------+-----+
| deptno | dname      | loc      | deptno | num |
+--------+------------+----------+--------+-----+
|     10 | ACCOUNTING | NEW YORK |     10 |   3 |
|     20 | RESEARCH   | DALLAS   |     20 |   5 |
|     30 | SALES      | CHICAGO  |     30 |   6 |
+--------+------------+----------+--------+-----+
3 rows in set (0.01 sec)
mysql> select t1.deptno,t1.dname,t1.loc,t2.num from dept t1,(select deptno,count(*) num from emp group by deptno) t2 where t1.deptno=t2.deptno;
+--------+------------+----------+-----+
| deptno | dname      | loc      | num |
+--------+------------+----------+-----+
|     10 | ACCOUNTING | NEW YORK |   3 |
|     20 | RESEARCH   | DALLAS   |   5 |
|     30 | SALES      | CHICAGO  |   6 |
+--------+------------+----------+-----+
3 rows in set (0.00 sec)

暴力解法:

mysql> select dept.dname,dept.deptno,dept.loc,count(*) from emp,dept where emp.deptno=dept.deptno group by dept.deptno,dept.dname,dept.loc;
+------------+--------+----------+----------+
| dname      | deptno | loc      | count(*) |
+------------+--------+----------+----------+
| ACCOUNTING |     10 | NEW YORK |        3 |
| RESEARCH   |     20 | DALLAS   |        5 |
| SALES      |     30 | CHICAGO  |        6 |
+------------+--------+----------+----------+
3 rows in set (0.01 sec)

总结:

解决多表问题的本质:想办法将多表转化为单表,所以mysql中,所有select的问题全部都可以转化成单表问题

4.5合并查询

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

4.5.1 union

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

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

// 1.查出工资大于2500的
mysql> select * from emp where sal>2500;
+--------+-------+-----------+------+---------------------+---------+------+--------+
| empno  | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+-----------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
| 007788 | SCOTT | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
| 007839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
| 007902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
+--------+-------+-----------+------+---------------------+---------+------+--------+
5 rows in set (0.00 sec)// 2.查出job=MANAGER的
mysql> select * from emp where job='MANAGER';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
| 007782 | CLACK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
+--------+-------+---------+------+---------------------+---------+------+--------+
3 rows in set (0.00 sec)// 3.进行合并
mysql> select * from emp where sal>2500 union select * from emp where job='MANAGER';
+-------+-------+-----------+------+---------------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+-----------+------+---------------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
|  7782 | CLACK | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
+-------+-------+-----------+------+---------------------+---------+------+--------+
6 rows in set (0.00 sec)
4.5.2 union all

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

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

// 1.查出工资大于2500的
mysql> select * from emp where sal>2500;
+--------+-------+-----------+------+---------------------+---------+------+--------+
| empno  | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+-----------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
| 007788 | SCOTT | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
| 007839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
| 007902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
+--------+-------+-----------+------+---------------------+---------+------+--------+
5 rows in set (0.00 sec)// 2.查出job=MANAGER的
mysql> select * from emp where job='MANAGER';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
| 007782 | CLACK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
+--------+-------+---------+------+---------------------+---------+------+--------+
3 rows in set (0.01 sec)// 3.进行合并
mysql> select * from emp where sal>2500 union all select * from emp where job='MANAGER';
+-------+-------+-----------+------+---------------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+-----------+------+---------------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
|  7782 | CLACK | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
+-------+-------+-----------+------+---------------------+---------+------+--------+
8 rows in set (0.00 sec)

http://www.ppmy.cn/news/1365092.html

相关文章

MySQL:单表查询SQL语句

提醒&#xff1a;设定下面的语句是在数据库名为 db_student里执行的。 创建t_student表 CREATE TABLE t_student(id INT NOT NULL AUTO_INCREMENT,stuName VARCHAR(30) DEFAULT NULL,age INT,sex VARCHAR(4) DEFAULT NULL,gradeName VARCHAR(30) DEFAULT NULL,PRIMARY KEY(id)…

修改Qt生成iOS应用的原生底层,编译QtBase下的ios子模块

1.下载Qt源码 2.找到ios.pro子工程 3.使用QtCreaor12打开ios.pro工程 4.出现工程下只有一个.pro文件解决 复制修改好的toolchain.prf文件进行替换. 修改方法:

HTML知识点

HTML 【一】HTML简介 【1】什么是HTML HTML是一种用于创建网页结构和内容的超文本标记语言&#xff0c;它是构建网页的基础。为了让浏览器正确渲染页面&#xff0c;我们必须遵循HTML的语法规则。浏览器在解析网页时会将HTML代码转换为可视化的页面&#xff0c;所以我们在浏览…

Netty入门指南:从零开始的异步网络通信

欢迎来到我的博客&#xff0c;代码的世界里&#xff0c;每一行都是一个故事 Netty入门指南&#xff1a;从零开始的异步网络通信 前言Netty简介由来&#xff1a;发展历程&#xff1a;异步、事件驱动的编程模型&#xff1a; 核心组件解析通信协议高性能特性异步编程范式性能优化与…

鸿蒙应用程序包安装和卸载流程

开发者 开发者可以通过调试命令进行应用的安装和卸载&#xff0c;可参考多HAP的调试流程。 图1 应用程序包安装和卸载流程&#xff08;开发者&#xff09; 多HAP的开发调试与发布部署流程 多HAP的开发调试与发布部署流程如下图所示。 图1 多HAP的开发调试与发布部署流程 …

leetcode 3.反转链表;

1.题目&#xff1a; 给你单链表的头节点 head &#xff0c;请你反转链表&#xff0c;并返回反转后的链表。 2.用例&#xff1a; 3.题目解析&#xff1a; &#xff08;1&#xff09;函数头&#xff1a; 要求返回结点&#xff0c;就 ListNode* reverseList(ListNode* head)&…

代码随想录算法训练营day60 || 647.回文子串,516. 最长回文子序列

动态规划&#xff0c;字符串性质决定了DP数组的定义 | LeetCode&#xff1a;647.回文子串_哔哩哔哩_bilibili 动态规划再显神通&#xff0c;LeetCode&#xff1a;516.最长回文子序列_哔哩哔哩_bilibili 647.回文子串 // 时间复杂度O(n^2) // 空间复杂度O(n^2) class Solution …

R语言空间分析、模拟预测与可视化

随着地理信息系统&#xff08;GIS&#xff09;和大尺度研究的发展&#xff0c;空间数据的管理、统计与制图变得越来越重要。R语言在数据分析、挖掘和可视化中发挥着重要的作用&#xff0c;其中在空间分析方面扮演着重要角色&#xff0c;与空间相关的包的数量也达到130多个。在本…

Redis的发布订阅功能教程,实现实时消息和key过期事件通知功能

Redis的发布订阅 Redis的发布/订阅(Pub/Sub)功能是一种消息传递模式,用于实现消息发布者(publisher)和订阅者(subscriber)之间的消息通信。在这种模式下,消息的发送者(发布者)将消息发送到特定的频道(channel),而订阅了该频道的接收者(订阅者)将会接收到这些消息…

gprMax3.0随机介质建模

此处利用gprMax建立随机介质模型,采用matlab生成随机数组,保存为HDF5文件,此处为全代码,无需修改即可运行。在gprMax输入文件中使用#geometry_objects_read:读入自定义的随机模型 此文参考其他博主的自定义几何形状模块gprMax3.0建模时如何自定义目标的几何形状_#geomet…

Baby_enc-攻防世界-MISC

题目描述&#xff1a; 下载得到enc.py和out.txt&#xff0c;分别是加密算法和结果。先看enc.py&#xff1a; import codecs def enc(s, t):if t:l list(map(ord, s))return enc(.join(list(map(chr, [l[i]^l[i1] for i in range(len(l)-1)]))), t-1)else:return swith open(i…

trie树(前缀树)

前缀树 1. 前缀树的的介绍2.前缀树的实现2.1插入功能2.2删除功能2.3查找前缀和查找单词功能2.4 哈希表版本 1. 前缀树的的介绍 在计算机科学中&#xff0c;trie&#xff0c;又称前缀树或字典树&#xff0c;是一种有序树&#xff0c;用于保存关联数组&#xff0c;其中的键通常是…

Stable Diffusion 模型分享:【Checkpoint】YesMix(动漫、2.5D)

本文收录于《AI绘画从入门到精通》专栏,专栏总目录:点这里。 文章目录 模型介绍生成案例案例一案例二案例三案例四下载地址模型介绍 条目内容类型大模型基础模型SD 1.5来源

C语言第三十二弹---自定义类型:联合和枚举

✨个人主页&#xff1a; 熬夜学编程的小林 &#x1f497;系列专栏&#xff1a; 【C语言详解】 【数据结构详解】 目录 1、联合体 1.1、联合体类型的声明 1.2、联合体的特点 1.3、相同成员的结构体和联合体对比 1.4、联合体大小的计算 1.5、联合的⼀个练习 2、枚举类型 …

关于CSS常见选择器应用的基础教程

在网页开发中&#xff0c;CSS选择器是非常重要的一部分&#xff0c;它们用来指定你想要样式化的HTML元素。熟练掌握各种选择器的用法可以帮助你更有效地实现网页布局和设计。本文将介绍一些常见的CSS选择器&#xff0c;并演示它们的基本用法及应用场景。 一、元素选择器&#…

Android 接入指纹识别

接入指纹框架&#xff1a;https://github.com/Tencent/soter implementation com.github.Tencent.soter:soter-wrapper:2.0.91.Application中初始化 class IApplication : Application() {override fun onCreate() {super.onCreate()instance thisinitSort()}private fun in…

数仓项目6.0(二)数仓

中间的几步意义就在于&#xff0c;缓存中间处理数据样式&#xff0c;避免重复计算浪费算力 分层 ODS&#xff08;Operate Data Store&#xff09; Spark计算过程中&#xff0c;存在shuffle的操作&#xff0c;而shuffle会将计算过程一分为二&#xff0c;前一阶段不执行完&…

我的NPI项目之设备系统启动(八) -- Android14的GKI2.0开发步骤和注意事项

GKI是什么&#xff1f; Google为什么要推行GKI&#xff1f; GKI全称General Kernel Image。GKI在framework和kernel之间提供了标准接口&#xff0c;使得android OS能够轻松适配/维护/兼容不同的设备和linux kernel。 Google引入GKI的目的是将Framework和Kernel进一步的解耦。因…

IBM在闪存系统集成实时恶意软件I/O检测功能

IBM在其最新一代FlashCore Modules&#xff08;FCMs&#xff09;固件中集成了使用机器学习进行实时勒索软件和其他攻击检测的功能。这些FCMs是专用于IBM FlashSystem 5000和Storwize阵列的闪存驱动器&#xff0c;采用U.2外形尺寸及NVMe接口。现有的第三代FCMs分别提供4.8、9.6、…

连接未来:嵌入式系统在物联网时代的应用

连接未来&#xff1a;嵌入式系统在物联网时代的应用 随着物联网技术的不断发展&#xff0c;嵌入式系统在物联网时代扮演着至关重要的角色。嵌入式系统作为连接物理世界和数字世界的桥梁&#xff0c;为物联网的实现提供了技术支持和基础设施。以下将从几个方面探讨嵌入式系统在…