MySQL--复合查询--内外链接--0422

已于 2023-05-13 09:55:01 修改
阅读量715 收藏
点赞数 1
分类专栏： MySQL 文章标签： mysql 数据库 sql
于 2023-05-09 21:13:04 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_68741368/article/details/130521896
版权

注：为了方便查看 mysql语句会有突然的换行，书写时请勿模仿。

目录

1.单表查询回顾

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

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

2.多表查询

比如需要查询雇员名字和所在部门编号及部门名字。

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

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

2.2 多表查询总结

3.自连接

显示员工ford的上级领导的编号和姓名

 4. 子查询

4.1 单行子查询

显示SMITH同一部门的员工

4.2 多行子查询

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

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

 4.3 多列子查询

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

4.4 在from子句中使用子查询

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

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

4.5 合并查询

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

1.单表查询回顾

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

可以使用如下语句吗？

select ename,max(sal) from emp;

 max(sal)是聚合函数，把整个表当做一个数据来看，但是ename不能将整个表当做一个数据，所以会出现错误。但是如果select筛选的表结构是max(sal)显示出来的表，那么就不会再有错误了。所以用过两条语句也可以达成目的。

select max(sal) from emp;

select ename,job from emp where sal=5000; -->5000就是表里面最大值

select是支持嵌套的

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

有一条select语句的结果被当做某条select的条件，这条select语句称为子查询。执行顺序类似递归。注意子查询设置的别名，在主查询中依然看不到！

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

mysql> select ename,job from emp where sal>(select avg(sal) from emp);
+-------+-----------+
| ename | job       |
+-------+-----------+
| JONES | MANAGER   |
| BLAKE | MANAGER   |
| CLARK | MANAGER   |
| SCOTT | ANALYST   |
| KING  | PRESIDENT |
| FORD  | ANALYST   |
+-------+-----------+

2.多表查询

有时仅使用一张表达不到查询效果。

比如需要查询雇员名字和所在部门编号及部门名字。

由于雇员名字在emp表中，而部门名字在dept表中，因此需要联合查询。

mysql> select * from dept,emp;

 上述语句也会生成一个表，这个表是dept和emp的笛卡尔积。

 笛卡尔积会出现很多冗余数据是没有意义的，可以使用两张表有关联的部分进行筛选。比如部门编号。

mysql> select * from dept,emp where dept.deptno=emp.deptno;

 这张表就是多表查询后最完整的表，然后就可以从这张表里挑需要的数据进行显示了。

mysql> select ename,emp.deptno,dname from dept,emp where dept.deptno=emp.deptno;
+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| JONES  |     20 | RESEARCH   |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| CLARK  |     10 | ACCOUNTING |
| SCOTT  |     20 | RESEARCH   |
| KING   |     10 | ACCOUNTING |
| TURNER |     30 | SALES      |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| FORD   |     20 | RESEARCH   |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+

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

mysql> select dname,ename,sal from emp,dept where emp.deptno=dept.deptno 
                and dept.deptno=10;
+------------+--------+---------+
| dname      | ename  | sal     |
+------------+--------+---------+
| ACCOUNTING | CLARK  | 2450.00 |
| ACCOUNTING | KING   | 5000.00 |
| ACCOUNTING | MILLER | 1300.00 |
+------------+--------+---------+

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

mysql> select ename,sal,grade from emp,salgrade where emp.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 |
| CLARK  | 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)

2.2 多表查询总结

多表组合，形成笛卡尔积本质上就是数据的穷举。

解决多表查询的思路：

  1. 先确定和哪些表有关系
  2. 让几张有联系的表形成笛卡尔积
  3. 在形成的笛卡尔积中添加筛选条件，将多表查询看做为一张表的查询

3.自连接

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

显示员工ford的上级领导的编号和姓名

可以发现当前语句在书写时，需要在emp表中查找名字为ford的数据而且在显示筛选条件时也是在emp表中。称为子连接。

可以使用子查询来写

mysql> select empno,ename from emp where 
                empno=(select mgr from emp where ename='FORD');
+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+

也可以使用多表查询

 同一张表不能直接查询两次，但是如果将该表起别名，就可以拼起来了。

mysql> select leader.empno,leader.ename from emp leader,emp worker 
            where worker.mgr=leader.empno and worker.ename='ford';
+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+

 4. 子查询

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

4.1 单行子查询

显示SMITH同一部门的员工

mysql> select ename,deptno from emp where deptno=(select deptno from emp where ename='smith');
+-------+--------+
| ename | deptno |
+-------+--------+
| SMITH |     20 |
| JONES |     20 |
| SCOTT |     20 |
| ADAMS |     20 |
| FORD  |     20 |
+-------+--------+

4.2 多行子查询

in

in (...)

在(...)范围中

all

all(...)

比(...)范围内都

any

any(...)

比(...)范围内任意一个

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

