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

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

Hatena
October 08, 2021

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

Hatena

October 08, 2021
Tweet

More Decks by Hatena

Other Decks in Technology

Transcript

  1. ظ٭ذي٭ت畘杼بتطّ %#.4ס䏔Ⱏ .Z42-דכ ˝ ظ٭ذ؅䥩霄⴫׊յⲖ曍׻ׂ⟊㰆 ˝ ظ٭ذ؅✳ֹ☔עظ٭ذֿלס׻ֹמ劲硯׈׿יַ׾־䙫閁׊םׂי׵ַַ ˝ 榫鵍מֵ؂׎י僃鸵ם啶鵰ךظ٭ذ؅⟊㰆׌׾ ˝

    ┾⮛ؓؠجت ˝ ⺱侇מ傴׀鱮ײ鐆ײ鱮ײֿ氦榟׊ג侇מ룲뤕ֿ颯׀םַ׻ֹמך׀׾ ˝ ظ٭ذ䮇㜊؅ꡔ׃ ˝ ظ٭ذ؅傴׀鱮؆ךַ׾鵍╈ך%#ئ٭ف٭ֿ蛽הג㕙⺬յ傴׀鱮ײֿ╈鵍ⶐ珷מ磙؂׾⺎耆䓪׵ֵ׾ ˝ אַֹזג꤀סٛ؜فٛ٭ס☼磝ײ؅䧏זיַ׾ ̜ظ٭ذ؅㔋擱מ䣽ַגַ 
  2. 榫鏤丝杼  榫鏤 䙫⽏ ط٭هٜ ظ٭ذס꥗⺬ ٝؤ٭غ א׿ב׿סط٭هٜסظ٭ذס磝车车 ؜ّٚ ط٭هٜסظ٭ذס㷯䓪

    ت؞٭ُ ط٭هٜס啶鵰 ל؆ם؜ֵֿّٚ׾ ־յ؜ّٚמעל؆ם⡁ֿ⪌׾־յ FUD 
  3. 42- 3%#.4מゖַ⺬؂׎ 䳩⛼ ؅车ֹג״ס銧鏤 ˝ %%- %BUB%FOJUJPO-BOHVBHF ˝ ت؞٭ُ؅㲊紶㚺催׌׾42- ˝

    %.- %BUB.BOJQVMBUJPO-BOHVBHF ˝ ظ٭ذ؅䳩⛼׌׾42- ˝ %$- %BUB$POUSPM-BOHVBHF ˝ ظ٭ذ׫סؓؠجتמ꞊׌׾42- ˝ ׆ס铺紶ךע磆☭׊םַ 
  4. $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`) ); 
  5. 丗⡁㑔  ˝ 丝丗 ˝ INTEGER INT TINYINT BIGINT ˝

    UNSIGNED㷯䓪؅♀┙׌׾כ瑞⺘扛׊ך⟊㰆ך׀׾׻ֹמם׾ UNSIGNED BIGINT ˝ 㷯䓪؜ّٚס攐䓪նظ٭ذ㑔夵מ樟ם׾㷯䓪ֵֿ׾ ˝ 宯Ⳃ㵸丗憠 ˝ FLOAT DOUBLE ˝ ㍾㲊㵸丗憠 ˝ DECIMAL NUMERIC 
  6. 丗⡁㑔  ˝ 劼ֵר׿מ妳؅חׄ׾䑒釐ֵֿ׾ ˝ ❆INT㑔ע-2147483648־׼2147483647ס疗㍱؅⟊㰆ך׀ ׾ ˝ ׊־׊⧠ٝؤ٭غע勅㛙癨ⶡמ⮼鷼ך׀׾ ˝

    id 鵽樑מ✳ֹ؜ّٚ םלעUNSIGNED BIGINTמ׊יֽׂכ㱦䑏 ˝ 18446744073709551615 ◹ ױך⟊㰆ך׀׾ 
  7. 乃㰄⮛㑔 ˝ 㛡ׂס㕙⺬ךVARCHAR(n)؅✳ֹ ˝ ⺎㚺Ꝏ乃㰄⮛ך0 ~ 65,535 byte⟊㰆⺎耆 ˝ name

    VARCHAR(32)עրname؜ّٚע乃㰄⮛㑔ךյ乃㰄 ⟊㰆⺎耆ցכַֹ䙫⽏ ˝ ֵ׾爊䈱Ꝏַ乃㰄⮛؅⟊㰆׌׾כ׀מעTEXT؅⮵榫 ˝ TEXT MEDIUMTEXT LONGTEXT 
  8. 仼♀כ侇ꝴ㑔  ˝ 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$ ˝ ذّؕد٭٤ס哭䒝ֵֿ׾ 
  9. ⯆硜כע ˝ ؜ّٚמ⪌׾ظ٭ذמ㵚׌׾⯆꡾ ˝ 䙫㍲׊םַظ٭ذֿ⪌׽鱮ױםַ׻ֹמ ˝ ⯆硜מ⹚׌׾כؙٚ٭מם׾ !" σʔλ௥Ճͷ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' 
  10. ⯆硜❆ ☽מ׵ֵ׾ ⯆硜⺲ ⫂㳊 PRIMARY KEY⯆硜 ؜ّٚס⡁ֿط٭هٜ⫂ךٝؤ٭غ؅┉䙫 מ閁⮯ך׀׾׆כ؅澬㲊 UNIQUE⯆硜 ؜ّٚס⡁ֿط٭هٜ⫂ך┉䙫ךֵ׾׆כ

    ؅澬㲊 גד׊NULL͸͋Δ NOT NULL⯆硜 ؜ّٚס⡁ֿNULLךעםַ׆כ؅澬㲊 FOREIGN KEY⯆硜 邾丗סط٭هٜꝴך丝⺬䓪ֿ⹦׿יַ׾׆ כ؅澬㲊 
  11. 13*."3:,&: ╚؞٭ ˝ ط٭هٜ⫂ךٝؤ٭غ؅┉䙫מ閁⮯ך׀׾؜ّٚ ˝ חסط٭هٜךח䧗㲊ך׀׾ ˝ PRIMARY KEYס؜ّٚע域ס⯆硜؅嵹ג׈םַכַׄםַ ˝

    ☽סٝؤ٭غכꄆ邾׊יעַׄםַ UNIQUE⯆硜 ˝ ⡁ֿםׄ׿ףם׼םַ NOT NULL⯆硜 ˝ PRIMARY KEYעؕ٤ظشؠت 䏲鳭 כ׊י׵✳ֻ׾ 
  12. ䷉״י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ךעםַ 
  13. ♓꡸ע♓┖סط٭هֵֿٜ׾⯥䬠ך !" ϢʔβʔΛද͢ςʔϒϧ 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) ); 
  14. */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啶 乃 
  15. 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啶乃 
  16. 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&啶乃 
  17. %&-&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&啶乃 
  18. 奂鬳 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'; 
  19. 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); 
  20. خ٭ع 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) 
  21. ً٭ة٤ء -*.*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; 
  22. ءٜ٭م٤ء (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) 
  23. ط٭هٜ⺱㚖ס磵⺬ +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םלס磵⺬亠嫎׵ֵ׾ 
  24. ط٭هٜ⺱㚖ס磵⺬ +0*/   ˝ blogs JOIN usersCMPHTط٭هٜמVTFSTط٭هٜ؅磵⺬׈׎׾ ˝ ON

    blogs.user_id = users.idCMPHTVTFS@JEכ┉舙׌׾⡁סVTFSTJE؅䧏חٝؤ٭غ؅磵⺬׈׎׾ 
  25. هٞءئ٭لت؅❆מ縒ֻיײ׾ ˝ ٗ٭ا٭ַֿ׾ ˝ ٗ٭ا٭ע⺲⯥؅䧏ח ˝ ٗ٭ا٭עهٞء؅䧏י׾ ˝ هٞءמעذؕعֿٜ♀ׂ ˝

    هٞءמע鐄伺乃ֿ傴ׄ׾ ˝ ٗ٭ا٭עهٞء؅邾丗䧏י׾ ל؆םط٭هٜ 辐 ך辐׎׾ד؀ֹ־ 
  26. ׆׿ך׵⭳全׾זה׶⭳全׾ׄל˟˟ ˝ ٗ٭ا٭⺲ס䗯㕔ֿꄆ邾׊יױ׌נ ˝ 鷿⽰䚉סֵ׾ؠؙٛמםזג׽׊םַ ˝ FYٗ٭ا٭⺲┉鈋؅⹦䐂׌׾ג״מע SELECT UNIQUE user_name

    FROM table_nameכַֹؠؙٛמ ˝ ┉鈋؅⹦׽גַדׄםסמUNIQUEֿ䑒釐םס ˝ ☪ס☼喋ךע׆׿דׄדׄלյ䣽ֹ䗯㕔ֿ㘃ֻג׼לֹ׌׾ ˝ FYهٞءמ鋗◄؅邾丗䧏ג׎גַ ˝ ؜ّٚ鴑ⱶ׊ג׼׆סط٭هٜך׵㲔杯ך׀׾ֿյ鋗◄ס丗דׄٝؤ٭غֿ⛼׼׿յאס ⮆ٗ٭ا٭٬هٞءסظ٭ذסꄆ邾ֿ㘃ֻ׾ 
  27. 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`) ); 
  28. 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, "ೋ࿠೔ৗϩά", "ೋ࿠ͷ೔ৗΛ࢒͢ϒϩάͰ͢ɻٕज़ϒϩά͸ͪ͜Β→""#"); 
  29. 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) 
  30. 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) 
  31. 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`) ); 
  32. 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) 
  33. ؕ٤ظشؠت ط٭هٜסٝؤ٭غ؅냕鵭מ吾碟׌׾ג״ס%#.4ס☼磝ײ ˝ .Z42-ךעٝؤ٭غ؅吾碟׌׾꤀յؕ٤ظشؠتֿ扛ַכ⩝꯽车־׼ط٭هٜ⪒⛮؅鐆ײ⹦׽㵚霄 סٝؤ٭غ؅釤♀ׄד׌ ˝ FYSELECT * FROM users

    WHERE id = 100000;ס㕙⺬յط٭هٜס⩝꯽־׼♭׍ח釤י ַזיidֿ100000מ┉舙׌׾׵ס؅䫒׊יַׂ ˝ ؜ّٚמؕ٤ظشؠتֿ⛼䡗׈׿יַ׾㕙⺬յظ٭ذ䨯⪌侇מאס؜ّٚסظ٭ذֿ吾碟מ僃鸵ם ظ٭ذ啶鵰ך⟊㰆׈׿׾ ˝ ؕ٤ظشؠتס⛼䡗׈׿יַ׾؜ֿّٚ吾碟㵚霄מֵ׾כյ⪒יסٝؤ٭غ؅鐧׬׍מ吾碟ֿ⭳全 ׾ ˝ .Z42-ךע#儒 #5SFF כַֹظ٭ذ啶鵰ֿ✳؂׿׾ 
  34. ؕ٤ظشؠتס⛼׽亠 ط٭هٜ㲊紶䏲׵⛼׿׾ֿյ׆׆ךעط٭هٜ㲊紶侇ס⛼׽亠מחַי ˝ 伺炐氳מ⛼׾亠嫎 ˝ ⶡ┉؜ّٚסؕ٤ظشؠتKEY (ΧϥϜ໊) INDEX ΠϯσοΫε໊ (ΧϥϜ໊)

    ˝ 邾丗؜ّٚסؕ٤ظشؠتINDEX ΠϯσοΫε໊ (ΧϥϜ1, !!", ΧϥϜ16) ˝ ⯆硜מ׻׽舅Ⳃך⛼׼׿׾ ˝ PRIMARY KEY ˝ UNIQUE 
  35. ❆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`) ); 
  36. ؕ٤ظشؠت⛼䡗סؤص 㲔车׌׾ؠؙٛ־׼縒ֻ׾ ˝ ؕ٤ظشؠت⛼䡗מעؤتعֿ־־׾ 䏲鳭 ג״յ扛Ꟊמ⛼׿ףַַ؂ׄךעםַ ˝ ؓوٛآ٭ب٘٤ך✳ֹSELECT乃؅ْؕ٭ة׊יյ✳؂׿אֹםؕ٤ظشؠت؅ 縒ֻ׾ ˝

    ❆րֵ׾ٗ٭ا٭סهٞء┉鈋؅⹦䐂׌׾ց ˝ ؠؙٛSELECT * FROM blogs WHERE user_id = 1 ˝ user_id؅⩕מ吾碟׌׾סךuser_idמؕ٤ظشؠتֵֿ׾כ냕鵭מ吾碟ך ׀׾ 
  37. ؕ٤ظشؠت嫰䙫憠 ˝ ؕ٤ظشؠت⛼䡗מעؤتعֿ־־׾ ˝ ٝؤ٭غס⛼䡗٬催二٬⯡ꢜ侇מؕ٤ظشؠت׵催二׌׾סךյ؛٭ف٭ىش غֵֿ׾ ˝ 㛡ׂסؓوٛآ٭ب٘٤ךעࢀরॲཧ ʼ ߋ৽ॲཧמם׾סךֵױ׽ゖ갭ם

    ַ ˝ ؕ٤ظشؠتֿ✳؂׿םַآ٭ت׵ֵ׾ ˝ ❆⪒♭䫒碟ס亠ֿ企ַ ˝ 齉鷹䈡洈؅辐׌ط٭هٜדכյٝؤ٭غ׊־םַסך⪒♭䫒碟׊ג亠ֿ企ַ 
  38. &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-"*/⭳ⱱ نؚ٭ُشع 
  39. 磙؂׽מ ˝ 42-מחַי ˝ 邾ꥭםؠؙٛ׷꥗銶 ئهؠؙٛ ˝ ط٭هٜ錃銶 ˝ 塜釨⴫

    ˝ عٚ٤اؠب٘٤ ˝ "$*%攐䓪٬ٞشؠ ˝ وٞءِٚ٤ء銧鏤־׼42-؅䣽ֹ ♀ꎰמ癨ⶡמ鋗鬼 ˝ / ゖ갭 ˝ ج؞ٖٛطؔ ˝ 42-ؕ٤ةؘؠب٘٤ 
  40. %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 
  41. %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 
  42. ⹆縒ٛ٤ؠ  3%#.4⪒芗 ˝ ظ٭ذي٭ت哭鑜̂]璵嫧㛻㰢؛٭و٤ؤ٭تؘؗؓ546,6#"0$8 ˝ ֵ׾爊䈱%#؅䣽ֹ׆כמ䜉׿י׀י־׼釤יײ׾כ杼鑜כ㲔饧ֿ⺬؂׈זי׻ַכ䓙ֹ .Z42-מחַי ˝ .Z42-.Z42-3FGFSFODF.BOVBM

    ˝ ف٭ة٘٤ꝴך㛻׀ׂ㚺؂׼םַ׵ס׵㛡ַסךյ仼儖鏤ַַֿ㕙⺬עס亠؅釤י׵׻ַ ˝ .Z42-.Z42-ٛنؒٝ٤تُؼٖٜؓ ˝ ٛنؒٝ٤تםסךյ㍭זגכ׀מ鰭傴氳מ鐆׳סֿ荁ַ 