応用情報のためのSQLチートシート

対象

• DBについてテキストは読んだけど、触ったことがない人
  • 触った気分を味わえるように操作の結果をなるべく書きます。
• 試験前にSQLコマンドを確認したい人
  • 応用情報の問題を解く範囲で必要な知識をまとめます。

動作確認環境

OS	AWS EC2インスタンス(amzn2.x86_64)
DB	mysql Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1

DDL Database Define Language　データベース定義言語

データベース作成

MariaDB [(none)]> CREATE DATABASE testdb;
MariaDB [(none)]> USE testdb
MariaDB [testdb]> 

ユーザ作成

/*localhostからアクセス可能なユーザ名user1を作成。*/
CREATE USER user1@localhost;

/*パスワードつきで作成*/
CREATE USER user1@localhost IDENTIFIED BY 'password';

ユーザ管理

/*ユーザ削除*/
DROP USER user1@localhost;

/*権限付与*/
GRANT [権限1],[権限2]... ON test TO user1@localhost ;
GRANT ALL ON test TO user1@localhost; --すべてのコマンドの実行権限
GRANT CREATE ON test TO user1@localhost; --CREATEコマンドの実行権限
GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON test TO user1@localhost;

/*権限削除*/
REVOKE [権限1],[権限2]... ON test FROM user1@localhost;
REVOKE ALL ON test FROM user1@localhost; --すべてのコマンドの実行権限
REVOKE CREATE ON test FROM user1@localhost; --CREATEコマンドの実行権限
REVOKE ALTER, DELETE, INSERT, SELECT, UPDATE ON test FROM user1@localhost;

/*権限確認*/
SHOW GRANTS FOR user1@localhost;

テーブルモデル

• test1

A	B	C
1	a	100
2	b	200
3	c	300

• test2　重複のあるモデル

A	B	C
1	a	100
2	b	200
3	c	300
4	a	100
5	b	200
6	c	300
7	d	400
8	NULL	NULL

• test3　test2の重複データBに対して、Bを主キーとし、追加の情報を持つテーブル

B	D
a	'aaa'
b	'bbb'
c	'ccc'
d	'ddd'

• test4　test2の主キーAとtets3の主キーBに対して重複した情報と、追加の情報を持つテーブル

A	B	E
1	a	10
1	a	11
1	a	12
2	b	20
2	b	21
3	c	30

以下、このテーブルを操作した際の結果コマンドをこのように記述します

[A,B,C][1,a,100][2,b,200][3,c,300]

CREATE TABLE　テーブル操作

/*テーブル作成*/
CREATE TABLE test(
  A int, --整数
  B nchar(5), --UNICODE文字列 5文字
  C int --整数
);

/*テーブル削除*/
DROP TABLE test1;

/*テーブル名変更*/
ALTER TABLE test1 RENAME testX; --test1 -> testXに変更

/*列追加*/
ALTER TABLE test1 ADD D nchar(10); --[A,B,C,D][1,a,100,NULL][2,b,200,NULL][3,c,300,NULL][4,d,400,NULL]

/*列削除*/
ALTER TABLE test1 DROP COLUMN C; --[A,B][1,a][2,b][3,c]

/*データ追加*/
INSERT INTO test1 VALUES(4,'d',400); --[A,B,C][1,a,100][2,b,200][3,c,300][4,d,400]

/*データ削除*/
DELETE FROM test1 WEHRE B = 'c'; --[A,B,C][1,a,100][2,b,200]

/*データ全削除*/
DELETE FROM test1; --Empty set

/*データ更新*/
UPDATE test1 SET C = '150' WHERE B = 'a'; --[A,B,C][1,a,150][2,b,200]

DML Database Manipulation Language データベース操作言語

##SELECT　選択・抽出

/*列を選択*/
SELECT A, B FROM test1; --[A,B][1,a][2,b][3,c]

/*全選択*/
SELECT * FROM test1; --[A,B,C][1,a,100][2,b,200][3,c,300]

WHERE + 条件　条件の指定

比較演算子

/*比較演算子*/
SELECT A, B FROM test1 WHERE C = 200; --[A,B][2,b]
SELECT A, B FROM test1 WHERE C > 200; --[A,B][3,c]
SELECT A, B FROM test1 WHERE C < 200; --[A,B][1,a]
SELECT A, B FROM test1 WHERE C >= 200; --[A,B][2,b][3,c]
SELECT A, B FROM test1 WHERE C <= 200; --[A,B][1,a][2,b]

