Share
Idag fortsätter vi vår resa till världen av SQL och relationsdatabasystem. I denna del tre av serierna lär vi oss hur vi arbetar med flera tabeller som har relationer med varandra. Först ska vi gå över några grundläggande begrepp, och sedan börjar vi arbeta med JOIN-frågor i SQL.
Du kan också se SQL-databaser i funktion genom att kolla in SQL-skript, appar och tillägg på Envato Market.
När man skapar en databas dikterar sunt förnuft att vi använder separata tabeller för olika typer av enheter. Några exempel är: kunder, order, föremål, meddelanden etc ... Men vi behöver också ha relationer mellan dessa tabeller. Till exempel gör kunder beställningar och order innehåller varor. Dessa relationer måste vara representerade i databasen. När vi hämtar data med SQL måste vi också använda vissa typer av JOIN-frågor för att få det vi behöver.
Det finns flera typer av databasrelationer. Idag kommer vi att täcka följande:
När du väljer data från flera tabeller med relationer använder vi JOIN-frågan. Det finns flera typer av JOIN, och vi kommer att lära oss om följande:
Vi kommer också att lära oss om ON-klausulen och USING-klausulen.
Låt oss säga att du har ett bord för kunder:
Vi kan lägga kundadressinformationen på ett separat bord:
Nu har vi en relation mellan tabellen Kunder och Adresser. Om varje adress kan tillhöra endast en kund är detta förhållande "En till En". Tänk på att denna typ av förhållande inte är så vanligt. Vårt första bord som inkluderade adressen tillsammans med kunden kunde ha fungerat bra i de flesta fall.
Observera att det nu finns ett fält med namnet "address_id" i tabellen Kunder, som refererar till matchningsrekordet i Adress-tabellen. Detta kallas en "Foreign Key" och den används för alla typer av databasrelationer. Vi kommer att täcka detta ämne senare i artikeln.
Vi kan visualisera förhållandet mellan kunden och adressposterna så här:
Observera att förekomsten av ett förhållande kan vara frivilligt, till exempel att ha en kundrekord som inte har någon relaterad adresspost.
Detta är den vanligaste typen av relation. Tänk på en e-handelswebbplats med följande:
I dessa fall skulle vi behöva skapa "One to Many" -relationer. Här är ett exempel:
Varje kund kan ha noll, en eller flera order. Men en order kan tillhöra endast en kund.
I vissa fall kan du behöva flera instanser på båda sidor av relationen. Till exempel kan varje order innehålla flera objekt. Och varje objekt kan också vara i flera order.
För dessa relationer måste vi skapa ett extra bord:
Tabellen Items_Orders har bara ett syfte, och det är att skapa ett "många till många" förhållande mellan artiklarna och orderna.
Här är en hur vi kan visualisera denna typ av relation:
Om du vill inkludera posterna records_orders i diagrammet kan det se ut så här:
Detta används när ett bord måste ha ett förhållande med sig själv. Låt oss till exempel säga att du har ett remissprogram. Kunder kan hänvisa andra kunder till din shoppingwebbplats. Bordet kan se ut så här:
Kunderna 102 och 103 hänvisades av kunden 101.
Detta kan faktiskt också likna "ett till många" förhållande eftersom en kund kan hänvisa till flera kunder. Det kan också visualiseras som en trädstruktur:
En kund kan referera noll, en eller flera kunder. Varje kund kan hänvisas av endast en kund, eller ingen alls.
Om du vill skapa en egen referens "många till många" förhållande, skulle du behöva ett extra bord precis som vi pratat om i det sista avsnittet.
Hittills har vi bara lärt oss om några av koncepten. Nu är det dags att få dem till liv med SQL. För den här delen måste vi förstå vilka utländska nycklar.
I förhållandena ovanstående exempel hade vi alltid dessa "**** _ id" -fält som hänvisade till en kolumn i en annan tabell. I det här exemplet är kolumnen customer_id i Order-tabellen en Foreign Key-kolumn:
Med en databas som MySQL finns det två sätt att skapa främmande nyckelspelare:
Låt oss skapa en enkel kund tabell:
CREATE TABLE kunder (customer_id INT AUTO_INCREMENT PRIMARY KEY, kundnamn VARCHAR (100));
Nu beställnings tabellen, som kommer att innehålla en utländsk nyckel:
CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMÄR NYCKEL, customer_id INT, belopp DUBBEL, UTGÅENDE KEY (customer_id) REFERENCES kunder (customer_id));
Båda kolumnerna (customers.customer_id och orders.customer_id) ska vara samma exakta datastruktur. Om man är INT, bör den andra inte vara BIGINT till exempel.
Observera att i MySQL endast InnoDB-motorn har fullt stöd för utländska nycklar. Men andra lagringsmotorer tillåter dig fortfarande att ange dem utan att ge några fel. Även indexkolumnen indexeras automatiskt, om du inte anger ett annat index för det.
Samma beställnings tabell kan skapas utan att uttryckligen förklara kolumn customer_id att vara en utländsk nyckel:
SKAPA TABLE-order (order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, antal DUBBEL, INDEX (customer_id));
När du hämtar data med en JOIN-fråga kan du fortfarande behandla den här kolumnen som en utländsk nyckel trots att databasmotorn inte känner till det förhållandet.
VÄLJ * FRÅN beställningar JOIN-kunder ANVÄNDER (customer_id)
Vi kommer att lära oss mer om JOIN-frågor längre fram i artikeln.
Min nuvarande favoritprogramvara för att designa databaser och visualisera de utländska nyckelrelationerna är MySQL Workbench.
När du har designat din databas kan du exportera SQL och köra den på din server. Detta är mycket användbart för större och mer komplexa databasdesigner.
För att hämta data från en databas som har relationer, behöver vi ofta använda JOIN-frågor.
Innan vi börjar, låt oss skapa tabellerna och några exempeldata att arbeta med.
Accentsconagua
CREATE TABLE kunder (customer_id INT AUTO_INCREMENT PRIMARY KEY, kundnamn VARCHAR (100)); CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMÄR NYCKEL, customer_id INT, belopp DUBBEL, UTGÅENDE KEY (customer_id) REFERENCES kunder (customer_id)); INSERT INTO 'kunder' ('customer_id', 'customer_name') värden (1, 'Adam'), (2, 'Andy'), (3, 'Joe'), (4, 'Sandy'); INSERT INTO "order" ("order_id", "customer_id", "amount") VÄRDER (1, 1, 19.99), (2, 1, 35.15), (3, 3, 17.56), (4, 4, 12.34) ; Vi har 4 kunder. En kund har två order, två kunder har en order vardera och en kund har ingen order. Låt oss nu se de olika typerna av JOIN-frågor som vi kan köra på dessa tabeller.
Det här är standard typen av JOIN-fråga när inget villkor anges.
Resultatet är en så kallad "kartesisk produkt" av tabellerna. Det betyder att varje rad från den första tabellen matchas med varje rad i den andra tabellen. Eftersom varje tabell hade 4 rader slutade vi att få ett resultat av 16 rader.
JOIN-sökordet kan valfritt ersättas med ett kommatecken istället.
Naturligtvis är den här typen av resultat vanligtvis inte användbar. Så låt oss titta på de andra medverkande typerna.
Med denna typ av JOIN-fråga måste tabellerna ha ett matchande kolumnnamn. I vårt fall har båda tabellerna customer_id-kolumnen. Så, MySQL kommer endast att ansluta till posterna när värdet på den här kolumnen matchar två poster.
Som du kan se kolumnen customer_id visas bara en gång den här tiden, eftersom databasmotorn behandlar detta som den vanliga kolumnen. Vi kan se de två beställningarna som Adam och de andra två beställningarna av Joe och Sandy ställde. Slutligen får vi lite användbar information.
När ett anslutningsvillkor är angivet, utförs ett Inner Join. I det här fallet skulle det vara en bra idé att få matchen customer_id på båda tabellerna. Resultaten ska likna Natural Join.
Resultaten är desamma utom en liten skillnad. Column customer_id upprepas två gånger, en gång för varje tabell. Anledningen är att vi bara bad databasen att matcha värdena på dessa två kolumner. Men det är faktiskt omedvetet att de representerar samma information.
Låt oss lägga till några fler villkor för frågan.
Den här gången fick vi bara orderna över $ 15.
Innan vi går vidare till andra medverkande typer måste vi titta på ON-klausulen. Detta är användbart för att sätta JOIN-villkoren i en separat klausul.
Nu kan vi skilja på JOIN-villkoret från WHERE-villkoret. Men det finns också en liten skillnad i funktionalitet. Vi kommer att se det i Vänster JOIN-exemplen.
USING-klausulen liknar ON-klausulen, men den är kortare. Om en kolumn är samma namn på båda tabellerna kan vi ange det här.
Faktum är att det här är ungefär som NATURLIGT JOIN, så att kolumnen (customer_id) inte upprepas två gånger i resultaten.
En VÄNSTER JOIN är en typ av Outer Join. I dessa frågor, om det inte finns någon matchning från den andra tabellen, visas posten från den första tabellen fortfarande.
Även om Andy inte har några beställningar visas hans skiva fortfarande. Värdena under kolumnerna i den andra tabellen är inställda på NULL.
Detta är också användbart för att hitta poster som inte har relationer. Till exempel kan vi söka efter kunder som inte har lagt några beställningar.
Allt vi gjorde var att leta efter NULL-värden för order_id.
Observera också att det yttre sökordet är valfritt. Du kan bara använda VÄNSTER JOIN istället för VÄNSTER UTGÅENDE JOIN.
Låt oss nu titta på en fråga med ett villkor.
Så vad hände med Andy och Sandy? VÄNSTER JOIN skulle returnera kunder utan matchande order. Problemet är att WHERE-klausulen blockerar dessa resultat. För att få dem kan vi försöka inkludera NULL-villkoret också.
Vi har Andy men ingen Sandy. Ändå ser det inte ut rätt. För att få vad vi vill behöver vi använda ON-klausulen.
Nu fick vi alla, och alla beställningar över $ 15. Som jag sa tidigare har ON-klausulen ibland lite annorlunda funktionalitet än WHERE-klausulen. I ett yttre samspel som den här är rader inkluderade även om de inte överensstämmer med villkoren.
En RÄTT YTTRE JOIN fungerar exakt densamma, men ordningens ordning är omvänd.
Den här gången har vi inga NULL-resultat eftersom varje order har en matchande kundrekord. Vi kan ändra ordning på tabellerna och få samma resultat som vi gjorde från den vänstra yttre delen.
Nu har vi NULL-värdena för att kundernas bord ligger på höger sida av medlemmen.
Tack för att du läste artikeln. Jag hoppas att du tyckte om det! Vänligen lämna dina kommentarer och frågor, och ha en bra dag!
Glöm inte att kolla in SQL-skript, appar och tillägg på Envato Market. Du får en känsla av vad som är möjligt med SQL-databaser, och du kan hitta den perfekta lösningen för att hjälpa dig med ditt nuvarande utvecklingsprojekt.
Följ oss på Twitter, eller prenumerera på Nettuts + RSS-flödet för de bästa webbutvecklingsstudierna på webben.
