Webtechnologien Teil 11: Ergänzungen zu...

58
Webtechnologien - WS 2015/16 - Teil 11/MySQL 12.11.15 1 Webtechnologien Teil 11: Ergänzungen zu MySQL

Transcript of Webtechnologien Teil 11: Ergänzungen zu...

Page 1: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

Webtechnologien - WS 2015/16 - Teil 11/MySQL 12.11.15 1

Webtechnologien

Teil 11: Ergänzungen zu MySQL

Page 2: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

2Webtechnologien - WS 2015/16 - Teil 11/MySQL

Literatur

[11-1] Lubokowitz, Mark: HTML CSS PHP JavaScript Perl MySQL SVG. Galileo Computing, 2003, Teil 7

[11-2] Reimers, Stefan; Thies, Gunnar: PHP 5.4 & MySQL 5.5. Galileo Computing, 4. Auflage, 2012

[11-3] Hinz, Stefan; Seeboeger-Weichselbaum, Michael: MySQL 5, GE-PACKT. 2. Auflage, mitp, 2006

[11-4] Kofler, Michael: MySQL. Addison-Wesley, 2001

[11-5] Münz, Stefan: <Professionelle Websites >. Addison-Wesley, 2005

[11-6] Eisentraut, Peter: PostgreSQL GE-PACKT. mitp, 2005

[11-7] http://dev.mysql.com/doc/index.html

[11-8] http://www.tutorialspoint.com/mysql/index.htm

Page 3: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

3Webtechnologien - WS 2015/16 - Teil 11/MySQL

Übersicht

• MySQL ist eine relationale Datenbank, aber keine "richtige" Datenbank wie z.B. Oracle oder DB2.

• Sehr gut für Web-Applikationen geeignet

• Ursprünglich als schnelle, einfache Datenbank konzipiertOptimal für das Lesen von Records

• Entwickelt ab 1995

• Aktuelle Versionen 5.5.*

• Lizenzen: Kommerziell und GPL

• Implementierungen u.a. für– LINUX

– Windows XP, Vista, 7, 8

Dieser Teil ist weder eine Einführung in SQL noch eine in die Installation bzw.Konfiguration. Es werden lediglich Spezialitäten von MySQL vorgestellt.

Page 4: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

4Webtechnologien - WS 2015/16 - Teil 11/MySQL

MySQL als Datenbank-Management-System

• MySQL ist eine Software zur Verwaltung von Tabellen verschiedenen Typs.

• Diese Software realisiert SQL auf diesen Tabellentypen.

• Für jeden Tabellentyp gibt es eine eigene Implementierung.

• Bei der Erstellung einer Tabelle kann der Typ ausgewählt werden. Der Typ bestimmt damit auch die Fähigkeiten.

Siehehttp://de.wikipedia.org/wiki/MyISAMhttp://de.wikipedia.org/wiki/InnoDB http://dev.mysql.com/doc/refman/5.5/en/storage-engines.html

Page 5: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

5Webtechnologien - WS 2015/16 - Teil 11/MySQL

Tabellentypen

Der Typ der Tabellen kann bei ihrer Erzeugung gewählt werden.

Z.B. CREATE TABLE tbl (…) ENGINE = MYISAM;

Folgende Typen sind möglich:

Erläuterung ENGINE

[Default bis 5.1] MYISAM

Veraltetes Format ISAM

Zusammenfassung von MyISAM-Tabellen MERGE

Tabellen im RAM HEAP

Berkeley-DB BDB

Nachbildung von Oracle [Default ab 5.5] InnoDB

Page 6: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

6Webtechnologien - WS 2015/16 - Teil 11/MySQL

Tabellentyp MyISAM

• Eigenschaften von MyISAM– AUTO_INCREMENT-Werte werden nicht wieder verwendet

– BLOB und TEXT können indiziert werden

– Attribute mit NULL-Werten können indiziert werden

– VARCHAR kann Länge 0 haben

– Binärkompatibel zwischen Plattformen

– Werkzeug myisamchk zur Integritätsprüfung vorhanden

– Keine Transaktionen

Siehe:http://dev.mysql.com/doc/refman/5.1/de/myisam-storage-engine.html http://dev.mysql.com/doc/refman/5.1/de/ansi-diff-transactions.html http://dev.mysql.com/doc/refman/5.5/en/myisam-storage-engine.html

Page 7: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

7Webtechnologien - WS 2015/16 - Teil 11/MySQL

Tabellentyp InnoDB

• Eigenschaften von InnoDB– Ursprünglich Nachbau von Oracle

– FOREIGN KEYS(Schlüssel zur direkten Verknüpfung mehrerer Tabellen)

– Schnell bei sehr großen Datenmengen

– Daten werden auf mehreren Dateien abgelegt.

– BLOB und TEXT können nicht indiziert werden.

– Transaktionen

– Ab Version 5.5 Standard

Siehe: http://dev.mysql.com/doc/refman/5.1/de/innodb.html http://dev.mysql.com/doc/refman/5.1/de/innodb-transactions-with-different-apis.html

Page 8: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

8Webtechnologien - WS 2015/16 - Teil 11/MySQL

Abfrage der Tabellentypen

• Es gibt einen MySQL-Interpreter; mit dem folgenden Kommando lassen sich die möglichen Tabellentypen abfragen:

show engines;

Page 9: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

9Webtechnologien - WS 2015/16 - Teil 11/MySQL

Namen I

Objekt Max. Länge Beschränkungen

Datenbank 64 Zeichen Alle erlaubten Zeichen einer/s Datei/Verzeichnisses außer "/" und "."

Tabelle 64 Zeichen Alle erlaubten Zeichen einer/eines Datei/Verzeichnisses außer "/" und "."

Attribut 64 Zeichen Alle erlaubten Zeichen: a-zA-Z0-9_$

Namen dürfen mit beliebigen erlaubten Zeichen beginnen,jedoch nicht ausschließlich aus Ziffern bestehen.

Page 10: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

10Webtechnologien - WS 2015/16 - Teil 11/MySQL

Namen II

• Qualifizierer dienen der eindeutigen Bezeichnung:[[Datenbankname.]Tabellenname.]Spaltenname

• Ohne Angabe von Datenbankname ist die Datenbank aus der letzten USE-Anweisung gemeint.

Namen Regel

Schlüsselwörter Keine Unterscheidung Groß-/Kleinschreibung

Datenbankname Hängt vom Dateisystem ab

Tabellenname Hängt vom Dateisystem ab

Attributname Keine Unterscheidung Groß-/Kleinschreibung

Indexname Keine Unterscheidung Groß-/Kleinschreibung

Aliasname Unterscheidung Groß-/Kleinschreibung

Page 11: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

11Webtechnologien - WS 2015/16 - Teil 11/MySQL

Bemerkungen

• In einigen Fällen werden Tabellennamen als Dateinamen benutzt - das hat folgende Konsequenzen:– Name muss den Konventionen der Dateisysteme entsprechen, also

Vorsicht bei Zeichen, die in einem Dateisystem erlaubt sind und in einem anderen nicht, z. B. der Doppelpunkt.

– Namen sollten sich nicht allein durch Groß-/Kleinschreibung unterscheiden, sonst: Probleme zwischen Windows und Linux.

• Empfehlung bei der Namensvergabe:– "Normale" Namen benutzen: A-Za-z0-9_

– Keine Signifikanz der Groß-/Kleinschreibung

– Maximale Länge: 28 Zeichen

– Keine Benutzung von SQL- oder MySQL-Bezeichnern

Page 12: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

12Webtechnologien - WS 2015/16 - Teil 11/MySQL

Signed Integer

Datentyp Wertebereich von Wertebereich bis

TINYINT(M) -128 (-27) 127 (27-1)

SMALLINT(M) -32 758 (-215) 32 767 (215-1)

MEDIUMINT(M) -8 388 608 (-223) 8 388 607 (223-1)

INT(M) -2 147 483 648 (-231) 2 147 483 647 (231-1)

INTEGER(M) wie INT(M) wie INT(M)

BIGINT(M) -9 223 372 036 854 775 808 (-263) 9 223 372 036 854 775 807 (263-1)

M: Anzahl der Stellen bei Ausgabe (<=255)Der Wert für M hat nichts mit Genauigkeit der internen Darstellung zu tun.

Page 13: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

13Webtechnologien - WS 2015/16 - Teil 11/MySQL

Unsigned Integer

Datentyp Wertebereich von Wertebereich bis

TINYINT 0 255(28)

SMALLINT 0 65 535 (216)

MEDIUMINT 0 16 777 215 (224)

INT 0 4 294 967 295 (232)

INTEGER 0 wie INT

BIGINT 0 18 446 744 073 709 551 615 (264)

Datentyp Speicherplatz

TINYINT 1 byte

SMALLINT 2 byte

MEDIUMINT 3 byte

INT/ INTEGER 4 byte

BIGINT 8 byte

Page 14: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

14Webtechnologien - WS 2015/16 - Teil 11/MySQL

Fließkomma-Zahlen I

Datentyp Von Bis

FLOAT(M,D) -3.402823466E+38 bis-1.176494351E-38

1.175494351E-38 bis 3.402823466E+38

DOUBLE(M,D) -1.7976931348623157E+308 bis -2.2250738585072014E-308

2.2250738585072041E-308 bis 1.7976931348623157E+308

DOUBLE PRECISION (M,D) Wie DOUBLE(M,D)

REAL(M,D) Wie DOUBLE(M,D)

M: Anzeigenbreite (<=255), D: Stellen hinter dem KommaDiese beiden Werte dienen lediglich zur Ausgabe,intern wird immer mit Double-Precision gearbeitet.

Page 15: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

15Webtechnologien - WS 2015/16 - Teil 11/MySQL

Fließkomma-Zahlen II

Datentyp Speicherplatz

FLOAT(M,D) 4 byte

FLOAT(X) X<=24: 4 byte

FLOAT(X) X> 24: 8 byte

DOUBLE(M,D) 8 byte

Für den Datenbank-Entwurf sind die Byte-Angaben der Feldervon Tabellen sehr wichtig, damit die Größe bei großen Datenmengenabgeschätzt und berücksichtigt werden kann.

Page 16: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

16Webtechnologien - WS 2015/16 - Teil 11/MySQL

Strings I

Datentyp Anzahl der Zeichen Index-Größe

CHAR(M) [BINARY] 0..255 1 Byte

VARCHAR(M) [BINARY] 0..255 1 Byte

TINYBLOB 0..255 1 Byte

TINYTEXT 0..255 1 Byte

BLOB 0..65535 2 Bytes

TEXT 0..65535 2 Bytes

MEDIUMBLOB 0..16 777 215 3 Bytes

MEDIUMTEXT 0..16 777 215 3 Bytes

LONGBLOB 0..4 294 967 295 4 Bytes

LONGTEXT 0..4 294 967 295 4 Bytes

Die Index-Größe bestimmt den Wertebereich zur Adressierung von Zeichen.

Page 17: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

17Webtechnologien - WS 2015/16 - Teil 11/MySQL

Erläuterungen

• CHAR(M) [BINARY]: – Angabe der exakten Größe bei Definition

– BINARY erzwingt Unterscheidung der Groß-/Kleinschreibung.

– Wird immer auf volle Länge aufgefüllt.

• VARCHAR(M) [BINARY]:– Es wird die maximale Größe angegeben.

– Ein zusätzliches Byte gibt die tatsächliche Länge an

– BINARY erzwingt Unterscheidung der Groß-/Kleinschreibung

• BLOB (Binary Large Object)Länge je nach Typ:– Belegung+1, Belegung+2 , Belegung+3 , Belegung+4 byte;

– Die zusätzlichen Bytes dienen der Längenangabe

– Unterscheidung Groß-/Kleinschreibung

– In der Realität auf 16 Mbyte beschränkt

Page 18: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

18Webtechnologien - WS 2015/16 - Teil 11/MySQL

Strings II

Zeichen Erläuterung

\\ Backslash

\% %, nur wo Jokerzeichen/Wildchars erlaubt sind

\_ _, nur wo Jokerzeichen/Wildchars erlaubt sind

\0 ASCII 0

\n Newline

\t Tab

\r Carriage Return

\b Backspace

'' ', nur innerhalb einfacher Anführungszeichen

"" ", nur innerhalb doppelter Anführungszeichen

Besondere Zeichenkombinationen innerhalb von Strings

Page 19: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

19Webtechnologien - WS 2015/16 - Teil 11/MySQL

Datum

Datentyp Formate Speicherplatz

DATE YYYY-MM-DD 3 byte

DATETIME YYYY-MM-DD HH:mm:SS 8 byte

TIMESTAMP(M) M=14: YYYYMMDDHHmmSSM=12: YYMMDDHHmmSSM=8: YYYYMMDDM=6: YYMMDD

4 byte

TIME HH:mm:SS 3 byte

YEAR(M) M=2: YYM=4: YYYY

1 byte

Hinweis:Wird der TIMESTAMP-Typ verwendet, wird das entsprechendeAttribut automatisch bei jeder Änderung des Records mit geändert.Dies führt zu Problemen bei Konvertierung in andere Datenbanken, diediese Eigenart nicht haben.

Page 20: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

20Webtechnologien - WS 2015/16 - Teil 11/MySQL

Weitere Datentypen

• BOOLWerte TRUE und FALSE (TINYINT(1))0 wird als FALSE, alles andere als TRUE interpretiert.

• ENUM ('Wert1','Wert2', ..., 'WertN')Aufzählungstyp: Es wird der Index in der Aufzählung abgelegt.

• SET ('Wert1','Wert2', ..., 'WertN')Mengen mit max. 64 Elementen: Feld der Indices in allen Kombinationen

Page 21: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

21Webtechnologien - WS 2015/16 - Teil 11/MySQL

Indices

• INDEX kann auf eine oder mehrere Spalten einer Tabelle angewendet werden– Indices brauchen nicht eindeutig zu sein

– Interne Tabelle mit sortierten Indices liegt in extra Datei

– Diese Datei kann sogar größer als Tabelle werden

• UNIQUE ist wie INDEX, aber– Eindeutige Werte

• PRIMARY KEY ist wie UNIQUE, aber– Pro Tabelle nur einmal

Page 22: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

22Webtechnologien - WS 2015/16 - Teil 11/MySQL

NULL und NOT NULL

• Bei Tabellendefinition zur Kennzeichnung von Pflichtfeldern (NOT NULL)

• NULL ist nicht gleich 0 oder dem leeren String.

• Vergleichsoperation: "IS NULL" oder "IS NOT NULL"Andere Vergleichsoperatoren sind nicht zulässig

• Standardwert eines Attributs: NULL, d.h. optional

• Deklaration mit NOT NULL ist gefordert bei:– PRIMARY KEY

– UNIQUE

– Attribut AUTO_INCREMENT

Page 23: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

23Webtechnologien - WS 2015/16 - Teil 11/MySQL

AUTO_INCREMENT

• Anwendung bei numerischen Feldern, wenn bei INSERT automatisch jeweils um 1 hoch gezählt werden soll

• Bei leerer Tabelle wird 1 eingetragen, ansonsten der höchste Wert + 1, d.h. es können Lücken aufgrund von Löschungen auftreten.

• Bedingungen– Als NOT NULL definiert

– PRIMARY KEY oder UNIQUE

– Nur positive Werte, d.h. kein Vorzeichen

– Nur ein einziges derartiges Attribut pro Tabelle

– Bei MyISAM keine Wiederverwendung schon vergebener Werte

– Löschen aller Datensätze führt zum Zählerstand 1

– Setzen eigener, nicht vorhandener Werte erlaubtNächster Wert ist immer der Größte + 1

Page 24: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

24Webtechnologien - WS 2015/16 - Teil 11/MySQL

Benutzer-Verwaltung

• Aufbau: Name@Hostspezifikation

• Ist der Name leer, so ist der anonyme Nutzer gemeint.

• Hostspezifikation gibt an, von welchem Rechner der angegebene Nutzer Name eine Verbindung aufbauen darf

• Hostspezifikation kann sein:– localhost (127.0.0.1) - nur innerhalb derselben Maschine

– Ohne Hostangabe wird der SQL-Platzhalter % verwendet,was "von jedem Rechner aus" bedeutet

– IP-Adresse

– DNS-Adresse

• Immer vorhandene Benutzer bzw. Namen:– root - Administrator (Initial leeres Passwort: Ändern!)

– PUBLIC - alle Benutzer

Page 25: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

25Webtechnologien - WS 2015/16 - Teil 11/MySQL

Verarbeiten von SQL-Befehlen

• XAMPP-Control benutzen – dies wurde im Teil über die Entwicklungsumgebung erläutert

• Mit PHP über das MySQL-Interface: dies wird in einem der nächsten Teile erläutert.

• Über die Kommando-Eingabe (Shell):– mysql (der SQL-Interpreter)

– mysqladmin

– mysqlshow

Damit diese problemlos über das MSDOS-Eingabefenster (Windows) benutzt werden können, muss der Pfad von MySQL in die globale Umgebungsvariable PATH integriert sein.

Um mit der Datenbank zu kommunizieren, gibt es folgendeMöglichkeiten:

Page 26: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

26Webtechnologien - WS 2015/16 - Teil 11/MySQL

Die wichtigsten SQL-Befehle

Name Erläuterung

CREATE DATABASE Erzeugen einer neuen Datenbank

DROP DATABASE Löschen einer bestehenden Datenbank

USE Datenbank auswählen

CREATE TABLE Anlegen einer neuen Tabellen

EXPLAIN Anzeige der Tabellenstruktur

SHOW Anzeige Datenbanken, Tabellen, Zugriffsrechte

SELECT Abfragen

INSERT Einfügen von Datensätzen

DELETE Löschen von Datensätzen

UPDATE Verändern von Datensätzen

LOAD DATA Kopieren von Daten aus Datei in Tabelle

Page 27: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

27Webtechnologien - WS 2015/16 - Teil 11/MySQL

Beispiele

CREATE TABLE Book ( Id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(255) NOT NULL, Autor VARCHAR(100) NOT NULL, Preis DECIMAL(4,2), Info TEXT, INDEX(Autor));

CREATE INDEX Indexname ON Tabelle (Attribut):

CREATE INDEX Ind ON Book(Id);

Das untere SQL-Konstrukt zeigt, wie ein Index per SQL erzeugt wird.

Page 28: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

28Webtechnologien - WS 2015/16 - Teil 11/MySQL

Funktionen bei SELECT

Funktion Erläuterung

AVG() Mittelwertbildung

MAX() Größter Wert

MIN() Kleinster Wert

SUM() Summe aller Werte

COUNT() Anzahl der Datensätze

SELECT count(*) FROM Book;SELECT AVG(preis), MAX(preis), MIN(preis) FROM Book;

Page 29: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

29Webtechnologien - WS 2015/16 - Teil 11/MySQL

LOAD DATA I

LOAD DATA [LOCAL] INFILE Datei INTO TABLE Tabelle [Optionen]

In den Optionen wird das Dateiformat angegeben; Default ist CSV mit TAB, jeder Record endet mit CR/LF

Beispiel:LOAD DATA INFILE "MeineBuecher.txt" INTO TABLE Book;

Optionen:

FIELDS TERMINATED BY Trennzeichen zwischen den Attributen

LINES TERMINATED BY Trennzeichen zwischen den Zeilen

Beispiel:LOAD DATA INFILE "MeineBuecher.txt" INTO TABLE BookFIELDS TERMINATED BY "$" LINES TERMINATED BY 0x03;

Page 30: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

30Webtechnologien - WS 2015/16 - Teil 11/MySQL

LOAD DATA II

1,Kritik der reinen Vernunft,Kant,"17,80",Philosophie2,Hamlet,Shakespeare,"32,00",Drama3,Die Leiden des jungen Werthers,Goethe,"15,20",Drama4,Die Physiker,Dürrenmatt,"7,50",Drama

Den initialen Tabelleninhalt in eine Excel-Tabelle eingeben unddann als CSV ausgeben – Auf den Trenner zwischen den Elementenachten - hier ist es ein Komma.Die Tabelle kann dann mit LOAD DATA zusammen mitFIELDS TERMINATED BY "," geladen werden.

Page 31: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

31Webtechnologien - WS 2015/16 - Teil 11/MySQL

Das Laufzeitverhalten steuernde Variablen

Um globale Variablen abfragen zu können (Beispiel):

SHOW GLOBAL VARIABLES like 'connect_timeout';

Um lokale Variablen abfragen zu können (Beispiel):

SHOW SESSION VARIABLES like 'insert_id';

Page 32: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

32Webtechnologien - WS 2015/16 - Teil 11/MySQL

Entwurfswerkzeuge

• DBDesigner 4.0.5.6 (Windows, Client)http://fabforce.net/downloads.php– Kostenlos– Graphische Definition von Tabellen– Generierung von SQL-Anweisungen zum Import nach MySQL– Gute Integration mit MySQL-Server

• Workbench 6.3.*http://dev.mysql.com/downloads/tools/workbench/ftp://ftp.gwdg.de/pub/misc/mysql/Downloads/MySQLGUITools/

• phpMyAdmin (unabhängig, Web-Interface, läuft auf Server)http://www.phpmyadmin.net/home_page/downloads.php– Kostenlos– Web-Schnittstelle– Keine Grafik

Page 33: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

33Webtechnologien - WS 2015/16 - Teil 11/MySQL

Werkzeuge (Windows) I

Werkzeug Erläuterung

mysql.exe Client mit Befehlsschnittstelle

mysqladmin.exe Verwaltungswerkzeug

mysqld.exemysqld-opt.exemysqld-nt.exe

Datenbankserver für Windows 9* und NT/2000/XP

mysqldump.exe Backup-Werkzeug zum Entladen

mysqlimport.exe Kommando- Schnittstelle zur SQL-LOAD DATA-Anweisung

mysqlshow.exe Kommando-Schnittstelle zur SQL-SHOW-Anweisung

Diese Werkzeuge gibt es - leicht im Namen geändert – auch für LINUX.

Page 34: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

34Webtechnologien - WS 2015/16 - Teil 11/MySQL

Werkzeuge (Windows) II

Damit die Verwaltungsprogrammedirekt in der MSDOS-Eingabeboxeingegeben werden können, mussder Pfad in PATH gesetzt werden:Bei XAMPP (Beispiel):c:\Programme\xampp\mysql\bin

Page 35: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

35Webtechnologien - WS 2015/16 - Teil 11/MySQL

Starten und Herunterfahren des Servers I

Falls der MySQL-Server nicht automatisch gestartet wurde:

• Start des Servers:mysqld

• Herunterfahren des Servers:mysqladmin -u root shutdown

• Prüfen, ob der Server funktioniert:mysqlshow

Entsprechend der Definition muss ein Passwort angegeben werden.

Page 36: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

36Webtechnologien - WS 2015/16 - Teil 11/MySQL

Starten und Herunterfahren des Servers II

Mit XAMPP lässt sich dies unter Windows ganz leicht durchführen.

Page 37: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

37Webtechnologien - WS 2015/16 - Teil 11/MySQL

mysqladmin I

Option Option Erläuterung

-? --help Listet mögliche Optionen und Anweisungen

-h --host Angabe der Station mit Datenbankserver

-p PW --password=PW Angabe des Passworts PW

-u B --user=B Angabe des Benutzernamens B

mysqladmin [Optionen] Anweisung Anweisung ...

Anweisung Erläuterung

create Name Anlegen einer neuen Datenbank

drop Name Löschen einer bestehenden Datenbank

password=PW Ändern des Passworts zu PW

ping Prüfen ob Server noch läuft

shutdown Server herunterfahren

status Status des Datenbankservers anzeigen

Page 38: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

38Webtechnologien - WS 2015/16 - Teil 11/MySQL

mysqladmin II - Beispiele

• Administrator-Passwort definieren:mysqladmin -u root --password=PW

• Aktualisieren der Änderungen an Tabellen:mysqladmin -u root -p reload

• Erzeugen einer neuen Datenbank:mysqladmin -u root -p create DataBase

• Löschen einer bestehenden Datenbank:mysqladmin -u root -p drop DataBase

Die Angabe –p dient dazu, dass das Passwort interaktiv abgefragt wird.

Page 39: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

39Webtechnologien - WS 2015/16 - Teil 11/MySQL

mysqlshow - Beispiele I

• Ausgabe aller bestehenden Datenbanken:mysqlshow -u root -h localhost -p

• Ausgabe aller Tabellen der Datenbank DataBase:mysqlshow -u root -h localhost -p DataBase

• Ausgabe aller Attribute von Tabelle User von DataBase:mysqlshow -u root -h localhost -p DataBase User

Page 40: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

40Webtechnologien - WS 2015/16 - Teil 11/MySQL

mysqlshow - Beispiele II

SQL-Anweisung Erläuterung

SHOW DATABASES Listet alle Datenbanken

SHOW STATUS Ausgabe diverser Informationen

SHOW VARIABLES Ausgabe diverser Variablen

SHOW WARNINGS Zeigt die aktuellen Warnungen

SHOW CREATE TABLE Erzeugt ein SQL-Ausdruck zum Anlegen der angegebenen Tabelle

SHOW COLUMNS Listet die Attribute der angegebenen Tabelle

SHOW INDEX Listet den Indexwert der angegebenen Tabelle

SQL-Befehle zum Analysieren bestehender Datenbanken:

Page 41: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

41Webtechnologien - WS 2015/16 - Teil 11/MySQL

mysql - der SQL-Interpreter I

mysql -u User [-h Host] [--password=Passwort] [-D Datenbank]

Parameter Erläuterung

User Name der Nutzers

Host IP-Adresse oder DNS-Name des Systems mit dem MySQL-Server

Datenbank Name der Datenbank auf dem Server

Initial nach der Installation hat der User root kein Passwort.Wird nur -p angegeben, wird interaktiv das Passwort verlangt.

Page 42: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

42Webtechnologien - WS 2015/16 - Teil 11/MySQL

mysql - der SQL-Interpreter II

So sieht es dann aus, wenn der mysql-Interpreter aufgerufen wurde.

Page 43: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

43Webtechnologien - WS 2015/16 - Teil 11/MySQL

mysql - der SQL-Interpreter II

Kommando Kürzel Erläuterung

help \h Optionen anzeigen

exit \q mysql beenden (quit geht auch)

; \g Anweisung an den Server schicken

use DB \u DB Datenbank DB auswählen

connect \r Erneut verbinden

source \. Eine externe SQL-Datei ausführen

status \s Status des Servers ausgeben

Ein Kommando wird erst dann ausgeführt, wenn es eines derobigen Kommandos ist oder wenn es mit ";" oder "\g"abgeschlossen wird, d.h. die obigen Kommandos starten auchohne abschließendes Semikolon.

Page 44: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

44Webtechnologien - WS 2015/16 - Teil 11/MySQL

Beispiel Datenbank "buecher" I

• Es wird eine kleine, aus einer einzigen Tabelle bestehende Datenbank erzeugt.

• Die Datenbank heißt "buecher", deren Tabelle wird durch die obige SQL-Expression definiert.

CREATE TABLE buch (ISBN CHAR(13) NOT NULL,Autor CHAR(20),Titel CHAR(30),Verlag INTEGER,PRIMARY KEY (ISBN)

);

Page 45: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

45Webtechnologien - WS 2015/16 - Teil 11/MySQL

Beispiel Datenbank "buecher" II

Die Datenbank wird erzeugt, was nichts anderes bedeutet, als dass...

Page 46: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

46Webtechnologien - WS 2015/16 - Teil 11/MySQL

Beispiel Datenbank "buecher" III

ein leererOrdner er-zeugt wird

Page 47: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

47Webtechnologien - WS 2015/16 - Teil 11/MySQL

Beispiel Datenbank "buecher" IV

Aber das Arbeiten geht!

Page 48: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

48Webtechnologien - WS 2015/16 - Teil 11/MySQL

Beispiel Datenbank "buecher" V

Jetzt wird die Tabelle eingerichtet....

Page 49: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

49Webtechnologien - WS 2015/16 - Teil 11/MySQL

Beispiel Datenbank "buecher" VI

Nun befinden sich drei Dateien im Datenbank-Ordner

Page 50: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

50Webtechnologien - WS 2015/16 - Teil 11/MySQL

Beispiel Datenbank "buecher" VII

Auch phpMyAdmin bestätigt dies... (hier eine ältere Version)

Page 51: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

51Webtechnologien - WS 2015/16 - Teil 11/MySQL

Typische mysql-Sitzung

mysql -u root -puse DataBase;SQL-Kommando;....SQL-Kommando;exit

Es wird das Password abgefragt

Hier wird die Datenbank ausgewählt

Hier kommen die einzelnen Kommandos

Ende der interaktiven Sitzung

