MYSQL初阶学习笔记——进阶7：子查询

#进阶7：子查询
/*
含义：
    出现在其他语句中的select语句，称为子查询和内查询
    外部的查询语句，称为主查询或外查询


分类：
1.按子查询出现位置
    select 后：     只支持标量子查询
    from 后        主要支持表子查询
    where 或 having 后：（重）    
            标量子查询
            列子查询
            行子查询（较少）
    exists 后（相关子查询）：
            主要支持表子查询

2.按子查询的结果集的行列数不同
    标量子查询（结果集1行1列）
    列子查询（结果集1列多行）
    行子查询（结果集1行多列）
    表子查询（结果集为多行多列）

*/
#一、where 或 having 后：（重）
/*
特点：
    1.子查询放在括号内
    2.子查询一般放在条件右侧
    3.标量子查询，一般搭配单行操作符使用 > < >= <= == <>
    4.列子查询，一般搭配多行操作符使用 in any/some all
    5.子查询执行优先于主查询，先执行子查询后执行主查询
*/
USE myemployees;    
#1.标量子查询（单行子查询）

#案例1：谁的工资比 Abel 高?

##思路: 先查出Abel工资，再查比其高的工资
#1
SELECT salary
FROM employees
WHERE last_name='Abel';
#2
SELECT last_name,salary
FROM employees
WHERE salary>(
    SELECT salary
    FROM employees
    WHERE last_name='Abel'
    );

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资

##思路：1.找141号员工的job_id，143号员工的salary；2.在1基础上筛选
#1
SELECT job_id
FROM employees
WHERE employee_id=141;

SELECT salary
FROM employees
WHERE employee_id=143;

#2
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
    );
#案例3：返回公司工资最少的员工的last_name,job_id和salary

##思路：1.查出工资最少是多少；2.员工信息
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
    SELECT MIN(salary)
    FROM employees
    );

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

##思路：1.50号部门的最低工资 2.在1上筛选 部门id和最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
    SELECT MIN(salary)
    FROM employees
    WHERE department_id=50    
    );


#2.列子查询

#案例1：返回location_id是1400或1700的部门中的所有员工姓名

#思路：1.查询location_id是1400或1700的部门有哪些？2.查询这些部门的所有员工信息
SELECT last_name,department_id
FROM employees
WHERE department_id IN (
    SELECT DISTINCT department_id
    FROM departments
    WHERE location_id IN (1400,1700)
    )
ORDER BY department_id;

#案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary

##思路：1.job_id为‘IT_PROG’工种任一工资；2.其它工种中比1低的员工
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY (
    SELECT DISTINCT salary
    FROM employees
    WHERE job_id ='IT_PROG'    
    )
AND job_id <> 'IT_PROG';

#案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工   的员工号、姓名、job_id 以及salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ALL (
    SELECT DISTINCT salary
    FROM employees
    WHERE job_id ='IT_PROG'    
    )
AND job_id <> 'IT_PROG';


#3.行子查询（较少）(多个条件操作符相同时才可用)

#案例：查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary) = (
    SELECT MIN(employee_id),MAX(salary)
    FROM employees
    );



#二、select后面
/*
仅仅支持标量子查询
*/

#案例1：查询每个部门的员工个数
SELECT d.*,(
    SELECT COUNT(*)
    FROM employees e
    WHERE e.department_id=d.department_id
    ) AS 员工个数
FROM departments d;

#案例2：查询员工号=102的部门名

SELECT (
    SELECT d.department_name
    FROM departments d
    JOIN employees e
    ON d.department_id=e.department_id
    WHERE e.employee_id=102
) AS  de_na;


#三、from后面
/*
将子查询结果充当一张表，要求必须起别名
*/

#案例1：查询每个部门的平均工资的工资等级

SELECT AVG(salary) ags,department_id
FROM employees e
GROUP BY department_id;

SELECT ag_dep.*,g.`grade_level`
FROM (
    SELECT AVG(salary) ags,department_id
    FROM employees e
    GROUP BY department_id
) AS ag_dep
INNER JOIN job_grades g
ON ags BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#四、exists后面（相关子查询）

/*
查看子查询结果是否存在：存在为1，不存在为0

语法：
exists(完整的查询语句)
结果：
1或0
*/

SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);

#案例1：查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
    SELECT last_name
    FROM employees e
    WHERE e.`department_id`=d.`department_id`
    );


#案例2：查询没有女朋友的男神信息
SELECT b.*
FROM boys b
WHERE NOT EXISTS(
    SELECT *
    FROM beauty be
    WHERE be.boyfriend_id=b.id
    );

#=====================Test========================
#1.查询和Zlotkey相同部门的员工姓名和工资

USE myemployees;
SELECT last_name,salary
FROM employees
WHERE department_id=( 
    SELECT department_id
    FROM employees
    WHERE last_name='Zlotkey'
    );

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。

SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
    SELECT AVG(salary)
    FROM employees    
    )
ORDER BY salary;

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

SELECT employee_id,last_name,salary,e.department_id,ags_dep.av
FROM employees e
INNER JOIN(
    SELECT AVG(salary) av,department_id
    FROM employees
    GROUP BY department_id    
) AS ags_dep
ON e.`department_id`=ags_dep.department_id
WHERE e.`salary`>ags_dep.av;



#4.查询和姓名中包含字母u的员工在同一个部门 的员工的员工号和姓名

##1.查询部门；2.此部门下的员工号和姓名
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%';
#2
SELECT last_name,employee_id
FROM employees
WHERE department_id IN (
    SELECT DISTINCT department_id
    FROM employees
    WHERE last_name LIKE '%u%'
    );

#5.查询在部门的location_id为1700的部门工作的员工的员工号

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


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

#7.查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名

SELECT CONCAT(first_name,last_name) AS 姓名
FROM employees
WHERE salary=(
    SELECT MAX(salary)
    FROM employees
    );