Bauinformatik II Softwareanwendungen 1 5. Semester 3. Vorlesung: Abfragen Verbunde in SQL Prof....

Bauinformatik IISoftwareanwendungen 1

5. Semester3. Vorlesung: Abfragen

Verbunde in SQLProf. Dr.-Ing. R. J. Scherer

Nürnberger Str. 31a2. OG, Raum 204

TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen

Relationale Datenbankenfür Bauingenieurprobleme


Folie-Nr.: 2Bauinformatik II, Softwareanwendunge 1; 1.

Vorlesung

Mengenoperationen

Vereinigung Produkt.a.b.c

.x

.y

.

.a

.a

.b

.b

.c

.c

.x

.y

.x

.y

.x

.y

KartesischesDifferenz Restriktion Projektion

.a1

.a2

.a3

.b1

.b1

.b2

.b1

.b2

.b3

.c1

.c2

.c3

.a1

.a2

.a3

.b1

.b1

.b2

.c1

.c1

.c2

(natürliche)Verbindung

.a

.a

.a

.b

.c

.x

.y

.z

.x

.y

.a.x.y..

DivisionSchnitt



Vorlesung

MengenoperationenVereinigung: R1 UNION R2 Aus den zwei Relationen R1 und R2 wird eine neue Relation erzeugt, die alle Tupel (Datensätze) enthält, die in wenigstens einer der beiden Relationen vorkommen.

Differenz: R1 MINUS R2 Aus den beiden Relationen R1 und R2 wird eine neue Relation erzeugt, die nurdieTupel enthält, die in R1, aber nicht in R2 vorkommen.

Projektion: R [ Attributauswahl ]Aus der Relation R wird durch diesen unären Operator eine neue Relation erzeugt, die aus allen Tupeln von R besteht, eingeschränkt auf die angegebene Auswahl von Attributen.

Restriktion: R WHERE Bedingung Aus der Relation R wird durch diesen unären Operator eine neue Relation erzeugt, die alle Tupel aus R enthält, die der angegebenen Bedingung genügen.



Vorlesung

MengenoperationenKartesisches Produkt: R1 TIMES R2 Aus den beiden Relationen R1 und R2 wird eine neue Relation erzeugt, die ausallen möglichen Kombinationen der Tupel der beiden Relationen bestehen.

Schnitt: R1 INTERSECT R2Aus den beiden Relationen R1 und R2 wird eine neue Relation erzeugt, die nurdieTupel enthält, die in beiden Relationen vorkommen.

Natürliche Verbindung: R1 JOIN R2Aus den beiden Relationen R1 und R2 wird eine neue Relation erzeugt, die ausAllen möglichen Kombinationen zwischen den Tupeln der beiden RelationenBesteht, wobei gemeinsame Attribute beider Relationen als Verknüpfung dienen.

Division: R1 DIVIDEBY R2Vorausgesetzt, dass die Relation R1 mindestens alle Attribute Att von R2 enthält, wird aus den beiden Relationen R1 und R2 eine neue Relation erzeugt, die alle Attribute von R1 außer den Attributen Att enthält, und die aus allen Tupeln aus R1 besteht, deren Werte in den Attributen Att mit den Werten aus R2 übereinstimmen.



Vorlesung

SQL – SELECT

SELECT-Hauptteil:[ { UNION | EXCEPT| INTERSECT } [ ALL ] SELECT-Hauptteil: [ { UNION | EXCEPT | INTERESECT } [ ALL ] . . . ] ] [ ORDER BY Ordnungsliste ]

Ein kompletter SELECT-Befehl kann aus beliebig vielen Hauptteilen, die über UNION, EXCEPT oder INTERSECT miteinander verknüpft sind, bestehen.

Die Punkte deuten an, dass die Aufzählung beliebig fortgesetzt werden kann. Bei mehr als zwei Verknüpfungen sollten jedoch Klammern gesetzt werden, um die Eindeutigkeit der Reihenfolge zu garantieren.



Vorlesung

SQL: SELECT - Projektion

SELECT Vorname , Name, PersNr FROM Mitarbeiter ;

Es werden nur die Spalten Vorname, Name und PersNr in dieser von den gespeicherten Daten abweichenden Reihenfolge aufgelistet.

Auflisten (nur) bestimmter Spalten in abweichender Reihenfolge:

SELECT Name , Vorname FROM Mitarbeiter ;

Es werden nur die Spalten Name und Vorname von der Tabelle Mitarbeiter in der Reihenfolge: Name, Vorname aufgelistet.

Auflisten (nur) bestimmter Spalten einer Tabelle:Projektion

nm1

lRen1m1m1

attr,,attrw,,w,,wtlRet

attr,,attrattr,,attr|w,,w:lRe

m1



Vorlesung

SQL: SELECT - Restriktion

SELECT * FROM Mitarbeiter WHERE LOHN <= 2500 ;

Auflisten (nur) bestimmter Datensätze in Abhängigkeit von einer Bedingung:

Es werden alle Datensätze aufgelistet, bei denen die Spalte Lohn <= 2500 ist.

Restriktion

TRUEtBedingunglRet|t:lReBedingung



Vorlesung

SQL: SELECT - BEISPIELRestriktion UND Projektion

Es werden alle Datensätze mit den Spalten Vorname , Name und PrsNrin dieser von den gespeicherten Daten abweichenden Reihenfolge aufgelistet,bei denen die Spalte Lohn <= 2500 ist.

Auflisten bestimmter Attriubte aus bestimmten Datensätzen in Abhängigkeit von einer Bedingung:

SELECT Name, Vorname, PrsNr FROM Mitarbeiter WHERE LOHN <= 2500 ;

rMitarbeitesNrPr,Vorname,Name2500Lohn

RestriktionProjektion



Vorlesung

SQL: SELECT - Relationen für WHERE-Klausel

Operatoren:

IntervalloperatorEnthaltenoperatorAuswahloperatorenÄhnlichkeitsoperatorExistenzoperatorEindeutigkeitsoperatorNulloperator

[ NOT ] BETWEEN . . . AND[ NOT ] INALL, ANY, SOME[ NOT ] LIKEEXISTSUNIQUEIS [ NOT ] NULL



Vorlesung


Auswahloperatoren ALL, ANY, SOME

SELECT Name, Vorname FROM Mitarbeiter WHERE Lohn > ALL (SELECT Lohn FROM Mitarbeiter WHERE Abteilung=5) ;

Ermittle alle Mitarbeiter, die mehr verdienen als alle Mitarbeiterder Abteilung 5

ALL testet, ob der Vergleichsoperator für alle Zeilen der Tabelle zutrifft

ANY (oder SOME) testet, ob der Vergleichsoperator für (mindestens) eine Zeile der Tabelle zutrifft.

SELECT Name, Vorname FROM Mitarbeiter WHERE Lohn > ANY (SELECT Lohn FROM Mitarbeiter WHERE Abteilung=5) ;

Ermittle alle Mitarbeiter, die mehr verdienen als irgend ein Mitarbeiterder Abteilung 5



Vorlesung


Existenzoperator EXISTS

SELECT Name, Vorname FROM Bauleiter WHERE NOT EXISTS (SELECT Name,VornameFROM Baustelle,Bauleiter WHERE Bauleiter.PersNr=Baustelle.BaulNr)

Ermittle die Namen der Bauleiter, die (zur Zeit) keine Baustelle haben.

EXISTS testet, ob eine Tabelle nicht leer ist. NOT EXISTS testet, ob eine Tabelle leer ist.



Vorlesung


Nulloperator IS [NOT] NULL

SELECT * FROM Baustellen WHERE BaulNr IS NULL)

Ermittle die Baustellen, denen kein Bauleiter zugewiesen ist



Vorlesung

SQL: SELECT - Vereinigung

SELECT attr1 , attr2 FROM Relm UNION SELECT attr1, attr2 FROM Relt ;

Voraussetzung: Beide Relationen haben das gleiche Relationenschema (gleiche Attribute und gleiche Attributtypen)

Doppelte Zeilen werden automatisch unterdrückt. Mit UNION ALL werden sie angezeigt.

Vereinigung aller Tupel der Relation R mit allen Tupeln der Relation S zu einer Relation

Vereinigung

tmtm lRetlRet|t:lRelRe



Vorlesung

Mitarbeiter PersNr Name Vorname Taetigkeit

12363 Ahrend Gerd Maurer

13332 Bayer Andreas Kranführer

26661 Berger Sven Kranführer

26663 Czerny Petra Baggerfahrer

35558 Müller Felix Bauleiter

36663 Müller Paul Gerüstbauer

36886 Berger Bert Baggerfahrer

Kunden KdNr Kd_Name Kd_Vorname Kd_Wohnort

1 Meier Kurt Radeberg

2 Müller Paul Kamenz

3 Ebert Karl Bautzen

4 Huber Hans München

SELECT Name , Vorname FROM Mitarbeiter UNION SELECT Kd_Name, Kd_Vorname FROM Kunden ;

Name Vorname

Ahrend Gerd

Bayer Andreas

Berger Bert

Berger Sven

Czerny Petra

Ebert Karl

Huber Hans

Meier Kurt

Müller Felix

Müller Paul



Vorlesung

SQL: SELECT - SCHNITT

SELECT * FROM Relm INTERSECT SELECT * FROM Relt ;

Ermittlung aller Tupel, die sowohl in Relation R als auch in Relation S enthalten sind.

Schnitt

tmtm lRetlRet|t:lRelRe



Vorlesung

Mitarbeiter

PersNr Name Vorname Taetigkeit













SELECT Name , Vorname FROM Mitarbeiter INTERSECT SELECT Kd_Name, Kd_Vorname FROM Kunden ;

Name Vorname

Müller Paul



Vorlesung

SQL: SELECT - DIFFERENZ

SELECT * FROM Relm MINUS SELECT * FROM Relt

Entfernen aller Tupel aus Relation R, die auch in Relation S enthalten sind.

Differenz

tmtm lRetlRet|t:lRe\lRe



Vorlesung

Mitarbeiter

PersNr Name Vorname Taetigkeit













Name Vorname

Ahrend Gerd

Bayer Andreas

Berger Bert

Berger Sven

Czerny Petra

Müller Felix

Müller Paul

SELECT Name,Vorname FROM Mitarbeiter MINUS SELECT Kd_Name, Kd_Vorname FROM Kunden



Vorlesung

SQL: SELECT - Kartesisches Produkt

SELECT * FROM Relm CROSS JOIN Relt

Erzeugen aller Kombinationen der Tupel aus Relm und Relt

Es wird das Kreuzprodukt aller Spalten der beiden Tabellen ausgegeben, d. h. alle möglichen Kombinationen aller Spalten der 1. Tabelle mit allen Spalten der 2. Tabelle.

SELECT * FROM Relm, Relt ;

tm1mtm1mttt

m1m1mmm

tm1mm1

tm

w,,wattr,,attrtlRet

w,,wattr,,attrtlRet

|w,,w,w,,w

:lRelRe



Vorlesung

R

A B C D

1 2 3 4

4 5 6 7

7 8 9 0

S

R x S

E F G

1 2 3

7 8 9

8 1 3

A B C D E F G

1 2 3 4 1 2 3

4 5 6 7 1 2 3

7 8 9 0 1 2 3

1 2 3 4 7 8 9

4 5 6 7 7 8 9

7 8 9 0 7 8 9

1 2 3 4 8 1 3

4 5 6 7 8 1 3

7 8 9 0 8 1 3

SELECT * FROM R CROSS JOIN S



Vorlesung

SQL: SELECT - Innerer Verbund / Gleichverbund

SELECT * FROM Relm INNER JOIN Relt ON Gleichverbundbedingung

Verbindet Tupel aus zwei Relationen, wenn der Wert zweier Attribute identisch ist.

