DDL Uebung - bs7-augsburg.de · che SQL Statements müssen in einem eigenen File...

DDL Uebung AnPr

Name Klasse Datum

ANPR_05_DDL_Uebung_v05.docx Seite 1

1 Vorbereitung Ziel dieses Aufgabenpaketes ist es, das Verhalten von Datenbanken bezüglich ihrer Datenstruktur zu verste-

hen. Wie müssen Tabellen strukturiert sein, damit die gewünschten Daten sinnvoll gespeichert werden können.

Für diese Übung muss eine MySQL Datenbank laufen. Weiterhin ist der MySQL – Client notwendig. Sämtli-

che SQL Statements müssen in einem eigenen File „DDL_Uebung.sql“ abgespeichert werden. Alle notwendi-

gen Informationen zur Bearbeitung dieser Aufgaben finden sich im SQL – Syntax Skript, welches Sie entweder

auf dem Klassenlaufwerk oder unter

http://www.bs7-augsburg.de/aicher/files_codeconcert/12SQL/SQL_Syntax.pdf

2 CREATE TABLE Statement Zuerst muss eine Datenbank erzeugt und genutzt werden. Wir nennen unsere Datenbank

„FirstTestDB“ und erzeugen sie mit dem CREATE DATABASE Statement. Anschließend wird sie

mit „USE“ geöffnet.

3 Einfaches Create TABLE Statement Als nächstes erzeugen wir uns eine Tabelle mit dem Namen „InsertTestTab“. Damit wir das Verhalten mög-

lichst gut analysieren können, sollen viele unterschiedliche Datentypen vorhanden sein:

Spaltenname: Datentyp:

ID INT

GanzeZahl1 INT

GanzeZahl2 INT

Zeichen1 VARCHAR(10)

Zeichen2 VARCHAR(10)

Datum1 DATE

Datum2 DATE

Die Details zu den Datentypen werden wir uns später ansehen. An dieser Stelle wollen wir uns ausschließlich

um das Verhalten der Tabelle bei Einfüge-Operationen kümmern.

Bevor wir weitermachen, schauen wir uns die Struktur der Tabelle mit dem DESC Befehl an:

DESC InsertTestTab;

4 INSERT Statement

Wir schreiben nun einen Datensatz mit dem INSERT-Statement. Hierzu nutzen wir zwei verschiedene Ver-

sionen dieses Statements:

INSERT INTO InsertTestTab SET

ID = 2,

GanzeZahl1 = 2,

GanzeZahl2 = 3,

Zeichen1 = "A",

Zeichen2 = "B",

Datum1 = "2000-01-02",

Datum2 = "2003-04-05";

bzw.

http://www.bs7-augsburg.de/aicher/files_codeconcert/12SQL/SQL_Syntax.pdf

DDL Uebung AnPr

Seite 2

INSERT INTO InsertTestTab

(ID, GanzeZahl1, GanzeZahl2, Zeichen1, Zeichen2, Datum1, Datum2) VALUES

(1, 2, 3, "A", "B", "2000-01-02", "2003-04-05");

Beide Statements liefern das gleiche Ergebnis. Dies können wir durch einen einfachen SELECT Befehl fest-

stellen:

SELECT * FROM InsertTestTab;

Die Frage ist nun, welches der beiden INSERT Statements wofür sinnvoller ist. Die erste Syntaxvariante ist

aber aufgrund der Schreibweise Spalte = Wert viel übersichtlicher und weniger Fehleranfällig als die zweite

Variante, da in der zweiten die Beziehung zwischen Spaltenname und Wert nur über die Reihenfolge sicher-

gestellt wird.

Das zweite Statement wiederum wird genutzt, wenn mehrere Datensätze auf einmal geschrieben werden müs-

sen:

INSERT INTO InsertTestTab

(ID, GanzeZahl1, GanzeZahl2, Zeichen1, Zeichen2, Datum1, Datum2) VALUES