mysql> select * from emp where job in
        (select distinct job from emp where deptno=10) and deptno!=10;
+--------+-------+---------+------+---------------------+---------+------+--------+
| 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 |
| 007369 | SMITH | CLERK   | 7902 | 1980-12-17 00:00:00 |  800.00 | NULL |     20 |
| 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 |
+--------+-------+---------+------+---------------------+---------+------+--------+

显示工资比部门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.3 多列子查询

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

mysql> select * from emp where (deptno,job)=
            (select deptno,job 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 |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL |     20 |
+--------+-------+-------+------+---------------------+---------+------+--------+


mysql> select * from emp where (deptno,job)=
            (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 |
+--------+-------+-------+------+---------------------+---------+------+--------+

4.4 在from子句中使用子查询

本质上还是多表查询，无非是把select的结果当做一个临时的表和其他表拼起来。

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

(1)先求一下每个部门的平均工资

mysql> select avg(sal), deptno from emp group by deptno;
+-------------+--------+
| avg(sal)    | deptno |
+-------------+--------+
| 2916.666667 |     10 |
| 2175.000000 |     20 |
| 1566.666667 |     30 |

(2)把平均工资表和emp表拼起来，形成笛卡尔积，然后进行条件筛选。

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

(3)进行筛选

mysql> select ename,deptno,sal,avgsal from emp,
        (select avg(sal) avgsal, deptno dt from emp group by deptno)tmp 
            where emp.deptno=tmp.dt and emp.sal>tmp.avgsal;

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

(1)先筛出来最高工资

mysql> select max(sal) maxsal,deptno from emp group by deptno;
+---------+--------+
| maxsal  | deptno |
+---------+--------+
| 5000.00 |     10 |
| 3000.00 |     20 |
| 2850.00 |     30 |
+---------+--------+

 (2) 将最高工资表和emp表拼起来

mysql> select* from emp,(select max(sal) maxsal,deptno 
            from emp group by deptno) tmp 
                where emp.deptno=tmp.deptno;

(3)加上限制条件和需要显示的信息列

mysql> select ename,emp.deptno,sal,maxsal from emp,
            (select max(sal) maxsal,deptno from emp group by deptno) tmp 
                    where emp.deptno=tmp.deptno and emp.sal=maxsal;
+-------+--------+---------+---------+
| ename | deptno | sal     | maxsal  |
+-------+--------+---------+---------+
| BLAKE |     30 | 2850.00 | 2850.00 |
| SCOTT |     20 | 3000.00 | 3000.00 |
| KING  |     10 | 5000.00 | 5000.00 |
| FORD  |     20 | 3000.00 | 3000.00 |
+-------+--------+---------+---------+

4.5 合并查询

为了合并多个select的执行结果，可以使用集合操作符 union(自带去重)，union all(不去重)

union的两个表的结构必须相同，否则union出来的结果无意义。

mysql> select ename,sal from emp where sal>2500 
        union select sal,job from emp where job='manager';
+---------+---------+
| ename   | sal     |
+---------+---------+
| JONES   | 2975.00 |
| BLAKE   | 2850.00 |
| SCOTT   | 3000.00 |
| KING    | 5000.00 |
| FORD    | 3000.00 |
| 2975.00 | MANAGER |
| 2850.00 | MANAGER |
| 2450.00 | MANAGER |
+---------+---------+

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

mysql> select ename,sal,job from emp where sal>2500 
        union select ename,sal,job from emp where job='manager';
+-------+---------+-----------+
| ename | sal     | job       |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| SCOTT | 3000.00 | ANALYST   |
| KING  | 5000.00 | PRESIDENT |
| FORD  | 3000.00 | ANALYST   |
| CLARK | 2450.00 | MANAGER   |
+-------+---------+-----------+

5.内外连接

5.1内连接

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

select ename, dname from EMP, DEPT where EMP.deptno=DEPT.deptno and
ename='SMITH';

select ename, dname from EMP inner join DEPT on EMP.deptno=DEPT.deptno and
ename='SMITH';

上述使用的全都是内连接

inner join 相当于求两个表的交集。

 5.2外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示，称为左外连接。

右侧的表完全显示，称为右外连接。

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

内连接和外连接的区别在于

  • left/right join相当于求left/right表的全集。没有对应的列属性就置成NULL
  • 内连接是求交集
Gosolo！
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
mysql-connector-java-8.0.34
12-13
mysql-connector-java-8.0.34
SQL学习之多表查询，基于Oracle下的HR用户（四）
kuikui1452的博客
10-09 959
多表查询 1 什么是多表查询 多表查询：当查询的数据并不是来源一个表时，需要使用多表链接操作完成查询。根据不同表中的数据之间的关系查询相关联的数据。 多表链接方式： 内连接：连接两个表，通过相等或不等判断链接列，称为内连接。在内连接中典型的联接运算有 = 或 <> 之类的比较运算符。包括等值联接和自然联接、非等值连接。 SQL99：交叉链接(CROSS JOIN) SQL99：内连接(INNER JOIN) SQL99：自然链接(NATURAL JOIN) 外连接：在两个表之间的连接，返回内连接
【MySQL】复合查询
QiHai的博客
05-03 792
在MySQL下的多表查询、嵌套查询、以及多表连接时的内外连接问题。
SQL 单行函数 练习
Hxy19910420的博客
10-11 2201
–1：验证UPPER()、LOWER()函数 select upper(‘HXY’),lower(‘hxy’) from dual; –2：现在查询出雇员姓名是“smith”的完整信息，但是由于失误，没有考虑到数据的大小写问题（在一些项目的运行之中经常会出现此类输入数据不考虑大小写的问题），此时可以使用UPPER()函数将全部内容变为大写 select * from emp where ename...
MySQL基本查询和运算符
drhrht的博客
08-02 490
作者简介大家好我是小唐同学(>
MySQL查询和函数
weixin_61447142的博客
09-18 559
MySQL查询和函数
mysql-connector-java-8.0.26.jar
12-18
该资源为mysql-connector-java-8.0.26.jar，欢迎下载使用哦！
mysql-connector-java-8.0.23.jar
12-18
该资源为mysql-connector-java-8.0.23.jar，欢迎下载使用哦！
mysql-connector-java-8.0.22.jar
08-14
包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-...
mysql-connector-java-8.0.27.jar
12-18
该资源为mysql-connector-java-8.0.27.jar，欢迎下载使用哦！
MySQL学习笔记——课后练习
java小豪的博客
02-16 1万+
MySQL学习笔记的课后练习
mysql多表查询求和_第8篇 MySQL 多表连接查询
weixin_39577908的博客
12-23 1321
前言连接查询，又称为多表查询，当查询的字段来自多个表，就会用到多表查询。实际数据工程项目开发中用到最多的就是连接查询。复杂的数据查询逻辑更多的是多表连接和子查询的复合查询类型。掌握是多表查询是一个DBA或后端/全栈开发者最起码的基本功。在连接查询中，SELECT 子句和WHERE 子句的条件筛选 用到的所有字段 需要显式指定<表名.字段名>，以告知MySQL读取的字段来自哪一个表。进行...
python数据库的题库_[python全栈]—数据库习题及答案
weixin_39607873的博客
12-08 614
python全栈-MYSQL—数据库习题1.列出至少有四个员工的所有部门信息查询下每个部门的人数select deptno,count() from emp group by deptno至少四位select deptno,count() from emp group by deptno having count()>4部门信息select d.,dd.coufrom dept d,(sel...
mysql简单查询某个字段值的总和
maybe_to_be的博客
07-14 6578
MySQL简单查询字段的值总和
查询出每个雇员的姓名，工资，部门名称，工资在公司的等级及其领导的姓名，领导的工资，以及领导所对应的等级
热门推荐
蜗牛学习笔记
06-05 3万+
如题：查询出每个雇员的姓名，工资，部门名称，工资在公司的等级及其领导的姓名，领导的工资，以及领导所对应的等级 这是oracle的默认Scott用户下面的emp，dept 和 salgrade 表间的一道思考题。 分析:先明确工资等级表(salgrade)中的内容；再查询查询出每个雇员的姓名，工资，部门名称，工资在公司的等级(salgrade)；在此上基础上增加查找上司姓名；最后确定雇员的经理姓名,经理的工资,以及经理工资所对应的等级。从这个例子可以看出，对于复杂的多表查询，要一步一步地实现，不要急于求成。
员工部门表综合查询SQL
weixin_30906425的博客
08-13 650
--数据库的表设计如下： --部门：部门编号，部门名称，地址； --员工：员工编号，员工名字，职务，管理编号，入职日期，薪资，奖金，部门编号； --创建部门表： CREATE TABLE dept( deptno INT PRIMARY KEY,dname VARCHAR(20),loc VARCHAR(20) );--创建员工表： CREATE TABLE emp(empno INT ...
查询各个员工的员工号，员工名及其所在部门的平均工资
it_taojingzhan的博客
10-02 8781
select employeeid, firstname, d.avgsal                   from employee,                  (select departmentid, avg(salary)                     from employee                    group by departmenti
一条语句查询出所有雇员的编号、姓名、性别、出生日期、电话号码部门名称和本月收入
m0_46547187的博客
04-05 2879
一条语句查询出所有雇员的编号、姓名、性别、出生日期、电话号码部门名称和本月收入
mysql-connector-java-bin
最新发布
03-22
mysql-connector-java-bin是MySQL官方提供的Java连接器，用于在Java应用程序中连接和操作MySQL数据库。它是一个JDBC驱动程序，提供了与MySQL数据库的通信和交互的功能。 mysql-connector-java-bin的主要特点包括： ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值