【入門】データベースとは？SQLの基本を理解しよう

データベースは現代のアプリケーション開発において欠かせない存在です。この記事では、データベースの基本概念とSQLの基礎について、初心者にもわかりやすく解説します。

データベースとは？

データベースとは、データを効率的に保存、管理、検索するためのシステムです。以下のような特徴があります：

1. データの永続化

   • データをファイルとして保存
   • アプリケーション終了後もデータを維持

2. データの一貫性

   • データの整合性を保証
   • 矛盾のない状態を維持

3. 同時アクセス制御

   • 複数のユーザーが同時にアクセス可能
   • データの競合を防止

4. セキュリティ管理

   • アクセス権限の制御
   • データの保護

リレーショナルデータベース

最も一般的なデータベースの形式は「リレーショナルデータベース」です。

特徴

1. テーブル形式

   • データを行と列で表現
   • 各列は特定の型を持つ

2. 関係性の定義

   • テーブル間の関連付け
   • 外部キーによる参照

3. 正規化

   • データの重複を削減
   • 一貫性を維持

主要な用語

• テーブル: データを格納する表
• レコード: テーブルの1行のデータ
• カラム: テーブルの列（項目）
• 主キー: レコードを一意に識別する値
• 外部キー: 他のテーブルを参照するためのキー

SQLの基本

SQLは「Structured Query Language」の略で、データベースを操作するための言語です。

1. データの取得（SELECT）

-- 基本的なSELECT文
SELECT * FROM users;

-- 特定のカラムを選択
SELECT name, email FROM users;

-- 条件付きの取得
SELECT * FROM users WHERE age >= 20;

-- 並び替え
SELECT * FROM users ORDER BY name ASC;

-- 件数制限
SELECT * FROM users LIMIT 10;

2. データの挿入（INSERT）

-- 1件のデータを挿入
INSERT INTO users (name, email, age)
VALUES ('山田太郎', 'yamada@example.com', 25);

-- 複数件のデータを一度に挿入
INSERT INTO users (name, email, age) VALUES
  ('佐藤花子', 'sato@example.com', 30),
  ('鈴木一郎', 'suzuki@example.com', 28);

3. データの更新（UPDATE）

-- 条件に合致するレコードを更新
UPDATE users
SET age = 26
WHERE name = '山田太郎';

-- 複数のカラムを同時に更新
UPDATE users
SET 
  email = 'new.yamada@example.com',
  updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

4. データの削除（DELETE）

-- 条件に合致するレコードを削除
DELETE FROM users
WHERE age < 20;

-- テーブルの全レコードを削除
DELETE FROM users;

5. テーブルの作成（CREATE TABLE）

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  age INTEGER,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

実践的なSQL例

1. ユーザーと注文の関連付け

-- テーブルの作成
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  product_name VARCHAR(100) NOT NULL,
  price INTEGER NOT NULL,
  ordered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- データの挿入
INSERT INTO users (name, email) VALUES
  ('山田太郎', 'yamada@example.com'),
  ('佐藤花子', 'sato@example.com');

INSERT INTO orders (user_id, product_name, price) VALUES
  (1, 'ノートPC', 80000),
  (1, 'マウス', 5000),
  (2, 'キーボード', 15000);

-- ユーザーごとの注文一覧を取得
SELECT 
  users.name,
  orders.product_name,
  orders.price,
  orders.ordered_at
FROM users
JOIN orders ON users.id = orders.user_id
ORDER BY users.name, orders.ordered_at;

2. 集計クエリ

-- ユーザーごとの注文合計金額
SELECT 
  users.name,
  COUNT(orders.id) as order_count,
  SUM(orders.price) as total_amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

-- 商品カテゴリごとの売上集計
SELECT 
  category,
  COUNT(*) as sale_count,
  SUM(price) as total_sales,
  AVG(price) as avg_price
FROM products
JOIN orders ON products.id = orders.product_id
GROUP BY category
HAVING total_sales >= 100000
ORDER BY total_sales DESC;

3. サブクエリの活用

