SQL - Beuth Hochschule für Technik Berlin

Datenbankfunktionen: SQL und andere
Frank Steyer
SQL-Gebiet
DDL
Data Definition Language
DML
Data Manipulation Language
DCL
Data Control Language
DPL
Data Programming Lanuage
Datenbank-Funktionen jenseits SQL
Selbstverwaltung
Data Dictionary
Administration
Start-Stop, Sicherung, Wiederherstellung, Überwachung
Oberflächenbau
interaktiv, programmierbar, Datenbankkopplung, Internet
TFH Berlin/Steyer
Datenbankfunktionen: SQL und andere
Data Definition Language
Datenbank
Idee:
Datenbehälter, übergeordnete organisatorische Einheit, z.B. für Benutzer, Gebiet
create database database_name, drop database database_name
Tabelle
Idee:
Datenbehälter für ein Anwendungsobjekt, z.B. für Artikel
mit Spalten (Attribute des Objektes) und Zeilen (Exemplare des Objekts)
(1) Grundform: create table artikel (
anr
aname
apreis
amaterial
alagermenge
integer,
char(12),
decimal (5,2),
char(15),
integer)
Zeilen
Spalten
Spaltenüberschriften
drop table table_name
(2) Datentypen: integer, char(n), decimal(p,s) etc.
(3) einfache Spaltenbeschränkungen: unique, mandatory, optional, primary key
(4) Wertebeschränkungen:
check
(5) Beschränkungen durch andere Tabellen, referentielle Integrität
z.B.
kunde
bestellt
artikel
create table kunde (knr integer primary key,...)
create table artikel (anr integer primary key,...)
create table artikel (knr integer references kunde (knr), anr integer references artikel (anr)…)
TFH Berlin/Steyer
Datenbankfunktionen: SQL und andere
Sicht
Sichten
Tabelle
Idee:
Benutzerdatenausschnitt entsprechend den Benutzeranforderungen aus dem Datenmodell
Enthält nur die Definition, die Daten werden bei Bedarf aus der Tabelle abgeleitet
Faktisch: SELECT-Benennung mit Namen, also Abkürzung
3 Funktionen:
neue Überschriften: Namenänderung der Spalten (z.B. in anderen Sprachen),
Verdeckungen, virtuelle Werte (Berechnungen)
create view teureartikel (name, bruttopreis) as select name, bruttopreis from artikel where bruttopreis > 1000
drop view teureartikel
Index
Idee:
Hilfsdatei zum schnelleren Durchsuchen der Haupttabelle, vergleichbar mit Stichwortregister
Tabelle
Indexdatei
einfach zu finden
x
y
y
x
aufwendig zu finden
create index index_name on Spaltenname(Tabellenname), drop index index_name
lesefreundlich (weil man die gesuchte Zeilenposition im Index nachschlagen kann)
änderungsunfreundlich (weil immer an zwei Stellen geändert werden muss)
materialisierte Sicht
Der Grund dafür ist die manchmal lange Aufbauzeit einer normalen kompliziert definierten Sicht:
Speichern in eine Tabelle (create table + insert-select). Nötig ist natürlich ein periodischer Update.
Sicht
materialisierte
Sicht
Tabelle
TFH Berlin/Steyer
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, auf, ab, mehrfach
doppelte Zeilen
arithmetische Operationen (nur 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)
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
SQL
Data Control Language
(vom Datenbankadministrator ausgeführt)
Es gibt Gefahren für die Daten: 1)
2)
3)
4)
unberechtigte Zugriffe
Mehrfachbenutzung
Irrtümer (rückgängig machen)
Technische Abbrüche (sollen keine Trümmer hinterlassen)
zu 1) Benutzerverwaltung
create user … identified by …
drop user …
grant { connect | resource | dba } to meier
grant <Tätigkeit> on <Objekt> to <Benutzer>
revoke <Tätigkeit> on <Objekt> from <Benutzer>
Ablauf: - Superuser wird bei Installation festgelegt.
- Superuser legt Datenbankadministratoren an.
- Jeder Datenbankadministrator legt seine Mitarbeiter-User an (resource, connect).
meier
Operationen (select, insert, update, delete, definieren, benutzen)
person
form1
x
x
Oberflächen werden immer "höher", d.h. entfernen sich immer mehr vom DBMS. Die Benutzer werden
immer datenbankunausgebildeter. SQL verschwindet in den Tiefen der Systeme, bleibt aber als funktionaler
Standard erhalten.
zu 2) Sperren
Bei gleichzeitiger Mehrfachbenutzung tritt evtl. lost update auf (Die spätere Änderung überschreibt die
frühere.). Abhilfe für beides ist sperren.
in der Datenbank:
meier 500
user 1
user 2
meier +100
meier -100
Ablauf: - user1 holt den Satz mit 500
- user2 holt den Satz mit 500
- user1 addiert 100: 600
- user2 subtrahiert 100: 400
- user1 schreibt zurück: 600
- user2 schreibt zurück: 400
TFH Berlin/Steyer
SQL
Die Änderung des user1 (+100) geht verloren. user1 müsste den Satz während der ganzen Zeit sperren.
Weitere Unterscheidungen: Lesesperren, Schreibsperren, gravierendes Problem: Deadlock
zu 3) Transaktionsverwaltung
Eine Transaktion ist eine benutzerdefinierte Befehlsfolge, die ganz oder garnicht ausgeführt wird, auch
wenns länger dauert.
Beginn einer Transaktion am Anfang der Sitzung
Ende einer Transaktion am Ende der Sitzung
dazwischen mit "commit" Ende und Anfang einer Transaktion zugleich (früher mal begin transaction, end
transaction)
Zweck:
Realisierungsgarantie auch längerer Abläufe
Realisierung:
Im Log-Protokoll werden alle Datenänderungen (kein Lesen) schnell parallel notiert und bei
Bedarf nachgearbeitet oder rückgängig gemacht ("rollback, undo").
SQL:
Probleme:
COMMIT, ROLLBACK
Je weiter zurück die Dinge liegen, die man ungeschehen machen will, desto mehr Platz für
das Protokoll braucht man.
ACID-Eigenschaften einer Transaktion:
A (atomicity):
C (consistency):
I (isolation):
D (durability):
Eine Transaktion ist unteilbar, wird also ganz oder gar nicht ausgeführt.
VOR und NACH der Transaktion ist der Datenbestand konsistent.
Die Einzelaktionen können ungestört durchgeführt werden.
Die Ergebnisse werden dauerhaft/permanent gespeichert.
Es gibt eine einstellbare Transaktionsschärfe (isolation level)
Isolierungsstufe 0: Es wird nichts gesperrt. (READ UNCOMMITTED) Bsp. Ist Tabelle leer ?
Isolierungsstufe 1: Es wird implizit eine Zeile gesperrt, wenn sie angefasst wird. Es ist immer nur eine Zeile
pro Tabelle gesperrt. (READ COMMITTED) Bsp. Gehaltserhöhung für alle.
Isolierungsstufe 2: Es wird eine Zeile gesperrt, wenn sie angefasst wird. Diese Sperre wird für
wiederholtes Arbeiten gehalten. (REPEATABLE READ) Bsp. Umbuchung
Isolierungsstufe 3: Es wird implizit eine ganze Ergebnismenge für wiederholtes Arbeiten gesperrt.
(CURSOR STABILITY) Bsp. Jahresabschluss u.a. Statistiken
zu 4) technische Abbrüche
werden damit auch erledigt
TFH Berlin/Steyer
SQL
Data Programming Language
ohne Prozeduralität
mit Prozeduralität.
Der klassische Fall ist die (häufige) Kommunikation zwischen einem Programm und der Datenbank.
Durch Prozeduralität von SQL wird jedoch bei ausgedehnten Computernetzen die
Kommunikationsbelastung reduziert.
Deshalb können in der Datenbank Prozeduren angelegt werden, die alle üblichen Anforderungen erfüllen:
Abkürzung für einen oder mehrere SQL-Befehle, Steuerungsbefehle (if/while/begin-end/lokaleVariable),
Parametrisierung, Anschluss von Prozeduren anderer Sprachen
SQL-Abkürzung
create procedure p1
as select * from person
execute p1
Ablaufsteuerung
create procedure p2
as select 'xxxxx' =
case when nr = 1 then "eins"
when nr = 2 then "zwei"
end
execute p2
create procedure p3
as if exists (select * from person)
insert into testprotokoll values ('Personen existieren')
execute p3
create procedure p4
as declare @x int
select @x=1
while @x<4
begin
print 'eine person'
select @x=@x+1
end
execute p4
TFH Berlin/Steyer
SQL
Parameter
create procedure p5 @nachname varchar(40), @vorname varchar(20)
as
select * from autoren
where au_vname = @vorname
and au_nname = @nachname
execute p5 ‘Meier’ ‘Otto’
select * from person
1
2
3
99
Adam
Berta
Conny
Xaver
create procedure p1 as select * from person
execute p1
create procedure p2 as if exists (select * from person) print 'personen
existieren'
execute p2
create procedure p3 as
begin
declare @vorname char(5)
declare c cursor
for
select pname from person
open c
fetch next from c into @vorname print @vorname
while @@fetch_status = 0
begin
fetch next from c into @vorname
print @vorname
end
deallocate c
end
execute p3
create procedure p4 @pname char(5) as select * from person where pname = @pname
execute p4 'Adam'
TFH Berlin/Steyer
SQL
Selbstverwaltung durch ein Data Dictionary
Systemtabellen
sysobjects
Beschreibt Systemobjekte: S definiert eine Systemtabelle, U eine Benutzertabelle, V
eine View, L eine Log-Datei, P eine DB-Prozedur, R eine Integritätsregel, D einen
default-Wert, TR einen Trigger.
Beispiel:
select * from sysobjects
where xtype = "U"
syscolumns
Beschreibt Spalten.
sysindexes
Beschreibt Indizes.
sysusers
Beschreibt Benutzer.
sysdatabases Beschreibt Datenbanken.
Systemprozeduren
sp_help
Meldet Informationen zu Datenbankobjekten (jedes in der sysobjects-Tabelle
aufgeführte Objekt), Datentypen.
sp_helptext
Gibt den Text einer Regel, eines Standardwertes, einer unverschlüsselten
gespeicherten Prozedur, eines Triggers oder einer Sicht aus.
sp_rename
Ändert in der aktuellen Datenbank den Namen eines vom Benutzer erzeugten
Objekts (z. B. Tabelle, Spalte, benutzerdefinierter Datentyp).
Administration
Oberflächenbau
Frontendkopplung mit einem Backend-DBS
Verfügbarkeit im Internet
TFH Berlin/Steyer