Am besten ist es, interaktiv einzelne Kommandos auszuprobieren,und dann eine Kommandokette, z. B. die Tabellendefinitionen, ineine Datei schreiben, die dann mittel "<" in das Kommando mysqlumgeleitet wird.Für verschiedene Versuche werden verschiedene Dateien aufgebaut.So kann auch leicht von einer Maschine auf eine andere "umgezogen"werden.

Page 52: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

52Webtechnologien - WS 2015/16 - Teil 11/MySQL

Dump und Import I

mysqldump [Optionen] Datenbank > Datei

Es sollen alle Datenbanken auf dem Server entladen werden: mysqldump -u root --password=root -A >backup.txt

Es soll nur die Datenbank DataBase entladen werden: mysqldump -u root --password=root DataBase >backup.txt

Das neue Aufsetzen einer Datenbank erfolgt anhand der hier generierten Datei mit mysql.

Page 53: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

53Webtechnologien - WS 2015/16 - Teil 11/MySQL

Dump und Import II

mysqlimport [Optionen] Datenbank Datei

Die Optionen entsprechen denen von mysqladmin.Der Name der zu füllenden Tabelle ergibt sich aus dem vorderenTeil des Dateinamens Datei.

Beispiel: mysqlimport DataBase User.txt

Hier wird für die Datenbank DataBase die Tabelle User anhandder Daten aus der Datei User.txt gefüllt.

Page 54: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

54Webtechnologien - WS 2015/16 - Teil 11/MySQL

Datenbank umziehen I

Auf dem Source-System:

mysqldump -u root --password=pw webdb >webdb.sql

Auf dem Ziel-System:

mysql -u root --password=pwDROP DATABASE webdb;CREATE DATABASE webdb;use webdb;source webdb.sql;exit;

Page 55: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

55Webtechnologien - WS 2015/16 - Teil 11/MySQL

Datenbank umziehen II

Wem das zu umständlich ist, schreibt sich ein bat-Skript:

dbinstall.bat:mysqladmin -u root -f --password=root drop $1mysqladmin -u root -f --password=root create $1mysql -u root --password=root -D $1 <$1.sql

Dieses wird dann z. B. mit

dbinstall webdb

aufgerufen und erwartet eine Datei webdb.sql mit den SQL-Befehlenvon mysqldump.

Page 56: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

56Webtechnologien - WS 2015/16 - Teil 11/MySQL

Datenbank umziehen III

Da das interne MySQL-Format bei Benutzung von MyISAM-Tabellenplattformunabhängig ist, reicht es aus

nach Beendigung des MySQL-Servers(!)den Ordner mit den DB-Daten

zu kopieren. Dies gilt nur für MyISAM-Tabellen, die ab Version 5.5nicht mehr Standard sind.

Bitte beachten Sie, dass Sie auch verschiedene VersionenIhrer Tabellen(-Inhalte) verschieden benennen sollten,so dass immer klar ist, was die aktuelle Version ist.

Page 57: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

57Webtechnologien - WS 2015/16 - Teil 11/MySQL

Prüfen der Tabellen mit myisamchk

Aufruf nach Herunterfahren des Servers:myisamchk Optionen Pfad-zum-Ordner/Tabelle.MYI

Der Pfad-zum-Ordner ist der Ordner, in dem für jede Datenbank einweiterer Unterordner vorhanden ist, in dem sich die Tabellen befinden.

Prüfen aller Tabellen aller Datenbanken z.B.:myisamchk Optionen E:\data\*\*.MYI

Als Optionen können u.a. benutzt werden:-c Prüfen-T Als ungeprüft kennzeichnen-U Prüfzustand abspeichern-q Prüfung ohne Reparatur-r Recover (Zaubermittel zur Reparatur)

mysqlcheck macht dasselbe, aber bei laufendem Server.

Page 58: Webtechnologien Teil 11: Ergänzungen zu MySQLwi.f4.htw-berlin.de/.../Folien/Wiederholung/D-WT-MySQL-1.pdf · 2019-09-26 · Webtechnologien - WS 2015/16 - Teil 11/MySQL 5 Tabellentypen

58Webtechnologien - WS 2015/16 - Teil 11/MySQL

Nach dieser Anstrengung etwas Entspannung...