概要
情報技術の基礎として理解しておきたい データベース操作 について、集合演算、関係演算、SQL-DDL、SQL-DML、SQL-DCL、その他のSQL構文を整理する。
6行の売上表を作り、抽出・集計・取消しを同じデータで試す。SQLの分類を覚えるだけでなく、各句の役割によって結果の行数や値がどう変わるかを確かめる。
この記事の構成
- 集合／関係演算
集合／関係演算を整理し、要素同士がどう結び付くかを確認。 - SQL
言語の役割を整理し、共通の売上表で定義・登録・集計・取消しを確認。 - データ定義言語：SQL-DDL（Data Definition Language）
データ定義言語：SQL-DDL（Data Definition Language）と式や用語が表す意味を整理。 - データ操作言語：SQL-DML（Data Manipulation Language）
データ操作言語：SQL-DML（Data Manipulation Language）の意味と要点を具体例から整理。 - データ制御言語：SQL-DCL（Data Control Language）
アクセス権を制御する命令言語で、代表的な命令文としてGRANT文とREVOKE文がある。 - トランザクション制御言語：SQL-TCL（Transaction Control Language）
COMMITとROLLBACKによってトランザクションの確定と取消しを制御。 - 関連するSQL構文
同じ6行を使い、COUNT(*)・COUNT(列)・NULLの比較方法の違いを確認。
集合／関係演算
データベースは次の集合演算と関係演算でデータ集合を表現する。
-
集合演算（和、差、積、直積）
※ 和 、 差 、 積 については 応用情報技術 - 離散数学 > 集合 の集合演算と同じ。
直積 は\(2\) つのテーブル\(A\)、\(B\)の 全組合わせ を表す。-
例：テーブル\(A\)とテーブル\(B\)の直積\(A × B\)
-
テーブル\(A\)
\(X\) \(Y\) \(1\) a \(2\) b \(3\) c -
テーブル\(B\)
\(X\) \(Z\) \(1\) \(d\) \(2\) \(e\) \(3\) \(f\) -
直積\(A × B\)
\(AX\).\(X\) \(AX\).\(Y\) \(BX\).\(X\) \(BX\).\(Z\) \(1\) \(a\) \(1\) \(d\) \(1\) \(a\) \(2\) \(e\) \(1\) \(a\) \(3\) \(f\) \(2\) \(b\) \(1\) \(d\) \(2\) \(b\) \(2\) \(e\) \(2\) \(b\) \(3\) \(f\) \(3\) \(c\) \(1\) \(d\) \(3\) \(c\) \(2\) \(e\) \(3\) \(c\) \(3\) \(f\)
-
-
関係演算（選択、射影、結合、商）
選択 は レコードを抽出する 操作（演算）。
射影 は フィールドを抽出する 操作（演算）。
結合 は複数の関係を条件に基づいて組み合わせる操作（演算）。条件に一致する組だけを返す 内部結合 、指定した側の一致しない組も残す 外部結合 などがある。 自然結合 は同じ名前の属性を等しい条件で結び、重複する属性を一つにする内部結合の一種となる。
商 は\(B\)の属性が\(A\)に含まれているとき、\(A\)の残りの属性について\(B\)のすべての組と組み合わせた組が\(A\)に存在する値を求める操作（演算）。例えば「すべての必修科目を履修した学生」を求める場合に対応する。
SQL
SQL（Structured Query Language）はリレーショナルデータベース管理システム（RDBMS）においてデータの操作や定義を行うためのデータベース言語となる。DDL、DML、DCLに加えてトランザクション制御をTCLとして分けるなど、分類方法は教材やDBMSによって異なる。本記事では次の分類で整理する。
また、SQLは国際標準化され、多様なリレーショナルデータベースで利用できる。ただし、利用できる構文や機能にはDBMSごとの差がある。代表例として下記のデータベースがある。
- Oracle Database：Oracle社が提供するRDBMS。
- PostgreSQL：オープンソースのRDBMS。
- MySQL：GPLのCommunity Editionと商用版が提供されるRDBMS。
- SQLite：アプリケーションへ組み込んで利用できるRDBMS。
- Microsoft SQL Server：Microsoft社が複数のエディションで提供するRDBMS。
- Microsoft Access：Microsoft 365などで提供されるデスクトップ向けデータベース管理システム。
データ定義言語：SQL-DDL（Data Definition Language）
テーブル、ビュー、インデックスなどを定義する命令言語で、以下の命令文を用いたSQLを指す。
-
CREATE文
データ定義の作成を実行する命令文。
テーブル定義を新規作成するCREATE TABLEなど。
以下、CREATE TABLEに指定できる主な制約。
構文 意味 PRIMARY KEY 主キー制約 NOT NULL 非ナル制約（NOT NULL制約） REFERENCES 参照制約（外部キー制約） UNIQUE 一意性制約 -
ALTER文
データ定義の変更を実行する命令文。
テーブル定義を変更するALTER TABLEなど。 -
DROP文
データ定義の削除を実行する命令文。
テーブル定義を削除するDROP TABLEなど。 -
TRUNCATE文
データの全削除を実行する命令文。
テーブルデータを全削除するTRUNCATE TABLEなど。
-
小さな売上表を作り、後の例を同じデータで試す
以下はPostgreSQLで実行できる学習用の例で、SQLの基本的な構文だけを使っている。空の練習用データベースで一度実行し、後の集計・トランザクションの例にも使う。金額が未入力の行を比較するため、amount列はNULLを許している。CREATE TABLE example_sales ( id INTEGER PRIMARY KEY, category VARCHAR(10) NOT NULL, amount INTEGER CHECK (amount >= 0) ); INSERT INTO example_sales (id, category, amount) VALUES (1, 'A', 100), (2, 'A', 200), (3, 'A', NULL), (4, 'B', 50), (5, 'B', 50), (6, 'C', 300);CREATE TABLEは表の構造を定義するDDL、INSERTは行を入れるDMLである。構造を作ることと値を登録することを別の操作として確認できる。
データ操作言語：SQL-DML（Data Manipulation Language）
既存のテーブルやビューに対してデータを操作する命令言語で、以下の命令文を用いたSQLを指す。
-
SELECT文（検索）
レコードの検索（抽出）を実行する命令文。
以下、SELECT文に指定できる抽出オプション。
構文 意味 FROM 対象テーブルの指定 INNER JOIN 結合条件に一致する行を内部結合 LEFT / RIGHT / FULL OUTER JOIN 指定した側の不一致行も残して外部結合 WHERE 検索条件の指定 GROUP BY グループ化（特定条件で集計）の指定 HAVING グループ化後の検索条件の指定 ORDER BY 並び順の指定 DISTINCT 抽出結果から重複除く指定 ※ LEFT OUTER JOINは左側の表、RIGHT OUTER JOINは右側の表、FULL OUTER JOINは両側の表の不一致行も残す。
-
INSERT文（新規登録）
レコードの挿入（新規登録） を実行する命令文。
INSERT INTOの後に対象テーブル名とフィールド名を指定し、末尾に設定値を指定。
設定値に関しては値を直接指定するVALUESと抽出結果を設定するSELECTがある。 -
UPDATE文（更新）
レコードの更新を実行する命令文。
UPDATEの後に対象テーブル名を指定し、SETに更新するフィールド名と設定値を指定。
末尾にWHEREで更新対象の条件を記述し、対象範囲を指定。 -
DELETE文（削除）
レコードの削除を実行する命令文。
DELETE FROMの後に対象テーブル名を指定し、末尾にWHEREで削除対象の条件を記述し、対象範囲を指定。 -
副問合せ（サブクエリ）
SQLステートメント内部に入れ子で入っているSQL文を指す。
SELECT文だけでなく、INSERT、UPDATE、DELETEなどでも利用できる。
また、内側の副問合せが外側の問合せの列を参照し、外側の行ごとに関連付けて評価されるものを相関副問合せという。
-
WHEREで行を選び、HAVINGで集計結果を選ぶ
上の売上表から「一件100以上の売上だけを対象に、カテゴリごとの合計が250以上のもの」を取り出す。SELECT category, SUM(amount) AS total FROM example_sales WHERE amount >= 100 GROUP BY category HAVING SUM(amount) >= 250 ORDER BY category;WHEREによってBの50の二行と金額がNULLの一行は集計対象から外れる。その後Aは100+200=300、Cは300と集計され、どちらもHAVINGの条件を満たすため、結果はA・300、C・300の二行になる。
WHERE SUM(amount) >= 250とは書かない。WHEREで決めるのは集計へ入れる行であり、集計後の値を判定するのがHAVINGだからである。この役割の違いは PostgreSQLの集約関数の説明 でも確認できる。
データ制御言語：SQL-DCL（Data Control Language）
アクセス権を制御する命令言語で、代表的な命令文としてGRANT文とREVOKE文がある。
-
GRANT文
アクセス権限の付与を実行する命令文。
データベース利用者に対してテーブルやオブジェクトなどの操作権限を付与する。 -
REVOKE文
アクセス権限の取消を実行する命令文。
GRANT文で付与した権限を取消す。
トランザクション制御言語：SQL-TCL（Transaction Control Language）
トランザクションを制御する命令をTCLとして分ける場合、代表的な命令文としてCOMMIT文とROLLBACK文がある。教材によってはこれらをDCLに含めて説明する場合もある。
- COMMIT文
トランザクション内の変更を確定する命令文。 - ROLLBACK文
トランザクション内の未確定の変更を取り消し、トランザクション開始時または指定したセーブポイントの状態へ戻す命令文。
-
ROLLBACKで取り消される範囲を確かめる
次の例は上で作成した表のid=1の金額が100である状態から実行する。BEGIN; UPDATE example_sales SET amount = 110 WHERE id = 1; SELECT amount FROM example_sales WHERE id = 1; ROLLBACK; SELECT amount FROM example_sales WHERE id = 1;同じセッション内の最初のSELECTは110、ROLLBACK後のSELECTは100を返す。BEGINからROLLBACKまでの変更が取り消されたことを確かめられる。一方、COMMITで確定した変更をその後にROLLBACKしただけで過去へさかのぼって取り消すことはできない。
関連するSQL構文
以下、上記の言語カテゴリと併せて覚えておきたい代表的なSQL構文。
-
集計関数
行の集合に対して合計、平均、最大、最小、件数などを求める関数。 GROUP BY を指定した場合はグループごとに、指定しない場合は対象全体に対して集計する。
構文 意味 SUM 合計 AVG 平均 MAX 最大 MIN 最小 COUNT COUNT(*)は行数、COUNT(式)はNULLでない値の数 -
比較演算子
条件に指定する比較演算子で、以下の種類がある。
演算子 意味 < 小なり > 大なり <= 小なりイコール >= 大なりイコール <> ノットイコール IN(値\(1\), 値\(2\), 値\(3\) …) \(1\) OR \(2\) OR \(3\) …（複数のOR条件） BETWEEN \(1\) AND \(2\) 値1～値2の範囲（両端を含む） LIKE %や_を使ったパターン一致 EXISTS レコードの存在有無 -
集合演算
複数の SELECT 結果に対して和集合、積集合、差集合を求める。利用できる演算子はDBMSによって異なる。
構文 意味 UNION 双方のSELECT結果から同一レコードを集約して統合。 UNION ALL 双方のSELECT結果から同一レコードを集約せず統合。 INTERSECT 双方のSELECT結果に共通する行を取得。 EXCEPT 前のSELECT結果から後のSELECT結果に含まれる行を除外。 -
カーソル
SELECT 結果に対して\(1\) レコードずつ順に読み出す仕組み。
SQLの実装例では DECLARE カーソル名 CURSOR FOR SELECT ... のように問合せをカーソルとして定義。
OPEN でカーソルを開き、 FETCH で \(1\) レコードずつ読み込み、 CLOSE でカーソルを閉じる。 -
ビュー（VIEW）
-
複数テーブルの特定項目を参照用として1テーブルで表現したもので、 導出表 とも呼ばれる。
-
ビューを更新できる条件はDBMSによって異なる。一般に複数テーブルの結合、集計関数、DISTINCT、GROUP BY、集合演算などを含むビューは、そのままでは更新できない場合が多い。
-
-
ストアドプロシージャ
- データベース操作を一連の処理にまとめ、DBMS（データベース管理システム）に登録したものを指し、プロシージャ名で呼び出して使用。
-
NULLは0でも空文字でもなく、比較方法も異なる
同じ表を集計すると、行数と入力済み金額の件数は異なる。SELECT COUNT(*) AS rows_count, COUNT(amount) AS amount_count, SUM(amount) AS total FROM example_sales;結果は順に6、5、700。COUNT(*)は行を数え、
COUNT(amount)はamountがNULLでない行を数える。SUMもNULLを除いた値を足すため、未入力の一行を0円と判定したという意味ではない。未入力の行を探すときは
WHERE amount IS NULLとする。amount = NULLの比較結果は真にならないため、目的の行を抽出できない。未入力と0円の区別が必要な集計では、NULLをどう扱うかを先に決める。
まとめ
- 関係演算の選択は行を絞り、射影は列を絞る。結合は複数の表を関連付ける。
- WHEREは集計前、HAVINGは集計後に条件を適用。INNER JOINは一致する行、OUTER JOINは一致しない行も含めて取得。
- 副問い合わせはある問い合わせの結果を別の問い合わせの条件や値として利用。
- DDLはデータベース構造、DMLはデータ、DCLは権限、TCLはトランザクションを操作。ただし分類方法は教材やDBMSによって異なる。
-
GRANTは権限を与え、REVOKEは権限を取り消す。
-
WHEREは集計へ入れる行、HAVINGは集計後の結果を選ぶ。NULLを含む6行ではCOUNT(*)とCOUNT(amount)が異なり、ROLLBACKの効果は未確定の変更に限られる。