مذكرات حول تصمبم قواعد البيانات وتطبيقاتها

الأخ sandm: معذرة كل المعذرة على التأخر في الرد، ولكن حسبك أن إجابتك صحيحة جداً، وأحمد الله أن جعلني سبباً في خدمة عباده...

الأستاذ الفاضل إكسير: أسأل الله أن يتقبل دعاءك وأن يجزيك مثله. والله لقد سررت بهذه المداخلة أيما سرور، وبنفس اللسان الذي أشكرك به جداً على مشاركتك، أؤكد به على كلام أخي محمد ندا:

اقتباس

نشكر لك المعلومات القيمة ونتمنى ألا تكون ماراً بين السطور على عجل .. ولكن نتمنى أن تكون معنا دائماً فى هذه السلسلة

لا غنى بنا عن مساهمات الأفاضل، ولا أحب أن تتجمد الفائدة لانشغالي عن المساهمة المنتظمة... بارك الله لكم ونفعنا بكم، آمين.

السلام عليكــم ورحمـة الله وبركاتــه ،،

الأخوة الأفاضل

مرحباً بكم من جديد ..

فى محاولة متواضعة للتذكير بحلقات دورة الــSQL وتبسيط مطالعتها ... قمت بجمع الحلقات الماضية ووضعها فى ملف مرفق.

يجب عمل التالى:

فك الضغط عن المجلد المرفق .. سينتج لديك مجلد إسمه SQLCourse

ضعه كما هو على الــD فى جهازك .. وقم بفتح الصفحة الأولى Index .. تجد فيها عناوين للحلقات وموضوعاتها .. إفتحها مبشارة وبذلك يمكنك مطالعة الحلقات ومراجعتها حتى لو لم تكن متصلاً بالإنترنت ....

أرجو أن أكون ساهمت فى التيسير ولو بقدر بسيط.

تحياتى

محمد ندا

SQLCourse.rar

وعليكــم السـلام ورحمة الله وبركاتـه..

الصراحة، لا أدري هل أندهش من هذا العمل العجيب، أم لا أفعل، لأننا من المفروض أن نكون قد تعودنا منك على هذه العجائب...

بالفعل أخي محمد قد عجبت وسررت لهذا الجهد الجميل، ومن ناحيتي سأحاول استكمال الحلقات رويداً رويداً، ثم أسهم معك إن شاء الله في المجموع ببعض الترتيب للعناوين...

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

أسأل الله أن يجعل عملنا خالصاً لوجهه، وأن يثيبك على عملك خير الثواب...

أخوك أحمد

رقم الحلقة (36)

المزيد عن التجميع

مرة أخرى، ملخص سريع لعملية الاستعلام من جدول باستخدام SQL، حسب ما تم عرضه إلى الآن:

- اختيار حقل أو أكثر من الجدول في الأمر SELECT. تستطيع الاستعلام عن كل الحقول بكتابة النجمة فقط.

- إذا لم تكن ترغب في استحضار كل صفوف هذه الحقول، استخدم المقطع WHERE لتحديد بعض الشروط.

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

- للتفاصيل عن أنواع هذه المقارنات، راجع من فضلك الحلقات السابقة.

- تستطيع أيضاً تقييد عدد الصفوف المستعادة باختيار عدد محدد من الأعلى باستخدام الكلمة TOP.

- تستطيع أيضاً التخلص من الصفوف المتطابقة باستخدام الكلمة DISTINCT.

- يمكنك ترتيب الصفوف المستعادة حسب أي حقل (أو حسب أكثر من حقل، واحداً بعد الآخر) باستخدام المقطع ORDER BY.

- من الطبيعي أنك ستحتاج إلى طريقة أخرى لاستخراج البيانات في شكل خلاصات وليس مجرد سرد كامل أو جزئي لصفوف الجدول. في هذه الحالة، أنت تبحث عن حسابات تستطيع تطبيقها على البيانات داخل الجدول. تتيح لك SQL القيام بهذه الحسابات عبر دوال تجميعية تستطيع إعادة مجموع أو عدد أو القيمة الصغرى أو الكبرى أو المتوسط الحسابي لقيم حقل (لاحظ مرة أخرى أننا نتعامل مع حقول). طبعاً بإمكانك تطبيق هذه الدوال على أكثر من حقل، لكن ليست هذه هي المشكلة. المشكلة أنك لا تريد تطبيق الحسابات على كل صفوف الجدول دفعة واحدة، وإنما على مجموعات داخل الجدول يجمعها رابط مشترك. مثلاً، تريد مجموع رواتب الموظفين بعد فرزهم في مجموعات، كل مجموعة تمثل قسماً في الشركة. الرابط المشترك هنا هو قيمة حقل القسم، لأن كلهم لهم نفس القيمة في هذا الحقل. نقول هنا إن (التجميع) تم حسب حقل القسم. أيضاً، نريد عدد الزبائن، لكن بعد تجميعهم حسب حقل المدينة، وهكذا. عملية التجميع ممكنة عبر المقطع GROUP BY.

- هناك بعض القيود في استخدام هذا المقطع؛ في قائمة الأمر SELECT، كل الحقول التي لم تدخل في حسابات تجميعية (لم نطبق عليها دوال تجميعية)، يجب ذكرها في قائمة المقطع GROUP BY.

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

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

HAVING

لنفترض أنك تريد عدد الموظفين في كل قسم، لكن تود استبعاد الأقسام التي يقل عدد موظفيها عن خمسة موظفين. هل تستطيع استخدام WHERE لتنفيذ المطلوب؟

من أجل الإجابة عن هذا السؤال، نرجع إلى فكرة استخدام WHERE. هذا المقطع يستخدم لمقارنة قيم الحقول مع قيمة مطلقة أو مجموعة من القيم. هل هناك حقل في الجدول يحوي عدد الموظفين؟ إذاً، كيف ستتم المقارنة؟ ولهذا لا يمكن استخدام هذه الطريقة من أجل هذا المطلوب. أنت تقول: ولكن هذا الحقل سيكون موجوداً بعد تنفيذ الاستعلام، لأن الدالة COUNT ستعيد عدد الموظفين، ويمكن تجميعهم حسب القسم. كيف نستطيع الاستفادة من هذه الحقيقة في تصفية الخلاصات المستعادة؟ هنا يأتي دور المقطع HAVING. هذا المقطع يطبق شرطاً أو أكثر بطريقة مشابهة لما يفعله المقطع WHERE، ولكن على نتيجة دوال التجميع وليس على الحقول قبل التجميع.

نستخدم هذا المقطع لتنفيذ المثال السابق كالتالي:

SELECT Count(EmpNO) AS CountOfEmpNO, EmpDept
FROM tblEmployee GROUP BY EmpDept
HAVING Count(EmpNO) >= 5

حاول استخدام العبارة التالية، وانتبه لرسالة الخطأ:

SELECT Count(EmpNO) AS CountOfEmpNO, EmpDept
FROM tblEmployee GROUP BY EmpDept
HAVING Year(EmpHireDate) = 1981

ماذا تقول الرسالة؟ جرب استخدام هذاه العبارة:

SELECT Count(EmpNO) AS CountOfEmpNO, EmpDept FROM tblEmployee GROUP BY EmpDept HAVING EmpDept =30

وماذا عن هذه العبارة؟

SELECT Count(EmpNO) AS CountOfEmpNO, EmpDept FROM tblEmployee GROUP BY EmpDept HAVING EmpNo =7844

ما الذي تستنتجه؟

وهكذا لا تستطيع استخدام هذا المقطع إلا على الحقول المشاركة في التجميع أو نتيجته. الأمر مختلف كما علمنا سابقاً مع WHERE (كيف؟)

مثال

لنفترض أن المطلوب هو القسم الذي يحوي أكبر عدد من الموظفين مع عدد الموظفين فيه. لا تستطيع استخدام الدالة MAX مباشرة هنا في حدود ما تعلمناه، لأنه ليس هناك حقل يحوي عدد الموظفين في الجدول. لكن دعنا نرى ما يمكن عمله...

العبارة الأولى أعلاه تعيد عدد الموظفين في كل قسم. الصفوف مرتبة حسب حقل التجميع وهو القسم. هذه هي نتيجة العبارة الأولى:

إذا فكرنا باستخدام TOP من أجل استعادة أول صف، فإنه يجب أن يكون الصف الأول صاحب أكبرعدد للموظفين، وهذا يعني أن نرتب أولاً الصفوف حسب عدد الموظفين، تنازلياً، هكذا:

SELECT  Count(EmpNO) AS CountOfEmpNO, EmpDept
FROM tblEmployee GROUP BY EmpDept
ORDER BY Count(EmpNO) DESC

النتيجة هي:

لم يبق إلا إضافة TOP 1 لإتمام المطلوب:

SELECT  TOP 1 Count(EmpNO) AS CountOfEmpNO, EmpDept
FROM tblEmployee GROUP BY EmpDept
ORDER BY Count(EmpNO) DESC

الناتج هو سطر وحيد يمثل القسم 30 وعدد موظفيه 6.

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

(يتبع إن شاء الله)...

السلام عليكــم ورحمـة الله وبركاتــه ،،،

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

الشكر موصول لأستاذنا الكريم / محمد ندا لجهوده في متابعة وإحياء هذا الموضع بالنقاش .

تقبلوا تحياتي

أخى dbprog مرحباً بك فى هذه السلسلة .. وأرجو أن تتابع التواصل معنا هنا تفيد وإن شاء الله تستفيد.

أخى الحبيب أحمد مبارك الحيقى

أولاً: مرحباً بك وبالحلقة الجديدة.

ثانياً:

اقتباس

الصراحة، لا أدري هل أندهش من هذا العمل العجيب، أم لا أفعل،

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

والآن بعد قراءة الحلقة سنضيفها تباعاً للملف المجمع .. كمشاركة متواضعة فى تجميع هذا المجهود الرائع.

تحياتى

محمد ندا

السلام عليكم ورحمة الله وبركاته

حاب اشكر استاذي احمد مبارك على المجهود الكبير الذي يبذله في ايصال المعلومة لكل محتاج لها الله يبارك لك في عملك ورزقك ويوسع عليك

في الدينا والاخرة

كما اوجة الشكر لكل من شارك في هذه السلسلة الرائعة من خبرائنا واعضاء المنتدى الافاضل

واخص بالشكر استاذي محمد ندى مدير السلسلة الله يبارك فيه وبكل صراحة انا اعجب بشكل كبير بالجهد الذي بذله في اظهار الحلقات

في ملفات خاصة بها وكانت طريقة احترافية يعطيك الف عافية ولو فيها زحمة اتمنى عليك انه لا يقتصر على دورس السيكوال بل كل دورس

السلسلة من البداية الله يبارك فيك وجزاك الله الف خير وعافية

واعتذر لاخواني عن عدم متابعتي للدروس التي طرحت بسبب انشغالي بالعمل واكمال الدراسة تدرون السنة الدراسية بدأت وان شاء الله اكمل

الدراسة ادعوا لي بالنجاح واسمحوا لي لم استطيع ولم اجد الوقت لقراءة الدورس الاخيرة الله يعين واحصل وقت لقرائتها

وشكرا مرة اخرى لكل من ساهم في انجاح هذه السلسلة والحمدالله انها للحين متواصلة وان شاء الله اتكون مقدما لدروس اخرى يقدمها استذتنا

في وتكون كاملة وشاملة بشكل منهجي

الله يعطيكم العافية

اخوكم محمد المسيفري

مرحبا بك استاذ احمد

ونشكرك على عودتك لإكمال الدروس بعد تأخر لم نعتاد عليه :)

واتمنى لك التوفيق وإلى الأمام

وسدد الله خطاك على طريق الصواب

أخى الحبيب محمد المسيفرى

بارك الله فيك .. ووفقك فى دراستك وجعل النجاح حليفك.

ونتمنى ألا تغيب علينا كثيراً .. وتطل علينا ما سمح به وقتك

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

تحياتى

محمد ندا

الحمد لله الذي سخر لنا ناس تقوم على خدمة العلم لمن يبحثون عنه

ونسال الله ان يوفقكم لما تحبون وان يكتبها لكم في موازين حسناتكم

ونطلب المزيد

وحابه اشكر كل من شارك في هذه السلسلة الرائعة من خبرائنا واعضاء المنتدى الافاضل

اختكم في الله روان

بارك الله فيك يا أستاذ أحمد

دمت موفقاً ومأجوراً من قبل العلي القدير على هذا العطاء العلمي

ابو منتظر

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

رقم الحلقة (37)

فكرة الاستعلام من أكثر من جدول

مقدمة

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

العلاقات

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

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

الثانية، كيف يتم تمثيل هذه العلاقات في قواعد البيانات العلائقية بين مجموعة من الجداول؟ كل ما لدينا في قواعد البيانات العلائقية من مخازن للبيانات هو الجداول. والجداول هي مجموعة من الأعمدة (الحقول) والصفوف (السجلات). في الأنواع الأخرى من قواعد البيانات، كانوا يحتاجون إلى وسائل خارجية لتمثيل العلاقات بين الوحدات المختلفة، مثلاً المؤشرات، لكن قواعد البيانات العلائقية تمتاز بأن هذه العلاقات يتم تمثيلها ضمن البيانات نفسها في الجداول. كيف ذلك؟ العلاقة بين جدولين أو أكثر يتم تمثيلها باستخدام حقل (عمود) مشترك بين الجداول. لاحظ أن هذا يعني تكراراً لقيم هذا الحقل المشترك في أكثر من مكان، لكن هذا ثمن لا بد من دفعه من أجل الاحتفاظ بالعلاقات، ومن ثم تكامل قاعدة البيانات (أي نوع من التكامل؟).

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

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

نضع أي حقل في أي جدول؟ إذا وضعنا حقل رقم الفاتورة في جدول العملاء، فإنه مع كل فاتورة تحرر لعميل يجب إضافة صف في جدول العملاء فيه بيانات هذا العميل بالضبط ما عدا رقم الفاتورة الجديدة سيختلف. من الواضح أن هذا خيار غير ممكن، لأن رقم العميل سيتكرر وهذا غير ممكن (رقم العميل مفتاح أساسي). إذاً، فالحل هو وضع حقل رقم العميل في جدول الفواتير، وهذا لن يضير جدول الفواتير لأن لكل فاتورة عميل واحد أصلاً، فلن نضطر لتكرار رقم الفاتورة مع أي عميل آخر، ولا مع نفس العميل (في المرة القادمة سنحرر فاتورة جديدة برقم جديد لهذا العميل). وللذين ما يزالون يذكرون أنواع العلاقات من الحلقات الماضية، فإن هذه علاقة من نوع واحد إلى متعدد، وكقاعدة، ضع دوماً الحقل المشترك بين الجدولين في جهة المتعدد (جهة جدول الفواتير هنا، لأن لكل فاتورة عميل واحد فقط، لكن لكل عميل عدد من الفواتير).

لاحظ من فضلك أن هذا الحقل المشترك الذي افقنا على أنه مفتاح أساسي في أحد الجدولين، يسمى المفتاح الأجنبي في الجدول الآخر، ويمكن أن تتكرر قيمه هنا، إذ فقد بغربته ميزاته السابقة. فيمكن أن تجد رقم العميل مع أكثر من فاتورة في أكثر من صف من جدول الفواتير. كما أنه لا يتوجب علي التنبيه مرة أخرى على أن المفتاح الأساسي قد يتكون من أكثر من حقل، وعلى هذا قد نجد أكثر من حقل مشترك بين الجدولين المرتبطين بعلاقة.

الآن نحن نملك العلاقة، وهي المطلب الأساسي من أجل إمكانية الاستعلام من أكثر من جدول أساساً. فهل هذا كل شيء؟ للأسف، كالعادة، الحياة أكثر تعقيداً من ذلك. هنالك ما ينبغي أن تعلمه أولاً عن إشكاليات قد تواجهها عند الربط بين الجدولين.

