Пользователь Roman Beskrovnyi
Roman Beskrovnyi
35 уровень
Харьков

"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания

Статья из группы Java-проекты
Статья из серии о создании Java-проекта (ссылки на другие материалы — в конце). Ее цель — разбор ключевых технологий, итог — написание телеграм-бота. Предыдущие статьи и разбор домашнего задания по базам данных: 1, 2, 3. Всем, у кого хватило терпения и выдержки, всем, кто идет со мной уже четвертую статью — вы молодцы. Как говорится, дорогу осилит идущий. На этой неделе выйдет заключительная статья о Базах Данных, в которой мы поговорим о типах связей и джоинах (соединениях). Но перед тем, как мы разберемся с новой информацией — проверим домашнее задание… Я прям училкой себя почувствовал. Не серчайте на меня: педагогического образования у меня нет, маемо шо маемо. Поскольку на прошлой неделе подробная проверка ДЗ заняла львиную долю материала, я решил разбить разбор домашки и обзор нового материала на две части."Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 1

Собственно, разбор домашки

Я определенно рад тому, что находятся люди, которые делают ДЗ и говорят об этом. Это здорово! Я максимально уверен, что просто прочитать без закрепления знаний — это путь в никуда. Поэтому все, кто сделал или пытался сделать, — респект. Напомню условия заданий:
  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, поэтому нужно просто пойти на их сайт и выбрать нужную версию и операционную систему. Для этого перейдем по этой ссылке:"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 2Здесь вы можете выбрать именно ту операционную систему, которая вам нужна. Нажимаем Download, но вместо загрузки мы увидим такое окно:"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 3Не теряемся, просто ищем кнопочку с названием No thanks, just start my download, и начнется скачивание. Зачем они это делают? Наверное, чтобы больше регистрировалось у них, нам это не важно. После успешной загрузки запускаем установочный файл. На MacOS это выглядит так:"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 4Просто переносим значок — и все, установка завершена. Уже не так сложно, как установка самого MySQL, правда? Или уже просто привыкли и стали более опытные ;) Вторая часть этой задачи — установить соединение с нашей базой данных. Что для этого нужно? Нажимаем плюсик рядом с MySQL Connections:"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 5В появившемся окне вводим необходимые данные:
  • Connection Name — имя нашего соединения. Пишите максимально понятные имена, чтобы потом не было проблем с идентификацией. Я этому соединению даю имя JRTB_DB;
  • Hostname — уже будет задан как локальный 127.0.0.1 (он же localhost). В нашем случае ничего менять не нужно, так как БД установлена на компьютере, а вот если БД где-то в другом месте, то и хост (ip той машины, на которой запущена БД), соответственно, изменить нужно;
  • Username — также по необходимости можно задавать своего юзера. Если вы не добавляли ничего в этом ключе, оставьте его неизменным;
  • Password — нажимаем Store in Keychain и задаем именно тот пароль, который вы задавали у себя. Я оставил все по-простому — root.
Чтобы проверить, будет ли соединение, нажимаем Test Connection:"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 6Ну и если все сделано было правильно, результат не заставит себя ждать:"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 7Теперь у нас есть сохраненное соединение в БД, и не нужно будет каждый раз создавать соединение, заполнять имя и пароль. И будет выглядеть это счастье вот так:"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 8Заходим в только что созданное соединение и видим окно для запросов. Чтобы быть уверенным, что все правильно, проверим список баз данных, зайдем в нашу и получим все данные о городах:"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 9Причем здесь мы получаем картбланш на то, что нам нужно. Первая секция отвечает за скрипт, который мы вводим. Далее, в Result Grid, мы видим результат последней операции в скрипте. А в Action Output показан список операций и его результат. Очень полезная вещь, хочу я вам сказать: с ее помощью можно следить за скоростью выполнения определенных скриптов. Почему это важно? Одна из самых распространенных проблем в скорости выполнения задач в приложении — это скорость выполнения запросов в БД. Здесь их можно будет быстро и удобно проверить руками.

Пишем необходимые запросы

У нас всего 7 запросов, которые нужно выполнить, поехали!

  1. Получить самую многочисленную страну. Здесь можно пойти хитро и несколькими маршрутами:

  • По данным таблицы country

