Агрегатні функції системи компоновки даних. Застосування агрегатних функцій і вкладених запитів в операторі вибору Яка агрегатна функція sql знаходить максимальне значення

Багато запити до БД не потребують тій мірі деталізації, яку забезпечують SQL-запити, розглянуті в попередніх прикладах. Так, у всіх запитах перерахованих далі, потрібно дізнатися лише одне або кілька значень, які підсумовують інформацію, що міститься в БД:

1) яка сума доходів у всіх жителів?
2) який найбільший і найменший спільний дохід окремого жителя?
3) який середньодушовий дохід жителя Зеленограда?
4) який середньодушовий дохід жителів кожної квартири?
5) скільки жителів в кожній квартирі?

Мовою SQL запити такого типу можна створювати за допомогою агрегатних функцій і пропозицій GROUP BY і HAVING, використовуваних в операторі SELECT.

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

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

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

Щоб обчислити середній дохід жителя Зеленограда, потрібен такий запит:

SELECT 'середньодушовий дохід, AVG (SUMD) FROM PERSON

В SQL є шість агрегатних функцій, які дозволяють отримувати різні види підсумкової інформації (рис. 3.16):

SUM () обчислює суму всіх значень, що містяться в стовпці;

AVG () обчислює середнє серед значень, що містяться в стовпці;

- MIN () знаходить найменше серед всіх значень, що містяться в стовпці;
- МАХ () знаходить найбільше серед усіх значень, що містяться в стовпці;
- COUNT () підраховує кількість значень, що містяться в стовпці;

COUNT (*) підраховує кількість рядків у таблиці результатів запиту.

Аргументом агрегатної функції може бути просте ім'я стовпця, як в попередньому прикладі, або вираз, як у наступному запиті, що задає обчислення середньодушового податку:

SELECT AVG (SUMD * 0.13)

Рис. 3.16.

При виконанні цього запиту створюється тимчасовий стовпець, що містить значення (SUMD * 0.13) для кожного рядка таблиці PERSON, а потім обчислюється середнє значення тимчасового стовпчика.

Суму доходів у всіх жителів Зеленограда можна обчислити за допомогою агрегатної функції SUM:

SELECT SUM (SUMD) FROM PERSON

Агрегатна функція може бути використана і для обчислення підсумків по таблиці результатів, отриманої з'єднанням декількох вихідних таблиць. Наприклад, можна обчислити загальну суму доходу, яка отримана жителями від джерела з назвою «Стипендія»:

SELECT SUM (MONEY)

FROM PROFIT, HAVE_D

WHERE PROFIT.ID \u003d HAVE_D.ID

AND PROFIT.SOURCE ^ Стипендія '

Агрегатні функції MIN () і MAX () дозволяють знайти відповідно найменше і найбільше значення в таблиці. При цьому стовпець може містити числові або строкові значення або значення дати або часу.

Наприклад, можна визначити:

(А) найменший спільний дохід, отриманий жителями, і найбільший податок, який підлягає сплаті:

SELECT MIN (SUMD), MAX (SUMD * 0.13)

(Б) дати народження найстарішого і наймолодшого жителя:

SELECT MIN (RDATE), MAX (RDATE)

(В) прізвища, імена та по батькові самого першого і останнього жителів в списку, що згруповані за алфавітом:

SELECT MIN (FIO), MAX (FIO)

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

При використанні функції MIN () і МАХ () із строковими даними результат порівняння двох рядків залежить від використовуваної таблиці кодування символів.

Агрегатна функція COUNT () підраховує кількість значень в стовпці будь-якого типу:

(А) скільки квартир в 1-му мікрорайоні?

SELECT COUNT (ADR)

WHERE ADR LIKE *%, 1_

(Б) скільки жителів мають джерела доходу?

SELECT C0UNT (DISTINCT NOM)

(В) скільки джерел доходу використовуються жителями?

SELECT COUNT (DISTINCT ID)

Ключове слово «DISTINCT» вказує, що підраховуються неповторювані значення в стовпці.

Спеціальна агрегатна функція COUNT (*) підраховує рядки в таблиці результатів, а не значення даних:

(А) скільки квартир у 2-му мікрорайоні?

WHERE ADR LIKE "%, 2 _-% '

(Б) скільки джерел доходу у Іванова Івана Івановича?

FROM PERSON, HAVE_D

WHERE FIO \u003d "Іванов Іван Іванович"

AND PERSON.NOM \u003d HAVE_D.NOM

(В) скільки жителів проживає в квартирі за певною адресою?

SELECT COUNT (*) FROM PERSON WHERE ADR \u003d "Зеленоград, 1001-45 '

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

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

SELECT AVG (SUMD), SUM (SUMD), (100 * AVG (MONEY / SUMD))

FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM \u003d HAVE_D.NOM AND HAVE_D.ID \u003d PROFIT.ID

Без агрегатних функцій запит виглядав би так:

SELECT SUMD, SUMD, M0NEY / SUMD FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM \u003d HAVE_D.NOM AND HAVE_D.ID \u003d PROFIT.ID

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

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

SELECT МАХ (SUMD) -MIN (SUMD)

Однак агрегатна функція не може бути аргументом для іншої агрегатної функції, тобто заборонені вкладені агрегатні функції.

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

SELECT FIO, SUM (SUMD)

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

Сказане не стосується випадків обробки підзапитів і запитів з угрупованням.

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

Наприклад, згрупуємо відношення R1 за значенням стовпця Дисципліна. Ми отримаємо 4 групи, для яких можна обчислити деякі групові значення, наприклад кількість кортежів в групі, максимальне або мінімальне значення стовпця Оцінка.

Це робиться за допомогою агрегатних функцій. Агрегатні функції обчислюють одиночне значення для всієї групи таблиці. Список цих функцій представлений в таблиці 5.7.

Таблиця 5.7.агрегатні функції


R1
	ПІБ	дисципліна	оцінка
Група 1	Петров П. І.	Бази даних
	Сидоров К. А.	Бази даних
	Миронов А. В.	Бази даних
	Степанова К. Е.	Бази даних
	Крилова Т. С.	Бази даних
	Владимиров В. А.	Бази даних
