分组函数 多表连接 子查询

时间:2022-04-26 13:36:17

count计数行数
sum 求和 avg 求平均值 min最小值 max最大值
例:elect avg(salary), avg(nvl(commission_pct, 0)) from employees;
//求所有人的提成平均值 包括没有提成的 nvl:定义空值
只有count(*) 会把空值统计进去 count 不会统计空值
group by 根据……分组 空值不会被排除
只允许出现分组的列和分组的函数
例:select department_id, avg(salary) from employees group by department_id;
//查询每个部门的平均工资 以部门为分组
多列分组
用逗号分隔
例:select department_id, job_id, max(salary) from employees group by department_id, job_id order by department_id;
//先按部门分组 部门相同的按工作分组 并且按照部门从小到大排序
having 相当于group by 之后的where语句
例:select department_id,avg(salary) from employees where job_id not like '_man%' and department_id is not null group by department_id having avg(salary)>='5000' order by avg(salary) desc;
//按部门求出所有有部门的普通员工的平均工资,部门平均工资少于5000的不显示,最终结果按平均工资的降序排列。
多表连接
select e.last_name,d.department_name
from employees e,departments d
where e.department_id=d.department_id(+)
//from 给俩表定义别名 where后面(+)包含哪一个表的全部 from左边的表包含进去就写在where的右边 同理右边的表写左边
左外连接:107(106+1)
select e.last_name, d.department_name
from employees e, departments d
where e.department_id=d.department_id(+)

select e.last_name, d.department_name
from employees e left outer join departments d
on e.department_id=d.department_id;
右外连接:122(106+16)
select e.last_name, d.department_name
from employees e, departments d
where e.department_id(+)=d.department_id;

select e.last_name, d.department_name
from employees e right outer join departments d
on e.department_id=d.department_id;
完全外连接:123(106+1+16)
select e.last_name, d.department_name
from employees e full outer join departments d
on e.department_id=d.department_id;
n张表连接:
select e.last_name, d.department_name, l.city
from employees e, departments d, locations l
where e.department_id=d.department_id
and d.location_id=l.location_id;
SQL语法
select e.last_name, d.department_name, l.city
from employees e join departments d on e.department_id=d.department_id
join locations l on d.location_id=l.location_id;
左外链接
select e.last_name, d.department_name, l.city
from employees e, departments d, locations l
where e.department_id=d.department_id(+)
and d.location_id=l.location_id(+);
SQL语法
select e.last_name, d.department_name, l.city
from employees e left outer join departments d on e.department_id=d.department_id
left outer join locations l on d.location_id=l.location_id;
查询所有员工姓名,部门名称,部门所属城市(city),国家(country)和区域(region)名称,对于空值用“无”代替。(N/A)
(使用oracle和sql99的语法)
select e.last_name,d.department_name,l.city,c.country_name,r.region_name
from employees e,departments d,locations l,countries c,regions r
where e.department_id=d.department_id(+)
and d.location_id=l.location_id(+)
and l.country_id=c.country_id(+)
and c.region_id=r.region_id(+)
SQL语法
select e.last_name,d.department_name,l.city,c.country_name,r.region_name
from employees e left outer join departments d
on e.department_id=d.department_id
left outer join locations l
on d.location_id=l.location_id
left outer join countries c
on l.country_id=c.country_id
left outer join regions r
on c.region_id=r.region_id