تفيدنا لغة الاستعلام البنيوية Structured Query Language -أو SQL اختصارًا- في إدارة البيانات المخزنة في نظام إدارة قواعد بيانات علاقية Relational Database Management System -أو RDBMS اختصارًا- ومن أبرز الوظائف المفيدة في SQL هي إنشاء استعلام ضمن استعلام آخر، والذي يُعرَف باسم الاستعلام الفرعي Subquery أو الاستعلام المتداخل Nested وهو موضوع مقالنا اليوم.
متطلبات العمل
يجب توفر حاسوب يشغّل أحد أنواع أنظمة إدارة قواعد البيانات العلاقية RDBMS التي تستخدم لغة SQL. وبالنسبة لهذا المقال فقد اختبرنا التعليمات والأمثلة الواردة فيه باستخدام البيئة التالية:
- خادم عامل على أحدث إصدار من توزيعة أوبنتو Ubuntu مع مستخدم ذو صلاحيات مسؤول مختلف عن المستخدم الجذر، وجدار حماية مُفعَّل كما هو موضح في دليل الإعداد الأولي للخادم مع الإصدار 20.04 من أوبنتو
- نظام MySQL مُثبَّت ومؤمَّن على الخادم كما هو موضّح في مقال كيفية تثبيت MySQL على أوبنتو
وقد نفذنا خطوات هذا المقال باستخدام مستخدم MySQL مختلف عن المستخدم الجذر وفق الطريقة الموضحة في الخطوة 3 من المقال.
ملاحظة: تجدر الإشارة إلى أنّ الكثير من أنظمة إدارة قواعد البيانات العلاقية RDBMS لها تقديماتها الفريدة من لغة SQL، إذ ستعمل الأوامر المُقدمة في هذا المقال بنجاح مع معظم هذه الأنظمة، ولكن قد نجد بعض الاختلافات في الصيغة أو الناتج عند اختبارها على أنظمة مختلفة عن MySQL.
سنحتاج أيضًا إلى قاعدة بيانات تحتوي على جدول يحتوي بيانات تجريبية نموذجية لنتمكّن من التدرب على استخدام الاستعلامات المتداخلة، وفي حال لم تكن متوفرة فيمكن مطالعة القسم التالي لمعرفة كيفية إنشاء قاعدة البيانات والجدول المستخدَمَين في أمثلة هذا المقال.
الاتصال بخادم MySQL وإعداد قاعدة بيانات تجريبية نموذجية
إذا كانت قاعدة بيانات SQL الخاصة بنا تعمل على خادم بعيد، علينا الاتصال بالخادم باستخدام بروتوكول SSH من جهازنا المحلي كما يلي:
$ ssh user@your_server_ip
ثم نفتح واجهة سطر أوامر خادم MySQL مع وضع اسم حساب مستخدم MySQL الخاص بنا مكان user:
$ mysql -u user -p
ننشئ قاعدة بيانات بالاسم zooDB:
mysql> CREATE DATABASE zooDB;
إذا أُنشئِت قاعدة البيانات بنجاح، فسيظهر الخرج التالي:
الخرج Query OK, 1 row affected (0.01 sec)
يمكن اختيار قاعدة البيانات zooDB من خلال تنفيذ تعليمة USE التالية:
mysql> USE zooDB;
الخرج Database changed
اخترنا قاعدة البيانات، وسننشئ جدولًا ضمنها، حيث سننشئ جدولًا يخزّن المعلومات حول الزوار الذين يزورون حديقة الحيوان، وسيحتوي هذا الجدول على الأعمدة السبعة التالية:
-
guest_id: يخزّن قيمًا للزوار الذين يزورون حديقة الحيوان ويستخدم نوع البياناتint، ويمثّل المفتاح الرئيسي Primary Key للجدول، ممّا يعني أن كل قيمة في هذا العمود ستمثّل معرّفًا فريدًا للصف الخاص بها -
first_name: الاسم الأول لكل زائر ويستخدم نوع البياناتvarcharبحد أقصى 30 محرف -
last_name: الاسم الأخير لكل زائر ويستخدم نوع البياناتvarcharبحد أقصى 30 محرف -
guest_type: يحدد هل الزائر بالغ أو طفل ويستخدم نوع البياناتvarcharبحد أقصى 15 محرف -
membership_type: نوع العضوية لكل زائر ويستخدم نوع بياناتvarcharبحد أقصى 30 محرف -
membership_cost: تكلفة أنواع العضوية المختلفة، ويستخدم نوع البياناتdecimalبدقة 5 ومقياس 2، أي أن القيم الموجودة في هذا العمود يمكن أن تحتوي على خمسة أرقام مع وجود رقمين على يمين الفاصلة العشرية -
total_visits: يسجل إجمالي عدد الزيارات لكل زائر ويستخدم نوع البياناتint
سننشئ جدولًا باسم guests يحتوي على الأعمدة السابقة من خلال تشغيل الأمر CREATE TABLE التالي:
mysql> CREATE TABLE guests ( mysql> guest_id int, mysql> first_name varchar(30), mysql> last_name varchar(30), mysql> guest_type varchar(15), mysql> membership_type varchar(30), mysql> membership_cost decimal(5,2), mysql> total_visits int, mysql> PRIMARY KEY (guest_id) mysql> );
ثم ندخل بعض البيانات التجريبية في هذا الجدول الفارغ كما يلي:
mysql> INSERT INTO guests mysql> (guest_id, first_name, last_name, guest_type, membership_type, membership_cost, total_visits) mysql> VALUES mysql> (1, 'Judy', 'Hopps', 'Adult', 'Resident Premium Pass', 110.0, 168), mysql> (2, 'Nick', 'Wilde', 'Adult', 'Day Pass', 62.0, 1), mysql> (3, 'Duke', 'Weaselton', 'Adult', 'Resident Pass', 85.0, 4), mysql> (4, 'Tommy', 'Yax', 'Child', 'Youth Pass', 67.0, 30), mysql> (5, 'Lizzie', 'Yax', 'Adult', 'Guardian Pass', 209.0, 30), mysql> (6, 'Jenny', 'Bellwether', 'Adult', 'Resident Premium Pass', 110.0, 20), mysql> (7, 'Idris', 'Bogo', 'Child', 'Youth Pass', 67.0, 79), mysql> (8, 'Gideon', 'Grey', 'Child', 'Youth Pass', 67.0, 100), mysql> (9, 'Nangi', 'Reddy', 'Adult', 'Guardian Champion', 400.0, 241), mysql> (10, 'Octavia', 'Otterton', 'Adult', 'Resident Pass', 85.0, 11), mysql> (11, 'Calvin', 'Roo', 'Adult', 'Resident Premium Pass', 110.0, 173), mysql> (12, 'Maurice', 'Big', 'Adult', 'Guardian Champion', 400.0, 2), mysql> (13, 'J.K.', 'Lionheart', 'Child', 'Day Pass', 52.0, 1), mysql> (14, 'Priscilla', 'Bell', 'Child', 'Day Pass', 104.0, 2), mysql> (15, 'Tommy', 'Finnick', 'Adult', 'Day Pass', 62.0, 1);
الخرج Query OK, 15 rows affected (0.01 sec) Records: 15 Duplicates: 0 Warnings: 0
نحن الآن جاهزون لبدء استخدام الاستعلامات المتداخلة في لغة SQL.
ما هو الاستعلام المتداخل
الاستعلام في لغة SQL هو عملية تسترجع البيانات من جدول في قاعدة بيانات وتتضمن تعليمة SELECT دائمًا، أما الاستعلام المتداخل nested query فهو استعلام داخل استعلام آخر، بمعنى آخر الاستعلام المتداخل هو تعليمة SELECT توضع بين قوسين عادة، وتُضمَّن في عملية SELECT أو INSERT أو DELETE رئيسية، ويُعَد الاستعلام المتداخل مفيدًا في الحالات التي نريد فيها تنفيذ أوامر متعددة في تعليمة استعلام واحدة بدلًا من كتابة عدة أوامر لإعادة النتيجة المطلوبة، ويمكننا من خلال الاستعلامات المتداخلة إتمام عمليات معقدة على البيانات بطريقة أسهل.
سنستخدم في هذا المقال الاستعلامات المتداخلة مع تعليمات SELECT و INSERT و DELETE، كما سنستخدم الدوال التجميعية Aggregate Functions ضمن استعلام متداخل لمقارنة قيم البيانات بقيم البيانات المفروزة التي حدّدناها باستخدام تعلميتي WHERE و LIKE.
استخدام الاستعلامات المتداخلة مع تعليمة SELECT
لنوضّح فائدة الاستعلامات المتداخلة عمليًا باستخدام البيانات التجريبية التي أضفناها في خطوة سابقة، ولنفترض مثلًا أننا نريد البحث عن جميع الزوار في الجدول guests الذين زاروا حديقة الحيوان بعدد مرات أكبر من متوسط الزيارات. قد نفترض أن بإمكاننا العثور على هذه المعلومات من خلال الاستعلام التالي:
mysql> SELECT first_name, last_name, total_visits mysql> FROM guests mysql> WHERE total_visits > AVG(total_visits);
ولكن سيعيد الاستعلام الذي يستخدم الصيغة السابقة خطأ كما يلي:
الخرج ERROR 1111 (HY000): Invalid use of group function
سبب هذا الخطأ هو أن الدوال التجميعية مثل الدالة AVG() لا تعمل إلا إذا كانت مُنفَّذة ضمن تعليمة SELECT.
أحد الخيارات لاسترجاع هذه المعلومات هو تشغيل استعلام للعثور على متوسط عدد زيارات الزوار أولًا، ثم تشغيل استعلام آخر للعثور على النتائج بناءً على تلك القيمة كما هو الحال في المثالين التاليين:
mysql> SELECT AVG(total_visits) FROM guests;
الخرج +-----------------+ | avg(total_visits) | +-----------------+ | 57.5333 | +-----------------+ 1 row in set (0.00 sec)
mysql> SELECT first_name, last_name, total_visits mysql> FROM guests mysql> WHERE total_visits > 57.5333;
الخرج +----------+---------+------------+ | first_name | last_name | total_visits | +----------+---------+------------+ | Judy | Hopps | 168 | | Idris | Bogo | 79 | | Gideon | Grey | 100 | | Nangi | Reddy | 241 | | Calvin | Roo | 173 | +----------+---------+------------+ 5 rows in set (0.00 sec)
ولكن يمكننا الحصول على مجموعة النتائج نفسها باستخدام استعلام واحد من خلال تداخل الاستعلام الأول (SELECT AVG(total_visits) FROM guests;) مع الاستعلام الثاني. ينبغي أن نضع في الحسبان أن استخدام العدد المناسب من الأقواس مع الاستعلامات المتداخلة أمر ضروري لإكمال العملية التي نريد تنفيذها، لأن الاستعلام المتداخل هو أول عملية تُنفَّذ:
mysql> SELECT first_name, last_name, total_visits mysql> FROM guests mysql> WHERE total_visits > mysql> (SELECT AVG(total_visits) FROM guests);
الخرج +------------+-----------+--------------+ | first_name | last_name | total_visits | +------------+-----------+--------------+ | Judy | Hopps | 168 | | Idris | Bogo | 79 | | Gideon | Grey | 100 | | Nangi | Reddy | 241 | | Calvin | Roo | 173 | +------------+-----------+--------------+ 5 rows in set (0.00 sec)
يوضّح المثال السابق أهمية استخدام استعلام متداخل في تعليمة واحدة كاملة للحصول على النتائج المطلوبة بدلًا من الاضطرار إلى تشغيل استعلامين منفصلين، وفق الخرج الذي حصلنا عليه فقد زار خمسة زوار حديقة الحيوان بعدد مرات أكبر من متوسط الزيارات، ويمكن أن تقدّم هذه المعلومات نظرة مفيدة للتفكير في طرق إبداعية لضمان استمرار الأعضاء الحاليين في زيارة حديقة الحيوان وتجديد عضويتهم كل سنة.
استخدام الاستعلامات المتداخلة مع تعليمة INSERT
لا يقتصر الأمر على تضمين الاستعلام المتداخل في تعليمات SELECT أخرى فقط، إذ يمكننا أيضًا استخدام الاستعلامات المتداخلة لإدخال البيانات في جدول موجود مسبقًا من خلال تضمين الاستعلام المتداخل في عملية INSERT.
لنفترض وجود حديقة حيوانات ما تطلب بعض المعلومات عن زوار حديقة الحيوان خاصة بنا وتود تقديم خصم بنسبة 15% للزوار الذين يشترون العضوية الدائمة في موقعها، لذا سنستخدم تعليمة CREATE TABLE لإنشاء جدول جديد بالاسم upgrade_guests والذي يحتوي على ستة أعمدة، وننتبه جيدًا لأنواع البيانات مثل int و varchar والحد الأقصى من المحارف التي يمكن الاحتفاظ بها، فإن لم تكن متماشية مع أنواع البيانات الأصلية من الجدول guests الذي أنشأناه في قسم إعداد قاعدة بيانات نموذجية، فسنتلقى خطأً عند محاولة إدخال بيانات من الجدول guests باستخدام استعلام متداخل ولن تُنقَل البيانات بطريقة صحيحة.
لننشئ هذا الجدول مع المعلومات التالية:
mysql> CREATE TABLE upgrade_guests ( mysql> guest_id int, mysql> first_name varchar(30), mysql> last_name varchar(30), mysql> membership_type varchar(30), mysql> membership_cost decimal(5,2), mysql> total_visits int, mysql> PRIMARY KEY (guest_id) mysql> );
احتفظنا بمعظم معلومات نوع البيانات في هذا الجدول كما كانت في الجدول guests من أجل التناسق والدقة، وحذفنا أي أعمدة إضافية لا نريدها في الجدول الجديد. أصبح هذا الجدول الفارغ جاهزًا للبدء، والخطوة التالية هي إدخال قيم البيانات المطلوبة في الجدول.
نكتب التعليمة INSERT INTO مع الجدول upgrade_guests الجديد مع وجود اتجاه واضح لمكان إدخال البيانات، ثم نكتب الاستعلام المتداخل باستخدام تعليمة SELECT لاسترجاع قيم البيانات ذات الصلة وتعليمة FROM للتأكد من أن البيانات تأتي من الجدول guests.
سيطبّق خصم بقيمة 15% على الأعضاء الدائمين من خلال تضمين عملية الضرب الرياضية * للضرب بالعدد 0.85 ضمن الاستعلام المتداخل (membership_cost * 0.85)، وتفيدنا تعليمة WHERE في فرز القيم الموجودة في العمود membership_type. يمكننا تضييق نطاق النتائج لتشمل فقط نتائج الأعضاء الدائمين باستخدام تعليمة LIKE ووضع رمز النسبة المئوية % قبل وبعد الكلمة "Resident" بين علامتي اقتباس مفردتين لتحديد نوع العضوية التي تتبع النمط أو المفردات نفسها، وسيُكتَب هذا الاستعلام كما يلي:
mysql> INSERT INTO upgrade_guests mysql> SELECT guest_id, first_name, last_name, membership_type, mysql> (membership_cost * 0.85), total_visits mysql> FROM guests mysql> WHERE membership_type LIKE '%resident%';
الخرج Query OK, 5 rows affected, 5 warnings (0.01 sec) Records: 5 Duplicates: 0 Warnings: 5
يشير الخرج السابق إلى إضافة خمسة سجلات إلى الجدول upgrade_guests الجديد. يمكن التأكد من نقل البيانات التي طلبتها بنجاح من الجدول guests إلى الجدول upgrade_guests الفارغ الذي أنشأناه من خلال تشغيل الأمر التالي مع الشروط التي حدّدناها مع الاستعلام المتداخل وتعليمة WHERE:
mysql> SELECT * FROM upgrade_guests;
الخرج +----------+------------+------------+-----------------------+-----------------+--------------+ | guest_id | first_name | last_name | membership_type | membership_cost | total_visits | +----------+------------+------------+-----------------------+-----------------+--------------+ | 1 | Judy | Hopps | Resident Premium Pass | 93.50 | 168 | | 3 | Duke | Weaselton | Resident Pass | 72.25 | 4 | | 6 | Jenny | Bellwether | Resident Premium Pass | 93.50 | 20 | | 10 | Octavia | Otterton | Resident Pass | 72.25 | 11 | | 11 | Calvin | Roo | Resident Premium Pass | 93.50 | 173 | +----------+------------+------------+-----------------------+-----------------+--------------+ 5 rows in set (0.01 sec)
نلاحظ الإدراج الصحيح لمعلومات عضوية الزائر الدائم "Resident" ذات الصلة من الجدول guest في الجدول upgrade_guests، مع إعادة حساب التكلفة membership_cost الجديدة مع تطبيق خصم 15%، وبذلك ساعدت هذه العملية في تقسيم الجمهور المناسب واستهدافه، وأصبحت الأسعار المخفَّضة متاحة بسهولة لمشاركتها مع الأعضاء الجدد المحتملين.
استخدام الاستعلامات المتداخلة مع تعليمة DELETE
لنفترض أننا نريد إزالة الزوار المعتادين والتركيز فقط على الترويج لخصم البطاقة المميزة للأعضاء الذين لا يزورون حديقة الحيوان كثيرًا حاليًا. نبدأ هذه العملية باستخدام تعليمة DELETE FROM بحيث يكون من الواضح المكان الذي ستحذف البيانات منه، وهو الجدول upgrade_guests في حالتنا، ثم نستخدم تعليمة WHERE لفرز أي قيمة من العمود total_visits تزيد عن الكمية المحدَّدة في الاستعلام المتداخل. نستخدم تعليمة SELECT في استعلامنا المتداخل المُضمَّن للعثور على المتوسط الحسابي AVG للعمود total_visits بحيث تحتوي تعليمة WHERE السابقة على قيم البيانات المناسبة للمقارنة معها. وأخيرًا، نستخدم تعليمة FROM لاسترجاع تلك المعلومات من الجدول guests، وسيكون الاستعلام الكامل كما يلي:
mysql> DELETE FROM upgrade_guests mysql> WHERE total_visits > mysql> (SELECT AVG(total_visits) FROM guests);
الخرج Query OK, 2 rows affected (0.00 sec)
لنتأكّد من حذف هذه السجلات بنجاح من الجدول upgrade_guests، ونستخدم تعليمة ORDER BY لتنظيم النتائج وفق العمود total_visits بترتيب رقمي تصاعدي.
ملاحظة: لن يؤدي استخدام تعليمة DELETE لحذف السجلات من جدولنا الجديد إلى حذفها من الجدول الأصلي، حيث يمكننا تشغيل التعليمة SELECT * FROM original_table للتأكد من وجود جميع السجلات الأصلية، حتى إن كانت محذوفة من الجدول الجديد.
mysql> SELECT * FROM upgrade_guests ORDER BY total_visits;
الخرج +----------+------------+------------+-----------------------+-----------------+--------------+ | guest_id | first_name | last_name | membership_type | membership_cost | total_visits | +----------+------------+------------+-----------------------+-----------------+--------------+ | 3 | Duke | Weaselton | Resident Pass | 72.25 | 4 | | 10 | Octavia | Otterton | Resident Pass | 72.25 | 11 | | 6 | Jenny | Bellwether | Resident Premium Pass | 93.50 | 20 | +----------+------------+------------+-----------------------+-----------------+--------------+ 3 rows in set (0.00 sec)
يشير هذا الخرج إلى نجاح تعليمة DELETE والاستعلام المتداخل في حذف قيم البيانات المُحدَّدة، لذا سيحتوي هذا الجدول الآن على معلومات الزوار الثلاثة الذين عدد زياراتهم أقل من متوسط عدد الزيارات، وهو ما يُعَد نقطة انطلاق لموظف حديقة الحيوان للتواصل مع هؤلاء الزوار بشأن الترقية إلى التذاكر المميزة بسعر مخفَّض لتشجيعهم على الذهاب أكثر إلى حديقة الحيوان.
الخلاصة
شرحنا في هذا المقال الاستعلامات المتداخلة ووضحنا فائدتها في الحصول على نتائج دقيقة لم نكن سنتمكن من الحصول عليها إلا من خلال تشغيل استعلامات منفصلة، وعرضنا أمثلة عملية لاستخدام تعليمات SELECT وINSERT و DELETE مع الاستعلامات المتداخلة لتوفير طريقة أخرى لإدخال البيانات أو حذفها في خطوة واحدة.
ترجمة -وبتصرف- للمقال How To Use Nested Queries in SQL لصاحبته Jeanelle Horcasitas.
أفضل التعليقات
لا توجد أية تعليقات بعد
انضم إلى النقاش
يمكنك أن تنشر الآن وتسجل لاحقًا. إذا كان لديك حساب، فسجل الدخول الآن لتنشر باسم حسابك.