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

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 【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;
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4天前
|
关系型数据库 MySQL 分布式数据库
《MySQL 简易速速上手小册》第6章:MySQL 复制和分布式数据库(2024 最新版)
《MySQL 简易速速上手小册》第6章:MySQL 复制和分布式数据库(2024 最新版)
29 2
|
20天前
|
SQL 数据可视化 关系型数据库
轻松入门MySQL:深入探究MySQL的ER模型,数据库设计的利器与挑战(22)
轻松入门MySQL:深入探究MySQL的ER模型,数据库设计的利器与挑战(22)
104 0
|
20天前
|
存储 关系型数据库 MySQL
轻松入门MySQL:数据库设计之范式规范,优化企业管理系统效率(21)
轻松入门MySQL:数据库设计之范式规范,优化企业管理系统效率(21)
|
20天前
|
关系型数据库 MySQL 数据库
轻松入门MySQL:精准查询,巧用WHERE与HAVING,数据库查询如虎添翼(7)
轻松入门MySQL:精准查询,巧用WHERE与HAVING,数据库查询如虎添翼(7)
|
21天前
|
存储 关系型数据库 MySQL
数据库字符编码MySQL中使用UTF-8还是UTFB4
数据库字符编码MySQL中使用UTF-8还是UTFB4
20 0
|
25天前
|
缓存 NoSQL 关系型数据库
在Python Web开发过程中:数据库与缓存,MySQL和NoSQL数据库的主要差异是什么?
MySQL是关系型DB,依赖预定义的表格结构,适合结构化数据和复杂查询,但扩展性有限。NoSQL提供灵活的非结构化数据存储(如JSON),无统一查询语言,但能横向扩展,适用于大规模、高并发场景。选择取决于应用需求和扩展策略。
114 1
|
25天前
|
SQL 关系型数据库 MySQL
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(8.0版本升级篇)
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(8.0版本升级篇)
95 0
|
16天前
|
存储 关系型数据库 MySQL
MySQL基础入门:数据库操作全攻略
MySQL基础入门:数据库操作全攻略
47 0
|
16天前
|
关系型数据库 MySQL 数据库
卸载云服务器上的 MySQL 数据库
卸载云服务器上的 MySQL 数据库
31 0
|
20天前
|
SQL 关系型数据库 MySQL
轻松入门MySQL:深入学习数据库表管理,创建、修改、约束、建议与性能优化(3)
轻松入门MySQL:深入学习数据库表管理,创建、修改、约束、建议与性能优化(3)