لما لا نضع كل بيانات المشروع بداخل جدول واحد؟
في البداية و قبل الخوض في موضوع ربط معلومات الجداول, يجب أن تعرف لما نقوم بتوزيع بيانات المشروع على عدة جداول بدل وضعها كلها في جدول واحد.
في حال كنت ستضع كل بيانات المشروع في جدول واحد, ستواجه المشاكل التالية:
ستضطر إلى تكرار الكثير من القيم في كل سطر تضيفه.
الحقول التي لا تنوي وضع قيم فيها ستكون قيمتها
NULLو بالتالي أيضاً ستجد الحقول الفارغة تتكرر في كل سطر تضيفه.قد تحتاج إلى مئات الأعمدة حتى تتمكن من تخزين كل المعلومات.
تكرار نفس المعلومات أكثر من مرة في قاعدة البيانات يتطلب مساحة كبيرة للتخزين.
إيجاد المعلومات و إجراء عمليات عليها سيستغرق وقت أكثر كلما أصبح حجم قاعدة البيانات أكبر.
مثال
إذا كنت ستقوم بتخزين معلومات الزبائن و طلبياتهم في جدول واحد فقط كالتالي.
لاحظ كم سيكون هناك حقول فارغة و قيم متكررة لأنك ستضطر إلى أعادة إدخال نفس معلومات الزبون بجانب طلبيته.
| person_id | first_name | last_name | order_number | product_1_name | product_1_price | product_2_name | product_2_price | product_3_name | product_3_price |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Ahmad | Alhazem | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1 | Ahmad | Alhazem | 100 | Computer Screen | 160 | NULL | NULL | NULL | NULL |
| 1 | Ahmad | Alhazem | 215 | Keyboard | 10 | Mouse | 7 | NULL | NULL |
إذا اطلعت جيداً على الجدول ستلاحظ أن كل فاتورة أجراها الزبون تطلبت إعادة إدخال معلوماته (إسمه, إسمه و عائلته) من جديد.
المشكلة الثانية التي تظهر هي أنه يوجد حقول كثيرة فارغة في كل سطر.
المشاكل التي تكلمنا عنها لا تقتصر على التكرار فقط, فمثلاً في حال أردنا تحديث إسم الزبون, سنضطر إلى تحديث إسمه في كل سطر خاص به و ليس مرة واحدة.
خلاصة
لا تفكر إطلاقاً بوضع كل بيانات المشروع بداخل جدول واحد, بل فكر دائماً بأن عليك توزيع بيانات المشروع على عدة جداول بشكل منطقي و مرتب.
كيف يتم ربط الجداول مع بعضها؟
في البداية عليك معرفة أن ربط الجداول مع بعضها هو شيء نحققه من خلال بناء الجداول بشكل مترابط منطقياً.
بمعنى أنك لا تقوم بكتابة أمر خاص حتى تربط قيم الجداول مع بعضها.
ما نفعله حتى نربط البيانات الموضوعة في أي جدول مع البيانات الموضوعة في جدول آخر, هو وضع عامود خاص في كل جدول تكون جميع قيمه موحدة (أي لا يوجد فيها أي تكرار) مثل العامود id الذي كنا نضعه في كل جدول ننشئه تماماً كما في المثال التالي.
مثال
في حال كانت قاعدة البيانات تحتوي على جدولين كالتالي:
الجدول الأول إسمه
countriesو هو مخصص لحفظ معلومات البلدان.الجدول الثاني إسمه
usersو هو مخصص لحفظ معلومات المستخدمين و التي من ضمنها بلد كل مستخدم.
إذا إفترضنا أن الجدول countries يتضمن معلومات 3 بلدان فقط كالتالي.
| countries | ||
|---|---|---|
| id | name | currency |
| 1 | Lebanon | Lebanese Pound |
| 2 | Oman | Omani Riyal |
| 3 | Egypt | Egyption Pound |
و إذا إفترضنا أن الجدول users يتضمن معلومات 5 مستخدمين كالتالي.
و هنا لاحظ أننا لم نكتب أسماء بلدان المستخدمين من جديد, بل وضعنا فقط رقم تعرفة البلد المذكور في الجدول countries.
| users | |||
|---|---|---|---|
| id | first_name | last_name | country_id |
| 1 | Ahmad | Eid | 1 |
| 2 | Ramez | Morad | 3 |
| 3 | Hassan | Mortada | 2 |
| 4 | Saad | Alkassem | 1 |
| 5 | Zaher | Fahmi | 3 |
الآن, إذا وضعنا الجدولين بجانب بعض, سنلاحظ كيف ربطنا قيم الجدول users بقيم الجدول countries من خلال رقم التعرفة id الخاص بكل بلد.
إذا نظرنا أولاً إلى الجدول countries سنجد أن كل سطر فيه يملك رقم id خاص.
إذا نظرنا بعدها إلى الجدول users سنجد أن العامود country_id يستخدم قيم العامود id الموجودة في الجدول countries للإشارة إلى بلدان المستخدمين.
معلومات الجدول countries نقرؤها كالتالي:
البلد رقم
1هو السعوديةKSAو عملته الربال السعوديSaudi Riyal.البلد رقم
2هو عمانOmanو عملته الربال السعوديOmani Riyal.البلد رقم
3هو مصرEgyptو عملته الجنيه المصريEgyption Pound.
إذاً أصبحنا قادرين على معرفة البلد من خلال رقم التعرفة id الخاص به.
معلومات الجدول users يمكننا قراءتها كالتالي:
المستخدم رقم
1إسمهAhmad Eid, و هو من السعوديةKSAلأنه من البلد رقم1.المستخدم رقم
2إسمهRamez Morad, و هو من عمانOmanلأنه من البلد رقم2.المستخدم رقم
3إسمهHassan Mortada, و هو من مصرEgyptلأنه من البلد رقم3.المستخدم رقم
4إسمهSaad Alkassem, و هو من السعوديةKSAلأنه من البلد رقم1.المستخدم رقم
5إسمهZaher Fahmi, و هو من مصرEgyptلأنه من البلد رقم3.
فوائد ربط الجداول مع بعضها
1- التخلص من القيم المكررة
بكل بساطة إذا نظرت للجدولين users و countries ستلاحظ أنهما لا يحتويان على أي حقول فارغة لا داعي لها, و لا يوجد قيم مكررة.
2- تحديث القيمة مرة واحدة لجميع
إذا قمت بتحديث أي معلومة عادية في الجدول countries, فإنها ستتغير بشكل تلقائي بالنسبة كل جدول مرتبط به.
أي إذا قمنا بتحديث قيمة الحقل name و الحقل currency في الجدول countries فإنها ستتحدث بشكل تلقائي بالنسبة لكل مستخدم مرتبط بها في الجدول users حيث أن جميع المستخدمين في هذا الجدول مرتبطين برقم id البلد الثابت في الجدول countries و ليس بقيمه بشكل مباشر.
3- إضافة معلومات جديدة مرة واحدة للجميع
إذا قمت بإضافة عامود جديد في الجدول countries سيتم إضافته أيضاً بالنسبة لجميع المستخدمين في الجدول users.
فمثلاً, في حال قمنا بإضافة عامود جديد في الجدول countries إسمه symbol وضعنا فيه رمز عملة كل بلد كالتالي.
عندها, بدون إجراء أي تعديل على الجدول users سنعرف رمز العملة التي يعرفها كل مستخدم من خلال رقم تعرفة البلد country_id الموضوع مسبقاً.
فعلى سبيل المثال, أي مستخدم يملك country_id يساوي 1 يعرف العملة التي يرمز لها بالحرفين LBP.
4- الدقة في حفظ المعلومات
ما سنتكلم عنه الآن, سنشرحه بتفصيل ممل لاحقاً و لكن خذ فكرة عامة عنه.
إذا تم تعيين العامود country_id كمفتاح أجنبي ( Foreign Key ) بالنسبة للعامود id الموجود في الجدول countries, عندها يصبج العامود country_id قادر على تخزين أرقام من العامود id الموجود في الجدول countries فقط.
في حالتنا, يصبح العامود country_id قادر على أن يخزن الأرقام 1 و 2 و 3 فقط.
بمعنى آخر, إذا حاولت تخزين الرقم 100 في الحقل country_id سيتم منعك من ذلك لأن الحقل id الموجود في الجدول countries لا يوجد فيه القيمة 100.
طريقة ربط القيم الموضوعة في عدة جداول عند استرجاعها
عند حفظ البيانات, شاهدنا كيف أننا نحفظها في عدة جداول بشكل منطقي و مرتب و خالي من أي تكرار.
السؤال الأهم الآن, إذا تم وضع البيانات في عدة جداول, و عند الحاجة لاسترجاعها نريد تجميعها في جدول واحد فقط كيف نفعل ذلك؟
ببساطة, عند جلب البيانات بواسطة الأمر SELECT نقوم بوضع أسماء الجداول التي سنحضر منها القيم و نذكر أسماء الأعمدة التي تربط الجداول باستخدام الكلمة ON فتقوم قاعدة البيانات بتجميع القيم الموجودة في أكثر من جدول و ترجعها كجدول واحد.
كمثال بسيط, يمكنك دمج الجدولين users و countries في جدول واحد عند جلبها كالتالي.
| id | first_name | last_name | country |
|---|---|---|---|
| 1 | Ahmad | Eid | Lebanon |
| 2 | Ramez | Morad | Egypt |
| 3 | Hassan | Mortada | Oman |
| 4 | Saad | Alkassem | Lebanon |
| 5 | Zaher | Fahmi | Egypt |
في الدرس التالي ستفهم أنواع العلاقات بين الجدوال حتى تفهم طريقة بناء الجداول بشكل مترابط.
من بعدها ستتعلم طريقة ربط القيم المشتركة و الموزعة على عدة جداول عند استرجاعها بتفصيل ممل أيضاً.
مفهوم العلاقات في قواعد البيانات
في البداية, قبل بناء أي قاعدة بيانات يجب ان يكون لديك معرفة دقيقة بالمعلومات التي ينوي صاحب المشروع أن يتم تخزينها.
على أساس المعلومات التي ينوي صاحب العمل تخزينها في المشروع, تقوم ببناء قاعدة البيانات حتى تخزنها بما يلائم المشروع.
الآن, بعد معرفة كل المعلومات التي ينوي صاحب المشروع تخزينها, سيكون عليك إنشاء عدة جداول في قاعدة البيانات بهدف تخزين المعلومات التي يتم إدخالها بشكل صحيح خالي من أي تكرار.
عندما يحاول المبرمج المبتدئ إنشاء الجداول في قاعدة البيانات, فإنه في العادة يضيع في نقطة واحدة و هي كيفية توزيع بيانات المشروع على عدة جداول بشكل صحيح حتى يستطيع ربطها مع بعضها من جديد عندما يريد إسترجاعها و هذا ما ستتعلمه بالضبط من هذا الدرس.
بشكل عام, بمجرد أن تفهم نوع العلاقة المنطقية بين معلومات المشروع, تأكد أنك قادر على بناء أي قاعدة بيانات تريد حتى و إن كانت تتألف من ألف جدول.
أنواع العلاقات بين الجداول
عند التفكير بنوع العلاقة بين جدول و آخر, فإنه سيكون أحد الإحتمالات التالية:
واحد لواحد ( One to One ).
واحد لمتعدد ( One to Many ).
متعدد لمتعدد ( Many to Many ).
إعداد رسومات لتوضيح نوع العلاقة بين الجداول
عند إعداد رسم لإيضاح نوع العلاقة بين الجداول, قد تجد إختلاف في طرق الرسم على حسب البرنامج الذي تستخدمه و لكن الفكرة ستكون نفسها تماماً.
بالإضافة إلى ذلك, قد تجد الرسم يضع أسماء الجداول فقط أثناء رسم العلاقات فيما بينها.
و قد تجد البرنامج أكثر دقة حيث يريك كيف أن الأعمدة بداخل الجداول مرتبطة مع بعضها البعض.
علاقة واحد لواحد ( One to One )
هذه العلاقة تعني أن كل قيمة في الجدول, لا يمكن أن يستخدهها الجدول الآخر أكثر من مرة واحدة.
كمثال بسيط, أثناء تسجيل المواطنين في سجلات الدولة الرسمية, تقوم السلطات المختصة بإعطاء كل مواطن رقم فريد يميزه عن بقية المواطنين حتى تتجنب الوقوع بخطأ تشابه الأسماء عند إجراء أي معاملة قانونية متعلقة به. هذا الرقم يسمى الرقم الوطني للشخص.
الرقم الوطني قد يستخدم في أمكان عديدة و لكن في بعض الحالات قد يكون إستخدامه ممكناً مرة واحدة و إليك بعض الأمثلة.
المثال الأول
كل مواطن يعمل في وظيفة ما يتم إعطاؤه رقم ضريبي خاص به في الدولة, هذا الرقم يتم وضعه في الأساس بناءاً على الرقم الوطني للمواطن.
من المنطقي جداً في هذه الحالة أن يكون كل شخص يملك رقم ضريبي واحد لأن هذا الرقم ستستخدمه الدولة لتحدد الضرائب المترتبة على كل مواطن من خلاله.
المثال الثاني
يمكن للمواطن يمكنه الحصول على رخصة قيادة واحدة من نفس النوع, فمثلاً لا يمكن للمواطن إمتلاك رخصتين لقيادة السيارات في وقت واحد.
رقم رخصة القيادة التي تعطى للمواطن يتم وضعه في الأساس بناءاً على الرقم الوطني للمواطن لأنه الشيء الوحيد الذي يضمن تمييزه عن باقي المواطنين.
المثال الثالث
إذا قدمت طلب للحصول على جواز سفر, سيتم تسجيل أن المواطن رقم كذا أصبح يملك جواز السفر رقم كذا. و في حال فقدت جواز سفرك أو أردت الحصول على جواز سفر جديد, تجدهم يقوموا بإتلاف جواز سفرك القديم و يعطوك جواز سفر جديد بدلاً منه. أي تجدهم يسمحوا لك باقتناء جواز سفر واحد باسمك.
إذاً, في حالة جواز السفر فإنه يسمح بأن يتم تسجيل جواز سفر واحد لكل شخص أيضاً.
رقم جواز السفر الذي يعطى للمواطن يتم وضعه في الأساس بناءاً على الرقم الوطني للمواطن لأنه الشيء الوحيد الذي يضمن تمييزه عن باقي المواطنين.
طريقة رسم العلاقة واحد لواحد
شكل الرموز المستخدمة في تحديد شكل علاقة واحد لواحد بين جدول و آخر يختلف من برنامج لآخر و لكن الفكرة هي نفسها.
قد تجد خط بين الجدولين عليه 1 : 1 كالتالي.
قد تجد خط بين الجدولين في كل طرف منه يوجد 1 كالتالي.
قد تجد خط بين الجدولين في كل طرف منه يوجد خط واحد عامودي كالتالي.
في حال كانت العلاقة محددة بدقة بين الجداول, سيتم وصل الخط الذي يربط الأعمدة المشتركة بين الجداول مباشرةً عليها, و هنا يمكننا وضع أي رمز من الرموز السابقة للإشارة إلى أن العلاقة بينهما هي واحد لواحد كالتالي.
طريقة حفظ البيانات في علاقة واحد لواحد
الصورة التالية توضح كيف تكون البيانات مخزنة في الجداول في حال كان نوع العلاقة واحد لواحد.
لاحظ أنه في الجدول passports لا يمكن وجود رقم id أي شخص أكثر من مرة لأنه بالمنطق لا يمكن السماح للشخص بالحصول على أكثر من جواز سفر.
علاقة واحد لمتعدد ( One to Many )
هذه العلاقة تعني أنه كل قيمة في الجدول, يمكن أن يستخدهها الجدول الآخر بالقدر الذي يريده.
هذه العلاقة هي أكثر نوع من العلاقات التي نتعامل معها و إليك بعض الأمثلة:
في موقع فيسبوك, يستطيع المستخدم أن ينشر عدد غير محدد من البوستات و التعليقات.
في موقع تويتر, يستطيع المستخدم أن ينشر عدد غير محدد من التغريدات و التعليقات.
في موقع يوتيوب, يستطيع صاحب القناة أن ينشر عدد غير محدد من الفيديوهات, و يستطيع أن يعلق عليها هو و متابعيه قدر ما شاء إلخ..
طريقة رسم العلاقة واحد لمتعدد
شكل الرموز المستخدمة في تحديد شكل علاقة واحد لمتعدد بين جدول و آخر يختلف من برنامج لآخر و لكن الفكرة هي نفسها.
قد تجد خط بين الجدولين عليه 1 : M كالتالي.
قد تجد خط بين الجدولين في طرف منه يوجد 1 و في طرف آخر يوجد M كالتالي.
قد تجد خط بين الجدولين في طرف منه يوجد 1 و في طرف آخر يوجد % كالتالي.
قد تجد خط بين الجدولين في طرف منه يوجد خط واحد عامودي و في طرف آخر يوجد ثلاث خطوط مرتبطة كالتالي.
في حال كانت العلاقة محددة بدقة بين الجداول, سيتم وصل الخط الذي يربط الأعمدة المشتركة بين الجداول مباشرةً عليها, و هنا يمكننا وضع أي رمز من الرموز السابقة للإشارة إلى أن العلاقة بينهما هي واحد لمتعدد كالتالي.
طريقة حفظ البيانات في علاقة واحد لمتعدد
الصورة التالية توضح كيف تكون البيانات مخزنة في الجداول في حال كان نوع العلاقة واحد لمتعدد.
لاحظ أنه في الجدول posts يمكن وجود رقم user_id أي شخص أكثر من مرة لأنه بالمنطق يمكن للشخص كتابة أكثر من منشور أو مقال.
علاقة متعدد لمتعدد ( Many to Many )
هذه العلاقة تعني أن كل قيمة في الجدول, يمكن أن يستخدهها الجدول الآخر بالقدر الذي يريده و العكس صحيح.
هذا النوع من العلاقات قد يمر معك في بعض الأحيان و هو النوع الوحيد من العلاقات الذي يحتاج طريقة خاصة للتعامل معه.
كمثال بسيط, في حال كنت تبني قاعدة بيانات لجامعة, و تريد تخزين معلومات الطلاب و المواد المسجل بها كل طالب.
هنا الطالب يمكنه أن يتسجل في عدة مواد في نفس الوقت, و المادة الواحدة يمكن أن يتسجل بها عدة طلاب في نفس الوقت أيضاً.
إذاً طبيعة العلاقة بين جدول الطلاب و جدول المواد هي علاقة متعددة.
طريقة رسم العلاقة متعدد لمتعدد
شكل الرموز المستخدمة في تحديد شكل علاقة متعدد لمتعدد بين جدول و آخر يختلف من برنامج لآخر و لكن الفكرة هي نفسها.
قد تجد خط بين الجدولين عليه M : M كالتالي.
قد تجد خط بين الجدولين في كل طرف منه يوجد M كالتالي.
قد تجد خط بين الجدولين في كل طرف منه يوجد % كالتالي.
قد تجد خط بين الجدولين في كل طرف منه يوجد ثلاث خطوط مرتبطة كالتالي.
طريقة إنشاء علاقة متعدد لمتعدد
في مثالنا السابق حول الطلاب و المواد, لا بد لنا أن نضع معلومات الطلاب في جدول و معلومات المواد في جدول آخر.
بعدها يجب أن نفكر بطريقة لربط هذين الجدولين مع بعضهما حتى نستطيع معرفة مواد كل طالب أو من هم طلاب كل مادة.
قاعدة عامة: لربط أي جدولين بشكل تكون العلاقة بينهما متعدد لمتعدد, يجب أن تنشئ جدول ثالث حتى يكون وسيط بينهما.
إذاً في العلاقة التي نوعها متعدد لمتعدد, لا يمكنك ربط معلومات الجدولين مع بعضهما إلا بإضافة جدول ثالث يكون بمثابة وسيط بينهما.
الآن, بما أن كل طالب في جدول الطلاب يملك رقم id خاص به و كل مادة في جدول المواد تملك رقم id خاص بها أيضاً, سنعتمد على هذه الأرقام في الجدول الوسيط من أجل تحديد كل طالب في أي مادة مسجل لأنها الشيء الوحيد الذي نستطيع من خلاله تمييز الطلاب عن بعضهم و تمييز المواد عن بعضها.
عند إضافة جدول وسيط بين الجدولين سيصبح شكل الرسم كالتالي.
لاحظ أننا قمنا بتحويل العلاقة M : M بين الجدولين students و methods إلى علاقتين نوعهما 1 : M عند ربطهما بالجدول الوسيط الذي قمنا بتسميته students_methods و ستفهم السبب بعد قليل.
في حال كانت العلاقة محددة بدقة بين الجداول, سيتم وصل الخط الذي يربط الأعمدة المشتركة بين الجداول مباشرةً عليها, و هنا يمكننا وضع أي رمز من الرموز السابقة للإشارة إلى أن العلاقة بينهما هي واحد لمتعدد كالتالي.
طريقة حفظ البيانات في علاقة متعدد لمتعدد
الصورة التالية توضح كيف تكون البيانات مخزنة في الجداول في حال كان نوع العلاقة متعدد لمتعدد.
لاحظ كيف أصبحنا قادرين على جعل الطالب قادر أن يتسجل في عدة مواد في نفس الوقت, و المادة الواحدة يمكن أن يتسجل بها عدة طلاب في نفس الوقت أيضاً.
إذا كنا سنقرأ المعلومات المخزنة في الجدول students_methods, يمكننا قرائتها إما بالنسبة لكل طالب و إما بالنسبة لكل مادة.
و لنقرأ المعلومات بشكل مفهوم, نقوم بتبديل id الطالب بإسم الطالب. و نقوم بتبديل id المادة بإسم المادة كما سنفعل الآن.
قراءة الجدول بالنسبة لكل طالب
الطالب أحمد مصري مسجل في المادة
Algorithmsو المادةNetwork 1.الطالب وسام العلي مسجل في المادة
Algorithmsو المادةWeb Development.
قراءة الجدول بالنسبة لكل مادة
المادة
Algorithmsمسجل فيها الطالب أحمد المصري و الطالب وسام العلي.المادة
Network 1مسجل فيها الطالب أحمد المصري.المادة
Web Developmentمسجل فيها الطالب وسام العلي.
مفهوم ربط المعلومات عند جلبها
بعد تخزين البيانات في جداول متفرقة و بشكل منطقي كما شاهدنا في الدرسين السابقين, لا بد لنا الآن من معرفة كيف سنسترجع هذه المعلومات بشكل مرتب و كأنها موضوعة في جدول واحد لأن أي معلومات سنطلبها من قاعدة البيانات سترجعها لنا في جدول واحد.
إذاً, في هذا الدرس سنتعلم طرق ربط المعلومات المخزنة في عدة جداول عند جلبها و عرضها في جدول واحد.
طرق ربط القيم التي يتم جلبها من عدة جداول
عند جلب المعلومات المخزنة في عدة جداول في وقت واحد, يوجد 4 طرق أساسية لتحديد كيف سيتم دمج معلوماتهم التي سيتم إرجاعها كالتالي.
إذا اخترت الدمج Inner Join فأنت بذلك تريد الحصول فقط على القيم المشتركة بين الجدولين.
إذا اخترت الدمج Full Join فأنت بذلك تريد الحصول على القيم المشتركة و الغير مشتركة بين الجدولين.
إذا اخترت الدمج Left Join فأنت بذلك تريد الحصول على كل قيم الجدول الأيسر بالإضافة إلى القيم المشتركة معه من الجدول الأيمن.
إذا اخترت الدمج Right Join فأنت بذلك تريد الحصول على كل قيم الجدول الأيمن بالإضافة إلى القيم المشتركة معه من الجدول الأيسر.
تجهيز قاعدة البيانات التي سنطبق عليها
قم بتنفيذ الإستعلام التالي حتى تنشئ قاعدة بيانات جديدة إسمها harmash تحتوي على الجداول التالية:
الجدول الأول إسمه
countriesو هو مخصص لحفظ معلومات البلدان.الجدول الثاني إسمه
usersو هو مخصص لحفظ معلومات المستخدمين و التي من ضمنها بلد كل مستخدم.
الإستعلام
-- سيتم حذفها harmash في حال كان يوجد بالأساس قاعدة بيانات إسمها
DROP DATABASE IF EXISTS harmash;
-- harmash هنا قمنا بإنشاء قاعدة بيانات جديدة إسمها
CREATE DATABASE harmash;
-- harmash هنا قمنا بتحديد أن أي إستعلام جديد سيتم تنفيذه على قاعدة البيانات
USE harmash;
-- يتألف من عامودين countries هنا قمنا بإنشاء جدول جديد إسمه
-- لأننا قمنا بتحديدها قبل إستدعاء هذا الأمر harmash سيتم إنشاء هذا الجدول بداخل قاعدة البيانات
CREATE TABLE countries (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
UNIQUE (name)
);
-- هنا قمنا بإضافة 5 أسطر في الجدول, أي أضفنا معلومات 5 بلدان
-- لاحظ أننا لم نحدد أسماء الأعمدة التي سنضع فيها البيانات لأننا قمنا بملئ جميع المعلومات
-- في البداية هو حتى يكون عدد القيم الموضوعة يساوي عدد أعمدة الجدول null سبب وضع الكلمة
-- ستقوم قاعدة البيانات بوضع رقم تعرفة مختلف لكل سطر null مكان الكلمة
INSERT INTO countries VALUES (null, "KSA");
INSERT INTO countries VALUES (null, "Oman");
INSERT INTO countries VALUES (null, "Egypt");
INSERT INTO countries VALUES (null, "Kuwait");
INSERT INTO countries VALUES (null, "Bahrain");
-- يتألف من 5 أعمدة users هنا قمنا بإنشاء جدول جديد إسمه
-- لأننا قمنا بتحديدها قبل إستدعاء هذا الأمر harmash سيتم إنشاء هذا الجدول بداخل قاعدة البيانات
CREATE TABLE users (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(30),
first_name VARCHAR(50),
last_name VARCHAR(50),
country_id INT,
UNIQUE (username)
);
-- هنا قمنا بإضافة 10 أسطر في الجدول, أي أضفنا معلومات 10 مستخدمين
-- لاحظ أننا لم نحدد أسماء الأعمدة التي سنضع فيها البيانات لأننا قمنا بملئ جميع المعلومات
-- في البداية هو حتى يكون عدد القيم الموضوعة يساوي عدد أعمدة الجدول null سبب وضع الكلمة
-- ستقوم قاعدة البيانات بوضع رقم تعرفة مختلف لكل سطر null مكان الكلمة
INSERT INTO users VALUES (null, "rami", "Rami", "Masri", 1);
INSERT INTO users VALUES (null, "ahmad", "Ahmad", "Naji", 2);
INSERT INTO users VALUES (null, "hanan", "Hanan", "Mostafa", 5);
INSERT INTO users VALUES (null, "saly", "Saly", "Harmush", null);
INSERT INTO users VALUES (null, "samir", "Samir", "Saleh", 3);
INSERT INTO users VALUES (null, "hamad", "Hamad", "Akel", 2);
INSERT INTO users VALUES (null, "abdullah", "Abdullah", "Helmi", 1);
INSERT INTO users VALUES (null, "rashed", "Rashed", "Masri", 5);
INSERT INTO users VALUES (null, "majed", "Majed", "Alali", null);
INSERT INTO users VALUES (null, "rayan", "Rayan", "Kasabi", null);
بعد تنفيذ الإستعلام السابق في phpMyAdmin سيتم إنشاء قاعدة البيانات harmash و إنشاء الجدولين countries و users بداخلها.
قم بالنقر على إسم قاعدة البيانات harmash من القائمة اليسرى حتى تبدأ بالتعامل معها و تطبيق ما ستتعلمه في هذا الدرس.
الجدول التالي يظهر البيانات التي قمنا بإضافتها بشكل إفتراضي في الجدول countries.
| countries | ||
|---|---|---|
| id | name | |
| 1 | KSA | |
| 2 | Oman | |
| 3 | Egypt | |
| 4 | Kuwait | |
| 5 | Bahrain | |
الجدول التالي يظهر البيانات التي قمنا بإضافتها بشكل إفتراضي في الجدول users.
| users | ||||
|---|---|---|---|---|
| id | username | first_name | last_name | country_id |
| 1 | rami | Rami | Masri | 1 |
| 2 | ahmad | Ahmad | Naji | 2 |
| 3 | hanan | Hanan | Mostafa | 5 |
| 4 | saly | Saly | Harmush | NULL |
| 5 | samir | Samir | Saleh | 3 |
| 6 | hamad | Hamad | Akel | 2 |
| 7 | abdullah | Abdullah | Helmi | 1 |
| 8 | rashed | Rashed | Masri | 5 |
| 9 | majed | Majed | Alali | NULL |
| 10 | rayan | Rayan | Kasabi | NULL |
الآن, إذا وضعنا الجدولين بجانب بعض, سنلاحظ كيف ربطنا قيم الجدول users بقيم الجدول countries من خلال رقم التعرفة id الخاص بكل بلد.
ملاحظات حول الجدول users:
عند تحديد بلدان المستخدمين لم نقم بكتابة أسماء البلدان من جديد, بل وضعنا فقط رقم
idالبلد المذكور في الجدولcountries.يوجد 3 مستخدمين لم يتم تحديد بلدهم, أي عندهم قيمة الحقل
country_idتساويNULL.لا يوجد أي مستخدم من البلد الذي يملك رقم
idيساوي4, أي لا يوجد أي مستخدم منKuwait.
كيف يتم ربط قيم الجداول في الإستعلام
حل مشكلة تضارب أسماء الأعمدة
عند جلب البيانات من أكثر من جدول, يجب تحديد إسم الجدول الذي يحتوي على العامود لتضمن أن لا يحدث تضارب في الأسماء.
فعلى سبيل المثال, في كل من الجدولين users و countries يوجد عامود إسمه id.
إذا كنت سنحضر البيانات من هذين الجدولين في نفس الوقت فإنك حتماً ستواجه مشكلة تضارب الأسماء عند محاولة عرض قيم العامود id لأن قاعدة البيانات لن تعرف أي id بالضبط أنت تريد.
كل ما عليك فعله لتجنب حدوث تضارب في الأسماء هو ذكر إسم الجدول الذي تريد إحضار قيم العامود منه قبله كالتالي.
users.id -- users الموجود في الجدول id هنا ستفهم قاعدة البيانات أنك تقصد العامود
countries.id -- countries الموجود في الجدول id هنا ستفهم قاعدة البيانات أنك تقصد العامود
ذكر أسماء الجداول و أسماء الأعمدة التي تربط الجداول ببعضها
نقطة أخرى عليك الإنتباه لها و هي أنك حتى لو ذكرت إسم كل جدول ستحضر قيم أعمدته, فإنك لا تزال مجبر على ذكر إسم كل جدول بعد الكلمة FROM.
بالإضافة إلى ذلك فإنك أيضاً, يجب أن تحدد إسم العامود الذي يربطين الجدولين ببعضهما في نهاية الإستعلام بعد الكلمة ON كالتالي.
SElECT columuns
FROM table1 join_type table2
ON table1.table2_id = table_2.id
فمثلاً, إذا كنت ستحضر قيم من الجدولين users و countries في نفس الإستعلام, فيجب أن تفعل التالي:
ذكر إسم الجدولين
usersوcountriesبعد الكلمةFROMلأنك يجب أن تذكر من أين ستحضر قيم الجداول.وضع كلمة بين إسم الجدولين تحدد من خلالها كيف تريد أن يتم ربط قيمهما.
وضع الكلمة
ONفي النهاية لتحديد أسماء الأعمدة التي تربط الجدولين ببعض.
جلب القيم المشتركة بين الجداول
نقصد بالقيم المشتركة بين الجداول, هو عندما يكون الجدول يشير إلى معلومة موجودة في جدول آخر. عندها تكون المعلومة مشتركة بينهما.
الصورة التالية توضح القيم المشتركة بين الجدولين users و countries.
قاعدة عامة
لجلب الأسطر التي تحتوي قيم مشتركة نضع بين الجداول الكلمة INNER JOIN و التي يمكنك اختصارها بالكلمة JOIN فقط.
لتحديد ما هو الحقل المشترك بين الجداول و الذي يربطهم مع بعض, نستخدم الكلمة ON.
مثال
الإستعلام التالي, يقوم بطباعة id و username و country كل مستخدم بشرط ان يكون المستخدم يملك رقم بلد موجود في الجدول countries.
الإستعلام
SELECT users.id, users.username, countries.name AS 'country' -- هنا قمنا بتحديد الأعمدة التي نريد الحصول علي قيمها مع تحديد كل عامود من أي جدول سيتم جلبه
FROM users JOIN countries -- هنا قمنا بتحديد أسماء الجداول التي سنحضر القيم منها بالإضافة إلى تحديد أننا نريد القيم المشتركة بينهما فقط
ON users.country_id = countries.id; -- countries الموجودة في الجدول id هي قيم العامود users الموجودة في الجدول country_id هنا حددنا أن قيم العامود
الفيديو التالي يشرح كود الإستعلام بدقة و يشرح كيف حصلنا على النتيجة.
سنحصل على النتيجة التالية عند تنفيذ الإستعلام.
لاحظ أن كل مستخدم لا يملك country_id لم يتم وضعه في النتيجة.
| id | username | country |
|---|---|---|
| 1 | rami | KSA |
| 2 | ahmad | Oman |
| 3 | hanan | Bahrain |
| 5 | samir | Egypt |
| 6 | hamad | Oman |
| 7 | abdullah | KSA |
| 8 | rashed | Bahrain |
جلب كل قيم الجدول الأيسر بالإضافة إلى القيم المشتركة معه من الجدول الأيمن
لجلب كل قيم الجدول الأيسر بالإضافة إلى القيم المشتركة معه من الجدول الأيمن, نضع بين الجداول LEFT OUTER JOIN أو LEFT JOIN فقط.
الفكرة هنا هي أنه في حال كانت القيم التي ننوي إحضارها من الجداول الأيسر غير موجودة في الجدول الأيمن فإننا أيضاً نريد وضعها كالتالي.
مثال
الإستعلام التالي, يقوم بطباعة id و username و country كل مستخدم موجود في الجدول users سواء كان يملك رقم بلد محدد أم لا لأننا وضعنا الجدول users من الناحية اليسرى و حددنا أننا نريد ربط الجدولين بطريقة LEFT JOIN.
إذاً في حال كان المستخدم يملك رقم بلد محدد, سيتم إستبداله بإسم بلده.
و في حال لم يكن يملك رقم بلد المستخدم محدد, سيتم وضع القيمة الموجودة أصلاً في العامود.
الإستعلام
SELECT users.id, users.username, countries.name AS 'country' -- هنا قمنا بتحديد الأعمدة التي نريد الحصول علي قيمها مع تحديد كل عامود من أي جدول سيتم جلبه
FROM users LEFT JOIN countries -- countries و القيم المشتركة معها فقط في الجدول users هنا قمنا بتحديد أننا نريد كل قيم الجدول
ON users.country_id = countries.id; -- countries الموجودة في الجدول id هي قيم العامود users الموجودة في الجدول country_id هنا حددنا أن قيم العامود
الفيديو التالي يشرح كود الإستعلام بدقة و يشرح كيف حصلنا على النتيجة.
سنحصل على النتيجة التالية عند تنفيذ الإستعلام.
لاحظ أنه تم وضع كل المستخدمين, و تم وضع إسم بلد كل مستخدم يملك country_id يشير لبلد موجود في الجدول countries.
| id | username | country |
|---|---|---|
| 1 | rami | KSA |
| 2 | ahmad | Oman |
| 3 | hanan | Bahrain |
| 4 | saly | NULL |
| 5 | samir | Egypt |
| 6 | hamad | Oman |
| 7 | abdullah | KSA |
| 8 | rashed | Bahrain |
| 9 | majed | NULL |
| 10 | rayan | NULL |
جلب كل قيم الجدول الأيمن بالإضافة إلى القيم المشتركة معه من الجدول الأيسر
لجلب كل قيم الجدول الأيمن بالإضافة إلى القيم المشتركة معه من الجدول الأيسر, نضع بين الجداول RIGHT OUTER JOIN أو RIGHT JOIN فقط.
الفكرة هنا هي أنه في حال كانت القيم التي ننوي إحضارها من الجداول الأيسر غير موجودة في الجدول الأيمن فإننا أيضاً نريد وضعها كالتالي.
مثال
الإستعلام التالي, يقوم بطباعة id و username و country كل مستخدم بشرط أن يكون يملك رقم بلد موجود في الجدول countries.
كما أنه سيتم ذكر أسماء كل البلدان الموجودة في الجدول countries سواء كان هناك مستخدم من هذا البلد أم لم يكن لأننا وضعنا الجدول countries من الناحية اليمنى و حددنا أننا نريد ربط الجدولين بطريقة RIGHT JOIN.
إذاً في حال كان المستخدم يملك رقم بلد محدد, سيتم إستبداله بإسم بلده.
و في حال كان رقم البلد غير محدد من قبل أي مستخدم فإنه أيضاً سيتم عرضه.
الإستعلام
SELECT users.id, users.username, countries.name AS 'country' -- هنا قمنا بتحديد الأعمدة التي نريد الحصول علي قيمها مع تحديد كل عامود من أي جدول سيتم جلبه
FROM users RIGHT JOIN countries -- countries و القيم المشتركة معها فقط في الجدول users هنا قمنا بتحديد أننا نريد كل قيم الجدول
ON users.country_id = countries.id; -- countries الموجودة في الجدول id هي قيم العامود users الموجودة في الجدول country_id هنا حددنا أن قيم العامود
الفيديو التالي يشرح كود الإستعلام بدقة و يشرح كيف حصلنا على النتيجة.
سنحصل على النتيجة التالية عند تنفيذ الإستعلام.
لاحظ أنه تم وضع كل المستخدمين, و تم وضع إسم بلد كل مستخدم يملك country_id يشير لبلد موجود في الجدول countries.
| id | username | country |
|---|---|---|
| 1 | rami | KSA |
| 2 | ahmad | Oman |
| 3 | hanan | Bahrain |
| 5 | samir | Egypt |
| 6 | hamad | Oman |
| 7 | abdullah | KSA |
| 8 | rashed | Bahrain |
| NULL | NULL | Kuwait |
جلب القيم المشتركة و الغير مشتركة بين الجداول
نقصد بالقيم المشتركة و الغير مشتركة هي أنه يجب إحضار كل المعلومات الموجودة في الجداول مع ربط القيم المشتركة بينهما.
الصورة التالية توضح القيم المشتركة و الغير مشتركة بين الجدولين users و countries.
قاعدة عامة
جلب الأسطر التي تحتوي قيم مشتركة و غير مشتركة يختلف من قاعدة بيانات لأخرى.
في قواعد بيانات SQL Server يمكنك بكل سهولة وضع الكلمة FULL OUTER JOIN بين أسماء الجداول فقط و التي يمكنك اختصارها بالكلمة FULL JOIN أيضاً.
و بالطبع لتحديد ما هو الحقل المشترك بين الجداول و الذي يربطهم مع بعض, نستخدم الكلمة ON.
كحل عام يمكن تطبيقه مع جميع قواعد البيانات بما فيها قواعد بيانات MySQL, يمكنك إحضار كل القيم الموجودة في الجدول الأيسر مع القيم المشتركة معها في الجدول الأيمن و إحضار كل القيم الموجودة في الجدول الأيمن مع القيم المشتركة معها في الجدول الأيسر و من ثم دمجها مع بعض و إزالة القيم المكررة بواسطة الأمر UNION.
SELECT column_name(s)
FROM table1 LEFT JOIN table2
ON table1_column = table2_column
UNION
SELECT column_name(s)
FROM table1 RIGHT JOIN table2
ON table1_column = table2_column;
مثال
الإستعلام التالي, يقوم بجلب معلومات المستخدم سواء كانت مرتبطة بالجدول countries أو غير مرتبطة به.
كما أنه يقوم بجلب أسماء البلدان سواء كانت مرتبطة بالجدول users أو غير مرتبطة به.
الإستعلام
SELECT users.id, users.username, countries.name AS 'country' -- countries بالإضافة إلى القيم المشتركة معها في الجدول users هنا قمنا بإحضار كل قيم الجدول
FROM users LEFT JOIN countries
ON users.country_id = countries.id
UNION -- هنا قمنا بدمج النتيجتين السابقتين في جدول واحد مع عدم وضع أسطر تحتوي على قيم مكررة
SELECT users.id, users.username, countries.name -- users بالإضافة إلى القيم المشتركة معها في الجدول countries هنا قمنا بإحضار كل قيم الجدول
FROM users RIGHT JOIN countries
ON users.country_id = countries.id
الفيديو التالي يشرح كود الإستعلام بدقة و يشرح كيف حصلنا على النتيجة.
سنحصل على النتيجة التالية عند تنفيذ الإستعلام.
لاحظ أنه تم وضع كل مستخدم سواء كان يملك رقم بلد محدد أم لا, و تم وضع كل بلد سواء كان هناك مستخدم منه أم لا.
| id | username | country |
|---|---|---|
| 1 | rami | KSA |
| 2 | ahmad | Oman |
| 3 | hanan | Bahrain |
| 4 | saly | NULL |
| 5 | samir | Egypt |
| 6 | hamad | Oman |
| 7 | abdullah | KSA |
| 8 | rashed | Bahrain |
| 9 | majed | NULL |
| 10 | rayan | NULL |
| NULL | NULL | Kuwait |
الكلمة AS مع الجداول
في الدروس السابقة شاهدنا أنه بإمكاننا تغيير أسماء الجداول عند جلبها بواسطة الكلمة AS.
في هذا الدرس ستتعلم طريقة وضع أسماء مختصرة للجداول حين نجلب المعلومات من أكثر من جدول في نفس الوقت حتى لا تضطر إلى كتابة إسم الجدول كما هو كلما أردنا الإشارة إلى جدول موجود فيه.
الشكل العام لإستخدامها مع الجداول
SELECT alias_name.column1, alias_name.column2, ...
FROM table_name AS alias_name;
إذاً لوضع إسم مختصر للجدول, نضع AS بعد إسم الجدول و من ثم ندخل الإسم المختصر له.
بعد وضع إسم مختصر للجدول, تستخدم هذا الإسم نفسه للوصول لأعمدته.
تجهيز قاعدة البيانات التي سنطبق عليها
قم بتنفيذ الإستعلام التالي حتى تنشئ قاعدة بيانات جديدة إسمها harmash تحتوي على الجداول التالية:
الجدول الأول إسمه
countriesو هو مخصص لحفظ معلومات البلدان.الجدول الثاني إسمه
usersو هو مخصص لحفظ معلومات المستخدمين و التي من ضمنها بلد كل مستخدم.
الإستعلام
-- سيتم حذفها harmash في حال كان يوجد بالأساس قاعدة بيانات إسمها
DROP DATABASE IF EXISTS harmash;
-- harmash هنا قمنا بإنشاء قاعدة بيانات جديدة إسمها
CREATE DATABASE harmash;
-- harmash هنا قمنا بتحديد أن أي إستعلام جديد سيتم تنفيذه على قاعدة البيانات
USE harmash;
-- يتألف من عامودين countries هنا قمنا بإنشاء جدول جديد إسمه
-- لأننا قمنا بتحديدها قبل إستدعاء هذا الأمر harmash سيتم إنشاء هذا الجدول بداخل قاعدة البيانات
CREATE TABLE countries (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
UNIQUE (name)
);
-- هنا قمنا بإضافة 5 أسطر في الجدول, أي أضفنا معلومات 5 بلدان
-- لاحظ أننا لم نحدد أسماء الأعمدة التي سنضع فيها البيانات لأننا قمنا بملئ جميع المعلومات
-- في البداية هو حتى يكون عدد القيم الموضوعة يساوي عدد أعمدة الجدول null سبب وضع الكلمة
-- ستقوم قاعدة البيانات بوضع رقم تعرفة مختلف لكل سطر null مكان الكلمة
INSERT INTO countries VALUES (null, "KSA");
INSERT INTO countries VALUES (null, "Oman");
INSERT INTO countries VALUES (null, "Egypt");
INSERT INTO countries VALUES (null, "Kuwait");
INSERT INTO countries VALUES (null, "Bahrain");
-- يتألف من 5 أعمدة users هنا قمنا بإنشاء جدول جديد إسمه
-- لأننا قمنا بتحديدها قبل إستدعاء هذا الأمر harmash سيتم إنشاء هذا الجدول بداخل قاعدة البيانات
CREATE TABLE users (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(30),
first_name VARCHAR(50),
last_name VARCHAR(50),
country_id INT,
UNIQUE (username)
);
-- هنا قمنا بإضافة 10 أسطر في الجدول, أي أضفنا معلومات 10 مستخدمين
-- لاحظ أننا لم نحدد أسماء الأعمدة التي سنضع فيها البيانات لأننا قمنا بملئ جميع المعلومات
-- في البداية هو حتى يكون عدد القيم الموضوعة يساوي عدد أعمدة الجدول null سبب وضع الكلمة
-- ستقوم قاعدة البيانات بوضع رقم تعرفة مختلف لكل سطر null مكان الكلمة
INSERT INTO users VALUES (null, "rami", "Rami", "Masri", 1);
INSERT INTO users VALUES (null, "ahmad", "Ahmad", "Naji", 2);
INSERT INTO users VALUES (null, "hanan", "Hanan", "Mostafa", 5);
INSERT INTO users VALUES (null, "saly", "Saly", "Harmush", null);
INSERT INTO users VALUES (null, "samir", "Samir", "Saleh", 3);
INSERT INTO users VALUES (null, "hamad", "Hamad", "Akel", 2);
INSERT INTO users VALUES (null, "abdullah", "Abdullah", "Helmi", 1);
INSERT INTO users VALUES (null, "rashed", "Rashed", "Masri", 5);
INSERT INTO users VALUES (null, "majed", "Majed", "Alali", null);
INSERT INTO users VALUES (null, "rayan", "Rayan", "Kasabi", null);
بعد تنفيذ الإستعلام السابق في phpMyAdmin سيتم إنشاء قاعدة البيانات harmash و إنشاء الجدولين countries و users بداخلها.
قم بالنقر على إسم قاعدة البيانات harmash من القائمة اليسرى حتى تبدأ بالتعامل معها و تطبيق ما ستتعلمه في هذا الدرس.
الجدول التالي يظهر البيانات التي قمنا بإضافتها بشكل إفتراضي في الجدول countries.
| countries | ||
|---|---|---|
| id | name | |
| 1 | KSA | |
| 2 | Oman | |
| 3 | Egypt | |
| 4 | Kuwait | |
| 5 | Bahrain | |
الجدول التالي يظهر البيانات التي قمنا بإضافتها بشكل إفتراضي في الجدول users.
| users | ||||
|---|---|---|---|---|
| id | username | first_name | last_name | country_id |
| 1 | rami | Rami | Masri | 1 |
| 2 | ahmad | Ahmad | Naji | 2 |
| 3 | hanan | Hanan | Mostafa | 5 |
| 4 | saly | Saly | Harmush | NULL |
| 5 | samir | Samir | Saleh | 3 |
| 6 | hamad | Hamad | Akel | 2 |
| 7 | abdullah | Abdullah | Helmi | 1 |
| 8 | rashed | Rashed | Masri | 5 |
| 9 | majed | Majed | Alali | NULL |
| 10 | rayan | Rayan | Kasabi | NULL |
الآن, إذا وضعنا الجدولين بجانب بعض, سنلاحظ كيف ربطنا قيم الجدول users بقيم الجدول countries من خلال رقم التعرفة id الخاص بكل بلد.
ملاحظات حول الجدول users:
عند تحديد بلدان المستخدمين لم نقم بكتابة أسماء البلدان من جديد, بل وضعنا فقط رقم
idالبلد المذكور في الجدولcountries.يوجد 3 مستخدمين لم يتم تحديد بلدهم, أي عندهم قيمة الحقل
country_idتساويNULL.لا يوجد أي مستخدم من البلد الذي يملك رقم
idيساوي4, أي لا يوجد أي مستخدم منKuwait.
متى نحتاج وضع أسماء مختصرة؟
الحالة الأولى
إذا كنت تتعامل مع جدول واحد, فأنت لست بحاجة إطلاقاً لذكر إسم الجدول الذي تتعامل مع أعمدته.
عند التعامل مع جدول واحد فقط, لا تحتاج أن تضع إسم مختصر للجدول لأنه من ناحية لا يوجد داعي لذلك, و من ناحية أخرى سيؤدي لزيادة حجم الكود.
فمثلاً, إذا كنا نريد طباعة first_name و last_name المستخدمين الموضوعين في الجدول users.
لاحظ كيف سكون شكل الإستعلام, إذا قمنا بذكر إسم الجدول الذي يحتوي على الأعمدة أو إذا وضعنا إسم مختصر للجدول.
الإستعلام
شكل الإستعلام بدون وضع أسماء مختصرة للجداول.
SELECT first_name, last_name
FROM users;
شكل الإستعلام مع ذكر إسم الجدول الذي نتعامل مع أعمدته.
SELECT users.first_name, users.last_name
FROM users;
شكل الإستعلام إذا وضعنا إسم مختصر للجدول users مثل الحرف u فقط.
SELECT u.first_name, u.last_name
FROM users AS u;
سنحصل على النتيجة التالية عند تنفيذ الإستعلام.
| first_name | last_name |
|---|---|
| Rami | Masri |
| Ahmad | Naji |
| Hanan | Mostafa |
| Saly | Harmush |
| Samir | Saleh |
| Hamad | Akel |
| Abdullah | Helmi |
| Rashed | Masri |
| Majed | Alali |
| Rayan | Kasabi |
الحالة الثانية
إذا كنت تتعامل مع جدولين أو أكثر, يفضل وضع أسماء مختصرة للجداول لأن ذلك سؤدي لتقليل حجم الكود.
بالإضافة إلى أنك بذلك ستتجنب حدوث تضارب في أسماء الجداول كما سبق و شاهدنا في الدرس السابق.
الآن, في حال أردنا طباعة id و username و country كل مستخدم موجود في الجدول users سواء كان يملك رقم بلد محدد أم لا يمكننا أن نربط الجدولين بطريقة LEFT JOIN تماماَ كما فعلنا في الدرس السابق.
هنا ستلاحظ أن حجم الكود سيصبح أقل إذا وضعنا أسماء مختصرة للجداول.
و لك أن تتخيل كم سيصبح حجم الكود أصغر إذا كنا سنحضر المعلومات من أعمدة أخرى و كنا ننوي نضع شروط.
الإستعلام
شكل الإستعلام مع ذكر إسم الجدول الذي نتعامل مع أعمدته.
SELECT users.id, users.username, countries.name AS country
FROM users LEFT JOIN countries
ON users.country_id = countries.id;
شكل الإستعلام إذا وضعنا الحرف u كإسم مختصر للجدول users و الحرف c كإسم مختصر للجدول countries.
SELECT u.id, u.username, c.name AS country
FROM users AS u LEFT JOIN countries AS c
ON u.country_id = c.id;
سنحصل على النتيجة التالية عند تنفيذ الإستعلام.
| id | username | country |
|---|---|---|
| 1 | rami | KSA |
| 2 | ahmad | Oman |
| 3 | hanan | Bahrain |
| 4 | saly | NULL |
| 5 | samir | Egypt |
| 6 | hamad | Oman |
| 7 | abdullah | KSA |
| 8 | rashed | Bahrain |
| 9 | majed | NULL |
| 10 | rayan | NULL |
الحالة الثالثة
إذا كان الجدول نفسه يحتوي على أعمدة قيمها مترابطة ببعضها البعض, في هذه الحالة تكون مجبر على وضع إسم مختصر للجدول حتى تتمكن من جلب البيانات بشكل مترابط.
ربط بيانات الجدول نفسه ببعضها ( Self Join ) لم نتطرق له سابقاً لأنك يجب أن تعرف كيف تضع إسم مختصر للجدول حتى تتمكن من فعل ذلك.
في الدرس التالي ستتعلم كيف يتم ربط بيانات الجدول نفسه ببعضها بتفصيل ممل.
في حال كان الجدول نفسه يحتوي على أعمدة قيمها مترابطة ببعضها البعض و تريد إسترجاعها بشكل مرتب, هنا يجب أن تربط الأعمدة مع بعضها بأسلوب يقال له الربط الذاتي ( Self Join ) لأننا نتعامل مع أعمدة موجودة في نفس الجدول.
حالات نطبق فيها الربط الذاتي
في مواقع التواصل الإجتماعي مثل فيسبوك يمكنك وضع تعليق ( Comment ) على أي منشور ( Post ) تراه و بعدها يمكن لشخص أخر أن يضع تعليق آخر كردّ على تعليقك أنت. إذاً هنا عندنا ربط ذاتي لأن التعليق الجديد مرتبط بالتعليق الأول و كلاهما موضوع في نفس الجدول.
في صفحات الويب نفسها أحياناً تلاحظ أن صفحات الموقع نفسها مربوطة ببعضها فمثلاً تجد الموضع يظهر لك أن الصفحة الفلانية تابعة لصفحة أخرى.
في هذه الحالة أيضاً يوجد ربط ذاتي لأن الصفحة التي يظهر لك أنها تابعة لصفحة أخرى, هي صفحة مرتبطة بها و كلاهما موضوع في نفس الجدول.
تجهيز قاعدة البيانات التي سنطبق عليها
قم بتنفيذ الإستعلام التالي حتى تنشئ قاعدة بيانات جديدة إسمها harmash و تنشئ فيها جدول إسمه employees يحتوي على بيانات 5 موظفين.
الإستعلام
-- سيتم حذفها harmash في حال كان يوجد بالأساس قاعدة بيانات إسمها
DROP DATABASE IF EXISTS harmash;
-- harmash هنا قمنا بإنشاء قاعدة بيانات جديدة إسمها
CREATE DATABASE harmash;
-- harmash هنا قمنا بتحديد أن أي إستعلام جديد سيتم تنفيذه على قاعدة البيانات
USE harmash;
-- يتألف من 5 أعمدة users هنا قمنا بإنشاء جدول جديد إسمه
-- لأننا قمنا بتحديدها قبل إستدعاء هذا الأمر harmash سيتم إنشاء هذا الجدول بداخل قاعدة البيانات
CREATE TABLE employees (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
manager_id INT
);
-- هنا قمنا بإضافة 10 أسطر في الجدول, أي أضفنا معلومات 10 مستخدمين
-- لاحظ أننا لم نحدد أسماء الأعمدة التي سنضع فيها البيانات لأننا قمنا بملئ جميع المعلومات
-- في البداية هو حتى يكون عدد القيم الموضوعة يساوي عدد أعمدة الجدول null سبب وضع الكلمة
-- ستقوم قاعدة البيانات بوضع رقم تعرفة مختلف لكل سطر null مكان الكلمة
INSERT INTO employees VALUES (null, "Rami", null);
INSERT INTO employees VALUES (null, "Ahmad", 1);
INSERT INTO employees VALUES (null, "Hanan", 1);
INSERT INTO employees VALUES (null, "Saly", 1);
INSERT INTO employees VALUES (null, "Samir", 1);
بعد تنفيذ الإستعلام السابق في phpMyAdmin سيتم إنشاء قاعدة البيانات harmash و إنشاء الجدول employees بداخلها.
قم بالنقر على إسم قاعدة البيانات harmash من القائمة اليسرى حتى تبدأ بالتعامل معها و تطبيق ما ستتعلمه في هذا الدرس.
البيانات التي قمنا بإضافتها بشكل إفتراضي في الجدول employees.
| id | name | manager_id |
|---|---|---|
| 1 | Rami | NULL |
| 2 | Ahmad | 1 |
| 3 | Hanan | 1 |
| 4 | Saly | 1 |
| 5 | Samir | 1 |
نلاحظ أن جدول الموظفين يتضمن عامودين متشابهين هما id و manager_id و فكرتهما هي التالية:
العامود
idيحتوي على رقم تعرفة كل موظف.العامود
manager_idيحتوي على رقم تعرفة الموظف الذي يعتبر المدير بالنسبة للموظف.
قراءة بيانات الموظفين
الموظف
Rami, لا يوجد عندهmanager_idو هذا يعني أنه هو المدير.
بالمنطق إذا كنت موظف في شركة و لا يوجد مدير مسؤول عنك, فهذا يعني أنك أنت نفسك مدير الشركة.الموظف
Ahmad, مديره هو الموظف الذي يملك رقمidيساوي1, أي الموظفRamiهو مديره.الموظفة
Hanan, مديرها هو الموظف الذي يملك رقمidيساوي1, أي الموظفRamiهو مديره.الموظفة
Saly, مديرها هو الموظف الذي يملك رقمidيساوي1, أي الموظفRamiهو مديره.الموظف
Samir, مديره هو الموظف الذي يملك رقمidيساوي1, أي الموظفRamiهو مديره.
طريقة ربط معلومات الجدول نفسه ببعضها عند جلبها
في البداية عند ربط قيم الجداول عند جلبها, سبق و عرفنا أنه يوجد 4 أشكال أساسية لربط قيمهم كما في الصورة التالية.
الآن لربط قيم الجدول نفسه ببعضها عند جلبها, نقوم بجلب قيم الجدول نفسه مرتين و من ثم نربطهما ببعضهما كأنهما جدولين منفصلين عن بعضهما بالضبط كما كنا نربط الجداول مع بعضها.
حتى تتمكن من جلب قيم الجدول نفسه مرتين, يجب أن تذكر إسم الجدول مرتين و تعطيه إسمين مختلفين. عندها يمكنك أن تتعامل معه و كأنه جدولين منفصلين.
في مثالنا الحالي, النسخة الأولى من الجدول employees سنقوم بتسميتها e1, و النسخة الثانية سنقوم بتسميتها e2.
أمثلة حول ربط معلومات الجدول نفسه ببعضها عند جلبها
المثال الأول
الإستعلام التالي يقوم بجلب id و name كل موظف لديه مدير بالإضافة إلى name الموظف الذي يعتبر مدير الشخص.
ما فعلناه لربط القيم المشتركة في الجدول نفسه ببعضها هو إعطاء الجدول إسمين مختلفين هما e1 و e2 مع وضع الكلمة JOIN بينهما.
الصورة التالية توضح كيف سيتم ربط قيم الجدولين.
إذاً سيتم جلب قيم العامودين id و name من الجدول e1, و قيم العامود name من الجدول e2.
الإستعلام
SELECT -- هنا قمنا بتحديد الأعمدة التي نريد الحصول علي قيمها مع تحديد كل عامود من أي جدول سيتم جلبه
e1.id AS 'Employee Id', -- الموظف فيه id لأننا سنعرض Employee Id هذا العامود قمنا بتسميته
e1.name AS Employee, -- لأننا سنعرض إسم الموظف فيه Employee هذا العامود قمنا بتسميته
e2.name AS Manager -- لأننا سنعرض إسم الموظف الذي يعتبر بمثابة مدير للموظف Manager هذا العامود قمنا بتسميته
FROM
employees AS e1 JOIN employees AS e2 -- مع القيم المشتركة بينهما فقط e2 و في جدول آخر إسمه e1 في جدول إسمه employees هنا قمنا بتحديد أننا نريد كل قيم الجدول
ON
e1.manager_id = e2.id; -- e2 الموجودة في الجدول id هي قيم العامود e1 الموجودة في الجدول manager_id هنا حددنا أن قيم العامود
سنحصل على النتيجة التالية عند تنفيذ الإستعلام.
| Employee ID | Employee | Manager |
|---|---|---|
| 2 | Ahmad | Rami |
| 3 | Hanan | Rami |
| 4 | Saly | Rami |
| 5 | Samir | Rami |
المثال الثاني
الإستعلام التالي يقوم بجلب id و name كل موظف بالإضافة إلى name الموظف الذي يعتبر مدير الشخص.
ما فعلناه لربط كل القيم الموجودة في الجدول نفسه ببعضها هو إعطاء الجدول إسمين مختلفين هما e1 و e2 مع وضع الكلمة LEFT JOIN بينهما.
الصورة التالية توضح كيف سيتم ربط قيم الجدولين.
إذاً سيتم جلب قيم العامودين id و name من الجدول e1, و قيم العامود name من الجدول e2.
الإستعلام
SELECT -- هنا قمنا بتحديد الأعمدة التي نريد الحصول علي قيمها مع تحديد كل عامود من أي جدول سيتم جلبه
e1.id AS 'Employee Id', -- الموظف فيه id لأننا سنعرض Employee Id هذا العامود قمنا بتسميته
e1.name AS Employee, -- لأننا سنعرض إسم الموظف فيه Employee هذا العامود قمنا بتسميته
e2.name AS Manager -- لأننا سنعرض إسم الموظف الذي يعتبر بمثابة مدير للموظف Manager هذا العامود قمنا بتسميته
FROM
employees AS e1 LEFT JOIN employees AS e2 -- e2 و القيم المشتركة معها فقط من e1 مع كل قيم e2 و في جدول آخر إسمه e1 في جدول إسمه employees هنا قمنا بتحديد أننا نريد كل قيم الجدول
ON
e1.manager_id = e2.id; -- e2 الموجودة في الجدول id هي قيم العامود e1 الموجودة في الجدول manager_id هنا حددنا أن قيم العامود
سنحصل على النتيجة التالية عند تنفيذ الإستعلام.
| Employee ID | Employee | Manager |
|---|---|---|
| 1 | Rami | NULL |
| 2 | Ahmad | Rami |
| 3 | Hanan | Rami |
| 4 | Saly | Rami |
| 5 | Samir | Rami |
المثال الثالث
الإستعلام التالي هو نفس الإستعلام الموضوع في المثال الثاني تماماً مع فرق واحد و هو أنه في حال كان الموظف لا يوجد عنده مدير - أي الحقل Manager عنده يساوي NULL - سيتم تبديل قيمته بعبارة 'No Manager'.
الإستعلام
SELECT
e1.id AS 'Employee Id',
e1.name AS Employee,
IFNULL(e2.name, 'No Manager') AS Manager -- 'No Manager' فارغاً سيتم تبديله بعبارة Manager في حال كان الحقل الموضوع في العامود
FROM
employees AS e1 LEFT JOIN employees AS e2
ON
e1.manager_id = e2.id;
سنحصل على النتيجة التالية عند تنفيذ الإستعلام.
| Employee ID | Employee | Manager |
|---|---|---|
| 1 | Rami | No Manager |
| 2 | Ahmad | Rami |
| 3 | Hanan | Rami |
| 4 | Saly | Rami |
| 5 | Samir | Rami |