はじめに
OSS-DB Silver取得に向けて勉強しています。
覚えにくいと感じた事項のメモ。
小さな疑問が出てくるので調べたことのメモ。Q&Aも。
SQL使ってやってみたいこと（思いつき）もメモ。
黒本＝『徹底攻略OSS-DB Silver問題集［Ver.2.0］対応』
スッキリ＝『スッキリわかるSQL入門 第2版 ドリル222問付き!』
SQL
SQL、覚えにくいと感じるもののみ、メモ。
操作する行の絞り込み（スッキリ3章）
| SQL | 機能 | 用例 | メモ | 該当 |
|---|---|---|---|---|
| （WHERE句に書けるもの） | - | - | ＝結果が必ず(TRUE)または儀(FALSE)となる条件式。 | スッキリp78 |
| （NULLは「=」や「<>」では判定できない） | - | - | 必ずIS NULL や IS NOT NULL を使って条件式を作ること。 | スッキリp83、7章p219 |
| IN演算子 | - | - | たくさんの値といっぺんに比較したいときに便利。 「式」や「副問い合わせ」と組み合わせてはじめて、その真価を発揮する。 |
スッキリp89、7章p214 |
| ANY演算子 | - | - | 同上。 | スッキリp89、7章p216 |
| ALL演算子 | - | - | 同上。 | スッキリp89、7章p216 |
| NOT IN と <>ALL | 双方同じ意味。すべての値と一致しないことを判定する演算子。 | - | - | スッキリ7章p218 |
| IN と =ANY | 双方同じ意味。いずれかの値と一致することを判定する演算子。 | - | - | スッキリ7章p218 |
検索結果の加工（スッキリ4章）
| SQL | 機能 | 用例 | メモ | 該当 |
|---|---|---|---|---|
| DISTINCT | 重複行を除外する | SELECT DISTINCT 列名 FROM テーブル名 | 位置に注意！他の修飾と異なりSELECT文の「最初」に記述する | スッキリp112 |
| ASC（省略可） | 昇順 | SELECT 列名 FROM テーブル名 ORDER BY 列名 ASC |
ORDER BY句の初期値は昇順なので、並び順(=ASC/DESC)の指定を省略すると、結果は昇順になる | スッキリp114 |
| DESC | 降順 | SELECT 列名 FROM テーブル名 ORDER BY 列名 DESC |
スッキリp112 | |
| OFFSET | 先頭から数行だけ取得する | SELECT 列名 FROM テーブル名 ORDER BY 列名 OFFSET 先頭から除外する行数 ROWS |
省略はできないので、除外せずに1件目から取得したい場合には0を指定する | スッキリp118 |
| FETCH | 先頭から数行だけ取得する | SELECT 列名 FROM テーブル名 ORDER BY 列名 FETCH NEXT 取得行数 ROWS ONLY |
FETCH句を省略すると、該当するすべての行が抽出される | スッキリp118 |
| （OFFSET-FETCH句 用例） | 出金額の高い順に3件を取得する | SELECT 費目,出金額 FROM 家計簿 ORDER BY 出金額 DESC OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY |
スッキリp119 | |
| （OFFSET-FETCH句 用例） | 3番目に高い出金額だけを取得する | SELECT 費目,出金額 FROM 家計簿 ORDER BY 出金額 DESC OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY |
OFFSET-FETCH句はORDER BY句と併用されることが多い機能だが、SQL Serverを除き、OFFSET-FETCH句だけでも使用できる。ただしその場合は、どのような並び順で返ってくるかは実行してみるまでわからない(スッキリp133コラム参照)。なので決まった順序での抽出が必要な場合は、必ずORDER BY句を使え！！ | スッキリp119 |
| （集合演算子＝ ・UION(和集合) ・EXCEPT(NIMUS)(差集合) ・INTERSECT(積集合) |
||||
| UNION | 和集合 | SELECT 文1 UNION SELECT 文2 |
【UNIONとUNITON ALLの違い】 UNIONは重複行を1行にまとめる |
スッキリp123 |
| UNION ALL | 和集合 | SELECT 文1 UNION ALL SELECT 文2 |
【UNIONとUNITON ALLの違い】 UNION ALLは重複行をすべてそのまま返す |
スッキリp123 |
| （集合演算子でORDER BY句を使うときの注意点） | ・ORDER BY句は最後のSELECT文に記述する ・列番号以外による指定(列名やASによる列名)の場合、1つめのSELECT文のものを指定する |
スッキリp126 | ||
| （差集合の考え方） | - | - | 「差集合がわからない」（過去書いたQiita） | - |
式と関数（スッキリ5章）
演算子
| 演算子 | 機能 | - | 構文 | メモ | 該当 |
|---|---|---|---|---|---|
| CASE演算子 | 値を変換する | 構文1 | CASE 評価する列や式 WHEN 値1 THEN 値1のときに返す値 (WHEN 値2 THEN 値2のときに返す値)... (ELSE デフォルト値) END |
CASEからENDまでが1つの選択列であり、条件に応じた結果に化ける。 | スッキリp145 |
| CASE演算子 | 値を変換する | 構文2 | CASE WHEN 条件1 THEN 条件1のときに返す値 (WHEN 条件2 THEN 条件2のときに返す値)... (ELSE デフォルト値) END |
・構文1と違い、CASEのすぐ後ろに列名や式を記述しない。その代わり、WHENの後ろには値ではなく条件式を記述する。 ・最初に一致したWHENが採用される。一致しないときはELSEが採用される。 ・式の結果に応じて複数のパターンの結果を得たい場合にCASE演算子の利用が有効。 |
スッキリp146 |
関数
| 関数名 | 引数 | 戻り値 | 構文 | メモ | 該当 |
|---|---|---|---|---|---|
| LENGTH | 文字列が格納された列(or式) | 文字列の長さを表す数値 | LENGTH (引数) | スッキリp148 | |
| TRIM | 左右から空白を除去した文字列 | TRIM (文字列を表す列) | 空白を除去する関数 | スッキリp152 | |
| LTRIM | 左側の空白を除去した文字列 | LTRIM (文字列を表す列) | 空白を除去する関数 | スッキリp152 | |
| RTRIM | 右側の空白を除去した文字列 | RTRIM (文字列を表す列) | 空白を除去する関数 | スッキリp152 | |
| REPLACE | 置換処理された後の文字列 | REPLACE(置換対象の文字列,置換前の部分文字列,置換後の部分文字列) | 文字列を置換する関数 | スッキリp154 | |
| SUBSTRING/SUBSTR | 抽出された部分文字列 | SUBSTRING(文字列を表す列,抽出を開始する位置,抽出する文字の数) | 文字列の一部を抽出する関数 | スッキリp154 | |
| CONCAT | 連結後の文字列 | 文字列を連結する関数 | スッキリp155 | ||
| ROUND | 四捨五入した値 | ROUND(数値を表す列,有効とする桁数) | 指定桁で四捨五入する関数 ※「有効とする桁数」に指定する値が制の場合は小数部の桁数、負の場合は整数部の桁数を表す |
スッキリp156 | |
| TRUNC | 切り捨てた値 | TRUNC (数値を表す列,有効とする桁数) | 指定桁で切り捨てる関数 ※「有効とする桁数」＝同上 |
スッキリp157 | |
| POWER | 数値を指定した回数だけ乗じた結果 | POWER (数値を表す列,何乗するかを指定する数値) | べき乗を計算する関数 | スッキリp157 | |
| CURRENT_DATE | 引数不要 | 引数不要なため、関数名の後ろに()は付記しない | 現在の日付(YYYY-MM-DD)を得る関数 | スッキリp158 | |
| CURRENT_TIME | 引数不要 | 引数不要なため、関数名の後ろに()は付記しない | 現在の時刻(HH:MM:SS)を得る関数 | スッキリp158 | |
| CAST | 変換後の値 | CAST(変換する値 AS 変換する型) | データ型を変換する関数 | スッキリ159 | |
| COALESCE | 引数のうち、最初に現れたNULLでない引数 | COALESCE(列や式1,列や式2,…) ※引数は任意の数を指定可。ただし、すべての引数は型が一致している必要がある。もしすべての引数がNULLの場合、戻り値はNULL。 |
最初に登場するNULLでない値を返す関数 | スッキリp160 | |
| （COALESCEの用例） | → | NULLを明示的に表示する | SELECT 日付,費目, COALESCE(メモ,'(メモはNULLです)') AS メモ,入金額,出金額 FROM 家計簿 |
←「もしNULLの場合はこの値(メモはNULLです)を代入してね」という使い方ができる！ | スッキリp161 |
集計とグループ化（スッキリ6章）
SELECT文の全貌（スッキリp197）
| SELECT文に記述可能な部品すべて |
|---|
| SELECT 選択列リスト |
| FROM テーブル名 |
| [WHERE 条件式] |
| [GROUP BY グループ化列名] |
| [HAVING 集計結果に対する条件式] |
| [ORDER BY 並び替え列名] |
| ※[]でくくられた修飾は任意で記述するものだが、それぞれを書ける場所は決まっている。特に、ORDER BY句はほかにどのような修飾を書いたとしても、必ず最後に記述する必要があるので要注意。 |
副問い合わせ（スッキリ7章）
| 事項 | 詳細 | - | 該当 |
|---|---|---|---|
| 副問い合わせとNULL | NOT IN 演算子は、右辺に列挙された値を不等号を使って1つひとつ比較し、すべての値と等しくないことを判定する演算子。よって、右辺に1つでもNULLが含まれると、NOT IN 演算子による比較結果はすべてNULLとなる。 逆に、IN演算子は、右辺に列挙された値を等号を使って比較していき、いずれかの値と等しければ真と判断する演算子。よって右辺にNULLが含まれていても、等しい値が1つでもあれば、結果を得ることができる。 |
スッキリp219(複数値(ベクター)の代わりに副問い合わせ) | |
| 副問い合わせの結果から確実にNULLを除外する方法 | ①【IS NOTNULL版】 副問い合わせの絞り込み条件に、IS NOT NULL 条件を含める。 |
【例】 SELECT * FROM 家計簿アーカイブ WHERE 費目 IN ( SELECT 費目 FROM 家計簿 WHERE 費目 IS NOT NULL ) ※NULLを除外する条件を追加した。 |
スッキリp220 |
| 副問い合わせの結果から確実にNULLを除外する方法 | ②【COALESCE版】 COALESCE関数を使ってNULLを別の値に置き換える。 |
【例】 SELECT * FROM 家計簿アーカイブ WHERE 費目 IN ( SELECT COALESCE ( 費目, '不明' ) FROM 家計簿 ※費目がNULLなら代わりに'不明'にする。 |
p220(複数値(ベクター)の代わりに副問い合わせ) |
| EXISTS演算子 | 副問い合わせの内部から主問い合わせの表や列を利用する副問い合わせを「相関副問い合わせ」という。特に「ほかのテーブルに値が登場する行のみ抽出したい」場合に、EXISTS演算子とともに使われる。パターンとして覚えておくとよい。 | 【パターン】 SELECT 列 FROM テーブル1 WHERE EXISTS (SELECT * FROM テーブル2 WHERE テーブル1.列 = テーブル2.列) ※太字＝外側SQLの列を利用している。 |
スッキリp225 |
複数テーブルの結合（スッキリ8章）
| 事項 | SQL | メモ | 該当 |
|---|---|---|---|
| 左外部結合 | SELECT ～ FROM 左表の名前 LEFT JOIN 右表の名前 ON 結合条件 |
「左表については結合相手が見つからなくても、NULLであっても必ず出力せよ」 | スッキリp257 |
| 右外部結合 | SELECT ～ FROM 左表の名前 RIGHT JOIN 右表の名前 ON 結合条件 |
「右表の全行を必ず出力する」 | スッキリp258 |
| 完全外部結合 | SELECT ～ FROM 左表の名前 FULL JOIN 右表の名前 ON 結合条件 |
「左右の表の全行を必ず出力する」 | スッキリp258 |
| 内部結合 | SELECT 選択列リスト FROM テーブルA JOIN テーブルB ON 両テーブルの結合条件 |
結合すべき相手の行が見つからない場合に行が消滅してしまう通常の結合。 | スッキリp246、260 |
テーブルの別名が複数登場するJOIN句が難しい
(自分メモ：JOIN句の中にテーブルの別名が何個も登場すると、SQLを理解するのが困難。
p267、268の練習問題を何度も解いてマスターする！(2021/3/14現在))
例えばこの問題。これがスラスラ理解＆書けるようにする！
今は、最後の「JOIN A AS C ON B.B1=C.A1」の理解に時間がかかる。(2021/3/14現在)
【スッキリp267 練習問題8-1-(4)】
次のようなテーブルAとテーブルBがあります。これらをを用いて、下のSQL文を実行したときの結果表を記述してください。
SELECT A.A1 , C.A2 , B1 , B2 FROM A
JOIN B
ON A.A1=B.B1
JOIN A AS C
ON B.B1=C.A1
テーブルA
| A1 | A2 |
|---|---|
| 1 | 3 |
| 2 | 4 |
テーブルB
| B1 | B2 |
|---|---|
| 1 | 2 |
| 3 | NULL |
解答
| A.A1 | C.A2 | B1 | B2 |
|---|---|---|---|
| 1 | 3 | 1 | 2 |
用語
| 用語 | 意味 | 参考 | 該当 |
|---|---|---|---|
| カラム | 文字の位置やデータの長さを表す単位 | 「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典 | 黒本4章SQL |
| マルチバイト文字 | 全角文字(=2バイト以上のデータで表現する文字) | 「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典 | 黒本4章SQL |
| バイト | 単位。1バイト＝8ビット。 | 「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典 | 黒本4章SQL |
| 引数 | ひきすう。入力。 | SQL Server 虎の巻-【初級編⑦】SQLの関数を使いこなす | 黒本4章SQL |
| バイナリデータ | コンピュータが理解しやすいデータ（2進数で表現されたデータ）であり、テキストデータ以外のデータ。 | ITを分かりやすく解説 | 黒本4章SQL |
| 正規表現 | いくつかの文字列を一つの形式で表現するための表現方法 | サルにもわかる正規表現入門 | 黒本4章SQL |
| ネスト構造 | =入れ子。あるものがその内側に別のものを内包している状態。「SELECTをネストする(p205)」 | - | スッキリ7章副問い合わせp205 |
| 副問い合わせ | =副照会/サブクエリ。ほかのSQL文の一部分として登場するSELECT文のこと。 丸カッコ()でくくって記述する。 |
- | スッキリ7章副問い合わせp205 |
一言メモ
| 事項 | メモ | 該当 |
|---|---|---|
| CHAR型で指定した数値は | 文字数。バイト数ではない。 | 黒本4章SQL |
| 【コツ】 スッキリ書けるSQL |
1.まず、命令文(SELECTやINSERTなど)を記述する。 2.次に、テーブル指定の部分を記述する。 3.テーブル指定より後ろの部分を記述する。 4.テーブル指定より前の部分を記述する(SELECT文のみ)。 ※特に1.~2.を考え込まずにでいるよう訓練しておくとスムーズ。 ※p67の各命令の固有部分の図を参照する。 |
スッキリ2章基本文法と四大命令p68 |
| 【コツ】 副問い合わせを習得するコツ |
・副問い合わせが処理されるしくみを理解しておく。 （まず、内側にあるSELECT文が実行され結果に化ける→そして、外側のSQLが実行される） ※つまり副問い合わせは、具体的な値に置き換えることができる。 ・副問い合わせの代表的な3つのパターンを学んでおく。 （単一の値/複数の値/表の値） （ITの世界では複数のデータ(値)をある構造に従ってひとかたまりに取り扱うことがよくある。1つ以上のデータで形成されたものをデータ構造(data structure)という。なかでも基本的なデータ構造は「スカラー(単一の値)/ベクター(1次元に並んだ値/配列)/マトリックス(2次元に並んだ値/表)」の3つ。副問い合わせの3つのパターンは、検索結果がそれぞれこの3つになると考えると理解しやすい。） |
スッキリ7章副問い合わせp207 |
エラー
| エラー内容 | 解決方法 | 該当 |
|---|---|---|
| dokoQL、本(スッキリ)の通りに入力したがエラーが出て実行できない | 全角スペースが紛れ込んでいるので、全角スペースを消す/半角スペースに変更する。 | スッキリ |
読み方
| 用語 | 読み方 | 該当 |
|---|---|---|
| INTEGER | インテジャー | 黒本4章SQL |
| BOOLEAN型 | ブーリアンがた | 黒本4章SQL |
| DISTINCT | ディスティンクト | スッキリ4章検索結果の加工 |
| OFFSET-FETCH句 | オフセット-フィッチく | スッキリ4章検索結果の加工 |
| LENGTH | レングス | スッキリ5章式と関数 |
| TRIM | トリム | スッキリ5章式と関数 |
| LTRIM | エルトリム | スッキリ5章式と関数 |
| RTRIM | アールトリム | スッキリ5章式と関数 |
| CONCAT | コンキャット | スッキリ5章式と関数 |
| TRUNC | トランク | スッキリ5章式と関数 |
| COALESCE | コアレス | スッキリ5章式と関数 |
| EXISTS | イグジスツ | スッキリ7章副問い合わせ |
Q&A
ROLLBACKって書くの？書かないの？
（該当：『スッキリ』9章トランザクション p282）
【トランザクションを使うための指示】
・BEGIN：開始の指示。この指示以降のSQL文を1つのトランザクションとする。
・COMMIT：終了の指示。この指示までを1つのトランザクションとし、変更を確定する。
・ROLLBACK:終了の指示。この指示までを1つのトランザクションとし、変更の取り消しをする。
（引用：『スッキリ~』p282）
【私はこう理解した】
（ROLLBACKを書かないパターン）
「BIEGIN;」でトランザクションを開始して→「実行したいSQL文」をつらつら書いて→「COMMIT;」でSQL文を確定する。もし「COMMIT;」する前に「実行したいSQL文」の実行中に障害が発生した場合、実行中のSQL文の処理は全て取り消される。これが「ロールバック」。「ROLLBACK」と明記しなくても、勝手に実行される。
（ROLLBACKを書くパターン）
「COMMIT;」前であれば、「ROLLBACK」というSQL文をDBMSに送ることで、明示的にロールバックを発生させることができる。ただし、多くのDBMS付属のSQL実行ツールが、デフォルト状態では「自動コミットモード」と呼ばれるモードで動作している。
（自動コミットモードについて）
このモードにあるとき、DBMSは1つのSQL文が実行されるたびに、自動的に裏でコミットを実行してしまいます。
DBMSによっては、自動コミットモード中であっても「BEGIN」を実行することで、コミットかロールバックまでの間、一時的に自動コミットを解除することができます。
（引用：『スッキリ~』p283
Q:PostgreSQLは自動コミットモードがデフォルトなのだろうか？
→YES。自動コミットモードがデフォルト。
デフォルトでは、PostgreSQL はトランザクションを 非連鎖モード（他のデータベースシステムでは "自動コミット" としても知られています）で実行します。
（引用：PostgreSQL 7.3.4 リファレンスマニュアル-BEGIN-説明）
Q:PostgreSQLではどうやって自動コミットを解除するの？
上記リファレンスマニュアルには
autocommit モードをオフにした場合、BEGIN は不要です。
（引用：PostgreSQL 7.3.4 リファレンスマニュアル-BEGIN-説明）
と書いてあったので、解除は可能。ただ、その方法は書かれていなかった。（見つけられなかっただけかも）
他のサイトに解除方法が書かれていた。コマンド操作で解除するようだ。
→参照：「FrontPage-PostgreSQLでオートコミットをオフにする方法」
思いつき
SQLのインデックスを使って自作アナログシンセに和音機能を付けたい
『スッキリ~』11章のインデックスを読んでいてふと思った。
子どもらが大きくなってハンダや工具を家の中に置けるようになったら、やりたいと思っている「アナログシンセ作り」。その機能として、DBから和音を出力する、ってできるんじゃなかろうか。DB使わずに実行する方法もありそうだけど。インデックス使うと高速化できるらしいから、必要ならインデックス機能使うのアリかも。和音だけじゃなくて、音階と音色を組み合わせて、たとえば「"デザインあ"っぽいサウンドが作れるセット」とか作ると楽しそうだし、娘&息子 喜びそう。（SQLの使い方間違ってる...??）2021/3/17