SQL Factsheet: Die wichtigsten Befehle

1. Datenbank und Tabellen erstellen

CREATE DATABASE coding_learning;
USE coding_learning;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  points INT NOT NULL DEFAULT 0
);

2. SELECT: Daten lesen

SELECT * FROM users;

SELECT name, email
FROM users;

SELECT name, points
FROM users
WHERE points >= 50
ORDER BY points DESC;

3. INSERT: Daten einfuegen

INSERT INTO users (name, email, points)
VALUES ('Mina', 'mina@example.com', 80);

INSERT INTO users (name, email)
VALUES ('Alex', 'alex@example.com');

4. UPDATE: Daten aendern

UPDATE users
SET points = points + 10
WHERE id = 1;

UPDATE users
SET email = 'new@example.com'
WHERE id = 2;

Wichtig: UPDATE fast immer mit WHERE benutzen. Ohne WHERE aenderst du alle Datensaetze.

5. DELETE: Daten loeschen

DELETE FROM users
WHERE id = 3;

Auch DELETE fast immer mit WHERE. Ohne Bedingung wird die Tabelle geleert.

6. JOIN: Tabellen verbinden

SELECT users.name, courses.title
FROM enrollments
JOIN users ON enrollments.user_id = users.id
JOIN courses ON enrollments.course_id = courses.id;

Ein JOIN verbindet Tabellen ueber Schluessel. Hier wird gezeigt, welcher User in welchem Kurs ist.

7. Aggregationen: zaehlen und berechnen

SELECT COUNT(*) AS user_count
FROM users;

SELECT AVG(points) AS average_points
FROM users;

SELECT course_id, COUNT(*) AS lesson_count
FROM lessons
GROUP BY course_id;

8. Nuetzliche Operatoren