SQL

• https://harlequin.sh/ – вроде, симпатичная питонячная штука для общения с базами.
• https://en.wikipedia.org/wiki/DBeaver - штука для общения с базами данных. В принципе, их там семейство, штук, мож лучше не ее, а что-то другое.
• litecli - pgcli is a command line interface for SQlite with auto-completion and syntax highlighting. It is also capable of pretty printing tabular data.Домашняя страница: https://litecli.com
• sqlite
  • sqlite3-pcre
    This SQLite loadable extension enables the REGEXP operator, which is not implemented by default, to call PCRE routines for regular expression matching. Домашняя страница: http://git.altlinux.org/people/at/packages/?p=sqlite3-pcre.git
  • sqlitebrowser - SQLite Database Browser — графическая программа с интерфейсом Qt для создания и редактирования баз данных SQLite. С её помощью пользователи и разработчики могут просматривать, редактировать и администрировать базы данных в знакомом табличном интерфейсе без необходимости знания команд SQL. Домашняя страница: http://sqlitebrowser.org/. Даже использовала.

• SELECT не только выбирает что-то из таблиц, но и выводит строку (в одинарных кавычках в запросе) либо результат вычисления.
• А чтоб выбирал из таблиц, это SELECT что FROM таблица. Например, SELECT * FROM tablename – выбрать всё из таблицы tablename.
• SELECT COUNT(*) FROM tablename – это уже посчитать, сколько этих *, патамушта COUNT(*) - посчитать.
• SELECT columnname1, columnname2, columnname3 FROM tablename; выведет все записи, но в них только эти столбцы, причём в указанном порядке.

• LIMIT 10 (10 записей)
• OFFSET 5 (начиная с шестой, то ись пять пропущены).

WHERE поле > 4; -- условие для среза данных

В хвост запроса. Задать условия можно с помощью операторов сравнения:

• = — равно;
• != — не равно;
• > — больше;
• < — меньше;
• >= — больше или равно;
• <= — меньше или равно.

если значение сравнивают с символьным типом, набор символов нужно взять в одинарные кавычки: WHERE поле = 'Иванов'. Это правило также касается даты и времени: WHERE поле = '2013-07-01'. Поле, по которому формируется условие, может отсутствовать в SELECT. В PostgreSQL в операторе WHERE нельзя использовать псевдонимы. Потому что «под капотом» во время компиляции WHERE выполняется раньше, чем SELECT, когда псевдонимы ещё не назначены.

Функция DATE_TRUNC Дата и время часто хранятся в таком виде: '2009-11-19 11:03:05'. Но сравнивать даты в таком формате неудобно, если нужен, например, только год. В таких случаях в PostegreSQL используют функцию DATE_TRUNC, которая «усекает» дату и время до необходимого значения. Синтаксис функции такой: DATE_TRUNC('отрезок времени', поле). Отрезок времени может быть разным, главное — не забыть одинарные кавычки:

'microseconds' — микросекунды; 'milliseconds' — миллисекунды; 'second' — секунда; 'minute' — минута; 'hour' — час; 'day' — день; 'week' — неделя; 'month' — месяц; 'quarter' — квартал; 'year' — год; 'decade' — десятилетие; 'century' — век.

SELECT date,
       DATE_TRUNC('month', date)
FROM hotdog
LIMIT 5;

При использовании оператора DATE_TRUNC PostgreSQL возвращает данные, которые имеют тип timestamp with time zone. Этот тип данных похож на date, только вдобавок содержит время. Кроме того, такой тип производит поправку на временной пояс, в котором находится компьютер пользователя. Пример: вы усекаете данные до месяца из даты 2023-03-04. После использования DATE_TRUNC вы получите следующий результат: 2023-03-01 00.00.00.

При формировании типа timestamp к данным добавилось нулевое время, поскольку этот тип обязан его содержать. Так как тип имеет расширение with time zone, система сравнит полученный результат с вашим временем. Если ваш часовой пояс отличается от нулевого в меньшую сторону, то дата может измениться: если отнять три часа от 2023-03-01 00.00.00, получится 2023-02-28, то есть последний день предыдущего месяца.

