Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
PAGE 2
7 Запити
7.1 Створення запиту
Запит являє собою команду, написану мовою SQL, яка вибирає дані з таблиць. Якщо запит виготовляється візуальним способом, то інтерпретатор складає його скрипт у вигляді тексту, написаний теж мовою SQL. Скрипт вигідний тим, що його можна редагувати, він також застосовується на стадії вивчення мови SQL. На відміну від інших мов програмування, запит вказує серверу, які дані потрібно знайти, але не повідомляє, як це зробити. Слід особливо наголосити на тому, що запит – команда, але не вибірка даних.
У підручниках з SQL терміни “команда” і “запит” часто вважаються еквівалентними. Тут термін “запит” стосується тільки команд, які повертають дані, але не до загальних команд SQL, які створюють або модифікують дані.
Дані, повернені запитом, називаються підсумковим набором. Як і таблиця, він складається із записів та полів, тобто виглядає так, як таблиця. Вибірка не дублює таблицю, але містить її задану частину або всю інформацію, вона формується заново при кожному відкритті (запуску) запиту. Підсумковий набір не зберігається на диску, це тимчасові дані. Він може містити поля як із однієї, так з декількох таблиць.Для одержання вибірки служить команда SELECT, яка має такий вигляд (у скороченому варіанті):
SELECT список _імен_полів FROM ім’я_джерела
WHERE умова_відбору_записів
GROUP BY критерій групування
Тут службові слова SELECT і FROM – обов’язкові.
WHERE дозволяє обмежити набір записів. Якщо використовуються всі поля джерела, то замість їх переліку у списку імен полів запиту вживається символ зірочка.
Секція GROUP BY дозволяє групувати записи за заданим критерієм (наприклад, підсумувати кількість транспортованого газу для кожного газопроводу).
Приклад простого запиту: видати всі поля всіх записів таблиці gazpr, де діаметр однієї нитки (поле diam) менший за 1800:
SELECT * FROM gazpr WHERE diam < 1800;
Як бачимо, запит може містити вирази. До складу виразу можуть входити:
Нижче поданий перелік та опис цих складників (нагадаємо, що константи та імена полів і таблиць вже розглядалися в попередніх розділах).
Оператори зазвичайно містять від одного до чотирьох символів. Слід зауважити, що деякі з них існують тільки у версії SQL для PostgreSQL і можуть не бути в інших СУБД.
Об’єднання двох рядкових констант у один виконується оператором конкатенації (||), наприклад, так:
SELECT 'Ра' || 'кета' AS example;
example
------------
Ракета
Крім, власне демонстрації злиття двох рядків, у цьому прикладі показано застосування альтернативного імені example, яке тут дозволяє підвищити наглядність результату.
Строкові оператори служать для порівняння двох рядків (або чисел), вони такі:
|
Оператор |
Опис |
|
= |
Повертає True, якщо перший рядок точно збігається з другим |
|
! = |
Повертає True, якщо перший рядок не збігається з другим |
|
<> |
Ідентичний оператору ! = |
|
< |
Повертає True, якщо перший рядок передує другому |
|
<= |
Повертає True, якщо перший рядок передує другому або їх значення співпадають |
|
> |
Повертає True, якщо другий рядок передує першому |
|
>= |
Повертає True, якщо другий рядок передує першому або їх значення співпадають |
Оператори регулярних виразів розширюють можливості строкових операторів, вони такі:
~ – перевіряє (видає true, якщо так) чи в рядку існує збіг для регулярного виразу;
!~ – перевіряє чи в рядку немає збігу для регулярного виразу;
~* – перевіряє чи в рядку існує збіг без урахування регістру символів;
!~* – перевіряє чи в рядку немає збігу без урахування регістру символів.
Регулярний вираз може містити такі метасимволи (спеціальні символи):
^ – з початку рядка;
$ – наприкінці рядка;
. – один символ;
[ ] – будь-який із символів, перелічених у квадратних дужках;
[^] – будь-який символ, крім перелічених у квадратних дужках;
[-] – будь-який символ з інтервалу, заданого в квадратних дужках;
[^-] – будь-який символ, крім символів з інтервалу, заданого в квадратних дужках;
? – один примірник попереднього символа або підвиразу (якщо, звичайно, він є) ;
* – довільна кількість примірників попереднього символа або підвиразу;
+ – один і більше примірників попереднього символа або підвиразу;
| – лівий або правий підвираз;
( ) – групування підвиразів з явним визначенням пріоритету операцій;
Нижче наведено декілька прикладів застосування регулярних виразів для видачі:
SELECT name FROM authors WHERE name ~ '^A|^T';
SELECT title FROM books WHERE title ~*'Кобзар';
SELECT title FROM books WHERE title ~* '^ Кобзар.( малий | повний)';
SELECT title FROM books WHERE title ~* '(^К.*[о,м]к)|(ий$|не$)';
Математичні оператори використовуються в числових виразах, вони такі:
|
а + b |
Підсумовування чисел а і b |
|
а - b |
Віднімання числа b від а |
|
а * b |
Множення числа а на b |
|
а / b |
Ділення числа а на b |
|
а % b |
Залишок від ділення а на b |
|
а ^ b |
Піднесення а до степеня b |
|
|/ а |
Квадратний корінь з а |
|
||/ а |
Кубічний корінь з a |
|
а! |
Факторіал а |
|
!!а |
Факторіал а (відрізняється від постфіксного оператора тільки розташуванням аргумента а) |
|
@а |
Модуль (абсолютне значення) а |
Приклад застосування операції множення: видати коди газопроводів та кількість транспортованого газу за весь період обліку, збільшену в 1.6 разів (так начислюється премія персоналу, який обслуговує газопровід) і названу premia, взяті з таблиці oblik:
SELECT kod_g, (1.6 * kilk)::numeric(8, 2) AS premia FROM oblik;
PostgreSQL має такі варіанти зміни типів даних:
тип 'символьна константа'
'символьна константа':: тип
числова константа:: тип
ідентифікатор:: тип
CAST (‘символьна константа' AS тип)
CAST (числова константа AS тип)
CAST (ідентифікатор AS тип)
Математичні оператори порівняння такі ж, як вищерозглянені строкові.
Ключове слово BETWEEN (також іноді називається оператором) дозволяє перевірити, чи входить значення в деякий інтервал. Наприклад, нищенаведена команда SELECT видає книги, ціна яких знаходиться в інтервалі між 10 та 17:
SELECT naz_kniga FROM kniga WHERE cina BETWEEN 10 AND 17;
Двійкові оператори виконують порозрядні операції з бітовими послідовностями або цілими числами, вони аналогічні до мови С. Приклад зсуву числа 8 (двійкове 1000) на два розряди вправо та перетворення одержаної бітової послідовності в цілочисловий тип функцією bittoint4():
SELECT bittoint4(b'1000' >> 2);
Пріоритет операторів SQL (у порядку спадання):
|
Оператор |
Опис |
|
:: |
Явне перетворення типу |
|
[ ] |
Індексація елемента масиву |
|
. |
Крапка , роздільник імен таблиці і поля |
|
– |
Унарний мінус |
|
^ |
Піднесення до степеня |
|
* / % |
Множення, ділення і залишок |
|
+ – |
Додавання і віднімання |
|
IS значення |
Перевірка наявності заданого значення |
|
IS NULL |
Перевірка наявності значення NULL |
|
IS NOT NULL |
Перевірка розбіжності з NULL |
|
IN перелік |
Задання набору значень |
|
BETWEEN |
Перевірка приналежності до інтервалу |
|
LIKE ILIKE |
Вибір за зразком |
|
<> <= >= = |
Оператори порівняння |
|
AND ОR NOT |
Логічні оператори |
Функції
Аргумент функцій записується в круглих дужках. Вони не обов’язкові тільки для функцій SQL92 current_date, current_time і current_timestamp. У PostgreSQL ці функції також викликаються без круглих дужок, щоб забезпечити сумісність зі специфікацією SQL92.
Приклад видачі версії PostgreSQL:
SELECT version(); -- PostgreSQL 8.4.7 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.
real (Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 32-bit
Математичні функції:
|
Функція |
Опис |
|
abs(x) |
Модуль (абсолютне значення) х |
|
acos(x) |
Арккосинус х |
|
asin(x) |
Арксинус х |
|
atan(x) |
Арктангенс х |
|
cbrt(x) |
Кубічний корінь х |
|
ceil(x) |
Заокруглення х до цілого у верхню сторону |
|
cos(x) |
Косинус х |
|
cot(x) |
Котангенс х |
|
degrees(r) |
Кількість градусів у r радіан |
|
exp(x) |
Експонента числа x |
|
floor(x) |
Заокруглення х до цілого в нижню сторону) |
|
ln(x) |
Натуральний логарифм х (функція, зворотна ехр (х)) |
|
log(b.x) |
Логарифм х з основою b |
|
log(x) |
Десятковий логарифм х |
|
mod(x, y) |
Залишок від ділення х на у |
|
pi() |
Константа π=3,1415926... |
|
pow(x, y) |
Піднесення х до степеня у |
|
radians(d) |
Кількість радіан в d градусів |
|
random() |
Видача псевдовипадкового числа в інтервалі від 0,0 до 1,0 |
|
round(x) |
Зокруглення числа х до найближчого цілого |
|
sin(x) |
Синус х |
|
sqrt(x) |
Квадратний корінь з х |
|
tan(x) |
Тангенс |
|
trunc(x) |
Ціла частина числа х |
|
trunc(x, s) |
Заокруглення числа x до s цифр у дробовій частині |
Приклад видачі cos(π/3):
SELECT cos(pi()/3) AS kosinus;
Рядкові функції видають такі значення:
ascii(s) – ASCII-код символа строкової змінної s;
btrim(s[, t]) – видалення на початку і наприкінці рядка s всіх символів, які входять у рядок t.
Якщо аргумент t не заданий, то усікаються пробіли, символи табуляції і т. д.);
char_length(s) – довжина рядка s у символах (стандарт SQL92);
chr(n) – символ з ASCII-кодом n (зворотня функція до ascii(s));
s ilike(f) – перевіряє чи вираз f збігається (без урахування регістра символів) з s;
initcap(s) – рядок s, в якій перша буква кожного слова перетвориться до верхнього регістру;
length(s) – довжина рядка s у символах;
s like(f) – перевіряє чи вираз f збігається з s;
lower(s) – рядок s, перетворений до нижнього регістру (стандарт SQL92);
lpad(s , n [, c]) – рядок s, доповнений зліва вмістом рядка с (або пробілами, якщо аргумент с
не заданий) до довжини n (або усічену зправа до n символів);
ltrim(s [, f]) – рядок s, на початку якого видалені всі символи, що входять в рядок f (якщо
аргумент f не заданий, то усікаються початкові пробіли, символи табуляції і т. д.);
octet_length(s) – довжина рядка символів s у байтах (стандарт SQL92);
position(b IN s) – позиція підрядка b в рядку s (відлік починається з 1 (стандарт SQL92));
repeat(s , n) – повторення рядка s n разів;
rpad(s , n [, c]) – рядок s, доповнений зправа вмістом рядка c (або пробілами, якщо аргумент c
не заданий) до довжини n (або усічений зліва до n символів);
rtrim(s [, f ]) – рядок s, наприкінці якого видалені всі символи, що входять в рядок f (якщо
аргумент f не заданий, то усікаються кінцеві пробіли, символи табуляції і т. д.);
strpos(s , b) – позиція підрядка b в рядку s (відлік починається з 1). Належить до функцій
PostgreSQL і дублює функцію SQL position(), але з передачею аргументів у стилі C;
substr(s , n[, l]) – виділення підрядка з рядка s, починаючи з позиції n. Необов’язковий
аргумент І визначає максимальну довжину підрядка в символах. Належить до функцій
PostgreSQL і дублює функцію SQL substring(), але з передачею аргументів у стилі C;
substring(s FROM n FOR l) – виділення підрядка з рядка s, починаючи з позиції n.
Необов’язковий аргумент І визначає максимальну довжину підрядка в символах
(стандарт SQL92);
to_ascii(s, f) – рядок s, перетворений з розширеним кодуванням f в ASCII;
translate(s, f, r) – рядок s, у якому всі символи, які входять у рядок f, замінюються символами
рядка r;
trim(напрямок f FROM s) – рядок s, на початку і/або наприкінці якого видалені всі символи,
що входять в рядок f. Напрямок задається словами SQL: LEADING, TRAILING, BOTH.
(стандарт SQL92, здатна замінити функції PostgreSQL btrim(), ltrim() і rtrim());
upper(s) – рядок s, перетворений до верхнього регістра.
Приклад видачі ASCII-коду букви “Т”:
SELECT ascii('T');
Функції для роботи з датою і часом:
current_date – поточна дата типу date (стандарт SQL92, функція не має дужок);
current_time – поточний час типу time (стандарт SQL92, не має дужок);
current_timestamp – поточна дата і час типу timestamp (стандарт SQL92, не має дужок);
date_part(s, t) – компонента дати або часу, визначена рядком s, із значення типу timestamp;
date_part(s, i) – компонента дати або часу, визначена рядком s, із значення типу interval;
date_trunc(s, t) – значення типу timestamp, усічене до точності s;
extract(k FROM t) – компоненту дати або часу, визначену k, із значення t типу timestamp;
extract(k FROM i) – компоненту дати або часу, визначену k, із значення t типу interval;
isfinite(t) – перевірка чи значення типу timestamp invalid або infinity;
isfinite(i) – перевірка чи значення типу interval не infinity;
now() – поточна дата і час у вигляді значення типу timestamp. Еквівалент константи now;
timeofday() – поточна дата і час у вигляді значення типу text.
Приклади окремих форматів дати:
ISO 2001-06-25
SQL 06/25/2001
PostgreSQL Моn 25 Jun
German 25.06.2001
Приклад видачі поточної дати і часу типу timestamp, поточної дати типу date та порядковий номер поточного місяця:
SELECT now();
SELECT current_date;
SELECT extract(month FROM current_date);
Функції перетворення типів.
Як було зазначено вище, PostgreSQL має операції для перетворення ти пів, однак, деякі перетворення зручніше виконувати за допомогою функцій. Вони такі:
bitfromint4(n) – число n на бітову послідовність;
bittoint4(b) – бітову послідовність на десяткове число;
to_char(n, f) – число в рядок формату f;
to_char(t, f) – значення типу timestamp в рядок формату f;
to_date(s, f) – рядок f формату дати в значення типу date;
to_number(s, f) – рядок f формату дати в значення типу numeric;
to_timestamp(s, f) – рядок f формату дати в значення типу timestamp;
timestamp(d) – значення типу date до типу timestamp;
timestamp(d. t) – два значення типів date і time до типу timestamp.
Приклад перетворення бітової послідовності 101010 на десяткове число:
SELECT bittoint4(B'101010');
Деякі інші функції, наприклад text(), теж дозволяють змінити тип, наприклад, перетворимо число 1000 на текст:
SELECT text (1000) AS explicit_text;
explicit_text
----------------------
1000
При форматуванні перетворених даних можуть застосовуватися такі метасимволи:
9 – цифра;
. – крапка, відокремлює цілу частину числа від дробової;
, – кома, використовується для розділення груп розрядів (тисячі, мільйони і т. д.);
D – десятковий роздільник (наприклад, крапка);
G – роздільник груп розрядів (наприклад, кома);
SG – знак плюс (+) або мінус (-) залежно від значення;
Ml – знак мінус (-), якщо число від’ємне;
PL – знак плюс (+), якщо число додатнє;
S – знак плюс (+) або мінус (-), визначений в локальному контексті;
L – грошовий знак, визначений в локальному контексті;
RN – римські цифри для значень в інтервалі від 1 до 3999;
FM – видалити всі початкові і прикінцеві нулі, створені символами 9.
Приклад перетворення числа 123456789 на рядок, у якому тисячі розділити комами і представлення його як дійсне з двома знаками (тут – нулями) після крапки:
SELECT to_char(123456789, 'PL999G999G999D99'); -- + 123 456 789,00
Агрегатні функції застосовуються в запитах для групування даних, вони такі:
avg(вираз) – середнє арифметичне значення;
count(вираз) – кількість записів зі значенням відмінним від NULL;
mах(вираз) – максимальне значення;
min(вираз) – мінімальне значення;
stddev(вираз) – середньоквадратичне відхилення;
sum(вираз) – сума;
variаnce(вираз) – дисперсія.
Наведемо ще декілька прикладів запиту:
SELECT b'10000' >> 2; - - = 4
SELECT * FROM gazpr WHERE naz_g ~ 'есн';
SELECT * FROM gazpr WHERE naz_g ~ '[г, ч]';
- - почин (^) на “Є” без врах рег (*), а далі через довільне число символів (.)
- - зустріч буква з переліку ([р, К]) , наступною за якою стоїть буква “е” :
SELECT * FROM gazpr WHERE naz_g ~* '(^Є.*[р, К]е)'; - - Єлецьк-Орел
SELECT * FROM gazpr WHERE naz_g ='Україна';
SELECT * FROM gazpr WHERE substring(naz_g FROM 2 FOR 1) = 'л';
SELECT * FROM gazpr WHERE substring(naz_g FROM 3 FOR 2) = 'ец';
SELECT ||/ 27; - - 3
SELECT to_char(123456789, 'PL999G999G999D99');
SELECT to_char(-23456789, '999G999G999D99'); - - дає мінус
SELECT to_char(3123, 'rn'); - - mmmcxxiii
SELECT length(to_char(128,'999')); - - 4
SELECT length(to_char(128,'99999')); - - 6