دورة الأكسيس : الدرس الأخير : لغة الاستعلامات الهيكلية 2
بسم الله الرحمن الرحيم .
السلام عليكم ورحمة الله وبركاته .
استخدام لغة ( تقنية ) الإستعلامات بطريقة
TSQL (2) .
سنبعد في درسنا هذا عن TSQL مؤقتاً عن جو البحث وسنتحدث عن اضافة وحذف الجداول في قواعد البيانات .
سنبدأ أولاً بصيغة انشاء جدول وذلك بالشكل التالي :
CREATE TABLE tb1 ( colum type, colum type,........);
وكمثال على ذلك ... لننشئ جدولاً تحت اسم Tb2 يحتوي على حقلين : الإسم Name من نوع ( نص - String ) والآخر Age من نوع Number :
CREATE TABLE tb1 (name text,age number );
إذن ماذا لو أردنا أن نحدد حجم حقل العمر Name بخمس خانات فقط ... إذن ضع عدد الخانات بين قوسين مع تعيين نوع بيانات الاسم ك Text وذلك بالشكل التالي :
CREATE TABLE tb1 (name text(6),age number );
آخر نقطة سأشرحها في انشاء الجداول هي كيف نجعل الحقل لا يقبل فراغ - Null ... ولنجرب هذا المثال مع الإسم أيضاً :
CREATE TABLE tb1 (name text(6) Not Null,age number );
ولكن ماذا عن الحذف ... في الواقع هذا الكود يقوم بحذف الجدول الذي قمنا بإنشاءه تواً :
DROP Table tb1;
ولو كان هذا الجدول مرتبطاً بعلاقات فلا بد من تحديثها باضافة CASCADE إلى آخر الكود وذلك بالشكل التالي :
DROP Table tb1 CASCADE;
لا تنسى أن تنشأ الجدول بعد حذفه لأننا سوف نعمل عليه مجدداً .
والآن سنبدأ في اضافة حقول إليه وذلك بعد عملية الانشاء - أعتقد أن الفرق بين الجدول وقاعدة البيانات والحقول أصبح واضحاً - .
نستطيع اضافة الحقول بصيغة عامة بالطريقة التالية :
ALTER TABLE tb1 ADD colum type;
فلنضف حقلين جديدين : الأول هو تاريخ الميلاد ، والثاني النوع ( ذكر - أنثى ) .
ALTER TABLE tb1 ADD birth_Date date,Gender Text;
والآن لنحذف أحد هذه الحقول وليكن حقل Gender ... لاحظ أننا دائماً نضيف كلمة CASCADE لتحديث العلاقات - في حال وجودها - ، وذلك بالشكل التالي :
ALTER TABLE tb1 DROP gender CASCADE;
والآن سوف نتعلم اضافة السجلات ، وسوف نستخدم Tb_Main كجدول لنجرب أوامرنا عليه ، والآن إلى الصيغة العامة لأمر الإضافة :
INSERT INTO table VALUES (v1, v2,......);
لاحظ أننا سنمرر القيم واحداً وراء الثاني وتفصل بينهم فاصلة ، مع مراعاة وضع النصوص بين علامتي تنصيص "" والتواريخ بين علامتي ## .
سنمرر القيم التالية : الرقم - الإسم الأول - الإسم الأخير - رقم الهاتف - العمر - العنوان - البريد الإلكتروني - السنة - اللغة - الموقع - متزوج/لا - ملاحظات .
وذلك بالشكل التالي :
Insert into tb_main values(11,'ahmed','gamal',6666666,18,'Cairo','Hamm ada2091','First Year','','',false,'');
تواحهنا مشكلة وهو أننا جعلنا الرقم حقلاً وحيداً - لا تتكرر قيمه - لذا وجب علينا في كل مرة أن نغير الرقم ... برمجياً يمكننا ذلك ولكن من خلال الأكسيس جرب ازالة رقم 11 ووضع كلمة MyNumber بالشكل التالي :
Insert into tb_main values(MyNumber,'ahmed','gamal',6666666,18,'Cairo' ,'Hammada2091','First Year','','',false,'');
جرب تنفيذ جملة الإستعلام وستظهر لك رسالة تطلب منك ادخال قيمة MyNumber . ضع قيمة بشرط ألا تكون مكررة وسوف يتم اضافتها في الجدول .
* ملاحظة :
إن هذا الجزء في الأكسيس سيقابله متغير في الفيجوال بيسك .. كذا الامر بالنسبة للبحث .
هل تتذكر جملة البحث التالية :
Select * From Tb_Main where name='ahmed';
جرب أن تزيل Ahmed وضع متغيراً MyName بالشكل التالي :
Select * From Tb_Main where name = myname;
وهذا يعطيك تحكماً فيما ترغب في البحث عنه ...
التعديل :
الصيغة العامة لتعديل بيانات سجل بالشكل التالي :
UPDATE table_name SET colum1 = v1 , colum2 = v2 WHERE colum = v;
سنجرب الآن مثالاً لجعل العمر = 28 والموقع =
www.vb4arab.com للشخص الذي اسمه الأول ( Ahmed ) والاخير ( Gamal ) :
Update tb_main set age=25 , site='
www.vb4arab.com' where fname='ahmed' and lname='Gamal';
ولكن ماذا لو كان الأمر يشمل أكثر من سجل . في الواقع هذا الأمر شائع ويشبهه المثال التالي :
Update tb_main set fname='Ahmad' where fname='ahmed';
قد يستخدم مثل هذا المثال لتوحيد الأسماء خصوصاً مع اللغة العربية ، كما يمكن أن يستخدم أيضاً لتجاهل تأثير الهمزة في عمليات البحث ، فمثلاً يصبح أحمد = احمد .
ولكن ماذا لو أردنا تعميم التغييرات ... في هذه الحالة لن نضع الشرط ، ويمكن وضع أمر شبيه بالأمر التالي لزيادة الأعمار 5 سنوات :
Update tb_main set age=age+5;
يعتبر هذا الأمر من أكثر الأوامر شيوعاً في قواعد بيانات الشركات ... فمثلاً يستخدم لزيادة رواتب الموظفين بنسبة معينة ( يمكن أن تكون زيادة ثابته أو نسبيه ) .
ولكن ماذا عن الحذف ... في الواقع تتخذ جملة البحث صيغة عامة كالتالي :
DELETE FROM table WHERE colum = value;
ويمكن أن يكون الحذف تحت تأثير تحقق أكثر من شرط .. في المثال التالي سوف نحذف جميع الأشخاص الذين تزيد أعمارهم عن الأربعين :
Delete from tb_main where age>40;
ولكن ماذا عن حذف كامل محتويات جدول ما ... هذه هي الطريقة :
DELETE * FROM table;
الدوال في الاستعلامات .
تستخدم العديد من الدوال ضمن طيات جمل الاستعلام ، وهي شائعة الاستخدام ، ومريحة ، وتعيد قيمة وحيدة - لا تعيد جدول - سنتعرف على بعض الدوال مع بعض الأمثلة خلال هذا الدرس .
ولننظر نظرة سريعة إلى الصيغة العامة لاستخدام الدوال والتي تأخذ الشكل التالي :
SELECT func(colum) FROM table WHERE condition;
الدالة
AVG :
تعطينا هذه الدالة متوسط حقل ما ، ولحساب متوسط الأعمار في قاعدة البيانات مثلاً نكتب أمراً كالتالي :
Select AVG(Age) from tb_main;
ولك ان تتخيل ماذا سنفعل لو لم نستخدم هذه الدالة ، كنا سندور على جميع السجلات حيث نجمع ارقام كل سجل ثم ننتقل إلى التالي وهكذا - برمجياً - .
أيضاً يمكننا استخدام الدالة بشرط ... فمثلاً لحساب متوسط أعمار الأشخاص الذين لا تزيد أعمارهم عن 25 :
SELECT AVG(Age) FROM tb_main where age < 25;
لو لاحظت لوجدت أن ناتج الدالة يظهر في حقل تحت اسم Expr100 أو ما شابه ... لذا قم - إذا كنت تريد - باعادة تسمية حقل الناتج عن طريق As كما تعلمنا سابقاً وبالشكل التالي :
SELECT AVG(Age) as AVGAGE FROM tb_main WHERE age < 25;
لاحظ أن الدالة سوف تتجاهل السجلات الفارغة ...
الدوال
Sum,Max,Min :
تعطي هذه الدوال المجموع - الأكبر - الأصغر على التوالي ، وهذا مثال على أكبر عمر في قاعدة البيانات :
SELECT max(Age) AS mxAGE FROM tb_main;
لا تنس أن بإمكاننا وضع شرط لعملية الإستعلام .
الدالة
Count :
وتعيد هذه الدالة عدد السجلات ولها صورتان تعمل مع أكسيس ... أما الصورة الثالثة فهي لا تعمل على الاكسيس :
الصيغة الأولى :
Count(colum) ... وتعيد عدد السجلات التي لم يكن هذا الحقل فارغاً فيها .
الصيغة الثانية : Count(*) ... وتعيد عدد السجلات دون النظر إلى إذا ما كانت فارغة أم لا .
أما الصيغة الثالثة : COUNT(DISTINCT Colum( حيث تقوم بعد السجلات مع تجاهل المكرر منها .
ولنر مثالاً على الصيغة الثانية :
Select count(*) From Tb_Main;
لا تنس أن بإمكانك وضع شرط لعملية العد .
كانت هذه بعض الدوال البسيطة والاكثر شهرة في TSql والآن ، ماذا لو كانت لدينا علاقات ونرغب في العمل عليها .
في مثل هذه الحالة ... نريد الحصول على معلومات البطاقة الشخصية الخاصة باسم ما وذلك من Tb_R2 - لاحظ أنه لا يوجد حقل للاسم في الجدول الثاني - .
SELECT tb_main.Fname, tb_main.lname, Tb_R1.passport, tb_r1.bclass, tb_r1.from
FROM tb_main, tb_r1
WHERE tb_main.Number = tb_r1.Number;
دعنا الآن نلاحظ الفروق بين هذه الطريقة والطريقة السابقة :
أولاً : نقوم بكتابة اسم الجدول.اسم الحقل وذلك مهم لأننا نتعامل مع أكثر من جدول .
ثانياً : نقوم بكتابة اكثر من جدول بعد عبارة
From لأننا نريد النواتج من أكثر من جدول .
ثالثاً : السطر الأخير من جملة الاستعلام هو لكي يعرض المعلومات التي تتشابه أرقامها في الجدولين سوية .
دعنا الآن نجرب مثالاً آخراً ... وذلك بالاستعلام عن كافة المعلومات التي تتعلق بالأصدقاء الذين تزيد أعمارهم عن 20 سنة .
SELECT tb_main.Fname, tb_main.lname, Tb_R1.passport, tb_r1.bclass, tb_r1.from
FROM tb_main, tb_r1
WHERE tb_main.Number = tb_r1.Number and tb_main.age >25;
*** واجب سريع : حاول أن تنشئ جملة استعلام تقوم بعرض اسماء الطلاب + ارقام البطاقات الشخصية + فصيلة الدم + عدد الكتب الدراسية لكل صديق يحتوي اسمه على
h ( لا تنسى عبارة like ) .
والآن نريد تطبيق امثلة جديدة ، ولكي نبدأ في تطبيق أمثلة هذا الدرس لا بد أن يكون بعض الأصدقاء لديهم سجلات في الجدول الثاني والبعض الآخر لا .
ومازال حديثنا حول العلاقات وما يتعلق بها ... وسنتحدث عن الدالة Join .
النوع الأول :
Inner Join :
هي الطريقة العادية والتي تحدثنا عنها في الدرس السابق تعطينا النواتج في حال كان شرط العلاقة متحققاً في كلا الجدولين .
بمعنى : جرب أن تحذف بعض سجلات الأصدقاء من الجدول الثاني ، وستجد أن معلوماتهم - حتى الأساسية - لن تظهر .
هذا النوع من الربط يطلق عليه Inner Join وهو الافتراضي ... ويمكننا كتابته بالشكل التالي للتفريق بين الأنواع المختلفة :
SELECT tb_main.Fname, tb_main.lname, Tb_R1.passport, tb_r1.bclass, tb_r1.from
FROM tb_main inner join tb_r1
on tb_main.Number=tb_r1.Number;
لاحظ أننا استبدلنا Where ب on كما وضعنا inner join بين اسمي الجدولين .
النوع الثاني :
Left Join :
في هذا النوع يعرض جميع بيانات الجدول الأول ( على اليسار ) ومن ثم يعرض البيانات المقابلة في الجدول الثاني .
لاحظ في هذا النوع أمرين :
* إذا كانت هناك سجلات ( على اليسار ) لا تقابلها سجلات على اليمين فإن اليسار يعرض ويعرض اليمين فارغاً .
* وإذا كانت هناك سجلات ( على اليمين ) ليس لها مقابل على اليسار فإنها لا تُعرض أبداً .
مثال هذه النوعية :
SELECT tb_main.Fname, tb_main.lname, Tb_R1.passport, tb_r1.bclass, tb_r1.from
FROM tb_main Left JOIN tb_r1 ON tb_main.Number=tb_r1.Number;
النوع الثالث :
Right Join :
هو النوع العكسي تماماً للعملية السابقة ، وهذا مثال عليه :
SELECT tb_main.Fname, tb_main.lname, Tb_R1.passport, tb_r1.bclass, tb_r1.from
FROM tb_main Right JOIN tb_r1 ON tb_main.Number=tb_r1.Number;
في النهاية لا تنس ان تراجع سلسلة دروس TSQL لمزيد من الفائدة ، أيضاً لا تنس ان SQL لا زالت تحتوي على الكثير من الأوامر والتي يمكنك البحث في المزيد منها على الانترنت .
بهذا نكون بحمد الله قد اتممنا الجزء الأول من الدورة الكاملة ، أي الدورة الأولى، وإلى اللقاء في الدورة القادمة .
والله الموفق ...
والسلام عليكم ورحمة الله وبركاته .