文章目录
前言
上次说到了MySQL的查询进阶五和六,这次继续讨论查询的相关的知识
链接: https://pan.baidu.com/s/1a_wstxPVhxeliZkt6-BTiw
提取码: gaj8
进阶七:子查询
含义:
出现在其他语句中的select语句,称为子查询或内查询
外部的查询语句称为主查询或外查询
分类:
按子查询出现的位置:
select后面:
仅仅支持标量子查询
from后面:
支持表子查询
where或having后面*
标量子查询(单行)√
列子查询(多行)√
行子查询
exists后面(相关子查询)
表子查询
按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一行多列)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
一、where或having后面
1.标量子查询(单行子查询)
2.列子查询(多行子查询)
3.行子查询(多行多列)
特点:
1.子查询放在小括号内
2.子查询一般放在条件的右侧
3.标量子查询,一般搭配着单行操作符(>,<,=等等)使用
列子查询,一般搭配这多行操作符(in,any/some,all)使用
4.子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
*/
1.标量子查询
#案例1、谁的工资比Abel高?
#①查询Abel的工资
SELECT salary
FROM `employees`
WHERE `last_name`='Abel';
#②查询员工的信息,满足salary>①结果
SELECT *
FROM `employees`
WHERE salary>(
SELECT salary
FROM `employees`
WHERE `last_name`='Abel'
);
#案例2:返回job_id与141号员工相同,salary比143号员工多的员工 姓名,job_id和工资
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:返回公司工资最少的员工的姓名,`job_id`和`salary`
SELECT `last_name`,`job_id`,`salary`
FROM `employees`
WHERE salary=(
SELECT MIN(salary)
FROM `employees`
);
#案例4、查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT`department_id`,MIN(salary)
FROM `employees`
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM `employees`
WHERE `department_id`=50
);
2.列子查询(多行子查询)
#案例1、返回`location_id`是1400或1700的部门中的所有的员工的姓名
SELECT `last_name`
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`
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';
#案例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';
或者
SELECT `employee_id`,`last_name`,`job_id`,`salary`
FROM `employees`
WHERE salary < (
SELECT MIN(`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`
);
二、放在SELECT的后面(仅仅支持标量子查询(也就是一行一列))
#案例、查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM `employees` e
WHERE e.`department_id`=d.`department_id`
)个数
FROM `departments` d;
#案例、查询员工号=102的部门名
SELECT `department_name`
FROM `departments` d,`employees` e
WHERE d.`department_id`=e.`department_id`
AND e.`employee_id`=102;
三、放在FROM的后面(意思就是把查询的结果当作表来进行使用(要求必须起别名))
#案例、查询每个部门的平均工资的等级
SELECT biao.*,j.`grade_level`
FROM (
SELECT AVG(salary) ag,`department_id`
FROM `employees`
GROUP BY `department_id`
)biao
INNER JOIN `job_grades` j
ON biao.ag BETWEEN j.`lowest_sal` AND `highest_sal`;
四、EXISTS后面(相关子查询)
/*
语法:
exists(完整的查询语句)
结果:
1或0
*/
#案例、查询有员工的部门名
SELECT `department_name`
FROM `departments` d
WHERE EXISTS(
SELECT *
FROM `employees` e
WHERE e.`department_id`=d.`department_id`
);
进阶八:分页查询
相关概念
/*
应用场景:
当要显示的数据,一页显示不全,需要分页提交SQL请求
语法:
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset】,size;
offset 要显示条目的起始索引(起始索引从0开始)
size要显示的条目个数
特点:
1.limit语句放在查询语句的最后
2.公式:
要显示的页数 page,每页的条目数size
select 查询列表
from 表
limit (page-1)*size,size
*/
案例
#案例,查询前五条的员工的信息
SELECT *
FROM `employees`
LIMIT 0,5
或者
SELECT *
FROM `employees`
LIMIT 5
#要查询11到25条
SELECT *FROM `employees` LIMIT 10,15;
#有奖金的员工的信息,并且显示出较高的工资的前面10条的信息
SELECT *
FROM `employees`
WHERE `commission_pct` IS NOT NULL
ORDER BY salary DESC
LIMIT 10;
进阶九:联合查询
union 联合 合并:将多条查询语句的结果合并成一个结果
语法: 查询语句1 union 查询语句2 union …
应用场景: 要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致 需要注意的点:
1.要求多条查询语句的列数是一致的
2.要求多条查询语句的查询的每一列的类型和顺序最好一致
3.union关键字默认去重,如果使用union all可以包含重复项