2 لغة البيانات SQL

السؤال: كيف نصيغ هذا المخطط بلغة البرمجة؟

الجواب المختصر: باستعمال لغة البرمجة SQL

إذا كنت تحب الممارسة مباشرة فإليك هذا الموقع: https://pgexercises.com/ حيث يتم طرح مسائل تتطلب منك البرمجة بلغة SQL، وتعرف ما إذا كانت إجابتك صحيحة أو لا فوْرًا.

وإذا كنت تحب الاستزادة على هذا الدرس، فراجع: دورة sql المقدمة من جامعة هارفرد.

ما هي لغة SQL؟

لغة الاستعلام المنضبطة (Structured Query Language) لغة برمجة أنشئت عام 1974م ولا تزال الأشهر لإدارة ومعالجة البيانات في نظم إدارة قواعد البيانات العلائقية (RDBMS) إلى يومنا هذا. بل لا يكاد يخلو وصف وظيفي لمطور / مهندس برمجيات (Software Engineer) أو عالم بيانات (Data Scientist) من تطلُّب مستوى أساسي إلى متوسط في هذه المهارة.

من حيث اللغة، هي قريبة من الإنجليزية؛ إذْ هي لغة وصفية (Declarative) حيث نكتب شكل النتيجة؛ ليقرر النظام كيفية الحصول عليها بخطَّة مكونة من تعليمات تفصيلية ينفذها.

وهي على النقيض من اللغات الإجرائية (Procedural) مثل بايثون وجافا وجافا سكريبت وسي وجو ورست وغيرها= التي يتم كتابة الخطوات التفصيلية نفسها التي يتم تنفيذها بالتتابع.

ما سبب اختلاف نظم قواعد البيانات؟

تأمل المثالين التاليين..

مثل أن تستعلم: “من هم عملاؤنا في مدينة كذا؟”

SELECT CustomerName
FROM Customers
WHERE City = 'Riyadh';

أو تستعلم: “كم هو متوسط ما دفعه العميل 123 خلال جميع طلباته؟”

SELECT AVG(Total)
FROM Orders
WHERE CustomerID = 123;

لاحظ أن الأمر هو اختيار (SELECT) النتيجة مباشرةً ويتم تعيين الجدوَل (Orders) مباشرةً. ولا تجد حلقات (loop) أو أو شروط (if-else)؛ بل يتولى المفسِّر تأويل الخطوات بأفضل طريقة ممكنة لاسترجاع هذه البيانات بناء على:

طريقة الفهرسة والهيكلة للبيانات (مثل: B-Tree)
طريقة التخزين الحقيقية في أجهزة العتاد (Hardware) بحسب نوعها

وهذا من نقاط التمايز بين مطوري قواعد البيانات نفسها؛ فمثلاً:

فتستعمل SQLite لسهولتها وعدم الحاجة لسعة كبيرة؛ مثلاً: لأجهزة الجوال
وتستعمل PostgreSQL لتضافر جهود المجتمع البرمجي عليها لكونها مفتوحة المصدر
وتستعمل CockroachDB لدعمها التوسع الأفقي (Horizontal Scalability) مع المحافظة على ضمانات الاتساق (ACID)؛ لكنها حديثة نسبيًّا

وهكذا فإن لكل نظام طريقة يقرأ فيها نفس الأوامر، ليفسرها بطريقة مختلفة تتناسَب مع الوظيفة التي بني النظام ليكون رائدًا فيها.

الأوامر في لغة SQL

لا يخلوا الأمر في لغة SQL من أحد هذه الأصناف الأربعة:

الأول: لغة تعريف البيانات (DDL): تُستخدم لتعريف بنية قاعدة البيانات ومكوناتها مثل المخططات المتضمنة للجداول المتضمنة للأعمدة. وتشمل أوامر مثل:

للإنشاء: CREATE
للتغيير: ALTER
للحذف: DROP

الثاني: لغة معالجة البيانات (DML): تُستخدم للتعامل مع الصفوف تجمعها حروف كلمة (CRUD) وهي:

الإنشاء (Create) ويقابلها: INSERT
القراءة (Read) ويقابلها: SELECT
التحديث (Update) ويقابلها: UPDATE
الحذف (Delete) ويقابلها: DELETE

الثالث: لغة التحكم في البيانات (DCL): تُستخدم للتحكم في صلاحيات الوصول إلى البيانات وتحديد المسموح للمستخدمين، وتشمل أوامر مثل: GRANT, REVOKE.

