はてなリモートインターン2021 RDBMSブートキャンプ 講義資料

https://hatenacorp.jp/recruit/intern/2021

Transcript

1. 3%#.4ه٭ع؞ٔ٤و
   
   

   View Slide

2. 㡎״מ
   ه٭ع؞ٔ٤وמחַי
   ˝ 䏲ⶐق٭عך㍭׼םַ׆כ؅泘氳מ
   ˝ 哭䒝־׼ךעםׂյ⪮⛮❆׷طؠؼشؠ╈䑏מ
   ˝ 磵啶껮ׄ餉妳⽏םסךյ䏲ך鐧׬׾כז־־׽ס┉חמ׊י׵
   ׼ֻ׾כ䇞ַך׌
   
   

   View Slide

3. 㡎״מ
   ׆ס铺紶ךע3%#.4מחַי
   ˝ עיםךע.Z42-כַֹ%#.4؅׻ׂ✳זיַױ׌
   ˝ 䏲ⶐق٭عס嚀耆ꝧ氦ך.Z42-؅⹦׽䣽ֹכםזג꤀յ־׼
   鐧׬׾׆כֿםַ׻ֹמ
   
   

   View Slide

4. ☪仼סإ٭ٜ
   .Z42-؅ם؆כםׂ䣽ֻ׾׻ֹמם׾
   ˝ .Z42-מظ٭ذ؅劲硯⭳全׾׻ֹמם׾
   ˝ .Z42-מظ٭ذ؅⟊㰆٬⹆拨٬催二٬⯡ꢜֿ⭳全׾
   ˝ ط٭هٜ錃銶ס縒ֻ亠כقنؚ٭ُ٤تֿם؆כםׂ؂־׾
   
   

   View Slide

5. ׆ס铺紶ך䣽ֻםַ׆כ
   ˝ ظ٭ذي٭تס☼磝ײ
   ˝ 鎋׊ַط٭هٜ錃銶׷42-סطؠؼشؠ
   ˝ قنؚ٭ُ٤تס鎋׊ַ鎇
   ˝ .Z42-♓㛙ס׆כ
   
   

   View Slide

6. 泘域
   ˝ 㡎״מ
   ˝ 3%#.4מחַי
   ˝ 㓹儖稴
   ˝ 䑴榫稴
   ˝ ♀ꎰ
   
   

   View Slide

7. 3%#.4מחַי
   
   

   View Slide

8. 3%#.4כע
   3%#.43FMBUJPOBM%BUBCBTF.BOBHFNFOU4ZTUFN
   ˝ 3FMBUJPOBM%BUBCBTF
   ˝ ꞊➟ظ٭ذي٭ت
   ˝ %BUBCBTF.BOBHFNFOU4ZTUFN
   ˝ ظ٭ذي٭ت畘杼بتطّ
   ꞊➟ظ٭ذي٭تסظ٭ذي٭ت畘杼بتطّ
   
   

   View Slide

9. ꞊➟ظ٭ذي٭ت
   
   ꞊➟ٓظٜמ㓹טַגظ٭ذي٭ت
   ˝ ꞊➟ٓظٜכע
   ˝ ظ٭ذ؅꞊➟כ׊י辐杯׊⹦׽䣽ֹظ٭ذס辐杯亠嫎
   ˝ 釤ג泘כ׊יע辐 ط٭هٜ
   ס䎬䌋ךظ٭ذ؅辐杯׌׾
   
   

   View Slide

10. ꞊➟ظ٭ذي٭ت
    
    
    

    View Slide

11. ꞊➟ظ٭ذي٭ت
    
    
    

    View Slide

12. ꞊➟ظ٭ذي٭ت
    
    
    

    View Slide

13. ظ٭ذي٭ت畘杼بتطّ
    %#.4ס䏔Ⱏ .Z42-דכ
    
    ˝ ظ٭ذ؅䥩霄⴫׊յⲖ曍׻ׂ⟊㰆
    ˝ ظ٭ذ؅✳ֹ☔עظ٭ذֿלס׻ֹמ劲硯׈׿יַ׾־䙫閁׊םׂי׵ַַ
    ˝ 榫鵍מֵ؂׎י僃鸵ם啶鵰ךظ٭ذ؅⟊㰆׌׾
    ˝ ┾⮛ؓؠجت
    ˝ ⺱侇מ傴׀鱮ײ鐆ײ鱮ײֿ氦榟׊ג侇מ룲뤕ֿ颯׀םַ׻ֹמך׀׾
    ˝ ظ٭ذ䮇㜊؅ꡔ׃
    ˝ ظ٭ذ؅傴׀鱮؆ךַ׾鵍╈ך%#ئ٭ف٭ֿ蛽הג㕙⺬յ傴׀鱮ײֿ╈鵍ⶐ珷מ磙؂׾⺎耆䓪׵ֵ׾
    ˝ אַֹזג꤀סٛ؜فٛ٭ס☼磝ײ؅䧏זיַ׾
    ̜ظ٭ذ؅㔋擱מ䣽ַגַ
    
    

    View Slide

14. 㓹儖稴
    
    

    View Slide

15. 㓹儖稴
    42-מ׻׾
    ˝ ظ٭ذ㲊紶
    ˝ ظ٭ذ䳩⛼
    
    

    View Slide

16. 榫鏤丝杼
    
    榫鏤 䙫⽏
    ط٭هٜ ظ٭ذס꥗⺬
    ٝؤ٭غ א׿ב׿סط٭هٜסظ٭ذס磝车车
    ؜ّٚ ط٭هٜסظ٭ذס㷯䓪
    ت؞٭ُ ط٭هٜס啶鵰 ל؆ם؜ֵֿّٚ׾
    ־յ؜ّٚמעל؆ם⡁ֿ⪌׾־յ
    FUD
    
    
    

    View Slide

17. 榫鏤丝杼
    
    
    

    View Slide

18. 榫鏤丝杼
    
    
    

    View Slide

19. 42-
    3%#.4מゖַ⺬؂׎ 䳩⛼
    ؅车ֹג״ס銧鏤
    ˝ %%- %BUB%FOJUJPO-BOHVBHF
    
    ˝ ت؞٭ُ؅㲊紶㚺催׌׾42-
    ˝ %.- %BUB.BOJQVMBUJPO-BOHVBHF
    
    ˝ ظ٭ذ؅䳩⛼׌׾42-
    ˝ %$- %BUB$POUSPM-BOHVBHF
    
    ˝ ظ٭ذ׫סؓؠجتמ꞊׌׾42-
    ˝ ׆ס铺紶ךע磆☭׊םַ
    
    

    View Slide

20. ظ٭ذ㲊紶
    
    

    View Slide

21. ظ٭ذ㲊紶
    ˝ CREATE TABLE乃
    ˝ ظ٭ذ㑔
    ˝ ⯆硜
    
    

    View Slide

22. $3&"5&5"#-&乃
    ˝ ط٭هٜ؅㲊紶׌׾啶乃
    ˝ CREATE TABLE ςʔϒϧ໊ (ςʔϒϧఆٛ);
    ˝ .Z42-.Z42-ٛنؒٝ٤تُؼٖٜؓ$3&"5&5"#-&啶乃
    !" `!"` ͸ίϝϯτ
    !" id,name,created_atΧϥϜΛؚΉςʔϒϧΛɺusersͱ͍͏໊લͰఆ͍ٛͯ͠Δ
    CREATE TABLE `users` (
    `id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(32) NOT NULL,
    `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`name`)
    );
    
    

    View Slide

23. ظ٭ذ㑔
    
    

    View Slide

24. ظ٭ذ㑔
    ˝ ؜ّٚסظ٭ذס㑔
    ˝ 㑔מ׻זיظ٭ذ⟊㰆侇מ־־׾㳊ꄈֿ樟ם׾סך磵啶㛻◄
    ˝ 䏲־׼㚺ֻ׾ס׵㛻㚺דזג׽׌׾
    ˝ ׻ׂ✳ֹ׵סמחַי磆☭
    ˝ 丗⡁㑔
    ˝ 乃㰄⮛㑔
    ˝ 仼♀כ侇ꝴ㑔
    
    

    View Slide

25. 丗⡁㑔
    
    ˝ 丝丗
    ˝ INTEGER INT TINYINT BIGINT
    ˝ UNSIGNED㷯䓪؅♀┙׌׾כ瑞⺘扛׊ך⟊㰆ך׀׾׻ֹמם׾ UNSIGNED BIGINT
    
    ˝ 㷯䓪؜ّٚס攐䓪նظ٭ذ㑔夵מ樟ם׾㷯䓪ֵֿ׾
    ˝ 宯Ⳃ㵸丗憠
    ˝ FLOAT DOUBLE
    ˝ ㍾㲊㵸丗憠
    ˝ DECIMAL NUMERIC
    
    

    View Slide

26. 丗⡁㑔
    
    ˝ 劼ֵר׿מ妳؅חׄ׾䑒釐ֵֿ׾
    ˝ ❆INT㑔ע-2147483648־׼2147483647ס疗㍱؅⟊㰆ך׀
    ׾
    ˝ ׊־׊⧠ٝؤ٭غע勅㛙癨ⶡמ⮼鷼ך׀׾
    ˝ id 鵽樑מ✳ֹ؜ّٚ
    םלעUNSIGNED BIGINTמ׊יֽׂכ㱦䑏
    ˝ 18446744073709551615 ◹
    ױך⟊㰆ך׀׾
    
    

    View Slide

27. 乃㰄⮛㑔
    ˝ 㛡ׂס㕙⺬ךVARCHAR(n)؅✳ֹ
    ˝ ⺎㚺Ꝏ乃㰄⮛ך0 ~ 65,535 byte⟊㰆⺎耆
    ˝ name VARCHAR(32)עրname؜ّٚע乃㰄⮛㑔ךյ乃㰄
    ⟊㰆⺎耆ցכַֹ䙫⽏
    ˝ ֵ׾爊䈱Ꝏַ乃㰄⮛؅⟊㰆׌׾כ׀מעTEXT؅⮵榫
    ˝ TEXT MEDIUMTEXT LONGTEXT
    
    

    View Slide

28. 仼♀כ侇ꝴ㑔
    
    ˝ DATETIME
    ˝ 仼♀כ侇ꝴס┸亠؅⻠׳
    ˝ ئَ٭ع׌׾疗㍱ע1000-01-01 00:00:00־׼9999-12-31 23:59:59
    ˝ ذّؕد٭٤ס哭䒝ֿםַ
    ˝ TIMESTAMP
    ˝ 仼♀כ侇ꝴס┸亠؅⻠׳
    ˝ ئَ٭ع׌׾疗㍱ע1970-01-01 00:00:01ס65$־׼2038-01-19 03:14:07ס65$
    ˝ ذّؕد٭٤ס哭䒝ֵֿ׾
    
    

    View Slide

29. 仼♀כ侇ꝴ㑔
    
    ˝ DATETIMEכTIMESTAMPע鷿ַ؅䙫閁׊י✳ַױ׊׺ֹ
    ˝ .Z42-.Z42-ٛنؒٝ٤تُؼٖٜؓ%"5&յ
    %"5&5*.&յֽ׻צ5*.&45".1㑔
    ˝ TIMESTAMPע䇗ױך׊־✳ֻםַ
    ˝ ذّؕد٭٤ס哭䒝ֿ扛ַ亠ֿ䣽ַ׷׌ַ㕙ꪫֿ㛡ַ
    ˝ TIMESTAMP؅✳ֹ꤀յ䙫㍲׊םַذّؕد٭٤؅䧗㲊׊י׊ױ
    ֹכ㍭׾
    
    

    View Slide

30. ⯆硜
    
    

    View Slide

31. ⯆硜כע
    ˝ ؜ّٚמ⪌׾ظ٭ذמ㵚׌׾⯆꡾
    ˝ 䙫㍲׊םַظ٭ذֿ⪌׽鱮ױםַ׻ֹמ
    ˝ ⯆硜מ⹚׌׾כؙٚ٭מם׾
    !" σʔλ௥ՃͷSQL
    !" ੒ޭ
    mysql> INSERT INTO users SET id = 1, name = 'miki_bene';
    Query OK, 1 row affected (0.01 sec)
    !" ಉ͡ id ͷ஋ΛೖΕΑ͏ͱ͢ΔͱΤϥʔ
    mysql> INSERT INTO users SET id = 1, name = 'miki_bene2';
    ERROR 1062 (23000): Duplicate entry '1' for key 'users.PRIMARY'
    
    

    View Slide

32. ⯆硜❆ ☽מ׵ֵ׾
    
    ⯆硜⺲ ⫂㳊
    PRIMARY KEY⯆硜 ؜ّٚס⡁ֿط٭هٜ⫂ךٝؤ٭غ؅┉䙫
    מ閁⮯ך׀׾׆כ؅澬㲊
    UNIQUE⯆硜 ؜ّٚס⡁ֿط٭هٜ⫂ך┉䙫ךֵ׾׆כ
    ؅澬㲊 גד׊NULL͸͋Δ
    
    NOT NULL⯆硜 ؜ّٚס⡁ֿNULLךעםַ׆כ؅澬㲊
    FOREIGN KEY⯆硜 邾丗סط٭هٜꝴך丝⺬䓪ֿ⹦׿יַ׾׆
    כ؅澬㲊
    
    

    View Slide

33. 13*."3:,&: ╚؞٭
    
    ˝ ط٭هٜ⫂ךٝؤ٭غ؅┉䙫מ閁⮯ך׀׾؜ّٚ
    ˝ חסط٭هٜךח䧗㲊ך׀׾
    ˝ PRIMARY KEYס؜ّٚע域ס⯆硜؅嵹ג׈םַכַׄםַ
    ˝ ☽סٝؤ٭غכꄆ邾׊יעַׄםַ UNIQUE⯆硜
    
    ˝ ⡁ֿםׄ׿ףם׼םַ NOT NULL⯆硜
    
    ˝ PRIMARY KEYעؕ٤ظشؠت 䏲鳭
    כ׊י׵✳ֻ׾
    
    

    View Slide

34. ䷉״יusersط٭هٜס㲊紶
    CREATE TABLE `users` (
    `id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(32) NOT NULL,
    `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`name`)
    );
    ˝ usersכַֹ⺲⯥סط٭هٜ؅㲊紶׊յ⺨؜ّٚע
    ˝ idױךס丝丗ס⡁ֿ⪌׽յNULLךםַ־חظ٭ذֿꄆ邾׌׾׆כֿםַ
    ˝ name僃㛻乃㰄ס乃㰄⮛ס⡁ֿ⪌׽յNULLךםַ־חظ٭ذֿꄆ邾׌׾׆כֿםַ
    ˝ created_at1000-01-01 00:00:00־׼9999-12-31 23:59:59ױךסذّؕد٭٤ס扛ַ侇⯍ס⡁ֿ⪌׽յ
    NULLךעםַ
    
    

    View Slide

35. ظ٭ذ䳩⛼
    
    

    View Slide

36. $36%
    ˝ ظ٭ذ䳩⛼׌׾꤀ס㓹儖嚀耆
    ˝ 域ס꯽乃㰄
    ˝ $SFBUF 傴׀鱮ײ
    
    ˝ 3FBE ⹆拨
    
    ˝ 6QEBUF 催二
    
    ˝ %FMFUF ⯡ꢜ
    
    
    

    View Slide

37. ♓꡸ע♓┖סط٭هֵֿٜ׾⯥䬠ך
    !" ϢʔβʔΛද͢ςʔϒϧ
    CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(32) NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (`id`)
    );
    !" ϒϩάΛද͢ςʔϒϧɻ user_id ͸ϒϩάΛ͍࣋ͬͯΔϢʔβʔͷid
    CREATE TABLE blogs (
    id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(50) NOT NULL,
    description VARCHAR(512),
    PRIMARY KEY (id)
    );
    
    

    View Slide

38. */4&35乃 $SFBUF
    
    ٝؤ٭غ؅鴑ⱶ׌׾*/4&35乃
    !" users ςʔϒϧʹ (1, "͸ͯͳଠ࿠", "2021-08-17 12:00:00") ͷϨίʔυΛ௥Ճ
    mysql> INSERT INTO users SET id = 1, name = "͸ͯͳଠ࿠", created_at = "2021-08-17 12:00:00";
    Query OK, 1 row affected (0.01 sec)
    !" ผͷॻ͖ํͰ
    mysql> INSERT INTO users (id, name, created_at) VALUES (2, "͸ͯͳೋ࿠", "2021-08-17 13:00:00");
    Query OK, 1 row affected (0.01 sec)
    .Z42-.Z42-ٛنؒٝ٤تُؼٖٜؓ*/4&35啶
    乃
    
    

    View Slide

39. 4&-&$5乃 3FBE
    
    ط٭هٜ־׼ٝؤ٭غ؅吾碟׌׾4&-&$5乃
    !" usersςʔϒϧͷϨίʔυΛશ݅ݕࡧ(औಘ)
    mysql> SELECT * FROM users;
    (݁Ռলུ)
    !" ߜΓࠐΈʹ͸ WHERE ۟Λ෇͚Δ(ޙड़)
    !" id ͕ 1 ͷϨίʔυΛݕࡧ
    mysql> SELECT * FROM users WHERE id = 1;
    +----+-----------+---------------------+
    | id | name | created_at |
    +----+-----------+---------------------+
    | 1 | ͸ͯͳଠ࿠ | 2021-08-17 12:00:00 |
    +----+-----------+---------------------+
    1 row in set (0.00 sec)
    .Z42-.Z42-ٛنؒٝ٤تُؼٖٜؓ4&-&$5啶乃
    
    

    View Slide

40. 61%"5&乃 6QEBUF
    
    仴מ㰆㏇׌׾ٝؤ٭غ؅催二׌׾61%"5&乃
    !" ߋ৽͍ͨ͠Ϩίʔυ͸ WHERE ۟Ͱࢦఆ͢Δ
    mysql> UPDATE users SET name = 'miki_bene' WHERE id = 1;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1 Changed: 1 Warnings: 0
    mysql> SELECT * FROM users WHERE id = 1;
    +----+-----------+---------------------+
    | id | name | created_at |
    +----+-----------+---------------------+
    | 1 | miki_bene | 2021-08-17 12:00:00 |
    +----+-----------+---------------------+
    1 row in set (0.01 sec)
    !" ஫ҙ! WHERE ۟Ͱࢦఆ͠ͳ͍৔߹͸શ݅ߋ৽ର৅ʹͳΔ
    mysql> UPDATE users SET name = "miki_bene";
    .Z42-.Z42-ٛنؒٝ٤تُؼٖٜؓ61%"5&啶乃
    
    

    View Slide

41. %&-&5&乃 %FMFUF
    
    ٝؤ٭غ؅⯡ꢜ׌׾
    mysql> SELECT * FROM users WHERE id = 1;
    +----+-----------------+---------------------+
    | id | name | created_at |
    +----+-----------------+---------------------+
    | 1 | ͸ͯͳଠ࿠ | 2021-08-17 12:00:00 |
    +----+-----------------+---------------------+
    1 row in set (0.01 sec)
    !" ࡟আ͍ͨ͠Ϩίʔυ͸WHEREͰࢦఆ͢Δ
    mysql> DELETE FROM users WHERE id = 1;
    Query OK, 1 row affected (0.01 sec)
    mysql> SELECT * FROM users WHERE id = 1;
    Empty set (0.00 sec)
    !" ஫ҙ! WHERE ۟Ͱࢦఆ͠ͳ͍ͱશ݅࡟আͷର৅ʹͳΔ
    mysql> DELETE FROM users;
    .Z42-.Z42-ٛنؒٝ٤تُؼٖٜؓ%&-&5&啶乃
    
    

    View Slide

42. ظ٭ذס⹦䐂亠嫎
    ˝ 吾碟兢♭WHERE⺀
    ˝ خ٭عORDER BY⺀
    ˝ ً٭ة٤ءLIMIT⺀OFFSET⺀
    ˝ ءٜ٭م٤ءGROUP BY
    ˝ ط٭هٜ⺱㚖ס磵⺬JOIN
    
    

    View Slide

43. 吾碟兢♭8)&3&⺀
    WHERE⺀ך兢♭؅礓׽鱮؆ך㵚霄؅吾碟ך׀׾
    ˝ ׆׆ךע׻ׂ✳ֹ׵ס؅╈䑏מ磆☭
    ˝ .Z42-.Z42-ٛنؒٝ٤تُؼٖٜؓ䌋ס啶乃
    
    

    View Slide

44. 奂鬳
    42-מַֽי equalע= not equalע!"
    وٞءِٚ٤ء銧鏤ס丗⡁׷乃㰄⮛ס׻ֹמ > < !# !$ך奂鬳ך׀׾
    !" id͕1ͷϨίʔυΛݕࡧ
    SELECT * FROM users WHERE id = 1;
    !" id͕1Ͱ͸ͳ͍ϨίʔυΛݕࡧ
    SELECT * FROM users WHERE id !# 1;
    !" created_at ͕ 2021-08-17 12:00:00 ΑΓେ͖͍ϨίʔυΛݕࡧ
    SELECT * FROM users WHERE created_at > '2021-08-17 12:00:00';
    
    

    View Slide

45. ANDORIN
    ˝ AND׷ORך兢♭؅磝ײ⺬؂׎׾׆כֿך׀׾
    ˝ ⺱׋؜ّٚמ㵚׌׾ORע INךױכ״י辐杯׌׾׆כ׵ך׀׾
    !" created_at ͕ 2021-08-17 12:00:00 ΑΓେ͖͍ϨίʔυΛݕࡧ
    !" name͕"͸ͯͳ"͔Β࢝·ΔϨίʔυΛݕࡧ
    !" `LIKE`͸จࣈྻͷ෦෼Ұக(`%`͕ϫΠϧυΧʔυͰ, ೚ҙͷ0จࣈҎ্ͷจࣈྻʹͳΔ)
    SELECT * FROM users WHERE created_at > '2021-08-17 12:00:00' AND name LINE '͸ͯͳ%';
    !" id͕1͔2͔3ͷϨίʔυΛݕࡧ
    SELECT * FROM users WHERE id = 1 OR id = 2 OR id = 3;
    !" ্ͷSQLΛINΛ࢖ͬͯ
    SELECT * FROM users WHERE id IN (1, 2, 3);
    
    

    View Slide

46. خ٭ع 03%&3#:⺀
    
    ˝ WHERE⺀ס䏲מORDER BY ΧϥϜ໊ ॱং؅♀ׄיյ吾碟磵冽סꯂ䈋؅䧗㲊ך׀׾خ٭ع
    ֿ⭳全׾
    ˝ DESCע꡸ꯂASCע伭ꯂ ORDER BY⺀؅泸樋׊ג㕙⺬עASC
    
    !" users ͷશϨίʔυΛ created_at ͷ߱ॱͰݕࡧ
    mysql> SELECT * FROM users ORDER BY created_at DESC;
    +----+-----------------+---------------------+
    | id | name | created_at |
    +----+-----------------+---------------------+
    | 2 | ͸ͯͳೋ࿠ | 2021-08-17 13:00:00 |
    | 1 | ͸ͯͳଠ࿠ | 2021-08-17 12:00:00 |
    +----+-----------------+---------------------+
    2 rows in set (0.00 sec)
    !" title ʹ"೔ه"ؚ͕·ΕΔϨίʔυΛɺid ͷঢॱͰݕࡧ
    mysql> SELECT * FROM blogs WHERE title LIKE '%೔ه%' ORDER BY id ASC;
    +----+---------+----------------------------------------+----------------------------------------------------------------------+
    | id | user_id | title | description |
    +----+---------+----------------------------------------+----------------------------------------------------------------------+
    | 1 | 1 | Ұ࿠ͷMySQL೔ه | Ұ࿠ͷMySQLͷษڧه࿥Λ࢒͢ϒϩάͰ͢ |
    | 2 | 2 | ೋ࿠ͷDBεϖγϟϦετ೔ه | ೋ࿠ͷDBεϖγϟϦετʹͳΔͨΊͷه࿥ϩάͰ͢ |
    +----+---------+----------------------------------------+----------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    

    View Slide

47. ً٭ة٤ء -*.*5⺀0''4&5⺀
    
    ˝ LIMIT⺀ע吾碟磵冽ס⹦䐂♭丗؅䧗㲊ך׀׾
    ˝ OFFSET⺀ע䧗㲊׊ג⡁דׄ吾碟磵冽؅鐆ײ괊ף׊יׂ׿׾
    ˝ ׆׿؅磝ײ⺬؂׎יً٭ة٤ء嚀耆؅㲔杯ך׀׾
    !" usersςʔϒϧͷશϨίʔυ͔Βcreated_atͷ߱ॱͰݕࡧ͠ɺ3ϨίʔυಡΈඈ͹্ͨ͠Ͱ3݅ͷݕࡧ݁ՌΛಘΔ
    !" = 1ϖʔδ3݅·Ͱදࣔ͢Δ
    !" 1ϖʔδ໨
    SELECT * FROM users ORDER BY created_at LIMIT 3 OFFSET 0;
    !" 2ϖʔδ໨
    SELECT * FROM users ORDER BY created_at LIMIT 3 OFFSET 3;
    !" 3ϖʔδ໨
    SELECT * FROM users ORDER BY created_at LIMIT 3 OFFSET 6;
    
    

    View Slide

48. ً٭ة泘
    SELECT * FROM users ORDER BY created_at LIMIT 3 OFFSET 0;
    48
    

    View Slide

49. ً٭ة泘
    SELECT * FROM users ORDER BY created_at LIMIT 3 OFFSET 3;
    49
    

    View Slide

50. ً٭ة泘
    SELECT * FROM users ORDER BY created_at LIMIT 3 OFFSET 6;
    50
    

    View Slide

51. ءٜ٭م٤ء (3061#:
    
    
    ˝ GROUP BYךյظ٭ذ؅ءٜ٭م٤ءך׀׾
    !" Ϣʔβʔ͕ϒϩάΛ͍͍ͭ࣋ͬͯ͘Δͷ͔Λूܭɻuser_id ຖʹ blogs ςʔϒϧͷϨίʔυΛάϧʔϐϯά͠ɺͦͷ਺Λग़͢
    !" COUNT(*)͸ͦͷ৚݅ʹ߹க͢ΔϨίʔυͷ਺Λදࣔ͢Δ
    !" AS͸ผ໊Λ༩͑Δ͜ͱ͕Ͱ͖Δ
    mysql> SELECT user_id, COUNT(*) AS blog_count FROM blogs GROUP BY user_id;
    +---------+------------+
    | user_id | blog_count |
    +---------+------------+
    | 1 | 1 |
    | 2 | 2 |
    +---------+------------+
    2 rows in set (0.00 sec)
    
    

    View Slide

52. ءٜ٭م٤ء (3061#:
    
    
    
    

    View Slide

53. ط٭هٜ⺱㚖ס磵⺬ +0*/
    
    
    JOINךյ樟ם׾ط٭هٜמ⟊㰆׈׿גظ٭ذ؅磵⺬ך׀׾
    !" users ςʔϒϧͱ blogs ςʔϒϧΛ݁߹ͯ͠ɺϒϩάͷ࡞ऀͱλΠτϧͷҰཡΛग़͢
    mysql> SELECT users.name AS user_name, blogs.title AS blog_title FROM blogs
    !# JOIN users
    !# ON blogs.user_id = users.id
    !# WHERE users.id = 2;
    +-----------------+----------------------------------------+
    | user_name | blog_title |
    +-----------------+----------------------------------------+
    | ͸ͯͳೋ࿠ | ೋ࿠ͷDBεϖγϟϦετ೔ه |
    | ͸ͯͳೋ࿠ | ೋ࿠೔ৗϩά |
    +-----------------+----------------------------------------+
    2 rows in set (0.00 sec)
    RIGHT JOIN٬INNER JOIN٬OUTER JOINםלס磵⺬亠嫎׵ֵ׾
    
    

    View Slide

54. ط٭هٜ⺱㚖ס磵⺬ +0*/
    
    
    ˝ blogs JOIN usersCMPHTط٭هٜמVTFSTط٭هٜ؅磵⺬׈׎׾
    ˝ ON blogs.user_id = users.idCMPHTVTFS@JEכ┉舙׌׾⡁סVTFSTJE؅䧏חٝؤ٭غ؅磵⺬׈׎׾
    
    

    View Slide

55. 䑴榫稴
    
    

    View Slide

56. 䑴榫稴
    ׆׿׼ס׈؂׽؅䣽ֹ
    ˝ ط٭هٜ錃銶ס縒ֻ亠
    ˝ قنؚ٭ُ٤تס鎇
    
    

    View Slide

57. ط٭هٜ錃銶ס縒ֻ亠
    
    

    View Slide

58. هٞءئ٭لت؅❆מ縒ֻיײ׾
    ˝ ٗ٭ا٭ַֿ׾
    ˝ ٗ٭ا٭ע⺲⯥؅䧏ח
    ˝ ٗ٭ا٭עهٞء؅䧏י׾
    ˝ هٞءמעذؕعֿٜ♀ׂ
    ˝ هٞءמע鐄伺乃ֿ傴ׄ׾
    ˝ ٗ٭ا٭עهٞء؅邾丗䧏י׾
    ל؆םط٭هٜ 辐
    ך辐׎׾ד؀ֹ־
    
    

    View Slide

59. ⪒鼧┉חסط٭هٜ 辐
    ך傴ַיײ׾
    
    

    View Slide

60. ׆׿ך׵⭳全׾זה׶⭳全׾ׄל˟˟
    ˝ ٗ٭ا٭⺲ס䗯㕔ֿꄆ邾׊יױ׌נ
    ˝ 鷿⽰䚉סֵ׾ؠؙٛמםזג׽׊םַ
    ˝ FYٗ٭ا٭⺲┉鈋؅⹦䐂׌׾ג״מע
    SELECT UNIQUE user_name FROM table_nameכַֹؠؙٛמ
    ˝ ┉鈋؅⹦׽גַדׄםסמUNIQUEֿ䑒釐םס
    ˝ ☪ס☼喋ךע׆׿דׄדׄלյ䣽ֹ䗯㕔ֿ㘃ֻג׼לֹ׌׾
    ˝ FYهٞءמ鋗◄؅邾丗䧏ג׎גַ
    ˝ ؜ّٚ鴑ⱶ׊ג׼׆סط٭هٜך׵㲔杯ך׀׾ֿյ鋗◄ס丗דׄٝؤ٭غֿ⛼׼׿յאס
    ⮆ٗ٭ا٭٬هٞءסظ٭ذסꄆ邾ֿ㘃ֻ׾
    
    

    View Slide

61. 䣽ֹ䗯㕔מ浓泘׊יط٭هٜ؅⮆Ⱏ
    ˝ րٗ٭ا٭ցכրهٞءցמ浓泘׊יյא׿ב׿؅ط٭هٜך
    辐杯׌׾
    ˝ usersط٭هٜכblogsط٭هٜמ⮆Ⱏ׌׾
    ˝ ط٭هٜמ车؅┉䙫מ攐㲊ך׀׾؞٭؅鴑ⱶ׌׾
    ˝ ط٭هٜ⺱㚖ֿ꞊鵽؅䧏י׾׻ֹמ׌׾
    
    

    View Slide

62. ط٭هٜ؅⮆Ⱏ׊יַׂ
    
    

    View Slide

63. ط٭هٜמ车؅攐㲊ך׀׾؞٭؅鴑ⱶ׌׾
    
    

    View Slide

64. ط٭هٜ⺱㚖ֿ꞊鵽؅䧏י׾׻ֹמ׌׾
    
    

    View Slide

65. ٗ٭ا٭כهٞءס꞊➟؅㍲מ׊יײ׾
    
    

    View Slide

66. ٗ٭ا٭כهٞءס꞊➟؅㍲מ׊יײ׾
    ˝ usersכblogsע┉㵚㛡ס꞊➟
    ˝ 㛡סط٭هٜמյ┉סط٭هٜסٝؤ٭غ؅┉䙫מ攐㲊׌׾؜
    ّٚס⡁؅䧏ג׎׿ףյ┉㵚㛡ס꞊➟؅辐׎׾
    ˝ 浓泘׌׾䗯㕔ס꞊➟؅㍲מ׊יײ׾כ䨿ֻ׷׌ַ
    ˝ &3㍲(PPHMF吾碟
    
    

    View Slide

67. 42- $3&"5&5"#-&
    
    CREATE TABLE `users` (
    `id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(32) NOT NULL,
    PRIMARY KEY (`id`)
    );
    CREATE TABLE `blogs` (
    `id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `title` VARCHAR(254) NOT NULL,
    `description` VARCHAR(512) NOT NULL,
    PRIMARY KEY (`id`)
    );
    
    

    View Slide

68. 42- */4&35
    
    INSERT INTO users (id, name) VALUES
    (1, "͸ͯͳҰ࿠"),
    (2, "͸ͯͳೋ࿠");
    INSERT INTO blogs (id, user_id, title, description) VALUES
    (1, 1, "Ұ࿠ͷMySQL೔ه", "Ұ࿠ͷMySQLͷษڧه࿥Λ࢒͢ϒϩάͰ͢"),
    (2, 2, "ೋ࿠ͷDBεϖγϟϦετ೔ه", "ೋ࿠ͷDBεϖγϟϦετʹͳΔͨΊͷه࿥ϩάͰ͢"),
    (3, 2, "ೋ࿠೔ৗϩά", "ೋ࿠ͷ೔ৗΛ࢒͢ϒϩάͰ͢ɻٕज़ϒϩά͸ͪ͜Β→""#");
    
    

    View Slide

69. 42- 4&-&$5
    
    !" name ͕ "͸ͯͳೋ࿠" ͷ users ͷϨίʔυΛऔಘ
    mysql> SELECT * FROM users WHERE name = "͸ͯͳೋ࿠";
    +----+-----------------+
    | id | name |
    +----+-----------------+
    | 2 | ͸ͯͳೋ࿠ |
    +----+-----------------+
    1 row in set (0.00 sec)
    !" user_id ͕ 2 ͷ blogs ͷϨίʔυΛऔಘ
    mysql> SELECT * FROM blogs WHERE user_id = 2;
    +----+---------+----------------------------------------+-----------------------------------------------------------------------------+
    | id | user_id | title | description |
    +----+---------+----------------------------------------+-----------------------------------------------------------------------------+
    | 2 | 2 | ೋ࿠ͷDBεϖγϟϦετ೔ه | ೋ࿠ͷDBεϖγϟϦετʹͳΔͨΊͷه࿥ϩάͰ͢ |
    | 3 | 2 | ೋ࿠೔ৗϩά | ೋ࿠ͷ೔ৗΛ࢒͢ϒϩάͰ͢ɻٕज़ϒϩά͸ͪ͜Β→!!$ |
    +----+---------+----------------------------------------+-----------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    

    View Slide

70. 42- +0*/
    
    !" JOIN ͯ͠"͸ͯͳೋ࿠"ͷ࠷ॳͷදΛ࡞Δ
    mysql> SELECT users.name AS user_name, blogs.title AS blog_title, blogs.description AS blog_description FROM blogs
    JOIN users
    ON blogs.user_id = users.id
    WHERE users.id = 2;
    +-----------------+----------------------------------------+-----------------------------------------------------------------------------+
    | user_name | blog_title | blog_description |
    +-----------------+----------------------------------------+-----------------------------------------------------------------------------+
    | ͸ͯͳೋ࿠ | ೋ࿠ͷDBεϖγϟϦετ೔ه | ೋ࿠ͷDBεϖγϟϦετʹͳΔͨΊͷه࿥ϩάͰ͢ |
    | ͸ͯͳೋ࿠ | ೋ࿠೔ৗϩά | ೋ࿠ͷ೔ৗΛ࢒͢ϒϩάͰ͢ɻٕज़ϒϩά͸ͪ͜Β→!!$ |
    +-----------------+----------------------------------------+-----------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    

    View Slide

71. ׆׆ך頇鐆嚀耆ס鴑ⱶ؅縒ֻיײ׾
    ˝ ٗ٭ا٭עهٞء؅頇鐆ך׀׾
    ˝ ❆րעים┉鼂ցסٗ٭ا٭עր◅鼂ס%#تًبٔٛتع仼
    鋗ցր◅鼂仼䅻ٞءցסهٞء؅頇鐆ך׀׾
    ˝ 4/4ךַֹրنؚٞ٭ց嚀耆
    ˝ ׆׿؅ط٭هٜ啶鵰ך辐׌מע
    
    

    View Slide

72. ٗ٭ا٭כهٞءס頇鐆꞊➟؅㍲מ׊יײ׾
    
    

    View Slide

73. ٗ٭ا٭כهٞءס頇鐆꞊➟؅㍲מ׊יײ׾
    ˝ usersכblogsע㛡㵚㛡ס꞊➟
    ˝ blogsמsubscribe_user_idס׻ֹם؜ّٚ؅餉׊יײ׾
    ˝ ͸ͯͳҰ࿠ֿೋ࿠ͷDB~כೋ࿠೔ৗ؅頇鐆׊יַ׾׆כ؅辐杯⺎耆
    ˝ ͸ͯͳࡾ࿠ֿೋ࿠ͷDB~؅頇鐆׊יַ׾׆כע辐杯ך׀םַ
    ˝ blogs.subscribe_user_idעח׊־⡁؅劲硯ך׀םַ
    ˝ 㛡㵚㛡ס꞊➟ךעյusers׷blogsמ؜ّٚ؅鴑ⱶ׌׾ך辐杯׌׾׆כע
    ㍭ꦘ
    
    

    View Slide

74. usersכblogsס頇鐆꞊➟؅辐׌ط٭هٜ؅⛼׾
    
    

    View Slide

75. usersכblogsס頇鐆꞊➟؅辐׌ط٭هٜ؅⛼׾
    ˝ 頇鐆؅辐׌subscriptionط٭هٜ؅⛼׾
    ˝ subscription־׼usersכblogsסٝؤ٭غ؅攐㲊ך׀׾׻
    ֹמuser_idכblog_id؅؜ّٚמ䧏ח
    ˝ 㛡㵚㛡ס꞊➟؅辐׌מעյ꞊➟؅辐׌ط٭هٜ؅⛼זיײ׾כ
    ַַ
    
    

    View Slide

76. 42- $3&"5&5"#-&
    
    CREATE TABLE user_blog_subscription (
    `user_id` BIGINT UNSIGNED NOT NULL,
    `blog_id` BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`user_id`, `blog_id`)
    );
    
    

    View Slide

77. 42- */4&35
    
    INSERT INTO user_blog_subscription (user_id, blog_id) VALUES
    (1, 2),
    (1, 3),
    (2, 1);
    
    

    View Slide

78. 42- 4&-&$5+0*/
    
    !" ͸ͯͳҰ࿠ ͷߪಡ
    mysql> select * from user_blog_subscription where user_id = 1;
    +---------+---------+
    | user_id | blog_id |
    +---------+---------+
    | 1 | 2 |
    | 1 | 3 |
    +---------+---------+
    2 rows in set (0.01 sec)
    !" ͸ͯͳҰ࿠ ͕ߪಡ͍ͯ͠ΔϒϩάҰཡ
    mysql> SELECT blogs.* FROM user_blog_subscription
    JOIN blogs
    ON user_blog_subscription.blog_id = blogs.id
    WHERE user_blog_subscription.user_id = 1;
    +----+---------+----------------------------------------+-----------------------------------------------------------------------------+
    | id | user_id | title | description |
    +----+---------+----------------------------------------+-----------------------------------------------------------------------------+
    | 2 | 2 | ೋ࿠ͷDBεϖγϟϦετ೔ه | ೋ࿠ͷDBεϖγϟϦετʹͳΔͨΊͷه࿥ϩάͰ͢ |
    | 3 | 2 | ೋ࿠೔ৗϩά | ೋ࿠ͷ೔ৗΛ࢒͢ϒϩάͰ͢ɻٕज़ϒϩά͸ͪ͜Β→!!$ |
    +----+---------+----------------------------------------+-----------------------------------------------------------------------------+
    2 rows in set (0.01 sec)
    
    

    View Slide

79. ط٭هٜ錃銶ס縒ֻ亠ױכ״
    ˝ 䣽ֹ䗯㕔מ浓泘׊יط٭هٜכ꞊➟؅縒ֻיײ׾
    ˝ ط٭هٜ⺱㚖ס꞊➟ע㍲מ׊יײ׾כ؂־׽׷׌ַ
    ˝ ꞊➟؅ط٭هٜך辐׌כ׌ז׀׽׌׾׆כ׵ֵ׾
    
    

    View Slide

80. قنؚ٭ُ٤ت
    
    

    View Slide

81. ظ٭ذي٭تכقنؚ٭ُ٤ت
    %#ע8FCئ٭لت؅ꝧ氦٬鷞ㅀ׌׾┕ךقنؚ٭ُ٤تסゖ갭מ笋׽׷׌ַ
    ˝ ئ٭لتֿ禈ׂ꡾׽ظ٭ذע㘃ⱶ׊禈ׄ׾
    ˝ 僃⮣עゖ갭מם־זגؠֿؙٛյظ٭ذꄈֿ㘃ֻג׆כך㲔车侇ꝴ־־׾׻ֹמ
    םזי׊ױזג׽
    ˝ قنؚ٭ُ٤تסゖ갭עئ٭لتס⿁韬⛥┖מ笋ֿ׾
    ˝ ؠؙٛס㲔车מ侇ꝴֿ־־׾
    ˝ ̜ٗ٭اמ鲭׌ٝتَ٤تֿ鷐ׂם׾
    ˝ ̜ٗ٭اֿꦕ׿׾
    
    

    View Slide

82. ׆ֹם׼םַג״מ
    ˝ قنؚ٭ُ٤تמ꞊׌׾湳閁؅♀ׄױ׊׺ֹ
    ˝ ׆׆ךע♓┖סחמחַי䣽ַױ׌
    ˝ ؕ٤ظشؠت
    ˝ &91-"*/
    
    

    View Slide

83. ؕ٤ظشؠت
    ط٭هٜסٝؤ٭غ؅냕鵭מ吾碟׌׾ג״ס%#.4ס☼磝ײ
    ˝ .Z42-ךעٝؤ٭غ؅吾碟׌׾꤀յؕ٤ظشؠتֿ扛ַכ⩝꯽车־׼ط٭هٜ⪒⛮؅鐆ײ⹦׽㵚霄
    סٝؤ٭غ؅釤♀ׄד׌
    ˝ FYSELECT * FROM users WHERE id = 100000;ס㕙⺬յط٭هٜס⩝꯽־׼♭׍ח釤י
    ַזיidֿ100000מ┉舙׌׾׵ס؅䫒׊יַׂ
    ˝ ؜ّٚמؕ٤ظشؠتֿ⛼䡗׈׿יַ׾㕙⺬յظ٭ذ䨯⪌侇מאס؜ّٚסظ٭ذֿ吾碟מ僃鸵ם
    ظ٭ذ啶鵰ך⟊㰆׈׿׾
    ˝ ؕ٤ظشؠتס⛼䡗׈׿יַ׾؜ֿّٚ吾碟㵚霄מֵ׾כյ⪒יסٝؤ٭غ؅鐧׬׍מ吾碟ֿ⭳全
    ׾
    ˝ .Z42-ךע#儒 #5SFF
    כַֹظ٭ذ啶鵰ֿ✳؂׿׾
    
    

    View Slide

84. ؕ٤ظشؠتס⛼׽亠
    ط٭هٜ㲊紶䏲׵⛼׿׾ֿյ׆׆ךעط٭هٜ㲊紶侇ס⛼׽亠מחַי
    ˝ 伺炐氳מ⛼׾亠嫎
    ˝ ⶡ┉؜ّٚסؕ٤ظشؠتKEY (ΧϥϜ໊) INDEX ΠϯσοΫε໊ (ΧϥϜ໊)
    ˝ 邾丗؜ّٚסؕ٤ظشؠتINDEX ΠϯσοΫε໊ (ΧϥϜ1, !!", ΧϥϜ16)
    ˝ ⯆硜מ׻׽舅Ⳃך⛼׼׿׾
    ˝ PRIMARY KEY
    ˝ UNIQUE
    
    

    View Slide

85. ❆VTFS@JE؜ّٚמחַיؕ٤ظشؠت؅⛼䡗
    !" user_id ͱ title ΧϥϜʹ͍ͭͯΠϯσοΫεΛ࡞੒
    CREATE TABLE `blogs` (
    `id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `title` VARCHAR(254) NOT NULL,
    `description` VARCHAR(512) NOT NULL,
    PRIMARY KEY (`id`),
    KEY (`user_id`),
    INDEX `idx_title` (`title`)
    );
    
    

    View Slide

