SQL je jeden z nejdůležitějších jazyků v IT. Každý vývojář by ho měl ovládat.
Základy¶
SELECT name, email FROM users WHERE active = true ORDER BY name LIMIT 10;
INSERT, UPDATE, DELETE¶
INSERT INTO users (name, email) VALUES (‘Jan’, ‘[email protected]’);
UPDATE users SET active = false WHERE last_login < ‘2024-01-01’;
DELETE FROM users WHERE active = false;
JOIN¶
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total > 100;
INNER JOIN = oba musí existovat. LEFT JOIN = i bez shody vpravo. RIGHT JOIN = opak.
Agregace¶
SELECT status, COUNT(*), AVG(total), SUM(total)
FROM orders
GROUP BY status
HAVING COUNT(*) > 10;
Subqueries¶
SELECT * FROM users WHERE id IN (
SELECT user_id FROM orders WHERE total > 1000
);
CTE (Common Table Expression)¶
WITH top_customers AS (
SELECT user_id, SUM(total) as total_spent
FROM orders GROUP BY user_id
)
SELECT u.name, tc.total_spent
FROM users u JOIN top_customers tc ON u.id = tc.user_id
ORDER BY tc.total_spent DESC;
Window Functions¶
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) as rank,
AVG(salary) OVER () as avg_salary
FROM employees;
Indexy¶
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
Transakce¶
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
Tip¶
SQL je jazyk, který se vyplatí zvládnout na pokročilé úrovni. Window functions a CTEs vás odliší od průměru.