كيفية استخدام الاستعلامات المتداخلة في SQL

مقدمة

يُستخدم لغة الاستعلام الهيكلية (SQL) لإدارة البيانات في نظام إدارة قواعد البيانات العلاقية (RDBMS). وظيفة مفيدة في SQL هي إنشاء استعلام داخل استعلام، المعروف أيضًا باسم الاستعلام الفرعي أو الاستعلام المتداخل. الاستعلام المتداخل هو عبارة عن عبارة SELECT عادةً ما تكون محاطة بقوسين، ومضمنة داخل عملية SELECT أساسية، INSERT، أو DELETE.

في هذا البرنامج التعليمي، ستستخدم الاستعلامات المتداخلة مع عبارات SELECT، INSERT، و DELETE. كما ستستخدم وظائف التجميع داخل استعلام متداخل لمقارنة قيم البيانات مع القيم المرتبة التي حددتها مع عبارات WHERE و LIKE.

المتطلبات الأولية

لمتابعة هذا الدليل، ستحتاج إلى جهاز كمبيوتر يعمل بنظام إدارة قواعد البيانات العلاقية (RDBMS) معين يستخدم SQL. تم التحقق من التعليمات والأمثلة في هذا البرنامج التعليمي باستخدام البيئة التالية:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL مثبت ومؤمن على الخادم. اتبع دليلنا كيفية تثبيت MySQL على Ubuntu 20.04 لإعداد هذا. يفترض هذا الدليل أنك قمت أيضًا بإعداد مستخدم MySQL غير الجذر، كما هو موضح في الخطوة 3 من هذا الدليل.

لممارسة استخدام الاستعلامات المدمجة في هذا البرنامج التعليمي، ستحتاج إلى قاعدة بيانات وجدول محملين ببيانات عينية. إذا لم يكن لديك واحد جاهز للإدراج، يمكنك قراءة الجزء التالي الاتصال بـ MySQL وإعداد قاعدة بيانات عينية لمعرفة كيفية إنشاء قاعدة بيانات وجدول. ستشير هذا البرنامج التعليمي إلى هذه القاعدة بيانات العينية والجدول في جميع أنحاءه.

الاتصال بـ MySQL وإعداد قاعدة بيانات عينية

إذا كانت قاعدة البيانات SQL الخاصة بك تعمل على خادم بعيد، قم بتسجيل الدخول إلى الخادم الخاص بك من جهازك المحلي عبر SSH:

ssh sammy@your_server_ip

بعد ذلك، افتح موجه MySQL، مستبدلاً sammy بمعلومات حساب المستخدم MySQL الخاص بك:

mysql -u sammy -p

قم بإنشاء قاعدة بيانات بالاسم zooDB:

CREATE DATABASE zooDB;

إذا تم إنشاء قاعدة البيانات بنجاح، ستتلقى الإخراج التالي:

Output
Query OK, 1 row affected (0.01 sec)

لتحديد قاعدة البيانات zooDB قم بتشغيل البيان التالي USE:

USE zooDB;

Output
Database changed

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

• guest_id: يخزن قيم للضيوف الذين يزورون الحديقة، ويستخدم نوع البيانات int. هذا أيضًا يعمل كـالمفتاح الأساسي للجدول، مما يعني أن كل قيمة في هذه العمود ستعمل كمعرف فريد لصفها الخاص.
• first_name: يحمل الاسم الأول لكل زائر باستخدام نوع البيانات varchar بحد أقصى 30 حرفًا.
• last_name: يستخدم نوع البيانات varchar، مرة أخرى بحد أقصى 30 حرفًا، لتخزين اسم العائلة لكل زائر.
• guest_type: يحتوي على نوع الزائر (بالغ أو طفل) لكل زائر باستخدام نوع البيانات varchar بحد أقصى 15 حرفًا.
• membership_type: يمثل نوع العضوية التي يحملها كل زائر، باستخدام نوع البيانات varchar لاحتواء بحد أقصى 30 حرفًا.
• membership_cost: يخزن تكلفة مختلف أنواع العضويات. يستخدم هذا العمود نوع البيانات decimal بدقة خمسة ومقياس اثنين، مما يعني أن القيم في هذا العمود يمكن أن تحتوي على خمسة أرقام، واثنين من الأرقام عن يمين الفاصلة العشرية.
• total_visits: يستخدم نوع البيانات int لتسجيل إجمالي عدد الزيارات من كل ضيف.

