SQLテーブルの作成とデータ型の基礎から実践まで

SQLテーブルの基礎と実践

SQLテーブルの作成と基本構文

テーブルを作成する基本的なCREATE TABLE文の構文を見ていきましょう。以下は社員情報を管理するテーブルの作成例です：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE DEFAULT CURRENT_DATE,
    department_id INT,
    salary DECIMAL(10,2)
)

 

このコードでは、以下の要素を定義しています：

• PRIMARY KEY制約（employee_id）
• NOT NULL制約（氏名フィールド）
• UNIQUE制約（email）
• デフォルト値（hire_date）
• 数値型と精度の指定（salary）

SQLテーブルのデータ型と使い分け

主要なデータ型とその特徴を表で整理しました：

データ型	用途	サイズ	使用例
INT	整数値	4バイト	社員ID、年齢
DECIMAL	小数を含む数値	可変	給与、単価
VARCHAR	可変長文字列	指定長まで	名前、住所
CHAR	固定長文字列	固定	性別、国コード
DATE	日付	3バイト	入社日、生年月日
TIMESTAMP	日時	8バイト	更新日時、ログ時刻

SQLテーブルの制約と整合性

データの整合性を保つための主要な制約について説明します：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE DEFAULT CURRENT_DATE,
    total_amount DECIMAL(10,2) CHECK (total_amount > 0),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
)

 

制約の種類と役割：

• PRIMARY KEY：レコードの一意性を保証
• FOREIGN KEY：他テーブルとの参照整合性を維持
• CHECK：入力値の妥当性をチェック
• DEFAULT：値が指定されない場合のデフォルト値を設定

SQLテーブルのインデックス設計

効率的なクエリ実行のためのインデックス作成方法を解説します：

-- 単一カラムインデックス
CREATE INDEX idx_employee_name 
ON employees (last_name)
-- 複合インデックス
CREATE INDEX idx_order_customer 
ON orders (customer_id, order_date)

 

インデックスを作成する際の注意点：

• 検索頻度の高いカラムを選択
• 更新頻度とのバランスを考慮
• カーディナリティ（値の種類の多さ）を考慮

SQLテーブルのパフォーマンスチューニング

テーブル設計時のパフォーマンス最適化のポイントをまとめます：

1. 正規化レベルの適切な選択
   
   
   
   • 第3正規形までを基本とする
   • 必要に応じて非正規化を検討

2. パーティショニングの活用
   

   
      CREATE TABLE sales (
          sale_id INT,
          sale_date DATE,
          amount DECIMAL(10,2)
      ) PARTITION BY RANGE (YEAR(sale_date))
      

3. 適切なストレージエンジンの選択
   
   
   
   • InnoDB：トランザクション重視
   • MyISAM：読み取り重視

4. 効率的なデータ型の選択
   
   
   
   • 必要最小限のサイズ指定
   • 適切な精度の設定

SQLテーブルの運用管理

日常的な運用管理に必要な操作をまとめます：

1. テーブル構造の変更
   

   
   -- カラムの追加
   ALTER TABLE employees 
   ADD COLUMN bonus DECIMAL(10,2)
   -- インデックスの追加
   ALTER TABLE employees 
   ADD INDEX idx_department (department_id)
   

2. テーブルのメンテナンス
   

   
   -- テーブルの最適化
   OPTIMIZE TABLE employees
   -- 統計情報の更新
   ANALYZE TABLE employees
   

3. バックアップと復元
   

   
   -- テーブル定義のエクスポート
   SHOW CREATE TABLE employees
   -- データのバックアップ
   mysqldump -u username -p database_name employees > backup.sql
   

4. 監視と統計情報

• テーブルサイズの確認
• クエリパフォーマンスの監視
• ディスク使用量の管理

 

これらの運用管理作業は、定期的なメンテナンススケジュールに組み込むことをお勧めします。