SQL-Tutorial

SQL Tutorial
SQL - Tutorial
SS 06
Hubert Baumgartner
INSO - Industrial Software
Institut für Rechnergestützte Automation | Fakultät für Informatik | Technische Universität Wien
Inhalt des Tutorials
1
Grundlagen
2
Das Relationenmodell
3
Die Datenbanksprache SQL
4
Übungsbeispiel
Sof tware Engineering 2005W | SQL-Tutorial
2
Gründe für die Verwendung einer Datenbank
 Redundanz
 Konsistenz
 Sicherheit
 Mehrbenutzerfähig
 Integritätsüberprüfung
Sof tware Engineering 2005W | SQL-Tutorial
3
Inhalt des Tutorials
1
Grundlagen
2
Das Relationenmodell
3
Die Datenbanksprache SQL
4
Übungsbeispiel
Sof tware Engineering 2005W | SQL-Tutorial
4
Wichtige Begriffe
 Entität (Entity)
 Attribut
 Relation
 Schlüssel
 Integritätsbedingungen
 Relationales Datenbanksystem
 Index
Sof tware Engineering 2005W | SQL-Tutorial
5
Grundlegende EER-Konstrukte
 Entitäten
 Attribute
Sof tware Engineering 2005W | SQL-Tutorial
6
Grundlegende EER-Konstrukte
 Relationen
 Weak Entity
Sof tware Engineering 2005W | SQL-Tutorial
7
Komplexität einer Beziehung
 1:1-Relation
 1:n-Relation
 m:n-Relation
Sof tware Engineering 2005W | SQL-Tutorial
8
Generalisierung
Sof tware Engineering 2005W | SQL-Tutorial
9
Existenz einer Entity in einer Beziehung
 Zwingende (obligatorische) Relation
 Optionale Relation
Sof tware Engineering 2005W | SQL-Tutorial
10
Beispiel-EER
Sof tware Engineering 2005W | SQL-Tutorial
11
Inhalt des Tutorials
1
Grundlagen
2
Das Relationenmodell
3
Die Datenbanksprache SQL
4
Übungsbeispiel
Sof tware Engineering 2005W | SQL-Tutorial
12
Die Datenbanksprache SQL
 Allgemeines
 Datentypen
▪
▪
▪
▪
▪
▪
▪
CHAR(n)
VARCHAR(n)
INTEGER
SMALLINT
FLOAT
DOUBLE
DATE
Sof tware Engineering 2005W | SQL-Tutorial
13
Datendefinition
 Erzeugen von Tabellen
CREATE TABLE tabellenname (
attribut1 typ {Optionen} {, attributN typ {Optionen} }
{ , Zusatzoptionen } );
 Beispiel:
CREATE TABLE Person
(Vorname
VARCHAR(20) NOT NULL,
Nachname
VARCHAR(30) NOT NULL,
Adresse
VARCHAR(60) NOT NULL,
Geburtsdatum DATE
NOT NULL,
Telefonnummer VARCHAR(30),
Geschlecht
INTEGER
NOT NULL,
PRIMARY KEY (Vorname, Nachname, Adresse, Geburtsdatum),
CHECK((Geschlecht >= 1) AND (Geschlecht <= 2)));
Sof tware Engineering 2005W | SQL-Tutorial
14
Datendefinition
 Ändern von Tabellen
ALTER TABLE tabellenname
ADD
attributname1 typ1 |
DROP
attributname1 |
CHANGE
attributname1_alt attributname1_neu typ1_neu |
MODIFY
attributname1 typ1_neu
{ , ADD
attributnameN typN |
DROP
attributnameN |
CHANGE
attributnameN_alt attributnameN_neu typN_neu |
MODIFY
attributnameN typN_neu };
 Beispiel:
ALTER TABLE Person
ADD Email VARCHAR(20),
MODIFY Telefonnummer VARCHAR(40) NOT NULL,
DROP Geschlecht;
Sof tware Engineering 2005W | SQL-Tutorial
15
Datendefinition
 Löschen von Tabellen
DROP TABLE tabellenname1 {, tabellennameN};
 Beispiel:
DROP TABLE Person;
Sof tware Engineering 2005W | SQL-Tutorial
16
Datenmanipulation
 Einfügen von Daten
INSERT INTO tabellenname
[(attribut1, attribut2, .... attributN)]
VALUES (wert1, wert2, .... wertN);
 Beispiel:
INSERT INTO Person
(Vorname, Nachname, Adresse, Geburtsdatum, Telefonnummer,
Geschlecht)
VALUES
(“Hans“,“Meier“,“Waldweg 2“,“10.09.1970“,“01/7654321“,1);
Sof tware Engineering 2005W | SQL-Tutorial
17
Datenmanipulation
 Ändern von Daten
UPDATE tabellenname
SET attributname = value {, attributname = value}
[ WHERE Bedingung ];
 Beispiel:
UPDATE Person SET
Vorname = “Hans“, Nachname = “Huber“
WHERE
Adresse = “Waldweg 2“;
Sof tware Engineering 2005W | SQL-Tutorial
18
Datenmanipulation
 Löschen von Daten
DELETE FROM tabellenname
[ WHERE Bedingung ];
 Beispiel:
DELETE FROM Person
WHERE
Vorname = “Hans” AND Nachname = “Huber”;
Sof tware Engineering 2005W | SQL-Tutorial
19
Datenabfragen
 Grundkonstruktion einer Abfrage
SELECT attributname {, attributname}
FROM tabellenname { , tabellenname }
[ WHERE Bedingung ]
[ ORDER BY [ ASC | DESC ] attributname ];
 Beispiele:
SELECT * FROM Person;
SELECT Vorname, Nachname FROM Person;
SELECT * FROM Person ORDER BY Vorname;
Sof tware Engineering 2005W | SQL-Tutorial
20
Datenabfragen
 Die Selektion
▪ Realisiert durch die WHERE-Klausel
 Beispiel:
SELECT * FROM Person
WHERE Geschlecht = 1 AND Geburtsdatum>“01.01.1960“;
Sof tware Engineering 2005W | SQL-Tutorial
21
Datenabfragen
 Die Projektion
▪ Realisiert durch Angabe von Attributen
 Beispiel:
SELECT Vorname, Nachname, Geburtsdatum FROM Person;
Sof tware Engineering 2005W | SQL-Tutorial
22
Datenabfragen
 Aggregatfunktionen
▪
▪
▪
▪
▪
COUNT (attributname)
SUM (attributname)
AVG (attributname)
MAX (attributname)
MIN (attributname)
 Beispiel:
SELECT COUNT(*) FROM Spieler
WHERE Verein_Name = „Tennis 2005“;
Sof tware Engineering 2005W | SQL-Tutorial
23
Datenabfragen
 Gruppierung
SELECT [ ALL | DISTINCT ] [Alias.] attributname
{, [Alias.] attributname }
FROM tabellenname [Alias] {, tabellenname [Alias] }
[ WHERE Bedingung ]
[
GROUP BY [Alias.] attributname {, [Alias.] attributname }
[ HAVING Bedingung]
]
[ ORDER BY [ASC | DESC] attributname];
 Beispiel:
SELECT Spieler_SpielerNr, COUNT(Spieler_SpielerNr)
FROM Teilnahme
GROUP BY Spieler_SpielerNr;
Sof tware Engineering 2005W | SQL-Tutorial
24
Datenabfragen
 Mengenoperationen
SQL-Query [ UNION SQL-Query ];
 Beispiel:
(SELECT Adresse FROM Person)
UNION
(SELECT Adresse FROM Verein);
Sof tware Engineering 2005W | SQL-Tutorial
25
Datenabfragen
 Teilabfragen
Innerhalb der WHERE-Klausel kann an jeder Stelle, an der
ein Wert erwartet wird, dieser auch durch eine SQL-Abfrage
berechnet werden.
 Vergleichsoperatoren für Teilabfragen:
EXISTS, ALL, ANY, IN, NOT IN
 Beispiel:
SELECT * FROM Spieler WHERE Preisgeld >
(SELECT Preisgeld FROM Spieler
WHERE Person_Vorname = „Christian“ AND
Person_Nachname = „Lang“);
Sof tware Engineering 2005W | SQL-Tutorial
26
Datenabfragen
 Der Verbund (Join)
Wenn benötigte Daten in getrennten Tabellen liegen können diese
bei Bedarf über ihre Schlüssel in Zusammenhang gebracht werden.
 Beispiel:
SELECT s.SpielerNr, t.Bezeichnung
FROM Spieler s,Turnier t, Teilnahme tn
WHERE s.SpielerNr
= tn.Spieler_SpielerNr AND
t.Bezeichnung = tn.Turnier_Bezeichnung;
entspricht
SELECT s.SpielerNr, t.Bezeichnung
FROM (Spieler s JOIN Teilnahme tn ON
s.SpielerNr = tn.Spieler_SpielerNr
) JOIN Turnier t ON
tn.Turnier_Bezeichnung = t.Bezeichnung;
Sof tware Engineering 2005W | SQL-Tutorial
27
Benutzersichten
 SQL-Views
CREATE VIEW Name AS Select-Anweisung;
▪ Jede zulässige SELECT-Anweisung kann als View definiert werden.
Sof tware Engineering 2005W | SQL-Tutorial
28
Inhalt des Tutorials
1
Grundlagen
2
Das Relationenmodell
3
Die Datenbanksprache SQL
4
Übungsbeispiel
Sof tware Engineering 2005W | SQL-Tutorial
29
Ünungsbeispiel - Aufgabenstellung

Erstellen Sie ein EER-Datenbankmodell das alle
notwendigen Entitäten und Beziehungen des Systems
abbildet. Redundante Informationen sollen vermieden
werden.

Erstellen Sie ein SQL Statement zum Erzeugen der
Tabelle AUFTRAG aus der in Punkt a modellierten
Datenbank.

Fügen Sie einen beispielhaften Datensatz durch ein SQL
Statement ein.
Sof tware Engineering 2005W | SQL-Tutorial
30
Ünungsbeispiel - Angabetext
Verwaltung eines Versanddienstes
Für die Verwaltung des Versanddienstes SendIT soll ein System erstellt
werden. Im System werden Auftraggeber erfasst. Diese erhalten eine
eindeutige ID und werden zusätzlich noch mit einem Namen, Adresse und einer
eMail-Adresse im System gespeichert. Ein Auftrag setzt sich aus mehreren
Einzelsendungen zusammen. Weiters wird für jeden Auftrag das
Erstellungsdatum, das geplante Durchführungsdatum, der Preis und eine
Zahlungsfrist (in Wochen) gespeichert. Ein Auftrag erhält eine eindeutige
Auftragsnummer (ID). Die maximale Zahlungsfrist ergibt sich aus der Anzahl der
Einzelsendungen. Wenn diese unter 100 liegt, kann eine maximale
Zahlungsfrist von 2 Wochen eingetragen werden. Sonst von 4 Wochen. Eine
Einzelsendung besteht aus mehreren Komponenten. Diese sind einerseits die
Ware und der Mitarbeiter der die Ware ausliefert und andererseits der
Empfänger. Weiters ist die Information verfügbar, wann die Einzelsendung
durchgeführt werden konnte und der Empfänger die Ware erhalten hat. Die
Ware erhält eine eindeutige ID, eine Beschreibung und einen Lagerort. Vom
Boten werden die überlichen Mitarbeiterdaten gespeichert. Mitarbeiter werden
ausser mit Namen, Adresse und einer eMail-Adresse auch mit einer SVNR im
System gespeichert.
Sof tware Engineering 2005W | SQL-Tutorial
31
Ünungsbeispiel - Lösung
ACHTUNG: Es gibt nie nur eine gültige Lösung!
Sof tware Engineering 2005W | SQL-Tutorial
32
Ünungsbeispiel - Lösung
create table AUFTRAG
(ID
integer not null primary key,
Erstellung
date
not null,
Durchfuehrung date,
Preis
integer not null,
Frist
integer not null,
check (Frist <= 4),
PersonID
integer not null,
foreign key(PersonID) references PERSON(ID));
insert into AUFTRAG values (0, 01-01-01, null, 100, 2, 78);
Sof tware Engineering 2005W | SQL-Tutorial
33