CRUD и запросы

INSERT INTO users (email, name) VALUES ('a@b.com', 'Алиса');

INSERT INTO users (email, name) VALUES
    ('a@b.com', 'Алиса'),
    ('c@d.com', 'Боб'),
    ('e@f.com', 'Катя');

INSERT INTO users (email, name) VALUES ('a@b.com', 'Алиса');
SELECT LAST_INSERT_ID();

INSERT INTO users_backup (email, name)
SELECT email, name FROM users WHERE created_at < '2025-01-01';

INSERT INTO users (email, name)
VALUES ('a@b.com', 'Алиса')
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    updated_at = NOW();

INSERT INTO users (email, name)
VALUES ('a@b.com', 'Алиса') AS new
ON DUPLICATE KEY UPDATE
    name = new.name,
    updated_at = NOW();

REPLACE INTO users (id, email, name) VALUES (1, 'a@b.com', 'Алиса');

INSERT IGNORE INTO users (email, name) VALUES ('a@b.com', 'Алиса');

SELECT * FROM users;
SELECT id, email FROM users;
SELECT email AS user_email FROM users;
SELECT DISTINCT status FROM orders;
SELECT * FROM users LIMIT 10 OFFSET 20;

WHERE age = 25
WHERE age != 25
WHERE age <> 25
WHERE age > 18 AND country = 'RU'
WHERE status = 'paid' OR status = 'shipped'
WHERE status IN ('pending', 'paid')
WHERE age BETWEEN 18 AND 65
WHERE email LIKE '%@gmail.com'      -- регистрозависимо в utf8mb4_general_ci? НЕТ, зависит от коллации
WHERE email IS NULL
WHERE email IS NOT NULL

ORDER BY created_at DESC
ORDER BY name ASC, age DESC
ORDER BY id LIMIT 10
ORDER BY created_at DESC NULLS LAST   -- в MySQL через IS NULL

ORDER BY col IS NULL, col ASC;

SELECT country, COUNT(*) AS cnt
FROM users
GROUP BY country
ORDER BY cnt DESC;

SELECT country, COUNT(*) AS cnt
FROM users
GROUP BY country
HAVING COUNT(*) > 10;

UPDATE users SET name = 'Алиса' WHERE id = 1;

UPDATE users
SET name = 'Алиса',
    email = 'alice@b.com',
    updated_at = NOW()
WHERE id = 1;

UPDATE orders o
JOIN (
    SELECT order_id, SUM(price * quantity) AS total
    FROM order_items
    GROUP BY order_id
) s ON s.order_id = o.id
SET o.total = s.total;

SELECT ROW_COUNT();

DELETE FROM users WHERE id = 1;

DELETE FROM orders
WHERE status = 'cancelled'
  AND created_at < NOW() - INTERVAL 1 YEAR;

DELETE o FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.email = 'spam@example.com';

DELETE o, oi
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'cancelled';

SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 20, 10;   -- OFFSET 20, LIMIT 10
SELECT * FROM users LIMIT 10 OFFSET 20;

SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 10;

SELECT
    COUNT(*) AS total,
    COUNT(bio) AS with_bio,
    COUNT(DISTINCT country) AS countries,
    AVG(age) AS avg_age,
    MIN(created_at) AS first_user,
    MAX(created_at) AS last_user,
    SUM(total) AS revenue
FROM users;

SELECT
    email,
    CASE
        WHEN age < 18 THEN 'ребёнок'
        WHEN age < 65 THEN 'взрослый'
        ELSE 'пенсионер'
    END AS category
FROM users;

SELECT IF(age >= 18, 'adult', 'minor') FROM users;
SELECT IFNULL(bio, 'нет описания') FROM users;
SELECT COALESCE(phone, email, 'нет контакта') FROM users;
SELECT NULLIF(country, '') FROM users;

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;
-- или ROLLBACK;

SELECT @@autocommit;   -- 1 по умолчанию
SET autocommit = 0;    -- не рекомендуется

START TRANSACTION;
INSERT INTO users (email) VALUES ('a@b.com');
SAVEPOINT sp1;
INSERT INTO users (email) VALUES ('a@b.com');   -- ошибка UNIQUE
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

// ❌
await pool.query(`SELECT * FROM users WHERE email = '${email}'`);

// ✅
await pool.query('SELECT * FROM users WHERE email = ?', [email]);

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ?');
$stmt->execute([$email]);

User::where('email', $email)->first();
DB::select('SELECT * FROM users WHERE email = ?', [$email]);

-- 1. INSERT
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Алиса');
SET @user_id = LAST_INSERT_ID();

-- 2. SELECT
SELECT id, email, name, created_at FROM users WHERE id = @user_id;

-- 3. UPDATE
UPDATE users SET name = 'Алиса П.' WHERE id = @user_id;

-- 4. Upsert
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Алиса Петрова')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- 5. DELETE
DELETE FROM users WHERE id = @user_id;

Обновит/удалит всё

foreach ($rows as $r) {
    $pdo->exec("INSERT ...");   // 1000 запросов
}

Лишний трафик, ломкость

Full scan

Сносит связанные данные через CASCADE

WHERE varchar_col = 123   -- ломает индекс

Блокировки надолго

Возвращает id первой строки, не последней