子查询

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

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

2查询每个部门最低工资

SELECT
    MIN(salary) Minsalary,
    department_id
FROM
    employees e
GROUP BY
    department_id

3在2的基础上做筛选，满足min(salary)>1

SELECT
    MIN(salary) Minsalary,
    department_id
FROM
    employees e
GROUP BY
    department_id
HAVING
    MIN(salary) > (
        SELECT
            MIN(salary)
        FROM
            employees
        WHERE
            department_id = 50
    )

多行子查询
in/not in 等于列表中任意一个
any| some 和子查询返回的某一个值比较
all 和子查询返回的所有制比较
2查询location_id是1400或者1700的部门编号

多表左外连接

SELECT
    last_name
FROM
    employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN locations l ON d.location_id = l.location_id
WHERE
    l.location_id IN (1400, 1700)

使用子查询

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

当然也可使用where查询
3返回其他工种比job_id为'IT-PROG'部任一工资低的员工号，姓名，job_id

SELECT
    employee_id,
    last_name,
    job_id,
    salary
FROM
    employees e
WHERE
    salary < ANY (
        SELECT
            salary
        FROM
            employees
        WHERE
            job_id = 'IT_PROG'
    )
AND job_id <> 'IT_PROG'

思考
not in 与 all
in any

行子查询

查询员工编号最小并且工资最高的员工信息
传统做法

*

SELECT
    *
FROM
    employees
WHERE
    (employee_id, salary) = (
        SELECT
            min(employee_id),
            MAX(salary)
        FROM
            employees
    )

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

SELECT
    ag_dep.*, jg.grade_level
FROM
    (
        SELECT
            avg(salary) avgSal,
            department_id
        FROM
            employees e
        GROUP BY
            department_id
    ) ag_dep
INNER JOIN job_grades jg ON ag_dep.avgSal BETWEEN jg.lowest_sal
AND highest_sal

练习
1查询和Zlotkey相同部门的员工姓名和工资

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,
    e.department_id
FROM
    employees e
INNER JOIN (
    SELECT
        avg(salary) avgSal,
        department_id
    FROM
        employees
    GROUP BY
        department_id
) ag_dep ON e.department_id = ag_dep.department_id
WHERE
    salary > ag_dep.avgSal

3查询平均工资最高的job信息

SELECT
    *
FROM
    jobs
WHERE
    job_id = (
        SELECT
            job_id
        FROM
            employees e
        GROUP BY
            job_id
        ORDER BY
            salary DESC
        LIMIT 1
    )

4查询平均工资高于公司平均工资的部门有哪些

SELECT
    avg(salary) sal,
    department_id
FROM
    employees
GROUP BY
    department_id
HAVING
    sal > (
        SELECT
            avg(salary)
        FROM
            employees
    )

5查询公司中所有manager的详细信息

SELECT
    *
FROM
    employees
WHERE
    employee_id IN (
        SELECT
            manager_id
        FROM
            employees
    )

6查询各部门中最高工资中最低的那个部门的最低工资

SELECT
    min(salary) salary
FROM
    employees
WHERE
    department_id = (
        SELECT
            department_id
        FROM
            employees e
        GROUP BY
            department_id
        ORDER BY
            max(salary) ASC
        LIMIT 1
    ) #查询10号部门最低工资

7查询平均工资最高的部门的manager的详细信息

SELECT
    last_name,
    salary,
    email
FROM
    employees ee
INNER JOIN departments d ON d.manager_id = ee.employee_id
WHERE
    d.department_id = (
        SELECT
            department_id
        FROM
            employees e
        GROUP BY
            department_id
        ORDER BY
            AVG(salary) DESC
        LIMIT 1
    )

union 默认会去重
union All不去重
语法: A与B字段必须对应

SELECT
    学号,
    姓名,
    成绩
FROM
    A
UNION
    SELECT
        学号,
        姓名,
        成绩
    FROM
        B