Какие знаешь типы SQL запросов?

Типы SQL запросов (DML, DDL, DCL, TCL)

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

1. DML (Data Manipulation Language) — манипуляция данными

Эти запросы изменяют или извлекают данные из БД.

SELECT — получение данных

-- Простой SELECT
SELECT id, name, email FROM users WHERE age > 18;

-- С агрегацией
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

-- С JOIN
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';

-- С подзапросом
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

// Java JDBC пример
String query = "SELECT id, name, email FROM users WHERE age > ?";
PreparedStatement stmt = connection.prepareStatement(query);
stmt.setInt(1, 18);
ResultSet rs = stmt.executeQuery();

while (rs.next()) {
    int id = rs.getInt("id");
    String name = rs.getString("name");
    String email = rs.getString("email");
}

INSERT — добавление данных

-- Простой INSERT
INSERT INTO users (name, email, age)
VALUES ('John', 'john@example.com', 30);

-- Массовый INSERT
INSERT INTO users (name, email, age) VALUES
('John', 'john@example.com', 30),
('Jane', 'jane@example.com', 28),
('Bob', 'bob@example.com', 35);

-- INSERT из SELECT
INSERT INTO users_backup
SELECT * FROM users WHERE created_at < '2024-01-01';

public void insertUser(String name, String email, int age) throws SQLException {
    String query = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
    PreparedStatement stmt = connection.prepareStatement(query);
    
    stmt.setString(1, name);
    stmt.setString(2, email);
    stmt.setInt(3, age);
    
    int affectedRows = stmt.executeUpdate();
    System.out.println("Inserted: " + affectedRows + " row(s)");
}

UPDATE — изменение данных

-- Простой UPDATE
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- UPDATE с условием
UPDATE users
SET last_login = NOW(), status = 'active'
WHERE created_at > '2024-01-01' AND status = 'inactive';

-- UPDATE на основе JOIN
UPDATE users u
SET u.points = u.points + 100
WHERE u.id IN (
    SELECT DISTINCT user_id FROM orders
    WHERE total > 1000 AND created_at > '2024-01-01'
);

public void updateUser(Long id, String email) throws SQLException {
    String query = "UPDATE users SET email = ? WHERE id = ?";
    PreparedStatement stmt = connection.prepareStatement(query);
    
    stmt.setString(1, email);
    stmt.setLong(2, id);
    
    int affectedRows = stmt.executeUpdate();
    System.out.println("Updated: " + affectedRows + " row(s)");
}

DELETE — удаление данных

-- Простой DELETE
DELETE FROM users WHERE id = 1;

-- DELETE с условием
DELETE FROM users
WHERE created_at < '2024-01-01'
AND status = 'inactive';

-- DELETE с JOIN
DELETE FROM orders
WHERE user_id IN (
    SELECT id FROM users WHERE status = 'banned'
);

public void deleteUser(Long id) throws SQLException {
    String query = "DELETE FROM users WHERE id = ?";
    PreparedStatement stmt = connection.prepareStatement(query);
    
    stmt.setLong(1, id);
    
    int affectedRows = stmt.executeUpdate();
    System.out.println("Deleted: " + affectedRows + " row(s)");
}

2. DDL (Data Definition Language) — определение структуры данных

Эти запросы создают, изменяют и удаляют объекты БД (таблицы, индексы, представления).

CREATE — создание

-- CREATE TABLE
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- CREATE INDEX
CREATE INDEX idx_user_email ON users(email);
CREATE UNIQUE INDEX idx_order_number ON orders(order_number);

-- CREATE VIEW
CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';

-- CREATE SCHEMA (БД)
CREATE SCHEMA my_database;

ALTER — изменение

-- ALTER TABLE - добавить колонку
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- ALTER TABLE - удалить колонку
ALTER TABLE users DROP COLUMN phone;

-- ALTER TABLE - изменить тип колонки
ALTER TABLE users MODIFY COLUMN age SMALLINT;

-- ALTER TABLE - переименовать колонку
ALTER TABLE users RENAME COLUMN created_at TO date_created;

-- ALTER TABLE - добавить FOREIGN KEY
ALTER TABLE orders
ADD CONSTRAINT fk_user_id
FOREIGN KEY (user_id) REFERENCES users(id);

DROP — удаление

-- DROP TABLE
DROP TABLE users;
DROP TABLE IF EXISTS users;  -- Не выдаст ошибку если таблица не существует

