Рівень 20

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

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

Нарешті! Грег знайшов пару для Реджі:

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

Наступного дня Реджі стоїть під дверима Грега, і він дуже розлючений.

Реджі кричить: "Звісно, вона цікавиться тваринами. Але ти не сказав мені, що вона робить з них опудала! Там повсюди мертві тварини!"

Який вигляд матиме наступний запит Грега після створення кількох стовпців із захопленнями?

Блиснімо не лише металокерамікою, а й інтелектом, записавши на папері свій варіант розв'язання цього завдання. А потім звіримося з варіантом Магістра.

contact_id
last_name
first_name
phone
email
gender
birthday
profession
city
state
status
interest 1
interest 2
interest 3
interest 4
seeking

Подумаймо й запишемо відповідь для цього запиту, а потім звіримося з правильним варіантом від Магістра.

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

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

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

Неатомарні стовпці з наявної таблиці слід перемістити до нових таблиць.

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

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

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

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

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

Опис даних (стовпців і таблиць) вашої бази даних, зокрема всіх взаємопов'язаних об'єктів і зв'язків між ними, називається СХЕМОЮ.

Ви побачили, як перетворили таблицю з інформацією про клоунів. Тепер спробуймо зробити те саме з таблицею my_contacts.

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

Нижче показано спрощене подання таблиці my_contacts.

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

Видаляємо стовпець interests і розміщуємо його в окремій таблиці.

Стовпець interests переміщується до нової таблиці.

У новій таблиці interests зберігатимуться всі захоплення з таблиці my_contacts (окремий запис для кожного захоплення).

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

Ми винесли захоплення з таблиці my_contacts, але як визначити, кому які захоплення належать? Потрібно взяти інформацію з таблиці my_contacts і розмістити її в таблиці interests так, щоб ці дві таблиці були пов'язані між собою.

Наприклад, для цього можна включити стовпці first_name і last_name до таблиці interests.

Ми рухаємося в правильному напрямку, але first_name і last_name — не найкращий спосіб зв'язування таблиць.

Чому?

Дві таблиці мають зв'язуватися через унікальний стовпець. На щастя, оскільки ми вже узялися до нормалізації, у my_contacts такий стовпець уже є: це первинний ключ.

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

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

Нагадаємо, що ПЕРВИННИЙ КЛЮЧ (primary key) є одним із прикладів унікальних індексів і застосовується для унікальної ідентифікації записів таблиці. Жодні два записи таблиці не можуть мати однакових значень первинного ключа. Первинний ключ зазвичай скорочено позначають як PK (primary key).

Зовнішній ключ має бути пов'язаний з унікальним значенням із батьківської таблиці.

Це значення необов'язково має бути значенням первинного ключа, але воно неодмінно має бути унікальним.

Функція SUBSTR отримує текст стовпця interests і повертає його задану частину. Ми виділяємо символи, які були скопійовані до interest1, а також ще два символи (кому та пробіл).

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

Однак роботу ще не закінчено. Тепер потрібно зробити те саме для стовпців interest2, interest3 та interest4.

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

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

Звісно, ми можемо написати чотири окремі команди SELECT для виведення всіх значень:

SELECT interest1 FROM my_contacts; SELECT interest3 FROM my_contacts;

SELECT interest2 FROM my_contacts; SELECT interest4 FROM my_contacts;

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

Перевірмо наші знання та поміркуймо над запитанням нижче. І порадуймо Магістра правильною відповіддю!

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

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

Перехресне з'єднання (CROSS JOIN) повертає комбінації кожного запису першої таблиці з кожним записом другої таблиці.

Результат з'єднання складається з 20 записів (5 іграшок * 4 хлопчики), тобто з усіх можливих комбінацій.

І навіщо мені це потрібно?

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

Розгляньмо наведені нижче таблиці. У кожного хлопчика є лише одна іграшка. Зв'язок належить до типу "один до одного", а toy_id — зовнішній ключ.

Усе, що потрібно, — визначити, яка іграшка належить кожному з хлопчиків. Ми можемо скористатися внутрішнім з'єднанням з оператором = для пошуку збігів зовнішнього ключа boys з первинним ключем toys.

Еквівалентне з'єднання — це внутрішнє з'єднання з перевіркою рівності.

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

Нееквівалентне з'єднання перевіряє неспівпадіння значень.

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

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

Природне з'єднання зв'язує записи за значеннями стовпців з однаковими іменами.

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

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

Запит усередині іншого запиту?

Хіба так можна?

Інтерв'ю тижня:

СЕНСАЦІЯ! РОЗСЛІДУВАННЯ! Псевдоніми SQL: що вони приховують?

Після глави 8 ви можете будувати з'єднання, як справжній SQL-професіонал. Нижче перелічено основні поняття цієї глави. Повний список інструментів наведено в додатку 3.

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

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

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

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

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

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

Падаване, перевіримо, як ти зрозумів нову тему, і закріпимо твої знання. Спробуй написати такий запит

Фактично ми лише об'єднуємо два запити в один. Перший запит називається зовнішнім, а другий — внутрішнім.

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

Оскільки підзапит використовує оператор =, він повертає одне значення, один запис з одного стовпця (іноді його називають "коміркою", але в SQL використовують термін скалярне значення). Це значення порівнюється зі стовпцями в умові WHERE.

Подивімося, як працює аналогічний запит до таблиці my_contacts. РСКБД зчитує скалярне значення з таблиці zip_code і порівнює його зі стовпцями в умові WHERE.

Чому не можна зробити те саме за допомогою з'єднань?

Нижче перелічено деякі правила, яким мають відповідати підзапити. Заповніть пропуски словами з наступного набору (деякі слова можна використовувати кілька разів).

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

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

Напишіть запити для отримання відповідей на такі запитання (використовуйте з'єднання та некорельовані підзапити там, де це доречно). Використовуйте схему бази даних gregs_list.

Отже, підзапит можна вкласти в інший підзапит?

Як найкраще побудувати підзапит усередині підзапиту?

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

Знайти найвисокооплачуванішого веб-дизайнера

Знайти людей, які заробляють х

після чого підставити перший запит замість х.

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

У лівому зовнішньому з'єднанні для КОЖНОГО ЗАПИСУ ЛІВОЇ таблиці шукається відповідність серед записів правої таблиці.

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

Нижче наведено синтаксис лівого зовнішнього з'єднання на прикладі таблиць, які вже використовувалися. Таблицю girls указано першою після FROM, тому вона вважається лівою таблицею; далі йдуть ключові слова LEFT OUTER JOIN; і нарешті, таблиця toys вважається правою таблицею.

І все? Питається, чого ми досягли? Виходить, зовнішнє з'єднання нічим не відрізняється від внутрішнього.

Відрізняється: зовнішнє з'єднання повертає запис незалежно від того, чи є в нього збіг у таблиці.

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

Напишіть запити для отримання відповідей на такі запитання (використовуйте з'єднання та некорельовані підзапити там, де це доречно). Використовуйте схему бази даних gregs_list.

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

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

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

Ми вважаємо, що Містер Сніфлз є власним начальником, тому в нього значення boss_id збігається з id.

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

САМОПОСИЛАЛЬНИЙ зовнішній ключ — це первинний ключ таблиці, що використовується в тій самій таблиці для інших цілей.

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

SELECT name, boss_id FROM clown_info;

Але нам потрібні пари: ім'я клоуна та ім'я його начальника.

Напишіть запити для отримання відповідей на такі запитання (використовуйте з'єднання та некорельовані підзапити там, де це доречно). Використовуйте схему бази даних gregs_list.

Існує ще один спосіб отримання об'єднаних результатів таблиць — так звані об'єднання (ключове слово UNION).

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

Грег помічає, що в результаті немає дублікатів, проте посади перелічено не по порядку, тож він намагається повторити запит, додавши умову ORDER BY до кожної команди SELECT.

Як ви думаєте, що сталося під час виконання нового запиту?

Кількість стовпців у командах SELECT має бути однаковою. Не можна вибрати два стовпці однією командою, а ще один стовпець — іншою.

Як ви думаєте, що станеться, якщо стовпці, які об'єднуються, належать до різних типів даних?

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

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

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

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

Тепер ти справжній SQL-джедай

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