🖥 Компактная таблица-шпаргалка по SQL.

📌 Шпаргалка

@sqlhub

🕵‍♂Использование CROSS JOIN для задач поиска пересечений в исторических данных

🔵 CROSS JOIN (или полное соединение таблиц без условий) — декартова перемножение множеств. Говоря простым языком — для каждого варианта первого множества будет сопоставлены все варианты второго множества.

• Правильное использование этого типа соединения может помочь в решении сложных задач, например, нахождении пересечений в исторических данных.

• Структура таблиц представленных примеров в изображении 1.

📕 Допустим: клиент Сидоров Степан Павлович подал заявку на кредит. На некотором этапе рассмотрения потребовался анализ платежеспособности клиента андеррайтером.
Заявка автоматически распределилась на сотрудника Петрова Екатерина Павловна.

На первый взгляд ничего подозрительного нет — в ФИО людей совпадает только отчество, что является широко распространенной ситуацией.

❗️ Однако, при детальном анализе выясняется, что девичья фамилия андеррайтера и клиента совпадают (Сидорова / Сидоров) изображение 2.

В таком случае заявка клиента должна была распределиться на другого сотрудника, чтобы решение, вынесенное по заявке, было не предвзятым.

• Для решения данной задачи можно использовать простой запрос с CROSS JOIN изображение 3.

➡️ Продолжение

🖥 Что нового в SQLAlchemy 2.0?

В начале 2023 года вышла SQLAlchemy 2.0 — библиотека на Python для работы с реляционными СУБД, которая работает с Object Relational Mapper (объектно-реляционным отображением). Основная задача SQLAlchemy — синхронизация объектов Python с данными в БД.

То есть с помощью SQLAlchemy можно описывать структуры БД и работать с их данными на объектно-ориентированном коде на Python без использования чистого SQL. Другая важная особенность SQLAlchemy — код для работы с базой данных будет одинаковым вне зависимости от БД, которую использует разработчик. Такой подход позволяет без проблем мигрировать с одной базой данных на другую.

Полный список нововведений в SQLAlchemy 2.0 можно посмотреть в официальной документации сервиса.

▪ Читать

@sqlhub

🤔 10 аргументов “за” и “против” SQL 

• Вторая по обсуждаемости тема в среде специалистов по SQL: как произносить то, что большинство людей называют Sequel ([сиквел] вместо [эс кью эл]). Это удивительно, поскольку язык существует более 40 лет.

• Но, пожалуй, самая обсуждаемая тема, связанная с SQL, — это вопрос о том, считается ли он языком программирования.

Почему SQL не является языком программирования

🟢 Создание и выполнение запросов, а не скриптов
🟢 Не изменяет состояние
🟢 Отсутствие циклирования
🟢 Репутация в отрасли

Почему SQL является языком программирования

🟣 Соответствие критерию Тьюринга
🟣 Переменные, условная логика, определения функций
🟣 Возможность создавать приложения (хотя это сложно)
🟣 Так считают в Google

Подробное разъяснение читай тут

@sqlhub

⭐️ 100 датасетов для анализа данных на все случаи жизни.

▪Список
▪Зеркало

@sqlhub

🖥 Простой скрипт для резервного копирования всех баз данных SQL Server

Иногда то, что кажется нам сложным, оказывается гораздо проще, чем мы думали, и в этом заключается сила использования T-SQL для решения повторяющихся задач.

Одной из таких задач может быть необходимость создания резервных копий всех баз данных на вашем сервере. Если баз не много, это не проблема, но я видел несколько серверов, где на одном экземпляре SQL Server было 100+ баз данных.

Вы можете использовать SQL Server Management Studio для резервного копирования баз данных или даже использовать Maintenance Plans, но использование T-SQL — гораздо более простой и быстрый подход.

Решение
С помощью языка T-SQL вы можете создавать скрипты резервного копирования, а с помощью мышки вы можете просматривать все базы данных для их резервного копирования по очереди. Можно также использовать цикл while, если вы предпочитаете не использовать мышь. Это очень простой процесс, и для его выполнения вам понадобится всего несколько команд.

