الدرس 21 SQL

مفهوم وضع شروط لتخزين القيم

عند تخزين البيانات في أي جدول, نلاحظ أنه علينا فقط إحترام نوع البيانات التي يجب تخزينها في كل حقل. فمثلاً العامود الذي نوعه INT يمكن أن نخزن فيه أعداد صحيحة, و العامود الذي نوعه VARCHAR يمكن أن نخزن فيه نصوص و هكذا.

ماذا لو أردت تحديد القيم التي يسمح بتخزينها, فمثلاً كنت تريد تخزين أعداد صحيحة و لكنك تريد أن تكون هذه الأعداد بين 0 و 100 فقط.
في هذه الحالة يمكنك إضافة قيد ( CONSTRAINT ) نوعه CHECK تضع فيه شرط قبول أي قيمة سيتم تخزينها في العامود.

وضع شرط على العامود عند إنشاء الجدول

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

مثال

        CREATE TABLE books (
        id    INT NOT NULL,
        title VARCHAR(255),
        pages INT,
        price DECIMAL(5,2),
        -- يجب أن تكون قيمته أقل من 50 price يجب أن تكون قيمته أكبر من 0, و العامود pages يحدد أن العامود chk_book_pages_and_price هنا قمنا بوضع قيد بإسم
        CONSTRAINT chk_book_pages_and_price CHECK (pages > 0 AND price < 50)
        );
      

طريقة متاحة في قواعد بيانات MySQL

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

مثال

        CREATE TABLE books (
        id    INT NOT NULL,
        title VARCHAR(255),
        pages INT,
        price DECIMAL(5,2),
        -- يجب أن تكون قيمته يجب أن تكون أقل من 50 price يجب أن تكون قيمته أكبر من 0, و العامود pages هنا قمنا بوضع قيد يحدد أن العامود
        CHECK (pages > 0 AND price <= 50)
        );
      

طريقة متاحة في قواعد بيانات SQL Server / Oracle / Access

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

مثال

        CREATE TABLE books (
        id    INT NOT NULL,
        title VARCHAR(255),
        pages INT CHECK (pages  > 0),           -- قيمته يجب أن تكون أكبر من 0 pages هنا قمنا بوضع قيد يحدد أن العامود
        price DECIMAL(5,2) CHECK (price < 50)   -- قيمته يجب أن تكون أقل من 50 price هنا قمنا بوضع قيد يحدد أن العامود
        );
      

وضع شرط على العامود بعد إنشاء الجدول

في حال كنت تنوي وضع شروط على القيم التي سيتم تخزينها في عامود واحد, يمكنك استخدام الأمر ALTER لوضعها كالتالي.

      ALTER TABLE table_name
      ADD CHECK (conditions);
    

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

      ALTER TABLE table_name
      ADD CONSTRAINT check_constraint_name
      CHECK (conditions);
    

المثال الأول

الإستعلام التالي يقوم بوضع شرط على القيم التي يمكن تخزينها في العامود price و يحدد أن القيم التي تضاف فيه يجب أن تكون أقل من 50.

الإستعلام

        ALTER TABLE books          -- books هنا قمنا بتحديد أننا نريد إجراء تعديل على بنية الجدول
        ADD CHECK (price < 50);    -- قيمته يجب أن تكون أقل من 50 price هنا قمنا بوضع قيد يحدد أن العامود
      

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

الإستعلام التالي يقوم بوضع شرط على القيم التي يمكن تخزينها في العامود price يحدد أن القيم التي تضاف فيه يجب أن تكون أصغر من 0, و شرط آخر على العامود price يحدد أن القيم التي تضاف فيه يجب أن تكون أقل من 50.

الإستعلام

        ALTER TABLE books                          -- books هنا قمنا بتحديد أننا نريد إجراء تعديل على بنية الجدول
        ADD CONSTRAINT chk_book_pages_and_price    -- chk_book_pages_and_price هنا قمنا بوضع قيد بإسم
        CHECK (pages > 0 AND price < 50)           -- يجب أن تكون قيمته أقل من 50 price يجب أن تكون قيمته أكبر من 0, و العامود pages يحدد أن العامود
      

حذف قيد الشرط الموضوع على العامود

طريقة إلغاء قيد ( CONSTRAINT ) الشرط الموضوع على القيم التي يمكن تخزينها في العامود تختلف من قاعدة بيانات لأخرى.

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

      ALTER TABLE table_name
      DROP CHECK chk_constraint_name;
    

الإستعلام التالي يقوم بإلغاء قيد إسمه chk_book_pages.

مثال

        ALTER TABLE books             -- books هنا قمنا بتحديد أننا نريد إجراء تعديل على بنية الجدول
        DROP CHECK chk_book_pages;    -- الموضوع على أحد أعمدته chk_book_pages هنا قمنا بتحديد أننا نريد إلغاء القيد
      

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

      ALTER TABLE table_name
      DROP CONSTRAINT chk_constraint_name;
    

الإستعلام التالي يقوم بإلغاء قيد إسمه chk_book_pages.

مثال

        ALTER TABLE books                  -- books هنا قمنا بتحديد أننا نريد إجراء تعديل على بنية الجدول
        DROP CONSTRAINT chk_book_pages;    -- الموضوع على أحد أعمدته chk_book_pages هنا قمنا بتحديد أننا نريد إلغاء القيد
      

مفهوم الـ Transaction

كلمة Transaction يقصد بها مجموعة من الأوامر إما أن تتنفذ جميعها بنجاح و إما لا يتم تنفيذها.

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

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

كخلاصة للموضوع, Transaction يقصد بها محاولة تنفيذ جميع الأوامر و في حال فشل أي أمر موضوع موضوع يجب إلغاء تنفيذ كل الأوامر التي تم تنفيذها بنجاح.
قواعد بيانات MySQL - SQL Server - Access - Oracle - SQLite جميعها تتيح لك إمكانية وضع الكود في Transaction.

طريقة وضع الكود بداخل Transaction

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

لإعلام قاعدة البيانات بأنك تريد إيقاف الحفظ التلقائي, يجب أن تنفذ الإستعلام التالي.

      SET autocommit = OFF;
    

الآن, عليك معرفة أن طريقة وضع الكود بداخل Transaction تختلف قليلاً من قاعدة بيانات لأخرى و لكن الفكرة هي نفسها تماماً.

في قواعد بيانات MySQL / Oracle / SQLite

      BEGIN;
      -- statements
      COMMIT;
    

• الكلمة BEGIN نضعها لتحديد أين تبدأ الـ Transaction.

• مكان الكلمة -- statements نضع كل الأوامر التي نريدها إما أن تتنفذ جميعها بنجاح و إما لا يتم تنفيذها.

• الكلمة COMMIT نضعها لتحديد أين تنتهي الـ Transaction مما يعني أنه عند الوصول لها بدون أي مشاكل سيتم الموافقة على حفظ كل العمليات التي تم إجراءها.

ملاحظة

إذا كنت تستخدم قواعد بيانات SQL Server أو Access فكل ما عليك فعله هو تبديل كلمة BEGIN; بكلمة BEGIN TRANSACTION;.

مثال

في الإستعلام التالي, إفترضنا أننا نقوم بتخزين عملية شراء لمنتج.

• المنتج الذي تم بيعه إفترضنا أنه يملك id يساوي 33.

• الشخص الذي قام بشراء المنتج إفترضنا أنه يملك يملك id يساوي 1.

إذا تمت عملية البيع بنجاح, يجب إنقاص كمية المنتج الذي تم بيعه في جدول المنتجات products واحداً.
أيضاً, يجب إضافة id الشخص الذي قام بالشراء و id المنتج الذي تم بيعه في جدول الطلبيات orders.

بما أنه يجب تنفيذ العمليتين سويةً أو عدم تنفيذهما من الأساس في حال حدث خطأ ما, سنقوم بوضع العملية بداخل Transaction.

الإستعلام

        -- Transaction هنا قمنا ببدء
        BEGIN;

        -- يساوي 33 بهدف إنقاصها واحداً id للمنتج الذي يملك available_quantity هنا إفترضنا أننا نريد تحديث قيمة الحقل
        UPDATE products SET available_quantity = 9 WHERE id = 33;

        -- المنتج في جدول الطلبيات id المشتري و id سيتم إضافة أن available_quantity إذا لم يحدث مشكلة عند محاولة إنقاص 1 من قيمة الحقل
        INSERT INTO orders (user_id, product_id) values (1, 33);

        -- سيتم حفظ التغيرات في قاعدة البيانات Transaction إذا لم يحدث أي خطأ في الأوامر الموضوعة في الـ
        COMMIT;
      

