ہم ڈیٹا بیس اور SQL زبان کا تجزیہ کرتے ہیں۔ (حصہ 3) - "A سے Z تک جاوا پروجیکٹ"

جاوا پروجیکٹ بنانے کے سلسلے میں ایک مضمون (دیگر مواد کے لنکس آخر میں ہیں)۔ اس کا مقصد کلیدی ٹیکنالوجیز کا تجزیہ کرنا ہے، نتیجہ ٹیلی گرام بوٹ لکھنا ہے۔

• ہوم ورک چیک کر رہا ہے۔
  • ایک بنیادی کلید شامل کرنا
  • مالڈووا کو شامل کرنا
  • شہروں کی میز بنائیں
  • شہروں میں غیر ملکی کلید شامل کریں۔
  • بونس کا حصہ۔ ٹیسٹنگ
• بیان کا انتخاب کریں۔
  • WHERE پیرامیٹر
  • پیرامیٹر کے لحاظ سے آرڈر کریں۔
  • GROUP BY پیرامیٹر
• گھر کا کام
• نتیجہ

ہیلو، خواتین و حضرات، آئیے ڈیٹا بیس، ایس کیو ایل اور دیگر چیزوں کے بارے میں بات کرتے رہیں۔ آج کا مواد جزوی نظریہ اور جزوی مشق پر مشتمل ہوگا۔ میں آپ کو یاد دلاتا ہوں کہ پچھلی بار ہم نے ہر چیز کو ترتیب دینے، ڈیٹا بیس، ایک ٹیبل بنانے اور اس سے ڈیٹا حاصل کرنے کے طریقے کے بارے میں بات کی تھی۔ یہ دیکھنے کا وقت ہے کہ آیا ریموٹ سینسنگ کے ساتھ کچھ کام ہوا ہے۔ میری رائے میں اس کا نصف صرف پچھلے مضمون کی بنیاد پر کیا جا سکتا تھا۔ یہ پتہ چلا کہ ایک ایپلی کیشن کو صحیح طریقے سے جمع کرنے اور ہر چیز کو کم و بیش خوبصورت بنانے کے لیے، آپ کو ڈیٹا بیس کے بارے میں بات کرنے کی ضرورت ہے، اور ان کے بارے میں بات کرنے کے لیے آپ کو کافی وقت گزارنا ہوگا۔

ہوم ورک چیک کر رہا ہے۔

کاموں کو کامیابی سے مکمل کرنے والے تمام لوگوں کے لیے بہت زیادہ احترام۔ اس کا مطلب ہے کہ آپ سمجھتے ہیں کہ صرف آپ کو اس کی ضرورت ہے اور یہ صرف آپ کی مدد کرتا ہے۔ جن لوگوں نے میرے کام کو نظرانداز کیا ہے، میں آپ کو یہ شرط یاد دلاتا ہوں:

1. آپ کو ID فیلڈ سے کنٹری ٹیبل اسکیما میں ایک بنیادی کلید (PRIMARY KEY) شامل کرنے کی ضرورت ہے۔
2. ملک کی میز پر ایک اور ملک شامل کریں - مالڈووا۔
3. پچھلے مضمون کی اسکیم کے مطابق، ایک ٹیبل سٹی بنائیں، جس میں وہ تمام فیلڈز ہوں گے جو بیان کیے گئے ہیں۔ فیلڈ کے نام درج ذیل ہوں گے: id، name، country_id، آبادی۔
4. سٹی ٹیبل میں ایک بنیادی کلید شامل کریں۔
5. سٹی ٹیبل میں ایک غیر ملکی کلید شامل کریں۔

شروع کرنے کے لیے، آئیے پچھلے مضمون کا پہلا حصہ استعمال کریں اور ڈیٹا بیس ٹرمینل پر جائیں۔

ایک بنیادی کلید شامل کرنا

آپ ایک بنیادی کلید (PRIMARY KEY) دو طریقوں سے شامل کر سکتے ہیں: ٹیبل بناتے وقت فوراً، یا تخلیق کے بعد، ALTER TABLE کا استعمال کرتے ہوئے۔

