Share
Det er viktig for hver webutvikler å være kjent med databaseinteraksjoner. I del to av serien fortsetter vi å utforske SQL-språket og anvende det vi har lært på en MySQL-database. Vi vil lære om indekser, datatyper og mer komplekse spørringsstrukturer.
Vennligst se delen "Hva du trenger" i den første artikkelen her: SQL For Beginners (del 1).
Hvis du vil følge eksemplene i denne artikkelen på din egen utviklingsserver, gjør du følgende:
Indekser (eller nøkler) brukes hovedsakelig for å forbedre hastigheten på datainnhentingsoperasjoner (f.eks. SELECT) på tabeller.
De er en så viktig del av en god database design, det er vanskelig å klassifisere dem som "optimalisering". I de fleste tilfeller er de inkludert i opprinnelig design, men de kan også legges til senere med et ALTER TABLE-spørring.
De vanligste årsakene til å indeksere databasekolonner er:
Nesten hvert bord skal ha en PRIMARY KEY, i de fleste tilfeller som en INT med AUTO_INCREMET alternativet.
Hvis du husker fra den første artikkelen, opprettet vi et "user_id" -felt i brukertabellen, og det var en PRIMARY KEY. På denne måten, i en webapplikasjon, kan vi henvise til alle brukere med deres id-nummer.
Verdiene som er lagret i en PRIMARY KEY-kolonne må være unike. Også, det kan ikke være mer enn én PRIMARY KEY på hver tabell.
La oss se et utvalgssøk, opprette et bord for USA-stater listen:
CREATE TABLE states (id INT AUTO_INCREMENT PRIMARY KEY, navn VARCHAR (20));
Det kan også skrives slik:
CREATE TABLE states (id INT AUTO_INCREMENT, navn VARCHAR (20), PRIMARY KEY (id));
Siden vi forventer at statenavnet skal være en unik verdi, bør vi endre det forrige spørreeksemplet litt:
CREATE TABLE states (id INT AUTO_INCREMENT, navn VARCHAR (20), PRIMARY KEY (id), UNIQUE (navn));
Som standard blir indeksen navngitt etter kolonnens navn. Hvis du vil, kan du tilordne et annet navn til det:
CREATE TABLE states (id INT AUTO_INCREMENT, navn VARCHAR (20), PRIMARY KEY (id), UNIQUE state_name (navn));
Nå er indeksen kalt 'state_name' i stedet for 'navn'.
La oss si at vi vil legge til en kolonne for å representere året som hver stat ble med.
CREATE TABLE states (ID INT AUTO_INCREMENT, navn VARCHAR (20), join_year INT, PRIMARY KEY (id), UNIQUE (navn), INDEX (join_year));
Jeg har nettopp lagt til kolonnen join_year og indeksert den. Denne typen indeks har ikke den unike begrensningen.
Du kan også nevne det NØKKEL i stedet for INDEX.
CREATE TABLE states (id INT AUTO_INCREMENT, navn VARCHAR (20), join_year INT, PRIMARY KEY (id), UNIQUE (navn), KEY (join_year));
Legge til en indeks reduserer ytelsen til INSERT og UPDATE spørringer. Fordi hver gang nye data legges til bordet, oppdateres indeksdataene automatisk, noe som krever ekstra arbeid. Prestasjonsvinduene på SELECT-søkene oppveier vanligvis langt over dette. Men likevel, ikke bare legg til indekser på hver enkelt tabell kolonne uten å tenke på spørsmålene du vil kjøre.
Før vi går videre med flere spørsmål, vil jeg gjerne lage en prøve tabell med noen data.
Dette vil bli en liste over amerikanske stater, med deres datoen for datoen (datoen staten ratifiserte USAs grunnlov eller ble tatt opp i unionen) og deres nåværende befolkninger. Du kan kopiere lim inn følgende til MySQL-konsollen:
CREATE TABLE states (id INT AUTO_INCREMENT, navn VARCHAR (20), join_year INT, befolkning INT, PRIMARY KEY (id), UNIQUE (navn), KEY (join_year)); INSERT TIL STATER VÆRDIER (1, 'Alabama', 1819, 4661900), (2, 'Alaska', 1959, 686293), (3 'Arizona', 1912, 6500180), (4, 'Arkansas', 1836, 2855390 ), (5, 'California', 1850, 36756666), (6, 'Colorado', 1876, 4939456), (7 'Connecticut', 1788, 3501252), (8, 'Delaware', 1787, 873092) (9, Florida, 1845, 18328340), (10, Georgia, 1788, 9685744), (11, Hawaii, 1959, 1288198), (12, Idaho, 1890, 1523816), , Illinois, 1818, 12901563), (14, Indiana, 1816, 6376792), (15, Iowa, 1846, 3002555), (16, 'Kansas', 1861, 2802134), Kentucky, 1792, 4269245), (18, 'Louisiana', 1812, 4410796), (19, 'Maine', 1820, 1316456), (20, 'Maryland', 1788, 5633597) , 1788, 6497967), (22, 'Michigan', 1837, 10003422), (23, 'Minnesota', 1858, 5220393), (24, 'Mississippi', 1817, 2938618), (25, 'Missouri', 1821 , 5911605), (26, 'Montana', 1889, 967440), (27, 'Nebraska', 1867, 1783432), (28, 'Nevada', 1864, 2600167), (29, 'New Hampshire', 1788, 1315809), (30, 'New Jersey', 1787, 8682 661), 31, New Mexico, 1912, 1984356, 32, New York, 1788, 19490297, 33, North Carolina, 1789, 9222414), 34, 'North Dakota' 1889, 641481), (35, 'Ohio', 1803, 11485910), (36, 'Oklahoma', 1907, 3642361), (37, 'Oregon', 1859, 3790060), (38, 'Pennsylvania', 1787, 12448279), (39, 'Rhode Island', 1790, 1050788), (40, 'South Carolina', 1788, 4479800), (41, 'South Dakota', 1889, 804194), (42, 'Tennessee', 1796 , 6214888), (43, 'Texas', 1845, 24326974), (44, 'Utah', 1896, 2736424), (45, 'Vermont', 1791, 621270), (46, 'Virginia', 1788, 7769089 ), (47, 'Washington', 1889, 6549224), (48, 'West Virginia', 1863, 1814468), (49, 'Wisconsin', 1848, 5627967), (50, 'Wyoming', 1890, 532668) ;
GROUP BY-klausulen grupperer de resulterende dataarkene i grupper. Her er et eksempel:
Så hva skjedde det? Vi har 50 rader i tabellen, men 34 resultater ble returnert av denne spørringen. Dette skyldes at resultatene ble gruppert av kolonnen 'join_year'. Med andre ord ser vi bare en rad for hver forskjellig verdi av join_year. Siden noen stater har samme deltaår, har vi mindre enn 50 resultater.
For eksempel var det bare en rad for året 1787, men det er 3 stater i den gruppen:
Så det er tre stater her, men bare Delaware navn ble vist etter GROUP BY-spørringen tidligere. Faktisk kunne det ha vært noen av de tre statene, og vi kan ikke stole på dette datatykket. Så hva er meningen med å bruke GROUP BY-klausulen?
Det ville være mest ubrukelig uten å bruke en aggregatfunksjon som COUNT (). La oss se hva noen av disse funksjonene gjør, og hvordan de kan få oss noen nyttige data.
Dette er kanskje den mest brukte funksjonen sammen med GROUP BY-spørringer. Den returnerer antall rader i hver gruppe.
For eksempel kan vi bruke det til å se antall stater for hvert medarbeiderår:
Hvis du bruker en GROUP BY-aggregatfunksjon, og ikke spesifiserer en GROUP BY-bestemmelse, blir alle resultatene satt i en enkelt gruppe.
Antall alle rader i tabellen:
Antall rader som tilfredsstiller en WHERE-setning:
Disse funksjonene returnerer minimums-, maksimums- og gjennomsnittsverdiene:
Denne funksjonen sammenkaller alle verdier i gruppen til en enkelt streng, med en gitt separator.
I det første GROUP BY spørreeksemplet kunne vi bare se ett statens navn per år. Du kan bruke denne funksjonen til å se alle navnene i hver gruppe:
Hvis det endrede bildet er vanskelig å lese, er dette spørringen:
VELG GROUP_CONCAT (navn SEPARATOR ','), join_year FRA stater GROUP BY join_year;
Du kan bruke dette til å legge opp de numeriske verdiene.
I likhet med andre programmeringsspråk har SQL noe støtte for kontrollflyt.
Dette er en funksjon som tar tre argumenter. Første argument er tilstanden, det andre argumentet brukes hvis tilstanden er sant og det tredje argumentet brukes hvis tilstanden er feil.
Her er et mer praktisk eksempel der vi bruker det med SUM () -funksjonen:
SELECT SUM (IF (befolkning> 5000000, 1, 0)) AS big_states, SUM (IF (population <= 5000000, 1, 0) ) AS small_states FROM states;
Det første SUM () -samtalen teller antall store stater (befolkning over 5 millioner) og den andre teller antall små stater. IF () -kallet i disse SUM () anropene returnerer enten 1 eller 0 basert på tilstanden.
Her er resultatet:
Dette fungerer i likhet med bryteresettklæringene du kanskje er kjent med fra programmering.
La oss si at vi vil kategorisere hver stat i en av tre mulige kategorier.
SELECT COUNT (*), CASE WHEN befolkning> 5000000 THEN 'big' når befolkning> 1000000 THEN 'medium' ELSE 'liten' END AS state_size FRA stater GROUP BY state_size;
Som du kan se, kan vi faktisk GROUP BY verdien returneres fra CASE-setningen. Her er hva som skjer:
HAVING-klausul tillater oss å legge til forhold til "skjulte" felt, for eksempel de returnerte resultatene av aggregatfunksjoner. Så det brukes vanligvis sammen med GROUP BY.
For eksempel, la oss se på spørringen vi brukte for å telle antall stater etter hvert år:
VELG COUNT (*), join_year FRA stater GROUP BY join_year;
Resultatet var 34 rader.
Men la oss si at vi bare er interessert i rader som har en telling høyere enn 1. Vi kan ikke bruke WHERE-klausulen for dette:
Dette er hvor HAVING blir nyttig:
Husk at denne funksjonen kanskje ikke er tilgjengelig i alle databasesystemer.
Det er mulig få resultatene av ett søk og bruk det til en annen spørring.
I dette eksemplet vil vi få staten med den høyeste befolkningen:
VELG * FRA stater WHERE befolkning = (VELG MAX (befolkning) FRA stater);
Den indre spørringen vil returnere den høyeste befolkningen i alle stater. Og det ytre spørsmålet vil søke i tabellen igjen med den verdien.
Du kan tenke dette var et dårlig eksempel, og jeg er litt enig. Det samme spørsmålet kan skrives mer effektivt som dette:
VELG * FRA stater ORDER BY population DESC LIMIT 1;
Resultatene i dette tilfellet er de samme, men det er en viktig forskjell mellom disse to typer spørsmålene. Kanskje et annet eksempel vil vise det bedre.
I dette eksemplet får vi de siste delene som ble med i unionen:
VELG * FRA stater WHERE join_year = (VELG MAX (join_year) FRA stater);
Det er to rader i resultatene denne gangen. Hvis vi hadde brukt ORDER BY ... LIMIT 1 type spørring her, ville vi ikke ha mottatt det samme resultatet.
Noen ganger kan det være lurt å bruke flere resultater returnert av det indre spørsmålet.
Etter spørringen finner årene, når flere stater ble med i unionen, og returnerer listen over disse statene:
VELG * FRA stater WHERE join_year IN (velg join_year FRA stater GROUP BY join_year HAR COUNT (*)> 1) BESTILL BY join_year;
Subqueries kan bli ganske komplekse, derfor vil jeg ikke komme langt inn i dem i denne artikkelen. Hvis du vil lese mer om dem, sjekk ut MySQL-håndboken.
Det er også verdt å merke seg at subqueries kan noen ganger ha dårlig ytelse, så de bør brukes med forsiktighet.
Med en UNION-forespørsel kan vi kombinere resultatene av flere SELECT-spørringer.
Dette eksemplet kombinerer stater som starter med bokstaven "N" og stater med store populasjoner:
(VELG * FRA stater WHERE navn som 'n%') UNION (VELG * FRA stater WHERE befolkning> 10000000);
Legg merke til at New York er begge store og navnet begynner med bokstaven "N". Men det vises bare en gang fordi dupliserte rader fjernes automatisk fra resultatene.
En annen fin ting om UNION er at du kan kombinere søk på forskjellige tabeller.
La oss anta at vi har tabeller for ansatte, ledere og kunder. Og hvert bord har et e-postfelt. Hvis vi ønsker å hente alle e-post med en enkelt spørring, kan vi kjøre dette:
(VELG e-post FRA ansatte) UNION (VELG e-post FRA ledere) UNION (VELG e-post FRA kunder WHERE subscribed = 1);
Det ville hente alle e-postmeldinger av alle ansatte og ledere, men bare e-postene til kunder som har abonnert på å motta e-post.
Vi har allerede snakket om INSERT-spørringen i den siste artikkelen. Nå som vi utforsket databaseindekser i dag, kan vi snakke om mer avanserte funksjoner i INSERT-spørringen.
Dette er nesten som en betinget uttalelse. Spørringen prøver først å utføre en gitt INSERT, og hvis den mislykkes på grunn av en duplikatverdi for en PRIMARY KEY eller UNIQUE KEY, utfører den en UPDATE i stedet.
La oss lage et testbord først.
Det er et bord for å holde produkter. Kolonnen 'lager' er antall produkter vi har på lager.
Prøv nå å sette inn en duplikatverdi og se hva som skjer.
Vi fikk en feil som forventet.
La oss si at vi fikk en ny breadmaker og ønsker å oppdatere databasen, og vi vet ikke om det allerede er en rekord for det. Vi kunne sjekke for eksisterende poster og deretter gjøre en ny spørring basert på det. Eller vi kunne bare gjøre alt i ett enkelt spørsmål:
Dette fungerer akkurat som INSERT med ett viktig unntak. Hvis en dupliserende rad er funnet, slettes den først og deretter utføres INSERT, så vi får ingen feilmeldinger.
Merk at siden dette egentlig er en helt ny rad, ble id økt.
Dette er en måte å undertrykke duplikatfeilene, vanligvis for å forhindre at programmet bryter. Noen ganger kan det være lurt å forsøke å sette inn en ny rad og bare la den mislykkes uten noen klager hvis det finnes en duplikat.
Ingen feil returnert og ingen rader ble oppdatert.
Hver tabellkolonne må ha en datatype. Så langt har vi brukt INT, VARCHAR og DATE-typer, men vi snakket ikke om dem i detalj. Det er også flere andre datatyper vi bør utforske.
Først, la oss starte med de numeriske datatyper. Jeg liker å sette dem inn i to separate grupper: Integers vs Non-Integers.
En heltallskolonne kan bare inneholde naturlige tall (ingen desimaler). Som standard kan de være negative eller positive tall. Men hvis alternativet UNSIGNED er satt, kan det bare inneholde positive tall.
MySQL støtter 5 typer heltall, med forskjellige størrelser og områder:
Disse datatyper kan inneholde desimaltall: FLOAT, DOBBEL og DECIMAL.
FLOAT er 4 byte, DOUBLE er 8 byte og de fungerer på samme måte. Men DOUBLE har bedre presisjon.
DECIMAL (M, N) har en varierende størrelse basert på presisjonsnivået, som kan tilpasses. M er maksimum antall siffer, og N er antall siffer til høyre for desimaltegnet.
For eksempel har DECIMAL (13,4) maksimalt 9 heltallstall og 4 brøkdeltall.
Som navnet antyder, kan vi lagre strenger i disse datatypekolonnene.
CHAR (N) kan holde opptil N tegn, og har en fast størrelse. For eksempel vil CHAR (50) alltid ta 50 tegn på plass, per rad, uavhengig av størrelsen på strengen i den. Det absolutte maksimumet er 255 tegn
VARCHAR (N) virker det samme, men lagringsstørrelsen er ikke løst. N brukes kun til maksimal størrelse. Hvis en streng som er kortere enn N-tegn er lagret, vil det ta så mye plass på harddisken. Den absolutte maksimale størrelsen er 65535 tegn.
Variasjoner av TEKST datatypen er mer egnet for lange stenger. TEXT har en grense på 65535 tegn, MEDIUMTEXT 16,7 millioner tegn og LONGTEXT 4,3 milliarder tegn. MySQL lagrer vanligvis dem på separate steder på serveren, slik at hovedoppbevaring for bordet forblir relativt liten og rask.
DATE lagrer datoer og viser dem i dette formatet 'ÅÅÅÅ-MM-DD', men inneholder ikke tidsinfo. Den har en rekkevidde på 1001-01-01 til 9999-12-31.
DATETIME inneholder både dato og klokkeslett, og vises i dette formatet 'ÅÅÅÅ-MM-DD HH: MM: SS'. Den har et utvalg av '1000-01-01 00:00:00' til '9999-12-31 23:59:59'. Det tar 8 byte plass.
TIMESTAMP fungerer som DATETIME med noen unntak. Det tar bare 4 byte plass og rekkevidden er '1970-01-01 00:00:01' UTC til '2038-01-19 03:14:07' UTC. Så det kan for eksempel ikke være bra å lagre fødselsdatoer.
TIME lagrer bare tiden, og året bare lagrer året.
Det er noen andre datatyper som støttes av MySQL. Du kan se en liste over dem her. Du bør også sjekke lagringsstørrelsene for hver datatype her.
Takk for at du har lest artikkelen. SQL er et viktig språk og et verktøy i webutviklerens arsenal.
Vennligst legg inn dine kommentarer og spørsmål, og ha en flott dag!
Klar til å ta dine ferdigheter til neste nivå, og begynne å dra nytte av dine skript og komponenter? Sjekk ut vår søsters markedsplass, CodeCanyon.
Accentsconagua