开发者

MySQL复合查询和内外连接的操作代码

开发者 https://www.devze.com 2022-12-12 09:32 出处：网络 作者： s_persist
目录一、复合查询1.1 多表查询1.2 自连接1.3 子查询1.3.1 单行子查询1.3.2 多行子查询1.3.3 多列子查询1.3.4 在from子句中使用子查询1.4 合并查询1.4.1 union1.4.2 union all二、内外连接2.1 内连接2.2 外连接2.2.1
相关专题：MySQL内外连接MySQL复合查询
目录
  • 一、复合查询
    • 1.1 多表查询
    • 1.2 自连接
    • 1.3 子查询
      • 1.3.1 单行子查询
      • 1.3.2 多行子查询
      • 1.3.3 多列子查询
      • 1.3.4 在from子句中使用子查询
    • 1.4 合并查询
      • 1.4.1 union
      • 1.4.2 union all
  • 二、内外连接
    • 2.1 内连接
      • 2.2 外连接
        • 2.2.1 左外连接
        • 2.2.1 右外连接

    一、复合查询

    1.1 多表查询

    实际开发中往往数据来自不同的表，所以需要多表查询，但是可以将多张表做笛卡尔积后的表当做是一张表，也就是单表查询。

    我们用一个简单的公司管理系统，有三张表EMP、DEPT、SALGRADE来演示如何进行多表查询。

    语法：from 表1, 表2 ...

    这样其实就是对多张表进行笛卡尔积，假设是两张表：

    • 第一张表的第一行和第二张表的所有行组合。
    • 第一张表的第二行和第二张表的所有行组合。…

    MySQL复合查询和内外连接的操作代码

    这样所有的组合，并不都是有意义的，所有一般都是根据条件筛选的。

    在多表查询中，有可能多张表有相同的字段，为了便于区分，可以使用表名.字段名表示某一张表的某一个字段。

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

    1.2 自连接

    自连接是对一张表的查询，但是使用多张表的查询方式，自己做笛卡尔积。

    例如：

    mysql> show create table emp \G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
    • 显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号）。
    • 这是员工表，领导也是属于员工，所以无法只用一条简单查找语句对一张表查找两次，这就需要用到复合查询。
    • 子查询 — 即嵌套查询，使用两条查询语句
    mysql> select empno, ename from emp where empno = (select mgr from emp where ename = 'FORD');
+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+
1 row in set (0.00 sec

    使用自连接的两张表查询，这里需要对表使用别名

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

    1.3 子查询

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

    1.3.1 单行子查询

    返回一行记录的子查询

    • 显示SMITH同一部门的员工
    mysql> select deptno, ename from emp where deptno = (select deptno from emp where ename = 'SMITH');
+--------+-------+
| deptno | ename |
+--------+-------+
|     20 | SMITH |
|     20 | JONES |
|     20 | SCOTT |
|     20 | ADAMS |
|     20 | FORD  |
+--------+-------+
5 rows in set (0.00 sec)

    1.3.2 多行子查询

    返回多行记录的子查询，仍然是一列（一个字段）。

    in关键字（属于查询出来的多行中的一行，无法用于比较）：查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的

    先拆分，找到10号部门的岗位：

    mysql> select di编程客栈stinct job from emp where deptno = 10;
+-----------+
| job       |
+-----------+
| MANAGER   |
| PRESIDENT |
| CLERK     |
+-----------+
3 rows in set (0.00 sec)
-- 然后再复合
mysql> select ename, job, sal, deptno from emp where job in (select distinct job from emp where deptno = 10) and deptno <=> 10;
+--------+-----------+---------+--------+
| ename  | job       | sal     | deptno |
+--------+-----------+---------+--------+
| CLARK  | MANAGER   | 2450.00 |     10 |
| KING   | PRESIDENT | 5000.00 |     10 |
| MILLER | CLERK     | 1300.00 |     10 |
+--------+-----------+---------+--------+
3 rows in set (0.00 sec)

    all关键字（与查询结果的所有行比较为真的）：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

    同样可以拆分

    -- 先找到部门号为30的所有工资
mysql> select sal from emp where deptno = 30;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+
6 rows in set (0.00 sec)
-- 判断 > 所有行
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的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工） some是any的别称很少使用。

    mysql> select ename, sal, deptno from emp where sal > any (select sal from emp where deptno=30);
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| CLARK  | 2450.00 |     10 |
| SCOTT  | 3000.00 |     20 |
| KING   | 5000.00 |     10 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| FORD   | 3000.00 |     20 |
| MILLwww.devze.comER | 1300.00 |     10 |
+--------+---------+--------+
12 rows in set (0.00 sec)

    1.3.3 多列子查询

    单行子查询是指子查询只返回单列，单行数据

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

    其实很简单，多列也可以比较，和单列一样。

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

    mysql> select ename, deptno, job from emp where (job, deptno) = (select job, deptno from emp where ename = 'SMITH') and ename <=> 'SMITH';
+-------+--------+-------+
| ename | deptno | job   |
+-------+--------+-------+
| SMITH |     20 | CLERK |
+-------+--------+-------+
1 row in set (0.00 sec)

    1.3.4 在from子句中使用子查询

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

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

    -- 先找到每个部门的平均工资
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)
-- 然后再用这张表和原本的表做笛卡尔积，多表查询
mysql> select emp.ename, emp.deptno, emp.sal, format(dept_avg,2) from emp , (select deptno , avg(sal) dept_avg from emp group by deptno) tmp wheree tmp.deptno = emp.deptno and emp.sal > dept_avg;
+-------+--------+---------+--------------------+
| ename | deptno | sal     | format(dept_avg,2) |
+-------+--------+---------+--------------------+
| ALLEN |     30 | 1600.00 | 1,566.67           |
| JONES |     20 | 2975.00 | 2,175.00           |
| BLAKE |     3javascript0 | 2850.00 | 1,566.67           |
| SCOTT |     20 | 3000.00 | 2,175.00           |
| KING  |     10 | 5000开发者_Hadoop.00 | 2,916.67           |
| FORD  |     20 | 3000.00 | 2,175.00           |
+-------+--------+---------+--------------------+
6 rows in set (0.00 sec)

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

    对于有未知的，聚合的信息（人员数量），先将它求出来

    -- 首先拿到相关表的信息