ٹیبل بنانے کے دوران بنیادی کلید

چونکہ ہم پہلے ہی ایک ٹیبل بنا چکے ہیں، اور اسے حذف کیے بغیر ہم اس ڈیٹا بیس کے اندر اس نقطہ نظر کو نہیں دکھا سکیں گے، اس لیے ہم صرف ایک عارضی ٹیسٹ ڈیٹا بیس بنائیں گے جس میں ہم سب کچھ کریں گے۔ آئیے درج ذیل کمانڈز درج کریں:

• ایک نیا ڈیٹا بیس بنائیں:

  $CREATE ڈیٹا بیس ٹیسٹ؛

• ایک بنیادی کلید شامل کرتے ہوئے ایک ٹیبل بنائیں:

  $ ٹیبل ملک بنائیں (ID INT، نام VARCHAR(30)، بنیادی کلید (id)؛

عام طور پر، کچھ بھی پیچیدہ نہیں ہے. متغیرات کا اعلان کرنے کے بعد، مندرجہ ذیل حصہ PRIMARY KEY (id) شامل کیا جاتا ہے ، جہاں اس فیلڈ کا نام جو بنیادی کلید ہو گا قوسین میں پاس کیا جاتا ہے۔ اور آئیے دیکھتے ہیں کہ ٹیبل اسکیما کیسے بدلا ہے: $DESC ملک؛ جیسا کہ آپ دیکھ سکتے ہیں، آئی ڈی کے اندراج کے لیے کلیدی فیلڈ میں قدر PRI ظاہر ہوئی ہے ۔

ٹیبل بنانے کے بعد بنیادی کلید

جیسا کہ میں نے پہلے کہا، ٹیبل بنانے کے بعد پہلی کلید ALTER TABLE کا استعمال کرتے ہوئے تفویض کی جا سکتی ہے ۔ ہم اس مثال کو اپنے شہروں کے ڈیٹا بیس میں چلائیں گے :

• آئیے ٹیسٹ ون سے اپنے ڈیٹا بیس پر جائیں:

  $USE شہر؛

• проверим, что мы точно в нашей БД (там должно быть еще одно поле — population). Для этого напишем:

  $ DESC population;

все правильно, table наша. Напишем следующее:

$ ALTER TABLE country ADD PRIMARY KEY (id);

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

  $ DESC country;

Как видно из картинки, все правильно, meaning PRI находится именно там, где и должно быть. Кстати, ведь мы позанимались с тестовой БД. Теперь же ее нужно удалить: зачем нам захламлять server, верно? Для этого используем уже довольно-таки известную команду: $ DROP DATABASE test;

Добавляем Молдову

Для начала нужно определиться, что мы будем записывать. Следующий ID у нас будет 4. Name будет Moldova, а ее население составляет 3550900. Поэтому выполняем уже известную нам команду INSERT INTO: $ INSERT INTO country VALUES (4, ‘Moldova’, 3550900); И проверяем, точно ли это meaning есть в БД: $ SELECT * FROM country WHERE id = 4;В requestе на получение данных я сразу определил, по Howому полю будет выполняться поиск, поэтому мы получor только одну запись, которая нам и была нужна.

Создаем таблицу cities

По схеме из первой статьи о БД, получим нужную информацию о таблице. В ней будут следующие поля:

• id — уникальный идентификатор;
• name — Name города;
• country_id — внешний ключ страны;
• population — население города.

Каждый раз писать уникальный ID How-то напряжно, вы не находите? Хочется отдать это на откуп властям 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. Значит, мы все правильно сделали. Проверим данные уже на fully настроенной таблице. Для этого сделаем последнюю часть задания — внешний ключ.

Добавляем внешний ключ в 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 — сработал именно так, How мы хотели. ID-шники все заполнены, хоть мы их и не передавали. Внешний ключ — штука зависимая. Whatбы проверить, правильно ли она работает, можно постараться записать внешний ключ, которого во внешней таблице нет. Допустим, мы решor, что id = 5 — это Казахстан. Но реально его в таблице стран нет. И чтобы проверить, что БД будет ругаться, добавим город — Астана: $ INSERT INTO city (name, country_id, population) VALUES (‘Astana’, 5, 1136156); И мы закономерно получаем ошибку:Вот теперь внешний ключ следит за тем, чтобы мы не попытались присвоить городу страну, которой нет в нашей БД. На этом часть по домашнему заданию можно считать завершенной — вперед к новому :)

Оператор SELECT

Ну что, все уже не кажется таким страшным, да? Want еще раз отметить, что для Java-разработчиков знание БД — это must have. Без БД никуда. Да, уже хочется начать писать приложение, согласен. Но это нужно. Поэтому так и будем продолжать. При помощи оператора SELECT мы получаем данные из БД. То есть это типичная DML операция (забыли уже, что это?...))) Перечитайте статьи ДО). Чем хороши реляционные базы данных? У них огромная функциональность по агрегации и получению данных. Для этого дела и используется оператор SELECT. Казалось бы, да что там может быть сложного, да? А вот оказывается, что разбираться еще прorчно) Нам важно понять основы, от которых уже можно будет отталкиваться. Самый простейший request с оператором SELECT — выбрать все данные из одной таблицы. Мне очень понравилось описание из вики о том, в Howом именно порядке должны идти операторы в SELECT requestе, поэтому нагло скопирую его сюда:

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]. Так How БД делает намного быстрее операции фильтрации, сортировки и агрегации, чем Java-code, то лучше это дело и оставить БД на откуп. Поэтому путем усложнения задач будем открывать новый функционал.

Параметр WHERE

Whatбы отфильтровать выборку, используется слово WHERE. Толковать это нужно следующим образом: SELECT * FROM tablename (выбрать все поля из таблицы tablename) WHERE talbe_row = 1 (где в записях поле table_row равно 1). Важно отметить, что порядок ключевых слов в requestе важен. Нельзя написать WHERE a =1 FROM table_name SELECT *. Для русского языка это ок, и некоторым может показаться не таким уж зашкваром, но для SQL это недопустимо. Пишем следующий request: $ 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;Отлично, а что если нам нужно добавить еще одно condition — по поиску имен через регулярное выражение (описывать регулярки здесь я не буду: вот человек “коротко” в 4 частях сделал это)? Например, мы помним, How пишется город, но не fully… Для этого в выражении фильтрации можно добавить ключевое слово LIKE: $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE “%hark%”;И таким способом мы получor еще и Харьков. По итогу можем сказать, что у нас очень даже неплохой поиск получился. Но вот хотелось бы отсортировать не по ID-шнику, а по населению, но How? Да очень просто…

Параметр ORDER BY

При помощи ORDER BY мы можем отсортировать записи, которые получor по определенному полю. Сортирует он How числа, так и строки. Расширим предыдущий request, отсортируем по населению, добавив ORDER BY population: $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE “%hark%” ORDER BY population;Как мы видим, sorting произошла в натуральном порядке, то есть по возрастанию. А если мы хотим наоборот? Для этого нужно добавить слово DESC: $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE “%hark%” ORDER BY population DESC;Теперь sorting идет по уменьшению населения. И делает это БД очень быстро: ниHowие там Collections.sort не идут в сравнение. Теперь отсортируем по строкам, по имени в обратном порядке: $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE “%hark%” ORDER BY name DESC;

Параметр GROUP BY

Используется для группировки записей по определенным полям. Обычно это нужно, чтобы применять агрегатные функции… А что такое агрегатные функции?)) Есть смысл группировать по Howим-то полям, если они одинаковые для разных записей. Разберем, что имеется в виду, на нашем примере. Скажем, в городах есть внешние ключи — ID-шники стран. Так вот, ID одинаков для городов из одной страны. Поэтому можно взять и сгруппировать записи по ним: $ SELECT country_id, COUNT(*) FROM city GROUP BY country_id;Но без агрегирующих функций это выглядит How-то блекло, согласитесь. Поэтому рассмотрим несколько самых распространенных функций:

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