أنشئ جدولًا باسم guests يحتوي على كل من هذه الأعمدة عن طريق تشغيل الأمر CREATE TABLE التالي:

CREATE TABLE guests (
guest_id int, 
first_name varchar(30),
last_name varchar(30),
guest_type varchar(15),
membership_type varchar(30),
membership_cost decimal(5,2), 
total_visits int,
PRIMARY KEY (guest_id)
); 

بعد ذلك، قم بإدراج بعض البيانات العينية في الجدول الفارغ:

INSERT INTO guests
(guest_id, first_name, last_name, guest_type, membership_type, membership_cost, total_visits)
VALUES
(1, 'Judy', 'Hopps', 'Adult', 'Resident Premium Pass', 110.0, 168),
(2, 'Nick', 'Wilde', 'Adult', 'Day Pass', 62.0, 1),
(3, 'Duke', 'Weaselton', 'Adult', 'Resident Pass', 85.0, 4),
(4, 'Tommy', 'Yax', 'Child', 'Youth Pass', 67.0, 30),
(5, 'Lizzie', 'Yax', 'Adult', 'Guardian Pass', 209.0, 30),
(6, 'Jenny', 'Bellwether', 'Adult', 'Resident Premium Pass', 110.0, 20),
(7, 'Idris', 'Bogo', 'Child', 'Youth Pass', 67.0, 79),
(8, 'Gideon', 'Grey', 'Child', 'Youth Pass', 67.0, 100),
(9, 'Nangi', 'Reddy', 'Adult', 'Guardian Champion', 400.0, 241),
(10, 'Octavia', 'Otterton', 'Adult', 'Resident Pass', 85.0, 11),
(11, 'Calvin', 'Roo', 'Adult', 'Resident Premium Pass', 110.0, 173), 
(12, 'Maurice', 'Big', 'Adult', 'Guardian Champion', 400.0, 2), 
(13, 'J.K.', 'Lionheart', 'Child', 'Day Pass', 52.0, 1), 
(14, 'Priscilla', 'Bell', 'Child', 'Day Pass', 104.0, 2),
(15, 'Tommy', 'Finnick', 'Adult', 'Day Pass', 62.0, 1);

Output
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

بمجرد إدخال البيانات، أنت جاهز للبدء في استخدام الاستعلامات المتداخلة في SQL.

استخدام الاستعلامات المتداخلة مع SELECT

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

لفهم الاستعلامات المتداخلة بشكل أفضل، دعونا نوضح كيف يمكن أن تكون مفيدة باستخدام البيانات العينية من الخطوة السابقة. على سبيل المثال، فلنقل أنك ترغب في العثور على جميع الضيوف في جدول guests الذين زاروا الحديقة بتردد أعلى من المتوسط. قد تفترض أنه يمكنك العثور على هذه المعلومات باستخدام استعلام مثل الآتي:

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > AVG(total_visits);

ومع ذلك، سيؤدي استعلام باستخدام هذا النحو إلى ظهور خطأ:

Output
ERROR 1111 (HY000): Invalid use of group function

السبب في هذا الخطأ هو أن الدوال العامة مثل AVG() لا تعمل ما لم يتم تنفيذها داخل عبارة SELECT.

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

SELECT AVG(total_visits) FROM guests;

Output
+-----------------+
| avg(total_visits) |
+-----------------+
| 57.5333 |
+-----------------+
1 row in set (0.00 sec)

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > 57.5333;

Output
+----------+---------+------------+
| 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;) داخل الثاني. يجب أن تكون على علم بأنه في الاستعلامات المتداخلة، فإن استخدام القوسين بالكمية المناسبة ضروري لإكمال العملية التي ترغب في أدائها. وذلك لأن الاستعلام المتداخل هو أول عملية تُنفذ.

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > 
(SELECT AVG(total_visits) FROM guests);

Output
+------------+-----------+--------------+
| 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 باستخدام استعلام متداخل والبيانات لن تنتقل بشكل صحيح. قم بإنشاء جدولك بالمعلومات التالية:

CREATE TABLE upgrade_guests (
guest_id int, 
first_name varchar(30),
last_name varchar(30),
membership_type varchar(30),
membership_cost decimal(5,2),
total_visits int, 
PRIMARY KEY (guest_id)
); 

