Теоретичний матеріал та презентація до Теми №5

2. Основні оператори SQL

Класифікація операторів мови SQL

За функціональним призначенням підмножини команд SQL поділяються на 5 основних категорій [8; 14]:

DDL (Data Definition Language — мова визначення даних): оператори створення, зміни та видалення схем, таблиць, індексів, просторових обмежень та уявлень (CREATE, ALTER, DROP, TRUNCATE).

DML (Data Manipulation Language — мова маніпулювання даними): оператори додавання, оновлення та вилучення атрибутивних і просторових записів (INSERT, UPDATE, DELETE).

DQL (Data Query Language — мова запитів): оператор вибірки даних (SELECT), що становить основу аналітичної роботи в СУБД.

DCL (Data Control Language — мова управління доступом): адміністрування прав доступу користувачів та ролей до об'єктів бази геоданих (GRANT, REVOKE).

TCL (Transaction Control Language — мова управління транзакціями): забезпечення атомарності та цілісності при групових операціях з даними (COMMIT, ROLLBACK, SAVEPOINT).

Функціонування бази геоданих базується на трьох ключових групах операторів SQL: DDL (визначення структури), DML (управління вмістом) та DQL (формування запитів) [8; 14]. У геоінформаційних СУБД ці оператори розширені для роботи з об'єктами OGC Simple Features [27; 29].

Оператори визначення даних (DDL — Data Definition Language)

DDL-оператори відповідають за формування фізичної схеми бази геоданих: створення, модифікацію та видалення таблиць, просторових колон, індексів та обмежень цілісності [8].

·        CREATE TABLE: створення нової таблиці з визначенням типів даних (числові, текстові, часові, просторові) та обмежень (Primary Key, Foreign Key, CHECK, NOT NULL).

·        ALTER TABLE: модифікація структури існуючої таблиці (додавання нових атрибутів, зміна типів даних, додавання просторових індексів чи обмежень).

·        DROP TABLE: повне вилучення таблиці та її даних із баз геоданих.

·        TRUNCATE TABLE: миттєве очищення всіх рядків таблиці із збереженням її DDL-структури.

Оператори маніпулювання даними (DML — Data Manipulation Language)

DML-оператори забезпечують внесення, оновлення та вилучення атрибутивних і просторових записів у таблицях [14].

·        INSERT INTO: додавання нових рядків. Просторова геометрія передається у текстовому форматі WKT (Well-Known Text) або EWKT за допомогою функцій ST_GeomFromText() або ST_SetSRID() [27; 29].

·        UPDATE: коригування значення атрибутів або геометричного положення об'єктів за визначеною умовою.

·        DELETE FROM: вилучення записів, що відповідають умові предикату.

Оператор вибірки (DQL — Data Query Language): Структура SELECT

Оператор SELECT є головним інструментом геоаналітики, витягування та трансформації даних [8].

Написання команд SQL

У цьому розділі ми стисло описуємо структуру інструкції SQL та позначення, що використовуються для визначення формату різних конструкцій SQL. Інструкція SQL складається із зарезервованих слів і слів, визначених користувачем. Зарезервовані слова є незмінною частиною мови SQL і мають фіксоване значення. Їх слід писати точно так, як вимагає стандарт; вони не можуть бути розірвані переходом на новий рядок. Слова, визначені користувачем, створюються самим користувачем (відповідно до певних синтаксичних правил) і позначають назви різних об'єктів бази даних, таких як таблиці, стовпці, представлення, індекси тощо. Розміщення слів в інструкції також підпорядковується певним синтаксичним правилам. Хоча стандарт цього не вимагає, багато діалектів SQL передбачають використання спеціального символу для позначення завершення кожної інструкції (зазвичай це крапка з комою «;»).

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

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

•    кожна частина (пункт) інструкції має починатися з нового рядка;

•    початок кожної частини має бути вирівняний відносно початку інших частин;

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

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

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

Вибір рядків (умова WHERE)

Однак часто виникає потреба обмежити кількість рядків, що отримуються в результаті запиту. Це можна зробити за допомогою умови WHERE, яка складається з ключового слова WHERE та умови пошуку, що визначає, які саме рядки слід вибрати. Існує п’ять основних умов пошуку (або предикатів, згідно з термінологією ISO):

•    Порівняння — порівняння значення одного виразу зі значенням іншого виразу.

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

•    Належність до множини — перевірка того, чи дорівнює значення виразу одному зі значень у заданій множині.

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

•    Null — перевірка того, чи містить стовпець значення NULL (невідоме значення).

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

У SQL доступні такі прості оператори порівняння:

= дорівнює

< > не дорівнює (стандарт ISO)

< менше ніж

> більше ніж

! = не дорівнює (допускається в деяких діалектах)

< = менше або дорівнює

> = більше або дорівнює

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

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

Стандарт ISO визначає п’ять агрегатних функцій:

COUNT — повертає кількість значень у вказаному стовпці;

SUM — повертає суму значень у вказаному стовпці;

AVG — повертає середнє значення у вказаному стовпці;

MIN — повертає найменше значення у вказаному стовпці;

MAX — повертає найбільше значення у вказаному стовпці.