الدرس 16 SQL

مفهوم القيم الإفتراضية لكل حالة

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

• A في حال كانت علامة الطالب بين 90 و 100.

• B في حال كانت علامة الطالب بين 89 و 80.

• C في حال كانت علامة الطالب بين 79 و 70.

• D في حال كانت علامة الطالب بين 69 و 60.

• F في حال كانت علامة الطالب بين 59 و 0.

• Error إذا لم تكن علامة الطالب بين 0 و 100.

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

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

الشكل العام لوضع قائمة إحتمالات على أي عامود

    CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
    END;
  

• لوضع قائمة من القيم الإفتراضية التي سيتم وضعها مكان قيم العامود الأصلية, نضع الكلمتين CASE و End في المكان الذي كنا ننوي فيه عرض قيم العامود.

• أي شرط تريد وضعه على قيم العامود, تضع قبله الكلمة WHEN و بعده الكلمة THEN التي تضع من بعدها القيمة التي سيتم وضعها في حال تحقق الشرط.

• إذا أردت وضع قيمة إفتراضية يتم وضعها في حال لم يتطابق أي شرط, فيمكنك وضع الكلمة ELSE و وضع القائمة الإفتراضية بعدها.

ملاحظة

عند وضع قائمة إحتمالات, تأكد من وضع إحتمالات لكل القيم, لأنه في حال مرور قيمة لا توجد إطلاقاً ضمن قائمة الإحتمالات الموضوعة, سيتم وضع القيمة Null مكانها في النتيجة النهائية.

تجهيز قاعدة البيانات التي سنطبق عليها

قم بتنفيذ الإستعلام التالي حتى تنشئ قاعدة بيانات جديدة إسمها harmash و تنشئ فيها جدول إسمه employees يحتوي على بيانات 10 موظفين.

الإستعلام

      -- سيتم حذفها harmash في حال كان يوجد بالأساس قاعدة بيانات إسمها
      DROP DATABASE IF EXISTS harmash;

      -- harmash هنا قمنا بإنشاء قاعدة بيانات جديدة إسمها
      CREATE DATABASE harmash;

      -- harmash هنا قمنا بتحديد أن أي إستعلام جديد سيتم تنفيذه على قاعدة البيانات
      USE harmash;

      -- يتألف من ستة أعمدة students هنا قمنا بإنشاء جدول جديد إسمه
      -- لأننا قمنا بتحديدها قبل إستدعاء هذا الأمر harmash سيتم إنشاء هذا الجدول بداخل قاعدة البيانات
      CREATE TABLE students (
      id   INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(50),
      math INT
      );

      -- هنا قمنا بإضافة 10 أسطر في الجدول, أي أضفنا معلومات 10 طلاب
      -- لاحظ أننا لم نحدد أسماء الأعمدة التي سنضع فيها البيانات لأننا قمنا بملئ جميع المعلومات
      -- في البداية هو حتى يكون عدد القيم الموضوعة يساوي عدد أعمدة الجدول null سبب وضع الكلمة
      -- ستقوم قاعدة البيانات بوضع رقم تعرفة مختلف لكل سطر null مكان الكلمة
      INSERT INTO students VALUES (null, "Ahmad", 82);
      INSERT INTO students VALUES (null, "Rami", 87);
      INSERT INTO students VALUES (null, "Said", 73);
      INSERT INTO students VALUES (null, "Noura", 92);
      INSERT INTO students VALUES (null, "Amani", 78);
      INSERT INTO students VALUES (null, "Mhamad", 65);
      INSERT INTO students VALUES (null, "Ahmad", 45);
      INSERT INTO students VALUES (null, "Mostafa", 95);
      INSERT INTO students VALUES (null, "Jana", 68);
      INSERT INTO students VALUES (null, "Houssam", 120);
    

بعد تنفيذ الإستعلام السابق في phpMyAdmin سيتم إنشاء قاعدة البيانات harmash و إنشاء الجدول students بداخلها.
قم بالنقر على إسم قاعدة البيانات harmash من القائمة اليسرى حتى تبدأ بالتعامل معها و تطبيق ما ستتعلمه في هذا الدرس.

