MySQL - Ein Überblick

 

Was ist MySQL überhaupt?

MySQL, bzw. der MySQL Server ist ein relationales Open-Source Datenbankverwaltungssystem. Darunter versteht man die elektronische Datenverwaltung in Computersystemen, in diesen Fall nach dem relationalen Datenbankmodell.

 

Wie ist eine relationale Datenbank aufgebaut?

Wie es der Name schon vermuten läßt, basiert die theoretische Grundlage dieses Datenbankmodells auf der relationalen Algebra. Dabei geht es um die Relationen (also die Abhängigkeiten, die zwischen Dingen bestehen), welche in Form einer Tabelle beschrieben wird.

Begriffe welche in der relationalen Algebra vorkommen, finden auch Verwendung im relationalen Datenbankmodell:

  • Relation <-> Tabelle
  • Tupel <-> Zeile
  • Kardinalität <-> Anzahl der Tupel (Zeilen)
  • Attribut <-> Spalte
  • Grad <-> Anzahl der Attribute (Spalten)
  • Primärschlüssel <-> Primärschlüssel (eindeutiger Identifikator)
  • Wertebereich <-> Typ (Werte die ein Attribut, eine Spalte einnehmen kann)
  • Skalar <-> Wert (Wert eines Attributes in einem Tupel)

 

Dieses Beispiel soll die oben genanten Begriffe praktisch veranschaulichen:

Notenliste

schueler_nr vorname name fach note
10 Max Mustermann Kunst 2
24 Maxi Musterfrau Kunst 5
10 Max Mustermann EDV 4
24 Maxi Musterfrau EDV 1

 

Es fängt an mit dem Tabellenname "Notenliste". Dies wird auch Relationenname genannt und ist vom Grad 5 und der Kardinalität 4. Nun folgen die Spaltenüberschriften (Relationsschema), die den Datensatz beschreiben (schueler_nr, vorname, name, fach und note).  Die Spalten spiegeln unsere oben genannten Attribute wieder. Der beschriebene Datensatz, also eine Zeile der Relation (10, Max, Mustermann, Kunst, 2) bildet ein Tupel. Und der Wert eines Attributes in einem Tupel (z. B. das Attribut "note" des Tupel [24, Maxi, Musterfrau, EDV, 1] , also der Wert "1") das Skalar. Um später mit den Werten arbeiten zu können, wird bei der Konzeption der Tabelle schon festgelegt von welchen Typ eine Spalte ist. Sprich, welchen Wertebereich darf die Spalte einnehmen. Wenn wir uns z. B. das Attribut "note" anschauen, überlegen wir uns im Vorfeld was für Werte vorkommen können. Das österreichische Notensystem hat einen Wertebereich von 1 bis 5. Also besteht unser wählbarer Wertebereich aus positiven, ganzen Zahlen. Wenn wir uns nun die von MySQL verwendeten Datentypen anschauen:

TINYINT 0 bis 255 oder von -128 bis +127
SMALLINT 0 bis 65.535 oder von -32.768 bis +32.767
MEDIUMINT 0 bis 16.777.215 oder von -8.388.608 bis +8.388.607
INT 0 bis ~4,3 Mill. oder von -2.147.483.648 bis +2.147.483.647
INTEGER 0 bis ~4,3 Mill. oder von -2.147.483.648 bis +2.147.483.647
BIGINT 0 bis 2^64-1 oder von -(2^63) bis (2^63)-1
FLOAT Fließkommazahl, -3,402823466^38 bis -1,175494351^38, 0 und 1,175494351^38 bis 3,402823466^38
DOUBLE Fließkommazahl, -1,798^308 bis ~ -2,225^-308, 0 und ~ 2,225^-308 bis ~ 1,798^308
REAL Fließkommazahl, -1,798^308 bis ~ -2,225^-308, 0 und ~ 2,225^-308 bis ~ 1,798^308
DECIMAL Fließkommazahl, vorzeichenbehaftet. Speicherbedarf: x=1 wenn D=0, sonst x=2
NUMERIC Fließkommazahl, vorzeichenbehaftet. Speicherbedarf: x=1 wenn D=0, sonst x=2
DATE Datum im Format 'YYYY-MM-DD'. 01.01.1000 bis 31.12.9999
DATETIME Datumsangabe im Format 'YYYY-MM-DD hh:mm:ss'
TIMESTAMP Zeitstempel. 1.1.1970 bis 2037. Ab Version 4.1 Format 'YYYY-MM-DD hh:mm:ss'
TIME Zeit zwischen -838:59:59 und +839:59:59. Ausgabe: 'hh:mm:ss'
YEAR Jahr zwischen 1901 bis 2155
CHAR Zeichenkette fester Länge M. 0 bis 255 Zeichen
VARCHAR Zeichenkette variabler Länge, Maximum ist M. Wertebereich für M: 0 bis 255 Zeichen
BLOB Binäres Objekt mit variablen Daten
TEXT Wie BLOB. Berücksichtigt beim Sortieren & Vergleichen die Groß- und Kleinschreibung nicht
ENUM Liste von Werten (val1, val2 ...). Maximal 65.535 eineindeutige Elemente sind möglich
SET String-Objekt mit verschiedenen Variablen. Maximal 64 'Mitglieder' sind möglich

fällt unsere Auswahl schnell auf den Typ "TINYINT". Auch wenn heutzutage der Arbeitsspeicher und ähnliches nicht mehr zwangsläufig den Flaschenhals eines Datenbanksystems darstellt, sollte für die Performance trotzdem auf einen nicht zu groß dimensionierten Wertebereich geachtet werden.

Da eine Datenbank meist aus mehreren Tabellen besteht, ist es zu dessen Verknüpfung sehr wichtig die Datensätze eindeutig zuordnen zu können. Dazu bedienen wir uns dem eindeutigen Identifikator "Primärschlüssel". Der Primärschlüssel darf sich nach der Erstellung nicht mehr ändern und muss eindeutig sein. In dem Beispiel wäre es die Schülernummer "schueler_nr". Wenn ich nun z. B. eine zweite Tabelle "Fehlstunden" hätte welche mir von jedem Schüler die Fehlstunden abspeichert, würde ich auch dort als Primärschlüssel die Schülernummer eingeben und könnte nun die Datensätze zu einander in Verbindung setzen.

schueler_nr vorname name fach note
10 Max Mustermann Kunst 2
24 Maxi Musterfrau Kunst 5
10 Max Mustermann EDV 4
24 Maxi Musterfrau EDV 1

 

schueler_nr fehlstunden
10 5
24 7

Nun wüsste ich durch die entsprechenden SQL-Abfragen, das Max Mustermann 5 Fehlstunden hatte und Maxi Musterfrau sogar 7. Somit haben wir anhand des Primärschlüssels 2 Tabellen miteinander verknüpft. Dieses Beispiel zeigt uns auch eine weitere wichtige Sache im Datenbankdesign, Redundanz ist in Datenbanken nicht erwünscht. Um bei dem Beispiel zu bleiben, wäre es nicht wünschenswert in der Fehlstundentabelle noch den Vor- und Nachnamen zu speichern, da er durch die Verknüpfung der beiden Tabellen durch die Schülernummer ganz einfach abrufbar ist.

Nachdem wir uns nun einen Überblick des Aufbaus verschaft haben, geht es nun dazu über SQL-Abfragen zu gestalten. Dazu gibt uns MySQL ein gewissen Befehlssatz an die Hand, welcher im wesentliche auf dem ANSI SQL Standard basiert.

 

 

Befehlsreferenz

CREATE ALTER
DROP RENAME
SELECT INSERT
UPDATE DELETE
DO REPLACE
TRUNCATE

Die Grundbefehlsreferenz besteht, wie oben beschrieben, aus 11 Befehlen.

