نقوم بتحليل قواعد البيانات ولغة SQL. (الجزء 5 - الاتصالات والانضمامات) - "مشروع جافا من الألف إلى الياء"

مقال من سلسلة حول إنشاء مشروع Java (توجد روابط لمواد أخرى في النهاية). هدفه هو تحليل التقنيات الرئيسية، والنتيجة هي كتابة روبوت برقية.

• أنواع العلاقات في قاعدة البيانات
  • واحد لكثير
  • واحد إلى واحد (واحد إلى واحد)
  • الكثير للكثيرين
• الاتصالات (الانضمام)
  • صلة داخلية
  • الانضمام الأيسر
  • الانضمام الصحيح
  • تأمين الانضمامات
• العمل في المنزل

مرحبًا بالجميع، كبار السن وكبار السن في المستقبل في مجال البرمجيات. كما قلت في الجزء السابق ( فحص الواجبات المنزلية )، اليوم ستكون هناك مواد جديدة. بالنسبة لأولئك المتحمسين بشكل خاص، فقد قمت بحفر واجب منزلي مثير للاهتمام حتى يتمكن أولئك الذين يعرفون كل شيء بالفعل وأولئك الذين لا يعرفون ولكن يريدون البحث عنه في Google من ممارسة مهاراتهم واختبارها. اليوم سنتحدث عن أنواع الاتصالات والانضمامات.

أنواع العلاقات في قاعدة البيانات

لفهم ما هي العلاقات، عليك أن تتذكر ما هو المفتاح الخارجي. ولمن نسي مرحبا بكم في بداية السلسلة .

واحد لكثير

دعونا نتذكر مثالنا مع البلدان والمدن. ومن الواضح أن المدينة يجب أن يكون لها بلد. كيفية ربط بلد بمدينة؟ من الضروري أن نرفق بكل مدينة معرفًا فريدًا (ID) للبلد الذي تنتمي إليه: لقد فعلنا ذلك بالفعل. يُطلق على هذا أحد أنواع الاتصالات - واحد لكثير (سيكون من الجيد أيضًا معرفة النسخة الإنجليزية - واحد لكثير). لإعادة الصياغة، يمكننا أن نقول: يمكن أن تنتمي عدة مدن إلى بلد واحد. هذه هي الطريقة التي يجب أن تتذكرها: علاقة رأس بأطراف. حتى الآن الأمر واضح، أليس كذلك؟ إذا لم يكن الأمر كذلك، فها هي الصورة الأولى من الإنترنت: تظهر أن هناك عملاء وطلباتهم. فمن المنطقي أنه يمكن للعميل الواحد أن يكون لديه أكثر من طلب واحد. هناك واحد إلى متعدد :) أو مثال آخر: هناك ثلاثة جداول: الناشر والمؤلف والكتاب. كل ناشر لا يريد الإفلاس ويريد النجاح لديه أكثر من مؤلف، ألا توافقونني الرأي؟ وفي المقابل، قد يكون لكل مؤلف أكثر من كتاب واحد - ولا يمكن أن يكون هناك شك في ذلك أيضًا. وهذا يعني، مرة أخرى، ارتباط مؤلف واحد بالعديد من الكتب، وناشر واحد بالعديد من المؤلفين . هناك العديد من الأمثلة الرائعة التي يمكن تقديمها. قد تكمن صعوبة الإدراك في البداية فقط في تعلم التفكير المجرد: النظر من الخارج إلى الطاولات وتفاعلها.

واحد إلى واحد (واحد إلى واحد)

يمكن القول أن هذه حالة خاصة للتواصل بين شخصين. حالة يرتبط فيها سجل واحد في جدول واحد بسجل واحد فقط في جدول آخر. ما هي الأمثلة التي يمكن أن تكون من الحياة؟ إذا استثنينا تعدد الزوجات، فيمكننا القول أن هناك علاقة فردية بين الزوج والزوجة. على الرغم من أنه حتى لو قلنا أن تعدد الزوجات مسموح به، فلا يزال بإمكان كل زوجة أن يكون لها زوج واحد فقط. ويمكن قول الشيء نفسه عن الوالدين. يمكن أن يكون لكل شخص أب بيولوجي واحد وأم بيولوجية واحدة فقط. علاقة صريحة بين شخصين. بينما كنت أكتب هذا، خطرت ببالي فكرة: لماذا إذن تقسيم علاقة رأس برأس إلى سجلين في جداول مختلفة، إذا كان لديهم بالفعل علاقة رأس برأس؟ لقد توصلت إلى الجواب بنفسي. ويمكن أيضًا ربط هذه السجلات بسجلات أخرى بطرق أخرى. ما الذي أتحدث عنه؟ مثال آخر على الاتصالات الفردية هو بين الدولة والرئيس. هل من الممكن تدوين كافة البيانات الخاصة بالرئيس في جدول "الدولة"؟ نعم، يمكنك ذلك، لن يقول SQL كلمة واحدة. ولكن إذا كنت تعتقد أن الرئيس هو أيضًا شخص... وقد يكون لديه أيضًا زوجة (علاقة أخرى بين شخصين) وأطفال (علاقة أخرى بين شخصين) ثم يتبين أنه سيكون كذلك ضروري لربط البلاد مع زوجة الرئيس وأولاده…. يبدو مجنونا، أليس كذلك؟ :D يمكن أن يكون هناك العديد من الأمثلة الأخرى لهذا الاتصال. علاوة على ذلك، في مثل هذه الحالة، يمكنك إضافة مفتاح خارجي إلى كلا الجدولين، على عكس علاقة رأس بأطراف.

