🔔 あなたへのおすすめ記事「For you（β）」をリリースしました
Zenn
Open24

データベースの基本

SQL
Database
ShionShion

データベースの基本知識

  • データベース（DB）の実体はただのファイル
  • DB の中でも、複数表の形式でデータ管理する DB をリレーショナルデータベース（RDB）という
  • DB の操作をするにはデータベース管理システム（DBMS）と呼ばれるプログラムが必要
  • DB に直接 SQL を送信するのではなく DBMS に送信する
  • 通常、DBMS には、各 DB が定める特有の手順や形式に従ってネットワーク経由で SQL を送信する
    • 各製品（Oracle, MySQL など）は、DBMS と通信を行うための専用ソフトウェア（ドライバという）を提供している
    • Java などでプログラムを作り、その中からドライバの命令を呼び出せば DBMS に SQL を送信できる
    • ただほとんどの製品で SQL クライアントが用意されているため、それを使用すればプログラムを作成することなく手軽に SQL を DBMS に送信できる
  • 「この値を指定すれば、ある1行を完全に特定できる」役割を担う列を、主キーという
  • DBMS は、テーブルの各行を１つずつ順番に処理していく
    • 式の評価（足し算など）も、各行で行われる
  • ある２つのテーブルの間に情報としての関連がある場合、その関連をリレーション（シップ）という

SQL の全体像

DML (Data Manipulation Language)

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • EXPLAIN
  • LOCK TABLE

TCL (Transaction Control Language)

  • COMMIT
  • ROLL BACK
  • SET TRANSACTION
  • SAVEPOINT

DDL (Data Definition Language)

  • CREATE
  • ALTER
  • DROP
  • TRUNCATE

DCL (Data Control Language)

  • GRANT
  • REVOKE
ShionShion

SELECT

基本の形

SELECT 列名, 列名, .... FROM テーブル名 WHERE 条件

その他

  • 列名に AS をつけると別名を定義できる
ShionShion

INSERT

基本の形

INSERT INTO テーブル名 ('列名', '列名', ...) VALUES ('追加値', '追加値', ....)

その他

  • 全ての列に値を指定する場合には、テーブル名直後の括弧は不要
ShionShion

UPDATE

基本の形

UPDATE テーブル名 SET 列名 = 更新値, 列名 = 更新値, ... WHERE 条件
ShionShion

DELETE

基本の形

DELETE FROM テーブル名 WHERE 条件
ShionShion

SQL 基本ルール

  • 文の途中に改行を入れることができる（→誰がみてもみやすい SQL を！）
  • 行の先頭や行の途中に半角の空白を入れることができる（→誰がみてもみやすい SQL を！）
  • コメントは -- または /* */ で記載する
  • 予約語（SELECT など）は大文字と小文字のどちらで記述しても良い（→だが PJ の規約にしたがう！）
ShionShion

比較演算子

  • NULL であることを判定するためには IS NULL を、NULL 出ないことを判定するには、IS NOT NULL 演算子を使う
  • パターンマッチング（あるパターンに合致しているかのチェック）には LIKE 演算子を使う
    • %: 任意の 0 文字以上の文字列
    • _: 任意の 1 文字
  • BETWEEN 演算子は、ある範囲内に値が収まっているかを判定する
    • BETWEEN 値 AND 値
  • IN 演算子は、括弧内に列挙した複数の値のいずれかにデータが合致するかを判定する
    • IN (値, 値, ...)
  • ANY 演算子は、括弧内に列挙した複数の値を比較していずれかが真なら真と判定する
    • ANY (値, 値, ...)
  • ALL 演算子は、括弧内に列挙した複数の値を比較してすべて真なら真と判定する
    • ALL (値, 値, ...)
ShionShion

検索結果の加工

重複行を除外する

DISTINCT を使用する。
データの種類を取得したい場面で役立つ。

SELECT DISTINCT 列名, ... FROM テーブル名

並び替える

ORDER BY を使用する。

SELECT 列名, ... FROM テーブル名 ORDER BY 列名 ASC[DESC]

並び替える（複数行）

複数指定すると、最初に指定された列で並び替え、次に指定された列で並び替え・・・を繰り返す。

SELECT 列名, ... FROM テーブル名 ORDER BY 列名 ASC[DESC], 列名 ASC[DESC]

行数を限定して取得する

OFFSET - FETCH を使用する。
トップ5とかトップ3みたく、みたいところだけ取得できる。

