GAMBAS-BUCH 3.19.5

Es gehört u.a. zu den Aufgaben eines DB-Administrators

• neue Datenbank-Benutzer zu erzeugen,
• neue Datenbanken anzulegen und
• für jeden DB-Benutzer die erforderlichen DB-Rechte festzulegen.

Für diese Arbeiten können Sie eine geeignete Software wie pgadmin3 oder dbeaver einsetzen oder nutzen ein DB-Clientprogramm wie psql. Damit Sie alle vorgestellten Beispiele nachvollziehen können, sollten Sie für Ihr DBMS PostgreSQL einen DB-Benutzer `test` und eine Datenbank `test` anlegen. Dem neuen DB-Benutzer wird voller Zugriff auf die Datenbank `test` gewährt.

DB-Benutzer:    test
DB-Passwort:    test
Datenbankname:  test

Starten Sie den DB-Monitor psql in einer Konsole als DB-Administrator mit dem DB-Namen postgres und dem Passwort appw, dass durch die Option -W eingefordert wird, um eine Verbindung zum DB-Server herzustellen:

$ psql -h 127.0.0.1 -p 5432 -U postgres -W

Für das Arbeiten mit dem DB-Clientprogramm gilt:

• Zuerst markieren Sie die angegebene SQL-Anweisung oder das psql-Kommando, das stets mit einem Backslash beginnt,
• dann wechseln Sie in die Konsole mit dem DB-Monitor und abschließend
• geben Sie die SQL-Anweisung oder das psql-Kommando mit <CTRL>+<SHIFT>+V oder mit 'Einfügen' aus dem Kontext-Menü (RMT) ein:

postgres=# CREATE ROLE test WITH LOGIN ENCRYPTED PASSWORD 'test';
postgres=# COMMENT ON ROLE test IS 'PostgreSQL-Test-User';

So erhalten Sie einen Überblick über alle angelegten DB-Benutzer auf dem DB-Server:

postgres=# \du+

Das Anlegen einer Datenbank ist schnell erledigt. Beachten Sie, dass die Datenbank einen Besitzer hat – den im ersten Abschnitt erzeugten DB-Benutzer test.

• Zuerst markieren Sie die angegebene SQL-Anweisung oder das psql-Kommando, das stets mit einem Backslash beginnt,
• dann wechseln Sie in die Konsole mit dem DB-Monitor und abschließend
• geben Sie die SQL-Anweisung oder das psql-Kommando mit <CTRL>+<SHIFT>+V oder mit 'Einfügen' aus dem Kontext-Menü (RMT) ein:

Mit der folgenden Anweisung wird eine neue Datenbank auf dem DB-Server erzeugt:

CREATE DATABASE test
  WITH OWNER = test
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'de_DE.UTF-8'
       LC_CTYPE = 'de_DE.UTF-8'
       CONNECTION LIMIT = -1;

Es folgt die Ausführung im DB-Clientprogramm, das bei erfolgreicher Durchführung 'CREATE DATABASE' mitteilt:

postgres=# CREATE DATABASE test
  WITH OWNER = test
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'de_DE.UTF-8'
       LC_CTYPE = 'de_DE.UTF-8'
       CONNECTION LIMIT = -1;
CREATE DATABASE
postgres=#

Anschließend wird der Datenbank optional eine kurze Beschreibung mitgegeben:

postgres=# COMMENT ON DATABASE test IS 'Test-Database';

Mit dem Kommando \d+ wird eine Übersicht aller Datenbanken auf dem DB-Server ausgegeben:

postgres=# \d+
postgres=# \quit

Mit dem Beenden des DB-Monitors psql ist Ihre Arbeit als DB-Administrator erst einmal beendet. Jetzt wechseln Sie vom DB-Administrator zu Ihrer Rolle als DB-Benutzer. Die beginnt damit, dass Sie

• vom DB-Client aus mit dem DB-Konto des DB-Benutzers (test, test) eine Verbindung zum DB-Server herstellen,
• sich mit der Datenbank `test` verbinden,
• eine neue DB-Tabelle anlegen,
• Daten in die Tabelle einfügen und
• Daten aus der DB-Tabelle von bestimmten DB-Feldern auslesen.

hans@mint20:~$ psql -h 127.0.0.1 -p 5432 -U test -W
Password: 
psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
 
test=> \connect test
Password: 
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "test" as user "test".
test=> 

Fixer geht die direkte Verbindung zur Datenbank `test` mit dem um die Option –dbname erweiterten Kommando:

hans@mint20:~$ psql -h 127.0.0.1 -p 5432 --dbname=test -U test -W

Für das Anlegen einer Tabelle verwenden Sie die folgende SQL-Anweisung:

CREATE TABLE IF NOT EXISTS 
  test 
    (
  	id 		SERIAL PRIMARY KEY,
  	vorname 	TEXT NOT NULL, 
  	nachname 	TEXT NOT NULL, 
  	wohnort 	TEXT NOT NULL, 
  	plz 		TEXT, 
  	strasse 	TEXT, 
  	gebdatum 	DATE, 		-- Geburtstag. Das DB-Speicherformat ist:  yyyy-mm-dd. ← Kommentar
  	tfestnetz 	TEXT, 
  	tmobil 	TEXT, 
  	email 		TEXT		-- Ohne abschließendes Komma!
)
;

Option: Anschließend wird auch die DB-Tabelle `test` mit einer kurzen Beschreibung ausgestattet:

postgres=# COMMENT ON TABLE test IS 'Test-Table';

Mit dem psql-Kommando `\d test` oder `\d+ test` erhalten Sie stets eine Beschreibung der DB-Felder einer DB-Tabelle:

test=> \d test
                              Table "public.test"
  Column   |  Type   | Collation | Nullable |             Default              
-----------+---------+-----------+----------+----------------------------------
 id        | integer |           | not null | nextval('test_id_seq'::regclass)
 vorname   | text    |           | not null | 
 nachname  | text    |           | not null | 
 wohnort   | text    |           | not null | 
 plz       | text    |           |          | 
 strasse   | text    |           |          | 
 gebdatum  | date    |           |          | 
 tfestnetz | text    |           |          | 
 tmobil    | text    |           |          | 
 email     | text    |           |          | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

Tragen Sie 45 Datensätze in die DB-Tabelle `test` ein. Markieren Sie die u.a. SQL-Anweisung, wechseln in die Konsole mit dem DB-Monitor und geben die SQL-Anweisung mit <CTRL>+<SHIFT>+V ein:

INSERT INTO test (vorname,nachname,wohnort,plz,strasse,gebdatum,tfestnetz,tmobil,email) 
  VALUES
    ('Arno','Adler','Arneburg','39606','Am Hafen 3','1981-12-08','03937864322','1715749482','arno.adler@arneburg.de'),
    ('Bruno','Bär','Berlin','10404','Bode-Strasse 1','1986-12-06','03094157777','1716771528','bruno.baer@freenet.de'),
    ('Gerda','Geier','Gera','07997','Gartenweg 23','1980-09-12','03657788989','1714472473','gerda.geier@gera.de'),
    ('Lutz','Lama','Leipzig','04103','Lessing-Allee 5','1989-02-25','0641432222','1717346836','lutz.lama@wwleipzig.de'),
    ('Maria','Meise','München','80805','Malergasse 10','1980-07-20','0867554324','1716821096','maria.meise@mawa.com'),
    ('Emil','Elch','Erfurt','99033','Eggert-Strasse 3c','1989-04-18','0361334455','1714287196','emil.elch@erfurt.de'),
    ('Detlef','de Hirsch','Dresden','01067','Deichweg 8','1984-01-28','0351876544','1716271745','detlef.dehirsch@dresden.de'),
    ('Hans-Helmut','Huhn','Hamburg','22111','Hafengasse 90','1988-01-26','04067554008','1716360418','hhhuhn@arcor.com'),
    ('Friedrich','Fledermaus','Frankfurt a.M.','60308','Flusenweg 12','1983-01-01','06101666664','1715209075','fledermaus74@web.de'),
    ('Norbert','Natter','Nürnberg','90402','Nord-Strasse 6a','1980-04-05','091155224324','1713545289','norbert.natter@web.de'),
    ('Clara','Chamäleon','Celle','29229','Claus-Kurt-Weg 1','1990-05-28','05141554678','1717398273','clara.camae@leon.de'),
    ('Ingelore','Igel','Immenstadt i.A.','87509','Imm-Reute 3','1988-12-10','08328552233','1715500891','ingelore.igel@web.de'),
    ('Stephanie','Storch','Stendal','39596','Strohgasse 55b','1989-05-10','039312232366','1716249744','stephanie.storch@reiseland.de'),
    ('Kurt','Kater','Köln','29229','Kanalstrasse 107','1985-05-11','0221767878789','1716968271','kurt.kater@koeln-nord.de'),
    ('Susanne','Sperling','Stuttgart','70173','Strasse der Einheit 3','1988-11-28','05141554678','1713955401','susanne.sperling@aol.com'),
    ('Doreen','Delphin','Dessau-Roßlau','06842','Drosselweg 2','1983-08-01','03455662271','1714171247','ddelphin@arcor.net'),
    ('Andreas','Ameise','Aachen','52064','An der Mauer 4','1982-04-19','02419854332','1715571201','andreas.ameise@aachen.de'),
    ('Fred','Fliege','Fürstenfeldbruck','82276','Feldrain 2','1985-06-23','08148642278','1717564483','fred.fliege@web.de'),
    ('Brunhilde','Bussard','Berlin','10405','Berg-Strasse 44c','1987-11-12','03066422783','1714613147','brunhilde.bussard@web.de'),
    ('Karl-Heinz','Kranich','Kleve','82276','Kurze Strasse 9','1985-02-11','028216666774','1715671343','khkranich@kranich.net'),
    ('Ziri','Zebra','Zittau','02763','Zur Post 33e','1983-06-08','035853376223','1716739494','ziri.zebra@zittauerland.de'),
    ('Paul','Pferd','Pinneberg','25421','Paradegasse 5','1990-03-01','041015555577','1714546720','paul.pferd@rennbahn.de'),
    ('Quirina','Qualle','Querfurt','06268','Quartz-Steg 33','1985-04-28','03477123456','1713455231','quirina.qualle@arcor.de'),
    ('Rüdiger','Rüsselkäfer','Rüdesheim','65385','Radweg 23','1980-06-03','067220042278','1714576104','r.kaefer@web.de'),
    ('Victor','Vogelspinne','Vogelsberg','99610','Vor dem Tore 4-5','1980-11-12','03637290887','1714737048','vvspinne@herbarium.de'),
    ('Jens-Peter','Jaguar','Jena','82276','Janusweg 66','1984-09-14','036410815','1716538095','jp.jaguar@juga.de'),
    ('Otto','Otter','Oldenburg i.H.','23779','Oberer Steg 2','1981-06-22','046313423888','1716342612','otto.otter@freenet.de'),
    ('Thomas','Taube','Taubenheim','02689','Tannwald-Strasse 4','1984-10-18','03593650441','1714320997','tom.taube@taubeev.de'),
    ('Ullrich','Uhu','Ulm','89073','Ulmenweg 7','1988-02-07','073181862258','1713517261','ullrich.uhu@ulm.de'),
    ('Walter','Wisent','Wittenberge','19322','Wiesenweg 50','1989-05-14','038771111258','1715563424','walter.wisent@web.de'),
    ('Dorothea','Dorsch','Düsedau','39606','Dammer Weg 123','1982-07-03','03937345222','1715128617','dodo@dorsch.com'),
    ('Ernst','Esel','Erfurt','99033','Eckstrasse 6','1989-01-18','0361337612','1715533977','ernst.esel@aol.com'),
    ('Hannelore','Haifisch','Hamburg','22131','Hochbauamt 2-7','1980-05-18','04012384566','1714506260','halohai@freenet.de'),
    ('Hans','Hundt','Hameln','31785','Hoher Weg 34','1984-10-16','0515176395588','1716869476','hans.hundt@haba.com'),
    ('Nancy','Nasenbär','Naumburg','06618','Neue Strasse 44a','1984-10-15','0344567888','1714332937','nancy.nb@zoo-nb.de'),
    ('Wolfgang','Wolf','Werra','06766','Waldweg 6a','1984-10-14','036922123','1716355311','wolf.wolter@werra-berg.de'),
    ('Erwin','Eule','Erfurt','99099','Erzberg 6','1988-11-11','0361234333','1718885311','eeule@freenet.de'),
    ('Bernd','Bisam','Berlin','10315','Bad-Ring 1c','1990-09-09','0304409999','1527035312','bernd@bisam-park.de'),
    ('Hans','Hase','Hassel','39695','Hain 12','1988-03-05','0393217752','1727775311','hassel.hase@web.de'),
    ('Stefan','Spatz','Stendal','39576','Seeweg 6a','1989-11-11','03931844123','1715454561','spatz89@aol.com'),
    ('Karl','Keiler','Kassel','34117','Kleiner Weg 3','1988-12-12','05618967944','1527022212','karl.k@arcor.de'),
    ('Xenia','Xanter','Xanten','46509','Xyloner See 2','1989-04-03','0280134556','1612345799','x.y.jolle@freenet.de'),
    ('Luise','Laus','Löbau','02708','Langer Kolken 1','1988-06-23','03587622022','1622323239','familia.l@genua.it'),
    ('Zeno','Zander','Zingst','18374','Ziegenhof 4c','1987-04-04','03823299744','1530345888','zeno.zack@web.de'),
    ('Volker','von Viper','Verden','27283','Volksbad 5c','1987-09-03','04231667293','1724522277','von.viper@web.de');

Mit der folgenden SQL-Anweisung können Sie zum Beispiel die Daten aus den Feldern `vorname`, `nachname`, `plz`, `wohnort` sowie das berechnete aktuelle Alter ausgeben – aber nur von den Orten, deren Postleitzahl mit einer 3 beginnen:

SELECT vorname AS "Vorname", nachname AS "Nachname", plz AS "PLZ", wohnort AS "Ort", 
EXTRACT(YEAR FROM age(gebdatum)) AS "Alter"
FROM test
WHERE plz LIKE '3%'
ORDER BY EXTRACT(YEAR FROM age(gebdatum)) DESC;

test=> SELECT vorname AS "Vorname", nachname AS "Nachname", plz AS "PLZ", wohnort AS "Ort", 
test-> EXTRACT(YEAR FROM age(gebdatum)) AS "Alter"
test-> FROM test
test-> WHERE plz LIKE '3%'
test-> ORDER BY nachname DESC;
  Vorname  | Nachname |  PLZ  |   Ort    | Alter 
-----------+----------+-------+----------+-------
 Stephanie | Storch   | 39596 | Stendal  |    32
 Stefan    | Spatz    | 39576 | Stendal  |    31
 Karl      | Keiler   | 34117 | Kassel   |    32
 Hans      | Hundt    | 31785 | Hameln   |    36
 Hans      | Hase     | 39695 | Hassel   |    33
 Dorothea  | Dorsch   | 39606 | Düsedau  |    38
 Arno      | Adler    | 39606 | Arneburg |    39
(7 rows)
 
test=> quit
hans@mint20:~$