(3, 2, 3, "A", "B", "2000-01-02", "2003-04-05"),

(4, 5, 6, "C", "D", "2001-02-03", "2004-05-06");

Insofern wird empfohlen, bei mehreren Datensätzen die zweite Variante zu nehmen und bei einzelnen Datens-

ätzen die erste Variante.

5 Nullable und Defaultwerte Nun schreiben wir einen neuen Datensatz, allerdings wollen wir NULL Werte in unseren Spalten erreichen.

Der einfachste Weg ist es, die NULL Werte direkt in die Tabelle einzutragen. Dies ermöglichen wir mit dem

folgenden Statement:


ID = 5,

GanzeZahl1 = NULL,

GanzeZahl2 = NULL,

Zeichen1 = NULL,

Zeichen2 = NULL,

Datum1 = NULL,

Datum2 = NULL;

Wenn wir mit unserem SELECT Statement das Ergebnis betrachten, sehen wir die erwarteten Zustände der

Spalten – nämlich NULL. Nun versuchen wir folgendes Statement:


ID = 6;

Wie wir sehen, erreichen wir mit dieser verkürzten Version das gleiche Ergebnis. Dies liegt daran, dass ein

Auslassen einer Spalte im Statement keinen Wert schreibt, was aber im Normalfall NULL bedeutet. Nun lö-

schen wir unsere Tabelle wieder mit folgendem Statment:

DROP TABLE IF EXISTS InsertTestTab;

(Anm.: der Zusatz „IF EXISTS“ macht Skripte sicherer – sie produzieren keinen Fehler, wenn sie zweimal

hintereinander laufen und die Tabelle vielleicht nicht existiert). Jetzt erzeugen wir die Tabelle neu, ergänzen

jedoch unsere Spalten um einige weitere Eigenschaften:

AnPr DDL Uebung

Seite 3

Spaltenname: Datentyp: Eigenschaft:

ID INT

GanzeZahl1 INT NOT NULL

GanzeZahl2 INT NOT NULL DEFAULT 99

Zeichen1 VARCHAR(10) NOT NULL

Zeichen2 VARCHAR(10) NOT NULL DEFAULT "XX"

Datum1 DATE NOT NULL

Datum2 DATE NOT NULL DEFAULT "9999-12-31"

Ein kurzer Check mit dem DESC Befehl zeigt uns nun die neuen Eigenschaften. Versuchen wir nun das State-

ment nochmal:


ID = 1;

Wie wir sehen, haben die Spalten nun alle definierten Werte. Tragen wir diese in die Tabelle ein:

Spaltenname: Datentyp: Eigenschaft: Wert:

ID INT GanzeZahl1 INT NOT NULL GanzeZahl2 INT NOT NULL DEFAULT 99 Zeichen1 VARCHAR(10) NOT NULL Zeichen2 VARCHAR(10) NOT NULL DEFAULT "XX" Datum1 DATE NOT NULL Datum2 DATE NOT NULL DEFAULT "9999-12-31"

NOT NULL verhindert also, dass in den Spalten der Zustand NULL entsteht, indem ein Standardwert gesetzt

wird. Wem dieser nicht gefällt, kann mit Hilfe von „DEFAULT“ einen eigenen Wert setzen.

Die Standardwerte werden aber nur dann gesetzt, wenn die Spalte im Statement keine Berücksichtigung findet.

Sollten wir explizit einen NULL Wert setzen, so wird das Statement mit einer Fehlermeldung abbrechen.

6 Primärschlüssel Für die nächste Übung benötigen wir eine neue Tabelle, mit folgenden Datentpyen:


Vorname VARCHAR(30)

Nachname VARCHAR(30)

Geburtsdatum DATE

PLZ CHAR(5)

Wir erstellen die Tabelle und fügen nun zwei identische Datensätze ein:

INSERT INTO KeyTestTab (Vorname, Nachname, Geburtsdatum, PLZ) VALUES

("Hans", "Maier", "1991-04-21", "12345"),

("Hans", "Maier", "1991-04-21", "12345");

Wir haben nun zwei Datensätze, welche nicht voneinander unterscheidbar sind. Wenn wir nun einen dieser

Datensätze löschen wollen, können wir ihn nicht benennen. Diese Situation ist für eine saubere Datenbasis

sehr ungünstig; zumal es kaum Situationen gibt, in denen wir wirklich zwei absolut voneinander unabhängige

Datensätze brauchen – im Regelfall ist solch eine Situation ein Fehlerfall! Wenn wir diesen nun bereinigen

DDL Uebung AnPr

Seite 4

wollen, gibt es keine Möglichkeit nur einen von den beiden Datensätzen zu löschen, da das DELETE Statement

eine eindeutige Identifikation benötigt:

DELETE FROM KeyTestTab WHERE Nachname = "Maier";

Wenn wir dieses Statement ausführen, so werden beide Datensätze gelöscht sein. Es gibt kein Statement wie

„Lösche den ersten von beiden“.

Die Einzige Möglichkeit, dieses Dilemma zu umgehen ist es nun, dass die Datenbank sich darum kümmert,

dass jeder einzelne Datensatz eindeutig identifizierbar ist. Dies wird mit dem „Primary Key“ (Primärschlüssel)

ermöglicht. Wir löschen die Tabelle also wieder und erzeugen sie neu:

CREATE TABLE KeyTestTab (

Vorname VARCHAR(30),

Nachname VARCHAR(30),

Geburtsdatum DATE,

PLZ CHAR(5),

PRIMARY KEY(Vorname, Nachname)

);

Wir haben nun noch eine weitere Eigenschaft hinzugefügt – den PRIMARY KEY. Primärschlüsselfelder sind

automatisch NOT NULL, was wir mit Hilfe des DESC Befehls sehen können. Sehen wir uns das Verhalten

der Tabelle bezüglich gleicher Datensätze nochmal an, indem wir die beiden Datensätze von vorhin nochmal

eintragen.

Wie wir feststellen, wird der erste Datensatz geschrieben, der zweite wird aber aufgrund einer Primärschlüs-

selverletzung abgewiesen – wir können also keine zwei gleichen Datensätze schreiben – wobei die Gleichheit

über die Felder „Vorname“ und „Nachname“ definiert wird. Folgendes Statement wird also auch nicht möglich

sein:


("Peter", "Maier", "1971-02-20", "12345"),

("Peter", "Maier", "1991-04-21", "12345");

Obwohl es sich offensichtlich um zwei verschiedene Peter Mayer handelt (das sehen wir an den unterschied-

lichen Geburtsdaten), dürfen sie nicht geschrieben werden, da Vorname und Nachname identisch sind. Um

dies nun zu ermöglichen (Peter Mayer ist nun ein nicht ganz seltener Name), müssten wir das Geburtsdatum

ebenfalls in die Liste der Primärschlüssel aufnehmen. Aber wenn wir es genau nehmen, ist auch dies theore-

tisch problembehaftet, da es bestimmt zwei verschiedene Personen gibt, die gleich heißen und das gleiche

Geburtsdatum haben.

Grundsätzlich lernen wir hieraus, dass natürliche Schlüssel (also Primärschlüssel auf Basis von Spalten, wel-

che Daten des eigentlichen Objektes halten) keine gute Idee sind! Wir brauchen Alternativen.

Üblicherweise wird dies durch ein zusätzliches Schlüsselfeld erledigt, welches „künstliche Schlüssel“ beinhal-

tet:


ID INT,



Geburtsdatum DATE,

PLZ CHAR(5),

PRIMARY KEY(ID)

);

Nun können wir die Datensätze schreiben – müssen uns aber für jeden Datensatz einen eigenen künstlichen

Schlüssel überlegen (im folgenden Beispiel die 1 und die 2):

AnPr DDL Uebung

Seite 5

INSERT INTO KeyTestTab (ID, Vorname, Nachname, Geburtsdatum, PLZ) VALUES

(1, "Peter", "Maier", "1971-02-20", "12345"),

(2, "Peter", "Maier", "1991-04-21", "12345");

Das ist jetzt für den Programmierer etwas umständlich, stets sicher zu sein, dass die ID des neuen Datensatzes

wirklich eindeutig ist. Der Weg zuerst zu prüfen, ob die ID nicht existiert um sie anschließend zu schreiben ist

problematisch, da zwischen der Prüfung und dem Schreiben ein anderer Client die ID zufällig schreiben könnte

– das System wäre also bei konkurrierenden Zugriffen fehlerbehaftet. Aus diesem Grund haben die RDBMS

im Regelfall einen Automatismus für solche Situationen implementiert – das AUTO_INCREMET Feld.

7 AUTO_INCREMENT Feld Löschen wir die Tabelle nochmal und legen sie mit folgendem Statement neu an:


ID INT AUTO_INCREMENT,



Geburtsdatum DATE,

PLZ CHAR(5),

PRIMARY KEY(ID)

);

Wir haben das ID Feld nun so konfiguriert, dass es die eindeutige ID automatisch über ein Hochzählen selbst

erstellt. Dies prüfen wir, indem wir die beiden Datensätze wieder schreiben, allerdings ohne das ID Feld:


("Peter", "Maier", "1971-02-20", "12345"),

("Peter", "Maier", "1991-04-21", "12345");

Ein SELECT auf die Tabelle zeigt uns nun, dass wir in der ID Spalte tatsächlich unterschiedliche Nummer

wiederfinden. Versuchen wir nun, das Verhalten von AUTO_INCREMENT in MySQL nun etwas näher zu

analysieren:

• Schreibe drei weitere Datensätze in die Tabelle, ohne die ID fest anzugeben

• Lösche den Datensatz mit der ID 5

• Schreibe einen neuen Datensatz in die Tabelle, ohne die ID fest anzugeben

• Schreibe einen neuen Datensatz wobei der ID Wert fest auf 10 gesetzt ist

• Schreibe einen neuen Datensatz in die Tabelle, ohne die ID fest anzugeben

• Schreibe einen neuen Datensatz wobei der ID Wert fest auf 9 gesetzt ist

Die Interpretation der Ergebnisse liefert nun folgende Erkenntnis:

• AUTO_INCREMENT beginnt bei 1 zu zählen

• Der nächste Wert ist immer um 1 höher, als der bis dahin (historisch) höchste Wert in der Tabelle –

also auch wenn dieser bereits gelöscht wurde

• Lücken können nur gefüllt werden, wenn die ID explizit angegeben wird

Hiermit lassen sich also eindeutige IDs erzeugen, welche auch nach dem Löschen (also auch historisch) ein-

deutig bleiben.

Aufgrund dieser Eigenschaften sind AUTO_INCREMENT Felder oft in Datenbanken zu finden.

DDL Uebung AnPr

Seite 6

8 Foreign Key Feld Als nächstes kümmern wir uns um die referenzielle Integrität. Hierfür müssen wir unseren USBWebserver

umkonfigurieren. Dort im Ordner mysql finden wir die Datei „my.ini“. Der Eintrag „skip-innodb“ muss aus-

kommentiert werden (in der neuen Version von USBWebserver wird diese Zeile nicht mehr vorhanden sein –

man hat dort INNODB nun auch als default Engine eingeführt, weshalb dort diese Schritte entfallen):

#skip-innodb

Anschließend muss Mysql neu gestartet werden (Achtung – nicht der USBWebserver, denn das würde die

Konfiguration wieder zurücksetzen).

Nun können wir zwei Tabellen mit einer Fremdbeziehung erstellen:

CREATE TABLE Kunde (

K_ID INT PRIMARY KEY,

Name VARCHAR(30)

) ENGINE INNODB;

CREATE TABLE Bestellung (

B_ID INT PRIMARY KEY,

K_ID INT,

Datum DATE,

FOREIGN KEY (K_ID) REFERENCES Kunde(K_ID)

) ENGINE INNODB;

Die Angabe INNODB ist nur dann notwendig, wenn INNODB nicht die default Engine ist. Jetzt fehlen noch

die Daten. Für die Analyse reichen uns zwei Datensätze:

INSERT INTO Kunde SET K_ID = 1, Name = "a";

INSERT INTO Bestellung SET B_ID = 10, K_ID = 1, Datum = DATE(NOW());

Versuche nun das Verhalten dieser Tabellen zu analysieren, indem Daten neu eingetragen, gelöscht oder ver-

ändert werden.

Statement: Verhalten:

INSERT INTO Bestellung SET B_ID = 10,

K_ID = 2, Datum = DATE(NOW());

DELETE FROM Kunde WHERE K_ID = 1;

UPDATE Kunde SET K_ID = 2 WHERE

K_ID = 1;

9 Alter Table Statement Tabellen können nachträglich beliebig verändert werden. Hierzu verwenden wir den Syntax des „ALTER TA-

BLE“ Statement, welches aufgrund der vielen Optionen eine sehr umfangreiche Dokumentation unter

„mysql.org“ erhalten hat.

Finde heraus, wie man eine VARCHAR(20) Spalte Namens Name zu der Tabelle InsertTestTab hinzufügen

kann, wie anschließend der Spaltenname auf „Vorname“ geändert wird und die Spalte wieder löschen werden

kann. Probiere die Statements aus.

Gehen wir nochmal kurz auf den Fall ein, dass eine Spalte hinzugefügt wird – und zwar in einer Tabelle,

welche bereits Datensätze beinhaltet. Hierbei steht die Frage im Raum, mit welchen Werten die Spalten initia-

lisiert werden. Prüfe folgende drei Fälle:

AnPr DDL Uebung

Seite 7

Eigenschaft der Spalte: Initialer Wert:

INT

INT NOT NULL

INT DEFAULT 99

Wie wir sehen, ist das Verhalten identisch mit der Situation, dass das Feld in einem Insert nicht explizit be-

legt wird (siehe Kapitel 5).

Nun ändern wir die Eigenschaft einer Tabelle. Dies geht mit zwei verschiedenen Varianten des ALTER TA-

BLE Befehls. Gehen wir mal davon aus, dass wir aus der Spalte Zeichen1 ein VARCHAR(20) Feld machen:

ALTER TABLE InsertTestTab CHANGE Zeichen1 Zeichen1 VARCHAR(20);

Das sieht natürlich etwas „merkwürdig“ aus, da wir den Bezeichner der Tabellenspalte zweimal angeben müs-

sen. Insofern wird das Ändern der Spalteneigenschaften im Regelfall wie folgt erledigt:

ALTER TABLE InsertTestTab MODIFY Zeichen1 VARCHAR(20);

Hiermit werden sämtliche Eigenschaften der Spalte Zeichen1 neu bestimmt. Die heißt aber auch, dass wenn

bspw. ein Defaultwert gesetzt wäre, dieser nun nicht mehr gesetzt ist.

Im Wesentlichen sprechen wir hier von einem „Typecast“, den die Datenbank hier durchführt. Insofern ist es

nun für uns von Interesse, wie die Daten innerhalb der Tabelle bei eventuellen Konflikten verändert werden.

Für die Nächste Übung sehen wir uns die folgende Tabelle an. Alle vorgegebenen Werte sollen in einer ent-

sprechenden Tabellenspalte hinterlegt werden. Mit Hilfe eines ALTER TABLE Statements sollen nun die

Werte geändert werden und das Ergebnis des Typecasts notiert werden. (Bspw. muss der Wert 10 in ein Feld

vom Typ INT geschrieben werden und anschließend geprüft werden, wie der Wert sich verändert, wenn aus

dem Feld ein TINYINT, DATE, VARCHAR(2) oder VARCHAR(10) Datentyp gemacht wird.)

INT TINYINT DATE VARCHAR(2) VARCHAR(10)

10

10000

„2000-12-31“

„2000-12-31“

„Hallo“

„1234567“

„2000-99-99“

DDL Uebung AnPr

Seite 8

10 INDEX Erstellung Für das nächste Thema erinnern wir uns nochmal an die Tabellen aus dem Ka-

pitel für Foreing Keys. Hier haben wir die rechts stehende Situation abgebildet.

Die Kunden können Bestellungen durchführen.

Der Zusammenhang zwischen den beiden Tabellen wird über die K_ID herge-

stellt. Wenn ein Kunde mit der K_ID 1 eine Bestellung tätigt, so wird diese ID im Datensatz der entsprechen-

den Bestellung zu finden sein. Sehen wir uns hierzu ein paar Beispieldaten an:

Basierend auf den K_ID Werten können wir also feststellen, sie der Kunde zu jeder Bestellung heißt. Gehen

wir nun davon aus, dass wir von jeder Bestellung jeden Kundennamen haben wollen, dann muss über die K_ID

ein Abgleich von beiden Tabellen gemacht werden. Wenn wir uns nun überlegen, was der Computer hier

leisten muss, dann kommen wir auf folgende Schritte:

Nehme aus Tabelle „Bestellung“ jeden einzelnen Datensatz und vergleiche ihn mit allen Datensätzen der

Tabelle.

Dies bedeutet, dass er 5x4 Prüfungen durchführen muss. Dies ist insofern problematisch, weil wir sehr selten

„nur“ 5 Datensätze in einer Tabelle haben. Üblicherweise geht es in die Millionen. In solch einem Fall würden

wir Laufzeiten erlangen, welche nicht mehr tragbar sind. Um dies zu demonstrieren, sehen wir uns folgende

zwei Tabellen an, welche „bewusst“ inperformant gestaltet wurden:



Vorname VARCHAR(20)

Geburtsdatum DATE

Beruf VARCHAR(60)



Vorname VARCHAR(20)

Geburtsdatum DATE

Ort VARCHAR(60)

Die beiden Tabellen beinhalten Berufs- und Wohnortangaben von Personen. Diese wiederum werden über den

Nachnamen, Vornamen und dem Geburtsdatum identifiziert. Dies ist wie gesagt eher schlecht designet, für die

Demonstration der Indizes ist es jedoch hilfreich.

Die Datenbank „BigDB“ beinhaltet nun die beiden Tabellen Wohnort (647448 Datensätze) und Beruf (323724

Datensätze). Wenn wir also einen Abgleich wie oben beschrieben machen wollen, muss der Rechner ca. 200

Mrd. Vergleiche durchführen (jeweils auf drei Feldern). Dies wird nicht in einer sinnvollen Zeit zu bewerk-

stelligen sein. Insofern müssen wir dem Rechner helfen!

Die Lösung für dieses Problem heißt „Index“. Man kann sich einen Index vorstellen, wie ein Schlagwortver-

zeichnis in einem Buch. Wenn wir also in einem Buch alle Kapitel zum Thema „SQL“ suchen, können wir

AnPr DDL Uebung

Seite 9

entweder alle Seiten durchblättern (also ohne Index vorgehen), oder wir suchen uns im Schlagwortverzeichnis

alle relevanten Kapitel und gehen dann direkt auf die entsprechenden Seiten. Auf der Datenbank werden durch

einen Index (genau wie in einem Buch) extra Daten geschrieben, welche zugriffsoptimiert vorliegen. Sehen

wir uns das mal an:

Im obigen Beispiel haben wir nun eine Indextabelle für K_ID in der Tabelle „Bestellung“ erstellt. Dadurch

