SQL -

SQL
Data Manipulation Language
insert:
zeilenweise (definitionsgemäss, freihe Reihenfolge, mit select)
update:
zeilenmengenweise (mit/ohne where)
delete:
zeilenmengenweise (mit/ohne where)
select:
zeilenmengenweise
(1)
Grundfunktionen:
ganze Tabelle
Spaltenauswahl
Zeilenauswahl
sortieren
doppelte Zeilen
arithmetische Operationen (zur Ausgabe)
(2)
where
Grundform
logische Operationen
arithmetische Operationen (als Bedingung)
(3)
Join
Tabellenverknüpfung (equi-joins, inner, outer)
(4)
Subselects
in where-Bedingung untergeordnete Wertermittlung
(5)
Mengenoperationen
*,
(6)
Gruppierung
Mengenfunktionen für Zeilengruppen
(7)
Trigger
zusätzliche (automatische) Kommandoausführung
count(*), sum, min, max, avg (total oder gruppenweise)
create table insertzaehler (zeilenzahl integer)
insert into insertzaehler values (0)
create trigger t1
on person
for insert
as update insertzaehler set zeilenzahl = zeilenzahl + 1
(8)
Beispiel:
cursor Die Datenbank liefert eine Zeilenmenge. Die Programmiersprache bearbeitet nur
einzeln. Deshalb wird ein Zwischenspeicher (Cursor) eingerichtet, aus dem mit
Cursoroperationen (first, next, prev, last, absolute n, relative n) einzelne Ergebnisse
abgerufen werden und in Programmvariablen übertragen werden können.
Tabelle artikel (anr, aname, apreis, amaterial, alagermenge)
Tabelle 'kunde': kundennr nachname vorname kalter
Tabelle 'bestellt': kundennr, artikelnr, menge
kunde
TFH Berlin/Steyer
bestellt
artikel
Datenmanipulation
Datenmanipulation genauer
Daten einfügen
direktes Einfügen mit und ohne Spaltennamen
insert into person (knr,nachname,vorname,konto)
values (3395,'Brenner','Karl',-4913.00)
insert into person
values (3390,'Heigert','Maria',-2.19)
Zeilen einfügen aus einer anderen Tabelle
insert into person
select knr, nachname, vorname, konto
from kunde
where konto < 0
Daten ändern, löschen
Die Sprachkonzepte von SQL kommen in geeigneter Weise in den Änderungskommandos wieder vor.
update kunde
set plz = 70441, ort = 'Stuttgart'
where knr = 3390
delete from kunde
Daten lesen: Spaltenauswahl
Durch Aufzählen der gewünschten Spalten in der ersten Zeile erhält man die Ergebnistabelle mit den
zugehörigen Spalten in derselben Anordnung.
select nachname, vorname
from kunde
select vorname, nachname
from kunde
select * from kunde
Daten lesen: Zeilenauswahl
Die Zeilenauswahl geschieht durch eine Wertebedingung in der WHERE-Zeile.
select anrede, vorname, nachname, ort
from kunde
where ort = 'Muenchen'
select nachname, ort, konto
from kunde
where konto = 0.0
select nachname, ort
from kunde
where konto is null
Daten lesen: Sortieren
select ort
from kunde
order by ort desc
TFH Berlin/Steyer
Datenmanipulation
Daten lesen: doppelte Zeilen vermeiden
select distinct ort
from kunde
Daten lesen: logische Verknüpfungen
Die Zeilenauswahl kann durch Logik im WHERE-Teil beeinflusst, z.B. verschärft werden.
select vorname, nachname, ort, konto
from kunde
where ort = 'Muenchen' or konto > 0
select vorname, nachname, ort, konto
from kunde
where ort = 'Muenchen' and konto > 0
=
GLEICH
<
KLEINER ALS
<=
KLEINER ODER GLEICH
>
GRÖSSER ALS
>=
GRÖSSER ODER GLEICH
<>
UNGLEICH
für mehrere Bedingungen
AND, OR
für Werte in einem Bereich
BETWEEN x AND y
für Werte in einer Menge
IN (x,y,z)
für Vergleich mit einem Teilwert LIKE '%abc% oder LIKE '_a_'
für verneinte Bedingungen
NOT
Klammernsetzung ist möglich
Daten lesen: Arithmetik
Arithmetische Ausdrücke sind an zwei Stellen möglich: In der WHERE-Bedingung dienen sie zur flexibleren
Zeilenauswahl: in der Ausgabeliste und in der WHERE-Bedingung
select hname, ort, preis
from hotel
where preis*7 < 500
select hname, ort, preis, preis+preis, preis*7
from hotel
Daten lesen: Tabellenverknüpfung
Zwei Tabellen
kunde
bestellt
knr nachname
knr menge
select kunde.nachname, bestellt.menge
from kunde, bestellt
where kunde.nachname = 'Barth' and kunde.knr=bestellt.knr;
Die Verknüpfung der beiden Tabellen erfolgt über zwei identische Spalten im WHERE-Teil,
die normale Wertebeschränkung ebenfalls.
TFH Berlin/Steyer
Datenmanipulation
ANSI Syntax:
select kunde.nachname, bestellt.menge
from kunde join bestellt
on kunde.knr=bestellt.knr;
where kunde.nachname = 'Barth'
Die beiden Tabellen sind durch das Wort „join“ verbunden.
Die Tabellenverknüpfung und normale Wertebeschränkung sind getrennt, die
Tabellenverknüpfungsbedingung steht nach ON.
Weitere Operatoren:
=
<
<=
<>
>
>=
(gleich)
(kleiner)
(kleiner oder gleich)
(ungleich)
(grösser)
(grösser oder gleich)
Inner Join:
SELECT *
FROM mitarbeiter, abteilung
WHERE wohnort = stadt
m_nr m_name
m_vorname
abt_nr wohnort
abt_nr abt_name
stadt
---------------------------------------------------------------------------------------------------------------------------2581 Kaufmann
Brigitte
a2
München
a2
Diagnose
München
25348 Keller
Hans
a3
München
a1
Beratung
München
2581 Kaufmann
Brigitte
a2
München
a1
Beratung
München
25348 Keller
Hans
a3
München
a2
Diagnose
München
Outer Join:
SELECT mitarbeiter.*, abteilung.abt_nr
FROM mitarbeiter, abteilung
WHERE wohnort *= stadt
m_nr m_name
m_vorname
abt_nr wohnort
abt_nr
---------------------------------------------------------------------------------------------25348 Keller
Hans
a3
München
a1
25348 Keller
Hans
a3
München
a2
2581 Kaufmann
Brigitte
a2
München
a1
2581 Kaufmann
Brigitte
a2
München
a2
29346 Probst
Andreas
a2
Augsburg
NULL
9031 Maier
Rainer
a2
Augsburg
NULL
10102 Huber
Petra
a3
Landshut
NULL
28559 Mozer
Sybille
a1
Ulm
NULL
18316 Müller
Gabriele
a1
Rosenheim
NULL
Subselects
An der Stelle von Werten können (in Klammern) untergeordnete Selects stehen, die diese Werte erst
ermitteln.
delete from bestellt where kundennr = (select kundennr from kunde where nachname = 'Müller') and
artikelnr = (select artikelnr from artikel where artikelname = 'Rahmen');
TFH Berlin/Steyer
Datenmanipulation
Daten lesen: V ereinigung von Ergebnismengen
select hname, ort, preis, 'UEBER 100'
from hotel
where preis > 100.00
union
select hname, ort, preis, 'UNTER 100'
from hotel
where not preis > 100.00
order by 1, 2
Die Teile müssen vereinigungsverträglich sein.
Mengenoperationen für die ganze Tabelle
select sum(gehalt) from Person
Mengenoperationen gruppenweise
select sum(gehalt) from Person group by wohnort
Mengenoperationen gruppenweise nur für bestimmte Gruppen
select sum(gehalt) from Person group by wohnort having sum(gehalt) < 5000
Trigger
Es gibt eine Tabelle person (pnr integer, pname char(12))
pnr
pname
-------------------------------222
Adam
223
Berta
Konstanter Eintrag:
create table ereignis (kommentar char(50))
create trigger insertprotokoll
on person
for insert
as insert into ereignis values ('Schon wieder ein Neuer')
insert into person ...
select * from ereignis
kommentar
-------------------------------Schon wieder ein Neuer
Update-Zähler:
create table zähler (anzahl integer)
insert into zähler values (0)
create trigger updatezähler
on person
for update
as update zähler set anzahl = anzahl + 1
TFH Berlin/Steyer
Datenmanipulation
Delete-Protokoll:
create table delperson (pnr integer, pname char(12))
create trigger deleteprotokoll
on person
for delete
as insert into delperson select * from deleted
delete from person ... und select * from delperson und select * from person
pnr
pname
-------------------------------223
Berta
pnr
pname
-------------------------------222
Adam
Die Tabelle deleted (mit gleicher Struktur wie person) enthält die durch einen Trigger gelöschten Werte.
Die Tabelle inserted (mit gleicher Struktur wie person) enthält die durch einen Trigger eingefügten Werte.
Beide Tabellen enthalten die durch einen Trigger geänderten Werte.
ausführliches Delete-Protokoll:
Neben den gelöschten Daten werden noch User und Datumuhrzeit protokolliert.
create table delperson2 (us char(10), ti datetime, pnr int, pname char(12))
create trigger deleteprotokoll2
on person
for delete
as insert into delperson2 select user_name(), getdate(), * from deleted
delete from person where pnr = 222
select * from delperson2
dbo
2007-12-04 11:19:00.497
TFH Berlin/Steyer
222
Adam