SQL ist eine der wichtigsten Sprachen in der IT. Jeder Entwickler sollte sie beherrschen.
Grundlagen¶
SELECT name, email FROM users WHERE active = true ORDER BY name LIMIT 10;
INSERT, UPDATE, DELETE¶
INSERT INTO users (name, email) VALUES (‘Hans’, ‘[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 = beide muessen existieren. LEFT JOIN = auch ohne Uebereinstimmung rechts. RIGHT JOIN = Gegenteil.
Aggregation¶
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;
Indizes¶
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
Transaktionen¶
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
Tipp¶
SQL ist eine Sprache, die sich auf fortgeschrittenem Niveau zu beherrschen lohnt. Window Functions und CTEs heben Sie vom Durchschnitt ab.