Помощничек
Главная | Обратная связь


Археология
Архитектура
Астрономия
Аудит
Биология
Ботаника
Бухгалтерский учёт
Войное дело
Генетика
География
Геология
Дизайн
Искусство
История
Кино
Кулинария
Культура
Литература
Математика
Медицина
Металлургия
Мифология
Музыка
Психология
Религия
Спорт
Строительство
Техника
Транспорт
Туризм
Усадьба
Физика
Фотография
Химия
Экология
Электричество
Электроника
Энергетика

Застосування агрегатних функцій в інструкції SELECT



МОВА SQL

 

Анотація Тема присвячена вивченню мови запитів реляційних баз даних.

 

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

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

Мова для взаємодії з БД SQL (Structured Query Language) з'явилася у середині 70 - х і була розроблена в рамках проекту експериментальної реляційної СУБД System R . Ця мова була орієнтована на зручне і зрозуміле користувачам формулювання запитів до реляційної БД.

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

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

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

Ще однією відмінністю SQL є необов'язкове видалення кортежів-дублікатів в остаточному або проміжних відношеннях-результатах. Строго кажучи, результатом оператора вибірки в мові SQL є не відношення, а мультимножина кортежів. У тих випадках, коли семантика запиту вимагає наявність відношення, знищення дублікатів проводиться неявно.

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

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

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

Це непроцедурна мова, тому тут необхідно указувати, яка інформація повинна бути одержана, а не як її можна одержати. Інакше кажучи, мова SQL не вимагає вказівки методів доступу до даних.

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

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

з деяким відступом відносно початка фрази, що указуватиме на їх підлеглість.

 

 

Компоненти SQL

 

 

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

Інструкції SQL в Microsoft Jet підрозділяються на наступні категорії:

  • інструкції Мови Визначення Даних (DDL);
  • інструкції Мови Маніпулювання Даними (DML).

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

  • створити, змінити і видалити об'єкти бази даних, включаючи безпосередньо базу даних;
  • змінити імена об'єктів схеми бази даних;

Інструкції DDL неявно фіксують попередню транзакцію і запускають нову транзакцію.

Інструкції DDL в SQL - вирази, сформовані з наступних команд:

 

Інструкція Опис

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

індексів

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

бази даних

ALTERВикористовується для зміни таблиць, додавання полів

або зміни визначень полів

 

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

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

Інструкції DML - найбільш часто використовувані інструкції SQL:

 

Інструкція Опис

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

метою відбору рядків, які задовольняють певним критеріям

INSERTВикористовується для пакетного завантаження в базу

даних нових рядків даних в одній операції (запит на

додавання записів)

UPDATEВикористовується для зміни значень окремих записів і

полів (запит на оновлення)

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

даних. Застосування інструкції узгоджується з

принципами підтримки цілісності,

тому інструкція не завжди може бути виконана,

навіть якщо вона записана синтаксично правильно.

 

Пропозиції SQL

 

 

Пропозиції змінюють умови, використовувані для визначення даних, які потрібно відібрати або обробити. Далі перераховані деякі пропозиції, доступні в Jet SQL.

 

 

Пропозиція Опис

 

FROMПризначена для вказівки імені таблиці, з якої повинні бути

вибрані записи

WHERE Специфікує умови, яким повинні задовольняти вибрані записи

GROUP BYВикористовується для розподілу вибраних записів в певні групи

HAVINGОголошує умову, якій повинна задовольняти кожна група записів

ORDER BYВикористовується для специфікації порядку сортування

вибраних записів

CONSTRAINTЗ цього ключового слова починається визначення індексу

для існуючої таблиці в інструкції CREATE TABLE

 

Операції SQL

 

Операції мови SQL,вживані для завдання умови, можна розділити на:

 

1. Операції порівняння дозволяють порівняти значення двох виразів. Ці операції перераховані нижче:

 

<Менше

>Більше

=Дорівнює

<>Не дорівнює

<=Менше або рівно

>=Більше або рівно

 

2. Логічні операції:

 

BETWEEN.ANDВизначає приналежність значення виразу вказаному

діапазону

LIKEВикористовується для завдання шаблону

INПеревіряє, чи співпадає значення виразу з одним з елементів