Тогда необходимо просто отсортировать наш запрос по населению и взять только одну запись. Для этого дела в конце скрипта нужно добавить оператор LIMIT и указать необходимое количество: $ SELECT * FROM country ORDER BY population DESC LIMIT 1;"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 10

  • По данным таблицы city

Здесь все интереснее, потому что запрос будет сложнее, но и интереснее. Так как мы еще не имеем понятия о джоинах, можем получить только ID-шник страны: $ SELECT country_id, SUM(population) FROM city GROUP BY country_id ORDER BY SUM(population) DESC LIMIT 1;"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 11Здесь мы сделали прикольную вещь — собрали сумму населения всех известных городов по каждой стране, отсортировали по этой сумме и взяли первый элемент. Ну как, здорово? Я вот в восторге :D После такого сразу ощущаешь себя гуру запросов… (ненадолго, конечно))

  1. Получить самую малочисленную страну. Здесь можно пойти хитро и несколькими маршрутами

В этом случае все будет ровно так же. Разница лишь в том, что сортировать будет обратно — и все. Поэтому просто пишу запросы:

  • По данным таблицы 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; А результат уже посмотрите сами!

  1. Среднее количество жителей в стране

Вот опять ТЗ стоит как-то не точно, как будто писал менеджер... Почему я так решил? Потому что неясно, в какой таблице работать. Но это нормально: задач, в которых будет сразу все понятно и ясно, просто не бывает. Поэтому нужно внимательно вчитываться в задачи, и если есть вопросы — сразу задавать их! Это так, ремарка. С учетом данных, которые есть у нас в БД, будем искать по данным из городов. Для этого пишем следующий запрос: $ SELECT country_id, AVG(population) FROM city GROUP BY country_id;"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 12Здесь все просто: мы используем функцию AVG и группируем наши записи городов по странам.

  1. Среднее количество жителей в странах, чьи имена заканчиваются на “a”

Здесь будет небольшое изменение по запросу. Нужно добавить фильтрацию по именам, прежде чем мы будем делать группировку. Делаю я домашку, как и все студенты, перед публикацией этой статьи, и понимаю, что без джоинов эту задачу не решить. Почему? Потому что помимо ID-шника страны нам нужно еще получить его имя. А это нельзя сделать без соединения двух таблиц в одну запись. Поэтому я сделаю эту задачу, конечно, но это мой косяк…))) Хотел придумать задачу с использованием LIKE в запросе…) $ SELECT ci.country_id, AVG(ci.population) FROM city ci INNER JOIN country co ON ci.country_id = co.id WHERE co.name LIKE "%a" GROUP BY country_id;"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 13Что здесь произошло? Вначале мы соединили записи с таблицы city и country по внешнему ключу country_id, отфильтровали по именам стран, чтобы они заканчивались на “a”, и уже потом группировали по country_id.

  1. Количество стран, у которых население больше четырех миллионов

Здесь нам нужно просто использовать функцию COUNT и добавить фильтрацию на население: $ SELECT COUNT(*) from country WHERE population > 4000000; В результате узнаем, что таких стран 3. Правильно ли это? Да, только Молдова не проходит этот рубеж.

  1. Отсортировать страны по уменьшению количества жителей

Чтобы сделать это, нужно использовать уже известный нам оператор ORDER BY. Но учтите, что по умолчанию сортировка идёт в натуральном порядке. Для чисел это значит, что сортируется по возрастанию, для строк — что начиная с первых символов. Если нам нужна сортировка по убыванию, нам нужен обратный от натурального: $ SELECT * FROM country ORDER BY population DESC;"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 14

  1. Отсортировать страны по имени в натуральном порядке

Вот здесь нам и пригодятся знания того, что такое натуральный порядок. Так как он по умолчанию, для нас это проще простого: $ SELECT * FROM country ORDER BY name;"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 15"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 16

Вместо вывода

Так уж получилось, что размер решения домашнего задания получится очень большой, поэтому мы сделаем исключение: я публикую эту статью с проверкой, а в пятницу опубликую новый материал со связями и джоинами. Всем спасибо за прочтение. До пятницы!
Другие материалы серии:
Комментарии (23)
Чтобы просмотреть все комментарии или оставить свой, перейдите в полную версию
Andrey 22 уровень, Мурманск
24 октября 2020
+
Василий Бабин 28 уровень, Москва
21 октября 2020
+
Kubik_13 34 уровень, Москва
21 октября 2020
+