Разбираем базы данных и язык 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) VALUES (‘Kharkov’, 1, 1443000), (‘Kyiv’, 1, 3703100), (‘Minsk’, 3, 2545500), (‘Odessa’, 1, 1017699), (‘Voronezh’, 2, 1058261), (‘Kishinev’, 4, 695400); Как видно, можно через одну команду INSERT INTO делать несколько записей одновременно. Удобная вещь, запоминайте) И сразу же посмотрим, что там в таблице: $ SELECT * FROM city;AUTO_INCREMENT — сработал именно так, как мы хотели. ID-шники все заполнены, хоть мы их и не передавали. Внешний ключ — штука зависимая. Чтобы проверить, правильно ли она работает, можно постараться записать внешний ключ, которого во внешней таблице нет. Допустим, мы решили, что id = 5 — это Казахстан. Но реально его в таблице стран нет. И чтобы проверить, что БД будет ругаться, добавим город — Астана: $ INSERT INTO city (name, country_id, population) VALUES (‘Astana’, 5, 1136156); И мы закономерно получаем ошибку:Вот теперь внешний ключ следит за тем, чтобы мы не попытались присвоить городу страну, которой нет в нашей БД. На этом часть по домашнему заданию можно считать завершенной — вперед к новому :)

Оператор SELECT

Ну что, все уже не кажется таким страшным, да? Хочу еще раз отметить, что для Java-разработчиков знание БД — это must have. Без БД никуда. Да, уже хочется начать писать приложение, согласен. Но это нужно. Поэтому так и будем продолжать. При помощи оператора SELECT мы получаем данные из БД. То есть это типичная DML операция (забыли уже, что это?...))) Перечитайте статьи ДО). Чем хороши реляционные базы данных? У них огромная функциональность по агрегации и получению данных. Для этого дела и используется оператор SELECT. Казалось бы, да что там может быть сложного, да? А вот оказывается, что разбираться еще прилично) Нам важно понять основы, от которых уже можно будет отталкиваться. Самый простейший запрос с оператором 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: $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE “%hark%”;И таким способом мы получили еще и Харьков. По итогу можем сказать, что у нас очень даже неплохой поиск получился. Но вот хотелось бы отсортировать не по 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 и так далее). Да, я не рассказал всего что можно, но я и цели такой не ставил перед собой. Да, я знаю что вы уже жаждете писать приложение. Наберитесь терпения, все это нужно. И для проекта, и для вашего профессионального роста. Пока ждете, убедитесь, что Гит уже знаком для вас. Я его буду использовать уже по умолчанию, как известный инструмент. Спасибо всем за прочтение. В следующей статье поговорим о связях в БД и джоинах. Вот там-то и будут крутые задачи))

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