Введення в структуровану мову запитів — SQL 1

Введення в структуровану мову запитів - SQL 1. Елементи мови SQL. 2. Припустимі типи даних. 3. Оператори SQL. 4. Синтаксис оператора вибірки даних. BNF- нотація. 4. Використання імен кореляції (аліасів, псевдонімів). 5. Вбудовані функції.

Елементи мови SQL (Structured Query Language) - фактично стандартна мова доступу до БД. Кожен діалект - це надмножина деякої підмножини стандарту SQL → ускладнює переносимість додатків. Термінологія: РТ SQL "відношення" → "таблиці“ "кортежи" → "рядки“ "атрибути" → "колонки“ Мова SQL є реляційно повною

Припустимі типи даних загальні типи даних строковий числовий дати й часу нуль-значення Тип даних визначає значення й довжину даних, а також формат їхнього подання при візуалізації

Припустимі типи даних char varchar визначена довжина varchar 2 long varchar довільна довжина Строкові дані - послідовність рядків символів

Припустимі типи даних Числові дані integer цілі smallint number decimal з фіксованою крапкою Double Precision Float Real із плаваючою крапкою точність: 10 зн/ц 5 зн/ц 15 зн/ц заданої до 15 зн/ц від 22 до 53 від 1 до 21 за замовчуванням

Припустимі типи даних дата й час datestamp (timestamp) подання дати й часу date подання дати time подання часу

Припустимі типи даних Константи, вираження, системні змінні Константи - єдине значення: строкові, числові й дата/час. Системні змінні можна використовувати у виразах замість імен колонок і констант: NULL - для подання невизначених значень; ROWID - внутрішній системний номер рядка; USER - ім'я користувача, активного в цей момент; SYSDATETIME - системний поточний час і дата; SYSDATE - системна поточна дата; SYSTIME - системний поточний час;

Припустимі типи даних Виразом в SQL є ітем або комбінація ітемів з припустимими для них операціями, що дає єдине значення: імена колонок, константи, зв'язані змінні, результати обчислень функцій, системні змінні й інші вирази. Якщо один з ітемів має нуль-значення, то результат виразу також має нуль-значення.

Оператори SQL Оператори DDL (Data Definition Language) оператори визначення об'єктів БД: CREATE TABLE - створити таблицю ALTER TABLE - змінити таблицю DROP TABLE - видалити таблицю CREATE DOMAIN - створити домен ALTER DOMAIN - змінити домен DROP DOMAIN - видалити домен CREATE COLLATION - створити послідовність DROP COLLATION - видалити послідовність CREATE VIEW - створити подання DROP VIEW - видалити подання

Оператори SQL Оператори DML (Data Manipulation Language) - оператори маніпулювання даними: SELECT - відібрати рядок з таблиць INSERT - додати рядок в таблицю UPDATE - змінити рядок в таблиці DELETE - видалити ряодк в таблиці COMMIT - зафіксувати внесені зміни ROLLBACK - відкотити внесені зміни

Оператори SQL Оператори захисту й керування даними: CREATE ASSERTION - створити обмеження DROP ASSERTION - видалити обмеження GRANT - надати привілею користувачеві або додатку на маніпулювання об'єктами REVOKE - скасувати привілею користувача або додатка

Синтаксис оператора вибірки даних. BNF- нотація. Стандартні форми Бэкуса-Наура (BNF) - умовні позначки для опису синтаксису операторів. • Символ ": : =" означає рівність по визначенню. • Ключові слова записуються прописними буквами. • Заповнювачі конкретних значень елементів і змінних записуються курсивом. • Необов'язкові елементи оператора укладені у квадратні дужки []. • Вертикальна риса | - всі попередні їй елементи списку є необов'язковими й можуть бути замінені будь-яким іншим елементом після цієї риси.

Синтаксис оператора вибірки даних. BNF- нотація. • Фігурні дужки {} - все, що міститься усередині них, є єдиним цілим. • Три крапок ". . . " - попередня частина оператора може бути повторена будь-яка кількість разів. • Багато крапок, усередині якого перебуває кома ". , . . " - попередня частина оператора, яка складає з декількох елементів, розділених комами, може мати довільне число повторень. Кому не можна ставити після останнього елемента!!!! • Круглі дужки є елементом оператора.

