我正在参加「掘金·启航计划」
多表查询
简介
概念:
- 多表查询:指的是从多张表中查询数据,
分类:
-
连接查询
-
内连接:相当于查询 A B 交集的数据
-
外连接:
- 左外连接:查询 A 表所有数据以及交集部分的数据
- 右外连接:查询 B 表所有数据以及交集部分的数据
-
-
子查询
内连接
内连接相当于查询 A B 交集的数据。
- 隐式内连接:
SELECT
字段列表
FROM
表1,表2...
WHERE
条件;
案例:连接 员工表 和 部门表,使用隐式内连接
-- 查询 emp 的 name gender 以及 dept 的 dname
SELECT
emp.name,
emp.gender,
dept.dname
FROM
emp,
dept
WHERE
emp.dep_id = dept.did;
-- 同样的语句,我们可以给表使用别名:
SELECT
t1.name,
t1.gender,
t2.dname
FROM
emp t1,
dept t2
WHERE
t1.dep_id = t2.did;
- 显示内连接
SELECT
字段列表
FROM
表1 [INNER]
JOIN
表2
ON
条件;
案例:比如连接 员工表 和 部门表,使用显示内连接
-- 查询 emp 的 name gender 以及 dept 的 dname
SELECT
emp.name,
emp.gender,
dept.dname
FROM
emp
INNER JOIN
dept
ON
emp.dep_id = dept.did;
-- 注意,inner 关键字可以省略:
SELECT
emp.name,
emp.gender,
dept.dname
FROM
emp
JOIN
dept
ON
emp.dep_id = dept.did;
外连接
-- 左外连接:查询 表1 所有数据和交集部分的数据
SELECT
字段列表
FROM
表1
LEFT [OUTER] JOIN
表2
ON
条件;
-- 右外连接:查询 表2 所有数据和交集部分的数据
SELECT
字段列表
FROM
表1
RIGHT [OUTER] JOIN
表2
ON
条件;
子查询
概念:
- 子查询指的是查询中嵌套查询。
分类:
- 子查询根据查询结果的不同,作用也不用。
- 通常查询结果可以分为三种:单行单列、多行单列、多行多列
代码:
-- 单行单列:作为条件值,使用 = != > < 等操作符进行条件判断
-- 语法如下:
SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询语句);
-- 多行单列:作为条件值,使用 in 关键字进行条件判断
-- 语法如下:
SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询语句);
-- 多行多列:作为虚拟表
-- 语法如下:
SELECT 字段列表 FROM (子查询语句) WHERE 条件;
案例
建表语句
-- 职务表(job)
CREATE TABLE job(
id INT PRIMARY KEY, -- 职务编号:id
jname VARCHAR(20), -- 职务名称:jname
description VARCHAR(50) -- 职务描述:description
);
-- 部门表(dept)
CREATE TABLE dept(
id INT PRIMARY KEY, -- 部门编号:id
dname VARCHAR(50), -- 部门名称:dname
location VARCHAR(50) -- 部门所在地:location
);
-- 员工表(emp)
CREATE TABLE emp(
id INT PRIMARY KEY, -- 员工编号:id
ename VARCHAR(50), -- 员工姓名:ename
job_id INT, -- 职务编号:job_id
dept_id INT, -- 部门编号:dept_id
mgr INT, -- 上级领导:mgr
joinDate DATE, -- 入职日期:joinDate
salary DECIMAL(7,2), -- 工资:salary
bonus DECIMAL(7,2), -- 奖金:bonus
CONSTRAINT emp_jobId_ref_job_id_fk FOREIGN KEY(job_id) REFERENCES job(id),
CONSTRAINT emp_deptId_ref_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(id)
);
-- 工资等级表(salaryGrade)
CREATE TABLE salaryGrade(
grade INT PRIMARY KEY, -- 级别:id
lSalary INT, -- 最低工资:lSalary
hSalary INT -- 最高工资:hSalary
);
数据的填充
-- 添加五个部门:
INSERT INTO
dept(id, dname, location)
VALUES
(10, '行政办公部', '北京'),
(20, '人力资源部', '上海'),
(30, '生产技术部', '广州'),
(40, '计划营销部', '深圳'),
(50, '财务部', '北京');
-- 添加四个职务:
INSERT INTO
job(id, jname, description)
VALUES
(1, '董事长', '管理整个公司'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向顾客推销产品'),
(4, '文员', '使用办公软件');
-- 添加员工:
INSERT INTO
emp(id,ename,job_id,dept_id,mgr,joinDate,salary,bonus)
VALUES
(1001, '墨弦', 1, 10, null, '2021-06-27', null, null),
(1002, '竹青', 2, 20, 1001, '2021-07-03', '35000.00', null),
(1003, '倾川', 2, 20, 1001, '2021-07-08', '30000.00', null),
(1004, '清妍', 2, 20, 1001, '2021-07-08', '30000.00', null),
(1005, '蒙易', 4, 30, 1002, '2021-07-15', '28000.00', '4000.00'),
(1006, '恬雅', 4, 50, 1003, '2021-07-15', '28000.00', null),
(1007, '世御', 3, 40, 1004, '2021-07-15', '22000.00', null),
(1008, '清芷', 4, 30, 1004, '2022-02-07', '28000.00', '3000.00'),
(1009, '明轩', 4, 50, 1002, '2022-06-07', '20000.00', '4000.00'),
(1010, '言风', 3, 40, 1004, '2022-07-07', '15000.00', null),
(1011, '忆珊', 4, 30, 1002, '2022-08-07', '14000.00', '4000.00'),
(1012, '钧枫', 3, 40, 1003, '2022-08-07', '12000.00', null),
(1013, '语真', 4, 50, 1004, '2022-11-07', '7000.00', '3000.00'),
(1014, '绮灵', 4, 30, 1003, '2022-11-07', '14000.00', '4000.00'),
(1015, '奇羽', 3, 40, 1003, '2022-12-07', '12000.00', null);
-- 添加 5 个工资等级
INSERT INTO
salaryGrade(grade, lSalary, hSalary)
VALUES
(1, 7000, 11000),
(2, 12000, 14000),
(3, 15000, 20000),
(4, 22000, 28000),
(5, 30000, 35000);
多表查询语句
- 查询所有员工信息(编号,姓名,工资,职务名称,职务描述)
/*
分析:
1. 前三个信息属于员工表 emp ,即 id, ename, salary
2. 后两个信息属于职务表 job ,即 jname, description
3. 这两张表属于一对多的关系,即 emp.job_id = job.id
4. 因此可以用 内连接 的方式来查询
*/
-- 隐式内连接:
SELECT
emp.id,
emp.ename,
emp.salary,
job.jname,
job.description
FROM
emp, job
WHERE
emp.job_id = job.id
ORDER BY
emp.id;
-- 显式内连接:
SELECT
emp.id,
emp.ename,
emp.salary,
job.jname,
job.description
FROM
emp
INNER JOIN job ON emp.job_id = job.id
ORDER BY emp.id;
- 在一个需求的基础上,增加查询的字段:部门名称以及部门的位置
/*
分析:
1. 新增添的查询字段位于部门表 dept 中
2. dept 和 emp 同样是一对多的关系,即emp.dept_id = dept.id
3. 同样使用内连接的方式
*/
-- 隐式内连接:
SELECT
emp.id,
emp.ename,
emp.salary,
job.jname,
job.description,
dept.dname,
dept.location
FROM
emp, job, dept
WHERE
emp.job_id = job.id and emp.dept_id = dept.id
ORDER BY
emp.id;
-- 显式内连接:
SELECT
emp.id,
emp.ename,
emp.salary,
job.jname,
job.description,
dept.dname,
dept.location
FROM
emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON emp.dept_id = dept.id
ORDER BY emp.id;
- 查询员工的姓名,工资以及工资的等级:
/*
分析:
1. 前 2 个信息属于员工表 emp ,即 ename, salary
2. 而 工资等级 属于 salaryGrade 表中的信息
3. 我们可以使用表中的字段 lSalary 和 hSalary 来判断工资等级
*/
-- 语句如下:
SELECT
t1.ename,
t1.salary,
t2.grade
FROM
emp t1,
salaryGrade t2
WHERE
t1.salary >= t2.lSalary
AND
t1.salary <= t2.hSalary
ORDER BY
t1.salary;
- 将这四张表联合查询:
- 查询员工姓名和工资、职务名和职务描述、部门名称与位置 以及 工资等级
SELECT
emp.id,
emp.ename,
emp.salary,
job.jname,
job.description,
dept.dname,
dept.location,
t2.grade
FROM
emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON emp.dept_id = dept.id
INNER JOIN salaryGrade t2 ON
emp.salary BETWEEN t2.lSalary AND t2.hSalary
ORDER BY emp.id;
- 查询出 部门编号、部门名称、部门位置、部门人数:
/*
分析:
1. 前三个信息属于部门表 dept
2. 最后一个部门人数可以先按照部门id进行分组,然后再调用聚合函数count(*) 来统计数量即可
3. 最后根据子查询,让部门表和分组后的表进行内连接
*/
-- 语句如下:
SELECT
dept.*,
t1.count
FROM
dept,
(
SELECT
dept_id,
count(*) AS count
FROM
emp
GROUP BY
dept_id
) AS t1
WHERE
dept.id = t1.dept_id;