mySQL子查询

一、where后面的列自查询（多行子查询）
in/not in(可选择值列表) ---等于列表中的任意一个
    比如： a in(10,20,30); 可以替换为a = 10 or a = 20 or a=30;
any/some ---和子查询返回结果的某个值满足比较关系就返回true
    比如：a > any(10,20,30), 可以替换为 a > min(10,20,30);
all --- 和子查询中的每一个值都进行比较都满足比较关系时返回true
    比如：a > all(10,20,30); 可替换为a > max(10,20,30);

#列出location_id为1400和1700的部门中所有员工的姓名

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

#查询其它工种中比job_id为`IT_PROG`的员工中某一员工工资低的员工工号、姓名、和工资

SELECT e.job_id, e.last_name, e.salary 员工工资
FROM 
    employees e
WHERE
    e.salary < ANY(
            SELECT salary 
            FROM
                employees
            WHERE
                job_id = 'IT_PROG'
        )
AND
    e.job_id != 'IT_PROG' #e.job_id <> 'IT_PROG'
ORDER BY 员工工资 DESC;

   

    

#查询其它工种中比job_id为`IT_PROG`的员工中所有员工工资都低的员工工号、姓名、和工资    

SELECT e.job_id, e.last_name, e.salary as em_salarys
FROM 
    employees e
WHERE
    e.salary < ALL(
            SELECT salary 
            FROM
                employees
            WHERE
                job_id = 'IT_PROG'
        )
AND
    e.job_id != 'IT_PROG' /*e.job_id <> 'IT_PROG'*/
ORDER BY em_salarys DESC;

#查询每个部的部门信息和部门对应员工个数---使用非子查询方法

SELECT d.*,COUNT(employee_id) 部门人数
FROM
    departments d
LEFT JOIN 
    employees e
ON 
    d.`department_id` = e.`department_id`
GROUP BY d.`department_id`
ORDER BY 部门人数 DESC;

   
#查询每个部的部门信息和部门对应员工个数---使用selec后跟子查询    
#执行过程：先列出部门信息，然后从列出部门信息第一条到最后一条逐条和子查询进行操作---唯一一个子查询后于主查询进行

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

#查询员工号等于102的部门

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

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

SELECT j.`grade_level` FROM `jop_grades` j 
SELECT AVG(salary) FROM `employees` e GROUP BY e.department_id

SELECT g.`grade_level`,avg_inf.avg_sal
FROM
    (SELECT AVG(salary) avg_sal FROM employees  GROUP BY department_id) avg_inf #这里子语句相当虚拟出一个新的表单
INNER JOIN 
    jop_grades g
ON  
    avg_inf.avg_sal BETWEEN g.lowest_sal AND g.highest_sal

#列出各个部门中工资高于本部门平均工资的员工数和部门号

#1、列出各部门员工数和部门号 ---虚拟表一

SELECT e1.department_id, COUNT(*),salary
FROM 
    employees AS e1
GROUP BY 
    e1.department_id

#2、列出各个部门平均工资---虚拟表二，为了和表一能够形成关系，表二列也列出department_id

SELECT AVG(salary) avg_sal, department_id
FROM 
    employees AS e2
GROUP BY 
    e2.department_id;

#合并

SELECT e1.department_id, COUNT(*)
FROM 
    employees AS e1
INNER JOIN 
    (SELECT department_id, AVG(salary) avg_sal FROM employees AS e2 GROUP BY e2.department_id) b3
ON 
    e1.`department_id` = b3.department_id
AND e1.`salary` > b3.avg_sal
GROUP BY 
    e1.`department_id`

二、exists后面（相关子查询）

作用是判断子查询有没有结果的存在。

SELECT EXISTS (SELECT department_id FROM employees);

返回结果是：1

SELECT EXISTS (SELECT department_id FROM employees WHERE department_id = 9999);

返回结果是：0

一般exists很少使用，因为都可以使用IN去替代
#查询员工人数不为0的部门名 ---使用exists

SELECT d.department_name
FROM 
	departments AS d
WHERE EXISTS(SELECT COUNT(*) FROM employees e WHERE d.`department_id` = e.`department_id` )

#查询员工人数不为0的部门名 ---使用IN

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