🗊Презентация Формування SQL-запиту на вибірку. Використання агрегатних функцій. Підзапити

Заняття 5. Формування SQL-запиту на вибірку. Використання агрегатних функцій. Підзапити

Визначення SQL-запиту на вибірку Запит на вибірку даних – це команда або інструкція, яка дається СУБД, щоб вона вивела певну інформацію з таблиць бази даних.

Загальний синтаксис інструкції SELECT SELECT [ALL | DISTINCT] {* | вираз-стовпець [AS псевдонім] [, ...]} FROM таблиця [, ...] ![WHERE умова пошуку] ![GROUP BY список стовпців групування] ![HAVING умова пошуку] вийняткові ![ORDER BY список стовпців сортування умова сортування];

Порядок виконання SQL-запиту на вибірку FROM – СУБД вибирає таблицю з бази даних. WHERE – з таблиці вибираються записи, які відповідають умові пошуку, і відкидаються решта (фільтр записів). GROUP BY – створюються групи записів, відібраних оператором WHERE (якщо він є в SQL-виразі), і кожна група відповідає якому-небудь значенню стовпця групування. Стовпець групування може бути будь-яким стовпцем таблиці, заданій в операторі FROM, а не лише тими, які вказані у виразі SELECT. HAVING – опрацьовує кожну із створених груп записів, залишаючи лише ті з них, які задовольняють умові. Цей оператор використовується лише разом з оператором GROUP BY. SELECT – вибирає з таблиці, віртуально створеної в результаті застосування наведених операторів, лише вказані стовпці. ORDER BY – сортує записи таблиці. При цьому в умову сортування можна вказувати лише ті стовпці, які вказані в операторі SELECT.

Усунення надлишковості вибраних даних Ключове слово DISTINCT (ВІДМІННІСТЬ) усуває повторювані значення з команди SELECT: SELECT DISTINCT стовп_1, … FROM таблиця; DISTINCT слідкує за тим, які значення стовп_1 були раніше, щоб вони не дублювались у результатній таблиці.

Усунення надлишковості вибраних даних в MySQL ALL DISTINCT LIMIT (вказується вкінці запиту)

Основні типи умов пошуку (предикатів) порівняння – порівнюються результати обчислення одного виразу з результатами обчислення іншого виразу; діапазон – перевіряється, чи попадає результат обчислення виразу у заданий діапазон значень; належність до множини – перевіряється, чи належить результат обчислення виразу до заданої множини значень; відповідність шаблону – перевіряється, чи відповідає деяке символьне значення заданому шаблону; існування – перевіряється чи існує хоча б один рядок, який задовольняє умові; перевірка на невизначене значення – перевіряється, чи містить заданий стовпець значення NULL.

Спеціальні SQL-предикати. Належність до множини IN та NOT IN визначає список значень, в який може або не може входити дане значення стовпця WHERE стовп IN (зн_1, …); Альтернативою є поєднання предикатів порівняння з логічною операцією OR.

Спеціальні SQL-предикати. Предикат діапазону BETWEEN визначає діапазон значень, в який має попадати задане значення стовпця. Включає граничні значення у діапазон. WHERE стовп BETWEEN зн_1 AND зн_2; На відміну від оператора IN, оператор BETWEEN є чутливим до порядку, тобто першим має бути менше значення (як символьне так і числове). Має особливості роботи з символьними значеннями!!!

Спеціальні SQL-предикати. Предикат шаблона LIKE (подібний) та NOT LIKE (не подібний) застосовуються тільки до полів типу CHAR або VARCHAR, в яких вони знаходить підстрічки. В якості умови вони використовують групові символи, або маски, яких є два типи: символ підкреслення ( _ ), який заміняє одиничний символ; знак процента ( % ), який заміняє послідовність символів довільної довжини. WHERE стовп LIKE ' ';

Спеціальні SQL-предикати. Предикат існування EXISTS та NOT EXISTS – предикати, які повертають значення TRUE або FALSE, і які можна застосовувати окремо або разом з іншими булевими виразами. EXISTS не може використовувати агрегатні функції у своєму підзапиті. У зв'язаних підзапитах предикат EXISTS виконується для кожного рядка зовнішньої таблиці. Можна комбінувати предикат EXISTS із з'єднаннями таблиць. SELECT * FROM Customer WHERE EXISTS (SELECT * FROM Customer WHERE City = 'SanJose');

Спеціальні SQL-предикати. Перевірка на значення NULL IS NULL застосовується для виявлення записів, в яких той чи інший стовпець має невідоме значення. IS NOT NULL застосовується, коли необхідно виключити з результатів запис з NULL-значеннями. WHERE стовп IS NULL;

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

Аргумент ODER BY Застосовується для упорядкування (сортування) записів. Записується і виконується вкінці запиту. Сортує записи усієї таблиці або окремих її груп, у випадку застосування оператора GROUP BY. Після імені стовпця групування можна вказувати ключове слово, яке задає режим сортування: ASC – за зростанням (за замовчуванням) і DESC – за спаданням. Усі стовпці, які впорядковуються, повинні вказуватись у виразі SELECT.

Агрегатні (статистичні) функції в SQL COUNT (параметр) – обчислює кількість записів, вказаних у параметрі. Якщо необхідно отримати кількість усіх записів, то в якості параметра вказується * (або унікальний ідентифікатор). Якщо в якості параметра вказано ім’я стовпця, то функція поверне кількість записів, в яких цей стовпець має не NULL значення. Щоб знати, скільки різних значень має стовпець, перед його іменем вказується ключове слово DISTINCT.

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

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

Використання агрегатних функцій у підзапитах Приклад 7. Вивести усі операції за 3 жовтня з платежем вище середнього: SELECT * FROM Orders WHERE odate = '2009-10-03' AND amt > (SELECT AVG(amt) FROM Orders);

Підзапити, в результаті яких виходить декілька значень IN – використовується з підзапитами. BETWEEN – не використовується з підзапитами. LIKE – не використовується з підзапитами.

Підзапити у фразі HAVING Такі підзапити можуть використовувати свої агрегатні функції, якщо вони не виводять декількох значень. Приклад 10. Порахувати кількість замовників з рейтингом, вищим середнього, у місті San Jose: SELECT rating, COUNT(cnum) FROM Customers GROUP BY rating HAVING rating > (SELECT AVG(rating) FROM Customers WHERE city = 'SanJose');

Завдання 1. Написати запити на вибірку для власної бази даних 1 запит, який відображає тільки декілька стовпців із певної таблиці; 3 запити з використанням мінімум будь-яких 2 спеціальних предикатів; 3 запити із використанням 3 різних агрегатних функцій; 3 запити із використанням підзапитів (мінімум один з них з агрегатною функцією).