本記事の概要
こちらは2021.04.10に株式会社xhackさんの勉強会「【オンラインハンズオン】はじめてのSQL」を主催させて頂いた時に作成した資料です。
筆者はバックエンド中心のエンジニアとしてデビューしたてのピヨピヨではありますが、
データベースが好きでSQLをちゃんと理解したい！と思っていた際
よく参加させて頂いているxhackさんの勉強会で主催やりませんか？と
代表の松田さんにお声を掛けて頂いたのがきっかけでやらせて頂きました。感謝！😊
はじめに
1. 今回のゴール
- SQLで基本のデータベース操作（参照・挿入・更新・削除）を行えるようになる
- SQLでの代表的な構文の意味を理解できる
2. ざっくりSQL
- データベースを操作したり、中のデータを定義するための言語
参考：「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典 - SQL
3. ざっくりデータベース
- データを整理して格納しておき、必要に応じて取り出してこれる便利なアレ（雑）
テーブル：表 ／ カラム：列 ／ レコード：行
フィールド：一つの要素
- Excelでいうとこの、シート・列・行・セル ってとこですね！
参考：【データベース用語まとめ】テーブル、カラム、フィールド、レコードとは？
ブラウザツールを使ってSQLを書いてみよう！
今回使うツール：SQLite online
1. 今回使うデータベースを準備しよう（CREATE TABLE & INSERT）
データベースを作って初期データを挿入する
- SQLite online の画面左側のメニューから「PostgreSQL」を選択 ＞ Click to Connect
- 下記を画面内にコピペして、左下の「Run it（F8）」を押下
CREATE TABLE menus
("id" int, "category" varchar(8), "name" varchar(20), "price" int)
;
INSERT INTO menus
("id", "category", "name", "price")
VALUES
(1, '定食', '焼きそば定食', 680),
(2, '定食', 'ハンバーグ定食', 680),
(3, '定食', 'ミックスフライ定食', 800),
(4, '単品', 'カレーライス', 600),
(5, '単品', 'オムライス', 800),
(6, '単品', 'シーザーサラダ', 580),
(7, 'デザート', 'ケーキ', 480),
(8, 'デザート', 'プリンパフェ', 600),
(9, 'ドリンク', '生ビール', 380),
(10, 'ドリンク', '焼酎', 500),
(11, 'ドリンク', 'コーヒー', 300)
;
- 参考：好きなデータのテーブル情報を簡単に用意する
SQL fiddleの右上メニュー「Text to DDL」からデータを入力→「Append to DDL」押下で
画面内に上記のようなCREATE TABLE、INSERTのSQLが生成されます！べんり😊
※ 具体的なやり方は参考記事を参照
2. はじめてのSQL（全ての列を取得してみよう）
SELECT * FROM menus
- 「＊」（アスタリスク）＝ 全ての列
- SELECT 列名 FROM テーブル名 で 指定した列名の列を取得
SQLの基本ルール
1. 改行・スペースの挿入
- 文の途中に改行を入れる事ができる
- 行の先頭・途中に半角のスペースを入れる事ができる（長くなって見づらい時に便利！）
改行してないver.
SELECT category, name, price FROM menus WHERE price >= 500
改行したよver.
SELECT category, name, price
FROM menus
WHERE price >= 500
💡 改行する事で文の区切りがわかりにくくなる場合は、
1つのSQL文の末尾にセミコロン（;）を付けて文の区切りを明示する事も可能
2. コメント記法
- ハイフン2つ（- -）から行末まではコメントになり、無視される（命令とみなされない）
- /* から*/ まではコメントとなり、無視される（命令とみなされない）
コメントを記述したSQL
/* レストランPUIPUI 定食メニュー表示用SQL
作成日：2021.04.01 */
SELECT category, name, price
FROM menus
WHERE category = '定食' -- 定食のみで絞り込み
3. 予約語の記述
SELECT や WHERE などの命令に使う単語は、SQLとして特別な意味を持つ「予約語」である
→ テーブルや列名などには使用不可-
予約語を記述する際、大文字と小文字の区別はない
→ どちらで書いても同じように動作するが、判別しやすい書き方がGood※ 列名やテーブル名において大文字・小文字を区別するかは、DBMSの種類やOS・設定などによる
4. クォーテーションの使い方
PostgreSQLなどの標準SQLでは、下記の仕様になっているそう
- シングルクォーテーションで囲う：文字列定数として扱う
- ダブルクォーテーションで囲う：カラム名として扱う
'value1'はvalue1という文字列として捉え、"value1"はvalue1というカラムの名前として捉えられます。
ちなみにMySQLだけ独自の仕様になっており、上記とは異なるようです。
参考：えっ、まだPostgreSQLでダブルクォーテーション使ってるの？
データ型とリテラル
1. データ型
- テーブルの列ごとに指定された、格納可能なデータの種類
（列にはデータ型で指定された種類の情報しか格納できない）
テーブル「molcars」の例
※ モルカーたちの誕生日・年齢はテキトーですw
| birthday | name | age |
|---|---|---|
| 2011-04-01 | シロモ | 10 |
| 2010-02-14 | ポテト | 11 |
| 2008-03-25 | テディ | 13 |
birthday： DATE（日付型）
name： VARCHAR（10）（可変長文字列型、最大10バイト）
age： INTEGER（整数型）
💡利用可能なデータ型はDBMS製品によって異なるが、上記3つは基本的に必ずある
- 冒頭でCREATE TABLEした時も、各列のデータ型を指定してましたね😊
CREATE TABLE menus
("id" int, "category" varchar(8), "name" varchar(20), "price" int)
;
2. リテラル
- SQLの中に書き込まれたデータそのものの事
- 冒頭でCREATE TABLEした後、データの中身をINSERTで挿入してもらいましたが
idとpriceは数値そのまま・categoryとnameは「 ' 」でくくられています。なぜでしょう？？
INSERT INTO menus
("id", "category", "name", "price")
VALUES
(1, '定食', '焼きそば定食', 680),
(2, '定食', 'ハンバーグ定食', 680),
(3, '定食', 'ミックスフライ定食', 800),
(4, '単品', 'カレーライス', 600),
(5, '単品', 'オムライス', 800),
(6, '単品', 'シーザーサラダ', 580),
(7, 'デザート', 'ケーキ', 480),
(8, 'デザート', 'プリンパフェ', 600),
(9, 'ドリンク', '生ビール', 380),
(10, 'ドリンク', '焼酎', 500),
(11, 'ドリンク', 'コーヒー', 300)
;
💡 「 ' 」でくくられたリテラル＝文字列情報、くくられていないリテラル＝数値情報 として扱われる！
/* 2つのリテラルの違い */
123 -- 「123」という数値（数量や金額）を表す
'123' -- 1・2・3という3つの文字列の並びを表す
SQL4大命令
1. SELECT：テーブルから目的のデータを取得
SELECT 列名 FROM テーブル名
（ WHERE 取得条件 ） -- なくてもOK
（ その他の修飾 ） -- なくてもOK
- 複数の列を取得してみよう
SELECT name, price FROM menus
- ASで別名を定義してみよう
SELECT name AS メニュー名, price AS 価格
FROM menus AS メニュー
💡 わかりやすく短い列名を付ける事ができる！
※ Oracle DBではテーブルの別名を付ける際「AS」を記述してはならないルールとなっているそう
2. UPDATE: すでにテーブルに存在するレコードを書き換える
UPDATE テーブル名
SET 列名1 = 値1, 列名2 = 値2......
（ WHERE 取得条件 ） -- どの行を書き換えるか（無いと全ての行の値が上書きされてしまう）
- テーブルの特定の行を更新してみよう
UPDATE menus
SET price = 100
WHERE name = '生ビール'
考えてみよう 🤔
レストランPUIPUI、お客さんを増やしたいので定食500円均一キャンペーンをやります！
メニューの更新が必要です。
どんなUPDATE文を書けば、全部の定食を500円に変更できるでしょうか？？
上記を参考に、各自SQLを書いて動かして試してみましょう〜✏️
SQLite onlineでの検証のポイント
- UPDATE文実行後は、左メニューのテーブル名（menus）をWクリックで
下記コードが実行されて更新後のテーブルが表示されます（反映を確認できる）
SELECT * FROM menus
- UPDATE後のidの並び変更が気になる人は、下記コードを実行すれば昇順に変わります
SELECT * FROM menus ORDER BY id -- id順（昇順でソート）
※ ORDER BYについては第二回で説明します！
※ コメント部を入れたまま実行するとエラーになるので、コピー後は必ず外して実行してくださいね
↓
↓
↓
- 【回答】全部の定食を500円に変更しよう
UPDATE menus
SET price = 500
WHERE category = '定食'
3. DELETE: すでにテーブルに存在するレコードを削除する
DELETE FROM テーブル名
（ WHERE 取得条件 ） -- どの行を削除するか（無いと全ての行が削除されてしまうので注意！）
💡 特定の行を削除する命令なので、列を指定する必要はなし
→ DELETEとFROMの間に何も書かないでOK
（SELECTの場合は「SELECT 列名 FROM テーブル名」としていましたよね😋）
- テーブルの特定の行を削除してみよう
DELETE FROM menus
WHERE name = 'シーザーサラダ' -- シィィザーーァァァッ！！！！！
💡 条件の指定の仕方によっては複数行の削除も可能です。
考えてみよう 🤔
レストランPUIPUIの常連さんは近所のおっちゃん👴（推定50〜60代）が中心です。
そのため、デザートのメニューが全く売れず在庫が無駄になるので、メニューからなくす事にしました。
（完全なる偏見です・・・デザートがお好きなナイスミドルの方、ごめんなさいｗ）
SQLを書いて、メニューからデザート全てを削除してみましょう！
↓
↓
↓
- 【回答】デザートの行を全て削除
DELETE FROM menus
WHERE category = 'デザート'
4. INSERT: テーブルに新しいレコードを追加する
INSERT INTO テーブル名
（列名1, 列名2, 列名3…） -- 全ての列に値を追加する場合は省略可能
VALUES （値1, 値2, 値3…）
💡 行を新たに追加するので、WHERE句での条件指定は不要
💡 VALUES句で追加する値は、2行目で指定した列名と順番・数・データ型をぴったり合わせる必要あり
（全ての列に値を追加する場合は、全ての列分の値を渡さないとだめ🈲）
考えてみよう 🤔
- menusに新しいメニューを追加してみよう
/* このSQL文には間違いがあります。どの部分でしょう？
試しに実行してみてエラーを確認した後、正しいコードで書き直して実行してみてください！ */
INSERT INTO menus
("id", "category", "name", "price")
VALUES
('12', '980', '天津チャーハン餃子唐揚げ焼き小籠包定食')
↓
↓
↓
【回答】正しくはこうですね！😊
INSERT INTO menus
("id", "category", "name", "price")
VALUES
(12, '単品', '天津チャーハン餃子唐揚げ焼き小籠包定食', 980)
この書き方でもいけます。（全ての列に値を渡すので、列名は省略可能）
INSERT INTO menus
VALUES
(12, '定食', '天津チャーハン餃子唐揚げ焼き小籠包定食', 980)
余裕のある方は、お好きなメニューを追加してみてくださいね！🍽❣️
🍵 4大命令のまとめ 🍵
- SELECT（検索） : テーブルから目的のデータを取得する
- UPDATE （更新） : テーブルに存在するレコードを書き換える
- DELETE（削除） ： テーブルに存在するレコードを削除する
- INSERT（追加） ： テーブルに新しいレコードを追加する
データの絞り込み（WHERE句）
1. WHERE句の基本
- 処理対象の行の絞り込みに必要 → WHERE句で指定しないと「全ての行」が対象になる
- SELECT・UPDATE・DELETEで使用可能 → INSERTでは使用しない
- WHEREの後には条件式を書く → 絞り込みの条件に合った「正しい条件式」を書く ←これ重要！！
じゃあ、どんな条件式を書けばいいの？👀
👉 結果が必ずTRUE か FALSE（真 か 偽）になる条件式を書こう
例）価格が600円より大きい行のname、priceを取得
SELECT name, price FROM menus
WHERE price > 600
DBMS（データベースマネジメントシステム）さんは、上記のSQL（命令）を受け取ったら
テーブルの中を１行１行、順番に見て「該当する（TRUE）」か「該当しない（FALSE）」かを判定し
TRUEの行のみの値を見せてくれます。
※ エンヤ婆・・・ではなくDBMSさんのつぶやき
👵 ＜ オムライスは 800円… price ＞ 600に該当するですじゃ！見せるのですじゃ
👵 ＜ カレーライスは600円… price ＞ 600に該当しないですじゃ！オロロ〜ン これは無視ですじゃ
2. 正しく使おう！比較演算子
- 比較演算子一覧
| 演算子 | 意味 |
|---|---|
| ＝ | 左右の値が等しい |
| ＜ | 左辺は右辺より小さい |
| ＞ | 左辺は右辺より大きい |
| ＜＝ | 左辺は右辺の値以下 |
| ＞＝ | 左辺は右辺の値以上 |
| ＜＞ | 左右の値は等しくない |
3. NULL
- NULL ＝ そこに何も格納されていない、未定義であることを表す
- 数字の0や空白文字（' '）とも異なる
NULL、0、空文字の違いを理解するために以下を実行してみよう
INSERT INTO menus
("id", "category", "name", "price")
VALUES
(13, 'サービス', 'スマイル', 0), -- スマイル0円
(14, 'サービス', '', 30000) -- 一体どんなサービスが・・・？？？
INSERT INTO menus
("id", "category", "name")
VALUES
(15, 'サービス', 'ボードゲーム貸出') -- priceを設定しない＝未定義＝NULLとなる
NULLってどんな時に使うの？ 👀
今回のテーブル「menus」＝レストランのメニューのため、メニュー名や価格を定義しないというのは
考えにくいですが
たとえばユーザー登録時の住所の情報を格納するテーブル「addresses」があったとして
「郵便番号」や「都道府県」の列は必ず値が存在するはずですが、
「マンション名・部屋番号」の列は一戸建て🏠のユーザーの場合格納できるデータがないかと思います。
そんな場合にNULLとしたり、空文字（' '）を格納したりします。
（この辺は設計によって変わってくるかと思います）
WHERE句でNULLかどうかを判定する
考えてみよう 🤔
- priceがNULLの行のname、priceを抽出してみよう
↓
↓
↓
【回答】
SELECT name, price FROM menus
WHERE price IS NULL
- priceがNULLではない行のname、priceを表示する時は・・・
SELECT name, price FROM menus
WHERE price IS NOT NULL
NULLは比較演算子「＝」では判定できない！
これ、私もよく間違えてました。。。笑
- 理由
比較演算子「＝」や「＞」は値同士を判定するためのもの。
値ではないNULLを比較すると、不明な結果であるUNKNOWN（不明・計算不能）となってしまう。
💡 booleanの値（TRUE または FALSE）は = で判定可能です!
4. BETWEEN
ある範囲内に値が収まっているかを判定する
式 BETWEEN 値１ AND 値２
💡 値１以上値２以下の範囲なので、値１と値２ぴったりのデータも含まれる！
考えてみよう 🤔
- priceが500円以上800円以下のcategory, name, priceを抽出してみよう
↓
↓
↓
【回答】
SELECT category, name, price FROM menus
WHERE price BETWEEN 500 AND 800
【参考】基本情報の問題でも、日付の範囲でデータを絞り込むやつがよく出題されます
/* 設問 1
リコールの対象となる電子部品の出荷先の顧客番号，顧客名, 出荷番号，出荷日，出荷数を
顧客番号の昇順に表示する。
リコールの対象となる電子部品の部品番号は“007551”で
出荷日は2015年1月10日から2015年1月20日までである。
次のSQL文の a に入れる正しい答えを，解答群の中から選べ。 */
SELECT 顧客表.顧客番号, 顧客表.顧客名,
出荷表.出荷番号, 出荷表.出荷日, 出荷表.出荷数
FROM 顧客表, 出荷表
WHERE 出荷表.顧客番号 = 顧客表.顧客番号 AND
出荷表.部品番号 = '007551' AND
[ a ]
ORDER BY 顧客表.顧客番号
------
/* a に入る解答 */
出荷表.出荷日 BETWEEN '20150110' AND '20150120'
5. IN
値がカッコ（）内のリストのいずれかに合致するかどうかを判定する
式 IN （値１, 値２, 値３...）
💡 ＝（イコール）だと1つの値との比較しかできないけど、INならたくさんの値との比較ができる！
- カテゴリーが定食または単品のメニューを抽出してみよう
SELECT category, name, price FROM menus
WHERE category IN ('定食', '単品')
6. NOT IN
（）内の値のどれにも合致しない事を判定する
（この中にはナイヨ〜）
- カテゴリーが定食または単品以外のメニューを抽出してみよう
SELECT category, name, price FROM menus
WHERE category NOT IN ('定食', '単品')
7. LIKE演算子でパターンマッチング
〜 ドキッ❣️文字列さんの部分一致 〜
- LIKE演算子に使えるパターン文字
| パターン文字 | 意味 |
|---|---|
| % | 任意の0文字以上の文字列 |
| _ | 任意の1文字 |
- 「ライス」という文字列を含むメニューを抽出してみよう！（米🍚が食べたい）
SELECT name, price FROM menus
WHERE name LIKE ('%ライス%')
💡 前後に「%」＝ 0文字以上を想定しての検索なので、「ライス」を含むものは全て取り出せる
'%ライス'なら「蒸しライス」
'ライス%'なら「ライス大盛り」
'ライス_'なら「ライス丼」（後ろ１文字のみ）
みたいな・・・イメージできましたか？😊（米しかねえ）
考えてみよう 🤔
- nameに「焼き」という文字が含まれるメニューのname, priceを抽出してみよう
- 抽出できたら、その中の１つのnameをあなたの好きな料理に更新してみよう！
- できそうな人は、nameとpriceの両方を１文で更新してみてください✨
↓
↓
↓
【回答】
SELECT name, price FROM menus
WHERE name LIKE ('%焼き%')
🍤 ・🍤・ 🍤
UPDATE menus
SET name = '海老マヨ定食', price = 750
WHERE name = '焼きそば定食'
いざ！論理演算子使いこなしマスターへ
1. AND
AかつB（2つの条件式両方に一致する場合のみTRUEとなる）
条件式A AND 条件式B
- 金額が300円より高いドリンクを600円に更新してみよう（値上げ・・・！！💸）
UPDATE menus
SET price = 600
WHERE category = 'ドリンク'
AND price > 300
2. OR
AまたはB（2つの条件式のどちらかに一致するならTRUEとなる）
条件式A OR 条件式B
- OR検索を試してみよう（酒！飲まずにはいられないッ🍺）
SELECT name, price FROM menus
WHERE name = '生ビール'
OR name = '焼酎'
3. NOT
条件式に当てはまらない場合にTRUEとする
（「●●ではない」、「●●以外」を取り出したい時に便利！）
SELECT name, price FROM menus
WHERE NOT name LIKE ('%ライス%')
こうすると、nameに「ライス」が含まれないメニューのみが抽出されますね！
4. 要注意ッ！論理演算子の優先順位
複数の論理演算子を使ったSELECT文を実行する時は、前から順番にではなく
優先順位の高い演算子から実行される🕊
定食または単品で、nameにライスまたはフライとつくメニューを抽出したい気持ち🍚🍤
（おいしいものは 脂肪と糖でできている）
下記を実行してみて結果を確認・・・
SELECT category, name, price FROM menus
WHERE category = '定食' or category = '単品' -- カテゴリーが定食または単品
AND name LIKE ('%ライス%') OR name LIKE ('%フライ%') -- かつ、名前にライスかフライを含むもの
さて、思い通りにいきましたか？？
↓
↓
↓
- 論理演算子の優先順位
| 順位 | 論理演算子 |
|---|---|
| 1 | NOT |
| 2 | AND |
| 3 | OR |
今回NOTはないので、まずANDが実行されて「単品かつ名前にライスを含むもの」が抽出された後
「定食」OR 「単品かつ名前にライスを含むもの」OR 「名前にフライを含むもの」の結果が返るため
狙いとは違うメニューになったかと思います。。。💧
どうやったら狙い通りのSQLを書けるのでしょう？ 👀
SELECT category, name, price FROM menus
WHERE ( category = '定食' or category = '単品' )
AND ( name LIKE ('%ライス%') OR name LIKE ('%フライ%'))
このように、カッコ（）で優先順位をあげてやれば おけまる水産ですね😍🐠！！
ぜひ実際に動かして確認してみてください✨
（カッコを全角で書かないよう注意してくださいね〜）
おわりに
これまで使った命令＆演算子を組み合わせて
レストランPUIPUIのメニューを好きなように変えまくっちゃいましょう 😆
（ラーメン屋・焼肉屋・大衆居酒屋などなど 業種転換しちゃってOKです🍜）
👏😆 お疲れさまでしたァン！！💞