Skip to content

Latest commit

 

History

History
1127 lines (790 loc) · 71.2 KB

SQL.md

File metadata and controls

1127 lines (790 loc) · 71.2 KB
Raw

Structured Query Language

Команды SQL

  • DDL - язык определения данных (Data Definition Language)
Команда Описание
CREATE Создать объект базы данных
ALTER Модифицировать существующий объект базы данных
DROP Удалить существующий объект базы данных
  • DML - язык изменения данных (Data Manipulation Language)
Команда Описание
SELECT Извлечь записи из таблицы
INSERT Создать записи
UPDATE Изменить записи
DELETE Удалить записи
  • DCL - язык управления данными (Data Control Language)
Команда Описание
GRANT Наделить пользователя правами
REVOKE Отменить права пользователя
  • TCL - язык управления транзакциями (Transaction Control Language)
Команда Описание
BEGIN Начало транзакции
COMMIT Подтверждение транзакции
ROLLBACK Откат транзакции
SAVEPOINT Создание точки сохранения
RELEASE SAVEPOINT Освобождение точки сохранения

Базовые конструкции SQL

*

Вывести все колонки из таблицы products:

SELECT * FROM products;
Подсчет количества строк в колонке

Вывести количество записей в таблице products:

SELECT COUNT(*) FROM products;
Несколько колонок

Вывести колонки product_name и unit_price из таблицы products:

SELECT product_name, unit_price FROM products;
Исключение дубликатов

Вывести данные без повторений из колонки unit_price таблицы products:

SELECT DISTINCT unit_price FROM products;
WHERE, OFFSET, LIMIT

Вывести product_name из products, где discontinued равен 0, записи с 5 по 10:

SELECT product_name FROM products WHERE discontinued = 0 OFFSET 5 LIMIT 10;
IN

Вывести те записи product_name из products, в которых reorder_level равен одному из значений, указанных в скобках:

SELECT product_name FROM products WHERE reorder_level IN (25, 15);
BEETWEEN ... AND ...

Вывести данные из колонок order_date и order_id из таблицы orders, где order_date находится в пределах от '1997-01-01' до '1998-01-01':

SELECT order_date, order_id FROM orders WHERE order_date BETWEEN '1997-01-01' AND '1998-01-01';
Выборка по совпадениям

Оператор LIKE используется для выполнения поиска по шаблону в текстовых данных. Он позволяет находить строки, соответствующие указанному шаблону, используя специальные подстановочные символы.

% Заменяет ноль или более символов; _ Заменяет ровно один символ.

Если шаблон не содержит % и _ , тогда шаблон представляет в точности строку и LIKE работает как оператор сравнения.

LIKE 'J_n' будет соответствовать всем именам, которые начинаются с J, за которыми следует любой один символ, а затем n. Результатом могут быть Jan, Jon, и т.д.

SELECT * FROM employees WHERE name LIKE 'J_n';

Вывести те данные из customer_id и company_name из таблицы customers, где в названии customers_id в середине присутствует символ 'A':

('%A' - если customer_id оканчивается на 'A', 'A%'- если customer_id начинается на 'A')

SELECT customer_id, company_name FROM customers WHERE customer_id LIKE '%A%';

Поиск строк, содержащих символ %

SELECT * FROM queries WHERE query LIKE '%\%%' ESCAPE '\';

Поиск имен, где второй символ любой и после третьего символа любые символы

SELECT * FROM employees WHERE name LIKE 'J_n%';

LIKE является регистрозависимым оператором. Для регистронезависимого поиска используется оператор ILIKE.

Переименование колонок

Вывести данные company_name, переименовав колонку в com_name:

SELECT company_name AS com_name FROM customers;
Максимальное значение

Вывести максимальное значение unit_price из таблицы order_details:

SELECT MAX(unit_price) FROM order_details;
Округление, среднее значение

Вывести округленное среднее число unit_price из таблицы order_details:

SELECT ROUND(AVG(unit_price)) FROM order_details;
Округление в меньшую сторону

Вывести число, округленное в меньшую сторону до ближайшего целого числа:

SELECT FLOOR(15.78);
Арифметика в запросе

Вывести округленное произведение значений колонок unit_price и quantity и поместить полученные значения в колонку, названную res_values:

SELECT ROUND(unit_price * quantity) AS res_value FROM order_details;
НЕ

Вывести значения order_id и discount из order_details, которые НЕ равны 0:

SELECT order_id, discount FROM order_details WHERE discount <> 0;
Обратный порядок

Вывести customer_id и order_date из таблицы orders, где значения отсортированы по колонке order_date В ОБРАТНОМ ПОРЯДКЕ:

SELECT customer_id, order_date FROM orders ORDER BY order_date DESC;
Группировка данных

Вывести общее количество записей колонки country из таблицы employees, сгруппировав результат по колонке country (количество каждой из country):

