SQL – eine Einführung - Benutzer-Homepage
Transcrição
SQL – eine Einführung - Benutzer-Homepage
SQL – eine Einführung SQL – Konzept und Geschichte Tabelle 1: SQL – Geschichte 1974 1977 1986 1989 1992 1999 2004 2006 2008 2012 IBM entwickelt SEQUEL (Structured English Query Language) Relational Software Inc (später Oracle) überarbeitet SEQUEL zu SEQUEL/2, dann SQL genannt ANSI standardisiert SQL auf Basis von IBMs Entwurf Revision des Standards publiziert als SQL89 oder auch SQL1, jetzt auch ein ISO-Standard ANSI/ISO publiziert SQL92, auch SQL2 genannt Objektrelationale Merkmale werden in den Standard übernommen, SQL:1999 oder SQL3 SQL:2003 wird publiziert (erste Erweiterungen für XML) SQL:2006 mit Erweiterungen für XML (SQL/XML) SQL:2008 diverse Erweiterungen SQL:2011 Erweiterungen für temporale Daten Tabelle 2: SQL – Bestandteile des Standards Part Part Part Part Part Part Part Part 1 2 3 4 9 10 11 13 Part 14 Framework (SQL/Framework) Foundation (SQL/Foundation) Call-Level-Interface (SQL/CLI) Persistent Stored Modules (SQL/PSM) Management of External Data (SQL/MED) Object Language Bindings (SQL/OLB) Information and Definition Schemas (SQL/Schemata) SQL Routines and Types Using the Java TM Programming Language (SQL/JRT) XML-Related Specifications (SQL/XML) Syntaxelemente und Datentypen In Datenbanken werden Werte gespeichert, die aus einem bestimmten Wertebereich sind, man sagt auch, die einen bestimmten Datentyp haben. Damit man einen Wert hinschreiben kann, muss man eine entsprechende Repräsentation als Literal verwenden. Betrachten wir also zunächst Datentypen in SQL: 1 SQL – eine Einführung SQL-Anweisung 1. Strings in SQL Beispiel: Hello World in SQL select ’Hello World, this is SQL’ as Nachricht Ausgabe: nachricht -------------------------Hello World, this is SQL (1 row) SQL-Anweisung 2. Strings in SQL Beispiel: Ausgeben eines Apostrophs select ’Dies ist ein Apostroph: ’’!’ as Nachricht Ausgabe: nachricht ---------------------------Dies ist ein Apostroph: ’! (1 row) SQL-Anweisung 3. String-Operator || Beispiel: Vor- und Nachnamen zusammensetzen select ’Thomas’ || ’ ’ || ’Lehr’ as Name Ausgabe: name ------------Thomas Lehr (1 row) SQL-Anweisung 4. String-Operator lower Beispiel: Zeichenfolge in Kleinbuchstaben umwandeln select lower(’DAS IST KLEIN’) as Beispiel 2 SQL – eine Einführung Tabelle 3: Datentypen in SQL String-Typen CHAR(n) VARCHAR(n) CLOB BLOB Zeichenfolge der Länge n, ggfs. mit Leerzeichen aufgefüllt Zeichenfolge variabler Länge mit maximal n Zeichen Zeichenfolge beliebiger Länge Binäre Zeichenfolge beliebiger Länge Numerische Typen NUMERIC(p, s) DECIMAL(p, s) SMALLINT INTEGER BIGINT FLOAT(p) REAL DOUBLE Dezimalzahl mit (genau) p Stellen Genauigkeit, davon s hinterm Komma Dezimalzahl mit (mind.) p Stellen Genauigkeit, davon s hinterm Komma ganze Zahl, Bereich i.a. 2 Bytes, d.h. -32768 - 32767 ganze Zahl, Bereich i.a. 4 Bytes ganze Zahl, Bereich i.a. 8 Bytes Fließkommazahl mit einer Genauigkeit von p binären Stellen Fließkommazahl mit mind. 6 präzisen Dezimalstellen (p=24) Fließkommazahl mit mind. 15 präzisen Dezimalstellen (p=53) Datumstypen DATE TIME TIMESTAMP INTERVAL Tag von 4713 v. Chr. - 5874897 Zeit von 00 - 24 Uhr, Mikrosekunden genau Tag und Zeit Zeitintervall, Zeitdauer Boolesche Typen BOOLEAN Wahrheitswert true/false aber evtl auch <null> Ausgabe: beispiel --------------das ist klein (1 row) SQL-Anweisung 5. String-Operator substring Beispiel: Teil aus Zeichenfolge ausschneiden select substring(’X String ohne X’ from 3) as Beispiel 3 SQL – eine Einführung Tabelle 4: Operatoren in SQL String-Typen || lower upper substring overlay trim Konkatenation von Strings Alle Zeichen in Kleinbuchstaben umwandeln Alle Zeichen in Großbuchstaben umwandeln Teil der Zeichenfolge Teilstring ersetzen Zeichen am Beginn/Ende des Strings entfernen Numerische Typen + * / % ˆ Addition Subtraktion Multpilikation Division, bei ganzen Zahl wird der Rest abgeschnitten Modulo Potenz Datumstypen + Boolesche Typen and or not Addition von Tagen, Stunden, Intervallen Subtraktion von Tagen, Stunden, Intervallen; Differenz von Tagen, Intervallen Logisches und Logisches oder Logisches nicht Ausgabe: beispiel --------------String ohne X (1 row) SQL-Anweisung 6. Addition, Subtraktion und Multiplikation Beispiel: Rechenaufgabe select 2 + 3 * (5 -2) as Ergebnis Ausgabe: ergebnis ---------11 (1 row) 4 SQL – eine Einführung SQL-Anweisung 7. Variable CURRENT_DATE Beispiel: Aktuellen Tag ausgeben select CURRENT_DATE as "Aktueller Tag" Ausgabe: Aktueller Tag --------------2009-03-17 (1 row) Dieses Beispiel wurde offensichtlich am 17. März 2009 ausprobiert. SQL-Anweisung 8. Rechnen mit Datum Beispiel: Heute in 30 Tagen select CURRENT_DATE + 30 as "in 30 Tagen" Ausgabe: in 30 Tagen ------------2009-04-16 (1 row) SQL-Anweisung 9. Vergleich von Daten Beispiel: Ist der 1. Februar 2007 schon vorbei? select CURRENT_DATE > ’2007-02-01’ as Ergebnis Ausgabe: Ergebnis ---------t (1 row) Beispieldatenbank Wein In der Einführung verwenden wir die Datenbank Wein, die folgende Struktur hat: • Die Tabelle Artikel enthält die Weine, die ein Weinhändler anbietet: Der Artikel mit der <ArtNr> trägt die Bezeichnung <Bez> und wird produziert vom Weingut <Weingut>, er hat den Jahrgang <Jahrgang>, die Farbe <Farbe> und kostet <Preis>. 5 SQL – eine Einführung Tabelle 5: Inhalt der Datenbank Wein Artikel: ArtNr 100001 100002 100003 604851 145119 Bez Weingut Jahrgang Les Châteaux Chablis Château Caraguilhes Prosecco Val Monte Le Cop de Cazes Louis Max Louis Max Louis Max Cave Bellenda Domaine Cazes 2002 2005 2005 <null> 2004 Farbe Preis rot weiß rosé weiß rot 17.90 15.50 14.90 7.60 6.90 Lieferant: LftNr 1 2 Firma Postfach PLZ Ort Weinimport Lehr Bremer Weinkontor 45367 56 F-68567 28195 Colmar Bremen LieferBez: LftNr ArtNr 1 1 1 2 2 2 100001 100002 100003 100002 145119 604851 Kunde: KndNr Name Vorname Str PLZ Ort 100101 100102 100105 Kehl Kehl Riesling Thomas Thomas Karin Weinstr. 3 Im Riesling 3 67, Rue du Château 79675 68734 F-68567 Kaiserstuhl Eltville Colmar Auftrag: AuftrNr 1003 1001 1002 1004 Datum KndNr 2007-03-01 2006-10-12 2006-02-12 2006-02-12 100101 100101 100102 <null> AuftrPos: AuftrNr Anzahl ArtNr 1003 1003 1003 1001 1001 1001 1001 1002 12 12 12 1 1 1 1 48 100001 100002 100003 100001 100002 100003 145119 100003 6 SQL – eine Einführung • Die Tabelle Lieferant verzeichnet Firmen, die Weine liefern: Der Lieferant mit der Lieferantennummer <LftNr> hat den Namen <Firma>, und als Adresse <Postfach>, <PLZ>, <Ort>. • Der Weinhändler bezieht seine Artikel nicht direkt bei Weingütern, sondern bei den Lieferanten. Ein Lieferant kann verschiedene Weine liefern. Diese Lieferbeziehung ist in der Tabelle LieferBez verzeichnet: Der Lieferant mit der Lieferantennummer <LftNr> liefert den Wein mit der Artikelnummer <ArtNr>. • Die Tabelle Kunde verzeichnet die Kunden des Weinhändlers: Der Kunde mit der Kundennummer <KndNr> heißt <Name>, <Vorname> und wohnt in <Str>, <PLZ> <Ort>. • Die Tabelle Auftrag enthält Aufträge der Kunden: Der Auftrag mit der Auftragsnummer <AuftrNr> wird vom Kunden mit der Kundennummer <KndNr> am <Datum> erteilt.1 • Aufträge haben Auftragspositionen, die in der Tabelle AuftrPos verzeichnet sind: Der Auftrag <AuftrNr> hat eine Position, in der die <Anzahl> vom Artikel mit der Artikelnummer <ArtNr> bestellt wird. Abfragen einer Tabelle SQL-Anweisung 10. Wiedergabe einer Tabelle Beispiel: Alle Angaben zu allen Weinen der Tabelle Artikel select * from Artikel Ausgabe: artnr | bez | weingut | jahrgang | farbe | preis --------+---------------------+---------------+----------+-------+------100001 | Les Châteaux | Louis Max | 2002 | rot | 17.90 100002 | Chablis | Louis Max | 2005 | weiß | 15.50 100003 | Château Caraguilhes | Louis Max | 2005 | rosé | 14.90 604851 | Prosecco Val Monte | Cave Bellenda | | weiß | 7.60 145119 | Le Cop de Cazes | Domaine Cazes | 2004 | rot | 6.90 (5 rows) 1 In unserer Beispieldatenbank kann es sein, dass es einen Auftrag ohne Kundennummer gibt. Das wird in einem realistischen Szenario nicht der Fall sein und läßt sich auch leicht verhindern (wodurch?). In der Beispieldatenbank kommt aber ein solcher unvollständiger Auftrag vor, weil wir an ihm zeigen können, wie man einen äußeren Verbund in SQL formuliert. 7 SQL – eine Einführung Bemerkung: Verwendet man „*“ muss man sich auf die Reihenfolge der Attribute verlassen. Also eignet sich dieses Konstrukt im Grunde nur für Ad-hoc-Abfragen. Projektion SQL-Anweisung 11. Ausgaben bestimmter Spalten einer Tabelle (Projektion) Beispiel: Artikelnummer, Bezeichnung und Weingut der Weine select ArtNr, Bez, Weingut from Artikel Ausgabe: artnr | bez | weingut --------+---------------------+--------------100001 | Les Châteaux | Louis Max 100002 | Chablis | Louis Max 100003 | Château Caraguilhes | Louis Max 604851 | Prosecco Val Monte | Cave Bellenda 145119 | Le Cop de Cazes | Domaine Cazes (5 rows) SQL-Anweisung 12. Projektion kann zu Duplikaten führen Beispiel: Weingut der Weine select Weingut from Artikel Ausgabe: weingut --------------Louis Max Louis Max Louis Max Cave Bellenda Domaine Cazes (5 rows) SQL-Anweisung 13. Unterdrückung von Duplikaten Beispiel: Weingut der Weine – ohne mehrfache Nennung desselben Weinguts select distinct Weingut from Artikel 8 SQL – eine Einführung Ausgabe: weingut --------------Cave Bellenda Domaine Cazes Louis Max (3 rows) Bemerkung Manchmal möchte man nur eine Zeile zu einem bestimmten Attribut. So möchte man vielleicht in unserer Tabelle Artikel einen Wein pro Weingut sehen, egal welchen. Dafür gibt es distinct on: select distinct on (Weingut) * from Artikel Als Ergebnis erhält man in unserer Datenbank drei Weine, von jedem Weingut eines; welcher Wein von „Louis Max“ dies ist, ist zufällig. SQL-Anweisung 14. Ausgabe von Spalten mit Transformation und Umbenennung Beispiel: Artikelnummer, Bezeichnung und Einzelpreis sowie Kartonpreis (12 zum Preis von 11) aller Weine select ArtNr, Bez, Preis as "Einzelpreis", 11 * Preis as "Kartonpreis" from Artikel Ausgabe: artnr | bez | Einzelpreis | Kartonpreis --------+---------------------+-------------+------------100001 | Les Châteaux | 17.90 | 196.90 100002 | Chablis | 15.50 | 170.50 100003 | Château Caraguilhes | 14.90 | 163.90 604851 | Prosecco Val Monte | 7.60 | 83.60 145119 | Le Cop de Cazes | 6.90 | 75.90 (5 rows) SQL-Anweisung 15. Ausgabe von Werten mit dem case-Operator Beispiel: Ausgabe von Bezeichnung, Weingut und Jahrgang der Weine. Unbekannter Jahrgang soll als ’o.A.’ ausgegeben werden. select Bez, Weingut, case when Jahrgang is null then ’o.A.’ else cast(Jahrgang as text) end as Jahrgang from Artikel 9 SQL – eine Einführung Ausgabe: bez | weingut | jahrgang ---------------------+---------------+---------Les Châteaux | Louis Max | 2002 Chablis | Louis Max | 2005 Château Caraguilhes | Louis Max | 2005 Prosecco Val Monte | Cave Bellenda | o.A. Le Cop de Cazes | Domaine Cazes | 2004 (5 rows) Bemerkung Für das obige Beispiel eignet sich auch die Funktion coalesce, die das erste ihrer Argumente zurückgibt, das nicht null ist. In unserem Beispiel: select Bez, Weingut, coalesce( cast(Jahrgang as text), ’o.A.’) as Jahrgang from Artikel Restriktion SQL-Anweisung 16. Einfache Filterbedingung Beispiel: Alle Angaben zu den Rotweinen select * from Artikel where Farbe = ’rot’ Ausgabe: artnr | bez | weingut | jahrgang | farbe | preis --------+-----------------+---------------+----------+-------+------100001 | Les Châteaux | Louis Max | 2002 | rot | 17.90 145119 | Le Cop de Cazes | Domaine Cazes | 2004 | rot | 6.90 (2 rows) SQL-Anweisung 17. Negation von Bedingungen Beispiel: Alle Angaben zu Weinen, die nicht rot sind select * from Artikel where Farbe <> ’rot’ Variante select * from Artikel where not (Farbe = ’rot’) 10 SQL – eine Einführung Ausgabe: artnr | bez | weingut | jahrgang | farbe | preis --------+---------------------+---------------+----------+-------+------100002 | Chablis | Louis Max | 2005 | weiß | 15.50 100003 | Château Caraguilhes | Louis Max | 2005 | rosé | 14.90 604851 | Prosecco Val Monte | Cave Bellenda | | weiß | 7.60 (3 rows) result SQL-Anweisung 18. Vergleiche in Bedingungen Beispiel: Alle Angaben der Weine, die weniger als 15 Euro kosten select * from Artikel where Preis < 15.00 Ausgabe: artnr | bez | weingut | jahrgang | farbe | preis --------+---------------------+---------------+----------+-------+------100003 | Château Caraguilhes | Louis Max | 2005 | rosé | 14.90 604851 | Prosecco Val Monte | Cave Bellenda | | weiß | 7.60 145119 | Le Cop de Cazes | Domaine Cazes | 2004 | rot | 6.90 (3 rows) Tabelle 6: Vergleichsoperatoren Operator Bedeutung = <> < <= >= > gleich ungleich kleiner kleiner gleich größer gleich größer Im SQL Standard wird „ungleich“ durch <> angegeben. Viele Datenbanksysteme erlauben auch !=. SQL-Anweisung 19. Vergleiche mit and kombiniert Beispiel: Alle Angaben zu Rotweinen, die weniger als 15 Euro kosten. select * from Artikel where Farbe = ’rot’ and Preis < 15.00 11 SQL – eine Einführung Ausgabe: artnr | bez | weingut | jahrgang | farbe | preis --------+-----------------+---------------+----------+-------+------145119 | Le Cop de Cazes | Domaine Cazes | 2004 | rot | 6.90 (1 row) SQL-Anweisung 20. Vergleiche mit or kombiniert Beispiel: Alle Angaben zu Weine, die rot sind oder günstiger als 15 Euro. select * from Artikel where Farbe = ’rot’ or Preis < 15.00 Ausgabe: artnr | bez | weingut | jahrgang | farbe | preis --------+---------------------+---------------+----------+-------+------100001 | Les Châteaux | Louis Max | 2002 | rot | 17.90 100003 | Château Caraguilhes | Louis Max | 2005 | rosé | 14.90 604851 | Prosecco Val Monte | Cave Bellenda | | weiß | 7.60 145119 | Le Cop de Cazes | Domaine Cazes | 2004 | rot | 6.90 (4 rows) Tabelle 7: Logische Operatoren und Wahrheitstafeln Operator Bedeutung not and or nicht und (inklusives) oder a b a and b a or b true true true false false null true false null false null null true false null false false null true true true false null null a not a true false null false true null Die Operatoren and und or sind kommutativ. Der Operator not bindet am stärksten, dann and und schließlich or. Am besten, man klammert die Operatoren and und or auf jeden Fall, damit keine Unklarheiten auftreten können. Vorsicht ist geboten, wenn man Funktionen in logischen Ausdrücken verwendet: es ist nicht garantiert, dass bei der Auswertung des Ausdrucks alle Funktionen wirklich ausgeführt werden. 12 SQL – eine Einführung SQL-Anweisung 21. Bindung logischer Operatoren I Beispiel: Alle Angaben zu Weißweinen oder günstigen Rotweinen select * from Artikel where Farbe = ’weiß’ or Farbe = ’rot’ and Preis < 15.00 Ausgabe: artnr | bez | weingut | jahrgang | farbe | preis --------+--------------------+---------------+----------+-------+------100002 | Chablis | Louis Max | 2005 | weiß | 15.50 604851 | Prosecco Val Monte | Cave Bellenda | | weiß | 7.60 145119 | Le Cop de Cazes | Domaine Cazes | 2004 | rot | 6.90 SQL-Anweisung 22. Bindung logischer Operatoren II Beispiel: Alle Angaben zu günstigen Weiß- oder Rotweinen select * from Artikel where (Farbe = ’weiß’ or Farbe = ’rot’) and Preis < 15.00 Ausgabe: artnr | bez | weingut | jahrgang | farbe | preis --------+--------------------+---------------+----------+-------+------604851 | Prosecco Val Monte | Cave Bellenda | | weiß | 7.60 145119 | Le Cop de Cazes | Domaine Cazes | 2004 | rot | 6.90 (2 rows) SQL-Anweisung 23. Vergleiche von Tupeln Beispiel: Alle Rotweine des Weingutes ’Louis Max’ select * from Artikel where (Weingut, Farbe) = (’Louis Max’, ’rot’ ) Ausgabe: artnr | bez | weingut | jahrgang | farbe | preis --------+--------------+-----------+----------+-------+------100001 | Les Châteaux | Louis Max | 2002 | rot | 17.90 (1 row) In SQL kann man Tupelvergleiche auch mit anderen Operatoren machen, die lexikographisch ausgewertet werden. Dies kann verwirrend sein, deshalb rate ich davon ab. 13 SQL – eine Einführung SQL-Anweisung 24. Der Operator between . . . and Beispiel: Alle Angaben zu den Weinen aus den Jahrgängen 2004 - 2006 select * from Artikel where Jahrgang between 2004 and 2006 Ausgabe: artnr | bez | weingut | jahrgang | farbe | preis --------+---------------------+---------------+----------+-------+------100002 | Chablis | Louis Max | 2005 | weiß | 15.50 100003 | Château Caraguilhes | Louis Max | 2005 | rosé | 14.90 145119 | Le Cop de Cazes | Domaine Cazes | 2004 | rot | 6.90 (3 rows) SQL-Anweisung 25. Der Operator in Beispiel: Alle Angaben zu den Weinen von ‚Louis Max‘ und ‚Domaine Cazes‘ select * from Artikel where Weingut in (’Louis Max’, ’Domaine Cazes’) Ausgabe: artnr | bez | weingut | jahrgang | farbe | preis --------+---------------------+---------------+----------+-------+------100001 | Les Châteaux | Louis Max | 2002 | rot | 17.90 100002 | Chablis | Louis Max | 2005 | weiß | 15.50 100003 | Château Caraguilhes | Louis Max | 2005 | rosé | 14.90 145119 | Le Cop de Cazes | Domaine Cazes | 2004 | rot | 6.90 (4 rows) SQL-Anweisung 26. Der Operator like Beispiel: Alle Angaben zu Weinen, in deren Bezeichnung die Zeichenfolge ‚Château‘ vorkommt. select * from Artikel where Bez like ’%Château%’ Ausgabe: artnr | bez | weingut | jahrgang | farbe | preis --------+---------------------+-----------+----------+-------+------100001 | Les Châteaux | Louis Max | 2002 | rot | 17.90 100003 | Château Caraguilhes | Louis Max | 2005 | rosé | 14.90 (2 rows) 14 SQL – eine Einführung SQL-Anweisung 27. Der Operator like Beispiel: Alle Angaben zu Weinen, deren Bezeichnung mit ‚Château‘ beginnt. select * from Artikel where Bez like ’Château%’ Ausgabe: artnr | bez | weingut | jahrgang | farbe | preis --------+---------------------+-----------+----------+-------+------100003 | Château Caraguilhes | Louis Max | 2005 | rosé | 14.90 (1 row) SQL-Anweisung 28. Der Operator like Beispiel: Alle Angaben zu Weinen, deren Bezeichnung als zweiten Buchstaben ein ‚e‘ hat. select * from Artikel where Bez like ’_e%’ Ausgabe: artnr | bez | weingut | jahrgang | farbe | preis --------+-----------------+---------------+----------+-------+------100001 | Les Châteaux | Louis Max | 2002 | rot | 17.90 145119 | Le Cop de Cazes | Domaine Cazes | 2004 | rot | 6.90 (2 rows) Bemerkung: Wenn man in einem Ausdruck mit like ‚%‘ oder ‚_‘ verwenden möchte, muss man ein Escape-Zeichen vorausstellen. Die Syntax lautet dann like ’pattern’ escape ’escape char’, also z.B. like ’%\%’ escape ’\’. In PostgreSQL ist ‚\‘ als Escape-Zeichen voreingestellt. Bemerkung: Außer like gibt es auch den Operator similar to mit dem Vergleiche mit regulären Ausdrücken gemacht werden können. Beispiel: select * from Artikel where bez similar to ’(L|P)%’ Hat zum Ergebnis alle Artikel, die mit den Buchstaben ‚L‘ oder ‚P‘ beginnen. Sortierung 15 SQL – eine Einführung SQL-Anweisung 29. Aufsteigende Sortierung Beispiel: Alle Weine, sortiert nach Preis, den günstigsten zuerst select * from Artikel order by Preis Ausgabe: artnr | bez | weingut | jahrgang | farbe | preis --------+---------------------+---------------+----------+-------+------145119 | Le Cop de Cazes | Domaine Cazes | 2004 | rot | 6.90 604851 | Prosecco Val Monte | Cave Bellenda | | weiß | 7.60 100003 | Château Caraguilhes | Louis Max | 2005 | rosé | 14.90 100002 | Chablis | Louis Max | 2005 | weiß | 15.50 100001 | Les Châteaux | Louis Max | 2002 | rot | 17.90 (5 rows) SQL-Anweisung 30. Absteigende Sortierung Beispiel: Alle Weine, sortiert nach Preis, den teuersten zuerst select * from Artikel order by Preis desc Ausgabe: artnr | bez | weingut | jahrgang | farbe | preis --------+---------------------+---------------+----------+-------+------100001 | Les Châteaux | Louis Max | 2002 | rot | 17.90 100002 | Chablis | Louis Max | 2005 | weiß | 15.50 100003 | Château Caraguilhes | Louis Max | 2005 | rosé | 14.90 604851 | Prosecco Val Monte | Cave Bellenda | | weiß | 7.60 145119 | Le Cop de Cazes | Domaine Cazes | 2004 | rot | 6.90 (5 rows) SQL-Anweisung 31. Sortierung nach mehreren Spalten Beispiel: Alle Weine sortiert nach Weingut (aufsteigend) und dann nach Preis (absteigend) select * from Artikel order by Weingut asc, Preis desc Ausgabe: artnr | bez | weingut | jahrgang | farbe | preis --------+---------------------+---------------+----------+-------+------604851 | Prosecco Val Monte | Cave Bellenda | | weiß | 7.60 145119 | Le Cop de Cazes | Domaine Cazes | 2004 | rot | 6.90 100001 | Les Châteaux | Louis Max | 2002 | rot | 17.90 100002 | Chablis | Louis Max | 2005 | weiß | 15.50 100003 | Château Caraguilhes | Louis Max | 2005 | rosé | 14.90 (5 rows) 16 SQL – eine Einführung Aggregationen SQL-Anweisung 32. Die Aggregatfunktion count Beispiel: Ausgabe der Anzahl der Artikel select count(*) as Anzahl from Artikel Ausgabe: anzahl -------5 (1 row) SQL-Anweisung 33. Die Aggregatfunktion count Beispiel: Ausgabe der Anzahl der (verschiedenen) Weingüter select count(distinct Weingut) as "Anzahl Weingüter" from Artikel Ausgabe: Anzahl Weingüter -----------------3 (1 row) SQL-Anweisung 34. Die Aggregatfunktion sum Beispiel: Der Preis von 3 Flaschen verschiedener Weine von Louis Max select sum(Preis) as "Preis Combo Louis Max" from Artikel where Weingut = ’Louis Max’ Ausgabe: Preis Combo Louis Max ----------------------48.30 (1 row) 17 SQL – eine Einführung SQL-Anweisung 35. Die Aggregatfunktion avg Beispiel: Ausgabe des durchschnittlichen Preises der Weine select round(avg(Preis),2) as Durchschnittspreis from Artikel Ausgabe: durchschnittspreis -------------------12.56 (1 row) SQL-Anweisung 36. Die Aggregatfunktion min Beispiel: Ausgabe des frühesten Jahrgangs, von dem Weine im Angebot sind select min(Jahrgang) as "frühester Jahrgang" from Artikel Ausgabe: frühester Jahrgang -------------------2002 (1 row) SQL-Anweisung 37. Die Aggregatfunktion max Beispiel: Ausgabe des höchsten Preises eines angebotenen Weins select max(Preis) as "höchster Preis" from Artikel Ausgabe: höchster Preis ---------------17.90 (1 row) Abfragen nach Daten mehrerer Tabellen (Der Verbund) 18 SQL – eine Einführung SQL-Anweisung 38. Das kartesische Produkt Beispiel: Angabe aller Kunden kombiniert mit Aufträgen – unabhängig davon, ob der Kunde den Auftrag gestellt hat select Kunde.KndNr, Kunde.Name, Auftrag.AuftrNr, Auftrag.Datum, Auftrag.KndNr from Kunde cross join Auftrag Ausgabe: kndnr | name | auftrnr | datum | kndnr --------+----------+---------+------------+-------100101 | Kehl | 1003 | 2007-03-01 | 100101 100102 | Kehl | 1003 | 2007-03-01 | 100101 100105 | Riesling | 1003 | 2007-03-01 | 100101 100101 | Kehl | 1001 | 2006-10-12 | 100101 100102 | Kehl | 1001 | 2006-10-12 | 100101 100105 | Riesling | 1001 | 2006-10-12 | 100101 100101 | Kehl | 1002 | 2006-02-12 | 100102 100102 | Kehl | 1002 | 2006-02-12 | 100102 100105 | Riesling | 1002 | 2006-02-12 | 100102 100101 | Kehl | 1004 | 2006-02-12 | 100102 | Kehl | 1004 | 2006-02-12 | 100105 | Riesling | 1004 | 2006-02-12 | (12 rows) Variante SQL89 select Kunde.KndNr, Kunde.Name, Auftrag.AuftrNr, Auftrag.Datum, Auftrag.KndNr from Kunde, Auftrag SQL-Anweisung 39. Der (innere) Verbund (join) Beispiel: Angabe der Kunden und Aufträge, mit den Aufträgen, die die Kunden tatsächlich getätigt haben – also: nicht alle Kombinationen, sondern die passenden Kombinationen. select Kunde.KndNr, Kunde.Name, Auftrag.AuftrNr, Auftrag.Datum from Kunde join Auftrag on Kunde.KndNr = Auftrag.KndNr Ausgabe: kndnr | name | auftrnr | datum --------+------+---------+-----------100101 | Kehl | 1001 | 2006-10-12 100101 | Kehl | 1003 | 2007-03-01 100102 | Kehl | 1002 | 2006-02-12 (3 rows) Variante mit using select Kunde.KndNr, Kunde.Name, Auftrag.AuftrNr, Auftrag.Datum from Kunde join Auftrag using (KndNr) 19 SQL – eine Einführung Variante natural join select Kunde.KndNr, Kunde.Name, Auftrag.AuftrNr, Auftrag.Datum from Kunde natural join Auftrag Variante SQL89 select Kunde.KndNr, Kunde.Name, Auftrag.AuftrNr, Auftrag.Datum from Kunde, Auftrag where Kunde.KndNr = Auftrag.KndNr Bemerkung MS SQL Server unterstützt weder die Form mit „using“ noch „natural join“. SQL-Anweisung 40. Der Verbund mit Filterbedingung Beispiel: Welche Weine sind beim Auftrag mit der Auftragsnummer 1003 zu liefern? select AuftrNr, Bez, Weingut, Jahrgang from AuftrPos join Artikel using (ArtNr) where AuftrNr = 1003 Ausgabe: auftrnr | bez | weingut | jahrgang ---------+---------------------+-----------+---------1003 | Les Châteaux | Louis Max | 2002 1003 | Chablis | Louis Max | 2005 1003 | Château Caraguilhes | Louis Max | 2005 (3 rows) SQL-Anweisung 41. Der Verbund mehrerer Tabellen Beispiel: Kundennummer, Auftragsnummer und Angaben zu den bestellten Weinen von Auftrag 1001 select KndNr, AuftrNr, Bez, Weingut, Jahrgang from Auftrag join AuftrPos using (AuftrNr) join Artikel using (ArtNr) where AuftrNr = 1001 order by ArtNr Ausgabe: kndnr | auftrnr | bez | weingut | jahrgang --------+---------+---------------------+---------------+---------100101 | 1001 | Les Châteaux | Louis Max | 2002 100101 | 1001 | Chablis | Louis Max | 2005 100101 | 1001 | Château Caraguilhes | Louis Max | 2005 100101 | 1001 | Le Cop de Cazes | Domaine Cazes | 2004 (4 rows) 20 SQL – eine Einführung SQL-Anweisung 42. Der Verbund mehrerer Tabellen Beispiel: Name des Kunden, Auftragsnummer und Weine des Auftrags 1001? select Name, AuftrNr, Bez, Weingut, Jahrgang from Kunde join Auftrag using (KndNr) join AuftrPos using (AuftrNr) join Artikel using (ArtNr) where AuftrNr = 1001 order by ArtNr Ausgabe: name | auftrnr | bez | weingut | jahrgang ------+---------+---------------------+---------------+---------Kehl | 1001 | Les Châteaux | Louis Max | 2002 Kehl | 1001 | Chablis | Louis Max | 2005 Kehl | 1001 | Château Caraguilhes | Louis Max | 2005 Kehl | 1001 | Le Cop de Cazes | Domaine Cazes | 2004 (4 rows) SQL-Anweisung 43. Der Verbund mehrerer Tabellen Beispiel: Kundennummer und Name der Kunden, die einen Weißwein bestellt haben select distinct KndNr, Name from Kunde join Auftrag using (KndNr) join AuftrPos using (AuftrNr) join Artikel using (ArtNr) where Farbe = ’weiß’ Ausgabe: kndnr | name --------+-----100101 | Kehl (1 row) SQL-Anweisung 44. Der Verbund mit ein und derselben Tabelle (selfjoin) Beispiel: Gibt es zwei Kunden mit demselben Namen, aber verschiedenen Anschriften; wenn ja, wen? select A.Name, A.Vorname, A.Str, A.PLZ, A.Ort from Kunde A, Kunde B where A.Name = B.Name and A. Vorname = B.Vorname and not (A.Str = B.Str and A.PLZ = B.PLZ and A.Ort = B.Ort) 21 SQL – eine Einführung Ausgabe: name | vorname | str | plz | ort ------+---------+---------------+-------+------------Kehl | Thomas | Im Riesling 3 | 68734 | Eltville Kehl | Thomas | Weinstr. 3 | 79675 | Kaiserstuhl (2 rows) Variante select A.Name, A.Vorname, A.Str, A.PLZ, A.Ort from Kunde A, Kunde B where A.Name = B.Name and A. Vorname = B.Vorname and (A.Str <> B.Str or A.PLZ <> B.PLZ or A.Ort <> B.Ort) Variante mit Tupelvergleich select A.Name, A.Vorname, A.Str, A.PLZ, A.Ort from Kunde A, Kunde B where (A.Name, A.Vorname) = (B.Name, B.Vorname) and (A.Str, A.PLZ, A.Ort) <> (B.Str, B.PLZ, B.Ort) Kombination von Daten aus verschiedenen Tabellen (Mengenoperationen) SQL-Anweisung 45. Der Operator union Beispiel: Name, Straße bzw Postfach, PLZ Ort aller Lieferanten und Kunden select Vorname || ’ ’ PLZ || ’ ’ || Ort union select Firma as Name, PLZ || ’ ’ || Ort || Name as Name, Str as Anschrift1, as Anschrift2 from Kunde ’PF ’|| Postfach as Anschrift1, as Anschrift2 from Lieferant Ausgabe: name | anschrift1 | anschrift2 -------------------+--------------------+------------------Bremer Weinkontor | PF 56 | 28195 Bremen Karin Riesling | 67, Rue du Château | F-68567 Colmar Thomas Kehl | Im Riesling 3 | 68734 Eltville Thomas Kehl | Weinstr. 3 | 79675 Kaiserstuhl Weinimport Lehr | PF 45367 | F-68567 Colmar (5 rows) 22 SQL – eine Einführung SQL-Anweisung 46. Der Operator intersect Beispiel: Alle Orte, an denen sowohl ein Kunde als auch ein Lieferant wohnt select Ort from Kunde intersect select Ort from Lieferant Ausgabe: ort -------Colmar (1 row) Bemerkung Der Operator „intersect“ wird von MS SQL Server erst ab der Version von 2005 unterstützt. SQL-Anweisung 47. Der Operator except Beispiel: Alle Orte, an denen ein Kunde wohnt, jedoch kein Lieferant select Ort from Kunde except select Ort from Lieferant Ausgabe: ort ------------Eltville Kaiserstuhl (2 rows) Bemerkung Der Operator „except“ wird von MS SQL Server erst ab der Version von 2005 unterstützt. Berücksichtigung unbekannter Information (Äußerer Verbund) SQL-Anweisung 48. Der innere Verbund (inner join) Beispiel: Alle Kunden mit ihren Aufträgen select KndNr, Name, Vorname, Ort, AuftrNr, Datum from Kunde join Auftrag using (KndNr) 23 SQL – eine Einführung Ausgabe: kndnr | name | vorname | ort | auftrnr | datum --------+------+---------+-------------+---------+-----------100101 | Kehl | Thomas | Kaiserstuhl | 1001 | 2006-10-12 100101 | Kehl | Thomas | Kaiserstuhl | 1003 | 2007-03-01 100102 | Kehl | Thomas | Eltville | 1002 | 2006-02-12 (3 rows) SQL-Anweisung 49. Der innere Verbund zeigt nicht alle Kunden Beispiel: Kundennummer ohne Aufträge select KndNr from Kunde except select KndNr from Auftrag Ausgabe: kndnr -------100105 (1 row) Wir sehen: die Kundennumer (es handelt sich um die Kundin ‚Riesling‘) ist zwar in der Kundentabelle verzeichnet. Da sie aber bisher noch nichts bestellt hat, erscheint sie nicht in der Aufstellung der Kunden mit ihren Aufträgen. Oft möchte man aber alle Kunden mit ihren Aufträgen ausgeben, also auch diejenigen Kunden, die keinen Auftrag erteilt haben. Hierzu verwendet man den äußeren Verbund (outer join), der auch gegebenenfalls fehlende Information berücksichtigt. SQL-Anweisung 50. Der linke äußere Verbund left outer join Beispiel: Alle Kunden mit ihren Aufträgen, ggfs auch ohne Auftrag select KndNr, Name, Vorname, Ort, AuftrNr, Datum from Kunde left outer join Auftrag using (KndNr) Ausgabe: kndnr | name | vorname | ort | auftrnr | datum --------+----------+---------+-------------+---------+-----------100101 | Kehl | Thomas | Kaiserstuhl | 1001 | 2006-10-12 100101 | Kehl | Thomas | Kaiserstuhl | 1003 | 2007-03-01 100102 | Kehl | Thomas | Eltville | 1002 | 2006-02-12 100105 | Riesling | Karin | Colmar | | (4 rows) 24 SQL – eine Einführung Jetzt erscheint auch die Kundin ‚Riesling‘, für sie kann es natürlich keine Werte zu „AuftrNr“ und „Datum“ geben, deshalb wird in die Ergebnistabelle in diese Felder <null> eingetragen (in psql durch Leerstellen dargestellt). Wir haben unsere Beispieldatenbank so eingerichtet, dass es einen Auftrag ohne Angabe einer Kundennummer geben kann. Dies ist zwar nicht wirklich realistisch, ermöglicht es uns jetzt aber auch die weiteren Formen des äußeren Verbunds an diesem Beispiel zu zeigen. SQL-Anweisung 51. Der äußere Verbund right outer join Beispiel: Alle Aufträge ggfs. auch ohne Kunde select KndNr, Name, Vorname, Ort, AuftrNr, Datum from Kunde right outer join Auftrag using (KndNr) Ausgabe: kndnr | name | vorname | ort | auftrnr | datum --------+------+---------+-------------+---------+-----------100101 | Kehl | Thomas | Kaiserstuhl | 1001 | 2006-10-12 100101 | Kehl | Thomas | Kaiserstuhl | 1003 | 2007-03-01 100102 | Kehl | Thomas | Eltville | 1002 | 2006-02-12 | | | | 1004 | 2006-02-12 (4 rows) SQL-Anweisung 52. Der äußere Verbund full outer join Beispiel: Alle Aufträge ggfs. auch ohne Kunde und alle Kunden ggfs. auch ohne Auftrag select KndNr, Name, Vorname, Ort, AuftrNr, Datum from Kunde full outer join Auftrag using (KndNr) Ausgabe: kndnr | name | vorname | ort | auftrnr | datum --------+----------+---------+-------------+---------+-----------100101 | Kehl | Thomas | Kaiserstuhl | 1001 | 2006-10-12 100101 | Kehl | Thomas | Kaiserstuhl | 1003 | 2007-03-01 100102 | Kehl | Thomas | Eltville | 1002 | 2006-02-12 | | | | 1004 | 2006-02-12 100105 | Riesling | Karin | Colmar | | (5 rows) Bemerkung Man kann bei den outer joins das Schlüsselwort „outer“ auch weglassen, d.h. „left join“ usw. genügt. 25 SQL – eine Einführung Geschachtelte Anweisungen In SQL ist es möglich, an allen Stellen, an denen ein Wert oder ein Bezeichner eines Wertes angegeben werden kann, auch einen Ausdruck anzugeben, dessen Ergebnis ein Wert ist. Ebenso kann an Stelle einer Tabelle ein Ausdruck angegeben werden, der eine Tabelle zum Ergebnis hat. Diese Eigenschaft einer Programmiersprache wird oft „Orthogonalität“ genannt. SQL hat in diesem Sinne die Eigenschaft der Orthogonailtät seit SQL92. SQL-Anweisung 53. Orthogonalität I Beispiel: Anzahl von Weiß- und Rotweinen im Angebot select (select where (select where count(*) from Artikel Farbe = ’weiß’) as "Anz Weißweine", count(*) from Artikel Farbe = ’rot’) as "Anz Rotweine" Ausgabe: Anz Weißweine | Anz Rotweine ---------------+-------------2 | 2 (1 row) SQL-Anweisung 54. Orthogonalität II Beispiel: Adressen von Lieferanten oder Kunden in Frankreich select * from (select Vorname || ’ ’ || Name as Name, Str as Anschrift1, PLZ || ’ ’ || Ort as Anschrift2 from Kunde union select Firma as Name, ’PF ’|| Postfach as Anschrift1, PLZ || ’ ’ || Ort as Anschrift2 from Lieferant) as Anschriften where Anschrift2 like ’F-%’ Ausgabe: name | anschrift1 | anschrift2 -----------------+--------------------+---------------Karin Riesling | 67, Rue du Château | F-68567 Colmar Weinimport Lehr | PF 45367 | F-68567 Colmar (2 rows) 26 SQL – eine Einführung SQL-Anweisung 55. Orthogonalität III Beispiel: Artikel mit dem höchsten Preis select * from Artikel where Preis = (select max(Preis) from Artikel) Ausgabe: artnr | bez | weingut | jahrgang | farbe | preis --------+--------------+-----------+----------+-------+------100001 | Les Châteaux | Louis Max | 2002 | rot | 17.90 (1 row) SQL-Anweisung 56. Geschachteltes SQL Beispiel: Kunden, die einen Auftrag erteilt haben select KndNr, Name from Kunde where KndNr in (select KndNr from Auftrag) Ausgabe: kndnr | name --------+-----100101 | Kehl 100102 | Kehl (2 rows) SQL-Anweisung 57. Geschachteltes SQL Beispiel: Kunden, die noch keinen Auftrag erteilt haben select KndNr, Name from Kunde where KndNr not in (select KndNr from Auftrag where KndNr is not null) Ausgabe: kndnr | name --------+---------100105 | Riesling (1 row) Bemerkung Wegen der Möglichkeit, dass in der KndNr des Auftrags auch <null> stehen kann, müssen wir die Bedingung „is not null“ hinzufügen. In vielen Fällen ist das nicht nötig. 27 SQL – eine Einführung Geschachtelte SQL-Anweisungen kann man oft an Stelle eines Verbunds verwenden.2 SQL-Anweisung 58. Verbund versus Geschachtelter Anweisungen Beispiel: Kunden, die Weißweine bestellt haben select distinct KndNr, Name from Kunde join Auftrag using (KndNr) join AuftrPos using (AuftrNr) join Artikel using (ArtNr) where Farbe = ’weiß’ select KndNr, Name from Kunde where KndNr in ( select distinct KndNr from Auftrag where AuftrNr in ( select distinct AuftrNr from AuftrPos where ArtNr in (select ArtNr from Artikel where Farbe = ’weiß’) ) ) Ausgabe: kndnr | name --------+-----100101 | Kehl (1 row) SQL-Anweisung 59. Korrelierte geschachtelte SQL-Anweisungen Beispiel: Ältester Jahrgang der Weine jeder Farbe select A.Bez, A.Weingut, A.Jahrgang, A.Farbe from Artikel A where A.Jahrgang = (select min(B.Jahrgang) from Artikel B where A.Farbe = B.Farbe) Ausgabe: bez | weingut | jahrgang | farbe ---------------------+-----------+----------+------Les Châteaux | Louis Max | 2002 | rot Chablis | Louis Max | 2005 | weiß Château Caraguilhes | Louis Max | 2005 | rosé (3 rows) 2 Diese Eigenschaft ist nicht besonders „orthogonal“, denn dadurch gibt es oft viele verschiedene Möglichkeiten, dieselbe Anfrage auszudrücken. 28 SQL – eine Einführung SQL-Anweisung 60. Der Operator exists Beispiel: Alle Kunden, die einen Auftrag erteilt haben select * from Kunde where exists (select * from Auftrag where Auftrag.KndNr = Kunde.KndNr) Ausgabe: kndnr | name | vorname | str | plz | ort --------+------+---------+---------------+-------+------------100101 | Kehl | Thomas | Weinstr. 3 | 79675 | Kaiserstuhl 100102 | Kehl | Thomas | Im Riesling 3 | 68734 | Eltville (2 rows) SQL-Anweisung 61. Der Operator exists Beispiel: Alle Kunden, die keinen Auftrag erteilt haben select * from Kunde where not exists (select * from Auftrag where Auftrag.KndNr = Kunde.KndNr) Ausgabe: kndnr | name | vorname | str | plz | ort --------+----------+---------+--------------------+---------+-------100105 | Riesling | Karin | 67, Rue du Château | F-68567 | Colmar (1 row) 29 SQL – eine Einführung Gruppierung SQL-Anweisung 62. Gruppierung Beispiel: Zahl der Weine je Farbe select Farbe, count(*) as Anzahl from Artikel group by Farbe Ausgabe: farbe | anzahl -------+-------weiß | 2 rosé | 1 rot | 2 (3 rows) SQL-Anweisung 63. Gruppierung Beispiel: Zahl der Weine pro Jahrgang select Jahrgang, count(*) as Anzahl from Artikel group by Jahrgang Ausgabe: jahrgang | anzahl ----------+-------2002 | 1 2004 | 1 2005 | 2 | 1 (4 rows) SQL-Anweisung 64. Gruppierung und Null-Werte Beispiel: Zahl der Weine pro Jahrgang select Jahrgang, count(Jahrgang) as Anzahl from Artikel group by Jahrgang Ausgabe: jahrgang | anzahl ----------+-------2002 | 1 2004 | 1 2005 | 2 | 0 30 SQL – eine Einführung SQL-Anweisung 65. Gruppierung Beispiel: Zahl der Positionen pro Auftrag select AuftrNr, count(*) as "Zahl Positionen" from AuftrPos group by AuftrNr Ausgabe: auftrnr | Zahl Positionen ---------+----------------1001 | 4 1002 | 1 1003 | 3 (3 rows) SQL-Anweisung 66. Gruppierung Beispiel: Zahl der Flaschen pro Auftrag select AuftrNr, sum(Anzahl) as "Zahl Flaschen" from AuftrPos group by AuftrNr Ausgabe: auftrnr | Zahl Flaschen ---------+--------------1001 | 4 1002 | 48 1003 | 36 (3 rows) SQL-Anweisung 67. Bedingungen an die Gruppen Beispiel: Aufträge mit mehr als 10 Flaschen select AuftrNr, sum(Anzahl) as "Zahl Flaschen" from AuftrPos group by AuftrNr having sum(Anzahl) > 10 Ausgabe: auftrnr | Zahl Flaschen ---------+--------------1002 | 48 1003 | 36 (2 rows) 31 SQL – eine Einführung SQL-Anweisung 68. Gruppierung und Verbund Beispiel: Statistik der verkauften Flaschen pro Farbe des Weins select Farbe, sum(Anzahl) as "Zahl Flaschen" from AuftrPos join Artikel using (ArtNr) group by Farbe Ausgabe: farbe | Zahl Flaschen -------+--------------weiß | 13 rosé | 61 rot | 14 (3 rows) SQL-Anweisung 69. Der Operator having Beispiel: Aufträge mit mehr als 10 Rotweinen select AuftrNr, sum(Anzahl) as "Zahl Rotweine" from AuftrPos join Artikel using (ArtNr) where Farbe = ’rot’ group by AuftrNr having sum(Anzahl) > 10 Ausgabe: auftrnr | Zahl Rotweine ---------+--------------1003 | 12 (1 row) SQL-Anweisung 70. Gruppierung und Sortierung Beispiel: Warenwert der Aufträge sortiert nach Gesamtpreis (höchster zuerst) select AuftrNr, sum(Anzahl * Preis) as "Gesamtpreis" from AuftrPos join Artikel using (ArtNr) group by AuftrNr order by 2 desc Ausgabe: auftrnr | Gesamtpreis ---------+------------1002 | 715.20 1003 | 579.60 1001 | 55.20 (3 rows) 32 SQL – eine Einführung SQL-Anweisung 71. Gruppierung und fehlende Information Beispiel: Umsatz pro Kunde mit Angaben zu allen Kunden select KndNr, Name, sum(Anzahl * Preis) as "Gesamtumsatz" from Kunde left outer join Auftrag using(KndNr) left outer join AuftrPos using (AuftrNr) left outer join Artikel using (ArtNr) group by KndNr, Name Ausgabe: kndnr | name | Gesamtumsatz --------+----------+-------------100101 | Kehl | 634.80 100102 | Kehl | 715.20 100105 | Riesling | (3 rows) Modifizierende Anweisungen SQL-Anweisung 72. Die INSERT-Anweisung Beispiel: Einfügen eines neuen Artikels insert into Artikel(ArtNr, Bez, Weingut, Jahrgang, Farbe, Preis) values( 100104, ’Ancien Domaine’, ’Louis Max’, 2004, ’rot’, 17.00) Ausgabe: select * from Artikel artnr | bez | weingut | jahrgang | farbe | preis --------+---------------------+---------------+----------+-------+------100001 | Les Châteaux | Louis Max | 2002 | rot | 17.90 100002 | Chablis | Louis Max | 2005 | weiß | 15.50 100003 | Château Caraguilhes | Louis Max | 2005 | rosé | 14.90 604851 | Prosecco Val Monte | Cave Bellenda | | weiß | 7.60 145119 | Le Cop de Cazes | Domaine Cazes | 2004 | rot | 6.90 100104 | Ancien Domaine | Louis Max | 2004 | rot | 17.00 (6 Zeilen) Bemerkung Darüberhinaus sieht der SQL-Standard vor, dass die Werte für eine Insert-Anweisung durch eine Select-Anweisung angegeben werden können. Die Insert-Anweisung hat dann folgenden beispielhaften Aufbau: insert into Artikel(ArtNr, Bez, Weingut, Jahrgang, Farbe, Preis) select ArtNr, Bezeichnung, Domäne, Jahr, Farbe, Preis from ... 33 SQL – eine Einführung SQL-Anweisung 73. Die UPDATE-Anweisung Beispiel: Preiserhöhung des neu hinzugefügten Artikels um 10% update Artikel set Preis = Preis * 1.1 where ArtNr = 100104 Ausgabe: select * from Artikel artnr | bez | weingut | jahrgang | farbe | preis --------+---------------------+---------------+----------+-------+------100001 | Les Châteaux | Louis Max | 2002 | rot | 17.90 100002 | Chablis | Louis Max | 2005 | weiß | 15.50 100003 | Château Caraguilhes | Louis Max | 2005 | rosé | 14.90 604851 | Prosecco Val Monte | Cave Bellenda | | weiß | 7.60 145119 | Le Cop de Cazes | Domaine Cazes | 2004 | rot | 6.90 100104 | Ancien Domaine | Louis Max | 2004 | rot | 18.70 (8 rows) SQL-Anweisung 74. Ändern mehrerer Felder eines Datensatzes Beispiel: Der Wein mit der Artikelnummer 100104 wird durch einen anderen Wein ersetzt update Artikel set Bez = ’Nouveau Ligne’, Jahrgang = 2003, Preis = 14.30 where ArtNr = 100104 Ausgabe: select * from Artikel artnr | bez | weingut | jahrgang | farbe | preis --------+---------------------+---------------+----------+-------+------100001 | Les Châteaux | Louis Max | 2002 | rot | 17.90 100002 | Chablis | Louis Max | 2005 | weiß | 15.50 100003 | Château Caraguilhes | Louis Max | 2005 | rosé | 14.90 604851 | Prosecco Val Monte | Cave Bellenda | | weiß | 7.60 145119 | Le Cop de Cazes | Domaine Cazes | 2004 | rot | 6.90 100104 | Nouveau Ligne | Louis Max | 2003 | rot | 14.30 (8 rows) 34 SQL – eine Einführung SQL-Anweisung 75. Die DELETE-Anweisung Beispiel: Löschen des Weins mit der Artikelnummer 100104 delete from Artikel where ArtNr = 100104 Ausgabe: select * from Artikel artnr | bez | weingut | jahrgang | farbe | preis --------+---------------------+---------------+----------+-------+------100001 | Les Châteaux | Louis Max | 2002 | rot | 17.90 100002 | Chablis | Louis Max | 2005 | weiß | 15.50 100003 | Château Caraguilhes | Louis Max | 2005 | rosé | 14.90 604851 | Prosecco Val Monte | Cave Bellenda | | weiß | 7.60 145119 | Le Cop de Cazes | Domaine Cazes | 2004 | rot | 6.90 (5 rows) Bemerkung: Was geschieht, wenn man bei der DELETE-Anweisung die Bedingung hinter where vergisst? Datendefinition SQL-Anweisung 76. Anweisungen zum Erzeugen von Tabellen Beispiel: Auszug aus dem SQL-Skript zur Erzeugung der Datenbank zum Weinhandel create table ArtNr Bez Weingut Jahrgang Farbe Preis ); Artikel ( numeric(6) primary key, varchar(80) not null, varchar(80) not null, numeric(4), char(4) check (Farbe in (’rot’, ’rosé’, ’weiß’)), numeric(6,2) not null create table KndNr Name Vorname Str PLZ Ort ); Kunde ( numeric(6) primary key, varchar(40) not null, varchar(40), varchar(40), varchar(8) not null, varchar(40) not null create table Auftrag ( 35 SQL – eine Einführung AuftrNr Datum KndNr numeric(8) primary key, date not null, numeric(6) not null references Kunde(KndNr) on update cascade on delete restrict ); create table AuftrPos ( AuftrNr numeric(8) references Auftrag(AuftrNr) on update cascade, Anzahl integer not null, ArtNr numeric(6) not null references Artikel(ArtNr) on update cascade on delete restrict, primary key (AuftrNr, ArtNr) ); SQL-Anweisung 77. Name einer Tabelle ändern Beispiel: Umtaufen der Tabelle „Artikel“ in „Wein“ alter table Artikel rename to Wein SQL-Anweisung 78. Name einer Spalte ändern Beispiel: Umtaufen der Spalte „ArtNr“ in „WeinId“ alter table Wein rename column ArtNr to WeinID SQL-Anweisung 79. Eine neue Spalte hinzufügen Beispiel: Spalte mit Angabe zur Anbauart einfügen alter table Wein add column Anbauart varchar(12) check (Anbauart in (’biologisch’, ’herkömmlich’)) default ’herkömmlich’ Ausgabe: select WeinId, Bez, Anbauart from Wein weinid | bez | anbauart --------+---------------------+------------100002 | Chablis | herkömmlich 100003 | Château Caraguilhes | herkömmlich 604851 | Prosecco Val Monte | herkömmlich 145119 | Le Cop de Cazes | herkömmlich 100001 | Les Châteaux | herkömmlich (5 rows) 36 SQL – eine Einführung Burkhardt Renz Technische Hochschule Mittelhessen Fachbereich MNI Wiesenstr. 14 D-35390 Gießen Rev 6.2 – 18. April 2013 37