Група 2	Сидоров К. А.	теорія інформації
	Степанова К. Е.	теорія інформації
	Крилова Т. С.	теорія інформації
	Миронов А. В.	теорія інформації	Null
Група 3	Трофимов П. А.	Мережі та телекомунікації
	Іванова Є. А.	Мережі та телекомунікації
	Уткіна Н. В.	Мережі та телекомунікації
Група 4	Владимиров В. А.	Англійська мова
	Трофимов П. А.	Англійська мова
	Іванова Є. А.	Англійська мова
	Петров П. І.	Англійська мова	i

Агрегатні функції використовуються подібно іменам полів в операторі SELECT, але з одним винятком: вони беруть ім'я поля як аргумент. З функціями SUM і AVG можуть використовуватися тільки числові поля. З функціями COUNT, MAX і MIN можуть використовуватися як числові, так і символьні поля. При використанні з символьними полями МАХ і MIN транслюватимуть їх в еквівалент ASCII коду і обробляти в алфавітному порядку. Деякі СУБД дозволяють використовувати вкладені агрегати, але це є відхиленням від стандарту ANSI з усіма наслідками, що випливають звідси наслідками.

Наприклад, можна обчислити кількість студентів, які здавали іспити з кожної дисципліни. Для цього треба виконати запит з угрупованням по полю «Дисципліна» і вивести в якості результату назва дисципліни і кількість рядків у групі по даній дисципліні. Застосування символу * як аргумент функції COUNT означає підрахунок всіх рядків в групі.

SELECT R1.Дісціпліна. СОUNТ (*)

GROUP BY R1 Дисципліна

результат:

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

SELECT R1.Дісціпліна. COUNT (*)

FROM R1 WHERE R1.

Оцінка IS NOT NULL

GROUP BY Rl.Дісціпліна

Отримаємо результат:

В цьому випадку рядок зі студентом


Миронов А, В.	теорія інформації	Null

не потрапить в набір кортежів перед угрупованням, тому кількість кортежів в групі для дисципліни «Теорія інформації» буде на 1 менше.

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

Звернувшись знову до бази даних «Сесія» (таблиці Rl, R2, R3), знайдемо кількість успішно складених іспитів:

WHERE Оцінка\u003e 2:

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

SELECT Rl.Дісціпліна.

COUNT (DISTINCT R1.Оценка)

WHERE R1.Оценка IS NOT NULL

GROUP BY Rl.Дісціпліна

результат:

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

SELECT R2.Группа. R1.Дісціпліна. COUNT (*), АVР (Оцінка)

WHERE Rl.ФІО \u003d R2.ФІО AND

Rl.Оценка IS NOT NULL AND

Rl.Оценка\u003e 2

GROUP BY R2.Группа. Rl.Дісціпліна

результат:

Ми не можемо використовувати агрегатні функції в реченні WHERE, тому що предикати оцінюються в термінах одиночного рядка, а агрегатні функції - в термінах груп рядків.

Пропозиція GROUP BY дозволяє визначати підмножину значень в особливому полі в термінах іншого поля і застосовувати функцію агрегату до підмножини. Це дає можливість об'єднувати поля і агрегатні функції в єдиній пропозиції SELECT. Агрегатні функції можуть застосовуватися як в вираженні виведення результатів рядки SELECT, так і в виразі умови обробки сформованих груп HAVING. У цьому випадку кожна агрегатна функція обчислюється для кожної виділеної групи. Значення, отримані при обчисленні агрегатних функцій, можуть бути використані для виведення відповідних результатів або для умови відбору груп.

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

SELECT R2.Группа

WHERE Rl.ФІО \u003d R2.ФІО AND

Rl.Оценка \u003d 2

GROUP BY R2.Группа. R1.Дісціпліна

HAVING count (*)\u003e 1

Надалі в якості прикладу будемо працювати не з БД «Сесія», а з БД «Банк», що складається з однієї таблиці F, в якій зберігається відношення F, що містить інформацію про рахунки в філіях деякого банку:

F \u003d ;

Q \u003d (Філія, Місто);

оскільки на цій базі можна яскравіше проілюструвати роботу з агрегатними функціями і угрупованням.

Наприклад, припустимо, що ми хочемо знайти сумарний залишок на рахунках в філіях. Можна зробити роздільний запит для кожного з них, вибравши SUM (Залишок) з таблиці для кожної філії. GROUP BY, однак, дозволить помістити їх все в одну команду:

SELECT Філія, SUM

GROUP BY Філія:

GROUP BY застосовує агрегатні функції незалежно для кожної групи, яка визначається за допомогою значення поля Філія. Група складається з рядків з однаковим значенням поля Філія, і функція SUM застосовується окремо для кожної такої групи, тобто сумарний залишок на рахунках підраховується окремо для кожної філії. Значення поля, до якого застосовується GROUP BY, має, за визначенням, тільки одне значення на групу виводу, як і результат роботи агрегатної функції. Тому ми можемо поєднати в одному запиті агрегат і поле. Ви можете також використовувати GROUP BY з декількома полями.

Припустимо, що ми хотіли б побачити тільки ті сумарні значення залишків на рахунках, які перевищують $ 5000. Щоб побачити сумарні залишки понад $ 5000, необхідно використовувати пропозицію HAVING. Пропозиція HAVING визначає критерії, які використовуються, щоб видаляти певні групи з виводу, точно так само як пропозиція WHERE робить це для індивідуальних рядків.

Правильною командою буде наступна:

SELECT Філія, SUM (Залишок)

GROUP BY Філія

HAVING SUM (Залишок)\u003e 5000;

Аргументи в пропозиції HAVING підкоряються тим же самим правилам, що і в реченні SELECT, де використовується GROUP BY. Вони повинні мати одне значення на групу виводу.

Наступна команда буде заборонена:

SELECT Філіал.SUM (Залишок)

FROM F GROUP BY Філія

HAVING ДатаОткритія \u003d 27/12/1999;