SELECT country, COUNT(*) FROM employees GROUP BY country;
Фильтрация результатов

Вывести category_id и сумму произведений unit_price и units_in_stock в таблице products, сгруппировав результаты по category_id (сколько суммарно вышло sum_price на каждый category_id) и отфильтровав результаты (вывести только те category_id), где SUM(unit_price * units_in_stock) больше 5000:

(работает в сочетании с GROUP BY)

SELECT category_id, SUM(unit_price * units_in_stock) AS sum_price
FROM products
GROUP BY category_id
HAVING SUM(unit_price * units_in_stock) > 5000;
Объединение

Вывести country из таблицы employees, country из таблицы customers и объединить результаты в одну колонку:

(UNION устраняет дубликаты, UNION ALL выводит результат с дубликатами)

SELECT country FROM employees
UNION
SELECT country FROM customers;
Пересечение

Вывести country, совпадающие для customers и suppliers:

SELECT country FROM customers
INTERSECT
SELECT country FROM suppliers;
Исключение

Вывести country из таблицы customers, которых нет в country таблицы suppliers:

SELECT country FROM customers
EXCEPT
SELECT country FROM suppliers;
Соединение

INNER JOIN Вывести product_name и units_in_stock из products и company_name из suppliers, левая таблица - products, правая - suppliers, соединить их по колонке supplier_id, одинаковой для обеих таблиц:

(для таблицы products колонка supplier_id является внешним ключом)

Соединение таблиц происходит по внешнему ключу, далее выбираются колонки, которые необходимо вывести из обеих таблиц.

SELECT products.product_name, suppliers.company_name, products.units_in_stock
FROM products
INNER JOIN suppliers ON products.supplier_id = suppliers.supplier_id;

LEFT / RIGHT JOIN Вывести количество записей из таблицы employees и объединить с таблицей orders по внешнему ключу employee_id:

(LEFT JOIN выведет все данные из левой таблицы и только совпадающие из правой. RIGHT JOIN работает обратным образом)

SELECT COUNT(*)
FROM employees
LEFT JOIN orders ON orders.employee_id = employees.employee_id;
Синтаксические конструкции USING и NATURAL при соединениях

USING берет наименование колонки, одинаковое для двух таблиц, по которой идет соединение.

NATURAL соединение происходит по всем одинаково проименованным колонкам.

INNER JOIN suppliers ON products.supplier_id = suppliers.supplier_id;

-- или

JOIN suppliers USING(supplier_id)

-- или

NATURAL JOIN suppliers
Подзапросы

Вывести те company_name из suppliers, country которых есть среди country из таблицы customers:

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

SELECT company_name
FROM suppliers
WHERE country IN (SELECT country
                FROM customers)

WHERE EXISTS Вывести company_name и contact_name из customers, если соблюдено условие в подзапросе (Вывести customer_id из orders, где customer_id в таблицах orders и customers совпадает, и где freight находится в заданном диапазоне):

(EXISTS (или NOT EXISTS) возвращает True или False. Если True, то внешний запрос выводит выборку данных, если False то данных нет)

SELECT company_name, contact_name
FROM customers
WHERE EXISTS (SELECT customer_id
            FROM orders
            WHERE customer_id = customers.customer_id
            AND freight BETWEEN 50 AND 100)

ANY / ALL Вывести company_name из customers, где customer_id равен НЕКОТОРЫМ из выборки в подзапросе (вывести customer_id из orders, соединив таблицы orders и order_details по order_id, где quantity больше 40):

(ANY - некоторые, ALL - все)

SELECT company_name
FROM customers
WHERE customer_id = ANY(SELECT customer_id
                        FROM orders
                        JOIN order_details USING(order_id)
                        WHERE quantity > 40)

DDL (Data Difinition Language)

CREATE TABLE table_name создать таблицу

ALTER TABLE table_name изменить таблицу * ADD COLUMN column_name data_type изменить колонку (с типом данных) * RENAME TO new_table_name переименовать таблицу * RENAME old_column_name TO new_column_name переименовать колонку * ALTER COLUMN column_name SET DAtA TYPE data_type задать новый тип данных для колонки

DROP TABLE table_name удалить таблицу

TRUNCATE TABLE table_name очистить таблицу от данных

DROP COLUMN column_name удалить колонку

Изменить таблицу и добавить в нее внешний ключ:

(ключевое слово ONLY прямо указывает, что изменения относятся только к указанной таблице)

ALTER TABLE ONLY orders
    ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers;

Изменить таблицу и назначить для атрибута PRIMARY KEY:

ALTER TABLE ONLY categories
    ADD CONSTRAINT pk_categories PRIMARY KEY (category_id);

Изменить таблицу, добавить условие CHECK для атрибута:

ALTER TABLE product
    ADD COLUMN price DECIMAL CONSTRAINT CHK_product_price CHECK (price >= 0);
Автоинкремент

