אנו מנתחים מסדי נתונים ושפת SQL. (חלק 5 - חיבורים והצטרפות) - "פרויקט ג'אווה מא' עד ת'"

מאמר מסדרה על יצירת פרויקט ג'אווה (קישורים לחומרים אחרים נמצאים בסוף). המטרה שלו היא לנתח טכנולוגיות מפתח, התוצאה היא כתיבת בוט טלגרם.

• סוגי קשרים במסד הנתונים
  • אחד לרבים
  • אחד לאחד (אחד לאחד)
  • רבים-לרבים
• חיבורים (מצטרפים)
  • הצטרפות פנימית
  • שמאל הצטרף
  • ימינה הצטרף
  • אבטחת הצטרפות
• שיעורי בית

שלום לכולם, קשישים עתידיים ואנשי מקצוע בתחום התוכנה. כפי שאמרתי בחלק הקודם ( בדיקת שיעורי בית ), היום יהיה חומר חדש. למי שרוצה במיוחד, חפרתי מטלת בית מעניינת כדי שמי שכבר יודע הכל ומי שלא יודע אבל רוצה לחפש בגוגל יוכל להתאמן ולבדוק את כישוריו. היום נדבר על סוגי חיבורים והצטרפות.

סוגי קשרים במסד הנתונים

כדי להבין מה זה מערכות יחסים, אתה צריך לזכור מהו מפתח זר. למי ששכח, ברוך הבא לתחילת הסדרה .

אחד לרבים

בואו נזכור את הדוגמה שלנו עם מדינות וערים. ברור שלעיר חייבת להיות מדינה. איך מקשרים מדינה לעיר? יש צורך לצרף לכל עיר מזהה ייחודי (מזהה) של המדינה אליה היא שייכת: כבר עשינו זאת. זה נקרא אחד מסוגי החיבורים - אחד לרבים (טוב יהיה לדעת גם את הגרסה האנגלית - one-to-many). בפרפרזה, אפשר לומר: כמה ערים יכולות להיות שייכות למדינה אחת. ככה צריך לזכור את זה: מערכת יחסים של אחד לרבים. עד כאן זה ברור, נכון? אם לא, אז הנה התמונה הראשונה מהאינטרנט: היא מראה שיש לקוחות וההזמנות שלהם. הגיוני שלקוח אחד יכול לקבל יותר מהזמנה אחת. יש אחד לרבים :) או דוגמה אחרת: יש שלוש טבלאות: מוציא לאור, מחבר וספר. לכל מו"ל שלא רוצה לפשוט רגל ורוצה להצליח יש יותר ממחבר אחד, אתה לא מסכים? בתורו, לכל מחבר יכול להיות יותר מספר אחד - גם בזה אין ספק. וזה אומר, שוב, חיבור של מחבר אחד לספרים רבים, הוצאה אחת לסופרים רבים . יש עוד הרבה מאוד דוגמאות שאפשר לתת. הקושי בתפיסה בהתחלה יכול להיות רק בלמידת חשיבה מופשטת: להסתכל מבחוץ על השולחנות ועל האינטראקציה ביניהם.

אחד לאחד (אחד לאחד)

ניתן לומר שזה מקרה מיוחד של תקשורת של אחד לרבים. מצב שבו רשומה אחת בטבלה אחת קשורה לרשומה אחת בלבד בטבלה אחרת. אילו דוגמאות יכולות להיות מהחיים? אם נשלל פוליגמיה, אזי נוכל לומר שיש מערכת יחסים של אחד לאחד בין בעל ואישה. אמנם גם אם נאמר שמותר לפוליגמיה, הרי שלכל אישה עדיין יכול להיות בעל אחד בלבד. אותו דבר אפשר לומר על הורים. לכל אדם יכול להיות רק אב ביולוגי אחד ורק אם ביולוגית אחת. מערכת יחסים מפורשת של אחד לאחד. בזמן שכתבתי את זה, עלתה בי מחשבה: מדוע אם כן לחלק מערכת יחסים של אחד לאחד לשני רשומות בטבלאות שונות, אם כבר יש להם קשר אחד לאחד? הגעתי לתשובה בעצמי. רשומות אלו עשויות להיות מקושרות לרשומות אחרות בדרכים אחרות. על מה אני מדבר? דוגמה נוספת לחיבורים אחד לאחד היא בין המדינה לנשיא. האם ניתן לרשום את כל הנתונים על הנשיא בטבלת "המדינה"? כן, אתה יכול, SQL לא יגיד מילה. אבל אם אתה חושב שגם הנשיא הוא אדם... ואולי יש לו גם אישה (עוד מערכת יחסים של אחד על אחד) וילדים (עוד קשר של אחד לרבים) ואז מסתבר שזה יהיה הכרחי כדי לחבר את המדינה עם אשת הנשיא וילדיו... נשמע מטורף, נכון? :D יכולות להיות דוגמאות רבות אחרות לחיבור הזה. יתרה מכך, במצב כזה, ניתן להוסיף מפתח זר לשתי הטבלאות, בשונה ממערכת יחסים של אחד לרבים.

רבים-לרבים

כבר על סמך השם, אתה יכול לנחש על מה נדבר. לעתים קרובות בחיים, ואנו מתכנתים את חיינו, ישנם מצבים שבהם סוגי הקשרים הנ"ל אינם מספיקים כדי לתאר את הדברים שאנו צריכים. כבר דיברנו על מו"לים, ספרים וסופרים. פשוט יש כאן כל כך הרבה קשרים... לכל פרסום יכולים להיות כמה מחברים - קשר של אחד לרבים. יחד עם זאת, לכל סופר יכול להיות כמה מו"לים (למה לא, הסופר התפרסם במקום אחד, רב על כסף, הלך להוצאה אחרת, למשל). וזו שוב מערכת יחסים של אחד לרבים. או זה: לכל מחבר יכולים להיות כמה ספרים, אבל לכל ספר יכולים להיות גם כמה מחברים. שוב, מערכת יחסים של אחד לרבים בין סופר לספר, ספר ומחבר. מדוגמה זו אנו יכולים להסיק מסקנה רשמית יותר: היה ברור איך להגדיר את סוגי החיבור הקודמים ב-SQL: אנחנו פשוט מעבירים את המזהה של זה לרשומות האלה, שיש הרבה מהן, נכון? מדינה אחת נותנת את תעודת הזהות שלה כמפתח זר לערים רבות. מה לעשות עם מערכות יחסים רבות לרבים ? שיטה זו אינה מתאימה. עלינו להוסיף טבלה נוספת שתחבר בין שני הטבלאות. לדוגמה, בוא נלך ל-MySQL, ניצור מסד נתונים חדש manytomany, ניצור שתי טבלאות, מחבר וספר, שיכילו רק שמות ומזהים שלהם: CREATE DATABASE manytomany; השתמש ברבים רבים; CREATE TABLE author( id INT AUTO_INCREMENT, שם VARCHAR(100), PRIMARY KEY (id) ); CREATE TABLE book( 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 מחבר (מזהה ), UNIQUE (מזהה_ספר, מזהה_סופר) ); כאן השתמשנו בכמה תכונות חדשות שצריך להגיב עליהן בנפרד:

• NOT NULL פירושו שתמיד יש למלא את השדה, ואם לא, SQL יגיד לנו זאת;
• UNIQUE אומר ששדה או חבורה של שדות חייבים להיות ייחודיים בטבלה. לעתים קרובות קורה שבנוסף למזהה הייחודי, שדה אחד נוסף חייב להיות ייחודי עבור כל רשומה. ו-UNICK אחראית בדיוק לעניין הזה.

מהפרקטיקה שלי: במעבר ממערכת ישנה לחדשה, עלינו כמפתחים לאחסן את המזהים של המערכת הישנה כדי לעבוד איתה וליצור משלנו. למה ליצור משלך ולא להשתמש בישנים? יכול להיות שהם לא מספיק ייחודיים, או שגישה זו ליצירת מזהים כבר לא רלוונטית ומוגבלת. לשם כך, הפכנו את השם הישן לייחודי גם בטבלה. כדי לבדוק זאת, עליך להוסיף נתונים. הוסף ספר ומחבר: NSERT INTO book (שם) VALUES ("book1"); INSERT INTO מחבר (שם) VALUES ("מחבר1"); אנחנו כבר יודעים ממאמרים קודמים שיהיו להם מזהים 1 ו-1. לכן, נוכל להוסיף מיד רשומה לטבלה השלישית: INSERT INTO authors_x_books VALUES (1,1); והכל יהיה בסדר עד שנרצה לחזור שוב על הפקודה האחרונה: כלומר, רשום שוב את אותם מזהים: התוצאה תהיה טבעית - שגיאה. יהיה כפיל. הערך לא יתועד. כך ייווצר חיבור של רבים לרבים... כל זה מאוד מגניב ומעניין, אבל עולה שאלה הגיונית: איך משיגים את המידע הזה? כיצד לשלב נתונים מטבלאות שונות יחד ולקבל תשובה אחת? על זה נדבר בחלק הבא))

חיבורים (מצטרפים)

בחלק הקודם הכנתי אתכם להבין מיד מה זה הצטרפות והיכן להשתמש בהן. כי אני משוכנעת עמוקות שברגע שתגיע ההבנה, הכל יהפוך לפשוט מאוד, וכל הכתבות על הצטרפות יהיו ברורות כעיני תינוק :D באופן גס ובכלל, הצטרפות מקבלות תוצאה מכמה טבלאות באמצעים של JOIN (הצטרפות מאנגלית join). וזה הכל...) וכדי להצטרף, צריך לציין את השדה שלפיו יצטרפו הטבלאות. השטן לא מפחיד כמו שהוא מצויר, נכון?) לאחר מכן, נדבר רק על אילו סוגי חיבורים יש וכיצד להשתמש בהם. ישנם סוגים רבים של הצטרפות, ולא נשקול את כולם. רק אלה שאנחנו באמת צריכים. זו הסיבה שאנחנו לא מעוניינים בהצטרפות אקזוטיות כמו Cross and Natural. שכחתי לגמרי, אנחנו צריכים לזכור ניואנס אחד נוסף: לטבלאות ושדות יכולים להיות כינויים - שמות בדויים. הם משמשים בצורה נוחה להצטרפות. לדוגמה, אתה יכול לעשות זאת: SELECT * FROM table1; אם השאילתה תשתמש לעתים קרובות בטבלה1, אז אתה יכול לתת לה כינוי: SELECT* FROM table1 בתור t1; או אפילו קל יותר לכתוב: SELECT * FROM table1 t1; ואז בהמשך השאילתה ניתן יהיה להשתמש ב-t1 ככינוי לטבלה זו.

הצטרפות פנימית

ההצטרפות הנפוצה והפשוטה ביותר. הוא אומר שכאשר יש לנו שתי טבלאות ושדה שבאמצעותו ניתן לחבר אותו, ייבחרו כל הרשומות שהקשרים ביניהן קיימים בשתי הטבלאות. היה קשה לומר איכשהו. בואו נסתכל על דוגמה: בואו נוסיף רשומה אחת למאגר הערים שלנו. ערך אחד עבור ערים ואחד עבור מדינות: $ INSERT INTO country VALUES(5, "אוזבקיסטן", 34036800); ו- $ INSERT INTO city (שם, אוכלוסייה) 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; כאן, למרות שהשמות זהים, ניתן לראות בבירור שדות הערים באים קודם, אחר כך שדות המדינות. אבל שני הערכים שהוספנו למעלה אינם שם. כי ככה בדיוק עובד INNER JOIN.

שמאל הצטרף

ישנם מקרים, ולעתים קרובות למדי, כאשר איננו מרוצים מאובדן שדות של הטבלה הראשית בשל העובדה שאין רישום לכך בטבלה הסמוכה. בשביל זה יש 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; עכשיו ברור שבמקרה הזה לא תהיה טביליסי, אבל תהיה לנו אוזבקיסטן. משהו כזה…))

אבטחת הצטרפות

עכשיו אני רוצה להראות לכם תמונה טיפוסית שצעירים דומסים לפני ראיון כדי לשכנע אותם שהם מבינים את המהות של הצטרפות: כאן הכל מוצג בצורה של סטים, כל עיגול הוא טבלה. והמקומות שבהם הוא מצויר הם החלקים שיוצגו ב-SELECT. בואו נראה:

• INNER JOIN הוא רק הצומת של קבוצות, כלומר, אותן רשומות שיש להן חיבורים לשתי טבלאות - A ו-B;
• LEFT JOIN הוא כל הרשומות מטבלה A, כולל כל הרשומות מטבלה B שיש להן צומת (חיבור) עם A;
• RIGHT JOIN הוא בדיוק ההפך מ-LEFT JOIN - כל הרשומות בטבלה B והרשומות מ-A שיש להן קשר.

אחרי כל זה, התמונה הזו צריכה להיות ברורה))

שיעורי בית

הפעם המשימות יהיו מאוד מעניינות וכל מי שיפתור אותן בהצלחה יכול להיות סמוך ובטוח שהם מוכנים להתחיל לעבוד בצד ה-SQL! המשימות לא נלעסות ונכתבו לתלמידי חטיבת הביניים, אז זה לא יהיה לך קל ומשעמם :) אתן לך שבוע לעשות את המשימות בעצמך, ולאחר מכן אפרסם מאמר נפרד עם ניתוח מפורט של הפתרון למשימות שנתתי לך.

המשימה בפועל:

1. כתוב סקריפט SQL כדי ליצור את טבלת 'סטודנט' עם השדות הבאים: id (מפתח ראשי), שם, שם משפחה, דואר אלקטרוני (ייחודי).
2. כתוב סקריפט SQL כדי ליצור את טבלת 'ספר' עם השדות הבאים: id, title (מזהה + כותרת = מפתח ראשי). קשר את 'תלמיד' ו'ספר' עם מערכת יחסים 'תלמיד' אחד לרבים 'ספר'.
3. כתוב סקריפט SQL כדי ליצור את טבלת 'מורה' עם השדות הבאים: id (מפתח ראשי), שם, שם_משפחה, דואר אלקטרוני (ייחודי), נושא.
4. קשר את 'תלמיד' ו'מורה' עם מערכת יחסים 'תלמיד' רבים-לרבים מורה'.
5. בחר 'תלמיד' שיש לו 'אורו' בשם המשפחה שלהם, למשל 'סיד אורו v', ' וורו נובסקי'.
6. בחר מטבלת 'תלמיד' את כל שמות המשפחה ('שם_משפחה') ואת מספר החזרות שלהם. קחו בחשבון שיש שמות במסד הנתונים. מיין לפי כמות בסדר יורד. זה אמור להיראות כך:

   שם משפחה	כַּמוּת
   פטרוב	15
   איבנוב	12
   סידורוב	3

7. בחר את 3 השמות המובילים שחוזרים על עצמם מ'תלמיד'. מיין לפי כמות בסדר יורד. זה אמור להיראות כך:

   שֵׁם	כַּמוּת
   אלכסנדר	27
   סרגיי	10
   פיטר	7

8. בחר 'סטודנטים' שיש להם את המספר הגדול ביותר של 'ספר' ו'מורה' משויך. מיין לפי כמות בסדר יורד. זה אמור להיראות כך:

   שם המשפחה של המורה	שם המשפחה של התלמיד	כמות הספר
   פטרוב	סידורוב	7
   איבנוב	נַפָּח	5
   פטרוב	קנקבה	2>

9. בחר את ה'מורה' שיש לו את המספר הגדול ביותר של 'ספר' מכל ה'תלמיד' שלו. מיין לפי כמות בסדר יורד. זה אמור להיראות כך:

   שם המשפחה של המורה	כמות הספר
   פטרוב	9
   איבנוב	5

10. בחר 'מורה' שמספר 'ספר' עבור כל 'תלמיד' שלו הוא בין 7 ל-11. מיין לפי כמות בסדר יורד. זה אמור להיראות כך:

    שם המשפחה של המורה	כמות הספר
    פטרוב	אחד עשר
    סידורוב	9
    איבנוב	7

11. הדפס את כל ה'שם_משפחה' וה'שם' של כל ה'מורה' וה'תלמיד' עם השדה 'סוג' (תלמיד או מורה). מיין בסדר אלפביתי לפי 'שם_משפחה'. זה אמור להיראות כך:

    שם משפחה	סוּג
    איבנוב	סטוּדֶנט
    קנקבה	מוֹרֶה
    נַפָּח	סטוּדֶנט
    סידורוב	מוֹרֶה
    פטרוב	מוֹרֶה

12. הוסף עמודת 'שיעור' לטבלת 'סטודנט' הקיימת, שתשמור את הקורס בו התלמיד נמצא כעת (ערך מספרי מ-1 עד 6).
13. פריט זה אינו חובה, אך יהווה יתרון. כתוב פונקציה שתעבור על כל ה'ספרים' ותוציא את כל ה'כותרות' מופרדות בפסיקים.

סיכום

הסדרה על מסד הנתונים נגררה קצת. לְהַסכִּים. עם זאת, עברנו כברת דרך ובעקבות כך אנו יוצאים עם ידע בעניין! תודה לכולכם שקראתם, אני מזכיר לכם שכל מי שרוצה להמשיך הלאה ולעקוב אחר הפרויקט צריך ליצור חשבון ב- GitHub ולהירשם לחשבון שלי :) עוד יבואו - בואו נדבר על Maven ו-Docker. תודה לכולם על הקריאה. אני חוזר שוב: מי שהולך ישלוט בדרך ;)

רשימה של כל החומרים בסדרה נמצאת בתחילת מאמר זה.