Tutorial ANSI SQL-92

Inhoud

SQL leren

SQL is een taal waarmee met databases wordt gecommuniceerd. Met alle databases! Daarvoor is een standaard ontwikkeld waaraan alle databaseontwikkelaars zich in principe moeten houden. Deze standaard is ANSI SQL dat staat voor American National Standardisation Institute - Structured Query Language.

ANSI SQL-92 is de huidige standaard die wordt gebruikt.

MySQL is een database type, net zoals acces, MSSQL (= SQL SERVER), DB2, PostgreSQL, Oracle, Ingress, Informix, Whatcom, Paradox etc.

Ieder databasetype behoort dus te communiceren via ANSI SQL. Dit is in principe ook zo, maar toch wijken de database types af. Dit komt doordat er bij bepaalde databases functies en commando's werken die bij andere databasetypes niet werken. Bijvoorbeeld de functie auto_increment bij MySQL. Dit is een functie van MySQL en daarmee kan via SQL (de taal) gecommuniceerd worden. Probeer je dezelfde query met auto_increment op een andere databasetype, dan werkt het niet omdat die database de hele functie niet kent. Dus kan het zijn dat de SQL waarmee je met database 1 communiceert, niet werkt voor database 2.

Voor de duidelijkheid: MySQL is geen taal, maar een databasetype die communiceert via SQL.

De taal SQL kan onderverdeeld worden in een aantal subtalen. De belangrijkste zijn :

DML: Data Manipulation Language. Dit is het gedeelte van SQL waarbij je gegevens in de database toevoegt, gegevens uit de database opvraagt, gegevens in de database bewerkt.

DDL: Data Defenition Language. Met dit gedeelte maak en bewerk je de structuur van de database en de databasetabellen.

De commando's die gebruikt worden heten query's. De gebruikte query's zijn getest op een MySQL database en een MSSQL database. In deze tutorial wordt uitsluitend het gebruik van DML uitgelegd. In een volgende tutorial komt DDL aan de orde

Als je wilt zoeken in deze tutorial dan kan dat met behulp van CTRL F.

DML: het opvragen van gegevens

Selecteren

Het opvragen van gegevens gebeurt met het SELECT commando. De meest eenvoudige SELECT query ziet er zo uit:

Stel dat dit één van de tabellen uit de database is (gegevens zijn fictief. Tabel is niet genormaliseerd.).
Deze tabel noemen we leden:

Als we de namen uit de tabel willen selecteren krijgen we de volgende query:

We krijgen dan als resultaat:

Als we de namen en het aantal berichten uit de tabel willen selecteren krijgen we de volgende query:

We krijgen dan als resultaat:

Als we de functies uit de tabel willen selecteren krijgen we de volgende query:

We krijgen dan als resultaat:

Je ziet dat sommige functies meerdere keren voorkomen. Dat komt omdat we in de query gezet hebben dat wel alle resultaten willen. Om te voorkomen dat we dubbele resultaten krijgen gebruiken we het commando DISTINCT . De query komt er dan als volgt uit te zien:

We krijgen dan als resultaat:

Als je alle gegevens uit alle kolommen wilt halen kun je of alle kolommen noemen in je query, dus:

Of je maakt gebruik van de asteriks (*). De query komt er dan zo uit te zien:

Sorteren

Gegevens uit een tabel kunnen ook gesorteerd opgevraagd worden. Om te sorteren gebruiken we het commando ORDER BY . Het sorteren kan op 2 manieren; oplopend en aflopend. In het Engels is dat ascending en descending. In SQL wordt dat afgekort naar de commando's ASC en DESC
Stel dat je de namen uit de tabel wilt hebben maar dan gesorteerd op het aantal berichten met de persoon met het hoogste bericht bovenaan. De query komt er dan zo uit te zien:

Die geeft het resultaat:

Resultaten worden in principe altijd oplopend getoond. Je kunt het commando ASC dus eigenlijk weglaten. Een alternatieve query waarbij je ook bovenstaand resultaat krijgt is deze:

De 2 in deze query staat voor het tweede 'te selecteren' item in de query, oftwel de berichten.

Ook is het mogelijk om random te selecteren. Dit is niet echt ordenen, want het is random, maar het is wel een makkelijke functie. Je kunt gebruik maken van de functie RAND() om random te selecteren

Criterium

Als je bepaalde rijen wilt selecteren die moeten voldoen aan 1 of meerdere voorwaarden, of juist niet moeten voldoen aan 1 of meerdere voorwoorden gebruiken we het commando WHERE. In onderstaand voorbeeld gaan we de namen selecteren van iedereen die de functie Moderator heeft. De query wordt dan:

We krijgen dan dit resultaat:

De basis syntax voor de query is:

Dit criterium is opgebouwd uit 3 onderdelen:

kolomnaam operator veldwaarde

De volgende operators kunnen in SQL gebruikt worden:

Criteria

Als je resultaten wilt hebben die aan meerdere criteria moeten voldoen kun je gebruik maken van de commando's AND, OR en NOT .

Stel dat we alle namen willen hebben van de mensen die de leeftijd van 20 jaar of ouder hebben en niet de functie van Admin hebben. De query komt er dan zo uit te zien:

De resultaten zullen dan zijn:

Als we allen namen willen hebben van de mensen die de leeftijd hebben van 18 jaar of meer dan 1000 berichten hebben krijgen we onderstaande query:

Dit geeft de resultaten:

Je ziet dat Anouk en Jasper_van geen 18 zijn en toch worden geselecteerd. Dat komt omdat zij wel aan het tweede criterium voldoen. Ze hebben namelijk meer dan 1000 berichten.

Met NOT kun je een bewering omdraaien in de query. Stel dat we alle rijen willen hebben waar de woonplaats niet Parijs is. Een van de query's die dit resultaat oplevert is:

Dit resultaat kun je ook krijgen door deze query te gebruiken:

Het is maar net wat je makkelijker vind.

Aantal rijen limiteren in MySQL

Je kunt ook het aantal rijen dat je selecteert beperken (limiteren) door gebruik te maken van het commando LIMIT . Stel dat je de naam van de persoon wilt hebben die de meeste berichten hebt. We moeten dan een query maken die sorteert op berichten en slechts 1 rij selecteert. De query komt er dan zo uit te zien:

Totaan LIMIT 1 zul je de query moeten begrijpen, dat is allemaal behandeld. LIMIT 1 geeft aan dat de query moet stoppen na het 1e resultaat. De query ordent eerst alle rijen naar het aantal berichten aflopend, vervolgens pakt hij de 1e rij daarvan.

Stel dat we alleen de namen willen weten van de eerste 3 personen met de meeste berichten. Dan moeten we een extra parameter opgeven zodat de query weet dat er maar 3 resultaten gewenst zijn. De query komt er dan zo uit te zien:

Je ziet dat er nu na LIMIT 2 getallen staan. Het eerste getal, de 0, staat voor de 1e rij. Het tellen begint in SQL bij 0. Het tweede getal is het aantal rijen dat geselecteerd moet worden. In dit geval 3 dus. Bovenstaande query geeft als resultaat:

Als we alleen de namen willen weten van de personen die op 1 en op 2 na de meeste berichten hebben wordt de query als volgt:

Dit geeft als resultaat:

Het gebruik van LIMIT is geen ANSI-SQL, maar een functie van MySQL. Dit zal dus waarschijnlijk niet op een ander databasetype werken.

Aantal rijen limiteren in MSSQL

MSSQL kent het LIMIT gebeuren helaas niet. Maar kent wel een soortgelijk commando. In MSSQL kun je gebruik maken van TOP. Dit heeft echter wel z'n beperkingen omdat je niet midden in een tabel kunt selecteren zoals met LIMIT in MySQL wel. Als je de eerste 5 entries wilt hebben kun je deze query gebruiken in MSSQL:

Dit geeft het resultaat:

Om resultaten te krijgen die midden in een tabel zetten zul je een wat geavanceerdere query moeten maken. Daar ga ik nu niet op in. Dit komt later nog aan bod. Het gebruik van TOP is geen ANSI-SQL, maar een functie van MSSQL. Dit zal dus waarschijnlijk niet op een ander databasetype werken.

Kolommen hernoemen met AS

Bij query's in scripts zie je heel vaak dat bepaalde kolommen hernoemt worden met behulp van AS. Hieronder ga ik in op vragen als: hoe werkt dat? En waarom wordt dat gebruikt?

Stel dat je een query gebruikt zoals deze:

Je wilt de gemiddelde leeftijd berekenen, afgerond op 1 decimaal. Je voert de query uit in je script en wilt de resultaten in een while loop binnenhalen. Maar hoe heet nu het veld dat je nodig hebt om de resultaten weer te geven? Die naam kun je zelf geven door het resultaat in de query al een naam te geven. In dit geval noemen we het veld gem, van gemiddeld. De query komt er dan zo uit te zien:

Het hernoemen van kolommen wordt vooral gebruikt wanneer query's lang en onoverzichtelijk worden. Als je voor jezelf duidelijke namen geeft aan resultaten werkt dat een stuk makkelijker. Later bij het gebruik van meerdere tabellen door elkaar (JOINS en subquery's) en bij functies zul je zien dat AS heel makkelijk is.

Functies in SQL

De taal SQL kent verscheidene functies die gebruikt kunnen worden om de data in de database te manipuleren. Het aanroepen van functies in SQL lijkt op het aanroepen van functies in programmeertalen zoals C++, PHP en JavaScript. Eerst komt de functienaam, gevolgd door een haakje openen, parameters, haakje sluiten, oftewel:

functie(parameters)

Hieronder zal ik kort een paar belangrijke functies bespreken en er voorbeelden bij zetten. Een aantal functies in SQL kunnen gebruikt worden in het te selecteren gedeelte maar ook in als criterium.

• ROUND(getal, aantal decimalen) : met ROUND kun je getallen afronden op een door je zelf op te geven aantal decimalen.

  SELECT ROUND(2.5628374,3) ;

  geeft als resultaat : 2.563

• LEN(veldnaam) : LEN geeft het aantal tekens terug dat gebruikt is in een kolom.

  SELECT LEN(functie) AS tekens FROM leden ;

  geeft als resultaat :

  tekens
  5
  5
  9
  9
  9
  9
  9
  8

  Admin is nl. 5 karakters (= tekens in dit geval), Moderator is 9 tekens en Reporter = 8 tekens.
  Let op! Spaties en leestekens tellen ook mee als teken! In MySQL wordt gebruik gemaakt van de functie LENGTH() in plaats van LEN()

• UCASE(veldnaam) : UCASE geeft het resultaat terug in Hoofdletters.

  SELECT UCASE(naam) AS hoofdletters FROM leden ;

  geeft als resultaat:

  hoofdletters

  ANOUK

  LEEJOO

  HANS

  FEPPIE

  JIM

  FLANCE

  DONNY_NL

  JASPER_VAN

  Deze functie is ook te gebruiken in het criteriumdeel van een query. Zo geeft de query:

  SELECT id, naam, leeftijd FROM leden WHERE UCASE(naam) = 'LEEJOO' ;

  het volgende resultaat:

  id	naam	leeftijd
  2	Leejoo	30

  Let op! Deze functie werkt niet in MSSQL!

• LCASE(veldnaam) : LCASE geeft het resultaat terug in kleine letters.

  SELECT LCASE(naam) AS kleine_letters FROM leden ;

  geeft als resultaat:

  kleine_letters

  anouk

  leejoo

  hans

  feppie

  jim

  flance

  donny_nl

  Jasper_van

  Deze functie is ook te gebruiken in het criterium deel van een query en is daardoor heel geschikt voor bijvoorbeeld een logincontrole. Zo geeft de query:

  SELECT id, naam, leeftijd FROM leden WHERE LCASE(naam) = 'anouk' ;

  dit resultaat:

  id	naam	leeftijd
  1	Anouk	20

  Let op! Deze functie werkt niet in MSSQL!

• CONCAT(veldnaam 1,veldnaam 2) : de functie CONCAT kan 2 velden met elkaar verbinden en desgewenst een string toevoegen in het resultaat.

  SELECT CONCAT(naam," ",woonplaats) AS combi FROM leden ;

  geeft dit resultaat

  combi

  Anouk Jamaica

  Leejoo Londen

  Hans Parijs

  Feppie New York

  Jim Oegstgeest

  Flance Bermuda

  Donny_nl Rio

  jasper_van Parijs

  Als je bijvoorbeeld een komma tussen de naam en woonplaats wilt hebben krijg je de volgende query:

  SELECT CONCAT(naam,", ",woonplaats) AS combi FROM leden ;

  dit geeft als resultaat:

  combi

  Anouk, Jamaica

  Leejoo, Londen

  Hans, Parijs

  Feppie, New York

  Jim, Oegstgeest

  Flance, Bermuda

  Donny_nl, Rio

  jasper_van, Parijs

  Let er wel op dat een string tussen een dubbele quote komt te staan zodat de query dit stukje herkent als string.
  Let op! Deze functie werkt niet in MSSQL en is geen ANSI SQL-92 maar ANSI SQL-96!

• LEFT(veldnaam,aantal tekens) : met LEFT kun je het aantal tekens uit een kolom selecteren.

  SELECT LEFT(functie,3) AS eerste3 FROM leden ;

  geeft als resultaat:

  eerste3

  Adm

  Adm

  Mod

  Mod

  Mod

  Mod

  Mod

  Rep

  Als we deze functie in het criterium deel van de query gebruiken zoals in deze query:

  SELECT * FROM leden WHERE LEFT(functie,3) = 'Mod' ;

  krijgen we dit resultaat:

  id	naam	leeftijd	woonplaats	functie	berichten	geboren
  3	Hans	18	Parijs	Moderator	627	1984-11-05
  4	Feppie	18	New York	Moderator	1084	1984-09-09
  5	Jim	25	Oegstgeest	Moderator	565	1980-02-01
  6	Flance	18	Bermuda	Moderator	3255	1985-01-30
  7	Donny_nl	27	Rio	Moderator	834	1977-05-31

  Let op! In MSSQL werkt dit niet op velden van het type TEXT!

• RIGHT(veldnaam, aantal tekens) : idem aan de functie LEFT alleen bij RIGHT wordt er geteld vanaf de rechterkant.

  SELECT RIGHT(functie,3) AS laatste3 FROM leden ;

  geeft als resultaat:

  laatste3

  min

  min

  tor

  tor

  tor

  tor

  tor

  ter

  Ook deze functie kun je in het criterium deel van de query gebruiken. Zo geeft de query:

  SELECT functie FROM leden WHERE RIGHT(functie,3) = 'tor' ;

  Dit resultaat:

  functie

  Moderator

  Moderator

  Moderator

  Moderator

  Moderator

  Let op! In MSSQL werkt dit niet op velden van het type TEXT!

• REPLACE('string','te vervangen','vervangen door') : de functie REPLACE (niet te verwarren met het commando REPLACE!) kan delen in een string vervangen. Als we in een string bijvoorbeeld het teken = willen vervangen door de letters is, krijgen we de volgende query:

  SELECT REPLACE('twee plus twee = 4', '=', 'is') ;

  dit geeft het resultaat: twee plus twee is 4

• INSTR(veldnaam, "karakter") : met INSTR kun je de 1e positie bepalen van een bepaald karakter. Als het teken niet voorkomt in de string dan is het resultaat van die rij 0.

  SELECT INSTR(functie,"o") AS positie FROM leden ;

  geeft als resultaat:

  positie
  0
  0
  2
  2
  2
  2
  2
  4

  Ook deze functie kun je in het criterium deel van de query gebruiken. Met de query:

  SELECT naam FROM leden WHERE INSTR(functie,"o") = 2 ;

  krijgen we het resultaat:

  naam

  Hans

  Feppie

  Jim

  Flance

  Donny_nl

  Let op! Deze functie werkt niet in MSSQL!

• COUNT(veldnummer of veldnaam) : Met COUNT kun je heel snel het aantal rijen tellen in de tabel. Als argument kun je de naam van de kolom gebruiken of een getal dat overeenkomt met de kolom. Zo geven onderstaande query's hetzelfde resultaat:

  SELECT COUNT(naam) FROM leden ;

  SELECT COUNT(1) FROM leden ;

  Resultaat van deze query's is bij allebei 8. De kolom naam is de 2e kolom uit de tabel en dat staat gelijk met kolomnummer 1.

• MIN(veldnaam) : Met MIN kun je het laagste getal vinden bij een numeriek type of de entry die begint met de eerste letter uit het alfabet bij een tekstuele kolom. Zo geeft onderstaande query:

  SELECT MIN(naam) FROM leden ;

  als resultaat: Anouk
  Anouk begint nl. met een A en dat is de eerste letter van het alfabet. Bij onderstaande query selecteren we de laagste leeftijd:

  SELECT MIN(leeftijd) FROM leden ;

  Dit geeft als resultaat: 18
  Let op! Bij MSSQL werkt MIN alleen op numerieke kolommen!

• MAX(veldnaam) : Met MAX kun je het hoogste getal vinden bij een numeriek type of de entry die begint met de laatste letter uit het alfabet bij een tekstuele kolom. Zo geeft onderstaande query:

  SELECT MAX(naam) FROM leden ;

  als resultaat: Jasper_van
  omdat zijn naam met de R begint. Dit is in deze tabel de laagste letter waar een naam mee begint. Bij onderstaande query selecteren we de hoogste leeftijd:

  SELECT MAX(leeftijd) FROM leden ;

  dit geeft als resultaat: 30
  Let op! Bij MSSQL werkt MAX alleen op numerieke kolommen!

• SUM(veldnaam) : Met SUM kun je alle waarden uit een kolom bij elkaar optellen. Voorwaarde is wel dat het type kolom een numerieke is. De totale leeftijd van de tabel is:

  SELECT SUM(leeftijd) FROM leden ;

  resultaat: 177

• AVG(veldnaam) : AVG geeft het gemiddelde (averidge) van een kolom. Voorwaarde is ook hier dat het type kolom numeriek is.

  SELECT AVG(leeftijd) FROM leden ;

  geeft als resultaat : 22.1250

Stel dat we de gemiddelde leeftijd willen weten van deze groep, afgerond op 1 decimaal. Dan kun je de functies binnen functies gebruiken en krijg je een query zoals deze:

Het resultaat van deze query is: 22.1

Later in deze tutorial wordt expliciet in gegaan op de functie DATE. Deze is nl. erg belangrijk en kent vele mogelijkheden.

Berekeningen in SQL

Het is mogelijk om direct in de query berekeningen uit te voeren met waarden. Dit is echter wel beperkt tot het gebruik van 4 operators;

+ : optellen
- : aftrekken
/ : delen
* : vermenigvuldigen

Rekenen kan uiteraard alleen met getallen en niet met tekst. Stel dat we van iedereen uit de tabel de helft van het aantal berichten willen weten. De query daarvoor is:

Dit resulteert in:

Stel dat we een derde willen hebben van de som van de berichten en de leeftijd afgerond op 1 decimaal. Dan zou dus de berekening zijn: (berichten + leeftijd) / 3 . In de query wordt dit dan:

Dit geeft de resultaten:

Tot zover de berekeningen.

Nog meer handige operators

• Met BETWEEN kun je rijen opvragen die tussen 2 waarden liggen. Stel dat we alle namen willen van de personen die de leeftijd hebben tussen de 20 en de 25. We krijgen dan de volgende query:

  SELECT naam FROM leden WHERE leeftijd BETWEEN 20 AND 25 ;

  dit geeft de resultaten:

  naam

  Anouk

  Jim

  Jasper_van

• Met het commando IN kunnen een query verkorten. Stel dat we een bepaald aantal personen met een bepaalde leeftijd moeten hebben. We zouden dan een query kunnen maken met heel veel OR 's, maar ook een kortere en duidelijkere query met IN. Stel dat we alle namen van personen met als leeftijd 18, 21, 25 en 27 willen hebben. We kunnen dan de volgende query maken:

  SELECT naam FROM leden WHERE leeftijd = '18' OR leeftijd = '21' OR leeftijd = '25' OR leeftijd = '27' ;

  Deze query is met IN als volgt:

  SELECT naam FROM leden WHERE leeftijd IN ('18','21','25','27') ;

  beide query's geven als resultaat:

  naam

  Hans

  Feppie

  Jim

  Flance

  Donny_nl

  Jasper_van

  Je ziet dat de tweede query een stuk korter is. Bij kleine query's maakt dat niet zoveel uit maar bij het gebruik van subquery's kan dit aanzienlijk schelen. Daarover later meer.

• Met LIKE kunnen we selecteren op numerieke waarden door een patroon op te geven. Daardoor is dit commando bijzonder geschikt om te gebruiken bij zoekfuncties. Stel dat we alle namen willen krijgen van mensen die een o in hun woonplaats hebben. De query komt er dan zo uit te zien

  SELECT naam FROM leden WHERE woonplaats LIKE '%o%' ;

  je ziet dat het argument na LIKE dit is: '%o%'
  De 2 % tekens zijn de zogenaamde wildcards en staan dus voor ieder willekeurig teken. Deze query levert dit resultaat op:

  naam

  Leejoo

  Feppie

  Jim

  Donny_nl

  Stel dat je iedereen wilt wiens naam begint met een a. De query komt er dan zo uit te zien:

  SELECT naam FROM leden WHERE naam LIKE 'a%' ;

  het resultaat is : Anouk

  Stel dat we alle namen willen die beginnen met een a of waar een dubbele e (ee) in zit. De query wordt dan:

  SELECT naam FROM leden WHERE naam LIKE '%ee%' OR naam LIKE 'a%' ;

  deze query geeft als resultaat:

  naam

  Anouk

  Leejoo

  Met het commando LIKE is het ook mogelijk om te selecteren op stringlengte. Daarvoor gebruiken we het teken _ , de underscore. Stel dat we alle namen willen hebben van de personen wiens naam uit 6 tekens bestaan. De query wordt dan:

  SELECT naam FROM leden WHERE naam LIKE '______' ;

  (In deze query staat 6 keer het underscore teken!)

  dit geeft als resultaat:

  naam

  Leejoo

  Feppie

  Flance

  Ook kunnen we de wildcard combineren met de stringlengte zoeker. Stel dat we de naam uit de database willen hebben waar een ej in voorkomt gevolgd door een tekenlengte van 2. We krijgen dan de query:

  SELECT naam FROM leden WHERE naam LIKE '%ej__' ;

  Dit geeft als resultaat: Leejoo
  De uitleg is eenvoudig. Er wordt gezocht naar een naam waarbij de tekenreeks persé ej moet bevatten en waarbij achter de ej precies 2 tekens moeten staan (2 keer het underscore teken na de ej). Het maakt niet uit wat er voor de reeks ej staat, vandaar de wildcard voor de ej.

  Stel dat we juist de resultaten willen hebben die niet aan een bepaald patroon voldoen. We kunnen dan gebruik maken van NOT LIKE. Stel dat we uit de tabel leden alle namen willen selecteren waar geen a in zit. We krijgen dan de query:

  SELECT naam FROM leden WHERE naam NOT LIKE '%a%' ;

  Het resultaat is dan:

  naam

  Leejoo

  Feppie

  Jim

  Donny_nl

  Je ziet dat dit commando een echt must is voor iedere database.

• NULL is een heel ander maar wel makkelijk aspect van databases. Met NULL kun je bepalen of kolommen in een rij ingevuld zijn of niet. De syntax is iets afwijkend ten opzichte van andere criteria. In plaats van = gebruiken we IS. Bij andere criteria wordt gekeken of een stelling WAAR of ONWAAR is. Bij NULL wordt gekeken of de rij in de kolom ONBEKEND of BEKEND (IS NOT NULL) is.

• GROUP BY. Heel vaak heb je informatie nodig uit de tabellen die je kunt verkrijgen door waarden van kolommen te groeperen of rijen en kolommen bij elkaar op te tellen. Het commado GROUP BY groepeert. Het grote verschil met SELECT DISTINCT is dat je kunt rekenen met GROUP BY. Stel dat we willen weten hoeveel functies er zijn en hoeveel leden iedere functie heeft.

  SELECT functie, COUNT(*) AS cnt_rij FROM leden GROUP BY functie ;

  allereerst selecteren we de functie en tellen we de rijen als tijdelijke kolom cnt_rij. We groeperen vervolgens per functie zodat we de rijen per functies krijgen. Dit levert onderstaand resultaat op:

  functie	cnt_rij
  Admin	2
  Moderator	5
  Reporter	1

  Stel dat we willen weten hoeveel berichten er totaal door de verschillende functiegroepen zijn geplaatst. We krijgen dan de volgende query:

  SELECT functie, SUM(berichten) AS sum_ber FROM leden GROUP BY functie ;

  met als resultaat:

  functie	sum_ber
  Admin	1355
  Moderator	6365
  Reporter	2494

  Stel dat we per functie groep de gemiddelde leeftijd (2 decimalen) willen weten en de helft van het totaal aantal berichten van die groep. Het geheel gesorteerd op het aantal berichten aflopend (hoogst bovenaan). We krijgen dan de query:

  SELECT functie, ROUND(AVG(leeftijd),2) AS gem_lft, (SUM(berichten) / 2) AS halve_ber FROM leden GROUP BY functie ORDER BY halve_ber DESC ;

  eerst wordt de functie geselecteerd. Vervolgens de gemiddelde leeftijd, afgerond op 2 decimalen. Als derde tellen we de berichten per groep op en delen we die door 2 (heeft geen enkel nut, maar het toont aan dat we kunnen rekenen). We groeperen per functie en sorteren op de bericht aantallen. Dit geeft het resultaat:

  functie	gem_lft	halve_ber
  Moderator	21.20	3182.50
  Reporter	21.00	1247.00
  Admin	25.00	677.50

• Stel dat we willen weten welke functiegroep totaal meer dan 5000 berichten heeft. We moeten dan de functies groeperen en daar een WHERE op los laten. Helaas kan dit niet want WHERE wordt geevalueerd voor de groepering. We moeten hier gebruik maken van HAVING. De query die we zoeken wordt:

  SELECT functie, SUM(berichten) AS tot_ber FROM leden GROUP BY functie HAVING SUM(berichten) > 5000 ;

  en geeft als resultaat:

  functie	tot_ber
  Moderator	6365

  Uitleg: we selecteren de functie en het totaal aantal berichten, gegroepeert als functie. Dus het totaal aantal berichten per functiegroep. Maar alleen die functiegroep waarbij het totaal aan berichten groter is dan 5000 .

  Stel dat we de functiegroepen willen hebben waarbij de laagste leeftijd in die groep 18 is. We krijgen dan de volgende query:

  SELECT functie FROM leden GROUP BY functie HAVING MIN(leeftijd) = 18 ;

  met als resultaat de functiegroep: Moderator

Joins (deel 1)

Het komt natuurlijk heel vaak voor dat je gegevens nodig hebt uit meer dan 1 tabel. Hiervoor zul je query's moeten maken die tabellen onderling met elkaar verbindt en hieruit de juiste gegevens selecteert. Dit doen met een zogenaamde JOIN. Er zijn heel veel type joins. Omdat niet alle join-types worden ondersteunt in alle databases behandel ik hieronder de meest voorkomende. Hieronder volgt een opsomming van een aantal joins. Die met een sterretje behandel ik in deze tutorial.

Crossjoin*, Innerjoin*, Left Outer join*, Right Outer join*, Full Outer join*, Self join*, Equi-join, Non-qui-join, Thetajoin, Naturaljoin.

• Cross JOIN: bij een Cross JOIN wordt iedere rij van tabel 1 weergegeven in combinatie met alle rijen uit tabel 2. Als we bijvoorbeeld werken met 2 tabellen, leden en logins.

  leden :

  id	naam	leeftijd	woonplaats	functie	berichten	geboren
  1	Anouk	20	Jamaica	Admin	1203	1985-01-05
  2	Leejoo	30	Londen	Admin	152	1974-07-28
  3	Hans	18	Parijs	Moderator	627	1984-11-05
  4	Feppie	18	New York	Moderator	1084	1984-09-09
  5	Jim	25	Oegstgeest	Moderator	565	1980-02-01
  6	Flance	18	Bermuda	Moderator	3255	1985-01-30
  7	Donny_nl	27	Rio	Moderator	834	1977-05-31
  8	Jasper_van	21	Parijs	Reporter	2494	1983-06-27

  logins :

  id	datum_tijd	cookie
  6	2005-06-02 12:08:09	8e1b52a5ec3f5462d02bb6e2bf24bc79
  7	2005-07-02 21:09:43	b1d6a5c66a6feb882a02c461e6270b29
  8	2005-07-02 23:12:09	a3bc525a0c6aa915a0f8cc109296220a

  En we maken de query:

  SELECT naam, datum_tijd FROM leden, logins ;

  dan krijgen we het resultaat van alle namen uit de tabel leden met daarbij alle datum_tijden van logins, oftwel:

  naam	datum_tijd
  Anouk	2005-02-06 12:08:09
  Leejoo	2005-02-06 12:08:09
  Hans	2005-02-06 12:08:09
  Feppie	2005-02-06 12:08:09
  Jim	2005-02-06 12:08:09
  Flance	2005-02-06 12:08:09
  Donny_nl	2005-02-06 12:08:09
  Jasper_van	2005-02-06 12:08:09
  Anouk	2005-02-07 21:09:43
  Leejoo	2005-02-07 21:09:43
  Hans	2005-02-07 21:09:43
  Feppie	2005-02-07 21:09:43
  Jim	2005-02-07 21:09:43
  Flance	2005-02-07 21:09:43
  Donny_nl	2005-02-07 21:09:43
  Jasper_van	2005-02-07 21:09:43
  Anouk	2005-02-07 23:12:09
  Leejoo	2005-02-07 23:12:09
  Hans	2005-02-07 23:12:09
  Feppie	2005-02-07 23:12:09
  Jim	2005-02-07 23:12:09
  Flance	2005-02-07 23:12:09
  Donny_nl	2005-02-07 23:12:09
  Jasper_van	2005-02-07 23:12:09

  Je ziet dat alle namen dus idd alle tijden krijgen. Je kunt zo dus enorme resultaten krijgen. Wat is het nut hiervan? Geen idee... dit is slechts een voorbeeld om te laten zien hoe de Join in het algemeen werkt. De Cross JOIN zul je waarschijnlijk niet vaak gebruiken.

• INNER JOIN : de INNER JOIN is een koppeling van tabellen op basis van een gelijke kolom in die 2 tabellen. In onderstaand voorbeeld gebruik ik de tabel leden en de tabel berichten. De tabel leden bevat een kolom id. Deze komt overeen met de kolom l_id (lid_id) in de tabel berichten.

  berichten :
  

  id	l_id	datum_tijd	onderwerp	bericht	html	smilie	bbcode
  1	6	2004-10-18 15:12:23	Photoshop	veel tekst 1	0	0	0
  2	5	2004-10-20 18:45:56	php	veel tekst 2	1	0	1
  3	6	2004-11-02 05:18:12	MySQL	veel tekst 3	0	0	1
  4	6	2004-11-10 16:25:23	SQL	veel tekst 4	0	1	0
  5	8	2004-11-10 21:35:45	Paint Shop Pro	veel tekst 5	0	0	0
  6	8	2004-11-22 07:45:57	Firefox	veel tekst 6	0	1	1
  7	12	2004-12-08 10:01:02	pgsql	veel tekst 7	1	0	1
  8	7	2004-12-12 12:12:12	PHP/MySQL	veel tekst 8	0	1	1
  9	6	2004-12-12 23:02:46	css	veel tekst 9	0	1	1
  10	5	2004-12-18 21:29:51	Fonts	veel tekst 10	1	1	1
  11	7	2004-12-24 22:53:21	css/text	veel tekst 11	0	0	0
  12	8	2005-01-02 19:19:46	MSIE	veel tekst 12	1	1	1
  13	2	2005-01-08 16:45:12	FireWorks	veel tekst 13	1	1	1
  14	4	2005-01-15 16:23:34	notepad	veel tekst 14	0	1	1
  15	4	2005-01-16 08:56:21	dc dvd	veel tekst 15	0	1	1
  16	6	2005-01-17 12:46:21	laptop	veel tekst 16	0	0	1
  17	3	2005-01-31 00:01:06	ASP	veel tekst 17	0	1	0
  18	8	2005-02-01 15:41:42	HTML	veel tekst 18	1	1	1
  19	12	2005-02-04 08:47:42	Netscape	veel tekst 19	0	1	1
  20	5	2005-02-07 16:49:37	dbms	veel tekst 20	0	1	0

  Als we nu willen weten welke berichten bij welke naam hoort, krijgen we onderstaande query:

  SELECT naam, onderwerp FROM leden INNER JOIN berichten ON leden.id = berichten.l_id ;

  dit geeft het resultaat:

  naam	onderwerp
  Flance	Photoshop
  Jim	php
  Flance	MySQL
  Flance	SQL
  Jasper_van	Paint Shop Pro
  Jasper_van	Firefox
  Donny_nl	PHP/MySQL
  Flance	css
  Jim	Fonts
  Donny_nl	css/text
  Jasper_van	MSIE
  Leejoo	FireWorks
  Feppie	notepad
  Feppie	cd dvd
  Flance	laptop
  Hans	ASP
  Jasper_van	HTML
  Jim	dbms

  Als je een JOIN uitvoerd met meerdere tabellen kan het veel typwerk zijn. Om dit te vereenvoudigen kunnen we met AS tijdelijke tabelnamen geven aan de tabellen. Zo hadden we hierboven de query:

  SELECT naam, onderwerp FROM leden INNER JOIN berichten ON leden.id = berichten.l_id ;

  met AS wordt dit :

  SELECT l.naam, b.onderwerp FROM leden AS l INNER JOIN berichten AS b ON l.id = b.l_id ;

  bij kleinere query's heeft dit niet zoveel nu, maar bij lange query's kan dit heel makkelijk zijn.

• LEFT OUTER JOIN : met de INNER JOIN konden we selecteren op basis van een gelijkwaardige kolom waar de waarden ingevuld waren. Met een LEFT JOIN kunnen we van een tabel de kolommen selecteren die niet ingevuld zijn. Stel dat we willen weten welke naam van de tabel leden geen berichten heeft geplaatst. We krijgen dan de query:

  SELECT leden.naam FROM leden LEFT JOIN berichten ON leden.id = berichten.l_id WHERE berichten.l_id IS NULL ;

  wat hebben we hier gedaan? We selecteren de naam van de tabel leden, maken een vergelijking met de tabel berichten op basis van het id van leden en l_id van berichten. Met de WHERE berichten.l_id IS NULL, kijken we waar er geen resultaten zijn. Dus we zoeken de naam uit de tabel leden waarbij geen berichten zijn op basis van het id nummer in beide tabellen. Dit geeft het resultaat: Anouk want haar id nummer komt niet voor in de kolom l_id in de tabel berichten.

  Bovenstaande query schrijven we dan verkort als:

  SELECT l.naam FROM leden AS l LEFT JOIN berichten AS b ON l.id = b.l_id WHERE b.l_id IS NULL ;

  en geeft hetzelfde resultaat. Alleen de schrijfwijze is korter.

• RIGHT OUTER JOIN : de RIGHT JOIN is hetzelfde als de LEFT JOIN maar met het grote verschil dat we met RIGHT JOIN de rechter tabel van de JOIN (in de query) kunnen weergeven. Stel dat we de onderwerpen willen weten van de tabel berichten die geschreven zijn door iemand die niet in de tabel leden staat. We krijgen dan de volgende query:

  SELECT b.onderwerp FROM leden AS l RIGHT JOIN berichten AS b ON l.id = b.l_id WHERE naam IS NULL ;

  dit ziet er vreemd uit, we selecteren het onderwerp uit de tabel leden terwijl er in de tabel leden geen kolom zit genaamd onderwerp. Hier is dus de JOIN aan het werk. Dus we selecteren het onderwerp uit de vergelijking van de tabellen op het id (zelfde als bij de LEFT JOIN) waar de naam null is. Dus waar er geen resultaten voor de naam zijn. Dit geeft het resultaat:

  onderwerp

  pgsql

  netscape

  Deze 2 onderwerpen zijn geschreven door de persoon met l_id = 12. In de leden tabel komt niemand voor met id = 12 en dus kan er geen naam gevonden worden bij deze onderwerpen. Zouden we dit resultaat ook niet met een LEFT JOIN kunnen bereiken? In dit geval kan dat. We krijgen dan de query:

  SELECT b.onderwerp FROM berichten AS b LEFT JOIN leden AS l ON b.l_id = l.id WHERE naam IS NULL ;

  In dit geval is de LEFT JOIN query makkelijker, maar het gaat erom dat de RIGHT JOIN duidelijk is.

We hebben gezien hoe je resultaten kunt verkrijgen door 2 tabellen te combineren. De voorbeelden die we gebruikt hebben kunnen we uitleggen met onderstaand figuur. De tabellen (de cirkels) die elkaar gedeeltelijk overlappen:

Figuur 1

Met een INNER JOIN kunnen we de resultaten uit het gedeelte M halen. Met een LEFT JOIN (leden LEFT JOIN berichten) kunnen we de resultaten verkrijgen uit het gedeelte L. De RIGHT JOIN (leden RIGHT JOIN berichten) stelt ons in staat om de resultaten uit gedeelte R te verkrijgen.

Joins (deel 2)

• FULL OUTER JOIN : bij een FULL OUTER JOIN krijgen we alle rijen van de tabellen terug. Deze JOIN kan gebruikt worden om uit Figuur 1 de resultaten uit de vlakken L en R. Helaas ondersteunen niet alle databasesystemen de FULL OUTER JOIN. De databases die ik gebruik voor deze tutorial, MySQL en MSSQL ondersteunen de FULL OUTER JOIN niet. Daarom hou ik de query simpel:

  SELECT l.id, l.naam, g.id, g.datum_tijd FROM leden AS l FULL JOIN logins AS g ON l.id = g.id ;

  oftewel, selecteer het id en naam uit leden en id en datum_tijd uit logins maak 1 rij als de id's overeen komen. Dit geeft het resultaat:

  id	naam	id	datum_tijd
  1	Anouk
  2	Leejoo
  3	Hans
  4	Feppie
  5	Jim
  6	Flance	6	2005-06-02 12:08:09
  7	Donny_nl	7	2005-07-02 21:09:43
  8	Jasper_van	8	2005-07-02 23:12:09

  
  
  

• SELF JOIN : een SELF JOIN is een INNER JOIN maar dan niet met 2 tabellen, maar met 1, dezelfde, tabel. Stel dat we willen weten wie er uit de tabel leden ouder is dan wie allemaal. Oftwel, we willen een lijst waaruit blijkt welke personen ouder zijn dan de anderen. Daarvoor moeten we een query maken die de leeftijden onderling vergelijkt en de resultaten sorteert. We krijgen dan de query:

  SELECT l1.naam AS Naam_Oud, l1.leeftijd AS Oud, l2.naam AS Naam_Jong, l2.leeftijd AS Jong FROM leden AS l1 INNER JOIN leden AS l2 ON l1.leeftijd > l2.leeftijd ORDER BY l1.leeftijd DESC ;

  wat doen we hier; we selecteren de naam van fictieve tabel 1 en hernoemen deze kolom naar Oud. Ook selecteren we de naam van fictieve tabel 2 en hernoemen deze kolom naar Jong. De kolom Oud heeft een leeftijd die hoger is dan de leeftijd van kolom Jong. De resultaten worden aflopen geordend op naam van de oudere persoon. Dit geeft het resultaat:

  Naam_Oud	Oud	Naam_Jong	Jong
  Leejoo	30	Anouk	20
  Leejoo	30	Hans	18
  Leejoo	30	Feppie	18
  Leejoo	30	Jim	25
  Leejoo	30	Flance	18
  Leejoo	30	Donny_nl	27
  Leejoo	30	Jasper_van	21
  Donny_nl	27	Anouk	20
  Donny_nl	27	Hans	18
  Donny_nl	27	Feppie	18
  Donny_nl	27	Jim	25
  Donny_nl	27	Flance	18
  Donny_nl	27	Jasper_van	21
  Jim	25	Anouk	20
  Jim	25	Hans	18
  Jim	25	Feppie	18
  Jim	25	Flance	18
  Jim	25	Jasper_van	21
  Jasper_van	21	Anouk	20
  Jasper_van	21	Hans	18
  Jasper_van	21	Feppie	18
  Jasper_van	21	Flance	18
  Anouk	20	Hans	18
  Anouk	20	Feppie	18
  Anouk	20	Flance	18

  Je ziet dat Leejoo ouder is dan iedereen en Donny_nl de op 1 na oudste, want hij is ouder dan iedereen behalve Leejoo. En zo gaat het rijtje dus verder.

Tot zover de joins. T.z.t. zal dit nog uitgebreid worden.

Subquery's

Als je in een query het resultaat nodig hebt van een andere query kun je dit bereiken door middel van een subquery. Waar kun je een subquery in je query gebruiken?

• in de WHERE van een SELECT
  
• in de HAVING van een SELECT
  
• in de SELECT van een SELECT
  
• in de FROM van een SELECT
  
• in de WHERE van een INSERT (komt later nog aan bod)
  
• in de WHERE van een UPDATE (komt later nog aan bod)
  
• in de WHERE van een DELETE (komt later nog aan bod)
  

MySQL ondersteunt subquery's vanaf MySQL versie 4.1.7. Aangezien ik deze versie niet tot m'n beschikking heb zijn alle onderstaande subquery's getest op MSSQL.

• IN : Met IN kunnen we eenvoudig een resultaat halen m.b.v. een subquery.
  Stel dat we de namen willen weten van iedereen die recentelijk heeft ingelogd en dus een cookie uit heeft staan waarvan de gegevens gecontroleerd worden met de tabel logins. Dat kan met een INNER JOIN, maar ook met een eenvoudige subquery.

  De JOIN query wordt:

  SELECT l.naam FROM leden AS l INNER JOIN logins AS g ON l.id = g.id ;

  en de query met een subquery wordt:

  SELECT naam FROM leden WHERE id IN ( SELECT id FROM logins) ;

  beide query's geven het resultaat:

  naam

  Flance

  Donny_nl

  Jasper_van

  Persoonlijk vind ik de subquery in dit geval makkelijker.

• EXISTS : met EXISTS kijken we of een subquery uberhaupt rijen kan terug geven. Zo niet, dan blijft het resultaat leeg. Stel dat we alleen de namen willen selecteren uit de tabel logins als er recent is ingelogd door minimaal 1 van de eerste 5 leden. Als er niet recent is ingelogd hoeven we geen resultaten te zien. We krijgen dan de query:

  SELECT naam FROM leden WHERE EXISTS ( SELECT * FROM logins WHERE id < 6) ;

  dit geeft geen resultaat omdat niemand met id 1 - 5 recent heeft ingelogd en daardoor in de tabel logins staat. Stel dat we alle namen willen uit de ledentabel wanneer er iemand heeft ingelogd met id 6 of hoger. De query wordt dan:

  SELECT naam FROM leden WHERE EXISTS ( SELECT * FROM logins WHERE id > 5) ;

  Er zijn rijen uit de subquery en dus worden alle namen uit de tabel leden getoont. Het resultaat is dan ook:

  naam

  Anouk

  Leejoo

  Hans

  Feppie

  Jim

  Flance

  Donny_nl

  Jasper_van

  
  
  

• ALL : dit kan het beste uitgelegd worden met een voorbeeld. Stel dat we willen weten wie de oudste is uit de tabel leden.

  SELECT naam, leeftijd FROM leden WHERE leeftijd >= ALL ( SELECT leeftijd FROM leden) ;

  De subquery geeft alle rijen met leeftijden. De leeftijd uit de hoofdquery wordt vergeleken met alle (ALL) leeftijden uit de subquery. Alleen als de leeftijd uit de query hoger is, of gelijk aan de rijen uit de subquery wordt deze als resultaat gegeven. Je krijgt dus maar 1 leeftijd en dus 1 resultaat terug.

  naam	leeftijd
  Leejoo	30

  
  
  

• ANY : stel dat iedereen willen selecteren uit de tabel leden behalve de oudste. Dan krijgen we de query:

  SELECT naam, leeftijd FROM leden WHERE leeftijd < ANY ( SELECT leeftijd FROM leden) ;

  de subquery geeft weer alle leeftijden. De hoofdquery zoekt een leeftijd die kleiner is dan 1 van de leeftijden in de subquery. Wordt die gevonden dan is dat niet de oudste en is het dus een resultaat. Het uiteindelijke resultaat is:

  naam	leeftijd
  Anouk	20
  Hans	18
  Feppie	18
  Jim	25
  Flance	18
  Donny_nl	27
  Jasper_van	21

  
  
  

Je ziet dat je dus met subquery's heel makkelijk resultaten kunt verkrijgen die je met JOINS ook wel kunt krijgen, maar dan op een wat ingewikkeldere manier. Dit zijn slechts 3 voorbeelden want met subquery's lijken de mogelijkheden eindeloos.

Datum en tijd functies

SQL kent verscheidene functies waarmee berekeningen kunnen worden uitgevoerd met datums en tijden. Bij iedere databasetype zijn er diverse functies die uitsluitend bij dat databasetype werken. Hieronder geef ik er een aantal die volgens de ANSI norm op ieder databasetype zou moeten werken.

• CURRENT_DATE() : met CURRENT_DATE() kun je de huidige systeemdatum als resultaat krijgen. De query is eenvoudig:

  SELECT CURRENT_DATE() AS datum ;

  en geeft als restultaat:

  datum

  2005-02-14

  Let op! Deze functie wordt niet ondersteunt in MSSQL.

• CURRENT_TIME() : met CURRENT_TIME() kun je de huidige systeemtijd als resultaat krijgen. De query is eenvoudig:

  SELECT CURRENT_TIME() AS tijd ;

  en geeft als restultaat:

  tijd

  13:16:13

  Let op! Deze functie wordt niet ondersteunt in MSSQL.

• CURRENT_TIMESTAMP() : met CURRENT_TIMESTAMP() kun je de huidige systeemdatum en tijd als resultaat krijgen. De query is eenvoudig:

  SELECT CURRENT_TIMESTAMP() AS datum_tijd ;

  In MSSQL wordt deze query zo geschreven:

  SELECT GETDATE() AS datum_tijd ;

  en geeft als restultaat:

  datum_tijd

  2005-02-14 13:24:44

  CURRENT_TIMESTAMP() is hetzelfde als NOW() in MySQL.

• DATE() : met DATE() kunnen we uit een string de datum ontrekken. Stel dat we een kolom hebben van het type DATETIME (dus datum en tijd) en daar willen we alleen de datum van hebben. De query wordt dan:

  SELECT DATE(kolomnaam) FROM tabel ;

  Ook kun je een string invoegen:

  SELECT DATE('2005-02-14 12:09:45') AS datum ;

  dit geeft het resultaat:

  datum

  2005-02-14

  Let op! Deze functie wordt niet ondersteunt in MSSQL en in MySQL pas vanaf versie 4.1.1 .

• YEAR() : met YEAR() kunnen we uit een string de datum ontrekken. Stel dat we een kolom hebben van het type DATETIME (dus datum en tijd) en daar willen we alleen de datum van hebben. De query wordt dan:

  SELECT YEAR(kolomnaam) FROM tabel ;

  Ook kun je een string invoegen:

  SELECT YEAR('2005-02-14 12:09:45') AS jaar ;

  dit geeft het resultaat:

  jaar

  2005

De functionaliteit van het rekenen met data en tijden is eindeloos. Vooral MySQL heeft in vergelijking met andere databasetypes erg veel functies om te kunnen rekenen met data en tijden.

Hieronder staan een aantal links van websites waar de functies en berekeningen zeer uitvoerig worden besproken

MySQL:
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

MSSQL:
http://www.databasejournal.com/features/mssql/article.php/10894_2197931_1

PostgreSQL:
http://www.postgresql.org/docs/7.4/interactive/functions-datetime.html

DML: het invoegen van gegevens

Als we gegevens in willen voegen in een tabel kan dat met 2 commando's. Het INSERT commando en het REPLACE commando. Het laatste commando is geen ANSI SQL, deze werkt alleen op de MySQL database, maar kan erg handig zijn. Vandaar dat ik deze hier bespreek.

INSERT

Met INSERT voegen we gegevens toe in een tabel. De basis syntax is:

Als we de tabel leden willen uitbreiden met een nieuw lid, dan krijgen we onderstaane query:

Er is nu een rij toegevoegd in de tabel leden. Een alternatieve schrijfwijze is dit:

Je ziet dat nu de kolomnamen zijn weggelaten en alleen de waarden worden beschreven. Dit kan erg riskant zijn als je een grote tabel hebt. 1 kolom vergeten bij de waarden en je kunt de query opniew doen.
Bij MySQL bestaat het commando auto_increment, wat zoveel wil zeggen dat die kolom automatisch met 1 wordt opgehoogd. Stel dat de kolom id van de tabel leden in MySQL auto_increment is. Dan wordt deze kolom dus automatisch opgehoogd met 1. De query kan dan volstaan met:

In bovenstaande query hebben we de kolomnaam id weggelaten en de waarde die deze kolom in deze rij zou moeten krijgen. Persoonlijk vind ik dit een erg handige functie van MySQL. In sommige andere databasetypes is er wel een vergelijkbare functie.
Ook is het mogelijk om meerdere rijen tegelijk in te voegen zonder daarvoor aparte query's te hoeven maken. Stel dat we 2 rijen willen toevoegen in 1 query, de query wordt dan:

Je ziet dat de volgende rij eenvoudigweg gescheiden wordt door een komma gevolgd door de gegevens van de 2e rij die ingevoegd moet worden.

REPLACE

Het REPLACE commando in MySQL is eigenlijk hetzelfde commando als het INSERT commando met dit verschil dat REPLACE eerst, een al bestaande rij, met daarin een unieke waarde of primairy key, verwijdert zodat er geen dubbele waarde in komen. Dit commando vind ik zelf heel handig bij sessie en cookie registratie. Stel dat we in de tabel logins een wijziging hebben. Jasper_van heeft opnieuw ingelogd. De data in de tabel logins wordt dan in MySQL op deze manier bijgewerkt. De kolom id is de primairy key. We krijgen dan de query:

Na deze query wordt eerst de rij verwijderd waarin id = 8 is. Daarna wordt er een nieuwe rij ge-insert met id = 8. Het gebruik van REPLACE is wel stukken langzamer dan INSERT, maar kan dus wel heel handig zijn.

Bij zowel INSERT als REPLACE is het niet nodig om enkele quotes te gebruiken bij waarden als die waarden (en kolomtypes) numeriek zijn. In de tabel leden zijn de kolommen id, leeftijd en berichten van het type INT en dus numeriek. De query:

kan dus ook zo geschreven worden:

waarbij de waarden van de kolommen id, leeftijd en berichten zonder quotes geschreven zijn. De ANSI standaard laat dit echter aan de programmeur zelf over. Het mag zonder quotes, het mag met quotes.

DML: het bewerken van gegevens

UPDATE

Het bewerken van gegevens gebeurt met het commando UPDATE. Dit commando UPDATE de aangegeven kolommen in de aangegeven rijen. De basis syntax is:

Stel dat de tabel leden willen aanpassen want Flance is verhuist, z'n functie is gewijzigd en z'n berichtenaantal is gestegen. We krijgen dan de query:

De tabel leden waar het id nummer 6 is wordt nu geupdate. Als we het WHERE commando hadden weggelaten en dus de query hadden gekregen:

Dan was iedere rij in de tabel leden geupdate met deze gegevens. Daarom is de WHERE statement erg van belang bij het UPDATE commando. De eerste UPDATE query had ook dit kunnen zijn:

we hebben dus het WHERE statement aangepast. Nu wordt iedere rij, waar de naam Flance is, geupdate met bovenstaande gegevens. Let er daarom op dat je zoveel mogelijk de unieke waarde of primairy key neemt als criterium voor een enkele rij UPDATE.

Rekenen met UPDATE

Ook is het mogelijk om te rekenen binnen een UPDATE. Voorwaarde hiervoor is wel de kolom een numeriek type is zoals INT of TINYINT. Ook is het mogelijk om te rekenen met kolommen die van het type DATETIME en andere tijdsindex hebben. Stel dat we in de tabel leden de kolom berichten om een of andere reden willen ophogen met 10. Dus dat alle leden 10 extra berichten krijgen. We zullen dan de query moeten maken die de kolom update met 10:

Als je deze query uitvoert zullen alle berichten van alle leden worden opgehoogd. Je kunt hier ook criteria aan stellen net zoals in de voorbeelden hierboven.

DML: het verwijderen van gegevens

Het verwijderen van rijen in een tabel doen we met het commando DELETE. De basis syntax is:

dit commando verwijdert hele rijen tegelijk. Het is dus cruciaal om duidelijk aan te geven aan welke voorwaarden de criteria moeten voldoen om niet de verkeerde gegevens te verwijderen. Als we de criteria weg zouden laten en deze query uitvoeren:

dan worden alle rijen uit die tabel verwijderd. We krijgen dan dus een legen tabel. Stel dat we uit de tabel leden de rij willen verwijderen waar de naam 'Ome Willem' is. De query wordt dan:

Net zoals bij het UPDATE commado maken we bij de criteria zoveel mogelijk gebruik van de unieke waarde of primairy key. Zouden we dit doen:

dan zouden alle rijen waar de naam 'Ome Willem' is worden verwijderd. In ons voorbeeld is dat slechts 1 rij. Maar in een grote tabel met veel leden is het niet onwaarschijnlijk dat mensen dezelfde naam hebben. Vandaar dus dat het criteria zoveel mogelijk de unieke waarde of primairy key moeten zijn wanneer je 1 specifieke rij wilt verwijderen.

In MySQL kwamen we al eerder het commando LIMIT tegen. Dit commando kan ook gebruikt worden bij het verwijderen van rijen als extra zekerheid. De query zou dan worden:

Het is in principe niet nodig, maar voor de zekerheid kun je dit erbij plaatsen.

-----------

Tot zo ver DML in SQL, het opvragen, invoegen, wijzigen en verwijderen van gegevens uit een database m.b.v. SQL.
Ik hoop dat jullie er wat van geleerd hebben. Mocht je fouten tegenkomen (nobody's perfect) laat me dit dan s.v.p. weten op donny at semeleer dot nl . Uiteraard zijn op- en aanmerkingen ook van harte welkom! Kijk ook eens op mijn Google Plus account.

Eén van de volgende tutorials gaat dus over DDL, de SQL die nodig is om tabelstructuren te maken en te onderhouden.

-----------

Overige SQL tutorials:

• Tutorial ANSI SQL-92
• Tutorial MySQL SQL, operatoren
• Tutorial MySQL SQL, rekenfuncties
• Tutorial MySQL SQL, stringfuncties

-----------

Interessante links:

• MySQL info
  MySQL (nl)
  
  
• database nieuws:
  http://www.databasejournal.com
  
  
• ansi sql tester:
  http://developer.mimer.se/validator/parser92/index.tml
  
  
• LIMIT in MSSQL:
  http://www.webmasterworld.com/forum10/7660.htm
  
  
• Startpagina dochters:
  sql | database | php | cursus | xml | Uitvaartverzekering
  
  

-----------

Naslagwerken gebruikt voor deze tutorial:

SQL voor MySQL ontwikkelaars door Rick f. van der Lans, ISBN 978 90 12 12150 7
Basis ANSI SQL van Computrain

Websites gebruikt voor deze tutorial:

http://www.sqlteam.com
http://www.mysql.com
http://developer.mimer.se/validator/parser92/index.tml
http://www.databasejournal.com
http://www.postgresql.com
http://www.w3schools.com/sql/sql_intro.asp

-----------

Dank gaat uit naar :

M'n Uitvaartverzekering verstrekker.
En dit gezellig cafe in Rotterdam.
Bezoek ook de website van onze sponsor Conservatrix Levensverzekeringen.

-----------