AI 助理
备案控制台
开发者社区 数据库 文章 正文

【MySQL 数据库】3、多表查询

2024-03-01 72
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS PostgreSQL，集群系列 2核4GB
简介： 【MySQL 数据库】3、多表查询


一、多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

  • 一对多(多对一)
  • 多对多
  • 一对一

二、多表查询

CREATE TABLE dept ( id INT auto_increment COMMENT 'ID' PRIMARY KEY, NAME VARCHAR ( 50 ) NOT NULL COMMENT '部门名称' ) COMMENT '部门表';
INSERT INTO dept ( id, NAME )
VALUES
  ( 1, '研发部' ),
  ( 2, '市场部' ),
  ( 3, '财务部' ),
  ( 4, '销售部' ),
  ( 5, '总经办' ),
  ( 6, '人事部' );
CREATE TABLE emp (
id INT auto_increment COMMENT 'ID' PRIMARY KEY,
NAME VARCHAR ( 50 ) NOT NULL COMMENT '姓名',
age INT COMMENT '年龄',
job VARCHAR ( 20 ) COMMENT '职位',
salary INT COMMENT '薪资',
entrydate date COMMENT '入职时间',
managerid INT COMMENT '直属领导ID',
dept_id INT COMMENT '部门ID' 
) COMMENT '员工表';
ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY ( dept_id ) REFERENCES dept ( id );
INSERT INTO emp ( id, NAME, age, job, salary, entrydate, managerid, dept_id )
VALUES
  ( 1, '金庸', 66, '总裁', 20000, '2000-01-01', NULL, 5 ),
  ( 2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1 ),
  ( 3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1 ),
  ( 4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1 ),
  ( 5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1 ),
  ( 6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1 ),
  ( 7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3 ),
  ( 8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3 ),
  ( 9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3 ),
  ( 10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2 ),
  ( 11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2 ),
  ( 12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2 ),
  ( 13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2 ),
  ( 14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4 ),
  ( 15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4 ),
  ( 16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4 ),
  ( 17, '陈友谅', 42, NULL, 2000, '2011-10-12', 1, NULL );

# 会产生笛卡尔乘积
select * from emp, dept;
# 消除笛卡尔乘积
select * from emp, dept where emp.dept_id = dept.id;

多表查询分类:

三、内连接

1、查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)

# 隐式内连接
SELECT
  emp.NAME '员工名',
  dept.NAME '部门名' 
FROM
  emp,
  dept 
WHERE
  emp.dept_id = dept.id;

2、查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)

SELECT
  e.NAME '员工名',
  d.NAME '部门名' 
FROM
  emp e
  INNER JOIN dept d ON e.dept_id = d.id;

四、外连接

1、查询emp表的所有数据, 和对应的部门信息

SELECT
  e.*,
  d.name '部门名'
FROM
  emp e
  LEFT JOIN dept d ON e.dept_id = d.id;

2、查询dept表的所有数据, 和对应的员工信息(右外连接)

# 右外连接
SELECT
  d.*,
  e.*  
FROM
  emp e
  right JOIN dept d ON e.dept_id = d.id;
# 左外连接
SELECT
  d.*,
  e.* 
FROM
  dept d
  LEFT JOIN emp e ON d.id = e.dept_id;

五、自连接

1、查询员工 及其 所属领导的名字

SELECT
  e.*,
  m.NAME '领导名' 
FROM
  emp e
  JOIN emp m ON e.managerid = m.id;
SELECT
  * 
FROM
  emp;
SELECT
  e.*,
  m.NAME '领导名' 
FROM
  emp e,
  emp m 
WHERE
  e.managerid = m.id;

2、查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来

SELECT
  e.NAME '员工名',
  e.managerid '领导id',
  m.NAME '领导名' 
FROM
  emp e
  LEFT JOIN emp m ON e.managerid = m.id;

六、联合查询

需求：将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来

# 没有去除重复项
SELECT
  emp.NAME,
  emp.age,
  emp.salary 
FROM
  emp 
WHERE
  emp.salary < 5000 UNION ALL SELECT emp.NAME, emp.age, emp.salary FROM emp WHERE emp.age > 50;
# 去重
SELECT
  emp.NAME,
  emp.age,
  emp.salary 
FROM
  emp 
WHERE
  emp.salary < 5000 UNION SELECT emp.NAME, emp.age, emp.salary FROM emp WHERE emp.age > 50;

