Einstieg in relationale Datenbankenmit MySQL

Dr. Kerstin Puschke

September 2009

1

Lizenz

LizenzDieser Text steht unter einer Creative Commons Attribution-Share Alike 3.0 GermanyLizenz, siehe http://creativecommons.org/licenses/by-sa/3.0/de/

Inhaltsverzeichnis

I Einführung 4

1 Grundbegriffe 4

2 Architektur 4

3 MySQL 5

II Datenbankabfragen 6

4 Überblick verschaffen 6

5 Einfache Abfragen 6

6 Ergebnisse einschränken 7

III Datenbankdesign 8

7 Einführung in die theoretischen Grundlagen 87.1 Datenbankmodell . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87.2 Datenbankschema . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

8 Datenbankdesign in der Praxis 108.1 Ablauf . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108.2 Konzeptionelles Design: Entity-Relationship-Model . . . . . . . . . . 108.3 Logisches Design: Relationales Schema . . . . . . . . . . . . . . . . 128.4 Physikalisches Design: Storage Engine . . . . . . . . . . . . . . . . . 12

9 Qualitätssicherung 129.1 Normalform . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

10 Datenbank definieren 1310.1 Exkurs: Die MySQL-Dokumentation . . . . . . . . . . . . . . . . . . 1310.2 Tabellen anlegen . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

IV Datenbanksprachen 15

11 Allgemein 15

12 SQL 15

V Datenbanken erzeugen und manipulieren 16

13 Daten eintragen, ändern, löschen 16

2

14 Tabellen verknüpfen 16

VI Vor- und Nachteile von Datenbanken 18

15 Datenbanken vs. Dateien 1815.1 Vorteile von Datenbanksystemen . . . . . . . . . . . . . . . . . . . . 1815.2 Nachteile von Datenbanksystemen . . . . . . . . . . . . . . . . . . . 19

16 Relationale Datenbanksysteme 19

VII Datenbank manipulieren - Weitere Möglichkeiten 19

17 Aggregierte Daten 19

3

Teil I

Einführung1 GrundbegriffeDatenbanksystem Datenbank und Datenbankmanagementsystem

• Datenbanksystem dient der Speicherung und Verwaltung von Daten

– besteht aus Datenbanken und Datenbankmanagementsystem (DBMS)

• Datenbank: strukturiert abgelegte Daten

– zu verwaltende Daten, z.B. Adressen

– Datenbankschema: Metadaten zur Datenbankdefinition

• Datenbankmanagementsystem: Software zur Verwaltung der Daten

– Datenbank definieren, erzeugen, manipulieren

2 ArchitekturClient und Server

• Datenbankserver: Software, die Datenbankdienste (ein Datenbanksystem) an-bietet

– auch der Rechner, auf dem dieser Dienst läuft

• Anwendung oder Client greift auf den Datenbankserver zu

Architektur

Mainframe

• Großrechner (Zugriff über Terminals)

Zwei-Schichten-Architektur

• Datenbankserver

• Anwendung, Client Anwendungslogik und Darstellung/Benutzeroberfläche

Drei-Schichten-Architektur

• Datenbankserver

• Anwendung (Anwendungslogik) Webserver oder Anwendungsserver (Darstel-lung)

• Browser oder Client des Anwendugnsservers

4

3 MySQLMySQL

• DBMS

• sehr verbreitet in Webanwendungen

• auch eingebettet in anderen Anwendungen

• schwedische Firma MySQL AB, jetzt Sun Microsystems, Übernahme durch Oracle

• kostenlose Community-Version, freie Software (GPL)

• kostenpflichtige Enterprise-Version

MySQL Clients und Anwendungen

• Kommandozeilenclient mysql

• Webinterface phpmyadmin

• MySQL GUI Tools

• . . .

• Webanwendungen

– MediaWiki

– WordPress

– . . .

• Anwendungen mit eingebetteter Datenbank

– Zimbra

– . . .

5

Teil II

Datenbankabfragen4 Überblick verschaffenAufgaben

Überblick verschaffenSHOW DATABASES, USE datenbank, SHOW TABLES, DESCRIBE tabelle

Erste Befehle

• SHOW DATABASES zeigt alle Datenbanken

• USE datenbank wählt Datenbank datenbank aus

• SHOW TABLES zeigt Tabellen der gewählten Datenbank

• DESCRIBE tabelle zeigt Spalten der Tabelle an

5 Einfache AbfragenAufgaben

Einfache AbfragenSELECT, ORDER BY, LIMIT, NULL

Abfragen I

