4. ابرازها در زبان SQL

4.1. Introduction

در اکثر دستورات SQL می‌توان از یک عبارت استفاده کرد. برای مثال، دستور SELECT را در نظر بگیرید:

syntaxe

SELECT expr1, expr2, ... from table WHERE expression

SELECT سطرهایی را که در آنها expression برقرار است انتخاب می‌کند و مقادیر expri را برای هر یک از آنها نمایش می‌دهد.

مثال‌ها

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

در این بخش، هدف ما توضیح مفهوم عبارت است. یک عبارت پایه به شکل زیر است:

operand1 opérateur operand2

یا

function(پارامترها)

مثال

در عبارت ، GENRE = 'ROMAN'

• GENRE اپرند ۱ است
• 'ROMAN' اپرند ۲ است
• مساوی (=) عملگر است

در عبارت upper(genre)

• `upper` یک تابع است
• 'type' یک پارامتر این تابع است.

ابتدا به عبارت‌هایی که شامل عملگرها هستند می‌پردازیم، سپس توابع موجود در فایربرد را معرفی می‌کنیم.

4.2. بیان‌های دارای عملگر

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

• عددی
• رشته‌ای
• تاریخ
• منطقی یا بولین

4.2.1. بیان‌هایی با عملوندهای عددی

4.2.1.1. فهرست عملگرها

فرض کنید nombre1، nombre2 و nombre3 اعداد باشند. عملگرهای زیر قابل استفاده هستند:

عملگرهای رابطه‌ای

nombre1 > nombre2	: number1 بزرگ‌تر از number2 است
nombre1 >= nombre2	: number1 بزرگتر یا مساوی number2
nombre1 < nombre2	: number1 کمتر از number2 است
nombre1 <= nombre2	: number1 کمتر یا مساوی number2 است
nombre1 = nombre2	: number1 برابر با number2 است
nombre1 != nombre2	: number1 با number2 برابر نیست
nombre1 <> nombre2	: مشابه مورد بالا
nombre1 BETWEEN nombre2 AND nombre3	: number1 در محدوده [nombre2,nombre3] است
nombre1 IN (liste de nombres)	: number1 به لیست اعداد تعلق دارد
nombre1 IS NULL	: number1 هیچ ارزشی ندارد
nombre1 IS NOT NULL	: number1 دارای مقدار است

عملگرهای عددی

nombre1 + nombre2	: جمع
nombre1 - nombre2	: تفریق
nombre1 * nombre2	: ضرب
nombre1 / nombre2	: تقسیم

4.2.1.2. عملگرهای رابطه‌ای

یک عبارت رابطه‌ای رابطه‌ای را بیان می‌کند که یا درست یا نادرست است. بنابراین نتیجه چنین عبارتی یک مقدار بولین یا منطقی است.

مثال‌ها:

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

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

SQL> select titre,prix from biblio where prix in (200,210)

4.2.1.3. عملگرهای عددی

ما با عبارت‌های حسابداری آشنا هستیم. آن‌ها نمایانگر محاسبه‌ای هستند که باید روی داده‌های عددی انجام شود. ما قبلاً با چنین عبارت‌هایی مواجه شده‌ایم: فرض می‌کنیم قیمتی که در سوابق فایل BIBLIO ذخیره شده، قیمت پیش از مالیات است. می‌خواهیم هر اوراق بهادار را به همراه قیمتش TTC با نرخ TVA معادل ۱۸٫۶ درصد نمایش دهیم:

    SELECT TITRE, PRIX*1.186 FROM BIBLIO

اگر قیمت‌ها ۳٪ افزایش یابند، دستور به صورت زیر خواهد بود:

    UPDATE BIBLIO SET PRIX = PRIX*1.03

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

1	fonctions	<---- اولویت بالاتر
۲	()
۳	* et /
4	+ et -	<---- اولویت پایین‌تر

وقتی دو عملوند با اولویت یکسان در یک عبارت ظاهر شوند، عملوندی که در سمت چپ‌تر قرار دارد، اول ارزیابی می‌شود.

مثال‌ها

بیان PRIX*TAUX+TAXES به صورت زیر ارزیابی خواهد شد (PRIX*TAUX)+TAXES. این به این دلیل است که عملگر ضرب در اول اعمال می‌شود. بیان PRIX*TAUX/100 به صورت (PRIX*TAUX)/100 ارزیابی خواهد شد.

4.2.2. بیان‌هایی با عملوندهای کاراکتری

4.2.2.1. فهرست عملگرها

می‌توان از عملگرهای زیر استفاده کرد:

فرض کنید string1، string2 و string3 نمونه‌هایی از رشته‌ها باشند.

chaine1 > chaine2	: string1 از string2 بلندتر است
chaine1 >= chaine2	: string1 بزرگ‌تر یا مساوی string2 است
chaine1 < chaine2	: string1 کمتر از string2 است
chaine1 <= chaine2	: string1 کمتر یا مساوی string2 است
chaine1 = chaine2	: string1 برابر با string2 است
chaine1 != chaine2	: string1 با string2 برابر نیست
chaine1 <> chaine2	: مشابه مورد بالا
chaine1 BETWEEN chaine2 AND chaine3	: string1 در محدوده [chaine2,chaine3]
chaine1 IN liste de chaines	: string1 به لیست رشته‌ها تعلق دارد
chaine1 IS NULL	: string1 هیچ ارزشی ندارد
chaine1 IS NOT NULL	: string1 دارای مقدار است
chaine1 LIKE modèle	: string1 با الگو مطابقت دارد

عملگر الحاق

string1 || string2 : chaine2 الحاق‌شده با chaine1

4.2.2.2. عملگرهای رابطه‌ای

مقایسه رشته‌ها با استفاده از عملگرهایی مانند <، <= و غیره به چه معناست؟

هر کاراکتر به صورت یک عدد صحیح رمزگذاری می‌شود. هنگام مقایسه دو کاراکتر، کدهای عددی آن‌ها با هم مقایسه می‌شوند. رمزگذاری مورد استفاده از ترتیب لغت‌نامه‌ای طبیعی پیروی می‌کند:

blanc<..< 0 < 1 < ...< 9 < ...< A < B <... < Z < ... < a < b < ... < z

اعداد قبل از حروف و حروف بزرگ قبل از حروف کوچک قرار می‌گیرند.

4.2.2.3. مقایسه دو رشته

رابطه «CHAT» < «CHIEN» را در نظر بگیرید. آیا این رابطه درست است یا نادرست؟ برای انجام این مقایسه، SGBD دو رشته را کاراکتر به کاراکتر بر اساس کدهای عددی آن‌ها مقایسه می‌کند. به محض اینکه دو کاراکتر متفاوت یافت شوند، رشته‌ای که کوچکتر است کوتاه‌تر از رشته دیگر در نظر گرفته می‌شود. در مثال ما، «CHAT» با «CHIEN» مقایسه می‌شود. نتایج متوالی زیر را به دست می‌آوریم:

     'CHAT    ' 'CHIEN'
----------------------------
    'C'    =    'C'
    'H'    =    'H'
     'A    '     < 'I'

پس از این مقایسه نهایی، مشخص می‌شود که رشته 'CHAT' کوتاه‌تر از رشته 'CHIEN' است. بنابراین رابطه 'CHAT' < 'CHIEN' برقرار است.

اکنون «CHAT» و «cat» را مقایسه کنیم.

     'CHAT    ' 'گربه'
--------------------------
     'C    '     < 'c'

پس از این مقایسه، رابطه «CHAT» < «cat» درست اعلام می‌شود.

مثال‌ها

SQL> select titre from biblio

SQL> select titre from biblio where upper(titre) between 'L' and 'M'

4.2.2.4. اپراتور LIKE

عملگر LIKE به صورت زیر استفاده می‌شود: chaîne LIKE modèle

این رابطه زمانی برقرار است که chaîne با modèle مطابقت داشته باشد. مورد دوم یک رشتهٔ کاراکتری است که ممکن است شامل دو کاراکتر جنگلی باشد:

%	که نشان‌دهنده هر دنباله‌ای از کاراکترها است
_	که نشان‌دهنده هر کاراکتر واحدی است

مثال‌ها

SQL> select titre from biblio

SQL> select titre from biblio where titre like 'M%';

SQL> select titre from biblio where titre like 'L_ %';

