Bauinformatik II Softwareanwendungen 1 Relationale Datenbanken für Bauingenieurprobleme 5. Semester 3. Vorlesung: Abfragen Verbunde in SQL Prof. Dr.-Ing. R. J. Scherer Nürnberger Str. 31a 2. OG, Raum 204 TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Mengenoperationen Kartesisches Vereinigung Differenz Restriktion Projektion Produkt a x a x b y a y b x c b y c x c y (natürliche) Schnitt Division Verbindung a x x b1 c1 a1 b1 c1 a y a y a1 b1 b2 c2 a2 b1 c1 a z a2 b1 b3 c3 a3 b2 c2 b x a3 b2 c y . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 2 Mengenoperationen Vereinigung: 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 nur dieTupel enthält, die in R1, aber nicht in R2 vorkommen. 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. 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. TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 3 Mengenoperationen Kartesisches Produkt: R1 TIMES R2 Aus den beiden Relationen R1 und R2 wird eine neue Relation erzeugt, die aus allen möglichen Kombinationen der Tupel der beiden Relationen bestehen. Schnitt: R1 INTERSECT R2 Aus den beiden Relationen R1 und R2 wird eine neue Relation erzeugt, die nur dieTupel enthält, die in beiden Relationen vorkommen. Natürliche Verbindung: R1 JOIN R2 Aus den beiden Relationen R1 und R2 wird eine neue Relation erzeugt, die aus Allen möglichen Kombinationen zwischen den Tupeln der beiden Relationen Besteht, wobei gemeinsame Attribute beider Relationen als Verknüpfung dienen. Division: R1 DIVIDEBY R2 Vorausgesetzt, 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. TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 4 SQL – SELECT Ein kompletter SELECT-Befehl kann aus beliebig vielen Hauptteilen, die über UNION, EXCEPT oder INTERSECT miteinander verknüpft sind, bestehen. SELECT-Hauptteil: [ { UNION | EXCEPT| INTERSECT } [ ALL ] SELECT-Hauptteil: [ { UNION | EXCEPT | INTERESECT } [ ALL ] . . . ] ] [ ORDER BY Ordnungsliste ] 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. TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 5 SQL: SELECT - Projektion Projektion Auflisten (nur) bestimmter Spalten einer Tabelle: Re l w , , w | attr , , attr attr , , attr 1 m 1 m 1 n attr1 ,,attrm Re l : t Re lt w1 ,, w m ,, w n 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 in abweichender Reihenfolge: SELECT Vorname , Name, PersNr FROM Mitarbeiter ; Es werden nur die Spalten Vorname, Name und PersNr in dieser von den gespeicherten Daten abweichenden Reihenfolge aufgelistet. TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 6 SQL: SELECT - Restriktion Restriktion Auflisten (nur) bestimmter Datensätze in Abhängigkeit von einer Bedingung: BedingungRe l : t | t Re l Bedingung t TRUE SELECT * FROM Mitarbeiter WHERE LOHN <= 2500 ; Es werden alle Datensätze aufgelistet, bei denen die Spalte Lohn <= 2500 ist. TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 7 SQL: SELECT - BEISPIEL Restriktion UND Projektion Projektion Restriktion Auflisten bestimmter Attriubte aus bestimmten Datensätzen in Abhängigkeit von einer Bedingung: Lohn 2500 Name ,Vorname ,PrsNr Mitarbeite r SELECT Name, Vorname, PrsNr FROM Mitarbeiter WHERE LOHN <= 2500 ; Es werden alle Datensätze mit den Spalten Vorname , Name und PrsNr in dieser von den gespeicherten Daten abweichenden Reihenfolge aufgelistet, bei denen die Spalte Lohn <= 2500 ist. TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 8 SQL: SELECT - Relationen für WHERE-Klausel Operatoren: Intervalloperator Enthaltenoperator Auswahloperatoren Ähnlichkeitsoperator Existenzoperator Eindeutigkeitsoperator Nulloperator [ NOT ] BETWEEN . . . AND [ NOT ] IN ALL, ANY, SOME [ NOT ] LIKE EXISTS UNIQUE IS [ NOT ] NULL TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 9 SQL: SELECT - Relationen für WHERE-Klausel Auswahloperatoren ALL, ANY, SOME ALL testet, ob der Vergleichsoperator für alle Zeilen der Tabelle zutrifft Ermittle alle Mitarbeiter, die mehr verdienen als alle Mitarbeiter der Abteilung 5 SELECT Name, Vorname FROM Mitarbeiter WHERE Lohn > ALL (SELECT Lohn FROM Mitarbeiter WHERE Abteilung=5) ; ANY (oder SOME) testet, ob der Vergleichsoperator für (mindestens) eine Zeile der Tabelle zutrifft. Ermittle alle Mitarbeiter, die mehr verdienen als irgend ein Mitarbeiter der Abteilung 5 SELECT Name, Vorname FROM Mitarbeiter WHERE Lohn > ANY (SELECT Lohn FROM Mitarbeiter WHERE Abteilung=5) ; TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 10 SQL: SELECT - Relationen für WHERE-Klausel Existenzoperator EXISTS EXISTS testet, ob eine Tabelle nicht leer ist. NOT EXISTS testet, ob eine Tabelle leer ist. Ermittle die Namen der Bauleiter, die (zur Zeit) keine Baustelle haben. SELECT Name, Vorname FROM Bauleiter WHERE NOT EXISTS (SELECT Name,Vorname FROM Baustelle,Bauleiter WHERE Bauleiter.PersNr=Baustelle.BaulNr) TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 11 SQL: SELECT - Relationen für WHERE-Klausel Nulloperator IS [NOT] NULL Ermittle die Baustellen, denen kein Bauleiter zugewiesen ist SELECT * FROM Baustellen WHERE BaulNr IS NULL) TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 12 SQL: SELECT - Vereinigung Vereinigung Vereinigung aller Tupel der Relation R mit allen Tupeln der Relation S zu einer Relation Re l m Re l t : t | t Re l m t Re l t 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. TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 13 Mitarbeiter Kunden 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 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 TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 14 SQL: SELECT - SCHNITT Schnitt Ermittlung aller Tupel, die sowohl in Relation R als auch in Relation S enthalten sind. Re l m Re l t : t | t Re l m t Re l t SELECT * FROM Relm INTERSECT SELECT * FROM Relt ; TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 15 Kunden Mitarbeiter Kd_Name Kd_Vorname Kd_Wohnort 1 Meier Kurt Radeberg Kranführer 2 Müller Paul Kamenz Sven Kranführer 3 Ebert Karl Bautzen Czerny Petra Baggerfahrer 4 Huber Hans München 35558 Müller Felix Bauleiter 36663 Müller Paul Gerüstbauer 36886 Berger Bert Baggerfahrer PersNr Name Vorname Taetigkeit 12363 Ahrend Gerd Maurer 13332 Bayer Andreas 26661 Berger 26663 KdNr SELECT Name , Vorname FROM Mitarbeiter INTERSECT SELECT Kd_Name, Kd_Vorname FROM Kunden ; Name Vorname Müller Paul TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 16 SQL: SELECT - DIFFERENZ Differenz Entfernen aller Tupel aus Relation R, die auch in Relation S enthalten sind. Re l m \ Re l t : t | t Re l m t Re l t SELECT * FROM Relm MINUS SELECT * FROM Relt TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 17 Mitarbeiter Kunden Kd_Name Kd_Vorname Kd_Wohnort 1 Meier Kurt Radeberg Kranführer 2 Müller Paul Kamenz Sven Kranführer 3 Ebert Karl Bautzen Czerny Petra Baggerfahrer 4 Huber Hans München 35558 Müller Felix Bauleiter 36663 Müller Paul Gerüstbauer 36886 Berger Bert Baggerfahrer PersNr Name Vorname Taetigkeit 12363 Ahrend Gerd Maurer 13332 Bayer Andreas 26661 Berger 26663 KdNr SELECT Name,Vorname FROM Mitarbeiter MINUS SELECT Kd_Name, Kd_Vorname FROM Kunden Name Vorname Ahrend Gerd Bayer Andreas Berger Bert Berger Sven Czerny Petra Müller Felix Müller Paul TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 18 SQL: SELECT - Kartesisches Produkt Erzeugen aller Kombinationen der Tupel aus Relm und Relt w1 ,, w m , w m1 ,, w m t | Re l m Re l t : t m Re l m t m attr1 ,, attrm w1 ,, w m t t Re l t t t attrm1 ,, attrm t w m1 ,, w m t SELECT * FROM Relm CROSS JOIN Relt SELECT * FROM Relm, 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. TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 19 R S A B C D E F G 1 2 3 4 1 2 3 4 5 6 7 7 8 9 7 8 9 0 8 1 3 SELECT * FROM R CROSS JOIN S RxS 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 TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 20 SQL: SELECT - Innerer Verbund / Gleichverbund Verbindet Tupel aus zwei Relationen, wenn der Wert zweier Attribute identisch ist. Re l m Re l t : Gleichverbundbedingung Re l m Re l t SELECT * FROM Relm INNER JOIN Relt ON Gleichverbundbedingung 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. TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 21 R S A B C D E F G 1 2 3 4 1 2 3 4 5 6 7 7 8 9 7 8 9 0 8 1 3 SELECT * FROM R INNER JOIN S ON R.A = S.E R AES A B C D E F G 1 2 3 4 1 2 3 7 8 9 0 7 8 9 TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 22 SQL: SELECT - NATÜRLICHER VERBUND Verknüpft Tabellen über die Gleichheit aller gleichlautenden Spalten. Re l mRe l t : attr1 , , attr j , attrk , , attrp , attrp 1 , , attrm t Re lm .attrk Re l t .attrk Re l m .attrp Re l t .attrp Re l m Re l t SELECT Relm.*, Relt.* FROM Relm NATURAL JOIN Relt 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. TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 23 R S A B C D C D G 1 2 3 4 1 2 3 4 5 6 7 7 8 9 2 2 7 8 8 1 3 3 4 1 2 7 8 9 0 SELECT R.*, S.* FROM R NATURAL JOIN S R S A B C D G 2 2 7 8 9 3 4 1 2 3 TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 24 SQL: SELECT - LEFT JOIN Mit einem Left Outer Join wird eine sogenannte linke Inklusionsverknüpfung erstellt Re l m t attr1 , , attrm , attrm 1 , , attrm t | t attr , , attr , attr , , attr w , , w , , , 1 m m 1 m t 1 m Re l t : Re l m Re l t t Re l t attr , , attr w , , w m m m 1 m 1 m t Re l Re l t attr , , attr w , , w e m t e 1 m 1 m NULL SELECT * FROM Relm LEFT JOIN Relt ON Relm.attrx = Relt.attry 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. TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 25 R S A B C D E F G 1 2 3 4 1 2 3 4 5 6 7 7 8 9 7 8 9 0 8 1 3 SELECT * FROM R LEFT JOIN S ON R.A = S.E R S 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 TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 26 SQL: SELECT - RIGHT JOIN Mit einem Right Outer Join wird eine sogenannte rechte Inklusionsverknüpfung erstellt Re l m t attr1 , , attrm , attrm 1 , , attrm t | t attr1 , , attrm , attrm 1 , , attrm t , ,, w 1 , , w t Re l t : Re l m Re l t t t Re l t t t attr1 , , attrt w 1 , , w t t Re l Re l t attr , , attr w , , w e m t e 1 t 1 t SELECT * FROM Relm RIGHT JOIN Relt ON Relm.attrx = Relt.attry NULL 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. TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 27 R S A B C D E F G 1 2 3 4 1 2 3 4 5 6 7 7 8 9 7 8 9 0 8 1 3 SELECT * FROM R RIGHT JOIN S ON R.A = S.E R S A B C D E F G 1 2 3 4 1 2 3 7 8 9 0 7 8 9 8 1 3 TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 28 SQL: SELECT - FULL JOIN Kombination von Left Outer Join und Right Outer Join. Re l m Re l t : Re l m Re l t t attr1 , , attrm , attrm 1 , , attrm t | t attr , , attr , attr , , attr w , , w , , , 1 m m 1 m t 1 m t m Re l m t m attr1 , , attrm w 1 , , w m t Re l Re l t attr , , attr w , , w e m t e 1 m 1 m t attr1 ,, attrp , attrp 1 ,, attrp t | t attr1 ,, attrp , attrp 1 ,, attrp t ,,, w1 ,, w t t Re l t attr , , attr w , , w t t t 1 t 1 t t Re l Re l t attr ,, attr w ,, w e m t e 1 t 1 t SELECT * FROM Relm FULL JOIN Relt ON Relm.attrx = Relt.attry 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. TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 29 R S A B C D E F G 1 2 3 4 1 2 3 4 5 6 7 7 8 9 7 8 9 0 8 1 3 SELECT * FROM R FULLR JOIN FULL S ONS (R.A = S.E) R S 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 TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 30 SQL: SELECT - UNION JOIN Es werden die Tupel beider Relationen aufgenommen. Sie werden aber nicht über eine Bedingung verknüpft. t attr1 ,, attrm , attrm1 ,, attrm t | Re l m UNIONRe l t : t attr1 ,, attrm , attrm1 ,, attrm t w1 ,, w m ,,, t m Re l m t m attr1 ,, attrm w1 ,, w m t attr1 ,, attrp , attrp 1 ,, attrp t | t attr1 ,, attrp , attrp 1 ,, attrp t ,,, w1 ,, w t t t Re l t t t attr1 ,, attrt w1 ,, w t SELECT * FROM Relm UNION JOIN Relt TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 31 R S A B C D E F G 1 2 3 4 1 2 3 4 5 6 7 7 8 9 7 8 9 0 8 1 3 SELECT * FROM R UNION JOIN S R UNIONS 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 TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 32 SQL: SELECT - THETA JOIN Der Theta Join ist die Verallgemeinerung des Inner Join Re l m Re l t : VerbundbedingungRe l m Re l t SELECT * FROM Relm INNER JOIN Relt ON (Theta-)Verbundbedingung 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 TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 33 R S A B C D E F G 1 2 3 4 1 2 3 4 5 6 7 7 8 9 7 8 9 0 8 1 3 SELECT * FROM R INNER JOIN S ON R.A <= S.E R AES 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 TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 34 SQL: SELECT - SELF JOIN Der Self-Join ist ein beliebiger Join, bei dem ein Attribut mit einem Attribut derselben Relation verbunden wird (Rekursion, vgl. 5. Vorlesung) SELECT R1.*, R2.* FROM R AS R1 INNER JOIN R AS R2 ON R1.A = R2.D Um zweimal dieselbe Tabelle benutzen zu können, bekommt sie zwei verschiedene Alias-Namen TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 35 R A B C D 1 2 3 4 4 5 6 7 R1 R2 7 8 9 0 A B C D A B C D 1 2 3 4 1 2 3 4 4 5 6 7 4 5 6 7 7 8 9 0 7 8 9 0 SELECT R1.*, R2.* FROM R AS R1 INNER JOIN R AS R2 ON R1.A = R2.D 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 TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 36 SQL: SELECT - (RELATIONALE) DIVISION Mit der Division filtert man alle Teilrelationen einer Relation aus, die zu allen Tupeln einer zweiten Relation in Beziehung stehen. SELECT * FROM T DIVIDEBY S T R S T S R 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. TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 37 T 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 E F G 1 2 3 4 7 8 9 1 2 3 4 5 6 7 7 8 9 7 8 9 7 8 9 0 7 8 9 8 1 3 1 2 3 4 8 1 3 4 5 6 7 8 1 3 7 8 9 0 8 1 3 S SELECT * FROM T DIVIDEBY S R A B C D 1 2 3 4 4 5 6 7 7 8 9 0 TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 38 SQL – FREMDSCHLÜSSELBESCHRÄNKUNGEN Fremdschlüsselbeschränkungen regeln die Maßnahmen bei Veränderungen in der "Elterntabelle", auf die durch Fremdschlüssel aus der "Kindtabelle" verwiesen wurde FOREIGN KEY (Spaltenname1) REFERENCES tbl_name (Spaltenname2, ...) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}] 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. TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 39 SQL – FREMDSCHLÜSSELBESCHRÄNKUNGEN 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. TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 40 SQL – FREMDSCHLÜSSELBESCHRÄNKUNGEN 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.) TU Dresden - Institut für Baumechanik und Bauinformatik - Computeranwendung im Bauwesen Bauinformatik II, Softwareanwendunge 1; 1. Vorlesung Folie-Nr.: 41