التراجع عن تنفيذ أوامر الـ Transaction

لحفظ كل التغيرات التي تم إجراءها بداخل Transaction قلنا أننا يجب أن ننفذ الأمر COMMIT; لإعلام قاعدة البيانات أننا نريد حفظ كل العمليات التي تم إجراءها.
الآن للتراجع عن كل التغيرات التي تم إجراءها على قاعدة البيانات, يمكننا أن ننفذ الأمر ROLLBACK; ما لم يتم تنفيذ الأمر COMMIT; لأنه لا يمكننا التراجع عن تنفيذ الأوامر في حال تم تنفيذها.

مثال

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

• المنتج الذي تم بيعه إفترضنا أنه يملك id يساوي 33.

• الشخص الذي قام بشراء المنتج إفترضنا أنه يملك يملك id يساوي 1.

إذا تمت عملية البيع بنجاح, يجب إنقاص كمية المنتج الذي تم بيعه في جدول المنتجات products واحداً.
أيضاً, يجب إضافة id الشخص الذي قام بالشراء و id المنتج الذي تم بيعه في جدول الطلبيات orders.

بما أنه يجب تنفيذ العمليتين سويةً أو عدم تنفيذهما من الأساس في حال حدث خطأ ما, سنقوم بوضع العملية بداخل Transaction.
في النهاية, بما أننا سنقوم بإلغاء تنفيذ العمليتين في النهاية, سنستدعي الأمر ROLLBACK; بدل استدعاء الأمر COMMIT;.

الإستعلام

        -- Transaction هنا قمنا ببدء
        BEGIN;

        -- يساوي 33 بهدف إنقاصها واحداً id للمنتج الذي يملك available_quantity هنا إفترضنا أننا نريد تحديث قيمة الحقل
        UPDATE products SET available_quantity = 9 WHERE id = 33;

        -- المنتج في جدول الطلبيات id المشتري و id سيتم إضافة أن available_quantity إذا لم يحدث مشكلة عند محاولة إنقاص 1 من قيمة الحقل
        INSERT INTO orders (user_id, product_id) values (1, 33);

        -- INSERT و UPDATE للتراجع عن تنفيذ الأمرين ROLLBACK هنا قمنا باستدعاء الأمر
        ROLLBACK;
      

مفهوم الفهارس و أنواعها

أي كتاب ورقي تقرؤه تجد في بدايته أو في نهايته فهرس يساعدك على البحث فيه بشكل سريع. فمثلاً في أغلب كتب العلوم تجد في أول الكتاب فهرس يخبرك في أي صفحة يبدأ كل درس, و في القرآن الكريم أيضاً تجد في نهايته فهرس يمكنك من خلاله معرفة في أي صفحة تبدأ كل سورة بسهولة.

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

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

في قواعد البيانات يوجد نوعين أساسيين من الفهارس هما Clustered Indexes و Non Clustered Indexes و الإثنين فكرتهما تسهيل إيجاد المعلومات.

مفهوم الـ Clustered Indexes

الفهرس الذي من النوع Clustered يقوم بحفظ المعلومات من الأساس بشكل مرتب, فمثلاً عندما تقوم بتعيين أي عامود كمفتاح رئيسي ( Primary Key ) في الجدول, تقوم قاعدة البيانات بشكل تلقائي بإضافة فهرس له نوعه Clustered لهذا السبب كنا نجد id المستخدمين يتم إرجاعها بنفس الترتيب الذي تم فيه إضافتهم كالتالي.

id	username	first_name	last_name
1	ahmad	Ahmad	Eid
2	ramez	Ramez	Morad
3	hassan	Hassan	Mortada
4	saad	Saad	Alkassem
5	zaher	Zaher	Fahmi

مفهوم الـ Non Clustered Indexes

الفهرس الذي من النوع Non Clustered فكرته وضع فهرس خارجي لأي عامود في الجدول يتم فيه الإشارة إلى بيانات الجدول الأساسي بشكل مرتب.
للدقة أكثر, الفهرس الخارجي يقوم بالإشارة إلى عنوان السطر في القرص الصلب ( Physical Address in Hard Drive ) الذي يحتوي على البيانات في الجدول.

كمثال بسيط, إذا أردنا وضع فهرس Non Clustered خاص للعامود username فهذا الفهرس سيحفظ قيم هذا العامود بشكل أبجدي مرتب و سيضع عنوان كل سطر في القرص الصلب تم منه إحضار القيم.
الطريقة التي يتم ترتيب القيم فيها لا يمكنك رؤيتها بعينك في قواعد البيانات و لكن يمكنك تخيلها كالتالي.

username	address
ahmad	AB123FD123142
hassan	AB87393F83AD4
ramez	F83AB8D47393A
saad	80DDC62384AFB
zaher	BC01234ABDFE7

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

متى يجب إضافة فهرس؟

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

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

هل إضافة فهرس لها تأثير سلبي على الأداء؟

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

طريقة إضافة فهرس

لإضافة فهرس جديد سواء لعامود واحد أو لأكثر من عامود في ذات الوقت, نستخدم الأمر CREATE INDEX لفعل ذلك.

الشكل العام لإضافة فهرس

      CREATE INDEX index_name
      ON table_name (colums); 
    

• مكان الكلمة index_name نضع الإسم الذي نريد وضعه للفهرس.

• مكان الكلمة table_name نضع إسم الجدول الذي سنضع الفهرس لأعمدته.

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

المثال الأول

الإستعلام التالي ينشئ فهرس إسمه idx_username خاص للعامود username الموجود في الجدول users.

الإستعلام

        CREATE INDEX idx_username
        ON users (username); 
      

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

الإستعلام التالي ينشئ فهرس إسمه idx_full_name خاص للعامودين first_name و last_name الموجودين في الجدول users.

الإستعلام

        CREATE INDEX idx_full_name
        ON users (first_name, last_name); 
      

طريقة إضافة فهرس يحتوي على قيم موحدة

لجعل العامود يقبل قيم موحدة يوجد ثلاث طرق يمكنك إتباع أحدها:

• جعل نوع العامود من الأساس UNIQUE.

• إضافة CONSTRAINT للعامود نوعه UNIQUE.

• إضافة INDEX للعامود نوعه UNIQUE و هذا ما سنتعلمه الآن.

لإضافة فهرس جديد سواء لعامود واحد أو لأكثر من عامود في ذات الوقت و جعلهم يقبلوا قيم موحدة فقط, نستخدم الأمر CREATE UNIQUE INDEX لفعل ذلك.

الشكل العام لإضافة فهرس يحتوي على قيم موحدة فقط

      CREATE UNIQUE INDEX index_name
      ON table_name (colums); 
    

• مكان الكلمة index_name نضع الإسم الذي نريد وضعه للفهرس.

• مكان الكلمة table_name نضع إسم الجدول الذي سنضع الفهرس لأعمدته.

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

المثال الأول

الإستعلام التالي ينشئ فهرس قيمه موحدة, إسمه idx_username و هو خاص للعامود username الموجود في الجدول users.

الإستعلام

        CREATE UNIQUE INDEX idx_username
        ON users (username); 
      

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

الإستعلام التالي ينشئ فهرس قيمه موحدة, إسمه idx_full_name و هو خاص للعامودين first_name و last_name الموجودين في الجدول users.

الإستعلام

        CREATE UNIQUE INDEX idx_full_name
        ON users (first_name, last_name); 
      

طريقة حذف الفهرس

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

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

      ALTER TABLE table_name
      DROP INDEX index_name;
    

الإستعلام التالي يقوم بإلغاء قيد إسمه idx_username.

مثال

        ALTER TABLE users
        DROP INDEX idx_username; 
      

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

      DROP INDEX table_name.index_name; 
    

الإستعلام التالي يقوم بإلغاء قيد إسمه idx_username.

مثال

        DROP INDEX users.idx_username; 
      

في قواعد بيانات Oracle / SQLite

      DROP INDEX index_name;
    

الإستعلام التالي يقوم بإلغاء قيد إسمه idx_username.

مثال

        DROP INDEX idx_username;
      

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

      DROP INDEX index_name ON table_name;
    

الإستعلام التالي يقوم بإلغاء قيد إسمه idx_username.

مثال

        DROP INDEX idx_username ON users;