SQL Academy: всё о реляционных БД и SQL

Хранимые процедуры в MySQL 📘 Хранимые процедуры в MySQL — это мощный инструмент для автоматизации и упрощения рутинных задач по обработке данных в базах данных 🚀. Они представляют собой наборы SQL-команд, которые компилируются один раз и хранятся на сервере. Это позволяет повторно использовать код, обеспечивая более быстрое выполнение и уменьшение нагрузки на сеть. Зачем нужны хранимые процедуры? 🤔 🔹Эффективность: Хранимые процедуры выполняются на сервере, что снижает количество передаваемых данных между сервером и клиентом 📉. 🔹Безопасность: Они обеспечивают дополнительный слой безопасности, позволяя ограничить доступ к данным и логике обработки 🔒. 🔹Уменьшение дублирования кода: Хранимые процедуры могут быть вызваны из любого места в приложении, что уменьшает дублирование кода 🔄. Пример хранимой процедуры 📝 Вот как можно создать простую хранимую процедуру в MySQL, которая добавляет новую запись в таблицу:

DELIMITER $$

CREATE PROCEDURE AddCustomer(IN custName VARCHAR(100), IN custAddress VARCHAR(255))
BEGIN
    INSERT INTO Customers (Name, Address) VALUES (custName, custAddress);
END$$

DELIMITER ;

Этот пример демонстрирует создание процедуры AddCustomer, которая принимает два параметра (имя и адрес клиента) и вставляет эти данные в таблицу Customers. Для вызова ранее созданной процедуры AddCustomer используйте следующий SQL-запрос:

CALL AddCustomer('Иван Иванов', '123 Примерная ул., Москва');

Этот вызов произведет вставку новой записи в таблицу Customers с именем и адресом, указанными в качестве параметров. Заключение Хранимые процедуры в MySQL играют важную роль в разработке и управлении базами данных 🗄️. Они не только упрощают код и улучшают его поддержку, но и повышают производительность за счет уменьшения количества обработки данных на стороне клиента и сервера. Использование хранимых процедур может значительно повысить эффективность работы с большими объемами данных и сложными запросами 🌟.

REGEXP 🔍в SQL REGEXP — это оператор в SQL, использующий регулярные выражения для сопоставления строк в запросе к базе данных. Это позволяет выполнять сложные поиски по шаблонам внутри текстовых данных. Для чего используется? 🎯 1️⃣Искать строки, соответствующие сложным шаблонам. 2️⃣Выполнять поиск без учета регистра. 3️⃣Находить строки, содержащие определенные символьные последовательности в любом месте текста. 4️⃣Использовать группировку и альтернативы в шаблонах поиска. Примеры использования Предположим, у нас есть таблица users с колонкой email. Мы хотим найти все адреса электронной почты на домене example.com.

SELECT email FROM users WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@example\.com$';

Отличие от LIKE 😼 Хотя операторы LIKE и REGEXP могут использоваться для поиска текстовых данных, между ними есть несколько ключевых различий: 🔹Гибкость: REGEXP предлагает гораздо большую гибкость и мощь 💪, позволяя создавать сложные шаблоны поиска, в то время как LIKE ограничен простыми шаблонами с использованием символов % и _. 🔹Производительность: LIKE может быть быстрее для простых поисков 🚀, поскольку он менее ресурсоемок, в то время как REGEXP может быть более затратным из-за сложности обработки регулярных выражений. 🔹Синтаксис: Синтаксис REGEXP сложнее, поскольку он требует понимания регулярных выражений, тогда как LIKE проще в использовании для базового поиска. В целом, выбор между LIKE и REGEXP зависит от конкретных требований к поиску 🧐. Если нужен простой поиск по шаблону, то подойдет LIKE. Для более сложных и гибких поисковых запросов лучше использовать REGEXP .

Мы опубликовали новую статью на vc «Взламываем SQL интервью на позиции в Тинькофф и Альфа-Банк» Поддержите нас лайками 👍 и комментариями 💬. https://vc.ru/hr/1085970-vzlamyvaem-sql-intervyu-na-pozicii-v-tinkoff-i-alfa-bank ❗️Лайкать и комментить важно для попадания поста в топ главной виси в первые 30-60 минут. Потом тоже полезно, но эффективность с каждым часом кратно снижается из-за конкуренции с другими материалами и из-за алгоритмов площадки.

