6. تعميق المعرفة بلغة SQL

6.1. Introduction

في هذا الفصل، نقدم

صيغًا أخرى للأمر SELECT تجعله أمر استعلام قويًا للغاية، لا سيما لاستعلام عدة جداول في آن واحد.
صيغ موسعة للأوامر التي تمت دراستها سابقًا

لتوضيح الأوامر المختلفة، سنعمل مع الجداول التالية المستخدمة لإدارة الطلبات في نظام PME لتوزيع الكتب:

6.1.1. جدول CLIENTS

تخزن هذه الجدولة معلومات عن عملاء نظام PME:

`ID`	رقم يحدد هوية العميل بشكل فريد - المفتاح الأساسي
`NOM`	اسم العميل
`STATUT`	I=فرد، E=شركة، A=إدارة
`PRENOM`	الاسم الأول في حالة الفرد
`CONTACT`	اسم الشخص المسؤول عن الاتصال لدى العميل (في حالة شركة أو جهة إدارية)
`RUE`	عنوان العميل - الشارع
`VILLE`	المدينة
`CPOSTAL`	الرمز البريدي
`TELEPH`	رقم الهاتف
`DEPUIS`	منذ متى وأنت عميل لدينا؟
`DEBITEUR`	O (نعم) إذا كان العميل مدينًا للشركة، و N (لا) في حالة عدم وجود دين.

6.1.2. الجدول ARTICLES

يخزن معلومات عن المنتجات المباعة، وهي في هذه الحالة الكتب. وهيكلها كما يلي:

`ISBN`	رقم يحدد الكتاب بشكل فريد (ISBN = الرقم الدولي الموحد للكتاب) - المفتاح الأساسي
`TITRE`	عنوان الكتاب
`CODEDITEUR`	الرمز الذي يحدد الناشر بشكل فريد
`AUTEUR`	اسم المؤلف
`RESUME`	ملخص الكتاب
`QTEANCOUR`	الكمية المباعة خلال العام
`QTEANPREC`	الكمية المباعة في العام السابق
`DERNVENTE`	تاريخ آخر عملية بيع
`QTERECUE`	كمية آخر شحنة
`DERNLIV`	تاريخ آخر شحنة
`PRIXVENTE`	سعر البيع
`COUT`	تكلفة الشراء
`MINCDE`	الحد الأدنى للكمية المطلوبة
`MINSTOCK`	الحد الأدنى للمخزون
`QTESTOCK`	الكمية المتوفرة في المخزون

قد يكون محتواه كما يلي:

6.1.3. الجدول COMMANDES

تسجل هذه الجدولة المعلومات المتعلقة بالطلبات التي قدمها العملاء. وهي تتكون من العناصر التالية:

`NOCMD`	رقم يحدد الطلب بشكل فريد - المفتاح الأساسي
`IDCLI`	رقم العميل الذي قدم هذا الطلب - مفتاح خارجي - الرمز المرجعي CLIENTS(ID)
`DATE_CMD`	تاريخ إدخال هذا الطلب
`ANNULE`	O (نعم) إذا تم إلغاء الطلب و N (لا) في حالة عدم الإلغاء.

6.1.4. الجدول DETAILS

تحتوي على تفاصيل الطلب، أي أرقام الكتب المطلوبة وكمياتها. وهي تتكون من العناصر التالية:

`NOCMD`	رقم الطلب - مفتاح خارجي يشير إلى العمود NOCMD في الجدول COMMANDES
`ISBN`	رقم الكتاب المطلوب - مفتاح خارجي يشير إلى العمود ISBN في الجدول LIVRES
`QTE`	الكمية المطلوبة

قد يكون محتواه كما يلي:

فيما سبق، نرى أن الطلب رقم 3 (NOCMD) يتعلق بثلاثة كتب. وهذا يعني أن العميل قد طلب ثلاثة كتب في نفس الوقت. يمكن العثور على مراجع هذا العميل في الجدول [COMMANDES] حيث نرى أن الطلب رقم 3 تم تنفيذه من قبل العميل رقم 5. وتُظهر لنا الجدول [CLIENTS] أن العميل رقم 5 هو شركة NetLogos في سيغري.

6.2. الطلب SELECT

نهدف هنا إلى تعميق معرفتنا بالطلب SELECT من خلال عرض صيغ جديدة له.

6.2.1. صيغة الاستعلام متعدد الجداول

syntaxe

SELECT العمود1، العمود2، ...

FROM الجدول1، الجدول2، ...، الجدولp

WHERE condition

ORDER BY ...

action

الجديد هنا هو أن الأعمدة colonne1، colonne2، ... تأتي من عدة جداول table1، table2، ... إذا كان هناك جدولان يحتويان على أعمدة تحمل نفس الاسم، يتم إزالة الغموض باستخدام الترميز tablei.colonnej. يمكن أن يشير الرمز condition إلى أعمدة من جداول مختلفة.

طريقة العمل

`1`	يتم إنشاء الجدول الناتج الديكارتي لـ table1 و table2 و... و tablep. إذا كان ni هو عدد الصفوف في tablei، فإن الجدول الذي تم إنشاؤه يحتوي بالتالي على n1n2...np* صفًا تضم جميع أعمدة الجداول المختلفة.
`2`	يتم تطبيق condition من WHERE على هذه الجدولة. وبذلك يتم إنتاج جدول جديد
`3`	ويتم ترتيب هذه الجدولة وفقًا للطريقة المحددة في ORDER.
`4`	يتم عرض الأعمدة المطلوبة وفقًا لـ SELECT.

أمثلة

نستخدم الجداول المعروضة سابقًا. نريد معرفة تفاصيل الطلبات التي تم إجراؤها بعد 25 سبتمبر:

SQL>select details.nocmd,isbn,qte from commandes,details
  where commandes.datecmd>'25-sep-91'
  and details.nocmd=commandes.nocmd

يُلاحظ أنه بعد FROM، نضع أسماء جميع الجداول التي نشير إلى أعمدةها. في المثال السابق، تنتمي الأعمدة المحددة جميعها إلى الجدول DETAILS. ومع ذلك، تشير الشرط إلى الجدول COMMANDES. ومن هنا تأتي ضرورة تسمية هذه الأخيرة بعد FROM. غالبًا ما يُطلق على العملية التي تختبر تساوي أعمدة جدولين مختلفين اسم «الربط المتساوي» (equi-join).

كان من الممكن أيضًا كتابة الاستعلام SELECT بالطريقة التالية:

SQL> select details.nocmd,isbn,qte from commandes
    inner join details on details.nocmd=commandes.nocmd
    where commandes.datecmd>'25-sep-91'

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

SQL>select commandes.nocmd, articles.titre, details.qte
  from commandes,articles,details
  where commandes.datecmd>'25-sep-91'
  and details.nocmd=commandes.nocmd
  and details.isbn=articles.isbn

يتم الحصول على نفس النتيجة باستخدام الاستعلام التالي SQL، وهو أقل قابلية للقراءة:

SQL> select details.nocmd,articles.titre,details.qte from details
    inner join commandes on details.nocmd=commandes.nocmd
    inner join articles on  details.isbn=articles.isbn
    where commandes.datecmd>'25-sep-91'

فيما يلي، يتم إجراء ربطين داخليين مع الجدول [DETAILS]:

أحدهما مع الجدول [COMMANDES] للوصول إلى تاريخ طلب شراء كتاب
الأول مع الجدول [ARTICLES] للوصول إلى عنوان الكتاب المطلوب

كما نريد اسم العميل الذي قام بالطلب:

SQL>select commandes.nocmd, articles.titre, qte ,clients.nom
  from commandes,details,articles,clients
  where commandes.datecmd>'25-sep-91'
  and details.nocmd=commandes.nocmd
  and details.isbn=articles.isbn
  and commandes.idcli=clients.id

كما نريد تواريخ الطلبات وعرضها بترتيب تنازلي حسب هذه التواريخ:

SQL>select commandes.nocmd, commandes.datecmd, articles.titre, qte ,clients.nom
    from commandes,details,articles,clients
    where commandes.datecmd>'25-sep-91'
    and details.nocmd=commandes.nocmd
    and details.isbn=articles.isbn
    and commandes.idcli=clients.id
    order by commandes.datecmd descending

فيما يلي بعض القواعد التي يجب مراعاتها في عمليات الربط:

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

6.2.2. الربط الذاتي

نريد معرفة الكتب التي يزيد سعر بيعها عن سعر كتاب «Using SQL»:

SQL>select a.titre from articles a, articles b
  where b.titre='Using SQL'
  and a.prixvente>b.prixvente

الجدولان المستخدمان في عملية الربط متطابقان هنا: الجدول articles. ولتمييزهما، يتم إعطاؤهما اسم مستعار: from articles a، articles b. يُسمى الاسم المستعار للجدول الأول a، أما الاسم المستعار للجدول الثاني فهو b. يمكن استخدام هذه الصيغة حتى لو كانت الجداول مختلفة. عند استخدام اسم مستعار، يجب استخدامه في كل مكان في الأمر SELECT بدلاً من الجدول الذي يشير إليه.

6.2.3. الربط الخارجي

نريد معرفة العملاء الذين اشتروا شيئًا ما في سبتمبر مع تاريخ الطلب. يتم عرض العملاء الآخرين بدون هذا التاريخ:

SQL>select clients.nom,commandes.datecmd from clients
    left outer join commandes  on clients.id=commandes.idcli
    where datecmd between '01-sep-91' and '30-sep-91'

من المثير للدهشة هنا عدم الحصول على النتيجة الصحيحة. من المفترض أن نجد جميع العملاء موجودين في الجدول [CLIENTS]، وهذا ليس هو الحال. وعندما نفكر في كيفية عمل الارتباط الخارجي، ندرك أن العملاء الذين لم يشتروا قد تم ربطهم بصف فارغ في الجدول COMMANDES، وبالتالي بتاريخ فارغ (القيمة NULL في مصطلحات SQL). وبالتالي، فإن هذا التاريخ لا يستوفي الشرط المحدد للتاريخ، ولا يتم عرض العميل المقابل. دعونا نجرب شيئًا آخر:

SQL>select clients.nom,commandes.datecmd from clients
    left outer join commandes  on clients.id=commandes.idcli
    where (commandes.datecmd between '01-sep-91' and '30-sep-91')
        or (commandes.datecmd is null)

نحصل هذه المرة على الإجابة الصحيحة لسؤالنا.

6.2.4. الاستعلامات المتداخلة

syntaxe

SELECT العمود[s] FROM الجدول[s]

WHERE تعبير عامل الاستعلام

ORDER BY ...

fonctionnement

requête هو أمر SELECT الذي يُنتج مجموعة من 0 أو 1 أو عدة قيم. وبذلك نحصل على شرط WHERE من النوع

التعبير - العامل (القيمة1، القيمة2، ...، القيمةi)

يجب أن يكون كل من expression و vali من نفس النوع. إذا كانت الاستعلامة تُرجع قيمة واحدة، فإننا نعود إلى شرط من النوع

التعبير - عامل - القيمة

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

IN

expression IN (val1, val2, ..., vali): صحيحة إذا كانت قيمة expression هي أحد عناصر قائمة vali.

NOT IN

معكوس IN

ANY

يجب أن يسبقه =، !=، >، >=، <، <=

expression >= ANY (val1, val2, .., valn): صحيح إذا كان expression >= لأحد قيم vali في القائمة

ALL

يجب أن يسبقه =،!=،>،>=،<،<=

expression >= ALL (val1, val2, .., valn): صحيح إذا كان التعبير >= لجميع القيم vali في القائمة

EXISTS

الاستعلام: صحيح إذا كان requête يُرجع سطرًا واحدًا على الأقل.

أمثلة

نستعرض السؤال الذي تم حله بالفعل باستخدام ربط متساوي: عرض العناوين التي يزيد سعر بيعها عن سعر كتاب «Using SQL».

SQL>select titre from ARTICLES
    where prixvente > (select prixvente from ARTICLES where titre='Using SQL')

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

