PostgreSQL: ممارسة أساسية شاملة في PostgreSQL
آخر تحديث: 2026-08-26
بعد إنهاء أول 6 دروس أساسية، حان الوقت▶�ربط كل شيء معًا في مشروع كامل — تأخذك هذه المقالة عبر بناء قاعدة بيانات متجر كتب إلكتروني من الصفر.
1. ما ستتعلمه
- التدفق الكامل من تحليل المتطلبات إلى تصميم الجداول
- إنشاء قاعدة بيانات وتبديل الاتصالات
- تصميم جداول متعددة مرتبطة بالقيود
- إدراج دفعة واستيراد بيانات UPSERT
- استعلامات بسيطة للتحقق من صحة البيانات
- تعديل هياكل الجداول والتنظيف
2. قصة حقيقية لمطور منفرد
(1) المشكلة: بناء قاعدة البيانات منفردًا
Alice هي مطورة منفردة تحتاج إلى إعداد قاعدة بيانات لمشروع متجر كتب إلكتروني. متطلباتها هي:
- 5 جداول أساسية: users, books, categories, orders, reviews
- يجب أن يكون البريد الإلكتروني للمستخدم فريدًا؛ كلمة المرور لا يجب أن تكون فارغة
- يجب أن يكون سعر الكتاب > 0؛ ISBN يجب أن يكون فريدًا
- الطلبات تربط المستخدمين والكتب
- يجب أن يكون للمراجعات تقييم (1–5)
- تحتاج إلى استيراد دفعة من البيانات الأولية؛ بعض الكتب قد تكون موجودة بالفعل (تتطلب UPSERT)
(2) تدفق بناء قاعدة البيانات الكامل
تكمل Alice تهيئة قاعدة البيانات من الصفر بهذه الخطوات:
-- الخطوة 1: إنشاء قاعدة بيانات
CREATE DATABASE bookstore;
-- الخطوة 2: الاتصال بقاعدة البيانات الجديدة
\c bookstore
-- الخطوات 3-7: إنشاء الجداول بالأنواع والقيود المناسبة
-- (SQL الكامل أدناه في القسم 6)
(3) النتيجة
- خبرة تصميم قاعدة بيانات كاملة (من المتطلبات إلى SQL)
- 5 جداول + قيود + تتاليات مفاتيح خارجية = تصميم بمستوى إنتاجي
- استيراد دفعة UPSERT = مهارة واقعية
- تم في 30 دقيقة = القدرة على التسليم منفردًا
3. تحليل المتطلبات
(1) مخطط ER لمتجر الكتب الإلكتروني
erDiagram
USERS ||--o{ ORDERS : يقدم
BOOKS ||--o{ ORDER_ITEMS : يحتوي
CATEGORIES ||--o{ BOOKS : لديه
USERS ||--o{ REVIEWS : يكتب
BOOKS ||--o{ REVIEWS : يتلقى
ORDERS ||--|| ORDER_ITEMS : يتضمن
USERS {
int id PK
varchar email UK
varchar name
varchar password_hash
timestamptz created_at
}
CATEGORIES {
int id PK
varchar name UK
text description
}
BOOKS {
int id PK
varchar isbn UK
varchar title
decimal price
int stock
int category_id FK
jsonb metadata
}
ORDERS {
int id PK
int user_id FK
varchar status
decimal total
timestamptz created_at
}
ORDER_ITEMS {
int id PK
int order_id FK
int book_id FK
int quantity
decimal unit_price
}
REVIEWS {
int id PK
int user_id FK
int book_id FK
smallint rating
text content
timestamptz created_at
}
(2) قائمة الجداول والقيود
| الجدول | الأعمدة | القيود الرئيسية |
|---|---|---|
| users | 5 | email UNIQUE NOT NULL, password_hash NOT NULL |
| categories | 3 | name UNIQUE |
| books | 7 | isbn UNIQUE, price > 0 CHECK, stock >= 0 CHECK, category_id FK |
| orders | 5 | user_id FK CASCADE, حالة CHECK, total >= 0 CHECK |
| order_items | 5 | order_id FK CASCADE, book_id FK RESTRICT, quantity > 0 CHECK |
| reviews | 6 | user_id FK SET NULL, book_id FK CASCADE, rating 1-5 CHECK |
4. ابنه خطوة بخطوة
(1) إنشاء قاعدة البيانات والاتصال
(1) ▶ مثال
-- إنشاء قاعدة بيانات متجر الكتب
CREATE DATABASE bookstore
WITH ENCODING = 'UTF8'
LC_COLLATE = 'en_US.utf8'
LC_CTYPE = 'en_US.utf8';
-- الاتصال بقاعدة البيانات الجديدة
\c bookstore
Output:
CREATE TABLE
(2) إنشاء جدول categories
(2) ▶ مثال
-- جدول الفئات: أنواع الكتب
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) UNIQUE NOT NULL,
description TEXT
);
-- إدراج الفئات الأولية
INSERT INTO categories (name, description) VALUES
('Programming', 'كتب عن تطوير البرمجيات ولغات البرمجة'),
('Database', 'تصميم قواعد البيانات و SQL وإدارة البيانات'),
('Data Science', 'تعلم الآلة والإحصاء وتحليل البيانات'),
('DevOps', 'CI/CD والحوسبة السحابية والبنية التحتية'),
('Web Development', 'تقنيات الويب للواجهة والخلفية');
Output:
INSERT 0 1
(3) إنشاء جدول users
(3) ▶ مثال
-- جدول المستخدمين: عملاء متجر الكتب
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL,
password_hash CHAR(60) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- إدراج مستخدمين عينة
INSERT INTO users (email, name, password_hash) VALUES
('alice@example.com', 'Alice', '$2a$12$hash_alice_1234567890abcdefghijklmnopqrs'),
('bob@example.com', 'Bob', '$2a$12$hash_bob_1234567890abcdefghijklmnopqrstuvwx'),
('charlie@example.com', 'Charlie', '$2a$12$hash_charlie_1234567890abcdefghijklmno')
RETURNING id, email, name;
Output:
INSERT 0 1
(4) إنشاء جدول books
(4) ▶ مثال
-- جدول الكتب: المنتج الأساسي
CREATE TABLE books (
id SERIAL PRIMARY KEY,
isbn VARCHAR(13) UNIQUE NOT NULL,
title VARCHAR(300) NOT NULL,
price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
stock INTEGER DEFAULT 0 CHECK (stock >= 0),
category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
metadata JSONB DEFAULT '{}'::jsonb,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- إدراج كتب عينة مع بيانات وصفية
INSERT INTO books (isbn, title, price, stock, category_id, metadata) VALUES
('9780134685991', 'Effective Python', 39.99, 120, 1,
'{"author": "Brett Slatkin", "pages": 352, "edition": 2}'::jsonb),
('9780596007270', 'Learning PostgreSQL', 44.99, 80, 2,
'{"author": "Regina Obe", "pages": 500}'::jsonb),
('9781491910368', 'Python Data Science Handbook', 49.99, 60, 3,
'{"author": "Jake VanderPlas", "pages": 548}'::jsonb),
('9781098118283', 'Kubernetes Up and Running', 54.99, 45, 4,
'{"author": "Brendan Burns", "pages": 300, "edition": 3}'::jsonb),
('9781718500417', 'CSS in Depth', 42.99, 90, 5,
'{"author": "Keith Grant", "pages": 432}'::jsonb)
RETURNING id, title, price;
Output:
INSERT 0 1
(5) إنشاء جدولي orders و order_items
(5) ▶ مثال
-- جدول الطلبات
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
status VARCHAR(20) DEFAULT 'pending'
CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
total_amount DECIMAL(12, 2) DEFAULT 0 CHECK (total_amount >= 0),
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- جدول عناصر الطلب
CREATE TABLE order_items (
id SERIAL PRIMARY KEY,
order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE RESTRICT,
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price > 0)
);
-- إدراج طلب عينة
INSERT INTO orders (user_id, status, total_amount) VALUES
(1, 'paid', 84.98)
RETURNING id;
-- إدراج عناصر الطلب (افترض معرف الطلب = 1)
INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES
(1, 1, 1, 39.99),
(1, 2, 1, 44.99);
Output:
INSERT 0 1
(6) إنشاء جدول reviews
(6) ▶ مثال
-- جدول المراجعات: مراجعات المستخدمين للكتب
CREATE TABLE reviews (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
title VARCHAR(200),
content TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- إدراج مراجعات عينة
INSERT INTO reviews (user_id, book_id, rating, title, content) VALUES
(1, 1, 5, 'نصائح Python ممتازة',
'يغطي هذا الكتاب العديد من نصائح Python العملية التي أستخدمها يوميًا في عملي.'),
(2, 2, 4, 'مقدمة جيدة لـ PostgreSQL',
'رائع للمبتدئين، لكن يمكن أن يستخدم مواضيع أكثر تقدمًا مثل التقسيم.'),
(3, 3, 5, 'ضروري لعلماء البيانات',
'تغطية شاملة لـ NumPy و Pandas و Matplotlib و Scikit-learn.')
RETURNING id, rating, title;
Output:
INSERT 0 1
5. استيراد دفعة UPSERT
(7) ▶ مثال
-- استيراد دفعة جديدة من الكتب: بعضها موجود بالفعل (حسب isbn)، بعضها جديد
INSERT INTO books (isbn, title, price, stock, category_id, metadata) VALUES
('9780134685991', 'Effective Python', 35.99, 150, 1,
'{"author": "Brett Slatkin", "pages": 352, "edition": 2}'::jsonb),
('9780596007270', 'Learning PostgreSQL', 49.99, 100, 2,
'{"author": "Regina Obe", "pages": 500}'::jsonb),
('9781119557265', 'SQL for Data Analysis', 34.99, 200, 2,
'{"author": "Ulka Rodgers", "pages": 288}'::jsonb),
('9781484254555', 'PostgreSQL High Performance', 59.99, 30, 2,
'{"author": "Ibragimov", "pages": 400}'::jsonb)
ON CONFLICT (isbn)
DO UPDATE SET
price = EXCLUDED.price,
stock = books.stock + EXCLUDED.stock
RETURNING id, title,
CASE WHEN xmax = 0 THEN 'NEW' ELSE 'UPDATED' END AS operation;
Output:
id | title | operation
----+------------------------------+-----------
1 | Effective Python | UPDATED
2 | Learning PostgreSQL | UPDATED
6 | SQL for Data Analysis | NEW
7 | PostgreSQL High Performance | NEW
6. استعلامات التحقق
(8) ▶ مثال
-- 1. عد السجلات في كل جدول
SELECT 'users' AS table_name, COUNT(*) FROM users
UNION ALL SELECT 'categories', COUNT(*) FROM categories
UNION ALL SELECT 'books', COUNT(*) FROM books
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items
UNION ALL SELECT 'reviews', COUNT(*) FROM reviews;
-- 2. التحقق من علاقات المفاتيح الخارجية
SELECT o.id AS order_id, u.name AS customer,
b.title, oi.quantity, oi.unit_price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN books b ON oi.book_id = b.id;
-- 3. التحقق من عمل القيود (يجب أن يفشل)
-- INSERT INTO books (isbn, title, price, stock) VALUES ('test', 'Test', -10, 5);
-- ERROR: check constraint "books_price_check" violated
-- INSERT INTO reviews (user_id, book_id, rating) VALUES (1, 1, 6);
-- ERROR: check constraint "reviews_rating_check" violated
-- 4. التحقق من نتائج UPSERT
SELECT isbn, title, price, stock
FROM books
WHERE isbn IN ('9780134685991', '9781119557265')
ORDER BY isbn;
Output:
count
-------
5
(1 row)
7. تعديل هيكل الجدول
(9) ▶ مثال
-- إضافة عمود published_year إلى books
ALTER TABLE books ADD COLUMN published_year INTEGER;
-- إضافة علامة is_featured إلى books
ALTER TABLE books ADD COLUMN is_featured BOOLEAN DEFAULT false;
-- تحديث published_year من JSONB metadata
UPDATE books
SET published_year = (metadata->>'year')::INTEGER
WHERE metadata ? 'year';
-- إضافة عمود phone إلى users
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- التحقق من التغييرات
\d books
\d users
Output:
-- SQL statement executed successfully
8. مثال كامل: سكربت تهيئة ب��قرة واحدة
-- ============================================
-- مثال كامل: سكربت تهيئة قاعدة بيانات متجر الكتب
-- شغل هذا من psql متصلاً بقاعدة بيانات postgres
-- ============================================
-- 1. إنشاء قاعدة البيانات
CREATE DATABASE bookstore
WITH ENCODING = 'UTF8'
LC_COLLATE = 'en_US.utf8'
LC_CTYPE = 'en_US.utf8';
-- 2. الاتصال بقاعدة البيانات الجديدة
\c bookstore
-- 3. إنشاء جميع الجداول بترتيب التبعية
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) UNIQUE NOT NULL,
description TEXT
);
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL,
password_hash CHAR(60) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE books (
id SERIAL PRIMARY KEY,
isbn VARCHAR(13) UNIQUE NOT NULL,
title VARCHAR(300) NOT NULL,
price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
stock INTEGER DEFAULT 0 CHECK (stock >= 0),
category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
metadata JSONB DEFAULT '{}'::jsonb,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
status VARCHAR(20) DEFAULT 'pending'
CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
total_amount DECIMAL(12, 2) DEFAULT 0 CHECK (total_amount >= 0),
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE order_items (
id SERIAL PRIMARY KEY,
order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE RESTRICT,
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price > 0)
);
CREATE TABLE reviews (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
title VARCHAR(200),
content TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- 4. إدراج بيانات عينة
INSERT INTO categories (name, description) VALUES
('Programming', 'تطوير البرمجيات ولغات البرمجة'),
('Database', 'تصميم قواعد البيانات و SQL وإدارة البيانات'),
('Data Science', 'تعلم الآلة وتحليل البيانات');
INSERT INTO users (email, name, password_hash) VALUES
('alice@example.com', 'Alice', '$2a$12$hash_alice'),
('bob@example.com', 'Bob', '$2a$12$hash_bob');
INSERT INTO books (isbn, title, price, stock, category_id, metadata) VALUES
('9780134685991', 'Effective Python', 39.99, 120, 1,
'{"author": "Brett Slatkin", "pages": 352}'::jsonb),
('9780596007270', 'Learning PostgreSQL', 44.99, 80, 2,
'{"author": "Regina Obe", "pages": 500}'::jsonb),
('9781491910368', 'Python Data Science Handbook', 49.99, 60, 3,
'{"author": "Jake VanderPlas", "pages": 548}'::jsonb);
INSERT INTO orders (user_id, total_amount) VALUES (1, 84.98);
INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES
(1, 1, 1, 39.99), (1, 2, 1, 44.99);
INSERT INTO reviews (user_id, book_id, rating, title, content) VALUES
(1, 1, 5, 'ممتاز', 'نصائح Python عملية.'),
(2, 2, 4, 'مقدمة جيدة', 'رائع للمبتدئين.');
-- 5. تحقق
SELECT 'categories' AS t, COUNT(*) FROM categories
UNION ALL SELECT 'users', COUNT(*) FROM users
UNION ALL SELECT 'books', COUNT(*) FROM books
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'reviews', COUNT(*) FROM reviews;
Output:
t | count
------------+-------
categories | 3
users | 2
books | 3
orders | 1
reviews | 2
❓ أسئلة شائعة
->>'year' (يعيد TEXT) ثم حول بـ ::INTEGER. إذا كنت تستعلم عن year بشكل متكرر، أضف عمود GENERATED أو فهرس تعبير.📖 ملخص
- تدفق تصميم قاعدة البيانات: تحليل المتطلبات → مخطط ER → ترتيب إنشاء الجداول → تصميم القيود → استيراد البيانات → التحقق
- ترتيب إنشاء الجداول يتبع تبعيات المفاتيح الخارجية: الجداول المشار إليها أولاً، الجداول المشيرة لاحقًا
- القيود تحمي جودة البيانات: UNIQUE (التفرد) / CHECK (التحقق الشرطي) / FK (السلامة المرجعية) / NOT NULL (غير فارغ)
- استيراد دفعة UPSERT: ON CONFLICT DO UPDATE يتعامل مع البيانات المكررة
- جملة RETURNING تتحقق من نتائج العمليات
- ALTER TABLE يحسن هيكل الجدول تكرارياً (إضافة أعمدة، تغيير قيود)
- الاستعلامات الشاملة تتحقق من سلامة البيانات: JOIN لدمج الجداول + COUNT للإحصائيات
📝 تمارين
-
أساسي (★): باتباع خطوات هذا الدرس، أنشئ قاعدة بيانات متجر الكتب وجميع الجداول الستة من الصفر، أدرج بيانات العينة، ثم شغل استعلام��ت التحقق للتأكد من أن عدد صفوف كل جدول يطابق الأمثلة.
-
متوسط (★★): نفذ UPSERT على قاعدة بيانات متجر الكتب: استورد 4 سجلات كتب (اثنان منها لهما ISBNs موجودة). للكتب الموجودة، حدث السعر وزد المخزون؛ الكتب الجديدة تدرج بشكل طبيعي. استخدم RETURNING لإظهار ما إذا كان كل سجل NEW أو UPDATED.
-
تحدي (★★★): أضف جدول ربط many-to-many
book_authorsإلى قاعدة بيانات متجر الكتب (كتاب يمكن أن يكون له مؤلفون متعددون، ومؤلف يمكنه كتابة كتب متعددة)، بالإضافة إلى جدولauthors. صمم هيكل الجدول (بمفاتيح خارجية وقيود)، أدرج 3 مؤلفين وبيانات الربط، ثم اكتب استعلام JOIN يعرض جميع أسماء المؤلفين لكل كتاب.