Scott表下有這么幾個常用的表,，而且還帶有數(shù)據(jù)。分別是emp,、dept,、salgrade,； 1,、查看表結(jié)構(gòu)用desc desc emp; 2、空表dual,，最常用的空表,，如： select 2 * 4 from dual; select sysdate from dual; 3,、雙引號能保持格式 如：select sysdate “toDay 日 期” from dual; 4、|| 字符串連接 如：select 2*3 || 8 from dual; select ename || sal from scott.emp; select ename || ‘ORACLE’ from scott.emp; 5,、單引號,，如：select 2 * 2 || 'abc''efg' from dual; 用兩個單引號表示一個單引號 6、去掉重復(fù)數(shù)據(jù)distinct select distinct deptno from scott.emp; 去掉重復(fù)組合：select distinct deptno,job from scott.emp; 7,、where查詢 A,、=查詢，select * from scott.emp where sal = 1500; B,、比較<,、>、>=,、<= select * from scott.emp where sal > 1500; C,、and or select * from scott.emp where sal > 1500 and sal <= 5000 or deptno = 10; D、in,、not in select * from scott.emp where sal in (1500, 800) and deptno not in (10, 20) E,、like模糊 escape 轉(zhuǎn)義 Select * from scott.emp where ename like ‘%in%’; Select * from scott.emp where ename like ‘%in\%k%’; Select * from scott.emp where ename like ‘%in#%k%’ escape ‘#’; 表示like中的#號是轉(zhuǎn)義字符，相當(dāng)于\ F,、is null,、is not null K、 order by select sal, ename from scott.emp order by sal; select sal, ename from scott.emp order by sal asc; select sal, ename from scott.emp order by sal desc; select sal, ename from scott.emp where sal > 2000 order by sal desc; select sal, deptno, ename from scott.emp order by sal,deptno desc; 8,、function A,、lower、upper,、substr select lower(‘a(chǎn)bcABC’) from dual; select upper(‘a(chǎn)bcABC’) from dual; substr(target, startIndex, length) select substr(‘a(chǎn)bcABC’, 1, 3) from dual; B,、chr、ascii 將數(shù)字安裝ascii值轉(zhuǎn)換成字符：select char(65) from dual; 將字符轉(zhuǎn)換成ascii值：select ascii(‘Z’) from dual; C,、round,、to_char 精確小數(shù) select round(22.456) from dual; 保留2位小數(shù)：select round(22.456, 2) from dual; 精確到個位：select round(22.456, -1) from dual; 貨幣 設(shè)置貨幣格式，000前面不足就用0代替 select to_char(sal, '$000,000.00') from scott.emp; 999就不會替換不足的地方,，只會安裝格式輸出 select to_char(sal, '$999,999.99') from scott.emp; 本地貨幣格式 select to_char(sal, 'L999,999.99') from scott.emp; 日期 日期格式 格式控制 描述 YYYY,、YYY、YY 分別代表4位,、3位,、2位的數(shù)字年 YEAR 年的拼寫 MM 數(shù)字月 MONTH 月的全拼 MON 月的縮寫 DD 數(shù)字日 DAY 星期的全拼 DY 星期的縮寫 AM 表示上午或者下午 HH24、HH12 12小時制或24小時制 MI 分鐘 SS 秒鐘 SP 數(shù)字的拼寫 TH 數(shù)字的序數(shù)詞 “特殊字符” 假如特殊字符 HH24:MI:SS AM 15:43:20 PM select to_char(sysdate, 'YYYY-MM-DD HH:MI:SS') from dual; select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual; D,、to_date,、to_number、nvl to_date(target, current_format) select to_date('2011-4-2 17:55:55', 'YYYY-MM-DD HH:MI:SS') from dual; select to_number('$12,322.56', '$999,999.99') 10 from dual; select to_number('$12,322.56', '$00,000.00') 10 from dual; select to_number('22.56') 10 from dual; nvl可以將某個字段的空值轉(zhuǎn)換成指定的值 select ename, sal, nvl(comm, 1.00) from scott.emp; 9,、group function 組函數(shù)：min,、max,、avg、sum,、count select max(sal) from scott.emp; select min(sal) from scott.emp; select avg(sal) from emp; select round(avg(sal), 2) from emp; select to_char(avg(sal), 'L999,999.99') from emp; select sum(sal) from emp; select count(comm) from emp; select count(distinct deptno) from emp; 10,、group by 分組 select deptno, avg(sal) from emp group by deptno; select deptno, job, avg(sal) from emp group by deptno, job; 求部門最高工資的所在部門的員工信息： select deptno, ename, sal from emp where sal in (select max(sal) from emp group by deptno); 11、having 對分組數(shù)據(jù)進(jìn)行過濾 求部門評價工資： select * from (select avg(sal) sal, deptno from emp group by deptno) where sal > 2000; select avg(sal) sal, deptno from emp group by deptno having avg(sal) > 2000; 12,、子查詢 求部門分組后工資最高的員工信息 select emp.ename, emp.sal, emp.deptno from emp, (select max(sal) max_sal, deptno from emp group by deptno) t where emp.sal = t.max_sal and emp.deptno = t.deptno; 求部門平均工資等級 select s.grade, t.deptno, t.avg_sal from scott.salgrade s, (select deptno, avg(sal) avg_sal from emp group by deptno) t where t.avg_sal > s.losal and t.avg_sal < s.hisal;（between） 13,、自連接 select a.ename, b.ename mgr_name from emp a, emp b where a.empno = b.mgr; 14、 連接查詢 select dname, ename from dept, emp where dept.deptno = emp.deptno; select dname, ename from dept join emp on dept.deptno = emp.deptno; select dname, ename from dept join emp using(deptno); select dname, ename from dept left join emp on dept.deptno = emp.deptno; select dname, ename from dept right join emp on dept.deptno = emp.deptno; select dname, ename from dept full join emp on dept.deptno = emp.deptno; select a.ename, b.ename mgr_name from emp a join emp b on a.mgr = b.empno; select a.ename, b.ename mgr_name from emp a left join emp b on a.mgr = b.empno; 15,、 Rownum select rounum, deptno, dname from dept; select * from ( select rownum r, dept.* from dept ) t where t.r > 2; 16,、樹狀結(jié)構(gòu)查詢 select level, empno, ename, mgr from emp connect by prior mgr = empno; 17、排序函數(shù) --按部門分組,，給出分組后的序號 select row_number() over(partition by deptno order by sal), emp.* from emp; --rank排序,，空出相同部分 select rank() over(partition by deptno order by sal), emp.* from emp; select rank() over(order by deptno), emp.* from emp; select rank() over(order by sal), emp.* from emp; --dense_rank排序給出相同序號,不空留序號 select rank() over(order by sal), emp.* from emp; select dense_rank() over(order by sal), emp.* from emp; 18、交集,、并集,、割集查詢 --并集：不帶重復(fù)數(shù)據(jù) select * from emp union select * from emp2; --并集：帶重復(fù)數(shù)據(jù) select * from emp union all select * from emp2; --割集,顯示不同部分 select * from emp minus select * from emp2; 19、 查詢系統(tǒng)表,、視圖 select owner, object_name, object_type, status, dba_objects.* from dba_objects where object_type = 'view' and status = 'invalid'; select * from user_objects where object_type like 'PROCEDURE'; 20,、練習(xí)題 --部門最高薪資員工信息 select ename, sal, deptno from emp where sal in (select max(sal) from emp group by deptno); --部門最高薪資員工信息 select ename, sal, emp.deptno from emp join (select max(sal) max_sal, deptno from emp group by deptno) t on emp.deptno = t.deptno and emp.sal = t.max_sal; --部門平均薪資等級 select grade, losal, hisal, t.avg_sal from salgrade join (select avg(sal) avg_sal, deptno from emp group by deptno) t on t.avg_sal between losal and hisal; --經(jīng)理人 select ename, job from emp where empno in (select mgr from emp); --不用分組函數(shù)，查詢薪水最高值 select * from (select sal, ename from emp order by sal desc) where rownum = 1; select distinct a.sal from emp a join emp b on a.sal > b.sal where rownum = 1; select sal from emp where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal); --部門平均薪水最高的部門編號 select deptno, t.avg_sal from (select avg(sal) avg_sal, deptno from emp group by deptno) t where avg_sal = ( select max(avg_sal) max_sal from (select avg(sal) avg_sal, deptno from emp group by deptno) ); select deptno, t.avg_sal from (select avg(sal) avg_sal, deptno from emp group by deptno) t where avg_sal = ( select max(avg(sal)) max_sal from emp group by deptno ); --部門平均薪水最高的部門名稱 select dname from dept where deptno = ( select deptno from (select avg(sal) avg_sal, deptno from emp group by deptno) t where avg_sal = ( select max(avg_sal) max_sal from (select avg(sal) avg_sal, deptno from emp group by deptno) ) ); select dname from dept where deptno = ( select deptno from (select avg(sal) avg_sal, deptno from emp group by deptno) t where avg_sal = ( select max(avg(sal)) from emp group by deptno ) ); --平均薪水最低的部門的部門名稱 select dname from dept where deptno = ( select deptno from (select avg(sal) avg_sal, deptno from emp group by deptno) where avg_sal = ( select min(avg_sal) min_sal from ( select avg(sal) avg_sal from emp group by deptno ) ) ); select dname from dept where deptno = ( select deptno from (select avg(sal) avg_sal, deptno from emp group by deptno) where avg_sal = ( select min(avg(sal)) avg_sal from emp group by deptno ) ); --平均薪水等級最低的部門的部門名稱 select dname from dept where deptno = ( select deptno from ( select grade, t.deptno from salgrade s join ( select avg(sal) avg_sal, deptno from emp group by deptno ) t on t.avg_sal between s.losal and s.hisal ) where grade = ( select min(grade) from salgrade s join ( select avg(sal) avg_sal, deptno from emp group by deptno ) t on t.avg_sal between s.losal and s.hisal ) ); --部門經(jīng)理人中,，平均薪水最低的部門名稱 select t.deptno, dname from ( select sal, deptno from emp where empno in (select distinct mgr from emp) ) t join dept on t.deptno = dept.deptno where sal = ( select min(sal) from emp where empno in (select distinct mgr from emp) ); --比普通員工的最高薪水還要高的經(jīng)理人名稱 select * from ( select empno, ename, sal from emp where empno in (select distinct mgr from emp where mgr is not null) ) t where t.sal > ( select max(sal) max_sal from emp where empno not in ( select distinct mgr from emp where mgr is not null ) ); 出處：http://www.cnblogs.com/hoojo/archive/2011/05/03/2035246.html
|
