Jürgen Schilling - Natur | Fotografie | Technik .

 

JürgenSchilling

Natur | Fotografie | Technik

 
 

 Home

 Black&White
 Cyanotypie
 Fotogramme
 Infrarot
 Jägerprüfung
 Kategorien
 Kirlianfotografie
 Lochkamera
 Makroaufnahmen



Lightbox


 Manuals:
 
Alle Manuals
 Aluminium eloxieren
 Hochspannungsgenerator
 Belichtungszeitentabelle
 Beurteilung v. Negativen
 Blitzlichtfotographie
 Farbmischung
 Filmentwicklung
 Infrarotfotografie
 Teslatrafo
 Teslatrafo Rechner
 Schrauben u. Gewinde

 Entwicklingszeiten:
 Agfa Rodinal
 Kodak D76

 Tiefenschäferechner

 
 Impressum


______

















  
  Bilder pro Gast
  phpMyAdmin
  B4YCounter
  Most viewed
  Logout
  Login
  Theme curve
  Theme my-fruity

MySQL Basics - Hier die Unterlagen von meiner letzten MySQL Schulung


Zuerst mußte ein MySQL 5.2 Datenbankserver installiert werden.
Das funktioniert unter XP im einfachsten Fall durch Doppelklick auf die mysql-5.1.37-win32.msi.
Eine weitere Konfiguration ist hier nicht notwendig.

Um später dynamische Webseiten entwickeln zu können, mußte noch ein Apache2.2 Webserver und PHP5 installiert und konfiguriert werden.


Einrichtung meines Webservers:

Webroot: D:\01-Homepage\wwwroot
Domäne: de
Server: hal9000


PHP 5.2.1 im Apache2.2 aktivieren:
Die C:\Program Files\PHP\php5apache2_2.dll muß im "LoadModule"-Block der http.conf eingetragen werden.

LoadModule php5_module "C:\Program Files\PHP\php5apache2_2.dll"

Aufruf der Webseite:
http://hal9000/phpMyAdmin/index.html

MyPHPAdmin:
http://hal9000/phpMyAdmin/index.php






Auf den MySQL verbinden:

shell> mysql -u root -p

Verbindungsoptionen:

--host     = HOST_NAME     / -h HOST_NAME     = > Verbindung mit bestimmten MySQL-Server herstellen
--port     = PORT          / -P Port          = > Verbindung über bestimmten Port herstellen (Standard-Port ist 3306)
--socket   = SOCKET_NAME   / -S SOCKET_NAME   = > Verbindung über Socket herstellen
--user     = USER_NAME     / -u USER_NAME     = > Benutzername angeben
--password = PASS          / -pPASS           = > Paßwort angeben (Wichtig: Bei Kurzform ohne Leerzeichen!)
--compress =                                  = > Daten werden werden vorher komprimiert und beim Empfänger wieder entpackt

Beispiele:

shell > mysql --host = sqlserver.domain.com  : Verbindung mit Server "sqlserver" herstellen
shell > mysql --protocol = tcp               : Verbindung über TCP/IP (von allen Betriebssystem unterstützt)
shell > mysql --protocol = socket            : Verbindung über Unix-Socket (nur von Unix/Linux unterstützt)
shell > mysql --protocol = pipe              : Verbindung über named Pipe (nur von Windows unterstützt)
shell > mysql --socket = /tmp/mysql.sock     : Verbindung über Standard-Unix-Socket
shell > mysql -u user07 -p4di09 -h localhost : user07 verbindet sich mit Passwort 4di09
shell > mysql -u user07 -p4di09 --compress   : Traffic wird reduziert, da Daten komprimiert werden






Optionsfiles (my.cnf) beim einloggen benutzen:

Um dem Anwender etwas Abhilfe bei den zahlreichen Optionen zu verschaffen, kann man generelle Einstellungen in einfachen Textdateien festlegen. Die Standard-Konfigurationsdatei befindet sich unter Linux in der Datei /etc/my.cnf und unter Windows in C:\my.cnf.

Die Datei ist in Gruppen aufgebaut, welche in eckigen Klammern definiert werden. In der Regel steht in den eckigen Klammern, für welches Programm die Optionen gültig sind. In dem unten aufgeführten Beispiel wurden Optionen für die Programme mysql und mysqld festgelegt.

Man kann die Konfigurationsdateien explizit angeben:

