Nach Anlegen eines neuen Datensatzes wird die aktuelle Zeit automatisch gesetzt. Dieses geschieht nur beim Anlegen, nicht wenn sich ein Datensatz ändert!

INSERT INTO zeit (txt) VALUES ('Hier steht text...');

mysql> SELECT * FROM zeit;

Hier sieht man sehr gut, daß automatisch nach dem Anlegen des Datensatzes das aktuelle Datum gespeichert wird.

Es ist aber auch möglich, daß ein Feld nur bei Änderungen aktualisiert wird. Dazu verwendet man beim Anlegen das Attribut ON UPDATE CURRENT_TIMESTAMP.

MySQL zeigt eine Fehlermeldung an, wenn man beides miteinander verbindet, also DEFAULT CURRENT_TIMESTAMP mit ON UPDATE CURRENT_TIMESTAMP. Wenn man beides nutzen möchte, muß man die Tabelle folgendermaßen erstellen:

CREATE TABLE zeit01 (
tbl01 TIMESTAMP DEFAULT 0,
tbl02 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
txt TINYTEXT );


Beim Anlegen des Datensatzes (INSERT, REPLACE) wird nur Spalte tbl01 aktualisiert. Wird ein Datensatz geändert (UPDATE), ändert sich Spalte tbl02:

UPDATE zeit01 SET txt="Text (aktualisiert)";

SELECT * FROM zeit01;

Column Attributes
Beim Anlegen einer neuen Spalte kann man optional weitere Attribute festlegen. Somit kann man beeinflussen, wie MySQL die Spalte "behandelt". Attribute können sein, welche Default-Werte, welcher Zeichensatz oder welche Sortierreihenfolge (Collation) verwendet werden sollen. Die Angabe von Attributen ist nicht zwingend erforderlich. Welche Attribute man nutzen kann ist abhängig von dem verwendetem Datentyp.

CREATE TABLE irgendwas
(
spalte1 INT UNSIGNED NOT NULL,
spalte2 CHAR(30) CHARACTER SET utf8,
spalte3 DATETIME DEFAULT '2008-12-01 00:30:01'
);

Spalte1 = Datentyp INT, keine negativen Werte möglich, kein Nullwerte möglich
Spalte2 = Datentyp CHAR, verwendeter Zeichensatz ist utf8
Spalte3 = Datentyp DATETIME mit vorgegebenen Default-Wert

Numeric Column Attributes
Folgende Attribute können bei numerischen Datentypen wie INT oder FLOAT angegeben werden:

UNSIGNED = Der Wertebereich ist nur für positive Zahlen gültig. Negative Zahlen sind nicht erlaubt.
ZEROFILL = Leere oder fehlende Zahlen werden automatisch mit Nullen aufgefüllt.

Beispiel:

INT(4) ZEROFILL: Speichert man die Zahlen 5, 35 und 4523, so werden diese als 00005, 00035 und 04523 gespeichert und angezeigt.
AUTO_INCREMENT : Wird dazu benutzt, um eindeutige Zahlen einem Datensatz zuzuweisen. Dabei wird bei einem neuen Eintrag
                 der Eintrag um eins erhöht. Dieses wird beispielsweise bei einem Index verwendet, so daß jeder Datensatz über eine ID erreichbar ist.

String Column Attributes
Folgende Attribute können bei string-Datentypen wie CHAR oder TEXT angegeben werden:

CHARACTER SET = Festlegen des Zeichensatzes (utf8, latin1, etc)
COLLATE       = Sortierreihenfolge festlegen. Dieser muß vom Zeichensatz unterstützt werden.
BINARY        = Binäre Sortierreihenfolge kann bei nicht-binären Zeichensätzen (CHAR, TEXT, etc) aktiviert werden


SHOW CHARACTER SET;

General Column Attributes

Folgende Attribute können auf alle Datentypen angewandt werden:

NULL oder NOT NULL = Legt fest, ob eine Spalte NULL-Werte enthalten darf oder nicht (NOT NULL).
                     Der NULL-Wert bedeutet "Keine Daten". Folgende Aussage aus dem offiziellen MySQL-Handbuch soll
                     dieses Attribut näher erläutern.

NULL ist nicht dasselbe wie der leere String ''!. Die folgenden Anweisungen sind beispielsweise völlig verschieden:

mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ('');

Beide Anweisungen fügen einen Wert in die phone-Spalte ein, die erste allerdings einen NULL-Wert und die zweite einen leeren String.
Die erste Eingabe bedeutet so viel wie "Telefonnummer unbekannt" und die zweite bedeutet "Die Person hat kein Telefon und somit keine Telefonnummer".

Beispiel:

CREATE TABLE test ( abc INT NOT NULL);

DEFAULT = Mit diesem Attribut kann man einen Standard-Wert festlegen, der dann automatisch gespeichert wird, wenn beim Einfügen eines neuen Datensatzes kein Wert angegeben wird.

- DEFAULT kann bei allen Datentypen außer TEXT und BLOB verwendet werden
- Bei einer NOT NULL Spalte ist der Default-Wert NULL nicht erlaubt
- DEFAULT-Werte müssen innerhalb des Wertebereichs sein.

UNIQUE
Erzeugt einen Index, bei dem ein Wert nur einmal vorkommen darf

PRIMARY KEY
Wird in der Regel dazu benutzt, einen eindeutigen Schlüssel (ID) für einen Datensatz zu generieren. Somit kann es nicht zu Verwechslungen kommen.

Beispiel:

CREATE TABLE test ( abc INT NOT NULL PRIMARY KEY);
CREATE TABLE test ( abc INT NOT NULL, PRIMARY KEY (abc));

Using the AUTO_INCREMENT Column Attribute

Das Attribut AUTO_INCREMENT kann zu einem numerischen Datentyp hinzugefügt werden. Es ermöglicht, daß Zahlen automatisch hochgezählt werden, um sie beispielsweise als Index zu verwenden. Die Spalte muß in diesem Fall natürlich als NOT NULL definiert werden.

Beispiel:

CREATE TABLE fussball
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
verein CHAR(100),
platz TINYINT,
PRIMARY KEY (id)
);


Dieses Beispiel zeigt eine Tabelle, welche eine Spalte als Index verwendet (id). Diese wird automatisch hochgezählt. Mit PRIMARY KEY (id) wird festgelegt, welche Spalte der Primärschlüssel ist. Der Wert in id ist also einzigartig und darf nicht doppelt vorkommen.

Möchte man einen Index erstellen, der auch doppelte Werte enthalten darf, so wählt man das Attribut INDEX.

Folgendes sollte man bei AUTO_INCREMENT beachten:

- Die Spalte muß als INT deklariert werden
- Die Spalte muß als UNSIGNED deklariert werden, da logischerweise nur positive Werte Sinn machen.
- AUTO_INCREMENT wird in der Regel als eindeutiger Index verwendet, daher in Verbindung mit PRIMARY KEY oder UNIQUE angelegt.
- Mit der Funktion LAST_INSERT_ID() lässt sich herausfinden, welcher Eintrag zuletzt gespeichert wurde:

SELECT * FROM fussball WHERE id = LAST_INSERT_ID();


- Gelöschte Datensätze wirken sich nicht auf das Hochzählen aus. Wenn Datensätze von 1-10 existieren, und Nr.10 gelöscht wird,
  so hat der nächste neue Datensatz den Wert 11 und nicht 10.

Database Properties

Jede Datenbank wird durch ein Verzeichnis repräsentiert:
 
# ls -1 /var/lib/mysql/
lokal
mysql
programm
t3_gus
t3_redaktion
t3_testseite


In jedem Datenbank-Verzeichnis werden die Tabellen gespeichert:

# ls -1 /var/lib/mysql/t3_gus/
be_groups.MYD
be_groups.MYI
be_groups.frm

be_sessions.MYD
be_sessions.MYI
be_sessions.frm

be_users.MYD
be_users.MYI
be_users.frm


Jede Datenbank hat einen Standard-Zeichensatz (character set) und -Sortierreihenfolge (collation).

MySQL verwendet den Begriff "schema" als Synonym zu "database". CREATE SCHEMA entspricht CREATE DATABASE. In der Regel verwendet man den Begriff database, aber in einigen grafischen Administrationstools werden die Datenbanken als schema beschrieben. Wenn man auf den Begriff schema trifft, sollte man diesen als database erkennen.

-> Schema = Database

Creating Databases
Beim Erstellen neuer Datenbanken kann man auch einige Optionen angeben.
Das einfache Anlegen einer neuen Datenbank erfolgt folgendermaßen:

CREATE DATABASE db_01;

Wenn eine Datenbank bereits existiert, erscheint eine Fehlermeldung. Um dies zu verhindern, kann man die Option IF NOT EXIST mit angeben. Hierbei wird die Datenbank nur dann angelegt, wenn sie noch nicht existiert:

CREATE DATABASE IF NOT EXIST db_01;

Des Weiteren kann man der Datenbank eine einen Standard-Zeichensatz (character set) und -Sortierreihenfolge (collation) mitgeben, welche dann für die neuen Tabellen gilt:

CREATE DATABASE db_01 CHARACTER SET utf8 COLLATION utf8_german_1;

Altering Databases

Mit dem Befehl ALTER DATABASE können Änderungen an der Datenbank vorgenommen werden.
Folgender Befehl ändert die Sortierreihenfolge:

ALTER DATABASE db_01 COLLATE utf_danish_ci;

Des Weiteren kann man auch den Zeichensatz ändern:

ALTER DATABASE db_01 CHARACTER SET latin1;

Obtaining Database Metadata

Nach einer MySQL-Instalation wird automatisch die Datenbank INFORMATION_SCHEMA angelegt. Diese Datenbank enthält Metadaten über alle Datenbanken und ist quasi die Informationsdatenbank bzw. Systemkatalog von MySQL.


SHOW TABLES FROM INFORMATION_SCHEMA;

Wichtige Tabellen in dieser Datenbank sind beispielsweise SCHEMA_PRIVILEGES, welche die Zugriffsrechte auf die einzelnen Datenbanken enthält, oder die CHARACTER_SETS, welche die verfügbaren Zeichensätze anzeigt. Hier die Übersicht, welche Tabelle welchen Zweck hat:

CHARACTER_SETS                         = Infos über verfügbare Zeichensätze
COLLATIONS                             = Infos über verfügbare Sortierreihenfolgen der Zeichensätze
COLLATION_CHARACTER_SET_APPLICABILITY  = Welcher Zeichensatz gilt für welche Kollation?
COLUMNS                                = Informiert über die Spalten der Tabellen
COLUMN_PRIVILEGES                      = Spaltenberechtigungen
KEY_COLUMN_USAGE                       = Infos über Schlüsselspalten
ROUTINES                               = Infos über gespeicherte Routinen
SCHEMATA                               = Infos über die einzelnen Datenbanken
SCHEMA_PRIVILEGES                      = Berechtigungen auf die Datenbanken
STATISTICS                             = Informationen über Tabellenindizes
TABLES                                 = Infos über einzelne Tabellen
TABLE_CONSTRAINTS                      = Zeigt an, welche Tabellen Constraints unterliegen
TABLE_PRIVILEGES                       = Tabellenberechtigungen
TRIGGERS                               = Infos über Trigger
USER_PRIVILEGES                        = Benutzerberechtigungen
VIEWS                                  = Infos über Views in Datenbanken


Beispiel:

USE information_schema;
SELECT * FROM SCHEMATA;

Table Properties

Datenbanken bestehen aus Tabellen und Tabellen wiederum aus Datensätzen. Physikalisch gesehen speichert MySQL Tabellen in Form von Dateien in den Datenbank-Verzeichnissen:

# ls -1 /var/lib/mysql/t3_gus/
be_groups.MYD
be_groups.MYI
be_groups.frm

be_sessions.MYD
be_sessions.MYI
be_sessions.frm

be_users.MYD
be_users.MYI
be_users.frm

In diesem Beispiel erkennt man die Daten der Tabellen be_groups, be_sessions und be_users. MySQL verwaltet die Tabellen Mithilfe von storage engines (Speicher-Engines), die sich in bestimmten Eigenschaften unterscheiden. Somit hat man für die verschiedenen Einsatzzwecke mit ihren spezifischen Anforderungen die Wahl zwischen mehreren Speicherroutinen.

MyISAM Speicherengine
Die MyISAM-Engine ist weit verbreitet und speichert Tabellen in drei Dateien: *.frm (Tabellenformat), *.MYD (Datendatei) und *.MYI (Indexdatei), wie man in oben genannten Beispiel erkennen kann. Mit myisamchk können Tabellen geprüft und repariert werden.

InnoDB Speicherengine
Eine weitere Speicherengine ist InnoDB. Auf den offiziellen MySQL-Seiten kann man folgendes zu dieser Speicher-Engine lesen:
InnoDB wurde für maximale Leistung bei der Verarbeitung großer Datenmengen ausgelegt. Es gibt wohl keine andere festplattengestützte Speicher-Engine für relationale Datenbanken, die so effizient mit der CPU umgeht. InnoDB wird in einer Vielzahl großer Produktionsdatenbanken eingesetzt, die hohe Anforderungen an die Leistung stellen.

MEMORY Speicherengine
Die Memory-Engine speichert keine Daten auf der Festplatte, sondern verwaltet die Tabellen im Arbeitsspeicher. Nur die Tabellenbeschreibung wird auf der Festplatte gespeichert.

Weitere Speicherengines
Neben den hier genannten Speicherengines gibt es noch MERGE-, BDB-, EXAMPLE-, FEDERATED-, oder ARCHIVE-Speicherengines.

Um eine Tabelle mit einer bestimmten Speicherengine zu erstellen, gibt man dies bei Erstellung an:

CREATE TABLE t (i INT) ENGINE = MYISAM;
CREATE TABLE t (i INT) ENGINE = MEMORY;
CREATE TABLE t (i INT) ENGINE = InnoDB;
 

Creating Tables

Eine Tabelle muß zwingend einer Datenbank angehören und kann nicht "alleine existieren". Beim Erstellen einer Tabelle können Optionen angegeben werden. Hier das wichtigste im Überblick

# Einfaches Erstellen einer Tabelle
CREATE TABLE tbl_01 (id INT);

# Erstellen einer Tabelle, wenn sie noch nicht existiert
CREATE TABLE IF NOT EXISTS tbl_01 (id INT);

# Explizite Angabe der Datenbank
CREATE TABLE db001.tbl_01 (id INT);

# Angabe der Speicher-Engine beim Anlegen einer Tabelle
CREATE TABLE tbl_01 (id INT) ENGINE = InnoDB;

# Speicher-Engine einer vorhandenen Tabelle ändern/konvertieren
ALTER TABLE tbl_01 ENGINE = MyISAM

# Die Standard-Engine beim Start von MySQL festlegen
mysqld --default-storage-engine InnoDB

# Standard-Engine während des MySQL-Betriebs für Clients ändern
mysql> SET GLOBAL Storage_engine = InnoDB;

# Ein Client kann die Speicher-Engine mit folgenden Befehlen ändern
mysql> SET SESSION storage_engine = InnoDB;
       SET storage_engine = InnoDB;

Creating Table Based on Exisiting Tables

Es gibt in MySQL keinen Kopierbefehl für Tabellen. Wenn man die Struktur oder den Dateninhalt einer bestehenden Tabelle für eine neue Tabelle verwenden möchte, muß man sich aus der Kombination der Befehle CREATE und SELECT oder LIKE behelfen:

# Erstellen einer leeren Tabelle mit der Struktur einer existierenden
CREATE TABLE tbl_01 LIKE tbl_population;

# Kopieren einer Tabelle
CREATE TABLE tbl_01 SELECT * FROM tbl_population;

# Kopieren einer Tabelle (nur bestimmte Datensätze)
CREATE TABLE tbl_01 SELECT * FROM tbl_population WHERE people > 10000;

Die Beispiele zeigen, daß man beim Kopieren einer Tabelle mit dem Befehl SELECT die gewünschten Datensätze in die neue Tabelle übernehmen kann.

Using TEMPORARY Tables

Eine temporäre Tabelle wird wie der Name schon sagt dafür gebraucht, um z.B. für Testzwecke eine Tabelle zu erstellen, die anschließend mit DROP TABLE gelöscht wird. Um diesen Vorgang zu automatisieren, kann man eine temporäre Tabelle anlegen, die automatisch nach Beenden der Client-Verbindung gelöscht wird:

CREATE TEMPORARY TABLE tbl_01 INT;

Folgendes gilt für eine temporäre Tabelle:
- Die Tabelle ist nur für denjenigen sichtbar, der sie erstellt hat
- Eine temporäre Tabelle existiert nur für die Dauer der Verbindung, in der sie erstellt wurde
- Eine temporäre Tabelle kann denselben Namen wie eine nicht-temporäre Tabelle haben
- Eine temporäre Tabelle kann nur mit ALTER TABLE, nicht aber RENAME TABLE umgenannt werden

Altering Tables

Mit dem Befehl ALTER TABLE kann man Tabellen editieren. Folgende Operationen sind hierbei möglich:

- Die Tabelle umbenennen
- Spalten löschen und hinzufügen
- den Name einer Spalte umbenennen
- Indizes löschen und hinzufügen


Adding and Dropping Columns
Folgende Befehle können für das Hinzufügen und Löschen von Spalten in einer Tabelle benutzt werden:

# Hinzufügen einer weiteren Spalte in einer bestehenden Tabelle
ALTER TABLE tbl_01 ADD mitglieder INT;

# Hinzufügen einer weiteren Spalte an den Anfang in einer bestehenden Tabelle
ALTER TABLE tbl_01 ADD mitglieder INT FIRST;

# Hinzufügen einer weiteren Spalte in bestimmter Position
ALTER TABLE tbl_01 ADD mitglieder INT AFTER Vorname;

# Löschen einer Spalte in einer bestehenden Tabelle
ALTER TABLE tbl_01 DROP mitglieder INT;


Modifying Existing Columns

Der Befehl ALTER TABLE in Verbindung mit MODIFY und CHANGE kann dazu genutzt werden, um einzelne Spalten zu editieren:

# Den Datentyp der Spalte mitglieder auf int(15) festlegen
ALTER TABLE zeit MODIFY mitglieder int(15);


Mit CHANGE kann neben den Spalteneigenschaften gleichzeitig auch den Namen der Spalte ändern. Die Syntax ist
ALTER TABLE tbl CHANGE alter_name neuer_name OPTIONEN;

# Den Datentyp der Spalte mitglieder ändern (ohne Umbenennen)
ALTER TABLE zeit CHANGE mitglieder mitglieder int(15);

# Den Datentyp der Spalte mitglieder ändern (mit Umbenennen)
ALTER TABLE zeit CHANGE mitglieder members int(15);

Renaming a Table
Tabellen können folgendermaßen umbenannt werden:

# Einfaches Umbenennen
ALTER TABLE tbl_01 RENAME TO tbl_neu;

# Umbenennen mit RENAME
RENAME TABLE tbl_01 TO tbl_neu;

# Mehrere Tabellen gleichzeitig umbenennen
RENAME TABLE tbl_01 TO tbl_neu, tbl_01 TO vereine, tbl_03 TO staedte;

Dropping Tables
Mit dem Befehl DROP können Tabellen gelöscht werden:

# Löschen einer Tabelle
DROP TABLE tbl_01;

# Löschen mehrerer Tabellen
DROP TABLE t1, t2, t3;

# Nur dann löschen, wenn die Tabelle auch wirklich existiert
DROP TABLE IF EXISTS tbl_01;

Emptying Tables
Tabellen können teilweise oder komplett geleert werden:

# Löschen des Inhalts einer Tabelle
TRUNCATE TABLE tbl_01;

oder:

DELETE FROM tbl_01;

Der Vorteil von DELETE FROM ist, dass mit der WHERE-Bedingung das Löschen auf bestimmte Datensätze begrenzt werden kann, also die Tabelle nur teilweise geleert wird:

# Tabelle teilweise löschen
DELETE FROM tbl_01 WHERE id > 50;

Indexes

Indizes kann man sich wie Inhaltsverzeichnisse vorstellen, welche die MySQL-Abfragen deutlich beschleunigen können. Gerade bei großen Datenbanken bzw. Tabellen können die Abfragen unakzeptabel langsam sein. Des Weiteren ist es möglich, mit einem Index die einzelnen Datensätze eindeutig zu markieren.

In MySQL gibt es drei Index-Typen:

Primary Key
Werte in einer Spalte, welche als Primary Key deklariert ist, stellen einen eindeutigen Verweis auf einen Datensatz dar. Ein Wert darf in dieser Spalte nur einmal vorkommen und nicht als NOT NULL deklariert sein. In nahezu jeder Tabelle gibt es eine Spalte namens id, welche als Primary Kex deklariert ist.

Unique Index
Ist ähnlich dem Primary Key Index, der dazu benutzt wird, um einen eindeutigen Index einer Zeile zuzuweisen. Der Unterschied ist, das hier auch NULL-Werte angegeben werden dürfen.

Non-Unique Index
Hier dürfen Werte mehrfach vorkommen.

Fulltext Index
Wird für Textsuche benutzt.

Creating Indexes
In folgenden Rubriken wird beschrieben, wie man Indizes erstellt.

Ein Index bei der Erstellung einer Tabelle wird folgendermaßen angelegt:

# Einen non-unique Index erstellen
CREATE TABLE tbl01 (
name CHAR(30),
alt TINYINT,
stadt CHAR(50),
INDEX (name)
);

In diesem Beispiel wir die Spalte "name" als Index verwendet. Dieses ist ein schlechtes Beispiel, weil ein Name mehrmals vorkommen kann. Besser wäre, wenn man das aktuelle Datum oder einen INT-Wert mit auto_increment verwendet.
Es ist auch möglich, mehrere Indizes in einer Tabelle zu verwenden. Man spricht hier von einem Composite Index, sprich einem kombinierten Index aus mehreren Spalten.

# Composite Index aus mehrere Spalten erstellen
CREATE TABLE tbl01 (
name CHAR(30),
vorname CHAR(30),
alt TINYINT,
stadt CHAR(50),
INDEX (name, vorname)
);

Um einen Unique-Index zu erstellen, verwendet man das Schlüsselwort UNIQUE. Somit ist ausgeschlossen, daß man doppelte Werte im Index hat:

# Unique-Index erstellen
CREATE TABLE tbl01 (
id INT,
name CHAR(30),
vorname CHAR(30),
alt TINYINT,
stadt CHAR(50),
UNIQUE (id)
);

Alternativ zu Unique kann man auch PRIMARY KEY verwenden. Man sollte sich aber bei den Unterschieden im Klaren sein.

Unterschied UNIQUE und PRIMARY KEY

- Bei Unique können auch mehrere NULL-Werte vorkommen. Bei PRIMARY KEY sind Null-Werte nicht erlaubt.
- Eine Tabelle kann maximal nur einen Spalte als PRIMARY KEY besitzen. Anders ist es bei UNIQUE, hier können mehrere Spalten als Unique definiert werden.
=> Eine einzelne UNIQUE-Spalte, die keine Nullen enthalten darf, ist equivalent zu PRIMARY KEY

Creating and Using Primary Keys

Mit folgenden Befehlen erstellt man eindeutige Indizes, so daß eine Spalte über genau einen Wert angesprochen werden kann.
Verwechslungen bei SELECT, UPDATE oder DELETE können nahezu ausgeschlossen werden.

# Spalte id als PRIMARY KEY definieren (WICHTIG: id muß NOT NULL sein)
CREATE TABLE tbl01 (
id NOT NULL,
name CHAR(30),
PRIMARY KEY (id)
);

# Dieselbe Schreibweise in Kurzform
CREATE TABLE tbl01 (
id NOT NULL PRIMARY KEY,
name CHAR(30),
);

# Verwendung von Unique als eindeutigen Identifier. Entspricht PRIMARY KEY
CREATE TABLE tbl01 (
id NOT NULL UNIQUE,
name CHAR(30),
);

Composite Index: Es ist auch möglich, einen eindeutigen Schlüssel aus zwei Spalten zu generieren. Wir weisen PRIMARY KEY bzw. UNIQUE einfach zwei Spalten zu:

# PRIMARY KEY aus zwei Spalten (composite index)
CREATE TABLE people (
vorname CHAR(30) NOT NULL,
nachname CHAR(30) NOT NULL,
PRIMARY KEY (vorname, nachname)
);

# Dasselbe in Grün: UNIQUE aus zwei Spalten (composite index)
CREATE TABLE people (
vorname CHAR(30) NOT NULL,
nachname CHAR(30) NOT NULL,
UNIQUE (vorname, nachname)
);

Was bedeutet dies nun genau: es ist möglich, daß Vor- und Nachnamen mehrfach vorkommen dürfen. Beide dürfen beliebig kombiniert werden, nur eine Kombination darf nicht doppelt / mehrfach vorkommen. Die Einzigartigkeit besteht also einer eindeutigen Kombination aus Vor- und Nachname.

Adding Indexes to Existing Tables
Mit folgenden Befehlen kann man einen oder mehrere Indizes nachträglich zu einer Tabelle hinzufügen:

# Hinzufügen eines PRIMARY KEY Indexes (Spalte muß existieren)
ALTER TABLE tbl01 ADD PRIMARY KEY (id);

# Hinzufügen eines Indexes
ALTER TABLE tbl01 ADD INDEX (vorname, nachname);

# Hinzufügen eines Indexes mit dem Befehl CREATE
CREATE UNIQUE INDEX IDIndex IN tbl01 (id);
CREATE INDEX NameIndex ON tbl01 (vorname, nachname);

Wichtig: Nur mit ALTER TABLE kann man einen PRIMARY KEY nachträglich hinzufügen. Daher ist ALTER TABLE flexibler als CREATE.

Obtaining Table and Index Metadata
Genaue Informationen über eine Tabelle kann man sich mit dem SELECT-Befehl aus der Tabelle INFORMATION_SCHEMA.TABLES auslesen lassen. Hier werden Informationen zu allen Tabellen gespeichert. Um sich nun Infos zu einer bestimmten Tabelle anzeigen zu lassen, gibt man den Namen der Datenbank (TABLE_SCHEMA) und der Tabelle (TABLE_NAME) an. Folgendes Beispiel zeigt Informationen über die Tabelle "zeit" der Datenbank "test" an:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = "zeit" AND TABLE_SCHEMA = "test";


TABLE_CATALOG: NULL
TABLE_SCHEMA: test
TABLE_NAME: zeit
TABLE_TYPE: BASE TABLE
ENGINE: MyISAM
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 1
AVG_ROW_LENGTH: 36
DATA_LENGTH: 36
MAX_DATA_LENGTH: 281474976710655
INDEX_LENGTH: 1024
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: 2008-02-25 15:18:23
UPDATE_TIME: 2008-02-25 15:18:23
CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:



Der Befehl SHOW TABLES FROM test zeigt alle Tabellen einer Datenbank an:

SHOW TABLES FROM test;


Nach Tabellen suchen mit LIKE %


# Tabellen anzeigen, die mit mu beginnen
SHOW TABLES FROM test LIKE 'mu%';

# Tabellen anzeigen, die tr enthalten
SHOW TABLES FROM test LIKE '%tr%';

# Tabellen anzeigen, die auf en enden
SHOW TABLES FROM test LIKE '%en';


INDEX einer Tabelle anzeigen mit SHOW INDEX FROM
SHOW INDEX FROM tbl01\G;

Table: tbl01
Non_unique: 1
Key_name: name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:

Informationen zu den Spalten einer Tabelle anzeigen lassen
Folgende Befehle können dazu genutzt werden, um Informationen zu den einzelnen Spalten anzeigen zu lassen:

DESCRIBE tbl01;

SHOW COLUMNS FROM tbl01;

SHOW FIELDS FROM tbl01;

Using SELECT to Retrieve Date

Der SELECT-Befehl wird vor allem dazu benutzt, um Datensätze aus den Tabellen zu lesen und anzuzeigen.
Hier eine Übersicht der möglichen Optionen für den SELECT-Befehl:

SELECT spalte1, spalte2, spalte3
FROM tbl_1
WHERE id <=500
GROUP BY gruppe
HAVING ausdruck
ORDER BY spalte1
LIMIT 10;

Specifying Which Columns To Receive

Viele glauben, dass der SELECT-Befehl ausschließlich dazu benutzt wird, um Datensätze aus Tabellen zu lesen. Das ist aber falsch, denn SELECT kann viel mehr. Grundsätzlich ist es die FROM Option nicht zwingend erforderlich, um Datensätze einer Tabelle auszulesen.
Folgendes Beispiel zeigt grundlegende Funktionen von SELECT:


SELECT 1+10, REPEAT("Hallo", 2), 3*9;

In der 1. Spalte sieht man das Ergebnis aus 1+10. Die zweite Spalte zeigt den Begriff "Hallo" zwei Mal wiederholt.
Die dritte Spalte zeigt das Ergebnis aus der Multiplikation aus 3 mal 9.


Eine einfache Abfrage aus einer Tabelle sieht folgendermaßen aus:

SELECT Chip_id, Chiptakt, Speichertakt, Speicher FROM gpu LIMIT 1;

In diesem Fall werden nur bestimmte Spalten ausgelesen.
LIMIT bedeutet, daß maximal 1 Datensatz (Zeile) ausgelesen wird.

Um alle Spalten auszulesen, kann man das * verwenden:

SELECT * FROM gpu LIMIT 1;

Spalten in der Ausgabe umbenennen
Standardmäßig wird bei einer SELECT-Abfrage der Name der Spalte angezeigt. Diesen Namen kann man Mithilfe von AS umbenennen:


# Spalte umbenennen
SELECT Speicher AS 'RAM In MB' FROM gpu LIMIT 1;

Eine Datenbank in der SELECT Abfrage mit angeben
Wenn man nicht mit dem Befehl USE database eine Verbindung zu einer Datenbank aufgebaut hat, kann MySQL logischerweise nicht die richtige Tabelle finden. Dazu kann man Datenbank zusätzlich angeben:

SELECT * FROM db001.gpu LIMIT 1;

Es ist zwar möglich, eine Standard-Datenbank anzugeben, aber wenn man ohne Datenbank-Verbindung eine Tabelle aus einer anderen Datenbank ansprechen möchte, muß man oben gezeigtes Prinzip trotzdem anwenden.

Specifying Which Rows to Retrieve
Mit dem Schlüsselwort WHERE kann man Kriterien festlegen, welche Datensätze ausgelesen werden sollen. Die wichtigsten Ausdrücke sind = (gleich), < (kleiner) und > (größer), welche auch kombiniert werden können: <= (kleiner gleich) und >= (größer gleich). Des Weiteren kann man die Suchkriterien mit AND und OR genauer eingrenzen. Folgendes Beispiel soll dies näher erläutern.

SELECT Chip_id, Speichertakt FROM gpu WHERE Speichertakt >= 100 AND Speichertakt <= 115 OR Speichertakt > 2000;

Die Datensätze müssen folgende Kriterien erfüllen:

1.) Speichertakt muß größer gleich 100 und kleiner gleich 115 sein
2.) Oder der Speichertakt ist größer als 2000
Die Kriterien kann man mit der runden Klammer gruppieren.

Um beispielsweise Grafikkarten anzeigen zu lassen, dessen Speichertakt zwischen 100-200 und 400-500 liegt,
muß man die SELECT- Anweisung folgendermaßen schreiben:

SELECT Chip_id, Speichertakt FROM gpu
WHERE (Speichertakt >= 100 AND Speichertakt <= 115)
OR (Speichertakt >= 400 AND Speichertakt <= 500);

Using ORDER BY to Sort Query results
Mit dem Schlüsselwort ORDER BY kann man festlegen, nach welcher Spalte die Datensätze sortiert werden sollen. Außerdem können ASC (aufsteigend, "von klein nach groß") und DESC (absteigend, von "groß nach klein") benutzt werden. Standardmäßig verwendet MySQL ASC, sortiert als "von klein nach groß".


# Sortieren nach vorname, dann nach nachname
SELECT id, vorname, nachname FROM tbl ORDER BY vorname, nachname;

# vorname absteigend, nachname aufsteigend sortieren
SELECT id, vorname, nachname FROM tbl ORDER BY vorname DESC, nachname ASC;


Wie wendet man ORDER BY an, wenn man die Spalte bei der Ausgabe umbenennt? Man nimmt einfach den Alias oder gibt die Spaltenzahl an:

# Sortieren in Verbindung mit Alias-Spalten
SELECT vorname AS abc, nachname AS xyz FROM tbl ORDER BY abc, xyz;

# 1 steht für erste, 2 für zweite Spalte
SELECT vorname AS abc, nachname AS xyz FROM tbl ORDER BY 1, 2;

The Natural Sort Order of Data Types
In diesem Abschnitt wird die natürliche Sortierreihenfolge beschrieben und wie sich diese auf die Ausgabe auswirkt.

Kurzgefaßt: nach welchen Kriterien wird überhaupt sortiert?
Wie bereits besprochen ist die Sortierreihenfolge vom jeweiligen Datentyp abhängig, der der Spalte zugewiesen wurde. Es gibt beispielsweise nicht-binäre und binäre Datentypen,
die MySQL-intern unterschiedlich sortieren.

Man kann natürliche Sortierreihenfolge auf folgende Datentypen festlegen:
 
- Numerische Spalten: werden nach dem Zahlenwert sortiert
- Tempöräre Spalten: werden nach Zeit sortiert
- Spalten mit Zeichenketten (string): Hier ist die Reihenfolge abhängig, ob es sich um einen binären oder nicht binären Datentyp handelt.
 
Außerdem ist die festgelegte Collation (Sortierreihenfolge) entscheidend.

Beispiel:

#CHAR-Spalte, non-case-sensitiv


SELECT spalte1 FROM tbl ORDER BY spalte1;

Ausgabe in Reihenfolge: aAaaBbbB

In diesem Beispiel wird die Groß- und Kleinschreibung ignoriert.



# CHAR-Spalte, case-sensitiv

SELECT spalte1 FROM tbl ORDER BY spalte1 COLLATION latin1_general_cs;

Ausgabe in Reihenfolge: AaaaBBbb

Nun wird auch die Groß- und Kleinschreibung beachtet.



Beispiel:

# Binäre Sortierreihenfolge:

SELECT spalte1 FROM tbl ORDER BY spalte1 COLLATION latin1_bin;

Ausgabe in Reihenfolge: ABBaaabb

Bei der Ausgabe werden zuerst die Groß-, dann die Kleinbuchstaben angezeigt. Das ist folgendermaßen zu erklären. Bei einem Binär-Datentyp wird jedem Zeichen intern ein Wert zugewiesen. In diesem Bsp. hat jeder Buchstabe einen internen, individuellen Wert, über den dieser angesprochen wird. Das könnte intern ungefähr so aussehen: A=1, B=2, C=3,...,a=27,b=28,c=29, usw... Die Ausgabe wird nun entsprechend nach den Werten sortiert.


Sortierreihenfolge bei ENUM
Bei einer eigens angelegten Aufzählung mit dem Typ ENUM ist die Sortierreihenfolge bestimmt durch die Reihenfolge, wie sie angelegt wurden:

Beispiel:

CREATE TABLE monate
(
spalte1 ENUM('Jan','Feb','Mar','Apr','Mai','Jun','Jul','Aug')
);


SELECT * FROM monate ORDER BY spalte1;

Ausgabe: Jan, Feb, Mar, Apr, Mai, Jun, Jul, Aug

Die Ausgabe mit SELECT wird nach der Reihenfolge sortiert, wie die Datensätze angelegt wurden.

Um die Ausgabe wiederum alphabetisch zu sortieren, kann man CAST() benutzen:

SELECT * FROM monate ORDER BY CAST(spalte1 AS CHAR);

Ausgabe: Apr, Aug, Feb, Jul, Jun, Mar, Mai

Limiting a Selection Using LIMIT

Mit dem Schlüsselwort LIMIT kann man die Anzahl der auszugebenden Datensätze einschränken.

# Ausgabe der ersten 20 Zeilen

SELECT * FROM tbl01 LIMIT 20;

Dieser Befehl bedeutet, daß nur die ersten 20 Datensätze angezeigt werden. LIMIT kann aber noch einen weiteren Zahlenwert enthalten. Wenn zwei Werte angegeben werden, so bedeutet der erste Wert "Ignoriere die Datensätze bis X" und der zweite Wert "lese die nächsten X Zeilen aus".


# Ausgabe 30 Zeilen beginnend bei dem 1000. Datensatz

SELECT * FROM tbl01 LIMIT 1000,30;

Using DISTINCT to Eliminate Duplicates

Das Schlüsselwort DISTINCT() wird dazu verwendet, um doppelte Werte in einer Spalte zusammenzufassen. Daher ähnelt DISTINCT der Funktion GROUP BY.

Folgendes Beispiel soll die Funktionsweise verdeutlichen:


SELECT name FROM multiindex;

SELECT DISTINCT(name) FROM multiindex;

SELECT name FROM multiindex GROUP BY name;

Aggregating Results

MySQL bietet es an, die Ergebnisse zusammenzufassen und Operationen anzuwenden. MySQL kann zählen, Durchschnittswerte errechnen, Minimal- und Maximalwerte ermitteln oder Ergebnisse summieren.

In folgenden Kapiteln werden die Funktionen MIN(), MAX(), SUM(), AVG(), COUNT() und GROUP_CONCAT() näher erläutert.

The MIN() und MAX() Aggregate Funktions

Die Funktionen MIN() und MAX() werden dazu benutzt, um den kleinsten bzw. größten Wert aus einer Spalte ausfindig zu machen:

Beispiel:

SELECT MAX(TRANSISTOREN), MIN(TRANSISTOREN) FROM gpu;

MIN() und MAX() kann man auch auf Spalten anwenden, die Zeichenketten (Strings) enthalten:

Beispiel:

SELECT MAX(hersteller), MIN(hersteller) FROM gpu_hrst;

3dfx ist der kleinste Wert, da mit einer Zahl beginnend. Der Buchstabe X ist ziemlich am Ende des Alphabets, daher ist XGI der größte Wert. Auch hier ist wieder entscheidend, ob es sich um einen binären oder nicht-binären Datentyp handelt.

The SUM() and AVG() Aggregate Functions

Ähnlich wie MIN() und MAX() funktionieren auch die Funktionen SUM() und AVG(). Sie berechnen aber die Summe und den Durchschnitt der Spaltenwerte:

Beispiel:

SELECT SUM(TRANSISTOREN), AVG(TRANSISTOREN) FROM gpu;

SUM() und AVG() können auch auf String-Datentypen angewendet werden, bringen aber keine sinnvolle Ergebnisse.

The COUNT() Aggregate Function

Mit der Funktion COUNT() können zum einen Zeilen und zum anderen Werte gezählt werden:

Beispiel:

# Zählen aller Zeilen in einer Tabelle

mysql> SELECT COUNT(*) FROM bench;

Neben der Anzahl der Zeilen kann COUNT() auch Werte in einer Spalte zählen.
Hierbei werden alle Werte beachtet, die nicht NULL sind.

In folgendem Beispiel sind 80 Null-Werte enthalten (884 - 804 = 80):

# Zählen von Werten in einer Spalte

SELECT COUNT(hdd) FROM bench;

In Verbindung mit DISTINCT können die unterschiedlichen Werte gezählt werden.

Beispiel:

# Wie viele unterschiedliche Namen wurden im Jahr 2008 vergeben?

SELECT COUNT(DISTINCT vorname) FROM geburten WHERE jahr=2008;

Man kann diesen Befehl auch erweitern. Gibt man bei COUNT die Spalten an, so werden die Kombinationen gezählt.
Um beispielsweise Namensvetter auszulesen, schreibt man folgendes:

# Wie viele Namensvetter gab es im Jahr 2008?

SELECT COUNT(DISTINCT vorname, nachname) FROM geburten WHERE jahr=2008;

Hinweis: In Verbindung mit GROUP_BY zählt COUNT, wie oft ein Wert vorkommt.

The GROUP_CONCAT() Function
Mit der Funktion GROUP_CONCAT() können die Zeilenwerte in einer einzigen Zelle angezeigt werden.
Anstatt daß jeder Wert in einer neuen Zeile erscheint,werden die Ergebnisse komma-separiert in einer Zelle ausgegeben.

Beispiel:

SELECT GROUP_CONCAT(hersteller) FROM gpu_hrst;

Das Trennzeichen für die Ausgabe kann ebenfalls geändert werden. Dazu verwendet man das Schlüsselwort SEPARATOR.

Beispiel:

SELECT GROUP_CONCAT(hersteller SEPARATOR '-') FROM gpu_hrst;

Aggregation for NULL Values or Empty Sets

Alle oben aufgeführten Funktionen ignorieren NULL-Werte. Ausnahme ist COUNT():

- COUNT(*) zählt Zeilen, so dass NULL-Werte nicht ignoriert werden
- COUNT(ausdruck) ignoriert NULL-Werte wiederum!

Grouping Results

Das Schlüsselwort GROUP_BY wird dazu verwendet, mehrfach vorkommende Werte zusammenzufassen. Daher ähnelt dieses Schlüsselwort der Funktion DISTINCT.
In Verbindung mit GROUP_BY() kann man COUNT() dazu verwenden, um diese mehrfach vorkommende Werte zu zählen:


# Gruppieren und zählen mehrfach vorkommender Werte:

SELECT section_id, COUNT(*) FROM bench GROUP BY section_id;

In diesem Beispiel gibt es insgesamt 225 Datensätze, welche als section_id den Wert 2 haben. Man kann auch mehrere Spalte gruppieren.



# Zählen, wie viele Personen welche Grafikkarte besitzen

SELECT section_id, vga, COUNT(*) FROM bench GROUP BY section_id, vga WHERE section_id=1;

In diesem Beispiel gibt es zwei Anwender, die den Benchmark mit der section_id und der Grafikkarte Radeon 9800 Pro durchlaufen haben.



Sehr praktisch ist auch die Möglichkeit, die bekannten Rechnungsfunktionen (MIN, MAX, etc) einzusetzen.
Folgendes Beispiel soll den schlechtesten, besten und durchschnittlichen Wert der Benchmarkergebnisse ermitteln:


# Schlechteste, beste und durchschnittliche Werte ermitteln

SELECT section_id, MIN(points_result), MAX(points_result), AVG(points_result)
FROM bench
GROUP BY section_id;

Selecting Groups with HAVING

HAVING ist ein zusätzliches Schlüsselwort, um Ergebnisse einzugrenzen. Es kommt dann zum Einsatz, wenn man in Verbindung mit GROUP BY das Schlüsselwort WHERE benutzt.

Beispiel:

SELECT section_id, vga, COUNT(*)
FROM bench
WHERE section_id > 3 OR section_id < 10
GROUP BY section_id, vga;

In diesem Beispiel werden nun zahlreiche Datensätze aus der Tabelle gelesen, denn alle Benchmarkergebnisse mit einer ID zwischen 4 und 9 werden angezeigt.
Um nun hier nur bestimmte Datensätze herauszulesen, setzt man HAVING ein.

SELECT section_id, vga, COUNT(*)
FROM bench
WHERE section_id < 3 OR section_id > 10
GROUP BY section_id, vga
HAVING vga = 'Radeon X1950 Pro';

In diesem Beispiel werden nur die Ergebnisse angezeigt, bei der eine Radeon X1950 Pro Grafikkarte eingesetzt wurde.

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äßt 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 ();

Mehrere Einträge mit einem einzelnen INSERT Statement erzeugen.

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);

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 existierender 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, der Datensatz verworfen, aber keine Fehlermeldung zu erzeugen
3.) ON DUPLICATE KEY UPDATE: Bei doppelten Key Value wird der Wert automatisch verändert

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.

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):

SELECT * FROM logging;

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.
D.h. mit jedem Einlogvorgang wird der Zähler in der Spalte "logs" um 1 hochgezählt.


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


SELECT * FROM logging;

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

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, daß 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:

CREATE TABLE multikey (
i INT NOT NULL UNIQUE,
k INT NOT NULL UNIQUE,
y INT NOT NULL UNIQUE
);


SELECT * FROM multikey;

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

REPLACE INTO multikey (i,k,y) VALUES (1,2,3);


SELECT * FROM multikey;

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;

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 muß man die Datensätze 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;

The DELETE and TRUNCATE TABLE Statements

Die Anweisungen DELETE und TRUNCATE TABLE werden dazu benutzt, um Datensätze in Tabellen zu löschen.
Der Unterschied zwischen DELETE und TRUNCATE TABLE ist, daß 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ätze 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, muß 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

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;

Import- and Export Operations

Ein wichtiger Aspekt in jeder Anwendung, die Datensätze verwaltet, sind Import- und Export-Funktionen. MySQL unterstützt diese Funktionen natürlich auch. Hier gibt es zwei Möglichkeiten:

Im MySQL-Client (MySQL-Prompt)
Wenn man über den MySQL-Client mit dem MySQL-Server verbunden ist, kann man mit LOAD DATA INFILE Daten importieren und mit SELECT ... INTO OUTFILE Daten exportieren.

Über die Kommandozeile
MySQL bietet die Programme mysqlimport und mysqldump an, um Daten aus einer Datenbank zu importieren und exportieren.

Importing Data with LOAD DATA INFILE

Mit dem Befehl LOAD DATA INFILE können Datensätze von einer externen Datei in eine Tabelle importiert werden.

LOAD DATA INFILE 'dateiname' INTO TABLE tbl01;


Der Dateiname muß in Hochkommata geschrieben werden.
Windows-Anwender müssen aufpassen, wenn ein absoluter Pfad angegeben wird.
'\' interpretiert MySQL als Steuerzeichen, so daß man für den Pfad entweder / oder \\ verwenden muß:

# Windows-Anwender müssen die Pfadangabe anpassen

LOAD DATA INFILE 'C:/data/bck-01-02-2009.sql' INTO TABLE tbl01;

LOAD DATA INFILE 'C:\\data\\bck-01-02-2009.sql' INTO TABLE tbl01;

Specifying the Data File Location

Standardmäßig sucht MySQL die zu importierende Datei auf dem Serverhost, wenn mit LOAD DATA INFILE Datensätze importiert werden sollen. Wenn sich die Daten aber nicht auf dem Server, sondern auf dem Client befinden, muß der Befehl um LOCAL erweitert werden:

LOAD DATA INFILE = Datei sich auf dem Server befinden importieren
LOAD DATA LOCAL INFILE = Datei die sich auf Client befinden importieren

Wichtig: Verzeichnisangabe bei LOAD DATA INFILE
Wenn eine relative Pfadangabe erfolgt, so sucht MySQL in dem Datenverzeichnis der Standard-Datenbank von MySQL.
Wenn die Standard-Datenbank beispielsweise default lautet, so hieße das entsprechende Verzeichnis /var/mysql/data/default/.

Ausgehend von diesem Verzeichnis sucht MySQL die Daten:

# Drei Möglichkeiten:

LOAD DATA INFILE '/var/mysql/data/default/backup.txt' INTO TABLE tbl01;

LOAD DATA INFILE 'backup.txt' INTO TABLE tbl01;

LOAD DATA INFILE './default/backup.txt' INTO TABLE tbl01;

Skipping Data File Lines

Mit den Schlüsselwörtern IGNORE x LINES können die ersten x-Zeilen ignoriert werden:

# Die ersten 20 Zeilen werden ignoriert

LOAD DATA INFILE 'backup.txt' INTO TABLE tbl01 IGNORE 20 LINES;

Skipping Data File Lines

Wenn man mit LOAD DATA INFILE Datensätze importiert, geht man davon aus, daß die Zeilen komplett und die Spalten in der richtigen Reihenfolge sind. Wenn zu viele Spalten pro Zeile in der Datei existieren, werden die überflüssigen Spalten verworfen. Enthalten die Zeilen zu wenige Spalten, so werden die Datensätze (ähnlich beim INSERT-Befehl) mit den Standardwerten aufgefüllt.

Stellen wir uns folgendes Szenario vor: Wir haben eine Tabelle mit den Spalten vorname und name.
Die zu importierende Datei enthält Zeilen, in denen die Spalten vertauscht sind. Man kann den LOAD DATA INFILE Befehl nun folgendermaßen formulieren, um die Daten in richtiger Reihenfolge zu importieren:

# Spalten explizit angeben bzw.vertauschen

LOAD DATA INFILE 'backup.txt' INTO TABLE tbl01 (name, vorname);

Skipping or Transforming Column Values

Es ist möglich, gewisse Spalten nicht zu importieren. Dazu kann man so genannte Benutzervariablen definieren. Wenn man beispielsweise eine Spalte nicht importieren möchte, gibt man für die Spalte einen beliebigen Namen beginnend mit einem @ an, beispielsweise @skip an. Das @ sagt MySQL, daß es sich um eine Variable handelt.
Wenn man jetzt kein SET-Statement angibt, werden die Daten nicht übernommen, sprich sie werden nicht importiert.

# Die erste Spalte nicht importieren

LOAD DATA INFILE 'backup.txt'
INTO TABLE tbl01 (@skip, name, vorname, alter);

Man kann aber in Verbindung mir SET auch nützliche Operationen durchführen. Dazu setzt man in der Spaltenangabe bei der entsprechende Spalte eine Benutzervariable und verarbeitet diese dann nach der SET-Anweisung weiter:


Die erste Spalte wird verworfen, Vorname und Nachname werden in eine Spalte zusammengefügt:

LOAD DATA INFILE 'backup.txt'
INTO TABLE tbl01 (@skip, @name, @vorname, alter)
SET name=CONCAT(@vorname,' '@nachname);

# Das Alter verdoppeln:

LOAD DATA INFILE 'backup.txt'
INTO TABLE tbl01 (id, name, vorname, @var1)
SET alter=@var1*2;

Exporting Data with SELECT ... INTO OUTFILE

INTO OUTFILE wird dazu genutzt, um Datensätze zu exportieren. Der Standard-Speicherort ist das Verzeichnis der Standard-Datenbank. Unter Gentoo Linux wäre das ein Verzeichnis in /var/lib/mysql. Wie bei LOAD DATA INFILE müssen bei INTO OUTFILE die zu schreibenden Dateien in ' ' gesetzt werden. Ohne weitere Angabe speichert INTO OUTFILE die Daten in einem vorbestimmten Format, nämlich mit Tabs separiert.

Folgendes Beispiel soll die Funktionsweise verdeutlichen:


SELECT * FROM logging;

SELECT * INTO OUTFILE 'backup.txt' FROM logging;


# cd /var/lib/mysql/test/
# cat backup.txt
peter 1
hans 3
anja 1
meik 1

Data File Format Specifiers

Im vorher gezeigten Beispiel wurden Daten in dem Standard-Format exportiert, so daß die einzelnen Datensätze beispielsweise durch Tabs getrennt und nicht mit Hochkommata eingebunden wurden. Oftmals ist es aber notwendig, die Ausgabe anzupassen.
In diesem Fall ist es möglich, die Anweisungen LOAD DATA INFILE und INTO OUTFILE um die Schlüsselwörter TERMINATED BY, ENCLOSED BY, ESCAPED BY, LINES TERMINATED BY zu erweitern.

# Syntax

FIELDS
TERMINATED BY 'string'
ENCLOSED BY 'char'
ESCAPED BY 'char'
 'string'


# Wenn man keine Angaben macht, werden diese Standardwerte genutzt:

FIELDS
TERMINATED BY '\t'
ENCLOSED BY ''
ESCAPED BY '\\'



Die LINE-Sequenz bezieht sich auf das Ende der Zeile. Dieses ist ganz wichtig, denn mit LINES TERMINATED BY wird eine neue Zeile und somit auch ein neuer Datensatz angezeigt. Hier gibt es bei Windows- und UNIX-Systemen unterschiedliche Konventionen. Jeder hat wahrscheinlich schon einmal das Problem gehabt, dass eine UNIX-Textdatei im Windows Notepad aus nur einer einzigen Zeile besteht. Grund dafür sind die unterschiedlichen "line terminators".


# \n steht für Newline, wird unter UNIX verwendet
LINES TERMINATED BY \n

# \r steht für Carriage return
LINES TERMINATED BY \r

# Für Windows muss man beides angeben
LINES TERMINATED BY \n\r


In folgendem Beispiel soll die Funktionsweise näher erläutert werden:


# Beispiel: Ausgabe als CSV-Datei

SELECT * INTO OUTFILE 'backup.txt'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM logging;



# cat backup.txt
"peter","1"
"hans","3"
"anja","1"
"meik","1"


Hier eine Übersicht über die wichtigsten Sequenzen:

\N = NULL Value
\0 = NUL (zero) byte
\b = Backspace
\n = Newline
\r = Carriage return
\s = Space
\t = Tab
\' = Single quote
\" = Double quote
\\ = Backslash

Importing and Exporting NULL Values

NULL steht in MySQL bekanntlich für "Wert nicht vorhanden" oder "Wert unbekannt". Beim Ex-/Import mit LOAD DATA INFILE werden Nullwerte als \N deklariert. Exportiert man also Datensätze aus einer Tabelle, welches NULL-Werte sind, so werden diese in der Datei als \N gespeichert.

Importing Data with mysqlimport

Was man mit LOAD DATA INFILE in dem MySQL-Client machen kann, erledigt mysqlimport über die Shell bzw. Eingabeaufforderung. Somit kann man Mithilfe von Skripten den Import automatisieren oder einfach den Import ohne den MySQL-Client durchführen.

# Syntax von MySQLImport

mysqlimport [options] db_name textfile1 textfile2 ...


Wichtig: mysqlimport ist nicht mit dem Import Mithilfe von mysql zu verwechseln und kann keine Dump-Files importieren, die mit mysqldump erstellt wurden!

Hier einige Beispiele:


# Import von backup.txt in die DB db_namen. Angabe des Zeichens für das Zeilenende:

mysqlimport --lines-terminated-by="\r\n" db_namen backup.txt

# Angabe, daß die einzelnen Werte mit " umklammert sind

mysqlimport --fields-enclosed-by='"' db_namen backup.txt

# Angabe, daß die einzelnen Werte mit , beendet werden

mysqlimport --fields-terminated-by=, db_namen backup.txt


Was passiert bei doppelten Einträgen?

# Doppelte Werte werden verworfen

mysqlimport --ignore db_namen backup.txt

# Doppelte Werte werden durch die neuen Werte ersetzt/überschrieben

mysqlimport --replace db_namen backup.txt