18232

SQL – абревіатура від Structured Query Language (структурована мова запитів)

Лекция

Информатика, кибернетика и программирование

SQL SQL – абревіатура від Structured Query Language структурована мова запитів. Мова SQL – найбільш поширена мова запитів для реляційних баз даних. Її перші версії називались SEQUEL тому часом SQL називають сіквел але більш правильно його називати еск’юел. Однією з головних переваг мо...

Украинкский

2013-07-07

231.5 KB

21 чел.

SQL

SQL – абревіатура від Structured Query Language (структурована мова запитів).

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

SQL -  це мова запитів, що використовується у більшості сучасних реляційних СУБД. Вона не належить до категорії системних або програмних мов. Це множинно-орієнтована функціональна (а не  процедурних мова), тому вона не містить засобів керування програмними потоками ( розгалужень та операторів циклу ) та засобів організації інтерфейсу, наприклад, створення форм або друкування звітів. Не процедурна мова – це мова, в якій описується, що потрібно одержати, а не як це одержати.

SQL надає такі можливості:

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

Відповідно конструкції мови при вивченні корисно розділити на групи (або підмови):

  •  мова запитів даних використовується для отримання інформації з таблиць (наприклад, команда SELECT)
  •  мова маніпулювання даними для вставки, видалення рядків, зміни окремих стовпців всередині рядків (наприклад, INSERT, DELETE, UPDATE)
  •  мова обробки транзакцій для об’єднання декількох команд мови маніпулювання даними (наприклад, BEGIN TRAN[SACTION], COMMIT [TRAN[SACTION] WORK], ROLLBACK [TRAN[SACTION] WORK]); якщо відбувся збій при виконанні команд ММД, всі попередні операції відміняються
  •  мова опису даних, тобто структури таблиць і представлень, які створюються чи змінюються (наприклад, CREATE ALTER TABLE,  ADD  ALTER COLUMN,  CREATE ALTER VIEW)
  •  мова керування курсором для виділення 1 (чи поточного) рядка результуючого набору записів
  •  мова керування даними для виконання адміністраторських функцій.

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

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

  1.  Знайти всі відомості про всіх постачальників.

SELECT *

FROM П;

Запит формулюється за допомогою команди SELECT, яка складається з двох фраз:

  •  фраза FROM вказує, з якої таблиці потрібно вибрати дані, у нашому випадку – з таблиці П;
  •  фраза SELECT вказує, які саме атрибути (стовпці) з цієї таблиці мають бути вибрані, у нашому випадку – всі.

  1.  Знайти номери постачальників з статусом, більш 20, що живуть у місті N.

SELECT КП

FROM П

WHERE місто = ‘NAND статус > 20;

Для запису умови вибору використовується фраза WHERE. Алгоритм обробки запиту з фразою WHERE такий:

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

У цьому прикладі з таблиці П вибираються рядки, що містять інформацію про постачальників,  які мають атрибути місто ‘N’ та статус більше 20. Результатом запиту є

стовпчик КП отриманого відношення.

  1.  Знайти прізвища постачальників, які постачають деталь Д1.

SELECT Прізвище

FROM П

WHERE КП IN

(SELECT КП

FROM ОПД

WHERE КД = Д1’);

В фразі WHERE використовується оператор IN, який задає перевірку на належність елемента множині. Множина задана незалежним підзапитом.

У прикладі внутрішній запит визначає множину КП з таблиці ОПД , для яких КД = Д1.

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

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

SELECT Прізвище

FROM П

WHERE КП IN

SELECT КП

FROM ОПД

WHERE КД IN

 SELECT КД

FROM Д

WHERE колір = ‘червоний’;

Спочатку з таблиці Д вибираються коди деталей (КД), колір яких червоний, потім - з таблиці ОПД вибираються КП таких деталей, і, нарешті, з таблиці П – прізвища постачальників, які роблять ці поставки.

 

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

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

SELECT Прізвище

FROM П

WHERE КП IN

SELECT КП

FROM ОПД

WHERE КД IN

 SELECT КД

FROM ОПД

WHERE КП IN

SELECT КП

FROM П

WHERE прізвище = ‘Іванчук’;

Як і в попередніх прикладах, запити обробляються « знизу вверх»: з таблиці П вибираються КП, прізвище яких ‘Іванчук’, далі - з таблиці поставок ОПД коди деталей КД, які поставляють ці постачальники, далі - з таблиці ОПД коди поставок КП,  якими поставляються ці деталі, і, нарешті, з таблиці П – прізвища постачальників, які роблять ці поставки.

  1.  Знайти прізвища постачальників, які не постачають деталь Д1.

SELECT Прізвище

FROM П

WHERE ‘Д1’ NOT IN

SELECT КД

FROM ОПД

WHERE КП = П.КП;

NOT IN – оператор перевірки на неналежність елемента множині.

  1.  Знайти номери тих деталей, що постачаються кількома постачальниками.

SELECT DISTINCT КД

FROM ОПД X

WHERE КД IN

SELECT КД

FROM ОПД

WHERE КП ¹ X.КП;

У фразі FROM поряд з найменуванням таблиці можна явно вказувати ще одне ім’я ( alias).

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

 

DISTINCT – модифікатор, що дозволяє вилучити повторювані значення. В фразі FROM з таблицею ОПД зв’язується псевдонім Х, за яким виконується посилання на цю таблицю в під запиті. У фразі WHERE використовується оператор IN, який задає перевірку на належність елемента множині тих кодів деталей, постачальник яких не співпадає з постачальником деталі з  рядка таблиці ОПД, що розглядається в головному запиті.

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

SELECT DISTINCT КД, місто

FROM ОПД, П

WHERE ОПД.КП = П.КП;

В цьому запиті дані вибираються з двох таблиць: ОПД і П.

У кількох наступних запитах ( вони позначені * ) використані такі можливості, як порівняння множин. Засоби для таких дій були лише у ранніх версіях мови SEQUEL. У сучасних реалізаціях мови SQL їх можна тільки моделювати. З методичних міркувань ми спочатку розглянемо приклади у контексті класичного SEQUEL, а пізніше будуть наведені способи їх моделювання відповідно до сучасних можливостей.

  1.  * Знайти прізвища постачальників, які постачають всі деталі.

SELECT прізв

FROM П

WHERE  (SELECT КД

FROM ОПД

WHERE КП = П.КП) =

(SELECT КД

FROM Д);

Демонструється запит з корельованим підзапитом. При цьому:

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

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

  1.  * Знайти прізвища постачальників, які постачають принаймні всі ті деталі, що і постачальник з номером П127.

SELECT Прізвище

FROM П

WHERE КП IN

 SELECT КП

 FROM ОПД X

 WHERE  (SELECT КД

FROM ОПД

WHERE КП = X.КП) CONTAINS

(SELECT КД

FROM ОПД

WHERE КП = ‘127’);

У цьому запиті за допомогою оператора  CONTAINS  (тільки SEQUEL ) перевіряється, чи множина деталей, які постачає постачальник, визначений в зовнішньому підзапиті, містить множину деталей, які постачає постачальник з номером 127.

SEQUEL 2:

SELECT прізв

FROM П

WHERE КП IN

SELECT КП

 FROM ОПД

GROUP BY КП

 HAVING SET(КД) CONTAINS

(SELECT КД

FROM ОПД 

WHERE КП = ‘127’);

В цьому варіанті запиту фраза GROUP BY дає змогу поділити множину рядків таблиці ОПД  на групи за ознакою рівності значень в стовпці КП.

Фраза HAVING грає таку ж роль для груп, що і фраза WHERE для рядків: вона дозволяє задавати умови на групах рядків, формованих фразою GROUP BY. Ця фраза включається в речення лише при наявності фрази GROUP BY, а вирази в HAVING повинні приймати єдине значення для групи. У нашому підзапиті  вибираються ті групи поставок, для яких множини деталей містять множини деталей, які постачає постачальник з кодом 127.

В SQL використовуються  теоретико-множинної операції UNION, INTERSECT, EXCEPT (MINUS) (об’єднати, перетнути, відняти множини). Аргументами цих операторів є таблиці, що повинні бути сумісними за кількістю стовпців і типом даних.

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

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

(SELECT КП

FROM П) EXCEPT

(SELECT КП

 FROM ОПД);

В цьому прикладі продемонстровано використання теоретико-множинної операції EXCEPT. Результатом є множина кодів постачальників за виключенням  кодів постачальників, які що-небудь постачають.

  1.  * Знайти коди постачальників, що постачають деталь Д1 та Д2.

SELECT DISTINCT  КП

FROM ОПД

GROUP BY ОПД.КП

HAVING SET(ОПД.КД) CONTAINS (‘Д1’, ‘Д2’);

  1.  * Знайти коди постачальників, що мають такі ж значення статусу і міста, що і постачальник П127.

SELECT КП

FROM П

WHERE <статус, місто > IN

 SELECT статус, місто

FROM П

WHERE КП = ‘П127’;

В цьому прикладі використовується оператор порівняння IN для визначення належності  статусу і міста певного постачальника множині, що складається з однієї пари <статус, місто > постачальника П127.

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

  1.  Знайти загальну кількість постачальників.

SELECT COUNT(*) FROM П;

  1.  Знайти загальну кількість деталей з номером Д123.

SELECT SUM(кількість)

FROM ОПД

WHERE КД = Д123;

  1.  Знайти прізвище постачальників з найбільшим статусом.

SELECT Прізвище

FROM П

WHERE статус = SELECT MAX(статус) FROM П;

  1.  Для кожної деталі, що постачається, знайти її номер і кількість постачальників.

SELECT КД, COUNT(КП)

FROM ОПД GROUP BY КД;

  1.  Знайти номери деталей, які постачаються більше ніж одним постачальником.

SELECT КД

FROM ОПД GROUP BY КД

HAVING COUNT(КП) > 1;

  1.  В таблиці Д знайти кортеж з кодом деталі 321 і замінити її колір на жовтий.

UPDATE Д

SET колір = ‘жовтий’

WHERE КД = 321;

Для зміни значень в таблиці  використовується оператор UPDATE. Всі рядки таблиці, які задовольняють умову, вказаній у фразі  WHERE, змінюються згідно з фразою SET.

  1.  Збільшити кількість деталей з номером 137, що постачаються постачальником з номером 12, на 10.

UPDATE ОПД

SET кількість = кількість + 10

WHERE КП = 12 AND КД = 137’;

  1.  Внесення нових кортежів.

INSERT INTO <ім’я таблиці>  [<поле1>,   <поле2>,… ]

VALUES (<значення1>, <значення2>, …)

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

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

Наприклад, вставити в таблицю Д ті рядки з таблиці Z, атрибут «колір» яких має значення червоний:

INSERT INTO Д

 SELECT *

FROM Z

WHERE колір = червоний’;

CREATE DOMAIN – створити новий тип.

DROP – знищити таблицю.

  1.  Вилучення рядків з таблиці.

DELETE П

WHERE КП = ‘013’;

З таблиці П вилучаються рядки, в яких  КП = ‘013’.

  1.  * Знайти коди деталей, які постачаються для всіх одержувачів у місті N.

SELECT DISTINCT КД

FROM ОПД X

WHERE  (SELECT КО

FROM ОПД

WHERE КД = X.КД) CONTAINS

(SELECT КО

FROM O

WHERE місто = ‘N’);

АБО

SELECT DISTINCT КД

FROM ОПД

GROUP BY КД

HAVING SET(КО) CONTAINS

(SELECT КО

FROM О

WHERE місто = ‘N’);

SQL ACCESS

Розглянемо приклад.

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