shell > mysql --defaults-file=/etc/mysql.cnf         :Andere Default-Konfig-Datei verwenden
shell > mysql --defaults-extra-file=/etc/andere.cnf  :Zusätzlich zur Default-Datei eine weitere Datei nutzen


Auszug aus der /etc/my.cnf:


[mysql]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1

[mysqld]
character-set-server = latin1
default-character-set = latin1
user = mysql
port = 3306






Scripte mit MySQL ausführen:

Anstatt alle Befehle nach und nach per Hand über die Konsole einzutippen, kann die Befehle in einer einfachen Textdatei schreiben, um sie anschließend in MySQL zu importieren. Somit wäre es beispielsweise möglich, bestimmte Routineaufgaben zeitgesteuert ausführen zu lassen, ohne selbst am PC zu sitzen.

mysql> SOURCE update.txt         // Ausführen der Befehle mittels Befehl SOURCE in MySQL.
mysql> SOURCE /tmp/update.txt    // Wenn sich die Datei nicht im selben Verzeichnis befindet.


Beispiel:

Das Script liegt unter D:\world.sql
Der "\" in der Pfadangabe muß maskiert werden, sonst wird der Pfad nicht erkannt.

mysql> source d:\\world.sql;






MySQL Output Format:

Wenn man sich mit MySQL mit einer Datenbank verbindet, wird die Ausgabe auf der Konsole standardmäßig in Tabellenform angezeigt. Dieses kann man aber ändern:

shell> mysql --batch  / -b   // Batchmode-Anzeige (TABs)
shell> mysql --table  / -t   // Anzeige in Tabellenform
shell> mysql --html   / -h   // Produziert HTML-Code
shell> mysql --xml    / -X   // Produziert XML-Code






Using the --safe-updates Option

Man kann den MySQL-Monitor mit der Option --safe-updates starten, so daß Änderungen an den Datenbanken nur beschränkt möglich sind.
Dies ist vor allem dann von Nutzen, wenn man auf Produktivdatenbanken arbeitet:

shell> mysql --safe-updates DATABASE

Die Option --safe-updates wirkt sich unter anderem folgendermaßen aus:

- Die Befehle UPDATE und DELETE werden nur in Verbindung mit WHERE und LIMIT ausgeführt
- Die Ausgabe von SELECT ist auf 1.000 beschränkt






Datentypen:

Man kann in Datenbanken unterschiedliche Werte speichern: einfachen Text, Datum, Zahlen von 1 bis 100, Währungen, usw. Abhängig von den zu speichernden Werten muß man den passenden Datentyp für die jeweilige Spalte auswählen. Außerdem ist aus Performance-Gründen wichtig, den passenden Datentyp zu wählen, da die einzelnen Datentypen unterschiedlich viel Speicherplatz in Anspruch nehmen.

Die Datentypen in MySQL kann man in drei Hauptkategorien einteilen:

- numerische Datentypen (Integer, Fließkomma-, Dezimalzahlen, Boolean, etc.)
- String-Typen (einzelne Zeichen, Text, etc.)
- Datums- und Zeittypen (Uhrzeit, Datum)

Beim Anlegen einer Spalte in einer Tabelle muß man neben den Spaltenamen auch den Datentyp festlegen.

Beispiel:

CREATE TABLE auskunft
(
name VARCHAR(20) CHARACTER SET latin1,
email TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);



In diesem Beispiel wird eine neue Tabelle namens auskunft erstellt. Diese Tabelle enthält zwei Spalten name und email, welche als VARCHAR mit 20 Zeichen und TEXT definiert werden.






Character Set Support (= Der Zeichensatz)
 
Der Zeichensatz legt fest, welche Schriftzeichen unterstützt werden. Auf der Welt gibt es eine Vielfalt an Sprachen und viele besitzen sogar eigene Schriftzeichen. Aus diesem Grund gibt es bei MySQL einige Zeichensätze (character sets), um beispielsweise chinesische Schriftzeichen oder die deutschen Sonderzeichen wie ß speichern und anzeigen zu können.
MySQL unterstützt standardmäßig den Latin1-Zeichensatz (auch bekannt als ISO-8859-1), welcher für westeuropäischen Länder genutzt wird. Hier fehlen aber einige Zeichen, wie beispielsweise ä,ö,ü aus der deutschen Sprache. In letzterem Fall sollte man ISO-8859-15 wählen. Beim Latin1-Zeichensatz belegt jedes Zeichen 1 Byte.






Collation - Sortierreihenfolge

