MySql教程

MySQL-sql99-子查询

本文主要是介绍MySQL-sql99-子查询,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

image

image

image

image

标量子查询

案例:谁的工资比Abel高?

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

image

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

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

image

整道题的结果就出来啦

案例:返回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
);

image

在子查询中运用分组函数

案例:返回公司工资最少的员工的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`
);

image

在having后面用子查询

image

案例:查询最低工资大于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
);

image

image

这篇关于MySQL-sql99-子查询的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!