SQL syntax summary
1. Read fields based on conditions and define a field to take only a few specified values or one value.
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. Remove duplicate (different) fields 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. A field cannot be 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 records that meet a certain condition
delete from blis_bstoffermigplan bsf where bsf.keyid='110206'
5. Take the name_a field and put the field alias name_b
select bsf.keyid subcode,bsf.bstoffermigplanid from blis_bstoffermigplan bsf
The keyid field is taken here, and the subcode field is displayed.
6.connection rollback commit
Rollback is to cancel the behavior done in memory and not persist it to the database. Commit is to persist the behavior done in memory to the database.
7. Use Dual in Oracle. Dual is a unique virtual table of Oracle. The sequence of many systems in Oracle generally establishes a one-to-one correspondence with the table. However, if you want to insert it programmatically, you must manually specify it, such as adding Account data, the corresponding value is inserted into SEQ_ACCOUNT.nextval, variables and functions can be obtained through Dual
S: select getdate();
O: select sysdate from dual;
select SEQ_INTEGRATIONTASK.NEXTVAL from DUAL
8. (PK) Primary key (PK) (for database)
9. Sort (numbers, letters from large to small)
select bsf.* from blis_bstoffermigplan bsf order by bsf.ordertypeid desc
10.Insert a record
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 a record
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. Insert type clob with a large amount of data
13. Query date
The following two statements have the same function
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')
14Find duplicate records based on a value in a field
For example, find the blis_usageaccess records with the same chargeactivationid value.
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. Obtain the total value of a certain field through query. If this value is null, a preset default value will be given.
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'
Here we are concerned about the usage of nvl. nvl (arg, value) means that if the value of the previous arg is null, the returned value is the following value.
16. You can use this method when you know the name of a column but don’t know which table it belongs to.
select * from user_col_comments ucc where ucc.column_name = 'column_name'
For example: select * from user_col_comments ucc where ucc.column_name = 'ORDERID' will find a series of tables with ORDERID fields.
17. Traverse two fields and arrange them
select (pf.offername || ' '|| cur.name) offercode from blis_packageoffer pf,blis_currency cur where cur.status='Active' and pf.status='Active'
The result is as follows:
offercode
a1 b1
a1 b2
a2 b1
a2 b2
18.Conditional judgment
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
When the value of pc.tagname exists and the value of st.servicetag does not exist, the record can also be retrieved.
20. Make the table manually editable
select rowid,st.* from blis_sosprovisionticket st where st.sosprovisionticketid=102508
Using classes12.zip will still throw classNotFoundException: oracle.jdbc.driver.OracleDriver. It will be normal if you use class12.jar. classes12.zip or class12.jar is the JDBC oracle driver class.
Create database:
View all tables: select * from dba_all_tables
View all users: select * from all_users
View all DBA users:select * from dba_users
Create role:create role BLIS_ADMIN_ROLE;
Create a new user: create user username identified by password
Grant table space usage rights: grant resource to username
Grant create table permission: grant create table to username
Grant permission to connect to the database: grant create session to username
View all tablespaces: select * from dba_tablespaces
Grant any table to a user: grant create any table to BLIS_ADMIN_ROLE;
Grant search capabilities to a user: grant create any index to BLIS_ADMIN_ROLE;
Grant a user the ability to retrieve, insert, update, and delete a table: grant select, insert, update, delete on BLIS_ACCAGENCYCOMMISSION to BLIS_ADMIN_ROLE;
Export database: For example: exp blis/blis@dbls full=y file=d:1.dmp
The string format for connecting to the ORACLE database is
jdbc:oracle:thin:@host:port:SID
Note that it is the SID and not the database name.