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

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

• التحقق من الواجبات المنزلية
  • إضافة مفتاح أساسي
  • إضافة مولدوفا
  • إنشاء جدول المدن
  • إضافة مفتاح خارجي للمدن
  • جزء المكافأة. اختبارات
• بيان التحديد
  • أين المعلمة
  • ترتيب حسب المعلمة
  • المجموعة حسب المعلمة
• العمل في المنزل
• خاتمة

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

التحقق من الواجبات المنزلية

احترام كبير لكل من أكمل المهام بنجاح. هذا يعني أنك تفهم أنك وحدك من يحتاج إلى هذا وأنه يساعدك فقط. ومن فرط في مهمتي، أذكركم بشرط:

1. تحتاج إلى إضافة مفتاح أساسي (المفتاح الأساسي) من حقل المعرف إلى مخطط جدول البلد.
2. أضف دولة أخرى إلى جدول الدولة - مولدوفا.
3. وفقًا لمخطط المقالة السابقة، قم بإنشاء مدينة جدول تحتوي على كافة الحقول الموضحة. ستكون أسماء الحقول كما يلي: المعرف، الاسم، معرف_البلد، السكان.
4. إضافة مفتاح أساسي إلى جدول المدينة.
5. أضف مفتاحًا خارجيًا إلى جدول المدينة.

للبدء، دعونا نستخدم الجزء الأول من المقالة السابقة ونذهب إلى محطة قاعدة البيانات.

إضافة مفتاح أساسي

يمكنك إضافة مفتاح أساسي (PRIMARY KEY) بطريقتين: فورًا عند إنشاء جدول، أو بعد الإنشاء باستخدام ALTER TABLE.

المفتاح الأساسي أثناء إنشاء الجدول

نظرًا لأننا أنشأنا جدولًا بالفعل، وبدون حذفه لن نتمكن من إظهار هذا النهج داخل قاعدة البيانات هذه، فسنقوم ببساطة بإنشاء قاعدة بيانات اختبارية مؤقتة سنفعل فيها كل شيء. دعنا ندخل الأوامر التالية:

• إنشاء قاعدة بيانات جديدة:

  اختبار إنشاء قاعدة بيانات $؛

• إنشاء جدول بإضافة مفتاح أساسي:

  $ إنشاء جدول بلد(id INT, name VARCHAR(30), PRIMARY KEY (id));

بشكل عام، لا شيء معقد. بعد الإعلان عن المتغيرات، يتم إضافة الجزء التالي PRIMARY KEY (id) ، حيث يتم تمرير اسم الحقل الذي سيكون المفتاح الأساسي بين قوسين. ودعونا نرى كيف تغير مخطط الجدول: $ DESC Country؛ كما ترون، ظهرت قيمة PRI في حقل المفتاح لإدخال المعرف .

المفتاح الأساسي بعد إنشاء الجدول

كما قلت سابقًا، يمكن تعيين المفتاح الأول بعد إنشاء الجدول باستخدام ALTER TABLE . سنقوم بتشغيل هذا المثال في قاعدة بيانات مدننا :

• دعنا نذهب إلى قاعدة البيانات الخاصة بنا من الاختبار الأول:

  مدن $USE؛

• دعونا نتحقق من أننا موجودون بالتأكيد في قاعدة البيانات الخاصة بنا (يجب أن يكون هناك حقل آخر - السكان). للقيام بذلك نكتب:

  $ DESC السكان؛

كل شيء صحيح، الطاولة لنا. لنكتب ما يلي:

$ ALTER TABLE Country ADD PRIMARY KEY (id) ؛

• والتحقق منه على الفور باستخدام الأمر:

  بلد DESC $؛

كما ترون من الصورة، كل شيء صحيح، قيمة PRI هي بالضبط حيث ينبغي أن تكون. بالمناسبة، لقد عملنا مع قاعدة بيانات اختبارية. الآن نحن بحاجة إلى حذفه: لماذا نحتاج إلى فوضى الخادم، أليس كذلك؟ للقيام بذلك، نستخدم أمر معروف إلى حد ما: اختبار $ DROP DATABASE؛

إضافة مولدوفا

أولا علينا أن نقرر ما الذي سنسجله. سيكون معرفنا التالي هو 4. سيكون الاسم مولدوفا، ويبلغ عدد سكانها 3550900. لذلك، نقوم بتنفيذ الأمر INSERT INTO الذي نعرفه بالفعل: $ INSERT INTO Country VALUES (4, 'Moldova', 3550900); ونتحقق مما إذا كانت هذه القيمة موجودة بالضبط في قاعدة البيانات: $ SELECT * FROM Country WHERE id = 4; في طلب البيانات، قمت على الفور بتحديد الحقل الذي سيتم البحث فيه، لذلك حصلنا على سجل واحد فقط، وهو ما نحتاجه.

