SQL-FAQ

Probleme bei der Datenhaltung:

Redundanz:
Mehrfachspeicherung ein- und derselben Information

• Erhöhter Speicherbedarf und Aktualiserungsaufwand

Logische / physikalische Datenabhängigkeit:
Es ist eine enge Abhängigkeit zwischen der Struktur der Datensätze einer Datei, und der Struktur im bearbeitenden Programm gegeben.

• Logische:
  Wird durch eine geänderte Benutzeranforderung eine Dateistruktur geändert, müssen auch alle darauf zugreifenden Programme geändert werden.
• Physikalische:
  Wird aus Optimierungsgründen die Zugriffsart auf eine Datei von z.B. sequentiell auf Index-orientiert geändert, müssen alle darauf zugreifenden Programme ebenfalls geändert werden.

Ablaufsicherheit:
Beim Zugriff auf eine Datei durch mehrere Benutzer/Programme muss eine Synchronisierung erfolgen, um inkonsistente Zustände durch parallele Zugriffe zu vermeiden.

Datenschutz/-sicherheit:
Datenschutz durch Zugriffsberechtigungen muss in jedem einzelnen Programm realisiert werden, da keine zentrale Instanz vorhanden ist. Die Sicherheit der Daten wird meist durch nächtliche Backups gewährleistet, wobei im Schadensfall die Daten bis zur letzten Sicherung verloren sind.

SQL

Die Anweisung bildet Relationen auf Relationen ab.

Syntax:
select                     <Spalte1>,...,<Spalten>
from                       <Tabelle1>,...,<Tabellen>
where                     <Bedingung>
group by               <Attribut-Liste>
having                   <Bedingung>
order by <Attributliste>

Reihenfole:
Mit
from
wird zunächst die Haupttabelle ausgewählt, vermindert um die Tupel, welche die bei
where
angegebene(n) Bedingung(en) nicht erfüllen. Durch
group by
werden die Tupel mit gleichen Attributwerten zusammengefasst, wenn sie die gemeinsame Gruppeneigenschaft bei
having
erfüllen. Nur diese Tupel werden in der Ergebnisrelation auf die nach
select
angegebenen Spalten projiziert. Schließlich werden die Tupel für die Ausgabe in der Reihenfolge sortiert, die durch
order by
festgelegt wird.

Beschreibung der Komponenten:

SELECT

Beschränkt die Ausgabe der Tupel einer Relation auf bestimmte Attribute. Das Ergebnis ist eine temporäre Relation.
select kunde_nr, ort
from kunden;

FROM
Gibt die Quelltabelle(n) an.

WHERE
Beschränkt die Auswahl der Daten aus einer Tabelle auf Tupel, die einer bestimmten Eigenschaft genügen.
select * from lager
where lagerbestand > 10;

GROUP BY
Verdichtet Daten nach bestimmten Kriterien. Tupel mit gleichen Werten in einer oder mehreren Spalten werden zu einem Tupel gruppiert. Alle in der select-Anweisung aufgeführten Attribute, die keine Gruppenfunktionen sind, müssen innerhalb der group by-Klausel angegeben werden.
select plz, count(*) as `Kundenzahl`
from kunden
group by plz;

HAVING
Beschränkt das Ergebnis eines selects mit group by auf Tupel, die in der Verdichtung gewisse Eigenschaften erfüllen.
select plz, count(*) as `Kundenzahl`
from kunden
group by plz
having count(*) > 5;
ORDER BY
Sortiert die Ausgabe nach den angegebenen Attributen, in der angegebenen Reihenfolge.
asc sortiert aufsteigend (ascending)
desc sortiert absteigend (descending)
select *
from artikel
order by warengruppe asc, einkaufspreis desc;

Die Verbund-Operation (join)

Natural join
Join dient dazu, mehrere Tabellen unter bestimmten Bedingungen zu einer zusammenzufügen. Somit kann mit dem select-Befehl mehr als eine Tabelle erreicht werden.

select     *
from        kunden k
                join rechnungen r
                on k.kunden_nr = r.kunden_nr
where     bezahlt = 0;

Die mit on dargestellte Bedingung ist notwendig, da join das kartesische Produkt der beiden Relationen kunden und rechnungen liefert. Erst durch on k.kunden_nr = r.kunden_nr entsteht eine sinnvolle Relation, da somit nur Tupel mit identischen Schlüsselwerten in den Kundennummern übernommen werden.

Da in dem gezeigten Beispiel eine Gleichheitsbedingung vorliegt, spricht man auch von einem natural join oder EQUI-join. Die Ergebnisrelation enthält nur Tupel, bei denen der Wert der verbindenden Attribute (kunden_nr) in beiden Ausgangsrelationen vorhanden war.

Outer join

