単一テーブルクエリ
主要クエリ構文
| 構文 | 説明 | 使用例 |
|---|
| SELECT 列名 FROM テーブル | 特定列を取得 | SELECT employee_name, position FROM staff; |
| SELECT DISTINCT 列名 | 重複除外 | SELECT DISTINCT department FROM staff; |
| WHERE 条件 | 条件指定 | SELECT * FROM staff WHERE salary > 5000; |
| BETWEEN 値1 AND 値2 | 範囲指定 | SELECT * FROM staff WHERE hire_date BETWEEN '2020-01-01' AND '2023-01-01'; |
| LIKE 'パターン' | 部分一致検索 | SELECT * FROM staff WHERE employee_name LIKE '山%'; |
| GROUP BY 列名 | グループ化 | SELECT department, AVG(salary) FROM staff GROUP BY department; |
| ORDER BY 列名 | ソート | SELECT * FROM staff ORDER BY hire_date DESC; |
| LIMIT 件数 | 取得行数制限 | SELECT * FROM staff LIMIT 5; |
サンプルスキーマ
CREATE DATABASE company;
USE company;
CREATE TABLE staff (
id INT PRIMARY KEY,
employee_name VARCHAR(20) UNIQUE,
position VARCHAR(20),
salary DECIMAL(10,2),
department_id INT
);
INSERT INTO staff VALUES
(101, '田中', 'マネージャー', 650000, 1),
(102, '佐藤', 'エンジニア', 480000, 2);
集計関数の使用例
-- 平均給与計算
SELECT AVG(salary) FROM staff WHERE department_id = 2;
-- 部署別最大給与
SELECT department_id, MAX(salary)
FROM staff
GROUP BY department_id
HAVING MAX(salary) > 600000;
複数テーブルクエリ
テストデータ作成
CREATE DATABASE library;
USE library;
CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
user_name VARCHAR(30) UNIQUE
);
CREATE TABLE books (
book_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(50) UNIQUE,
status VARCHAR(10) DEFAULT 'available'
);
CREATE TABLE loans (
loan_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
book_id INT,
loan_date DATE
);
内部結合の実装
-- 書籍と貸出情報の結合
SELECT b.title, u.user_name, l.loan_date
FROM books b
INNER JOIN loans l ON b.book_id = l.book_id
INNER JOIN users u ON l.user_id = u.user_id;
外部結合の応用
-- 全ユーザーの貸出状況（書籍未貸出も表示）
SELECT u.user_name, b.title
FROM users u
LEFT JOIN loans l ON u.user_id = l.user_id
LEFT JOIN books b ON l.book_id = b.book_id;
サブクエリ活用
-- 平均価格以上の書籍
SELECT title FROM books
WHERE price >= (SELECT AVG(price) FROM books);
外部キー制約の設定
ALTER TABLE loans
ADD CONSTRAINT fk_user
FOREIGN KEY (user_id)
REFERENCES users(user_id)
ON DELETE CASCADE;
ALTER TABLE loans
ADD CONSTRAINT fk_book
FOREIGN KEY (book_id)
REFERENCES books(book_id)
ON UPDATE CASCADE;
外部キー動作の検証
-- 親レコード削除時の連鎖削除
DELETE FROM users WHERE user_id = 1001;
-- 主キー更新時の連鎖更新
UPDATE books SET book_id = 2001 WHERE book_id = 100;