вказаного списку

IS NULLПорівняння з невизначеним значенням

EXISTSВідбір записів, відповідних заданому критерію

ALLПорівняння початкового значення зі всіма іншими, що

входять в деякий набір даних

ANYПорівняння заданого значення з кожним із значень деякого

набору даних

 

3. Операції об'єднання

 

ANDОб'єднання декількох умов шляхом операції логічного множення

ORОб'єднання декількох умов шляхом операції логічного додавання

 

4. Операція заперечення

NOT

 

 

Агрегатні функції

 

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

 

Агрегатна функція Опис

AVGПовертає середнє значення окремого поля

COUNTПовертає кількість записів в таблиці

SUMПовертає суму значень окремого поля

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

MINПовертає найменше значення в специфікованому полі

 

 

Інструкції DML

 

 

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

 

 

Запит на вибірку

 

Інструкція SELECT (запит на вибірку ) призначена для отримання записів з бази даних у вигляді набору записів із збереженням його в новому об'єкті RECORDSET. У додатку можна потім маніпулювати цим набором записів - виводити записи на екран у формі, додавати, змінювати і/або видаляти їх, а також створювати звіти.

SELECT - найчастіше перше слово в інструкції SQL. Більшість інструкцій SQL є запитами SELECT або SELECT INTO. Запити SELECT не змінюють дані в базі даних - вони тільки відбирають дані.

Синтаксис інструкції SELECT має наступний вигляд:

 

SELECT [ALL| DISTINCT] {*| ім'я поля1 [AS нове ім'я][, ім'я поля2 [AS нове ім'я][…}

FROM ім'я таблиці1[, ім'я таблиці2]

[WHERE умова вибірки або з'єднання]

[GROUP BY Список полів для угрупування]

[HAVING умова відбору для групи]

[ORDER BY Список полів, по яких треба упорядкувати результат [ASC|DESC]]

[WITH OWNERACCESS OPTION];

 

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

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

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

Пропозиція WHERE визначає, які записи з таблиць, перерахованих в пропозиції FROM повинні бути включені в результат інструкції SELECT. Якщо пропозиція WHERE не специфікується, запит повертає всі рядки з таблиць. Коли інструкцією SELECT створюється об'єкт RECORDSET, імена стовпців таблиці стають іменами об'єкту FIELD в RECORDSET. Пропозицією AS можна встановити для стовпців альтернативні (можливо більш описові або економні) імена.

 

Приклади запитів

 

Розглянемо наступну схему даних

 

1. Вибрати всю інформацію з таблиці Товари

SELECT *

FROM товари;

 

2. Вибрати список наявних товарів з вказівкою ціни.

SELECT найменування, ціна

FROM товари;

3. Вибрати найменування товарів(без повторень)

SELECT DISTINCT найменування

FROM товари;

 

4. Вибрати всі товари 2-й категорії

SELECT *

FROM товари

WHERE категория=2;

 

5. Вибрати інформацію про товари, продані з 1-го жовтня 2002 р.

SELECT *

FROM продажи

WHERE дата_продажи>#10/1/2002#;

 

6. Вибрати клієнтів, у яких не вказаний телефон.

SELECT прізвище, ім'я, по батькові, місто, підприємство

FROM клієнти

WHERE телефон Is Null;

 

7. Вибрати товари, ціна яких знаходиться в діапазоні від 10 до 200.

SELECT *

FROM Товари

WHERE Ціна BETWEEN 10 AND 200;

 

Або

7а. SELECT *

FROM Товари

WHERE Цена>=10 AND Цена<=200;

 

8. Вибрати клієнтів з Одеси, Києва.

SELECT *

FROM Клієнти

WHERE город='Одесса' OR город='Киев';

 

9. Вибрати клієнтів з Одеси, Києва, Львова.

SELECT *

FROM Клієнти

WHERE місто IN ('Одесса','Киев','Львов');

 

10. Вибрати клієнтів, прізвище яких починається на букву А

SELECT *

FROM Клієнти

WHERE ФІО LIKE ‘А *’;

 

11. Вибрати клієнтів, для яких номер телефону починається з 8067774, а решта 4 цифр невідома.

SELECT *

FROM Клієнти

WHERE Телефон LIKE ‘8067774####’;

 

 

Пропозиція ORDER BY (сортування результатів)

 

Пропозиція ORDER BY сортує записи, одержані в результаті запиту, у порядку зростання або убування на основі значень вказаного поля або полів. За умовчанням використовується порядок сортування за збільшенням( від А до Я і від 0 до 9). Для сортування по спаданню ( від Я до А і від 9 до 0) слід додати зарезервоване слово DESC після кожного імені поля, яке потрібно відсортувати в спадаючому порядку. Пропозиція ORDER BY може містити декілька полів. Спочатку записи сортуються по першому полю в списку ORDER BY, потім записи, що мають співпадаючі значення в першому полі сортуються по другому полю і т.д.

 

12. Відсортувати список товарів за абеткою.

SELECT *

FROM Товари

ORDER BY Найменування;

 

13. Видати список товарів у порядку спадання ціни.

SELECT *

FROM Товари

ORDER BY 4 DESC;

 

Тут вказаний номер стовпця сортування.

 

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

SELECT *

FROM Товари

ORDER BY Категорія, Найменування, Ціна DESC;

 

Застосування агрегатних функцій в інструкції SELECT

 

Агрегатна функція Опис

AVGПовертає середнє значення окремого поля

COUNTПовертає кількість записів в таблиці

SUMПовертає суму значень окремого поля

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

MINПовертає найменше значення в специфікованому полі

 

Агрегатні функції оперують із значеннями в єдиному стовпці таблиці і повертають єдине значення. Функції COUNT, MAX, MIN застосовні як до числових, так і до нечислових полів. Функції SUM, AVG можуть використовуватися тільки у разі числових полів. За винятком COUNT (*), при обчисленні результатів будь-яких функцій спочатку виключаються всі порожні значення, після чого необхідна операція застосовується тільки до конкретних значень стовпця, що залишилися. Варіант COUNT (*) є особливим випадком використання функції COUNT - його призначення полягає в підрахунку всіх рядків в результуючій таблиці, незалежно від того, містяться там порожні, такі, що дублюються або будь-які інші значення.

Якщо до застосування агрегатної функції необхідно виключити значення, що дублюються, слідує перед ім'ям стовпця у визначенні функції помістити ключове слово DISTINCT. Ключове слово DISTINCT не має сенсу для функцій MAX і MIN. Проте його використання може надавати ефект на результати виконання функцій SUM і AVG, тому слід наперед обдумати, чи повинне воно бути присутнім у кожному конкретному випадку, Крім того, ключове слово DISTINCT в кожному запиті може бути вказано не більше одного разу.

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

 

15. Знайти максимальну і мінімальну кількість проданих товарів за одну покупку.

SELECT MAX(Продано) AS [Максимум], MIN(Продано) AS [Мінімум]

FROM Продажи;

 

 

Пропозиція GROUP BY

 

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

 

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

SELECT Код_клиента, SUM(Продано) AS [кількість покупок]

FROM Продажи

GROUP BY Код_клиента;

 

17. Вибрати клієнтів, які зробили за один раз більше 10 покупок і упорядкувати результат у порядку спадання кількості покупок.

SELECT Код_клиента, SUM(Продано) AS [Kількість покупок]

FROM Продажи

WHERE Продано>10

GROUP BY Код_клиента

ORDER BY 2 DESC;

 

 

Пропозиція HAVING

 

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

Пропозиція HAVING є необов'язковою.

Пропозиція HAVING схоже на пропозицію WHERE, яка визначає, які записи повинні бути відібрані. Після того, як запису будуть згруповані за допомогою пропозиції GROUP BY, пропозиція HAVING указує, які з одержаних записів повинні бути відібрані:

Пропозиція HAVING може містити до 40 виразів, зв'язаних логічними операторами, такими як And і Or.

 

18. Вибрати тих клієнтів загальна кількість покупок яких більше 20.

SELECT Код_клиента, SUM(Продано) AS [Kількість покупок]

FROM Продажи

GROUP BY Код_клиента

HAVING SUM(Продано)>20

ORDER BY 2 DESC;

 

 

 




Поиск по сайту:

©2015-2020 studopedya.ru Все права принадлежат авторам размещенных материалов.