SELECT DISTINCTROW П.статус, MAX(ОПД.ціна)

FROM П INNER JOIN ОПД ON П.КП = ОПД.КП

GROUP BY П.статус

HAVING ((MAX(ОПД.ціна)) < 

SELECT AVG(ОПД.ціна)

FROM ОПД);

  1.  DISTINCTROW – призначений для відображення унікальних значень після виконання SELECT. Якщо у FROM стоїть лише одна таблиця, то DISTINCTROW та DISTINCT не відрізняються.
  2.  INNER JOIN ON – природне з’єднання таблиць.
  3.  GROUP BYутворює групи по однакових значеннях (в даному випадку – однаковому значенню статуса).
  4.  SELECT AVGобчислюється середнє значення по таблиці.

Кожне поле, яке вказане в SELECT, може бути супроводжено аліасом:

SELECT IN AS ALIAS

TOP 5 – взяти перші 5 значень.

TOP 5 PERCENTвзяти перші 5% значень.

Приклади.

  1.  Знайти прізвища постачальників, які щось постачають.

SELECT DISTINCTROW П.прізвище

FROM П

WHERE EXISTS  (SELECT *

FROM ОПД

WHERE ОПД.КП = П.КП);

  1.  Знайти назву і вагу деталей, для яких вага більша ніж у кожної червоної деталі.

SELECT DISTINCTROW Д.назва, Д.вага

FROM Д

WHERE ((Д.вага) > ALL

(SELECT Д.вага

FROM Д

WHERE колір = ‘червоний’));

Для пошуку хоча б одного значення використовуються SOME, ANY.

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

SELECT П.прізвище

FROM  П INNER JOIN  ( ОПД INNER JOIN  Д  ON Д.КД=ОПД.КД )

                                       ON  П.КП=ОПД.КП

GROP BY П.прізвище

HAVING COUNT (Д.колір) =

                                      SELECT COUNT (Д.колір)

                                      FROM  Д INNER JOIN  ( ОПД  INNER JOIN П ON П.КП=ОПД.КП)

                                                                               ON ОПД.КД=Д.КД

WHERE П.прізвище=”Іванчук”

Запити з множинними порівняннями

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

Для розгляду особливостей виразових можливостей таких версій SQL, як SQL-92  та  MySQL опишемо структуру таблиць з предметної області “Розклад вищого навчального закладу”. У нижченаведених таблицях опущені описи доменів атрибутів, бо для подальших прикладів це несуттєво. Атрибути, що утворюють ключі відповідних реляції, виділені підкресленням. Для зручності та кращого розуміння в таблицях наводяться як українські назви атрибутів і реляцій, так і більш звичні для мови SQL відповідні англомовні назви.

Лектор

Код лектора

Прізвище

Організація

Телефон

Наук. ступінь

Lect

CdL

Name

Org

Phone

Degree

Предмет

Код предмета

Назва

Кільк. годин

Тип

Контроль

Subj

CdS

Nm

Hours

Tp

Testing

Група

Код групи

Курс

Кафедра

Факультет

Кільк. чол.

Grp

CdG

Course

Caf

Faculty

Quant

Розклад

Код лектора

Код предмета

Код групи

Аудиторія

День

Пара

Тиждень

Sch

CdL

CdS

CdG

Aud

Day

TmL

Week

Запити з множинними порівняннями у мові SQL (СУБД MySQL)

Деякі СУБД, зокрема MySQL, підтримують версію мови SQL, де відсутні SubSELECT, тому викликають певну зацікавленість технологічні прийоми, спрямовані на реалізацію складних запитів з множинними порівняннями на основі однорівневих операторів SELECT.

Спочатку розглянемо запит :

“Знайти прізвища лекторів, які не проводять ніяких занять (тобто відсутні у розкладі)“. Такий запит можна реалізувати операцією віднімання кодів лекторів з реляції Розклад від множини кодів лекторів з реляції Лектор.

Основним засобом, за допомогою якого таке завдання можна реалізувати в SQL-92, є операція EXCEPT.

(SELECT Lect.CdL

FROM Lect ) EXCEPT 

(SELECT Sch.CdL

FROM Sch );

У версії SQL (MS Access) операція EXCEPT відсутня, тому зазвичай використовується такі варіанти:

(SELECT Lect.CdL

FROM Lect

WHERE Lect.CdL NOT  IN

(SELECT Sch.CdL

FROM Sch );

або

SELECT Lect.CdL

FROM Lect  LEFT JOIN Sch ON Lect.CdL = Sch.CdL

WHERE Lect.CdL = NULL;

Як бачимо, в останньому випадку застосовується однорівневий SELECT, тому така схема реалізації допустима і в СУБД, де підтримується тільки однорівневі оператори, наприклад, MySQL.  

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

Якщо задані дві реляції А[X,Y] і В[Y], то

(A[YY]B)    (A[X] \ ((A[X] B) \ A)[X]),

де   операція ділення;

 [ ] операція проекції;

\ операціія різниці;

декартів добуток.

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

Q1:

{A[X]}

SELECT A.X

FROM  A;

Q2:

{A[X] B}

SELECT Q1.X, B.Y

FROM  Q1,B;

Q3:

{(A[X] B) \ A)[X]}

SELECT Q2.X

FROM  Q2 LEFT JOIN A ON

            Q2.X=A.X  AND Q2.Y=A.Y

WHERE A.X = NULL AND A.Y = NULL;

Q4:

{A[X]\Q3}

SELECT A.X

FROM  A LEFT JOIN Q3 ON

                A.X = Q3.X

WHERE Q3.X = NULL;

Розглянемо запит: Знайти прізвища лекторів, що читають всі предмети.

Для реалізації такого типу запиту засобами однорівневого SQL, декомпонуємо його на ряд підзапитів:

  1.  Знайти всі можливі варіанти читання предметів викладачами (Q1)

