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

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

التمرين 1

اكتب برنامج نصي SQL لإنشاء جدول "الطالب" مع الحقول التالية: المعرف (المفتاح الأساسي)، الاسم، اسم العائلة، البريد الإلكتروني (فريد). لقد قمنا بذلك بالفعل، لذلك لا ينبغي أن تكون هناك أي مشاكل. تحتاج في البرنامج النصي إلى تحديد المفتاح الأساسي وحقل فريد يختلف عن الحقل الأساسي. أولاً، لنقم بإنشاء قاعدة بيانات جديدة لهذه المهمة: $ CREATE DATABASE Final_task; ودعنا نستخدم قاعدة البيانات هذه: $ USE Final_task; بمجرد إعداد البيئة وجاهزيتها لتشغيل المهمة، يمكننا كتابة البرنامج النصي التالي: $ CREATE TABLE Student ( id INT AUTO_INCREMENT, name VARCHAR(40), last_name VARCHAR(50), email VARCHAR(100), PRIMARY KEY ( معرف)، فريد (البريد الإلكتروني ))؛ حتى الآن، لا يوجد شيء جديد مقارنة بما مررنا به بالفعل. أي تعليقات غير ضرورية، دعونا نمضي قدما.

المهمة 2-3

اكتب برنامج نصي SQL لإنشاء جدول "الكتاب" مع الحقول التالية: المعرف، العنوان (المعرف + العنوان = المفتاح الأساسي). قم بربط "الطالب" و"الكتاب" بعلاقة "الكتاب" من "الطالب" واحد إلى متعدد. دعونا ندمج مهمتين في مهمة واحدة لجعلها أسرع وأكثر ملاءمة. لقد ناقشت بالفعل كيفية إضافة مفتاح خارجي منفصل في المقالات السابقة. ولإضافة ذلك، علينا أن نتذكر كيف نقوم بإجراء الاتصالات ومن خلال ماذا. ستساعدك المقالة السابقة، ثم إليك البرنامج النصي: $ CREATE TABLE book ( id INT, title VARCHAR(100), Student_id INT DEFAULT NULL, PRIMARY KEY (id, title), FOREIGN KEY (student_id) مراجع الطالب (id) ))); بهذه الطريقة البسيطة، أضفنا مفتاحًا مركبًا لجدولنا PRIMARY KEY (id، title) ، والآن سيكون المفتاح هو الزوج بالضبط. وهذا يعني أنه يمكن أن يكون هناك أكثر من قيمة حقل معرف متطابقة في الجدول. ونفس الشيء بالضبط بالنسبة للعنوان.

المهمة 4

اكتب برنامج نصي SQL لإنشاء جدول "المدرس" مع الحقول التالية: المعرف (المفتاح الأساسي)، الاسم، اسم العائلة، البريد الإلكتروني (فريد)، الموضوع. نواصل إعداد قاعدة البيانات الخاصة بنا للاستعلامات، وإنشاء جدول المعلم: $ CREATE TABLE teacher( id INT AUTO_INCREMENT, name VARCHAR(30), last_name VARCHAR(30), email VARCHAR(100), subject VARCHAR(40), PRIMARY KEY ( معرف)، فريد (البريد الإلكتروني))؛ حتى الآن ليس الأمر صعبا، أليس كذلك؟ ثلاث مهام قد انتهت بالفعل!

المهمة 5

قم بربط "الطالب" و"المعلم" بعلاقة "الطالب" مع معلم متعدد إلى متعدد. الآن هذا أكثر إثارة للاهتمام! لقد تحدثنا للتو عن هذه المرة الأخيرة. اسمحوا لي أن أذكرك بما هو مطلوب لتحقيق ذلك: تحتاج إلى إنشاء جدول وسيط لتخزين أزواج الطلاب والمعلمين. بمساعدتها، سيكون من الممكن إنشاء علاقة متعدد إلى متعدد. لذلك، دعونا نقوم بإنشاء جدول Student_x_techers . أسلوب التسمية مفتوح ويمكن أن يكون أيضًا: Student_teacher . $ إنشاء جدول Student_x_teachers ( Student_id INT NOT NULL، teacher_id INT NOT NULL، المفتاح الأساسي (student_id، teacher_id)، المفتاح الخارجي (student_id) مراجع الطالب (id)، المفتاح الخارجي (teacher_id) المراجع teacher(id))؛ كما ترون، كل شيء يتم بشكل واضح ومتماسك. لدينا مفتاح مركب لمفتاحين خارجيين: Student_id و teacher_id. لماذا أيضا مفتاح خارجي؟ لكي نتأكد من وجود سجلات في جداول الطلاب والمعلمين للأزواج التي يتم تسجيلها.

