GAMBAS BOOK 3.19.5

So that you can follow all the examples presented, you should create a DB user 'test' and a database 'test' for your DBMS MySQL. The new DB user is granted full access to the database `test`.

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

Start the DB monitor mysql in a console as DB administrator with the DB name root and the password ampw, which is requested by the -p option to establish a connection to the DB server:

$ mysql --host=127.0.0.1 --port=3306 -u root -p
$ mysql -h localhost -P 3306 -u test -p		  '-- Alternative

The following applies to working with the DB client programme:

• First select the specified SQL statement or mysql command,
• then switch to the console with the DB monitor and finally
• enter the SQL statement or the mysql command with <CTRL>+<SHIFT>+V or with 'Paste' from the context menu (RMT).

CREATE USER IF NOT EXISTS 'test'@'localhost' 
  IDENTIFIED BY 'test'
  COMMENT 'MySQL-Test-User';

This gives you an overview of all properties of the DB user test on the DB server:

mysql> SELECT * FROM mysql.user WHERE user='test'\G

Creating a database is done quickly. The following instruction creates a new database on the MySQL DB server:

mysql> CREATE DATABASE test;

The DB user `test` - by the DB administrator - is then given rights for the database `test` and all objects in it, such as tables, views or triggers. The restriction remains that the database user `test` can only log on to the DB server locally:

mysql> GRANT ALL PRIVILEGES ON test.* TO 'test'@'localhost';
mysql> quit

When you exit the DB monitor mysql, your work as DB administrator is finished for the time being and you now switch from DB administrator to your role as DB user. This begins with you entering

• establish a connection to the DB server from the DB client with the DB account of the DB user (test, test),
• view all databases, connect to the database `test`,
• create a new DB table,
• insert data into the table and
• read data from the DB table from certain DB fields.

The command `SHOW DATABASES` displays an overview of all databases on the DB server. Attention: As you are logged in as DB user `test` on the MySQL DB server, this overview only contains the databases to which the logged-in DB user `test` has access:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

Now switch to the database `test` with the command `USE <database_name>`:

mysql> USE test;
Database changed

You can also connect to a specific database immediately at startup if you use this command:

$ mysql --host=127.0.0.1 --port=3306 -D test -u test -p

To create a table, use the following SQL statement:

CREATE TABLE IF NOT EXISTS 
  test 
    ( 
      id 	INT NOT NULL AUTO_INCREMENT,
      vorname 	VARCHAR(15) NOT NULL, 
      nachname 	VARCHAR(25) NOT NULL, 
      wohnort 	VARCHAR(20) NOT NULL, 
      plz 	VARCHAR(5), 
      strasse 	VARCHAR(30), 
      gebdatum 	DATE, 			        -- Birthday. The DB storage format is: yyyy-mm-dd.
      tfestnetz VARCHAR(15), 
      tmobil 	VARCHAR(15), 
      email 	VARCHAR(30),
      CONSTRAINT pk_to_id PRIMARY KEY(id) 	-- Without a final comma!
)
;

The success of the action is confirmed by the output of the following command

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+

The command `DESCRIBE <db_tablename> always provides you with a detailed description of the DB fields for a specific DB table:

mysql> DESCRIBE test;
 
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int         | NO   | PRI | NULL    | auto_increment |
| vorname   | varchar(15) | NO   |     | NULL    |                |
| nachname  | varchar(25) | NO   |     | NULL    |                |
| wohnort   | varchar(20) | NO   |     | NULL    |                |
| plz       | varchar(5)  | YES  |     | NULL    |                |
| strasse   | varchar(30) | YES  |     | NULL    |                |
| gebdatum  | date        | YES  |     | NULL    |                |
| tfestnetz | varchar(15) | YES  |     | NULL    |                |
| tmobil    | varchar(15) | YES  |     | NULL    |                |
| email     | varchar(30) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

The table is still empty - as confirmed by a query of all data records in the DB table `test`:

mysql> SELECT * FROM test;
Empty set (0.00 sec)

Enter 45 data records in the DB table `test`. Select the SQL statement below, switch to the console with the DB monitor and enter the SQL statement with <CTRL>+<SHIFT>+V:

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

You can use the following SQL statement to output the data from the fields “first name”, “last name”, “postcode”, “city” and the calculated current age, for example - but only from locations whose postcode begins with a 3:

SELECT vorname AS "Vorname", nachname AS "Nachname", plz AS "PLZ", wohnort AS "Ort", 
(YEAR(NOW()) - YEAR(GebDatum) - IF(DAYOFYEAR(NOW()) < DAYOFYEAR(CONCAT(YEAR(NOW()), DATE_FORMAT(GebDatum,'-%m-%d'))),1,0)) AS "Alter" 
FROM test
WHERE plz LIKE '3%'
ORDER BY nachname DESC;

mysql> SELECT  vorname AS "Vorname", nachname AS "Nachname", plz AS "PLZ", wohnort AS "Ort", 
    -> (YEAR(NOW()) - YEAR(GebDatum) - IF(DAYOFYEAR(NOW()) < DAYOFYEAR(CONCAT(YEAR(NOW()), DATE_FORMAT(GebDatum,'-%m-%d'))),1,0)) AS "Alter" 
    -> FROM    test
    -> WHERE   plz LIKE '3%'
    -> 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 in set (0,00 sec)
 
mysql> quit
Bye
hans@mint20:~$