Mysql子查询

最新推荐文章于 2022-02-11 18:03:06 发布

蓝色螃蟹

最新推荐文章于 2022-02-11 18:03:06 发布

阅读量108

点赞数

文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/qq_52332852/article/details/121631613

版权

Mysql

子查询

1.含义：

出现在其他语句内的select语句，成为子查询或者内部查询。

一、where或having后面

1.标量子查询（结果集只有一行一列）
2.列子查询（结果集只有一列多行）
3.行子查询（结果集只有一行多列）
特点：
① 子查询放在小括号内
② 子查询一般放在条件的右侧
③ 标量子查询，一般搭配着单行操作符使用
> < >= <= <>
列子查询，一般搭配着多行操作符使用
in any/some all
④ 子查询的执行优先于主查询执行，住查询的条件用到了子查询的结果

标量子查询

案例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和工资
①查询141号员工的job_id
select job_id
from employees
where employee_id = 141；
②查询143员工的salary
select salary
from employees
where employee_id = 143;
③查询员工的姓名，job_id和工资，要求job_id=①并且salary>②
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
①查询公司的最低工资
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
);
案例4:查询最低工资大于50号部门最低工资的部门id和其最低工资
①查询50号部门的最低工资
select min(salary)
from employees
where department_id = 50;
②查询每个部门的最低工资
select min(salary)
from employees
group by department_id;
③在②的基础上筛选，满足min(salary)>①
select department_id,min(salary)
from employees
gourp by department_id
having min(salary)>(
select min(salary)
from employees
where department_id = 50
);
非法使用标量子查询
select department_id,min(salary)
from employees
gourp by department_id
having min(salary)>(
select salary
from employees
where department_id = 50
); haing后面的子查询结果集不是一行一列

列子查询（多行子查询）

案例1：返回location_id是1400或者1700的部门中的所有员工姓名
①查询location_id是1400或1700
select department_id
from departments
where location_id in (1400,1700);
（一列多行）
②查询所有员工姓名。要求部门号是①列表中某一个
select last_name
from employees
where department_id in(
select department_id
from departments
where location_id in (1400,1700)
);
案例2：返回其他工种比job_id为‘IT_prog’部门任一工资低的员工员工号，姓名，job_id 以及salary
any和some：大于或者小于任意一个，
all：大于或者小于所有的，比最小的低

①查询job_id 为‘IT_prog’部门任一工资
select distinct salary
from employees
where job_id = ‘IT_prog’;
②查询员工号，姓名，job_id 以及salary，salary<①中任意一个
select last_name,employees_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,employees_id,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 last_name,employees_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,employees_id,job_id,salary
from employees
where salary<(
select min(salary)
from employees
where job_id = ‘IT_prog’
) and job_id<>‘IT_prog’；

行子查询（一行多列或者多行多列）

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

select 星
form employees
where (employee_id,salary) = (
select min(employee_id),max(salary)
from employees
);

①查询最小的员工编号
select min(employee_id)
form employees;
②查询最高工资
select max(salary)
form employees;
③查询员工信息
select *
from employees
where employee_id = (
select min(employee_id)
form employees
) and salary =(
select max(salary)
form employees
);

二、select后面（标量）

案例1：查询每个部门的员工个数
select d.星,
（select count(星)
from employees
where e.department_id = d.dpartment_id
) 个数
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后面（表子查询）

将子查询结果充当一张表，必须起别名

案例：查询每个部门的平均工资的工资等级
①查询每个部门的平均工资
select avg(salary),department_id
from employees
group by department_id
②连接①的结果集和job_grades表，筛选条件平均工资
select ag_dep.星，g.gade_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 higest_sal ;

四、放在exists后面（相关子查询）

1.语法：
exists（完整的查询语句）
结果：
1或0
引入：
select exixts(
select employee_id from employees);
//exists查询employee_id 有没有值，有值返回1

select exixts(
select employee_id from employees
where salary=30000
);
//不存在，返回0

案例1：查询有员工的部门名
select departmen_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：查询没女朋友的男神信息
#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.boyrifend_id
);