「ゼロからはじめるデータベース操作」を読んでSQLの基本を再復習した
新しい会社でWebエンジニアを初めて、2週間が経過しました。 Railsのコーディングとか、gitの操作だとかAWS周りとか毎日新しいことにチャレンジできてとにかく楽しいですね！！
今日は少しいつもとは違い、読書の防備録です。
今回読んだ本
SQLの入門書として、評判のいいミック先生の「ゼロからはじめるデータベース操作」です。
なぜ読んだか
現職で主に扱っている言語は、Ruby(RoR)とPHP(Symphony+Zendとか色々濃縮された香ばしいシステム)の２つです。
フレームワーク側でORマッパーがあるので、直のSQL文を書くことはほとんどありませんが、
裏側でどんなSQLが動いているのか仕組みを理解しておきたくて、再復習も兼ねて読んでみました。
(Kindleで購入してしまったため写真がありません。。次から紙媒体で買います。。)
なぜ「SQL第２版 ゼロからはじめるデータベース操作」か
高専時代にSQLに関する授業があったのですが、その際の教科書でした。
ほぼ演習課題をひたすら実施していく授業スタイルだったのですが、
その際にかなり分かりやすい参考書という印象があったため再購入しました。
(当時の教科書は、ITにとても興味がない高専生だったので授業終わりと同時に捨ててしまいました。。)
何が得られたか
SQLの基本的な操作(SELECT, INSERT, DELETE, UPDATE)
異なるテーブル同士を結合して、欲しいデータを取得する方法
IN、EXSITSを使った少し複雑なクエリ
今の時代、ほとんどのWebアプリ開発ではORマッパーが用意された
フレームワークを利用することがほとんどで、
裏側で動いているSQLクエリを意識せずともDBから欲しいデータが取ってくることができます。
ただ裏側の仕組みを知っておくことは大切だと思います。
裏側を知っていれば全体を意識しながらコーディングできる。
操作に時間がかかっている処理があった際に、トラブルシューティングができる。
アプリに関わるエンジニアは必読ではないでしょうか。
章末に演習があるのもありがたいです。
印象に残った部分の記録
演習に当たって、MysqlのDockerイメージとSequel Pro(DBを操作するGUIツール)を利用しました。
Mysql8系を利用したのですが、途中Sequel Proがクラッシュすることがあったので、
Table Plusとか別のツールの方がいいかもしれません。
version: "3"
services:
db:
image: mysql:latest
ports:
- "3306:3306"
environment:
MYSQL_DATABASE: playground
MYSQL_ROOT_PASSWORD: admin
MYSQL_USER: docker
MYSQL_PASSWORD: docker
TZ: 'Asia/Tokyo'
volumes:
- ./data:/var/lib/mysql
- ./log:/var/log/mysql
- ./conf:/etc/mysql/conf.d
volumes:
data:
driver: local
[client] default-character-set=utf8mb4 [mysqld] character-set-server=utf8mb4 general-log=1 general-log-file=/var/log/mysql/mysqld.log default_authentication_plugin=mysql_native_password
第１章 データベースとSQL
DBの種類
階層型DB
- データを木構造で表現する
- かなり前からあるデータベースで今はほとんど使われていない
リレーショナルDB
- 今回学習した
SQLを利用するDB - 列と行からなる２次元表でデータを管理するDB
- 今回学習した
オブジェクト指向DB
- 保存されるデータ(オブジェクト)同士の関連をそのまま格納する
- オブジェクトが保管されているため、ORマッパーが不要になる
KVS
あとは他にもJSON形式保管するドキュメント指向DBとかがありますよね。
SQLの文とその種類
下の用語を覚えていたからなんだと言う話ですが、
そう言う用語があるんだと思ったのでメモ。
DDL(Data Definition Language)
- データ定義言語
- CREATE
- DROP
- ALTER
DML(Data Manipulation Language)
- データ操作言語
- SELECT
- INSERT
- UPDATE
- DELETE
DCL(Data Control Language)
- データ制御言語
- COMMIT
- ROLLBACK
- GRANT
- REVOKE
テーブルの作成
テーブルの作成にはCREATE TABLEを使う。
CREATE TABLE (テーブル名) ( <列名> <データ型> <制約> ・・
create table (shohin_id char(4) not null, shohi_mei varchar(100) not null, shohin_bunrui varchar(32) not null, hanbai_tanka integer , shiire_tanka integer , torokubi date , primary key (shohin_id) );
基本的なデータ型
| 型名 | 内容 |
|---|---|
| INTEGER | 数値 |
| CHAR | 固定長の文字列型。CHAR(最大長) |
| VARCHAR | 可変長の文字列型。VARCHAR(最大長) |
| DATE | 日付型 |
それぞれの列に対しての制約
列に入る値に対して制約をかけることができる
- NOT NULL
- 必ず値が入らなければならない
- UNIQUE
- 一意性の制約
- DEFAULT
- データ挿入時に値がなかった場合のデフォルト値
- AUTO INCREMENT
- 自動で連番を入力させる
- FOREIGN KEY
- 外部キー制約
- 参照生合成を担保
テーブルの削除
DROP TABLE <テーブル名>
テーブル定義の変更
列を追加するとかテーブルのスキーマを変更する際に利用する
ALETER TABLE <テーブル名> <操作> <定義>;
列を追加する場合は下記。
ALTER TABLE <テーブル名> ADD COLUMN <列の定義> ALTER TABLE Shohin ADD COLUMN shohin_mei_kana VARCHAR(100);
ちなみにテーブル名の変更はRENAMEを使う。
RENAME Table Shohin to Item
データの登録
INSERT INTO <テーブル名> VALUES (<データ1>, <データ2>・・・) INSERT INTO Shohin VALUES ( '0001', '鉛筆' ・・・・)
第２章検索の基本
重複行の削除
DISTINCTを使うと重複行を削除した状態で出力ができる。
SELECT DISTINCT shohin_bunrui FROM Shohin;
比較演算子
| 演算子 | 意味 |
|---|---|
| = | ~と等しい |
| <> | ~と等しくない |
| >= | 以上 |
| <= | 以下 |
| < | より小さい |
| < | より大きい |
NULL判定
NULLを条件にしたい場合にはIS NULLを使い、
NULL以外を条件にした場合にはIS NOT NULLを使う。
SELECT * from Shohin WHERE shiire_tanka IS NOT NULL;
第３章 集約と並べ替え
集約関数
データの合計値とか、平均を求めたい時には
DBが用意している集約関数を利用できる。
- COUNT
- レコード数
- SUM
- 合計
- AVG
- 平均
- MAX
- 最大値
- MIN
- 最小値
テーブルの行数を数えたい場合
SELECT COUNT(*) from Shohin where shiire_tanka > 1000 AND shiire_tanka is not null /* 重複行を除外して行数を数える場合には()内にDISTINCT */ SELECT COUNT(DISTINCT shohin_bunrui) from Shohin;
GROUP BY
~ごとや~単位といった、集約にはGROUP BYを使う。
SELECTに指定できる列名は、GROUP BYで集約した列のみ。
SELECT <列名1> <列名2> FROM <テーブル名> GROUP BY <列名1> <列名2>
GROUP BYとWHEREを併用した場合のクエリの実行順序に注意
FROM -> WHERE -> GROUP BY -> SELECT
HAVING
集約した結果に対して、特定の条件を加えたい場合に使う
例えば、単価の合計値が1000円以上の商品分類など
集約関数を条件にすることが多い
SELECT <列名> FROM <テーブル名> GROUP BY <列名> HAVING <集約したグループに対する条件> /* HAVINGを利用した場合の実行順序 */ SELECT -> FROM -> WHERE -> GROUP BY -> HAVING
HAVING と WHEREどっち使う？
HAVINGで指定した集約関数によっては、
ソートが行われるため事前にWHEREでグループ化対象の行数を減らしておくことが望ましい。
- HAVINGを使った方がいい場合
- 条件に集約関数を使うとき
- WHEREを使った方がいい場合
- 集約した列に対する条件ではないとき
ORDER BY
検索結果を特定の列に対して並び替えを行って表示させたい時に使う
第２ソートキーを追加もできる。
SELECT <列名1> <列名2> ・・・ FROM <テーブル名> ORDER BY <列名1> DESC(or ASC) <列名2> DESC(OR ASC)
４章 データの更新
INSERTで他のテーブルからデータをコピーする
INSERT INTO コピー先のテーブル名 (列名1 ,列名2, 列名3) SELECT 列名1,列名2,列名3 FROM コピー元のテーブル名 WHERE 条件
トランザクション
例えば、銀行の振込みたいに、
処理AAさんの口座から1000円引くと処理BBさんの口座に1000円足す
みたいな複数の操作を連続的に行う一連の処理のことをトランザクションという。
START TRANSACTIONでCOMMIT or ROLLBACKを指定する
START TRANSACTION; INSERT INTO ・・・ UPDATE SET ・・・ UPDATE SET ・・・ COMMIT;
ACID特性
トランザクション処理はACIDを守らなければならない
Atomicity(原子性)
- トランザクションが終わった時、全て実行された状態(
COMMIT)か全て実行されない状態(ROOLBACK)になる - かっこよくいうとAll Or Nothing
- トランザクションが終わった時、全て実行された状態(
Consistency(一貫性)
Isolation(独立性)
Durability(永続性)
第5章 複雑な問い合わせ
ビュー
SELECT文をハードディスクに保存して、仮想的なテーブルを作成する- データを保存しないため、容量を節約することができる
CREATE VIEW <ビュー名> (<ビューの列名・・・>) AS SELECT・・・
作成したビューはテーブルのように呼び出すことができる。
SELECT * FROM <ビュー名>
サブクエリ
FROMの配下にSELECTを繋げて、使い捨てのビュー的なものを作成できる
SELECT shohin_bunrui, cnt_shohin FROM ( SELECT shohin_bunrui, COUNT(*) as shohin_bunrui FROM Shohin GROUP BY shohin_bunrui) as ShohinSum;
スカラサブクエリ
必ず１行１列だけの戻り値を返すサブクエリをスカラサブクエリと呼ぶ
スカラサブクエリを使うことで、where内で集約関数を使うことができる。
SELECT shohin_id, shohin_mei, hanbai_tanka FROM Shohin WHERE hanbai_tanka > ( SELECT AVG(*) FROM Shohin);
相関サブクエリ
外側のクエリの値を使うサブクエリを相関サブクエリという。
上のスカラサブクエリでは下記のSQL文を実行できない。 サブクエリの実行結果が、複数行になるため。
SELECT shohin_id, shohin_mei, hanbai_tanka FROM Shohin WHERE hanbai_tanka > ( SELECT AVG(*) FROM Shohin ORDER BY shohin_bunrui);
下記のように、外側のクエリの値を限定してあげることで実行できる。
shohin_bunruiを限定しているため、１行１列の値を返している。
SELECT shohin_id, shohin_mei, hanbai_tanka FROM Shohin as T1 WHERE hanbai_tanka > ( SELECT AVG(*) FROM Shohin as T2 WHERE T1.shohin_bunrui = T2.shohin_bunrui ORDER BY shohin_bunrui);
第６章 関数、述語、CASE式
算術関数
- MOD
- 剰余を返す
- ROUND(対象の値, 丸める桁数)
- 四捨五入
文字列関数
- CONCAT(文字列1, 文字列2)
- 文字列連結
- LENGTH(文字列)
- 文字列の長さ
- MYSQLはバイト数を数えるため日本語１文字(2byte)は2になる
- LOWER, UPPER
- 小文字、大文字にする
- REPLACE(対象文字列, 置換前, 置換後)
- 文字列置換
- SUBSTRING(対象文字列 FROM 切り出し一のindex FOR 切り出す文字数)
- 文字列の切り出し
日付関数
- CURRENT_DATE
- 現在の日付(Y-M-d)
- CURRENT_TIME
- 現在の時刻(h:m:s)
- CURRENT_TIMESTAMP
- 現在の日時(Y-M-d h:m:s)
- EXTRACT(日付要素 FROM 日付)
- 特定の日付要素(例えば年だけとか)を切り出す
- EXTRACT(YEAR FROM CURRENT_TIME_STAMP)
- 特定の日付要素(例えば年だけとか)を切り出す
変換関数
- CAST(変換前のデータ型 AS 変換後のデータ型)
- 型変換
- COALESCE(データ1, データ2・・・)
- NULLを別な値に変えて扱う
- COALESCE(NULL, 'test', NULL)だとtestが表示される
LIKE述語
部分一致検索ができる
- 前方一致
文字列%
- 後方一致
%文字列
- 中間一致
%文字列%
SELECT * from Shohin WHERE name LIKE "%パンツ%"
IN 述語
IN内に含まれたデータを抽出できるようになる。
(否定の場合はNOT IN)
サブクエリと組み合わせることで柔軟な取り出しができそう
SELECT shohin_id, shohin_mei, hanbai_tanka FROM Shohin WHERE shohin_mei IN ( SELECT shohin_mei FROM Shohin WHERE shohin_id = "000c")
EXSITS述語
ある条件に合致するレコードの存在有無を調べる。
詳しい説明は下記がいいと思います。
[参考記事] (https://qiita.com/darkimpact0626/items/5a5d03c27ae7c849566f)
CASE式
プログラミング言語みたいに、case ~ whenができる
CASE WHEN <条件式> THEN <式> WHEN <条件式> THEN <式> ELSE <式> END /* 例えば価格帯ごとの件数を調べる場合 */ SELECT COUNT(CASE WHEN hanbai_tanka < 500 THEN 1 END) as low, COUNT(CASE WHEN hanbai_tanka BETWEEN 501 AND 1000 THEN 1 END) as mid, COUNT(CASE WHEN hanbai_tanka > 1000 THEN 1 END) as high FROM Shohin;
第７章 集合演算
積集合(INTERSECT)
SELCT ~ FROM テーブル1 INTERSECT SELECT ~ FROM テーブル2
差集合(EXCEPT)
SELECT ~ FROM テーブル1 EXCEPT SELECT ~ FROM テーブル2
INNER JOIN
２つ以上のテーブルを結合することを結合(JOIN)という。
両方のテーブルが指定したカラムの値が一致するものだけを結合するのが内部結合(INNER JOIN)
SELECT T3.tenpo_id, T3.tenpo_mei, T1.shohin_mei, T2.zaiko FROM Shohin as T1 INNER JOIN Zaiko as T2 ON T1.shohin_id = T2.shohin_id INNER JOIN Tenpo as T3 ON T2.tenpo_id = T3.tenpo_id
OUTER JOIN
片方のテーブルの値が全て取得される結合を外部結合(OUTER JOIN)
下記の場合はShohinのテーブルの値が全て出力される
SELECT T2.tenpo_id, T2.tenpo_mei, T1.shohin_mei, T1.hanbai_tanka FROM Zaiko as T1 LEFT OUTER JOIN Tenpo as T2 ON T1.tenpo_id = T2.tenpo_id
