第十三天 分组函数和多表连接

分组函数：
常用分组函数avg count max min stddey sum
ps
分组函数的运算会去掉空值
用了group by后 select后边只可以跟group by后的列，和分组函数。

多列分组，group by后用逗号分隔，顺序从左到右依次进行
having 是对分组进行筛选

理解执行顺序
from
where
group by
sum（对数据进行计算）
having
order by
select

 

练习：

按部门求出所有有部门的普通员工的平均工资，部门平均工资少于5000的不显示，最终结果按平均工资的降序排列。

select department_id, avg(salary) avg_sal

from employees

where job_id not like '%\_MGR' escape '\' and department_id is not null

group by department_id

having avg(salary)>=5000

order by avg_sal desc;

 

多表连接

内连接：106(106, 11)

select e.last_name, d.department_name

from employees e, departments d

where e.department_id=d.department_id;    

 

SQL99语法

select e.last_name, d.department_name

from employees e join departments d 

on e.department_id=d.department_id;

 

左外连接：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;

 

练习：

查询所有员工姓名，部门名称，部门所属城市(city)，国家(country)和区域(region)名称，对于空值用“无”代替。(N/A)

（使用oracle和sql99的语法）

select e.last_name, nvl(d.department_name,0), 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(+);