🚀 Foreign Keys (Внешние ключи) Внешние ключи — это столбцы в таблице, которые ссылаются на ключевые столбцы в другой таблице. Это основа для связывания данных между таблицами в базе данных. Их главная цель — поддержание целостности данных и обеспечение связи между таблицами 🌐. Зачем нужны? ℹ️Обеспечивают связь между таблицами 🤝 ℹ️Гарантируют, что каждое значение в столбце существует в связанной таблице ℹ️Помогают избежать неконсистентности данных Как создать? 1️⃣Определите основной ключ (Primary Key) в родительской таблице. 2️⃣Создайте столбец в дочерней таблице, который будет внешним ключом. 3️⃣Свяжите их с помощью ограничения FOREIGN KEY. Пример SQL кода:

CREATE TABLE Parents (
    ParentID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Children (
    ChildID INT PRIMARY KEY,
    Name VARCHAR(100),
    ParentID INT,
    FOREIGN KEY (ParentID) REFERENCES Parents(ParentID)
);

Особенности: - Каскадное удаление: Если родительская запись удаляется, все связанные дочерние записи тоже удаляются. - Ограничение целостности 🔒: Нельзя добавить запись в дочернюю таблицу, если нет соответствующей записи в родительской таблице. - Улучшает производительность запросов благодаря индексации связанных столбцов 🏎️. Когда вы используете внешние ключи, вы облегчаете себе жизнь, гарантируя, что ваша база данных останется чистой и все связи между таблицами будут корректными. Правильно используйте внешние ключи, и ваши данные всегда будут в порядке! 🔐💼

Транзакции в MySQL 🚀 - это способ гарантировать, что ваши данные остаются надежными и целостными. Давайте разберемся, как это работает на примерах. Что такое транзакции? 🤔 Транзакция - это группа операций с данными, которые либо полностью выполняются, либо нет. Они основаны на принципах ACID: ℹ️ Атомарность (все или ничего) ℹ️Согласованность (данные остаются правильными) ℹ️ Изоляция (операции не мешают друг другу) ℹ️ Долговечность (результаты сохраняются после завершения) Примеры использования 🛠 1. Перевод денег с одного счета на другой:

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Здесь мы используем START TRANSACTION и COMMIT, чтобы гарантировать, что обе операции выполнены вместе. 2. Откат при ошибке:

START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
-- Если ошибка, отменяем всё
ROLLBACK;

ROLLBACK отменяет все изменения, если что-то пошло не так. Заключение Использование транзакций в MySQL помогает сохранять ваши данные в безопасности и целостности, гарантируя, что комплексные операции либо выполняются полностью, либо не оказывают влияния вовсе.

🌟 Считаем возраст человека правильно 🌟 Вы когда-нибудь задумывались, как правильно вычислить возраст человека в SQL? 🤔 Может показаться, что всё просто: возьмём текущий год, отнимем год рождения, и вуаля! Но ой, подстерегает соблазн ошибки! 😈 🚫 Неправильный подход:

SELECT YEAR(NOW()) - YEAR('2003-07-03 14:10:26');

Проблема такого подхода в том, что он не учитывает был ли день рождения у данного человека в этом году или ещё нет. То есть, если на момент запроса уже наступило 3-е июля (07-03), то человек отпраздновал свой день рождения и ему уже 21 лет, иначе ему по-прежнему 20 года. Разница функций YEAR тут будет бесполезна — в обоих случаях она даст 21 лет. 🤔 Что же делать? Мы могли бы пойти по пути вычисления разницы в днях и деления на 365:

SELECT FLOOR(DATEDIFF(NOW(), '2003-07-03 14:10:26') / 365);

Это лучше, но всё же не совсем точно из-за наличия високосных годов. 📆 ✅ Правильный подход с TIMESTAMPDIFF:

TIMESTAMPDIFF(YEAR, '2003-07-03 14:10:26', NOW());

Используя функцию TIMESTAMPDIFF, мы получаем точную разницу в годах между двумя датами, учитывая все нюансы календаря! 🎯 Так что, когда дело доходит до вычисления возраста, доверяйте TIMESTAMPDIFF — и ваши расчёты будут точны как никогда! 🚀💡

Мы продолжаем собирать коллекцию тестовых заданий по SQL от ведущих компаний, чтобы сделать вашу подготовку еще более эффективной и интересной! Если в последнее время вы сталкивались с уникальными и вызывающими заданиями по SQL на собеседованиях или в тестовых заданиях, мы будем рады, если поделитесь ими с нами! 📩 Отправляйте свои задания на feedback@sql-academy.org и помогите нам сделать нашу базу еще богаче и полезнее для всех, кто стремится развиваться и достигать новых высот в области SQL. В знак благодарности за ваши усилия, в случае если ваш материал окажется особенно ценным, мы с удовольствием предоставим вам промокод на бесплатный доступ к премиуму на нашей платформе. 🎁 Давайте вместе строить лучшее будущее для всех, кто интересуется SQL!

🚀 Вопросы с собеседования на позицию intern аналитика в Тинькофф: разбор SQL (часть 2) 🚀 Сегодня мы разберем некоторые интересные вопросы по SQL, которые могут встретиться на собеседовании в Тинькофф. 📊🔍 1️⃣ Можно ли делать JOIN таблицы саму на себя? 🤔 Абсолютно! Это называется self-join и полезно для работы с иерархическими данными или для сравнения строк в таблице. Вот пример запроса:

SELECT a.name AS EmployeeName, b.name AS ManagerName
FROM employees a
JOIN employees b ON a.manager_id = b.id;

Здесь мы используем одну и ту же таблицу employees для поиска имени сотрудника и его менеджера. Псевдонимы a и b помогают нам различать два использования таблицы. 2️⃣ Какой из операторов: SELECT, FROM, WHERE, GROUP BY - выполняется последним? 🧐 И тут главный герой - SELECT! 🌟 Хотя он стоит на первом месте по синтаксису, логически данные выбираются после всех фильтраций и группировок. Так что, порядок такой: 1. FROM ➡️ 2. WHERE ➡️ 3. GROUP BY ➡️ 4. SELECT. 3️⃣ Минимальное и максимальное количество записей в FULL JOIN таблицы на 10 и 100 строк? 📊 С FULL JOIN всё интересно: ℹ️Минимум? 100 строк, если каждая из 10 нашла свою пару. ℹ️Максимум? 110 строк, если совпадений ноль и каждая строка появляется отдельно. #задание_из_собеседования #tinkoff #intern #analytic

🚀 Внимание, обновление в разделе «Тренажер»! 🌟 Теперь у нас есть уникальная коллекция заданий прямиком из реальных собеседований по SQL от ведущих компаний! 💼 Если вы недавно сталкивались с интересными задачами на собеседованиях или тестовых заданиях по SQL, поделитесь ими с нами по адресу feedback@sql-academy.org. Давайте вместе сделаем нашу базу заданий еще лучше и полезнее для всех! 🤝💡 Вместе мы создадим мощный инструмент для подготовки и успеха в будущих собеседованиях! ✨

🚀 Вопросы с собеседования на позицию intern аналитика в Тинькофф: разбор SQL 🚀 Сегодня мы разберем некоторые интересные вопросы по SQL, которые могут встретиться на собеседовании в Тинькофф. 📊🔍 1️⃣ Может ли измениться результат запроса, если в LEFT JOIN поменять местами таблицы ? Да, если поменять местами таблицы в LEFT JOIN, результат запроса кардинально изменится. Все потому, что LEFT JOIN берет все строки из "левой" таблицы, дополняя их данными из "правой". Смена местами меняет логику: теперь "правая" становится "левой" и наоборот. Это влияет на то, какие строки и как будут включены в результат. 🔄 2️⃣ 5 + NULL это сколько? В SQL, когда вы выполняете арифметическую операцию с NULL, результатом всегда будет NULL. Это связано с тем, что NULL представляет собой неопределенное значение, и любая операция с неопределенным значением также является неопределенной. Таким образом, 5 + NULL будет равно NULL. 3️⃣ Какие функции умеют возвращать значения из предыдущих/последующих строк для заданной строки таблицы ? В SQL, чтобы работать с данными из строк до и после текущей, используются оконные функции. Эти функции обеспечивают доступ к значениям предыдущих/последующих строк: ℹ️LEAD(): Получает данные из строки после текущей, позволяя смотреть вперед на заданное количество строк. ℹ️ LAG(): Доступ к данным из строки перед текущей, предоставляя возможность анализировать предыдущие значения. ℹ️ FIRST_VALUE() и LAST_VALUE(): Возвращают первое и последнее значение в наборе строк соответственно, идеально для сравнения текущих значений с крайними в диапазоне. ℹ️ NTH_VALUE(): Дает значение из конкретной позиции в окне, полезно для нахождения конкретных точек данных в последовательности. Для тех, кто хочет углубиться в тему оконных функций: https://sql-academy.org/ru/guide/windows-functions #задание_из_собеседования #tinkoff #intern #analytic

Больше об CTE по ссылке https://sql-academy.org/ru/guide/operator-with