الرابع: لغة التحكم في المعاملات (TCL): تُستخدم لإدارة المعاملات وضمان سلامة تنفيذها أو التراجع عنها عند الخطأ، وتشمل أوامر مثل: COMMIT, ROLLBACK, SAVEPOINT.

أولاً: لغة تعريف البيانات (DDL)

فمن التعريف: CREATE DATABASE - تعريف قاعدة CREATE TABLE - تعريف جدول ALTER TABLE - تغيير أعمدة CREATE USER - تعريف مستخدم

إنشاء جدول

مثال جملة تعريف جدوَل بالاسم products وفيه ثلاثة أعمدة تمثل ثلاثة صفات:

الأولى (id) معرِّف الجدوَل من النوع (SERIAL وهو رقم تسلسلي 1 ثم 2 ثم 3 …إلخ.) وموسوم بأنه المفتاح الأساسي (PRIMARY KEY)
الثانية (name) وهو اسم المنتج، من النوع (VARCHAR(64)) أي: نصّ يصل توطله إلى 64 حرفٍ كحد أقصى، وموسومٌ بقيْد عدم العدم؛ أي: ألا يكون غير معيَّن ولو بالنص الفارغ
الثالثة (price) وهو سعر المنتج، من النوع (INTEGER) أي: العدد الصحيح، وله نفس القيد السابق.

CREATE TABLE products (
   id    SERIAL      PRIMARY KEY,
   name  VARCHAR(64) NOT NULL,
   price INTEGER     NOT NULL
);

لاحظنا أن الاسم يكون أولاً، ثم النوع، ثم القيود الإضافية.

أما حذف الجدول فبكلمة DROP على النحو التالي:

DROP TABLE products;

وبذلك يختفي الجدول وما معه من بيانات. لذلك تعامل معها بحذر شديد!

راجع الصفحة المرجعية لأمر إنشاء الجدول: https://www.postgresql.org/docs/current/sql-createtable.html

هل يجب وضع الفاصلة المنقوطة `;` ؟

نعم. يجب وضع الفاصلة المنقوطة ; للدلالة على نهاية الجُملة. كما أن النقطة علامة على نهاية الجملة في اللغة العربية والإنجليزية .

الفهرسة

إن جواب الاستعلام يكون بإحدى طريقتين

الأولى: المسح الكامل (Full Scan): أن ينظر في الجدوَل صفًّا صفًّا من أوله إلى آخره. فإذا كان لديك مليون صف، قاعدة البيانات ستقرأ المليون صف واحدًا تلو الآخر. الزمن: يزداد خطيًا مع حجم البيانات (قد يستغرق دقائق).

الثانية: الاستعانة بفهرس (Index): أن يستعمل إحدى خوارزميات الفهرسة بطرائقها المتعددة (مثل: B-Tree أو Hash أو GiST). ففي مليون صف، قد تحتاج فقط لـ 3 أو 4 قفزات. الزمن: شبه فوري (أجزاء من الثانية).

ومثال إنشاء فهرس للعمود id من جدول products هو القطعة التالية:

CREATE INDEX products_id_index ON products (id);

للمزيد انظر مرجع الفهارس: https://www.postgresql.org/docs/current/indexes.html

تعريف أنواع البيانات

وكل صفة يتم تعريفها لا بد لها من نوع. فمن الأنواع:

الأعداد
النصوص
الوقت
المعرِّف

وذلك يؤثر في ثلاثة أمور:

حجم أصغر في التخزين
كفاءة في عملية الاسترجاع (سرعة أعلى وتكلفة أقل)
إتاحة المقارنة والمعالجة في جملة الاستعلام نفسها

أولاً: أنواع العدد

النوع INTEGER | INT
- الحجم: 4 بايت
- المدى: من سالب 2147483648 حتى موجب 2147483647
النوع BIGINT
- الحجم: 8 بايت
- المدى: من سالب 9223372036854775808 حتى موجب 9223372036854775807
النوع DECIMAL
- يصل إلى 131072 رقم قبل الفاصلة العشرية
- ويصل إلى 16383 رقم بعد الفاصلة العشرية

ثانيًا: نوع الوقت

النوع TIMESTAMP (الطابع الزمني) - يمثل عدد الثواني منذ بزوغ فجر نظام لِنكس. أي منذ:

1970-01-01 00:00:00 YYYY-MM-DD hh:mm:ss

ثالثًا: أنواع النصوص

