الجزاء الخامس SQL بناء قواعد بيانات لمشاريع

معرفة المعلومات الأساسية التي يجب تخزينها في المشروع

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

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

• هل ستكون الشخص الوحيد الذي يمكنه كتابة المقالات؟

• هل تريد أن تكون مسؤول عن تعيين المدونيين و الموافقة على المقالات التي سينشروها؟

• هل تريد جعل زوار الموقع قادرين على وضع تعليقات على المقالات؟

• هل تريد إجبار الزوار على تسجيل الدخول حتى يتمكنوا من وضع التعليقات؟

• هل تريد تريد جعل الشخص المسجل في الموقع يصله رسالة على بريده الإلكتروني عندما تنشر مقالات جديدة؟

• هل سيكون المستخدم قادر على وضع صورة شخصية؟

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

• ما هي الطرق التي تريد إعمتادها لجعل المستخدم يسترجع حسابه في حال نسي كلمة مروره؟

• هل سيكون المستخدم قادر على تسجيل الدخول بواسطة حسابه في فيسبوك أو تويتر؟

يمكنك أن تطرح عليه عدد غير محدد من الأسئلة و من بعدها يمكنك البدء بوضع خطة لبناء قاعدة البيانات الخاصة بهذه المدونة.

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

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

لنفترض أن العميل أراد حفظ المعلومات التالية في المدونة:

• معلومات كل مستخدم (إسمه, إسم المستخدم الخاص به, كلمة المرور, بريده الإلكتروني, تاريخ ميلاده, رقم هاتفه, جنسه, صورته, إسم بلده, دوره في المدونة, تعليقاته).

• معلومات كل مقال (عنوانه, محتواه, تصنيفه, إسم الكاتب, تاريخ نشره, هل يسمح بوضع تعليقات عليه أم لا).

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

1- إسم الشخص

إسم الشخص يتألف في العادة من إسم الشخص و إسم عائلته.
لذا إسم الشخص سيتم وضعه في عامودين كالتالي:

• العامود الأول إسمه first_name و نوعه VARCHAR.

• العامود الأول إسمه last_name و نوعه VARCHAR.

ملاحظة: لو أراد العميل تخزين إسم الأب أيضاً, يمكن إضافة عامود ثالث إسمه father_name و نوعه VARCHAR أيضاً.

2- إسم المستخدم

إسم المستخدم username الخاص بكل شخص يجب أن يكون نوعه VARCHAR و UNIQUE لأنه لا يجب وجود أكثر من شخص عندهم نفس إسم المستخدم.

3- كلمة المرور

كلمة المرور أو كلمة السر password الخاصة بكل شخص يمكن حفظها بعدة أشكال مع الإشارة إلى أننا دائماً نحفظها بشكل مشفر حتى لا يتمكن أي أحد من معرفتها.
على حسب نوع التشفير الذي تختاره في مشروعك تقوم بتحديد خصائص العامود الذي ستحفظ فيه كلمات المرور المشفرة.
إذا افترضنا أننا سنعتمد على تشفير إسمه MD5 فهنا سيكون عدد أحرف كل كلمة مرور مشفرة هو 32 حرف بالضبط مهما كان حجم كلمة المرور الأصلية.
لذلك سنقوم بجعل نوع العامود CHAR و تحديد أنه يتألف من 32 حرف بالضبط لأن حقوله دائماً ستحتوي على هذا العدد من الأحرف.

4- تاريخ ميلاد الشخص

تاريخ ميلاد الشخص birthdate يجب أن يكون نوعه DATE.

5- رقم هاتف الشخص

رقم هاتف الشخص phone يمكن جعل نوعه INT و لكن هل نحن بحاجة لأن يكون كذلك؟
الجواب هو كلا, لأننا لن نجري عليه أي عملية خاصة بالأرقام و نريد أن يتم حفظه كما تم إدخاله بالضبط و هذا الأمر مستحيل إن كان نوع العامود INT.
فمثلاً, إن كان رقم هاتف المستخدم قد يتضمن رموز مثل +961711731343 و هذا الأمر ممنوع في حال كان نوع العامود INT.
أيضاً قد يتضمن اصفاراً من الناحية اليسرى مثل 00961711731343 و هذا الأمر ممنوع كذلك في حال كان نوع العامود INT.

لهذا السبب العامود phone يجب أن يكون نوعه VARCHAR لأنه سيضمن حفظ الرقم كما تم إدخاله.

6- جنس الشخص

جنس الشخص يمكنك أن تفعله بطريقتين على حسب حاجتك:

يمكنك تسمية العامود الخاص بجنس الشخص is_male و تجعل نوعه BOOLEAN و عندها إذا كان المستخدم ذكر تخزن القيمة True و إذا كان المستخدم أنثى تخزن القيمة False.

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

7- صورة الشخص

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

في الجدول photos يجب أن تضع عامود خاص لتضع id الشخص الذي هو صاحب هذه الصورة حتى تربطها به.

8- بلد الشخص

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

9- دور الشخص

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

10- تعليقات الشخص

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

12- عنوان المقال

عنوان المقال title يجب أن يكون نوعه VARCHAR.

13- محتوى المقال

محتوى المقال content يجب أن يكون نوعه VARCHAR كبير جداً أو أي نوع نصي يمكن أن يتقبل أكبر عدد ممكن من الأحرف.

13- تصنيف المقال

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

14- تاريخ نشر المقال

تاريخ النشر publishing_date يجب أن يكون نوعه DATE.

15- إمكانية التعليق أم لا

يمكنك تسمية العامود الخاص بالسماح بوضع تعليقات أم لا are_comments_enabled و تجعل نوعه BOOLEAN و عندها إذا كان يسمح بوضع تعليقات تخزن القيمة True و إذا كان لا يسمح تخزن القيمة False.

مرحلة تصميم و إنشاء قاعدة البيانات

الشكل العام للجداول التي سننشئها و التي سنربطها ببعضها سيكون كالتالي.

الآن سنقوم بكتابة الأوامر التي ستنشئ لنا الجداول بالإضافة إلى تعيين المفاتيح الرئيسية و الأجنبية.
ملاحظة: المفاتيح الأجنبية ( Foreign Keys ), الفهارس ( Indexes ) و القيود ( Constraints ) قمنا بتعريفها بعد إنشاء جميع الجداول.

الإستعلام

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

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

-- و بالتالي أي شيء ننشئه سيتم إنشاؤه فيها blog هنا قمنا بتحديد أن أي إستعلام جديد سيتم تنفيذه على قاعدة البيانات
USE blog;

-- يتألف من عامودين genders هنا قمنا بإنشاء جدول جديد إسمه
CREATE TABLE genders (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(20)
);

-- يتألف من عامودين roles هنا قمنا بإنشاء جدول جديد إسمه
CREATE TABLE roles (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(20)
);

-- يتألف من عامودين categories هنا قمنا بإنشاء جدول جديد إسمه
CREATE TABLE categories (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255)
);

-- يتألف من 6 أعمدة photos هنا قمنا بإنشاء جدول جديد إسمه
CREATE TABLE photos (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    url VARCHAR(2000),
	physical_path VARCHAR(255),
	size_KB INT,
	user_id INT
);

-- يتألف من 9 أعمدة users هنا قمنا بإنشاء جدول جديد إسمه
CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
	password CHAR(32),
	email VARCHAR(255),
	first_name VARCHAR(100),
	last_name VARCHAR(100),
    birthdate DATE,
	phone VARCHAR(20),
	gender_id INT,
	role_id INT
);

-- يتألف من 8 أعمدة posts هنا قمنا بإنشاء جدول جديد إسمه
CREATE TABLE posts (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    url VARCHAR(2000),
	title VARCHAR(255),
	content VARCHAR(21844), -- MySQL الرقم 21844 هو أقصى حد ممكن في قواعد بيانات
	publishing_date DATE,
    are_comments_enabled BOOLEAN,
	user_id INT,
	category_id INT
);

-- يتألف من 6 أعمدة comments هنا قمنا بإنشاء جدول جديد إسمه
CREATE TABLE comments (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	content VARCHAR(2000),
	publishing_date DATE,
    parent_comment_id INT,
	user_id INT,
	post_id INT
);

-- genders و users هو بمثابة مفتاح أجنبي بين الجدولين gender_id للإشارة إلى أن العامود fk_users_genders هنا قمنا بوضع قيد إسمه
ALTER TABLE users
ADD CONSTRAINT fk_users_genders
FOREIGN KEY (gender_id) REFERENCES genders(id);

-- roles و users هو بمثابة مفتاح أجنبي بين الجدولين role_id للإشارة إلى أن العامود fk_users_roles هنا قمنا بوضع قيد إسمه
ALTER TABLE users
ADD CONSTRAINT fk_users_roles
FOREIGN KEY (role_id) REFERENCES roles(id);

-- users و photos هو بمثابة مفتاح أجنبي بين الجدولين user_id للإشارة إلى أن العامود fk_photos_users هنا قمنا بوضع قيد إسمه
ALTER TABLE photos
ADD CONSTRAINT fk_photos_users
FOREIGN KEY (user_id) REFERENCES users(id);

-- users و posts هو بمثابة مفتاح أجنبي بين الجدولين user_id للإشارة إلى أن العامود fk_posts_users هنا قمنا بوضع قيد إسمه
ALTER TABLE posts
ADD CONSTRAINT fk_posts_users
FOREIGN KEY (user_id) REFERENCES users(id);

-- categories و posts هو بمثابة مفتاح أجنبي بين الجدولين category_id للإشارة إلى أن العامود fk_posts_categories هنا قمنا بوضع قيد إسمه
ALTER TABLE posts
ADD CONSTRAINT fk_posts_categories
FOREIGN KEY (category_id) REFERENCES categories(id);

-- users و comments هو بمثابة مفتاح أجنبي بين الجدولين user_id للإشارة إلى أن العامود fk_comments_users هنا قمنا بوضع قيد إسمه
ALTER TABLE comments
ADD CONSTRAINT fk_comments_users
FOREIGN KEY (user_id) REFERENCES users(id);

-- posts و comments هو بمثابة مفتاح أجنبي بين الجدولين post_id للإشارة إلى أن العامود fk_comments_posts هنا قمنا بوضع قيد إسمه
ALTER TABLE comments
ADD CONSTRAINT fk_comments_posts
FOREIGN KEY (post_id) REFERENCES posts(id);

-- comments الموجود معه في الجدول id هو بمثابة مفتاح أجنبي بالنسبة للعامود parent_comment_id للإشارة إلى أن العامود fk_comments_comments هنا قمنا بوضع قيد إسمه
ALTER TABLE comments
ADD CONSTRAINT fk_comments_comments
FOREIGN KEY (parent_comment_id) REFERENCES comments(id);

-- يجب أن تكون موحدة users الموجود في الجدول username للإشارة إلى أن قيم العامود uidx_users_username هنا قمنا بوضع قيد إسمه
CREATE UNIQUE INDEX uidx_users_username
ON users (username);

-- يجب أن تكون موحدة users الموجود في الجدول email للإشارة إلى أن قيم العامود uidx_users_email هنا قمنا بوضع قيد إسمه
CREATE UNIQUE INDEX uidx_users_email
ON users (email); 

-- يجب أن تكون موحدة photos الموجود في الجدول url للإشارة إلى أن قيم العامود uidx_photos_url هنا قمنا بوضع قيد إسمه
CREATE UNIQUE INDEX uidx_photos_url
ON photos (url); 

-- يجب أن تكون موحدة photos الموجود في الجدول physical_path للإشارة إلى أن قيم العامود uidx_photos_physical_path هنا قمنا بوضع قيد إسمه
CREATE UNIQUE INDEX uidx_photos_physical_path
ON photos (physical_path); 

-- يجب أن تكون موحدة posts الموجود في الجدول url للإشارة إلى أن قيم العامود uidx_posts_url هنا قمنا بوضع قيد إسمه
CREATE UNIQUE INDEX uidx_posts_url
ON posts (url); 
		

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

مرحلة إدخال البيانات في قاعدة البيانات

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

بالنسبة لإدخال معلومات مستخدم جديد, فإن أي مستخدم سيتم إضافته يجب أن يختار رقم الجنس gender_id من الجدول genders و رقم الدور role_id من الجدول roles لذلك يجب إدخال معلومات هذين الجدولين قبل محاولة إدخال معلومات أي مستخدم.

بالنسبة للمقالات, فإن أي مقال نضيفه يجب أن يكون مرتبط برقم id مستخدم محدد من الجدول users (كاتب المقال) و رقم id تصنيفه من الجدول categories (التصنيف الذي ينتمي إليه المقال). لذلك وجود المستخدم و تصنيفات المقال أساسي حتى تتمكن من إضافة مقال جديد مرتبط بهم.

بالنسبة للتعليقات, فإن أي تعليق نضيفه يجب أن يكون مرتبط برقم id مستخدم محدد من الجدول users (صاحب التعليق) و رقم id مقال محدد من الجدول posts (المقال الذي يوضع التعليق عليه). لذلك وجود المستخدم و المقال أساسي حتى تتمكن من إضافة تعليق جديد مرتبط بهم.