إنشاء جدول المدن

باستخدام الرسم البياني من المقالة الأولى حول قاعدة البيانات، نحصل على المعلومات اللازمة حول الجدول. وسوف تحتوي على الحقول التالية:

• معرف - معرف فريد؛
• الاسم - اسم المدينة؛
• Country_id - المفتاح الخارجي للبلد؛
• السكان — سكان المدينة.

من المجهد بعض الشيء أن تكتب معرفًا فريدًا في كل مرة، ألا تعتقد ذلك؟ أود أن أترك هذا الأمر لسلطات MySQL . وهناك مثل هذه الطريقة - الزيادة التلقائية . نحن بحاجة إلى إضافة هذا إلى الحقل الرقمي، وإذا لم نمرر القيم بشكل صريح، فإن MySQL نفسها ستزيد المعرف بمقدار واحد مقارنة بالمعرف السابق. لذلك، فإن إنشاء جدول سيبدو كما يلي: $ CREATE TABLE city ( id INT AUTO_INCREMENT, name VARCHAR(30), Country_id INT, السكان INT, PRIMARY KEY (id)); دعونا نلقي نظرة على الرسم البياني للجدول لمعرفة ما إذا كان كل شيء قد تم بشكل صحيح: $ DESC city; كما ترون من الرسم البياني للجدول، لدينا وصف جديد لحقل المعرف - الزيادة التلقائية. لذلك فعلنا كل شيء بشكل صحيح. دعونا نتحقق من البيانات في جدول تم تكوينه بالكامل. للقيام بذلك، سنقوم بالجزء الأخير من المهمة - المفتاح الخارجي.

إضافة مفتاح خارجي للمدن

بالنسبة للمفتاح الخارجي، سيكون هناك هذا الأمر: $ ALTER TABLE city ADD FOREIGN KEY (country_id) REFERENCES Country(id); ودعنا نتحقق على الفور من الخطأ في مخطط الجدول: هل تغير على مدار الساعة؟ مدينة $DESC؛

جزء المكافأة. اختبارات

لقد نسيت إضافته إلى المهمة - املأ البيانات التي كانت في لقطة شاشة الجزء الأول. لقد نسيت، والآن سأفعل ذلك بنفسي. وبالنسبة لأولئك المهتمين، يمكنك القيام بذلك بنفسك بدوني وبعد ذلك سنتحقق؛) كانت هناك خاركوف، كييف، مينسك، أوديسا، فورونيج، وسنضيف أيضًا تشيسيناو. لكن هذه المرة لن نرسل المعرفات، بل سنتخطاها: $ INSERT INTO city (name, Country_id, السكان) VALUES ('Kharkov', 1, 1443000), ('Kyiv', 1, 3703100), ('Minsk' ، 3، 2545500)، ('أوديسا'، 1، 1017699)، ('فورونيج'، 2، 1058261)، ('كيشينيف'، 4، 695400)؛ كما ترون، يمكنك إجراء عدة إدخالات في وقت واحد باستخدام أمر INSERT INTO واحد. شيء مفيد، تذكر) وعلى الفور دعونا نرى ما هو موجود في الجدول: $ SELECT * FROM city؛ AUTO_INCREMENT - يعمل تمامًا كما أردنا. ملفات الهوية كلها مملوءة رغم أننا لم نرسلها. المفتاح الخارجي هو شيء تابع. للتحقق مما إذا كان يعمل بشكل صحيح، يمكنك محاولة كتابة مفتاح خارجي غير موجود في الجدول الخارجي. لنفترض أننا قررنا أن المعرف = 5 هو كازاخستان. لكنها في الواقع ليست في جدول البلدان. وللتحقق من أن قاعدة البيانات ستقسم، أضف المدينة - أستانا: $ INSERT INTO city (name, Country_id, السكان) VALUES ('Astana', 5, 1136156); ومن الطبيعي أن نحصل على الخطأ: الآن يتأكد المفتاح الخارجي من أننا لا نحاول تعيين بلد للمدينة غير الموجودة في قاعدة بياناتنا. يمكن اعتبار هذا الجزء من الواجب المنزلي مكتملاً - ننتقل إلى الجزء الجديد :)

بيان التحديد

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

SELECT
  [DISTINCT | DISTINCTROW | ALL]
  select_expression,...
FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula}]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]