النوع CHAR - يمثل سلسلة أحرف ذات طول ثابت دائمًا (من 1 إلى 255) بغض النظر عن القيمة الفعلية.
النوع VARCHAR - يمثل سلسلة أحرف ذات طول متغير (أقصى طول من 0 إلى 65,535) لكن الطول الفعلي سيكون بحسب كل قيمة تُخزَّن.

رابعًا: المعرِّف الفريد العالمي

المعرف الفريد العالم (UUID) هو سلسلة من الأرقام السداسية العشرية الصغيرة، في عدة مجموعات تفصلها شرطات: 8 + 4 + 4 + 4 + 12 = 32 رقمًا يمثل 128-بت. مثال:

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

تضمن الخوارزمية المولِّدة لهذا المعرفة استحالة تشابهه مع معرِّف آخر تم توليده بنفس الخوارزمية، ولو كان من طرف آخر. لذا فهي المعتمَدة في الأنظمة الموزعة؛ لضمان التفرُّد.

القيود الإضافية

من القيود (Constraints) الإضافية اللتي تضمنها SQL التالي: NOT NULL عدم العدم PRIMARY KEY مفتاح رئيسي UNIQUE التفرُّد

أمر تغيير أعمدة الجدول

مثال ذلك، إضافة عمود جديد للجدول (products):

ALTER TABLE products
ADD COLUMN description VARCHAR(255);

لاحظ أن ما بعد كلمة ADD COLUMN هو مثل التعريف، فالاسم أولاً (description) ثم النوع (VARCHAR(255)) ثم القيود الإضافية لكن لا وجود لها في هذا المثال.

ومثال آخر، حذف العمود ذاته من الجدول بالكلمة DROP على النحو التالي:

ALTER TABLE products
DROP COLUMN description;

وهذه أيضًا يجب التعامل معها بحذر، إذْ أن البيانات تُمحى نهائيًّا من غير رجعة!

وانظر الصفحة المرجعية لأمر تغيير الجدول: https://www.postgresql.org/docs/current/sql-altertable.html

ثانيًا: لغة معالجة البيانات (DML)

ومن المعالجة: INSERT - إدراج صف SELECT - استرجاع صفوف UPDATE - تغيير صفوف DELETE - حذف صفوف

إدراج الصفوف

مثال للأمر إدراج ثلاثة صفوف:

INSERT INTO products (name, price) VALUES
('Laptop', 1200),
('Keyboard', 75),
('Mouse', 25);

لاحظ وجوب الفاصلة , للفصل بين القيم وبين الصفوف.
وكذلك وجود الفاصلة المنقوطة بعد الصف الأخير.

راجع الصفحة المرجعية لأمر إدراج الصفوف: https://www.postgresql.org/docs/current/sql-insert.html

حذف الصفوف

مثال أمر حذف صفوف بشرط:

DELETE FROM products
WHERE id = 3;

ويجب التعامل معها بحذر؛ فإن عُدم الشرط، تم حذف جميع الصفوف!

راجع الصفحة المرجعية لأمر حذف الصفوف: https://www.postgresql.org/docs/current/sql-delete.html

تحديث الصفوف

مثال أمر تحديث الصفوف بشرط:

UPDATE products
SET price = 1300
WHERE id = 1;

ويجب التعامل معها بحذر؛ فإن عُدم الشرط تم تعديل جميع الصفوف!

راجع الصفحة المرجعية لأمر تحديث الصفوف: https://www.postgresql.org/docs/current/sql-update.html

لغة التحكم في المعاملات (TCL)

المعاملة (Transaction) مفهوم أساسي لجميع أنظمة قواعد البيانات؛ جوهرها صياغة مجموعة عمليات في جملة واحدة يتعلق نفوذها بنفوذ كل خطواتها، وبفشل واحدة منها يتم استعادة الحال (rollback) قبل الجملة كلها (إما غانم، وإما سالم all-or-nothing).

ومن خصائصها: العزل (Isolation) أي أنها أثناء عملها لا تؤثر على أي معاملة أخرى متزامنة معها (concurrent).

وهذه الآلية مدعومة في نظم قواعد البيانات بما يسمى لغة التحكم في المعاملات (TCL)؛ المتضمنة كلمات مثل: BEGIN و COMMIT و ROLLBACK. ونمثل لها بمثال تحويل أموال من حساب إلى حسابٍ آخر؛ حيث لا نحتمل إطلاقًا أن تعمل الخطوة الأولى (الخصم) وتفشل الثانية (الزيادة):

BEGIN;
    UPDATE accounts SET balance = balance - 100.00
        WHERE name = 'Adam';
    UPDATE accounts SET balance = balance + 100.00
        WHERE name = 'Belal';
