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

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

Hatena
December 19, 2022

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

https://hatena.co.jp/recruit/intern/2022

脚注などで紹介したリンクを以下に再掲します:
P19
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.1.20 CREATE TABLE ステートメント: https://dev.mysql.com/doc/refman/8.0/ja/create-table.html
- PostgreSQL: Documentation: 14: CREATE TABLE: https://www.postgresql.org/docs/current/sql-createtable.html
P38
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.6 INSERT ステートメント: https://dev.mysql.com/doc/refman/8.0/ja/insert.html
- PostgreSQL: Documentation: 14: INSERT: https://www.postgresql.org/docs/current/sql-insert.html
P39
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.10 SELECT ステートメント: https://dev.mysql.com/doc/refman/8.0/ja/select.html
- PostgreSQL: Documentation: 14: SELECT: https://www.postgresql.org/docs/current/sql-select.html
P41
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.13 UPDATE ステートメント: https://dev.mysql.com/doc/refman/8.0/ja/update.html
- PostgreSQL: Documentation: 14: UPDATE: https://www.postgresql.org/docs/current/sql-update.html
P42
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.2 DELETE ステートメント: https://dev.mysql.com/doc/refman/8.0/ja/delete.html
- PostgreSQL: Documentation: 14: DELETE: https://www.postgresql.org/docs/current/sql-delete.html
P87
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 8.8.1 EXPLAIN によるクエリーの最適化: https://dev.mysql.com/doc/refman/8.0/ja/using-explain.html
- PostgreSQL: Documentation: 14: EXPLAIN: https://www.postgresql.org/docs/current/sql-explain.html
P90
- 「推測するな、計測せよ」: http://users.ece.utexas.edu/~adnan/pike.html
P101
- データベース概論Ⅰ | 筑波大学オープンコースウェア|TSUKUBA OCW: https://ocw.tsukuba.ac.jp/course/systeminformation/database-systems-i/
- MySQL 8.0 リファレンスマニュアル: https://dev.mysql.com/doc/refman/8.0/ja/
- PostgreSQL: Documentation: 14: PostgreSQL 14.5 Documentation: https://www.postgresql.org/docs/current/index.html
P102
- Accessing databases - The Go Programming Language: https://golang.org/doc/#data-access

Hatena

December 19, 2022
Tweet

More Decks by Hatena

Other Decks in Programming

Transcript

  1. ͸͡Ίʹ • ͸ͯͳͰ͸ओʹ MySQL ͱ PostgreSQL ͱ͍͏ DBMS ΛΑ͘ ࢖͍ͬͯ·͢

    • ޙ൒ύʔτͷػೳ։ൃͰ͜ΕΒΛ࢖͏ࡍʹɺ0 ͔Βௐ΂Δ͜ͱ ͕ͳ͍Α͏ʹجૅతͳ෦෼Λઆ໌͠·͢ #hatenaintern2022 3
  2. RDBMS ͱ͸ RDBMS = Relational Database Management System • Relational

    Database • ؔ܎σʔλϕʔε • Database Management System • σʔλϕʔε؅ཧγεςϜ ؔ܎σʔλϕʔεͷσʔλϕʔε؅ཧγεςϜ #hatenaintern2022 8
  3. ༻ޠ ༻ޠ ҙຯ ςʔϒϧ σʔλͷू߹ Ϩίʔυ ςʔϒϧͷσʔλͷ૊ 1 ߦ 1

    ߦ ΧϥϜ ςʔϒϧͷσʔλͷଐੑ εΩʔϚ ςʔϒϧͷߏ଄ ʢͲΜͳΧϥϜ͕͋Δ͔ɺΧϥϜʹ͸Ͳ Μͳ஋͕ೖΔ͔ɺͳͲʣ #hatenaintern2022 14
  4. SQL RDBMS ʹ໰͍߹Θͤʢૢ࡞ʣΛߦ͏ͨΊͷݴޠ • DDL (Data Definition Language) • εΩʔϚΛఆٛ͢Δ

    SQL • DML (Data Manipulation Language) • σʔλΛૢ࡞͢Δ SQL • ݕࡧɺߋ৽ͳͲ • DCL (Data Control Language) • σʔλ΁ͷΞΫηε੍ޚʹؔ͢Δ SQL • ͜ͷߨٛͰ͸঺հ͠ͳ͍ SQL Ͱॻ͔Εͨɺσʔλϕʔε΁ͷ໰͍߹ΘͤจΛɺʮΫΤϦʯͱݺͿ #hatenaintern2022 16
  5. σʔλఆٛ • DDL (Data Definition Language) ← ͜Ε • εΩʔϚΛఆٛ͢Δ

    SQL • DML (Data Manipulation Language) • σʔλΛૢ࡞͢Δ SQL • ݕࡧɺߋ৽ͳͲ #hatenaintern2022 18
  6. CREATE TABLE จ1 • ςʔϒϧΛఆٛ͢Δߏจ • CREATE TABLE ςʔϒϧ໊ (ςʔϒϧఆٛ);

    -- `--` ͸ίϝϯτ -- id, name, created_at ΧϥϜΛؚΉςʔϒϧΛɺ -- users ͱ͍͏໊લͰఆٛ CREATE TABLE users ( id BIGINT NOT NULL, name VARCHAR(32) NOT NULL, created_at TIMESTAMP NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); 1 MySQL :: MySQL 8.0 ϦϑΝϨϯεϚχϡΞϧ :: 13.1.20 CREATE TABLE εςʔτϝϯτ PostgreSQL: Documentation: 14: CREATE TABLE #hatenaintern2022 19
  7. CREATE TABLE จ id BIGINT NOT NULL, • BIGINT ͷ෦෼:

    ΧϥϜͷσʔλܕ • NOT NULL ͷ෦෼: ΧϥϜͷ੍໿ #hatenaintern2022 20
  8. ਺஋ܕ • ੔਺ • TINYINT , INTEGER , BIGINT ͳͲ

    • ӈʹߦ͘΄Ͳଟ͘ͷܻΛอଘͰ͖Δ • MySQL Ͱ͸ UNSIGNED ଐੑΛ෇༩͢Δͱූ߸ແ͠ͰอଘͰ͖ΔΑ͏ʹͳΔʢBIGINT UNSIGNEDʣ • ଐੑ: ΧϥϜͷಛੑɻ͚ͭΒΕΔଐੑ͸ɺσʔλܕʹΑͬͯҟͳΔ • ුಈখ਺఺ • FLOAT , DOUBLE • ݻఆখ਺఺ • DECIMAL , NUMERIC #hatenaintern2022 23
  9. ਺஋ܕ • ܻ͋;ΕʹؾΛ͚ͭΔඞཁ͕͋Δ • Ϩίʔυ͝ͱʹϢχʔΫʹ͚ͭΔ id ͳͲ͸ BIGINT UNSIGNED ʹ

    ͓ͯ͘͠ͱ҆৺ • 18446744073709551615ʢ1844ژʣ·ͰอଘͰ͖Δ #hatenaintern2022 24
  10. จࣈྻܕ • ଟ͘ͷ৔߹Ͱ VARCHAR(n) Λ࢖͏ • Մม௕จࣈྻͰ 0 ʙ 65,535

    όΠτอଘՄೳ • VARCHAR(32) ͷΑ͏ʹɺอଘ͢Δ࠷େจࣈ਺Λࢦఆ͢Δ • ͋Δఔ౓௕͍จࣈྻΛอଘ͢Δͱ͖ʹ͸ςΩετܕΛར༻ • MySQL Ͱ͸ TEXT, MEDIUMTEXT, LONGTEXT ͳͲ • PostgreSQL ʹ͸ TEXT ͚ͩ༻ҙ͞Ε͍ͯΔ #hatenaintern2022 25
  11. ೔෇ͱ࣌ؒܕ MySQL ͷ৔߹ • DATETIME • ೔෇ͱ࣌ؒͷ྆ํΛؚΉ • αϙʔτ͢Δൣғ͸ 1000-01-01

    00:00:00 ʙ 9999-12-31 23:59:59 • λΠϜκʔϯͷ֓೦͕ͳ͍ • TIMESTAMP • ೔෇ͱ࣌ؒͷ྆ํΛؚΉ • αϙʔτ͢Δൣғ͸ 1970-01-01 00:00:01 UTC ʙ 2038-01-19 03:14:07 UTC • λΠϜκʔϯͷ֓೦͕͋Δ #hatenaintern2022 26
  12. ੍໿ ྫ: ʮusers ςʔϒϧͷ id ͕ҰҙʹͳΔʯͱ͍͏੍໿Λ͚ͭͨঢ়ଶͩͱ...2 -- id = 1

    ͷ user Λ௥Ճͯ͠ɺ੒ޭ͢Δ INSERT INTO users (id, name, created_at) VALUES (1, 'name1', NOW()); Query OK, 1 row affected (0.01 sec) -- ಉ͡ id ͷ஋ΛೖΕΑ͏ͱ͢ΔͱɺΤϥʔʹͳΔ INSERT INTO users (id, name, created_at) VALUES (1, 'name2', NOW()); ERROR 1062 (23000): Duplicate entry '1' for key 'users.PRIMARY' 2 ΫΤϦͷԼʹॻ͍ͯ͋Δͷ͸ RDBMS ͔Βฦ͞Ε͖ͯͨΫΤϦͷ݁Ռɻجຊతʹ MySQL ͷ΋ͷΛషͬͯ͋ΔͷͰ PostgreSQL Ͱ࣮ߦ͢Δͱҧ͏݁Ռ͕ฦͬͯ͘Δͱࢥ͍·͕͢ɺݟͨ໨͕ҧ ͏͚ͩͰɺҙຯ͍ͯ͠Δ͜ͱ͸͍͍ͩͨಉ͡Ͱ͢ɻ #hatenaintern2022 31
  13. ੍໿ͷྫ • NOT NULL੍໿: ΧϥϜ͸ NULL Ͱ͋ͬͯ͸͍͚ͳ͍ • NULL ͸ɺ஋͕ܽଛ͍ͯ͠Δ͜ͱΛද͢

    • UNIQUE ੍໿: ΧϥϜͷ஋͕ςʔϒϧ಺ͰҰҙͰͳ͚Ε͹ͳΒͳ͍ • ͨͩ͠ NULL ͸͍ͭ͋ͬͯ͘΋ڐ͞ΕΔͷͰ஫ҙ • PRIMARY KEY ੍໿: ΧϥϜͷ஋͕ςʔϒϧ಺ͰϨίʔυΛҰҙʹࣝผͰ͖ͳ͚Ε͹ ͍͚ͳ͍ • FOREIGN KEY ੍໿: ඥ͍͍ͮͯΔςʔϒϧؒͰ੔߹ੑ͕औΕ͍ͯͳ͚Ε͹͍͚ͳ͍ #hatenaintern2022 32
  14. PRIMARY KEY (ओΩʔ) • ςʔϒϧ಺ͰϨίʔυΛҰҙʹࣝผͰ͖ΔΧϥϜ • 1ͭͷςʔϒϧͰ1ͭࢦఆͰ͖Δ • PRIMARY KEY

    ੍໿͸ɺUNIQUE ੍໿ͱ NOT NULL ੍໿Λซͤ࣋ͬͯ ͍Δͱ͍͑Δ • PRIMARY KEY͸ΠϯσοΫεʢޙड़ʣͱͯ͠΋࢖͑Δ #hatenaintern2022 33
  15. վΊͯ users ςʔϒϧͷఆٛΛݟΔ CREATE TABLE users ( id BIGINT NOT

    NULL, name VARCHAR(32) NOT NULL, created_at TIMESTAMP NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); users ͱ͍͏໊લͷςʔϒϧΛఆٛ͠ɺҎԼͷΧ ϥϜΛ࣋ͨͤΔ • id • ੔਺ͷ஋͕ೖΓɺ͜ͷ஋ʹΑͬͯςʔϒϧ಺ ͷϨίʔυΛҰҙʹࣝผͰ͖Δ • name • ࠷େ32จࣈͷจࣈྻͷ஋͕ೖΓɺNULL Ͱͳ ͍͔ͭσʔλ͕ॏෳ͢Δ͜ͱ͕ͳ͍ • created_at • ࣌ࠁͷ஋͕ೖΓɺNULL Ͱ͸ͳ͍ #hatenaintern2022 34
  16. σʔλૢ࡞ • DDL (Data Definition Language) • εΩʔϚΛఆٛ͢Δ SQL •

    DML (Data Manipulation Language) ← ͜Ε • σʔλΛૢ࡞͢Δ SQL • ݕࡧɺߋ৽ͳͲ #hatenaintern2022 35
  17. CRUD • σʔλૢ࡞͢Δࡍͷجຊػೳ • ͦΕͧΕͷૢ࡞ͷ಄จࣈ͔Β • Create (௥Ճ) • Read

    (ࢀর) • Update (ߋ৽) • Delete (࡟আ) #hatenaintern2022 36
  18. Ҏ߱͸ҎԼͷςʔϒϧ͕͋ΔલఏͰ ϢʔβʔͱɺϢʔβʔ͕ॴ༗͢ΔϒϩάΛ؅ཧ͍ͯ͠Δঢ়ଶ CREATE TABLE users ( id BIGINT UNSIGNED NOT

    NULL, name VARCHAR(32) NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id) ); CREATE TABLE blogs ( id BIGINT NOT NULL, -- ϒϩάΛ͍࣋ͬͯΔϢʔβʔͷ id user_id BIGINT NOT NULL, title VARCHAR(50) NOT NULL, description VARCHAR(512), PRIMARY KEY (id) ); #hatenaintern2022 37
  19. INSERT จ (Create)3 ςʔϒϧʹϨίʔυΛ௥Ճ͢Δ INSERT จ INSERT INTO users (id,

    name, created_at) VALUES (1, '͸ͯͳଠ࿠', '2022-08-17 12:00:00'); Query OK, 1 row affected (0.01 sec) MySQLɺPostgreSQL ͰͦΕͧΕผͷॻ͖ํ΋͋Δ 3 MySQL :: MySQL 8.0 ϦϑΝϨϯεϚχϡΞϧ :: 13.2.6 INSERT εςʔτϝϯτ PostgreSQL: Documentation: 14: INSERT #hatenaintern2022 38
  20. SELECT จ (Read)4 ςʔϒϧ͔ΒϨίʔυΛݕࡧ͢Δ SELECT จ -- users ςʔϒϧͷϨίʔυΛશ݅ݕࡧʢऔಘʣ SELECT

    * FROM users; * ͸ʮશͯͷΧϥϜʯͱ͍͏ҙຯɻ* ͷ୅ΘΓʹݸผͷΧϥϜ໊Λࢦఆ͢Δ͜ͱ΋Ͱ͖Δ +----+-----------------+---------------------+ | id | name | created_at | +----+-----------------+---------------------+ | 1 | ͸ͯͳଠ࿠ | 2022-08-17 12:00:00 | +----+-----------------+---------------------+ 1 row in set (0.00 sec) 4 MySQL :: MySQL 8.0 ϦϑΝϨϯεϚχϡΞϧ :: 13.2.10 SELECT εςʔτϝϯτ PostgreSQL: Documentation: 14: SELECT #hatenaintern2022 39
  21. SELECT จ (Read) ߜΓࠐΈʹ͸ WHERE ۟Λ෇͚Δʢޙड़ʣ -- id ͕ 1

    ͷϨίʔυΛݕࡧ SELECT * FROM users WHERE id = 1; +----+-----------------+---------------------+ | id | name | created_at | +----+-----------------+---------------------+ | 1 | ͸ͯͳଠ࿠ | 2022-08-17 12:00:00 | +----+-----------------+---------------------+ 1 row in set (0.00 sec) #hatenaintern2022 40
  22. UPDATE จ (Update)5 طʹଘࡏ͢ΔϨίʔυΛߋ৽͢Δ UPDATE จ -- ߋ৽͍ͨ͠ϨίʔυΛ WHERE ۟Ͱࢦఆ͢Δ

    UPDATE users SET name = '͸ͯͳೋ࿠' WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- ஫ҙ! WHERE ۟Ͱߋ৽ର৅Λࢦఆ͠ͳ͚Ε͹ɺςʔϒϧ಺ͷϨίʔυ͕શ݅ߋ৽͞ΕΔ UPDATE users SET name = '͸ͯͳೋ࿠'; 5 MySQL :: MySQL 8.0 ϦϑΝϨϯεϚχϡΞϧ :: 13.2.13 UPDATE εςʔτϝϯτ PostgreSQL: Documentation: 14: UPDATE #hatenaintern2022 41
  23. DELETE จ (Delete)6 ϨίʔυΛ࡟আ͢Δ DELETE FROM users WHERE id =

    1; Query OK, 1 row affected (0.01 sec) -- ஫ҙ! WHERE ۟Ͱ࡟আର৅Λࢦఆ͠ͳ͚Ε͹ɺςʔϒϧ಺ͷϨίʔυ͕શ݅࡟আ͞ΕΔ DELETE FROM users; 6 MySQL :: MySQL 8.0 ϦϑΝϨϯεϚχϡΞϧ :: 13.2.2 DELETE εςʔτϝϯτ PostgreSQL: Documentation: 14: DELETE #hatenaintern2022 42
  24. σʔλͷऔಘํ๏ ໨త खஈ ৚݅Λ͚ͭͯߜΓࠐΈ WHERE ۟ ιʔτ ORDER BY ۟

    औಘ݅਺ͷ੍ݶ LIMIT ۟ ϖʔδϯά LIMIT ۟ͱ OFFSET ۟Λ૊Έ߹ΘͤΔ άϧʔϐϯά GROUP BY ۟ ςʔϒϧಉ࢜ͷ݁߹ JOIN ۟ #hatenaintern2022 43
  25. ൺֱ ʮ౳͍͠ʯ͸ =ɺʮ౳͘͠ͳ͍ʯ͸ !=ʢ<> ͱ΋ॻ͔ΕΔʣ ϓϩάϥϛϯάݴޠͷ਺஋΍จࣈྻͷΑ͏ʹ >, <, >=, <=

    ͰൺֱͰ͖Δ -- id ͕ 1 ͷϨίʔυΛݕࡧ SELECT * FROM users WHERE id = 1; -- id ͕ 1 Ͱ͸ͳ͍ϨίʔυΛݕࡧ SELECT * FROM users WHERE id != 1; -- created_at ͕ 2022-08-17 12:00:00 ΑΓେ͖͍ʢΑΓޙͷʣϨίʔυΛݕࡧ SELECT * FROM users WHERE created_at > '2022-08-17 12:00:00'; ͨͩ͠ɺNULL ͸஋Ͱ͸ͳ͍ͷͰ column_name = NULL ͱॻ͍ͯ΋ NULL ͷϨίʔυ͸ಘΒΕͳ͍ɻ column_name IS NULL ͱॻ͘͜ͱɻ #hatenaintern2022 45
  26. ൺֱ • AND ΍ OR Ͱෳ਺ͷ৚݅Λ૊Έ߹ΘͤΔ͜ͱ͕Ͱ͖Δ • ಉ͡ΧϥϜʹର͢Δ OR ͸ɺIN

    Ͱ·ͱΊͯදݱ͢Δ͜ͱ΋Ͱ͖Δ -- 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); • LIKE: จࣈྻͷύλʔϯϚονϯά -- name ͕ "͸ͯͳ" ͔Β࢝·ΔϨίʔυΛݕࡧ -- `%` ͸ϫΠϧυΧʔυจࣈͰɺ೚ҙͷ0จࣈҎ্ͷจࣈྻΛද͢ SELECT * FROM users WHERE name LIKE '͸ͯͳ%'; #hatenaintern2022 46
  27. ORDER BY ۟ • ORDER BY ΧϥϜ໊ [ASC/DESC]: ݕࡧ݁Ռͷιʔτ͕ग़དྷΔ •

    ASC ͸ঢॱɺDESC ͸߱ॱ • WHERE ۟ͱ૊Έ߹ΘͤΔͱ͖͸ɺWHERE ۟Λઌʹॻ͔ͳ͍ͱಈ͔ͳ͍ -- user ςʔϒϧͷϨίʔυΛ created_at ͷ߱ॱͰݕࡧ SELECT * FROM users ORDER BY created_at DESC; +----+-----------------+---------------------+ | id | name | created_at | +----+-----------------+---------------------+ | 2 | ͸ͯͳೋ࿠ | 2022-08-17 13:00:00 | | 1 | ͸ͯͳଠ࿠ | 2022-08-17 12:00:00 | +----+-----------------+---------------------+ 2 rows in set (0.00 sec) #hatenaintern2022 47
  28. ϖʔδϯά (LIMIT ۟ / OFFSET ۟) • LIMIT ۟: ݕࡧ݁Ռͷऔಘ݅਺ΛࢦఆͰ͖Δ

    • OFFSET ۟: ࢦఆͨ͠஋͚ͩݕࡧ݁ՌΛಡΈඈ͹͢ • ͜ͷ2ͭΛ૊Έ߹ΘͤͯϖʔδϯάػೳΛ࣮ݱͰ͖Δ -- users ςʔϒϧͷશϨίʔυ͔Β created_at ͷ߱ॱͰݕࡧ͠ɺ3ͷഒ਺෼ͷϨίʔυΛಡΈඈ͹্ͨ͠Ͱ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; #hatenaintern2022 48
  29. ϖʔδϯά ͨͩ͠ɺLIMIT / OFFSET Λ࢖ͬͨϖʔδϯάʹ͸ҎԼͷΑ͏ͳσϝϦοτ͕͋Δ • ύϑΥʔϚϯε • OFFSET Ͱࢦఆ͞Εͨߦ਺·ͰḷΓண͘·Ͱʹશͯͷߦ਺Λ਺͑ͳ͚Ε͹͍͚ͳ͍

    • σʔλ͕ͣΕΔ • ϖʔδϯάΛ͍ͯ͠Δ్தʹߦ͕ࠩ͠ࠐ·ΕͨΓ࡟আ͞ΕͨΓͨ͠ΒɺͣΕͯ͠·͏ • ྫ: 1ϖʔδ໨(1ʙ10݅໨)ΛΫΤϦ → 1݅໨ͷσʔλ͕࡟আ͞ΕΔ → 2ϖʔδ໨(11ʙ20݅໨)ΛΫ ΤϦ → ΋ͱ΋ͱͷ11݅໨͕ͲͪΒͷϖʔδʹ΋ݱΕͳ͍ ͜ΕΒͷσϝϦοτΛආ͚ΔͨΊʹɺ࣍ͷํ๏͕Α͘࢖ΘΕΔ #hatenaintern2022 52
  30. ϖʔδϯά લϖʔδͷ࠷ऴߦͷ஋Λɺϖʔδͷ۠੾ΓͷΩʔͱͯ͠ ࢖͏ ྫ: 1ϖʔδ໨͸ id = 3 ͷϨίʔυ͕࠷ޙͩͬͨͷͰɺ2 ϖʔδ໨Λऔಘ͢Δͱ͖ʹ͸

    id > 3 ͷ΋ͷΛऔಘ͢Δ SELECT * FROM table WHERE id > લͷϖʔδͷ࠷ऴߦͷid ORDER BY id ASC LIMIT 1ϖʔδ͋ͨΓͷ݅਺ ϝϦοτ • ύϑΥʔϚϯε • ΠϯσοΫεʢޙड़ʣ͕͋Ε͹ɺݱࡏͷϖʔδͷߦ ·Ͱૉૣ͘ඈ΂Δ • σʔλͷͣΕ • ى͜Βͳ͍ σϝϦοτ • ೚ҙͷϖʔδ·Ͱඈ΂ͳ͍ • 1ϖʔδ໨͔Β10ϖʔδ໨ʹߦ͘ɺͱ͍͏ͷ͸ෆՄೳ #hatenaintern2022 53
  31. άϧʔϐϯά (GROUP BY ۟) • GROUP BY ۟: σʔλΛάϧʔϐϯά͢Δ ྫ:

    Ϣʔβʔ͕ϒϩάΛ͍͍ͭ࣋ͬͯ͘Δͷ͔ Λूܭ͢Δ blogs ςʔϒϧͷϨίʔυΛ user_id ͝ͱʹά ϧʔϐϯά͠ɺͦΕͧΕͷάϧʔϓ಺ͷ߹ܭ਺ Λग़͢ • COUNT(*) : ৚݅ʹ߹க͢ΔϨίʔυͷ਺Λද ࣔ͢Δ • AS : ΧϥϜʹผ໊ʢΤΠϦΞεʣΛ͚ͭΔ 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) #hatenaintern2022 54
  32. ςʔϒϧಉ࢜ͷ݁߹ (JOIN ۟) • JOIN ۟: ҟͳΔςʔϒϧʹอଘ͞ΕͨσʔλΛ݁߹Ͱ͖Δ -- users ςʔϒϧͱ

    blogs ςʔϒϧΛ݁߹ͯ͠ɺϒϩάͷ࡞ऀͱλΠτϧͷҰཡΛग़͢ SELECT users.name AS user_name, blogs.title AS blog_title FROM blogs JOIN users ON blogs.user_id = users.id; +-----------------+--------------------------+ | user_name | blog_title | +-----------------+--------------------------+ | ͸ͯͳଠ࿠ | ଠ࿠ͷ೔ه | | ͸ͯͳೋ࿠ | ೋ࿠ͷ೔ه | | ͸ͯͳೋ࿠ | ೋ࿠ͷٕज़ϒϩά | +-----------------+--------------------------+ 3 rows in set (0.00 sec) ΫΤϦʹςʔϒϧ͕ෳ਺ొ৔͢Δ৔߹ɺςʔϒϧ໊.ΧϥϜ໊ ͰΧϥϜΛද͢ #hatenaintern2022 56
  33. ςʔϒϧಉ࢜ͷ݁߹ (JOIN ۟) • blogs JOIN users : blog ςʔϒϧʹ

    user ςʔϒϧΛ݁߹ͤ͞Δ • ON blogs.user_id = users.id : blogs.user_id ͱҰக͢Δ஋ͷ users.id Λ࣋ͭϨίʔυΛ݁߹ͤ͞Δ • JOIN ʹ͸ RIGHT JOIN, INNER JOIN, OUTER JOIN ͳͲͷछྨ͕͋ Δ͕ɺ͜͜Ͱ͸৮Εͳ͍ #hatenaintern2022 58
  34. ςʔϒϧઃܭ ϒϩάαʔϏεΛྫʹߟ͑ͯΈΔ • Ϣʔβʔ͕͍Δ • Ϣʔβʔ͸໊લΛ࣋ͭ • Ϣʔβʔ͸ϒϩάΛ࣋ͯΔ • ϒϩάʹ͸λΠτϧ͕෇͘

    • ϒϩάʹ͸આ໌จ͕ॻ͚Δ • Ϣʔβʔ͸ϒϩάΛෳ਺࣋ͯΔ ͲΜͳςʔϒϧ(ද)ͰදͤΔ͔ʁ #hatenaintern2022 62
  35. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά ͜ΕͰ΋ՄೳͰ͸͋Δ͚Ͳ... • Ϣʔβʔ໊ͷ৘ใ͕ॏෳ͍ͯ͠Δ • ؒҧ͑ͯҰ෦͚ͩߋ৽ͯ͠͠·ͬͨΒ੔߹ੑ͕ͳ͘ͳͬͯ͠·͏ • γϯϓϧͳ͜ͱΛ͍͚ͨͩ͠ͳͷʹෳࡶͳΫΤϦΛॻ͘৔ॴ͕Ͱ͖ͯ͠·͏

    • ྫ: Ϣʔβʔ໊ҰཡΛऔಘ͢ΔͨΊʹ͸ SELECT DISTINCT user_name FROM blogs;ͱ͍͏ΫΤϦʹͳΔ • ѻ͏৘ใ͕૿͑ͨΒͲ͏͢Δʁ • ྫ: ϒϩάʹهࣄΛෳ਺͍࣋ͨͤͨ • هࣄΧϥϜΛ௥Ճͨ͠Β͜ͷςʔϒϧͰ΋࣮ݱͰ͖Δ͕ɺهࣄͷ਺͚ͩϨίʔυ͕࡞ΒΕΔͷͰɺͦͷ෼ Ϣʔβʔɾϒϩάͷσʔλͷॏෳ͕૿͑Δ #hatenaintern2022 64
  36. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά ѻ͏৘ใʹண໨ͯ͠ςʔϒϧΛ෼ׂ • ʮϢʔβʔʯͱʮϒϩάʯʹண໨ͯ͠ɺͦΕͧΕΛςʔϒϧͰද ݱ͢Δ • users ςʔϒϧͱ

    blogs ςʔϒϧʹ෼ׂ͢Δ • ͦΕͧΕͷςʔϒϧʹɺߦΛҰҙʹಛఆͰ͖ΔΩʔΛ௥Ճ͢Δ • ͦͷΩʔΛ࢖ͬͯɺςʔϒϧಉ͕࢜ؔ࿈Λ࣋ͯΔΑ͏ʹ͢Δ #hatenaintern2022 65
  37. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά ׬੒ܗ CREATE TABLE users ( id BIGINT

    NOT NULL, name VARCHAR(32) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE blogs ( id BIGINT NOT NULL, user_id BIGINT NOT NULL, title VARCHAR(254) NOT NULL, description VARCHAR(512) NOT NULL, PRIMARY KEY (id) ); #hatenaintern2022 71
  38. ςʔϒϧઃܭ - Ϣʔβʔͱϒϩά Ϣʔβʔ໊ͱϒϩά໊͸ผͷςʔϒϧʹ֨ೲ͞ΕΔΑ͏ʹͳͬͨ ͷͰɺͦΕΒͷσʔλΛҰؾʹऔಘ͢Δʹ͸ JOIN ͕ඞཁʹͳΔ 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 | +-----------------+----------------------------------------+-----------------------------------------------------------------------------+ | ͸ͯͳೋ࿠ | ೋ࿠ͷ೔ه | ೔ৗʹ͍ͭͯॻ͖·͢ | | ͸ͯͳೋ࿠ | ೋ࿠ͷٕज़ϒϩά | ٕज़తͳ࿩୊Λॻ͖·͢ | +-----------------+----------------------------------------+-----------------------------------------------------------------------------+ 2 rows in set (0.00 sec) #hatenaintern2022 72
  39. ςʔϒϧઃܭ - ߪಡػೳ • blogs ʹ subscribe_user_idʢ͜ͷϒϩάΛߪಡ͍ͯ͠ΔϢʔ βʔ IDʣͷΑ͏ͳΧϥϜΛ଍͢ͱʁ •

    blogs.subscribe_user_id ͸1͔ͭ͠஋Λ֨ೲͰ͖ͳ͍ • → ෳ਺ਓ͕ಉ͡ϒϩάΛߪಡ͍ͯ͠Δঢ়ଶΛදݱͰ͖ͳ͍ • ଟରଟͷؔ܎͸ɺͲͪΒ͔ͷςʔϒϧʹΧϥϜΛ௥Ճ͢Δ͚ͩ Ͱ͸දݱͰ͖ͳ͍ #hatenaintern2022 75
  40. ςʔϒϧઃܭ - ߪಡػೳ • users ͱ blogs ͷߪಡͷؔ܎Λද͢ςʔϒϧΛ࡞Δ • subscriptions

    ςʔϒϧ • subscriptions ͔Β users ͱ blogs ͷϨίʔυΛಛఆͰ͖ΔΑ͏ʹɺuser_id ͱ blog_id ΧϥϜΛ࣋ͭ • ͜ͷྫʹݶΒͣɺଟରଟͷؔ܎Λද͢ʹ͸ɺؔ܎Λද͢ςʔϒϧΛ࡞ͬͯΈΔͱ͍͍ #hatenaintern2022 76
  41. ςʔϒϧઃܭ - ߪಡػೳ ׬੒ܗ CREATE TABLE subscriptions ( user_id BIGINT

    NOT NULL, blog_id BIGINT NOT NULL, PRIMARY KEY (user_id, blog_id) ); #hatenaintern2022 77
  42. ςʔϒϧઃܭ - ߪಡػೳ ʮ͸ͯͳଠ࿠ʯͱ͍͏໊લͷϢʔβʔ͕ߪಡ͍ͯ͠ΔϒϩάҰཡΛऔಘ͢Δʹ͸ɺ3ͭͷςʔϒ ϧͷ݁߹͕ඞཁʹͳΔ SELECT blogs.* FROM subscriptions JOIN

    users ON subscriptions.user_id = users.id JOIN blogs ON subscriptions.blog_id = blogs.id WHERE users.name = '͸ͯͳଠ࿠'; +-----+---------+-----------------+--------------------------------+ | id | user_id | title | description | +-----+---------+-----------------+--------------------------------+ | 102 | 2 | ೋ࿠ͷ೔ه | ೔ৗʹ͍ͭͯॻ͖·͢ | +-----+---------+-----------------+--------------------------------+ 1 row in set (0.00 sec) #hatenaintern2022 78
  43. ύϑΥʔϚϯε DB ͸ Web αʔϏεΛ։ൃɾӡӦ͢Δ্ͰύϑΥʔϚϯεͷ໰୊ʹܨ͕Γ΍͍͢ • αʔϏε͕ଓ͘ݶΓσʔλ͸૿Ճ͠ଓ͚Δ • ࠷ॳ͸໰୊ͳ͔ͬͨΫΤϦ͕ɺσʔλྔ͕૿͑ͨ͜ͱͰ࣮ߦʹ͕͔͔࣌ؒΔΑ͏ʹͳͬͯ͠ ·ͬͨΓ

    • DB ͷύϑΥʔϚϯεͷ໰୊͸αʔϏεͷ඼࣭௿Լʹܨ͕Δ • ΫΤϦͷ࣮ߦʹ͕͔͔࣌ؒΔ • → Ϣʔβʹฦ͢Ϩεϙϯε͕஗͘ͳΔ • → Ϣʔβ͕཭Εͯ͠·͏ #hatenaintern2022 81
  44. ΠϯσοΫε ςʔϒϧͷϨίʔυΛߴ଎ʹݕࡧ͢ΔͨΊͷ࢓૊Έ • ϨίʔυΛݕࡧ͢ΔࡍɺΠϯσοΫε͕ͳ͚Ε͹ɺઌ಄ߦ͔ΒςʔϒϧશମΛಡΈऔΓର৅ͷϨίʔυΛݟ෇͚ͩ͢ • ྫ: SELECT * FROM users

    WHERE id = 100000; ͳΒɺςʔϒϧͷઌ಄͔Β1݅ͣͭݟ͍ͯͬͯ id ͕ 100000 ʹҰக ͢Δ΋ͷΛ୳͢ • ΧϥϜʹΠϯσοΫε͕࡞੒͞Ε͍ͯΔ৔߹ɺͦͷΧϥϜͷσʔλ͕ݕࡧʹ࠷దͳσʔλߏ଄Ͱอଘ͞ΕΔ • Α͘࢖ΘΕΔͷ͸ɺB-Tree ͱ͍͏σʔλߏ଄ • ݕࡧର৅ͷΧϥϜ͕ΠϯσοΫεΛ࣋ͭΧϥϜͩͱɺશͯͷϨίʔυΛௐ΂ͣʹݕࡧ͕Ͱ͖Δ • ΠϯσοΫε͕ఆٛ͞Ε͍ͯΕ͹ɺςʔϒϧʹσʔλΛૠೖͨ͠ࡍʹɺࣗಈͰΠϯσοΫεʹ΋σʔλ͕௥Ճ͞Ε Δ #hatenaintern2022 83
  45. ΠϯσοΫε • ໌ࣔతʹ࡞Δํ๏ CREATE INDEX index_name ON table_name (column_name); •

    ੍໿ʹΑΓࣗಈͰ࡞ΒΕΔ • PRIMARY KEY • UNIQUE MySQL Ͱ͸ɺCREATE TABLE จͷதͰΠϯσοΫεΛఆٛ͢Δ͜ͱ΋Ͱ͖Δ CREATE TABLE table_name ( -- (ུ) INDEX index_name (column_name) ); #hatenaintern2022 84
  46. ΠϯσοΫε ΠϯσοΫεͷ஫ҙ఺ • ΠϯσοΫε࡞੒ʹ͸ίετ͕͔͔Δ • Ϩίʔυͷ࡞੒ɾߋ৽ɾ࡟আ࣌ʹΠϯσοΫε΋ߋ৽͢ΔͷͰɺΦʔόʔϔου͕͋Δ • ଟ͘ͷΞϓϦέʔγϣϯͰ͸ ࢀরॲཧ >

    ߋ৽ॲཧ ʹͳΔͷͰ͋·Γ໰୊ͳ͍ • ΠϯσοΫε͕࢖ΘΕͳ͍έʔε΋͋Δ • ྫ: શ݅୳ࡧͷํ͕ૣ͍ • ౎ಓ෎ݝΛද͢ςʔϒϧͩͱɺ47Ϩίʔυ͔͠ͳ͍ͷͰશ݅୳ࡧͨ͠ํ͕ૣ͍ #hatenaintern2022 86
  47. EXPLAIN7 • ΫΤϦͷ࣮ߦܭըΛग़ྗ͢Δํ๏ • ࣮ߦܭը: ΫΤϦʹͲͷΑ͏ͳΠϯσοΫεΛ࢖༻͠ɺͲΜͳॱ൪ͰςʔϒϧΛ݁ ߹͢Δͷ͔ɺͳͲ • ࣮ߦܭը͕Θ͔Ε͹ɺͲͷ෦෼͕ύϑΥʔϚϯεΛѱ͍ͯ͘͠Δ͔Λ஌Δ͜ͱ͕Ͱ͖ Δ

    • ΫΤϦͷઌ಄ʹ EXPLAIN Λ෇͚Δ͜ͱͰɺͦͷΫΤϦͷ࣮ߦܭըΛಘΔ͜ͱ͕Ͱ͖Δ 7 MySQL :: MySQL 8.0 ϦϑΝϨϯεϚχϡΞϧ :: 8.8.1 EXPLAIN ʹΑΔΫΤϦʔͷ࠷దԽ PostgreSQL: Documentation: 14: EXPLAIN #hatenaintern2022 87
  48. EXPLAIN ͜͜Ͱ͸ MySQL ͷྫΛग़͍ͯ͠·͢ -- ΠϯσοΫεͷޮ͔ͳ͍ΫΤϦͷEXPLAIN -- type ALL ͸ϑϧςʔϒϧεΩϟϯɺςʔϒϧશମΛಡΈऔ͍ͬͯΔ͜ͱΛࣔ͢

    = ΠϯσοΫε͕࢖ΘΕ͍ͯͳ͍ EXPLAIN SELECT COUNT(*) FROM blogs WHERE title LIKE '%೔ه'; +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 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) #hatenaintern2022 88
  49. EXPLAIN -- ΠϯσοΫε͕ޮ͘ΫΤϦͷ EXPLAIN -- type range ͸ΠϯσοΫεΛ࢖༻͠ɺಛఆͷൣғʹ͋ΔߦͷΈ͕औಘ͞Ε͍ͯΔ͜ͱΛࣔ͢ EXPLAIN SELECT

    COUNT(*) FROM users WHERE id > 2000; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | users | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 1 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) #hatenaintern2022 89
  50. ऴΘΓʹ ѻ͑ͳ͔ͬͨτϐοΫ΍Ωʔϫʔυʹ͍ͭͯྻڍ͓ͯ͘͠ͷͰɺڵຯ͕͋Ε͹ௐ΂ͯΈ͍ͯͩ͘͞ • SQL ʹ͍ͭͯ • ෳࡶͳΫΤϦ΍ूܭ (αϒΫΤϦ) • ૊ΈࠐΈؔ਺

    • ςʔϒϧઃܭ • ਖ਼نԽ • τϥϯβΫγϣϯ • ACID ಛੑ • ϩοΫ • ϓϩάϥϛϯάݴޠ͔Β SQL Λѻ͏ (෇࿥ʹࢀߟϦϯ Ϋهࡌ) • N+1 ໰୊ • ηΩϡϦςΟ • SQL ΠϯδΣΫγϣϯ #hatenaintern2022 93
  51. Docker Λ࢖ͬͯ MySQL Λૢ࡞͢Δ (1/3) MySQL ͷίϯςφΛىಈ $ 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 ্هͷίϯςφͷ MySQL ʹೖΔ $ docker container exec -it rdbms-bootcamp mysql -uuser -ppasswd bootcamp #hatenaintern2022 95
  52. Docker Λ࢖ͬͯ MySQL Λૢ࡞͢Δ (2/3) ஫ҙ఺ • root ϢʔβʔͰ͸ Docker

    ίϯςφ಺͔ΒͰͳ͍ͱೖΕ·ͤΜ • → ݖݶ؅ཧͷؔ܎ • ެࣜͷΠϝʔδͩͱ mysql client ͔ΒೖΔͱ೔ຊޠ͕ೖྗͰ͖ͳ͍ɾग़ྗ͞Εͳ ͍ • OS ʹ೔ຊޠઃఆ͕ೖ͍ͬͯͳ͍ͨΊ • ҎԼͷํ๏Ͱ೔ຊޠೖྗͰ͖ΔΑ͏ʹͳΔ #hatenaintern2022 96
  53. Docker Λ࢖ͬͯ MySQL Λૢ࡞͢Δ (3/3) ೔ຊޠͷઃఆํ๏ # ίϯςφ಺ʹೖΔ $ docker

    container exec -it rdbms-bootcamp /bin/bash # locale ઃఆΛΠϯετʔϧ͠ɺ೔ຊޠ͕࢖͑ΔΑ͏ʹ͢Δ root@xxx:/$ microdnf install glibc-langpack-ja root@xxx:/$ exit # LANG ؀ڥม਺Λ೔ຊޠʹͯ͠ɺmysql ʹϩάΠϯ $ docker container exec -it rdbms-bootcamp env LANG="ja_JP.utf8" mysql -uuser -ppasswd bootcamp #hatenaintern2022 97
  54. Docker Λ࢖ͬͯ PostgreSQL Λૢ࡞͢Δ PostgreSQL ͷίϯςφΛىಈ $ docker pull postgres:14.5

    $ docker run --name rdbms-bootcamp -e POSTGRES_PASSWORD=passwd postgres:14.5 ্هͷίϯςφͷ PostgreSQL ʹೖΔ $ docker container exec -it rdbms-bootcamp psql -h localhost -U postgres -d postgres #hatenaintern2022 99
  55. ࢀߟϦϯΫ (1/2) RDBMS શൠ • σʔλϕʔε֓࿦ᶗ | ஜ೾େֶΦʔϓϯίʔε΢ΣΞʛTSUKUBA OCW •

    ͋Δఔ౓ DB Λѻ͏͜ͱʹ׳Ε͖͔ͯͯΒݟͯΈΔͱɺཧ࿦ͱ࣮ફ͕߹Θͬͯ͞Α͍ͱࢥ͏ ϦϑΝϨϯε • MySQL 8.0 ϦϑΝϨϯεϚχϡΞϧ • PostgreSQL: Documentation: 14: PostgreSQL 14.5 Documentation • ϦϑΝϨϯεͳͷͰɺࠔͬͨͱ͖ʹࣙॻతʹ࢖͏ͱྑ͍Ͱ͢ • ࣮ࡍʹ࢖͏όʔδϣϯʹ߹ͬͨϚχϡΞϧΛࢀরͨ͠ํ͕ྑ͍Ͱ͢ #hatenaintern2022 101
  56. ࢀߟϦϯΫ (2/2) Go ݴޠ͔ΒσʔλϕʔεΛѻ͏ํ๏ • Accessing databases - The Go

    Programming Language • ϓϩάϥϛϯάݴޠ͔Βѻ͏Ұྫͱͯ͠ Go ͷυΩϡϝϯτΛ঺հ͠ ·͢ • ͜ͷυΩϡϝϯτࣗମ͸ Go ͷυΩϡϝϯτͰ͕͢ɺϓϩάϥϜ͔Β SQL Λѻ͏͜ͱʹ͍ͭͯͷνϡʔτϦΞϧ͔ΒҰൠతͳ࿩୊ɺ஫ҙ ఺ͳͲʹ৮ΕΒΕ·͢ #hatenaintern2022 102