Sinusuri namin ang mga database at ang wikang SQL. (Part 4 - checking homework) - "Java project from A to Z"

Isang artikulo mula sa isang serye tungkol sa paglikha ng isang proyekto ng Java (nasa dulo ang mga link sa iba pang mga materyales). Ang layunin nito ay pag-aralan ang mga pangunahing teknolohiya, ang resulta ay pagsulat ng isang telegram bot. Mga nakaraang artikulo at pagsusuri ng takdang-aralin sa mga database: 1 , 2 , 3 . Sa lahat ng may pasensya at tibay, sa lahat ng sumama sa akin para sa ikaapat na artikulo - magaling. Sabi nga nila, nakakabisado ng naglalakad ang daan. Sa linggong ito ang huling artikulo tungkol sa Mga Database ay ilalabas, kung saan pag-uusapan natin ang tungkol sa mga uri ng mga relasyon at pagsali . But before we deal with the new information, let’s check our homework... I really felt like a teacher. Huwag kang magalit sa akin: Wala akong pedagogical education, sigurado iyon. Dahil noong nakaraang linggo ang isang detalyadong pagsusuri ng remote control ay kinuha ang malaking bahagi ng materyal, nagpasya akong hatiin ang pagsusuri ng araling-bahay at ang pagsusuri ng bagong materyal sa dalawang bahagi.

Actually, homework analysis

Talagang natutuwa ako na may mga taong nag-PD at pinag-uusapan ito. Ito ay astig! Sigurado ako hangga't maaari na ang simpleng pagbabasa nang walang pagsasama-sama ng kaalaman ay isang daan patungo sa wala. Samakatuwid, lahat ng gumawa o sinubukang gawin - igalang. Hayaan akong ipaalala sa iyo ang mga kondisyon ng mga gawain:

1. Unawain ang HAVING operator at magsulat ng isang halimbawang query para sa mga talahanayan mula sa aming halimbawa. Kung kailangan mong magdagdag ng ilang mga patlang o higit pang mga halaga upang gawin itong mas malinaw, idagdag ang mga ito. Kung nais ng sinuman, isulat ang iyong halimbawang solusyon sa mga komento - para masuri ko rin ito kung may oras ako.
2. I-install ang MySQL Workbench upang gumana sa database sa pamamagitan ng UI. Sa tingin ko mayroon na kaming sapat na pagsasanay sa pagtatrabaho mula sa console. Kumonekta sa database. Kung gumamit ka ng ibang bagay upang magtrabaho kasama ang database, huwag mag-atubiling laktawan ang gawaing ito. Dito at higit pa ay gagamitin ko lamang ang MySQL Workbench.
3. Sumulat ng mga kahilingan para sa resibo gamit ang aming data:
   1. ang pinakamaliit/pinaka-mataong bansa;
   2. average na bilang ng mga naninirahan sa bansa;
   3. average na bilang ng mga naninirahan sa mga bansa na ang mga pangalan ay nagtatapos sa "a";
   4. ang bilang ng mga bansang may populasyon na higit sa apat na milyon;
   5. pag-uri-uriin ang mga bansa sa pamamagitan ng pagbaba ng bilang ng mga naninirahan;
   6. pagbukud-bukurin ang mga bansa ayon sa pangalan sa natural na pagkakasunud-sunod.

Pag-usapan natin ang HAVING

Знание оператора Having может помочь вам пройти не одно собеседование, где будут задачи на SQL. Поэтому понять его крайне важно. Так уж получилось, что использовать условия для агрегирующих функций (SUM, MIN, MAX, AVG) нельзя. К тому же, HAVING используют для полей, которые группируются. What это значит? Например, если мы хотим получить страны, где среднее количество жителей в городах больше 50 000 жителей, без использования HAVING нам не обойтись. Как я понимаю, сделано это потому, что агрегация происходит уже после того, How выполнится оператор WHERE и нельзя добавить в него значения агрегации, которые будут подсчитаны позже. Даже если пока что мои суждения не сильно добавляют понимания, можно просто принять это How факт и идти с ним. В программировании часто получается так, что если в один момент что-то непонятно, это вполне может означать, что мозг просто еще не переварил это. Переспите с этой мыслью, и на следующий день все станет яснее.

Установка MySQL Workbench

Здесь и далее я буду использовать именно Workbench для requestов. Покажу, что нужно для установки и создания соединения с базой данных. Это продукт от Oracle, поэтому нужно просто пойти на их сайт и выбрать нужную версию и операционную систему. Для этого перейдем по этой ссылке:Здесь вы можете выбрать именно ту операционную систему, которая вам нужна. Нажимаем Download, но instead of загрузки мы увидим такое окно:Не теряемся, просто ищем кнопочку с названием No thanks, just start my download, и начнется скачивание. Зачем они это делают? Наверное, чтобы больше регистрировалось у них, нам это не важно. После успешной загрузки запускаем установочный файл. На MacOS это выглядит так:Просто переносим значок — и все, установка завершена. Уже не так сложно, How установка самого MySQL, правда? Или уже просто привыкли и стали более опытные ;) Вторая часть этой задачи — установить соединение с нашей базой данных. What для этого нужно? Нажимаем плюсик рядом с MySQL Connections:В появившемся окне вводим необходимые данные:

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

Whatбы проверить, будет ли соединение, нажимаем Test Connection:Ну и если все сделано было правильно, результат не заставит себя ждать:Теперь у нас есть сохраненное соединение в БД, и не нужно будет каждый раз создавать соединение, заполнять Name и пароль. И будет выглядеть это счастье вот так:Заходим в только что созданное соединение и видим окно для requestов. Whatбы быть уверенным, что все правильно, проверим список баз данных, зайдем в нашу и получим все данные о городах:Причем здесь мы получаем картбланш на то, что нам нужно. Первая секция отвечает за скрипт, который мы вводим. Далее, в Result Grid, мы видим результат последней операции в скрипте. А в Action Output показан список операций и его результат. Очень полезная вещь, хочу я вам сказать: с ее помощью можно следить за speedю выполнения определенных скриптов. Почему это важно? Одна из самых распространенных проблем в скорости выполнения задач в приложении — это speed выполнения requestов в БД. Здесь их можно будет быстро и удобно проверить руками.

Пишем необходимые requestы

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

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

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

Тогда необходимо просто отсортировать наш request по населению и взять только одну запись. Для этого дела в конце скрипта нужно добавить оператор LIMIT и указать необходимое количество: $ SELECT * FROM country ORDER BY population DESC LIMIT 1;

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

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

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

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

• По данным таблицы 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. Среднее количество жителей в стране

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

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

Здесь будет небольшое изменение по requestу. Нужно добавить фильтрацию по именам, прежде чем мы будем делать группировку. Делаю я домашку, How и все студенты, перед публикацией этой статьи, и понимаю, что без джоинов эту задачу не решить. Почему? Потому что помимо ID-шника страны нам нужно еще получить его Name. А это нельзя сделать без соединения двух таблиц в одну запись. Поэтому я сделаю эту задачу, конечно, но это мой косяк…))) Хотел придумать задачу с использованием LIKE в requestе…) $ 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;What здесь произошло? Вначале мы соединor записи с таблицы city и country по внешнему ключу country_id, отфильтровали по именам стран, чтобы они заканчивались на “a”, и уже потом группировали по country_id.

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

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

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

Whatбы сделать это, нужно использовать уже известный нам оператор ORDER BY. Но учтите, что по умолчанию sorting идёт в натуральном порядке. Для чисел это значит, что сортируется по возрастанию, для строк — что начиная с первых символов. Если нам нужна sorting по убыванию, нам нужен обратный от натурального: $ SELECT * FROM country ORDER BY population DESC;

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

Вот здесь нам и пригодятся знания того, что такое натуральный порядок. Так How он по умолчанию, для нас это проще простого: $ SELECT * FROM country ORDER BY name;

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

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

Список всех материалов серии в начале этой статьи.