15.4. Lesson: Abfragen
Wenn Sie ein Kommando wie SELECT ... schreiben, wird es in der Regel als Abfrage bezeichnet - Sie befragen die Datenbank nach Informationen.
Ziel dieser Lektion: Zu lernen, wie man Abfragen erstellt, die nützliche Informationen zurückgeben.
Bemerkung
Wenn Sie es noch nicht in der letzten Lektion gemacht haben, fügen Sie die folgenden Personen zur Tabelle people hinzu. Wenn Sie Fehlermeldungen zu Einschränkungen des Fremdschlüssels erhalten, müssen Sie zuerst das Objekt ‚Main Road‘ in die streets Tabelle einfügen
insert into people (name,house_no, street_id, phone_no)
values ('Joe Bloggs',3,2,'072 887 23 45');
insert into people (name,house_no, street_id, phone_no)
values ('Jane Smith',55,3,'072 837 33 35');
insert into people (name,house_no, street_id, phone_no)
values ('Roger Jones',33,1,'072 832 31 38');
insert into people (name,house_no, street_id, phone_no)
values ('Sally Norman',83,1,'072 932 31 32');
15.4.1. Ordnen der Ergebnisse
Lassen Sie uns eine Personenliste geordnet nach ihrer Hausnummer abrufen:
select name, house_no from people order by house_no;
Ergebnis:
name | house_no
--------------+----------
Joe Bloggs | 3
Roger Jones | 33
Jane Smith | 55
Sally Norman | 83
(4 rows)
Sie können die Ergebnisse nach den Werten von mehreren Spalten sortieren lassen:
select name, house_no from people order by name, house_no;
Ergebnis:
name | house_no
--------------+----------
Jane Smith | 55
Joe Bloggs | 3
Roger Jones | 33
Sally Norman | 83
(4 rows)
15.4.2. Filtern
Oftmals will man nicht jeden einzelnen Datensatz der Datenbank sehen, besonders wenn tausende von Einträgen enthalten sind und man nur einen oder zwei sehen möchte.
Es folgt ein Beispiel eines numerischen Filters, der nur Objekte zurück gibt, deren house_no kleiner ist als 50:
select name, house_no from people where house_no < 50;
name | house_no
-------------+----------
Joe Bloggs | 3
Roger Jones | 33
(2 rows)
Man kann Filter (Benutzung der WHERE Klausel) und Sortierung kombinieren (Benutzung der ORDER BY Klausel):
select name, house_no from people where house_no < 50 order by house_no;
name | house_no
-------------+----------
Joe Bloggs | 3
Roger Jones | 33
(2 rows)
Man kann auch auf Basis von Textdaten filtern:
select name, house_no from people where name like '%s%';
name | house_no
-------------+----------
Joe Bloggs | 3
Roger Jones | 33
(2 rows)
Hier wurde die Klausel LIKE verwendet, um alle Namen, die s enthalten zu finden. Man sieht, dass die Abfrage zwischen Groß- und Kleinschreibung unterscheidet, so dass der Eintrag Sally Norman nicht zurückgegeben wurde.
Wenn man eine Zeichenkette ohne Unterscheidung zwischen Groß- und Kleinschriebung suchen möchte, kann man die Klausel ILIKE verwenden:
select name, house_no from people where name ilike '%r%';
name | house_no
--------------+----------
Roger Jones | 33
Sally Norman | 83
(2 rows)
Diese Abfrage gab alle Objekte people zurück, die ein r oder R in ihrem Namen enthalten.
15.4.3. Verbindungen
Und wenn man die Details zu einer Person und ihre Adresse anstatt der ID sehen möchte? Um das zu erreichen, muss man die zwei Tabellen mit einer einzelnen Abfrage verbinden. Lassen Sie uns ein Beispiel ansehen:
select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;
Bemerkung
Bei Verbindungen müssen immer die zwei Tabellen angegeben werden, aus denen die Informationen stammen, in diesem Fall people und streets. Man muss außerdem angeben, welche Schlüssel miteinander verbunden werden (Fremd- und Primärschlüssel). Ohne diese Angabe erhält man eine Liste aller möglichen Kombinationen an Personen und Straßen, ohne zu wissen, welche Person in welcher Straße wohnt!
Die richtige Ausgabe sieht wie folgt aus:
name | house_no | name
--------------+----------+-------------
Joe Bloggs | 3 | Low Street
Roger Jones | 33 | High street
Sally Norman | 83 | High street
Jane Smith | 55 | Main Road
(4 rows)
Wir werden uns Verbindungen später im Zusammenhang mit komplexeren Abfragen noch einmal ansehen. Vorerst müssen wir uns nur merken, dass sie einen einfachen Weg zur Kombination von Informationen aus zwei oder mehr Tabellen darstellen.
15.4.4. Unterauswahl
Unterauswahlen erlauben es, Objekte aus einer Tabelle basierend auf den Daten einer anderen Tabelle auszuwählen, die über eine Fremdschlüsselverbindung angebunden ist. In unserem Fall wollen wir Personen finden, die in einer bestimmten Straße wohnen.
Lassen Sie uns zuerst unsere Daten etwas optimieren:
insert into streets (name) values('QGIS Road');
insert into streets (name) values('OGR Corner');
insert into streets (name) values('Goodle Square');
update people set street_id = 2 where id=2;
update people set street_id = 3 where id=3;
Schauen wir uns die Daten nach den Veränderungen an: wir können unsere Abfrage aus dem vorherigen Kapitel wieder verwenden:
select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;
Ergebnis:
name | house_no | name
--------------+----------+-------------
Roger Jones | 33 | High street
Sally Norman | 83 | High street
Jane Smith | 55 | Main Road
Joe Bloggs | 3 | Low Street
(4 rows)
Nun erstellen wir eine Unterauswahl basierend auf diesen Daten. Wir wollen nur die Personen ausgeben, die in der Straße mit street_id 1 leben:
select people.name
from people, (
select *
from streets
where id=1
) as streets_subset
where people.street_id = streets_subset.id;
Ergebnis:
name
--------------
Roger Jones
Sally Norman
(2 rows)
Auch wenn es ein sehr einfaches Beispiel und unnötig für unseren kleinen Datensatz ist, so zeigt es doch wie nützlich und wichtig Unterauswahlen beim Abfragen großer und komplexer Datensätze sind.
15.4.5. Abfragen zur Aggregierung
Eine der mächtigsten Merkmale einer Datenbank ist die Fähigkeit, die Daten in den Tabellen der Datenbank zusammenzufassen. Diese Zusammenfassungen werden Aggregierungs-Abfragen genannt. Es folgt ein typisches Beispiel solch einer Abfrage, die ermittelt, wieviele Personen-Objekte sich in unserer Personentabelle befinden:
select count(*) from people;
Ergebnis:
count
-------
4
(1 row)
Wir können die Anzahl auch über den Straßennamen zusammenfassen:
select count(name), street_id
from people
group by street_id;
Ergebnis:
count | street_id
-------+-----------
2 | 1
1 | 3
1 | 2
(3 rows)
Bemerkung
Da wir keine ORDER BY Klausel verwendet haben, weicht die Reihenfolge der Ergebnisse unter Umständen von der hier dargestellten ab.
Try Yourself 
Fassen Sie die Personen nach Straßen zusammen und geben Sie die richtigen Straßen anstatt der street_ids aus.
Answer
Here is the correct SQL statement you should use:
select count(people.name), streets.name
from people, streets
where people.street_id=streets.id
group by streets.name;
Result:
count | name
------+-------------
1 | Low Street
2 | High street
1 | Main Road
(3 rows)
You will notice that we have prefixed field names with table names (e.g. people.name and streets.name). This needs to be done whenever the field name is ambiguous (i.e. not unique across all tables in the database).
15.4.6. In Conclusion
Wir haben gesehen, wie man Abfragen zur Ausgabe von Daten nutzt, um sinnvolle Information aus unserer Datenbank zu erhalten.
15.4.7. What’s Next?
Als Nächstes werden wir sehen, wie man Sichten aus den erstellten Abfragen erstellt.