Щоб визначити кількість записів у таблиці MySQL, необхідно скористатися спеціальною функцією COUNT().

Функція COUNT() повертає кількість записів у таблиці, які відповідають заданому критерію.

Функція COUNT(expr) завжди вважає ті рядки, у яких результатом виразу expr є NOT NULL .

Винятком із цього правила є використання функції COUNT() із зірочкою як аргумент - COUNT(*) . У цьому випадку вважаються всі рядки, незалежно від того, NULL вони або NOT NULL .

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

SELECT COUNT(*) FROM table_name

Як порахувати кількість записів та вивести на екран

Приклад PHP+MySQL-коду для підрахунку та виведення загальної кількості рядків:

$res = mysql_query("SELECT COUNT(*) FROM table_name") $row = mysql_fetch_row($res); $ total = $ row; // всього записів echo $ total; ?>

Цей приклад ілюструє найпростіший варіант використання функції COUNT(). Але за допомогою цієї функції можна виконувати й інші завдання.

Вказавши певний стовпець таблиці як параметр, функція COUNT(column_name) повертає кількість записів цього стовпця, які містять значення NULL . Записи із значеннями NULL ігноруються.

SELECT COUNT(column_name) FROM table_name

Використовувати функцію mysql_num_rows() не можна, тому що для того, щоб дізнатися загальну кількість записів, потрібно виконати запит SELECT * FROM db, тобто отримати всі записи, а це небажано, тому краще використовувати функцію count.

$result = mysql_query("SELECT COUNT(*) як rec FROM db");

Використання функції COUNT() на прикладі

Ось ще один приклад використання функції COUNT(). Допустимо, є таблиця ice_cream з каталогом морозива, в якій знаходяться ідентифікатори категорій та назви морозива.

Вчимося підбивати підсумки. Ні, це ще результати вивчення SQL, а підсумки значень стовпців таблиць бази даних. Агрегатні функції SQL діють щодо значень шпальти з метою отримання єдиного результуючого значення. Найчастіше застосовуються агрегатні функції SQL SUM, MIN, MAX, AVG та COUNT. Слід розрізняти два випадки застосування агрегатних функцій. Перший: агрегатні функції використовуються власними силами і повертають одне результуюче значення. Другий: агрегатні функції використовуються з оператором SQL GROUP BY, тобто з групуванням по полях (стовпцям) для отримання результуючих значень у кожній групі. Розглянемо спочатку випадки використання агрегатних функцій без угруповання.

Функція SQL SUM

Функція SQL SUM повертає суму значень шпальти таблиці бази даних. Вона може застосовуватися тільки до стовпців, значення яких числа. Запити SQL для отримання результуючої суми починаються так:

SELECT SUM (ІМ'Я_СТОЛБЦЯ) ...

Після цього виразу слідує FROM (ІМ'Я_ТАБЛИЦІ), а далі за допомогою конструкції WHERE може бути задана умова. Крім того, перед іменем стовпця може бути вказано DISTINCT, і це означає, що враховуватимуться лише унікальні значення. За умовчанням враховуються всі значення (для цього можна особливо вказати не DISTINCT, а ALL, але слово ALL не є обов'язковим).

приклад 1.Є база даних фірми з даними про її підрозділи та співробітників. Таблиця Staff також має стовпець з даними про заробітну плату співробітників. Вибірка з таблиці має такий вигляд (для збільшення картинки клацнути по ній лівою кнопкою миші):

Для отримання суми розмірів усіх заробітних плат використовуємо наступний запит:

SELECT SUM (Salary) FROM Staff

Цей запит поверне значення 287664,63.

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

Функція SQL MIN

Функція SQL MIN також діє щодо стовпців, значеннями яких є числа та повертає мінімальне серед усіх значень стовпця. Ця функція має синтаксис, аналогічний синтаксису функції SUM.

приклад 3.База даних та таблиця - ті ж, що й у прикладі 1.

Потрібно дізнатися про мінімальну заробітну плату співробітників відділу з номером 42. Для цього пишемо наступний запит:

Запит поверне значення 10 505,90.

І знову вправу для самостійного вирішення. У цьому та деяких інших вправах знадобиться вже не лише таблиця Staff, а й таблиця Org, що містить дані про підрозділи фірми:

приклад 4.До таблиці Staff додається таблиця Org, що містить дані підрозділах фірми. Вивести мінімальну кількість років, опрацьованих одним співробітником у відділі, розташованому у Бостоні.

Функція SQL MAX

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

Приклад 5.

Потрібно дізнатися про максимальну заробітну плату співробітників відділу з номером 42. Для цього пишемо наступний запит:

Запит поверне значення 18352,80

Прийшов час вправи для самостійного вирішення.

Приклад 6.Знову працюємо з двома таблицями – Staff та Org. Вивести назву відділу та максимальне значення комісійних, які отримують один співробітник у відділі, що відноситься до групи відділів (Division) Eastern. Використати JOIN (з'єднання таблиць) .

Функція SQL AVG

Зазначене щодо синтаксису для попередніх описаних функцій є правильним і щодо функції SQL AVG. Ця функція повертає середнє серед усіх значень стовпця.

Приклад 7.База даних та таблиця - ті ж, що й у попередніх прикладах.

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

Результатом буде значення 6,33

Приклад 8.Працюємо з однією таблицею – Staff. Вивести середню зарплату працівників зі стажем від 4 до 6 років.

Функція SQL COUNT

Функція SQL COUNT повертає кількість записів таблиці бази даних. Якщо в запиті вказати SELECT COUNT(ІМ'Я_СТОЛБЦЯ) ..., то результатом буде кількість записів без урахування записів, у яких значенням стовпця є NULL (невизначене). Якщо використовувати як аргумент зірочку і почати запит SELECT COUNT(*) ..., то результатом буде кількість всіх записів (рядків) таблиці.

Приклад 9.База даних та таблиця - ті ж, що й у попередніх прикладах.

Потрібно дізнатися про кількість всіх співробітників, які отримують комісійні. Число співробітників, у яких значення стовпця Comm – не NULL, поверне наступний запит:

SELECT COUNT (Comm) FROM Staff

Результатом буде 11.

приклад 10.База даних та таблиця - ті ж, що й у попередніх прикладах.

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

SELECT COUNT (*) FROM Staff

Результатом буде значення 17.

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

Приклад 11.Працюємо з однією таблицею – Staff. Вивести кількість співробітників у відділі планування (Plains).

Агрегатні функції разом із SQL GROUP BY (угрупуванням)

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

Приклад 12Існує база даних порталу оголошень. Вона містить таблицю Ads, яка містить дані про оголошення, подані за тиждень. Стовпець Category містить дані про великі категорії оголошень (наприклад, Нерухомість), а стовпець Parts - про дрібніші частини, що входять до категорії (наприклад, частини Квартири та Дачі є частинами категорії Нерухомість). Стовпець Units містить дані про кількість поданих оголошень, а стовпець Money - про грошові суми, отримані за подачу оголошень.

Category	Part	Units	Money
Транспорт	Автомашини	110	17600
Нерухомість	Квартири	89	18690
Нерухомість	Дачі	57	11970
Транспорт	Мотоцикли	131	20960
Будматеріали	Дошки	68	7140
Електротехніка	Телевізори	127	8255
Електротехніка	Холодильники	137	8905
Будматеріали	Регіпс	112	11760
Дозвілля	Книги	96	6240
Нерухомість	Будинки	47	9870
Дозвілля	Музика	117	7605
Дозвілля	Ігри	41	2665

Використовуючи оператор SQL GROUP BY, знайти суми грошей, отриманих за подачу оголошень в кожній категорії. Пишемо наступний запит:

SELECT Category, SUM (Money) AS Money FROM Ads GROUP BY Category

приклад 13.База даних та таблиця - та сама, що у попередньому прикладі.

Використовуючи оператор SQL GROUP BY, з'ясувати, в якій частині кожної категорії було подано найбільшу кількість оголошень. Пишемо наступний запит:

SELECT Category, Part, MAX (Units) AS Maximum FROM Ads GROUP BY Category

Результатом буде наступна таблиця:

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

Реляційні бази даних та мова SQL

ВИЛІЧЕННЯ

Підсумкові функції

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

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

• COUNT (параметр ) Повертає кількість записів, зазначених у параметрі. Якщо потрібно отримати кількість усіх записів, то як параметр слід вказати символ зірочки (*). Якщо в якості параметра вказати ім'я стовпця, функція поверне кількість записів, у яких цей стовпець має значення, відмінні від NULL. Щоб дізнатися, скільки різних значень містить стовпець, перед ім'ям слід вказати ключове слово DISTINCT. Наприклад:

SELECT COUNT(*) FROM Клієнти;

SELECT COUNT(Сумма_замовлення) FROM Клієнти;

SELECT COUNT(DISTINCT Сума_замовлення) FROM Клієнти;

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

SELECT Регіон , COUNT(*) FROM Клієнти ;

• SUM (параметр ) Повертає суму значень зазначеного в параметрі стовпця. Параметр може бути і вираз, що містить ім'я стовпця. Наприклад:

SELECT SUM (Сума_замовлення) FROM Клієнти;

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

Припустимо, що у вихідній таблиці значення стовпця Сума_заказу виражені у рублях, а нам потрібно обчислити загальну суму в доларах. Якщо поточний обмінний курс дорівнює, наприклад, 27,8, отримати необхідний результат можна за допомогою виразу:

SELECT SUM (Сума_замовлення*27.8) FROM Клієнти;

• AVG (параметр ) Повертає середнє арифметичне всіх значень зазначеного в параметрі стовпця. Параметр може бути виразом, що містить ім'я стовпця. Наприклад:

SELECT AVG (Сумма_замовлення) FROM Клієнти;

SELECT AVG (Сумма_замовлення*27.8) FROM Клієнти

WHERE Регіон<>"Північно-3апад";

• МАХ (параметр ) Повертає максимальне значення в стовпці, вказаному в параметрі. Параметр може також бути виразом, що містить ім'я стовпця. Наприклад:

SELECT МАХ(Сума__замовлення) FROM Клієнти;

SELECT МАХ(Сумма_замовлення*27.8) FROM Клієнти

W HERE Регіон<>"Північно-3апад";

• MIN (параметр ) Повертає мінімальне значення в стовпці, зазначеному в параметрі. Параметр може бути виразом, що містить ім'я стовпця. Наприклад:

SELECT MIN(Сумма_замовлення) FROM Клієнти;

SELECT MIN (Сума__замовлення*27 . 8) FROM Клієнти

W HERE Регіон<>"Північно-3апад";

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

SELECT Регіон, SUM (Сума_замовлення) FROM Клієнти

GROUP BY Регіон;

Результатна таблиця для цього запиту містить імена регіонів та підсумкові (загальні) суми замовлень усіх клієнтів із відповідних регіонів (рис. 5).

Тепер розглянемо запит на отримання всіх підсумкових даних щодо регіонів:

SELECT Регіон, SUM (Сумма_замовлення), AVG (Сума_замовлення), МАХ(Сума_замовлення), MIN (Сумма_замовлення)

FROM Клієнти

GROUP BY Регіон;

Вихідна та результатна таблиці показані на рис. 8. У прикладі лише Північно-Західний регіон представлений у вихідній таблиці більш ніж одним записом. Тож у результатної таблиці йому різні підсумкові функції дають різні значення.

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

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

AS заголовок_стовпця

Ключове слово AS (як) означає, що у результатній таблиці відповідний стовпець повинен мати заголовок, вказаний після AS. Заголовок, що призначається, ще називають псевдонімом. У наступному прикладі (рис. 9) задаються псевдоніми для всіх стовпців, що обчислюються:

SELECT Регіон,

SUM (Сума_замовлення) AS [Загальна сума замовлення],

AVG (Сума_замовлення) AS [Середня сума замовлення],

МАХ(Сума_замовлення) AS Максимум,

MIN (Сума_замовлення) AS Мінімум,

FROM Клієнти

GROUP BY Регіон;

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

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

Підсумкові функції можна використовувати у виразах SELECT та HAVING, але їх не можна застосовувати у виразі WHERE. Oneратор HAVING аналогічний оператору WHERE, але, на відміну від WHERE, він відбирає записи в групах.

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

SELECT Регіон , Count(*)

FROM Клієнти

GROUP BY Регіон HAVING COUNT(*) > 1;

Функції обробки значень

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

• рядкові функції;
• числові функції;
• функції дати-часу.

Рядкові функції

Рядкові функції приймають як параметр рядок і повертають після обробки рядок або NULL.

• SUBSTRING (рядок FROM початок)Повертає підрядок, що виходить з рядка, який вказаний як параметррядок. Підрядка починається з символу, порядковий номер якого вказаний у параметрі початок, і має довжину, вказану у параметрі довжина. Нумерація символів рядка ведеться ліворуч, починаючи з 1. Квадратні дужки тут вказують лише на те, що укладений в них вираз не є обов'язковим. Якщо вираз FOR довжина не використовується, то повертається підрядок відпочаток і до кінця вихідного рядка. Значення параметрівпочаток та довжина повинні вибиратися так, щоб підрядок, що шукається, дійсно знаходилася всередині вихідного рядка. Інакше функція SUBSTRING поверне NULL.

Наприклад:

SUBSTRING ("Дорога Маша!" FROM 9 FOR 4) Повертає "Маша";

SUBSTRING ("Дорога Маша!" FROM 9) Повертає "Маша!";

SUBSTRING("Дорога Маша! " FROM 15) Повертає NULL.

Використовувати цю функцію в SQL-вираженні можна, наприклад:

SELECT * FROM Клієнти

WHERE SUBSTRING(Регіон FROM 1 FOR 5) = "Північ";

• UPPER (рядок ) ¦ переводить усі символи вказаної у параметрі рядка у верхній регістр.
• LOWER (рядок ) переводить всі символи вказаної в параметрі рядка в нижній регістр.
• TRIM (LEADING | TRAILING | BOTH ["символ"] FROM рядок ) видаляє провідні (LEADING), заключні (TRAILING) або ті та інші (BOTH) символи з рядка. За промовчанням символом, що видаляється, є пробіл (" "), тому його можна не вказувати. Найчастіше ця функція використовується саме для видалення прогалин.

Наприклад:

TRIM (LEADING "" FROM "місто Санкт-Петербург") обертає "місто Санкт-Петербург";

TRIM (TRALING "FROM "місто Санкт-Петербург") повертає "місто Санкт-Петербург";

TRIM (BOTH ""FROM" місто Санкт-Петербург") повертає "місто Санкт-Петербург";

TRIM(BOTH FROM "місто Санкт-Петербург") повертає "місто Санкт-Петербург";

TRIM(BOTH "г" FROM "місто Санкт-Петербург") повертає "род Санкт-Петербур".

Серед цих функцій найчастіше використовувані - SUBSTRING() та TRIM().

Числові функції

Числові функції як параметр можуть приймати дані як числового типу, але повертають завжди число чи NULL (невизначене значення).

• POSITION ( цільовийРядок IN рядок) ¦ шукає входження цільового рядка у зазначений рядок. У разі успішного пошуку повертає номер положення її першого символу, інакше 0. Якщо цільовий рядок має нульову довжину (наприклад, рядок " "), то функція повертає 1. Якщо хоча б один із параметрів має значення NULL, то повертається NULL. Нумерація символів рядка ведеться ліворуч, починаючи з 1.

Наприклад:

POSITION ("e" IN "Привіт усім") Повертає 5;

POSITION ("всім" IN "Привіт усім") повертає 8;

POSITION (" "Привіт всім") повертає 1;

POSITION("Привіт!" IN "Привіт усім") Повертає 0.

У таблиці Клієнти (див. рис. 1) стовпець Адреса містить, окрім назви міста, поштовий індекс, назву вулиці та інші дані. Можливо, вам потрібно буде вибрати записи про клієнтів, які мешкають у певному місті. Так, якщо потрібно вибрати записи, що стосуються клієнтів, які проживають в Санкт-Петербурзі, то можна скористатися таким виразом SQL-запиту:

SELECT * FROM Клієнти

WHERE POSITION ("Санкт-Петербург" IN Адреса) > 0;

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

SELECT * FROM Клієнти

WHERE Адреса LIKE "% Петербург %";

• EXTRACT (параметр ) ¦ витягує елемент із значення типу дата-час або з інтервалу. Наприклад:

EXTRACT (MONTH FROM DATE "2005-10-25")повертає 10.

• CHARACTER_LENGTH (рядок ) Повертає кількість символів у рядку.

Наприклад:

CHARACTER_LENGTH("Привіт усім") Повертає 11.

• OCTET_LENGTH (рядок ) Повертає кількість октетів (байтів) у рядку. Кожен символ латиниці або кирилиці представляється одним байтом, а символ китайського алфавіту - двома байтами.
• CARDINALITY (параметр ) приймає як параметр колекцію елементів і повертає кількість елементів в колекції (кардинальне число). Колекція може бути, наприклад, масивом або мультимножиною, що містить елементи різних типів.
• ABS (число ) Повертає абсолютне значення числа. Наприклад:

ABS (-123) Повертає 123;

ABS (2 - 5) повертає 3.

• МО D (число1, число2 ) Повертає залишок від цілочисленного поділу першого числа на друге. Наприклад:

MOD (5, з) повертає 2;

MOD (2, з) повертає 0.

• LN (число ) Повертає натуральний логарифм числа.
• ЕХР (число) повертає е число (Підстава натурального логарифму в ступені число).
• POWER (число1, число2 ) Повертає число1число2 (Число1 в ступені число2).
• SQRT (число ) Повертає квадратний корінь з числа.
• FLOOR (число ) Повертає найбільше ціле число, що не перевищує задане параметром (округлення в меншу сторону). Наприклад:

FLOOR (5.123) Повертає 5.0.

• CEIL (число) або CEILING (число ) Повертає найменше ціле число, яке не менше заданого параметром округлення у більшу сторону). Наприклад:

CEIL (5.123) Повертає 6. 0.

• WIDTH_BUCKET (число1, число2, числоЗ, число4) повертає ціле число в діапазоні між 0 і число4 + 1. Параметри число2 і числоЗ задають числовий відрізок, розділений на рівновеликі інтервали, кількість яких визначається параметром число 4. Функція визначає номер інтервалу, в який потрапляє значення число1. Якщо число1 знаходиться поза заданим діапазоном, то функція повертає 0 або число 4 + 1. Наприклад:

WIDTH_BUCKET(3.14, 0, 9, 5) Повертає 2.

Функції дати-часу

У мові SQL є три функції, які повертають поточну дату та час.

• CURRENT_DATE Повертає поточну дату (тип DATE).

Наприклад: 2005-06-18.

• CURRENT_TIME (число ) Повертає поточний час (тип TIME). Цілочисельний параметр показує точність подання секунд. Наприклад, при значенні 2 секунди будуть представлені з точністю до сотих (дві цифри в дрібній частині):

12:39:45.27.

• CURRENT_TIMESTAMP (число ) Повертає дату та час (тип TIMESTAMP). Наприклад, 2005-06-18 12:39:45.27. Цілочисельний параметр вказує на точність подання секунд.

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

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

Обчислювані вирази

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

Логічні оператори AND, OR та NOT та функції були розглянуті раніше.

Арифметичні оператори:

• + | додавання;
• - віднімання;
• * ¦ множення;
• / | Поділ.

Строковий оператортільки один оператор конкатенації або склеювання рядків (| |). У деяких реалізаціях SQL (наприклад Microsoft Access) замість (| |) використовується символ (+). Оператор конкатенації приписує другий рядок до кінця першої приклад, вираз:

"Саша" | | "любить" | | "Машу"

поверне як результат рядок "Сашалюбить Машу".

При складанні виразів слід стежити, щоб операнди операторів мали допустимі типи. Наприклад, вираз: 123 + "Саша" неприпустимий, оскільки арифметичний оператор додавання застосовується до рядкового операнда.

Вирази, що обчислюються, можуть знаходитися після оператора SELECT, а також у висловлюваннях умов операторів WHERE і HAVI NG.

Розглянемо кілька прикладів.

Нехай таблиця Продаж містить стовпці Тип_товару, Кількість і Ціна, а нам потрібно знати виручку для кожного типу товару. Для цього достатньо до списку стовпців після оператора SELECT включити вираз Кількість*Ціна:

SELECT Тип_товару, Кількість, Ціна, Кількість*Ціна AS

Разом FROM Продаж;

Тут використовується ключове слово AS (як) для завдання псевдоніма стовпця з даними, що обчислюються.

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

Мал. 10. Результат запиту з обчисленням виручки за кожним типом товару

Якщо потрібно дізнатися загальну виручку від продажу всіх товарів, достатньо застосувати наступний запит:

SELECT SUM (Кількість*Ціна) FROM Продаж;

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

SELECT Тип_товару, Кількість*Ціна AS Разом

FROM Продажі

WHERE Кількість*Ціна > 1000;

Припустимо, що потрібно отримати таблицю, в якій два стовпці:

Товар, що містить тип товару та ціну;

Разом, що містить виторг.

Оскільки передбачається, що у вихідній таблиці продажу стовпець Тип_товару є символьним (тип CHAR), а стовпець Ціна ? числової, то при об'єднанні (склейці) даних із цих стовпців необхідно виконати приведення числового типу до символьного за допомогою функції CAST (). Запит, який виконує це завдання, має такий вигляд (рис. 11):

SELECT Тип_товару | | " (Ціна: " | | CAST(Ціна AS CHAR(5)) | | ")" AS Товар, Кількість*Ціна AS Разом

FROM Продаж;

Мал. 11. Результат запиту з об'єднанням різнотипних даних в одному стовпці

Примітка. У Microsoft Access аналогічний запит матиме такий вигляд:

SELECT Тип_товару + " (Ціна: " + C Str (Ціна) + ")" AS Товар,

Кількість*Ціна AS Разом

FROM Продаж;

Умовні висловлювання з оператором CASE

У звичайних мовах програмування є оператори умовного переходу, які дозволяють управляти обчислювальним процесом залежно від цього, виконується чи ні певна умова. У мові SQL таким оператором є CASE (випадок, обставина, екземпляр). У SQL:2003 цей оператор повертає значення і, отже, може використовуватися у виразах. Він має дві основні форми, які ми розглянемо у цьому розділі.

Оператор CASE зі значеннями

Оператор CASE зі значеннями має наступний синтаксис:

CASE проверяемое_значение

WHEN значення1 THEN результат1

WHEN значення2 THEN резулътат2

. . .

WHEN значенням N THEN результат N

ELSE результатХ

У випадку, коли значення, що перевіряєтьсяодно значення1 , оператор CASE повертає значеннярезультат1 , вказаний після ключового слова THEN (то). Інакше проверяемое_значение порівнюється ззначення2 , і якщо вони рівні, то повертається значення результат2. В іншому випадку значення, що перевіряється, порівнюється з наступним значенням, вказаним після ключового слова WHEN (коли) і т. д. Якщо проверяемое_значение не дорівнює жодному з таких значень, то повертається значеннярезультат X , вказаний після ключового слова ELSE (інакше).

Ключове слово ELSE не є обов'язковим. Якщо воно відсутнє і жодне з значень, що підлягають порівнянню, не дорівнює значенню, що перевіряється, то оператор CASE повертає NULL.

Допустимо, на основі таблиці Клієнти (див. рис. 1) потрібно отримати таблицю, в якій назви регіонів замінено їх кодовими номерами. Якщо у вихідній таблиці різних регіонів не надто багато, то для вирішення цього завдання зручно скористатися запитом з оператором CASE:

SELECT Ім'я, Адреса,

CASE Регіон

WHEN "Москва" THEN "77"

WHEN "Тверська область" THEN "69"

. . .

ELSE Регіон

AS Код регіону

FROM Клієнти;

Оператор CASE з умовами пошуку

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

CASE

WHEN умова1 THEN результат1

WHEN уоловіе2 THEN результат2

. . .

WHEN умова N THEN результат N

ELSE результатХ

Оператор CASE перевіряє, чи справді умова1 для першого запису в наборі, визначеному оператором WHERE, або у всій таблиці, якщо WHERE відсутня. Якщо так, то CASE повертає значення результат1. Інакше для цього запису перевіряється умова2. Якщо воно істинно, то повертається значення результат2 і т. д. Якщо жодна з умов не виконується, то повертається значення результат X , вказане після ключового слова ELSE.

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

Припустимо, у таблиці книги (Назва, Ціна) стовпець має значення NULL, якщо відповідної книги немає. Наступний запит повертає таблицю, в якій замість NULL відображається текст "Немає":

SELECT Назва,

CASE

WHEN Ціна IS NULL THEN "Нема в наявності"

ELSE CAST(Ціна AS CHAR(8))

AS Ціна

FROM Книги;

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

Зауважте, що замість першої форми оператора CASE завжди можна використовувати другу:

CASE

WHEN проверяемое_значение = значение1 THEN результат1

WHEN проверяемое_значение = значение2 THEN результат2

. . .

WHEN проверяемое_значение = значення N THEN результат N

ELSE результати

Функції NULLIF та COALESCE

У ряді випадків, особливо в запитах на оновлення даних (оператор UPDATE), зручно використовувати замість громіздкого оператора CASE компактніші функції NULLIF () (NULL, якщо) та COALESCE () (об'єднувати).

Функція NULLIF ( значення1, значення2) повертає NULL, якщо значення першого параметра відповідає значенню другого параметра, у разі невідповідності повертається значення першого параметра без змін. Тобто якщо рівність значення1 = значення2 виконується, то функція повертає NULL, інакше значення значення1.

Ця функція еквівалентна оператору CASE у наступних двох формах:

• CASE значення1

WHEN значення2 THEN NULL

ELSE значення1

• CASE

WHEN значення1 = значення2 THEN NULL

ELSE значення1

Функція COALESCE ( значення1, значення2, ... ,значення N) приймає список значень, які можуть бути певними, так і невизначеними (NULL). Ця функція повертає певне значення зі списку або NULL, якщо всі значення не визначені.

Ця функція еквівалентна наступному оператору CASE:

CASE

WHEN значення 1 IS NOT NULL THEN значення 1

WHEN значення 2 IS NOT NULL THEN значення 2

. . .

WHEN значення N IS NOT NULL THEN значення N

ELSE NULL

Припустимо, що в таблиці Книги (Назва, Ціна) стовпець Ціна має значення NULL, якщо відповідної книги немає. Наступний запит повертає таблицю, в якій замість NULL відображається текст "Немає":

SELECT Назва, COALESCE (CAST(Ціна AS CHAR(8)),

"Немає в наявності") AS Ціна

FROM Книги;

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

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

приклад. Знайти мінімальну та максимальну ціну на персональні комп'ютери:

приклад. Знайти наявну кількість комп'ютерів, випущених виробником А:

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

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

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

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

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

Пропозиція GROUP BYвикористовується визначення груп вихідних рядків, яких можуть застосовуватися агрегатні функції (COUNT, MIN, MAX, AVG та SUM). Якщо ця пропозиція відсутня, і використовуються агрегатні функції, то всі стовпці з іменами, згаданими в SELECT, повинні бути включені до агрегатні функції, і ці функції будуть застосовуватися до всього набору рядків, які відповідають предикату запиту. В іншому випадку всі стовпці списку SELECT, не ввійшлив агрегатні функції повинні бути вказані у пропозиції GROUP BY. У результаті всі вихідні рядки запиту розбиваються на групи, що характеризуються однаковими комбінаціями значень цих стовпцях.
Після цього до кожної групи буде застосовано агрегатні функції. Слід пам'ятати, що з GROUP BY всі значення NULL трактуються як рівні, тобто. при групуванні поля, що містить NULL-значення, всі такі рядки потраплять в одну групу. Якщоза наявності пропозиції GROUP BY , у пропозиції SELECTвідсутні агрегатні функції
, то запит просто поверне по одному рядку з кожної групи. Цю можливість, поряд із ключовим словом DISTINCT, можна використовувати для виключення дублікатів рядків у результуючому наборі.

Розглянемо простий приклад: SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price FROM PC

GROUP BY model;

У цьому запиті кожної моделі ПК визначається їх кількість і середня вартість. Усі рядки з однаковими значеннями model (номер моделі) утворюють групу, і на виході SELECT обчислюються кількість значень та середні значення ціни кожної групи. Результатом виконання запиту буде наступна таблиця:	model	Qty_model
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

Avg_price

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

• правил виконання агрегатних функцій Якщо в результаті виконання запитуне отримано жодного рядка
• (або одного рядка для цієї групи), то вихідні дані для обчислення будь-якої з агрегатних функцій відсутні. У цьому випадку результат виконання функцій COUNT буде нуль, а результатом всіх інших функцій - NULL.Аргумент агрегатної функціїне може сам містити агрегатні функції
• (функція від функції). Тобто. в одному запиті не можна, скажімо, одержати максимум середніх значень. Результат виконання функції COUNT єціле число
• (INTEGER). Інші агрегатні функції успадковують типи даних значень, що обробляються. Якщо при виконанні функції SUM був отриманий результат, що перевищує максимальне значення типу даних, що використовується, виникає.

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

Пропозиція HAVING

Після цього до кожної групи буде застосовано агрегатні функції. Слід пам'ятати, що з GROUP BY всі значення NULL трактуються як рівні, тобто. при групуванні поля, що містить NULL-значення, всі такі рядки потраплять в одну групу. пропозиція WHEREвизначає предикат для фільтрації рядків, то пропозиція HAVINGзастосовується після угрупованнядля визначення аналогічного предикату, що фільтрує групи за значеннями агрегатних функцій. Ця пропозиція потрібна для перевірки значень, які отримані за допомогою агрегатної функціїне з окремих рядків джерела записів, визначеного в пропозиції FROM, а з груп таких рядків. Тому така перевірка не може утримуватись у пропозиції WHERE.

SQL - Урок 8. Угруповання записів та функція COUNT()

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

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

У прикладі обидва аргументи дадуть однаковий результат, т.к. Усі стовпці таблиці мають тип NOT NULL. Давайте напишемо запит, використовуючи як аргумент стовпець id_topic:

SELECT COUNT(id_topic) FROM posts;

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

SELECT id_topic, COUNT(id_topic) FROM posts GROUP BY id_topic;

Оператор GROUP BYвказує СУБД згрупувати дані по стовпцю id_topic (тобто кожна тема - окрема група) і кожної групи підрахувати кількість рядків:

Ну ось, у темі з id=1 у нас 3 повідомлення, а з id=4 – одне. До речі, якби в полі id_topic були можливі відсутності значень, такі рядки були б об'єднані в окрему групу зі значенням NULL.

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

SELECT id_topic, COUNT(id_topic) FROM posts GROUP BY id_topic HAVING COUNT(id_topic) > 2;

В результаті маємо:

В уроці 4 ми розглядали, які умови можна задавати оператором WHERE, ті ж умови можна задавати і оператором HAVING, Тільки треба запам'ятати, що WHEREфільтрує рядки, а HAVING- Групи.

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