MySQL Datenbankabfragen

Autoren: D. Supper, A. Grella, M. Retzbach, G. Kompa, Elektronikschule Tettnang

Für die Abfrage von Datenbanken steht eine umfangreiche Befehlsstruktur zur Verfügung. Dabei werden alle Abfragen mit dem select-Befehl durchgeführt.

In diesem Artikel wird der Aufbau des select-Befehls gezeigt und die verschiedenen Abfragemöglichkeiten, wie Filterung, Berechnungen, die Abfrage aus verschiedenen Tabellen u.v.m. erklärt.

Datenabfrage data query

Mit Hilfe des select-Befehls werden Datensätze aus einer Datenbank abgerufen.
Als Grundlage zum Testen der Befehlsstruktur dient die hier aufgeführte Tabelle, mit Kunden aus Tettnang, Friedrichshafen und Lindau.

Befehl	Bedeutung
`select <colname>`	Auswahl einer Spalte
`from <tbname>`	Auswahl der Tabellen
`where <condition>`	Auswahl mit Bedingung begrenzen
`group by <colname>`	Zusammenfassen bestimmter Datensätze
`having <condition>`	Auswahl der Datensätze mit Bedingung auf Gruppenebene begrenzen
`order by <colname> [asc\|desc];`	Sortieren in auf- oder absteigender Reihenfolge

Einfache select-Abfragen beinhalten die Ausgabe aller Daten mit select * ... oder sortierte Ausgaben mit order by ... asc. Des weiteren lassen sich mit where Datensätze filtern und auch Ausgaben ohne Berücksichtigung doppelter Einträge ist möglich.

Erstellen der Tabelle "kunde" und Ausgabe

knr	name	plz	umsatz
1	Brecht	88069	60
2	Müller	88069	70
3	Bachmann	88131	55
4	Kraus	88046	32

drop table if exists kunde;
create table kunde (knr int not null auto_increment primary key, name varchar(30), plz varchar(5), umsatz int);
insert into kunde (name,plz,umsatz) values("Brecht","88069",60);
insert into kunde (name,plz,umsatz) values("Müller","88069",70);
insert into kunde (name,plz,umsatz) values("Bachmann","88131",55);
insert into kunde (name,plz,umsatz) values("Kraus","88046",32);
select * from kunde;

Kunden sortiert ausgeben

knr	name	plz	umsatz
3	Bachmann	88131	55
1	Brecht	88069	60
4	Kraus	88046	32
2	Müller	88069	70

> select * from kunde order by name asc;

Kunde "Kraus" ausgeben

knr	name	plz	umsatz
4	Kraus	88046	32

> select * from kunde where name="Kraus";

PLZ ohne Doppelte ausgeben

plz
88131
88069
88046

> select distinct plz from kunde;

Datenabfrage - mit Operatoren

Operatoren eignen sich zum Filtern von Datensätzen. Man unterscheidet: arithmetische, relationale, logische und sonstige Operatoren

Operatortyp	Beispiele
arithmetisch (berechnen)	`+ - * /`
relational (vergleichen)	`< <= > >= != =`
logisch (verknüpfen)	`and/&& or/\|\| not/!`
sonstige	`is null is not null like between in`

Man beachte das der like-Operator eine Suche auf auf Basis von Textmustern ermöglicht.

Des weiteren können neu berechnete Spalten ausgegeben werden. Der Spaltenname wird mit dem Schlüsselwort as angegeben.

Kunden deren Umsatz über 50 EUR liegt

knr	name	umsatz
3	Bachmann	55
1	Brecht	60
2	Müller	70

> select knr, name, umsatz from kunde where umsatz > 50 order by umsatz asc;

Bruttoumsatz der Kunden

knr	name	brutto
1	Brecht	71.40
2	Müller	83.30
3	Bachmann	65.45
4	Kraus	38.08

> select knr, name, umsatz*1.19 as brutto from kunde;

Wildcards und Funktionen verwenden

Mit Wildcards und dem like-Operator kann man nach Textmustern filtern. Man beachte, dass durch entsprechende Klammersetzung Prioritäten bei der Suche gesetzt werden können.

Mit Hilfe von Funktionen können Berechnungen durchgeführt werden:

Funktion	Bedeutung
`count()`	Anzahl der Datensätze
`avg()`	Durchschnitt berechnen
`max()`	Maximalwert bestimmen
`min()`	Minimalwert bestimmen
`sum()`	Summe berechnen

Kunden mit 'B' und 'K' und PLZ

knr	name	plz	umsatz
1	Brecht	88069	60
4	Kraus	88046	32

> select * from kunde where (name like 'B%' or name like 'K%') and plz like '880%';

Anz. Datensätze, Max und Average

count(*)	max(umsatz)	avg(umsatz)
4	70	54.25

> select count(*), max(umsatz), avg(umsatz) from kunde;

Gruppierungen anlegen, verwalten und anzeigen

Möchte man bestimmte Datensätze zusammenfassen, kann man diese mit group by gruppieren. In der Regel werden auf diese Gruppen dann Funktionen angewandt.

Mit Hilfe von having anstelle von where kann man diese dann filtern. So lassen sich bspw. alle PLZ (die Gruppe) auflisten aus denen mindestens zwei Kunden kommen.

Anz. Kunden pro PLZ

plz	anzahl
88046	1
88069	2
88131	1

> select plz, count(*) as anzahl from kunde group by plz;

PLZ mit mind. 2 Kunden

PLZ	Anzahl
88069	2

> select plz, count(*) as Anzahl from kunde group by plz having Anzahl >= 1;

Entspann dich erstmal ...

Der eingeschlossene Nagel

Wie kommt der Nagel dort hinein? Ein Nagel im Holz eingeschlossen. Wie geht das?

Übung 1 Abfrage Fortbildung

Tabelle Teilnehmer

idtnr	name	plz	fkkurs
1	Brecht	88069	2
2	Müller	88069	1
3	Bachmann	88131	3
4	Kraus	88046	4
5	Stroh	88046	4
6	Wolters	88046	2
7	Blaich	88046	4

drop table if exists teilnehmer;
create table teilnehmer (idtnr int not null auto_increment primary key, name varchar(30), plz varchar(5), fkkurs int);
insert into teilnehmer (name,plz,fkkurs) values("Brecht","88069",2);
insert into teilnehmer (name,plz,fkkurs) values("Müller","88069",1);
insert into teilnehmer (name,plz,fkkurs) values("Bachmann","88131",3);
insert into teilnehmer (name,plz,fkkurs) values("Kraus","88046",4);
insert into teilnehmer (name,plz,fkkurs) values("Stroh","88046",4);
insert into teilnehmer (name,plz,fkkurs) values("Wolters","88046",2);
insert into teilnehmer (name,plz,fkkurs) values("Blaich","88046",4);
select * from teilnehmer;

Tabelle Kurs

idkurs	inhalt	beitrag	dauer
1	Webdesign	120	20
2	Office	350	35
3	Java Beginner	230	18
4	Java Expert	470	32

drop table if exists kurs;
create table kurs (idkurs int not null auto_increment primary key, inhalt varchar(30), beitrag int, dauer int);
insert into kurs (inhalt,beitrag,dauer) values("Webdesign",120,20);
insert into kurs (inhalt,beitrag,dauer) values("Office",350,35);
insert into kurs (inhalt,beitrag,dauer) values("Java Beginner",230,18);
insert into kurs (inhalt,beitrag,dauer) values("Java Expert",470,32);
select * from kurs;

Datenbank und Tabelle anlegen

Lege zunächst eine neue Datenbank namens "fortbildung" an und liste anschließend alle Datenbanken auf.
Lies die Tabellen "teilnehmer" und "kurs" ein.

Datenbankabfrage - Dokumentiere alle Befehle

