create_database.sql - master - ap1k/Fitnes_club

create_database.sql
131 строка6 KB
Roman
С deletами
30 май 2025, 13:57
30 май 2025, 13:570713f80
-- SQL скрипт для создания базы данных фитнес-клуба
-- Используется PostgreSQL

-- Создание базы данных (выполнить от имени администратора)
-- CREATE DATABASE fitness_club;

-- Подключение к базе данных fitness_club
\c fitness_club;

-- Создание таблицы клиентов
CREATE TABLE IF NOT EXISTS clients (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE,
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Создание таблицы тренеров
CREATE TABLE IF NOT EXISTS trainers (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    specialization VARCHAR(100) NOT NULL,
    experience_years INTEGER DEFAULT 0,
    hourly_rate DECIMAL(10,2) NOT NULL,
    phone VARCHAR(20) UNIQUE
);

-- Создание таблицы абонементов
CREATE TABLE IF NOT EXISTS memberships (
    id SERIAL PRIMARY KEY,
    client_id INTEGER NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    trainer_id INTEGER REFERENCES trainers(id) ON DELETE SET NULL,
    membership_type VARCHAR(50) NOT NULL,
    start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    end_date TIMESTAMP NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'Активный'
);

-- Создание индексов для оптимизации запросов
CREATE INDEX IF NOT EXISTS idx_clients_phone ON clients(phone);
CREATE INDEX IF NOT EXISTS idx_clients_email ON clients(email);
CREATE INDEX IF NOT EXISTS idx_trainers_specialization ON trainers(specialization);
CREATE INDEX IF NOT EXISTS idx_memberships_client_id ON memberships(client_id);
CREATE INDEX IF NOT EXISTS idx_memberships_trainer_id ON memberships(trainer_id);
CREATE INDEX IF NOT EXISTS idx_memberships_status ON memberships(status);
CREATE INDEX IF NOT EXISTS idx_memberships_end_date ON memberships(end_date);

-- Вставка тестовых данных

-- Тренеры
INSERT INTO trainers (first_name, last_name, specialization, experience_years, hourly_rate, phone) VALUES
('Алексей', 'Петров', 'Силовые тренировки', 5, 2500.00, '+7-900-123-4567'),
('Мария', 'Сидорова', 'Йога и растяжка', 3, 2000.00, '+7-900-234-5678'),
('Дмитрий', 'Козлов', 'Кардио тренировки', 7, 3000.00, '+7-900-345-6789'),
('Елена', 'Волкова', 'Функциональный тренинг', 4, 2200.00, '+7-900-456-7890')
ON CONFLICT (phone) DO NOTHING;

-- Клиенты
INSERT INTO clients (first_name, last_name, phone, email) VALUES
('Иван', 'Иванов', '+7-911-111-1111', 'ivan.ivanov@email.com'),
('Анна', 'Смирнова', '+7-911-222-2222', 'anna.smirnova@email.com'),
('Петр', 'Васильев', '+7-911-333-3333', 'petr.vasiliev@email.com'),
('Ольга', 'Николаева', '+7-911-444-4444', 'olga.nikolaeva@email.com'),
('Сергей', 'Морозов', '+7-911-555-5555', 'sergey.morozov@email.com')
ON CONFLICT (phone) DO NOTHING;

-- Абонементы
INSERT INTO memberships (client_id, trainer_id, membership_type, start_date, end_date, price, status) VALUES
(1, 1, 'Базовый', CURRENT_TIMESTAMP - INTERVAL '30 days', CURRENT_TIMESTAMP + INTERVAL '335 days', 3000.00, 'Активный'),
(2, NULL, 'Премиум', CURRENT_TIMESTAMP - INTERVAL '30 days', CURRENT_TIMESTAMP + INTERVAL '335 days', 5000.00, 'Активный'),
(3, 2, 'VIP', CURRENT_TIMESTAMP - INTERVAL '30 days', CURRENT_TIMESTAMP + INTERVAL '335 days', 8000.00, 'Активный'),
(4, NULL, 'Базовый', CURRENT_TIMESTAMP - INTERVAL '30 days', CURRENT_TIMESTAMP + INTERVAL '335 days', 3000.00, 'Активный'),
(5, 3, 'Премиум', CURRENT_TIMESTAMP - INTERVAL '30 days', CURRENT_TIMESTAMP + INTERVAL '335 days', 5000.00, 'Активный');

-- Полезные запросы для проверки данных

-- Просмотр всех клиентов с их абонементами
SELECT 
    c.first_name || ' ' || c.last_name AS client_name,
    c.phone,
    m.membership_type,
    m.status,
    m.start_date,
    m.end_date,
    t.first_name || ' ' || t.last_name AS trainer_name
FROM clients c
LEFT JOIN memberships m ON c.id = m.client_id
LEFT JOIN trainers t ON m.trainer_id = t.id
ORDER BY c.last_name, c.first_name;

-- Статистика по типам абонементов
SELECT 
    membership_type,
    COUNT(*) as count,
    SUM(price) as total_revenue
FROM memberships 
GROUP BY membership_type
ORDER BY count DESC;

-- Тренеры с количеством клиентов
SELECT 
    t.first_name || ' ' || t.last_name AS trainer_name,
    t.specialization,
    COUNT(m.id) as active_clients
FROM trainers t
LEFT JOIN memberships m ON t.id = m.trainer_id AND m.status = 'Активный'
GROUP BY t.id, t.first_name, t.last_name, t.specialization
ORDER BY active_clients DESC;

-- Просроченные абонементы
SELECT 
    c.first_name || ' ' || c.last_name AS client_name,
    c.phone,
    m.membership_type,
    m.end_date,
    CURRENT_DATE - m.end_date::date AS days_expired
FROM memberships m
JOIN clients c ON m.client_id = c.id
WHERE m.end_date < CURRENT_TIMESTAMP AND m.status = 'Активный'
ORDER BY m.end_date;

-- Обновление статуса просроченных абонементов
-- UPDATE memberships 
-- SET status = 'Завершен' 
-- WHERE end_date < CURRENT_TIMESTAMP AND status = 'Активный';

COMMIT;