 分頁經(jīng)典實現(xiàn): select t2.* from (select t1.*,rownum rn from ( select * from users ) t1 where rownum <= 600100 ) t2 where t2.rn >= 600000; 在100W+級別的表中,執(zhí)行時間為1.077秒 合并查詢: Union:用于合并2個select查詢語句的并集結(jié)果,會去重復 Union all:類似于union,但是不會去重復,不會排序 Intersect:用于取得2個select結(jié)果集中的交集 Minus:用于取得2個select結(jié)果集中的差集,只顯示存在于第一個select語句并且不存在于第二個select語句中的結(jié)果集. 函數(shù): 1,、 Cube函數(shù)：立方體函數(shù),，根據(jù)Cube中的列分別進行分組 cube(deptno,job)：先按deptno分組,再按job分組, 再按(deptno,job)聯(lián)合分組 顯示每個部門的平均工資,，每個崗位的平均工資,，每個部門每個崗位的平均工資 select avg(sal),deptno,job from emp group by cube(deptno,job); 函數(shù)分類: 一,、 單行函數(shù) 1. 字符函數(shù) (1) ascii():返回與指定的字符相對應的十進制數(shù): select ascii('A') A,ascii('a') a,ascii('0') zero ,ascii(' ') space from dual; A A ZERO SPACE -- ---------- ---------- ---------- 65 97 48 32 (2)char():給出整數(shù),返回對應的字符.和ascii()函數(shù)具有相反的功效. (3)concat:連接2個字符串,和’||’具有相同的功效 (4)initcap():返回字符串,并將字符串的第一個字母大寫 Select initcap('helloworld') h from dual; H ---------- Helloworld (5)instr(C1,C2,I,J):在一個指定的字符串中搜索指定字符的位置, C1:被搜索的字符串; C2:希望搜索的字符; I:搜索的開始位置,默認為1: J:出現(xiàn)的第幾次,默認為1. 例: select instr('oracle traning','ra',1,2) as indexs from dual; INDEXS ---------- 9 (6)length:返回字符串的長度 (7)lower()和upper():將字符串轉(zhuǎn)化為小/大寫 例: select lower('Aa') v1,upper('Aa') as v2 from dual; V1 V2 -- -- aa AA (8)rpad()和lpad():在列的右邊/坐標粘貼字符 select rpad ('gao',10,'*') from dual; RPAD('GAO',10,'*') ------------------ gao******* select rpad ('gao',10,'*') from dual; RPAD('GAO',10,'*') ------------------ gao******* (9)ltrim()和rtrim()去掉左邊和右邊的空格 (10)substr(string,start,count):截取字符串,注意,start從1開始 (11)replace(string,s1,s2):替換一個字符串中的某些字符 Ps:在用replace替換select查詢出的內(nèi)容時,替換掉的僅僅是顯示的數(shù)據(jù),數(shù)據(jù)表中保存的數(shù)據(jù)并未被改變. (12)soundex():返回一個與給定的字符串讀音相同的字符串(并不常用) (13)trim():剪掉字符串前后指定的字符, 如果不指定,默認剪掉空格(與字符語法略有不同) select trim('*' from '**張三**' ) as name from dual; NAME ---- 張三 2.數(shù)字函數(shù) (1)abs:返回指定值的絕對值 (2)acos():反余弦 …還有很多類似的三角函數(shù)的函數(shù),這里不一一列出. (3)ceil():返回大于或等于給出數(shù)字的最大整數(shù). ceil是天花板的意思,所以是向上取整. select ceil(4.12),ceil(5) from dual; CEIL(4.12) CEIL(5) ---------- ---------- 5 5 (4)floor():返回小于或等于給出數(shù)字的最大整數(shù).和ceil有類似的效果 (5)exp():返回一個數(shù)字e(2.7182818)的n次方根 (6)in(n)和log(n1,n2):返回一個數(shù)字的對數(shù)值和指數(shù)值 (7)mod(n1,n2):返回一個n1除以n2的余數(shù) (8)power(n1,n2):返回n1的n2次方根.power(2,3)返回值為2的3次方8 (9)round():用于四舍五入 trunc():用于截取,可以指定截取到小數(shù)點后面幾位小數(shù) select trunc(456.123,2),trunc(456.123,-2),round(456.123) from dual; TRUNC(456.123,2) TRUNC(456.123,-2) ROUND(456.123) ---------------- ----------------- -------------- 456.12 400 456 (10)sign(n):取數(shù)字n的符號,大于0返回1,小于0返回-1,等于0返回0 3.日期函數(shù): (1) add_months():增加或減去月份 例:顯示最近3個月入職的員工 分析:入職時間加上3個月,如果時間大于當前日期,則是3個月內(nèi)入職的 select * from emp where add_months(hiredate,3) > sysdate; (2) last_day():返回日期的最后一天.比如查詢這個月的最后一天是幾月幾號: select last_day(sysdate) from dual; (3) months_between():計算2個時間點之后有幾個月. 比如:計算smith入職到現(xiàn)在又多少個月 select months_between(sysdate,t.hiredate) from emp t where t.empno = 7369; MONTHS_BETWEEN(SYSDATE,T.HIRED ------------------------------ 408.385160543608 (4) next_day(date,’day’):給出日期date和星期x之后下一個星期的日期 select next_day('18-5月-2001','星期五') from dual; NEXT_DAY('18-5月-2001','星期五 ------------------------------ 2001/5/25 如上: 離 2001 年 5 月 18 日最近的星期五是 2001 年五月 25 日. (5)sysdate:獲取當前時間. 4.轉(zhuǎn)換函數(shù): (1) chartorowid()/rowidtochar():將字符數(shù)據(jù)類型和rowid類型相互轉(zhuǎn)換 (2) convert(c,dest,sset):將源字符串sset從一個字符集轉(zhuǎn)到另一個dest字符集.(沒搞明白是怎么用的) (3)hextoraw():將一個十六進制的字符串轉(zhuǎn)換成二進制 (4)rowtohex():將一個二進制的字符串轉(zhuǎn)換成十六進制 (5) to_char(date,fmt):將日期date按照fmt格式轉(zhuǎn)換成char格式 例:select to_char(sysdate,'yyyy-mm-dd hh24:mm:ss') from dual; TO_CHAR(SYSDATE,'YYYY-MM-DDHH2 ------------------------------ 2014-12-28 22:12:26 Fmt的格式不僅僅限于時間,還有其他的格式: yy:顯示兩位數(shù)字的年份: 9:表示數(shù)字,并忽略前面的0 yyyy:顯示4位數(shù)字的年份 0:表示數(shù)字,如果位數(shù)不足,用0補齊 mm:兩位數(shù)字的年份 .:在指定位置顯示. dd:2位數(shù)字的天 ,:在指定位置顯示, hh24: $:在數(shù)字前面加美元符號$ hh12 L:在數(shù)字前面加本地貨幣符號 mi,ss:顯示分鐘/秒 C:在數(shù)字前面加國際貨幣符號 day:星期幾 G:在指定位置顯示組分隔符 D:在指定位置顯示小數(shù)點符號(.) (6) to_date(string,'format'):將字符串轉(zhuǎn)換成oracle中的日期. Formate:是預定義的日期格式 (7) to_multi_byte():將單字節(jié)字符串轉(zhuǎn)換成多字節(jié)字符 (8) to_number():將給出的字符串轉(zhuǎn)換成數(shù)字 (9) bfilename(dir,file): 參數(shù) dir 表示路徑,，參數(shù) file 表示文件名,。 5.系統(tǒng)函數(shù): (1) convert(‘x’,’desc’,’source’):將x變量的源source轉(zhuǎn)換為desc (2)dump(s,fmt,start,length):以fmt指定的內(nèi)部數(shù)字格式返回一個varchar2類型的值 (3) empty_blob()/empty_clob():對大數(shù)據(jù)類型字段初始化 (4)greatest()/least():返回一組表示式中的最大/小值,比較的ascii碼值 (5) uid/user:返回當前登錄用戶的uid/用戶名 (6) sys_context('userenv',info); select sys_context('userenv','db_name') from dual; select sys_context('userenv',’host’) from dual; info可以的選項: terminal:當前會話客戶想對應的終端的標識符 language:語言 db_name:當前數(shù)據(jù)庫名稱 nls_date_format:當前會話客戶端所對應的日期格式 session_user: 當前會話客戶端所對應的數(shù)據(jù)庫用戶名 current_schemal:當前會話客戶端所對應的解決方案 host:數(shù)據(jù)庫所在的主機名 二、多行函數(shù) (1) Avg: (2) Max(): (3) Min(): (4) Stddev():求標準差 (5) variance():求協(xié)方差 (6) group by:分組函數(shù) (7) having:對group by的分組數(shù)據(jù)進行過濾 (8) order by:排序函數(shù) (9) decode: 類似于 case 語句,，或者說是數(shù)字電路中的譯碼器電路,。 例: select decode(deptno,10,'這是 10 號部門',20,'這是 20 號部門',30,'這是 30 號部門') from emp; DECODE(DEPTNO,10,'這是10號部門 ------------------------------ 這是 20 號部門 這是 30 號部門 這是 30 號部門 這是 20 號部門 這是 30 號部門 這是 30 號部門 這是 10 號部門 這是 20 號部門 這是 10 號部門 這是 30 號部門 這是 20 號部門 這是 30 號部門 這是 20 號部門 這是 10 號部門 |
|
|