В PostgreSQL более ранних версий, чем 9 в качестве автоинкремента используется тип SERIAL. В последних версиях наиболее предпочтительна следующая конструкция:

CREATE TABLE new_table
(
    column_id INT GENERATED ALWAYS AS IDENTITY (START WITH 0 INCREMENT BY 1) NOT NULL
);
UPDATE

Изменить в таблице table_name в колонке column_name атрибут с id 5:

UPDATE table_name
SET column_name = 'update_name'
WHERE id = 5;
DELETE

Удалить из таблицы table_name данные, где rating < 50:

DELETE FROM table_name
WHERE rating < 50;
RETURNING

Оператор RETURNING используется для получения значений после выполнения операции INSERT, UPDATE или DELETE. Он позволяет вернуть значения измененных столбцов или вычисленные значения после выполнения операции.

Например, если нужно вставить новую хапись в таблицу и получить значение автоматически сгенерированного идентификатора (например, ID), можно использовать оператор RETURNING:

INSERT INTO employees (name, department)
VALUES ('John Doe', 'HR')
RETURNING id;

В данном примере после выполнения оператора INSERT в таблицу employees будет вставлена новая запись с именем "John Doe" и отделом "HR". Оператор RETURNING вернет сгенерированный идентификатор (ID) этой записи.

Оператор RETURNING также может быть полезен при выполнении операций обновления (UPDATE) или удаления (DELETE), позволяя получить значения измененных столбцов или выполнить дополнительные действия на основе этих значений.

INSERT ON CONFLICT

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

Наиболее часто используется в следующих случаях:

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

Пример использования INSERT ON CONFLICT:

Если строка с id=1 уже существует в таблице, то будут обновлены значения столбца name на 'Jane'.

INSERT INTO table_name (id, name)
VALUES (1, 'John')
ON CONFLICT (id) DO UPDATE SET name = 'Jane';

Временные таблицы

Временные таблицы в SQL - это таблицы, которые создаются и существуют только в течение текущей сессии или до конца выполнения запроса. Они могут быть созданы с помощью ключевого слова CREATE TEMPORARY TABLE или просто CREATE TABLE с указанием ключевого слова TEMPORARY.

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

CREATE TEMPORARY TABLE temp_table (
    id INT,
    name VARCHAR(50),
    age INT
);

INSERT INTO temp_table (id, name, age) VALUES (1, 'Alice', 25);
INSERT INTO temp_table (id, name, age) VALUES (2, 'Bob', 30);

SELECT * FROM temp_table;

DROP TABLE temp_table;

В данном примере создается временная таблица temp_table с тремя колонками (id, name, age), затем в нее добавляются две записи. После этого выполняется запрос на выбор всех данных из временной таблицы temp_table. В конце сеанса сессии временная таблица будет автоматически удалена с помощью команды DROP TABLE.

Представление (VIEW)

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

Представления используются для упрощения запросов к данным. Они позволяют пользователю или приложению работать с данными, не зная сложности запросов или структуры базы данных.

Преимущества использования представлений:

  1. Упрощение сложных запросов: Представления позволяют объединять и фильтровать данные из разных таблиц в один запрос, тем самым упрощая запросы для конечных пользователей.
  2. Обеспечение безопасности данных: Представления могут служить фильтрами, ограничивающим доступ к определенным данным или столбцам.
  3. Повторное использование запросов: Представления могут быть переиспользованы в различных частях приложения или запросах.
  4. Упрощение разработки и обслуживания: Представления позволяют разработчикам абстрагироваться от сложности базы данных и сосредоточиться на запросах и бизнес-логике.

Чтобы создать представление, используется оператор CREATE VIEW, который определяет имя представления, выбирает столбцы и условия для фильтрации данных. Затем представление может быть использовано в запросах, как обычная таблица.

Пример создания представления:

CREATE VIEW myview AS
SELECT column1, column2
FROM table_name;

После создания представления можно использовать его в запросах:

SELECT * FROM myview;

Ограничения, налагаемые на VIEW:

  • Можно только добавлять новые колонки,
    • нельзя удалить существующие,
    • нельзя переименовывать колонки,
    • нельзя менять порядок следования колонок
  • Можно переименовывать сами VIEW

Чтобы была возможность вносить изменения во VIEW, вместо CREATE VIEW нужно прописывать CREATE OR REPLACE VIEW.

Условия для модификации данных через VIEW:

  • Во VIEW используется только одна таблица в секции FROM,
  • Во VIEW не используется DISTINCT, GROUP BY, HAVING, UNION, INTERSECT, EXCEPT, LIMIT
  • Во VIEW не используются оконные функции MIN, MAX, SUM, COUNT, AVG

MATERIALIZED VIEW

MATERIALIZED VIEW в SQL - это предварительно вычисленное представление данных, которое хранится на диске как обычная таблица. Оно может использоваться для ускорения процесса выполнения запросов, так как данные в materialized view уже предварительно вычислены и индексированы.

MATERIALIZED VIEW могут быть полезны в следующих случаях:

  • Когда требуется часто выполнять тяжелые запросы, которые замедляют работу системы. MATERIALIZED VIEW позволяет сохранить результат запроса и обновлять его только при необходимости.
  • При наличии больших объемов данных, которые часто запрашиваются, чтобы уменьшить время выполнения запросов.
  • При необходимости предварительно вычисленных агрегатов для отчетов или аналитики.

Подсчета общего количества заказов для каждого клиента:

CREATE MATERIALIZED VIEW customer_order_count AS
SELECT customer_id, COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_id;

Обновление данных:

REFRESH MATERIALIZED VIEW customer_order_count;

Использование в запросе:

SELECT *
FROM customer_order_count
WHERE customer_id = 123;

В данном примере customer_order_count хранит общее количество заказов для каждого клиента. При необходимости данные в MATERIALIZED VIEW можно обновить с помощью команды REFRESH, чтобы отразить последние изменения в базе данных.

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

Основные различия:

  1. Хранение данных:

    • MATERIALIZED VIEW хранит реальные данные на диске, что позволяет уменьшить время выполнения запросов за счет предварительного вычисления и индексирования данных.
    • VIEW не хранит данные физически, он просто сохраняет определение запроса, и каждый раз запрос будет выполняться заново при обращении к нему.

  2. Операции обновления:

    • Данные в MATERIALIZED VIEW может быть обновлен вручную или автоматически с использованием триггеров или планировщиков задач.
    • Обычный VIEW всегда дает актуальные данные из базовых таблиц, поскольку запрос выполняется динамически при обращении.

  3. Затраты на обновление данных:

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

Таким образом, MATERIALIZED VIEW обычно используются для оптимизации производительности системы путем предварительного вычисления и хранения данных, в то время как обычные VIEW обеспечивают динамическое представление данных из базовых таблиц.

CASE / WHEN

Вывести product_name, unit_price, unit_in_stock из таблицы products, и в зависимости от поставленных условий, в отдельную колонку amount выводить текстовые сообщения 'lots of', 'average' и 'low number':

SELECT product_name, unit_price, unit_in_stock
    CASE WHEN unit_in_stock >= 100 THEN 'lots of'
         WHEN unit_in_stock > 50 AND unit_in_stock < 100 THEN 'average'
         WHEN unit_in_stock < 50 THEN 'low number'
         ELSE 'unknown'
    END AS amount
FROM products;

COALESCE

Вывести order_id, order_date и обработанную колонку ship_region таким образом, что будут отображены значения не NULL, а значения NULL будут заменены строкой 'нет данных';

SELECT order_id, order_date, COALESCE(ship_region, 'нет данных') AS ship_region
FROM orders;

Массивы

Массивы - это структуры данных, которые позволяют хранить несколько значений в одной переменной. Они могут быть использованы для хранения списков, наборов объектов или других типов данных.

В SQL массивы могут быть определены с помощью квадратных скобок []. Например:

CREATE TABLE my_table (id INT, my_array INT[]);

Затем можно вставить значения в массив следующим образом:

INSERT INTO my_table (id, my_array) VALUES (1, '{1,2,3}');

Для доступа к элементам массива использестся индексация - my_array[1] вернет первый элемент массива.

В PL/pgSQL, можно создать массивы с помощью оператора ARRAY []. Например, my_array := ARRAY[1,2,3]. Для доступа к элементам массива используется оператор []. Например, my_array[1] вернет первый элемент массива.

Массивы могут быть использованы в SQL и PL/pgSQL для хранения списков значений, фиксированных наборов объектов или для выполнения операций над наборами данных. Они удобны для работы с наборами данных, например, если нужно хранить список id пользователей, список заказов или другие списки данных.

Основные операции над массивами в SQL и PL/pgSQL включают добавление или удаление элементов, доступ к элементам массива, выполнение операций над всеми элементами массива и преобразование массивов в другие типы данных.

В целом, массивы в SQL и PL/pgSQL предоставляют удобный способ хранения и обработки списков данных. Они могут быть использованы для различных целей, включая хранение списков, фильтрацию данных, выполнение операций над множествами и многое другое.

Функции

  • SQL-функции
  • Процедурные (pl/pgSQL функции)
  • Серверные функции (написанные на C)
  • Собственные C-функции

Простая скалярная функция

-- Синтаксис функции
CREATE OR REPLACE FUNCTION total_price() RETURNS DOUBLE PRECISION AS $$
	SELECT SUM(unit_price * units_in_stock) AS total
	FROM products
$$ LANGUAGE SQL;

-- Вызов функции
SELECT total_price() AS total_price_products;

Функция с аргументами

CREATE OR REPLACE FUNCTION get_product_price_by_name(prod_name VARCHAR) RETURNS DOUBLE PRECISION AS $$
	SELECT unit_price
	FROM products
	WHERE product_name = prod_name
