Запити MySQL у декількох таблицях (JOINs) ІТ-центр
навчальні матеріали для занять з інформатики
(остання зміна на цій сторінці: 13.11.2016)
Відеоурок
Якщо вам подобається лише відео подивись на, ти навряд чи щось навчишся. Для кращого ефекту навчання працюйте безпосередньо на комп’ютері та дотримуйтесь прикладів.
Фольги
(остання синхронізація презентації у форматі PDF: 02.08.2017)
Якщо ви не можете побачити презентацію PDF, натисніть тут, щоб завантажити: Пряме завантаження презентації у форматі PDF
Ця послідовність передбачає знання такого змісту: Вступ до запитів SELECT у MySQL.
Вправи з докладними рішеннями та поясненнями Ви можете знайти тут: Вправи на JOIN в MySQL
Дамп MySQL "customer-simply.sql"
Щоб ви могли зрозуміти приклади у слайдах та у відеоуроці, завантажте дамп MySQL: customer-simply.sql, відкрийте його у Workbench (Файл -> Відкрити SQL Script…) і дайте йому запуститися. Створюється база даних "Клієнти", яка містить дві таблиці ("Місцеположення" та "Клієнти").
Запитувати дані з двох таблиць
Приклад сценарію: Зображення показує дві зв’язані таблиці, пов’язані із зовнішнім ключем. Подумайте, які первинні ключі, а які зовнішні.
Рішення:
Клієнти (customerID, ім'я клієнта, ім'я клієнта, телефон клієнта, ↑ країна клієнта, номер клієнта)
країн (landID, countryName, countryDelivery status)
Отже, первинними ключами є customerID та countryID (розпізнаються підкресленням), зовнішнім ключем є customerLand у таблиці клієнтів (розпізнається стрілкою перед customerCountry). Зовнішній ключ kundeLand відноситься до первинного ключа країн таблиці.
Отже, вкладка “замовник” містить поле “customerLand”. Сюди вводиться ідентифікатор країни, який посилається на поле landID у таблиці “країни”.
Якщо ви хочете відобразити ім'я клієнта та назву країни в повному тексті в запиті SELECT, вам потрібно отримати дані з обох таблиць.
Код для створення цих двох таблиць:
з деякими фіктивними даними:
Варіант 1: ПРИЄДНАТИСЯ/ЛІВО ПРИЄДНАТИСЯ
Вибирає зазначені поля (customerID, customerName, customerCountry, countryID, countryName) з двох таблиць customers і countries (customer JOIN countries) і обмежується випадками, коли customerCountry відповідає countryID (WHERE customerCountry = countryID).
Якщо оператор WHERE тут опущений, усі записи даних від "замовників" поєднуються з усіма записами даних від "країн", так що з 3 країнами та 4 клієнтами виводиться загалом 12 рядків (чотири з яких виводяться три рази кожна - так надлишковий).
Цей безглуздий результат називається декартовим продуктом. Завжди переглядайте результати запитів, щоб переконатися, що ви зрозуміли результати своїх запитів із правильними обмеженнями.
Якщо в таблицях Поля з однаковими назвами повинна бути вказана таблиця, з якої слід взяти значення, синтаксис: ім'я таблиці. ім'я поля
ЛІВО ПРИЄДНАЙТЕСЬ
Якщо значення таблиці customerCountry вказано в таблиці «клієнти», яку неможливо знайти в таблиці «країни», тоді цей запис даних не виводиться. Такий неправильний запис даних буде доповненням до переліку SQL вище, наприклад,.
LandID 9 не існує, тому запис даних не виводиться у наведеному запиті.
За допомогою LEFT JOIN виводяться всі записи даних лівої (“лівої”) таблиці:
Увага: "ДЕ" замінено на "УВІМК.".
Варіант 2: неявне позначення
Замість того, щоб писати JOIN явно, ми можемо просто відокремити таблиці, які потрібно запитувати, комами:
До речі, ВНУТРІШНЄ ПРИЄДНАННЯ - це те саме, що і ПРИЄДНАЙТЕСЯ. Говорять про синтаксичний цукор.
Вправи
Всі вправи можна знайти у колекції матеріалів (там також є всі додаткові файли, такі як зображення, схеми класів або шаблони HTML!).
Ви можете завантажити поточну вправу тут як файл txt.
***** Вправи: MySQL - SELECT: Запит декількох таблиць за допомогою JOINs *****
MySQL06_1: декартовий продукт
Поясніть своїми словами, чому цей запит не дає значущого результату:
--- Запуск коду MySQL ---
ВИБЕРІТЬ * З КЛІЄНТІВ, МІСЦЯ
--- Кінець коду ---
MySQL06_2: Прості запити за двома таблицями - БД просто для клієнта
Для цієї вправи використовуйте цю базу даних: 07mysql/_dumps/customers/customers-simply.sql
A) Введіть ім’я, поштовий індекс та місто всіх клієнтів. Список містить ім’я клієнта та назву місця, де він живе.
Б) Введіть ім’я та місце проживання всіх клієнтів, які мають поштовий індекс 79312.
В) Введіть ім’я та місце проживання всіх клієнтів, які проживають в Еммендінгені (критерієм обмеження є НЕ поштовий індекс, а „Еммендінген“).
Г) Введіть назву, місце проживання та кількість мешканців для всіх клієнтів, які проживають у місці, де проживає понад 70 000 жителів.
Д) Перелічіть усі місця, де мешкає менше 1 000 000 жителів.
F) Введіть ім'я клієнта та ім'я місця для всіх клієнтів, які проживають у місцях з населенням від 100 000 до 1 500 000.
G) Введіть ім'я клієнта, поштовий індекс та назву міста для всіх клієнтів, чиє ім'я містить "e", і всіх міст, які містять "u" або "r" (тому frEd from stUden відображається точно так само, як jEssE від bRnz, з солей, але ні, а також мартин з гамбурга).
MySQL06_2a: Вправи на запити за двома таблицями: Книгарня
Використовуйте цю базу даних для цієї вправи:
07mysql/_dumps/buchladen/buchladen.sql
A) Перелічіть усіх постачальників, які проживають у Reute. Результат: назва постачальника, назва місця, поштовий індекс
Б) Відобразіть список усіх видавців, імена яких починаються на L. Видання: назва видавця, назва місця; будь-ласка, сортуйте за назвою місця у порядку зменшення.
В) Відобразіть список усіх постачальників, імена яких починаються на Schus. або Лое . починається і хто сидить в Еммендінгені. Результат: Назва постачальника, назва місця, відсортована за назвою постачальника у порядку зростання.
Г) Відобразити список усіх місць, але лише тих місць, в яких знаходиться видавець, із назвою n у назві.
E) Надайте список усіх книг та їх постачальників (увага: зверніться до 3 таблиць!)
F) Надайте список усіх книг, вартість яких перевищує 10 євро (видання: назва постачальника, назва книги, ціна продажу) - Увага: продивіться 3 таблиці
G) Вивести список усіх книг (видання: назва книги, категорія) - Увага: запитайте 3 таблиці
MySQL06_3: ПРИЄДНАННЯ проти ЛІВОГО ПРИЄДНАННЯ
Для цієї вправи використовуйте цю базу даних: 07mysql/_dumps/customers/customers-simply.sql
Розгляньте наступні два твердження та поясніть, чим будуть відрізнятися таблиці результатів. Після цього спробуйте команди і перевірте свою відповідь.
--- Запуск коду MySQL ---
-- Заява 1: ПРИЄДНАЙТЕСЬ ЛІВО
ВИБЕРІТЬ k.customer_id, k.name, o.name З КЛІЄНТІВ ЯК k ЗЛІВА ПРИЄДНАЙТЕСЯ КАК o o УВІМКНЕНО або поштовий індекс = k.ort_postcode ЗАМОВИТИ за k.customer_id
-- Заява 2: EQUI-JOIN
ВИБЕРІТЬ k.customer_id, k.name, o.name ВІД замовника ЯК k, розташування AS o ДЕ або поштовий індекс = k.ort_postcode ЗАМОВИТИ за k.customer_id
--- Кінець коду ---
MySQL06_4: Вправи на запити до 5 таблиць: Книгарня
Використовуйте цю базу даних для цієї вправи:
07mysql/_dumps/buchladen/buchladen.sql
А) Ми шукаємо всіх постачальників, які базуються у Фрайбурзі. Введіть назву постачальника, місцезнаходження постачальника та поштовий індекс.
(Рішення: Виходять три постачальники.)
Б) Ми шукаємо всіх видавців, які базуються в Мюнхені. Введіть назву видавця та місце публікації.
(Рішення: Є три видавці.)
В) Ми шукаємо всі книги, видані Verlag Assal. Введіть назву книги, рік видання та назву видавця, відсортовану за роком видання в порядку зменшення.
(Підказка: Вийшло чотири книги.)
Г) Ми шукаємо всі книги у постачальника Schustermann. Вихідна назва книги та назва постачальника.
(Рішення: Вийдуть три книги.)
Д) Всі трилери розшукуються. Вивести назву та категорію книги, відсортовану за назвою книги.
(Підказка: Буде видано п’ять книг, перша - „Один останній поцілунок“.)
F) Ми шукаємо всі любовні романи. Відображення назви книги, категорії та назви видавця, відсортованих за назвою книги у порядку зростання.
(Рішення: Виходить сім записів, "Спочатку сільський лікар та його секретар").
Ж) Ми шукаємо всі книги Сабріни Мюллер. Введіть прізвище автора, ім’я та назву книги, відсортовані за назвою книги в порядку зменшення.
(Підказка: Повертаються чотири записи, спочатку "Дике полювання на Джона Сміта - Відплата".)
З) Розшукуються всі трилери Сабріни Мюллер. Включіть ім’я автора, назву книги та категорію.
(Рішення: Виводиться два набори даних.)
(Попередження: насправді ви цього поки не можете зробити!)
Ми шукаємо всі книги Сабріни Мюллер, які можна класифікувати за категоріями трилер або гумор. Включіть ім’я автора, назву книги та категорію.
Щоб книга "Дике полювання на Джона Сміта - Помста" не з'являлася двічі, вам доведеться згрупувати результат за назвою книги, додавши ORDER BY перед ORDER BY:
--- Запуск коду MySQL ---
ГРУПА ЗА НАЗВОМ
--- Кінець коду ---
Так що обидві категорії тепер також відображаються в стовпці "опис" для цієї книги, не просто використовуйте назву поля "опис" для виводу категорії, а скоріше
--- Запуск коду MySQL ---
GROUP_CONCAT (ім'я)
--- Кінець коду ---
Це означає, що для книг, які класифікуються на кілька категорій, усі категорії відображаються в одному полі, розділеному комами.
(Рішення: Виводиться три записи даних.)