4.2.2.5. عملگر الحاق

SQL > select '[' || titre || ']' from biblio where upper(titre) LIKE 'L_ %'

4.2.3. بیان‌هایی با عملوندهای از نوع تاریخ

فرض کنید date1، date2 و date3 تاریخ باشند. عملگرهای زیر قابل استفاده هستند:

عملگرهای رابطه ای

date1 < date2	اگر date1 زودتر از date2 باشد، درست است
date1 <= date2	در صورتی که date1 قبل یا مساوی date2 باشد، درست است
date1 > date2	صحیح است اگر تاریخ۱ بعد از تاریخ۲ باشد
date1 >= date2	در صورتی که تاریخ۱ برابر یا بعد از تاریخ۲ باشد، درست است.
date1 = date2	در صورتی که date1 و date2 یکسان باشند، درست است
date1 <> date2	در صورتی که تاریخ۱ و تاریخ۲ متفاوت باشند، درست است.
date1 != date2	به همین ترتیب
date1 BETWEEN date2 AND date3	صحیح است اگر تاریخ۱ بین تاریخ۲ و تاریخ۳ باشد
date1 IN (liste de dates)	در صورتی که date1 در لیست تاریخ‌ها باشد، درست است
date1 IS NULL	در صورتی که date1 هیچ ارزشی نداشته باشد، درست است
date1 IS NOT NULL	در صورتی که date1 مقدار داشته باشد، درست است
date1 LIKE modèle	صحیح است اگر تاریخ۱ با الگو مطابقت داشته باشد
ALL,ANY,EXISTS

عملگرهای عددی

date1 - date2	: تعداد روزها بین date1 و date2
date1 - nombre	: تاریخ۲ به گونه‌ای که تاریخ۱ – تاریخ۲ = عدد
date1 + nombre	: تاریخ2 به گونه‌ای که تاریخ2 – تاریخ1 = عدد

مثال‌ها

SQL> select achat from biblio

SQL>select achat from biblio
   where achat between '01.01.1988' and '31.12.1988';

SQL> select titre, achat from biblio
where cast(achat as char(10)) like '1988'

کتاب‌های کتابخانه چند ساله هستند؟

SQL> select titre, cast('now' as date)-achat "age(jours)" from biblio

4.2.4. بیان‌هایی با عملوندهای بولی

به یاد داشته باشید که یک مقدار بولین یا منطقی دو مقدار ممکن دارد: true یا false. عملوند منطقی اغلب نتیجه یک عبارت رابطه‌ای است.

فرض کنید booléen1 و booléen2 دو مقدار بولی باشند. سه عملگر ممکن وجود دارد که به ترتیب اولویت عبارتند از:

booléen1 AND booléen2	اگر هر دو booléen1 و booléen2 درست باشند، درست است.
booléen1 OR booléen2	در صورتی که هر یک از booléen1 یا booléen2 درست باشد، درست است.
NOT booléen1	مقداری دارد که معکوس مقدار booléen1 است.

مثال‌ها

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

ما در جستجوی کتاب‌هایی در محدوده قیمتی هستیم:

SQL> select titre,genre,prix from biblio
     where prix>=130 and prix <=170

جستجوی معکوس:

SQL> select titre,genre,prix from biblio
     where prix<130 or prix >170
     order by prix asc

از اولویت عملگرها آگاه باشید!

SQL> select titre,genre,prix from biblio
  where genre='ROMAN' and prix>200 or prix<100
  order by prix asc

ما از پرانتز برای کنترل اولویت عملگرها استفاده می‌کنیم:

SQL> select titre,genre,prix from biblio
  where genre='ROMAN' and (prix>200 or prix<100)
  order by prix asc

4.3. تابع‌های از پیش تعریف‌شده‌ی Firebird

فایربرد دارای توابع از پیش تعریف‌شده است. این توابع را نمی‌توان مستقیماً در دستورات SQL استفاده کرد. ابتدا باید اسکریپت SQL <firebird>\UDF\ib_udf.sql را اجرا کنید، که در آن <firebird> به مسیر نصب Firebird اشاره دارد:

با IBExpert، به شرح زیر عمل می‌کنیم:

• ما از ابزار [Script Excecutive] که از طریق گزینه [Tools/ Script Executive] به دست آمده است، استفاده می‌کنیم:

• پس از آماده شدن ابزار، اسکریپت <firebird>\UDF\ib_udf.sql را بارگذاری می‌کنیم:

• سپس اسکریپت را اجرا می‌کنیم:

پس از انجام این کار، توابع از پیش تعریف‌شده‌ی Firebird برای پایگاه داده‌ای که هنگام اجرای اسکریپت به آن متصل بودید، در دسترس قرار می‌گیرند. برای مشاهده این موضوع، کافی است به اکسپلورر پایگاه داده بروید و روی گره [UDF] مربوط به پایگاه داده‌ای که توابع در آن وارد شده‌اند کلیک کنید:

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

و بیایید آن را به شرح زیر تعریف کنیم (کلیک راست روی جداول / جدول جدید):

بیایید یک سطر به این جدول اضافه کنیم:

اکنون بیایید به ویرایشگر SQL (F12) برویم و دستور زیر را صادر کنیم:

SQL> select cos(0) from test

که از تابع از پیش تعریف‌شده cos (کسینوس) استفاده می‌کند. دستور بالا برای هر سطر جدول TEST، مقدار cos(0) را ارزیابی می‌کند، که در این مورد شامل یک سطر واحد است. در نتیجه، به سادگی مقدار cos(0) را نمایش می‌دهد:

تابع‌های UDF (تابع‌های تعریف‌شده توسط کاربر) تابع‌هایی هستند که کاربران می‌توانند ایجاد کنند، و بنابراین کتابخانه‌هایی از تابع‌های UDF را می‌توان در وب یافت. در اینجا، ما تنها برخی از آن‌هایی را که با نسخه قابل دانلود Firebird (2005) در دسترس هستند، توصیف می‌کنیم. ما آن‌ها را بر اساس نوع غالب پارامترهایشان یا بر اساس نقششان طبقه‌بندی می‌کنیم:

• توابع با پارامترهای عددی
• توابع با پارامترهای از نوع رشته‌ای

4.3.1. توابع با پارامترهای عددی

abs(nombre)	مقدار مطلق یک عدد abs(-15) = 15
ceil(nombre)	کوچک‌ترین عدد صحیح بزرگ‌تر یا مساوی nombre ceil(15.7) = 16
floor(nombre)	بزرگ‌ترین عدد صحیح کوچکتر یا مساوی با nombre floor(14.3) = 14
div(nombre1,nombre2)	بقیه تقسیم صحیح (که نتیجه یک عدد صحیح است) nombre1 بر nombre2 قسم‌بر (7, 3) = 2
mod(nombre1,nombre2)	بقیه تقسیم صحیحی (که در آن کسر یک عدد صحیح است) از nombre1 بر nombre2 بقیه تقسیم درونی (مخرج یک عدد صحیح است) از nombre1 بر nombre2
sign(nombre)	-1 اگر nombre < 0 0 اگر nombre = 0 +۱ اگر nombre > ۰ sign(-6) = -1
sqrt(nombre)	مکعب ریشه nombre اگر nombre ≥ 0 -1 اگر nombre < 0 √(16)=4

4.3.2. توابع با پارامترهای رشته‌ای

ascii_char(number)	کاراکتر با کد ASCII nombre ascii_char(65) = 'A'
lower(string)	chaine را به حروف کوچک تبدیل می‌کند lower('INFO') = 'info'
ltrim(string)	حذف حروف اضافی سمت چپ – فاصله‌های قبل از متن در chaine حذف می‌شوند: ltrim(' kitten')='kitten'
جایگزینی(string1, string2, string3)	chaine2 را در chaine1 با chaine3 جایگزین می‌کند. replace('cat and dog','cat','**')='**at and **og'
rtrim(string1, string2)	حاشیه‌زدایی راست – مشابه ltrim اما در سمت راست rtrim('cat ')='cat'
substr(string, p, q)	یک زیر رشته از chaine که از موقعیت p شروع شده و در موقعیت q. پایان می‌یابد substr('chaton', 3, 5) = 'ato'
ascii_val(character)	کد ASCII از caractère ascii_val('A') = 65
strlen(string)	تعداد کاراکترها در chaine strlen('kitten') = 6