Die Wichtigsten bzw. meistgenutzten Befehle werden anhand der CRUD Matrix sichtbar,

CRUD

Create: INSERT

  • um neue Daten zu speichern

Read: SELECT

  • um Daten abzurufen

Update: UPDATE

  • um Daten zu bearbeiten

Delete: DELETE

  • um Daten zu entfernen

welches eine gute Technik ist um Tabellen einer Datenbank aufzuspüren, welche z. B. bei einer Website für die Interaktion zwischen User und Website verantwortlich sind.

Um aber erst einmal Tabellen zu haben, müssen wir die Datenbank und die Tabellen zunächst erstellen.

Um eine Datenbank zu erstellen, nutzt man den Befehl

 

CREATE DATABASE

  • CREATE DATABASEschule
    • erstellt die Datenbank "schule"
    • diese wird anhand der vorgegebenen Struktur von MySQL erstellt, da der ANSI Standard die Struktur komplett dem Datenbanksystem überlässt.

Wie man sieht ist dieser Part sehr einfach und genau so einfach ist das löschen einer Datenbank. Dies geschieht mit dem Befehl:

 

 

DROP DATABASE

  • DROP DATABASEschule
    • löscht die Datenbank "schule"
    • löscht damit auch sämtliche Inhalte der Datenbank "schule"

Nun haben wir eine Datenbank namens "schule" angelegt, aber es fehlen uns noch Tabellen um mit ihr sinnvoll arbeiten zu können. Dazu bedienen wir uns

 

 

CREATE TABLE

  • für die Erstellung von Tabellen brauchen wir schon ein paar mehr Infos, dazu gehört zumindest
    • der Tabellenname
    • der Spaltenname
    • und der Datentyp der Spalten
      • Die möglichen Datentypen können der oben stehenden Tabelle entnommen werden.
    • optional (diese Parameter müssen bei der Erstellung einer Tabelle nicht mit angegeben werden)
      • NULL Zulässigkeit
        • NULL bedeutet nicht 0, sondern "kein Wert vorhanden". Mögliche Werte sind:
          • NULL
          • NOT NULL
      • Defaultwert
        • setzt den Wert einer Spalte fest, wenn bei einer INSERT Anweisung kein Wert angegeben wurde.
      • Primärschlüssel
        • es ist sinnvoll direkt bei der Erstellung der Tabelle anzugeben, welche Spalte den Primärschlüssel beinhaltet. Also das Bindeglied zwischen den Tabellen.

Ich hatte oben im Beispiel eine Tabelle "Notenliste" und eine Tabelle "Fehlstunden". Diese erstelle ich nun in der Datenbank "schule" mittels CREATE TABLE.

CREATE TABLE Notenliste

(

schueler_nr tinyint PRIMARY KEY,

vorname char NOT NULL,

name char NOT NULL,

fach char(10) NOT NULL,

note tinyint DEFAULT 0

)

CREATE TABLE Fehlstunden

(

schueler_nr tinyint PRIMARY KEY,

fehlstunden tinyint NOT NULL

)

Nun haben wir eine Datenbank "schule" mit den Tabellen

"Notenliste"

schueler_nr vorname name fach note

und "Fehlstunden" erstellt

schueler_nr fehlstunden

 

 

Bevor ich zu dem Themenbereich "Wie bekomme ich meine Daten in die Datenbank" komme, möchte ich noch kurz aufzeiten wie man die Datenbank und die Tabellen bearbeiten kann, falls uns ein Fehler bei der Erstellung unterlaufen ist. Die Schlagwörter sind hier ALTER TABLE, DROP TABLE und TRUNCATE TABLE.

 

ALTER TABLE

  • Für die Änderung der Tabellendefinition
    • RENAME
      • zur Änderung des Tabellennamens
    • ADD
      • fügt der Tabelle eine neue Spalte hinzu
    • CHANGE
      • ändert die Definition einer Spalte
    • DROP
      • entfernt Spalten
  • Dies sieht in der praktischen Umsetzung dann so aus:

ALTER TABLE "Tabellenname" RENAME "Neuer Tabellenname";

ALTER TABLE "Tabellenname"

ADD "Spaltenname" Datentyp Attribute

CHANGE "Spaltenname" "Neuer Spaltenname" Datentyp Attribute

DROP "Spaltenname";

DROP TABLE

  • zum löschen einer Tabelle samt Tabellendefinition
    • dies bedeutet das die Tabelle samt Inhalt, Berechtigungen, Indizes usw. aus der Datenbank gelöscht wird

DROP TABLE "Tabellenname";

TRUNCATE TABLE

  • zum löschen aller Zeilen (also sämtlicher Inhalt) einer Tabelle, aber die Struktur der Tabelle bleibt bestehen.
  • Dieser Befehl ist gleichzusetzen mit einer DELETE .... FROM .... Anweisung
    • Allerdings deutlich schneller, ABER
    • bei TRUNCATE protokolliert das System die Änderungen nicht mit!!! Sprich, sie sind nicht rückgängig zu machen.

TRUNCATE TABLE "Tabellenname";

 

 

Mit diesen Befehlen sind wir nun im Bereich der Datenmanipulation angelangt. Dazu gehören noch die Befehle INSERT INTO, UPDATE und DELETE

INSERT INTO

  • zum einfügen eines Datensatzes in eine Tabelle
  • zur Eingabe eines Datensatzes, braucht das System für jede Spalte einen Wert. Wenn dieser nicht vom Nutzer angegeben wird, wird der festgelegte Defaultwert verwendet. Wenn kein Defaultwert definiert ist, trägt das Datenbanksystem NULL als Wert ein. Ist für eine Spalte kein Defaultwert angebeben und wurde die Spalte als NOT NULL gekennzeichnet, wirft das System einen Error aus.

INSERT INTO "Tabellenname" (Spaltenname1, Spaltenname2,..., Spaltenname)

VALUES (WertSpaltenname1, WertSpaltenname2,..., WertSpaltenname);

  • die Spaltenlistung (Spaltenname1, Spaltenname2,..., Spaltenname), ist optional. Wenn die Listung weggelassen wird, fügt er die Werte, welche unter VALUE angegeben werden, in der original Reihenfolge den Spalten hinzu. Wenn ich aber die Spaltenlistung angebe, kann man die Werte auch in anderer Reihenfolge in die Datenbank schreiben (Spaltenname1, Spaltenname5, Spaltenname 2,..., Spaltenname). Dies kann natürlich sehr hilfreich sein, wenn ich die Werte z. B. aus einen Array übernehme und ich dieses nicht neu ordnen möchte. Ausserdem dient die Angabe der Spaltenlistung auch der besseren Lesbarkeit.

 

UPDATE

  • mit INSERT INTO können wir nun ganze Datensätze in die Tabelle einfügen. Der Befehl UPDATE ist da spezifischer. Mit UPDATE kann man, gewünschte Kriterien entsprechend, einzelne Daten geziehlt modifizieren.

UPDATE "Tabellenname"

SET "Spaltenname" = "Wert";

  • Optional kann man eine WHERE Klausel hinzufügen, um noch mehr eingrenzen zu können welche Datensätze modifiziert werden sollen.

UPDATE "Tabellenname"

SET "Spaltenname" = "Wert"

WHERE "Bedingung";

DELETE

  • mittels der DELETE Anweisung, löscht man Datensätze aus der Datenbank. Auch diese Anweisung kann mittels einer optionalen WHERE Klausel spezifiziert werden.

DELETE FROM "Tabellenname"

WHERE "Bedingung";

 

 

Nachdem wir nun eine Datenbank samt Tabellen und Inhalte erstellen können, fehlt uns noch die Möglichkeit den Inhalt der Datenbank abzufragen ("query"). Dies geschieht mit der SELECT-Anweisung und deren Derivate.

SELECT

  • die einfachste Art der Abfrage listet alle Spalten und Zeilen einer Tabelle auf

SELECT * FROM "Tabellenname";

  • Dies ist natürlich sehr allgemein gehalten, verbraucht sehr viel Resourcen und liefert ggf. eine große Menge an nicht gebrauchten Ergebnissen. Deshalb ist es sinnvoller zu versuchen das gewünschte Ergebniss zielgenau zu erreichen. z. B. durch Einschränkung auf bestimmte Spalten einer Tabelle.

SELECT "Spaltenname1", "Spaltenname2" FROM "Tabellenname";

  • Hier werden nun alle Zeilen der Spalten 1 und 2 der gewünschten Tabelle ausgegeben und nicht mehr der Inhalt aller Spalten. Das Ergebniss kann natürlich, je nach Datensatzlage, doppelte Einträge beinhalten. Um nur unterschiedliche Ergebnisse zu bekommen, kann man die Abfrage noch weiter eingrenzen.

SELECT DISTINCT "Spaltenname"

FROM "Tabellenname";

  • Nun sind wir schon soweit, das wir kein doppelten Eintrag als Ergebniss bekommen. Wenn wir diese Beispiele an einer konkreten Datenbank ausprobieren, werde wir schnell merken, das unser Ergebnioss nicht unbedingt lesbar sein muss, da unsere Spaltenbeschriftung häufig aus Abkürzungen besteht. Dies können wir sehr leicht ändern (wobei die Beschriftung auf der Datenbank sich nicht ändert, sondern nur ein Alias für die Ausgabe erzeugt wird, mit welchen ich aber bei komplexen Abfragen auch weiter arbeiten kann).

SELECT "Spaltenname1" AS "Wunschname", "Spaltenname2"

FROM "Tabellenname";

  • Jetzt haben wir schon die ersten Ergebnisse bekommen und sehen das sie uns wohl zu allgemein sind. Deshalb fangen wir nun an die Ergebnisse einzuschränken. Dazu nutzen wir am häufigsten die WHERE Klausel.

SELECT "Spaltenname1", "Spaltenname2"

FROM "Tabellenname"

WHERE "Spaltenname2" = "XYZ";

  • Als Ergebniss werden uns nun alle Einträge der Spalten "Spaltenname1" und "Spaltenname2" aufgelistet, bei denen "Spaltenname2" den Inhalt "XYZ" hat. MySQL unterstützt dabei nicht nur den Vergleichsoperator "=" sondern noch folgende andere:
    • < - Kleiner als
    • > - Größer als
    • = - Gleich
    • != , <> - Ungleich
    • "Wert" BETWEEN "min" AND "max" - Ergibt 1 (true) wenn der "Wert" größer gleich "min" ist und wenn der Wert kleiner gleich "max" ist
    • "Wert" NOT BETWEEN "min" AND "max"- Ergibt 1 (true) wenn der "Wert" NICHT größer gleich "min" ist und wenn der "Wert" NICHT kleiner gleich "max" ist
      • NOT ("Wert" BETWEEN "min" AND "max")
    • "Wert" IS "boolean" , "Wert" IS NOT "boolean" - Vergleicht ob ein "Wert" boolean (true, false, unknown) ist oder ob ein "Wert" nicht boolean ist.
    • "Wert" IS NULL, "Wert" IS NOT NULL - Vergleicht ob ein "Wert" NULL ist oder nicht.
  • Es gibt noch ein paar mehr Vergleichsoperatoren, aber dies sind die am häufig genutzten. Um in einer WHERE Klausel mehrere Vergleiche durchführen zu können, bedint man sich den logischen Operatoren
    • OR bzw. || - Logisches Oder
    • AND bzw. && - Logisches Und
    • NOT - Logisches Nicht

z. B.

SELECT "Spaltenname1", "Spaltenname2"

FROM "Tabellenname"

WHERE "Spaltenname2" = "XYZ" AND "Spaltenname1" != 2;

  • Wenn wir nun eine Abfrage über mehrere Spalten machen, möchten wir natürlich auch festlegen wonach das Ergebniss sortiert wird. Dies geschieht mittels ORDER BY

SELECT "Spaltenname1", "Spaltenname2"

FROM "Tabellenname"

WHERE "Spaltenname2" = "XYZ"

ORDER BY "Spaltenname2";

  • Die Sortierung geschieht immer aufsteigend (ASC - ascending sort) wenn ich nichts expliziet angebe. Wenn ich die Sortierung aber absteigend haben möchte, muss ich DESC - descending sort angeben.

SELECT "Spaltenname1", "Spaltenname2"

FROM "Tabellenname"

WHERE "Spaltenname2" = "XYZ"

ORDER BY "Spaltenname2" DESC;

  • Bisher haben wir die Abfragen immer auf eine Tabelle bezogen. Nun kann es aber vorkommen, das ich die gewünschten Daten auf mehrere Tabellen verteilt habe. Um an diese Daten zu kommen, muss man mit dem sogenannten JOIN arbeiten. Dabei gibt es mehrere Varianten.
    • INNER JOIN ist die Verschmelzung mehrerer Tabellen. Wenn es einen Wert gibt, der in beiden Tabellen vorkommt, wird ein Ergebniss ausgebeben, ansonsten wird nichts ausgegeben
    • FULL OUTER JOIN zeigt generell alle Werte beider Tabellen an, wobei als erstes die Treffer der Bedingung aufgelistet werden und dann erst die Werte auf die die Bedingung nicht zutrifft.
    • LEFT OUTER JOIN zeigt alle Werte der linken Tabelle an.
    • RIGHT OUTER JOIN zeigt alle Werte der rechten Tabelle an.
  • Die ANSI Synthax zum JOIN sieht wie folgt aus:

SELECT "Tabellenname.Spaltenname" [,...n]

FROM "Tabellenname"

(Verknüpfungstyp) JOIN "Tabellenname"

ON "Verknüpfungsbedingung

WHERE "Suchkriterium"

ORDER BY "Tabellenname.Spaltenname";

  • Gerade im Zusammenhang mit den JOINS, braucht man oft eine umfangreichere WHERE Klausel, als es mit den oben genanten Vergleichsoperatoren möglich ist. Dazu gibt es im SQL Standart die Möglichkeit des SUBSELECTS. Also ein SELECT innerhalb der WHERE Klausel. Dieses SELECT darf aber nur ein Wert und eine Spalte ergeben.

SELECT ...

FROM ....

WHERE xyz =

(

SELECT ...

FROM ...

WHERE ...

);

  • Als nächstes möchte ich noch darauf eingehen, das es in der SELECT Anweisung nicht nur möglich ist mit festen Werten zu arbeiten, sondern auch mit Funktionen (welche von MySQL vorgegeben sind). Da gibt es z. B. AVG um den Durschnitt von Werten auszurechnen, oder COUNT() zum Zählen von Ergebnissen usw. Um diese Funktionen an Bedingungen knüpfen zu können, gibt uns SQL ein weiteren Befehl an die Hand. HAVING. HAVING ist eine WHERE Klausel für die Funktionen die im SELECT vorkommen können.

SELECT xyz, COUNT(w)

FROM zz

WHERE xyz = 5

GROUP BY xyz

HAVING COUNT(w)=

(

SELECT MAX(COUNT(w))

FROM xyz

GROUP BY xyz

);

Dies war er nun, mein MySQL Überblick. Ich habe die grundlegenden Funktionen beschrieben und erklärt. Wie es die Artikelüberschrift schon sagt, ist dies nur ein Überblick und geht nicht sehr in die Tiefe. Weiter reichende Informationen finden Sie aber reichlich im Internet oder in Buchform. Wenn Sie konstruktive Kritik und Anregungen loswerden möchten, würde ich mich über ein Kommentar zum Artikel freuen.

Back…