Поле ДатаОткритія не може бути використано в пропозиції HAVING, тому що воно може мати більше ніж одне значення на групу виводу. Щоб уникнути такої ситуації, пропозиція HAVING має посилатися тільки на агрегати і поля, обрані GROUP BY. Є правильний спосіб зробити вищезгаданий запит:

SELECT Філія, SUM (Залишок)

WHERE ДатаОткритія \u003d "27/12/1999"

GROUP BY Філія;

Зміст даного запиту наступний: знайти суму залишків по кожній філії рахунків, відкритих 27 грудня 1999 року.

Як і говорилося раніше, HAVING може використовувати тільки аргументи, які мають одне значення на групу виводу. Практично, посилання на агрегатні функції - найбільш загальні, а й поля, обрані за допомогою GROUP BY, також допустимі. Наприклад, ми хочемо побачити сумарні залишки на рахунках філій у Санкт-Петербурзі, Пскові і Урюпинську:

SELECT Філіал.SUМ (Залишок)

WHERE F.Філіал \u003d Q.Філіал

GROUP BY Філія

HAVING Філія IN ( "Санкт-Петербург". "Псков". "Урюпінськ");

Тому в арифметичних виразах предикатів, що входять в умову вибірки розділу HAVING, прямо можна використовувати тільки специфікації стовпців, зазначених в якості стовпців групування в розділі GROUP BY. Інші стовпці можна специфікувати тільки всередині специфікацій агрегатних функцій COUNT, SUM, AVG, MIN і MAX, що обчислюють в даному випадку деякий агрегатний значення для всієї групи рядків. Аналогічно іде справа з підзапитах, що входять в предикати умови вибірки розділу HAVING: якщо в підзапиті використовується характеристика поточної групи, то вона може здаватися тільки шляхом посилання на стовпці групування.

Результатом виконання розділу HAVING є згрупована таблиця, яка містить тільки ті групи рядків, для яких результат обчислення умови пошуку є TRUE. Зокрема, якщо розділ HAVING присутній в табличному вираженні, що не містить GROUP BY, то результатом його виконання буде або порожня таблиця, або результат виконання попередніх розділів табличного вираження, що розглядається як одна група без стовпців групування.

Вкладені запити SQL

Тепер повернемося до БД «Сесія» і розглянемо на її прикладі використання вкладених запитів.

За допомогою SQL можна вкладати запити всередину один одного. Зазвичай внутрішній запит генерує значення, яке перевіряється в предикате зовнішнього запиту (в реченні WHERE або HAVING), що визначає, вірно воно чи ні. Спільно з підзапитом можна використовувати предикат EXISTS, який повертає істину, якщо висновок підзапиту не пустили.

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

FROM Rl AS A, Rl AS В

FROM Rl A. Rl В:

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

Наприклад, покажемо, як виглядають на SQL деякі запити до БД «Сесія»:

Список тих, хто здав всі покладені іспити.

WHERE Оцінка\u003e 2

HAVING COUNT (*) \u003d (SELECT COUNT (*)

WHERE R2.Группа \u003d R3.Группа AND ФІОа.ФІО)

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

Список тих, хто повинен був здавати екзамен з БД, але поки ще не здавав.

SЕLЕСТФІО

WHERE R2.Fpynna \u003d R3.Группа AND Дисципліна \u003d "БД" AND NOT EXISTS

(SELECT ПІБ FROM Rl WHERE ПІБ \u003d а.ФІО AND Дисципліна \u003d "БД")

Предикат EXISTS (SubQuery) правдивий, коли підзапит SubQuery не пустили, тобто містить хоча б один кортеж, в іншому випадку предикат EXISTS хибна.

Предикат NOT EXISTS назад - правдивий тільки тоді, коли підзапит SubQuery порожній.

Зверніть увагу, яким чином NOT EXISTS з вкладеним запитом дозволяє обійтися без операції різниці відносин. Наприклад, формулювання запиту зі словом «все» може бути виконана як би з подвійним запереченням. Розглянемо приклад бази, яка моделює поставку окремих деталей окремими постачальниками, вона представлена \u200b\u200bодним відношенням SP «Постачальники-деталі» зі схемою

SP (Номер_поставщіка. Номер_деталі) Р (номер_деталі. Найменування)

Ось яким чином формулюється відповідь на запит: «Знайти постачальників, які постачають всі деталі».

SELECT DISTINCT НОМЕР_ПОСТАВЩІКА FROM SP SP1 WHERE NOT EXISTS

(SELECT номер_деталі

FROM P WHERE NOT EXISTS

(SELECT * FROM SP SP2

WHERE SР2.номер_поставщіка \u003d SР1.номер_поставщіка AND

sр2.номер_деталі \u003d Р.номер_деталі)):

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

SELECT DISTINCT Номер_поставщіка

GROUP BY Номер_поставщіка

HAVING CounKDISTINCT номер_деталі) \u003d