Вообще эти функции можно использовать и без группировки, только тогда будет выведено только одно поле. Попробуем их для нашего населения городов: $ SELECT COUNT(*) FROM city;What попросor, то и получor. Просто количество записей. Иногда это бывает полезно. Например, если нам нужно узнать количество статей Howого-то автора. Не нужно выгребать их из БД и уже считать. Можно просто использовать COUNT(). $ SELECT AVG(population) FROM city;$ SELECT MIN(population) FROM city; И здесь уже вступает в силу группировка. Например, стоит задача получить самый малочисленный город в стране. Уже знаете, How это сделать? Попробуйте сами, потом смотрите: $ SELECT country_id as Страна, MIN(population) FROM city WHERE GROUP BY country_id;Пока что мы видим только ID-шник страны, но это не беда — в следующий раз все сделаем. А так уже есть результат, и мы получor, что хотели — самый малочисленный город страны с ID = 1. С остальными функциями будет то же самое. Важно отметить, что выгребать все поля через * при использовании группировки и агрегации не получится! Подумайте над этим ;)

Домашнее задание

По итогу предыдущих статей видно, что домашнее задание заходит, поэтому продолжим)) Да, все кто сделает ДЗ — продолжаем ставить “+” в комментариях. Это важно для меня, что тема домашнего задания интересна вам, чтобы я продолжал ее делать и далее. Да, я читаю вашу комментарии регулярно. Отвечаю, конечно, реже. Я видел, что просor давать сложнее задачи по SQL. Пока мы не выучим джоины, интересных задач не будет, поэтому будут те, которые нужны мне для дальнейшего материала.

Задачи:

Разобраться с оператором HAVING и написать пример requestа для таблиц из нашего примера. Если нужно добавить Howие-то поля or ещё значений, чтобы было нагляднее — добавляйте. Кто хочет — пишите в комментариях свой пример решения: так я его еще и проверить смогу, если успею.
1. Установить MySQL Workbench для работы с БД через UI. Я думаю, мы уже достаточно практиковались с работой из консоли. Подключиться к БД. Если используете что-то другое для работы с БД, смело скипайте это задание. Здесь и дальше я буду использовать только MySQL Workbench.
2. Написать requestы на получение по нашим данным:
   1. самой мало- / многочисленной страны;
   2. среднее количество жителей в стране;
   3. среднее количество жителей в странах, чьи имена заканчиваются на “a”;
   4. количество стран, у которых население больше четырех миллионов;
   5. отсортировать страны по уменьшению количества жителей;
   6. отсортировать страны по имени в натуральном порядке.

Вывод

Сегодня мы подробно разобрали домашнее задание из прошлого урока. Причем я считаю это важным и для тех, кто не сделал его, и для тех, кто сделал. Для первых это возможность узнать ответ, а для вторых — сверить со своим результатом. Подписывайтесь на мой гитхаб аккаунт, чтобы быть в курсе изменений по проекту. Я буду там вести всю codeовую базу. Все будет проходить в этой организации. Далее мы обсудor оператор SELECT. Он самый важный для нас. Именно по нему будут проходить все requestы на получение данных, и мы должны его понимать. Самое главное — помнить порядок добавления параметров (WHERE, ORDER BY, GROUP BY и так далее). Да, я не рассказал всего что можно, но я и цели такой не ставил перед собой. Да, я знаю что вы уже жаждете писать приложение. Наберитесь терпения, все это нужно. И для проекта, и для вашего профессионального роста. Пока ждете, убедитесь, что Гит уже знаком для вас. Я его буду использовать уже по умолчанию, How известный инструмент. Спасибо всем за прочтение. В следующей статье поговорим о связях в БД и джоинах. Вот там-то и будут крутые задачи))

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