コンテンツへスキップ
科目
  • 1 クエリの基礎
    1.1

    SELECTおよびFROM

    データベースはデータをテーブルに格納します。クエリは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つのレコード、各カラムは1つのフィールド
    テーブルはレコードを格納:各行は1つのレコード、各カラムは1つのフィールド
    • 各行は1つのレコードです。SQLキーワードは慣例として大文字で書きます。
    • ステートメントは半角ピリオド ; で終わります。
    1.2

    カラムの選択

    希望するカラムをコンマで区切ってリストアップします。結果内でカラム名を変更するには 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 は結果から重複値を削除します。
    1.3

    WHEREによるフィルタリング

    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;
    
    • これらの部分を論理的な順序で読み取ってください: FROM → WHERE → SELECT 。(データベースは異なる方法で処理を行うかもしれませんが、結果は同じです。)

    ** 一般的なミス **

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

    AND, OR, NOT

    条件を 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;
    
    ANDは全て真が必要;ORはいずれか真;NOTは1つ反転
    ANDは全て真が必要;ORはいずれか真;NOTは1つ反転
    2.2

    LIKE, IN および BETWEEN

    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
    
    パターン 一致する内容
    'M%' Mで始まる
    '%a' aで終わる
    '%an%' "an" を含む
    'M_i' M、次にちょうど1文字、そしてi

    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;
    
    2.3

    NULL

    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 は両端を含める。
  • 3 ソートと制限
    3.1

    ORDER BY および LIMIT

    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 は返される行の数を制限しますが、ソートした後に行われます。
    ORDER BYはソート;LIMITはソート後の最初のnを保持
    ORDER BYはソート;LIMITはソート後の最初のnを保持
  • 4 集計とグループ化
    4.1

    集約関数

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

    関数 提供される値
    COUNT(*) 行数
    SUM(col) 合計
    AVG(col) 平均値
    MIN(col) / MAX(col) 最小値 / 最大値
    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;
    
    4.2

    GROUP BY および HAVING

    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;
    
    GROUP BYは値ごとに1つのバケットに行を集約;各バケットが1つの要約行になる
    GROUP BYは、各行を値ごとに1つのバケツにまとめます。各バケツが1つの集計行になります

    SQL句を必要な構文順に記述せよ。以下の表はクエリに関する推論のための論理モデルであり、エンジンが物理的な実行順序を最適化することがある:

    ステップ 節
    1 FROM (および JOIN)
    2 WHERE — 行をフィルタリング
    3 GROUP BY — グループ形成
    4 HAVING — グループをフィルタリング
    5 SELECT — 出力列の計算
    6 ORDER BY、次に LIMIT

    ** 一般的なミス **

    • 予測可能なグループ結果を得るには、選択された非集計列をそれぞれグループ化するか、データベースによってサポートされる既知の機能的依存性を使用する。SQLiteではGROUP BYの外側に裸の列を許可するが、それはグループ内の任意の行から来ることがあり、成功したクエリでも意図した値を保証するものではない。
    • WHERE でグループ化前に行をフィルタリングし、 HAVING でグループ化後にグループをフィルタリングします。
    • COUNT(*) は行数をカウントします; COUNT(col) はその列のNULLをスキップします。
  • 5 ジョイン
    5.1

    キーとリレーションシップ

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

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

    INNER JOIN

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

    CREATE TABLE class (id INTEGER PRIMARY KEY, name TEXT);
    CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT, class_id INTEGER REFERENCES class(id));
    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;
    
    INNER JOINは各外部キーを主キーにマッチさせ、一致した行を組み合わせます
    INNER JOINは各外部キーを主キーにマッチさせ、一致した行を組み合わせます
    5.3

    グループ化とのJOIN

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

    CREATE TABLE class (id INTEGER PRIMARY KEY, name TEXT);
    CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT, class_id INTEGER REFERENCES class(id));
    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.id, c.name;
    

    キー c.id でグループ化し、名前も併せて指定してください: 偶然同じ名前を持つ2つのクラスが、 otherwise 1つの行に統合されてしまいます。

    5.4

    LEFT JOIN

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

    CREATE TABLE class (id INTEGER PRIMARY KEY, name TEXT);
    CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT, class_id INTEGER REFERENCES class(id));
    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を使用します。
  • 6 データの修正
    6.1

    INSERT

    INSERT INTO ... VALUES ... は新しい行を追加します。列名を指定し、同じ順序で値を与えます。(以下の各ブロックは結果が見えるよう SELECT で終わっています。)

    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 (...), (...); は1つのステートメントで複数の行を追加できます。
    INSERTはテーブルに新しい行を追加します
    INSERTはテーブルに新しい行を追加します
    6.2

    UPDATE

    UPDATE ... SET ... WHERE ... は既存の行を変更します。必ず WHERE を追加してください。否则、すべての行が変更されてしまいます。

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

    DELETE

    DELETE FROM ... WHERE ... は行を削除します。 WHERE がなければ、テーブル全体が空になります。

    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;
    

    ** 一般的なミス **

    • UPDATE と DELETE に WHERE がなければ、すべての行が変更されるため、必ず WHERE を追加してください。
    • INSERT では、値が列リストに対して順序と型で対応している必要があります。
    • 危険な DELETE を最初にテストし、同じ WHERE を持つ条件として SELECT とする。
  • 7 テーブルの定義
    7.1

    CREATE TABLE & データ型

    CREATE TABLE はテーブルのスキャーマを定義します: カラム名とそのデータ型です。SQLiteの主要なデータ型は INTEGER, TEXT, および REAL (浮動小数点数: 高速ですが常に正確ではないため、0.1 + 0.2 = 0.3 は誤りです)。

    CREATE TABLE student (id INTEGER, name TEXT, score REAL);
    INSERT INTO student VALUES (1, 'Mei', 88.5);
    SELECT * FROM student;
    
    CREATE TABLEは列とその型を指定します
    CREATE TABLEは列とその型を指定します
    7.2

    キーと制約

    制約とは列に対するルールです: 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で外部キーを宣言する。SQLiteでは、トランザクション開始前に各接続に対してPRAGMA foreign_keys = ON;を有効化して強制执行する。宣言だけでは強制执行を保証しない。

    PRAGMA foreign_keys = ON;
    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) を書きます。
    7.3

    ALTER TABLE

    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; はテーブルを完全に削除します——構造とデータです。

    ** 一般的なミス **

    • 各カラムに適した型を宣言する。SQLiteでは型名の省略を許容し、通常は柔軟なタイピングを使用する。型宣言だけでは格納されたすべての値を検証しない。
    • プライマリキーは一意でありNOT NULLであるように設計してください。通常のSQLiteテーブルでは、一部のプライマリキー宣言でもNULLが許可される場合があります; テキストキーには NOT NULL を明示的に使用してください。 INTEGER PRIMARY KEY は特殊な行IDの挙動を持ちます: NULLを挿入すると整数のidが割り当てられます。
    • 外部キー強制执行が有効な場合、非NULLの外部キー値は参照されたキーと一致しなければならない。カラムがNOT NULLを持つ場合を除き、NULLは許容される。
  • 8 データベース設計
    8.1

    リレーションシップとER図

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

    リレーションシップ 例
    one-to-one(1対1) 人とそのパスポート
    one-to-many(1対多) クラスとその学生
    many-to-many(多対多) 学生とクラブ
    「many」側が外部キーを持ち、many-to-manyリンクにはジョイントテーブルが必要です
    「many」側が外部キーを持ち、many-to-manyリンクにはジョイントテーブルが必要です

    ジョイントテーブルはリンクごとに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;
    
    8.2

    正規化

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

    • 1NF: 各セルには1つの原子値が含まれる——セル内にリストは含めない。
    • 2NF: どの列も複合キーの一部のみに依存してはいけない。
    • 3NF: どの列も非キー列に依存してはいけない。
    未正規化(悪い) 正規化済み(良い)
    student(name, club1, club2) student(name) + membership(student, club)

    ** 一般的なミス **

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

ログインまたはアカウント作成

IGCSE, A-Level & AP