用sql語(yǔ)句查詢(xún)前十條記錄VIP

1、用sql語(yǔ)句查詢(xún)前十條記錄篇一：SQL查詢(xún)前10條記錄(SqlServermysqloracle)語(yǔ)法分析 SQL查詢(xún)前10條記錄（SqlServer/mysql/oracle）語(yǔ)法分析 Sql Server : Sql代碼 select top X * from table_name -查詢(xún)前X條記錄，可以改成需要的數(shù)字。 select top n * from (select top m * from table_name order by column_name ) a order by column_namedesc -查詢(xún)第N到M條記錄。常用的分頁(yè)也是這種方式。 例如常用的分頁(yè)方式：

2、declare page int declare row int set page=2 -頁(yè)數(shù) set row=3 -每頁(yè)展示行數(shù) select top (row) * from (select top (row*page) * from table_name order by id ) a order by iddesc -最基本的分頁(yè)方式，改變r(jià)ow和page達(dá)到分頁(yè)效果 MYSQL查詢(xún)前10條的方法(limit參數(shù)的第一個(gè)參數(shù)n（如下面的0或1）表示前n條記錄是不在選擇范圍內(nèi)，相當(dāng)于hibernate的setFirstResult的參數(shù)加一；第二個(gè)參數(shù)表示要選擇的記錄條數(shù)，相當(dāng)于hibe

3、rnate的setMaxResult的參數(shù))：Sql代碼 select * from table_name limit 0,10 -通常0是可以省略的，直接寫(xiě)成 limit 10。0代表從第0條記錄后面開(kāi)始，也就是從第一條開(kāi)始 select * from table_name limit 1,10 -則為從第一條后面的記錄開(kāi)始展示，也就是說(shuō)從 第二條開(kāi)始。 MySQL查詢(xún)前5條方法(假設(shè)一個(gè)表為users表，這里作為子查詢(xún)時(shí)要加入別名不然會(huì)出錯(cuò)，還有一點(diǎn)要注意的是order by在limit前面，不然還沒(méi)出結(jié)果前就說(shuō)要前幾條，怎么搞嘛，所以limit要放最后。)：Sql代碼 select *

4、from (select * from users order by id limit 0 , 10) partOfUsers order by id desc limit 0 , 5;ORACLE查詢(xún)前十條的方法：Sql代碼 select * from table_name where rownum X -X為前多少條記錄 select * from (select a.*,a.rownum rn from （select * from table_name） a where a.rownum M)where rn n -這句就是從n到m也的數(shù)據(jù)，分為三層結(jié)構(gòu)篇二：SQL語(yǔ)句大全查看表空間

5、數(shù)據(jù)庫(kù)各個(gè)表空間增長(zhǎng)情況的檢查：SQL SELECT A.TABLESPACE_NAME,( 1 -(A.TOTAL)/B.TOTAL)* 100 USED_PERCENT FROM (SELECT TABLESPACE_NAME,SUM (BYTES) TOTAL FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) A,(SELECT TABLESPACE_NAME,SUM (BYTES) TOTAL FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME=B.TA

6、BLESPACE_NAME; SQL SELECT UPPER(F.TABLESPACE_NAME) 表空間名 , D.TOT_GROOTTE_MB 表空間大小 (M) , D.TOT_GROOTTE_MB - F.TOTAL_BYTES 已使用空間 (M) , TO_CHAR(ROUND(D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100 , 2 ), 990.99 ) 使用比 ,F.TOTAL_BYTES 空閑空間 (M) , F.MAX_BYTES 最大塊 (M) FROM (SELECT TABLESPACE_NAME,

7、 ROUND(SUM (BYTES) / ( 1024 * 1024 ), 2 ) TOTAL_BYTES, ROUND(MAX (BYTES) / ( 1024 * 1024 ), 2 ) MAX_BYTES FROM SYS .DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME,ROUND(SUM (DD.BYTES) / ( 1024 * 1024 ), 2 ) TOT_GROOTTE_MB FROM SYS .DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAM

8、E) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC ; 查看各個(gè)表空間占用磁盤(pán)情況 : SQL COL TABLESPACE_NAME FORMAT A20; SQL SELECT B.FILE_ID FILE_ID, B.TABLESPACE_NAME TABLESPACE_NAME, B.BYTES BYTES, (B.BYTES-SUM (NVL(A.BYTES, 0 ) USED, SUM (NVL(A.BYTES, 0 ) FREE, SUM (NVL(A.BYTES, 0 )/(B.BYTES)* 100

9、 PERCENT FROM DBA_FREE_SPACE A,DBA_DATA_FILES B WHERE A.FILE_ID=B.FILE_ID GROUP BY B.TABLESPACE_NAME,B.FILE_ID,B.BYTES ORDER BY B.FILE_ID; 數(shù)據(jù)庫(kù)對(duì)象下一擴(kuò)展與表空間的 free 擴(kuò)展值的檢查：SQL SELECT A.TABLE_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME FROM ALL_TABLES A,(SELECT TABLESPACE_NAME, MAX (BYTES) AS BIG_CHUNK FROM DBA

10、_FREE_SPACE GROUP BY TABLESPACE_NAME ) F WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME AND A.NEXT_EXTENT F.BIG_CHUNK UNION SELECT A.INDEX_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME FROM ALL_INDEXES A,(SELECT TABLESPACE_NAME, MAX (BYTES) AS BIG_CHUNK FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F WHERE F

11、.TABLESPACE_NAME = A.TABLESPACE_NAME AND A.NEXT_EXTENT F.BIG_CHUNK; Disk Read 最高的 SQL 語(yǔ)句的獲?。篠QL SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS) WHERE ROWNUM = 5 ; 查找前十條性能差的 sql SELECT * FROM (SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS, SQL_TEXT FROM V$SQLAREA

12、ORDER BY DISK_READS DESC ) WHERE ROWNUM 10 ; 等待時(shí)間最多的 5 個(gè)系統(tǒng)等待事件的獲?。篠QL SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE SQL% ORDER BY TOTAL_WAITS DESC ) WHERE ROWNUM = 5 ; 查看當(dāng)前等待事件的會(huì)話 : COL USERNAME FORMAT A10 SET LINE 120 COL EVENT FORMAT A30 SELECT SE.SID,S.USERNAME,SE.EVENT,SE.TOTA

13、L_WAITS,SE.TIME_WAITED,SE.AVERAGE_WAIT FROM V$SESSION S,V$SESSION_EVENT SE WHERE S.USERNAME IS NOT NULL AND SE.SID=S.SID AND S.STATUS= ACTIVE AND SE.EVENT NOT LIKE %SQL*NET% SELECT SID, EVENT, P1, P2, P3, WAIT_TIME, SECONDS_IN_WAIT, STATE FROM V$SESSION_WAIT WHERE EVENT NOT LIKE %MESSAGE% AND EVENT

14、NOT LIKE SQL*NET% AND EVENT NOT LIKE %TIMER% AND EVENT != WAKEUP TIME MANAGER 找到與所連接的會(huì)話有關(guān)的當(dāng)前等待事件：SELECT SW.SID,S.USERNAME,SW.EVENT,SW.WAIT_TIME,SW.STATE,SW.SECONDS_IN_WAIT SEC_IN_WAIT FROM V$SESSION S,V$SESSION_WAIT SW WHERE S.USERNAME IS NOT NULL AND SW.SID=S.SID AND SW.EVENT NOT LIKE %SQL*NET% ORD

15、ER BY SW.WAIT_TIME DESC ; Oracle 所有回滾段狀態(tài)的檢查：SQL SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,DBA_ROLLBACK_SEGS.STATUS FROM DBA_ROLLBACK_SEGS,V$DATAFILE WHERE FILE_ID=FILE #; Oracle 回滾段擴(kuò)展信息的檢查 : COL NAME FORMAT A10 SET LINESIZE 140 SELECT SUBSTR(NAME , 1 , 40 ) NAME ,EXTENTS ,

16、RSSIZE,OPTSIZE,AVEACTIVE,EXTENDS,WRAPS,SHRINKS,HWMSIZE FROM V$ROLLNAME RN,V$ROLLSTAT RS WHERE (RN.USN=RS.USN); EXTENTS : 回滾段中的盤(pán)區(qū)數(shù)量。 Rssize: 以字節(jié)為單位的回滾段的尺寸。 optsize ：為 optimal 參數(shù)設(shè)定的值。 Aveactive: 從回滾段中刪除盤(pán)區(qū)時(shí)釋放的以字節(jié)為單位的平均空間的大小。 Extends: 系統(tǒng)為回滾段增加的盤(pán)區(qū)的次數(shù)。 Shrinks: 系統(tǒng)從回滾段中清除盤(pán)區(qū)（即回滾段收縮）的次數(shù)。回滾段每次清除盤(pán)區(qū)時(shí)，系統(tǒng)可能會(huì)從這個(gè)回滾

17、段中消除一個(gè)或多個(gè)盤(pán)區(qū)。 Hwmsize: 回滾段尺寸的上限，即回滾段曾經(jīng)達(dá)到的最大尺寸。 ( 如果回滾段平均尺寸接近 OPTIMAL 的值，那么說(shuō)明 OPTIMAL 的值設(shè)置正確，如果回滾段動(dòng)態(tài)增長(zhǎng)次數(shù)或收縮次數(shù)很高，那么需要提高 OPTIMAL 的值 ) 查看回滾段的使用情況，哪個(gè)用戶(hù)正在使用回滾段的資源 : SELECT S.USERNAME, U.NAME FROM V$TRANSACTION T,V$ROLLSTAT R, V$ROLLNAME U,V$SESSION S WHERE S.TADDR=T.ADDR AND T.XIDUSN=R.USN AND R.USN=U.USN

18、ORDER BY S.USERNAME; 如何查看一下某個(gè) shared_server 正在忙什么 : SELECT A.USERNAME,A.MACHINE,A.PROGRAM,A.SID, A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXT FROM V$SESSION A,V$PROCESS B,V$SQLTEXT C WHERE B.SPID= 13161 AND B.ADDR=A.PADDR AND A.SQL_ADDRESS=C.ADDRESS(+) ORDER BY C.PIECE; 數(shù)據(jù)庫(kù)共享池性能檢查 : SELECT NAMESPACE,GETS,G

19、ETHITRATIO,PINS,PINHITRATIO,RELOADS,INVALIDATIONS FROM V$LIBRARYCACHE WHERE NAMESPACE IN ( SQLAREA , TABLE/PROCEDURE , BODY , TRIGGER 檢查數(shù)據(jù)重載比率 : SELECT SUM (RELOADS)/SUM (PINS)* 100 RELOAD RATIO FROM V$LIBRARYCACHE; 檢查數(shù)據(jù)字典的命中率 : SELECT 1 -SUM (GETMISSES)/SUM (GETS) DATA DICTIONARY HIT RATIO FROM V$R

20、OWCACHE; ( 對(duì)于 library cache, gethitratio 和 pinhitratio 應(yīng)該大于 90%, 對(duì)于數(shù)據(jù)重載比 率 ,reload ratio 應(yīng)該小于 1%, 對(duì)于數(shù)據(jù)字典的命中率 ,data dictionary hit ratio 應(yīng)該大于 85%) 檢查共享內(nèi)存的剩余情況 : SELECT REQUEST_MISSES, REQUEST_FAILURES FROM V$SHARED_POOL_RESERVED; ( 對(duì)于共享內(nèi)存的剩余情況 , request_misses 和 request_failures 應(yīng)該接近 0)數(shù)據(jù)高速緩沖區(qū)性能檢查 :

21、SELECT 1 -P.VALUE /(B.VALUE +C.VALUE ) DB BUFFER CACHE HIT RATIO FROM V$SYSSTAT P,V$SYSSTAT B,V$SYSSTAT C WHERE P.NAME = PHYSICAL READS AND B.NAME = DB BLOCK GETS AND C.NAME = CONSISTENT GETS 檢查 buffer pool HIT_RATIO 執(zhí)行 SELECT NAME , (PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS) MISS_HIT_RATIO FRO

22、M V$BUFFER_POOL_STATISTICS WHERE (DB_BLOCK_GETS+ CONSISTENT_GETS) ( 正常時(shí) db buffer cache hit ratio 應(yīng)該大于 90%, 正常時(shí) buffer pool MISS_HIT_RATIO 應(yīng)該小于 10%) 數(shù)據(jù)庫(kù)回滾段性能檢查 : 檢查 Ratio 執(zhí)行 SELECT SUM (WAITS)* 100 /SUM (GETS) RATIO , SUM (WAITS) WAITS , SUM (GETS) GETS FROM V$ROLLSTAT; 檢查 count/value 執(zhí)行 : SELECT CL

23、ASS ,COUNT FROM V$WAITSTAT WHERE CLASS LIKE %UNDO% SELECT VALUE FROM V$SYSSTAT WHERE NAME = CONSISTENT GETS ( 兩者的 value 值相除 ) 檢查 average_wait 執(zhí)行 : SELECT EVENT,TOTAL_WAITS,TIME_WAITED,AVERAGE_WAIT FROM V$SYSTEM_EVENT WHERE EVENT LIKE %UNDO% 檢查 RBS header get ratio 執(zhí)行 : SELECT N.NAME ,S.USN,S.WRAPS,

24、DECODE(S.WAITS, 0 , 1 , 1 - S.WAITS/S.GETS) RBS HEADER GET RATIO FROM V$ROLLSTAT S,V$ROLLNAME N WHERE S.USN=N.USN; ( 正常時(shí) Ratio 應(yīng)該小于 1%, count/value 應(yīng)該小于 0.01%,average_wait 最好為 0 ，該值越小越好 ,RBS header get ratio 應(yīng)該大于 95%) 殺會(huì)話的腳本 : SELECT A.SID,B.SPID,A.SERIAL#,A.LOCKWAIT,A.USERNAME,A.OSUSER,A.LOGON_TIME

25、,A.LAST_CALL_ET/ 3600 LAST_HOUR,A.STATUS, ORAKILL |SID| |SPID HOST_COMMAND, ALTER SYSTEM KILL SESSION |A.SID| , |A.SERIAL#| SQL_COMMAND FROM V$SESSION A,V$PROCESS B WHERE A.PADDR=B.ADDR AND SID 查看排序段的性能 : SQL SELECT NAME , VALUE FROM V$SYSSTAT WHERE NAME IN ( SORTS (MEMORY) , SORTS (DISK) 7 、查看數(shù)據(jù)庫(kù)庫(kù)對(duì)

26、象 : SELECT OWNER, OBJECT_TYPE, STATUS, COUNT (*) COUNT # FROM ALL_OBJECTS GROUP BY OWNER,OBJECT_TYPE, STATUS; 8 、查看數(shù)據(jù)庫(kù)的版本 : SELECT * FROM V$VERSION; 9 、查看數(shù)據(jù)庫(kù)的創(chuàng)建日期和歸檔方式 : SELECT CREATED, LOG_MODE, LOG_MODE FROM V$DATABASE; 10 、捕捉運(yùn)行很久的 SQL: COLUMN USERNAME FORMAT A12 COLUMN OPNAME FORMAT A16 COLUMN PR

27、OGRESS FORMAT A8 SELECT USERNAME,SID,OPNAME,ROUND(SOFAR* 100 / TOTALWORK, 0 ) | % AS PROGRESS,TIME_REMAINING,SQL_TEXT FROM V$SESSION_LONGOPS , V$SQL WHERE TIME_REMAINING 0 AND SQL_ADDRESS=ADDRESS AND SQL_HASH_VALUE = HASH_VALUE; 11 、查看數(shù)據(jù)表的參數(shù)信息 : SELECT PARTITION_NAME, HIGH_VALUE, HIGH_VALUE_LENGTH,

28、TABLESPACE_NAME,PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT,NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, PCT_INCREASE, FREELISTS ,FREELIST_GROUPS, LOGGING , BUFFER_POOL , NUM_ROWS, BLOCKS,EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE,LAST_ANALYZED FROM DBA_TAB_PARTITIONS -WHERE T

29、ABLE_NAME = :TNAME AND TABLE_OWNER = :TOWNER ORDER BY PARTITION_POSITION; 12 、查看還沒(méi)提交的事務(wù) : SELECT * FROM V$LOCKED_OBJECT; SELECT * FROM V$TRANSACTION; 13 、查找 object 為哪些進(jìn)程所用 : SELECT P.SPID,S.SID,S.SERIAL# SERIAL_NUM,S.USERNAME USER_NAME, A.TYPE OBJECT_TYPE,S.OSUSER OS_USER_NAME,A.OWNER,A.OBJECT OBJEC

30、T_NAME,DECODE(SIGN( 48 - COMMAND), 1 , TO_CHAR(COMMAND), ACTION CODE # | TO_CHAR(COMMAND) ) ACTION, P.PROGRAM ORACLE_PROCESS,S.TERMINAL TERMINAL,S.PROGRAM PROGRAM,S.STATUS SESSION_STATUS FROM V$SESSION S, V$ACCESS A, V$PROCESS P WHERE S.PADDR = P.ADDR AND S.TYPE = USER AND A.SID = S.SID AND A.OBJECT

31、 = SUBSCRIBER_ATTR ORDER BY S.USERNAME, S.OSUSER; 14 、查看回滾段 : SQL COL NAME FORMAT A10 SQL SET LINESIZE 100 SQL SELECT ROWNUM , SYS .DBA_ROLLBACK_SEGS.SEGMENT_NAME NAME , V$ROLLSTAT.EXTENTS EXTENTS , V$ROLLSTAT.RSSIZE SIZE_IN_BYTES, V$ROLLSTAT.XACTS XACTS, V$ROLLSTAT.GETS篇三：SQL語(yǔ)句整理大全(免費(fèi)收藏) SQL語(yǔ)句整理大全(

32、免費(fèi)收藏) 如何查看某個(gè)回滾段里面，跑的什么事物或者正在執(zhí)行什么sql語(yǔ)句: SQL SELECT D.SQL_TEXT, A.NAME FROM V$ROLLNAME A, V$TRANSACTION B, V$SESSION C, V$SQLTEXT D WHERE A.USN = B.XIDUSN AND B.ADDR = C.TADDR AND C.SQL_ADDRESS = D.ADDRESS AND C.SQL_HASH_VALUE = D.HASH_VALUE AND A.USN = 1; (備注：你要看哪個(gè)，就把usn=?寫(xiě)成幾就行了) 查看控制文件: SQL SELECT *

33、 FROM V$CONTROLFILE; 查看日志文件: SQL COL MEMBER FORMAT A50 SQL SELECT * FROM V$LOGFILE; 如何查看當(dāng)前SQL*PLUS用戶(hù)的sid和serial#: SQL SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE AUDSID=USERENV( SESSIONID 如何查看當(dāng)前數(shù)據(jù)庫(kù)的字符集: SQL SELECT USERENV( LANGUAGE ) FROM DUAL; SQL SELECT USERENV( LANG ) FROM DUAL; 怎么判斷當(dāng)前正在使用何種

34、SQL優(yōu)化方式: 用EXPLAIN PLAN產(chǎn)生EXPLAIN PLAN?檢查PLAN_TABLE中ID=0的POSITION列的值 SQL SELECT DECODE(NVL(POSITION,-1),-1, RBO ,1, CBO ) FROM PLAN_TABLE WHERE ID=0; 如何查看系統(tǒng)當(dāng)前最新的SCN號(hào)：SQL SELECT MAX(KTUXESCNW * POWER(2,32) + KTUXESCNB) FROM X$KTUXE; 在ORACLE中查找TRACE文件的腳本: SQL SELECT U_DUMP.VALUE | / | INSTANCE.VALUE | _

35、ORA_ | V$PROCESS.SPID | NVL2(V$PROCESS.TRACEID, _ | V$PROCESS.TRACEID, NULL ) | .TRC TRACE FILE FROM V$PARAMETER U_DUMP CROSS JOIN V$PARAMETER INSTANCE CROSS JOIN V$PROCESS JOIN V$SESSION ON V$PROCESS.ADDR = V$SESSION.PADDR WHERE U_DUMP.NAME = USER_DUMP_DEST AND INSTANCE.NAME = INSTANCE_NAME AND V$S

36、ESSION.AUDSID=SYS_CONTEXT( USERENV , SESSIONID SQL SELECT D.VALUE | /ORA_ | P.SPID | .TRC TRACE_FILE_NAME FROM (SELECT P.SPID FROM SYS.V_$MYSTAT M,SYS.V_$SESSION S, SYS.V_$PROCESS P WHERE M.STATISTIC# = 1 AND S.SID = M.SID AND P.ADDR = S.PADDR) P,(SELECT VALUE FROM SYS.V_$PARAMETER WHERE NAME = USER

37、_DUMP_DEST ) D; 如何查看客戶(hù)端登陸的IP地址: SQL SELECT SYS_CONTEXT( USERENV , IP_ADDRESS ) FROM DUAL; 如何在生產(chǎn)數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)追蹤客戶(hù)端IP地址的觸發(fā)器：SQL CREATE OR REPLACE TRIGGER ON_LOGON_TRIGGER AFTER LOGON ON DATABASE BEGIN DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT( USERENV , IP_ADDRESS END; REM 記錄登陸信息的觸發(fā)器 (來(lái)自:WWBoth

38、Win. 博威 范文 網(wǎng):用sql語(yǔ)句查詢(xún)前十條記錄)CREATE OR REPLACE TRIGGER LOGON_HISTORY AFTER LOGON ON DATABASE -WHEN (USER= WACOS ) -ONLY FOR USER WACOS BEGIN INSERT INTO SESSION_HISTORY SELECT USERNAME,SID,SERIAL#,AUDSID,OSUSER,ACTION,SYSDATE,NULL,SYS_CONTEXT( USERENV , IP_ADDRESS ),TERMINAL,MACHINE,PROGRAM FROM V$SES

39、SION WHERE AUDSID = USERENV( SESSIONID END; 查看表空間的名稱(chēng)及大小: SQL SELECT T.TABLESPACE_NAME, ROUND(SUM(BYTES/(1024 * 1024), 0) TS_SIZE FROM DBA_TABLESPACES T, DBA_DATA_FILES D WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME GROUP BY T.TABLESPACE_NAME; 查看表空間物理文件的名稱(chēng)及大小: SQL SELECT TABLESPACE_NAME,FILE_ID,FILE_NAME,ROUND(BYTES / (1024 * 1024), 0) TOTAL_SPACE FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME; 查看回滾段名稱(chēng)及大小: SQL SELECT SEGMENT_NAME, TABLESPACE_NAME, R.STATUS, (INITIAL_EXTENT / 1024) INITIAL