今さら聞けない人のための「SQLデータベース」超入門 / OSC Hokkaido 2022_A super introduction to SQL database for beginners

2022/06/25 にオンラインで開催された「オープンソースカンファレンス北海道（OSC-Hokkaido or OSC-do）での、日本MySQLユーザ会枠での発表資料です。

https://event.ospn.jp/osc2022-online-do/

#mysql
#mysql_jp
#osc
#opensource

Transcript

1. 「SQLデータベース」 超入門 2022/06/25 日本MySQLユーザ会 坂井 恵 今さら聞けない人のための オープンソースカンファレンス2022 Hokkaido #osc22do #mysql_jp
2. この時間は • 宮原徹さんがOSCでよく開催していた「いまさら聞けない人のための」 シリーズに感銘を受けて、開催することにしました。 • 長いこと使われている技術は、入門の部分について聞ける機会がどんど ん減っていく ← 詳しい人は最先端の話に興味がある → だからこそ「これから始める人のための」枠が必要！ 【各分野のベテランのみなさんへ】 • これから始める人のための話題を、意識的に発表していきませんか • 次回のOSCで「いまさら聞けない」シリーズ、やってみませんか
3. この時間は データベース、特に「SQLを使うデータベース」の話をします • データベースの種類を知る • データベースの役割を知る • データを操作する命令文である SQL を知る 45分間で「データベースを完全に理解」して、サラサラと取り扱え るようになる・・・・わけがない。ですが、今後データベースを学 んでいく際の考え方のベースとなることを目指します！ ※特にMySQLだけの話をするわけではなく「データベース」全般のお 話をするので、MySQL話を期待していた方はごめんなさい。
4. 自己紹介 • 坂井 恵（さかいけい） @sakaik • データベース(特にデータの塊やデータの流れなど)が大好き • 日本MySQLユーザ会 副代表 • ゆるく、かつ、ゆるく運営しているユーザ会 • #mysql_jp ウォッチしてください。最近時々オンラインイベントやって ます • 有限会社アートライ 代表取締役 • since 1998 • 業務分析や問題解決に強み • その根幹にあるのが「データ構造」への理解 • 株式会社 iCARE (協力先) • 今回OSC北海道に初めてスポンサー出展 • この次の枠でセミナーあります。ぜひ聞いてください！
5. データベースとは • IT以外を含む全世界的には： データを集めたもの • IT的には： データベースを構築してデータを扱えるようにしたソ フトウェアのこと • いろいろなタイプのソフトがあります。 • RDBMS: 「SQL」という専門の構文を使ってデータを出し入れす る「リレーショナルデータベース管理システム」の話をします 「SQLデータベース」超入門
6. RDBMS • リレーショナルデータベース管理システム • ( Relational DataBase Management System ) • 「テーブル」と「SQL」の理解を目指そう • データの保管と出し入れに特化した「データ取り扱いの専門シス テム」
7. RDBMS(データベースソフト)の種類 世の中には、様々な「RDBMS」と呼ばれるソフトがあります。 主なもの（主観）： • MySQL • PostgreSQL • Oracle Database • Microsoft SQL Server • Db2
8. なぜRDBMSを使うの？ 必要ないなら、使わなくてもいい！ • 新しいこと覚えなきゃいけないし • バージョン管理も面倒だし • 余計なもの使ったら複雑になるし ちょっとしたデータなら、自分でファイルを読み書きすればいい じゃない → 使うと便利だから使う！ ということを理解して。
9. RDBMSを使うことで得られるメ リット •シンプル •高速 •安全
10. RDBMSを使うことで得られる メリット ▪シンプル • データの出し入れと管理を専門システムに任せることで、プログラムのコー ドをシンプルにできる ▪高速 • データが増えても • 同時利用者が増えても ▪安全 • 「壊れる」からの安全 • 「悪い人」からの安全
11. そもそもデータベースってどこで使われるの？ • シンプルな例(サーバは機能によって複数台に分けられることがあります）
12. そもそもデータベースってどこで使われるの？ • データ保管のためのワクをあらかじめ定義（テーブル） • データ配置を整理して高速に取り出せるように • バックアップなどの機能 • プログラムから命令する  データの検索・取り出し  データの登録  データの変更  データの削除
13. そもそもデータベースってどこで使われるの？ • プログラムから命令する  データの検索・取り出し  データの登録  データの変更  データの削除 • データ保管のためのワクをあらかじめ定義 • データ配置を整理して高速に取り出せるように • バックアップなどの機能 SQL テーブルなど
14. テーブル • テーブルの例（イメージ） • 1行が1件のデータ(レコード) • 各列（「住所」や「氏名」など）には、その列に定められた内容だけを 登録する • Excelのシートにイメージは似ているけど、カーソルキーで移動しながら データ操作するわけではない
15. テーブル • テーブルの列には「型」を決めておく 数値 文字列 文字列 文字列 文字列 数値 日付
16. テーブルのデータには順序はない 目に見えるイメージ こういうイメージを 持つと良いかも→
17. テーブルは通常、いくつも作る/使う 顧客情報を格納するテーブル 商品情報を格納するテーブル 売上情報を格納するテーブル 担当者(社員)情報を格納するテーブル 例(イメージ） 店舗(支店)の情報を格納するテーブル
18. テーブルはスキーマ(データベース)の中に 作る ＲＤＢＭＳ スキーマ スキーマ スキーマ
19. テーブルは(本来)自由！ • だめなテーブル、とか言われるが、問題ないならいいじゃない！ • まずは自由に作ってみるところから • もちろん、本格的な業務で使うものは「プロ的」なテーブルの作 り方というのがあるので、ポイントを押さえた作り方を。 • 「こわれにくい」「遅くなりにくい」「取り出しやすい」など 様々な視点での「コツ」があって「テーブル設計」と言われてい る。これを学ぶのもDBのひとつの登竜門
20. 入門の頃は・・・ • 自分で１からテーブルを作るシーンよりは、すでにあるテーブル とデータ（他の人が作ってくれたもの）に対して操作をする人が 多いのでは？ • そこで使うのが、「SQL」（データベースのデータとかを操作する言語）
21. SQLとは • RDBMSを操作するための専用言語 • エス・キュー・エル と読む • Structured Query Language • ISOで標準化されている。 • SQL86/SQL89/SQL92/SQL:1999/SQL:2003/SQL:2008 など • 1992年のSQL92 (ISO 9075-1992) で基本的な仕様はほぼ完成 • 多くのRDBMSがこの標準にできるだけ従って開発されて いるので、RDBMSの種類に依らず同じ構文を使用可 能 • 実際にはある程度の方言はある（が、かなり「同じ」と言って良いほうだ と思います）
22. コラム： "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.
23. SQLでできること • データ操作(DML) • データの登録/削除/更新と抽出(検索・集計等) • INSERT/UPDATE/DELETE/SELECT • データ定義(DDL) • スキーマやテーブルなどデータベースオブジェクトの作成や破棄 • CREATE TABLE / DROP TABLE • CREATE DATABASE / DROP DATABASE • データ制御(DCL) • データ処理単位の制御 • BEGIN/COMMIT/ROLLBACK ← まずここを学ぼう！ ← データの更新をする 時には知っておくべきこ と（今日は割愛）
24. SQL命令の構文や例 ここからは、各種SQLの構文や例を紹介します 短時間で一度に学びきるのは難しいので、 • こんなことが • こんな風に書くことによって できるだな、という雰囲気を楽しんでください。
25. CREATE TABLE テーブル名 ( 列1の名前 データ型, 列2の名前 データ型, 列2の名前 データ型, : ); データ定義のSQL(DDL) (主に) スキーマやテーブルを作成したり、破棄したりする命令文。 CREATE文 / DROP文 / ALTER文 [構文] ※データ型については、使用するRDBMSのマニュアルを参照。 主に「文字列」「数値」「日付時刻」「その他」を意識すると理解しやすい https://dev.mysql.com/doc/refman/8.0/en/data-types.html ※各列には「NULL不許可」「PK」「(テーブル内での)重複不許可」などの制約を指定することもできる DROP TABLE テーブル名; ALTER TABLE テーブル名 ADD COLUMN 列名 データ型; など とりあえず自分でテーブルを作 れるようになるために、知って おこう
26. データ操作のSQL(DML) 更新系DML INSERT文: テーブル上にデータを登録(追加)する UPDATE文: テーブル上のデータの列値を変更する DELETE文: テーブル上のデータを削除する 参照系DML SELECT文: テーブル上にあるデータを抽出し、加工・集計等 を行う 次のスライドから、それぞれの概要を紹介します
27. INSERT文 • テーブルにデータ(レコード)を1件追加する文 INSERT INTO テーブル名(列名1, 列名2, …) VALUES (値1, 値2, …); [構文] INSERT INTO テーブル名 VALUES (値1, 値2, …); すべての列に値を入れる時は、列名の羅列は省略可能 INSERT INTO customer(cust_id, cust_name, kaiin_level, created_at) VALUES (123, 'Ano Hit', 4, '2022-06-25 14:23:00'); [例]
28. DELETE文 • テーブルから、指定行を削除する • 削除した行(レコード)は原則として元に戻せません • 必ずしも1行だけが消えるわけではなく「指定条件に合致した 行」すべてが一度に削除されます ※「行を特定する条件」の部分の詳細は、SELECT文のところでします。 DELETE / UPDATE / SELECT とも、この「行を特定する条件」の書き方が、 SQLを学ぶひとつの核になります。 ※ 今のところは、例えば「カラムAの値が5以上のもの」のように、列の値を指定するとイメー ジしておいてください（この例では カラムA＞5 のように指定する） DELETE FROM テーブル名 WHERE 行を特定する条件 [構文]
29. UPDATE zaiko SET amount = 123, updated_at = '2022-06-25 14:24:00' WHERE item_id = 10045; UPDATE文 • テーブル上のデータのうち指定した行について、カラ ムの値を変更する UPDATEテーブル名 SET カラム1 = 値1，カラム2 = 値2 .... WHERE 行を特定する条件; [例]
30. SELECT文 できること • テーブル上のデータから選択、射影の抽出を行う • 抽出したデータを加工する • 抽出したデータを集計する ※SELECTは INSERT/UPDATE/DELETEと比べても、非常に複雑なことができます。 SELECT文については、このあともう少し詳細に説明します SELECT カラム1, カラム2, .... FROM テーブル名 WHERE 行を特定する条件 ORDER BY 並べ替えに使用する列; [基本構文]
31. テーブル定義を知っていることが重要！ 見てきたように、データ操作のSQL文では ・テーブル名 ・カラム名 を記述します。 つまりSQL文を書くには対象となるテーブルの定義を知っている必 要があります。
32. SELECT文をもう少し解説 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 テーブルとデータ SELECT [取得したいカラムを羅列] FROM [取得元テーブル名] WHERE [行を特定する条件] ORDER BY [並べ替えに使用する列];
33. SELECT文をもう少し解説 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 テーブルとデータ SELECT [取得したいカラムを羅列] FROM [取得元テーブル名] WHERE [行を特定する条件] ORDER BY [並べ替えに使用する列]; SELECT ID, ITEM_NAME, CATEGORY_ID FROM ITEMS WHERE AMOUNT > 20 ORDER BY AMOUNT;
34. SELECT文をもう少し解説（きほん） 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 テーブルとデータ SELECT [取得したいカラムを羅列] FROM [取得元テーブル名] WHERE [行を特定する条件] ORDER BY [並べ替えに使用する列]; SELECT ID, ITEM_NAME, CATEGORY_ID FROM ITEMS WHERE AMOUNT > 20 ORDER BY AMOUNT; ほしい列を絞る ほしい行を絞る
35. SELECT文をもう少し解説（きほん） 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 ID, ITEM_NAME, CATEGORY_ID FROM ITEMS WHERE AMOUNT > 20 ORDER BY AMOUNT; ほしい行を絞る
36. SELECT文のきほん • テーブルから、取得したい行の指定（絞り込み）と、取得したい 列の指定を行うのが、一番基本的な使い方。 • 行の絞り込みは WHERE 句にて行う 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 ID, ITEM_NAME, CATEGORY_ID FROM ITEMS WHERE AMOUNT > 20 ORDER BY AMOUNT;
37. 列は、加工(計算等)をすることができる AMOUNTを2倍して得る ITEM_NAMEの先頭文字を得る例 (LEFT()関数を使用） 列の指定あれこれ SELECT ID, ITEM_NAME, AMOUNT * 2 FROM ITEMS WHERE AMOUNT > 20 ORDER BY AMOUNT; SELECT ID, LEFT(ITEM_NAME,2), AMOUNT FROM ITEMS WHERE AMOUNT > 20 ORDER BY AMOUNT;
38. カラムに別名 • カラムには取得した時に使用するための「別名」を付けられます • スペースを空けて別名を指定するだけです • 以下の例では AMOUNT * 2 という結果に対して amount_2baiという名前 を、LEFT()関数を通した結果に ITEM_NAME_TWOという別名をそれぞ れ付与しています。 SELECT ID, ITEM_NAME, AMOUNT, AMOUNT * 2 amount_2bai FROM ITEMS WHERE AMOUNT > 20 ORDER BY AMOUNT; SELECT ID, LEFT(ITEM_NAME,2) ITEM_NAME_TWO, AMOUNT FROM ITEMS WHERE AMOUNT > 20 ORDER BY AMOUNT; ID ITEM_NAME AMOUNT AMOUNT_2bai 8 iimono 27 54 6 Good goods 21 42 20 sample 99 198
39. 行の特定(WHERE)あれこれ 基本：カラムの値がどういうものを取得したいかを指定する 例） 組み合わせ(AND と OR) 例） • col1 = 1234 • col2 = 'ok' • col3 > 1000 • col4 <= 10000 • col5 IN (123, 124, 127, 130) • col6 <> '05' WHERE col1 = 1234 AND col2 = 'ok' AND col3 > 1000 AND (col4='15' OR col5 > '2021-01-01') • LEFT(col7, 2) = 'Ho'
40. 集計処理 (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 CATEGORY_ID cnt total 1 1 21 2 2 126 3 1 3 5 1 10 SELECT CATEGORY_ID, COUNT(*) cnt, SUM(AMOUNT) total FROM ITEMS WHERE AMOUNT > 0 GROUP BY CATEGORY_ID ORDER BY CATEGORY_ID GROUP BY 句でまとめに 使うカラムを指定し、 SELECT句で集計の関数を 指定する 主な集計関数 COUNT() SUM() MAX() MIN()
41. SELECT文の結果は常にテーブル！ 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 AMOUNT_2bai 8 iimono 27 54 6 Good goods 21 42 20 sample 99 198 SELECT 重要！ どんなに複雑な加工をしても、複雑な条件で抽出しても、絶 対に結果は「テーブルの性質を持ったもの」になります！
42. 複数テーブルからのSELECT • JOIN, サブクエリなど、複数のテーブルを絡めてデータ取得をす ることがあります。 • ありますっていうか、普通にやります。 • 単独テーブルからのSELECTを理解していれば！（＋結果は常に テーブル） • 「超」入門の範囲を超えるので今回は 紹介のみ
43. サブクエリとは ひとつのクエリの中の一部として、別のクエリを記述 することができます 日本語で「副問い合わせ」とも言います サブクエリを理解するには、クエリがどのような形の データを返すかを意識することが、近道です • 単一行？ 複数行？ • 単一のカラム？ 複数のカラム？ 43
44. サブクエリを記述できる場所 • SELECT xxx, [ココ((1)単一の値)] • FROM [ココ((3)複数列複数行)] • WHERE xxx = [ココ((1)単一の値)] • AND xxxx IN [ココ((2)単一列複数行)] 44 SELECT * FROM cust WHERE id IN ( SELECT DISTINCT cust_id FROM sales WHERE amount>1000 ); 例）
45. JOIN(結合)の例 • 以下のテーブルで prefテーブルのarea_id は areaテーブルの id に 紐付いているとします。 SELECT * FROM area; +----+--------------+ | id | area_name | +----+--------------+ | 2 | 東北地方 | | 3 | 関東地方 | | 5 | 近畿地方 | +----+--------------+ SELECT * FROM pref; +-----------+--------------+---------+ | pref_code | pref_name | area_id | +-----------+--------------+---------+ | 06 | 山形県 | 2 | | 12 | 千葉県 | 3 | | 13 | 東京都 | 3 | | 27 | 大阪府 | 5 | | 30 | 和歌山県 | 5 | +-----------+--------------+---------+ 45
46. JOIN(結合)の例 • 以下のテーブルで prefテーブルのarea_id は areaテーブルの id に 紐付いているとします。 SELECT * FROM area; +----+--------------+ | id | area_name | +----+--------------+ | 2 | 東北地方 | | 3 | 関東地方 | | 5 | 近畿地方 | +----+--------------+ SELECT * FROM pref; +-----------+--------------+---------+ | pref_code | pref_name | area_id | +-----------+--------------+---------+ | 06 | 山形県 | 2 | | 12 | 千葉県 | 3 | | 13 | 東京都 | 3 | | 27 | 大阪府 | 5 | | 30 | 和歌山県 | 5 | +-----------+--------------+---------+ 46 SELECT p.pref_code, p.pref_name, a.area_name FROM pref p LEFT OUTER JOIN area a ON (p.area_id=a.id); 補足： カラムの時に紹介した「別名」は、 テーブル名に対しても同様に指定できます +-----------+--------------+--------------+ | pref_code | pref_name | area_name | +-----------+--------------+--------------+ | 06 | 山形県 | 東北地方 | | 12 | 千葉県 | 関東地方 | | 13 | 東京都 | 関東地方 | | 27 | 大阪府 | 近畿地方 | | 30 | 和歌山県 | 近畿地方 | | 40 | 福岡県 | NULL | +-----------+--------------+--------------+
47. この先にあるもの • もっと色々なSQLの表現 • ウィンドウ関数や • WITH句 • ソフトの扱いに関すること • MySQLならMySQLの扱い方がある • 更新処理の同時実行の安全性に関すること • トランザクションとか etc 今日知った基本知識をベースに、どんどんRDBMSを知ってください。
48. きょう学んだこと • データベースってどんな役割？ • テーブル • SQL でできること • テーブル作成 • データの登録・変更・削除 • データの抽出、加工、集計等 これからの学び方 • まずは自分で触る環境を作って、遊んでみよう • SQL基本構文は覚えよう • 行の絞り込みの書き方を覚えよう（WHERE) • 列の指定方法、加工方法を覚えよう • もっと進んだSQL（集計や結合など）をひとつずつ学んでいこう
49. 本日の OSC-do B会場 Now ! Next !
50. ご静聴ありがとうございました MySQL 情報は twitter の #mysql_jp でチェック！ 「SQLデータベース」超入門 今さら聞けない人のための