SQL语法总结

男娘i 2021-09-12 04:20 471阅读 0赞

# SQL语法总结 #

\[**导读**\]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.e

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就是把[内存][Link 1]中做的行为持久化到数据库中。

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][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))))

[Link 1]: http://www.itbulo.com/hardware/cpu/
[blis_blis_dbls]: mailto:blis/blis@dbls