Liste alle Einträge der Tabelle "teilnehmer" auf.
Alle Postleitzahlen der Teilnehmer sollen angezeigt werden. Begründe wie man doppelte Datensätze vermeidet.
Erstelle eine Teilnehmerliste die nach den Namen sortiert ist und eine weitere die absteigend nach den Postleitzahlen sortiert ist.
Ermittele in alphabetischer Reihenfolge alle Kurse, deren Kosten über 200 EUR liegen.
Bestimme die Kurse, deren Kosten unter 300 EUR liegen.
Gib alle Teilnehmer aus, deren Namen mit "B" beginnen.
Liste Inhalt und Dauer aller Kurse auf, deren Dauer zwischen 20 und 34 Stunden beträgt.
Gib alle Teilnehmer aus die aus Tettnang oder Lindau sind.
Bestimme den Stundensatz eines jeden Kurses.
Ermittele alle Teilnehmer aus dem PLZ-Bereich "880xx" und deren Namen mit "B" oder "W" beginnen.
Bestimme die Teilnehmeranzahl aus den jeweiligen Orten.
Bestimme alle Orte mit mindestens zwei Teilnehmern.

Übung 2 Welche Aussage ist wahr?

Wähle die korrekten Aussagen aus.

Eine Mitarbeiterin gibt folgenden Befehl ein:
> select distinct plz from user;
Nenne die Auswirkung. Wähle eine Antwort.

Alle PLZ werden ausgegeben.
Es werden nur die doppelten PLZ ausgegeben.
Alle PLZ werden einfach ohne doppelte Einträge ausgegeben.
Der Befehl ist fehlerhaft.

Ein Mitarbeiter gibt folgenden Befehl ein:
> select * from teilnehmer order by plz desc;
Nenne die Auswirkung. Wähle zwei Antworten.

Es werden nur die plz ausgegeben.
Es wird der gesamte Tabelleninhalt angezeigt.
Die Daten werden absteigend sortiert.
Die Daten werden aufsteigend sortiert.

Eine Mitarbeiterin gibt folgenden Befehl ein:
> select * from kurs where beitrag < 300;
Nenne die Auswirkung. Wähle eine Antwort.

Alle Beiträge mit 300 oder weniger werden angezeigt.
Es werden alle Inhalte außer 300 angezeigt.
Alle Beiträge kleiner als 300 werden angezeigt.
Alle Beiträge größer als 300 werden angezeigt.

Ein Mitarbeiter gibt folgenden Befehl ein:
> select * from teilnehmer where name like "B%";
Finde die Fehleranzahl. Wähle eine Antwort.

kein Fehler
ein Fehler
zwei Fehler
mehr als zwei Fehler

Eine Mitarbeiterin gibt folgenden Befehl ein:
> select plz, count(*) from user group by plz;
Benenne die Auswirkung. Wähle zwei Antworten.

Es werden zwei Spalten angezeigt.
Alle Einträge der Tabelle werden angezeigt.
Der Befehl ist fehlerhaft.
Die Einträge werden nach der PLZ gruppiert.

Ein Nutzer gibt den Befehl > select plz, count(*) as anzahl from user group by plz having anzahl > 1; ein.

Wähle die passende Aussage zum Befehl. Wähle eine Antwort.

Es werden alle Orte angezeigt.
Es werden alle Orte ohne Anzahl angezeigt.
Es werden nur Orte mit 2 Usern angezeigt
Es werden nur Orte mit mindestens 2 Usern angezeigt.

Datumsabfragen Datumsfunktionen

Mit Hilfe von Datumsfunktionen können Berechnungen zu Terminen durchgeführt werden:

Funktion	Bedeutung
`now()`	Aktueller Zeitstempel
`year(<Datum>)`	Jahr
`month(<Datum>)`	Monat
`dayofyear(..)`, `dayofmonth(..)`, `dayofweek(..)`	Tagnummern von Jahr, Monat, Woche
`datediff(d1,d2)`, `adddate(d,n)`, `date_add(..)`	Tagesdifferenz von 2 Terminen, Zeit zu einem Termin addieren
`date_format(<Datum>,'%W')`	'%W' Wochentag, '%M' Monat

Datumsfunktionen

Datum	Jahr	Monat	Jahrestagnr
2021-05-17 10:02:28	2021	5	137

> select now() as Datum, year(now()) as Jahr, month(now()) as Monat, dayofyear(now()) as Jahrestagnr;

Datumsfunktionen

Termin	Tag
2021-07-17 10:02:30	Montag