Die offizielle Bezeichnung Collation steht bei Datenbanken für die Sortierreihenfolge. Mit Collation legt man also fest, wie die Strings durch die Anweisungen SELECT, ORDER BY oder GROUP BY sortiert werden.
Einer der wichtigsten Festlegung durch die Sortierreihenfolge (Collation) ist, ob die Groß-/Kleinschreibung beachtet wird. Wird die Groß-/Kleinschreibung beachtet, so spricht man von case sensitiv. Im anderen Fall spricht man von case insensitiv.
Des Weiteren kann es entscheidend sein, wie Varianten eines bestimmten Zeichens gehandhabt werden. Wie sollen beispielsweise "a", "á" und "à" sortiert werden? Werden diese Zeichen als dasselbe behandelt, spricht man von accent insensitiv, andernfalls von accent sensitiv.


mysql> SHOW COLLATION LIKE 'latin1%';

< /TR>
Collation Charset Id Default Compiled Sortlen
latin1_german1_ci latin1</ TD> 5 Yes 1
latin1_swedish_ci latin1 8 Yes Yes 1
latin1_dan ish_ci latin1 15 Yes 1
latin1_german2_ci latin1 31 Yes 2
latin1_bin latin1 47 Yes 1
latin1_general_ci latin1 48 Yes 1
latin1_general_cs latin1 49 Yes 1
latin1_spanish_ci latin1 94 Yes 1


Ein Zeichensatz kann mehrere Collations besitzen, wie man anhand der oben stehenden MySQL-Ausgabe für den Latin1-Zeichensatz sehen kann. _ci steht für case insensitiv, _cs für case sensitiv und _bin für Binary.






Unterschied non-binary-string und binary-string

non-binary-strings sind CHAR, VARCHAR und TEXT. Diese werden intern als "richtige Zeichen" gespeichert, welche zu einem bestimmten Zeichensatz gehören. Die Sortierung ist abhängig von der Kollation. Non-binary-strings werden dann benutzt, wenn man Zeichenketten speichern möchte, also beispielsweise geschriebene Texte.

binary-strings sind BINARY, VARBINARY, BLOB. Diese Datentypen werden intern als binäre Zeichen gespeichert, d.h. sie enthalten byte- anstatt zeichenbasierter Strings. Jeder Buchstabe, jedes Zeichen wird intern als Binärzahl gespeichert, wobei jede Binärzahl als Synonym für genau ein Textzeichen steht. Somit entsteht beispielsweise beim Sortieren der Eindruck, dass binary-strings case-sensitiv sind. Das liegt aber daran, daß z.B. a intern anders gespeichert wird als A. binary-strings besitzen keinen Zeichensatz. Sie werden eingesetzt, wenn man "rohe Daten" wie Bilder oder komprimierte Daten speichern möchte.

Beispiel:

CREATE TABLE login
(
login_user CHAR(32) CHARACTER SET latin1;
password CHAR(32) CHARACTER SET latin1 COLLATE latin1_general_cs;
picture MEDIUMBLOB;
);






Non-Binary String Daten Typen: CHAR, VARCHAR, TEXT

Datentypen des Typs "Nicht-Binäre Zeichenketten" (non-binary string) sind Datentypen, bei den Zeichen intern auch als Zeichen gespeichert werden und die einen Zeichensatz (character set) und eine Sortierreihenfolge (collation) besitzen. Non-binary strings unterscheiden sich in der maximalen Länge der Zeichenkette und der Art und Weise, wie Leerzeichen gehandhabt werden.

CHAR
Bei Char spricht man von einem "fixed-length type", was bedeutet, dass eine Zeichenkette immer dieselbe Größe und Länge hat. Definiert man beispielsweise eine Spalte mit CHAR(30) und speichert in dieser Spalte einen Wert mit 10 Zeichen, so werden die restlichen Zeichen mit Leerzeichen aufgefüllt.
-> Zeichenkette mit fester Länge und gleich-großem Speicherplatz. Automatisches Auffüllen mit Leerzeichen. Länge kann von 0-255 definiert werden.

Beispiel:

Bei einem Single-Character-Set muß MySQL bei CHAR(10) 10 Byte pro Wert (pro Zelle) reservieren. Bei UTF8 (3 Byte pro Zeichen) wären es sogar 30 Byte! Dieser Speicherplatz wird selbst dann benötigt, wenn ein leerer Zeichenstring gespeichert wird.


