大学のデータベースのテストのために範囲をまとめてみた。

テーブルの表示

SELECT文

sql

SELECT [ALL | DISTINCT] 列名 FROM テーブル
    [WHERE 条件]
    [GROUP BY 列名]
    [HAVING グループ抽出条件]
    [ORDER BY 列名 [ASC | DESC], ...]

DISTINCTは重複を除く。
GROUP BYはSELECTで指定した列に含まれてる必要あり、重複を除く。
COUNTを使う場合、DISTINCTは重複してないものを数える。
GROUP BYは重複してる数を数える。

具体的なDISTINCTとGROUP BYの違いは以下参照。

HAVINGはGROUP BY の条件。
ORDER BYはASCは昇順、DESCは降順。

表の集合演算

UNION

重複削除して、一つにまとめる

例

SELECT 生徒番号 FROM 生徒データ WHERE 出身中学 = '稲沢第一中学'
UNION
SELECT 生徒番号 FROM 生徒成績データ WHERE 外国語 >= 90;

INTERSECT

2個のテーブルの共通部分を抽出する。
列名が同じ時限定。

例

SELECT 生徒番号 FROM 生徒成績データ WHERE 数学 >= 90 
INTERSECT 
SELECT 生徒番号 FROM 生徒データ WHERE 性別 = '女';

EXCEPT

2個のテーブルの差分を抽出

例

SELECT 生徒番号 FROM 生徒データ 
EXCEPT 
SELECT 生徒番号 FROM クラブデータ;

表の結合

CROSS JOIN

全ての組み合わせを抽出

例

SELECT * 
FROM employee, department;
--または
SELECT * 
FROM table1 CROSS JOIN table2;

INNER JOIN

2個のテーブルの共通部分を抽出
INTERSECTとの違いは、列名が違っても良い。

例

SELECT *
FROM department JOIN employee ON department.id = employee.department_id

--例2
SELECT department.name, COUNT(employee.id) 
FROM department JOIN employee ON department.id = employee.department_id
GROUP BY department.id;

OUTER JOIN

INNER JOINプラスアルファ。一致しなかった要素もNULLとして表示する。

例

SELECT *
FROM department LEFT JOIN employee ON department.id = employee.department_id;
--例2
SELECT department.name, COUNT(employee.id) 
FROM department LEFT JOIN employee ON department.id = employee.department_id
GROUP BY department.id;

副問い合わせ

抽出結果からの抽出

例

SELECT *
FROM table1
    JOIN
        (
            SELECT *
            FROM table2
        ) AS table3
        ON table1.col1 = table3.col2
--以下と同義
FROM table1 JOIN table2 ON table1.col1 = table2.col2

抽出条件における副問い合わせ

WHEREやHABINGでの条件をSELECTの結果に反映できる。
下の例だと、table1.col1とtable2.col2が一致してる行のみ、table1を表示する。

例

SELECT *
FROM table1
WHERE table1.col1 IN (SELECT col2 FROM table2);

テーブルの操作

CREATE TABLE文

基本的な構文は以下の通り。

sql

CREATE TABLE テーブル名 (
    列名1 データ型 [列制約],
    列名2 データ型 [列制約],
    …
    [表制約]
);

データ型

SQLite3の場合、以下のデータ型がある。

INTEGER	整数
TEXT	文字列
REAL	実数
BLOB	バイナリ列
BOOLEAN	真偽値
DATE	日付
DATETIME	時刻

詳細は以下参照

https:--www.sqlite.org/datatype3.html

列制約

PRIMARY KEY
UNIQUE
NOT NULL
REFERENCES
CHECK

表制約

PRIMARY KEY
FOREIGN KEY
CHECK

実行例

sql