> select date_add(now(), interval 2 month) as Termin, date_format(now(),'%W') as Tag;

Abfragen mit verknüpften Tabellen inner & outer join

Die wohl wichtigste Funktion von SQL ist die Verknüpfung von zwei oder mehr Tabellen mit join. Dabei wird eine temporäre Tabelle erstellt. Ein inner join verarbeitet alle Einträge die nicht leer sind, ein outer join auch leere Null Datensätze.

Für ein inner join werden Tabellen zunächst über Primär- und Fremdschlüssel eindeutig verknüpft. Während in der Kunden-Tabelle der Fremdschlüssel "plz" ist, wird dieser in der Tabelle "ort" als Primärschlüssel primary key definiert. Dadurch werden doppelte Einträge vermieden und es sind keine leeren Einträge vorhanden. Ein inner join wertet nur Datensätze, die in beiden Tabellen vorkommen.

Möchte man auch leere Datensätze listen verwendet man outer join. Dabei wird über left outer join oder right outer join der Bezug festgelegt.
Ein left outer join, kurz left join, wertet alle Datensätze der 1. (linken) Tabelle und wenn vorhanden zugehörige Daten aus der 2. (rechten) Tabelle aus, wenn nicht vorhanden: NULL-Werte.
Ein right outer join, kurz right join, wertet alle Datensätze der 2. (rechten) Tabelle und wenn vorhanden zugehörige Daten aus der 1. (linken) Tabelle aus, wenn nicht vorhanden: NULL-Werte.

Tabelle "Ort" erstellen und anzeigen

plz	oname
88046	Friedrichshafen
88069	Tettnang
88131	Lindau

drop table if exists ort;
create table ort (plz varchar(5) not null primary key, oname varchar(30));
insert into ort (plz, oname) values('88046','Friedrichshafen');
insert into ort (plz, oname) values('88069','Tettnang');
insert into ort (plz, oname) values('88131','Lindau');
select * from ort;

Kundename und Ort anzeigen

Name	Ort
Brecht	Tettnang
Müller	Tettnang
Bachmann	Lindau
Krauss	Friedrichshafen

> select kunde.name as Name, ort.oname as Ort from ort inner join kunde on ort.plz = kunde.plz;

Lösung mit Cross-Join (langsamer):
> select kunde.name as Name, ort.oname as Ort from ort, kunde where ort.plz = kunde.plz;

Lösung mit outer join, falls unvollständige Datensätze auch angezeigt werden sollen:
> select kunde.name as Name, ort.oname as Ort from ort left outer join kunde on ort.plz = kunde.plz;

Übung 3 Verknüpfte Abfrage Fortbildung

Tabelle Teilnehmer

idtnr	name	plz	fkkurs
1	Brecht	88069	2
2	Müller	88069	1
3	Bachmann	88131	3
4	Kraus	88046	4
5	Stroh	88046	4
6	Wolters	88046	2
7	Blaich	88046	4
8	Tesla	88069	NULL

drop table if exists teilnehmer;
create table teilnehmer (idtnr int not null auto_increment primary key, name varchar(30), plz varchar(5), fkkurs int);
insert into teilnehmer (name,plz,fkkurs) values("Brecht","88069",2);
insert into teilnehmer (name,plz,fkkurs) values("Müller","88069",1);
insert into teilnehmer (name,plz,fkkurs) values("Bachmann","88131",3);
insert into teilnehmer (name,plz,fkkurs) values("Kraus","88046",4);
insert into teilnehmer (name,plz,fkkurs) values("Stroh","88046",4);
insert into teilnehmer (name,plz,fkkurs) values("Wolters","88046",2);
insert into teilnehmer (name,plz,fkkurs) values("Blaich","88046",4);
insert into teilnehmer (name,plz) values("Tesla","88069");
select * from teilnehmer;

Tabelle Kurs

idkurs	inhalt	beitrag	dauer
1	Webdesign	120	20
2	Office	350	35
3	Java Beginner	230	18
4	Java Expert	470	32