$$ LANGUAGE SQL;

Функция без имени аргумента

CREATE OR REPLACE FUNCTION get_hello(TEXT) RETURNS TEXT AS $$
    SELECT 'Hello' || $1 || '!';     -- номер вместо имени
$$ LANGUAGE SQL;

SELECT get_hello('world');

Метки "чистоты" функций:

  1. IMMUTABLE: Функция всегда возвращает одинаковый результат, если ей передаются одинаковые аргументы, и результат не зависит от состояния базы данных.

  2. STABLE: Функция всегда возвращает одинаковый результат при одинаковых входных параметрах, но может вернуть разные результаты при разных состояниях базы данных.

  3. VOLATILE: Функция может вернуть разные результаты для одинаковых аргументов, так как результат зависит от состояния базы данных или внешних вызовов.

  4. SECURITY DEFINER: Функция или процедура будет выполняться с привилегиями пользователя, создавшего ее, а не с привилегиями пользователя, который выполняет эту функцию или процедуру.

  5. Составные: Комбинация вышеупомянутых значений, например, STABLE в зависимости от аргументов и IMMUTABLE при равных аргументах.

Эти метки "чистоты" помогают оптимизатору запросов принимать решения о порядке выполнения функций и кэшировании результатов, так как он может предполагать стабильность результата для функций с соответствующими метками.

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

Оконные функции

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

SELECT column1, column2, aggregate_function(column3) 
OVER (PARTITION BY column4 ORDER BY column5)
FROM table_name;

Где:

  • column1, column2 - столбцы, которые нужно вернуть в результате запроса
  • aggregate_function - агрегатная функция, которую нужно применить к окну данных
  • column3 - столбец, к которому будет применена агрегатная функция
  • PARTITION BY - определяет, как разделить данные на группы для вычисления агрегатов
  • ORDER BY - определяет порядок строк внутри каждой группы

Допустим, у нас есть таблица "sales" с данными о продажах:

CREATE TABLE sales 
(
    id serial PRIMARY KEY,
    product_name VARCHAR(50),
    sale_date DATE,
    amount INTEGER
);

INSERT INTO sales (product_name, sale_date, amount) VALUES 
('Product A', '2022-01-01', 100),
('Product B', '2022-01-02', 200),
('Product B', '2022-01-07', 300),
('Product A', '2022-01-03', 120);

Теперь нужно найти суммарное количество продаж по каждому продукту на каждую дату:

SELECT 
    product_name, 
    sale_date, 
    amount,
    SUM(amount) OVER (PARTITION BY product_name ORDER BY sale_date) AS total_sales
FROM sales
ORDER BY product_name, sale_date;

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

Процедуры

Хранимые процедуры в SQL — это подготовленные и написанные на SQL операции, которые можно сохранить в базе данных для последующего вызова и повторного использования. Они позволяют группировать несколько операторов SQL вместе и создавать логику обработки данных.

Преимущества использования хранимых процедур:

  1. Использование хранимых процедур снижает объем передаваемого по сети трафика, так как запрос к процедуре отправляется один раз, а не в виде множества SQL запросов.
  2. Повышение производительности, так как хранимые процедуры могут выполняться быстрее, чем эквивалентные им запросы на стороне клиента.
  3. Увеличение безопасности, так как хранимые процедуры могут контролировать доступ к данным и обеспечивать более строгую проверку прав доступа.

Процедуры и функции в SQL имеют ряд существенных различий. Вот основные отличия между ними:

  1. Возвращаемое значение:

    • Функции обязаны вернуть значение, тогда как процедуры не обязаны возвращать результат.

  2. Использование в запросах:

    • Функции могут использоваться внутри SQL запросов, также как и обычные столбцы, в то время как процедуры вызываются как отдельные блоки программного кода.

  3. Транзакции:

    • Вызов функции в рамках SQL запроса выполняется в рамках той же транзакции, в то время как процедуры могут выполняться в собственной транзакции.

  4. Доступные операции:

    • Функции могут выполнять операции записи данных, но обычно используются для чтения и возврата результатов.
    • Процедуры могут включать в себя операции изменения данных (INSERT, UPDATE, DELETE) и они не обязаны возвращать результат.

  5. Параметры:

    • Как процедуры, так и функции могут принимать параметры.

  6. Использование RETURN:

    • Функции используют оператор RETURN, чтобы вернуть результат.
    • Процедуры не используют RETURN для возврата результата.

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

Пример:

CREATE TABLE t(TEXT);

CREATE PROCEDURE any_action(row INTEGER) AS $$
    TRUNCATE t;
    INSERT INTO t SELECT random() FROM generate_series(1, row);
$$ LANGUAGE SQL;

CALL any_action(row => 1000);

Индексы

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

