【DataBase】MySQL 17 子查询

2023-01-07 14:45:28

子查询

/*
含义：嵌套查询
出现在其他语句中的SELECT语句，成为子查询或者内查询
外部的查询语句，称为主查询或外查询

按子查询出现的位置
    SELECT:[仅标量子查询]
    FROM [表子查询]
    WHERE & HAVING [标量、列、行、子查询]
    EXISTS [相关子查询]
    
按结果集的行列数不同
    标量子查询     一行一列
    列子查询    多行一列
    行子查询    一行多列
    表子查询    多行多列

*/

标量子查询

-- WHERE & HAVING 后面
-- 1、标量子查询


-- 2、列子查询


-- 3、行子查询
/*
特点
子查询在小括号内
子查询一般在条件的右侧
标量子查询，一般搭配着单行操作符使用

列子查询、一般搭配着多行操作符使用
*/

-- 谁的工资比Abel高？
SELECT 
    `last_name`,`salary`
FROM
    `employees`
WHERE
    `salary` > (SELECT `salary` FROM `employees` WHERE `last_name` = 'Abel');


-- 返回工种ID和141号员工相同，工资比143员工多的员工，的姓名，工种ID和工资

-- 工种ID和141号员工相同的员工
SELECT `job_id` 
FROM `employees` 
WHERE `employee_id` = 141;

-- 143员工的工资
SELECT `salary` 
FROM `employees` 
WHERE `employee_id` = 143;

SELECT `last_name`,`job_id`,`salary`
FROM `employees`
WHERE 
`job_id`  = (
    SELECT `job_id` 
    FROM `employees` 
    WHERE `employee_id` = 141
) 
AND
`salary` > (
    SELECT `salary` 
    FROM `employees` 
    WHERE `employee_id` = 143
);

-- 返回公司工资最少的员工的，姓名、工种ID、工资

-- 工资最少
SELECT MIN(`salary`)
FROM `employees`;

SELECT 
    `last_name`,`job_id`,`salary`
FROM
    `employees`
WHERE
    `salary` = (SELECT MIN(`salary`) FROM `employees`);
    

-- 查询最低工资大于 50号部门最低工资的部门id和最低工资

-- 50号部门的最低工资
SELECT MIN(`salary`)
FROM `employees`
WHERE `department_id` = 50;


-- 每个部门的最低工资
SELECT MIN(`salary`)
FROM `employees`
GROUP BY `department_id`

-- 完成
SELECT MIN(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`
HAVING MIN(`salary`) > (
    SELECT MIN(`salary`)
    FROM `employees`
    WHERE `department_id` = 50
);

-- 子查询的执行优先主查询

列子查询

-- 列子查询



-- 查询Location_id是1400或者1700的部门中的所有员工姓名
SELECT 
    D.`location_id`,D.`department_name`,E.`last_name`
FROM 
    `departments` AS D,
    `employees` AS E
WHERE 
    D.`department_id` = E.`department_id`
    AND
    D.`location_id`  IN (1400,1700);
    

-- 第一步查询部门编号是 1400 & 1700
SELECT 
    D.`department_id` -- D.`location_id`
FROM 
    `departments` AS D
WHERE 
    D.`location_id`  IN (1400,1700);
    
-- 第二步 查询员工姓名，要求部门号是1列表中的某一个
SELECT `last_name`
FROM `employees`
WHERE `department_id` IN(
    SELECT 
        D.`department_id` -- D.`location_id`
    FROM 
        `departments` AS D
    WHERE 
        D.`location_id`  IN (1400,1700)
)



-- 查询 其他部门中比工种ID为'IT_PROG'部门任一工资低的员工的工号、姓名、工种ID以及工资

-- 查询1 工种ID为'IT_PROG'部门的任一工资低的员工
SELECT DISTINCT `salary` -- ,`department_id`,`last_name`
FROM `employees`
WHERE `job_id` = 'IT_PROG';

-- 查询2 员工的工号、姓名、工种ID以及工资低于1的任意一个
SELECT 
    `employee_id`,`last_name`,`salary`
FROM 
    `employees`
WHERE 
    `salary` < ANY(
        SELECT  MAX(`salary`) -- ,`department_id`,`last_name`
        FROM `employees`
        WHERE `job_id` = 'IT_PROG'
    ) 
    AND `job_id` <> 'IT_PROG';
    


-- 返回其他部门中比工种ID为IT_PROG部门所有工资都低的员工 的员工号，姓名，工种ID和工资

-- 工种ID为IT_PROG部门的员工的最高工资
SELECT MAX(`salary`)
FROM `employees`
WHERE `job_id` = 'IT_PROG';

-- 其他部门比1都低的员工的信息
SELECT `employee_id`,`last_name`,`job_id`,`salary`
FROM `employees`
WHERE `salary` < (
    SELECT MAX(`salary`)
    FROM `employees`
    WHERE `job_id` = 'IT_PROG'
) AND `job_id` <> 'IT_PROG';

行子查询

-- 行子查询
-- 员工编号最小且工资最高的信息

-- 员工编号最小
SELECT MIN(`employee_id`) FROM `employees`
-- 工资最高
SELECT MAX(`salary`) FROM `employees`

-- 等值筛选
SELECT 
    *
FROM 
    `employees`
WHERE 
    `employee_id` = (SELECT MIN(`employee_id`) FROM `employees`)
    AND
    `salary` = (SELECT MAX(`salary`) FROM `employees`);

-- 行子查询 多个字段同时查询
SELECT *
FROM `employees`
WHERE (`employee_id`,`salary`) = (SELECT MIN(`employee_id`),MAX(`salary`) FROM `employees`);

SELECT后子查询

-- SELECT后子查询
-- 每个部门的员工个数
SELECT D.`department_id`,D.`department_name`,(SELECT COUNT(*) FROM `employees` AS E WHERE D.`department_id` = E.`department_id`) "员工个数"
FROM `departments` AS D

-- 查询员工号=102的部门名

-- 查询部门ID
SELECT `department_id`
FROM `employees`
WHERE `employee_id` = 102;

-- 
SELECT `department_name` FROM`departments` WHERE `department_id` = (SELECT `department_id`
FROM `employees`
WHERE `employee_id` = 102)

-- SELECT 子查询 【只支持标量子查询】
SELECT (
    SELECT `department_name`
    FROM `departments` AS D
    INNER JOIN `employees` AS E
    ON D.`department_id` = E.`department_id`
    WHERE E.`employee_id` = 102
) "部门名"

FROM后子查询

-- 查询每个部门的平均工资的工资等级

-- 求平均工资
SELECT 
    AVG(`salary`)
FROM
    `employees` AS E
    INNER JOIN `departments` AS D
ON 
    E.`department_id` = D.`department_id`
GROUP BY 
    D.`department_id`
    
-- 求平均工资2
SELECT 
    AVG(`salary`),`department_id`
FROM
    `employees`
GROUP BY 
    `department_id`
    

-- 子查询的结果是一张表，要求连接查询时必须使用别名，否则无法引用
-- FROM后子查询
SELECT
    VG.*,JG.`grade_level`
FROM 
    (SELECT AVG(`salary`) AS 'avgSalary',`department_id` FROM `employees` GROUP BY `department_id`) AS VG
    INNER JOIN
    `job_grades` AS JG
ON 
    VG.avgSalary BETWEEN JG.`lowest_sal` AND JG.`highest_sal`;

EXISTS后相关子查询

只关心有还是没有的问题，有即1，无即0、

-- 查询有员工的部门名
SELECT `department_name`
FROM `departments` AS D
WHERE EXISTS(
    SELECT *
    FROM `employees` AS E
    WHERE D.`department_id` = E.`department_id`
);

-- 反之没有员工的部门名
SELECT `department_name`
FROM `departments` AS D
WHERE NOT EXISTS(
    SELECT *
    FROM `employees` AS E
    WHERE D.`department_id` = E.`department_id`
);

-- 用IN查询
SELECT `department_name`
FROM `departments`
WHERE `department_id` IN( SELECT `department_id` FROM `employees`);




-- 案例2 查询没有女神的男友信息

-- IN查询
SELECT B1.*
FROM `boys` AS B1
WHERE B1.`id` NOT IN(
    SELECT `boyfriend_id`
    FROM `beauty`
);

-- EXISTS查询
SELECT B1.*
FROM `boys` AS B1
WHERE NOT EXISTS(
    SELECT `boyfriend_id`
    FROM `beauty` AS B2
    WHERE B1.`id` = B2.`boyfriend_id`
)

案例练习：

-- 1查询和ZlotKey相同部门的员工，姓名和工资

-- ZlotKey的部门
SELECT `department_id` FROM `employees` WHERE `last_name` = 'ZlotKey';


-- 筛选员工
SELECT 
    `last_name`,`salary`
FROM 
    `employees`
WHERE 
    `department_id` = (SELECT `department_id` FROM `employees` WHERE `last_name` = 'ZlotKey')
    AND
    `last_name` <> 'ZlotKey';
    


-- 2工资比公司平均工资高的员工的员工号，姓名和工资

-- 公司平均工资
SELECT AVG(`salary`) FROM `employees`

-- 
SELECT `employee_id`,`last_name`,`salary`
FROM `employees`
WHERE `salary` > (SELECT AVG(`salary`) FROM `employees`);

-- 3查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资


-- 各部门平均工资
SELECT AVG(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`

-- 每个部门高于 上面水平的员工
SELECT
    E.`employee_id`,E.`last_name`,E.`salary`
FROM
    (SELECT AVG(`salary`) AS 'avgSalary',`department_id` FROM `employees` GROUP BY `department_id`) AS VG
    INNER JOIN `employees` AS E
ON 
    VG.`department_id` = E.`department_id`
WHERE
    E.`salary` > VG.avgSalary;
    
-- 4查询和姓名中包含u字母的员工在相同部门的员工的员工号和姓名

-- 姓名中包含u字母的员工的部门
SELECT `department_id`,`last_name`
FROM `employees`
WHERE `last_name` LIKE '%u%';

--
SELECT
    E.`employee_id`,E.`last_name`
FROM
    (SELECT `department_id`,`last_name` FROM `employees` WHERE `last_name` LIKE '%u%') AS DL
    INNER JOIN `employees` AS E
ON
    E.`department_id` = DL.`department_id`
WHERE
    E.`last_name` NOT LIKE '%u%'

-- 答案
SELECT DISTINCT `department_id`
FROM `employees`
WHERE `last_name` LIKE '%u%';

SELECT `employee_id`,`last_name`
FROM `employees`
WHERE `department_id` IN (SELECT DISTINCT `department_id` FROM `employees` WHERE `last_name` LIKE '%u%')

-- 5 查询部门在Location_id = 1700的部门的员工的员工号
SELECT `department_id`
FROM `departments` WHERE `location_id` = 1700;

SELECT `employee_id`
FROM `employees`
WHERE `department_id` IN (SELECT `department_id` FROM `departments` WHERE `location_id` = 1700)

-- 6 管理者在是King的员工姓名和工资
    
-- 这题直接就查出来，不需要什么自连接。
SELECT `manager_id`,`last_name`,`salary`,`employee_id`
FROM `employees`
WHERE `last_name` ='K_ing'


-- 7工资最高的员工的姓名，firstname和Lastname显示一列

-- 工资最高
SELECT MAX(`salary`)
FROM `employees`

-- 
SELECT CONCAT(`first_name`," _ ",`last_name`), `salary`
FROM `employees`
WHERE  `salary` = (SELECT MAX(`salary`) FROM `employees`);

码农公寓

子查询

标量子查询

列子查询

行子查询

SELECT后子查询

FROM后子查询

EXISTS后 相关子查询

相关文章

EXISTS后相关子查询