SQL - INLEIDING

HOOFDSTUKKEN

In dit hoofdstuk
Waarom een database?
Toegang tot de database
Uitvoeren van opdrachten (queries)
Voorbeeld van een query
Verstandig gebruik van databases

In dit hoofdstuk

Terug naar de top

In dit hoofdstuk wordt kort ingegaan op het waarom van een database, hoe men toegang krijgt tot de database en hoe opdrachten uitgevoerd worden.
Bij databases onderkent men de database, de databaseserver en de databasetaal. Volgende hoofdstukken gaan in op de databasetaal SQL; de instructies die men gebruikt om de database te manipuleren.
Niet wordt ingegaan op de wijze waarop de instructies overgebracht worden (interactief met phpMyAdmin of voorgeprogrammeerd met PHP). Dat is het onderwerp voor het volgende hoofdstuk.

Waarom een database?

Terug naar de top

In de meeste interactieve websitetoepassingen wil je gegevens bewaren, om later terug op te vragen en opnieuw te gebruiken. Voorbeeld: ‘Commentaar’ op een weblog. Een bezoeker schrijft een stukje commentaar op je weblog. Dit stukje tekst wil je bewaren. Als een andere bezoeker die commentaar wil zien en lezen, kan je de opgeslagen tekst terug laten lezen.
Een andere mogelijkheid:
Volgend voorbeeld: Een bezoeker heeft zich geregistreerd met een gebruikersnaam en wachtwoord. Deze laatste worden opgeslagen. Bij een volgend bezoek zal de gebruiker zijn gebruikersnaam en wachtwoord terug ingeven. Via PHP gaan we deze vergelijken met wat er is opgeslagen. Als die combinatie ‘gebruikersnaam’ en ‘wachtwoord’ terug wordt gevonden, zal de bezoeker toegang krijgen tot je site.

De gegevens worden opgeslagen in een ‘database’. Je kunt een database vergelijken met één grote kast . In die kast bevinden zich laden. Deze noemen we tabellen. Een tabel bestaat uit rijen en kolommen. Echter in de ‘database-taal’ spreken we niet van kolommen maar ‘velden’(fields). We Gebruiken niet het begrip rijen, maar van 'records’.
Er bestaan heel wat databases. De meest bekende zijn: dBASE, Oracle, Access, Microsoft SQL Server, Sybase, MySQL. Bij het gebruik van PHP wordt bijna steeds gebruik gemaakt van MySQL. In het vervolg van dit hoofdstuk wordt dan ook ingegaan op MySQL.

MySQL is een zogenaamde ‘relationele database’. Dit betekent dat de inhoud van 1 tabel kan worden gekoppeld aan de inhoud van een andere tabel. Daarmee kan veel overlap in tabellen worden voorkomen.

Toegang tot de database

Terug naar de top

De meeste website providers ondersteunen MySQL. In sommige gevallen is de ondersteuning van MySQL een optie en zul je voor het gebruik (extra) moeten betalen. Ook is het mogelijk een database aan te maken op de eigen PC.

Als je toegang tot de database wilt hebben, heb je de volgende, door de provider bij de registratie verkregen gegevens (c.q. bij de installatie op de PC ingevulde gegevens) nodig:

  • Host: heel vermoedelijk is dit “localhost”
  • Naam van de ‘database’
  • Gebruikersnaam of username
  • Wachtwoord

Veelal is op twee manieren toegang te verkrijgen tot de database:

  1. Met een speciaal programma. MySql is een 'kale' database. Er is een programma nodig om deze te kunnen benaderen. Eén van de belangrijkste en meest verbreide programma's is phpMyAdmin. Dit is een op php georiënteerd programma dat een schil vormt rond MySql. Binnen dit programma kan gewerkt worden met voorbewerkte commando's, maar ook met zuivere sql-commando's. Die commando's worden queriesgenoemd.
  2. Met behulp van php-programma's. Toegang tot de database kan verkregen worden met behulp van php-codes en de registratie-gegevens. Vervolgens kan met de database gewerkt worden. Daartoe worden php-functies gebruikt in combinatie met sql-commando's.

Uitvoeren van opdrachten (queries)

Terug naar de top

Een query is een opdracht die wordt verzonden naar een databaseserver om gegevens in een database te bewerken. Met een query kunt u vier bewerkingen uitvoeren met database-gegevens:

  • selecteren
  • wijzigen
  • toevoegen
  • verwijderen

