Eierkochen in heißer Quelle

MySQL Datenbankabfragen


Autoren: D. Supper, A. Grella, M. Retzbach, 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

Operator­typ 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 Bach­mann 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 bei­trag dau­er
1 Web­design 120 20
2 Office 350 35
3 Java Begin­ner 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
  1. Lege zunächst eine neue Datenbank namens "fortbildung" an und liste anschließend alle Datenbanken auf.
  2. Lies die Tabellen "teilnehmer" und "kurs" ein.
Datenbankabfrage - Dokumentiere alle Befehle
  1. Liste alle Einträge der Tabelle "teilnehmer" auf.
  2. Alle Postleitzahlen der Teilnehmer sollen angezeigt werden. Begründe wie man doppelte Datensätze vermeidet.
  3. Erstelle eine Teilnehmerliste die nach den Namen sortiert ist und eine weitere die absteigend nach den Postleitzahlen sortiert ist.
  4. Ermittele in alphabetischer Reihenfolge alle Kurse, deren Kosten über 200 EUR liegen.
  5. Bestimme die Kurse, deren Kosten unter 300 EUR liegen.
  6. Gib alle Teilnehmer aus, deren Namen mit "B" beginnen.
  7. Liste Inhalt und Dauer aller Kurse auf, deren Dauer zwischen 20 und 34 Stunden beträgt.
  8. Gib alle Teilnehmer aus die aus Tettnang oder Lindau sind.
  9. Bestimme den Stundensatz eines jeden Kurses.
  10. Ermittele alle Teilnehmer aus dem PLZ-Bereich "880xx" und deren Namen mit "B" oder "W" beginnen.
  11. Bestimme die Teilnehmeranzahl aus den jeweiligen Orten.
  12. 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.

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


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

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



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

  1. Alle Beiträge mit 300 oder weniger werden angezeigt.
  2. Es werden alle Inhalte außer 300 angezeigt.
  3. Alle Beiträge kleiner als 300 werden angezeigt.
  4. 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.

  1. kein Fehler
  2. ein Fehler
  3. zwei Fehler
  4. 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.

  1. Es werden zwei Spalten angezeigt.
  2. Alle Einträge der Tabelle werden angezeigt.
  3. Der Befehl ist fehlerhaft.
  4. 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.

  1. Es werden alle Orte angezeigt.
  2. Es werden alle Orte ohne Anzahl angezeigt.
  3. Es werden nur Orte mit 2 Usern angezeigt
  4. 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 Bach­mann 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 bei­trag dau­er
1 Web­de­sign 120 20
2 Office 350 35
3 Java Begin­ner 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
  1. Lege, falls noch nicht vorhanden, zunächst eine neue Datenbank namens "fortbildung" an und liste anschließend alle Datenbanken auf.
  2. Lies die Tabellen "teilnehmer" und "kurs" ein, bzw. ergänze fehlende Einträge.
verknüpfte Datenbankabfrage - Dokumentiere alle Befehle
  1. Liste die Beiträge eines jeden Teilnehmers auf.
  2. Liste die Kursdauer eines jeden Teilnehmers auf.
  3. Bestimme die Teilnehmer pro Kurs.
  4. Bestimme die Beiträge pro Ort.
  5. Liste zu jedem Teilnehmer den Kursnamen auf.
  6. 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.

  1. describe table kurs
  2. describe kurs;
  3. show table kurs;
  4. show tables;


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

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



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

  1. select kurs.kname, dozent.dname from kurs join dozent on kurs.idKurs=dozent.idDozent;
  2. select kurs.kname, dozent.dname from kurs inner join dozent on idKurs=idDozent;
  3. select kname, dname from kurs inner join dozent on kurs.idKurs=dozent.idDozent;
  4. select kurs.kname, dozent.dname from kurs inner join dozent on kurs.idKurs=dozent.idDozent;


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

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



Übung 5

Insel

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

  Play   

Wortliste und Satzbausteine



MySQL Datenbank­verwaltungs­system (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.