COMMIT;

يراجع في ذلك صفحة المعاملات في توثيق بوتسقرس (Postgresql).

أوامر الاستعلام

وجملة الاستعلام تبتدأ بكلمة SELECT، وهي أكثر الأوامِر استخدامًا من قسم أوامِر المعالجة (DML). وذكرنا أنها على نوعين: اختيار وتلخيص.

وقد يكون الاستعلام مركَّبًا من استعلام مضمَّن (Sub-query).

أولاً: الاختيار

نتيجة الاختيار: مجموعة صفات مستخرجة من عدة سجلات تكوِّن بمجموعها جدولاً أو صفًّا واحدًا.

تأمل المثال التالي:

SELECT name FROM Customers
WHERE Country = 'Germany'
  AND (Age < 40 OR Age > 60);

تُقرأ الجملة بهذا الترتيب:

من جدوَل الزبائن (FROM Customers)
رشِّح السجلات التي الشرط:
1. WHERE Country = 'Germany'
2. AND (Age < 40 OR Age > 60)
واستخرج صفة الاسم من كل منها (SELECT name)

وليست كل الكلمات دائمًا مطلوبة. تأمل المثال التالي:

SELECT * FROM Products
ORDER BY Price DESC
LIMIT 5;

وتُقرأ بهذا الترتيب:

من جدول المنتجات (FROM Products)
استخرج جميع الصفات (SELECT *)
ثم رتب النتائج بالسعر تنازليًّا (ORDER BY Price DESC) وعكسه (ASC)
ثم اقتصر على النتائج الخمس الأولى (LIMIT 5)

فالترتيب الفعلي للجملة إذًا:

تحديد الجدول (FROM)
الترشيح (`WHERE)
الاختيار (SELECT)
الترتيب (ORDER BY)
الحد (LIMIT)

عوامل جملة الشرط

وقد تدخل جملة الشرط عوامل:

كلمة NOT للنفي
كلمة IN للعضوية
كلمة BETWEEN للحصر

مثال نفي العضوية بكلمتي NOT و IN ثم وضع القيم المتعددة بين القوسين، على النحو التالي:

SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

مثال الحصر (بين تاريخين) بكلمتي BETWEEN ثم AND على النحو التالي:

SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-08-01';

ولاحظ أن التاريخ لابد أن يكون (السنة ثم الشهر ثم اليوم) بينهما شرطة (-) واحدة. كما أن هذا يفترض أن تعريف الصفة كان بنوع الوقت timestamp وليس النص أو العدد أو غيره.

دمج الجداول

يتم دمج الجداول لتكوين جدول واحد كأول خطوة قبل الترشيح على النحو التالي:

SELECT *
FROM toy
JOIN cat
  ON toy.cat_id = cat.id;

انظر المادة المكتوبة: https://learnsql.com/blog/sql-join-cheat-sheet/joins-cheat-sheet-a4.pdf
أو المادة المرئية: https://www.youtube.com/watch?v=Yh4CrPHVBdE&feature=youtu.be

الثاني: التلخيص

نتيجة التلخيص: كميات. وقد تُجمَع هذه الملخصات الكمية وتُفرَّقُ حسبتها على الصفات النوعيَّة للسجلات. ويكون الاستعلام تلخيصًا بوجود إحدى دوال التجميع (Aggregation Functions) وهي:

العد: COUNT
العليا: MAX
الدنيا: MIN
الجمع: SUM
المتوسط: AVG
الفريد: DISTINCT

مثال: احسب القيمة الدنيا والعليا والمتوسط لرواتب موظفي قسم البحث والتطوير.

SELECT MIN(salary), MAX(salary), AVG(salary)
FROM Employees
WHERE dep_id = 'R&D';

يُقرأ الاستعلام بالترتيب التالي:

من جدول الموظفين (FROM Employee)
رشح السجلات (WHERE dep_id = 'R&D')
احسب القيمة الدنيا والعليا والمتوسط لنفس العمود (salary)

لاحظ أن النتيجة عبارة عن ثلاثة أرقام وحسب، كل واحدٍ منها تلخِّص العمود طريقة حساب مختلفة تعتمد على مجموعة منه بحسب شرط التشريح.

تفريق التجميع على مجموعات

تستعمل الكلمة GROUP BY لتفريق الملخَّص بحسب قيمة نوعيَّة.

ففي هذا المثال نختار القِسم (dep_id) ليكون التلخيص لكل قسم بمفرده. فتكون النتيجة هي صفوفٌ عددها بعدد الأقسام التي ينتمي إليها الموظفون، ولكل متوسطه الخاص به:

SELECT dep_id, AVG(salary)
FROM Employees
GROUP BY dep_id;

الترشيح قبل أو بعد

توجد في SQL كلمتان للتشريح:

الأولى WHERE وهي ترشِّح قبل التجميع
الثانية HAVING وهي ترشِّح بعد التجميع

وهذا مثال استعلام عن متوسط نقاط الطلبة الناجحين (فوق 60 درجة) بحسب الدولة (يرشح من ذلك الدول التي يتجاوز متوسطها 90)

SELECT Country, AVG(score) AS s
FROM Students
WHERE score >= 60
GROUP BY Country
HAVING s > 90
ORDER BY s DESC;

ويتم قراءة هذا الاستعلام بهذا الترتيب:

من الطلبة (FROM Students)
رشح السجلات (WHERE score >= 60)
ضم السجلات بحسب الدولة (GROUP BY Country)
احسب المتوسط لكل دولة (SELECT Country, AVG(score) AS s)
رشح النتائج (HAVING s > 90)
رتب بحسب المتوسط تنازليًّا (ORDER BY s DESC)

جملة الاختيار المركَّبة

استعلام: أريد أسماء الأقسام التي لديها موظفين رواتبهم تتجاوز التسعة آلاف.

SELECT name FROM Departments
WHERE id IN (
    SELECT dep_id FROM Employees
    WHERE salary > 9000);

نقرأ الجملة التالية بهذا الترتيب:

نبدأ بالجملة المضمَّنة:
1. من جدوَل الموظفين (FROM Employees)
2. رشِّح (WHERE salary > 9000)
3. استخرج (SELECT dep_id)
ثم الجملة التي هي في سياقها:

من جدول الأقسام (FROM Departments)
رشِّح (WHERE id IN) نتيجة الجملة المضمَّنة التي كانت (SELECT dep_id)
استخرج (SELECT name)

ويُمكن الاستعانة بتعبير الجدول المشترك (Common Table Expression) المبتدئ بكلمة WITH، لبيان الترتيب الفعلي. وذلك على النحو التالي:

WITH HighSalaryEmployees AS (
    SELECT dep_id FROM Employees
    WHERE salary > 9000
)
SELECT name FROM Departments
WHERE id IN (
    SELECT dep_id FROM HighSalaryEmployees
);

الاختيار المركَّب المضمن لاستعلام أفقي

الاستعلام: أريد الموظفين الذين يتجاوز راتبهم المتوسط.

وهذا مثال خاطئ في لغة SQL لكتابة هذا الاستعلام:

SELECT * FROM Employees
WHERE salary > AVG(salary);

هذا خطأ لأن جملة التجميع (AVG(salary)) لا تأتي إلا في سياق (SELECT). ولذلك يجب وضعها في استعلام ضمني على النحو التالي:

SELECT * FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);

خلاصة ترتيب الأوامر

فبعد أن تعرفنا على الدمج والتوزيع والترشيح الثاني، والتضمين، يكون الترتيب الفعلي للتنفيذ هو:

تحديد الجدول بعد الدمج (FROM cat JOIN toy ON toy.cat_id = cat.id)
الترشيح الأول (`WHERE)
توزيع السجلات لمجموعات (GROUP BY country)
الاختيار (SELECT) بما في ذلك حساب دالة التجميع (AVG أو SUM ونحوهما) على كل مجموعة من السجلات بحسب التوزيع السابق
الترشيح الثاني (HAVING)
الترتيب (ORDER BY)
الحد (LIMIT)

والمضمَّن قبل السياق.

خلاصة

إنّ الانتقال من المخطّط المنطقي إلى ما تفهمه البرمجيّات يكون بلغة SQL، وهي لسان قواعد البيانات العلائقيّة منذ وُضعت في السبعينيات. تُعرِّف البنى بـ DDL، وتعالج السجلات بـ DML, وتضبط الصلاحيات بـ DCL, وتكفل سلامة المعاملات بـ TCL. وتقوم أوامرها على إعلان الغاية لا تفصيل الوسيلة؛ فيتولّى النظام اختيار الطريق وفق فهارسه وطرائق تخزينه. ثم تتجلّى قوتها في الاستعلام: اختياراً وتلخيصاً، وفي دمج الجداول، وفي توزيع النتائج ووضع الشرط قبل الجمع أو بعده.

السؤال التالي: كيف أوجه خطابي بلغة SQL لنظام قاعدة البيانات؟