3. مقدمة إلى لغة SQL

في هذا القسم من الفصل، نقدم الأوامر الأولى SQL التي تسمح بإنشاء واستخدام جدول واحد. ونقدم بشكل عام نسخة مبسطة منها. تتوفر صيغتها الكاملة في أدلة مرجعية Firebird (انظر الفقرة 2.2).

يتم استخدام قاعدة البيانات من قبل أشخاص ذوي مهارات متنوعة:

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

في الفقرة 2.6، قدمنا محرر SQL الخاص بالأداة [IB-Expert]. وهذه هي الأداة التي سنستخدمها. دعونا نستذكر بعض النقاط:

• يمكن الوصول إلى المحرر SQL عبر خيار القائمة [Tools/SQL Editor]، أو عبر المفتاح [F12]

عندها تظهر لنا نافذة [SQL Editor] حيث يمكننا كتابة الأمر SQL:

غالبًا ما يتم تمثيل لقطة الشاشة أعلاه بالنص التالي:

SQL> select * from BIBLIO

3.1. أنواع البيانات في Firebird

عند إنشاء جدول، يتعين علينا تحديد نوع البيانات التي يمكن أن يحتوي عليها عمود الجدول. نعرض هنا أنواع البيانات الأكثر شيوعًا في Firebird. تجدر الإشارة إلى أن أنواع البيانات هذه قد تختلف من SGBD إلى آخر.

SMALLINT	عدد صحيح في النطاق [-32768, 32767]: 4
INTEGER	عدد صحيح في المجال [–2 147 483 648, 2 147 483 647]: -100
NUMERIC(n,m) DECIMAL(n,m)	عدد حقيقي مكون من n أرقام، منها m أرقام بعد الفاصلة العشرية NUMERIC(5,2): -100.23، +027.30
FLOAT	عدد حقيقي مقرب بـ 7 أرقام ذات دلالة: 10.4
DOUBLE PRECISION	رقم حقيقي مقرب بـ 15 رقمًا معنويًا: -100.89
CHAR(N) CHARACTER(N)	سلسلة مكونة من N حرفًا بالضبط. إذا كانت السلسلة المخزنة تحتوي على أقل من N حرفًا، يتم استكمالها بمسافات. CHAR(10): 'ANGERS ' (4 مسافات في النهاية)
VARCHAR(N) CHARACTER VARYING(N)	سلسلة لا تتجاوز N حرفًا VARCHAR(10): 'ANGERS'
DATE	تاريخ: '2006-01-09' (تنسيق YYYY-MM-DD)
TIME	ساعة: '16:43:00' (تنسيق HH:MM:SS)
TIMESTAMP	التاريخ والوقت معًا: '2006-01-09 16:43:00' (التنسيق YYYY-MM-DD HH:MM:SS)

تسمح الدالة CAST() بالتحويل من نوع إلى آخر عند الضرورة. لتحويل قيمة V المُعلنة على أنها من النوع T1 إلى النوع T2، نكتب: CAST(V,T2). يمكن إجراء التغييرات التالية في النوع:

• من عدد إلى سلسلة أحرف. يتم هذا التغيير في النوع ضمناً ولا يتطلب استخدام الدالة CAST. وبالتالي، فإن العملية 1 + '3' لا تتطلب تحويل الحرف '3'. ونتيجتها هي العدد 4.
• DATE، TIME، TIMESTAMP إلى سلاسل أحرف والعكس صحيح. وبالتالي
• TIMESTAMP إلى TIME أو DATE والعكس بالعكس

في الجدول، قد يحتوي أحد الصفوف على أعمدة خالية من القيم. ويُقال إن قيمة العمود هي الثابت NULL. ويمكن التحقق من وجود هذه القيمة باستخدام العوامل

IS NULL / IS NOT NULL

3.2. إنشاء جدول

لمعرفة كيفية إنشاء جدول، نبدأ بإنشاء جدول في الوضع [Design] باستخدام IBExpert. وللقيام بذلك، نتبع الطريقة الموضحة في الفقرة 2.3. وبذلك ننشئ الجدول التالي:

ستُستخدم هذه الجدولة لتسجيل الكتب التي اشترتها إحدى المكتبات. وفيما يلي معنى الحقول:

Name	النوع	القيد	المعنى
ID	INTEGER	Primary Key	Identifiant du livre
TITRE	VARCHAR(30)	NOT NULL UNIQUE	Titre du livre
AUTEUR	VARCHAR(20)	NOT NULL	Son auteur
GENRE	VARCHAR(30)	NOT NULL	Son genre (Roman, Poésie, Policier, BD, ..)
ACHAT	DATE	NOT NULL	Date d'achat du livre
PRIX	NUMERIC6,2)	NOT NULL	Son prix
DISPONIBLE	CHAR(1)	NOT NULL	Est-il disponible ? O (oui), N (non)

كان من الممكن إنشاء هذا الجدول، الذي تم إنشاؤه باستخدام الأداة المساعدة IBEXPERT، مباشرةً باستخدام الأوامر SQL. لمعرفة هذه الأوامر، يكفي الرجوع إلى علامة التبويب [DDL] في الجدول:

الرمز SQL الذي سمح بإنشاء الجدول [BIBLIO] هو التالي:

SET SQL DIALECT 3;

SET NAMES ISO8859_1;


CREATE TABLE BIBLIO (
    ID INTEGER NOT NULL,
    TITRE VARCHAR(30) NOT NULL,
    AUTEUR VARCHAR(20) NOT NULL,
   GENRE VARCHAR(30) NOT NULL,
   ACHAT DATE NOT NULL,
   PRIX NUMERIC(6,2) NOT NULL,
   DISPONIBLE  CHAR(1) NOT NULL
);

ALTER TABLE BIBLIO ADD CONSTRAINT UNQ1_BIBLIO UNIQUE (TITRE);
ALTER TABLE BIBLIO ADD CONSTRAINT PK_BIBLIO PRIMARY KEY (ID);

• السطر 1: المالك Firebird - يشير إلى مستوى اللهجة SQL المستخدمة
• السطر 2: مالك Firebird - يشير إلى عائلة الأحرف المستخدمة
• الأسطر 6 - 14: معيار SQL: ينشئ الجدول BIBLIO بتحديد اسم وطبيعة كل عمود من أعمدة الجدول.
• السطر 16: المعيار SQL: ينشئ قيدًا يشير إلى أن العمود TITRE لا يسمح بوجود تكرارات
• السطر 17: القياسي SQL: يشير إلى أن العمود [ID] هو المفتاح الأساسي للجدول. وهذا يعني أنه لا يمكن أن يكون لسطرين في الجدول نفس القيمة لـ ID. ونحن هنا قريبون من القيد [UNIQUE NOT NULL] الخاص بالعمود [TITRE]، وبالتالي كان من الممكن استخدام العمود TITRE كمفتاح أساسي. الاتجاه السائد حاليًا هو استخدام مفاتيح أساسية لا تحمل أي معنى ويتم إنشاؤها بواسطة SGBD.

صيغة الأمر [CREATE TABLE] هي كما يلي:

syntaxe

CREATE TABLE الجدول (nom_colonne1 type_colonne1 contrainte_colonne1، nom_colonne2 type_colonne2 contrainte_colonne2، ...، nom_colonnen type_colonnen contrainte_colonnen، قيود أخرى)

action

ينشئ الجدول table مع الأعمدة المحددة nom_colonnei اسم العمود i المراد إنشاؤه type_colonnei نوع بيانات العمود i: char(30) numeric(6,2) date timestamp ... contrainte_colonnei القيود التي يجب أن تلتزم بها بيانات العمود i. وفيما يلي بعض منها: PRIMARY KEY: العمود هو مفتاح أساسي. وهذا يعني أنه لا يمكن أبدًا أن تحتوي صفين في الجدول على نفس القيمة في هذا العمود، كما أن وجود قيمة في هذا العمود إلزامي. ويستخدم المفتاح الأساسي بشكل أساسي لتعريف صف ما بشكل فريد. NOT NULL : لا يُسمح بوجود أي قيمة صفرية في هذا العمود. UNIQUE : لا يجوز تكرار أي قيمة أكثر من مرة في العمود. CHECK (شرط): يجب أن تستوفي قيمة العمود الشرط. autres contraintes يمكن هنا وضع - قيودًا على عدة أعمدة: check(col1>col2) - قيود المفاتيح الخارجية

كان من الممكن أيضًا إنشاء الجدول [BIBLIO] بالترتيب SQL التالي:

CREATE TABLE BIBLIO (
    ID INTEGER NOT NULL PRIMARY KEY,
    TITRE VARCHAR(30) NOT NULL UNIQUE,
    AUTEUR VARCHAR(20) NOT NULL,
   GENRE VARCHAR(30) NOT NULL,
   ACHAT DATE NOT NULL,
   PRIX NUMERIC(6,2) NOT NULL,
   DISPONIBLE  CHAR(1) NOT NULL
);

لنوضح ذلك. لنستخدم هذا الترتيب في محرر SQL (F12) لإنشاء جدول سنسميه [BIBLIO2]:

بعد التنفيذ، يجب تأكيد المعاملة لرؤية النتيجة في قاعدة البيانات:

وبعد ذلك، تظهر الجدولة في قاعدة البيانات:

بالنقر المزدوج على اسمها، يمكن الاطلاع على بنيتها:

ونجد بالفعل التعريف الذي قمنا بإنشائه للجدول [BIBLIO2]

3.3. حذف جدول

الأمر SQL لحذف جدول هو كما يلي:

syntaxe	DROP TABLE الجدول
action	حذف [table]

لحذف الجدول [BIBLIO2] الذي أنشأناه للتو، نقوم الآن بتنفيذ الأمر SQL التالي:

ونقوم بتأكيدها باستخدام [Commit]. يتم حذف الجدول [BIBLIO2]:

3.4. ملء جدول

نقوم بإدراج سطر في الجدول [BIBLIO] الذي أنشأناه للتو:

نؤكد إضافة السطر بواسطة [Commit] ثم نضغط بزر الفأرة الأيمن على السطر المضاف:

ونطلب، كما هو موضح أعلاه، نسخ السطر الذي تم إدراجه إلى الحافظة على شكل أمر SQL INSERT. ثم نستخدم أي محرر نصوص ونلصق (Lصق / Paste) ما قمنا بنسخه للتو. نحصل على الكود SQL التالي:

INSERT INTO BIBLIO (ID,TITRE,AUTEUR,GENRE,ACHAT,PRIX,DISPONIBLE) VALUES (1,'Candide','Voltaire','Essai','18-OCT-1985',140,'o');

صيغة أمر الإدراج SQL هي كما يلي:

syntaxe	insert into table [(colonne1, colonne2, ..)] values (القيمة1، القيمة2، ....)
action	يضيف سطراً (القيمة1، القيمة2، ..) إلى table. يتم تعيين هذه القيم إلى colonne1، colonne2،... إذا كانت موجودة، وإلا إلى أعمدة الجدول بالترتيب الذي تم تعريفها به.

لإدراج أسطر جديدة في الجدول [BIBLIO]، سنقوم بكتابة الأوامر التالية لـ INSERT في محرر SQL. سنقوم بتنفيذ هذه الأوامر واحدة تلو الأخرى والتحقق من صحتها باستخدام [Commit]. سنستخدم الزر [New Query] للانتقال إلى الأمر التالي INSERT.

insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (2,'Les fleurs du mal','Baudelaire','Poème','01-jan-78',120,'n');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (3,'Tintin au Tibet','Hergé','BD','10-nov-90',70,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (4,'Du côté de chez Swann','Proust','Roman','08-dec-78',200,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (5,'La terre','Zola','roman','12-jun-90',50,'n');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (6,'Madame Bovary','Flaubert','Roman','12-mar-88',130,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (7,'Manhattan transfer','Dos Passos','Roman','30-aug-87',320,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (8,'Tintin en Amérique','Hergé','BD','15-may-91',70,'o');

بعد المصادقة على الأوامر المختلفة SQL، نحصل على الجدول التالي:

3.5. الاستعلام عن جدول

3.5.1. مقدمة

في محرر SQL، نكتب الأمر التالي:

وننفذه. نحصل على النتيجة التالية:

يتيح الأمر SELECT الاطلاع على محتوى جداول قاعدة البيانات. ويتميز هذا الأمر ببنية لغوية غنية جدًا. نكتفي هنا بعرض الصيغة التي تتيح الاستعلام عن جدول واحد. وسنتناول لاحقًا الاستعلام المتزامن عن عدة جداول. صيغة الأمر SQL [SELECT] هي كما يلي:

syntaxe	SELECT [ALL|DISTINCT] [*|expression1 alias1, expression2 alias2, ...] FROM table
action	يعرض قيم expressioni لجميع صفوف الجدول. يمكن أن يكون expressioni عمودًا أو تعبيرًا أكثر تعقيدًا. يشير الرمز * إلى جميع الأعمدة. بشكل افتراضي، يتم عرض جميع صفوف الجدول (ALL). في حالة وجود DISTINCT، لا يتم عرض الصفوف المحددة المتطابقة إلا مرة واحدة. يتم عرض قيم expressioni في عمود بعنوان expressioni أو aliasi إذا تم استخدامه.

أمثلة:

SQL > select titre, auteur from biblio

SQL> select titre,prix from biblio

SQL> select titre TITRE_DU_LIVRE, prix PRIX_ACHAT from biblio

فيما سبق، قمنا بربط أسماء مستعارة (TITRE_DU_LIVRE، PRIX_ACHAT) بالأعمدة المطلوبة.

3.5.2. عرض الصفوف التي تستوفي شرطًا معينًا

syntaxe	SELECT .... WHERE condition
action	يتم عرض الأسطر التي تستوفي الشرط condition فقط

أمثلة

SQL> select titre,prix from biblio where prix>100

SQL> select titre,prix,genre from biblio where genre='Roman'

أحد الكتب ينتمي إلى النوع «رواية» وليس «رواية». نستخدم الدالة upper التي تحول سلسلة الأحرف إلى أحرف كبيرة للحصول على جميع الروايات.

SQL> select titre,prix,genre from biblio where upper(genre)='ROMAN'

يمكننا تجميع الشروط باستخدام العوامل المنطقية

AND	ET منطقية
OR	OU منطقية
NOT	النفي المنطقي

SQL> select titre,prix,genre from biblio where upper(genre)='ROMAN' and prix<100

SQL> select titre,genre from biblio

SQL> select titre,genre from biblio where upper(genre)='ROMAN' or upper(genre)='BD'

SQL> select titre,genre from biblio where not( upper(genre)='ROMAN' or upper(genre)='BD')

SQL> select titre,achat from biblio

SQL>select titre,achat from biblio where achat>'31-dec-1987'

SQL> select titre,prix from biblio where prix between 100 and 150

3.5.3. عرض الأسطر وفقًا لترتيب محدد

في الصيغ السابقة، يمكن إضافة جملة ORDER BY تحدد ترتيب العرض المطلوب:

syntaxe	SELECT .... ORDER BY expression1 [asc|desc]، expression2 [asc|dec]، ...
action	يتم عرض الأسطر الناتجة عن عملية التحديد بالترتيب التالي 1: ترتيب تصاعدي (asc / ascending وهو الإعداد الافتراضي) أو تنازلي (desc / descending) لـ expression1 2: في حالة تساوي القيم في expression1، يتم العرض وفقًا لقيم expression2 وهكذا...

أمثلة:

SQL>select titre, genre,prix,achat from biblio order by achat desc

SQL>select titre, genre,prix,achat from biblio order by prix

SQL>select titre, genre,prix,achat from biblio order by genre desc

SQL >select titre, genre,prix,achat from biblio order by genre desc, prix

SQL>select titre, genre,prix,achat from biblio order by genre desc, prix desc

3.6. حذف صفوف من جدول

syntaxe	DELETE FROM table [WHERE condition]
action	يحذف الأسطر من table التي تتحقق من condition. إذا كانت الأخيرة غير موجودة، يتم حذف جميع الأسطر.

أمثلة:

SQL> select titre from biblio

يتم إصدار الأمرين التاليين واحدًا تلو الآخر:

SQL> delete from biblio where titre='Candide'

SQL> select titre from biblio

3.7. تعديل محتوى جدول

syntaxe	تحديث الجدول بتعيين العمود 1 = التعبير 1، والعمود 2 = التعبير 2، ... [where condition]
action	بالنسبة للصفوف في table التي تتحقق من condition (جميع الصفوف في حالة عدم وجود شرط)، يتلقى colonnei القيمة expressioni.

أمثلة:

SQL> select genre from biblio

نكتب جميع الأنواع بأحرف كبيرة:

SQL> update biblio set genre=upper(genre)

نتحقق:

SQL> select genre from biblio

نعرض الأسعار:

SQL> select genre,prix from biblio;

ارتفاع أسعار الروايات بنسبة 5%:

SQL> update biblio set prix=prix*1.05 where genre='ROMAN';

نقوم بالتحقق:

SQL> select genre,prix from biblio

3.8. التحديث النهائي لجدول

عند إجراء تعديلات على جدول، يقوم Firebird في الواقع بإنشاء هذه التعديلات على نسخة من الجدول. ويمكن بعد ذلك جعلها نهائية أو إلغاؤها باستخدام الأوامر COMMIT و ROLLBACK.

syntaxe	COMMIT
action	يجعل التحديثات التي أُجريت على الجداول منذ آخر عملية COMMIT نهائية.

syntaxe	ROLLBACK
action	يلغي جميع التعديلات التي تم إجراؤها على الجداول منذ آخر عملية COMMIT.

Remarque

يتم تنفيذ أمر COMMIT ضمناً في الأوقات التالية: أ) عند قطع الاتصال بـ Firebird ب) بعد كل أمر يؤثر على بنية الجداول: CREATE، ALTER، DROP.

أمثلة

في محرر SQL، يتم وضع قاعدة البيانات في حالة معروفة من خلال التحقق من صحة جميع العمليات التي تم إجراؤها منذ آخر COMMIT أو ROLLBACK:

SQL> commit

نطلب قائمة العناوين:

SQL> select titre from biblio

حذف عنوان:

SQL> delete from biblio where titre='La terre'

التحقق:

SQL> select titre from biblio

تم حذف العنوان بنجاح. والآن نقوم بإبطال جميع التعديلات التي تم إجراؤها منذ آخر COMMIT / ROLLBACK:

SQL> rollback

التحقق:

SQL> select titre from biblio

نجد العنوان الذي تم حذفه. لنطلب الآن قائمة الأسعار:

SQL> select prix from biblio

لنفترض أن جميع الأسعار قد تم تعيينها على صفر.

SQL> update biblio set prix=0

لنتحقق من الأسعار:

SQL> select prix from biblio

لنحذف التعديلات التي أُجريت على القاعدة:

SQL> rollback

ولنتحقق مرة أخرى من الأسعار:

SQL> select prix from biblio

لقد استعدنا الأسعار الأصلية.

3.9. إضافة صفوف من جدول إلى جدول آخر

يمكن إضافة صفوف من جدول إلى جدول آخر عندما تكون هياكلها متوافقة. لتوضيح ذلك، لنبدأ بإنشاء جدول [BIBLIO2] له نفس هيكل [BIBLIO].

في مستكشف قواعد البيانات لـ IBExpert، انقر نقرًا مزدوجًا على الجدول [BIBLIO] للوصول إلى علامة التبويب [DDL]:

في هذه العلامة التبويبية، نجد قائمة الأوامر SQL التي تسمح بإنشاء الجدول [BIBLIO]. لننسخ هذا الرمز بالكامل إلى الحافظة (CTRL-A، CTRL-C). ثم نستدعي أداة تُسمى [Script Executive] تتيح تنفيذ قائمة الأوامر SQL:

يظهر محرر نصوص، حيث يمكننا لصق (CTRL-V) النص الذي تم وضعه مسبقًا في الحافظة:

غالبًا ما يُطلق على قائمة الأوامر SQL اسم «نص برمجي» SQL. سيسمح لنا [Script Executive] بتنفيذ مثل هذا البرنامج النصي، في حين أن المحرر SQL لم يكن يسمح بتنفيذ سوى أمر واحد في كل مرة. يسمح البرنامج النصي الحالي SQL بإنشاء الجدول [BIBLIO]. لنجعله ينشئ جدولًا باسم [BIBLIO2]. وللقيام بذلك، يكفي تغيير [BIBLIO] إلى [BIBLIO2]:

SET SQL DIALECT 3;

SET NAMES ISO8859_1;

CREATE TABLE BIBLIO2 (
    ID          INTEGER NOT NULL,
    TITRE       VARCHAR(30) NOT NULL,
    AUTEUR      VARCHAR(20) NOT NULL,
    GENRE       VARCHAR(20) NOT NULL,
    ACHAT       DATE NOT NULL,
    PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
    DISPONIBLE  CHAR(1) NOT NULL
);

ALTER TABLE BIBLIO2 ADD CONSTRAINT UNQ1_BIBLIIO2 UNIQUE (TITRE);

ALTER TABLE BIBLIO2 ADD CONSTRAINT PK_BIBLIIO2 PRIMARY KEY (ID);

لنقم بتشغيل هذا البرنامج النصي باستخدام الزر [Run Script] أدناه:

تم تنفيذ البرنامج النصي:

ويمكننا رؤية الجدول الجديد في مستكشف قواعد البيانات:

إذا نقرنا مرتين على [BIBLIO2] للتحقق من محتواها، نجد أنها فارغة، وهذا أمر طبيعي:

هناك صيغة بديلة للأمر SQL INSERT تسمح بإدراج صفوف من جدول آخر في جدول ما:

syntaxe	INSERT INTO table1 [(colonne1, colonne2, ...)] SELECT العمود أ، العمود ب، ... FROM table2 WHERE condition
action	يتم إضافة الأسطر من table2 التي تتحقق من condition إلى table1. يتم تعيين الأعمدة colonnea و colonneb و... من table2 بالترتيب إلى colonne1 و colonne2 و... في table1، ولذلك يجب أن تكون من نوع متوافق.

لنعد إلى محرر SQL:

ونصدر الأمر SQL التالي:

SQL> insert into BIBLIO2 select * from BIBLIO where upper(genre)='ROMAN'

الذي يُدرج في [BIBLIO2] جميع الأسطر من [BIBLIO] التي تتعلق برواية. بعد تنفيذ الأمر SQL، دعونا نُثبّته باستخدام [Commit]:

SQL> commit

بعد ذلك، دعونا نراجع بيانات الجدول [BIBLIO2]:

SQL> select * from BIBLIO2

3.10. حذف جدول

syntaxe	DROP TABLE table
action	حذف table

مثال: يتم حذف الجدول BIBLIO2

SQL> drop table BIBLIO2

تأكيد التغيير:

SQL> commit

في مستكشف قواعد البيانات، يتم تحديث عرض الجداول:

نكتشف أن الجدول [BIBLIO2] قد تم حذفه:

3.11. تعديل بنية الجدول

syntaxe	ALTER TABLE table [ ADD nom_colonne1 type_colonne1 contrainte_colonne1] [ALTER nom_colonne2 TYPE type_colonne2] [DROP nom_colonne3] [ADD contrainte] [DROP CONSTRAINT nom_contrainte]
action	تسمح بإضافة (ADD) وتعديل (ALTER) وحذف (DROP) أعمدة الجدول. صيغة الأوامر nom_colonnei type_colonnei contrainte_colonnei هي نفس صيغة الأوامر CREATE TABLE. كما يمكن إضافة/حذف قيود الجدول.

مثال: لنقم بتنفيذ الأمرين التاليين SQL بالتتابع في محرر SQL

SQL > alter table biblio add nb_pages numeric(4), alter genre type varchar(30)

SQL> commit

في مستكشف قواعد البيانات، دعونا نتحقق من بنية الجدول [BIBLIO]:

تم أخذ التعديلات في الاعتبار. لنرى كيف تطور محتوى الجدول:

SQL> select * from biblio

تم إنشاء العمود الجديد [NB_PAGES] ولكنه لا يحتوي على أي قيمة. دعونا نحذف هذا العمود:

SQL> alter table biblio drop nb_pages

SQL> commit

دعونا نتحقق من البنية الجديدة للجدول [BIBLIO]:

لقد اختفى العمود [NB_PAGES] بالفعل.

3.12. طرق العرض

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

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

3.12.1. إنشاء عرض

syntaxe	CREATE VIEW nom_vue AS SELECT العمود1، العمود2، ... FROM table WHERE condition [ WITH CHECK OPTION ]
action	ينشئ العرض nom_vue. وهي عبارة عن جدول يتكون من الأعمدة 1 و2 و... من table، أما الصفوف فتتكون من صفوف table التي تستوفي شرط condition (جميع الصفوف في حالة عدم وجود شرط)
WITH CHECK OPTION	تشير هذه الجملة الاختيارية إلى أن عمليات الإدراج والتحديث على العرض يجب ألا تنشئ أي أسطر لا يمكن للعرض تحديدها.

ملاحظة إن بناء جملة الاستعلام CREATE VIEW هي في الواقع أكثر تعقيدًا من تلك المعروضة أعلاه، وتسمح على وجه الخصوص بإنشاء عرض من عدة جداول. ويكفي لذلك أن يشمل الاستعلام SELECT عدة جداول (انظر الفصل التالي).

أمثلة

نقوم بإنشاء عرض من الجدول biblio لا يتضمن سوى الروايات (تحديد الصفوف) والأعمدة «العنوان» و«المؤلف» و«السعر» (تحديد الأعمدة):

SQL> create view romans as select titre,auteur,prix from biblio where upper(genre)='ROMAN';

SQL> commit

في مستكشف قواعد البيانات، نقوم بتحديث العرض (F5). يظهر عرض:

يمكننا معرفة الترتيب SQL المرتبط بالعرض. للقيام بذلك، انقر نقرًا مزدوجًا على العرض [ROMANS]:

الطريقة تشبه الجدول. ولها بنية:

ومحتوى:

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

SQL> select * from romans

SQL> insert into biblio values (10,'Le père Goriot','Balzac','Roman','01-sep-91',200,'o')

هل تظهر الرواية الجديدة في عرض [ROMANS]؟

SQL> select * from romans

دعونا نضيف شيئًا آخر غير الرواية إلى الجدول [BIBLIO]:

SQL> insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (11,'Poèmes saturniens','Verlaine','Poème','02-sep-92',200,'o');

لنتحقق من الجدول [BIBLIO]:

SQL> select titre, auteur from BIBLIO

دعونا نتحقق من طريقة العرض [ROMANS]:

SQL> select titre, auteur from ROMANS

الكتاب المضاف غير موجود في العرض [ROMANS] لأنه لم يكن يحتوي على upper(genre)='ROMAN'.

3.12.2. تحديث عرض

يمكن تحديث عرض كما هو الحال مع الجدول. تتأثر جميع الجداول التي تُستخرج منها بيانات العرض بهذا التحديث. فيما يلي بعض الأمثلة:

SQL> insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (13,'Le Rouge et le Noir','Stendhal','Roman','03-oct-92',110,'o')

SQL> select * from romans

SQL> select titre, auteur from biblio

يتم حذف سطر من العرض [ROMANS]:

SQL> delete from ROMANS where titre='Le Rouge et le Noir'

SQL> select * from romans

SQL> select auteur, titre from BIBLIO

تم حذف السطر الذي تم حذفه من العرض [ROMANS] من الجدول [BIBLIO] أيضًا. والآن نقوم بزيادة أسعار الكتب في العرض [ROMANS]:

SQL> update romans set prix=prix*1.05

نقوم بالتحقق في [ROMANS]:

SQL> select * from romans

ما هو التأثير على الجدول [BIBLIO]؟

SQL> select titre, auteur, prix from biblio

وقد زادت الروايات بنسبة 5% في الجدول [BIBLIO] أيضًا.

3.12.3. حذف عرض

syntaxe	DROP VIEW nom_vue
action	يحذف العرض المسمى

مثال

SQL> drop view romans

SQL> commit

في مستكشف قواعد البيانات، يمكن تحديث العرض (F5) للتأكد من اختفاء العرض [ROMANS]:

3.13. استخدام وظائف المجموعات

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

syntaxe1	SELECT f1، f2، ..، fn FROM table [ WHERE condition ]
action	يحسب الدوال الإحصائية fi على جميع صفوف الجدول للتحقق من احتمال وجود condition.

syntaxe2

SELECT f1، f2، ..، fn FROM table [ WHERE condition ] [ GROUP BY expr1, expr2, ..]

action

تؤدي الكلمة الرئيسية GROUP BY إلى تقسيم صفوف الجدول إلى مجموعات. تحتوي كل مجموعة على الصفوف التي تكون فيها التعبيرات expr1 و expr2 و... لها نفس القيمة. مثال: GROUP BY genre تضع في مجموعة واحدة الكتب التي تنتمي إلى نفس النوع. أما الجملة GROUP BY author,genre فستجمع في نفس المجموعة الكتب التي لها نفس المؤلف ونفس النوع. وتقوم الجملة WHERE condition أولاً باستبعاد الصفوف التي لا تستوفي الشرط من الجدول. ثم يتم تشكيل المجموعات بواسطة الجملة GROUP BY. بعد ذلك، يتم حساب الدوال fi لكل مجموعة من السطور.

syntaxe3	SELECT f1، f2، ..، fn FROM table [ WHERE condition ] [ GROUP BY expression] [ HAVING condition_de_groupe]
action	تقوم الجملة HAVING بتصفية المجموعات التي تشكلها الجملة GROUP BY. وبالتالي، فهي مرتبطة دائمًا بوجود هذه الجملة: GROUP BY. مثال: GROUP BY النوع HAVING النوع!='ROMAN'

الوظائف الإحصائية المتاحة fi هي التالية:

AVG(expression)	متوسط التعبير
COUNT(expression)	عدد الأسطر التي يكون فيها التعبير له قيمة
COUNT(*)	العدد الإجمالي للصفوف في الجدول
MAX(expression)	القيمة القصوى للتعبير
MIN(expression)	الحد الأدنى للتعبير
SUM(expression)	مجموع التعبير

أمثلة

SQL> select prix from biblio

السعر المتوسط؟ السعر الأقصى؟ السعر الأدنى؟

SQL> select avg(prix), max(prix), min (prix) from biblio

SQL> select titre, prix,genre from biblio

متوسط سعر الرواية؟ السعر الأقصى؟

SQL> select avg(prix) moyenne, max(prix) prix_maxi from biblio where upper(genre)='ROMAN'

كم عدد BD؟

SQL> select count(*) from biblio where upper(genre)='BD'

كم عدد الروايات التي يقل سعرها عن 100 فرنك؟

SQL> select count(*) from biblio where upper(genre)='ROMAN' and prix<100

SQL> select genre, prix from biblio

ما هو عدد الكتب ومتوسط سعر الكتاب بالنسبة للكتب من نفس النوع؟

SQL> select upper(genre) GENRE,avg(prix) PRIX_MOYEN,count(*) NOMBRE from biblio group by upper(genre)

نفس السؤال ولكن فقط بالنسبة للكتب التي ليست روايات:

SQL>
select upper(genre) GENRE,avg(prix) PRIX_MOYEN,count(*) NOMBRE
from biblio
group by upper(genre)
having upper(GENRE)!='ROMAN'

نفس السؤال ولكن فقط بالنسبة للكتب التي يقل سعرها عن 150 فرنك:

SQL> 
select upper(genre) GENRE,avg(prix) PRIX_MOYEN,count(*) NOMBRE
from biblio
where prix<150
group by upper(genre)
having upper(GENRE)!='ROMAN'

نفس السؤال، لكننا نحتفظ فقط بالمجموعات التي يزيد متوسط سعر الكتاب فيها عن 100 فرنك

SQL> 
select upper(genre) GENRE, avg(prix) PRIX_MOYEN,count(*) NOMBRE
from biblio
group by upper(genre)
having avg(prix)>100

3.14. إنشاء البرنامج النصي SQL لـ من جدول

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

سنقوم هنا بتصدير الجدول [BIBLIO]. لنأخذ الخيار [Extract Metadata]:

يُلاحظ في ما سبق أنه يجب تحديد القاعدة التي نريد تصدير عناصر منها. يبدأ الخيار تشغيل معالج:

1	مكان إنشاء البرنامج النصي SQL: في ملف (File) في الحافظة (Clipboard) في أداة Script Executive
2	اسم الملف في حالة اختيار الخيار [File]
3	ما الذي سيتم تصديره
4	أزرار لتحديد (->) أو إلغاء تحديد (<-) العناصر المراد تصديرها

إذا أردنا تصدير قاعدة البيانات بالكامل، فسنحدد الخيار [Extract All] أعلاه. نريد فقط تصدير الجدول BIBLIO. للقيام بذلك، باستخدام [4]، نختار الجدول [BIBLIO]، وباستخدام [2] نحدد ملفًا:

إذا توقفنا عند هذا الحد، فسيتم تصدير بنية الجدول [BIBLIO] فقط. لتصدير محتواه، يتعين علينا استخدام علامة التبويب [Data Tables]:

لنستخدم [1] لاختيار الجدول [BIBLIO]:

نستخدم [2] لإنشاء البرنامج النصي SQL:

لنقبل العرض. وهذا يتيح لنا رؤية البرنامج النصي الذي تم إنشاؤه في الملف [biblio.sql]:

/******************************************************************************/
/****          تم إنشاؤه بواسطة IBExpert 17/06/2004 22/01/20        06 15:06:13 ****/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES ISO8859_1;

CREATE DATABASE 'D:\data\serge\travail\2005-2006\polys\sql\DBBIBLIO.GDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET ISO8859_1;



/******************************************************************************/
/****                                                                 الجداول ****/
/******************************************************************************/



CREATE TABLE BIBLIO (
ID          INTEGER NOT NULL,
TITRE       VARCHAR(30) NOT NULL,
AUTEUR      VARCHAR(20) NOT NULL,
GENRE       VARCHAR(30) NOT NULL,
ACHAT       DATE NOT NULL,
PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 'Baudelaire', 'POèME', '1978-01-01', 120, 'n');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (3, 'Tintin au Tibet', 'Hergé', 'BD', '1990-11-10', 70, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (4, 'Du côté de chez Swann', 'Proust', 'ROMAN', '1978-12-08', 220.5, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (5, 'La terre', 'Zola', 'ROMAN', '1990-06-12', 55.13, 'n');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (6, 'Madame Bovary', 'Flaubert', 'ROMAN', '1988-03-12', 143.33, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (7, 'Manhattan transfer', 'Dos Passos', 'ROMAN', '1987-08-30', 352.8, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (8, 'Tintin en Amérique', 'Hergé', 'BD', '1991-05-15', 70, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (10, 'Le père Goriot', 'Balzac', 'Roman', '1991-09-01', 210, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (11, 'Poèmes saturniens', 'Verlaine', 'Poème', '1992-09-02', 200, 'o');

COMMIT WORK;



/******************************************************************************/
/****                                                     القيود الفريدة ****/
/******************************************************************************/

ALTER TABLE BIBLIO ADD CONSTRAINT UNQ1_BIBLIO UNIQUE (TITRE);


/******************************************************************************/
/****                                                           المفاتيح الأساسية ****/
/******************************************************************************/

ALTER TABLE BIBLIO ADD CONSTRAINT PK_BIBLIO PRIMARY KEY (ID);

• الأسطر من 1 إلى 3 هي تعليقات
• الأسطر من 5 إلى 12 هي لغة SQL الخاصة بـ Firebird
• الأسطر الأخرى هي من لغة SQL القياسية التي ينبغي أن يكون من الممكن إعادة تشغيلها في ملف SGBD الذي سيحتوي على أنواع البيانات المُعلنة في الجدول BIBLIO.

لنُعيد تشغيل هذا البرنامج النصي داخل Firebird لإنشاء جدول BIBLIO2 الذي سيكون نسخة مطابقة للجدول BIBLIO. وللقيام بذلك، سنستخدم [Script Executive] (Ctrl-F12):

لنقم بتحميل البرنامج النصي [biblio.sql] الذي أنشأناه للتو:

لنقوم بتعديله للاحتفاظ فقط بالجزء المتعلق بإنشاء الجدول وإدراج الصفوف. تم تغيير اسم الجدول إلى [BIBLIO2]:

CREATE TABLE BIBLIO2 (
    ID          INTEGER NOT NULL,
    TITRE       VARCHAR(30) NOT NULL,
    AUTEUR      VARCHAR(20) NOT NULL,
    GENRE       VARCHAR(30) NOT NULL,
    ACHAT       DATE NOT NULL,
    PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
    DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO BIBLIO2 (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 'Baudelaire', 'POèME', '1978-01-01', 120, 'n');
...

COMMIT WORK;

لنقم بتنفيذ هذا البرنامج النصي:

يمكننا التحقق في مستكشف قواعد البيانات من أن الجدول [BIBLIO2] قد تم إنشاؤه بالفعل وأنه يحتوي على البنية والمحتوى المتوقعين: