Share
I dag fortsetter vi vår reise inn i verden av SQL- og relationsdatabasesystemer. I denne delen tre av serien lærer vi hvordan vi arbeider med flere tabeller som har forhold til hverandre. Først vil vi gå over noen kjernekonsepter, og deretter begynne å jobbe med JOIN-spørringer i SQL.
Du kan også se SQL-databaser i bruk ved å sjekke ut SQL-skriptene, appene og tilleggene på Envato Market.
Når du oppretter en database, dikterer sunn fornuft at vi bruker separate tabeller for ulike typer enheter. Noen eksempler er: kunder, ordre, elementer, meldinger etc ... Men vi må også ha forhold mellom disse tabellene. For eksempel gjør kunder bestillinger, og ordrer inneholder elementer. Disse forholdene må være representert i databasen. Når vi henter data med SQL, må vi også bruke visse typer JOIN-spørringer for å få det vi trenger.
Det finnes flere typer database relasjoner. I dag skal vi dekke følgende:
Når du velger data fra flere tabeller med relasjoner, bruker vi JOIN-spørringen. Det er flere typer JOIN, og vi skal lære om følgende:
Vi vil også lære om ON-klausulen og USING-klausulen.
La oss si at du har et bord for kunder:
Vi kan legge kundens adresseinformasjon på et eget bord:
Nå har vi et forhold mellom tabellen Kunder og Adressebordet. Hvis hver adresse kan tilhøre bare en kunde, er dette forholdet "One to One". Husk at denne typen forhold ikke er veldig vanlig. Vårt første bord som inkluderte adressen sammen med kunden kunne ha fungert bra i de fleste tilfeller.
Legg merke til at nå er det et felt som heter "address_id" i Kunder-tabellen, som refererer til den tilsvarende posten i adressetabellen. Dette kalles en "Foreign Key", og den brukes til alle slags databaseforhold. Vi vil dekke dette emnet senere i artikkelen.
Vi kan visualisere forholdet mellom kunden og adressepostene slik:
Merk at eksistensen av et forhold kan være valgfritt, for eksempel å ha en kundeoppføring som ikke har noen relatert adressepost.
Dette er den mest brukte typen forhold. Vurder et e-handelsnettsted med følgende:
I disse tilfellene trenger vi å skape "One to Many" relasjoner. Her er et eksempel:
Hver kunde kan ha null, en eller flere bestillinger. Men en ordre kan bare tilhøre en kunde.
I noen tilfeller kan det hende du trenger flere forekomster på begge sider av forholdet. For eksempel kan hver ordre inneholde flere elementer. Og hvert element kan også være i flere ordrer.
For disse forholdene må vi lage et ekstra bord:
Tabellen Items_Orders har bare ett formål, og det er å skape et "mange til mange" forhold mellom elementene og ordrene.
Her er en hvordan vi kan visualisere denne typen forhold:
Hvis du vil inkludere postene poster i grafen, kan det se slik ut:
Dette brukes når et bord må ha forhold til seg selv. For eksempel, la oss si at du har et henvisningsprogram. Kunder kan henvise andre kunder til ditt shopping-nettsted. Tabellen kan se slik ut:
Kunder 102 og 103 ble henvist av kunden 101.
Dette kan faktisk også lignes på "ett til mange" forhold siden en kunde kan henvise flere kunder. Også det kan visualiseres som en trestruktur:
En kunde kan referere til null, en eller flere kunder. Hver kunde kan kun bli henvist av bare én kunde, eller ingen i det hele tatt.
Hvis du ønsker å lage et selvstendig referanse-forhold til mange til mange, vil du trenge et ekstra bord, akkurat som vi snakket om i den siste delen.
Så langt har vi bare lært om noen av konseptene. Nå er det på tide å bringe dem til livs ved hjelp av SQL. For denne delen må vi forstå hvilke utenlandske nøkler.
I forholdene ovenfor har vi alltid hatt disse "**** _ id" -feltene som refererte til en kolonne i et annet bord. I dette eksemplet er kolonnen customer_id i Order-tabellen en Foreign Key-kolonne:
Med en database som MySQL, er det to måter å opprette utenlandske nøkler kolonner:
La oss lage et enkelt kundetabell:
CREATE TABLE kunder (customer_id INT AUTO_INCREMENT PRIMARY KEY, customer_name VARCHAR (100));
Nå ordre bordet, som vil inneholde en utenlandsk nøkkel:
CREATE TABLE ordrer (order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, mengde DOBBEL, ULOVLIG KEY (customer_id) REFERENCES kunder (customer_id));
Begge kolonnene (customers.customer_id og orders.customer_id) skal være den samme eksakte datastrukturen. Hvis en er INT, bør den andre ikke være BIGINT for eksempel.
Vær oppmerksom på at i MySQL kun InnoDB-motoren har full støtte for utenlandske nøkler. Men andre lagringsmotorer vil fortsatt tillate deg å spesifisere dem uten å gi noen feil. Også den Foreign Key-kolonnen er indeksert automatisk, med mindre du angir en annen indeks for den.
Samme ordrebord kan opprettes uten at det eksplisitt erklæres at kolonnen customer_id skal være en utenlandsk nøkkel:
CREATE TABLE ordrer (order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, mengde DOBBEL, INDEX (customer_id));
Når du henter data med et JOIN-spørring, kan du fremdeles behandle denne kolonnen som en fremmednøkkel, selv om databasemotoren ikke er klar over forholdet.
VELG * FRA bestillinger KOMME MED KUNDER BRUKER (customer_id)
Vi skal lære om JOIN-spørringer nærmere i artikkelen.
Min nåværende favorittprogramvare for å designe databaser og visualisere Foreign Key-relasjonene er MySQL Workbench.
Når du designer databasen din, kan du eksportere SQL og kjøre den på serveren din. Dette kommer veldig praktisk til større og mer komplekse databasedesigner.
For å hente data fra en database som har forhold, må vi ofte bruke JOIN-spørringer.
Før vi begynner, la oss lage tabellene og noen eksempler på data for å jobbe med.
Accentsconagua
CREATE TABLE kunder (customer_id INT AUTO_INCREMENT PRIMARY KEY, customer_name VARCHAR (100)); CREATE TABLE ordrer (order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, mengde DOBBEL, ULOVLIG KEY (customer_id) REFERENCES kunder (customer_id)); INSERT TIL 'kunder' ('customer_id', 'customer_name') verdier (1, 'Adam'), (2, 'Andy'), (3, 'Joe'), (4, 'Sandy'); INSERT TIL 'bestillinger' ('order_id', 'customer_id', 'amount') verdier (1, 1, 19,99), (2, 1, 35,15), (3, 3, 17,56), (4, 4, 12,34) ; Vi har 4 kunder. En kunde har to bestillinger, to kunder har en bestilling hver og en kunde har ingen ordre. La oss nå se de forskjellige typer JOIN-spørsmål vi kan kjøre på disse tabellene.
Dette er standard typen JOIN-spørring når det ikke er angitt noen betingelse.
Resultatet er et såkalt "kartesisk produkt" av tabellene. Det betyr at hver rad fra den første tabellen er matchet med hver rad i det andre bordet. Siden hvert bord hadde 4 rader, endte vi med å få et resultat av 16 rader.
JOIN-søkeordet kan eventuelt erstattes med et komma i stedet.
Selvfølgelig er denne typen resultat vanligvis ikke nyttig. Så la oss se på de andre samarbeidstyper.
Med denne typen JOIN-spørring må tabellene ha et tilsvarende kolonnenavn. I vårt tilfelle har begge tabellene kolonnen customer_id. Så, MySQL vil bli med i postene bare når verdien av denne kolonnen passer på to poster.
Som du kan se kolonnen customer_id vises bare en gang denne gangen, fordi databasemotoren behandler dette som den vanlige kolonnen. Vi kan se de to ordrene som Adam og de andre to ordene av Joe og Sandy har lagt. Til slutt får vi litt nyttig informasjon.
Når en tilmeldingsbetingelse er angitt, utføres en Inner Join. I dette tilfellet vil det være en god ide å ha matchen customer_id på begge tabellene. Resultatene skal være lik Natural Join.
Resultatene er de samme bortsett fra en liten forskjell. Customer_id-kolonnen gjentas to ganger, en gang for hver tabell. Årsaken er at vi bare ba databasen om å matche verdiene på disse to kolonnene. Men det er faktisk uvitende at de representerer den samme informasjonen.
La oss legge til flere forhold til spørringen.
Denne gangen mottok vi kun ordrene over $ 15.
Før vi går videre til andre samarbeidstyper, må vi se på ON-klausulen. Dette er nyttig for å sette JOIN-betingelsene i en separat klausul.
Nå kan vi skille JOIN-tilstanden fra WHERE-vilkårene. Men det er også en liten forskjell i funksjonalitet. Vi vil se det i VENSTRE JOIN-eksemplene.
USING-klausulen ligner ON-klausulen, men den er kortere. Hvis en kolonne har samme navn på begge tabellene, kan vi spesifisere det her.
Faktisk er dette mye som NATURLIGT FØLG, slik at kolonnen for å delta (customer_id) ikke gjentas to ganger i resultatene.
En LEFT JOIN er en type Outer Join. I disse spørsmålene, hvis det ikke finnes noen kamp fra det andre bordet, vises ikke posten fra den første tabellen.
Selv om Andy ikke har noen ordre, blir hans plate fortsatt vist. Verdiene under kolonnene i den andre tabellen er satt til NULL.
Dette er også nyttig for å finne poster som ikke har forhold. For eksempel kan vi søke etter kunder som ikke har lagt inn noen ordre.
Alt vi gjorde var å lete etter NULL-verdier for order_id.
Vær også oppmerksom på at det ytre søkeordet er valgfritt. Du kan bare bruke VENSTRE JOIN i stedet for LEFT OUTER JOIN.
La oss nå se på et spørsmål med en tilstand.
Så hva skjedde med Andy og Sandy? VENSTRE JOIN skulle returnere kunder uten matchende ordrer. Problemet er at WHERE-klausulen blokkerer disse resultatene. For å få dem kan vi prøve å inkludere NULL tilstanden også.
Vi har Andy, men ingen Sandy. Likevel ser det ikke ut til riktig. For å få det vi ønsker, må vi bruke ON-klausulen.
Nå har vi alle, og alle bestillinger over $ 15. Som jeg sa tidligere, har ON-klausulen noen ganger litt annen funksjonalitet enn WHERE-klausulen. I et Ytre Bli med som denne, er rader inkludert selv om de ikke samsvarer med ON-betingelsene.
Et RIGHT OUTER JOIN fungerer nøyaktig det samme, men rekkefølgen på tabellene er reversert.
Denne gangen har vi ingen NULL-resultater fordi hver ordre har en matchende kundeoppføring. Vi kan endre rekkefølgen på tabellene og få de samme resultatene som vi gjorde fra VENSTRE YTRE JOIN.
Nå har vi de NULL-verdiene fordi kundetabellen er på høyre side av medlemmet.
Takk for at du har lest artikkelen. Jeg håper du likte det! Vennligst legg inn dine kommentarer og spørsmål, og ha en flott dag!
Ikke glem å sjekke ut SQL-skriptene, appene og tilleggene på Envato Market. Du får en følelse av hva som er mulig med SQL-databaser, og du kan finne den perfekte løsningen for å hjelpe deg med ditt nåværende utviklingsprosjekt.
Følg oss på Twitter, eller abonner på Nettuts + RSS-feed for de beste webutviklingsopplæringene på nettet.
