Date post: | 06-Apr-2016 |
Category: |
Documents |
Upload: | krista-grosse |
View: | 216 times |
Download: | 0 times |
12.12.01 MySQL Übung 1
OnLine Datenbanken
MySQL Übung
Datenbank für SkriptenverkaufBeim AStA
12.12.01 MySQL Übung 2
OnLine Datenbanken Für die Durchführung der Übung wird
xampplite eingesetzt. Es beinhaltet folgende Programme: Apache (Web Server) MySQL (Datenbank Server) phpMyAdmin (Werkzeug zum Erstellen und
bearbeiten von Datenbanken)Sie können diese Software downloaden von:http://www.apachefriends.org
12.12.01 MySQL Übung 3
Aufagabenbeschreibung Der Verkauf von Skripten beim AStA
soll mit Hilfe einer Datenbank erfasst werden. Folgende Vorgänge müssen in der Datenbank abgebildet werden:
1. Erstmalige Lieferung von Skripten2. Verkauf von Skripten3. Nachlieferung von Skripten
12.12.01 MySQL Übung 4
Benötigte Tabellen
1. skripte: enthält Informationen über die Skripte (Autor, Name, Preis, Anzahl, Restbestand)
2. lieferung: enthält Informationen über die Lieferung von Skripten
3. verkauf: enthält Informationen über den Verkauf von Skripten
12.12.01 MySQL Übung 5
Tabelle skripte
Struktur der Tabelle "skripte"
12.12.01 MySQL Übung 6
Hinweise zur Tabelle Skripte
Das Feld "id" ist der Primärschlüssel in dieser Tabelle. Das Attribut "autoincrement" bedeutet, dass es von MySQL automatisch weiter gezählt wird bei jedem neuen Datensatz
Das Feld "name" hat das Attribut "Unique". Es darf nur einmal in der Tabelle vorkommen
Das Feld "autor" ist ein index-Feld. Das beschleunigt die Suche nach Autoren
12.12.01 MySQL Übung 7
Hinweise zur Tabelle Skripte
Aus der Sicht einer optimalen Auslegung der Datenbank (Normalisierung) müsste eine extra Tabelle "Autoren" angelegt werden. Aus Gründen der Vereinfachung wird jedoch darauf verzichtet.
12.12.01 MySQL Übung 8
Tabelle Skripte erstellen
12.12.01 MySQL Übung 9
Tabelle Skripte erstellenSQL-Befehl zur Erstellung der Tabelle Skripte:CREATE TABLE skripte ( id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY
KEY ,name VARCHAR( 255 ) NOT NULL ,autor VARCHAR( 100 ) NOT NULL ,preis DECIMAL( 5, 2 ) NOT NULL ,beschreibung TEXT NOT NULL ,restbestand SMALLINT NOT NULL ,INDEX (autor) ,UNIQUE (name)
) ENGINE = MYISAM CHARACTER SET latin1 COLLATE
latin1_german2_ci;
12.12.01 MySQL Übung 10
Tabelle Lieferung
Struktur der Tabelle "lieferung"
12.12.01 MySQL Übung 11
Hinweis zur Tabelle Lieferung
Das Feld "skript_id" ist ein
Sekundärschlüssel und bildet die Verknüpfung zur Tabelle "skripte"
Tabelle Lieferung erstellen
12.12.01 MySQL Übung 12
12.12.01 MySQL Übung 13
Tabelle LieferungSQL-Befehl: CREATE TABLE lieferung ( skript_id SMALLINT UNSIGNED NOT NULL ,
datum DATE NOT NULL DEFAULT '0000-00-00',anzahl SMALLINT UNSIGNED NOT NULL ,preis DECIMAL( 5, 2 ) UNSIGNED ZEROFILL NOT NULL
) ENGINE = MYISAM ;
12.12.01 MySQL Übung 14
Tabelle Verkauf
Struktur der Tabelle "verkauf"
12.12.01 MySQL Übung 15
Tabelle verkauf erstellen
12.12.01 MySQL Übung 16
Tabelle Verkauf erstellenSQL-Befehl zur Erstellung der Tabelle Verkauf
CREATE TABLE verkauf (skript_id SMALLINT UNSIGNED NOT NULL ,datum DATE NOT NULL DEFAULT '0000-00-00',anzahl SMALLINT UNSIGNED NOT NULL ,erloes DECIMAL( 7, 2 ) UNSIGNED ZEROFILL NOT NULL
) ENGINE = MYISAM ;
Beziehung zwischen Tabellen erstellen
Wählen Sie In phpMyAdmin die Registerkarte Design.
verknüpfen Sie das Feld id in der Tabelle Skripte mit dem Feld skript_id in der Tabelle "lieferung".
verknüpfen Sie das Feld id in der Tabelle Skripte mit dem Feld skript_id in der Tabelle "verkauf".
s. Abbildung auf der nächsten Folie
12.12.01 MySQL Übung 17
Beziehung zwischen Tabellen erstellen
12.12.01 MySQL Übung 18
12.12.01 MySQL Übung 19
Lieferung von SkriptenMan muss 2 Vorgänge unterscheiden1. Erstmalige Lieferung: Je ein Datensatz
wird in die Tabellen lieferung und skripte eingefügt.
2. Nachlieferung von vorhandenen Skripten: Ein Datensatz wird in die Tabelle lieferung eingefügt. In der Tabelle skripte wird nur der restbestand und evtl. der Preis geändert
12.12.01 MySQL Übung 20
Verkauf von SkriptenFolgende Aktionen werden
durchgeführt:1. Ein neuer Datensatz wird in die
Tabelle verkauf eingefügt.2. In der Tabelle skripte wird der
Restbestand um die Anzahl der verkauften Skripte vermindert
12.12.01 MySQL Übung 21
Datensatz in Tabelle Skripte einfügen
INSERT INTO skripte (name , autor , preis , beschreibung , restbestand ) VALUES ('Einführung in die Agrarinformatik', 'Doluschitz, R.', '15.00', 'Daten- und Informationsbereitstellung sowie deren Verarbeitung und Nutzung auf unterschiedlichen Einsatzebenen in der Agrarwirtschaft.', 100
)
12.12.01 MySQL Übung 22
Datensatz in Tabelle Skripte einfügen
INSERT INTO skripte (name, autor, preis, beschreibung, restbestand)
VALUES ('Einführung in das Agrarrecht', 'Turner, G.; Werner, K.', '12.00',
'Besondere Kapitel des Agrarrechts: Landwirtschaftliches Eigentum, Erbrecht mit Landw. Sondererbrecht, Gebrauchsüberlassungsverträge, Sicherung von Krediten Landwirtschaftliches Arbeits-, Sozial- und Steuerrecht, Agrarrecht in Europa.', 150)
12.12.01 MySQL Übung 23
Datensatz in Tabelle Skripte einfügen
INSERT INTO skripte (name, autor, preis, beschreibung, restbestand)
VALUES ('Einführung in Javascript', 'RRZN', '6.00',
'Programmieren von dynamischen HTML-Seiten mit JavaScript.', 100)
12.12.01 MySQL Übung 24
Datensatz in Tabelle Skripte einfügen
INSERT INTO skripte (name, autor, preis, beschreibung, restbestand)
VALUES ('Einführung in SPSS', 'RRZN', '7.50',
'Das Statistikpaket SPSS für Anfänger.', 80)
12.12.01 MySQL Übung 25
Datensatz in Tabelle Skripte einfügen
INSERT INTO skripte( name, autor, preis, beschreibung, restbestand ) VALUES ('Word 2002 für Fortgeschrittene', 'RRZN', '6.00', 'Dokument- und Formatvorlagen, Verzeichnisse, Grafiken einfügen und bearbeiten,..', 100
)
12.12.01 MySQL Übung 26
Datensätze in Tabelle skripte
12.12.01 MySQL Übung 27
Datensätze in Tabelle lieferung einfügen
INSERT INTO lieferung (skript_id, datum, anzahl, preis)
VALUES ('1', '2006-06-12', 100, '15'),('2', '2006-06-12', 150, '12'),('3', '2006-06-12', 100, '6'),('4', '2006-06-12', 80, '7.50'),('5', '2006-06-12', 100, '6')
Tabelle lieferung
12.12.01 MySQL Übung 28
12.12.01 MySQL Übung 29
Datensatz in Tabelle verkauf einfügen
INSERT INTO verkauf (skript_id, datum, anzahl, erloes)
VALUES ('2', '2006-06-13', '10', '120'); UPDATE skripte SET restbestand = restbestand - 10 where id = 2
12.12.01 MySQL Übung 30
Datensatz in Tabelle verkauf einfügen
INSERT INTO verkauf (skript_id, datum, anzahl, erloes) VALUES ('1', '2006-06-13', '4', '60');
UPDATE skripte SET restbestand = restbestand - 4 where id = 1
12.12.01 MySQL Übung 31
Datensatz in Tabelle verkauf einfügen
INSERT INTO verkauf (skript_id, datum, anzahl, erloes)
VALUES ('3', '2005-12-13', '20', '120');
UPDATE skripte SET restbestand = restbestand - 20 where id = 3
12.12.01 MySQL Übung 32
Datensatz in Tabelle verkauf einfügen
INSERT INTO verkauf (skript_id, datum, anzahl, erloes)
VALUES ('3', '2006-12-13', '10', '60');
UPDATE skripte SET restbestand = restbestand - 10 where id = 3
12.12.01 MySQL Übung 33
Auswahl von Datensätzen
Beispiele für die SELECT-Anweisung:Ausgabe aller Skripte:
SELECT *FROM skripteORDER BY name
12.12.01 MySQL Übung 34
Auswahl von Datensätzen
Ausgabe von Verkaufszahlen und Erlös:(Skript-ID, Name, Anzahl und Erloes)
SELECT skripte.id, skripte.name, verkauf.anzahl,
verkauf.erloesFROM skripte, verkauf WHERE skripte.id = verkauf.skript_idORDER BY skripte.name
12.12.01 MySQL Übung 35
Auswahl von Datensätzen
Alternative Formulierung:(Verknüpfung der Tabellen mit JOIN)SELECT skripte.id, skripte.name,
verkauf.anzahl,verkauf.erloesFROM skripte JOIN verkauf WHERE skripte.id = verkauf.skript_idORDER BY skripte.id
12.12.01 MySQL Übung 36
Auswahl von Datensätzen
Weitere Alternative Formulierung:SELECT s.id, s.name, v.anzahl, v.erloesFROM skripte as s, verkauf as vWHERE s.id = v.skript_idORDER BY s.id Anmerkung: in diesem Beispiel werden Aliase
(Kürzel)für die Tabellennamen benutzt
Ergebnis der Abfrage
Ausgabe von Verkaufszahlen und Erlös:
12.12.01 MySQL Übung 37
12.12.01 MySQL Übung 38
Auswahl von DatensätzenAusgabe der verkauften Skripte in 2005select s.id, s.name, s.autor, v.anzahl,v.datum, v.erloes from skripte as s, verkauf as vwhere s.id = v.skript_id AND v.datum LIKE '2005%'ORDER BY s.idErgebnis der Abfrage:
12.12.01 MySQL Übung 39
Auswahl von Datensätzen
Ausgabe der Summe von Verkaufszahlen und Erlös:
SELECT s.id, s.name, SUM(v.anzahl), SUM(v.erloes)
FROM skripte AS s, verkauf AS v WHERE s.id = v.skript_id GROUP BY s.id
12.12.01 MySQL Übung 40
Auswahl von Datensätzen
Ergebnis der Abfrage:Verkaufszahlen und Erlös werden pro Skript zusammengefasst
12.12.01 MySQL Übung 41
Auswahl von Datensätzen
Übersicht über verkaufte Skripte im Jahr2006: SELECT s.id, s.name, s.autor, v.datum,
SUM( v.anzahl ) , SUM( v.erloes ) FROM skripte AS s, verkauf AS v WHERE s.id = v.skript_id AND v.datum LIKE '2006%' GROUP BY s.id
12.12.01 MySQL Übung 42
Auswahl von Datensätzen
Ergebnis der Abfrage:.
12.12.01 MySQL Übung 43
Auswahl von Datensätzen
Verknüpfung der Tabellen mit"LEFT OUTER JOIN":SELECT s.id, s.name, v.anzahl, v.erloes FROM skripte as s LEFT OUTER JOIN
verkauf as vON s.id = v.skript_idORDER BY s.id
12.12.01 MySQL Übung 44
Ergebnis der AbfrageHinweis: Ergebnis der Abfrage enthält auch
SkripteFür die es keinen Eintrag in derTabelle verkauf gibt (s. Zeilen 4+5)
12.12.01 MySQL Übung 45
Auswahl von Datensätzen
Ausgabe von Skripten, für die kein Eintrag in der Verkaufstabelle existiert:
SELECT s.id, s.name, s.autor, v.skript_id FROM skripte as s LEFT OUTER JOIN verkauf as vON s.id = v.skript_id WHERE v.skript_id is NULLORDER BY s.id
12.12.01 MySQL Übung 46
Ergebnis der Abfrage Ergebnis enthält nur Datensätze
von nicht verkauften Skripten
12.12.01 MySQL Übung 47
Auswahl von Datensätzen
Ausgabe von Verkaufszahlen:SELECT s.id, s.name, v.skript_id, v.anzahl,
v.erloes FROM verkauf as v LEFT OUTER JOIN skripte
as sON s.id = v.skript_idORDER BY s.id
12.12.01 MySQL Übung 48
Ergebnis der Abfrage Ergebnis der Abfrage enthält auch Einträge
in der Tabelle verkauf Für die es keinen passenden Eintrag in der Tabelle skripte gibt
Konsistenzprobleme in der DB. Evtl. Fehler bei der Eingabe der skript_id (s. Abb. Auf der nächsten Folie)
12.12.01 MySQL Übung 49
Ergebnis der Abfrage
12.12.01 MySQL Übung 50
Auswahl von DatensätzenAusgabe von Datensätzen aus
Skripten-Tabelle, mit Restbestand < 100SELECT id, name, autor, restbestandFROM skripteWHERE restbestand < 100ORDER BY name
12.12.01 MySQL Übung 51
Ergebnis der Abfrage
12.12.01 MySQL Übung 52
Nachlieferung von SkriptenINSERT INTO lieferung (skript_id, datum,
anzahl, preis) VALUES ('1', '2006-06-13', '50', '15.50'); UPDATE skripte SET restbestand = restbestand + 50,Preis = 15.50where id = 1Hinweis: Preis hat sich um 50 Cent erhöht!
12.12.01 MySQL Übung 53
Auswahl von DatensätzenAusgabe aller Liefervorgänge:SELECT s.id, s.name, s.autor,
l.datum, l.anzahl, l.preis, l.anzahl*l.preis
FROM skripte as s, lieferung as lWHERE s.id = l.skript_idORDER BY s.id
12.12.01 MySQL Übung 54
Auswahl von Datensätzen
Ergebnis der Abfrage:
12.12.01 MySQL Übung 55
Auswahl von DatensätzenAusgabe aller Liefervorgänge mit
Gesamtlieferkosten pro Skript:SELECT s.id, s.name, s.autor, l.datum,
sum(l.anzahl), sum(l.anzahl*l.preis)FROM skripte as s, lieferung as lWHERE s.id = l.skript_idGROUP BY s.idORDER BY s.id
12.12.01 MySQL Übung 56
Auswahl von DatensätzenErgebnis der Abfrage: