🖥 Наглядный гайд с функциями от SQL до Pandas


@sqlhub

🖥 Как выполнить запрос MySQL из командной строки

Вы можете легко выполнить запрос MySQL из командной строки, используя команду MySQL вместе с опцией -e.


mysql -h [хост] -u [имя пользователя] -p [пароль] [база данных] -e [SQL_запрос]

В приведенной выше команде необходимо указать имя пользователя, пароль, имя базы данных для соединения с базой данных MySQL. После опции -e необходимо указать SQL-запрос, который будет выполняться.

Вот простой пример выполнения SQL-запроса при подключении к локальной базе данных.


mysql -utest_user -ptest_password -hlocalhost db -e "SELECT * FROM table;".

Вы также можете опустить аргумент имени базы данных 'db', если вы ссылаетесь на нее в SQL-запросе.


mysql -utest_user -ptest_password -hlocalhost -e "SELECT * FROM db.table;"

Добавление точки с запятой в конце SQL-запроса необязательно. Но если вы хотите выполнить несколько SQL-запросов, вам необходимо разделить их с помощью точки с запятой, как показано в следующем примере.


mysql -utest_user -ptest_password -hlocalhost -e "SELECT * FROM db.table; SELECT * from db.table2;"

Вы также можете выполнять отдельные команды MySQL с опцией -e, если хотите.


mysql -utest_user -ptest_password -hlocalhost -e "SELECT * FROM db.table;"
mysql -utest_user -ptest_password -hlocalhost -e "SELECT * FROM db.table2;"

Вы также можете хранить свои SQL-запросы в файле и указать MySQL прочитать этот файл. Допустим, у вас есть файл queries.txt


nano queries.txt
со следующими запросами.


SELECT * FROM db.table;
SELECT * FROM db.table2;

Теперь, если вы хотите выполнить запросы в файле queries.txt, вы можете сделать это с помощью следующей команды. В этом случае MySQL будет читать входные данные из файла queries.txt.


mysql -utest_user -ptest_password -hlocalhost < queries.txt

Ключевым моментом, на который следует обратить внимание, является то, что вы должны указать ваш SQL-запрос в двойных кавычках. Если приведенный выше синтаксис команды вам не подходит, попробуйте заключить SQL-запрос в одинарные кавычки.

Также, если ваш SQL-запрос сам содержит двойные кавычки, то их нужно убрать, добавив перед ними обратную косую черту.

Результат вышеприведенного запроса будет выведен на консоль. Если вы хотите сохранить этот результат в другом файле, вам нужно будет использовать операторы перенаправления. Вот пример сохранения результата запроса MySQL в файл test.txt.


mysql -utest_user -ptest_password -hlocalhost -e "SELECT * FROM db.table;" > /home/user/test.txt

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


$ mysql -utest_user -ptest_password -hlocalhost -e "SELECT * FROM db.table;" | grep 'abc'

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

@sqlhub

🖥 Магия оптимизации SQL запросов

Думаю, каждый хоть раз использовал команду explain или хотя бы слышал про нее. Эта команда демонстрирует план выполнения запроса, но как именно СУБД приходит к нему остается загадкой. Да и как вообще СУБД понимает, что выбранный запрос оптимален? Неужели она проверяет все возможные варианты?

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

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

📌 Читать

@sqlhub

🖥 5 факторов, которые влияют на выбор базы данных

При выборе баз данных для текущего проекта (или при замене тех, которые не отвечают вашим текущим потребностям) количество возможных вариантов очень велико. Это и хорошо, и плохо, ведь нужны какие-то критерии фильтрации.

Сегодня есть гораздо больше баз данных, чем когда-либо. В декабре 2012 года, когда DB-Engines.com впервые начал ранжировать базы данных, у него получился список из 73 систем (существенный рост по сравнению с самым первым списком из 18 систем). Спустя десять лет, на декабрь 2022 года в списке было уже почти четыреста систем. За последнее десятилетие произошёл настоящий кембрийский взрыв технологий баз данных.

Нужно ориентироваться в обширном пространстве вариантов: SQL, NoSQL, множество «многомодельных» баз данных, которые могут быть сочетанием SQL и NoSQL, или множественные модели данных NoSQL (сочетающие две или более опций: документы, ключи-значения, широкие столбцы, графы и так далее).

Кроме того, пользователи не должны путать популярность с применимостью для них. Хотя сетевой эффект имеет свои преимущества («Все пользуются X, поэтому не ошибусь, если выберу её»), он также может привести к групповому мышлению, торможению инноваций и конкуренции.

▪ Читать статью

@sqlhub

🖥 Компактная таблица-шпаргалка по 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