7. SQL البسيطة

SQL، أو "لغة الاستعلام البنيوية" (Structured Query Language)، وسيلة لطرح الأسئلة على قواعد البيانات العلائقية وتحديث بياناتها. وقد رأيت SQL بالفعل عندما أنشأنا قاعدة بياناتنا الأولى. تذكّر:

SELECT postgis_full_version();

لكن ذلك كان سؤالًا عن قاعدة البيانات. والآن بعد أن حمّلنا البيانات في قاعدة بياناتنا، لنستخدم SQL لطرح أسئلة على البيانات! على سبيل المثال:

"ما أسماء جميع الأحياء في مدينة نيويورك؟"

افتح نافذة استعلام SQL في pgAdmin بالنقر على الزر "Query Tool".

_images/pgadmin_05.png

ثم أدخل الاستعلام التالي في نافذة الاستعلام

SELECT name FROM nyc_neighborhoods;

وانقر على الزر Execute Query (المثلث الأخضر).

_images/pgadmin_08.png

سيعمل الاستعلام لبضع (ميلي)ثوانٍ ويعيد 129 نتيجة.

_images/pgadmin_09.png

لكن ماذا حدث هنا بالضبط؟ لفهم ذلك، لنبدأ بـ "الأفعال" الأربعة في SQL:

  • SELECT: يعيد صفوفًا استجابةً لاستعلام

  • INSERT: يضيف صفوفًا جديدة إلى جدول

  • UPDATE: يعدّل صفوفًا موجودة في جدول

  • DELETE: يحذف صفوفًا من جدول

سنعمل بشكل شبه حصري مع SELECT لطرح أسئلة على الجداول باستخدام الدوال المكانية.

7.1. استعلامات SELECT

يكون استعلام التحديد عمومًا على الصيغة:

SELECT some_columns FROM some_data_source WHERE some_condition;

ملاحظة

للاطلاع على ملخص لجميع معاملات SELECT، راجع توثيق PostgreSQL.

some_columns إما أسماء أعمدة أو دوال لقيم الأعمدة. و some_data_source إما جدول واحد أو جدول مركّب ناتج عن ربط جدولين وفق مفتاح أو شرط. أما some_condition فهو مرشّح يقيّد عدد الصفوف المُعادة.

"ما أسماء جميع الأحياء في Brooklyn؟"

نعود إلى الجدول nyc_neighborhoods ومعنا مرشّح. يحتوي الجدول على جميع الأحياء في نيويورك، لكننا نريد فقط الأحياء الموجودة في Brooklyn.

SELECT name
  FROM nyc_neighborhoods
  WHERE boroname = 'Brooklyn';

سيعمل الاستعلام لعدد أقل من (الميلي)ثواني ويعيد 23 نتيجة.

سنحتاج أحيانًا إلى تطبيق دالة على نتائج استعلامنا. على سبيل المثال:

"ما عدد الحروف في أسماء جميع الأحياء في Brooklyn؟"

لحسن الحظ، يحتوي PostgreSQL على دالة لطول السلاسل النصية: char_length(string).

SELECT char_length(name)
  FROM nyc_neighborhoods
  WHERE boroname = 'Brooklyn';

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

يحتوي PostgreSQL على سلسلة من الدوال التجميعية المدمجة، منها الدالة العامة avg() للقيم المتوسطة و stddev() للانحرافات المعيارية.

"ما متوسط عدد الحروف والانحراف المعياري لعدد الحروف في أسماء جميع الأحياء في Brooklyn؟"

SELECT avg(char_length(name)), stddev(char_length(name))
  FROM nyc_neighborhoods
  WHERE boroname = 'Brooklyn';

         avg         |       stddev
---------------------+--------------------
 11.7391304347826087 | 3.9105613559407395

طُبّقت الدوال التجميعية في مثالنا الأخير على كل صف في مجموعة النتائج. فماذا لو أردنا إجراء التلخيصات على مجموعات أصغر ضمن مجموعة النتائج الكلية؟ لذلك نضيف عبارة GROUP BY. وكثيرًا ما تحتاج الدوال التجميعية إلى تعليمة GROUP BY إضافية لتجميع مجموعة النتائج حسب عمود واحد أو أكثر.

"ما متوسط عدد الحروف في أسماء جميع الأحياء في مدينة نيويورك، مصنّفًا حسب المقاطعة (borough)؟"

SELECT boroname, avg(char_length(name)), stddev(char_length(name))
  FROM nyc_neighborhoods
  GROUP BY boroname;

نُدرج العمود boroname في نتيجة الإخراج كي نتمكن من تحديد أي إحصائية تنطبق على أي مقاطعة. ففي الاستعلام التجميعي، يمكنك فقط إخراج الأعمدة التي تكون إما (أ) ضمن عبارة التجميع أو (ب) دوال تجميعية.

   boroname    |         avg         |       stddev
---------------+---------------------+--------------------
 Brooklyn      | 11.7391304347826087 | 3.9105613559407395
 Manhattan     | 11.8214285714285714 | 4.3123729948325257
 The Bronx     | 12.0416666666666667 | 3.6651017740975152
 Queens        | 11.6666666666666667 | 5.0057438272815975
 Staten Island | 12.2916666666666667 | 5.2043390480959474

7.2. قائمة الدوال

avg(expression): دالة تجميعية في PostgreSQL تعيد القيمة المتوسطة لعمود رقمي.

char_length(string): دالة نصية في PostgreSQL تعيد عدد المحارف في سلسلة نصية.

stddev(expression): دالة تجميعية في PostgreSQL تعيد الانحراف المعياري لقيم المُدخلات.