Grundlegendes SQL in PostgreSQL

Mag. Christian Gürtler
SQL – Relationale Datenbanken
PostgreSQL
MultiAugustinum
2010
2
Inhaltsverzeichnis
1 Installation und Anmelden
1.1 Terminal, DOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.2 pgAdmin3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.3 Nach dem Anmelden . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5
5
5
6
2 Tabellen erstellen
2.1 Numerische Datentypen . . . . . .
2.2 Texte . . . . . . . . . . . . . . . .
2.3 Binärdaten . . . . . . . . . . . . .
2.4 Datum- und Uhrzeit . . . . . . . .
2.5 Geometrische Datentypen . . . . .
2.6 Arrays . . . . . . . . . . . . . . . .
2.7 Netzwerkdatentypen . . . . . . . .
2.8 Enumerations – Aufzählungen . . .
2.9 weitere Attribute und Constraints
2.10 Beispiele . . . . . . . . . . . . . . .
2.11 Sequenzen . . . . . . . . . . . . . .
2.12 Zusammengesetzte Datentypen . .
2.13 Kommentare . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
7
8
9
9
9
10
11
13
14
14
17
20
22
23
3 Tabellen verändern
3.1 Hinzufügen von Spalten . . . .
3.2 Löschen von Spalten . . . . . .
3.3 Ändern von Spalten . . . . . .
3.4 Constraints hinzufügen/löschen
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
25
25
26
26
26
4 Daten auslesen
4.1 SELECT mit einer Tabelle . . . . . . . . . . . . . . .
4.1.1 Doppelte Werte filtern . . . . . . . . . . . .
4.1.2 SELECT mit COUNT() und GROUP BY .
4.1.3 weitere Aggregatfunktionen und Subselects
4.1.4 SELECT einschränken mit WHERE . . . .
4.2 SELECT mit mehreren Tabellen . . . . . . . . . .
4.2.1 WHERE . . . . . . . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
29
29
30
30
32
34
35
35
.
.
.
.
.
.
.
.
4
Inhaltsverzeichnis
4.2.2
4.2.3
JOIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.2.2.1 INNER JOIN – Paare finden . . . . . . . . . . . . . . .
4.2.2.2 OUTER JOIN – LEFT/RIGHT JOIN – Lücken finden
4.2.2.3 FULL OUTER JOIN – beidseitige Lücken finden . . .
4.2.2.4 JOIN mit WHERE . . . . . . . . . . . . . . . . . . . .
SELECT mit komplexeren Abfragen . . . . . . . . . . . . . . . .
4.2.3.1 AND/OR . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.2.3.2 COUNT/GROUP BY . . . . . . . . . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
35
35
36
37
37
37
37
38
5 Bestehende Daten ändern/löschen
41
5.1 UPDATE – ändern . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
5.2 DELETE – löschen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
1
Installation und Anmelden
PostgreSQL ist ein ziemlich mächtiger Datenbankserver, der sich fast vollständig an den
SQL-2003 Standard hält. Wir verwenden Version 8.4, one-click-installer für Windows,
Mac und Linux werden heruntergeladen von
http://www.enterprisedb.com/products/pgdownload.do.
Unter auf Linux kann auch PostgreSQL mit dem jeweiligen Paketmanager installiert
werden, der One-Click-Installer liefert aber auch das grafische Werkzeug pgAdmin3.
Diese Doku liefert nur einen Überblick über PostgreSQL, für detaillierter Infos bitte
unter http://www.postgresql.org/docs/8.4/static/index.html oder
http://www.postgresql.org/files/documentation/books/pghandbuch/html/
1.1 Terminal, DOS
bei MacOSX und Linux wird das Terminal verwendet, das DOS-Fenster bei Windows
(im Programmordner von PostgreSQL)
sputnik ~: psql -U postgres
psql (8.4.2)
Geben Sie »help« für Hilfe ein.
Damit meldet man sich als Datenbankuser postgres am lokalen Server an (Angabe des
Parameters −U ).
1.2 pgAdmin3
Hier muss zuerst eine Verbindung aufgebaut werden (Abbildung 3.1).
momentan nur
32-Bit-Version
verfügbar
6
1 Installation und Anmelden
Abbildung 1.1: pgAdmin
Abbildung 1.2: pgAdmin Datenbank
1.3 Nach dem Anmelden
kann eine bestehende Datenbank verwendet werden. Im Terminal mit
postgres=# \c guch
psql (8.4.2)
Sie sind jetzt verbunden mit der Datenbank »guch«.
guch=#
oder im pgAdmin3 durch Klick auf den Server (Abbildung 1.2). Sollte die Datenbank
nicht vorhanden sein, kann durch Rechtsklick auf die Serververbindung eine neue Datenbank erstellt werden, im Terminal geschieht dies durch
guch=# create database kino;
CREATE DATABASE
guch=#
2
Tabellen erstellen
In dieser Datenbank können dann Tabellen erstellt werden. Tabellen sind ja in SQL aus
Spalten aufgebaut, die die Attribute darstellen.
guch=# select * from person;
geb | gehalt | vor
------------+---------+-------1965-05-29 | 1000.00 |
1965-05-29 | 1000.00 |
2065-05-29 | 1000.00 |
1965-05-12 | 1000.00 |
1965-05-29 | 1000.00 |
1954-09-12 | 1000.00 |
1965-05-29 | 1000.00 | kathri
(7 Zeilen)
Diese Tabelle heisst person und weist die Spalten geb, gehalt, vor auf. Diese Spalten
werden dann zeilenweise mit Werten befüllt. Damit in jeder Spalte die richtigen Werte
gespeichert werden (und nicht beispielsweise bei gehalt ein Vorname) gibt es sogenannte
Datentypen.
8
2 Tabellen erstellen
2.1 Numerische Datentypen
für alle Zähl- und Rechenoperationen.
• SMALLINT, INT2: 2 Byte Speicherbedarf (Zahlenraum −32768 bis +32767)
• INTEGER, INT, INT4: 4 Byte Speicherbedarf, Zahlenraum ±2M illiarden
• BIGINT, INT8: 8 Byte Speicherbedarf
• REAL: 4 Byte Speicherbedarf, Fließkommazahl mit max. 6 Nachkommastellen, ungenaue Präzision (Rundung)
• DOUBLE PRECISION: 8 Byte Speicherbedarf, 15 Stellen gespeichert
• NUMERIC, DECIMAL: Speicherbedarf größenabhängig, exakte Speicherung ohne Rundung (Bankwesen)
• SERIAL, SERIAL4: 4 Byte Speicherbedarf, Sequenz vom Typ INT, für fortlaufende
Nummern
• BIGSERIAL, SERIAL8: 8 Byte Speicherbedarf, Sequenz vom Type BIGINT
Vergleich von DOUBLE PRECISION und NUMERIC an Hand des Reiskornproblems auf
einem Schachbrett: auf jedem Feld werden doppelt so viele Reiskörner gespeichert
wie auf dem vorangegengenen Feld; begonnen wird auf dem ersten Feld mit 1 Korn;
die Formel lautet: 1 + 21 + 22 + . . . + 263 = 264 − 1
guch=# select power(2::double precision, 64::double precision) -1;
?column?
---------------------1.84467440737096e+19
(1 Zeile)
guch=# select power(2::numeric, 64::numeric) -1;
?column?
--------------------------------------18446744073709551615.0000000000000000
(1 Zeile)
2.2 Texte
9
guch=#
Man erkennt deutlich, dass double precision ab der 14. Position rundet, während
numeric genau rechnet.
Es können bei numerischen Datentypen Längenangaben erfolgen:
NUMERIC(10)
10 Stellen, keine Nachkomma
NUMERIC(10,2) 10 Stellen gesamt, davon 2 Nachkomma
NUMERIC
Anzahl Ziffern vor/nach Komma egal
2.2 Texte
• VARCHAR: laut SQL-Standard ist eine Längenangabe vorgeschrieben, PostgreSQL
verlangt keine Angabe; ist eine vorhanden, wird sie verwendet (VARCHAR(40) – hier
können bis zu 40 Zeichen gespeichert werden).
• CHAR: wie bei VARCHAR ist auch hier laut Standard eine Längenangabe nötig, bei
PostgreSQL ist sie optional, allerdings bedeutet CHAR, dass nur 1 Zeichen gespeichert werden kann, bei CHAR(10) können bis zu 10 Zeichen gespeichert werden,
kürzere Eingaben werden im Unterschied zu VARCHAR mit Leerzeichen aufgefüllt.
CHAR ohne Längenangabe entspricht CHAR(1)
• TEXT: erlaubt Texte beliebiger Länge, allerdings kann hier keine Beschränkung
angegeben werden.
2.3 Binärdaten
• BYTEA: damit können Null-Bytes oder andere nicht-druckbare Zeichen gespeichert
werden, was bei Text-Datentypen nicht möglich ist – zumindest nicht ohne Informationsverlust.
2.4 Datum- und Uhrzeit
• TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP speichert Datum und Uhrzeit
• TIMESTAMP WITH TIME ZONE speichert aktuelle Zeitzone mit
• TIME WITHOUT TIME ZONE: Zeit ohne Zeitzone
• TIME WITH TIME ZONE: Zeit mit Zeitzone
• DATE: Datum
10
2 Tabellen erstellen
• INTERVAL: speichert Unterschied zwischen 2 Zeitangaben. Beispiel:
create table zeit_test(a interval);
insert into zeit_test values(age(now(), '1965-05-29'));
select * from zeit_test;
a
-----------------------------------------44 years 10 mons 17 days 16:15:40.106207
(1 Zeile)
2.5 Geometrische Datentypen
• POINT: 16 Byte Speicherbedarf, speichert einen Punkt (x, y)
• LSEG: 32 Byte Speicherbedarf, Strecke ((x1 , y1 ), (x2 , y2 ))
• BOX: 32 Byte, Rechteck ((x1 , y1 ), (x2 , y2 ))
• POLYGON: Vieleck
• CIRCLE: 24 Byte, Kreis < (x, y), r >
Angenommen, es sollen folgende Kreise gespeichert werden.
7
B
6
A
5
4
C
3
2
1
0
0
1
2
3
4
5
6
7
Abbildung 2.1: Geometrie
Das Anlegen und Befüllen der Tabelle erfolgt so:
create table geo(id serial primary key, kreis circle);
2.6 Arrays
11
insert into geo (kreis) values ('<(2,5),1>');
insert into geo (kreis) values ('<(6,6),1>');
insert into geo (kreis) values ('<(5,3),2>');
Jetzt können von den Kreisen Durchmesser, Umfang und Fläche errechnet werden.
select id, kreis, diameter(kreis) as durchmesser, area(kreis) as fläche,
radius(kreis) as radius from geo;
id |
kreis
| durchmesser |
fläche
| radius
----+-----------+-------------+------------------+-------1 | <(2,5),1> |
2 | 3.14159265358979 |
1
2 | <(6,6),1> |
2 | 3.14159265358979 |
1
3 | <(5,3),2> |
4 | 12.5663706143592 |
2
(3 Zeilen)
Oder man möchte einen neuen Kreis an der Stelle (3, 1) mit Radius 1 einfügen und
ermitteln, mit welchen Kreisen sich dieser überschneidet.
select * from geo where kreis && circle'(3,1),1)';
id |
kreis
----+----------3 | <(5,3),2>
(1 Zeile)
Oder man möchte an (2, 1) einen Kreis mit Radius 1 einfügen und die Abstände zu den
bestehenden Kreisen feststellen.
select *, circle '((2,1),1)'<-> kreis as abstand from geo;
id |
kreis
|
abstand
----+-----------+------------------1 | <(2,5),1> |
2
2 | <(6,6),1> | 4.40312423743285
3 | <(5,3),2> | 0.605551275463989
(3 Zeilen)
2.6 Arrays
Dieser Datentyp widerspricht im Prinzip der 1. Normalform, ist aber dennoch sehr sehr
praktisch. Angenommen, eine Gärtnerei speichert zu ihren Blumen die Farben ab, so
müsste korrekterweise eine M : N -Beziehung mit drei Tabellen hergestellt werden. Um
diesen Overhaed zu vermeiden, kann die Farbe in einem Array gespeichert werden.
12
2 Tabellen erstellen
create table blumen (
artnr int not null,
bezeichnung varchar(100),
farben varchar[],
primary key(artnr)
);
insert into blumen values(100, 'Rose','{"rot","weiss","gelb"}');
insert into blumen values(200, 'Tulpe','{"rot","gelb","schwarz"}');
insert into blumen values(300, 'Nelke','{"gelb"}');
Alle Blumen, die in der Variation rot vorhanden sind, werden so ermittelt:
select * from blumen where 'rot' = any(farben);
artnr | bezeichnung |
farben
-------+-------------+-------------------100 | Rose
| {rot,weiss,gelb}
200 | Tulpe
| {rot,gelb,schwarz}
(2 Zeilen)
Die ersten beiden Farben jeder Blume werden so ausgegeben
select farben[1:2] from blumen;
farben
------------{rot,weiss}
{rot,gelb}
{gelb}
(3 Zeilen)
Blumen, die sowohl rot als auch gelb verfügbar sind, werden so ausgegben:
select * from blumen where 'rot' = any(farben) and 'gelb' = any(farben);
artnr | bezeichnung |
farben
-------+-------------+-------------------100 | Rose
| {rot,weiss,gelb}
200 | Tulpe
| {rot,gelb,schwarz}
(2 Zeilen)
Um zum Beispiel die Farbpalette für Nelken zu erweitern:
update blumen set farben = array_append(farben, 'rot') where artnr = 300;
2.7 Netzwerkdatentypen
13
Hier erhält die Nelke zusätzlich die Farbe rot (am Ende des Arrays). Soll der Wert am
Beginn des Arrays eingefügt werden, so mus array_prepend verwendet werden.
Arrays können auch als String ausgegeben werden:
select bezeichnung, array_to_string(farben,' und ') from blumen;
bezeichnung |
array_to_string
-------------+-------------------------Rose
| rot und weiss und gelb
Tulpe
| rot und gelb und schwarz
Nelke
| gelb und rot
(3 Zeilen)
oder mittels string_to_array können auch Array-Spalten an Hand eines Strings aktualisiert werden
update blumen set farben = string_to_array('blau,grün,violett',',')
where bezeichnung = 'Rose';
select * from blumen;
artnr | bezeichnung |
farben
-------+-------------+--------------------200 | Tulpe
| {rot,gelb,schwarz}
300 | Nelke
| {gelb,rot}
100 | Rose
| {blau,grün,violett}
(3 Zeilen)
Beide Funktionen benötigen ein Trennzeichen als Angabe.
Mehrdimensionale Arrays werden so angelegt: INTEGER[][]. Arrays sollten ja eigentlich
aus PHP bekannt sein, der Unterschied liegt darin, dass die Zählung mit dem Wert 1
beginnt und nicht mit der 0.
2.7 Netzwerkdatentypen
• INET speichert eine IP-Adresse (optional mit Netzmaske, Standard ist /32)
• CIDR speichert Netzmaske
• MACADDR speichert eine Geräte(MAC)-Adresse
Der Vorteil dieser Datentypen gegenüber VARCHAR, CHAR, TEXT ist der, dass bei der
Eingabe automatisch das Format geprüft wird – eine IP-Adresse wie 192.168.1.300
wird als ungültig erkannt. Außerdem läßt sich auch beispielsweise ermitteln, ob eine
IP-Adresse in einem erlaubten Bereich liegt.
14
2 Tabellen erstellen
guch=# select '192.168.23.63'::INET <<= '192.168.23.42/27'::INET;
?column?
---------t
(1 Zeile)
guch=# select '192.168.23.64'::INET <<= '192.168.23.42/27'::INET;
?column?
---------f
(1 Zeile)
2.8 Enumerations – Aufzählungen
Achtung:
Groß/Kleinschreibung
beachten
Es gibt von PostgreSQL aus Möglichkeiten, in Spalten nur gewisse Werte zuzulassen,
beispielsweise sollen in der Tabelle person nur die Anreden Herr, Frau zugelassen werden.
Mit PostgreSQL kann ein individueller Datentyp erstellt werden.
create type anrede as enum('Herr', 'Frau');
create table personen (
id serial,
titel anrede,
name varchar(100),
primary key(id));
Eine weitere Möglichkeit bietet sich mit der Verwendung sogenannter check constraints,
dazu später.
2.9 weitere Attribute und Constraints
• NOT NULL: diese Spalte darf keine Leerwerte beinhalten (Achtung!!: NULL bedeutet
»nicht vorhanden«, und nicht 0 oder Leerzeichen).
• PRIMARY KEY: mit diesem Attribut wird ein Tupel eindeutug ausgezeichnet; dazu
ist das NOT NULL-Attribut notwendig.
• UNIQUE: wird bei Spalten angewendet, die nicht Primary Key sind, aber dennoch
eindeutige Werte beinhalten sollen. Beispielsweise in der Tabelle mitarbeiter einer
Firma: der Primary Key wäre möglicherweise die Personalnummer, ein UNIQUE
könnte auf die email-Adresse gelegt werden, wenn jeder Mitarbeiter eine eindeutige
email haben soll.
2.9 weitere Attribute und Constraints
15
• DEFAULT: wenn kein besonderer Werte angegeben wird, so kann ein Standard(Default)wert angegeben werden. Eine Tabelle mitarbeiter könnte eine Spalte
Gehalt aufweisen, als Standardwert könnte ein Mindestlohn von 1000,— eingetragen werden.
• CHECK: mit einem Check-Constraint können für Werte Regeln definiert werden.
Beispielsweise dürfen Artikelpreise nicht ≤ 0 sein, oder die Anrede darf nur die
Werte Herr, Frau aufweisen.
• FOREIGN KEY: mit einem Foreign-Key (Fremdschlüssel) wird referentielle Integrität
erreicht, es muss die Angabe erfolgen, auf welche Tabelle sich der Fremdschlüssel
bezieht.
Unterschied CHECK und eigener TYPE:
⋆ ein eigener Datentyp kann für mehrere Tabellen verwendet werden
⋆ ein CHECK CONSTRAINT ist nur in der aktuellen Tabelle gültig
⋆ bei einem Übertragen der Datenbank auf einen anderen Server
dürfen die eigenen Datentypen nicht vergessen werden
Beispiel:
create table person (
pid serial,
vorname varchar(40) NOT NULL,
nachname varchar(40) NOT NULL,
email varchar(100) NOT NULL UNIQUE,
gehalt decimal(9,2) default '1000.00',
anrede char(4) CHECK(anrede in('Herr','Frau')),
PRIMARY KEY(pid)
);
Erklärung:
• pid: wird automatisch fortlaufend nummeriert, am Ende wird noch zusätzlich ein
Primary Key auf diese Spalte gelegt (diese Werte müssen eindeutig sein)
• vorname: kann beliebige Zeichen beinhalten, maximal 40, weniger ja, mehr nein,
Leerwerte sind nicht erlaubt
• nachname: wie vorname
16
2 Tabellen erstellen
• email: maximal 100 Zeichen, jede email-Adresse darf nur einmal (in dieser Tabelle!!!) vorkommen
• gehalt(9,2): kann maximal eine Zahl mit 9 Ziffern, davon 2 nach dem Komma,
beinhalten; wird kein besonderer Wert angeführt, so wird 1000.00 als Standardwert
eingetragen, damit die Spalte nicht leer bleibt.
• anrede: es werden genau 4 Zeichen gespeichert, wobei nur die Werte Herr oder
Frau verwendet werden dürfen.
Hinweise zum CREATE TABLE-Befehl:
⋆ die einzelnen Spalten werden durch Beistrich getrennt
⋆ nach der letzten Spalte kommt kein Beistrich
⋆ einspaltige UNIQUE-, FOREIGN KEY- und PRIMARY KEYConstraints können bei der jeweiligen Spalte angegeben werden
(Spaltenconstraint oder Columnconstraint), mehrspaltige müssen
jedoch als Tabellenconstraint (sprich: am Ende des Create-Befehls)
geschrieben werden
⋆ daher Unique, Primary- und Foreign Keys am besten am Schluss
angeben
⋆ die einzelnen Eigenschaften eines Attributs werden durch Leerzeichen getrennt aneinander gereiht
Constraints können auch mit einem Namen versehen werden (sinnvoll, um später darauf zugreifen zu können – bei Änderungen etc. ), ansonsten vergibt PostgreSQL einen
Standardnamen.
2.10 Beispiele
17
2.10 Beispiele
• CREATE TABLE products (
product_no int,
name text,
price numeric CONSTRAINT positive_price CHECK (price > 0)
);
Hier wird ein Check-Constraint speziell bezeichnet (positive_price).
• CREATE TABLE products (
product_no int,
name text,
price numeric CHECK (price > 0),
discounted_price numeric CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);
Die beiden ersten Check-Constraints sind Columnconstraints, der dritte kontrolliert, ob der Wert in Spalte price über dem Wert der Spalte discounted_price
liegt.
Eine mögliche Schreibweise wäre auch so:
• CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0 AND price > discounted_price)
);
• CREATE TABLE products (
product_no integer,
name text,
price numeric,
CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0),
CONSTRAINT valid_discount CHECK (price > discounted_price));
Beispiel eines Tabellenconstraints
• CREATE TABLE products (
product_no integer UNIQUE,
name text,
price numeric);
Beispiel eines Unique-Constraints
18
2 Tabellen erstellen
• create table mitarbeiter (
mid int not null,
name varchar(40) not null,
email varchar(100) not null,
unique(name, email),
primary key(mid));
Hier muss eine Kombination name – email eindeutig sein, ein einzelner Name darf
doppelt existieren, auch eine spezielle email-Adresse, aber nicht eine Kombination
aus beiden.
insert into mitarbeiter values(1, 'hans','[email protected]');
insert into mitarbeiter values(2, 'hans','[email protected]');
FEHLER: doppelter Schluesselwert verletzt Unique-Constraint ...
insert into mitarbeiter values(2, 'susi','[email protected]');
select * from mitarbeiter;
mid | name | email
-----+------+---------------1 | hans | [email protected]
2 | susi | [email protected]
(2 Zeilen)
Hier sieht man, welche Werte möglich sind und welche nicht.
• CREATE TABLE products (
product_no integer CONSTRAINT must_be_different UNIQUE,
name text,
price numeric
);
Unique-Constraint mit speziellem Namen.
• CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
Primärschlüssel als Spaltenconstraint
• CREATE TABLE example (
a integer,
b integer,
c integer,
PRIMARY KEY (a, c)
);
2-spaltiger Primärschlüssel als Tabellenconstraint.
19
2.10 Beispiele
• CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no),
quantity integer
);
Fremdschlüssel-Constraint als Spaltenconstraint
• CREATE TABLE orders (
order_id integer PRIMARY KEY,
produkt_no integer REFERENCES products,
quantity integer
);
Wenn der Fremdschlüssel den gleichen Namen trägt, wie der Primärschlüssel in
der Bezugstabelle, dann kann der Befehl so verkürzt werden.
• M.N -Beziehung
CREATE TABLE produkte (
produkt_no int PRIMARY KEY,
name text,
preis numeric );
CREATE TABLE bestellungen (
bestell_nr int,
liefer_addresse text,
bestell_datum date,
primary key(bestell_nr)
);
CREATE TABLE bestell_artikel (
produkt_no int REFERENCES products,
bestell_nr integer REFERENCES orders,
menge integer,
PRIMARY KEY (produkt_no, bestell_nr) );
In diesem Fall existiert zwischen produkte und bestellungen eine M : N -Beziehung,
die neue Zwischentabelle (bestell_artikel) besitzt einen 2-fachen Primärschlüssel, jede Spalte wiederum ist ein Fremdschlüssel auf eine der Tabellen produkte
bzw. bestellungen.
20
2 Tabellen erstellen
2.11 Sequenzen
Sequenzen sind Zähler, die in beliebig großen Intervallen vor- oder rückwärts zählen
können. Die einfachste Variante stellt der Datentyp SERIAL dar. Sequenen können auch
manuell erstellt werden, wobei mehrere Einstellungsmöglichkeiten gegeben sind.
create sequence meine_seq;
1
create table kunden (
kdnr int default nextval('meine_seq'),
name varchar(100),
primary key(kdnr)
);
2
create table mitarbeiter (
mnr int default nextval('meine_seq'),
name varchar(100),
primary key(mnr)
);
3
insert into kunden (name) values ('meier');
4
insert into mitarbeiter (name) values ('sklave');
5
select * from kunden;
kdnr | name
------+------1 | meier
(1 Zeile)
6
select * from mitarbeiter;
mnr | name
-----+-------2 | sklave
(1 Zeile)
select currval('meine_seq');
currval
--------2
(1 Zeile)
7
In 1 wird eine Sequenz erzeugt, in 2 und 3 werden Tabellen erzeugt, die auf diese Sequenz zugreifen, in 4 und 5 werden Werte eingefügt, die in 6 abgerufen
werden. In 7 wird der letzte eingefügt Wert der Sequenz abgefragt.
2.11 Sequenzen
21
Mit nextval kann der nächste einzufügende Werte ermittelt werden, dies ist hifreich,
wenn die Tabelle in der entsprechenden Spalte keinen DEFAULT-Wert besitzt.
insert into mitarbeiter values
(nextval('meine_seq'), 'sklaventreiber');
select * from mitarbeiter;
mnr | name
-----+---------------2 | sklave
3 | sklaventreiber
(2 Zeilen)
Warum werden Sequenzen verwendet, wenn es doch auch SERIAL gibt? Die Verwendung
von NEXTVAL, CURRVAL bietet sich bei Bezieungen mit foreign keys an, hier muss ja
der Foreign-Key den richtigen Wert aufweisen.
create sequence rech_seq;
create table bestellungen(
id int not null,
best_datum date,
kundennr int,
primary key(id));
create table bestell_produkte (
produktid int not null,
bestell_id int not null,
menge int,
foreign key(bestell_id) references bestellungen(id)
);
insert into bestellungen values(
nextval('rech_seq'),'20010-04-20',6
);
insert into bestell_produkte values (
100,currval('rech_seq'), 6
);
insert into bestell_produkte values (
101,currval('rech_seq'), 10
);
select * from bestell_produkte;
22
2 Tabellen erstellen
produktid | bestell_id | menge
-----------+------------+------100 | 1 | 6
101 | 1 | 10
(2 Zeilen)
select * from bestellungen;
id | best_datum | kundennr
----+-------------+---------1 | 20010-04-20 | 6
(1 Zeile)
Hier wird in die Tabelle bestellungen eine Zeile eingefügt, die nächste Nummer für
den Primärschlüssel wird durch NEXTVAL bestimmt. Diese Nummer muss dann auch als
Foreign-Key für die Tabelle bestell_produkte verwendet werden (um referentielle
Integrität zu garantieren). Damit die Datenbankanwender nicht diesen Wert herausfinden müssen, gibt es die Funktion CURRVAL. Allerdings sollten die drei Insert-Befehle in
einer Transaktion laufen, damit kein anderer Datenbankprozess (der auch diese Sequenz
verwenden will) die Nummer verpfuschen kann.
Interessante Zusatzoptionen beim Erzeugen einer Sequenz:
increment by +2
Schrittweite um 2
minvalue 100
kleinster Wert 100
maxvalue 110
die Sequenz läuft bis 110
cycle
wenn die Nummern verbraucht sind,
beginnt die Sequenz von vorne (100)
Tabelle 2.1: Zusatzoptionen Sequenz
2.12 Zusammengesetzte Datentypen
Der Datentyp ENUM wurde bereits besprochen – hier wird ein eigener Typ konstruiert.
Zusammengesetzte Typen fassen 2 oder mehr Werte zusammen
create type namensfeld as (vorname varchar(40),nachname varchar(40));
create table adressbuch (name namensfeld, adresse text);
insert into adressbuch values(
row('christian','guertler'), 'mauterndorf 18');
2.13 Kommentare
23
select * from adressbuch;
name | adresse
---------------------+---------------(christian,guertler) | mauterndorf 18
select (name).vorname from adressbuch;
vorname
----------christian
(1 Zeile)
2.13 Kommentare
Mittels COMMENT lassen sich Kommentare für viele Datenbankobjekte einfügen
guch=# comment on table adressbuch is 'tabelle adressbuch mit zusammengesetztem datentyp';
COMMENT
guch=# \dd
Objektbeschreibungen
Schema | Name | Objekt | Beschreibung
--------+------------+--------+--------------------------------------------------public | adressbuch | table | tabelle adressbuch mit zusammengesetztem datentyp
(1 Zeile)
In pgAdminIII werden Kommentare automatisch bei den Eigenschaften des Objekts
dargestellt (Abbildung 2.2)
Abbildung 2.2: Kommentare in pgAdmin
Gelöscht werden Kommentare mit
guch=# comment on table adressbuch is null;
COMMENT
3
Tabellen verändern
Prinzipiell dient zum Verändern einer Tabelle der Befehl ALTER; damint können Spalten
und Constraints hinzugefügt, geändert oder gelöscht werden.
3.1 Hinzufügen von Spalten
• ALTER TABLE person ADD COLUMN land varchar;
Fügt eine Spalte namens land vom Typ varchar hinzu. Folgender Befehl funktioniert nicht,
ALTER TABLE person ADD COLUMN land VARCHAR NOT NULL
weil beim Hinzufügen einer Spalte anfangs alle neuen Werte auf NULL gesetzt werden, was aber vom NOT NULL-Constraint untersagt wird.
• Default-Werte können aber verwendet werden.
ALTER TABLE person ADD COLUMN land VARCHAR DEFAULT 'A';
• Es können auch mehrere Spalten auf einmal hinzugefügt werden.
ALTER TABLE person
ADD COLUMN land VARCHAR DEFAULT 'A',
ADD COLUMN hobby TEXT;
26
3 Tabellen verändern
3.2 Löschen von Spalten
• ALTER TABLE person DROP COLUMN land;
löscht eine Spalte
3.3 Ändern von Spalten
• ALTER TABLE person RENAME COLUMN hobby TO wasichgernetu;
oder kürzer
ALTER TABLE person RENAME hobby TO wasichgernetu;
ändert den Namen einer Spalte
• ALTER TABLE person ALTER COLUMN land SET DEFAULT 'CH';
ändert den Default-Wert
• ALTER TABLE person ALTER COLUMN land DROP DEFAULT;
löscht einen Default-Wert
• Umbenennen von Tabellen
ALTER TABLE person RENAME TO persons;
3.4 Constraints hinzufügen/löschen
• ALTER TABLE person ADD CONSTRAINT email_unique UNIQUE (email)
oder kürzer
ALTER TABLE person ADD UNIQUE(email)
fügen einen UNIQUE-Constraint hinzu
• ALTER TABLE peron DROP CONSTRAINT email_unique;
löscht ihn wieder (funktioniert auch bei FOREIGN KEYS, bzw. PRIMARY KEYS)
• ALTER TABLE person ADD PRIMARY KEY(pid)
fügt einen Primärschlüssel hinzu, die Spalte pid muss schon vorhanden sein!!!
• ALTER TABLE person
ADD COLUMN aid INT,
ADD FOREIGN KEY(aid) REFERENCES abteilung(aid);
fügt eine Spalte namens aid hinzu, die zugleich einen FOREIGN KEY darstellt.
3.4 Constraints hinzufügen/löschen
27
Wie die Constraints heissen, kann im Programm psql so herausgefunden werden:
test=# \d person;
Spalte |
Typ
| Attribute
--------+---------------------+----------pid
| integer
| not null
name | character varying
|
titel | anrede
|
aid
| integer
|
Indexe:
"person_pkey" PRIMARY KEY, btree (pid)
Fremdschlüssel-Constraints:
"person_aid_fkey" FOREIGN KEY (aid) REFERENCES abteilung(aid)
und im pgAdmin3 so:
Abbildung 3.1: Constraints in pgAdmin
4
Daten auslesen
Zum Ermitteln der Werte, die in einer Tabelle gespeichert sind, wird der Befehl SELECT
herangezogen.
SELECT * FROM tabelle
Dies sollte jedoch nur in Ausnahmen verwendet werden (Ressourcenverbrauch). Es sollten immer nur die Spalten angegeben werden, die wirklich für das Ergebnis erforderlich
sind.
4.1 SELECT mit einer Tabelle
Da beim Erstellen von Tabellen und Spalten meist kurze und oft wenig sprechende
Namen verwendet werden, kann beim Auslesen eine Art Alias einbezogen werden.
SELECT spalte1 AS 'meine_spalte' FROM tabelle
Hier steht in der Ausgabe statt ”spalte1” ”meine_spalte”. SELECT-Ergebnisse können
auch geordnet (ORDER BY)ausgegeben werden alphabetisch aufsteigend:
select * from kuenstler order by name asc;
alphabetisch absteigend:
select * from kuenstler order by name desc;
einschränken (LIMIT)auf eine bestimmte Menge (nur die ersten 10 Einträge)
select * from kuenstler order by name desc limit 10;
einschränken (LIMIT)auf eine bestimmte Menge (ab dem 5. Eintrag die nächsten 5).
select * from kuenstler order by name desc limit 5, 5;
30
4 Daten auslesen
4.1.1 Doppelte Werte filtern
SELECT DISTINCT spalte FROM tabelle
DISTINCT filtert doppelte Einträge heraus und listet sie nur einmal auf.
Name
Land
rolling stones
england
beatles
england
napalm death
usa
Tabelle 4.1: Tabelle künstler
Der Befehl SELECT land FROM kuenstler liefert folgendes Ergebnis,
Name
england
england
usa
Tabelle 4.2: Ausgabe
da ”england” zweimal vorhanden ist. Mit ”select distinct land from kuenstler” sieht
es so aus:
Land
england
usa
Tabelle 4.3: Ausgabe mit distinct
4.1.2 SELECT mit COUNT() und GROUP BY
count() zählt die Einträge in einer Tabelle:
• select count(*) from kuenstler;
liefert als Ergebnis 3
31
4.1 SELECT mit einer Tabelle
• select count(distinct land) from kuenstler;
liefert als Ergebnis 2.
• SELECT mit GROUP BY
Bsp: Musikalben
albumnr
album
band
1
autobahn
kraftwerk
2
radio aktiv
kraftwerk
3
mensch maschine
kraftwerk
4
monster movie
can
Tabelle 4.4: Musikalben
select band, count(band) as anzahl from kuenstler group by band;
liefert folgendes Ergebnis:
band
anzahl
kraftwerk
3
can
1
Tabelle 4.5: count mit group by
GROUP BY gruppiert bestimmte Spalten (in diesem Fall Spalte band) und zählt die
Summe der Einträge dieser Gruppierung.
• SELECT mit GROUP BY und HAVING
select band, count(band) as anzahl from kuenstler
group by band having count(band) > 2;
32
4 Daten auslesen
band
anzahl
kraftwerk
3
Tabelle 4.6: mit HAVING
HAVING schränkt in diesem Beispiel das Ergebnis ein auf bands, die mehr als 2
Einträge (having count > 2) haben.
4.1.3 weitere Aggregatfunktionen und Subselects
Neben COUNT(), das die Anzahl der Werte einer Spalte ermittelt, gibt es unter anderem
noch folgende Funktionen:
AVG() - berechnet den Mittelwert (average)
MIN() - ermittelt den Minimalwert einer Spalte
MAX() - ermittelt den Maximalwert einer Spalte
SUM() - berechnet die Summe der Werte dieser Spalte
Beispiele:
select AVG(preis) from alben;
select MAX(albumnr) from alben;
Beispiel Buchdatenbank:
test=# select * from buch;
buchnr | buchtitel | verlagid | year | preis
--------+-------------+----------+------+------1 | SQL
|
1 | 1997 | 45.99
2 | Datenbanken |
2 | 2000 | 35.99
3 | PHP
|
1 | 1998 | 37.99
4 | PostgreSQL |
| 2008 | 50.99
Allerdings sind solche Aggregatfunktionen nicht in einer WHERE-Klausel erlaubt; statt:
select * from buch where preis < AVG(preis);
schreibt man
select * from buch where preis < (select avg(preis) from buch);
und erhält als Ergebnis alle Bücher, deren Preis unter dem Durchschnitt liegt.
buchnr | buchtitel | verlagid | year | preis
--------+-------------+----------+------+------2 | Datenbanken |
2 | 2000 | 35.99
3 | PHP
|
1 | 1998 | 37.99
4.1 SELECT mit einer Tabelle
33
Soll zusätzlich noch der Durchschnittspreis in der Spaltenliste mitausgegeben werden,
so kann dieses Subselect auch in der SELECT-Liste verwendet werden.
select *, (select avg(preis) from buch) from buch where
preis < (select avg(preis) from buch);
und erhält als Ergebnis:
buchnr | buchtitel | verlagid | year | preis |
?column?
--------+-------------+----------+------+-------+--------------------2 | Datenbanken |
2 | 2000 | 35.99 | 42.7400000000000000
3 | PHP
|
1 | 1998 | 37.99 | 42.7400000000000000
Um die Bücher, ihre Preise, den Mittelwert und die Differenz zum Mittelwert auszugeben,
können Subselects kombiniert werden:
select buchtitel, preis,(select avg(preis) from buch) as mittel,
preis - (select avg(preis) from buch) as diff from buch;
und erhält
buchtitel | preis |
mittel
|
diff
-------------+-------+---------------------+--------------------SQL
| 45.99 | 42.7400000000000000 | 3.2500000000000000
Datenbanken | 35.99 | 42.7400000000000000 | -6.7500000000000000
PHP
| 37.99 | 42.7400000000000000 | -4.7500000000000000
PostgreSQL | 50.99 | 42.7400000000000000 | 8.2500000000000000
oder wer ein gerundetes Ergebnis bevorzugt:
select buchtitel, preis,(select round(avg(preis),2) from buch) as mittel,
preis - (select round(avg(preis),2) from buch) as diff from buch;
und erhält
buchtitel | preis | mittel | diff
-------------+-------+--------+------SQL
| 45.99 | 42.74 | 3.25
Datenbanken | 35.99 | 42.74 | -6.75
PHP
| 37.99 | 42.74 | -4.75
PostgreSQL | 50.99 | 42.74 | 8.25
34
4 Daten auslesen
4.1.4 SELECT einschränken mit WHERE
Vergleichsoperatoren (<, >, <=, >=, =,!=, LIKE, IS, IN)
Befehl
Bedeutung
select * from alben where preis < 15
listet die Einträge aus der Tabelle alben,
deren Preis unter 15 liegt
select * from alben where preis > 15
listet die Einträge aus der Tabelle alben,
deren Preis über 15 liegt
select * from alben where preis = 15
listet die Einträge aus der Tabelle alben,
deren Preis genau bei 15 liegt
select * from alben where preis <= 15
listet die Einträge aus der Tabelle alben,
deren Preis kleiner 15 oder genau 15 ist
select * from alben where preis >= 15
listet die Einträge aus der Tabelle alben,
deren Preis größer 15 oder genau 15 ist
select * from alben where preis != 15
listet die Einträge aus der Tabelle alben,
deren Preis anders als (ungleich)15 ist
select * from alben where
preis = 15 and year = 2000
listet die Einträge aus der Tabelle alben,
deren Preis genau 15 ist und deren Jahr
gleichzeitig 2000 beträgt (beide Bedingungen müssen erfüllt sein)
select * from alben where
preis = 15 or year = 2000
listet die Einträge aus der Tabelle alben,
deren Preis genau 15 ist oder deren Jahr
gleichzeitig 2000 beträgt (eine der Bedingungen muss erfüllt sein)
select * from alben where id in (1, 3) listet die Einträge aus der Tabelle alben,
wo die id der Liste in den runden Klammern entspricht (also 1 oder 3 ist)
select * from alben where id
between 1 and 3
listet die Einträge aus der Tabelle alben,
wo die id zwischen inklusive 1 und inklusive 3 liegt
select * from alben where
preis IS NULL
listet Einträge aus der Tabelle alben, wo
der Titel leer ist
Tabelle 4.7: Vergleichsoperatoren
35
4.2 SELECT mit mehreren Tabellen
4.2 SELECT mit mehreren Tabellen
4.2.1 WHERE
F
P
buchnr
buchtitel
verlagid
year
verlagnr
name
1
SQL
1
1997
1
dpunkt
2
Datenbanken
2
2000
2
mitp
3
PHP
1
1998
3
o’reilly
Tabelle 4.8: Buch
Tabelle 4.9: Verlag
Jedes Buch hat einen Verlag zugeordnet (verlagid). Die Spalte verlagid ist ein Foreign
Key und verweist auf die Tabelle verlag und dort auf den Primary Key(verlagid).
select buchtitel, name from buch, verlag where
buch.verlagid = verlag.verlagnr;
liefert Bücher mit ihren Verlagen
buchtitel
name
-------------------SQL
dpunkt
Datenbanken mitp
PHP
dpunkt
------------------Diese Ergebnis liefert nur Ergebnisse, die in beiden Tabellen passende Einträge aufweisen.
Die Tabelle buch ist mit ihrer Spalte verlagid mit der Tabelle verlag und deren Spalte
verlagnr verknüpft. Es wird nichts vom Verlag ”o’reilly” gelistet, da von diesem Verlag
kein Buch in der Tabelle buch existiert.
4.2.2 JOIN
4.2.2.1 INNER JOIN – Paare finden
Ein INNER JOIN findet Paare von Werten, das heisst, solche, bei denen Werte eines
Primärschlüssels mit dem verknüpften Fremdschlüssel übereinstimmen. Werte, die in
einem Primärschlüssel vorhanden sind, aber nicht im verknüpften Fremdschlüssel werden
nicht gefunden. Obiges Statement kann auch so geschrieben werden:
select buch.buchtitel, verlag.name from buch JOIN verlag
ON buch.verlagid = verlag.verlagnr;
36
4 Daten auslesen
Sollten Primärschlüssel und dazugehöriger Fremdschlüssel gleich lauten (was hier nicht
der Fall ist), kann das Statement auch abgekürzt werden:
select buch.buchtitel, verlag.name from buch JOIN verlag USING(verlagid);
4.2.2.2 OUTER JOIN – LEFT/RIGHT JOIN – Lücken finden
Mit einem OUTER JOIN können fehlende Partner aufgespürt werden.
SELECT buchtitel, name FROM buch RIGHT OUTER JOIN verlag ON
buch.verlagid = verlag.verlagnr;
Ergebnis:
buchtitel
| name
-------------+---------SQL
| dpunkt
PHP
| dpunkt
Datenbanken | mitp
| o`reilly
Das Kernstück dieser Abfrage ist »buch RIGHT OUTER JOIN verlag«. RIGHT OUTER
JOIN bewirkt hier, dass aus der rechten (RIGHT) angeführten Tabelle (verlag) alles
ausgegeben wird, auch wenn dieser Verlag kein passendes Buch in der Tabelle buch besitzt. Danach versucht der Befehl, zu allen ausgegebenen Verlagen die passenden Bücher
zu ermitteln; gelingt das nicht, resultiert als Ergebnis NULL – hier eine leere Stelle.
Statt RIGHT OUTER JOIN kann auch LEFT OUTER JOIN verwendet werden, nur müssen
dann die Spalten in der Syntax vertauscht werden. Das Wort OUTER bzw. INNER kann
übrignes weggelassen werden.
SELECT buchtitel, name FROM verlag LEFT JOIN buch ON
buch.verlagid = verlag.verlagnr;
Ohne Vertauschen
SELECT buchtitel, name FROM buch LEFT JOIN verlag ON
buch.verlagid = verlag.verlagnr;
ist das Ergebnis wie im klassischen SELECT. Durch LEFT JOIN wird die linke Tabelle
buch komplett gelistet und ihre evtl. vorhandenen Verlage ermittelt.
buchtitel
name
---------------------SQL
dpunkt
Datenbanken mitp
PHP
dpunkt
4.2 SELECT mit mehreren Tabellen
37
Mit rechter, bzw. linker Tabelle ist die Tabelle gemeint, die links (LEFT) bzw. rechts
(RIGHT) von der Klausel LEFT JOIN bzw. RIGHT JOIN steht.
4.2.2.3 FULL OUTER JOIN – beidseitige Lücken finden
Angenommen, es wird in die Tabelle buch ein Buch hinzugefügt, das keinem passenden
Verlag zugeordnet ist, so können mit einem FULL OUTER JOIN Lücken auf beiden Seiten
gefunden werden.
test=# insert into buch values (4, 'PostgreSQL',NULL, 2008);
INSERT 0 1
test=# select buchtitel, name from buch full outer join verlag
on buch.verlagid = verlag.verlagnr;
buchtitel
| name
-------------+---------SQL
| dpunkt
PHP
| dpunkt
Datenbank | mitp
PostgreSQL |
| o`reilly
Das Wort OUTER kann weggelassen werden.
4.2.2.4 JOIN mit WHERE
Ergebnisse eines JOINs können mit WHERE – wie beim klassischen SELECT – weiter eingeschränkt werden.
test=# select buchtitel, name from buch full join verlag
on buch.verlagid = verlag.verlagnr where buchtitel like 'P%';
buchtitel | name
-------------+-------PHP
| dpunkt
PostgreSQL |
(2 Zeilen)
4.2.3 SELECT mit komplexeren Abfragen
4.2.3.1 AND/OR
select buchtitel, name, year from buch, verlag where
buch.vid = verlag.vid AND (buch.year = 1998 OR buch.year = 1997);
Hier wird AND mit OR gemeinsam verwendet. Dann muss der Ausdruck, der OR enthält,
in runde Klammern gesetzt werden. Das heißt, dieser Ausdruck wird von MySQL zuerst
38
4 Daten auslesen
separat behandelt und das Ergebnis dann mit der AND-Abfrage verknüpft.
Obiges Beispiel ausgesprochen heißt: liste alles aus den Tabellen buch und verlag, wo
die Spalten vid übereinstimmen und wo das Jahr entweder 1997 oder 2000 beträgt. Das
Ergebnis:
buchtitel name
year
--------------------------SQL
dpunkt
1997
PHP
dpunkt
1998
Das Buch Datenbanken hat zwar auch einen passenden Partner in der Tabelle Verlag,
aber es ist aus dem Jahr 2000 und nicht 1997 oder 1998. Achtung: folgendes Kommando
funktioniert nicht (ein häufiger Fehler)
select buchtitel, name, year from buch, verlag where
buch.vid = verlag.vid and (buch.year = 1998 AND buch.year = 1997);
Ein Jahreseintrag kann nicht gleichzeitig 1997 UND 1998 sein!!!
4.2.3.2 COUNT/GROUP BY
In folgendem Beispiel wird die Anzahl der Bücher pro Verlag und eine Summe der Preise
pro Verlag ermittelt.
test=# alter table buch add column preis numeric(5,2);
ALTER TABLE
test=# update buch set preis = '45.99' where buchnr = 1;
UPDATE 1
test=# update buch set preis = '35.99' where buchnr = 2;
UPDATE 1
test=# update buch set preis = '37.99' where buchnr = 3;
UPDATE 1
test=# update buch set preis = '50.99' where buchnr = 4;
UPDATE 1
test=# select * from buch;
buchnr | buchtitel
| verlagid | year | preis
--------+-------------+-----------+------+------1 | SQL
|
1 | 1997 | 45.99
2 | Datenbanken |
2 | 2000 | 35.99
3 | PHP
|
1 | 1998 | 37.99
4 | PostgreSQL
|
| 2008 | 50.99
(4 Zeilen)
4.2 SELECT mit mehreren Tabellen
39
test=# select verlag.name, count(*), sum(preis) from buch, verlag where
verlag.verlagnr = buch.verlagid group by verlag.name;
name
| count | sum
--------+-------+------dpunkt |
2 | 83.98
mitp
|
1 | 35.99
5
Bestehende Daten ändern/löschen
5.1 UPDATE – ändern
neuer_wert2,..,spalte3=neuer_wert3] WHERE kriterium;
Beispiel:
update kuenstler set name = 'hansi hinterseer' where id = 1;
Dieser Befehl ändert den bestehenden Namen in der Tabelle kuenstler auf ”hansi hinterseer”, und zwar dort, wo die id 1 ist (where id = 1).
Beachte den Unterschied zwischen UPDATE und ALTER:
UPDATE ändert Werte (bestehende) in einer Tabelle
ALTER ändert die Tabellenstruktur (keine Werte, ausser wenn z. B. Spalten
gelöscht werden)
5.2 DELETE – löschen
DELETE FROM tabelle [WHERE kriterium];
DELETE FROM tabelle . . . ohne nähere Angaben löscht alle Werte!!!
delete from kuenstler where id = 3;
löscht nur den Eintrag, wo die id auf 3 gesetzt war. Beim Löschen - und auch beim
Update sollte das Kriterium so gewählt werden, dass möglichst nur die gewünschten
Werte geändert werden.