More than 3 years have passed since last update.

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

Last updated at 2021-04-17Posted at 2021-04-17

対象

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　処理手順に注意

GRPOUP BYで指定した列がユニークなkeyを持つビューを作る
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　処理手順に注意

GRPOUP BYで指定した列がユニークなkeyを持つビューを作る
keyごとにCOUNTでデータ数える
ビューの中で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句でビューを使う場合はエイリアスをにしないとエラーになる

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

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

対象

動作確認環境

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

データベース作成

ユーザ作成

ユーザ管理

テーブルモデル

CREATE TABLE テーブル操作

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

WHERE + 条件 条件の指定

比較演算子

論理演算子、BETWEEN、IN

LIKE 正規表現もどき

ORDER BY 並べ替え