SELECT

• verlangt Angabe von Spalte(n) und Tabellen, die auszugeben sind

• * bedeutet alle Spalten der Tabelle

• SELECT Name,Vorname FROM KundInnen

• SELECT * FROM KundInnen

ORDER BY

• sortiert Ausgabe

• SELECT Name,Vorname FROM KundInnen ORDER BY Name

6

Abfragen II

LIMIT

• beschränkt die Zahl der ausgegeben Einträge

• häufig mit ORDER BY kombiniert

• SELECT Name,Vorname FROM KundInnen LIMIT 5

NULL

• Kein Eintrag (z.B. für Kundin Müller keine Telefonnummer vermerkt): NULL

NULL

• NULL ist etwas anderes als die Zahl Null oder ein leerer String!

6 Ergebnisse einschränkenAufgaben

Ergebnisse einschränkenWHERE, AND, OR, UNION,DISTINCT

Ergebnisse einschränken I

WHERE

• schränkt Ergebnisse ein

• SELECT Name FROM KundInnen WHERE Stadt = ’Salzburg’

• SELECT Name FROM KundInnen WHERE Vorname IS NULL

Ergebnisse einschränken II

AND und OR

• verknüpfen mehrere Bedingungen

• SELECT Name FROM KundInnen WHERE Stadt = ’Salzburg’ ANDVorname=’Clara’

Abfragen einschränken III

UNION

• vereinigt Ergebnisse

• (SELECT Name FROM KundInnen WHERE Stadt =’Salzburg’) UNION(SELECT Name FROM KundInnen WHERE Stadt = ’Wien’)

7

Ergebnisse einschränken IV

SELECT DISTINCT

• gibt nur unterschiedliche Ergebnisse aus

• SELECT DISTINCT Stadt FROM KundInnen

Teil III

Datenbankdesign7 Einführung in die theoretischen Grundlagen

7.1 DatenbankmodellDatenbankmodell

• Konzept zur Beschreibung der Struktur einer Datenbank

• konzeptionelles Modell: formale Beschreibung eines Teils der Realität

Konzeptionelles Modell

• formale Beschreibung eines Ausschnittes der Realität

• verbreitet: Entity-Relationship-Model (Gegenstand-Beziehungs-Modell)

Datenbankmodell

• Konzept zur Beschreibung der Struktur einer Datenbank

• konzeptionelles Modell: formale Beschreibung eines Teils der Realität

• logisches Modell: Beschreibung der Implementierung

Logisches Modell

• Beschreibung der Implementierung

• verbreitete Modelle:

– netzwerkartig

– hierarchisch

– relational

– objektorientiert

– objektrelational

– ...

8

Relationales Modell

• E.F. Codd (1970)

• hohe Verbreitung seit den 80er Jahren, z.B.

– MySQL

– PostgreSQL

– Oracle

– . . .

• Datenbank als Sammlung von Relationen (Tabellen)

• Relation: Menge von Tupeln (Datensätzen, Zeilen)

– unsortiert

– keine Duplikate

• Schema einer Relation (Tabellenstruktur) besteht aus Attributen (Spalten)

Datenbankmodell

• Konzept zur Beschreibung der Struktur einer Datenbank

• konzeptionelles Modell: formale Beschreibung eines Teils der Realität

• logisches Modell: Beschreibung der Implementierung

• physikalisches Modell: Datenspeicherung, Zugriffspfade

Physikalisches Modell

• Wie werden die Daten gespeichert?

• Auf Platte geschrieben oder nur im Speicher gehalten?

• Wie auf Dateien aufgeteilt? Was für Dateien?

• Indizierung

7.2 DatenbankschemaDatenbankschema

Datenbankschema

• Beschreibung der Datenbankstruktur

• gespeichert als Metadaten in der Datenbank

• folgt gewählten Datenmodellen

– konzeptionelles SchemaER-Modell: ER-Diagramm

– logisches Schemarelationales Modell: Tabellen, Spalten, . . .

– physikalische Schema: Storage Engine und Indizierung

9

Datenbankzustand

Datenbankzustand

• Daten zu einem bestimmten Zeitpunkt

8 Datenbankdesign in der Praxis

8.1 AblaufDatenbankdesign Relationale Datenbanken entwerfen

• Anforderungsanalyse

– Geschäftsprozesse analysieren,. . .

• Konzeptionelles Design: konzeptionelles Schema entwerfen

– Anforderungen in formale Struktur überführen– Entitiy-Relationship-Diagramm entwickeln

• Logisches Design: logisches Schema entwerfen