-- 平均価格より高い商品を検索
SELECT name, price
FROM products
WHERE price > (
  SELECT AVG(price) FROM products
);

-- 最も注文の多いユーザーを検索
SELECT 
  users.name,
  order_count.count
FROM users
JOIN (
  SELECT 
    user_id,
    COUNT(*) as count
  FROM orders
  GROUP BY user_id
) order_count ON users.id = order_count.user_id
WHERE order_count.count = (
  SELECT COUNT(*) as count
  FROM orders
  GROUP BY user_id
  ORDER BY count DESC
  LIMIT 1
);

データベース設計の基本

1. 正規化

データベース設計で重要な「正規化」について説明します。

第1正規形（1NF）

• 各列が原子値（これ以上分割できない値）を持つ
• 同じような項目を複数持たない

-- 悪い例
CREATE TABLE contacts (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100),
  phone1 VARCHAR(20),
  phone2 VARCHAR(20),
  phone3 VARCHAR(20)
);

-- 良い例
CREATE TABLE contacts (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE contact_phones (
  id INTEGER PRIMARY KEY,
  contact_id INTEGER,
  phone_number VARCHAR(20),
  FOREIGN KEY (contact_id) REFERENCES contacts(id)
);

第2正規形（2NF）

• 第1正規形を満たす
• 部分関数従属を持たない

第3正規形（3NF）

• 第2正規形を満たす
• 推移関数従属を持たない

2. インデックス

検索性能を向上させるためのインデックスについて説明します。

-- 単一カラムのインデックス
CREATE INDEX idx_users_email ON users(email);

-- 複合インデックス
CREATE INDEX idx_orders_user_date ON orders(user_id, ordered_at);

-- ユニークインデックス
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

3. トランザクション

データの一貫性を保つためのトランザクション処理について説明します。

-- トランザクションの基本
BEGIN TRANSACTION;

INSERT INTO orders (user_id, product_id, quantity)
VALUES (1, 100, 2);

UPDATE products
SET stock = stock - 2
WHERE id = 100;

COMMIT;

-- エラー時のロールバック
BEGIN TRANSACTION;

INSERT INTO orders (user_id, product_id, quantity)
VALUES (1, 100, 2);

UPDATE products
SET stock = stock - 2
WHERE id = 100;

-- 在庫が足りない場合はロールバック
IF (SELECT stock FROM products WHERE id = 100) < 0 THEN
  ROLLBACK;
ELSE
  COMMIT;
END IF;

セキュリティの基本

1. SQLインジェクション対策

-- 悪い例（SQLインジェクションの危険あり）
const query = `SELECT * FROM users WHERE name = '${userName}'`;

-- 良い例（プリペアドステートメントを使用）
const query = 'SELECT * FROM users WHERE name = ?';
db.query(query, [userName]);

2. アクセス権限の設定

-- ユーザーの作成
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password';

-- 権限の付与
GRANT SELECT, INSERT ON database_name.* TO 'app_user'@'localhost';

-- 権限の確認
SHOW GRANTS FOR 'app_user'@'localhost';

まとめ

この記事では、データベースとSQLの基本について解説しました：

1. データベースの基本概念

   • データの永続化
   • 一貫性の保持
   • 同時アクセス制御

2. SQLの基本操作

   • SELECT（データの取得）
   • INSERT（データの挿入）
   • UPDATE（データの更新）
   • DELETE（データの削除）

3. データベース設計の基本

   • 正規化
   • インデックス
   • トランザクション

4. セキュリティ対策

   • SQLインジェクション対策
   • アクセス権限の管理

データベースは現代のアプリケーション開発において非常に重要な要素です。基本をしっかり理解し、適切に活用することで、より堅牢なアプリケーションを開発することができます。

次のステップ

データベースの基本を理解したら、以下の記事も参考にしてください：

• 【図解】リスト（配列）と辞書（オブジェクト）の違いを解説
• プログラミング初心者が最初に学ぶべき5つの概念
• 【初心者向け】プログラミングのエラーの種類と対処法