الكثير للكثيرين

بالفعل بناء على الاسم، يمكنك تخمين ما سنتحدث عنه. في كثير من الأحيان في حياتنا، ونحن نبرمج حياتنا، هناك مواقف لا تكون فيها أنواع الاتصالات المذكورة أعلاه كافية لوصف الأشياء التي نحتاجها. لقد تحدثنا بالفعل عن الناشرين والكتب والمؤلفين. هناك الكثير من الروابط هنا... يمكن أن يكون لكل منشور عدة مؤلفين - اتصال واحد لكثير. وفي الوقت نفسه، قد يكون لكل مؤلف عدة ناشرين (ولماذا لا، نشر الكاتب في مكان واحد، أو تشاجر على المال، أو ذهب إلى دار نشر أخرى، على سبيل المثال). وهذه مرة أخرى علاقة رأس بأطراف. أو هذا: يمكن أن يكون لكل مؤلف عدة كتب، ولكن يمكن أن يكون لكل كتاب أيضًا عدة مؤلفين. مرة أخرى، علاقة رأس بأطراف بين المؤلف والكتاب، والكتاب والمؤلف. من هذا المثال يمكننا استخلاص نتيجة أكثر رسمية: كان من الواضح كيفية تعيين أنواع الاتصال السابقة في SQL: نحن فقط نمرر معرف ذلك النوع إلى تلك السجلات، والتي يوجد منها الكثير، أليس كذلك؟ تعطي دولة واحدة هويتها كمفتاح أجنبي للعديد من المدن. ما يجب القيام به مع علاقات كثير إلى كثير ؟ هذه الطريقة غير مناسبة. نحتاج إلى إضافة جدول آخر يربط بين الجدولين. على سبيل المثال، دعنا نذهب إلى MySQL، وننشئ قاعدة بيانات جديدة متعددة العدد، وننشئ جدولين، المؤلف والكتاب، اللذين سيحتويان فقط على الأسماء ومعرفاتها: CREATE DATABASE Manytomy؛ استخدم العديد من العديد؛ إنشاء جدول المؤلف (المعرف INT AUTO_INCREMENT، الاسم VARCHAR(100)، المفتاح الأساسي (المعرف))؛ إنشاء كتاب جدول (المعرف INT AUTO_INCREMENT، الاسم VARCHAR(100)، المفتاح الأساسي (المعرف))؛ لنقم الآن بإنشاء جدول ثالث يحتوي على مفتاحين خارجيين من جداول المؤلف والكتب، وسيكون هذا الرابط فريدًا. أي أنه لن يكون من الممكن إضافة سجل بنفس المفاتيح مرتين: 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 (معرف)، فريد (book_id، Author_id))؛ استخدمنا هنا العديد من الميزات الجديدة التي يجب التعليق عليها بشكل منفصل:

• NOT NULL تعني أنه يجب دائمًا ملء الحقل، وإذا لم نفعل ذلك، فستخبرنا SQL بذلك؛
• تقول UNIQUE أن الحقل أو مجموعة الحقول يجب أن تكون فريدة في الجدول. غالبًا ما يحدث أنه بالإضافة إلى المعرف الفريد، يجب أن يكون هناك حقل آخر فريد لكل سجل. و UNIQUE هي المسؤولة عن هذا الأمر بالضبط.