Синтаксис оператора вибірки даних. BNF- нотація. Синтаксис оператора вибірки Вираз-Select-вираз : : = SELECT [ALL | DISTINCT] {{{Скалярне вираз | Функція агрегування | Вираз-Select-вираз} [AS Ім'я стовпця]}. , . . } | {{Ім'я таблиці | І’мя кореляції}. *} | * FROM { {Ім'я таблиці [AS] [Ім'я кореляції] [(Ім'я стовпця. , . . )]} | {Вираз-Select- вираз [AS] Ім'я кореляції [(Ім'я стовпця. , . . )]} | З'єднана таблиця }. , . . [WHERE Умовний вираз] [GROUP BY {[{Ім'я таблиці | І'мя кореляції}. ] Ім'я стовпця}. , . . ] [HAVING Умовний вираз]

Синтаксис оператора вибірки даних. BNF- нотація. Функція агрегування : : = COUNT (*) | { {COUNT | MAX | MIN | SUM | AVG} ([ALL | DISTINCT] Скалярний вираз) } Конструктор значень таблиці : : = VALUES Конструктор значень рядка. , . . Конструктор значень рядка : : = Елемент конструктора | конструктора. , . . ) | Вираз-Select- вираз (Елемент

Синтаксис оператора вибірки даних. BNF- нотація. Синтаксис з'єднаних таблиць З'єднана таблиця : : = Перехресне з'єднання | Природне з'єднання | З'єднання за допомогою предиката | З'єднання за допомогою імен стовпців | З'єднання об'єднання Тип з'єднання : : = INNER | LEFT [OUTER] | RIGTH [OUTER] | FULL [OUTER] Перехресне з'єднання : : = Таблиця А CROSS JOIN Таблиця В Природне з'єднання : : = Таблиця А [NATURAL] [Тип з'єднання] JOIN Таблиця В

Синтаксис оператора вибірки даних. BNF- нотація. З'єднання за допомогою предиката : : = Таблиця А [Тип з'єднання] JOIN Таблиця В ON Предикат З'єднання за допомогою імен стовпців : : = Таблиця А [Тип з'єднання] JOIN Таблиця В USING (Ім'я стовпця. , . . ) З'єднання об'єднання : : = Таблиця А UNION JOIN Таблиця В

Синтаксис оператора вибірки даних. BNF- нотація. CROSS JOIN - Перехресне з'єднання повертає просто декартовий добуток таблиць. NATURAL JOIN - Природне з'єднання відбувається по всіх стовпцях таблиць А и В, що мають однакові імена. JOIN. . . ON - З'єднання за допомогою предиката з'єднує рядки таблиць А и В за допомогою зазначеного предиката. JOIN. . . USING - З'єднання за допомогою імен стовпців з'єднує відношення подібно природному з'єднанню по тим загальним стовпцям таблиць А и Б, які зазначені в списку USING.

Синтаксис оператора вибірки даних. BNF- нотація. OUTER - Ключове слово OUTER (зовнішній) не є обов'язковими, воно не використовується ні в яких операціях з даними. INNER - Тип з'єднання "внутрішнє". У таблицях А и В з'єднуються тільки ті рядки, для яких знайдене збіг. LEFT (OUTER) - Тип з'єднання "ліве (зовнішнє)". Ліве з'єднання таблиць А и В містить у собі всі рядки з лівої таблиці А и ті рядки із правої таблиці В, для яких виявлений збіг.

Синтаксис оператора вибірки даних. BNF- нотація. RIGHT (OUTER) - Тип з'єднання "праве (зовнішнє)". Праве з'єднання таблиць А и В містить у собі всі рядки із правої таблиці В и ті рядки з лівої таблиці А, для яких виявлений збіг. FULL (OUTER) - Тип з'єднання "повне (зовнішнє)". Це комбінація лівого й правого з'єднань. UNION JOIN - З'єднання об'єднання. Воно включає тільки ті рядки з таблиць А и В, для яких не знайдене збігів.

Синтаксис оператора вибірки даних. BNF- нотація. Синтаксис умовних виразів розділу WHERE Умовне вираз : : = [ ( ] [NOT] {Предикат порівняння | Предикат between | Предикат in | Предикат like | Предикат null | Предикат кількісного порівняння | Предикат exist | Предикат unique | Предикат match | Предикат overlaps} [{AND | OR} Умовне вираз] [ ) ] [IS [NOT] {TRUE | FALSE | UNKNOWN}] Предикат порівняння : : = Конструктор значень рядка {= | < | > | <= | >= | <>} Конструктор значень рядка

Синтаксис оператора вибірки даних. BNF- нотація. Предикат between : : = Конструктор значень рядка [NOT] BETWEEN Конструктор значень рядка AND Конструктор значень рядка Приклад. PD. VOLUME BETWEEN 10 AND 100 Предикат in : : = Конструктор значень рядка [NOT] IN {(Selectвираз) | (Вираз для обчислення значення. , . . )} Приклад. P. PNUM IN (SELECT PD. PNUM FROM PD WHERE PD. DNUM=2)

Синтаксис оператора вибірки даних. BNF- нотація. Предикат like : : = Вираз для обчислення значення рядка-пошуку [NOT] LIKE Вираз для обчислення значення рядка-шаблона [ESCAPE Символ] Предикат null : : = Конструктор значень рядка IS [NOT] NULL Предикат кількісного порівняння : : = Конструктор значень рядка {= | < | > | <= | >= | <>} {ANY | SOME | ALL} (Select-вираз)

Синтаксис оператора вибірки даних. BNF- нотація. Предикат exist : : = EXIST (Select-вираз) Предикат unique : : = UNIQUE (Select-вираз) Предикат match : : = Конструктор значень рядка MATCH [UNIQUE] [PARTIAL | FULL] (Select-вираз) Предикат overlaps : : = Конструктор значень рядка OVERLAPS Конструктор значень рядка

Оператори SQL Приклади використання операторів маніпулювання даними INSERT - вставка рядків у таблицю Приклад 1. Вставка одного рядка в таблицю: INSERT INTO P (PNUM, PNAME) VALUES (4, "Іванов");

Оператори SQL Приклад 2. Вставка в таблицю декількох рядків, обраних з іншої таблиці (у таблицю TMP_TABLE уставляються дані про постачальників з таблиці P, що мають номери, більші 2): INSERT INTO TMP_TABLE (PNUM, PNAME) SELECT PNUM, PNAME FROM P WHERE P. PNUM>2;

Оператори SQL UPDATE - відновлення рядків у таблиці Приклад 3. Відновлення декількох рядків у таблиці: UPDATE P SET PNAME = "Пушников" WHERE P. PNUM = 1;

Оператори SQL DELETE - видалення рядків у таблиці Приклад 4. Видалення декількох рядків у таблиці: DELETE FROM P WHERE P. PNUM = 1; Приклад 5. Видалення всіх рядків у таблиці: DELETE FROM P;

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

Оператори SQL Відбір даних з однієї таблиці Синтаксис: SELECT…FROM… Приклад. Вибрати всі дані з таблиці Р: SELECT* FROM P; Приклад. Вибрати всі рядки з таблиці Р, що задовольняють деякій умові: SELECT* FROM P WHERE P. . . PNUM>2;

Оператори SQL Приклад. Вибрати деякі колонки з вихідної таблиці: SELECT P. NAME FROM P; Приклад. Вибрати деякі колонки з вихідної таблиці, видаливши з результату повторювані рядки: SELECT DISTINCT P. NAME FROM P;

Оператори SQL Приклад. Використання скалярних виразів і перейменувань колонок у запитах : SELECT TOVAR. TNAME, TOVAR. KOL, TOVAR. PRICE, "="AS EQU, TOVAR. KOL*TOVAR. PRICE AS SUMMA FROM TOVAR; TNAME Болт Гайка Гвинт KOL 10 20 30 PRICE 100 200 300 EQU = = = SUMMA 1000 4000 9000

Оператори SQL Приклад. Упорядкування результатів запиту: SELECT PD. PNUM, PD. DNUM, PD. VOLUME FROM PD ORDER BY DNUM; PNUM 1 2 3 1 2 1 DNUM 1 1 1 2 2 3 VOLUME 100 150 1000 250 300

Оператори SQL Приклад. Природне з'єднання таблиць (спосіб 2): SELECT PNUM, P. PNAME, PD. DNUM, PD. VOLUME FROM P JOIN PD USING PNUM; Приклад. Природне з'єднання таблиць (спосіб 3): SELECT P. PNUM, P. PNAME, PD. DNUM, PD. VOLUME FROM P NATURAL JOIN PD;

Оператори SQL Приклад. Природне з'єднання трьох таблиць: SELECT P. PNAME, D. DNAME, PD. VOLUME FROM P NATURAL JOIN PD NATURAL JOIN D; PNAME Іванов Петров Сідоров DNAME Болт Гайка Гвинт Болт VOLUME 100 200 300 150 1000

Оператори SQL Приклад. Прямий добуток таблиць: SELECT P. PNUM, P. PNAME, D. DNUM, D. DNAME FROM P, D; PNUM 1 1 2 2 3 3 PNAME Іванов Петров Сідоров DNUM 1 2 1 2 DNAME Болт Гайка

Оператори SQL Відношення P (Постачальники) PNUM PNAME PSTATUS 1 Іванов 4 2 Петров 1 3 Сідоров 2 Відношення D (Деталі) DNUM DNAME DSTATUS 1 Болт 3 2 Гайка 2 3 Гвинт 1

Оператори SQL "які постачальники мають право поставляти які деталі? " : SELECT P. PNUM, P. PNAME, P. PSTATUS, D. DNUM, D. DNAME, D. DSTATUS FROM P, D WHERE P. PSTATUS >= D. DSTATUS; PNUM PNAME PSTATUS DNUM DNAME DSTATUS 1 Іванов 4 1 Болт 3 1 Іванов 4 2 Гайка 2 1 Іванов 4 3 Гвинт 1 2 Петров 1 3 Гвинт 1 3 Сидоров 2 2 Гайка 2 3 Сидоров 2 3 Гвинт 1

Використання імен кореляції (аліасів, псевдонімів) Імена кореляції (аліаси, псевдоніми) дозволяють розрізняти копії та таблиці-оригінали. Імена кореляції вводяться у розділі FROM і йдуть через пробіл після імені таблиці. Імена кореляції повинні використовуватися як префікс перед ім'ям стовпця й відокремлюються від імені стовпця крапкою. Визначення імені кореляції діє тільки під час виконання запиту.

Оператори SQL PNAME 1 PSTATUS 1 PNAME 2 PSTATUS 2 Іванов 4 Петров 1 Приклад. Відібрати всі пари постачальників Іванов 4 Сидоров 2 таким чином, щоб перший постачальник у парі мав Петров 1 статус, Сидоров од 2 більший статусу другого постачальника: SELECT P 1. PNAME AS PNAME 1, P 1. PSTATUS AS PSTATUS 1, P 2. PNAME AS PNAME 2, P 2. PSTATUS AS PSTATUS 2 FROM P P 1, P P 2 WHERE P 1. PSTATUS 1 > P 2. PSTATUS 2;

Вбудовані функції Арифметичні функції ABS(X)Повертає абсолютне значення числа Х ACOS(X)/ ASIN(X)/ ATAN(X) Повертає арккосинус/-сінус/-тангенс числа Х COS(X)/SIN(X)/TAN(X) Повертає косинус/сінус/тангенс числа Х EXP(X) Повертає експоненту числа Х LN(X) Повертає натуральний логарифм числа ROUND(X, n) Округляє число Х до числа з n знаками після крапки SQRT(X) Повертає квадратний корінь числа Х LOG(a, X) Повертає логарифм числа Х по

Вбудовані функції Функції для обробки дати SYSDATE Повертає поточну дату й час ROUND(D[, F])/TRANC(D[, F]) Округляє/ усікає значення дати D відповідно до заданого шаблона NEXT_DAY(D, S) Повертає дату дня, що є першим днем, більше пізнім, чим поточна дата з назвою S

Вбудовані функції Приклад. Список нових службовців, надійшли за останній квартал в організацію: SELECT ENAME, HIREDATE + 92 DAYS FROM EMPLOYEE WHERE HIREDATE + 92 DAYS > SYSDATE AND DEPNO=30; що

Вбудовані функції Агрегатні функції AVG(X) = AVG(ALL X) AVG(DISTINCT X) Обчислює середнє значення аргументу, що може бути виразом будь-якого типу. COUNT (X) - Обчислює числа ітемів. MAX(X) - максимальне значення аргументу. MIN(X) - мінімальне значення аргументу SUM(X) аргументу. STDDEV([DISTINCT|ALL]X) Обчислює стандартне відхилення на безлічі значень аргументу

Вбудовані функції Приклад. Одержати загальну постачальників: SELECT COUNT(*) AS N FROM P; кількість Приклад. Одержати загальну, максимальну, мінімальну й середню кількості деталей, що поставляють: SELECT SUM(PD. VOLUME) AS SM, MAX(PD. VOLUME) AS MX, MIN(PD. VOLUME) AS MN, AVG(PD. VOLUME) AS AV FROM PD;

Вбудовані функції Використання агрегатних функцій з угрупованнями Приклад. Для кожної деталі одержати сумарну кількість, що поставляється : SELECT DNUM, SUM(PD. VOLUME) AS SM FROM PD GROUP BY PD. DNUM;

Вбудовані функції Приклад. Одержати номери деталей, сумарна кількість поставки яких перевершує 400: SELECT PD. DNUM, SUM(PD. VOLUME) AS SM FROM PD GROUP BY PD. DNUM HAVING SUM(PD. VOLUME) > 400;