Как создать резервные копии всех баз данных SQL Server
▪Укажите путь для хранения бэкапов баз данных
▪Укажите формат имени файла резервной копии
▪Выберите список баз данных для резервного копирования
▪Цикличность
▪Программно создаем команду резервного копирования базы данных, используя имя базы данных, путь и формат имени файла

Формат имени файла бэкапа базы данных DBname_YYYYDDMM.BAK
Вот скрипт, который позволит вам создать резервную копию каждой базы данных в вашем экземпляре SQL Server. Вам нужно будет изменить @path на соответствующий директорию резервного копирования.

DECLARE @name NVARCHAR(256) -- database name
DECLARE @path NVARCHAR(512) -- path for backup files
DECLARE @fileName NVARCHAR(512) -- filename for backup
DECLARE @fileDate NVARCHAR(40) -- used for file name

-- specify database backup directory
SET @path = 'C:\test\'

-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor
Формат имени файла бэкапа базы данных DBname_YYYYDDMM_HHMMSS.BAK
Если вы хотите также включить время в название файла, вы можете заменить эту строку в приведенном выше сценарии:

-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112)
этой строкой:

-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),':','')
Добавление дополнительных элементов в команду резервного копирования
Чтобы добавить дополнительные параметры, такие как прогресс резервного копирования (статистика) и сжатие, вы можете изменить эту строку в скрипте:

BACKUP DATABASE @name TO DISK = @fileName
этой строкой:

BACKUP DATABASE @name TO DISK = @fileName WITH STATS=10, COMPRESSION

Примечания
В этом скрипте мы обходим системные базы данных, но их также можно легко включить. Вы также можете превратить его в хранимую процедуру и передать имя базы данных или, если оставить NULL, создать резервные копии всех баз данных. В любом случае, этот скрипт даст вам отправную точку для простого резервного копирования всех ваших баз данных.

Кроме того, если вы хотите обойти некоторые пользовательские базы данных, вы можете включить их в раздел NOT IN.

Следующие шаги
Добавьте этот сценарий в свой набор инструментов (toolbox)
Измените этот сценарий и сделайте его хранимой процедурой, чтобы включить один или несколько параметров
Усовершенствуйте сценарий для использования дополнительных опций BACKUP

@sqlhub

🖥 Скрытые ошибки SQL, допускаемые опытными аналитиками данных

Написание неправильного синтаксиса SQL не так страшно, потому что вы сразу же узнаете об этом, исправите то, что упустили, и вуаля, все работает как ни в чем не бывало.

А вот что действительно плохо, так это когда код выполняется и выдает результат, но не правильный тогда вы можете не заметить проблему.

Возьмем две таблицы, первая содержит клиентов вашего сайта, а вторая – заказы, сделанные на сайте.

customers table:
+-------------+---------------+-------------+
| customer_id | customer_name | visit_count |
+-------------+---------------+-------------+
| 1 | John Doe | 5 |
| 2 | Jane Smith | 8 |
| 3 | Mike Johnson | 3 |
+-------------+---------------+-------------+

orders table:
+----------+-------------+------------+--------------+
| order_id | customer_id | order_date | total_amount |
+----------+-------------+------------+--------------+
| 1 | 1 | 2023-07-10 | 50.00 |
| 2 | 1 | 2023-07-12 | 75.00 |
| 3 | 2 | 2023-07-11 | 120.50 |
| 4 | 3 | 2023-07-15 | 25.75 |
+----------+-------------+------------+--------------+

Чаще всего мы работаем с таблицами не по отдельности, а с их объединениями. Приведенные выше таблицы можно объединить по столбцу customer_id.

• Теперь, если мы захотим вычислить что-то простое, например, общее количество посещений сайта или общее количество посещений по пользователям, то, если мы не будем очень внимательны, то, сами того не замечая, получим неверные результаты.
SELECT SUM(c.visit_count) AS total_visits
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id

В результате будет получено 21 посещение, что неверно.

• И даже когда мы пытаемся просуммировать общее количество посещений на одного пользователя :
SELECT c.customer_id, c.customer_name, SUM(c.visit_count) AS total_visits
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

• Мы получаем неверные значения для одного из клиентов (клиента, имеющего более одного заказа):
customers table:
+-------------+---------------+--------------+
| customer_id | customer_name | total_visits |
+-------------+---------------+--------------+
| 1 | John Doe | 10 |
| 2 | Jane Smith | 8 |
| 3 | Mike Johnson | 3 |
+-------------+---------------+--------------+

