第十一天
单行函数
SQL> select upper(first_name), lower(last_name), length(last_name) from employees;
SQL> select (sysdate-hire_date)/7 from employees;
SQL> select trunc((sysdate-hire_date)/30, 0) from employees;
SQL> select trunc(months_between(sysdate,hire_date), 0) from employees;
SQL> select sysdate+3650 from dual;
SQL> select add_months(sysdate, 120) from dual;
SQL> select next_day('2015-09-01', 'friday') from dual;
SQL> select next_day('2015-10-01', 6) from dual;
SQL> select last_day(sysdate) from dual;
SQL> select round(to_date('2015-10-10','yyyy-mm-dd'), 'MONTH') from dual;
SQL> select round(to_date('2015-10-16','yyyy-mm-dd'), 'MONTH') from dual;
SQL> select round(to_date('2015-10-10','yyyy-mm-dd'), 'YEAR') from dual;
SQL> select round(sysdate, 'DAY') from dual;
练习：
找出各月最后三天内受雇的所有雇员
extract(month from hire_date+4) != extract(month from hire_date)
找出早于25年之前受雇的雇员
months_between(sysdate, hire_date)/300>=25
显示正好为6个字符的雇员姓名
length(last_name)=6
显示所有雇员的姓名的前三个字符
substr(last_name, 1, 3)
显示所有雇员的姓名，用a替换所有'A'
replace(last_name, 'A', 'a')
类型转换和其他函数
SQL> select to_char(salary, '$999,999.00') from employees;
SQL> select last_name, to_char(hire_date, 'dd-Mon-RR') from employees;
SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
SQL> select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss AM') from dual;
SQL> select last_name from employees where hire_date=to_date('2006-05-23', 'yyyy-mm-dd');
SQL> select to_number('$123,456.78', '$999,999.00') from dual;
练习：
查询2006年入职员工：
select last_name
from employees
where hire_date between to_date('2006-01-01', 'yyyy-mm-dd')
and to_date('2006-12-31', 'yyyy-mm-dd');
select last_name
from employees
where to_char(hire_date, 'yyyy')='2006';
select last_name
from employees
where extract(year from hire_date)=2006;
--不推荐
select last_name
from employees
where hire_date like '2006%';
查询历年9月份入职的员工：
select last_name
from employees
where to_char(hire_date, 'mm')='09';
select last_name
from employees
where extract(month from hire_date)=9;
其他函数：
nvl：
nvl(val1, val2)
if val1 is not null
then
return val1;
else
return val2;
SQL> select last_name, salary*12*(1+nvl(commission_pct, 0)) total_salary from employees;
练习：
显示所有员工部门编号，没有部门的显示“未分配部门”
case和decode：
IT_PROG +1000
SA_REP +1500
ST_CLERK +2000
其他人工资不变
select salary+1000 from employees where job_id='IT_PROG';
select last_name, job_id, salary,
case job_id
when 'IT_PROG' then salary+1000
when 'SA_REP' then salary+1500
when 'ST_CLERK' then salary+2000
else salary
end new_salary
from employees;
select last_name, job_id, salary,
decode( job_id,
'IT_PROG', salary+1000,
'SA_REP', salary+1500,
'ST_CLERK', salary+2000,
salary) new_salary
from employees;
练习：
按照员工工资，对员工分级显示：
A 20001-25000
B 15001-20000
C 10001-15000
D 5001-10000
E 0-5000
分组函数
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; 错误语法
练习：
公司中不同职位的数量
计算每个部门的人数
按年份分组，求员工的工资总和
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;
多表连接
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
订单表:
CustID StoreID ProdID ChannelID
100 S100 P100 C100
客户表：
CustID name creditlevel
100 abc
地址表：
CustID adress
100 bj
100 tj
获取如下信息，准备工作：
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.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(+);
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(+);
select e.last_name 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;
自连接：
empid ename mgrid
100 abc
101 def 100
102 xyz 100
emp: mgr:
empid ename mgrid empid mgrname
100 abc 100 abc
101 def 100
102 xyz 100
101 def 100 100 abc
102 xyz 100 100 abc
select emp.ename, mgr.mgrname
from emp, mgr
where emp.mgrid=mgr.empid
emp: mgr:
empid ename mgrid empid ename mgrid
100 abc 100 abc
101 def 100 101 def 100
102 xyz 100 102 xyz 100
select e.last_name, m.last_name
from employees e, employees m
where e.manager_id=m.employee_id;
有经理的员工数：106
SQL> select count(*) from employees where manager_id is not null;
没有经理的员工数：1
SQL> select count(*) from employees where manager_id is null;
练习：
显示所有员工姓名和经理姓名，没有经理的显示“无”。
select e.last_name, nvl(m.last_name, 'N/A')
from employees e, employees m
where e.manager_id=m.employee_id(+);
不等值连接：
conn scott/tiger
select e.ename, sg.grade
from emp e, salgrade sg
where e.sal between sg.losal and sg.hisal;
练习：
找出工资大于所在部门平均工资的员工姓名。
create table avg_sal_dept as select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id;
select e.last_name, e.salary, asd.avg_sal
from employees e, avg_sal_dept asd
where e.department_id=asd.department_id
and e.salary>asd.avg_sal;
select e.last_name, e.salary, asd.avg_sal
from employees e, (select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id) asd
where e.department_id=asd.department_id
and e.salary>asd.avg_sal;
