SQL语法总结

网络整理 - 07-27

　　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)(