Queries kunnen worden opgenomen in dynamische webpagina's. De meeste dynamische webpagina’s voeren zes stappen uit:

  1. Bereid de gegevens voor
  2. Stel een query op
  3. Open een databaseverbinding
  4. Voer de query uit
  5. Sluit de databaseverbinding
  6. Verwerk het query-resultaat

Voor het opstellen of schrijven van een Query, is er een aparte ‘taal’ beschikbaar: SQL. SQL is de afkorting van "Structured Query Language". Dit is een algemene taal die gebruikt wordt om databases aan te spreken. Met behulp van SQL kunnen gegevens uit tabellen worden gehaald, gegevens worden toegevoegd en gegevens worden verwijderd. Ook kunnen er tabellen gemaakt, gewijzigd of verwijderd worden.

Voorbeeld van een query

Terug naar de top

We zullen eerst een voorbeeld laten zien van het selecteren van gegeven. Dit is het belangrijkste deel van SQL. We doen dit aan de hand van de tabel 'klanten' uit de database 'winkel':

Tabel klanten

Naam Adres Postcode Woonplaats
Jan Janssen Vredenburg 10 3511 BA Utrecht
Piet Pieterson Janskerk 4 3542 AX Utrecht
Erik Bergman Kalverstraat 67 1123 BS Amsterdam
Bob Lansing Hoofdstraat 35 4353 EF Rotterdam

We gaan nu de namen en woonplaatsen laten zien van de klanten die in Utrecht wonen. De betreffende query ziet er als volgt uit:

SELECT naam, woonplaats FROM klanten WHERE woonplaats = 'Utrecht'

NB. Dit is de zuivere sql-code die als zodanig ingevoerd wordt in phpMyAdmin. De resultaten worden dan direct (ook in phpMyAdmin) weergegeven.<br > Als men vanuit een webapplicatie (met behulp van php) hetzelfde wil doen en ook de resultaten direct (op een pagina van een website) wil weergeven, wordt de code veel ingewikkelder. Dezelfde sql-code vormt er echter wel de basis van:

<?php
$sql="SELECT naam, woonplaats FROM klanten WHERE woonplaats = 'Utrecht';";
$result=mysql_query($sql);
while($row=mysql_fetch_array($result)){
echo ($row["naam"]." ".["woonplaats"]."<br>");}
?>

In volgende hoofdstukken komen we hier uitgebreid op terug.

Let op!! Bij SQL is het verplicht om "strings", zoals Utrecht (dus gewoon tekst), tussen enkele quotes ( ' ' ) of dubbele quotes ( " " ) te plaatsen. Ook horen SQL statement afgesloten te worden door een puntkomma (;). Het heeft de voorkeur altijd voor het SQL van de query hoofdletters te gebruiken (in de bovenstaande query vetgedrukt), omdat zo duidelijk is wat SQL is en wat zelf veranderd kan worden (naam, woonplaats etc.). Dit een algemene regel.

De volgende gegevens worden dan weergegeven:

Naam Woonplaats
Jan Janssen Utrecht
Piet Pieterson Utrecht

 

Verstandig gebruik van databases

Terug naar de top

Er kan werkelijk alles opgeslagen worden in een database. Zelfs voor een zeer uitgebreide toepassing is daardoor veelal maar één uitgebreide database en één goede webapplicatie nodig. Echter, database gestuurde applicaties werken slechter naarmate databaseverbindingen vaker worden gebruikt en er meer gegevens via eenmaal geopende databaseverbindingen moeten worden verzonden. Database servers hebben een beperkt aantal gelijktijdige verbindingen. Het is daarom zinvol om databases met verstand te benutten en daar waar het niet nodig is, buiten databases om te werken.