mysql> select * from dept limit 3;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
+--------+------------+----------+
3 rows in set (0.00 sec)

mysql> select * from emp limit 3;
+--------+-------+----------+------+---------------------+---------+--------+--------+
| 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 |
+--------+-------+----------+------+---------------------+---------+--------+--------+
3 rows in set (0.01 sec)

    暴力使用多表查询

    注意：和聚合函数一起显示的字段，需要被分组，因为聚合函数对于某一个字段是不清楚的，比如count(*)，它是统计总数，并不对应某一个deptno,ename这些字段

    mysql> select dept.dname, dept.deptno, dept.loc, count(*) from dept, emp where dept.deptno = emp.deptno group by deptno;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'scott.dept.dname' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
---- 注意：和聚合函数一起显示的字段，需要被分组，因为聚合函数对于某一个字段是不清楚的，比如count(*)，它是统计总数，并不对应某一个deptno,ename这些字段
mysql> select dept.dname, dept.deptno, dept.loc, count(*) from dept, emp where dept.deptno = emp.deptno group by 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.00 sec)

    使用子查询

    -- 先子查询出每个部门的人员数量
mysql> select deptno, count(*) from emp group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     10 |        3 |
|     20 |        5 |
|     30 |        6 |
+--------+----------+
3 rows in set (0.00 sec)
-- 再和部门表组合，根据条件筛选。
mysql> select dept.dname, dept.deptno, dept.loc, dept_cnt from dept, (select deptno, count(*) dept_cnt from emp group by deptno) cnt_table where
cnt_table.deptno = dept.deptno;
+------------+--------+----------+----------+
| dname      | deptno | loc      | dept_cnt |
+------------+--------+----------+----------+
| ACCOUNTING |     10 | NEW YORK |        3 |
| RESEARCH   |     20 | DALLAS   |        5 |
| SALES      |     30 | CHICAGO  |        6 |
+------------+--------+----------+----------+
3 rows in set (0.00 sec)

    1.4 合并查询

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

    1.4.1 union

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

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

    岗位是MANAGER的必定有工资大于2500的，这里自动去重了

    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   |
+-------+----编程-----+-----------+
6 rows in set (0.00 sec)

    1.4.2 union all

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

    mysql> select ename, sal, job from emp where sal>2500 union all 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   |
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| CLARK | 2450.00 | MANAGER   |
+-------+---------+-----------+
8 rows in set (0.00 sec)

    二、内外连接

    2.1 内连接

    内连接inner join实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，我们前面学习的查询都是内连接，也是在开发过程中使用的最多的连接查询。

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

    前面使用的都是内连接。

    on后面是两张表的连接条件，后可以跟where条件语句查询表。

    -- 用前面的写法
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';

    两张表连接，并不是所有行都是有效的，所有使用连接条件更加明确。

    2.2 外连接

    外连接分为左外连接和右外连接

    2.2.1 左外连接

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

    什么叫完全显示呢？

    就是两张表通过连接条件连接，但是左侧的表有不满足条件的也要全部显示，右侧的表显示为NULL。

    语法：

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

    创建两张表

    mysql> select * from exam;
+------+-------+
| id   | grade |
+------+-------+
|    1 |    56 |
|    2 |    76 |
|   11 |     8 |
+------+-------+
3 rows in set (0.00 sec)

mysql> select * from stu;
+------+------+
| id   | name |
+------+------+
|    1 | jack |
|    2 | tom  |
|    3 | kity |
|    4 | nono |
+------+------+
4 rows in set (0.00 sec)

    使用内连接

    mysql> select * from stu inner join exam on exam.id = stu.id;