– Relationales Datenbankschema entwerfen– ER-Diagramm in Tabellen, Spalten usw. übersetzenmanuell oder software-

gestützt

• Physikalisches Design: Physikalische Schema entwerfen

– Storage Engine wählen– Indizierung festlegen

8.2 Konzeptionelles Design: Entity-Relationship-ModelEntity-Relationship-Model

• Entitiy-Relationship-Model, ER-Modell, ERM, Gegenstand-Beziehungs-Modell:konzeptionelles Datenmodell zur formalen Beschreibung eines Ausschnitts derrealen Welt

• P.P. Chen (1976)

Entity-Relationship-Model Elemente eines Schemas

• Entitäten: Gegenstände, Personen,. . .

• Beziehungen: Verknüpfungen von Entitäten

• Attribute: Eigenschaften von Entitäten oder Beziehungen

• Attribute haben Werte (nicht modelliert)

• Primärschlüssel: Attribut (oder Kombination mehrerer Attribute), welches eineEntität oder Relation eindeutig kennzeichnet

• Kardinalität von Beziehungen: (mögliche) Anzahl beteiligter Entitäten

10

ER-Diagramm

• ER-Diagramm: grafische Darstellung eines ER-modellierten Schemas

• verschiedene Notationen vorhanden

Erstellen von ER-Diagrammen Softwaretools

• Dia

• Microsoft Visio

• MySQL Workbench

• . . .

Elemente eines ER-Diagramms

• Entität: Rechteck

• Attribut: Kreis

• Beziehung: Raute

• Primärschlüssel: unterstrichen

• Kardinaliäten:

– Chen-Notation: 1 oder N

– Min-Max-Notation: Paare mit Einträgen 0,1 oder N, z.B. (1,N)

ER-Diagramm

Aufgaben

DatenbankentwurfAnforderungsanalyse, ER-Diagramm

11

8.3 Logisches Design: Relationales SchemaVom ER-Diagramm zum Relationalen Schema

• ein Primärschlüssel für jede Tabelle

• eine Tabelle für jede Entität

• eine Tabelle für jede n-m-Beziehung

• Attribute sind Spalten der jeweiligen Tabelle

• n-eins-Beziehung als Fremdschlüssel (in der Tabelle der Entität auf der n-Seite)

Aufgaben

DatenbankentwurfRelationales Schema, ER-Diagramm in Tabellen überführen

8.4 Physikalisches Design: Storage EngineStorage Engine Physikalisches Schema

• zuständig für Datenspeicherung

• setzt physikalisches Modell um

• MySQL bringt mehrere Storage Engines mit

• weitere Engines können eingebunden werden

• Standard Engine in MySQL: MyISAM Index Sequential Access Method

• zunehmend verbreitet: InnoDB

• wir verwenden die Standardeinstellungen

9 Qualitätssicherung

9.1 NormalformAufgabe

NormalformNegativbeispiel diskutieren

Normalform

NormalformJedes Attribut, das kein Schlüssel ist, hängt direkt vom Schlüssel ab, vom gesamtenSchlüssel, und zwar nur vom Schlüssel

12

Daten in Normalform

• keine Anomalien

• Konsistenz der Daten

• keine Redundanzen

• einfache Wartung

• effizientes Suchen und Bearbeiten

Normalisierung

• Normalisierung = in Normalform bringen

• verschiedene Algorithmen

• ER-Diagramme und daraus abgeleitete relationale Schemata:

– häufig schon in Normalform

– ggf. “nachbessern”

Normalform

HinweisDie Darstellung zum Thema Normalisierung/Normalform ist sehr vereinfacht. Wermehr wissen möchte, findet links im Wiki.

10 Datenbank definieren

10.1 Exkurs: Die MySQL-DokumentationUmgang mit der MySQL-Doku

• eckige Klammern umschliessen optionale Klauseln

• | bedeutet oder nur eine der genannten Alternativen kann/muss benutzt werden

• geschweifte Klammern: eine der Alternativen muss benutzt werden

• eckige Klammern: eine oder keine der Alternativen kann benutzt werden

• Beispiel 1: DROP TABLE [IF EXISTS] tbl_name

• Beispiel 2: {DESCRIBE | DESC} tbl_name [col_name | wild]}

13

10.2 Tabellen anlegenDatenbank definieren Tabellen anlegen

• Name der Tabelle

• Storage Engine

• Beschreibung der Spalten

– Name der Spalte

– Datentyp (INT Integer, VARCHAR(20) String mit max. 20 Zeichen)

