Mariadb Workshop

MariaDB Installation

MariaDB ist eine freie relationale Datenbank. Die Installation erfolgt über das Paketmanagement. Der Client ermöglicht die Verbindung zur Datenbank.

• apt update && apt install mariadb-server mariadb-client mycli

MariaDB Einrichtung

Nach der Installation sollte MariaDB initial konfiguriert werden, um die Sicherheit zu erhöhen. (Default ist ok, Passwort setzen)

• mysql_secure_installation

Mit dem MariaDB-Client verbinden

Nach der Installation kann man sich mit dem Client zur Datenbank verbinden.

• mysql -u root -p

Anzeigen der Datenbanken

Zeigt alle vorhandenen Datenbanken an.

• show databases;

Wechseln in eine Datenbank

Wechselt in die gewünschte Datenbank.

• use mysql;

Anzeigen der Tabellen einer Datenbank

Zeigt alle Tabellen der aktuellen Datenbank an.

• show tables;

Anzeigen aller Werte einer Tabelle

Zeigt alle Werte einer Tabelle an.

• select * from user;
• Geordnet
• select * from user\G;

Verbindung zur Datenbank beenden

Beendet die aktuelle Sitzung und verlässt den MariaDB-Client.

• exit;

Mit Passwort in der Befehlszeile verbinden

Man kann das Passwort direkt angeben, aber das ist unsicher.

• mysql -u root -p123Start

Datenbank Fahrzeuge anlegen

Erstellt eine neue Datenbank für Fahrzeuge.

• create database fahrzeuge;

Tabelle Autos anlegen

Erstellt eine Tabelle für Autos mit sinnvollen Feldern.

• use fahrzeuge;
• create table autos (

id int auto_increment primary key,
marke varchar(50),
modell varchar(50),
baujahr int,
farbe varchar(30),
preis decimal(10,2)
);

Beispieldaten in die Tabelle Autos einfügen

Fügt verschiedene Autos in die Tabelle ein.

• insert into autos (marke, modell, baujahr, farbe, preis) values

('Volkswagen', 'Golf', 2018, 'blau', 15999.99),
('BMW', '3er', 2020, 'schwarz', 27999.50),
('Mercedes', 'C-Klasse', 2019, 'silber', 31999.00),
('Audi', 'A4', 2021, 'weiß', 34999.99),
('Ford', 'Focus', 2017, 'rot', 13999.00),
('Opel', 'Astra', 2016, 'grau', 8999.50),
('Toyota', 'Corolla', 2022, 'grün', 24999.99),
('Honda', 'Civic', 2020, 'blau', 22999.00),
('Peugeot', '308', 2019, 'weiß', 18999.99),
('Renault', 'Megane', 2018, 'schwarz', 17999.50);

Alle Autos anzeigen

Zeigt alle Autos aus der Tabelle.

• select * from autos;

Bestimmte Spalten anzeigen

Zeigt nur Marke, Modell und Preis.

• select marke, modell, preis from autos;

Autos nach Preis sortieren

Sortiert die Autos nach Preis aufsteigend.

• select * from autos order by preis asc;

Nur Autos einer bestimmten Marke anzeigen

Zeigt nur Autos von BMW.

• select * from autos where marke = 'BMW';

Autos, die teurer als 20.000€ sind

Zeigt nur Autos mit einem Preis über 20.000€.

• select * from autos where preis > 20000;

Autos, die zwischen 2018 und 2021 gebaut wurden

Zeigt nur Autos aus den Baujahren 2018 bis 2021.

• select * from autos where baujahr between 2018 and 2021;

Anzahl der Autos in der Tabelle

Zählt, wie viele Autos gespeichert sind.

• select count(*) from autos;

Durchschnittlicher Preis aller Autos

Berechnet den Durchschnittspreis.

• select avg(preis) from autos;

Teuerstes Auto anzeigen

Zeigt das Auto mit dem höchsten Preis.

• select * from autos order by preis desc limit 1;

Günstigstes Auto anzeigen

Zeigt das Auto mit dem niedrigsten Preis.

• select * from autos order by preis asc limit 1;

Zwei neue Autos hinzufügen

Fügt zwei weitere Autos in die Tabelle ein.

• insert into autos (marke, modell, baujahr, farbe, preis) values

('Skoda', 'Octavia', 2021, 'blau', 25999.99),
('Mazda', 'CX-5', 2022, 'rot', 31999.50);

Ein Auto löschen

Löscht ein Auto anhand der ID.

• delete from autos where id = 12;

Daten aktualisieren

Ändert den Preis eines Autos mit einer bestimmten ID.

• update autos set preis = 29999.99 where id = 2;

Ändert die Farbe aller BMWs auf "blau".

• update autos set farbe = 'blau' where marke = 'BMW';

Aggregationen und Gruppen

Zeigt die durchschnittlichen Preise pro Marke.

• select marke, avg(preis) from autos group by marke;

Zeigt die Anzahl der Autos pro Marke.

• select marke, count(*) from autos group by marke;

Neue Tabelle Kunden anlegen

Fügt eine Tabelle für Kunden hinzu.

• create table kunden (

id int auto_increment primary key,  
name varchar(100),  
telefon varchar(20),  
email varchar(100)  

);

Joins: Autos mit Kunden verknüpfen

Fügt eine Spalte für den Fahrzeugbesitzer hinzu.

• alter table autos add column kunde_id int;

Fügt Kunden in die Kundentabelle ein.

• insert into kunden (name, telefon, email) values

('Max Mustermann', '01234', 'max@example.com'),
('Erika Musterfrau', '05678', 'erika@example.com'),
('Tobias Klein', '09876', 'tobias@example.com'),
('Anna Schmidt', '01112', 'anna@example.com'),
('Lukas Weber', '02223', 'lukas@example.com'),
('Laura Braun', '03334', 'laura@example.com'),
('Jonas Wolf', '04445', 'jonas@example.com'),
('Mia Fischer', '05556', 'mia@example.com'),
('Paul Richter', '06667', 'paul@example.com'),
('Lea Hoffmann', '07778', 'lea@example.com'),
('Tom Berger', '08889', 'tom@example.com');

Verknüpft Autos mit Kunden über die ID.

• update autos set kunde_id = 1 where id = 1;
• update autos set kunde_id = 2 where id = 2;
• update autos set kunde_id = 3 where id = 3;
• update autos set kunde_id = 4 where id = 4;
• update autos set kunde_id = 5 where id = 5;
• update autos set kunde_id = 6 where id = 6;
• update autos set kunde_id = 7 where id = 7;
• update autos set kunde_id = 8 where id = 8;
• update autos set kunde_id = 9 where id = 9;
• update autos set kunde_id = 10 where id = 10;
• update autos set kunde_id = 11 where id = 11;

Zeigt alle Autos mit den zugehörigen Kunden.

• select autos.marke, autos.modell, kunden.name from autos left join kunden on autos.kunde_id = kunden.id;

Erklärung: Autos mit Kunden verknüpfen

In diesem Abschnitt wurden zuerst 11 Kunden in die Tabelle kunden eingefügt. Danach wurde jedem Auto ein Kunde zugeordnet – über die Spalte kunde_id in der Tabelle autos.

Dabei wurde folgende Beziehung hergestellt: Das Auto mit der ID 1 gehört dem Kunden mit der ID 1, das Auto mit der ID 2 dem Kunden mit der ID 2 – und so weiter bis zum Auto mit der ID 11.

Die Zuordnung erfolgt über:

• update autos set kunde_id = X where id = X;

Jede Auto-ID entspricht dabei genau der Kunden-ID. Das funktioniert nur korrekt, wenn die IDs beider Tabellen wie im Beispiel aufeinander abgestimmt sind.

Der abschließende LEFT JOIN zeigt die verknüpften Daten: Er verbindet die Tabelle autos mit der Tabelle kunden, sodass in der Ausgabe zur Marke und dem Modell auch der Name des jeweiligen Kunden erscheint.

• select autos.marke, autos.modell, kunden.name from autos left join kunden on autos.kunde_id = kunden.id;

So lassen sich zusammengehörige Informationen aus mehreren Tabellen gemeinsam abfragen.

Benutzer anlegen

Erstellt einen neuen Benutzer für den Zugriff.

• create user 'autouser'@'localhost' identified by 'Passwort123';

Lesezugriff für autouser

Gibt Lesezugriff auf die Tabelle autos.

• grant select on fahrzeuge.autos to 'autouser'@'localhost';

Indizes für schnellere Abfragen

Erstellt einen Index auf der Spalte "marke".

• create index idx_marke on autos (marke);

Backup und Wiederherstellung

Sichert die Datenbank in eine Datei.

(Auf der Konsole)

• mysqldump -u root -p fahrzeuge > fahrzeuge.sql

In MariaDB die Datenbank löschen.

• mysql -u root -p

(In MariDB)

• drop database fahrzeuge;

Wiederherrstellen

(In MariDB)

• create database fahrzeuge;

(Auf der Konsole)

• mysql -u root -p fahrzeuge < fahrzeuge.sql

Benutzerrechte verwalten

Zeigt die aktuellen Berechtigungen eines Benutzers.

• show grants for 'autouser'@'localhost';

Entzieht dem Benutzer das Leserecht.

• revoke select on fahrzeuge.autos from 'autouser'@'localhost';

Löscht den Benutzer.

• drop user 'autouser'@'localhost';

Transaktionen nutzen

Beginnt eine Transaktion.

• start transaction;

Führt eine Änderung durch.

• update autos set preis = 19999.99 where marke = 'Ford';

Speichert die Änderung dauerhaft.

• commit;

Bricht eine Transaktion ab (Änderung wird nicht übernommen).

• rollback;

Trigger erstellen

Erstellt einen Trigger, der vor dem Einfügen eines Autos das Baujahr überprüft.

• create trigger check_baujahr before insert on autos

for each row begin if new.baujahr < 1900 then set new.baujahr = 1900; end if; end;

Views erstellen

Erstellt eine Ansicht mit allen Autos über 20.000€.

• create view teure_autos as

select * from autos where preis > 20000;

Zeigt den Inhalt des Views an.

• select * from teure_autos;

Gespeicherte Prozeduren

Erstellt eine Prozedur, die alle Autos einer bestimmten Marke anzeigt.

• delimiter //

create procedure get_autos_by_marke(in auto_marke varchar(50)) begin select * from autos where marke = auto_marke; end // delimiter ;

Führt die Prozedur aus.

• call get_autos_by_marke('BMW');

Stellt das Backup wieder her.

• mysql -u root -p fahrzeuge < fahrzeuge.sql;