(SELECT Count (номер_деталі)

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

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

Наприклад, знайдемо студентів, які здали всі іспити на оцінку не нижче ніж «добре». Працюємо з тією ж базою «Сесія», але додамо до неї ще одне відношення R4, яке характеризує здачу лабораторних робіт протягом семестру:

R 1 \u003d (ПІБ, Дисципліна, Оцінка);

R 2 \u003d (ПІБ, Група);

R 3 \u003d (Групи, Дисципліна)

R 4 \u003d (ПІБ, Дисципліна, Номер_лаб_раб, Оцінка);

Select R1.ФІО From R1 Where 4\u003e \u003d All (Select Rl.Оценка

Where R1.Фіо \u003d R11.Фіо)

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

Вибрати студентів, у яких оцінка по іспиту не менше, аніж хоча б одна оцінка по зданим їм лабораторних робіт з даної дисципліни:

Select R1.Фіо

From R1 Where R1.Оценка\u003e \u003d ANY (Select R4.Оценка

Where Rl.Дісціпліна \u003d R4. Дисципліна AND R1.Фіо \u003d R4.Фіо)

Зовнішні об'єднання SQL

Стандарт SQL2 розширив поняття умовного об'єднання. У стандарті SQL1 при об'єднанні відносин використовувалися тільки умови, що задаються в частині WHERE оператора SELECT, і в цьому випадку в результуюче відношення потрапляли тільки зчеплені за заданими умовами кортежі вихідних відносин, для яких ці умови були визначені й правдиві. Однак насправді часто необхідно об'єднувати таблиці таким чином, щоб в результат потрапили всі рядки з першої таблиці, а замість тих рядків другої таблиці, для яких не виконана умова з'єднання, в результат потрапляли б невизначені значення. Або навпаки, включаються всі рядки з правої (другий) таблиці, а відсутні частини рядків з першої таблиці доповнюються невизначеними значеннями. Такі об'єднання були названі зовнішніми на противагу об'єднанням, певним стандартом SQL1, які стали називатися внутрішніми.

У загальному випадку синтаксис частини FROM в стандарті SQL2 виглядає наступним чином:

FROM<список исходных таблиц> |

< выражение естественного объединения > |

< выражение объединения >

< выражение перекрестного объединения > |

< выражение запроса на объединение >

<список исходных таблиц>::= <имя_таблицы_1>

[Ім'я синоніма табліци_1] [...]

[,<имя_таблицы_п>[ <имя синонима таблицы_n> ] ]

<выражение естественного объединениям:: =

<имя_таблицы_1> NATURAL (INNER | FULL | LEFT | RIGHT) JOIN<имя_таблицы_2>

<выражение перекрестного объединениям: = <имя_таблицы_1> CROSS JOIN<имя_таблицы_2>

<выражение запроса на объединением:=

<имя_таблицы_1> UNION JOIN<имя_таблицы_2>

<выражение объединениям:= <имя_таблицы_1> (INNER |

FULL | LEFT | RIGHT) JOIN (ON умова)<имя_таблицы_2>

У цих визначеннях INNER - означає внутрішнє об'єднання, LEFT - ліве об'єднання, тобто в результат входять всі рядки таблиці 1, а частини результуючих кортежів, для яких не було відповідних значень в таблиці 2, доповнюються значеннями NULL (не визначено). Ключове слово RIGHT означає праве зовнішнє об'єднання, і на відміну від лівого об'єднання в цьому випадку в результуюче відношення включаються всі рядки таблиці 2, а відсутні частини з таблиці 1 доповнюються невизначеними значеннями, Ключове слово FULL визначає повне зовнішнє об'єднання: і ліве і праве. При повній зовнішній об'єднанні виконуються і праве і ліве зовнішні об'єднання і в результуюче відношення включаються всі рядки з таблиці 1, доповнені невизначеними значеннями, і всі рядки з таблиці 2, також доповнені невизначеними значеннями.

Ключове слово OUTER означає зовнішнє, але якщо задані ключові слова FULL, LEFT, RIGHT, то об'єднання завжди вважається зовнішнім.

Розглянемо приклади виконання зовнішніх об'єднань. Знову повернемося до БД «Сесія». Створимо відношення, в якому будуть стояти всі оцінки, отримані всіма студентами по всіх іспитах, які вони повинні були здавати. Якщо студент не здавав даного іспиту, то замість оцінки у нього буде стояти невизначене значення. Для цього виконаємо послідовно природне внутрішнє об'єднання таблиць R2 і R3 по атрибуту Група, а отримане відношення з'єднаємо лівим зовнішнім природним об'єднанням з таблицею R1, використовуючи стовпці ПІБ і Дисципліна. При цьому в стандарті дозволено використовувати дужкову структуру, так як результат об'єднання може бути одним з аргументів на частини FROM оператора SELECT.

SELECT Rl.ФІО, R1.Дісціпліна. Rl.Оценка

FROM (R2 NATURAL INNER JOIN R3) LEFT JOIN Rl USING (ПІБ. Дисципліна)

результат:


ПІБ	дисципліна	оцінка
Петров П. І.	Бази даних
Сидоров К. А.	Бази даних	4
Миронов Л. В.	Бази даних
Степанова К. Е.	Бази даних
Крилова Т. С.	Бази даних
Владимиров В. А.	Бази даних
Петров П. І.	теорія інформації	Null
Сидоров К. А.	теорія інформації
Миронов А. В.	теорія інформації	Null
Степанова К. Е.	теорія інформації
Крилова Т. С.	теорія інформації
Владимиров В. А.	теорія інформації	Null
Петров П. І.	Англійська мова
Сидоров К. А.	Англійська мова	Null
Миронов А. В.	Англійська мова	Null
Степанова К. Е.	Англійська мова	Null
Крилова Т. С.	Англійська мова	Null
Владимиров В. А.	Англійська мова
Трофимов П. А.	Мережі та телекомунікації
Іванова Є. А.	Мережі та телекомунікації

Розглянемо ще один приклад, для цього візьмемо БД «Бібліотека». Вона складається з трьох відносин, імена атрибутів тут набрані латинськими літерами, що є необхідним в більшості комерційних СУБД.

BOOKS (ISBN, TITL. AUTOR. COAUTOR. YEARJZD, PAGES)

READER (NUM_READER. NAME_READER, ADRESS. HOOM_PHONE. WORK_PHONE. BIRTH_DAY)

EXEMPLARE (INV, ISBN, YES_NO. NUM_READER. DATE_IN. DATE_DUT)

Тут таблиця BOOKS описує всі книги, присутні в бібліотеці, вона має такі атрибути:

ISBN - унікальний шифр книги;
TITL - назва книги;
AUTOR - прізвище автора;
COAUTOR - прізвище співавтора;
YEARIZD - рік видання;
PAGES - число сторінок.

Таблиця READER зберігає відомості про всі читачів бібліотеки, і вона містить такі атрибути:

NUM_READER - унікальний номер читацького квитка;
NAME_READER - прізвище та ініціали читача;
ADRESS - адреса читача;
HOOM_PHONE - номер домашнього телефону;
WORK_PHONE - номер робочого телефону;
BIRTH_DAY - дату народження читача.

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

INV - унікальний інвентарний номер примірника книги;
ISBN - шифр книги, який визначає, яка це книга, і посилається на відомості з першої таблиці;
YES_NO - ознака наявності або відсутності в бібліотеці даного екземпляра в поточний момент;
NUM_READER - номер читацького квитка, якщо книга видана читачеві, і Null в іншому випадку;
DATE_IN - якщо книга у читача, то це дата, коли вона видана читачеві; a DATE_OUT - дата, коли читач повинен повернути книгу в бібліотеку.

Визначимо перелік книг у кожного читача; якщо у читача немає книг, то номер примірника книги дорівнює NULL. Для виконання цього пошуку нам треба використовувати ліве зовнішнє об'єднання, тобто ми беремо всі рядки з таблиці READER і з'єднуємо з рядками з таблиці EXEMPLARE, якщо в другій таблиці немає рядка з відповідним номером читацького квитка, то в рядку результуючого відносини атрибут EXEMPLARE.INV буде мати невизначене значення NULL:

SELECT READER.NAME_READER, EXEMPLARE.INV

FROM READER RIGHT JOIN EXEMPLARE ON READER.NUM_READER \u003d EXEMPLARE.NUM_READER

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

FROM (BOOKS LEFT JOIN EXEMPLARE)

LEFT JOIN (READER NATURAL JOIN EXEMPLARE)

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

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

операція запиту па об'єднанняеквівалентна операції теоретико-множинного об'єднання в алгебрі. При цьому вимога еквівалентності схем вихідних відносин зберігається. Запит на об'єднання відбувається за наступною схемою:

SELECT - запит

UNION SELECT - запит

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

Наприклад, потрібно вивести список читачів, які тримають на руках книгу «Ідіот» або книгу «Злочин і кара». Воткакбудетвиглядетьзапрос:

SELECT READER. NAME_READER

FROM READER, EXEMPLARE.BOOKS

BOOKS.TITLE \u003d "(! LANG: Ідіот"!}

SELECT READER.NAME_READER

FROM READER, EXEMPLARE, BOOKS

WHERE EXEMPLARE.NUM_READER \u003d READER.NUM_READER AND

EXEMPLRE.ISBN \u003d BOOKS.ISBN AND

BOOKS.TITLE \u003d "(! LANG: Преступленіеінаказаніе"!}

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

Запит на об'єднання може об'єднувати будь-яке число вихідних запитів.

Так, до попереднього запиту можна додати ще читачів, які тримають на руках книгу «Замок»:

SELECT READER. NAME_READER

FROM READER. EXEMPLARE, BOOKS

WHERE EXEMPLARE.NUM_READER \u003d READER.NUM_READER AND.

EXEMPLRE.ISBN \u003d BOOKS.ISBN AND

BOOKS.TITLE \u003d "(! LANG: Замок"!}

У тому випадку, коли вам необхідно зберегти всі рядки з вихідних відносин, необхідно використовувати ключове слово ALL в операції об'єднання. У разі збереження дублікатів кортежів схема виконання запиту на об'єднання буде виглядати наступним чином:

SELECT - запит

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

SELECT DISTINCT READER.NAME_READER

FROM READER. EXEMPLARE.BOOKS

WHERE EXEMPLARE.NUM_READER \u003d READER.NUM_READER AND

EXEMPLRE.ISBN \u003d BOOKS.ISBN AND

BOOKS.TITLE \u003d "(! LANG: Ідіот" OR!}

BOOKS.TITLE \u003d "(! LANG: Злочин і покарання" OR!}

BOOKS.TITLE \u003d "(! LANG: Замок"!}

Жоден з вихідних запитів в операції UNION не повинен містити пропозиції впорядкування результату ORDER BY, однак результат об'єднання може бути впорядкований, для цього пропозиція ORDER BY із зазначенням списку стовпців упорядкування записується після тексту останнього вихідного SELECT-запиту.

Вступ

SQL (Structured Query Language) - Структурований Мова Запитів - стандартна мова запитів по роботі з реляційними БД.

Перший міжнародний стандарт мови SQL був прийнятий в 1989 р (далі ми будемо називати його SQL / 89 або SQL1). Іноді стандпрт SQL1 також називають стандартом ANSI / ISO і переважна більшість доступних на ринку СУБД підтримують цей стандарт повністю.

В кінці 1992 був прийнятий новий міжнародний стандарт мови SQL (який в подальшому будемо називати SQL / 92 або SQL2). І він не позбавлений недоліків, але в той же час є істотно більш точним і повним, ніж SQL / 89. На даний момент більшість виробників СУБД вносять зміни в свої продукти так, щоб вони в більшій мірі задовольняли стандарту SQL2.

Останній стандарт по мові SQL був випущений в 1996 р Він названий SQL3.

SQL можна в повній мірі віднести до традиційних мов програмування: він не містить традиційні оператори управління ходом виконання програми, оператори опису типів і багато іншого, він містить тільки набір стандартних операторів доступу до даних, що зберігаються в базі даних. Оператори SQL вбудовуються в базова мова програмування, яким може бути будь-який стандартний мову типу C ++, PL, COBOL і т.д. Крім того, оператори SQL можуть виконуватися безпосередньо в інтерактивному режимі.

1. Структура SQL.

SQL містить наступні розділи:

1. Оператори визначення даних DDL (Data definition language).

оператор	сенс	Дія
CREATE TABLE	створити таблицю	Створює нову таблицю в БД
DROP TABLE	видалити таблицю	Видаляє таблицю з БД
ALTER TABLE	змінити таблицю	Змінює структуру існуючої таблиці
CREATE VIEW	створити уявлення	Створює віртуальну таблицю, тобто таблицю, яка насправді не існує, але моделюється з використанням цього оператора.
ALTER VIEW	змінити уявлення	Змінює структуру або зміст віртуальної таблиці
DROP VIEW	видалити уявлення	Видаляє опис віртуальної таблиці. Саму таблицю видаляти не треба, тому що. вона насправді і не існує.
CREATE INDEX	створити індекс	Створює спеціальну фізичну структуру, яка називається індексом, яка забезпечує прискорення доступу до даних
DROP INDEX	видалити індекс	Видаляє створену структуру
CREATE SYNONYM	створити синонім
DROP SYNONYM	видалити синонім

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

3. Мова запитів Data Query Language (DQL)

4. Засоби управління транзакціями (DCL)

5. Засоби адміністрування даних (DDL)

програмний SQL

2. Типи даних

У мові SQL / 89 підтримуються наступні типи даних: CHARACTER, NUMERIC, DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, DOUBLE PRECISION. Ці типи даних класифікуються на типи рядків символів, точних чисел і приблизних чисел.

У стандарті SQL92 додані наступні типи даних:

VARCHAR (n) - рядки символів змінної довжини

NCHAR (N) - рядки локалізованих символів постійної довжини

NCHAR VARYING (n) - рядки локалізованих символів змінної довжини

BIT (n) - рядок бітів постійної довжини

BIT VARYING (n) - рядок бітів змінної довжини

DATE календарна дата

TIMESTAMP (точність) дата і час

INTERVAL часовий інтервал

3. Оператор вибору SELECT

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

Синтаксична діаграма опертора SELECT зображена на рис.1

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

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

У частині FROM задається перелік вихідних відносин (таблиць) запиту.

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

У частіGROUP BY задається список полів угруповання.

У частині HAVING задаються предикати-умови, що накладаються на кожну групу.

У частині ORDER BY задається список полів упорядкування результату.

У вираженні умов для частини WHERE можуть бути використані наступні предикати:

· Предикат порівняння зі зразком LIKE і NOT LIKE

· Предикат EXIST і NOT EXIST.

· Предикати порівняння { =, <>, >,<,>=,<=,}. Синтаксична діаграма предикатів порівняння представлена \u200b\u200bна рис.2

предикат IN - входить в безліч / не входить в безліч.

Предикат IN або NOT IN може також використовуватися і для порівняння перевіряється виразу з підзапитом, в цьому випадку синтаксична діаграма зображена на рис. 5.

Предикат IN правдивий тоді, коли значення атрибута, заданого в перевіряється вираженні для поточного кортежу збігається хоча б з одним з безлічі значень, отриманих в результаті виконання відповідного підзапиту або містяться в списку значень. І назад, предикат NOT IN - правдивий тільки тоді, коли значення заданого атрибуту в поточному кортежі не збігається ні з одним з безлічі значень, визначених вбудованим підзапитом або заданим списком значень.

предикат LIKE - включає (подібний)

Шаблон може містити символи _ підкреслення для позначення будь-якого одиночного символу;

% Символ відсотка - для позначення будь-якої довільної послідовності символів.

Предикат LIKE правдивий тоді, коли значення атрибута, заданого іменем стовпця в поточному кортежі включає в себе заданий<шаблон>.

Предітак NOT LIKE - правдивий тоді, коли значення атрибута в поточному кортежі не включає в себе заданий<шаблон>.

· Предикат NULL - невідомо, не визначено

Синтаксична діаграма предиката представлена \u200b\u200bна рис. 7.

В умовах пошуку можуть бути використані всі розглянуті раніше предикати.

Відклавши на час знайомство з угрупованням, розглянемо детально перші три рядки оператора SELECT:

SELECT - ключове слово, яке повідомляє СУБД, що ця команда - запит. Всі запити починаються цим словом з подальшим пропуском. За ним може слідувати спосіб вибірки - з видаленням дублікатів ( DISTINCT), Або без видалення ( ALL, Мається на увазі за замовчуванням). Потім слідує список перерахованих через кому стовпців, які вибираються запитом з таблиць, або символ ' * 'Для вибору всього рядка. Будь-які стовпці, не перераховані тут, не будуть включені в результуючий набір даних. Це, звичайно, не означає, що вони будуть видалені або їх інформація буде стерта з таблиць, тому що запит не вплине на інформацію в таблицях - він тільки показує дані.

FROM - ключове слово, яке повинно бути представлено в кожному запиті. Після ключового слова FROM слід один або кілька пробілів і далі список вихідних таблиць, які використовуються в запиті. Імена таблиць відокремлюються один від одного комами. Таблицями можна привласнити імена-псевдоніми, що буває корисно для здійснення операції з'єднання таблиці з самою собою або для доступу з вкладеного підзапиту до поточного запису зовнішнього запиту (вкладені підзапити тут не розглядаються). Ім'я користувача - це тимчасове ім'я таблиці, яке використовується тільки в даному запиті і далі не застосовується. Ім'я користувача відділяється від основного імені таблиці принаймні одним пробілом. Синтаксична діаграма частини FROM представлена \u200b\u200bна рис. 9.

Всі наступні частини оператора SELECT є необов'язковими.

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

Розглянемо відносини базу даних, яка моделює здачу сесії в деякому навчальному закладі. Нехай вона складається з трьох відносин,,. Будемо вважати, що вони представлені таблицями R1, R2 і R3 відповідно.

R1 \u003d (ПІБ, Дисципліна, Оцінка)

R2 \u003d (ПІБ, Група)

R3 \u003d (Група, Дисципліна)

Наведемо кілька прикладів використання оператора SELECT.

· Список всіх груп (без повторень), де повинні пройти іспити

SELECT DISTINCT Групи
FROM R3

· Список студентів, які здали екзамен з БД на «відмінно»

SELECT ПІБ
FROM R1
WHERE дисципліна \u003d "БД" AND оцінка = 5

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

SELECT ПІБ, Дисципліна
FROM R2, R3
WHERE R1.Группа = R2.Группа

Тут частина WHERE задає умови з'єднання відношень R1 і R2. При відсутності умов з'єднання в частині WHERE результат буде еквівалентний розширеному декартову твору і, в цьому випадку, кожному студенту були б приписані всі дисципліни з відносини R2, а не ті, які повинна здавати його група.

· Список нехлюїв, що мають кілька двійок

SELECT ПІБ
FROM R1 a, R1 b
WHERE a.ФІО \u003d b.ФІО AND
a.Дісціпліна <> b.Дісціпліна AND
a.Оценка<= 2 AND b.Оценка.<= 2

Тут ми використовували псевдоніми для іменування відносини R1 a і b, так як для запису умов пошуку нам необхідно працювати відразу з двома екземплярами даного відносини.

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

