Wichtig

Übersetzen ist eine Gemeinschaftsleistung Sie können mitmachen. Diese Seite ist aktuell zu 74.47% übersetzt.

15.4. Lesson: Queries

When you write a SELECT ... command it is commonly known as a query - you are interrogating the database for information.

Ziel dieser Lektion: Zu lernen, wie man Abfragen erstellt, die nützliche Informationen zurückgeben.

Bemerkung

If you did not do so in the previous lesson, add the following people objects to your people table. If you receive any errors related to foreign key constraints, you will need to add the ‚Main Road‘ object to your streets table first

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)

Here we used the LIKE clause to find all names with an s in them. You’ll notice that this query is case-sensitive, so the Sally Norman entry has not been returned.

If you want to search for a string of letters regardless of case, you can do a case in-sensitive search using the ILIKE clause:

select name, house_no from people where name ilike '%r%';

       name     | house_no
  --------------+----------
   Roger Jones  |       33
   Sally Norman |       83
  (2 rows)

That query returned every people object with an r or R in their name.

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)

Now let’s show you a sub-selection on this data. We want to show only people who live in street_id number 1:

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

Because we have not used an ORDER BY clause, the order of your results may not match what is shown here.

Try Yourself: ★★☆

Fassen Sie die Personen nach Straßen zusammen und geben Sie die richtigen Straßen anstatt der street_ids aus.

Antwort

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. Zusammenfassung

Wir haben gesehen, wie man Abfragen zur Ausgabe von Daten nutzt, um sinnvolle Information aus unserer Datenbank zu erhalten.

15.4.7. Was kommt als Nächstes?

Als Nächstes werden wir sehen, wie man Sichten aus den erstellten Abfragen erstellt.