Upgrade to PRO for Only $50/Year—Limited-Time Offer! 🔥

プログラミングを書けるようになったら 次はSQLを覚えよう!いまさら聞けない人のためのSQL超...

sakaik
September 03, 2022

プログラミングを書けるようになったら 次は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

sakaik

September 03, 2022
Tweet

More Decks by sakaik

Other Decks in Technology

Transcript

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

    • ゆるく、かつ、ゆるく運営しているユーザ会 • #mysql_jp ウォッチしてください。最近時々オンラインイベントやって ます • 有限会社アートライ 代表取締役 • since 1998 • 業務分析や問題解決に強み • その根幹にあるのが「データ構造」と「データの流れ」への理解 • 株式会社 iCARE (協力先) • 先日の OSC北海道に初めてスポンサー出展 • 「働くひとの健康を世界中に創る」
  2. RDBMS • RDBMS:リレーショナル データベース 管理 システム (Relational Database Management System)

    • 簡単に言うと、「テーブル」と呼ぶ形式にデータを登録していく もの • SQL: RDBMS上のデータを操作したり、RDBMSそのものを操作 したりするための専用言語
  3. テーブルは自分たちで作る • 自分たちで取り扱いデータを格納するために、自分で、どんなテーブル を作るかを考えて、作ります。 • 学習中は、まずは自由に作ってみるところから! • だめなテーブルと言われものもあるが、問題ないならいいじゃない!の 精神で。 今後、問題に気付ける能力をつけていきましょう。

    • 本格的な業務で使うものは「プロ的」なテーブルの作り方というのがあ るので、ポイントを押さえた作り方を。 • 「こわれにくい」「遅くなりにくい」「取り出しやすい」など様々な視 点での「コツ」があって「テーブル設計」と言われている。これを学ぶ のもDBのひとつの登竜門
  4. SQL

  5. SQLとは • RDBMSを操作するための専用言語 • エス・キュー・エル と読む • Structured Query Language

    • ISOで標準化されている。 • SQL86/SQL89/SQL92/SQL:1999/SQL:2003/SQL:2008 など • 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. テーブルの作成 • STEP 1 どんなテーブルを作るか決める  テーブル名  どんな列を持つのか 

    列名  その列の型  その列が持つ制約(必須、重複不可等)
  8. テーブルの作成 • 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のマニュアルを参照してください。
  9. その他のDBオブジェクトの作成と削除(概要) • テーブル以外にもDBオブジェクトは色々あってな。  スキーマ  インデックス  プロシージャ 

    ビュー  トリガー etc • みんな「SQL」を使って作ったり削除したりするのじゃ。 • CREATE / DROP の他に ALTER 文により変更できる。
  10. データの登録 • テーブルに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');
  11. 削除/更新/抽出 を学ぶ前に... • 行を特定する方法を習得するのが大切! • 格納データには、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
  12. 行の特定の方法 • 「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
  13. データの削除 • 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テーブル
  14. 既存データの更新(変更) • 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
  15. データ検索(抽出)のきほん(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
  16. データ検索(抽出)のきほん(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
  17. データ抽出:集計 • 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
  18. データ抽出:複数テーブルから • 多くの場合、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]
  19. テーブルの「結合」 • 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]
  20. テーブルの「結合」 • 左側のテーブルを基準に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
  21. テーブルの別名 • 複数のテーブルを使用する際は、各カラムがどのテーブルのものかを修 飾する必要があります。 • カラムの羅列をする際など、結構長いので、短めの別名を付けることも 多いです。 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
  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;
  25. 日本MySQLユーザ会 • 2000年設立で、緩くMySQLに関する情報交換などを行っています。一応、 メーリングリストが「本体」です • 最近はときどきオンラインイベントをやっています。はやくオフライン イベント再開したいです... • 日本でのMySQL情報は Twitter

    の #mysql_jp ハッシュタグに集まって います。ユーザ会の情報もこちらで案内しています。MySQLの話題をツ イートするときにも、ご利用ください • 最近 ユーザ会のDiscordサーバ始めました。Twitterで「MySQL discord」 で検索してください • 直近では、以下のイベントを予定しています • 9月中旬:DiscordでMySQLのおしゃべりをする会(「今夜も生でMySQL(仮題)」 • 9月22日:ClubMySQL (MySQL Shell for VSCodeのお話をたっぷり聞かせていただく予 定です)
  26. SQLが分かるようになると楽しい • データの流れはシステムの血管 • SQLは自由!どんどん書いてみよう • クエリの書き方でかかる時間に文字通り「桁が違う」ことがある。楽しい! • 思いついたらプログラムできるのと同様に、思いついたら膨大なデータから 抽出とか集計とかできるようになれる(いま、オープンデータとかいっぱい

    あります) • ある程度データ操作ができるようになったら、  テーブルの作り方(テーブル設計)  データ操作をひとつのカタマリとして扱う重要性(トランザクション) あたりについて、調べてみると良いでしょう。