können wir die Zuordnung zu der Kundentabelle von oben nach unten geordnet darstellen – wir haben die

Tabelle somit „geordnet“. Sind beide Seiten „geordnet“ – wenn also das Feld K_ID sowohl in der Kundenta-

belle, als auch in der Tabelle „Bestellung“ über einen Index verfügen, können wir nun bei der Zuordnung

beider Tabellen, die Logik verändern. Die hat eine radikale Reduktion der Vergleichsvorgänge zur Folge. Die

Schritte, welche der Rechner nun durchführen muss sind:

• Nehme den Ersten Datensatz aus der K_ID Indextabelle der Bestellungen und vergleiche ihn mit dem

ersten in der Kundentabelle.

• Wenn Gleichheit herrscht, haben wir einen Match. Wenn nicht und der Wert in der Kundentabelle ist

kleiner, nehme den nächsten in der Kundentabelle. Wenn er größer ist, den nächsten Wert in der K_ID

Indextabelle der Bestellungen.

In der Konsequenz haben wir somit nicht mehr MxN Vergleiche, sondern M+N. Für unsere BigDB mit 647448

bzw. 323724 Datensätzen sind es somit unter 1 Mio Vergleiche!

Versuchen wir dies mit der BigDB auszuprobieren. Wir kopieren die BigDB in ein lokales Verzeichnis und

starten das Batchfile „CreateDatabase.bat“. Im Folgenden müssen wir das Installationsverzeichnis der MySQL

Binaries angeben – also bspw.:

C:\Program Files\MySQL\MySQL Server 5.7\bin

Und schließlich noch das MySQL Passwort. Die Datenbank installiert sich nun von selbst. Nun starten wir den

MySQL Client und öffnen die Datenbank mit USE BigDB. Wenn wir nun folgendes Statement eingeben wür-

den,

SELECT count(*) FROM Beruf b, Wohnort w

WHERE b.Nachname = w.Nachname

AND b.Vorname = w.Vorname

AND b.Geburtsdatum = w.Geburtsdatum;

müssten wir je nach Rechner bis zu 24h warten, bis das Ergebnis zurückkommt. Dieses Statement zählt alle

Datensätze aus den Tabellen Beruf und Wohnort, welche über die Felder Vorname, Nachname und Geburts-

datum zusammenhängen (also die 200 Mrd. Vergleiche).

Versuchen wir also von vorneherein mit einem Index zu arbeiten. Hier bieten sich zwei Möglichkeiten – ent-

weder wir erstellen pro Spalte einen eigenen Index, oder wir erstellen einen kombinierten Index. Beginnen wir

mit den getrennten Indizes. Der Syntax für das Anlegen eines Indexes für die Spalte „Vorname“ in der Tabelle

„Beruf“ lautet:

DDL Uebung AnPr

Seite 10

CREATE INDEX idx_beruf_vorname ON Beruf (Vorname);

Da jeder Index einen Namen benötigt, wählen wir eine Konvention, in der die Tabelle und die Spalte auftaucht.

Der Befehl läuft auch bei großen Datenmengen nur einige Sekunden. Nun folgen alle weiteren Spalten, sodass

wir je einen Index für Vorname, Nachname und Geburtsdatum in den beiden Tabellen Beruf und Wohnort

haben.

Wenn wir nun das Select ausführen werden wir sehen, dass die Ausführungszeit nun im zweistelligen Sekun-

denbereich liegen wird:

Ausführungszeit mit getrennten Indizes:

Löschen wir nun die Indizes wieder. Der Syntax lautet:

DROP INDEX idx_beruf_vorname ON Beruf;

Jetzt probieren wir den Kombinierten Index aus. Wir erstellen also pro Tabelle nur einen Index, in den wir alle

relevanten Spalten eintragen.

CREATE INDEX idx_beruf ON Beruf (Vorname, Nachname, Geburtsdatum);

CREATE INDEX idx_wohnort ON Wohnort (Vorname, Nachname, Geburtsdatum);

