Druckansicht
Kontakt
RSS-Newsfeed
Sitemap
Impressum
Prüfungsvorbereitung zur MySQL-Zertifizierung
8.1 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;
8.2 Creating Tables
Eine Tabelle muss 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);
# Anlegen mehrerer Tabellen
CREATE TABLE tbl_01 (id INT, name CHAR(30), alter TINYINT);
# Explizite Angabe der Datenbank
CREATE TABLE db001.tbl_01 (id INT);
8.2.2 Specifying the Storage Engine for a Table
Wie bereits erwähnt kann man festlegen, welche Speicher-Engine verwendet werden soll. Die einzelnen Storage Engines sind für unterschiedliche Einsätze optimiert worden. Welche Engines die aktuelle MySQL-Installation unterstützt kann man sich mit folgendem Befehl anzeigen lassen:
mysql> SHOW ENGINES;
+------------+---------+----------------------------------------------------------------+
| Engine | Support | Comment |
+------------+---------+----------------------------------------------------------------+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys |
| BerkeleyDB | YES | Supports transactions and page-level locking |
| BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE | NO | Example storage engine |
| ARCHIVE | NO | Archive storage engine |
| CSV | NO | CSV storage engine |
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |
| FEDERATED | NO | Federated MySQL storage engine |
| MRG_MYISAM | YES | Collection of identical MyISAM tables |
| ISAM | NO | Obsolete storage engine |
+------------+---------+----------------------------------------------------------------+
Hier die wichtigsten Befehle in der Übersicht:
# 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;
8.2.3 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, muss 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, dass man beim Kopieren einer Tabelle mit dem Befehl SELECT die gewünschten Datensätze in die neue Tabelle übernehmen kann.
8.2.4 Using TEMPORARY Tables
Eine temporäre Tabelle wird wie der Name schon sagt dafür gebraucht, um bsp. 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 Tabell kann nur mit ALTER TABLE, nicht aber RENAME TABLE umgenannt werden
8.3 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
8.3.1 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;
8.3.2 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)
mysql> ALTER TABLE zeit CHANGE mitglieder members int(15);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> DESCRIBE zeit;
+---------+-----------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------+------+-----+---------------------+-------+
| tbl01 | timestamp | NO | | 0000-00-00 00:00:00 |
| members | int(15) | YES | | NULL | |
+---------+-----------+------+-----+---------------------+-------+
8.3.3 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;
# Vorteil durch RENAME: mehrere Tabellen können gleichzeitig umbenannt werden
RENAME TABLE tbl_01 TO tbl_neu, tbl_01 TO vereine, tbl_03 TO staedte;
8.3.4 Specifying Multiple Table Alterations
Mehrere Änderungen können komma-separiert geschrieben werden:
ALTER TABLE tbl_01 RENAME TO vereine, MODIFY members TINYINT, ADD names CHAR(30) AFTER spalte001;
8.4 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;
8.5 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:
# Teilweise löschen
DELETE FROM tbl_01 WHERE id > 50;
8.6 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.
8.6.1 Types of Indexes
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 merhfach vorkommen.
Des Weiteren gibt es folgenden speziellen Index:
Fulltext Index
Wird für Textsuche benutzt
8.6.2 Creating Indexes
In folgenden Rubriken wird beschrieben, wie man Indizes erstellt.
8.6.2.1 Defining Indexes at Table Creation Time
Ein Index bei der Erstellung einer Tabelle wird folgendermaßen angelegt:
# Einen non-unique Index erstellen
mysql> 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
mysql> 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, dass man doppelte Werte im Index hat:
# Unique-Index erstellen
mysql> 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
8.6.2.2 Creating and Using Primary Keys
Mit folgenden Befehlen erstellt man eindeutige Indizes, so dass 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 muss NOT NULL sein)
mysql> CREATE TABLE tbl01 (
-> id NOT NULL,
-> name CHAR(30),
-> PRIMARY KEY (id)
-> );
# Dieselbe Schreibweise in Kurzform
mysql> CREATE TABLE tbl01 (
-> id NOT NULL PRIMARY KEY,
-> name CHAR(30),
-> );
# Verwendung von Unique als eindeutigen Identifier
# Entspricht PRIMARY KEY
mysql> 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)
mysql> 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)
mysql> 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, dass 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.
8.6.2.4 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 muss existieren)
ALTER TABLE tbl01 ADD PRIMARY KEY (id);
# Hinzufügen eines Indexes
ALTER TABLE tbl01 ADD INDEX (vorname, nachname);
# Dasselbe 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.
8.6.3 Choosing an Indexing Algorithm
Für die Speicherengine MEMORY (folgendes ist nur für MEMORY wirksam, andere Speicherengines ignoriren die Angaben), kann man den Index-Algorithmus festlegen.Umgangssprachlich kann man sagen, dass der Algoithmus festlegt, auf welche Art und Weise das Inhaltsverzeichnis angelegt wird. Für unterschiedliche Anforderungen gibt es verschiedene Algorithmen.
Standardmäßig ist HASH der Algorithmus, man kann diesen aber folgendermaßen ändern:
mysql> CREATE TABLE people (
-> id INT,
-> INDEX USING BTREE (id)
-> );
mysql> ALTER TABLE people ADD INDEX USING BTREE (id);
8.7 Dropping Indexes
Um einen Index (und nicht die Spalte) zu entfernen, kann man die Befele ALTER TABLE und DROP INDEX verwenden:
# PRIMARY KEY INDEX entfernen
ALTER TABLE tbl01 DROP PRIMARY KEY;
ALTER TABLE tbl01 DROP INDEX NameIndex;
ALTER TABLE tbl01 DROP UNIQUE;
# Dasselbe mit dem Befehl DROP INDEX
DROP INDEX `PRIMARY KEY` ON tbl01;
DROP INDEX NameIndex ON tbl01;
8.8 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:
mysql> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = "zeit" AND TABLE_SCHEMA = "test"\G;
*************************** 1. row ***************************
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:
1 row in set (0.02 sec)
Der Befehl SHOW TABLES FROM test zeigt alle Tabellen einer Datenbank an:
mysql> SHOW TABLES FROM test; +----------------+
| Tables_in_test |
+----------------+
| multiindex |
| nullen |
| tbl01 |
| zeit |
+----------------+
4 rows in set (0.00 sec)
Nach Tabellen suchen – LIKE %
Manchmal muss man nach einer Tabelle suchen, wenn man den genauen Namen nicht kennt. Hier hilft die Option LIKE in Kombination mit % weiter:
# Tabellen anzeigen, die mit mu beginnen
mysql> SHOW TABLES FROM test LIKE 'mu%';
# Tabellen anzeigen, die tr enthalten
mysql> SHOW TABLES FROM test LIKE '%tr%';
# Tabellen anzeigen, die auf en enden
mysql> SHOW TABLES FROM test LIKE '%en';
INDEX einer Tabelle anzeigen – SHOW INDEX FROM
Mit SHOW INDEX FROM kann man sich die Indizes einer Tabelle anzeigen lassen:
mysql> SHOW INDEX FROM tbl01\G;
*************************** 1. row ***************************
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:
mysql> DESCRIBE tbl01;
mysql> SHOW COLUMNS FROM tbl01;
mysql> SHOW FIELDS FROM tbl01;