SQL - Benutzer-Homepage-Server der TH Mittelhessen

Mehr SQL
Views
SQL-Anweisung 1. Definition einer View
Beispiel:
Kontakte als View
create view Kontakte as
select Vorname || ’ ’ || Name as Name,
PLZ || ’ ’ || Ort as Anschrift2 from
union
select Firma as Name, ’PF ’|| Postfach
PLZ || ’ ’ || Ort as Anschrift2 from
Str as Anschrift1,
Kunde
as Anschrift1,
Lieferant
SQL-Anweisung 2. Verwendung einer View
Beispiel:
Teile der Kontakte ausgeben
select * from Kontakte
where Name like ’%Kehl%’
Ausgabe:
name
| anschrift1
|
anschrift2
-------------+---------------+------------------Thomas Kehl | Im Riesling 3 | 68734 Eltville
Thomas Kehl | Weinstr. 3
| 79675 Kaiserstuhl
(2 rows)
SQL-Anweisung 3. View löschen
Beispiel:
Kontakte löschen
drop view Kontakte
1
Mehr SQL
Benutzerdefinierte Funktionen
SQL-Anweisung 4. Eine einfache benutzerdefinierte Funktion
Beispiel:
Eine einfache Funktion erstellen
create function kartonpreis(numeric) returns numeric as $$
select $1 * 11;
$$ language sql
SQL-Anweisung 5. Einfache Funktionen verwenden
Beispiel:
Kartonpreis aus Preis berechnen
select ArtNr, Bez, kartonpreis(Preis) as "Kartonpreis"
from Artikel
Ausgabe:
artnr |
bez
| Kartonpreis
--------+---------------------+------------100001 | Les Châteaux
|
196.90
100002 | Chablis
|
170.50
100003 | Château Caraguilhes |
163.90
604851 | Prosecco Val Monte |
83.60
145119 | Le Cop de Cazes
|
75.90
(5 rows)
SQL-Anweisung 6. Funktion auf einer Tabelle
Beispiel:
Funktion erstellen
create function kartonpreis(Artikel) returns numeric as $$
select $1.preis * 11;
$$ language sql
Wir haben zwei Funktionen mit dem Namen „kartonpreis“ erstellt, die
sich durch ihre Parameter unterscheiden, d.h. SQL unterstützt das Überladen von Funktionen.
2
Mehr SQL
SQL-Anweisung 7. Verwendung der Funktion
Beispiel:
Kartonpreise ausgeben
select ArtNr, Bez, kartonpreis(Artikel.*) as "Kartonpreis"
from Artikel
Ausgabe:
artnr |
bez
| Kartonpreis
--------+---------------------+------------100001 | Les Châteaux
|
196.90
100002 | Chablis
|
170.50
100003 | Château Caraguilhes |
163.90
604851 | Prosecco Val Monte |
83.60
145119 | Le Cop de Cazes
|
75.90
(5 rows)
SQL-Anweisung 8. Funktionen löschen
Beispiel:
Beide Funktionen werden wieder entfernt
drop function kartonpreis(numeric);
drop function kartonpreis(artikel);
Benutzerdefinierte Funktionen und Prozeduren
Der SQL-Standard (ab SQL:1999) kennt benutzerdefinierte Funktionen
und Prozeduren.
Benutzerdefinierte Funktionen haben einen Rückgabewerte, benutzerdefinierte Prozeduren haben keinen Rückgabewert. Außerdem unterscheiden sie sich durch ihren Aufruf: benutzerdefinierte Prozeduren werden
mit dem SQL-Befehl call aufgerufen, wohingegen Funktionen in einem
Ausdruck auftreten.
PostgreSQL hat keine Implementierung der Call-Anweisung, deshalb nur
Beispiele von benutzerdefinierten Funktionen hier.
Trigger
3
Mehr SQL
SQL-Anweisung 9. Vorbereitung eines Szenarios mit Triggern
Beispiel:
Einrichten einer Tabelle für die Historie der Kundentabelle
create table HistorieKunde (
like Kunde
);
alter table HistorieKunde
add column Benutzer varchar(20);
alter table HistorieKunde
add column Aktion char(1) check (Aktion in (’i’, ’u’, ’d’));
alter table HistorieKunde
add column Zeitpunkt timestamp;
Die Tabelle HistorieKunde wollen wir verwenden, um alle Änderungen
an den Daten der Tabelle Kunde zu protokollieren.
SQL-Anweisung 10. Funktion programmieren
Beispiel:
Modifikation von Kunden protokollieren
create function auditKunde() returns trigger as $auditKunde$
begin
if ( tg_op = ’INSERT’ ) then
insert into HistorieKunde select new.*, user, ’i’, now();
return new;
elsif ( tg_op = ’UPDATE’ ) then
insert into HistorieKunde select new.*, user, ’u’, now();
return new;
elsif ( tg_op = ’DELETE’ ) then
insert into HistorieKunde select old.*, user, ’d’, now();
return old;
end if;
return null;
end;
$auditKunde$ language plpgsql;
SQL-Anweisung 11. Trigger erzeugen
Beispiel:
Trigger für das Protokoll von Änderungen an Kunden
create trigger InsertTrigger after insert
on Kunde for each row
execute procedure auditKunde();
4
Mehr SQL
create trigger UpdateTrigger after update
on Kunde for each row
execute procedure auditKunde();
create trigger DeleteTrigger after delete
on Kunde for each row
execute procedure auditKunde();
SQL-Anweisung 12. Triggerverwendung provozieren
Beispiel:
Neuen Kunden eingeben, ändern und löschen
insert into Kunde
values ( 200001, ’Neumann’, ’Klaus’, ’Hauptstr.’, ’35390’, ’Gießen’ );
update Kunde
set Str = ’Marktplatz’
where KndNr = 200001;
delete from Kunde
where KndNr = 200001;
SQL-Anweisung 13. Effekt des Triggers
Beispiel:
Protokoll inspizieren
select * from HistorieKunde
Ausgabe:
kndnr | name
|..|
str
|..| ben | a |
zeitpunkt
--------+---------+--+------------+--+-----+---+------------------------200001 | Neumann | | Hauptstr. | | br | i | 2009-04-02 12:11:28.555
200001 | Neumann | | Marktplatz | | br | u | 2009-04-02 12:11:28.665
200001 | Neumann | | Marktplatz | | br | d | 2009-04-02 12:11:28.665
(3 rows)
SQL-Anweisung 14. Trigger löschen
Beispiel:
Protokoll der Aktionen wieder entfernen
drop function auditKunde() cascade;
drop table HistorieKunde;
5
Mehr SQL
Ausgabe:
NOTICE:
NOTICE:
NOTICE:
drop cascades to trigger inserttrigger on table kunde
drop cascades to trigger deletetrigger on table kunde
drop cascades to trigger updatetrigger on table kunde
Abfrage war erfolgreich nach 150 ms. Keine Zeilen geliefert.
Sequenzen
Manchmal haben Entitäten, die in einer Tabelle gespeichert werden,
„natürliche“ Primärschlüssel. So ist z.B. die Matrikelnummer bei Studierenden eigens von der Studierendenverwaltung so vergeben worden,
dass sie als identifizierendes Merkmal verwendet werden kann. Andere
Systeme, wie z.B. in der Bibliothek können deshalb die Matrikelnummer
als Primärschlüssel einsetzen.
In anderen Fällen gibt es solch ein Merkmal nicht und es wird ein „künstlicher“ Primärschlüssel eingesetzt1 .
Ein solcher Schlüssel kann durch eine Anwendung oder durch das Datenbankmanagementsystem vergeben werden. DBMS sehen Mechanismen
zur Erzeugung von fortlaufenden Werten vor. In SQL ist der Mechanismus der Sequenzgenerator (sequence generator).
SQL-Anweisung 15. Sequenzen
Beispiel:
Erzeugen und Verwenden eines Sequenzgenerators mit Startwert 200000
create sequence KndNr_seq start 200000;
insert into Kunde(KndNr, Name, Vorname, PLZ, Str, Ort)
values( nextval(’KndNr_seq’), ’Neumann’, ’Holger’, ’60311’,
’Heinestr. 12’, ’Frankfurt am Main’);
select * from
Kunde;
Ausgabe:
kndnr |
name
| vorname |
str
|
plz
|
ort
--------+----------+---------+--------------------+---------+------------------100101 | Kehl
| Thomas | Weinstr. 3
| 79675
| Kaiserstuhl
100102 | Kehl
| Thomas | Im Riesling 3
| 68734
| Eltville
100105 | Riesling | Karin
| 67, Rue du Ch?teau | F-68567 | Colmar
200000 | Neumann | Holger | Heinestr. 12
| 60311
| Frankfurt am Main
(4 Zeilen)
1
Die Matrikelnummer ist aus Sicht der Studierendenverwaltung, die die Matrikelnummer ursprünglich vergibt, natürlich auch ein „künstlicher“ Schlüssel – nur für
andere Systeme ist er dann als vorhandenes eindeutiges Merkmal verwendbar.
6
Mehr SQL
Möchte man den aktuellen Wert wissen, kann man die Sequenz abfragen.
Man muss ja einer Kundenverwaltung z.B. wissen, welche Kundennummer das DBMS beim insert vergeben hat:
SQL-Anweisung 16. Sequenzen
Beispiel:
Ermitteln des aktuellen Werts der Sequenz
select currval(’KndNr_seq’);
Ausgabe:
currval
--------200000
(1 Zeile)
Man kann eine Sequenz auch bei der Definition einer Tabelle verwenden.
Dabei verwendet man nextval als Defaultwert für das entsprechende
Attribut der Tabelle. Beim Einfügen eines neuen Datensatzes lässt man
den Wert weg und das DBMS nimmt den nächsten Wert der Sequenz.
SQL-Anweisung 17. Sequenzen
Beispiel:
Verwenden eines Sequenz beim Erzeugen einer Tabelle
create sequence KndNr_seq start 100000;
create table Kunde (
kndnr numeric(6) primary key default nextval(’KndNr_seq’),
name varchar(40) not null,
vorname varchar(40),
str varchar(40),
plz varchar(8) not null,
ort varchar(40) not null
)
Rekursion in SQL
Für den folgenden Abschnitt verwenden wir die Datenbank „Twitter“,
die eine Tabelle FollowedBy hat, in der verzeichnet ist, welcher TwitterUser
welchen Follower hat.
Sehen wir uns die Tabelle mal an:
7
Mehr SQL
SQL-Anweisung 18. Twitter-Datenbank
Beispiel:
In Twitter gibt es TwitterUser und Follower in der Tabelle FollowedBy
select * from FollowedBy
Ausgabe:
twitteruser | follower
-------------+---------Jack
| Sarah
Sarah
| Justin
Sarah
| Jenny
Justin
| Jenny
Justin
| Tommy
(5 Zeilen)
Wenn man wissen möchte, welche Anhänger die Anhänger von ‚Jack‘
haben, dann kann man diese Anfrage mit der Technik des „Self-Joins“
lösen.
Im folgenden Beispiel wird ein solcher „Self-Join“ durchgeführt. Die Besonderheit gegenüber den früheren Beispielen, besteht in der „WITHKlausel“, in der man eine (virtuelle) Tabelle definieren kann, die dann
in der folgenden Anweisung verwendet werden kann.
SQL-Anweisung 19. SQL-Anweisung mit WITH
Beispiel:
Wir wollen die Anhänger der Anhänger von ’Jack’ finden
with
Follower(t,f) as (select F1.TwitterUser as t, F2.Follower as f
from FollowedBy F1, FollowedBy F2
where F1.Follower = F2.TwitterUser)
select t, f from Follower where t = ’Jack’
Ausgabe:
t
|
f
------+-------Jack | Jenny
Jack | Justin
(2 Zeilen)
Mit dieser Technik können wir aber nicht die Anhänger der Anhänger
der Anhänger. . . finden, wenn wir nicht wissen, wieviele Stufen es in der
Anhängerschaft gibt.
Beliebig tiefe Wege in dem Graphen, der die Anhängerschaft beschreibt,
finden wir mit rekursiven Anweisungen:
8
Mehr SQL
SQL-Anweisung 20. Rekursive SQL-Anweisung
Beispiel:
Wir wollen die Anhänger der Anhänger der Anhänger etc. von ’Jack’
finden
with recursive
Follower(t,f) as
(select TwitterUser as t, Follower as f from Followedby
union
select Follower.t, FollowedBy.follower as f
from Follower, FollowedBy
where Follower.f = FollowedBy.TwitterUser)
select t, f from Follower where t = ’Jack’
Ausgabe:
t
|
f
------+-------Jack | Sarah
Jack | Jenny
Jack | Justin
Jack | Tommy
(4 Zeilen)
SQL-Anweisung 21. Transitiver Abschluss
Beispiel:
Alle Beziehungen zwischen den Twitterern, egal über welchen Pfad
with recursive
Follower(t,f) as
(select TwitterUser as t, Follower as f from Followedby
union
select Follower.t, FollowedBy.follower as f
from Follower, FollowedBy
where Follower.f = FollowedBy.TwitterUser)
select t, f from Follower order by t
Ausgabe:
t
|
f
--------+-------Jack
| Sarah
Jack
| Jenny
Jack
| Justin
Jack
| Tommy
Justin | Tommy
Justin | Jenny
Sarah | Tommy
Sarah | Justin
Sarah | Jenny
(9 Zeilen)
9
Mehr SQL
Bisher hatte die Beispielrelation keine Zyklus. Wenn ein Zyklus besteht,
kann eine rekursive Abfrage zu einer unendlichen Schleife führen.
SQL-Anweisung 22. Zyklus in binärer Relation
Beispiel:
Zyklische Beziehung zwischen Twitterern erzeugen
insert into FollowedBy( TwitterUser, Follower)
values (’Tommy’, ’Jack’);
select * from FollowedBy;
Ausgabe:
twitteruser | follower
-------------+---------Jack
| Sarah
Sarah
| Justin
Sarah
| Jenny
Justin
| Jenny
Justin
| Tommy
Tommy
| Jack
(6 Zeilen)
SQL-Anweisung 23. Binäre Relation mit einem Zyklus, 1
Beispiel:
Alle Anhänger von „Jack“
with recursive
Follower(t,f) as
(select TwitterUser as t, Follower as f from Followedby
union
select Follower.t, FollowedBy.follower as f
from Follower, FollowedBy
where Follower.f = FollowedBy.TwitterUser)
select * from Follower where t = ’Jack’
Ausgabe:
t
|
f
------+-------Jack | Sarah
Jack | Jenny
Jack | Justin
Jack | Tommy
Jack | Jack
(5 Zeilen)
10
Mehr SQL
SQL-Anweisung 24. Binäre Relation mit einem Zyklus, 1
Beispiel:
Vorsicht: Folgende Anweisung endet nur, weil ein Timeout eingestellt
ist!!
set statement_timeout = ’1s’;
with recursive
Follower(t,f, depth) as
(select TwitterUser as t, Follower as f, 1 from Followedby
union
select Follower.t, FollowedBy.follower as f, Follower.depth + 1
from Follower, FollowedBy
where Follower.f = FollowedBy.TwitterUser)
select * from Follower where t = ’Jack’
Ausgabe:
FEHLER:
storniere Anfrage wegen Zeitüberschreitung
SQL-Anweisung 25. Binäre Relation mit einem Zyklus, 2
Beispiel:
Einfache Abhilfe: die Zahl der Ergebniszeilen limitieren
with recursive
Follower(t,f, depth) as
(select TwitterUser as t, Follower as f, 1 from Followedby
union
select Follower.t, FollowedBy.follower as f, Follower.depth + 1
from Follower, FollowedBy
where Follower.f = FollowedBy.TwitterUser)
select * from Follower where t = ’Jack’ limit 10
Ausgabe:
t
|
f
| depth
------+--------+------Jack | Sarah |
1
Jack | Jenny |
2
Jack | Justin |
2
Jack | Jenny |
3
Jack | Tommy |
3
Jack | Jack
|
4
Jack | Sarah |
5
Jack | Jenny |
6
Jack | Justin |
6
Jack | Jenny |
7
(10 Zeilen)
11
Mehr SQL
Sudoku in SQL
Mit rekursivem SQL kann man auch Sudoku lösen. Das geht so:
SQL-Anweisung 26. Sudoku in SQL
Beispiel:
Rekursives SQL löst Sudoku
-- Published by Marcin Mank in pgsql-general,
-- based on an Oracle version by Anton Scheffer
with recursive x(s, ind) as
( select sud, position(’ ’ in sud)
from (select ’ 24
38 6 72 91 481 7 3 96 48
97
69
51 75 9 8 414 16 57 9 96
83 ’::text
as sud) xx
union all
select substr( s, 1, ind - 1 ) || z || substr( s, ind + 1 ),
position(’ ’ in repeat(’x’,ind) || substr( s, ind + 1 ) )
from x, (select gs::text as z from Generate_series(1,9) gs) z
where ind > 0
and not exists ( select null
from generate_series(1,9) lp
where z.z = substr( s, ( (ind - 1 ) / 9 ) * 9 + lp, 1 )
or
z.z = substr( s, mod( ind - 1, 9 ) - 8 + lp * 9, 1 )
or
z.z = substr( s, mod( ( ( ind - 1 ) / 3 ), 3 ) * 3
+ ( ( ind - 1 ) / 27 ) * 27 + lp
+ ( ( lp - 1 ) / 3 ) * 6, 1 )
)
)
select s as "Lösung"
from x
where ind = 0;
Ausgabe:
Lösung
----------------------------------------------------------------------------------924156387637289154815743296148562973572391468369874512753928641481635729296417835
Burkhardt Renz
Technische Hochschule Mittelhessen
Fachbereich MNI
Wiesenstr. 14
D-35390 Gießen
Rev 6.1 – 2. Februar 2015
12