OSC22fall: これから始める人のためのRDBMS_SQL入門/osc22fall_rdbms_sql

2022/10/29(土)に開催された、オープンソースカンファレンス2022Online/Fall での 『これから始める人のためのRDBMS/SQL入門』発表資料です。

OSC2022 Online/Fall 2日目タイムテーブル：
https://register.ospn.jp/osc2022-online-fall/modules/eventrsv/2.html

Transcript

1. これから始める人のための RDBMS/SQL入門 2022/10/29 日本MySQLユーザ会 坂井 恵 オープンソースカンファレンス2022 Online/Fall #osc22on #mysql_jp
2. 今日のセミナーの内容 今回はデータベースの基礎についてお話します！ 様々なシステムの裏側で働いている「データベース」。 存在は知っているし、興味もあるのだけど、学ぶきっかけがないま ま来ている人も多いのではないでしょうか。 今日は、SQLデータベース(*1)が必要である理由から、RDBMSの基 本的なしくみ、SQLの考え方など、これから SQLデータベースに触 れようと思っている方向けに優しく易しく解説します。 (*1)より正確には リレーショナルデータベース管理システム(RDBMS)と言 います
3. 自己紹介 • 坂井 恵（さかいけい） @sakaik データベース(特にデータの流れやデータが貯まっているところ)が大好き • 日本MySQLユーザ会 副代表 • ゆるく、かつ、ゆるく運営しているユーザ会 • since 2000 • 有限会社アートライ 代表取締役 • since 1998 • 「データ構造」と「データの流れ」への幅広い理解 • 業務分析や問題解決に強み
4. 日本MySQLユーザ会 • 2000年設立で、緩くMySQLに関する情報交換などを行っています。一応、 メーリングリストが「本体」です • 最近はときどきオンラインイベントをやっています。はやくオフライン イベント再開したいです... • 日本でのMySQL情報は Twitter の #mysql_jp ハッシュタグに集まって います。ユーザ会の情報もこちらで案内しています。MySQLの話題をツ イートするときにも、ご利用ください • 最近 ユーザ会のDiscordサーバ始めました。使い方模索中の段階です。 Twitterで「MySQL discord」で検索してください • 直近では、以下のイベントを予定しています • 11月中旬：MySQL 8.0.31 リリースノートでわいわい言う勉強会（日付未定） • 11月中にもたぶん discordを使った交流会をやります（毎月実施中）
5. その他のMySQL関連イベント 日本オラクルさんも、活発に MySQLユーザ向けのイベントを 開催してくださっています。 (画像は12/2予定のイベント) FOSS4G Japan でもMySQLの GIS機能に関する講演があります。 (画像は 11/12予定のイベント)
6. 今日覚えてほしい ただひとつのこと #mysql_jp MySQLの情報は、Twitterのハッシュタグで確認 ハッシュタグ
7. データベース 超入門
8. データベース(RDBMS) の ソフトウェア • 主に以下のようなソフトウェアがあります オープンソース • MySQL • PostgreSQL 非オープンソース • Oracle Database • Microsoft SQL Server • IBM Db2 Database ※今日の説明は、主にMySQLを具体例として使いますが、他のDBMSでも考え方は 同じです。 ※実は「データベース」にも色々な種類があるのですが、今日の話の中では、SQL を使う「リレーショナルデータベース(RDBMS)」を対象とします
9. 「データベース」って どんなソフトなの？ 正確には「リレーショナル・データベース管理システム」って言います
10. データベースの「実体」 Windows上で動くMySQLを例にすると、「MySQL データベースサーバ」の実体は、 mysqld.exe という実行ファイルが単に起動しているだけです。 （通常はウィンドウを持たずにバックグラウンドで動 作します） DBMSごとに定めたポートで、接続されるのをひたす ら待ち続けます（MySQLは 3306番ポート）
11. データベースの「使用」(1) • データベースを使いたい場合は、一般に、RDBMSと会話するルール（プロ トコル）を知っているライブラリを使って、接続をします。
12. データベースの「使用」(2) • 実際には、あなたの作るプログラムの中で、そのライブラリを読み込ん で使用し、DBMSへの命令を送ったり結果を受け取ったりする使い方が 多いです。
13. データベースの「データ入れのハコ」 • データを保存する場所として、データベースが管理 するファイル内で「スキーマ」と呼ばれる小部屋を 作成します。(最初は何もないので自分で作ってい きます） • スキーマは必ずしもファイルシステムのフォルダと 対応するものではなく、RDBMSごとに工夫された 形式となっています。
14. データベースの「データのハコ」 • 実際にデータを登録するのは「テーブル」と 呼ばれるハコになります。 • テーブルは、スキーマの中に作ります。 • RDBMSのデータ保管に関する、スキーマや テーブルなどの物理的な仕組みについては、 基本的に「ブラックボックス」と捉えて良い です。良きに計らってくれます。 （ものすごく知り尽くしているレベルでの対応が必 要な時に意識することもありますが、それは最先端 の対応の話）
15. データベースとの「会話」 ここまでの話の全体像 • クライアントからは 「SQL」という言語を 使って、データベースの 操作をします
16. データベースに「接続」はできた！ じゃぁ、データベースとお話をしよう SQLで データベースさんと会話しよう
17. データベースを初めて学ぶときの2つのキーワード • テーブル • どんなテーブルを作るのか考える • 実際の作り方(命令＝SQL)を知る • SQL • RDBMSに対する様々操作を行う言語 • コレを知らないとなんの操作もできない
18. 立場別「データベース」との向き合い方 仕事上の立場により、DBMSとの接しかたもさまざま • エンジニア（開発者） • あらゆるSQLを駆使する • テーブルを作ったり、どのようなテーブルを作るかの設計 • RDBMSそのものの管理。配置やメモリ等のパラメタ設定など。運用方法 （バックアップなど）の方針策定など • 非エンジニア • すでに存在するテーブルからの、データの抽出や集計など
19. SQLとテーブル
20. テーブル • データを入れる「ハコ」 • あらかじめ登録する項目が決まっている（「列」または「カラム」と呼ぶ） • 列には「型」を定めておいて、その型に合致したデータを登録できる • 1件のデータは「行」または「レコード」と呼ぶ • このテーブルにデータを登録したり、テーブルからデータを検索したり集計 したりの操作を行える 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 数値 日付 文字列 数値 数値
21. テーブルの「型」 • RDBMSにより、少し異なる部分があります。お使いのマニュアルを参照 してください。 • MySQLの場合は以下のURLに記載されています • https://dev.mysql.com/doc/refman/8.0/en/data-types.html
22. SQLとは • RDBMSを操作するための専用言語 • エス・キュー・エル と読む • Structured Query Language • ISOで標準化されている。 • SQL86/SQL89/SQL92/SQL:1999/SQL:2003/SQL:2008/SQL:2016 など • 1992年のSQL92 (ISO 9075-1992) で基本的な仕様はほぼ完成 • 冒頭で紹介した、様々なRDBMSがこの標準にできるだけ従って開発されてい るので、RDBMSの種類に依らず同じ構文を使用可能 • 実際にはある程度の方言はある（とは言え、かなり同じと言って良いと思う）
23. コラム： "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.
24. SQLでできること • データベース自体への操作  スキーマ作成／破棄  テーブル作成／破棄／変更  その他DBオブジェクトの作成／破棄／変更 • テーブルのデータに対する操作  データの登録（行追加）  既存データの修正（更新・変更）  既存データを削除  既存データの取出し（抽出、集計） • データの制御に関する指示  トランザクション制御 ※今日はこの中の一部のみ紹介します
25. SQLでできること • データベース自体への操作  スキーマ作成／破棄  テーブル作成／破棄／変更  その他DBオブジェクトの作成／破棄／変更 • テーブルのデータに対する操作  データの登録（行追加）  既存データの修正（更新・変更）  既存データを削除  既存データの取出し（抽出、集計） • データの制御に関する指示  トランザクション制御 ※今日はこの中の一部のみ紹介します
26. SQLでできること ここから、具体的なSQLの構文を紹介していきます。 が 限られた時間ですので、 「こんな指示をすると」 「こんなことができる」 という雰囲気をつかんでもらえればと思います。
27. スキーマ(データベース) • スキーマ ＝ テーブルを入れるハコ • MySQLでは CREATE DATABASE文 • RDBMSによりスキーマやデータベースの考え方が少し異なります CREATE DATABASE mysystem; [例]
28. テーブルの作成と破棄 • CREATE TABLE 文で作る • 列名と、列の型、列の性質(制約)を羅列する CREATE TABLE items ( item_id INTEGER PRIMARY KEY, item_name VARCHAR(10) NOT NULL, price BIGINT, zaiko INTEGER, created_at DATETIME ); [例]テーブルの作り方 ※データ型は、各RDBMSのマニュアルを参照してください。 DROP TABLE items; [例]テーブルの破棄の仕方 今日は説明しませんが： • 既存テーブル定義の「変更」もできます（ALTER 文） • テーブル以外にも色々なオブジェクトがあります。CREATE文で作ります
29. SQLでできること • データベース自体への操作  スキーマ作成／削除  テーブル作成／削除／変更  その他DBオブジェクトの作成／削除／変更 • テーブルのデータに対する操作  データの登録（行追加）  既存データの修正（更新・変更）  既存データを削除  既存データの取出し（抽出、集計） • データの制御に関する指示  トランザクション制御 ※今日はこの中の一部のみ紹介します この4つが データの 基本操作！
30. データ操作：データ登録 • INSERT文を使います • 1件ずつ、登録します [例] INSERT INTO items (item_id, item_name, price, created_at) VALUES (12, '商品1', 150, '2022-09-07'); INSERT INTO items VALUES (12, '商品2', 380, 100, '2022-09-07'); CREATE TABLE items ( item_id INTEGER PRIMARY KEY, item_name VARCHAR(10) NOT NULL, price BIGINT, zaiko INTEGER, created_at DATETIME ); 先程 このSQLで 作ったテーブルに データを追加します
31. その他のデータ操作を学ぶ前に... テーブルに登録済のデータに対する操作を学んで行くために • 「どの行のデータ」を操作したいのかを指示する方法を学ぶ必要があり ます • Excelなどのスプレッドシートのように「123行目のデータ」という概念 は、ない • ということで・・・・（つづく） （削除／更新／抽出）
32. 対象の「行」を特定する方法 • 行を特定する方法を習得するのが大切！ • 格納データには、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
33. 「行」を指定する方法 • 「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
34. データ操作：データ削除 • DELETE文を使います • 行を特定する条件は、WHERE句で指定します DELETE FROM ITEMS WHERE CREATED_AT <= '2021-12-31'; [例]
35. データ操作：データ更新 • UPDATE文を使います • 行を特定する条件は、WHERE句で指定します • 変更したいカラムと値の組を SET句で指定します UPDATE ITEMS SET AMOUNT = 20 WHERE ID=13; [例]
36. データ操作：データ取り出し(抽出、検索、集計) • SELECT文を使います • 行を特定する条件は、WHERE句で指定します • 取得したい列を SELECT 句に指定します • SELECT句には計算式や関数を使用した加工を行うこともできます • 取得列には別名を付けることができます（ASは省略可能） SELECT ID, ITEM_NAME, AMOUNT FROM ITEMS WHERE ID=13; [例] SELECT ID, SUBSTR(ITEM_NAME,3,4) AS NAME_PART, TYPE1_AMT + TYPE2_AMT AS GOUKEI FROM ITEMS WHERE ID=13; [例]
37. 忘れないで！取得順序指定！ • RDBMS内に格納されているデータには（Excelのシートとは違って）順 序はありません。「何行目」という指定がないことからも分かりますね。 • SELECT文で取得したい行を指定する際、どんな順序で取得したいかを 指定します。（指定しないと、順序は「不定」となります） • 順序指定は ORDER BY 句を使用します。昇順(小さい順)は ASCを、降 順(大きい順)は DESCを指定します。（ASCは省略可能） SELECT ID, ITEM_NAME, AMOUNT FROM ITEMS WHERE ID=13 ORDER BY CREATED_AT DESC;
38. データ抽出：集計 • 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; CATEGORY _ID cnt goukei 1 1 21 2 2 126 3 1 3 5 2 10
39. データ抽出：複数テーブルから • 多くの場合、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]
40. テーブルの「結合」 • 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]
41. テーブルの「結合」 • 左側のテーブルを基準に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
42. テーブルの別名 • 複数のテーブルを使用する際は、各カラムがどのテーブルのものかを修 飾する必要があります。 • カラムの羅列をする際など、結構長いので、短めの別名を付けることも 多いです。 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;
43. SQLはさらに深く広い • 速度（パフォーマンス） • 良いハコ（テーブル設計） • 多彩な表現 • サブクエリ • CASE式 • Window関数 • CTE ひとつひとつ学んで、 できるようになっていく楽しさがあります。 ぜひ、データベース／SQLの学びに一歩を踏み出してください
44. 自分で試してみたくなった人のために 「第一歩」を踏み出す時に迷子になりやすいあなたのためのガイド • 何のソフトを使うかを決める • インストールする • （必要ならば初期ユーザ等の設定をする） • 接続してみる • スキーマ（データベース）を作成してみる • スキーマの中に入って(*1) 、テーブルを作ってみる • テーブルにデータを何件か登録してみる • 登録したデータを見てみる • そのうち1件を削除してみる • 登録したデータの一部の値を更新してみる • 登録したデータを「集計」してみる • 「コード」的なものを別テーブルとして作成できそうなデータであれば、作 成して、テーブルの結合にもトライしてみる (*1)ちょっと正確な表現ではありませんがご了承ください
45. 自分で試してみたくなった人のために 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; インストール方法によっては、 初回接続後にパスワードを変更 する必要があるかもしれません。 画面の表示や、ドキュメントを よく見て対応しましょう。
46. 自分で試してみたくなった人のために 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;
47. ぜひご自身で触ってみてください • これからの方は、第一歩を • 以前あきらめてしまった方は、新たな一歩を ぜひ踏み出してください。 データベース、、、、というかデータの取り扱い、 とても楽しいはずです！！