Чтобы получить только конкретную часть даты — год, месяц или минуту, — используют функцию EXTRACT. Её синтаксис: EXTRACT(отрезок времени FROM поле). Одинарные кавычки для значений внутри EXTRACT не нужны. Отрезок времени можно представить следующими значениями:

CENTURY — век; YEAR — год; QUARTER — квартал; MONTH — месяц; WEEK — неделя в году; DAY — день; DOY (от англ. day of the year) — день года, выраженный числом от 1 до 365 или 366, если год високосный; DOW (от англ. day of the week) — день недели, выраженный числом от 0 до 6, где понедельник — 1, воскресенье — 0. ISODOW (от англ. day of the week и ISO 8601) — день недели, выраженный числом от 1 до 7, где понедельник — 1, воскресенье — 7; HOUR — час; MINUTE — минута; SECOND — секунда; MILLISECOND — миллисекунда.

Выделим номер недели из поля с датой так же, как ранее мы делали с месяцем. На этот раз применим EXTRACT:

SELECT date, EXTRACT(WEEK FROM date) FROM hotdog LIMIT 5;

Теперь напротив каждой даты появилось число — но уже не дата, а номер недели. Давайте отфильтруем данные: оставим только те заказы, которые проходили на седьмой неделе.

SELECT order_id, bracelet_id, date, name_hotdog, ingredients FROM hotdog WHERE EXTRACT(WEEK FROM date) = 7 LIMIT 5;

SELECT last_name,
       percent_of_discount,
       percent_of_discount/100.0 AS discount
FROM buyer;
LIMIT 10;

Расчётное поле, которое вы создали, отображается в итоговой таблице, с ним можно проводить новые вычисления. Однако оно не сохраняется в базе данных.

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

Еще можно иногда заметить вот такой вариант деления:

SELECT 7::numeric/2

После делителя или делимого добавляют сочетание слов ::numeric. Так мы говорим программе, что это дробное число, и деление выполнится с остатком. Стандартные операторы для математических операций выглядят так:

• — сложение;
• — вычитание;
• — умножение;

/ — деление.

Что такое агрегирующие функции Агрегирующие функции позволяют проводить расчёты над группами данных и отображают результат в виде одной строки. Основные агрегирующие функции в SQL:

SUM(поле) возвращает сумму значений в поле; AVG(поле) находит среднее арифметическое для значений в поле; MIN(поле) возвращает минимальное значение в поле; МАХ(поле) возвращает максимальное значение в поле; COUNT(поле) выводит количество записей в поле.

Мы говорили про особенности деления целочисленных типов данных: в PostgreSQL такое деление проходит без остатка. Для агрегирующих функций это не важно — все операции проходят одинаково: и для целочисленных типов, и для дробных.

Ещё одна важная функция — это DISTINCT. Она позволяет находить и рассчитывать показатели только по уникальным значениям. Например, Света и Ира делали покупки в интернет-магазине. Света сделала 10 заказов, Ира — 15. Технически было совершено 25 заказов, и для каждого из них указан свой покупатель. Но уникальных покупателей — всего два. Так, имена Светы и Иры в заказах встречались 25 раз, но, исключив повторы с помощью функции DISTINCT, мы получим только двух покупательниц.

DISTINCT можно использовать как с другими агрегирующими функциями, так и без них. Отобразим на экране, сколько всего уникальных значений в поле percent_of_discount:

SELECT DISTINCT(percent_of_discount) FROM buyer;

Агрегирующие функции можно комбинировать с условиями. Например, нужно рассчитать средний возраст клиентов-мужчин в таблице buyer. Отфильтруем таблицу по полю gender и применим функцию AVG к полю age.

SELECT AVG(age) FROM buyer WHERE gender = 'Мужской';

Агрегирующие функции можно применять и вместе с вычислениями. Усложним задачу с подсчётом доли скидки — рассчитаем для неё среднее. Запрос будет выглядеть так:

SELECT AVG(percent_of_discont/100.0) FROM buyer;

Особенности запросов с GROUP BY

До сих пор мы указывали в SELECT поле, по которому производилась группировка, но это необязательно. Если его не указать, это поле просто не отобразится в итоговой таблице:

SELECT COUNT(name_hotdog) AS cnt_order,
       AVG(quantity) AS average_quantity,
       SUM(vegan_sausage) AS sum_vegan_sausage
FROM hotdog
GROUP BY name_hotdog;

Но если поле указано в SELECT, оно обязательно должно присутствовать и в группировке.

Вот такой код вернёт ошибку: поле date тоже нужно указать в группировке через запятую.

SELECT name_hotdog,
       date,
       AVG(price)
FROM hotdog
GROUP BY name_hotdog;

В группировках можно указывать псевдонимы.

Давайте подсчитаем среднюю цену товара для каждого месяца:

SELECT EXTRACT(MONTH FROM date) AS order_month,
       AVG(price)
FROM hotdog
GROUP BY order_month;

В группировке можно указывать несколько агрегирующих функций — или вообще все. Рассчитаем общее число заказов хот-догов, среднее количество хот-догов в заказе и сумму вегетарианских сосисок для каждого хот-дога. Название хот-дога — это категория, для которой будут рассчитаны агрегирующие функции. Чтобы не запутаться, назначим псевдонимы новым полям.

SELECT name_hotdog ,
       COUNT(name_hotdog) AS cnt_order,
       AVG(quantity) AS average_quantity,
       SUM(vegan_sausage) AS sum_vegan_sausage
FROM hotdog
GROUP BY name_hotdog;

Для таких случаев существует оператор HAVING. Он фильтрует значения, полученные в результате группировки — то есть работает не с новыми полями, а с результатом вычислений. Его записывают после GROUP BY.

Условие, по которому оператор HAVING фильтрует данные, необязательно указывать в SELECT. Например, нужно отобразить зоны парка и максимальный возраст клиентов, которые подключались в них, но исключить те, где средний возраст меньше 30 лет. При этом сам средний возраст можно не выводить на экран.

SELECT connection_area,
       MAX(age)
FROM buyerкоторому
WHERE company_marker = 0
GROUP BY connection_area
HAVING AVG(age) > 30;

Все операторы, с которыми вы знакомы, можно комбинировать с HAVING — но только при условии, что в запросе есть группировка GROUP BY. Без неё HAVING работать не будет.

ORDER BY Для сортировки данных в SQL используют оператор ORDER BY. Его пишут в самом конце запроса, после него можно указать только операторы LIMIT и OFFSET.

По умолчанию оператор ORDER BY сортирует данные от меньшего к большему. Чтобы изменить порядок сортировки, после названия поля указывают ключевое слово DESC: тогда данные будут отсортированы по убыванию. Если указать ASC, данные будут отсортированы по возрастанию — так же, как и по умолчанию.

SELECT connection_area,
       AVG(age)
FROM buyer
WHERE company_marker = 0
GROUP BY connection_area
ORDER BY AVG(age) DESC
LIMIT 5;

В запрос можно добавить псевдоним, чтобы не указывать агрегирующую функцию в запросе дважды:

SELECT connection_area,
       AVG(age) AS average_age
FROM buyer
WHERE company_marker = 0
GROUP BY connection_area
ORDER BY average_age DESC
LIMIT 5;

Комбинации сортировок, группировок и фильтраций — это прекрасный инструмент для создания рейтингов. Например, менеджер просит составить топ-3 самых продаваемых хот-догов. Решают эту задачу так:

Сначала агрегируем и группируем данные. Сортируем результат по убыванию. Ограничиваем выгрузку тремя строками.

Получаем запрос:

SELECT name_hotdog,
       COUNT(name_hotdog) AS cnt_hotdog
FROM hotdog
GROUP BY name_hotdog -- 1) группировка
ORDER BY cnt_hotdog DESC -- 2) сортировка
LIMIT 3; -- 3) ограничение

Сгруппируйте данные по месяцу, а потом по названию. Выручка — это произведение цены на количество.

SELECT EXTRACT(MONTH FROM date) AS order_month,
       name_hotdog,
       SUM (quantity) AS sum_quantity,
       price
FROM hotdog
GROUP BY order_month;
LIMIT 5