Tags:
create new tag
, view all tags

Structured Query Language Notes

Introduction

IDEA!Kapitelangaben beziehen sich auf das Buch The Practical SQL Handbook (Third Edition) von Judith S. Bowman, Sanda L. Emerson und Marcy Darnovsky

  • SQL dient zum Erzeugen, Abrufen und Verwalten von Daten in einer Datenbank viele verschiedene Dialekts MOVED TO... alle ähnlich auf Grund von Druck durch Market MOVED TO... easy to adapt

  • Buch geht auf "industrial sql" ein MOVED TO... intuitive codeverwendung

Chapter 1 (SQL and Relational Database Management)

  • relationale Datenbanken versuchen mittels relationalen mathematischen Modellen an die Aufgaben heranzugehen

Codd's Rules

Alle Infos als Datentabellen speichern

  • Keine andere Möglichkeit Daten zu speichern als in Tabellen MOVED TO... Tabelle/Kollone/Zeile ( = Relation/Tuple/Attribute oder File/Record/Field)
  • entity: subject of the table (e.g adressbook)
  • colums: a characteristic of an entity (e.g name, adress)
  • row: occurrence of an entity (e.g person, enterprise)
  • value: data element, identified by row/column
  • primary key: Wert, die eine Linie charakterisiert, wenn in der Kolonne nach einem bestimmten Wert gesucht wird
    • IDEA! e.g column: address MOVED TO... primary key: Daniel Ambuehler MOVED TO... gesuchter Wert: Lättenweg 19
  • system Table: beschreiben die database MOVED TO... auch in Tabellenform gemäss Codd´s Rules!!!

Zusammenhänge getrennt von Daten speichern

  • physical data independence: ich kann den Speicherplatz von Daten ändern, ohne dass sich bei der Ansicht der Daten etwas ändert!!
  • logical independence: ich kann Abhängigkeiten zwischen Tabellen, Rowe, Collumns ändern, ohne dass Funktionen beeinträchtig werden!

Eine einzige "High-Level-Language" soll benutzt werden

  • MOVED TO... eigentlich gleichgesetzt mit SQL smile
  • Data Modifikation: Daten hinzufügen, entfernen, ändern (Modifikation ooperations; e.g insert, delet, update)
  • Data Retrieval: Spezifische Daten finden (suchbefehl = data retrieval operations = queries MOVED TO... they all begin with "select")
  • administration/datacontrol commands: koordinieren den gebrauch der datentabelle (z.B Zugriffserlaubnis)

Unterstützt Relational Operations & union/intersection/difference/division

  • relational operations: 3 operationen (projection, selection, join)
  • Projection: wählt eine Kollone aus, die einer gewünschten angabe entsprechen
  • Selection: wählt eine Zeile aus, die einer gewünschten angabe entsprechen
    • Join: bringt Daten in zusammenhängenden Tabellen zusammen (Beispiel: in einer Tabelle sind Bücher mit verschiedenen Daten gsammelt, in einer anderen Tabelle Autoren mit verschiedenen Daten. Man kann sich jetzt die Bücher mit zugehörigen Autoren in einer derieved Tabe anzeigen lassen. Es muss aber eine Schnittstelle (Daten die in beiden Tabellen enthalten sind) zwischen den beiden Tabellen geben; beispielsweise ID-Nummer der Bücher) MOVED TO... dieser Befehl ist sehr wichtig, weil Tabellen meist eine beschränkte Anzahl von Rows/Collumns haben können
  • nonprocedural language: man kann sagen, was man will, ohne zu sagen zu sagen wo es sich befindet smile
  • derieved Table: eigtl. eine virtuelle Tabelle -> wenn etwas abgefragt wird, wird dies wieder als Tabelle dargestellt (deshalb virtuell)

Anderweitige Ansichten von Daten in der Datenbank müssen unterstützt werden

  • die Ansichten in SQL können gestaltet werden wie gewünscht
      • MOVED TO... die tatsächliche Ansicht sagt aber nichts darüber aus, wie die Daten tatsächlich gespeichert werden!!

