توابع SQL الدرجية
1-التوابع الرقمية:
----------
التوابع الرقمية وهي التوابع الدرجية الخاصة بالعمليات على القيم الرقمية ومن أهمها التوابع التالية:
• التابع Floor: يُقرِّب مُعامل الدخل إلى أقرب عدد صحيح أصغر من مٌعامل الدخل. ?x?
• التابع Ceiling: يُقرِّب مُعامل الدخل إلى أقرب عدد صحيح أكبر من مُعامل الدخل. ?x?
• التابع Round: يُقرِّب مُعامل الدخل ذو الفاصلة العشرية إلى أقرب عدد صحيح أو عدد حقيقي بدقة محددة.
• التابع Abs: يعيد القيمة المطلقة لمُعامل الدخل. | x |
• التابع Sqrt: يُعيد قيمة الجذر التربيعي لمُعامل الدخل.
• التابع Rand: يُعيد رقم عشوائي بين 0 و 1 و يستخدم مُعامل الدخل كأساس لتوليد الرقم العشوائي.
• التوابع Sin, Cos, Tan, Atan: توابع تحسب قيم ظل، تظل، جب، تجب الزاوية التي نأخذها كمُعامل دخل.
1-التوابع الرقمية (أمثلة):
التابع FLOOR:
نستخدم في هذا المثال تابع التدوير FLOOR لتدوير علامات الطلاب إلى الرقم الصحيح بإزالة الجزء ذو الفاصلة العشرية.
SELECT FLOOR(studentMark) FROM marks;
فإذا كان لدينا طالب علامته: 66.5، أو 66.2، أو 66.7، ستظهر علامته ضمن نتيجة هذا الاستعلام: 66
التابع CEILING:
إذا كان المطلوب تدوير علامات الطلاب إلى الرقم الصحيح الأعلى، نستخدم التابع CEILING كما في الصيغة التالية:
SELECT CEILING(studentMark) FROM marks;
فإذا كان لدينا طالب علامته: 66.5، أو 66.2، أو 66.7، ستظهر علامته ضمن نتيجة هذا الاستعلام: 67
التابع ROUND:
إذا كان المطلوب تدوير عدد ما إلى رقم بعدد خانات عشرية محدد نستطيع استخدام التابع ROUND كما في الصيغة التالية:
SELECT ROUND(studentMark, 1) FROM marks;
تظهر قيم الخرج كقيم ذات خانة عشرية واحدة. فإذا كانت علامة طالب 66 ستظهر القيمة ضمن نتيجة الاستعلام 66.0 وإذا كانت 66.55 ستصبح 66.5
التابع ABS:
إذا أردنا حساب القيمة المطلقة لحقل height يشير إلى ارتفاع نقاط جغرافية عن سطح البحر من الجدول geoTable، وذلك لتحديد الارتفاع الأكبر بينها، يمكننا استخدام الاستعلام:
SELECT MAX(ABS(height)) FROM geoTable;
يحسب التابع ABS القيمة المطلقة لكل ارتفاع، ويستخرج التابع التجميعيmax الإرتفاع الأعلى.
التوابع SIN, COS, TAN, ATAN
إذا أردنا حساب قيمة، جب أو تجب أو ظل مجموعة من الزوايا الواردة في الحقل Angle من الجدول Angles يكفي كتابة الاستعلام:
SELECT SIN(angle), COS(angle), TAN(angle) FROM Angles;
التابع RAND:
يُستخدَم التابع RAND في توليد رقم عشوائي بين 0 و 1، فإذا كان المطلوب توليد رقم عشوائي مستخدمين كأساس للتوليد الأرقام الواردة في حقل seed من الجدول Numbers أمكننا كتابة الاستعلام:
SELECT RAND(seed) FROM numbers;
التابع SQRT:
يحسب التابع SQRT الجذر التربيعي لقيمة معينة، إذ تعطي نتيجة الاستعلام التالي الرقم 3:
SELECT SQRT(9);
2-توابع سلاسل المحارف:
----------------
توابع سلاسل المحارف هي التوابع الدرجية الخاصة بالعمليات على السلاسل المحرفية، ومن أهمها التوابع التالية:
LEFT() يُعيد جزء من السلسلة يبتدئ من بدايتها حتى عدد محدد من المحارف
RIGHT() يُعيد جزء من السلسلة يبتدئ من نهايتها حتى عدد محدد من المحارف
SUBSTR() يُعيد جزء من السلسلة يبتدئ من موقع محدد فيها وبطول عدد محدد من المحارف
LENGTH() يُعيد طول السلسلة المحرفية
CONCAT() يُستخدم لدمج أكثر من سلسلة محرفية
LOWER() / UPPER() يحوِّل جميع محارف السلسلة إلى أحرف كبيرة أو صغيرة
TRIM() يلغي الفراغات من بداية ونهاية السلسلة المحرفية
INSTR() يُستخدم لتحديد موقع سلسلة جزئية ضمن سلسلة رئيسية
2-توابع سلاسل المحارف (أمثلة):
التابعان LEFT و RIGHT:
يُعيد التابعان LEFT و RIGHT جزء من سلسلة المحارف مؤلف من عدد محدد من المحارف ابتداءً من يسار السلسلة في حالة التابع LEFT، ومن يمين السلسلة في حالة التابع RIGHT. فإذا أردنا كتابة الاستعلام الذي يعيد أول 50 محرفاً من قيم الحقل Title في الجدول News يمكننا استخدام الصيغة التالية:
SELECT LEFT(title, 50) FROM News;
التابع SUBSTR:
يُعيد التابع SUBSTR جزء من سلسلة محارف، ابتداءً من موقع محدد في تلك السلسلة، وبطول عدد محدد من المحارف. فإذا أردنا كتابة الاستعلام الذي يعيد سلسلة محارف بطول 5 محارف اعتباراً من قيم الحقل Title مبتدئاً من المحرف رقم 10، نكتب الصيغة التالية:
SELECT SUBSTR(title, 10, 5) FROM News;
التابع LENGTH:
يحسب التابع LENGTH طول سلسلة محارف معينة. فإذا أردنا كتابة الاستعلام الذي يعيد أطوال سلاسل المحارف الخاصة بالحقل Title نستخدم الصيغة:
SELECT LENGTH(title) FROM News;
التابع CONCAT:
يدمج التابع CONCAT أكثر من سلسلة محرفية. فلإعادة قائمة بسلاسل محرفية تمثل حاصل دمج السلاسل المحرفية الخاصة بالحقل Title، مع السلاسل المحرفية الخاصة بالحقل Details في الجدول News، نكتب الصيغة:
SELECT CONCAT(title, details) FROM News;
التابعان Upper و Lower:
يحوِّل التابعان Upper و Lower جميع محارف السلسلة إلى أحرف كبيرة أو صغيرة (فقط للمحارف A-Z). فإذا أردنا تحويل السلاسل المحرفية في الحقل Title إلى سلاسل مؤلفة من أحرف كبيرة، نستخدم الاستعلام:
SELECT UPPER(title) FROM News;
التابع Trim:
يزيل التابع Trim الفراغات من بداية ونهاية السلسلة المحرفية. فإذا أردنا الحصول على السلاسل المحرفية في الحقل Title بعد التخلص من الفراغات، في بداية ونهاية سلاسل المحارف تلك، نستخدم الصيغة:
SELECT TRIM(title) FROM News;
التابع Instr:
يحدد التابع Instr موقع أول ظهور لسلسلة جزئية في سلسلة رئيسية. يعيد التابع القيمة 0 في حال عدم إيجاد السلسلة. فإذا كان المطلوب مثلاً كتابة استعلام يعيد موقع أول ظهور للسلسلة الجزئية 'Test' ضمن سلاسل الحقل Title من جدول News، نستخدم الصيغة:
SELECT INSTR(title, 'Test') FROM News;
3-توابع التاريخ والوقت:
---------------
توابع التاريخ والتوقيت هي التوابع الخاصة بالعمليات على التاريخ والوقت ومن أهمها التوابع التالية:
DATEDIFF() يُعيد الفرق بين تاريخين
GETDATE() يُعيد السنة، والتاريخ، واليوم، والساعة، والدقيقة، والثانية، وأجزاء الثانية
CURRENT_DATE() يعيد التاريخ الحالي الخاص بنظام إدارة قاعدة البيانات
CURRENT_TIME() يُعيد التوقيت الخاص بنظام إدارة قاعدة البيانات
CURRENT_TIMESTAMP() يُعيد التاريخ والتوقيت الخاصين بنظام إدارة قاعدة البيانات
3-توابع التاريخ والوقت (أمثلة):
التابع GETDATE:
الذي يُعيد التاريخ الحالي متضمناً السنة، والشهر، واليوم، والساعة، والدقيقة، والثانية وجزء الثانية. فلإظهار التاريخ الحالي يمكننا استخدام الصيغة:
GETDATE();
حيث تظهر النتيجة كما يلي: 04:50:32.28 06-12-2003
التابع DATEDIFF:
الذي يُعيد الفرق بين تاريخين. فإذا أردنا إظهار عدد الأيام الفاصلة بين التاريخ الحالي والتواريخ من قيم الحقل RegistrationDate في الجدول RegistrationInfo نستخدم الصيغة:
SELECT DATEDIFF(dd, RegistrationDate, GETDATE())
FROM RegistrationInfo;
التابع CURRENT_DATE
يُعيد هذا التابع قيمة التاريخ الخاص بنظام إدارة قاعدة البيانات (يُعتبر أحد متحولات نظام إدارة قواعد البيانات وهو ليس تابع فعلي). فإذا أردنا إظهار التاريخ الحالي نستخدم الصيغة:
SELECT CURRENT_DATE AS myDate;
التابع CURRENT_TIME
يُعيد هذا التابع قيمة التوقيت الحالي الخاص بنظام إدارة قاعدة البيانات (يُعتبر أحد متحولات نظام إدارة قواعد البيانات وهو ليس تابع فعلي). فإذا أردنا إظهار التوقيت الحالي نستخدم الصيغة:
SELECT CURRENT_TIME AS myTime;
التابع CURRENT_TIMESTAMP
يُعيد هذا التابع قيمة التاريخ والتوقيت الخاص بنظام إدارة قاعدة البيانات (يُعتبر أحد متحولات نظام إدارة قواعد البيانات وهو ليس تابع فعلي)، فالقيمة التي يعيدها التابع CURRENT_TIMESTAMP مشابهة تماماً للقيمة التي يعيدها التابع GETDATE.
فإذا أردنا إظهار عدد الأيام الفاصلة بين التاريخ الحالي والتواريخ المُخزنة في الحقل RegistrationDate من الجدول RegistrationInfo نستخدم الصيغة:
SELECT DATEDIFF(dd, RegistrationDate, CURRENT_TIMESTAMP)
FROM RegistrationInfo;
4-توابع التحويل:
توابع التحويل و هي التوابع الخاصة بالتحويل من نوع بيانات إلى نوع آخر ومن أهمها التوابع التالية:
STR() يُحول قيمة الدخل العددية إلى سلسلة محرفية.
TO_NUMBER() يُحول سلسلة المحارف المارة كمُعامل دخل إلى عدد.
CAST() يُحول قيمة الدخل إلى قيمة من أي نمط آخر من البيانات
CONVERT() يُحول قيمة الدخل إلى قيمة من أي نمط آخر من البيانات
4-توابع التحويل (أمثلة):
من أهم توابع التحويل التالية التابع STR الذي يُحول قيمة الدخل العددية إلى سلسلة محرفية، والتابع To_Number الذي يُحول سلسلة المحارف، المارة كمُعامل دخل، إلى عدد. يأخذ التابع الأخير معاملين، أحدهما السلسلة المحرفية المراد تحويلها إلى رقم، والآخر هو تنسيق يساعد التعليمة على فهم وتحويل السلسلة المحرفية.
التابع STR:
الذي يُحول قيمة الدخل العددية إلى سلسلة محرفية. يكون لهذا التابع الصيغة التالية:
STR(FLOAT, LENGTH, Precision);
فإذا أردنا مثلاً تحويل العدد 53.45 إلى سلسلة محارف نقوم باستخدام الصيغة:
STR(53.45, 5, 2);
حيث يمثل العدد 5 الطول الكامل للسلسلة المحرفية المُعادة، ويمثل العدد 2، عدد المراتب العشرية.
التابع TO_NUMBER:
الذي يُحول سلسلة المحارف المارة كمُعامل دخل إلى عدد. يأخذ التابع معاملين، أحدهما السلسلة المحرفية المراد تحويلها إلى رقم، والآخر هو تنسيق يساعد التعليمة على فهم وتحويل السلسلة المحرفية. فإذا أردنا تحويل السلسلة المحرفية '$3,15.2' إلى رقم نستخدم الصيغة:
TO_NUMBER('$3,15.2', '$9,99.9');
حيث أعطينا المعامل الخاص بتوضيح صيغة السلسلة المحرفية المراد تحويلها، القيمة '$9,99.9' لمساعدة التابع على المقارنة مع هذا المعامل، وتحديد الجزء الذي سوف يتم تحويله.
التابع CAST:
الذي يُحول قيمة الدخل إلى قيمة من أي نمط آخر من البيانات. يستخدم التابع CAST الصيغة:
CAST(Expression AS Data_Type)
التابع CONVERT:
يُحول قيمة الدخل إلى قيمة من أي نمط آخر من البيانات. يستخدم التابع CONVERT الصيغة:
CONVERT(Expression, Data_Type)
تمرين 1:
إذا كان لدينا جدول Goods يحتوي لائحة بالبضائع التي نريد نقلها(Good Description)، ووزن كل منها(GoodWeight)، وإذا علمنا أن عربة النقل تستطيع شحن 1.5 طن. حدد عدد رحلات النقل اللازمة لنقل كل مادة من المواد.
الحل:
SELECT goodDescription, CEILING(goodWeight / 1.5) AS Trips FROM Goods;
تمرين 2:
لدينا جدول Infoيحتوي الأرقام التسلسلية (CardNumber) للبطاقات المشاركة بسحب على جائزة نقدية، بالإضافة إلى أماكن بيع جميع البطاقات (center).
المطلوب اختيار بطاقة عشوائية، من أرقام البطاقات المتوفرة، علماً أن أرقام البطاقات صحيحة، متسلسلة ومحصورة بين الرقم 1 و 80000.
الحل:
يمكننا استخدام التابع RAND لتوليد رقم عشوائي. ولكن التابع RAND يولد رقم بين 0 و 1، لذلك علينا ضرب القيمة التي يولدها بـ 80000، وتدوير الناتج إلى أقرب عدد صحيح:
SELECT cardNumber
FROM Info
WHERE cardNumber = ROUND(RAND() * 80000, 0);
تمرين 3:
ليكن لدينا الجدول userInfo الذي يحتوي أسماء وعناوين البريد الالكتروني (userEmail)، لمجموعة من الأشخاص، المطلوب إعادة قائمة بأسماء نطاقات عناوين البريد الالكتروني
الحل:
سنعتمد هنا على استخدام التابع Instr لتحديد موقع ظهور المحرف '@' ضمن عناوين البريد الالكتروني، ثم نقتطع الجزء من عنوان البريد الالكتروني الذي يبدأ عند المحرف '@' وينتهي بنهاية سلسلة محارف. نستخدم لهذا الغرض التابع Substr.
SELECT SUBSTR(userEmail, INSTR('@', userEmail) + 1)
FROM userInfo;