Наявність невизначених Null значень підвищує гнучкість обробки інформації, що зберігається в БД. У наших прикладах ми можемо припустити ситуацію, коли студент прийшов на екзамент, але не здавав його за певних причин, в цьому випадку оцінка за деякою дисципліни для даного студента має невизначене значення. У даній ситуації можна поставити питання: «Знайти студентів, які прийшли на іспит, але не тих, хто складав його із зазначенням назви дисципліни». Оператор Select буде виглядати наступним чином:

SELECT ПІБ, Дисципліна

WHERE оцінкаIS NULL

Відразу хочу обмовитися, що всі приклади, наведені раніше умовні. Чому? Хіба вони не будуть працювати в реальних базах даних? Хіба вони неправильні? Тут все правильно крім імен атрибутів або стовпців таблиці. У більшості СУБД (Систем управління базами даних) забороняється іменувати стовпці на національних мовах, це об'єкти бази даних і об'єкти мови і потрібно, щоб вони іменувалися по правилам іменування ідентифікаторів в даній мові. Найчастіше ім'ям атрибута може бути послідовність літер латинського алфавіту і цифр, що починається з букви, яка не містить деяких спеціальних символів (наприклад пробілів, крапок, ком, знаків відсотка% і інших спеціальних символів) і має деякі обмеження по довжині. У різних СУБД ці обмеження різні, наприклад в MS SQL Server 2000 - довжина імені атрибута може досягати 128 символів. Довгі імена атрибутів незручні для написання запиту, але дуже короткі однобуквені імена не дозволяють зберегти семантику сенс шпальти таблиці, тому вибирають певний компроміс і називають недовга, але зручно, так щоб не треба було заглядати в повний опис бази даних при написанні кожного запиту. Крім того, імена атрибутів, так само як і імена інших об'єктів не повинні совпарать з ключовими словами мови SQL - тобто тими словами, які входять в оператори мови.

Тому з точки зору коректності ми повинні б були схему бази даних «Сесія» представити у вигляді

R1 \u003d (St_name, Discipline, Mark)

R2 \u003d (St_name, N_group)

R3 \u003d (N_group, Discipline)

І відповідним чином змінити все запити.

Застосування агрегатних функцій і вкладених запитів в операторі вибору

Запити можуть обчислювати узагальнене групове значення полів точно також як і значення одного поля. Це робиться за допомогою агрегатних функцій. Агрегатні функції виробляють одиночне значення для всієї групи таблиці. Список цих функцій:

Агрегатні функції використовуються подібно іменам полів в операторі SELECT, але з одним винятком: вони беруть ім'я поля як аргумент. З функціями SUM і AVG можуть використовуватися тільки числові поля. З функціями COUNT, MAX, і MIN можуть використовуватися як числові так і символьні поля. При використанні з символьними полями, MAX і MIN транслюватимуть їх в еквівалент ASCII, і обробляти в алфавітному порядку. Деякі СУБД дозволяють використовувати вкладені агрегати, але це є відхиленням від стандарту ANSI з усіма наслідками, що випливають звідси наслідками.

Звернувшись знову до бази даних «Сесія» (таблиці R1, R2, R3), знайдемо кількість успішно складених іспитів:

SELECT COUNT (*)
FROM R1
WHERE Mark\u003e 2;

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

Пропозиція GROUP BY дозволяє визначати підмножину значень, яке в подальшому згадується як групою, і застосовувати функцію агрегату до цієї групи. Група утворюється з усіх рядків, для яких значення полів угруповання, задані в реченні GROUP BY, мають однакове значення. Це дає можливість об'єднувати поля і агрегатні функції в єдиній пропозиції SELECT. Синтаксична діаграма застосування агрегатних функцій зображена на рис.10 Агрегатні функції можуть застосовуватися як в вираженні виведення результатів рядки SELECT, Так і в виразі умови обробки сформованих груп HAVING. У цьому випадку кожна агрегатна функція обчислюється для кожної виділеної групи. Значення, отримані при обчисленні агрегатних функцій, можуть бути використані для виведення відповідних результатів або для умови відбору груп.

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

Наприклад, такий запит завжди буде помилковим:

Select A

Group By B

Дійсно, давайте розберемося. Що ж ми хочемо знайти? Ми намагаємося вивести деяке значення стовпця А з таблиці Т , І при цьому виконуємо угруповання по іншому стовпцю, колонки В.Виконуємо угруповання - це означає, збираємо всі рядки з однаковими значеннями стовпчика В в одну групу і далі, а далі незрозуміло, ми виводимо значення стовпця А, але ж в одній групі може бути безліч значень, різних значень стовпця А. Так яке ж значення ми виводимо? Це незрозуміло ні нам, ні комп'ютера. Саме тому він відмовляється виконувати подібний запит і заявляє, що у нас синтаксична помилка.

Повернемося до нашої бази даних «Сесія», але доповнимо її ще кількома атрибутами. По-перше, серед студентів можуть бути однофамільці, тому для ідентифікації студента будемо застосовувати має намір студентської залікової книжки, який завжди однозначно ідентифікує студента. І по-друге, припустимо, що студент може робити кілька спроб складання іспиту з однієї і тієї ж дисципліни, а для цього введемо в відношення R1 дату чергової спроби здачі іспиту. І, нарешті, третє доповнення, будемо вважати, що у нас в ВУЗі навчається безліч груп за різними спеціальностями, тоді схема нашої бази даних буде наступною

Sessia (N_zach, Discipline, Mark, Data_ex)

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

Це робиться за допомогою агрегатних функцій. Агрегатні функції обчислюють одиночне значення для всієї групи таблиці. Список цих функцій представлений в табл. 7.

Агрегатні функції використовуються подібно іменам полів в операторі SELECT, але з одним винятком: вони беруть ім'я поля як аргумент. З функціями SUM і AVG можуть використовуватися тільки числові поля. З функціями COUNT, MAX і MIN можуть використовуватися як числові, так і символьні поля. При використанні з символьними полями MAX і MIN транслюватимуть їх в еквівалент ASCII коду і обробляти в алфавітному порядку. Деякі СУБД дозволяють використовувати вкладені агрегати, але це є відхиленням від стандарту ANSI з усіма наслідками, що випливають звідси наслідками.

