DDL (Data Definition Language)

-- Простейшая таблица
CREATE TABLE users (
    id INTEGER,
    name TEXT,
    email TEXT
);

-- Таблица с типами данных и ограничениями
CREATE TABLE products (
    id SERIAL PRIMARY KEY,           -- автоинкремент, первичный ключ
    name VARCHAR(255) NOT NULL,      -- не может быть пустым
    price DECIMAL(10,2) CHECK (price >= 0),  -- проверочное ограничение
    category_id INTEGER REFERENCES categories(id),  -- внешний ключ
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- Создание схемы
CREATE SCHEMA sales;
CREATE SCHEMA hr;
CREATE SCHEMA analytics;

-- Создание таблицы в схеме
CREATE TABLE sales.orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    amount DECIMAL(10,2)
);

-- Переключение на схему (PostgreSQL)
SET search_path TO sales, public;

-- Создание схемы с владельцем
CREATE SCHEMA private AUTHORIZATION admin_role;

-- Обычный индекс (B-Tree)
CREATE INDEX idx_users_email ON users(email);

-- Уникальный индекс
CREATE UNIQUE INDEX idx_users_phone ON users(phone);

-- Составной индекс
CREATE INDEX idx_orders_customer_date ON orders(customer_id, created_at);

-- Частичный индекс (только для активных пользователей)
CREATE INDEX idx_users_active_email ON users(email) WHERE is_active = TRUE;

-- Создание представления
CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE is_active = TRUE;

-- Использование как таблицы
SELECT * FROM active_users WHERE email LIKE '%@company.com';

-- Материализованное представление (хранит данные)
CREATE MATERIALIZED VIEW daily_sales AS
SELECT DATE(created_at) as sale_date, SUM(amount) as total
FROM orders
GROUP BY DATE(created_at);

-- PostgreSQL
CREATE DATABASE myapp;

-- С кодировкой и владельцем
CREATE DATABASE myapp 
    ENCODING 'UTF8' 
    OWNER app_user;

-- MySQL
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Создание последовательности
CREATE SEQUENCE order_number_seq START 1000 INCREMENT 1;

-- Использование
SELECT nextval('order_number_seq');  -- 1000
SELECT nextval('order_number_seq');  -- 1001
SELECT currval('order_number_seq');  -- 1001

-- Привязка к столбцу
ALTER TABLE orders ALTER COLUMN order_number SET DEFAULT nextval('order_number_seq');

-- Добавление колонки
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Добавление с значением по умолчанию
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- Добавление с NOT NULL (сначала добавить, потом заполнить, потом добавить NOT NULL)
ALTER TABLE users ADD COLUMN middle_name VARCHAR(100);
UPDATE users SET middle_name = '' WHERE middle_name IS NULL;
ALTER TABLE users ALTER COLUMN middle_name SET NOT NULL;

-- Изменение типа данных
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;

-- Изменение имени колонки (PostgreSQL)
ALTER TABLE users RENAME COLUMN phone TO contact_phone;

-- Установка значения по умолчанию
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'pending';

-- Удаление значения по умолчанию
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- Добавление NOT NULL
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Удаление NOT NULL
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

-- Добавление первичного ключа
ALTER TABLE users ADD PRIMARY KEY (id);

-- Добавление внешнего ключа
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer 
    FOREIGN KEY (customer_id) REFERENCES customers(id);

-- Добавление уникальности
ALTER TABLE users ADD CONSTRAINT uk_users_email UNIQUE (email);

-- Добавление проверочного ограничения
ALTER TABLE products ADD CONSTRAINT chk_products_price 
    CHECK (price >= 0);

-- Удаление ограничения
ALTER TABLE users DROP CONSTRAINT uk_users_email;

-- Удаление колонки (осторожно!)
ALTER TABLE users DROP COLUMN middle_name;

-- Удаление с проверкой зависимости (CASCADE)
ALTER TABLE users DROP COLUMN department_id CASCADE;

-- Переименование схемы
ALTER SCHEMA sales RENAME TO ecommerce;

-- Переименование индекса
ALTER INDEX idx_users_email RENAME TO idx_users_email_lower;

-- Изменение владельца
ALTER TABLE users OWNER TO new_admin;

-- Удаление таблицы (безвозвратно!)
DROP TABLE users;

-- Удаление с проверкой существования (без ошибки, если нет)
DROP TABLE IF EXISTS users;

-- Каскадное удаление (удаляет зависимые объекты)
DROP TABLE departments CASCADE;

-- Удаление индекса
DROP INDEX idx_users_email;

-- Без ошибки, если нет
DROP INDEX IF EXISTS idx_users_phone;

-- Удаление представления
DROP VIEW active_users;

-- Каскадное удаление (удаляет представления, зависящие от этого)
DROP VIEW active_users CASCADE;

-- Удаление пустой схемы
DROP SCHEMA old_schema;

-- Удаление схемы со всем содержимым
DROP SCHEMA old_schema CASCADE;

-- Удаление базы данных (PostgreSQL)
DROP DATABASE myapp;

-- MySQL
DROP DATABASE myapp;

-- Очистка таблицы (быстро, не логирует строки)
TRUNCATE TABLE logs;

