Мова SQL оперує термінами, що трохи відрізняються від термінів реляційної теорії, наприклад, замість "відношення" використавуються "таблиці", замість "кортежів" - "рядки", замість "атрибутів" - "колонки" або "стовпці". Мова SQL є реляційно повною. Це означає, що будь-який оператор реляційної алгебри може бути виражений підходящим оператором SQL.
7.1 Припустимі типи даних
Будь-який діалект SQL підтримують три загальних типи даних: строковий, числовий й тип для подання дати й часу. Завдання типу даних визначає значення й довжину даних, а також формат їхнього подання при візуалізації.
Для всіх типів даних визначено так зване нуль-значення, що вказує на відсутність даних у колонку зазначеного типу, тобто та обставина, що значення даних у сучасний момент часу невідомо.
Дані строкового типу являють собою послідовність рядків символів. Строкові дані можуть бути задані як з визначеною довжиною (ключові слова char або varchar (довжина рядка)), так і без вказівки довжини (ключове слово long varchar) для подання рядків довільної довжини.
Числові типи даних призначені для подання цілих чисел, чисел з десятковою крапкою й чисел із плаваючою крапкою. Будь-яке подання чисел задається своєю точністю й масштабом. Точність визначає припустиме подання кількосты значущих цифр числа, а масштаб - кількість значущих цифр після десяткової крапки.
Для подання цілих чисел використовуються типи interger (точність 10 значущих цифр) і smallint (точність 5 значущих цифр).
Для подання чисел з фіксованою десятковою крапкою використовуються типи number і decimal.
Для подання чисел із плаваючою крапкою в SQL передбачені такі типи даних:
- Double Precision - для чисел з точністю від 22 до 53 значущих цифр;
- Float (точність) - для подання чисел з точністю від 1 до 21 значущої цифри;
- Real - для чисел з точністю за замовчуванням (залежить від конкретної реалізації).
Звичайно в конкретних діалектах SQL використовуються три типи для подання таких даних:
- datestamp (timestamp) - для подання дати й часу;
- date - для подання дати;
- time - для подання часу.
Константи, вираження, системні змінні. Константи зазвичай визначають єдине значення й, відповідно до типу даних, що представляють, можуть бути строковими, числовими й представляти дату/час. Строкові константи повинні бути укладені в одинарні лапки.
В SQL існує ряд визначених системних змінних, які можна використовувати у виразах замість імен колонок і констант. До таких змінних відноять наступні:
- NULL - для подання невизначених значень;
- ROWID - (в SQLBase) внутрішній системний номер рядка в таблиці;
- USER - ім'я користувача, активного в цей момент;
- SYSDATETIME - системний поточний час і дата;
- SYSDATE - системна поточна дата;
- SYSTIME - системний поточний час;
- SYSTIMEZONE - часовий пояс, установлений у системі.
Виразом в SQL є ітем або комбінація ітемів з припустимими для них операціями, що дає єдине значення. У якості ітемів можуть виступати імена колонок, константи, зв'язані змінні, результати обчислень функцій, системні змінні й інші вирази. При цьому якщо один з ітемів має нуль-значення, то результат виразу також має нуль-значення.
7.2 Використання операторів мови SQL
7.2.1 Оператори SQL
Основу мови SQL становлять оператори, умовно розбиті на кілька груп за функціональним показником.
Оператори DDL (Data Definition Language) - оператори визначення об'єктів БД
- CREATE SCHEMA - створити схему БД
- DROP SHEMA - видалити схему БД
- CREATE TABLE - створити таблицю
- ALTER TABLE - змінити таблицю
- DROP TABLE - видалити таблицю
- CREATE DOMAIN - створити домен
- ALTER DOMAIN - змінити домен
- DROP DOMAIN - видалити домен
- CREATE COLLATION - створити послідовність
- DROP COLLATION - видалити послідовність
- CREATE VIEW - створити подання
- DROP VIEW - видалити подання
Оператори DML (Data Manipulation Language) - оператори маніпулювання даними
- SELECT - відібрати рядок з таблиць
- INSERT - додати рядок в таблицю
- UPDATE - змінити рядок в таблиці
- DELETE - видалити ряодк в таблиці
- COMMIT - зафіксувати внесені зміни
- ROLLBACK - відкотити внесені зміни
Оператори захисту й керування даними
- CREATE ASSERTION - створити обмеження
- DROP ASSERTION - видалити обмеження
- GRANT - надати привілею користувачеві або додатку на маніпулювання об'єктами
- REVOKE - скасувати привілею користувача або додатка
7.2.2 Оператори маніпулювання даними
INSERT - вставка рядків у таблицю
INSERT INTO
P (PNUM, PNAME)
VALUES (4, "Іванов");
UPDATE - відновлення рядків у таблиці
UPDATE P
SET PNAME = "Пушников"
WHERE P.PNUM = 1;
DELETE - видалення рядків у таблиці
DELETE FROM P;
7.2.3 Виборка даних
Оператор SELECT завжди виконується над деякими таблицями, що входять у БД.
Подання - це SELECT-вираз, що просто зберігається в БД. З погляду користувачів подання - це таблиця, яка не зберігається постійно в БД, а "виникає" у момент звертання до неї.
Результатом виконання оператора SELECT завжди є таблиця.
7.2.3.1. Відбір даних з однієї таблиці
Ключові слова, що використовуються: SELECT…FROM…
Виборка даних з таблиць, які задовольняють певній умові: ключове слово WHERE. Як умову в розділі WHERE можна використовувати складні логічні вирази, що використовують поля таблиць, константи, порівняння (>, <, = і т.д.), дужки, союзи AND й OR, заперечення NOT.
Вибірка деяких колонок з вихідної таблиці реалізується вказівкою списку потрібних колонок.
Якщо у вихідній таблиці було присутнє кілька рядків з різними номерами, але однаковими найменуваннями, то в результуючій таблиці будуть рядки з повтореннями - дублікати рядків автоматично не відкидаються.
Вибірка даних без дублювання даних досягається використанням ключового слова DISTINCT. Використання ключового слова DISTINCT приводить до того, що у результуючій таблиці будуть вилучені всі повторювані рядки.
SELECT DISTINCT P.NAME
FROM P;
Використання ключового слова AS дозволяє проіменувати колонки в результуючій таблиці
SELECT TOVAR...TNAME, TOVAR.KOL,
TOVAR.PRICE, "="AS EQU,
TOVAR.KOL*TOVAR.PRICE AS SUMMA
FROM TOVAR;
В результаті одержимо таблицю з колонками, яких не було у вихідній таблиці TOVAR:
TNAME | KOL | PRICE | EQU | SUMMA |
Болт | 10 | 100 | = | 1000 |
Гайка | 20 | 200 | = | 4000 |
Гвинт | 30 | 300 | = | 9000 |
Існує можливість упорядкувати результати запитів по полю за допомогою використання ключового слова ORDER BY та по декількох полях за зростанням або зменшенням за допомогою ключових слів ASC, DESC). Якщо явно не зазначені ключові слова ASC або DESC, то за замовчуванням приймається впорядкування по зростанню (ASC).
SELECT PD...PNUM, PD.DNUM, PD.VOLUME
FROM PD
ORDER BY DNUM;
В результаті одержимо наступну таблицю, впорядковану по полю DNUM:
PNUM | DNUM | VOLUME |
1 | 1 | 100 |
2 | 1 | 150 |
1 | 2 | 200 |
1 | 3 | 300 |
7.2.3.2. Відбір даних з декількох таблиць
Існує декілька способів отримати дані з декількох таблиць.
Природне з'єднання таблиць дозволяє отримати дані шляхом явної вказівки умов з'єднання. Таблиці, що з'єднують, перераховані у розділі FROM оператора, умова з'єднання наведена у розділі WHERE. Розділ WHERE, крім умови з'єднання таблиць, може також містити й умови відбору рядків.
Другий спосіб - природне з'єднання таблиць (спосіб 2 - ключові слова JOIN…USING…). Ключове слово USING дозволяє явно вказати, по яким із загальних колонок таблиць буде вироблятися з'єднання.
Третій спосіб - природне з'єднання таблиць (спосіб 3 - ключове слово NATURAL JOIN). У розділі FROM не зазначається, по яких полях виробляється з'єднання. NATURAL JOIN автоматично з'єднує по всіх однакових полях у таблицях.
7.2.4 Вбудовані функції
Арифметичні функції та функції обробки дати й часу
SQL підтримує повний набір арифметичних операцій і математичних функцій для побудови арифметичних виражень над колонками БД (+, -, *, /, ABS, LN, SQRT і т.д.). Перелік основних вбудованих математичних функцій та функцій по роботі з даними типу дата/час ви можете знайти в літератрних джерелах, перелік яких наданий наприкінці модуля.
Арифметичні вирази необхідні для одержання даних, які безпосередньо не зберігаються в колонках таблиць БД, але значення яких необхідні користувачеві.
Якщо вам потрібен був список нових службовців, що надійшли за останній квартал в організацію, то ви можете написати запит у наступному виді:
SELECT ENAME, HIREDATE,
HIREDATE + 92 DAYS
FROM EMPLOYEE
WHERE HIREDATE + 92 DAYS > SYSDATE
AND DEPNO=30;
Використання агрегатних функцій у запитах
В мові SQL передбачені такі оператори агрегатних функцій:
AVG(X) = AVG(ALL X) AVG(DISTINCT X) Обчислює середнє значення аргументу, що може бути виразом будь-якого типу. Нуль-значення ігноруються, ключове слово DISTINCT придушує дублікати.
COUNT(*) COUNT(X) = COUNT(ALL X) COUNT(DISTINCT X) Обчислює числа ітемів. При вказівці * завжди повертається число рядків у таблиці. Вказівка DISTINCT придушує дублікати.
MAX(X) = MAX(ALL X) MAX (DISTINCT X) Обчислює максимальне значення аргументу, що може бути виразом будь-якого типу. Нуль-значення ігноруються, ключове слово DISTINCT придушує дублікати
MIN(X) = MIN(ALL X) MIN (DISTINCT X) Обчислює мінімальне значення аргументу, що може бути виразом будь-якого типу. Нуль-значення ігноруються, ключове слово DISTINCT придушує дублікати
SUM(X) = SUM(ALL X) SUM (DISTINCT X) Обчислює суму значення аргументу, що може бути виразом будь-якого типу. Нуль-значення ігноруються, ключове слово DISTINCT придушує дублікати
Використання агрегатних функцій з угрупованнями
Для виведення результатів даних, сгрупованих за певною умовою, слід використовувати два ключових слова: GROUP BY та HAVING.
В переліку полів оператора SELECT, який містить розділ GROUP BY можна включати тільки агрегатні функції й поля, які входять в умову групування.
Умови, що використовують агрегатні функції повинні бути розміщені у спеціальному розділі HAVING. В одному запиті можуть зустрітися як умови відбору рядків у розділі WHERE, так й умови відбору груп у розділі HAVING. Умови відбору груп не можна перенести з розділу HAVING у розділ WHERE. Аналогічно й умови відбору рядків не можна перенести з розділу WHERE у розділ HAVING, за винятком умов, що включають поля зі списку угруповання GROUP BY.
Запита: для кожної деталі одержати сумарну кількість, що поставляється (ключове слово GROUP BY…):
SELECT..DNUM, SUM(PD.VOLUME) AS SM
GROUP BY PD.DNUM;
Цей запит буде виконуватися в такий спосіб. Спочатку рядки вихідної таблиці будуть згруповані так, щоб у кожну групу потрапили рядки з однаковими значеннями DNUM. Потім усередині кожної групи буде просумовано поле VOLUME. Від кожної групи до результуючої таблиці буде включений один рядок.
Запит: одержати номери деталей, сумарна кількість поставки яких перевершує 400 (ключове слово HAVING…):
SELECT PD.DNUM, SUM(PD.VOLUME) AS SM
GROUP BY PD.DNUM
HAVING SUM(PD.VOLUME) > 400;
Умова, що сумарна кількість поставки повинна бути більше 400, не може бути сформульована у розділі WHERE, тому що в цьому розділі не можна використовувати агрегатні функції. Умови, що використовують агрегатні функції повинні бути розміщені у спеціальному розділі HAVING.
7.2.5 Використання підзапитів
Дуже зручним засобом, що дозволяє формулювати запити більш зрозумілим чином, є можливість використання підзапитів, вкладених в основний запит.
Самий простий спосіб виконання запиту прямою вказівкою в розділі WHERE вкладеного запиту.
Запит: одержати список постачальників, статус яких менше максимального статусу у таблиці постачальників (порівняння з підзапитом):
SELECT *
FROM P
WHERE P.STATYS < (SELECT MAX(P.STATUS) FROM P);
Тоді як поле P.STATUS рівняється з результатом підзапиту, то підзапит повинен бути сформульований так, щоб повертати таблицю, що складається рівно з одного рядка й однієї колонки.
Результат виконання запиту буде еквівалентний результату такої послідовності дій:
- Виконати один раз вкладений підзапит й одержати максимальне значення статусу.
- Просканувати таблицю постачальників P, щоразу порівнюючи значення статусу постачальника з результатом підзапиту, і відібрати тільки ті рядки, в яких статус менше максимального.
Другий спосіб формування підзапиту - використання предиката IN. Результат виконання запиту буде еквівалентний результату такої послідовності дій:
- Виконати один раз вкладений підзапит й одержати перелік значень.
- Просканувати таблицю запиту, щораз перевіряючи, чи втримується значення відповідного атрибута в результаті підзапиту.
Існує ще один спосіб - використання предиката EXIST. Результат виконання запиту буде еквівалентний результату такої послідовності дій:
- Просканувати таблицю запиту, щоразу виконуючи підзапит з новим значенням відповідного атрибута з таблиці запиту.
- В результат запиту включити тільки ті рядки з таблиці запиту, для яких вкладений підзапит повернув непусту множину рядків.
Коли вкладений підзапит містить параметр (зовнішнє посилання), переданий з основного запиту, такі підзапити називаються кореліруємими (correlated). Зовнішнє посилання може приймати різні значення для кожного рядка-кандидата, оцінюваного за допомогою підзапиту, тому підзапит повинен виконуватися заново для кожного рядка, який відбирається в основному запиті. Такі підзапити характерні для предиката EXIST, але можуть бути використані й в інших підзапитах.
7.2.6. Використання об'єднання, перетинання й різниці
Результуючі таблиці поєднуваних запитів повинні бути сумісні, тобто мати однакову кількість стовпців й однакові типи стовпців у порядку їхнього перерахування. Не потрібно, щоб поєднувані таблиці мали б однакові імена колонок. Це відрізняє операцію об'єднання запитів в SQL від операції об'єднання у реляційній алгебрі. Найменування колонок у результуючому запиті будуть автоматично взяті з результату першого запиту в об'єднанні.
Для виконання об'єднання двох підзапитів використовуються ключові слова UNION (об'єднання) та INTERSECT (перетин) та EXCEPT (різниця).
Запит: одержати імена постачальників, що мають статус, більший 3-ого або що поставляють хоча б одну деталь номер 2
SELECT P.PNAME
FROM P
WHERE P.STATUS > 3 UNION SELECT P.PNAME
FROM P, PD
WHERE P.PNUM = PD.PNUM AND PD.DNUM = 2;
Запит: одержати імена постачальників, що мають статус, більший 3-ого й одночасно поставляють хоча б одну деталь номер 2:
SELECT P.PNAME
FROM P
WHERE P.STATUS > 3 INTERSECT SELECT P.PNAME
FROM P, PD
WHERE P.PNUM = PD.PNUM AND PD.DNUM = 2;
Запит: одержати імена постачальників, що мають статус, більший 3-ого, за винятком тих, хто поставляє хоча б одну деталь номер 2:
SELECT P.PNAME
FROM P
WHERE P.STATUS > 3 EXCEPT SELECT P.PNAME
FROM P, PD
WHERE P.PNUM = PD.PNUM AND PD.DNUM = 2;