Folien (SQL) - sven.köppel.org

SQL
Structured Query Language
1
Grundlagen der Programmierung II
DBIS - SS2009
DBS – Sprachen
Das DBS stellt als Schnittstelle eine
Datenbanksprache(n) für die folgenden Zwecke
zur Verfügung:
– Datenabfrage und -manipulation – Data
Manipulation Language (DML)
– Verwaltung der Datenbank – Data Definition
Language (DDL)
– Berechtigungssteuerung – Data Control Language
(DCL)
z.B. SQL
2
Grundlagen der Programmierung II
DBIS - SS2009
Structured Query Language
• SQL ist für Relationale Datenbanksysteme!
Standards:
• SQL-1 von 1986 bzw. 1989 (ca. 120 Seiten)
• SQL-2 (SQL92) von 1992 (ca. 580 Seiten)
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
• SQL-3 (SQL99) von 2000 (ca. 1200 Seiten)
http://www.cse.iitb.ac.in/dbms/Data/Papers-Other/SQL1999/ansi-iso-9075-2-1999.pdf
• SQL 2003 - ISO/IEC 9075:2003
• SQL:2006 - ISO/IEC 9075-14:2006 (SQL/XML)
3
Grundlagen der Programmierung II
DBIS - SS2009
Syntaktische Grundform der SQLAnfrage
select
from
where
4
A1,A2, ... ,An
R1, R2, ... ,Rm
Bedingung;
Grundlagen der Programmierung II
DBIS - SS2009
Anfragen ohne Bedingungen
Filiale ( Name Leiter Stadt Einlagen )
select Name, Leiter from Filiale;
select Leiter, Name from Filiale;
select Stadt from Filiale;
select * from Filiale;
5
Grundlagen der Programmierung II
DBIS - SS2009
SQL verwirklicht das Prinzip der
„Vielfachmenge“ (engl. multiset). In den
Ergebnismengen können demnach Duplikate
auftreten.
Sind keine Duplikate erwünscht, müssen sie
explizit durch den Zusatz distinct entfernt
werden.
select distinct Stadt from Filiale;
6
Grundlagen der Programmierung II
DBIS - SS2009
Aggregatfunktionen
Die sog. Aggregatfunktionen können in der
select-Klausel anstelle von einzelnen
Attributen angegeben werden.
Ergebnis einer Aggregatfunktion ist ein Wert,
kein Tupel.
select count(∗) as AnzahlKonten
from Konto;
7
Grundlagen der Programmierung II
DBIS - SS2009
Aggregatfunktionen
• min( A ) zur Berechnung des Minimalwerts aller Tupel unter dem
Attribut A.
• max( A ) zur Berechnung des Maximalwerts aller Tupel unter dem
Attribut A.
• avg( [ distinct ] A ) zur Berechnung des Durchschnittswerts aller
Tupel unter dem Attribut A, wobei unter Angabe von distinct
mehrfach gleiche Werte nur einmal in die Berechnung eingehen.
• sum( [ distinct ] A ) zur Berechnung der Summe aller Tupel unter
dem Attribut A, wobei unter Angabe von distinct mehrfach gleiche
Werte nur einmal in die Berechnung eingehen.
• count( ∗ ) zum Zählen der Tupel der betrachteten Relation.
• count( [ distinct ] A) zum Zählen der Tupel der betrachteten
Relation, wobei zunächst eine Duplikateneliminierung bezogen auf
Werte unter dem Attribut A stattfindet.
8
Grundlagen der Programmierung II
DBIS - SS2009
where-Klausel
Bezüglich der Bedingung sind Vergleiche mit den
üblichen Operatoren, den logischen
Verknüpfungen and und or sowie beliebige
Klammerungen gestattet.
Konto ( KontoNr KundenNr FilialName Saldo )
select * from Konto
where Saldo > 5000 or KundenNr <= 100;
9
Grundlagen der Programmierung II
DBIS - SS2009
Range query
Als Vereinfachung für bestimmte Arten von
Abfragen zwischen zwei Grenzwerten steht
unter SQL der between Operator zur
Verfügung.
Achtung: 2. Wert sollte größer sein,
sonst gibt es kein Ergebnis!
select KontoNr
from Konto
where Saldo between 5000 and 30000;
10
Grundlagen der Programmierung II
DBIS - SS2009
Textvergleiche - LIKE
Kunde ( KundenNr Name Vorname Straße Stadt )
select KundenNr
from Kunde
where Name like 'To%' and
Vorname not like '_arste%';
Bem.:
% für beliebige Zeichenfolgen
_ für genau ein Zeichen
11
Grundlagen der Programmierung II
DBIS - SS2009
Null-Werte
select * from Konto where Name=NULL;
… würde nicht funktionieren  immer falsch!
… is [not] null …
select * from Konto where Name IS [NOT] NULL;
12
Grundlagen der Programmierung II
DBIS - SS2009
Anfrage über mehrere Relationen
Werden in der from-Klausel mehrere Relationen
spezifiziert, so erfolgt die Berechnung des
kartesischen Produktes.
Konto ( KontoNr KundenNr FilialName Saldo )
Filiale ( Name Leiter Stadt Einlagen )
select * from Filiale, Konto;
13
Grundlagen der Programmierung II
DBIS - SS2009
Join
Konto ( KontoNr KundenNr FilialName Saldo )
Filiale ( Name Leiter Stadt Einlagen )
select * from Filiale, Konto
where Filiale.Name = Konto.FilialName;
alternativ mit Alias
select * from Filiale F, Konto K
where F.Name = K.FilialName;
14
Grundlagen der Programmierung II
DBIS - SS2009
Mengenoperationen
Verknüpfung von Anfragen mittels:
– UNION [ALL] - Vereinigung
– INTERSECT - Durchschnitt
– MINUS
- Differenz
Durch diese Operatoren können einzelne selectAnfragen mit attributgleichen und
typkompatiblen Ergebnissen miteinander
verbunden werden.
15
Grundlagen der Programmierung II
DBIS - SS2009
select KundenNr, FilialName from Konto
union
select KundenNr, FilialName from Kredit;
Der union-Operator folgt, im Gegensatz zu
den meisten anderen SQL-Konstrukten, nicht
dem Ansatz der Vielfachmenge!
Will man eventuelle Duplikate nicht
beseitigen, so ist union all zu verwenden.
16
Grundlagen der Programmierung II
DBIS - SS2009
Unterabfragen
Es können auch Unteranfragen genutzt werden:
select Name from Kunde
where
KundenNr = max(select KundenNr from
Konto);
17
Grundlagen der Programmierung II
DBIS - SS2009
Schachtelung von Abfragen
•
•
•
•
[NOT] EXISTS
[NOT] IN
ANY
ALL
18
Grundlagen der Programmierung II
DBIS - SS2009
select Name, Vorname, Straße, Stadt
from Kunde KU
where not exists (
select *
from Kredit KR
where KU.KundenNr = KR.KundenNr);
Ergebnis: Alle Kunden, die (noch) keinen Kredit
haben.
19
Grundlagen der Programmierung II
DBIS - SS2009
Suche alle Kunden, die sowohl ein Konto als auch
ein Sparbuch haben.
select Name, Vorname from Kunde KU
where exists (
select * from Konto KO
where KU.KundenNr = KO.KundenNr
and exists (
select * from Sparbuch SP
where SP.KundenNr = KO.KundenNr
)
);
20
Grundlagen der Programmierung II
DBIS - SS2009
Alle Filialen, bei denen mindestens ein Kunde
ein Konto hat.
select * from Filiale
where Name in (
select distinct FilialName from Konto
);
21
Grundlagen der Programmierung II
DBIS - SS2009
Alle Kunden, die in der „Zeil“-Filiale ein Konto
und einen Kredit haben.
select distinct KundenNr from Konto
where FilialName = 'Zeil'
and KundenNr in (
select distinct KundenNr from Kredit
where FilialName = 'Zeil'
);
22
Grundlagen der Programmierung II
DBIS - SS2009
Alle Konten außer das, mit dem größten Saldo.
select * from Konto
where Saldo < any ( /* kleiner als irgendeines */
select Saldo from Konto
);
23
Grundlagen der Programmierung II
DBIS - SS2009
Das Konto mit dem kleinsten Saldo.
select * from Konto
where Saldo <= all ( /* kleiner gleich jedem */
select Saldo from Konto
);
Der Vergleich = any entspricht dabei dem inOperator und der Vergleich <> all entspricht not in.
24
Grundlagen der Programmierung II
DBIS - SS2009
Sortierung
select
A1,A2, ... ,An
from
R1, R2, ... ,Rm
[where
Bedingung]
[order by (Ai [asc|desc])+];
select * from Kredit
order by FilialName, KreditNr;
25
Grundlagen der Programmierung II
DBIS - SS2009
Tabellen erstellen
Eine Tabelle wird im Minimalfall mit ihrem
eindeutigen Namen sowie der Liste der
zugehörigen Attribute samt Domänen nach
folgendem Schema definiert:
create table Relations-Name (
Attribut-Name Domäne { , Attribut-Name
Domäne}∗
);
26
Grundlagen der Programmierung II
DBIS - SS2009
create table Konto (
KtoNr integer,
KundenName char(25),
FilialName char(25),
Saldo real
);
create table Kunde (
Name char(25),
Vorname char(25),
Straße char(25),
Stadt char(25),
GebDatum date
);
27
Grundlagen der Programmierung II
DBIS - SS2009
SQL-2 Datentypen
28
Grundlagen der Programmierung II
DBIS - SS2009
Primärschlüssel
Mittels der Klausel primary key kann eine unter
den Attributfolgen einer Relation – bei der
Definition der Tabelle – als Primärschlüssel
ausgezeichnet werden. Die Benutzung dieser
Klausel ist nur einmal pro Relation gestattet.
create table Konto (
KtoNr integer primary key not null,
KundenName char(25),
FilialName char(25),
Saldo real ) ;
29
Grundlagen der Programmierung II
DBIS - SS2009
Primärschlüssel
Wenn mehr als ein Attribut als Primärschlüssel
definiert werden sollen, wird die Klausel in der
Form primary key (Attributnamen-Liste)
verwendet.
create table Transaktion (
vonKtoNr integer not null,
anKtoNr integer not null,
Datum date not null,
Betrag real,
primary key (vonKtoNr, anKtoNr, Datum) ) ;
30
Grundlagen der Programmierung II
DBIS - SS2009
Einfügen von Tupeln
Um Daten einzufügen, spezifiziert man entweder
das Tupel, das eingefügt werden soll …
Die Werte für die Attribute der Tupel müssen aus
der Domäne der Attribute sein.
insert into Kunde
values ( 'Otto', 'Hans', 'Bäckerweg 12', 'Frankfurt',
'1970-12-01' ) ;
 Der Kunde "Hans Otto" wird eingefügt.
