oracle课堂笔记--第十二天
分组函数 ，多行函数
对非空的数据总结
只有在列里才先把空值排除
分组不需要
SQL> select count(*), sum(salary), avg(salary), min(salary), max(salary) from employees;
SQL> create table t1(x int);
SQL> insert into t1 values (null);
SQL> insert into t1 values (1);
SQL> commit;
SQL> select count(*) from t1;
SQL> select count(x) from t1;
SQL> select max(x) from t1;
SQL> select min(x) from t1;
SQL> select sum(x) from t1;
SQL> select avg(x) from t1;
SQL> select avg(salary), avg(nvl(commission_pct, 0)) from employees;
SQL> select count(distinct department_id) from employees; 去除重复值
Group by分组：
SQL> select department_id, avg(salary) from employees group by department_id;
多列分组：
SQL> select department_id, job_id, max(salary) from employees group by department_id, job_id;
SQL> select department_id, job_id, max(salary), last_name from employees group by department_id, job_id; 错误语法
练习：
公司中不同职位的数量
select (distinct job_id)from employees;
计算每个部门的人数
Select Department_id,count(employees_id)
From employees
Where department_id is not null
Group by department_id
按年份分组，求员工的工资总和
Select extract（year from hire_date）,avg(salary)
From employees
Group by extract(year from hire_date)
Having语句：
SQL> select department_id, avg(salary) from employees where avg(salary)>=5000 group by department_id; 错误语句
SQL> select department_id, avg(salary) from employees group by department_id having avg(salary)>=5000;
练习：
按部门求出所有有部门的普通员工的平均工资，部门平均工资少于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;
多表连接no
emp： dept:
empno ename deptno deptno dname
100 abc 10 10 sales
101 def 10 20 market
102 xyz 20 30 it
103 opq null
for emp in 100 .. 103
for dept in 10 .. 30
emp.deptno=dept.deptno
100 abc 10 10 sales
101 def 10 10 sales
102 xyz 20 20 market
获取如下信息，准备工作：
employees:
员工总数：107
SQL> select count(*) from employees;
有部门的员工数：106
SQL> select count(*) from employees where department_id is not null;
SQL> select count(department_id) from employees;
没有部门的员工数：1
SQL> select count(*) from employees where department_id is null;
departments：
部门总数：27
SQL> select count(*) from departments;
有员工的部门数：11
SQL> select count(distinct department_id) from employees;
没有员工的部门数：16
SQL> select count(*) from departments where department_id not in (select department_id from employees where department_id is not null);
for dept in 1..27
for emp in 1..107
dept.deptid不在emp表中出现
select count(*)
from employees e, departments d
where e.department_id(+)=d.department_id
and e.employee_id is null;
select count(*)
from departments d
where not exists
(select 1 from employees where department_id=d.department_id);
select (select count(*) from departments)-(select count(distinct department_id) from employees) from dual;
内连接：106(106, 11)
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 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 departments d, employees e
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;
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.locationy_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(+);
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;
