Lock in $30 Savings on PRO—Offer Ends Soon! ⏳

これから始める人のためのデータベース超入門 / A super introduction fo...

sakaik
September 08, 2022

これから始める人のためのデータベース超入門 / A super introduction for Database beginners.

2022/09/07 に開催された 第51回ニフクラ エンジニアミートアップ にて「これから始める人のためのデータベース超入門 」と題してお話させていただきました、発表資料です。

https://fujitsufjct.connpass.com/event/258334

#エンジニアミートアップ

sakaik

September 08, 2022
Tweet

More Decks by sakaik

Other Decks in Technology

Transcript

  1. 自己紹介 • 坂井 恵(さかいけい) @sakaik データベース(特にデータの塊やデータの流れなど)が大好き • 日本MySQLユーザ会 副代表 •

    ゆるく、かつ、ゆるく運営しているユーザ会 • since 2000 • 有限会社アートライ 代表取締役 • since 1998 • 「データ構造」と「データの流れ」への幅広い理解 • 業務分析や問題解決に強み • いつでもお引き合いお待ちしています! • 株式会社 iCARE (協力先) • 「働くひとの健康を世界中に創る」 • 先日の OSC北海道にもスポンサー出展しました • 明日からの RubyKaigi でも"Ruby Sponsor" やってます!
  2. 日本MySQLユーザ会 • 2000年設立で、緩くMySQLに関する情報交換などを行っています。一応、 メーリングリストが「本体」です • 最近はときどきオンラインイベントをやっています。はやくオフライン イベント再開したいです... • 日本でのMySQL情報は Twitter

    の #mysql_jp ハッシュタグに集まって います。ユーザ会の情報もこちらで案内しています。MySQLの話題をツ イートするときにも、ご利用ください • 最近 ユーザ会のDiscordサーバ始めました。使い方模索中の段階です。 Twitterで「MySQL discord」で検索してください • 直近では、以下のイベントを予定しています • 9月中旬:DiscordでMySQLのおしゃべりをする会(「今夜も生でMySQL(仮題)」 • 9月22日:ClubMySQL (MySQL Shell for VSCodeのお話をたっぷり聞かせていただく予 定です)
  3. データベース(RDBMS) の ソフトウェア • 主に以下のようなソフトウェアがあります オープンソース • MySQL • PostgreSQL

    非オープンソース • Oracle Database • Microsoft SQL Server • IBM Db2 Database ※今日の説明は、主にMySQLを具体例として使いますが、他のDBMSでも考え方は 同じです。 ※実は「データベース」にも色々な種類があるのですが、今日の話の中では、SQL を使う「リレーショナルデータベース(RDBMS)」を対象とします
  4. テーブル • データを入れる「ハコ」 • あらかじめ登録する項目が決まっている(「列」または「カラム」と呼ぶ) • 列には「型」を定めておいて、その型に合致したデータを登録できる • 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 数値 日付 文字列 数値 数値
  5. SQLとは • RDBMSを操作するための専用言語 • エス・キュー・エル と読む • Structured Query Language

    • ISOで標準化されている。 • SQL86/SQL89/SQL92/SQL:1999/SQL:2003/SQL:2008/SQL:2016 など • 1992年のSQL92 (ISO 9075-1992) で基本的な仕様はほぼ完成 • 冒頭で紹介した、様々なRDBMSがこの標準にできるだけ従って開発されてい るので、RDBMSの種類に依らず同じ構文を使用可能 • 実際にはある程度の方言はある(とは言え、かなり同じと言って良いと思う)
  6. コラム: "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.
  7. SQLでできること • データベース自体への操作  スキーマ作成/破棄  テーブル作成/破棄/変更  その他DBオブジェクトの作成/破棄/変更 •

    テーブルのデータに対する操作  データの登録(行追加)  既存データの修正(更新・変更)  既存データを削除  既存データの取出し(抽出、集計) • データの制御に関する指示  トランザクション制御 ※今日はこの中の一部のみ紹介します
  8. SQLでできること • データベース自体への操作  スキーマ作成/破棄  テーブル作成/破棄/変更  その他DBオブジェクトの作成/破棄/変更 •

    テーブルのデータに対する操作  データの登録(行追加)  既存データの修正(更新・変更)  既存データを削除  既存データの取出し(抽出、集計) • データの制御に関する指示  トランザクション制御 ※今日はこの中の一部のみ紹介します
  9. スキーマ(データベース) • スキーマ = テーブルを入れるハコ • MySQLでは CREATE DATABASE文 •

    RDBMSによりスキーマやデータベースの考え方が少し異なります CREATE DATABASE mysystem; [例]
  10. テーブルの作成と破棄 • 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文で作ります
  11. SQLでできること • データベース自体への操作  スキーマ作成/削除  テーブル作成/削除/変更  その他DBオブジェクトの作成/削除/変更 •

    テーブルのデータに対する操作  データの登録(行追加)  既存データの修正(更新・変更)  既存データを削除  既存データの取出し(抽出、集計) • データの制御に関する指示  トランザクション制御 ※今日はこの中の一部のみ紹介します
  12. データ操作:データ登録 • 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 ); さっき作った このテーブルに データを追加します
  13. 対象の「行」を特定する方法 • 行を特定する方法を習得するのが大切! • 格納データには、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
  14. 行の特定の方法 • 「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
  15. データ操作:データ取り出し(抽出、検索、集計) • 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; [例]
  16. データ抽出:集計 • 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
  17. データ抽出:複数テーブルから • 多くの場合、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]
  18. テーブルの「結合」 • 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]
  19. テーブルの「結合」 • 左側のテーブルを基準に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
  20. テーブルの別名 • 複数のテーブルを使用する際は、各カラムがどのテーブルのものかを修 飾する必要があります。 • カラムの羅列をする際など、結構長いので、短めの別名を付けることも 多いです。 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;
  21. SQLはさらに深く広い • 速度(パフォーマンス) • 良いハコ(テーブル設計) • 多彩な表現 • サブクエリ •

    CASE式 • Window関数 • CTE ひとつひとつ学んで、 できるようになっていく楽しさがあります。 ぜひ、データベース/SQLの学びに一歩を踏み出してください
  22. 自分で試してみたくなった人のために 「第一歩」を踏み出す時に迷子になりやすいあなたのためのガイド • 何のソフトを使うかを決める • インストールする • (必要ならば初期ユーザ等の設定をする) • 接続してみる

    • スキーマ(データベース)を作成してみる • スキーマの中に入って(*1) 、テーブルを作ってみる • テーブルにデータを何件か登録してみる • 登録したデータを見てみる • そのうち1件を削除してみる • 登録したデータの一部の値を更新してみる • 登録したデータを「集計」してみる • 「コード」的なものを別テーブルとして作成できそうなデータであれば、作 成して、テーブルの結合にもトライしてみる (*1)ちょっと正確な表現ではありませんがご了承ください
  23. 自分で試してみたくなった人のために 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; インストール方法によっては、 初回接続後にパスワードを変更 する必要があるかもしれません。 画面の表示や、ドキュメントを よく見て対応しましょう。
  24. 自分で試してみたくなった人のために 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;