SQL Tutorial

グループ合同の新卒研修で行った SQL 入門向けの解説 + ワークショップです。
基本的な部分の解説のみで、一部触れていない構文もございます。
ご了承ください。

KKK: 価格, TNK: 単価, MST: マスタ, IDX: インデックス

# URL
HomePage: https://nrslib.com
Twitter: https://twitter.com/nrslib

Transcript

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