Hoe Nested Queries te Gebruiken in SQL

Introductie

Structured Query Language (SQL) wordt gebruikt om gegevens te beheren in een relationeel databasebeheersysteem (RDBMS). Een nuttige functie in SQL is het maken van een query binnen een query, ook wel bekend als een subquery of geneste query. Een geneste query is een SELECT-statement die doorgaans tussen haakjes wordt geplaatst en ingebed is binnen een primaire SELECT, INSERT of DELETE-bewerking.

In deze zelfstudie zult u geneste queries gebruiken met de SELECT, INSERT en DELETE-statements. U zult ook aggregaatfuncties binnen een geneste query gebruiken om de gegevenswaarden te vergelijken met de gesorteerde gegevenswaarden die u hebt gespecificeerd met de WHERE– en LIKE-clausules.

Vereisten

Om deze handleiding te volgen, hebt u een computer nodig die draait op een soort relationeel databasebeheersysteem (RDBMS) dat SQL gebruikt. De instructies en voorbeelden in deze zelfstudie zijn gevalideerd met behulp van de volgende omgeving:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL geïnstalleerd en beveiligd op de server. Volg onze Handleiding voor het installeren van MySQL op Ubuntu 20.04 om dit in te stellen. Deze handleiding gaat ervan uit dat je ook een niet-root MySQL-gebruiker hebt ingesteld, zoals beschreven in Stap 3 van deze handleiding.

Om te oefenen met het gebruik van geneste queries in deze tutorial, heb je een database en tabel nodig met voorbeeldgegevens. Als je er geen hebt om in te voegen, kun je het volgende lezen in de sectie Verbinding maken met MySQL en een voorbeelddatabase instellen om te leren hoe je een database en tabel kunt maken. Deze tutorial zal gedurende het hele proces naar deze voorbeelddatabase en tabel verwijzen.

Verbinding maken met MySQL en een voorbeelddatabase instellen

Als je SQL-database op een externe server draait, maak dan verbinding via SSH naar je server vanaf je lokale machine:

ssh sammy@your_server_ip

Vervolgens open je de MySQL-prompt, waarbij je sammy vervangt door jouw MySQL-gebruikersaccountgegevens:

mysql -u sammy -p

Maak een database genaamd zooDB:

CREATE DATABASE zooDB;

Als de database succesvol is aangemaakt, ontvangt u de volgende uitvoer:

Output
Query OK, 1 row affected (0.01 sec)

Om de database zooDB te selecteren, voert u de volgende USE-verklaring uit:

USE zooDB;

Output
Database changed

Na het selecteren van de database, maakt u een tabel binnen de database aan. Voor het voorbeeld van deze tutorial maken we een tabel aan die informatie opslaat over gasten die de dierentuin bezoeken. Deze tabel zal de volgende zeven kolommen bevatten:

• guest_id: slaat waarden op voor gasten die de dierentuin bezoeken, en gebruikt het gegevenstype int. Dit dient ook als de primaire sleutel van de tabel, wat betekent dat elke waarde in deze kolom zal fungeren als een unieke identificator voor de respectievelijke rij.
• first_name: bevat de voornaam van elke gast met behulp van het gegevenstype varchar met een maximum van 30 tekens.
• last_name: gebruikt het gegevenstype varchar, opnieuw met een maximum van 30 tekens, om de achternaam van elke gast op te slaan.
• guest_type: bevat het gasttype (volwassene of kind) voor elke gast met behulp van het gegevenstype varchar met een maximum van 15 tekens.
• membership_type: vertegenwoordigt het lidmaatschapstype dat elke gast heeft, met behulp van het gegevenstype varchar om maximaal 30 tekens op te slaan.
• membership_cost: slaat de kosten op voor verschillende lidmaatschapstypen. Deze kolom gebruikt het gegevenstype decimal met een precisie van vijf en een schaal van twee, wat betekent dat waarden in deze kolom vijf cijfers kunnen hebben, en twee cijfers rechts van het decimaalteken.
• total_visits: gebruikt het gegevenstype int om het totale aantal bezoeken van elke gast vast te leggen.

Maak een tabel met de naam guests aan die elk van deze kolommen bevat door het volgende CREATE TABLE-commando uit te voeren:

CREATE TABLE guests (
guest_id int, 
first_name varchar(30),
last_name varchar(30),
guest_type varchar(15),
membership_type varchar(30),
membership_cost decimal(5,2), 
total_visits int,
PRIMARY KEY (guest_id)
); 

Vervolgens voeg je wat voorbeeldgegevens toe aan de lege tabel:

INSERT INTO guests
(guest_id, first_name, last_name, guest_type, membership_type, membership_cost, total_visits)
VALUES
(1, 'Judy', 'Hopps', 'Adult', 'Resident Premium Pass', 110.0, 168),
(2, 'Nick', 'Wilde', 'Adult', 'Day Pass', 62.0, 1),
(3, 'Duke', 'Weaselton', 'Adult', 'Resident Pass', 85.0, 4),
(4, 'Tommy', 'Yax', 'Child', 'Youth Pass', 67.0, 30),
(5, 'Lizzie', 'Yax', 'Adult', 'Guardian Pass', 209.0, 30),
(6, 'Jenny', 'Bellwether', 'Adult', 'Resident Premium Pass', 110.0, 20),
(7, 'Idris', 'Bogo', 'Child', 'Youth Pass', 67.0, 79),
(8, 'Gideon', 'Grey', 'Child', 'Youth Pass', 67.0, 100),
(9, 'Nangi', 'Reddy', 'Adult', 'Guardian Champion', 400.0, 241),
(10, 'Octavia', 'Otterton', 'Adult', 'Resident Pass', 85.0, 11),
(11, 'Calvin', 'Roo', 'Adult', 'Resident Premium Pass', 110.0, 173), 
(12, 'Maurice', 'Big', 'Adult', 'Guardian Champion', 400.0, 2), 
(13, 'J.K.', 'Lionheart', 'Child', 'Day Pass', 52.0, 1), 
(14, 'Priscilla', 'Bell', 'Child', 'Day Pass', 104.0, 2),
(15, 'Tommy', 'Finnick', 'Adult', 'Day Pass', 62.0, 1);

Output
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

Zodra je de gegevens hebt ingevoegd, ben je klaar om geneste queries in SQL te gaan gebruiken.

Het gebruik van Geneste Queries met SELECT

In SQL is een query een bewerking die gegevens ophaalt uit een tabel in een database en altijd een SELECT-statement bevat. Een geneste query is een volledige query die is ingebed in een andere bewerking. Een geneste query kan alle elementen bevatten die worden gebruikt in een gewone query, en elke geldige query kan worden ingebed in een andere bewerking om een geneste query te worden. Bijvoorbeeld, een geneste query kan worden ingebed in INSERT– en DELETE-bewerkingen. Afhankelijk van de bewerking moet een geneste query worden ingebed door de verklaring tussen het juiste aantal haakjes te plaatsen om een bepaalde volgorde van bewerkingen te volgen. Een geneste query is ook handig in situaties waarin je meerdere opdrachten wilt uitvoeren in één query statement, in plaats van meerdere te schrijven om het gewenste resultaat of de gewenste resultaten terug te krijgen.

Om geneste queries beter te begrijpen, laten we illustreren hoe ze nuttig kunnen zijn aan de hand van de voorbeeldgegevens uit de vorige stap. Stel bijvoorbeeld dat je alle gasten in de gasten-tabel wilt vinden die het dierenpark vaker hebben bezocht dan het gemiddelde aantal. Je zou aannemen dat je deze informatie kunt vinden met een query zoals de volgende:

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > AVG(total_visits);

Maar een query met deze syntax zal een foutmelding opleveren:

Output
ERROR 1111 (HY000): Invalid use of group function

De reden voor deze fout is dat aggregaatfuncties zoals AVG() niet werken tenzij ze worden uitgevoerd binnen een SELECT-clausule.

Een optie om deze informatie op te halen zou zijn om eerst een query uit te voeren om het gemiddelde aantal gastenbezoeken te vinden, en vervolgens een andere query uit te voeren om resultaten te vinden op basis van die waarde, zoals in de volgende twee voorbeelden:

SELECT AVG(total_visits) FROM guests;

Output
+-----------------+
| avg(total_visits) |
+-----------------+
| 57.5333 |
+-----------------+
1 row in set (0.00 sec)

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > 57.5333;

Output
+----------+---------+------------+
| first_name | last_name | total_visits |
+----------+---------+------------+
| Judy | Hopps | 168 |
| Idris | Bogo | 79 |
| Gideon | Grey | 100 |
| Nangi | Reddy | 241 |
| Calvin | Roo | 173 |
+----------+---------+------------+
5 rows in set (0.00 sec)

Je kunt echter hetzelfde resultaatset verkrijgen met een enkele query door de eerste query (SELECT AVG(total_visits) FROM gasten;) binnen de tweede te nesten. Houd er rekening mee dat bij geneste queries het gebruik van de juiste hoeveelheid haakjes nodig is om de gewenste bewerking uit te voeren. Dit komt doordat de geneste query de eerste bewerking is die wordt uitgevoerd:

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > 
(SELECT AVG(total_visits) FROM guests);

Output
+------------+-----------+--------------+
| first_name | last_name | total_visits |
+------------+-----------+--------------+
| Judy       | Hopps     |          168 |
| Idris      | Bogo      |           79 |
| Gideon     | Grey      |          100 |
| Nangi      | Reddy     |          241 |
| Calvin     | Roo       |          173 |
+------------+-----------+--------------+
5 rows in set (0.00 sec)

Volgens deze output bezochten vijf gasten de dierentuin vaker dan het gemiddelde. Deze informatie kan nuttige inzichten bieden om creatieve manieren te bedenken om ervoor te zorgen dat huidige leden de dierentuin vaak blijven bezoeken en jaarlijks hun lidmaatschap verlengen. Bovendien toont dit voorbeeld de waarde aan van het gebruik van een geneste query in één volledige verklaring voor de gewenste resultaten, in plaats van twee afzonderlijke queries uit te voeren.

Het Gebruik van Geneste Queries met INSERT

Met een geneste query bent u niet beperkt tot het alleen insluiten ervan binnen andere SELECT-verklaringen. In feite kunt u geneste queries ook gebruiken om gegevens in te voegen in een bestaande tabel door uw geneste query in te sluiten binnen een INSERT-bewerking.

Om dit te illustreren, laten we zeggen dat een aangesloten dierentuin wat informatie vraagt over uw gasten omdat ze geïnteresseerd zijn in het aanbieden van een korting van 15% aan gasten die een “Inwoner” lidmaatschap kopen op hun locatie. Gebruik hiervoor CREATE TABLE om een nieuwe tabel genaamd upgrade_guests te maken die zes kolommen bevat. Let goed op de gegevenstypen, zoals int en varchar, en de maximale tekens die ze kunnen bevatten. Als ze niet overeenkomen met de originele gegevenstypen van de guests tabel die u hebt gemaakt in de het instellen van een voorbeelddatabase sectie, zult u een foutmelding ontvangen wanneer u probeert gegevens in te voegen vanuit de guests tabel met behulp van een geneste query en zullen de gegevens niet correct worden overgebracht. Maak uw tabel met de volgende informatie:

CREATE TABLE upgrade_guests (
guest_id int, 
first_name varchar(30),
last_name varchar(30),
membership_type varchar(30),
membership_cost decimal(5,2),
total_visits int, 
PRIMARY KEY (guest_id)
); 

Voor consistentie en nauwkeurigheid hebben we de meeste informatie over gegevenstypen in deze tabel hetzelfde gehouden als in de guests tabel. We hebben ook eventuele extra kolommen die we niet in de nieuwe tabel willen verwijderd. Met deze lege tabel klaar voor gebruik is de volgende stap om de gewenste gegevenswaarden in de tabel in te voegen.

Bij deze operatie schrijft u INSERT INTO en de nieuwe upgrade_guests tabel, zodat er een duidelijke richting is voor waar de gegevens worden ingevoegd. Schrijf vervolgens uw geneste query met de SELECT instructie om de relevante gegevenswaarden op te halen en FROM om ervoor te zorgen dat ze afkomstig zijn van de guests tabel.

Daarnaast, pas de 15% korting toe op een van de “Resident” leden door de vermenigvuldiging wiskundige bewerking, *, toe te voegen om te vermenigvuldigen met 0.85, binnen de geneste queryverklaring (membership_cost * 0.85). Gebruik vervolgens de WHERE-clausule om te sorteren op waarden in de membership_type-kolom. U kunt het zelfs nog verder beperken tot alleen resultaten voor “Resident” lidmaatschappen met behulp van de LIKE-clausule en plaats het percentage % symbool voor en na het woord “Resident” tussen enkele aanhalingstekens om elk lidmaatschap te selecteren dat hetzelfde patroon volgt, of in dit geval dezelfde bewoording. Uw query wordt als volgt geschreven:

INSERT INTO upgrade_guests
SELECT guest_id, first_name, last_name, membership_type, 
(membership_cost * 0.85), total_visits
FROM guests
WHERE membership_type LIKE '%resident%';

Output
Query OK, 5 rows affected, 5 warnings (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 5

De output geeft aan dat er vijf records zijn toegevoegd aan de nieuwe upgrade_guests tabel. Om te bevestigen dat de door u gevraagde gegevens succesvol zijn overgebracht van de guests tabel naar de lege upgrade_guests tabel die u hebt gemaakt, en met de door u gespecificeerde voorwaarden met de geneste query en de WHERE-clausule, voert u het volgende uit:

SELECT * FROM upgrade_guests;

Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        1 | Judy       | Hopps      | Resident Premium Pass |           93.50 |          168 |
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|       11 | Calvin     | Roo        | Resident Premium Pass |           93.50 |          173 |
+----------+------------+------------+-----------------------+-----------------+--------------+
5 rows in set (0.01 sec)

Volgens deze output van uw nieuwe upgrade_guests tabel is de “Resident” gerelateerde gastlidmaatschapsinformatie uit de guest tabel correct ingevoegd. Daarnaast is de nieuwe membership_cost opnieuw berekend met de toegepaste 15% korting. Als gevolg hiervan heeft deze operatie geholpen om het juiste publiek op te splitsen en te targeten en zijn de kortingsprijzen direct beschikbaar om te delen met deze potentiële nieuwe leden.

Het Gebruik van Geneste Query’s met DELETE

Om te oefenen met het gebruik van een geneste query met een DELETE-statement, laten we zeggen dat je alle gasten wilt verwijderen die frequente bezoekers zijn omdat je je alleen wilt richten op het promoten van de korting op het geüpgradede premium abonnement voor leden die momenteel niet vaak naar de dierentuin komen.

Begin deze operatie met het DELETE FROM-statement zodat duidelijk is waar de gegevens worden verwijderd, in dit geval de upgrade_guests-tabel. Gebruik vervolgens de WHERE-clausule om eventuele total_visits te sorteren die meer zijn dan het bedrag dat is gespecificeerd in de geneste query. Gebruik in je ingebedde geneste query SELECT om het gemiddelde, AVG, van total_visits te vinden, zodat de voorafgaande WHERE-clausule de juiste gegevenswaarden heeft om tegen te vergelijken. Gebruik ten slotte FROM om die informatie uit de guests-tabel op te halen. De volledige queryverklaring zal er als volgt uitzien:

DELETE FROM upgrade_guests
WHERE total_visits >
(SELECT AVG(total_visits) FROM guests);

Output
Query OK, 2 rows affected (0.00 sec)

Bevestig dat die records succesvol zijn verwijderd uit de upgrade_guests-tabel en gebruik ORDER BY om de resultaten te ordenen op total_visits in numerieke en oplopende volgorde:

SELECT * FROM upgrade_guests ORDER BY total_visits;

Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
+----------+------------+------------+-----------------------+-----------------+--------------+
3 rows in set (0.00 sec)

Zoals deze output aangeeft, hebben de DELETE-verklaring en de geneste query goed gefunctioneerd bij het verwijderen van de gespecificeerde gegevenswaarden. Deze tabel bevat nu de informatie voor de drie gasten met minder dan het gemiddelde aantal bezoeken, wat een uitstekend startpunt is voor de vertegenwoordiger van het dierenpark om contact met hen op te nemen over een upgrade naar een premium pas tegen een gereduceerde prijs en hopelijk hen aan te moedigen om vaker naar het dierenpark te gaan.

Conclusie

Geneste queries zijn nuttig omdat ze u in staat stellen zeer gedetailleerde resultaten te verkrijgen die u anders alleen zou kunnen verkrijgen door aparte queries uit te voeren. Bovendien bieden het gebruik van INSERT– en DELETE-verklaringen met geneste queries u een andere manier om gegevens in één stap in te voegen of te verwijderen. Als u meer wilt weten over hoe u uw gegevens kunt organiseren, bekijk dan onze serie over Hoe SQL te gebruiken.