SQL高级查询
聚合函数查询
- count(*)
- count()
- sum()
- avg()
- max()
- min()
利用聚合函数产生虚拟字段
因为要用这些聚合函数,所以要配合select使用,因此有
select *, (select avg(SALARY) from MEMBER) as AVG_SALARY from MEMBER;
聚合函数虚拟字段排序
select *, MAX_SALARY =
case
when NUM='001' then (select max(SALARY) from MEMBER where NUM=001)
when NUM='002' then (select max(SALARY) from MEMBER where NUM=002)
end
from MEMBER
order by MAX_SALARY
分组查询
注意:分组之前的条件用where,分组之后用having
SQL嵌套子查询
in运算符
嵌套子查询可以放在select后作为某一个字段,也可以放在where子句中
单行嵌套子查询
多行嵌套子查询
不能使用单行运算符,必须使用多行运算符来判断条件。主要是用where配合in来实现。
select * from WORKER where NUM_WAREHOUSE in
(
select NUM_WAREHOUSE from WAREHOUSE where AREA in
(
(select max(AREA) from WAREHOUSE),
(select min(AREA) from WAREHOUSE)
)
)
exist运算符
where exists (select * from)
返回真或假
any运算符
只要嵌套子查询中有一行为真,结果就为真
where SALARY > any(select SALARY from ····)