Differenzierung zwischen leer/null und unbekannt (Wert)

  • daten/datenbanken sollen für verschiedene Users sichtbar sein (nicht nur für den erstellet), in SQL können Authorities auf andere users übertragen werden. dies alles wird unter permissions/security verstanden
  • integrity: SQL sorgt dafür, dass keine Unbeständlichkeiten entstehen z.b durch abbrechen/fehlschlagen von Operationen (Prozess soll bis zum ende durchlaufen, oder abgebrochen werden...)
    • entity integrity: kein primary key soll einen null wert haben
    • referential integrity: wenn ein Value an mehreren Orten vorkommt und geändert wird, sollen gleich alle Orte erneuert werden (Beispiel Telefon Nr kommt in mehreren Tabellen vor MOVED TO... bei Änderung, will man die nur einmal ändern!)

Security, Integrity und Recovery

  • Es müssen Mechanismen für security (authorization), integrity und recovery angeboten werden

Chapter 2 (Designing Databases)

  • wie soll die Datenbank aussehen? (logical design MOVED TO... Verknüpfungen der verschiedenen Tabellen)
  • ALERT! wichtig: das logical design ist unabhängig vom physical storage und von der endgültigen view ALERT!

Normalization

  • wichtige "Regeln" für das Design von Databases. MOVED TO... versuchen die data integrity zu gewährleisten, durch Verhinderung von doppelten Daten. Dazu wird oftmals eine Tabelle in mehrere Tabellen aufgespilitet. ( MOVED TO... "non-loss decomposition")
    • MOVED TO... normalization rules werden oft angewendet, wenn man eine Vorstellung hat, wie die Tabellen/Rows/Collumns aussehen sollen und man prüfen will, ob das so Sinn macht. (ALERT! hilft nicht, sich diese Vorstellung zu verschaffen ALERT!)
  • es gibt 5 "weitverbreitet akzeptierte" normal forms. diese normal forms können als stufen angesehen werden und müssen auch in der Reihenfolge 1 - 5 umgesetzt werden (wenn die daten in der 3rd normal form sind, sind sie also auch in der 1st und 2nd normal form). Oftmals werden nicht alle Normal Forms umgesetzt
  • First Normal Form: Es gibt in jedem "Feld" der Tabell (also Kreuzung einer Spalte/Zeile) einen und nur genau einen Wert. (MOVED TO... Problem: Pro Versand Nr. werden Bücher mit Verschiedenen Buchnr. verschickt MOVED TO... Zu diesem Zweck kann die Tabelle in zwei aufgespaltet werden, wobei in der ersten nur die Versandnr mit Details gespeichert wird, in der zweiten Eine Liste mit Büchernr und der Dazugehörigen Versandnr.)
  • Second Normal Form: Jeder Wert in einer Reihe muss sich voll und ganz auf den Primarykey beziehen (Beispiel: Primarykey ist die Kombination aus AutorenNr und BuchNr, eine Spalte ist der Vertrag des Autors. Falls ein Buch mehrere Autoren hat und der Vertrag beide Autoren einbezieht, dann bezieht sich der Vertrag also nur teilweise auf den Primarykey.) Grundsätzlich ist es so, dass die 2 Normal Form nur zur Anwendung kommt, falls der Primarykey aus mehreren Reihen besteht.
  • Third Normal Form: Jede Non-Key Reihe darf ausschliesslich Informationen über den Primary-Key enthalten und darf nicht abhängig sein von anderen Non-Keys (Beispiel: Bei Bestellungen wird die Anzahl bestellter Bücher gespeichert. Wenn aber mehrere verschiedene Bücher bestellt werden, muss die Information über die bestellte Stückzahl pro Buch irgendwo anders her genommen werden --> gegen die Regel).
  • Forth Normal Form: verbietet unabhängige 1:N Beziehungen zwischen Primary Key Reihen und Non Key Reihen (Wenn Autos und Katzen von Personen in der gleichen Tabelle gespeichert werden, kann es so aussehen als gäbe es eine Beziehung zwischen Autos und Katzen. Wenn eine Person mehrere Autos hat und dann eines "gelöscht" werden muss, kann es sein, dass leere Zeilen entstehen. Deshalb speichert man besser die Autos und die Katzen in separaten Tabellen)
  • Fifth Normal Form: Sieht vor, die Tabellen in möglichst kleine Tabellen aufzuspalten um Repetitionen von Informationen zu verhindern. Tabellen der Fifth Normal form enthalten meist nur 1 bis 2 weitere Reihen neben der Primary Key Column. Wenn Informationen ändern, ist es relativ einfach dies zu updaten, weil die Info nur an einem Ort gespeichert ist, allerdings kann es mühsam sein die Tabellen immer wieder zu joinen. Zudem entstehen Schwierigkeiten, wenn sich Primary Keys ändern.

