mysql 子查询

# 子查询
/*
出现在其他语句中的select语句，成为子查询或内查询
外部的查询语句，成为主查询或外查询

分类：
按子查询出现的位置：
    select：
        仅支持标量子查询
    from：
        支持表子查询
    where或having：☆
        标量子查询，√
                列子查询  √
        行子查询
    exists(相关子查询)：
        表子查询
按功能或结果集的行列数：
    标量子查询（结果集只有一行一列），也称单行子查询
    列子查询（结果集是一列多行）    ，也称多行子查询
    行子查询（结果集为一行多列）
    表子查询（结果集一般为多行多列）

*/

#一、 where 或 having后面
/*
1. 标量子查询
2. 列子查询
3. 行子查询

特点：
1. 子查询放在小括号内
2. 子查询一般放在条件的右侧
3. 标量子查询，一般搭配着单行操作符使用
    > < >= <= <>
4. 列子查询，一般搭配多行操作符使用
    any/some all in
*/

#1. 标量子查询
# 谁的工资比abel高
#1.查询abel的工资
SELECT e.`salary`
FROM employees e
WHERE e.`last_name` = 'Abel';

#2.查询比Abel工资高的人
SELECT 
  r.`last_name`,
  r.`salary` 
FROM
  employees r 
WHERE r.`salary` > 
  (SELECT 
    e.`salary` 
  FROM
    employees e 
  WHERE e.`last_name` = 'Abel') ;


# 返回job_id与141号员工相同，salary比143号员工多的员工
SELECT e.`salary`
FROM employees e
WHERE e.`employee_id` = 141;

SELECT e.`job_id`
FROM employees e
WHERE e.`employee_id` = 141;

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

# 返回工资最少的员工的last_name job_id salary
SELECT MIN(e.`salary`)
FROM employees e;

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

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

SELECT MIN(e.`salary`)
FROM employees e
WHERE e.`department_id` = 50;

#查询每个部门的最低工资
SELECT e.`department_id`,MIN(e.`salary`) 
FROM employees e
GROUP BY e.`department_id`;

SELECT ee.`department_id`, MIN(ee.`salary`) AS m
FROM employees ee
GROUP BY ee.`department_id`
HAVING m > (
    SELECT MIN(e.`salary`)
    FROM employees e
    WHERE e.`department_id` = 50
);