VARCHAR
Hierbei handelt es sich um eine Zeichenkette mit variabler Länge. Das bedeutet, das wirklich nur die gewünschten Zeichen gespeichert und bei kürzeren Zeichenketten keine Zeichen aufgefüllt werden werden. Aus diesem Grund ist die Speichergröße variabel. Bei VARCHAR ergibt sich die Speichergröße aus der Länge der Zeichenkette zuzüglich 1-2 Byte für die Angabe der Zeichenkettelänge (zusätzliche Information).
-> Zeichenkette mit variabler Länge und unterschiedlichem Speicherplatz. Länge kann von 0-65.535 definiert werden.

Wert

CHAR(4)

Speicherplatz

VARCHAR(4)

Speicherplatz

''

' '

4 Byte

' '

1 Byte

'ab'

'ab '

4 Byte

'ab '

3 Byte

'abcd'

'abcd'

4 Byte

'abcd'

5 Byte

'abcdefgh'

'abcd'

4 Byte

'abcd'

5 Byte

Beispiel:

Wenn eine Spalte als VARCHAR(10) deklariert ist 10 Single-Byte-Zeichen gespeichert werden, so werden 10 Bytes + 1 Byte benötigt. Dieser Speicher wird automatisch angepaßt, wenn z.B.. Triple-Byte-Zeichen (UTF8) verwendet werden. Hier werden 30 Bytes + 1 Byte benötigt.


TEXT
Hier gibt es gleich vier unterschiedliche Typen (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT), bei den Speichergröße und die maximale Zeichenlänge vorgeschrieben sind. Im Prinzip handelt es sich hier um einen VARCHAR-Typ, bei dem Zeichenlänge fest definiert und nicht selbst ausgewählt werden kann. TEXT ist also ebenfalls eine Zeichenkette mit variabler Länge, der benötigte Speicherplatz ist also davon abhängig, wie viel Text nun wirklich gespeichert wird.

Binary String Data Types: BINARY, VARBINARY, BLOB
BINARY, VARBINARY und BLOB sind binäre Zeichenketten und equivalent zu den nicht-binär Zeichenketten CHAR, VARCHAR und TEXT. Wie bereits besprochen werden bei binären Zeichenketten die einzelnen Zeichen als Binärdaten gespeichert. Jedes Zeichen hat einen eindeutigen Binärwert zugeordnet, so daß jedes Zeichen, sei es z.B. groß oder klein geschrieben, eindeutig identifizierbar ist. Aus diesem Grund haben BINARY, VARBINARY und BLOB keinen Zeichensatz (character set) und keine Sortierreihenfolge (collation). Sortierung und Vergleiche basieren in diesem Fall auf den numerischen Werten der Bytes in den Werten.






Temporal Data Types
Datentypen für Datum und Uhrzeit ermöglichen das Speichern zeitlicher Werte. Bei diesen Datentypen handelt es sich um DATETIME, DATE, TIMESTAMP, TIME und YEAR, welche unterschiedliche Datums- und Zeitangaben über verschiedene Zeiträume speichern. Des Weiteren unterscheiden sich diese Datentypen in der Speichergröße, welchen jeder Wert beansprucht.
Um die Anzeige des Datums und der Uhrzeit zu beeinflussen, kann man die Funktionen DATE_FORMAT() und TIME_FORMAT() verwenden.


TIMESTAMP Daten Typ
Der Datentyp TIMESTAMP ähnelt dem Datentyp DATETIME. Beide werden dazu benutzt, um Datums- und Uhrzeitangaben zu speichern. Sie unterscheiden sich aber in dem Wertebereich. Außerdem bringt der TIMESTAMP Datentyp weitere, nützliche Funktionen mit. Wie der Name schon sagt, handelt es sich um einen Zeitstempel und daher wird TIMESTAMP dazu verwendet, Zeitangaben bei Änderung, Erstellung oder Löschung von Datensätzen zu speichern.
Wenn man in MySQL zwei Spalten des Typs TIMESTAMP anlegt, so erhält die erste Spalte automatisch den Default-Wert CURRENT_TIMESTAMP, so daß bei einem neuen Datensatz automatisch die aktuelle Uhrzeit/Datum gespeichert wird.


CREATE TABLE zeit (
tbl01 TIMESTAMP,
tbl02 TIMESTAMP,
txt TINYTEXT );



mysql> DESCRIBE zeit;

Field Type Null Key Default Extra
tbl01 timestamp NOCURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
tbl02 timestamp NO 0000-00-00 00:00:00
txt tinytext YES NULL

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;