Entity-relationship Modeling

eine "Anleitung", zur Findung der Struktur der zukünftigen Datenbase ausgehend von den Daten (also was man vor der normalization macht) MOVED TO... ein mögliches vorgehen:

  • evalluieren: was für infos habe ich, woher, wer fügt ein/hinzu, was könnte kritisch sein in Bezug auf Zeit/Verfügbarkeit? was muss produziert werden, für wen?
  • was für Themen (entities) habe ich (MOVED TO... ergeben tabellen), was für eigenschaften haben die einzelnen Objekte (MOVED TO... ergeben Spalten) MOVED TO... brauche ich noch mehr attribute?
  • datenstruktur entwerfen (data structure)
  • kontrollieren, ob es in jeder Tabelle ein attribut (Spalte) gibt, wo ein unverwechselbarer primary key verwendet werden kann (falls nicht muss evt. nochmals eine Spalte eingefügt werden. (note: namen zu verwenden ist heikel; es können mehrmals die gleichen vorkommen, Firmennamen werden oft unterschiedlich geschrieben, wenn von verschiedenen Personen eingefügt --MOVED TO... ID-Nrs sind gute Lösungen; bsp AHV-NR. Mitarbeiter-Nr...)
  • wie sind die entities miteinander verknüpft (one to many, many to many), können die Tabellen mit Foreign Keys verknüpft werden)
  • dann folgen die normalization rules um das resultat bis hier hin zu überprüfen
  • database online stellen und das resultat nach zufriedenheit beurteilen

was ist ein gutes database design:

  • zusammenhänge sind einfach zu verstehen
  • consistency ist gewährleistet
  • ermöglicht eine hohe performance
  • ALERT!important: die anzahl columns ist wichtig: viele Spalten machen die Tabelle unleserlich, aufspalten in viele Tabellen machen die Zusammenhänge schwierig zum erkennen. Zudem haben die Anzahl Spalten einen Einfluss auf die Leistung ALERT!

Foreign Key

  • eine Kollone mit der eine Tabelle mit einer anderen verknüpft werden kann. Je nach vebindungsvariante (1:1/1:N) müssen Foreign Keys anders gesetzt werden.

One to Many ("1-to-N" oder "1:N")

  • in eine richtung eindeutig (e.g ein Buch kann nur einen Verlag haben, aber ein Verlag kann mehrere Bücher haben). Dabei muss bei der Foreign Key Setzung darauf geachtet werden, dass er klug gewählt wird MOVED TO... bei diesem Beispiel ist es sinnvoll ein Attribut bei der Buchtabelle einzufügen mit der ID des Verlages. So können mehrere Bücher die gleiche Information in dieser Spalte haben (mehrere Bücher haben den gleichen Verlag). Würde in der Verlagtabelle ein Attribut für die Bücher eingefügt, müsste jedes Mal wenn ein neues Buch erscheint eine Zeile in der Buchtabelle plus ein Wert in der Spalte "Bücher" in der Verlagstabelle eingefügt werden ALERT! grosse FehlerquelleALERT!

Many to Many ("N-to-N" oder "N:N")

  • in beide Richtungen zweideutig (ein Autor kann mehrere Bücher schreiben, und ein Buch kann mehrere Autoren haben)
  • Das heisst es ist keine Primary Key - Foreign Key kombination zur Verbindung der beiden Tabellen möglich! Es muss eine Weitere Tabelle erstellt werden, die die Beiden Primary Keys (z.B Autoren Nr und Buch Nr) beinhalted und zusammenbringt. MOVED TO... Liste der Buch Nr. in erster Kollone, dazugehörige Autoren Nr in zweiter Kollone; Hat ein Buch 2 Autoren, werden 2 Zeilen mit der gleichen Buch Nr. erzeugt. MOVED TO... Als Primary Key der neuen Tabelle dient also die Kombination der beiden Kollonen; Es gibt immer nur ein Objekt mit bestimmter Autoren Nr und Buch Nr... Die Beziehung zwischen der einen Tabelle (z.B Autorentabelle) und der Verbindungstabelle ist demnach wie 1:N

One to One ( "1-to-1" oder "1:1")

  • diese Beziehung wird eher selten verwendet, weil man die Informationen auch gleich in die gleiche Tabelle tun könnte. Es kann aber trotzdem Sinn machen, wenn man Informationen hat, die selten verwendet werden (z.B Copyright, anzahl Seiten von Büchern). Durch Aufspaltung der Tabellen kann man so evt die Performance verbessern (das macht man aber eigentlich nur, wenn man sehr sicher ist mit dem Umgang seiner Datenbank)

Chapter 3 (Creating and Filling a Database)

wichtig für das programmieren:

  • theoretisch kann man zeilenumbrüche machen, wo man will, allerdings sollte man auf eine geeignete Darstellung achten
  • Bezeichnungen sind normalerweise Case-sensitive und werden grundsätzlich klein geschrieben.
  • SQL Befehle sind nicht Case-sensitive und werden grundsätzlich alles gross geschrieben.
  • 4 Dinge, die eine Tabelle immer haben muss: Name, Name der Reihen, Datentypen der Kollonen, ob der NULL-Status akzeptiert wird in der Reihe
  • Wahl der Datentypen ist sehr wichtig; so können beispielsweise int durch mathematische Operationen verändert werden, was bei chars natürlich nicht der Fall ist.
    • char: Strings mit bestimmter Länge (z.B Telefonnr werden typischerweise als chars abgespeichert!! MOVED TO... wenn als Zahlen können beispielsweise 0-s am Anfang verloren gehen)
    • varchar: String mit undefinierter Länge -> als eigentlich ein Text (bspsweise eine Beschreibung)
    • Ganzzahlige datentypen (int, number, smallint): ganzzahlige Zahlen, rechenoperationen und Funktionen wie max/min sind möglich
    • Decimal Datentypen (real, double, float): Zahlen mit Nachkommastellen, je nach Datentyp unterschiedliche Genauigkeit
    • Money Datentypen: für Währungen, nicht alle Systeme verfügen über MD (MOVED TO... dann decimals benützen)
    • Date & Time: Zeiten, Daten, es können teilweise auch Rechenoperationen ausgeführt werden (MOVED TO... Zeitdifferenzen berechnen)
    • Binary Datentypen: e.g Bilder

IDEA! decimal(6,3) reserviert Speicherplatz für eine Dezimalzahl mit 6 Stellen vor, und 3 Stellen nach dem Komma

nochmals zu "NULL": für jede Spalte muss entschieden werden ob es erlaubt sein soll eine NULL-Angabe zu machen (hängt von der Situation ab, ob das vorkommen kann). Primary-Keys sollen nie NULL-Angaben erlauben, bei Foreign-Keys kann es sein, dass es nötig ist.

Indexes

  • Indexes sollen die Performance verbessern. Ein Index funktionniert ähnlich wie ein Inhaltsverzeichnis eines Buches, es sagt dem Leser, wo sich gewisse Informationen befinden (-> schnellerer Zugriff möglich). Im Allgemeinen lohnt es sich nur Reihen zu indexieren, welche häufig abgefragt werden (Primary Keys, Foreign Keys, häufig abgefragte Attributes). Das ändern von indexierten Daten dauert länger.
    • composite index: Ein Index, der sich aus mehreren Spalten zusammensetzt (bspsweise Vor/Nachname in der Autorentabelle)
    • unique index: in Reihen mit unique index, wird jeder Wert nur einmal erlaubt (MOVED TO... macht Sinn bei Primary Keys), wird ein weitere Zeile mit dem gleichen Wert in der indexierten Reihe eingefügt, erscheint eine Fehlermeldung.
    • clustered index (nicht in allen database managements verfügbar): Sortiert die Zeilen der Tabelle immer gleich wie sich physikalisch gespeichert werden. MOVED TO... dadurch kann immer nur eine Reihe clustered indexiert werden. (macht dann sinn, wenn eine Reihe immer wieder durchsucht wird MOVED TO...; Bsp. primary key). Änderung dauern allerdings etwas länger, da die Zeilen immer neu sortiert werden müssen
  • indexieren lohnt sich nicht bei: kleinen Tabellen (System verzichtet eh auf das index), Reihen mit wenig Auswahl von Angaben (true, false, unknown; male,female,unknown)

Mit SELECT und INSERT können Daten von anderen Tabellen in kopiert werden, unter der Voraussetzung, dass die Datentypen, der zu kopierenden Reihen übereinstimmen. Es muss beim kopieren auf die Reihenfolge der Spalten geachtet werden. Kopierte Werte können (natürlich nur gewisse Datentypen) mit mathematischen Funktionen abgeändert werden, bevor sie in die neue Tabelle eingefügt werden. Es können auch nur einzelne Reihen in einer Zeile übernommen werden.

Mit UPDATE können bestehende Daten geändert werden.

UPDATE "DANIEL_TEST"."SALESDETAILS" 
SET TITLE_ID = 'PS2091' 
WHERE SONUM='1'

Mit DELETE können Zeilen gelöscht werden

DELETE 
FROM "DANIEL_TEST"."SALESDETAILS"
WHERE SONUM='1'

Mit WHERE können nur bestimmte Zeilen geändert werden. Wird der WHERE-Clause weggelassen, werden alle Zeilen geändert.

Chapter 4 (Selection Data from Database)

Gesucht wird grundsätzlich mit:

select ROW
from TABLE
where PRIMARY_KEY_VALUE

  • mit select * werden alle Reihen der Tabelle angezeigt (kann auch mit where kombiniert werden)
  • es können mehrere ROWs abgefragt werden MOVED TO... ROW1, ROW2
  • auch mehrere Tabellen sind möglich &M& TABLE1.ROW1, TABLE2.ROW2, from TABLE1, TABLE2
  • die Reihen erscheinen in der Reihenfolge, wie die ROWs unter select aufgeführt werden. Durch anfügen von "as NAME" können die Namen der Reihen in der Abfrage geändert werden.
  • Durch anfügen eines Strings nach dem select-Befehl kann eine Information erzeugt werden, die in jeder Zeile erscheint
  • MOVED TO...um einen Appostroph zu erzeugen braucht es zwei davon MOVED TO... einer der sagt, dass hier noch nicht fertig ist mit dem String und ein zweiter, der wirklich einen Appostroph erzeugt!
  • Sofern die Datentypen es erlauben (wenn nur Zahlen funktionnierts aber auch mit CHAR!) kann nach ROW eine Arithmetische Operation eingefügt werden (MOVED TO...QTY_SHIPPED*2; es wird überall die doppelte Menge angzeigt). Es können auch Rows als Faktoren, Summanden etc verwendet werden.
  • Mit < > können auch Strings verglichen werden -> "<" bedeutet vorher im Alphabet. Allerdings scheinen alle Grossbuchstaben zuerst zu kommen.
  • Mit BETWEEN kann ein ähnliches Resultat wie mit X<a AND X>b erziehlt werden. Allerdings werden bei BETWEEN die Grenzen mitgezählt. Bei NOT BETWEEN ist dies nicht der Fall. (= wie X<b or X>a)
  • Mit IN können mehrere Bedingungen verknüpft werden: WHERE STATE in ('CA', 'UT', 'NV') bedeutet es werden alle Zeilen angezeigt, die in der Spalte STATE CA, UT, oder NV aufweisen.
  • Mit IN können auch sogenante Subqueries abgerufen werden, das heisst es wird in einem select ein weiteres select abgerufen. (Siehe Codebeispiel subquery)