SELECT Sch.CdL,Subj.CdS

FROM Sch, Subj;

2)  Знайти коди лекторів, які не читають якийсь предмет (Q2)

   SELECT Q1.CdL

   FROM  Q1 LEFT JOIN Sch  ON (Q1.CdL = Sch.CdL)

                              AND (Q1.CdS = Sch.CdS)

    WHERE (Sch.CdL = NULL)  AND (Sch.CdS = NULL);

  1.  Знайти коди лекторів, які читають всі предмети (Q3)

    SELECT Sch.CdL

    FROM  Q2 RIGHT JOIN Sch  ON (Q2.CdL = Sch.CdL)

    WHERE (Q2.CdL = NULL);

  1.  Знайти прізвища лекторів, які читають всі предмети (Q4)

     SELECT Lect.Name

    FROM  Lect INNER JOIN Q3  ON (Q3.CdL = Lect.CdL) ;

Розглянемо ще один запит, дещо складніший: “Знайти прізвища лекторів, які читають принаймні всі  ті предмети, які читає викладач Іванчук ”. В термінах класичного SEQUEL цей запит може виглядати, наприклад, так:

SELECT DISTINCT Lect.Name

FROM Lect

WHERE Lect.CdL IN

 (SELECT Sch.CdL

   FROM Sch

   GROUP BY Sch.Cdl

HAVING SET(Sch.CdS)

                    CONTAINS  //     

   (SELECT Sch.CdS                 

    FROM  Sch

    WHERE  Sch.CdL IN

         SELECT Lect.CdL

           FROM Lect

           WHERE Lect.Name = “Іванчук”)));

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

  1.  Знайти коди предметів, які читає Іванчук.(Q1)

SELECT Sch.CdS

FROM  Sch INNER JOIN Lect ON

               Sch.CdL =  Lect.CdL

WHERE Lect.Name = ”Іванчук”; 

(Q2)

SELECT Sch.CdL, Q1.CdS

FROM  Sch, Q1;  

  1.  Знайти коди лекторів, що не читають якийсь з тих предметів, які читає Іванчук.(Q3)

     SELECT Sch.CdL

     FROM  Q2  LEFT JOIN  Sch ON

                 Sch.CdL =  Q2.CdL AND Sch.CdS =  Q2.CdS

           WHERE  Sch.CdL =  NULL AND Sch.CdS =  NULL;

  1.  Знайти коди лекторів, які читають принаймні всі ті предмети, які читає Іванчук.(Q4)

     SELECT Sch.CdL

     FROM  Q3  RIGHT JOIN  Sch ON

                 Sch.CdL =  Q3.CdL AND Sch.CdS =  Q3.CdS

           WHERE  Q3.CdL =  NULL AND Q3.CdS =  NULL;

  1.  Знайти прізвища лекторів попереднього запиту.(Q5)

      SELECT Lect.Name

      FROM  Q4 INNER JOIN Lect ON

                          Q4.CdL =  Lect.CdL ;

            

ПОРІВНЯЛЬНИЙ АНАЛІЗ  ВИРАЗОВИХ МОЖЛИВОСТЕЙ SQL-92 СТАНДАРТА І КЛАСИЧНОГО  SQL(SEQUEL).

Виробники програмних продуктів при розробці мовних процесорів SQL орієнтуються на стандарт 1992 року або SQL-92.

Порівняємо окремі (найбільш цікаві на наш погляд) виразові можливості стандартного SQL-92 та його класичного попередника SEQUEL/2 [2]. Мова SEQUEL була задумана як мова функціонального типу з відповідними засобами та технологічними прийомами програмування. Під тиском користувачів, переважна  більшість яких була зорієнтована на процедурний стиль програмування, в наступній версії мови SEQUEL/2 з’являються додаткові засоби, такі як GROUP BY  та функції множинного порівняння типу SET.

Пізніше, на початку 80-х років мова SEQUEL/2 отримала нову назву SQL, а згодом в нових версіях цієї мови зникають можливості для множинного порівняння, що було зумовлено великою складністю алгоритмів порівняння множин, а відтак намаганням розробників зорієнтувати користувачів на застосування більш ефективних схем запитів пошуку даних. Але на нашу думку деякі схеми запитів класичного SEQUEL/2 зручніші для користувача (принаймні з логічної точки зору), бо мають стрункішу логічну структуру і чіткіше семантичне тлумачення через теорію множин та теорію відображень у порівнянні з функціонально еквівалентними їм схемами запитів, що виражені засобами SQL-92. Проілюструємо це твердження рядом прикладів.

Розглянемо наступний тип запиту: Знайти прізвища лекторів, що читають всі предмети.

В термінах мови SEQUEL (класичний) цей запит може бути представлений так:

SELECT DISTINCT Lect.Nam

FROM Lect

WHERE Lect.CdL IN

(SELECT Sch.CdL

 FROM Sch

 GROUP BY Sch.CdL

       HAVING SET(Sch.CdS) =

(SELECT Subj.CdS

    FROM Subj ))

Але в SQL-92 (і також в SQL Access) немає засобів для множинного порівняння, тому потрібна суттєва модифікація.

  1.  варіант (кількісний)  /* Count*/

SELECT Lect.Name

FROM Lect

WHERE Lect.CdL IN (SELECT Sch.CdL

                                     FROM Sch

                                     GROUP BY Sch.CdL

                                     HAVING Count(Sch.CdS) = (SELECT Сount(Subj.CdS)

                                                                                      FROM Subj));

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

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

2) варіант (ні –ні )  /* not – not */

SELECT DISTINCT Lect.Name

FROM Lect

WHERE NOT EXISTS   (SELECT *

                                         FROM  Subj

                                         WHERE NOT EXISTS  (SELECT *

                                                                                  FROM Sch

                                    WHERE Lect.CdL = Sch.Cdl AND Subj.Cds = Sch.CdS));

Цей варіант передбачає перетворення початкового запиту на рівні природної мови до такого ( функціонально еквівалентного початковому): “Знайти прізвища лекторів, для яких не існує предметів, які б вони не читали.”

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

Інший підхід до проблеми множинного порівняння може базуватися на тій обставині, що з усіх операцій реляційної алгебри Кодда тільки операція ділення не дозволяє безпосереднього представлення засобами SQL-92 (чи SQL Access). Але операція ділення не є незалежною, тобто вона може бути виражена через інші операції реляційної алгебри.  

Якщо задані дві реляції А[X,Y] і В[Y], то

(A[YY]B)    (A[X] \ ((A[X] B) \ A)[X]),

де   операція ділення;

 [ ] операція проекції;

\ операціія різниці;

декартів добуток.

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

Більш технологічним, на наш погляд, є підхід, який можна вважати (до певної міри) розширенням варіанту “ні-ні”, але з формальними перетвореннями на рівні теоретико-множинних співвідношень. 

Розглянемо запит:“Знайти коди лекторів, які читають принаймні всім групам кафедри МІ”. В термінах класичного SEQUEL цей запит може виглядати, наприклад, так:

SELECT DISTINCT Cdl

FROM Sch

GROUP BY Cdl

HAVING SET(Cdg)           /* A */

                    CONTAINS

   (SELECT Cdg                 /* B */

    FROM Grp

    WHERE  Grp.Caf = "mi" );

Якщо множину кодів груп SET(Cdg) позначимо через А, а множину (SELECT Cdg …) – через В, то отримаємо співвідношення

(A  B)    (B\A=) або (B(-A) =),

(останнє особливо важливо для SQL Access, в зв’язку з відсутністю операції EXCEPT). У відповідності з останнім можемо отримати таку SQL-програму.

/* CONTAINSNOT STRICT*/

SELECT DISTINCT x.Cdl

FROM Sch AS x

WHERE  NOT EXISTS  (SELECT *

                                         FROM Grp

                                         WHERE  Grp.Caf = "mi"

                                                           AND

                                            ( Grp.Cdg NOT IN   (Select Sch.Cdg

                                                                               FROM Sch

                                                                                WHERE Sch.Cdl = x.Cdl) ) );

Якщо потрібне строге включення, то маємо

(A B)    ((B \A = ) & (A\B )) або

(B(-A)=) & (A(-B))

/* CONTAINS-STRICT */

SELECT DISTINCT x.Cdl

FROM Sch AS x

WHERE NOT EXISTS  (SELECT *

                                        FROM Grp

                                        WHERE  Grp.Caf = "mi"

                                                       AND

                                           ( Grp.Cdg NOT IN  (Select Sch.Cdg

                                                                             FROM Sch

                                                                             WHERE Sch.Cdl = x.Cdl) ) )

                                                                                         AND  EXISTS

                                                                                (Select Sch.Cdg

                                                                                  From Sch

                                                                                  WHERE (Sch.Cdl = x.Cdl)  

                                                                                    AND

          (Sch.Cdg NOT IN    (Select Grp.Cdg

                                             FROM Grp

                                             WHERE  Grp.Caf = "mi" )));

Для випадку рівності множин маємо:

(A = B)    ((B \A = ) & (A \ B = )) або

(B(-A)=) & (A(-B)=)

/* EXACTLY */

SELECT DISTINCT x.Cdl

FROM Sch AS x

WHERE NOT EXISTS   (SELECT *

                                         FROM Grp

                                         WHERE  Grp.Caf = "mi"

                                                      AND

                                       ( Grp.Cdg NOT IN  (Select Sch.Cdg

                                                                         FROM Sch

                                                                        WHERE Sch.Cdl = x.Cdl) ) )

                                                                         AND   NOT EXISTS

                                                                           (Select Sch.Cdg

                                                                             From Sch

                                                                             WHERE (Sch.Cdl = x.Cdl)  

                                                              AND (Sch.Cdg NOT IN   (Select Grp.Cdg

                                                                                                          FROM Grp

                                                                                    WHERE  Grp.Caf = "mi" )));

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

Мова опису даних

Створення і маніпулювання базою даних 

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

  •  створення бази даних;
  •  створення, зміна і видалення таблиць;
  •  видалення бази даних.

Створення бази даних. Оператор CREATE DATABASE

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

CREATE DATABASE <ім’я бази данних> 

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

  •  делегувати повноваження для створення бази даних (системний адміністратор, або людина, якій такі повноваження були передані системним адміністратором);
  •  визначити місце (диск, каталог), де буде розташовуватися база даних.
  •  зарезервувати певний обсяг  дискового простору для подальшого запам'ятовування рядків таблиць та іншої інформації. Як правило, цей розмір може надалі збільшуватися.

Створення таблиці. Оператор CREATE TABLE

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

CREATE TABLE <ім’я таблиці>

 ( <поле 1> <тип даних 1> [ NOT NULL ]

 [,<поле 2> <тип даних 2> [ NOT NULL ]]...)

Кожна система має свої типи даних. Наведемо як приклад типи даних Oracle.

Data Type

Comments

CHAR

Текстово-числові дані довжиною від 1 до 255 символів.

DATE

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

LONG

Рядок символьних даних змінної довжини аж до 2 гігабайт.

LONG RAW

Двійкові дані довжиною до 2 гігабайт.

NUMBER

Числові, позитивні чи негативні числа з фіксованою чи  плаваючою крапкою.

RAW

Двійкові дані довжиною до 255 байт.

ROWID

Шістнадцятковий рядок, що подає унікальну адресу рядка в таблиці.

VARCHAR2

Текстово-числові дані довжиною від 1 до 2000 символів.

Значення NULL. Деякі з полів, наприклад, ключові, обов'язково повинні бути присутніми. Для вказівки цього використовується конструкція NOT NULL.

Унікальні значення.

Деякі системи дозволяють вказувати, що обумовлене поле повинне містити унікальні, тобто не повторювані, значення. Це особливо важливо для ключових полів. Інші системи, наприклад, Oracle і SQL Server, дозволяють декларувати унікальні (UNIQUE) індекси. Нарешті, деякі системи, включаючи Oracle, мають тип даних, значення якого автоматично привласнюється створюваним рядкам, таким чином, щоб ці значення були унікальними в межах таблиці на весь час її існування. У Oracle таким типом є ROWID. Окрім того, Oracle має фразу UNIQUE для вказівки списку полів, що спільно повинні подавати унікальне значення в таблиці.

Приклад: Створити таблицю Lect (використовуємо типи даних Oracle)

CREATE TABLE Lect

( СdL  ROWID,

 Name  CHAR(50) NOT NULL,

 Org  CHAR(20),

 Phone  CHAR(10),

 Degree  CHAR(10)

)

Створення таблиці на базі існуючої.

Найзагальнішим методом створення таблиць є використання команди CREATE TABLE. Однак, деякі системи надають альтернативний спосіб визначення таблиць з використанням формату і даних наявної таблиці. Цей метод корисний для вибирання даних з таблиці для тимчасового збереження і модифікації. У Oracle синтаксис цієї команди такий:

CREATE TABLE <нова таблиця> (<список полів>)

AS (SELECT <список полів>

  FROM <стара таблиця>

      [<WHERE...>])

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

Модифікація таблиці. Оператор ALTER TABLE

Часто виявляється, що уже визначена таблиця не відповідає зміненим вимогам до бази даних. Команда ALTER TABLE дозволяє змінити структуру таблиці після її створення. Вона дозволяє:

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

Синтаксис команди такий:

ALTER TABLE <ім’я таблиці>

[ADD <поле>  <означення поля> |

   ALTER <поле>  <параметри> |

 DROP <поле>  <параметри>  ]

Видалення таблиці. Оператор DROP TABLE

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

DROP TABLE <ім’я таблиці>

Видалення бази даних. Оператор DROP DATABASE

Є команда для видалення всієї бази даних. Її синтаксис такий:

DROP DATABASE <ім’я бази даних>

Віртуальні таблиці й індекси

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

Використання віртуальних таблиць.

Віртуальні таблиці використовуються для створення складних запитів, що постійно зберігаються. До віртуальної таблиці можна звертатися як до звичайної таблиці. Вона може використовуватися, як таблиця в директивах SELECT, INSERT, INPUT, UPDATE, DELETE. Віртуальна таблиця не займає місця в базі даних, як звичайна таблиця. Синтаксис такий:

CREATE VIEW <віртуальна таблиця> [(<список полів>)] AS

SELECT < список полів >

FROM <імена таблиць>

[WHERE …]

[WITH CHECK OPTION]

Необов'язкова фраза WITH CHECK OPTION (з контролем) указує, що для операцій INSERT і UPDATE над цією таблицею повинен здійснюватися контроль, що забезпечує задоволення WHERE фрази підзапиту.

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

Проста віртуальна таблиця.

Це віртуальна таблиця, що є копією вихідної, але під іншим ім'ям.

CREATE VIEW COPY_Lect AS

    SELECT *

 FROM Lect

Тут створюється віртуальна таблиця FACULTYCOPY, що є точною копією FACULTY.

Можна також створювати віртуальні таблиці на основі інших віртуальних таблиць.

Вибирання стовпчиків.

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

CREATE VIEW Phone_Lect (Name, Phone) AS

    SELECT *

 FROM Lect

Перейменування стовпчиків.

Синтаксис створення віртуальних таблиць дозволяє перейменовувати стовпчики вихідної таблиці. Для цього необхідно явно вказати стовпчик у фразі SELECT і вказати необхідні імена стовпчиків у фразі CREATE VIEW.

CREATE VIEW Lect_Phone (First_Name, Lect_Phone) AS

    SELECT Name, Phone 

 FROM Lect

Складні конструкції.

Фраза WHERE у складі оператора CREATE VIEW може містити підзапити. Наприклад, для одержання віртуальної таблиці із списком лекторів (з науковими ступенями), які викладають БД, а також хоча б один інший курс, варто написати:

CREATE VIEW Lect_2 (Name, Degree) AS

SELECT Lect.Name, Degree

FROM  Subj, Sch, Lect

WHERE Lect.CdL = Sch.CdL AND

 Sch.CdS = Subj.CdS AND

 Subj.Nm='БД' AND

 Lect.CdL IN  

   SELECT Lect.CdL

   FROM Subj, Sch, Lect

   WHERE Lect.CdL = Sch.CdL AND

     Sch.CdS = Subj.CdS

     Subj.Nm != 'БД'

Обмеження на використання директиви SELECT.

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

  •  не можна використовувати оператор UNION,
  •  не можна використовувати фразу ORDER BY.

Зміна даних через віртуальні таблиці.

Віртуальні таблиці можна використовувати в директивах UPDATE, INSERT, DELETE для зміни даних у базі даних, але при цьому повинні виконуватися такі умови:

  •  не можна використовувати директива DELETE до віртуальних таблиць, визначених на багатьох базових таблицях
  •  директиву INSERT можна використовувати тільки в тому випадку, якщо віртуальна таблиця містить усі NOT NULL стовпчика базової таблиці;
  •  якщо відбувається додавання чи оновлення через з'єднану віртуальну таблицю, то всі обновлювані записи повинні належати одній і тій же фізичній таблиці.
  •  не можна вставляти чи обновляти записи через віртуальну таблицю, визначену із фразою DISTINCT.
  •  не можна оновлювати віртуальні стовпчики (тобто стовпчики, що є обчисленням чи виразом виконання функції).

Застосування віртуальних таблиць.

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

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

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

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

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

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

Видалення віртуальної таблиці.

