Belangrijk

Vertalen is een inspanning van de gemeenschap waaraan u deel kunt nemen. Deze pagina is momenteel voor 85.11% vertaald.

15.4. Les: Query’s

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

Het doel voor deze les: Query’s die bruikbare informatie teruggeven leren maken.

Notitie

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. Resultaten ordenen

Laten we een lijst van mensen ophalen, gesorteerd op hun huisnummers:

select name, house_no from people order by house_no;

Resultaat:

     name     | house_no
--------------+----------
 Joe Bloggs   |        3
 Roger Jones  |       33
 Jane Smith   |       55
 Sally Norman |       83
(4 rows)

U kunt de resultaten sorteren op de waarden van meer dan één kolom:

select name, house_no from people order by name, house_no;

Resultaat:

     name     | house_no
--------------+----------
 Jane Smith   |       55
 Joe Bloggs   |        3
 Roger Jones  |       33
 Sally Norman |       83
(4 rows)

15.4.2. Filteren

Vaak zult u niet elk afzonderlijk record in de database willen zien - in het bijzonder als er duizenden records zijn en u alleen geïnteresseerd bent in het bekijken van één of twee.

Hier is een voorbeeld van een numeriek filter wat alleen objecten teruggeeft waarvan het house_no kleiner is dan 50:

select name, house_no from people where house_no < 50;

      name     | house_no
  -------------+----------
   Joe Bloggs  |        3
   Roger Jones |       33
  (2 rows)

U kunt filters combineren (gedefinieerd met behulp van de clausule WHERE) met sorteren (gedefinieerd met behulp van de clausule ORDER BY):

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)

U kunt ook filteren gebaseerd op tekstgegevens:

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. Samenvoegingen (joins)

Wat als u de details van personen en hun straatnaam wilt zien in plaats van de ID? U dient de twee tabellen samen te voegen in één enkele query om dat te kunnen doen. Laten we eens naar een voorbeeld kijken:

select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;

Notitie

Met samenvoegingen (joins) worden twee of meer tabellen of views samengevoegd, in dit geval de tabellen people en streets. Er dient te worden gespecificeerd welke velden overeen moeten komen (vreemde sleutel & primaire sleutel). Indien deze velden niet worden gespecificeerd worden alle velden van de tabellen onderling gecombineerd. Dit resulteert in alle mogelijke combinaties van mensen en straten, maar geeft geen antwoord op de vraag wie in welke straat woont.

Hier is hoe de juiste uitvoer eruit zal zien:

     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)

We zullen samenvoegingen (joins) opnieuw tegenkomen als we later meer complexe query’s maken. Onthoud alleen dat zij een eenvoudige manier bieden om de informatie uit twee of meer tabellen te combineren.

15.4.4. Sub-selecteren

Sub-selecties stellen u in staat objecten te selecteren uit één tabel, gebaseerd op de gegevens uit een andere tabel die is gekoppeld met een relatie vreemde sleutel. In ons geval willen we mensen vinden die in een specifieke straat wonen.

Laten we eerst onze gegevens iets aanpassen:

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;

Laten we even snel kijken naar onze gegevens na deze wijzigingen: we kunnen onze query uit het eerdere gedeelte opnieuw gebruiken:

select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;

Resultaat:

     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;

Resultaat:

     name
--------------
 Roger Jones
 Sally Norman
(2 rows)

Hoewel dit een zeer eenvoudig voorbeeld is en onnodig met onze kleine sets met gegevens, illustreert het hoe bruikbaar en belangrijk sub-selecties kunnen zijn bij het bevragen van grote en complexe sets met gegevens.

15.4.5. Samenvattende query’s

Eén van de krachtige mogelijkheden van een database is zijn mogelijkheid om de gegevens in zijn tabellen samen te vatten. Deze samenvattingen worden aggregate query’s genoemd. Hier is a typisch voorbeeld dat u vertelt hoeveel objecten people er in uw tabel people staan:

select count(*) from people;

Resultaat:

 count
-------
     4
(1 row)

Als we de tellingen willen samenvatten op straatnaam kunnen we dit doen:

select count(name), street_id
from people
group by street_id;

Resultaat:

 count | street_id
-------+-----------
     2 |         1
     1 |         3
     1 |         2
(3 rows)

Notitie

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

Probeer zelf: ★★☆

Samenvatten van de personen op straatnaam en de feitelijke straatnamen laten zien in plaats van de street_id’s.

Antwoord

Hier is het juiste argument voor SQL dat u zou moeten gebruiken:

select count(people.name), streets.name
from people, streets
where people.street_id=streets.id
group by streets.name;

Resultaat:

count |    name
------+-------------
    1 | Low Street
    2 | High street
    1 | Main Road
(3 rows)

U zult zien dat we vooraf gedefinieerde veldnamen met tabelnamen hebben (bijv. people.name en streets.name). Dit moet elke keer worden gedaan wanneer de veldnaam dubbelzinnig is (d.i. niet uniek voor alle tabellen in de database).

15.4.6. Conclusie

U heeft gezien hoe u query’s kunt gebruiken om de gegevens in uw database terug te geven op een manier die u in staat stelt daaruit bruikbare informatie te halen.

15.4.7. Wat volgt?

Vervolgens zult u zien hoe u weergaven kunt maken uit de query’s die u heeft geschreven.