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. Auto mit ID 1 wurde gelöscht, daher ab ID 2 beginnen.

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;

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;

Mariadb Workshop