Розбираємо бази даних та мову SQL. (Частина 4 - перевірка домашнього завдання) - "Java-проект від А до Я"

Стаття із серії про створення Java-проекту (посилання на інші матеріали – наприкінці). Її мета — аналіз ключових технологій, результат — написання телеграм-бота. Попередні статті та розбір домашнього завдання за базами даних: 1 , 2 , 3 . Всім, у кого вистачило терпіння та витримки, усім, хто йде зі мною вже четверту статтю, ви молодці. Як кажуть, дорогу здолає той, хто йде. Цього тижня вийде заключна стаття про Бази Даних, в якій ми поговоримо про типи зв'язків та джоїни (з'єднання). Але перед тим, як ми розберемося з новою інформацією — перевіримо домашнє завдання… Я прям училкою почував себе. Не серчайте на мене: педагогічної освіти в мене немає, маємо, що маємо. Оскільки минулого тижня докладна перевірка ДЗ зайняла левову частку матеріалу, я вирішив розбити розбір будинку та огляд нового матеріалу на дві частини.

Власне, розбір будинку

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

1. Розібратися з оператором HAVING та написати приклад запиту для таблиць з нашого прикладу. Якщо потрібно додати якісь поля чи ще значень, щоб було наочніше, додавайте. Хто хоче — пишіть у коментарях свій приклад рішення — то я його ще й перевірити зможу, якщо встигну.
2. Встановити MySQL Workbench для роботи з базою даних через UI. Я думаю, що ми вже досить практикувалися з консолі. Підключитись до БД. Якщо ви використовуєте щось інше для роботи з БД — сміливо скипайте це завдання. Тут і далі я використовуватиму тільки MySQL Workbench.
3. Написати запити на отримання за нашими даними:
   1. найменш/багато чисельної країни;
   2. середня кількість жителів країни;
   3. середня кількість жителів у країнах, чиї імена закінчуються на “a”;
   4. кількість країн, у яких населення більше чотирьох мільйонів;
   5. відсортувати країни щодо зменшення кількості мешканців;
   6. відсортувати країни на ім'я в натуральному порядку.

Поговоримо про HAVING

Знання оператора Having може допомогти вам пройти не одну співбесіду, де будуть завдання SQL. Тому зрозуміти його дуже важливо. Так вийшло, що використовувати умови для агрегуючих функцій (SUM, MIN, MAX, AVG) не можна. До того ж HAVING використовують для полів, які групуються. Що це означає? Наприклад, якщо ми хочемо отримати країни, де середня кількість мешканців у містах більше 50 000 мешканців, без використання HAVING нам не обійтися. Як я розумію, зроблено це тому, що агрегація відбувається вже після того, як виконається оператор WHERE і не можна додати до нього значення агрегації, які будуть підраховані пізніше. Навіть якщо поки що мої думки не сильно додають розуміння, можна просто прийняти це як факт і йти з ним. У програмуванні часто виходить так, що якщо раптом щось незрозуміло, це цілком може означати, що мозок просто ще не переварив це. Переспіть із цією думкою, і наступного дня все стане ясніше.

Установка MySQL Workbench

Тут і далі я використовуватиму саме Workbench для запитів. Покажу, що потрібно для встановлення та створення з'єднання з базою даних. Це продукт від Oracle, тому потрібно просто піти на їхній сайт і вибрати потрібну версію та операційну систему. Для цього перейдемо за цим посиланням : Тут ви можете вибрати саме ту операційну систему, яка вам потрібна. Натискаємо Download , але замість завантаження ми побачимо таке вікно: Не губимося, просто шукаємо кнопку з назвою No thanks, just start my download , і почнеться скачування. Для чого вони це роблять? Напевно, щоби більше реєструвалося у них, нам це не важливо. Після успішного завантаження запускаємо інсталяційний файл. На MacOS це виглядає так:Просто переносимо значок - і все, встановлення завершено. Вже не так складно, як установка самого MySQL, правда? Або вже просто звикли і стали досвідченішими;) Друга частина цього завдання — встановити з'єднання з нашою базою даних. Що для цього потрібно? Натискаємо плюсик поряд з MySQL Connections: У вікні, що з'явилося, вводимо необхідні дані:

• Connection Name – ім'я нашого з'єднання. Пишіть максимально зрозумілі імена, щоб потім не було проблем із ідентифікацією. Я цьому з'єднанню даю ім'я JRTB_DB ;
• Hostname вже буде заданий як локальний 127.0.0.1 (він же localhost). У нашому випадку нічого міняти не потрібно, тому що БД встановлена ​​на комп'ютері, а от якщо БД десь в іншому місці, то і хост (ip тієї машини, на якій запущена БД), відповідно, потрібно змінити;
• Username - також за необхідності можна ставити свого користувача. Якщо ви не додавали нічого в цьому ключі, залиште його незмінним;
• Password — натискаємо Store in Keychain і задаємо саме пароль, який ви задавали у себе. Я залишив все по-простому – root .

Щоб перевірити, чи буде з'єднання, натискаємо Test Connection : Ну і якщо все зроблено було правильно, результат не забариться: Тепер у нас є збережене з'єднання в БД, і не потрібно буде щоразу створювати з'єднання, заповнювати ім'я та пароль. І буде виглядати це щастя ось так: Заходимо в створене з'єднання і бачимо вікно для запитів. Щоб бути впевненим, що все правильно, перевіримо список баз даних, зайдемо в нашу та отримаємо всі дані про міста: Причому тут ми отримуємо картбланш на те, що нам потрібно. Перша секція відповідає за скрипт, який ми запроваджуємо. Далі, у Result Grid , ми бачимо результат останньої операції у скрипті. А в Action Outputпоказаний список операцій та його результат. Дуже корисна річ, хочу я вам сказати: за її допомогою можна стежити за швидкістю виконання певних скриптів. Чому це важливо? Одна з найпоширеніших проблем у швидкості виконання завдань у додатку — швидкість виконання запитів у БД. Тут їх можна буде швидко та зручно перевірити руками.

Пишемо необхідні запити

У нас лише 7 запитів, які потрібно виконати, поїхали!

1. Отримати найчисленнішу країну. Тут можна піти хитро і кількома маршрутами:

• За даними таблиці country

Тоді необхідно просто відсортувати наш запит щодо населення та взяти лише один запис. Для цієї справи в кінці скрипту потрібно додати оператор LIMIT та вказати необхідну кількість: $ SELECT * FROM country ORDER BY population DESC LIMIT 1;

• За даними таблиці city

Тут все цікавіше, тому що запит буде складнішим, але й цікавішим. Так як ми ще не маємо поняття про джоїни, можемо отримати тільки ID-шник країни: $ SELECT country_id, SUM(population) Тут ми зробабо прикольну річ — зібрали суму населення всіх відомих міст у кожній країні, відсортували за цією сумою та взяли перший елемент. Ну як, здорово? Я ось у захваті :D Після такого одразу відчуваєш себе гуру запитів… (ненадовго, звичайно)

2. Отримати найменшу країну. Тут можна піти хитро і кількома маршрутами

У цьому випадку все буде так само. Різниця лише в тому, що сортуватиме назад — і все. Тому просто пишу запити:

• За даними таблиці city

$ SELECT country_id, SUM(population) FROM city GROUP BY country_id ORDER BY SUM(population) LIMIT 1;

• За даними таблиці country

$ SELECT * FROM country ORDER BY population LIMIT 1; А результат уже подивіться самі!

3. Середня кількість жителів країни

Ось знову ТЗ стоїть якось не точно, начебто писав менеджер... Чому я так вирішив? Тому що неясно, у якій таблиці працювати. Але це нормально: завдань, в яких буде одразу все зрозуміло та ясно, просто не буває. Тому потрібно уважно вчитуватись у завдання, і якщо є питання — одразу задавати їх! Це так, ремарк. З урахуванням даних, які є у нас у БД, шукатимемо за даними із міст. Для цього пишемо наступний запит: SELECT country_id, AVG(population) FROM city GROUP BY country_id; Тут все просто: ми використовуємо функцію AVG та групуємо наші записи міст країнами.

4. Середня кількість жителів у країнах, чиї імена закінчуються на “a”

Тут буде невелика зміна на запит. Потрібно додати фільтрацію за іменами, перш ніж ми будемо робити угруповання. Роблю я будинки, як і всі студенти, перед публікацією цієї статті, і розумію, що без джоїнів це завдання не вирішити. Чому? Тому що, крім ID-шника країни, нам потрібно ще отримати його ім'я. А це не можна зробити без з'єднання двох таблиць в один запис. Тому я зроблю це завдання, звичайно, але це мій косяк…))) Хотів придумати завдання з використанням LIKE в запиті…) $ SELECT ci . co.id WHERE co.name LIKE "%a" GROUP BY country_id;Що тут сталося? Спочатку ми з'єднали записи з таблиці city і country за зовнішнім ключем country_id, відфільтрували за іменами країн, щоб вони закінчувалися на “a”, і потім групували по country_id.

5. Кількість країн, у яких населення більше чотирьох мільйонів

Тут потрібно просто використовувати функцію COUNT і додати фільтрацію на населення: $ SELECT COUNT(*) from country WHERE population > 4000000; В результаті дізнаємося, що таких країн 3. Чи це правильно? Так, лише Молдова не проходить цей рубіж.

6. Відсортувати країни щодо зменшення кількості мешканців

Щоб зробити це потрібно використовувати вже відомий нам оператор ORDER BY. Але врахуйте, що за умовчанням сортування йде у натуральному порядку. Для чисел це означає, що сортується за зростанням, для рядків — що з перших символів. Якщо нам потрібне сортування за спаданням, нам потрібен зворотний від натурального: $ SELECT * FROM country ORDER BY population DESC;

7. Відсортувати країни за назвою в натуральному порядку

Ось тут нам і знадобляться знання того, що таке натуральний порядок. Так як він за промовчанням, для нас це простіше простого: $ SELECT * FROM country ORDER BY name;

Замість виведення

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

Список всіх матеріалів серії на початку цієї статті.