เราวิเคราะห์ฐานข้อมูลและภาษา SQL (ตอนที่ 4 - ตรวจการบ้าน) - "Java project from A to Z"

บทความจากซีรีส์เกี่ยวกับการสร้างโปรเจ็กต์ Java (ลิงก์ไปยังเนื้อหาอื่นอยู่ท้ายสุด) เป้าหมายคือการวิเคราะห์เทคโนโลยีที่สำคัญ ผลลัพธ์คือการเขียนบอทโทรเลข บทความก่อนหน้าและวิเคราะห์การบ้านฐานข้อมูล: 1 , 2 , 3 . ถึงทุกคนที่มีความอดทนและความอดทน ถึงทุกคนที่ติดตามบทความที่สี่กับฉัน - ทำได้ดีมาก อย่างที่เขาว่ากัน คนที่เดินก็เชี่ยวชาญถนนได้ สัปดาห์นี้บทความสุดท้ายเกี่ยวกับฐานข้อมูลจะเผยแพร่ ซึ่งเราจะพูดถึงประเภทของความสัมพันธ์และการเข้าร่วม แต่ก่อนที่เราจะจัดการกับข้อมูลใหม่ เรามาตรวจการบ้านกันดีกว่า... ฉันรู้สึกเหมือนเป็นครูจริงๆ อย่าโกรธฉันเลย ฉันไม่มีการศึกษาด้านการสอน นั่นแน่นอน ตั้งแต่สัปดาห์ที่แล้ว การตรวจสอบรีโมทคอนโทรลอย่างละเอียดได้กินส่วนแบ่งของเนื้อหาอย่างมาก ฉันจึงตัดสินใจแบ่งการวิเคราะห์การบ้านและการทบทวนเนื้อหาใหม่ออกเป็นสองส่วน

จริงๆแล้วการวิเคราะห์การบ้าน

ดีใจมากที่มีคนทำ PD และพูดถึงเรื่องนี้ ที่นี่หนาว! ฉันมั่นใจมากที่สุดเท่าที่จะเป็นไปได้ว่าการอ่านโดยไม่รวบรวมความรู้เป็นหนทางไปที่ไหนเลย ดังนั้นทุกคนที่ทำหรือลองทำก็ให้ความเคารพ ฉันขอเตือนคุณถึงเงื่อนไขของงาน:

1. ทำความเข้าใจกับ ตัวดำเนินการHAVINGและเขียนแบบสอบถามตัวอย่างสำหรับตารางจากตัวอย่างของเรา หากคุณต้องการเพิ่มช่องบางช่องหรือค่าอื่นๆ เพื่อให้ชัดเจนยิ่งขึ้น ให้เพิ่มช่องเหล่านั้น หากใครต้องการ เขียนวิธีแก้ปัญหาตัวอย่างของคุณในความคิดเห็น เพื่อที่ฉันจะได้ตรวจสอบได้หากมีเวลา
2. ติดตั้ง MySQL Workbench เพื่อทำงานกับฐานข้อมูลผ่าน UI ฉันคิดว่าเราฝึกฝนการทำงานจากคอนโซลมามากพอแล้ว เชื่อมต่อกับฐานข้อมูล หากคุณใช้อย่างอื่นเพื่อทำงานกับฐานข้อมูล คุณสามารถข้ามงานนี้ได้เลย ฉันจะใช้เฉพาะ MySQL Workbench เท่านั้น
3. เขียนคำขอรับโดยใช้ข้อมูลของเรา:
   1. ประเทศที่เล็กที่สุด/มีประชากรมากที่สุด
   2. จำนวนประชากรโดยเฉลี่ยในประเทศ
   3. จำนวนประชากรโดยเฉลี่ยในประเทศที่ชื่อลงท้ายด้วย "a";
   4. จำนวนประเทศที่มีประชากรมากกว่าสี่ล้านคน
   5. จัดเรียงประเทศโดยการลดจำนวนประชากร
   6. จัดเรียงประเทศตามชื่อตามลำดับธรรมชาติ

มาพูดถึง HAVING กันดีกว่า

การรู้ว่ามีผู้ดำเนินการสามารถช่วยให้คุณผ่านการสัมภาษณ์ได้มากกว่าหนึ่งครั้งซึ่งอาจมีปัญหาเกี่ยวกับ SQL ดังนั้นการทำความเข้าใจจึงเป็นสิ่งสำคัญอย่างยิ่ง มันเกิดขึ้นจนคุณไม่สามารถใช้เงื่อนไขสำหรับการรวมฟังก์ชัน (SUM, MIN, MAX, AVG) นอกจากนี้ HAVING ยังใช้สำหรับฟิลด์ที่ถูกจัดกลุ่มอีกด้วย มันหมายความว่าอะไร? ตัวอย่างเช่น หากเราต้องการได้รับประเทศที่มีจำนวนประชากรโดยเฉลี่ยในเมืองมากกว่า 50,000 คน เราจะไม่สามารถทำได้โดยไม่ใช้ HAVING ตามที่ฉันเข้าใจ สิ่งนี้เกิดขึ้นเนื่องจากการรวมเกิดขึ้นหลังจากดำเนินการคำสั่ง WHERE และเป็นไปไม่ได้ที่จะเพิ่มค่าการรวมลงไปซึ่งจะถูกคำนวณในภายหลัง แม้ว่าความคิดเห็นของฉันจะไม่ได้เพิ่มความเข้าใจมากนัก แต่คุณก็สามารถยอมรับสิ่งนี้เป็นข้อเท็จจริงและดำเนินการต่อไป ในการเขียนโปรแกรม มักจะเกิดขึ้นว่าหากมีบางสิ่งที่ไม่ชัดเจนในช่วงเวลาหนึ่ง อาจหมายความว่าสมองยังไม่ได้ย่อยมัน นอนคิดแบบนี้ แล้ววันรุ่งขึ้นทุกอย่างจะชัดเจนขึ้น

การติดตั้ง MySQL Workbench

ฉันจะใช้ Workbench สำหรับการสืบค้นที่นี่และต่อไป ฉันจะแสดงสิ่งที่คุณต้องติดตั้งและสร้างการเชื่อมต่อกับฐานข้อมูล นี่เป็นผลิตภัณฑ์จาก Oracle ดังนั้นคุณเพียงแค่ต้องไปที่เว็บไซต์และเลือกเวอร์ชันและระบบปฏิบัติการที่คุณต้องการ โดยไปที่ลิงก์นี้ : คุณสามารถเลือกระบบปฏิบัติการที่คุณต้องการได้ที่นี่ คลิกดาวน์โหลดแต่แทนที่จะดาวน์โหลด เราจะเห็นหน้าต่างนี้: อย่าหลงทาง เพียงมองหาปุ่มที่เรียกว่าไม่ ขอบคุณ เพียงแค่เริ่มการดาวน์โหลดของฉันและการดาวน์โหลดจะเริ่มขึ้น ทำไมพวกเขาถึงทำอย่างนั้น? อาจเป็นไปได้ว่าเพื่อให้มีคนลงทะเบียนกับพวกเขามากขึ้น สิ่งนี้จึงไม่ใช่เรื่องสำคัญสำหรับเรา หลังจากดาวน์โหลดสำเร็จ ให้รันไฟล์การติดตั้ง บน MacOS มีลักษณะดังนี้: เพียงลากไอคอน เพียงเท่านี้การติดตั้งก็เสร็จสมบูรณ์ ไม่ยากเท่ากับการติดตั้ง MySQL ใช่ไหม? หรือเราเพิ่งคุ้นเคยและมีประสบการณ์มากขึ้น ;) ส่วนที่สองของงานนี้คือการสร้างการเชื่อมต่อกับฐานข้อมูลของเรา สิ่งที่จำเป็นสำหรับสิ่งนี้? คลิกเครื่องหมายบวกถัดจากการเชื่อมต่อ MySQL: ในหน้าต่างที่ปรากฏขึ้น ให้ป้อนข้อมูลที่จำเป็น:

• ชื่อการเชื่อมต่อ - ชื่อของการเชื่อมต่อของเรา เขียนชื่อให้ชัดเจนที่สุดเพื่อไม่ให้เกิดปัญหาในการระบุตัวตนในภายหลัง ฉันให้การเชื่อมต่อนี้ชื่อJRTB_DB ;
• ชื่อโฮสต์ - จะถูกตั้งค่าเป็นโลคัล127.0.0.1 (หรือที่รู้จักในชื่อโลคัลโฮสต์) ในกรณีของเรา ไม่จำเป็นต้องเปลี่ยนแปลงใด ๆ เนื่องจากมีการติดตั้งฐานข้อมูลบนคอมพิวเตอร์ แต่ถ้าฐานข้อมูลอยู่ที่อื่น โฮสต์ (ip ของเครื่องที่ฐานข้อมูลทำงานอยู่) จะต้องมีการเปลี่ยนแปลงตามนั้น
• ชื่อผู้ใช้ - คุณสามารถระบุผู้ใช้ของคุณได้หากจำเป็น หากคุณยังไม่ได้เพิ่มสิ่งใดในคีย์นี้ ให้ปล่อยไว้โดยไม่มีการเปลี่ยนแปลง
• รหัสผ่าน - คลิกจัดเก็บในพวงกุญแจแล้วตั้งรหัสผ่านที่คุณตั้งไว้สำหรับตัวคุณเอง ฉันทิ้งทุกอย่างให้เรียบง่าย- root

หากต้องการตรวจสอบว่าการเชื่อมต่อใช้งานได้หรือไม่ให้คลิกทดสอบการเชื่อมต่อ : ถ้าทุกอย่างถูกต้องผลลัพธ์จะเกิดขึ้นไม่นาน: ตอนนี้เรามีการเชื่อมต่อที่บันทึกไว้ในฐานข้อมูลและเราไม่จำเป็นต้องสร้างการเชื่อมต่อและกรอก ในชื่อและรหัสผ่านทุกครั้ง และความสุขนี้จะมีลักษณะดังนี้: เราเข้าสู่การเชื่อมต่อที่สร้างขึ้นใหม่และเห็นหน้าต่างคำขอ เพื่อให้แน่ใจว่าทุกอย่างถูกต้อง มาตรวจสอบรายการฐานข้อมูล ไปที่ของเราและรับข้อมูลทั้งหมดเกี่ยวกับเมืองต่างๆ: และที่นี่ เราได้รับ carte blanche สำหรับสิ่งที่เราต้องการ ส่วนแรกรับผิดชอบสคริปต์ที่เราป้อน ต่อไป ในResult Gridเราจะเห็นผลลัพธ์ของการดำเนินการครั้งล่าสุดในสคริปต์ และAction Outputจะแสดงรายการการดำเนินการและผลลัพธ์ ฉันอยากจะบอกคุณว่ามีประโยชน์มาก: ด้วยความช่วยเหลือนี้คุณสามารถตรวจสอบความเร็วในการประมวลผลของสคริปต์บางตัวได้ ทำไมมันถึงสำคัญ? ปัญหาที่พบบ่อยที่สุดประการหนึ่งเกี่ยวกับความเร็วของการดำเนินการงานในแอปพลิเคชันคือความเร็วของการดำเนินการค้นหาในฐานข้อมูล ที่นี่คุณสามารถตรวจสอบด้วยมือได้อย่างรวดเร็วและสะดวก

เราเขียนคำขอที่จำเป็น

เรามีคำขอเพียง 7 ข้อที่ต้องทำ ลุยเลย!

1. รับประเทศที่มีประชากรมากที่สุด ที่นี่คุณสามารถไปได้อย่างมีไหวพริบและใช้หลายเส้นทาง:

• ตามตารางของประเทศ

จากนั้นเราเพียงแค่ต้องจัดเรียงข้อความค้นหาตามประชากรและบันทึกเพียงบันทึกเดียว ในการดำเนินการนี้ คุณต้องเพิ่ม ตัวดำเนินการ LIMIT ที่ท้ายสคริปต์ และระบุปริมาณที่ต้องการ: $ SELECT * FROM Country ORDER BY ประชากร DESC LIMIT 1;

• ตามตารางเมือง

ทุกอย่างน่าสนใจมากขึ้นที่นี่เพราะคำขอจะซับซ้อนมากขึ้น แต่ก็น่าสนใจยิ่งขึ้นด้วย เนื่องจากเรายังไม่ทราบเกี่ยวกับการเข้าร่วม เราจึงได้เฉพาะรหัสประเทศเท่านั้น: $ SELECT country_id, SUM(population) FROM city GROUP BY country_id ORDER BY SUM(population) DESC LIMIT 1; ที่นี่เราทำสิ่งที่ยอดเยี่ยม - เรารวบรวมผลรวมของประชากรของเมืองที่รู้จักทั้งหมดในแต่ละประเทศ จัดเรียงตามผลรวมนี้ และนำองค์ประกอบแรกมา มันเยี่ยมยอดเหรอ? ดีใจ :D หลังจากนี้คุณรู้สึกเหมือนเป็นกูรูด้านแบบสอบถามทันที... (ไม่นานแน่นอน))

2. รับประเทศที่เล็กที่สุด ที่นี่คุณสามารถไปได้หลายเส้นทางอย่างมีไหวพริบ

ในกรณีนี้ทุกอย่างจะเหมือนกันทุกประการ ข้อแตกต่างเพียงอย่างเดียวคือการเรียงลำดับจะกลับรายการ นั่นคือทั้งหมด ดังนั้นฉันจึงเขียนคำขอ:

• ตามตารางเมือง

$ SELECT Country_id, SUM(ประชากร) จากเมือง GROUP BY Country_id ORDER BY SUM(ประชากร) LIMIT 1;

• ตามตารางของประเทศ

$ SELECT * จากประเทศ เรียงลำดับตามจำนวนประชากร จำกัด 1; แล้วเห็นผลด้วยตัวคุณเอง!

3. จำนวนประชากรโดยเฉลี่ยในประเทศ

ขอย้ำอีกครั้งว่าข้อกำหนดทางเทคนิคนั้นไม่ถูกต้องราวกับว่าผู้จัดการเขียนไว้... ทำไมฉันถึงตัดสินใจเรื่องนี้? เพราะยังไม่ชัดเจนว่าจะเข้างานโต๊ะไหน แต่นี่เป็นเรื่องปกติ: ไม่มีงานที่ทุกอย่างจะชัดเจนและเข้าใจได้ทันที ดังนั้นคุณต้องอ่านงานให้ละเอียด และหากคุณมีคำถาม ให้ถามพวกเขาทันที! ถูกต้องเป็นข้อสังเกต โดยคำนึงถึงข้อมูลที่เรามีในฐานข้อมูล เราจะทำการค้นหาโดยใช้ข้อมูลจากเมืองต่างๆ เมื่อต้องการทำเช่นนี้ ให้เขียนแบบสอบถามต่อไปนี้: $ SELECT Country_id, AVG(population) FROM city GROUP BY Country_id; ง่ายมากที่นี่: เราใช้ฟังก์ชัน AVG และจัดกลุ่มบันทึกเมืองของเราตามประเทศ

4. จำนวนประชากรโดยเฉลี่ยในประเทศที่ชื่อลงท้ายด้วย "a"

จะมีการเปลี่ยนแปลงเล็กน้อยที่นี่เมื่อมีการร้องขอ เราจำเป็นต้องเพิ่มการกรองตามชื่อก่อนที่จะทำการจัดกลุ่ม ฉันทำการบ้านเช่นเดียวกับนักเรียนทุกคน ก่อนที่จะเผยแพร่บทความนี้ และฉันเข้าใจว่าปัญหานี้ไม่สามารถแก้ไขได้หากไม่เข้าร่วม ทำไม เพราะนอกจากรหัสประเทศแล้ว เรายังต้องได้รับชื่อของเขาด้วย และสิ่งนี้ไม่สามารถทำได้หากไม่รวมสองตารางเป็นหนึ่งระเบียน ดังนั้นฉันจะทำงานนี้แน่นอน แต่นี่คือวงกบของฉัน...))) ฉันต้องการทำงานโดยใช้ LIKE ในคำขอ...) $ SELECT ci.country_id, AVG(ci.population ) จากเมือง ci INNER JOIN Country co ON ci.country_id = co.id โดยที่ชื่อร่วม LIKE "%a" GROUP BY Country_id; เกิดอะไรขึ้นที่นี่? ขั้นแรก เรารวมบันทึกจากตารางเมืองและประเทศโดยใช้คีย์ต่างประเทศ Country_id ซึ่งกรองตามชื่อประเทศเพื่อให้ลงท้ายด้วย "a" จากนั้นจึงจัดกลุ่มตาม Country_id เท่านั้น

5. จำนวนประเทศที่มีประชากรมากกว่าสี่ล้านคน

ที่นี่เราเพียงแค่ต้องใช้ฟังก์ชัน COUNT และเพิ่มการกรองประชากร: $ SELECT COUNT(*) จากประเทศ WHERE ประชากร > 4000000; จึงพบว่ามี 3 ประเทศดังกล่าว ถูกต้องไหม? ใช่ มีเพียงมอลโดวาเท่านั้นที่ไม่ผ่านเหตุการณ์สำคัญนี้

6. เรียงลำดับประเทศตามจำนวนประชากรที่ลดลง

ในการดำเนินการนี้ เราจำเป็นต้องใช้ตัวดำเนินการ ORDER BY ที่เราทราบอยู่แล้ว แต่โปรดจำไว้ว่าตามค่าเริ่มต้น การเรียงลำดับจะเป็นไปตามลำดับตามธรรมชาติ สำหรับตัวเลข หมายความว่าเรียงลำดับจากน้อยไปมาก สำหรับสตริง หมายความว่าเรียงลำดับโดยเริ่มจากอักขระตัวแรก หากเราต้องการการเรียงลำดับจากมากไปหาน้อย เราต้องการค่าผกผันของการเรียงลำดับตามธรรมชาติ: $ SELECT * FROM country ORDER BY ประชากร DESC;

7. จัดเรียงประเทศตามชื่อตามลำดับธรรมชาติ

นี่คือจุดที่ความรู้เกี่ยวกับระเบียบธรรมชาติจะมีประโยชน์ เนื่องจากเป็นค่าเริ่มต้น มันจึงเป็นเรื่องง่ายสำหรับเรา: $ SELECT * FROM country ORDER BY name;

แทนที่จะเป็นเอาท์พุต

มันบังเอิญว่าขนาดของวิธีแก้ปัญหาการบ้านจะใหญ่มาก ดังนั้นเราจะมีข้อยกเว้น: ฉันกำลังเผยแพร่บทความนี้ด้วยเช็ค และในวันศุกร์ฉันจะเผยแพร่เนื้อหาใหม่ที่มีการเชื่อมต่อและเข้าร่วม ขอบคุณทุกคนที่อ่าน ถึงวันศุกร์!

รายการเนื้อหาทั้งหมดในซีรีส์นี้อยู่ที่ตอนต้นของบทความนี้