تصميم موقع الويب الخاص بك مع PHP و MySQL


الدرس: وظائف SQL


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

هي أقل عددا من التي في PHP لكنها مكرسة خصيصا لقواعد البيانات وتثبت أنها قوية جدا في الممارسة العملية. لنأخذ مثالنا من الجدول jeux_video ، فهي تسمح لك باسترداد متوسط ​​سعر جميع الألعاب بسهولة ، وحساب عدد الألعاب التي يملكها كل شخص ، واستخراج أكثر الألعاب تكلفة أو الأرخص ، إلخ. وظائف ضرورية أيضا عند العمل مع التواريخ في SQL ، كما سنفعل في الفصل التالي.
يمكن تصنيف وظائف SQL إلى فئتين:

  • الوظائف العددية(scalar)  : أنها تعمل على كل المدخلات. على سبيل المثال ، يمكنك تغيير قيمة كل إدخال في حقل إلى أحرف كبيرة ؛
  • الوظائف التجميعية (aggregate)  : عند استخدام هذه الوظائف، يتم إجراء العمليات الحسابية على الجدول لارجاع قيمة واحدة  . على سبيل المثال ، يؤدي حساب متوسط ​​السعر إلى إرجاع قيمة: متوسط ​​السعر.

الوظائف العددية


سنكتشف أولاً كيفية استخدام دالة SQL من نوع عددي  : الوظيفة UPPER . عندما تتعلم كيفية استخدامها ، ستكون قادرًا على فعل الشيء نفسه مع جميع الوظائف العددية الأخرى. سأقدم لك بعد ذلك مجموعة صغيرة من الوظائف العددية لتعرفها ، مع العلم أن هناك وظائف أخرى ولكن لا يمكننا مراجعتها جميعًا لأنها ستكون طويلة جدًا.
استخدم دالة SQL عددية
على سبيل المثال ، سنركز عملنا على الجدول jeux_video الذي نعرفه جيدًا الآن. كتذكير ، إليك ما يبدو عليه:
ID nom possesseur console prix nbre_joueurs_max commentaires
1 Super Mario Bros Florent NES 4 1 Un jeu d'anthologie !
2 Sonic Patrick Megadrive 2 1Pour moi, le meilleur jeu au monde !
3 Zelda : ocarina of time Florent Nintendo 64 15 1Un jeu grand, beau et complet comme on en voit rarement de nos jours
4 Mario Kart 64 Florent Nintendo 64 25 4 Un excellent jeu de kart !
5 Super Smash Bros Melee Michel GameCube 55 4 Un jeu de baston délirant !
نكتب أسماء وظائف SQL بحروف كبيرة، كما هو الحال بالفعل لمثل معظم الكلمات الرئيسية SELECT ، INSERT الخ  إنه ليس التزامًا بل اتفاقية ، وهي العادة التي اتخذها المبرمجون.
لتوضيح كيفية استخدام وظائف SQL العددية ، سأستند إلى الوظيفة UPPER() التي تتيح لك تحويل حقل بأكمله إلى أحرف كبيرة. لنفترض أننا نريد الحصول على أسماء جميع الألعاب بأحرف كبيرة ؛ هكذا نكتب استعلام SQL :

SELECT UPPER(nom) FROM jeux_video
يتم استخدام وظيفة UPPER على الحقل nom . وبالتالي فإننا نستعيد جميع أسماء الألعاب بحروف كبيرة.
هل هذا يغير محتويات الجدول؟
لا! الجدول لا يزال هو نفسه . تغير الوظيفة  UPPER القيمة المرسلة إلى PHP فقط. لذلك نحن لا نلمس محتويات الجدول.
يؤدي هذا بالفعل إلى إنشاء "حقل افتراضي" موجود فقط لمدة الطلب. يُنصح بإعطاء اسم لهذا الحقل الافتراضي الذي يمثل أسماء الألعاب بحروف كبيرة. للقيام بذلك ، استخدم الكلمة الأساسية AS، مثل هذا:

SELECT UPPER(nom) AS nom_maj FROM jeux_video
نستعيد أسماء الألعاب بالأحرف الكبيرة عبر حقل افتراضي يسمى nom_maj .
هذا الحقل الافتراضي يسمى alias .
فيما يلي الجدول الذي سيعود به MySQL بعد الاستعلام السابق:
nom_maj
SUPER MARIO BROS
SONIC
ZELDA : OCARINA OF TIME
MARIO KART 64
SUPER SMASH BROS MELEE
يمكننا استخدامه في PHP لعرض أسماء الألعاب بحروف كبيرة:

<?php
$reponse = $bdd->query('SELECT UPPER(nom) AS nom_maj FROM jeux_video');

while ($donnees = $reponse->fetch())
{
    echo $donnees['nom_maj'] . '
'; } $reponse->closeCursor(); ?>
web dynamique php
عرض أسماء الألعاب بأحرف كبيرة
كما ترون ، يسترجع PHP فقط الحقل المحدد nom_maj ( حتى إذا لم يكن موجودًا في الجدول) . من خلال عرض محتوى هذا الحقل ، نحصل فقط على أسماء الألعاب بحروف كبيرة.
بالطبع ، يمكنك أيضًا استرداد محتوى الحقول الأخرى كما كان من قبل دون تطبيق وظيفة عليها بالضرورة:

SELECT UPPER(nom) AS nom_maj, possesseur, console, prix FROM jeux_video
سنقوم بعد ذلك باستعادة البيانات التالية :
SUPER MARIO BROS Florent NES 4 SONIC Patrick Megadrive 2 ZELDA : OCARINA OF TIME Florent Nintendo 64 15 MARIO KART 64 Florent Nintendo 64 25 SUPER SMASH BROS MELEE Michel GameCube 55
nom_maj possesseur console prix
SUPER MARIO BROSFlorentNES4
SONICPatrickMegadrive2
ZELDA : OCARINA OF TIME FlorentNintendo 6415
MARIO KART 64FlorentNintendo 6425
SUPER SMASH BROS MELEEMichelGameCube55
أنت تعرف الآن كيفية استخدام دالة SQL العددية. 
دعنا نذهب على بعض الوظائف من نفس النوع ، والتي تستخدم بالتالي بنفس الطريقة.
عرض بعض الوظائف العددية المفيدة
سأقدم لك مجموعة مختارة من الوظائف العددية التي قد يكون من المفيد معرفتها. هناك العديد من الوظائف الآخرين كما سنرى في نهاية هذه القائمة ، ولكن سيكون طويلًا جدًا ولن يكون مفيدًا لتقديمهم جميعًا هنا.
UPPER  : تحويل إلى أحرف كبيرة
تحول هذه الوظيفة نص الحقل إلى حروف كبيرة. اكتشفنا أن نقدم وظائف SQL :

SELECT UPPER(nom) AS nom_maj FROM jeux_video
وبالتالي ، سيتم إرجاع لعبة "Sonic" في النموذج "SONIC" في حقل مسمى nom_maj .
LOWER  : تحويل إلى أحرف صغيرة
هذه الوظيفة لها تأثير معاكس: سيتم كتابة المحتوى بالكامل في أحرف صغيرة.

SELECT LOWER(nom) AS nom_min FROM jeux_video
هذه المرة ، سيتم إرجاع لعبة
LENGTH  : حساب عدد الحروف
يمكنك الحصول على طول الحقل باستخدام الوظيفة LENGTH()  :

SELECT LENGTH(nom) AS longueur_nom FROM jeux_video
بالنسبة إلى "Sonic" ، سنحصل على القيمة 5 في الحقل longueur_nom .
ROUND  : تقريب العدد العشري
يتم  استخدام الوظيفة ROUND() في الحقول ذات القيم العشرية. لا يوجد أي شيء في الجدول jeux_video، ولكن إذا كان لدينا أسعار عشرية ، فيمكننا تقريب القيم بهذه الوظيفة.
تستحق هذه الوظيفة مُدخلين: اسم الحقل الذي يجب تقريبه وعدد الأرقام بعد العلامة العشرية التي تريد الحصول عليها. على سبيل المثال:

SELECT ROUND(prix, 2) AS prix_arrondi FROM jeux_video
وبالتالي ، إذا كانت تكلفة اللعبة 25.86999 دولار ، فسنحصل على القيمة 25.87 يورو في أحد الحقول prix_arrondi .
وأشياء أخرى !
هناك العديد من وظائف SQL الأخرى من نفس النوع ولكن لا يمكنني تقديمها لكم جميعًا. توفر وثائق MySQL قائمة أكثر اكتمالا بكثير من الوظائف الرياضية  (مثل ROUND ) و والوظائف على السلاسل  (مثل UPPER ) . إذا كنت ترغب في اكتشاف الآخرين ، فهذا هو المكان المناسب لك!

الوظائف التجميعية (aggregate) 


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

SELECT AVG(prix) AS prix_moyen FROM jeux_video
Average_Price
28.34
لعرض هذه المعلومات في PHP ، يمكننا أن نفعل كالمعتاد ( هذا يعمل):

<?php
$reponse = $bdd->query('SELECT AVG(prix) AS prix_moyen FROM jeux_video');

while ($donnees = $reponse->fetch())
{
    echo $donnees['prix_moyen'];
}

$reponse->closeCursor();
?>
ومع ذلك ، لماذا انزعجنا بعمل حلقة نظرًا لأننا نعرف أننا سنحصل على إدخال واحد فقط ، نظرًا لأننا نستخدم دالة تجميعية؟
يمكننا تحمل تكلفة الاتصال fetch() مرة واحدة وخارج الحلقة نظرًا لوجود إدخال واحد فقط. وبالتالي فإن الكود التالي مناسب أكثر في هذه الحالة:

<?php
$reponse = $bdd->query('SELECT AVG(prix) AS prix_moyen FROM jeux_video');

$donnees = $reponse->fetch();
echo $donnees['prix_moyen'];

$reponse->closeCursor();
?>
هذا الكود أبسط وأكثر منطقية. نقوم باسترداد الإدخال الأول والوحيد باستخدام fetch() وعرض ما يحتوي عليه ، ثم نغلق المؤشر. لا حاجة للقيام بذلك في حلقة لأنه لا يوجد إدخال الثاني.
لا تتردد في التصفية!
بالطبع ، يمكنك الاستفادة من القوة الكاملة لـ SQL للحصول ، على سبيل المثال ، على متوسط ​​سعر الألعاب التي يملكها باتريك. إليك كيف سنفعل ذلك:

SELECT AVG(prix) AS prix_moyen FROM jeux_video WHERE possesseur='Patrick'
سيتم إجراء المتوسط ​​فقط في قائمة الألعاب التي تنتمي إلى Patrick . يمكنك حتى الجمع بين الشروط للحصول على متوسط ​​سعر ألعاب باتريك التي يلعبها لاعب واحد . جربه!
لا تخلط دالة مجمعة مع الحقول الأخرى
انتبه إلى هذه النقطة لأنه ليس من السهل فهمها: ليس عليك استرداد الحقول الأخرى من الجدول عند استخدام دالة مجمعة ، على عكس الدوال القياسية السابقة. في الواقع ، ما معنى أن يكون:

SELECT AVG(prix) AS prix_moyen, nom FROM jeux_video
سوف نسترجع من جانب واحد متوسط ​​سعر جميع الألعاب ومن ناحية أخرى قائمة أسماء جميع الألعاب ... من المستحيل تمثيل ذلك في جدول واحد.
كما تعلمون ، تقوم SQL بإرجاع المعلومات في شكل صفيف. ومع ذلك ، لا يمكننا تمثيل متوسط ​​السعر (الذي يأخذ إدخال واحد) في نفس الوقت مثل قائمة الألعاب. إذا أردنا الحصول على هذين المُدخلين ، فسيتعين علينا تقديم طلبين.
عرض بعض الوظائف التجميعية المفيدة
AVG  : حساب المتوسط
هذه هي الوظيفة التي درسناها للتو لاكتشاف وظائف التجميع. تقوم بإرجاع متوسط ​​حقل يحتوي على أرقام:

SELECT AVG(prix) AS prix_moyen FROM jeux_video
SUM  : مجموع القيم
يتم SUMاستخدام الوظيفة لإضافة كل قيم الحقل. وبالتالي ، يمكننا معرفة القيمة الإجمالية للألعاب التي تخص باتريك:

SELECT SUM(prix) AS prix_total FROM jeux_video WHERE possesseur='Patrick'
MAX  : إرجاع القيمة القصوى
تقوم هذه الوظيفة بتحليل الحقل وإرجاع القيمة القصوى الموجودة. للحصول على أغلى سعر للعبة:

SELECT MAX(prix) AS prix_max FROM jeux_video
MIN  : إرجاع القيمة الدنيا
وبالمثل ، يمكننا الحصول على أرخص سعر للعبة:

SELECT MIN(prix) AS prix_min FROM jeux_video
COUNT  : حساب عدد الإدخالات
تسمح الوظيفة COUNT بحساب عدد الإدخالات. انها مثيرة جدا للاهتمام ولكن أكثر تعقيدا. يمكن استخدامه بالفعل بعدة طرق مختلفة.
الاستخدام الأكثر شيوعًا هو إعطائها  * كمُدخل:

SELECT COUNT(*) AS nbjeux FROM jeux_video
هذا يعطي إجمالي عدد الألعاب في الجدول.
يمكننا بالطبع تصفية مع جملة WHERE، للحصول على عدد الألعاب التي تنتمي إلى Florent على سبيل المثال:

SELECT COUNT(*) AS nbjeux FROM jeux_video WHERE possesseur='Florent'
من الممكن حساب الإدخالات التي لا يكون أحد الحقول فيها فارغًا ، بمعنى أنه غير صالح NULL . لا توجد لعبة من هذا القبيل في طاولتنا jeux_video، لكن لنفترض أنه بالنسبة لبعض الألعاب لا نعرف الحد الأقصى لعدد اللاعبين. سنترك بعض الإدخالات فارغة ، مما سيكون له تأثير في العرض NULL (بلا قيمة) في العمود nbre_joueurs_max (كما في الجدول التالي) .
ID nom possesseur console prix nbre_joueurs_max commentaires
1 Super Mario Bros Florent NES 4 1 Un jeu d'anthologie !
2 Sonic Patrick Megadrive 2 1Pour moi, le meilleur jeu au monde !
3 Zelda : ocarina of time Florent Nintendo 64 15 1Un jeu grand, beau et complet comme on en voit rarement de nos jours
4 Mario Kart 64 Florent Nintendo 64 25 4 Un excellent jeu de kart !
5 Super Smash Bros Melee Michel GameCube 55 4 Un jeu de baston délirant !
في هذه الحالة ، لا يمكننا حساب سوى عدد الألعاب التي لها أقصى عدد محدد من اللاعبين. يجب تحديد اسم الحقل المطلوب لتحليله كمُدخل:

SELECT COUNT(nbre_joueurs_max) AS nbjeux FROM jeux_video
في مثالنا ، سيتم احتساب ألعاب Zelda  و Mario Kart فقط لأننا نعرف الحد الأقصى لعدد اللاعبين. لذلك سوف نحصل على "2" في الرد.
أخيرًا ، من الممكن حساب عدد القيم المميزة في حقل معين. على سبيل المثال في العمود possesseur ، يظهر Florent عدة مرات ، و Patrick أيضًا ، إلخ. لكن كم من الناس مختلفين في الجدول؟ يمكننا معرفة ذلك باستخدام الكلمة الرئيسية DISTINCT أمام اسم الحقل للتحليل ، مثل هذا:

SELECT COUNT(DISTINCT possesseur) AS nbpossesseurs FROM jeux_video
يمكننا أن نرى بسهولة عدد الأشخاص المختلفين المدرجين في الجدول. حاول أن تفعل الشيء نفسه لمعرفة عدد لوحات المفاتيح المختلفة في الجدول!

GROUP BY و HAVING : تجميع البيانات


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

SELECT AVG(prix) AS prix_moyen, console FROM jeux_video
ليس من المنطقي الحصول على متوسط ​​سعر جميع الألعاب وحقل "Console" في نفس الوقت. لا يوجد سوى متوسط ​​سعر واحد لجميع الألعاب ، ولكن هناك وحدات تحكم (console) متعددة. لا يمكن لـ MySQL إرجاع صفيف يطابق هذه المعلومات.
GROUP BY  : تجميع البيانات
ومع ذلك ، قد يكون من المنطقي طرح متوسط ​​سعر الألعاب لكل وحدة تحكم  ! للقيام بذلك، يجب استخدام الكلمة الجديدة:  GROUP BY . وهذا يعني "تجميع حسب" . نحن نستخدم هذه الجملة مع وظيفة مجمعة ( مثل AVG ) للحصول على معلومات مثيرة للاهتمام حول مجموعات من البيانات.
فيما يلي مثال على استخدام GROUP BY  :

SELECT AVG(prix) AS prix_moyen, console FROM jeux_video GROUP BY console
web dynamique php
يجب أن تستخدم GROUP BY في نفس الوقت كدالة تجميعية ، وإلا فإنها غير مجدية. هنا ، نحصل على متوسط السعر(AVG(prix)) ووحدة التحكم(console) ، ونختار التجميع حسب وحدة التحكم. لذلك ، سوف نحصل على قائمة لوحات المفاتيح المختلفة للجدول ومتوسط سعر ألعاب كل منصة!
Average_Priceوحدة التحكم
12.67Dreamcast
5.00 Gameboy
47.50GameCube
هذه المرة القيم متسقة! لدينا قائمة لوحات المفاتيح ومتوسط ​​سعر الألعاب المرتبطة.
التمرين  : حاول الحصول بنفس القيمة الإجمالية للألعاب التي يمتلكها كل شخص.
HAVING : تصفية البيانات المجمعة
HAVING هو المكافئ قليلاً لـ WHERE، ولكنه يعمل على البيانات بمجرد تجميعها. لذلك فهي طريقة لتصفية البيانات في نهاية العمليات.
انظر الطلب التالي:

SELECT AVG(prix) AS prix_moyen, console FROM jeux_video GROUP BY console HAVING prix_moyen <= 10
web dynamique php
أن يكون متوسط ​​السعر <= 10
مع هذا الطلب ، نحصل فقط على قائمة لوحات المفاتيح ومتوسط ​​سعرها إذا كان متوسط ​​السعر لا يتجاوز 10دولار.
HAVING يجب أن تستخدم فقط على نتيجة وظيفة مجمعة. هذا هو السبب في أننا نستخدمه هنا prix_moyen وليس على console .
أنا لا أفهم الفرق بين WHERE و HAVING . كلا منهما يقوم بالتصفية ، أليس كذلك؟
نعم ولكن ليس في نفس الوقت.   WHERE يعمل أولاً ، قبل تجميع البيانات ، بينما HAVING يعمل أولاً ، بعد تجميع البيانات. يمكننا أيضًا الجمع بين الاثنين جيدًا ، انظر المثال التالي:

SELECT AVG(prix) AS prix_moyen, console FROM jeux_video WHERE possesseur='Patrick' GROUP BY console HAVING prix_moyen <= 10
web dynamique php
WHERE يُنفَّذ قبل HAVING
هنا ، نطلب استرداد متوسط ​​سعر وحدة التحكم لكل ألعاب Patrick ( WHERE) ، بشرط ألا يتجاوز متوسط ​​سعر ألعاب وحدة التحكم 10 دولار ( HAVING) .

في الخلاصة


  • MySQL يسمح لك بتنفيذ وظائف معينة في حد ذاتها ، دون الحاجة إلى الذهاب من خلال PHP . هذه الوظائف تقوم بتعديل البيانات التي تم إرجاعها.
  • هناك نوعان من الوظائف:
    • و ظائف العددية  : فهي تعمل على كل دخول استردادها. فهي تسمح ، على سبيل المثال ، بتحويل محتوى الحقل بالكامل إلى أحرف كبيرة أو تقريب كل قيمة ؛
    • و ظائف التجميعية  : أنها تجعل الحسابات على عدة مُدخلات وإرجاع قيمة واحدة. على سبيل المثال: حساب المتوسط ​​، مجموع القيم ، حساب عدد الإدخالات ، إلخ.
  • يمكنك إعطاء اسم آخر للحقول التي تم تعديلها بواسطة الوظائف عن طريق إنشاء أسماء مستعارة باستخدام الكلمة الأساسية AS .
  • عند استخدام دالة تجميعية ، من الممكن تجميع البيانات مع GROUP BY .
  • بعد تجميع البيانات ، يمكنك تصفية النتيجة مع HAVING . لا ينبغي الخلط بينها وبين WHERE التي تُنفّذ قبل تجميع البيانات.