大学のデータベースのテストのために範囲をまとめてみた。
テーブルの表示
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 生徒成績データ
--インデックスが使用されていない。