前回の続きで第二章の主要点をまとめていきたいと思います。
(具体的なテーブル表など割愛させてもらうことが多いです)
o21o21.hatenablog.jp
今回は、「SQLの基礎」です。
IT系の業務をしていることがあれば1度は絶対見聞きしたことのあるものばかりでしょう。
ただ、なんとなく作業の中でないがしろ(仕組みを考えず作業したりノリでSQLを書いていたり...ww)にしていた部分が復習していると新たな気付きがありました。
SELECT文
SELECTといえば、「検索」です。
こんなSQLは実に簡単でよくみますね。
SELECT name, address
FROM Address
WHERE address = '東京';
WHEREという単語はSQLにおいて、「どこ？」ではなく、「〜という場所」という関係副詞の用法です。
ANDやORをつければ複数条件で検索ができます。
ただ、ORを複数回使用するのであれば、
INをつかって、IN('value', 'value', 'value')のようにした方が効率的です。
また、NULLは、IS NULL, IS NOT NULLですね。
GROUP BYは、カットと集約という機能から成り立っているということを覚えておきましょう。
GROUP BYを使用するときは、大きなホールケーキをイメージしてみるとわかりやすいです。
どんな目的でカットしよう？とか考えると何のカラムでGROUP BYすればいいかイメージがつきやすいかもしれません。
HAVING句は、レコードの集合に対して条件指定を行うものだと考えます。
SELECT address, COUNT(*)
FROM Address
GROUP BY address
HAVING COUNT(*) = 1;
これでレコード数が1人だけの住所列を選択しています。
だいたいSELECT文を流して出力された結果の順序は特に決まったルールがあるわｋではありません。
DBMS内での決まったルールみたいなものはあるかもしれませんが、これはとてもローカルなお話です。
そこで明示的にレコードの順序を保証するならば、ORDER BYを使えばいいわけです。
ORDER BY DESC みたいに。
ちなみに、昇順(ASC)/降順(DESC)を指定しましょう(デフォルトはASCなので昇順で並べたいなら指定する必要はありません)。
また、このORDER BYを使用したとき、同じ値(例: 年齢を検索して22歳が2人以上いた場合など)があったとき、
この同じ値内での順番は他に条件指定がなければランダムです。
VIEW とは、SELECT文をDBに保存する機能です。
以下がVIEWの作成の仕方。
CREATE VIEW <view name> (row1, row2)
AS
SELECT address, COUNT(*)
FROM Address
GROUP BY address;
このVIEWの使用方法としては、以下。
SELECT row1, row2
FROM <view name>;
理解するより見たほうが早いです。
SELECT name
FROM Address
WHERE name IN (SELECT name FROM Address1);
よくWHERE句条件作成においてサブクエリを使用するケースが多いでしょうか。
もちろんFROM句に直接サブクエリを使うこともできます。
サブクエリを使用した場合、内部でサブクエリから優先的に実行されることを覚えておきましょう。
IN句などで定数を直接使用していた場合は、メンテが必要になるかもしれませんが、サブクエリで書いておけばメンテも最小限になるかもしれません。
条件分岐、集合演算、ウィンドウ関数、更新
SQLはコードの中に手続きを一切記述しないため、必然的に条件分岐も「式」という単位で行われます。
SELECT name,
address,
CASE WHEN address = '東京' THEN '関東'
CASE WHEN address = '福島' THEN '東北'
CASE WHEN address = '三重' THEN '中部'
CASE WHEN address = '大阪' THEN '関西'
ELESE NULL END AS district
FROM Address;
CASE式はSELECT、FROM、WHERE、GROUP BY句などどこでも使用できます。
WHERE句で例えるなら、ORが和集合でANDが積集合です。
Address1(9行レコード)テーブルとAddress2テーブル(6レコード)があるとして、
SELECT *
FROM Address1
UNION
SELECT *
FROM Address2;
この結果は13行です。本来であれば15行あるのかと思ってしまいますが、
2行少ない理由は、両方のテーブルに存在重複しているレコードがあるからです。
UNIONでは、重複するレコードは削除する動作をします。
もし削除したくない場合は、UNION ALLを使用しましょう。
SELECT *
FROM Address1
INTERSECT
SELECT *
FROM Address2;
INTERSECTは、積集合です。「交差する」と覚えましょう。
両テーブルに共通するレコードしか出力されません。
EXCEPTは、差集合です。こちらは、「除外する」と覚えましょう。
SELECT *
FROM Address1
EXCEPT
SELECT *
FROM Address2;
このSQLは、Address1 - Address2ということになります。
Address1からはAddress2にある共通しているレコードが削除されて出力されます。
(※削除と書いていますが、実際テーブルから削除されるわけでは勿論ありません。)
EXCEPTは、テーブルの書く順序で結果が変わります。
(例: Address2 - Address1)
先程、GROUP BYでは、「集約とカット機能を持っている」と説明しました。
この集約機能を省いたものが、PARTITION BYです。
なので、結果が異なります。
こちらがGROUP BY。
SELECT address, COUNT(*)
FROM Address
GROUP BY address;
address | count
-----------+-------
千葉県 | 2
福島県 | 2
三重県 | 1
和歌山県 | 1
こちらがPARTITION BY。
SELECT address,
COUNT(*) OVER(PARTITION BY address)
FROM Address;
address | count
-----------+-------
千葉県 | 2
千葉県 | 2
東京都 | 3
東京都 | 3
東京都 | 3
福島県 | 2
福島県 | 2
和歌山県 | 1
また、ウィンドウ関数として使用できるのはCOUNTやSUMの他に、
RANKやDENSE_RANK(抜け番なし)ROW_NUMBERなどが存在します。
例: RANK() OVER (ORDER BY age DESC) AS rnk
レコードを1行ずつ登録していく。
INSERT INTO <table name> (column1, column2, ....;) VALUES (value1, value2, .........);
valueには、文字列型ならシングルコーテーションを、NULLならNULLと指定します。
複数行追加することも可能です。VALUESのあとに続けて値を書いて行きましょう。
テーブルのレコード全てを削除。
DELETE FROM <tabale name>;
全てのレコードを削除しないなら、WHERE句で条件を指定しましょう。
登録済みのレコードに対して、データを更新します。
こちらもWHERE句で条件を付け加えることが可能です。
UPDATE <table name>
SET <column> = 式;
まとめ
以上、簡単な内容でしたが根本となる句や式が確認できました。
基礎となるSQLを知らなければ応用も活用も難しくなってしまいます。非手続き型で直観的に記述できるのがSQLの良いところです。
検索する目的や欲しい結果を求める際、素早く思考できるよになれるとより次回以降の章も理解度が増すのかと思いました。