七、什么是子查询

八、标量子查询

1.查询 "销售部" 的所有员工信息

SELECT
  * 
FROM
  emp 
WHERE
  dept_id = ( SELECT id FROM dept WHERE NAME = '销售部' );

2.查询在 "方东白" 入职之后的员工信息

SELECT
  * 
FROM
  emp 
WHERE
  entrydate > ( SELECT entrydate FROM emp WHERE NAME = '方东白' );

九、列子查询

1.查询 "销售部" 和 "市场部" 的所有员工信息

SELECT
  * 
FROM
  emp 
WHERE
  dept_id IN ( SELECT id FROM dept WHERE NAME = '销售部' OR NAME = '市场部' );

2.查询比 财务部 所有人工资都高的员工信息

SELECT
  * 
FROM
  emp 
WHERE
  salary > ALL ( SELECT salary FROM emp WHERE dept_id = ( SELECT id FROM dept WHERE NAME = '财务部' ) );

【比 财务部 所有人工资都高的员工信息】换句话说就是：比 财务部 工资最高的人的工资还高的员工信息

# 标量子查询
SELECT
  * 
FROM
  emp 
WHERE
  salary > ( SELECT max( salary ) FROM emp WHERE dept_id = ( SELECT id FROM dept WHERE NAME = '财务部' ) );

3.查询比研发部其中任意一人工资高的员工信息

SELECT
  * 
FROM
  emp 
WHERE
  salary > SOME ( SELECT salary FROM emp WHERE dept_id = ( SELECT id FROM dept WHERE NAME = '研发部' ) );

十、行子查询

查询与 "张无忌" 的薪资及直属领导相同的员工信息

SELECT
  * 
FROM
  emp 
WHERE
  ( salary, managerid ) = ( SELECT salary, managerid FROM emp WHERE NAME = '张无忌' );

十一、表子查询

1.查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

SELECT
  * 
FROM
  emp 
WHERE
  ( job, salary ) IN ( SELECT job, salary FROM emp WHERE NAME IN ( '宋远桥', '鹿杖客' ) );

2.查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息

把查询出来的结果集做为临时表来用

# 子查询的结果作为临时表
SELECT
  t.*,
  d.*
FROM
  ( SELECT * FROM emp WHERE entrydate > '2006-01-01' ) t
  LEFT JOIN dept d ON t.dept_id = d.id;

十二、多表查询练习题

# losal: 最低薪资
# hisal: 最高薪资
# grade: 等级
CREATE TABLE salgrade ( grade INT, losal INT, hisal INT ) COMMENT '薪资等级表';
INSERT INTO salgrade
VALUES
  ( 1, 0, 3000 );
INSERT INTO salgrade
VALUES
  ( 2, 3001, 5000 );
INSERT INTO salgrade
VALUES
  ( 3, 5001, 8000 );
INSERT INTO salgrade
VALUES
  ( 4, 8001, 10000 );
INSERT INTO salgrade
VALUES
  ( 5, 10001, 15000 );
INSERT INTO salgrade
VALUES
  ( 6, 15001, 20000 );
INSERT INTO salgrade
VALUES
  ( 7, 20001, 25000 );
INSERT INTO salgrade
VALUES
  ( 8, 25001, 30000 );

1.查询员工的姓名、年龄、职位、部门信息 （隐式内连接）

# 隐式内连接
SELECT
  e.NAME,
  e.age,
  e.job,
  d.id '部门id',
  d.NAME '部门名' 
FROM
  emp e,
  dept d 
WHERE
  e.dept_id = d.id;

2.查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）

# 显示内连接
SELECT
  e.NAME,
  e.age,
  e.job,
  d.id '部门id',
  d.NAME '部门名' 
FROM
  emp e
  INNER JOIN dept d ON e.dept_id = d.id 
WHERE
  e.age < 30;

3.查询拥有员工的部门ID、部门名称

# 显式内连接
SELECT DISTINCT
  d.id,
  d.NAME 
FROM
  dept d
  INNER JOIN emp e ON d.id = e.dept_id;
  
# 隐式内连接
SELECT DISTINCT
  d.id,
  d.NAME 
FROM
  emp e,
  dept d 
WHERE
  d.id = e.dept_id;

4.查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出 来(外连接)

SELECT
  e.*,
  d.NAME '部门名' 
FROM
  emp e
  LEFT JOIN dept d ON e.dept_id = d.id 