البيانات التي قمنا بإضافتها بشكل إفتراضي في الجدول students.

id	name	math
1	Ahmad	82
2	Rami	87
3	Said	73
4	Noura	92
5	Amani	78
6	Mhamad	65
7	Ahmad	45
8	Mostafa	95
9	Jana	68
10	Houssam	120

أمثلة حول وضع حالات للقيم التي سترجع

المثال الأول

الإستعلام التالي يقوم بعرض id كل طالب, name كل طالب, بالإضافة إلى ما إن كان الطالب ناجح في مادة الرياضيات أم لا.

طباعة ما إن كان الطلاب ناجح في المادة أم لا وضعناها كالتالي:

• إذا كانت العلامة بين 60 و 100 فعندها يعتبر ناجحاً.

• إذا كانت العلامة بين 0 و 59 فعندها يعتبر راسباً.

• غير ذلك, هناك خطأ في العلامة المدخلة و يجب مراجعة الأستاذ.

الإستعلام

      SELECT                 -- لجلب بيانات من جدول SELECT هنا قمنا باستخدام الأمر
      id AS 'Id',        -- هنا قمنا بتحديد أول عامود سيتم عرض قيمه مع تغيير إسم العامود
      name AS 'Name',    -- هنا قمنا بتحديد ثاني عامود سيتم عرض قيمه مع تغيير إسم العامود
      CASE               -- هنا قمنا بتحديد ما سيتم عرضه في العامود الثالث, سيتم عرض قيمه من القائمة التالية
      WHEN math BETWEEN 60 AND 100 THEN 'Passed'   -- 'Passed' بين 60 و 100 سيتم وضع القيمة math إذا كانت قيمة الحقل
      WHEN math BETWEEN 0  AND 59  THEN 'Failed'   -- 'Failed' بين 0 و 59 سيتم وضع القيمة math إذا كانت قيمة الحقل
      ELSE 'Refer to Professor'                    -- 'Refer to Professor' أي قيمة أخرى, سيتم وضع الجملة math إذا كانت قيمة الحقل
      END AS 'Math Note'                               -- هنا قمنا بتغيير إسم العامود
      FROM
      students;          -- هنا قمنا بتحديد إسم الجدول الذي سيتم جلب البيانات منه
    

سنحصل على النتيجة التالية عند تنفيذ الإستعلام.

Id	Name	Math Note
1	Ahmad	Passed
2	Rami	Passed
3	Said	Passed
4	Noura	Passed
5	Amani	Passed
6	Mhamad	Passed
7	Ahmad	Failed
8	Mostafa	Passed
9	Jana	Passed
10	Houssam	Refer to Professor

المثال الثاني

الإستعلام التالي يقوم بعرض معلومات الطالب كما هي, و علامة مادة الرياضيات math يعرضها مرة ثانية على شكل أحرف كالتالي:

• A في حال كانت علامة الطالب بين 90 و 100.

• B في حال كانت علامة الطالب بين 89 و 80.

• C في حال كانت علامة الطالب بين 79 و 70.

• D في حال كانت علامة الطالب بين 69 و 60.

• F في حال كانت علامة الطالب بين 59 و 0.

• Error إذا لم تكن علامة الطالب بين 0 و 100.