1 row in set (0.00 sec)
tbl01 tbl02 txt
2009-08-26 09:33:23 0000-00-00 00:00:00 Hier steht text...

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;

tbl01 tbl02 txt
0000-00-00 00:00:00 2009-08-26 09:43:08 Text (aktualisiert)< ABLE>1 row in set (0.00 sec)





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;

< TD>US ASCII
Charset Description Default collation Maxlen
big5 Big5 Traditional Chinese big5_ chinese_ci 2
dec8 DEC West European dec8_swedish_ci 1
cp850 DOS West European c p850_general_ci 1
hp8 HP West European hp8_english_ci 1
koi8r KOI8-R Relcom Russiankoi8r_general_ci 1
latin1 cp1252 West European latin1_swedish_ci 1
latin2 ISO 8859 -2 Central European latin2_general_ci 1
swe7 7bit Swedish swe7_swedish_ci 1
ascii ascii_general_ci 1
ujis EUC-JP Japanese ujis_japanese_ci 3





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;

TABLE_PRIVILEGES
Tables_in_information_schema
CHARACTER_SETS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICA BILITY
COLUMNS
COLUMN_PRIVILEGES
ENGINES
EVENTS
FILES
GLOBA L_STATUS
GLOBAL_VARIABLES
KEY_COLUMN_USAGE
PARTITIONS
PLUGINS
PROCESSLIST
PROFILING
REFERENTIAL_CONSTRAINTS
ROUTINES
SCHEMATA
SCHEMA_PRIVILEGES
SESSION_STATUS
SESSION_VARIABLES
STATISTICS
TABLES
TABLE_CONSTRAINTS
TRIGGERS
USER_PRIVILEGES
VIEWS

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;

CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH
NULL information_schema utf8 utf8_general_ci NULL
NULL imagedb latin1 latin1_swedish_ci NULL
NULL mysql latin1 latin1_swedish_ci NULL
NULL test latin1 latin1_swedish_ci NULL





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;

Field Type Null Key Default Extra
name char(30) YESNULL
vorname char(30) YES NULL
alt tinyint(4) Y D> NULL
stadt char(50) YES NULL





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;

1+10 REPEAT("Hallo", 2) 3*9
11 HalloHallo 27

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;

 Chip_id  Chiptakt  Speichertakt  Speicher
 68  612  2160  768


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;

RAM In MB
768





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;

 Chip_id  Speichertakt
 68  2160
 45  2020
 25  110
 32  103
 10  100
 8  115
 7  100
 30  100
 18  100
 29  100
 30  100
 29  100
 44  2200



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;

name
Anja
Anja
Anja
Peter
Peter

SELECT DISTINCT(name) FROM multiindex;

name
Anja
Peter

SELECT name FROM multiindex GROUP BY name;

name
Anja
Peter





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;

MAX(TRANSISTOREN) MIN(TRANSISTOREN)
754 1

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

Beispiel:

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

MAX(hersteller) MIN(hersteller)
XGI 3dfx

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(TRANSISTOREN) AVG(TRANSISTOREN)
22393 142.6306


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;

COUNT(*)
884


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;

COUNT(hdd)
804


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;

COUNT(DISTINCT vorname)
62



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;

COUNT(DISTINCT vorname, nachname)
2

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;

GROUP_CONCAT(hersteller)
ATI,Nvidia,XGI,Matrox,PowerVR,3dfx,Intel,S3,3DLabs,ITG


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;

GROUP_CONCAT(hersteller SEPARATOR '-') |
ATI-Nvidia-XGI-Matrox-PowerVR-3dfx-Intel-S3-3DLabs-ITG





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;

section_id  COUNT(*)
0
1
2
3
4
3
3
225
53
158

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;

section_id  vga COUNT(*)
1
1
1
ASUS GeForce ATI
Radeon 9550
ATI Radeon
1
1
2

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;

section_id  MIN(points_result)  MAX(points_result) AVG(points_result)
0
1
2
3
0
749
109
414
9999
17520
40818
24225
2856.8571
5584.6915
8238.8622
11528.2453





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

section_id  vga COUNT(*)
4
8
Radeon X1950 Pro
Radeon X1950 Pro
1
2

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;

name logs
peter
hans
anja
1
1
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.
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;

name logs
peter
hans
anja
1
2
1

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;

i k y
1
2
3
4
1
2
3
4
1
2
3
4

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;

i k y
1
4
2
4
3
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;






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;

name logs
peter
hans
anja
meik
1
3
1
1


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






© 31.08.2009, Juergen Schilling