بالنسبة للصور, بما أن أي صورة تضيفها يجب أن تكون مربطة برقم id مستخدم محدد من الجدول users (صاحب الصورة) فهذا يعني أنه يجب إضافة مستخدم قبل إضافة صورة له و هكذا.

خلاصة

• يجب إدخال معلومات الجداول genders و roles و categories في البداية لأن الجداول الأخرى تعتمد عليهم.

• بعدها يجب إدخال معلومات الجدول users لأنه يصبح بإمكانك إضافة مستخدمين.

• بعدها يمكنك إذا شئت إدخال معلومات الجدول photos لأنه يصبح بإمكانك إضافة صور للمستخدمين.

• بعدها يمكنك إذا شئت إدخال مقالات في الجدول posts لأنه يصبح بإمكانك إضافة مقال.

• بعدها يمكنك إذا شئت إدخال تعليقات في الجدول comments لأنه يصبح بإمكانك إضافة تعليقات.

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

الإستعلام

-- و بالتالي أي شيء ننشئه سيتم إنشاؤه فيها blog هنا قمنا بتحديد أن أي إستعلام جديد سيتم تنفيذه على قاعدة البيانات
USE blog;

-- genders هنا قمنا بإضافة سطرين في الجدول
INSERT INTO genders VALUES (null, 'Male');
INSERT INTO genders VALUES (null, 'Female');

-- roles هنا قمنا بإضافة 4 أسطر في الجدول
INSERT INTO roles VALUES (null, 'Administrator');
INSERT INTO roles VALUES (null, 'Editor');
INSERT INTO roles VALUES (null, 'Writer');
INSERT INTO roles VALUES (null, 'Subscriber');

-- categories هنا قمنا بإضافة 5 أسطر في الجدول
INSERT INTO categories VALUES (null, 'Programming');
INSERT INTO categories VALUES (null, 'Operating Systems');
INSERT INTO categories VALUES (null, 'Networking');
INSERT INTO categories VALUES (null, 'Electronics');
INSERT INTO categories VALUES (null, 'Artificial Intelligence');

-- users هنا قمنا بإضافة 10 أسطر في الجدول
-- 'pass1234' ملاحظة: كلمة مرور جميع المستخدمين وضعناها 
INSERT INTO users VALUES (null, 'mhamad', 'pass1234', 'mhamad@example.com', 'Mhamad', 'Harmush', '1994-05-12', '+96101708089', 1, 1);
INSERT INTO users VALUES (null, 'ahmad', 'pass1234', 'ahmad@exmaple.com', 'Ahmad', 'Masri', '1998-08-18', '+962358645235', 1, 2);
INSERT INTO users VALUES (null, 'hala', 'pass1234', 'hala@exmaple.com', 'Hala', 'Hassan', '1998-01-16', '+96370348927', 2, 4);
INSERT INTO users VALUES (null, 'rola', 'pass1234', 'rola@exmaple.com', 'Rola', 'Senjekdar', '2000-01-01', '+96325684752', 2, 4);
INSERT INTO users VALUES (null, 'ziad', 'pass1234', 'ziad@exmaple.com', 'Ziad', 'Asmar', '1992-05-2', '+966087046489', 1, 3);
INSERT INTO users VALUES (null, 'mostafa', 'pass1234', 'mostafa@exmaple.com', 'Mostafa', 'Kamel', '1988-02-12', '+97305654975', 1, 2);
INSERT INTO users VALUES (null, 'saly', 'pass1234', 'saly@exmaple.com', 'Saly', 'Saadi', '2001-11-17', '+94856215578', 2, 3);
INSERT INTO users VALUES (null, 'shahad', 'pass1234', 'shahad@exmaple.com', 'Shahad', 'Alanzy', '1995-08-08', '+985865862569', 2, 4);
INSERT INTO users VALUES (null, 'hazem', 'pass1234', 'hazem@exmaple.com', 'Hazem', 'Hassoun', '1997-06-17', '+964258258855', 1, 4);
INSERT INTO users VALUES (null, 'rana', 'pass1234', 'rana@exmaple.com', 'Rana', 'Karim', '1989-11-01', '+98215482365', 2, 4);

-- photos هنا قمنا بإضافة 6 أسطر في الجدول
INSERT INTO photos VALUES (null, 'https://harmash.com/uploaded/photos/6546842.PNG', 'uploaded/photos/6546842.PNG', 1027, 1);
INSERT INTO photos VALUES (null, 'https://harmash.com/uploaded/photos/5324654.PNG', 'uploaded/photos/5324654.PNG', 2088, 2);
INSERT INTO photos VALUES (null, 'https://harmash.com/uploaded/photos/5249824.PNG', 'uploaded/photos/5249824.PNG', 1512, 4);
INSERT INTO photos VALUES (null, 'https://harmash.com/uploaded/photos/4578515.PNG', 'uploaded/photos/4578515.PNG', 1065, 5);
INSERT INTO photos VALUES (null, 'https://harmash.com/uploaded/photos/8789354.PNG', 'uploaded/photos/8789354.PNG', 2005, 8);
INSERT INTO photos VALUES (null, 'https://harmash.com/uploaded/photos/5878942.PNG', 'uploaded/photos/5878942.PNG', 1687, 9);

-- posts هنا قمنا بإضافة سطرين في الجدول
INSERT INTO posts VALUES (null, 'https://harmash.com/java/java-overview', 'Java Overview', 'Java is a popular programming language, created in 1995. It is owned by Oracle, and more than 3 billion devices run Java.', '2020-03-01', true, 5, 1);
INSERT INTO posts VALUES (null, 'https://harmash.com/linux/linux-file-system', 'Linux File System', 'The Filesystem Hierarchy Standard (FHS) defines the structure of file systems on Linux and other UNIX-like Operating Systemss. However, Linux file systems also contain some directories that aren’t yet defined by the standard.', '2020-03-03', true, 1, 2);

-- comments هنا قمنا بإضافة 4 أسطر في الجدول
INSERT INTO comments VALUES (null, 'Great introduction!', '2020-03-01', null, 3, 1);
INSERT INTO comments VALUES (null, 'Wow, this is very helpful.', '2020-03-02', null, 4, 1);
INSERT INTO comments VALUES (null, 'How to List Users in Linux?', '2020-03-03', null, 9, 2);
INSERT INTO comments VALUES (null, 'Execute the following code: cat /etc/passwd', '2020-03-04', 3, 1, 2);
		

بعد تنفيذ الإستعلام السابق سيتم تعبئة القيم في الجداول السبعة كالتالي.

الجدول genders

id	title
1	Male
2	Female

الجدول roles

id	title
1	Administrator
2	Editor
3	Writer
4	Subscriber

الجدول categories

id	title
1	Programming
2	Operating Systems
3	Networking
4	Electronics
5	Artificial Intelligence

الجدول users

id	username	password	email	first_name	last_name	birthdate	phone	gender_id	role_id
1	mhamad	pass1234	mhamad@example.com	Mhamad	Harmush	1994-05-12	+96101708089	1	1
2	ahmad	pass1234	ahmad@exmaple.com	Ahmad	Masri	1998-08-18	+962358645235	1	2
3	hala	pass1234	hala@exmaple.com	Hala	Hassan	1998-01-16	+96370348927	2	4
4	rola	pass1234	rola@exmaple.com	Rola	Senjekdar	2000-01-01	+96325684752	2	4
5	ziad	pass1234	ziad@exmaple.com	Ziad	Asmar	1992-05-2	+966087046489	1	3
6	mostafa	pass1234	mostafa@exmaple.com	Mostafa	Kamel	1988-02-12	+97305654975	1	2
7	saly	pass1234	saly@exmaple.com	Saly	Saadi	2001-11-17	+94856215578	2	3
8	shahad	pass1234	shahad@exmaple.com	Shahad	Alanzy	1995-08-08	+985865862569	2	4
9	hazem	pass1234	hazem@exmaple.com	Hazem	Hassoun	1997-06-17	+964258258855	1	4
10	rana	pass1234	rana@exmaple.com	Rana	Karim	1989-11-01	+98215482365	2	4

ملاحظة

كلمة السر pass1234 لو كنا سنشفرها بخوارزمية MD5 لكنت رأيتها في قاعدة البيانات محفوظة هكذا b4af804009cb036a4ccdc33431ef9ac9.
في هذا الدرس لم نركز إطلاقاً على التشفير لأن هذا موضوع آخر ليس له علاقة بمبادئ قواعد البيانات بل هو أمر مرتبط بشكل أساسي بالبرمجة و الشبكات.

الجدول photos

id	url	physical_path	size_KB	user_id
1	https://harmash.com/uploaded/photos/6546842.PNG	uploaded/photos/6546842.PNG	1027	1
2	https://harmash.com/uploaded/photos/5324654.PNG	uploaded/photos/5324654.PNG	2088	2
3	https://harmash.com/uploaded/photos/5249824.PNG	uploaded/photos/5249824.PNG	1512	4
4	https://harmash.com/uploaded/photos/4578515.PNG	uploaded/photos/4578515.PNG	1065	5
5	https://harmash.com/uploaded/photos/8789354.PNG	uploaded/photos/8789354.PNG	2005	8
6	https://harmash.com/uploaded/photos/5878942.PNG	uploaded/photos/5878942.PNG	1687	9

الجدول posts

id	url	title	content	publishing_date	are_comments_enabled	user_id	category_id
1	https://harmash.com/java/java-overview	Java Overview	Java is a popular programming language, created in...	2020-03-01	true	5	1
2	https://harmash.com/linux/linux-file-system	Linux File System	The Filesystem Hierarchy Standard (FHS) defines th...	2020-03-03	true	1	2

الجدول comments

id	content	publishing_date	parent_comment_id	user_id	post_id
1	Great introduction!	2020-03-01	NULL	3	1
2	Wow, this is very helpful.	2020-03-02	NULL	4	1
3	How to List Users in Linux?	2020-03-03	NULL	9	2
4	Execute the following code: cat /etc/passwd	2020-03-04	3	1	2

أمثلة شاملة حول استرجاع البيانات

المثال الأول

الإستعلام التالي يقوم بعرض email الشخص الذي يعتبر مدير المدونة ( Administrator ).

الإستعلام

إذا كنت تعرف أن المدير هو الشخص الذي يملك role_id يساوي 1 يمكنك مباشرةً كتابة التالي.

SELECT email
FROM users
WHERE role_id = 1;
		

إذا أردت الإعتماد على كلمة Administrator لمعرفة الشخص الذي يعتبر المدير, يجب أن تكتب التالي.

SELECT email
FROM users
JOIN roles
ON users.role_id = roles.id
WHERE roles.title = 'Administrator'
		

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

email
mhamad@example.com

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

الإستعلام التالي يقوم بعرض إسم مستخدم ( username ) كل شخص عنده مقال واحد على الأقل مع عرض عنوان ( title ) كل مقال.

لتقليل حجم الكود, قمنا بوضع الحرف u كإسم مختصر للجدول users, و الحرف p كإسم مختصر للجدول posts.
جعلنا نوع الربط JOIN للإشارة إلى أننا نريد عرض الأشخاص الذين عندهم مقالات فقط.

الإستعلام

SELECT u.username, p.title AS post_title
FROM users u
JOIN posts p
ON p.user_id = u.id
		

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

username	post_title
ziad	Java Overview
mhamad	Linux File System

المثال الثالث

الإستعلام التالي يقوم بإحصاء و عرض عدد الأشخاص ( users ) على حسب أدوارهم ( roles ), أي المعلومات التالية:

• كم شخص عبارة عن Administrator.

• كم شخص عبارة عن Editor.

• كم شخص عبارة عن Writer.

• كم شخص عبارة عن Subscriber.

لتقليل حجم الكود, قمنا بوضع الحرف r كإسم مختصر للجدول roles, و الحرف u كإسم مختصر للجدول users.
لحساب عدد المستخدمين, إستخدمنا الدالة COUNT().
وضعنا إسم الجدول roles من الناحية اليسرى و جعلنا نوع الربط LEFT JOIN للإشارة إلى أننا نريد عرض كل الأدوار حتى و لو لم يكن هناك أي شخص له دور محدد منها.
بما أن الإحصاء سيرتكز على إحصاء عدد الأدوار نسبةً لدور كل مستخدم, قمنا بتجميع الأدوار نسبة لعناوين ( title ) الأدوار.

الإستعلام

SELECT r.title AS roles, COUNT(u.role_id) AS users
FROM roles r LEFT JOIN users u
ON u.role_id = r.id
GROUP BY r.title;
		

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

roles	users
Administrator	1
Editor	2
Subscriber	5
Writer	2

المثال الرابع

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

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

• الجدول users وضعنا الحرف u كإسم مختصر له.

• الجدول genders وضعنا الحرف g كإسم مختصر له.

• الجدول roles وضعنا الحرف r كإسم مختصر له.

لدمج إسم الشخص مع إسم عائلته في عامود واحد سنستخدم الدالة CONCAT().
للحصول على مسمى جنس المستخدم في المدونة يجب أن نربط الحقل gender_id الموجود في الجدول u مع الحقل id الموجود في الجدول g.
للحصول على مسمى دور المستخدم في المدونة يجب أن نربط الحقل role_id الموجود في الجدول u مع الحقل id الموجود في الجدول r.

الإستعلام

