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つのレコードです。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'; カラム名には引用符なし。
条件を 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つ反転
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;
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 で実行します。
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;
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は各外部キーを主キーにマッチさせ、一致した行を組み合わせます
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;
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 。
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はテーブルに新しい行を追加します
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 を追加してください。
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;
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;
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;