للحفاظ على التوافق والدقة، قمنا بالاحتفاظ بمعظم معلومات أنواع البيانات في هذا الجدول نفسه كما هو الحال في جدول guests. كما قمنا بإزالة أي أعمدة إضافية لا نريدها في الجدول الجديد. مع هذا الجدول الفارغ جاهز للاستخدام، الخطوة التالية هي إدراج قيم البيانات المرغوبة في الجدول.

في هذه العملية، اكتب INSERT INTO والجدول الجديد upgrade_guests، بحيث يكون هناك توجيه واضح لمكان إدراج البيانات. بعد ذلك، اكتب استعلامك المتداخل مع عبارة SELECT لاسترجاع قيم البيانات ذات الصلة و FROM للتأكد من أنها قادمة من جدول guests.

بالإضافة إلى ذلك، قم بتطبيق خصم 15٪ على أي من أعضاء “المقيمين” عن طريق تضمين العملية الرياضية للضرب * بواسطة 0.85 داخل الاستعلام المتداخل (membership_cost * 0.85). ثم استخدم عبارة WHERE لفرز القيم في عمود membership_type. يمكنك تحديد نطاق البحث أكثر باستخدام العبارة LIKE وتضع رمز النسبة % قبل وبعد كلمة “Resident” بين علامات اقتباس مفردة لتحديد أي عضويات تتبع نفس النمط ، أو في هذه الحالة نفس الصيغة. سيكون استعلامك كما يلي:

INSERT INTO upgrade_guests
SELECT guest_id, first_name, last_name, membership_type, 
(membership_cost * 0.85), total_visits
FROM guests
WHERE membership_type LIKE '%resident%';

Output
Query OK, 5 rows affected, 5 warnings (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 5

الناتج يشير إلى أن هناك خمسة سجلات تمت إضافتها إلى الجدول الجديد upgrade_guests. للتحقق من نقل البيانات التي طلبتها بنجاح من جدول guests إلى الجدول الفارغ upgrade_guests الذي أنشأته، وبالشروط التي حددتها مع الاستعلام المتداخل وعبارة WHERE، قم بتشغيل الأمر التالي:

SELECT * FROM upgrade_guests;

Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| 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)

وفقًا لهذا الإخراج من جدول upgrade_guests الجديد، تم إدراج معلومات عضوية الضيوف ذات الصلة بـ “المقيمين” من جدول guest بشكل صحيح. بالإضافة إلى ذلك، تم إعادة حساب تكلفة العضوية الجديدة بتطبيق الخصم 15٪. نتيجة لذلك، ساعدت هذه العملية في تقسيم الجمهور المناسب واستهدافه، ولديك الآن الأسعار المخفضة جاهزة لمشاركتها مع هؤلاء الأعضاء المحتملين الجدد.

استخدام الاستعلامات المتداخلة مع DELETE

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

ابدأ هذا العملية بعبارة DELETE FROM حتى يكون واضحًا من أين يتم حذف البيانات، في هذه الحالة، جدول upgrade_guests. ثم، استخدم شرط WHERE لفرز أي total_visits التي تزيد عن القيمة المحددة في الاستعلام المتداخل. في الاستعلام المتداخل المضمن، استخدم SELECT للعثور على المتوسط، AVG، لـ total_visits، حتى يحتوي الشرط السابق WHERE على القيم البيانات المناسبة للمقارنة. وأخيرًا، استخدم FROM لاسترجاع تلك المعلومات من جدول guests. ستكون عبارة الاستعلام الكاملة كالتالي:

DELETE FROM upgrade_guests
WHERE total_visits >
(SELECT AVG(total_visits) FROM guests);

Output
Query OK, 2 rows affected (0.00 sec)

تأكد من حذف تلك السجلات بنجاح من جدول upgrade_guests واستخدم ORDER BY لتنظيم النتائج حسب total_visits بترتيب رقمي تصاعدي:

SELECT * FROM upgrade_guests ORDER BY total_visits;

Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| 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 والاستعلام المدمج تصرفا بشكل صحيح في حذف القيم المحددة. يحتوي هذا الجدول الآن على معلومات الضيوف الثلاثة الذين لديهم أقل عدد من الزيارات من المتوسط، وهو نقطة بداية رائعة للممثل الخاص بالحديقة للتواصل معهم حول الترقية إلى تذكرة متميزة بسعر مخفض ونأمل أن نشجعهم على زيارة الحديقة بشكل أكثر تواترا.

الاستنتاج

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