Статья из серии о создании Java-проекта (ссылки на другие материалы — в конце). Ее цель — разбор ключевых технологий, итог — написание телеграм-бота.Здравия желаю, дамы и господа, продолжаем говорить о БД, SQL и прочем.
В сегодняшнем материале будет часть теории и часть практики. Напомню, что в прошлый раз мы поговорили о том, как все настроить, как создать БД, таблицу и получить из нее данные.
Пришло время посмотреть, получилось ли что с ДЗ. По моим ощущениям, половину можно было сделать только основываясь на прошлой статье.
Оказалось, чтобы нормально собрать приложение и сделать все более-менее красиво, нужно рассказать о базах данных, а чтобы о них рассказать, нужно потратить много времени.властям 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. Значит, мы все правильно сделали.
Проверим данные уже на полностью настроенной таблице. Для этого сделаем последнюю часть задания — внешний ключ.
Проверка домашнего задания
Всем, кто успешно сделал задачи — огромный респект. Это значит, вы понимаете, что это нужно только вам и помогает это только вам. Тем, кто пренебрег моей задачей, напомню условие:- Нужно добавить в схему таблицы country первичный ключ (PRIMARY KEY) из поля ID.
- Добавить в таблицу country еще одну страну — Молдову.
- По схеме предыдущей статьи создать таблицу city, в которой будут все поля, что описаны. Имена полей будут следующие: id, name, country_id, population.
- Добавить первичный ключ в таблице city.
- Добавить внешний ключ в таблице city.
Добавляем первичный ключ
Добавить первичный ключ (PRIMARY KEY) можно двумя способами: сразу же при создании таблицы, а также после создания, используя ALTER TABLE.Первичный ключ во время создания таблицы
Так как у нас уже создана таблица, и без ее удаления мы не сможем показать данный подход в рамках этой базы данных, просто создадим временную базу данных test, в которой все сделаем. Введем следующие команды:создаем новую базу данных:
$ CREATE DATABASE test;
создаем таблицу с добавлением первичного ключа:
$ CREATE TABLE country(id INT, name VARCHAR(30), PRIMARY KEY (id));
Первичный ключ после создания таблицы
Как я уже говорил ранее, первый ключ после создания таблицы можно присвоить при помощи ALTER TABLE. Этот пример мы и выполним в нашей базе данных cities:перейдем в нашу БД из тестовой:
$ USE cities;
проверим, что мы точно в нашей БД (там должно быть еще одно поле — population). Для этого напишем:
$ DESC population;
и проверим сразу же командой:
$ DESC country;
все правильно, таблица наша. Напишем следующее:
$ ALTER TABLE country ADD PRIMARY KEY (id);
Добавляем Молдову
Для начала нужно определиться, что мы будем записывать. Следующий 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 — население города.
Добавляем внешний ключ в 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 — находит среднее значение.
Домашнее задание
По итогу предыдущих статей видно, что домашнее задание заходит, поэтому продолжим)) Да, все кто сделает ДЗ — продолжаем ставить “+” в комментариях. Это важно для меня, что тема домашнего задания интересна вам, чтобы я продолжал ее делать и далее. Да, я читаю вашу комментарии регулярно. Отвечаю, конечно, реже. Я видел, что просили давать сложнее задачи по SQL. Пока мы не выучим джоины, интересных задач не будет, поэтому будут те, которые нужны мне для дальнейшего материала.Задачи:
- Разобраться с оператором HAVING и написать пример запроса для таблиц из нашего примера. Если нужно добавить какие-то поля или ещё значений, чтобы было нагляднее — добавляйте. Кто хочет — пишите в комментариях свой пример решения: так я его еще и проверить смогу, если успею.
- Установить MySQL Workbench для работы с БД через UI. Я думаю, мы уже достаточно практиковались с работой из консоли. Подключиться к БД. Если используете что-то другое для работы с БД, смело скипайте это задание. Здесь и дальше я буду использовать только MySQL Workbench.
- Написать запросы на получение по нашим данным:
- самой мало- / многочисленной страны;
- среднее количество жителей в стране;
- среднее количество жителей в странах, чьи имена заканчиваются на “a”;
- количество стран, у которых население больше четырех миллионов;
- отсортировать страны по уменьшению количества жителей;
- отсортировать страны по имени в натуральном порядке.
ПЕРЕЙДИТЕ В ПОЛНУЮ ВЕРСИЮ