03 - Організація баз даних та знаньТема 7 - Введення в структуровану мову запитів SQL Конспект лекції

Ключові терміни:

кореліруємий підзапит, оператор DDL, оператор DML, подання

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

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

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

Будь-який діалект 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

Основу мови 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 - скасувати привілею користувача або додатка

Найбільш важливими для користувача є оператори маніпулювання даними (DML).

INSERT - вставка рядків у таблицю

Вставка одного рядка до таблиціСховати

INSERT INTO

P (PNUM, PNAME)

VALUES (4, "Іванов");

UPDATE - відновлення рядків у таблиці

Відновлення декількох рядківСховати

UPDATE P

SET PNAME = "Пушников"

WHERE P.PNUM = 1;

DELETE - видалення рядків у таблиці

Видалення рядків з таблиці...Сховати

DELETE FROM P;

Оператор SELECT є фактично найважливішим для користувача й самим складним оператором SQL. Оператор SELECT завжди виконується над деякими таблицями, що входять у БД.

Насправді в БД можуть бути не тільки постійно збережені таблиці, а також тимчасові таблиці й так називані подання. Подання - це SELECT-вираз, що просто зберігається в БД. З погляду користувачів подання - це таблиця, яка не зберігається постійно в БД, а "виникає" у момент звертання до неї. З погляду оператора SELECT і постійно збережені таблиці, і тимчасові таблиці й подання виглядають зовсім однаково.

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

7.2.3.1. Відбір даних з однієї таблиці

Ключові слова, що використовуються: SELECT…FROM…

Вибірка всіх даних з таблиціСховати

SELECT *

FROM P;

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

Виборка даних з таблиць, які задовольняють певній умові: ключове слово WHERE. Як умову в розділі WHERE можна використовувати складні логічні вирази, що використовують поля таблиць, константи, порівняння (>, <, = і т.д.), дужки, союзи AND й OR, заперечення NOT.

Вибірка рядків за умовоюСховати

SELECT*

FROM P

WHERE P...PNUM>2;

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

Вибірка визначених колонок з таблиціСховати

SELECT P.NAME

FROM P;

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

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

Вибірка даних без дублювання даних досягається використанням ключового слова 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

SELECT PD.PNUM, PD.DNUM, PD.VOLUME

FROM PD

ORDER BY DNUM ASC, VOLUME DESC;

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

PNUM	DNUM	VOLUME
2	1	150
1	1	100
1	2	200
1	3	300

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

Природне з'єднання таблиць дозволяє отримати дані шляхом явної вказівки умов з'єднання. Таблиці, що з'єднують, перераховані у розділі FROM оператора, умова з'єднання наведена у розділі WHERE. Розділ WHERE, крім умови з'єднання таблиць, може також містити й умови відбору рядків.

Приклад синтаксису запитаСховати

SELECT P.PNUM, P.PNAME, PD.DNUM,

PD.VOLUME

FROM P, PD

WHERE P.PNUM = PD.PNUM;

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

PNUM	PNAME	DNUM	VOLUME
1	Іванов	1	100
1	Іванов	2	200
1	Іванов	3	300
2	Петров	1	150
2	Петров	2	250
3	Сидоров	1	1000

Другий спосіб - природне з'єднання таблиць (спосіб 2 - ключові слова JOIN…USING…). Ключове слово USING дозволяє явно вказати, по яким із загальних колонок таблиць буде вироблятися з'єднання.

Приклад синтаксису запитаСховати

SELECT PNUM, P.PNAME, PD.DNUM,

PD.VOLUME

FROM P JOIN PD USING PNUM;

Третій спосіб - природне з'єднання таблиць (спосіб 3 - ключове слово NATURAL JOIN). У розділі FROM не зазначається, по яких полях виробляється з'єднання. NATURAL JOIN автоматично з'єднує по всіх однакових полях у таблицях.

Приклад синтаксису запитаСховати

SELECT P.PNUM, P.PNAME, PD.DNUM,

PD.VOLUME

FROM P NATURAL JOIN PD;

Арифметичні функції та функції обробки дати й часу

SQL підтримує повний набір арифметичних операцій і математичних функцій для побудови арифметичних виражень над колонками БД (+, -, *, /, ABS, LN, SQRT і т.д.). Перелік основних вбудованих математичних функцій та функцій по роботі з даними типу дата/час ви можете знайти в літератрних джерелах, перелік яких наданий наприкінці модуля.

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

Приклад використання арифметичних функцій та функцій обробки датиСховати

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

SELECT ENAME, HIREDATE,

HIREDATE + 92 DAYS

FROM EMPLOYEE

WHERE HIREDATE + 92 DAYS > SYSDATE

AND DEPNO=30;

Ключове слово SYSDATE завжди повертає поточну дату. У цьому прикладі також показано, як використається арифметичний оператор додавання зі змінними типу "дата". До змінного типу "дата" можна додавати й віднімати з нього ціле число днів, місяців, років, годин, хвилин, секунд, мікросекунд. Для цього використаються відповідні ключові слова (DAY, MONTH і т.д.), що випливають за цілою константою (дробова частина ігнорується, якщо ви вказуєте число з десятковою крапкою). Є обмеження на використання дужок у таких вираженнях (так, висновок у дужки вираження 1 DAYS + 1 YEARS приведе до помилки).

Використання агрегатних функцій у запитах

В мові 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 придушує дублікати

STDDEV([DISTINCT|ALL]X) Обчислює стандартне відхилення на безлічі значень аргументу, що може бути виразом будь-якого типу. Нуль-значення ігноруються, ключове слово DISTINCT придушує дублікати

VARIANCE([DISTINCT|ALL]) Обчислює квадрат дисперсії

Приклади синтаксису запитівСховати

Запит: одержати загальну кількість постачальників (ключове слово COUNT):

SELECT COUNT(*) AS N

FROM P;

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

Запит: одержати загальну, максимальну, мінімальну й середню кількості деталей, що поставляють, (ключові слова SUM, MAX, MIN, AVG):

SELECT SUM(PD.VOLUME) AS SM,

MAX(PD.VOLUME) AS MX,

MIN(PD.VOLUME) AS MN,

AVG(PD.VOLUME) AS AV

FROM PD;

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

SM	MX	MN	AV
2000	1000	100	333,33

Використання агрегатних функцій з угрупованнями

Для виведення результатів даних, сгрупованих за певною умовою, слід використовувати два ключових слова: 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.

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

Самий простий спосіб виконання запиту прямою вказівкою в розділі WHERE вкладеного запиту.

Приклад синтаксису запитаСховати

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

SELECT *

FROM P

WHERE P.STATYS < (SELECT MAX(P.STATUS) FROM P);

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

Результат виконання запиту буде еквівалентний результату такої послідовності дій:

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

Другий спосіб формування підзапиту - використання предиката IN.

Приклад синтаксису запитаСховати

Запит: одержати перелік постачальників, що поставляють деталь номер 2:

SELECT *

FROM P

WHERE P.PNUM IN (SELECT DISTINCT PD.PNUM

FROM PD WHERE PD.DNUM = 2);

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

Результат виконання запиту буде еквівалентний результату такої послідовності дій:

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

Існує ще один спосіб - використання предиката EXIST.

Приклад синтаксису запитаСховати

Запит: одержати перелік постачальників, що поставляють деталь номер 2:

SELECT *

FROM P

WHERE EXIST (SELECT *

FROM PD

WHERE PD.PNUM = P.PNUM AND PD.DNUM = 2);

Результат виконання запиту буде еквівалентний результату такої послідовності дій:

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

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

Може здатися, що запити, які містять кореліруєми підзапити будуть виконуватися повільніше, ніж запити з некореліруємими підзапитами. Насправді це не так, тому що те, як користувач сформулював запит, не визначає, як цей запит буде виконуватися. Мова SQL є не процедурною, а декларативною. Це значить, що користувач, який формулює запит, просто описує, яким повинен бути результат запиту, а як цей результат буде отриманий - за це відповідає сама СКБД.

Приклад синтаксису запита з використанням предиката NOT EXISTСховати

Запит: одержати перелік постачальників, що не поставляють деталь номер 2:

SELECT *

FROM P

WHERE NOT EXIST (SELECT *

FROM PD

WHERE PD.PNUM = P.PNUM AND PD.DNUM = 2);

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

Результуючі таблиці поєднуваних запитів повинні бути сумісні, тобто мати однакову кількість стовпців й однакові типи стовпців у порядку їхнього перерахування. Не потрібно, щоб поєднувані таблиці мали б однакові імена колонок. Це відрізняє операцію об'єднання запитів в 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;