AGAIN NULL: NULLS werden bei Queries wie <5000 oder >5000 nicht aufgeführt, weil NULL weder kleiner noch grösser 5000 ist, sondern eben NULL!!! NULL Werte können aber auch abgefragt werden: where ADVANCE is NULL

  • Mit LIKE kann nach Zeichensequenzen gesucht werden (Bsp: Alle Buchtitel die "Computer" enthalten)
  • % vor oder nach dem Wort bedeuted, dass dort eine beliebige Anzahl Zeichen stehen können (0 bis N)
  • _ bedeutet, dass an dieser Stelle ein Zeichen ist.

Chapter 5 (Sorting Data and Other Selection Techniques)

Um Queries übersichtlicher zu gestalten, ist es Sinnvoll die Resultate zu ordnen. Bei Alphabetischer Ordnung hängt das Suchresultat stark vom System ab (Umgang mit Sonderzeichen, Gross/Kleinbuchstaben etc). Eventuell lässt sich das Einstellen.Die Sortierte Tabelle kann noch verfeinert sortiert werden: man kann z.B innerhalb der Bücher mit gleichen Preisen noch nach Autor sortieren. Dadurch sind alle Bücher eines Autors mit dem gleichen Preis zusammen aufgelistet. (ALERT! Durch umdrehen der Reihenfolge der ROWS entsteht ein anderes Resultat MOVED TO... Es wird dann beispielsweise zuerst nach Autor und dann nach Preis sortiert).

  • Durch anfügen von DESC, ASC nach dem Row-Name kann die Sortierreihenfolge verändert werden.
  • Bei modifizierten Rows (ROW1*ROW2) wird durch angeben der Spaltenzahl (von links her gezählt) angegeben, nach welcher Spalte sortiert werden soll.
  • NULLS werden je nach System unterschiedlich gehandhabt. (In Oracle werden sie höher als all Nicht-NULLS einsortiert)
  • Mit ALL (eingefügt vor dem ROW-Name) werden alle Werte einer Row angezeigt. Das heisst die Funktion macht eigentlich das, was standartmässig geschieht. Mit DISTINCT kann erreicht werden, jeder Wert der in einer Row vorkommt nur einmal angezeigt wird.

Aggregates sind eigentlich Funktionen, mit denen Mittelwerte, Summen, Maxima, Minima etc. bestimmt werden können. Spannend ist vorallem die Funktion count, mit der Werte gezählt werden können

  • ALERT! NULLS werden von Count nicht erfasstALERT! Ausnahme bildet der Befehl Count(*)

Chapter 6 (Grouping Data and Reporting from it)

Mit GROUP BY kann das COUNT-Aggregate so eingesetzt warden, dass die Vorkommen einzelner Werte gezählt werden und einzel aufgelistet werden:

select state, count (state)
from authors group by state

Mit diesem Befehl wird beispielsweise jeder Staat einmal aufgelistet und daneben angefügt, wievielmal dieser Staat in der ROW State vorkommt.

ALERT! Achtung: Grouped by findet NULLS und listet sie auf. Damit aber auch aufgelistet wird, wie viele NULLS es gibt muss mit count(*) gearbeitet werden!! (count(ROW) findet NULLS nicht!!!). Obwohl NULL = NULL, werden alle NULLS einer ROW unter derselben Group aufgeführt.

*GROUP BY macht eigentlich nur mit Aggregates (siehe Chpt 5) Sinn. Die Verwendung von GROUP BY ohne Aggregates liefert gleiche Resultate wie DISTINCT.

  • Wenn Groups nach bestimmten Kriterien aussortiert werden sollen, muss HAVING verwendet werden und nicht WHERE
  • ALERT!WHERE folgt auf FROM, und HAVING folgt auf GROUP BY ALERT!

Wenn man eine Information nicht hat, aber man weiss, was man ausschliessen kann (Ich kenne ein Geburtsdatum nicht, aber ich weiss, dass es 1990 im August war...), kann das nicht so in die Datenbank eingetragen werden. Eine solche Information nennt man distinguished null.