+------+------+------+-------+
| id   | name | id   | grade |
+------+------+------+-------+
|    1 | jack |    1 |    56 |
|    2 | tom  |    2 |    76 |
+------+------+------+-------+
2 rows in set (0.00 sec)

    使用stu为左表的左外连接

    mysql> select * from stu left join exam on exam.id = stu.id;
+------+------+------+-------+
| id   | name | id   | grade |
+------+------+------+-------+
|    1 | jack |    1 |    56 |
|    2 | tom  |    2 |    76 |
|    3 | kity | NULL |  NULL |
|    4 | nono | NULL |  NULL |
+------+------+------+-------+
4 rows in set (0.00 sec)

    使用exam为左表的左外连接

    mysql> select * from exam left join stu on exam.id = stu.id;
+------+-------+------+------+
| id   | grade | id   | name |
+------+-------+------+------+
|    1 |    56 |    1 | jack |
|    2 |    76 |    2 | tom  |
|   11 |     8 | NULL | NULL |
+------+-------+------+------+
3 rows in set (0.00 sec)

    2.2.1 右外连接

    和左外连接对称，左侧不满足右侧的填充为NULL。

    select 字段 from 表名1 right join 表名2  on 连接条件；
    mysql> select * from exam right join stu on exam.id = stu.id;
+------+-------+------+------+
| id   | grade | id   | name |
+------+-------+------+------+
|    1 |    56 |    1 | jack |
|    2 |    76 |    2 | tom  |
| NULL |  NULL |    3 | kity |
| NULL |  NULL |    4 | nono |
+------+-------+------+------+
4 rows in set (0.00 sec)

    列出部门名称和这些部门的员工信息，同时列出没有员工的部门

    显然要以部门表为主

    mysql> select dept.deptno, dept.dname, emp.ename, emp.job from dept left join emp on emp.deptno = dept.deptno;
+--------+------------+--------+-----------+
| deptno | dname      | ename  | job       |
+--------+------------+--------+-----------+
|     20 | RESEARCH   | SMITH  | CLERK     |
|     30 | SALES      | ALLEN  | SALESMAN  |
|     30 | SALES      | WARD   | SALESMAN  |
|     20 | RESEARCH   | JONES  | MANAGER   |
|     30 | SALES      | MARTIN | SALESMAN  |
|     30 | SALES      | BLAKE  | MANAGER   |
|     10 | ACCOUNTING | CLARK  | MANAGER   |
|     20 | RESEARCH   | SCOTT  | ANALYST   |
|     10 | ACCOUNTING | KING   | PRESIDENT |
|     30 | SALES      | TURNER | SALESMAN  |
|     20 | RESEARCH   | ADAMS  | CLERK     |
|     30 | SALES      | JAMES  | CLERK     |
|     20 | RESEARCH   | FORD   | ANALYST   |
|     10 | ACCOUNTING | MILLER | CLERK     |
|     40 | OPERATIONS | NULL   | NULL      |
+--------+------------+--------+-----------+
15 rows in set (0.00 sec)

    左外连接是以左表为主

    右外连接是以右表为主

    到此这篇关于MySQL复合查询和内外连接的文章就介绍到这了,更多相关MySQL复合查询内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

    0

    上一篇:

    :下一篇

    更多 数据库 相关资讯：

    精彩评论

    暂无评论...
    登录 注册
    Qedev.com
    验证码 换一张
    取 消

    关注公众号

    热门标签

    图文推荐

    MySQL字符串日期格式转换的几种常用方法例子解析

    MySQL字符串日期格式转换的几种常用方法例子解析
    详解Sqlsugar调用Oracle的存储过程

    详解Sqlsugar调用Oracle的存储过程
    SpringBoot集成Mongodb的操作方法

    SpringBoot集成Mongodb的操作方法
    如何用PyMongo在Python中操作MongoDB的超完整指南

    如何用PyMongo在Python中操作MongoDB的超完整指南
    Vcenter清理/storage/archive空间的处理方式

    Vcenter清理/storage/archive空间的处理方式
    mysql插入带emoji表情的数据报错问题

    mysql插入带emoji表情的数据报错问题
    Mysql允许他人访问本机数据库的实现步骤

    Mysql允许他人访问本机数据库的实现步骤
    Docker安装MongoDB的过程(mongo.latest)

    Docker安装MongoDB的过程(mongo.latest)

    开发者开发者网给大家分享系统运维,大数据运维,云计算,编程开发技巧,路由交换,运维和开发相关的资讯及技术文章，同时StackOverflow中文社区，知识经验交流分享。

    法律声明：本站内容均为网友上传，网站举办方负责审核和监督，如存在版权或非法内容，欢迎举报，我们将尽快予以删除。邮箱：devze@qq.com

    Copyright © 2018-2020 开发者. All rights reserved. Powered by 开发者ICP备案号: 京ICP备10032868号-9