# WHERE 或 HAVING 语句后边 --> 标量子查询
# 1 谁的工资比Abel高
SELECT
emp.last_name,
emp.salary
FROM
employees emp
WHERE
emp.salary > (
SELECT
salary
FROM
employees
WHERE
employees.last_name = 'Abel'
);
# 2 返回JOB_ID与141号员工相同,SALARY比143号员工多的员工,姓名,JOB_ID和工资
SELECT
emp.last_name,
emp.job_id,
emp.salary
FROM
employees emp
WHERE
emp.job_id = (
SELECT
job_id
FROM
employees
WHERE
employees.employee_id = '141'
)
AND emp.salary > (
SELECT
salary
FROM
employees
WHERE
employees.employee_id = '143'
);
# 3 返回公司工资最少的员工LAST_NAME,JOB_ID和SALARY
SELECT
emp.last_name,
emp.job_id,
emp.salary
FROM
employees emp
WHERE
emp.salary = (
SELECT
MIN(salary)
FROM
employees
);
# 4 查询最低工资大于50号部门最低工资的部门ID和其最低工资
SELECT
MIN(emp.salary),
emp.department_id
FROM
employees emp
GROUP BY
emp.department_id
HAVING
MIN(emp.salary) > (
SELECT
MIN(salary)
FROM
employees
WHERE
department_id = '50'
);
1.2 列子查询
# WHERE 或 HAVING 语句后边 --> 列子查询
# 1 返回LOCATION_ID是1400或1700的部门中的所有员工姓名
SELECT
emp.last_name,
emp.department_id
FROM
employees emp
WHERE
emp.department_id IN (
SELECT
dep.department_id
FROM
departments dep
WHERE
dep.location_id IN ('1400', '1700')
);
# 2 返回其它工种中比JOB_ID为‘IT_PROG’工种任一工资低的员工的员工号、姓名、JOB_ID以及SALARY
SELECT
emp.employee_id,
emp.last_name,
emp.job_id,
emp.salary
FROM
employees emp
WHERE
emp.salary < ANY (
SELECT
salary
FROM
employees
WHERE
job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';
# 3 返回其他工种比JOB_ID为‘IT_PROG’部门所有工资都低的员工工号、姓名、JOB_ID以及SALARY
SELECT
emp.employee_id,
emp.last_name,
emp.job_id,
emp.salary
FROM
employees emp
WHERE
emp.salary < ALL (
SELECT
salary
FROM
employees
WHERE
job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';
1.3 行子查询
# WHERE 或 HAVING 语句后边 --> 行子查询
# 1 查询员工编号最小并且工资最高的员工信息
# 一般写法
SELECT
*
FROM
employees emp
WHERE
emp.salary = (
SELECT
MAX(emp.salary)
FROM
employees emp
)
AND emp.employee_id = (
SELECT
MIN(emp.employee_id)
FROM
employees emp
);
# 行子查询写法
SELECT
*
FROM
employees emp
WHERE
(emp.employee_id, emp.salary) = (
SELECT
MIN(employee_id),
MAX(salary)
FROM
employees
);
2 SELECT 语句后边
只支持标量子查询(查询结果为一行一列)
# SELECT 语句后边
# 1 查询每个部门的员工个数
SELECT
dep.*, (
SELECT
COUNT(*)
FROM
employees emp
WHERE
emp.department_id = dep.department_id
)
FROM
departments dep;
# 2 查询员工号 = 102 的部门名
SELECT
(
SELECT
dep.department_name
FROM
departments dep
INNER JOIN employees emp ON dep.department_id = emp.department_id
WHERE
emp.employee_id = '102'
);
3 FROM 语句后边
将子查询结果充当一张表,要求必须起别名
# FROM 语句后边
# 1 查询每个部门的平均工资的工资等级
SELECT
A.departID,
jobg.grade_level,
A.avgSalary
FROM
(
SELECT
emp.department_id AS departID,
AVG(emp.salary) AS avgSalary
FROM
employees emp
GROUP BY
emp.department_id
) A
INNER JOIN job_grades jobg ON A.avgSalary BETWEEN jobg.lowest_sal
AND jobg.highest_sal;
4 EXISTS 语句后边
语法:
EXISTS(完整的查询语句)
# 返回结果只有可能是1(存在)/ 0(不存在)
# EXISTS 语句后边
# 1 查询有员工的部门名
SELECT
dep.department_name
FROM
departments dep
WHERE
EXISTS (
SELECT
*
FROM
employees emp
WHERE
dep.department_id = emp.department_id
);
子查询1 WHERE 或 HAVING 语句后边1.1 标量子查询1.2 列子查询1.3 行子查询2 SELECT 语句后边3 FROM 语句后边4 EXISTS 语句后边含义:出现在其他语句中的SELECT语句,称为子查询或内查询。外部的查询语句称为主查询或外查询分类:按子查询出现的位置:SELECT语句后边:仅仅支持标量子查询FROM语句后边:支持表子查询WHERE或HAVING语句后边:支持标量子查询、列子查询、行子查询EXISTS语句后边(也被称为相关子查询):表子查询按结