المهمة 6

حدد "الطالب" الذي يحتوي على "oro" في اسم عائلته، على سبيل المثال "Sidorov"، "Voronovsky". ولجعله مثيرًا للاهتمام ومرئيًا بالنسبة لنا، أقترح إضافة العديد من الطلاب أولاً، بحيث يكون بعضهم مناسبًا لهذا الطلب والبعض الآخر لا. لذلك، دعونا نكتب أولئك الذين يجب إدراجهم كنتيجة للطلب: $ INSERT INTO Student (الاسم، اسم العائلة، البريد الإلكتروني) VALUES ('Ivan'، 'Sidorov'، 'ivan.sidorov@gmail.com')؛ $ أدخل في قيم الطالب (الاسم، اسم العائلة، البريد الإلكتروني) ("Nikolay"، "Voronovsky"، "nikolay.voronovsky@gmail.com")؛ وأولئك الذين لا ينبغي لهم الدخول: $ INSERT INTO Student (الاسم، اسم العائلة، البريد الإلكتروني) VALUES ("Roman"، "Fortny"، "roman.fortny@gmail.com")؛ $ INSERT INTO الطالب (الاسم، اسم العائلة، البريد الإلكتروني) VALUES('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); دعونا نتحقق من النتيجة وننظر إلى قائمة البيانات الموجودة في جدول الطلاب: $ SELECT * FROM Student؛ ونحصل على: هناك أربعة سجلات إجمالاً، اثنان منهم يجب أن يكونا مناسبين، واثنان لا ينبغي أن يكونا مناسبين. بعد إعداد جميع البيانات للطلب، يمكننا تقديم طلب للمهمة نفسها: $ SELECT * FROM Student WHERE last_name LIKE '%oro%'; ونتيجة لذلك، استعرض إيفان ونيكولاي القائمة.

المهمة 7

المهمة التالية هي أن نقرأ: اختر من جدول "الطالب" جميع الأسماء الأخيرة ('اسم_العائلة') وعدد تكراراتها. ضع في اعتبارك أن هناك أسماء تحمل الاسم نفسه في قاعدة البيانات. الترتيب حسب الكمية بالترتيب التنازلي. يجب أن تبدو هذه:

اسم العائلة	كمية
بيتروف	15
إيفانوف	12
سيدوروف	3

للتوضيح، تحتاج إلى إضافة المزيد من البيانات. دون مزيد من اللغط، دعونا نضيف بيتروف وإيفانوف وسيدوروف، الذين لا يعرفون قرابتهم؛) لن أخترع عنوان بريد إلكتروني، سأستبعده فقط من الإدخالات الجديدة. لنقم بتشغيل الأمر التالي 12 مرة: $ INSERT INTO Student (name, last_name) VALUES ('Ivan', 'Ivanov'); دعونا نضيف 15 بتروف: $ INSERT INTO Student (name, last_name) VALUES ('Petr', 'Petrov'); واثنين من Sidorov (لدينا واحد بالفعل))): $ INSERT INTO Student (الاسم، اسم العائلة) VALUES ('Sidor'، 'Sidorov')؛ البيانات جاهزة الآن. للحصول على مثل هذه البيانات، تحتاج إلى إجراء تجميع؛ ولإجراء تجميع، تحتاج إلى استخدام عامل التشغيل Group By، وتحتاج إلى القيام بذلك حسب حقل last_name. يمكنك أيضًا ملاحظة أن عدد التكرارات تم تحديده بالكمية ، وهنا عليك أيضًا أن تتذكر كيفية إنشاء أسماء مستعارة في SQL: $ SELECT last_name, COUNT(*) ككمية من Student GROUP BY last_name ORDER BY COUNT(*) DESC ; لذلك بالغت في الأمر مع بيتروف - اتضح أنه 16))

المهمة 8

الحالة: اختر أكثر 3 أسماء متكررة من "الطالب". الترتيب حسب الكمية بالترتيب التنازلي. يجب أن تبدو هذه:

اسم	كمية
الكسندر	27
سيرجي	10
نفذ	7

أوه، لهذا الغرض لدينا بالفعل إيفانز وبيترز وسيدورس. لذلك ليست هناك حاجة لإضافتهم. نحن نعرف بالفعل كيفية الفرز. الشيء الوحيد الذي لم نتحدث عنه اليوم هو كيفية اختيار عدد معين من السجلات. لقد ظهر هذا بالفعل في الحلول السابقة لمشاكل قاعدة البيانات. لمن لم يقرأها فليقرأها. بالنسبة للبقية، دعنا ننتقل مباشرة إلى النقطة: $ SELECT name, COUNT(*) as الكمية FROM Student GROUP BY name ORDER BY COUNT(*) DESC LIMIT 3; كما يتبين من الاستعلام، إذا كنت تعرف ترتيب العوامل في استعلام SELECT، فلن تكون هناك مشاكل في تنفيذ مثل هذا الاستعلام. وهذه المهمة لا تزال على عاتقنا. والمعرفة التي تم تقديمها سابقًا كافية لحل هذه المشكلة.

المهمة 9

حالة المهمة: حدد "الطالب" الذي لديه أكبر عدد من "الكتاب" و"المعلم" المرتبط به. الترتيب حسب الكمية بالترتيب التنازلي. يجب أن تبدو هذه:

الاسم الأخير للمعلم	الاسم الأخير للطالب	كمية الكتاب
بيتروف	سيدوروف	7
إيفانوف	حداد	5
بيتروف	كانكافا	2

إذن، من الواضح أن هذه المهمة أكثر صعوبة من المهمة السابقة، أليس كذلك؟ لا عجب: رائحتها تشبه الانضمام... وأكثر من واحد) أولاً، علينا أن نفهم ما يجب القيام به... من الواضح أن كمية الكتاب تتطلب التجميع. ولكن ماذا؟ ولماذا يجب أن نجمعهم؟ يتضمن الاستعلام ثلاثة جداول وتجميع وفرز. انطلاقا من حقيقة أن السجلات التي لا توجد بها كتب لا تظهر، فهذا يعني أنك بحاجة إلى اتخاذ INNER JOIN. سنقدم أيضًا طلبًا للانضمام إلى LEFT JOIN حتى لا تكون هناك مشاكل في ذلك. وهناك عدة خيارات. أول شيء نقوم به هو ضم ثلاثة جداول في سجل واحد. بعد ذلك، نقوم بالتجميع حسب الطالب وإضافة اسم المعلم إليه. ماذا سنختار؟ اسم المعلم والطالب وعدد الكتب. لنضيف بيانات للطلب:

• ثلاثة مدرسين؛
• عشرة كتب؛
• ربط طالبين مع ثلاثة معلمين.

ثلاثة مدرسين

$ INSERT INTO teacher(last_name) VALUES ('Matvienko'); $ INSERT INTO teacher(last_name) VALUES ('Shevchenko'); $ INSERT INTO teacher(last_name) VALUES ("Vasilenko");

10 كتب

سوف آخذ هويات الطلاب الأول والثاني. وسوف أرفق الكتب لهم. نظرًا لعدم تعيين AUTO_INCREMENT، لكي لا تكتب معرفًا جديدًا في كل مرة، عليك القيام بما يلي: $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; بعد ذلك، أضف كتبًا للطالب الأول: $ INSERT INTO book (title, Student_id) VALUES('book1', 1); $ INSERT INTO الكتاب (العنوان، Student_id) VALUES('book2', 1); $ INSERT INTO الكتاب (العنوان، Student_id) VALUES('book3', 1); $ INSERT INTO الكتاب (العنوان، Student_id) VALUES('book4', 1); $ INSERT INTO الكتاب (العنوان، Student_id) VALUES('book5', 1); $ INSERT INTO book (title, Student_id) VALUES('book6', 1); والكتب للطالب الثاني: $ INSERT INTO book (title, Student_id) VALUES('book7', 2); $ INSERT INTO الكتاب (العنوان، Student_id) VALUES('book8', 2); $ INSERT INTO الكتاب (العنوان، Student_id) VALUES('book9', 2); $ INSERT INTO book (title, Student_id) VALUES('book10', 2);

اتصالات المعلم والطالب

للقيام بذلك، قم بإضافة Student_x_teachers إلى الجدول: $ INSERT INTO Student_x_teachers VALUES (1,1); $INSERT INTO Student_x_teachers VALUES(1,2); $INSERT INTO Student_x_teachers VALUES(2,3);

فلننفذ الطلب

نقوم بالمرحلة الأولى - نقوم بربط ثلاثة جداول في سجل واحد: $ SELECT * FROM teacher tch INNER JOIN Student_x_teachers st_x_tch ON tch.id = st_x_tch.teacher_id INNER JOIN Student st ON st_x_tch.student_id = st.id INNER JOIN book b ON st .id = b.student_id; بالطبع، ليس لدينا أي سجلات حتى الآن، ومع ذلك، يمكننا أن نرى أننا نجحنا في ربط ثلاثة جداول. نضيف الآن تجميع الكتب وفرزها والحقول التي نحتاجها: $ SELECT tch.last_name, st.last_name, st.id, COUNT(*) ككتب من الطالب st INNER JOIN book b ON st.id = b.student_id INNER انضم إلى الطلاب_x_teachers st_x_tch ON st.id = st_x_tch.student_id INNER JOIN teacher tch ON tch.id = st_x_tch.teacher_id GROUP BY st.id ORDER BY books DESC؛ لكننا حصلنا على خطأ في SQL والإجابة التالية: رمز الخطأ: 1055. التعبير رقم 1 من قائمة SELECT ليس في جملة GROUP BY ويحتوي على عمود غير مجمع "final_task.tch.last_name" والذي لا يعتمد وظيفيًا على الأعمدة في GROUP BY لا يمكن استخدام هذه العناصر نظرًا لوجود علاقة متعدد إلى متعدد بين المعلم والطالب. وهذا صحيح: لا يمكننا تعيين معلم واحد فقط لكل طالب. لذلك دعونا نذهب في الاتجاه الآخر. دعونا نستخدم شيئًا يسمى عرض SQL. ما هي الفكرة: نقوم بإنشاء عرض منفصل، وهو جدول جديد، بالفعل مع التجميع الذي نحتاجه. وسنضيف إلى هذا الجدول الأسماء الضرورية للمعلمين. ولكننا نأخذ في الاعتبار إمكانية وجود أكثر من مدرس، لذلك سيتم تكرار الإدخالات. إنشاء عرض: $ إنشاء عرض لكتب الطلاب كـ SELECT st.last_name,st.id,COUNT(*) ككتب من الطالب st INNER JOIN book b ON st.id=b.student_id GROUP BY st.id ORDER BY books DESC; بعد ذلك، نعمل مع هذا العرض كجدول بسيط يحتوي على ثلاثة حقول: اسم عائلة الطالب، ومعرف_الطالب، وعدد الكتب. وفقًا لمعرف الطالب، يمكننا أيضًا إضافة مدرس من خلال رابطين: $ SELECT tch.last_name كـ 'Teacher'، sbw.last_name 'Student'، sbw.books كـ 'Books' من Studentbook sbw INNER JOIN Student_x_teachers stch ON sbw. id = stch.student_id INNER JOIN teacher tch ON tch.id = stch.teacher_id; والآن ستكون النتيجة: هاه! هذا طلب، أليس كذلك؟) اتضح كما هو متوقع: الطالب الذي يحمل المعرف = 1 لديه ستة كتب ومدرسين، والطالب الذي يحمل المعرف = 2 لديه أربعة كتب ومعلم واحد.

المهمة 10

الحالة: اختر "المعلم" الذي لديه أكبر عدد من "الكتب" بين جميع "طلابه". الترتيب حسب الكمية بالترتيب التنازلي. يجب أن تبدو هذه:

الاسم الأخير للمعلم	كمية الكتاب
بيتروف	9
إيفانوف	5

هنا يمكننا استخدام طلب جاهز من المهمة السابقة. ما الذي نحتاج إلى تغييره حيال ذلك؟ لدينا هذه البيانات بالفعل، نحتاج فقط إلى إضافة مجموعة أخرى وإزالة اسم الطالب من بيانات الإخراج. لكن أولاً، دعونا نضيف طالبًا آخر إلى المعلم لجعل النتيجة أكثر إثارة للاهتمام. للقيام بذلك، نكتب: $ INSERT INTO Student_x_teachers VALUES (2, 1); والاستعلام نفسه: $ SELECT tch.last_name كـ 'Teacher'، SUM(sbw.books) كـ 'Books' من Studentbook sbw INNER JOIN الطلاب_x_teachers stch ON sbw.id = stch.student_id INNER JOIN teacher tch ON tch.id = stch . teacher_id المجموعة بواسطة tch.id؛ ونتيجة لذلك، نحصل على: هذا المعلم فاسيلينكو لديه 10 كتب، وشيفشينكو لديه 6...)

المهمة 11

الحالة: اختر "معلمًا" يتراوح عدد "كتبه" لجميع طلابه بين 7 و11. الترتيب حسب الكمية بالترتيب التنازلي. يجب أن تبدو هذه:

الاسم الأخير للمعلم	كمية الكتاب
بيتروف	أحد عشر
سيدوروف	9
إيفانوف	7

هذا هو المكان الذي سنستخدم فيه HAVING. تحدثنا عنه. سيكون الطلب هو نفسه تمامًا كما كان من قبل، كل ما عليك فعله هو إضافة شرط أن يكون عدد الكتب في نطاق معين. وكما قلت في المقالات السابقة، عندما نحتاج إلى إجراء التصفية أثناء التجميع و/أو وظائف التجميع، نحتاج إلى استخدام HAVING : $ SELECT tch.last_name كـ "Teacher"، وSUM(sbw.books) كـ "Books" من Studentbook sbw INNER JOIN Student_x_teachers stch ON sbw.id = stch.student_id INNER JOIN teacher tch ON tch.id = stch.teacher_id GROUP BY tch.id HAVING SUM(sbw.books) > 6 AND SUM(sbw.books) < 12; لقد أبرزت الجزء الذي أضفته. وفي الحقيقة النتيجة المتوقعة: فقط فاسيلينكو اجتاز هذه الجولة))

المهمة 12

الحالة: طباعة جميع "اسم العائلة" و"الاسم" لجميع "المعلم" و"الطالب" مع حقل "النوع" (طالب أو مدرس). قم بالفرز أبجديًا حسب "اسم_الأسرة". يجب أن تبدو هذه:

اسم العائلة	يكتب
إيفانوف	طالب
كانكافا	مدرس
حداد	طالب
سيدوروف	مدرس
بيتروف	مدرس

أي أننا بحاجة إلى الجمع بين مخرجين، وهذا هو بالضبط الغرض من UNION. بمعنى آخر، سنأخذ سجلات من الطلاب ومن المعلمين ونطبعها معًا: $ SELECT last_name, 'teacher' كنوع من المعلم UNION ALL حدد last_name, 'student' كنوع من الطالب ORDER BY last_name; وسيكون هناك معلمون وطلاب. يبدو أن كل شيء بسيط، ولكن هذا عندما ننظر بالفعل إلى النتيجة. ولذا عليك تخمين شيئين.

المهمة 13

الحالة: قم بإضافة عمود "المعدل" إلى جدول "الطالب" الموجود، والذي سيخزن الدورة التدريبية التي يحضرها الطالب حاليًا (قيمة رقمية من 1 إلى 6). ALTER TABLE طالب ADD CONSTRAINT check_rate CHECK (المعدل > 0 والمعدل < 7)؛ نحن هنا نضيف حقلاً من خلال ALTER TABLE ونقوم بالتحقق لتعيين الحد الأقصى لهذا الحقل من 1 إلى 6.

المهمة 14

الحالة: هذا العنصر غير مطلوب، ولكنه سيكون ميزة إضافية. اكتب دالة تمر عبر جميع "الكتب" وتخرج جميع "العناوين" مفصولة بفواصل. هنا تحتاج فقط إلى إرجاع سطر نتيجة الطلب، والذي سيحتوي على جميع عناوين الكتب. هنا مرة أخرى اضطررت إلى جوجل. توجد مثل هذه الوظيفة - GROUP_CONCAT ، والتي يتم من خلالها القيام بذلك بكل بساطة: $ SELECT GROUP_CONCAT(title) from book; وهذا كل شيء...)) جميع المهام الـ 14 جاهزة.

الاستنتاجات

Uuhhh... لم يكن الأمر سهلاً. كان مثيرا للاهتمام. كانت المهام تستحق العناء، وأنا أكثر من متأكد. أثناء قيامنا بهذه المهام، مررنا بأشياء كثيرة لم تكن معروفة من قبل:

• عرض SQL
• GROUP_CONCAT
• اتحاد

وما إلى ذلك وهلم جرا. شكرا لكل من كانت لديه القوة لقراءة وتكرار ما فعلته. من يعرف كيفية تقديم الطلبات بشكل أفضل - اكتب في التعليقات، سأقرأها بالتأكيد)

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