Scott表下有這么幾個常用的表，而且還帶有數據。分別是emp、dept、salgrade； 1、查看表結構用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、去掉重復數據distinct select distinct deptno from scott.emp; 去掉重復組合：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 轉義 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中的#號是轉義字符，相當于\ 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(‘abcABC’) from dual; select upper(‘abcABC’) from dual; substr(target, startIndex, length) select substr(‘abcABC’, 1, 3) from dual; ? B、chr、ascii 將數字安裝ascii值轉換成字符：select char(65) from dual; 將字符轉換成ascii值：select ascii(‘Z’) from dual; C、round、to_char 精確小數 select round(22.456) from dual; 保留2位小數：select round(22.456, 2) from dual; 精確到個位：select round(22.456, -1) from dual; 貨幣 設置貨幣格式，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位的數字年 YEAR 年的拼寫 MM 數字月 MONTH 月的全拼 MON 月的縮寫 DD 數字日 DAY 星期的全拼 DY 星期的縮寫 AM 表示上午或者下午 HH24、HH12 12小時制或24小時制 MI 分鐘 SS 秒鐘 SP 數字的拼寫 TH 數字的序數詞 ? “特殊字符” 假如特殊字符 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可以將某個字段的空值轉換成指定的值 select ename, sal, nvl(comm, 1.00) from scott.emp; ? 9、group function 組函數：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 對分組數據進行過濾 求部門評價工資： 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、樹狀結構查詢 select level, empno, ename, mgr from emp connect by prior mgr = empno; ? 17、排序函數 --按部門分組，給出分組后的序號 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、交集、并集、割集查詢 --并集：不帶重復數據 select * from emp union select * from emp2; --并集：帶重復數據 select * from emp union all select * from emp2; --割集,顯示不同部分 select * from emp minus select * from emp2; ? 19、 查詢系統表、視圖 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、練習題 --部門最高薪資員工信息 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; --經理人 select ename, job from emp where empno in (select mgr from emp); --不用分組函數，查詢薪水最高值 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 ) ); --部門經理人中，平均薪水最低的部門名稱 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) ); --比普通員工的最高薪水還要高的經理人名稱 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 ) );

轉載于:https://www.cnblogs.com/bokxy/p/4873017.html

總結

以上是生活随笔為你收集整理的Oracle笔记 三、function 、select的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。