SQL Tutorial

E37b4344ef4bfd0fc4826c04971e54fb?s=47 nrs
June 14, 2019

SQL Tutorial

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

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

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

E37b4344ef4bfd0fc4826c04971e54fb?s=128

nrs

June 14, 2019
Tweet

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

  11. 11

  12. 12

  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

  29. 29

  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 (1)もっとも人口が少ない国を探そう (2)面積がもっとも小さい国ベスト3を探そう

    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’

    が2文字以上存在する国の中で もっとも gdp が高い国を探そう 119
  120. 答え合わせ 120

  121. IN 句 SQL

  122. アジアとヨーロッパの中で 面積トップ3はどこの国でしょう 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

  138. 138

  139. 省略形を正式名称にしたい 139

  140. 140

  141. ふたつのテーブルを game.team1 と eteam.id でくっつけよう 141

  142. SELECT * FROM game JOIN eteam ON game.team1 = eteam.id

    142
  143. 143

  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

  188. 188

  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

  196. 196

  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
  205. 205

  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

  281. 281

  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