MySQL学习总结(四)DQL语言之子查询/标量子查询/列子查询/行子查询/表子查询/分页查询/联合查询

一、子查询

(一)概念

出现在其他语句内部的SELECT语句,称为子查询或内查询;内部嵌套其他SELECT语句的查询,称为外查询或主查询

(二)分类

  • 按子查询出现的位置
    • SELECT后面:仅支持标量子查询
    • FROM后面:支持表子查询
    • WHERE或HAVING后面:支持标量子查询、列子查询、行子查询
    • EXISTS后面(相关子查询):支持表子查询
  • 按子查询结果集的行列数不同
    • 标量子查询(结果集只有一行一列)
    • 列子查询(结果集只有一列多行)
    • 行子查询(结果集为多行多列或一行多列)
    • 表子查询(结果集一般为多行多列)

(三)WHERE或HAVING后面的子查询

支持标量子查询、列子查询、行子查询

特点

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

1.标量子查询

一般搭配着单行操作符使用(> < >= <= = <>)

案例1:谁的工资比Abel高?

#1.查询Abel的工资
SELECT salary
FROM employees
WHERE last_name='Abel';
#2.查询员工的信息,满足salary>1中的结果
SELECT *
FROM employees
WHERE salary>(
	SELECT salary
	FROM employees
	WHERE last_name='Abel'#子查询没有分号
);

案例2:返回工种号与141号员工相同,且工资比143号员工多的员工姓名,工种号和工资

#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.查询员工的姓名,工种号和工资,要求工种号=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
)

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

  • 返回多行
  • 使用多行比较操作符
操作符 含义
IN/NOT IN 等于(或不等于)列表中的任意一个
ANY/SOME 和子查询返回的某一个值比较
ALL 和子查询返回的所有值比较

案例1:返回位置编号是1400或1700的部门中的所有员工姓名

#1.查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700);
#2.查询员工姓名,要求部门号是1中结果中的任意一个
SELECT last_name
FROM employees
WHERE department_id IN(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);
#方式二:也可以用=ANY代替IN
SELECT last_name
FROM employees
WHERE department_id =ANY(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);

案例2:返回位置编号不是1400,也不是1700的部门中的所有员工姓名

SELECT last_name
FROM employees
WHERE department_id NOT IN(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);
#方式二:NOT IN也可以用<>ALL替换
SELECT last_name
FROM employees
WHERE department_id <>ALL(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);

案例3:查询其他工种中比工种号为’IT_PROG’的工种中某一工资要低的员工的员工号、姓名、工种号以及工资

#1.查询job_id为'IT_PROG'部门所有工资
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG';
#2.查询其他工种中比工种号为'IT_PROG'的工种中某一工资要低的员工的员工号、姓名、工种号以及工资,满足salary<1中的任意一个结果
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';
#方式二:比某一工资要低,即小于结果集的最大工资
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
	SELECT MAX(salary)
	FROM employees
	WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';

案例4:查询其他部门中比工种号为’IT_PROG’的部门所有工资都要低的员工的员工号、姓名、工种号以及工资

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';
#方式二:比所有工资都要低,即小于结果集的最低工资
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
	SELECT MIN(salary)
	FROM employees
	WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';

小结
IN可以用=ANY替换
NOT IN可以用<>ALL替换
<ANY可以用<MAX替换
>ANY可以用>MIN替换
<ALL可以用<MIN替换
>ALL可以用>MAX替换

3.行子查询

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

#1.查询最小的员工编号
SELECT MIN(employee_id)
FROM employees;
#2.查询最高工资
SELECT MAX(salary)
FROM employees;
#3.查询员工信息,满足员工编号=1中结果且工资=2中结果
SELECT *
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
) AND salary=(
	SELECT MAX(salary)
	FROM employees
);
#方式二:
SELECT *
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

(四)SELECT后面的子查询

仅支持标量子查询

案例:查询每个部门的部门信息以及员工个数

SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id=d.department_id
) 个数
FROM departments d;

(五)FROM后面的子查询

支持表子查询:将子查询结果充当一张表,要求必须起别名

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

#1.查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
#2.连接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;

MySQL学习总结(四)DQL语言之子查询/标量子查询/列子查询/行子查询/表子查询/分页查询/联合查询

(六)EXISTS后面的子查询

支持表子查询

EXISTS(即是否存在)
语法:EXISTS(完整的查询语句),结果返回1或0

案例:查询有员工的部门名

SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.department_id=e.department_id
);
#方式二:
SELECT department_name
FROM departments d
WHERE d.department_id IN(
	SELECT department_id
	FROM employees 
);

练习题1:查询各部门中工资高于本部门平均工资的员工的员工号、姓名和工资

#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;

练习题2:查询和姓名中包含字母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%'
);

练习题3:查询姓为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'
);

练习题4:查询工资最高的员工的姓名,要求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
);

练习题5:查询平均工资高于公司平均工资的部门有哪些

#1.查询平均工资
SELECT AVG(salary)
FROM employees;
#2.查询每个部门的平均工资,满足>1中结果
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
	SELECT AVG(salary)
	FROM employees
);

练习题6:查询出公司中所有领导的详细信息

