はじめに
SQL（Structured Query Language）は、リレーショナルデータベース管理システム（RDBMS）でデータを操作するための標準的な言語です。SQLを使用すると、データベースに対してデータの挿入、更新、削除、検索などの操作を行うことができます。今回は、SQLの基本的な内容を整理してみました。
目次
SQLの実行環境
SQLは多くのデータベース管理システムでサポートされています。
- MySQL
- PostgreSQL
- SQLite
- Microsoft SQL Server
- Oracle Database
- Amazon RDS（リレーショナルデータベースサービス）
- Amazon Redshift（データウェアハウスサービス）
基本的な構文
1. データベースの作成
CREATE DATABASE my_database;
2. テーブルの作成
CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
hire_date DATE
);
3. データの挿入
INSERT INTO employees (id, first_name, last_name, email, hire_date)
VALUES (1, 'kimura', 'tarou', 'kimura@example.com', '2023-01-01');
4. データの更新
UPDATE employees
SET email = 'kimura.tarou@newdomain.com'
WHERE id = 1;
5. データの削除
DELETE FROM employees
WHERE id = 1;
6. データの検索
SELECT * FROM employees;
7. 条件付きデータの検索
SELECT * FROM employees
WHERE hire_date > '2022-01-01';
8. データの並び替え
SELECT * FROM employees
ORDER BY last_name ASC;
9. 集計関数
SELECT COUNT(*) AS total_employees FROM employees;
10. グループ化
SELECT hire_date, COUNT(*) AS total_hired
FROM employees
GROUP BY hire_date;
高度な構文
1. JOIN（結合）
異なるテーブル間で関連するデータを結合するために使用します。
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.id;
2. サブクエリ
クエリの中に別のクエリを含めることができます。
SELECT first_name, last_name
FROM employees
WHERE department_id = (SELECT id FROM departments WHERE department_name = 'Sales');
3. CASE文
条件に基づいて異なる値を返すために使用します。
SELECT first_name, last_name,
CASE
WHEN hire_date < '2020-01-01' THEN 'Veteran'
ELSE 'Newcomer'
END AS employee_status
FROM employees;
4. インデックスの作成
検索パフォーマンスを向上させるためにインデックスを作成します。
CREATE INDEX idx_last_name ON employees (last_name);
5. トランザクション
一連の操作を一つの単位として実行し、全ての操作が成功するか、全てが失敗するかを保証します。
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
主キーと主キー制約
主キー（Primary Key）と主キー制約（Primary Key Constraint）は、リレーショナルデータベースにおいて、データの一意性と整合性を保証するための重要な機能です。主キーは、テーブル内の各行を一意に識別するための列または列の組み合わせです。主キー制約は、主キーとして指定された列が一意であり、NULL値を持たないことを保証します。
主キーの特徴
- 一意性 主キーの値はテーブル内で一意でなければなりません。つまり、同じ値を持つ行が複数存在してはなりません。
- 非NULL 主キーの列はNULL値を持つことができません。すべての行に対して有効な値が必要です。
- 単一または複数の列 主キーは単一の列で構成されることもあれば、複数の列の組み合わせで構成されることもあります（複合主キー）。
主キー制約の定義
主キー制約は、テーブルを作成する際に指定することができます。また、既存のテーブルに対して後から追加することも可能です。
テーブル作成時に主キー制約を指定する
CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
hire_date DATE
);
上記の例では、id列が主キーとして指定されています。
複合主キーの例
CREATE TABLE orders (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);
この例では、order_idとproduct_idの組み合わせが主キーとして指定されています。
既存のテーブルに主キー制約を追加する
ALTER TABLE employees
ADD CONSTRAINT pk_employees PRIMARY KEY (id);
主キー制約の利点
- データの一意性を保証 主キー制約により、テーブル内の各行が一意に識別されることが保証されます。
- データの整合性を保つ 主キー制約は、データの重複やNULL値を防ぐため、データの整合性を保つのに役立ちます。
- パフォーマンスの向上 主キー制約により、データベースはインデックスを自動的に作成し、検索や結合操作のパフォーマンスが向上します。
主キー制約の例
単一列の主キー制約
CREATE TABLE students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
enrollment_date DATE
);
複合主キー制約
CREATE TABLE enrollment (
student_id INT,
course_id INT,
enrollment_date DATE,
PRIMARY KEY (student_id, course_id)
);
主キー制約の削除
既存の主キー制約を削除することも可能です。
ALTER TABLE employees
DROP CONSTRAINT pk_employees;
主キーとユニークキー
主キー（Primary Key）とユニークキー（Unique Key）は、どちらもデータベース内のデータの一意性を保証するための重要な制約です。主キーはテーブル内で一意の識別子として機能し、NULL値を許容しません。一方、ユニークキーは複数設定可能で、NULL値を許容します。これらの制約を適切に使用することで、データの整合性とパフォーマンスを向上させることができます。
主キー（Primary Key）
-
一意性
- 主キーはテーブル内の各行を一意に識別します。主キーの値はテーブル内で一意でなければなりません。
-
非NULL
- 主キーの列はNULL値を持つことができません。すべての行に対して有効な値が必要です。
-
テーブルごとに1つ
- 各テーブルには1つの主キーしか存在できません。主キーはテーブルの一意の識別子として機能します。
-
インデックスの自動作成
- 主キー制約を設定すると、データベースは自動的にインデックスを作成します。これにより、検索や結合操作のパフォーマンスが向上します。
-
デフォルトのクラスター化インデックス
- 多くのデータベースシステムでは、主キーはデフォルトでクラスター化インデックスとして作成されます。これは、データが物理的に主キーの順序で格納されることを意味します。
主キーの例
CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
hire_date DATE
);
ユニークキー（Unique Key）
-
一意性
- ユニークキーもテーブル内の各行の値が一意であることを保証します。ただし、ユニークキーはNULL値を許容します（ただし、データベースによってはNULL値も一意である必要があります）。
-
NULL値の許容
- ユニークキーの列はNULL値を持つことができます。ただし、複数のNULL値が存在する場合、それらは一意と見なされます。
-
複数のユニークキー
- 各テーブルには複数のユニークキーを設定することができます。これにより、複数の列または列の組み合わせに対して一意性を保証できます。
-
インデックスの自動作成
- ユニークキー制約を設定すると、データベースは自動的にインデックスを作成します。これにより、検索や結合操作のパフォーマンスが向上します。
-
非クラスター化インデックス
- 多くのデータベースシステムでは、ユニークキーはデフォルトで非クラスター化インデックスとして作成されます。これは、データが物理的にユニークキーの順序で格納されないことを意味します。
ユニークキーの例
CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100) UNIQUE,
hire_date DATE
);
主キーとユニークキーの違いのまとめ
| 特徴 | 主キー（Primary Key） | ユニークキー（Unique Key） |
|---|---|---|
| 一意性 | テーブル内で一意 | テーブル内で一意 |
| NULL値の許容 | 許容されない | 許容される（ただし、データベースによる） |
| テーブルごとの数 | 1つのみ | 複数設定可能 |
| インデックス | 自動的にインデックスが作成される | 自動的にインデックスが作成される |
| クラスター化 | デフォルトでクラスター化インデックス | デフォルトで非クラスター化インデックス |
参考文献
オラクルマスター教科書 Silver SQL Oracle Database SQL
https://www.shoeisha.co.jp/book/detail/9784798172361