Upgrade to Pro — share decks privately, control downloads, hide ads and more …

SQL Tutorial

nrs
June 14, 2019

SQL Tutorial

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

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

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

nrs

June 14, 2019
Tweet

More Decks by nrs

Other Decks in Programming

Transcript

  1. Masanobu Naruse
    SQL Tutorial
    SQL

    View Slide

  2. はじめに
    SQL

    View Slide

  3. ←アプリケーション開発者
    成瀬 允宣
    3

    View Slide

  4. お話する内容は
    アプリケーション開発者が
    見ている景色です
    4

    View Slide

  5. 概要
    SQL

    View Slide

  6. Structured Query Language
    6

    View Slide

  7. Structured Query Language
    7

    View Slide

  8. リレーショナルデータベースの
    管理や操作を行うための人工言語
    8

    View Slide

  9. リレーショナルデータベースの
    管理や操作を行うための人工言語
    今日のメイン
    9

    View Slide

  10. リレーショナルデータベース

    イメージが沸かない方へ
    10

    View Slide

  11. 11

    View Slide

  12. 12

    View Slide

  13. フィールド
    13

    View Slide

  14. カラム
    14

    View Slide

  15. レコード
    15

    View Slide

  16. テーブル
    16

    View Slide

  17. データベース
    17

    View Slide

  18. というところで
    問題です
    18

    View Slide

  19. Q. 職業が「プログラマ」の人の条件は?
    19

    View Slide

  20. Q. 職業が「プログラマ」の人の条件は?
    A. D列が”Programmer”であるデータ
    20

    View Slide

  21. Q. 職業が「プログラマ」の人の条件は?
    A. D列が”Programmer”であるデータ
    SELECT * FROM users WHERE D = 'Programmer'
    21

    View Slide

  22. 実行して取れるデータ
    SELECT * FROM users WHERE D = 'Programmer'
    22

    View Slide

  23. 実行して取れるデータ
    SELECT * FROM users WHERE D = 'Programmer'
    23

    View Slide

  24. やってみよう
    SQL

    View Slide

  25. https://sqlzoo.net
    25

    View Slide

  26. https://sqlzoo.net
    26

    View Slide

  27. https://sqlzoo.net
    27

    View Slide

  28. https://sqlzoo.net
    28

    View Slide

  29. 29

    View Slide

  30. ここに SQL を入力して
    「Submit SQL」ボタンを押す
    30

    View Slide

  31. テーブル名
    カラム名
    ここに SQL を入力して
    「Submit SQL」ボタンを押す
    31

    View Slide

  32. と入力して
    実行してみよう
    SELECT * FROM world
    Exercise 1 : 最初のステップ SQLZOO: 0 SELECT basics
    32

    View Slide

  33. Wrong とか言われてるけど
    気にしない!
    33

    View Slide

  34. world テーブルのレコードを
    すべて取得
    34

    View Slide

  35. SELECT * FROM world
    SELECT * FROM world
    35

    View Slide

  36. SELECT * FROM world
    FROM 句
    36

    View Slide

  37. SELECT * FROM world
    FROM 句
    テーブルを指定
    37

    View Slide

  38. SELECT * FROM world
    SELECT * FROM world
    38

    View Slide

  39. SELECT * FROM world
    SELECT 句
    39

    View Slide

  40. SELECT * FROM world
    SELECT 句
    結果として取得するカラムを指定
    40

    View Slide

  41. SELECT * FROM world
    SELECT 句
    結果として取得するカラムを指定
    *(アスタリスク)はワイルドカードで
    「すべて」や「なんでも」の意味
    41

    View Slide

  42. 欲しいカラムが ‘name’ と ‘area’ なら
    42

    View Slide

  43. 欲しいカラムが ‘name’ と ‘area’ なら
    SELECT name, area FROM world
    SELECT world.name, world.area FROM world
    43

    View Slide

  44. Exercise 2 : 欲しいデータを取得 SQLZOO: 0 SELECT basics
    列の順序がアルファベット順になるような
    SQL を実行しよう
    44

    View Slide

  45. 答え合わせ
    45

    View Slide

  46. WHERE 句
    SQL

    View Slide

  47. area(面積)が日本より大きい国を探そう
    47

    View Slide

  48. area(面積)が日本より大きい国を探そう
    まず日本の面積は?
    48

    View Slide

  49. データはここにある
    49

    View Slide

  50. name はたぶん Japan
    50

    View Slide

  51. name はたぶん Japan
    SELECT * FROM world WHERE name = 'Japan'
    51

    View Slide

  52. SELECT * FROM world WHERE name = 'Japan'
    52

    View Slide

  53. SELECT * FROM world WHERE name = 'Japan'
    name が ‘Japan’ のレコードを取得
    53

    View Slide

  54. SELECT * FROM world WHERE name = 'Japan'
    54

    View Slide

  55. SELECT * FROM world WHERE name = 'Japan'
    area が 377930 より大きい国を探す
    55

    View Slide

  56. SELECT * FROM world WHERE area > 377930
    56

    View Slide

  57. SELECT * FROM world WHERE area > 377930
    area が 377930 より大きいレコードを取得
    57

    View Slide

  58. SELECT * FROM world WHERE area > 377930
    58

    View Slide

  59. SELECT * FROM world WHERE area > 377930
    条件追加:日本より人口の多い国
    59

    View Slide

  60. SELECT * FROM world WHERE name = 'Japan'
    60

    View Slide

  61. SELECT * FROM world WHERE name = 'Japan'
    area が 377930 より大きい
    かつ
    人口が 127,090,000 人より
    多い国
    61

    View Slide

  62. FROM world WHERE area > 377930 AND population > 1
    62

    View Slide

  63. インデント大事
    SELECT *
    FROM world
    WHERE area > 377930
    AND population > 127090000
    63

    View Slide

  64. インデント大事
    予約語で改行するといい感じ
    SELECT *
    FROM world
    WHERE area > 377930
    AND population > 127090000
    64

    View Slide

  65. SELECT *
    FROM world
    WHERE area > 377930
    AND population > 127090000
    65

    View Slide

  66. SELECT *
    FROM world
    WHERE area > 377930
    AND population > 127090000
    area が 377,930 より大きい
    66

    View Slide

  67. SELECT *
    FROM world
    WHERE area > 377930
    AND population > 127090000
    area が 377,930 より大きい
    かつ
    67

    View Slide

  68. SELECT *
    FROM world
    WHERE area > 377930
    AND population > 127090000
    area が 377,930 より大きい
    かつ population が 127,090,000 より大きい
    68

    View Slide

  69. SELECT *
    FROM world
    WHERE area > 377930
    AND population > 127090000
    69

    View Slide

  70. Exercise 3 : 条件指定 SQLZOO: 0 SELECT basics
    面積か人口の数字が
    日本以下の国を取得しよう
    SELECT *
    FROM world
    WHERE area > 377930
    AND population > 127090000
    Sample
    70

    View Slide

  71. 答え合わせ
    71

    View Slide

  72. ORDER BY 句
    SQL

    View Slide

  73. 日本より面積と人口が大きい国で
    GDP が最も少ない国は?
    73

    View Slide

  74. SELECT *
    FROM world
    WHERE area > 377930
    AND population > 127090000
    ORDER BY gdp
    74

    View Slide

  75. 逆に日本より面積と人口が小さい国で
    GDP が最も多い国は?
    75

    View Slide

  76. SELECT *
    FROM world
    WHERE area < 377930
    AND population < 127090000
    ORDER BY gdp DESC
    76

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  80. SELECT *
    FROM world
    ORDER BY gdp DESC,
    area,
    population DESC
    ソートするカラムは複数指定できる
    80

    View Slide

  81. Exercise 4 : ソート SQLZOO: 0 SELECT basics
    (1)もっとも人口が少ない国を探そう
    (2)面積がもっとも小さい国ベスト3を探そう
    81

    View Slide

  82. 答え合わせ
    82

    View Slide

  83. 副問い合わせ(サブクエリ)
    SQL

    View Slide

  84. ところで
    84

    View Slide

  85. SELECT *
    FROM world
    WHERE area > 377930
    AND population > 127090000
    ORDER BY gdp
    85

    View Slide

  86. SELECT *
    FROM world
    WHERE area > 377930
    AND population > 127090000
    ORDER BY gdp
    人口変わったらどうしよう
    86

    View Slide

  87. SELECT *
    FROM world
    WHERE area > 377930
    AND population > 127090000
    ORDER BY gdp
    87

    View Slide

  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

    View Slide

  89. SELECT *
    FROM world
    WHERE area > 377930
    AND population >
    (
    SELECT population
    FROM world
    )
    ORDER BY gdp
    副問い合わせが
    複数行の結果になるとエラー
    89

    View Slide

  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

    View Slide

  91. 答え合わせ
    91

    View Slide

  92. LIKE句
    SQL

    View Slide

  93. 日本みたいな国探して
    93

    View Slide

  94. 日本みたいな国探して
    94

    View Slide

  95. ‘Ja’ で始まる国を探して
    95

    View Slide

  96. SELECT *
    FROM world
    WHERE name LIKE 'Ja%'
    96

    View Slide

  97. SELECT *
    FROM world
    WHERE name LIKE 'Ja%'
    97

    View Slide

  98. SELECT *
    FROM world
    WHERE name LIKE 'Ja%'
    ワイルドカード
    98

    View Slide

  99. SELECT *
    FROM world
    WHERE name LIKE 'J%';
    SELECT *
    FROM world
    WHERE name LIKE '%n';
    SELECT *
    FROM world
    WHERE name LIKE 'J%n';
    99

    View Slide

  100. SELECT *
    FROM world
    WHERE name LIKE 'J%';
    SELECT *
    FROM world
    WHERE name LIKE '%n';
    SELECT *
    FROM world
    WHERE name LIKE 'J%n';
    クエリの区切りはセミコロン 100

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  104. 注意
    適当な LIKE 検索はスロークエリの元
    104

    View Slide

  105. インデックス
    SQL

    View Slide

  106. 辞書で何かを調べるとき
    どうやって辞書を引いてる?
    106

    View Slide

  107. 日本を調べるなら
    「に」から
    はじまるところ
    107

    View Slide

  108. 日本を調べるなら
    「に」から
    はじまるところ
    DB も同じ!
    108

    View Slide

  109. 任意のカラムにインデックスを設定すると
    検索が高速になる
    (辞書のもくじみたいなもの)
    109

    View Slide

  110. 任意のカラムにインデックスを設定すると
    検索が高速になる
    (辞書のもくじみたいなもの)
    110

    View Slide

  111. SELECT *
    FROM world
    WHERE name LIKE 'J%';
    SELECT *
    FROM world
    WHERE name LIKE '%n';
    SELECT *
    FROM world
    WHERE name LIKE 'J%n';
    111

    View Slide

  112. SELECT *
    FROM world
    WHERE name LIKE 'J%';
    SELECT *
    FROM world
    WHERE name LIKE '%n';
    SELECT *
    FROM world
    WHERE name LIKE 'J%n';
    インデックスが効く 112

    View Slide

  113. SELECT *
    FROM world
    WHERE name LIKE 'J%';
    SELECT *
    FROM world
    WHERE name LIKE '%n';
    SELECT *
    FROM world
    WHERE name LIKE 'J%n';
    インデックスが効かない 113

    View Slide

  114. SELECT *
    FROM world
    WHERE name LIKE 'J%';
    SELECT *
    FROM world
    WHERE name LIKE '%n';
    SELECT *
    FROM world
    WHERE name LIKE 'J%n';
    インデックスが前方だけ効く 114

    View Slide

  115. 注意
    手あたり次第ダメ絶対
    115

    View Slide

  116. インデックスを設定するということは
    どこかにその情報を保持しているということ
    116

    View Slide

  117. インデックスを設定するということは
    どこかにその情報を保持しているということ
    リソースを消費するし
    データを追加する際に
    インデックスの更新が必要
    117

    View Slide

  118. インデックスを設定するということは
    どこかにその情報を保持しているということ
    リソースを消費するし
    データを追加する際に
    インデックスの更新が必要
    必要なところにだけ
    インデックスを貼ろう
    118

    View Slide

  119. Exercise 6 : あいまい検索 SQLZOO: 0 SELECT basics
    国名に ‘a’ が2文字以上存在する国の中で
    もっとも gdp が高い国を探そう
    119

    View Slide

  120. 答え合わせ
    120

    View Slide

  121. IN 句
    SQL

    View Slide

  122. アジアとヨーロッパの中で
    面積トップ3はどこの国でしょう
    122

    View Slide

  123. SELECT *
    FROM world
    WHERE continent IN ('Asia', 'Europe')
    ORDER BY area DESC
    123

    View Slide

  124. SELECT *
    FROM world
    WHERE continent IN ('Asia', 'Europe')
    ORDER BY area DESC
    124

    View Slide

  125. SELECT *
    FROM world
    WHERE continent IN ('Asia', 'Europe')
    ORDER BY area DESC
    125

    View Slide

  126. 126
    SELECT *
    FROM world
    WHERE population IN
    (
    SELECT population
    FROM world
    WHERE name = 'Japan'
    )
    IN ならサブクエリの結果が
    複数行でも OK
    (このクエリ自体は微妙)

    View Slide

  127. SUM
    SQL

    View Slide

  128. 世界の全人口は?
    128

    View Slide

  129. SELECT SUM(population)
    FROM world;
    129

    View Slide

  130. Exercise 7 : 合計を求めよう SQLZOO: 0 SELECT basics
    アジアの総人口がいくつか調べよう
    130

    View Slide

  131. 答え合わせ
    131

    View Slide

  132. COUNT
    SQL

    View Slide

  133. 世界にはいくつの国がある?
    133

    View Slide

  134. SELECT COUNT(*)
    FROM world;
    134

    View Slide

  135. Exercise 8 : 数えよう SQLZOO: 0 SELECT basics
    アジアの国がいくつかあるか調べよう
    135

    View Slide

  136. 答え合わせ
    136

    View Slide

  137. JOIN 句
    SQL

    View Slide

  138. 138

    View Slide

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

    View Slide

  140. 140

    View Slide

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

    View Slide

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

    View Slide

  143. 143

    View Slide

  144. こっちは?
    144

    View Slide

  145. SELECT *
    FROM game
    JOIN eteam ON game.team1 = eteam.id
    JOIN eteam ON game.team2 = eteam.id
    145

    View Slide

  146. SELECT *
    FROM game
    JOIN eteam ON game.team1 = eteam.id
    JOIN eteam ON game.team2 = eteam.id
    146

    View Slide

  147. SELECT *
    FROM game
    JOIN eteam ON game.team1 = eteam.id
    JOIN eteam ON game.team2 = eteam.id
    147

    View Slide

  148. SELECT *
    FROM game
    JOIN eteam eteam1 ON game.team1 = eteam1.id
    JOIN eteam eteam2 ON game.team2 = eteam2.id
    148

    View Slide

  149. SELECT *
    FROM game
    JOIN eteam eteam1 ON game.team1 = eteam1.id
    JOIN eteam eteam2 ON game.team2 = eteam2.id
    エイリアス(別名)
    149

    View Slide

  150. SELECT *
    FROM game
    JOIN eteam eteam1 ON game.team1 = eteam1.id
    JOIN eteam eteam2 ON game.team2 = eteam2.id
    150

    View Slide

  151. SELECT *
    FROM game
    JOIN eteam eteam1 ON game.team1 = eteam1.id
    JOIN eteam eteam2 ON game.team2 = eteam2.id
    151

    View Slide

  152. SELECT *
    FROM game
    JOIN eteam eteam1 ON game.team1 = eteam1.id
    JOIN eteam eteam2 ON game.team2 = eteam2.id
    二重にデータが取れてるし
    そもそも正式名称あるから
    いらないよね
    152

    View Slide

  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

    View Slide

  154. 別名を省略形にすべきか否か
    154

    View Slide

  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

    View Slide

  156. 個人の意見を言います
    156

    View Slide

  157. 何も考えずに省略するのはやめよう
    157

    View Slide

  158. SELECT
    g.id,
    g.mdate,
    g.stadium,
    t1.teamname,
    t1.coach,
    t2.teamname,
    t2.coach
    158

    View Slide

  159. SELECT
    g.id,
    g.mdate,
    g.stadium,
    t1.teamname,
    t1.coach,
    t2.teamname,
    t2.coach
    省略形は脳内スタックを消費する
    159

    View Slide

  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

    View Slide

  161. コーディングでも
    「安易な省略形はやめよう」
    と言われています
    161

    View Slide

  162. KKK TNK
    MST
    何の意味か分かりますか
    IDX
    162

    View Slide

  163. SQL も同じ
    163

    View Slide

  164. コーディングの基本は
    書くときに努力して
    読むときに楽をすること
    164

    View Slide

  165. そのエイリアスが理解しやすいかどうか
    一歩踏みとどまって考えてください
    165

    View Slide

  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

    View Slide

  167. 答え合わせ
    167

    View Slide

  168. INNER / LEFT OUTER / RIGHT OUTER
    JOIN
    SQL

    View Slide

  169. 通常の JOIN は INNER JOIN
    169

    View Slide

  170. id name division
    1 John 1
    2 Jane 2
    3 Naruse 3
    id name
    1 Customer Support
    2 System
    partners divisions
    170

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  176. DISTINCT
    SQL

    View Slide

  177. player の一覧がほしい
    177

    View Slide

  178. SELECT * FROM goal ORDER BY player
    178

    View Slide

  179. SELECT * FROM goal ORDER BY player
    179

    View Slide

  180. SELECT * FROM goal ORDER BY player
    重複してる
    180

    View Slide

  181. SELECT * FROM goal ORDER BY player
    重複してる
    DISTINCT
    別個の
    181

    View Slide

  182. SELECT DISTINCT(player) FROM goal ORDER BY player
    182

    View Slide

  183. SELECT DISTINCT(player) FROM goal ORDER BY player
    183

    View Slide

  184. Exercise 10 : 組み合わせ SQLZOO: 6 JOIN
    player は全部で何人いるでしょうか
    184

    View Slide

  185. 答え合わせ
    185

    View Slide

  186. CREATE TABLE
    SQL

    View Slide

  187. データを入れるテーブルの作り方
    187

    View Slide

  188. 188

    View Slide

  189. CREATE TABLE world
    (
    name VARCHAR(20),
    continent VARCHAR(20),
    area INTEGER,
    population INTEGER,
    gdp INTEGER,
    capital VARCHAR(20),
    tld VARCHAR(4)
    ) 189

    View Slide

  190. CREATE TABLE world
    (
    name VARCHAR(20),
    continent VARCHAR(20),
    area INTEGER,
    population INTEGER,
    gdp INTEGER,
    capital VARCHAR(20),
    tld VARCHAR(4)
    )
    190

    View Slide

  191. CREATE TABLE world
    (
    name VARCHAR(20),
    continent VARCHAR(20),
    area INTEGER,
    population INTEGER,
    gdp INTEGER,
    capital VARCHAR(20),
    tld VARCHAR(4)
    )
    カラムの宣言
    191

    View Slide

  192. CREATE TABLE world
    (
    name VARCHAR(20),
    continent VARCHAR(20),
    area INTEGER,
    population INTEGER,
    gdp INTEGER,
    capital VARCHAR(20),
    tld VARCHAR(4)
    )
    カラムの型
    VARCHAR は可変長な文字列
    192

    View Slide

  193. CREATE TABLE world
    (
    name VARCHAR(20),
    continent VARCHAR(20),
    area INTEGER,
    population INTEGER,
    gdp INTEGER,
    capital VARCHAR(20),
    tld VARCHAR(4)
    )
    カラムの型
    VARCHAR は可変長な文字列
    データベースによって方言があるので注意
    193

    View Slide

  194. INSERT
    SQL

    View Slide

  195. テーブルにデータを挿入する
    195

    View Slide

  196. 196

    View Slide

  197. INSERT INTO world
    VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad', '.pk')
    197

    View Slide

  198. CONSTRAINT
    SQL

    View Slide

  199. INSERT INTO world
    VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad', '.pk')
    199

    View Slide

  200. INSERT INTO world
    VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad', '.pk')
    INSERT INTO world
    VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad')
    tld 入れ忘れた!
    200

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  205. 205

    View Slide

  206. INSERT INTO world
    VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad', '.pk')
    206

    View Slide

  207. INSERT INTO world
    VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad', '.pk')
    INSERT INTO world
    VALUES ( 'Pakistan', 'Asia', 881912, 188020000, 215117000000, 'Islamabad', '.pk')
    207

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  212. PRIMARY KEY
    SQL

    View Slide

  213. 世の中には同一性が重要なことがある
    213

    View Slide

  214. What’s 同一性?
    214

    View Slide

  215. 誕生日
    20 才 21 才
    215

    View Slide

  216. 誕生日
    20 才 21 才
    別人?
    216

    View Slide

  217. John Doe John Doe
    217

    View Slide

  218. John Doe John Doe
    同一人物?
    218

    View Slide

  219. 人が区別される理由は
    属性とは無縁なところにある
    219

    View Slide

  220. Name
    John Doe
    Jane Doe
    John Smith
    220

    View Slide

  221. Name
    John Doe
    Jane Doe
    John Smith
    John Smith さんの名字が
    Doe に変わったら?
    221

    View Slide

  222. Name
    John Doe
    Jane Doe
    John Doe
    222

    View Slide

  223. Name
    John Doe
    Jane Doe
    John Doe
    ソフトウェアシステムには
    見分けがつかない
    223

    View Slide

  224. id Name
    1 John Doe
    2 Jane Doe
    3 John Doe
    224

    View Slide

  225. id Name
    1 John Doe
    2 Jane Doe
    3 John Doe
    ソフトウェアシステムでも
    見分けがつく
    225

    View Slide

  226. CREATE TABLE user
    (
    id INTEGER PRIMARY KEY,
    name VARCHAR(20) NOT NULL
    )
    226

    View Slide

  227. CREATE TABLE user
    (
    id INTEGER PRIMARY KEY,
    name VARCHAR(20) NOT NULL
    )
    id = Identity(同一性)
    227

    View Slide

  228. CREATE TABLE user
    (
    id INTEGER PRIMARY KEY,
    name VARCHAR(20) NOT NULL
    )
    PRIMARY KEY 制約は一意であることを保証する
    228

    View Slide

  229. CREATE TABLE user
    (
    id INTEGER PRIMARY KEY,
    name VARCHAR(20) NOT NULL
    )
    PRIMARY KEY 制約は一意であることを保証する
    (NOT NULL かつ UNIQUE)
    229

    View Slide

  230. SQL ZOO
    SQL

    View Slide

  231. 時間のある限り Workshop
    231

    View Slide

  232. コメント
    SQL

    View Slide

  233. SELECT
    *
    FROM world
    WHERE area > 377930
    233

    View Slide

  234. SELECT
    *
    FROM world
    WHERE area > 377930 -- 日本の面積
    234

    View Slide

  235. 脆弱性
    SQL

    View Slide

  236. ログイン機能はどう作るか
    236

    View Slide

  237. SELECT
    *
    FROM users
    WHERE users.id = 'naruse'
    AND users.password = 'password'
    237

    View Slide

  238. SELECT
    *
    FROM users
    WHERE users.id = 'naruse'
    AND users.password = 'password'
    これでレコード読めたら OK
    238

    View Slide

  239. SELECT
    *
    FROM users
    WHERE users.id = 'naruse'
    AND users.password = 'password'
    ここはユーザの入力
    239

    View Slide

  240. SELECT
    *
    FROM users
    WHERE users.id = ''
    AND users.password = ''
    240

    View Slide

  241. SELECT
    *
    FROM users
    WHERE users.id = ''
    AND users.password = ''
    ID: naruse
    PASS: password
    241

    View Slide

  242. SELECT
    *
    FROM users
    WHERE users.id = 'naruse'
    AND users.password = 'password'
    ID: naruse
    PASS: password
    242

    View Slide

  243. SELECT
    *
    FROM users
    WHERE users.id = ''
    AND users.password = ''
    ID: naruse
    PASS: ‘ OR ‘a’ = ‘a
    243

    View Slide

  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

    View Slide

  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'
    アタック成功

    View Slide

  246. SQL インジェクション
    という脆弱性です
    246
    SELECT
    *
    FROM users
    WHERE users.id = 'naruse’
    AND users.password = '' OR 'a' = 'a'

    View Slide

  247. 防ぎ方
    247

    View Slide

  248. プレースホルダーという
    プログラミング言語に用意されている
    SQL 用の構文を利用
    DB::select(
    'SELECT * FROM users WHERE id = ? AND password = ?',
    ['naruse', 'password']
    );
    248

    View Slide

  249. OR
    249

    View Slide

  250. 許容しない文字を
    ちゃんと弾くか
    無害化する
    ID: naruse
    PASS: ‘ OR ‘a’ = ‘a
    250

    View Slide

  251. OR
    251

    View Slide

  252. そもそも SQL を
    プログラムから使わない
    252

    View Slide

  253. O/R Mapper
    SQL

    View Slide

  254. インピーダンスミスマッチ
    電気工学の分野において
    エネルギーの伝わり方が異なる媒体の
    境界にて反射や損失が発生し
    エネルギー伝送に不都合が起きる現象
    254

    View Slide

  255. インピーダンスミスマッチ
    転じて
    コンピュータシステムにおける
    異なるコンセプトにある技術的要素同士の
    ギャップを指す
    255

    View Slide

  256. インピーダンスミスマッチ
    オブジェクト指向プログラミングと
    リレーショナルデータベースの
    関係がよくある例として挙げられる
    256

    View Slide

  257. OOP
    Employee
    Name
    Age
    Division
    Position
    257

    View Slide

  258. OOP RDB
    Employee
    Name
    Age
    Division
    Position
    258

    View Slide

  259. OOP RDB
    Employee
    Name
    Age
    Division
    Position
    name age division position
    Jane 22 1 1
    employees
    259

    View Slide

  260. OOP RDB
    Employee
    Name
    Age
    Division
    Position
    name age division position
    Jane 22 1 1
    id name
    1 System Division
    employees
    divisions
    260

    View Slide

  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

    View Slide

  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

    View Slide

  263. public class MyDbContext : DbContext
    {
    public DbSet Users { get; set; }
    }
    public class User
    {
    public string Id { get; set; }
    public string Name { get; set; }
    }
    263

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  267. コードファースト
    SQL

    View Slide

  268. SQL が作れるなら
    テーブルも作れるんでない?
    268

    View Slide

  269. ソースコード
    269

    View Slide

  270. ソースコード データベース
    270

    View Slide

  271. ソースコード データベース
    テーブル
    生成
    271

    View Slide

  272. ソースコード データベース
    変更
    272

    View Slide

  273. ソースコード データベース
    テーブル
    定義変更
    変更
    273

    View Slide

  274. リードモデル
    SQL

    View Slide

  275. O/R Mapper は万能ではない
    275

    View Slide

  276. コードから SQL を生成するため
    最適化ができないときがある
    276

    View Slide

  277. そんなときは SQL を実行したり
    融通の効く薄いライブラリを使って
    最適化を行う
    277

    View Slide

  278. 判断基準は
    それが読み込み処理かどうか
    278

    View Slide

  279. 詳しくは
    CQRS
    という単語で調べてみてね
    279

    View Slide

  280. コンウェイの法則
    SQL

    View Slide

  281. 281

    View Slide

  282. 単純な読み込みなのに
    なんでこんな遅いんだ?
    282

    View Slide

  283. id product awesome del
    1 101 2 101 3 102 283

    View Slide

  284. id product awesome del
    1 101 2 101 3 102 :
    :
    というデータが数万件
    284

    View Slide

  285. id product awesome del
    1 101 2 101 3 102 :
    :
    というデータが数万件
    SELECT * FROM orders WHERE awesome LIKE '%id=¥'5¥'%'
    285

    View Slide

  286. id product awesome del
    1 101 2 101 3 102 :
    :
    というデータが数万件
    SELECT * FROM orders WHERE awesome LIKE '%id=¥'5¥'%'
    どうしてこうなったか
    286

    View Slide

  287. テーブルの定義を変更するために
    データベースを管理しているチームに
    依頼しなくてはいけなかった
    287

    View Slide

  288. テーブルの定義を変更するために
    データベースを管理しているチームに
    依頼しなくてはいけなかった
    コンウェイの法則
    288

    View Slide

  289. システムを設計する組織は、
    その構造をそっくりまねた構造の
    設計を生み出してしまう
    コンウェイの法則
    289

    View Slide

  290. おわりに
    SQL

    View Slide

  291. 今日話したことは
    最低限必要なものだけです
    291

    View Slide

  292. 他にも構文はいろいろあります
    データベースの種類ごとに特性もあります
    292

    View Slide

  293. 試行錯誤と学習を繰り返して
    プロダクトに最適なものを
    探していってください
    293

    View Slide

  294. Auther Masanobu Naruse
    HomePage https://nrslib.com
    Twitter @nrslib

    View Slide