نريد معرفة العناوين التي يزيد سعر بيعها عن متوسط سعر البيع:

SQL> select titre from ARTICLES
    where prixvente > (select avg(prixvente) from ARTICLES)

من هم العملاء الذين طلبوا الأوراق المالية الناتجة عن الاستعلام السابق؟

SQL>select distinct idcli from COMMANDES,DETAILS
    where DETAILS.isbn in
    (select isbn from ARTICLES where prixvente
        > (select avg(prixvente) from ARTICLES))
    and COMMANDES.nocmd=DETAILS.nocmd

توضيحات

يتم اختيار الرموز ISBN من الجدول DETAILS، وهي الرموز الموجودة ضمن الكتب التي يزيد سعرها عن متوسط سعر الكتب.
في الصفوف المحددة في الخطوة السابقة، لا يوجد رمز العميل IDCLI. وهو موجود في الجدول COMMANDES. يتم الربط بين الجدولين من خلال رقم الطلب NOCMD، ومن هنا تأتي معادلة الربط COMMANDES.nocmd=DETAILS.nocmd.
قد يكون العميل نفسه قد اشترى أحد الكتب المعنية عدة مرات، وفي هذه الحالة سيظهر رمزه IDCLI عدة مرات. لتجنب ذلك، نضع الكلمة الرئيسية DISTINCT بعد SELECT. وبشكل عام، فإن DISTINCT يزيل التكرارات في الصفوف الناتجة عن SELECT.
للحصول على اسم العميل، يتعين علينا إجراء ربط متساوي إضافي بين الجدولين COMMANDES و CLIENTS كما هو موضح في الاستعلام التالي.

SQL> select distinct CLIENTS.nom from COMMANDES,DETAILS,CLIENTS
    where DETAILS.isbn in
    (select isbn from ARTICLES where prixvente
        > (select avg(prixvente) from ARTICLES))
    and COMMANDES.nocmd=DETAILS.nocmd
    and COMMANDES.IDCLI=CLIENTS.ID

البحث عن العملاء الذين لم يقدموا أي طلبات منذ 24 سبتمبر:

SQL>select nom from CLIENTS
    where clients.id not in
    (select distinct commandes.idcli from commandes where datecmd>='24-sep-91')

لقد رأينا أنه يمكن تصفية السطور بطريقة أخرى غير استخدام الجملة WHERE: وذلك باستخدام الجملة HAVING بالاقتران مع الجملة GROUP BY. تقوم الجملة HAVING بتصفية مجموعات من الأسطر.

وكما هو الحال مع الجملة WHERE، فإن صيغة


     HAVING expression opérateur requête

ممكنة، مع الشرط المذكور سابقًا بأن expression يجب أن يكون أحد التعبيرات expri في الجملة


     GROUP BY expr1, expr2, ...

أمثلة

ما هي الكميات المباعة للكتب التي تزيد عن 200F؟

لنعرض أولاً الكميات المباعة حسب العنوان:

SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
    where DETAILS.isbn=ARTICLES.isbn
    group by titre

الآن، لنقم بتصفية الأسهم:

SQL> select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
    where DETAILS.isbn=ARTICLES.isbn
    group by titre
    having titre in (select titre from ARTICLES where prixvente>200)

وبطريقة ربما تكون أكثر وضوحًا، كان من الممكن كتابة ما يلي:

SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
    where DETAILS.isbn=ARTICLES.isbn
    and ARTICLES.prixvente>200
    group by titre

6.2.5. الاستعلامات المترابطة

في حالة الاستعلامات المتداخلة، يكون هناك استعلام أم (الاستعلام الأبعد) واستعلام ابن (الاستعلام الأقرب). لا يتم تقييم الاستعلام الأم إلا بعد الانتهاء من تقييم الاستعلام ابن بالكامل.

تتبع الاستعلامات المرتبطة نفس الصيغة، مع الاختلاف التفصيلي التالي: يقوم الاستعلام الفرعي بإجراء ربط مع جدول الاستعلام الرئيسي. وفي هذه الحالة، يتم تقييم مجموعة الاستعلام الرئيسي والاستعلام الفرعي بشكل متكرر لكل صف في الجدول الرئيسي.

