SQL

SQL Dialekt

Unterschiedliche Datenbankimplementation haben leicht unterschiedliche implementationen von SQL. Hier habe ich ausschließlich dem MariaDB SQL Dialekt verwendet, allerdings könnten die hier gezeigten Befehle auch für andere Datenbanken funktionieren.

Database operationen

List

> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| database           |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Create

> CREATE DATABASE IF NOT EXISTS test_db;
Query OK, 1 row affected (0.001 sec)

Erstellt eine neue Datenbank mit dem spezifizierten Namen.

Tabellenoperationen

Create

> CREATE TABLE IF NOT EXISTS bus (
    -> kfz INT,
    -> bustyp CHAR(120) NOT NULL,
    -> sitzplaetze INT NOT NULL CHECK (sitzplaetze >= 0),
    -> baujahr INT NOT NULL,
    -> PRIMARY KEY (kfz)
    -> );
Query OK, 0 rows affected (0.026 sec)

Bei der Tabellenerstellung muss man den Tabellennamen (bus) definieren. Danach kommen die Spaltendefinitionen, wobei erst der Attribname, dann der Datentyp und zuletzt bei Bedarf “constraints”, also Beschränkungen definiert werden.

Der Primärschlüssel wird auch definiert, allerdings passiert dies abgetrennt von der Spaltendefinition.

Für dieses Beispiel habe ich die Relation vom Bus aus der Seite des Relationenmodells definiert.

Bus(_Kfz_, Bustyp, Sitzplätze, Baujahr)

Describe

> DESCRIBE bus;
+-------------+-----------+------+-----+---------+-------+
| Field       | Type      | Null | Key | Default | Extra |
+-------------+-----------+------+-----+---------+-------+
| kfz         | int(11)   | NO   | PRI | NULL    |       |
| bustyp      | char(120) | NO   |     | NULL    |       |
| sitzplaetze | int(11)   | NO   |     | NULL    |       |
| baujahr     | int(11)   | NO   |     | NULL    |       |
+-------------+-----------+------+-----+---------+-------+
4 rows in set (0.001 sec)

Um die Definition einer Tabelle abzurufen, kann mann den DESCRIBE Befehl verwenden. Dieser zeigt alle Spalten, ihre Datentypen, ob diese nullable sind, ob sie ein Schlüssel sind, und was ihr Standardwert ist. Zudem werden Extrainformationen (z.B. AUTO_INCREMENT) im Extra block abgebildet.

Alter

Mit ALTER lassen sich Tabellendefinitionen, wie z.B. ihre Spalten, ändern. Ein Bespiel für das Ändern des Datentyps für kfz:

> ALTER TABLE bus MODIFY kfz INT(16);
Query OK, 0 rows affected (0.034 sec)
Records: 0  Duplicates: 0  Warnings: 0

Viele Aspekte einer Tabelle können so geändert werden. Die volle Liste gibt es auf der MariaDB Dokumentation.

Drop Table

DROP TABLE löscht alle Einträge in der Tabelle und löscht anschliesend die Tabelle selber.

> DROP TABLE test_table;
Query OK, 0 rows affected (0.021 sec)

Truncate Table

Anders als DROP TABLE, löscht TRUNCATE TABLE die Tabelle nicht vollständig, sondern nur die Einträge, welche in dieser Tabelle gespeichert sind.

> SELECT * FROM test_table;
+------+
| id   |
+------+
|    5 |
+------+
1 row in set (0.001 sec)

> TRUNCATE TABLE test_table;
Query OK, 0 rows affected (0.018 sec)

> SELECT * FROM test_table;
Empty set (0.001 sec)

Datenabfrage

Select

> SELECT * FROM bus;
+-----+--------+-------------+---------+
| kfz | bustyp | sitzplaetze | baujahr |
+-----+--------+-------------+---------+
|   1 | MAN    |          76 |    2021 |
+-----+--------+-------------+---------+
1 row in set (0.007 sec)

Die WHERE-clause ist optional. Doch sollte man sich immer auf die Daten beschreiben, welche man auch wirklich braucht. Wenn eine Tabelle 2 Millionen Einträge hat, sollte man nicht 2 Millionen Einträge rausholen. Man muss nämlich kein Genie sein, um zu wissen, dass das lange dauern könnte.

Eine gute Datenbankabfrage beschränkt sich explizit auf die Daten, welche gebraucht werden. Wenn man also wissen möchte, wie viele Sitzplätze der Bus mit der KFZ von 2 hat, dann sollte man dies auch in eine passende Abfrage umwandeln. Vergleichsoperatoren sind später noch beschrieben.

> SELECT sitzplaetze FROM bus WHERE kfz = 2;
+-------------+
| sitzplaetze |
+-------------+
|          56 |
+-------------+
1 row in set (0.001 sec)

Count

Um die Anzahl von Einträgen/Reihen in einer Tabelle zu bekommen, kann man COUNT(*) verwenden.

> SELECT COUNT(*) FROM bus;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.001 sec)

Um die Anzahl verschiedener Einträge innerhalb einer Spalte einer Tabelle zu bekommen, kann man COUNT(DISTINCT <attribut>) nutzen.

> SELECT COUNT(DISTINCT bustyp) FROM bus;
+------------------------+
| COUNT(DISTINCT bustyp) |
+------------------------+
|                      2 |
+------------------------+
1 row in set (0.007 sec)

Min, Max, Sum, Avg

Diese mathematischen Operatoren machen genau, was man erwarten würde.

> SELECT
    -> MIN(sitzplaetze) AS "Kleinster Bus",
    -> MAX(sitzplaetze) AS "Größter Bus",
    -> SUM(sitzplaetze) AS "Sitzplätze Ins.",
    -> AVG(sitzplaetze) AS "Sitzplätze Durch."
    -> FROM bus;
+---------------+---------------+------------------+--------------------+
| Kleinster Bus | Größter Bus   | Sitzplätze Ins.  | Sitzplätze Durch.  |
+---------------+---------------+------------------+--------------------+
|            51 |            76 |              183 |            61.0000 |
+---------------+---------------+------------------+--------------------+
1 row in set (0.001 sec)

Select Distinct

Wählt nur einzigartige Elemente aus. Wenn mehrere Spalten gegeben sind, müssen die Spaltentupel einzigartig sein, nicht aber die einzelnen Attribute.

> SELECT bustyp FROM bus;
+----------+
| bustyp   |
+----------+
| MAN      |
| Mercedes |
| Mercedes |
+----------+
3 rows in set (0.000 sec)

> SELECT DISTINCT bustyp FROM bus;
+----------+
| bustyp   |
+----------+
| MAN      |
| Mercedes |
+----------+
2 rows in set (0.001 sec)

Group By

GROUP BY kann bei den Aggregatfunktionen (COUNT, MAX, MIN, SUM, und AVG) genutzt werden, um Werte voneinander zu trennen.

> SELECT bustyp, SUM(sitzplaetze) FROM bus
    -> GROUP BY bustyp;
+----------+------------------+
| bustyp   | SUM(sitzplaetze) |
+----------+------------------+
| MAN      |               76 |
| Mercedes |              107 |
+----------+------------------+
2 rows in set (0.001 sec)

Having

Die HAVING Klausel kann benutzt werden, um GROUP BY Ergebnisse weiter einzuschrenken. Im Gegensatz zu WHERE wird die Bedingung angewand, nachdem die Gruppierung vorgenommen wurde.

> SELECT bustyp, SUM(sitzplaetze) AS "total" FROM bus
    -> GROUP BY bustyp HAVING total >= 100;
+----------+-------+
| bustyp   | total |
+----------+-------+
| Mercedes |   107 |
+----------+-------+
1 row in set (0.001 sec)

Limit

LIMIT beschränkt die maximale Anzahl an Zeilen, welche zurückgegeben werden können.

> SELECT * FROM bus LIMIT 2;
+-----+----------+-------------+---------+
| kfz | bustyp   | sitzplaetze | baujahr |
+-----+----------+-------------+---------+
|   1 | MAN      |          76 |    2021 |
|   2 | Mercedes |          56 |    2023 |
+-----+----------+-------------+---------+
2 rows in set (0.001 sec)

Order By

Sortiert die zurückgegebenen Zeilen nach dem Wert eines Attributs. Mögliche Richtungen werden durch ein ASC order DESC angegeben. ASC ist der Standardwert.

> SELECT * FROM bus ORDER BY kfz DESC;
+-----+----------+-------------+---------+
| kfz | bustyp   | sitzplaetze | baujahr |
+-----+----------+-------------+---------+
|   3 | Mercedes |          51 |    2023 |
|   2 | Mercedes |          56 |    2023 |
|   1 | MAN      |          76 |    2021 |
+-----+----------+-------------+---------+
3 rows in set (0.001 sec)

Join

Für diese Beispiele wurden zudem die folgenden Relationen deklariert und mit Werten befüllt:

Fahrer(_Name_, Strasse, PLZ, Ort, Telefon)
Fahrt(_FNr_, Reiseziel, Datum, Dauer, Preis, ↑Kfz, ↑Name)

Natural Join

Siehe Natural Join.

> SELECT DISTINCT name, kfz, strasse FROM fahrt
    -> NATURAL JOIN fahrer ORDER BY kfz;
+---------+------+--------------+
| name    | kfz  | strasse      |
+---------+------+--------------+
| Emily   |    1 | Hans Str.    |
| Jürgen  |    2 | Hanauer Str. |
| Ahmed   |    3 | Dorf Str.    |
+---------+------+--------------+
3 rows in set (0.001 sec)

Probleme mit Natural Join

Wenn möglich sollte der natural join vermieden werden, da man bie diesem ausversehen unabhängige Spalten miteinander verbinden kann, was einerseits zu Performanceproblemen, anderseits aber auch zu unvollständigen Daten führen kann.

Equi Join

Siehe Equi Join.

Der Equi Join (oder auch nur Join) verbindet zwei Tabellen anhang übereinstimmender Werte an einer explizit gewählten Spalte.

> SELECT DISTINCT fahrer.name, fahrt.kfz, fahrer.strasse
    ->  FROM fahrt JOIN fahrer ON fahrer.name=fahrt.name ORDER BY kfz;
+---------+------+--------------+
| name    | kfz  | strasse      |
+---------+------+--------------+
| Emily   |    1 | Hans Str.    |
| Jürgen  |    2 | Hanauer Str. |
| Ahmed   |    3 | Dorf Str.    |
+---------+------+--------------+
3 rows in set (0.001 sec)

JOIN hat weitere alternativen:

• (INNER) JOIN: gibt nur Reihen zurück, wo beide Werte übereinstimen.
• LEFT (OUTER) JOIN: gibt alle Reihen aus der linken Tabelle zurück, aber nur Werte aus der Rechten, falls es eine Übereinstimmung gibt.
• RIGHT (OUTER) JOIN: entgegengesetze Operation zu LEFT JOIN.
• FULL (OUTER) JOIN: gibt alle Reihen zurück, falls es eine Übereinstimmumg gibt.

Vergleichsoperatoren

Like

Vergleicht den textuellen Wert eines Attributes mit einem Regex-ähnlichen Muster. Es gibt zwei Typen an Wildcards:

• %: ähnlich wie Regex .*.
• _: ähnlich wie Regex ..
• [abc]: Einer der gelisteten Buchstaben.
• [a-z]: Buchstabe zwischen Anfangs- und Endbuchstabe.

> SELECT DISTINCT bustyp FROM bus
    -> WHERE bustyp LIKE "Me%";
+----------+
| bustyp   |
+----------+
| Mercedes |
+----------+
1 row in set (0.001 sec)

LIKE kann auch negiert werden, indem man ein NOT LIKE daraus macht.

> SELECT DISTINCT bustyp FROM bus
    -> WHERE bustyp NOT LIKE "Me%";
+--------+
| bustyp |
+--------+
| MAN    |
+--------+
1 row in set (0.001 sec)

Between

Kontrolliert, ob ein Zahlenwert innerhalb einer Zahlenreihe vorhanden ist. Beide Begrenzungen sind inklusiv.

> SELECT * FROM bus
    -> WHERE sitzplaetze BETWEEN 50 AND 70;
+-----+----------+-------------+---------+
| kfz | bustyp   | sitzplaetze | baujahr |
+-----+----------+-------------+---------+
|   2 | Mercedes |          56 |    2023 |
|   3 | Mercedes |          51 |    2023 |
+-----+----------+-------------+---------+
2 rows in set (0.001 sec)

BETWEEN kann auch zu NOT BETWEEN negiert werden.

In

Kontrolliert, ob ein Zahlenwert in einer Liste von Nummern vorkommt.

> SELECT * FROM bus
    -> WHERE kfz IN (1,3);
+-----+----------+-------------+---------+
| kfz | bustyp   | sitzplaetze | baujahr |
+-----+----------+-------------+---------+
|   1 | MAN      |          76 |    2021 |
|   3 | Mercedes |          51 |    2023 |
+-----+----------+-------------+---------+
2 rows in set (0.008 sec)

Is Null

IS NULL kontrolliert, ob ein Wert NULL, bzw. keinen Wert hat. Kehrwert hierbei wäre IS NOT NULL.

> SELECT * FROM bus
    -> WHERE kfz = 1 AND kfz IS NOT NULL;
+-----+--------+-------------+---------+
| kfz | bustyp | sitzplaetze | baujahr |
+-----+--------+-------------+---------+
|   1 | MAN    |          76 |    2021 |
+-----+--------+-------------+---------+
1 row in set (0.001 sec)

Verschachtelte Selects

Wenn man Daten abhängig von einer anderen Auswahl auswählen möchte, braucht man verschachtelte SELECT-Abfragen. Ein mögliches Beispiel wäre die Frage “Welcher Bus hat die kleinste Anzahl an Sitzplätzen”. Hierzu fragt man zuerst ab, was die kleinste Anzahl an Sitzplätzen ist, und fragt dann nach den Bus-daten ab, welcher diese genaue Anzahl an Sitzplätzen hat.

> SELECT * FROM bus WHERE
    -> sitzplaetze = (SELECT MIN(sitzplaetze) FROM bus);
+-----+----------+-------------+---------+
| kfz | bustyp   | sitzplaetze | baujahr |
+-----+----------+-------------+---------+
|   3 | Mercedes |          51 |    2023 |
+-----+----------+-------------+---------+
1 row in set (0.001 sec)

Datenmodifikation

Insert

> INSERT INTO bus (kfz, bustyp, sitzplaetze, baujahr)
    -> VALUES (1, "MAN", 76, 2021);
Query OK, 1 row affected (0.008 sec)

Das Ergebnis der Operation ist bei Select absehbar.

Update

Vorsicht

UPDATE Klauseln sollten IMMER mit einer WHERE Bedingung eingeschränkt sein. Falls sie dies nicht sind, wird das Update sich auf jede Zeile auswirken, was typischerweise nicht gewollt ist.

> UPDATE bus SET baujahr = 2024 WHERE kfz = 2;
Query OK, 1 row affected (0.009 sec)
Rows matched: 1  Changed: 1  Warnings: 0

> SELECT kfz, baujahr FROM bus WHERE kfz = 2;
+-----+---------+
| kfz | baujahr |
+-----+---------+
|   2 |    2024 |
+-----+---------+
1 row in set (0.001 sec)

Delete From

Vorsicht

Auch DELETE FROM Klauseln sollten IMMER mit einer WHERE Bedingung eingeschränkt sein. Sonst werden ALLE Einträge in einer Tabelle gelöscht.

> DELETE FROM bus WHERE kfz = 4;
Query OK, 1 row affected (0.003 sec)

> SELECT * FROM bus WHERE kfz = 4;
Empty set (0.000 sec)