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