يتناول هذا القسم الطرق الأساسية للاستعلام عن البيانات وعرض الجداول، وربط البيانات بين عدة جداول، كما نتعرف فيه على كيفية تعديل البيانات ضمن الجدول أو حذفها، والعديد من التعليمات والتوابع الأكثر أهمية في Postgres.
إنشاء الجداول
سنشرح في هذه الفقرة كيفية إنشاء الجداول المذكورة في المثال السابق يدويًّا.
ملاحظة: إن كنت قمت بتنزيل قاعدة البيانات من المقال السابق، تثبيت Postgres والتعرف على أساسيات إدارتها لقواعد البيانات، فيمكنك الاطلاع على التعليمات الحقيقية التي تم من خلالها إنشاء الجداول، وذلك من خلال الأوامر التالية في صدفة bash:
pg_dump -st products hsoubguide pg_dump -st purchases hsoubguide pg_dump -st purchase_items hsoubguide pg_dump -st users hsoubguide
تقوم التعليمة السابقة بإظهار جميع التعليمات اللازمة لإنشاء الجدول المذكور بالحالة التي هو عليها الآن، إلا أننا سنركّز في هذه الفقرة على التعليمات الأساسية فقط.
في حال قمت بتنزيل النسخة الكاملة من قاعدة البيانات كما ورد سابقًا، وترغب في إنشاء الجداول من الصفر أيضًا، فسنقوم لذلك بإنشاء قاعدة بيانات جديدة كي لا تتعارض مع الجداول السابقة.
CREATE DATABASE hsoubguide2;
يمكننا إنشاء جدول جديد في قاعدة البيانات عن طريق التعليمة CREATE TABLE حيث نقوم بتحديد اسم الجدول، وتفصيل أسماء الأعمدة ونوع البيانات المحتواة في كل منها، إلى جانب الصفات الخاصة بكل عمود، كما في الأمثلة التالية التي تشرح نفسها بنفسها:
CREATE TABLE products ( id integer NOT NULL, title character varying(255), price numeric, created_at timestamp with time zone, deleted_at timestamp with time zone, tags character varying(255)[] );
في التعليمة السابقة، أُنشئ جدول اسمه products وحُدّد فيه الأعمدة التالية:
-
العمود
id، يحتوي بيانات نوعهاintegerأي أعداد صحيحة، وله المواصفةNOT NULL، أي أنه يُمنع إنشاء سطر دون تحديد قيمة لهذا العمود. -
العمود
title، يحتوي بيانات محرفية، طولها متغير، يمكن أن يصل إلى 255 حرفًا، ولذلك نستخدم النوعcharacter varyingمع وضع الطول الأكبر بين قوسين. -
العمود
price، يحوي بيانات عددية من النوعnumericوهو نوع عددي مميز، يمكن تخزين أعداد يصل عدد منازلها إلى 131072 منزلة قبل الفاصلة العشرية، و 16383 منزلة بعد الفاصلة! إلا أن العمليات الحسابية عليه تكون أبطأ من النوعintegerوالسعة التخزينية التي يحتاجها أكبر أيضًا. -
العمود
created_atو العمودdeleted_atيحتويان على بيانات التاريخ والوقت مع حفظ المنطقة الزمنية، فهما من النوعtimestamp with time zone -
العمود
tagsيحتوي بيانات محرفية من النوعcharacter varyingإلا أن وجود الرمز[]في تعريف هذا العمود، يعني أنه سيحتفظ بمصفوفة من العبارات المحرفية (أي أنه لن يحتوي عبارة واحدة، بل قائمة من العبارات)
يمكنك الاطلاع على المزيد من التفاصيل عن الأنواع العددية من توثيق Postgres الرسمي.
يمكننا رؤية تفاصيل الجدول الذي أُنشئ كما يلي:
hsoubguide2=# \d products
Table "public.products"
Column | Type | Collation | Nullable | Default
------------+--------------------------+-----------+----------+---------
id | integer | | not null |
title | character varying(255) | | |
price | numeric | | |
created_at | timestamp with time zone | | |
deleted_at | timestamp with time zone | | |
tags | character varying(255)[] | | |
بعد أن قُمنا بإنشاء الجدول وتحديد أنواع الأعمدة، سنقوم بتحديد مواصفة هامّة للعمود id هي PRIMARY KEY وتعني أن القيمة المخزنة ضمن هذا العمود ستكون فريدة UNIQUE لا تتكرر في أي سطر من الأسطر، ولا يمكن أن تكون NULL، وستكون القيمة المخزنة في هذا العمود هي التي تميّز الأسطر عن بعضها.
سنتعرف من خلال هذه الخطوة على التعليمة التي تقوم بتعديل تعريف الجداول ALTER TABLE، والتعديل الذي سنقوم به سيكون ADD CONSTRAINT كما يلي:
ALTER TABLE ONLY public.products ADD CONSTRAINT products_pkey PRIMARY KEY (id);
حيث قمنا بإضافة CONSTRAINT أسميناه products_pkey ينصّ على جعل العمود id بالمواصفة PRIMARY KEY، وذلك في الجدول products.
يمكننا استخدام التعليمة ALTER لتغيير أي شيء من خصائص الجدول، كتغيير اسمه، أو تغيير اسم أحد الأعمدة، أو إضافة أعمدة جديدة أو حذف أعمدة، ويمكنك الاطلاع على المزيد عن هذه التعلمية من توثيق Postgres الرسمي.
أصبح لديك الآن المعرفة الكافية لفهم كيفية إنشاء الجداول، ويمكنك قراءة التعليمات التالية وتنفيذها بوضوح لإنشاء الجداول التي نحتاجها في أمثلتنا.
CREATE TABLE purchases ( id integer NOT NULL, created_at timestamp with time zone, name character varying(255), address character varying(255), state character varying(2), zipcode integer, user_id integer );
CREATE TABLE purchase_items ( id integer NOT NULL, purchase_id integer, product_id integer, price numeric, quantity integer, state character varying(255) );
CREATE TABLE users ( id integer NOT NULL PRIMARY KEY, email character varying(255), password character varying(255), details public.hstore, created_at timestamp with time zone, deleted_at timestamp with time zone );
ملاحظة: في الجدول الأخير، يوجد العمود details يحوي بيانات من النوع hstore، وسنتحدث عن هذا النوع الخاص في الفصل التالي، أنواع بيانات خاصة في قواعد بيانات Postgres.
والآن، إذا أردت حذف جدولٍ من الجداول، فاستخدم التعليمة DROP TABLE كما يلي:
DROP TABLE users
تعديل الجداول
يمكننا التعديل على الجدول بعد إنشائه باستخدام التعليمة الأساسية ALTER، حيث يمكننا من خلالها القيام بتعديل جميع خصائص الجداول، نذكر منها أهم عمليات التعديل التالية:
1- تعديل اسم الجدول:
ALTER TABLE users RENAME TO our_lovely_users;
2- تعديل اسم عمود ما:
ALTER TABLE products RENAME COLUMN title TO product_name;
3- إضافة عمود إلى الجدول:
ALTER TABLE users ADD COLUMN image_url character varying(1024);
4- حذف عمود من الجدول:
ALTER TABLE users
DROP COLUMN IF EXISTS image_url
5- تعديل نوع البيانات في عمود ما من الجدول:
ALTER TABLE products ALTER COLUMN price SET DATA TYPE float;
يمكنك التوسع في إمكانية التعديل على الجداول بالاطلاع على وثائق Postgres الرسمية.
إدخال البيانات
بعد أن قمنا بإنشاء الجداول، علينا إدخال البيانات فيها، ويتم ذلك عن طريق التعليمة INSERT INTO ، وسنقوم في المثال بإدخال سطر في الجدول products كما يلي:
INSERT INTO products( id, title, price, created_at, tags ) VALUES ( 1, 'Python Book', 29.99, NOW(), ARRAY['Book','Programming','Python'] );
توضّح التعليمة السابقة كيفية إدخال سطر في جدول ما، وذلك عن طريق ذكر اسم الجدول، ثم ترتيب الأعمدة التي سنحدد قيمتها بين قوسين، وبعد ذلك نكتب الكلمة المفتاحية VALUES ثم نذكر قيمة كل عمود في السطر الجديد بالترتيب نفسه.
لاحظ استخدامنا للتابع NOW الذي يعطي التاريخ والوقت في لحظة تنفيذ التعليمة.
لاحظ أيضًا كيف قمنا بإدخال مصفوفة في العمود tags، وسنتحدث عن ذلك أكثر لاحقًا في هذا الدليل.
للتأكد من أن السطر تم إدخاله، يمكنك استخدام التعليمة SELECT وهي تعليمة الاستعلام الأساسية، وسنستخدمها هنا دون أي شروط كما يلي:
SELECT * FROM products;
حيث تشير * إلى اختيار جميع الأعمدة لعرضها من الجدول products، وسنحصل على المخرجات التالية:
hsoubguide2=# SELECT * FROM products; id | title | price | created_at | deleted_at | tags ----+------------------+--------+-------------------------------+------------+--------------------------- 1 | Python Book | 29.99 | 2020-06-22 12:22:02.281079+03 | | {Book,Programming,Python} (1 row)
يمكننا أيضًا إدخال عدة أسطر في تعليمة INSERT واحدة، وذلك بوضع قوسين () حول البيانات الخاصة بكل سطر، وفصل الأسطر بفاصلة كما يلي:
INSERT INTO products(id,title,price,created_at,tags) VALUES ( 2,'Book2',1.99,NOW(),ARRAY['a1','b','q'] ), ( 3,'Book3',2.99,NOW(),ARRAY['a2','c','w'] ), ( 4,'Book4',3.99,NOW(),ARRAY['a3','d','e'] ), ( 5,'Book5',4.99,NOW(),ARRAY['a4','e','r'] );
ملاحظة: سنتحدث عن كيفية التعديل على الأسطر المخزّنة أو حذف أسطر من الجدول في فقرات لاحقة.
كانت هذه بداية سريعة في كيفية إنشاء جدول وإدخال البيانات إليه، ثم الاستعلام عنها، وسنتوسع في الفقرات التالية في أنواع الاستعلامات وكيفية القيام بها، وذلك على اعتبار وجود الجداول والبيانات مُسبقًا، واعتبار أن إدخال البيانات يتم تلقائيًّا عن طريق ربط قاعدة البيانات بموقع ويب، أو بواجهة مستخدم ما.
استعراض الجداول
نستعرض في المثال التالي، كيفية عرض جميع الجداول المخزنة في قاعدة البيانات باستخدام التعليمة \dt كما يلي:
hsoubguide=# \dt List of relations Schema | Name | Type | Owner --------+----------------+-------+---------- public | products | table | postgres public | purchase_items | table | postgres public | purchases | table | postgres public | users | table | postgres (4 rows)
ملاحظة: العبارة hsoubguide=# مولّدة تلقائيًّا من psql وتحوي اسم قاعدة البيانات التي نحن بداخلها، لذلك لا تقم بنسخها في حال كنت ترغب بنسخ التعليمات في هذا الدليل.
الاستعلام عن البيانات
كلمة "استعلام" تعني طلب الحصول على أسطر محددة من قاعدة البيانات، ولكننا قد نرغب في تحديد أعمدة محددة من جداول محددة، وقد نحتاج إلى تحديد شروط على الأسطر التي نرغب بالحصول عليها، كأن نطلب الحصول على المستخدمين الذين لم يقوموا بأي تفاعل منذ سنة، أو الحصول على بيانات عن المنتجات التي يتجاوز عدد مبيعاتها قيمة محددة.
ولذلك فالخطوة الأولى في بناء الاستعلامات هي معرفة مصدر البيانات التي تتعامل معها، ثم معرفة البيانات التي يمكننا الحصول عليها، وهي الأعمدة التي يحتويها الجدول.
في مثالنا التالي، سنحاول معرفة بعض المعلومات عن المستخدمين المخزّنين في قاعدة البيانات.
سنستخدم التعليمة \d يليها اسم الجدول لاستعراض أسماء الأعمدة ضمن الجدول كما يلي:
hsoubguide=# \d users
Table "public.users"
Column | Type | Collation | Nullable | Default
------------+--------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
email | character varying(255) | | |
password | character varying(255) | | |
details | hstore | | |
created_at | timestamp with time zone | | |
deleted_at | timestamp with time zone | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "purchases" CONSTRAINT "purchases_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
ظهر لدينا الآن مجموعة متنوعة من البيانات، سنختار منها المعلومات الثلاث التالية الخاصة بالمستخدمين وهي id و email و created_at.
والآن أصبحنا نعرف المعلومات الأساسية لبناء الاستعلام وهي:
- الجدول الذي نريد استعلام البيانات منه
- البيانات التي نريدها من ذاك الجدول
يبين المثال التالي التركيب النحوي للقيام بالاستعلام المطلوب، وهو يحوي البيانات المُراد الحصول عليها، يليها اسم الجدول الحاوي عليها، ثم فاصلة منقوطة للدلالة على انتهاء الاستعلام:
hsoubguide=# SELECT id,email,created_at hsoubguide-# FROM users; id | email | created_at ----+-------------------------------+------------------------ 1 | Earlean.Bonacci@yahoo.com | 2009-12-20 22:36:00+02 2 | Evelyn.Patnode@gmail.com | 2010-11-12 23:27:00+02 3 | Derek.Crenshaw@gmail.com | 2009-03-08 05:06:00+02 4 | Shari.Julian@yahoo.com | 2010-11-20 12:58:00+02 5 | Zita.Breeding@gmail.com | 2009-08-12 01:33:00+03 6 | Samatha.Hedgpeth@yahoo.com | 2010-07-18 13:40:00+03 7 | Quinton.Gilpatrick@yahoo.com | 2010-09-03 00:56:00+03 8 | Vivian.Westmoreland@yahoo.com | 2009-10-01 14:34:00+03 9 | Danny.Crays@gmail.com | 2009-04-22 10:30:00+03 10 | Edmund.Roles@yahoo.com | 2009-07-08 00:01:00+03 11 | Shanell.Lichtenstein@aol.com | 2009-05-22 03:18:00+03 12 | Romaine.Birdsell@aol.com | 2009-01-14 07:07:00+02 13 | Zita.Luman@yahoo.com | 2009-02-04 16:49:00+02 14 | Claud.Cousineau@gmail.com | 2009-08-17 21:48:00+03 15 | Kali.Damore@yahoo.com | 2010-07-07 13:28:00+03 16 | Graciela.Kubala@yahoo.com | 2010-08-19 08:42:00+03 17 | Theresia.Edwin@yahoo.com | 2010-08-11 11:21:00+03 18 | Ozella.Yoshimura@gmail.com | 2010-07-23 19:03:00+03 19 | Wynona.Greening@aol.com | 2009-05-24 17:25:00+03 20 | Kimi.Mcqueeney@gmail.com | 2010-06-22 18:16:00+03 21 | Cherryl.Tarnowski@gmail.com | 2009-01-26 11:56:00+02 22 | Isabel.Breeding@gmail.com | 2010-07-11 16:28:00+03 23 | Ivana.Kurth@yahoo.com | 2010-06-25 11:36:00+03 24 | Humberto.Jonson@yahoo.com | 2009-09-23 16:09:00+03 25 | Ivana.Sosnowski@aol.com | 2009-01-16 13:55:00+02 26 | Cortney.Strayer@gmail.com | 2009-07-19 09:08:00+03 27 | Williams.Upson@gmail.com | 2010-08-10 08:48:00+03 28 | Jeremiah.Buonocore@yahoo.com | 2009-03-19 09:49:00+02 29 | Ozella.Roles@gmail.com | 2009-10-09 12:44:00+03 30 | Salvatore.Arends@aol.com | 2009-09-05 04:55:00+03 31 | Layne.Sarver@aol.com | 2010-09-26 11:00:00+03 32 | Takako.Gilpatrick@aol.com | 2009-02-22 17:46:00+02 33 | Russ.Mcclain@yahoo.com | 2010-01-12 19:27:00+02 34 | Claud.Westmoreland@aol.com | 2010-06-11 20:21:00+03 35 | Derek.Knittel@gmail.com | 2010-08-17 00:09:00+03 36 | Eleanor.Patnode@yahoo.com | 2010-06-06 04:27:00+03 37 | Carmel.Bulfer@aol.com | 2009-06-06 23:13:00+03 38 | Mauro.Pung@yahoo.com | 2009-08-20 05:34:00+03 39 | Sherilyn.Hamill@gmail.com | 2010-04-02 02:39:00+03 40 | Glen.Lanphear@yahoo.com | 2010-08-06 18:14:00+03 41 | Stacia.Schrack@aol.com | 2010-06-14 22:28:00+03 42 | Tonette.Alba@gmail.com | 2009-12-28 12:21:00+02 43 | Eve.Kump@yahoo.com | 2009-08-20 12:45:00+03 44 | Shanell.Maxson@gmail.com | 2009-11-21 08:28:00+02 45 | Gudrun.Arends@gmail.com | 2010-06-30 15:30:00+03 46 | Angel.Lessley@yahoo.com | 2009-08-21 20:06:00+03 47 | Harrison.Puett@yahoo.com | 2009-07-21 18:20:00+03 48 | Granville.Hedgpeth@gmail.com | 2009-08-03 17:54:00+03 49 | Samatha.Pellegrin@yahoo.com | 2009-03-25 22:17:00+02 50 | Wan.Dilks@gmail.com | 2009-10-09 01:43:00+03 (50 rows)
ملاحظة: يجب إنهاء تعليمات postgres بفاصلة منقوطة، وفي حال عدم القيام بذلك، فعند الضغط على enter لا تعتبر psql أن التعليمة قد انتهت، وتنتظر كتابة الفاصلة المنقوطة في السطر الجديد.
يسمح لنا الأمر السابق بعرض جميع بيانات الجدول، إلا أن هذا لا يناسب عرض الجداول الحاوية على كمية كبيرة من البيانات، ولذلك يتوجب علينا الحد من كمية البيانات وتصفيتها.
الحد من البيانات المعروضة باستخدام LIMIT
يمكننا إضافة التوجيه LIMIT يليه عدد الأسطر الأكبر الذي نسمح بعرضه عند الاستعلام، وذلك للحد من الأسطر المعروضة، كما يلي:
hsoubguide=# SELECT id,email,created_at hsoubguide-# FROM users hsoubguide-# LIMIT 5; id | email | created_at ----+---------------------------+------------------------ 1 | Earlean.Bonacci@yahoo.com | 2009-12-20 22:36:00+02 2 | Evelyn.Patnode@gmail.com | 2010-11-12 23:27:00+02 3 | Derek.Crenshaw@gmail.com | 2009-03-08 05:06:00+02 4 | Shari.Julian@yahoo.com | 2010-11-20 12:58:00+02 5 | Zita.Breeding@gmail.com | 2009-08-12 01:33:00+03 (5 rows)
ترتيب البيانات باستخدام ORDER BY
بعد أن تعرفنا على كيفية الحد من البيانات، قد نرغب بإظهار عدد محدود من البيانات ولكن وفق ترتيب معيّن، فمثلًا نريد الاستعلام عن 5 مستخدمين ولكن بعد ترتيبهم حسب البريد الإلكتروني الخاص بهم، وللقيام بذلك سنستخدم التوجيه ORDER BY كما يلي:
hsoubguide=# SELECT id,email,created_at hsoubguide-# FROM users hsoubguide-# ORDER BY email hsoubguide-# LIMIT 5; id | email | created_at ----+-----------------------------+------------------------ 46 | Angel.Lessley@yahoo.com | 2009-08-21 20:06:00+03 37 | Carmel.Bulfer@aol.com | 2009-06-06 23:13:00+03 21 | Cherryl.Tarnowski@gmail.com | 2009-01-26 11:56:00+02 14 | Claud.Cousineau@gmail.com | 2009-08-17 21:48:00+03 34 | Claud.Westmoreland@aol.com | 2010-06-11 20:21:00+03 (5 rows)
ماذا لو أردنا القيام بترتيب تنازلي؟ يمكننا استخدام DESC اختصارًا لكلمة "descending" كما يلي:
hsoubguide=# SELECT id,email,created_at hsoubguide-# FROM users hsoubguide-# ORDER BY email DESC hsoubguide-# LIMIT 5; id | email | created_at ----+--------------------------+------------------------ 13 | Zita.Luman@yahoo.com | 2009-02-04 16:49:00+02 5 | Zita.Breeding@gmail.com | 2009-08-12 01:33:00+03 19 | Wynona.Greening@aol.com | 2009-05-24 17:25:00+03 27 | Williams.Upson@gmail.com | 2010-08-10 08:48:00+03 50 | Wan.Dilks@gmail.com | 2009-10-09 01:43:00+03 (5 rows)
فلنلقِ نظرةً على جدول المنتجات كي نحظى بفرصة الحصول على مثالٍ جديد:
hsoubguide=# SELECT title,price
hsoubguide-# FROM products
hsoubguide-# LIMIT 8;
title | price
------------------+--------
Dictionary | 9.99
Python Book | 29.99
Ruby Book | 27.99
Baby Book | 7.99
Coloring Book | 5.99
Desktop Computer | 499.99
Laptop Computer | 899.99
MP3 Player | 108.00
(8 rows)
سنقوم في المثال التالي بترتيب الجدول السابق حسب السعر، ثمّ سنقوم بترتيب المنتجات المتشابهة بالسعر بعكس الترتيب الأبجدي لأسماء المنتجات، وذلك كما يلي:
hsoubguide=# SELECT title,price
hsoubguide-# FROM products
hsoubguide-# ORDER BY price ASC, title DESC
hsoubguide-# LIMIT 10;
title | price
---------------+-------
Coloring Book | 5.99
Baby Book | 7.99
Pop CD | 9.99
Holiday CD | 9.99
Electronic CD | 9.99
Dictionary | 9.99
Country CD | 9.99
Classical CD | 9.99
Romantic | 14.99
Drama | 14.99
(10 rows)
عمليات التجميع Aggregation Functions
سنتعرف في هذه الفقرة على توابع يمكنها القيام بتجميع قيم الأعمدة في قيمة واحدة، فمثلًا يمكننا الحصول على متوسط أسعار المنتجات في جدول المنتجات، أو عدد المنتجات الموجودة، أو السعر الأكبر أو اﻷصغر، وذلك كما يلي:
hsoubguide=# SELECT MAX(price),MIN(price),AVG(price),SUM(price),COUNT(price) hsoubguide-# FROM products; max | min | avg | sum | count --------+------+----------------------+---------+------- 899.99 | 5.99 | 132.0390476190476190 | 2772.82 | 21 (1 row)
لاحظ كيف اختلفت المخرجات فلم يعد هناك أسماء الأعمدة المعتادة، بل استُبدلت بأسماء التوابع المستخدمة.
تقوم عملية التجميع COUNT بإرجاع عدد الأسطر التي تحتوي قيمة غير خالية، وسيوضّح المثال التالي ما نرمي إليه:
hsoubguide=# SELECT COUNT(*),
hsoubguide-# COUNT(email),
hsoubguide-# COUNT(details)
hsoubguide-# FROM users;
count | count | count
-------+-------+-------
50 | 50 | 34
(1 row)
لاحظ كيف أرجع استدعاء العملية COUNT على العمود details القيمة 34، رغم أنها أرجعت القيمة 50 للعمود email، وقد استخدمنا كذلك COUNT(*) لإظهار عدد جميع الأسطر في الجدول بغض النظر عن قيم الأعمدة.
ولكن هل لاحظت أسماء الأعمدة في المخرجات؟ إنها جميعًا count، ولا يبدو ذلك مفيدًا لمن سيطّلع على هذه المخرجات، ولذلك سنستخدم التوجيه AS.
للمزيد عن عمليات التجميع، يمكنك الرجوع إلى توثيق Postgres الرسمي.
التوجيه AS
يسمح لنا هذا التوجيه بإعادة تسمية أعمدة جدول الخرج، وذلك ليتناسب مع الهدف الذي قُمنا بالاستعلام لأجله.
رأينا في المثال السابق كيف قمنا بثلاث عمليات COUNT مختلفة، ولكن الجدول في المخرجات كانت جميع أعمدته بالاسم count دون تمييز، ولذلك سنستخدم التوجيه AS لتغيير أسماء هذه الأعمدة كما يلي:
hsoubguide=# SELECT COUNT(*) AS TOTAL_COUNT,
hsoubguide-# COUNT(email) AS EMAIL_COUNT,
hsoubguide-# COUNT(details) AS DETAILS_COUNT
hsoubguide-# FROM users;
total_count | email_count | details_count
-------------+-------------+---------------
50 | 50 | 34
(1 row)
الآن أصبحت المخرجات مقروءة ومفهومة أكثر، ولكن تجدر بنا الإشارة إلى أن ذكر التوجيه AS هو أمر اختياريّ، فيمكننا تسمية جداول الخرج بدون الحاجة إلى كتابة التوجيه AS كما يلي:
hsoubguide=# SELECT COUNT(*) TOTAL_COUNT,
hsoubguide-# COUNT(email) EMAIL_COUNT,
hsoubguide-# COUNT(details) DETAILS_COUNT
hsoubguide-# FROM users;
total_count | email_count | details_count
-------------+-------------+---------------
50 | 50 | 34
(1 row)
حافظ دومًا على مقروئية الجدول في المخرجات، فقد يكون لديك في العمود عمليات حسابية أو عدة توابع في عبارة واحدة، وهنا سيكون لزامًا استخدام الأسماء المستعارة للأعمدة وإلا ستبدو الجداول في المخرجات غير مفهومة.
ترشيح البيانات (Filtering)
قًمنا حتى الآن باستعلام جميع البيانات في الجدول، وأكثر ما استطعنا تحديده هو تقليل عدد الأسطر في المخرجات، وترتيبها بشرط محدد، ولكننا ماذا لو أردنا استعراض جميع الأسطر التي تحقق شرطًا ما؟ كيف يمكننا استعراض المنتجات التي يتجاوز سعرها قيمة محددة؟ أو المستخدمين الذين أنشئت حساباتهم بعد تاريخ محدد؟
يمكننا ترشيح البيانات باستخدام الشرط WHERE، ويبين المثال التالي عملية ترشيح للحصول على المستخدمين الذين أُنشئ حسابهم في تاريخ 30/6/2010 فصاعدًا:
SELECT email, created_at FROM users WHERE created_at >= '2010-06-30';
كما يمكننا دمج شروط أخرى باستخدام AND أو OR، ويبين المثال التالي استخدام AND للاستعلام عن جميع المستخدمين الذين قاموا بإنشاء حسابات في شهر تموز عام 2010:
hsoubguide=# SELECT email, created_at
hsoubguide-# FROM users
hsoubguide-# WHERE created_at >= '2010-07-01'
hsoubguide-# AND created_at < '2010-08-01';
email | created_at
----------------------------+------------------------
Samatha.Hedgpeth@yahoo.com | 2010-07-18 13:40:00+03
Kali.Damore@yahoo.com | 2010-07-07 13:28:00+03
Ozella.Yoshimura@gmail.com | 2010-07-23 19:03:00+03
Isabel.Breeding@gmail.com | 2010-07-11 16:28:00+03
(4 rows)
ملاحظة: لاحظ كيف يتغيّر محثّ الأوامر من hsoubguide=# إلى hsoubguide-# عندما تتوزع التعليمة على عدة أسطر، وتكون الفاصلة المنقوطة هي نهاية التعليمة.
والآن ما رأيك باستخدام بعض أوامر التجميع مثل SUM و AVG وغيرها للحصول على بعض المعلومات المفيدة!
سنبدأ بالاطلاع على جدول المنتجات المشتراة، لنرى ماذا بإمكاننا أن نستخلص منه من المعلومات:
hsoubguide=# SELECT * hsoubguide-# FROM purchase_items hsoubguide-# LIMIT 20; id | purchase_id | product_id | price | quantity | state ----+-------------+------------+--------+----------+----------- 2 | 1 | 3 | 27.99 | 1 | Delivered 3 | 1 | 8 | 108.00 | 1 | Delivered 4 | 2 | 1 | 9.99 | 2 | Delivered 5 | 3 | 12 | 9.99 | 1 | Delivered 6 | 3 | 17 | 14.99 | 4 | Delivered 7 | 3 | 11 | 9.99 | 1 | Delivered 8 | 4 | 4 | 7.99 | 3 | Delivered 9 | 5 | 18 | 14.99 | 1 | Delivered 10 | 5 | 2 | 29.99 | 4 | Delivered 11 | 6 | 5 | 5.99 | 1 | Delivered 12 | 7 | 6 | 499.99 | 3 | Returned 13 | 8 | 10 | 529.00 | 1 | Delivered 14 | 8 | 7 | 899.99 | 1 | Delivered 15 | 9 | 15 | 9.99 | 2 | Delivered 16 | 10 | 2 | 29.99 | 1 | Delivered 17 | 11 | 9 | 499.00 | 2 | Delivered 18 | 12 | 14 | 9.99 | 5 | Delivered 19 | 12 | 10 | 529.00 | 1 | Delivered 20 | 13 | 8 | 108.00 | 1 | Delivered 21 | 14 | 20 | 14.99 | 1 | Delivered (20 rows)
هذا الجدول فيه الكثير من المعلومات الهامة، وسنعود إليه في الفقرة التالية، ولكن الآن يمكننا رؤية أن بعض عمليات الشراء يتم إيصالها "Delivered" وبعضها تُرجع للمتجر "Returned"، وسنقوم في المثال التالي بعدّها، وإيجاد مجموع المشتريات التي تم إيصالها بالفعل.
hsoubguide=# SELECT COUNT(*) hsoubguide-# FROM purchase_items hsoubguide-# WHERE state='Delivered'; count ------- 3888 (1 row)
hsoubguide=# SELECT COUNT(*) hsoubguide-# FROM purchase_items hsoubguide-# WHERE state='Returned'; count ------- 246 (1 row)
hsoubguide=# SELECT COUNT(*) hsoubguide-# FROM purchase_items; count ------- 4371 (1 row)
سنوجد مجموع المشتريات التي سُلِّمَت بالفعل، وتلك التي أُعيدَت:
hsoubguide=# SELECT SUM(price)
hsoubguide-# FROM purchase_items
hsoubguide-# WHERE state='Delivered';
sum
-----------
517787.85
(1 row)
hsoubguide=# SELECT SUM(price) hsoubguide-# FROM purchase_items hsoubguide-# WHERE state='Returned'; sum ---------- 36456.87 (1 row)
استخدام عبارة CASE الشرطية
تسمح لنا عبارة CASE بعمل بنية مشابهة للبنية البرمجية switch الموجودة في معظم لغات البرمجة، أو يمكن تشبيهها أكثر بعبارات if متتابعة، حيث يمكننا إنتاج قِيَم محددة بناءً على اختبارات على قيم أُخرى، ولا بد أنه المثال سيوضّح المقال.
ننشئ جدولًا بسيطًا فيه عمود واحد يحوي أعدادًا صحيحة.
hsoubguide=# CREATE TABLE case_example( hsoubguide(# number INT hsoubguide(# ); CREATE TABLE
وسنقوم بإدخال 5 أعداد في هذا الجدول كما يلي:
hsoubguide=# INSERT INTO case_example VALUES(1),(2),(3),(4),(5),(6);
INSERT 0 6
hsoubguide=# SELECT * FROM case_example ;
number
--------
1
2
3
4
5
6
(6 rows)
والآن سنقوم بالاستعلام عن عناصر هذا العمود، ولكن بدلًا من طباعة العنصر كما هو، سنطبع عبارات حسوبية بناءً على قيمة هذا العمود.
hsoubguide=# SELECT (CASE
WHEN number=1 THEN 'Hsoub'
WHEN number=2 OR number=3 THEN 'Khamsat'
WHEN number<5 THEN 'Mostaql'
WHEN number>=5 AND number<=6 THEN 'ANA'
WHEN number=7 THEN 'IO'
ELSE '.com'
END) FROM case_example ;
case
---------
Hsoub
Khamsat
Khamsat
Mostaql
ANA
ANA
(6 rows)
التعديل UPDATE والحذف DELETE
بعد أن تعرّفنا على كيفية ترشيح البيانات في الاستعلامات، سنستخدم تعليمة الترشيح نفسها WHERE لتحديد الأسطر التي نرغب بالتعديل على محتوياتها أو حذفها.
انتبه: في هذه الفقرة نقوم بالتعديل على محتويات الجدول من البيانات، أو حذف سطر ما من أسطر الجدول، أما لو أردت التعديل على الجدول نفسه بإضافة عمود أو حذف عمود، أو التعديل على اسم عمود ما أو نوع بياناته، فعليك استخدام التعليمة ALTER المذكورة في فقرة تعديل الجداول.
سننشئ أولًا جدولًا جديدًا، كيف لا تؤثر على جداولنا السابقة التي تحوي معلومات مهمة:
hsoubguide=# CREATE TABLE test_table( hsoubguide(# id integer PRIMARY KEY, hsoubguide(# number integer, hsoubguide(# name character varying(5) hsoubguide(# ); CREATE TABLE
hsoubguide=# INSERT INTO test_table VALUES hsoubguide-# (1,10,'hello'), hsoubguide-# (2,13,'mosta'), hsoubguide-# (3,-5,'test2'), hsoubguide-# (4,22,'hel..'), hsoubguide-# (5,-9,'test1'); INSERT 0 5
وهذه هي محتويات الجدول الجديد:
hsoubguide=# SELECT * from test_table; id | number | name ----+--------+------- 1 | 10 | hello 2 | 13 | mosta 3 | -5 | test2 4 | 22 | hel.. 5 | -9 | test1 (5 rows)
التعديل على الأسطر
يمكننا باستخدام الأمر UPDATE مع إضافة التوجيه SET تعديل الأسطر التي تحقق الشرط المذكور في تعليمة الترشيح WHERE كما في المثال التالي:
hsoubguide=# UPDATE test_table SET name = 'O_O' WHERE id =1; UPDATE 1 hsoubguide=# SELECT * FROM test_table ; id | number | name ----+--------+------- 2 | 13 | mosta 4 | 22 | hel.. 3 | -50 | test2 5 | -90 | test1 1 | 10 | O_O (5 rows)
كما يمكن تعديل عدة أعمدة معًا حسب شرط محدد، كما يلي:
hsoubguide=# UPDATE test_table
hsoubguide=# SET name = ('WOW' || id ),
hsoubguide=# number = id*10
hsoubguide=# WHERE id > 3;
UPDATE 2
hsoubguide=# SELECT * from test_table ; id | number | name ----+--------+------- 2 | 13 | mosta 3 | -50 | test2 1 | 10 | O_O 4 | 40 | WOW4 5 | 50 | WOW5 (5 rows)
عدَّلنَا في المثال السابق القيمة في العمود name لتحتوي كلمة WOW موصولة بالرقم المحتوى في العمود id للسطر نفسه. وعدَّلنا القيمة في العمود number لتكون ناتج ضرب الرقم الموجود في العمود id مضروبًا بـ10. وهذه التعديلات تتم على الأسطر التي يتحقق فيها الشرط id>3.
في حال لم نضع أي شرط على القيام بالتعديل، فإن التعديل ينفّذ على جميع الأسطر، كما يلي:
hsoubguide=# UPDATE test_table hsoubguide=# SET number = LENGTH(name); UPDATE 5
hsoubguide=# SELECT * from test_table ; id | number | name ----+--------+------- 2 | 5 | mosta 3 | 5 | test2 1 | 3 | O_O 4 | 4 | WOW4 5 | 4 | WOW5 (5 rows)
استخدمنا التابع LENGTH الذي يُرجع عدد الحروف للوسيط المُمرَّر له، ثم أسندناها للعمود number، وذلك دون استخدام أي شرط، لذلك تم تنفيذ التعديلات على جميع الأسطر.
إجراء عمليات حسابية على الأعمدة
يمكننا استخدام تعليمة التعديل UPDATE لتعديل قيم عمود ما عن طريق عملية حسابية أو أي عملية أخرى، كما في المثال التالي:
hsoubguide=# UPDATE test_table hsoubguide-# SET number = number*10 hsoubguide-# WHERE number >3 hsoubguide-# ; UPDATE 4
hsoubguide=# SELECT * from test_table hsoubguide-# ; id | number | name ----+--------+------- 1 | 3 | O_O 2 | 50 | mosta 3 | 50 | test2 4 | 40 | WOW4 5 | 40 | WOW5 (5 rows)
تعديل أسطر الجدول مع إظهار التعديلات
يمكن استخدام الكلمة المفتاحية RETURNING بشكل مشابه للاستعلام SELECT في نهاية عبارة التعديل، بحيث يتم التعديل ثم الاستعلام عن بعض الأعمدة في الجدول المعدّل وعرضها، وذلك كما في المثال التالي:
hsoubguide=# UPDATE test_table hsoubguide=# SET name=number hsoubguide=# WHERE id<3 hsoubguide=# RETURNING id,number,name; id | number | name ----+--------+------ 1 | 10 | 10 2 | 13 | 13 (2 rows) UPDATE 2
حذف الأسطر
يمكننا حذف سطر ما عن طريق تحديد شرط ما يشير إلى ذلك السطر بالذات دون سواه، ويُعد المعرّف الخاص بالسطر أفضل ما يمكن تحديده لحذف سطرٍ ما مع ضمان عدم حذف سواه، وذلك لأن له المواصفة PRIMARY KEY التي تمنع إنشاء سطرين بنفس القيمة في العمود id.
أوًلًا سنسترجع الجدول الخاص بنا قبل إجراء عمليات التعديل في الفقرة السابقة، وذلك عن طريق حذف جميع الأسطر، ثم إدخالها مرةً أخرى.
يمكنك حذف جميع محتويات الجدول كما يلي:
hsoubguide=# DELETE FROM test_table; DELETE 5
hsoubguide=# SELECT * FROM test_table; id | number | name ----+--------+------ (0 rows)
ثم سنعيد إدخال الأسطر كما سبق:
hsoubguide=# INSERT INTO test_table VALUES hsoubguide-# (1,10,'hello'), hsoubguide-# (2,13,'mosta'), hsoubguide-# (3,-5,'test2'), hsoubguide-# (4,22,'hel..'), hsoubguide-# (5,-9,'test1'); INSERT 0 5
سنحذف الآن السطر ذا المُعرّف id بقيمة 5:
hsoubguide=# DELETE FROM test_table hsoubguide-# WHERE id=5; DELETE 1
hsoubguide=# SELECT * FROM test_table; id | number | name ----+--------+------- 1 | 10 | hello 2 | 13 | mosta 3 | -5 | test2 4 | 22 | hel.. (4 rows)
تحذير: تُعد تعليمة DELETE من التعليمات الخطيرة، فالسطر المحذوف لا يمكن استعادته إلا في حال وجود نسخة احتياطية عن قاعدة البيانات، فاستخدم هذه التعليمة بحذر.
يمكننا طبعًا استخدام تعليمة الترشيح WHERE لتحديد أكثر من سطر، ومن ثمّ سيتم حذف جميع الأسطر التي تحقق الشرط المحدد، فمثلًا يمكننا حذف جميع الأسطر التي تكون قيمة العمود number فيها موجبة كما يلي:
hsoubguide=# DELETE FROM test_table hsoubguide=# WHERE number>0; DELETE 3
hsoubguide=# SELECT * from test_table; id | number | name ----+--------+------- 3 | -5 | test2 (1 row)
الربط Join
سنتعرف في هذه الفقرة على أسلوب أساسي في استخراج البيانات المرتبطة من عدة جداول، وطريقتها الرئيسية ببساطة، هي طلب البيانات من عدة جداول ثم تصفيتها، وسنبدأ بأبسط طرق الربط على الإطلاق، ونتدرج فيها إلى الوصول إلى أنواع الربط المتعددة.
الاستعلام من عدة جداول معًا
سننشئ جدولين للاختبار وإدخال بيانات إليهما، كي نتمكن من استعراض المثال الخاص بنا:
الجدول الأول اسمه names فيه عمودان، الأول رقم معرّف للشخص، والثاني فيه اسمه.
hsoubguide=# CREATE TABLE names( hsoubguide(# id integer PRIMARY KEY, hsoubguide(# name character varying(255) hsoubguide(# ); CREATE TABLE
hsoubguide=# INSERT INTO names VALUES hsoubguide-# (1,'mostafa'), hsoubguide-# (2,'ali'), hsoubguide-# (3,'fares'); INSERT 0 3
hsoubguide=# SELECT * FROM names; id | name ----+--------- 1 | mostafa 2 | ali 3 | fares (3 rows)
أما الجدول الثاني، ففيه أرقام هواتف نفس الأشخاص، ولكن لن نضع فيه أسماءهم، ففيه الرقم المعرّف بالشخص، ورقم هاتفه:
hsoubguide=# CREATE TABLE phones( hsoubguide(# id integer PRIMARY KEY, hsoubguide(# phone character varying(15) hsoubguide(# ); CREATE TABLE
hsoubguide=# INSERT INTO phones VALUES hsoubguide-# (1,'+966123456789'), hsoubguide-# (2,'+961111111111'), hsoubguide-# (3,'+962333333333'); INSERT 0 3
hsoubguide=# SELECT * FROM phones; id | phone ----+--------------- 1 | +966123456789 2 | +961111111111 3 | +962333333333 (3 rows)
فلنبدأ بالاستعلام عن البيانات من الجدولين معًا:
hsoubguide=# SELECT * FROM names, phones; id | name | id | phone ----+---------+----+--------------- 1 | mostafa | 1 | +966123456789 2 | ali | 1 | +966123456789 3 | fares | 1 | +966123456789 1 | mostafa | 2 | +961111111111 2 | ali | 2 | +961111111111 3 | fares | 2 | +961111111111 1 | mostafa | 3 | +962333333333 2 | ali | 3 | +962333333333 3 | fares | 3 | +962333333333 (9 rows)
لاحظ كيف تم إظهار جميع أسطر الجدول الأول مرتبطة بسطر واحد من الجدول الثاني، ثم مرةً أخرى، جميع أسطر الجدول الأول مرتبطة بالسطر الثاني من الجدول الثاني وهكذا.
ماذا لو أردنا عرض اسم الشخص مع رقم هاتفه؟ يكفي أن نختار الأسطر التي يتوافق فيها العمود id من الجدول names مع العمود id من الجدول phones، ولكن علينا كتابة اسم الجدول تليه نقطة . ثم اسم العمود id كي نتمكن من تمييز العمود الموجود في الجدول الأول عن الآخر في الجدول الثاني، كما يلي:
hsoubguide=# SELECT * FROM phones, names hsoubguide-# WHERE phones.id = names.id; id | phone | id | name ----+---------------+----+--------- 1 | +966123456789 | 1 | mostafa 2 | +961111111111 | 2 | ali 3 | +962333333333 | 3 | fares (3 rows)
قمنا لتوّنا بالربط بين جدولين، وسنقوم بتحسين المخرجات الآن قليلًا، بعرض العمود id مرة واحدة:
hsoubguide=# SELECT phones.id,phone,name hsoubguide-# FROM phones,names hsoubguide-# WHERE phones.id = names.id; id | phone | name ----+---------------+--------- 1 | +966123456789 | mostafa 2 | +961111111111 | ali 3 | +962333333333 | fares (3 rows)
والآن يمكننا أن نتّجه إلى تطبيق الربط على مثالنا الخاص بالمنتجات المشتراة، هل تتذكر جدول المنتجات المُشتراة:
hsoubguide=# SELECT * hsoubguide-# FROM purchase_items hsoubguide-# LIMIT 6; id | purchase_id | product_id | price | quantity | state ----+-------------+------------+--------+----------+----------- 2 | 1 | 3 | 27.99 | 1 | Delivered 3 | 1 | 8 | 108.00 | 1 | Delivered 4 | 2 | 1 | 9.99 | 2 | Delivered 5 | 3 | 12 | 9.99 | 1 | Delivered 6 | 3 | 17 | 14.99 | 4 | Delivered (6 rows)
لاحظ الأعمدة الأولى، purchase_id يدل على المعرّف الخاص بعملية الشراء كاملة (التي يقوم بها زبون ما في وقت محدد)، والعمود product_id الذي يحدد المعرف الخاص بالمنتج الذي تم شراؤه.
هذه الأعمدة لا تحتوي بيانات عن المنتجات أو عن عملية الشراء، بل هي تشير إلى المعرّف الخاص بها في الجداول الخاصة بها، فهل يمكننا استخدام هذا المعرّف لجلب تلك البيانات من جداولها ثم دمج تلك البيانات مع بيانات الجدول الحالي؟
ما نحاول الوصول إليه في هذه الفقرة، هو ربط عدة جداول مع بعضها، للحصول على معلومات متكاملة في جدول الخرج، فبدلًا من عرض المعرّف الخاص بالمنتج، نريد عرض اسمه مثلًا، وبدلًا من عرض المعرّف الخاص بعملية الشراء نريد عرض المستخدم الذي قام بالشراء.
فلنلقِ نظرة على مخطط قاعدة البيانات الخاصة بنا:
hsoubguide=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------------+-------+----------
public | products | table | postgres
public | purchase_items | table | postgres
public | purchases | table | postgres
public | users | table | postgres
(4 rows)
وسنقوم في مثالنا التالي بالاستعلام عن المنتجات التي تم شراؤها مؤخرًا، ونحتاج للقيام بذلك إلى بيانات من جدولي المنتجات والمشتريات معًا، وسنبدأ بأخذ نظرة إلى الجدولين لمعرفة الأعمدة التي سنتحتاج إليها:
يحوي الجدول products على تفاصيل المنتجات، كاسم المنتج وسعره:
hsoubguide=# SELECT * from products;
id | title | price | created_at | deleted_at | tags
----+------------------+--------+------------------------+------------+---------------------------
1 | Dictionary | 9.99 | 2011-01-01 22:00:00+02 | | {Book}
2 | Python Book | 29.99 | 2011-01-01 22:00:00+02 | | {Book,Programming,Python}
3 | Ruby Book | 27.99 | 2011-01-01 22:00:00+02 | | {Book,Programming,Ruby}
4 | Baby Book | 7.99 | 2011-01-01 22:00:00+02 | | {Book,Children,Baby}
5 | Coloring Book | 5.99 | 2011-01-01 22:00:00+02 | | {Book,Children}
6 | Desktop Computer | 499.99 | 2011-01-01 22:00:00+02 | | {Technology}
7 | Laptop Computer | 899.99 | 2011-01-01 22:00:00+02 | | {Technology}
8 | MP3 Player | 108.00 | 2011-01-01 22:00:00+02 | | {Technology,Music}
9 | 42" LCD TV | 499.00 | 2011-01-01 22:00:00+02 | | {Technology,TV}
10 | 42" Plasma TV | 529.00 | 2011-01-01 22:00:00+02 | | {Technology,TV}
11 | Classical CD | 9.99 | 2011-01-01 22:00:00+02 | | {Music}
12 | Holiday CD | 9.99 | 2011-01-01 22:00:00+02 | | {Music}
13 | Country CD | 9.99 | 2011-01-01 22:00:00+02 | | {Music}
14 | Pop CD | 9.99 | 2011-01-01 22:00:00+02 | | {Music}
15 | Electronic CD | 9.99 | 2011-01-01 22:00:00+02 | | {Music}
16 | Comedy Movie | 14.99 | 2011-01-01 22:00:00+02 | | {Movie,Comedy}
17 | Documentary | 14.99 | 2011-01-01 22:00:00+02 | | {Movie}
18 | Romantic | 14.99 | 2011-01-01 22:00:00+02 | | {Movie}
19 | Drama | 14.99 | 2011-01-01 22:00:00+02 | | {Movie}
20 | Action | 14.99 | 2011-01-01 22:00:00+02 | | {Movie}
(20 rows)
بينما يحوي الجدول purchase_items على تفاصيل عمليات الشراء، ففي كل عملية شراء، يمكن أن يكون هناك عدة منتجات مُشتراة، حيث يحوي السطر الواحد في هذا الجدول على عملية شراء لمنتج واحد، ويخزن العمود purchase_id رقمًا تسلسليًّا لعملية الشراء الكاملة، ويشير العمود product_id إلى الرقم المعرّف للمنتج، وهو نفسه الموجود في الجدول السابق products وبفضل وجوده يمكننا الربط بين الجدولين.
hsoubguide=# SELECT * from purchase_items LIMIT 12; id | purchase_id | product_id | price | quantity | state ----+-------------+------------+--------+----------+----------- 2 | 1 | 3 | 27.99 | 1 | Delivered 3 | 1 | 8 | 108.00 | 1 | Delivered 4 | 2 | 1 | 9.99 | 2 | Delivered 5 | 3 | 12 | 9.99 | 1 | Delivered 6 | 3 | 17 | 14.99 | 4 | Delivered 7 | 3 | 11 | 9.99 | 1 | Delivered 8 | 4 | 4 | 7.99 | 3 | Delivered 9 | 5 | 18 | 14.99 | 1 | Delivered 10 | 5 | 2 | 29.99 | 4 | Delivered 11 | 6 | 5 | 5.99 | 1 | Delivered 12 | 7 | 6 | 499.99 | 3 | Returned 13 | 8 | 10 | 529.00 | 1 | Delivered (12 rows)
الطريقة الأساسية لربط الجداول هي المفاتيح (keys) وسنشرح عنها لاحقًا باستفاضة أكثر، أما ما يهمنا الآن هو معرفة أن العمود product_id في جدول المشتريات يشير إلى العمود id في جدول المنتجات.
والآن يمكننا إنشاء الاستعلام كما يلي:
SELECT products.title, purchase_items.quantity FROM products, purchase_items WHERE products.id = purchase_items.product_id LIMIT 12;
لاحظ كيف يتم طلب عمودين من جدولين مختلفين عن طريق كتابة اسم الجدول تتبعه نقطة . ثم اسم العمود المطلوب.
وستكون المخرجات بالشكل التالي:
title | quantity
------------------+----------
Ruby Book | 1
MP3 Player | 1
Dictionary | 2
Holiday CD | 1
Documentary | 4
Classical CD | 1
Baby Book | 3
Romantic | 1
Python Book | 4
Coloring Book | 1
Desktop Computer | 3
42" Plasma TV | 1
(12 rows)
نلاحظ في المثال السابق، أنه ليس لدينا تفاصيل عن عملية الشراء، سوى عن الكمية المشتراة من كل منتج، وسنحاول في المثال التالي استعراض المنتج والكمية المشتراة، مع ذكر اسم المشتري وتاريخ الشراء، كي تكون المعلومات المعروضة مفيدة أكثر.
نستعرض أولًا الجدول purchases:
hsoubguide=# SELECT * FROM purchases LIMIT 7; id | created_at | name | address | state | zipcode | user_id ----+------------------------+--------------------+----------------+-------+---------+--------- 1 | 2011-03-16 17:03:00+02 | Harrison Jonson | 6425 43rd St. | FL | 50382 | 7 2 | 2011-09-14 08:00:00+03 | Cortney Fontanilla | 321 MLK Ave. | WA | 43895 | 30 3 | 2011-09-11 08:54:00+03 | Ruthie Vashon | 2307 45th St. | GA | 98937 | 18 4 | 2011-02-27 22:53:00+02 | Isabel Wynn | 7046 10th Ave. | NY | 57243 | 11 5 | 2011-12-20 14:45:00+02 | Shari Dutra | 4046 8th Ave. | FL | 61539 | 34 6 | 2011-12-10 15:29:00+02 | Kristofer Galvez | 2545 8th Ave. | WA | 83868 | 39 7 | 2011-06-19 06:42:00+03 | Maudie Medlen | 2049 44th Ave. | FL | 52107 | 8 (7 rows)
وجدنا الأعمدة التي كنا نبحث عنها في ما أردنا الوصول إليه قبل قليل، إنها الأعمدة name و created_at، ويمكننا الآن ربط هذين العمودين بالاستعلام السابق للحصول على مخرجات أكثر فائدة:
SELECT products.title, purchase_items.quantity, purchases.name, purchases.created_at FROM products, purchase_items, purchases WHERE products.id = purchase_items.product_id AND purchases.id = purchase_items.purchase_id LIMIT 12;
لاحظ في المثال السابق عبارة WHERE المرشِّحة، حيث وضعنا فيها شرطين. وسنحصل على المخرجات التالية:
title | quantity | name | created_at
------------------+----------+--------------------+------------------------
Ruby Book | 1 | Harrison Jonson | 2011-03-16 17:03:00+02
MP3 Player | 1 | Harrison Jonson | 2011-03-16 17:03:00+02
Dictionary | 2 | Cortney Fontanilla | 2011-09-14 08:00:00+03
Holiday CD | 1 | Ruthie Vashon | 2011-09-11 08:54:00+03
Documentary | 4 | Ruthie Vashon | 2011-09-11 08:54:00+03
Classical CD | 1 | Ruthie Vashon | 2011-09-11 08:54:00+03
Baby Book | 3 | Isabel Wynn | 2011-02-27 22:53:00+02
Romantic | 1 | Shari Dutra | 2011-12-20 14:45:00+02
Python Book | 4 | Shari Dutra | 2011-12-20 14:45:00+02
Coloring Book | 1 | Kristofer Galvez | 2011-12-10 15:29:00+02
Desktop Computer | 3 | Maudie Medlen | 2011-06-19 06:42:00+03
42" Plasma TV | 1 | Isabel Crissman | 2011-05-28 04:19:00+03
(12 rows)
في المثال السابق، ربطنا ثلاثة جداول للحصول على معلومات مترابطة وإظهارها في جدول مخرجات واحد، ولكن ربما لاحظت كيف أصبح الاستعلام طويلًا، ومعقّدًا بعض الشيء، ولذلك وُجد العرض View كطريقة لحفظ الاستعلام كجدول منطقي جديد، لا يُخزّن في الذاكرة، ولكن يمكننا التعامل معه كأنه كذلك، وهو ما سنتحدث عنه بعد استعراض أنواع الربط المختلفة.
أنواع الربط
سنستعرض أنواعًا أخرى من الربط الذي قمنا به في الفقرات السابقة، وسنستخدم لذلك مثالًا جديدًا، ننشئ فيه جدولين، الأول لتخزين اسم الطالب وكنيته، والثاني نخزّن فيه اسم الطالب وعلامته في مادة الرياضيات.
ملاحظة: للاستفادة الكاملة من هذا الدليل، نتمنّى أن تطبِّق عزيزي القارئ الأمثلة بنفسك، كي يحصل على الخبرة العملية إلى جانب الفهم النظري للمحتوى.
hsoubguide=# CREATE TABLE student( hsoubguide(# id integer PRIMARY KEY, hsoubguide(# name character varying(255), hsoubguide(# last_name character varying (255) hsoubguide(# ); CREATE TABLE
hsoubguide=# CREATE TABLE marks( hsoubguide(# id integer PRIMARY KEY, hsoubguide(# name character varying(255), hsoubguide(# marks integer hsoubguide(# ); CREATE TABLE
hsoubguide=# INSERT INTO student VALUES hsoubguide-# (1,'mostafa','ayesh'), hsoubguide-# (2,'ali','badawi'), hsoubguide-# (3,'samer','khateeb'), hsoubguide-# (4,'amer','masre'); INSERT 0 4
hsoubguide=# INSERT INTO marks VALUES hsoubguide-# (1,'ali',14), hsoubguide-# (2,'ramez',20), hsoubguide-# (3,'amer',16), hsoubguide-# (4,'fadi',18); INSERT 0 4
لاحظ أن جدول العلامات لا يحتوي جميع الطلاب المذكورة أسمائهم في جدول الطلاب، وكذلك يحتوى طلابًا غير مذكورين في جدول الطلاب، ولاحظ كذلك أن المعرّف id لطالب ما، ليس من الضروري أن يطابق المعرّف الخاص به في جدول العلامات ! ولذلك فلن نتمكن من ربط الجدولين عن طريق المعرّف الخاص بالسطر.
الربط الداخلي INNER JOIN
نستعرض فيه التقاطع بين جدولين دون أي معلومات إضافية:
hsoubguide=# SELECT student.name,marks.mark hsoubguide-# FROM student hsoubguide-# INNER JOIN marks hsoubguide-# ON student.name = marks.name; name | mark ------+------ ali | 14 amer | 16 (2 rows)
يبدو ذلك مشابهًا لما رأيناه في الفقرة السابقة:
hsoubguide=# SELECT student.name,marks.mark hsoubguide-# FROM student,marks hsoubguide-# WHERE student.name = marks.name; name | mark ------+------ ali | 14 amer | 16 (2 rows)
الربط اليساري LEFT JOIN
في هذا الربط، يتم عرض جميع أسطر الجدول اليساري (أي الجدول المرتبط بالتوجيه FROM) ويتم عرض الأسطر التي تقابلها أو NULL في حال عدم وجود ما يقابلها:
hsoubguide=# SELECT student.name,marks.mark hsoubguide-# FROM student hsoubguide-# LEFT JOIN marks hsoubguide-# ON student.name = marks.name; name | mark ---------+------ ali | 14 amer | 16 samer | mostafa | (4 rows)
الربط اليساري الخرجي LEFT OUTER JOIN
نستخدمه للحصول على الأسطر التي ليس لها ما يقابلها في الجدول اليميني كما يلي:
hsoubguide=# SELECT student.name,marks.mark hsoubguide-# FROM student hsoubguide-# LEFT JOIN marks hsoubguide-# ON student.name = marks.name hsoubguide-# WHERE mark IS NULL; name | mark ---------+------ samer | mostafa | (2 rows)
الربط اليميني RIGHT JOIN
كما في الربط اليساري، إلا أن الربط اليميني يظهر جميع أسطر الجدول المرتبط بالتعليمة JOIN (في مثالنا هو الجدول marks)، بينما يظهر الأسطر التي لها مقابل فقط من الجدول الأول (اليساري) أو NULL في حال عدم وجود تقابل، كما يلي:
hsoubguide=# SELECT student.name,marks.mark
hsoubguide-# FROM student
hsoubguide-# RIGHT JOIN marks
hsoubguide-# ON student.name = marks.name;
name | mark
------+------
ali | 14
| 20
amer | 16
| 18
(4 rows)
من الواضح في المثال السابق أن العبارة SELECT student.name, marks.mark غير مناسبة لهذا النوع من الربط، وذلك لأن العلامات التي ليس لها مقابل لأسماء أصحابها في الجدول names ستظهر بلا أسماء، وهذا أمر غير منطقي في العرض، لذلك سنضيف إضافةً بسيطة لحل هذه المشكلة:
hsoubguide=# SELECT COALESCE(student.name,marks.name) AS student_name, hsoubguide=# marks.mark hsoubguide=# FROM student hsoubguide=# RIGHT JOIN marks hsoubguide=# ON student.name = marks.name; student_name | mark --------------+------ ali | 14 ramez | 20 amer | 16 fadi | 18 (4 rows)
استخدمنا التابع COALESCE الذي يؤدي إلى عرض قيمة السطر من الجدول الأول له إن لم يكن قيمته NULL، وإلا فإنه يعرض قيمة السطر من الجدول الثاني، كما استخدمنا التوجيه AS كي لا يظهر اسم التابع في جدول المخرجات، ويظهر بدلًا منه العبارة student_name.
الربط اليميني الخارجي RIGHT OUTER JOIN
هنا يتم استثناء الأسطر التي لها مقابل في الجدول الأول، ونستعرض الأسطر التي ليس لها مقابل من الجدول الأول فقط.
hsoubguide=# SELECT COALESCE(student.name,marks.name) AS Student_name, hsoubguide-# marks.mark hsoubguide-# FROM student hsoubguide-# RIGHT JOIN marks hsoubguide-# ON student.name = marks.name hsoubguide-# WHERE student.name IS NULL; student_name | mark --------------+------ ramez | 20 fadi | 18 (2 rows)
الربط الخارجي الكامل FULL OUTER JOIN
يتم فيه إظهار جميع الأسطر من جميع الجداول، سواء كان لها مقابل (عندها تُعرض قيمته) أو لم يكن لها مقابل (يُعرض NULL).
hsoubguide=# SELECT COALESCE(student.name,marks.name) AS Student_name, hsoubguide-# marks.mark hsoubguide-# FROM student hsoubguide-# FULL OUTER JOIN marks hsoubguide-# ON student.name = marks.name; student_name | mark --------------+------ ali | 14 ramez | 20 amer | 16 fadi | 18 samer | mostafa | (6 rows)
ما رأيكم لو نضع علامة الصفر لكل من لم يقدم الامتحان؟ نترك لكم تنفيذ الاستعلام التالي:
SELECT COALESCE(student.name,marks.name) AS Student_name, COALESCE(marks.mark,0) AS mark FROM student FULL OUTER JOIN marks ON student.name = marks.name;
الربط الخارجي الكامل حصًرا FULL OUTER JOIN-only
نستخدم هذا الربط لاستعراض جميع الأسطر من الجدولين باستثناء الأسطر المرتبطة ببعضها من الجدولين معًا حسب شرط الربط، فهو بذلك يعطي نتائج الربط الخارجي الكامل، مستثنى منها نتائج الربط الداخلي:
hsoubguide=# SELECT COALESCE(student.name,marks.name) AS Student_name, hsoubguide-# COALESCE(marks.mark,0) hsoubguide-# FROM student hsoubguide-# FULL OUTER JOIN marks hsoubguide-# ON student.name = marks.name hsoubguide-# WHERE student.name IS NULL OR marks.name IS NULL; student_name | coalesce --------------+---------- ramez | 20 fadi | 18 samer | 0 mostafa | 0 (4 rows)
خلاصة
تعرفنا في هذا الفصل على كيفية إنشاء وتعديل وحذف الجداول، وكذلك تعرفنا على كيفية إدخال البيانات فيها ثم الاستعلام عنها مع ترشيحها والاستفادة من العديد من الأوامر للحصول على المخرجات المطلوبة، كما تعرفنا على عمليات التجميع وعلى طرق ربط الجداول للحصول على النتائج المستخلصة من عدة مصادر.
ومع ازدياد خبرتك في التعامل مع الاستعلامات، وخصوصًا بعد تعرفك على طرق ربط الجداول في هذا الفصل، ربما تكون قد لاحظت أن بعض الاستعلامات قد أصبحت طويلة، وصعبة القراءة، وبعضها يتكرر كثيرًا مع بعض التعديلات، ولذا فقد آن الأوان أن نتعرف إلى العرض View وبعض المزايا المتقدمة في Postgres في الفصل التالي، ليسهل علينا استخدام الاستعلامات الطويلة.
اقرأ أيضًا
- المقال التالي: مزايا متقدمة في Postgres
- المقال السابق: أساسيات استخدام صدفة psql
- النسخة الكاملة من كتاب الدليل العملي إلى قواعد بيانات PostgreSQL
- المرجع المتقدم إلى لغة SQL
أفضل التعليقات
لا توجد أية تعليقات بعد
انضم إلى النقاش
يمكنك أن تنشر الآن وتسجل لاحقًا. إذا كان لديك حساب، فسجل الدخول الآن لتنشر باسم حسابك.