mySQL基础之子查询

最新推荐文章于 2021-11-05 21:28:05 发布

尕焱aYan

最新推荐文章于 2021-11-05 21:28:05 发布

阅读量372

点赞数 1

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_45342806/article/details/109688975

版权

进阶7：子查询

含义：
		出现在其他语句中的select语句,称为子查询或内查询
		外部的查询语句,称为主查询或外查询
分类：
	按子查询出现的位置：
							SELECT后面：
											仅仅支持标量子查询
							FROM后面：
											支持表子查询
							WHERE或having后面： 🔺
											标量子查询(单行)
											列子查询(多行)
											
											行子查询
											
							EXISTS后面(相关子查询)：
											表子查询
	按结果集的行列数不同：
							标量子查询(结果集只有一行一列)
							列子查询(结果集只有一列多行)
							行子查询(结果集有一行多列)
							表子查询(结果集一般为多行多列)

一、子查询放在where或having后面

	1、标量子查询(单行子查询)
	2、列子查询(多行子查询)
	
	3、行子查询(单行多列)
	
	
	特点：
		1.子查询放在小括号内
		2.子查询一般放在条件的右侧
		3.标量子查询，一般搭配着单行操作符使用
		>  <  >=  <=  =  <>
		
		4.列子查询，一般搭配着多行操作符使用
		in  any/some  all
		
		5.子查询的执行优先于主查询执行,主查询的条件用到了子查询
			得到结果

1、标量子查询(单行子查询)

案例1：谁的工资比 Abel 高？

1)查询 Abel 的工资

SELECT salary
FROM employees
WHERE last_name = 'Abel'

查询员工的信息，满足salary > 1) 的结果

SELECT *
FROM employees
WHERE (
	
		SELECT salary
		FROM employees
		WHERE last_name = 'Abel'

);

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

1)查询141号员工的job_id

SELECT job_id
FROM employees
WHERE employee_id = 141;

2)查询143号员工的salary

SELECT salary
FROM employees
WHERE employee_id = 143;

3)查询员工的姓名,job_id和工资,要求job_id = 1) 并且salary > 2)

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

1)查询公司的最低工资

SELECT MIN(salary)
FROM employees;

2)查询last_name,job_id和salary,要求salary = 1)

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

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

1)查询50号部门的最低工资

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

2)查询每个部门的最低工资

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id；

3)在2)基础上进行筛选,满足min(salary) > 1)

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

非法使用标量子查询

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
									SELECT salary
									FROM employees
									WHERE department_id = 250
);

2、列子查询(多行子查询)

案例1：返回location_id是1400或1700的部门中的所有员工姓名

1) 查询location_id是1400或1700的部门编号

SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700);

2)查询员工姓名,要求部门编号是1)列表中的的某一个

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

);

案例2：返回其他工种中比job_id为IT_PROG工种任一工资低的员工的员工号、姓名、job_id 以及salary

1)查询job_id为 IT_PROG 部门任一工资

SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG';

2)查询员工号、姓名、job_id 以及salary,salary < any(1)(1中任意一个)

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

/*
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < (
										SELECT max(salary)
										FROM employees
										WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';


*/

案例3：返回其他部门中比job_id为IT_PROG部门所有工资都低的员工的员工号、姓名、job_id 以及salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ALL(
										SELECT DISTINCT salary
										FROM employees
										WHERE job_id = 'IT_PROG'
										
) AND job_id <> 'IT_PROG';

3.行子查询(结果集一行多列或多行多列)

案例：查询员工编号最小并且工资最高的员工信息

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

/*

1)查询最小的员工编号

SELECT MIN(employee_id)
FROM employees;

2)查询最高工资

SELECT MAX(salary)
FROM employees;

3)查询员工信息

SELECT *
FROM employees
WHERE employee_id = (
			SELECT MIN(employee_id)
			FROM employees

) AND salary = (
			SELECT MAX(salary)
			FROM employees

);

二、子查询放在SELECT后面

/*
仅仅支持标量子查询
*/

案例1：查询每个部门的员工个数

SELECT d.*,(
							SELECT COUNT(*)
							FROM employees e
							WHERE e.department_id = d.department_id

) count1
FROM departments d;

案例2：查询员工号 = 102的部门名

SELECT (
			SELECT department_name
			FROM departments d
			INNER JOIN employees e
			ON d.department_id = e.department_id
			WHERE e.employee_id = 102
) 部门名;

三、子查询放在from后面

/*
将子查询结果充当一张表,要求必须起别名
*/

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

1.先查询每个部门的平均工资

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

2.再查询工资等级

SELECT * FROM job_grades;

3.连接1的结果集和job_grades表,筛选条件平均工资 between lowest_sal and highest_sal

