mag. christian gürtler - multiaugustinumguch/images/9/9c/postgresql.pdf · 2 tabellen erstellen in...

Mag. Christian Gürtler

SQL – Relationale DatenbankenPostgreSQL

MultiAugustinum 2010

Inhaltsverzeichnis

1 Installation und Anmelden 51.1 Terminal, DOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51.2 pgAdmin3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51.3 Nach dem Anmelden . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

2 Tabellen erstellen 72.1 Numerische Datentypen . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82.2 Texte . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92.3 Binärdaten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92.4 Datum- und Uhrzeit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92.5 Geometrische Datentypen . . . . . . . . . . . . . . . . . . . . . . . . . . . 102.6 Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112.7 Netzwerkdatentypen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132.8 Enumerations – Aufzählungen . . . . . . . . . . . . . . . . . . . . . . . . . 142.9 weitere Attribute und Constraints . . . . . . . . . . . . . . . . . . . . . . 142.10 Beispiele . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172.11 Sequenzen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202.12 Zusammengesetzte Datentypen . . . . . . . . . . . . . . . . . . . . . . . . 222.13 Kommentare . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

3 Tabellen verändern 253.1 Hinzufügen von Spalten . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253.2 Löschen von Spalten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263.3 Ändern von Spalten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263.4 Constraints hinzufügen/löschen . . . . . . . . . . . . . . . . . . . . . . . . 26

4 Daten auslesen 294.1 SELECT mit einer Tabelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

4.1.1 Doppelte Werte filtern . . . . . . . . . . . . . . . . . . . . . . . . . 304.1.2 SELECT mit COUNT() und GROUP BY . . . . . . . . . . . . . . 304.1.3 weitere Aggregatfunktionen und Subselects . . . . . . . . . . . . . 324.1.4 SELECT einschränken mit WHERE . . . . . . . . . . . . . . . . . 34

4.2 SELECT mit mehreren Tabellen . . . . . . . . . . . . . . . . . . . . . . . 354.2.1 WHERE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35

4 Inhaltsverzeichnis

4.2.2 JOIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 354.2.2.1 INNER JOIN – Paare finden . . . . . . . . . . . . . . . . 354.2.2.2 OUTER JOIN – LEFT/RIGHT JOIN – Lücken finden . 364.2.2.3 FULL OUTER JOIN – beidseitige Lücken finden . . . . 374.2.2.4 JOIN mit WHERE . . . . . . . . . . . . . . . . . . . . . 37

4.2.3 SELECT mit komplexeren Abfragen . . . . . . . . . . . . . . . . . 374.2.3.1 AND/OR . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374.2.3.2 COUNT/GROUP BY . . . . . . . . . . . . . . . . . . . . . . . 38

5 Bestehende Daten ändern/löschen 415.1 UPDATE – ändern . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415.2 DELETE – löschen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41

1Installation und Anmelden

PostgreSQL ist ein ziemlich mächtiger Datenbankserver, der sich fast vollständig an denSQL-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. momentan nur

32-Bit-Version

verfügbarUnter auf Linux kann auch PostgreSQL mit dem jeweiligen Paketmanager installiertwerden, der One-Click-Installer liefert aber auch das grafische Werkzeug pgAdmin3.Diese Doku liefert nur einen Überblick über PostgreSQL, für detaillierter Infos bitteunter http://www.postgresql.org/docs/8.4/static/index.html oderhttp://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 desParameters −U).

1.2 pgAdmin3

Hier muss zuerst eine Verbindung aufgebaut werden (Abbildung 3.1).

http://www.enterprisedb.com/products/pgdownload.do

http://www.postgresql.org/docs/8.4/static/index.html

http://www.postgresql.org/files/documentation/books/pghandbuch/html/

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 Datenbanknicht vorhanden sein, kann durch Rechtsklick auf die Serververbindung eine neue Da-tenbank erstellt werden, im Terminal geschieht dies durch

guch=# create database kino;

CREATE DATABASE

guch=#

2Tabellen erstellen

In dieser Datenbank können dann Tabellen erstellt werden. Tabellen sind ja in SQL ausSpalten 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 Spaltenwerden dann zeilenweise mit Werten befüllt. Damit in jeder Spalte die richtigen Wertegespeichert werden (und nicht beispielsweise bei gehalt ein Vorname) gibt es sogenannteDatentypen.

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 ±2Milliarden

• BIGINT, INT8: 8 Byte Speicherbedarf

• REAL: 4 Byte Speicherbedarf, Fließkommazahl mit max. 6 Nachkommastellen, un-genaue Präzision (Rundung)

• DOUBLE PRECISION: 8 Byte Speicherbedarf, 15 Stellen gespeichert

• NUMERIC, DECIMAL: Speicherbedarf größenabhängig, exakte Speicherung ohne Run-dung (Bankwesen)

• SERIAL, SERIAL4: 4 Byte Speicherbedarf, Sequenz vom Typ INT, für fortlaufendeNummern

• 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 gespeichertwie 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ährendnumeric genau rechnet.

Es können bei numerischen Datentypen Längenangaben erfolgen:

NUMERIC(10) 10 Stellen, keine NachkommaNUMERIC(10,2) 10 Stellen gesamt, davon 2 NachkommaNUMERIC Anzahl Ziffern vor/nach Komma egal

2.2 Texte

• VARCHAR: laut SQL-Standard ist eine Längenangabe vorgeschrieben, PostgreSQLverlangt keine Angabe; ist eine vorhanden, wird sie verwendet (VARCHAR(40) – hierkönnen bis zu 40 Zeichen gespeichert werden).

• CHAR: wie bei VARCHAR ist auch hier laut Standard eine Längenangabe nötig, beiPostgreSQL ist sie optional, allerdings bedeutet CHAR, dass nur 1 Zeichen gespei-chert 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änkungangegeben werden.

2.3 Binärdaten

• BYTEA: damit können Null-Bytes oder andere nicht-druckbare Zeichen gespeichertwerden, was bei Text-Datentypen nicht möglich ist – zumindest nicht ohne Infor-mationsverlust.

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


• 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.

0

1

2

3

4

5

6

7

0 1 2 3 4 5 6 7

C

AB

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>');



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 undermitteln, 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 denbestehenden 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 sehrpraktisch. Angenommen, eine Gärtnerei speichert zu ihren Blumen die Farben ab, somüsste korrekterweise eine M : N -Beziehung mit drei Tabellen hergestellt werden. Umdiesen Overhaed zu vermeiden, kann die Farbe in einem Array gespeichert werden.


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);


-------+-------------+--------------------

100 | Rose | {rot,weiss,gelb}


(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 amBeginn 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 ak-tualisiert werden

update blumen set farben = string_to_array('blau,grün,violett',',')

where bezeichnung = 'Rose';

select * from blumen;


-------+-------------+---------------------


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 eigentlichaus PHP bekannt sein, der Unterschied liegt darin, dass die Zählung mit dem Wert 1beginnt 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 derEingabe 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 eineIP-Adresse in einem erlaubten Bereich liegt.


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

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 wer-den.Mit PostgreSQL kann ein individueller Datentyp erstellt werden.

Achtung:Groß/Klein-schreibungbeachten

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; dazuist das NOT NULL-Attribut notwendig.

• UNIQUE: wird bei Spalten angewendet, die nicht Primary Key sind, aber dennocheindeutige Werte beinhalten sollen. Beispielsweise in der Tabelle mitarbeiter einerFirma: der Primary Key wäre möglicherweise die Personalnummer, ein UNIQUEkönnte auf die email-Adresse gelegt werden, wenn jeder Mitarbeiter eine eindeutigeemail 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 SpalteGehalt aufweisen, als Standardwert könnte ein Mindestlohn von 1000,— eingetra-gen 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 dieWerte 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üsselbezieht.

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 Serverdü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 einPrimary 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


• email: maximal 100 Zeichen, jede email-Adresse darf nur einmal (in dieser Tabel-le!!!) 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 Standardwerteingetragen, damit die Spalte nicht leer bleibt.

• anrede: es werden genau 4 Zeichen gespeichert, wobei nur die Werte Herr oderFrau 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 KEY-Constraints können bei der jeweiligen Spalte angegeben werden(Spaltenconstraint oder Columnconstraint), mehrspaltige müssenjedoch als Tabellenconstraint (sprich: am Ende des Create-Befehls)geschrieben werden

⋆ daher Unique, Primary- und Foreign Keys am besten am Schlussangeben

⋆ die einzelnen Eigenschaften eines Attributs werden durch Leerzei-chen getrennt aneinander gereiht

Constraints können auch mit einem Namen versehen werden (sinnvoll, um später dar-auf zugreifen zu können – bei Änderungen etc. ), ansonsten vergibt PostgreSQL einenStandardnamen.

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).


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 kontrol-liert, ob der Wert in Spalte price über dem Wert der Spalte discounted_price

liegt.Eine mögliche Schreibweise wäre auch so:


product_no integer,

name text,

price numeric CHECK (price > 0),

discounted_price numeric,

CHECK (discounted_price > 0 AND price > discounted_price)

);


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


product_no integer UNIQUE,

name text,

price numeric);

Beispiel eines Unique-Constraints


• 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 darfdoppelt existieren, auch eine spezielle email-Adresse, aber nicht eine Kombinationaus 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.


product_no integer CONSTRAINT must_be_different UNIQUE,

name text,

price numeric

);

Unique-Constraint mit speziellem Namen.


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.

2.10 Beispiele 19

• 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 inder 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üs-sel, jede Spalte wiederum ist ein Fremdschlüssel auf eine der Tabellen produkte

bzw. bestellungen.


2.11 Sequenzen

Sequenzen sind Zähler, die in beliebig großen Intervallen vor- oder rückwärts zählenkönnen. Die einfachste Variante stellt der Datentyp SERIAL dar. Sequenen können auchmanuell 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), 2

primary key(kdnr)

);

create table mitarbeiter (

mnr int default nextval('meine_seq'),

name varchar(100), 3

primary key(mnr)

);

insert into kunden (name) values ('meier'); 4

insert into mitarbeiter (name) values ('sklave');

5

select * from kunden;

kdnr | name

------+-------

1 | meier

(1 Zeile) 6


mnr | name

-----+--------

2 | sklave

(1 Zeile)

select currval('meine_seq'); 7

currval

---------

2

(1 Zeile)

In 1 wird eine Sequenz erzeugt, in 2 und 3 werden Tabellen erzeugt, die auf die-se Sequenz zugreifen, in 4 und 5 werden Werte eingefügt, die in 6 abgerufenwerden. 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');


mnr | name

-----+----------------

2 | sklave

3 | sklaventreiber

(2 Zeilen)

Warum werden Sequenzen verwendet, wenn es doch auch SERIAL gibt? Die Verwendungvon NEXTVAL, CURRVAL bietet sich bei Bezieungen mit foreign keys an, hier muss jader 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;


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ürden Primärschlüssel wird durch NEXTVAL bestimmt. Diese Nummer muss dann auch alsForeign-Key für die Tabelle bestell_produkte verwendet werden (um referentielle

Integrität zu garantieren). Damit die Datenbankanwender nicht diesen Wert heraus-finden müssen, gibt es die Funktion CURRVAL. Allerdings sollten die drei Insert-Befehle ineiner Transaktion laufen, damit kein anderer Datenbankprozess (der auch diese Sequenzverwenden 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 Objektsdargestellt (Abbildung 2.2)

Abbildung 2.2: Kommentare in pgAdmin

Gelöscht werden Kommentare mit

guch=# comment on table adressbuch is null;

COMMENT

3Tabellen verändern

Prinzipiell dient zum Verändern einer Tabelle der Befehl ALTER; damint können Spaltenund 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 funktio-niert nicht,

ALTER TABLE person ADD COLUMN land VARCHAR NOT NULL

weil beim Hinzufügen einer Spalte anfangs alle neuen Werte auf NULL gesetzt wer-den, 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

4Daten 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 soll-ten immer nur die Spalten angegeben werden, die wirklich für das Ergebnis erforderlichsind.

4.1 SELECT mit einer Tabelle

Da beim Erstellen von Tabellen und Spalten meist kurze und oft wenig sprechendeNamen 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önnenauch 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” siehtes 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

4.1 SELECT mit einer Tabelle 31

• select count(distinct land) from kuenstler;

liefert als Ergebnis 2.

• SELECT mit GROUP BYBsp: 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 dieSumme 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 2Einträ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 anderemnoch folgende Funktionen:AVG() - berechnet den Mittelwert (average)MIN() - ermittelt den Minimalwert einer SpalteMAX() - ermittelt den Maximalwert einer SpalteSUM() - berechnet die Summe der Werte dieser SpalteBeispiele:

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.


--------+-------------+----------+------+-------

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 Jahrgleichzeitig 2000 beträgt (beide Bedingun-gen müssen erfüllt sein)


preis = 15 or year = 2000

listet die Einträge aus der Tabelle alben,deren Preis genau 15 ist oder deren Jahrgleichzeitig 2000 beträgt (eine der Bedin-gungen 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 Klam-mern 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 inklu-sive 3 liegt


preis IS NULL

listet Einträge aus der Tabelle alben, woder Titel leer ist

Tabelle 4.7: Vergleichsoperatoren

4.2 SELECT mit mehreren Tabellen 35

4.2 SELECT mit mehreren Tabellen

4.2.1 WHERE

buchnr buchtitel verlagid year

1 SQL 1 1997

2 Datenbanken 2 2000

3 PHP 1 1998

Tabelle 4.8: Buch

verlagnr name

1 dpunkt

2 mitp

3 o’reilly

Tabelle 4.9: Verlag

F P

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 Spalteverlagnr verknüpft. Es wird nichts vom Verlag ”o’reilly” gelistet, da von diesem Verlagkein 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 einesPrimärschlüssels mit dem verknüpften Fremdschlüssel übereinstimmen. Werte, die ineinem Primärschlüssel vorhanden sind, aber nicht im verknüpften Fremdschlüssel werdennicht 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 nichtder 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


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) allesausgegeben wird, auch wenn dieser Verlag kein passendes Buch in der Tabelle buch be-sitzt. Danach versucht der Befehl, zu allen ausgegebenen Verlagen die passenden Bücherzu 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üssendann 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


Ohne Vertauschen

SELECT buchtitel, name FROM buch LEFT JOIN verlag ON


ist das Ergebnis wie im klassischen SELECT. Durch LEFT JOIN wird die linke Tabellebuch komplett gelistet und ihre evtl. vorhandenen Verlage ermittelt.

buchtitel name

----------------------

SQL dpunkt

Datenbanken mitp

PHP dpunkt


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 passendenVerlag zugeordnet ist, so können mit einem FULL OUTER JOIN Lücken auf beiden Seitengefunden 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 ein-geschrä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, wodie Spalten vid übereinstimmen und wo das Jahr entweder 1997 oder 2000 beträgt. DasErgebnis:

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 Kommandofunktioniert 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 Preisepro 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


UPDATE 1


UPDATE 1


UPDATE 1

test=# select * from buch;


--------+-------------+-----------+------+-------

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)


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

5Bestehende 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 hin-terseer”, und zwar dort, wo die id 1 ist (where id = 1).

Beachte den Unterschied zwischen UPDATE und ALTER:UPDATE ändert Werte (bestehende) in einer TabelleALTER ändert die Tabellenstruktur (keine Werte, ausser wenn z. B. Spaltengelö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 beimUpdate sollte das Kriterium so gewählt werden, dass möglichst nur die gewünschtenWerte geändert werden.

mag. christian gürtler - multiaugustinumguch/images/9/9c/postgresql.pdf · 2 tabellen erstellen in...

Documents