185
Oracle必會的基礎SQL-管理維護
1.查詢當前數據庫誰在運行什麼sql
SELECT OSUSER, USERNAME, SQL_TEXT FROM V$SESSION A, V$SQLTEXT B WHERE A.SQL_ADDRESS =B.ADDRESS ORDER BY ADDRESS, PIECE;
2.如何查詢和刪除重複記錄？
DELETE FROM TABLE_NAME T1
WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME T2
WHERE T1.COL1= T2.COL1 AND T1.COL2= T2.COL2);
3.如何用BBB表的數據去更新AAA表數據 (有關聯的字段)
UPDATE AAA SET BNS_SNM=(SELECT BNS_SNM FROM BBB WHERE AAA.DPT_NO=BBB.DPT_NO) WHERE EXISTS (SELECT BNS_SNM FROM BBB WHERE AAA.DPT_NO=BBB.DPT_NO) ;
ORA-28000：the account is locked
4.關於Rownum
(1)仔細閱讀下麵三條命令,猜想其結果
Select * from users where rownum>0;
Select * from users where rownum>1;
Select * from users where rownum<2;
猜想 根據時間倒序查詢10筆訂單
select * from ord_order b where rownum<11 order by create_time desc ;
select * from (select * from ord_order b order by create_time desc ) where rownum<11 ;
以上2條sql語句得到的數據一樣嗎？哪個數據是符合要求的。
(2)rownum的值來源原理
1 Oracle executes your query.
2 Oracle fetches the first row and calls it row number 1.
3 Have we gotten past row number meets the criteria? If no, then Oracle discards the row, If yes, then Oracle return the row.
4 Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).
5 Go to step
(3). 總結：因為ROWNUM是對結果集加的一 個偽列，即先查到結果集之後再加上去的一個列 （強調：先要有結果集）。簡單的說rownum是對符合條件結果的序列號。它總是從1開始排起的。所以你選出的結果不可能沒有1，而有其他大於1的值。
最後更新：2017-04-02 16:47:46