SELECT 列名 FROM テーブル名
    ORDER BY 列名 ASC[DESC]
OFFSET 先頭から除外する行数 ROWS
    FETCH NEXT 取得行数 ROWS ONLY
ShionShion

集合演算子

SQL では３つの集合演算を使用できる。

  • UNION：和集合
    • ２つの検索結果を足し合わせたもの
  • EXCEPT：差集合
    • 最初の検索結果から次の検索結果と重複する部分を取り除いたもの
  • INTERSECT：積集合
    • ２つの検索結果で重複するもの

２つの SELECT 文の結果を足し合わせる

UNION を使用する。
和集合の結果に重複行があれば１行にまとめられる。
UNION ALL とすると重複行をまとめずにすべてそのまま返す。

SELECT 文1
UNION (ALL)
SELECT 文2

２つの SELECT 文の結果の差を得る

EXCEPT を使用する。

SELECT 文1
EXCEPT (ALL)
SELECT 文2

２つの SELECT 文に共通する行を得る

INTERSECT を使用する。

SELECT 文1
INTERSECT (ALL)
SELECT 文2
ShionShion

様々な演算子

基本の演算子

  • +
  • -
  • *
  • /
  • ||：文字連結

分岐する

CASE を使用する。
評価し、その結果に応じて値を自由に変換できる。

CASE 評価する列や式 WHEN 値1 THEN 値1の時に返す値
                  WHEN 値2 THEN 値2の時に返す値
                  ...
                  ELSE デフォルト値
END
ShionShion

さまざまな関数

文字の長さを取得する

LENGTH を使用する。

SELECT LENGTH(列名) FROM テーブル名

空白を除去する

TRIM を使用する。
文字の前後についた空白を除去できる。
左右から空白を除去する場合は TRIM だが、右だけなら RTRIM 、左だけなら LTRIM を使用する。

SELECT TRIM(列名) FROM テーブル名

指定文字を置換する

REPLACE を使用する。

UPDATE テーブル名 SET 列名 = REPLACE(列名, 置換前文字列, 置換後文字列)

文字列の一部を抽出する

SUBSTRING を使用する。

SELECT SUBSTRING(列名, 抽出を開始する位置, 抽出する文字数) FROM テーブル名

指定桁で四捨五入

ROUND を使用する。

SELECT ROUND(列名, 有効とする桁数) FROM テーブル名

指定桁で切り捨て

TRUNC を使用する。

SELECT TRUNC(列名, 有効とする桁数) FROM テーブル名

冪乗（べきじょう）

POWER を使用する。

SELECT POWER(列名, 何乗するかの数値) FROM テーブル名

現在の日時を得る

  • CURRENT_TIMESTAMP：現在の日時を取得
  • CURRENT_DATE：現在の日付
  • CURRENT_TIME：現在の時刻
INSERT INTO テーブル名 VALUES (CURRENT_DATE, 列名, 列名...)

データ型を変換する

CAST を使用する。

SELECT CAST(変換対象の列 AS 変換する型) FROM テーブル名

最初に登場する NULL でない値を返す

COALESCE を使用する。

SELECT COALESCE(列や式, 列や式...) FROM テーブル名

使い所は、「もし NULL の場合はこの値で代用する」用途で便利。
以下はメモが NULL の場合、NULLです と表示する SQL 文。

SELECT COALESCE(メモ, 'NULLです') FROM テーブル名
ShionShion

集計関数

集計関数は、集計の対象となった全ての行に対して１回だけ計算を行い、１つの答えを出す。
つまり、結果は必ず１行になる。

代表的な集計関数

  • SUM：各行の値の合計を求める
  • MAX：各行の値の最大値を求める
  • MIN：各行の値の最小値を求める
  • AVG：各行の値の平均値を求める
  • COUNT：行数を求める

AVG, SUM, COUNT は、DISTINCT を指定すると、その列で重複している値を除いた状態で集計が行われる。

SELECT COUNT(DISTINCT 列名) FROM テーブル名

また、基本的に NULL は無視されるため、NULL を 0 に読み替えて集計したい際は COALESCE を使用すると良い。

平均を求めるSQL
SELECT AVG(COALESCE(列名, 0)) AS 平均 FROM テーブル名
ShionShion

グループ化

GROUP BY を使用する。
指定した基準で検索結果をいくつかのまとまりに分けることができる。

SELECT グループ化の基準列名, 集計関数
FROM テーブル名
WHERE 元の表に対する絞り込み条件
GROUP BY グループ化の基準列名

グループ集計が行われる流れは以下のとおり。

  1. WHERE 句によって行を絞り込む
  2. グループごとに検索結果を分割
  3. 各グループを集計する

WHERE 句は集計よりも前に行われるため、例えば以下のような WHERE 句を書くとエラーになる。

WHERE SUM(出金額) > 0

集計結果を行った後の結果に対して絞り込みを行いたい場合は、HAVING 句を使用する。

SELECT グループ化の基準列名, 集計関数
FROM テーブル名
WHERE 元の表に対する絞り込み条件
GROUP BY グループ化の基準列名
HAVING 集計結果に対する絞り込み条件
ShionShion

副問い合わせ(サブクエリ)

副問い合わせの３つのパターン

  1. 単一の値の代わりとして、サブクエリの結果を用いる
    SET 句や SELECT の列名で利用する
  2. 複数の値の代わりとして、サブクエリの結果を用いる
    IN, ANY, ALL 演算子で利用する
  3. 表の値の代わりとして、サブクエリの結果を用いる
    FROM句やINSERT文で利用する

相関副問い合わせ

副問い合わせの内部から主問い合わせの表や列を利用する副問い合わせを、相関副問い合わせという。
EXISTS 演算子と共に使われる典型的なパターンのため押さえておくと良い。

SELECT 列名 FROM テーブルA
WHERE EXISTS
(SELECT * FROM テーブルB WHERE テーブルB.列名 = テーブルA.列名)
ShionShion

テーブルの結合

前提

そもそも結合は・・・

  • 左右に並んだ２つのテーブルを単純にくっつけるような処理ではない
  • 結合する２つのテーブルは対等な関係ではない
  • あくまで主役は FROM 句で指定したテーブルで、JOIN 句で指定したテーブルの内容を必要に応じて繋いでいる
  • DBMS は１行ずつ処理していく際、「テーブルAのこの行に繋ぐべきテーブルBの行はどれか」と探しながら繋いでいるイメージ

基本の使い方

JOIN を使用する。
テーブル間でどの行を繋ぐかは ON で指定する。

SELECT 列名
    FROM テーブルA
    JOIN テーブルB
        ON 両テーブルの結合条件

その他押さえておくべきことは、「右表に結合相手の行がない場合や、左表の結合条件の列が NULL の場合、結合結果から消滅する」という点。

ただ、左表の結合条件の列が何らかの理由で NULL が入ることがあるかもしれない。
その際は、左外部結合を使用する。

左外部結合

LEFT JOIN を使用する。
左表については、結合相手が見つからなくても、NULL であっても必ず出力するようにできる。

SELECT ~ FROM テーブルA
    LEFT JOIN テーブルB
        ON 結合条件

簡単にいうと、LEFT JOIN は左表の全ての行を必ず出力するというもの。

左があるということは・・・右もある！

右外部結合

RIGHT JOIN を使用する。
テーブルAで使われていない項目がテーブルBにあった場合も、失われることはなくなる。

SELECT ~ FROM テーブルA
    RIGHT JOIN テーブルB
        ON 結合条件

RIGHT JOIN で、右表の全ての行を必ず出力することができる。

完全外部結合

FULL JOIN を使用する。
左右全ての表の全ての行を必ず出力できる。

SELECT ~ FROM テーブルA
    FULL JOIN テーブルB
        ON 結合条件
ShionShion

トランザクション

DBMS がひとかたまりの SQL を扱うことをトランザクションという。

トランザクション指示は以下のようにする。

  1. BEGIN：開始の指示
  2. COMMIT：終了の指示
  3. ROLLBACK：取消して終了の指示

原子性（Atomicity）

DBMS は、トランザクションに含まれる全ての SQL について、必ず「全ての実行が完了している」か「１つも実行されていない」かのどちらかの状態になるように制御する。
この性質を原子性という。

分離性（isolation）

DBMS は、あるトランザクションを実行する際、他のトランザクションから影響を受けないように、それぞれを分離して実行する。
この性質を分離性という。

DBMS はこの制御を行うために、内部でロックと呼ばれる仕組みを使用する。あるトランザクションが現在読み書きしている行に鍵をかけ、他の人のトランザクションからは読み書きできないようにしてしまう。
コミットまたはロールバックで終了すると、鍵は解除され他の人のトランザクションがその行を読み書きできるようになる。

分離レベル

どの程度厳密にトランザクションを分離するかは、トランザクション分離レベルで指定できる。

分離レベル ダーティーリード 反復不能読み取り ファントムリード
READ UNCOMMITTED 🙅‍♀️恐れあり 🙅‍♀️恐れあり 🙅‍♀️恐れあり
READ COMMITTED 🙆発生しない 🙅‍♀️恐れあり 🙅‍♀️恐れあり
REPEATABLE READ 🙆発生しない 🙆発生しない 🙅‍♀️恐れあり
SERIALIZABLE 🙆発生しない 🙆発生しない 🙆発生しない

分離レベルを設定する際は以下の SQL で可能。

SET TRANSACTION ISOLATION LEVEL 分離レベル名
ShionShion

ロックの活用

DBMS はトランザクションの分離性を確保するために自動的に行にロックをかける。
一方、SQL 文を使って指定した対象を明示的にロックすることもできる。

明示的なロックの種類

  1. 行ロック
  2. 表ロック
  3. データベースロック

ロックをかける際の制限の強さ

  1. 排他ロック
  2. 共有ロック

排他ロックと共有ロックの違いは下記の記事がわかりやすい。
https://wa3.i-3-i.info/diff717lock.html

行ロック

通常、SELECT で選択した行には自動的に共有ロックがかかる。
しかし FOR UPDATE を使用すると排他ロックをかけることができる。

SELECT ~ FOR UPDATE (NOWAIT)

※ NOWAIT をつけると DBMS はロックの解除を待機せずすぐさまロック失敗のエラーを返す

表ロック

表全体をロックするには、LOCK TABLE を使用する。
モード名は EXCLUSIVE（排他）、SHARE（共有）がある。

LOCK TABLE テーブル名 IN モード名 MODE (NOWAIT)
ShionShion

権限の付与と剥奪

DCL (Data Control Language) は、誰に、どのようなデータ操作やテーブル操作を許可するかといった権限を設定するための SQL 命令の総称。
権限を付与するには GRANT を使用する。

GRANT 権限名 TO ユーザー名

権限を剥奪するには REVOKE を使用する。

REVOKE 権限名 FROM ユーザー名

※ 権限名やユーザー名は DBMS による

これらは、データベース全権を管理する データベース管理者（DBA: Databaase Administrator）だけが使用する命令。

ShionShion

テーブル操作

テーブルの作成

CREATE TABLE を使用する。
基本の形は以下。

CREATE TABLE テーブル名 (
    列名1 型名 制約の指定,
    列名2 型名 制約の指定,
    :
    列名n 型名 制約の指定
)

制約の指定

代表的な制約の指定は以下の３つ。

  1. PRIMARY KEY
  2. FOREIGN KEY
  3. NOT NULL
  4. UNIQUE
  5. CHECK

PRIMARY KEY

主キーの役割を担う列には、主キー制約（非NULL、重複なし）をつける。
PRIMARY KEY を使用する。

FOREIGN KEY

参照整合性（外部キーが指し示す先にあるべき行が存在してリレーションシップが成立している状態）が崩れるようなデータ操作をしようとした場合にエラーを発生させ、強制的に処理を中断させる制約が、外部キー制約。
REFERENCES を使用する。

CREATE TABLE テーブル名 (
    列名1 型名 REFERENCES 参照先テーブル名(参照先列名),
    :
)

NOT NULL

INSERT 文でデータを追加する際、一部の列の値が省略される場合がある。その際は NULL が入るが、NOT NULL を使用することで NULL を禁止できる。
NOT NULL 制約は、DEFAULT 指定と組み合わせて利用されるのが一般的。

デフォルト値を設定しておけば INSERT でデータを追加する際、値を指定していなくても NULL の代わりにデフォルト値が入るようになる。

CREATE TABLE テーブル名 (
    列名1 型名 DEFAULT デフォルト値 NOT NULL,
    :
)

UNIQUE

ある列の内容が決して重複してはならない場合、UNIQUE をつける。

CHECK

ある列に格納される値が妥当かどうかを細かく判定したい場合、CHECK を使用する。
CHECK の後ろの括弧内に記述した条件式が真となる値だけが格納を許される。

CREATE TABLE テーブル名 (
    列名1 INTERGER DEFAULT 0 CHECK(列名1 >= 0),
    :
)

テーブルの削除

テーブルそのものを削除する場合は、DROP TABLE を使用する。

