MySQL заявки в множество таблици (JOINs) ИТ център
учебни материали за часове по информатика
(последна промяна на тази страница: 13.11.2016)
Видео урок
Ако ви харесва само видеото погледнете, едва ли ще научите нещо. За най-добър ефект на обучение работете директно на компютъра и следвайте примерите.
Фолиа
(последно синхронизиране на PDF презентацията: 02.08.2017)
Ако не можете да видите PDF презентация, щракнете тук, за да я изтеглите: Директно изтегляне на PDF презентация
Тази последователност предполага, че сте запознати със следното: Въведение в заявките SELECT в MySQL.
Упражнения с подробни решения и обяснения можете да намерите тук: Упражнения за JOINs в MySQL
MySQL дъмп "клиент-просто.sql"
За да можете да разберете примерите в слайдовете и във видео урока, изтеглете този MySQL дъмп: customer-simply.sql, отворете го в Workbench (File -> Open SQL Script ...) и го оставете да работи. Създава се база данни "Клиенти", която съдържа две таблици ("Местоположения" и "Клиенти").
Заявка за данни от две таблици
Примерен сценарий: Картината показва две свързани таблици, които са свързани с външен ключ. Помислете кои са първичните ключове и кои са външните ключове.
Решение:
Клиенти(Клиентски номер, име на клиента, име на клиента, телефон на клиента, ↑ страна на клиента, номер на клиента)
държави(landID, countryName, countryDelivery status)
Следователно първичните ключове са customerID и countryID (разпознаваеми чрез подчертаването), външният ключ е customerLand в таблицата на клиентите (разпознаваем със стрелката пред customerCountry). Външният ключ kundeLand се отнася до първичния ключ на страните от таблицата.
Следователно раздел „клиент“ съдържа поле „customerLand“. Тук се въвежда идентификаторът на държава, който се отнася до полето landID в таблицата „страни“.
Ако искате да покажете името на клиента и името на държавата в пълен текст в заявка SELECT, трябва да извлечете данни от двете таблици.
Код за генериране на тези две таблици:
с някои фиктивни данни:
Вариант 1: ПРИСЪЕДИНЕТЕ/НАЛЯВО СЕ ПРИСЪЕДИНЕТЕ
Избира посочените полета (customerID, customerName, customerCountry, countryID, countryName) от двете таблици клиенти и държави (клиентски ПРИСЪЕДИНЯВАНЕ страни) и е ограничено до случаите, когато customerCountry съвпада с countryID (WHERE customerCountry = countryID).
Ако операторът WHERE е пропуснат тук, всички записи на данни от „клиенти“ се комбинират с всички записи на данни от „страни“, така че за 3 държави и 4 клиенти се извеждат общо 12 реда (четири от които се извеждат три пъти всеки - така 8 излишен).
Този безсмислен резултат се нарича декартов продукт. Винаги преглеждайте резултатите от заявката си, за да видите дали сте осмислили резултатите от заявката си с правилни ограничения.
Ако в таблиците Полета с идентични имена трябва да се посочи таблицата, от която трябва да се вземе стойността, синтаксис: име на таблица.име на поле
НАЛЯВО ПРИСЪЕДИНЯВАНЕ
Ако в таблицата „клиенти“ е посочена стойност за customerCountry, която не може да бъде намерена в таблицата „страни“, този запис на данни не се извежда. Такъв неправилен запис на данни би бил допълнение към SQL списъка по-горе, напр.
LandID 9 не съществува, така че записът на данни не се извежда в горната заявка.
С LEFT JOIN се извеждат всички записи на данни от лявата („лява“) таблица:
Внимание: „WHERE“ се заменя с „ON“.
Вариант 2: имплицитна нотация
Вместо да пишете JOIN изрично, можем просто да отделим таблиците, които ще бъдат заявени със запетаи:
Между другото, INNER JOIN е точно същото като JOIN. Говори се за синтактична захар.
Упражнения
Всички упражнения могат да бъдат намерени в колекцията от материали (там са и всички допълнителни файлове като изображения, диаграми на класове или HTML шаблони!).
Можете да изтеглите текущото упражнение тук като txt файл.
***** Упражнения: MySQL - SELECT: Заявка за множество таблици с JOINs *****
MySQL06_1: Декартов продукт
Обяснете със собствени думи защо тази заявка не дава значим резултат:
--- Стартиране на кода на MySQL ---
ИЗБЕРЕТЕ * ОТ клиенти, местоположения
--- Край на кода ---
MySQL06_2: Прости заявки за две таблици - DB-проста за клиента
Използвайте тази база данни за това упражнение: 07mysql/_dumps/customers/customers-simply.sql
A) Въведете името, пощенския код и града на всички клиенти. Списъкът съдържа името на клиента и името на мястото, където той живее.
Б) Въведете името и местоживеенето на всички клиенти, които имат пощенски код 79312.
В) Въведете името и местоживеенето на всички клиенти, които живеят в Emmendingen (критерият за ограничение НЕ е пощенският код, а „Emmendingen“).
Г) Въведете името, местоживеенето и броя на жителите за всички клиенти, които живеят на място с повече от 70 000 жители.
Д) Избройте всички места с по-малко от 1 000 000 жители.
Е) Въведете името на клиента и мястото на всички клиенти, които живеят на места с население между 100 000 и 1 500 000.
G) Въведете името на клиента, пощенския код и името на града за всички клиенти, чието име съдържа „e“ и всички градове, които съдържат „u“ или „r“ (така че frEd от stUden се показва точно като jEssE от bRnz, от соли, но не и мартин от хамбург).
MySQL06_2a: Упражнения за заявки в две таблици: Книжарница
Използвайте тази база данни за това упражнение:
07mysql/_dumps/buchladen/buchladen.sql
A) Избройте всички доставчици, които живеят в Reute. Резултат: име на доставчик, име на място, пощенски код
Б) Покажете списък на всички издатели, чиито имена започват с L. Издание: име на издател, име на място; моля сортирайте по име на място в низходящ ред.
В) Покажете списък на всички доставчици, чиито имена започват с Schus. или Лое . започва и които седят в Емендинген. Резултат: Име на доставчика, име на място, сортирано по име на доставчик във възходящ ред.
Г) Показване на списък с всички места, но само местата, на които се намира издателят с n в името му.
Д) Осигурете списък на всички книги и техните доставчици (внимание: консултирайте се с 3 таблици!)
Е) Предоставете списък на всички книги, които струват над 10 евро (издание: име на доставчик, заглавие на книгата, продажна цена) - Внимание: консултирайте се с 3 таблици
G) Изведете списък с всички книги (издание: заглавие на книгата, категория) - Внимание: запитвайте 3 таблици
MySQL06_3: ПРИСЪЕДИНЕТЕ се срещу НАЛЯВО ПРИСЪЕДИНЯВАНЕ
Използвайте тази база данни за това упражнение: 07mysql/_dumps/customers/customers-simply.sql
Погледнете следните две твърдения и обяснете как ще се различават таблиците с резултати. След това опитайте командите и проверете отговора си.
--- Стартиране на кода на MySQL ---
-- Изявление 1: ЛЯВО ПРИСЪЕДИНЯВАНЕ
ИЗБЕРЕТЕ k.customer_id, k.name, o.name ОТ клиенти КАТО НАЛЯВО СЕ СЕ ПРИСЪЕДИНЯВАТ КАТО o ВКЛЮЧЕНО или пощенски код = k.ort_postcode ПОРЪЧАЙТЕ по k.customer_id
-- Изявление 2: EQUI-JOIN
ИЗБЕРЕТЕ k.customer_id, k.name, o.name ОТ клиент КАТО k, местоположения КАТО o КЪДЕ или пощенски код = k.ort_postcode ПОРЪЧКА ПО k.customer_id
--- Край на кода ---
MySQL06_4: Упражнения за заявки за до 5 таблици: Книжарница
Използвайте тази база данни за това упражнение:
07mysql/_dumps/buchladen/buchladen.sql
А) Търсим всички доставчици със седалище във Фрайбург. Въведете име на доставчика, местоположение на доставчика и пощенски код.
(Решение: Излизат трима доставчици.)
Б) Търсим всички издатели със седалище в Мюнхен. Въведете името на издателя и мястото на публикуване.
(Решение: Има трима издатели.)
В) Търсим всички книги, издадени от Verlag Assal. Въведете заглавието на книгата, годината на издаване и името на издателя, сортирано по година на издаване в низходящ ред.
(Решение: Излизат четири книги.)
Г) Търсим всички книги от доставчика Schustermann. Изходно заглавие на книгата и име на доставчика.
(Решение: Ще бъдат издадени три книги.)
Д) Търсят се всички трилъри. Извеждане на заглавие и категория на книгата, сортирано по заглавие на книгата.
(Съвет: Ще бъдат издадени пет книги, първата е „Една последна целувка“.)
Е) Търсим всички любовни романи. Показване на заглавието на книгата, категорията и името на издателя, сортирано по заглавие на книгата във възходящ ред.
(Решение: Излизат седем записа, първо „Държавният лекар и неговият секретар“.)
Ж) Търсим всички книги на Сабрина Мюлер. Въведете фамилията, името и заглавието на автора, сортирани по заглавие на книгата в низходящ ред.
(Съвет за решение: Връщат се четири записа, първо „Wild Hunt for John Smith - Retribution“.)
З) Издирвани са всички трилъри на Сабрина Мюлер. Включете име на автора, заглавие на книгата и категория.
(Решение: Извеждат се два набора от данни.)
(Предупреждение: все още не можете да го направите!)
Търсим всички книги на Сабрина Мюлер, които могат да бъдат класифицирани в категориите трилър или хумор. Включете име на автора, заглавие на книгата и категория.
За да не се появи книгата "Дивият лов на Джон Смит - Отмъщението" два пъти, трябва да групирате резултата по заглавие на книгата, като добавите ORDER BY пред ORDER BY:
--- Стартиране на кода на MySQL ---
GROUP BY заглавие
--- Край на кода ---
Така че и двете категории сега се показват и в колоната "описание" за тази книга, не просто използвайте името на полето "описание" за изхода на категорията, а по-скоро
--- Стартиране на кода на MySQL ---
GROUP_CONCAT (име)
--- Край на кода ---
Това означава, че за книги, които са класифицирани в няколко категории, всички категории се показват в едно поле, разделено със запетаи.
(Решение: Извеждат се три записа с данни.)