Помнишь, как мы с тобой недавно обсуждали, что такое СУБД и язык SQL? А хочешь за 15 минут узнать, как устроены самые простые SQL-запросы? Тогда сразу поделюсь небольшим секретом - язык SQL устроен очень просто! При этом, его знание открывает тебе большие красивые двери в мир практической работы с базами данных. В отличие от программирования, где существуют сотни различных языков и тысячи частных особенностей, язык SQL имеет единый, плюс-минус универсальный стандарт. В общем и целом, изучение простых SQL-запросов вполне укладывается в пару недель, включая практику. Что-ж, перейдем от слов к деру - поехали!
Сегодня у нас на обзоре простые примеры SQL-запросов к классической реляционной СУБД (почему некоторые системы называют реляционными - объясняю вот тут). Эта статья так же продолжает общий цикл публикаций про СУБД и SQL, добавляя больше подробностей и некоторых деталей.
В двух словах напомню уже пройденный ранее материал. СУБД - это система управления базами данных, а язык SQL - это способ взаимодействия с данными. Практически любое приложение в современном мире работает с данными. И если язык программирования нужен чтобы создать конкретное прикладное приложение (условно - интерфейс), то СУБД нужна чтобы приложение могло хранить данные. Существует множество различных СУБД, но все они используют плюс-минус одинаковый стандарт SQL. Таким образом, выучив этот универсальный язык, ты сможешь легко работать хоть с MySQL, хоть с Oracle, хоть с другими системами.
Важность и повсеместную применимость языка SQL сложно переоценить. Из моего личного опыта, он так или иначе используется во всех коммерческих проектах с которыми я работал. Опять же, весь современный мир основан на данных - и для работы с ними практически всегда используется язык запросов SQL.
К слову сказать, технологии SQL в 2023 году будет плюс-минус 49 лет с момента изобретения. Такой вот прекрасный язык-долгожитель, постепенно эволюционирующий, и развивающийся вместе с современным миром. И пусть тебя не вводят в заблуждение весёлые видео из TikTok или фотографии котиков! В конечном итоге, любая современная платформа где-то хранит свои данные. И в большинстве случаев, они хранятся в классических реляционных СУБД - для работы с которыми используется почти 50-летний дедушка SQL.
Что такое язык SQL? 🈯️
SQL - это сокращение от Structured Query Language - то есть "Язык структурированных запросов". Основа языка SQL - "запрос". Что это такое, и зачем оно нужно? Давай ненадолго вернёмся к прошлым статьям. Программирование, с точки зрения конечного продукта, всегда держится на двух столбах. Первый столб - это условное приложение, а второй - условная база данных. Приложение - это просто интерфейс, кнопки, картинки, поля для ввода текста, и всё вот это. База данных же совсем наоборот - это куча непонятных файлов без интерфейса, которые хранятся в какой-то папке. А вот СУБД - это специальная "прослойка" между базой данных (файлами) и приложением (интерфейсом).
База данных, по сути своей, является пассивным инструментом. Её поведение целиком и полностью зависит от внешнего мира. А именно - от поведения внешнего пользователя, и от особенностей работы конкретной СУБД. И если мы рассматриваем взаимодействие с базой из этого условного внешнего мира, то любое действие в ней происходит с помощью "запроса". То есть, с помощью конкретной SQL команды, которую кто-то отправил внутрь СУБД.
В простом виде, это выглядит следующим образом: пользователь нажимает на какую-то кнопку внутри прикладной программы (например "войти в систему"). Программа создаёт SQL-запрос к базе, и отправляет его в СУБД. Далее, СУБД анализирует полученный запрос и отправляет в ответ на него какие-то данные. Например, подтверждение что пароль верный, и пользователя можно пустить в систему. Интерфейс получает эти данные, и показывает пользователю что-то красивое и понятное.
Именно по этой причине, SQL имеет такое название. Любые операции с базами данных происходят с помощью запросов. Запросы используются для того, чтобы найти данные, добавить данные, изменить данные или удалить данные. Фактически, вся "операционная" часть языка SQL сводится к этим четырём типам операций.
Как писать простые SQL-запросы 📬
Прежде чем мы начнем, предварительно убедись, что у тебя на компьютере установлена СУБД. А так же, проверь есть ли у тебя приложение для управления базой данных через графический интерфейс. В качестве простой СУБД для начала экспериментов, я советую MySQL. В качестве графического интерфейса рекомендую стандартный MySQL Workbench - он потребуется для создания и отправки запросов.
Если же ты хочешь зайти в SQL "по хардкору" - скачивай PostgreSQL + pgAdmin. Это более сложная СУБД, но в общем и целом она значительно лучше и шире в плане своих возможностей. Условно говоря, MySQL - это уровень регионального проекта. А вот на PostgreSQL работают такие гиганты, как например Авито и Госуслуги.
Если ты сделал всё правильно - а именно, установил и потом запустил СУБД и графический интерфейс к нему, то пришло время экспериментов! В первую очередь, СУБД попросит тебя создать таблицу с некоторым набором столбцов. Ты можешь проявить свободу фантазии, и назвать столбцы любым образом - предположим это будет таблица "Users" со столбцами Id, UserName, UserAge, UserBiography и так далее. Не спеши волноваться или заранее переживать - представь себе что ты создаёшь новый excel-файл - потому что в-сущности своей, это похожие друг на друга вещи. В конечном итоге, у тебя должна получиться новая таблица, к которой ты сможешь отправлять запросы на языке SQL.
Запомни - основных SQL-запросов всего 4 (звучит просто, не так ли?). Это запросы SELECT, INSERT, UPDATE, DELETE. Каждый из этих запросов нужен для выполнения определенного действия, а именно:
- INSERT - добавить данные в какую-то таблицу
- SELECT - найти данные внутри таблицы
- UPDATE - обновить какие-то данные внутри таблицы
- DELETE - удалить данные из таблицы
Сочетание этих запросов, и некоторых других специальных команд, по-сути своей, и создает синтаксис языка SQL. К слову сказать, язык SQL стандартизован по определенным правилам, и есть большие сложные документы которые описывают все его нюансы. Но на данный момент тебе совсем не обязательно погружаться глубоко в эту историю - начни с простых запросов, которые мы сейчас разобрали.
Иерархия базы данных 📖
Небольшой нюанс в дополнение к SQL-запросам. Каждый запрос, который ты пишешь - создаётся для конкретной таблицы с конкретными данными. Внутри условной базы данных может быть много различных таблиц, например: "пользователи", "журнал событий", "опубликованные записи", "заметки", и так далее. У каждой из этих таблиц будут свои наборы столбцов, индексов и других настроек. Создавая SQL запрос, ты будешь всегда привязываться к какой-то конкретной таблице, а не к абстрактной "базе данных целиком".
Например, если ты хочешь получить список всех пользователей (у тебя есть соответствующая таблица которую ты ранее создал) - то тебе нужно будет адресовать запрос типа SELECT именно к этой таблице. При этом, данный запрос ни коим образом не будет влиять на другие таблицы, находящиеся внутри этой же базы данных.
Это можно представить как папку с файлами. Внутри некоторый папки может быть несколько Excel-файлов, каждый из которых будет содержать какую-то определенную информацию. Если ты откроешь какой-то один файл и будешь работать с ним, остальные файлы останутся нетронутыми - и будут спокойно себе лежать в этой папке дальше.
Другая аналогия. База данных в некотором смысле - это библиотека со множеством отделов, и множеством книг внутри каждого отдела. Когда ты хочешь найти конкретную книгу, и прочитать какие-то конкретные разделы внутри этой книги - тебе нужно пойти в какой-то один отдел.
Пример создания простой таблицы в MySQL, добавления и поиска данных внутри неё 🐬
Чтобы научиться писать SQL-запросы, давай воспользуемся следующим примером. Допустим, у тебя есть таблица "жители", внутри которой хранятся данные о некоторых жителях разных городов. Пусть внутри этой таблицы будут вот такие столбцы: "Имя", "Фамилия", "Пол", "Дата рождения", "Адрес", "Город проживания", "Рост", "Биография". Так же допустим, что внутри этой таблицы ещё есть столбец "цвет волос", и прямо сейчас тебе нужно найти всех пользователей с некоторым конкретным цветом волос, родившихся в определенные годы. Описать задачу другими словами можно вот так:
"Нужно найти внутри таблицы имена и фамилии людей с рыжими волосами, которые родились с 1990 по 1994 годы, после чего отсортировать их по фамилии в алфавитном порядке"
Для решения этой учебной задачи, нам сначала нужно будет создать саму таблицу. Создание таблиц внутри СУБД выполняется так же через язык SQL. И хотя ты возможно уже увидел графический интерфейс MySQL Workbench или pgAdmin, пусть они не вводят тебя в заблуждение - эти программы просто транслируют твои действия в язык SQL, который потом отправляется на сервер "в общем порядке". У графических интерфейсов для работы с базами данных нет каких-то волшебных полномочий на прямое изменение данных внутри базы. Они занимаются упрощением доступа к данным, и переводом твоих графических кликов по различным кнопкам в SQL-запросы. // Обращаю твоё внимание - что это достаточно важный концептуальный момент. Некоторые начинающие разработчики баз данных "свято верят" в то, что GUI это какой-то волшебный портал внутрь файловых систем баз данных. Но на самом деле, это не так. Это просто трансляторы действий в SQL-запросы.
Создание таблицы внутри базы данных выполняется с помощью команды CREATE TABLE (буквально - "Создать таблицу"). Давай рассмотрим, как будет выглядеть синтаксис для создания нашей таблицы "жители".
CREATE TABLE
Синтаксис этого запроса очень прост. В буквально смысле, запрос просит базу данных создать отдельную таблицу (условно говоря - "файл"). Внутри этого файла, как и у любой уважающей себя таблицы, должны быть столбцы. Их нужно перечислить внутри запроса. Так же, желательно объяснить таблице - какой из столбцов будет уникальным (какой из столбцов будет ключом к каждой записи). Особенность такого столбца будут в том, что значения в нём всегда должны быть уникальны. Ключи могут быть числовыми или строковыми, но пока не будем забегать вперёд - давай сделаем самый простой числовой ключ, который будет увеличиваться на 1 для каждой новой записи: 1,2,3... и так далее. Вот как будет выглядеть синтаксис такого запроса:
CREATE TABLE `citzens` (
`id` INT NOT NULL AUTO_INCREMENT,
`first_name` TEXT NULL,
`last_name` TEXT NULL,
`gender` VARCHAR(45) NULL,
`birth_date` DATETIME NULL,
`address` TEXT NULL,
`city_of_living` TEXT NULL,
`height` DOUBLE NULL,
`biography` TEXT NULL,
`hair_color` TEXT NULL,
PRIMARY KEY (`id`));
Внутри запроса мы только что определили основные столбцы таблицы. Каждому столбцу мы присвоили определенный тип данных. Так например, текстовые столбцы получили тип данных TEXT, а числовые - INT или DOUBLE. Для столбца birh_date мы использовали специальный тип данных DateTime. О том, что такое типы данных и зачем они нужны у меня есть отдельная статья. Теперь ты можешь смело вставлять этот запрос в GUI и отправлять его на выполнение. Вот так:
Если ты сделал всё правильно, то в нижней части экрана появится подтверждение успешного выполнения запроса. А в списке таблиц появится новая таблица (если не появился - нажми правой кнопкой на список таблиц и кликни Refresh All / обновить все. В крайнем случае, перезагрузи GUI).
Начиная с этого момента, ты можешь создавать фактические запросы к новой таблице "citzens". Ты можешь добавлять туда данные, искать, обновлять или удалять данные. Ты можешь делать это через точно такие же SQL запросы - просто используй текстовое поле в центральной части экрана.
Следующий этап решения задачи - наполнение таблицы какими-то первичными данными. Давай попробуем добавить туда несколько жителей.
INSERT
Вставка данных в таблицу осуществляется с помощью команды INSERT. В переводе на Русский язык - "Вставить". Внутри команды перечисляется список столбцов, и соответствующий ему список значений для этих столбцов. Давай попробуем создать такую команду, и добавить информацию в только что созданную таблицу.
INSERT INTO `citzens`
(
`first_name`,
`last_name`,
`gender`,
`birth_date`,
`address`,
`city_of_living`,
`height`,
`biography`,
`hair_color`
)
VALUES
(
'Алексей',
'Петров',
'М',
'1983-11-12 14:15:33',
'Крылатское шоссе дом 3',
'Москва',
187,
'Увлекается моделированием космических кораблей',
'Каштановые'
),
(
'Светлана',
'Хохотунчик',
'Ж',
'1993-06-11 12:15:33',
'проспект Самолётостроителей дом 8, кв.73',
'Москва',
167,
'Переводчик с Китайского',
'Рыжие'
),
(
'Иван',
'Ивановских',
'М',
'1984-06-11 12:15:33',
'Пулковское шоссе д.17, кв.1',
'Москва',
172,
'Занимается профессиональным спортом',
'Коричневые'
),
(
'Евгений',
'Булкоед',
'М',
'1993-12-12 11:22:44',
'Тверская д.8, кв.111',
'Москва',
187,
'Профессиональный ловец улиток',
'Рыжие'
);
Обрати внимание, что внутри запроса каждому столбцу соответствует значение, которое мы хотим в него добавить. И кстати, ты можешь сам дописать туда ещё больше данных - просто добавь их через запятую, после последних скобочек, следуя формату данных из приведенного примера.
И наконец, переходим к выборке данных из таблицы. Давай составим простой запрос на выборку данных. Это будет запрос типа SELECT. Рассмотрим его синтаксис более подробно:
SELECT
Основа запроса на поиск данных - команда SELECT. Она сообщает СУБД о том, что ты хочешь что-то найти в какой-то конкретной таблице. Эта команда также позволяет выбрать, какие именно поля (столбцы) данных отображать в результирующей таблице. В данном примере, мы стремимся найти людей, которые соответствуют некоторым поставленным условиям - и выбрать их имена и фамилии.
Команда SELECT начинается с соответствующего ключевого слова, после которого через запятую указываются столбцы, которые мы хотим получить. Обрати внимание, что это пока не полная SQL-команда, а только её начало:
SELECT
first_name,
last_name
...
Следующее важное ключевое слово внутри SQL-запроса на выборку данных - "FROM". Это слово определяет конкретную таблицу, из которой ты будешь извлекать данные.
Вот следующая часть SQL-запроса. Опять же, обрати внимание, что это только часть запроса. Она уже будет работать, но в качестве результата мы получим все данные из таблицы - а нам нужны конкретные люди с определенными признаками по цвету волос:
SELECT
first_name,
last_name
FROM
citzens
...
Далее, нам нужно использовать ключевое слово WHERE. Эта команда является опциональной, но в данном случае она нужна. WHERE позволяет отфильтровать данные, чтобы получить только какую-то определенную их часть. Без этой команды запрос будет возвращать все данные. В нашем примере мы хотим отфильтровать наш запрос, чтобы он включал только людей с рыжими волосами, родившихся в 2004 году. Начнем с фильтра волос:
SELECT
first_name,
last_name
FROM
citzens
WHERE
hair_color = 'Рыжие';
В данный момент это уже будет работающим SQL-кодом с работающим условием фильтрации. Ты можешь выполнить его внутри GUI и посмотреть на результаты.
Но поскольку наш пример является учебным, давай так же дополним его фильтром по возрасту.
Фильтрацию по числовым диапазонам можно делать с помощью стандартных операторов - больше, меньше, равно. Но помимо равенства (=), есть ещё ключевое слово BETWEEN - это еще один оператор, который можно и нужно использовать для условных запросов. Этот оператор будет работать для поиска значений, которые находятся между указанными минимальным и максимальным цифрами.
В нашем случае, мы будем использовать BETWEEN для получения записей за определенный период, например с 1990 по 1994 годы. Вот так будет выглядеть такой запрос:
SELECT
first_name,
last_name
FROM
citzens
WHERE
birth_date BETWEEN '1990-01-01' AND '1994-12-31';
И наконец, давай соединим две части запроса в один большой запрос. Чтобы мы увидели и людей с определенным цветом волос, которые одновременно с этим родились в некоторый диапазон дат. Для этого, мы используем ключевое слово AND. Оно позволит соединить несколько условий внутри оператора WHERE. Помни, что мы стремимся сузить результаты, включив в него людей с каштановыми волосами и одновременно с этим тех из них, кто родился с 1990 по 1994 годы. Поэтому запрос дополняется все более новыми конструкциями.
Ключевое слово AND нужно поставить между первым и вторым условием. То есть он как бы попросит базу данных найти данные которые соответствуют первому условию и одновременно с этим, второму условию. Вот как это выглядит (работающий SQL-запрос):
SELECT
first_name,
last_name
FROM
citzens
WHERE
hair_color = 'Рыжие'
AND
birth_date BETWEEN '1990-01-01' AND '1994-12-31';
Организация результатов поиска - сортировка и группировка данных 📦
Организация результатов и некоторые дополнительные действия также могут быть выполнены в рамках SQL запроса. Тут на помощь приходят функции ORDER BY и GROUP BY. Сначала мы рассмотрим наши бывшие SQL-запросы, но уже с включением функции ORDER BY и GROUP BY.
Операция ORDER BY позволяет выполнять сортировку по любому из полей, указанных в инструкции SELECT. В данном случае давай упорядочим по фамилии. Вот наш SQL-запрос:
SELECT
first_name,
last_name
FROM
citzens
WHERE
hair_color = 'Рыжие'
AND
birth_date BETWEEN '1990-01-01' AND '1994-12-31'
ORDER BY
last_name;
GROUP BY
GROUP BY предназначен для группировки данных по некоторому столбцу. Он определенным образом похож на ORDER BY, но они различаются способом манипулирования данными. В то время как ORDER BY упорядочивает данные в определенном порядке, GROUP BY объединяет похожие данные, позволяя группировать и подсчитывать дубликаты в полях. С помощью этой операции, можно так же подсчитать результаты группировки. Например, благодаря такому SQL-запросу, можно получить количество людей с определенной фамилией внутри базы данных:
SELECT
SELECT irst_name,
last_name,
COUNT(Id)
FROM
citzens
GROUP BY
last_name;
ORDER BY против GROUP BY
Чтобы показать тебе различие между операторами ORDER BY и GROUP BY, я на мгновение отойду от нашего примера про Москву и рассмотрю базовый набор данных. Ниже приведен набор из четырех идентификационных номеров сотрудников вместе с соответствующими именами (микро-таблица).
Если я буду использовать оператор ORDER BY, имена сотрудников в этом списке будут отсортированы в алфавитном порядке. Мой результат будет выглядит так:
Если бы я использовал оператора GROUP BY, подсчёт в этом списке упорядочит имена сотрудников в некотором порядке, и отобразит количество людей с разными именами. Для этого, потребуется так же использовать оператор COUNT.
Поведём итоги 🎬
SQL-запросы позволяют пользователям получать информацию из базы данных, выполняя команды, написанные на SQL. С помощью языка SQL, а так же с помощью графических систем GUI ты можешь делать с таблицами внутри базы данных следующие операции:
- Получение данных. SQL-запросы позволяют пользователям быстро извлекать определенные подмножества данных из больших баз данных. Ты можешь попросить базу данных найти какие-то данные по какому-то конкретному условию.
- Модификация данных. SQL-запросы не только извлекают данные, но и позволяют совершать операции по изменению данных. Пользователи могут обновлять строки данных. Каждое обновление записывается на сервере в специальном жунрале, и позволяет отследить хронологию действий.
- Добавление данных. Операция INSERT является первым шагом при работе с новыми таблицами. Она в буквальном смысле записывают в таблицу определенный набор информации, которая становится доступна для всех пользователей этой базы данных.
- Удаление данных. SQL-запросы позволяют безопасно удалять данные, не влияя на работу других пользователей. В чем особенность "опасности" одновременного удаления данных сразу несколькими пользователями - я писал вот здесь.
Для начинающего программиста очень важно уметь использовать SQL и понимать, как именно устроены базы данных. Практически все современные приложения хранят данные определенным образом - хоть соцсети, хоть банки. Начав развиваться как программист, ты рано или поздно столкнёшься с вопросами хранения данных. И хорошо, если к этому моменту ты будешь подготовлен на учебных примерах и простых практических задачах. Успехов!
🔥 Понравилось? Подпишись! Победим восстание роботов вместе! 🔥
🚀 P.S. Ты можешь круто поддержать меня и проект "Войти в IT" на boosty! Я публикую там более эксклюзивный и профессиональный, иногда немного личный контент. Хочешь посмотреть как я выгляжу в реальной жизни? Тогда жми: Ссылка 🚀
P.S.2 У меня ещё есть Telegram-канал. Там посты чуть попроще, и чуть повеселей. Ссылка
