SQL語法總結
1.按條件讀取字段, 定義某個字段只取規定的幾個值，或一個值
select os.* from blis_order o,blis_orderservice os where o.orderid=os.orderid and o.ordertypeid not in (4, 8, 10, 11) and o.status in ('New','Accepted','Pending-apPRoval','Pending-effective','Pending-correction') and snp.status='Active' and b.entityid=1
2.去掉重復(互異)的字段distinct
select distinct op.name,op. from blis_chargeactivation ca,blis_orderparameter op where op.mastertype='charge' and ca.chargeactivationid=op.masterid and ca.parentcode like '%NBR Storage Charge%'
3.某個字段不可為null
select os.orderserviceid,os.orderofferid,o.ordertypeid,o.status from Blis_Order o, Blis_Orderservice os where o.orderid=os.orderid and os.orderofferid is not null
4.刪除滿足某個條件的記錄
delete from blis_bstoffermigplan bsf where bsf.keyid='110206'
5.取name_a字段,放入字段別名name_b
select bsf.keyid subcode,bsf.bstoffermigplanid from blis_bstoffermigplan bsf
這里取的是keyid字段， 顯示的為subcode字段。
6.connection rollback commit
rollback 就是把在內存中做的行為取消，不持久化到數據庫中，commit就是把內存中做的行為持久化到數據庫中。
7. 在Oracle中使用Dual, Dual是Oracle一個特有的虛擬表, Oracle中很多系統的sequence（序列）,sequence一般和表建立了一一對應關系，但是要編程插入的話，必須手工指定，比如增加條account數據，相應的值插入SEQ_ACCOUNT.nextval，變量和函數都可以通過Dual中獲得
S: select getdate();
O: select sysdate from dual;
select SEQ_INTEGRATIONTASK.NEXTVAL from DUAL
8.(PK)主鍵(PK) (for database)
9.排序(數字，字母由大到小)
select bsf.* from blis_bstoffermigplan bsf order by bsf.ordertypeid desc
10.插入一條記錄
insert into blis_bstoffermigplan (bstoffermigplanid, entityid, keyid, subioncode, ordertypeid, type, templatecode, currencycode, exceptioncount, lastexception, att1, att2, att3,att4,att5,offercode, status, createdby, creationdate, lastmodifiedby, lastmodifieddate) values (seq_bstoffermigplan.nextval, ?, ?, ?, ?, ?,?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?,sysdate, ?, sysdate)
11，更新一條記錄
update offermigplan.db_table_name set entityid=?,keyid=?,subioncode=?,ordertypeid=?,type=?,templatecode=?,currencycode=?,exceptioncount=?,lastexception=?,att1=?,att2=?,att3=?,att4=?,att5=?,offercode=?,status=?,createdby=?,lastmodifiedby=?,lastmodifieddate=sysdate where bstoffermigplanid=?
12.插入數據量比較大的類型clob
13.查詢日期
下列兩條語句功能相同
select * from blis_account acc where to_char(acc.lastmodifieddate,'YYYYMMDD')>'20050101'
select * from blis_account acc where acc.lastmodifieddate>to_date('2005-01-01','yyyy-mm-dd')
14找出根據某個字段中的值重復的記錄
比如找出chargeactivationid 有相同值的blis_usageaccess記錄
select * from blis_usageaccess where chargeactivationid in (select chargeactivationid from blis_usageaccess group by chargeactivationid having count(*) >1)
USAGEACCESSID CHARGEACTIVATIONID SERVICEACCESSCODE
292518 148701 AUDIO-BROADCAST@
292517 148701 VOip@
292516 148701 CALLIN-DID@
292515 148701 CALLBACK-INTL@
292512 148701 CALLIN@
292513 148701 CALLIN-TOLLFREE@
292514 148701 CALLBACK@
292478 147945 AUDIO-BROADCAST@
292477 147945 VOIP@
292475 147945 CALLBACK-INTL@
292476 147945 CALLIN-DID@
292472 147945 CALLIN@
15.通過查詢獲得某個字段的合計值，如果這個值位null將給出一個預設的默認值
select nvl(ob.bookingvalue，0) bookingvalue from blis_order o, blis_orderbooking ob where o.orderid=ob.orderid and o.orderid =125034 and ob.bookingtypeid = 215 and ob.status = 'Active'
這里關心nvl的用法，nvl（arg,value）代表如果前面的arg的值為null那么返回的值為后面的value
16.知道一個column的名字，但不清楚它屬于哪張table時，可以使用
select * from user_col_comments ucc where ucc.column_name = 'column_name'
比如： select * from user_col_comments ucc where ucc.column_name = ‘ORDERID’ 就會查出一系列有ORDERID字段的表。
17.遍歷兩字段排列
select (pf.offername || ' '|| cur.name) offercode from blis_packageoffer pf,blis_currency cur where cur.status='Active' and pf.status='Active'
結果如下：
offercode
a1 b1
a1 b2
a2 b1
a2 b2
18.條件判斷
case when pc.provisioningby = 'BPS' then 'True'
else 'False' end
select sos.Sosorderserviceid, st.sosprovisionticketid,
(case when pc.provisioningby = 'BPS' then 'True'
else 'False' end) isConnector
from blis_sosprovisionticket st, blis_sosorderform sof,
blis_sosorderservice sos, blis_packagecomponent pc
where sof.sosorderformid = sos.sosorderformid
and sos.sosorderserviceid = st.sosorderserviceid
and sos.status = 'Active' and st.status = 'Active'
and pc.tagname(+) = st.servicetag and pc.provisioningby
and sof.sosorderformid = 104789
19. pc.tagname(+) =st.servicetag
當pc.tagname存在值，st.servicetag不存在值的話，記錄也可以檢索出來。
20.讓表可以手工編輯
select rowid,st.* from blis_sosprovisionticket st where st.sosprovisionticketid=102508
用classes12.zip 還是會拋出classNotFoundException：oracle.jdbc.driver.OracleDriver，換用class12.jar就正常了,classes12.zip 或class12.jar是JDBC oracle驅動類
創建數據庫：
查看所有表: select * from dba_all_tables
查看所有用戶: select * from all_users
查看所有DBA用戶:select * from dba_users
創建role :create role BLIS_ADMIN_ROLE;
創建新用戶:create user username identified by passWord
授予表空間使用權：grant resource to username
授予創建表權限：grant create table to username
授予連接數據庫的權限：grant create session to username
查看所有表空間: select * from dba_tablespaces
把任何表授予某用戶: grant create any table to BLIS_ADMIN_ROLE;
授予某用戶檢索功能:grant create any index to BLIS_ADMIN_ROLE;
授予某用戶對某表有檢索，插入，更新，刪除功能:grant select, insert, update, delete on BLIS_ACCAGENCYCOMMISSION to BLIS_ADMIN_ROLE;
導出數據庫：比如: exp blis/blis@dbls full=y file=d:1.dmp
連接ORACLE數據庫的字符串格式是
jdbc:oracle:thin:@主機:端口:SID
注意是SID 而不是數據庫名
SID不對的話會拋出異常：java.sql.SQLException: Io 異常: Connection refused(DEION=(TMP=)(VSNNUM=153092352)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))