86. ؕ٤ظشؠت⛼䡗סؤص
    㲔车׌׾ؠؙٛ־׼縒ֻ׾
    ˝ ؕ٤ظشؠت⛼䡗מעؤتعֿ־־׾ 䏲鳭
    ג״յ扛Ꟊמ⛼׿ףַַ؂ׄךעםַ
    ˝ ؓوٛآ٭ب٘٤ך✳ֹSELECT乃؅ْؕ٭ة׊יյ✳؂׿אֹםؕ٤ظشؠت؅
    縒ֻ׾
    ˝ ❆րֵ׾ٗ٭ا٭סهٞء┉鈋؅⹦䐂׌׾ց
    ˝ ؠؙٛSELECT * FROM blogs WHERE user_id = 1
    ˝ user_id؅⩕מ吾碟׌׾סךuser_idמؕ٤ظشؠتֵֿ׾כ냕鵭מ吾碟ך
    ׀׾
    
    

    View Slide

87. ؕ٤ظشؠت嫰䙫憠
    ˝ ؕ٤ظشؠت⛼䡗מעؤتعֿ־־׾
    ˝ ٝؤ٭غס⛼䡗٬催二٬⯡ꢜ侇מؕ٤ظشؠت׵催二׌׾סךյ؛٭ف٭ىش
    غֵֿ׾
    ˝ 㛡ׂסؓوٛآ٭ب٘٤ךעࢀরॲཧ ʼ ߋ৽ॲཧמם׾סךֵױ׽ゖ갭ם
    ַ
    ˝ ؕ٤ظشؠتֿ✳؂׿םַآ٭ت׵ֵ׾
    ˝ ❆⪒♭䫒碟ס亠ֿ企ַ
    ˝ 齉鷹䈡洈؅辐׌ط٭هٜדכյٝؤ٭غ׊־םַסך⪒♭䫒碟׊ג亠ֿ企ַ
    
    

    View Slide

