15.4. الدرس: الاستعلامات

عندما تكتب أمر SELECT ... فإنه يُعرف عادةً بالاستعلام - فأنت تستجوب قاعدة البيانات طلبًا للمعلومات.

هدف هذا الدرس: تعلّم كيفية إنشاء استعلامات تُرجع معلومات مفيدة.

ملاحظة

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

insert into people (name,house_no, street_id, phone_no)
          values ('Joe Bloggs',3,2,'072 887 23 45');
insert into people (name,house_no, street_id, phone_no)
          values ('Jane Smith',55,3,'072 837 33 35');
insert into people (name,house_no, street_id, phone_no)
          values ('Roger Jones',33,1,'072 832 31 38');
insert into people (name,house_no, street_id, phone_no)
          values ('Sally Norman',83,1,'072 932 31 32');

15.4.1. ترتيب النتائج

لنسترجع قائمة بالأشخاص مرتبة حسب أرقام منازلهم:

select name, house_no from people order by house_no;

النتيجة:

     name     | house_no
--------------+----------
 Joe Bloggs   |        3
 Roger Jones  |       33
 Jane Smith   |       55
 Sally Norman |       83
(4 rows)

يمكنك فرز النتائج حسب قيم أكثر من عمود:

select name, house_no from people order by name, house_no;

النتيجة:

     name     | house_no
--------------+----------
 Jane Smith   |       55
 Joe Bloggs   |        3
 Roger Jones  |       33
 Sally Norman |       83
(4 rows)

15.4.2. التصفية

في الغالب لن ترغب في رؤية كل سجل في قاعدة البيانات - خاصة إذا كانت هناك آلاف السجلات وأنت مهتم برؤية سجل أو اثنين فقط.

إليك مثالًا على مرشّح رقمي لا يُرجع إلا الكائنات التي تقل قيمة house_no فيها عن 50:

select name, house_no from people where house_no < 50;

      name     | house_no
  -------------+----------
   Joe Bloggs  |        3
   Roger Jones |       33
  (2 rows)

يمكنك الجمع بين المرشّحات (المعرَّفة باستخدام عبارة WHERE) والفرز (المعرَّف باستخدام عبارة ORDER BY):

select name, house_no from people where house_no < 50 order by house_no;

      name     | house_no
  -------------+----------
   Joe Bloggs  |        3
   Roger Jones |       33
  (2 rows)

يمكنك أيضًا التصفية بناءً على البيانات النصية:

select name, house_no from people where name like '%s%';

      name     | house_no
  -------------+----------
   Joe Bloggs  |        3
   Roger Jones |       33
  (2 rows)

استخدمنا هنا عبارة LIKE للعثور على جميع الأسماء التي تحتوي على s. ستلاحظ أن هذا الاستعلام حساس لحالة الأحرف، ولذلك لم يُرجَع المدخل Sally Norman.

إذا أردت البحث عن سلسلة أحرف بغض النظر عن حالتها، فيمكنك إجراء بحث غير حساس لحالة الأحرف باستخدام عبارة ILIKE:

select name, house_no from people where name ilike '%r%';

       name     | house_no
  --------------+----------
   Roger Jones  |       33
   Sally Norman |       83
  (2 rows)

أرجع ذلك الاستعلام كل كائن من people يحتوي اسمه على r أو R.

15.4.3. الربط (Joins)

ماذا لو أردت رؤية تفاصيل الشخص واسم شارعه بدلًا من المعرّف ID؟ لفعل ذلك، تحتاج إلى ربط الجدولين معًا في استعلام واحد. لنلقِ نظرة على مثال:

select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;

ملاحظة

عند الربط، تذكر دائمًا الجدولين اللذين تأتي منهما المعلومات، وهما في هذه الحالة people و streets. وعليك أيضًا تحديد المفتاحين اللذين يجب أن يتطابقا (المفتاح الأجنبي والمفتاح الأساسي). وإن لم تحدد ذلك، فستحصل على قائمة بجميع التوليفات الممكنة من الأشخاص والشوارع، دون أي طريقة لمعرفة من يسكن فعلًا في أي شارع!