# 列子查询
/*
多行操作符：
in/not in : 等于列表中的任意一个
any|some: 和子查询返回的某一个值比较
all:和子查询返回的所有值比较
*/
# 返回location_id为1400或1700的部门员工姓名
SELECT e.`last_name`,e.`department_id`,l.`location_id`
FROM employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`
INNER JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE l.`location_id` IN (1400,1700);

SELECT DISTINCT d.`department_id`
FROM departments d
WHERE d.`location_id` IN (1400,1700);

SELECT e.`last_name`,e.`department_id`
FROM employees e
WHERE e.`department_id` IN (
    SELECT distinct d.`department_id`
    FROM departments d
    WHERE d.`location_id` IN (1400,1700)
);

SELECT e.`last_name`,e.`department_id`
FROM employees e
WHERE e.`department_id` = ANY (
    SELECT d.`department_id`
    FROM departments d
    WHERE d.`location_id` IN (1400,1700)
);

SELECT e.`last_name`,e.`department_id`
FROM employees e
WHERE e.`department_id` IN (
    SELECT d.`department_id`
    FROM departments d
    WHERE d.`location_id` IN (1400,1700)
);

SELECT e.`last_name`,e.`department_id`
FROM employees e
WHERE e.`department_id` <> ALL (
    SELECT d.`department_id`
    FROM departments d
    WHERE d.`location_id` IN (1400,1700)
);

# 返回其他工种比job_id为‘IT_PROG’部门任意工资都低的
SELECT DISTINCT e.`salary`
FROM employees e
WHERE e.`job_id`='IT_PROG';


SELECT ee.`last_name`,ee.`employee_id`,ee.`job_id`,ee.`salary`
FROM employees ee
WHERE ee.`salary`< ANY(
    SELECT DISTINCT e.`salary`
    FROM employees e
    WHERE e.`job_id`='IT_PROG'
) AND ee.`job_id` <> 'IT_PROG';

SELECT ee.`last_name`,ee.`employee_id`,ee.`job_id`,ee.`salary`
FROM employees ee
WHERE ee.`salary`< (
    SELECT MAX(e.`salary`)
    FROM employees e
    WHERE e.`job_id`='IT_PROG'
) AND ee.`job_id` <> 'IT_PROG';


# 返回其他工种比job_id为‘IT_PROG’部门所有工资都低的
SELECT ee.`last_name`,ee.`employee_id`,ee.`job_id`,ee.`salary`
FROM employees ee
WHERE ee.`salary`< ALL(
    SELECT DISTINCT e.`salary`
    FROM employees e
    WHERE e.`job_id`='IT_PROG'
) AND ee.`job_id` <> 'IT_PROG';



SELECT ee.`last_name`,ee.`employee_id`,ee.`job_id`,ee.`salary`
FROM employees ee
WHERE ee.`salary`< (
    SELECT MIN(e.`salary`)
    FROM employees e
    WHERE e.`job_id`='IT_PROG'
) AND ee.`job_id` <> 'IT_PROG';

#3 行子查询， 一行多列，多行多列
#案例：查询员工编号最小并且工资最高的员工信息
SELECT MIN(e.`employee_id`)
FROM employees e;

SELECT MAX(ee.`salary`)
FROM employees ee;

SELECT*
FROM employees e
WHERE e.`employee_id` = (
    SELECT MIN(e.`employee_id`)
    FROM employees e
)AND e.`salary` = (
    SELECT MAX(ee.`salary`)
    FROM employees ee
)

# 必须具有同样的筛选判断符号，比如都用 =
SELECT * 
FROM employees
WHERE (employee_id, salary) = (
    SELECT MIN(employee_id),MAX(salary)
    FROM employees
);

#select 后面
# 仅支持标量子查询
#案例：查询每个部门的员工个数
SELECT d.*, IFNULL(cc.c,0)AS c
FROM  departments d
LEFT JOIN (
    SELECT COUNT(*) c,department_id
    FROM employees
    GROUP BY department_id
)AS cc
ON d.`department_id`=cc.department_id;

SELECT d.*,(
    SELECT COUNT(*)
    FROM employees e
    WHERE e.department_id = d.`department_id`
)AS 个数
FROM departments d;

#查询员工号=102的部门名
SELECT d.*
FROM departments d
WHERE d = (
    SELECT e.`department_id`
    FROM employees e
    WHERE e.`employee_id`=102
);

SELECT e.`department_id`
FROM employees e
WHERE e.`employee_id`=102;

SELECT (
    SELECT e.`department_id`
    FROM employees e
    WHERE e.`employee_id`=102
    AND e.department_id = d.`department_id`
)
FROM departments d;

SELECT e.`employee_id`,e.`department_id`,(
    SELECT d.department_name
    FROM departments d
    WHERE d.department_id = e.`department_id`
) AS 部门名
FROM employees e
WHERE e.`employee_id` = 102;

SELECT(
    SELECT d.department_name
    FROM departments d
    INNER JOIN employees e
    ON d.department_id = e.department_id
    WHERE e.employee_id=102

)AS 部门;

#三、from后面
# 查询每个部门的平均工资的工资等级
SELECT r.d_id,r.s,j.`grade_level`
FROM (
    SELECT e.`department_id` AS d_id, AVG(e.`salary`) AS s
    FROM employees e
    GROUP BY e.`department_id`
)AS r
INNER JOIN job_grades j
ON r.s BETWEEN j.`lowest_sal` AND j.`highest_sal`;

#四、exists 后面(相关子查询) 
/*
 语法:
    [not]exists(完整的查询语句)
 结果：
    1或0
*/
#子查询是否有值
SELECT EXISTS(SELECT * FROM employees);

#查询有员工的部门名
SELECT d.`department_name`
FROM departments d
WHERE d.`department_id` IN (
    SELECT e.`department_id`
    FROM employees e
    WHERE e.`department_id`=d.`department_id`
);

SELECT d.`department_name`
FROM departments d
WHERE EXISTS(
    SELECT e.`department_id`
    FROM employees e
    WHERE e.`department_id`=d.`department_id`
);


# 查询没有女朋友的男神信息
USE girls;
SELECT bb.*
FROM boys bb
WHERE bb.`id` NOT IN (
    SELECT boyfriend_id
    FROM beauty
);

SELECT bb.*
FROM boys bb
WHERE NOT EXISTS(
    SELECT boyfriend_id
    FROM beauty
    WHERE beauty.`boyfriend_id` = bb.`id`
);

#查询和Zlotkey相同部门的员工姓名和工资
SELECT ee.`last_name`, ee.`salary`
FROM employees ee
WHERE ee.`department_id` = (
    SELECT e.`department_id`
    FROM employees e
    WHERE e.`last_name` = 'Zlotkey'
);
#34

#查询工资比公司平均工资高的员工的员工号，姓名和工资
SELECT AVG(e.`salary`)
FROM employees e;

SELECT ee.`employee_id`,ee.`last_name`,ee.`salary`
FROM employees ee
WHERE ee.`salary` > (
    SELECT AVG(e.`salary`)
    FROM employees e
);
#51

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

SELECT e.`department_id` AS id, AVG(e.`salary`) AS r
FROM employees e
GROUP BY e.`department_id`;

SELECT ee.`last_name`,ee.`employee_id`,ee.`salary`,ee.`department_id`,r.avs
FROM employees ee
INNER JOIN(
    SELECT e.`department_id` AS id,AVG(e.`salary`) AS avs
    FROM employees e
    GROUP BY e.`department_id`
)AS r
ON ee.`department_id` = r.id
WHERE ee.`salary`>r.avs; #38


# 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT ee.`last_name`,ee.`employee_id`,ee.`department_id`
FROM employees ee
INNER JOIN (
    SELECT DISTINCT e.`department_id` AS id
    FROM employees e
    WHERE e.`last_name` LIKE '%u%'
)AS r
ON ee.`department_id` = r.id;  #96

SELECT DISTINCT e.`department_id`
FROM employees e
WHERE e.`last_name` LIKE '%u%'; #5

SELECT last_name,employee_id
FROM employees
WHERE department_id IN (
    SELECT DISTINCT e.`department_id`
    FROM employees e
    WHERE e.`last_name` LIKE '%u%'
);#96


#查询在部门的location_id为1700的部门工作的员工的员工号
SELECT e.`last_name`,e.`employee_id`
FROM employees e
INNER JOIN(
    SELECT DISTINCT d.`department_id` AS id
    FROM departments d
    INNER JOIN locations l
    ON d.`location_id` = l.`location_id`
    WHERE l.`location_id`=1700
) AS r
ON e.`department_id` = r.id;  #18

SELECT e.`last_name`,e.`employee_id`
FROM employees e
WHERE e.`department_id` = ANY(
    SELECT DISTINCT d.`department_id`
    FROM departments d
    WHERE d.`location_id`=1700
);

SELECT DISTINCT d.`department_id`
FROM departments d
WHERE d.`location_id`=1700;



#查询管理者是King的员工姓名和工资
SELECT e.`last_name`,e.`salary`
FROM employees e
WHERE e.`manager_id` IN (
    SELECT e.`employee_id`
    FROM employees AS e
    WHERE e.`last_name` = 'K_ing'
); #14


SELECT e.`employee_id`,e.`last_name`
FROM employees AS e
WHERE e.`last_name` = 'K_ing';

#查询工资最高的员工的姓名，要求first name 和last name 以姓.名显示
SELECT MAX(e.`salary`) 
FROM employees AS e;

SELECT CONCAT(ee.`first_name`,'.',ee.`last_name`)
FROM employees AS ee
WHERE ee.`salary` = (
    SELECT MAX(e.`salary`) 
    FROM employees AS e
);