#1.查询所有领导的编号manager_id
SELECT DISTINCT manager_id
FROM employees
#2.查询领导的详细信息,满足employee_id=1中结果
SELECT *
FROM employees
WHERE employee_id=ANY(
	SELECT DISTINCT manager_id
	FROM employees
);

练习题7:查询每个专业的男生人数和女生人数分别是多少
已知表:
MySQL学习总结(四)DQL语言之子查询/标量子查询/列子查询/行子查询/表子查询/分页查询/联合查询

SELECT majorid,
(SELECT COUNT(*) FROM student WHERE sex='男' AND majorid=s.majorid) 男,
(SELECT COUNT(*) FROM student WHERE sex='女' AND majorid=s.majorid) 女
FROM student s
GROUP BY majorid;

二、分页查询

应用场景:当要显示的数据一页显示不全,需要分页提交sql请求
格式

SELECT 查询列表
FROM 表1
【连接类型 JOIN 表2
ON 连接条件
WHERE 筛选条件
GROUP BY 分组字段
HAVING 分组后的筛选
ORDER BY 排序的字段】
LIMIT offset,size;

offset表示要显示条目的起始索引(这里的起始索引从0开始
size表示要显示的条目个数

特点

  1. LIMIT语句要放在查询语句的最后
  2. 公式:假设已知要显示的页数page和每页的条目数size
    那么就是LIMIT (page-1)*size,size;

案例1:查询前五条员工信息

SELECT * FROM employees LIMIT 0,5;
#当要显示的起始索引是0时可以省略
SELECT * FROM employees LIMIT 5;

案例2:查询第11条—第25条

SELECT * FROM employees LIMIT 10,15;

案例3:将有奖金的且工资较高的前10名员工的员工信息显示出来

SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;

练习题1:说出查询语句中的执行先后顺序

SELECT 查询列表			七
FROM 表1				一
连接类型 JOIN 表2		二
ON 连接条件				三
WHERE 筛选条件			四
GROUP BY 分组字段		五
HAVING 分组后的筛选		六
ORDER BY 排序的字段		八
LIMIT 偏移,条目数;		九

练习题2:查询平均工资最低的部门信息
方式一:子查询

#1.查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
#2.查询1中结果的最低平均工资
SELECT MIN(ag)
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
)ag_dep;
#3.查询哪个部门的平均工资=2中结果
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
	SELECT MIN(ag)
	FROM (
		SELECT AVG(salary) ag,department_id
		FROM employees
		GROUP BY department_id
	)ag_dep
);
#4.查询部门信息
SELECT d.*
FROM departments d
WHERE d.department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING AVG(salary)=(
		SELECT MIN(ag)
		FROM (
			SELECT AVG(salary) ag,department_id
			FROM employees
			GROUP BY department_id
		)ag_dep
	)
);

方式二:分页查询+子查询

#1.查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
#2.求出最低平均工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;
#3.查询部门信息
SELECT *
FROM departments 
WHERE d.department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
);

练习题3:查询平均工资最低的部门信息和该部门的平均工资

#1.查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
#2.求出最低平均工资的部门编号
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;
#3.查询部门信息和平均工资
SELECT d.*,ag
FROM departments d
JOIN(
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1;
) ag_dep
ON d.department_id=ag_dep.department_id;

练习题4:查询平均工资最高的工种信息

#1.查询平均工资最高的工种编号
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
#2.查询工种信息
SELECT *
FROM jobs
WHERE job_id=(
	SELECT job_id
	FROM employees
	GROUP BY job_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
)

练习题5:所有部门中最高工资最低的那个部门的最低工资是多少

#1.查询各部门的最高工资最低的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1;
#2.查询1中结果的那个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
WHERE department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY MAX(salary)
	LIMIT 1
);

练习题6:查询平均工资最高的部门的领导的详细信息

#1.查询平均工资最高的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1;
#2.将employees和departments连接查询,筛选条件是1中结果
SELECT *
FROM employees e
INNER JOIN departments d
ON d.manager_id=e.employee_id
WHERE d.department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
);

三、联合查询

UNION 联合/合并
将多条查询语句的结果合并成一个结果

格式

查询语句1
UNION
查询语句2
UNION
...
查询语句n

案例1:查询部门编号>90或邮箱包含a的员工信息
方式一:

SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

方式二:

SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;

应用场景要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致时
特点

  1. 要求多条查询语句的查询列数是一致的!
  2. 要求多条查询语句的每一列类型和顺序最好一致
  3. UNION关键字默认去重,如果使用UNION ALL可以包含重复项

案例2:查询中国用户中男性的信息以及外国用户中
已知有如下表:
MySQL学习总结(四)DQL语言之子查询/标量子查询/列子查询/行子查询/表子查询/分页查询/联合查询
MySQL学习总结(四)DQL语言之子查询/标量子查询/列子查询/行子查询/表子查询/分页查询/联合查询

SELECT c_id,c_name,c_sex FROM c_user WHERE c_sex='男'
UNION 
SELECT f_id,f_name,f_sex FROM f_user WHERE f_sex='male';

MySQL学习总结(四)DQL语言之子查询/标量子查询/列子查询/行子查询/表子查询/分页查询/联合查询

上一篇:5.6 进阶6:多表连接查询


下一篇:mysql连接查询