SQL-schüler
Werbung
Einführung SQL Einführung SQL Schülerversion November 2002 Haas/Resch 1. ZIEL DER LVA ................................................................................................................................................ 3 2. EINFÜHRUNG DATENBANKSYSTEM ....................................................................................................... 3 2.1. ANALOGIE ZUR DATENMODELLIERUNG ........................................................................................................ 4 2.2. EIGENSCHAFTEN EINER RELATIONALEN DATENBANK SIND: .......................................................................... 4 2.3. INTEGRITÄTSBEDINGUNGEN .......................................................................................................................... 5 2.3.a. Semantische Integrität ........................................................................................................................... 5 2.3.b. referentielle Integrität ........................................................................................................................... 5 2.3.c. operationale Integrität........................................................................................................................... 6 3. DREI SCHICHTENMODELL EINES DATENBANKSYSTEMS .............................................................. 6 3.1. LOGISCHE SCHICHT (EXTERNES SCHEMA) ..................................................................................................... 6 3.2. KONZEPTIONELLE SCHICHT (KONZEPTIONELLES SCHEMA)............................................................................ 6 3.3. PHYSISCHE SCHICHT (INTERNES SCHEMA) .................................................................................................... 7 4. WAS IST NUN SQL?........................................................................................................................................ 7 4.1. DDL (DATA DEFINITION LANGUAGE) .......................................................................................................... 8 4.2. DQL (DATA QUERY LANGUAGE).................................................................................................................. 8 4.3. DML (DATA MANIPULATION LANGUAGE) ................................................................................................... 8 4.4. DCL (DATA CONTROL LANGUAGE) .............................................................................................................. 8 4.4.a. Transaktionslogik .................................................................................................................................. 8 4.4. b. Zugriffsrechte ....................................................................................................................................... 8 5. TABELLEN ANLEGEN .................................................................................................................................. 9 5.1. 1:N BEZIEHUNG ........................................................................................................................................... 9 Prüfen der Tabellen und Beziehungen .................................................................................................... 12 Eingeben der Daten über "Datenblattansicht"........................................................................................ 12 Erstellen eines einfachen Formulares ..................................................................................................... 12 Eingeben von Daten für die Schüler ........................................................................................................ 13 Erstellen eines einfachen Berichtes ........................................................................................................ 13 5.2. M:N BEZIEHUNG ......................................................................................................................................... 14 5.3. GENERALISIERUNG...................................................................................................................................... 15 5.3.a. Variante beide möglich: ...................................................................................................................... 15 5.3.b. nur eines von beiden ............................................................................................................................ 16 6. SELECT ........................................................................................................................................................... 17 6.1. EINFACHE ABFRAGEN.................................................................................................................................. 17 6.2. VERKNÜPFUNG............................................................................................................................................ 19 6.3. AGGREGATFUNKTIONEN ............................................................................................................................. 20 6.4. RECHENFUNKTIONEN .................................................................................................................................. 21 6.5. DATUMSFUNKTIONEN ................................................................................................................................. 21 6.6. STRINGFUNKTIONEN ................................................................................................................................... 21 6.7. GRUPPIERUNG ............................................................................................................................................. 21 6.8. KARTESISCHES PRODUKT (KREUZPRODUKT) ............................................................................................... 24 6.9. INNER (EUQI-) JOIN .................................................................................................................................... 25 6.10. OUTER / LEFT / RIGHT JOIN ....................................................................................................................... 26 6.11. SELF JOIN................................................................................................................................................. 27 6.12. SUB - SELECT......................................................................................................................................... 27 6.12.A. IN Operator ...................................................................................................................................... 28 6.12.B. unsynchronisertes / synchronisiertes SELECT ................................................................................. 28 6.12.C. EXISTS.............................................................................................................................................. 29 6.12.D. ANY (irgendeiner) ............................................................................................................................ 30 6.12.E. ALL ................................................................................................................................................... 30 -1- Einführung SQL 6.13. UNION ..................................................................................................................................................... 31 A. DATENMODELL SCHULDATENBANK .................................................................................................. 33 -2- Einführung SQL 1. Ziel der LVA Bis jetzt haben wir uns mit Datenmodellierung beschäftigt. Nun wollen wir den Übergang in die eine konkrete, verwirklichte Datenbank vollziehen. Den Anfang macht hierbei das Abfragen von Daten und erst später werden wir in Access Tabellen selbst anlegen und Daten ändern. 2. Einführung Datenbanksystem Bevor wir uns auf das konkrete SQL stürzen, möchte ich noch einige Überlegungen zu den Begriffen und der Denkwelt anstellen. Wir haben uns schon zu Beginn von ADAT mit der Frage beschäftigt: Was ist überhaupt eine Datenbank? Überall dort, wo es um die Aufbewahrung oder Verarbeitung von großen Datenmengen geht, sind Datenbanksysteme im Spiel. Diese "Informations - Lager" sind darauf spezialisiert, den Umgang mit großen Datenmengen zu vereinfachen. Dazu wird meist eine standardisierte Schnittstelle (SQL, ...) zur Verfügung gestellt. Ein Datenbanksystem besteht aus der Datenbasis und dem Datenbankmanagementsystem. Die Datenbasis umfasst die eigentlichen Nutzdaten der Anwender aber auch Daten zur Steuerung und Verwaltung des Systems (Berechtigungen, ...). Unter dem Datenbankmanagementsystem sind alle die Funktionen zusammengefasst, die zum Beschreiben, Speichern und Wiedergewinnen von Daten benötigt werden. Ein Beispiel für ein solches Datenbanksystem ist etwa Microsoft Access. In der Datenbasis werden Daten in Form von Datenbanken gespeichert. Eine Datenbank ist eine Ansammlung von Daten, zum Beispiel im betrieblichem Umfeld. Die Daten in einer Datenbank werden in Tabellen gespeichert. Jede Tabelle besteht aus Zeilen (Records) und hat eine genau festgelegte Anzahl von Spalten (Feldern). Jedes Feld hat einen ganz konkreten Datentyp. Das kommt uns doch sicher nicht unbekannt vor ... -3- Einführung SQL 2.1. Analogie zur Datenmodellierung Ich möchte nun ganz konkret den Schritt vom Datenmodell zur Datenbank (bzw. den Tabellen) vorzeigen. Bei der Datenmodellierung haben wir von Entitäten, Attributen und Beziehungen gesprochen. Eine der möglichen Notation (Schreibweisen) ist das Relationenmodell: Personen SozVersNr L01 L02 L03 … Name Eva Peter Susan GebDat 1.1.82 13.5.85 8.9.91 Geschlecht f m f Das ist genau die Denkweise, wenn wir mit einem Datenbanksystem arbeiten: Wir sehen: Tabellen (das waren unsere Entitätsmenge bzw. Relationenschema = Summe aller Attribute) Namen der Tabelle (Name des Relationenschemas) Felder (das waren unsere Attribute) Zeilen oder Records (das waren unsere Entitäten) Primärschlüssel gibt es in beiden Denkwelten (verpflichtend) Neu hinzu kommt der Datentyp eines Feldes (Attributes). Bei der Datenmodellierung ist uns egal gewesen, welche Datentypen wir in einem Attribut modellieren wollen. Wir erinnern uns: ERWIN ermöglicht die Eingabe von zusätzlichen Eigenschaften zu den Attributen - das sind genau diese Datentypen um die es hier geht. Beispiele für Datentypen sind: Integer, String, Fließkommazahl, usw. 2.2. Eigenschaften einer relationalen Datenbank sind: kompakte Speicherung (kein unnützes Belegen von Speicherplatz) keine Redundanz (ein und das selbe Faktum sind mehr als einmal gespeichert) und damit garantierte Integrität der Daten Beispiel: Mitarbeiterdaten, Abteilungsnummer und -Bezeichnung kommt bei jedem Mitarbeiter vor. Wenn die Bezeichnung der Abteilung geändert wird, dann müssen alle Datensätze durchsucht und die Abteilung geändert werden Ein und die selbe Entität darf nicht zweimal gespeichert werden (laufende Nummer beim Schlüssel!) Alle Teilschritte einer Änderungen müssen zu einem Zeitpunkt vollständig oder überhaupt nicht durchgeführt werden (Transaktion) - Beispiel Abbuchung von einem Konto auf ein anderes: TAN (Transaktionsnummer beim TeleBanking) Kein Zugriff auf Daten außer durch "Bordmittel" des Datenbanksystems Integrität -4- Einführung SQL 2.3. Integritätsbedingungen 2.3.a. Semantische Integrität Die semantische Integrität steht für die Korrektheit der Datenbankinhalte. Dabei gilt es falsche Eingaben oder unzulässige Änderungen abzufangen. Zum Zeitpunkt der Datendefinition müssen dem Datenbanksystem die Integritätsregeln mitgeteilt werden. Daneben besteht aber auch noch die Möglichkeit im Anwendungsprogramm bestimmte Kontrollen einzubauen. So können für einzelne Attribute ein bestimmter Wertebereich angegeben werden. In MS ACCESS haben Sie im Tabellenentwurf durch die Wahl eines passenden Felddatentyps, durch die Festlegung von Gültigkeitsregeln sowie die Verwendung von Nachschlageassistenten die Möglichkeit anzugeben, welche Anforderungen die Eingabedaten erfüllen müssen. Für die Erhaltung der Korrektheit der Beziehungen zwischen Attributen einer Relation ist es wichtig, dass der Schlüssel eindeutig ist (Verwendung eines Primärschlüssels). Nur so ist es möglich, einen Datensatz eindeutig zu identifizieren. 2.3.b. referentielle Integrität Die referentielle Integrität befasst sich mit der Korrektheit von Beziehungen und stellt sicher, dass Änderungen in der Datenbank nicht dazu führen, dass Fremdschlüssel auf nicht mehr vorhandene Datensätze verweisen. Um zu diesem Fenster zu kommen, muß man die Tabelle anklicken. Dann den Knopf oder Extras/Beziehungen im Menü. Wenn es schon eine Beziehung gibt, dann einfach die Beziehung anklicken und rechte Maustaste und "Beziehung bearbeiten". Dann ist folgendes Popup zu sehen: -5- Einführung SQL 2.3.c. operationale Integrität Die operationale Integrität bezieht sich auf das korrekte Verhalten der Anwendung. Um zu verhindern, dass Datenbankinhalte durch systembedingte Fehler verfälscht werden, wurden sogenannte Transaktionen eingeführt. Benutzerzugriffe auf die Datenbank erfolgen über Transaktionen, die einerseits nur Daten abfragen, andererseits aber auch Daten ändern beziehungsweise löschen können. Transaktionen sind eine logische Folge von Datenbankoperationen, die in Gesamtheit die gültigen Konsistenzbedingungen erfüllen. Transaktionen, die Änderungen in der Datenbank bewirken dürfen nur in Gesamtheit durchgeführt werden oder gar nicht. Daher werden diese auch als atomar bezeichnet. Kommt es während der Durchführung einer Transaktion zu einem Systemfehler, muss der Zustand der Transaktion auf den Beginn zurückgesetzt werden. Die Transaktionsverarbeitung ist heute bereits fester Bestandteil von Datenbanksystemen. Greifen mehrere Benutzer zugleich auf eine Datenbank zu ist die Konsistenzsicherung jedoch wesentlich komplexer als oben beschrieben. Sperrmechanismen sind eine Möglichkeit Zugriffskonflikte zu vermeiden. Beispiel: Überweisung auf ein Konto. 3. Drei Schichtenmodell eines Datenbanksystems In der Einführung zur Datenmodellierung haben wir uns schon mit dem Drei - Schichtenmodell beschäftigt: Das 3-Schema-Konzept wurde 1975 vom Standard Planning and Requirements Comitee (SPARC) und dem American Standards Comitee on Computers and Information Processing (ANSI/X3) als Vorschlag für die Standardisierung von Datenbankarchitekturen entwickelt. Die Beschreibung der Daten erfolgt auf drei verschiedenen Ebenen, die unterschiedliche Sichtweisen auf die Daten ermöglichen und gleichzeitig für Datenunabhängigkeit sorgen. logische Schicht konzeptionelle Schicht physische Schicht 3.1. logische Schicht (externes Schema) Das externe Schema ist die Sicht des Benutzers auf die Daten. Unter einem externen Schema versteht man anwendungsbezogene Sichten auf Teilbereiche des konzeptionellen Schemas. Dem Benutzer werden dabei die Daten in einer für ihn geeigneten Form zur Verfügung gestellt. Beispiel: In einer Datenbank sind Personaldaten gespeichert. Bestimmten Mitarbeitern darf man nur den Name und die Anschrift der Personaldaten zugänglich machen, während anderen Mitarbeitern der Zugriff auf die gesamten Personaldaten möglich sein muss. Der Benutzer muss keine Kenntnisse über den internen Aufbau einer Datenbank besitzen. Für ihn ist es unerheblich, welche Daten in welchen Tabellen abgespeichert werden. 3.2. konzeptionelle Schicht (konzeptionelles Schema) Die konzeptionelle Schicht nimmt im 3-Schichtenmodell eine zentrale Stellung ein. Es wird jener Teil der realen Welt, der für eine Anwendung relevant ist, abgebildet. Das konzeptionelle Schicht stellt eine Gesamtsicht auf die Daten eines Unternehmens auf logischer Ebene dar, ohne -6- Einführung SQL darauf Rücksicht zu nehmen, wie die Anwendung später realisiert wird und welches Datenbankmanagementsystem zum Einsatz kommt. Auf konzeptioneller Ebene wird festgelegt, was in der Datenbank gespeichert werden soll, d.h. welche Daten gespeichert werden sollen, wie diese genau aussehen und wie die Daten untereinander in Beziehung stehen. Charakteristisch für das konzeptionelle Schema ist, dass es einen relativ stabilen Bezugspunkt im gesamten Unternehmensmodell darstellt. Änderungen der externen Sicht wie beispielsweise die Entwicklung neuer Anwendungsprogramme, berühren das konzeptionelle Schema nicht. Das war also das Thema der Datenmodellierung zu Beginn des Semesters! Nun benötigen wir aber ein Werkzeug, um das Datenmodell in einem konkreten Datenbanksystem zu implementieren! 3.3. physische Schicht (internes Schema) Das interne Schema ist die physikalische Speicherung der Daten auf den Datenträgern. Es wird der mögliche Zugriff auf die Daten, d.h. wie und wo die Daten gespeichert werden, definiert. Neben der Speicherung der Daten in einer zentralen Datenbank ist auch der Einsatz verteilter Datenbanken möglich. Vom internen Schema ist die Performance einer Datenbank sehr stark abhängig. Beispiel: Auf dieser Ebene wird festgelegt, in welcher Reihenfolge die Felder "Kundennummer", "Vorname", "Name" und "Ort" gespeichert werden und wie die Dateiorganisation aussieht. Wie ist die Datenbank (Dateiorganisationsformen, ...) realisiert? Wie werden die Daten auf einem Datenträger gespeichert? Welches Betriebssystem wird verwendet? Wie kann eine Abfrage möglichst schnell bearbeitet werden? Ein wichtiger Aspekt der Relationalen Datenbanken ist, daß die physische und die konzeptionelle Schicht NICHT von einander abhängen dürfen: Der Zugriff auf die Daten erfolgt nur mit Bordmitteln des Datenbanksystems, ein direkter Zugriff (von außen ist NICHT möglich!). Es ist zumeist nicht bekannt, wie das Datenbanksystem die Daten abspeichert! Wenn die physische Schicht ausgewechselt wird (SAP: unterschiedliche Datenbanksysteme erlaubt), dann funktionieren alle Abfragen etc. ohne Änderung - die konzeptionelle und externe Schicht sind davon NICHT betroffen! Mit Methoden zur Datenspeicherung werden wir uns bei den Datenträgern und der Datenspeicherung beschäftigen! 4. Was ist nun SQL? SQL ist die Abkürzung zu Structured Query Language o o o SQL ist eine genormte Abfragesprache, allerdings hat jeder Hersteller einen eigenen Dialekt. Sprache der 4. Generation. Die Zielrichtung ist hier: "Was möchte ich haben?" Bei prozeduralen Sprachen (C/C++/Java) muß der Entwickler den Algorithmis im Detail implementieren und sein gewünschtes Ergebnis zu erziehlen. SQL liefert im Regelfall eine Menge von Ergebnissen (nicht Eines) -7- Einführung SQL o Embedded SQL: Wenn SQL in einem Programm eingebaut wird, dann gibt es Hilfskonzepte um mit den Ergebnislisten fertig zu werden - in Access (VBA) gibt es Dynasets, SAP kennt "interne Tabellen", andere Sprachen verwenden einen "Datenbank Cursor". SQL hat Sprachelemente für folgende Aufgabengebiete: 4.1. DDL (Data Definition Language) Arbeiten mit der Struktur der Daten (Datenbank, Tabelle) CREATE DATABASE CLOSE DATABASE DROP DATABASE CREATE TABLE ALTER TABLE DROP TABLE Datenbank anlegen eine Datenbank schliessen Datenbank (mit allen Tabellen und Daten) löschen Tabelle anlegen Tabellenstruktur ändern Tabelle mit Daten! löschen -> konzeptionelle Schicht Hinweis: Wir werden für unsere ersten Gehversuche Access verwenden. Access besitzt für diese Themengebiet bequeme, grafische Werkzeuge. Wir werden und mit diesen Werkzeugen aber erst später beschäftigen. 4.2. DQL (Data Query Language) SELECT Suchen / Abfragen von Daten aus Tabellen -> logische Schicht 4.3. DML (Data Manipulation Language) UPDATE INSERT DELETE Ändern von Daten in Tabellen Einfügen Löschen -> logische Schicht 4.4. DCL (Data Control Language) 4.4.a. Transaktionslogik Menge von Einzelschritten, die entweder alle vollständig oder gar nicht ausgeführt werden. Beispiel Internet-Banking: TAN (Transaktionsnummer) Abbuchen Konto A, Buchen auf Konto B -> Beide Buchungen müssen durchgeführt werden! COMMIT ROLLBACK 4.4. b. Zugriffsrechte GRANT REVOKE Rechte einräumen Rechte wegnehmen -> konzeptionelle Schicht -8- Einführung SQL 5. Tabellen anlegen 5.1. 1:N Beziehung Wir werden später mit dem Anlegen von Tabellen beschäftigen, für den Moment nur ein Beispiel: Erwin: Klasse.ER1 Mit ERWIN erzeugen wir nun ein SQL Script, daß die Tabellen wie gewünscht anlegt: Einstellungen ERWIN: Dazu müssen wir im Menü Display / physical Schema Level anwählen. Weiters muß im Menu Option / physical Schema auf "Informix" gestellt werden (ist ziemlich nahe an Access): Der Editor muß auf Informix Database Schema gestellt werden (Menü Editor). Nun können wir per Doppelklick die Datentypen zu den Attributen (Feldern) einer Entität (Tabelle) anpassen: Wenn der Feldtyp nur einmal vorkommt, dann können wir direkt einen Datentyp zuweisen: - Feld anklicken - Datentyp auswählen - Bei Datentyp mit Länge einfach Länge zwischen die runden Klammern einfügen Bei Fremdschlüsselbeziehungen werden wir das natürlich anders machen, da wir sonst den Datentyp der betreffenden Felder mehrfach eintippen müssen (Fehlerquelle!) - dazu gibt es den Knopf "UserDefined Datatypes" -9- Einführung SQL - Namen des Datentype eingeben - Datentyp as dem Pulldown Menü auswählen (ja, die Länge wieder zwischen die Klammern schreiben) Knopf "Insert" drücken Knopf "Exit" drücken Da es nun eigene Datentypen gibt, wird ein neues Feld am Bildschirm angezeigt: Wir wählen nun das Feld KlNr und den Datentyp DT_Kl_Nr (durch) anklicken aus. Fertig. Die zweite Entiät erbt diesen Datentyp automatisch mit! Erzeugen des SQL Scripts zum Anlegen der Tabellen: Im Menü Report/Informix Database Schema auswählen, - 10 - Einführung SQL Mit "Preview" wir das Script angezeigt, mit "Report" wird eine Datei mit dem Script erzeugt: DROP TABLE Klassen; CREATE TABLE Klassen (KlNr KlName PRIMARY KEY (KlNr) ); char(4) NOT NULL, CHAR(10), DROP TABLE Schueler; CREATE TABLE Schueler (SchNr char(5) NOT NULL, KlNr char(4), Name char(20), Vorname CHAR(15), PRIMARY KEY (SchNr), FOREIGN KEY (KlNr) REFERENCES Klassen CONSTRAINT gehört_zu ); Bei Access muß man zuerst eine neue Datenbank anlegen, oder eine existierende aufmachen. Datei / Neu, Datenbank, klasse.mdb im gewünschten Verzeichnis Abfrage anklicken, Neu / Entwurfsansicht / OK, Assistenen schließen, Ansicht SQL wählen, dann erschein das SQL Fenster. Hier geben wir den Code aus dem Generator ein (Cut and Paste): Dann muß man vier "Abfragen" anlegen - je eine für die Tabelle Klassen und Schueler. Aus logischen Gründen (Fremdschlüssel) muß die Klassentabelle vor der Tabelle Schueler angelegt werden. Das Anlegen per Script funktioniert wie bei SELECT: einfach das Script eingeben und den Knopf drücken. Die Tabelle wird kommentarlos angelegt - oder eine Fehlermeldung ausgegeben, falls sie schon vorhanden ist. Anmerkung: In Access ist das so gelöst, daß eine Datenbank immer eine eigene Daten (*.mdb) ist. Andere Datenbanksysteme speichern in einer einzigen Daten unterschiedliche Datenbanken ab. - 11 - Einführung SQL Der Zusatz "Constraint" geht so in Access nicht! Prüfen der Tabellen und Beziehungen Eingeben der Daten über "Datenblattansicht" Dazu wechselt man einfach auf das Objekt "Tabelle" und öffnet die gewünschte Tabelle mit einem Doppelklick. Beim Dreieck geben wir einfach Daten ein. Navigieren mit Cursur etc. Daten werden mit "Speichern" (Diskette) gesichert. Bei den Schülern wird das schon spannender (Fremdschlüssel - Integritätsbedingungen!) Die Klasse 3HDB gibt es nicht - was geschieht beim Sichern (bzw. gesichert wird beim Wechsel auf eine neue Zeile)? Super, das soll so sein! Erstellen eines einfachen Formulares Unter Objekte Formular wählen und "Neu" - 12 - Einführung SQL Automatisch wird nun eine nettere Oberfläche generiert: Daten eingeben (dort wo Dreieck steht) Bleistift, wenn Daten geändert worden sind (durch draufklicken wird gespeichert) Zeile markieren und "Entf" drücken (löschen eines Satzes): blättern mit den Pfeilchen Hinweis: Genauso kann man sehr einfach in C++ / VB (A) mit einem Data Control arbeiten! Eingeben von Daten für die Schüler Fremdschlüssel testen - OK, Primärschlüssel testen: Erstellen eines einfachen Berichtes - 13 - Einführung SQL 5.2. M:N Beziehung Erwin kann die M:N Beziehung NICHT selbst auflösen, darum haben wir uns ja geeinigt, daß wir die M:N Beziehungen schon im ER Diagramm in 1:N Beziehungen auflösen: Erwin: Bücher.er1 CREATE TABLE Buch (B_ISBN Titel PRIMARY KEY (B_ISBN) ); CREATE TABLE Schueler (S_Nr Name PRIMARY KEY (S_Nr) ); CHAR(13) NOT NULL, CHAR(30), CHAR(5) NOT NULL, CHAR(20), CREATE TABLE leiht_aus (S_Nr CHAR(5) NOT NULL, B_ISBN CHAR(13) NOT NULL, Datum CHAR(18), PRIMARY KEY (S_Nr, B_ISBN), FOREIGN KEY (B_ISBN) REFERENCES Buch CONSTRAINT R_4, FOREIGN KEY (S_Nr) REFERENCES Schueler CONSTRAINT R_3 ); Das war zu erwarten! - 14 - Einführung SQL 5.3. Generalisierung Diese Arte der Generalisierung bedeutet, daß ein Arzt auch Patient sein kann und umgekehrt! Hier darf eine Person nur entweder Arzt oder Patient sein (genau eine der Alternativen) Erwin: Generalisierung.ER1 Erwin: Generalisierung2.ER1 5.3.a. Variante beide möglich: CREATE TABLE Person (P# P_Name PRIMARY KEY (P#)); char(10) NOT NULL, CHAR(20), CREATE TABLE Ärzte (P# char(10) NOT NULL, Fachgebiet CHAR(18), PRIMARY KEY (P#), FOREIGN KEY (P#) REFERENCES Person); CREATE TABLE Patienten (P# char(10) NOT NULL, Krankheit CHAR(18), PRIMARY KEY (P#), FOREIGN KEY (P#) REFERENCES Person); CREATE TABLE Diagnose (P# char(10) NOT NULL, P# char(10) NOT NULL, Diagnose CHAR(18), PRIMARY KEY (P#, P#), FOREIGN KEY (P#) REFERENCES Patienten CONSTRAINT wird_beh, FOREIGN KEY (P#) REFERENCES Ärzte CONSTRAINT behandel); Die Idee, die ERWIN verfolgt ist klar: Personen Alle Personen (egal ob Arzt oder Patient) müssen in der Tabelle Personen enthalten sein Ärzte In der Tabelle Ärzte sind alle jene Personennummern eingetragen, die Ärzte sind Patienten In der Tabelle Patienen sind alle Personennummern eingetragen, die Patienten sind (ja, auch Ärzte - durch die Art der Generalisierung vorgegeben - 15 - Einführung SQL ACHTUNG: Den Vorschlag in der Tabelle Diagnose beide Personennummernfelder mit dem gleichen Feldnamen zu versehen ist natürlich KEINE gute Idee! 5.3.b. nur eines von beiden Erwin würde das gleiche Datenbank Script erzeugen - was aber völlig übertrieben ist, denn die Tabellen für Ärzte und Patienten sind in diesem Fall unnötiger aufwand: Es reicht eine Tabelle mit einer Personenart aus! Formuliert in ERWIN-Denkweise schaut das so aus: Erwin: Generalisierung3.ER1 Vorteil: Nachteil: eine Tabelle gespart Tatsache, daß eine Person ein Arzt ist, kann nur durch Programm geprüft werden. Bei der Eingabe durch einfache Bordmittel nicht prüfbar (Kann sein, daß Patient beim Arzt eingetragen wird!) CREATE TABLE Diagnose (P# char(10) NOT NULL, P# char(10) NOT NULL, Diagnose CHAR(30), PRIMARY KEY (P#, P#), FOREIGN KEY (P#) REFERENCES Person CONSTRAINT R_14, FOREIGN KEY (P#) REFERENCES Person CONSTRAINT R_13 ); CREATE TABLE Person (P# char(10) NOT NULL, P_Art CHAR(4) NOT NULL, P_Name CHAR(20), PRIMARY KEY (P#), FOREIGN KEY (P_Art) REFERENCES PersonenArt CONSTRAINT R_12 ); CREATE TABLE PersonenArt (P_Art P_Bez PRIMARY KEY (P_Art) ); CHAR(4) NOT NULL, CHAR(20), - 16 - Einführung SQL 6. Select Jetzt geht es aber los mit dem SELECT! Die Grundform ist sehr einfach: SELECT "welche Felder" FROM "welcher Tabelle" WHERE "Bedingung" AND / OR "Bedingung"; 6.1. einfache Abfragen Alle Felder und alle Zeilen der Tabelle select * from schueler; Zur Sicherheit alle Felder der Tabelle schueler (später für Join notwendig!) select schueler.* from schueler; Projektion (einzelne Felder aus der Menge der Tabellenfelder auswählen) select s_name, s_vorname from schueler; Überschriften bei der Ausgabe mit angeben select s_name as Name, s_vorname as Vorname from schueler; - 17 - Einführung SQL konstante Strings verwenden select s_name, "i", s_vorname as Vorname from schueler; Vermeiden von doppelten Werten select distinct s_vorname from schueler; Achtung: Die Reihenfolge der Records ist nicht vorhersehbar! Nur wenn ein Index über dem Datenfeld liegt, dann werden die Daten sortiert geliefert! select s_schueler, s_name from schueler where s_schuelernr > 50; Diese Daten werden sortiert ausgegeben, da s_schuelernr Primärindex ist. -> aufsteigend sortiert -> Werte nicht Null Sekundärindices können nach Wunsch angelegt werden (also mit/ohne Null, auf/absteigend,...) Hinweis: Ein Index ist ein Werkzeug, um die Suche in einer Tabelle zu beschleunigen. Wir werden uns bei der Datenspeicherung ausführlich damit beschäftigen. - 18 - Einführung SQL Menge der Zeilen einschränken select * from schueler where s_name = 'Huber' Die Bedingung kann mit =, <, >, <>, <= oder >= formuliert werden. 6.2. Verknüpfung Weiters sind Verknüpfungen mit "AND", "OR" und "NOT" möglich. (Beispiel Access) SELECT * FROM schueler s WHERE s.schu > 50 AND s.gebdat > #1/1/80#; "Raute" eingefasst In Access ist das Datum mit Einkommen zwischen 200 und 400 Lehreinheiten SELECT * FROM lehrer l WHERE l.gehalt >= 200 AND l.gehalt <= 400; ist gleichbedeutend mit: WHERE l.gehalt BETWEEN 200 AND 400; ACHTUNG: BETWEEN nimmt die Grenzen (200 und 400) mit!!! Aufzählen von Einzelwerten: SELECT * FROM lehrer l WHERE l.gehalt = 82 OR l.gehalt = 200 OR l.gehalt = 300; ist gleichbedeutend mit: WHERE l.gehalt IN (82, 200, 300); Alle Schüler, deren Vornamen mit "B" beginnen: SELECT * FROM schueler WHERE s_vorname LIKE "B*"; Hinweis: Access: * steht für beliebig viele Zeichen (0 oder N) SQL Standard: "%" Access: ? steht für genau ein Zeichen SQL Standard: _ - 19 - Einführung SQL Sortieren Wenn ein Schlüsselfeld vollständig verwendet wird, dann sortierte Ausgabe, sonst Reihenfolge NICHT vorhersehbar! SELECT * FROM schueler WHERE s_vorname LIKE "B*" ORDER BY s_vorname DESC; DESCENDING (DESC) - Z zuerst, zuletzt Adam ASCENDING (ASC) - aufsteigend, A zuerst, zuletzt Z SELECT * FROM schueler WHERE s_vorname LIKE "B*" ORDER BY s_vorname ASC; NULL Werte abfragen WHERE Feld IS NULL; Verneinung WHERE Feld IS NOT NULL; 6.3. Aggregatfunktionen Die Aggregatfunktionen ermitteln zu einem Feld (Spalte) aus einer Menge von einzelnen Sätzen einen Wert. COUNT MINIMUM(MIN) MAXIMUM(MAX) Anzahl der Sätze nur über numerische Felder: AVERAGE(AVG) Durchschnitt in der Gruppe SUMME(SUM) Summenwert der Gruppe SELECT COUNT( * ) AS anzahl FROM schueler; -> zählen aller Schüler, die in der Datenbank sind - 20 - Einführung SQL Achtung: Es werden immer alle Datensätze gezählt, auch wenn NULL Werte in einzelnen Feldern vorhanden sind! zählen aller Schüler, die keine NULL Werte in diesem Attribut haben! SELECT COUNT (s_gebdat) ... Anzahl aller unterschiedlichen Geburtstage der Schüler SELECT COUNT( DISTINCT s_gebdat ) FROM schueler; 6.4. Rechenfunktionen Achtung "+": + addiert bei Zahlen +, & hängt Strings zusammen +, -, *, \ ganzzahliges Ergebnis, / MOD, ^ 6.5. Datumsfunktionen DATE() DAY() WEEKDAY() MONTH() YEAR() Wochentag als Zahl (0..Sonntag) 6.6. Stringfunktionen LEFT MID RIGHT UPPER Zeichen von Links: Zeichen von Links: Zeichen von Rechts: Konvertierung in Großbuchstaben LEFT("12345",2) MID("12345,2,3) RIGHT("12345",2) UPPER("aBcDE") -> "12" -> "234" -> "45" -> "ABCDE" 6.7. Gruppierung Die ersten SELECT Anweisungen haben sich immer auf alle Datensätze in einer Tabelle bezogen - wir haben uns mit der WHERE Klausel genau gewünscht, welche Datensätze wir haben wollten. Mit der SELECT - Liste haben wir uns die Spalten (Felder) gewählt. Dann haben wir und mit den Aggregatfunktionen (SUM, AVG, etc) beschäftigt. Die Aggregatfunktionen liefern genau eine Zeile als Antwort: z.B.: Die Summe aller Stunden eines Lehrers. Nun wenden wir uns der Gruppierung zu - unter Gruppierung versteht man daß eine Menge (Liste) von Zeilen als Ergebnis geliefert wird. Jede gelieferte Zeile ist eine verdichtete Darstellung einer Menge von Zeilen, die aber nur als ein gemeinsame Ergebniszeile ausgegeben wird. - 21 - Einführung SQL Ein einfaches Beispiel ist die Lehrertabelle: Wir wollen nun wissen, welche unterschiedlichen Vornamen es gibt. Bis jetzt haben wir geschrieben: SELECT DISTINCT(l_vorname) FROM lehrer WHERE L_ID IN ("BE", "B1", "AS", "HA", "WA", "BI"); und erhalten das gewünschte Ergebnis: OK, aber nun wollen wir wissen, wie oft ein solcher Vorname in der Lehrertabelle vorkommt - dafür brauchen wir nun die Gruppierung: SELECT l_vorname, count(*) AS [Anzahl der Namen] FROM lehrer WHERE L_ID IN ("BE", "B1", "AS", "HA", "WA", "BI") GROUP BY l_vorname; Die Abarbeitung funktioniert so: Die Datensätze werden gemäß WHERE Klausel aus der Tabelle ausgewählt Die Zeilen einer Tabelle werden sortiert - und zwar nach den Feldern in der Group by Klausel Also bei uns werden die Daten für die Abfrage intern sortiert nach l_vorname: L_ID L_Name L_Vorname L_Gebdat L_Gehalt L_L_Chef BE Beringer Alfred 15.07.1961 220,00 HA B1 Berger Alfred 02.03.1945 400,00 AS HA WA BI Aschauer Hanke Walter Bilek Anton Gustav Hans Hans 19.08.1961 12.12.1950 11.11.1949 03.03.1932 180,00 300,00 115,00 280,00 PI B1 PI HA alle Records, mit gleichen Inhalten der GROUP BY Felder (L_Vorname) gehören zu einer "Gruppe" Gruppe L_ID L_Name L_Vorname L_Gebdat L_Gehalt L_L_Chef 1 BE Beringer Alfred 15.07.1961 220,00 HA 1 B1 Berger Alfred 02.03.1945 400,00 2 3 4 4 AS HA WA BI Aschauer Hanke Walter Bilek Anton Gustav Hans Hans 19.08.1961 12.12.1950 11.11.1949 03.03.1932 180,00 300,00 115,00 280,00 PI B1 PI HA für jede Gruppe werden die nötigen Verarbeitungen durchgeführt (genau eine Zeile ausgeben). Dabei ist es aber nicht mehr möglich auf die einzelnen Sätze einer Gruppe zuzugreifen - es ist - 22 - Einführung SQL nur möglich Aggregate zu benutzen. Es wird also nur genau eine Ergebniszeile zu einer ganzen Gruppe ausgegeben! das Ergebnis der Verarbeitung wird ausgegeben Wir können nun unsere Abfrage von vorhin aufgliedern: SELECT l_vorname FROM lehrer WHERE L_ID IN ("BE", "B1", "AS", "HA", "WA", "BI") GROUP BY l_vorname; Liefert natürlich das gleiche Ergebnis wie DISTINCT, aber die Aufgabenstellung zu zählen, wieviele Mitglieder die Gruppe hat können wir nicht beantworten. Erst mit der Abfrage: SELECT l_vorname, count(*) AS [Anzahl der Namen] FROM lehrer WHERE L_ID IN ("BE", "B1", "AS", "HA", "WA", "BI") GROUP BY l_vorname; Können wir die vollständige Antwort geben: - Die Datensätze werden ausgewählt - Die Gruppen nach den Vornamen werden gebildet - COUNT(*) bezieht sich auf die Datensätze innerhalb der Gruppe (auch wenn ich diese "von aussen" nicht mehr sehen kann! Warum können wir nicht sagen? SELECT l_vorname, l_Name FROM lehrer GROUP BY l_vorname; Anwort: BE Beringer Alfred B1 Berger Alfred Es müßte eine Zeile ausgeben werden, die aber zwei unterschiedliche Werte für das Feld l_name hat! Das kann natürlich nicht sein! -> das sollten wir schon als Gruppenverarbeitung kennen! Achtung: In der Select Liste dürfen daher also nur Attribute aus der "GROUP BY" Klausel - und Aggregate vorkommen! Wer macht mehr als 2 Stunden ? Wenn man Bedingungen an die Gruppe richten möchte, dann verwendet man die Klausel HAVING: Dieses Script ermittelt die Summe aller Stunden eines Lehrers. Von der fertigen Liste der Stundenanzahlen werden nur die Ergebniszeilen genommen, die Anzahlen größer als zwei haben. SELECT st_l_lehrer, count( * ) AS ANZAHL FROM stunden WHERE st_g_fach = "PR" GROUP BY st_l_lehrer HAVING count(*) > 2; Wir verwenden hier die Klausel "HAVING". HAVING bezieht sich auf das Ergebnis der Gruppierung: Von der gruppierten Ergebnissätzen werden nur jene tatsächlich ausgegeben, die der Bedingung in HAVING genügen! - 23 - Einführung SQL Bei der Formulierung der HAVING Klausel gelten die gleichen Regeln, wie bei der WHERE Klausel. Es können alle Operatoren genutzt werden und Unterabfragen (die kommen noch) eingeleitet werden. In der HAVING Klausel können aber nur folgende Aggregatsfunktionen eingesetzt werden: - SUM() - AVG() - MIN() - MAX() - COUNT() Dazu Abarbeitung von SQL Anweisungen ansehen: 1. Kreuzprodukt durch FROM Klausel (das lernen wir gleich kennen) 2. WHERE Klausel durchführen (eliminieren aller Zeilen, die nicht der WHERE Klausel entsprechen) -> daher kann hier nie eine Gruppenfunktion sein (es wird ja nur eliminiert) 3. Gruppierung 4. HAVING Klausel -> eine Bedingung, die sich an die Gruppe (Gruppierung) richtet 5. ORDER BY Klausel 6.8. kartesisches Produkt (Kreuzprodukt) Bis jetzt haben wir nur Abfragen mit einer Tabelle behandelt, nun wollen wir uns um Relationen / Beziehungen zwischen mehreren Tabellen kümmern. SELECT * FROM schueler, klassen; Funktionsweise des Kreuzproduktes: -> Alle Attribute von schueler und klassen! -> Jeder Schüler geht in jede Klasse (=Kreuzprodukt) Wenn in einer Select Anweisung mehr als eine Tabelle spezifiziert wird, aber keine WHERE Klausel zur Verbindung der beiden Tabellen angegeben wird, dann wird das kartesische Produkt dieser Tabellen erzeugt: Jede Zeile der einen Tabelle wird mit jeder Zeile der anderen Tabelle verknüpft (Kreuzprodukt der angebenen Tabellen). Das ist zwar ein gültiger Join, aber die Aussagekraft ist nicht sehr hoch bis sogar falsch! In unserem Schulbeispiel würde so jeder Schüler in jede Klasse gehen - das stimmt aber mit Sicherheit nicht, da ein Schüler nur in einer Klasse sein kann! Beispiel: Schüler S_Schüler A B C S_Klasse 1 1 2 Klassen K_Klasse 1 2 K_Bez 3HDA 3HDB - 24 - Einführung SQL SELECT * FROM schueler, klassen; Ergebnis: S_Schüler A A B B C C S_K_Klasse 1 1 1 1 2 2 K_Klasse 1 2 1 2 1 2 K_Bez 3HDA 3HDB 3HDA 3HDB 3HDA 3HDB 6.9. Inner (Euqi-) Join Also, es muß bei einem (vernünftigem) Join mindestens eine WHERE Abfrage geben (Schlüssel und Fremdschlüssel der beiden Tabellen mit "=" abgefragt). Ein sinnvoller Join benötigt n-1 Bedingungen (mit AND verknüpft) in der WHERE Klausel. Alle Attribute von beiden Tabellen: SELECT k.*, s.* FROM schueler s, klassen k WHERE s.s_k_klasse = k.k_id; Schüler S_Schüler A B C S_Klasse 1 1 3 Klassen K_Klasse 1 2 K_Bez 3HDA 3HDB Ergebnis: S_Schüler S_K_Klasse K_Klasse K_Bez A 1 1 3HDA B 1 1 3HDA Logische Abarbeitung: 1. Kreuzprodukt der angegebenen Tabellen 2. WHERE Klausel auf dieses Kreuzprodukt anwenden Inner (Equi) Join: Es werden ausschließlich die innerhalb (Inner) beider Tabellen vorhandenen Zeilen mit gleichen Schlüsselwerten (Equi) berücksichtig. Alle Attribute der Schülertabelle und von der Klassentabelle nur die Klassenbezeichnung: SELECT s.*, k.k_bez FROM schueler s, klassen k WHERE s.s_k_klasse = k.k_id; - 25 - Einführung SQL 6.10. Outer / Left / Right Join OUTER Join: Beim Outer Join wird zwischen einer Haupt- und einer nachgeordneten Tabelle unterschieden. Aus der Haupttabelle werden auf jeden Fall alle Datensätze berücksichtigt. Die Daten der Nebentabelle werden nur bei Gleichheit der Schlüssel berücksichtigt. LEFT Join: Es werden auf jeden Fall alle Datensätze der linken Tabelle selektiert - die Datensätze aus der rechten Tabelle bei denen die Schlüsselwerte übereinstimmen. RIGHT Join: Es werden auf jeden Fall alle Datensätze der rechten Tabelle selektiert - die Datensätze aus der linken Tabelle bei denen die Schlüsselwerte übereinstimmen. Beispiel: Schüler S_Schüler A B C S_Klasse 1 1 2 Klassen K_Klasse 2 3 K_Bez 3HDB 3HDC SELECT S.S_schuler, S.S_Klasse, K.K_Bez FROM Schuler s LEFT JOIN Klassen k ON s.S_KLasse = K.K_Klasse; Es werden also auf jeden Fall alle Datensätze der linken Tabelle (Schüler) ausgebenen: S_Schüler A B C S_Klasse 1 1 2 Die Klassenbezeichnug wird nur dann ausgegeben, wenn die Join Bedingung einen gültigen Wert in einem Feld der beiden Tabellen findet. In unserem Fall also nur für die Klasse "2": S_Schüler A B C S_Klasse K_Klasse 1 1 2 3HDB - 26 - Einführung SQL Achtung: Der Equi Join würde alle Datensätze "verlieren", wo es keine Übereinstimmung in den Feldinhalten gibt: SELECT S.S_schuler, S.S_Klasse, K.K_Bez FROM Schuler s, Klassen k WHERE s.S_KLasse = K.K_Klasse; S_Schüler S_Klasse K_Klasse C 2 3HDB 6.11. SELF Join Beim Self Join benötigen wir die gleiche Tabelle zweimal in einer Abfrage. Zur Realisierung benötigen wir unbedingt einen Alias! Namen des Lehrers und Namen des Vorgesetzten SELECT u.l_name, v.l_name FROM lehrer AS v, lehrer AS u WHERE u.l_l_chef = v.l_id; Achtung: Wenn ein Lehrer keinen Vorgesetzten hat, dann bekommen wir diesen Lehrer auch nicht in der Liste! 6.12. SUB - SELECT Das Sub-Select ist eine in Klammern eingeschlossene SELECT Anweisung innerhalb der WHERE Klausel einer anderen SELECT Anweisung Unterabfragen können in mehreren Ebenen geschachtelt sein. Sie werden in umgekehrter Reihenfolge der Definitionsebenen abgearbeitet. Also die "innerste" Abfrage wird zuerst abgearbeitet. Bei Unterabfragen können alle im Zugriff befindlichen Tabellen angesprochen werden. Wenn man Tabellen durch Aliase benennt, dann werden diese wie beim Self Join als unterschiedliche Tabellen behandelt - sonst kann man diese eine Tabelle als gemeinsame (globale) Variable sehen Eine Unterabfrage bezieht sich immer nur auf genau ein Feld! Wenn die Unterabfrage genau einen Wert liefert (Aggregatfunktion!), dann kann in der umschließenden Abfrage ein relationaler Operator (=, <,>) verwendet werden Wer wohnt dort, wo auch der Schüler Nummer 122 wohnt? Einführungsbeispiel zu SUB - SELECT Abfrage "WoWohntSchueler122" - 27 - Einführung SQL SELECT s_adresse FROM schueler WHERE s_schnr = 122; => z.B.: Döbling Abfrage "WerWohntInDoebling" SELECT * FROM schueler WHERE s_adresse = "Döbling"; Realisierung als SUB - SELECT: SELECT * FROM schueler WHERE s_adresse = (SELECT s_adresse FROM schueler WHERE s_schnr = 122); S_SCHNR S_Name S_Vorname S_Gebdat S_Adresse S_K_Klasse 55 Schulz Xandl 122 Graf Bobby 03.09.1964 Doebling 03TB 01.01.1962 Doebling 03TA Hinweis: Im SELECT und im SUB-SELECT wird die gleiche Tabelle verwendet - ohne unterschiedlichen Alias etc. In beiden Selects wird daher die gleiche Tabelle verarbeitet - dazu noch mehr bei den synchronisierten Selects. 6.12.A. IN Operator Der Operator "IN" überprüft in einem Sub-Select, ob eine in der Hauptabfrage gestellte Bedingung erfüllt wird. Der IN Operator wird in der Regel zur Suche in anderen Tabellen verwendet. Er prüft nacheinander für jeden Wert aus der Vergleichsfeld der Haupttabelle, ob dieser Wert in der Vergleichsfeld der Untertabelle steht - oder bei "NOT IN" halt eben nicht. Die beiden Feldnamen müssen gleich sein! Hinweis: Vergleich hierzu bei den einfachen Abfragen die Verwendung von "IN". Wir habe hier einfach eine fixe Liste von Werten angegeben - genauso funktioniert das auch bei der Unterabfrage! Fahrgemeinschaften (Welche Schüler wohnen im gleichen Ort - mindestens 2 Schüler?) SELECT s_schnr, s_name, s_adresse FROM schueler WHERE s_adresse IN (SELECT s_adresse FROM schueler GROUP BY s_adresse HAVING COUNT(*) > 1) ORDER BY s_adresse; Distinct? -> GROUP BY erzeugt immer genau einen Wert! 6.12.B. unsynchronisertes / synchronisiertes SELECT Alle Daten zum Klassensprecher und Stellvertreter - 28 - Einführung SQL SELECT schueler.* FROM schueler, klassen WHERE s_schnr = k_s_klaspr; nicht optimal, da durch das Kreuzprodukt sehr viele Ergebnissätze erzeugt werden müssen! SELECT * FROM schueler WHERE s_schnr IN (SELECT k_s_klaspr FROM klassen) OR s_schnr IN (SELECT k_s_klasprstv FROM klassen); Sehr viel schneller, da Sub-Select zuerst ausgeführt wird und das Teilergebnis signifikant kleiner ist! Das nennt man "unsynchronisiertes Select". Eine weitere Variante ist das "synchronisierte Select" (beide Selects laufen direkt hinter einander): Hierbei wird in der Abfrage und in der Unterabfrage die gleiche Tabelle angesprochen. ACHTUNG: Gibt natürlich nur die Daten der Klassensprecher aus! SELECT * FROM schueler WHERE EXISTS (SELECT 'A' FROM klassen WHERE s_schnr = k_s_klaspr); "SELECT Liste wird ohnehin nie ausgegeben! 6.12.C. EXISTS Wir wollen nur wissen, ob ein Eintrag in einer Tabelle vorhanden ist - es sollen aber keine Daten transportiert werden! die Unterabfrage darf nur eine Tabelle aufweisen (JOIN NICHT erlaubt) die SELECT Klauser der Unterabfrage wird entweder mit "*" oder mit einer beliebigen Konstanten eingeleitet (es werden ja ohnehin keine Daten transprotiert!) es existiert KEINE Vergleichsspalte zwischen Abfrage und Unterabfrage die Verknüpfung der beiden Abfragen erfolgt in der WHERE Klausen der Unterabfrage!!! Beispiel: SELECT persnr, pname FROM persdat p WHERE EXISTS ( SELECT * FROM abteilungen a WHERE p.persnr = f.persnr) die EXISTS Abfrage ist teurer (Laufzeit) als die Verwendung der IN Abfrage ACHTUNG: Das Zurückgeben einer Stringkonstanten ist syntaktisch notwendig! Auf keinen Fall irgendein Feld der Tabelle nehmen, um sich den Datentransport zu ersparen! - 29 - Einführung SQL 6.12.D. ANY (irgendeiner) Wenn eine Unterabfrage bei relationalen Operatoren verwendet werden soll, dann wird der Operator ANY oder ALL benötigt. Sonst darf die Unterabfrage ja nur genau einen Wert liefern, dieser Wert (atomar) wird beim Vergleich verwendet. Alle Lehrer, die mehr verdienen als irgendeiner der Lehrer "MY" und "AU", sortiert nach Einkommen SELECT l.* FROM lehrer l WHERE l.l_gehalt > ANY ( SELECT l2.l_gehalt FROM Lehrer l2 WHERE l2.l_ID IN ("MY", "AU")) ORDER BY l.l_gehalt L_ID L_Name L_Vorname L_Gebdat L_Gehalt L_L_Chef AU LN Auwald Lenau Herbert 07.09.1946 Nikolaus 22.02.1938 AS PI Aschauer Anton Pirkner Walter 19.08.1961 22.06.1955 180,00 PI 220,00 B1 BE BI Beringer Alfred Bilek Hans 15.07.1961 03.03.1932 220,00 HA 280,00 HA LI HA Lindner Kristine 12.12.1958 Hanke Gustav 12.12.1950 300,00 HA 300,00 B1 B1 Berger 400,00 Alfred 02.03.1945 150,00 LN 180,00 HA Also, der "ANY" Test wird in Zusammenhang zwischen den SQL Vergleichsoperatoren (=, <>, <, <=, >, >=) benötigt. Dabei wird der Wert eines Feldes mit einer Liste von Werten aus einem SUB - Select verglichen. Der angegebene Vergleich wird durchgeführt - liefert auch nur ein einziger Vergleich das Ergebnis TRUE, dann liefert auch der "ANY" - Test TRUE! Wir können den ANY Test also als ODER Verknüpfung interpretieren. ACHTUNG: Das SUB - Select darf nur EIN Feld als Ergebnis liefern! SUB - Select ist die leere Menge In diesem Fall liefert der "ANY" Test immer "Falsch" - was ja auch Sinn macht! NULL Werte im SUB - Select Das ist nicht ganz einfach, wir werden mit der Vergleichsergebnis "Falsch" rechnen müssen! 6.12.E. ALL Der "ALL" Test wird ebenfalls bei den SQL Vergleichsoperatoren benötigt. Hier wird "Wahr" als Ergebnis ermittelt, wenn ALLE Vergleiche mit dem SUB - Select wahr ergeben haben! Beispiel: Ermittle alle Bestellungen und Kundennummern, zu den Kunden, die Bestellungen gemacht haben, nachdem alle Produkte der Bestellund 2001 geliefert worden sind" SELECT id, cust_id FROM sales_order WHERE order_date > ALL ( SELECT ship_date FROM sales_order_items WHERE id=2001) id cust_id 2002 102 2003 103 2004 104 - 30 - Einführung SQL 2005 101 ... ... Der "ALL" Test ist also mit einer UND - Verknüpfung vergleichbar! Achtung: SUB - Select ist die leere Menge ALL liefert "Wahr" NULL Werte im SUB - Select If the comparison test is false for any values in the result set, the ALL search returns FALSE. It returns TRUE if all values are true. Otherwise, it returns UNKNOWN--for example, this can occur if there is a NULL value in the subquery result set but the search condition is TRUE for all non-NULL values. Verneinung des "ALL" Tests Vorsicht, das ist NICHT Äquivalent: NOT a = ALL (SUB - Select) a <> ALL (SUB - Select) 6.13. UNION UNION dient zum Verbinden von SELECT Abfragen. Die Ergebnisse von unterschiedlichen SELECT Abfragen können zur einem gemeinsamen Ergebnis verbunden werden. Die Voraussetzung ist, daß die Spaltenauswahl in Typ und Grösse übereinstimmen. Wenn nötig kann eine die Spaltenauswahl durch geeignete Ausdrücke (Leerfelder) angepaßt werden. Eine Namensgleichheit der Felder ist NICHT erforderlich. Aliases müssen in der ersten SELECT Anweisung deklariert werden. Wenn ein Lehrer Klassenvorstand ist, dann Klassenname, sonst ein Strich ausgeben. Beispiel: Bilek 03TA Hanke ---Alle Lehrer, die Klassenvorstände sind: SELECT l_vorname, l_name, k_bez FROM lehrer, klassen WHERE l_id = k_l_klavst; Alle Lehrer, die KEIN Klassenvorstand sind: SELECT l_vorname, l_name, '----' FROM lehrer WHERE NOT EXISTS (SELECT '#' FROM klassen WHERE l_id = k_l_klavst); UNION verwenden: - 31 - Einführung SQL SELECT l_vorname, l_name, k_bez FROM lehrer, klassen WHERE l_id = k_l_klavst UNION SELECT l_vorname, l_name, '----' FROM lehrer WHERE NOT EXISTS (SELECT '#' FROM klassen WHERE l_id = k_l_klavst) ORDER BY 2; ORDER BY geht nur mit der Reihenfolgennummer! Weiters gibt es (in Oracle): MINUS (Menge A ohne Menge B) INTERSECT (Durchschnitt der beiden Mengen) - 32 - Einführung SQL A. Datenmodell Schuldatenbank - 33 -