مثال

نستأنف المثال الذي نرغب فيه في الحصول على أسماء العملاء الذين لم يقدموا أي طلبات منذ 24 سبتمبر:

SQL> 
select nom from clients
    where not exists
        (select idcli from commandes
            where datecmd>='24-sep-91'
                and commandes.idcli=clients.id)

يتم تنفيذ الاستعلام الأصلي على الجدول clients. أما الاستعلام الفرعي فيقوم بربط الجدولين clients و commandes. وبالتالي، لدينا استعلام مترابط. بالنسبة لكل سطر في الجدول clients، يتم تنفيذ الاستعلام الفرعي: حيث يبحث عن الرمز id الخاص بالعميل في الطلبات التي تمت بعد 24 سبتمبر. وإذا لم يعثر عليه (not exists)، يتم عرض اسم العميل. ثم ننتقل إلى السطر التالي من الجدول clients.

6.2.6. معايير الاختيار لكتابة SELECT

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

الربط

SQL>
select distinct nom from clients,commandes
    where clients.id=commandes.idcli

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

SQL> 
select nom from clients
    where id in (select idcli from commandes)

تعطي نفس النتيجة.

الاستعلامات المترابطة

SQL>
select nom from clients
    where exists (select * from commandes where commandes.idcli=clients.id)

تعطي نفس النتيجة.

يقترح المؤلفان كريستيان MAREE وجاي LEDANT، في كتابهما «SQL، مقدمة وبرمجة وإتقان»، بعض معايير الاختيار:

الأداء

لا يعرف المستخدم كيف «يتمكن» SGBD من العثور على النتائج التي يطلبها. لذلك، لن يكتشف إلا من خلال التجربة أن أسلوب كتابة معين أكثر كفاءة من آخر. يؤكد MAREE و LEDANT من واقع خبرتهما أن الاستعلامات المترابطة تبدو عمومًا أبطأ من الاستعلامات المتداخلة أو عمليات الربط.

الصياغة

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

يجب تسمية الجداول التي تحتوي على أعمدة المعلمات في SELECT (SELECT col1، col2، ...) بعد الكلمة الرئيسية FROM. عندئذ يتم إجراء الضرب الديكارتي لهذه الجداول، وهو ما يُعرف بالربط.

عندما تعرض الاستعلام نتائج من جدول واحد، ويستلزم تصفية الصفوف في هذا الجدول الرجوع إلى جدول آخر، يمكن استخدام الاستعلامات المتداخلة.

6.3. امتدادات الصيغة

لأغراض التسهيل، قمنا في أغلب الأحيان بعرض صيغ مختصرة للأوامر المختلفة. في هذا القسم، نقدم صيغها الموسعة. وهي واضحة بذاتها لأنها مشابهة لتلك الخاصة بالأمر SELECT الذي تمت دراسته باستفاضة.

INSERT

`syntaxe1`	INSERT INTO table (col1, col2, ..) VALUES (val1, val2, ...)
`syntaxe2`	INSERT INTO table (col1, col2, ..) (requête)
`explication`	تم عرض هاتين الصيغتين

DELETE

`syntaxe1`	DELETE FROM table WHERE condition
`explication`	هذه الصيغة معروفة. ونضيف أن الشرط يمكن أن يحتوي على استعلام بالصيغة WHERE تعبير عامل (استعلام)

UPDATE

`syntaxe1`	UPDATE table SET col1=expr1, col2=expr2, ... WHERE condition
`explication`	سبق عرض هذه الصيغة. ونضيف أن الشرط يمكن أن يحتوي على استعلام بالصيغة WHERE تعبير عامل (استعلام)

`syntaxe2`	UPDATE table SET (col1, col2, ..) = الاستعلام 1، (cola, colb, ..) = الاستعلام 2، ... WHERE condition
`explication`	يمكن أن تأتي القيم المعينة للأعمدة المختلفة من استعلام.