SQL Handout · ⁨SQL 講義資料⁩

データベースはデータをテーブルに格納します。クエリはSELECTを使用してデータを読み取ります。SELECT *はすべてのカラムを返します；FROMはテーブル名を指定します。

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
SELECT * FROM student;

• 各行は1つのレコードです。SQLキーワードは慣例として大文字で書きます。
• ステートメントは半角ピリオド ; で終わります。

希望するカラムをコンマで区切ってリストアップします。結果内でカラム名を変更するには AS を使用します（エイリアス）。

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
SELECT name, score AS mark FROM student;

選択されたカラムは計算式也可以是； 新しいカラム名を付けるために AS とペアにします：

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
SELECT name, score + 5 AS bonus FROM student;

• SELECT DISTINCT col は結果から重複値を削除します。

WHERE は条件に一致する行のみを保持します。=, <> (不等), <, >, <=, >= と比較してください。テキストはシングルクォートで囲みます。

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71), (3, 'Ana', 95);
SELECT name, score FROM student WHERE score >= 80;

• SQLはこの順序で部分を実行します：FROM → WHERE → SELECT。

** 一般的なミス **

• テキスト値はシングルクォートで囲みます：WHERE name = 'Ann'； カラム名には引用符なし。
• SELECT * はすべてのカラムを返すので——必要なカラムのみ名前を指定してください。
• WHERE は行をフィルタリングし、FROM の後に配置される。

条件を AND, OR, NOT で組み合わせます。AND はすべての条件が真である必要があります； OR はいずれかの条件が真であれば十分です； NOT は1つの条件を反転させます。優先順位を設定するために括弧を使用してください。

CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT name FROM student WHERE form = '11A' AND score >= 90;

LIKE はテキストパターンに一致します：% は任意のテキストを表し、_ は1文字を表します——これらはワイルドカードです。

CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT name FROM student WHERE name LIKE 'M%';        -- starts with M

IN は値のセットに一致し； BETWEEN は範囲に一致します（両端を含みます）。

CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT name, score FROM student
WHERE form IN ('11A', '11C') AND score BETWEEN 80 AND 100;

NULL は欠落している値を示します——そのセルには何も格納されていません。NULL は 0 でもなく、空白文字列でもありません。= や <> との比較は絶対に一致せず、IS NULL または IS NOT NULL でテストする必要があります。

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', NULL), (3, 'Ana', 95);
SELECT name FROM student WHERE score IS NULL;

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', NULL), (3, 'Ana', 95);
SELECT name, score FROM student WHERE score IS NOT NULL;

• WHERE score = NULL は結果に一行も返しません——サムさんの行さえも返しません。
• 演算におけるNULLはNULLになります：score + 5 はサムさんに対してNULLのままです。

** 一般的なミス **

• 空の値のテストには IS NULL を使用し、= NULL を絶対に使用しないでください。
• LIKE において、% は任意のテキストに一致し、_ は1文字に一致します：'A%' は「Aで始まる」を意味します。
• IN (1, 2, 3) は多くの OR より短い；BETWEEN a AND b は両端を含める。

ORDER BY は結果行をソートします。降順（高い順から低い順）にするには DESC を追加します；デフォルトは昇順（低い順から高い順）です。

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei',88),(2,'Sam',71),(3,'Ana',95);
SELECT name, score FROM student ORDER BY score DESC;

複数のカラムでソート

複数のカラムをリストアップします。最初のカラムでの同点の場合、次のカラムで破ります。

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei',88),(2,'Sam',88),(3,'Ana',95);
SELECT name, score FROM student ORDER BY score DESC, name ASC;

LIMIT (トップN)

LIMIT n は最初の n 行のみを保持します——トップNリストにするには ORDER BY とペアにします。

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei',88),(2,'Sam',71),(3,'Ana',95);
SELECT name, score FROM student ORDER BY score DESC LIMIT 2;

• ORDER BY はエイリアスまたは計算式に基づいてソートすることもできます：ORDER BY avg_score DESC。
• テキストはアルファベット順にソートされます：ORDER BY name は A → Z で実行します。

** 一般的なミス **

• ORDER BY はデフォルトで昇順にソートします；降順にするには DESC を追加します。
• ORDER BY は末尾近くに来るが、WHERE と GROUP BY の後である。
• LIMIT は返される行の数を制限しますが、ソートした後に行われます。

集約関数は多数の行を1つの要約値に変換します。平均値を ROUND(x, 2) で包むと整頓されます。

CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT COUNT(*), ROUND(AVG(score), 2), MAX(score) FROM student;

GROUP BY はグループごとに1つの要約行を作成します。HAVING はこれらのグループをフィルタリングします——WHERE に似ていますが、グループ化後に実行されます。

CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT form, COUNT(*) AS n, ROUND(AVG(score), 2) AS avg_score
FROM student
GROUP BY form
HAVING COUNT(*) > 1;

SQLクエリの節をどのように記述しても、SQLは常に同じ固定された順序で実行されます：

** 一般的なミス **

• 集計関数と同じ行には、 GROUP BY に含まれていない plain（単純な）列を選択することはできません。
• WHERE でグループ化前に行をフィルタリングし、 HAVING でグループ化後にグループをフィルタリングします。
• COUNT(*) は行数をカウントします； COUNT(col) はその列のNULLをスキップします。

主キーはテーブル内の各行を一意に識別します。あるテーブルの外部キーは別のテーブルの主キーを指し示し、これにより両者の間にリレーションシップが構築されます。

CREATE TABLE class (id INTEGER, name TEXT);
CREATE TABLE student (id INTEGER, name TEXT, class_id INTEGER);
INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', 2);
SELECT * FROM student;

JOINは2つのテーブルからの行を組み合わせます。 INNER JOIN ... ON ... はキーが一致する行のみを保持します。短いエイリアス（ s, c ）を使用すると、クエリが読みやすくなります。

CREATE TABLE class (id INTEGER, name TEXT);
CREATE TABLE student (id INTEGER, name TEXT, class_id INTEGER);
INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', 2);
SELECT s.name, c.name AS class
FROM student s INNER JOIN class c ON s.class_id = c.id;

まずJOINを行い、その後 GROUP BY を使用して結合された行全体を集計します。

CREATE TABLE class (id INTEGER, name TEXT);
CREATE TABLE student (id INTEGER, name TEXT, class_id INTEGER);
INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', 2), (3, 'Ana', 1);
SELECT c.name AS class, COUNT(*) AS n
FROM student s INNER JOIN class c ON s.class_id = c.id
GROUP BY c.name;

INNER JOINは一致する行のみを保持します。 LEFT JOIN は左側（最初の）テーブルのすべての行を保持し、一致しない場合は右側のテーブルの列は NULL として返されます。

CREATE TABLE class (id INTEGER, name TEXT);
CREATE TABLE student (id INTEGER, name TEXT, class_id INTEGER);
INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', NULL);
SELECT s.name, c.name AS class
FROM student s LEFT JOIN class c ON s.class_id = c.id;

• Samはクラスを持っていませんが、行は依然として表示され、 class の値は NULL となります。
• 一致しない行のみを見つけるには、 WHERE c.id IS NULL を追加します。

** 一般的なミス **

• ON の条件がないJOINでは、すべての行がすべての行とペアになります（クロスジョイン）。
• 外部キーを主キーにマッチさせる: ON orders.customer_id = customers.id 。
• 他方側に一致する行がない行はINNER JOINで除外されます。それらを残すにはLEFT JOINを使用します。

INSERT INTO ... VALUES ... adds new rows 行. Name the columns, then give the values in the same order. (Each block below ends with a SELECT so you can see the result.)

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student (id, name, score) VALUES (1, 'Mei', 88);
INSERT INTO student VALUES (2, 'Sam', 71);
SELECT * FROM student;

• INSERT INTO t VALUES (...), (...); adds several rows in one statement.

DELETE FROM ... WHERE ... removes rows. Without WHERE it empties the whole table 表.

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
DELETE FROM student WHERE score < 80;
SELECT * FROM student;

Common mistakes

• UPDATE and DELETE without a WHERE change every row — always add the WHERE.
• In INSERT, the values must line up with the column list in order and type.
• Test a risky DELETE first as a SELECT with the same WHERE.

CREATE TABLE はテーブルのスキーマ（列名とそのデータ型）を定義します。主要なSQLiteのデータ型は INTEGER 、 TEXT 、および REAL （小数点付き数字）です。

CREATE TABLE student (id INTEGER, name TEXT, score REAL);
INSERT INTO student VALUES (1, 'Mei', 88.5);
SELECT * FROM student;

制約とは列に対するルールです： PRIMARY KEY （一意ID）、 NOT NULL （値必須）、 UNIQUE 、 DEFAULT （デフォルト値）。

CREATE TABLE student (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    score INTEGER DEFAULT 0
);
INSERT INTO student (id, name) VALUES (1, 'Mei');
SELECT * FROM student;

外部キーを REFERENCES で宣言します。これはその列が他のテーブルの主キーを指していることを記録します：

CREATE TABLE class (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE student (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    class_id INTEGER REFERENCES class(id)
);
INSERT INTO class VALUES (1, 'Maths');
INSERT INTO student VALUES (1, 'Mei', 1);
SELECT s.name, c.name AS class
FROM student s INNER JOIN class c ON s.class_id = c.id;

• 長い形式は単独の行に FOREIGN KEY (class_id) REFERENCES class(id) を書きます。

ALTER TABLE ... ADD COLUMN ... は既に存在するテーブルのスキーマを変更します。 DEFAULT は古い行の新しい列を埋めます。

CREATE TABLE student (id INTEGER, name TEXT);
INSERT INTO student VALUES (1, 'Mei');
ALTER TABLE student ADD COLUMN score INTEGER DEFAULT 0;
SELECT * FROM student;

• ALTER TABLE student RENAME TO pupil; はテーブル全体の名前を変更します。
• DROP TABLE student; はテーブルを完全に削除します——構造とデータです。

** 一般的なミス **

• 各カラムにはデータ型が必要です（例：INTEGER, TEXT）。
• 主キーは一意であり、NULLであることはできません。
• 外部キーの値は、それが指すテーブル内に存在する必要があります。

テーブルはリレーションシップを通じて接続されます。One-to-many（1対多）が最も一般的です：1つのクラスに多くの学生がいる場合です。Many-to-many（多対多）には中間にジョイントテーブルが必要です。エンティティ・リレーションシップ図（ER図）は、各エンティティをボックスで、各リレーションシップを線で描きます。

ジョイントテーブルはリンクごとに1行を持ちます。ここではMeiは2つのクラブに参加しており、Chessには2人のメンバーがいます：

CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE club (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE membership (student_id INTEGER, club_id INTEGER);
INSERT INTO student VALUES (1, 'Mei'), (2, 'Sam');
INSERT INTO club VALUES (1, 'Chess'), (2, 'Art');
INSERT INTO membership VALUES (1, 1), (1, 2), (2, 1);
SELECT s.name, c.name AS club
FROM membership m
INNER JOIN student s ON m.student_id = s.id
INNER JOIN club c ON m.club_id = c.id;

正規化（Normalisation）は、重複（同じデータの繰り返し）とそれに伴う更新ミスを避けるためにテーブルを整理するもの。最初の3つの正規形：

• 1NF: 各セルには1つの原子値が含まれる——セル内にリストは含めない。
• 2NF: どの列も複合キーの一部のみに依存してはいけない。
• 3NF: どの列も非キー列に依存してはいけない。

** 一般的なミス **

• 繰り返しのグループを多数の類似した列にするのではなく、独自のテーブルに分割する（正規化）。
• 各テーブルはONE（1つ） kind of thing（種類のもの）だけを説明すべきです。
• 他方のテーブルの主キーを指す外部キーを使ってテーブルを連結します。