CREATE TABLE 生徒データ (
    生徒番号 INTEGER PRIMARY KEY,
    クラス INTEGER NOT NULL,
    番号 INTEGER NOT NULL,
    名前 TEXT NOT NULL,
    性別 TEXT CHECK(性別 IN ('男', '女')),
    住所 TEXT,
    出身中学 TEXT
);
CREATE TABLE 選択科目データ (
    生徒番号 INTEGER PRIMARY KEY REFERENCES 生徒データ(生徒番号),
    芸術選択 TEXT,
    文理選択 TEXT,
    社会選択 TEXT,
    理科選択 TEXT
);
CREATE TABLE クラブデータ (
    生徒番号 INTEGER PRIMARY KEY REFERENCES 生徒データ(生徒番号),
    クラブ TEXT
);
CREATE TABLE 生徒成績データ (
    生徒番号 INTEGER PRIMARY KEY REFERENCES 生徒データ(生徒番号),
    外国語 INTEGER CHECK(外国語 BETWEEN 0 AND 100),
    数学 INTEGER CHECK(数学 BETWEEN 0 AND 100),
    国語 INTEGER CHECK(国語 BETWEEN 0 AND 100),
    理科 INTEGER CHECK(理科 BETWEEN 0 AND 100),
    社会 INTEGER CHECK(社会 BETWEEN 0 AND 100)
);

--表制約を使うとこうなる
CREATE TABLE 生徒データ (
    生徒番号 INTEGER,
    クラス INTEGER NOT NULL,
    番号 INTEGER NOT NULL,
    名前 TEXT NOT NULL,
    性別 TEXT,
    住所 TEXT,
    出身中学 TEXT,
    PRIMARY KEY (生徒番号),
    CHECK(性別 IN ('男', '女'))
);
CREATE TABLE 選択科目データ (
    生徒番号 INTEGER,
    芸術選択 TEXT,
    文理選択 TEXT,
    社会選択 TEXT,
    理科選択 TEXT,
    PRIMARY KEY (生徒番号),
    FOREIGN KEY (生徒番号) REFERENCES 生徒データ(生徒番号)
);
CREATE TABLE クラブデータ (
    生徒番号 INTEGER,
    クラブ TEXT,
    PRIMARY KEY (生徒番号),
    FOREIGN KEY (生徒番号) REFERENCES 生徒データ(生徒番号)
);
CREATE TABLE 生徒成績データ (
    生徒番号 INTEGER,
    外国語 INTEGER,
    数学 INTEGER,
    国語 INTEGER,
    理科 INTEGER,
    社会 INTEGER,
    PRIMARY KEY (生徒番号),
    FOREIGN KEY (生徒番号) REFERENCES 生徒データ(生徒番号),
    CHECK(外国語 BETWEEN 0 AND 100),
    CHECK(数学 BETWEEN 0 AND 100),
    CHECK(国語 BETWEEN 0 AND 100),
    CHECK(理科 BETWEEN 0 AND 100),
    CHECK(社会 BETWEEN 0 AND 100)
);

DROP TABLE文

表を削除する。

例

DROP TABLE 表名;

--もし存在しない場合、エラーがでるので以下を使う
DROP TABLE IF EXISTS 表名;

INSERT文

テーブルへレコード(行)を挿入する。

sql

INSERT INTO 表名 [(列名1, 列名2, …)] VALUES (値1, 値2, 値3);

例

INSERT INTO 生徒データ (生徒番号, クラス, 番号, 名前, 性別, 住所, 出身中学)
VALUES
    (12061, 1, 1, '六角竜也', '男', '伊倉町', '伊倉第三中学'),
    (12031, 1, 2, '立花翔太', '男', '伊倉町', '伊倉第八中学'),
    (12038, 1, 3, '毛利陽菜', '女', '伊倉町', '伊倉第七中学');
INSERT INTO クラブデータ (生徒番号, クラブ)
VALUES
    (12061, '体操部'),
    (12031, 'サッカー部'),
    (12038, '剣道部');

UPDATE文

レコードの編集をする。

sql

UPDATE 表名 SET 列名1=値1, 列名2=値2, …
[WHERE 条件];

例

UPDATE クラブデータ SET クラブ='空手部' WHERE 生徒番号 = 12038;

DELETE文

レコードの削除をする。

sql

DELETE FROM 表名 [WHERE 条件];

例

DELETE FROM クラブデータ WHERE 生徒番号 = 12061;

ALTER TABLE文

テーブルの定義内容の変更をするときに使う。

テーブル名の変更

sql

ALTER TABLE 変更前テーブル名 RENAME TO 変更後テーブル名;

列の追加

sql

ALTER TABLE テーブル名 ADD COLUMN 列名 データ型;

列の削除

sql

ALTER TABLE テーブル名 DROP COLUMN 列名;

列定義の変更

sql

ALTER TABLE テーブル名 ALTER COLUMN 列名 データ型 [列制約];

表制約の追加

sql

ALTER TABLE テーブル名 ADD 表制約;

View

CREATE VIEW

仮想的なテーブルを作る。

SQLite3の場合、更新はできない。

sql

CREATE VIEW ビュー名 AS SELECT文;

例

CREATE VIEW 所属クラブ AS
SELECT
  生徒データ.生徒番号,
  生徒データ.名前,
  クラブデータ.クラブ
FROM
  生徒データ LEFT JOIN クラブデータ ON 生徒データ.生徒番号 = クラブデータ.生徒番号
ORDER BY
  生徒データ.生徒番号;

LIMITをつけると、仮想テーブルの行数を制限できる。

sql

SELECT * FROM 所属クラブ LIMIT 5;

実テーブルを更新した場合は、それを参照するビューにも反映される。

仮想テーブルに対して直接更新行えない。

DROP VIEW

仮想テーブルの削除を行う。

sql

DROP VIEW ビュー名;

インデックス関連のSQL

インデックスとは、検索を高速化するための仕組み。
木構造にすることで、アクセスする時間を減らすことができる。

sql

--インデックス作成
CREATE INDEX インデックス名 ON テーブル名 (列名);

--複合インデックス
CREATE INDEX インデックス名 ON テーブル名 (列名1, 列名2, 列名3);

--部分インデックス
CREATE INDEX インデックス名 ON テーブル名 (列名) WHERE 抽出条件;

--インデックス削除
DROP INDEX インデックス名;

例

CREATE INDEX index_外国語 ON 生徒成績データ (外国語);

CREATE INDEX index_合計点 ON 生徒成績データ (外国語 + 数学 + 国語 + 理科 + 社会);

CREATE INDEX index_選択科目 ON 選択科目データ (芸術選択, 社会選択, 理科選択);

CREATE INDEX index_理系理科 ON 選択科目データ (理科選択) WHERE 文理選択 = '理系';

SQLのプロファイリング

SQLの実行時間計測

SQLite3では以下のコマンドで実行時間が表示される。

sql

.timer on

--result
Run Time: real 0.001 user 0.000103 sys 0.000411

EXPLAIN

DBMSがどのような処理手順でSQLの実行を行っているか確認できる。

sql

EXPLAIN QUERY PLAN SQL文;

インデックスが使用されている場合は、使用されているインデックス名が表示されるため、意図通りにインデックスが動作しているか確認することができる。

また、以下で処理の詳細がわかる。

sql

EXPLAIN SQL文;

実行例

EXPLAIN QUERY PLAN SELECT * FROM 生徒成績データ WHERE 外国語 BETWEEN 60 AND 80;

--result
QUERY PLAN
`--SEARCH TABLE 生徒成績データ USING INDEX index_外国語 (外国語>? AND 外国語<?)
--インデックスが使用されている。


--インデックス削除
DROP INDEX index_外国語;

--もう一度
EXPLAIN QUERY PLAN SELECT * FROM 生徒成績データ WHERE 外国語 BETWEEN 60 AND 80;

--result
QUERY PLAN
`--SCAN TABLE 生徒成績データ
--インデックスが使用されていない。