プログラミングを書けるようになったら 次はSQLを覚えよう！いまさら聞けない人のためのSQL超入門 / Let's begin SQL next. An super introduction to SQL

2022/09/03 にオンラインで開催された Learn Languages 2022(LLイベント)での発表資料です。 LLイベントは Open Developers Conference (ODC) 2022 併催として開催されました。
（タイトルが日本語としておかしいのはご愛敬として見逃してください）

https://event.ospn.jp/odc2022-online/

#mysql
#mysql_jp
#ODC
#LLevent

Transcript

1. いまさら聞けない人の ためのSQL超入門 2022/09/03 日本MySQLユーザ会 坂井 恵 プログラミングを書けるようになったら 次はSQLを覚えよう！ Learn Languages 2022 #ll2022jp #mysql_jp 第2プログラミング言語への招待
2. プログラミングを書けるようになったら 次はSQLを覚えよう！ いまさら聞けない人のためのSQL超入門 ひとつの言語を使ってプログラムを少し書けるようになってきたと き、データベースを使用する壁に行き当たった人も多いでしょう。 このセッションでは、そんなあなたのために、データベースを操作 するための言語「SQL」を紹介します。 そもそもデータベースって何だろう、どうしてわざわざ使う必要が あるんだろう、というところからお話するので、これからSQLや データベースの勉強を始めようと思っている方はぜひお聞きくださ い。 (本セッションの案内文より）
3. 自己紹介 • 坂井 恵（さかいけい） @sakaik • データベース(特にデータの塊やデータの流れなど)が大好き • 日本MySQLユーザ会 副代表 • ゆるく、かつ、ゆるく運営しているユーザ会 • #mysql_jp ウォッチしてください。最近時々オンラインイベントやって ます • 有限会社アートライ 代表取締役 • since 1998 • 業務分析や問題解決に強み • その根幹にあるのが「データ構造」と「データの流れ」への理解 • 株式会社 iCARE (協力先) • 先日の OSC北海道に初めてスポンサー出展 • 「働くひとの健康を世界中に創る」
4. 今日のめあて • 何かひとつのプログラミング言語を少し使えるように なった人で • 次にデータベース（SQL）を学んでみようかなという、 これからSQLを学ぶ人向けに • データベースの概要と • SQLを学んで行くためのアウトラインを提供する
5. データベースって プログラムを書いていて、ちょっと多めのデータを扱いたくなった とき、どうしますか。 （多め ＝ 種類の多さ、データ量の多さ） → データを扱う処理を自前で書くと、結構たいへん。 → イイカンジにやってくれるライブラリとかあったらいいよね データ取り扱いのプロフェッショナル！ それが「データベース管理システム」
6. データベースってどこで使われるの？ • シンプルな例(サーバは機能によって複数台に分けられることがあります）
7. データベースってどこで使われるの？ • シンプルな例(サーバーは機能によって複数台に分けられることがあります） SQL
8. データベースって • データ扱い専用の機能（ソフト） • データの安全な保存や、便利な取り出し方法を提供 • 保管時の特徴によって様々な方式のデータベースがあるけど、今 日紹介するのはその中でも RDBMS というもの
9. RDBMS • RDBMS：リレーショナル データベース 管理 システム （Relational Database Management System) • 簡単に言うと、「テーブル」と呼ぶ形式にデータを登録していく もの • SQL: RDBMS上のデータを操作したり、RDBMSそのものを操作 したりするための専用言語
10. 主なRDBMS • Oracle Database • Microsoft SQL Server • Db2 • MySQL • PostgreSQL etc
11. SQLを学ぶということ 色々なソフトがあるので、それぞれのソフトでのデータ操作を覚え なきゃいけないの？ 「SQL」は、ISOで標準化されているので、基本的な部分はSQLを 覚えれば、どのRDBMSでも同じ操作ができる！ SQLの学習は、非常にコスパがいい！！
12. SQLを覚えればどのRDBMSでも使えるのでとてもおトク..... （小声） • 実をいうと、そんなにパーフェクトに「同じ」というわけではあ りません。 • それぞれの独自拡張や方言などがあります。 • それでも、主要な部分は「同じ」なので、ご安心を！ ▪ 方言や独自拡張の目立つ主な部分 • データ型 （大部分は共通。一部異なる） • 関数 （かなり異なる） • ストアドプロシージャ （全然異なる）
13. SQLの話をする前に「テーブル」の話 • RDBMSにおいて、データを登録するための「箱」 • テーブルの例（イメージ） • 1行が1件のデータ(レコード) • 各列（「住所」や「氏名」など）には、その列に定められた内容だけを登録する • Excelのシートにイメージは似ているけど、カーソルキーで移動しながらデータ操 作するわけではない 数値 文字列 文字列 文字列 文字列 数値 日付
14. テーブルは通常、いくつも作る/使う 顧客情報を格納するテーブル 商品情報を格納するテーブル 売上情報を格納するテーブル 担当者(社員)情報を格納するテーブル 例(イメージ） 店舗(支店)の情報を格納するテーブル 通常、ひとつのシステムは、たくさんのテーブルを作ってデータを格納します。
15. テーブルは自分たちで作る • 自分たちで取り扱いデータを格納するために、自分で、どんなテーブル を作るかを考えて、作ります。 • 学習中は、まずは自由に作ってみるところから！ • だめなテーブルと言われものもあるが、問題ないならいいじゃない！の 精神で。 今後、問題に気付ける能力をつけていきましょう。 • 本格的な業務で使うものは「プロ的」なテーブルの作り方というのがあ るので、ポイントを押さえた作り方を。 • 「こわれにくい」「遅くなりにくい」「取り出しやすい」など様々な視 点での「コツ」があって「テーブル設計」と言われている。これを学ぶ のもDBのひとつの登竜門
16. SQL
17. SQLとは • RDBMSを操作するための専用言語 • エス・キュー・エル と読む • Structured Query Language • ISOで標準化されている。 • SQL86/SQL89/SQL92/SQL:1999/SQL:2003/SQL:2008 など • 1992年のSQL92 (ISO 9075-1992) で基本的な仕様はほぼ完成 • 多くのRDBMSがこの標準にできるだけ従って開発されているので、 RDBMSの種類に依らず同じ構文を使用可能 • 実際にはある程度の方言はある（が、かなり「同じ」と言って良いほうだと思います）
18. コラム： "SQL" の発音 • エス・キュー・エルです • （シークェルではないです） 例： MySQL リファレンスマニュアルより https://dev.mysql.com/doc/refman/8.0/en/what-is-mysql.html The official way to pronounce “MySQL” is “My Ess Que Ell” (not “my sequel”), but we do not mind if you pronounce it as “my sequel” or in some other localized way.
19. SQLでできること • データベース自体への操作 テーブル作成／削除 その他DBオブジェクトの作成／削除 • テーブルのデータに対する操作 データの登録（行追加） 既存データの修正（更新・変更） 既存データを削除 既存データの取出し（抽出、集計） • データの制御に関する指示 ※赤字：今日紹介するもの
20. SQL入門（概要）
21. テーブルの作成・削除 • STEP 1 どんなテーブルを作るか決める • STEP 2 そのテーブルを作るためのSQLを記述して実行する
22. テーブルの作成 • STEP 1 どんなテーブルを作るか決める  テーブル名  どんな列を持つのか  列名  その列の型  その列が持つ制約（必須、重複不可等）
23. テーブルの作成 • STEP 2 そのテーブルを作るためのSQLを記述して実行する  テーブル名  どんな列を持つのか  列名  その列の型  その列が持つ制約（必須、重複不可等） CREATE TABLE テーブル名 ( 列1の名前 データ型, 列2の名前 データ型, 列3の名前 データ型, : ); ※制約がある場合はデータ型の後ろに記述する CREATE TABLE items ( item_id INTEGER PRIMARY KEY, item_name VARCHAR(10) NOT NULL, price BIGINT, zaiko INTEGER, created_at DATETIME ); [構文] [例] ※データ型は、各RDBMSのマニュアルを参照してください。
24. テーブルの削除 • テーブルの中に入っているデータも消えます • 「消していいですか？」なんて聞いてくれません DROP TABLE テーブル名; DROP TABLEitems; [構文] [例]
25. その他のDBオブジェクトの作成と削除(概要) • テーブル以外にもDBオブジェクトは色々あってな。  スキーマ  インデックス  プロシージャ  ビュー  トリガー etc • みんな「SQL」を使って作ったり削除したりするのじゃ。 • CREATE / DROP の他に ALTER 文により変更できる。
26. SQLによるデータ操作 • テーブルに対して データを入れたり（登録） 既存データを削除したり 既存データの一部列の値を変更したり（更新） 登録済データを検索・抽出、集計したり できます。 この中で一番幅広く学んで行くことになる(と思う)のが  登録済データを検索・抽出、集計したり です。 ここからの話は、作ったテーブル対す る、データの操作です！
27. データの登録 • テーブルに1行を追加する命令です • INSERT文を使います INSERT INTO テーブル名(列名1, 列名2, …) VALUES (値1, 値2, …); INSERT INTO テーブル名 VALUES (値1, 値2, …); すべての列に値を入れる時は、列名の羅列は省略可能 [構文] [例] INSERT INTO items (item_id, item_name, price, created_at) VALUES (12, '商品1', 150, '2022-09-03'); CREATE TABLE items ( item_id INTEGER PRIMARY KEY, item_name VARCHAR(10) NOT NULL, price BIGINT, zaiko INTEGER, created_at DATETIME ); INSERT INTO items VALUES (12, '商品2', 380, 100, '2022-09-03');
28. 削除/更新/抽出 を学ぶ前に... • 行を特定する方法を習得するのが大切！ • 格納データには、Excelとかみたいに「何行目」という概念はない • 以下の ITEMS テーブルのデータを例にすると・・・・ • 「ID列の値が 8 のもの」 • 「CATEGORY_IDが 1または2のもの」 • 「CREATED_ATが 2021年11月1日以降のもの」 • 「 CATEGORY_IDが5のもののうち、AMOUNTが10以下のもの」 • 「ITEM_NAME に "mono"という文字列を含むもの」 • 「CATEGORY_IDが9以外のもの」 などのように、列の値を使って指定します。 ID ITEM_NAME CATEGORY_ID AMOUNT CREATED_AT 11 Nanika 3 3 2021/6/21 15 Tekitou 5 10 2021/7/23 13 Arekore 5 0 2021/9/12 8 iimono 2 27 2021/11/8 6 Good goods 1 21 2021/11/30 20 sample 2 99 2022/6/23
29. 行の特定の方法 • 「ID列の値が 8 のもの」 • 「CATEGORY_IDが 1または2のもの」 • 「CREATED_ATが 2021年11月1日以降のもの」 • 「 CATEGORY_IDが5のもののうち、AMOUNTが10以下のもの」 • 「ITEM_NAME に "mono"という文字列を含むもの」 • 「CATEGORY_IDが9以外のもの」 • ID=8 • CATEGORY_ID = 1 OR CATEGORY_ID = 2 • CREATED_AT >= '2021-11-01' • CATEGORY_ID=5 AND AMOUNT<=10 • ITEM_NAME LIKE '%mono%' • CATEGORY_ID <> 9
30. データの削除 • DELETE文を使います • 削除対象となる行の条件を、WHERE句で指定します DELETE FROMテーブル名 WHERE 行を特定する条件 DELETE FROM ITEMS WHERE CREATED_AT <= '2021-12-31'; [構文] [例] ID ITEM_NAME CATEGORY_ID AMOUNT CREATED_AT 11 Nanika 3 3 2021/6/21 15 Tekitou 5 10 2021/7/23 13 Arekore 5 0 2021/9/12 8 iimono 2 27 2021/11/8 6 Good goods 1 21 2021/11/30 20 sample 2 99 2022/6/23 ITEMSテーブル
31. 既存データの更新(変更) • UPDATE文を使います • 更新対象となる行の条件を、WHERE句で指定します • 更新したい列とその値を指定します UPDATEテーブル名 SET カラム1 = 変更したい値, カラム2 = 変更したい値 .... WHERE 行を特定する条件 UPDATE ITEMS SET AMOUNT = 20 WHERE ID=13 [構文] [例] ID ITEM_NAME CATEGORY_ID AMOUNT CREATED_AT 11 Nanika 3 3 2021/6/21 15 Tekitou 5 10 2021/7/23 13 Arekore 5 0 2021/9/12 8 iimono 2 27 2021/11/8 6 Good goods 1 21 2021/11/30 20 sample 2 99 2022/6/23
32. データ検索(抽出)のきほん(1) • SELECT文を使います • 取得したい行の条件を、WHERE句で指定します • 取得したい列名を指定します SELECT 取得したいカラム1, カラム2... FROM テーブル名 WHERE 行を特定する条件 SELECT ID, ITEM_NAME, AMOUNT FROM ITEMS WHERE ID=13 [構文] [例] ID ITEM_NAME CATEGORY_ID AMOUNT CREATED_AT 11 Nanika 3 3 2021/6/21 15 Tekitou 5 10 2021/7/23 13 Arekore 5 0 2021/9/12 8 iimono 2 27 2021/11/8 6 Good goods 1 21 2021/11/30 20 sample 2 99 2022/6/23 ID ITEM_NAME AMOUNT 13 Arekore 0
33. データ検索(抽出)のきほん(2) • 取得したい列を記述する部分には、計算式や関数なども使えます • 取得する列には別名を付けることもできます SELECT 取得したいカラム1, カラム2... FROM テーブル名 WHERE 行を特定する条件 SELECT ID, SUBSTR(ITEM_NAME,3,4) AS NAME_PART, TYPE1_AMT + TYPE2_AMT AS GOUKEI FROM ITEMS WHERE ID=13 [構文] [例] ID ITEM_NAME TYPE1_AMT TYPE2_AMT CREATED_AT 11 S_Nanika 3 3 2021/6/21 15 S_Tekitou 5 10 2021/7/23 13 A_Arekore 5 4 2021/9/12 8 K_iimono 2 0 2021/11/8 6 A_Good goods 1 21 2021/11/30 20 I_sample 2 99 2022/6/23 ID NAME_PART GOUKEI 13 Arek 9
34. 忘れないで！取得順序指定！ • RDBMS内に格納されているデータには（Excelのシートとは違って）順 序はありません。「何行目」という指定がないことからも分かりますね。 • SELECT文で取得したい行を指定する際、どんな順序で取得したいかを 指定します。（指定しないと、順序は「不定」となります） • 順序指定は ORDER BY 句を使用します。昇順(小さい順)は ASCを、降 順(大きい順)は DESCを指定します。（ASCは省略可能） SELECT ID, ITEM_NAME, AMOUNT FROM ITEMS WHERE ID=13 ORDER BY CREATED_AT DESC;
35. データ抽出：集計 • SQLの検索機能の醍醐味 → 強力な集計機能 • GROUP BY 句を使います ID ITEM_NAME CATEGORY_ID AMOUNT CREATED_AT 11 Nanika 3 3 2021/6/21 15 Tekitou 5 10 2021/7/23 13 Arekore 5 0 2021/9/12 8 iimono 2 27 2021/11/8 6 Good goods 1 21 2021/11/30 20 sample 2 99 2022/6/23 SELECT CATEGORY_ID, COUNT(*) cnt, SUM(AMOUNT) goukei FROM ITEMS WHERE AMOUNT > 0 GROUP BY CATEGORY_ID
36. データ抽出：複数テーブルから • 多くの場合、SQLでの抽出(検索・集計)は、複数のテーブルをくっつけ て情報を取得することが多いです • 例えば、以下の ITEMSテーブルのCATEGORY_ID 列の数値は、 CATEGORYテーブルの CAT_ID で示される行を参照することで、その 名称を取得することができます。 • テーブルを「結合」して 取得します。 ID ITEM_NAME CATEGORY_ID AMOUNT 11 Nanika 3 3 15 Tekitou 5 10 13 Arekore 5 0 8 iimono 2 27 6 Good goods 1 21 20 sample 2 99 CAT_ID CATEGORY_NAME NANIKA 1 カテゴリ1 2 カテゴリ2 3 カテゴリ3 5 カテゴリ5 7 カテゴリ7 [ITEMS] [CATEGORY]
37. テーブルの「結合」 • JOIN句を使用します • 両方のテーブルに存在するものだけを取得するもの（INNER JOIN）と、 どちらかのテーブルを軸として、相手側テーブルの対象データ有無にか かわらず取得するもの（OUTER JOIN）があります。 • 個人的な意見ですが、まずはOUTER JOIN でテーブル結合の考え方にな じんだ後で、「両方に存在するものだけ」のINNER JOIN を追加で覚え るという順序でいいんじゃなかな、と思っています ID ITEM_NAME CATEGORY_ID AMOUNT 11 Nanika 3 3 15 Tekitou 5 10 13 Arekore 5 0 8 iimono 2 27 6 Good goods 1 21 20 sample 2 99 CAT_ID CATEGORY_NAME NANIKA 1 カテゴリ1 2 カテゴリ2 5 カテゴリ5 7 カテゴリ7 [ITEMS] [CATEGORY]
38. テーブルの「結合」 • 左側のテーブルを基準にOUTER JOIN(外部結合)するには LEFT OUTER JOIN を使用します。 • 2つのテーブルだけでなく、3つ4つ5つ...結合することもできます。 ID ITEM_NAME CATEGORY_ID AMOUNT 11 Nanika 3 3 15 Tekitou 5 10 13 Arekore 5 0 8 iimono 2 27 6 Good goods 1 21 20 sample 2 99 CAT_ID CATEGORY_NAME NANIKA 1 カテゴリ1 2 カテゴリ2 5 カテゴリ5 7 カテゴリ7 [ITEMS] [CATEGORY] SELECT ITEMS.ID, ITEMS.ITEM_NAME, CATEGORY.CATEGORY_NAME, CATEGORY.NANIKA, ITEMS.AMOUNT FROM ITEMS LEFT OUTER JOIN CATEGORY ON (ITEMS.CATEGORY_ID=CATEGORY.CAT_ID) WHERE AMOUNT > 0
39. テーブルの別名 • 複数のテーブルを使用する際は、各カラムがどのテーブルのものかを修 飾する必要があります。 • カラムの羅列をする際など、結構長いので、短めの別名を付けることも 多いです。 SELECT ITEMS.ID, ITEMS.ITEM_NAME, CATEGORY.CATEGORY_NAME, CATEGORY.NANIKA, ITEMS.AMOUNT FROM ITEMS LEFT OUTER JOIN CATEGORY ON (ITEMS.CATEGORY_ID=CATEGORY.CAT_ID) WHERE ITEMS.AMOUNT > 0 ORDER BY ITEMS.ID SELECT it.ID, it.ITEM_NAME, cat.CATEGORY_NAME, cat.NANIKA, it.AMOUNT FROM ITEMS it LEFT OUTER JOIN CATEGORY cat ON (it.CATEGORY_ID=cat.CAT_ID) WHERE it.AMOUNT > 0 ORDER BY it.ID
40. SQLの構文の話はここまで とりあえず • テーブルを作って • データを入れたり • 消したり • 更新したり • 抽出、集計（結合も含む） する方法をざっと紹介してきました。
41. 自分で試してみたくなった人のために 「第一歩」を踏み出す時に迷子になりやすいあなたのためのガイド • 何のソフトを使うかを決める • インストールする • （必要ならば初期ユーザ等の設定をする） • 接続してみる • スキーマ（データベース）を作成してみる • スキーマの中に入って(*1) 、テーブルを作ってみる • テーブルにデータを何件か登録してみる • 登録したデータを見てみる • そのうち1件を削除してみる • 登録したデータの一部の値を更新してみる • 登録したデータを「集計」してみる • 「コード」的なものを別テーブルとして作成できそうなデータであれば、作 成して、テーブルの結合にもトライしてみる (*1)ちょっと正確な表現ではありませんがご了承ください
42. 自分で試してみたくなった人のために MySQL編 • インストール：Windows/Linux/MacOS などで動作します。インストール方法は 調べてください（インストーラまたは yum/aptなどのパッケージあります）。 2022年9月現在の最新バージョンは、MySQL 8.0.30 です。インストール方法に よって、パスワードを途中で設定したり、画面に表示されていたり、ログに書か れていたりします。 • コマンドライン（コマンドプロンプトやbash）で、MySQLに接続します。 • 接続されたら、とりあえず情報見てみましょう • データベース（スキーマ）を作成します。お好きな名前で。 • 作ったスキーマを使用する宣言をします（「中に入る」イメージ） これで、テーブルを作ったり、データを入れたり出したりできるようになります。 > mysql -uroot -p mysql> status; mysql> CREATE DATABASE sukinanamae; mysql> USE sukinanamae; インストール方法によっては、 初回接続後にパスワードを変更 する必要があるかもしれません。 画面の表示や、ドキュメントを よく見て対応しましょう。
43. 自分で試してみたくなった人のために MySQL編 知っておくと便利なコマンドを紹介： • データベース（スキーマ）一覧を見る • カレントスキーマ（今使うことになっているスキーマ）を見る • カレントスキーマに存在するテーブル一覧を見る • テーブル定義を見る • MySQLから切断する mysql> SHOW DATABASES; mysql> SHOW TABLES; mysql> DESC mitai_table; mysql> SHOW CREATE TABLE mitai_table¥G mysql> SELECT DATABASE(); ¥Gの代わりに普通に ; でも良 いです。見比べてみてください。 mysql> exit;
44. 日本MySQLユーザ会 • 2000年設立で、緩くMySQLに関する情報交換などを行っています。一応、 メーリングリストが「本体」です • 最近はときどきオンラインイベントをやっています。はやくオフライン イベント再開したいです... • 日本でのMySQL情報は Twitter の #mysql_jp ハッシュタグに集まって います。ユーザ会の情報もこちらで案内しています。MySQLの話題をツ イートするときにも、ご利用ください • 最近 ユーザ会のDiscordサーバ始めました。Twitterで「MySQL discord」 で検索してください • 直近では、以下のイベントを予定しています • 9月中旬：DiscordでMySQLのおしゃべりをする会（「今夜も生でMySQL（仮題）」 • 9月22日：ClubMySQL (MySQL Shell for VSCodeのお話をたっぷり聞かせていただく予 定です)
45. まとめ
46. SQLが分かるようになると楽しい • データの流れはシステムの血管 • SQLは自由！どんどん書いてみよう • クエリの書き方でかかる時間に文字通り「桁が違う」ことがある。楽しい！ • 思いついたらプログラムできるのと同様に、思いついたら膨大なデータから 抽出とか集計とかできるようになれる（いま、オープンデータとかいっぱい あります） • ある程度データ操作ができるようになったら、  テーブルの作り方（テーブル設計）  データ操作をひとつのカタマリとして扱う重要性（トランザクション） あたりについて、調べてみると良いでしょう。