Основы SQL

SELECT — выборка данных

-- Basic SELECT
SELECT * FROM users;
SELECT name, email FROM users;

-- WHERE conditions
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE name = 'Alice' AND active = true;
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE name LIKE 'A%';       -- Starts with A
SELECT * FROM users WHERE email LIKE '%@test.com'; -- Ends with @test.com
SELECT * FROM users WHERE role IN ('admin', 'editor');
SELECT * FROM users WHERE deleted_at IS NULL;

-- ORDER BY
SELECT * FROM users ORDER BY name ASC;
SELECT * FROM users ORDER BY created_at DESC, name ASC;

-- LIMIT and OFFSET
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;  -- Page 3 (items 21-30)

<?php
declare(strict_types=1);

// PHP example
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare('SELECT * FROM users WHERE age > :age ORDER BY name');
$stmt->execute(['age' => 18]);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

INSERT — вставка данных

-- Single row
INSERT INTO users (name, email, age) VALUES ('Alice', '[email protected]', 25);

-- Multiple rows
INSERT INTO users (name, email, age) VALUES
    ('Alice', '[email protected]', 25),
    ('Bob', '[email protected]', 30),
    ('Charlie', '[email protected]', 35);

-- INSERT ... SELECT
INSERT INTO users_archive (name, email)
    SELECT name, email FROM users WHERE active = false;

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

-- Update specific rows
UPDATE users SET name = 'Alice Smith' WHERE id = 1;
UPDATE users SET age = age + 1 WHERE birthday = CURRENT_DATE;

-- Update multiple columns
UPDATE users SET
    name = 'Alice',
    email = '[email protected]',
    updated_at = NOW()
WHERE id = 1;

Ловушка экзамена: UPDATE без WHERE обновит ВСЕ строки в таблице! DELETE без WHERE удалит ВСЕ строки! Это частая ловушка и на экзамене, и в реальной работе.

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

-- Delete specific rows
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE active = false AND last_login < '2023-01-01';

-- Delete all rows (careful!)
DELETE FROM users;        -- Удаляет все строки
TRUNCATE TABLE users;     -- Быстрее, сбрасывает auto-increment

JOIN — объединение таблиц

-- INNER JOIN — only matching rows from both tables
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN — all rows from left, matching from right (NULL if no match)
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN — all rows from right, matching from left
SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- Self JOIN
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Multiple JOINs
SELECT u.name, o.id, p.name AS product
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

GROUP BY и агрегатные функции

-- Aggregate functions
SELECT COUNT(*) FROM users;                    -- Count rows
SELECT COUNT(DISTINCT city) FROM users;        -- Count unique cities
SELECT AVG(age) FROM users;                    -- Average
SELECT SUM(total) FROM orders;                 -- Sum
SELECT MIN(price), MAX(price) FROM products;   -- Min/Max

-- GROUP BY
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
ORDER BY user_count DESC;

-- HAVING — filter grouped results
SELECT city, AVG(age) AS avg_age
FROM users
GROUP BY city
HAVING AVG(age) > 25
ORDER BY avg_age DESC;

-- Difference: WHERE filters rows BEFORE grouping
--             HAVING filters groups AFTER grouping

Запомни: WHERE фильтрует строки ДО группировки. HAVING фильтрует ПОСЛЕ группировки. В HAVING можно использовать агрегатные функции, в WHERE — нельзя.

Подзапросы

-- Subquery in WHERE
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

-- Subquery in FROM
SELECT avg_orders.city, avg_orders.avg_total
FROM (
    SELECT u.city, AVG(o.total) AS avg_total
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    GROUP BY u.city
) AS avg_orders
WHERE avg_orders.avg_total > 50;

-- EXISTS
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);