SELECT
	CONCAT(u.first_name, ' ', u.last_name) AS name,
    g.title AS gender,
    r.title AS role
FROM
	users u
JOIN
	genders g ON u.gender_id = g.id
JOIN
	roles r ON u.role_id = r.id
ORDER BY
	role;
		

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

name	gender	role
Mhamad Harmush	Male	Administrator
Mostafa Kamel	Male	Editor
Ahmad Masri	Male	Editor
Hazem Hassoun	Male	Subscriber
Rana Karim	Female	Subscriber
Shahad Alanzy	Female	Subscriber
Rola Senjekdar	Female	Subscriber
Hala Hassan	Female	Subscriber
Saly Saadi	Female	Writer
Ziad Asmar	Male	Writer

المثال الخامس

الإستعلام التالي يقوم بوضع إسم كل مستخدم ( username ) في الجدول users بالإضافة إلى رابط صورته ( url ) الموضوع في الجدول photos.
في حال كان الشخص لا يملك صورة في الجدول photos سيتم عرض جملة No Photo في النتيجة النهائية.

لتقليل حجم الكود, قمنا بوضع الحرف u كإسم مختصر للجدول users, و الحرف p كإسم مختصر للجدول photos.
لتبديل أي قيمة Null في العامود photo بالجملة No Photo إستخدمنا الدالة IFNULL().
وضعنا إسم الجدول users من الناحية اليسرى و جعلنا نوع الربط LEFT JOIN للإشارة إلى أننا نريد عرض كل أسماء المستخدمين حتى و لو لم يكن هناك أي صورة لهم في الجدول photos.

الإستعلام

SELECT u.username, IFNULL(p.url, 'No Photo') AS photo
FROM users u
LEFT JOIN photos p
ON p.user_id = u.id;
		

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

username	photo
mhamad	https://harmash.com/uploaded/photos/6546842.PNG
ahmad	https://harmash.com/uploaded/photos/5324654.PNG
hala	No Photo
rola	https://harmash.com/uploaded/photos/5249824.PNG
ziad	https://harmash.com/uploaded/photos/4578515.PNG
mostafa	No Photo
saly	No Photo
shahad	https://harmash.com/uploaded/photos/8789354.PNG
hazem	https://harmash.com/uploaded/photos/5878942.PNG
rana	No Photo

المثال السادس

الإستعلام التالي يقوم بعرض إسم مستخدم كل شخص كتب تعليق, بالإضافة إلى التعليق الذي وضعه, و عنوان المقال الذي علق عليه.

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

• الجدول comments وضعنا الحرف c كإسم مختصر له.

• الجدول users وضعنا الحرف u كإسم مختصر له.

• الجدول posts وضعنا الحرف p كإسم مختصر له.

للحصول على إسم المستخدم الذي وضع التعليق يجب أن نربط الحقل user_id الموجود في الجدول c مع الحقل id الموجود في الجدول u.
للحصول على عنوان المقال الذي تم وضع التعليق عليه يجب أن نربط الحقل post_id الموجود في الجدول c مع الحقل id الموجود في الجدول p.

الإستعلام

SELECT u.username, c.content AS comment, p.title AS on_post
FROM comments c
JOIN users u ON c.user_id = u.id
JOIN posts p ON c.post_id = p.id;
		

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

username	comment	on_post
hala	Great introduction!	Java Overview
rola	Wow, this is very helpful.	Java Overview
hazem	How to List Users in Linux?	Linux File System
mhamad	Execute the following code: cat /etc/passwd	Linux File System

المثال السابع

الإستعلام التالي يقوم بإحصاء و عرض عدد المقالات ( posts ) الموجودة في كل فئة ( categories ), أي المعلومات التالية:

• عدد المقالات التابعة لفئة Programming.

• عدد المقالات التابعة لفئة Operating Systems.

• عدد المقالات التابعة لفئة Networking.

• عدد المقالات التابعة لفئة Electronics.

• عدد المقالات التابعة لفئة Artificial Intelligence.

لتقليل حجم الكود, قمنا بوضع الحرف p كإسم مختصر للجدول posts, و الحرف c كإسم مختصر للجدول categories.
لحساب عدد المقالات, إستخدمنا الدالة COUNT().
وضعنا إسم الجدول categories من الناحية اليسرى و جعلنا نوع الربط LEFT JOIN للإشارة إلى أننا نريد عرض كل الفئات حتى و لو لم يكن هناك أي مقال تابع لها.
بما أن الإحصاء سيرتكز على إحصاء عدد المقالات نسبةً للفئة كل مقال, قمنا بتجميع المقالات نسبة لعناوين ( title ) الفئات.