WHERE
  e.age > 40;

5.查询所有员工的工资等级 ☆☆☆

表连接的条件是：工资范围（不一定只有外键可以作为表连接的条件）

SELECT
  e.id,
  e.NAME,
  e.salary,
  g.losal,
  g.hisal,
  g.grade '工资等级' 
FROM
  emp e
  JOIN salgrade g ON e.salary BETWEEN g.losal 
  AND g.hisal;

6.查询 "研发部" 所有员工的信息及 工资等级

SELECT
  e.*,
  s.* 
FROM
  emp e
  JOIN salgrade s ON e.salary BETWEEN s.losal 
  AND s.hisal 
WHERE
  e.dept_id = ( SELECT id FROM dept WHERE NAME = '研发部' );

7.查询 "研发部" 员工的平均工资

SELECT
  avg( e.salary ) '平均工资' 
FROM
  emp e 
WHERE
  e.dept_id = ( SELECT id FROM dept WHERE NAME = '研发部' );
SELECT
  avg( e.salary ) '平均工资' 
FROM
  emp e,
  dept d 
WHERE
  e.dept_id = d.id 
  AND d.NAME = '研发部';

8.查询工资比 "灭绝" 高的员工信息

# 标量子查询
SELECT
  e.* 
FROM
  emp e 
WHERE
  e.salary > ( SELECT salary FROM emp WHERE NAME = '灭绝' )

9.查询比平均薪资高的员工信息

SELECT
  e.* 
FROM
  emp e 
WHERE
  e.salary > ( SELECT avg( salary ) FROM emp );

10.查询低于本部门平均工资的员工信息

SELECT
  * 
FROM
  emp e1 
WHERE
  e1.salary < ( SELECT avg( e2.salary ) FROM emp e2 WHERE e2.dept_id = e1.dept_id );

11.查询所有的部门信息, 并统计部门的员工人数

SELECT
  d.id,
  d.NAME,
  ( SELECT count( * ) FROM emp e WHERE e.dept_id = d.id ) '人数' 
FROM
  dept d;
