- Day 52:SQL基礎 ― SELECT・INSERT・UPDATE・DELETEでユーザー管理データベースを操作する
- 今日のゴール
- SQLとCRUDの関係を整理する
- 今回作るユーザー管理データベース
- SQLを読む前に、最低限の書き方を覚える
- 実習ファイルを準備する
- SELECTでデータを取得する
- 検索条件を少し広げる
- INSERTでユーザーを登録する
- UPDATEでユーザーを更新する
- DELETEでユーザーを削除する
- トランザクションで変更を確定・取り消しする
- 実践:一連の操作をPythonから実行する
- SQLと入力値を分ける理由を深掘りする
- 更新・削除を安全にする確認手順
- 外部キーのあるユーザーを削除するとどうなるか
- Next.jsの画面操作とSQLを対応させる
- よくあるつまずきと確認方法
- 自力で取り組む練習問題
- 初心者向けのSQLテンプレート
- Day 52のチェックリスト
- まとめ
Day 52:SQL基礎 ― SELECT・INSERT・UPDATE・DELETEでユーザー管理データベースを操作する
Day 52では、データベースに保存された情報を操作するためのSQLを学びます。Day 51で理解したTable、Row、Column、Primary Keyを土台に、今回はユーザーの登録・一覧表示・編集・削除を実践します。
大切なのは、SQLの文法を暗記することだけではありません。「どのテーブルの、どの行に、何をするのか」を明確にして、安全に操作することです。
今日のゴール
今回の学習では、次の状態を目指します。
SELECTで必要なユーザー情報を取得できます。INSERTでユーザーを登録できます。UPDATEで指定したユーザーを更新できます。DELETEで指定したユーザーを削除できます。WHEREで操作対象を絞り込めます。- SQLの構造と入力値を分けて、安全に実行できます。
- 変更の確定と取り消しを区別できます。
今回はNext.jsへの接続前の練習として、SQLiteをPythonから操作します。PythonはSQLを実行する補助として使い、学習の中心はSQLそのものに置きます。
SQLとCRUDの関係を整理する
CRUDは、アプリがデータを扱う基本操作をまとめた言葉です。今回のSQLでは、次のように対応します。
| CRUD | 意味 | SQL | ユーザー管理での例 |
|---|---|---|---|
| Create | 追加 | INSERT | ユーザーを登録します |
| Read | 取得 | SELECT | ユーザー一覧を表示します |
| Update | 更新 | UPDATE | 名前や年齢を変更します |
| Delete | 削除 | DELETE | ユーザーを削除します |
SQLiteでは、これらのSQL文によって、行の追加・取得・更新・削除を行えます。(🔗 sqlite.org)
ここで、CRUDのCreateとCREATE TABLEを混同しないようにします。
CREATE TABLEは、データを保存する表を作ります。INSERTは、作成済みの表へデータを追加します。
今日は最初に表を用意し、その後は主に4つのSQLで行を操作します。
今回作るユーザー管理データベース
今回は、usersというテーブルをひとつ作ります。
初期データ
| id | name | age | is_active | |
|---|---|---|---|---|
| 1 | 葵 | 🔗 aoi@example.com | 24 | 1 |
| 2 | 蓮 | 🔗 ren@example.com | 30 | 1 |
| 3 | 凛 | 🔗 rin@example.com | NULL | 0 |
それぞれの列には、次の役割を持たせます。
| 列名 | 役割 | 今回のルール |
|---|---|---|
id | ユーザーを識別します | 整数の主キー |
name | 名前を保存します | 未設定不可、空白だけの名前も不可 |
email | メールアドレスを保存します | 未設定不可、重複不可 |
age | 年齢を保存します | 未設定可、登録する場合は0〜150 |
is_active | 利用状態を保存します | 1が有効、0が無効 |
ageは説明用の項目です。実際のアプリでは、必要性や更新方法を考えて、年齢を保存するか、生年月日から求めるかなどを別途設計します。
今回使う名前やメールアドレスは、学習用のダミーデータです。
is_activeは今回の状態管理用の列です
この実習では、1を有効、0を無効として使います。
ただし、この列を作るだけでログイン制御やアクセス制限が完成するわけではありません。状態をどの処理で確認するかは、アプリ側で実装します。
SQLを読む前に、最低限の書き方を覚える
まず、短いSQLを見てみます。
-- usersテーブルから、IDと名前を取得します
SELECT id, name
FROM users;SQLこのSQLは、「usersから、idとnameを取得する」という意味です。
今回の記事では、読みやすくするため、次の書き方で統一します。
- SQLのキーワードは
SELECTのように大文字で書きます。 - テーブル名と列名は
usersやnameのように小文字で書きます。 - 文の終わりには
;を付けます。 - 文字列は
'葵'のようにシングルクォートで囲みます。 - 説明コメントには
--を使います。
SQLでは、文字列の値と列名を区別することが重要です。今回は文字列をシングルクォートで表します。(🔗 sqlite.org)
実習ファイルを準備する
今回は、Day 51のデータベースを変更せず、別の学習用データベースを作ります。
day52-sql/
├── schema.sql
└── practice.py
実行すると、同じフォルダーにday52.dbが作られます。
実データのあるデータベースではなく、この実習専用のファイルで操作してください。
テーブルと初期データを用意する
schema.sql
-- テーブル作成と初期登録を、ひとまとまりで実行します
BEGIN;
-- ユーザーを保存するテーブルです
CREATE TABLE users (
-- ユーザーを一意に識別します
id INTEGER PRIMARY KEY,
-- 前後の通常の空白を除き、1〜50文字に制限します
name TEXT NOT NULL
CHECK (length(trim(name)) BETWEEN 1 AND 50),
-- 今回は保存値の重複を禁止します
email TEXT NOT NULL UNIQUE,
-- 年齢は未設定を許可します
age INTEGER
CHECK (age BETWEEN 0 AND 150),
-- 有効状態は0または1に制限します
is_active INTEGER NOT NULL DEFAULT 1
CHECK (is_active IN (0, 1))
);
-- 学習用のユーザーを登録します
INSERT INTO users (id, name, email, age, is_active)
VALUES
(1, '葵', 'aoi@example.com', 24, 1),
(2, '蓮', 'ren@example.com', 30, 1),
(3, '凛', 'rin@example.com', NULL, 0);
-- ここまでの変更を確定します
COMMIT;SQLNOT NULL、UNIQUE、CHECK、DEFAULTは、それぞれ値の制限や既定値を定義します。今回のageではNOT NULLを付けていないため、年齢未設定のNULLを許可します。(🔗 sqlite.org)
この定義は学習用です。メール形式の検証や、大文字・小文字を含むメールアドレスの重複判定方針まで完成しているわけではありません。
また、通常のSQLiteの型宣言だけで、すべての不正な型を厳密に拒否できると考えないようにします。アプリ側でも、入力値の型や範囲を確認する設計が必要です。(🔗 sqlite.org)
SELECTでデータを取得する
SELECTは、保存されているデータを読み取るためのSQLです。
まずは、どの列が必要かを指定します。
ユーザー一覧を取得する
-- 全ユーザーの主要項目を取得します
SELECT
id,
name,
email,
age,
is_active
FROM users
ORDER BY id ASC;SQLこのSQLは、次のように読みます。
SELECTで、取得したい列を指定します。FROM usersで、対象テーブルを指定します。ORDER BY id ASCで、IDの小さい順に並べます。
結果の並び順を保証したい場合は、ORDER BYを明示します。ORDER BYがない結果を、登録順やID順と決めつけてはいけません。(🔗 sqlite.org)
必要な列だけ取得する
名前だけ必要なら、すべての列を取得する必要はありません。
-- 表示に必要なIDと名前だけ取得します
SELECT id, name
FROM users
ORDER BY id;SQLSQLでは、次のように*で全列を指定することもできます。
-- テーブルの全列を取得します
SELECT *
FROM users;SQLただし、この講座では、アプリで使う列を明示する書き方を基本にします。取得する情報が読みやすくなり、後から列が増えたときにも、何を使っているか確認しやすくなるためです。
WHEREで特定のユーザーを取得する
-- IDが2のユーザーを取得します
SELECT id, name, email
FROM users
WHERE id = 2;SQLWHEREは、対象となる行の条件です。
今回はidが主キーなので、IDが2のユーザーは最大1件です。
一方、次の条件では複数件が一致する可能性があります。
-- 有効なユーザーを取得します
SELECT id, name
FROM users
WHERE is_active = 1
ORDER BY id;SQL1件の操作には主キー、一覧の絞り込みには条件という使い分けを意識します。
条件に一致しなければ0件になる
-- 存在しないIDを検索します
SELECT id, name
FROM users
WHERE id = 999;SQL該当するユーザーがいなければ、結果は0件です。
これは通常の検索結果です。「SQLが失敗した」という意味ではありません。取得結果が0件だった場合に、画面でどのように案内するかをアプリ側で決めます。(🔗 sqlite.org)
検索条件を少し広げる
比較演算子を使う
| 書き方 | 意味 | 例 |
|---|---|---|
= | 等しい | id = 1 |
<> | 等しくない | is_active <> 1 |
> | より大きい | age > 20 |
>= | 以上 | age >= 20 |
< | より小さい | age < 30 |
<= | 以下 | age <= 30 |
これらの比較をWHEREの条件として使えます。(🔗 sqlite.org)
-- 25歳以上のユーザーを取得します
SELECT id, name, age
FROM users
WHERE age >= 25
ORDER BY id;SQL初期データなら、30歳の蓮さんが対象です。
ANDとORで条件を組み合わせる
-- 有効、かつ25歳以上のユーザーを取得します
SELECT id, name, age
FROM users
WHERE is_active = 1
AND age >= 25
ORDER BY id;SQL-- IDが1、またはIDが3のユーザーを取得します
SELECT id, name
FROM users
WHERE id = 1 OR id = 3
ORDER BY id;SQLANDは両方を満たす条件、ORはどちらかを満たす条件です。組み合わせる場合は、意図を明確にするため括弧を使います。(🔗 sqlite.org)
-- 有効、かつ「25歳未満または年齢未設定」のユーザーです
SELECT id, name, age
FROM users
WHERE is_active = 1
AND (age < 25 OR age IS NULL)
ORDER BY id;SQLNULLの判定にはIS NULLを使う
-- 年齢が未設定のユーザーを取得します
SELECT id, name
FROM users
WHERE age IS NULL;SQL-- 年齢が設定されているユーザーを取得します
SELECT id, name, age
FROM users
WHERE age IS NOT NULL
ORDER BY id;SQLNULLは、通常の値と同じように= NULLで比較するものではありません。未設定かを調べるときは、IS NULLまたはIS NOT NULLを使います。(🔗 sqlite.org)
件数を制限する
-- IDの大きいユーザーから、最大2件取得します
SELECT id, name
FROM users
ORDER BY id DESC
LIMIT 2;SQLASCは昇順、DESCは降順です。LIMITは、返す行数の上限を指定します。(🔗 sqlite.org)
ここでは「IDの大きい順」であり、厳密な「登録日時の新しい順」と同じ意味ではありません。登録日時で並べたいアプリでは、日時の列を設計します。
INSERTでユーザーを登録する
次に、新しいユーザーを追加します。
登録する列と値を対応させる
-- 新しいユーザーを登録します
INSERT INTO users (
name,
email,
age
)
VALUES (
'結衣',
'yui@example.com',
27
);SQL列名と値は、順番で対応します。
| 指定した列 | 保存する値 |
|---|---|
name | '結衣' |
email | 'yui@example.com' |
age | 27 |
INSERTでは、指定した列に対応する値を渡します。省略した列には、定義された既定値などが使われます。(🔗 sqlite.org)
今回、is_activeはDEFAULT 1なので、省略すると1になります。idも省略しており、SQLiteのINTEGER PRIMARY KEYの仕組みで整数が割り当てられます。(🔗 sqlite.org)
初期状態から1回だけ登録した場合
| id | name | age | is_active | |
|---|---|---|---|---|
| 4 | 結衣 | 🔗 yui@example.com | 27 | 1 |
ただし、アプリでは「次のIDは必ず4だ」と決めつけません。実行例では、登録時に取得したIDを、その後の操作へ引き継ぎます。
重複したメールアドレスは登録できない
今回のemailにはUNIQUEを付けています。
そのため、初期データと同じaoi@example.comを別のユーザーへ保存しようとすると、制約違反になります。データベースの制約は、登録だけでなく更新時にも適用されます。(🔗 sqlite.org)
この失敗は、想定外のクラッシュとして放置するのではなく、アプリ側で「使用済みのメールアドレスです」などと案内する対象になります。
UPDATEでユーザーを更新する
UPDATEでは、既存の行の値を変更します。
指定したユーザーの名前と年齢を変更する
-- まず、変更対象を確認します
SELECT id, name, age
FROM users
WHERE id = 2;
-- IDが2のユーザーだけを変更します
UPDATE users
SET
name = '蓮(更新)',
age = 31
WHERE id = 2;
-- 変更結果を確認します
SELECT id, name, age
FROM users
WHERE id = 2;SQLSETには、新しく設定する列と値を書きます。
今回変更しているのはnameとageだけです。指定していないemailやis_activeは、そのままです。(🔗 sqlite.org)
UPDATEで最も重要なのはWHEREです
UPDATEでWHEREを省略すると、すべての行が更新対象になります。(🔗 sqlite.org)
そのため、今回のユーザー編集では、必ず主キーで対象を指定します。
-- 編集対象のユーザーを主キーで限定します
UPDATE users
SET name = '蓮(更新)'
WHERE id = 2;SQL「名前が蓮のユーザー」を条件にすると、同名の利用者が複数いる場合に複数件へ作用する可能性があります。
人に見せる名前と、操作対象を特定するIDを分けることが、ここでも重要になります。
対象0件は、通常エラーではありません
存在しないIDを指定したUPDATEは、通常、その対象行がないため0件の更新になります。SQLの構文エラーとは別です。(🔗 sqlite.org)
アプリでは、単にSQLの実行が終了しただけで「更新成功」とせず、想定した対象があったかを確認します。
DELETEでユーザーを削除する
DELETEでは、条件に一致する行を削除します。
-- まず削除対象を確認します
SELECT id, name, email
FROM users
WHERE id = 4;
-- IDが4のユーザーだけを削除します
DELETE FROM users
WHERE id = 4;SQLDELETE FROM usersは対象テーブルを指定し、WHEREが対象行を絞ります。
こちらもWHEREを省略すると、テーブルのすべての行が削除対象になります。テーブルの定義を消す操作ではなく、行を削除する操作です。(🔗 sqlite.org)
削除と無効化を区別する
ユーザー管理では、行そのものを消す以外に、利用状態を変更する設計もあります。
-- 行を残したまま、利用状態を無効にします
UPDATE users
SET is_active = 0
WHERE id = 2;SQL今回の設計では、次の違いがあります。
| 操作 | 保存後の状態 | アプリ側で考えること |
|---|---|---|
DELETE | ユーザーの行がなくなります | 関連データをどう扱うか |
UPDATE ... is_active = 0 | 行は残り、無効状態になります | 取得・操作時に状態を確認するか |
無効化すれば個人情報を削除したことになる、という意味ではありません。何を残す必要があり、何を削除する必要があるかは、アプリの要件として別途決めます。
トランザクションで変更を確定・取り消しする
データを書き換えるときは、変更を確定するか、取り消すかも重要です。
| SQL | 意味 |
|---|---|
BEGIN | トランザクションを開始します |
COMMIT | 変更を確定します |
ROLLBACK | 未確定の変更を取り消します |
SQLiteでは、これらで明示的なトランザクションを扱えます。(🔗 sqlite.org)
更新を試して、元へ戻す例
-- 未確定の変更をまとめる範囲を開始します
BEGIN;
-- 学習用に年齢を一時変更します
UPDATE users
SET age = 99
WHERE id = 1;
-- この接続では、一時変更した値を確認できます
SELECT id, name, age
FROM users
WHERE id = 1;
-- 確定せず、変更を取り消します
ROLLBACK;
-- 元の24歳へ戻ったことを確認します
SELECT id, name, age
FROM users
WHERE id = 1;SQLROLLBACKは、確定済みの変更をいつでも戻せる万能な元に戻す機能ではありません。 ここでは、現在のトランザクション内の未確定の変更を取り消しています。(🔗 sqlite.org)
また、「先にSELECTしたから絶対に安全」というわけでもありません。実際の多人数アプリでは、確認後に別の処理がデータを変える可能性も考え、条件やトランザクションを設計します。
実践:一連の操作をPythonから実行する
ここまでのSQLは、各命令を理解するための説明例です。
ここからは、新しい学習用データベースを作り、同じ流れをまとめて実行するプログラムを用意します。先ほどのSQLを手動で実行していなくても、このファイルで実習できます。
実行する内容は、次のとおりです。
- テーブルと初期データを作成します。
- ユーザー一覧を表示します。
- ユーザーを1人登録します。
- IDが2のユーザーを更新します。
- 今回登録したユーザーを削除します。
- 一時更新して、ロールバックします。
- メールアドレスの重複が拒否されることを確認します。
practice.py
# SQLiteとファイル操作の機能を読み込みます
import sqlite3
from pathlib import Path
# 実行場所ではなく、このファイルの位置を基準にします
BASE_DIR = Path(__file__).resolve().parent
DB_PATH = BASE_DIR / "day52.db"
SCHEMA_PATH = BASE_DIR / "schema.sql"
# 既存のDBへ意図せず二重登録しないようにします
if DB_PATH.exists():
raise SystemExit(
"day52.dbは既に存在します。"
"再実行する場合は、内容を確認して別名へ移動してください。"
)
def show_users(connection, heading):
"""ユーザー一覧をID順に表示します。"""
print(f"\n")
rows = connection.execute(
"""
SELECT id, name, email, age, is_active
FROM users
ORDER BY id;
"""
).fetchall()
for row in rows:
print(tuple(row))
# データベースへ接続します
connection = sqlite3.connect(DB_PATH)
try:
# 今回は外部キーを使いませんが、接続時に有効化します
connection.execute("PRAGMA foreign_keys = ON")
# 固定の学習用SQLから初期化します
schema = SCHEMA_PATH.read_text(encoding="utf-8")
connection.executescript(schema)
show_users(connection, "初期データ")
# 正常終了すると確定し、例外があれば取り消す範囲です
with connection:
cursor = connection.execute(
"""
INSERT INTO users (name, email, age)
VALUES (?, ?, ?);
""",
("結衣", "yui@example.com", 27),
)
# 決め打ちせず、実際に登録されたIDを受け取ります
new_user_id = cursor.lastrowid
print(f"\n登録したID:{new_user_id}")
show_users(connection, "登録後")
# IDが2のユーザーを更新します
with connection:
cursor = connection.execute(
"""
UPDATE users
SET name = ?, age = ?
WHERE id = ?;
""",
("蓮(更新)", 31, 2),
)
# この実習では、対象が1行あることを期待します
if cursor.rowcount != 1:
raise RuntimeError("更新対象を1件に特定できませんでした。")
show_users(connection, "更新後")
# 今回登録したユーザーだけを削除します
with connection:
cursor = connection.execute(
"""
DELETE FROM users
WHERE id = ?;
""",
(new_user_id,),
)
if cursor.rowcount != 1:
raise RuntimeError("削除対象を1件に特定できませんでした。")
show_users(connection, "削除後")
# 明示的にトランザクションを開始します
connection.execute("BEGIN")
try:
connection.execute(
"UPDATE users SET age = ? WHERE id = ?;",
(99, 1),
)
age = connection.execute(
"SELECT age FROM users WHERE id = ?;",
(1,),
).fetchone()[0]
print(f"\n取り消し前の年齢:{age}")
finally:
# この学習用の変更は、必ず取り消します
connection.rollback()
age = connection.execute(
"SELECT age FROM users WHERE id = ?;",
(1,),
).fetchone()[0]
print(f"取り消し後の年齢:{age}")
# 重複メールアドレスによる制約違反を確認します
try:
with connection:
connection.execute(
"""
INSERT INTO users (name, email, age)
VALUES (?, ?, ?);
""",
("重複確認", "aoi@example.com", 20),
)
except sqlite3.IntegrityError:
print("\n制約違反により、重複登録が拒否されました。")
show_users(connection, "最終データ")
except Exception:
# 未確定の変更があれば取り消します
connection.rollback()
raise
finally:
# 接続を明示的に閉じます
connection.close()PythonPythonのsqlite3では、?による値のバインド、lastrowidによる登録IDの取得、rowcountによる対象件数の確認などができます。with connectionはトランザクションの確定・取り消しを扱いますが、接続そのものは閉じないため、最後にclose()しています。(🔗 docs.python.org)
実行コマンド
# 2つのファイルを置いたフォルダーで実行します
python practice.py
# 環境によっては、こちらを使います
# python3 practice.py
Pythonのsqlite3が利用できる環境を前提とします。
実行後に確認できること
初期状態から実行すると、結衣さんが登録され、その後、今回取得したIDを使って削除されます。
最終的なデータは、次の状態になります。
| id | name | age | is_active | |
|---|---|---|---|---|
| 1 | 葵 | 🔗 aoi@example.com | 24 | 1 |
| 2 | 蓮(更新) | 🔗 ren@example.com | 31 | 1 |
| 3 | 凛 | 🔗 rin@example.com | NULL | 0 |
途中では、次の表示も確認できます。
登録したID:4
取り消し前の年齢:99
取り消し後の年齢:24
制約違反により、重複登録が拒否されました。
Pythonでは、SQLのNULLが取得結果でNoneとして表示されます。(🔗 docs.python.org)
作成済みのday52.dbがある場合、プログラムは開始時に停止します。再実行する場合は、そのファイルが学習用であることを確認し、別名へ移動してから実行します。
SQLと入力値を分ける理由を深掘りする
今回のプログラムでは、SQLへ値を直接組み込まず、次のように渡しています。
# SQLの構造と、入力値を分けて渡します
connection.execute(
"""
UPDATE users
SET name = ?, age = ?
WHERE id = ?;
""",
("蓮(更新)", 31, 2),
)Python?は値を受け取る位置です。
名前に引用符などが含まれていても、値として渡す方法を使えば、SQLの構造と混同せずに扱えます。Pythonのsqlite3でも、値の受け渡しには文字列の組み立てではなく、プレースホルダーを使います。(🔗 docs.python.org)
プレースホルダーの数と値の数を合わせる
# ?が3つあるため、値も3つ渡します
connection.execute(
"""
INSERT INTO users (name, email, age)
VALUES (?, ?, ?);
""",
("結衣", "yui@example.com", 27),
)Python値が1つの場合は、Pythonのタプルの書き方にも注意します。
# 1要素のタプルなので、末尾のカンマが必要です
connection.execute(
"SELECT id, name FROM users WHERE id = ?;",
(1,),
)Python値を分ければ、すべての検証が不要になるわけではありません
値のバインドは、入力内容を業務上正しいものへ変換する機能ではありません。
たとえば、アプリ側では次の確認が必要です。
- 名前が長すぎないか。
- 年齢が整数として入力されているか。
- メールアドレスの形式が適切か。
- 操作する利用者に更新権限があるか。
また、プレースホルダーは基本的に値を渡すためのものであり、任意のテーブル名や列名を安全に選ぶ仕組みではありません。動的な並び替えなどを追加するときは、許可した列名から選ぶ設計にします。(🔗 sqlite.org)
更新・削除を安全にする確認手順
今回の実習では、次の順番を基本にします。
- 対象テーブルを確認します。
- 主キーなどの条件で対象を限定します。
- 必要に応じて、同じ条件の
SELECTで確認します。 - 値をバインドして、更新または削除します。
- 対象件数と結果を確認します。
- 意図した処理なら確定し、問題があれば取り消します。
ただし、画面で事前確認をしたことと、サーバーで権限確認をしたことは別です。
主キーは「操作してよい証明」ではありません
ユーザーIDが2だと分かっても、そのユーザー情報を誰でも編集してよいわけではありません。
今回の実習はローカルの学習用操作ですが、Next.jsへ組み込むときには、ログイン状態や権限をサーバー側で確認する設計を加えます。
ブラウザーから受け取るIDを信用しすぎない
画面から送られてくるIDは、操作対象を指定する入力です。
それだけで、本人や管理者であることは証明できません。
公開範囲も分けて考える
データベースにはメールアドレスが必要でも、一般公開のユーザー一覧には不要かもしれません。
最後に確認すること
「取得できる情報」と「画面やAPIへ返してよい情報」を同じものとして扱わないことが大切です。今回の全項目表示は、学習用のローカル確認として行っています。
外部キーのあるユーザーを削除するとどうなるか
Day 51では、記事から投稿者を参照する外部キーを学びました。
もしposts.user_idがusers.idを参照し、削除ルールがRESTRICTなら、記事のあるユーザーをそのまま削除できない場合があります。(🔗 sqlite.org)
今回のDay 52では、操作の基本に集中するため、記事テーブルを作っていません。
実際のアプリでは、ユーザー削除時に次の方針を決めます。
- 記事がある間は削除を拒否しますか。
- 記事の所有者を変更しますか。
- ユーザー情報の一部だけを削除・置換しますか。
- 関連データも削除する設計にしますか。
削除できないからといって、外部キー制約を安易に無効化するのではなく、データをどう扱うべきかを先に決めます。
Next.jsの画面操作とSQLを対応させる
今日学んだSQLは、今後、Next.jsのサーバー側処理から呼び出すことになります。
| 画面の操作 | サーバー側で行うこと | SQLの基本形 |
|---|---|---|
| ユーザー一覧を開く | 表示可能なユーザーを取得します | SELECT |
| 登録ボタンを押す | 入力を確認して登録します | INSERT |
| 保存ボタンを押す | 権限と入力を確認して更新します | UPDATE |
| 削除ボタンを押す | 対象と権限を確認して削除します | DELETE |
今回の設計イメージは、次のとおりです。
Next.jsの画面
↓
サーバー側で入力・権限を確認
↓
SQLまたはORMでデータベースを操作
↓
結果に応じて画面を更新
今後ORMを使う場合でも、「どの条件で何件を取得・変更するか」という理解は変わりません。SQLの基本が分かると、ライブラリのコードが何をしているかも読み解きやすくなります。
よくあるつまずきと確認方法
| 症状 | 主な確認箇所 | 修正の方向 |
|---|---|---|
no such table: users | 接続先と初期化 | 正しいDBでテーブルを作ったか確認します |
| 登録時に制約違反になる | UNIQUEやCHECK | 重複や値の範囲を確認します |
| 年齢未設定の人を取得できない | NULLの条件 | IS NULLを使います |
| 更新しても何も変わらない | WHEREのID | 対象が存在するか確認します |
| 複数人が更新された | 条件が広すぎないか | 1人の更新なら主キーで限定します |
| 値の渡し方でエラーになる | ?と引数 | 数と順番をそろえます |
| 再実行できない | DBファイルの存在 | 学習用DBを確認して別名へ移動します |
| 変更が残らない | 確定処理 | ロールバックしていないか確認します |
これらのうち、制約、条件式、SQL実行時の引数やトランザクションの動作は、SQLiteとsqlite3の仕様に従います。(🔗 sqlite.org)
自力で取り組む練習問題
練習は、最終データが残った学習用データベース、または別に作った練習用データベースで行います。初期状態と最終状態では蓮さんの名前や年齢が違うため、どの状態から始めるかを確認します。
有効なユーザーだけを取得する
次の条件でSQLを書きます。
- 取得する列は
idとnameです。 is_activeが1のユーザーだけを対象にします。- IDの小さい順に並べます。
使うものは、SELECT、FROM、WHERE、ORDER BYです。
年齢が未設定のユーザーを取得する
凛さんを、名前ではなく「年齢が未設定」という条件で探します。
NULLの判定方法を、もう一度確認します。
新しいユーザーを登録する
任意のダミーデータで、ユーザーを1人追加します。
- IDは省略します。
- メールアドレスは既存データと重複させません。
is_activeを省略し、既定値を確認します。- 実際に登録されたIDを取得します。
登録したユーザーの名前だけを更新する
直前に取得したIDを使い、名前だけを変更します。
ほかの列が変わっていないことも確認します。
削除せずに無効化する
同じユーザーのis_activeを0にします。
その後、「全ユーザーの一覧」と「有効なユーザーだけの一覧」で表示がどう違うかを確かめます。
変更を取り消す
トランザクションを開始し、一時的に名前を変更してからロールバックします。
変更前の名前へ戻ったことを確認し、確定と取り消しの違いを説明します。
初心者向けのSQLテンプレート
次のテンプレートは、今回のusersテーブルに対する具体例です。まずは列名と条件を読み替えられるようにします。
取得のテンプレート
-- 必要な列を、必要な条件で取得します
SELECT id, name, email
FROM users
WHERE is_active = 1
ORDER BY id ASC;SQL追加のテンプレート
-- 列名と値の順番を対応させます
INSERT INTO users (name, email, age)
VALUES ('空', 'sora@example.com', 28);SQL更新のテンプレート
-- 対象を主キーで限定します
UPDATE users
SET name = '葵(変更後)'
WHERE id = 1;SQL削除のテンプレート
-- 実行前に、対象のIDと内容を確認します
DELETE FROM users
WHERE id = 3;SQL固定値のSQLは構文を理解するための例です。フォームなどの入力値を受け取る実装では、実習プログラムのように値をバインドします。
Day 52のチェックリスト
- CRUDと4つのSQLの対応を説明できます。
SELECTで必要な列だけを取得できます。WHEREで行を絞り込めます。ORDER BYとLIMITを使えます。NULLをIS NULLで判定できます。INSERTで列と値を対応させられます。UPDATEとDELETEで対象を限定できます。- 対象0件とSQLエラーを区別できます。
- プレースホルダーで入力値を渡せます。
COMMITとROLLBACKを区別できます。- 主キーの指定とは別に、操作権限の確認が必要だと理解しています。
まとめ
Day 52では、ユーザー管理データベースを題材に、SELECT、INSERT、UPDATE、DELETEを学びました。
特に重要なのは、SELECTで必要な情報を選び、WHEREで操作対象を限定し、入力値をSQLの構造と分けて渡すことです。さらに、変更を確定する前に問題を確認できるよう、トランザクションの基本も扱いました。
Next.jsでCRUD画面を作るときも、土台になるのは今回の考え方です。まずは「この操作は、どの行へ、何をするのか」を説明できるようにし、そのうえで画面とデータベースをつないでいきます。