الإستعلام

SELECT c.title AS categories, COUNT(p.category_id) AS posts
FROM categories c LEFT JOIN posts p
ON p.category_id = c.id
GROUP BY c.title;
		

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

categories	posts
Artificial Intelligence	0
Electronics	0
Networking	0
Operating Systems	1
Programming	1

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

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

بما أن التعليقات و الردود عبارة عن تعليقات عادية موضوعة في الجدول comments مع فارق واحد هو أن التعليق يعتبر بمثابة رد في حال كان عنده parent_comment_id يشير لتعليق آخر موجود في الجدول نفسه يجب أن نحول الجدول إلى جدولين و نربطهم بأسلوب Self Join لنتمكن من عرض التعليق و الرد الموضوع عليه.

أول نسخة من الجدول comments قمنا بتسميتها c1 و هي التي سنحضر منها التعليق, و النسخة الثانية منه قمنا بتسميتها c2 و هي التي سنحضر منها الرد على التعليق.

الإستعلام

SELECT c1.content AS comment, c2.content AS reply
FROM comments c1
JOIN comments c2
ON c2.parent_comment_id = c1.id;
		

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

comment	reply
How to List Users in Linux?	Execute the following code: cat /etc/passwd

ما يجب معرفته قبل تحميل قواعد بيانات

عند البحث عن قواعد بيانات جاهزة, يفترض أن تبحث عن ملف نوعه sql يحتوي على إستعلامات جاهزة عند تنفيذها تقوم بإنشاء جداول مترابطة و فيها بيانات جاهزة.
إذاً قاعدة البيانات الجاهزة يقصد بها محتوى ( Tables - Constraints - Indexes إلخ.. ) يمكن إضافته كما هو في قاعدة بيانات.

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

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

قم بتنفيذ الإستعلام التالي حتى تنشئ قاعدة بيانات جديدة إسمها test.

الإستعلام

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

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

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

أين أجد قواعد بيانات جاهزة؟

في البداية, بما أننا نتعامل مع قواعد بيانات MySQL يجب أن تبحث عن قواعد بيانات مبنية لقواعد بيانات MySQL و ليس نوع آخر.

1. للحصول على قواعد بيانات جاهزة يمكنك الدخول للرابط التالي الذي يحتوي على أكثر من 100 مشروع فيهم قواعد بيانات جاهزة.

توجه للرابط »

2. بمجرد الدخول للرابط السابق سيظهر لك المشاريع التي فيها قواعد بيانات.

3. قم بالنقر على إسم أي مشروع تجد أن قاعدة البيانات الموجودة فيه مبنية بقواعد بيانات MySQL كما يظهر في المشروع التالي.

4. في هذا المشروع, قام صاحب المشروع بوضع قاعدة البيانات بداخل مجلد إسمه database حتى يساعدك في إيجادها لذا أنقر عليه حتي يفتح.
   ملاحظة: إذا دخلت لمشاريع أخرى قد تجد قاعدة البيانات موضوعة بشكل مباشر مع الملفات الخارجية و ستعرفها مباشرةً لأن إمتدادها سيكون sql كما أشرنا سابقاً.

5. لاحظ أن المبرمج قام بتسمية الملف الذي يحتوي على أوامر إنشاء قاعدة البيانات onlineshop.sql.

6. أنقر عليه حتى ترى الكود الذي بداخله.

7. الأوامر الموضوعة في الملف onlineshop.sql تظهر بشكل جميل و ملون و لكن قد تجد بعض الصعوبة في نسخها كما هي.

8. أنقر على الزر Raw حتى يتم فتح الملف onlineshop.sql في صفحة خاصة حتى تتمكن من نسخه بسهولة كالتالي.

9. محتوى الملف الملف onlineshop.sql سيظهر كالتالي, قم بنسخه كما هو.
   معلومة: لنسخ كل الكود بسرعة يمكنك النقر على ctrl + A لتحديد كله, ثم أنقر على ctrl + C لنسخ كل المحتوى الذي تم تحديده.

10. إرجع لصفحة phpMyAdmin و ضع النص الذي نسخته فيها و يمكنك النقر على ctrl + A لتفعل ذلك بشكل سريع.

11. أنقر على الزر Go حتى يتم تنفيذ الإستعلام.

مشاهدة محتوى قاعدة البيانات التي تم إضافتها

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

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

تعديل المقال