標籤：
查詢現有資料庫：select name from V$database;解鎖使用者scott:alter user scott account unlock;普通使用者串連：conn scott預設密碼：tiger普通管理員：system/system超級管理員：Sys/sys中斷連線：disconnect目前使用者：show user查看該使用者下的所有對象：select * from tab;dual表是oracle內虛擬一個表，妙用很多
單行函數
模糊查詢%表示零個或多個字元_ 表示一個字元對於特殊符號可使用ESCAPE標識符來尋找select * from emp where ename like ‘%*_%‘ escape ‘*‘上面的escape表示*後面的那個符號不被當成特殊字元處理，就是尋找普通的_符號
Scott使用者內建的表結構僱員表EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)部門表dept(deptno,dname,loc)工資等級表salgrade(grade,losal,hisal)獎金錶BONUS(ENAME,JOB,SAL,COMM)
單表查詢example選擇在部門30中員工的所有資訊select * from emp where deptno=30;列出職位為（manager）的員工的編號，姓名select empno,ename from emp where job=‘MANAGER‘找出獎金高出工資的員工select * from emp where comm>sal;找出每個員工獎金和工資的總和select ename,comm+sal from emp;找出部門10中的經理（MANAGER）和部門20中的普通員工（CLERK）；找出部門10中既不是經理也不少普通員工，而且工資大於2000的員工SELECT * FROM EMP WHERE DEPTNO=10 AND JOB NOT IN (‘MANAGER‘,‘CLERK‘) AND SAL>=2000；找出有獎金的員工的不同工作select distinct job from EMP where comm is not null and comm>0;找出沒有獎金或者獎金低於500的員工select * from emp where comm<500 or comm is null;顯示僱員姓名，根據其服務年限，將最老的僱員排在最前面select ename from emp order by hiredate;
字元函數upper，lower（大寫，小寫）initcap(將每個能識別的單詞的第一個字母大寫，其他小寫，中間出息中文，空格都將視為一個單詞)concat(‘a‘,‘b‘); ‘a‘||‘b‘ 串連倆字串length（）字串長度substr(‘abcde‘,length(‘abcde‘)-2,2) 從第三個字元開始取‘abcde’的2個字元，結果為‘cd‘,第三個值可預設replace(ename,‘A‘,a)將ename的所有A換為ainstr(‘Hello world‘,‘or‘)第二個字串在第一個字串中的位置（結果為8）lpad(‘Smith‘,10,‘*‘) *****Smithrpad(‘smith‘,10,‘*‘) Smith*****trim(‘ Mr Smith ‘)過濾首尾空格trim([BOTH|LEADING|TRAILING] ‘*‘ from ‘****ab***‘) ab
數值函數round（419，-1） 精確到小數點後多少位，進行四捨五入，-1的結果為420round（412.313，2）結果為412.31mod(5,4) 取餘，1trunc類似round，截取時不進行四捨五入
日期函數months_between(date1,date2),返回相差的月數date1-date2add_months(to_date(‘19910522‘,‘yyyymmdd‘))增加一個月next_day(sysdate,‘星期一‘)下一個星期一的日期last_day(sysdate)對應月份的最後一天
轉換函式//sysdate---2015-03-16to_char(sysdate,‘yyyy‘)2015to_char(sysdate,‘fmyyyy-mm-dd‘)2015-3-16to_char(sysdate,‘yyyy-mm-dd‘)2015-03-16
select to_char(sal,‘L999,999,999‘)from emp; ￥800 ￥3,000to_char(sysdate,‘D‘)返回這是這周的第幾天，注意這裡返回的是美國習慣，即周日是第一天to_number(‘13‘)to_date(‘20051103‘,‘yyyymmdd‘)
通用函數nvl(欄位名，‘x’)該欄位若為空白值時，顯示為Xnullif(運算式1，運算式2)如果運算式1等於運算式2，則返回空值，否則返回運算式1的值nvl2(運算式，不為空白設值，為空白設值)coalesce（運算式1，運算式2，運算式3）依次考察各參數運算式，遇到非null值即停止並返回該值select empno,ename,sal,case deptno when 10 then ‘財務部‘ when 20 then ‘研發部‘ when 30 then ‘銷售部‘ else‘未知部門‘ end部門 from emp;select empno,ename,sal,decode( deptno, ‘財務部‘, 20,‘研發部‘ ,30,‘銷售部‘,‘未知部門‘ )部門 from emp;
練習：1.找出每個月倒數第三天受雇的員工（如：2009-5-29）select * from emp where hiredate+2=last_day(hiredate);2.找出30年前的雇的員工select * from emp where hiredate<=add_months(sysdate,-30*12);3.所有員工名字前加上Dear ,並且首字母大寫select * from ‘Dear ‘|| initcap(ename) from emp;4.找出姓名為5個字母的員工select * from emp where length(ename)=5;5.找出姓名中不帶R這個字母的員工select * from emp where ename not like ‘%R%‘;6.顯示所有員工的姓名的第一個字select substr(ename,1,1) from emp;7.顯示所有員工，按名字第一個字母降序排列，若相同，則按工資升序排列select ename,sal from emp order by substr(ename,1,1) desc,sal;8.假設一個月為30天，找出所有員工的日薪，不計小數select ename,round(sal/30)daily_sal from emp;9.找到2月受雇的員工select * from emp where to_char(hiredate,‘fmmm‘)=‘2‘;10.列出員工加入公司的天數(四捨五入)select ename,round(months_between(sysdate,hiredate)*30)from emp;11.分別用case和decode函數列出員工所在的部門，deptno顯示‘部門10’，deptno=20顯示‘部門20’否則為‘其他部門’select ename,case deptno when 10 then‘部門10‘ when 20 then ‘部門20‘ else ‘其他部門‘ end 部門 from emp;select ename,decode(deptno,10,‘部門10‘,20,‘部門20‘,‘其他部門‘)部門 from emp;
分組函數count如果資料庫表沒有資料，count（*）返回的不是null，而是0avg,max,min,sum用avg算均值時，若為null，不算均數，如emp表中除了1400,300,500,0外，都為空白值，算出來的值為550，此時可用nvl（）函數強制分組函數處理空值select avg(nvl(comm,0)) from emp;group by 不允許出現在where中(用having)select deptno,avg(sal) from emp group by deptno;having 字句select deptno,job,avg(sal) from emp where hiredate >=todate(‘1981-05-01‘,‘yyyy-mm-dd‘) group by deptno,job having avg(sal) >1200 order by deptno,job;分組函數嵌套select max(avg(sal)) from emp group by deptno;練習：1.統計各部門下工資大於500員工的平均工資表select avg(sal) where sal>500 group deptno;2.統計各部門下平均工資大於1600的部門select deptno,avg(sal) from emp group by deptno having avg(sal)>1600;3.算出部門30中薪水最高的員工薪水select max(sal) from where deptno=30;4.算出部門30中薪水最高的員工姓名select ename from emp where sal=(select max(sal) from where deptno=30);5.算出每個職位的員工數和最低工資select job,min(sal),count(*)from emp group by job;
6.算出每個部門，每個職位的平均工資和平均獎金（平均值包括沒有獎金）如果平均獎金大於300，顯示‘獎金不錯‘，如果平均獎金100到300，顯示‘獎金一般‘，如果平均獎金小於100，顯示“基本沒有獎金”，按部門編號降序，平均工資降序排列select deptno,job,avg(sal)平均工資，avg(nvl(comm,0))平均獎金，case when avg(nvl(comm,0))>=300 then ‘獎金不錯‘ avg(nvl(comm,0))>100 and avg(nvl(comm,0))<300 then ‘獎金一般‘ else ‘基本沒有獎金‘ end 獎金狀況 from emp group by deptno,job order by deptno desc, avg(sal) desc;7.列出員工表中每個部門的員工數，和部門noselect deptno,count(*) from emp group by deptno;
8.得到工資大於自己部門平均工資的員工資訊select * from emp e1,(select deptno,avg(sal)avgsal from emp group by deptno)e2 where e1.deptno=e2.deptno and e1.sal>e2.avgsal;9.分組統計每個部門下，每種職位的平均獎金和總工資（包括獎金）select deptno,job,avg(nvl(comm,0)),sum(sal+nvl(comm,0)) from emp group by deptno,job;
oracle從零開始學習筆記