В PostgreSQL существует несколько типов индексов:

  1. B-дерево (B-tree) - это самый распространенный тип индекса в PostgreSQL. B-tree индексы - это специальные структуры данных, которые помогают ускорить поиск и сортировку данных в базе данных. Когда создается B-tree индекс для столбца в таблице, PostgreSQL создает дерево, где каждый узел содержит ключи (значения) из этого столбца, а также ссылки на другие узлы.

Когда выполняется запрос на поиск данных по индексированному столбцу, PostgreSQL использует этот B-tree индекс для быстрого определения, где находятся нужные данные. Вместо того чтобы просматривать все строки в таблице, база данных идет по узлам дерева, уменьшая количество операций поиска.

  • Создается по-умолчанию;

    CREATE INDEX idx_time ON btcusdt (curr_time);

    • Поддерживает операции >, <, <=, >=, =;

    • Поддерживает LIKE 'abc%' (но не '%abc');

    • Индексирует NULL;

    • Сложность поиска O(logN);

  1. Хэш-индекс (Hash index) - используется для эффективного поиска точных значений ключа. Он создается на основе хэш-функции, что позволяет быстро найти соответствующую запись.

    CREATE INDEX idx_time ON btcusdt USING HASH (curr_time);

    • Поддерживает только операцию '=';

    • Не отражается в журнале предзиписи (WALL);

    • Сложность поиска O(1) (мгновенно);

  2. GIN (Generalized Inverted Index) - используется для полнотекстового поиска и поиска с использованием массивов и других сложных типов данных.

  3. GiST (Generalized Search Tree) - обеспечивает поддержку различных типов поиска, таких как географический, полнотекстовый, поиск с учетом расстояния между объектами и многих других.

  4. SP-GiST (Space-Partitioned GiST) - обеспечивает эффективный поиск и пространственную индексацию для ключей, связанных с пространственными объектами.

  5. BRIN (Block Range INdex) - позволяет быстро и компактно находить блоки записей, которые удовлетворяют условию поиска в больших таблицах.

Для создания индекса используется команда CREATE INDEX. Например, чтобы создать B-дерево индекс на столбцах "id" и "name" в таблице "users", можно использовать следующий SQL-запрос:

CREATE INDEX idx_users_name ON users(name);

Такая запись является сокращением от следующего варианта создания индекса:

CREATE INDEX idx_users_name ON users
USING btree -- метод доступа
(
    id int4_ops, -- класс операторов для integer
    name  text_ops  -- класс операторов по умолчанию для text
);

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

Таким образом, при использовании CREATE UNIQUE INDEX нельзя добавить в индексированное поле две строки с одинаковым значением, а при использовании CREATE INDEX такое дублирование допустимо.

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

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

REINDEX

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

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

Команда REINDEX не является стандартом SQL, а является частью добавления к стандарту, которое поддерживается не всеми СУБД. Например, PostgreSQL поддерживает команду REINDEX для работы с индексами.

Перестройка индекса index_name

REINDEX INDEX index_name;

При обновлении версии сервера с помощью pg_upgrade необходимо выполнить REINDEX для существующих индексов, чтобы задействовать для них возможность устранения дубликатов.

WITH

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

WITH состоит из нескольких частей:

  1. Начало с ключевого слова WITH,
  2. Имя временной таблицы,
  3. Определение столбцов временной таблицы (если необходимо),
  4. Оператор AS, который указывает начало определения временной таблицы,
  5. Запрос, который извлекает данные для временной таблицы.

Пример использования WITH в SQL:

WITH sales AS (
    SELECT product_id, SUM(amount) AS total_sales
    FROM order_items
    GROUP BY product_id
)

SELECT products.product_name, sales.total_sales
FROM products
JOIN sales ON products.product_id = sales.product_id;

В этом примере создается временная таблица sales, которая содержит суммарные продажи для каждого продукта из таблицы order_items. Затем эта временная таблица объединяется с таблицей products, чтобы показать продукты и их общие продажи.

Подзапросы CTE могут быть материализованы. Материализацией управляет планировщик, но такое поведение можно гарантировать, указав ключевое слово MATERIALIZED. В этом случае подзапрос не раскрывается, его результат вычисляется и помещается либо в оперативную память (в пределах work_mem), либо сбрасывается во временный файл.

WITH b AS MATERIALIZED (
  SELECT * FROM bookings
)
SELECT * FROM b
WHERE b.book_ref = '000112';

PREPARE

Оператор PREPARE используется для подготовки и кэширования запросов до их выполнения. Это позволяет уменьшить накладные расходы на обработку запроса и повторно использовать его в дальнейшем для улучшения производительности. Оператор PREPARE является расширением PostgreSQL, она отсутствует в стандарте.

Процесс использования оператора PREPARE:

  1. Сначала подготавливается запрос с помощью оператора PREPARE, указывается имя для подготовленного запроса и сам запрос.
  2. Затем выполняется этот подготовленный запрос с помощью оператора EXECUTE.
  3. Для очистки или удаления подготовленного запроса можно использовать оператор DEALLOCATE.