論理演算子、BETWEEN、IN

/*凡例*/
SELECT * FROM test1 WHERE [列] [条件1] [論理演算子] [条件2]...
SELECT * FROM test1 WHERE [列] BETWEEN [最小値] AND [最大値]; --最大値以上、最大値以下の行でソート
SELECT * FROM test1 WHERE [列] IN ([データ1],[データ2],...); --データが一致する行でソート

/*論理演算子*/
SELECT * FROM test1 WHERE 150 < C AND C < 250; --[A,B,C][2,b,200]
SELECT * FROM test1 WHERE C < 150 OR 250 < C ; --[A,B,C][1,a,100][3,c,300]

/*BETWEEN */
SELECT * FROM test1 WHERE C BETWEEN 150 AND 2000; --[A,B,C][2,b,200]
SELECT * FROM test1 WHERE 150 <= C AND C <= 200; --等価

/*IN*/
SELECT * FROM test1 WHERE B IN ('a', 'c'); --[A,B,C][1,a,100][3,c,300]
SELECT * FROM test1 WHERE B = 'a' OR B = 'c'; --等価

LIKE　正規表現もどき

/*LIKE*/
SELECT * FROM test1 WHERE [列] LIKE '%'; --"%"は任意の複数文字データ
SELECT * FROM test1 WHERE [列] LIKE '_'; --"_"は任意の1文字データ
SELECT * FROM test1 WHERE B LIKE 'a%'; --[A,B,C][1,a,100] 
SELECT * FROM test1 WHERE B LIKE '_'; --[A,B,C][1,a,100][2,b,200][3,c,300]

ORDER BY　並べ替え

/*凡例*/
SELECT * FROM test2 ORDER BY [列] ASC; --[列]で昇順に並べ替え
SELECT * FROM test2 ORDER BY [列] DESC; --[列]で降順に並べ替え

/*昇順*/
SELECT * FROM test2 ORDER BY B ASC; --[A,B,C][8,NULL,NULL][1,a,100][2,b,200][5,b,200][4,a,100][3,c,300][6,c,300][7,d,400]
SELECT * FROM test2 ORDER BY B; --等価、デフォルトは昇順

/*降順*/
SELECT * FROM test2 ORDER BY C DESC; --[A,B,C][7,d,400][3,c,300][6,c,300][2,b,200][5,b,200][1,a,100][4,a,100][8,NULL,NULL]

集約関数

/*凡例*/
SELECT [集約関数]([列]) FROM test2; -- [集約関数([列])][result]

/*合計*/
SELECT SUM(C) FROM test2; --[SUM(C)][1600]

/*平均*/
SELECT AVG(A) from test2; --[AVG(A)][4.5]

/*最大*/
SELECT MAX(C) from test2; --[MAX(C)][400]

/*最小*/
SELECT MIN(C) from test2; --[NIN(C)][100]

COUNT　行を数える

SELECT COUNT([列]) FROM test2; --[列]のデータ数
SELECT COUNT(*) FROM test2; --8 (test2の行数)

POINT　数え方の違いに注意
COUNT(カラム名)はNULL値でないデータの数(行数?)を返す
COUNT(*)はNULL値かどうかに関係なく、取得された行の数を返す

GROUP BY + 集約関数　重複排除

/*凡例*/
SELECT B from test2 GROUP BY B; --[キー列][key1][key2]...
SELECT [キー列], [集約関数]([列]) FROM test2 GROUP BY [キー列]; -- [キー列,集約結果][key1,result1][key2,result2]...

/*指定した列で重複排除したビューを作る -> ユニークなデータ(key)を持つ*/
SELECT B from test2 GROUP BY B; --[B][NULL][a][b][c][d]
SELECT A from test2 GROUP BY B; --[A][8][1][2][3][7]

/*keyごとに集約関数で計算する*/
SELECT B, SUM(C) FROM test2 GROUP BY B; --[B,SUM(C)][NULL,NULL][a,200][b,400][c,600][d,400]
SELECT B, AVG(A) FROM test2 GROUP BY B; --[B,AVG(C)][NULL,8.0][a,2.5][b,3.5][c,4.5][d,7]
SELECT B, MAX(A) FROM test2 GROUP BY B; --[B,MAX(A)][NULL,8][a,4][b,5][c,6][d,7]
SELECT B, MIN(A) FROM test2 GROUP BY B; --[B,MIN(A)][NULL,8][a,1][b,2][c,3][d,7]

/*keyごとに出現回数を数える*/
SELECT B, COUNT(B) FROM test2 GROUP BY B; --[B,COUNT(B)][NULL,0][a,2][b,2][c,2][d,1]
SELECT B, COUNT(*) FROM test2 GROUP BY B; --[B,COUNT(B)][NULL,1][a,2][b,2][c,2][d,1]
SELECT *, COUNT(*) FROM test2 GROUP BY B ;--[A,B,C,COUNT(*)][8,NULL,NULL,1][1,a,100,2][2,b,200,2][3,c,300,2][7,d,400,1]
SELECT *, COUNT(A) FROM test2 GROUP BY B ;--[A,B,C,COUNT(A)][8,NULL,NULL,1][1,a,100,2][2,b,200,2][3,c,300,2][7,d,400,1]
SELECT *, COUNT(B) FROM test2 GROUP BY B ;--[A,B,C,COUNT(B)][8,NULL,NULL,0][1,a,100,2][2,b,200,2][3,c,300,2][7,d,400,1]
SELECT *, COUNT(C) FROM test2 GROUP BY B ;--[A,B,C,COUNT(C)][8,NULL,NULL,0][1,a,100,2][2,b,200,2][3,c,300,2][7,d,400,1]

POINT　処理手順に注意

1. GRPOUP BYで指定した列がユニークなkeyを持つビューを作る
2. keyごとに集約関数で演算する、COUNT(キー列)の場合は重複排除する前のkeyの数を数える

AS 別名

/*凡例*/
SELECT [列] AS [別名] FROM test2; --[別名][value][value2]...

/*列に別名を付ける*/
SELECT A AS num FROM test2; --[num][1][2][3][4][5][6][7][8]

HAVING + 条件　条件指定(≒WHERE)

/*凡例*/
SELECT [列] FROM test2 HAVING [条件]; --[条件]によるフィルタ
SELECT [列] FROM test2 GROUP BY [キー列] HAVING [条件]; --[キー列]による集計 -> 条件によるフィルタ
SELECT [列] FROM test2 WHERE [条件1] GROUP BY [キー列] HAVING [条件2]; --[条件1]によるフィルタ -> [キー列]による集計 -> [条件2]によるフィルタ

/*条件指定*/
SELECT * FROM test2 HAVING B = 'a'; --[A,B,C][1,a,100][4,a,100]
SELECT * FROM test2 HAVING C <= '200'; --[A,B,C][1,a,100][2,b,200][4,a,100][5,b,200]

/*重複数を指定して抽出する*/
SELECT B, COUNT(B) FROM test2 GROUP BY B HAVING COUNT(B) > 1; --[B,COUNT(B)][a,2][b,2][c,2]

POINT　処理手順に注意

1. GRPOUP BYで指定した列がユニークなkeyを持つビューを作る
2. keyごとにCOUNTでデータ数える
3. ビューの中でHAVINGで指定した条件に合うものを抽出する

POINT　HAVINGとWHEREの違いに注意

• SQL実行順序の違い
  • FROM → WHERE → GROUP BY → HAVING → SELECT → ORDERBY
• 集計関数とCOUNTが使用可能かの違い
  • HAVING SUM()　〇 OK
  • WHERE SUM()　× NG

INNER JOIN　内部結合

/*凡例*/
SELECT [列],... FROM [テーブル1] INNER JOIN [テーブル2] ON [結合条件];
SELECT [列],... FROM [テーブル1] JOIN [テーブル2] ON [結合条件]; --INNNERは省略可

/*table2 x tabel3*/
SELECT * FROM test2 INNER JOIN test3 ON test2.B = test3.B;
--[A,B,C,B,D][1,a,100,a,aaa][2,b,200,b,bbb][3,c,300,c,ccc][4,a,100,a,aaa][5,b,200,b,bbb][6,c,300,c,ccc][7,d,NULL,d,ddd]

/*table3 x tabel2*/
SELECT * FROM test3 INNER JOIN test2 ON test2.B = test3.B;
--[B,D,A,B,C][a,aaa,1,a,100][b,bbb,2,b,200][c,ccc,3,c,300][a,aaa,4,a,100][b,bbb,5,b,200][c,ccc,6,c,300][d,ddd,7,d,NULL]

/*table2 x tabel3 列指定*/
SELECT A, test2.B, C, D FROM test2 INNER JOIN test3 ON test2.B = test3.B;
--[A,B,C,D][1,a,100,aaa][2,b,200,bbb][3,c,300,ccc][4,a,100,aaa][5,b,200,bbb][6,c,300,ccc][7,d,NULL,ddd]

/*table3 x tabel4*/
SELECT * FROM test3 INNER JOIN test4 ON test3.B = test4.B;
--[B,D,A,B,E][a,aaa,1,a,10][a,aaa,1,a,11][a,aaa,1,a,12][b,bbb,2,b,20][b,bbb,2,b,21][c,ccc,3,c,30]

/*table4 x table2, table4 x tabel3 */
SELECT test4.A, test3.D, test4.E  from test4
  INNER JOIN test2 ON test4.A = test2.A
  INNER JOIN test3 ON test4.B = test3.B;
--[A,D,E][1,aaa,10][1,aaa,11][1,aaa,12][2,bbb,20][2,bbb,21][3,ccc,30]

POINT　結合順序を入れ替えても結果は変わらない、ベースはどちらでもいい
POINT　結合条件に重複があるテーブルを基準に結合する(内積計算する)

OUTTER JOIN　外部結合

/*凡例*/
SELECT [列],... FROM [テーブル1] LEFT OUTER JOIN [テーブル2] ON [結合条件];
--LEFT OUTER JOIN の場合はjoin句の前にある[テーブル1]をベースにする
SELECT [列],... FROM [テーブル1] RIGHT OUTER JOIN [テーブル2] ON [結合条件];
--RIGHT OUTER JOIN の場合はjoin句の後にある[テーブル2]をベースにする
SELECT [列],... FROM [テーブル1],[テーブル2]; --条件なしの外部結合になる

/*test3をベースにtest4で拡張*/
SELECT * FROM test3 LEFT OUTER JOIN test4 ON test3.B = test4.B;
--[B,D,A,B,E][a,aaa,1,a,10][a,aaa,1,a,11][a,aaa,1,a,12][b,bbb,2,b,20][b,bbb,2,b,21][c,ccc,3,c,30]
[d,ddd,NULL,NULL,NULL]
SELECT * FROM test4 RIGHT OUTER JOIN test3 ON test3.B = test4.B;
--[A,B,E,B,D][1,a,10,a,aaa][1,a,11,a,aaa][1,a,12,a,aaa][2,b,20,b,bbb][2,b,21,b,bbb][3,c,30,c,ccc][NULL,NULL,NULL,d,ddd]　等価

/*test3をベースにtest4で拡張*/
SELECT * FROM test4 LEFT OUTER JOIN test3 ON test3.B = test4.B;
--[A,B,E,B,D][1,a,10,a,aaa][1,a,11,a,aaa][1,a,12,a,aaa][2,b,20,b,bbb][2,b,21,b,bbb][3,c,30,c,ccc]

/*RTGHT JOIN*/
SELECT * FROM test3 RIGHT OUTER JOIN test4 ON test3.B = test4.B;
--[B,D,A,B,E][a,aaa,1,a,10][a,aaa,1,a,11][a,aaa,1,a,12][b,bbb,2,b,20][b,bbb,2,b,21][c,ccc,3,c,30]

POINT　ベースのテーブルに対して、結合するテーブルの情報でデータを拡張する(外積計算する)
POINT　重複があるテーブルをベースにすれば、結果は内部結合と等価

副問い合わせ

/*戻り値の数値を利用するパターン*/
SELECT * FROM test2 WHERE C > (SELECT AVG(C) FROM test1); -- [A,B,C][3,c,300]　(戻り値 = 200)
SELECT A,B,C,C >= (SELECT AVG(C) FROM test1) AS flag FROM test1;
--[A,B,C,flag][1,a,100,0][2,b,200,1][3,c,300,c] (戻り値 = 200)

/*戻り値のビューを利用するパターン*/
SELECT B,C FROM (SELECT * FROM test1) alias; 
--[B,C][a,100][b,200][c,300]　(戻り値 = test1自体)
SELECT * FROM test2 WHERE C IN (SELECT C FROM test1 WHERE test1.C <= 200);
--[A,B,C][1,a,100][2,b,200][4,a,100][5,b,200]　(戻り値 =[C][100][200])
SELECT * FROM (SELECT AVG(C) as avg FROM test1) alias1, test1 alias2 WHERE alias1.avg <= alias2.C;
--[avg,A,B,C][200,2,b,200][200,3,c,300]　(戻り値 =　[ACG(C)][200])

POINT　副問い合わせのwhere句の戻り値が何かを考える
POINT　FROM句でビューを使う場合はエイリアスをにしないとエラーになる