الإستعلام

      SELECT                           -- لجلب بيانات من جدول SELECT هنا قمنا باستخدام الأمر
      id AS 'Id',                  -- هنا قمنا بتحديد أول عامود سيتم عرض قيمه مع تغيير إسم العامود
      name AS 'Name',              -- هنا قمنا بتحديد ثاني عامود سيتم عرض قيمه مع تغيير إسم العامود
      math AS 'Math (Percent)',    -- هنا قمنا بتحديد ثالث عامود سيتم عرض قيمه مع تغيير إسم العامود
      CASE                         -- هنا قمنا بتحديد ما سيتم عرضه في العامود الرابع, سيتم عرض قيمه من القائمة التالية
      WHEN math BETWEEN 90 AND 100 THEN 'A'    -- 'A' بين 90 و 100 سيتم وضع الحرف math إذا كانت قيمة الحقل
      WHEN math BETWEEN 80 AND 89  THEN 'B'    -- 'B' بين 80 و 89 سيتم وضع الحرف math إذا كانت قيمة الحقل
      WHEN math BETWEEN 70 AND 79  THEN 'C'    -- 'C' بين 70 و 79 سيتم وضع الحرف math إذا كانت قيمة الحقل
      WHEN math BETWEEN 60 AND 69  THEN 'D'    -- 'D' بين 60 و 69 سيتم وضع الحرف math إذا كانت قيمة الحقل
      WHEN math BETWEEN 0  AND 59  THEN 'E'    -- 'E' بين 0 و 59 سيتم وضع الحرف math إذا كانت قيمة الحقل
      ELSE 'Refer to Professor'                -- 'Refer to Professor' أي قيمة أخرى, سيتم وضع الجملة math إذا كانت قيمة الحقل
      END AS 'Math (Letter)'                       -- هنا قمنا بتغيير إسم العامود
      FROM
      students;                    -- هنا قمنا بتحديد إسم الجدول الذي سيتم جلب البيانات منه
    

سنحصل على النتيجة التالية عند تنفيذ الإستعلام.

Id	Name	Math (Percent)	Math (Letter)
1	Ahmad	82	B
2	Rami	87	B
3	Said	73	C
4	Noura	92	A
5	Amani	78	C
6	Mhamad	65	D
7	Ahmad	45	E
8	Mostafa	95	A
9	Jana	68	D
10	Houssam	120	Refer to Professor

مفهوم القيم الموحدة

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

إذا حاولت إنشاء حساب جديد في مدونة هرمش و قمت بإدخال mhamad كإسم المستخدم, سيظهر لك أنه لا يمكنك استخدام هذا الإسم كالتالي.

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

فوائدة القيم الموحدة في التطبيقات و المواقع

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

• يمكنك تسجيل الدخول به.

• يمكنك البحث عن أي مستخدم من خلال إسم المستخدم الخاص به.

• يمكنك الإبلاغ عن أي مستخدم من خلال إسم المستخدم الخاص به.

• في مواقع التواصل الإجتماعي مثل تويتر و فيسبوك, يمكنك أن تشير لأي حساب ( أي تفعل له Mention ) من خلال وضع الرمز @ و من ثم ذكر إسم المستخدم.

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

الكلمة UNIQUE

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

ملاحظة: في الأمثلة التي وضعناها في هذا الدرس, إعتمدنا أسلوب قواعد بيانات MySQL لأنها القاعدة التي نتعامل معها منذ بداية الدورة.

في قواعد بيانات MySQL

لإنشاء عامود لا يقبل أن يتم تخزين نفس القيمة فيه أكثر من مرة نضيف الخاصية UNIQUE كالتالي.

    CREATE TABLE table_name (
    column_name datatype,
    UNIQUE (column_name)
    );
  

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

في قواعد بيانات Access / SQL Server / Oracle

لإنشاء عامود لا يقبل أن يتم تخزين نفس القيمة فيه أكثر من مرة نضيف الخاصية UNIQUE كالتالي.

    CREATE TABLE table_name (
    column_name datatype UNIQUE
    );
  

إنشاء عامود لا يقبل قيم مكررة

قم بتنفيذ الإستعلام التالي حتى تنشئ قاعدة بيانات جديدة إسمها harmash و تنشئ فيها جدول إسمه users يحتوي على بيانات 5 مستخدمين.
ملاحظة: قمنا بإضافة الخاصية UNIQUE إلى العامود username لأننا لا نريد أن يكون هناك مستخدمين عندهم نفس إسم المستخدم.

الإستعلام

      -- سيتم حذفها harmash في حال كان يوجد بالأساس قاعدة بيانات إسمها
      DROP DATABASE IF EXISTS harmash;

      -- harmash هنا قمنا بإنشاء قاعدة بيانات جديدة إسمها
      CREATE DATABASE harmash;

      -- harmash هنا قمنا بتحديد أن أي إستعلام جديد سيتم تنفيذه على قاعدة البيانات
      USE harmash;

      -- يتألف من 4 أعمدة users هنا قمنا بإنشاء جدول جديد إسمه
      -- لأننا قمنا بتحديدها قبل إستدعاء هذا الأمر harmash سيتم إنشاء هذا الجدول بداخل قاعدة البيانات
      CREATE TABLE users (
      id         INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      first_name VARCHAR(50),
      last_name  VARCHAR(50),
      username   VARCHAR(30),
      UNIQUE(username)          -- لا يقبل تخزين قيم مكررة username هنا قمنا بتحديد أن العامود
      );

      -- هنا قمنا بإضافة 5 أسطر في الجدول, أي أضفنا معلومات 5 مستخدمين
      -- لاحظ أننا لم نحدد أسماء الأعمدة التي سنضع فيها البيانات لأننا قمنا بملئ جميع المعلومات
      -- في البداية هو حتى يكون عدد القيم الموضوعة يساوي عدد أعمدة الجدول null سبب وضع الكلمة
      -- ستقوم قاعدة البيانات بوضع رقم تعرفة مختلف لكل سطر null مكان الكلمة
      INSERT INTO users VALUES (null, "Ahmad", "Alhazem", "ahmad");
      INSERT INTO users VALUES (null, "Rami", "Algharib", "rami");
      INSERT INTO users VALUES (null, "Said", "Alnaja", "said");
      INSERT INTO users VALUES (null, "Noura", "Jamali", "noura");
      INSERT INTO users VALUES (null, "Amani", "Sarrouf", "amani");
    

بعد تنفيذ الإستعلام السابق في phpMyAdmin سيتم إنشاء قاعدة البيانات harmash و إنشاء الجدول users بداخلها.
قم بالنقر على إسم قاعدة البيانات harmash من القائمة اليسرى حتى تبدأ بالتعامل معها و تطبيق ما ستتعلمه في هذا الدرس.

البيانات التي قمنا بإضافتها بشكل إفتراضي في الجدول users.

id	first_name	last_name	username
1	Ahmad	Alhazem	ahmad
2	Rami	Algharib	rami
3	Said	Alnaja	said
4	Noura	Jamali	noura
5	Amani	Sarrouf	amani

تجربة إدخال قيم متكررة في عامود نوعه UNIQUE

الإستعلام التالي يحاول إضافة مستخدم جديد و إعطائه إسم مستخدم يملكه شخص آخر.

الإستعلام

      INSERT INTO users (first_name, last_name, username)     -- هنا قمنا بتحديد أسماء الأعمدة التي سنمرر لها القيم
      VALUES ("Ahmad", "Harmush", "ahmad");                   -- هنا قمنا بتمرير القيم التي سيتم إضافتها فيها
    

عند تنفيذ الإستعلام سيظهر الخطأ التالي.
معنى هذا الخطأ أنه يوجد في الأساس مستخدم عنده username يساوي 'ahmad' لهذا لا يمكن إضافة المستخدم الجديد.

Duplicate entry 'ahmad' for key 'username'

لإضافة المستخدم السابق, ستكون مجبر على تغيير إسم المستخدم سواء تغييره بالكامل أو التعديل عليه.
الإستعلام التالي يضيف المستخدم السابق و يعرض كل بيانات المستخدمين الموجودين في الجدول.

ما فعلناه لحل مشكلة Duplicate entry هو تغيير قيمة الحقل usernam إلى 'ahmad2020' لأنه لا يوجد مستخدم آخر يملك نفس هذه القيمة.

الإستعلام

      INSERT INTO users (first_name, last_name, username)     -- هنا قمنا بتحديد أسماء الأعمدة التي سنمرر لها القيم
      VALUES ("Ahmad", "Harmush", "ahmad2020");               -- هنا قمنا بتمرير القيم التي سيتم إضافتها فيها

      SELECT * FROM users;                                    -- لمعرفة التغيرات الجديدة users هنا قمنا بعرض جميع البيانات الموجودة في الجدول
    

سنحصل على النتيجة التالية عند تنفيذ الإستعلام.

id	first_name	last_name	username
1	Ahmad	Alhazem	ahmad
2	Rami	Algharib	rami
3	Said	Alnaja	said
4	Noura	Jamali	noura
5	Amani	Sarrouf	amani
6	Ahmad	Harmush	ahmad2020