31
Grundlagen der Programmierung II
DBIS - SS2009
… oder schreibt eine Anfrage, die eine Menge von Tupeln als
Ergebnis hat, die eingefügt werden soll.
insert into Sparbuch
select KreditNr, 50.00, KundenName, FilialName from Kredit
where FilialName in (
select FilialName from Filiale where Stadt = 'Frankfurt'
);
Alle Kunden, die bei Filialen in Frankfurt einen Kredit
haben, bekommen ein Sparbuch mit einem Startkapital
von 50,- €.
32
Grundlagen der Programmierung II
DBIS - SS2009
Sichten (views)
Eine View ist eine Relation, die nicht Teil des
konzeptuellen Schemas der Datenbank ist, sondern
dem Benutzer als virtuelle Relation zur Verfügung
gestellt wird. Views können nicht gespeichert
werden sondern müssen für jede Anfrage, die sie
referenziert neu berechnet werden.
In SQL wird eine View folgendermaßen definiert:
create view View-Name as
<Anfrage-Ausdruck>
33
Grundlagen der Programmierung II
DBIS - SS2009
create view alleKunden as
( select KundenName, FilialName from Konto )
union
( select KundenName, FilialName from Kredit ) ;
Ab jetzt kann die View in Anfragen benutzt werden:
select KundenName from alleKunden
where FilialName = 'Westend';
34
Grundlagen der Programmierung II
DBIS - SS2009
Probleme mit Views
Obwohl Views sehr nützlich sein können,
bringen sie Probleme mit, wenn sie in update,
insert oder delete-Anfragen verwendet
werden.
Das Problem ist, die Veränderungen in den
virtuellen Relationen auf die wirklichen
Relationen, die im konzeptuellen Schema
vorhanden sind, zu übertragen.
35
Grundlagen der Programmierung II
DBIS - SS2009
create view Kredit_Info as
select FilialName, KreditNr, KundenName
from Kredit ;
Da SQL es erlaubt, eine View in jedem Ausdruck zu
verwenden, könnte man versucht sein, etwa Folgendes
auszuführen:
insert into Kredit_Info
values ('Westend', 143, 'Meiser');
Diese Einfüge-Operation muss zu einem insert in der
Relation Kredit führen, dafür fehlt allerdings der Wert
für das Attribut Betrag.
36
Grundlagen der Programmierung II
DBIS - SS2009
Es gibt zwei unterschiedliche Ansätze, um mit
diesem Problem umzugehen:
1.Zurückweisen der Operation, und eine
Fehlermeldung an den Benutzer.
2.Einfügen des Tupels (143, null, 'Meiser',
'Westend') in die Relation Kredit.
Der null-Wert repräsentiert hierbei einen nicht
existierenden Wert.
37
Grundlagen der Programmierung II
DBIS - SS2009
Nicht in diesen Folien
(aber in den anderen!)
• Die Operationen delete und update und
mögliche Anomalien dabei.
• Null-Werte
• Zwischenergebnisse
• …
38
Grundlagen der Programmierung II
DBIS - SS2009
Weitere Dinge …
•
•
•
•
•
•
•
GROUP BY
HAVING
Inner und Outer Join
Alter und Drop Table
Index
Foreign Key
… und vieles vieles mehr!!!!
– … auch abhängig vom DBMS und der SQL-Version
39
Grundlagen der Programmierung II
DBIS - SS2009