SIGMA-SE Tech Blog

SIGMA-SE Tech Blog


当サイトは、過去に運営していた別ドメイン（unisia-se.com）から sigma-se.com へ移行した技術ブログです。
旧サイトの記事をもとに、内容の精査・加筆・最新化を行い再構成しています。
正確で実用的な情報提供を目的としています。

応用情報技術 - 基礎：19/21 データベース操作（関係演算・SQL・権限管理）

概要

情報技術の基礎として理解しておきたいデータベース操作について、集合演算、関係演算、SQL-DDL、SQL-DML、SQL-DCL、その他のSQL構文を整理する。

データベース操作では、表から必要な行や列を取り出し、結合し、更新し、権限を制御する。
SQLの構文だけでなく、裏側にある集合演算と関係演算を理解すると、問題文を読みやすくなる。

この記事で扱うこと

  • 和、差、積、直積などの集合演算。
  • 選択、射影、結合、商などの関係演算。
  • CREATE、ALTER、DROP など DDL の役割。
  • SELECT、INSERT、UPDATE、DELETE など DML の役割。
  • GRANT、REVOKE など DCL の役割。

理解しておきたい要点

分野 整理する内容
関係演算 選択は行、射影は列、結合は表同士の関連。
SQL-DDL 表やビューなど構造を定義する命令。
SQL-DML データの検索、追加、更新、削除。
SQL-DCL 権限付与と権限取り消し。
副問い合わせ 問い合わせ結果を別の問い合わせ条件に使う構文。

集合／関係演算

データベースは、次の集合演算と関係演算でデータ集合を表現する。

  • 集合演算（和、差、積、直積）
    ※ 和、差、積については、応用情報技術 - 離散数学 > 集合 の集合演算と同じ。
    直積は、\(2\) つのテーブル\(A\)、\(B\)の全組合わせを表す。

    • 例：テーブル\(A\)とテーブル\(B\)の直積\(A \times B\)
      • テーブル\(A\)
        \(X\) \(Y\)
        \(1\)a
        \(2\)b
        \(3\)c
      • テーブル\(B\)
        \(X\) \(Z\)
        \(1\)\(d\)
        \(2\)\(e\)
        \(3\)\(f\)
      • 直積\(A × B\)
        \(A\).\(X\) \(A\).\(Y\) \(B\).\(X\) \(B\).\(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\)

  • 関係演算（選択、射影、結合、商）
    選択は、レコードを抽出する操作（演算）。
    射影は、フィールドを抽出する操作（演算）。
    結合は、直積に選択と射影を組合わせた操作（演算）で、双方に共通するレコードを抽出する内部結合（自然結合）と片方のレコードに存在する行のみを抽出する外部結合がある。
    商は、演算をテーブル\(A \div B\)とした場合、テーブル\(A\)から テーブル\(B\)のレコードに一致するレコードを抽出し、\(B\)のフィールドと重複レコードを削除する操作（演算）。

SQL

SQL（Structured Query Language）は、リレーショナルデータベース管理システム（RDBMS）において、データの操作や定義を行うためのデータベース言語で、次の \(4\) 種類のデータ言語に大別される。

また、SQLは国際標準化により多様なリレーショナルデータベースで利用できるようになっており、代表的なものとして下記のデータベースがある。

  • Oracle Database：有償で大規模データ向け。
  • PostgreSQL：無償で小規模～大規模データ向け。
  • MySQL：商用利用のみ有償で小規模～大規模データ向け。
  • SQLite：無償で小規模、中規模データ向け。
  • Microsoft SQL Server：有償で小規模～大規模データ向け。
  • Microsoft Access：有償（Office）で小規模データ向け。

データ定義言語：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など。

データ操作言語：SQL-DML（Data Manipulation Language）

既存のテーブルやビューに対して、データを操作する命令言語で、以下の命令文を用いたSQLを指す。

  • SELECT文（検索）
    レコードの検索（抽出）を実行する命令文。
    以下、SELECT文に指定できる抽出オプション。

    構文 意味
    FROM 対象テーブルの指定
    INNER JOIN 親テーブル（FROM）と内部結合（※）
    OUTER JOIN 親テーブル（FROM）と外部結合（※）
    WHERE 検索条件の指定
    GROUP BY グループ化（特定条件で集計）の指定
    HAVING グループ化後の検索条件の指定
    ORDER BY 並び順の指定
    DISTINCT 抽出結果から重複除く指定

    ※ 親テーブル（FROM）を土台とするLEFT OUTER JOINと、結合するテーブルを土台とするRIGHT OUTER JOINがある。

  • INSERT文（新規登録）
    レコードの挿入（新規登録） を実行する命令文。
    INSERT INTOの後に対象テーブル名とフィールド名を指定し、末尾に設定値を指定する。
    設定値に関しては、値を直接指定するVALUESと抽出結果を設定するSELECTがある。

  • UPDATE文（更新）
    レコードの更新を実行する命令文。
    UPDATEの後に対象テーブル名を指定し、SETに更新するフィールド名と設定値を指定する。
    末尾にWHEREで更新対象の条件を記述し、対象範囲を指定する。

  • DELETE文（削除）
    レコードの削除を実行する命令文。
    DELETEの後に対象テーブル名を指定し、末尾にWHEREで削除対象の条件を記述し、対象範囲を指定する。

  • 副問合せ（サブクエリ）
    SQLステートメント内部に入れ子で入っているSQL文を指す。
    ※ SELECT文で使用されるケースがほとんど
    また、共通テーブルを用いて、本体のSQLと入れ子にしているSQLを紐付けたSQLを相関副問合せという。

データ制御言語：SQL-DCL（Data Control Language）

アクセス権の制御や状態管理を操作する命令言語で、以下の命令文を用いたSQLを指す。

  • GRANT文
    アクセス権限の付与を実行する命令文。
    データベース利用者に対して、テーブルやオブジェクトなどの操作権限を付与する。

  • REVOKE文
    アクセス権限の取消を実行する命令文。
    GRANT文で付与した権限を取消す。

  • COMMIT文
    トランザクションの確定を実行する命令文。
    トランザクション内で発生したデータ更新を確定する。

  • ROLLBACK文
    トランザクションの破棄を実行する命令文。
    トランザクション内で発生したデータ更新を破棄し、無かったことにする。

その他のデータ言語（SQL構文）

以下、上記データ言語以外の代表的なSQL構文。

  • 集計関数
    GROUP BY（グループ化）の集計条件となる関数で以下の種類がある。

    構文 意味
    SUM 合計
    AVG 平均
    MAX 最大
    MIN 最小
    COUNT レコード数

  • 比較演算子
    条件に指定する比較演算子で、以下の種類がある。

    演算子 意味
    < 小なり
    > 大なり
    <= 小なりイコール
    >= 大なりイコール
    <> ノットイコール
    IN(値\(1\), 値\(2\), 値\(3\) …) \(1\) or \(2\) or \(3\) …（複数 or 条件）
    BETWEEN \(1\) TO \(2\) 値1～値2の間
    LIKE 前方後方部分一致
    EXISTS レコードの存在有無

  • 集合演算
    SELECT結果の和集合演算となるUNIONがある。

    構文 意味
    UNION 双方のSELECT結果から同一レコードを集約して統合する。
    UNION ALL 双方のSELECT結果から同一レコードを集約せず統合する。

  • カーソル
    SELECT結果に対して、\(1\) レコードずつ順に読み出す仕組み。
    SELECTの前にCURSOR FORを指定し、結果をDECLAREで任意の変数に格納する。
    OPENでカーソルを開き、FETCHで \(1\) レコードずつ読み込み、CLOSEでカーソルを閉じる。

  • ビュー（VIEW）

    • 複数テーブルの特定項目を参照用として1テーブルで表現したもので、導出表とも呼ばれる。
    • ビューは、主に参照用として利用されるが、下記の条件下で変更することも可能。
      • 複数のテーブルを結合していないこと。
      • 集計関数、DISTINCT、GROUP BYなどで複数レコードを集約していないこと。

  • ストアドプロシージャ

    • データベース操作を一連の処理にまとめ、DBMS（データベース管理システム）に登録したものを指し、プロシージャ名で呼び出して使用する。

違いを整理する

比較する項目 整理するポイント
選択と射影 選択はレコードを絞る、射影は項目を絞る。
WHEREとHAVING WHEREは集計前、HAVINGは集計後の条件。
INNER JOINとOUTER JOIN 一致する行だけか、一致しない行も残すかで異なる。
DDLとDML 構造を操作するか、データを操作するかで見分ける。
GRANTとREVOKE 権限を与えるか、取り消すかの違い。

実務とのつながり

  • SQL
    アプリケーション開発やデータ分析で日常的に使う。

  • 関係演算
    複雑なSQLを論理的に理解する助けになる。

  • 権限管理
    情報漏えいや誤更新を防ぐための基本となる。

まとめ

  • データベース操作は、集合演算と関係演算をSQLで表現するものと考えると理解しやすい。
  • DDLは構造、DMLはデータ、DCLは権限を扱う。
  • 選択、射影、結合の違いはSQL読解の基本となる。

参考文献

  • 瀬戸 美月 (\(2020\)) 『徹底攻略 応用情報技術者教科書』株式会社インプレス
「学習ノート」の最新 5件
・応用情報技術 - 基礎：21/21 データベース応用（DWH・OLAP・分散DB・ビッグデータ）
・応用情報技術 - 基礎：20/21 データベース管理（トランザクション・排他制御・障害回復）
・応用情報技術 - 基礎：19/21 データベース操作（関係演算・SQL・権限管理）
・応用情報技術 - 基礎：18/21 データベース設計（モデル・正規化・制約）
・応用情報技術 - 基礎：17/21 マルチメディア（音声・画像・CG・規格）

学習ノート 情報処理技術 SQL データベース 応用情報技術

2021年10月8日20:51

カテゴリー
タグ Angular Telegram OpenWeatherMap SCXML MeCab NumPy LaTeX MathML MathJax Matplotlib GitHub Git venv PostgresSQL Django Python Apache Nginx CentOS VPS Vim TensorFlow 古典暗号 暗号技術 基礎理論 機械学習 深層学習 ニューラルネットワーク SSL/TLS Let's Encrypt scikit-learn 共通鍵暗号 公開鍵暗号 現代暗号 耐量子暗号 換字式暗号 頻度分析 多表式暗号 近代暗号 軍事暗号 機械暗号 DNS SSH デバッグ Python基礎 タスク指向型対話 応用情報技術 データベース SQL Webシステム TypeScript Node.js データ構造 アルゴリズム OS ハードウェア UI/UX マルチメディア
Copyright SIGMA-SE All Rights Reserved.
s-hama@sigma-se.jp