88. &91-"*/
    ˝ .Z42-ךؠؙٛס㲔车銶槆 ✳榫׌׾ؕ٤ظشؠت׷ط٭هٜ
    ؅磵⺬׌׾ꯂ䈋םל
    ؅⭳ⱱ׌׾亠嫎
    ˝ ⩝꯽מEXPLAIN؅♀ׄ׾׆כךյאסؠؙٛמחַיס䗯㕔؅
    䐂׾׆כֿך׀׾
    
    

    View Slide

89. &91-"*/ס❆
    !" ΠϯσοΫεͷޮ͔ͳ͍ΫΤϦͷEXPLAIN
    !" type ALL ͸ϑϧςʔϒϧεΩϟϯɺςʔϒϧશମΛಡΈऔ͍ͬͯΔ͜ͱΛࣔ͢ = ΠϯσοΫε͕࢖ΘΕ͍ͯͳ͍
    mysql> EXPLAIN SELECT COUNT(*) FROM blogs WHERE description like '%aaa';
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | 1 | SIMPLE | blogs | NULL | ALL | NULL | NULL | NULL | NULL | 109745 | 11.11 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    !" ΠϯσοΫε͕ޮ͘ΫΤϦͷEXPLAIN
    !" type range ͸ΠϯσοΫεΛ࢖༻͠ɺಛఆͷൣғʹ͋ΔߦͷΈ͕औಘ͞Ε͍ͯΔ͜ͱΛࣔ͢
    mysql> EXPLAIN SELECT COUNT(*) FROM blogs WHERE user_id > 2000;
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
    | 1 | SIMPLE | blogs | NULL | range | user_id | user_id | 8 | NULL | 54872 | 100.00 | Using where; Using index |
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
    1 row in set, 1 warning (0.00 sec)
    .Z42-.Z42-ٛنؒٝ٤تُؼٖٜؓ&91-"*/⭳ⱱ
    نؚ٭ُشع
    
    

    View Slide