هنا يمكنك أن ترى أنه لا يمكنك وضع عامل التشغيل GROUP BY أولاً ثم عامل التشغيل WHERE. يجب أن نتذكر ذلك حتى لا يكون هناك استياء لاحقًا من الأخطاء التي ليس من الواضح من أين أتت. $SELECT * من المدينة؛ لكن من الواضح أن حذف جميع البيانات ليس أمرًا ممتعًا بالنسبة لنا. وهذا هو نفسه تمامًا إذا أردنا طرق المسامير بالمجهر [1] ، [2] . وبما أن قاعدة البيانات تقوم بعمليات التصفية والفرز والتجميع بشكل أسرع بكثير من كود Java، فمن الأفضل ترك هذا الأمر لقاعدة البيانات. لذلك، من خلال تعقيد المهام، سنفتح وظائف جديدة.

أين المعلمة

لتصفية التحديد، يتم استخدام كلمة WHERE . يجب تفسير ذلك على النحو التالي: SELECT * FROM tablename (حدد كافة الحقول من اسم الجدول table) حيث talbe_row = 1 (حيث يساوي حقل table_row في السجلات 1). من المهم ملاحظة أن ترتيب الكلمات الرئيسية في الاستعلام مهم. لا يمكنك كتابة WHERE a =1 FROM table_name SELECT *. هذا أمر جيد بالنسبة للغة الروسية، وقد لا يبدو الأمر في حالة من الفوضى بالنسبة للبعض، لكن هذا غير مقبول بالنسبة لـ SQL. نكتب الاستعلام التالي: $ SELECT * FROM city WHERE Country_id = 1; واخترنا المدن الأوكرانية. ليس سيئا، أليس كذلك؟ ماذا لو أردنا ليس فقط الأوكرانية، ولكن أيضا البيلاروسية؟ لهذا الغرض، يمكننا سرد مجموعة القيم التي يمكن أن يأخذها الحقل: $SELECT * FROM city WHERE Country_id IN(1, 3); ولدينا بالفعل مدن من بلدين تستجيب. ماذا لو كانت هناك شروط متعددة للتصفية؟ لنفترض أننا نريد مدنًا يزيد عدد سكانها عن مليوني نسمة؟ للقيام بذلك، استخدم الكلمات OR و AND : $ SELECT * FROM city WHERE Country_id IN (1, 3) AND السكان > 2000000؛ عظيم، ولكن ماذا لو كنا بحاجة إلى إضافة شرط آخر - للبحث عن الأسماء من خلال تعبير عادي (لن أصف التعبيرات العادية هنا: هنا شخص قام بذلك "لفترة وجيزة" في 4 أجزاء )؟ على سبيل المثال، نتذكر كيفية تهجئة مدينة، ولكن ليس بشكل كامل... للقيام بذلك، يمكنك إضافة الكلمة الأساسية LIKE إلى تعبير التصفية : $ SELECT * FROM city WHERE Country_id IN (1, 3) AND السكان > 2000000 أو اسم مثل "%hark%"; وبهذه الطريقة حصلنا أيضًا على خاركوف. ونتيجة لذلك، يمكننا القول أن بحثنا كان جيدًا جدًا. لكني أرغب في التصنيف ليس حسب الهوية، بل حسب عدد السكان، ولكن كيف؟ نعم بسيط جدا...

ترتيب حسب المعلمة

باستخدام ORDER BY، يمكننا فرز السجلات التي تلقيناها حسب حقل معين. يقوم بفرز الأرقام والسلاسل. دعونا نوسع الاستعلام السابق، ونفرز حسب السكان، ونضيف الترتيب حسب السكان: $ SELECT * FROM city WHERE Country_id IN (1, 3) AND السكان > 2000000 أو الاسم مثل "%hark%" الترتيب حسب السكان؛ وكما نرى فإن الفرز تم بالترتيب الطبيعي، أي بترتيب تصاعدي. وماذا لو أردنا العكس؟ للقيام بذلك، تحتاج إلى إضافة كلمة DESC: $ SELECT * FROM city WHERE Country_id IN (1, 3) AND السكان > 2000000 أو اسم مثل "%hark%" ORDER BY السكان DESC؛ الآن يعتمد الفرز على تقليل عدد السكان. وتقوم قاعدة البيانات بذلك بسرعة كبيرة: لا يمكن مقارنة Collections.sort هناك. الآن دعونا نفرز حسب الصف، حسب الاسم بترتيب عكسي: $ SELECT * FROM city WHERE Country_id IN (1, 3) AND السكان > 2000000 أو الاسم مثل "%hark%" ORDER BY name DESC؛

المجموعة حسب المعلمة

يستخدم لتجميع السجلات حسب حقول محددة. عادةً ما يكون هذا مطلوبًا لاستخدام الوظائف التجميعية... ما هي الوظائف التجميعية؟)) من المنطقي التجميع حسب بعض الحقول إذا كانت متماثلة بالنسبة لسجلات مختلفة. دعونا نلقي نظرة على ما يعنيه هذا باستخدام مثالنا. لنفترض أن المدن لديها مفاتيح خارجية - معرفات الدولة. لذا، فإن المعرف هو نفسه بالنسبة للمدن من نفس البلد. لذلك، يمكنك أخذ السجلات وتجميعها بواسطتها: $ SELECT Country_id, COUNT(*) FROM city GROUP BY Country_id; ولكن بدون وظائف التجميع، يبدو الأمر باهتًا بعض الشيء، يجب أن تعترف بذلك. لذلك، دعونا نلقي نظرة على عدد قليل من الوظائف الأكثر شيوعا:

• COUNT — عدد السجلات، يمكن استخدامه بدون تجميع، ويستخدم كـ COUNT(*) . في حالة التجميع حسب بعض الحقول - COUNT(groupped_field);
• MAX - يبحث عن الحد الأقصى لقيمة حقل معين؛
• MIN - يبحث عن الحد الأدنى لقيمة حقل معين؛
• SUM - يبحث عن مجموع حقل معين؛
• AVG - يجد القيمة المتوسطة.

بشكل عام، يمكن استخدام هذه الوظائف دون تجميع، وعندها فقط سيتم عرض حقل واحد فقط. دعونا نجربها مع سكان مدينتنا: $ SELECT COUNT(*) FROM city; ما طلبوه هو ما حصلوا عليه. فقط عدد السجلات. في بعض الأحيان يكون هذا مفيدًا. على سبيل المثال، إذا أردنا معرفة عدد المقالات لمؤلف معين. ليست هناك حاجة لإخراجهم من قاعدة البيانات وإحصائهم. يمكنك ببساطة استخدام COUNT(). $ حدد متوسط ​​(عدد السكان) من المدينة؛ $ SELECT MIN(السكان) من المدينة؛ وهذا هو المكان الذي يدخل فيه التجميع حيز التنفيذ. على سبيل المثال، المهمة هي الحصول على أصغر مدينة في البلاد. هل تعرف بالفعل كيفية القيام بذلك؟ جرب ذلك بنفسك، ثم شاهد: $ SELECT Country_id as Country, MIN(population) FROM city WHERE GROUP BY Country_id; حتى الآن، لا نرى سوى بطاقة هوية الدولة، لكن هذا لا يهم - في المرة القادمة سنفعل كل شيء. إذن هناك نتيجة بالفعل، وحصلنا على ما أردناه - أصغر مدينة في الدولة ذات المعرف = 1. وستكون بقية الوظائف كما هي. من المهم ملاحظة أن مسح جميع الحقول من خلال * عند استخدام التجميع والتجميع لن ينجح! فكر في الأمر ؛)

العمل في المنزل

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

مهام:

افهم عامل التشغيل HAVING واكتب استعلامًا نموذجيًا للجداول من مثالنا. إذا كنت بحاجة إلى إضافة بعض الحقول أو المزيد من القيم لجعل الأمر أكثر وضوحًا، فقم بإضافتها. إذا أراد أي شخص ذلك، فاكتب حل المثال الخاص بك في التعليقات: وبهذه الطريقة يمكنني أيضًا التحقق منه إذا كان لدي الوقت.
1. قم بتثبيت MySQL Workbench للعمل مع قاعدة البيانات من خلال واجهة المستخدم. أعتقد أننا حصلنا بالفعل على ما يكفي من التدريب على العمل من وحدة التحكم. الاتصال بقاعدة البيانات. إذا كنت تستخدم شيئًا آخر للعمل مع قاعدة البيانات، فلا تتردد في تخطي هذه المهمة. هنا وأكثر سأستخدم MySQL Workbench فقط.
2. اكتب طلبات لتلقيها باستخدام بياناتنا:
   1. أصغر/أكبر دولة من حيث عدد السكان؛
   2. متوسط ​​عدد السكان في البلاد؛
   3. متوسط ​​عدد السكان في البلدان التي تنتهي أسماؤها بالحرف "أ"؛
   4. عدد البلدان التي يزيد عدد سكانها عن أربعة ملايين نسمة؛
   5. فرز البلدان عن طريق خفض عدد السكان؛
   6. فرز الدول حسب الاسم بالترتيب الطبيعي.

خاتمة

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

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