Oracle SQL Usage Tips Summary

1, delete the duplicate data of a table the same table_name used

DELETE
From table_name A
WHERE rowid > (SELECT min (rowid)
From table_name b
WHERE b.id = a.id and B.name=a.name);
2. Export all information of a user
Exp scott/[email protected]File=d:/scott.dmp
3, import all the information of a userImp scott/[email protected]File=d:/scott.dmp full=y; --or
Imp scott/[email protected]File=d:/scott.dmp Fromuser=scott Touser=scott
4. Export a few tables of a user, not all
Exp scott/[email protected]File=d:/scott.dmp tables= (emp,dept)
5. Import a few tables of a user, not all
Imp scott/[email protected]File=d:/scott.dmp tables= (emp,dept)
6. Give DBA authority to a user
Grant DBA to Scott;
7. Reclaim the rights of a user
Revoke DBA from Scott;
8. Delete Table space

alter Tablespace_name JXFDA offline; 
Drop tablespace tablespace_name including contents and datafiles;
9. Delete User (cascade)

Drop user user_name cascade;
10. Get a substring

Get t.ename= ' fhg1 ', get ' HG '
Select substr (t.ename,1,2) from EMP t;

11. Data batch update or insert (merge)

MERGE into table_name P-table to be modified

USING table_other O-Depends on the table (if it is more than one table, establish the view as (select ... Alias

on (p.product_id = np.product_id)

when Matched Then

UPDATE

SET p.product_name = np.product_name, p.category = np.category where ...;

When isn't matched then

Insert Values (...) where ....