DROP TABLE テーブル名

テーブル定義の変更

ALTER TABLE を使用する。

列の追加は下記のようにする。

ALTER TABLE テーブル名 ADD 列名 型

列の削除は下記のようにする。

ALTER TABLE テーブル名 DROP 列名

テーブルの存在を確認してから 作成/削除 する

テーブルの作成や削除は、同名テーブルが存在しているかどうかで成功の可否が左右される。
そのため、テーブルの存在を確認し、実行を制御できるオプションがある。

存在しない時のみテーブルを作成するには下記のようにする。

CREATE TABLE IF NOT EXSITS テーブル名 (
    :
)

存在する時のみテーブルを削除するには下記のようにする。

DROP TABLE IF EXISTIS テーブル名
ShionShion

全てのデータを高速に削除する

TRUNCATE TABLE を使用する。

TRUNCATE TABLE テーブル名

実行結果は、DELETE FROM と同じだが、TRUNCATE TABLE を使用するとロールバックが不可能になるが高速である。

ShionShion

インデックスの作成

インデックスを作成すると、検索が早くなる。
インデックスの特徴は以下。

  • 指定した列に対して作られる
  • インデックスが存在する列に対して検索が行われると、DBMS は自動的にインデックスの使用を試みる
  • インデックスには名前をつける

インデックスを作成する SQL は以下。

CREATE INDEX インデックス名 ON テーブル名(列名)

インデックスを削除する SQL は以下。

DROP INDEX インデックス名

インデックスの作成によるデメリットも存在する。

  • インデックス情報を保存するために、ディスク容量を消費する
  • テーブルのデータが変更されるとインデックスも書き換える必要がある

特に２つ目のデメリットが重要で、INSERT や UPDATE 、DELETE のオーバーヘッドが増える点に注意する。

ShionShion

ビューの作成

結果表をテーブルのように扱えるビューという機能が存在する。

例えば「4月の全ての入出金を表示」し、「4月に使った費目を一覧表示」するには、両者とも WHERE 句で「4月中の」という条件を書くことになり面倒。

そんな時に、「4月の分だけを抽出したもの」をビューとして作成しておいたらそれを使い回すことができて便利になる。

ビューを作成するには、CREATE VIEW を作成する。

CREATE VIEW ビュー名 AS SELECT 文

ビューを削除するには、DROP VIEW を作成する。

DROP VIEW ビュー名

ビューは、FROM 句の後ろに指定して使用する。

SELECT * FROM ビュー名

ただ、ビューの実体は単なる「名前をつけた SELECT 文」にすぎないため、基本的にビューに対しては SELECT しか行えない点に注意する。

ShionShion

採番を楽にする

CREATE TABLE で列を定義する際に、「連番を振る列である」と宣言すると、データが追加されるタイミングで自動的に連番が振られる列を定義できる。

CREATE TABLE テーブル名(
    ID INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    :
)
ShionShion

データベース設計の流れ

  1. お客様の要件ヒアリング
  2. 概念設計
    管理すべき情報はどのようなものなのかを整理
  3. 論理設計
    どのようなテーブルを作り、それぞれのテーブルにどのような列を作るかを整理
  4. 物理設計
    特定の DBMS を使う前提にたち、テーブルの全ての列について型、制約、インデックスなどテーブル作成に必要な全ての要素を確定させる

概念設計

概念設計では、要件を実現するために、抽象的な概念として管理すべきエンティティを明らかにする。
エンティティには複数の属性を持ち、エンティティ同士にどのような関連があるかもここで明らかにする。

概念設計の成果は、ER図にまとめるのが一般的。

https://wa3.i-3-i.info/word11593.html

論理設計

論理設計では、概念上のエンティティを RDB で取り扱いやすい形のテーブルに変形する。
まずは、「多対多」の分解やキーの整理などから始める。

次に、正規化を行う。

  1. 第一正規化
  2. 第二正規化
  3. 第三正規化

それぞれの正規化の詳細は別の記事に任せるとして、ここではいくつかのキーワードを整理する。

関数従属性

「ある列Aの値が決まれば、自ずと列Bの値も決まる」関係。このとき、「列Bは列Aに関数従属している」という。
主キーとは、「その値を決めればどの行なのかを完全に特定できる」列のこと。つまり、そもそもテーブルに含まれる主キー以外の列は、主キー列に対して関数従属しているべきである。

ログインするとコメントできます