– Eintrag obligatorisch? (NULL/NOT NULL)

– Eintrag eindeutig? (UNIQUE)

– Schlüssel? (PRIMARY KEY, impliziert NOT NULL und UNIQUE)

– Default-Wert? (DEFAULT)

– Extras? (z.B. AUTO_INCREMENT; AUTO_INCREMENT ist kein Standard-SQL!)

Tabellen verwalten I Tabelle anlegen

CREATE TABLECREATE TABLE KundInnen (KundNr INT PRIMARY KEY AUTO_INCREMENT,Name VARCHAR(50) NOT NULL), Vorname VARCHAR(25)

Tabellen verwalten II Informationen über Tabellen

DESCRIBE und SHOW CREATE TABLEDESCRIBE KundInnen SHOW CREATE TABLE KundInnen

Tabellen verwalten III Tabellenstruktur ändern

ALTER TABLEALTER TABLE KundInnen DROP COLUMN Vorname . . .

ALTER TABLEVorsicht! Viele Stolperfallen!

Tabellen verwalten IV Tabellen löschen

DROP TABLEDROP TABLE KundInnen

14

Aufgaben

Datenbank definierenCREATE TABLE, DROP TABLE, SHOW CREATE TABLE

Teil IV

Datenbanksprachen11 AllgemeinDatenbanksprachen

• Data Definition Language (DDL) Definition des Datenbankschemas

• Data Manipulation Language (DML) Daten abfragen und verändern

• Data Control Language (DCL) Zugriffsrechte

• Data Administration Language (DAL) finetuning, Zugriffspfad

12 SQLSQL

• Structured Query Language

• Vorgänger: SEQUEL

• Sprache relationaler DBMS

• beinhaltet DDL, DML, DCL, DAL

• deklarative Sprache beschreibt, was zu tun ist, nicht wie

• erweitert um prozedurale Funktionalitäten (Ablaufsteuerung)

• Konvention: Befehle und Co. in Großbuchstaben schreiben SELECT * FROMkurse

SQL Implementierungen

• sehr verbreitet, z.B. MySQL, PostgreSQL, Oracle u.v.a.

• fast alle Systeme

– implementieren nicht den kompletten Standard

– implementieren eigene Erweiterungen

• daher: Kompatibilität der Systeme eingeschränkt

15

Teil V

Datenbanken erzeugen undmanipulieren13 Daten eintragen, ändern, löschenAufgabenDatenbank erzeugen und manipulierenINSERT, UPDATE, DELETE

Datenbank erzeugen und manipulieren Daten eintragenINSERTINSERT INTO KundInnen (Name,Vorname) VALUES (’Example’,’Erna’)

Datenbank erzeugen und manipulieren Daten ändernUPDATEUPDATE KundInnen SET TelefonNr=’1234567’ WHERE KundNr=’666’

Datenbank erzeugen und manipulieren Daten entfernenDELETEDELETE FROM KundInnen WHERE Stadt=’Wien’

14 Tabellen verknüpfenDeskriptoren für Objekte in SQL

• Objekt: Tabelle, Spalte,. . .

– kein Objekt im Sinne von OO-Programmierung!

• Deskriptor: identifiziert das Objekt (z.B. Spaltenname)

• unterschiedlich für die verschiedenen Datenbanksystem

Deskriptoren für Objekte in MySQLVollständig

• Tabelle: Datenbank.Tabelle

• Spalte: Datenbank.Tabelle.Spalte

Abkürzung

• Angabe der Datenbank kann entfallen, wenn die mit USE voreingestellte benutztwerden soll

• Angabe der Tabelle kann zusätzlich entfallen, wenn Spaltenname innerhalb derverwendeten Tabellen eindeutig

16

Tabellen verknüpfen JOIN

• Abfragen über mehrere Tabellen

• Tabellen verknüpfen (JOIN)

• verknüpfe Zeilen, ggf. gemäß einer Bedingung (an gemeinsame Attribute)

• über verknüpften Tabellen SELECT durchführen

Tabellen verknüpfen BeispielKundInnen

KundNr Name Vorname1 Example Erna2 Kundin Karla

[0.3cm]

BestellungenKundNr ArtikelNr2 51 42 6

Tabellen verknüpfen Cross Join, Cartesisches ProduktKundNr Name Vorname KundNr ArtikelNr1 Example Erna 1 41 Example Erna 2 51 Example Erna 2 62 Kundin Karla 2 62 Kundin Karla 2 52 Kundin Karla 2 6

Tabellen verknüpfen Theta JoinVerknüpfungsbedingung: KundInnen.KundNr = Bestellungen.KundNr[0.3cm]

KundNr Name Vorname KundNr ArtikelNr1 Example Erna 1 42 Kundin Karla 2 52 Kundin Karla 2 6

Aufgaben

Tabellen verknüpfenJOIN

JOIN-Syntax INNER JOIN

• Zeilen ohne Entsprechung in der anderen Tabelle entfallen

• SELECT Name, Vorname, ArtikelNr FROM KundInnen INNER JOINBestellungen ON KundInnen.KundNr = Bestellungen.KundNr

17

JOIN-Syntax OUTER JOIN

• Zeilen ohne Entsprechung in der anderen Tabelle werden mit NULL-Einträgenergänzt

• LEFT JOIN: alle Zeilen der linken Tabelle, dazu nur Zeilen der rechten Tabelle,die eine Entsprechung in der linken Tabelle haben

• SELECT Name, Vorname, ArtikelNr FROM KundInnen INNER JOINBestellungen ON KundInnen.KundNr = Bestellungen.KundNrzeigt auch KundInnen, die nichts bestellt haben

Teil VI

Vor- und Nachteile von Datenbanken15 Datenbanken vs. Dateien

15.1 Vorteile von DatenbanksystemenVorteile von Datenbanksystemen

• Daten und Datenstruktur enthalten Datenstruktur als Metadaten im DBS gespei-chert

• konkurrierende, gleichzeitige Zugriffe handhaben

• Fehlertoleranz

• Sicherheit (Zugriffsschutz) und Flexibilität durch Trennung von Anwendung undDaten

• große Datenmengen beherrschbar

Trennung von Anwendung und Daten Externe Sichten

• Trennung von physikalischer Ebene, logischer Ebene und externer Ebene (Sicht)

• Sicht: user-abhängige Zugriffsmöglichkeit und Präsentation der Daten Beispiel:

– ZugriffsschutzBeispiel: Prüfungsbüro hat Zugriff auf andere Daten als Stu-dierendenverwaltung

– angepaßte Präsentation der Daten

• kleinere Änderung auf einer Ebene beeinflußt andere Ebenen nicht

18

15.2 Nachteile von DatenbanksystemenNachteile von Datenbanksystemen

• nicht alle Datenbanksysteme bieten die genannten Vorteile

• Aufwand nicht immer gerechtfertigt

• insbesondere unterschiedliche Sichten aufwändig

• Beherrschbarkeit großer Datenmengen verleitet zu ausufernder Datensammlungoder Verknüpfung von Daten

– Mißachtung des Datenschutzes, insbesondere der gebotenen Datenspar-samkeit drängt sich u.U. auf

16 Relationale DatenbanksystemeRelationale Datenbanksysteme

• Daten stark strukturiert nachträgliche Änderung des Schemas problematisch

– gut geeignet für stark strukturierte Daten z.B. Adressdaten

– weniger geeignet für flexible Strukturenz.B. user generated content

• nicht für verteilte Systeme optimiert

• hohe Verbreitung auch in diesen Bereichenlange Zeit quasi alternativlos

Teil VII

Datenbank manipulieren - WeitereMöglichkeiten17 Aggregierte DatenAufgaben

Aggregierte DatenCOUNT, SUM, MAX,... GROUP BY, HAVING

Aggregierte Daten

COUNT

• gibt Anzahl der Datensätze aus

• SELECT COUNT(*) FROM KundInnen WHERE Stadt=’WIEN’ liefertAnzahl der KundInnen aus Wien

SUM,AVG,MAX,MIN

19

• Summe, Durchschnitt, Maximum, Minimum

• SELECT SUM(Rechnungsbetrag) FROM Rechnungen

GROUP BY und HAVING

GROUP BY

• läßt Zeilen mit gemeinsamem Attributwert aggregieren

• SELECT Name, COUNT(*) FROM KundInnen gibt aus, wieviele KundIn-nen jeweils den gleichen Nachnamen haben

HAVING

• schränkt Ergebnisse ein

• SELECT Name, COUNT(*) FROM KundInnen HAVING COUNT(*) >1 zeigt nur Namen, die mehrfach vorkommen

GROUP BY und HAVING

HAVING vs. WHERE

• WHERE stellt Bedingungen an die Spalten, die in die Berechnung eingehen

• HAVING stellt Bedingungen an die Ergebnisse der Berechnung

SpaltenKeine Spalten in SELECT und HAVING eintragen, die nicht in GROUP BY stehen!

20