3.2.2.1. データ定義言語

1. 概要

　データ定義言語（Data Definition Language, DDL）は、SQLの重要な構成要素の一つです。DDLは、データベースの構造を定義し、管理するために使用されます。具体的には、スキーマ、テーブル、ビュー、および処理権限を定義するためのSQL文を提供します。

　DDLの重要性は、以下の点にあります：

1. データベース構造の設計と実装
2. データの整合性と一貫性の確保
3. セキュリティとアクセス制御の管理
4. パフォーマンスの最適化

　DDLの理解は不可欠です。データベース設計やアプリケーション開発において、DDLの知識は基礎となり、実務でも頻繁に使用されます。

2. 詳細説明

2.1. スキーマとテーブルの定義

　スキーマは、データベース内の論理的な構造を表します。テーブルは、実際にデータを格納する基本的な単位です。

スキーマの作成

CREATE SCHEMA schema_name;

テーブルの作成

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

　スキーマとテーブルは、データベースの設計とデータの格納方法を決定する重要な構成要素です。スキーマはデータベース全体の論理的な枠組みを提供し、テーブルはその枠組みの中で実際にデータを管理するための具体的な方法を示します。

2.2. データ型

　SQLでは、様々なデータ型が利用可能です。主な型は以下の通りです：

1. 文字型：CHAR（固定長文字列）、VARCHAR（可変長文字列）
2. 数値型：INTEGER（整数）、DECIMAL（精度の高い数値）、FLOAT（浮動小数点数）
3. 日付型：DATE（日付）、TIME（時間）、TIMESTAMP（タイムスタンプ） 　各データ型は、保存する情報の種類とその操作に最適な方法を定義します。例えば、CHARは常に固定長の文字列を保存するため、パフォーマンス上の利点がありますが、VARCHARは可変長でメモリ効率が良いです。

2.3. 制約

　データの整合性を保つために、以下の制約を定義できます：

1. 一意性制約：UNIQUE（一意の値を強制）
2. 参照制約：FOREIGN KEY（他のテーブルの主キーを参照）
3. 検査制約：CHECK（特定の条件を満たすことを強制）
4. 非NULL制約：NOT NULL（NULL値を許可しない）

例：

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department_id INTEGER,
    salary DECIMAL(10,2) CHECK (salary > 0),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

　PRIMARY KEYは、各行を一意に識別するためのキーであり、FOREIGN KEYは、別のテーブルとのリレーションシップを確立するために使用されます。これにより、データの整合性と関連性を確保できます。

2.4. ビューの定義

　ビューは、1つ以上のテーブルから導出される仮想的なテーブルです。ビューは、データの表示方法を柔軟に制御し、セキュリティやデータの簡略化に役立ちます。

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

更新可能なビューと更新不可能なビュー

1. 更新可能なビュー：単一のテーブルに基づき、集約関数や DISTINCT を使用しないビュー
2. 更新不可能なビュー：複数のテーブルを結合したり、集約関数を使用したビュー 　ビューはデータのアクセスや表示を制御するための便利な方法で、ユーザーに必要な情報だけを提供することができます。

2.5. 処理権限の定義

　アクセス権を管理するために、GRANT文とREVOKE文を使用します。

GRANT privilege_name ON object_name TO user_name;
REVOKE privilege_name ON object_name FROM user_name;

　これにより、異なるユーザーや役割に応じた柔軟なアクセス制御が可能になります。

3. 応用例

3.1. Eコマースシステムでの活用

　Eコマースシステムでは、商品、注文、顧客情報などを管理するためにDDLが使用されます。例えば、新しい商品が追加されたときにテーブルを更新したり、在庫数が少ない商品のビューを作成したりする場面でDDLが役立ちます。

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    stock INTEGER NOT NULL CHECK (stock >= 0)
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE VIEW low_stock_products AS
SELECT name, stock
FROM products
WHERE stock < 10;

3.2. 銀行システムでのセキュリティ管理

　銀行システムでは、セキュリティが極めて重要です。DDLを使用して適切なアクセス権を設定し、データの安全性を確保します。例えば、新しいユーザーが追加された場合にそのユーザーに対する権限を設定したり、不要になった権限を削除したりする際にDDLが使われます。

GRANT SELECT, INSERT ON accounts TO teller;
GRANT ALL PRIVILEGES ON transactions TO manager;
REVOKE DELETE ON accounts FROM teller;

4. 例題

例題1

　以下の要件に基づいて、studentsテーブルを作成するSQL文を書いてください。

• 学生ID（整数型、主キー）
• 氏名（50文字以内の文字列、NULL不可）
• 生年月日（日付型）
• GPA（小数点以下2桁までの数値、0.0から4.0の間）

回答例：

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    birth_date DATE,
    gpa DECIMAL(3,2) CHECK (gpa >= 0.0 AND gpa <= 4.0)
);

例題2

　employeesテーブルから、部門ごとの平均給与を表示するdept_avg_salaryビューを作成してください。

回答例：

CREATE VIEW dept_avg_salary AS
SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id;

5. まとめ

　SQLのデータ定義言語（DDL）は、データベースの構造を定義し管理するための重要なツールです。主な機能として、以下があります：

1. スキーマとテーブルの作成
2. データ型の指定（文字型、数値型、日付型など）
3. 制約の設定（一意性制約、参照制約、検査制約、非NULL制約）
4. ビューの定義と管理
5. アクセス権の制御 　DDLの適切な使用により、効率的で安全なデータベース設計が可能になります。これらの概念を十分に理解し、実際のシステム開発や運用に活かせるようになることが求められます。

追加の学習リソース

　詳細な学習には以下のリソースを参照してください：

• SQLの公式ドキュメント
• オンラインSQLチュートリアル