طرق الربط بين الجداول

(يتبع إن شاء الله)...

أخى الحبيب أحمد ..

افتقدناك كثيراً والله ليس فى السلسلة فقط بل فى المنتدى .. فقد طالت الغيبة هذه المرة .. ولكنا ما زلنا وسنظل معك ما كان فى العمر بقية وفى السلسلة تكملة.

وأكرر عن نفسى أننى أقدر مشاغلك فكلنا لدينا مثلك وأحيانا لا نجد الوقت لنفعل أى شئ خارج الحلقة اليومية للعمل.

تحياتى

محمد ندا

______________________.rar

أخى الكريم Rahmasoft

أولاً: مرحباً بك فى هذه السلسلة مشاركاً ومفيداً ومستفيداً إن شاء الله.

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

وأرجو أن تكون مشاركاً دائماً فى السلسلة التى يبذل فيها محررها وصاحبها الأستاذ/أحمد مبارك الحيقى مجهودا كبيراً .. وذلك ليزداد ازدهارها وتقدمها بمشاركتك مع الأخوة المهتمين بالسلسلة.

أشكرك مرة أخرى ومرحباً بك.

تحياتى

محمد ندا

اثابك الله على الدرس وما يحتويه على معلومات مهمه ومفيده

سر إلى الأمام ونحن بعون الله ننتظر دروسك بكل شغف

والله يفرجها عليك وعلينا ويعينك ويثبتك على طريق الصواب

ودمت بخير وصحة وسلام

والله يا أخي rahmahsoft1 مجهود جبار تشكر عليه، وإن شاء الله أسهم معكم بما تيسر من الملفات عندي.... أشكرك جزيلاً، تقسيم جميل...

رقم الحلقة (38)

طرق الربط بين جدولين

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

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

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

شكل الجدول الناتج لا يبدو مشجعاً، ويحوي الكثير من التكرار الذي لا يقدم أي معلومات مفيدة. في الحقيقة، ما حصلنا عليه للتو يسمى في الجبر العلائقي (حاصل الضرب الديكارتي لعلاقتين). تذكر من فضلك أنه في النموذج الرياضي الذي بنى عليه Codd نموذج قواعد البيانات العلائقية، يسمى الجدول (علاقة). هذا الضرب يجمع بين كل زوج في العلاقتين في عدد من الاحتمالات يساوي حاصل ضرب عدد عناصر العلاقة الأولى في عدد عناصر العلاقة الثانية (4 * 5 = 20 في حالتنا هذه). كل قيمة في الحقل الأول ارتبطت بكل القيم من الحقل الثاني. هذا النوع من الربط غير ذي جدوى في قواعد البيانات، وينتج عند عدم وجود (أو عدم استخدام) العلاقة بين جدولين. يمكنك الحصول على هذه النتيجة بواسطة SQL بأن تذكر حقول واسمي الجدولين فحسب في عبارة SELECT، كما يلي (هذه العبارة لا يقبلها الأكسس لسبب أذكره حالاً بإذن الله):

SELECT n, n FROM t1, t2

الفرض أن t1 هو اسم الجدول الأول، وt2 هو اسم الجدول الثاني. كما أن n هو اسم الحقل الرقمي في الجدولين. لكن المشكلة في هذه العبارة أن اسمي الحقلين متطابقان، وهذا يضع الأكسس في حيرة: أي الحقلين هو المقصود بـ n الأولى، وأيهما هو المقصود بـ n الثانية؟ لا تقل لي أن الأمر أوضح من أن يحتمل الحيرة، وأنه يمكن للأكسس أن يتصرف، ويفترض أن كل حقل من أحد الجدولين. هذا بالنسبة لي ولك، وللنملة التي تمشي الآن في أحد الأركان (ربما) يكون صحيحاً. لكن بالنسبة لجهاز أفضل تعريف له هو أنه أسرع غبي في العالم، فإن الحل الأنجع هو أن يقذف في وجهك رسالة تخبرك بكل أدب أن (اسم الحقل يحتمل أن يشير إلى أكثر من جدول)!

الحل لهذا الإشكال أن تميز كل حقل باسم الجدول الذي ينتمي إليه، وذلك بكتابة اسم الجدول بعده نقطة ثم اسم الحقل. اسم الجدول هنا يسمى qualifier، وتعني شيئاً معرفاً. تصبح العبارة كالتالي:

SELECT t1.n, t2.n FROM t1, t2

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

SELECT t1.n, t2.n FROM t1, t2 WHERE t1.n = t2.n

ناتج هذه العبارة هو التالي:

هل أعجبك هذا الجواب؟ بغض النظر عن ذلك، ما قمت به هو عملية ربط JOIN بين الجدولين بطريقة خاصة، يسميها الأكسس عملية الربط الداخلي INNER JOIN Operation، ويخصص لها صيغة محددة في تحديد العلاقة، فتصبح العبارة السابقة كالتالي:

SELECT t1.n, t2.n FROM t1 INNER JOIN t2 ON t1.n = t2.n

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

إذا كانت قيم أحد الجداول لها مكانة خاصة في نفسك، وتريد استعراضها على كل حال، بغض النظر عن وجود ما يطابقها في الجدول الثاني، فبإمكانك ان تعمد إلى الخيار الثالث، وهو ما يسمى بالربط الخارجي OUTER JOIN. الربط الخارجي هو طريقة خاصة في الربط بين جدولين، بحيث يتم استعادة كل صفوف جدول، ثم البحث عن صفوف الجدول الثاني التي تتطابق فيها قيم الحقل المشترك مع الجدول الأول. قد يحدث أن تبقى بعض صفوف الجدول الأول بدون ما يقابلها من الجدول الثاني، وفي هذه الحالة تبقى الحقول الممثلة للجدول الثاني خالية. في حالة لم يكن هذا الكلام مفهوماً، سيصبح كذلك بإذن الله بعد أن نرى تطبيقه على جدولينا الصغيرين، لكن قبل ذلك، ينبغي أن أذكر أن الربط الخارجي نوعان، لأننا ذكرنا أنه تتم استعادة البيانات من جدول واحد بشكل أساسي، ثم ما يقابله من الجدول الثاني، وعلى هذا نتوقع أن تختلف النتيجة باختلاف الجدول الذي نختاره كأساس في الربط. لأننا نفترض الربط بين جدولين، فإنه قد تم اختيار موقع الجدولين للتمييز بينهما. إذا تم اختيار الجدول الأيمن، فإن الربط الأيمن RIGHT JOIN هو الذي يستخدم، وإلا فإن الربط الأيسر LEFT JOIN هو الذي يستخدم. لاحظ أن كلمة الخارجي قد تم حذفها اختصاراً فقط.

كتطبيق للربط الخارجي بالنسبة لجدولي الأرقام، قارن بين العبارتين التاليتين:

SELECT t1.n, t2.n FROM t1 RIGHT JOIN t2 ON t1.n = t2.n

ناتجها هو:

SELECT t1.n, t2.n FROM t1 LEFT JOIN t2 ON t1.n = t2.n

والناتج هو:

لاحظ من فضلك هذه النقاط المهمة:

 إذا تم ربط جدولين بمفتاح أساسي في أحدهما، فينبغي لهذا المفتاح (الأجنبي في الجدول الآخر) ألا يحتوي قيماً لا توجد في المفتاح الأساسي، على خلاف المثال المركب هاهنا.

 العبارتان التاليتان متكافئتان:

SELECT t1.n, t2.n FROM t1 RIGHT JOIN t2 ON t1.n = t2.n

SELECT t1.n, t2.n FROM t2 LEFT JOIN t1 ON t1.n = t2.n

 لو تأملنا الجداول الناتجة في حالة الربط الخارجي، لوجدنا أن بنيتها تمهد لنا الحصول على القيم الموجودة في أحد الجدولين، ولا توجد في الآخر، وهو الفرق بين الجدولين (مطلوب شائع في تطبيقات قواعد البيانات). مثلاً، في المثال الأول، نستطيع معرفة الفرق بين t2 وt1، أي القيم الموجودة في الحقل المشترك في t2 ولا توجد في t1 باشتراط أن تساوي حقول t1 القيمة الخالية في العبارة المستخدمة بالمثال، كالتالي:

SELECT t2.n FROM t1 RIGHT JOIN t2 ON t1.n = t2.n WHERE t1.n IS NULL

تمرين: استخرج القيمتين 0 و6 الموجودتين في حقل t1 وليستا في حقل t2، باستخدام استعلام مناسب.

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

(يتبع إن شاء الله)...

السلام عليكم

جزاكم الله خيرا يا استاذ أحمد على مابذلته من مجهود و وقت

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

أرجو من الله أن يتقبل منا هذا العمل خالصا لوجهه الكريم و أن ينفع به المسلمين

أرجو من حضرتك إرسال ملفات الوررد حيث سأستعين بالله و أكمل ما بدأته و لكن أرجو أن تعطيني رأيك في ما تم إنجازه و أرحو أن ترسله لي عبر البريد

rahmasoft1@yahoo.com

أخوكم أبو رحمة

السلام عليكم ورحمة الله وبركاته

شكرا استاذي وخبيرنا احمد على الدروس الشيقة

واسمحوا لي قلة مشاركتي في الحلقات وذلك لانشغالي بالدارسة والعمل في نفس الوقت

الله المستعان

الحلقات جد مفيدة الله يبارك فيك وجزيك الخير

وعليكــم السـلام ورحمة الله وبركاتـه..

الأخ العزيز أبا رحمة، أسأل الله أن يتقبل دعاءك، وأن يصلح لنا نياتنا وأعمالنا، وأن يتقبل منا، وينفعنا بها في الدنيا والآخرة... آمين.

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

أمهلني بضع أيام، حتى أمر على المشاركات، وأحذف ما لا أراه مناسباً (على غرار ما فعلت حضرتك)، ثم نتواصل بإذن الله... جزاك الله خيراً كثيراً.

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

رقم الحلقة (39)

أبدأ هذه الحلقة بالاقتباس من الحلقة السابعة عشر، فقرة تلمس نقطة أحب التأكيد عليها ثانية هاهنا:

اقتباس

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

بحمد الله قد أتينا فيما سبق على ما وعدت به بشأن الحديث عن التكامل المرجعي، وعن طرق الربط، لكن ما أود التنبيه عليه هنا هو ضرورة التمييز بين العلاقة واستخدامها. العلاقة تنشأ باستخدام الحقول المشتركة: بشكل عام المفتاح الأساسي في جدول وصورته (المفتاح الأجنبي) في الجدول الآخر يعرفان العلاقة بين الجدولين. عند استعادة بيانات فعلياً من الجدولين، لا بد لك من استخدام هذه العلاقة في عبارة SQL في واحدة من طرق الربط التي تحدثنا عنها للتو في الحلقة السابقة: {INNER | LEFT | RIGHT} JOIN. الرمز (|) يعني (أو)، حيث أنك تستخدم واحدة فقط من هذه العمليات في المرة الواحدة. كما أشرت في الفقرة أعلاه، تعريف العلاقة يسمح للأكسس بحفظ التكامل المرجعي، فلا يسمح بوجود قيم في المفتاح الأجنبي لم توجد بعد في المفتاح الأساسي مثلاً، وكذلك يسمح للأكسس باقتراح عمليات ربط مناسبة عند استخدام مصمم الاستعلامات. أما وجود العلاقة من الأساس فهو الذي يتيح الربط بين الجدولين من أجل استعادة البيانات منهما. من المفروض أن العلاقة موجودة طبيعياً، ولكن يتم تمثيلها بالمفاتيح كما سبق.

الاستعلام من أكثر من جدول: أمثلة

بالرجوع إلى مثالنا العتيد لجدول الموظفين، أعيد عرضه هاهنا لسهولة المتابعة:

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

العلاقة بين الجدولين تتمثل في حقل dptNo في جدول الأقسام، وهو المفتاح الأساسي في هذا الجدول، وصورته (EmpDept) في جدول الموظفين، وهو مفتاح أجنبي هنا. هذا المثال يوضح أيضاً نقطة قد يغفل عنها البعض: ليس شرطاً أن تتطابق أسماء المفتاحين في الجدولين، لكن المهم هو أن تكون قيمهما من نفس المجال (راجع الحلقتين 15 و22). نوع العلاقة هنا واحد إلى متعدد من جدول الأقسام إلى جدول الموظفين، وهذا يعني أن كل قسم قد يضم أكثر من موظف، لكن الموظف الواحد لا يعمل في أكثر من قسم.

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

SELECT tblEmployee.EmpNo, tblEmployee.EmpName, tblEmployee.EmpHireDate, tblEmployee.EmpSal, tblDepartment.dptName
FROM tblDepartment INNER JOIN tblEmployee ON tblDepartment.dptNo = tblEmployee.EmpDept

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

مثال آخر أقل وضوحاً هو التالي: المطلوب استعراض بيانات كل الأقسام مع أعداد الموظفين في كل قسم. تحليل المطلوب يقودنا إلى النقاط التالية:

 بيانات الأقسام المطلوبة، تشمل الرقم والاسم، لا بد من استخراجها من جدول الأقسام.

 أعداد الموظفين لا نستطيع استخراجها إلا من جدول الموظفين.

 المطلوب هو عدد الموظفين في كل قسم، وهذا يعني تجميع الموظفين حسب الأقسام. نعم، نحتاج هنا إلى GROUP BY.

قد تبدأ بعبارة كالتالي:

SELECT tblDepartment.dptNo, tblDepartment.dptName, Count (tblEmployee.EmpNO) AS CountOfEmpNO
FROM tblDepartment INNER JOIN tblEmployee ON tblDepartment.dptNo = tblEmployee.EmpDept
GROUP BY tblDepartment.dptNo, tblDepartment.dptName

وناتجها هو:

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

SELECT tblDepartment.dptNo, tblDepartment.dptName, Count (tblEmployee.EmpNO) AS CountOfEmpNO
FROM tblDepartment LEFT JOIN tblEmployee ON tblDepartment.dptNo = tblEmployee.EmpDept
GROUP BY tblDepartment.dptNo, tblDepartment.dptName

SELECT tblDepartment.dptNo, tblDepartment.dptName, Count (tblEmployee.EmpNO) AS CountOfEmpNO
FROM tblEmployee RIGHT JOIN tblDepartment ON tblDepartment.dptNo = tblEmployee.EmpDept
GROUP BY tblDepartment.dptNo, tblDepartment.dptName

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

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

SELECT tblDepartment.* 
FROM tblDepartment LEFT JOIN tblEmployee ON tblDepartment.dptNo = tblEmployee.EmpDept
WHERE tblEmployee.EmpNO Is Null

الناتج هو طبعاً:

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

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

/index.ph...st&p=457382

(يتبع إن شاء الله)...

رقم الحلقة (40)

الاستعلامات الفرعية SUBQUERIES

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

فكرة الاستعلام الفرعي

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

من أبسط الأمثلة لتوضيح هذه الفكرة، مثال استخراج الموظف صاحب أكبر راتب في الشركة. نفذنا هذا المطلوب من قبل أو نحوه باستخدام TOP مع الترتيب التنازلي. بالإمكان المقارنة المباشرة مع أكبر راتب في مقطع WHERE إذا كنا نعلم هذا الراتب. يمكن معرفة الراتب باستعلام يستخدم الدالة MAX. لدينا هاهنا استعلامان، استعلام يستخرج الراتب الكبر، وآخر يستخرج الموظف صاحب هذا الراتب الأكبر بمقارنة راتبه مع هذا الراتب. المطلوب هو تنفيذ الاستعلامين في عبارة واحدة، والاستفادة من نتيجة أحدهما في تنفيذ الآخر. في مثل هذه الحالات، تأتي الاستعلامات الفرعية لتقول بكل ثقة: لو سمحتم، أفسحوا الطريق!

استخدام الاستعلامات الفرعية ضمن قائمة حقول SELECT

أحياناً، تكون إمكانية استخدام استعلام كامل ضمن الحقول المستعادة مفيدة جداً. في هذا الاستعلام تستطيع جلب بيانات من جدول آخر. في بعض الحالات يكون أداء نفس المهمة بواسطة الربط التقليدي بين الجداول (JOIN) واضحاً، لكن في حالات أخرى يكون من الصعب تصور القيام بنفس المهمة التي يمكن ان يؤديها الاستعلام الفرعي.

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

SELECT e1.EmpNO, e1.EmpName, e1.EmpHireDate, e1.EmpManager, (SELECT e2.EmpHireDate FROM tblEmployee AS e2 WHERE e2.EmpNo = e1.EmpManager) AS [ManagerHiredate]
FROM tblEmployee AS e1

السر في العبارة السابقة هو التمييز بين جدول الموظفين داخل الاستعلام الفرعي وفي الاستعلام الأساسي باستخدام أسماء مستعارة (e1 للاستعلام الأساسي وe2 للاستعلام الفرعي)، بحيث تم ربط جدول الموظفين في الاستعلام الفرعي بجدول الموظفين في الاستعلام الأساسي بمساواة رقم الموظف في الاستعلام الفرعي برقم مدير الموظف في الاستعلام الأساسي. لاحظ هنا أن الاستعلام الفرعي يتم تنفيذه لكل صف. قمت باستجلاب أرقام مديري الموظفين وتواريخ تعيين الموظفين من أجل سهولة التأكد من نتيجة الاستعلام (قارن تاريخ تعيين مدير بتاريخ تعييم موظف يحمل نفس رقم المدير). ناتج هذه العبارة هو التالي:

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

SELECT (SELECT COUNT (*) FROM tblEmployee AS e2 WHERE e2.EmpNo <= e1.EmpNo) AS Serial, EmpNo, EmpName, EmpHireDate, EmpSal
FROM tblEmployee AS e1

وناتج العبارة هو:

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

استخدام الاستعلامات الفرعية في مقطع WHERE (أو HAVING)

استخدام الاستعلامات الفرعية في شروط المقارنة متنوع، وتوجد العديد من الكلمات المحجوزة التي تسمى predicates، وتحدد طريقة معينة لمقارنة الناتج من الاستعلام الفرعي مع قيم حقول الاستعلام الأساسي. في البدء، دعنا نر كيف يمكن استخدام عوامل المقارنة الرياضية المألوفة مع الاستعلامات الفرعية. إذا عدنا إلى المثال الأول في هذه الحلقة، والذي يطلب استعادة الموظف صاحب أكبر راتب، فإن الاستعلام الفرعي الذي نحتاجه يعيد أكبر راتب، وتتم مقارنة هذا الراتب مع راتب كل موظف بواسطة عامل المقارنة (=) من أجل استخراج الموظف صاحب هذا الراتب. طبعاً إذا كان هناك أكثر من موظف بنفس الراتب فإن كلاً منهم سيتم استعراضه، لكن الاستعلام الفرعي يجب ألا يعيد أكثر من قيمة واحدة فقط. العبارة المطلوبة هي:

SELECT EmpNo, EmpName, EmpHireDate, EmpSal
FROM tblEmployee WHERE EmpSal = (SELECT MAX (EmpSal) FROM tblEmployee)

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

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

SELECT EmpNo, EmpName, EmpHireDate, EmpSal
FROM tblEmployee WHERE EmpDept IN (SELECT dptNo from tblDepartment WHERE dptName IN ("Sales", "Research"))

تم هنا استخدام IN بالطريقتين: استعلام فرعي، وقيم يدوية. لاحظ أن الاستعلام الفرعي لا بد أن يعيد حقلاً مفرداً لأن المقارنة تتم مع حقل مفرد. ناتج العبارة هو:

تجدر أيضاً الإشارة إلى أنه يمكن استخدام NOT IN لاستثناء القيم التي يعيدها الاستعلام الفرعي من الاستعلام الأساسي.

هنالك أيضاً كما أشرت بضع كلمات محجوزة تستخدم مع الاستعلامات الفرعية خاصة. هذه الكلمات هي ALL, ANY, SOME, EXISTS (NOT EXISTS). تستطيع استخدام EXISTS لاستعادة صف في الاستعلام الأساسي في حالة كان الاستعلام الفرعي يعيد صفاً أو أكثر، فهي تستخدم للتأكد من (وجود) قيم في الاستعلام الفرعي. مثال على ذلك الاستعلام عن الأقسام بشرط وجود موظفين يزيد راتبهم عن 3000 في القسم. العبارة المستخدمة قد تشبه التالي:

SELECT tblDepartment.* FROM tblDepartment WHERE EXISTS (SELECT tblEmployee.* FROM tblEmployee WHERE EmpSal >= 3000 AND tblEmployee.EmpDept = tblDepartment.dptNo)

لاحظ كيف ربطنا في الاستعلام الفرعي بيم رقم القسم في جدول الأقسام (الاستعلام الأساسي)، ورقم القسم في جدول الموظفين (الاستعلام الفرعي). في حالةوجود أي موظف بالشرط المذكور في الاستعلام الفرعي، فإنه تتم استعادة القسم في الاستعلام الأساسي. الناتج من العبارة السابقة هو:

مثال آخر، الاستعلام عن العملاء في حالة وجود مشتريات لهم في الشهر الأخير. الاستعلام الأساسي يستحضر بيانات العميل، والاستعلام الفرعي يربط كل صف من جدول العملاء بجدول الفواتير، ويتأكد من وجود أي صفوف مستعادة من هذا الجدول لكل عميل في الشهر الأخير؛ فهو عبارة عن استعلام عن فواتير العميل الحالي في الاستعلام الأساسي بالنسبة للشهر الأخير. يمكن التأكد من عدم وجود صفوف مستعادة من الاستعلام الفرعي لاستعادة الصف في الاستعلام الأساسي باستخدام NOT، كما أرجو أن تلاحظ انه بإمكاننا باستخدام EXISTS فقط استعادة أكثر من حقل في الاستعلام الفرعي.

تستخدم ALL لمقارنة قيم حقل في الاستعلام الأساسي بكل القيم المستعادة من استعلام فرعي.المقارنة قد تتم باستخدام عوامل المقارنة الرياضية. مثلاً، تريد استعادة الموظف من قسم البحوث الذي يقل راتبه عن رواتب كل الموظفين في قسم المحاسبة. تستخدم ALL مع الاستعلام الفرعي كالتالي:

SELECT EmpNo, EmpName, EmpHireDate, EmpSal
FROM tblDepartment INNER JOIN tblEmployee ON tblDepartment.dptNo=tblEmployee.EmpDept
WHERE dptName="Research" AND EmpSal < ALL (SELECT EmpSal FROM tblEmployee WHERE EmpDept IN (SELECT dptNo FROM tblDepartment WHERE dptName = "Accounting"))

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

الكلمتان SOME وANY مترادفتان في المعنى. وتقومان بمقارنة قيم حقل في الاستعلام الأساسي، بقيم حقل في الاستعلام الفرعي، فإذا كانت المقارنة ناجحة ولو بقيمة واحدة من الاستعلام الفرعي، تمت استعادة الصف في الاستعلام الأساسي. هذا يعني أنه باستخدامهما نستطيع استعادة الصفوف من الاستعلام الأساسي التي تحقق شرط المقارنة مع (أي) أو (بعض) القيم المعادة في الاستعلام الفرعي. هذا على خلاف ALL، التي تشترط تحقق شرط المقارنة مع (كل) القيم المعادة في الاستعلام الفرعي. المثال على ذلك هو استعادة بيانات الموظفين الذين يقل راتبهم عن راتب أي موظف في قسم آخر. الصيغة تشبه تماماً صيغة ALL، لذا أترك تنفيذ المثال كتمرين.

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

