Мова SQL (Structured Query Language) – як універсальний засіб створення баз даних, маніпулювання данними та отримамання потрібної інформації, НУДПСУ

Мова SQL(Structured Query Language) – як універсальний засіб створення баз даних, маніпулювання данними та отримамання потрібної інформації.

SQL - це аббревіатура від Structured Query Language, що в перекладі з англійської означає мова структурованих запитів. Мова SQL орієнтована на реляційні бази даних, про які велась мова в попередніх лекціях. В ідеалі, люба мова для роботи з базами повинна надавати користувачеві такі можливості:

• створюватибази даних і таблиці з повним описом їх структури;

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

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

Крім того, мова роботи з базами даних повинна вирішувати всі вищезгадані при мінімальних зусиллях зі сторони користувача, а структура і синтаксис її команд повина бути простою і доступною для вивчення. Також мова повинна бути універсальною, тобто повинна відповідати загальновизнаному стандарту, що дозволить використовувати один і той же синтаксис і структуру команд при переході від однієї СУБД до іншої. Мова SQL задовольняє практично всім цим вимогам. Мова SQL має дві основні компоненти:

• мова DDL (Data Definition Language), засобами якої визначаються структури бази;

• мова DML (Data Manipulation Language), засобами якої здійснюється виборка і поновлення даних.

Мова SQL може використовуватись двома способами. Перший передбачає інтерактивний режим роботи, який полягає в тому, що користувач з терміналу вводить окремі SQL-оператори. Другиий метод полягає в тому, що SQL-оператори вбудовуються певним чином у процедурні мови програмування (C, C++ ). В основному ці 2 форми SQL працюють однаково. На практичних роботах ми будемо користуватись інтерактивною формою.

На сьогоднішній день для мови SQL існує міжнародний стандарт (ISO 1992р), який робить цю мову стандартною мовою визначення і маніпулювання реляційними базами даних.

Термінологія SQL. Стандарт ISO SQL не підтримує таких формальних термінів як “відношення”, “атрибут” і “кортеж”, замість них вживаються терміни “таблиця”, “стовпчик” і “рядок”. Ми будемо притримуваться термінології ISO.

Запис SQL-операторів. Мова SQL містить зарезервовані слова а також слова, які визначаються користувачем. Зарезервовані слова є постійною частиною самої мови SQL і мають фіксоване значення. Їх потрібно записувати так як це визначено мовою, їх не можна розбивати на частини для переносу з одного рядка в інший. Слова, що визначаються користувачем, задаються самим користувачем ( у відповідності з визначеними синтаксичними правилами) і є іменами різних об’єктів бази – таблиць, стовчиків, представлень, індексів, тощо.

Слова в операторі розташовуються у порядку визначеному синтаксичними правилами мови. Більшість компонентів SQL-оператора не чутливі до регістру. Це означає, що можна використовувати як малі так і великі букви. Наприклад, зарезервоване слово Select можна записати і так: SELECT – обидва записи еквівалентні. В подальшому ми будемо притримуватиись таких правил:

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

• Малі букви латинського алфавіту будемо використовуватимемо для написання слів, які визначаються користувачем.

• Вертикальна риска ( | ) вказує на необхідність вибору одного з декількох наведених значень – наприклад, a | b | c.

• Фігурні дужки визначають обов’язковий елемент – наприклад, { a }.

• Квадратні дужки визначають необов’язковий елемент – наприклад, [a].

• Трикрапка ( … ) використовується для необовязкового повтору певної конструкції від 0 до декількох раз – наприклад, {a|b}[,c…]. Цей запис означає, що після a або b записується від нуля до декількох повторів с, відокремлених комами.

На практиці для визначення таблиць використовуються DDL-оператори, а для заповнення цих таблиць даними і виборки з них використовуються DML-оператори. Спочатку ми розглянемо DML-оператори, а потім DDL-оператори.

До DML-операторів мови SQL відносяться такі оператори:

• SELECT – виборка даних з бази;

• INSERT – вставка даних у таблицю;

• UPDATE – поновлення (заміна) даних в таблиці;

• DELETE – видалення даних з таблиці.

Числові константи SQL. Любе число є константою, наприклад, 200,3,0.9 – є числовими константами.

Символьні або текстові константи. Люба послідовність букв і цифр є символьною константою. Символьні константи поміщаються в одинарні кавички, наприклад, ‘Школа’,’5-та школа’,’200’. Зауважимо, що константи 200 і ‘200’ є різними в SQL. В мові SQL константи як числові так і нечиислові називають одним словом – літерал.

Приклад використання літералів в операторі INSERT:

INSERT INTO ustanova(kod,nazva) VALUES(10234,’Академія ДПС України’)

Цей оператор вставляє в таблицю ustanova новий рядок значення першої колонки стає 10234 (ця колонка має числовий формат), а другої колонки (вона має текстовий формат) – “Академія ДПС України”.

В мові SQL діють арифметичні операції – додавання, віднімання, множення і ділення. Ці операції позначаються:

• + - додавання (сума);

• - - віднімання (різниця);

• * - множення (добуток);

• / - ділення (частка).

Алгебричні вирази. Люба числова константа є алгебричним виразом. Люба назва стовпчика таблиці є алгебричним виразом. Алгебричні вирази називають сумісними по типу, якщо вони мають однаковий тип (символьний, числовий). Якщо A i B алгебричні вирази числового типу , то A+B, A-B, A*B, A/B - алгебричні вирази числового типу. Наприклад, 100/2, (column_name*0.2)/1.2, column_name1*column_name2+100. Якщо вирази А і B мають строковий тип, то A+B є строковим виразом. Наприклад, ‘Академія’+’ ДПСУ’, column_name3 + ’зараховано’, column_name3+ column_name4. У вище наведених прикладах column_name1, column_name2 означали назви стовпчиків числового типу, а column_name3, column_name3 – назви стовпчиків символьного типу.

В мові SQL можна використовувати такі оператори зрівнювання:

Якщо вирази A i B вирази одного типу, то вирази:

A = B, A < B, A > B, A <= B, A >= B, A <> B

називатимемо предикатами. Предикати приймають 2 значення: ІСТИНА і НЕІСТИНА.

Наприклад, column_name1>1000, column_name3=‘Академія’.

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

Якщо P_1, P₂ – предикати, то

P₁ AND P₂  , P₁ OR  P₂ , NOT P₁

також предикати.

Нижче в таблиці наведені результати застосування булевих операцій.

Прості запити

Призначення оператора SELECT полягає в тому, щоб вибирати необхідні дані з однієї або декількох таблиць. Це виключно потужний оператор, який здатний виконувати дії, еквівалентні  операторам реляційної алгебри selection, projection ijoin. Загальнний формат оператора SELECT має вид:

SELECT [DISTINCT | ALL] {* | column_expression [ AS new_name][,…]}

       FROM table_name [alias][,…]

       [WHERE condition]

       [GROUP BY  column_list] [HAVING condition]

       [ORDER BY column_list]

Параметр column_expression – це і’мя стовпчика або виразу з декількох імен. Параметр table_name – це є і’мя існуючої в базі таблиці або представлення, до яких потрібно доступитись. Необов’язковий параметр alias – це скорочення для імені таблиці table_name. Обробка елементів оператора SELECT виконується в такій послідовності:

1. FROM  - Визначаються імена таблиць, які використовуються

2. WHERE - Виконується фільтрація рядків згідно заданої умови відбору

3. GROUP BY - Утворюються групи рядків з однаковими значеннями в зазначених колонках

4. HAVING – Фільтруються групи рядків обєкту згідно вказаної умови

5. SELECT – Встановлюється, які стовпчики повині бути у вихідних даних

6. ORDER BY – Визначається порядок результату виконання оператора.

Порядок фраз в операторі SELECT не можна змінювати. Тільки дві фрази –SELECT і FROM є обов’язковими, всі інші можуть бути пропущеними. Результат запиту до таблиць є таблиця. Символ *  в операторі SELECT означає, що будуть вибрані всі стовпчики таблиці.

Приклади. Нехай в таблиці table_a зберіігається інформація про покупки товарів, а в table_b – про продаж цих товарів. Ці таблиці мають таакий вид:

       table_a                                                                table_b

В стовпчики “kod” в обох таблицях заноситься код товару. В стовпчик “suma” таблиці table_a заноситься вартість закупленого товару, а в таблиці table_b – сума проданого товару. В стовчик “data” таблиці table_a заноситься дата закупки товару, а в table_b – дата продажу товару.

Задача. Одержати перелік товарів з відповідними сумами і датами, які були закуплені. Відповідний оператор SELECT матиме вид:

SELECT kod,suma,data

FROM table_a

Оскільки не задана умова відбору фраза WHERE не потрібна. Результат запиту матиме вид:

Точно такий же результат буде після виконання такого SELECT’у:

       SELECT *

       FROM table_a

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

       SELECT suma, kod

       FROM table_a

Результат запиту матиме вид:

(6 рядків)

Використання ключового слова DISTINCT. Задача. Потрібно скласти перелік кодів товарів які закуповувались. Якщо оператор SELECT задати таким чином:

       SELECT kod

       FROM table_a

Результат запиту матиме вид:

(6 рядків)

Зверніть увагу на дубльовані рядки, поскільки, на відміну від операції реляційної алгебри projection, оператор SELECT не виключає дубльованих рядків. Для видалення з результуючої таблиці дубльованих рядків використовується ключове слово DISTINCT. Відредагований запит матиме вид:

       SELECT DISTINCT kod

       FROM table_a

Результат запиту матиме вид:

(3 рядки)

При використанні ключового слова ALL оператор SELECT повертає всі рядки.

За змовчуванням (не вказано ні DISTINCT ні ALL), вважається, що діє ALL.

Обчислювальні поля. Задача. Одержати перелік товарів з відповідними сумами і відповідними сумами податку ПДВ, які закладені у вартість закуплених товарів. Відповідний оператор SELECT матиме вид:

       SELECT kod,suma,(suma*0.2)/(1+0.2) AS pdv

       FROM table_a

 Результат запиту матиме вид:

(6 рядків)

Умова відбору (ключове слово WHERE)

В наведених вище прикладах в результаті виконання оператора SELECT вибирались всі рядки вказаної таблиці. Якщо потрібно задати критерій відбору рядків це задається після ключового слова WHERE. Існує 5 основних типів умов відбору (або, згідно термінології ISO, предикатів).

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

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

• Приналежність до множини. Перевіряється, чи належить результат обчислення виразу до заданої множини значень.

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

• Значення NULL. Перевіряється, чи даний стовпчик має невизначене значення NULL.

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

Зрівняння.

Необхідно скласти перелік товарів, ціна покупки яких перевищила 90.

       SELECT kod,suma,data

       FROM table_a

       WHERE suma>90

Результат запиту матиме вид:

(2 рядки)

Одержати перелік товарів з відповідними сумами і відповідними сумами податку ПДВ, в яких сума податку ПДВ перевщує 10. Відповідний оператор SELECT матиме вид:

       SELECT kod,suma,(suma*0.2)/(1+0.2) AS pdv

       FROM table_a

       WHERE suma*0.2/1.2 >10

Результат запиту матиме вид:

Необхідно скласти перелік покупок товару з кодом 1, ціна покупки яких не перевищувала 80.

       SELECT kod,suma,data

       FROM table_a

       WHERE kod=1 AND suma<=80

Результат запиту матиме вид:

Використання діапазонів (BETWEEN / NOT BETWEEN)

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

       SELECT kod,suma,data

       FROM table_a

       WHERE suma BETWEEN 50 AND 100

Результат запиту матиме вид:

Умови відбору з перевіркою входження у множину (IN / NOT IN)

Задача. Необхідно скласти список покупок, які здійснювались 1.1.2002 і 2.2.2002 року. Запит матиме такий вид:

       SELECT *

       FROM table_a

       WHERE data IN( #1/1/2002#,#2/2/2002#)

Результат запиту матиме вид:

Умови відбору з допомогою шаблону (LIKE / NOT LIKE)

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

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

- _   Символ підкреслювання представляє любий одиночний символ.

Всі останні символи в шаблоні представляють самі себе. Наприклад:

• Adresa LIKE ‘П%’ – цей шаблон означає, що перший символ значення стовпчика Adresa повинен бути символом П, а всі останні символи можуть бути любими;

• Adresa LIKE ‘П___’ - цей шаблон означає, що значення стовпчика Adresa повиненно мати довжину 4 і починатись з символа П;

• Adresa LIKE ‘%п’ – цей шаблон означає, значення стовпчика Adresa повинено закінчуватись символом п;

• Adresa LIKE ‘%ДПС%’ – цей шаблон означає, що значення стовпчика Adresa повиненно містити підслово ДПС;

• Adresa NOT LIKE ‘П%’ – цей шаблон означає, що перший символ значення стовпчика Adresa НЕ повинен бути символом П, а всі останні символи можуть бути любими.

Приклад. Нехай таблиця ustanova має такі стовпчики: kod, Nazva, Adresa, в яких заноситься інформація про ідентифікаційний код підприємства, його назву і адресу відповідно. Тоді запит

       SELECT *

       FROM ustanova

       WHERE Adresa LIKE’%Ірпінь%’

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

Використання значення NULL в умовах відбору (IS NULL / NOT NULL)

Для знаходження незаповнених значень певного стовпчика деякої таблиці використовується спеціальне ключове слово IS NULL. Наприклад, для формування списку підпрємств з незаповненими адресами потрібно написати такий запит:

       SELECT *

       FROM ustanova

       WHERE Adresa IS NULL

і навпаки, запит

       SELECT *

       FROM ustanova

       WHERE Adresa IS NOT NULL

сформує список підприємств із заповненими адресами.

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

До цих пір кожний запит базувався на одній таблиці. Але на практиці цього недостатньо. Для того, щоб об’єднать в результуючий таблиці даних з декількох таблиць, необхідно виконати операцію з’єднання. Для виконання з’єднання двох і більше таблиць в одному запиті необхідно після ключового слова FROM вказати імена цих таблиць, розділивши їх комами, після чого додати в запит ключове слово WHERE з визначенням стовпчиків, які викориситовуються для з’єднання вказаних таблиць. Крім того, замість імен таблиць можна використовувати назначені їм в розділі FROM псевдоніми (аліаси). В цьому випадку імена таблиць і назначені їм аліаси повинні розділятись комами.

Імена таблиць і стовпчиків

Повне ім’я стовпчика складається з імені таблиці, безпосередньо за яким стоїть крапка, а за нею - ім’я стовпчика. Наприклад, table_a.kod,table_b.suma, table_a.suma. В наведених раніше прикладах імена таблиць можно було опускати, поскільки запити використовували тільки одну таблицю. Навіть формуючи запит до декількох таблиць їх імена можна опустити, якщо імена цих таблиць різні. Одначе так буває не завжди.

З’єднання таблиць (join)

Припустимо, необхідно встановити звязок між закупленим товаром і проданим. Для цього потрібно прирівняти код купленого товару до коду проданого товару, тобто table_a.kod=table_b.kod. Отже запит матиме вид:

SELECT a.kod AS kod_a,a.suma AS suma_a, b.kod AS kod_b,b.suma AS suma_b

       FROM table_a a,table_b b

       WHERE a.kod=b.kod

Результат запиту матиме вид:

Тепер пояснимо роботу оператора SELECT, в якому використовується декілька таблиць. На основі таблиць, які зустічаються після ключового слова FROM (в нашому випадку – table_a i table_b) , будується декартовиий добуток множин рядків таблиць. В одержаному добутку перевіряється істинність предикату заданого у WHERE, якщо предикат приймає значення істина, рядок поміщається в рельтуючу множину, в противному разі – не включається. Тепер стає зрозумілим, чому в результаті є 6 рядків в яких kod_a i kod_b приймають значення 1 (в таблиці table_a значення 1 в колонці kod зустрічається 3 рази, а у таблиці table_b відповідно 2 рази, отже в декартовому добутку буде 6 рядків) і 4 рядки, в яких kod_a i kod_b приймають значення 2 (в таблиці table_a значення 2 в колонці kod зустрічається 2 рази і в таблиці table_b 2 рази, отже в декартовому добутку буде 4 рядків). Оскільки, в table_b в колонці kod 3 не зустрічається, тому в результаті не має рядка з kod_a=kod_b=3.

В наведеному вище прикладі продемонстровано з’єднання (join) двох таблиць. З’єднання, яке використовує предикати, які побудовані виключно на рівностях, називається еквіз’єднанням. Еквіз’єднання є найбільш поширеним типом з’єднанням, але існують і інші типи з’єднання. Фактично в з’єднанні можна використовувати любу операцію зрівняння. Наприклад,

SELECT a.kod AS kod_a,a.suma AS suma_a, b.kod AS kod_b,b.suma AS suma_b

       FROM table_a a,table_b b

       WHERE a.kod>b.kod

Результат запиту матиме вид:

Наведемо приклад більш практичнішої задачі. Для обчислення прибуткового податку з громадян в Excel’і користуються такою формулою

Якщо(Дохід<=17; 0;

    Якщо(Дохід<=85; (Дохід-17)*0.1;

       Якщо(Дохід<=170; (Дохід-85)*0.15+6.8;

          Якщо(Дохід<=1020; (Дохід-170)*0.2+19.55;

            Якщо(Дохід<=1700; (Дохід-1020)*0.3+189.55;

                  (Дохід-1700)*0.4+393.55)))));

Нижче наведена таблиця tpp обрахування прибуткового податку на зарплату:

Якщо D – дохід, то податок P на прибуток обраховується за формулою:

(D-d_l)*p+c.

Рядок, з якого вибирабться відповідні d_l, p i c, визначається нерівностями:

d_l<D<=d_r

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

Нахай таблиця Vidomist має вид:

де через kod позначено ідентифікаційний код особи, priz – прізвище особи, D – суму сукупного доходу.

Для того, щоб сформувати відомість нарахованих сум податку на прибуток, необхідно застосувати такий оператор SELECT:

       SELECT a.kod,a.priz, (a.D-b.d_l)*b.p+b.c AS podatok

       FROM Vidomist a, tpp b

       WHERE (a.D>b.d_l) AND (a.D<=b.d_r)

Стандарт SQL-92 додатково надає і таку можливість створення цього з’єднання:

FROM Vidomist a INNER JOIN tpp b ON((a.D>b.d_l) AND (a.D<=b.d_r))

Таким чином, запит виглядатиме так:

SELECT a.kod,a.priz, (a.D-b.d_l)*b.p+b.c AS podatok

FROM Vidomist a INNER JOIN tpp b ON((a.D>b.d_l) AND (a.D<=b.d_r))

Запит, який з’єднанує таблиці table_a i table_b матиме вид:

SELECT a.kod AS kod_a,a.suma AS suma_a, b.kod AS kod_b,b.suma AS suma_b

FROM table_a a INNER JOIN table_b b ON a.kod=b.kod

Ліве з’єднання (LEFT JOIN)

Ліве з’єднання таблиць, наприклад table_a і table_b, застосовують тоді, коли потрібно, щоб результат містив всі рядки однієї таблиці (наприклад table_a) і лише ті рядки з іншої таблиці (наприклад table_b), для яких в першій таблиці є відповідники. Наприклад, ліве з’єднання таблиць table_a і table_b матиме вид:

SELECT a.kod AS kod_a,a.suma AS suma_a, b.kod AS kod_b,b.suma AS suma_b

FROM table_a a LEFT JOIN table_b b ON a.kod=b.kod

Результат запиту матиме вид:

Для стовпчика kod таблиці table_a зі значеннім 3 у таблиці table_b немає відповідника, тому в результуючій таблиці у стовчиках, які віповідають стовпчикам таблиці table_b стоять значення NULL.

Наведемо приклад з’єднання 3-х таблиць. Змінимо структуру таблиці Vidomist, а саме:

де стовпчики year i mounth означають відповідно рік і місяць виплати зарплати. Додамо нову таблицю Osoba, в якій зберігається інформація про працівників:

Задача. Потрібно сформувати відомість зарплати за 2-й місяць 2002 року з колонками: “Прізвище”, “Нараховано”, “Сума податку”, “До виплати”.

Відповідний SQL-оператор матиме вид:

SELECT a.priz AS Прізвище, b.D AS Нараховано, (b.D-c.d_l)*c.p+c.c AS [Сума податку], b.D-((b.D-c.d_l)*c.p+c.c) AS [До виплати]

FROM (Osoba a INNER JOIN Vidomist b ON a.kod=b.kod) INNER JOIN tpp c ON((b.D>c.d_l) AND (b.D<=c.d_r))

WHERE b.year=2002 AND b.mounth=2

Еквівалентна форма даного SQL-оператора матиме вид:

SELECT a.priz AS Прізвище, b.D AS Нараховано, (b.D-c.d_l)*c.p+c.c AS [Сума податку], b.D-((b.D-c.d_l)*c.p+c.c) AS [До виплати]

FROM Osoba a, Vidomist b, tpp c

WHERE a.kod=b.kod AND (b.D>c.d_l) AND (b.D<=c.d_r) AND

b.year=2002 AND b.mounth=2

Результуюча таблиця матиме вид:

Сортування результату (фраза ORDER BY)

Взагалі кажучи, рядки резудьтуючої таблиці SQL-запиту не впорядковані якимось чином. Але їх можна впорядкувати певним чином, для чого в операторі SELECT використовується фраза ORDER BY. Фраза ORDER BY містить список розділених комами ідентифікаторів стовпчиків таблиць по яким потрібно впорядкувати результуючу таблицю. Фраза ORDER BY дозволяє впорядкувати вибрані записи в порядку зростання (ASC) або спадання (DESC) значення стовпчиків, незалежно від того є вони в результуючій таблиці чи ні. Але в деяких діалектах вимагається, щоб фраза ORDER BY містила лише назви стовчиків, які зустрічаються в списку виборки оператора SELECT. Отже, якщо потрібно впорядкувати результат запиту з попереднього прикладу по коду, необхідно в кінці SQL-оператора додати ORDER BY a.kod. Оператор матиме вид:

SELECT a.kod AS kod_a,a.suma AS suma_a, b.kod AS kod_b,b.suma AS suma_b

       FROM table_a a LEFT JOIN table_b b ON a.kod=b.kod

       ORDER BY a.kod

Результат запиту матиме вид:

а результат SQL-оператора

SELECT a.kod AS kod_a,a.suma AS suma_a, b.kod AS kod_b,b.suma AS suma_b

FROM table_a a LEFT JOIN table_b b ON a.kod=b.kod

ORDER BY a.kod, a.suma

матиме вид:

Групування результатів (фраза GROUP BY). Агрегатні функціії

Стандарт ISO містить визначення 5 агрегатних функцій:

Всі ці функції оперують зі значеннями одного стовпчика і повертають єдине значення. Функції COUNT, MIN, MAX можуть застосовуватись як до числових стовпчиків, так і до нечислових, а функції SUM i AVG застосовують лише до числових стовпчиків. За виключенням COUNT(*), при обчисленні значення функцій значення NULL не враховуються. Варіант COUNT(*) є особливим випадком застосування функкції COUNT. COUNT(*) повертає загальну кількість рядків результуючої таблиці, незалежно від того містяться там NULL-значення, дубльовані рядки або любі інші значення. Якщо до застосування агрегатних функцій необхідно виключити дубльовані значення, то потрібно перед іменем стовпчика, який є аргументом агрегатної функції помістити ключове слово DISTINCT, наприклад COUNT(DISTINCT kod). Слід зауважити, що не всі діалекти підтримують цю конструкцію. Скажімо Access не підтримує такого використання COUNT.

Стандарт ISO допускає використання ключового слова ALL (тобто всіх), що означає, що виключати дублі непотрібно. За змовчуванням, діє ALL.

Слід зауважити, що ключове слово DISTINCT в кожному запиті можна вказати не більше одного разу. Агрегатні функції можуть використовуватись тільки в списку фрази SELECT, або в складі HAVING. У всіх інших випадках використання агрегатних недопустимо. Якщо список у фразі SELECT є  агрегатні функції і в тексті запиту відсутня фраза GROUP BY, тоді список у фразі SELECT не повинен містити назви стовпчиків. Наприклад, такий запит

SELECT kod, SUM(suma)

FROM table_a

є некоректним, поскільки назва стовпчика kod не повинна зустрічатись у списку фрази SELECT.

Приклади використання агрегатних функцій. Запит

       SELECT COUNT(kod)

       FROM table_a

Поверне число 6 – кількість рядків table_a. Запит

       SELECT MAX(suma)

       FROM table_a

поверне максимальне значення стовпчика suma – 150. Запит

       SELECT MIN(suma) AS suma_min,AVG(suma) AS suma_avg

       FROM table_a

поверне таку таблицю результату:

де suma_min означає найменше значення стовпчика suma, а suma_avg середнє значення стовчика suma таблиці table_a. Запит

       SELECT MAX(suma),MIN(suma),AVG(suma)

       FROM table_a

       WHERE data<#15-1-2002#

поверне, відповідно, максимальне, мінімальне і середнє значення стовпчика suma для покупок, які були здійснені до 15-1-2002року. Це числа 150, 55 і 101,666666666667 відповідно.

Використання фрази GROUP BY

Наведені вище приклади підсумкових даних подібні підсумковим рядкам, які поміщаються в кінці звітів. Правда, досить часто у звітах вимагається формувати проміжні підсумки. Для цієї цілі в операторі SELECT вказується фраза GROUP BY. Стандарт ISO вимагає, щоб фрази SELECT і GROUP BY були тісно пов’язані між собою. При використанні в операторі SELECT фрази GROUP BY фраза SELECT повиненна містити тільки такі типи елементів:

• імена стовпчиків;

• агрегатні функції;

• костанти;

• вирази, що містять перечислені вище елементи.

Всі імена стовпчиків, які перераховані в списку SELECT’у повинні бути присутніми і у фразі GROUP BY.

На конкретних прикладах пояснимо як потрібно користуватись групуванням. Нагадаємо, що в таблиці table_a міститься інформація про закупки товарів. Якщо уважно подивитись, в цій таблиці є по декілька рядків, у яких значення стовпчика kod (код товару) повторюється, наприклад 1 зустрічається 3 рази. Група рядків, у яких kod=1, матиме вид:

Якщо потрібно дізнатись на яку суму закуплено кожного товару, необхідно сформувати такий запит:

       SELECT kod,SUM(suma) AS suma_tov

       FROM table_a

       GROUP BY kod

Результуюча таблиця цього запиту матиме вид:

Отже, оператор SELECT поступає таким чином, спочатку формує групи по значеннях стовпчиків, які перелічені у фразі GROUP BY (в нашому випадку одне поле kod), потім для кожної групи виконує агрегатну функцію і результат заносить у результуючу таблицю. Якби потрібно було крім сум вказати і кількість покупок кожного товару, запит був би таким:

SELECT kod,SUM(suma) AS suma_tov, COUNT(kod) AS kilkist_pokupok

       FROM table_a

       GROUP BY kod

Результуюча таблиця цього запиту матиме вид:

Якщо ж потрібно дізнатись на яку суму закуплено кожного товару до 20.1.2002року, необхідно сформувати такий запит:

       SELECT kod,SUM(suma) AS suma_tov

       FROM table_a

       WHERE data<#20/1/2002#

       GROUP BY kod

Результуюча таблиця цього запиту матиме вид:

Використання фрази HAVING

Фраза HAVING призначена для сумісного використання з фразою GROUP BY для обмеження відбору груп, які відбираються для результуючої таблиці запиту. Хоч фрази HAVING і WHERE мають схожий синтаксис, їх призначення різне. WHERE призначене для фільтрації рядків, які використовуються в тому числі для групування, фраза HAVING призначена для фільтрації груп, які заносяться в результуючу таблицю. На практиці предикат фрази HAVING повинен містить одну з агрегатних функцій. Наприклад, якщо потрібно дізнатись на яку суму закуплено кожного товару, причому сумарна вартість товару не первищувала б 200 грн.,необхідно сформувати такий запит:

       SELECT kod,SUM(suma) AS suma_tov

       FROM table_a

       GROUP BY kod

       HAVING SUM(suma)<=200

Результуюча таблиця цього запиту матиме вид:

Якщо потрібно сформувати список товарів (з включенням в результуючу таблицю коду товару, сумарну вартість цього товару і кількість покупок цього товару), середня вартість покупки товару перевищувала би 50, запит мав би вид:

SELECT kod,SUM(suma) AS suma_tov, COUNT(kod) AS kilkist_pokupok

       FROM table_a

       GROUP BY kod

       HAVING AVG(suma)>50

Результуюча таблиця цього запиту матиме вид:

Якщо ж до попереднього запиту додати умову: покупки здійснювались до 20.01.2002 року, запит матиме вид:

SELECT kod,SUM(suma) AS suma_tov, COUNT(kod) AS kilkist_pokupok

       FROM table_a

       WHERE data<#20/1/2002#

       GROUP BY kod

       HAVING AVG(suma)>50

Результуюча таблиця цього запиту матиме вид:

Підзапити

Звернімося до таблиць Vidomist(kod,year,month,D), tpp(d_l,d_r, p,c) i Osoba(kod, priz). Нагадаємо, що в таблиці Vidomist (kod,year,month,D – її поля) зберігається інформація про нараховану зарплату співробітників за певний рік і місяць. Таблиця tpp містить дані для обрахування податку на прибуток, а таблиця Osoba інформацію про персональний склад. Нехай нам потрібно сформувати список осіб з такими колонками: “ Прізвище ”,” Нараховано ”, які одержують нараховану зарплату вищу за середню середню нараховану зарплату за 2-й місяць 2002 року. Середню нараховану зарплату за 2-й місяць 2002 року визначить такий запит:

SELECT AVG(D)

FROM Vidomist

WHERE year=2002 AND mounth=2

Середня нарахована зарплата становить 500 грн.

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

SELECT a.priz AS Прізвище, b.D AS Нараховано

FROM (Osoba AS a INNER JOIN Vidomist AS b ON a.kod=b.kod)

WHERE b.year=2002 AND b.mounth=2 AND b.D> (SELECT AVG(D) FROM Vidomist WHERE year=2002 AND mounth=2)

Результуюча таблиця матиме вид:

Пояснимо цей SELECT-оператор. Запит

SELECT AVG(D)

FROM Vidomist

WHERE year=2002 AND mounth=2

який розташований у фразі WHERE першого SELECT’у (він взятий у дужки) називають підзапитом. На відміну від основного запиту, якого ще називають зовнішнім запитом, підзапит ще називають внутрішнім запитом. Цей підзапит знаходить середню нараховану зарплату. Потім запит відбирає ті рядки, в яких нарахована зарплата  перевищує число (в цьому конкретному випадку воно дорівнює 500), знайдене підзапитом.

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

SELECT a.priz AS Прізвище, b.D AS Нараховано, b.D-(SELECT AVG(D) FROM Vidomist WHERE year=2002 AND mounth=2) AS Перевищення

FROM (Osoba AS a INNER JOIN Vidomist AS b ON a.kod=b.kod)

WHERE b.year=2002 AND b.mounth=2 AND b.D> (SELECT AVG(D) FROM Vidomist WHERE year=2002 AND mounth=2)

Результуюча таблиця матиме вид:

Якщо ж середню величину зарплати обраховувати виходячи з суми, яку повинні видавати на руки (нарахована мінус податок на прибуток) і запит має повертати такі колонки: “ Прізвище ”,” Нараховано ”,” Сума податку ” і  “ До виплати ”, то запит матиме вид:

SELECT a.priz AS Прізвище, b.D AS Нараховано, (b.D-c.d_l)*c.p+c.c AS [Сума податку], b.D-((b.D-c.d_l)*c.p+c.c) AS [До виплати]

FROM (Osoba AS a INNER JOIN Vidomist AS b ON a.kod=b.kod) INNER JOIN tpp AS c ON (b.D>c.d_l) AND (b.D<=c.d_r)

WHERE b.year=2002 AND b.mounth=2 AND (b.D-((b.D-c.d_l)*c.p+c.c))> (SELECT AVG(b.D-((b.D-c.d_l)*c.p+c.c)) FROM Osoba a, Vidomist b, tpp c WHERE a.kod=b.kod AND (b.D>c.d_l) AND (b.D<=c.d_r) AND b.year=2002 AND b.mounth=2)

Результуюча таблиця матиме вид:

Підзапит цього запиту має вид:

SELECT AVG(b.D-((b.D-c.d_l)*c.p+c.c))

FROM Osoba a, Vidomist b, tpp c

WHERE a.kod=b.kod AND (b.D>c.d_l) AND (b.D<=c.d_r) AND b.year=2002 AND b.mounth=2

Підзапит може вказуватись безпосередньо після операторів зрівнювання (=, <, >, <=, >=, <>) у фразах WHERE i HAVING. Текст підзапиту потрібно брати в дужки. До підзапитів застосовують такі правила і обмеження.

1. В підзапитах не повинна використовуватись фраза ORDER BY, хоч вона може використовуватись у зовнішньому запиті.

2. За змовчуванням імена стовпчиків в підзапиті відносяться до таблиць, які вказані у фразі FROM підзапиту. Разом з тим допускається посилатись і на стовпчики таблиць, які вказані у фразі FROM зовнішнього запиту, для чого використовуються аліаси.

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

4. Якщо підзапит є одним з двох операндів оператора зрівнювання, то підзапит має повертати єдине значення.

Існує три типи підзапитів.

• Скалярний підзапит – це підзапит, який повертає єдине значення. У вищенаведених прикладах використовувався скалярний підзапит.

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

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

Ключові слова ANY i ALL

Ключові слова ANY i ALL використовуються лише з підзапитами, які повертають один стовпчик чисел. Якщо перед підзапитом стоятиме ключове слово ALL, умова зрівняння вважається істинною, якщо вона виконується для всіх значень в результуючому стовпчику підзапиту. Наприклад, запит

SELECT *

FROM Vidomist

WHERE D> ALL (SELECT D FROM Vidomist WHERE kod IN (1,2))

Результат наведено нижче

В данному конкретному випадку внутрішній запит

SELECT D FROM Vidomist WHERE kod IN (1,2)

відбере такі значення: {367.78,1309.55,400,500}.

Очевидно, умова D> ALL (SELECT D FROM Vidomist WHERE kod IN (1,2))

еквівалентна умові D> (SELECT MAX(D) FROM Vidomist WHERE kod IN (1,2)).

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

SELECT *

FROM Vidomist

WHERE D> (SELECT MAX(D) FROM Vidomist WHERE kod IN (1,2))

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

D> ANY(SELECT D FROM Vidomist WHERE kod IN (1,2))

істинна  тоді і тільки тоді коли вона істинна хоча б для одного значення, яке відбере внутрішній запит. Отже, любе D, яке більше 400, задовольнить нашій умові.

Результатом запиту

       SELECT *

       FROM Vidomist

       WHERE D> ANY (SELECT D FROM Vidomist WHERE kod IN (1,2))

буде таблиця:

Очевидно, цей запит буде еквівалентний  запиту

       SELECT *

       FROM Vidomist

       WHERE D> (SELECT MIN(D) FROM Vidomist WHERE kod IN (1,2))

Ключові слова EXISTS i NOT EXISTS

Ключові слова EXISTS i NOT EXIST використовуються лише з підзапитами. Результатом їх виконання є логічне значення “ІСТИНА” або “ХИБНЕ”. Для ключовогослова EXISTS результат дорівнює “ІСТИНА” в тому і лише в тому випадку, коли результуюча таблиця підзапиту містить хоча б один рядок. Якщо результуюча таблиця підзапиту пуста, результат обробки ключового слова EXISTS буде рівним “ХИБНЕ”. Для кючового слова NOT EXISTS використовуються правила обробки, протилежні по відношенню до ключового слова EXISTS. Поскільки по ключсовим словам перевіряється лише наявність рядків результуючої таблиці, ця таблиця може містити довільне число як рядків так і стовпчиків. Приклад використання ключового слова NOT EXISTS наведено вище

SELECT *

FROM Vidomist a

WHERE NOT EXISTS

(SELECT *

FROM Osoba

WHERE a.kod=b.kod)

Наведений запит поверне ті рядки з таблиці Vidomist, які не відповідають жодному рядку з таблиці Osoba, тобто є такими, що порушують цілісність посилань. Отже, якщо цей запит поверне пусту таблицю, цілісність посилань не порушена.

Комбінування результуючих таблиць (операції UNION, INTERSECT і EXCEPT)

В мові SQL можна використовувати звичні операції над множинами – об’єднання (UNION), перетин (INTERSECT) і різницю (difference), які дозволяють комбінувати результати виконання двох або більше запитів в єдину результуючу таблицю. На таблиці, до яких застосовують теоретико-множинні операції, накладаються певні обмеження. А саме:

• таблиці повинні мати однакову кількість стовпчиків;

• типи i-го стовпчика обох таблиць мають бути однаковими;

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

Наведемо приклад використання операції UNION. Потрібно сформувати відомість нарахованої зарплати за лютий місяць 2002 року з обовязковим включенням до списку всіх працівників незалежно від того нараховували їм зарплату чи ні. Відповідний запит матиме вид:

       SELECT a.priz AS Прізвище, b.D AS [Нарахована сума]

       FROM Osoba a, Vidomist b

       WHERE a.kod=b.kod AND b.year=2002 AND b.mounth=2

       UNION

       SELECT priz,NULL

       FROM Osoba

       WHERE NOT kod IN(SELECT kod

FROM Osoba a, Vidomist b

WHERE a.kod=b.kod AND b.year=2002 AND b.mounth=2)

Перший оператор SELECT (до UNION) сформує список працівників, яким у лютому 2002 року нарахували зарплату, а другий оператор SELECT (після UNION) сформує список працівників, яким не нарахували зарплату у лютому 2002 року.

Результуюча таблиця матиме вид:

Поновлення бази даних

Мова SQL є повнофункціональною мовою маніпулювання даними, яка може використовуватись не лише для виборок даних з бази, але і для внесення змін у базу даних. Оператори модифікації інформації в базі даних не настільки складні як оператор SELECT. Є три оператори модифікації даних в базі. Це

• INSERT – використовується для вставки нових даних у таблицю

• UPDATE - використовується для модифікації вже існуючої в таблиці інформації

• DELETE - використовується для видалення з таблиці рядку(ів) даних.

Вставка нових даних

Існує дві форми оператора INSERT. Перша використовується для вставки єдиного рядка в таблицю. Ця форма має такий формат:

INSERT INTO table_name [(column_list)]

VALUES (data_value_list)

Тут параметр table_name (Ім’я таблиці) означає або ім’я таблиці або ім’я представлення. Параметр column_list (Список стовпчиків) є списком імен одного або більше стовпчиків розділених комами. Параметр column_list не є обов’язковим. Якщо він відсутній, вважається, що використовується список всіх стовпчиків таблиці, причому імена стовпчиків розташовані в тому ж порядку в якому вони були задані в операторі CREATE TABLE (створення таблиці).

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

Параметр data_value_list (список значень) повинен наступним чином відповідати параметру column_list:

• кількість елементів в обох списках повинна бути однакова;

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

• типи даних елементів списку data_value_list повинні бути сумісними з типами даних відповідних стовпчиків з column_list.

Приклад використання конструкції INSERT…VALUES. Оператор:

INSERT INTO Osoba

VALUES(4,’Мельник’)

всталяє новий рядок в таблицю Osoba, в якому стовпчик kod приймає значення 4, а стовпчик priz – Мельник.

Якщо потрібно вставити новий рядок в таблицю з використанням лише деяких стовпчиків, потрібно в круглих дужках перерахувати через кому імена відповідних стовпчиків (параметр column_list). Наприклад, в table_a потрібно додати рядок, в якому kod=4, suma=245, а data невизначена. Оператор INSERT матиме вид:

       INSERT INTO table_a (kod,suma)

       VALUES(4,245)

Друга форма оператора INSERT дозволяє скопіювати множину рядків однієї таблиці в іншу. Цей оператор має такий формат:

       INSERT INTO table_name [(column_list)]

       SELECT …

Параметри table_name і column_list мають той же формат і зміст що й при вставці в таблицю одного рядка. Рядки, які вставляються в указану таблицю, є рядками результуючої таблиці, сформованої оператором SELECT. Всі наведені вище обмеження, що стосувались першої форми оператора INSERT, дійсні і в цьому випадку.

Наведемо приклад. Нехай нам потрібно формувати баланс по закупкам товарів за певний період і зберігати в новій таблиці balans. Таблиця balans має два стовпчики – kod - код товару і suma - загальна сума вартості цього товару.

Оператор INSERT, який поповнює таблицю balans сумарними даними з table_a за період з 1.1.2002 по 31.1.2002 року, матиме вид:

       INSERT INTO balans(kod,suma)

       SELECT kod, SUM(suma)

       FROM table_a

       WHERE data>=#1/1/2002# AND data<=#31/1/2002#

       GROUP BY kod

       ORDER BY kod

Результатом виконання цього оператора є таблиця balans:

Нижче наведено приклад застосування другої форми оператора INSERT, який поповнює таблицю Vidomist даними з тієї ж таблиці Vidomist. Припустімо, що в 3-му місяці зарплату кожного співробітника збільшили рівно в 1.2 рази по відношенню до зарплати в 2-му місяці. Відповідний оператор INSERT матиме вид:

       INSERT INTO Vidomist (kod,year,mounth,D)

       SELECT kod, year,3,D*1.2

       FROM Vidomist

       WHERE mounth=2

Після виконання цього оператора таблиця Vidomist матиме вид:

Модифікація даних (оператор UPDATE)

Оператор UPDATE змінює значення певних стовпчиків в заданій таблиці. Цей оператор має формат:

UPDATE table_name

SET column_name1=data_value1 [,column_name2=data_value2 ...]

[WHERE  предикат]

Параметр table_name визначає і’мя таблиці бази даних, параметри column_name1, column_name2, ... визначають імена стовпчиків, значення яких потрібно замінити новими значеннями - data_value1, data_value2, ... відповідно.

Фраза WHERE  предикат необов’язкова. Якщо вона опущена, значення стовпчиків будуть змінені в усіх рядках таблиці. Якщо ж фраза WHERE присутня, то зміни відбудуться лише в тих рядках, які задовольняють умові відбору. Наприклад, оператор

       UPDATE Vidomist

       SET D=D*1.05

       WHERE mounth=2

збільшує зарплату всім співробітникам за 2-й місяць.

Приклад використання підзапиту в операторі UPDATE. Небхідно зарплату Сидорова за 3-й місяць збільшити на 100 гривень. Оператор UPDATE матиме вид:

       UPDATE Vidomist

       SET D = D+100

WHERE mounth=2 AND kod=(SELECT kod FROM Osoba WHERE PRIZ='Сидоров')

Використання INNER JOIN, LEFT JOIN в операторі UPDATE

Вище згадувалась таблиця balans, в якій зберігалась сумарні по видам товарів витрати на закупівлю товару за певний період. Якщо нам потрібно дізнатись власне баланс по кожній групі товарів(вартість закупки мінус вартість продажу), нам потрібно застосувати такий оператор UPDATE:

       UPDATE balans a INNER JOIN table_b b ON a.kod=b.kod

       SET a.suma=a.suma-b.suma

       WHERE b.data >=#1/1/2002# AND b.data<=#31/1/2002#

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

balans a INNER JOIN table_b b ON a.kod=b.kod

задає умову з’єднання таблиць balans i table_b. Оператор UPDATE для кожного рядка таблиці balans послідовно рядок за рядком проглядає таблицю table_b і для рядків для яких виконується умова

a.kod=b.kod

виконує операцію, яка вказана у фразі SET , тобто заміщує старе значення a.suma новим, а саме (a.suma- b.suma). В нашому випадку в таблиці table_b для першого рядка {1,195} таблиці balans є 2 рядки, в яких поле kod=1, а саме {1,50} і {1,47}. Отже, спочатку UPDATE замінить 195 на 195-47=98, а потім 145 замінить на 145-47=98. В результаті, перший рядок таблиці balans матиме вид {1,98}. Аналогічним чином оператор UPDATE поступає для другого рядка таблиці balans і т.д. Після виконання оператора UPDATE, таблиця balans матиме вид:

Зауваження. Не у всіх діалектах мови SQL підтримується використання конструкції INNER JOIN в операторі UPDATE.

Видалення даних з бази(оператор DELETE)

Оператор DELETE використовують для видалення даних з певної таблиці. Цей оператор має такий формат:

       DELETE FROM table_name

       [WHERE предикат]

Параметр table_name визначає ім’я таблиці. Фраза WHERE необовязкова. Якщо вона відсутня, будуть видалені всі рядки з таблиці. Якщо фраза WHERE присутня, будуть видалені тільки ті рядки з таблиці, для яких предикат істинний.

Наведемо приклад застосування оператора DELETE. Необхідно видалити з таблиці Vidomist рядки з найбільшою і найменшою сумою нарахованої зарплати. Оператор DELETE, який вирішує поставлену задачу матиме вид:

DELETE FROM Vidomist

WHERE (D=(Select MAX(D) FROM Vidomist)) OR (D=(Select MIN(D)

FROM Vidomist))

Результуюча таблиця матиме вид:

Визначення даних

Мова DDL (Data Definition Language), як частина мови SQL, дозволяє створювати і видаляти різні об’єкти бази даних – наприклад, таблиці, представлення, індекси тощо. Основними операторами мови SQL, призначеними для визначення даних, є:

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

В різних СУБД процедура створення баз даних різна. В клієнт-серверних системах право створення баз даних як правило закріплене за адміністратором бази даних (АБД). В персональних системах база даних створюється в процесі інсталяції і настройки параметрів самої СУБД. Стандарт ISO не визначає процедури створенння екземпляру бази даних, тому в кожному з діалектів мови SQL використовується свій власний підхід. Наприклад, в СУБД ORACLE створення екземпляру бази є частиною процесу інсталяції системи.

У відповідності із  стандартом ISO, таблиці та інші об’єкти бази даних існують в деякому середовищі(environment). Крім того, кожне середовище містить в собі один або більше каталогів (catalog), а кожний каталог – набір схем (shema). Схема – це поіменована сукупність взаємопов’язаних об’єктів бази даних ( всі об’єкти бази даних повинні бути описані в тій чи іншій схемі). Об’єктами схеми можуть бути таблиці, представленння, домени і набори символів. Стандарт залишає право вибору конкретного механізму створення і знищення каталогів за розробником СУБД, але регламентує механізм створення і знищення схем. Оператор визначенння схеми має такий формат:

CREATE SHEMA [name | AUTHORIZATION creator-identifier]

Таким чином, якщо творцем схеми під іменем sql_test буде користувач Ivanov, то даний оператор виглядатиме таким чином:

CREATE SHEMA sql_test AUTHORIZATION Ivanov

Схема видаляється з допомогою оператора DROP SHEMA, який має такий формат:

DROP SHEMA name [RESTRICT | CASCADE]

Якщо вказано ключове слово RESTRICT (саме воно діє за змовчуванням), схема повинна бути пустою(не містити жодного об’єкту), інакше виконання цієї операції буде відмінено. Якщо вказано ключове слово CASCADE, автоматично будукть видалені всі повязані з цією схемою об’єкти. Якщо ж в ході виконання цього оператора якась операція закінчилась невдало, буде відмінено виконання всього оператора DROP SHEMA.

На сьогоднішній день оператори CREATE SHEMA і DROP SHEMA реалізовані в небагатьох СУБД.

Створенння таблиць (оператор CREATE TABLE)

Після створення загальної структури бази даних можна приступати до створення таблиць, які представляють відношення, що входять до складу проекту бази даних. Для цього використовується оператор CREATE TABLE (спрощений варіант), який має формат:

CREATE TABLE table_name

(column_name data_type [NULL | NOT NULL][,…])

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

Ключове слово NULL визначає, що дане поле може містити значення NULL. Ключове слово NOT NULL визначає, що дане поле не може містити значення NULL. За змовчуванням, стандарт ISO визначає, що діє ключове слово NULL.

Стовпчики первинних ключів завжди визначаються з ключовим словом NOT NULL.

Приклад використання оператора CREATE TABLE. Оператор

CREATE TABLE Osoba (

kod INTEGER NOT NULL,

priz VARCHAR(50) NOT NULL)

створить таблицю Osoba, в якій є 2 стовпчики – kod i priz. Поле kod має цілочисельний тип, а поле priz має тип VARCHAR, тобто символьний і яке містить не більше 50 символів. Оператор, який створить таблицю table_a, якою ми користувались на попередніх лекціях, матиме вид

CREATE TABLE table_a(

kod INTEGER NOT NULL,

suma DECIMAL(8,2) NOT NULL,

data DATE )

В стандарті ISO наведено декілька варіантів визначення цього оператора. Базовий формат цього оператора має вид:

       CREATE TABLE table_name

       {(column_name data_type [NOT NULL] [UNIQUE]

       [DEFAULT default_option] [CHECK (search_condition)] [,…]}

       [PRIMARY KEY (list_of_columns),]

       {[UNIQUE (list_of_columns)] [,…]}

       {FOREIGN KEY (list_of_foreign_key_columns)

       REFERENCES parent_table_name [list_of_candidate_key_columns)],

            [MATCH {partial | FULL}]

            [ON UPDATE referential_action]

            [ON DELETE referential_action] [,…]}

       {[CHECK (searh_condition)] [,…])}

Дана версія оператора CREATE TABLE містить не тільки визначення данних (параметри table_name, column_name, data_type і специфікатор NOT NULL, які ми розглядали у спрощеному варіанті) але й засоби визначення цілісності бази даних, ключів і таке інше.

Необовязкова фраза DEFAULT задає значення - default_option, яке буде вставлятись оператором INSERT для невизначеного значення даного стовпчика

(default_option може бути літералом).

Необовязкова фраза UNIQUE задає унікальність значення цього поля, тобто не повинно існувати двох і більше рядків в таблиці з одноковим значення цього поля. Для стовпчиків, які вказані у фразі UNIQUE повинен застосовуватись режим NOT NULL для їх можливих значень. Система заблокує виконання любого оператора INSERT аба UPDATE який створює рядок з дубльованими значеннями ствпчиків, які вказані у фразі UNIQUE.

Необов’язкова фраза CHECK (search_condition). Кожний стовпчик має власний домен, тобто набір допустимих значень. Наприклад, для визначення наукового ступеня співробітника достатньо всього 3 значення – не має, кадидат і доктор. Тому домен для поля Naukov_stupin можна визначити як набір з 3-х значень- ‘не має’, ‘кадидат’,  ‘доктор’. Фраза

CHECK (Naukov_stupin IN(‘не має’, ‘кадидат’,  ‘доктор’))

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

Всі інші фрази оператора CREATE TABLE, що залишились називають обмеженнями для таблиці і можуть відокремлюватись (не обовязково) фразою

CONSTRAINT constraint_name.

Використанння цієї фрази дозволить в подальшому відмінити те чи інше обмеження за допомогою оператора ALTER TABLE.

Фраза PRIMARY KEY визначає стовпчик або стовпчики, які утворюють первинний ключ таблиці. Визначення любої таблиці повинно містити не більше однієї фрази PRIMARY KEY. Система заблокує виконання любого оператора INSERT аба UPDATE який створює рядок з дубльованими значеннями в стовпчиках, вказаних у фразі PRIMARY KEY. Наприклад, якщо S₁ i S₂ – стовпчики, які вказані у фразі PRIMARY KEY, і в таблиці вже є рядок зі значеннями a i b у цих стовпчиках відповідно, то спроба вставити новий рядок зі значеннями a i b у цих стовпчиках буде заблокована.

Фраза FOREIGN KEY дозволяє визначити зовнішні ключі таблиці. Фраза містить такі елементи:

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

• фразу REFERENCES, яка визначає батьківську таблицю з іменем parent_table_name, яка містить потенційний ключ, поля якого визначені зовнішнім ключем (задаються списком list_of_candidate_key_columns);

• необовязкову фразу ON UPDATE для виначення правила поновлення даних, які задаються параметром referential_action. Цей параметр може приймати одне з таких значеннь: CASCADE, SET NULL, SET DEFAULT, NO ACTION. Якщо фраза ON UPDATE опущена, за змовчуванням використовується правило NO ACTION;

• необовязкову фразу ON DELETE для виначення правила поновлення даних, які задаються параметром referential_action. Цей параметр може приймати одне з таких значеннь: CASCADE, SET NULL, SET DEFAULT, NO ACTION. Якщо фраза ON DELETE опущена, за змовчуванням використовується правило NO ACTION.

Правила підтримки цілісності посилань, які задаються у фразі FOREIGN KEY після ON UPDATE і ON DELETE:

• CASCADE – виконується поновлення/видалення рядка з батьківської таблиці з автоматичним поновленням/видаленням всіх рядків дочірньої таблиці, повязаних з даним рядком батьківської таблиці;

• SET NULL – виконується видалення рядка з батьківської таблиці, а у зовнішні ключі дочірньої таблиці, повязаних з даним рядком батьківської таблиці заноситься значенння NULL. Цей варіант застосовується лише для тих стовпчиків зовнішнього ключа, у визначенні яких задано NULL;

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

• NO ACTION операція видалення рядка з батьківської таблиці відміняється. Це значення використовується за змовчуванням.

Приклади. Оператор

CREATE TABLE Vidom(

kod INTEGER NOT NULL,

year SMALLINT NOT NULL,

mounth SMALLINT NOT NULL,

D REAL NOT NULL DEFAULT 0 ,

CONSTRAINT ind PRIMARY KEY (kod,year, mounth))

створить таблицю з назвою Vidom, яка матиме стовпчики - kod, year, mounth, D і первинний ключ з іменем ind, який визначається стовпчиками kod,year, mounth. Причому поля kod,year, mounth, D не можуть приймати значення NULL, а поле D за змовчуванням прийматиме значення 0, тобто оператор INSERT, що не містить явного задання значення поля D, автоматично підставлятиме значення 0. Приклад використання CHECK для недопущення вводу помилкових значень.

       CREATE TABLE Osoba

       (ID_OSOBA INTEGER NOT NULL,

       PRIZ VARCHAR(50) NOT NULL,

       NAME VARCHAR(50) NOT NULL,

       POBAT VARCHAR(50) NOT NULL,

       SEX CHAR(4) CHECK (SEX IN (‘жін.’,’чол.’)),

       ID_FACULT SMALLINT NOT NULL)

Цей оператор створить таблицю Osoba з відповідними стовпчиками, причому в стовпчик SEX система не дозволить ввести нічого крім значень ‘жін.’і ’чол.’.

Слід зауважити, що не всі діалекти SQL підтримують фразу DEFAULT (Access 97 не підтримує цієї фрази).

Видалення таблиць (оператор DROP TABLE)

Непотрібні таблиці видаляються з бази з допомогою оператора DROP TABLE, який має формат:

DROP TABLE table_name [RESTRICT | CASCADE]

Наприклад, для видалення таблиці Vidomist з бази необхідно застосувати оператор:

DROP TABLE Vidomist

Якщо в операторі вказано ключове слово RESTRICT  і в базі існує хоча б один об’єкт, який пов’язаний з таблицею table_name (наприклад, VIEW в якому використовується ця таблиця), операція видалення буде відхиленна. Якщо в операторі вказано ключове слово CASCADE, автоматично буде видалена таблиця table_name і всі об’єкти, які пов’язані з цією таблицею.

Створенння індексу (оператор CREATE INDEX)

Індекс – це обєкт бази даних, з допомогою якого істотньо збільшується швидкість пошуку даних в таблицях. Створення індексів не передбачено стандртом, але більшість розробників СУБД підтримують такий оператор:

       CREATE [UNIQUE] INDEX index_name

       ON table_name(column [ASC | DESC][,…])

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

CREATE  INDEX ind_osoba ON Osoba (kod ASC)

створює індекс ind_osoba по полю kod у висхідному порядку для таблиці Osoba.

Якщо в операторі вказано ключове слово UNIQUE, автоматично буде підтримуватись унікальність значень ключа. Тобто СУБД не дозволить існування двох рядків з одинаковим значенням індексу. В нашому прикладі це означає, що СУБД не допустить двох і більше рядків у таблиці Osoba з одннаковим кодом.

Видалення індексу (оператор DROP INDEX)

Оператор DROP INDEX виддаляє з бази створений раніше індекс. Він має формат:

DROP INDEX index_name

Наприклад, оператор

DROP INDEX ind_osoba

видаляє з бази створений раніше індекс ind_osoba для таблиці Osoba.

Модифікація структури таблиці (оператор ALTER TABLE)

Для зміни структури існуючої тавблиці стандартом ISO передбачено оператор ALTER TABLE. Визначення цього оператора містить шість параметрів для виконання таких операцій:

• вставка в таблицю нового стовпчика;

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

• видалення з визначення таблиці існуючого обмеження;

• визначення для зазначеного стовпчика значення за змовчуванням;

• відміна встановленого значення за змовчуванням для даного стовпчика.

Оператор ALTER TABLE має такий формат:

ALTER TABLE table_name

[ADD [COLUMN] column_name data_type [NOT NULL] [UNIQUE]

[DEFAULT default_option] [CHECK (search_condition)]]

[DROP [COLUMN] column_name [RESTRICT | CASCADE]]

[ADD [CONSTRAINT [constrain_name]] table_constraint_definition]

[DROP CONSTRAINT  constrain_name [RESTRICT | CASCADE]]

[ALTER [COLUMN] SET DEFAULT default_option]

[ALTER [COLUMN] DROP DEFAULT]

Тут параметри мають ті ж призначення, що і в операторі CREATE TABLE. Параметр table_constraint_definition може приймати одне з таких значень: PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK. У фразі DROP COLUMN column_name  задається ім’я стовпчика, який видаляється з таблиці. Кваліфікатор RESTRICT означає, що якщо на даний стовпчик посилається який небудь інший об’єкт бази, він з таблиці не видалиться; кваліфікатор  CASCADE означає, що крім стовпчика вказаної таблиці будуть видалені всі посилання на данний стовпчик в базі. За змовчуванням діє кваліфікатор RESTRICT.

Оператор ALTER TABLE реалізовано не у всіх діалектах SQL. В деяких діалектах підтримується урізаний варіант цього оператора (не дозволяється видаляти вже існуючі стовпчики).

Використання транзакцій

Стандарт ISO визначає модель транзакцій, яка використовує оператори COMMIT і ROLLBACK. Біільшість (але не всі) комерційних реалізацій мови SQL підтримують цю модель, яка вперше була реалізована в СУБД DB2 корпорації IBM. Транзакцією називається логічна одиниця роботи, що складається з одного або більше SQL-операторів, яка обробляється системою як єдина і неподільна дія. В стандарті вказується, що в мові SQL транзакція автоматично запускається любим  ініціалізуючим транзакцію SQL-оператором (наприклад, SELECT, INSERT, UPDATE). Зміни, що внесені в базу даних в ході виконання даної транзакції, не будуть видимі для любих інших транзакцій, що виконуються паралельно, до тих пір поки ця транзакція не буде завершена. Завершення транзакції може бути виконано одним з наступних способів:

• Ввод оператора COMMIT означає успішне завершення транзакції. Після його виконання внесені в базу зміни набувають постійного характеру. Після обробки оператора COMMIT ввод любого ініціадізуючого транзакцію оператора автоматично викличе запуск нової транзакції.

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

• При внесенні SQL-оператора в текст програми (мається на увазі вбудований SQL) успішне завершення її роботи автоматично викличе завершення останньої запущеної програмою транзакції навіть у випадку відсутності оператора COMMIT.

• При внесенні SQL-оператора в текст програми, що аврійно завершила свою роботу автоматично викличе відкат останньої транзакції.

В мові SQL заборонено використовувати вкладені транзакції. За допомогою оператора SET TRANSACTION користувачі мають змогу налаштовувати процедуру обробки транзакій. Основний формат цього оператора має вид:

SET TRANSACTION

[READ ONLY | READ WRITE] |

[ISOLATION LEVEL READ UNCOMMITTED | READ COMMITTED |

REPEATABLE READ | SERIALIZED]

Кваліфікатори READ ONLY і READ WRITE вказують, що в транзакціях допускається лише виконання операцій читання або читання і записування відповідно. За змовчуванням використовується кваліфікатор READ WRITE (якщо тільки не вибрано рівень ізоляції READ UNCOMMITTED). Паказник рівня ізоляції визначає степінь взаємодії з іншими транзакціями.

Властивості транзакцій. Існують деякі властивості, які властиві кожній транзакції. Нижче наведено чотири основних властивостей.

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

• Узгодженність. Кожна транзакція повинна переводити базу даних з одного узгодженого стану в інший узгоджений стан.

• Ізольованість. Всі транзакції виконуються незалежно одна від одної. Тобто, проміжні результати незавершеної транзакції недоступні для інших транзакцій.

• Довготривалість. Результати успішно завершених (зафіксованих) транзакцій зберігаються в базі постійно і не повинні бути втраченими в результаті наступних аварійних ситуацій.

Управління доступом даних

Для організації захисту інформації в базі даних в мові SQL передбачені оператори GRANT i REVOKE. Механізм захисту побудований на використанні ідентифікаторів користувачів, які надають ним відповідні права і привілеї. Ідентифікатором користувача називається звичний ідентифікатор мови SQL (допустима послідовність символів) для ідентифікації певного користувача бази даних. Кожному користувачеві бази адміністратор бази даних (АБД) назначає певний ідентифікатор, який повязаний з певним паролем. Кожний SQL-оператор виконується від імені деякого користувача. Ідентифікатор користувача вказує на обєкти бази даних з якими даний користувач може працювати а також які операції доступні для даного користувача.

Кожний створений в середовищі SQL об’єкт має свого господаря. Господар задається ідентифікатором користувача, визначеному у фразі AUTORIZATION тієї ж схеми, якій належить даний об’єкт.

Привілеї. Привілеями називають дії, які користувач має право застосовувати до таблиці бази даних або до представлення. В стандарті ISO визначені такі привілеї:

• SELECT – право вибирати дані з таблиць;

• INSERT – право втавляти в таблицю нові рядки;

• UPDATE – право змінювати дані в таблиці;

• DELETE – право видаляти рядки з таблиці;

• REFFERENCES – право посилатись на стовпчики вказаної таблиці.

Привілеї INSERT і UPDATE можуть застосовуватись і для окремих стовпчиків таблиць. Це означатиме, що користувач матиме можливість вносити зміни лише у вказані стовпчики зазначених таблиць. Якщо користувач з допомогою оператора CREATE TABLE створює нову таблицю, він автоматично стає її господарем і має повний набір привілеїв по відношенню до цієї таблиці.

Надання привілеїв іншим користувачам (оператор GRANT)

Кожний користувач в середовищі SQL має спеціальний ідентифікатор (послідовність символів), який називають ідентифікатором користувача (authorization ID). В багатокористувацьких системах є процедура входу в систему, у відповідності з якою здійснюється доступ до бази даних. Ця процедура визначає ID, який пов’язаний з даним користувачем. Кожний користувач має свій ID, хоча для різних користувачів допускається використання однакових ID. Кожний користвач має свій набір привілеїв, тобто множину тих дій, які йому дозволено виконувати (вхід в систему це мінімальний привілей). Привілеї з часом можуть змінюватись: нові привілеї назначатись – старі відмінятись. Привілей дається конкретному користувачеві для окремої таблиці або представлення. Користувач, який створив дану таблицю, має всі права на неї. Він також може передати частину з них іншому користувачеві.

Наведемо стандартні привілеї, які може назначити користувач:

• SELECT – користувач з цим привілеєм може виконувати запити для цієї таблиці;

• INSERT - користувач з цим привілеєм може виконувати оператор INSERT для цієї таблиці;

• UPDATE - користувач з цим привілеєм може виконувати оператор UPDATE для цієї таблиці. Привілей обмежується множиною стовпчиків цієї таблиці;

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

• REFERENCES - користувач з цим привілеєм може визначити зовнішній ключ, який використовує один або декілька стовпчиків.

Механізм SQL дозволяє виконати призначення користувачам цих привілеїв з допомогою оператора GRANT. Оператор GRANT має такий формат:

GRANT {privileges_list | ALL PRIVILEGES}

ON object_name

TO {autorization_id_list | PUBLIC}

[WITH GRANT OPTION]

Параметр privileges_list задає список, що містить один або більше стандартних привілеїв розділених комами. Крім того, для зручності, введено ключове слово ALL PRIVILEGES, що означає надання всіх привілеїв.

Параметр object_name задає ім’я об’єкту бази даних (таблиці, представлення, домена, набору символів).

Фраза WITH GRANT OPTION є необовязковою. З її допомогою задають всім вказаним у списку autorization_id_list  користувачам передавать іншим користувачам всі надані їм привілеї. Якщо ця фраза опущена, одержувач привілеїв не зможе передавать свої права іншим користувачам.

Приклади. Надання всіх привілеїв щодо таблиці Osoba користувачеві Stud.

GRANT ALL PRIVILEGES

ON Osoba

TO Stud WITH GRANT OPTION

В результаті користувач з ідентифікатором Stud одержить право на модифікацію даних (вставлять, видалять а також поновлювати рядки) в таблиці Osoba а також вибирати дані з цієї таблиці. Оскільки в цьому операторі присутня фраза WITH GRANT OPTION, користувач Stud може передавати всі або частину своїх прав іншим користувачам.

Наступний приклад показує як надати користувачеві з ідентифікатором Admin привілеї SELECT і UPDATE  для стовпчика PRIZ таблиці Osoba.

GRANT SELECT,UPDATE(PRIZ)

ON Osoba

TO Admin

Оскільки фраза WITH GRANT OPTION відсутня, користувач Admin не зможе передати свої права іншим користувачам.

Наступний приклад показує як надати декільком користувачам з ідентифікаторами Admin і Stud привілею SELECT для таблиці Osoba.     

GRANT SELECT

ON Osoba

TO Admin, Stud

Приклад надання привілею SELECT всім користувачам:

GRANT SELECT

ON Osoba

TO PUBLIC

Використання ключового слова PUBLIC означає, що всі користувачі(які вже зареєстровані в базі, так і майбутні) одержать право використання таблиці Osoba в операторі SELECT.

Відміна наданих привілеїв (оператор REVOKE)

Для відміни наданих користувачам привілеїв використовують оператор REVOKE. Він має такий формат:

REVOKE [GRANT OPTION FOR] {privilege_list | ALL PRIVILEGES}

ON object_name

FROM { autorization_id_list | PUBLIC} [RESTRICT | CASCSADE]

Ключове слово ALL PRIVILEGES означає відміну всіх привілеїв для вказаного користувача, які надані йому раніше.

Наприклад, якщо потрібно відмінити привілей на оператор SELECT для таблиці Osoba, наданий всім користувачам в попередньму прикладі, потрібно застосувати такий оператор REVOKE:

REVOKE SELECT

ON Osoba

FROM PUBLIC

Якщо потрібно відмінити привілей на оператор SELECT для таблиці Osoba, наданий користувачеві з ідентифікатором Stud, необхідно застосувати такий оператор REVOKE:

REVOKE SELECT

ON Osoba

FROM Stud.

З повагою ІЦ “KURSOVIKS”!