4. Вирази мови SQL

4.1. Introduction

У більшості команд SQL можна використовувати вираз. Візьмемо, наприклад, команду SELECT:

syntaxe

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

SELECT відбирає рядки, для яких expression є істинним, і для кожного з них відображає значення expri.

Приклади

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

У цьому розділі ми пропонуємо детально розглянути поняття виразу. Елементарний вираз має такий вигляд:

операнд1 opérateur операнд2

або

функція(параметри)

Приклад

У виразі GENRE = 'ROMAN'

• GENRE є операндом 1
• 'ROMAN' — це операнд 2
• = — це оператор

У виразі upper(genre)

• upper — це функція
• «genre» — це параметр цієї функції.

Спочатку ми розглянемо вирази з операторами, а потім ознайомимося з функціями, доступними у Firebird.

4.2. Вирази з операторами

Ми класифікуємо вирази з операторами за типом їх операндів:

• числові
• рядок
• дата
• булеві або логічні

4.2.1. Вирази з операндами числового типу

4.2.1.1. Список операторів

Нехай nombre1, nombre2, nombre3 — числа. Можна використовувати такі оператори:

Реляційні оператори

nombre1 > nombre2	: число1 більше за число2
nombre1 >= nombre2	: число1 більше або дорівнює числу2
nombre1 < nombre2	: число1 менше за число2
nombre1 <= nombre2	: число1 менше або дорівнює числу2
nombre1 = nombre2	: число1 дорівнює числу2
nombre1 != nombre2	: число1 відрізняється від числа2
nombre1 <> nombre2	: те саме
nombre1 BETWEEN nombre2 AND nombre3	: число1 лежить у проміжку [nombre2,nombre3]
nombre1 IN (liste de nombres)	: число1 належить до списку чисел
nombre1 IS NULL	: число1 не має значення
nombre1 IS NOT NULL	: число1 має значення

Арифметичні оператори

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 18,6 %:

    SELECT TITRE, PRIX*1.186 FROM BIBLIO

Якщо ціни мають зрости на 3%, команда буде такою:

    UPDATE BIBLIO SET PRIX = PRIX*1.03

У виразі можуть бути присутні кілька арифметичних операторів, а також функції та дужки. Ці елементи обробляються за різними пріоритетами:

1	fonctions	<---- має вищий пріоритет
2	()
3	* et /
4	+ et -	<---- нижчий пріоритет

Коли у виразі присутні два оператори з однаковим пріоритетом, спочатку обчислюється той, що знаходиться найдалі ліворуч у виразі.

Приклади

Вираз PRIX*TAUX+TAXES буде обчислено як (PRIX*TAUX)+TAXES. Адже саме оператор множення буде використано першим. Вираз PRIX*TAUX/100 обчислюватиметься як (PRIX*TAUX)/100.

4.2.2. Вирази з операндами типу «символ»

4.2.2.1. Список операторів

Можна використовувати такі оператори:

Нехай chaine1, chaine2, chaine3 — це зразки рядків

chaine1 > chaine2	: рядок1 довший за рядок2
chaine1 >= chaine2	: рядок1 довший або дорівнює рядку2
chaine1 < chaine2	: рядок1 менший за рядок2
chaine1 <= chaine2	: рядок1 менший або дорівнює рядку2
chaine1 = chaine2	: рядок1 дорівнює рядку2
chaine1 != chaine2	: рядок1 відрізняється від рядка2
chaine1 <> chaine2	: те саме
chaine1 BETWEEN chaine2 AND chaine3	: рядок1 знаходиться в діапазоні [chaine2,chaine3]
chaine1 IN liste de chaines	: ланцюг1 належить до списку ланцюгів
chaine1 IS NULL	: рядок1 не має значення
chaine1 IS NOT NULL	: рядок1 має значення
chaine1 LIKE modèle	: рядок1 відповідає шаблону

Оператор конкатенації

рядок1 || рядок2 : 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» та «chat».

     'CHAT    ' 'chat'
--------------------------
     'C    '     < 'c'

Після цього порівняння відношення «CHAT» < «chat» визнається істинним.

Приклади

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. Останній є символьним рядком, який може містити два символи-замінники:

%	що позначає будь-яку послідовність символів
_	що позначає 1 довільний символ

Приклади

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 <> date2	справджується, якщо date1 та date2 відрізняються.
date1 != date2	те саме
date1 BETWEEN date2 AND date3	справджується, якщо date1 знаходиться між date2 та date3
date1 IN (liste de dates)	справджується, якщо date1 міститься у списку дат
date1 IS NULL	справджується, якщо date1 не має значення
date1 IS NOT NULL	справджується, якщо date1 має значення
date1 LIKE modèle	є істинним, якщо date1 відповідає шаблону
ALL,ANY,EXISTS

Арифметичні оператори

date1 - date2	: кількість днів між date1 та date2
date1 - nombre	: дата2, для якої date1-date2=число
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. Вирази з булевими операндами

Нагадаємо, що булеве значення або логічне значення може приймати два значення: істина або хиба. Логічний операнд часто є результатом відносного виразу.

Нехай 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

Firebird має вбудовані функції. Вони не можуть бути відразу використані в командах SQL. Спочатку потрібно виконати скрипт SQL <firebird>\UDF\ib_udf.sql, де <firebird> позначає каталог інсталяції Firebird:

З IBExpert ми діємо наступним чином:

• використовуємо інструмент [Script Excecutive], отриманий за допомогою опції [Tools/ Script Executive]:

• після завантаження інструменту ми завантажуємо скрипт <firebird>\UDF\ib_udf.sql:

• потім виконуємо скрипт:

Після цього попередньо визначені функції Firebird стають доступними для тієї бази даних, до якої було встановлено з’єднання під час виконання скрипта. Щоб переконатися в цьому, достатньо перейти до оглядача баз даних і клацнути на вузлі [UDF] тієї бази, до якої було імпортовано функції:

Вище наведено функції, доступні для цієї бази даних. Щоб їх перевірити, зручно мати таблицю з одним рядком. Назвемо її TEST:

і визначимо її наступним чином (правий клік на Tables / New Table):

Додамо до цієї таблиці один рядок:

Тепер перейдемо до редактора SQL (F12) і видамо таку команду SQL:

SQL> select cos(0) from test

яка використовує попередньо визначену функцію cos (косинус). Наведена вище команда обчислює cos(0) для кожного рядка таблиці TEST, тобто фактично для одного рядка. Отже, просто відображається значення cos(0):

Функції UDF (User Defined Function) — це функції, які може створювати користувач, і в Інтернеті можна знайти бібліотеки таких функцій 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 div(7,3) = 2
mod(nombre1,nombre2)	залишок цілочисельного ділення (частка є цілим числом) nombre1 на nombre2 mod(7,3)=1
sign(nombre)	-1, якщо nombre < 0 0, якщо nombre = 0 +1, якщо nombre > 0 sign(-6) = -1
sqrt(nombre)	корінь квадратний з nombre, якщо nombre ≥ 0 -1, якщо nombre < 0 sqrt(16)=4

4.3.2. Функції з параметрами типу «рядок символів»

ascii_char(число)	символ з кодом ASCII nombre ascii_char(65) = 'A'
lower(рядок)	перетворює chaine на малі літери lower('INFO') = 'info'
ltrim(рядок)	Left Trim — пробіли перед текстом chaine видаляються: ltrim(' кошеня')='кошеня'
replace(рядок1,рядок2,рядок3)	замінює chaine2 на chaine3 у chaine1. replace('chat et chien','ch','**')='**at et **ien'
rtrim(рядок1,рядок2)	Right Trim — те саме, що ltrim, але з правого боку rtrim('кіт ')='кіт'
substr(рядок, p, q)	підрядок chaine, що починається з позиції p і закінчується на позиції q. substr('chaton', 3, 5) = 'ato'
ascii_val(символ)	код ASCII для caractère ascii_val('A') = 65
strlen(рядок)	кількість символів у chaine strlen('кошеня') = 6