Slide 1
Slide 1 text
Masanobu Naruse
SQL Tutorial
SQL
Slide 2
Slide 2 text
はじめに
SQL
Slide 3
Slide 3 text
←アプリケーション開発者
成瀬 允宣
3
Slide 4
Slide 4 text
お話する内容は
アプリケーション開発者が
見ている景色です
4
Slide 5
Slide 5 text
概要
SQL
Slide 6
Slide 6 text
Structured Query Language
6
Slide 7
Slide 7 text
Structured Query Language
7
Slide 8
Slide 8 text
リレーショナルデータベースの
管理や操作を行うための人工言語
8
Slide 9
Slide 9 text
リレーショナルデータベースの
管理や操作を行うための人工言語
今日のメイン
9
Slide 10
Slide 10 text
リレーショナルデータベース
の
イメージが沸かない方へ
10
Slide 11
Slide 11 text
11
Slide 12
Slide 12 text
12
Slide 13
Slide 13 text
フィールド
13
Slide 14
Slide 14 text
カラム
14
Slide 15
Slide 15 text
レコード
15
Slide 16
Slide 16 text
テーブル
16
Slide 17
Slide 17 text
データベース
17
Slide 18
Slide 18 text
というところで
問題です
18
Slide 19
Slide 19 text
Q. 職業が「プログラマ」の人の条件は？
19
Slide 20
Slide 20 text
Q. 職業が「プログラマ」の人の条件は？
A. D列が”Programmer”であるデータ
20
Slide 21
Slide 21 text
Q. 職業が「プログラマ」の人の条件は？
A. D列が”Programmer”であるデータ
SELECT * FROM users WHERE D = 'Programmer'
21
Slide 22
Slide 22 text
実行して取れるデータ
SELECT * FROM users WHERE D = 'Programmer'
22
Slide 23
Slide 23 text
実行して取れるデータ
SELECT * FROM users WHERE D = 'Programmer'
23
Slide 24
Slide 24 text
やってみよう
SQL
Slide 25
Slide 25 text
https://sqlzoo.net
25
Slide 26
Slide 26 text
https://sqlzoo.net
26
Slide 27
Slide 27 text
https://sqlzoo.net
27
Slide 28
Slide 28 text
https://sqlzoo.net
28
Slide 29
Slide 29 text
29
Slide 30
Slide 30 text
ここに SQL を入力して
「Submit SQL」ボタンを押す
30
Slide 31
Slide 31 text
テーブル名
カラム名
ここに SQL を入力して
「Submit SQL」ボタンを押す
31
Slide 32
Slide 32 text
と入力して
実行してみよう
SELECT * FROM world
Exercise 1 : 最初のステップ SQLZOO: 0 SELECT basics
32
Slide 33
Slide 33 text
Wrong とか言われてるけど
気にしない！
33
Slide 34
Slide 34 text
world テーブルのレコードを
すべて取得
34
Slide 35
Slide 35 text
SELECT * FROM world
SELECT * FROM world
35
Slide 36
Slide 36 text
SELECT * FROM world
FROM 句
36
Slide 37
Slide 37 text
SELECT * FROM world
FROM 句
テーブルを指定
37
Slide 38
Slide 38 text
SELECT * FROM world
SELECT * FROM world
38
Slide 39
Slide 39 text
SELECT * FROM world
SELECT 句
39
Slide 40
Slide 40 text
SELECT * FROM world
SELECT 句
結果として取得するカラムを指定
40
Slide 41
Slide 41 text
SELECT * FROM world
SELECT 句
結果として取得するカラムを指定
*（アスタリスク）はワイルドカードで
「すべて」や「なんでも」の意味
41
Slide 42
Slide 42 text
欲しいカラムが ‘name’ と ‘area’ なら
42
Slide 43
Slide 43 text
欲しいカラムが ‘name’ と ‘area’ なら
SELECT name, area FROM world
SELECT world.name, world.area FROM world
43
Slide 44
Slide 44 text
Exercise 2 : 欲しいデータを取得 SQLZOO: 0 SELECT basics
列の順序がアルファベット順になるような
SQL を実行しよう
44
Slide 45
Slide 45 text
答え合わせ
45
Slide 46
Slide 46 text
WHERE 句
SQL
Slide 47
Slide 47 text
area（面積）が日本より大きい国を探そう
47
Slide 48
Slide 48 text
area（面積）が日本より大きい国を探そう
まず日本の面積は？
48
Slide 49
Slide 49 text
データはここにある
49
Slide 50
Slide 50 text
name はたぶん Japan
50
Slide 51
Slide 51 text
name はたぶん Japan
SELECT * FROM world WHERE name = 'Japan'
51
Slide 52
Slide 52 text
SELECT * FROM world WHERE name = 'Japan'
52
Slide 53
Slide 53 text
SELECT * FROM world WHERE name = 'Japan'
name が ‘Japan’ のレコードを取得
53
Slide 54
Slide 54 text
SELECT * FROM world WHERE name = 'Japan'
54
Slide 55
Slide 55 text
SELECT * FROM world WHERE name = 'Japan'
area が 377930 より大きい国を探す
55
Slide 56
Slide 56 text
SELECT * FROM world WHERE area > 377930
56
Slide 57
Slide 57 text
SELECT * FROM world WHERE area > 377930
area が 377930 より大きいレコードを取得
57
Slide 58
Slide 58 text
SELECT * FROM world WHERE area > 377930
58
Slide 59
Slide 59 text
SELECT * FROM world WHERE area > 377930
条件追加：日本より人口の多い国
59
Slide 60
Slide 60 text
SELECT * FROM world WHERE name = 'Japan'
60
Slide 61
Slide 61 text
SELECT * FROM world WHERE name = 'Japan'
area が 377930 より大きい
かつ
人口が 127,090,000 人より
多い国
61
Slide 62
Slide 62 text
FROM world WHERE area > 377930 AND population > 1
62
Slide 63
Slide 63 text
インデント大事
SELECT *
FROM world
WHERE area > 377930
AND population > 127090000
63
Slide 64
Slide 64 text
インデント大事
予約語で改行するといい感じ
SELECT *
FROM world
WHERE area > 377930
AND population > 127090000
64
Slide 65
Slide 65 text
SELECT *
FROM world
WHERE area > 377930
AND population > 127090000
65
Slide 66
Slide 66 text
SELECT *
FROM world
WHERE area > 377930
AND population > 127090000
area が 377,930 より大きい
66
Slide 67
Slide 67 text
SELECT *
FROM world
WHERE area > 377930
AND population > 127090000
area が 377,930 より大きい
かつ
67
Slide 68
Slide 68 text
SELECT *
FROM world
WHERE area > 377930
AND population > 127090000
area が 377,930 より大きい
かつ population が 127,090,000 より大きい
68
Slide 69
Slide 69 text
SELECT *
FROM world
WHERE area > 377930
AND population > 127090000
69
Slide 70
Slide 70 text
Exercise 3 : 条件指定 SQLZOO: 0 SELECT basics
面積か人口の数字が
日本以下の国を取得しよう
SELECT *
FROM world
WHERE area > 377930
AND population > 127090000
Sample
70
Slide 71
Slide 71 text
答え合わせ
71
Slide 72
Slide 72 text
ORDER BY 句
SQL
Slide 73
Slide 73 text
日本より面積と人口が大きい国で
GDP が最も少ない国は？
73
Slide 74
Slide 74 text
SELECT *
FROM world
WHERE area > 377930
AND population > 127090000
ORDER BY gdp
74
Slide 75
Slide 75 text
逆に日本より面積と人口が小さい国で
GDP が最も多い国は？
75
Slide 76
Slide 76 text
SELECT *
FROM world
WHERE area < 377930
AND population < 127090000
ORDER BY gdp DESC
76
Slide 77
Slide 77 text
SELECT *
FROM world
WHERE area > 377930
AND population > 127090000
ORDER BY gdp
SELECT *
FROM world
WHERE area < 377930
AND population < 127090000
ORDER BY gdp DESC 77
Slide 78
Slide 78 text
SELECT *
FROM world
WHERE area > 377930
AND population > 127090000
ORDER BY gdp
SELECT *
FROM world
WHERE area < 377930
AND population < 127090000
ORDER BY gdp DESC
昇順
78
Slide 79
Slide 79 text
SELECT *
FROM world
WHERE area > 377930
AND population > 127090000
ORDER BY gdp
SELECT *
FROM world
WHERE area < 377930
AND population < 127090000
ORDER BY gdp DESC
降順
79
Slide 80
Slide 80 text
SELECT *
FROM world
ORDER BY gdp DESC,
area,
population DESC
ソートするカラムは複数指定できる
80
Slide 81
Slide 81 text
Exercise 4 : ソート SQLZOO: 0 SELECT basics
（１）もっとも人口が少ない国を探そう
（２）面積がもっとも小さい国ベスト３を探そう
81
Slide 82
Slide 82 text
答え合わせ
82
Slide 83
Slide 83 text
副問い合わせ（サブクエリ）
SQL
Slide 84
Slide 84 text
ところで
84
Slide 85
Slide 85 text
SELECT *
FROM world
WHERE area > 377930
AND population > 127090000
ORDER BY gdp
85
Slide 86
Slide 86 text
SELECT *
FROM world
WHERE area > 377930
AND population > 127090000
ORDER BY gdp
人口変わったらどうしよう
86
Slide 87
Slide 87 text
SELECT *
FROM world
WHERE area > 377930
AND population > 127090000
ORDER BY gdp
87
Slide 88
Slide 88 text
SELECT *
FROM world
WHERE area > 377930
AND population >
(
SELECT population
FROM WORLD
WHERE name = 'Japan'
)
ORDER BY gdp
SELECT *
FROM world
WHERE area > 377930
AND population > 127090000
ORDER BY gdp
88
Slide 89
Slide 89 text
SELECT *
FROM world
WHERE area > 377930
AND population >
(
SELECT population
FROM world
)
ORDER BY gdp
副問い合わせが
複数行の結果になるとエラー
89
Slide 90
Slide 90 text
Exercise 5 : 副問い合わせ SQLZOO: 0 SELECT basics
面積も副問い合わせを利用するようにしよう
SELECT *
FROM world
WHERE area > 377930
AND population >
(
SELECT population
FROM WORLD
WHERE name = 'Japan'
)
ORDER BY gdp
90
Slide 91
Slide 91 text
答え合わせ
91
Slide 92
Slide 92 text
LIKE句
SQL
Slide 93
Slide 93 text
日本みたいな国探して
93
Slide 94
Slide 94 text
日本みたいな国探して
94
Slide 95
Slide 95 text
‘Ja’ で始まる国を探して
95
Slide 96
Slide 96 text
SELECT *
FROM world
WHERE name LIKE 'Ja%'
96
Slide 97
Slide 97 text
SELECT *
FROM world
WHERE name LIKE 'Ja%'
97
Slide 98
Slide 98 text
SELECT *
FROM world
WHERE name LIKE 'Ja%'
ワイルドカード
98
Slide 99
Slide 99 text
SELECT *
FROM world
WHERE name LIKE 'J%';
SELECT *
FROM world
WHERE name LIKE '%n';
SELECT *
FROM world
WHERE name LIKE 'J%n';
99
Slide 100
Slide 100 text
SELECT *
FROM world
WHERE name LIKE 'J%';
SELECT *
FROM world
WHERE name LIKE '%n';
SELECT *
FROM world
WHERE name LIKE 'J%n';
クエリの区切りはセミコロン 100
Slide 101
Slide 101 text
SELECT *
FROM world
WHERE name LIKE 'J%';
SELECT *
FROM world
WHERE name LIKE '%n';
SELECT *
FROM world
WHERE name LIKE 'J%n';
‘J’ からはじまる 101
Slide 102
Slide 102 text
SELECT *
FROM world
WHERE name LIKE 'J%';
SELECT *
FROM world
WHERE name LIKE '%n';
SELECT *
FROM world
WHERE name LIKE 'J%n';
‘n’ で終わる 102
Slide 103
Slide 103 text
SELECT *
FROM world
WHERE name LIKE 'J%';
SELECT *
FROM world
WHERE name LIKE '%n';
SELECT *
FROM world
WHERE name LIKE 'J%n';
‘J’ で始まり ‘n’ で終わる 103
Slide 104
Slide 104 text
注意
適当な LIKE 検索はスロークエリの元
104
Slide 105
Slide 105 text
インデックス
SQL
Slide 106
Slide 106 text
辞書で何かを調べるとき
どうやって辞書を引いてる？
106
Slide 107
Slide 107 text
日本を調べるなら
「に」から
はじまるところ
107
Slide 108
Slide 108 text
日本を調べるなら
「に」から
はじまるところ
DB も同じ！
108
Slide 109
Slide 109 text
任意のカラムにインデックスを設定すると
検索が高速になる
（辞書のもくじみたいなもの）
109
Slide 110
Slide 110 text
任意のカラムにインデックスを設定すると
検索が高速になる
（辞書のもくじみたいなもの）
110
Slide 111
Slide 111 text
SELECT *
FROM world
WHERE name LIKE 'J%';
SELECT *
FROM world
WHERE name LIKE '%n';
SELECT *
FROM world
WHERE name LIKE 'J%n';
111
Slide 112
Slide 112 text
SELECT *
FROM world
WHERE name LIKE 'J%';
SELECT *
FROM world
WHERE name LIKE '%n';
SELECT *
FROM world
WHERE name LIKE 'J%n';
インデックスが効く 112
Slide 113
Slide 113 text
SELECT *
FROM world
WHERE name LIKE 'J%';
SELECT *
FROM world
WHERE name LIKE '%n';
SELECT *
FROM world
WHERE name LIKE 'J%n';
インデックスが効かない 113
Slide 114
Slide 114 text
SELECT *
FROM world
WHERE name LIKE 'J%';
SELECT *
FROM world
WHERE name LIKE '%n';
SELECT *
FROM world
WHERE name LIKE 'J%n';
インデックスが前方だけ効く 114
Slide 115
Slide 115 text
注意
手あたり次第ダメ絶対
115
Slide 116
Slide 116 text
インデックスを設定するということは
どこかにその情報を保持しているということ
116
Slide 117
Slide 117 text
インデックスを設定するということは
どこかにその情報を保持しているということ
リソースを消費するし
データを追加する際に
インデックスの更新が必要
117
Slide 118
Slide 118 text
インデックスを設定するということは
どこかにその情報を保持しているということ
リソースを消費するし
データを追加する際に
インデックスの更新が必要
必要なところにだけ
インデックスを貼ろう
118
Slide 119
Slide 119 text
Exercise 6 : あいまい検索 SQLZOO: 0 SELECT basics
国名に ‘a’ が２文字以上存在する国の中で
もっとも gdp が高い国を探そう
119
Slide 120
Slide 120 text
答え合わせ
120
Slide 121
Slide 121 text
IN 句
SQL
Slide 122
Slide 122 text
アジアとヨーロッパの中で
面積トップ３はどこの国でしょう
122
Slide 123
Slide 123 text
SELECT *
FROM world
WHERE continent IN ('Asia', 'Europe')
ORDER BY area DESC
123
Slide 124
Slide 124 text
SELECT *
FROM world
WHERE continent IN ('Asia', 'Europe')
ORDER BY area DESC
124
Slide 125
Slide 125 text
SELECT *
FROM world
WHERE continent IN ('Asia', 'Europe')
ORDER BY area DESC
125
Slide 126
Slide 126 text
126
SELECT *
FROM world
WHERE population IN
(
SELECT population
FROM world
WHERE name = 'Japan'
)
IN ならサブクエリの結果が
複数行でも OK
（このクエリ自体は微妙）
Slide 127
Slide 127 text
SUM
SQL
Slide 128
Slide 128 text
世界の全人口は？
128
Slide 129
Slide 129 text
SELECT SUM(population)
FROM world;
129
Slide 130
Slide 130 text
Exercise 7 : 合計を求めよう SQLZOO: 0 SELECT basics
アジアの総人口がいくつか調べよう
130
Slide 131
Slide 131 text
答え合わせ
131
Slide 132
Slide 132 text
COUNT
SQL
Slide 133
Slide 133 text
世界にはいくつの国がある？
133
Slide 134
Slide 134 text
SELECT COUNT(*)
FROM world;
134
Slide 135
Slide 135 text
Exercise 8 : 数えよう SQLZOO: 0 SELECT basics
アジアの国がいくつかあるか調べよう
135
Slide 136
Slide 136 text
答え合わせ
136
Slide 137
Slide 137 text
JOIN 句
SQL
Slide 138
Slide 138 text
138
Slide 139
Slide 139 text
省略形を正式名称にしたい
139
Slide 140
Slide 140 text
140
Slide 141
Slide 141 text
ふたつのテーブルを
game.team1 と eteam.id
でくっつけよう
141
Slide 142
Slide 142 text
SELECT *
FROM game
JOIN eteam ON game.team1 = eteam.id
142
Slide 143
Slide 143 text
143
Slide 144
Slide 144 text
こっちは？
144
Slide 145
Slide 145 text
SELECT *
FROM game
JOIN eteam ON game.team1 = eteam.id
JOIN eteam ON game.team2 = eteam.id
145
Slide 146
Slide 146 text
SELECT *
FROM game
JOIN eteam ON game.team1 = eteam.id
JOIN eteam ON game.team2 = eteam.id
146
Slide 147
Slide 147 text
SELECT *
FROM game
JOIN eteam ON game.team1 = eteam.id
JOIN eteam ON game.team2 = eteam.id
147
Slide 148
Slide 148 text
SELECT *
FROM game
JOIN eteam eteam1 ON game.team1 = eteam1.id
JOIN eteam eteam2 ON game.team2 = eteam2.id
148
Slide 149
Slide 149 text
SELECT *
FROM game
JOIN eteam eteam1 ON game.team1 = eteam1.id
JOIN eteam eteam2 ON game.team2 = eteam2.id
エイリアス（別名）
149
Slide 150
Slide 150 text
SELECT *
FROM game
JOIN eteam eteam1 ON game.team1 = eteam1.id
JOIN eteam eteam2 ON game.team2 = eteam2.id
150
Slide 151
Slide 151 text
SELECT *
FROM game
JOIN eteam eteam1 ON game.team1 = eteam1.id
JOIN eteam eteam2 ON game.team2 = eteam2.id
151
Slide 152
Slide 152 text
SELECT *
FROM game
JOIN eteam eteam1 ON game.team1 = eteam1.id
JOIN eteam eteam2 ON game.team2 = eteam2.id
二重にデータが取れてるし
そもそも正式名称あるから
いらないよね
152
Slide 153
Slide 153 text
SELECT
game.id,
game.mdate,
game.stadium,
eteam1.teamname,
eteam1.coach,
eteam2.teamname,
eteam2.coach
FROM game
JOIN eteam eteam1 ON game.team1 = eteam1.id
JOIN eteam eteam2 ON game.team2 = eteam2.id
153
Slide 154
Slide 154 text
別名を省略形にすべきか否か
154
Slide 155
Slide 155 text
SELECT
g.id,
g.mdate,
g.stadium,
t1.teamname,
t1.coach,
t2.teamname,
t2.coach
FROM game g
JOIN eteam t1 ON game.team1 = t1.id
JOIN eteam t2 ON game.team2 = t2.id
155
Slide 156
Slide 156 text
個人の意見を言います
156
Slide 157
Slide 157 text
何も考えずに省略するのはやめよう
157
Slide 158
Slide 158 text
SELECT
g.id,
g.mdate,
g.stadium,
t1.teamname,
t1.coach,
t2.teamname,
t2.coach
158
Slide 159
Slide 159 text
SELECT
g.id,
g.mdate,
g.stadium,
t1.teamname,
t1.coach,
t2.teamname,
t2.coach
省略形は脳内スタックを消費する
159
Slide 160
Slide 160 text
SELECT
game.id,
game.mdate,
game.stadium,
eteam1.teamname,
eteam1.coach,
eteam2.teamname,
eteam2.coach
SELECT
g.id,
g.mdate,
g.stadium,
t1.teamname,
t1.coach,
t2.teamname,
t2.coach
省略形は脳内スタックを消費する
160
Slide 161
Slide 161 text
コーディングでも
「安易な省略形はやめよう」
と言われています
161
Slide 162
Slide 162 text
KKK TNK
MST
何の意味か分かりますか
IDX
162
Slide 163
Slide 163 text
SQL も同じ
163
Slide 164
Slide 164 text
コーディングの基本は
書くときに努力して
読むときに楽をすること
164
Slide 165
Slide 165 text
そのエイリアスが理解しやすいかどうか
一歩踏みとどまって考えてください
165
Slide 166
Slide 166 text
Exercise 9 : テーブルを結合しよう SQLZOO: 6 JOIN
goal テーブルと eteam テーブルを結合しよう
SELECT *
FROM game
JOIN eteam eteam1 ON game.team1 = eteam1.id
JOIN eteam eteam2 ON game.team2 = eteam2.id
Sample
166
Slide 167
Slide 167 text
答え合わせ
167
Slide 168
Slide 168 text
INNER / LEFT OUTER / RIGHT OUTER
JOIN
SQL
Slide 169
Slide 169 text
通常の JOIN は INNER JOIN
169
Slide 170
Slide 170 text
id name division
1 John 1
2 Jane 2
3 Naruse 3
id name
1 Customer Support
2 System
partners divisions
170
Slide 171
Slide 171 text
id name division
1 John 1
2 Jane 2
3 Naruse 3
id name
1 Customer Support
2 System
partners divisions
SELECT * FROM partners
JOIN divisions ON partners.division = divisions.id
171
Slide 172
Slide 172 text
id name division
1 John 1
2 Jane 2
3 Naruse 3
id name
1 Customer Support
2 System
partners divisions
SELECT * FROM partners
JOIN divisions ON partners.division = divisions.id
partners.id partners.name partners.division divisions.id divisions.name
1 John 1 1 Customer Support
2 Jane 2 2 System
172
Slide 173
Slide 173 text
id name division
1 John 1
2 Jane 2
3 Naruse 3
id name
1 Customer Support
2 System
partners divisions
SELECT * FROM partners
LEFT OUTER JOIN divisions ON partners.division = divisions.id
173
Slide 174
Slide 174 text
id name division
1 John 1
2 Jane 2
3 Naruse 3
id name
1 Customer Support
2 System
partners divisions
SELECT * FROM partners
LEFT OUTER JOIN divisions ON partners.division = divisions.id
174
Slide 175
Slide 175 text
id name division
1 John 1
2 Jane 2
3 Naruse 3
id name
1 Customer Support
2 System
partners divisions
partners.id partners.name partners.division divisions.id divisions.name
1 John 1 1 Customer Support
2 Jane 2 2 System
3 Naruse 3 NULL NULL
SELECT * FROM partners
LEFT OUTER JOIN divisions ON partners.division = divisions.id
175
Slide 176
Slide 176 text
DISTINCT
SQL
Slide 177
Slide 177 text
player の一覧がほしい
177
Slide 178
Slide 178 text
SELECT * FROM goal ORDER BY player
178
Slide 179
Slide 179 text
SELECT * FROM goal ORDER BY player
179
Slide 180
Slide 180 text
SELECT * FROM goal ORDER BY player
重複してる
180
Slide 181
Slide 181 text
SELECT * FROM goal ORDER BY player
重複してる
DISTINCT
別個の
181
Slide 182
Slide 182 text
SELECT DISTINCT(player) FROM goal ORDER BY player
182
Slide 183
Slide 183 text
SELECT DISTINCT(player) FROM goal ORDER BY player
183
Slide 184
Slide 184 text
Exercise 10 : 組み合わせ SQLZOO: 6 JOIN
player は全部で何人いるでしょうか
184
Slide 185
Slide 185 text
答え合わせ
185
Slide 186
Slide 186 text
CREATE TABLE
SQL
Slide 187
Slide 187 text
データを入れるテーブルの作り方
187
Slide 188
Slide 188 text
188
Slide 189
Slide 189 text
CREATE TABLE world
(
name VARCHAR(20),
continent VARCHAR(20),
area INTEGER,
population INTEGER,
gdp INTEGER,
capital VARCHAR(20),
tld VARCHAR(4)
) 189
Slide 190
Slide 190 text
CREATE TABLE world
(
name VARCHAR(20),
continent VARCHAR(20),
area INTEGER,
population INTEGER,
gdp INTEGER,
capital VARCHAR(20),
tld VARCHAR(4)
)
190
Slide 191
Slide 191 text
CREATE TABLE world
(
name VARCHAR(20),
continent VARCHAR(20),
area INTEGER,
population INTEGER,
gdp INTEGER,
capital VARCHAR(20),
tld VARCHAR(4)
)
カラムの宣言
191
Slide 192
Slide 192 text
CREATE TABLE world
(
name VARCHAR(20),
continent VARCHAR(20),
area INTEGER,
population INTEGER,
gdp INTEGER,
capital VARCHAR(20),
tld VARCHAR(4)
)
カラムの型
VARCHAR は可変長な文字列
192
Slide 193
Slide 193 text
CREATE TABLE world
(
name VARCHAR(20),
continent VARCHAR(20),
area INTEGER,
population INTEGER,
gdp INTEGER,
capital VARCHAR(20),
tld VARCHAR(4)
)
カラムの型
VARCHAR は可変長な文字列
データベースによって方言があるので注意
193
Slide 194
Slide 194 text
INSERT
SQL
Slide 195
Slide 195 text
テーブルにデータを挿入する
195
Slide 196
Slide 196 text
196
Slide 197
Slide 197 text
INSERT INTO world
VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad', '.pk')
197
Slide 198
Slide 198 text
CONSTRAINT
SQL
Slide 199
Slide 199 text
INSERT INTO world
VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad', '.pk')
199
Slide 200
Slide 200 text
INSERT INTO world
VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad', '.pk')
INSERT INTO world
VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad')
tld 入れ忘れた！
200
Slide 201
Slide 201 text
INSERT INTO world
VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad', '.pk')
INSERT INTO world
VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad')
tld 入れ忘れた！
NULL
201
Slide 202
Slide 202 text
INSERT INTO world
VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad', '.pk')
INSERT INTO world
VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad')
tld 入れ忘れた！
NULL
防ぎたい
202
Slide 203
Slide 203 text
CREATE TABLE world
(
name VARCHAR(20) NOT NULL,
continent VARCHAR(20) NOT NULL,
area INTEGER NOT NULL,
population INTEGER NOT NULL,
gdp INTEGER NOT NULL,
capital VARCHAR(20) NOT NULL,
tld VARCHAR(4) NOT NULL
)
203
Slide 204
Slide 204 text
CREATE TABLE world
(
name VARCHAR(20) NOT NULL,
continent VARCHAR(20) NOT NULL,
area INTEGER NOT NULL,
population INTEGER NOT NULL,
gdp INTEGER NOT NULL,
capital VARCHAR(20) NOT NULL,
tld VARCHAR(4) NOT NULL
)
204
Slide 205
Slide 205 text
205
Slide 206
Slide 206 text
INSERT INTO world
VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad', '.pk')
206
Slide 207
Slide 207 text
INSERT INTO world
VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad', '.pk')
INSERT INTO world
VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad', '.pk')
207
Slide 208
Slide 208 text
INSERT INTO world
VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad', '.pk')
INSERT INTO world
VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad', '.pk')
間違えて Pakistan がふたつ！
208
Slide 209
Slide 209 text
INSERT INTO world
VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad', '.pk')
INSERT INTO world
VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad', '.pk')
間違えて Pakistan がふたつ！
防ぎたい
209
Slide 210
Slide 210 text
CREATE TABLE world
(
name VARCHAR(20) UNIQUE,
continent VARCHAR(20) NOT NULL,
area INTEGER NOT NULL,
population INTEGER NOT NULL,
gdp INTEGER NOT NULL,
capital VARCHAR(20) NOT NULL,
tld VARCHAR(4) NOT NULL
)
210
Slide 211
Slide 211 text
CREATE TABLE world
(
name VARCHAR(20) UNIQUE,
continent VARCHAR(20) NOT NULL,
area INTEGER NOT NULL,
population INTEGER NOT NULL,
gdp INTEGER NOT NULL,
capital VARCHAR(20) NOT NULL,
tld VARCHAR(4) NOT NULL
)
211
Slide 212
Slide 212 text
PRIMARY KEY
SQL
Slide 213
Slide 213 text
世の中には同一性が重要なことがある
213
Slide 214
Slide 214 text
What’s 同一性？
214
Slide 215
Slide 215 text
誕生日
20 才 21 才
215
Slide 216
Slide 216 text
誕生日
20 才 21 才
別人？
216
Slide 217
Slide 217 text
John Doe John Doe
217
Slide 218
Slide 218 text
John Doe John Doe
同一人物？
218
Slide 219
Slide 219 text
人が区別される理由は
属性とは無縁なところにある
219
Slide 220
Slide 220 text
Name
John Doe
Jane Doe
John Smith
220
Slide 221
Slide 221 text
Name
John Doe
Jane Doe
John Smith
John Smith さんの名字が
Doe に変わったら？
221
Slide 222
Slide 222 text
Name
John Doe
Jane Doe
John Doe
222
Slide 223
Slide 223 text
Name
John Doe
Jane Doe
John Doe
ソフトウェアシステムには
見分けがつかない
223
Slide 224
Slide 224 text
id Name
1 John Doe
2 Jane Doe
3 John Doe
224
Slide 225
Slide 225 text
id Name
1 John Doe
2 Jane Doe
3 John Doe
ソフトウェアシステムでも
見分けがつく
225
Slide 226
Slide 226 text
CREATE TABLE user
(
id INTEGER PRIMARY KEY,
name VARCHAR(20) NOT NULL
)
226
Slide 227
Slide 227 text
CREATE TABLE user
(
id INTEGER PRIMARY KEY,
name VARCHAR(20) NOT NULL
)
id = Identity（同一性）
227
Slide 228
Slide 228 text
CREATE TABLE user
(
id INTEGER PRIMARY KEY,
name VARCHAR(20) NOT NULL
)
PRIMARY KEY 制約は一意であることを保証する
228
Slide 229
Slide 229 text
CREATE TABLE user
(
id INTEGER PRIMARY KEY,
name VARCHAR(20) NOT NULL
)
PRIMARY KEY 制約は一意であることを保証する
（NOT NULL かつ UNIQUE）
229
Slide 230
Slide 230 text
SQL ZOO
SQL
Slide 231
Slide 231 text
時間のある限り Workshop
231
Slide 232
Slide 232 text
コメント
SQL
Slide 233
Slide 233 text
SELECT
*
FROM world
WHERE area > 377930
233
Slide 234
Slide 234 text
SELECT
*
FROM world
WHERE area > 377930 -- 日本の面積
234
Slide 235
Slide 235 text
脆弱性
SQL
Slide 236
Slide 236 text
ログイン機能はどう作るか
236
Slide 237
Slide 237 text
SELECT
*
FROM users
WHERE users.id = 'naruse'
AND users.password = 'password'
237
Slide 238
Slide 238 text
SELECT
*
FROM users
WHERE users.id = 'naruse'
AND users.password = 'password'
これでレコード読めたら OK
238
Slide 239
Slide 239 text
SELECT
*
FROM users
WHERE users.id = 'naruse'
AND users.password = 'password'
ここはユーザの入力
239
Slide 240
Slide 240 text
SELECT
*
FROM users
WHERE users.id = ''
AND users.password = ''
240
Slide 241
Slide 241 text
SELECT
*
FROM users
WHERE users.id = ''
AND users.password = ''
ID: naruse
PASS: password
241
Slide 242
Slide 242 text
SELECT
*
FROM users
WHERE users.id = 'naruse'
AND users.password = 'password'
ID: naruse
PASS: password
242
Slide 243
Slide 243 text
SELECT
*
FROM users
WHERE users.id = ''
AND users.password = ''
ID: naruse
PASS: ‘ OR ‘a’ = ‘a
243
Slide 244
Slide 244 text
SELECT
*
FROM users
WHERE users.id = ''
users.password = ''
ID: naruse
PASS: ‘ OR ‘a’ = ‘a
SELECT
*
FROM users
WHERE users.id = 'naruse’
AND users.password = '' OR 'a' = 'a'
244
Slide 245
Slide 245 text
SELECT
*
FROM users
WHERE users.id = ''
users.password = ''
ID: naruse
PASS: ‘ OR ‘a’ = ‘a
245
SELECT
*
FROM users
WHERE users.id = 'naruse’
AND users.password = '' OR 'a' = 'a'
アタック成功
Slide 246
Slide 246 text
SQL インジェクション
という脆弱性です
246
SELECT
*
FROM users
WHERE users.id = 'naruse’
AND users.password = '' OR 'a' = 'a'
Slide 247
Slide 247 text
防ぎ方
247
Slide 248
Slide 248 text
プレースホルダーという
プログラミング言語に用意されている
SQL 用の構文を利用
DB::select(
'SELECT * FROM users WHERE id = ? AND password = ?',
['naruse', 'password']
);
248
Slide 249
Slide 249 text
OR
249
Slide 250
Slide 250 text
許容しない文字を
ちゃんと弾くか
無害化する
ID: naruse
PASS: ‘ OR ‘a’ = ‘a
250
Slide 251
Slide 251 text
OR
251
Slide 252
Slide 252 text
そもそも SQL を
プログラムから使わない
252
Slide 253
Slide 253 text
O/R Mapper
SQL
Slide 254
Slide 254 text
インピーダンスミスマッチ
電気工学の分野において
エネルギーの伝わり方が異なる媒体の
境界にて反射や損失が発生し
エネルギー伝送に不都合が起きる現象
254
Slide 255
Slide 255 text
インピーダンスミスマッチ
転じて
コンピュータシステムにおける
異なるコンセプトにある技術的要素同士の
ギャップを指す
255
Slide 256
Slide 256 text
インピーダンスミスマッチ
オブジェクト指向プログラミングと
リレーショナルデータベースの
関係がよくある例として挙げられる
256
Slide 257
Slide 257 text
OOP
Employee
Name
Age
Division
Position
257
Slide 258
Slide 258 text
OOP RDB
Employee
Name
Age
Division
Position
258
Slide 259
Slide 259 text
OOP RDB
Employee
Name
Age
Division
Position
name age division position
Jane 22 1 1
employees
259
Slide 260
Slide 260 text
OOP RDB
Employee
Name
Age
Division
Position
name age division position
Jane 22 1 1
id name
1 System Division
employees
divisions
260
Slide 261
Slide 261 text
OOP RDB
Employee
Name
Age
Division
Position
name age division position
Jane 22 1 1
id name
1 System Division
id name
1 Partner
employees
divisions
positions
261
Slide 262
Slide 262 text
OOP RDB
Employee
Name
Age
Division
Position
name age division position
Jane 22 1 1
id name
1 System Division
id name
1 Partner
employees
divisions
positions
O/R
Mapper
262
Slide 263
Slide 263 text
public class MyDbContext : DbContext
{
public DbSet Users { get; set; }
}
public class User
{
public string Id { get; set; }
public string Name { get; set; }
}
263
Slide 264
Slide 264 text
public class MyDbContext : DbContext
{
public DbSet Users { get; set; }
}
public class User
{
public string Id { get; set; }
public string Name { get; set; }
}
using (var context = new MyDbContext())
{
var user = context.Users.Where(x => x.Name == "naruse");
...
}
264
Slide 265
Slide 265 text
public class MyDbContext : DbContext
{
public DbSet Users { get; set; }
}
public class User
{
public string Id { get; set; }
public string Name { get; set; }
}
using (var context = new MyDbContext())
{
var user = context.Users.Where(x => x.Name == "naruse");
...
}
SELECT * FROM users WHERE namee = 'naruse'
265
Slide 266
Slide 266 text
public class MyDbContext : DbContext
{
public DbSet Users { get; set; }
}
public class User
{
public string Id { get; set; }
public string Name { get; set; }
}
using (var context = new MyDbContext())
{
var user = context.Users.Where(x => x.Name == "naruse");
...
}
SELECT * FROM users WHERE namee = 'naruse'
SQL が生成されて
実行される
266
Slide 267
Slide 267 text
コードファースト
SQL
Slide 268
Slide 268 text
SQL が作れるなら
テーブルも作れるんでない？
268
Slide 269
Slide 269 text
ソースコード
269
Slide 270
Slide 270 text
ソースコード データベース
270
Slide 271
Slide 271 text
ソースコード データベース
テーブル
生成
271
Slide 272
Slide 272 text
ソースコード データベース
変更
272
Slide 273
Slide 273 text
ソースコード データベース
テーブル
定義変更
変更
273
Slide 274
Slide 274 text
リードモデル
SQL
Slide 275
Slide 275 text
O/R Mapper は万能ではない
275
Slide 276
Slide 276 text
コードから SQL を生成するため
最適化ができないときがある
276
Slide 277
Slide 277 text
そんなときは SQL を実行したり
融通の効く薄いライブラリを使って
最適化を行う
277
Slide 278
Slide 278 text
判断基準は
それが読み込み処理かどうか
278
Slide 279
Slide 279 text
詳しくは
CQRS
という単語で調べてみてね
279
Slide 280
Slide 280 text
コンウェイの法則
SQL
Slide 281
Slide 281 text
281
Slide 282
Slide 282 text
単純な読み込みなのに
なんでこんな遅いんだ？
282
Slide 283
Slide 283 text
id product awesome del
1 101
Slide 284
Slide 284 text
id product awesome del
1 101
Slide 285
Slide 285 text
id product awesome del
1 101
Slide 286
Slide 286 text
id product awesome del
1 101
Slide 287
Slide 287 text
テーブルの定義を変更するために
データベースを管理しているチームに
依頼しなくてはいけなかった
287
Slide 288
Slide 288 text
テーブルの定義を変更するために
データベースを管理しているチームに
依頼しなくてはいけなかった
コンウェイの法則
288
Slide 289
Slide 289 text
システムを設計する組織は、
その構造をそっくりまねた構造の
設計を生み出してしまう
コンウェイの法則
289
Slide 290
Slide 290 text
おわりに
SQL
Slide 291
Slide 291 text
今日話したことは
最低限必要なものだけです
291
Slide 292
Slide 292 text
他にも構文はいろいろあります
データベースの種類ごとに特性もあります
292
Slide 293
Slide 293 text
試行錯誤と学習を繰り返して
プロダクトに最適なものを
探していってください
293
Slide 294
Slide 294 text
Auther Masanobu Naruse
HomePage https://nrslib.com
Twitter @nrslib