4.子查询、分页查询、联合查询
七、子查询
/*
含义:出现在其他语句中的select语句,称为子查询或内查询
外部的查询语句,称为主查询或外查询
分类:
按照子查询出现的位置:
select后面:
仅仅支持标量子查询
from后面:
支持表子查询: 将子查询结果充当一张表,要求必须取别名
where或者having后面:(重点)
标量子查询(单行)√
列子查询(多行)√
行子查询(频率少)
exists后面(又称相关子查询)
表子查询
语法:exists(完整的查询语句) 只关心查询语句有无返回值
结果:1或0
按照结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
*/
一、WHERE或HAVING后面
1.标量子查询(单行子查询)
2.列子查询(多行子查询)
3.行子查询(多列多行)
特点:① 子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询,一般搭配着单行操作符>、<、=、<=、>=、<>
列子查询,一般搭配多行操作符使用:IN/NOT IN 等于列表中的任意一个(或不等于列表中任意一个)
ANY/SOME 和子查询返回的某一个值比较
ALL 和子查询返回的所有值比较
④子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
#1,。标量子查询
#案例1:谁的工资比Abel高
#①查询Abel的工资
SELECT salary FROM employees e WHERE e.last_name = 'Abel';
#② 查询员工信息,满足salary》①
SELECT * FROM employees WHERE salary>( SELECT salary FROM employees e WHERE e.last_name = 'Abel' );
#案例2:查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary) FROM employees GROUP BY department_id; HAVING 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' );
#3.行子查询(结果集一行多列或多行多列)
#案例:查询员工编号最小并且工资最高的员工信息
#一般查询语句
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后面
#案例:查询每个部门的平均工资的工资等级
SELECT job_grades SELECT AVG(salary),department_id FROM employees GROUP BY department_id; SELECT ag_dep.*,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(完整的查询语句) 只关心查询语句有无返回值
结果:1或0
#案例1:查询有员工的部门名
SELECT department_name FROM departments d WHERE EXISTS( SELECT * FROM employees e WHERE d.department_id = e.department_id )
#案例:查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资
SELECT employee_id,last_name,salary,e.department_id FROM ( SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id ) ag_dep INNER JOIN employees e ON e.department_id = ag_dep.department_id WHERE ag_dep.ag <e.salary;
八、分页查询(重点)
应用场景:当显示的数据,一页显示不全,需要分页提交SQL请求
/*
语法:
select 查询列表
from 表1
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit offset,size;
注:offset 要显示条目的起始索引(起始索引从0开始)当从第一条开始时可以省略
size 一次要显示的条目个数
特点:①limit语句放在查询语句的最后
②公式
要显示的页面page,每页的条件数目size
select *
from 表
limit (page-1)*size,size;
*/
#案例1:查询前五条员工信息
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
查询语句n
……
应用场景:
要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致时
特点:
①要求多条查询语句的查询列数是一致的
②要求多条查询语句的查询的每一列的类型和顺序最好是一致的
③union 关键字默认去重,如果使用union all可以包含重复项
*/
#引入案例;查询部门编号>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;