MySQL子查询的一些练习

子查询的编写技巧

• 相对简单：从外往里
• 较复杂：从里到外
• 相关子查询：从外往里

1.查询平均工资最低的部门的信息和该部门的平均工资
SELECT中用相关子查询

方式一：
SELECT *,(SELECT AVG(salary) FROM employees t3 WHERE t3.department_id = t1.department_id) "AVG_SAL"
FROM employees t1
WHERE t1.department_id = (SELECT department_id #找最低部门的ID
                          FROM employees t2
                          GROUP BY department_id
                          HARING AVG(salary) = (SELECT MIN(avg_sal) #找部门的最低工资
                                                FROM(SELECT AVG(salary) avg_sal #找部门的平均工资
                                                     FROM employees
                                                     GROUP BY department_id
                                                     ) "t_dept_avg_sal"
                                               )
                          );

方式二：使用ALL
SELECT *,(SELECT AVG(salary) FROM employees t3 WHERE t3.department_id = t1.department_id) "AVG_SAL"
FROM employees t1
WHERE t1.department_id = (SELECT department_id #找最低部门的ID
                          FROM employees t2
                          GROUP BY department_id
                          HARING AVG(salary) <= ALL (SELECT AVG(salary) 
                              /*已经在其中*/          FROM employees
                                                     GROUP BY department_id
                                                    )
                         );

方式三：使用LIMIT
SELECT t1.*,(SELECT AVG(salary) FROM employees t3 WHERE t3.department_id = t1.department_id) "AVG_SAL"
FROM employees t1
WHERE t1.department_id = (SELECT department_id #找最低部门的ID
                          FROM employees t2
                          GROUP BY department_id
                          HARING AVG(salary) = (SELECT AVG(salary) 
                                                FROM employees #直接显示出所要的数据（最低工资）
                                                GROUP BY department_id
                                                ORDER BY AVG(salary) ASC
                                                LIMIT 0,1
                                               )
                         );
 方式四:多表的连接
 SELECT *,(SELECT AVG(salary) FROM employees t3 WHERE t3.department_id = t1.department_id) "AVG_SAL"
 FROM employees t1 JOIN (SELECT department_id,AVG(salary)
                         FROM employees t2   #将已经查出最低工资部门的信息作为一张表
                         GROUP BY department_id
                         ORDER BY AVG(salary) ASC
                         LIMIT 0,1
                        )"t_dept_avg_sal"
 ON t1.department_id = t_dept_avg_sal.department_id;                        
 
 2.查询公司manger的id
#方式一：自连接
SELECT DISTINCT t1.empolye_id,
FROM employees t1 JOIN employees t2
WHERE t1.employee_id = t2.manger_id;

#子查询
SELECT T1.employee_id
FROM emplpoyees t1
WHERE t1.employee_id IN (SELECT DISTINCT t2.manger_id
                         FROM employees t2
                        );

#使用EXISTS          有 (NOT)IN 的时候通常可以改成 (NOT)EXISTS
SELECT T1.employee_id
FROM emplpoyees t1
WHERE EXISTS(SELECT *
             FROM employees t2
             WHERE t1.employee_id = t2.manger_id
            );
             
3.查询各个部门 中最高工资中最低的那个部门的 最低工资是多少 (与第一题类似，方法在这里就只写一种了)

#思路：先找出每个部门工资最高的数，再找其中工资最低的部门，然后在该部门中寻找低的工资
SELECT MIN(salary)
FROM emplpyees 
WHERE deparment_id = (SELECT deparment_id #找到部门的ID
                      FROM employees
                      GROUP BY depaetment_id
                      HARING MAX(salary) = (SELECT max(salary) max_sal                                                                           FROM employees #找到最高工资中最低的那个部门的工资
                                            GROUP BY department_id
                                            ORDER BY max_sal ASC
                                            LIMIT 1
                                           )
                    );
                          
						 
#查询部门号，不包括job_id是‘ST’的
方式一;
select department_id
from departments
where department_id not in (SELECT distinct depqrtment_id,job_id#not in 不确定是否有一条数据
                            FROM employees
                            WHERE job_id ='ST'  
                           );

方式二：
select department_id
from departments t2
where department_id not EXISTS (SELECT *       #not exists:不满足输出，满足停止
                                FROM employees t1,
                                WHERE t1.department_id  = t2.department_id AND t1.job_id = 'ST' 
                               );

#查询每个部门下的人数大于5的部门的名称(相关)
SELECT department_name
FROM departments t1
WHERE 5 < (SELECT COUNT(*)
           FROM departments t2
           where t1.department_id = t2.department_id
          );