【備忘録】 DB / SQL基礎

うぃろぅです。

今日はDB基礎。やっていきましょう。

今日のテーマ

DB基礎

vviilloovv.hatenablog.com

設計編を先にやってしまっているからいまさら感がすごい。まぁ何か身につくものがあるでしょう。

DBとは

データを一元管理する仕組みのこと。

変遷

台帳(紙) → ファイル(データ) → DB(データ)

フォルダ内にファイルをぶわーって並べて管理するイメージがファイル。経験はない。
年賀状の住所録をフロッピー内に保存、みたいな。被りが生じることが大いにある。

でもhoge_(日付)とかfuga_r1みたいにつけるのは…バージョン管理かそれは。

RDB

RDB(リレーショナル・データベース)はリレーショナル・モデルに基づくデータベースのことである。

トートロジー感ある。要は複数の表を関連付けて管理する、といったところ。

構成要素

表(TABLE)

RDBにおいてデータを管理する基本単位。

関連付けは「主キー」「外部キー」といったキーを参照して行う。

RDBMS

Relational
DataBase
Management
System

RDBを管理するシステムのこと。障害発生時にリカバリしたり、データの不整合を防いだりと重要な役割を担っている。

SQL

Structured
Query
Language

DBを操作するための言語。

• DML
  Data Manipulation Language - データ操作文。selectとかdeleteとかのよく使うものがこれにあたる。
• DDL
  Data Definition Language - データ定義文。createとかdropとかのテーブル自体の操作に使うのがこちら。
• DCL
  Data Control Language - データ制御文。grantとかcommitとかの権限、トランザクションといったデータ外のことを制御するのがこれ。

以上の3種類がある。

基本的なデータ検索

実際に何ができるかを見ていく。

表の照会

表から必要なデータを取り出すことができる。

例として

• 射影
  列を取り出す。
• 選択
  行を取り出す。
• グループ化
  読んで字のごとく。

が挙げられる。

射影

select (列名1, 列名2, ...)  
from (表名)

よく見るやつ。select *で全列取り出し。

選択

select (列名1, 列名2, ...)
from (表名)
where (検索条件式)

条件に合う行を検索して抽出する。

演算子として、

• 比較演算子
  >とか<>とかのExcelでもよく見るアレ。
• BETWEEN演算子
  (列名) between 値 and 値で範囲をとってこれる。
• IN演算子
  (列名) in (値1, 値2, ...)でor検索が簡単にできる。
• LIKE演算子
  (列名) like '文字'でパターンマッチできる。IDが1で始まって9で終わる、とかそういう調べ方。
• NULL演算子
  (列名) is nullで空値を検索できる。

がある。between以降の演算子はnot指定も可能。

• 例

select *
from sample
where id not in (101, 201)

idが101と201以外の行を引っ張ってくる。

select *
from people
where address like '%区%'

addressに区という文字を含んでいる行を抽出する。

論理演算子

• and
  条件を全て満たす
• or
  条件をどれか満たす
• not
  否定を表す

これらを適切に使うことで欲しいデータを的確に引っ張ってこれる。

並べ替え

order by (ソート対象列) asc / descで昇順(asc)か降順(desc)に並べ替え。

select *
from sample
order by price desc

priceで降順に並べ替え。省略した場合はデフォルトでascになる。

グループ化

集合関数

• count (列名 / *)
  行数をカウント。
• sum (列名)
  総和を算出。
• avg (列名)
  平均値を算出。
• max (列名)
  最大値を算出。
• min (列名)
  最低値を算出。

ができる。

select count(*)
from sample

とすると対象テーブルの件数が出力される。件数を算出するだけのため、countしない場合より処理が軽い。

select (列名1, 列名2, ...)
from (表名)
(where 条件)
group by (グループ化する列名)

基本文法。

select id, sum(price), max(income)
from item
group by id
having sum(price) > 10

集合関数を使った条件を記述する場合having句を用いる。

応用

基礎とは

結合

複数の表から同時に情報を抽出し、1つにまとめる。

• 内部結合
• 外部結合
• 自己結合
• クロス結合

の4種類がある。

www.techscore.com

TECHSCORE先生に訊くのが手っ取り早いが、つらつらと書いていく。

内部結合

共通の列の値が結合条件に一致する行の取り出し。

• item

item_id	item_name	shelf_id
001	メモ帳	101
002	付箋	101
003	鉛筆	102
004	ボールペン	102
005	玄翁	105

• shelf

shelf_id	shelf_name	floor
101	紙類	1F
102	筆記類	2F
103	食器類	1F
104	日用品	2F
105	工具	2F

この表に対して

select item_id, item_name, shelf_name, floor
from item inner join shelf
on item.shelf_id = shelf.shelf_id

を実行すると

item_id	item_name	shelf_name	floor
001	メモ帳	紙類	1F
002	付箋	紙類	1F
003	鉛筆	筆記類	2F
004	ボールペン	筆記類	2F
005	玄翁	工具	2F

この表が得られる。同一のカラム名が存在する場合(表名).(列名)と書いてどの表のカラムなのかをはっきりさせる必要がある。

例を書くのか一番時間かかる…。

外部結合

主キーの値が一致しない値も抽出してくる。

• item

item_id	item_name	shelf_id
001	メモ帳	101
002	付箋	101
003	鉛筆	102
004	ボールペン	102
005	玄翁	105

• shelf

shelf_id	shelf_name	floor
101	紙類	1F
102	筆記類	2F
103	食器類	1F
104	日用品	2F
105	工具	2F

さっきの表を再掲。この表に対して

select item_id, item_name, shelf_name, floor
from item right outer join shelf
on item.shelf_id = shelf.shelf_id

item_id	item_name	shelf_name	floor
001	メモ帳	紙類	1F
002	付箋	紙類	1F
003	鉛筆	筆記類	2F
004	ボールペン	筆記類	2F
005	玄翁	工具	2F
		食器類	1F
		日用品	2F

この表が得られる。

left outer joinとすることで左側の表を全て出力、rightでその逆。今回はshelfの一致したいものも出力するのでright outer joinとしている。full outer joinで両方の一致していない行も含めて出力される。書き方が難しい…。

ちなみにleft right full が書いてある場合outerが省略可能で、innerは左右の別がそもそもないため省略可能となっている。

select a, b from hoge join fuga on b = a; -- 内部結合
select a, b from hoge right join fuga on b = a; -- 右外部結合
select a, b from hoge left join fuga on b = a; -- 左外部結合
select a, b from hoge full join fuga on b = a; -- 完全外部結合

つまりこう書けるということ。

自己結合

同一表を結合する。

codezine.jp

例書くの疲れたわかりやすい例が載っていたため詳細はこちら参照。

重複行削除に力を発揮するみたい。
他には例えば社員IDと上司IDがあり、上司IDは社員IDに含まれている場合に使える。
社員表から上司表を作成すると。なるほど。

select (別名).(列名)
from (表名) as (別名1) inner join (表名) as (別名2)
on (別名1).(外部キー列) = (別名2).(外部キー列)

同一表を参照するため、asで別名を宣言することが多い。asは省略可能。

クロス結合

ある表とある表の組み合わせを求める。

select (列名1, 列名2, ...)
from (表1) cross join (表2)

特に難しいところもなく。
これを何に使うかといえば、各データの組み合わせに対してテストを実行する、ということでテストデータ作成に有用。

• item

item_id	item_name	shelf_id
001	メモ帳	101
002	付箋	101
003	鉛筆	102
004	ボールペン	102
005	玄翁	105

• shelf

shelf_id	shelf_name	floor
101	紙類	1F
102	筆記類	2F
103	食器類	1F
104	日用品	2F
105	工具	2F

select item_id, item_name, shelf_name, floor
from item cross join shelf

これをうっかり実行すると

item_id	item_name	shelf_name	floor
001	メモ帳	紙類	1F
001	メモ帳	筆記類	2F
001	メモ帳	食器類	1F
001	メモ帳	日用品	2F
001	メモ帳	101
002	付箋	紙類	1F
002	付箋	筆記類	2F
002	付箋	食器類	1F
002	付箋	日用品	2F
002	付箋	工具	2F
003	鉛筆	紙類	1F
003	鉛筆	筆記類	2F
003	鉛筆	食器類	1F
003	鉛筆	日用品	2F
003	鉛筆	工具	2F
004	ボールペン	紙類	1F
004	ボールペン	筆記類	2F
004	ボールペン	食器類	1F
004	ボールペン	日用品	2F
004	ボールペン	工具	2F
005	玄翁	紙類	1F
005	玄翁	筆記類	2F
005	玄翁	食器類	1F
005	玄翁	日用品	2F
005	玄翁	工具	2F

こうなる。この例は実用性はないがあくまで例ということで勘弁して欲しい。

複問い合わせ

ある表を照会した結果を用いて別の表を紹介する。

where (検索条件) = (select文)と続けていく。これが何階層にもなってしまうと処理時間が大変なことになりがち。

www.atmarkit.co.jp

よくわかる解説がこちら。

データ検索以外の操作

データ追加

insertを用いる。

insert into (表名)(列名1, 列名2, ...)
values (値1, 値2, ...)

全ての列に値を設定する場合列名は省略可能。

• item

item_id	item_name	shelf_id
001	メモ帳	101
002	付箋	101
003	鉛筆	102
004	ボールペン	102
005	玄翁	105

おなじみのこれに対して

insert into item
values ('006', '鉋', '105')

これを実行すると

item_id	item_name	shelf_id
001	メモ帳	101
002	付箋	101
003	鉛筆	102
004	ボールペン	102
005	玄翁	105
006	鉋	105

こうなる。かんたーん。

