含义:出现在其他语句中的select 语句,称为子查询或者内查询。
外部的查询语句称为主查询或者外查询
分类:
按照子查询位置
select后面:标量子查询
from后面:表子查询
where或者having后面:标量查询(重点),列子查询(重点),行子查询
exists后面:表子查询
按结果集行列数不同:
标量子查询(一行一列)
行子查询(一行多列)
列子查询(一列多行)
表子查询(一般为多行多列)
where或者having后子查询(重点)
特点:
①子查询都在()内
②子查询一般放在条件的右侧;
③标量子查询一般配合单行操作符使用(> < >= <= = !=),多行子查询配合多行操作符使用(in any/some all)
④子查询优先执行
标量子查询
1.查询比名为Abel工资高的员工信息
select *
from employees
where salary >(
select salary
from employees
where last_name = 'Abel'
);
2.job_id 与141号员工相同,工资比143号员工高的姓名,job_id ,salary
select last_name,job_id ,salary
from employees e
where job_id =(
select job_id
from employees
where employee_id =141
) and salary >(
select salary
from employees
where employee_id =143
)
3.查询最少工资的员工信息
select last_name,salary
from employees
where salary = (
select min(salary)
from employees
)
4.查询最低工资大于 50号部门的最低工资 的部门id和其最低工资
select min(salary),department_id
from employees
group by department_id
having min(salary) > (
select min(salary)
from employees
where department_id=50
)
5.非法标量子查询:子查询结果不是单行单列
列子查询(多行子查询)
1.in(最常用)
select last_name
from employees
where department_id
in(
select distinct department_id
from departments
where location_id in(1400,1700)
)
2.any/some(可替代)
select last_name
from employees
where department_id
= any(
select distinct department_id
from departments
where location_id in(1400,1700)
)
3.all(可替代)
select last_name,employee_id,job_id,salary
from employees
where salary < all(
select distinct salary
from employees
where job_id = 'IT'
)
select last_name,employee_id,job_id,salary
from employees
where salary < (
select max(salary)
from employees
where job_id = 'IT'
)
上面两句结果相同
not in和<>all可替换
行子查询(一行多列,多行多列,有局限性,用的很少)
判断条件的符号一样时可以使用
select last_name salary
from employees
where (employee_id,salary)=(
select min(employee_id),max(salary)
from employees
);
select last_name salary
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
) 个数
from department
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
from (
select avg(salary) ag,department_id
from employees e
group by department_id
) ag_dep inner join job_grades j
on ag between j.lowest and j.highest;
exists后面(相关子查询,可代替,用得少)
判断有没有,结果只有1或者0,1是有
语法:exist(子查询语句)
exists实现的in一定可以实现。
exists先主查询,然后根据子查询筛选。
select department_name
from departments d
where exists(
select * from employees e
where d.department = e.department_id
);
select department_name
from departments d
where d.department_id
in (
select department_id
from employees
);
分页查询
select 【查询列表】
from 表
【jointype join
on 连接条件
where 筛选条件
group by 分组条件
having 查询后的筛选条件
order by 排序的字段】
limit offset,size;
offset显示条目的起始索引;(索引从0开始,如果是0可以省略)
size是要显示的条目个数;
特点:
①放在查询语句最后,执行也是最后;
②公式:页数page,条目size,则(page-1)*size,size;
上述执行顺序如下:
from
join(笛卡尔乘积大表)
on
where
group by
having
select
order by
limit
经典例题:
1.查询每个专业的男生个数和女生个数各多少
方式一:
select count(*),sex,major_id
from student
group by sex,major_id;
方式二:
select major_id,
(select count(*) from student where sex ='男' and major_id = s.major_id) 男,
(select count(*) from student where sex ='女' and major_id = s.major_id) 女
from student s
group by major_id
2.查询没有成绩的学生个数
select count(*)
from student s
left join result r
on s.sudentno=r.studentno
where r.id is null;
学习整理于MySQL 基础+高级篇.