SQL文の中級編

SQL文の中級的内容のまとめメモ📝

• 内部結合

-- "users" と "orders" テーブルを内部結合して、全ユーザーの注文情報を取得
SELECT users.id AS user_id, users.name, orders.id AS order_id, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

• 左外部結合

-- ユーザーに関連する注文情報がない場合でもユーザー情報を取得
SELECT users.id AS user_id, users.name, orders.id AS order_id, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

• 右外部結合

-- 注文に関連するユーザーがいない場合でも注文情報を取得
SELECT users.id AS user_id, users.name, orders.id AS order_id, orders.total
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

• 自己結合

-- "employees" テーブルで、マネージャーと部下の関係を取得
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.id;

• 単一行サブクエリ

-- "orders" テーブルの最高注文金額を取得
SELECT * 
FROM orders
WHERE total = (SELECT MAX(total) FROM orders);

• 複数行サブクエリ

-- 特定の価格以上の商品を注文したユーザーを取得
SELECT name 
FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total >= 100);

• EXISTSのサブクエリ

-- 注文を持つユーザーを取得
SELECT name
FROM users
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

• ビュー（仮想テーブル）の作成と利用
  ※データの再利用性向上や複雑なクエリの簡略化、セキュリティ（不要なデータの隠蔽）が目的。

-- ユーザーごとの合計注文金額を表示するビューを作成
CREATE VIEW user_order_totals AS
SELECT users.id AS user_id, users.name, SUM(orders.total) AS total_spent
FROM users
INNER JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

-- ビューを利用してデータを取得
SELECT * FROM user_order_totals WHERE total_spent > 100;

• インデックスの作成
  ※クエリしたデータの検索速度（パフォーマンス）向上、

-- "users" テーブルの "name" カラムにインデックスを作成
CREATE INDEX idx_users_name ON users(name);

-- "orders" テーブルの "user_id" カラムにインデックスを作成
CREATE INDEX idx_orders_user_id ON orders(user_id);

• トランザクション制御
  ※DB操作における複数のSQLクエリをまとめて行うこと。
  ※ACID特性（=Atomicity（原子性）、Consistency（一貫性）、Isolation（独立性）、Durability（永続性））の保証。
  ※COMMIT: 変更を確定し、データベースに保存すること。
  ※ROLLBACK: エラーが発生した場合に、すべての変更を取り消してトランザクション開始前の状態に戻すこと。

-- トランザクションを開始して複数の操作を一括管理
BEGIN;

-- 操作1: ユーザーの作成
INSERT INTO users (name, age) VALUES ('Charlie', 28);

-- 操作2: 注文を追加
INSERT INTO orders (user_id, total) VALUES (LASTVAL(), 150);

-- 変更を確定
COMMIT;

-- エラー時に変更を元に戻す
ROLLBACK;

• 排他制御（Locking）
  ※データベース内で 複数のトランザクションが同時に同じデータを操作する際の競合を防ぐ仕組みのこと。

-- トランザクション分離レベルを設定してデータ整合性を保つ
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;

-- 他のトランザクションからのデータ変更を防ぐ
UPDATE users SET age = age + 1 WHERE id = 1;

COMMIT;