24-基础SQL-多表查询-子查询（嵌套查询）

子查询

 

案例：创建部门表 和 员工表（熟悉子查询）

-- 部门表
CREATE TABLE dept(
    id int auto_increment comment "ID" primary key ,
    name varchar(50) not null comment "部门名称"
)comment "部门表";

-- 员工表
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 "员工表";

-- 部门表数据
INSERT INTO dept(id,name)
VALUES
(1,"研发部"),
(2,"市场部"),
(3,"财务部"),
(4,"销售部"),
(5,"总经办")

-- 员工表数据
INSERT INTO emp(id,name,age,job,salary,entrydate,managerid,dept_id)
VALUES
(1, "员工1", 66, "总裁", 20000, "2000-01-01", null, 5),
(2, "员工2", 35, "经理", 12500, "2007-01-01", 1, 1),
(3, "员工3", 20, "开发", 8300, "2020-01-01", 2, 1),
(4, "员工4", 23, "开发", 10400, "2021-01-01", 2, 1),
(5, "员工5", 26, "开发", 11000, "2021-01-01", 3, 1)

 

子查询-标量子查询

 1）查询 "研发部" 的所有员工信息

a. 查询 "研发部" 的部门ID

SELECT dept.id FROM dept WHERE name = "研发部"

b. 根据 "研发部" 的部门ID，查询员工信息

SELECT * FROM emp WHERE dept_id = (SELECT dept.id FROM dept WHERE name = "研发部")

2）查询在 "员工3" 入职之后的员工信息

a. 查询 "员工3" 的入职日期

SELECT entrydate FROM emp WHERE name = "员工3"

b. 查询指定入职日期之后的员工信息

SELECT * FROM emp WHERE entrydate > (SELECT entrydate FROM emp WHERE name = "员工3")

 

子查询-列子查询

1）查询 "研发部" 和 "总经办" 的所有员工信息

a. 查询 "研发部" 和 "总经办" 的部门ID

SELECT id FROM dept WHERE name = "研发部" OR name = "总经办"

b.根据部门ID，查询员工信息

SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name = "研发部" OR name = "总经办");

2）查询比  "研发部" 所有人工资都高的员工信息

a. 查询 "研发部" 所有人员的工资

SELECT salary FROM emp 

WHERE dept_id = (SELECT id FROM dept WHERE name = "研发部")

b. 比 "研发部" 所有人工资都高的员工信息

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）查询比  "研发部" 任意一人工资都高的员工信息

a. 查询 "研发部" 所有人员的工资

SELECT salary FROM emp 

WHERE dept_id = (SELECT id FROM dept WHERE name = "研发部")

b. 比 "研发部" 任意一人工资都高的员工信息

SELECT * FROM emp 

WHERE salary > ANY(SELECT salary FROM emp 

WHERE dept_id = (SELECT id FROM dept WHERE name = "研发部"))

-- 根据题意，也可以理解为比 研发部最低工资 高的员工信息
SELECT * FROM emp 

WHERE salary > (SELECT MIN(salary) FROM emp 

WHERE dept_id = (SELECT id FROM dept WHERE name = "研发部"))

 

子查询-行子查询

1）查询 与 "员工4" 直属领导相同的员工信息

a. 查询 "员工4" 的直属领导

SELECT managerid FROM emp WHERE name = "员工4"

b. 查询 与 "员工4" 直属领导相同的员工信息

SELECT * FROM emp WHERE managerid IN (SELECT managerid FROM emp WHERE name = "员工4")

 

子查询-表子查询

1）查询与 "员工2" 和 "员工3" 职位和部门相同的员工信息

a. 查询 "员工2" 和 "员工3" 的职位和部门信息

SELECT job, dept_id FROM emp WHERE name = "员工2" OR name = "员工3"

b. 查询与 "员工3" 职位和部门相同的员工信息

SELECT * FROM emp WHERE (job,dept_id) IN (SELECT job, dept_id FROM emp WHERE name = "员工2" OR name = "员工3")

2）查询入职日期是 "2020-01-01" 之后的员工信息，及其部门信息

a. 入职日期是 "2020-01-01" 之后的员工信息

SELECT * FROM emp WHERE entrydate > "2020-01-01"

b. 根据这部分员工信息，查询对应的部门信息

SELECT e.*, d.name as "所属部门" FROM(

(SELECT * FROM emp WHERE entrydate > "2020-01-01") e

LEFT JOIN

dept d ON e.dept_id = d.id)