Chapter 7 (Joining Tables for Comprehensive Data Analysis)

Durch joinen von Tabellen können Informationen aus verschiedenen Tabellen zusammengefügt werden. Vorallem bei Databases die normalized wurden ist dies wichtig, weil die Informationen, z.B über einen Autor auf verschiedene Tabellen aufgeteilt wurden. Gute Möglichkeiten für Joins bieten Key-Rows. Zum einen ist dort die Vollständigkeit der Daten meist gewährleistet, zum anderen sind diese Schnittstellen vom Designer der Datenbank vorgesehen. Für Joins müssen nicht nur die Datentypen übereinstimmen (int to int e.g) sondern auch vom Inhalt her müssen die Rows gleich sein (es macht nicht sehr viel Sinn, das Gewicht Personen mit dem Alter von Autos zu joinen, auch wenn beides ints sind)

  • NULLS werden niemals gejoint, da ein NULL niemals gleich einem anderen Wert sein kann MOVED TO... keine Übereinstimmung!

Bei Joins ist es sinnvoll Aliases (kurzer name als Abkürzung für eine Tabelle z.B) zu verwenden, damit man nicht immer den ganzen Tabellennamen schreiben muss und man trotzdem die Tabellen ROWS exakt zuweisen kann:

        select t.title, e.editor_lname, e.editor_fname
        from titles t, editors e
        

  • Man kann joins auch mit anderen logical operations als "=" machen (z.B =, <, >=), man spricht dann von „theta joins".
  • Tabellen können auch mit sich selber gejoint werden (self join), beispielsweise um herauszufinden ob eine bestimmte eigenschaft auf mehrere Zeilen zutreffen. Dabei muss aber beachtet werden, dass eine ROW auch immer mit sich selber verglichen wird -> man muss dieses Resultat abfangen.
  • Durch joins können auch mehr als 2 Tabellen verknüpft werden. Dies ist vorallem für Many-to-Many Beziehungen wichtig, da dort Informationen der einen Tabelle mittels Zwischentabelle mit einer anderen Tabelle verknüpft werden. (Siehe Codebeispiele für schönes Join Beispiel mit mehreren Tabellen).

Es kann vorkommen, dass man eine join hat, bei der nur eine bestimmte Anzahl Rows eine mögliche Verbindung haben (in einer Tabelle Personen mit Geb. datum, Haustiere etc, in einer anderen Tabelle Personen mit mail-adressen). Wenn ich die joinen will, wird es Leute ohne Mailadresse geben. Wenn ich aber dennoch alle Leute aus der ersten Tabelle anzeigen lassen möchte, brauche ich einen outer join. Dabei kann ich sagen, ob alle Rows aus der ersten (linken) Tabelle, oder alle Rows aus der zweiten (rechten) Tabelle in der Query enthalten sein sollen (left/right outer join). Outerjoins sehen folgendermassen aus: where a.city = p.city (+)

ALERT!Achtung, das Plus steht in Oracle auf der Seite, wo nicht alle Rows enthalten sind (MOVED TO...; left outer join)ALERT!

Wie werden Joins gemacht?

  • Zuerst werden alle möglichen kombinationen aufgeschrieben (kartesisches Produkt):
Tabelle a, ROW 1: a, b Tabelle b, ROW 1: c, d

ROW 2: c, d Tabelle b, ROW 2: e, f

Kartesisches Produkt: a, b, c, d

a, b, e, f

c, d, c, d

c, d, e, f

  • durch das joinen (where-clause) werden die ROWs herausgefiltert die Sinn machen (z.B where COLUMN1 = COLUMN3)
MOVED TO... Join-query: c, d, c, d

Eine weiter Möglichkeit Daten zu verbinden ist UNION. Es wird nicht wie beim joinen nach Schnittstellen von Tabellen gesucht, sondern einfach Reihen von verschiedenen Tabellen (die Datentypen müssen natürlich kompatibel sein) in einer Reihe in einer neuen Tabelle angezeigt (z.B Vor/Nachnamen von Autoren, Editoren etc). ALERT!UNION löscht doppelte Zeilen à um alle Zeilen zu sehen muss ALL vor UNION eingefügt werden.ALERT!

