子查询
出现再其它语句内部的select语句,称为子查询或内查询;
内部嵌套其它select语句的查询,称为外查询或主查询;
分类:
- 出现位置:
select后面:支持标量子查询
from后面:支持表子查询
where或having后面:支持标量,列子查询,但行子查询用的少
exists后面(相关子查询):支持表子查询 - 结果集行列数:
标量子查询(一行一列)
列子查询(一列多行)
行子查询(一行多列)
表子查询(多行多列)
where或having后
- 子查询均放在小括号内
- 标量子查询一般搭配单行操作符使用:< > <= >= <>
- 列子查询一般搭配多行操作符使用:in/not in,any/some,all
- 子查询的执行优先于主查询的执行
- 标量子查询
例:谁的工资比Abel高
SELECT last_name
FROM employees
WHERE salary > (SELECT salary
FROM employees
WHERE last_name='Abel');
- 列子查询
例:查询其它部门中比job_id为IT_PROG部门任一员工工资要低的员工的员工号,姓名,job_id,salary。
SELECT employee_id, last_name, job_id , salary
FROM employees
WHERE salary<ANY(
SELECT salary
FROM employees
WHERE job_id='IT_PROG'
)
AND job_id != 'IT_PROG';
- 行子查询
例:查询员工编号最小且工资最高的员工信息
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后
- 将子查询的结果充当一张表,要求必须起别名
例:查询各部门平均工资的工资级别
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可以判断是否存在
语法:exists(完整的查询语句)
结果:0或1
例:查询员工名和部门名
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 e
);
分页查询
应用场景:当需要查询的数据一页显示不全,需要分页提交sql请求
语法:
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组条件
having 分组后的筛选
order by 排序字段】
limit offset, size;
- offset指要显示的条目的起始索引(起始从0开始,若offset为0,可省略)
- size指要显示的条目个数
- limit语句要放在查询语句的最后
- 公式:要显示的页数是page,每页条目数为size
select 查询列表 from 表 limit (page-1)*size,size;
例:要查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;
联合查询
将多条查询语句的结果合并成一个结果
语法:
查询语句1
union
查询语句2
union
...;
- 一般应用于所需查询结果来自多个表,且多个表间没有连接关系,但查询信息一致时
- 要求多条查询语句的查询列数是一致的
- 要求多条查询语句所查询的每一列的类型和顺序一致
- union一般会自动去重,如果想留下重复的部分,则使用union all