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

Стаття із серії про створення Java-проекту (посилання на інші матеріали – наприкінці). Її мета — аналіз ключових технологій, результат — написання телеграм-бота.

• Перевірка домашнього завдання
  • Додаємо первинний ключ
  • Додаємо Молдову
  • Створюємо таблицю cities
  • Додаємо зовнішній ключ до cities
  • Бонусна частина. Тестування
• Оператор SELECT
  • Параметр WHERE
  • Параметр ORDER BY
  • Параметр GROUP BY
• Домашнє завдання
• Висновок

Здоров'я бажаю, пані та панове, продовжуємо говорити про БД, SQL та інше. У сьогоднішньому матеріалі буде частина теорії та частина практики. Нагадаю, що минулого разу ми поговорабо про те, як налаштувати все, як створити БД, таблицю і отримати з неї дані. Настав час подивитися, чи вийшло щось із ДЗ. За моїми відчуттями, половину можна було зробити лише ґрунтуючись на минулій статті. Виявилося, щоб нормально зібрати додаток і зробити все більш-менш красиво, потрібно розповісти про бази даних, а щоб про них розповісти, потрібно витратити багато часу.

Перевірка домашнього завдання

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

1. Потрібно додати у схему таблиці country первинний ключ (PRIMARY KEY) із поля ID.
2. Додати до таблиці country ще одну країну – Молдову.
3. За схемою попередньої статті створити таблицю city, де будуть всі поля, що описані. Імена полів будуть наступні: id, name, country_id, population.
4. Додати первинний ключ у таблиці city.
5. Додати зовнішній ключ до таблиці city.

Щоб почати, скористаємося першою частиною минулої статті та зайдемо до терміналу бази даних.

Додаємо первинний ключ

Додати первинний ключ (PRIMARY KEY) можна двома способами: відразу ж після створення таблиці, і навіть після створення, використовуючи ALTER TABLE.

Первинний ключ під час створення таблиці

Так як у нас вже створена таблиця, і без її видалення ми не зможемо показати цей підхід у рамках цієї бази даних, просто створимо тимчасову базу даних test, в якій все зробимо. Введемо такі команди:

• створюємо нову базу даних:

  $ CREATE DATABASE test;

• створюємо таблицю з додаванням первинного ключа:

  $ CREATE TABLE country (id INT, name VARCHAR (30), PRIMARY KEY (id));

Загалом нічого складного. Після оголошення змінних додається наступна частина PRIMARY KEY (id) , де у дужках передається ім'я поля, яке буде первинним ключем. І дивимося, як змінилася схема таблиці: $DESC country; Як бачимо, поле Key для запису id з'явилося значення PRI .

Первинний ключ після створення таблиці

Як я вже говорив раніше, перший ключ після створення таблиці можна присвоїти за допомогою ALTER TABLE . Цей приклад ми і виконаємо в нашій базі даних cities :

• перейдемо в нашу БД із тестової:

  $ USE cities;

• перевіримо, що ми точно в нашій БД (там має бути ще одне поле – population). Для цього напишемо:

  $ DESC population;

все правильно, таблиця наша. Напишемо таке:

$ ALTER TABLE country ADD PRIMARY KEY (id);

• і перевіримо відразу ж командою:

  $DESC country;

Як видно з картинки, все правильно, значення PRI знаходиться саме там, де має бути. До речі, ми позаймалися з тестової БД. Тепер її треба видалити: навіщо нам захаращувати сервер, вірно? Для цього використовуємо вже досить відому команду: $ DROP DATABASE test;

Додаємо Молдову

Для початку потрібно визначитися, що ми записуватимемо. Наступний ID у нас буде 4. Ім'я буде Moldova, а її населення становить 3550900. Тому виконуємо вже відому нам команду INSERT INTO: $INSERT INTO country VALUES (4, 'Moldova', 3550900); І перевіряємо, чи це значення є в БД: $ SELECT * FROM country WHERE id = 4; У запиті на отримання даних я відразу визначив, яким полем буде виконуватися пошук, тому ми отримали тільки один запис, який нам і був потрібний.

Створюємо таблицю cities

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

• id – унікальний ідентифікатор;
• name - ім'я міста;
• country_id – зовнішній ключ країни;
• population – населення міста.

Щоразу писати унікальний ID якось напружено, ви не знаходите? Хочеться віддати це на відкуп владі MySQL. І такий спосіб є - AUTO INCREMENT . Потрібно це додати в цифрове поле, і якщо ми не передаватимемо явно значення, MySQL сам збільшить ID на одиницю в порівнянні з попереднім. Тому створення таблиці виглядатиме так: $ CREATE TABLE city (id INT AUTO_INCREMENT, name VARCHAR(30), country_id INT, population INT, PRIMARY KEY (id)); Подивимося на схему таблиці, чи правильно зробабо: $ DESC city;Як видно зі схеми таблиці, у нас з'явився новий опис поля id — auto_increment. Виходить, ми все правильно зробабо. Перевіримо дані вже повністю налаштованої таблиці. Для цього зробимо останню частину завдання зовнішній ключ.

Додаємо зовнішній ключ до cities

Для зовнішнього ключа буде така команда: $ ALTER TABLE city ADD FOREIGN KEY (country_id) REFERENCES country(id); І відразу перевіримо, що там зі схемою таблиці: чи не змінилася вона годиною? $ DESC city;

Бонусна частина. Тестування

Забув я додати завдання — заповнити даними, які були в скрині першої частини. Забув, отже, зараз сам зроблю. А тим, кому цікаво, можете самі зробити це без мене і потім звіримо ;) Там були Харків, Київ, Мінськ, Одеса, Воронеж і ще додамо Кишинів. Але цього разу ID-шники не будемо передавати, пропустимо їх: $ INSERT INTO city (name, country_id, population) 3, 2545500), ('Odessa', 1, 1017699), ('Voronezh', 2, 1058261), ('Kishinev', 4, 695400); Як бачимо, можна через одну команду INSERT INTO робити кілька записів одночасно. Зручна річ, запам'ятовуйте) І відразу подивимося, що там у таблиці: $ SELECT * FROM city; AUTO_INCREMENT- Спрацював саме так, як ми хотіли. ID-шники всі заповнені, хоч ми їх і не передавали. Зовнішній ключ – штука залежна. Щоб перевірити, чи правильно вона працює, можна спробувати записати зовнішній ключ, якого у зовнішній таблиці немає. Припустимо, ми вирішабо, що id = 5 – це Казахстан. Але реально їх у таблиці країн немає. І щоб перевірити, що БД лаятиметься, додамо місто — Астана: VALUES ('Astana', 5, 1136156); І ми закономірно отримуємо помилку: Ось тепер зовнішній ключ слідкує за тим, щоб ми не спробували привласнити місту країну, якої немає в нашій БД. На цьому частину за домашнім завданням можна вважати завершеною - вперед до нового:)

Оператор SELECT

Ну що, все вже не здається таким страшним, га? Хочу ще раз відзначити, що для Java-розробників знання БД це must have. Без БД нікуди. Так, вже хочеться почати писати додаток, згоден. Але це потрібне. Тому так і продовжуватимемо. За допомогою оператора SELECT ми отримуємо дані із БД. Тобто це типова DML операція (забули вже, що це?...))) Перечитайте статті ДО). Чим хороші реляційні бази даних? У них величезна функціональність з агрегації та отримання даних. Для цієї справи використовується оператор SELECT. Здавалося б, що там може бути складного, так? А ось виявляється, що розумітися ще пристойно) Нам важливо зрозуміти основи, від яких вже можна буде відштовхуватися. Найпростіший запит із оператором SELECT - вибрати всі дані з однієї таблиці. Мені дуже сподобався опис з вікі про те,

SELECT
  [DISTINCT | DISTINCTROW | ALL]
  select_expression,...
FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula}]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]

Тут видно, що не можна спочатку поставити оператор GROUP BY, а потім вже WHERE. Це потрібно запам'ятати, щоб потім не було образи за помилки, які незрозуміло, звідки беруться. $SELECT * FROM city; Але вигребти всі дані для нас не прикольно. Це так само, якби ми хотіли цвяхи забивати мікроскопом [1] , [2] . Так як БД робить набагато швидше операції фільтрації, сортування та агрегації, ніж Java-код, краще це справа і залишити БД на відкуп. Тому шляхом ускладнення завдань відкриватимемо новий функціонал.

Параметр WHERE

Щоб відфільтрувати вибірку, використовується слово WHERE . Тлумачити це потрібно так: SELECT * FROM tablename (вибрати всі поля з таблиці tablename) WHERE talbe_row = 1 (де в записах поле table_row дорівнює 1). Важливо, що порядок ключових слів у запиті є важливим. Не можна написати WHERE a =1 FROM table_name SELECT *. Для російської мови це ок, і деяким може здатися не таким зашкваром, але для SQL це неприпустимо. Пишемо наступний запит: $ SELECT * FROM city WHERE country_id = 1; І ми обрали українські міста. Непогано, правда? А якщо ми хочемо не лише українців, а й білоруських? Для цієї справи ми можемо перерахувати колекцію значень, які може набувати поле: $ SELECT * FROM city WHERE country_id IN (1, 3);І вже у відповіді у нас міста із двох країн. А якщо є кілька умов для фільтрації? Допустимо, ми хочемо міста з населенням понад два мільйони? Для цього використовують слова OR та AND : $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000; Відмінно, а якщо нам потрібно додати ще одну умову — з пошуку імен через регулярне вираження (описувати регулярки тут я не буду: ось людина “коротко” в 4 частинах зробив це)? Наприклад, ми пам'ятаємо, як пишеться місто, але не повністю ... Для цього у виразі фільтрації можна додати ключове слово LIKE :І в такий спосіб ми отримали ще й Харків. За підсумками можемо сказати, що в нас дуже непоганий пошук вийшов. Але хотілося б відсортувати не по ID-шнику, а по населенню, але як? Так дуже просто.

Параметр ORDER BY

За допомогою ORDER BY ми можемо відсортувати записи, які отримали за певним полем. Сортує як числа, так і рядки. Розширимо попередній запит, відсортуємо за населенням, додавши ORDER BY population: $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE “%hark%” ORDER BY population; Як бачимо, сортування відбулося натуральному порядку, тобто за зростанням. А якщо ми хочемо навпаки? Для цього потрібно додати слово DESC: $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE “%hark%” ORDER BY population DESC; Наразі сортування йде щодо зменшення населення. І робить це БД дуже швидко: ніякі там Collections.sortне йдуть у порівняння. Тепер відсортуємо за рядками, на ім'я у зворотному порядку: $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE “%hark%” ORDER BY name DESC;

Параметр GROUP BY

Використовується для групування записів на певних полях. Зазвичай це потрібно, щоб застосовувати агрегатні функції… А що таке агрегатні функції?)) Є сенс групувати якимись полями, якщо вони однакові для різних записів. Розберемо, що мають на увазі, на прикладі. Скажімо, у містах є зовнішні ключі – ID-шники країн. Так ось, ID однаковий для міст із однієї країни. Тому можна взяти та згрупувати записи за ними: $SELECT country_id, COUNT(*) FROM city GROUP BY country_id; Але без агрегуючих функцій це виглядає якось блякло, погодьтеся. Тому розглянемо кілька найпоширеніших функцій:

• COUNT — кількість записів, які можна без групування, використовують як COUNT(*) . У разі ж угруповання з якогось поля — COUNT(groupped_field);
• MAX - знаходить максимальне значення за певним полем;
• MIN - знаходить мінімальне значення за певним полем;
• SUM - знаходить суму за певним полем;
• AVG – знаходить середнє значення.

Взагалі, ці функції можна використовувати і без угруповання, тільки тоді буде виведено лише одне поле. Спробуємо їх для населення міст: $ SELECT COUNT(*) FROM city; Що попросабо, те й одержали. Просто кількість записів. Іноді це корисно. Наприклад, якщо нам потрібно дізнатися про кількість статей якогось автора. Не треба вигрібати їх із БД і вже рахувати. Можна просто використовувати COUNT(). $ SELECT AVG(population) FROM city; $ SELECT MIN (population) FROM city; І тут уже набирає чинності угруповання. Наприклад, стоїть завдання отримати найменше місто в країні. Чи знаєте, як це зробити? Спробуйте самі, потім дивіться: $ SELECT country_id as Країна, MIN(population) FROM city WHERE GROUP BY country_id;Поки що ми бачимо лише ID-шник країни, але це не біда — наступного разу все зробимо. А так вже є результат, і ми отримали, що хотіли — найменше місто країни з ID = 1. З іншими функціями буде те саме. Важливо відзначити, що вигрібати всі поля через * при використанні угруповання та агрегації не вдасться! Подумайте над цим;)

Домашнє завдання

За підсумками попередніх статей видно, що домашнє завдання заходить, тому продовжимо)) Так, всі хто зробить ДЗ - продовжуємо ставити "+" у коментарях. Це важливо для мене, що тема домашнього завдання цікава вам, щоб я продовжував її робити далі. Так, я читаю ваші коментарі регулярно. Відповідаю, звісно, ​​рідше. Я бачив, що просабо давати складніше завдання SQL. Поки ми не вивчимо джоїни, цікавих завдань не буде, тому будуть ті, які мені потрібні для подальшого матеріалу.

Завдання:

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

Висновок

Сьогодні ми детально розібрали домашнє завдання минулого уроку. Причому, я вважаю це важливим і для тих, хто не зробив його, і для тих, хто зробив. Для перших це можливість дізнатися про відповідь, а для других — звірити зі своїм результатом. Підписуйтесь на мій аккаунт гітхаб , щоб бути в курсі змін по проекту. Я там вестиму всю кодову базу. Все проходитиме в цій організації . Далі ми обговорабо оператор SELECT. Він найважливіший для нас. Саме по ньому проходитимуть усі запити на отримання даних, і ми маємо його розуміти. Найголовніше – пам'ятати порядок додавання параметрів (WHERE, ORDER BY, GROUP BY тощо). Так, я не розповів всього, що можна, але я і мети такої не ставив перед собою. Так, я знаю, що ви вже хочете писати додаток. Наберіться терпіння, це все потрібно. І для проекту, і для вашого професійного зростання. Поки чекаєте, переконайтеся, що Гіт вже знайомий вам. Я його використовуватиму вже за умовчанням, як відомий інструмент. Дякую всім за прочитання. У наступній статті поговоримо про зв'язки у БД та джоїнах. Ось там і будуть круті завдання))

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