Nun starten wir unser SELECT nochmal und notieren wieder die Ausführungszeit:

Ausführungszeit mit kombiniertem Index:

Wie wir sehen, ist die Ausführungszeit noch weiter gesunken, was jedoch nicht wundern dürfte, da der Rechner

sich nun den Abgleich der einzelnen Indextabellen spart.

Wir können also festhalten, dass wir bei Feldern, über die Verknüpfungen abgebildet werden, stets ein Index

liegen sollte – immerhin haben wir die Ausführungszeit von mehreren Stunden auf ein paar Sekunden senken

können. Bei Primary Key Feldern ist dies automatisch der Fall, bei den Fremdschlüsselfeldern nicht immer

(nur bei InnoDB, aber auch dann nur, wenn wir die Fremdschlüssel beim CREATE TABLE Statement ange-

geben haben).

11 LOAD DATA INFILE Daten können wir nicht nur mit Hilfe des INSERT INTO Statements in die Datenbank laden. Vor allem größere

Datenmengen sollten mit einem alternativen Verfahren geladen werden – dem LOAD DATA INFILE. Dieser

Befehl ist MySQL spezifisch – bei anderen Datenbanken finden sich andere Lösungen (wie SQL*Loader bei

Oracle oder dem DB2 LOAD Befehl).

Grundgedanke dieser Befehle ist es, aus einem Textfile (auch „Flatfile“ genannt) die Daten strukturiert in die

Datenbank zu laden. Hierzu muss mit Hilfe des Befehls (oder wie beim SQL*Loader auch mit Hilfe eines

Kontrollfiles) dem System mitgeteilt werden, wie die Daten zu laden sind. Sehen wir uns hierzu mal ein ein-

faches File an, welches in unsere KeyTestTab Tabelle geladen werden kann:

100 Peter Lustig 2000-01-01 12345

101 Karl Huber 1999-02-02 54321

Wir erzeugen also ein einfaches Textfile „myData.txt“ und geben die genannten Daten ein. Die Werte werden

über einen Tabulator getrennt. Nun versuchen wir das File in unsere Tabelle zu laden:

AnPr DDL Uebung

Seite 11

LOAD DATA INFILE 'C:/tmp/myData.txt'

INTO TABLE KeyTestTab

LINES TERMINATED BY '\r\n';

Der Befehl lädt also die Daten aus dem File direkt in die Tabelle. Hierbei muss aber folgendes sichergestellt

sein:

• Die Reihenfolge der Datenspalten muss mit der Reihenfolge der Tabellenspalten übereinstimmen

• Das Textfile darf keine Überschriften haben

• Das Textfile muss Tabsepariert sein

• In dem Textfile dürfen die Felder nicht mit Anführungsstrichen eingeschlossen sein

Versuchen Sie nun den Befehl so abzuändern, dass das Folgende File auch korrekt gelesen werden kann:

;ID:;NN:;VN:;GBD:;PLZ:

1->200;"Lustig";"Peter";"2000-01-01";12345

2->201;"Huber";"Karl";"1999-02-02";54321

Hinweis: Bei der Nutzung des Befehls LOAD DATA INFILE ist der häufigste Fehler eine falsche Reihenfolge

der Befehle. Im Internet steht eine sehr ausführliche Beschreibung dieses Befehls, inklusive notwendiger Rei-

henfolge!

DDL Uebung AnPr

Seite 12

12 Lizenz Diese(s) Werk bzw. Inhalt von Maik Aicher (www.codeconcert.de) steht unter einer

Creative Commons Namensnennung - Nicht-kommerziell - Weitergabe unter gleichen

Bedingungen 3.0 Unported Lizenz.

http://www.codeconcert.de/

DDL Uebung - bs7-augsburg.de · che SQL Statements müssen in einem eigenen File...

Documents

Transcript of DDL Uebung - bs7-augsburg.de · che SQL Statements müssen in einem eigenen File...