SQL初心者から一歩づつ進んでみる
はじめに
業務で覚えたテクニックを記載していく。
程度の低いことかもしれないが、 後輩を抱えた時に一読してもらえるものを目指したい。
対象RDBは「mysql」「oracle」
SQLの予約語は常に大文字で記載する
一部のRDBは内部で大文字にアッパーしているらしい。
ですが、今では処理速度に対して影響がないと言われています。
私的には読み易さを重視して大文字で記載するようにしています。
検索条件で同一カラムに対しての複数の候補をシンプルに書く
WHERE ID = 'aa' OR ID = 'ab' OR ID = 'ac'
複数候補からの一致を条件とする場合にIN句を使用する。
WHERE ID IN ('aa','ab','ac')
DBビューワーでSQL整形を行う
SQLを記述する時は常にSQL整形・崩し・フォーマットを行う。
MySQL Workbenchだと ⌘+B で SQLBeauty
急にSQLの評価をする時に恥ずかしい思いをしない為にも。
レビュアーが読み易いように。
SELECT結果の表示順を自由自在に操る
ORDER BY句にCASE~WHEN~THEN~ENDを指定する。
テスト仕様書の確認項目と見比べる時などに。
テーブル結合を操る
結合キーには何種類かあるが、 JOIN テーブル名 ON 条件式 をUSINGに変更してSQLを短くする。
結合テーブルのカラム名が同一であった場合はUSINGを使用して文字数を削減することができる。
ただし、結合表内でカラムが一意となるため、alias.カラムで取得しようとするとエラーが発生する。
日付のfrom-to検索
日付カラム BETWEEN TO_DATE('2010/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2017/03/31 23:59:59','YYYY/MM/DD HH24:MI:SS')
結合テーブルからのSELECTは絞り込んでから結合する
自分が何を取得したいかを考えてからSQLを組み立てる。
適当に組んで結果が返ってくるのが遅いことなどありましたので、、、
※ ただし正規化が進んでいるテーブル同士の結合においては昨今では絞り込む必要はなさそう
NULLをどう評価するのか事前に確認する
RDB、バージョンによって振る舞いが異なるので。
暗黙の型変換を活用しない
型に従った正しい書式で行う。
暗黙の型変換はバージョンによって仕様が変わる。
テーブル内の大量データ削除
DELETEコマンドで指定行を削除する方法以外にやり方があることを知る。
テーブルからエクスポートし、その後TRUNCATE TABLEを行う。
必要レコードの抽出を指定しインポート。
回りくどいやり方であるが、これが早い場合が存在する。
存在すればUPDATE、しなければINSERT
javaのコーディングで下記のようにやっていたが、別の手段がある。
(1)まずはレコードの存在チェック
(2)存在チェックを元にSQLの組み立て
(3)SQLの発行
oracleの「MERGE INTO」を使えば下記のステップ
(1)SQLの組み立て
(2)SQLの発行
mysqlでは「REPLACE」コマンドで同じようなことができる。
先輩に教えたら喜んでました。
＊ 実行計画は？と聞かれましたがケースバイケースだと思いますのでこの手法が最善ということはありません
抽出するレコードINSERT文を作成する
別DBへのINSERT文をSQLで作成。
SELECT 'INSERT INTO TABLE_NAME tab INTO ('' || tab.no || '', '' || tab.name || '')' as Query FROM TABLE_NAME tab WHERE tab.no = '01';
SELECT CONCAT('INSERT INTO TABLE_NAME tab INTO ('',tab.no'','',tab.name,'')') as Query FROM TABLE_NAME tab WHERE tab.no = '01';
注意したいのは抽出元と挿入先のカラムの型
Discussion