90. قنؚ٭ُ٤ت劲銧
    䫟峮׌׾םյ銶峮׎׻
    ˝ قنؚ٭ُ٤ت㵚瓀؅׌׾כ׀עⳆך㵚⭚׊יעַׄםַ
    ˝ ׀ה؆כ銶峮׊յٍعٜؾشؠ؅䤧䭂׊י㵚⭚׊יַׂ䑒釐ֿ
    ֵ׾
    
    

    View Slide

91. 磙؂׽מ
    
    

    View Slide

92. 磙؂׽מ
    ˝ 侇ꝴֽ檄׿喋ך׊ג
    ˝ 哭䒝٬42-٬ط٭هٜ錃銶٬قنؚ٭ُ٤تכ׈׼זגׄלյ㓹灄氳םع
    مشؠ؅⪒י䣽ֻיַ׾؂ׄךעםַך׌
    ˝ 湳閁׷杼鑜מחַיעյ狜ַג侇ꝴ׷ؕ٤ذ٭٤ֿ磙؂זג䏲ך׋זׂ׽
    כ
    ˝ 㲔ئ٭لتסظ٭ذꄈ؅䣽ֹ磬꽦עյ䏲ⶐق٭عך׊ז־׽⛮꽦׊י׀י
    ׂד׈ַ
    ˝ 䣽ֻם־זגعمشؠ׷؞٭٠٭غמחַי⮛䧸׊יֽ׀ױ׌
    
    

    View Slide

