3. مقدمه‌ای بر زبان SQL

در این بخش، اولین دستورات SQL برای ایجاد و کار با یک جدول واحد را ارائه می‌دهیم. ما عموماً نسخه ساده‌شده‌ای از این دستورات را ارائه می‌کنیم. سینتکس کامل آن‌ها در راهنماهای مرجع Firebird (به بخش 2.2 مراجعه کنید) موجود است.

یک پایگاه داده توسط افرادی با مهارت‌های گوناگون استفاده می‌شود:

• مدیر پایگاه داده معمولاً فردی ماهر در زبان SQL و پایگاه‌های داده است. این مدیر است که جداول را ایجاد می‌کند، زیرا این عملیات معمولاً فقط یک بار انجام می‌شود. با گذشت زمان، ممکن است نیاز به تغییر ساختار جداول پیدا کند. یک پایگاه داده مجموعه‌ای از جداول است که با روابط به هم متصل شده‌اند. این مدیر پایگاه داده است که این روابط را تعریف می‌کند. آنها همچنین مسئول اعطای مجوز به کاربران مختلف پایگاه داده هستند. به عنوان مثال، آنها مشخص می‌کنند که کاربر خاصی مجاز به مشاهده محتویات یک جدول است اما اجازه تغییر آن را ندارد.
• کاربر پایگاه داده کسی است که به داده‌ها جان می‌بخشد. بسته به حقوق اعطا شده توسط مدیر پایگاه داده، آن‌ها داده‌ها را در جداول مختلف پایگاه داده اضافه، اصلاح یا حذف خواهند کرد. آن‌ها همچنین داده‌ها را تحلیل می‌کنند تا اطلاعات مفیدی را برای پیشبرد روان کسب‌وکار، امور اداری و غیره استخراج کنند.

در بخش ۲.۶، ویرایشگر SQL را از ابزار [IB-Expert] معرفی کردیم. این ابزاری است که ما از آن استفاده خواهیم کرد. بیایید چند نکته را مرور کنیم:

• می‌توان از طریق گزینه منوی [Tools/SQL Editor] یا از طریق کلید [F12] به ویرایشگر SQL دسترسی داشت

این باعث باز شدن پنجره‌ای [SQL Editor] می‌شود که در آن می‌توانیم یک فرمان SQL را وارد کنیم:

اسکرین‌شات بالا اغلب با متن زیر نمایش داده می‌شود:

SQL> select * from BIBLIO

3.1. انواع داده‌های فایربرد

هنگام ایجاد یک جدول، باید نوع داده‌ای را که یک ستون جدول می‌تواند در خود داشته باشد مشخص کنیم. در اینجا رایج‌ترین انواع داده Firebird را تشریح می‌کنیم. لطفاً توجه داشته باشید که این انواع داده ممکن است از یک SGBD به 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	عدد حقیقی با تقریب به ۷ رقم معنی‌دار: ۱۰.۴
DOUBLE PRECISION	عدد حقیقی با تقریب ۱۵ رقم معنادار: -100.89
CHAR(N) CHARACTER(N)	یک رشته دقیقاً شامل N کاراکتر. اگر رشته ذخیره‌شده کمتر از N کاراکتر داشته باشد، با فضاها پُر می‌شود. CHAR(10): 'ANGERS ' (۴ فضای اضافی در انتها)
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. ایجاد یک جدول

برای اینکه بدانیم چگونه یک جدول ایجاد کنیم، ابتدا با استفاده از IBExpert در حالت [Design] یک جدول ایجاد می‌کنیم. برای این کار، روش توصیف‌شده در بخش 2.3 را دنبال می‌کنیم. بدین ترتیب جدول زیر را ایجاد می‌کنیم:

این جدول برای ثبت کتاب‌های خریداری‌شده توسط یک کتابخانه استفاده خواهد شد. فیلدها به شرح زیر تعریف شده‌اند:

نام	نوع	محدودیت	معنی
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);

• خط ۱: Firebird owner – نشان‌دهنده سطح گویش SQL است
• خط ۲: مالک Firebird – مجموعه کاراکتر مورد استفاده را مشخص می‌کند
• خطوط ۶–۱۴: استاندارد SQL: جدول BIBLIO را با تعریف نام و نوع داده هر یک از ستون‌های آن ایجاد می‌کند.
• خط ۱۶: استاندارد SQL: یک قید ایجاد می‌کند که مشخص می‌کند ستون TITRE اجازه تکرار ندارد
• خط ۱۷: استاندارد 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 : هیچ مقدار 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] می‌نامیم:

پس از اجرا، باید تراکنش را commit کنید تا نتیجه را در پایگاه داده مشاهده کنید:

پس از انجام این کار، جدول در پایگاه داده ظاهر می‌شود:

با دوبار کلیک روی نام آن، می‌توانید ساختار آن را مشاهده کنید:

می‌توانیم ببینیم که این جدول با تعریفی که برای جدول [BIBLIO2] ایجاد کردیم مطابقت دارد.

3.3. حذف یک جدول

دستور SQL برای حذف یک جدول به شرح زیر است:

syntaxe	DROP TABLE table
action	Supprime [table]

برای حذف جدول [BIBLIO2] که همین حالا ایجاد کرده‌ایم، اکنون دستور زیر را اجرا می‌کنیم: SQL

و با دستور [Commit] آن را تأیید کنید. جدول [BIBLIO2] حذف شد:

3.4. پر کردن یک جدول

بیایید یک سطر به جدول [BIBLIO] که همین حالا ایجاد کرده‌ایم اضافه کنیم:

افزودن ردیف را با استفاده از [Commit] تأیید کنید، سپس روی ردیف اضافه شده کلیک راست کنید:

و همانطور که در بالا نشان داده شده است، درخواست می‌کنیم که سطر درج‌شده به شکل یک دستور به کلیپ‌بورد کپی شود: SQL INSERT. سپس، هر ویرایشگر متنی را باز کرده و آنچه را که به تازگی کپی کرده‌ایم، الصاق کنید. کد زیر را به دست می‌آوریم: SQL

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

سینتکس دستور درج به شرح زیر است:

syntaxe	وارد کنید به جدول [(colonne1, colonne2, ..)] مقادیر (value1, value2, ....)
action	یک ردیف (value1, value2, ..) به table اضافه می‌کند. این مقادیر در صورت وجود به ستون‌های colonne1, colonne2, ... تخصیص داده می‌شوند؛ در غیر این صورت، به ستون‌های جدول به ترتیبی که تعریف شده‌اند اختصاص می‌یابند.

برای درج ردیف‌های جدید در جدول [BIBLIO]، دستورات زیر INSERT را در ویرایشگر SQL وارد کنید. این دستورات را یکی‌یکی با استفاده از [Commit] اجرا و تأیید کنید. برای رفتن به دستور بعدی، [New Query]، از دکمه [New Query] استفاده کنید.

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');

پس از تأیید سفارش‌های مختلف [Commit]، جدول زیر را به دست می‌آوریم:

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'

یکی از کتاب‌ها به جای «Novel» در دسته «novel» قرار گرفته است. ما از تابع upper استفاده می‌کنیم که یک رشته را به حروف بزرگ تبدیل می‌کند تا همه رمان‌ها را بازیابی کنیم.

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

ما می‌توانیم شرایط را با استفاده از عملگرهای منطقی ترکیب کنیم

AND	ET logique
OR	OU logique
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	رده‌های حاصل از انتخاب به ترتیب ۱: ترتیب صعودی (asc – پیش‌فرض) یا نزولی (desc) expression1 ۲: در صورت تساوی برای 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	update table set column1 = expression1, column2 = expression2, ... [where condition]
action	برای سطرهای موجود در table که شرایط موجود در condition را برآورده می‌کنند (تمام سطرها در صورتی که هیچ شرطی وجود نداشته باشد)، مقدار expressioni به colonnei اختصاص داده می‌شود.

مثال‌ها:

SQL> select genre from biblio

تمام بخش‌های کد با حروف بزرگ نوشته شده‌اند:

SQL> update biblio set genre=upper(genre)

ما بررسی می‌کنیم:

SQL> select genre from biblio

ما قیمت‌ها را نمایش می‌دهیم:

SQL> select genre,prix from biblio;

قیمت رمان‌ها ۵ درصد افزایش می‌یابد:

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

ببینیم:

SQL> select genre,prix from biblio

3.8. به‌روزرسانی نهایی یک جدول

وقتی تغییراتی در یک جدول اعمال می‌شود، فایربرد در واقع آن‌ها را روی یک نسخهٔ کپی از جدول اعمال می‌کند. این تغییرات سپس می‌توانند با استفاده از دستورات COMMIT و ROLLBACK دائمی یا لغو شوند.

syntaxe	COMMIT
action	تغییراتی را که از آخرین اجرای COMMIT روی جداول اعمال شده، دائمی می‌کند.

syntaxe	ROLLBACK
action	تمام تغییرات اعمال‌شده بر روی جداول را از آخرین اجرای COMMIT بازمی‌گرداند.

Remarque

یک COMMIT به‌طور ضمنی در زمان‌های زیر انجام می‌شود: الف) هنگام خروج از فایربرد b) پس از هر دستوری که ساختار جداول را تغییر می‌دهد: CREATE, ALTER, DROP.

مثال‌ها

در ویرایشگر SQL، پایگاه داده با commit کردن تمام عملیات انجام شده از آخرین 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) در آن جای‌گذاری کنیم:

