บทความจากซีรีส์เกี่ยวกับการสร้างโปรเจ็กต์ Java (ลิงก์ไปยังเนื้อหาอื่นอยู่ท้ายสุด) เป้าหมายคือการวิเคราะห์เทคโนโลยีที่สำคัญ ผลลัพธ์คือการเขียนบอทโทรเลข
สวัสดีทุกคน ผู้อาวุโสในอนาคตและผู้อาวุโสด้านซอฟต์แวร์ อย่างที่บอกไปแล้วในตอนที่แล้ว ( ตรวจการบ้าน ) วันนี้จะมีเนื้อหาใหม่ สำหรับผู้ที่มีความกระตือรือร้นเป็นพิเศษ ผมได้ขุดการบ้านที่น่าสนใจไว้ เพื่อให้ผู้ที่รู้ทุกอย่างแล้ว และผู้ที่ไม่รู้แต่อยากเปิด Google ก็สามารถฝึกฝนและทดสอบทักษะของตนเองได้
วันนี้เราจะพูดถึงประเภทของการเชื่อมต่อและการเข้าร่วม
เพื่อให้เข้าใจว่าความสัมพันธ์คืออะไร คุณต้องจำไว้ว่า Foreign Key คืออะไร สำหรับผู้ที่ลืมยินดีต้อนรับสู่จุดเริ่มต้นของซีรีส์
นี่คือภาพแรกจากอินเทอร์เน็ต ซึ่งแสดงว่ามีลูกค้าและคำสั่งซื้อของพวกเขา เป็นเรื่องสมเหตุสมผลที่ลูกค้ารายหนึ่งสามารถมีคำสั่งซื้อได้มากกว่าหนึ่งรายการ มีแบบหนึ่งต่อกลุ่ม :) หรืออีกตัวอย่างหนึ่ง:
มีสามตาราง: ผู้จัดพิมพ์ ผู้แต่ง และหนังสือ สำนักพิมพ์ทุกรายที่ไม่อยากล้มละลายและอยากประสบความสำเร็จมีนักเขียนมากกว่าหนึ่งคนเห็นด้วยไหม? ในทางกลับกันผู้แต่งแต่ละคนอาจมีหนังสือมากกว่าหนึ่งเล่ม - ก็ไม่มีข้อสงสัยเช่นกัน และนี่หมายถึงการเชื่อมโยงระหว่างผู้เขียนคนหนึ่งกับหนังสือหลายเล่ม ผู้จัดพิมพ์หนึ่งคนต่อผู้เขียนหลายคน มีตัวอย่างอีกมากมายที่สามารถให้ได้ ความยากลำบากในการรับรู้ในตอนแรกอาจอยู่ที่การเรียนรู้ที่จะคิดเชิงนามธรรมเท่านั้น: การมองโต๊ะจากภายนอกและการโต้ตอบของพวกเขา
เห็นได้ชัดว่าจะตั้งค่าประเภทการเชื่อมต่อก่อนหน้าใน SQL อย่างไร: เราแค่ส่ง ID ของรายการนั้นไปยังบันทึกเหล่านั้นซึ่งมีอยู่มากมายใช่ไหม ประเทศหนึ่งให้ ID ของตนเป็นรหัสต่างประเทศแก่หลายเมือง จะทำอย่างไรกับ ความสัมพันธ์ แบบกลุ่มต่อกลุ่ม ? วิธีนี้ไม่เหมาะ เราจำเป็นต้องเพิ่มตารางอื่นที่จะเชื่อมต่อทั้งสองตาราง ตัวอย่างเช่น ไปที่ MySQL สร้างฐานข้อมูลใหม่ manytomany สร้างสองตาราง ผู้แต่ง และหนังสือ ซึ่งจะมีเพียงชื่อและ IDs เท่านั้น: CREATE DATABASE manytomany; ใช้หลาย ๆ อย่าง; สร้างผู้เขียนตาราง (id INT AUTO_INCREMENT, ชื่อ VARCHAR(100), PRIMARY KEY (id) ); สร้างหนังสือตาราง (id INT AUTO_INCREMENT, ชื่อ VARCHAR(100), PRIMARY KEY (id) );
ตอนนี้เรามาสร้างตารางที่สามซึ่งจะมีคีย์ต่างประเทศสองตัวจากตารางผู้เขียนและตารางหนังสือของเรา และลิงก์นี้จะไม่ซ้ำกัน นั่นคือจะไม่สามารถเพิ่มบันทึกด้วยคีย์เดียวกันสองครั้งได้: CREATE TABLE authors_x_books ( book_id INT NOT NULL, author_id INT NOT NULL, FOREIGN KEY (book_id) REFERENCES book(id), FOREIGN KEY (author_id) REFERENCES author (id ), UNIQUE (book_id, author_id) );
ที่นี่เราใช้คุณสมบัติใหม่หลายประการที่จำเป็นต้องแสดงความคิดเห็นแยกกัน:
ผลลัพธ์จะเป็นไปตามธรรมชาติ - ข้อผิดพลาด ก็จะมีการซ้ำกัน รายการจะไม่ถูกบันทึก นี่คือวิธีสร้างการเชื่อมต่อแบบกลุ่มต่อกลุ่ม... ทั้งหมดนี้เจ๋งและน่าสนใจมาก แต่มีคำถามเชิงตรรกะเกิดขึ้น: จะรับข้อมูลนี้ได้อย่างไร? จะรวมข้อมูลจากตารางต่างๆ เข้าด้วยกันแล้วได้คำตอบเดียวได้อย่างไร นี่คือสิ่งที่เราจะพูดถึงในส่วนถัดไป))
ที่นี่แม้ว่าชื่อจะเหมือนกัน แต่คุณจะเห็นได้อย่างชัดเจนว่าสาขาของเมืองมาก่อน แล้วตามด้วยสาขาของประเทศ แต่สองรายการที่เราเพิ่มด้านบนไม่มีอยู่ เพราะนั่นคือวิธีการทำงานของ INNER JOIN
มีรายการใหม่เกี่ยวกับทบิลิซีและทุกสิ่งที่เกี่ยวข้องกับประเทศถือเป็นโมฆะ มักจะใช้วิธีนี้
ตอนนี้ชัดเจนว่าในกรณีนี้จะไม่มีทบิลิซี แต่เราจะมีอุซเบกิสถาน อะไรแบบนั้น…))
ที่นี่ทุกอย่างจะแสดงในรูปแบบของชุด แต่ละวงกลมคือตาราง และสถานที่ที่มีการทาสีทับคือส่วนที่จะแสดงใน SELECT มาดูกัน:

ประเภทของความสัมพันธ์ในฐานข้อมูล

หนึ่งต่อหลาย
จำตัวอย่างของเรากับประเทศและเมืองต่างๆ ชัดเจนว่าเมืองต้องมีประเทศ จะเชื่อมโยงประเทศกับเมืองได้อย่างไร? จำเป็นต้องแนบตัวระบุเฉพาะ (ID) ของประเทศที่เมืองนั้นอยู่ให้กับแต่ละเมือง: เราได้ทำสิ่งนี้ไปแล้ว สิ่งนี้เรียกว่าการเชื่อมต่อประเภทใดประเภทหนึ่ง - แบบหนึ่งต่อหลายรายการ (เป็นการดีที่จะรู้เวอร์ชันภาษาอังกฤษ - แบบหนึ่งต่อกลุ่ม) ในการถอดความเราสามารถพูดได้ว่า: หลายเมืองสามารถอยู่ในประเทศเดียวได้ นั่นคือวิธีที่คุณควรจดจำ: ความสัมพันธ์แบบหนึ่งต่อกลุ่ม จนถึงตอนนี้ก็ชัดเจนแล้วใช่ไหม? ถ้าไม่เช่นนั้น

หนึ่งต่อหนึ่ง (หนึ่งต่อหนึ่ง)
นี่อาจกล่าวได้ว่าเป็นกรณีพิเศษของการสื่อสารแบบหนึ่งต่อกลุ่ม สถานการณ์ที่หนึ่งระเบียนในตารางหนึ่งเกี่ยวข้องกับเพียงระเบียนเดียวในอีกตารางหนึ่ง มีตัวอย่างอะไรบ้างจากชีวิต? ถ้าเราแยกสามีภรรยาออก เราก็สามารถพูดได้ว่ามีความสัมพันธ์แบบหนึ่งต่อหนึ่งระหว่างสามีและภรรยา แม้ว่าเราจะบอกว่าอนุญาตให้มีสามีภรรยาหลายคนได้ แต่ภรรยาแต่ละคนก็ยังมีสามีได้เพียงคนเดียวเท่านั้น เช่นเดียวกันอาจกล่าวได้เกี่ยวกับผู้ปกครอง แต่ละคนสามารถมีบิดาผู้ให้กำเนิดได้เพียงคนเดียวและมารดาผู้ให้กำเนิดได้เพียงคนเดียวเท่านั้น ความสัมพันธ์แบบหนึ่งต่อหนึ่งที่ชัดเจน ในขณะที่ฉันกำลังเขียนสิ่งนี้ มีความคิดเกิดขึ้นกับฉัน: ทำไมจึงแบ่งความสัมพันธ์แบบหนึ่งต่อหนึ่งออกเป็นสองบันทึกในตารางที่แตกต่างกัน ในเมื่อพวกเขามีความสัมพันธ์แบบหนึ่งต่อหนึ่งอยู่แล้ว? ฉันคิดคำตอบขึ้นมาเอง บันทึกเหล่านี้อาจเชื่อมโยงกับบันทึกอื่นด้วยวิธีอื่น ฉันกำลังพูดถึงอะไร? อีกตัวอย่างหนึ่งของการเชื่อมต่อแบบหนึ่งต่อหนึ่งคือระหว่างประเทศกับประธานาธิบดี เป็นไปได้ไหมที่จะเขียนข้อมูลทั้งหมดเกี่ยวกับประธานาธิบดีลงในตาราง "ประเทศ"? ใช่ คุณทำได้ SQL จะไม่พูดอะไรสักคำ แต่ถ้าคุณคิดว่าประธานาธิบดีเป็นคนด้วย... และเขาอาจมีภรรยา (ความสัมพันธ์แบบตัวต่อตัวอีกแบบหนึ่ง) และลูก ๆ (ความสัมพันธ์แบบตัวต่อตัวอีกแบบหนึ่ง) แล้วปรากฎว่ามันจะเป็น จำเป็นต้องเชื่อมโยงประเทศกับภรรยาและลูกของประธานาธิบดี…. ฟังดูบ้าใช่มั้ย? :D อาจมีตัวอย่างอื่นๆ อีกมากมายสำหรับการเชื่อมต่อนี้ นอกจากนี้ ในสถานการณ์เช่นนี้ คุณสามารถเพิ่มคีย์ต่างประเทศให้กับทั้งสองตารางได้ ซึ่งแตกต่างจากความสัมพันธ์แบบหนึ่งต่อกลุ่มหลายต่อหลาย
จากชื่อแล้วคุณสามารถเดาได้ว่าเราจะพูดถึงอะไร บ่อยครั้งในชีวิตและเราตั้งโปรแกรมชีวิตของเรา มีสถานการณ์ที่การเชื่อมต่อประเภทข้างต้นไม่เพียงพอที่จะอธิบายสิ่งที่เราต้องการ เราได้พูดคุยเกี่ยวกับผู้จัดพิมพ์ หนังสือ และผู้แต่งแล้ว มีความเชื่อมโยงมากมายที่นี่... สิ่งพิมพ์แต่ละฉบับสามารถมีผู้เขียนได้หลายคน - การเชื่อมโยงแบบหนึ่งต่อกลุ่ม ในเวลาเดียวกัน ผู้เขียนแต่ละคนอาจมีสำนักพิมพ์หลายสำนัก (ทำไมไม่ นักเขียนได้รับการตีพิมพ์ในที่เดียว ทะเลาะเรื่องเงิน ไปสำนักพิมพ์อื่น เป็นต้น) และนี่เป็นความสัมพันธ์แบบหนึ่งต่อกลุ่มอีกครั้ง หรือสิ่งนี้: ผู้แต่งแต่ละคนสามารถมีหนังสือได้หลายเล่ม แต่หนังสือแต่ละเล่มก็สามารถมีผู้แต่งได้หลายคนเช่นกัน อีกครั้งหนึ่ง ความสัมพันธ์แบบหนึ่งต่อกลุ่มระหว่างผู้แต่งกับหนังสือ หนังสือและผู้แต่ง จากตัวอย่างนี้ เราสามารถสรุปได้อย่างเป็นทางการมากขึ้น:
ถ้าเรามีสองตาราง A และ B A สามารถเกี่ยวข้องกับ B เหมือนกับหนึ่งต่อหลาย ๆ คน แต่ B ยังสามารถเกี่ยวข้องกับ A ได้เหมือนกับที่ใครคนหนึ่งเกี่ยวข้องกับหลาย ๆ คน ซึ่งหมายความว่าพวกเขามีความสัมพันธ์แบบกลุ่มต่อกลุ่ม |


- NOT NULL หมายความว่าจะต้องกรอกข้อมูลในช่องนี้เสมอ และหากไม่เป็นเช่นนั้น SQL จะบอกเรา
- UNIQUE บอกว่าเขตข้อมูลหรือกลุ่มเขตข้อมูลต้องไม่ซ้ำกันในตาราง บ่อยครั้งเกิดขึ้นที่นอกเหนือจากตัวระบุที่ไม่ซ้ำกันแล้ว อีกหนึ่งฟิลด์จะต้องไม่ซ้ำกันสำหรับแต่ละเรกคอร์ด และ UNIQUE เป็นผู้รับผิดชอบเรื่องนี้อย่างแท้จริง

การเชื่อมต่อ (ร่วม)
ในส่วนที่แล้ว ผมเตรียมให้คุณเข้าใจได้ทันทีว่า join คืออะไร และจะใช้ได้ที่ไหน เพราะฉันเชื่อมั่นอย่างลึกซึ้งว่าทันทีที่ความเข้าใจเกิดขึ้น ทุกอย่างจะง่ายมากในทันที และบทความทั้งหมดเกี่ยวกับการรวมจะชัดเจนราวกับดวงตาของเด็กทารก :D โดยทั่วไปแล้ว การรวมจะได้รับผลลัพธ์จากหลายตารางด้วยวิธี ของ JOIN (เข้าร่วมจากภาษาอังกฤษเข้าร่วม) เพียงเท่านี้...) และหากต้องการเข้าร่วม คุณต้องระบุฟิลด์ที่จะรวมตารางเข้าด้วยกัน ปีศาจไม่น่ากลัวเหมือนที่วาดไว้ใช่ไหม?) ต่อไป เราจะมาพูดถึงประเภทของการรวมและวิธีใช้งานกัน การรวมมีหลายประเภท และเราจะไม่พิจารณาทั้งหมด เฉพาะสิ่งที่เราต้องการจริงๆ นั่นเป็นเหตุผลที่เราไม่สนใจการเชื่อมที่แปลกใหม่เช่น Cross และ Natural ฉันลืมไปโดยสิ้นเชิงเราต้องจำความแตกต่างอีกอย่างหนึ่ง: ตารางและฟิลด์สามารถมีนามแฝงได้ - นามแฝง ใช้สำหรับการเข้าร่วมอย่างสะดวก ตัวอย่างเช่น คุณสามารถทำสิ่งนี้: SELECT * FROM table1; หากแบบสอบถามมักจะใช้ table1 คุณสามารถตั้งชื่อแทนได้: SELECT* FROM table1 as t1; หรือเขียนง่ายกว่า: SELECT * FROM table1 t1; และต่อมาในการสืบค้น จะสามารถใช้t1เป็นนามแฝงสำหรับตารางนี้ได้เข้าร่วมภายใน
การเข้าร่วมที่ธรรมดาและง่ายที่สุด มันบอกว่าเมื่อเรามีสองตารางและเขตข้อมูลที่สามารถรวมเข้าด้วยกันได้ บันทึกทั้งหมดที่มีความสัมพันธ์อยู่ในสองตารางจะถูกเลือก มันยากที่จะพูดอย่างใด ลองดูตัวอย่าง: มาเพิ่มหนึ่งระเบียนลงในฐานข้อมูลเมืองของเรากัน หนึ่งรายการสำหรับเมืองและอีกหนึ่งรายการสำหรับประเทศ: $ INSERT INTO country VALUES(5, "Uzbekistan", 34036800); และ $ INSERT INTO เมือง (ชื่อ, ประชากร) VALUES("Tbilisi", 1171100); เราได้เพิ่มประเทศที่ไม่มีเมืองในตารางของเรา และเมืองที่ไม่เกี่ยวข้องกับประเทศในตารางของเรา ดังนั้น INNER JOIN มีส่วนร่วมในการออกบันทึกทั้งหมดสำหรับการเชื่อมต่อที่อยู่ในสองตาราง นี่คือลักษณะไวยากรณ์ทั่วไปเมื่อเราต้องการรวมสองตาราง table1 และ table2: SELECT * FROM table1 t1 INNER JOIN table2 ON t1.id = t2.t1_id; จากนั้นบันทึกทั้งหมดที่มีความสัมพันธ์ในสองตารางจะถูกส่งกลับ ในกรณีของเรา เมื่อเราต้องการรับข้อมูลสำหรับประเทศพร้อมกับเมือง ผลลัพธ์จะเป็นดังนี้: $ SELECT * FROM city ci INNER JOIN Country co ON ci.country_id = co.id;
เข้าร่วมทางซ้าย
มีหลายกรณีและบ่อยครั้งที่เราไม่พอใจกับการสูญเสียฟิลด์ของตารางหลักเนื่องจากไม่มีบันทึกในตารางที่อยู่ติดกัน นี่คือสิ่งที่ LEFT JOIN มีไว้เพื่อ หากในคำขอก่อนหน้านี้เราระบุ LEFT แทนที่จะเป็น INNER เราจะเพิ่มเมืองอื่นในการตอบกลับ - ทบิลิซี: $ SELECT * FROM city ci LEFT JOIN Country co ON ci.country_id = co.id;
เข้าร่วมอย่างถูกต้อง
ที่นี่จะมีความแตกต่างจาก LEFT JOIN โดยที่ฟิลด์ทั้งหมดจะไม่ถูกเลือกทางด้านซ้าย แต่ทางด้านขวาในการเชื่อมต่อ นั่นคือไม่ใช่เมือง แต่ทุกประเทศจะถูกยึด: $ SELECT * FROM city ci RIGHT JOIN Country co ON ci.country_id = co.id;
การรักษาความปลอดภัยเข้าร่วม
ตอนนี้ ฉันต้องการให้คุณเห็นภาพทั่วไปที่รุ่นน้องอัดแน่นก่อนการสัมภาษณ์เพื่อโน้มน้าวพวกเขาว่าพวกเขาเข้าใจสาระสำคัญของการรวม:
- INNER JOIN เป็นเพียงจุดตัดของชุด นั่นคือ ระเบียนที่มีการเชื่อมต่อกับสองตาราง - A และ B;
- LEFT JOIN คือบันทึกทั้งหมดจากตาราง A รวมถึงบันทึกทั้งหมดจากตาราง B ที่มีจุดตัด (การเชื่อมต่อ) กับ A
- RIGHT JOIN ตรงกันข้ามกับ LEFT JOIN ทุกประการ ระเบียนทั้งหมดในตาราง B และระเบียนจาก A ที่มีความสัมพันธ์กัน
การบ้าน
คราวนี้งานจะน่าสนใจมากและทุกคนที่แก้ปัญหาได้สำเร็จสามารถมั่นใจได้ว่าพวกเขาพร้อมที่จะเริ่มทำงานในฝั่ง SQL! งานไม่เคี้ยวและเขียนขึ้นสำหรับนักเรียนระดับกลางดังนั้นมันจะไม่ง่ายและน่าเบื่อสำหรับคุณ :) ฉันจะให้เวลาคุณทำงานด้วยตัวเองหนึ่งสัปดาห์จากนั้นฉันจะเผยแพร่บทความแยกต่างหากพร้อมการวิเคราะห์โดยละเอียด ของการแก้ปัญหางานที่ฉันให้คุณงานจริง:
- เขียนสคริปต์ SQL เพื่อสร้างตาราง 'นักเรียน' โดยมีฟิลด์ต่อไปนี้: id (คีย์หลัก), ชื่อ, นามสกุล, อีเมล (ไม่ซ้ำกัน)
- เขียนสคริปต์ SQL เพื่อสร้างตาราง 'Book' โดยมีฟิลด์ต่อไปนี้: id, title (id + title = คีย์หลัก) เชื่อมโยง 'นักเรียน' และ 'หนังสือ' กับความสัมพันธ์ 'หนังสือ' แบบหนึ่งต่อกลุ่มของ 'นักเรียน'
- เขียนสคริปต์ SQL เพื่อสร้างตาราง 'ครู' โดยมีฟิลด์ต่อไปนี้: id (คีย์หลัก), ชื่อ, นามสกุล, อีเมล (ไม่ซ้ำกัน), หัวเรื่อง
- เชื่อมโยง 'นักเรียน' และ 'ครู' กับความสัมพันธ์ 'นักเรียน' แบบกลุ่มต่อกลุ่มครู
- เลือก 'Student' ที่มี 'oro' ในนามสกุล เช่น 'Sid oro v', 'V oro novsky'
- เลือกจากตาราง 'นักเรียน' นามสกุลทั้งหมด ('last_name') และจำนวนการซ้ำ พิจารณาว่ามีชื่อซ้ำกันในฐานข้อมูล เรียงตามปริมาณจากมากไปน้อย มันควรมีลักษณะเช่นนี้:
นามสกุล ปริมาณ เปตรอฟ 15 อีวานอฟ 12 ซิโดรอฟ 3 - เลือกชื่อซ้ำมากที่สุด 3 อันดับแรกจาก 'นักเรียน' เรียงตามปริมาณจากมากไปน้อย มันควรมีลักษณะเช่นนี้:
ชื่อ ปริมาณ อเล็กซานเดอร์ 27 เซอร์เกย์ 10 ปีเตอร์ 7 - เลือก 'นักเรียน' ที่มี 'หนังสือ' มากที่สุดและ 'ครู' ที่เกี่ยวข้อง จัดเรียงตามปริมาณจากมากไปน้อย มันควรมีลักษณะเช่นนี้:
นามสกุลอาจารย์ นามสกุลของนักเรียน ปริมาณหนังสือ เปตรอฟ ซิโดรอฟ 7 อีวานอฟ สมิธ 5 เปตรอฟ คันกะวะ 2> - เลือก 'ครู' ที่มี 'หนังสือ' มากที่สุดจาก 'นักเรียน' ทั้งหมดของเขา เรียงตามปริมาณจากมากไปน้อย มันควรมีลักษณะเช่นนี้:
นามสกุลอาจารย์ ปริมาณหนังสือ เปตรอฟ 9 อีวานอฟ 5 - เลือก 'ครู' ซึ่งมีหมายเลข 'หนังสือ' สำหรับ 'นักเรียน' ทั้งหมดของเขาอยู่ระหว่าง 7 ถึง 11 เรียงตามปริมาณจากมากไปน้อย มันควรมีลักษณะเช่นนี้:
นามสกุลอาจารย์ ปริมาณหนังสือ เปตรอฟ สิบเอ็ด ซิโดรอฟ 9 อีวานอฟ 7 - พิมพ์ 'last_name' และ 'ชื่อ' ของ 'ครู' และ 'นักเรียน' ทั้งหมดด้วยฟิลด์ 'ประเภท' (นักเรียนหรือครู) จัดเรียงตามตัวอักษรตาม 'last_name' มันควรมีลักษณะเช่นนี้:
นามสกุล พิมพ์ อีวานอฟ นักเรียน คันกะวะ ครู สมิธ นักเรียน ซิโดรอฟ ครู เปตรอฟ ครู - เพิ่มคอลัมน์ 'อัตรา' ลงในตาราง 'นักเรียน' ที่มีอยู่ ซึ่งจะจัดเก็บหลักสูตรที่นักเรียนอยู่ในปัจจุบัน (ค่าตัวเลขตั้งแต่ 1 ถึง 6)
- รายการนี้ไม่จำเป็น แต่จะเป็นบวก เขียนฟังก์ชันที่จะอ่าน 'หนังสือ' ทั้งหมดและแสดง 'ชื่อ' ทั้งหมดโดยคั่นด้วยเครื่องหมายจุลภาค
GO TO FULL VERSION