SELECT EmpNo, EmpName
FROM (SELECT EmpNo, EmpName FROM tblEmployee WHERE EmpDept = 10(

في هذه العبارة، مصدر الاستعلام هو استعلام آخر مؤقت يعيد الموظفين في القسم رقم 10. تستطيع الاستغناء عن هذا النوع بإنشاء استعلامات (رسمية)، ثم استخدامها كمصدر مع بقية الجداول.

قبل أن أنهي نقاشنا عن الاستعلامات الفرعية، من المهم أن تدرك هنا أن استخدام هذه الميزة قد يكون مفيداً جداً في أحوال خاصة. ربما لاحظت قلة استخدام هذه الاستعلامات الفرعية، وهذا يعود لأسباب. بعضها يعود ببساطة إلى غفلة بعض المبتدئين عنها، ربما لأنهم لا يصلون عادة في القراءة إلى ما بعد الأساسيات الأولية، أو يصلون ولا يبذلون الجهد الكافي لاستيعابها. لكن هناك بعض الأسباب الوجيهة التي تتعلق بالأداء. قد يكون أداء الاستعلامات التي تحوي استعلامات فرعية أقل (من حيث السرعة) من أداء استعلامات لا تعتمد عليها. كمثال على ذلك، عرفنا في الحلقة السابقة أن الربط الخارجي قد يستخدم لاستخراج الفرق في حقل بين جدولين، مثل الأرقام التي توجد في حقل بالجدول الأول ولا توجد في حقل مشابه بالجدول الثاني. من الواضح أن هذا الفرق يمكن تحديده بسهولة باستخدام NOT IN، إذ يمكن كتابة استعلام أساسي يستعيد قيم حقل من جدول بشرط عدم وجودها في قائمة القيم المستعادة بالاستعلام الفرعي. لكن بشكل عام، أداء الربط الخارجي أفضل من أداء الاستعلام الفرعي.

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

(يتبع إن شاء الله)...

الشكر الجزيل للمجهود الرائع الذي بحق يستحق الثناء و هو مفخرة من مفاخر هذا المنتدى الرائع الذي يتزين بمثل هكذا خبراء و المقالات التي تزداد يوما بعد يوم

استاذنا الكريم وفقك الله و جعل عملك في ميزان اعمالك ان شاء الله

لي طلب وهو ان امكن استاذنا الكريم وضع كل المناقشة في ملف ورد و ارفاقة لكي نتمكن من حفظها و الرجوع اليها كمرجع اكسس

هذا اذا كان لديك الملف جاهز و الا فانا سوف اصنعه بنفسي

تحياتي و اسف على الطرح المقدم

الأخ الكريم، فكرة التجميع في ملف موجودة بالفعل، وسبق وضعها من قبل الإخوة الكرام يوسف أحمد، ومدير الحلقة محمد ندا، وأبي رحمة جزاهم الله خيراً أجمعين. وقد كنت وعدت بتداول الملفات معهم أولاً بعد ترتيبها مبدئياً، ثم بإذن الله نضعها لسهولة المرجعية...

شكراً لك، وبإذن الله ألبي طلبك قريباً إن شاء الله...

أخوك أحمد

رقم الحلقة (41)

دوال SQL

مقدمة

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

تذكير سريع بمفهوم الدالة

الدالة هي في النهاية برنامج مستقل تمت كتابته من أجل هدف محدد. هذا الهدف هو في الغالب تنفيذ عملية ما على بعض القيم، واحتساب النتيجة. القيم التي تتم عليها المعالجة تعطى للدالة من قبل المبرمج (مستخدم الدالة) وتسمى معاملات parameters الدالة. القيمة الناتجة هي التي تهم المبرمج. لاحظ أن بعض الدوال لا يستقبل أي قيم، ولكنه قد يعيد قيمة. مثلاً، هناك دالة تستدعيها من أجل معرفة تاريخ اليوم؛ لا تستقبل هذه الدالة أي معاملات، لكنها تعيد قيمة هي تاريخ اليوم. مرة أخرى، هذه الدالة هي برنامج سبقت كتابته وترجمته، وهو جاهز في صيغته التنفيذية في مكتبة ما (ملف ما من نوع خاص)، واستدعاء الدالة التي تعيد قيمة لا يعدو استخدامها (كتابة اسمها مع معاملاتها بين قوسين) في تعبير ما expression مثل عملية حسابية أو منطقية، ولأن الدالة تعيد قيمة، فإنك تستخدمها مثل ما تستخدم أي متغير. يجب الانتباه هنا إلى نوع القيمة التي تعيدها الدالة من أجل الاستخدام الصحيح للدالة. كثيراً ما تستقبل القيم التي تعيدها الدوال في متغيرات، ولكل متغير نوع بيانات، لذا ينبغس أن يتوافق نوع المتغير مع نوع القيمة المتوقعة من الدالة. سنحن لا نتحدث هنا عن البرامج التي لا تعيد قيمة، وتسمى في بعض اللغات بشكل مختلف عن الدوال؛ في VBA مثلاً، تسمى هذه البرامج Sub عوضاً عن Function.

لماذا الدوال؟

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

أنواع الدوال

قبل أن أتحدث عن الدوال من جهة اختلاف أنواع البيانات، أرى من المهم التنبيه على أنني لا تحدث هنا الدوال التجميعية، التي تشكل نوعاً قائماً بذاته. تكلمنا عن هذه الدوال فيما سبق، ورأينا استخدامها في سياق التجميع مع المقطع GROUP BY. الفرق بين هذه الدوال وبقية الدوال، أنها عند تطبيقها على حقل، تدخل كل قيم الحقل في الاحتساب، وهذا يعني أن كل صفوف الجدول، أو كل صفوف المجموعة الواحدة في حالة وجود تجميع (الحالة الأغلب) تدخل في الاحتساب، وتنتج قيمة واحدة فقط عن كامل الجدول أو عن كل مجموعة في التجميع. مثلاً عند وجود ثلاثة أقسام في جدول من مئة صف مثلاً، وتم التجميع حسب القسم، فإن دالة مثل COUNT على حقل رقم الموظف تعيد فقط ثلاثة قيم (في ثلاثة صفوف)، هي عدد الموظفين في كل قسم من الأقسام الثلاثة. أما الدوال غير التجميعية، فإنها تطبق على كل قيمة في الحقل، في كل صف، بدون أي تجميع. بمعنى أنها تؤثر على قيم الحقل المفردة في كل صف، ولا تختصر عدد الصفوف. ولذلك، فإنها تستدعى (يتم تنفيذ البرنامج الذي تمثله الدالة) مع كل صف يتم استعادته من الجدول أو الجداول موضوع الاستعلام. يمكنك استخدام الدوال غير التجميعية في قائمة حقول الأمر SELECT وفي غيرها من المقاطع، مثل المقطع WHERE كما سنرى لاحقاً بإذن الله.

تختلف البيانات التي تحفظ في قاعدة البيانات، ولهذا تختلف الدوال التي تعالج هذه البيانات. بشكل عام، هناك دوال تعالج البيانات النصية، ودوال تعالج البيانات العددية (على تنوع الأعداد)، ودوال لمعالجة التواريخ والأوقات، لكن هناك أيضاً دوالّ لا يمكن بشكل دقيق تصنيفها تحت واحد من هذه البنود، مثلاً دوال تتعامل مع القيم الخالية. هناك الكثير من هذه الدوال المشتركة في لغة SQL بين كل منتجي برامج إدارة قواعد البيانات، على الرغم من وجود الكثير من التميز أيضاً. في Jet SQL (تطبيق مايكروسوفت للغة SQL في أكسس(، يتم استخدام دوال VBA (يتم استدعاء الدوال في مكتبات لغة VBA). آخر ما أقصده هنا هو توفير مرجع لهذه الدوال، وإنما القصدهو مجرد تقديم الفكرة والتنويه على وجود هذه الدوال، والحث على البحث عنها في مظانها، واستخدامها. يمكن البحث عن هذه الدوال من أجل مراجعة تفاصيلها من حيث الوظيفة، والمعاملات المطلوبة، والنتيجة المعادة، وطريقة الاستخدام في ملفات المساعدة أو في عدد ضخم من الكتب المتوفرة، أو في عالم النت الرحيب (الرحيب لدرجة الضياع). فيما يلي من سطور بإذن الله، أعرض أمثلة لبعض الدوال المتوفرة في الأكسس (في VBA بشكل دقيق)، مرتبة حسب أنواع الدوال.

الدوال النصية

عند التعامل مع النصوص (سلاسل الحروف، مثل الأسماء)، فإن ما يهمك هو عمليات من قبيل عدد حروف نص أو البحث عن حرف معين أو نص فرعي معين في نص آخر، أو لصق نص مع آخر، أو استقطاع جزء من نص، أو التخلص من الفراغات أو استبدال حرف بآخر أو تغيير حالة حرف إنجليزي مثلاً، وغير ذلك. هناك دالة معدة لكل عملية من هذه العمليات (وأكثر). أنت ترى أن المبرمجين أيضاً يتمتعون بالرفاهية في هذه الحياة. والآن، دعنا نلمس هذه الرفاهية لمسة خفيفة...

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

Len(), Left(), UCase(), LCase() وقصص أخرى

من أجل مثال يجمع عدداً من دوال معالجة النصوص، دعنا نفترض أننا نريد الاستعلام عن أسماء وأرقام هواتف الموظفين بشروط خاصة: الحرف الأول من الاسم يجب أن يكون حرفاً كبيراً capital أو upper case، وبقية الأحرف صغيرة lower case، كما أن أرقام الهاتف ينبغي أن تنسق حسب التالي: 0123034880 يصبح 012-303-4880. انظر إلى العبارة التالية:

SELECT UCase(Left(EmpName,1)) & LCase(Right(EmpName, Len(EmpName)-1)) AS Name, Left(EmpPhone,3) & "-" & Mid(EmpPhone,4,3) & "-" & Mid(EmpPhone,7) AS Phone1, Format(EmpPhone,"0##-###-####") AS Phone2
FROM tblEmployee

استخدمنا من أجل تنسيق رقم الهاتف طريقتين، الطريقة الطويلة باستخدام دالتين من أجل توضيح استخدامهما، والطريقة الأقصر باستخدام الدالة Format. اتضح أن الدالة الأخيرة أفضل من ناحية التعامل مع القيم الخالية، ولذلك كان الخرج أفضل، كما هو واضح في ناتج العبارة التالي:

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

الدالة ()UCase تستخدم لتحويل حالة أحرف معاملها النصي (الإنجليزي) إلى أحرف كبيرة إن كانت صغيرة من قبل. ومثلها الدالة LCase، ولكن التحويل يتم هاهنا من الأحرف الكبيرة إلى الصغيرة. لو استخدمنا هذه الدالة مباشرة على حقل اسم الموظف، فإن الناتج في الصف الأول، وهو LCase(SMITH) يصبح smith. لكن المطلوب هو أن يكون الحرف الأول كبيراً، لذلك قمنا بتقطيع كل اسم إلى جزءين: جزء هو عبارة عن حرف واحد من اليسار، والآخر عبارة عن بقية الأحرف من اليمين. من أجل استقطاع حرف من اليسار نستطيع استخدام أكثر من دالة، منها الدالة الواضحة في هذا السياق، Left(). هذه الدالة تأخذ معاملين: نصاً، وعدد الحروف المطلوبة على يسار هذا النص. من أجل استقطاع الحرف في أقصى اليسار، وتحويله إلى حرف كبير، نستخدم دالة UCase()، على نتيجة الدالة Left()، كالتالي:

UCase(Left('SMITH', 1) = UCase('S') = 'S'

بالمثل، نستطيع استخدام الدالة Right() من أجل استقطاع بقية الأحرف من اليمين. لكننا هنا لا نريد فقط حرفاً واحداً، ولا اثنين، ولكن عدداً متغيراً من الأحرف لا نعلم مسبقاً مقداره بالضبط. الحل هو أن نعمد إلى استخدام معادلة عامة تنفع مع كل النصوص. هذه المعادلة بسيطة جداً، لأن بقية عدد الحروف المطلوبة هو عدد حروف النص كله ماعدا واحداً. هذا يعني أننا نحتاج إلى معرفة العدد الكلي لحروف النص، ثم نطرح واحداً من النتيجة. لدينا دالة تعيد عدد حروف معاملها النصي، ولدينا معامل الطرح، لذلك فإن عدد الحروف عدا الحرف الأول يمكن استخلاصه كالتالي: Len('SMITH') – 1. بتحويل هذه النتيجة إلى أحرف صغيرة، تحصل على التالي:

LCase('MITH') = 'mith' = LCase(Right('SMITH', Len('SMITH' – 1))) = LCase(Right('SMITH', 4))

لم يتبق لنا الآن إلا لصق النتيجتين السابقتين معاً، ويمكن ذلك باستخدام المعامل & الذي يستخدم للصق النصوص بعضها إلى جانب بعض، كالتالي:

UCase(Left('SMITH',1)) & LCase(Right('SMITH', Len('SMITH')-1)) = 'S' & 'mith' = 'Smith'

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

المطلوب الثاني يختلف قليلاً عن المطلوب الأول، حيث نحتاج إلى الحصول على ثلاثة أجزاء من النص (رقم الهاتف) ثم لصقها معاً بعد فصلها بالحرف "-". من أجل الحصول على الحروف الثلاثة الأولى من أقصى اليسار، نستخدم الدالة Left() كما سبق مع تحديد 3 كمعامل ثانٍ. من أجل استقطاع ثلاثة أحرف أخرى من الوسط نحتاج إلى دالة جديدة لديها القدرة على استقطاع جزء من نص. هذه القدرة ينبغي أن تشمل إمكانية تحديد بداية للاستقطاع، ومقدار للاستقطاع (عدد الحروف المطلوبة). لدينا في هذا الصدد الدالة Mid()، التي تأخذ بالفعل ثلاثة معاملات: النص الكامل، رقم الحرف حيث يبدأ الاستقطاع، وعدد الحروف المطلوبة للاستقطاع. هذا يعني أن شيئاً مثل Mid('0123034880', 4, 3) يعيد '303'. المعامل الثالث لهذه الدالة اختياري وليس إجبارياً، وهذا يعني أنك إذا لم تزود الدالة بعدد الحروف المطلوبة للاستقطاع، فإن كل أحرف النص الكامل ابتداء من الموقع المحدد بالمعامل الثاني يتم استقطاعها. من أجل توضيح هذا، تم استخدام هذه الدالة من أجل استقطاع الجزء الأخير (الأيمن) من رقم الهاتف عوضاً عن الدالة Right(). بالتطبيق على رقم الهاتف الأول، نحصل على:

Left('0123034880',3) & '-' & Mid('0123034880',4,3) & '-' & Mid('0123034880',7) = 
'012' & '-' & '303' & '-' & '4880' = '012-303-4880

'

لاحظ أن هذه الطريقة لا تأخذ في الاعتبار حالة حقول رقم الهاتف الخالية؛ لذلك تجد الشرطات الصغيرة في الناتج ولو لم تكن هناك أرقام هاتف. واحد من الحلول الممكنة استخدام الدالة Format. هذه الدالة مرنة للغاية، وتستخدم لتنسيق الخرج، ولها عدة تنويعات مثل FormatNumber، وFormatCurrency، وFormatDateTime وغيرها، وكل واحد منها بستقبل العديد من المعاملات أغلبها اختياري. أرجو أن ترجع إلى ملفات المساعدة أو أي مرجع من أجل الصيغ الدقيقة للاستخدام. ما يهمنا هنا هو استخدام الشكل العام الأول للدالة، وبالرجوع إلى نوع التعبير المعطى كمعامل أول للدالة، يمكن تنسيق الخرج بواسطة تنسيق محدد في المعامل الثاني. هناك عدد من الطرق القياسية في تنسيق الأرقام والتواريخ، لكن فيما يتعلق بالنصوص، تستطيع استخدام تنسيق خاص بك كالتالي:

Format('0123034880', '0##-###-####') = '012-303-4880'

لاحظ استخدام الهاش# مكان أي رقم، أما الصفر في البداية فهو من أجل أن يحافظ الأكسس على أول صفر في الخرج ولا يحذفه. لاحظ أيضاً استخدام عدد محدد من الأقام معلوم مسبقاً، ووضع الشرطة – في المكان المطلوب. أرجو كذلك أن تجرب مع هذه الدالة، مثلاً باستبدال هاش مكان الصفر في البداية، وتقليص أو زيادة عدد الهاشات، ومعاينة النتيجة.

هناك بعد الكثير من الدوال المتوفرة للاستخدام مع النصوص، حتى إنك تستطيع معرفة الآسكي كود ASCII Code لحرف، والعكس، وهو الحصول على الحرف من رقم الآسكي الخاص به. عندما تواجه مشكلة في الحياة العملية، فلا تنس أن تقوم باستكشاف سريع لقائمة الدوال المتاحة، حتى لا تعيد اختراع العجلة من جديد.

الدوال العددية

أشهر الدوال العددية هي الدوال الرياضية. منها الدوال المثلثية trigonometric، ودالة القيمة المطلقة absolute value، والجذر التربيعي square root، ودالة التقريب rounding، وغيرها. من أجل مثال على بعض هذه الدوال، دعنا نفترض مثالاً خيالياً يعيد الجذر التربيعي للأقام من 1 إلى 10، مع تقريب الناتج إلى أقرب رقمين عشريين:

SELECT TOP 10 (SELECT COUNT(*) FROM tblEmployee AS e1 WHERE e1.EmpNo <= e2.EmpNo) AS No, Sqr((SELECT COUNT(*) FROM tblEmployee AS e1 WHERE e1.EmpNo <= e2.EmpNo)) 
AS [Square Root of No], 
Round(Sqr((SELECT COUNT(*) FROM tblEmployee AS e1 WHERE e1.EmpNo <= e2.EmpNo)), 2) 
AS [Square Root Rounded To 2 Decimal Places]
FROM tblEmployee AS e2

ناتج هذه العبارة هو:

الاستعلام الفرعي يعيد رقماً تسلسلياً للموظف ضمن الاستعلام الأساسي (راجع من فضلك الحلقة السابقة). اخترنا عشرة موظفين فقط بواسطة TOP 10، وفي الحقل الثاني استخدمنا الدالة Sqr من أجل استعادة الجذر التربيعي للرقم المتسلسل، ثم قربناه في الحقل الثالث باستخدام الدالةRound التي تأخذ عدد الأماكن العشرية المطلوب التقريب إليها، في معاملها الثاني. لاحظ أن هذا المثال مصطنع، ولكنه يخدم الغرض في توضيح استخدام الدوال العددية.

دوال التاريخ والوقت

لك أن تتوقع أن هناك العديد من الدوال التي تعالج التواريخ والأرقام كذلك. هناك حساب خاص بالتواريخ والأوقات يختلف عن حساب الأعداد كذلك. ربما من أشهر الدوال في هذه المجموعة الدوال Date()، وTime()، وNow()، التي تستخدم للحصول على التاريخ الحالي، والوقت الحالي، وكليهما على التوالي (حسب تقويم وساعة الجهاز). هنالك دوالّ تتيح لك جمع فترة زمنية محددة إلى تاريخ معين، هذه الفترة قد تكون سنة أو شهراً أو يوماً أو دقيقة وهكذا، ودالة أخرى تتيح لك طرح فترة من تاريخ معين، ودوال لاستخراج أجزاء السنة والشهر والأسبوع واليوم والساعة والدقيقة وحتى الثانية من تاريخ محدد. كما أن هناك معامل الطرح العادي (-) الذي يعيد الفرق بين تاريخين بعدد الأيام. كمثال على استخدام بعض هذه الدوال من SQL، دعنا نفترض أننا نريد الاستعلام عن تاريخ تعيين الموظفين مفصلاً إلى سنة وشهر (بالحروف) ويوم، ثم نريد كذلك أن نحدد تاريخ تقاعد كل موظف بإضافة ثلاثين سنة (مثلاً) إلى تاريخ تعيينه:

SELECT EmpNo AS [رقم الموظف], EmpName AS [اسم الموظف], EmpHireDate AS [تاريخ التعيين], Year(EmpHireDate) AS [سنة التعيين],
Format(EmpHireDate, 'mmm') AS [شهر التعيين],
Day(EmpHireDate) AS [يوم التعيين], 
DateAdd("yyyy", 30, EmpHireDate) AS [تاريخ التقاعد] 
FROM tblEmployee

الناتج هو:

في الدالة DateAdd()، يحدد المعامل الأول نوع الفترة (سنة yyyy، أو شهر m أو يوم d، وهكذا...)، ويحدد المعامل الثاني عدد الفترات المطلوب إضافتها (يمكن أن تكون بالسالب من أجل الحصول على تاريخ في الماضي)، ثم يحدد المعامل الثالث تاريخ بدء الاحتساب. أرجو الرجوع إلى المراجع من أجل تفاصيل بقية الدوال.

المزيد؟

نعم، هناك العديد من الدوال المتنوعة التي قد لا تندرج بالضبط تحت واحد من الأنواع السابقة، لكنها لا تقل أهمية أو كثرة في الاستخدام؛ مثال على ذلك الدالة ()IIf، ودوال الاختبار مثل ()IsNull، ودوال التحويل مثل CStr(). مرة أخرى، ليس المقام هنا مقام مرجعية لهذه الدوال، بقدر ما هو مقام إشارة إليها، مجرد إشارة، لتعلم أن هناك عدة مفيدة جداً في جعبتك عندما تواجه بعط المتطلبات. كتعويض عن التفصيل في هذه الحلقة، أزودك هنا بتمرين مفيد، ستستفيد منه إن شاء الله فائدة عظيمة، لو أنك كنت تريد...

تمرين:

اذهب إلى محرر VBA، ثم اكتب الدوال التالية دالة دالة (في أي مكان)، في كل مرة اكتب فقط اسم الدالة، ثم ظلله، ثم اضغط المفتاح F1. إذا كنت لا تعرف كيف تذهب إلى محرر VBA، فإنها لمشكلة! لكن حتى نحل هذه المشكلة، اذهب إلى تبويب الوحدات النمطية ثم أنشئ واحدة جديدة.

Asc, Chr, Format InStr, LCase, Left, LTrim, Mid, Replace, Right, RTrim, String, StrReverse, Trim, UCase

 Abs, Cos, Exp, Log, Rnd, Round, Sgn, Sin, Sqr

Date, DateAdd, DateDiff, DatePart, DateSerial, Day, Hour, Minute, Month, Now, Second, Time, Year

(يتبع إن شاء الله)...

الاستاذ أحمد مبارك الحيقي

عيد مبارك

وكل عام وانت واعضاء المنتدى بخير وفي صحة وعافية

أرى أن التعليمات المرفقة مع اي برنامج تعد مرجعاً غنياً للتعلم خصوصاً تلك التي ترفقها شركة مايكروسوفت في برامجها

لكن ما يحدث هوا ان غالبية المستخدمين لا يقدرون قيمتها او لايعلمون بوجودها

بالنسبة لتعليمات اكسس خاصة تلك التي تتحدث عن الدوال ففيها الكثير مما يمكن تعلمه ولكن تقف اللغه الانجليزية في كثير من الاحيان حائلاً بيننا وبين الاستفاده من تلك المعلومات

بالنسبة لي فانا احاول قدر ما تتيحه لي لغتي الانجليزية ولكن النقص في معرفة اللغه يودي الى نقص في معرفة وظيفة الدالة وامكانياتها

مع خالص التحية

مالك المقطري

السادة الأعضاء والأساتذة المحترمين:

أتشرف بقبولكم انتسابي لهذا المنتدى منذ فترة قريبة وكل عام وأنتم بخير بمناسبة الأضحى المبارك

أقوم الآن بدراسة جميع الحلقات لهذا الموضوع الهام جداً وسأستفيد منها حتماً وأتمنى أن أستطيع المساهمة في إغنائها

صايل عزام