Workshop| Structured Query Language (SQL)

Leerdoelen

• SQL kunnen gebruiken om gegevens uit een database te halen
• SQL kunnen gebruiken om de gegevens in een database te bewerken

Deze les

Deze les bevat vooral een overzicht van de mogelijkheden van de SQL (Structured Query Language).

SQL

SQL is een taaltje, dat je kunt gebruiken om vanuit een programma (bijvoorbeeld vanuit PHP) vragen (queries) te stellen of opdrachten te geven aan een relationele database.

SQL is gestandaardiseerd door de American National Standards Institute (ANSI) en door de International Organisation for Standardization (ISO). SQL wordt dus ondersteund door alle relationele database managment systemen (R-DBMS) die er zijn, maar dat weerhoudt producenten van database systemen er natuurlijk niet van met eigen aanvullingen op of dialecten van de standaard te komen.

SQL is vrij eenvoudig te leren, omdat de gramatica leesbaar is en er maar een beperkt aantal commando's te geven zijn.
Er zijn twee hoofdgroepen opdrachten:

SQL Data Definition Language (DDL):

bevat opdrachten om de structuur van de database aan te passen. Dit wordt dus vooral gebruikt door

• database administrators (DBA's)
• programma's om databases mee te beheren (zoals 'phpmyadmin')

Commando's uit deze groep zijn:

• CREATE statement: om een nieuwe database te maken of een tabel aan een database toe te voegen.
  Bijvoorbeeld:

  • CREATE DATABASE School 

  • CREATE TABLE Student (studentNr int, firstName varchar) 

• ALTER statement: om eigenschappen van een tabel te veranderen en bijvoorbeeld kolommen weg te halen of toe te voegen.
  Bijvoorbeeld:

  • ALTER TABLE Student ADD lastName varchar(30) 

  • ALTER TABLE Student DROP COLUMN firstName 

• TRUNCATE statement: om een tabel leeg te gooien, maar de lege tabel te houden
  Bijvoorbeeld:

  • Truncate TABLE Student 

• DROP statement: om tabellen uit een database te verwijderen of de database zelf te verwijderen
  Bijvoorbeeld:

  • DROP TABLE Student 

  • DROP DATABASE School 

• GRANT statement: om gebruikers van de database het recht te geven tabellen te bekijken, veranderen of verwijderen.
  Bijvoorbeeld:

  • GRANT ALL ON Student TO Piet 

• REVOKE statement: om gebruikers deze rechten weer te ontnemen
  Bijvoorbeeld:

  • REVOKE DELETE ON Student TO Piet 

Hoeveel verschillende gebruikers zal de database van een webapplicatie hebben?

Deze opdrachten moet je wel een beetje kennen, maar niet uit je hoofd kunnen gebruiken. Daar heb je phpmyadmin voor. Belangrijker voor jullie zijn de opdrachten in de groep:

SQL Data Manipulation Language (DML)

Opdrachten in deze groep zijn er om de gegevens in de database te manipuleren. Dat heb je dus nodig als je een PHP webapplicatie maakt, die gegevens uit de database moet halen of erin moet stoppen.

Commando's in deze groep zijn:

• SELECT statement: om gegevens uit de database te halen.
  Syntax:

  SELECT kolomnamen FROM tabelnamen WHERE voorwaarden

• INSERT statement: om records aan de database toe te voegen.
  Syntax:

  INSERT INTO tabelnaam (kolomnamen) VALUES (veldnamen) 

• DELETE statement: om records uit de database te verwijderen.
  Syntax:

  DELETE FROM tabelnaam WHERE voorwaarden

• UPDATE statement: om gegevens in de database te wijzigen.
  Syntax

  UPDATE tabelnaam SET kolomnaam = waarde WHERE voorwaarden

SELECT statement:

Het select statement gebruikte je dus om gegevens uit de database te halen. De algemene gramatica (syntax) van dit statement is:

SELECT kolomnamen FROM tabelnamen WHERE voorwaarden

Als je informatie uit een database wil halen moet je je afvragen:

• in welke tabellen staat de informatie die ik zoek: zoek ik studenten of steden?
• welke informatie heb ik precies nodig: aleen de voornamen of zowel de voor als de achternaam?
• wil ik alle records uit een tabel terugkrijgen of maar een gedeelte: ben je in alle studenten geinteresseerd of alleen in studenten uit Rotterdam

Als je de alle records uit een tabel wil ophalen en je dus geen voorwaarden stelt waar de opgehaalde records aan moeten voldoen, dan mag je de query (vraag aan de database) inkorten door het WHERE (voorwaarden) stuk weg laten. Stel dat je bijvoorbeeld de namen van alle studenten wil zou willen opvragen dan zou de querie luiden:

SELECT firstName, lastName FROM Student 

Als antwoord van de database zou je bijvoorbeeld de volgende lijst met gegevens terug kunnen krijgen:

Met het keyword DISTINCT kun je dubbele resultaten (antwoorden) weglaten. Stel dat je alle verschillende voornamen wil weten die er op school in omloop zijn dan zou je kunnen vragen:

SELECT DISTINCT firstName FROM Student 

en als antwoord terugkrijgen:

Als je niet alleen de naam van de studenten wil opvragen, maar alle beschikbare velden uit de Student tabel, dan kun je in plaats van alle veldnamen uit te schrijven deze samenvatten met een sterretje *

SELECT * FROM Student

geeft dus alle velden (kolommen) terug, omdat je * selecteert
en alle records (rijen) omdat je geen WHERE-clause hebt.
Kortom: de hele tabel

WHERE clause

Meestal zul je niet de alle records uit een tabel willen ophalen. Wie is er geinteresseert in een lijst van alle studenten op deze school? Te algemene vragen kunnen er toe leiden dat je lang moet wachten op een onoverzichtelijk lange lijst. Stel je voor dat je aan google vraagt om alle 20 miljoen webpagina's over webdesign voor je op te halen. De kunst is dus de vraag zo te stellen dat je precies het goede antwoord krijgt.

Voorwaarden stellen aan de resultaten die je terugkrijgt doe je met een WHERE statement en dat werkt volgens het zelfde principe als een if-statement in PHP. Als de records voldoen aan de door mij gestelde voorwaarden wil ik ze ophalen uit de database en anders niet. Stel dat ik een lijst wil van alle vrouwlijke studenten, dan zou de querie kunnen luiden:

SELECT studentNr, firstName, lastName
FROM Student 
WHERE gender = 'v'

en het volgende resultaat teruggeven:

Zoals in PHP een conditie uit drie delen bestaat (een variabele, een operator en een waarde), zo bestaat in SQL een conditie meestal uit een veldnaam, een operator en een waarde. Wel zijn de operatoren soms iets anders dan in PHP

WHERE firstName = 'Piet'

WHERE gender <> 'm'

WHERE studentNr > 777776

WHERE studentNr < 777782

WHERE studentNr >= 777777

WHERE studentNr <= 777781

Je moet er rekening mee houden dat strings in SQL tussen enkele quotes moeten staan en getallen net als in PHP zonder quotes geschreven horen te worden.

Een bijzondere operator is LIKE. Hiermee kan je aangeven dat het veld aan een bepaald patroon moet voldoen:

WHERE firstName LIKE 'B%'

WHERE lastName LIKE '%r'

WHERE firstName LIKE '%e%'

En wat merkwaardig is de IS operator, om te controleren of een (niet verplicht) veld een waarde heeft. Je kan daar niet gewoon de equals (=) operator voor gebruiken, omdat iets (een veld) nooit gelijk kan zijn aan niets (NULL)

WHERE phoneNumber IS NULL

WHERE phoneNumber IS NOT NULL

Soms wil je voorwaarden ook kunnen combineren:

WHERE lastName = 'Bakker' AND gender = 'v'

WHERE gender = 'v' OR firstName LIKE 'B%'

Maar wat nou te doen als je wil weten welke studenten er in Rotterdam wonen? Je hebt dan informatie nodig uit zowel de Student tabel als uit de Stad tabel. In de student tabel staan alleen nummertjes om de stad aan te duiden.

Er zijn twee manieren om dit op te lossen. De gangbare manier is door de tabellen als het ware tijdelijk aan elkaar vast te plakken (JOINen) tot een grote tabel. Meestal worden de tabellen aan elkaar geplakt door de foreign key in de ene tabel aan de primary key in de andere tabel te plakken, zoals dat bij het ontwerp van de database was bedacht. De query zou er dan uit komen te zien als:

SELECT Student.firstName, Student.lastName
FROM Student
JOIN City ON Student.woonplaats = Stad.stadID
WHERE Stad.naam = 'Rotterdam'

Let op: bovenstaande gramatica is de officiele ANSI/ISO standaard. Veel mensen koppelen de tabellen nog in de WHERE clause. Dat lijkt makkelijker, maar is uiteindelijk minder duidelijk, foutgevoeliger en dus niet de bedoeling.

De andere manier is door de vraag in twee stukken te knippen en de uitkomst van beide queries met elkaar te vergelijken. Je vraagt dus eerst het stadID op van Rotterdam en vervolgens zoek je de studenten met dat woonplaatsnummer. Dit heet een subquerie. Sommige vragen kun je alleen stellen met een subquery, maar het kan geen kwaad dit alleen te gebruiken als je niet anders kunt.

WHERE veldnaam IN (subquery)

WHERE veldnaam NOT IN (subquery)

bijvoorbeeld:

SELECT firstName, lastName 
FROM Student 
WHERE woonplaats NOT IN (
	SELECT stadID
	FROM City
	WHERE Naam = 'Rotterdam'
)

GROUP BY clause

Soms wil je niet de inhoud van een veld ophalen, maar globalere informatie. Daarvoor zijn speciale functies:

Let op: Met deze functies krijg je maar 1 record antwoord terug dat er 5 studenten zijn en niet 5 records.

Het komt vaak voor dat je wil weten hoe vaak bepaalde waardes voorkomen in de database. Stel dat je wil weten hoe vaak de verschillende voornamen voorkomen. Het zou wat omslachtig zijn om voor elke mogelijke voornaam een query te maken.

SELECT COUNT(*) 
FROM Student
WHERE studentNr >= 777777 
  AND studentNr <= 777781
  AND firstName = 'Eva'

Daarom kun je eerst de resultaten groeperen op voornaam, om dan per groep te vragen hoeveel resultaten er zijn:

SELECT firstName, COUNT(*) 
FROM Student
WHERE studentNr >= 777777 
  AND studentNr <= 777781
GROUP BY firstName

(als je in de bovenstaande query de GROUP BY clause weg laat krijg je een error, omdat SELECT firstName 5 resultaten teruggeeft en SELECT COUNT(*) maar 1 resultaat)

HAVING clause

Met de WHERE clause kun je voorwaarden stellen aan records die je terugkrijgt. Met de HAVING clause kun je ook voorwaarden stellen aan gegroepeerde berekende waardes, zoals alle steden waard meer dan 5 studenten wonen.

SELECT Stad.naam, COUNT(*) 
FROM Student
INNER JOIN Stad ON Student.woonplaats = Stad.stadID
GROUP BY woonplaats
HAVING count(*) > 5

ORDER BY clause

In de database worden alle records bewaard in de volgorde waarin ze erin zijn gekomen. Als de primary key een auto-increment nummer is, is dat dus in volgorde van de primary key. Voor gebruikers van de database is die volgorde zelden betekenisvol. Daarom kun je zelf aangeven in welke volgorde je de resultaten wil terugkrijgen.

SELECT firstName, lastName, woonplaats 
FROM Student 
WHERE studentNr >= 777777 
  AND studentNr <= 777781
ORDER BY firstName 

Standaard wordt oplopend (ascending) gesorteerd (bij cijfers maar ook bij letters), maar als je aflopend (descending) wil sorteren kan dat ook:

SELECT firstName, lastName, woonplaats 
FROM Student 
WHERE studentNr >= 777777 
  AND studentNr <= 777781 
ORDER BY lastName DESC

Ten slotte is het mogelijk op meerdere kolommen te sorteren. Bijvoorbeeld (lekker onlogisch): eerst aflopend op woonplaats, maar daarbinnen oplopend op achternaam

SELECT firstName, lastName, woonplaats 
FROM Student 
WHERE studentNr >= 777777 
  AND studentNr <= 777781 
ORDER BY woonplaats DESC, lastName ASC

INSERT statement

Het INSERT statement wordt vooral gebruikt om 1 record tegelijk aan 1 tabel tegelijk toe te voegen. Bijvoorbeeld het inschrijven van een nieuwe student:

INSERT 
INTO Student (studentNr, firstName, lastName, gender, woonplaats) 
VALUES (0777782, 'Jan', 'Klaassen', 'm', 123)

Als je primary key een auto-increment veld is en je wil niet onthouden wat het laatste volgnummertje was, dan kun je dat auto-increment veld beter weg laten. De database doet het dan voor jou automatisch goed.

INSERT 
INTO Student (firstName, lastName, gender, woonplaats) 
VALUES ('Jan', 'Klaassen', 'm', 123) 

Aangezien je ook geen zin hebt om eerst te moeten uitzoeken welk ID bij iemands woonplaats hoort, kun je hier goed van een subquery gebruik maken:

INSERT 
INTO Student (firstName, lastName, gender, woonplaats) 
VALUES ('Jan', 'Klaassen', 'm', (SELECT stadID FROM Stad WHERE naam = 'Lutjebroek')) 

DELETE statement

Het DELETE statement wordt gebruikt om records uit een tabel te verwijderen. Stel dat je de stad Rotterdam zou willen verwijderen dan zou de query luiden:

DELETE 
FROM Stad
WHERE naam = 'Rotterdam' 

Net als bij het SELECT statement mag je de WHERE clause weglaten als je geen voorwaarden stelt en dus alles in de tabel wil weggooien. Het is dus zaak de WHERE clause niet per ongeluk te vergeten en goed op te letten dat je precies de goede voorwaarden stelt (specifiek genoeg bent).

Om te voorkomen dat er studenten plotseling in een niet bestaande stad wonen (hun woonplaats was 300, maar stadID 300 is weggegooid) - om de integriteit (correctheid) van de database te waarborgen - controleert de database van te voren of er foreign keys zijn die naar de primary key van een weg te gooien record verwijzen. De database kan op 3 manieren reageren op een dergelijk DELETE opdracht:

• weigeren (het veiligst): records mogen niet weggegooid worden als er foreign keys naar verwijzen
• de foreign key leeg maken (op NULL zetten): de studenten worden dakloos. Dit kan alleen als de foreign key (woonplaats) een niet verplicht veld is
• alle verwijzende velden mee-verwijderen (zeer rigoreus): De stad wordt verwijderd inclusief bewoners (bommetje)

UPDATE statement

Het UPDATE statement wordt gebruikt om gegevens in de database te wijzigen. Bijvoorbeeld als iemand verhuist of om fouten te corrigeren:

UPDATE Student 
SET firstName = 'Daniel', gender='m' 
WHERE studentNr = 0777781 

Ook hier kan de WHERE clause worden weggelaten en is het dus zaak specifiek genoeg te zijn (de goede voorwaarden te stellen), want voordat je het weet heet iedereen in je database 'Daniel'

Ook hier gelden regels om de integriteit van de database te waarborgen. Neem bijvoorbeeld:

UPDATE Stad
SET stadID = 123
WHERE stadID = 300

Ook met deze querie zouden studenten met woonplaats 300 plotseling in een niet bestaande stad komen te wonen. Bovendien zouden er twee steden kunnen ontstaan met hetzelfde ID, waardoor je niet meer zou weten waar studenten met woonplaats 123 wonen. Ook hier kan de database op 3 manieren op reageren:

• weigeren (veiligst): Velden in de primary key mogen niet worden gewijzigd, zolang er foreign keys naar verwijzen.
• de foreign key leeg maken (op NULL zetten). Dit kan alleen als de foreign key (woonplaats) een niet verplicht veld is
• de foreign keys mee veranderen, zodat ze naar hetzelfde record blijven verwijzen

Referential Integrity

Het is het dus belangrijk dat foreign keys in de database worden vastgelegd - alleen zo kan je aangeven wat er moet gebeuren met records die verwijzen naar een veranderd veld. In MySql kan dat alleen als beide tabellen van het type InnoDB (en niet van het standaard type MyISAM). Ook vanuit PhpMyAdmin kan je foreign keys vastleggen, al moet je daar wel zelf aan denken