Sofern die Ergebnisrelation auch Tupel enthalten soll, bei denen der Wert der verbindenden Attribute nicht in den Ausgangsrelationen enthalten ist, wird der outer join verwendet.

• Left outer join
  Übernimmt alle Tupel aus der linken Tabelle, und ergänzt die fehlenden Attribute aus der rechten Tabelle durch null
• Right outer join
  Übernimmt alle Tupel aus der rechten Tabelle, und ergänzt die fehlenden Attribute aus der linken Tabelle durch null
• Full outer join
  Übernimmt alle Tupel aus beiden Tabellen, und ergänzt die jeweils fehlenden Attribute durch null

Schlüsselworte:

distinct
verdichtet Tupel mit identischen Werten zu einem Tupel
select distinct plz from kunden;

logische Operatoren
and, or, not können wie bekannt benutzt werden

Vergleichsoperatoren:
<, >, <=, >=, =,!=

können mit numerischen und alphanumerischen Werten benutzt werden

between
Überprüft, ob ein Attribut in einem angegebenen Bereich liegt.
where datum between `01-01-2001` and `31-12-2001`
Ist äquivalent zu
where datum >= `01-01-2001` and datum <= `01-01-2001`

Operatoren in / not in
Überprüfen, ob ein Attribut in einer Menge(Subquery) von Werten enthalten ist
where extract(year from datum) in (2001,2002)

Operator exists
Liefert „wahr”, wenn das Ergebnis einer Menge(Subquery) nicht leer ist.
select *
from kunden
where exists (select auftrag_nr from auftraege where kunden.kunde_nr = auftraege.kunde_nr);
 

Operator all
Kann mit allen Vergleichsoperatoren kombiniert werden. Führt den Vergleich mit jedem einzelnen Wert der Menge(Subquery) aus. Liefert „wahr“, wenn der Vergleich auf alle Werte „wahr“ ist. (AND-Verknüpfung)
Select *
From artikel
Where warengruppe = 02 and jahresumsatz > all (select jahresumsatz from artikel where warengruppe = 01);
 

Operator any
Kann mit allen Vergleichsoperatoren kombiniert werden. Führt den Vergleich mit jedem einzelnen Wert der Menge(Subquery) aus. Liefert „wahr“, wenn der Vergleich auf mindestens einen Wert „wahr“ ist. (OR-Verknüpfung)
Select *
From artikel
Where warengruppe = 01 and jahresumsatz > any (select jahresumsatz from artikel where warengruppe = 02);
 

Operator like
Vergleicht Werte eines Attributes anhand eines Musters
„%“        ist Platzhalter für beliebig viele Zeichen
„_“          ist Platzhalter für genau ein Zeichen
where kunde like(`A%`)
 

Prüfung auf null–Marken               is null / is not null
Prüft, ob ein Attribut mit der null-Marke belegt ist
where datum is not null

Gruppenfunktionen
Gruppenfunktionen sind auf Wertmengen definiert.

• avg(x)                    Durchschnitt der Werte der Spalte x
• count(x) Anzahl der Vorkomnisse von Werten in der Spalte x
• max(x)                   Maximum der Werte der Spalte x
• min(x)                   Minimum der Werte der Spalte x
• sum(x)                   Summe der Werte der Spalte x

Sonstige Funktionen

• lower(name)
  wandelt “name” in Kleinbuchstaben
• upper(name)
  wandelt “name” in Großbuchstaben
• substing(name,0,5)
  bildet einen String aus einem anderen String
  String „name“, Beginn des Substrings bei Index „0“ für „5“ Zeichen
• cast(<ausdruck> as <datentyp>)
  wandelt ein Attribut in einen anderen Typ
  cast(datum as character) - datum wird in Typ „character“ gewandelt
• round(betrag,0)
  rundet einen numerischen Betrag
  „0“ auf volle Euro
  „2“ auf volle hundert Euro

case-Funktion
case
  <when-klausel liste>
  else <skalarausdruck>
