Рівень 19

Принципи SQL-запитів

Ти зі мною чи з ними? Настав час обирати, на чиєму ти боці!

AND OR

Не плутайте AND з OR!

Якщо істинними мають бути ВСІ умови, використовуйте AND.

Якщо істинною має бути ХОЧА Б ОДНА з умов, використовуйте OR.

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

Оператор OR справді корисний, але я не розумію, чому в попередньому прикладі ми не скористалися AND.

Чи можна використовувати більше одного AND або OR в одній умові WHERE?

Звісно, умов можна поєднувати скільки завгодно. Також в одній умові AND можна використовувати разом з OR.

Чим AND відрізняється від OR

Наступні приклади демонструють можливі комбінації двох умов, поєднаних за допомогою AND та OR.

LIKE: слово, що заощаджує час

У Каліфорнії забагато міст. Якщо Грег спробує перелічити їх усі в запиті, поєднуючи через OR, це забере в нього надто багато часу. На щастя, існує корисне ключове слово LIKE, яке разом зі спеціальними символами шукає частину текстового рядка й повертає збіги.

Грег може скористатися LIKE так:

SELECT * FROM my_contacts

WHERE location LIKE`%CA`;

Спеціальні символи

LIKE зазвичай використовують із двома спеціальними символами — «замінниками», які представляють фактичний вміст рядка. Спеціальні символи, наче джокер у карткових іграх, відповідають будь-якому символу (або послідовності символів) рядка.

МОЗКОВИЙ

ШТУРМ

Які ще спеціальні символи вам траплялися в цьому рівні?

Мені це LIKE

LIKE використовується зі спеціальними символами. Перший з них — знак % — позначає будь-яку кількість довільних символів.

SELECT first_name FROM

my_contacts WHERE first_name

LIKE `%им`;

Другий спеціальний символ, який так часто трапляється в парі з LIKE, — це знак підкреслення ( _ ), що позначає рівно один довільний символ.

SELECT first_name FROM my_contacts WHERE first_name LIKE `_им`;

Перевірка діапазонів за допомогою AND та операторів порівняння

Власник бару хоче відібрати напої, калорійність яких потрапляє в заданий діапазон. Як скласти запит для діапазону від 30 до 60 включно?

SELECT drink_name FROM drink_info

WHERE

calories >= 30

AND

calories <= 60;

Лише МІЖ нами... Є й інший спосіб

Щоб перевірити, чи входить значення в діапазон, також можна скористатися ключовим словом BETWEEN. Такий запис коротший за попередній запит, але повертає ті самі результати. Зверніть увагу: BETWEEN включає межі діапазону (30 і 60). Конструкція BETWEEN еквівалентна використанню операторів <= і >=, але не < і >.

SELECT drink_name FROM drink_info

WHERE calories BETWEEN 30 AND 60

Спробуймо відповісти на запитання та перевірити свої знання. Візьміть ручку й аркуш паперу та запишіть свої варіанти запитів до запитань нижче. Як завжди, звіримося з відповідями, коли будеш готовий.

Змініть запит так, щоб він повертав назви всіх напоїв, які містять понад 60 або менше 30 калорій.

Спробуйте використати BETWEEN з текстовими стовпцями. Напишіть запит, що повертає назви всіх напоїв, які починаються з літер від «Д» до «О».

Як ти думаєш, який результат поверне наступний запит?
SELECT drink_name FROM drink_info WHERE calories BETWEEN 60 AND 30

Умова IN

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

Аманда назвала свою таблицю black_book. Вона хоче отримати список вдалих побачень, тож відбирає записи з позитивними оцінками.

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

Ключові слова NOT IN

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

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

МОЗКОВИЙ

ШТУРМ

Коли NOT IN зручніший за IN?

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

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

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

Ого! Але щоб упорядкувати такий великий список фільмів, доведеться виконати величезну кількість команд SELECT. Мої старі плати цього не витримають! Ти новенький і гарненький, тобі мільйон обчислень на секунду — дрібниця, а я від такого просто перегріюся. Ось лише невелика частина того неподобства, яке нам доведеться виконати:

МОЗКОВИЙ

ШТУРМ

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

ORDER BY

Хочете впорядкувати результати свого запиту? Це зовсім нескладно: додайте до команди SELECT ключові слова ORDER BY та ім'я стовпця таблиці.

Час розворушити мозок...

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

Якщо додати до запиту умову ORDER BY title, нам більше не доведеться відбирати назви, що починаються з певної літери: запит сам поверне дані, вишикувані в алфавітному порядку за значенням стовпця title.

Для цього достатньо прибрати із запиту умову title LIKE, а решту зробить ORDER BY title.

ORDER BY дає змогу відсортувати дані будь-якого стовпця.

Час розворушити мозок...

Створімо просту таблицю з єдиним стовпцем CHAR(1) з іменем «test_chars».
Вставте в неї цифри, літери (верхнього й нижнього регістру) та неалфавітні символи, наведені нижче (кожен символ вставляється в окремий запис). Вставте пробіл і залиште один запис зі значенням NULL.

Виконайте для цього стовпця запит на вибірку з новою конструкцією ORDER BY. Заповніть пропуски в книзі.

!"#$%&' ()*+,-./0123: ;<=> ?@ABCD[\]^ 'abcd{|}~

Застосуйте порядок сортування SQL до заданого рядка та вкажіть вивід, який поверне команда ORDER BY

ORDER за двома стовпцями

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

На жаль, директор вигадав для вас ще дещо...

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

ORDER за кількома стовпцями

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

Ти можеш більше! Відсортуй УСІ дані! Можливості сортування не обмежуються лише двома стовпцями. Ви можете виконати сортування за будь-якою кількістю стовпців, щоб отримати потрібну інформацію.

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

SELECT * FROM movie_table

ORDER BY category, purchased, title;

Впорядкована таблиця

Погляньмо, які дані поверне команда SELECT для вихідної таблиці фільмів.

... і впорядковані результати нашого запиту:

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

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

За замовчуванням SQL впорядковує стовпці ORDER BY за зростанням: від А до Я, від 1 до 99999 тощо. Якщо ви хочете отримати дані у зворотному порядку, вкажіть після імені стовпця ключове слово DESC.

Поширені запитання

Як таке можливо? Адже ми використовували ключове слово DESC, щоб отримати ОПИС таблиці. Ви впевнені, що його можна застосовувати й для зміни порядку?

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

В умові ORDER його інтерпретують як скорочення від DESCENDING, і воно визначає порядок результатів.

Чи можу я використовувати у своїх запитах повні слова DESCRIBE і DESCENDING, щоб уникнути плутанини?

Ви можете використовувати DESCRIBE, але DESCENDING не спрацює.

Ключове слово DESC після імені стовпця в умові ORDER BY впорядковує результати за спаданням.

DESC і зміна порядку даних

Уявіть, що ваші дані стоять на сходинках драбини. Коли ви піднімаєтесь сходами (дані впорядковано за зростанням), літера А трапиться вам раніше за літеру Б. Коли ви спускатиметесь (дані впорядковано за спаданням), першою на вашому шляху трапиться літера Я, а останньою — літера А.

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

SELECT title, purchased

FROM movie_table

ORDER BY title ASC, purchased DESC;

Вам лист!

TO: Персоналу відеотеки Galaxy QA Academy

FROM: Директор

Subject: Налітай!

Усім привіт!

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

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

І не забудьте принести звіт!

Ваш директор.

P. S. І не надто наряджайтеся, мені тут треба пересунути кілька меблів...

Проблеми з печивом

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

Мені потрібно якомога швидше визначити переможницю.

Принцеса Лея, магістр дівчат-падаванок

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

Використайте свої навички роботи з ORDER BY і напишіть запит, який допоможе Едвіні дізнатися ім'я переможниці.

Напишіть свій варіант запиту на папірці, після чого клацніть на варіанті відповіді Магістра та порівняйте їх.

Отже, визначмо переможницю серед дівчат і дізнаємося, хто ж отримає безкоштовні уроки верхової їзди!

Сеанси групового гіпнозу з GROUP BY

Як учинити, коли запит для перепрошивки дроїдів потрібно застосувати не до всіх, а лише до групи певного типу? Щоб згрупувати дані, використовують чудо-команду SQL — GROUP BY. Ця конструкція створена для вибірки окремих груп рядків із таблиці, до кожної з яких застосовуються функції, указані в SELECT (наприклад, COUNT(), MIN() тощо).

Ще одне дуже поширене застосування GROUP BY в SQL — вибірка унікальних записів із таблиць. У наступному прикладі ви побачите, що в результуючій вибірці немає повторюваних імен дівчат, тоді як у вихідній таблиці вони повторювалися, і не раз.

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

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

Припустімо, у нас є таблиця користувачів:

id — унікальний ідентифікатор.
email — e-mail користувача.
hash — унікальний хеш користувача.

І перед нами постало завдання вибрати унікальних користувачів, причому саме унікальних людей, а не унікальні облікові записи. Адже в однієї людини може бути й 100 акаунтів з різними e-mail і, звісно, id. А hash — це певний рядок, що характеризує її як унікальну людину.

Отже, нам треба вибрати всі записи з унікальним hash. Для цього знову використовується GROUP BY:

SELECT * FROM `table` GROUP BY `hash`

У результаті буде отримано лише унікальні hash, тобто двох однакових hash у результуючій вибірці ви не побачите, а за допомогою DISTINCT цього б не вийшло!

Функція AVG із GROUP BY

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

Кожна дівчинка продавала печиво сім днів. Для кожної дівчинки функція AVG підсумовує її продажі, а потім ділить суму на 7.

MIN і MAX

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

Щоб визначити найбільше значення в стовпці, використовується функція MAX, а щоб визначити найменше — функція MIN.

SELECT first_name, MAX (sales)

FROM cookie_sales

GROUP BY first_name;

SELECT first_name, MIX (sales)

FROM cookie_sales

GROUP BY first_name;

COUNT, або підрахунок рядків

Щоб дізнатися, яка з дівчат продавала печиво більше днів, ніж інші, Едвіна намагається використати для підрахунку функцію COUNT. Функція COUNT повертає кількість записів у стовпці

Щоб дізнатися, скільки днів продавали печиво, можна було впорядкувати результат за sale_date,
і відняти від останньої дати першу.
Правильно?

Насправді ні. Ми не можемо бути певні, що між першою й останньою датою не було пропущених днів.

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

Команда SELECT DISTINCT

Спершу подивімося, як працює ключове слово DISTINCT без функції COUNT

Тепер спробуймо виконати команду з функцією COUNT:

Практичне завдання рівня

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

Теоретичний тест рівня SQL-2

Форма для надсилання створених вами SQL-запитів

Вихід у SQL-простір