De volgende tips kunnen helpen om het werken met databases te verbeteren:

  • Gebruik voor standaardgegevens associatieve arrays in plaats van een database. Zie daarvoor het deel over variabelen.
  • Open een database zo laat mogelijk en sluit hem zo vroeg mogelijk. Bereid alle gegevens voor een query voor en open dan pas de databaseverbinding. Geef zo snel mogelijk de recordset vrij en sluit de database. Sluit daarbij e.e.a. in omgekeerde volgorde als waarin ze geopend zijn.
  • Probeer de php-code die nodig is om de gegevens te verwerken tussen opnenen en sluiten van de database tot een minimum te beperken. Je kunt bijvoorbeeld een recordset tekstvelden m.b.v. htmlentities converteren naar html. Beter is het om de recordset op te slaan in een string, de database te sluiten en dan pas htmlentities te gebruiken.
  • Ga eerst bij het invullen van formulieren controleren of ingebrachte gegevens (postcode) juist zijn / de juiste structuur hebben. Open de database pas als dat klopt. Om kort te gaan: houdt de controlestructuren zoveel mogelijk buiten het werken met de database.
  • Indexeer belangrijke velden / kolommen binnen een database. Dit versnelt, mits juist uitgevoerd, het opvragen van records. Indexeren dient met verstand gedaan te worden, want het kost geheugen- en schijfruimte en vertraagd toevoegen, wijzigen en verwijderen van records. Gebruik indexen in de volgende gevallen:
    • Primaire sleutel. Wordt gebruikt voor het vinden van specifieke records. Wordt overigens vaak al automatisch geindexeerd.
    • Externe sleutels. Plaatst een index op de velden die gebruikt worden als foreign key of externe sleutel voor relaties tussen tabellen.
    • Sorteervelden. Plaatst een index op de velden die gebruikt worden om records te sorteren.
  • Beperk de gegevens per record die via de verbinding moeten worden verzonden. Dus niet SELECT * (selecteer alles) maar wel SELECT prijsals alleen de prijs nodig is.
  • Beperk het aantal records dat getoond wordt: SELECT ... FROM ... LIMIT 0,9laat alleen de eerste 10 records zien. Ook zou men standaard extra selectiecriteria kunnen hanteren. Laat bijvoorbeelkd alleen de artikelen zien die in voorraad zijn. Pas na speciaal verzoek worden ook de andere getoond.
  • Maak gebruik van zogenaamde stored procedures. Dit is een verzameling instructies die opgeslagen is in de database. Voor stored procedures wordt vaak een andere programmeer- of scripttaal gebruikt. Dit is afhankelijk van de server. Stored procedures kunnen de volgende voordelen hebben:
    • Verschillende queries en sql-instructies kunnen gecombineerd worden tot één stored procedure.
    • De ene stored procedure kan mogelijk verwijzen naar een volgende. Hiermee kunnen complexe bewerkingen gecombineerd worden tot één geheel.
    • Stored procedures worden soms gecompileerd waardoor ze veel sneller zijn dan sql-instructies die eerst moeten worden geïnterpreteerd.
  • Verhoog de snelheid met output buffering: Dit gaat met de functie flush en ziet er als volgt uit:
    <?php
    // Eerste gedeelte van de webpagina genereren
    ...
    // Voorlopige uitvoer alvast naar de client verzenden
    flush();
    // Daarna pas de databaseverbinding openen
    ...
    ?>
  • Eén op één relaties denormaliseren. In een goede relationele database komen één op één relaties niet voor. Deze worden opgeslagen in één databasebestand van één tabel. Soms kunnen prestaties echter verbeterd worden door ze wel in meerdere tabellen (soms zelfs in meerdere databases) op te slaan. Bijvoorbeeld: Er kan een scheiding gemaakt worden tussen de gegevens van gebruikers in zijn algemeenheid en de gegevens die nodig zijn voor inloggen. De laatsten worden veelvuldig gebruikt. De eersten veel minder.
  • Verwijder of verplaats verouderde gegevens. Daarbij kan gebruik worden gemaakt van meerdere tabellen met dezelfde gegevensstructuur, maar andere namen. Databases met minder gegevens werken sneller.

SQL is a standard language for accessing and manipulating databases.


What is SQL?

  • SQL stands for Structured Query Language
  • SQL lets you access and manipulate databases
  • SQL is an ANSI (American National Standards Institute) standard

What Can SQL do?

  • SQL can execute queries against a database
  • SQL can retrieve data from a database
  • SQL can insert records in a database
  • SQL can update records in a database
  • SQL can delete records from a database
  • SQL can create new databases
  • SQL can create new tables in a database
  • SQL can create stored procedures in a database
  • SQL can create views in a database
  • SQL can set permissions on tables, procedures, and views

SQL is a Standard - BUT....

Although SQL is an ANSI (American National Standards Institute) standard, there are many different versions of the SQL language.

However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.

Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!


Using SQL in Your Web Site

To build a web site that shows some data from a database, you will need the following:

  • An RDBMS database program (i.e. MS Access, SQL Server, MySQL)
  • A server-side scripting language, like PHP or ASP
  • SQL
  • HTML / CSS

RDBMS

RDBMS stands for Relational Database Management System.

RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

The data in RDBMS is stored in database objects called tables.

A table is a collection of related data entries and it consists of columns and rows.

Terug naar de top