"SELECT … FROM … WHERE" - Klausel

4 Die Datenbankanfragesprache SQL
© Prof. Dr.-Ing. Wolfgang Lehner |
>
Einleitung
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
2
> Bestandteile einer Datenbanksprache
Datendefinitionssprache
(DDL = data definition language)
 Sprache zur Manipulation des Datenbankschemas
 (Meta-) Daten zur Beschreibung des Schemas (data dictionary)
 erlaubt die Spezifikation von weiteren Implementierungsdetails
Datenmanipulationssprache
(DML = data manipulation language)
 Aufgabe des Benutzers
 Einfügen, Löschen und Ändern von Datenobjekten in (aus) der Datenbank
 Suche nach Datenobjekten in der Datenbank
 Aufgabe des Datenbanksystems
 Übersetzung einer Anfrage, die ein Benutzer mittels von Objekten seiner externen Ebene
formuliert hat, in eine Anfrage, die auf Objekten der physischen Ebene effizient ausgeführt
werden kann.
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
3
> Datenbankanfragesprachen
Typen von Datenbanksprachen
 Prozedurale Datenbanksprachen
 Tupel- oder satzorientiert
 Programmierer denkt in Satzfolgen
 Navigation über Zugriffspfade durch die vorhandenen Daten
(findNext(), FindFirst() )
 Deskriptive Datenbanksprachen
 Mengenorientiert (typisch für das Relationenmodell)
 Programmierer denkt in Mengen von Sätzen mit bestimmten Eigenschaften
 Zugriff erfolgt durch inhaltliche Kriterien
(… alle Sätze mit der Eigenschaft …)
Zentral
 In prozeduralen Datenbankanfragesprachen wird spezifiziert, WIE das
Datenbanksystem etwas zu suchen hat
 In deskriptiven Datenbankanfragesprachen wird spezifiziert, WAS das
Datenbanksystem zu suchen hat
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
4
> Structured Query Language (= SQL)
Standardisierte Datenbanksprache (durch ISO)
 inzwischen von jedem relationalem DBS unterstützt (entsprechend viele Dialekte)
 entwickelt am IBM Research Lab in San Jose (CA) für das erste protypische
relationale Datenbanksystem R (ca. 1974)
 entworfen, um für den Laien benutzbar zu sein. … ist sie aber wohl doch nicht!
Erfordernisse einer vollständigen DB-Sprache
 Möglichkeiten zur Datendefinition und Anfrage
 Möglichkeiten zum Änderungsdienst
(Einfügen, Löschen, Modifizieren einer Menge von Tupeln)
 Definition von Sichten, physischen Hilfsmitteln, Integritätsbedingungen, …
 Zugriffskontrolle im Sinne des Datenschutzes
 Möglichkeiten zur Kopplung mit einer Wirtssprache
Eigenschaften
 Einsatz als interaktive Sprache und in Programmiersprachen (z. B. C, C++, …)
 Mischform aus relationaler Algebra und relationalem Kalkül mit einigen
Erweiterungen (arithmetische Ausdrücke und Aggregatsfunktionen)
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
5
> SQL-Standard und DBMS
Realisierung des SQL Standards
 Bis 1992 enthielt der Standard minimale Anforderungen, die die gängigen
Datenbanksysteme weitgehend erfüllten
 1992 erster "kompletter" Standard
 DBMS erfüllen die Niveau‘s "intermediate" und "full" nur unzureichend
 SQL:1999
 nimmt sehr viele neuen Anforderungen auf
 nach zu langer Entwicklungszeit nicht der wahre Erfolg
 DBMS 'hinken' hinterher
Befehle werden nur teilweise implementiert oder sind nicht in der standardisierten Form
vorhanden.
Es wird der Funktionsumfang in inkompatibler Weise erweitert, um sich von der
Konkurrenz abzugrenzen bzw. einen Marktvorteil zu erlangen.
 Meist ist der Standard den Implementierungen im Datenbanksystem voraus.
 …in einzelnen Features sind DBMS der Standardisierung voraus
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
6
>
Datendefinitionssprache (DDL)
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
7
> SQL Datentypen
Standardtypen (z.B. in ORACLE)





CHAR(size)
NUMBER(g,d)
VARCHAR(size)
DATE
DECIMAL(s)
Zeichenkette mit konstanter Länge size, size < 266
g = #Gesamtstellen, d = #Nachstellen
var. lange Zeichenkette mit maximaler Länge size (< 2000)
Datum: Jahr, Monat, …, Sekunde
s = #Stellen der Dezimalzahl
Spezielle Typen
 LONG
 CLOB
 LONG RAW
