ASP源码
PHP源码
.NET源码
JSP源码
SQL语法总结
1.按条件读取字段, 定义某个字段只取规定的几个值,或一个值
select os.* from blis_order o,blis_orderservice os where o.***erid=os.orderid and o.***ertypeid not in (4, 8, 10, 11) and o.status in ('New','Accepted','Pending-approval','Pending-effective','Pending-correction') and sn***tatus='Active' and b.***ityid=1
2.去掉重复(互异)的字段distinct
select distinct op.name,op. from blis_chargeactivation ca,blis_orderparameter op where op***stertype='charge' and ca***argeactivationid=op.masterid and ca***rentcode like '%NBR Storage Charge%'
3.某个字段不可为null
select os***derserviceid,os***derofferid,o.***ertypeid,o.status from Blis_Order o, Blis_Orderservice os where o.***erid=os.orderid and os***derofferid is not null
4.删除满足某个条件的记录
delete from blis_bstoffermigplan bsf where bs***eyid='110206'
5.取name_a字段,放入字段别名name_b
select bs***eyid subcode,bs***stoffermigplanid from blis_bstoffermigplan bsf
这里取的是keyid字段, 显示的为subcode字段。
6.***nection rollback commit
rollback 就是把在内存中做的行为取消,不持久化到数据库中,commit就是把内存中做的行为持久化到数据库中。
7. 在Oracle中使用Dual, Dual是Oracle一个特有的虚拟表, Oracle中很多系统的sequence(序列),sequence一般和表建立了一一对应关系,但是要编程插入的话,必须手工指定,比如增加条account数据,相应的值插入SE***CCOUNT.nextval,变量和函数都可以通过Dual中获得
S: select getdate();
O: select sysdate from dual;
select SE***NTEGRATIONTASK.NEXTVAL from DUAL
8.(PK)主键(PK) (for database)
9.排序(数字,字母由大到小)
select bsf.* from blis_bstoffermigplan bsf order by bs***rdertypeid 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 (se***stoffermigplan.nextval, ?, ?, ?, ?, ?,?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?,sysdate, ?, sysdate)
11,更新一条记录
update of***migplan.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(ac***astmodifieddate,'YYYYMMDD')>'20050101'
select * from blis_account acc where ac***astmodifieddate>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***okingvalue,0) bookingvalue from blis_order o, blis_orderbooking ob where o.***erid=ob.orderid and o.***erid =125034 and ob***okingtypeid = 215 and ob***atus = 'Active'
这里关心nvl的用法,nvl(arg,value)代表如果前面的arg的值为null那么返回的值为后面的value
16.知道一个column的名字,但不清楚它属于哪张table时,可以使用
select * from user_col_comments ucc where uc***olumn_name = 'column_name'
比如: select * from user_col_comments ucc where uc***olumn_name = ‘ORDERID’ 就会查出一系列有ORDERID字段的表。
17.遍历两字段排列
select (pf***fername || ' '|| cur.name) offercode from blis_packageoffer pf,blis_currency cur where cu***tatus='Active' and pf***atus='Active'
结果如下:
offercode
a1 b1
a1 b2
a2 b1
a2 b2
18.条件判断
case when pc***ovisioningby = 'BPS' then 'True'
else 'False' end
select so***osorderserviceid, st***sprovisionticketid,
(case when pc***ovisioningby = 'BPS' then 'True'
else 'False' end) isConnector
from blis_sosprovisionticket st, blis_sosorderform sof,
blis_sosorderservice sos, blis_packagecomponent pc
where so***osorderformid = so***osorderformid
and so***osorderserviceid = st***sorderserviceid
and so***tatus = 'Active' and st***atus = 'Active'
and pc***gname(+) = st***rvicetag and pc***ovisioningby
and so***osorderformid = 104789
19. pc***gname(+) =st***rvicetag
当pc***gname存在值,st***rvicetag不存在值的话,记录也可以检索出来。
20.让表可以手工编辑
select rowid,st.* from blis_sosprovisionticket st where st***sprovisionticketid=102508
用cl***es12.zip 还是会抛出classNotFoundException:or***e.jdbc.driver.OracleDriver,换用cl***12.jar就正常了,cl***es12.zip 或cl***12.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 fi***d:1.dmp
连接ORACLE数据库的字符串格式是
jdbc:oracle:thin:@主机:端口:SID
注意是SID 而不是数据库名