drop table if exists kurs;
create table kurs (idkurs int not null auto_increment primary key, inhalt varchar(30), beitrag int, dauer int);
insert into kurs (inhalt,beitrag,dauer) values("Webdesign",120,20);
insert into kurs (inhalt,beitrag,dauer) values("Office",350,35);
insert into kurs (inhalt,beitrag,dauer) values("Java Beginner",230,18);
insert into kurs (inhalt,beitrag,dauer) values("Java Expert",470,32);
select * from kurs;

Datenbank und Tabelle anlegen

Lege, falls noch nicht vorhanden, zunächst eine neue Datenbank namens "fortbildung" an und liste anschließend alle Datenbanken auf.
Lies die Tabellen "teilnehmer" und "kurs" ein, bzw. ergänze fehlende Einträge.

verknüpfte Datenbankabfrage - Dokumentiere alle Befehle

Liste die Beiträge eines jeden Teilnehmers auf.
Liste die Kursdauer eines jeden Teilnehmers auf.
Bestimme die Teilnehmer pro Kurs.
Bestimme die Beiträge pro Ort.
Liste zu jedem Teilnehmer den Kursnamen auf.
Liste zu jedem Teilnehmer den Kursnamen auf. Teilnehmer die keinen Kurs gebucht haben sollen auch ausgegeben werden.

Übung 4 Welche Aussage ist wahr?

Wähle die korrekten Aussagen aus.

Tabelle kurs:

idKurs	kname	fkDozent
primary key		foreign key

Tabelle dozent:

idDozent	dname	stundensatz
primary key

Nenne den korrekten Befehl zur Ausgabe der ausführlichen Tabellenstruktur.
Wähle eine Antwort.

describe table kurs
describe kurs;
show table kurs;
show tables;

Nenne einen Vorteil eine Spalte als Primärschlüssel zu definieren.
Wähle beliebig viele Antworten.

Jeder Datensatz hat eine eindeutige ID.
Es gibt genau einen leeren Datensatz.
Es gibt keine leere ID.
Es gibt keinen Fremdschlüssel.

Liste Kursname und zugehörigen Dozentennamen auf.
Wähle eine Antwort.

select kurs.kname, dozent.dname from kurs join dozent on kurs.fkDozent=dozent.idDozent;
select kurs.kname, dozent.dname from kurs inner join dozent on fkDozenz=idDozent;
select kname, dname from kurs inner join dozent on kurs.fkDozent=dozent.idDozent;
select kurs.kname, dozent.dname from kurs inner join dozent on kurs.fkDozent=dozent.idDozent;

Liste alle Dozenten mit Kursen auf, auch falls diese keine haben.
Wähle eine Antwort.

select kname, dname from kurs inner join dozent on idKurs=idDozent;
select dozent.dname, kurs.kname from dozent inner join kurs on kurs.idKurs=dozent.idDozent;
select dozent.dname, kurs.kname from dozent left join kurs on kurs.idKurs=dozent.idDozent;
select kurs dozent from kurs left join dozent on idKurs=idDozent;

Übung 5

Mache aus dem Lernen ein Erlebnis. Entkomme von der SQL-Insel.

Play

Wortliste und Satzbausteine

MySQL	Datenbankverwaltungssystem (engl. database management system)
SQL	Datenbanksprache (engl. structured query language)
Operator, -en	Operatoren sind zum Filtern von Datensätzen. Man unterscheidet zwischen arithmetischen (berechnen), relationalen (vergleichen) und logischen (verknüpfen) Operatoren.
die Wildcard, -s	ein Platzhalter (%) für beliebige Zeichen zum Filtern von Textmustern
die Funktion, -en	zum Durchführen von Berechnungen
die Gruppierung, -en	zum Zusammenfassen von Datensätzen

`inner join`	nur Datensätze, die in beiden Tabellen vorkommen
`left outer join`	alle Datensätze der 1. (linken) Tabelle und wenn vorhanden zugehörige Daten aus der 2. (rechten) Tabelle; wenn nicht vorhanden: NULL-Werte.
`right outer join`	alle Datensätze der 2. (rechten) Tabelle und wenn vorhanden zugehörige Daten aus der 1. (linken) Tabelle; wenn nicht vorhanden: NULL-Werte.