• Мы можем увидеть, что пошло не так, взглянув на таблицу, полученную в результате объединения:
+-------------+---------------+--------------+----------+------------+--------------+
| customer_id | customer_name | visit_count | order_id | order_date | total_amount |
+-------------+---------------+--------------+----------+------------+--------------+
| 1 | John Doe | 5 | 1 | 2023-07-10 | 50.00 |
| 1 | John Doe | 5 | 2 | 2023-07-12 | 75.00 |
| 2 | Jane Smith | 8 | 3 | 2023-07-11 | 120.50 |
| 3 | Mike Johnson | 3 | 4 | 2023-07-15 | 25.75 |
+-------------+---------------+--------------+----------+------------+--------------+

Это явление называется fanout, и его трудно заметить, поскольку результаты получаются частично корректными.

📌 Продолжение

@sqlhub

📊 VisiData v2.11

VisiDatahttps://github.com/saulpw/visidata является многофункциональным инструментом для обработки и просмотра табличных данных.

Этот интерактивный и эффективный инструмент предоставляет возможность работать с большими объемами данных.

VisiData работает с CSV файлами, электронными таблицами Excel, базами данных SQL и многими другими источниками данных.

pip3 install visidata

▪Github
▪Проект

@sqlhub

📌 Возможности SQLite, которые вы могли пропустить

🔵Частичные индексы (Partial Indexes)
При построении индекса можно указать условие попадания строки в индекс, к примеру, одна из колонок не пустая, а другая равна заданному значению.
create index idx_partial on tab1(a, b) where a is not null and b = 5;
select * from tab1 where a is not null and b = 5; --> search table tab1 using index

🔵Индексы на выражение (Indexes On Expressions)
Если в запросах к таблице часто используется выражение, то можно построить индекс по нему. Однако следует иметь в виду, что пока оптимизатор не очень гибок и перестановка столбцов в выражении приведет к отказу от использования индекса.
create index idx_expression on tab1(a + b);
select * from tab1 where a + b > 10; --> search table tab1 using index ...
select * from tab1 where b + a > 10; --> scan table

🔵Вычисляемые колонки (Generated Columns)
Если данные столбца представляют собой результат вычисления выражения по другим столбцам, то можно создать виртуальный столбец. Есть два вида: VIRTUAL (вычисляется каждый раз при чтении таблицы и не занимает места) и STORED (вычисляется при записи данных в таблицу и место занимает). Разумеется записывать данные в такие столбцы напрямую нельзя.
create table tab1 (
a integer primary key,
b int,
c text,
d int generated always as (a * abs(b)) virtual,
e text generated always as (substr(c, b, b + 1)) stored
);

🔵 R-Tree индекс
Индекс предназначен для быстрого поиска в диапазоне значений/вложенности объектов, т.е. задачи типичной для гео-систем, когда объекты-прямоугольники заданы своей позицией и размером и требуется найти все объекты, которые пересекаются с текущим. Данный индекс реализован в виде виртуальной таблицы (см. ниже) и это индекс только по своей сути. Для поддержки R-Tree индекса требуется собрать SQLite с флагом SQLITE_ENABLE_RTREE (по умолчанию не установлен).
create virtual table idx_rtree using rtree (
id, -- ключ
minx, maxx, -- мин и макc x координаты
miny, maxy, -- мин и макc y координаты
data -- дополнительные данные
);

insert into idx_rtree values (1, -80.7749, -80.7747, 35.3776, 35.3778);
insert into idx_rtree values (2, -81.0, -79.6, 35.0, 36.2);

select id from idx_rtree
where minx >= -81.08 and maxx <= -80.58 and miny >= 35.00 and maxy <= 35.44;

Остальные возможности можно тут

@sqlhub

➡️ Использование составных триггеров (compound triggers) Oracle

▪️Примеры таких триггеров в основном связаны с логированием изменений таблицы, я решила рассмотреть пример из двух таблиц, которые изменяют друг друга.

Дано: две таблицы orders и orders_detail.

CREATE TABLE TMP.orders
(
ID NUMBER, -- № заказа
DATE_DOC DATE, -- дата заказа
AMOUNT NUMBER, -- сумма заказа
DISCOUNT NUMBER -- скидка от 0 до 100%
);
CREATE TABLE TMP.orders_detail
(
ID NUMBER,
ID_ORDER NUMBER, -- № заказа
ID_GOODS NUMBER, -- идентификатор товара
PRICE NUMBER, -- цена товара
QTY NUMBER, -- количество
SUM_ORDER NUMBER -- стоимость заказа
);

Перед нами стоят задачи:

1) при обновлении поля orders.discount пересчитать orders_detail.sum_order, а также обновить поле orders.amount по всему заказу;
2) при изменении orders_detail.price и(или) orders_detail.qty пересчитать orders_detail.sum_order, а также обновить поле orders.amount по всему заказу;
3) orders_detail.sum_order считается по формуле: orders_detail.sum_order = orders_detail.price * orders_detail.qty * (1- orders.discount/100).

При таких задачах возникает ошибка мутирования таблиц ORA-04091: table is mutating.

Эта ошибка встречается тогда, когда триггер уровня строк пытается вы­полнить чтение или запись в таблицу, для которой сработал триггер.

• Например, нам нужно пересчитать поле orders_detail.sum_order, а для этого нам нужно прочитать поле orders.discount(которое изменилось).
• Начиная с Oracle Database 11g можно использовать compound(составные) триггеры. Они представляют собой четыре различных триггера, объединенных в один.

Вот пример синтаксиса такого триггера. ⤵️

Продолжение

@sqlhub

🦊 Kali Linux для продвинутого тестирования на проникновение. Sqlmap.

Наиболее распространенной и используемой уязвимостью на веб-сайтах является SQL-инъекция.

• Одним из самых полезных и мощных инструментов для оценки уязвимостей SQL-инъекций является sqlmap. Инструмент написан на Python, который автоматизирует разведку и эксплуатацию Firebird, Microsoft SQL, MySQL (теперь называется MariaDB, разработанный сообществом и коммерчески поддерживаемый форк MySQL), Базы данных Oracle, PostgreSQL, Sybase и SAP MaxDB.

Мы продемонстрируем атаку SQL-инъекцией на базу данных Mutillidae.

1) Определить веб-сервер, внутреннюю систему управления базами данных и доступные базы данных.

2) Получите доступ к веб-сайту Mutillidae и просмотрите веб-страницы, чтобы определить те, которые принимают пользовательский ввод (например, форма входа пользователя, которая принимает имя пользователя и пароль от удаленного пользователя); эти страницы могут быть уязвимы для SQL-инъекций.

3) Затем откройте Kali и из терминала введите следующее (используя соответствующий целевой IP-адрес):
root@kali:~# sqlmap -u 'http://targetip/mutillidae/index.php?page=user- info.php&username=admin&password=&user-info-php-submitbutton=

sqlmap вернет данные, как показано на *рисунке 1.

• Наиболее вероятной базой данных для хранения данных приложения, является БД Mutillidae;
поэтому мы проверим все таблицы этой базы данных, используя следующую команду:
root@kali:~# sqlmap -u "http://yourip/mutillidae/index.php?page=user-info.php&username=&password=&user-info-php-submit-button=View+Account+Details" -D mutillidae --tables

Данные, возвращаемые при выполнении этой команды, показаны на *рисунке 2.

📌Читать далее

@sqlhub

🐱 Переход на PgCat — прокси-сервер Postgres следующего поколения

PgCat  — это open source прокси-сервер Postgresql, в развитии которого мы участвуем и который используем на продакшене. Функционал PgCat для кластеров Postgresql: пул соединений, балансировка нагрузки, отработка отказа реплики.

• В Instacart мы применяем Postgresql почти для всех задач баз данных: эффективным кешированием, индексированием, оптимизацией запросов и вертикальным масштабированием экземпляров выжимаем из одного экземпляра Postgres немало производительности.

• Все это отличные стратегии, но с ними далеко не уедешь.
Не ограничиваясь одним экземпляром за счет добавления реплик чтения или горизонтального сегментирования БД, мы в обоих случаях увеличиваем сложность.

Почему PgCat?

📌 Разбираем подробно

@sqlhub