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:
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
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:~$