93. 磙؂׽מ
    ˝ 42-מחַי
    ˝ 邾ꥭםؠؙٛ׷꥗銶 ئهؠؙٛ
    
    ˝ ط٭هٜ錃銶
    ˝ 塜釨⴫
    ˝ عٚ٤اؠب٘٤
    ˝ "$*%攐䓪٬ٞشؠ
    ˝ وٞءِٚ٤ء銧鏤־׼42-؅䣽ֹ ♀ꎰמ癨ⶡמ鋗鬼
    
    ˝ /ゖ갭
    ˝ ج؞ٖٛطؔ
    ˝ 42-ؕ٤ةؘؠب٘٤
    
    

    View Slide

94. ♀ꎰ
    
    

    View Slide

95. %PDLFS؅✳זי.Z42-؅䳩⛼׌׾
    
    颯Ⳃ亠嫎
    $ docker pull mysql:8.0
    $ docker run !"name rdbms-bootcamp -e MYSQL_ROOT_PASSWORD="root_passwd" \
    -e MYSQL_USER='user' \
    -e MYSQL_PASSWORD='passwd' \
    -e MYSQL_DATABASE="bootcamp" \
    -p 13306:3306 mysql:8.0
    NZTRM׫ס⪌׽亠
    $ docker container exec -it rdbms-bootcamp mysql -uuser -ppasswd bootcamp
    
    

    View Slide

96. %PDLFS؅✳זי.Z42-؅䳩⛼׌׾
    
    嫰䙫憠
    ˝ SPPUٗ٭ا٭ךע%PDLFSؤ٤طػ⫂־׼ךםַכ⪌׿ױ׎؆
    ˝ ̜嘤꡾畘杼ס꞊➟
    ˝ ⪜䌋סْؕ٭ةדכNZTRMDMJFOU־׼⪌׾כ仼儖鏤ֿ⪌ⱱך׀םַ٬⭳ⱱ׈׿ם
    ַ
    ˝ 04מ仼儖鏤錃㲊ֿ⪌זיַםַג״
    ˝ mysql:8סي٭تْؕ٭ةע%FCJBOםסךյ♓┖ס亠嫎ך仼儖鏤⪌ⱱך׀׾
    ׻ֹמם׾
    
    

    View Slide

97. %PDLFS؅✳זי.Z42-؅䳩⛼׌׾
    
    仼儖鏤ס錃㲊亠嫎
    # ίϯςφ಺ʹೖΔ
    $ docker container exec -it rdbms-bootcamp /bin/bash
    # locale ઃఆΛΠϯετʔϧ͠ɺ೔ຊޠ͕࢖͑ΔΑ͏ʹ͢Δ
    root@xxx:/$ apt-get update !" apt-get install -y locales
    root@xxx:/$ echo "ja_JP.UTF-8 UTF-8" !# /etc/locale.gen
    root@xxx:/$ locale-gen
    root@xxx:/$ exit
    # LANG؀ڥม਺Λ೔ຊޠʹͯ͠ɺmysqlʹϩάΠϯ
    $ docker container exec -it rdbms-bootcamp env LANG="ja_JP.UTF-8" mysql -uuser -ppasswd bootcamp
    
    

    View Slide

98. ⹆縒ٛ٤ؠ
    
    3%#.4⪒芗
    ˝ ظ٭ذي٭ت哭鑜̂]璵嫧㛻㰢؛٭و٤ؤ٭تؘؗؓ546,6#"0$8
    ˝ ֵ׾爊䈱%#؅䣽ֹ׆כמ䜉׿י׀י־׼釤יײ׾כ杼鑜כ㲔饧ֿ⺬؂׈זי׻ַכ䓙ֹ
    .Z42-מחַי
    ˝ .Z42-.Z42-3FGFSFODF.BOVBM
    ˝ ف٭ة٘٤ꝴך㛻׀ׂ㚺؂׼םַ׵ס׵㛡ַסךյ仼儖鏤ַַֿ㕙⺬עס亠؅釤י׵׻ַ
    ˝ .Z42-.Z42-ٛنؒٝ٤تُؼٖٜؓ
    ˝ ٛنؒٝ٤تםסךյ㍭זגכ׀מ鰭傴氳מ鐆׳סֿ荁ַ
    
    

    View Slide

99. ⹆縒ٛ٤ؠ
    
    (P銧鏤־׼ظ٭ذي٭ت؅䣽ֹ
    ˝ "DDFTTJOHEBUBCBTFT5IF(P1SPHSBNNJOH-BOHVBHF
    ˝ وٞءِٚ٤ء銧鏤־׼䣽ֹ┉❆כ׊י(Pסغ؞ْٖ٤ع؅磆
    ☭׊ױ׌
    ˝ ׆סغ؞ْٖ٤ع舅⛮ע(Pסغ؞ْٖ٤عך׌ֿյوٞءّٚ
    ־׼42-؅䣽ֹ׆כמחַיסزٖ٭عٜٛؓ־׼┉芗氳ם鎇
    갭յ嫰䙫憠םלמ鉴׿׼׿ױ׌
    
    

    View Slide