tmngundbedinguGleichverbtm lRelRe:lRelRe

Der INNER JOIN entspricht der Bildung des kartesischen Produkts und anschließender Selektion mit der Bedingung, dass der Wert eines gewählten Attributes der Relation Relm gleich dem Wert eines gewählten Attributes der Relation Relt ist.



Vorlesung

A B C D E F G

1 2 3 4 1 2 3

7 8 9 0 7 8 9

R

A B C D

1 2 3 4

4 5 6 7

7 8 9 0

S

SELECT * FROM R INNER JOIN S ON R.A = S.E

SR EA

E F G

1 2 3

7 8 9

8 1 3



Vorlesung

SQL: SELECT - NATÜRLICHER VERBUND

SELECT Relm.*, Relt.* FROM Relm NATURAL JOIN Relt

Verknüpft Tabellen über die Gleichheit aller gleichlautenden Spalten.

Attribute mit gleichen Attributnamen sind im Ergebnis nur einmal enthalten.Haben die Relationen keine gleichlautenden Attributnamen, wird der Natural Join zum Cross Join. Gibt es nur ein gleichlautendes Attribut, so ist der Natural Join ein Inner Join mit anschließender Projektion, bei der das gleichnamige Attribut ein mal ausgeblendet wird.

tmattr.lReattr.lReattr.lReattr.lRe

tm1ppkj1tm

lRelRe

attr,,attr,attr,,attr,attr,,attr:lRelRe

ptpmktkm



Vorlesung

R

A B C D

1 2 3 4

4 5 6 7

2 2 7 8

3 4 1 2

7 8 9 0

S

C D G

1 2 3

7 8 9

8 1 3

A B C D G

2 2 7 8 9

3 4 1 2 3

SELECT R.*, S.* FROM R NATURAL JOIN S

SR



Vorlesung

SQL: SELECT - LEFT JOIN

SELECT * FROM Relm LEFT JOIN Relt ON Relm.attrx = Relt.attry

Mit einem Left Outer Join wird eine sogenannte linke Inklusionsverknüpfung erstellt

Linke Inklusionsverknüpfungen schließen alle Datensätze aus der ersten (linken) Tabelle ein, auch wenn keine entsprechenden Werte in der zweiten (rechten) Tabelle existieren.

m1m1etme

m1m1mmm

m1tm1mm1

tm1mm1

tmtm

w,,wattr,,attrtlRelRet

w,,wattr,,attrtlRet

,,,w,,wattr,,attr,attr,,attrt

|attr,,attr,attr,,attrt

lRelRe:lRelRe

NULL



Vorlesung

A B C D E F G

1 2 3 4 1 2 3

4 5 6 7

7 8 9 0 7 8 9

R

A B C D

1 2 3 4

4 5 6 7

7 8 9 0

S

E F G

1 2 3

7 8 9

8 1 3

SELECT * FROM R LEFT JOIN S ON R.A = S.E

SR



Vorlesung

SQL: SELECT - RIGHT JOIN

SELECT * FROM Relm RIGHT JOIN Relt ON Relm.attrx = Relt.attry

Mit einem Right Outer Join wird eine sogenannte rechte Inklusionsverknüpfung erstellt

Rechte Inklusionsverknüpfungen schließen alle Datensätze aus der zweiten (rechten) Tabelle ein, auch wenn keine entsprechenden Werte in der ersten (linken) Tabelle existieren.

t1t1etme

t1t1ttt

t1tm1mm1

tm1mm1

tmtm


w,,wattr,,attrtlRet

w,,w,,,attr,,attr,attr,,attrt


lRelRe:lRelRe

NULL



Vorlesung

A B C D E F G

1 2 3 4 1 2 3

7 8 9 0 7 8 9

8 1 3

R

A B C D

1 2 3 4

4 5 6 7

7 8 9 0

S

E F G

1 2 3

7 8 9

8 1 3

SELECT * FROM R RIGHT JOIN S ON R.A = S.E

SR



Vorlesung

SQL: SELECT - FULL JOINKombination von Left Outer Join und Right Outer Join.

Beidseitige Inklusionsverknüpfung enthält -die Tupel aus zwei Relationen, bei denen jeweils ein Attribut dieselben Werte enthält-alle Tupel aus der ersten (linken) und der zweiten (rechten) Relation, die keinen Partner in der jeweils anderen Relation haben.

t1t1etme

t1t1ttt

t1tp1pp1

tp1pp1


w,,wattr,,attrtlRet



m1m1etme

m1m1mmm

m1tm1mm1

tm1mm1

tmtm


w,,wattr,,attrtlRet



lRelRe:lRelRe

SELECT * FROM Relm FULL JOIN Relt ON Relm.attrx = Relt.attry



Vorlesung

A B C D E F G

1 2 3 4 1 2 3

4 5 6 7

7 8 9 0 7 8 9

8 1 3

SR FULL

R

A B C D

1 2 3 4

4 5 6 7

7 8 9 0

S

E F G

1 2 3

7 8 9

8 1 3

SELECT * FROM R FULL JOIN S ON (R.A = S.E)

SR



Vorlesung

SQL: SELECT - UNION JOIN

SELECT * FROM Relm UNION JOIN Relt

Es werden die Tupel beider Relationen aufgenommen. Sie werden aber nicht über eine Bedingung verknüpft.

t1t1ttt

t1tp1pp1

tp1pp1

w,,wattr,,attrtlRet



m1m1mmm

m1tm1mm1

tm1mm1

tUNIONm

w,,wattr,,attrtlRet



:lRelRe



Vorlesung

A B C D E F G

1 2 3 4

4 5 6 7

7 8 9 0

1 2 3

7 8 9

8 1 3

R

A B C D

1 2 3 4

4 5 6 7

7 8 9 0

S

E F G

1 2 3

7 8 9

8 1 3

SR UNIONSELECT * FROM R UNION JOIN S



Vorlesung

SQL: SELECT - THETA JOIN

Der Theta Join ist die Verallgemeinerung des Inner Join

Während beim Inner Join die Gleichheit des Inhalts zweier Attribute verglichen wird, wird beim Theta Join der Inhalt der Attribute i und j mit einer beliebigen Formel Theta(i,j) verglichen

tmingungVerbundbedtm lRelRe:lRelRe

SELECT * FROM Relm INNER JOIN Relt ON (Theta-)Verbundbedingung



Vorlesung

A B C D E F G

1 2 3 4 7 8 9

4 5 6 7 7 8 9

1 2 3 4 8 1 3

4 5 6 7 8 1 3

7 8 9 0 8 1 3

SR EA

R

A B C D

1 2 3 4

4 5 6 7

7 8 9 0

S

E F G

1 2 3

7 8 9

8 1 3

SELECT * FROM R INNER JOIN S ON R.A <= S.E



Vorlesung

SQL: SELECT - SELF JOIN

SELECT R1.*, R2.*FROM R AS R1 INNER JOIN R AS R2 ON R1.A = R2.D

Der Self-Join ist ein beliebiger Join, bei demein Attribut mit einem Attribut derselben Relation verbunden wird (Rekursion, vgl. 5. Vorlesung)

Um zweimal dieselbe Tabelle benutzen zu können,bekommt sie zwei verschiedene Alias-Namen



Vorlesung

R1.A R1.B R1.C R1.D R2.A R2.B R2.C R2.D

4 5 6 7 1 2 3 4

7 8 9 0 4 5 6 7

R

A B C D

1 2 3 4

4 5 6 7

7 8 9 0

SELECT R1.*, R2.*FROM R AS R1 INNER JOIN R AS R2 ON R1.A = R2.D

A B C D

1 2 3 4

4 5 6 7

7 8 9 0

A B C D

1 2 3 4

4 5 6 7

7 8 9 0

R1 R2



Vorlesung

SQL: SELECT - (RELATIONALE) DIVISION

SELECT * FROM T DIVIDEBY S