-- DROP INDEX
DROP INDEX idx_user_email ON users;

-- DROP VIEW
DROP VIEW active_users;

TRUNCATE — быстрое удаление всех строк

-- TRUNCATE TABLE (быстрее DELETE, но нельзя откатить без транзакции)
TRUNCATE TABLE users;  -- Удаляет все строки, но структуру оставляет

Различие TRUNCATE vs DELETE:

• DELETE удаляет строки по одной (можно WHERE), откатываемо
• TRUNCATE удаляет все сразу (нет WHERE), не откатываемо, быстрее

3. DCL (Data Control Language) — управление доступом

Эти запросы управляют правами доступа к БД.

-- GRANT - дать права
GRANT SELECT, INSERT, UPDATE ON users TO 'app_user'@'localhost';

-- GRANT - дать все права
GRANT ALL PRIVILEGES ON my_database.* TO 'admin'@'%';

-- REVOKE - отозвать права
REVOKE INSERT, UPDATE ON users FROM 'app_user'@'localhost';

-- REVOKE - отозвать все права
REVOKE ALL PRIVILEGES ON my_database.* FROM 'app_user'@'localhost';

4. TCL (Transaction Control Language) — управление транзакциями

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

-- BEGIN или START TRANSACTION
BEGIN;

-- COMMIT - подтвердить
COMMIT;

-- ROLLBACK - откатить
ROLLBACK;

-- SAVEPOINT - точка сохранения
SAVEPOINT sp1;
ROLLBACK TO sp1;

// Java JDBC пример транзакции
public void transferMoney(Long fromId, Long toId, BigDecimal amount) throws SQLException {
    Connection conn = dataSource.getConnection();
    
    try {
        conn.setAutoCommit(false);  // Начинаем транзакцию
        
        // Списываем со счёта 1
        String query1 = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
        PreparedStatement stmt1 = conn.prepareStatement(query1);
        stmt1.setBigDecimal(1, amount);
        stmt1.setLong(2, fromId);
        stmt1.executeUpdate();
        
        // Переводим на счёт 2
        String query2 = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
        PreparedStatement stmt2 = conn.prepareStatement(query2);
        stmt2.setBigDecimal(1, amount);
        stmt2.setLong(2, toId);
        stmt2.executeUpdate();
        
        conn.commit();  // Подтверждаем обе операции
    } catch (SQLException e) {
        conn.rollback();  // Откатываем обе операции при ошибке
        throw e;
    } finally {
        conn.close();
    }
}

5. Расширенные DDL: CONSTRAINT

-- PRIMARY KEY
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT
);

-- UNIQUE
CREATE TABLE users (
    email VARCHAR(100) UNIQUE NOT NULL
);

-- FOREIGN KEY
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- CHECK
CREATE TABLE users (
    age INT CHECK (age >= 18)
);

-- DEFAULT
CREATE TABLE users (
    status VARCHAR(20) DEFAULT 'active'
);

-- NOT NULL
CREATE TABLE users (
    email VARCHAR(100) NOT NULL
);

Сравнение типов запросов

Performance considerations

-- Плохо: SELECT без индекса
SELECT * FROM users WHERE email = 'john@example.com';

-- Хорошо: с индексом
CREATE INDEX idx_user_email ON users(email);
SELECT * FROM users WHERE email = 'john@example.com';

-- Плохо: N+1 запросы
SELECT * FROM users;  -- 1 запрос
FOR каждого user: SELECT * FROM orders WHERE user_id = ?;  -- N запросов

-- Хорошо: JOIN
SELECT u.*, o.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Плохо: SELECT без WHERE
SELECT * FROM users;  -- Может быть миллионы строк

-- Хорошо: с LIMIT
SELECT * FROM users LIMIT 20;

Best practices

• Используй PreparedStatement вместо конкатенации строк (SQL injection)
• Добавляй индексы на поля в WHERE и JOIN
• Используй пагинацию (LIMIT, OFFSET)
• Избегай SELECT *, выбирай только нужные поля
• Используй транзакции для связанных операций
• Профилируй запросы через EXPLAIN PLAN
• Используй миграции (Goose, Liquibase, Flyway) для DDL
• Откатываемо или нет: DDL обычно не откатываемо, используй миграции

Понимание типов SQL запросов — фундамент для эффективной работы с любой БД!