SQL

1. Mit der Datenbank arbeiten

mycli

mycli ist ein MySQL-Client für das Terminal. Er meldet sich mit deinen Zugangsdaten aus dem Profil von selbst an und öffnet deine Datenbank:

mycli

• Eine Abfrage endet mit ;, erst dann führt mycli sie aus. Lange Abfragen dürfen über mehrere Zeilen gehen.
• Beim Tippen schlägt mycli Befehle, Tabellen und Spalten vor; Tab übernimmt den Vorschlag.
• Lange Ergebnisse zeigt mycli seitenweise; Q führt zurück zur Eingabe.
• exit oder StrgD beendet mycli.

Eine weitere Datenbank aus deinem Profil öffnest du mit ihrem Namen, eine Datei mit SQL führst du aus, indem du sie an mysql gibst:

mycli db_k7q4bcd9fghj
mysql < film.sql

Tabellen und Spalten ansehen

SHOW TABLES;
DESCRIBE country;

SHOW TABLES listet die Tabellen der Datenbank, DESCRIBE die Spalten einer Tabelle mit ihren Datentypen, ob sie leer bleiben dürfen (NULL) und welche zum Schlüssel gehören (PRI, MUL).

2. Abfragen

SELECT und FROM

SELECT name, population FROM country;
SELECT * FROM country;
SELECT name, population / area AS density FROM country;

Nach SELECT stehen die Spalten, die das Ergebnis haben soll, nach FROM die Tabelle. * heißt »alle Spalten«. Eine Spalte kann auch ein berechneter Wert sein; AS gibt ihr einen Namen.

Zeilen filtern: WHERE

SELECT * FROM country WHERE continent = 'Europa';
SELECT name FROM country WHERE population < 5000000 AND area > 1000000;
SELECT name FROM country WHERE continent IN ('Europa', 'Asien');
SELECT name FROM country WHERE name LIKE '%land%';

Texte stehen in einfachen Anführungszeichen, Zahlen ohne.

Sortieren und begrenzen: ORDER BY, LIMIT

SELECT name, population FROM country ORDER BY population DESC LIMIT 10;
SELECT name, continent FROM country ORDER BY continent, name;

ORDER BY sortiert aufsteigend, mit DESC absteigend; nach mehreren Spalten, wenn die erste gleich ist. LIMIT gibt höchstens so viele Zeilen aus. Ohne ORDER BY ist die Reihenfolge nicht festgelegt.

Zählen und zusammenfassen

SELECT COUNT(*) FROM country WHERE continent = 'Asien';
SELECT DISTINCT continent FROM country;
SELECT continent, COUNT(*) AS countries, SUM(population) AS people
FROM country
GROUP BY continent
HAVING COUNT(*) >= 10
ORDER BY countries DESC;

COUNT(*) zählt Zeilen, SUM, AVG, MIN und MAX rechnen über eine Spalte. DISTINCT gibt jeden Wert nur einmal aus. GROUP BY fasst Zeilen mit gleichem Wert zu einer Gruppe zusammen; dann rechnen die Funktionen je Gruppe. WHERE filtert Zeilen vor dem Gruppieren, HAVING danach die Gruppen.

Tabellen verbinden: JOIN

SELECT movie.title, genre.name
FROM movie
JOIN movie_genre ON movie.id = movie_genre.movie_id
JOIN genre ON movie_genre.genre_id = genre.id
WHERE movie.title = 'Back to the Future';

JOIN … ON … setzt jede Zeile der einen Tabelle mit den Zeilen der anderen zusammen, für die die Bedingung gilt, meist Fremdschlüssel = Primärschlüssel. Spalten schreibt man dann mit ihrer Tabelle davor (movie.title). Braucht eine Abfrage dieselbe Tabelle zweimal, bekommt sie Namen: JOIN job AS ja ON …. LEFT JOIN behält auch Zeilen ohne Partner; deren Spalten aus der anderen Tabelle sind dann NULL.

Entfernungen auf der Erde

SELECT b.name, ROUND(ST_Distance_Sphere(POINT(a.longitude, a.latitude), POINT(b.longitude, b.latitude)) / 1000) AS km
FROM city AS a
JOIN city AS b ON b.id <> a.id
WHERE a.name = 'Berlin'
ORDER BY km
LIMIT 5;

POINT(x, y) macht aus zwei Zahlen einen Punkt, auf der Erde mit der Länge (longitude) zuerst und der Breite (latitude) danach. ST_Distance_Sphere gibt die Entfernung zweier Punkte auf einer Kugel vom Radius der Erde, in Metern, also Luftlinie. CROSS JOIN setzt jede Zeile der einen Tabelle mit jeder der anderen zusammen; JOIN … ON b.id <> a.id tut dasselbe ohne die Paare einer Stadt mit sich selbst. Für viele Millionen Punkte bekommt die Lage eine eigene Spalte vom Typ POINT mit einem SPATIAL INDEX, damit die Datenbank nicht jede Zeile ausrechnen muss.

3. Tabellen und Daten ändern

Tabellen anlegen: CREATE TABLE

CREATE TABLE entry (
  id       INT NOT NULL AUTO_INCREMENT,
  name     VARCHAR(80) NOT NULL,
  message  TEXT NOT NULL,
  created  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

DROP TABLE entry; löscht die Tabelle samt Inhalt. Wie ein ER-Modell zu Tabellen wird, steht unter ER-Modelle.

Zeilen einfügen: INSERT

INSERT INTO entry (name, message) VALUES ('Ada', 'Schönes Gästebuch!');
INSERT INTO entry (name, message) VALUES ('Alan', 'Hallo'), ('Grace', 'Hi');

Nach dem Tabellennamen stehen die Spalten, nach VALUES die Werte in derselben Reihenfolge, je Zeile ein Paar Klammern. Spalten mit AUTO_INCREMENT oder DEFAULT darf man weglassen.

Ändern und löschen: UPDATE, DELETE

UPDATE entry SET message = 'Schönes Gästebuch :)' WHERE id = 1;
DELETE FROM entry WHERE id = 2;

Beide wirken auf jede Zeile, auf die WHERE zutrifft. Ohne WHERE ändert oder löscht der Befehl alle Zeilen. Wer unsicher ist, schreibt zuerst ein SELECT mit derselben Bedingung und sieht nach, welche Zeilen getroffen werden.

4. SQL in Programmen

Werte über Platzhalter

Ein Programm, das eine Abfrage aus Text und Eingaben zusammenklebt, lässt die Eingabe mitbestimmen, was die Datenbank tut: Ein Anführungszeichen im Namen beendet den Text im SQL, und was danach kommt, liest MySQL als SQL. Das heißt SQL-Injection. Die Abhilfe ist immer dieselbe: Die Abfrage enthält Platzhalter, die Werte gehen getrennt an die Datenbank und werden nie als SQL gelesen.

// PHP mit PDO
$stmt = $db->prepare('INSERT INTO entry (name, message) VALUES (?, ?)');
$stmt->execute([$name, $message]);

# Ruby mit mysql2
client.prepare('SELECT * FROM entry WHERE name = ?').execute(name)

# Python mit mysql.connector
cursor.execute('SELECT * FROM entry WHERE name = %s', (name,))

Platzhalter stehen nur für Werte, nicht für Tabellen- oder Spaltennamen. Muss ein Programm einen Namen wählen, nimmt es ihn aus einer festen Liste erlaubter Namen.

5. Wenn MySQL etwas meldet