end
Zu beachten ist, dass alle Werte die zurückgegeben werden können, vom gleichen Typ sein müssen.
case
                when warengruppe = 1 then ‘Monitor’
                when warengruppe = 2 then ‘Drucker’
                else `unbekannte Artikelgruppe´
end

Mengenoperatoren
Die folgenden Mengenoperatoren können auf zwei oder mehr Tupelmengen angewendet werden:

• union
  Vereinigungsmenge, doppelte Tupel werden eleminiert
• union all
  Vereinigungsmenge, das eleminieren doppelter Tupel wird unterdrückt
• intersect
  Durchschnittsmenge
• minus
  Differenzmenge

select name, strasse
from kunden
UNION
select name, strasse
from lieferanten;

Select-Anweisungen können nur durch eine Mengenoperation verbunden werden, wenn die Ergebnisse der Selects in der Anzahl der Ergebnisspalten und ihrem Typ übereinstimmen.

create table

Create table legt Basistabellen in einer Datenbank an.

Syntax:
Create table <Name der Tabelle> (
                <Name Attribut1> <Typ> <Integritätsbedingung>,
                <Name Attribut2> <Typ> <Integritätsbedingung>);

 Als Integritätsbedingung kann benutzt werden

• not null
  verhindert, dass das Attribut null annimmt
• default
  gibt durch default <Ausdruck> einen Defaultwert vor, falls kein Wert angegeben wird

• check
  überprüft durch check <Ausdruck>, ob ein Wert in einem vorgegebenen Bereich liegt
• unique
  kennzeichnet Attribute als eindeutig
• primary key
  definiert einen Primärschlüssel
• foreign key
  definiert einen Fremdschlüssel

 create table artikel (
                artikel_nr             char(10)               not null,
                lagerplatz             char(1)                 check lagerplatz between 1 and 5,
                vorjahresumsatz numeric                                default 0);

drop table

Drop table löscht eine Tabelle.

Syntax:
drop table <tabelle> ;

Auch, zum Löschen eines Indexes

drop index <indexname> on <tabelle>;

alter table

 Alter table bietet viele Möglichkeiten, eine Tabellenstruktur nachträglich zu ändern.

 Syntax:
 Alter table <tabelle> <Liste von Aktionen>

 Möglich sind folgende Aktionen:

• add <Attributdeklaration>
  fügt eine zusätzliche Spalte in eine Tabelle ein, alle bestehenden Tupel werden mit null belegt
  alter table artikel add gewicht numeric(6,2);
• modify (<Attributname> <Attributeigenschaft>)
  ändert den Typ eines Attributs
  alter table artikel modify(lieferzeit smallint);
• drop <Attributname>
  löscht ein bestehendes Attribut aus einer Tabelle
  alter table artikel drop bestand_lager;

Es können mehrere Aktionen kombiniert werden:

alter table artikel
                modify( bestand_lager smallint),
                drop lieferzeit,
                add mindestbestellmenge smallint;

insert

Fügt neue Tupel in eine Relation ein.

Syntax:
insert [into] <Tabellenname>
[(<Liste von Attributnamen>)]
                values
                (<Liste von Attributwerten>);

Weiterhin ist es möglich, die Attributwerte aus einer select-Anweisung zu beziehen:

insert [into] <Tabellenname>
[(<Liste von Attributnamen>)]
                [as]
                <select-Anweisung>

 insert into auftraege
(auftrag_nr,kunden_nr)
values  (5011, 12477);

Die Attributnamen können auch weggelassen werden, dann werden alle Werte der Reihenfolge der Spalten nach eingefügt.

update

Ändert bestehende Attributwerte in Tupeln einer Relation. Betroffen ist stets eine Menge von Tupeln, die durch eine Selektion eingeschränkt werden kann.

Syntax:
update <tabelle>
                set
<Attribut1> = <Wert1>,
                ...
                [where <Bedingung>];

update kunden
set
name = `Computer As`,
strasse = `Platz 1`
where kunde_nr = 100;

delete

Löscht Tupel einer Relation. Betroffen ist stets eine Menge von Tupeln, die durch eine Selektion eingeschränkt werden kann.

Syntax:
delete from <tabelle>
                [where <Bedingung>];

delete from artikel where kunde_nr = 100;

Indizes

 Die DBMS bieten die Möglichkeit, Indizes für bestimmte Attribute einer Relation zu erstellen. Sie bieten den Vorteil, dass Zugriffe auf indizierte Attribute schneller ablaufen, da normalerweise alle Tupel in der Reihenfolge ihrer Eingabe durchlaufen werden.

 Syntax:
 create [unique] index <indexname> on <tabelle> (spalte,..,...);

 Üblich ist es, die Spalte, die einen Fremdschlüssel enthält, zu indizieren. Diese wird häufig in Verbundoperationen (join) verwendet, daher ist ein Geschwindigkeitsvorteil zu erwarten.

 create index name on kunden(name);

 Views

Ein View ist eine virtuelle Tabelle. Views werden durch select-Anweisungen erstellt, die die gewünschten Tupel auswählen.

Syntax:
create view <View-Name>
as <Select-Anweisung>

Ein View kann nach der Erstellung wie eine normale Tabelle benutzt werden. Somit ergeben sich folgende Vorteile:

1.Bessere Benutzeranpassung der Datenstrukturen und –mengen
2.Beschränkung der sichtbaren Daten (Datenschutz)
3.Kapselung von fragmentierten Daten, die über viele Relationen verteilt sind

create view plz_bereich_nord
as
select *
from kunden
where plz between 1 and 49999;

Das Beispiel erstellt ein View, das alle Spalten der Basistabelle besitzt, nicht aber alle Daten. Genauso können auch die sichtbaren Spalten eingeschränkt werden.