Наприклад, можна обчислити кількість студентів, які здавали іспити з кожної дисципліни. Для цього треба виконати запит з угрупованням по полю "Дисципліна" і вивести в якості результату назва дисципліни і кількість рядків у групі по даній дисципліні. Застосування символу * як аргумент функції COUNT означає підрахунок всіх рядків в групі.

SELECT R1.Дісціпліна, COUNT (*) FROM R1GROUP BY R1.Дісціпліна

результат:

Приклад. Отримати список дисциплін, за якими склали іспит не менше 5 осіб:

SELECT R1.ДісціплінаFROM R1GROUP BY R1.ДісціплінаHAVING COUNT (*)\u003e \u003d 5 Результат: Тут інструкція HAVING вибирає групи, що задовольняють заданій умові.

вкладені запити

Ставлення D (Деталі)

Ставлення PD (Поставки)

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

WHERE P.STATYS<

(SELECT MAX (P.STATUS)

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

зауваження

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

2. Використання предиката IN

(SELECT DISTINCT PD.PNUM

WHERE PD.DNUM \u003d 2);

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

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

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

3. Використання предиката EXISTS . Отримати список постачальників, що поставляють деталь номер 2:

PD.PNUM \u003d P.PNUM AND

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

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

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

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

4. Використання предиката NOT EXISTS . Отримати список постачальників, які не постачають деталь номер 2:

WHERE NOT EXISTS

PD.PNUM \u003d P.PNUM AND

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

5. Отримати імена постачальників, що поставляють всі деталі:

SELECT DISTINCT PNAME

WHERE NOT EXISTS

PD.DNUM \u003d D.DNUM AND

PD.PNUM \u003d P.PNUM));

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

Найбільш внутрішній підзапит параметризрвані двома параметрами (D.DNUM, P.PNUM) і має наступний сенс: відібрати всі рядки, що містять дані про постачання постачальника з номером PNUM деталі з номером DNUM. Заперечення NOT EXISTS говорить про те, що даний постачальник не поставляє дану деталь. Зовнішній до нього підзапит, сам є вкладеним і параметризованим параметром P.PNUM, має сенс: відібрати список деталей, які не поставляються постачальником PNUM. Заперечення NOT EXISTS говорить про те, що для постачальника з номером PNUM не повинно бути деталей, які не поставлялися б цим постачальником. Це в точності означає, що в зовнішньому запиті відбираються тільки постачальники, що поставляють всі деталі.

зовнішні з'єднання

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

У загальному випадку синтаксис частини FROM в стандарті SQL2 виглядає наступним чином:

FROM<список исходных таблиц>< выражение естественного соединения >< выражение соединения >< выражение перекрестного соединения >< выражение запроса на объединение ><список исходных таблиц>::= <имя_таблицы_1> [Ім'я синоніма табліци_1] [...] [,<имя_таблицы_n>[ <имя синонима таблицы_n> ] ]<выражение естественного соединения>:: =<имя_таблицы_1> NATURAL (INNER | FULL LEFT | RIGHT) JOIN<имя_таблицы_2><выражение перекрестного соединения>:: = <имя_таблицы_1> CROSS JOIN<имя_таблицы_2><выражение запроса на объединение>::=<имя_таблицы_1> UNION JOIN<имя_таблицы_2><выражение соединения>::= <имя_таблицы_1> (INNERFULL | LEFT | RIGHT) JOIN (ON умова |)<имя_таблицы_2>

У цих визначеннях INNER - означає внутрішнє (природне) з'єднання, LEFT - ліве з'єднання, тобто в результат входять всі рядки таблиці 1, а частини результуючих кортежів, для яких не було відповідних значень в таблиці 2, доповнюються значеннями NULL (не визначено). Ключове слово RIGHT означає праве зовнішнє з'єднання, і на відміну від лівого з'єднання в цьому випадку в результуюче відношення включаються всі рядки таблиці 2, а відсутні частини з таблиці 1 доповнюються невизначеними значеннями. Ключове слово FULL визначає повне зовнішнє з'єднання: і ліве і праве. При повній зовнішній з'єднанні виконуються і праве і ліве зовнішні з'єднання і в результуюче відношення включаються всі рядки з таблиці 1, доповнені невизначеними значеннями, і всі рядки з таблиці 2, також доповнені невизначеними значеннями.

Ключове слово OUTER означає зовнішнє, але якщо задані ключові слова FULL, LEFT, RIGHT, то з'єднання завжди вважається зовнішнім.

Розглянемо приклади виконання зовнішніх з'єднань. Знову повернемося до БД "Сесія". Створимо відношення, в якому будуть стояти всі оцінки, отримані всіма студентами по всіх іспитах, які вони повинні були здавати. Якщо студент не здавав даного іспиту, то замість оцінки у нього буде стояти невизначене значення. Для цього виконаємо послідовно природне внутрішнє з'єднання таблиць R2 і R3 по атрибуту Група, а отримане відношення з'єднаємо лівим зовнішнім природним з'єднанням з таблицею R1, використовуючи стовпці ПІБ і Дисципліна. При цьому в стандарті дозволено використовувати дужкову структуру, так як результат з'єднання може бути одним з аргументів на частини FROM оператора SELECT.

SELECT R1.ФІО, R1.Дісціпліна, R1.ОценкаFROM (R2 NATURAL INNER JOIN R3) LEFT JOIN R1 USING (ПІБ, Дисципліна)

результат:

ПІБ	дисципліна	оцінка
Петров П. І.	Бази даних
Сидоров К. А.	Бази даних
Миронов А. В.	Бази даних
Степанова К. Е.	Бази даних
Крилова Т. С	Бази даних
Владимиров В. А.	Бази даних
Петров П. І.	теорія інформації	Null
Сидоров К. А.	теорія інформації
Миронов А. В.	теорія інформації	Null
Степанова К. Е.	теорія інформації
Крилова Т. С	теорія інформації