Пример использования оператора PREPARE:

PREPARE my_statement (INT) AS
SELECT * FROM products WHERE product_id = $1;

EXECUTE my_statement(10);

В результате этого кода будет выполнен запрос, который выберет все столбцы из таблицы products для записей, где product_id равен 10.

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

В SQL запросе, $1 означает параметр, который будет передан в запрос во время выполнения. Такой подход позволяет делать запросы более универсальными и безопасными, так как параметры могут быть динамически вставлены в запрос без опасности SQL-инъекций.

При использовании PREPARE можно создать параметризованный запрос, в котором вместо конкретного значения подставляется переменная:

SELECT * FROM products
WHERE product_id = $1 AND price > $2;

В данном случае "$1" и "$2" - это параметры, которые должны быть переданы при выполнении запроса. Если выполнить подготовку запроса с помощью PREPARE, то при его выполнении вместо "$1" и "$2" будут подставлены соответствующие значения. Например, чтобы выполнить запрос для product_id=100 и цены больше 50, нужно выполнить что-то вроде:

EXECUTE my_prepared_query(100, 50);

Таким образом, "$1", "$2" и т.д. обозначают параметры и предполагают использование подстановки значений при выполнении запроса. Это одно из преимуществ подготовленных операторов — невозможность внедрения SQL-кода.

Курсор

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

DECLARE c CURSOR FOR SELECT * FROM t ORDER BY id;

FETCH c;


 id |  s  
----+-----
  1 | foo
(1 row)


FETCH 2 c;
 id |  s  
----+-----
  2 | bar
  3 | baz
(2 rows)

Составные типы

Составные типы в SQL позволяют создавать пользовательские типы данных, которые могут быть представлены как структурные типы, содержащие несколько атрибутов или полей. Такие типы могут быть используемы в качестве аргументов функций, переменных и полях таблиц.

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

CREATE TYPE address AS (
    street VARCHAR(50),
    city VARCHAR(50),
    zip_code VARCHAR(10)
);

CREATE TABLE employees
(
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(50),
    home_address address
);

INSERT INTO employees(name, home_address) 
VALUES('John Doe', ROW('123 Main St', 'Anytown', '12345'));

В этом примере address - это составной тип, содержащий три поля street, city и zip_code. Затем этот тип используется как атрибут структуры в таблице employees.

DOMAIN

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

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

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

Создание домена phone_number, который имеет тип данных TEXT и проверяет, что значение соответствует шаблону телефонного номера:

CREATE DOMAIN phone_number AS TEXT
   CHECK (VALUE ~ '^[0-9]{3}-[0-9]{3}-[0-9]{4}$')

Партицирование

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

Факторы, влияющие на потребность в партиционировании таблиц:

  • Объем данных и производительность: Партиционирование может улучшить производительность запросов к таблице, особенно если в таблице содержится большое количество данных. Даже при относительно небольших размерах (например, несколько десятков миллиардов записей), разделение данных на партиции может существенно ускорить выполнение запросов за счет уменьшения объема данных, которые необходимо сканировать.

  • Управление данными: Разбиение таблицы на партиции облегчает управление данными, особенно если нужно часто добавлять или удалять данные. Также это позволяет ускорить операции обслуживания и администрирования базы данных.

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

В целом, разумное партиционирование таблицы может принести выигрыш в производительности и обслуживаемости уже при значительно меньших объемах данных, чем 100 миллиардов записей.

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

CREATE TABLE sales 
(
    sale_id SERIAL PRIMARY KEY,
    sale_date DATE,
    sale_amount NUMERIC
);

CREATE TABLE sales_2019 PARTITION OF sales
FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');

CREATE TABLE sales_2020 PARTITION OF sales
FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');

Наследование

Для создания наследования таблиц используется оператор INHERIT. Наследование в PostgreSQL позволяет создавать иерархию таблиц, где дочерние таблицы могут наследовать структуру (колонки) и свойства (индексы, ограничения) родительской таблицы.

Оператор INHERIT применяется в случаях, когда нужно создать несколько таблиц с общей структурой данных, но с независимым хранением этих данных. Например, у есть таблица employee, и нужно создать таблицы developer и manager, которые будут содержать все колонки таблицы employee и иметь свои особенные колонки.

Родительская таблица employee:

CREATE TABLE employee (
    id SERIAL PRIMARY KEY,
   name VARCHAR(50),
    role VARCHAR(20)
);

Дочерняя таблицу developer, которая наследует структуру таблицы employee:

CREATE TABLE developer (
    skill_level VARCHAR(20)
) INHERITS (employee);

Дочерняя таблица manager, также наследующая структуру таблицы employee:

CREATE TABLE manager (
    department VARCHAR(30)
) INHERITS (employee);

Таким образом, таблицы developer и manager наследуют структуру таблицы employee, а также все индексы, ограничения и другие свойства. При добавлении данных в таблицы developer или manager, данные будут храниться независимо, но при выполнении запросов можно использовать наследование для упрощения запросов, например, поиск всех сотрудников независимо от их роли.

UNNEST

Функция UNNEST в SQL используется для развертывания массивов или списков (nested arrays или nested lists) в отдельные строки данных. Это полезно, когда данные хранятся в массивах и требуется разбить их на отдельные значения для дальнейшей обработки.

Предположим, у есть таблица "orders" с колонкой "items", в которой хранятся массивы товаров, заказанных клиентами:

CREATE TABLE orders (
    order_id INT,
    items VARCHAR[]
);

INSERT INTO orders (order_id, items) VALUES
(1, ARRAY['apple', 'banana', 'orange']),
(2, ARRAY['milk', 'bread']);

Чтобы развернуть массивы в отдельные строки, можно использовать функцию UNNEST в запросе:

SELECT order_id, unnest(items) AS item
FROM orders;

Результат будет следующим:

order_id | item

1 | apple 1 | banana 1 | orange 2 | milk 2 | bread

Таким образом, функция UNNEST позволяет разбить массивы на отдельные элементы и использовать их для дальнейшей обработки или анализа данных.

Приведение типов

Приведение типов или преобразование типов в PostgreSQL - это процесс изменения типа данных одного объекта данных на другой тип данных. В PostgreSQL это можно сделать с помощью оператора :: или функции приведения типов CAST(). Приведение типов может потребоваться, когда необходимо выполнить операцию над данными разных типов, или когда требуется сохранить данные в определенном формате.

  1. :: Преобразование строки '123' в целочисленное значение.
SELECT '123'::integer;
  1. CAST Может использоваться в различных случаях, например, для:
  • Преобразования строкового значения в числовой тип данных (например, INT, FLOAT).
  • Преобразования значений даты и времени в другой формат.
  • Преобразования значения в строковый тип данных для сравнения или конкатенации.

Преобразование строки '10' в целочисленное значение:

SELECT CAST('10' AS INT) AS number;

EXTRACT

Оператор EXTRACT используется для извлечения части даты или времени из значения даты или времени. Он позволяет получить год, месяц, день, час, минуту, секунду и другие компоненты из временного типа данных.

Часто оператор EXTRACT используется при написании запросов SQL для анализа данных даты и времени, чтобы извлечь определенные компоненты даты или времени и использовать их в условиях, фильтрах или вычислениях.

Предположим, есть таблица orders, содержащая информацию о заказах, включая дату и время создания каждого заказа. Нужно написать запрос, который выведет количество заказов, созданных в каждом месяце за последний год. Можно использовать оператор EXTRACT для этой задачи.

SELECT
    EXTRACT(MONTH FROM order_date) AS month,
    COUNT(*) AS num_orders
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY month
ORDER BY month;

В данном примере используется EXTRACT(MONTH FROM order_date) для извлечения номера месяца из столбца order_date. Затем считается количество заказов в каждом месяце с помощью COUNT(*), фильтруя данные только за последний год с использованием WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'.

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

TO_TIMESTAMP

Функция TO_TIMESTAMP используется для преобразования строкового значения даты или времени в тип данных TIMESTAMP. Она принимает два аргумента - строку с датой или временем и формат этой строки.

SELECT TO_TIMESTAMP('2022-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AS timestamp_value
FROM dual;

Результат выполнения запроса будет:

timestamp_value

2022-01-15 14:30:00.000

Этот пример преобразует строку '2022-01-15 14:30:00' в тип данных TIMESTAMP, используя формат 'YYYY-MM-DD HH24:MI:SS'.

LPAD

LPAD - это функция, которая добавляет указанный символ или строку в начало другой строки до достижения указанной длины. Синтаксис: LPAD(строка, длина, заполнитель)

  • строка - это строка, к которой нужно добавить символ или строку в начало;
  • длина - длина итоговой строки после добавления символа или строки;
  • заполнитель - символ или строка, которую необходимо добавить в начало строки.
SELECT LPAD('123', 5, '0') AS padded_string;

                    padded_string
----------------------------------------------------
00123

В этом примере, функция LPAD добавляет символ '0' в начало строки '123' до достижения общей длины в 5 символов.

FILTER

Оператор FILTER используется для фильтрации данных на основе определенного условия. Он обычно используется в комбинации с агрегатными функциями (например, SUM, COUNT, AVG) для вычисления значений только для определенных строк.

Вычисление среднего значения только для строк, где значение колонки 'status' равно 'completed':

SELECT AVG(salary) FILTER (WHERE status = 'completed') AS avg_salary_completed
FROM employees;

В этом примере оператор FILTER фильтрует строки по условию (status = 'completed'), и затем вычисляет среднее значение столбца 'salary' только для отфильтрованных строк.