以下の Qiita の続きです。以前からお世話になっているProgakuという共助学習エンジニアコミニティのモブプロを通して、SQL の直書きの必要にせまられ、全然分かってなかったなと感じ、個人用に作っています。もう完全に Qiita が最近はプログラミングに関する学習のまとめノート(※主教科書としているのは下記の①)代わり…(Qiita 考えた人ホンマ偉いな…(・∀・))
〇主な学習参考資料・実験材料
①『スッキリわかるSQL入門 中山 清喬/飯田 理恵子 共著, 株式会社フレアリンク 監修』②転職活動時のポートフォリオ(Laravel 10を)に付けた意見投稿フォームのCRUDに紐付けた MySQLWorkbench並びにデプロイ前のphpadmin(※phpadminのDBサーバーはMariaDBを使用)
以下しばらく、構造がよく似た複数のテーブルを扱う状態を考えます。純正のSQLの場合、1つの手段として以下のような集合演算子というのを扱うらしいです。
(なお、ここでいう集合演算は高校の「数学Ⅰ・ A 」や大学での「集合と位相」の序盤でやるような集合に対する演算と同義でした。※ここでの集合は各テーブル、要素が各テーブル内の個々のデータにあたるようです。)
① 和集合にあたるデータを取得する:UNION
※重複行はまとめて2つのSELECT文で取得したデータを足し合わせた結果を返す
SELECT column_name_a AS new_a, column_name_b AS new_b, … , column_name_n AS new_n
FROM table_name1
WHERE 条件式(column_name = 'value1' 等)
UNION
SELECT column_name_A, column_name_B, … , column_name_N
FROM table_name2
WHERE 条件式(column_name = 'value1' 等)
ORDER BY column_name1 ASC or DESC, column_name2 ASC or DESC, …
/*「UNION」が「和集合」の集合演算子*/
/*ORDER BY で何も指定しない場合、「ASC(昇順(小⇒大、古⇒新))」が指定された状態になる*/
/*使用時は「ASC or」または「or DESC」を消す*/
(1) 記述するカラムの列数とそれぞれのデータ型が一致していないといけない(※カラム名は一致している必要はない)
(2) ORDER BY ~は最後のSELECT文につける(※最終的に和集合としてとったものに対してソートをかけることになる)
(3) table_name1 と table_name2 が同一のものでもよい。(1つのテーブル内で取得する際の条件をかえたり、別のカラムと別のカラムを合わせる等)
(4) 取得した後のカラム名をASで指定し直す場合、1行目のものが適用される。
ことに注意
同一テーブルで条件式を変えてUNIONを使ってみた例(冒頭の実験材料②を使用)
※Laravelで転職活動時作ったポートフォリオに作った意見投稿フォームに紐付けたテーブルのマイグレーションファイル
下2つはそれぞれ対応するテーブルのphpadminの構造タブ と MySQLWorkBenchを開いた
※開発環境は利用していた民間プログラミングスクールでも使っていたXAMPPを使用
とりあえず、VisitorName、VisitorEmail はいずれもデータ型はVARCHAR(可変長の文字列)
今のところ使い道は思いつかないですが、例えばこんな感じのSQLを試してみました。(table名はどちらもcontact_forms)※赤矢印のカラムのnullでないところを取り出す条件式として、VisitorName、VisitorEmailカラムそれぞれの値をまとめてみる)
結果、上記のように同一テーブルの2つの異なるカラムの値を組み合わせた結果が抽出されることになる。
※重複行はまとめずに2つのSELECT文で取得したデータを足し合わせた結果を返す
SELECT column_name_a AS new_a, column_name_b AS new_b, … , column_name_n AS new_n
FROM table_name1
WHERE 条件式(column_name = 'value1' 等)
UNION ALL
SELECT column_name_A, column_name_B, … , column_name_N
FROM table_name2
WHERE 条件式(column_name = 'value1' 等)
ORDER BY column_name1 ASC or DESC, column_name2 ASC or DESC, …
/*ORDER BY で何も指定しない場合、「ASC(昇順(小⇒大、古⇒新))」が指定された状態になる*/
/*使用時は「ASC or」 または 「or DESC」を消す*/
② 差集合(ベン図を書いたときに太った三日月の形のようなものになるもの)にあたるデータを取得する:EXCEPT
※先に書いたSELECT文で取得したデータから、後で書いたSELECT文で取得できるものにも共通するデータを差し引いた結果を返す
SELECT column_name_a AS new_a, column_name_b AS new_b, … , column_name_n AS new_n
FROM table_name1
WHERE 条件式(column_name = 'value1' 等)
EXCEPT
SELECT column_name_A, column_name_B, … , column_name_N
FROM table_name2
WHERE 条件式(column_name = 'value1' 等)
ORDER BY column_name1 ASC or DESC, column_name2 ASC or DESC, …
/*Oracle DB の場合、EXCEPTの代わりに「MINUS」を用いる*/
/*ORDER BY で何も指定しない場合、「ASC(昇順(小⇒大、古⇒新))」が指定された状態になる*/
/*使用時は「ASC or」または「or DESC」を消す*/
※EXCEPT より上のSQL文で取得したデータから、EXCEPTより下のSQL文で取得したデータを差し引いた結果を返す
③ 積集合(または共通集合)(ベン図を書いたときに重なる部分)にあたるデータを取得する:EXCEPT
※先に書いたSELECT文で取得したデータから、後で書いたSELECT文で取得できるものにも共通するデータをを返す
SELECT column_name_a AS new_a, column_name_b AS new_b, … , column_name_n AS new_n
FROM table_name1
WHERE 条件式(column_name = 'value1' 等)
INTERSECT
SELECT column_name_A, column_name_B, … , column_name_N
FROM table_name2
WHERE 条件式(column_name = 'value1' 等)
ORDER BY column_name1 ASC or DESC, column_name2 ASC or DESC, …
/*ORDER BY で何も指定しない場合、「ASC(昇順(小⇒大、古⇒新))」が指定された状態になる*/
/*使用時は「ASC or」または「or DESC」を消す*/
※高校数学の集合で「集合A」かつ「集合B」を 「A$\cap$B」と書いたと思いますが、こいつに当たります。なお、A $\cup$ B = A + B - A$\cap$B で、上の差集合はEXCEPT句より上で取り出したデータをA、下のSELECTで取りだしたデータをBとみなすと、 A から A$\cap$B にあたる部分を取り除いたものにあたります。
※ MySQL 以外にも MariaDB や SQLite などいくつか RDBMS(Relational DataBase Management System)はありますが、どうも冒頭 ① の巻末資料によると、どのRDBMSを使うかによって使うことのできる句などの修飾語が異なるようです。
例えば、それによれば上3つのOSS(オープンソースソフトウェア:要するに無料で使えるもの)では、OFFSET-FETCH句は使えないが、有償のRDBMS の1つである Oracle DBでは使える等
※ 上記の SQL は、Laravel (PHP のフレームワークの1つ)でいうところの Eloquent ORM や テーブルのrelation のいずれかに相当します(整理次第、再度自分用に追記します)
※ ORM(本Qitta末尾その他参考資料⑤参照)は Laravel の Eloquent ORM 以外にも Django(ジャンゴ:Python のフレームワークの1つ)など他のフレームワークにもそれぞれあるようです。
その他参考資料
① Laravel の教科書 バージョン10 対応
② 参考資料①の著者が運営しているLaravelに関する技術ブログ
https://biz.addisteria.com/?s=relation
③【laravel】データベースの操作:Eloquent ORM編 @gone0021 さんQiita
https://qiita.com/gone0021/items/951cd63a7e591e18cd2a
④ Laravel 公式ドキュメントのrelationに関する部分
https://laravel.com/docs/11.x/eloquent-relationships
⑤ORMの概念理解 @minimabot さん in 株式会社スピードリンクジャパン
https://qiita.com/minimabot/items/0a3fcc41fd7140dfdc41
※ORM:オブジェクト関係マッピング:Object-Relational Mapping 、 SQLの代わりに ModelというDBと結びついたファイルを介してDBと連携して簡潔にデータを取り出すコード