یک فهرست از دستورات اغلب به عنوان یک اسکریپت نامیده می‌شود. [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 columnA, columnB, ... FROM table2 WHERE condition
action	رده‌های table2 که با condition مطابقت دارند به table1 اضافه می‌شوند. ستون‌های colonnea، colonneb و ... از table2 به ترتیب به column1، column2 و ... در table1 اختصاص داده می‌شوند و بنابراین باید از نوع سازگار باشند.

بیایید به ویرایشگر SQL بازگردیم:

و دستور زیر را صادر کنید: SQL:

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

که تمام خطوط مربوط به یک رمان را از [BIBLIO] در [BIBLIO2] درج می‌کند. پس از اجرای دستور SQL، بیایید آن را با [Commit] تأیید کنیم:

SQL> commit

پس از انجام این کار، بیایید داده‌ها را در جدول [BIBLIO2] مشاهده کنیم:

SQL> select * from BIBLIO2

3.10. حذف یک جدول

syntaxe	DROP TABLE table
action	supprime 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 column1, column2, ... FROM table WHERE condition [ WITH CHECK OPTION ]
action	نما nom_vue را ایجاد می‌کند. این یک جدول با ساختار column1، column2، … از 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 را روی آن اجرا کنید. در اینجا چند مثال برای امتحان کردن در ویرایشگر SQL آورده شده است:

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

تعداد رمان‌ها در [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 ابتدا هر ردیفی را که شرط را برآورده نمی‌کند از جدول حذف می‌کند. سپس گروه‌ها با عبارت 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 genre HAVING genre!='ROMAN'

توابع آماری زیر در دسترس هستند:

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'

چند رمان قیمتشان کمتر از ۱۰۰ فرانک است؟

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'

همان سؤال، اما فقط برای کتاب‌هایی با قیمت کمتر از ۱۵۰ فرانک:

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 F باشد.

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

3.14. اسکریپت SQL را برای یک table ایجاد کنید

زبان SQL یک زبان استاندارد است که می‌توان آن را با بسیاری از اسکریپت‌های SGBD استفاده کرد. برای تغییر از یک اسکریپت SGBD به اسکریپت دیگر، مفید است که یک پایگاه داده یا به سادگی برخی از عناصر آن را به صورت یک اسکریپت SQL صادر کنیم که با اجرای مجدد آن در یک SGBD دیگر، قادر به بازآفرینی عناصری باشد که در اسکریپت صادر شده‌اند.

در اینجا قصد داریم جدول [BIBLIO] را صادر کنیم. بیایید گزینه [Extract Metadata] را انتخاب کنیم:

توجه داشته باشید که همانطور که در بالا نشان داده شده است، باید در پایگاه داده‌ای باشید که می‌خواهید عناصر آن را صادر کنید. این گزینه یک جادوگر را اجرا می‌کند:

1	محل تولید اسکریپت SQL: به یک فایل (File) به کلیپ‌بورد در ابزار Script Executive
2	نام فایل در صورتی که گزینه [File] انتخاب شده باشد
3	چه چیزی را صادر کنیم
4	دکمه‌ها برای انتخاب (->) یا لغو انتخاب (<-) اشیاء قابل صادر

اگر می‌خواستیم کل پایگاه داده را صادر کنیم، گزینه [Extract All] را در بالا تیک می‌زدیم. ما صرفاً می‌خواهیم جدول BIBLIO را صادر کنیم. برای این کار، با استفاده از [4]، جدول [BIBLIO] را انتخاب کرده و با استفاده از [2]، یک فایل را مشخص می‌کنیم:

اگر در اینجا متوقف شویم، تنها ساختار جدول [BIBLIO] صادر خواهد شد. برای صادر کردن محتویات آن، باید از برگه [Data Tables] استفاده کنیم:

بیایید از [1] برای انتخاب جدول [BIBLIO] استفاده کنیم:

بیایید از [2] برای تولید اسکریپت SQL استفاده کنیم:

بیایید پیشنهاد را بپذیریم. این به ما امکان می‌دهد تا اسکریپتی را که در فایل [biblio.sql] تولید شده است مشاهده کنیم:

/******************************************************************************/
/****          تولید شده توسط IBExpert ۱۷ ژوئن ۲۰۰۴ ۲۲ ژانوی        ه ۲۰۰۶ ۱۵:۰۶:۱۳ ****/
/******************************************************************************/

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);

• خطوط ۱ تا ۳ توضیحات هستند
• خطوط ۵ تا ۱۲ مربوط به SQL مخصوص فایربرد هستند
• خطوط باقی‌مانده کد استاندارد SQL هستند که باید قابل اجرا مجدد در فایلی با نام SGBD باشند که در آن انواع داده‌ها در جدول BIBLIO اعلام شده است.

بیایید این اسکریپت را در فایربرد مجدداً اجرا کنیم تا جدول 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] واقعاً ایجاد شده و ساختار و محتوای مورد انتظار را دارد: