oracle简单笔记学习

select...列名，表达式，函数
distinct,别名
from... 表名
where... 条件表达式betwween..and ...  ,like,in
order by... 列名，列别名，表达式，数字 desc,asc

函数 --------单行函数
字符函数 upper/lower/lpad/rpad/length/substr/replace/trim/concat
数字函数 round/trunc/mod
日期函数 sysdate
转换函数 to_char
通用函数 nvl
默认的日期格式：dd-mon-RR
日期格式：yyyy/year/mm/dd/day/hh24/mi/ss/am/month
日期函数:
months_between(sysdate,hiredate); 间隔多少个月份
select add_months(sysdate,6) from dual; 加上六个月
select add_months(sysdate.-6) from dual 六个月以前
select sysdate+6 from dual; 加上六天

select next_day(sysdate,5) from dual; 下一个周四
select next_day(sysdate,'FRI') from dual; 下一个周五
select next_day(sysdate.'星期五')from dual
英文环境下使用'FRI'，中文环境下使用星期五，数字通用的
last_day(sysdate) from dual; 本月的最后一天

转换函数：
TO_NUMBER TO_DATE
select to_number('12')*13 from dual; //156
select to_number('$12,345.67','$99,999.99')from dual; //12345.67
9 数字位,
0 数字位，如果长度不够
数字<==>字符
select to_number('$12,345.67','$99,999.99')*0.1 from dual; //Integer.parseInt();
select to_number('ff','xx') from dual; //255
select to_char(12345,'$999,999.00')from dual; //12,345.00
select to_char(12345,'$000,000.00)from dual; //012,345.0
日期<==>字符
----------to_date('10 September 2009','dd Month yyyy')--------
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual; //2011-02-22 11:14:55
select to_char(sysdate,'yyyy/mm/dd day am') from dual; //2011/02/22 tuesday   am
select to_date('2000-10-10','yyyy-mm-dd')from dual; //10-OCT-00

insert into emp(empno,ename,hiredate
values(1234,'zhangsaan',sysdate));

insert into emp(empno,ename,hiredate
values(1235,'zhangwuji',to_date('2010-2-24','yyyy-mm-dd'));

通用函数
nvl,coalesce, －－－空值处理函数
decode －－－分支处理函数
coalesce(comm,sal,100)返回第一个非空的数值
select ename,sal,comm,nvl(comm,100)bouns  from emp; 如果奖金是空的话，就添加上100。
select ename,nvl(job,'not job yet') from emp; 将没有职位的人添加上not job yet这个职位
select ename,sal,comm,coalesce(comm,sal,100) from emp;

select ename,job,sal,decode(job,'SALESMAN',1.05*sal, if语句
      'ANALYST',1.1*sal,
      'MANAGER',1.15*sal,
sal) bonus  from emp;
多表查询
查找叫Smit的人的部门名称
select  t1.ename,t2.dname
from   t1 join t2
on   t1.c1= t2.c2
约束条件，用来保证数据的完整性
pk:primary key 主键，唯一的定位标识｀
fk:foreign 外键用来表示表间的关系
select emp.ename,dept.dname
from emp join dept on emp.deptno=dept.deptno;
dept 被参照的表是主表，父表
emp：外键参照dept表，从表，子表
select emptno
－－－－－－－－－－－－－－－－－－－－－－－－内连接    三种表现形式 join on
select e.ename,e.job,e.sal,d.dname 等值连接
from emp e join dept d
on e.deptno=d.deptno
and e.job='MANAGER';

select e.ename,e.sal,s.grade 非等值连接
from emp e join salgrade s
on e.sal between s.losal and s.hisal;

自连接
select worker.ename,manager.ename
from emp worker join emp manager
on worker.mgr =manager.empno;
一张表里的记录一定在另一张表中存在匹配的记录，否则不能出现在结果集中
－－－－－－－－－－－－－－－－－－－－－－－外连接
select t1.cname,  t2.dname
from  t1
[left | right | full | outer | join ]
join t2    on  t1.c1=t2.c2;

select e.ename,d.dname //emp是驱动表，以驱动表里的数据在结果集中找
from emp e left outer join dept d
on e.deptno=d.deptno;

select e.ename,d.dname
from dept d left outer join emp e
on e.deptno=d.deptno;

select e.ename,d.dname
from dept d full outer  join emp e
on e.deptno=d.deptno;

哪些员工没有下属，不是别人的领导，
外连接＋匹配表pk is null表示否定问题，不是不包括
select  worker.ename,manager.ename 列出所有的职员
from emp worker left outer join emp manager
on worker.mgr=manager.empno;

哪些部门没有叫smith的员工
1)用ename='SMITH'过滤emp表
2）用外连接dept做驱动表，得到结果集＝有smith的部门（驱动表中匹配不到的记录）
3）用匹配表的pk is null过滤掉内连接的结果集
select d.dname from
emp e right outer join dept d on
e.deptno=d.deptno
and e.ename='SMITH' --现执行这条语句
where e.empno is null;

组函数（多行进去出来一行）
select count(*) from t1;
max/min/avg/sum/count
select min(sal) from emp;
select max(sal) from emp;
select sum(sal) from emp;
select avg(sal) from emp;
主函数嵌套单行函数
select avg(nvl(comm,0))from emp;

计算员工分布在几个部门
select distinct deptno from emp;
select count(distinct deptno) from emp;
按照部门分组，计算每个部门的总和
select deptno ,sum(sal)
from emp
group by deptno;
select sum(sal) from emp where deptno is null; //没有部门的薪水总和

按照职位分组，计算每个职位的平均薪水
select job,avg(sal) //分组函数要求   按照select中的某一个去group by的后面
from emp
group  by job;
在select后面所有的非主函数的列必须出现在group by后面-
---结论：select后面的非组函数的列和group by后面的列名保持一致
select deptno ,job ,avg(sal)
from emp
group by deptno ,job
order by deptno ,job;