oracle_fun

select * from emp e where e.sal>2000 order by e.sal asc;
select * from emp e where e.sal>2000 order by e.sal desc;
select * from emp e where e.sal>2000 order by e.sal,e.job desc;


number(4,2)整数四位， 小数两位  数值类型
char 定长字符串  牺牲空间 保证效率(2000 bytes)
 varchar2 不定长字符串  牺牲效率 节约空间(4000 bytes)
(也有长度限制（4mb）,保存文件的存放路径)
nchar(size)


clob
blob


date


timestamp(精确度高)
binary_float 在java没有对应
select e.ename||'好人'||e.job from emp e;//拼接字符串
select p.*,(p.sal+p.comm)*12 "年薪水"from emp p;(有问题)
--nvl(列名，值) if（列值==null） return 值
select nvl(e.comm,0) from emp e;
select p.*,(p.sal+nvl(p.comm,0))*12 "年薪水"from emp p;
--decode if（==){}else if(==) return else if(==) return
select decode(e.job,'CLERK','没出息','SALESMAN','没前途','MANAGER','没本事',e.job) from emp e;
--case when
select 
 e.sal
case when e.sal<1000 then '穷光蛋'
 when e.sal>=1000 and e.sal<=3000 then '也是穷光蛋'
  when e.sal>3000 then '还是穷光蛋'
  end 判断穷蛋
 from emp e;
别名不要用单引号
--字符串函数
select length(e.ename),e.ename from emp e where length(e.ename)<=4;
select substr(e.ename,1,3) from emp e;
select trim('  aaa  ') from dual;
select * from dual;
ltrim/rtrim
select rtrim('  aaaa ')||'cc' from dual;
select 'aaa'||'c',concat('a','c') from dual;
select sysdate from dual;
select sysdate+1 from dual;以天为单位
--转换函数
varchar2 or char -- number
(隐式转换)
select ename,hiredate from emp where hiredate
--求emp表中在1981年入职的员工信息
select from emp e where to_char(e.hiredate,'yyyy')='1981'
select from emp e where to_char(e.hiredate,'yyyy-MM')='1981-02'
>'20-2月-08';


to_char
to_number
to_date;


to_char(date,'fmt');
YYYY\YY
MM
MON
DD
--数字转成字符
select to_char(3.14,'L9999.999') from dual;
select to_char(3.14,'L0000.999') from dual;


'yyyy-MM-dd HH24:mi:ss' to_char()(比较灵活)
--to_number
--to_date() 字符串转日期
select to_date('2011-02-18 13:12:20','yyy-MM-dd HH24:mi:ss') from dual;


--to_number
select to_number('3,14','9999.999') from dual;()不能随便用0
select to_number('3.14')+1 from dual;
Select to_char(next_day(add_months(hiredate,6),'星期五'),' fmDay,Month ddth,YYYY')  "review"
     from emp order by hiredate;
select sum() avg()跳过空（5条记录）
select sum(e.comm),count(e.comm),avg(e.comm) from emp e;
sekect avg(nvl(e.comm,0)) from emp e;
select count(e.empno) from emp e;


select e.empno,min(e.sal)
select distinct e.job from emp e;


--group by
select e.job,min(e.sal) from emp e group by e,job;


select e.job,e.ename,min(e.sal) from emp e group by e.job,e.ename;
select e.job,e.deptno,min(e.sal) from emp e group by e.job,e.deptno;
e.job,e.deptno这两个字段值相同为一组
select e.ename,min(e.sal) from emp e group by e.ename;
select e.ename from emp e ;


select e.comm from emp e group by e.comm;




select e.comm from emp e where 1=1 group by e.comm order by e.comm;(顺序)


select e.job,sum(e.sal) from emp e group by e.job having sum(e.sal)>6000


(完整语句如下)
--where sum(e.sal)(x)(写单行函数)
from
where 
group by 
having 
select
order by


select e.job,sum(e.sal) sal from emp e group by e.job having sum(e.sal)>6000 order by sal;


多表查询