SELECT ag_dep.*,g.grade_level
FROM (
			SELECT AVG(salary) ag,department_id
			FROM employees
			GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

四、子查询放在exists后面(相关子查询)

	EXISTS()  是否存在
	判断后面()内语句值是否为空,空为0,不空为1
	相当于Boolean类型
	
	语法：
				EXISTS(完整的查询语句)
				结果：
							0或1

SELECT EXISTS(
				SELECT employee_id 
				FROM employees
);

案例1：查询有员工的部门

EXISTS

SELECT department_name
FROM departments d
WHERE EXISTS(
				SELECT *
				FROM employees e
				WHERE d.department_id = e.department_id
);

in

SELECT department_name
FROM departments d
WHERE d.department_id IN(
					SELECT department_id
					FROM employees
);

案例2：查询没有女朋友的男神信息

USE girls;

in

SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
					SELECT boyfriend_id
					FROM beauty
);

exists

SELECT bo.*
FROM boys bo
WHERE NOT EXISTS (
						SELECT boyfriend_id
						FROM beauty b
						WHERE bo.id = b.boyfriend_id
						
);

--------------------------------练习题---------------------------------

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

1)查询Zlotkey的部门

SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey';

2)查询部门号 = 1 的姓名和工资

SELECT last_name,salary
FROM employees
WHERE department_id = (
							SELECT department_id
							FROM employees
							WHERE last_name = 'Zlotkey'
);

2.查询工资比公司平均工资高的员工的员工号,姓名和工资

1)查询平均工资

SELECT AVG(salary)
FROM employees;

#2)查询工资>1的员工号,姓名和工资
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
					SELECT AVG(salary)
					FROM employees
);

3.查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资

1)查询各部门工资

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

2)连接1结果集和employees表,进行筛选

SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN (
				SELECT AVG(salary) ag,department_id
				FROM employees
				GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary > ag_dep.ag;

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

1)查询姓名中包含字母u的员工的部门

SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%';

2)查询部门号=1中的任意一个的员工号和姓名

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

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

1)查询location_id为1700的部门

SELECT DISTINCT department_id
FROM departments
WHERE location_id = 1700;

2)查询部门号 = 1中的任意一个的员工号

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

6.查询管理者是K_ing的员工姓名和工资

1)查询姓名为K_ing的员工编号

SELECT employee_id
FROM employees
WHERE last_name = 'K_ing';

2)查询哪个员工的manager_id = 1

SELECT last_name,salary
FROM employees
WHERE manager_id IN (
					SELECT employee_id
					FROM employees
					WHERE last_name = 'K_ing'
);

7.查询工资最高的员工的姓名,要求first_name和last_name 显示为一列,列名为姓.名

1)查询最高工资

SELECT MAX(salary)
FROM employees;

#2)查询工资 = 1的姓.名
SELECT CONCAT(first_name,last_name) "姓.名"
FROM employees
WHERE salary = (
				SELECT MAX(salary)
				FROM employees
);

一、含义
嵌套在其他语句内部的select语句称为子查询或内查询,外面的语句可以是insert、update、delete、select等,一般select作为外面语句较多,外面如果为select语句,则此语句称为外查询或主查询

二、分类

1. 按出现位置
	 select后面：
			仅仅支持标量子查询
	 from后面：
			表子查询
	 where或having后面：
			标量子查询
			列子查询
			行子查询
	 exists后面：
			标量子查询
			列子查询
			行子查询
			表子查询

2. 按结果集的行列
	1. 标量子查询(单行子查询)：结果集为一行一列
	2. 列子查询(多行子查询)：结果集为多行一列
	3. 行子查询：结果集为多行多列
	4. 表子查询：结果集为多行多列

三、示例

where或having后面
1. 标量子查询
	案例:查询最低工资的员工姓名和工资
	1. 最低工资
		select MIN(salary) FROM employees;
	2. 查询员工的姓名和工资,要求工资 = '1'
		select last_name,salary
		from employees
		where salary = (
			select MIN(salary) FROM employees
		);
2. 列子查询
	案例:查询所有是领导的员工姓名
	1. 查询所有员工的manager_id
		select manager_id
		from employees;
	2. 查询姓名,employee_id属于 '1' 列表的一个
		select last_name
		from employees
		where employee_id in (
				select manager_id
				from employees
		);

尕焱aYan

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
2
评论
mySQL基础之子查询

进阶7：子查询含义：出现在其他语句中的select语句,称为子查询或内查询外部的查询语句,称为主查询或外查询分类：按子查询出现的位置： SELECT后面：仅仅支持标量子查询 FROM后面：支持表子查询 WHERE或having后面： ???? 标量子查询(单行) 列子查询(多行) 行子查询
复制链接

扫一扫