こちらも複問い合わせを利用可能で

insert into (追加する表名)
select (列名1, 列名2, ...)
from (検索する表名)
(where 条件)

とすれば条件に合うデータをまとめて追加できる。

データ更新

updateを用いる。

• item

item_id	item_name	shelf_id
001	メモ帳	101
002	付箋	101
003	鉛筆	102
004	ボールペン	102
005	玄翁	105
006	鉋	105

先ほど変更したこの表に対して

update item
set item_name = ノミ
where item_id = '006'

これを実行すると

item_id	item_name	shelf_id
001	メモ帳	101
002	付箋	101
003	鉛筆	102
004	ボールペン	102
005	玄翁	105
006	ノミ	105

こうなる。where句の条件が複数行に当てはまる場合全てに対して更新が走る。

データ削除

deleteを用いる。

• item

item_id	item_name	shelf_id
001	メモ帳	101
002	付箋	101
003	鉛筆	102
004	ボールペン	102
005	玄翁	105
006	ノミ	105

この表に対して

delete from item
where item_id = '006'

これを実行すると

item_id	item_name	shelf_id
001	メモ帳	101
002	付箋	101
003	鉛筆	102
004	ボールペン	102
005	玄翁	105

こうなる。こちらも複数同時に消すことが可能なため

delete from item

うっかりこれを実行すると全部消える。落ち着いてrollbackだ。

トランザクション

データベースに対する処理の基本単位。

リモートリポジトリとローカルリポジトリの関係に近い。手元の修正はリモートには反映されていないため、変更を確定するためにはcommit、変更を破棄するためにはrollbackを実行する。

表の定義

ここまでが表に対する操作、DML。ここからは表自体の作成や削除を行うDDLについて。

表作成

create tableを用いる。

先ほどのitem表を作成するのであれば

crate table item
(item_id CHAR(3) NOT NULL,  -- 固定長文字列、必須項目
item_name VARCHAR(20),  -- 可変長文字列
shelf_id INTEGER(3) NOT NULL)  -- 数値、必須項目

こんな感じ。型が適当なのは例なので許して。

大文字小文字の区別はないがなんとなく型名は大文字のイメージ。SELECTとかも大文字で書いてある場合が多いけれども。

表削除

drop table item

消すのはとても簡単。そしてテーブルの変更はrollbackできない。要注意。

RDBMSの機能

• データの物理構造管理
• データ定義情報管理
• データ操作機能提供
• 同時実行制御
• 機密保護
• 障害回復

概ねこんなところ。データ管理はお任せ!みたいな感じ。

データの物理構造管理

db-study.com

データの格納位置を一切気にすることなくSQLを扱える。

データ定義情報管理

• データファイル
  ユーザーが定義した表のデータ
• データ辞書
  データ構造、関連付け、容量等のDB自体の情報
• ログファイル
  変更履歴。

を管理してくれる。

データ操作機能提供

SQLで操作できるよ、ということ。微妙に方言はあるが概ね似たり寄ったり。

同時実行制御

多重更新を防止するための排他制御。ロックをうまいこと掛けてくれる。

デッドロック

お互いに更新したい箇所をロックしあってしまい、ロックがとけなくなる。
片方のSQL文をエラーにすることでRDBMSはデッドロックを回避させることが多い。

そもそもの更新順を設定する、1更新につき1行に限定する、といったことでデッドロックが起きないようにすることが大事。

トランザクション同時実行時の現象

• ダーティーリード
  commitしていないデータが検索できる現象。
• ノンリピータブルリード
  以前の問い合わせと同じデータが検索されない現象。
• ファントムリード
  以前の問い合わせ結果に含まれないデータが検索される現象。

qiita.com

わかりやすい。Qiitaは頼りになる。

トランザクション分離レベル

(分離レベル)	ダーティリード	ノンリピータブルリード	ファントムリード
READ UNCOMMITED	○	○	○
READ COMMITED	×	○	○
REPEATABLE READ	×	×	○
SERIALIZABLE	×	×	×

下にいくほど同時実行性が低くなる。

機密保護

DB内へのデータアクセスを制御する機能。権限を操作して制限をかける。

障害回復

データが破損したときに元に戻す機能。

• システム障害
• メディア障害
• アプリケーション障害

等様々な事情でデータは破損する。

バックアップとログファイルを組み合わせてデータを復元していくことになる。

ログファイル

• 更新前ログ
• 更新後ログ

2種類のログを比較して復元する。

ロールバック処理

更新前ログを使用して更新された部分を元に戻す処理。

ロールフォワード処理

更新後ログを使用し、障害発生直前の状態に回復する処理。

応用情報技術者試験を受けたときに勉強したなぁって。

まとめ

以上の知識を基に、各種ベンダーのRDBMSに関する知識を拡充していくべし。

めっちゃ書いた。先週より時間短かったのに表を作ったせいでよっぽど面倒なことに。

ではまた。