BLOB
 ROWID
 XML
© Prof. Dr.-Ing. Wolfgang Lehner |
variabel lange Zeichenkette mit maximal 2 GB
maximal ein Attribut vom Typ LONG in einer Relation
evtl. eingeschränkte Form der Anfragebearbeitung
variabel lange Bytekette mit maximal 2 GB
Typ einer Pseudospalte, die Schlüsselkandidat ist
Typ zur Ablage eines XML-Dokumentes
SQL
|
8
> Anlegen einer Relation
 nachfolgende Beispiele basieren auf dem folgenden Datenbankschema
 Kunde (KName, KAdr, Kto)
 Auftrag (KName, Ware, Menge)
 Lieferant (LName, LAdr, Ware, Preis)
CREATE TABLE: Relationenschema anlegen
 Syntax
CREATE TABLE <Relationen-Name> (<Spaltendefinition>[, <Spaltendefinition>]*)
wobei <Spaltendefinition>::=<Attributname> <Typ> [NOT NULL]
 Beispiel
CREATE TABLE Kunde
(KName
CHAR(20) NOT NULL,
KAdresse
VARCHAR(50),
Kto
DECIMAL(7))
 Beachte: NOT NULL sagt aus, dass das Attribut explizit belegt werden muss!!! Es
dürfen keine NULL-Werte auftreten.
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
9
> Ändern und Löschen einer Relation
ALTER TABLE: Relationenschema ändern
 Syntax
ALTER TABLE <Relationen-Name> ADD <Attributname> <Typ>
 Beachte: Eine Spalte wird rechts außen hinzugefügt, wobei NOT NULL nicht erlaubt
ist. Werte des neuen Attributes bestehender Tupel werden mit Nullwerten belegt.
DROP TABLE: Relationenschema löschen
 Syntax
DROP TABLE <Relationen-Name>
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
10
> Erzeugen einer Indexstruktur
CREATE INDEX: Index anlegen
 Indexe dienen der Verbesserung der Anfragezeit
 bezieht sich auf ein Attribut bzw. eine Menge von Attributen
 in kommerziellen Systemen: B-Bäume sowie Hashverfahren
• Syntax
CREATE [UNIQUE] INDEX <INDEX-Name>
ON <Relationen-Name>
(<Attributname> [<Ordnung>][, <Attributname> [<Ordnung>]]*) [CLUSTER]
 wobei gilt: <Ordnung> ::= ASC|DESC
 UNIQUE: Für alle Attributsnamen keine zwei Tupel mit gleichen Werten erlaubt
-> erfüllt Schlüsselbedingung.
 CLUSTER: Die Tupel der Relation werden tatsächlich in die Indexstruktur eingefügt und nicht nur die
Verweise (Primärindex)
-> nur ein Cluster-Index pro Relation.
 Beispiel
CREATE UNIQUE INDEX Kunden_idx ON Kunde (KName, KAdresse)
DROP INDEX: Index löschen
 Syntax
DROP INDEX <Index-Name>
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
11
> Erzeugen einer Datenbankansicht
CREATE VIEW: Sichten anlegen
 Sichten entsprechen externen DB-Schemata
 In relationalen Systemen werden Sichten als (abgeleitete) Relationen
aufgefasst, die durch Anfragen definiert werden.
 Syntax
CREATE VIEW <Sicht-Name> [(Attributname>[, <Attributname>]*)] AS
<subquery>
 Beispiel
CREATE VIEW Gute Kunden AS
SELECT * FROM Kunde WHERE Kto > 10
 Beachte: Das Schlüsselwort '*' stellt die Kurzschreibweise für die gesamte
Attributliste der in der FROM-Klausel angegebenen Relationen dar.
DROP VIEW: Sichten löschen
 Syntax: DROP VIEW <Sicht-Name>
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
12
>
Datenmanipulationssprache (DML)
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
13
> Grundschema für Abfragen
Grundschema zur Formulierung einer Anfrage in der DML
SELECT <Liste von Attributen>
FROM <ein oder mehrere Relationen>
[WHERE <Bedingung>]
SELECT KName
FROM Kunde
WHERE Kto < 0
Bemerkungen
 SELECT-Klausel entspricht der Projektion in der relationalen Algebra (und nicht der
Selektion).
 Die Bedingung nach der WHERE-Klausel enthält
 Vergleichsoperatoren (<, >, = …)
 boolsche Operatoren (AND, OR, NOT)
 Mengenoperatoren (IN, NOT IN) und Quantoren (ANY, SOME, ALL)
 Reihenfolge der Ausführung wird durch Klammern bestimmt
 Attribute mit gleichen Namen, die zu verschiedenen Relationen gehören, werden
mittels des Relationsnamen unterschieden (Punktnotation)
Beispiel: R.A, S.B, wobei R, S Relationen und A, B Attribute bezeichnen.
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
14
> Algebra-Operationen in SQL
Relation R
SELECT *
FROM R
Projektion πA,C(R)
SELECT
FROM R
DISTINCT A, C -- keine standardmäßige Duplikateeleminierung !!!
Selektion σB=b(R)
SELECT *
FROM R
WHERE B = b
Kartesisches Produkt R x S
SELECT *
FROM R, S
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
15
> Algebra-Operationen in SQL (2)
Theta-Join auf Relationen R(A,B) und S(C,D): RBΦDS
SELECT *
FROM R, S
WHERE B Φ D
Vereinigung der Relationen R und T (R und T haben die gleichen
Attribute)
SELECT * FROM R
UNION
SELECT * FROM T
Differenz der Relationen R und T (R und T haben die gleichen
Attribute)
SELECT * FROM R
MINUS
SELECT * FROM T
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
16
> Algebra-Operationen in SQL (3)
Allgemeine Bedeutung "SELECT … FROM … WHERE" - Klausel
SELECT A, B, C, …
FROM R, S, T, …
WHERE Bedingung
 entspricht in der relationalen Algebra
πA,B,C, …(σBedingung(R x S x T x …))
Bemerkung
 Die SELECT-Klausel alleine beseitigt keine Duplikate in der Ergebnisrelation.
 Duplikateeliminierung wird durch Hinzufügen des Schlüsselwortes "DISTINCT"
erzwungen.
SELECT DISTINCT A, B, C, …
FROM R, S, T, …
WHERE Bedingung
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
17
> Beispielanfragen
Beispielszenario
 Kunde (KName, KAdr, Kto)
 Auftrag (KName, Ware, Menge)
 Lieferant (LName, LAdr, Ware, Preis)
Welche Lieferanten liefern "CD-Rom-" oder "DVD-Drives"?
SELECT DISTINCT LName
FROM Lieferant
WHERE WARE = 'CD-Rom'
OR
WARE = 'DVD'
Welche Lieferanten liefern irgendetwas, das Huber bestellt hat?
SELECT DISTINCT LName
FROM Lieferant, Auftrag
WHERE Lieferant.Ware = Auftrag.Ware
AND
Kname = 'Huber'
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
18
>
Sprachelemente jenseits der relationalen
Algebra
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
19
> Sprachelemente jenseits der rel. Algebra
Eigenschaften von Aggregatsfunktionen
 Transformation, Verdichtung einzelner Tupel zu einem Gesamtwert
 Funktion COUNT() kann auf eine Menge von Tupeln angewendet werden
 Funktionen SUM(), AVG(), MIN() und MAX() können auf eine Menge von Zahlen, die als Spalte einer
Relation gegeben ist, angewandt werden.
 Zusätzlich stehen die statistischen Größen STDDEV() (Standardabweichung) und VARIANCE()
(Varianz) zur Verfügung.
Wieviele Lieferanten gibt es?
SELECT COUNT(DISTINCT LName)
FROM
Lieferant
Wieviele DVD-Laufwerke sind insgesamt bestellt?
SELECT SUM(Menge)
FROM
Auftrag
WHERE Ware = 'DVD'
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
20
> Gruppieren und Ordnen
Erweiterte Form der "SELECT … FROM … WHERE" - Klausel
SELECT …..
FROM …..
[WHERE …..]
[GROUP BY <Liste von Attributen>
[HAVING <Bedingung>]]
[ORDER BY <Liste von Attributen>]
"GROUP BY" - Klausel
 Tupel mit gleichen Werten für die angegebenen Attribute werden in Gruppen
zusammengefasst
 Pro Gruppe erzeugt die Anfrage ein Tupel der Ergebnisrelation
 deshalb:
hinter der SELECT-Klausel sind nur Attribute mit einem Wert pro Gruppe zugelassen
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
21
> Gruppieren und Ordnen (2)
"HAVING" - Klausel
 Auswahl der Gruppen anhand der Bedingung (es dürfen nur Argumente mit einem
Wert pro Gruppe auftreten)
"ORDER BY" - Klausel
 wird nach der WHERE-, GROUP BY- und HAVING-Klausel ausgeführt.
 Ergebnisrelation wird nach einem oder mehreren Attributen absteigend (DESC) oder
aufsteigend (ASC) sortiert.
Beispiel
SELECT LName
FROM Lieferant
GROUP BY LName
HAVING COUNT(*) > 5
Merke
 COUNT(X) zählt jeweils nur die Anzahl von Werten in X mit von NULL verschiedenen
Werten!
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
22
> Beispiel zur Aggregation und Sortierung
 Erstelle eine alphabetisch geordnete Liste aller Waren, in der für jede Ware der
minimale, maximale und der Durchschnittspreis angegeben ist.
SELECT
Ware, MIN(Preis), MAX(Preis), AVG(Preis)
FROM
Lieferant
GROUP BY Ware
ORDER BY Ware
 Welche Waren werden nur von einem Lieferanten geliefert?
SELECT
Ware
FROM
Lieferant
GROUP BY Ware
HAVING
COUNT(*) = 1
 Sortiere die Bestellungen nach Waren, für jede Ware nach der Größe der Bestellung
SELECT
*
FROM
Auftrag
ORDER BY Ware, Menge DESC
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
23
> Innere und äußere Verbundoperationen
Alternative Schreibweise zur "normalen" Verbundoperation
 SELECT *
FROM x, y
WHERE x.z = y.z
kann analog formuliert werden als
SELECT *
FROM x INNER JOIN y ON x.z = y.z
Outer-Joins
 unterschieden werden: rechte, linke und vollständige Outer-Joins
SELECT *
FROM x RIGHT OUTER JOIN y ON x.z = y.z
SELECT *
FROM x LEFT OUTER JOIN y ON x.z = y.z
SELECT *
FROM x FULL OUTER JOIN y ON x.z = y.z
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
24
> Tabellenausdrücke
In der FROM-Klausel kann ein beliebiger SELECT-Ausdruck
stehen
SELECT *
FROM (SELECT x, y, z
FROM …) TempTab
WHERE …
 der geschachtelten Tabelle muss ein Name zugewiesen werden
 kann auch ein (beliebiger externer) Funktionsaufruf sein!
Beispiel
 Gemeinsamer Durchschnittspreis von CD-Rom und DVD-Laufwerken
SELECT AVG(Preis)
SELECT AVG(Preis)
FROM (SELECT Preis
FROM TABLE (JavaGetPrices(…)) x
FROM
Lieferant
-- beliebige Java-Fkt!
WHERE
Ware = 'CD-Rom'
OR
Ware = 'DVD') x
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
25
> Tupelvariablen
Benennung von Relationen
 Anfragen sollen auch Relationen mit sich selber verknüpfen können
 dafür notwendig die Benennung von Relationen -> Tupelvariablen
Beispiele
 Gesucht sind Namen und Adressen aller Kunden, deren Kontostand kleiner als der
von Huber ist.
SELECT K1.KName, K1.KAdr
FROM Kunde K1, Kunde K2
WHERE K1.Kto < K2.Kto
AND
K2.KName = 'Huber'
 Finde alle Paare von Lieferanten, die eine gleiche Ware liefern.
SELECT DISTINCT L1.LName, L2.LName
FROM Lieferant L1, Lieferant L2
WHERE L1.Ware = L2.Ware
AND L1.LName < L2.LName
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
26
> Geschachtelte Anfragen
Prinzip der geschachtelten Anfragen
 Bedingungen in der WHERE-Klausel können die folgende Form haben:
 Subquery muss eine Relation mit einem Attribut liefern
<Attributname> IN <subquery>
oder
 Bedingung ist wahr, falls Subquery nicht leer ist
[NOT] EXISTS <Subquery>
Beispiel
 Welche Lieferanten liefern irgendetwas, das Huber bestellt hat?
SELECT DISTINCT LName
FROM Lieferant
WHERE Ware IN (SELECT Ware
FROM Auftrag
WHERE KName = 'Huber')
Wie kann die gleiche Anfrage formuliert werden, ohne dabei eine Subquery zu
benutzen?
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
27
>
Weitere Sprachelemente von SQL
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
28
> Weitere Sprachelemente von SQL
Verwendung von "ALL" und "SOME/ANY"
 Einige Anfragen mit Allquantoren können durch den ALL-Operator formuliert
werden.
 Gib für alle Waren die Namen der günstigsten Lieferanten aus!
SELECT Name, Ware
FROM Lieferant L
WHERE Preis <= ALL (SELECT Preis
FROM Lieferant
WHERE Ware = L.Ware)
 Eine Bedingung mit SOME bzw. ANY ist erfüllt, wenn sie für mindestens ein Element
der Menge erfüllt ist.
Arithmetische Ausdrücke in der SELECT-Klausel
 Gib die Preise der Waren der einzelnen Lieferanten in DM an!
SELECT LName, Ware, Preis * 1,9554
FROM
Lieferant
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
29
> Weitere Sprachelemente von SQL (2)
Suche nach Teilstrings
 Schreibweise: <Attributname> LIKE <Zeichenmuster>
SELECT *
FROM Kunde
WHERE KAdr LIKE '%Dresden%'
 Beachte
 Das SQL-Schlüsselzeichen '%' repräsentiert einen beliebigen String, während '_' ein einziges
Zeichen markiert.
 Negierte Form: KAdr NOT LIKE '%Dresden%' ist äquivalent zu NOT(KAdr LIKE '%Dresden%'
Test auf NULL-WERT
 Schreibweise: <Attributname> IS [NOT] NULL
 Gib alle Kundennamen aus, deren Adresse in der Datenbank nicht gespeichert ist!
SELECT KName
FROM Kunde
WHERE KAdr IS NULL
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
30
> Semantik von NULL-Werten
Typen von NULL-Werten
 not applicable
 zeigt an, dass ein Sachverhalt nicht anwendbar ist, bzw. ein Wert in der realen Welt nicht
vorkommt
 Beispiel: Typ des Videosystems bei einer Waschmaschine
 not available
 zeigt an, dass ein Wert (noch) nicht bekannt ist
 Beispiel: Typ des Videosystems bei einem Videorecorder
Beachtenswert im Umgang mit NULL-Werten
 es gilt immer: (NULL == NULL) ist immer falsch!
 COUNT(X) = 0, falls die Werte in X keinen von NULL verschiedenen Wert aufweisen!
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
31
>
Änderungsoperationen
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
32
> Tupelkonstruktor
Konstruktion eines Tupels aus mehreren Attributwerten: VALUES ()
VALUES (4711);
1
----------4711
VALUES (CURRENT DATE);
1
----------03/23/2009
VALUES ('Wolfgang', 'Lehner', 'Dresden'),
('Hartmut', 'Wedekind', Erlangen);
1
2
3
-------------- -------------- -------------Wolfgang Lehner
Dresden
Hartmut Wedekind Erlangen
VALUES (CURRENT TIMESTAMP - '2009-01-08-03.50.00.000000');
1
-------------------------------310930085355.773001
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
33
> Änderungsoperationen
INSERT: Einfügen von Tupeln
INSERT INTO <Relationen-Name>[(<Attributname> [, <Attributname>]*)]
VALUES (<Konstante> [, Konstante]*)
oder
INSERT INTO <Relationen-Name>[(<Attributname> [, <Attributname>]*)]
SELECT … FROM … WHERE …
DELETE: Löschen von Tuplen
DELETE
FROM <Relationen-Name>
[WHERE <Bedingung>]
UPDATE: Verändern von Tupeln
UPDATE <Relationen-Name>
SET <Attributname> = <Ausdruck> [, <Attributname> = <Ausdruck>]*
[WHERE <Bedingung>]
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
34
> Beispiele zu Änderungsoperationen
 Füge den Kunden Schmidt mit dem Kontostand 0 € ein
INSERT INTO Kunde (KName, Kto) VALUES ('Schmidt', 0)
 Erhöhe den Kontostand von Schmidt um 200
UPDATE Kunde
SET
Kto = Kto + 200
WHERE KName = 'Schmidt'
 Konvertiere alle Kontostände in Euro!
UPDATE Kunde
SET
Kto = Kto/1,9554
 Lösche alle Kunden, deren Kontostand höher ist als der Durchschnitt
DELETE
FROM Kunde
WHERE Kto > (SELECT AVG(Kto)
FROM Kunde)
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
35
> Zusammenfassung
Eigenschaften von Anfragesprachen
 prozedural versus deskriptiv
 Data Definition Language und Data Manipulation Language
Structured Query Language: SQL




Reflexion der Operatoren der relationalen Algebra
Geschachtelte Anfragen / Tupelvariable
Gruppierung, Aggregation, Sortierung
Rekursive Anfrage
Spezifikation von Integritätsbedingungen
 Referentielle Integrität
© Prof. Dr.-Ing. Wolfgang Lehner |
SQL
|
36