من ممارستي: عند الانتقال من نظام قديم إلى نظام جديد، يجب علينا، كمطورين، تخزين معرفات النظام القديم للعمل معه وإنشاء معرف خاص بنا. لماذا تصنع بنفسك ولا تستخدم القديم؟ قد لا تكون فريدة بما فيه الكفاية، أو قد لا يكون هذا الأسلوب في إنشاء المعرفات ملائمًا ومحدودًا. ولهذا الغرض، جعلنا اسم المعرف القديم فريدًا أيضًا في الجدول. للتحقق من ذلك، تحتاج إلى إضافة البيانات. أضف كتابًا ومؤلفًا: NSERT INTO قيم الكتاب (الاسم) ("book1")؛ أدخل في قيم المؤلف (الاسم) ("المؤلف 1")؛ نحن نعلم بالفعل من المقالات السابقة أنه سيكون لديهم معرفات 1 و1. لذلك، يمكننا على الفور إضافة سجل إلى الجدول الثالث: INSERT INTO Authors_x_books VALUES (1,1); وسيكون كل شيء على ما يرام حتى نرغب في تكرار الأمر الأخير مرة أخرى: أي كتابة نفس المعرفات مرة أخرى: ستكون النتيجة طبيعية - خطأ. سيكون هناك نسخة مكررة. لن يتم تسجيل الإدخال. هذه هي الطريقة التي سيتم بها إنشاء اتصال متعدد إلى متعدد... كل هذا رائع ومثير للاهتمام للغاية، ولكن يطرح سؤال منطقي: كيفية الحصول على هذه المعلومات؟ كيفية دمج البيانات من جداول مختلفة معًا والحصول على إجابة واحدة؟ وهذا ما سنتحدث عنه في الجزء القادم))

الاتصالات (الانضمام)

في الجزء السابق، قمت بإعدادك لكي تفهم على الفور ما هي الصلات وأين يمكن استخدامها. لأنني على قناعة تامة أنه بمجرد أن يأتي الفهم، سيصبح كل شيء بسيطًا للغاية على الفور، وستكون جميع المقالات حول الصلات واضحة مثل عيون الطفل:D بشكل تقريبي وبشكل عام، تحصل الصلات على نتيجة من عدة جداول عن طريق من JOIN (الانضمام من الإنجليزية الانضمام). وهذا كل شيء...) وللانضمام، تحتاج إلى تحديد الحقل الذي سيتم من خلاله ضم الجداول. الشيطان ليس مخيفًا كما هو مرسوم، أليس كذلك؟) بعد ذلك، سنتحدث فقط عن أنواع الروابط الموجودة وكيفية استخدامها. هناك العديد من أنواع الصلات، ولن نأخذها جميعًا في الاعتبار. فقط تلك التي نحتاجها حقًا. ولهذا السبب نحن لسنا مهتمين بالصلات الغريبة مثل Cross وNatural. لقد نسيت تمامًا، نحتاج إلى تذكر فارق بسيط آخر: يمكن أن تحتوي الجداول والحقول على أسماء مستعارة - أسماء مستعارة. يتم استخدامها بشكل ملائم للصلات. على سبيل المثال، يمكنك القيام بذلك: SELECT * FROM table1; إذا كان الاستعلام يستخدم الجدول 1 غالبًا، فيمكنك منحه اسمًا مستعارًا: 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; هنا، على الرغم من أن الأسماء هي نفسها، يمكنك أن ترى بوضوح أن مجالات المدن تأتي أولا، ثم مجالات البلدان. لكن الإدخالين اللذين أضفناهما أعلاه غير موجودين. لأن هذا هو بالضبط كيف يعمل 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 لإنشاء جدول "الطالب" مع الحقول التالية: المعرف (المفتاح الأساسي)، الاسم، اسم العائلة، البريد الإلكتروني (فريد).
2. اكتب برنامج نصي SQL لإنشاء جدول "الكتاب" مع الحقول التالية: المعرف، العنوان (المعرف + العنوان = المفتاح الأساسي). قم بربط "الطالب" و"الكتاب" بعلاقة "الكتاب" من "الطالب" واحد إلى متعدد.
3. اكتب برنامج نصي SQL لإنشاء جدول "المدرس" مع الحقول التالية: المعرف (المفتاح الأساسي)، الاسم، اسم العائلة، البريد الإلكتروني (فريد)، الموضوع.
4. قم بربط "الطالب" و"المعلم" بعلاقة "الطالب" مع معلم متعدد إلى متعدد.
5. حدد "الطالب" الذي يحتوي على "oro" في اسم عائلته، على سبيل المثال "Sid oro v" و"V oro novsky".
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. شكرا للجميع على القراءة. وأكرر مرة أخرى: الذي يمشي سيتقن الطريق ;)

توجد قائمة بجميع المواد الموجودة في السلسلة في بداية هذه المقالة.