Mit der Division filtert man alle Teilrelationen einer Relation aus, die zu allen Tupeln einer zweiten Relation in Beziehung stehen.

Das Konzept der Division ist eng verknüpft mit dem Kartesischen Produk T = R x S zweier Relationen R und S, so daß T/S (T geteilt durch S) die Relation R ergibt. Hat T die Anzahl t Spalten und S die Anzahl s Spalten, so hat T/S die Anzahl t - s Spalten.

RST

SRT



Vorlesung

R

A B C D

1 2 3 4

4 5 6 7

7 8 9 0

S

T

E F G

1 2 3

7 8 9

8 1 3

A B C D E F G

1 2 3 4 1 2 3

4 5 6 7 1 2 3

7 8 9 0 1 2 3

1 2 3 4 7 8 9

4 5 6 7 7 8 9

7 8 9 0 7 8 9

1 2 3 4 8 1 3

4 5 6 7 8 1 3

7 8 9 0 8 1 3

SELECT * FROM T DIVIDEBY S



Vorlesung

SQL – FREMDSCHLÜSSELBESCHRÄNKUNGEN

FOREIGN KEY (Spaltenname1) REFERENCES tbl_name (Spaltenname2, ...) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

Fremdschlüsselbeschränkungen regeln die Maßnahmen bei Veränderungen in der "Elterntabelle", auf die durch Fremdschlüssel aus der "Kindtabelle" verwiesen wurde

Die über einen Fremdschlüssel aufgebaute referenzielle Integrität stellt sicher, dass in der Fremdschlüsseltabelle kein Fremdschlüssel existieren kann, der nicht auch in der referenzierten Tabelle als Wert des entsprechenden Primärschlüssels vorhanden ist.



Vorlesung


Wenn der Benutzer versucht, in der Elterntabelle eine Zeile zu ändern oder zu löschen, zu der in der Kindtabelle eine oder mehr passende Zeilen vorhanden sind, bieten sich folgende Möglichkeiten:

CASCADE: Bei Löschung/Änderung einer Zeile der Elterntabelle werden automatisch die zugehörigen Zeilen der Kindtabelle auch gelöscht oder geändert. Es gibt sowohl ON DELETE CASCADE als auch ON UPDATE CASCADE. Zwischen zwei Tabellen sollten Sie bitte nicht mehrere ON UPDATE CASCADE-Klauseln definieren, die auf derselben Spalte der Eltern- oder Kindtabelle arbeiten.

SET NULL: Bei Löschung/Änderung einer Zeile der Elterntabelle werden automatisch die zugehörigen Fremdschlüsselspalten der Kindtabelle auf NULL gesetzt. Das gilt nur, wenn die Fremdschlüsselspalten nicht als NOT NULL definiert sind. Sowohl ON DELETE SET NULL als auch ON UPDATE SET NULL wird unterstützt.



Vorlesung


NO ACTION: Im Standard-SQL bedeutet NO ACTION tatsächlich keine Aktion in dem Sinne, dass jeder Versuch, einen Primärschlüssel zu löschen oder zu ändern, unterbunden wird, wenn es dazu einen Fremschlüsselwert in der referenzierten Tabelle gibt. Die Lösch- oder Änderungsoperation auf der Elterntabelle werden zurückgewiesen.

RESTRICT weist die Lösch- oder Änderungsoperation auf der Elterntabelle zurück. NO ACTION und RESTRICT sind dasselbe wie ein Auslassen der ON DELETE- oder ON UPDATE-Klausel. (Manche Datenbanksysteme kennen verzögerte Prüfungen (deferred checks), zu denen auch NO ACTION gehört. Da in MySQL Fremdschlüssel-Constraints jedoch sofort geprüft werden, sind NO ACTION und RESTRICT hier dasselbe.)

Bauinformatik II Softwareanwendungen 1 5. Semester 3. Vorlesung: Abfragen Verbunde in SQL Prof....

Documents

Transcript of Bauinformatik II Softwareanwendungen 1 5. Semester 3. Vorlesung: Abfragen Verbunde in SQL Prof....