Это тестово-учебный проект "Финансовый учёт", выполненный на стеке "чистый PHP + MySQL". БД проекта соостоит из таблиц: Справочник организаций (org) и Список финансовых поступлений (pay)
--
-- Структура таблицы `org`
--
CREATE TABLE `org` (
`id` int(10) UNSIGNED NOT NULL, -- Уникальный идентификатор организации. AUTO_INCREMENT сам будет назначать ID.
`name` varchar(255) NOT NULL, -- Название организации. VARCHAR(255) - стандартный выбор для имен.
`parent_id` int(10) UNSIGNED DEFAULT NULL
-- ID родительской организации из этой же таблицы.
-- UNSIGNED, потому что ID не могут быть отрицательными.
-- DEFAULT NULL означает, что если при вставке не указать это поле,
-- оно автоматически станет NULL (т.е. организация будет головной).
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
--
-- Индексы сохранённых таблиц
--
--
-- Индексы таблицы `org`
--
ALTER TABLE `org`
ADD PRIMARY KEY (`id`), -- Делаем 'id' первичным ключом
ADD KEY `idx_parent_id` (`parent_id`); -- Создаём индекс для поля 'parent_id' для ускорения поиска дочерних компаний
--
-- AUTO_INCREMENT для сохранённых таблиц
--
--
-- AUTO_INCREMENT для таблицы `org`
--
ALTER TABLE `org`
MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- Ограничения внешнего ключа сохраненных таблиц
--
--
-- Ограничения внешнего ключа таблицы `org`
--
ALTER TABLE `org`
ADD CONSTRAINT `fk_org_parent` FOREIGN KEY (`parent_id`) REFERENCES `org` (`id`) ON DELETE SET NULL;
-- Создаём внешний ключ (Foreign Key)
-- Эта строчка гарантирует, что в 'parent_id' нельзя записать ID,
-- которого нет в колонке 'id' этой же таблицы 'org'.
-- ON DELETE SET NULL - очень важное правило:
-- Если мы удалим материнскую компанию, то у всех её "дочек" поле 'parent_id'
-- автоматически станет NULL, и они станут самостоятельными организациями,
-- а не удалятся сами. Это предотвращает потерю данных.
COMMIT;
--
-- Структура таблицы `pay`
--
CREATE TABLE `pay` (
`id` int(10) UNSIGNED NOT NULL, -- Уникальный идентификатор платежа
`org_id` int(10) UNSIGNED NOT NULL, -- Ссылка на организацию-получателя из таблицы 'org'
`amount` decimal(15,2) NOT NULL, -- Сумма платежа. DECIMAL(15,2) для рублей с точностью до копейки.
`pay_date` datetime NOT NULL -- Дата и время совершения платежа
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
--
-- Индексы сохранённых таблиц
--
--
-- Индексы таблицы `pay`
--
ALTER TABLE `pay`
ADD PRIMARY KEY (`id`), -- Устанавливаем первичный ключ
ADD KEY `idx_org_id` (`org_id`); -- Создаём индекс для поля org_id для ускорения поиска платежей по организации
--
-- AUTO_INCREMENT для сохранённых таблиц
--
--
-- AUTO_INCREMENT для таблицы `pay`
--
ALTER TABLE `pay`
MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- Ограничения внешнего ключа сохраненных таблиц
--
--
-- Ограничения внешнего ключа таблицы `pay`
--
ALTER TABLE `pay`
ADD CONSTRAINT `fk_pay_org` FOREIGN KEY (`org_id`) REFERENCES `org` (`id`);
-- Создаём внешний ключ (FOREIGN KEY)
-- Эта связь гарантирует, что мы не сможем добавить платёж для несуществующей организации.
-- Если кто-то попытается удалить организацию из справочника 'org',
-- а у неё будут платежи в этой таблице, база данных выдаст ошибку и не даст удалить.
COMMIT;
Ниже представлены ключевые SQL-запросы, используемые в системе для построения отчётов.
Базовый запрос для вывода всех операций поступления за выбранный период.
SELECT
p.pay_date,
o.name AS org_name,
p.amount
FROM pay p
JOIN org o ON p.org_id = o.id
WHERE p.pay_date BETWEEN ? AND ?
ORDER BY p.pay_date ASC
Запрос для группировки поступлений по каждой отдельной организации.
SELECT
o.name AS org_name,
SUM(p.amount) AS amount
FROM pay p
JOIN org o ON p.org_id = o.id
WHERE p.pay_date BETWEEN ? AND ?
GROUP BY o.id, o.name
HAVING SUM(p.amount) > 0
ORDER BY o.name ASC
Запрос для получения итогов только по организациям верхнего уровня (где parent_id IS NULL).
SELECT
o.name AS org_name,
SUM(p.amount) AS amount
FROM pay p
JOIN org o ON p.org_id = o.id
WHERE p.pay_date BETWEEN ? AND ?
AND o.parent_id IS NULL
GROUP BY o.id, o.name
HAVING SUM(p.amount) > 0
ORDER BY o.name ASC
Сложный запрос, который суммирует поступления головной организации и её прямых дочерних компаний.
SELECT
head.name AS org_name,
COALESCE(SUM(p.amount), 0) AS amount
FROM org head
LEFT JOIN pay p ON p.org_id IN (
SELECT id FROM org WHERE parent_id = head.id OR id = head.id
) AND p.pay_date BETWEEN ? AND ?
WHERE head.parent_id IS NULL
GROUP BY head.id, head.name
HAVING SUM(p.amount) > 0
ORDER BY head.name ASC
Запрос, используемый в API эндпоинте `/api/run/index.php` при `entity=pay`.
SELECT
p.id,
p.pay_date,
o.name AS org_name,
p.amount
FROM pay p
JOIN org o ON p.org_id = o.id
WHERE p.pay_date BETWEEN ? AND ?
ORDER BY p.pay_date DESC
LIMIT ?
Разбор ключевых слов и конструкций, используемых в запросах выше.
Что это: Конструкция для объединения данных из двух и более таблиц на основе связующего столбца.
Стандарт: Строго соблюдается стандартом SQL во всех СУБД (MySQL, PostgreSQL, MS SQL и др.).
Что это: `GROUP BY` группирует строки с одинаковыми значениями в одну. Агрегатные функции (как `SUM()`, `COUNT()`, `AVG()`) вычисляют итоговое значение для каждой такой группы.
Пример: `GROUP BY o.name` берет все строки с одной и той же организацией и "складывает" их в одну, а `SUM(p.amount)` считает общую сумму для этой группы.
Особенности реализации: В MySQL до версии 5.7 можно было писать `GROUP BY`, не включая в `SELECT` все неключевые поля, что могло приводить к непредсказуемым результатам. В PostgreSQL это запрещено стандартом SQL всегда. В современных версиях MySQL (по умолчанию) поведение такое же строгое, как в PostgreSQL.
Стандарт: В целом соблюдается, но исторически MySQL был более "либерален".
Что это: Фильтр для результатов, полученных после группировки (`GROUP BY`). Аналог `WHERE`, но `WHERE` фильтрует строки *ДО* группировки, а `HAVING` — *ПОСЛЕ*.
Зачем нужно: Нельзя использовать агрегатные функции в `WHERE`. Чтобы отфильтровать группы, например, "показать только те организации, у которых сумма поступлений больше 0", нужен `HAVING SUM(p.amount) > 0`.
Стандарт: Строго соблюдается стандартом SQL.
Что это: Сортировка итогового результата запроса.
Особенности реализации: И MySQL, и PostgreSQL поддерживают сортировку по нескольким полям (`ORDER BY col1 ASC, col2 DESC`) и даже по порядковому номеру столбца (`ORDER BY 1`), но это считается плохой практикой и не рекомендуется стандартом.
Стандарт: Строго соблюдается.
Что это: Функция, которая возвращает первое ненулевое (не `NULL`) значение из списка аргументов.
Зачем нужно: Очень полезна для обработки отсутствующих данных. Например, если у организации нет поступлений, `SUM(p.amount)` вернет `NULL`. `COALESCE(SUM(p.amount), 0)` заменит этот `NULL` на красивый `0`.
Стандарт: Строго соблюдается стандартом SQL.
Что это: Ограничение количества строк в результате запроса.
Особенности реализации: Это ключевое слово специфично для MySQL. В PostgreSQL используется немного другой синтаксис: `LIMIT ... OFFSET ...`. Хотя PostgreSQL также понимает `LIMIT`, стандартным для него является использование `FETCH FIRST n ROWS ONLY`.
Стандарт: Не является частью основного стандарта SQL-92, но поддерживается многими СУБД как расширение.