Є команда для видалення віртуальної таблиці. Її синтаксис такий:

DROP VIEW <ім’я віртуальної таблиці>

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

Використання індексів

Інший спосіб відобразити дані не так, як вони зберігаються, - це використовувати індекси. Індекси використовуються для таких цілей:

  •  задоволення вимоги унікальності записів;
  •  підтримки логічної упорядкованості даних відповідно до значень одного чи декількох полів;
  •  оптимізації виконання запитів.

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

Визначення індекса.

Базовий синтаксис оператора  створення індекса такий:

CREATE INDEX    <ім’я індекса>

ON    <ім’я таблиці>  (<поле1>[, <поле2>]…)

Однак у конкретних СУБД він істотно доповнюється іншими конструкціями.

Правила використання індексів.

При використанні індексів варто враховувати наступне.

  •  Для таблиць невеликих розмірів індекси практично не приводять до підвищення продуктивності.
  •  Індекси істотно підвищують продуктивність, коли стовпці містять в основному неповторювані дані чи багато NULL-значень.
  •  Індекси істотно оптимізують виконання запитів, що видають невелику кількість рядків (гарне правило – 25% рядків).
  •  Індекси підвищують швидкість пошуку даних, однак сповільнюють процес їхнього оновлення. Пам’ятайте про це, коли необхідно зробити одночасне оновлення великої кількості рядків з індексами. У цьому випадку можна розглянути варіант, коли перед таким оновленням індекс знищується, а після завершення оновлень він знову відновлюється.
  •  Зберігання  індексів потребує значних обсягів пам’яті. Якщо СУБД дозволяє керувати пам'яттю, відведіть частину пам'яті під індекси.
  •  Завжди індексуйте поля, що використовуються для з'єднання таблиць. Це істотно збільшує швидкість з'єднання таблиць.
  •  Не індексуйте поля, що регулярно піддаються інтенсивному оновленню.
  •  Якщо це можливо, то не зберігайте індекси і таблиці на тому самому фізичному пристрої. Поділ цих об'єктів знижує навантаження на носії і приводить до більш швидшого виконання запитів.

Складений індекс.

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

 CREATE INDEX  Lect_Name_Degree

 ON  Lect (Name, Degree)

створює індекс у таблиці Lect по полях Name і Degree. У складених індексах намагайтеся спочатку вказувати найчастіше вживані поля. Використовуйте складені індекси, якщо зазначені в них поля часто використовуються одночасно для вказівки умови вибирання даних.

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

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

 CREATE UNIQUE INDEX Lect_ID

 ON Lect(CdL)

свідчить, що в таблиці Lect значення поля CdL повинні бути неповторюваними (унікальними).

Порядок сортування полів в індексі.

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

 CREATE INDEX Lect_NAME_ Degree

 ON Lect (Name DESC, Degree)

указує на необхідність індексування таблиці Lect за стовпцем Name в спадаючому порядку, а за стовпцем Degree - у зростаючому. За замовчуванням завжди порядок сортування в індексі передбачається зростаючим.

Видалення індекса.

Видалення індекса виконується по команді DROP INDEX із синтаксисом:

DROP  INDEX  <ім’я індекса>

Мова обробки транзакцій

Транзакція (transaction) – це сукупність операцій маніпулювання базою даних, що повинні розглядатися як атомарна дія, тобто таку, що або цілком виконується, або, при необхідності, цілком скасовується як єдине ціле. Різні СУБД мають специфіку щодо підтримки транзакцій. Тому будемо використовувати деякий узагальнений синтаксис.

Транзакція починається командою типу:

BEGIN TRANSACTION [<імя транзакції>]

і завершується командою

COMMIT [TRANSACTION]

Наприклад, якщо є така транзакція:

BEGIN TRANSACTION

 INSERT  INTO П VALUES (1, "Іванов", 5, "Москва")

INSERT   INTO П VALUES(1, "Іваненко", 3, "Київ")

COMMIT

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

Скасування транзакції і точки збереження

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

ROLLBACK [TRANSACTION] [ TO [SAVEPOINT] savepoint

Тут також можна вказати так звані точки збереження (контрольні точки), про які мова йтиме далі. У прикладі, що наводиться:

BEGIN TRANSACTION

 INSERT INTO П VALUES (1, "Іванов", 5, "Москва")

 ROLLBACK TRANSACTION

INSERT INTO INTO П VALUES(1, "Іваненко", 3, "Київ")

COMMIT

буде вставлений тільки другий рядок, оскільки після додавання першого рядка відбувається скасування транзакції (дія з додавання першого рядка анулюється).

Після виконання команди COMMIT усі дії, що є в транзакції виконуються. Відміна транзакції приводить до скасування всіх дій, виконаних у транзакції до моменту виконання команди ROLLBACK TRANSACTION. Однак є можливість робити скасування не всієї транзакції, а її частини. Для цього використовуються так звані точки збереження, що після їхнього визначення можна використовувати в команді відкату транзакції. Синтаксис точок збереження такий:

 SAVEPOINT <імя точки збереження>

У наступному прикладі

BEGIN TRANSACTION

UPDATE ОПД

   SET ціна = ціна * 1,5 WHERE кільк < 5

   SAVEPOINT save_it

 DELETE FROM ОПД WHERE кільк < 5

 ROLLBACK TO SAVEPOINT save_it

COMMIT

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

Тригери

Тригери (trigger) – це SQL директива, що ініціюється при виконанні визначених операцій над об'єктами бази даних. Ми розглянемо як об'єкти бази даних – таблиці, а як операції – додавання, видалення і заміну рядків. Тригери – це один з механізмів підтримки цілісності бази даних.

У найпростішому вигляді синтаксис оголошення тригера є таким:

CREATE TRIGGER <імя тригера>

   {BEFORE | AFTER} <операції над таблицею> [OF <список полів>]ON  <ім’я таблиці>

  [WHEN  (<умова>)]

  <оператори SQL>

Якщо умова у фразі WHEN істинна або ця фраза відсутня, до (BEFORE)або після (AFTER)виконання операції INSERT, UPDATE чи DELETE над таблицею, зазначеною після слова ON, буде виконано вказані нижче оператори SQL. Коли таких операторів кшлька, їх слід помістити між ключовими слоами BEGIN ATOMSC  та END. Конструкція другого рядка означення тригера називається реченням ініціювання (triggering statement), WHENумовою ініціювання тригера (WHEN clause), а <оператори SQL>– дією тригера (trigger body).

Розглянемо декілька прикладів.

  1.  Перед видаленням інформації про деталь видалити. з таблиці ОПД інформацію про поставки цієї деталі

CREATE TRIGGER Д_видалення

  BEFORE DELETE    ON Д

   DELETE  FROM ОПД

WHERE  ОПД.кд =  Д.кд

  1.  Після видалення з таблиці Lect рядка про лектора встановити значення NULL у всіх рядках таблиці розкладу Sch, у яких значиться код цього лектора.

CREATE TRIGGER Lect_Delete

     AFTER DELETE    ON Lect

    UPDATE  Sch

SET CdL = NULL

WHERE Sch.CdL = Lect.CdL

Доступ до старих і нових значень рядків.

Якщо виконується оновлення рядків таблиці, то в тригері допускається звертання до старих і нових значень оновлюваних рядків. Це досягається використанням кваліфікатора NEW/OLD перед ім'ям стовпця. Такі кваліфікатори допускається використовувати як в умові тригера, так і в описі його дії.

Тригери і транзакції.

Неявно мається на увазі, що дії, які виконуються під час основної операції та в  тригері, складають єдину транзакцію. Це означає виконання наступних дій:

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

Отже, при необхідності тригер може ініціювати скасування транзакції.

3)  Рядок про поставку деталей може бути доданий тільки в тому випадку, коли існують рядки про постачальника, деталь, отримувача у відповідних таблицях:

CREATE TRIGGER ОПД_Insert

  ON  ОПД

  FOR INSERT

  WHEN NOT EXIST ( SELECT *

   FROM П, Д, О

     WHERE ОПД.кп = П.кп  AND

      Д.кд = П.кд  AND

      О.ко = П.ко  )

begin

 ROLLBACK TRANSACTION

End

Вкладеність тригерів.

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

Пам’ятайте, що вкладеність тригерів може призводити до їх "зациклення".

Обмеження на використання тригерів:

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


 

А также другие работы, которые могут Вас заинтересовать

83114. Цікаві «підказки» братів наших менших 108.5 KB
  Правила за якими будемо працювати на уроці: слайд про правила роботи в групах поважати думку кожного; говорити по черзі. Вправи на вдосконалення читацьких навичок Одним із важливих винаходів яким зараз дуже поширено користується людство є Загадка про метро Дві змії в землі лежать Табуни по них біжать...
83115. Додавання виду 37+6. Розв’язування простих та складених задач 139.5 KB
  Мета уроку. Ознайомити учнів з прийомом усного додавання з перехо дом через розряд, коли в одному з доданків відсутні де сятки. Удосконалювати вміння розв’язувати прості та складені задачі; знаходити числові значення буквених виразів. Розвивати навички усного рахунку.
83116. УКРАЇНСЬКІ ПИСЬМЕННИКИ ДІТЯМ 112.5 KB
  Поширити знання учнів про життя і творчість українських письменників які писали для дітей. А хто створює книги Ми можемо уявити своє життя без книги Чому Відповіді дітей Книга має величезне значення в житті людини. І сьогодні ми поведемо розмову про письменників України які писали для дітей.
83117. Як тварини готуються до зими? Комахи восени 141.5 KB
  Мета: встановлювати причинно-наслідкові зв’язки між змінами в неживій природі в житті рослин та тварин восени; формувати уявлення про комах про їхню поведінку восени; співставляти відповідність між зображенням комахи і способом в який вона готується до зими; збагачувати словник дітей новими словами...
83118. ПКРУГОВОРОТ ВОДЫ В ПРИРОДЕ. ПРИРОДОВЕДЕНИЕ 1.01 MB
  Цели урока: Формировать представление о процессах испарения конденсации замерзания и таяния воды о связи с сезонными изменениями в природе. Базовые понятия на уроке: вещество газообразное состояние твёрдое состояние круговорот воды конденсация испарение гипотеза.
83119. Человек. Виды деятельности. Одежда 111 KB
  Задачи урока: образовательная: научить использовать активную лексику урока в речи актуализировать знания по теме Одежда цвета профессии; Развивающая: развивать память внимание воображение учащихся; Практическая: практика речи письма визуального восприятия материала;...
83120. Имя прилагательное как часть речи в русском и английском языках. Изменение имён прилагательных по родам и числам. Сравнение категории числа имени прилагательного в русском и английском языках 58.5 KB
  Цель: обобщить и систематизировать знания учащихся об имени прилагательном развивать умения распознавать имена прилагательные в русском и английском языках определять род и число прилагательного в русском языке и невозможность определения категории числа в английском.
83121. Путешествие с Планетой. Мой дом – моя Родина 549.5 KB
  Основные понятия и термины урока: Планета Земля вселенная планеты солнечной системы Родина страна столица мой город компас горизонт карта план местности экология экологические проблемы. Кто пришел к нам в гости Правильно к нам в гости пришла Планета Земля. Планета Земля подготовила для вас вопросы и задания.
83122. Різноманітність рослин у природі. В.О.Сухомлинський «Верба над ставком» 110 KB
  Формувати уявлення про різноманітність рослинного світу про водорості мохи хвощі папороті хвойні рослини квіткові рослини уміння розрізняти окремі рослини цих груп розуміти цінність їх у природі; виробляти уміння узагальнювати робити висновки оцінювати результати діяльності...