Share
Het is belangrijk dat elke webontwikkelaar vertrouwd is met database-interacties. In deel twee van de serie gaan we verder met het verkennen van de SQL-taal en toepassen van wat we hebben geleerd in een MySQL-database. We zullen informatie krijgen over indexen, gegevenstypen en meer complexe querystructuren.
Raadpleeg het gedeelte "Wat u nodig hebt" in het eerste artikel hier: SQL voor beginners (deel 1).
Als u de voorbeelden in dit artikel op uw eigen ontwikkelingsserver wilt volgen, doet u het volgende:
Indexen (of sleutels) worden voornamelijk gebruikt voor het verbeteren van de snelheid waarmee gegevens worden opgehaald (bijvoorbeeld SELECT) in tabellen.
Ze zijn zo'n belangrijk onderdeel van een goed databaseontwerp, dat het moeilijk is ze te classificeren als "optimalisatie". In de meeste gevallen zijn ze opgenomen in het oorspronkelijke ontwerp, maar ze kunnen ook later worden toegevoegd met een ALTER TABLE-query.
De meest voorkomende redenen voor het indexeren van databasekolommen zijn:
Bijna elke tabel zou een PRIMAIRE SLEUTEL moeten hebben, in de meeste gevallen als een INT met de AUTO_INCREMET-optie.
Als je je herinnert uit het eerste artikel, hebben we een veld 'user_id' gemaakt in de tabel met gebruikers en het was een PRIMAIRE SLEUTEL. Op deze manier kunnen we in een webapplicatie naar alle gebruikers verwijzen door hun id-nummers.
De waarden die zijn opgeslagen in een PRIMARY KEY-kolom moeten uniek zijn. Er kan ook niet meer dan één PRIMAIRE SLEUTEL op elke tafel staan.
Laten we een voorbeeldquery bekijken, een tabel maken voor de lijst met VS-staten:
CREATE TABLE-statussen (ID INT AUTO_INCREMENT PRIMARY KEY, naam VARCHAR (20));
Het kan ook zo worden geschreven:
CREATE TABLE-statussen (ID INT AUTO_INCREMENT, naam VARCHAR (20), PRIMARY KEY (id));
Aangezien we verwachten dat de naam van de staat een unieke waarde is, moeten we het voorbeeld van de vorige query een beetje wijzigen:
CREATE TABLE-statussen (ID INT AUTO_INCREMENT, naam VARCHAR (20), PRIMARY KEY (id), UNIQUE (naam));
Standaard heeft de index de naam van de kolomnaam. Als u wilt, kunt u er een andere naam aan toewijzen:
CREATE TABLE-statussen (ID INT AUTO_INCREMENT, naam VARCHAR (20), PRIMARY KEY (id), UNIQUE state_name (name));
De index heeft nu de naam 'staat_naam' in plaats van 'naam'.
Laten we zeggen dat we een kolom willen toevoegen die het jaar vertegenwoordigt waarin elke staat is toegetreden.
CREATE TABLE-statussen (ID INT AUTO_INCREMENT, naam VARCHAR (20), join_jaar INT, PRIMARY KEY (id), UNIQUE (naam), INDEX (join_year));
Ik heb zojuist de kolom join_year toegevoegd en deze geïndexeerd. Dit type index heeft niet de uniciteitsbeperking.
U kunt het ook KEY noemen in plaats van INDEX.
CREATE TABLE-statussen (ID INT AUTO_INCREMENT, naam VARCHAR (20), join_jaar INT, PRIMARY KEY (id), UNIQUE (naam), KEY (join_year));
Het toevoegen van een index vermindert de prestaties van INSERT- en UPDATE-query's. Omdat telkens wanneer nieuwe gegevens aan de tabel worden toegevoegd, de indexgegevens ook automatisch worden bijgewerkt, wat extra werk vereist. De prestatiewinst op de SELECT-vragen weegt meestal ver op. Maar toch, voeg niet alleen indexen toe aan elke tabelkolom zonder na te denken over de zoekopdrachten die u gaat uitvoeren.
Voordat we verder gaan met meer vragen, zou ik graag een voorbeeldtabel willen maken met wat gegevens.
Dit is een lijst van Amerikaanse staten met hun toetredingsdatums (de datum waarop de staat de grondwet van de Verenigde Staten heeft geratificeerd of is toegelaten tot de Unie) en hun huidige populaties. U kunt het volgende kopiëren naar uw MySQL-console:
CREATE TABLE-statussen (ID INT AUTO_INCREMENT, naam VARCHAR (20), join_year INT, populatie INT, PRIMARY KEY (id), UNIQUE (naam), KEY (join_year)); INSERT INTO staten VALUES (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), (13 , 'Illinois', 1818, 12901563), (14, 'Indiana', 1816, 6376792), (15, 'Iowa', 1846, 3002555), (16, 'Kansas', 1861, 2802134), (17, ' Kentucky ', 1792, 4269245), (18,' Louisiana ', 1812, 4410796), (19,' Maine ', 1820, 1316456), (20,' Maryland ', 1788, 5633597), (21,' Massachusetts ' , 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) ;
De GROUP BY-clausule groepeert de resulterende gegevensrijen in groepen. Hier is een voorbeeld:
Dus wat is er net gebeurd? We hebben 50 rijen in de tabel, maar 34 resultaten zijn door deze query geretourneerd. Dit komt omdat de resultaten zijn gegroepeerd op de kolom 'join_year'. Met andere woorden, we zien slechts één rij voor elke afzonderlijke waarde van join_year. Aangezien sommige staten hetzelfde join_year hebben, hebben we minder dan 50 resultaten.
Er was bijvoorbeeld slechts één rij voor het jaar 1787, maar er zijn 3 staten in die groep:
Er zijn dus drie staten hier, maar alleen de naam van Delaware verscheen na de eerdere zoekopdracht GROUP BY. Eigenlijk had het een van de drie staten kunnen zijn en we kunnen niet op dit stuk gegevens vertrouwen. Wat heeft het dan voor zin om de GROUP BY-component te gebruiken?
Het zou meestal nutteloos zijn zonder een verzamelfunctie zoals COUNT () te gebruiken. Laten we eens kijken wat sommige van deze functies doen en hoe ze ons nuttige gegevens kunnen bezorgen.
Dit is misschien de meest gebruikte functie samen met GROUP BY-query's. Het geeft het aantal rijen in elke groep terug.
We kunnen het bijvoorbeeld gebruiken om het aantal staten voor elk join_jaar te bekijken:
Als u een GROUP BY-aggregatiefunctie gebruikt en geen GROUP BY-component opgeeft, worden de volledige resultaten in één groep geplaatst.
Aantal van alle rijen in de tabel:
Aantal rijen dat voldoet aan een WHERE-clausule:
Deze functies retourneren de minimum-, maximum- en gemiddelde waarden:
Met deze functie worden alle waarden in de groep samengevoegd tot een enkele tekenreeks, met een gegeven scheidingsteken.
In het eerste GROUP BY-queryvoorbeeld konden we maar één naam per jaar zien. U kunt deze functie gebruiken om alle namen in elke groep te bekijken:
Als de gewijzigde afbeelding moeilijk te lezen is, is dit de zoekopdracht:
SELECT GROUP_CONCAT (naam SEPARATOR ','), join_year FROM states GROUP BY join_year;
U kunt dit gebruiken om de numerieke waarden bij elkaar op te tellen.
Net als andere programmeertalen heeft SQL enige ondersteuning voor de besturingsstroom.
Dit is een functie die drie argumenten vereist. Eerste argument is de voorwaarde, tweede argument wordt gebruikt als de voorwaarde waar is en het derde argument wordt gebruikt als de voorwaarde onwaar is.
Hier is een meer praktisch voorbeeld waarbij we het gebruiken met de functie SUM ():
SELECT SUM (IF (populatie> 5000000, 1, 0)) AS big_states, SUM (IF (population <= 5000000, 1, 0) ) AS small_states FROM states;
De eerste SUM () -aanroep telt het aantal grote staten (populatie meer dan 5 miljoen) en de tweede telt het aantal kleine toestanden. De aanroep IF () binnen deze SUM () -aanroepen retourneert 1 of 0 op basis van de voorwaarde.
Hier is het resultaat:
Dit werkt vergelijkbaar met de switch-case statements die u misschien kent uit de programmering.
Laten we zeggen dat we elke staat willen indelen in een van de drie mogelijke categorieën.
SELECTEER COUNT (*), CASE WANNEER populatie> 5000000 DAN 'GROOT' WANNEER bevolking> 1000000 DAN 'gemiddeld' ANDERS 'klein' END ALS state_size FROM states GROUP BY state_size;
Zoals u kunt zien, kunnen we GROUP BY BY de waarde teruggeven uit de CASE-instructie. Dit is wat er gebeurt:
De clausule HAV stelt ons in staat om voorwaarden toe te passen op 'verborgen' velden, zoals de geretourneerde resultaten van geaggregeerde functies. Het wordt dus meestal samen met GROUP BY gebruikt.
Laten we bijvoorbeeld de query bekijken die we hebben gebruikt voor het tellen van het aantal staten door lid te worden van het jaar:
SELECT COUNT (*), join_year FROM states GROUP BY join_year;
Het resultaat was 34 rijen.
Laten we echter stellen dat we alleen geïnteresseerd zijn in rijen met een telling hoger dan 1. We kunnen de WHERE-component hiervoor niet gebruiken:
Dit is waar HAVING nuttig wordt:
Houd er rekening mee dat deze functie mogelijk niet beschikbaar is in alle databasesystemen.
Het is mogelijk om de resultaten van één zoekopdracht op te halen en deze voor een andere query te gebruiken.
In dit voorbeeld krijgen we de staat met de hoogste populatie:
SELECT * FROM states WHERE population = (SELECT MAX (population) FROM states);
De innerlijke query retourneert de hoogste populatie van alle staten. En de buitenste query zal opnieuw de tabel doorzoeken met behulp van die waarde.
Je zou kunnen denken dat dit een slecht voorbeeld was, en ik ben het daar enigszins mee eens. Dezelfde zoekopdracht kan efficiënter worden geschreven als volgt:
SELECT * FROM states ORDER BY-populatie DESC LIMIT 1;
De resultaten in dit geval zijn hetzelfde, maar er is een belangrijk verschil tussen deze twee soorten zoekopdrachten. Misschien zal een ander voorbeeld dat beter laten zien.
In dit voorbeeld krijgen we de laatste staten die lid zijn geworden van de Unie:
SELECT * FROM-toestanden WHERE join_year = (SELECT MAX (join_year) FROM states);
Er zijn deze keer twee rijen in de resultaten. Als we het type ORDER BY ... LIMIT 1-type query hier hadden gebruikt, hadden we niet hetzelfde resultaat ontvangen.
Soms wilt u misschien meerdere resultaten gebruiken die door de innerlijke query zijn geretourneerd.
Na het zoeken naar de jaren, wanneer meerdere staten lid zijn geworden van de Unie, wordt de lijst met die staten geretourneerd:
SELECT * FROM-staten WHERE join_year IN (SELECT join_year FROM states GROUP BY join_jaar TELLING HALEN (*)> 1) BESTELLING DOOR join_year;
Subquery's kunnen behoorlijk complex worden, daarom kom ik er in dit artikel niet veel verder op in. Als je meer over ze wilt lezen, bekijk dan de MySQL-handleiding.
Het is ook vermeldenswaard dat subquery's soms slechte prestaties kunnen hebben, dus moeten ze voorzichtig worden gebruikt.
Met een UNION-query kunnen we de resultaten van meerdere SELECT-query's combineren.
Dit voorbeeld combineert staten die beginnen met de letter 'N' en staten met grote populaties:
(SELECT * FROM states WHERE name LIKE 'n%') UNION (SELECT * FROM states WHERE population> 10000000);
Let op: New York is zowel groot als de naam begint met de letter 'N'. Maar het wordt maar één keer weergegeven omdat dubbele rijen automatisch uit de resultaten worden verwijderd.
Een ander aardig ding over UNION is dat u query's op verschillende tabellen kunt combineren.
Laten we aannemen dat we tabellen hebben voor werknemers, managers en klanten. En elke tabel heeft een e-mailveld. Als we alle e-mails met één enkele query willen ophalen, kunnen we dit uitvoeren:
(SELECT e-mail van werknemers) UNION (SELECT e-mail van managers) UNION (SELECT e-mail van klanten WAAR geabonneerd = 1);
Het zou alle e-mails van alle werknemers en managers ophalen, maar alleen de e-mails van klanten die zich hebben ingeschreven om e-mails te ontvangen.
We hebben het al gehad over de INSERT-vraag in het laatste artikel. Nu we vandaag de database-indexen hebben verkend, kunnen we praten over meer geavanceerde functies van de INSERT-query.
Dit is bijna als een voorwaardelijke verklaring. De query probeert eerst een bepaalde INSERT uit te voeren en als deze mislukt vanwege een dubbele waarde voor een PRIMARY KEY of UNIQUE KEY, voert deze in plaats daarvan een UPDATE uit.
Laten we eerst een testtafel maken.
Het is een tafel om producten te houden. De kolom 'aandelen' is het aantal producten dat we op voorraad hebben.
Probeer nu een dubbele waarde in te voegen en te zien wat er gebeurt.
We hebben een foutmelding gekregen zoals verwacht.
Laten we zeggen dat we een nieuwe broodmachine hebben ontvangen en de database willen bijwerken, en we weten niet of er al een record voor is. We kunnen controleren of er al bestaande records zijn en vervolgens op basis daarvan een nieuwe zoekopdracht uitvoeren. Of we kunnen het gewoon allemaal in één simpele query doen:
Dit werkt precies zoals INSERT met één belangrijke uitzondering. Als een dubbele rij wordt gevonden, wordt deze eerst verwijderd en wordt vervolgens de INSERT uitgevoerd, zodat we geen foutmeldingen krijgen.
Merk op dat omdat dit eigenlijk een geheel nieuwe rij is, de id is verhoogd.
Dit is een manier om de dubbele fouten te onderdrukken, meestal om te voorkomen dat de toepassing breekt. Soms wilt u misschien proberen een nieuwe rij in te voegen en deze zonder enige klachten te laten mislukken in het geval er een duplicaat is gevonden.
Geen fouten geretourneerd en geen rijen bijgewerkt.
Elke tabelkolom moet een gegevenstype hebben. Tot nu toe hebben we de typen INT, VARCHAR en DATE gebruikt, maar we hebben er niet in detail over gesproken. Er zijn ook verschillende andere gegevenstypen die we moeten verkennen.
Laten we eerst beginnen met de numerieke gegevenstypen. Ik plaats ze graag in twee afzonderlijke groepen: gehele getallen versus niet-integers.
Een kolom met een geheel getal kan alleen natuurlijke getallen bevatten (geen decimalen). Standaard kunnen dit negatieve of positieve getallen zijn. Maar als de UNSIGNED-optie is ingesteld, kan deze alleen positieve cijfers bevatten.
MySQL ondersteunt 5 soorten gehele getallen, met verschillende grootten en bereiken:
Deze gegevenstypes kunnen decimale getallen bevatten: FLOAT, DUBBEL en DECIMAAL.
FLOAT is 4 bytes, DOUBLE is 8 bytes en ze werken op dezelfde manier. DUBBEL heeft echter een betere precisie.
DECIMAL (M, N) heeft een variërende grootte op basis van het precisieniveau, dat kan worden aangepast. M is het maximale aantal cijfers en N is het aantal cijfers rechts van de komma.
DECIMAL (13,4) heeft bijvoorbeeld een maximum van 9 integer cijfers en 4 decimalen.
Zoals de naam al doet vermoeden, kunnen we tekenreeksen opslaan in deze gegevenstypekolommen.
CHAR (N) kan maximaal N tekens bevatten en heeft een vaste grootte. CHAR (50) kost bijvoorbeeld altijd 50 tekens ruimte, per rij, ongeacht de grootte van de reeks erin. Het absolute maximum is 255 tekens
VARCHAR (N) werkt hetzelfde, maar de opslaggrootte is niet vast. N wordt alleen gebruikt voor de maximale grootte. Als een string korter dan N tekens wordt opgeslagen, heeft deze veel minder ruimte nodig op de harde schijf. De absolute maximale grootte is 65535 tekens.
Variaties van het gegevenstype TEXT zijn meer geschikt voor lange reeksen. TEXT heeft een limiet van 65535 tekens, MEDIUMTEXT 16,7 miljoen tekens en 4,3 miljard LONGTEXT-tekens. MySQL slaat ze meestal op afzonderlijke locaties op de server op, zodat de hoofdopslag voor de tabel relatief klein en snel blijft.
DATE slaat datums op en geeft ze weer in dit formaat 'JJJJ-MM-DD' maar bevat geen tijdinformatie. Het heeft een bereik van 1001-01-01 tot 9999-12-31.
DATETIME bevat zowel de datum als de tijd en wordt weergegeven in de notatie 'JJJJ-MM-DD UU: MM: SS'. Het heeft een bereik van '1000-01-01 00:00:00' tot '9999-12-31 23:59:59'. Er zijn 8 bytes ruimte nodig.
TIMESTAMP werkt zoals DATETIME met een paar uitzonderingen. Er zijn slechts 4 bytes ruimte nodig en het bereik is '1970-01-01 00:00:01' UTC tot '2038-01-19 03:14:07' UTC. Het is bijvoorbeeld niet altijd goed om geboortedata op te slaan.
TIME slaat alleen de tijd op en YEAR slaat alleen het jaar op.
Er zijn enkele andere gegevenstypen die worden ondersteund door MySQL. Je kunt hier een lijst met hen bekijken. Bekijk hier ook de opslagformaten van elk gegevenstype.
Bedankt voor het lezen van het artikel. SQL is een belangrijke taal en een hulpmiddel in het arsenaal van webontwikkelaars.
Laat uw opmerkingen en vragen achter en geniet van een geweldige dag!
Klaar om je vaardigheden naar een hoger niveau te tillen, en te profiteren van je scripts en componenten? Bekijk onze zustermarktplaats, CodeCanyon.
Accentsconagua