Share
Det är viktigt för varje webbutvecklare att vara bekant med databasinteraktioner. I del två av serien fortsätter vi att utforska SQL-språket och tillämpa det vi har lärt oss på en MySQL-databas. Vi kommer att lära oss om index, datatyper och mer komplexa sökstrukturer.
Vänligen se avsnittet "Vad du behöver" i den första artikeln här: SQL för nybörjare (del 1).
Om du vill följa exemplen i den här artikeln på din egen utvecklingsserver gör du följande:
Indexer (eller nycklar) används huvudsakligen för att förbättra hastigheten på datahämtningsoperationer (t.ex. SELECT) på tabeller.
De är en så viktig del i en bra databasdesign, det är svårt att klassificera dem som "optimering". I de flesta fall ingår de i den ursprungliga designen, men de kan också läggas till senare med en ALTER TABLE-fråga.
De vanligaste orsakerna till att indexera databas kolumner är:
Nästan varje tabell borde ha en PRIMARY KEY, i de flesta fall som en INT med alternativet AUTO_INCREMET.
Om du kommer ihåg från den första artikeln skapade vi ett "user_id" -fält i användartabellen och det var en PRIMÄR KEY. På så sätt kan vi i en webbapplikation hänvisa till alla användare med deras id-nummer.
Värdena som lagras i en PRIMARY KEY-kolumn måste vara unika. Dessutom kan det inte finnas mer än en PRIMARY KEY på varje tabell.
Låt oss se en exempelfråga, skapa en tabell för USA-staterna:
CREATE TABLE states (id INT AUTO_INCREMENT PRIMARY KEY, namn VARCHAR (20));
Det kan också skrivas så här:
CREATE TABLE states (id INT AUTO_INCREMENT, namn VARCHAR (20), PRIMARY KEY (id));
Eftersom vi förväntar oss att statens namn ska vara ett unikt värde, bör vi ändra det föregående sökexemplet lite:
CREATE TABLE states (id INT AUTO_INCREMENT, namn VARCHAR (20), PRIMARY KEY (id), UNIQUE (namn));
Som standard namnges indexet efter kolumnnamnet. Om du vill kan du tilldela ett annat namn till det:
CREATE TABLE states (id INT AUTO_INCREMENT, namn VARCHAR (20), PRIMARY KEY (id), UNIQUE state_name (namn));
Nu heter indexet 'state_name' istället för 'name'.
Låt oss säga att vi vill lägga till en kolumn för att representera det år som varje stat gick med.
CREATE TABLE states (id INT AUTO_INCREMENT, namn VARCHAR (20), join_year INT, PRIMARY KEY (id), UNIQUE (namn), INDEX (join_year));
Jag har just lagt till kolumnen join_year och indexerat den. Denna typ av index har inte den unika begränsningen.
Du kan också namnge det KEY istället för INDEX.
CREATE TABLE states (id INT AUTO_INCREMENT, namn VARCHAR (20), join_year INT, PRIMARY KEY (id), UNIQUE (namn), KEY (join_year));
Lägga till ett index minskar prestandan för INSERT och UPDATE-frågor. För varje gång nya data läggs till i tabellen uppdateras indexdata automatiskt, vilket kräver ytterligare arbete. Prestandavinsterna på SELECT-frågorna överväger vanligtvis det här långt. Men lägg till, lägg inte bara till index på varje tabellkolumn utan att tänka på frågorna du kommer att köra.
Innan vi går vidare med fler frågor vill jag skapa en provtabell med viss data.
Detta kommer att vara en lista över amerikanska stater, med deras anslutningsdatum (det datum som staten ratificerade USA: s konstitution eller var med i unionen) och deras nuvarande befolkningar. Du kan kopiera klistra in följande till din MySQL-konsol:
CREATE TABLE states (id INT AUTO_INCREMENT, namn VARCHAR (20), join_year INT, population INT, PRIMARY KEY (id), UNIQUE (namn), KEY (join_year)); INSATTER IN i tillståndsvärden (1, 'Alabama', 1819, 4661900), (2, 'Alaska', 1959, 686293), (3 'Arizona', 1912, 6500180), (4, 'Arkansas', 1836, 2855390 ), (5, "Kalifornien", 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 grupperar de resulterande dataraderna i grupper. Här är ett exempel:
Så vad hände just? Vi har 50 rader i tabellen, men 34 resultat returnerades av denna fråga. Detta berodde på att resultaten var grupperade genom kolumnen "join_year". Med andra ord ser vi bara en rad för varje distinkt värde av join_year. Eftersom vissa stater har samma år, har vi mindre än 50 resultat.
Till exempel fanns det bara en rad för år 1787, men det finns tre stater i den gruppen:
Så det finns tre stater här, men endast Delaware namn visade sig efter GROUP BY-frågan tidigare. Det kan faktiskt ha varit någon av de tre staterna och vi kan inte lita på denna data. Vad är meningen med att använda GROUP BY-klausulen?
Det skulle vara mest värdelöst utan att använda en aggregerad funktion som COUNT (). Låt oss se vad några av dessa funktioner gör och hur de kan få oss några användbara data.
Det här är kanske den vanligaste funktionen tillsammans med GROUP BY-frågor. Den returnerar antalet rader i varje grupp.
Till exempel kan vi använda den för att se antalet stater för varje deltagande år:
Om du använder en GROUP BY-aggregatfunktion och inte anger en GROUP BY-klausul, kommer alla resultat att läggas i en enda grupp.
Antal alla rader i tabellen:
Antal rader som uppfyller en WHERE-klausul:
Dessa funktioner returnerar minimi-, maximi- och medelvärdena:
Denna funktion sammanlänkar alla värden inom gruppen till en enda sträng, med en given separator.
I det första GROUP BY-frågan kunde vi bara se ett statligt namn per år. Du kan använda den här funktionen för att se alla namn i varje grupp:
Om den ändrade bilden är svår att läsa är det här frågan:
VÄLJ GROUP_CONCAT (namn SEPARATOR ','), join_year FRA stater GROUP BY join_year;
Du kan använda detta för att lägga till de numeriska värdena.
På samma sätt som andra programmeringsspråk har SQL lite stöd för kontrollflödet.
Det här är en funktion som tar tre argument. Första argumentet är villkoret, det andra argumentet används om villkoret är sant och det tredje argumentet används om villkoret är felaktigt.
Här är ett mer praktiskt exempel där vi använder den med funktionen SUM ():
SELECT SUM (IF (population> 5000000, 1, 0)) AS big_states, SUM (IF (population <= 5000000, 1, 0) ) AS small_states FROM states;
Det första SUM () -numret räknar antalet stora stater (befolkning över 5 miljoner) och den andra räknar antalet små stater. IF () -samtalet i dessa SUM () samtal returnerar antingen 1 eller 0 baserat på villkoret.
Här är resultatet:
Det här fungerar på samma sätt som säckrapporter som du kanske känner till från programmering.
Låt oss säga att vi vill kategorisera varje stat i en av tre möjliga kategorier.
VÄLJ COUNT (*), CASE WHEN befolkning> 5000000 THEN "big" när befolkningen> 1000000 THEN "medium" ELSE "liten" END AS state_size FROM stater GROUP BY state_size;
Som du kan se kan vi faktiskt GROUP BY värdet returneras från CASE-satsen. Här är vad som händer:
HAVING-klausul tillåter oss att tillämpa villkor på "dolda" fält, såsom de returnerade resultaten av aggregatfunktionerna. Så brukar det användas tillsammans med GROUP BY.
Låt oss exempelvis titta på frågan som vi använde för att räkna antalet stater genom att delta år:
VÄLJ COUNT (*), join_year FRA stater GROUP BY join_year;
Resultatet var 34 rader.
Låt oss dock säga att vi endast är intresserade av rader som har ett tal högre än 1. Vi kan inte använda WHERE-klausulen för detta:
Det här är HÄR som blir användbart:
Tänk på att den här funktionen kanske inte är tillgänglig i alla databassystem.
Det är möjligt att få resultaten från en fråga och använda den för en ny fråga.
I detta exempel får vi staten med den högsta befolkningen:
VÄLJ * FRÅN stater WHERE population = (VÄLJ MAX (befolkning) FRÅN stater);
Den inre frågan kommer att returnera den högsta befolkningen i alla stater. Och den yttre frågan kommer att söka i tabellen igen med det värdet.
Du kanske tror att det här var ett dåligt exempel, och jag är ense om det. Samma fråga kan skrivas mer effektivt på följande sätt:
VÄLJ * FRÅN STATER BESTÄLL AV BYGG DESC GRÄNS 1;
Resultaten i detta fall är desamma, men det finns en viktig skillnad mellan dessa två typer av frågor. Kanske ett annat exempel kan visa det bättre.
I det här exemplet kommer vi att få de sista staterna som gick med i unionen:
VÄLJ * FRÅN STÄLLNINGAR WHERE join_year = (VÄLJ MAX (join_year) FROM states);
Det finns två rader i resultaten denna gång. Om vi hade använt ORDER BY ... LIMIT 1 typ av fråga här hade vi inte fått samma resultat.
Ibland kanske du vill använda flera resultat som returneras av den inre frågan.
Efterfrågan finner år, när flera stater gick med i unionen och returnerar listan över dessa stater:
VÄLJ * FRÅN stater WHERE join_year IN (VÄLJ join_year FRA stater GRUPP BY join_year HAR COUNT (*)> 1) BESTÄLL BY join_year;
Subqueries kan bli ganska komplexa, därför kommer jag inte att få mycket längre in i dem i den här artikeln. Om du vill läsa mer om dem, kolla in MySQL-handboken.
Det är också värt att notera att subqueries ibland kan ha dålig prestanda, så de bör användas med försiktighet.
Med en UNION-fråga kan vi kombinera resultaten av flera SELECT-frågor.
I det här exemplet kombineras stater som börjar med bokstaven "N" och stater med stora populationer:
(VÄLJ * FRÅN LÄNDER VAR NÄR LIKNANDE 'N%') UNION (VÄLJ * FRÅN STÄLLNINGAR WHERE population> 10000000);
Observera att New York är både stor och namnet börjar med bokstaven "N". Men det visas bara en gång eftersom dubbla rader tas bort från resultaten automatiskt.
En annan trevlig sak om UNION är att du kan kombinera frågor på olika tabeller.
Låt oss anta att vi har tabeller för anställda, chefer och kunder. Och varje tabell har ett e-postfält. Om vi vill hämta alla e-postmeddelanden med en enda fråga kan vi köra det här:
(SELECT email FROM anställda) UNION (VÄLJ e-post FRÅN chefer) UNION (VÄLJ email från kunder WHERE subscribed = 1);
Det skulle hämta alla e-postmeddelanden från alla anställda och chefer, men bara e-postmeddelanden till kunder som har prenumererat på att få e-postmeddelanden.
Vi har redan pratat om INSERT-frågan i den senaste artikeln. Nu när vi undersökt databasindex idag kan vi prata om mer avancerade funktioner i INSERT-frågan.
Det här är nästan som ett villkorligt uttalande. Frågan försöker först att utföra en given INSERT, och om den misslyckas på grund av ett duplikatvärde för en PRIMARY KEY eller UNIQUE KEY, utför den en UPDATE istället.
Låt oss först skapa ett testbord.
Det är ett bord för att hålla produkter. Kolumnen "lager" är antalet produkter vi har på lager.
Försök nu lägga in ett dubblettervärde och se vad som händer.
Vi fick ett fel som förväntat.
Låt oss säga att vi fick en ny breadmaker och vill uppdatera databasen, och vi vet inte om det redan finns en rekord för den. Vi kunde kontrollera efter befintliga poster och sedan göra en annan fråga baserad på det. Eller vi kan bara göra allt i en enkel fråga:
Det fungerar precis som INSERT med ett viktigt undantag. Om en duplicerad rad hittas, raderar den först och utför sedan INSERT, så vi får inga felmeddelanden.
Observera att eftersom detta faktiskt är en helt ny rad, ökades id.
Detta är ett sätt att undertrycka dubbletterna, vanligtvis för att förhindra att programmet bryts. Ibland kanske du vill försöka infoga en ny rad och låt den inte misslyckas utan några klagomål om det finns en duplikat.
Inga fel returnerade och inga rader uppdaterades.
Varje tabellkolumn måste ha en datatyp. Hittills har vi använt INT, VARCHAR och DATE-typer men vi pratade inte om dem i detalj. Det finns också flera andra datatyper som vi bör utforska.
Låt oss börja med de numeriska datatyperna. Jag gillar att placera dem i två separata grupper: Heltal vs Non-Integers.
En heltalskolumn kan innehålla endast naturliga nummer (inga decimaler). Som standard kan de vara negativa eller positiva tal. Men om alternativet UNSIGNED är inställt kan det bara innehålla positiva siffror.
MySQL stöder 5 typer av heltal, med olika storlekar och intervall:
Dessa datatyper kan innehålla decimaltal: FLOAT, DOUBLE och DECIMAL.
FLOAT är 4 byte, DOUBLE är 8 byte och de fungerar på samma sätt. Dock har DOUBLE bättre precision.
DECIMAL (M, N) har en varierande storlek baserat på precisionsnivån, som kan anpassas. M är det maximala antalet siffror, och N är siffran till höger om decimaltalet.
Till exempel har DECIMAL (13,4) högst 9 heltal och 4 fraktionstal.
Som namnet antyder kan vi lagra strängar i dessa datatypkolumner.
CHAR (N) kan rymma upp till N tecken och har en fast storlek. Till exempel tar CHAR (50) alltid 50 tecken i mellanslag, per rad, oavsett strängens storlek i den. Det absoluta maximala värdet är 255 tecken
VARCHAR (N) fungerar lika, men lagringsstorleken är inte fixerad. N används endast för maximal storlek. Om en sträng kortare än N tecken lagras kommer det att ta så mycket utrymme på hårddisken. Den absoluta maximala storleken är 65535 tecken.
Variationer i TEXT-datatypen är mer lämpliga för långa strängar. TEXT har en gräns på 65535 tecken, MEDIUMTEXT 16,7 miljoner tecken och LONGTEXT 4,3 miljarder tecken. MySQL lagrar vanligtvis dem på separata platser på servern så att det stora lagret för bordet fortfarande är relativt litet och snabbt.
DATE lagrar datum och visar dem i det här formatet "ÅÅÅÅ-MM-DD" men innehåller inte tidsinfo. Den har ett utbud av 1001-01-01 till 9999-12-31.
DATETIME innehåller både datum och tid och visas i detta format "ÅÅÅÅ-MM-DD HH: MM: SS". Den har ett urval av '1000-01-01 00:00:00' till '9999-12-31 23:59:59'. Det tar 8 byte utrymme.
TIMESTAMP fungerar som DATETIME med några undantag. Det tar bara 4 byte utrymme och intervallet är '1970-01-01 00:00:01' UTC till '2038-01-19 03:14:07' UTC. Så det kan till exempel inte vara bra att lagra födelsedatum.
TIME lagrar bara tiden, och året sparar endast året.
Det finns några andra datatyper som stöds av MySQL. Du kan se en lista över dem här. Du bör också kolla lagringsstorlekarna för varje datatyp här.
Tack för att du läste artikeln. SQL är ett viktigt språk och ett verktyg i webbutvecklarens arsenal.
Vänligen lämna dina kommentarer och frågor, och ha en bra dag!
Klar för att ta dina färdigheter till nästa nivå och börja dra nytta av dina skript och komponenter? Kolla in vår systermarknad, CodeCanyon.
Accentsconagua