Datenmanipulation - Beuth Hochschule für Technik Berlin

Datenmanipulation
3 Datenmanipulation
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 einfügen, ändern, löschen
Die Sprachkonzepte von SQL kommen in geeigneter Weise in den Änderungskommandos wieder vor.
insert into kunde (knr,anrede,nachname,vorname,plz,ort,konto)
values (3914,'Herr','Fehrbach','Karl',22359,'Hamburg',0.00)
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
TFH Berlin/Steyer
3-1
Datenmanipulation
Daten lesen: Sortieren/doppelte Zeilen
select distinct ort
from kunde
order by ort desc
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
traditionelle Syntax:
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
3-2
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)
Drei Tabellen
kunde
bestellt
artikel
knr nachname
knr anr menge
anr
aname
traditionelle Syntax:
select kunde.nachname, bestellt.menge, artikel.aname
from kunde, bestellt, artikel
where kunde.nachname = 'Barth' and kunde.knr=bestellt.knr and artikel.anr=bestellt.anr
Die Verknüpfung der drei Tabellen erfolgt über identische Spalten im WHERE-Teil,
die normale Wertebeschränkung ebenfalls.
ANSI Syntax:
select kunde.nachname, bestellt.menge, artikel.aname
from kunde join (bestellt join artikel on bestellt.anr = artikel.anr)
on kunde.knr=bestellt.knr;
where kunde.nachname = 'Barth'
Die zweite Tabelle der Verknüpfung wird nun zuerst durch eine Verknüpfung mit der dritten Tabelle
eingeschränkt, mit Angabe dieser Verknüpfungsspalte hinter ON.
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
(4 rows affected)
TFH Berlin/Steyer
3-3
Datenmanipulation
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
(9 rows affected)
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');
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
TFH Berlin/Steyer
3-4
Datenmanipulation
Trigger
Es gibt eine Tabelle person (pnr integer, pname char(12))
pnr
pname
-------------------------------222
Adam
223
Berta
Konstanter Eintrag:
create table insprot (kommentar char(50))
create trigger insertprotokoll
on person
for insert
as insert into insprot values ('Schon wieder ein Neuer')
insert into person ...
select * from insprot
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
update person ... und select * from zähler
anzahl
-------------------------------1
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.
TFH Berlin/Steyer
3-5
Datenmanipulation
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
3-6