テーブル問い合わせ

表領域の一覧 ORACLE

SELECT * FROM DBA_TABLESPACES

ユーザーの一覧 ORACLE

SELECT * FROM DBA_USERS

テーブルの一覧 ORACLE

SELECT * FROM ALL_TABLES

表領域の中に入っている表や索引の一覧 ORACLE

SELECT * FROM DBA_SEGMENTS

スカラー副問い合わせ ORACLE 9i以降

SELECT 　　field1, 　　field2, 　　(SELECT field3 FROM tbl2 B WHERE B.key = A.key) field3 　　FROM tbl1 A 　※単一行の結果を戻す場合に使用

EXISTS関数使用方法（一致、不一致） SQL SERVER

tbl1 と tbl2 のテーブルの key が一致したものだけを抽出 SELECT * FROM tbl1 WHERE EXISTS (SELECT * FROM tbl2 WHERE tbl1.key = tbl2.key) 不一致の場合 SELECT * FROM tbl1 WHERE NOT EXISTS (SELECT * FROM tbl2 WHERE tbl1.key = tbl2.key)

階層問い合わせ ORACLE

部門マスタの部門コードと親部門コードの親子関係から末端（フラグに1）の部門コードを抽出 SELECT * FROM 部門マスタ 　　　　WHERE フラグ=1 　　　　CONNECT BY PRIOR 部門コード = 親部門コード 　　　　START WITH 部門コード = [選択された部門コード] 　　　　ORDER BY 部門コード

システム日付の取得 ORACLE/DB2

SYSDATE （ORACLE) SQL> SELECT SYSDATE FROM dual; SYSDATE ------- 05-06-29 　　日付型(24時間)：'YYYY-MM-DD HH24:MI:SS' CURRENT DATE （DB2） SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1 　　TIMESTAMP ('2002-10-20-12.00.00.000000') 　　TIMESTAMP ('2002-10-20 12:00:00') 　　DATE ('2002-10-20') 　　DATE ('10/20/2002') 　　TIME ('12:00:00') 　　TIME ('12.00.00')

文字型(日付)を日付型に変換 ORACLE

TO_DATE()関数 SQL> SELECT TO_DATE('20050518','YYYYMMDD') FROM dual; TO_DATE('20050518','YYYYMMDD') ------------------------------ 05-05-18 SQL> SELECT TO_DATE('2005-05-18','YYYY-MM-DD') FROM dual; TO_DATE('2005-05-18','YYYY-MM-DD') ---------------------------------- 05-15-18

日付型を文字列に変換 ORACLE

TO_CHAR()関数 SQL> SELECT TO_CHAR(SYSDATE,'MM/DD') FROM dual; TO_CHAR(SYSDATE,'MM/DD') ------------------------ 02/28

日付の加算、減算 ORACLE/SQL SERVER

[ORACLE] ADD_MONTHS(日付,加算される値) ＜例＞現在日付から6ヶ月前と一致する日付を求める SELECT * FROM tbl1 WHERE tbl1.日付 = add_months(sysdate,-6) [SQL Server] DATEADD(日付の要素,加算される値,日付) ＜例＞現在日付から6ヶ月前と一致する日付を求める SELECT * FROM tbl1 WHERE tbl1.日付 = DATEADD(Month,-6,getdate())

データ型の変換 SQL SERVER

日付型から文字型への変換 CONVERT(データ型,データ値,文字列形式) ＜例＞2004/11/02が格納されている @mydate を 20041102 の文字列に変換 SELECT * FROM tbl1 WHERE tbl1.日付 = CONVERT(char(8),@mydate,112)

文字列の置換 ORACLE

REPLACE(文字列,置換対象文字列,置換文字列) ＜例＞文字列 '123456789' から '5' の文字列を 'a' に置換 　結果：'1234a6789' SQL> SELECT REPLACE('123456789','5','a') FROM dual; REPLACE('123456789','5','a') ---------------------------- 1234a6789

文字列の左に文字を埋める ORACLE

LPAD(文字列,切り出し指定数,埋める文字) ＜例＞文字列 'aaa' に 前に'*'を埋め 5バイト切り出す 　結果：'**aaa' SQL> SELECT LPAD('aaa',5,'*') FROM dual; LPAD('aaa',5'*') ---------------- **aaa 空白の埋めの場合は埋める文字を省略する

文字列の右に文字を埋める ORACLE

RPAD(文字列,切り出し指定数,埋める文字) ＜例＞文字列 'aaa' に 後に'*'を埋め 5バイト切り出す 　結果：'aaa**' SQL> SELECT RPAD('aaa',5,'*') FROM dual; RPAD('aaa',5'*') ---------------- aaa** 空白の埋めの場合は埋める文字を省略する

文字列の左側にある空白を削除する ORACLE

LTRIM(文字列) ＜例＞文字列 ' 1' の前の空白を削除する 　結果：'1' SQL> SELECT LTRIM('　　　　　1') FROM dual; LTRIM('　　　　　1') -------------------- 1

文字列の右側にある空白を削除する ORACLE

RTRIM(文字列) ＜例＞文字列 '1　　　　　' の後の空白を削除する 　結果：'1' SQL> SELECT RTRIM('1　　　　　') FROM dual; LTRIM('1　　　　　') -------------------- 1

文字列の検索 ORACLE

INSTR(文字列,検索文字列,開始位置,検索HIT番号) ＜例1＞文字列 'aaa0bbb0ccc' の1文字目から 0 を検索し最初HITしたその位置を返す SQL> SELECT INSTR('aaa0bbb0ccc',0,1,1) FROM dual; INSTR('aaa0bbb0ccc',0,1,1) --------------------------- 4 ＜例2＞文字列 'aaa0bbb0ccc' の2文字目から 0 を検索し2回目にHITしたその位置を返す SQL> SELECT INSTR('aaa0bbb0ccc',0,2,2) FROM dual; INSTR('aaa0bbb0ccc',0,2,2) --------------------------- 8 ＜例3＞文字列 'aaa0bbb0ccc' の後ろから 0 を検索し最初にHITしたその位置を返す（開始位置が負の場合は後から検索） SQL> SELECT INSTR('aaa0bbb0ccc',0,-1,1) FROM dual; INSTR('aaa0bbb0ccc',0,-1,1) ---------------------------- 8

文字列の切り出し ORACLE

SUBSTR(文字列,開始位置,対象文字数) ＜例1＞文字列 'aaa0bbb0ccc' の5文字目から3文字分を切り出す SQL> SELECT SUBSTR('aaa0bbb0ccc',5,3) FROM dual; SUBSTR('aaa0bbb0ccc',5,3) -------------------------- bbb ＜例2＞開始位置以降を全て取り出す場合は対象文字数を省略できる SQL> SELECT SUBSTR('aaa0bbb0ccc',5) FROM dual; SUBSTR('aaa0bbb0ccc',5) ------------------------ bbb0ccc

除算した余りの取得 ORACLE

MOD(分子,分母) ＜例＞3 を 2 で除算した余り 1 SQL> SELECT MOD(3,2) FROM dual; 　MOD(3,2) ---------- 　　　　1

小数点以下の切捨て ORACLE

TRUNC(数値[,桁]) ※桁を省略すると少数点以下が切り捨てられる ＜例＞6.666666 を 少数点第 2 位で切り捨てる SQL> SELECT TRUNC(6.666666,2) FROM dual; TRUNC(6.666666,2) ----------------- 　　　　　　6.66

SELECT(問い合わせ)内で条件分岐(CASE)の結果に基づいて値を置換する方法 ORACLE/SQL SERVER

[SQL Server] パターン�@ 　SELECT number = 　　　CASE field1 　　　　　WHEN 'A' THEN 1 　　　　　WHEN 'B' THEN 2 　　　　　ELSE 3 　　　END 　FROM tbl1 　説明：tbl1 のテーブル内の field1 の値が A の場合は number に 1 を B の場合は 2 を それ以外は 3 を代入している パターン�A 　SELECT number = 　　　CASE 　　　　　WHEN field1 = 'A' THEN 1 　　　　　WHEN field1 = 'B' THEN 2 　　　　　ELSE 3 　　　END 　FROM tbl1 　説明：パターン�@と同様の結果が得られる。（�@では等しいかどうかの比較判定のみ、�Aではさまざまな比較判定も可能） [ORACLE] 　SELECT 　　　(CASE 　　　　　WHEN field1 = 'A' THEN 1 　　　　　WHEN field1 = 'B' THEN 2 　　　　　ELSE 3 　　　END) number 　FROM tbl1

SELECT(問い合わせ)内で条件分岐(DECODE)の結果に基づいて値を置換する方法 ORACLE

DECODE(文字列,条件1,戻り値1,[条件2,戻り値2,]...[初期値]) ＜例＞tbl1 のテーブル内の field1 の値が A の場合は number に 1 を B の場合は 2 を それ以外は 3 を代入している SELECT DECODE(field1,'A',1,'B',2,3) AS number1 FROM tbl1 DECODEでカウントを取る方法 ＜例＞field1の値がAの場合に1を代入し加算することによりAの件数を取得している Bの件数も同様である SELECT SUM(DECODE(field1,'A',1,0) AS "Aの件数", 　　　　SUM(DECODE(field1,'B',1,0) AS "Bの件数" FROM tbl1 DECODEとSIGN関数の複合 ＜例＞SIGN関数は受け取った数値がプラスなら1 マイナスなら-1 0なら0を返す 　　　　　DECODEと一緒に使用することにより大小比較が可能　例ではAとBを比較している SELECT DECODE(SIGN( A - B ),1,'Aが大きい',-1,'Bが大きい','同じ') FROM tbl1

ROWIDによる問い合わせ ORACLE

ROWIDを扱うパッケージの中の行番号を調べる SELECT ROWID, DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) FROM tbl1 　DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)に行番号が格納されている

ROWNUMによる問い合わせ(先頭レコードからxx件問い合わせ方法) ORACLE

先頭レコードから 10 件問い合わせるには SELECT * FROM tbl1 WHERE ROWNUM <= 10 　※　ORDER BY 句がある場合 ROWNUMの順序が変わってしまう為注意が必要 　　　また、先頭レコードを含まない抽出は処理できない WHERE ROWNUM = 3 とかは×

問い合わせでの行ロック方法 ORACLE

FOR UPDATE句を使用 SELECT * FROM tbl1 WHERE key = 1 FOR UPDATE ロックされていたら待たずにエラーを返す場合 SELECT * FROM tbl1 WHERE key = 1 FOR UPDATE NOWAIT

集合問い合わせ ORACLE

UNIONを使用(2つの問合せの結果を1つに結合) tbl1とtbl2を結合しています。重複レコードは含めない。 ※各問合せで選択される列の数とデータ型は同じでなければならない SELECT * FROM tbl1 UNION SELECT * FROM tbl2 UNION ALLを使用(2つの問合せの結果を1つに結合) tbl1とtbl2を結合しています。重複レコードも含める。 ※各問合せで選択される列の数とデータ型は同じでなければならない SELECT * FROM tbl1 UNION ALL SELECT * FROM tbl2

集合差問い合わせ ORACLE 9i以降

MINUSを使用(2つの問合せの結果を1つに結合) tbl1とtbl2を結合しています。重複レコードは含めない。 ※各問合せで選択される列の数とデータ型は同じでなければならない SELECT * FROM tbl1 MINUS SELECT * FROM tbl2

集合結合問い合わせ ORACLE 9i以降

INTERSECTを使用(両方の問い合わせに共通するレコードを返す) SELECT * FROM tbl1 INTERSECT SELECT * FROM tbl2 EXCEPTを使用(両方の問い合わせに共通しないレコードを返す) SELECT * FROM tbl1 EXCEPT SELECT * FROM tbl2

改行コードの抽出

SELECT * FROM tbl1 WHERE fields1 like '%' || chr(13) || '%'

テーブル定義のCREATE TABLE文の表示 ORACLE

DBMS_METADATAパッケージを使用 SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','TBL1') FROM DUAL; DBMS_METADATA.GET_DDL('TABLE','TBL1') -------------------------------------------------------------- 　CREATE TABLE "FANTASISTA"."TBL1" 　（　　"FIELD1"　 NOT NULL ENABL

CREATE文のファイル出力 ORACLE

下記のスクリプトを作成しSQL PLUSで実行 ・テーブルのファイル出力 SET LONG 32000 SET PAGESIZE 15000 SET LINESIZE 1000 SET HEADING OFF SET TRIMSPOOL ON SPOOL C:\temp\出力ファイル名.sql SELECT DBMS_METADATA.GET_DDL('TABLE',a.TABLE_NAME,a.OWNER) || ';' FROM DBA_ALL_TABLES a WHERE a.OWNER = スキーマ名; SPOOL OFF ・インデックスのファイル出力 SET LONG 32000 SET PAGESIZE 15000 SET LINESIZE 1000 SET HEADING OFF SET TRIMSPOOL ON SPOOL C:\temp\出力ファイル名.sql SELECT DBMS_METADATA.GET_DDL('INDEX',a.INDEX_NAME,a.OWNER) || ';' FROM DBA_INDEXES a WHERE a.OWNER = スキーマ名; SPOOL OFF ・シーケンスのファイル出力 SET LONG 32000 SET PAGESIZE 15000 SET LINESIZE 1000 SET HEADING OFF SET TRIMSPOOL ON SPOOL C:\temp\出力ファイル名.sql SELECT DBMS_METADATA.GET_DDL('SEQUENCE',a.SEQUENCE_NAME,a.SEQUENCE_OWNER) || ';' FROM DBA_SEQUENCES a WHERE a.SEQUENCE_OWNER = スキーマ名; SPOOL OFF

PL/SQL文のファイル出力 ORACLE

下記のスクリプトを作成しSQL PLUSで実行 ・PL/SQLのファイル出力 SET DEFINE ON SET VERIFY OFF SET PAGESIZE 0 SET FEEDBACK OFF SET TRIMSPOOL ON SET HEADING OFF COL TEXT FORMAT a4000 SET LINE 4000 SET ESCAPE ON SPOOL C:\temp\出力ファイル名.sql SELECT 　　CASE 　　WHEN UPPER(TEXT) LIKE 'PACKAG%' || UPPER(NAME) || '%' THEN 'CREATE OR REPLACE ' || TEXT 　　WHEN UPPER(TEXT) LIKE 'END%' || UPPER(NAME) || '%' THEN TEXT || CHR(13) || '/' 　　ELSE TEXT 　　END AS TEXT FROM 　　DBA_SOURCE WHERE 　　OWNER = 'スキーマ名' 　　NAME = 'PL/SQL名' ORDER BY 　　OWNER, 　　NAME, 　　TYPE, 　　LINE ; SPOOL OFF SET PAGESIZE 24 SET FEEDBACK ON SET HEADING ON SET LINE 100 SET ESCAPE OFF

SGAの確認 ORACLE

SELECT * FROM v$sga;

コメントの確認 ORACLE

SELECT 　　c.COMMENTS テーブル論理名, 　　a.TABLE_NAME テーブル物理名, 　　a.COMMENTS カラム論理名, 　　a.COLUMN_NAME カラム物理名 FROM 　　USER_COL_COMMENTS a, 　　USER_TAB_COLUMNS b, 　　USER_TAB_COMMENTS a WHERE 　　a.TABLE_NAME = b.TABLE_NAME 　　a.TABLE_NAME = c.TABLE_NAME 　　a.TABLE_NAME = 'テーブル名' 　　a.COLUMN_NAME = b.COLUMN_NAME ORDER BY 　　a.TABLE_NAME,b.COLUMN_ID

初期化パラメータの確認 ORACLE

SELECT * FROM v$parameter2;

ソースの表示 ORACLE

SELECT text FROM all_source WHERE name = 'パッケージ名' AND OWNER = 'スキーマ名' ORDER BY LINE;

バージョン確認 ORACLE/SQL Server

[ORACLE] SELECT * FROM v$version; [SQL Server] SELECT @@version

シーケンステーブルの確認 DB2

SELECT * FROM SYSCAT.SEQUENCES

現在のシーケンス番号の確認 DB2

SELECT * FROM SYSIBM.SYSSEQUENCES ※ただし一度NEXTVALしないと表示されない（NEXTVALを行うとシーケンス番号を次の番号に更新してしまう） 　VALUES NEXTVAL FOR シーケンステーブル名

シノニムの一覧表示 ORACLE

ユーザ管理のシノニム SELECT * FROM USER_SYNONYMS パブリックシノニムの一覧表示 SELECT * FROM DBA_SYNONYMS WHERE OWNER = 'PUBLIC' AND NOT TABLE_OWNER IN ('SYS', 'SYSMAN', 'SYSTEM','WMSYS', 'EXFSYS', 'ORDSYS','MDSYS', 'XDB');

使用しているテーブルの件数表示 ORACLE

使用しているテーブルの件数を出力するSELECT文 SELECT 　　table_name, 　　TO_NUMBER ( 　　　　EXTRACTVALUE ( 　　　　　　xmltype( 　　　　　　　　dbms_xmlgen.getxml('SELECT COUNT(*) AS C FROM '||table_name) 　　　　　　), 　　　　　　'/ROWSET/ROW/C' 　　　　) 　　) AS count FROM user_tables

Enterprise Managerと同様の表領域確認 ORACLE

Enterprise Managerと同様の表領域確認 SELECT 　　d.tablespace_name AS "表領域名", 　　nowspace AS "サイズ(MB)", 　　ROUND(nowspace-freespace) AS "使用量(MB)", 　　ROUND((1-freespace/nowspace)*100) AS "使用率(%)", 　　freespace AS "空容量(MB)" FROM 　　(SELECT tablespace_name,ROUND(SUM(bytes)/(1024*1024)) AS nowspace 　　　FROM dba_data_files GROUP BY tablespace_name) d, 　　(SELECT tablespace_name,ROUND(SUM(bytes)/(1024*1024)) AS freespace 　　　FROM dba_free_space GROUP BY tablespace_name) f WHERE 　　d.tablespace_name = f.tablespace_name