Download
1 / 151
1.51k likes | 1.63k Views
Rückblick. CREATE TABLE Statement. Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002. CREATE TABLE. Allgemein: CREATE TABLE table_name (spaltendefinitionsliste [,tabellenintegritätsregelliste]); spaltendefinition ::= spaltenname typangabe [default-Klausel]
E N D
Rückblick CREATE TABLE Statement Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
CREATE TABLE Allgemein: CREATE TABLE table_name(spaltendefinitionsliste[,tabellenintegritätsregelliste]); spaltendefinition ::=spaltenname typangabe [default-Klausel] [spaltenintegritätsregelliste] tabellenintegritätsregel ::= check-klausel | primary_key-klausel | unique-klausel | foreign-key-klausel Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
CREATE TABLE Beispiel CREATE TABLE bestellung ( bestellnr INTEGER NOT NULL, kundennr INTEGER NOT NULL, bestelldatum DATE NOT NULL, lieferdatum DATE, rechnungsbetrag DECIMAL(8,2), CHECK(bestelldatum < lieferdatum), PRIMARY KEY (bestellnr), FOREIGN KEY (kundennr) REFERENCES Kunde ON UPDATE CASCADE ON DELETE CASCADE ); Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SQL-Datentypen INTEGER (4 Byte) 123456, -653437, +12 SMALLINT (2 Byte) 1234. –6354 NUMERIC(p,q) (Dezimalzahl genau p Stellen, q hinter „.“) DECIMAL(p,q) (Dezimalzahl mind. p Stellen, q hinter „.“) FLOAT(p) (Gleitpunktzahlen mindestens p Stellen) 2.96E+8 CHARACTER(n) Zeichenketten mit genau n Zeichen CHARACTER VARYING(n) (Zeichenketten mit höchstens n) DATE DATE'1995-06-22' TIME(p) TIME'09:18:05.23, (p: Nachkommastellen für Sekunden) TIMESTAMP(p) Datum + Uhrzeit TIMESTAMP'1995-06-06 10:00' BOOLEAN TRUE, FALSE, UNKNOWN BIT(n) Bitketten mit genau n Bits Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
Übungsaufgabe Schemadefinition Vertriebsdatenbank (Web-Shop) in SQL Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
Preis Anzahl BDatum PNr BNr KuNr n 1 n m Posten bestellt Produkt Bestellung Kunde m n n 1 n n Anzahl LDATUM Preis offeriert LPreis liefert aus bearbeitet liefert OPosten beschreibt 1 n 1 Spediteur Mitarbeiter n Offerte 1 n 1 1 SNr MNr ONr bearbeitet Offerte Lieferant Kategorie KaNr LNr
Tabellen Vertriebsdatenbank Kunde(KuNr, Name, Adresse, Rabatt) Produkt(PNr, P-Name, KaNr, LNr, Preis, LPreis) Bestellung(BNr,KuNr,MNr,SNr,Bestelldatum, Lieferdatum) Lieferant(LNr,Name, Adresse) Kategorie(KaNr,Name) Spediteur(SNr, Name, Adresse) Mitarbeiter(MNr,Name, Adresse) Offerte(Onr, KuNr, MNr) Posten(PNr,BNr,Anzahl) Oposten(PNr,ONr,Anzahl,Preis) Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
Lösungsbeispiel 1 CREATE TABLE Kunde ( KuNr integer PRIMARY KEY, Name char(20) NOT NULL, Adresse char(50), Rabatt Decimal(3,1) ); Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
Lösungsbeispiel 2 CREATE TABLE Bestellung ( BNr integer PRIMARY KEY, KuNr integer NOT NULL, MNr integer, SNr integer, Bestelldatum Date DEFAULT CURRENT_DATE, Lieferdatum Date, FOREIGN KEY (KuNr) REFERENCES Kunde ON DELETE NO ACTION ON UPDATE CASCADE, FOREIGN KEY (MNr) REFERENCES Mitarbeiter ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (SNr) REFERENCES Spediteur ON DELETE SET NULL ON UPDATE CASCADE ); Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
Lösungsbeispiel 3 CREATE TABLE Posten ( BNr integer, PNr integer, Anzahl integer NOT NULL, PRIMARY KEY (BNr, PNr), FOREIGN KEY (BNr) REFERENCES Bestellung ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (PNr) REFERENCES Produkt ON DELETE NO ACTION ON UPDATE CASCADE ); Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
Aufgabe Bibliothek • Modellieren Sie eine Bibliothek (Bücher/ Ausleihe / Autoren / Benutzer (Ausleihende) / Vormerkungen) im E/R-Modell (möglichst redundanzfrei).Folgendes sollte dabei berücksichtigt werden: • Ein Buch kann mehrere Autoren haben. • Ein Buch kann in verschiedenen Auflagen vorliegen. • Jede Auflage eines Buches kann in mehreren Exemplaren in der Bibliothek vorhanden sein. • Bücher sollten nach explizit zugeordneten Schlagworten gesucht werden können. • Übersetzen Sie das E/R-Modell möglichst redundanzfrei ins relationale Modell (Datenbankschema in Tabellenform) • Geben Sie alle SQL-Kommandos an, die zur Anlage der Tabellen notwendig sind (inklusive aller sinnvollen Integritätsregeln). Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
ISBN Titel ANr Name ? m n Autor von Buch Autor Verlag Vorname Ort 1 n SNr hatAufl von Schlagwort m Wort n Auflage Buch- auflage Preis Datum 1 Jahr hatExemp leiht aus BNr Name 1 n m Datum Benutzer Buch- exemplar Vorname BibNr hat vorgemerkt m n Standort Adresse
Bibliothek - Tabellenmodell Autor(Anr, Name, Vorname) Buch(ISBN, Titel, Verlag, Ort) Autor-von(Anr,ISBN) Schlagwort(SNr, Wort) Schlagwort-von(SNr,ISBN) Buchauflage(Auflage, Preis, Jahr, ISBN) Buchexemplar(BibNr, Standort, ISBN, Auflage) Benutzer(BNr, Name, Vorname, Adresse) Leiht-aus(BibNr,BNr,Datum) Hat-vorgemerkt(BNr,BibNr, Datum)
Bibliothek Datenbankschema CREATE TABLE Statements
Autor CREATE TABLE Autor ( ANr INTEGER PRIMARY KEY, Name CHAR(30) NOT NULL, Vorname CHAR(30) );
Buch CREATE TABLE Buch ( ISBN INTEGER PRIMARY KEY, Verlag CHAR(30) NOT NULL, Titel CHAR(30) NOT NULL, Ort CHAR(30) );
Autor-von CREATE TABLE Autor-von ( ISBN INTEGER, ANr INTEGER, PRIMARY KEY ( ISBN, ANr), FOREIGN KEY ISBN REFERENCES Buch ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY ANr REFERENCES Autor ON DELETE CASCADE ON UPDATE CASCADE );
Schlagwort CREATE TABLE Schlagwort ( SNr INTEGER PRIMARY KEY, Wort CHAR(30) NOT NULL );
Schlagwort von CREATE TABLE Schlagwort-von ( ISBN INTEGER FOREIGN KEY REFERENCES Buch, SNr INTEGER FOREIGN KEY REFERENCES Schlagwort, PRIMARY KEY ( ISBN, SNr) );
Buchauflage CREATE TABLE Buchauflage ( ISBN INTEGER, Auflage INTEGER, Preis NUMERIC(8,2), Jahr CHAR(4), PRIMARY KEY(ISBN, Auflage), FOREIGN KEY ISBN REFERENCES Buch ON DELETE CASCADE ON UPDATE CASCADE);
Buchexemplar CREATE TABLE Buchexemplar ( BibNR INTEGER PRIMARY KEY, Standort CHAR(10), ISBN INTEGER NOT NULL, Auflage INTEGER NOT NULL, FOREIGN KEY ISBN REFERENCES Buchauflage ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY Auflage REFERENCES Buchauflage ON DELETE CASCADE ON UPDATE CASCADE );
Benutzer CREATE TABLE Benutzer ( BNr INTEGER PRIMARY KEY, Name CHAR(30) NOT NULL, Vorname CHAR(30) NOT NULL, Adresse CHAR(60) NOT NULL );
Leiht-aus CREATE TABLE leiht-aus ( BibNr INTEGER FOREIGN KEY REFERENCES Buchexemplar, BNr INTEGER FOREIGN KEY REFERENCES Benutzer, PRIMARY KEY (BNr, BibNr) );
Hat-vorgemerkt CREATE TABLE hat-vorgemerkt ( BibNr INTEGER FOREIGN KEY REFERENCES Buchexemplar, BNr INTEGER FOREIGN KEY REFERENCES Benutzer, Datum DATE, PRIMARY KEY (BNr, BibNr) );
SELECT Anweisung
SELECT mit einer Tabelle
SELECT - Anweisung • Die SELECT-Anweisung dient der Definition und Ausgabe einer virtuellen Tabelle auf der Basis vorhandener Tabellen und Abfragen • Die SELECT-Klausel realisiert die Projektion • Die Selektion erfolgt in den WHERE- und HAVING-Klauseln Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT - Anweisung Unterscheidung: • SELECT-Anweisung • Abfrage-Anweisungist eine SELECT-Anweisung ohne ORDER BY-Klausel Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT - Anweisung allgemeinste Form: SELECT [DISTINCT | ALL] A1, ..., Ak, <AGG>(Ak+1), ..., <AGG>(Ak+n)FROM R1, ..., Rm[WHERE <condition1>][GROUP BY Ai1, ..., Ail][HAVING <condition2>][ORDER BY Ap1, ..., Apm ] Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT -Reihenfolge Syntaktische Reihenfolge der Klauseln SELECT [DISTINCT | ALL] A1, ..., Ak, <AGG>(Ak+1), ..., <AGG>(Ak+n)FROM R1, ..., Rm[WHERE <condition1>][GROUP BY Ai1, ..., Ail][HAVING <condition2>][ORDER BY Ap1, ... Apm ] Die angegebene Reihenfolge der Klauseln ist zwingend! Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT -Reihenfolge „Zeitliche“ Abarbeitung der Klauseln 1. FROM-Klausel 2. [WHERE-Klausel] 3. [GROUP BY - Klausel] 4. [HAVING -Klausel] 5. SELECT -Klausel 6. [ORDER BY ... ] Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT - [DISTINCT | ALL] SELECT-Kommando ([DISTINCT | ALL]): SELECT [DISTINCT | ALL] A1, ..., Ak, FROM R1, ..., Rm DISTINCT : alle identische Zeilen in der Ergebnistabelle werden zusammengefasst. ALL : alle auftretenden Tupel werden angezeigt (Voreinstellung) Die Angabe ist optional. Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT - Klauseln SELECT-Klausel leistet die Projektion auf die gewünschten Attribute. Es werden die Attribute aufgelistet, die die Ergebnisliste enthalten soll. • „*“ listet alle Attribute der Tabelle auf. • Integriert auch arithmetische Operationen und Aggregatfunktionen Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT - Klauseln FROM-Klausel hier werden die Tabellen aufgelistet, aus denen die Daten entnommen werden sollen. ermöglicht Umbenennungen durch „Tupelvariablen“ bzw. ALIAS-Namen die verwendeten Relationen werden mittels des kartesischen Produktes verknüpft Verbünde (JOINS) können direkt definiert werden. Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT - Klauseln WHERE-Klausel • leistet die Selektion der gewünschten Tupel aus den betrachteten Tabellen, durch Angabe einer Bedingung. • Geschachtelte Unterabfragen sind in der WHERE-Klausel möglich. • Erlaubt die Formulierung von Verbundbedingungen um z.B. aus einem kartesischen Produkt einen Gleichverbund (EQUI-JOIN) zu machen. Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT - Klauseln GROUP BY-Klausel • fasst alle Tupel gemäss Gleichheit bezüglich der Werte der angegebenen Attribute jeweils in einer Gruppe zusammen und erzeugt eine Tabelle, in der Attribute auftreten, die für jede Gruppe einen Wert haben. Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT - Klauseln HAVING-Klausel leistet die Selektion der gewünschten Tupel aus der Ergebnistabelle, die durch die GROUP BY Klausel erzeugt wurde. ORDER BY-Klausel sorgt für eine sortierte Ausgabe der Ergebnistabelle gehört nicht zur Abfrage-Anweisung Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT - WHERE-Klausel [WHERE <condition1>] condition 1 ist dabei logischer Ausdruck logischer Ausdruck • atomarer logischer Ausdruck • (a AND b), (a OR b), NOT b wobei a und b logische Ausdrücke sind. Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT - WHERE-Klausel Alle Tupel, für die der Vergleich das Resultat FALSE oder UNKNOWN (NULL) ergibt, erscheinen nicht im Ergebnis. Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT - WHERE-Klausel [WHERE <condition1>] atomarer logischer Ausdruck • Term1 Vergleichsoperator Term 2 Vergleichsoperatoren: {=, <, >, <>,<=, >=} Beispiel: kundennr*24 = 19+7 Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT - WHERE-Klausel [WHERE <condition1>] Term • Spaltenname (z.B.: „name“, „kundennr“) • Vergleichswert ( 24, NULL, Peter) • Funktion auf Termen (kundennr*24 / 19+7) Numerische Operatoren: {+. -, *, /} viele zusätzliche Funktionen Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT - WHERE-Klausel Zusätzliche Funktionen • Aggregatfunktionen (COUNT, SUM, AVG, MAX, MIN etc.) • Datums- und Zeitfunktionen (LAST_DAY, NEXT_DAY, etc.) • Arithmetische Funktionen (ABS, LN, LOG, COS SIN etc) • Zeichenfunktionen (LENGTH, CONCAT, etc.) • Umwandlungsfunktionen (TO-CHAR, TO-NUMBER) Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT - WHERE-Klausel Atomarer logischer Ausdruck (Beispiel): kundennr = 103 ort <> ‘Basel‘ lieferdatum IS NULL lieferdatum IS NOT NULL mindestbestand = 400 mindestbestand / 2 = 12*24 Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT - WHERE-Klausel logischer Ausdruck (Beispiel): kundennr = 103 AND ort <> ‘Basel‘ lieferdatum IS NULL OR mindestbestand = 400 lieferdatum IS NOT NULL AND kundennr > 100 NOT mindestbestand / 2 = 12*24 Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT - NULL-Werte NULL-Werte in Vergleichen liefermenge = NULL hat als Ergebnis „unknown“ (NULL) Deshalb muss die Abfrage anders aussehen: liefermenge IS NULL bzw. liefermenge IS NOT NULL Diese Abfragen sind wahr oder falsch! Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT Beispiel Null-Werte: SELECT bestellnr, artikelnr, bestellmenge, liefermenge FROM position WHERE liefermenge IS NULL; Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT - Projektion und Selektion Beispiel 1: SELECT kundennr, name, status FROM kunde WHERE status = ‘S‘; Gibt alle Kundeneinträge mit status =„S“ aus, projiziert auf die Attribute „kundennr, name, status“, der Tabelle kunde. Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT - Projektion und Selektion Beispiel 2: SELECT * FROM kunde WHERE status = ‘S‘; Gibt alle Kundeneinträge mit status =„S“ aus, projiziert auf alle Attribute der Tabelle kunde. Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
SELECT Beispiel 3: SELECT bestellnr, artikelnr, bestellmenge, liefermenge FROM position WHERE liefermenge <= bestellmenge * 0.9; Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
