Kryιk

はじめてLESSON 01

この教材で学ぶこと table、key、CRUD、JOIN、transactionを最初のschemaで学びます。

データベースとSQL入門

対象読者: dataを永続化したいが、tableやSQLを初めて学ぶ人。

この教材でできるようになること

  • database、DBMS、table、row、columnを説明できる
  • entityとrelationshipから小さなschemaを作れる
  • SELECT、INSERT、UPDATE、DELETEの基本を読める
  • primary key、foreign key、transactionの役割を説明できる

1. databaseとDBMS

databaseは整理されたdataの集合、DBMSは保存、検索、更新、権限、同時実行、復旧などを管理するsoftwareです。

relational databaseではdataをtableとして表し、table間の関係をkeyで表します。

2. 最初のschema

CREATE TABLE courses (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE lessons (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  course_id bigint NOT NULL REFERENCES courses(id),
  title text NOT NULL,
  position integer NOT NULL CHECK (position > 0),
  UNIQUE (course_id, position)
);
  • primary key: rowを一意に識別する
  • foreign key: 別tableに存在するrowとの関係を守る
  • NOT NULL: 値を必須にする
  • CHECK: domain ruleをDBでも守る
  • UNIQUE: 重複を防ぐ

3. CRUD

INSERT INTO courses (title) VALUES ('プログラミング');

SELECT id, title
FROM courses
ORDER BY created_at DESC;

UPDATE courses
SET title = 'プログラミング基礎'
WHERE id = 1;

DELETE FROM courses
WHERE id = 1;

UPDATEやDELETEでは、先に同じWHEREを使ったSELECTで対象を確認します。条件なしの更新・削除を避けます。

4. JOINで関係を読む

SELECT
  courses.title AS course_title,
  lessons.position,
  lessons.title AS lesson_title
FROM courses
JOIN lessons ON lessons.course_id = courses.id
ORDER BY courses.id, lessons.position;

どのkeyとどのkeyを結ぶかを明示します。同名columnにはtable名またはaliasを付けます。

5. parameter binding

外部入力を文字列連結でSQLへ入れません。

cursor.execute(
    "SELECT id, title FROM courses WHERE title = ?",
    (user_input,),
)

placeholder記法はdriverにより異なります。利用中のlibraryのparameter APIを使います。

6. transaction

複数操作を一つの単位として成功または失敗させます。

BEGIN;

INSERT INTO courses (title) VALUES ('Database');
INSERT INTO lessons (course_id, title, position)
VALUES (currval('courses_id_seq'), 'SQL入門', 1);

COMMIT;

途中で失敗した場合はROLLBACKします。具体的なID取得方法はDBMSにより異なるため、上記構文を別DBへそのまま移植できるとは限りません。

7. backupはrestoreできて初めて有効

backup jobの成功だけでなく、別環境へrestoreし、件数や整合性を確認します。schema migration、application version、restore手順を一緒に管理します。

演習: 学習進捗schema

user、course、lesson、completionを表すschemaを設計してください。

  • 各tableのprimary key
  • relationshipとforeign key
  • 必須column
  • 重複完了を防ぐconstraint
  • user別の完了lesson一覧を返すJOIN
  • course削除時の扱い

理解チェック

  • databaseとDBMSを区別できる
  • entityとrelationshipからtableを作れる
  • 更新前に対象rowを確認できる
  • parameter bindingとtransactionの目的を説明できる

公式資料

次に読む

SQL実践でJOIN、集約、subqueryへ進みます。