MySQL-Zertifizierung, Kapitel 11 komplett

11.2 The INSERT Statement

Mit dem Befehl INSERT werden neue Datensätze zu einer Tabelle hinzugefügt. Die Syntax lautet INSERT INTO tbl (spalte1, spalte2, spalte3) VALUES (Wert1, Wert2, Wert3)


Folgende Beispiele erläutern den Befehl INSERT:

# Ausgeschriebene Schreibweise
INSERT INTO tbl01 SET name = 'Müller', alter = 25, groesse = 177;

# Kurze Schreibweise
INSERT INTO tbl01 (name, alter, groesse) VALUES ('Müller', 25, 177);

In der Regel wird die kurze Schreibweise verwendet. Diese lässt sich dann sogar weiter verkürzen, wenn die Anzahl der Values mit der Anzahl der Spalten übereinstimmt. Dann kann die Angabe der Spalte weggelassen werden. Wenn die Anzahl nicht übereinstimmt, quittiert MySQL dies in folgender Schreibweise mit einer Fehlermeldung.

# Beispiel: Tabelle mit drei Spalten (name, alter, groesser)
# Kurzschreibweise mit übereinstimmender Anzahl an Values
INSERT INTO tbl01 VALUES ('Müller', 25, 177);

Werden beim Anlegen eines neuen Datensatzes Spalten nicht angegeben, werden diese mit den Default-Werten aufgefüllt. Um eine Zeile nur mit den Default-Werten anzulegen, gibt man folgendes ein:

# Zeile mit Default-Werten anlegen
INSERT INTO tbl01 () VALUES ();

11.2.1 Adding Multiple Records with a Single INSERT Statement

Mit dem INSERT-Befehl können auch mehrere Einträge gleichzeitig gespeichert werden. Dabei werden die einzelnen Datensätze einfach in runde Klammern gruppiert. Die Syntax lautet folgendermaßen: INSERT INTO tbl (spalte1, spalte2, spalte3) VALUES (Wert1, Wert2, Wert3), (Wert1, Wert2, Wert3), (Wert1, Wert2, Wert3), ... ;


Folgendes Beispiel speichert die Werte 1-5 in der Spalte id. Jeder Wert steht für eine neue Zeile:

INSERT INTO tbl (id) VALUES (1), (2), (3), (4), (5);

Die Muliple-Row-Inserts sind effektiver als Single-Row-Inserts, da der MySQL-Server diese besser verarbeiten kann.

11.2.2 Handling Duplicate Key Values

Was passiert, wenn man eine Tabelle hat, welche einen unique-Index besitzt (bsp. eine Spalte ID als unique definiert) und nun beim INSERT ein doppelter Eintrag gespeichert werden möchte? Beispiel: Ein neuer Datensatz hat als ID 562 und ein bereits exisitierender Eintrag hat dieselbe ID.


MySQL verarbeitet doppelte Key Values folgendermaßen:


1.) Standard: der neue Datensatz wird verworfen und MySQL zeigt eine Fehlermeldung

2.) INSERT IGNORE: damit wird MySQL angehalten, den Datensatz zu verwerfen, aber keine Fehlermeldung zu erzeugen

3.) ON DUPLICATE KEY UPDATE: Bei doppelten Key Value wird der Wert automatisch verändert

11.2.3 Using INSERT ... ON DUPLICATE KEY UPDATE

Das Schlüsselwort ON DUPLICATE KEY UPDATE kann man sinngemäß folgendermaßen beschreiben: "Wenn Du einen neuen Datensatz einfügst, derselbe Datensatz aber bereits existiert, dann aktualisiere den gefundenen Datensatz. Ansonsten lege den neuen Datensatz wie gewohnt an."


Diese Funktion ist also dann praktisch, wenn man eine Tabelle für regelmäßige Aktualisierungen verwendet, und man nicht umständlich mit SELECT nach einem vorhandenen Datensatz suchen, mit UPDATE diesen bearbeiten und wenn doch kein passender Datensatz gefunden wurde mit INSERT einen neuen Datensatz anlegen möchte. Diese würde eine nicht sinnvolle Verkettung von Bedingungen bedeuten.


In folgendem Beispiel soll die Anzahl gespeichert werden, wie oft sich Anwender einloggen. Hierbei werden neue Datensätze gespeichert, wenn die Anwender noch nicht existieren, oder der vorhandene Datensatz aktualisiert, wenn bereits ein Eintrag existiert.

mysql> CREATE TABLE logging (
    -> name CHAR(30),
    -> logs INT,
    -> PRIMARY KEY (name)
    -> );

Für dieses Beispiel wird eine einfache Tabelle mit den Spalten name und logs angelegt, wobei die Spalte name als PRIMARY KEY verwendet wird. Diese Tabelle wird testweise mit Daten gefüllt (alle Anwender haben sich erst einmal eingeloggt):

mysql> SELECT * FROM logging;
+-------+------+
| name  | logs |
+-------+------+
| peter |    1 | 
| hans  |    1 | 
| anja  |    1 | 
+-------+------+

Jetzt kommt ON DUPLICATE KEY UPDATE zum Einsatz. Wenn ein Eintrag mit INSERT eingefügt wird und dieser Name bereits existiert, soll die Spalte logs um 1 erhöht werden (logs=logs+1). So ist es auf eine einfache Art und Weise möglich, vorhandene Datensätze zu aktualisieren und neue Datensätze einzutragen.

mysql> INSERT INTO logging (name, logs)
    -> VALUES ('hans', 1)
    -> ON DUPLICATE KEY UPDATE logs=logs+1;

mysql> SELECT * FROM logging;                                                   
+-------+------+
| name  | logs |
+-------+------+
| peter |    1 | 
| hans  |    2 | 
| anja  |    1 | 
+-------+------+

Wenn der User noch nicht existiert, wird die ON DUPLICATE KEY UPDATE Anweisung ignoriert und der Datensatz normal angelegt.

11.3 The REPLACE Statement

Das Schlüsselwort REPLACE hat dieselbe Syntax wie INSERT. Der Hauptunterschied besteht darin, wie doppelte Key Values behandelt werden. So ist bei REPLACE die Anweisung ON DUPLICATE KEY UPDATE nicht möglich.


REPLACE arbeitet folgendermaßen:


1.) Datensatz noch nicht vorhanden: Datensatz wird wie gewohnt angelegt

2.) Datensatz vorhanden: Datensatz wird gelöscht und mit neuen Werten angelegt


Aus diesem Grund kann man sagen, dass REPLACE eine Kombination aus DELETE (wenn benötigt) und INSERT ist.

# Ausgeschriebene Schreibweise
REPLACE INTO tbl01 SET name = 'Müller', alter = 25, groesse = 177;

# Kurze Schreibweise
REPLACE INTO tbl01 (name, alter, groesse) VALUES ('Müller', 25, 177);

# Mehrere Datensätze aktualisieren
REPLACE INTO tbl (id) VALUES (1), (2), (3), (4), (5);

Ein Datensatz wird für REPLACE dann als doppelter Key Value identifiziert, wenn ein Wert mit dem unique-Index-Wert übereinstimmt.


Die besondere Funktionsweise von REPLACE wird anhand folgendes Beispiel verdeutlicht. Eine Tabelle, die nur aus UNIQUE-Indizes besteht: 

mysql> CREATE TABLE multikey (
    -> i INT NOT NULL UNIQUE,
    -> k INT NOT NULL UNIQUE,
    -> y INT NOT NULL UNIQUE
    -> );
mysql> SELECT * FROM multikey;
+---+---+---+
| i | k | y |
+---+---+---+
| 1 | 1 | 1 | 
| 2 | 2 | 2 | 
| 3 | 3 | 3 | 
| 4 | 4 | 4 | 
+---+---+---+

Wenn man jetzt den REPLACE Befehl auf alle drei Spalten anwendet, werden die ersten drei Spalten gelöscht und eine einzige Spalte angelegt:

mysql> REPLACE INTO multikey (i,k,y) VALUES (1,2,3);
Query OK, 4 rows affected (0.00 sec)

mysql> SELECT * FROM multikey;
+---+---+---+
| i | k | y |
+---+---+---+
| 1 | 2 | 3 | 
| 4 | 4 | 4 | 
+---+---+---+

11.4 The UPDATE Statement

UPDATE wird dazu benutzt, um Werte in einer Tabelle zu ändern. Hierbei ist es notwendig, einen Datensatz eindeutig zu identifizieren, damit dieser geändert werden kann. Dies erfolgt in der Regel mit der WHERE-Klausel:

# Datensatz ändern
UPDATE tbl01 SET name = 'Peter' WHERE id= 30;

# Mehrere Daten ändern
UPDATE tbl01 SET name = 'Peter', alter = 17 WHERE id= 30;

# Das Alter ALLER Datensätze auf 18 setzen
UPDATE tbl01 SET alter = 18;

# Das Alter um 1 erhöhen
UPDATE tbl01 SET alter =  alter+1;

11.4.1 Using UPDATE with ORDER BY and LIMIT

Die UPDATE Anweisung kann mit ORDER BY und LIMIT kombiniert werden, um bsp. Änderungen auf bestimmte Datensätze einzugrenzen.


ORDER BY kann beispielsweise eingesetzt werden, wenn man die IDs der Datensätze um 1 verringern möchte. Wenn die Datensätze durcheinander sind und MySQL versucht, die ID 3 auf 2 zu verringern, die ID 2 aber noch existiert, gibt es eine Fehlermeldung. In diesem Fall muss man die Datensätz nach der ID sortieren, um dann die IDs zu ändern:

UPDATE tbl01 SET id = id-1 ORDER BY id;

LIMIT könnte man beispielsweise zusätzlich anwenden, wenn man nur die ersten fünf ID's ändern möchte:

UPDATE tbl01 SET id = id-1 ORDER BY id LIMIT 5;

11.5 The DELETE and TRUNCATE TABLE Statements

Die Anweisungen DELETE und TRUNCATE TABLE werden dazu benutzt, um Datensätze in Tabellen zu löchen. Der Unterschied zwischen DELETE und TRUNCATE TABLE ist, dass man mit TRUNCATE TABLE ausschließlich alle Datensätze löschen kann. Mit DELETE kann man auch einzelne Datensätze löschen oder die Löschanweisung auf bestimmte Datensätz begrenzen.

# Diese Anweisung leeren eine Tabelle,
# also alle Datensätze werden gelöscht
DELETE FROM tbl01;
TRUNCATE TABLE tbl01;
TRUNCATE tbl01;

Um bestimmte Datensätze zu löschen, muss man die DELETE-Funktion mit WHERE kombinieren:

# Löschen eines einzelnen Datensatzes
DELETE FROM tbl01 WHERE id = 5;

DELETE

- kann einzelne Datensätze löschen

- ist langsamer als TRUNCATE

11.5.1 Using DELETE with ORDER BY and LIMIT

Wie beim REPLACE-Befehl kann man DELETE mit ORDER BY und LIMIT kombinieren. Folgendes Beispiel soll dies verdeutlichen.

# Löschen der ersten 5 Datensätze sortiert nach der id
DELETE FROM tbl01 ORDER BY id LIMIT 5;

11.6 Privileges Required for Update Statements

Welche Rechte (privileges) benötigt ein SQL-User, um die in Kapitel 11 erläuterten Anweisungen durchführen zu können?


- INSERT, UPDATE und DELETE benötigen INSERT-, UPDATE- und DELETE-Rechte

- REPLACE benötigt DELETE- und INSERT-Rechte

- TRUNCATE TABLE benötigt DELETE-Rechte