3.Mysql子查询练习

1.子查询概述

子查询指一个查询语句嵌套在另一个查询语句内部的查询，内部的查询是外部查询的条件，这个特性从MySQL4.1开始引入

子查询(内查询)在主查询之前执行完成

子查询的结果被主查询(外查询)使用

注意事项：

• 子查询要包含在括号内

• 将子查询放在比较条件的右侧

• 单行操作符对应单行子查询，多行操作符对应多行子查询

2.子查询的使用

2.1单行子查询

单行比较操作符

=等于，>大于，>=大于等于，<小于，<=小于等于，<>不等于

1.查找薪水大于所有员工平均薪水的员工

SELECT employee_id, first_name, last_name, salary FROM employees

              WHERE salary > ( SELECT AVG(salary) FROM employees)

2.查询工资大于149号员工工资的员工的信息 

select employee_id,last_name,salary  from employees
          where salary > ( select salary from employees where employee_id = 149 )

3.返回job_id与141号员工相同，salary比143号员工多的员工姓名，job _id和工资

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)

4.返回公司工资最少的员工的last_name,job_id和salary

select last_name,job_id,salary from employees where salary = ( select min(salary) from employees )

5.查询与147号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id

select employee_id,manager_id,department_id  from employees
where manager_id = (
           select manager_id from employees where employee_id = 147)
 and department_id = (select department_id from employees where employee_id = 147)
 and employee_id <> 147

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

select department_id,min(salary) min_salary  from employees
           where department_id is not null
group by department_id
having min_salary > (
    select min(salary) from employees where department_id = 50)

7.查询每一个部门的最低工资

select deptno, min(sal) min_sal from emp group by deptno

8.最低工资关联人员信息

select e.* from emp e, (select deptno, min(sal) min_sal from emp group by deptno) s

           where e.deptno = s.deptno   and e.sal = s.min_sal

9.先查询出部门的最低工资，然后匹配最低工资的雇员信息

select * from emp where sal in (select min(sal) from emp group by deptno)

2.2多列子查询

 1.查找属于位置ID为1700的所有员工

SELECT  employee_id, first_name, last_name  FROM  employees
WHERE
    department_id IN (SELECT department_id  FROM departments WHERE location_id = 1700)
ORDER BY first_name , last_name

2.返回其它job id中比job id为'IT_PROG’部门任一工资低的员工的员工号姓名、job id以及salary

select last_name,job_id,salary from employees
where salary < any (
    select salary from employees where job_id = 'IT_PROG'
)
and job_id <> 'IT_PROG'

3.返回其它job id中比job id为'IT_PROG’部门所有工资低的员工的员工号姓名、job id以及salary

select last_name,job_id,salary from employees
where salary < all (
    select salary from employees where job_id = 'IT_PROG')
and job_id <> 'IT_PROG'

4.查询平均工资最低的部门ID

select department_id,avg(salary) from employees group by department_id order by avg(salary) limit 1

5.询员工表中是领导的员工信息

select employee_id,last_name,manager_id from employees where employee_id in ( select manager_id from employees )

6.查询员工表中不是领导的员工信息

select employee_id,last_name,manager_id  from employees
where employee_id not in (
    select manager_id from employees where manager_id is not null)

7.EXISTS 与 NOT EXISTS关键字

查询公司管理者的employee_id，last_name，job_id，department_id信息

# 方式一 自连接

SELECT DISTINCT e1.employee_id,e1.last_name,e1.job_id,e1.department_id # 因为管理者可能管理多个员工，所以需要去重
FROM employees e1 JOIN employees e2 # 相当于求交集
WHERE e1.employee_id=e2.manager_id

# 方式二 子查询
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN( SELECT DISTINCT manager_id FROM employees)

# 方式三：exists
SELECT e1.employee_id,e1.last_name,e1.job_id,e1.department_id
FROM employees e1
WHERE EXISTS(SELECT *  FROM employees e2  WHERE e1.employee_id=e2.manager_id)

8.HAVING 中的子查询

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

SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id  # 只能想到使用group by...having,因为where中不能使用聚合函数
HAVING MIN(salary)>(
                    SELECT MIN(salary) FROM employees WHERE department_id=50)

9.CASE中的子查询

SELECT  STUDENT_NAME,
    (CASE WHEN score < 60 THEN '不及格'
        WHEN score >= 60 AND score < 80 THEN '及格'
        WHEN score >= 80 THEN '优秀'
        ELSE '异常' END) AS REMARK
FROM  TABLE

小练习

1.查询工资大于149号员工工资的员工的信息

SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(SELECT salary FROM employees WHERE employee_id=149)

2.返回job_id与141号员工相同，salary比143号员工多的员工姓名,job_id和工资

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

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

4.查询与141号员工的manager_id和department_id相同的其他员工

SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id=(
                  SELECT manager_id FROM employees  WHERE employee_id=141)
AND department_id=(
                  SELECT department_id FROM employees  WHERE employee_id=141)
AND employee_id<>141;   # 注意去除141号员工本身

5.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

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

6.工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary

SELECT last_name,job_id,salary FROM employees WHERE salary > ALL( SELECT salary FROM employees WHERE job_id = 'SA_MAN' )

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

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

8.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）

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

9.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）

SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
        SELECT AVG(salary)  FROM employees e2  WHERE e2.department_id = e1.`department_id`)

10.查询出公司中所有 manager 的详细信息

# 自连接

SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM employees emp JOIN employees mgr  ON emp.manager_id = mgr.employee_id;

# 子查询

SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
            SELECT DISTINCT manager_id  FROM employees)