إليك شكل المخرجات الصحيحة:

     name     | house_no |    name
--------------+----------+-------------
 Joe Bloggs   |        3 | Low Street
 Roger Jones  |       33 | High street
 Sally Norman |       83 | High street
 Jane Smith   |       55 | Main Road
(4 rows)

سنعود إلى الربط عندما ننشئ استعلامات أكثر تعقيدًا لاحقًا. تذكّر فقط أنه يوفر طريقة بسيطة لجمع المعلومات من جدولين أو أكثر.

15.4.4. التحديد الفرعي (Sub-Select)

تتيح لك التحديدات الفرعية تحديد كائنات من جدول بناءً على بيانات من جدول آخر مرتبط به عبر علاقة مفتاح أجنبي. في حالتنا، نريد العثور على الأشخاص الذين يسكنون في شارع معيّن.

لنجرِ أولًا بعض التعديلات البسيطة على بياناتنا:

insert into streets (name) values('QGIS Road');
insert into streets (name) values('OGR Corner');
insert into streets (name) values('Goodle Square');
update people set street_id = 2 where id=2;
update people set street_id = 3 where id=3;

لنلقِ نظرة سريعة على بياناتنا بعد هذه التغييرات: يمكننا إعادة استخدام الاستعلام من القسم السابق:

select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;

النتيجة:

     name     | house_no |    name
--------------+----------+-------------
 Roger Jones  |       33 | High street
 Sally Norman |       83 | High street
 Jane Smith   |       55 | Main Road
 Joe Bloggs   |        3 | Low Street
(4 rows)

لنعرض لك الآن تحديدًا فرعيًا على هذه البيانات. نريد عرض الأشخاص الذين يسكنون في street_id رقم 1 فقط:

select people.name
from people, (
    select *
    from streets
    where id=1
  ) as streets_subset
where people.street_id = streets_subset.id;

النتيجة:

     name
--------------
 Roger Jones
 Sally Norman
(2 rows)

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

15.4.5. الاستعلامات التجميعية

من الميزات القوية لقاعدة البيانات قدرتها على تلخيص البيانات الموجودة في جداولها. وتُسمى هذه الملخصات الاستعلامات التجميعية (aggregate queries). إليك مثالًا نموذجيًا يخبرنا بعدد كائنات الأشخاص في جدول الأشخاص:

select count(*) from people;

النتيجة:

 count
-------
     4
(1 row)

إذا أردنا تلخيص الأعداد حسب اسم الشارع، فيمكننا فعل ما يلي:

select count(name), street_id
from people
group by street_id;

النتيجة:

 count | street_id
-------+-----------
     2 |         1
     1 |         3
     1 |         2
(3 rows)

ملاحظة

لأننا لم نستخدم عبارة ORDER BY، فقد لا يطابق ترتيب نتائجك ما هو معروض هنا.

جرّب بنفسك: ★★☆

لخّص الأشخاص حسب اسم الشارع واعرض أسماء الشوارع الفعلية بدلًا من قيم street_id.

الإجابة

إليك عبارة SQL الصحيحة التي يجب أن تستخدمها:

select count(people.name), streets.name
from people, streets
where people.street_id=streets.id
group by streets.name;

النتيجة:

count |    name
------+-------------
    1 | Low Street
    2 | High street
    1 | Main Road
(3 rows)

ستلاحظ أننا سبقنا أسماء الحقول بأسماء الجداول (مثل people.name و streets.name). ويجب فعل ذلك كلما كان اسم الحقل ملتبسًا (أي غير فريد عبر جميع جداول قاعدة البيانات).

15.4.6. الخلاصة

رأيت كيف تستخدم الاستعلامات لإرجاع البيانات من قاعدة بياناتك بطريقة تتيح لك استخراج معلومات مفيدة منها.

15.4.7. ما التالي؟

سترى فيما يلي كيف تنشئ طرق عرض (views) من الاستعلامات التي كتبتها.