Fondovi-lekcii

ЗМІСТ

Вступ...............................................................................................................................................3

МОДУЛЬ 1.....................................................................................................................................5

Тема 1. Вступ до SQL. Синтаксис SQL. Типи даних............................................5

Тема 2. Створення баз даних.................................................................................19

Тема 3. Створення таблиць....................................................................................21

Тема 4. Вставка стрічок з допомогою оператора INSERT. Вилучення стрічок з

допомогою оператора DELETE, редагування стрічки з допомогою команди

UPDATE..................................................................................................................24

Тема 5. Оператор SELECT.....................................................................................35

Тема 6. Комбінування умов з допомогою операторів AND, OR, NOT.

Порівняння по шаблону. Порівняння з діапазоном. Сортування стрічок з

допомогою речення ORDER BY...........................................................................39

Тема 7. Створення псевдонімів. З’єднання таблиці із собою. Вибір даних з

кількох таблиць.......................................................................................................45

Тема 8. Введення обмежень в базах даних..........................................................59

Тема 9. Використання збережуваних процедур в базах даних. Використання

генераторів ключів у базах даних в InterBase. Використання тригерів у базах

даних........................................................................................................................65

Тема 10. Безпека в базах даних.............................................................................78

МОДУЛЬ 2...................................................................................................................................82

Тема 11. Оператор EXISTS. Сумування і групування даних.............................82

Тема 12. З’єднання з базою даних. Використання збережуваних процедур в

Delphi.......................................................................................................................99

Тема 13. Використання механізму транзакцій компонента Database..............104

Тема 14. Сервер баз даних InterBase і компоненти InterBase Express.............107

Тема 15. Виконання запитів за допомогою компонента TIBDataSet...............110

Тема 16. Підпорядковані запити в таблицях......................................................114

Література...................................................................................................................................116

2

ВступБагато хто вважає, що поєднання трьох букв S-Q-L є абревіатура, яка

розшифровується як Structured Query Language (Структурована мова запитів). Так от, SQL

означає SQL, і більш нічого. Чому? Так тому, що так прийнято в ANSI. Офіційна назва

SQL – Database Language SQL (Мова баз даних SQL). Особливу увагу на дану помилку

звертають лише тому, що вона створює помилки. Якщо програмісти, початківці, будуть

вважати, що SQL і справді означає “мова структурованих запитів”, це стане їм у поганій

пригоді, оскільки саме ця мова є найгіршою зі всіх можливих описів канонічного SQL,

хоча живучість даного опису здається академікам і професіоналам забавною із наступних

причин:

SQL не структурована мова, оскільки його не можна розбити на блоки або

процедури;

SQL не обмежується тільки запитами, оскільки в ньому є багато інших команд

крім SELECT;

SQL не є повною мовою за визначенням Тюрінга.

SQL нікому не належить. Це означає, що на права власності ніхто не претендує.

SQL – це відкритий стандарт, що розроблявся одним із комітетів в Американському

національному інституті стандартизації (ANSI). У свою чергу, ANSI – це не приватна

фірма, а орган уряду США, покликаний сприяти розвитку національних стандартів.

Більше того, SQL є не тільки американським, але і міжнародним стандартом ISO/IEC

9075:1992, оскільки в 1992 році був включений в число стандартів Міжнародної

організації по стандартизації (ISO). В цьому зв'язку хотілося б зазначити, що справжнє

видання засновано на стандарті ANSI SQL 1992. Таким чином, коли ви зустрінете

позначення ANSI SQL, SQL-92 або просто SQL, їх слід розглядати як синоніми, якщо

додатково не вказано нічого іншого. Положення дещо ускладнюється тим, що з'явився

стандарт ANSI SQL-99. Проте на нього поки можна не звертати уваги, оскільки до його

впровадження пройде ще декілька років і він буде сумісний із стандартом SQL-92. Тобто,

програма, що відповідає стандарту SQL-92, буде працювати під будь-якою СУБД, що

відповідає стандарту SQL-99.

Слід зазначити, що всі постачальники СУБД по-різному вносять свої доповнення в

канонічний ANSI SQL, щоб зробити його більш могутнім. Частіше всього такими

розширеннями є додаткові команди, ключові слова, функції, типи даних і конструкції

управляючої логіки. Іноді, правда, цих розширень виявляється дуже багато. Зокрема,

Oracle і Microsoft вже внесли так багато доповнень, що з'явилися PL/ SQL і Transact-SQL –

3

самостійні повноправні мови, а не підмножини SQL. Проте звичайно розширення одного

постачальника несумісні з розширеннями іншого.

Хронологія розвитку SQL:

1970 р. – Кодд опублікував статтю про реляційну модель.

1974 р. – мова SEQUEL.

1979 р. – реляційна СУБД Oracle.

1986 р. – організація ANSI прийняла SQL1.

1987 р. – організація ISO прийняла SQL1.

1989 р. – SQL1 (SQL-89).

1992 р. – ANSI впровадила SQL2 (SQL-92).

1992 р. – SQL3.

2002 р. – IBM створила СУБД DB2.

4

МОДУЛЬ 1Тема 1. Вступ до SQL. Синтаксис SQL. Типи даних.

1. Вступ до SQL.2. Синтаксис SQL.3. Типи даних.

1. Вступ до SQL.

SQL – це стандартна мова програмування, яка використовується для створення,

модифікації, пошуку і вибірки інформації, що зберігається в довільній реляційній базі

даних, яка управляється відповідною системою управління базами даних (СУБД).

Мова SQL дуже могутня, тому її підтримують найбільш популярні СУБД, зокрема

Microsoft Access, Oracle і MySQL, хоча рівень цієї підтримки істотно залежить від того,

про яку саме СУБД йдеться.

SQL – це стандартизована, непроцедурна мова програмування, яка

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

цьому вбудований і/або інтерактивний SQL.

SQL – одна з формальних мов, тобто засіб, за допомогою якого ви передаєте

комп'ютеру інструкції, що називаються програмою. Програмне забезпечення вашої бази

даних виконує цю програму, написану на мові SQL. Це значить, що СУБД виконує ті

запити, які ви їй передали, і відображає результати їх роботи, у тому числі яке-небудь

повідомлення про помилку. Треба сказати, що мови програмування, названі також

формальними мовами, відрізняються від мов спілкування, названих неформальними або

природними мовами, головним чином тим, що створюються під конкретну мету, повністю

позбавлені двозначності, мають вельми обмежений словниковий запас і гнучкість. Таким

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

при її написанні, це відбулося тому, що ваша програма містить яку-небудь помилку

(логічну або синтаксичну - в останньому випадку, швидше за все, буде виведено

відповідне повідомлення, що описує помилку), а не тому, що комп'ютер неправильно

зрозумів ваші інструкції, формалізовані у вигляді програми.

Будучи формальною мовою, SQL, як інша мова цього типу, має свої синтаксис і

семантику. Синтаксис включає власне слова і символи, а також правила, по яких ці слова і

символи можна використовувати при створенні команд і програм. Семантика допомагає

з'ясувати реальне значення, значення будь-якої синтаксично правильної команди. Ви

цілком можете написати на SQL яку-небудь команду, відповідну синтаксису мови, яка,

проте, буде виражати невірне значення (тобто буде правильною синтаксично, але

невірною семантично).

5

Для будь-якої процедурної мови програміст повинен явно прописувати кроки, які

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

мов (такі мови ще називають декларативними). Програмуючи на такій мові, вам треба

описати те, що саме ви хочете зробити, а не те, як ви збираєтеся це робити. У випадку з

SQL оптимізатор, який входить до складу програмного забезпечення вашої СУБД,

самостійно розрахує це саме „як”. Саме тому SQL не містить таких конструкцій

Програмуючи на SQL, вам треба вказати тільки те, що саме необхідно зробити, а

далі сама СУБД автоматично і непомітно для вас визначає і виконує ту послідовність

покрокових операцій, яку вимагається виконати для досягнення бажаного результату.

Проте управляючі конструкції звичайно присутні в SQL і застосовуються при

створенні так званих процедур збереження - певних наборів інструкцій, призначених для

виконання складних дій з даними.

Якщо ви включаєте команди SQL як елементи в які-небудь програми, написані на

процедурних мовах, вважається, що ви застосовуєте так званий вбудований SQL, а ті

процедурні мови, на яких написані ці більш великі або, точніше, несучі програми,

називають базовими. Такою мовою на практиці частіше всього виявляється будь-яка мова

програмування загального призначення, зокрема С, Java або COBOL, або яка-небудь мова

сценаріїв, наприклад Perl, PHP або Python.

Якщо в режимі реального часу адресувати команди на мові SQL безпосередньо

вашій СУБД, а вона відобразить відповідні результати відразу ж, як тільки отримає,

значить, ви застосовуєте інтерактивний або динамічний SQL. Зазначимо, що всі сучасні

сервери СУБД поставляються в комплекті з такими графічними додатками або утилітами

командного рядка, які сприймають інтерактивні команди SQL або/і текстові файли, що

містять SQL-пpoграми, тобто сценарії. Майте на увазі, що між інтерактивними і

вбудованими командами можуть існувати невеликі синтаксичні відмінності.

Всі команди SQL прийнято ділити на дві основні групи - мова маніпулювання

даними (DML) і мова визначення даних (DDL).

Для будь-якої бази даних команди групи DML відбирають, обчислюють,

вставляють, видаляють і редагують ті дані, які зберігаються в цій базі.

Команди групи DDL створюють, модифікують і знищують такі об'єкти бази даних,

як таблиці, індекси і погляди.

SQL – це інструмент, призначений для вибірки та опрацювання інформації, яка

міститься в комп’ютерній базі даних. SQL – це мова програмування, яка застосовується

для організації взаємодії користувача із базою даних. Це не алгоритмічна мова.

6

Якщо користувачу необхідно отримати якусь інформацію із бази даних, то він

запитує її за допомогою SQL. СУБД опрацьовує запит, знаходить потрібні дані і надсилає

їх користувачу.

Функції SQL:

1) організація даних – SQL дає користувачу можливість визначити структуру

представлення даних, а також встановлювати відношення між елементами бази даних;

2) вибірка даних – SQL дає можливість користувачу чи прикладній програмі

отримати із бази даних інформацію, яка в ній міститься;

3) опрацювання даних – SQL дає можливість користувачу чи прикладній програмі

змінювати базу даних, тобто додавати нові дані, оновлювати чи вилучати існуючі;

4) управління доступом – за допомогою SQL можна обмежувати можливості

користувача щодо вибірки і зміни даних та захистити дані від несанкціонованого доступу;

5) спільне використання даних – SQL координує спільне використання даних

користувачами, які працюють паралельно так, щоб вони не заважали один одному;

6) цілісність даних – SQL дозволяє забезпечити цілісність бази даних, захищаючи

її від руйнації через неузгоджені зміни чи відмови системи.

Роль SQL

Сам по собі SQL це не СУБД і не який-небудь окремий програмний продукт. SQL –

це інструмент, за допомогою якого здійснюється зв’язок користувача з базою даних. SQL

працює лише з реляційними базами даних.

Ядро бази даних – це серцевина СУБД. Воно відповідає за фізичне структурування

даних, а також запис їх на диск та зчитування з диску. Ядро сприймає SQL-запити від

інших компонентів СУБД. SQL використовує самі різні функції, зокрема: 1) SQL-мова

інтерактивних запитів; 2) SQL-мова програмування бази даних; 3) SQL-мова

адміністрування бази даних. Адміністратор бази даних використовує SQL для визначення

структури бази даних та управління доступом до даних; 4) SQL – це мова створення

клієнт-серверних програм; 5) SQL – це мова доступу до даних в Internet; 6) SQL – це мова

розосереджених баз даних, тобто SQL дозволяє розподілити дані між різними

обчислювальними системами і зв’язати відповідне програмне забезпечення; 7) SQL – мова

шлюзів баз даних.

7

Переваги SQL:

1) незалежність від конкретних СУБД;

2) міжплатформна переносимість;

3) наявність стандартів;

4) підтримка компаніями IBM та Microsoft;

5) реляційна основа;

6) високорівнева структура;

7) можливість виконання інтерактивних запитів;

8) забезпечення програмного доступу до бази даних;

9) можливість різноманітного представлення даних;

10) повноцінність, як мови опрацьовування баз даних;

11) можливість динамічного визначення даних;

12) підтримка архітектури клієнт-сервер;

13) підтримка корпоративних прикладних програм;

14) розширюваність та підтримка об’єктно-орієнтованого програмування;

15) можливість доступу до даних в Internet;

16) інтеграція з мовою Java (протокол JDBC);

17) промислова інфраструктура.

2. Синтаксис SQL.

Коментар. Всякий коментар – це необов'язковий текст, який друкується в

окремому рядку програми, щоб пояснити цю програму. Коментар повинен починатися з

двох дефісів. Коли СУБД знаходить їх, вона ігнорує те, що стоїть за ними, тобто сам

коментар. Коментарі займають цілий рядок.

Команда SQL. Будь-яка команда SQL – це допустима комбінація лексем, якій

передує яке-небудь ключове слово. Тут під лексемою розуміється основна неподільна

частинка мови SQL в тому значенні, що граматично ніякої лексеми не можна розділити на

більш дрібні складові елементи. Лексемами є ключові слова, ідентифікатори, оператори,

літерали і інші символи.

Речення. Будь-яка команда SQL включає не менш одну пропозицію. В

найзагальнішому випадку всяка пропозиція SQL – це фрагмент команди SQL, який

починається з якого-небудь ключового слова, є обов’язковим або необов’язковим і

повинен бути записаний в певному порядку. В даному прикладі ми маємо чотири

пропозиції, а саме: SELECT, FROM, WHERE, ОRDER.

8

Ключові слова. Довільне ключове слово, іноді зване зарезервованим, – це таке

слово, яке в мові SQL має певне значення і застосування якого в SQL строго

регламентовано. Слід мати на увазі, що використовування будь-якого ключового слова

зовні контексту (наприклад, як ідентифікатора) буде вважатися помилкою. В 9таб. 1.1

перераховані ключові слова SQL, а в таб. 1.2 – потенційні слова SQL (які поки не є

офіційно зарезервованими, але одного разу можуть ними стати).

Ідентифікатори. Довільний ідентифікатор – це таке слово, яке розробник бази

даних застосовує для того, щоб іменувати об’єкти довільної бази даних, у тому числі

таблиці, стовпці, псевдо імена (псевдоніми) і представлення. Ідентифікатор не може бути

ключовим словом, і його довжина не може перевищувати 128 знаків. Знаком в SQL може

бути будь-який символ алфавіту, включаючи символи латинського алфавіту і латинські

ідеограми. В нашому прикладі іменами, зокрема, є au_fname, au_lname, authors і state.

Завершальна крапка з комою. Запис кожної команди SQL повинен закінчуватися

крапкою з комою.

Приклад команди SQL:

--Retrieve authors from New YorkSELECT au_fname, au_lname FROM authors WHERE state = 'Lviv' ORDER BY au_lname;

Таблиця 1.1. Ключові слова SQL

ABSOLUTE COMMIT ELSE INSERT Null ACTION CONNECT END INT ONLY ADD CONNECTION END-EXEC INTEGER OPEN ALL CONSTRAINT ESCAPE INTERSECT OPTION ALLOCATE CONSTRAINTS EXCEPT INTERVAL OR ALTER CONTINUE EXCEPTION INTO ORDER AND CONVERT EXEC IS OUTER ANY CORRESPONDING EXECUTE ISOLATION OUTPUT ARE COUNT EXISTS JOIN OVERLAPS AS CREATE EXTERNAL KEY PAD ASC CROSS EXTRACT LANGUAGE PARTIAL ASSERTION CURRENT FALSE LAST POSITION AT CURRENT DATE FETCH LEADING PRECISION

AUTHORIZATION CURRENT TIME FIRST LEFT PREPARE AVG CURRENTJTIMESTAMP FLOAT LEVEL PRESERVE BEGIN CURRENTJJSER FOR LIKE PRIMARY BETWEEN CURSOR FOREIGN LOCAL PRIOR BIT DATE FOUND LOWER PRIVILEGES BIT LENGTH DAY FROM MATCH PROCEDURE BOTH DEALLOCATE FULL MAX PUBLIC DEC GET MIN READ CASCADE DECIMAL GLOBAL MINUTE REAL CASCADED DECLARE GO MODULE REFERENCES CASE DEFAULT GOTO MONTH RELATIVE CAST DEFERRABLE GRANT NAMES RESTRICT CATALOG DEFERRED GROUP NATIONAL REVOKE CHAR DELETE HAVING NATURAL RIGHT CHARACTER DESC HOUR NCHAR ROLLBACK

CHARJ.ENGTH DESCRIBE IDENTITY NEXT ROWS CHARACTER LENGTH DESCRIPTOR IMMEDIATE NO SCHEMA

CHECK DIAGNOSTICS IN NOT SCROLL CLOSE DISCONNECT INDICATOR NULL SECOND COALESCE DISTINCT INITIALLY NULLIF SECTION

9

COLLATE DOMAIN INNER NUMERIC SELECT COLLATION DOUBLE INPUT OCTET LENGTH SESSION COLUMN DROP INSENSITIVE SESSIONJJSER SET SUM TRAILING UPPER WHENEVER SIZE SYSTEM USER TRANSACTION USAGE WHERE SMALLINT TABLE TRANSLATE USER WITH SOME TEMPORARY TRANSLATION USING WORK SPACE THEN TRIM VALUE WRITE SQL TIME TRUE VALUES YEAR SQLCODE TIMESTAMP UNION VARCHAR ZONE SQLERROR TIMEZONEJHOUR UNIQUE VARYING SQLSTATE TIMEZONE MINUTE UNKNOWN VIEW SUBSTRING TO UPDATE WHEN

Таблиця 1.2. Потенційні ключові слова SQL AFTER EQUALS OLD RETURN TEST ALIAS GENERAL OPERATION RETURNS THERE ASYNC IF OPERATORS ROLE TRIGGER BEFORE IGNORE OTHERS ROUTINE TYPE BOOLEAN LEAVE PARAMETERS ROW UNDER BREADTH LESS PENDANT SAVEPOINT VARIABLE COMPLETION LIMIT PREORDER SEARCH VIRTUAL CALL LOOP PRIVATE SENSITIVE VISIBLE CYCLE MODIFY PROTECTED SEQUENCE WAIT DATA NEW RECURSIVE SIGNAL WHILE DEPTH NONE REF SIMILAR WITHOUT DICTIONARY OBJECT REFERENCING SQLEXCEPTION EACH OFF REPLACE SQLWARNING

ELSEIF OID RESIGNAL STRUCTURE

SQL - це мова з вільним форматом речень. Будь-яка його команда може:

• бути надрукована як у верхньому, так і в нижньому регістрі (наприклад, ключові

слова SELECT і select вважаються ідентичними);

• продовжуватися на наступному рядку скільки завгодно довго за умови, що ви не

будете розбивати на дві частини слова, лексеми і рядки в лапках (тобто рядкові

константи);

• бути надрукована на одному рядку з будь-якими іншими командами;

• починатися на будь-якій позиції горизонтальної розмітки екрану/листа.

Проте вам слід дотримуватися певного стилю написання команд SQL, наприклад

використовувати верхній регістр для ключових слів і нижній регістр для ідентифікаторів.

Крім того, можна починати кожне речення з нового рядка з відповідним відступом.

Питання стилю не можна ігнорувати, тому що його відсутність приводить до наступних

помилок:

неправильна орфографія при написанні якого-небудь ідентифікатора або команди;

відсутність завершальної крапки з комою;

невірний порядок розміщення речень в тілі команди;

відсутність лапок у стрічкових констант (літералів) і констант дати і часу;

наявність лапок у цифровых констант;

невірне сумісне застосування імен таблиць і імен;

В літературі по SQL ввідне ключове слово будь-якої команди часто називають

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

10

Оскільки вживати ключові слова як ідентифікатори заборонено, ви цілком можете

вбудовувати будь-які ключові слова всередину ідентифікаторів як складову частину слова.

Наприклад: group і max не можна використовувати як ідентифікатор, а ідентифікатор

groups і max_price цілком допустимі.

select au fname,AU_LNAMEFROMauthors WhErE state = 'NY' orderbY AU_lname;

Команда, приведена в прикладі еквівалентна попередній команді.

Застосовувати пропуски в іменах бази даних не рекомендується. Але якщо дуже

хочеться, можна вставити пропуски в будь-який ідентифікатор, тоді його треба буде

укласти в одинарні лапки, от так:' last name'. Проте підкреслимо ще раз: краще

застосовувати не пропуски, а знак підкреслення (last name) або ідентифікатор, набраний

заголовними і прописними буквами (LastName).

Вираз - це будь-яка дозволена комбінація символів, яка служить для обчислення

єдиного агрегованого значення даних. Складаючи будь-який вираз, ви можете

комбінувати математичні або логічні оператори, ідентифікатори, константи, функції,

імена стовпців і т.д.

Комерційні СУБД накладають власні, обмеження на довжину ідентифікатора і на

алфавіт. Тому має сенс проаналізувати документацію по вашій СУБД, використовуючи

ключі пошуку „ідентифікатори” і „імена”.

Більше того, різні СУБД мають свої власні додаткові ключові слова, які, очевидно,

не можуть бути ідентифікаторами в „рідній” СУБД (але цілком можуть бути

ідентифікаторами по канонах SQL і, можливо, в інших СУБД). От чому непогано було б

організувати пошук в документації по вашій СУБД з використанням ключів пошуку

„ключові слова” і „зарезервовані слова”.

3. Типи даних.

1.3.1. КомандиВсі команди SQL мають однакову структуру:

11

Всього в SQL існує приблизно 40 команд. Кожна команда просить СУБД виконати

певну дію: вибрати дані, створити таблицю чи вставити в таблицю нові дані.

Основні команди SQL

Команда Опис

Опрацювання данихSELECT Вибирає дані із таблиціINSERT Додає нові рядки до таблиціDELETE Вилучає рядки із таблиціUPDATE Оновлює дані, які містяться в таблиці

Визначення данихCREATE TABLE Створює нову таблицю і додає її до бази данихDROP TABLE Знищує таблицю, тобто вилучає її із бази данихALTER TABLE Змінює структуру існуючої таблиціCREATE VIEW Створює новий погляд та додає його до бази данихDROP VIEW Знищує погляд, тобто вилучає його з бази данихCREATE INDEX Створює індекс для стовпчикаDROP INDEX Знищує індекс для стовпчикаCREATE SCHEMA Створює нову схемуDROP SCHEMA Знищує схемуCREATE DOMAIN Створює та додає до бази даних новий доменALTER DOMAIN Змінює визначення доменуDROP DOMAIN Знищує домен, тобто вилучає домен з бази даних

Управління доступомGRANT Надає користувачу певні привілеї доступуREVOKE Скасовує вказані привілеї доступу

Управління транзакціямиCOMMIT Завершує біжучу транзакціюROLLBACK Скасовує біжучу транзакціюSET TRANSACTION Починає транзакцію і вказує режим доступу до данихКожна команда SQL починається із ключового слова, яке визначає дію, що

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

якими працює команда, або містить уточнюючу інформацію про дії, що виконуються

командою. Кожна секція також починається із ключового слова, наприклад: WHERE,

FROM, INTO. Деякі секції в команді обов’язкові, а деякі необов’язкові. Конкретна

структура та зміст секцій залежить від команди. Багато секцій містять імена таблиць чи

стовпчиків, деякі секції містять додаткові ключові слова, константи, вирази.

1.3.2. ІменаКожний об’єкт в базі даних повинен мати ім’я. Повне ім’я кожного об’єкту

повинно бути унікальним. Імена вказують, над якими об’єктами SQL-команда виконує

свою дію. Основні об’єкти, які повинні мати імена згідно стандарту SQL1 – це таблиці,

12

стовпчики, користувачі. Стандарт SQL2 до цього переліку ще додає схеми (колекції,

набори таблиць), обмеження (умови, які накладаються на вміст таблиць та їх відношення)

і домени (допустимі набори значень, які можуть бути занесені у стовпчик). За стандартом

SQL1 імена повинні складати від 1 до 18 символів, починатися з літери і не містити

пробілів, чи знаків пунктуації. Стандарт SQL2 дозволяє утворювати імена довжиною 128

символів.

Якщо в команді вказане ім’я таблиці, то СУБД вважає, що відбувається звертання

до однієї із Ваших власних таблиць, тобто до однієї з тих таблиць БД, яку створили Ви

самі. Якщо Ви маєте відповідні привілеї, тобто відповідний дозвіл, то можна звертатися до

таблиць, власниками яких є інші користувачі. Для цього використовується повне ім’я

таблиці:

Стандарт SQL2 узагальнює поняття повного імені таблиці:

Деякі СУБД організовані таким чином, що дозволяють створювати та

опрацьовувати кілька баз даних (Inter Base, MS SQL SERVER). Вони дають можливість

звертатися до таблиці із іншої бази даних.

Приклад 1. Звертання до таблиці із іншої БД за допомогою драйверів BDE.DELETE FROM Pracivnyky

WHERE Kod IN(SELECT Kod FROM :ARCHIVE: Pracivnyky)

Цей запит вилучає із таблиці Pracivnyky біжучої бази даних інформацію про тих

працівників, яка міститься в однойменній таблиці із бази даних, зв’язаної з BDE-аліасом

ARCHIVE.

MS SQL Server реалізує звертання до бази даних так (діалект SQL Server: Transact-

SQL):

Ця команда вибирає всю інформацію із таблиці Pracivnyky вказаної бази даних.

Якщо в команді вказане ім’я стовпчика, то СУБД сама визначає, якій саме таблиці

із тих, що вказані у цій команді, належить даний стовпчик. Якщо в команді вказуються

13

два однойменних стовпчика із різних таблиць, то необхідно вказувати повне ім’я

стовпчика:

Якщо стовпчик міститься в таблиці, власником якої є інший користувач, то повне

ім’я має таку будову:

1.3.3. Типи данихСтандарт SQL1 описує мінімальний набір типів даних. Ці типи підтримуються

усіма комерційними СУБД. Стандарт SQL2 додав до переліку типів даних літерні рядки

змінної довжини, значення дати і часу тощо.

Типи даних в SQL

Тип даних ОписCHAR (довжина)

Літерні рядки постійної довжини SQL1CHARACTER (довжина)VARCHAR (довжина)

Літерні рядки змінної довжини SQL1CHAR VARYING (довжина)CHARACTER VARYING (довжина)NCHAR (довжина)

Локалізовані літерні рядки постійної довжини SQL2

NATIONAL CHAR (довжина)NATIONAL CHARACTER (довжина)NCHAR VARYING (довжина)

Локалізовані літерні рядки змінної довжини SQL3

NATIONAL CHAR VARYING (довжина)NATIONAL CHARACTER VARYING (довжина)INTEGER

Цілі числа SQLINTSMALLINT Малі цілі числа SQL1BIT (довжина) Рядки бітів постійної довжини SQL2BIT VARYING (довжина) Рядки бітів змінної довжини SQL2NUMERIC (точність, степінь)

Числа з плаваючою крапкою SQL1DECIMAL (точність, степінь)DEC (точність, степінь)FLOAT (точність)REAL Числа з плаваючою крапкою низької

точності SQL1

DOUBLE PRECISIONЧисла з плаваючою крапкою високої точності SQL1

DATE Дата SQL2TIME (точність) Час SQL2TIMESTAMP (точність) Дата-час SQL2INTERVAL Інтервал SQL2

Різні СУБД по-різному опрацьовують типи даних. Розглянемо це на прикладі

опрацювання дати і часу.

Приклад.

14

Для DB2

DATA << June 30, 1990 >>TIME << 12:30 PM >>

TIMESTAMP – конкретний момент часу з точністю до наносекунди.В цій СУБД значення дати і часу можна представити за допомогою літерних

рядків. Підтримуються арифметичні дії над датами:SELECT Prizv, DatePryjomu

FROM PracivnykyWHERE DatePryjomu > = ‘05/30/1990’+15 DAYS

Тут поле DatePryjomu типу DATE.

Для SQL ServerЄ єдиний тип для представлення дати-часу DATATIME:SELECT Prizv, DatePryjomu

FROM PracivnykyWHERE DatePryjomu > = ‘06/14/1990’

Оскільки не вказано часу, то за замовчуванням він вважається рівним опівночі. Якщо вказано точний час, то секція WHERE набуде вигляду:

WHERE DatePryjomu > = ‘06/14/1990 12:00 AM’

В SQL Server також підтримуються арифметичні дії над датами:WHERE DatePryjomu > = DATEADD (DAY, 15, ‘05/30/1990’)

Для OracleТип DATE TIMESTAMP (з SQL). Як і для SQL Server, якщо час явно не вказаний,

то він вважається рівним опівночі:WHERE DatePryjomu > = ‘14-JUN-90’

В Oracle також підтримуються арифметичні дії над датами:WHERE DatePryjomu > = ‘30-MAY-90’ + 15

1.3.4. Константи

1.3.4.1. Числові константи

Це насамперед точні числові константи:

1) цілі

2) десятковіПриклад. 2000.00, + 497.13

Не можна ставити символи, які відокремлюють розряди між цифрами. Не всі

діалекти SQL дозволяють ставити перед числом знак “+”, тому краще його уникати. Якщо

дані представляють грошові одиниці, то в більшості СУБД використовуються цілі або

десяткові константи. В деяких із них перед константою можна вказати символ грошової

одиниці, наприклад: $ 0.75, 200.75. Константи з плаваючою крапкою відносяться до

15

наближених числових констант, вони визначаються за допомогою літери “Е” і мають той

самий формат, що і більшість мов програмування.

1.3.4.2. Літерні рядки

Константи літерних рядків повинні бути обрамлені за допомогою одинарних

апострофів:

Приклад. ‘школа’

Якщо в константу необхідно включити апостроф, то його потрібно подвоїти,

наприклад: ‘ім”я’, ‘м”ясо’.

В деяких СУБД (SQL Server, Informix) дозволяється обробляти літерні константи

подвійними лапками. Це може привести до проблем при переході на платформу іншої

СУБД, тому подвійні лапки краще не використовувати.

1.3.4.3. Константи дати і часуКонстанти дати і часу представляються у вигляді літерних рядків. Формати цих

констант в різних СУБД відрізняються один від одного, крім того, спосіб запису дати і

часу залежить від країни.

Для SQL Server:

Дата ЧасMarch 15, 1990Mar 15 19903/15/19903-15-901990 MAR 15

15:30:253:30 pm3 PM

Для Oracle:

Дата15-MAR-90

16

Для DB2:

Система позначень Дата ЧасамериканськаєвропейськаяпонськаISO

5/19/196019.5.19601960-5-191960-5-19

2:18 PM14.18.0814:18:0814.18.08

1.3.4.4. Іменовані константи

Це стандартні константи, які представляють значення, що зберігаються в СУБД.

SQL1 : USERSQL2 : CURRENT_DATE

CURRENT_TIMECURRENT_TIMESTAMP

USERSESSION_USERSYSTEM_USER

Іменовані константи можна використовувати скрізь, де можна застосувати звичайну константу того самого типу.

Приклад 1.

SELECT Prizv, DataPryjomuFROM PracivnykyWHERE DataPryjomu > CURRENT_DATE

Деякі СУБД (SQL Server) забезпечують доступ до системних значень не за

допомогою іменованих констант, а за допомогою вбудованих функцій.

Приклад 2. Попередній приклад в MS SQL Server

SELECT Prizv, DataPryjomuFROM PracivnykyWHERE DataPryjomu > GETDATE ( )

1.3.5. ВиразиВ стандарті SQL визначені такі операції з пріоритетами:

1. *, /2. +, –

Для літерних рядків визначена операція склеювання: ||.

Крім цих операцій, в SQL2 визначені ще такі операції “+”, “–” для значень типу

DATE, TIME та TIMESTAMP. Операції відношення мають більш низьких пріоритет ніж

арифметичні операції. Як правило, логічні операції мають ще менший пріоритет.

Приклад.

X = Y OR A > BPascal: (X = (Y OR A)) > BSQL: (X = Y) OR (A > B)

1.3.6. Вбудовані функціїВбудовані функції в стандарті SQL1 взагалі не були визначені. В стандарт SQL2

ввійшли самі поширені.

17

Вбудовані функції за стандартом SQL2

Функція Значення, яке повертаєтьсяBIT_LENGTH (рядок) - кількість бітів у рядку;CAST (значення AS тип даних) - значення перетворене до вказаного типу

даних;CHAR_LENGTH (рядок) - кількість символів у рядку;CONVERT (рядок USING функція) - рядок перетворений згідно заданої

функції;CURRENT_DATE - повертає біжучу дату;CURRENT_TIME (точність) - біжучий час із заданою точністю;CURRENT_TIMESTAMP (точність) - біжуча дата і час із вказаною точністю;EXTRACT (частина FROM значення) - визначає частину із вказаного значення.

Частина типу DAY, HOUR, … Значення типу DATETIME;

LOWER (рядок) - рядок, перетворений до нижнього регістру;OCTET_LENGTH (рядок) - кількість байтів у рядку;POSITION (підрядок IN рядок) - позиція, з якої починається входження

підрядка у рядок;SUBSTRING (рядок FROM n FOR довжина) - частина рядка, яка починається із n-го

символу і має вказану довжину;TRANSLATE (рядок USING функція) - рядок, відтрансльований за допомогою

вказаної функції;TRIM (BOTH символ FROM рядок) - рядок, із якого вилучені перші та останні

вказані символи;TRIM (LEADING символ FROM рядок) - рядок, із якого вилучені вказані символи;TRIM (TRAILING символ FROM рядок) - рядок, із якого вилучені останні вказані

символи;UPPER (рядок) - рядок, перетворений до верхнього

регістру.

Контрольні питання:1. Дайте визначення SQL.2. Що таке синтаксис та семантика?3. На які групи поділяються команди SQL?4. Що виконують команди групи DML, DDL, DCL?5. Вкажіть основні функції SQL.6. Яка роль SQL?7. Вкажіть основні переваги SQL.8. Що таке коментар, команда sql, речення, ключові слова, ідентифікатори у мові SQL?9. Назвіть основні команди SQL.10. Який формат речень SQL?11. Який запис повного імені стовпця?12. Вкажіть типи даних в SQL.13. Що таке константа і які типи констант розрізняють в SQL?14. Вкажіть функції в стандарті SQL.

18

Тема 2. Створення баз даних1. Мова визначення даних.2. Створення бази даних.

1. Мова визначення даних

Команди SELECT, INSERT, DELETE, UPDATE, COMMITE, ROLLBACK

утворюють мову опрацювання даних DML (Date Manipulation Language). Команди DML

можуть модифікувати інформацію, яка зберігається в базі даних, але не можуть змінювати

її структуру. Зокрема, ці команди не можуть створювати чи вилучати таблиці або

стовпчики. Для зміни структури бази даних призначений інший набір команд SQL, який

називається мовою визначення даних DDL (Date Definition Language). Ці команди

дозволяють:

1) визначити структуру нової таблиці та створити її;

2) вилучити таблицю, яка вже не потрібна;

3) змінити визначення, тобто структуру для створеної таблиці;

4) визначити віртуальну таблицю;

5) реалізувати безпеку бази даних;

6) створювати індекс для прискореного доступу до даних в таблиці;

7) керувати фізичним розміщенням даних.

Ядро DDL утворюють три команди:

1) CREATE – дозволяє визначити та створити об’єкт бази даних;

2) DROP – дозволяє вилучити існуючий об’єкт бази даних;

3) ALTER – дозволяє змінити визначення (структуру) існуючого об’єкту бази даних.

Всі основні реляційні бази даних дозволяють використовувати ці команди під час

своєї роботи. Тобто структура реляційної бази даних є динамічною.

Стандарт SQL2 вимагає, щоб інструкції DDL можна було виконувати як в

інтерактивному режимі, так і програмно.

2. Створення бази даних

В СУБД, які встановлені на мейнфреймах, за створення нових баз даних відповідає адміністратор. В

СУБД, які встановлені на серверах більш низького рівня, окремі користувачі можуть мати право створення

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

ними. Якщо Ви працюєте із базою даних на персональному комп’ютері, то швидше за все Ви є як її

користувачем, так і адміністратором, і Вам потрібно створювати базу даних самостійно. В SQL1 є

специфікація опису структури бази даних, але не вказується спосіб її створення, тому що різні СУБД

використовують різні підходи до цього. Стандарт SQL2 не визначає поняття “база даних”, так як кожна

СУБД трактує його по-своєму.

19

СУБД ОписDB2 Структура бази даних визначена за замовчуванням. База даних

створюється при інсталяції DB2 на конкретну комп’ютерну систему. Користувач отримує доступ до бази даних при підключенні до сервера.

Oracle База даних створюється при інсталяції. В останніх версіях Oracle з’явилася команда CREATE DATABASE, яка дозволяє створювати бази даних.

Ingres До складу цієї СУБД входить утиліта Creatеdb, призначена для створення бази даних, і утиліта Destroydb, призначена для знищення бази даних.

MS SQL SERVERCREATE DATABASE – створює базу даних;DROP DATABASE – знищує базу даних.

SyBaseInformix

Контрольні питання:1. Які команди входять до групи опрацювання даних DML?2. Які команди входять до групи визначення даних DDL?3. Яка команда створює нову базу даних в Interbase?

20

Тема 3. Створення таблиць1. Команда створення таблиці.2. Заборона значення null за допомогою обмеження NOT NULL.

1. Команда створення таблиці.

Щоб створити таблицю, слід вказати:

• назва таблиці;

• назви стовпців;

• тип даних для стовпців;

• обмеження.

Назви таблиці і стовпців повинні відповідати вимогам SQL. Кожна СУБД має

власні вимоги. Типом даних для кожного стовпця можуть бути символи, числа,

інформація про дату і час або дані іншого типу. Обмеження дозволяють задавати

властивості, наприклад вводити значення null, значення за умовчанням, ключі і допустимі

значення.

Ви створюєте нову таблицю за допомогою команди CREATE TABLE, яка має

наступну структуру:

CREATE TABLE table (Colunn1 data_type1 [col_constraints1],column2 data_type2 [col_constraints2],...columnN data typeN [col_constraintsN] [, table_constraint1] [, table_constraint2]...[, table constraintN]);

table - це назва нової таблиці, яку ви створюєте; columnl, column2 ..., columnN -

назви стовпців в table. Ви повинні створити хоча б один стовпець. data typel, data type2..,

data typeN задають тип даних SQL для відповідних стовпців. Тип даних може включати

довжину, масштаб або точну специфікацію.

Назви таблиць у базі даних і кожного стовпця в таблиці повинні бути унікальними.

Кожне позначення стовпця включає його назву, тип даних, а також список

обмежень для стовпця (опціонально). Не слід розділяти обмеження для різних стовпців за

допомогою ком. Список обмежень таблиці вводиться після опису останнього стовпця. За

описом кожного стовпця (окрім останнього) і обмежень слідує кома.

Введення опису кожного стовпця таблиці починається з нового рядка.

Лістинг 1.1 створює таблицю titles, лістинг 1.2 – таблицю title authors.

Лістинг 1.1. Створити таблицю titles:

CREATE TABLE titles(

21

title_id СНАR(З), title_name VARCHAR(40) , type VARCHAR(IO) , pub id CHAR(3) , pages INTEGER , price DECIMAL(5,2) , sales INTEGER , pubdate DATE , contract SMALLINT);Лістинг 1.2. Створити таблицю title authors:CREATE TABLE title authors ( title_id CHAR(3),au_id CHAR(3),au_order SMALLINT,royalty share DECIMAL(5,2) );

Якщо ви спробуєте створити таблицю з назвою, яка вже існує в базі даних, СУБД

видасть помилку. Щоб уникнути збереження однієї таблиці замість іншої SQL вимагає,

щоб до створення таблиці ви видалили таблицю з тією ж назвою за допомогою команди

DROP TABLE.

Відразу після створення таблиця порожня (не має рядків). Щоб заповнити таблицю

даними, користуйтеся командою INSERT.

За умовчанням значення null в стовпцях дозволені.

Щоб змінити структуру існуючої таблиці, зверніться по допомогу команди ALTER

TABLE.

Щоб створити таблицю на основі структури і даних існуючої таблиці, зверніться по

допомогу команди SELECT INTO.

2. Заборона значення null за допомогою обмеження NOT NULL

Від здатності стовпця приймати значення null залежить, чи можуть рядки містити

NULL, тобто чи обов'язково вводити значення в цих рядках чи ні.

Коротко:

значення null є маркером, який повідомляє, що значення не було введено;

значення null відображає значення, якого немає, яке невідомо або яке непридатний.

Значення null в стовпці price свідчить про те, що ціна невідома або не була вказана,

а не про те, що товар не має ціни або що ціна рівна нулю;

значення null - не те ж саме, що (0), порожнє поле або пропуск (' ');

значення null не відноситься ні до одного типу даних і може бути вставлене в будь-

який стовпець, який це допускає;

в командах SQL слово NULL указує на значення null.

При установці обмеження на значення null слід ураховувати наступні чинники:

• обмеження на значення null завжди є обмеженням стовпця, а не таблиці

22

• ви задаєте обмеження на значення null за допомогою ключових слів NULL або

NOT

NULL в позначенні стовпця в команді CREATE TABLE;

• бажано уникати дозволу значення null, оскільки це ускладнює запити;

• заборона значення null в стовпці може допомогти зберегти цілісність даних,

оскільки при їх введенні обов'язково вказувати значення. СУБД не буде вставляти або

змінювати рядок, якщо стовпець (для якого заборонено значення null) містить NULL;

• деякі обмеження (наприклад, PRIMARY KEY) не можуть використовуватися в

стовпцях, для яких дозволено значення null;

• значення null впливає на обмеження повторних ключів за допомогою обмеження

FOREIGN KEY;

• якщо ви додали рядок за допомогою команди INSERT, але не вказали значення

стовпця, який допускає значення null, ваша СУБД вставить NULL (за умови, що немає

обмеження DEFAULT).

• ви можете ввести NULL безпосередньо в стовпці, для якого дозволено значення

null, причому незалежно від того, який тип даних або значення за умовчанням вказаний

для цього стовпця;

• якщо ви не вкажете NULL або NOT NULL, no умовчанню значення null буде

дозволено.

Контрольні питання:1. Яка команда створює таблицю?2. Що слід вказати при створенні таблиці?3. Яка структура команди CREATE TABLE?4. Що виконує команда обмеження NOT NULL?

23

Тема 4. Вставка стрічок з допомогою оператора INSERT. Вилучення стрічок з допомогою оператора DELETE, редагування стрічки з допомогою команди

UPDATE.1. Вставка стрічок з допомогою оператора INSERT. 2. Вилучення стрічок з допомогою оператора DELETE3. Редагування стрічки з допомогою команди UPDATE.

1. Вставка рядків за допомогою команди INSERT.

Команда INSERT додає нові рядки в таблицю. Використовують цю команду, щоб:

• додати рядок за допомогою положення стовпців в таблиці (INSERT VALUES);

• додати рядок за допомогою назв стовпців (INSERT VALUES);

• додати рядки з однієї таблиці в іншу (INSERT SELECT).

Перерахуємо важливі параметри команди INSERT:

• при додаванні рядків за допомогою положення в таблиці ви додаєте значення в

новий рядок в тій же послідовності, в якій вони з'являються в таблиці. При вставці рядка в

стовпець ви задаєте назву стовпця, в який додаєте значення для нового рядка.

Слід завжди додавати рядки за допомогою назв стовпців. При цьому ваш запит

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

додасть нові стовпці;

• за допомогою команди INSERT VALUES ви указуєте точні значення, які повинні

бути вставлені в таблицю. За допомогою команди INSERT SELECT ви вибираєте рядки з

іншої таблиці, які бажаєте помістити в поточну;

• INSERT VALUES додає в таблицю один рядок, INSERT SELECT - будь-яку

кількість рядків;

• кожне додане значення повинне бути того ж типу (або мати нагоду для

конвертації), що і інші дані в стовпці;

• щоб зберегти систему посилань, вставлений зовнішній ключ повинен містити

або NULL, або значення існуючого ключа з первинного або унікального посилання

повторного ключа.;

• додане значення не може відмінити обмеження;

• жоден вираз не повинен приводити до арифметичної помилки (наприклад,

переповнюванню або розподілу на нуль);

• пам'ятайте, що порядок рядків в таблиці не має значення і що ви не можете

управляти розташуванням рядків, тому нові рядки можуть з'явитися і будь-якому місці

таблиці.

24

Додавання рядка за допомогою положення стовпця

Введіть:INSERT INTO table VALUES(value1, value2..... valueN);

table - це назва таблиці, в KOTРУ ви додаєте рядок, value1, value2. valueN - це список

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

Кількість значень повинна відповідати кількості стовпців в table, а значення

повинні бути вказані в тій же послідовності, що і стовпці. СУБД вставляє кожне значення

в стовпець який співпадає з положенням значення в table, value1 додається в перший

стовпець нового рядка, value2 - в другий стовпець і т.д.

Команда-приклад додасть в таблицю oдну рядок.

Лістинг 4.1. Команда INSERT додасть в таблицю authors новий рядок, вставивши

значення в тому порядку, в якому йдуть стовпці в списку.

INSERT INTO authors VALUES('А08','Michael','Polk','512-953-1231','4028 Guadalupe St','Austin','TX','78701');

Додавання рядка за допомогою назв стовпцівВведіть:INSERT INTO table (column1, column2 ..., columnN) VALUES(value1, value2..., valueN);table - це назва таблиці, в яку ви додаєте рядок; column1, column2., columnN -

список назв стовпців в table; value1, value2..., valueN - список буквених позначень або

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

Кількість значень повинна відповідати кількості стовпців в списку, а значення

повинні бути вказані в тій же послідовності, що і назви стовпців. СУБД вставляє кожне

значення в списку, використовуючи відповідні значення в списку. Значення value1

додається в стовпець column1 нового рядка, значення value2- в стовпець column2 і т.д.

Пропущеному стовпцю привласнюється значення за умовчанням або NULL.

Команда-приклад додасть в таблицю один рядок. Простіше всього вказувати назви

стовпців в тому ж порядку, в якому вони приведені в таблиці.

Лістинг 4.2. Команда INSERT додасть в таблицю authors новий рядок, вставивши

значення в тому порядку, в якому йдуть стовпці в списку.

INSERT INTO authors(

25

au id, au_fname ,au_lname, phone ,address, city ,state, zip)

VALUES( 'A09','Іrene', 'Bell','415-225-4689' ,'810 Throckmorton Ave', 'Mill Valley', 'CA', '9494V);

Але ви можете перерахувати їх в довільному порядку.

Лістинг 4.3. Вам необов'язково вказувати, назви стовпців в тому ж порядку, в

якому вони йдуть в таблиці. В даному прикладі ми змінили порядок назв стовпців і

відповідних значень.

INSERT INTO authors(Zip,Phone,Address,Au_lname,Au_fname,state,au id,city)

VALUES('60614','312-998-0020','1937 N. Clark Sт','Weston','Dianne','ІL','A10','Chicago');

У будь-якому випадку значення в реченні VALUES повинні відповідати

послідовності, в якій ви вказали назви стовпців.

Якщо ви бажаєте вказати значення тільки для певних стовпців, можете пропустити

деякі стовпці.

Лістинг 4.4. Ми додали рядок для нового автора, але пропустили стовпці і

значення для адреси автора. СУБД автоматично додала в пропущені стовпці NULL.

INSERT INTO authors( Au_id ,Au_fname,Au_lname,phone)

VALUES('А11','Max',

26

'Allard','212-502-0955');

Якщо ви пропустите стовпець, СУБД повинна сама вказати для нього значення на

підставі назви стовпця. СУБД додасть значення за умовчанням (якщо воно було задано)

або NULL (якщо можливо). Якщо ви пропустите стовпець, який не має значення за

умовчанням і в який не можна додати NULL, СУБД видасть повідомлення про помилку і

не додасть новий рядок.

Додавання рядків з однієї таблиці в іншу

Введіть:

INSERT INTO table[(column1, column2..... columnN)]select_statement;table- ця назва таблиці, в яку ви додаєте рядок; column1, column2..., columnN-

список назв стовпців в ній; select statement- будь-яка команда SELECT, що прочитує рядки даних,

які повинні бути додані в таблицю.

pub id pub.name city state countryP05 This is Pizza? Press New York NY USAP06 This is Beer? Press Toronto ON Canada P07 This is Irany? Press London NULL United KindomP08 This is Fame? Press Los Angeles CA USA

Кількість стовпців в результаті виконання команди select statement повинне

відповідати кількості стовпців в table або списку стовпців. СУБД ігнорує назви стовпців

при виконанні команди selееt statement і замість них використовує положення в стовпці.

Для першого стовпця в table або column1 використовується перший стовпець select

statement в процесі виконання і т.д. Пропущеному стовпцю задається значення за

умовчанням або NULL.

Команда-приклад додасть в table декілька рядків.

В решті прикладів цього розділу використовується таблиця new publishers, яку ми

створили, щоб показати, як працює команда INSERT SELECT. Таблиця new publishers має

ту ж структуру, що і publishers, і використовується тільки як джерела нових рядків, але не

змінюється в результаті виконання команди INSERT.

Лістинг 4.5. додає рядки з інформацією про видання Лос-Анджелеса З ТАБЛИЦІ nеw

publishers в publishers. Ми пропустили список стовпців, тому СУБД буде використовувати

положення стовпців в таблиці publishers для додавання значень. Команда лістинга 4.5

додасть в publishers один рядок. Лістинг 4.6 додає рядки з інформацією про

неамериканські видання з таблиці new publishers в publishers. Назви стовпців в пунктах

INSERT і SELECT тут однакові, проте це необов'язково, оскільки СУБД ігнорує назви

стовпців, лічені командою SELECT, і використовує їх положення. Ця команда додасть в

publishers два рядки. Речення SELECT може рахувати порожній результат (нуль рядків).

27

Лістинг 4.7 додає рядки з інформацією про видання XXX з таблиці new publishers в

publishers. Ми можемо використовувати SELECT* замість списку назв стовпців, оскільки

new publishers і publishers мають однакову структуру. Команда з лістинга 4.7 не додасть в

publishers жодні рядки, оскільки видавництва з назвою XXX в таблиці new publishers

немає.

Лістинг 4.5. Додамо, з рядки з інформацією про видання Лос-Анджелеса з таблиці

new publishers в publishers.

INSERT INTO publishers SELECTPub_id,Pub_name,City,State,countryFROM new publishers WHERE city = 'Los Angeles';

Лістинг 4.6. Додати рядки з інформацією про неамериканські видання з таблиці new publishers в publishers.

INSERT INTO publishers(pub_id,pub_name,city,state,country)

SELECT Pub_id, Pub_name, City, state, country FROM new publishers WHERE country <> 'USA';

Лістинг 4.7. Додати рядки з інформацією про видання XXX з таблиці new publishers в publishers.

INSERT INTO publishers(Pub_id,Pub_name, city,state,country) SELECT *FROM new publishers WHERE pub name = 'XXX';

В SQL ключове слово INTO є опцією для команди INSERT. Microsoft Access, Oracle і

PostgreSQL обов'язково вимагають введення INTO.

Процес додавання рядків в таблицю і перший раз називається заповненням таблиці.

Ви можете використовувати команду SELECT INTO, щоб створити нову таблицю і

заповнити її рядками, які поверне команда SELECT.

28

Якщо ви бажаєте бути особливо уважними при додаванні рядків, можете

перевірити команду INSERT за допомогою тимчасової таблиці.

Ви також можете додавати рядки з ними допомогою представлень

Якщо ви застосовуєте транзакції, скористайтеся командою COMMIT після команди

INSERT, щоб зробити зміни постійними.

Якщо table1 і table2 мають однакову структуру, можете вставити всі рядки з table2

в table1 Для цього введіть:

INSERT INTO table1SELECT * FROM table2;

2. Зміна рядків за допомогою команди UPDATE.

Команда UPDATE змінює значення в існуючих рядках таблиці. Ви можете

використовувати цю команду, щоб змінювати:

• всі рядки в таблиці;

• окремі рядки в таблиці.

Щоб відновити рядки, потрібно вказати:

• яку таблицю змінювати;

• назви стовпців, які потрібно змінити, а також нові значення;

• умова пошуку з метою знаходження рядків для оновлення (опціонально).

Перерахуємо важливі параметри команди UPDATE:

• використовує речення WHERE, в якому вказується, які рядки потрібно змінити.

Без речення WHERE команда UPDATE змінить всі рядки в таблиці;

• може бути небезпечна, тому що ви можете випадково пропустити речення

WHERE (і змінити всі рядки) або неправильно вказати умову пошуку для WHERE (і

змінити не ті рядки). Перед запуском команди UPDATE ми рекомендуємо запустити

команду SELECT з тим ж реченням WHERE, але для оновлення рядків. Команда SELECT

відобразить всі рядки, які будуть змінені СУБД при запуску команди UPDATE. Щоб

відобразити тільки кількість цих рядків, користуйтеся командою SELECT COUNT (*);

• КОЖНЕ змінене значення повинне БУТИ ТОГО ж типу (або мати можливість для

конвертації), що і інші дані в стовпці

• щоб зберегти посилальну цілісність, СУБД дозволяє вказати дію, яка буде

виконуватися автоматично за допомогою команди UPDATE при зміні значення, на яке

вказує повторний ключ;

• змінене значення не може відмінити обмеження, накладене на стовпець;


переповнюванню або ділення на нуль);

29

• пригадайте, що порядок рядків в таблиці не має значення і що ви не можете

управляти розташуванням рядків, тому нові рядки можуть з'явитися в будь-якому місці

таблиці

Зміна рядків

Введіть:

UPDATE table SET column= expr [WHERE search condition];table - це назва таблиці, яку ви будете обновляти; column - назва стовпця. (з даними

для зміни) в table; expr- буквене позначення, вираз або запит, який прочитує одне

значення. Значення, лічене expr, замінить існуюче значення в со1итп. Щоб змінити

значення в декількох стовпцях, введіть в пункті SET список виразів (column = expr),

розділених комами. Ви можете задавати список полів для оновлення у будь-якому

порядку.

Умову Search condition задають умови які повинні дотримуватися для змінних

рядків. Цими умовами можуть бути умови WHERE (оператори порівняння, LIKE,

BETWEEN, IN і IS NULL, див. розділ 4) або умови запиту (оператори порівняння, IN,

ALL, ANY і EXISTS, див. розділ 8) в комбінації з AND, OR і NOT. Якщо ви опустите

речення WHERE, будуть змінені всі рядки в таблиці.

Лістинг 4.8. Замініть значення contract нулем у всіх рядках titles.

UPDATE titles SET contract =0;

Лістинг 4.9. Подвоїти ціну на книги по історії. UPDATE titlesSET price = price * 2.0 WHERE type = 'history';

Лістинг 4.10. Змінити стовпці type і pages для книг по психології. UPDATE titles SET type = 'self help',pages = NULL WHERE type = 'рsychology';

Лістинг 4.11. Зменшити в двічі продажі книг, які знаходяться на середньому рівні. UPDATE titles SET sales = sales * 0.5 WHERE sales > (SELECT AVG(sales) FROM titles);

Лістинг 4.8 замінює значення contract нулем. Відсутність речення WHERE

повідомляє СУБД, що слід змінити значення в стовпці contract у всіх рядках. Команда в

лістингу 4.8 змінює 13 рядків.

Лістинг 4.9 використовує арифметичний вираз і умову WHERE, щоб подвоїти ціну

на книги по історії. Команда, представлена в цьому лістингу, змінює три рядки.

Лістинг 4.10 змінює стовпці type і pages для книг по психології. Ви використовуєте

тільки одне речення SET, що б змінити декілька стовпців, розділивши вирази column =

30

expr комами (не слід ставити кому в кінці останнього виразу). Команда з лістинга 4.10

змінює три рядки.

Лістинг 4.11 використовує підзапит і функцію, щоб зменшити в двічі продажі книг,

які продаються краще, ніж всі книги в цілому. Команда, представлена в цьому лістингу,

змінює дні рядка.

Ви можете змінити значення в таблиці на підставі значень з іншої таблиці. Лістинг

9.12 використовує підзапити щоб змінити дату публікації КНИГ, які написала Sarah

Buchmann. Команда з лістинга 4.11 змінює три рядки.

Припустимо, що видавництво Abatis Publishers (P01) купило видавництво

Теnterhooks Press (P04). Тепер всі книги останнього випускаються в Abatis Publishers.

Лістинг 4.13 міняє інформацію про видавництво в titles з Р04 на Р01. Запит в реченні

WHERE прочитує pub_id для видавництва Tenterhooks Press. СУБД використовує pub_id,

щоб зчитати з таблиці titles всі книги, які випускаються видавництвом Tenterhooks Press.

Потім СУБД за допомогою значення, зчитаного запитом в реченні SET, змінює відповідні

рядки в таблиці titles. Оскільки запити використовуються з незмінним оператором

порівняння, вони повинні бути скалярними запитами і зчитувати єдині значення (тобто

результат одного рядка або стовпця

СУБД буде розраховувати вирази в реченні SET або WHERE з використанням

значень, які знаходилися в стовпцях до початку змін. Розглянемо команду UPDATE:

UPDATE mytable SET col1 = col1 * 2, col2 = coll * 4,col3 = col2 * 8 ,WHERE col1 = 1 AND Cоl2 = 2;

СУБД задає col1 рівним 2, col2 - рівним 4 (1х4, а не 2х4), col3 - рівним 16 (2х8, а не 4х8).

Лістинг 4.12. Замінити дату публікації для всіх книг, які написала Сара Бухманн, на 1 січня 2003 року.

UPDATE titlesSET pubdate = DATE '2003-01-01' WHERE title id IN

(SELECT title id FROM title authors WHERE au id IN

(SELECT au id FROM authorsWHERE au fname = 'Sarah' AND au lname = 'Buchman'));

Лістинг 4.13. Приписати всі книги, випущені видавництвом Tenterhooks Press, видавництву Abatis Publishers.

UPDATE titlesSET pub id = (SELECT pub id

FROM publishersWHERE pub name = 'Abatis Publishers')

WHERE pub id =

31

(SELECT pub id FROM publishers WHERE pub name = 'Tenterhooks Press');

Якщо ви бажаєте бути особливо уважними при зміні рядків, можете перевірити

команду UPDATE за допомогою тимчасової копії таблиці.

Ви також можете змінювати рядки за допомогою представлень.


UPDATE, щоб зробити зміни постійними.

3. Видалення рядків за допомогою команди DELETE

Команда DELETE видаляє рядки з таблиці. Ви можете використовувати цю

команду, щоб видаляти:

• всі рядки в таблиці;

• окремі рядки в таблиці.

Щоб видалити рядки, потрібно вказати:

• рядки в якій таблиці слід видалити;

• умова пошуку для знаходження рядків, що видаляються (опціонально).

Перерахуємо важливі параметри команди DELETE:

• на відміну від команд INSERT і UPDATE, ця команда не вимагає введення назв

стовпців, оскільки видаляє рядки цілком;

• видаляє рядки з таблиці, але не видаляє саму таблицю. Навіть якщо ви приберете

з таблиці всі рядки, сама таблиця залишиться. Якщо ви бажаєте видалити таблицю (разом

зі всіма даними, індексами і т.д.), зверніться до допомоги команди DROP TABLE;

• використовує (опціонально) речення WHERE, щоб визначити, які саме рядки слід

видалити. Якщо ви не вкажете умову пошуку, команда DELETE видалить всі рядки в

таблиці;

• може бути небезпечна, тому що ви можете випадково пропустити речення

WHERE (і видалити всі рядки) або неправильно вказати умову пошуку для WHERE (і

видалити не ті рядки). Перед запуском команди DELETE рекомендуємо запустити

команду SELECT з такою ж пропозицією WHERE. Команда SELECT відобразить всі

рядки, які будуть видалені СУБД при запуску команди DELETE. Щоб відобразити тільки

кількість таких рядків, користуйтеся командою SELECT COUNT (*);

• щоб зберегти посилальну цілісність, СУБД дозволяє вказати дію, яка буде

виконуватися автоматично за допомогою команди DELETE при видаленні рядків, на які

вказує повторний ключ;

32


переповнюванню або ділення на нуль);

• пригадайте, що порядок рядків в таблиці не має значення і що ви не можете

управляти розташуванням рядків, тому їх видалення може довільним чином змінити

розташування інших рядків в таблиці.

Видалення рядків

Введіть:

DELETE FROM table[WHERE search_ condition]; table - це назва таблиці, з якої ви будете видаляти рядки. search condition задає

умови, які повинні дотримуватися для рядків, що видаляються. Цими умовами можуть

бути умови WHERE (оператори порівняння, LIKE, BETWEEN, IN і IS NULL) або умови

запиту (оператори порівняння, IN, ALL, ANY і EXISTS) в комбінації з AND, OR і NOT.

Якщо ви опустите речення WHERE, будуть видалені всі рядки в таблиці.

Лістинг 4.14 видаляє всі рядки в royalties. Відсутність речення WHERE повідомляє

СУБД, що слід видалити всі рядки. Команда, представлена в цьому лістингу, видаляє 13

рядків.

В лістингу 4.15 пропозиція WHERE повідомляє СУБД, що з таблиці authors слід

видалити всіх авторів з прізвищем Халл (Hull). Команда цього лістинга видаляє два рядки.

Ви можете використовувати дані з однієї таблиці, щоб видалити дані в іншій

таблиці. Лістинг 4.16 використовує підзапит, щоб видалити з title authors всі книги,

випущені виданнями Р01 і Р04. Ця команда видаляє 12 рядків.

Лістинг 4.14. Видалити всі рядки в royalties.

DELETE FROM royalties;

title id advance royalty rate______ _______ __________Результат виконання лістинга 4.14Лістинг 4.15. Видалити з authors всіх авторів з прізвищем Халл.

DELETE FROM authorsWHERE au_lname = 'Hull';

Лістинг 4.16. Видалити з title authors всі книги, випущені виданнями Р01 і Р04.

DELETE FROM title authors WHERE title ld IN

(SELECT title id FROM titles WHERE pub id IN ('РО1,'Р04'));

Якщо ви бажаєте бути особливо уважними при видаленні рядків, можете

перевірити команду DELETE за допомогою тимчасової копії таблиці.

Ви також можете видаляти рядки за допомогою представлень.

33


DELETE, щоб зробити зміни постійними. TRUNCATE TABLE table; table - це таблиця,

яку ви бажаєте змінити.

Контрольні питання:1. Що виконує команда INSERT?2. Які параметри команди INSERT?3. Яка структура команди INSERT для додавання рядків з допомогою положення стовпців

в таблиці? 4. Яка структура команди INSERT для додавання рядків з допомогою назв стовпців?5. Яка структура команди INSERT для додавання рядків з однієї таблиці в іншу?6. Що виконує команда UPDATE?7. Які параметри команди UPDATE?8. Яка структура команди UPDATE?9. Що виконує команда DELETE?10. Які параметри команди DELETE?11. Яка структура команди DELETE?

34

Тема 5. Оператор SELECT

1. Оператор SELECT.2. Обчислювальні стовпчики. Альтернативне ім’я стовпчику.3. Порівняння значення стовпчика із константою.4. Правила виконання однотабличних запитів на вибірку.

1. Оператор SELECT.

Цей оператор вибирає всю інформацію із вказаної таблиці.

Результат оператора SELECT являє собою набір даних.

Нехай задано таблицю Товари.Tovary

Nazva Odynycia CinaЦукор

МолокоХліб

ДріжджіКовбаса

кгл

бухкгкг

2.601.001.303.6017.50

Приклад 1. Вибрати всю інформацію з таблиці Tovary.

SELECT *FROM Tovary

Цей оператор в результаті дасть набір даних, який повністю співпадає із таблицею

Tovary.

В секції SELECT вказується, яка інформація нам потрібна: зірочка (*) означає всі

стовпчики. Секція FROM означає джерела даних, з яких вибирається інформація.

Приклад 2. Вивести вміст стовпчиків одиниці та ціна.

SELECT Odynycia, CinaFROM Tovary

35

Приклад 3. Вивести перелік назв одиниць вимірювання всіх тих товарів, які є на складі.SELECT OdynyciaFROM Tovary

SELECT DISTINCT OdynyciaFROM Tovary

Odynycia Odynycia

кгл

бухкгкг

кгл

бух

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

2. Обчислювальні стовпчики. Альтернативне ім’я стовпчику.

В результуючий набір даних (НД) можуть входити не лише стовпчики, що

відповідають полям фізичних баз даних, а й обчислювальні поля, які визначаються

виразами в секції SELECT.

Вираз будується із констант, імен стовпчиків, знаків операцій та функцій, які

повинні бути визначені в стандарті SQL і допускатися в SELECT.

Приклад 4. Вивести всю інформацію про відпуск товару зі складу, вказавши

вартість кожного відпущеного товару.

SELECT V.*, V.Kilkist * T.CinaFROM Vidpusk V, Tovary TWHERE V.Tovar = T.Nazva

V.No V.Data V.Kilkist V.Tovar V.Pokupec Column 612

21.0122.01

1503

ЦукорДріжджі

ІвановПетров

390,0010,80

Для кожного рядка, що залишився після застосування критерію відбору WHERE,

обчислюється значення кожного елемента із списку полів SELECT і утворюється

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

поля таблиці бази даних відповідає значенню цього поля у даному біжучому рядку. Як

36

правило, СУБД автоматично призначає обчислювальним стовпчикам імена Column +

номер стовпчика. Також є можливість будь-якому стовпчику призначити альтернативне

ім’я.

SELECT V.*, V.Kilkist * T.Cina AS VartistFROM Vidpusk V, Tovary TWHERE V.Tovar = T.Nazva

3.Порівняння значення стовпчика із

константою

= < = ! < (не менше)< > = ! > (не більше)> <> != (не дорівнює)Приклад 5. Вибрати всю інформацію про наявні на складі товари, які коштують

понад 3 грн.:SELECT *

FROM Tovary WHERE Cina > 3

Можна порівнювати значення стовпчика із значенням іншого стовпчика (тої самої

або іншої таблиці).

4. Правила виконання однотабличних запитів на вибірку

Далі буде описана процедура генерації результату SQL – запита на вибірку (потім

вона буде доповнюватися). Ці результати отримуються після почергового застосування

секцій, які входять в оператор SELECT.

Спочатку виконується секція FROM, потім секція WHERE, потім секція SELECT.

Дії:

1. Взяти таблицю, вказану в секції FROM;

V.No V.Data V.Kilkist V.Tovar V.Pokupec Vartist12

21.0122.01

1503

ЦукорДріжджі

ІвановПетров

390,0010,80

Товари –5)Назва Одиниці Ціна

дріжджі кг. 3.60ковбаса кг. 17.50

37

константаопераціяполе

2. Якщо є секція WHERE – застосувати умову до кожного рядка (запиши лише ті

рядки, для яких умова = TRUE. Якщо умова = FALSE або = NULL – рядок відкидається);

3. Для кожного рядка, що залишився – обчислити значення кожного елемента із

списку полів секції SELECT – утворити рядок результуючої таблиці. При цьому:

звертання до поля відповідає значенню стовпчика у даному біжучому рядку.

4. Якщо є ключове слово DISTINCT – вилучити всі рядки – дублікати (залишити

лише унікальні рядки).

Контрольні питання:1. Що виконує команда SELECT?2. Яка структура команди SELECT?3. Який шаблон задає вибір всіх полів у рядку SELECT?4. Що таке альтернативне ім’я? Коли його використовують?5. Вкажіть команди для порівняння значення між двома стовпчиками. Де вони вказуються?6. Вкажіть правила виконання однотабличних запитів на вибірку.

38

Тема 6. Комбінування умов з допомогою операторів AND, OR, NOT. Порівняння по шаблону. Порівняння

з діапазоном. Сортування стрічок з допомогою речення ORDER BY.

1. Комбінування умов з допомогою операторів AND, OR, NOT. 2. Порівняння по шаблону.3. Порівняння з діапазоном. 4. Перевірка належності множині.5. Сортування стрічок з допомогою речення ORDER BY.

1. Порівняння значень виразів має наступну синтаксичну діаграму:

Операції відношення: =, >, <, <> (аналогічно !=), >= (аналогічно !<), <=

(аналогічно !>).

Коли порівнюються два вирази результатом може бути одне з трьох:

1) якщо порівняння істинне, то результат перевірки буде TRUE; 2) якщо порівняння хибне, то результат перевірки буде FALSE;3) якщо хоча б один з виразів має значення NULL, то результатом перевірки

буде NULL.

Складні логічні вирази будуються за допомогою операцій NOT, AND, OR.

На результат логічного виразу також впливають значення NULL.

NOT TRUE FALSE NULLFALSE TRUE NULL

AND TRUE FALSE NULLTRUE TRUE FALSE NULLFALSE FALSE FALSE FALSENULL NULL FALSE NULL

OR TRUE FALSE NULLTRUE TRUE TRUE TRUEFALSE TRUE FALSE NULLNULL TRUE NULL NULL

Якщо в логічному виразі об’єднується більш ніж одна умова відбору, то за

стандартом SQL пріоритети операцій йдуть у такому порядку:

1) NOT;2) AND;3) OR.

39

2. Перевірка на відповідність шаблону – це більш загальний спосіб, який

дозволяє замінити STARTING … WITH … і CONTAINING.

Шаблон – це літерний рядок, в який може входити довільна кількість

підстановочних символів.

Підстановочний символ “%” (знак процента) означає довільну кількість будь-яких

символів.

Приклад 1. Вивести всю інформацію про товари, назви яких починаються з літери “М” і закінчуються літерою “о”.

SELECT *FROM TovaryWHERE Nazva LIKE “М%о”

Підстановочний символ “_” (підкреслене тире) означає рівно один будь-який символ.

Приклад 2. Вивести всю інформацію про товари, назви яких містять п’ять символів, починаються з літери “М” і закінчуються літерою “о”.

SELECT *FROM TovaryWHERE Nazva LIKE “М_ _ _о”

Підстановочі символи можна використовувати в будь-якому місці шаблону, причому в шаблоні можна використовувати одразу декілька підстановочних символів.

Приклад 3.SELECT Company

FROM ClientyWHERE Company LIKE “Smiths_ n%”

Перевірку LIKE так само, як STARTING … WITH … та CONTAINING, можна

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

Якщо стовпчик в біжучому рядку має значення NULL, то результатом перевірок

LIKE, STARTING … WITH … та CONTAINING буде значення NULL.

Інколи потрібно записати такий шаблон, який в якості значущого символу містить

один із підстановочних символів. В таких випадках використовуються символи пропуску.

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

після нього вважається не підстановочним символом, а значущим, тобто відбувається

пропуск символу. Безпосередньо після символу пропуску може бути записаний або один

із двох підстановочних символів або сам символ пропуску, бо він також отримує в

шаблоні особливе значення. Тоді додається секція ESCAPE, після якої у лапках вказується

символ пропуску.

40

Приклад 4. Вивести всю інформацію про товари, коди яких починаються з таких чотирьох літер “А%ВС”, де “А%ВС” – назва, а символ “%” – літера.

SELECT *FROM TovaryWHERE Kod LIKE “А$%ВС%”

ESCAPE “$”

Перший символ “%” в шаблоні, який записаний після символу пропуску

вважається значущим, а другий символ “%” – підстановочним.

Приклад 5. Вивести всю інформацію про товари, коди яких починаються з таких літер “_ A%$B”, потім йде будь-який символ і літера “C”.

SELECT *FROM TovaryWHERE Kod LIKE “$_А$%$$В_С%”

ESCAPE “$”

3. Перевірка належності діапазону значень має таку синтаксичну діаграму:

Приклад 6. Вивести дані про всі відпуски товарів, для яких кількість відпущеного товару лежить в діапазоні від 100 до 300 одиниць.

SELECT *FROM VidpuskWHERE Kilkist BETWEEN 100 AND 300

В перевірці BETWEEN … AND … типи даних виразу, нижньої і верхньої меж

повинні бути сумісні. Ця перевірка нічого нового не приносить, так як:

Х BETWEEN А AND В ((X > = A) AND (X < = B))

Правила опрацювання значення NULL перевірки BETWEEN … AND … :

1) якщо вираз, що перевіряється, має значення NULL або якщо обидві межі

діапазону мають значення NULL, то результатом перевірки буде значення NULL.

2) якщо нижня межа дорівнює NULL, то результат перевірки дає FALSE тоді,

коли вираз, який перевіряється має значення більше ніж значення верхньої межі і дає

значення NULL в протилежному випадку.

3) якщо верхня межа має значення NULL, то перевірка BETWEEN … AND …

дає FALSE тоді, коли вираз, що перевіряється має значення менше ніж значення нижньої

межі і дає NULL в іншому випадку.

41

4. Перевірка належності множині значень має таку синтаксичну діаграму:

Приклад 7. Вивести дані про всі відпуски товарів, для яких кількість відпущеного

товару дорівнює 50, 100 або 300 одиниць.

SELECT *FROM VidpuskWHERE Kilkist ІN (50, 100, 300)

Перевірка належності множині значень краще за порівняння

. Слід уникати множин, які складаються лише з одного

елементу: X IN A, краще використати умову порівняння значень:

X = A.

Якщо вираз, що перевіряється, має значення NULL, то результатом перевірки буде

NULL.

NULL IN (…) → NULL

Інколи буває потрібно явно перевіряти значення стовпчика на рівність значенню

NULL, для цього використовується перевірка на рівність значенню NULL, яка має таку

синтаксичну діаграму:

Приклад 8. Вивести всі дані про відпуски товарів зі складу, для яких невказаний покупець.

SELECT *FROM VidpuskWHERE Pokupets IS NULL

Обернена формула перевірки IS NOT NULL дозволяє вибирати рядки, які не

містять значень NULL у вказаному стовпчику.

Приклад 9. Вивести всі дані про відпуски товарів зі складу, для яких вказаний покупець.

SELECT *FROM VidpuskWHERE Pokupets IS NOT NULL

На відміну від інших перевірок, перевірка на NULL не може повертати NULL в

якості результату. Перевірка на NULL завжди повертає TRUE або FALSE. Може здатися

дивним, що не можна перевіряти якесь значення на NULL за допомогою порівняння.

Приклад 9*. Хибний розв’язок попередньої задачі.SELECT *

42

FROM VidpuskWHERE Pokupets = NULL

Ключове слово NULL в такій перевірці не можна використовувати, тому що

насправді це не будь-яке конкретне значення, а просто сигнал, що значення відсутнє.

Навіть якщо б порівняння Pokupets = NULL було можливим, то правила опрацювання

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

очікується. Коли СУБД виявить рядок в якому поле Pokupets містить значення NULL. Що

буде в результаті такої перевірки TRUE чи FALSE? Оскільки значення по обидві сторони

від знаку дорівнює невідомі, то згідно правил виконання порівнянь повинно вийти NULL.

Оскільки умова відбору набуває значення, яке не співпадає із значенням TRUE, то даний

рядок відкується і він не ввійде в результуючу таблицю. Результуюча таблиця для цього

запиту буде порожньою, тобто не міститиме жодного рядка.

5. Секція ORDER BY – це необов’язкова секція і вона повинна записуватись

останньою в операторі SELECT.

Стовпчик – це ім’я стовпчика або його порядковий номер в секції SELECT.

Якщо є секція ORDER BY, то результуюча таблиця сортується згідно значення

стовпчиків вказаних в секції ORDER BY.

Результат можна сортувати по зростанню чи по спаданню. Для цього в секції

ORDER BY після імені стовпчика чи його порядкового номеру вказується зарезервоване

слово ASC або DESC. ASC – розміщує результат у порядку зростання (з початку до кінця)

по вказаному стовпчику чи його номеру, встановлюється за замовчуванням, а DESC –

43

розміщує результат у порядку спадання (з кінця до початку) по вказаному стовпчику чи

його номеру

Приклад 10. Вивести всю інформацію про відпуск товару зі складу та його ціну і результат вивести в порядку спадання прізвищ покупців.

SELECT V.*, T.CinaFROM Vidpusk V, Tovary TWHERE V.Tovar = T.NazvaORDER BY Pokupec DESC;

Контрольні питання:1. Вкажіть операції відношення.2. Які логічні оператори Ви знаєте?3. Який пріоритет виконання логічних операторів?4. Яка команда здійснює порівняння по шаблону? Вкажіть основні шаблони для

порівняння.5. Яка команда здійснює порівняння з діапазоном?6. Яка команда здійснює перевірку належності множині?7. Яка команда здійснює сортування записів? 8. Вкажіть команду сортування в порядку зростання та спадання.

44

Тема 7. Створення псевдонімів. З’єднання таблиці із собою. Вибір даних з кількох таблиць

1. Створення псевдонімів. 2. З’єднання таблиці із собою. 3. Вибір даних з кількох таблиць.4. Запити на об’єднання.

1. Створення псевдонімів.

Якщо є великі назви стовпчиків таблиць, то для того, щоб спростити запис в операторі

SELECT використовують псевдоніми.

Тоді уточнене ім’я стовпчиків задається так:

Наприклад:SELECT V.*, T.Cina

FROM Vidpusk V, Tovary TWHERE V.Tovar = T.Nazva

2. З’єднання таблиці із собою.

Деякі багатотабличні запити використовують відношення, що існують всередині однієї з

таблиць:

Нехай потрібно:

1). Вивести список імен працівників та їх керівників.

Кожному працівнику відповідає один рядок в таблиці Pracivnyky, а стовпчик

Kerivnyk містить ідентифікатор (код) того працівника, який є керівником даного

працівника.

Стовпчик Kerivnyk повинен бути зовнішнім ключем для тої таблиці, яка містить

дані про керівників. Цей стовпчик і є ним – це зовнішній ключ для самої таблиці

Pracivnyky.

45

Якщо попробувати створити цей запит як будь-який інший запит до двох таблиць з

відношенням „первинний ключ – зовнішній ключ”, то він виглядав би таким чином:

1).а). Неправильно:

SELECT Prizv, Prizv FROM Pracivnyky, Pracivnyky WHERE Kerivnyk = Kod_Pracivn

– цей оператор SELECT – неправильний із-за подвійного звертання до таблиці

Pracivnyky в секції FROM. Можна попробувати вилучити друге звертання до таблиці

Pracivnyky:

б). Неправильно:SELECT Prizv, Prizv FROM Pracivnyky WHERE Kerivnyk = Kod_Pracivn

Це – синтаксично правильний запит, але він не зробить того, що нам потрібно.

Це – однотабличний запит, тому СУБД почергово переглядає всі рядки таблиці

Pracivnyky, щоб знайти ті, які задовольняють умові

Kerivnyk = Kod_Pracivn

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

тобто працівник є своїм керівником. Таких рядків немає, тому запит не дасть ніякого

результату.

Для того, щоб зрозуміти, як в SQL вирішується ця проблема, уявимо собі що є дві

ідентичні копії таблиці Pracivnyky: одна копія називається Prac і містить дані про

працівників; інша – називається Keriv і містить дані про керівників.

Стовпчик Kerivnyk таблиці Prac – це зовнішній ключ для таблиці Keriv і наступний

запит буде працювати

в). Дві окремі таблиці – неправильно, бо їх немає.SELECT Prac. Prizv, Keriv.Prizv FROM Prac, Keriv WHERE Prac. Kerivnyk = Keriv. Kod_Pracivn

46

– оскільки стовпчики з прізвищами в двох таблицях мають однакові імена в секції

SELECT необхідно використовувати повні імена стовпчиків.

Для з’єднання таблиці самої з собою в SQL використовується саме такий підхід:

створення „уявної копії”. Замість того, щоб насправді зробити копію таблиці, СУБД

просто дозволяє звернутися до неї, використовуючи інше ім’я, яке називають псевдонім

таблиці.

Ось той самий запит з використанням псевдонімів таблиць:

г). Правильно:

SELECT Prac. Prizv, Keriv.Prizv FROM Pracivnyky Prac, Pracivnyky Keriv

WHERE Prac. Kerivnyk = Keriv. Kod_Pracivnyka

В секції FROM для кожної уявленої копії таблиці Pracivnyky призначається

псевдонім, який записується одразу після справжнього імені таблиці:

Якщо в секції FROM міститься псевдонім таблиці, то в повному імені стовпчика

треба використовувати цей псевдонім, а не справжнє ім’я таблиці.

3. Просте з’єднання таблиць (з’єднання за рівністю)

Процес формування пар рядків шляхом порівняння вмісту відповідних стовпчиків

– називається з’єднанням таблиць.

Таблиця, яка буде результатом процесу з’єднання, і яка містить дані із двох

таблиць – називається з’єднанням цих таблиць.

З’єднання на основі точної рівності між значеннями двох стовпчиків називаються

з’єднання за рівністю (можуть бути з’єднання і на основі інших видів порівняння

стовпчиків – їх розглянемо пізніше).

З’єднання – це основа багатотабличних запитів в SQL.

В реляційній БД вся інформація зберігається у вигляді явних значень у стовпчиках,

тому всі можливі відношення між таблицями можна сформувати, співставляючи вміст

відповідних стовпчиків.

Таким чином, з’єднання – це потужний (і єдиний!) спосіб виявлення відношень,

що існують між даними.

Оператор SELECT для багатотабличного запиту повинен містити умову відбору,

яка визначає зв’язок між стовпчиками:

2). Вивести номер і вартість замовлення, а також прізвище клієнта, який зробив це замовлення і величину наданого цьому клієнтові кредиту.

47

2). SELECT No_Zamovl, Vartist, Prizv, Credit FROM Zamovlennia,Clients WHERE Client = Kod_Clienta

- тут є особливості:І. Секція FROM містить дві таблиці.

ІІ. В умові відбору Client = Kod_Clienta порівнюються стовпчики із двох

таблиць. Такі стовпчики називаються зв’язаними.

Зауважимо, що в операторі SELECT ніде не сказано як саме повинен виконуватися

запит. Там немає вказівок типу „почніть з замовлень” або „почніть з клієнтів”. Замість

цього в запиті сказано що ми повинні отримати в результаті, а спосіб отримання в

результатів – залишається за СУБД.

Така умова відбору зменшує кількість пар рядків таблиці результатів: „Включити в

таблицю результатів запиту тільки ті пари рядків, для яких ідентифікатор клієнта (Client) в

таблиці Zamovlennia дорівнює ідентифікатору клієнта (Kod_Clienta) в таблиці Clients”.

Запити з використанням відношення „головна – підлегла” таблиці (предок – нащадок)

Із всіх багатотабличних запитів на практиці найбільш поширені запити до двох

таблиць, які зв’язані відношенням „головна – підлегла”. Запит про замовлення і клієнтів –

саме такий.

У кожного замовлення (нащадок) є відповідний клієнт (предок), і кожний клієнт

(предок) може мати багато замовлень (нащадків). Таблиці, із яких беруться рядки для

формування пари рядків результатів запиту, зв’язані відношенням „головна (Clients) –

підлегла (Zamovlennia)”.

В реляційній базі даних первинні (головні) і зовнішні ключі створюють відношення предок

/нащадок .

ClientsKod_Clienta .. .. ..

Zamovlennia.. .. Client .. ..

48

головний ключ – унікальний ідентифікатор

ключ з’єднання

зовнішній ключ– для зв’язку із іншою таблицею

1 : N

Таблиця, яка містить зовнішній ключ – це нащадок (підлегла таблиця), таблиця, у

якій відповідний стовпчик є первинним ключем, - це предок (головна таблиця). Щоб в

запиті використати відношення предок/нащадок, потрібно задати умову відбору, у якій

первинний ключ порівнюється із зовнішнім ключем:

2). SELECT No_Zamovl, Vartist, Prizv, Credit FROM Zamovlennia,Clients WHERE Client = Kod_Clienta

зовнішній ключ первинний ключ в таблиці Zamovlennia таблиці Clients

(для зв’язку із таблицею Clients)В SQL зовсім не вимагається, щоб зв’язані стовпчики обов’язково включалися в

результати багатотабличного запиту. На практиці вони, як правило, і не включаються – як

не було в нашому прикладі.

Це пов’язано з тим, що первинний і зовнішній ключі найчастіше являють собою

ідентифікатори, які людині важко запам’ятати, тоді як відповідні назви (міста, організації,

прізвища, посади тощо) запам’ятати набагато легше.

Тому цілком зрозуміло, що в секції WHERE для з’єднання двох таблиць

використовуються ідентифікатори, а в секції SELECT для створення результуючих

стовпчиків – більш зручні для сприйняття назви.

Умова для відбору рядків

В багатотабличному запиті можна комбінувати умову відбору, в якій визначаються

зв’язані стовпчики, з іншими умовами, щоб ще більше звузити результати запиту.

3). Нехай потрібно повторити попередній запит, але включити в нього лише

замовлення тих клієнтів, чий кредит перевищує 85 грн.

3). SELECT No_Zamovl, VartistPrizv, Credit

FROM Zamovlennia, Clients WHERE Client = Kod_Clienta AND Credit > 85

- застосування додаткової умови відбору зменшує кількість рядків в таблиці

результатів запиту.

Перша умова (Client = Kod_Clienta) відбирає із таблиці Zamovlennia і Clients ті пари

рядків, які зв’язані відношенням предок/нащадок; друга умова приводить до подальшого

відбору лише тих пар рядків, де кредит перевищує 85 грн.

Кілька зв’язаних стовпчиків

Розглянемо приклад:

49

TovaryKod_Tovaru Kod_Vyrobnyka … Cina …

Стовпчики Tovar і Vyrobnyk в таблиці Zamovlennia утворюють зовнішній ключ для

зв’язку із таблицею Tovary і зв’язані із її стовпчиками Kod_Tovaru і Kod_Vyrobnyka

відповідно.

Щоб сполучити таблиці на основі такого відношення предок/нащадок, необхідно

задати обидві пари зв’язаних стовпчиків.

4). Вивести номери замовлень на товари, а також відповідних товарів.

4). SELECT No_Zamovl, Nazva FROM Zamovlennia, Tovary

WHERE Tovar = Kod_Tovaru AND Vyrobnyk = Kod_Vyrobnyka

Умова відбору в цьому запиті визначає, що зв’язаними парами рядків таблиць

Zamovlennia і Tovary будуть ті, в яких пари зв’язаних стовпчиків містять одні і ті самі

значення.

З’єднання за допомогою кількох стовпчиків, як правило, використовуються в

запитах із складеними зовнішніми ключами, - як в наведеному прикладі.

3. Запити на вибірку до трьох і більше таблиць

SQL дозволяє з’єднувати дані із трьох і більше таблиць, використовуючи ту саму

методику, що і для з’єднання даних із двох таблиць.

Ось приклад з’єднання даних із трьох таблиць:

5). Вивести номер замовлення, прізвище клієнта, який його зробив, назву і ціну

замовленого товару, для замовлень вартістю понад 100 грн.

ZamovlenniaN_Z … Tovar Vyrobnyk … Kilkist …

50

Nazva

1 : N складений ключз’єднання

складений зовнішній ключ

складений первинний ключ

No_Zamovlennia

5). SELECT No_Zamovl, Prizv, Nazva, Cina FROM Zamovlennia, Clients, Tovary

WHERE Client = Kod_Clienta AND Tovar = Kod_Tovaru

AND Vyrobnyk = Kod_VyrobnykaAND Cina >100

Як видно із нарисованої схеми, в цьому запиті використовуються два зовнішні

ключі таблиці Zamovlennia: в ній стовпчик Client – це зовнішній ключ для таблиці Clients,

він зв’язує кожне замовлення з клієнтом, який його зробив.

Стовпчики (Tovar, Vyrobnyk) – це складений зовнішній ключ для таблиці Tovary,

вони зв’язують замовлення із замовленим товаром.

Інші з’єднання таблиць за рівністю

Переважна більшість багатотабличних запитів основана на відношеннях

предок/нащадок, але в SQL це зовсім не обов’язково; тобто, не вимагається, щоб зв’язані

стовпчики являли собою пару: „зовнішній ключ – первинний ключ” у зв’язку „підлегла –

головна таблиці”.

Будь-які два стовпчика із двох таблиць можуть бути зв’язані, якщо вони мають

сумісні типи даних.

6). Вивести всі замовлення, отримані в той день, коли на роботу був прийнятий

будь-який новий працівник.

6). SELECT Zamovlennia . * , Prizv FROM Zamovlennia, Pracivnyky WHERE Data_Otrym = Data_Pryjomu

51

- у цьому запиті зв’язаними є стовпчик Data_Otrym таблиці Zamovlennia та

стовпчик Data_Pryjomu таблиці Pracivnyky:

Data_Otrym = Data_Pryjomu

Ці стовпчики не входять ні у зовнішній ключ, ні у первинний ключ; та й взагалі,

відношення між рядками у цих парах – в достатній мірі дивне: спільне у цих замовленнях і

працівниках лише те, що вони з’явилися у фірмі один і той самий день.

Незважаючи на це, СУБД з готовністю сполучає таблиці так, як це визначено в

запиті.

Зв’язані стовпчики, подібні до тих, що наведені в нашому прикладі, створюють між

таблицями відношення „багато – до – багатьох” (N : M) :може поступити багато замовлень

в день прийому на роботу якогось працівника; також – в день отримання якогось

замовлення на роботу може бути прийнято декілька працівників.

Відношення „багато – до – багатьох” відрізняється від відношення „один – до –

багатьох”, яке створюється коли в якості зв’язаних стовпчиків використовується

первинний і зовнішній ключі.

Більш типова ситуація, коли в таблиці Zamovlennia реєструється працівник, який

прийняв це замовлення:

Підведемо підсумки:

52

1) в з’єднанні, створеному за допомогою зв’язування головного (первинного) ключа із

зовнішнім ключем завжди існує відношення „один – до – багатьох” (предок/нащадок).

2) в інших з’єднаннях також можуть існувати відношення „один – до – багатьох”, якщо по

крайній мірі в одній таблиці зв’язаний стовпчик містить унікальні значення в усіх

рядках (якщо в обидвох таблицях зв’язані стовпчики містять в усіх рядках унікальні

значення - то в з’єднанні буде відношення „ один – до – одного” ).

3) в загальному випадку в з’єднаннях, створених на основі довільних зв’язаних

стовпчиків, існує відношення „багато – до – багатьох”.

Зауважимо, що відмінності цих трьох випадків не впливають на форму запису оператора

SELECT, який визначає з’єднання. З’єднання всіх трьох видів записуються одним і

тим самим способом: в секції WHERE записується порівняння зв’язаних стовпчиків.

- з’єднання. З’єднання таблиць за нерівністю

(тета) - з’єднання відношення А по атрибуту Х з відношенням В по атрибуту Y –

це результат обчислення виразу.

(A B)

WHERE X Y

де A B – декартовий добуток відношень А і В.

X Y – результат порівняння значень атрибутів Х та Y, який може приймати

значення TRUE, FALSE, NULL.

- одна із операцій відношення: =, <>, >, >=, <, <=

Іншими словами, - з’єднання таблиці А по стовпчику Х з таблицею В по

стовпчику Y, - це результат вибірки з декартового добутку A B тих типів рядків, для

яких умова

X Y

має значення TRUE.

З’єднання за рівністю – це один із частинних випадків - сполучень. Хоча

серед всіх з’єднань найбільш поширеними є з’єднання за рівністю, але інколи

використовуються з’єднання таблиць за допомогою інших операцій порівняння.

7). Вивести дані про працівників (та відповідні філії), які перевищили план продаж

по своїй філії.

- фірма має в різних містах свої філії, в кожній встановлено свій план продаж на

одного працівника. Потрібно преміювати кращих працівників

53

7). SELECT Prizv,Misto,(Prodav - Plan_Prod) AS PerevFROM Pracivnyky,FiliiWHERE Filia = Kod_FiliiAND Prodav > Plan_Prod

В цьому прикладі використовується > - з’єднання таблиць Pracivnyky та Filii

– відбираються лише ті рядки, для яких умова Prodav > Plan_Prod

набуває значення TRUE.

Додаткова умова Filia = Kod_Filii

потрібна для того, щоб вибрати працівників, які працюють в даній філії – тобто, в

результуючій таблиці в кожній парі рядків рядок з таблиці Pracivnyky буде представляти

працівника, який працює у філії, представленій рядком з таблиці Filii.

З’єднання за нерівністю поширені відносно мало, але вони корисні в

системах підтримки прийняття рішень та інших системах, які досліджують більш складні

взаємозв’язки в базі даних.

4. Запити на об’єднання

Деколи буває потрібно об’єднати результати кількох операторів SELECT в одну

таблицю. Це забезпечується операцією UNION.

Приклад 1. Вивести список всіх товарів, ціна яких більше 200 грн. або тих, яких було замовлено більше ніж на 3000 грн. за один раз.

54

Як видно із рисунку, результуючий запис складається із двох частин. Першу частину можна сформулювати так: вивести список всіх товарів, ціна яких більше 200 грн.

SELECT Kod_Vyrobn, Kod_TovaruFROM Tovary

WHERE Cina > 200

Kod_Vyrobn Kod_Tovaru101204101204

4100284441012845

Другу частину запиту можна сформулювати так: вивести список всіх товарів, яких було замовлено більш ніж на 3000 грн. за один раз.

SELECT DISTINCT Vyrobnyk, TovarFROM Zamovlennia

WHERE Vartist > 3000

Vyrobnyk Tovar204204355

284428457755

Операція UNION створює одну таблицю результатів запиту, в якій містяться рядки результатів як першої, так і другої частин.

SELECT Kod_Vyrobn, Kod_TovaruFROM TovaryWHERE Cina > 200

UNIONSELECT DISTINCT Vyrobnyk, Tovar

FROM ZamovlenniaWHERE Vartist > 3000

101204101204355

41002844410128457755

Зауваження:

Імена стовпчиків в двох запитах, які об’єднуються за допомогою операції UNION

не обов’язково повинні співпадати:

1-ша таблиця: Kod_Vyrobnyka, Kod_Tovaru

2-га таблиця: Vyrobnyk, Tovar.

Оскільки стовпчики в двох таблицях можуть мати різні імена, - то стовпчики

результатів запиту на об’єднання, що повертаються операцією UNION,- безіменні.

Щоб таблиці результатів запитів можна було об’єднати за допомогою операції

UNION треба, щоб виконувалися такі умови:

1) результуючі таблиці повинні мати однакову кількість стовпчиків;

55

2) тип даних кожного стовпчика першої таблиці повинен точно співпадати із

типом даних відповідного стовпчика другої таблиці. Якщо це не так, то необхідно

виконати приведення типу за допомогою функції CAST;

3) жодна із двох таблиць не може бути відсортована за допомогою секції

ORDER BY, але об’єднані результати запиту можна буде відсортувати.

Стандарт SQL1 накладає такі обмеження: ті запити, які допускається

використовувати як операнди об’єднання UNION в SELECT повинні мати лише імена

стовпчиків або вказівку виводити всі стовпчики і забороняється використовувати вираз.

Дуже багато СУБД забороняють використовувати в таких операторах SELECT

секції GROUP BY та HAVING, крім того, є такі СУБД (SQL Server), які взагалі не

підтримують самої операції UNION.

При об’єднанні існує велика ймовірність того, що таблиці які об’єднуються будуть

мати рядки, що повторюються.

За замовчуванням операція UNION відкидає рядки дублікати, тому в результуючій

таблиці буде лише по одному рядку із цих товарів (попередній приклад).

Якщо в результуючій таблиці необхідно залишити всі рядки-дублікати, то потрібно

виконати операцію UNION ALL.

Секцію ORDER BY не можна використовувати в жодній вибірці, яка входить до

складу запиту на об’єднання, але результат об’єднання можна відсортувати за допомогою

секції ORDER BY, яка вказується тільки після останнього оператора SELECT, причому в

секції ORDER BY потрібно вказувати номери стовпчиків.

Приклад 2.

56

SELECT *FROM AUNION(SELECT *FROM BUNIONSELECT *FROM C)

Порядок виконання операції UNION, якщо вони однаково опрацьовують рядки-дублікати, немає значення:

(A UNION B) UNION C A UNION (B UNION C) (A UNION C) UNION B

Якщо в одному виразі комбінуються операції UNION та UNION ALL, то порядок виконання цих операцій має суттєве значення.

Приклад 3. A UNION ALL B UNION C1) A UNION ALL (B UNION C)

Результат: 6 рядків з В і С плюс 4 рядки з А і (В і С), загалом дорівнює 10 рядків.2) (A UNION ALL B) UNION C

Результат: 7 рядків.

Виконання запитів на об’єднання здійснюється за наступною схемою:

1. Якщо це запит на об’єднання кількох інструкцій SELECT, то для кожної з цих

інструкцій виконати дії 27 і отримати окрему таблицю результатів.

2. Сформувати декартовий добуток таблиць вказаних у секції FROM.

57

3. Якщо є секції WHERE, то застосувати умову відбору до кожного рядка таблиці

результатів декартового добутку. Залишаться лише ті рядки, для яких умова має значення

TRUE, а рядки, для яких умова має значення FALSE або NULL, відкидаються. Якщо в

секції WHERE міститься підлеглий запит, то він виконується для кожного рядка, що

перевіряється в даний момент.

4. Якщо є секція GROUP BY, то розділити рядки, які залишилися, на групи так,

щоб в кожній групі рядки мали однакові значення в усіх стовпчиках групування.

5. Якщо є секція HAVING, то застосувати задану в ній умову відбору до кожної

групи. Залишаться лише ті групи, для яких умова має значення TRUE. Якщо ж умова має

значення FALSE або NULL, то така група відкидається. Якщо секція HAVING містить

підлеглий запит, то він виконується для кожної групи, що перевіряється в даний момент.

6. Обчислити значення кожного елемента списку SELECT і створити один рядок

таблиці результатів запиту. Якщо є групування, то кожна група буде представлена одним

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

результаті одного рядка в результаті третьої дії.

7. Якщо є слово DISTINCT, то вилучити всі рядки-дублікати.

8. Якщо це запит на об’єднання, то об’єднати результати окремих інструкцій

SELECT в одну таблицю. При цьому, якщо використовується UNION то відкидаються

рядки-дублікати, якщо ж використовується операція UNION ALL, то рядки-дублікати

залишаються.

9. Якщо є секція ORDER BY, то відсортувати результати запиту.

Контрольні питання:1. Що таке псевдонім таблиці, для чого його використовують?2. Для чого створюють з’єднання таблиці з собою?3. Що називається з’єднанням таблиць?4. В якій секції вказується з’єднання? 5. Який тип відношення створюється при зв’язуванні первинного ключа із зовнішнім

ключем?6. Що таке - з’єднання?7. Що створюється операцією UNION?

58

Тема 8. Введення обмежень в базах даних 1. Види обмежень в базі даних.2. Первинний і унікальний ключі.3. Зовнішні ключі.4. Обмеження CHECK.5. Вилучення обмежень.

1. Види обмежень в базі даних.

Таблиця 8.1. Обмеження

ОБМЕЖЕННЯ ОПИСNOT NULL He дозволяє привласнювати стовпцю значення nullDEFAULT Задає для стовпця значення за замовчуваннямPRIMARY KEY Задає стовпець (стовпці) первинного ключа для таблиціFOREIGN KEY Задає стовпець (стовпці) повторного ключа для таблиціUNIQUE He дозволяє додавати в стовпець значення, що повторюються

CHECK Обмежує значення, які можуть додаватися в стовпець, за допомогою логічних виразів

Основні принципи роботи з обмеженнями

Обмеження дозволяють вказати вимоги до значень в стовпцях (табл. 8.1). Ваша

СУБД буде використовувати ці вимоги, щоб автоматично редагувати таблицю.

Обмеження бувають двох типів:

• обмеження стовпця є частиною опису стовпця і діє тільки для даного стовпця;

• обмеження таблиці не залежить від обмежень стовпця і може впливати на

декілька стовпців в таблиці. Щоб включити в обмеження вимоги для декількох стовпців,

слід використовувати обмеження таблиці.

Ви можете вказати декілька обмежень для стовпця або таблиці. Використання

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

стовпець, ви можете задати його як обмеження стовпця або таблиці. Якщо первинний

ключ включає два стовпці або більш, слід використовувати обмеження таблиці. Якщо ви

вкажете назви обмежень, це спростить роботу з ними. Наприклад, ви легко зможете

змінити або видалити таке обмеження за допомогою команди ALTER TABLE. Назви

обмежень вводити необов'язково, але багато які SQL -програмісти і дизайнери баз даних

присвоюють назви всім обмеженням. Ви можете не давати назв обмеженням NOT NULL і

DEFAULT, але ми рекомендуємо вказати назви всіх інших обмежень (не дивлячись на те,

що в прикладах даної книги ми цього не зробили). Якщо ви не присвоїли назви

обмеженню, СУБД зробить це за вас. Подібні назви включають багато символів, і їх дуже

непросто використовувати; краще за допомогою команди CONSTRAINT придумайте

59

власну назву обмеженню. Такі назви в попередженнях і повідомленнях про помилки, що

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

Присвоєння назви обмеженню

Перш ніж вводити опис обмеження, надрукуйте:

CONSTRAINT constraint name

constraint name - це назва обмеження, яка буде використовуватися SQL для його

ідентифікації. Такі назви для однієї таблиці повинні бути унікальними.

Обмеження з назвами будуть показані далі в прикладах цього розділу.

MySQL не підтримує пропозицію CONSTRAINT для обмежень стовпців (але ви

можете використовувати його для обмежень таблиць).

Існують такі види обмежень в базі даних InterBase:

первинний ключ – PRIMARY KEY;

унікальний ключ – UNIQUE KEY;

зовнішній ключ – FOREIGN KEY – може включати автоматичні тригери

ON UPDATE і ON DELETE; перевірки – CHECK.

Обмеження бази даних бувають двох типів – на основі одного поля і на основі

декількох полів таблиці. Розглянемо створення обмежень детальніше. Першим в описі

загального синтаксису обмежень йде ключове слово [CONSTRAINT constraint_name].

Тут CONSTRAINT означає обмеження, а constraint_name – ім’я цього обмеження

(необов’язкове).

За допомогою ключового слова CONSTRAINT можна задавати ім’я створюваному

обмеженню і у випадку використання синтаксису обмежень на основі одного поля, і у

випадку обмежень на основі декількох полів.

Якщо не вказати ім’я для обмеження, InterBase автоматично генерує його. Однак

краще все ж явно назначити ім’я, щоб покращити читабельність схеми бази даних.

Призначивши ім’я обмеженню, необхідно задати його тип.

2. Первинний і унікальний ключі.

Первинні ключі є одним з основних видів обмежень в базі даних. Вони

використовуються для однозначної ідентифікації записів у таблиці. Первинний ключ являє

собою одне або декілька полів в таблиці, комбінація яких унікальна для кожного запису.

Для однієї таблиці не існують повторювані значення первинного ключа.

Унікальні ключі несуть аналогічне навантаження – вони також використовуються

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

унікальних полягає в тому, що первинний ключ може бути в таблиці лише один, а

60

унікальних ключів – декілька. Слід зауважити, що і первинний і унікальний ключ можуть

використовуватися як основа для посилання для зовнішніх ключів.

Синтаксис створення первинного і унікального ключів на основі декількох полів: <PkUktconstraint> = [CONSTRAINT constraint] {PRIMARY KEY|UNIQUE) (col [,

col ...])

CREATE TABLE PkUk2( Number1 INTEGER NOT NULL, Namel VARCHAR(5O) NOT NULL, Kol INTEGER NOT NULL, Stoim NUMERIC(15,4) NOT NULL, CONSTRAINT Pkt PRIMARY KEY (Numberl, Namel),

/* первинний ключ Pkt на основі двох полів */ CONSTRAINT Uktl UNIQUE (kol, Stoim) );

/* унікальний ключ Uktl на основі двох полів */

Всі поля, які входять в склад первинного і унікального ключів, повинні

декларуватися як NOT NULL, так як ці ключі не можуть мати невизначеного значення.

Окрім створення обмежень первинних і унікальних ключів в момент створення

таблиці існує можливість додавати обмеження в уже існуючу таблицю. Для цього

використовується команда ALTER TABLE:

ALTER TABLE tablename ADD [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE)( col [, col ...] )

Давайте розглянемо приклад створення первинного і унікального ключа за допомогою ALTER TABLE. Спочатку створимо таблицю:

CREATE TABLE PkAlter( ID1 INTEGER NOT NULL, ID2 INTEGER NOT NULL, UID VARCHAR(24));

Потім добавимо ключі. Спочатку первинний:

ALTER TABLE PkAlter ADD CONSTRAINT Pkall PRIMARY KEY (idl, id2);

потім унікальний ключ:

ALTER TABLE PkAlter ADD CONSTRAINT Ukal UNIQUE (uid) ;

Важливо відмітити, що додавання (а також вилучання) обмежень первинних і

унікальних ключів до таблиці може здійснювати лише власник цієї таблиці або системний

адміністратор SYSDBA.

3. Зовнішні ключі.

Наступним обмеженням, яке часто використовується в базах даних InterBase, є

обмеження зовнішнього ключа. Це дуже потужний засіб для підтримання цілістності за

61

посиланнями в базі даних, яке дозволяє не лише контролювати наявність правильних

посилань в базі даних, але й автоматично керувати цими посиланнями!

Смисл створення зовнішнього ключа такий: якщо дві таблиці використовуються

для збереження взаємопов’язаної інформації, то необхідно гарантувати, щоб цей

взаємозв’язок був завжди коректний. Приклад — документ "накладна", що містить

спільний заголовок (дата, номер накладної і т. п.) і множина детальних записів

(найменування товару, кількість і т. п.).

Для збереження такого документа в базі даних створюються дві таблиці – одна для

збереження заголовків накладних, а друга – для збереження вмісту накладної – записів

про товари і їх кількість. Такі таблиці називаються головною і підпорядкованою або

таблицею-майстером і деталь-таблицею.

Згідно із здоровим глуздом неможливе існування вмісту накладної без наявності її

заголовка. Іншими словами, ми не можемо вставляти записи про товари, не створивши

заголовок накладної, а також не можемо вилучати запис заголовка, якщо існують записи

про товари.

Для реалізації такої поведінки таблиця заголовка з’єднується з таблицею

подробиць за допомогою обмежень зовнішнього ключа.

Давайте розглянемо смисл накладених обмежень зовнішнього ключа на прикладі

таблиць, що містять інформацію про накладні.

Для цього створимо дві таблиці для збереження накладної – таблицю SALES для

збереження заголовку і таблицю ITEMS для збереження інформації про товари, що

входять до накладної.

CREATE TABLE SALES ( SALE_NUMBER INTEGER NOT NULL, CUSTOMER_ID INTEGER, SALE_DATE TIMESTAMP, TOTAL_PRICE DOUBLE PRECISION,CONSTRAINT "PSALE_NUMBER" PRIMARY KEY (SALE_NUMBER) );

Зверніть увагу на те, що ми відразу задали первинний ключ в таблиці заголовка на

основі поля SALE_NUMBER – номер покупки. Решта полів таблиці SALES містять

інформацію про заголовок накладної – ідентифікаційний номер покупця, дату закупки,

суму, заплачену покупцем по всім накладним даної купівельної операції. Тепер визначимо

таблицю для збереження інформації про товарах, що входять до накладної:

CREATE TABLE ITEMS ( SALE_NUMBER INTEGER, ITEM_NO INTEGER, PART_NO VARCHAR(10), QTY SMALLINT);

62

Давайте розглянемо поля таблиці ITEMS. По перше, ще поле ITEM_NO, яким

ідентифікується номер накладної. По-друге – це SALE_NUMBER – цілочисельне поле, яке

використовується для посилання на ідентифікатор заголовка SALE_NUMBER в таблиці

заголовків накладних SALES. Далі іде поле PART_NO – поле, яке використовується для

посилання на ідентифікатор PART_NUMBER (номер товару) в таблиці PART, і QTY – що

описує сумарну вартість купленого товару.

В принципі, ніщо не заважає нам заповнити таблицю ITEMS записами, що

посилаються на неіснуючі записи в таблиці SALES. Також нічого не заважає вилученню

заголовка вже існуючої накладної, в результаті чого записи про товари можуть стати

"нічиїми". Це ж саме можна сказати і про поле PART_NO.

Сервер не буде перешкоджати всім цим вставкам і вилученням. Таким чином,

контроль за цілістністтю даних в базі даних повністю покладається на клієнтську

програму. А оскільки з однією базою даних можуть працювати декілька прикладних

програм, що розробляються різними програмістами, то це може призвести до різної

інтерпретації даних і до помилок.

Тому необхідно явно накласти обмеження на те, що в таблицю ITEMS можуть

вміщуватися лише такі записи про товари, які мають коректне ПОСИЛАННЯ на заголовок

накладної. Власне це і є обмеження зовнішнього ключа, яке дозволяє вставляти в поля, що

входять в обмеження, лише ті значення, які є в іншій таблиці.

Таке обмеження можна організувати за допомогою зовнішнього ключа. Для даного

прикладу необхідно накласти обмеження зовнішнього ключа на поле SALE_NUMBER і

пов’язати його з первинним ключем SALE_NUMBER в SALES. Додати зовнішні ключі в

уже існуючу таблицю можна такими командами:

ALTER TABLE ITEMS ADD FOREIGN KEY (PART_NO) REFERENCES PART (PART_NUMBER);ALTER TABLE ITEMS ADD FOREIGN KEY (SALE_NUMBER) REFERENCES SALES (SALE_NUMBER);

Тут ITEMS – ім’я таблиці, на яку накладається обмеження зовнішнього ключа; PART та

SALES – імена таблиць, що надають значення (ОСНОВУ посилання) для зовнішнього

ключа. В дужках наводяться імена полів, по яким встановлюється зв’язок.

В наведених прикладах перед параметром FOREIGN KEY може розміщуватися

CONSTRAINT foreign_name для того, щоб задати прямо ім’я обмеження. Якщо іде

зв’язок по групі полів, всі вони перелічуються в дужках через кому.

63

4. Обмеження CHECK

Одним з найбільш корисних обмежень в базі даних є обмеження перевірки. Ідея

його проста – перевіряти вставлюване в таблицю значення на деяку умову і, в залежності

від виконаної умови, вставляти чи не вставляти дані.

Синтаксис цього обмеження:

< tconstraint > = [CONSTRAINT constraint] CHECK (<search_condition>)

Тут constraint – ім’я обмеження; <search_condition> – умови пошуку, в якому в ролі

параметра може приймати участь вставлене/змінене значення. Якщо умови пошуку

виконуються, то вставка/зміна цього значення дозволяється, якщо ні – виникає помилка.

Самий простий приклад перевірки:

CREATE TABLE Checktst (ID integer CHECK(ID>0));

Ця перевірка встановлює, чи більше нуля вставлюване/змінюване значення поля

ID, і в залежності від результату дозволяє вставити/змінити нове значення чи викликати

виключення. Поле може мати лише одне обмеження CHECK.

5. Вилучення обмежень.

Часто доводиться вилучати різні обмеження по різним причинам. Щоб вилучити

обмеження, слід використати речення ALTER TABLE такого виду:

ALTER TABLE Сablename

DROP CONSTRAINT constraint_name

де constraint_name – ім’я обмеження, яке слід вилучити. Слід відзначити, що

вилучати обмеження може лише власник таблиці або системний адміністратор SYSDBA.

Контрольні питання:1. Вкажіть обмеження в базі даних.2. Які типи обмежень існують?3. Яка команда створює обмеження?4. Яке обмеження створює первинний ключ?5. Як додати обмеження в уже існуючу таблицю?6. Що виконує обмеження CHECK? Яка його структура?7. Коли доцільно використовувати обмеження DEFAULT?

64

Тема 9. Використання збережуваних процедур в базах даних. Використання генераторів ключів у базах даних в InterBase. Використання тригерів у

базах даних.1. Використання збережуваних процедур в базах даних.2. Використання генераторів ключів в базах даних в InterBase. 3. Використання тригерів в базах даних.

1. Визначення збережуваної процедури.

Збережувані процедури (англ. stored procedures) дозволяють реалізувати значну

частину логіки програми на рівні бази даних і в такий спосіб підвищити продуктивність

усієї програми, централізувати обробку даних і зменшити кількість коду, необхідного для

виконання поставлених задач. Практично будь-яка досить складна програма баз даних не

обходиться без використання збережуваних процедур.

Збережувана процедура (ЗП) – це частина метаданих бази даних, що представляє

собою відкомпільовану у внутрішнє представлення InterBase підпрограму, написану

спеціальною мовою, компілятор якої убудований у ядро сервера InterBase.

Основою могутніх можливостей, закладених у ЗП, є процедурна мова

програмування, що має у своєму складі як модифіковані речення звичайного SQL, такі, як

INSERT, UPDATE і SELECT, так і засобу організації розгалужень і циклів (IF, WHILE),

а також засоби обробки помилок і виняткових ситуацій. Мова збережуваних процедур

дозволяє реалізувати складні алгоритми роботи з даними, а завдяки орієнтованості на

роботу з реляційними даними ЗП виходять значно компактнішими аналогічних процедур

на традиційних мовах.

Синтаксис збережуваних процедур описується у такий спосіб:

CREATE PROCEDURE name I [ ( param datatype [, param datatype...])]

[RETURNS ( param datatype [, param datatype ...])]AS <procedure_body>;

< procedure_body> = [<variable_declaration_list>]

< block> < variable_declaration_list> = DECLARE VARIABLE var datatype; [DECLARE VARIABLE var datatype;

...]<block> =BEGIN < compound_statement> [< compound_statement> ...] ; END

65

Розглянемо, як приклад, дуже просту збережувану процедури, що приймає на вході

два числа, складає їх і повертає отриманий результат:

CREATE PROCEDURE SP_Add(first_arg DOUBLE PRECISION, secouond_arg DOUBLE PRECISION)

RETURNS (Result DOUBLE PRECISION)BEGIN

Result=first_arg+second_arg;SUSPEND;

END

Після команди CREATE PROCEDURE вказується ім'я знову створюваної

процедури (яке повинно бути унікальним у межах бази даних) – у даному випадку

SP_Add, потім у дужках через кому перелічуються вхідні параметри ЗП – first_arg і

second_arg – із вказанням їхніх типів.

Список вхідних параметрів є необов'язковою частиною оператора CREATE

PROCEDURE – бувають випадки, коли всі дані для своєї роботи процедура одержує за

допомогою запитів до таблиць усередині тіла процедури.

(У збережуваних процедурах використовуються будь-які скалярні типи даних

InterBase. He передбачене застосування масивів і типів, обумовлених користувачем, -

доменів).

Далі йде ключове слово RETURNS, після якого в дужках перелічуються

параметри, що повертаються, із вказівкою їхніх типів – у даному випадку лише один –

Result. Якщо процедура не повинна повертати параметри, то слово RETURNS і список

параметрів, що повертаються, відсутні.

Після RETURNS() зазначене ключове слово AS. До ключового слова AS йде

заголовок, а після нього – тіло процедури.

Тіло збережуваної процедури являє собою перелік описів її внутрішніх (локальних)

змінних , і блок операторів, охоплений операторними дужками BEGIN END. У даному

випадку тіло ЗП дуже просте – ми просто складаємо два вхідних аргументи і присвоюємо

результат вихідному, а потім викликаємо команду SUSPEND. Трохи пізніше ми

роз'яснимо зміст дії цієї команди, а поки лише відзначимо, що вона потрібна для передачі

параметрів, що повертаються, туди, відкіля була викликана збережувана процедура.

Зверніть увагу, що оператор усередині процедури закінчується крапкою з комою

(;). Як відомо, крапка з комою є стандартним роздільником команд у SQL – вона є

сигналом інтерпретатору SQL, що текст команди введений цілком і треба починати його

обробляти. Чи не вийде так, що, знайшовши крапку з коми в середині ЗП, інтерпретатор

SQL вирішить, що команда введена цілком і спробує виконати частина збережуваної

процедури? Це припущення не позбавлене змісту.

66

Команда ISQL, що змінює роздільник пропозицій SQL, виглядає так:

SET TERM <new_term><old_term>

Для типового випадку створення збережуваної процедури це виглядає так:

SET TERM ^;CREATE PROCEDURE some_procedureEND SET TERM ;^

Тепер, коли процедура створена, її треба якось викликати, передати їй параметри й

одержати результати, що повертаються. Це зробити дуже просто – досить написати SQL-

запит наступного виду:

SELECT *

FROM Sp_add(181.35.23.09)

Цей запит поверне нам один рядок, що містить всього одне поле Result, у якому

буде знаходитися сума чисел 181.35 і 23.09 тобто 204.44.

Таким чином, нашу процедуру можна використовувати в звичайних SQL-запитах,

що виконуються як у клієнтських програмах, так і в інших ЗП чи тригерах. Таке

використання нашої процедури стало можливим через застосування команди SUSPEND

наприкінці збережуваної процедури.

Для того щоб оголосити локальну змінну у збережуваній процедурі, необхідно

помістити її опис після ключового слова AS і до першого слова BEGIN. Опис локальної

змінної виглядає так:

DECLARE VARIABLE <variable_name> <variable_type>;

1.2. Цикли й оператори розгалуження.

Основна проблема "вбудовування" речення SELECT в процедуру полягає в тому,

що SELECT, як правило, породжує таблицю з множиною рядків і стовпців, а включаюча

мова не має засобів, що дозволяють оперувати одночасно більш ніж одним записом. З цієї

причини необхідно забезпечити міст між рівнем множин мови SQL і рівнем записів

процедури.

Для цього використовується одиничне SELECT, формат якого має вид

SELECT { * | элемент_select [, элемент_select] ...}INTO змінна [, змінна] ...FROM базова_таблиця [, базова_таблиця] ...[WHERE умова ];Очевидно, що цей опис відрізняється від стандартного підзапиту наявністю фрази

INTO і включенням змінних.

67

Наведемо приклад. Одержати загальну вагу продуктів на складі і занести її в

змінну Загальна_вага:

SELECT SUM(Кількість)INTO Загальна_вагаFROM Склад;

Якщо запит повертає більш одного рядка, то його необхідно оформити у виді

конструкції FOR SELECT...DO, що організує цикл для обробки кожного рядка, що

повертається.

FOR

SELECT ID, NAME, PRICE_1

FROM Table_Example

INTO :ID, :NAME, :new_price

DO

BEGIN

/*тут обробляємо кожен запис*/

END

Зверніть увагу, що змінні ID, NAME, new_price випереджаються двокрапкою – для того, щоб відрізнити їх від полів, що використовуються у запиті.

Крім команди FOR SELECT... DO, що організує цикл по записах будь-якої вибірки, існує інший вид циклу – WHILE...DO, що дозволяє організувати цикл на основі перевірки будь-яких умов. Ось приклад ЗП, що використовує цикл WHILE. ..DO. Ця процедура повертає квадрати цілих чисел від 0 до 99:

CREATE PROCEDURE QUADRETURNS (QUADRAT INTEGER)

ASDECLARE VARIABLE I INTEGER;BEGIN I=1 WHILE (i<100) DO BEGIN QUADRAT = I*I; SUSPEND; END END

Крім перебору результатів SQL-вибірки і класичного циклу, у мові збережуваних

процедур використовується оператор IF...THEN..ELSE, що дозволяє організувати

розгалуження в залежності від виконання яких-небудь умов.

Давайте розглянемо більш складний приклад збережуваної процедури, що робить

наступне:

1. Обчислює середню ціну в таблиці Table_example.

68

2. Далі для кожного запису в таблиці робить наступну перевірку: якщо існуюча

ціна (PRICE) більше середньої ціни, то встановлює ціну, рівну величині середньої ціни,

плюс фіксований відсоток, що задається.

3. Якщо існуюча ціна рівна чи менша середньої ціни, то встановлює ціну, рівну

колишній ціні, плюс половина різниці між колишньою і середньою ціною.

4. Повертає всі змінені рядки в таблиці.

Збережувана процедура, що реалізує це завдання має вид:

CREATE PROCEDURE IncreasePrices (Percent2Increase DOUBLE PRECISION) RETURNS (ID INTEGER, NAME VARCHAR(8O), new_price DOUBLE PRECISION) AS DECLARE VARIABLE avg_price DOUBLE PRECISION;

BEGIN SELECT AVG(Price_l) FROM Table_Example INTO :avg_price ; FOR SELECT ID,NAME, PRICE_1 FROM Table_Example INTO :ID, :NAME,:new_price DO BEGIN/*тут обробляємо кожен запис*/ IF (new_price > avg__price) THEN /*якщо існуюча ціна більше середньої ціни*/ BEGIN /*установимо нову ціну, рівну величині середньої ціни, плюс фіксований відсоток */ new_price = (avg_price + avg_price*(Percent2Increase/100); UPDATE Table_example SET PRICE_1 = :new_price WHERE ID = :ID; END ELSE BEGIN

/* Якщо існуюча ціна чи менше дорівнює середній ціні, то встановлює ціну, рівну колишній ціні, плюс половина різниці між колишньою і середньою

ціною */ new_price = (new_price + ((avg_price - new_price)/2));

UPDATE Table_example SET PRICE_1 = :new_price WHERE ID = :ID; END SUSPEND; END END

2. Створення генераторів.

Що таке генератор? Генератор – це іменований лічильник. Всередині бази даних

ми можемо створити лічильник, дати йому унікальне ім’я в межах цієї бази і керувати

значеннями цього лічильника. Це і буде генератор. Приклад:

CREATE GENERATOR g1; SET GENERATOR g1 TO 2445;

69

В цьому прикладі в першому рядку бази даних створюється генератор з іменем g1,

а у другому – цьому генератору присвоюється значення 2445. Щоб одержати і змінювати

значення генераторів, існує функція GEN_ID.

Ця функція приймає в ролі параметрів ім’я генератора і величину приросту, яку

потрібно надати даному генератору, а повертає цілочисельне значення, що відповідає

значенню генератора, одержаному в результаті додавання до нього приросту. Ось кілька

прикладів виклику функції GEN_ID:

Current_value = GEN_ID (g1, 0)

Тут current_value – це змінна, g1 – генератор, і 0 – це приріст. В цьому прикладі в

змінну current_value попаде значення генератора g1 після додавання до нього приросту 0 –

тобто поточне значення генератора!

Для одержання наступного значення генератора слід викликати функцію GEN_ID з

ненульовим приростом:

Current_value = GEN_ID (g1, 1)

Приріст може бути не рівним одиниці – він може бути довільним, в тому числі і

плаваючим, і навіть від’ємним:

Current_value = GEN_ID (g1, -23)

В лиці генераторів ми маємо надійний механізм для формування унікальних

первинних ключів. Як тепер вмістити одержуване від генератора значення в полі

первинного ключа? Для цього є два способи – вставка первинного ключа на стороні

клієнта і на стороні сервера.

У випадку формування первинного ключа на клієнті відбувається таке. Коли

сформовано запис, який буде уставлено до бази даних, виконується виклик функції

GEN_ID(generator, 1) і одержане значення підставляється в сформований запис.

Відбувається вставка в таблицю, при цьому ми одержуємо гарантовано унікальний

первинний ключ.

Другий спосіб – формування первинного ключа на стороні сервера – взагалі

виключає вся турботу на стороні клієнта про те, яким буде значення первинного ключа. В

цьому випадку при вставці запису спрацьовує тригер – спеціальний об’єкт БД, який може

здійснювати діє при вставці/вилученні/зміні записів в таблицях. И в цьому тригері

відбувається виклик функції GEN_ID, одержання потрібного значення генератора і

вставка його в таблицю.

2.1. Індекси.

Індекс – це впорядкований вказівник на записи в таблиці.

70

Вказівник означає, що індекс містить значення одного або декількох полів в

таблиці і адреси розміщення цих даних. Іншими словами, індекс складається з пар значень

"значення поля" – "фізичне розміщення цього поля". Таким чином, за значенням поля (або

полів), що входять до індексу, за допомогою індексу можна швидко знайти те місце в

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

значення полів, які зберігаються в індексі, впорядковані.

Єдине, чому сприяють індекси, – це прискорення пошуку запису за його

індексованим полем (індексоване – те що входить до індексу). Пошук за допомогою

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

таблиці.

Індекс не є частиною таблиці – це окремий об’єкт, пов’язаний з таблицею і іншими

об’єктами бази даних. Це дуже важливий момент реалізації СУБД, що дозволяє відділити

збереження інформації від її подання.

Індекси використовуються в трьох основних випадках:

1. Прискорення виконання запитів. Індекси створюються для полів, які

використовуються в умовах пошуку SQL-запитів.

2. Забезпечення унікальності значень в полях. Обмеження первинного ключа

вимагає, щоб в усій таблиці не знайшлося двох однакових значень полів, що входять до

первинного ключа. Щоб виконати цю умову, необхідно при кожній вставці нового запису

здійснювати пошук такого ж значення, яке буде вставлене.

3. Забезпечення цілісності посилань. Обмеження зовнішніх ключів Foreign key

використовується для перевірки того, щоб значення, які вставляються до таблиці,

обов’язково існували в іншій таблиці. При створенні зовнішнього ключа автоматично

створюється індекс, який використовується як для прискорення запитів, що

використовують з’єднання таблиць, так і для перевірки умов зовнішнього ключа.

Формат команди, що створює індекси має вид: CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]]

INDEX index ON table (col [, col ...]);

Мінімальним виразом, що створює індекс, є:

CREATE INDEX my_index ON Table_example(ID)

В цьому прикладі створюється індекс з іменем my_index для таблиці

Table_example, причому індексованим полем є поле ID. Індекс є зростаючим, тобто

значення в ньому впорядковані за зростанням, а також не унікальним, тобто значить, що

поле ID може мати декілька однакових значень. Це самий простий приклад індексу –

самий поширений.

Як бачимо з опису синтаксису, індекс може містити не одне, а декілька полів.

71

У визначенні індексу є ще одна опція – UNIQUE. Якщо її вказати, то індекс

дозволить заносити до таблиці лише унікальні значення. Фактично це служить основою

для реалізації унікальних ключів (UNIQUЕ KEY).

2.2. Обробка виключень і помилок.

Виключення InterBase багато в чому схожі на виключення інших мов високого

рівня, однак мають свої особливості. Фактично виключення InterBase – це повідомлення

про помилку, що має власне, яке задається програмістом, ім'я і текст повідомлення про

помилку. Створюється виключення в такий спосіб:

CREATE EXCEPTION <ім'я_виключення> <текст_виключення>;

Наприклад, ми можемо створити виключення такого виду:

CREATE EXCEPTION test_except 'Test exception';

Виключення легко чи вилучити чи змінити – вилучення задається командою

DROP EXCEPTION <ім'я_ вилучаємого_виключення>, а зміна – ALTER

EXCEPTION <ім'я_виключення> <текст_виключення>.

Щоб використовувати виключення в збережуваній процедурі чи тригері, необхідно

скористатися командою наступного виду:

EXCEPTION <ім'я_виключення>;

Давайте розглянемо застосування виключень на простому прикладі збережуваної

процедури, що виконує ділення одного числа на інше і повертає результат. Нам необхідно

відстежити випадок поділу на нуль і викликати виключення, якщо дільник дорівнює

нулю.

Для нашого приклада створимо наступне виключення:

CREATE EXCEPTION zero_divide 'Cannot divide by zero!';

Створимо збережену процедуру, що використовує це виключення:

CREATE PROCEDURE SP_DIVIDE(DILENE DOUBLE PRECISION, DILNYK DOUBLE PRECISION) RETURNS(RESULT DOUBLE PRECISION)ASBEGINif (Dilnyk<0.0000001) then BEGIN EXCEPTION zero_divide; Result=0; END ELSE BEGINResult=Dilene/Dilnyk; END SUSPEND;

72

END

Як бачите, текст ЗП тривіальний – на вході одержуємо Dilene і Dilnyk, потім

порівнюємо Dilnyk з 0.0000001, тобто фактично з нулем, у межах обраної погрішності в

одну десятимільйонну (тому що дійсні числа неможливо безпосередньо порівнювати

через погрішності в дробовій частині). Якщо Dilnyk близький до нуля в межах обраної

похибки, то ми збуджуємо виключення zero_divide. Що ж відбувається у випадку

виникнення виключення? Якщо ми спробуємо викликати виключення, виконуючи

процедуру SP_devide з нульовим дільником у ISQL, то одержимо наступне:

SQL> select * from sp_divide(300,0);

RESULT

Statement failed, SQLCODE = -836exception 1-Cannot divide by zero!

Іншими словами, повідомлення про помилку – це результат обробки нашого

виключення сервером InterBase. Коли InterBase виявляє появу в ЗП чи тригері

виключення, він перериває роботу цієї збережуваної процедури і відкочує всі зміни,

зроблені в поточному блоці BEGIN...END.

3. 1.Означення тригера.

Тригер у InterBase – це особливий вид збереженої процедури, що виконується

автоматично при вставці, чи вилученні або модифікації запису чи таблиці зображення

(view). Тригери можуть "спрацьовувати" безпосередньо до чи відразу ж послу зазначеної

події.

Як ви знаєте, SQL дає можливість вставляти, вилучати і модифікувати дані в

таблицях бази даних за допомогою відповідних команд – INSERT, DELETE і UPDATE.

Погодитеся, що було б непогано мати можливість перехопити передану команду і що-

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

записати ці дані в спеціальну табличку, а заодно записати, хто і коли зробив операцію над

даною таблицею. Чи відразу ж перевірити дані, що вставляються, на яку-небудь хитру

умову, яку неможливо реалізувати за допомогою опції CHECK, і в залежності від

результатів перевірки прийняти проведені зміни чи відкинути їх; змінити ці дані на

підставі деякого запиту чи змінити дані в інших зв'язаних таблицях.

Для того, щоб виконувати які-небудь дії, зв'язані зі зміною даних у базі даних, і

існують тригери.

73

Фактично тригер являє собою набір команд процедурної мови InterBase, що

реалізується при виконанні операцій INSERT/DELETE/UPDATE. На відміну від

збережуваних процедур, тригер ніколи нічого не повертає (та й кому повертати, адже

тригер явно не викликається). По тій же причині він не має також вхідних параметрів, але

замість них має контекстні змінні NEW і OLD. Ці змінні дозволяють одержати доступ до

полів таблиці, до якої приєднаний тригер.

Тригеру призначена роль віртуального цензора, що переглядає "листи" і який

вільний зробити усе, що завгодно, – пропустити їх незмінними, підправити їх,

просигналізувати про помилки чи навіть "докласти про це" кому слід.

Тригер завжди прив'язаний до якоїсь певної таблиці чи зображення і може

"перехоплювати" дані тільки цієї таблиці. Давайте розглянемо класифікацію тригерів і

призначення кожного виду. Як уже було сказано, існує 3 основних SQL-операції,

застосовні до даних, – INSERT/DELETE/UPDATE. Відповідно перший поділ тригерів –

за операціями, що обслуговуються. Кожен конкретний тригер прив'язаний до якої-небудь

операції, тобто тригер спрацьовує, коли в "його" таблиці відбувається дана операція.

Спрацьовування тригера може відбуватися як "до" так і "після" операції. Таким

чином, ми одержуємо 6 можливих видів тригерів на таблицю – до і після кожної з трьох

можливих SQL-операції.

3.2. Приклад тригера.

Давайте розглянемо простий приклад тригера, що спрацьовує ДО ВСТАВКИ в

таблицю і заповнює поле первинного ключа.

CREATE TABLE Table_example ( ID INTEGER NOT NULL, NAME VARCHAR(80),PRICE_1 DOUBLE PRECISION,CONSTRAINT pkTable PRIMARY KEY (ID));

Тут поле ID є первинним ключем і значення цього ключа повинні бути

унікальними в межах таблиці. Щоб забезпечити виконання цієї вимоги, створимо

генератор і тригер, що буде одержувати значення генератора і підставляти його в

таблицю. Таким чином, у полі ID завжди будуть унікальні значення, тому що значення

генератора буде збільшуватися щораз при звертанні до тригера. Отже, створюємо

генератор:

CREATE GENERATOR GEN_TABLE_EXAMPLE_ID;

І встановлюємо його початкове значення в одиницю:

SET GENERATOR GEN_TABLE_EXAMPLE_ID TO 1;

74

Тепер необхідно створити тригер. Треба сказать, що тригер, як і збережувана

процедура, може містити у своєму тілі кілька операторів, розділених крапкою з комою.

Тому вам необхідно скористатися командою зміни роздільника команд SET TERM, як це

було описано в розділі "Збережувані процедури". Ми ж будем наводити тексти тригерів

без обрамлення командами зміни роздільника.

Отже, розглянемо текст нашого тригера:

CREATE TRIGGER Table_example_bi FOR Table_exampleACTIVE BEFORE INSERT POSITION 0ASBEGIN IF (NEW.ID IS NULL) THEN NEW.ID = GEN_ID(GEN_TABLE_EXAMPLE_ID,1);END

Як бачите, тригер дуже нагадує збережувану процедуру (фактично, як уже було

сказано, це і є особливий різновид ЗП), але є і декілька відмінностей. Давайте докладно

розберемо "будову" тригера.

Опис команди створення тригера починається з ключових слів CREATE

TRIGGER, після яких з’являється ім'я тригера – Table_example_bi. Потім слідує ключове

слово FOR, після якого зазначене ім'я таблиці, для якої створюється тригер, –

Table_example.

На другому рядку команди приводиться опис сутності тригера – ключове слово

ACTIVE указує, що тригер є "активним". Тригер також може бути переведений у стан

INACTIVE. Це означає, що він буде зберігатися в базі даних, але він не буде

спрацьовувати. Сполучення ключових слів BEFORE INSERT визначає, що тригер

спрацьовує ДО ВСТАВКИ; а ключове слово POSITION і число 0 указують черговість

(позицію) створюваного тригера серед тригерів того ж типу для даної таблиці. Позиція

тригера потрібна тому, що в InterBase можливо створити більш 32000 тригерів кожного

виду (наприклад, BEFORE INSERT чи AFTER UPDATE), і серверу потрібно вказати, у

якому порядку ці тригери будуть виконуватися. Тригери з меншою позицією виконуються

першими. Якщо є декілька тригерів з однакової позицією, то вони будуть виконуватися за

абеткою.

Усе розглянуте вище до ключового слова AS утворює заголовок тригера. Після AS

слідує тіло тригера. Власне в тілі і здійснюється вставка значення в поле первинного

ключа. Але спочатку за допомогою вже знайомого вам оператора IF.. .THEN

перевіряється, чи не було заповнено це поле на клієнті. Вираз перевірки, що повертає

булеву TRUE (істина) чи FALSE (неправда), виглядає так:

75

NEW.ID IS NULL

Тут NEW – контекстна змінна.

3.3. Контекстні змінні.

Як уже відмічалося вище, тригер схожий на цензора, що безцеремонно оглядає усе,

що відноситься до цікавлячого його предмету. Інтерес нашого тригера описаний

сполученням ключових слів BEFORE INSERT – це значить, що всі операції вставки

(INSERT) викликають спрацьовування тригера. Причому він спрацює ДО (BEFORE)

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

ким-небудь на вставку, ще не занесені в таблицю. Вони знаходяться в деякому

проміжному буфері. І в тригера є можливість звертатися до цього буфера, щоб перевірити

і/або змінити значення даних – кандидатів на вставку. Ця можливість реалізована за

допомогою контекстної змінної NEW. Можна розглядати цю змінну як структуру (щось

подібне struct у Сі чи record у Pascal), елементи якої являють собою значення, прислані для

здійснення операції (INSERT у нашому прикладі). Тобто усередині тригера ми можемо

звернутися до всіх полів ще не вставленого запису, використовуючи для цього звертання:

New.ID, New.NAME і New.PRICE_l.

Ми можемо довідатися значення кожного поля запису, що вставляється, порівняти

його чи змінити. Це власне і робиться в цьому фрагменті коду:

IF (NEW.ID IS NULL) THENNEW.ID = GEN_ID(GEN_TABLE_EXAMPLE_ID,1);

Спочатку в операторі IF...THEN перевіряємо ідентифікатор ID на наявність якого-

небудь значення, адже він може бути згенерирований на клієнті. Якщо значенням NEW.ID

є NULL, то викликаємо функцію GEN_ID, що збільшує значення генератора

GEN_TABLE_EXAMPLE_ID на одиницю і потім повертає отримане число, що

привласнюється полю NEW.ID. Таким чином, ми "на льоту" змінили значення в записі,

що вставляється!

Крім контекстної змінної NEW, існує її дзеркальний аналог – змінна OLD. На

відміну від NEW, OLD містить старі значення запису, що вилучаються чи змінюються.

Наприклад, ми можемо використовувати змінну OLD для одержання значень записів, що

вилучаються з таблиці:

CREATE TRIGGER Table_example_ad0 FOR Table_example ACTIVE AFTER DELETE POSITION 0AS

BEGIN IF (OLD.id>1000) THENBEGIN /*..виконуємо певні дії..*/

76

OLD.ID=10; ENDEND

Тут ми створюємо тригер, що спрацьовує ПІСЛЯ ВИЛУЧЕННЯ (AFTER

DELETE). Як бачите, ми можемо одержати доступ до уже вилучених даних. Звичайно,

присвоєння OLD.ID=10; немає жодного змісту – присвоєне значення пропаде на виході з

тригера. Однак цей приклад показує, що ми можемо перехопити значення, що

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

Використання контекстних змінних часте викликає безліч питань. Справа в тім, що

в різних видах тригерів NEW і OLD використовуються по-різному, а в деяких їх узагалі

неможливо використовувати. Якщо ми розглянемо тригер у нашому прикладі, то він

викликається ДО ВСТАВКИ. Про які значення OLD може йти мова? Адже вставляється

зовсім новий запис! І дійсно, контекстна змінна OLD не може бути використана в

тригерах BEFORE/AFTER INSERT. А змінна NEW не може бути використана в

BEFORE/AFTER DELETE. Обидві ці змінні одночасно можуть бути використані в

тригерах BEFORE/AFTER UPDATE, причому змінювати що-небудь можна, тільки

використовуючи змінну NEW.

Контрольні питання:Що таке збережувана процедура?Який синтаксис збережуваних процедур?Яка команда створює лічильник?Що виконує індекс? Коли доцільно використовувати індекси?Що таке виключення? Яка його структура?Що таке тригер? Які контекстні змінні використовуються в тригерах?

77

Тема 10. Безпека в базах даних1. Користувач InterBase.2. Ролі.3. Організація користувачі у групи за допомогою ролей.4. Права. Роздача прав.5. Анулювання прав.

1. Користувач InterBase.

Використання комп’ютерів різко загострило проблеми збереження та конфіден-

ціальності даних. Інформація, яка зберігається в базі даних часто має дуже велику

вартість. Тому забезпечення безпеки збережуваних даних є невід’ємною час-тиною

сучасних СУБД.

InterBase надає різні засоби для керування безпекою в своїх базах даних. Як і

більшості СУБД, в InterBase захист даних базується на тому, що існує концепція

користувачів, які одержують те або інше право для роботи з кожним об’єктом всередині

бази даних. Реальні користувачі одержують в своє володіння ім’я користувача InterBase і

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

InterBase будемо розуміти реєстраційний запис, який складається з імені користувача та

його пароля.

Адміністратор СУБД InterBase заводить необхідну кількість користувачів і

призначає потрібні їм для роботи права, дозволяючи доступ лише для тієї частини

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

накладаються на об’єкти бази даних, здійснена в InterBase на рівні сервера бази даних, а

не самої бази даних. Наслідком цього є те, що фізично скопіювавши базу даних на інший

сервер, ми можемо скористатись паролем адміністратора цього сервера і одержати повний

доступ до інформації в базі даних. Тобто захист файла бази даних необхідно

забезпечувати на іншому рівні, ніж СУБД.

Користувач InterBase – це реєстраційний запис, доступний в усіх базах даних, які

обслуговуються даним сервером. Серед усіх користувачів найголовніший є SYSDBA –

системний адміністратор сервера InterBase. Це ім’я задане і не може змінюватися. За

замовчуванням цей користувач має всі права над довільними об’єктами бази даних. За

замовчуванням пароль цей пароль – “masterkey”. Для того, щоб створити нового

користувача слід використовувати системну консоль IBConsole. Після підключення

сервера за допомогою запису адміністратора, виберіть команду Server – UserSecurity.

В діалоговому вікні User Information натисніть кнопку New і задайте параметри User

78

Name, Password, Confirm Pas-sword (підтвердження пароля). Ці параметри варто

записати, щоб не забути.

Список користувачів можна переглянути в папці Users, яка міститься в дереві

сервера в IBConsole. Звичайно в системі InterBase заводять скільки користувачів, скільки

звертаються до бази даних.

2. Ролі.

Ролі в InterBase використовуються для організації користувачів з однаковими

правами в групи. Наприклад, якщо в нас є група користувачів, для яких необхід-ний

доступ лише на читання, ми створюємо роль з іменем READER, присвоюємо цій ролі

необхідні права і потім можемо присвоювати цю роль з усіма належними їй правами будь-

якому конкретному користувачу. Якщо давати напряму права конкретним користувачам,

про при зміні прав у групи користувачів доводиться модифікувати права у кожного

користувача.

Ролі – це об’єкти рівня бази даних. Вони видимі лише всередині тієї бази даних, в

якій визначені. Для того, щоб створити роль з іменем READER, необхідно виконати

наступний DDL-запит (його можна виконати в ISQL):

CREATE ROLE READER;

При використанні механізму ролей при з’єднанні з базою даних слід вказувати і

ім’я користувача і його бажану роль. Звичайно роль вказується в параметрах з’єднання з

базою даних.

3. Організація користувачі у групи за допомогою ролей.

Щоб зменшити кількість видаваних дозволів і об’єднати користувачів у групу за

принципом наявності в них однакових прав, використовується механізм ролей. Порядок

дії при використонні ролей наступний:

1. Необхідно створити роль.

2. Видати цій ролі достатні права.

3. Призначити конкретним користувачам цю роль.

4. При під’єднанні до бази даних вказати, окрім імені користувача, ту роль, яку

буде мати під час з’єднання даний користувач.

Наведемо приклад. Для початку створимо роль READER:

CREATE ROLE READER;

Видамо цій ролі права на читання таблиці:

GRANT Select ON Tab TO READER;

Присвоїмо цю роль користувачу TESTUSER:

79

GRANT READER TO testuser;

4. Права. Роздача прав.

Права – це дозвіл будь-якому користувачу, збережуваній процедурі або тригеру

здійснювати деяку операцію над певним об’єктом бази даних. Існує декілька видів

об’єктів, на які можна встановлювати права для користувачів та ролей. Це таблиці і їх

поля, подання і збережувані процедури. Існують такі права, що видаються користувачам

InterBase (Права також можуть видаватися для процедур):

1. Для таблиць та їх полів – права на виконання операцій SELECT, DELETE,

INSERT, UPDATE і REFERENCES (це право дає користувачу можливість створювати

обмеження зовнішнього ключа FOREIGN KEY на дану таблицю).

2. Для зображень (VIEW) та їх полів – права на виконання операцій SELECT,

DELETE, INSERT, UPDATE.

3. Права на виконання збережуваних процедур – EXECUTE. Користувач, що хоче

виконати певну процедуру, повинен мати на це право.

Можна перенести всю логіку роботи з даними в збережувані процедури, роздати їм

необхідні права на таблиці, а всім користувачам InterBase повністю заборонити доступ до

таблиць баз даних, щоб виключити пряму правку даних і дозволити лише

використовувати обчислювальні процедури.

Права на об’єкти бази даних роздаються за допомогою команди GRANT.

Для того, щоб виділити користувачу TESTUSER права на вибірку з таблиці Tab,

використовується команда:

GRANT Select ON Tab TO testuser;

Для того, щоб видати право на читання і запис даних, але не на їх зміну,

використовується команда:

GRANT Select, Insert ON Tab TO testuser;

Для того, щоб видати відразу всі права, використовують ключове слово ALL:

GRANT ALL ON Tab TO testuser;

Для того, щоб виділити права відразу декільком користувачам, вони розділяються

комами.

Якщо ж потрібно видати права відразу всім користувачам InterBase, то можна

скористатися ключовим словом PUBLIC:

GRANT Select, Update ON Tab TO PUBLIC;

80

Окрім видачі прав на всю таблицю інколи виникає потреба обмежити права

користувача декількома певними полями в таблиці. Наприклад, користувач BOSS має

право змінювати поле BIGMONEY, а решта користувачів можуть його лише переглядати:

GRANT Select ON Tab(BIGMONEY) TO PUBLIC;

GRANT ALL ON Tab(BIGMONEY) TO BOSS;

Аналогічний синтаксис команд використовується і для збережуваних процедур:

GRANT EXECUTE ON PROCEDURE SP_Add TO testuser;

5. Анулювання прав.

Для цього існує команда REVOKE. Вона являє собою копію GRANT, лише із

зворотною дією. Приклад:

REVOKE Select ON Tab FROM testuser;

При цьому неможливо якось обмежити або розширити права системного

адміністратора SYSDBA.

За замовчуванням права на довільний об’єкт в InterBase має лише його власник, а

також системний адміністратор SYSDBA. Відповідно, роздавати права за замов-чуванням

може лише власник об’єкта. Інший користувач, що не є власником об’єкта, не може

видати іншому користувачу права на цей об’єкт, якщо тільки власник не передав іншому

користувачу відповідні права із спеціальною опцієї WITH GRANT OPTION. Вказання

цієї опції в кінці звичайного речення GRANT означає, що користувач не лише одержує ці

права, але може передавати їх другому користувачу. Наприклад:

GRANT Select ON Tab TO testuser WITH GRANT OPTION;

Тепер користувач TESTUSER може не лише вибирати записи з таблиці Tab, але і

передавати право Select (і лише його) іншим користувачам.

Контрольні питання:1. Вкажіть серію команд створення нового користувача бази даних InterBase?2. Що таке роль? Як її створити?3. Опишіть команду роздачі прав.4. Яка команда надає відразу всі права?5. Яка команда видає права відразу всім користувачам?6. Вкажіть команду анулювання прав.

81

МОДУЛЬ 2Тема 11. Оператор EXISTS. Сумування і групування

даних.1.1. Оператор EXISTS.

1.2. Використання EXISTS з співвіднесеними підзапитами.1.3. Комбінація оператора EXISTS і з’єднання.1.4. Використання NOT EXISTS.1.5. Використання складних підзаписів з оператором EXISTS.

1.1. Агрегатні функції.1.2. Правила опрацювання значення NULL агрегатними функціями.1.3. Опрацювання унікальних записів агрегатними функціями.1.4. Агрегатні функції і значення NULL.

2. Сумування і групування даних.2.1. Секція HAVING – умова відбору груп.2.2. Обмеження на умову відбору груп.2.3. Значення NULL і умова відбору груп.2.4. Секція HAVING без секції GROUP BY.

1.1. Оператор EXISTS.

EXISTS – це логічний оператор. Це означає, що він може використовуватися в

предикаті або в комбінації з іншими виразами, які використовують оператори AND, OR і

NOT. Він бере підзапит як аргумент і оцінює його як вірний, якщо той здійснює довільний

вивід, або як невірний, якщо той не робить цього. Цим він відрізняється від інших

операторів предикату, в яких аргумент не може бути невідомим. Наприклад, ми хочемо

вибрати деякі дані з таблиці Замовників лише тоді, коли один або більше замовників в цій

таблиці знаходятся в San Jose:

SELECT cnum, cname, city FROM Customers WHERE EXISTS (SELECT * FROM Customers WHERE city = “San Jose”);

=============== SQL Execution Log ============ | SELECT snum, sname, city | | FROM Customers | | WHERE EXISTS | | (SELECT * | | FROM Customers | | WHERE city = “San Jose”); | | ============================================= | | cnum cname city | | ----- -------- ---- | | 2001 Hoffman London | | 2002 Giovanni Rome | | 2003 Liu San Jose | | 2004 Grass Berlin | | 2006 Clemens London |

82

| 2008 Cisneros San Jose | | 2007 Pereira Rome |

===========================================

Внутрішній запит вибирає всі дані для всіх замовників в San Jose. Оператор

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

що робить предикат істинним. Підзапит було виконано лише один раз для всього

зовнішнього запиту, і він, відповідно, має одне значення у всіх випадках. Тому EXISTS,

коли він використовується таким чином, робить предикат істинним або неістинним для

всіх рядків відразу.

1.2. Використання EXISTS з співвіднесеними підзапитами.

В співвіднесеному підзапиті, речення EXISTS оцінюється окремо для кожного

рядка таблиці, який вказано в зовнішньому запиті, так само, як і інші оператори предикату

при використання співвіднесеного підзапиту. Це дає можливість використовувати EXISTS

як предикат, що генерує різні значення для кожного рядка таблици, яка вказана в

основному запиті. Це означає, що інформація з внутрішнього запиту збережеться при

виведення безпосередньо, коли ви вико-ристовуєте EXISTS таким чином. Наприклад,

можна вивести продавців, які мають декількох замовників:

SELECT DISTINCT snum FROM Customers oouter WHERE EXISTS ( SELECT * FROM Customers iinner WHERE iinner.snum = oouter.snum AND iinner.cnum <> oouter.cnum ); =============== SQL Execution Log ============ | SELECT DISTINCT snum | | FROM Customers oouter | | WHERE EXISTS | | (SELECT * | | FROM Customers iinner | | WHERE iinner.snum = oouter.snum | | AND iinner.cnum <> oouter.cnum); | | ============================================= | | snum | | ----- | | 1001 | | 1002 | ============================================= Для кожного рядка-кандидата зовнішнього запиту (замовника, що перевіряється в

даний момент), внутрішній запит знаходить рядки, які співпадають із значен-ням поля

snum (яке мав продавець), але не із значенням поля cnum (яке відповідає іншим

замовникам). Якщо такі рядки знайдено внутрішнім запитом, це означає, що є два різних

замовника, які обслуговуються поточним продавцем (тобто продавцем замовника в

83

поточному рядку-кандидаті із зовнішнього запи-ту). Предикат EXISTS відповідно

істинний для поточного рядка, і тому номер продавця з поля (snum) таблиці, яка вказана в

зовнішньому запиті, буде виве-дено. Оскільки вказано параметр DISTINCT, кожен з цих

продавців буде вибраний лише один раз для кожного замовника, до якого він

призначений.

1.3. Комбінація оператора EXISTS і з’єднання.

Бувають випадки, коли нам необхідно вивести більше інформації про самих

продавців, а не тільки їх номери. Це можна зробити з’єднавши таблицю Замовників з

таблицею Продавців:

SELECT DISTINCT first.snum, sname, first.city FROM Salespeople first, Customers second WHERE EXISTS ( SELECT * FROM Customers third WHERE second.snum = third.snum AND second.cnum <> third.cnum ) AND first.snum = second.snum;

=============== SQL Execution Log ============ | SELECT DISTINCT first.snum, sname, first.city | | FROM Salespeople first, Customers second | | WHERE EXISTS | | (SELECT * | | FROM Customers third | | WHERE second.snum = third.snum | | AND second.cnum <> third.cnum) | | AND first.snum = second.snum; | | ============================================= | | snum sname city | | ----- -------- ---- | | 1001 Peel London | | 1002 Serres San Jose | =============================================

Внутрішній запит – це з’єднання таблиці Продавців з таблицею Замовників. Нове речення

основного предикату (AND first.snum=second.snum) оцінюється на тому ж рівні, що і

речення EXISTS. Це – функціональний предикат самого з’єднання, що порівнює дві

таблиці з зовнішнього запиту в термінах поля snum, яке є для них загальним. Логічні

оператори AND, вказують на те, що результати підзапиту мають смисл лише в тех

випадках, коли друга частина запиту істинна, а з’єднання – виконується. Таким чином,

комбінація з’єднання і підзапиту може стати потужним засобом обробки даних.

84

1.4. Використання NOT EXISTS.

В попередньому прикладі EXISTS використовувався сумістно з логічними

операторами. Логічний оператор, який найчастіше використовується з EXISTS – це

оператор NOT. Один із способів, за допомогою яких можна знайти всіх продавців, що

мають лише одного замовника, полягає в інвертуванні попереднього прикладу.

SELECT DISTINCT snum FROM Customers oouter WHERE NOT EXISTS(SELECT * FROM Customers iinner WHERE iinner.snum = oouter.snum AND iinner.cnum < > oouter.cnum ); =============== SQL Execution Log ============ | SELECT DISTINCT snum | | FROM Salespeople oouter | | WHERE NOT EXISTS | | (SELECT * | | FROM Customers iinner | | WHERE iinner.snum = oouter.snum | | AND iinner.cnum < > oouter.cnum); | | ============================================= | | cnum | | ----- | | 1003 | | 1004 | | 1007 | =============================================

1.5. Використання складних підзаписів з оператором EXISTS.

Програми підзапитів можуть бути багатократно вкладуваними. Ви можете

вкладувати їх два або більше в одиночний запит, і навіть один всередину другого.

Розглянемо запит, який виводить рядки всіх продавців, що мають замовників з більше ніж

одним поточним порядком. Виведення цієї інформації пов’язує всі три наші типові

таблиці:

SELECT * FROM Salespeople first WHERE EXISTS ( SELECT * FROM Customers second WHERE first.snum = second.snum AND 1 < ( SELECT COUNT (*) FROM Orders WHERE Orders.cnum = second.cnum )); =============== SQL Execution Log ============ | FROM Salespeople first | | WHERE EXISTS | | (SELECT * | | FROM Customers second | | WHERE first.snum = second.snum | | AND 1 < | | (SELECT CONT (*) | | FROM Orders |

85

| WHERE Orders.cnum = second.cnum)); | | ============================================= | | cnum cname city comm | | ----- -------- ---- -------- | | 1001 Peel London 0.17 | | 1002 Serres San Jose 0.13 | | 1007 Rifkin Barselona 0.15 | =============================================

Цей запит виконується приблизно так. Беремо кожен рядок таблиці Продавців як рядок-

кандидат (зовнішній запит) і виконуємо підзапити. Для кожного рядка-кандидата із

зовнішнього запиту, беремо у відповідність кожен рядок із таблиці Замовників (середній

запит). Якщо поточний рядок замовників не співпадає з поточним рядком продавця

(тобто, якщо first.snum <> second.snum ), предикат середнього запиту неістинний. Всякий

раз, коли ми знаходимо замовника в средньому запиті, який співпадає з продавцем у

зовнішньому запиті, ми повинні розглядати сам внутрішній запит, щоб визначити, чи буде

наш середний предикат запиту істинний. Внутрішній запит зчитує число порядків

поточного замов-ника (із середнього запиту). Якщо це число більше, ніж 1, предикат

середнього запиту істинний, і рядки вибираються. Це робить EXISTS-предикат

зовнішнього запиту істинним для поточного рядка продавця, і означає, що, по меншій

мірі, один із поточних замовників продавця має більш ніж один порядок.

2.1. Агрегатні (статистичні) функції

Агрегатні функції призначені для обчислення підсумкових значень на основі всіх записів набору

даних або на основі певної групи рядків.

COUNT (вираз) - ця функція обчислює кількість входжень відповідного виразу у всі рядки чи у групу рядків результуючого набору даних

SUM (вираз) - ця функція обчислює суму значень виразу по всіх рядках чи по групі рядків результуючої таблиці

AVG (вираз) - ця функція обчислює середнє арифметичне виразуMAX (вираз) - ця функція обчислює максимальне значення виразуMIN (вираз) - ця функція обчислює мінімальне значення виразу

Приклад 1. Обчислити кількість відпуску товаруSELECT COUNT (*)

FROM VidpuskColumn 1

2Приклад 2. Вивести загальну вартість відпущеного товаруSELECT SUM (V.Kilkist * T.Cina)FROM Vidpusk V, Tovary TWHERE V.Tovar = T.Nazva

Column 1400.80

Приклад 3. Вивести середнє арифметичне вартості всіх відпущених товарівSELECT AVG (V.Kilkist * T.Cina)FROM Vidpusk V, Tovary TWHERE V.Tovar = T.Nazva

86

Column 1200.40

В усіх цих прикладах результуюча таблиця містить лише один стовпчик і лише

один рядок.

Якщо із групи однакових рядків потрібно враховувати лише один, то перед

виразом у дужках записують слово DISTINCT.

Приклад 4. Вивести кількість різних видів окремих одиниць вимірювань, які є на складі.

SELECT COUNT (DISTINCT Odynycia)FROM Tovary

Column 13

В стандарті SQL говориться, що значення NULL агрегатними функціями ігнорується. Значення NULL відповідає невідомому або відсутньому значенню.

Нехай маємо таблицю Pracivnyky

PracivnykyNo Prizv Plan Prodano101102103104105106

ДідБаба

ВнучкаЖучкаКішкаМишка

350300

NULL300275350

36739275186286361

Приклад 5.SELECT COUNT (*), COUNT (Plan), COUNT (Prodano)FROM Pracivnyky

COUNT (*) COUNT (Plan) COUNT (Prodano)6 5 6

COUNT (*) - ця функція обчислює загальну кількість рядків у таблиціCOUNT (стовпчик) - ця функція обчислює кількість рядків, які у відповідному

стовпчику не мають значення NULL

Ігнорування значень NULL не впливає на результати функцій MIN та MAX, але воно може привести до проблем при використанні функцій SUM та AVG.

Приклад 6.SELECT SUM (Prodano), SUM (Plan), SUM (Prodano) – SUM (Plan), SUM (Prodano – Plan)FROM Pracivnyky

SUM (Prodano) SUM (Plan) SUM (Prodano) – SUM (Plan) SUM (Prodano – Plan)1667 1575 92 17

Можна було сподіватися, що вираз SUM (Prodano) – SUM (Plan) буде дорівнювати

виразу SUM (Prodano – Plan), але це не так. Причиною різних результатів є рядок із

87

значенням NULL у стовпчику Plan. Вираз SUM (Prodano) обчислює суму продаж для всіх

шести працівників, а вираз SUM (Plan) обчислює суму лише п’яти значень і не враховує

значення NULL. Вираз SUM (Prodano) – SUM (Plan) обчислює різницю між цими двома

сумами. З іншої сторони вираз SUM (Prodano – Plan) в якості аргументів приймає лише

п’ять значень, які не дорівнюють NULL, тому що в рядку, де значення запланованого

об’єму продаж дорівнює NULL, будь-який вираз, який містить поле Plan отримує

значення NULL. Отже, це значення функція SUM проігнорує, тобто результати цього

виразу не враховують фактичні продажі працівника, для якого ще не встановлено плану,

хоча вони ввійшли в результат попереднього виразу. Постає питання: яка ж відповідь є

„вірною”? Обидві. Перший вираз обчислює те, що і означає, тобто SUM (Prodano) – SUM

(Plan) обчислює різницю між сумарним об’ємом фактичних продаж і сумарним об’ємом

планових продаж. Другий вираз означає суму різниць між фактичними та плановими

продажами кожного працівника.

2.2. Правила опрацювання значення NULL агрегатними функціями:

1. Якщо будь-які значення, які містяться у стовпчику дорівнюють NULL, то при

обчисленні агрегатних функцій вони ігноруються.

2. Якщо всі значення у стовпчику дорівнюють NULL, то функції SUM, AVG, MIN,

MAX повернуть значення NULL, а функція COUNT поверне значення нуль.

3. Якщо стовпчик не містить значень (порожній стовпчик чи порожня таблиця), то

функції SUM, AVG, MIN, MAX повернуть значення NULL, а функція COUNT поверне

значення нуль.

4. Функція COUNT (*) підраховує кількість рядків і не залежить від наявності чи

відсутності у будь-якому стовпчику значення NULL. Якщо рядків у таблиці немає, то

функція COUNT поверне значення нуль.

Промислові СУБД можуть по-іншому опрацьовувати значення NULL агрегатними

функціями, тобто інакше порівняно із стандартом SQL.

Приклад 7.SELECT COUNT ()

FROM Vidpusk V, Tovary TWHERE V. Tovar = T. Nazva

SELECT SUM (V. Kilkist * T. Cina) FROM Vidpusk V, Tovary T WHERE V. Tovar = T. Nazva

SELECT AVG (V. Kilkist * T. Cina)FROM Vidpusk V, Tovary TWHERE V. Tovar = T. Nazva

SELECT MAX (V. Kilkist * T. Cina)FROM Vidpusk V, Tovary TWHERE V. Tovar = T. Nazva

88

SELECT MIN (V. Kilkist * T. Cina)FROM Vidpusk V, Tovary TWHERE V. Tovar = T. Nazva

- в усіх випадках результуюча таблиця містить один стовпчик і один рядок. Агрегатні функції також називають – статистичними.

2.3.Опрацювання унікальних записів агрегатними функціями

Для агрегатних функцій: якщо із групи однакових записів потрібно враховувати

лише одну, то перед виразом в дужках записують ключове слово DISTINCT :

Приклад 8 :Tovary

Nazva Odynyci Cinaцукор кг. 2.60

молоко л. 1.00хліб бух. 1.30

дріжджі кг. 3.60ковбаса кг. 17.50

Приклад 9 : Вибрати і вивести кількість різних видів одиниць вимірювання наявних на складі товарів.

SELECT COUNT (DISTINCT Odynyci) FROM Tovary

16)

COLUMN 1

3

2.4. Агрегатні функції і значення NULL

NULL – відповідає невідомому або не визначеному значенню. В стандарті SQL

сказано : “Значення NULL агрегатними функціями ігноруються.”

Нехай:

Персонал (Personal)Tab. №

Prizv Plan Prodano

101 Дід 350 367102 Баба 300 392103 Внучка 75104 Жучка 300 186105 Кішка 275 286106 Мишка 350 361

89

DISTINCTагрегатнафункція

вираз

Приклад 10 : SELECT COUNT (*),COUNT (Plan),COUNT (Prodano)

FROM Personal COUNT (*) COUNT (Plan) COUNT (Prodano)

6 5 6

COUNT (*) – обчислює загальну кількість рядків у таблиці.COUNT( ) – обчислює кількість значень у відповідному стовпчику, які не

дорівнюють NULL.

Ігнорування значень NULL – не впливає на результати функцій MIN( ) та MAX( ).

Але це ігнорування може привести до проблем при використанні функцій SUM( ) і AVG(),

наприклад:

Приклад 11 : SELECT SUМ (Prodano), SUМ (Plan),(SUМ (Prodano) – SUM(Plan)),

SUМ (Prodano - Plan) FROM Personal

SUМ (Prodano)

SUМ (Plan)

(SUМ (Prodano) – SUM(Plan))

SUM (Prodano Plan)

1667 1575 92 17

Можна було сподіватися, що вирази (SUМ (Prodano) – SUM(Plan)) та SUМ (Prodano

- Plan) дадуть ті самі результати, але це не так. Причиною різних результатів є рядок із

значенням NULL в стовпчику Plan.

Вираз SUM (Prodano) – обчислює суму продаж для всіх шести працівників, а

вираз SUМ (Plan) – обчислює суму тільки п’ять значень і не враховує значення NULL.

Наступний вираз обчислює різницю між цими двома сумами: SUМ (Prodano) –

SUM (Plan)

З іншої сторони, вираз SUМ (Prodano - Plan) приймає в якості аргументів лише

п’ять значень, які не дорівнюють NULL. В рядку, де значення запланованого об’єму

продаж дорівнює NULL (для Внучки), будь-який вираз, що містить поле Plan, отримає

значення NULL (а отже, Prodano – Plan NULL), і це значення функції SUМ( )

проігнорує. Отже, результати цього вирази не враховують фактичні продажі працівника,

для якого ще не встановлено плану, хоча вони ввійшли в результат попереднього виразу.

Яка ж відповідь є “вірною”? Обидві! Перший вираз обчислює саме те, що і означає, тобто

“сума по Prodano мінус сума по Plan” і другий вираз також обчислює саме те, що означає,

тобто “сума по різницях між Prodano і Plan”, але при наявності значень NULL результати

виразів – відрізняються.

Ось точні правила опрацювання значень, NULL статистичними функціями:

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

результату функції вони ігнорують ( не враховуються);

90

поле

2. якщо всі значення в стовпчику дорівнює NULL, то функції SUМ ( ), AVG ( ), MIN ( ),

MAX ( ) повертають значення NULL; функція COUNT ( ) повертає ;

3. якщо стовпчик не містить значень (порожній стовпчик порожня таблиця), то

COUNT ( ) 4. функція COUNT (*) підраховує кількість рядків і не залежить від наявності

чи відсутності в якомусь стовпчику значень NULL; якщо рядків в таблиці – немає, то

COUNT (*) .

Комерційні (промислові) СУБД можуть по-іншому опрацьовувати NULL –

значення статистичними функціями, ніж це описано в стандарті SQL - треба дивитися

опис конкретної СУБД.

3.1. Групування записів

Інколи буває потрібно отримати агрегатні значення не для всього результуючого

набору даних, а оремо для кожної із груп, які входять у цей результуючий набір.

Кожна група рядків характеризується однаковим значенням деякого стовпчика.

Приклад 1 2 . Вивести загальну кількість відпущеного товару для кожного із видів

товару.

Для розв’язку необхідно згрупувати відпуски товарів по назві товарів і обчислити

суму кількостей відпуску для кожної групи. Для групування в операторі SELECT після

секції WHERE додається секція GROUP BY. Якщо є секція ORDER BY, то секція GROUP

BY записується після секції WHERE, але перед секцією ORDER BY.

Якщо є секція GROUP BY, то обов’язково потрібно, щоб один із стовпчиків

результуючого набору даних представлявся агрегатною функцією, тобто щоб в секцію

SELECT входив вираз, який містить агрегатну функцію. Групування відбувається по

звичайним (неагрегатним) полям, тому для групування потрібно, щоб у секцію SELECT

входило по крайній мірі одне неагрегатне поле. Групування буде відбуватися за

однаковими значеннями у цьому неагрегатному стовпчику. Всі неагрегатні поля секції

SELECT повинні бути вказані в секції GROUP BY.

91

SUM ( )AVG ( )MIN ( )MAX ( )

NULL

SELECT V.Tovar, SUM (V.Kilkist) AS VidpFROM Vidpusk VGROUP BY V.Tovar

Vidpusk РезультатTovar ... Kilkist → Tovar Vidpшило ... 1 шило 4шило ... 3 мило 2мило ... 2 цукор 3цукор ... 1цукор ... 2

Приклад 2. Вивести загальну вартість кожного відпущеного товару.SELECT V.Tovar, SUM (V.Kilkist * T.Cina) AS Vart

FROM Vidpusk V, Tovary TGROUP BY V.Tovar

Правила виконання SQL-запиту на вибірку із врахуванням секції GROUP

BY:

1. Сформувати декартовий добуток таблиць, вказаних в секції FROM. Якщо в

секції FROM вказана одна таблиця, то декартовим добутком буде вона сама.

2. Якщо є секція WHERE, то умову вказану в цій секції слід застосувати до

кожного рядка таблиці, утвореної в результаті декартового добутку, і залишити лише ті

рядки, для яких результат умови має значення TRUE. Рядки, для яких умова має значення

NULL або FALSE відкидаються.

92

3. Якщо є секція GROUP BY, то потрібно розділити рядки, які залишились в

результуючій таблиці на групи так, щоб рядки в кожній групі мали однакові значення у

всіх стовпчиках групування одночасно.

Стовпчик групування – це той стовпчик, який визначає групу рядків. В межах

групи всі рядки у стовпчику групування мають одне і те саме значення.

4. Для кожного рядка, що залишився, або для кожної групи рядків потрібно

обчислити значення кожного елемента зі списку полів секції SELECT і утворити один

рядок таблиці результатів запиту, при цьому при будь-якому звертанні до стовпчика

береться значення стовпчика для біжучого рядка або групи рядків. Якщо є секція GROUP

BY, то в якості аргументу агрегатної функції використовується значення стовпчика із всіх

рядків, які входять у групу. Якщо ж секції GROUP BY немає, то використовується

значення стовпчика із усіх рядків таблиці результату запиту.

5. Якщо в секції SELECT є слово DISTINCT, то потрібно вилучити із таблиці

результатів запиту всі рядки дублікати.

6. Якщо є секція ORDER BY, то потрібно відсортувати результати запиту.

SQL дозволяє групувати результати запиту на основі кількох стовпчиків.

Наприклад, потрібно згрупувати замовлення по працівниках та клієнтах.

Задано таблицю Zamovlennia

ZamovlenniaPracivnyk Klient Kilkist ...

… … … …

Приклад 1 3 . Обчислити загальну кількість замовленого товару для кожного клієнта і для кожного працівника.

SELECT Pracivnyk, Klient, SUM (Kilkist)FROM ZamovlenniaGROUP BY Pracivnyk, Klient

Є дуже велике обмеження в стандарті SQL: навіть при групуванні по двом стовпчикам стандарт SQLзабезпечує лише один рівень групування.

Pracivnyk Klient SUM (Kilkist)

93

Даний запит генерує лише підсумковий рядок для кожної пари працівник-клієнт. В

стандарті SQL не можна створити групи і підгрупи з двома або більшою кількістю рівнів

підсумкових результатів. Найкраще, що можна зробити в стандарті SQL – це відсортувати

дані так, щоб рядки в таблиці результатів запиту розміщувалися в потрібному порядку.

Для більшості СУБД при використанні секції GROUP BY сортування виконується

автоматично, але цей автоматичний порядок сортування можна змінити за допомогою

секції ORDER BY.

Приклад 14 . Підрахувати загальну кількість замовленого товару по кожному

клієнту для кожного працівника і відсортувати результати запиту, в першу чергу, по

клієнтам, а вже потім по працівникам.

а) SELECT Pracivnyk, Klient, SUM (Kilkist)FROM ZamovlenniaGROUP BY Pracivnyk, KlientORDER BY Klient, Pracivnyk

б) ) SELECT Klient, Pracivnyk, SUM (Kilkist)FROM ZamovlenniaGROUP BY Klient, Pracivnyk

Запит б) в стандарті SQL впорядковує результати спочатку по клієнтам, а потім по

працівникам. Саме тому в стандарті SQL секція ORDER BY в цьому випадку

необов’язкова.

Якщо ж СУБД реалізує впорядкування при групуванні інакше ніж це описано в

стандарті SQL, то секція ORDER BY повинна бути обов’язково.

За допомогою одного SQL-запиту в стандарті SQL неможливо отримати як

детальні, так і проміжні підсумкові результати. Для того, щоб отримати детальні

результати з підсумками по групам потрібно написати програму, яка обчислює ці

проміжні підсумкові результати.

Стовпчики групування повинні бути фізичними стовпчиками таблиць вказаних у

секції FROM. Не можна групувати рядки на основі обчислювальних стовпчиків. Крім

того, є обмеження на список полів SELECT: всі елементи цього списку повинні мати одне

й те саме значення для кожної групи рядків. А це означає, що елементом списку полів

SELECT може бути: 1) константа; 2) агрегатна функція; 3) стовпчик групування; 4) вираз,

який складається із вищевказаних елементів.

На практиці у список полів SELECT запиту з групуванням завжди входить

стовпчик групування і агрегатна функція.

Якщо секція SELECT не містить агрегатної функції, то цей запит можна зробити

простішим за допомогою директиви DISTINCT без використання секції GROUP BY.

Якщо ж в секцію SELECT не включати стовпчик групування, то не можна буде

визначити, до якої саме групи належить кожний рядок таблиці результатів запиту.

94

Коли в стовпчику групування містяться значення NULL, то виникають додаткові

ускладнення: якщо значення стовпчика невідоме, то до якої саме групи його слід віднести.

В секції WHERE при порівнянні двох значень NULL результат буде NULL, а не TRUE,

тобто два значення NULL у секції WHERE не вважаються однаковими. Якщо таке

правило застосувати у секції GROUP BY, то це приведе до того, що кожний рядок із

значенням NULL в стовпчику групування буде занесений в окрему групу, яка складається

із одного єдиного цього рядка. Тому в стандарті SQL визначається, що два значення

NULL в секції GROUP BY співпадають.

(NULL = NULL) → TRUE

Тобто якщо два рядки мають значення NULL в однакових стовпчиках групування

та співпадаючі значення у всіх інших стовпчиках групування, то вони заносяться в одну й

ту ж групу.

Приклад 1 5 . People

Name Hair EyesОксана

ВіраНадяІгорГаляІра

ЄвгеніяМаріяЮра

МаринаСвітлана

Люба

коричневеNULLNULLNULLNULL

коричневекоричневекоричневекоричневекоричневе

білебіле

синісинісині

NULLNULLNULLNULLNULLNULLкарісинісині

SELECT Hair, Eyes, COUNT (*)FROM PeopleGROUP BY Hair, Eyes

Hair Eyes COUNT (*)коричневе

NULLNULL

коричневекоричневе

біле

синісині

NULLNULLкарісині

122412

3.2. Секція HAVING – умова відбору груп

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

для всіх груп, а лише для тих, які задовольняють певній умові, то після секції GROUP BY

перед секцією ORDER BY вказується секція HAVING.

95

Загальний вигляд оператора SELECT описується так:

Умова відбору груп секції HAVING має вигляд:

Подібно до того, як секція WHERE використовується для відбору окремих рядків,

так і секція HAVING використовується для відбору груп. Формат секції HAVING такий як

і в секції WHERE, за одним дуже важливим винятком: в секції WHERE не можна

вказувати агрегатних функцій.

Порядок виконання секцій в операторі SELECT такий:

1. FROM.

2. WHERE.

3. GROUP BY.

4. HAVING.

5. SELECT.

6. ORDER BY.

Приклад 1 6 . Яка середня вартість замовлення для кожного працівника із тих

працівників, у яких загальна вартість замовлень перевищує 3000.

SELECT Pracivnyk, AVG (Vartist)FROM ZamovlenniaGROUP BY PracivnykHAVING SUM (Vartist) > 3000

Можна вказувати різні агрегатні функції у секції SELECT і у секції HAVING.

Приклад 17 . Вивести загальну кількість купленого товару (в одиницях

вимірювання) для всіх тих покупців, у яких мінімальна кількість купленого товару не

менше ніж 100.

SELECT Pokupets, SUM (Kilkist)FROM VidpuskGROUP BY PokupetsHAVING MIN (Kilkist) > = 100

96

Правила виконання SQL-запиту на вибірку із врахуванням секції HAVING:1. FROM.2. WHERE.3. GROUP BY.4. HAVING.5. SELECT.6. DOSTINCT.7. ORDER BY.

Приклад 18. Вивести ціну, кількість наявних на складі товарів та загальну кількість

замовлених одиниць для кожної назви товару, якщо для нього загальна кількість

замовлених одиниць більша, ніж 75% від кількості наявних на складі товарів.

SELECT T.Nazva, T.Cina, T.Nay_Kilkist, SUM (Z.Kilkist)FROM Tovary T, Zamovlennia ZWHERE Z.Kod_Tovaru = T.Kod_Tovaru AND

Z.Kod_Vyrobn = T.Kod_VyrobnGROUP BY T.Kod_Vyrobn, T.Kod_Tovaru,

T.Nazva, T.Cina, T.Nay_KilkistHAVING SUM (Z.Kilkist) > 0.75 * T.Nay_KilkistORDER BY T.Nay_Kilkist DESC

3.3. Обмеження на умову відбору груп

Секція HAVING використовується для того, щоб відбирати групи рядків, тому

умова відбору секції HAVING застосовується не до окремих рядків, а до групи в цілому.

Це означає, що в умову відбору секції HAVING може входити 1) константа; 2) агрегатна

функція, яка повертає одне значення для всіх рядків, що входять у групу; 3) стовпчик

групування, який за означенням має одне й те саме значення в усіх рядках групи; 4) вираз,

який складається із всіх вищевказаних елементів.

На практиці умова відбору секції HAVING завжди містить як мінімум одну

агрегатну функцію. Якщо це не так, то таку умову можна і навіть буде краще перемістити

у секцію WHERE.

3.4. Значення NULL і умова відбору груп

Як і у секції WHERE умова відбору в секції HAVING може дати один з наступних

результатів: якщо умова набуває значення TRUE, то така група рядків залишається і для

неї генерується один єдиний рядок таблиці результатів запиту; якщо ж умова отримує

значення FALSE або NULL, то така група рядків ігнорується і для неї жодний рядок

таблиці результатів не створюється.

Правила опрацювання значень NULL в умовах відбору секції HAVING такі самі,

що і для секції WHERE.

97

3.5. Секція HAVING без секції GROUP BY

На практиці секція HAVING завжди використовується разом із секцією GROUP

BY, але синтаксис оператора SELECT цього не вимагає. Якщо секція HAVING

використовується без секції GROUP BY, то СУБД вважає всі результати запиту однією

групою, тобто агрегатні функції вказані в секції HAVING будуть застосовуватися до

однієї єдиної групи, яка складається з усіх рядків.

Контрольні питання:1. Що виконує оператор EXISTS?2. Коли доцільно використовувати оператор EXISTS?3. Що таке агрегатні функції?4. Опишіть основні агрегатні функції.5. Чим відрізняється результат виконання функцій COUNT (вираз) та COUNT (*)?6. Вкажіть правила опрацювання значення NULL агрегатними функціями.7. Коли використовується ключове слово DISTINCT?8. Що таке групування? Яка команда здійснює групування?9. В якому реченні задається умова відбору груп?

98

Тема 12. З’єднання з базою даних. Використання збережуваних процедур в Delphi

1. З’єднання з базою даних:2. Використання збережуваних процедур в Delphi.

1. З’єднання з сервером

Все звертання з програми до таблиць однієї бази даних на сервері здійснюється

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

відповідні значення властивості DatabaseName.

Все керування одиночним з’єднанням з якою-небудь базою даних здійснюється

компонентом TDatabase. В загальному випадку використання цього компонента в

програмах баз даних необов’язково. Однак при роботі с серверами SQL він необхідний,

так як:

дозволяє керувати з’єднанням з базою даних;

дозволяє керувати транзакціями простіше, ніж за допомогою операторів SQL;

забезпечує реєстрацію користувача на сервері.

При звичайному використанні локальних баз даних можна прекрасно обійтися і без

цього компонента.

В процесі роботи компонент активно використовує параметри псевдонімів і

драйверів BDE. Звичайно компонент TDatabase розміщується в модулі даних програми.

Для визначення бази даних (сервера), з якою програма встановлює з’єднання за

допомогою компонента TDatabase, частіш використовується властивість AliasName.

Властивості DatabaseName і DriverName являють альтернативний спосіб з’єднання. Якщо

з’єднання задане властивістю AliasName, то властивість DatabaseName можна

використовувати для створення тимчасового псевдоніма, який буде доступний лише для

компонентів доступу до даних всередині програми. При клацанні на кнопці списка

доступних псевдонімів властивості DatabaseName в Інспекторі об’єктів для довільного

компонента доступу до даних, в списку буде доступний і тимчасовий псевдонім

компонента TDatabase.

Наприклад, при переключення програми на іншу базу даних можна змінити лише

значення псевдоніма в компоненті TDatabase. Якщо всі компоненти наборів даних

підключені до тимчасового псевдоніму компонента TDatabase, то вони автоматично

переключаться на нову БД.

99

Додаткові можливості керування наборами даних при переключення з’єднання

надають властивості Connected і KeepConnection. Вони дозволяють одночасно з

з’єднанням закрити всі активні набори даних.

Якщо набори даних програми підключені до бази даних через компонент

TDatabase, то перед їх відкриттям необхідно встановити з’єднання з БД. З’єднання з БД

встановлюється за допомогою методу Оpen. Якщо спробувати активізувати набір даних

без цього, то з’єднання буде встановлено автоматично.

Аналогічна картина виникає при закритті наборів даних і відключенні від БД.

Додатковий засіб керуванняу в цьому випадку є властивість KeepConnection. Якщо вона

дорівнює True, то при закритті останнього відкритого набору даних з’єднання

залишається відкритим. В іншому випадку з’єднання автоматично закривається.

Це дозволяє керувати з’єднанням в різних вихідних ситуаціях. При великій

завантажуваності сервера буває необхідним переривати з’єднання кожний раз. Якщо

потрібно розвантажити мережевий трафік, то з’єднання краще залишити включеним.

При підключенні до бази даних досить часто потрібно задати значення для

параметрів драйвера BDE. Для цього використовується властивість Params, що являє

собою звичайний список. В ньому необхідно задавати назви змінюваних параметрів і їх

нові значення:

USERNAMЕ=SYSDBA PASSWORD=masterkey

Значення параметров можно задавати як статично, так і динамічно під час

виконання.

Компонент TDatabase може полегшити підключення до баз даних з реєстрацією

користувачів. При реєстрації на сервері досить задати ім’я користувача і пароль у

властивості Рarams і встановити для властивості LoginPrompt значення False. Ця

комбінація працює як під час виконання, так і під час розробки.

2. Використання збережуваних процедур в Delphi.

В Delphi використання збережуваних процедур забезпечується компонентом

TStoredProc. За його допомогою програма може використовувати як виконувані

процедури, так і процедури відбору. Так як компонент TStoredProc є нащадком

TDBDataSet, то результатом його роботи може бути виконання модифікацій на сервері без

повернення результату, одиничний результат або набір даних.

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

Збережувана процедура задається її іменем у властивості StoredProcName. При створенні

100

клієнтської програми в архітектурі клієнт/сервер для з’єднання з сервером бажано

використовувати компонент TDatabase.

Якщо збережувана процедура не має вхідних параметрів, то для її виконання

використовується метод ЕхесPгос. Наприклад, компонент StoredРroc1 можна пов’язати з

деякою збережуваною процедурою:

StoredProcl.DatabaseName := 'IBLOCAL';StoredProc1.StoredProcName := 'GetSomeVaiue';StoredProc1.ExecProc;

Якщо збережувана процедура має вхідні параметри, то для їх визначення

використовується властивість params типу TРarams. Для збережуваної процедури вхідні

параметри задаються так:

StoredProcI.Params[0].AsDateTime := Now;StoredProcI.Params[1].As Integer := SpinEditI.Value;StoredProcI.Params[2].AsString := Editl.Text;StoredProcI.ExecProc;

Після завдання вхідних параметрів за допомогою властивості params необхідно

підготувати процедуру до виконання. Для цього використовується метод prepare. Після

виконання процедури необхідно звільнити заняті ресурси. Цю роботу виконує метод

unprepare:

StoredProcI.Params[0].AsDateTime := Now;StoredProcI.Params[1].Aslnteger := SpinEditI.Value;StoredProcI.Params[2].AsString := Editl.Text;StoredProcI.Prepare;StoredProcI.ExecProc;StoredProcI.UnPrepare;

Ці два методи має смисл використовувати лише в тому випадку, якщо після

підготовки процедури вона буде викликана досить велике число разів підряд. Це

пов’язано з тим, що збережувана процедура готується сервером до виконання в

довільному випадку. При явному виклику метода prepare підготовка виконується лише

один раз. Тому наступний багатократний виклик процедури буде виконаний швидко.

Якщо в цій ситуації не вказати метод prepare, то при кожному виклику сервер буде

виконувати підготовку процедури знову і знову.

Для работи з параметрами також можна використати метод paramByName, який

повертає потрібний параметр зао його іменем:

StoredProcI.ParamByName('MaxDate').AsDateTime := Now;StoredProcI.Prepare;StoredProcI.ExecProc;

Якщо збережувана процедура повертає одиничний результат, то його можна одержати

в вихідному параметрі (або декількох вихідних параметрах). Для цього можна знову

скористатися методом ParamByName або властивістю Params:

101

with StoredProcl do begin Params[0].AsString := Edit1.Text; Prepare ; ExecProc; UnPrepare; Labell.Caption := Params[3].AsString;end;

Зверніть увагу, що порядок слідування параметрів збережуваної процедури в

програмі повинен відповідати порядку в декларації цієї процедури на сервері.

Якщо сервер підтримує виконання збережуваних процедур, що повертають набори

даних, то для їх використання досить відкрити набір даних компонента TStoredРroc за

допомогою метода Оpen. Якщо ця можливість не підтримується, то для одержання набору

даних збережуваної процедури можна скористатися запитом SQL, який здійснює виклик

процедури. Після активізації такого запиту в його наборі даних виявиться результат

виконання збережуваної процедури.

Для збереження і виконання збережуваних процедур під час виконання клієнтських

програм можна використовувати компонент TQuery. Продемонструємо це на прикладі

збережуваної процедури для сервера InterBase, який повертає в результаті набір даних.

Для створенні збережуваної процедури використовується запит з оператором

CREATE PROCEDURE.

CREATE PROCEDURE SumOrders (OrdDat DATE) RETURNS (DocNo VARCHAR(IO), OrdSum INTEGER) AS BEGIN FOR SELECT DocNo, SUM(ItemCount*Price) FROM ArchOrders WHERE OrdDat > :OrdDat GROUP BY DOCNO INTO :DocNo, :OrdSum DO SUSPEND;END

Для виконання збережуваної процедури компонент TQuery повинен містити такий запит:

SELECT * FROM SumOrders ('01.09.99')

В результаті в наборі даних запиту будуть міститися суми всіх замовлень після 1 вересня 1999 г.

Компонент TStoredProc.

Компонент TStoredProc забезпечує використання в програмах збережуваних

процедур. Так як цей інструмент обробки даних використовується серверами БД, то

компоненти TStoredProc використовуються в багаторівневих програмах. Прямим предком

компонента є клас TDBDataSet. Тому результатом виконання збережуваної процедури,

може бути не тільки одиночний результат, але і повноцінний набір даних. Засобами

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

102

Властивість DatabaseName визначає базу даних. Властивість StoredProcName задає

ім’я збережуваної процедури.

Важливу роль у роботі збережуваних процедур відіграють параметри. Через вхідні

параметри можна керувати роботою процедури. Вихідні параметри містять результат

виконання процедури.

Індексований список всіх параметрів збережуваної процедури (вхідних і вихідних)

міститься у властивості Рarams. Склад параметрів визначається текстом збережуваної

процедури. Керування параметрами в списку здійснюється за допомогою спеціалізованого

редактора, який відкривається при клацанні на кнопці властивості в Інспекторі об’єктів.

Крім цього, для доступу до окремих параметрів можна використати метод

РaramByName, який шукає параметр за його іменем.

Виконання збережуваної процедури здійснюється сервером після використання

методів ЕхесРгос або Оpen (лише для процедур, що повертають набір даних).

Перед виконанням збережувану процедуру необхідно підготувати. Зокрема, на

цьому етапі здійснюється передача параметрів і виділення ресурсов. Ця операція

виконується автоматично при використанні методів ЕхесPrос і Оpen або задається явно

методом Рrepare.

Явна підготовка процедури корисна при неоднократному виклику збережуваної

процедури. Якщо перед першим викликом процедури виконати метод Рrepare, то всі

наступні виклики будуть здійснюватися на основі зробленої один раз підготовки. В

іншому випадку подготовка буде виконуватися автоматично перед кожнім виконанням

збережуваної процедури.

Контрольні питання:1. Який компонент здійснює з’єднання з базою даних?2. Які параметри драйвера BDE необхідно вказати для підключення до БД?3. Для чого в Delphi використовують збережувані процедури?4. Як задати параметри для збережуваної процедури?

103

Тема 13. Використання механізму транзакцій компонента Database.

Керування транзакціями

При звертанні клієнтської програми до сервера всі команди повинні виконуватися в

рамках спеціально створюваних груп операторів – транзакцій. Транзакції незамінні при

довільному виді багатокористувацького доступу до даним, так як дозволяють ефективно

розв’язати виникаючих в цьому випадку проблем конкурентного доступа до даних і

множинних змін даних.

Транзакція – це група операторів SQL, об’єднаних логікою виконання програми,

які реалізують закінчену операцію з даними сервера. Після початку виконання транзакції

всі зроблені операторами зміни кешуються і записуються до бази даних лише після

команди, що підтверджує успішне виконання останнього оператора в рамках транзакції.

Відбувається так звана фіксація транзакції.

Якщо хоча б один оператор транзакції був виконаний з помилкою, то виконання

всіх операторів транзакції відміняється. Здійснюється так званий відкат транзакції.

Транзакція повинна задовольняти ряду вимог.

Довільна транзакція повинна бути або виконана, або відмінена.

Після завершення транзакції база даних повинна знаходитися в одному з

відомих стандартних станів. Після відкату транзакції база даних повинна знаходитися в

тому ж стані, що і до її початку.

Транзакція не повинна залежати від виконання інших транзакцій і не

впливати на них.

З цих вимог витікають деякі обмеження на оператори, що використовуються в

транзакціях.

Не можна використовувати оператори, які якимсь чином модифікують

структуру бази даних (створення і зміна структури таблиць, індексів, переглядів,

збережуваних процедур і т. д.).

Недопустиме використання операторів початку транзакції всередині

транзакції.

Не можна використовувати оператори надавання і позбавлення прав.

104

В багаторівневих програмах Delphi використання транзакцій забезпечує компонент

TDatabase. Будь-які дії з довільними наборами даних, підключеними через дане з’єднання,

що розміщуються між методом початку транзакції і методами фіксації або відкату

транзакції, будуть виконуватися в рамках однієї транзакції. Початок транзакції задається

методом StartTransaction. Фіксація транзакції здійснюється методом Commit. Відкат

виконується методом Rollback.

with Databasel do try StartTransaction; Table1.Edit; Table1.Fields[0].Value := Null; Table1.Post; { Довільні інші операції } Commit;exceptRollback;end;

При використанні транзакції кінцевий запис обнуленого поля в таблицю набору

даних Tablel відбудеться не після використання методу Post – (обнулене поле все-таки

з’явиться в базі даних), як в звичайних випадках, а тільки після виконання методу commit.

У випадку відкату транзакції полю буде повернено попереднє значення.

Зверніть увагу, що для виконання відкату транзакції дуже зручно використовувати

механізм генерування виключних ситуацій. Якщо всі оператори транзакції виконані

успішно, то транзакція фіксується. Якщо відбулася помилка – обробка виключення

дозволяє використати відкат.

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

Блокуванням даних дозволяють керувати рівні ізоляції транзакцій – механізм визначення

степені захищеності даних при багатокористувацькому доступі.

В Delphi рівні ізоляції транзакції простіше всього встановити за допомогою

властивості TransIsolation компонента TDatabase. Підтримуються три рівня ізоляції

транзакції.

Незавершене читання (tiDirtyRead) – транзакція читає всі незавершені зміни,

зроблені іншими транзакціями.

Завершене читання (tiReadCommitted) – транзакція може читати тільки

завершені зміни.

Повторюване читання (tiRepeatableRead) – зміни, зроблені з інших

транзакцій після читання набору даних, не видимі в даній транзакції. Тобто транзакція

може зчитувати заново одні і ті ж дані, так як інші транзакції не мають до них доступу.

105

При призначенні рівня ізоляції транзакцій слід витримувати баланс між надійністю

даних (тут слід враховувати ймовірність одночасного доступу до даних різних програм) і

швидкістю доступу (кожний рівень блокує дані в різній степені).

При малій кількості користувачів можна обійтися рівнем незавершеного читання.

Можливі помилки можна виправити за допомогою нехитрих перевірок ключових значень

перед записом. Зате всі програми одержать необмежений доступ до даних.

Рівень завершеного читання дає приблизну рівність можливостей неблокованого

доступу до даних і надійності даних.

Рівень повторюваного читання забезпує найвищу надійність даних ціною

максимально жорсткого блокування.

Не всі сервери підтримують всі три рівні ізоляції транзакцій. Механізм блокування

даних залижить від типу сервера. Большість серверів виконує блокування сторінками

даних, розмір однієї сторінки може задаватися адміністратором, мінімальний розмір

сторінки становить приблизно 1-2 Кбайт. В одній сторінці можуть зберігатися декілька

записів або частина одного запису (залежить від структури таблиці). При виконанні

деяких видів команд може бути заблокована ціла таблиця.

Контрольні питання:1. Що таке транзакція?2. Що таке рівні ізоляції транзакцій?3. Який компонент Delphi використовується для виконання транзакцій?

106

Тема 14. Сервер баз даних InterBase і компоненти InterBase Express.

1. Сервер баз даних InterBase і компоненти InterBase Express2. Механизм доступу до даних InterBase Express3. Компонент TIBDatabase.

1. Сервер баз даних InterBase і компоненти InterBase Express

В Delphi 5 з’явилася нова сторінка Палітри компонентів, що містить компоненти

доступу до даних, адаптовані для роботи з сервером InterBase. Компоненти з набору

InterBase Express призначені для роботи з сервером InterBase версії не нижче 5.5.

Їх перевага полягає в реалізації всіх функцій за рахунок прямого звертання до API

InterBase. Завдяки цьому суттєво підвищилась швидкість роботи компонентів. Окрім

цього, компоненти InterBase Express не потребують посередництва BDE, який сповільняє

роботу самих компонентів і програм в цілому. Нові компоненти надають розробнику нові

можливості.

Покращене керування транзакціями (для цього тепер призначено окремий

компонент TIBTransaction).

Нові компоненти доступу до даних, які дозволяють краще розв’язувати дуже

поширені задачі програмування (компоненти TIBDataSet, TIBSQL).

Можливість одержання відомостей про стан бази даних без прямого звертання

до її системних таблиць (компонент TIBDatabaseInfo).

Відслідковування стану процесів виконання запитів (компонент

TIBSQLMonitor).

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

використання цих компонентів в програмах БД не відрізняється від стандартної методики.

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

можна підключити до довільного стандартного компоненту відображення даних.

2. Механізм доступу до даних InterBase Express

В клієнтских прикладних програмах Delphi з’єднання з сервером БД здійснює

компонент TDatabase. Його аналогом з набору InterBase Express є компонент TIBDatabase.

Так як новий механізм доступу до сервера суттєво відрізняється від стандартного,

компонент TDatabase виявився розділеним на два: TIBDatabase і TIBTransaction.

Для створення клієнт/серверної програми необхідно не лише мати працюючий

сервер, але і інсталювати на клієнтських робочих місцях спеціальне програмне

забезпечення, яке виконує з’єднання клієнтської програми з сервером.

107

Механізм доступу до даних InterBase Express використовує для звертань до сервера

можливості клієнтського ПО InterBase. Якщо з даного комп’ютера доступні бази даних

будь-якого сервера на платформі InterBase, то розглянуті тут компоненти можуть

звертатися до цього сервера. При цьому не потрібно використовувати BDE.

В результаті все компоненти InterBase Express, що інкапсулюють набір даних,

повинні звертатися до бази даних лише через компонент з’єднання TIBDatabase.

Компоненти InterBase Express з метою пришвидшення виконання всіх функцій (в тому

числі і для з’єднання) використовують API InterBase.

3. Компонент TIBDatabase.

Оскільки для доступу до бази даних компонентам InterBase Express не потрібна

BDE, то для створення з’єднання використовується всього одна властивість

DatabaseName. В ній необхідно вказати повний шлях (включаючи ім’я сервера) до

вибраного файла БД з розширенням gdb. При клацанні на кнопці властивості в Інспекторі

об’єктів для цього можна скористатися стандартним діалогом вибору файла.

Компонент має власний редактор (викликається контекстним меню), який дозволяє

задати значення основних властивостей, що забезпечують з’єднання з базою даних (User

Name, Password, SQLRole, Character Set). Зокрема, необхідно вказати шлях до бази даних.

Він включає в себе ім’я сервера і локальний шлях до бази даних на сервері. У випадку,

коли сервер локальний, досить вказати лише шлях до бази даних.

Розглянемо випадок віддаленого сервера. Нехай InterBase встановлено в мережі на

комп’ютері з мережевим іменем SERV_IB і доступний по протоколу TCP/IP. Нехай база

знаходиться на сервері на диску E: у файлі ME_GDB.GDB. У даному випадку слід вказати

тип підключення Remote, ім’я сервера SERV_IB, вибрати протокол TCP/IP, а шлях до бази

даних встановити локальним відносно сервера, тобто Е:\ ME_GDB.GDB (тобто ми не

задаємо мережевий шлях файла, а локальний).

Настройка з’єднання проводиться звичайним способом (як у компонента

TDatabase). Для завдання вводимих при подключенні параметрів (ім’я користувача,

пароль, схема, роль, мова) використовуються властивості Params і LoginPrompt. При

завданні мови слід вказати параметр WIN-1251.

З’єднання підключається і відключаєть властивостю Сonnected. Властивість

IdleTimer задає часовий інтервал до відключення невикористовуваного з’єднання.

В компоненті TIBDatabase відсутні засоби керування транзакціями, які винесені до

окремого компоненту TIBTransaction. Властивість DefaultTransaction дозволяє задати

108

транзакцію за замовчуванням. Змінюючи значення цієї властивості, можна в одному

з’єднанні працювати з декількома транзакціями.

Довільна дія з базою даних відбувається у рамках тієї чи іншої транзакції. Робота з

InterBase базується на явному керуванні транзакціями.

Компонент TIBTransaction інкапсулює засоби керування транзакцією для з’єднання

з сервером InterBase.

Один компонент транзакції може працювати з декількома базами даних одночасно.

Для цього використовується властивість DefaultTransaction відповідних компонентів

з’єднання TIBDatabase.

Основною є властивість Params, в якій можна вказати рівень ізоляції транзакції.

Один з чорирьох рівнів ізоляції можна вибрати за допомогою редактора компонента, який

викликається з контекстного меню. Для більшості випадків рекомендується

використовувати режим Read Commited, який дозволяє запитам в одній транзакції

“бачити” зміни зроблені в контексті інших трансакцій.

TIBTransaction посилається на компонент бази даних за допомогою властивості

DefaultDataBase. Якщо також вказати властивість DefaultTransaction у TIBDatabase, то в

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

“підхоплювати” і вказану транзакцію.

Контрольні питання:1. Що виконує компонент TIBDatabase і TIBTransaction?2. Які властивості компонента TIBDatabase Ви знаєте?

109

Тема 15. Виконання запитів за допомогою компонента TIBDataSet

1. Програмне редагування даних.2. Редагування даних за допомогою візуальних компонентів.3. Вибірка даних з таблиці.

1. Вибірка даних з таблиці.

Спеціалісти вважають, що компоненти TIBTable та TIBQuery використовувати

недоцільно, оскільки вони призначені в основному для сумісності з компонентами BDE.

Для роботи з наборами даних радять використовувати компонент TIBDataSet.

Помістимо на форму такі компоненти:

IBTransaction1:TIBTransaction;IBDatabase1:TIBDatabase1;

IBDataSet1:TIBDataSet;

DataSourse1:TDataSourse1;DBGrid1:TDBGrid,

DBNavigator1:TDBNavigator.

Налаштуємо компоненти IBTransaction1 і IBDatabase1 на з’єднання з базою даних

Employee.gdb, які містится в каталозі Program Files\Common Files\Borland Shared\Data. У

IBDataSet1 необхідно задати властивості DataBase та Transaction, у DataSourse1задати

DataSourse1.DataSet рівним IBDataSet1, а у DBGrid1.Data-Sourse рівним DataSourse1. У

компоненті IBDataSet1 властивість BufferChunks встановимо рівною 10000, щоб буфер мів

вмістити весь набір даних.

Тепер необхідно вказати той запит, який ми хочемо виконати у властивості

SelectSQL компонента IBDataSet1

SelectSQL: SELECT * FROM EMPLOYEE;

Для того, щоб виконати цей запит під час виконання програми необхідно створити

такий обробник події OnFormCreate:

procedure TForm1.FormCreate(Sender: TObject);begin IBDatabase1.Connected:=True; IBDataSet1.Active:=True;end;

2. Редагування даних за допомогою візуальних компонентів.

В запущеному на виконання прикладі редагувати елементи за допомогою

компонента DBGrid1 безпосередньо не вдасться, оскільки ми задали лише властивість

SelectSQL. Для можливості модифікації необхідно виконати SQL-запит UPDATE, задавши

такі команди у властивості ModifySQL:

110

update EMPLOYEEset EMP_NO = :EMP_NO, FIRST_NAME = :FIRST_NAME, LAST_NAME = :LAST_NAME, PHONE_EXT = :PHONE_EXT, HIRE_DATE = :HIRE_DATE, DEPT_NO = :DEPT_NO, JOB_CODE = :JOB_CODE, JOB_GRADE = :JOB_GRADE, JOB_COUNTRY = :JOB_COUNTRY, SALARY = :SALARYwhere EMP_NO = :OLD_EMP_NO

Як бачимо, замість реальних значень у цьому запиті вказані параметри, назви яких

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

полів конкретного запису, то IBDataSet1 сам задасть значення всіх параметрів, взявши їх

із відповідних полів. Запит ModifySQL виконується і лише після цього зміни зроблені

користувачем появляться у базі даних.

Аналогічна послідовність пов’язана з запитами у властивостей InsertSQL та

DeleteSQL – вони виконуються при вставленні нового запису і вилучені запису.

InsertSQL:

insert into EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE, DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY)values (:EMP_NO, :FIRST_NAME, :LAST_NAME, :PHONE_EXT, :HIRE_DATE, :DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY)

DeleteSQL:

delete from EMPLOYEEwhere EMP_NO = :OLD_EMP_NO

Існує ще одна важлива особливість при створенні таких запитів. Після виконання

довільної модифікуючої дії IBDataSet1 виконує запит вказаний у властивості RefreshSQL,

який у нашому випадку має вид:

Select EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE, DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY,

111

FULL_NAMEfrom EMPLOYEE where EMP_NO = :EMP_NO

Смисл даного запиту стає очевидним, якщо допустити в базі даних існування

тригерів для таблиці EMPLOYEE, які модифікують значення полів. Оскільки зміни

відбуваються у самій базі даних відразу після модифікації, то без повторного

перечитування запису (тобто без Select модифікованого запису або вставленого запису) ми

не взнаємо про ті зміни, які були зроблені в тригерах.

IBX надає можливість швидко згенерувати необхідні модифікаційні запити за

допомогою редактора IBDataSet, який викликається за допомогою контекстного меню.

Вибравши із списка Table Name нашу таблицю, сформуємо списки Key Fields і Update

Fields. В списку Key Fields необхідно виділити ті поля, які будуть формувати умову

WHERE в запитах. Очевидно, що це повинні бути поля, які визначають первинний ключ у

таблиці. Якщо такий ключ існує для вибраної таблиці, його можна помітити просто

натиснувши на кнопку Select Primary Keys. В списку Update Fields необхідно виділити ті

поля, які користувач потім може редагувати. На рисунку видно, що поле FULL_NAME не

включено до списку, оскільки це CALCULATED-поле і його значення не можна

змінювати.

Залишається лише натиснути кнопку Generate SQL, щоб одержати всі запити:

InsertSQL, ModifySQL, DeleteSQL, RefreshSQL.

3. Програмне редагування даних.

Оскільки TIBDataSet породжений класом ТDataSet, у нього є три основні методи

для зміни даних: Delete, Insert (Append), Edit. Вставимо на форму кнопку Button. Нехай

нам потрібно вставити новий запис в IBDataSet1 при натискуванні цієї кнопки. Обробник

події OnClick при цьому буде мати вид:

112

procedure TForm1.SpeedButton1Click(Sender: TObject);begin with IBDataSet1 do begin Insert; FieldByName('EMP_NO').AsInteger:=147; FieldByName('DEPT_NO').AsInteger:=600; FieldByName('JOB_CODE').AsString:='VP'; FieldByName('JOB_GRADE').AsInteger:=2; FieldByName('SALARY').AsInteger:=105900; FieldByName('HIRE_DATE').AsDateTime:=Now; FieldByName('JOB_COUNTRY').AsString:='USA'; FieldByName('FIRST_NAME').AsString:='Smit'; FieldByName('LAST_NAME').AsString:='Piter'; Post; end;end;

Метод Insert формує пустий буфер для нашого (поки ще не введеного запису), Далі

ми задаємо значення потрібних полів. Після виконання методу Post IBDataSet1 виконує

запит, записаний у властивості InsertSQL, підставивши замість параметрів значення полів,

які ми задали.

Якщо запит пройшов успішно, то IBDataSet1 автоматично виконує RefreshSQL для

поновлення тільки що вставленого запису – для перевірки змін, які внесені на стороні бази

даних.

Контрольні питання:1. Опишіть роботу компонента TIBDataSet?2. Яка властивість компонента TIBDataSet дає можливість модифікації БД? Опишіть

структуру команди.3. Яка властивість компонента TIBDataSet здійснює вибір інформації з БД? Опишіть

структуру команди.4. Яка властивість компонента TIBDataSet додає до БД новий запис? Опишіть структуру

команди.5. Яка властивість компонента TIBDataSet вилучає інформацію з БД? Опишіть структуру

команди.

113

Тема 16. Підпорядковані запити в таблицях

Механізм master-detail

Механізм мастер-деталь часто виконується в клієнтських програмах для роботи з

базами даних, оскільки саме він дозволяє легко пов’язувати дані з різних таблиць, які

одержуються в наслідок нормалізації бази даних.

Додамо в програмі, яку ми розробили в роботі №14 нові компоненти:

IBDataSet2, DataSource2, DBGrid2. Пов’яжемо DataSource2 з IBDataSet2, а DBGrid2 з DataSource2. Вкажемо такий SelectSQL запит для IBDataSet2:

SELECT * FROM DEPARTMENT;

За допомогою редактора DataSet Editor створимо такі запити:

InsertSQL:

insert into DEPARTMENTDEPT_NO, DEPARTMENT, HEAD_DEPT, MNGR_NO, BUDGET, LOCATION, PHONE_NO)values:DEPT_NO, :DEPARTMENT, :HEAD_DEPT, :MNGR_NO, :BUDGET, :LOCATION, :PHONE

_NO)

DeleteSQL:

delete from DEPARTMENTwhere DEPT_NO = :OLD_DEPT_NO

ModifySQL:

update DEPARTMENTset DEPT_NO = :DEPT_NO, DEPARTMENT = :DEPARTMENT, HEAD_DEPT = :HEAD_DEPT, MNGR_NO = :MNGR_NO, BUDGET = :BUDGET, LOCATION = :LOCATION, PHONE_NO = :PHONE_NOwhere DEPT_NO = :OLD_DEPT_NO

RefreshSQL:

Select DEPT_NO, DEPARTMENT, HEAD_DEPT, MNGR_NO, BUDGET, LOCATION, PHONE_NOfrom DEPARTMENT where

114

DEPT_NO = :DEPT_NO

Тепер необхідно внести зміни до IBDataSet1. По перше, необхідно задати

властивість IBDataSet1.DataSource рівною DataSource2. По друге, необхідно зміними

IBDataSet1.SelectSQL:

SELECT * FROM EMPLOYEEWHERE DEPT_NO=:DEPT_NO;

При цьому значення параметра :DEPT_NO буде автоматично вибиратися з

одноіменного поля IBDataSet2: DEPT_NO.

Запустивши програму, ми побачимо, що при переміщеннях по другій таблиці в

першій будуть відображені лише співробітники поточного відділу.

Настроїмо IBDataSet1 таким чином, щоб при додаванні нового запису про

робітника автоматично підставлявся номер поточного відділу. Для цього напишемо такий

обробник OnNewRecord у IBDataSet1:

procedure TForm1.IBDataSet1NewRecord(DataSet: TDataSet);begin IBDataSet1.FieldByName('DEPT_NO').AsInteger:= IBDataSet2.FieldByName('DEPT_NO').AsInteger;end;

Контрольні питання:1. Яким чином виконується вибір даних з підпорядкованих таблиць?2. Яка комнда здійснює вибір підпорядкованих записів?

115

Література

Крис Фиайли, SQL Руководство по изучению языка, СПб Питер 2004

Д.Соломон, MICROSOFT SQL Server Энциклопедия пользователя

Х.Ладани, SQL Энциклопедия пользователя

«Рекоммендации по общепользовательскому интерфейсу», Microsoft ,

редакция.

116

Fondovi-lekcii

Documents

Transcript of Fondovi-lekcii