【JavaWeb】：多表查询

翼同学
50 阅读2分钟

我正在参加「掘金·启航计划」

多表查询

简介

概念：

  • 多表查询：指的是从多张表中查询数据，

分类：

  • 连接查询

    • 内连接：相当于查询 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;

image-20230121152502104.png

  • 在一个需求的基础上，增加查询的字段：部门名称以及部门的位置
/*
    分析：
        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;

image-20230121154406391.png

  • 查询员工的姓名，工资以及工资的等级：
/*
    分析：
        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;

image-20230121155955960.png

  • 将这四张表联合查询：
  • 查询员工姓名和工资、职务名和职务描述、部门名称与位置 以及 工资等级
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;

image-20230121161127655.png

  • 查询出 部门编号、部门名称、部门位置、部门人数：
/*
    分析：
        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;

image-20230121165646800.png

avatar
文章
阅读
粉丝