-- Очистка с переиспользованием места
TRUNCATE TABLE temp_data;

-- Каскадная очистка (очищает связанные таблицы)
TRUNCATE TABLE departments CASCADE;

-- Перезапуск последовательностей (сброс автоинкремента)
TRUNCATE TABLE users RESTART IDENTITY;

-- Переименование таблицы
ALTER TABLE users RENAME TO app_users;

-- Переименование колонки
ALTER TABLE users RENAME COLUMN phone TO mobile;

-- Переименование индекса
ALTER INDEX idx_users_email RENAME TO idx_app_users_email;

-- Одноколоночный
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

-- Составной (несколько колонок)
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    amount DECIMAL(10,2)
);

-- С каскадным удалением
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE,
    amount DECIMAL(10,2)
);

-- ON DELETE варианты
-- NO ACTION (по умолчанию) — запретить удаление
-- CASCADE — удалить связанные записи
-- SET NULL — установить NULL
-- SET DEFAULT — установить значение по умолчанию
-- RESTRICT — запретить удаление (немного отличается от NO ACTION)

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    phone VARCHAR(20)
);

-- Составной UNIQUE
CREATE TABLE user_roles (
    user_id INTEGER,
    role_id INTEGER,
    UNIQUE (user_id, role_id)
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price DECIMAL(10,2) CHECK (price >= 0),
    age_restriction INTEGER CHECK (age_restriction IN (0, 6, 12, 16, 18)),
    discount DECIMAL(5,2) CHECK (discount BETWEEN 0 AND 100)
);

-- CHECK с несколькими колонками
CREATE TABLE events (
    start_date DATE,
    end_date DATE,
    CHECK (end_date > start_date)
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending',
    is_deleted BOOLEAN DEFAULT FALSE
);

-- JSON/JSONB (PostgreSQL)
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    payload JSONB
);
INSERT INTO events (payload) VALUES ('{"user_id": 123, "action": "login"}');

-- UUID
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT
);

-- Массивы (PostgreSQL)
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    tags TEXT[]
);
INSERT INTO products (tags) VALUES (ARRAY['electronics', 'sale']);

-- Перечисления (ENUM)
CREATE TYPE user_status AS ENUM ('active', 'pending', 'blocked');
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    status user_status
);

-- Временная таблица (удаляется при закрытии сессии)
CREATE TEMP TABLE temp_orders AS
SELECT * FROM orders WHERE created_at > CURRENT_DATE - INTERVAL '30 days';

-- Временная таблица с явной структурой
CREATE TEMP TABLE temp_calc (
    id INTEGER,
    value DECIMAL(10,2)
);

-- Локальная временная таблица (только для текущей сессии)
CREATE LOCAL TEMPORARY TABLE local_data (id INT) ON COMMIT DROP;

-- Создание схемы
CREATE SCHEMA app;
CREATE SCHEMA app_private;
CREATE SCHEMA audit;

-- Создание таблицы в схеме
CREATE TABLE app.users (id INT, name TEXT);

-- Поиск таблицы в схеме (search_path)
SHOW search_path;  -- "$user", public
SET search_path TO app, public;

-- Удаление схемы
DROP SCHEMA app CASCADE;

BEGIN;
CREATE TABLE test (id INT);
INSERT INTO test VALUES (1);
ROLLBACK;  -- Таблица test исчезнет

START TRANSACTION;
CREATE TABLE test (id INT);  -- CREATE TABLE транзакционна
ALTER TABLE test ADD COLUMN name TEXT;  -- ALTER не транзакционна
ROLLBACK;  -- Таблица удалится, но ALTER уже был выполнен

BEGIN
    INSERT INTO users VALUES (1);  -- DML
    CREATE TABLE test (id INT);     -- DDL: неявный COMMIT
    ROLLBACK;                       -- Откатит только после CREATE, но не INSERT
END;

-- Плохо (вызовет ошибку, если таблицы нет)
DROP TABLE users;

-- Хорошо
DROP TABLE IF EXISTS users;

-- Плохо: ссылается на таблицу, которая еще не создана
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(id)  -- customers не существует
);

-- Хорошо: сначала customers, потом orders
CREATE TABLE customers (id INT PRIMARY KEY);
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT REFERENCES customers(id));

-- Плохо (VARCHAR(1000) для имени)
name VARCHAR(1000)

-- Хорошо (реалистичный лимит)
name VARCHAR(100)

-- Плохо: добавить NOT NULL в существующую таблицу с данными
ALTER TABLE users ADD COLUMN middle_name TEXT NOT NULL;  -- Ошибка!

-- Хорошо: сначала добавить с DEFAULT, потом убрать
ALTER TABLE users ADD COLUMN middle_name TEXT DEFAULT '';
UPDATE users SET middle_name = '' WHERE middle_name IS NULL;
ALTER TABLE users ALTER COLUMN middle_name SET NOT NULL;
ALTER TABLE users ALTER COLUMN middle_name DROP DEFAULT;

-- Опасно: удалит все заказы при удалении пользователя
ALTER TABLE orders ADD CONSTRAINT fk_orders_user 
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- Безопаснее: запретить удаление пользователя с заказами
ALTER TABLE orders ADD CONSTRAINT fk_orders_user 
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT;