Share
Vandaag vervolgen we onze reis naar de wereld van SQL- en relationele databasesystemen. In dit deel van de reeks zullen we leren hoe te werken met meerdere tabellen die relaties met elkaar hebben. Eerst gaan we enkele kernbegrippen bespreken en gaan we vervolgens aan de slag met JOIN-query's in SQL.
U kunt ook SQL-databases in actie zien door de SQL-scripts, apps en add-ons op Envato Market te bekijken.
Bij het maken van een database dicteert het gezond verstand dat we afzonderlijke tabellen gebruiken voor verschillende soorten entiteiten. Enkele voorbeelden zijn: klanten, bestellingen, artikelen, berichten, enz. Maar we moeten ook relaties hebben tussen deze tabellen. Klanten maken bijvoorbeeld bestellingen en bestellingen bevatten items. Deze relaties moeten in de database worden weergegeven. Ook moeten we bij het ophalen van gegevens met SQL bepaalde typen JOIN-query's gebruiken om te krijgen wat we nodig hebben.
Er zijn verschillende typen databaserelaties. Vandaag gaan we het volgende behandelen:
Bij het selecteren van gegevens uit meerdere tabellen met relaties, gebruiken we de JOIN-query. Er zijn verschillende soorten JOIN's en we gaan het volgende leren:
We zullen ook meer te weten komen over de ON-clausule en de USING-clausule.
Stel dat u een tabel hebt voor klanten:
We kunnen de adresgegevens van de klant op een aparte tabel zetten:
Nu hebben we een relatie tussen de tabel Klanten en de tabel Adressen. Als elk adres van slechts één klant kan zijn, is deze relatie "Eén op één". Houd er rekening mee dat dit soort relatie niet erg gebruikelijk is. Onze eerste tabel met het adres en de klant had in de meeste gevallen prima kunnen werken.
Merk op dat er nu een veld met de naam "adres_id" in de tabel Klanten staat, dat verwijst naar het overeenkomende record in de tabel Adres. Dit wordt een "Foreign Key" genoemd en het wordt gebruikt voor alle soorten databaserelaties. We zullen dit onderwerp later in het artikel behandelen.
We kunnen de relatie tussen de klant en adresgegevens als volgt visualiseren:
Merk op dat het bestaan van een relatie optioneel kan zijn, zoals het hebben van een klantrecord dat geen gerelateerd adresrecord heeft.
Dit is het meest gebruikte type relatie. Overweeg een e-commerce website, met het volgende:
In deze gevallen zouden we "One to Many" -relaties moeten maken. Hier is een voorbeeld:
Elke klant heeft mogelijk nul, een of meerdere bestellingen. Maar een bestelling kan slechts aan één klant toebehoren.
In sommige gevallen hebt u mogelijk meerdere instanties aan beide zijden van de relatie nodig. Elke bestelling kan bijvoorbeeld meerdere items bevatten. En elk item kan ook in meerdere bestellingen staan.
Voor deze relaties moeten we een extra tabel maken:
De Items_Orders-tabel heeft slechts één doel en dat is om een "Veel-tot-veel" -relatie te creëren tussen de artikelen en de bestellingen.
Hier is een manier hoe we dit soort relaties kunnen visualiseren:
Als u de records items_orders in de grafiek wilt opnemen, ziet het er als volgt uit:
Dit wordt gebruikt wanneer een tafel een relatie met zichzelf moet hebben. Stel dat u bijvoorbeeld een verwijzingsprogramma heeft. Klanten kunnen andere klanten verwijzen naar uw winkelwebsite. De tabel kan er als volgt uitzien:
Klanten 102 en 103 werden door de klant verwezen 101.
Dit kan in feite ook vergelijkbaar zijn met een "one-to-many" relatie omdat een klant meerdere klanten kan doorverwijzen. Ook kan het worden gevisualiseerd als een boomstructuur:
Eén klant kan nul, één of meerdere klanten doorverwijzen. Elke klant kan worden doorverwezen door slechts één klant, of helemaal geen klant.
Als je een "veel-naar-veel" -relatie wilt creëren, heb je een extra tafel nodig, net zoals we in de vorige sectie hebben besproken..
Tot nu toe hebben we slechts enkele van de concepten geleerd. Nu is het tijd om ze tot leven te brengen met behulp van SQL. Voor dit deel moeten we begrijpen wat Foreign Keys is.
In de bovenstaande relatievoorbeelden hadden we altijd deze "**** _ id" -velden die naar een kolom in een andere tabel verwezen. In dit voorbeeld is de kolom customer_id in de tabel Orders een kolom Foreign Key:
Met een database zoals MySQL zijn er twee manieren om kolommen met externe sleutels te maken:
Laten we een eenvoudige klantenlijst maken:
CREATE TABLE klanten (customer_id INT AUTO_INCREMENT PRIMARY KEY, klantnaam VARCHAR (100));
Nu de tabel met orders, die een Foreign Key zal bevatten:
MAAK TAFEL orders (order_id INT AUTO_INCREMENT PRIMAIRE SLEUTEL, customer_id INT, aantal DUBBELE, BUITENLANDSE SLEUTEL (customer_id) REFERENTIES klanten (customer_id));
Beide kolommen (customers.customer_id en orders.customer_id) moeten dezelfde exacte gegevensstructuur hebben. Als een INT is, moet de andere bijvoorbeeld niet BIGINT zijn.
Merk op dat in MySQL alleen de InnoDB-engine volledige ondersteuning biedt voor Foreign Keys. Maar met andere opslag-engines kunt u ze nog steeds specificeren zonder fouten te geven. De kolom Buitenlandse sleutel wordt ook automatisch geïndexeerd, tenzij u hier een andere index voor opgeeft.
De tabel met dezelfde orders kan worden gemaakt zonder de kolom customer_id expliciet als een foreign key te vermelden:
MAAKTAFEL orders (order_id INT AUTO_INCREMENT PRIMAIRE SLEUTEL, customer_id INT, aantal DOUBLE, INDEX (customer_id));
Wanneer u gegevens ophaalt met een JOIN-query, kunt u deze kolom nog steeds als een buitenlandse sleutel behandelen, ook al is de database-engine niet op de hoogte van die relatie.
SELECTEER * FROM bestellingen WORD LID VAN JOINING USERS (customer_id)
We zullen meer informatie vinden over JOIN-zoekopdrachten verderop in het artikel.
Mijn huidige favoriete software voor het ontwerpen van databases en het visualiseren van de relaties met Foreign Key is MySQL Workbench.
Nadat u uw database hebt ontworpen, kunt u de SQL exporteren en op uw server uitvoeren. Dit is erg handig voor grotere en complexere databaseontwerpen.
Om gegevens uit een database met relaties te halen, moeten we vaak JOIN-zoekopdrachten gebruiken.
Voordat we aan de slag gaan, maken we de tabellen en enkele voorbeeldgegevens om mee te werken.
Accentsconagua
CREATE TABLE klanten (customer_id INT AUTO_INCREMENT PRIMARY KEY, klantnaam VARCHAR (100)); MAAK TAFEL orders (order_id INT AUTO_INCREMENT PRIMAIRE SLEUTEL, customer_id INT, aantal DUBBELE, BUITENLANDSE SLEUTEL (customer_id) REFERENTIES klanten (customer_id)); INSERT INTO 'klanten' ('customer_id', 'customer_name') VALUES (1, 'Adam'), (2, 'Andy'), (3, 'Joe'), (4, 'Sandy'); INSERT INTO 'orders' ('order_id', 'klant_id', 'bedrag') VALUES (1, 1, 19.99), (2, 1, 35.15), (3, 3, 17.56), (4, 4, 12.34) ; We hebben 4 klanten. Eén klant heeft twee bestellingen, twee klanten hebben elk één bestelling en één klant heeft geen bestelling. Laten we nu de verschillende soorten JOIN-zoekopdrachten bekijken die we op deze tabellen kunnen uitvoeren.
Dit is het standaardtype JOIN-query wanneer er geen voorwaarde is opgegeven.
Het resultaat is een zogenaamd "Cartesiaans product" van de tabellen. Dit betekent dat elke rij uit de eerste tabel overeenkomt met elke rij van de tweede tabel. Omdat elke tabel 4 rijen had, kregen we uiteindelijk een resultaat van 16 rijen.
Het sleutelwoord JOIN kan optioneel worden vervangen door een komma.
Natuurlijk is dit soort resultaat meestal niet nuttig. Laten we dus de andere join-typen bekijken.
Met dit soort JOIN-query moeten de tabellen een overeenkomende kolomnaam hebben. In ons geval hebben beide tabellen de kolom customer_id. MySQL zal dus alleen aan de records deelnemen als de waarde van deze kolom overeenkomt met twee records.
Zoals u kunt zien, wordt de kolom customer_id slechts eenmaal weergegeven, omdat de database-engine dit als de gemeenschappelijke kolom behandelt. We kunnen de twee orders zien geplaatst door Adam, en de andere twee bevelen door Joe en Sandy. Eindelijk krijgen we wat nuttige informatie.
Wanneer een joinvoorwaarde is opgegeven, wordt een inner join uitgevoerd. In dit geval zou het een goed idee zijn om het veld customer_id in beide tabellen overeen te laten komen. De resultaten moeten vergelijkbaar zijn met de natuurlijke join.
De resultaten zijn hetzelfde behalve een klein verschil. De kolom customer_id wordt twee keer herhaald, één keer voor elke tabel. De reden is dat we alleen de database hebben gevraagd om de waarden op deze twee kolommen te evenaren. Maar het is feitelijk niet op de hoogte dat ze dezelfde informatie vertegenwoordigen.
Laten we wat meer voorwaarden aan de zoekopdracht toevoegen.
Deze keer ontvingen we alleen de bestellingen van meer dan $ 15.
Voordat we overgaan op andere join-typen, moeten we naar de ON-clausule kijken. Dit is handig om de JOIN-voorwaarden in een aparte clausule te plaatsen.
Nu kunnen we de voorwaarde JOIN van de WHERE-voorwaarden van de clausule onderscheiden. Maar er is ook een klein verschil in functionaliteit. We zullen dat zien in de LEFT JOIN-voorbeelden.
De clausule USING lijkt op de ON-component, maar is korter. Als een kolom op beide tabellen dezelfde naam heeft, kunnen we deze hier specificeren.
In feite lijkt dit veel op de NATURAL JOIN, dus de join-kolom (customer_id) wordt niet tweemaal in de resultaten herhaald.
A LINKS JOIN is een type Outer Join. Als er in deze query's geen overeenkomst wordt gevonden in de tweede tabel, wordt de record uit de eerste tabel nog steeds weergegeven.
Hoewel Andy geen orders heeft, wordt zijn record nog steeds weergegeven. De waarden onder de kolommen van de tweede tabel zijn ingesteld op NULL.
Dit is ook handig om records te vinden die geen relaties hebben. We kunnen bijvoorbeeld zoeken naar klanten die geen bestellingen hebben geplaatst.
Het enige wat we deden was om te zoeken naar NULL-waarden voor de order_id.
Merk ook op dat het OUTER-sleutelwoord optioneel is. Je kunt gewoon LEFT JOIN gebruiken in plaats van LEFT OUTER JOIN.
Laten we nu eens kijken naar een vraag met een voorwaarde.
Dus wat gebeurde er met Andy en Sandy? LEFT JOIN moest klanten retourneren zonder overeenkomende bestellingen. Het probleem is dat de WHERE-component die resultaten blokkeert. Om ze te krijgen, kunnen we ook proberen de NULL-voorwaarde op te nemen.
We hebben Andy maar geen Sandy. Toch ziet dit er niet goed uit. Om te krijgen wat we willen, moeten we de AAN-clausule gebruiken.
Nu hebben we iedereen en alle bestellingen van meer dan $ 15. Zoals ik eerder zei, heeft de ON-component soms een iets andere functionaliteit dan de WHERE-component. In een outer join zoals deze zijn rijen opgenomen, zelfs als deze niet overeenkomen met de voorwaarden van de ON-clausule.
A RIGHT OUTER JOIN werkt precies hetzelfde, maar de volgorde van de tabellen is omgekeerd.
Deze keer hebben we geen NULL-resultaten omdat elke bestelling een overeenkomend klantrecord heeft. We kunnen de volgorde van de tabellen veranderen en dezelfde resultaten krijgen als bij de LINKER OUTER JOIN.
Nu hebben we die NULL-waarden omdat de tabel met klanten zich aan de rechterkant van de join bevindt.
Bedankt voor het lezen van het artikel. Ik hoop dat je het leuk vond! Laat uw opmerkingen en vragen achter en geniet van een geweldige dag!
Vergeet niet om de SQL-scripts, apps en add-ons op Envato Market te bekijken. U krijgt een idee van wat mogelijk is met SQL-databases en misschien vindt u de perfecte oplossing om u te helpen met uw huidige ontwikkelingsproject.
Volg ons op Twitter of abonneer je op de Nettuts + RSS Feed voor de beste tutorials voor webontwikkeling op internet.