Mit UNION kann eine Art switch-case eingebaut werden. Die einzelnen Cases können durch verschiedene WHERE-clauses aufgetrennt werden. Anschliessend kann man sich mit Union die Resultate in der gleichen Tabelle anzeigen lassen. Siehe Codebeispiel.

Chapter 8 (Structuring Queries with Subqueries)

Ein Subquery (oder nested query) ist eigentlich eine Abfrage in einer Abfrage. Dabei unterscheidet man zwei verschiedene Subqueries:

  • Noncorrelated subquery: die Subquery liefert einen Wert an die „obere Etage", welche diese weiter verarbeitet.
  • Correlated subquery: Die Subquery bekommt bereits einen Wert von der „oberen Etage" verarbeitet diesen und liefert einen neuen Wert zurück.

Bei der Information die zurück geliefert wird gibt es 3 Typen:

  • 0 - N Werte werden zurück geliefert (eigtl. eine Liste von Werten): Subqueries werden mit IN, NOT IN, ALL, ANY eingeleitet
  • Ein einziger Wert wird zurückgeliefert: Subqueries eingeleitet mit = à wenn ich nur Leute haben will, die aus Calefornia kommen: where city = (...)
  • Zurückgeliefert wird ein Boolean, z.B exists

ALERT!häufig können Lösungen mit Subqueries auch mit Join gelöst werden ALERT!

Correlated Queries sind oft nicht so schön, denn dadurch, dass das Subquery informationen von der „oberen Etage" braucht, muss der Code mehrmals durchlaufen werden. Manchmal lässt sich aber in Problem nicht anders lösen. Mit Subqueries können nur Reihen angezeigt werden, welche in der oberen Query definiert werden MOVED TO... sonst muss ein Join angewendet werden.

  • Wo sich das Subquery auf die gleiche Tabelle wie die upper Query bezieht, ist ein Self-join meist sinnvoller.
  • Mit Subqueries kann man GROUP BY und HAVING nicht verwenden, ausser die Subquery liefert nur einen einzigen Wert zurück!
  • Mit ALL können alle Items einer Liste mit IN verglichen werden:

MOVED TO... ALL (2,5,7) à es werden Items gesucht, die grösser als alle diese Zahlen sind (MOVED TO... also 7) IDEA!= ALL(1,2,3) alles gleichzeitig (so nicht sehr sinnvoll) Mit ANY wird das gegenteil erziehlt: MOVED TO... ANY (1,5,3) à es werden Items gesucht, die kleiner sind alls eine von diesen Zahlen (MOVED TO... also 5) = ANY(1,4,7) Wert muss einer dieser Zahlen entsprechen (MOVED TO... gleichwertig mit IN(1,4,7) ABER = ANY (1,3,5) heisst nicht 1, oder nicht 3, oder nicht 5 (MOVED TO... macht keinen Sinn, nichts ist gleichzeitig 1,3 und 5 à immer true) !!

ALERT!achtung, wenn in der Liste ein NULL vorkommt, wird >/< ALL/ANY nichts zurückgeben (was ist grösser/kleiner als NULL?!)

Mit EXISTS/NOT EXISTS können Reihen angezeigt werden, wo beispielsweise eine Information in beiden Tabellen vorhanden, oder nicht vorhanden ist (Wenn ich beispielsweise eine Liste von Autoren will, die kein Buch eingetragen haben (-> also keinen Eintrag in der Tabelle der Bücher hat), kann ich mit

WHERE NOT EXISTS(….
   WHERE AUTHORS.AU_ID=TITLES.AU_ID)

Spezifisch diese Titel abfragen

  • Falls notwendig können auch mehrere Subqueries in einander verschachtelt werden!
  • Im Zusammenhang mit UPDATE, DELET, INSERT können ebenfalls Subqueries verwendet werden: Die Angabe, was gelöscht, eingefügt, oder verändert werden soll, ist dann die Subquery (where (select….))

-- DanielAmbuehler - 2010-12-16

Topic revision: r8 - 2010-12-22 - danieamb
 
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2017 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback