order by 的數(shù)值型靈活使用 select * from table_a where id=p_id order by decode(函數(shù),‘a(chǎn)sc‘,1,‘desc‘,-1)*jsny;
控制試圖的訪問時間: 6.create view ... as select ... from where exists(select x from dual where sysdate>=8:00am and sysdate<=5:00pm)
妙用decode實現(xiàn)排序 select * from tabname order by decode(mode,‘FIFO‘,1,-1)*to_char(rq,‘yyyymmddhh24miss‘);
select * from tabname order by decode(mode,‘FIFO‘,rq-sysdate, sysdate-rq)
找出某個時期內(nèi)工作日數(shù): select count(*) from ( select rownum-1 rnum from all_objects where rownum <= to_date(‘2002-02-28‘,‘yyyy-mm-dd‘) - to_date(‘2002- 02-01‘,‘yyyy-mm-dd‘)+1 ) where to_char( to_date(‘2002-02-01‘,‘yyyy-mm-dd‘)+rnum-1, ‘D‘ ) not in ( ‘1‘, ‘7‘ )
我覺得查詢重復記錄的語句就很經(jīng)典 select rowid,bdsszd from BADWDJ a where a.rowid != (select max(rowid) from BADWDJ b where a.bdsszd =b.bdsszd) 由它引申的就有很多有用的語句,如昨天回答別人的排序的難題 select id,bdsszd from BADWDJ a where a.id = (select max(id) from BADWDJ b where a.bdsszd =b.bdsszd) order by id
樹型結(jié)構(gòu)表的查詢: select ID,PARENT_ID from parent_child connect by prior id = parent_id start with id = 1;
2.group by,這個東東想想簡單,其實好多統(tǒng)計功能是離不開這個操作的。oracle8中擴充了group by rollup和cube的操作。有時候省了你好多功夫的。值得注意的是,當你對事物做過有效的人為歸并之后執(zhí)行g(shù)roup by 往往會更讓人心曠神怡。 3.很表豎置的經(jīng)典寫法,也要記?。簊um(decode( )) group by ... 注意:需要在一個subquery中確定一個橫置判點。 4.樹形結(jié)構(gòu)表的遍歷寫法:select ...from .... start with ... connect by prior (父子關(guān)系表達式)
select * from xxx where decode(:var,null,‘0‘,column1) = decode(:var,null,‘0‘,:var);
816以上的 一些分析函數(shù)如 rank() over() and row_number() over() 當然關(guān)于 group by rollup/cube使用的人恐怕特別少
如何實現(xiàn)最大匹配的操作? 例如:給定字符串 ‘1234‘, 而表中可能有記錄項‘1‘,‘12‘,‘123‘,‘1234‘,‘12345‘,要選出‘1234‘項 select * from ( select col_FT from table_FT where instr(‘12345‘,col_FT)=1 order by length(col_FT) desc) where rownum =1
給你一個意想不到的東西
SQL> select to_char(to_date(12,‘yyyy‘),‘year‘) from dual; TO_CHAR(TO_DATE(12,‘YYYY‘),‘YEAR‘) ------------------------------------------ twelve
select to_char(sysdate,‘day‘) from dual 還有 d、iw、mm等等格式
對于translate函數(shù)有一個功能 比如:找出某個字符串中完全是數(shù)字 select * from xxx where translate(column1,‘1234567890‘,‘‘) = column1; select trunc(sysdate) from dual; select trunc(sysdate,‘mm‘) from dual; 大家構(gòu)造幾個例子看看就能明白 select a,b,sum(c) from xxx group by rollup(a,b);
select a,b,sum(c) from xxx group by cube(a,b);
怎么查找字符串里面包含有%的記錄: 當然,常規(guī)方法就是利用 escape了 可如果不知道escape也行,比如 select * from xxx where replace(a,‘%‘,‘‘) = a;
利用decode解決動態(tài)sql的不確定條件查詢的問題: 假設(shè)前臺傳入的都是變量 select * from xxx where decode(:var,null,‘0‘,column1) = decode(:var,null,‘0‘,:var); 這樣比 like :var||‘%‘ 效率高
另:對于 select ... from a,b where a.a = b.a(+) and b.a is null; 我想對于不明白的朋友,我要交代一下用處才好:
比如,你需要查找在a表中有而b表中沒有的記錄 也許你會選擇 not in: select * from a aa where aa.a1 not in (select a1 from bb); 這是效率最低的 或者: select a1 from aa minus select a1 from bb;
所有這些寫法,都不如下面下率高: select a.* from aa a,bb b where a.a1 = b.a1(+) and b.a1 is null;
給一個很普通的適用的最高效的外連接例子(不是什么新鮮玩意): select ... from a,b where a.a = b.a(+) and b.a is null;
SEQUENCE create or replace trigger bef_ins_t_country_define before insert on t_country_define referencing old as old new as new for each row begin select s_country_id.nextval into :new.country_id from dual; end; / 第二種方法: CREATE OR REPLACE TRIGGER TR1 BEFORE INSERT ON temp_table FOR EACH ROW declare com_num NUMBER; BEGIN SELECT MAX(ID) INTO COM_NUM FROM TEMP_TABLE; :NEW.ID:=COM_NUM+1; END TR1; / 5。限制用戶登錄:創(chuàng)建一個概要文件 create profile CLERK_PROFILE limit session_per_user 1 #用戶可擁有的會話次數(shù) idle_time 10 #進程處于空閑狀態(tài)的時間(10分鐘) 然后就可以將該概要文件授予一個用戶 alter user A profile CLERK_PROFILE; 6。使觸發(fā)器為無效alter trigger yourtriggername disable
如果是對于某一個表的所有的觸發(fā)器: alter table yourtablename disable all triggers
更改數(shù)據(jù)庫時間顯示格式: SQL> alter session set nls_date_format = ‘YYYY-MM-DD HH24:MI:SS‘; 會話已更改。
1. 選取 TOP N 行記錄 A. SELECT * FROM CAT WHERE ROWNUM<=N B. SELECT * FROM ( SELECT * FROM CAT ORDER BY TABLE_TYPE ) WHERE ROWNUM<=N
2. 選取N1-N2行記錄 A. SELECT TABLE_NAME,TABLE_TYPE FROM ( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT ) WHERE ROWSEQ BETWEEN N1+1 AND N2; 或: SELECT * FROM CAT WHERE ROWNUM<=N2 MINUS SELECT * FROM CAT WHERE ROWNUM B. SELECT TABLE_NAME,TABLE_TYPE FROM ( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY TABLE_TYPE) WHERE ROWSEQ BETWEEN N1+1 AND N2;
查主鍵名稱: select * from user_constraints where table_name = ‘ART‘ and constraint_type =‘P‘;
保存過程內(nèi)容到文件 先修改init.ora 例如: utl_file_dir=/usr //路徑為 oracle所在的盤:/usr 此過程將用戶TEMP的P1過程的代碼保存到ORACLE安裝盤下/USR/TEXT.TXT中 create or replace procedure TEST is file_handle utl_file.file_type; STOR_TEXT VARCHAR2(4000); N NUMBER; I NUMBER; begin I:=1; SELECT MAX(LINE) INTO N FROM ALL_SOURCE WHERE OWNER=‘TEMP‘ AND NAME=‘P1‘; file_handle:=utl_file.fopen(‘/usr‘,‘test.txt‘,‘a(chǎn)‘); WHILE I<=N LOOP SELECT TEXT INTO STOR_TEXT FROM ALL_SOURCE WHERE OWNER=‘TEMP‘ AND NAME=‘P1‘ AND LINE= I; I:=I+1; utl_file.put_line(file_handle,stor_text); END LOOP; utl_file.fclose(file_handle); commit; end TEST; /
0、建立分區(qū)表 create table partition_test ( id number(9), tmpStr varchar2(10) ) partition by range(id) ( partition id01 values less than (3000000) tablespace test_tabspc1, partition id02 values less than (6000000) tablespace test_tabspc2, partition id03 values less than (9000000) tablespace test_tabspc3, partition id04 values less than (12000000) tablespace test_tabspc4, partition id05 values less than (MAXVALUE) tablespace test_tabspc5 ) /
1、建立局部分區(qū)索引 Create index your_index on caishui.partition_test(id) local ( partition id01 tablespace test_tabspc1, partition id02 tablespace test_tabspc2, partition id03 tablespace test_tabspc3, partition id04 tablespace test_tabspc4, partition id05 tablespace test_tabspc5 ) /
2、重建某一個分區(qū)的索引 alter index your_index rebuild partition id01 tablespace test_tabspc1 /
3、增加分區(qū) alter table caishui.partition_test add partition id06 values less than (15000000) tablespace test_tabspc6 /
4、有影響
5、可以 ALTER TABLE PARTITION_TEST MERGE PARTITIONS id01, id02 INTO PARTITION 新分區(qū)名 /
6、外部數(shù)據(jù)文件 d:\test.txt 1|豬八戒 2|孫悟空 3|唐僧
建一個控制文件 d:\test.ctl load data infile ‘d:\test.txt‘ append into table partition_test FIELDS TERMINATED BY "|" (id,tmpStr)
create table sale (month char(6),sell number); insert into sale values(‘200001‘,1000); insert into sale values(‘200002‘,1100); insert into sale values(‘200003‘,1200); insert into sale values(‘200004‘,1300); insert into sale values(‘200005‘,1400); insert into sale values(‘200006‘,1500); insert into sale values(‘200007‘,1600); insert into sale values(‘200101‘,1100); insert into sale values(‘200202‘,1200); insert into sale values(‘200301‘,1300); insert into sale values(‘200008‘,1000); commit;
SQL> select rownum,month,sell from sale where rownum=1;(可以用在限制返回記錄條數(shù)的地方,保證不出錯,如:隱式游標)
如何用rownum實現(xiàn)大于、小于邏輯?(返回rownum在4—10之間的數(shù)據(jù))(minus操作,速度會受影響) SQL> select rownum,month,sell from sale where rownum<10 2 minus 3 select rownum,month,sell from sale where rownum<5;
返回第5—9條紀錄,按月份排序 SQL> select * from (select rownum row_id ,month,sell 2 from (select month,sell from sale group by month,sell)) 3 where row_id between 5 and 9;
查所及殺鎖 select l.session_id sid, l.locked_mode lockmode, l.oracle_username db_user, l.os_user_name os_user, s.machine, s.schemaname, o.object_name tablename, q.sql_text from v$locked_object l, v$session s, v$sql q, all_objects o where l.session_id=s.sid and s.type=‘USER‘ and s.sql_address=q.address and l.object_id=o.object_id
alter system kill session ‘sid,SERIAL#‘
1.having 子句的用法
having 子句對 group by 子句所確定的行組進行控制,having 子句條件中只允許涉及常量,聚組函數(shù)或group by 子句中的列.
2.外部聯(lián)接"+"的用法
外部聯(lián)接"+"按其在"="的左邊或右邊分左聯(lián)接和右聯(lián)接.若不帶"+"運算符的表中的一個行不直接匹配于帶"+"預算符的表中的任何行,則前者的行與后者中的一個空行相匹配并被返回.若二者均不帶’+’,則二者中無法匹配的均被返回.利用外部聯(lián)接"+",可以替代效率十分低下的 not in 運算,大大提高運行速度.例如,下面這條命令執(zhí)行起來很慢
select a.empno from emp a where a.empno not in
(select empno from emp1 where job=’SALE’);
倘若利用外部聯(lián)接,改寫命令如下:
select a.empno from emp a ,emp1 b
where a.empno=b.empno(+)
and b.empno is null
and b.job=’SALE’;
可以發(fā)現(xiàn),運行速度明顯提高.
3.刪除表內(nèi)重復記錄的方法
可以利用這樣的命令來刪除表內(nèi)重復記錄:
delete from table_name a
where rowid< (select max(rowid) from table_name
where column1=a.column1 and column2=a.column2
and colum3=a.colum3 and ...);
問:用ORACLE的like(匹配操作命令)操作時,要查的條件含有特殊符號(_或%),該怎樣寫? 如我要找出以tt_開頭的表,若安以下寫法只能取出以tt開頭的表, 因為_在like中用意是任意單一字符。 SELECT Tname FROM tab WHERE Tname like ‘tt_%‘
答:SELECT * FROM tab WHERE tname LIKE ‘TT/_%‘ ESCAPE ‘/‘
自增字段: ORACLE一般的做法是同時使用序列和觸發(fā)器來生成一個自增字段. CREATE SEQUENCE SEQname INCREMENT BY 1 START WITH 1 MAXVALUE 99999999 / CREATE TRIGGER TRGname BEFORE INSERT ON table_name REFERENCING NEW AS :NEW FOR EACH ROW Begin SELECT SEQname.NEXTVAL INTO :NEW.FIELDname FROM DUAL; End; /
動態(tài)sql: 在oracle8.1.5中: 用execute immediate來實現(xiàn) declare tsql varchar2(200); begin tsql:=‘insert into ‘||tname||‘values (‘a(chǎn)aa‘,‘bbb‘)‘; execute immediate tsql; end; /
說明:復制表(只復制結(jié)構(gòu),源表名:a 新表名:b) SQL: select * into b from a where 1<>1 說明:拷貝表(拷貝數(shù)據(jù),源表名:a 目標表名:b) SQL: insert into b(a, b, c) select d,e,f from b; 說明:顯示文章、提交人和最后回復時間 SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 說明:外連接查詢(表名1:a 表名2:b) SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 說明:日程安排提前五分鐘提醒 SQL: select * from 日程安排 where datediff(‘minute‘,f開始時間,getdate())>5 說明:兩張關(guān)聯(lián)表,刪除主表中已經(jīng)在副表中沒有的信息 SQL: delete from info where not exists ( select * from infobz where info.infid=infobz.infid ) 說明:-- SQL: SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE FROM TABLE1, (SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,‘YYYY/MM‘) = TO_CHAR(SYSDATE, ‘YYYY/MM‘)) X, (SELECT NUM, UPD_DATE, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,‘YYYY/MM‘) = TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, ‘YYYY/MM‘) || ‘/01‘,‘YYYY/MM/DD‘) - 1, ‘YYYY/MM‘) ) Y, WHERE X.NUM = Y.NUM (+) AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B WHERE A.NUM = B.NUM 說明:-- SQL: select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名稱=‘"&strdepartmentname&"‘ and 專業(yè)名稱=‘"&strprofessionname&"‘ order by 性別,生源地,高考總成績 說明: 從數(shù)據(jù)庫中去一年的各單位電話費統(tǒng)計(電話費定額賀電化肥清單兩個表來源) SQL: SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, ‘yyyy‘) AS telyear, SUM(decode(TO_CHAR(a.telfeedate, ‘mm‘), ‘01‘, a.factration)) AS JAN, SUM(decode(TO_CHAR(a.telfeedate, ‘mm‘), ‘02‘, a.factration)) AS FRI, SUM(decode(TO_CHAR(a.telfeedate, ‘mm‘), ‘03‘, a.factration)) AS MAR, SUM(decode(TO_CHAR(a.telfeedate, ‘mm‘), ‘04‘, a.factration)) AS APR, SUM(decode(TO_CHAR(a.telfeedate, ‘mm‘), ‘05‘, a.factration)) AS MAY, SUM(decode(TO_CHAR(a.telfeedate, ‘mm‘), ‘06‘, a.factration)) AS JUE, SUM(decode(TO_CHAR(a.telfeedate, ‘mm‘), ‘07‘, a.factration)) AS JUL, SUM(decode(TO_CHAR(a.telfeedate, ‘mm‘), ‘08‘, a.factration)) AS AGU, SUM(decode(TO_CHAR(a.telfeedate, ‘mm‘), ‘09‘, a.factration)) AS SEP, SUM(decode(TO_CHAR(a.telfeedate, ‘mm‘), ‘10‘, a.factration)) AS OCT, SUM(decode(TO_CHAR(a.telfeedate, ‘mm‘), ‘11‘, a.factration)) AS NOV, SUM(decode(TO_CHAR(a.telfeedate, ‘mm‘), ‘12‘, a.factration)) AS DEC FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration FROM TELFEESTAND a, TELFEE b WHERE a.tel = b.telfax) a GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, ‘yyyy‘) 說明:四表聯(lián)查問題: SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
說明:得到表中最小的未使用的ID號 SQL: SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID FROM Handle WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)