文章标签：
云数据库 RDS MySQL 版
数据库
关系型数据库
MySQL
关键词：
云数据库 RDS MySQL 版数据库
数据库mysql
数据库多表查询
云数据库 RDS MySQL 版多表查询
云数据库 RDS MySQL 版数据库多表查询
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
张思睿ZGQ
目录
相关文章
叫个什么名字
|
19天前
|
NoSQL 关系型数据库 MySQL
微服务架构下的数据库选择：MySQL、PostgreSQL 还是 NoSQL？
在微服务架构中，数据库的选择至关重要。不同类型的数据库适用于不同的需求和场景。在本文章中，我们将深入探讨传统的关系型数据库（如 MySQL 和 PostgreSQL）与现代 NoSQL 数据库的优劣势，并分析在微服务架构下的最佳实践。
叫个什么名字
120 9
小王老师呀
|
20天前
|
存储 SQL 关系型数据库
使用MySQL Workbench进行数据库备份
【9月更文挑战第13天】以下是使用MySQL Workbench进行数据库备份的步骤：启动软件后，通过“Database”菜单中的“管理连接”选项配置并选择要备份的数据库。随后，选择“数据导出”，确认导出的数据库及格式（推荐SQL格式），设置存储路径，点击“开始导出”。完成后，可在指定路径找到备份文件，建议定期备份并存储于安全位置。
小王老师呀
160 11
2的n次方_
|
16天前
|
存储 SQL 关系型数据库
MySQL的安装&数据库的简单操作
本文介绍了数据库的基本概念及MySQL的安装配置。首先解释了数据库、数据库管理系统和SQL的概念，接着详细描述了MySQL的安装步骤及其全局配置文件my.ini的调整方法。文章还介绍了如何启动MySQL服务，包括配置环境变量和使用命令行的方法。最后，详细说明了数据库的各种操作，如创建、选择和删除数据库的SQL语句，并提供了实际操作示例。
2的n次方_
58 13
MySQL的安装&数据库的简单操作
程序员小海绵
|
21天前
|
存储 SQL 关系型数据库
一篇文章搞懂MySQL的分库分表，从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
MySQL如何进行分库分表、数据迁移？从相关概念、使用场景、拆分方式、分表字段选择、数据一致性校验等角度阐述MySQL数据库的分库分表方案。
程序员小海绵
91 14
一篇文章搞懂MySQL的分库分表，从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
vohelon
|
3天前
|
关系型数据库 Unix MySQL
MySQL是一种关系型数据库管理系统
MySQL是一种关系型数据库管理系统
vohelon
11 2
糜终
|
6天前
|
Oracle NoSQL 关系型数据库
主流数据库对比：MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比：MySQL、PostgreSQL、Oracle和Redis的优缺点分析
糜终
17 2
蓝易云
|
11天前
|
SQL 关系型数据库 MySQL
创建包含MySQL和SQLServer数据库所有字段类型的表的方法
创建一个既包含MySQL又包含SQL Server所有字段类型的表是一个复杂的任务，需要仔细地比较和转换数据类型。通过上述方法，可以在两个数据库系统之间建立起相互兼容的数据结构，为数据迁移和同步提供便利。这一过程不仅要考虑数据类型的直接对应，还要注意特定数据类型在不同系统中的表现差异，确保数据的一致性和完整性。
蓝易云
22 4
蓝易云
|
20天前
|
SQL 监控 关系型数据库
MySQL数据库中如何检查一条SQL语句是否被回滚
检查MySQL中的SQL语句是否被回滚需要综合使用日志分析、事务状态监控和事务控制语句。理解和应用这些工具和命令，可以有效地管理和验证数据库事务的执行情况，确保数据的一致性和系统的稳定性。此外，熟悉事务的ACID属性和正确设置事务隔离级别对于预防数据问题和解决事务冲突同样重要。
蓝易云
30 2
土木林森
|
2月前
|
SQL 关系型数据库 MySQL
【揭秘】MySQL binlog日志与GTID：如何让数据库备份恢复变得轻松简单？
【8月更文挑战第22天】MySQL的binlog日志记录数据变更，用于恢复、复制和点恢复；GTID为每笔事务分配唯一ID，简化复制和恢复流程。开启binlog和GTID后，可通过`mysqldump`进行逻辑备份，包含binlog位置信息，或用`xtrabackup`做物理备份。恢复时，使用`mysql`命令执行备份文件，或通过`innobackupex`恢复物理备份。GTID模式下的主从复制配置更简便。
土木林森
172 2
阿里云开发者
|
2月前
|
弹性计算 关系型数据库 数据库
手把手带你从自建 MySQL 迁移到云数据库，一步就能脱胎换骨
阿里云瑶池数据库来开课啦！自建数据库迁移至云数据库 RDS原来只要一步操作就能搞定！点击阅读原文完成实验就可获得一本日历哦～
阿里云开发者
64 0

热门文章

最新文章

  • 1
    RDS for SQL server 空间问题排查汇总
  • 2
    【Mysql】服务没有响应控制功能。 请键入 NET HELPMSG 2186 以获得更多的帮助。
  • 3
    MySQL + Keepalived 双主热备搭建
  • 4
    mysql建库
  • 5
    (解释文)My SQL中主键为0和主键自排约束的关系
  • 6
    centos安装nginx+mysql+php+fastcgi+memcache最简单方法
  • 7
    关于MYSQL的行列转换
  • 8
    使用 EF Power Tool Code Frist 生成 Mysql 实体
  • 9
    模拟生产环境MySQL5.5升级Percona5.6演示步骤
  • 10
    异构平台同步（Mysql到Oracle）
  • 1
    Doris数据库安装插件流程
    355
  • 2
    sql数据库安装过程,SQL数据库的安装过
    84
  • 3
    17：数据库连接池与Servlet整合-Java Web
    103
  • 4
    多态关联在数据库设计中的应用和解决方案
    65
  • 5
    【C 言专栏】C 语言与数据库的连接与操作
    206
  • 6
    【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】（代码演示＆可cv代码）
    443
  • 7
    【MySQL-5】DDL的数据库操作：查询＆创建＆删除＆使用（可cv代码+演示图）
    88
  • 8
    【MySQL-1】理解关系型数据库＆数据的数据模型
    104
  • 9
    Servlet 教程 之 Servlet 数据库访问 2
    45
  • 10
    Java代码一键生成数据库文档（案例详解）
    242

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL
  • 如何支撑HTAP场景-HybridDB for MySQL系统架构和技术演进

    • 相关实验场景

    更多
  • 自建数据库迁移到云数据库
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 云原生HTAP数据库，让你的交易和分析一库搞定
  • 5分钟上手Flink MySQL连接器

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    无影云桌面