标量子查询

案例：谁的工资比Abel高？

# ①查询Abel的工资
SELECT `salary`
FROM `employees`
WHERE `last_name`='Abel';

发现是一行一列 为标量子查询

# ②查询员工的信息，满足salary>①结果
SELECT *
FROM `employees`
WHERE `salary`>(
	SELECT `salary`
	FROM `employees`
	WHERE `last_name`='Abel'
);

整道题的结果就出来啦

案例：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id和工资

# 案例：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id和工资
# ①查询141号员工的job_id
SELECT `job_id`
FROM `employees`
WHERE `employee_id`=141;

# ②查询员工的salary
SELECT `salary`
FROM `employees`
WHERE `employee_id`=143;

# ③返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id和工资
SELECT `first_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
);

在子查询中运用分组函数

案例：返回公司工资最少的员工的last_name，job_id和salary

# 案例：返回公司工资最少的员工的last_name，job_id和salary
# ①查询公司的最低工资
SELECT MIN(`salary`)
FROM `employees`;

# ②查询last_name，job_id和salary,要求salary=①
SELECT `last_name`,`job_id`,`salary`
FROM `employees`
WHERE `salary`=(
	SELECT MIN(`salary`)
	FROM `employees`
);

在having后面用子查询

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

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

# ①查询50号部门的最低工资
SELECT MIN(`salary`)
FROM `employees`
WHERE `department_id`=50;

# ②查询每个部门的最低工资
SELECT MIN(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`;

# ③筛选②，满足MIN(`salary`)>①
SELECT MIN(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`
HAVING MIN(`salary`)>(
	SELECT MIN(`salary`)
	FROM `employees`
	WHERE `department_id`=50
);