Lezing databases en SQL

Lezing databases en SQL Inleiding............................................................................................................... 2 Doelgroep............................................................................................................. 2 Deel 1 .................................................................................................................. 3 1.1 Databases .................................................................................................... 3 1.2 Begrippen .................................................................................................... 3 1.2.1 Tabellen .................................................................................................... 3 1.2.2 Kolommen en gegevenstypen ...................................................................... 3 1.2.3 Indexen .................................................................................................... 4 1.2.4 Rijen......................................................................................................... 5 1.2.5 Sleutels..................................................................................................... 5 1.3 Relaties tussen tabellen ................................................................................. 5 1.4 Ontwerpen database...................................................................................... 6 1.5 Aanmaken database ...................................................................................... 8 Deel 2 .................................................................................................................14 2.1 Inleiding SQL ...............................................................................................14 2.2 Wat zijn de voordelen van SQL? .....................................................................14 2.3 SQL-opdrachten ...........................................................................................14 2.4 Een tabel aanmaken .....................................................................................15 2.5 Gegevens toevoegen ....................................................................................15 2.6 Gegevens wijzigen .......................................................................................16 2.7 Gegevens verwijderen ..................................................................................17 2.8 Gegevens opvragen ......................................................................................17 2.8.1 Gegevens sorteren ....................................................................................18 2.8.2 Gegevens filteren ......................................................................................19 2.8.3 Gegevens uit meerdere tabellen selecteren...................................................19 2.9 Een tabel verwijderen ...................................................................................21 2.10 Gereserveerde woorden van SQL..................................................................21

F:\website\website_nieuw\Downloads\Lezing databases en SQL.doc

blz. 1

Inleiding In deze lezing worden u de basisbeginselen van databases en SQL bijgebracht. De lezing bestaat uit 2 delen. In het eerste deel wordt uiteengezet hoe een database is opgebouwd en komen een aantal basisbegrippen aan de orde. Daarbij zal gebruik gemaakt worden van het programma MS Access 2000 van Microsoft. Andere bekende programma’s zijn: SQLserver (ook van Microsoft), Sybase, Oracle, MySQL en dBase. In het tweede deel wordt u uitgelegd hoe SQL werkt. SQL, wat staat voor Structured Query Language, is een taal die wordt gebruikt om met databases te communiceren. Ook hiervoor zullen we gebruik maken van MS Access 2000.

Doelgroep De lezing is bedoeld voor mensen die geen of weinig ervaring hebben met databases en/of SQL. Na afloop van de lezing zullen zij: Bekend zijn met belangrijke begrippen t.a.v. databases; Bekend zijn met de structuur van databases; In staat zijn om in MS Accesss eenvoudige tabellen aan te maken en relaties te leggen tussen tabellen; In staat zijn om eenvoudige SQL-opdrachten te genereren. Gerrit Tiemens, HCC Afdeling Arnhem, Kernlid locatie Arnhem.

Zevenaar, vrijdag 20 juni 2003.


blz. 2

Deel 1 1.1 Databases Misschien bent u zich er niet van bewust, maar u maakt in uw dagelijks leven vaak gebruik van een database. Zo gebruikt u een database wanneer u: • een naam in het adresboek van uw e-mailprogramma opzoekt; • een zoekopdracht geeft via een zoekmachine op het Internet (Ilse, Google, etc.); • op uw werk inlogt op het netwerk. Uw naam en wachtwoord worden gecontroleerd in een database; • met uw bankpasje geld uit de muur haalt. Door middel van een database wordt de ingevoerde PIN-code gecontroleerd en kunt u uw saldo opvragen. Maar het is niet altijd duidelijk wat een database precies is. Dit komt vooral omdat men dezelfde databasetermen gebruikt in verschillende contexten. Deze lezing begint daarom met een uitleg van de belangrijkste databasetermen.

1.2 Begrippen De term database (in het Nederlands ook wel gegevensbank geheten) wordt op verschillende manieren gebruikt, maar in dit geval (en vanuit het perspectief van SQL) is een database een verzameling gegevens die op een bepaalde manier geordend en bewaard worden. U kunt zich een database het beste voorstellen als een opbergkast waarin u verschillende mappen kunt bewaren. De opbergkast is gewoon de fysieke locatie om gegevens in op te slaan, ongeacht om welke gegevens het gaat en hoe ze zijn geordend. Mensen gebruiken de term database vaak als ze het databaseprogramma dat ze gebruiken bedoelen. Dit klopt niet en het is een bron van veel verwarring. Een databaseprogramma wordt vaak een Database Management System (DBMS) genoemd. De database is de opslagplaats die met een DBMS kan worden gemaakt en gemanipuleerd.

1.2.1 Tabellen Als u informatie in een opbergkast bewaart, smijt u het niet gewoon in een lade. U maakt mappen in de kast, waarna u gegevens die bij elkaar horen in specifieke mappen opbergt. In de wereld van de database wordt een map een tabel (of entiteit) genoemd. Een tabel is een gestructureerde lijst waarin gegevens van een bepaald type kunnen worden opgeslagen. Een tabel bevat, in het geval van een boekenwinkel, bijvoorbeeld een lijst van alle klanten. Het kernpunt is hier dat de gegevens die in de tabel worden opgeslagen van hetzelfde type zijn of in dezelfde lijst staan. Het is niet verstandig om een lijst met klanten en een lijst met bestelde boeken in dezelfde databasetabel op te slaan. In plaats daarvan maakt u twee tabellen, voor elke lijst één. Elke tabel in een database heeft een eigen naam waaraan u het kunt herkennen. Dit is een unieke naam. Dit betekent dat geen enkele andere tabel in de database dezelfde naam kan hebben.

1.2.2 Kolommen en gegevenstypen Tabellen zijn opgebouwd uit kolommen (attributen). Elke kolom bevat een bepaald soort informatie binnen de tabel. Dit is het beste uit te leggen door een database te zien als een raster, zoals een spreadsheet (Excel). Elke kolom in het raster bevat een bepaald soort informatie. Een tabel met klantgegevens bevat bijvoorbeeld een kolom met het klantnummer, terwijl in F:\website\website_nieuw\Downloads\Lezing databases en SQL.doc

blz. 3

een andere kolom de naam van de klant staat. Ook de adresgegevens zijn in verschillende kolommen opgedeeld (zie figuur 1).

(figuur 1)

Elke kolom in een database heeft een bijbehorend gegevenstype. Een gegevenstype bepaalt welke soort gegevens in de kolom kan worden opgeslagen. Als in een kolom alleen getallen worden opgeslagen (bijvoorbeeld het aantal artikelen in een bestelling), krijgt die kolom een numeriek gegevenstype. MS Access kent de volgende gegevenstypen: Tekst Memo Numeriek Datum/tijd Valuta AutoNummering

Tekst of combinaties van tekst en cijfers (maximaal 255 tekens) Tekst (maximaal 65.535 tekens) Numeriek (voor berekeningen) Datum of tijd Geldbedragen (voor financiële berekeningen) Het automatisch invoeren van opeenvolgende unieke nummers (bij het toevoegen van een nieuw record) Ja/Nee Boolean-waarden (Ja of Nee, Waar of Onwaar) OLE-object Objecten zoals Word-documenten, Excel-werkbladen, geluiden, afbeeldingen enz. (maximaal 1 Gigabyte) Hyperlink Voor opslag van een hyperlink (een verwijzing naar een bestand op het World Wide Web; als u op deze verwijzing klikt, wordt het betreffende bestand opgehaald) Wizard Opzoeken Dit is geen gegevenstype, maar helpt u bij het definiëren van het gegevenstype en de kenmerken van een veld.

1.2.3 Indexen Indexen worden gebruikt om gegevens logisch te sorteren, zodat zoek- en sorteeropdrachten sneller kunnen verlopen. U kunt een index het beste vergelijken met een index of trefwoordenlijst in een boek, een alfabetische lijst woorden met referenties naar locaties in het boek. Een index in een database werkt op bijna dezelfde wijze. De gegevens van de primaire sleutel (zie 1.2.5) zijn altijd gesorteerd, dat doet het DBMS voor u. U kunt een index definiëren op één of meer kolommen, zodat het DBMS voor zichzelf een gesorteerde lijst bewaart van de inhoud van de tabel. Het DBMS gebruikt deze index op dezelfde manier als u een register in een boek gebruikt. Het zoekt in de gesorteerde index naar de locaties van bepaalde woorden en haalt dan de gevraagde rijen op. U moet bij het aanmaken van indexen het volgende in gedachten houden: • Met indexen kunt u weliswaar sneller gegevens opzoeken, maar het invoeren, veranderen en verwijderen van gegevens wordt langzamer. Bij het uitvoeren van deze operaties moet het DBMS namelijk ook de indexen bijwerken. • Indexen kunnen veel opslagruimte in beslag nemen.


blz. 4

•

Indexen worden gebruikt voor filteren en sorteren van gegevens. Als u gegevens vaak in een bepaalde volgorde sorteert, kunt u hier beter een index voor definiëren.

1.2.4 Rijen Gegevens in een tabel worden bewaard in rijen. Elk opgeslagen record wordt bewaard in zijn eigen rij. Als u een tabel weer vergelijkt met een raster zoals een spreadsheet, zijn de verticale kolommen van het raster de kolommen van de tabel en de horizontale rijen zijn de rijen van de tabel (zie figuur 1). In een tabel met klantgegevens worden de gegevens van één klant in een rij geplaatst. Het aantal rijen in de tabel is het aantal records van de tabel. Een rij is dus een record in een tabel. Een onderdeel van zo’n record (bijvoorbeeld klantnummer) wordt ook wel veld genoemd.

1.2.5 Sleutels Elke rij in een tabel moet een kolom (of meerdere kolommen) bevatten die een unieke waarde heeft. Een tabel met klantgegevens bevat bijvoorbeeld een kolom met klantnummers, terwijl in een tabel met bestelgegevens een kolom met bestelnummers voorkomt. In een tabel met de gegevens van alle werknemers kan een kolom met werknemersnummers of Sofi-nummers worden gebruikt. De kolom (of reeks kolommen) waarmee elke rij in een tabel wordt geïdentificeerd, wordt de primaire sleutel (primary key) genoemd. De primaire sleutel verwijst naar een enkele rij. Het is heel moeilijk specifieke rijen uit een tabel bij te werken of te verwijderen als er geen primaire sleutel gedefinieerd is. Definieer daarom altijd een primaire sleutel. Elke kolom in een tabel kan worden aangewezen als primaire sleutel, zolang het aan de volgende voorwaarden voldoet: • Er mogen geen rijen zijn waarvan de primaire sleutels dezelfde waarden hebben; • De primaire sleutel in een rij moet altijd een waarde hebben, m.a.w. de kolom mag geen NULL-waarde bevatten; • De kolom die de waarden van de primaire sleutel bevat, kan niet worden veranderd of bijgewerkt; • De waarden van de primaire sleutel kunnen niet opnieuw worden gebruikt. Als een rij uit de tabel wordt verwijderd, kan de primaire sleutel niet aan een nieuwe rij worden toegekend. Primaire sleutels worden meestal gedefinieerd bij een enkele kolom binnen een tabel. Zo ligt het voor de hand dat de kolom klantnummer uit de tabel in figuur 1 de primaire sleutel is. Het is echter ook mogelijk een combinatie van meerdere kolommen te gebruiken als primaire sleutel. Als er meerdere kolommen worden gebruikt, gelden de hierboven gegeven regels voor alle kolommen. De waarden van alle kolommen moeten samen uniek zijn. De individuele kolommen hoeven dan dus geen unieke waarden te hebben. Er bestaat nog een heel belangrijk soort sleutel, de refererende of vreemde sleutel (foreign key). Deze sleutel is een kolom in een tabel waarvan de waarden in de primaire sleutel van een andere tabel moeten voorkomen.

1.3 Relaties tussen tabellen Een database bestaat nagenoeg altijd uit meerdere tabellen. Tussen deze tabellen kunnen relaties worden gelegd. Zo’n database heet een relationele database. Voordelen De voordelen van relaties blijken uit het volgende voorbeeld. Stel dat een schoolbibliotheek gegevens van boeken en uitgevers wil bijhouden. In een niet relationele database zou u één grote tabel kunnen maken voor alle informatie, maar dan zou u veel gegevens meer malen moeten opslaan. Voor elk boek zou u dan bijvoorbeeld ook het adres van de uitgever willen opnemen. Als een schoolbibliotheek tien boeken van F:\website\website_nieuw\Downloads\Lezing databases en SQL.doc

blz. 5

dezelfde uitgever in haar collectie heeft, dan moet dat adres tienmaal opgeslagen worden. Wanneer een uitgever verhuist, moeten er tien adreswijzigingen in de tabel worden aangebracht. In een relationele kunt u twee aparte tabellen maken, één met de boekgegevens en één met de gegevens van de uitgever, en deze tabellen vervolgens koppelen. Daardoor hoeft een adreswijziging maar één keer te worden aangebracht. Dat werkt niet alleen sneller. U voorkomt zo ook invoerfouten. De volgende relaties bestaan: Eén-op-veel In een één-op-veel (1-N) relatie kan de waarde met een primair sleutelveld in de ene tabel meer malen voorkomen in het gekoppelde veld in de andere tabel. Deze relatie komt het meest voor. Eén-op-één In een één-op-één (1-1) relatie heeft de waarde in een primair sleutelveld in de ene tabel nooit meer dan één overeenkomende waarde in het gekoppelde veld in een andere tabel. Deze relatie komt niet zo vaak voor. Meestal kunnen tabellen met een één-op-één relatie namelijk eenvoudig worden gecombineerd tot één tabel. Veel-op-veel In veel DBMS’en, waaronder MS Access kunt u geen veel-op-veel (N-N) relaties definiëren. Het mag dus niet nooit zo zijn dat de waarde in een veld in de ene tabel vaker in dat veld kan voorkomen dan in het gekoppelde veld in een andere tabel. Door altijd uit te gaan van een primair sleutelveld zult u nooit op zo’n veel-op-veel relatie stuiten.

1.4 Ontwerpen database Een belangrijke stap bij het ontwikkelen en bouwen van een database is het ontwerp. Een goed ontworpen database maakt het onderhoud een stuk eenvoudiger. Het is van het grootste belang dat de gegevens op de juiste manier in kolommen worden verdeeld. Zo is het bijvoorbeeld aan te bevelen om de woonplaats en de provincie in aparte kolommen op te nemen. Op die manier is het mogelijk gegevens op grond van een bepaalde kolom te sorteren of te filteren (bijvoorbeeld om alle klanten uit een bepaalde plaats of een bepaalde provincie te kunnen opzoeken). Als de woonplaats en de provincie in één kolom staan, zou het heel moeilijk zijn om de tabel te sorteren of te filteren op provincie. Bij het ontwerpen van een database moet u eerst bepalen wat het doel ervan is. Welke vragen moet de database kunnen beantwoorden? In welke vorm wordt de informatie momenteel opgeslagen? Wat voor rapporten verwacht u van een database? Als voorbeeld gaan we een database ontwerpen voor een boekenwinkel. De database moet informatie bevatten over: • alle klanten, • alle boeken, • alle bestellingen. We moeten dus drie tabellen aanmaken, een tabel Klant, een tabel Boek en een tabel Bestelling. De tabel Klant bevat de volgende gegevens per klant: • KlantID • Naam • Adres • Postcode • Plaats • Telefoon • E-mail F:\website\website_nieuw\Downloads\Lezing databases en SQL.doc

blz. 6

• • • •

Wijze van betaling Creditcard nummer Loginnaam Wachtwoord

De tabel Boek bevat de volgende gegevens per boek: BoekID • Titel • Schrijver • Prijs • ISBN-nummer • Uitgavedatum • Uitgever • Paperback/Hardcover • Aantal bladzijden • Voorraad De tabel Bestelling bevat de volgende gegevens per bestelling: • BestellingID • KlantID • BoekID • Datum • Geleverd • Betaald Een klant kan meerdere bestellingen doen en een boek kan in meerdere bestellingen voorkomen. Dat levert de volgende relaties op:

klant

Boek Bestelling 1

N

N

1

Al eerder zagen wij dat kolommen verschillende gegevenstypen kunnen hebben (zie blz. 4). Voor de tabel Klant geldt het volgende: Veldnaam - KlantID - Naam - Adres - Postcode - Plaats - Telefoon - E-mail - Betaling - Creditcardnr - Loginnaam - Wachtwoord

Gegevenstype Numeriek (Integer) Text (30) Text (30) Text (7) Text (30) Text (11) Text (30) Text (‘Visa’,’Rembours’, ‘Acceptgiro’) Text (12) Text (8) Text (8)

Sleutel pk

Verplicht ja ja ja ja ja ja ja ja

Voor de tabel Boek geldt het volgende: Veldnaam BoekID Titel

Gegevenstype Numeriek (Integer) Text (30)


Sleutel pk

Verplicht ja ja

blz. 7

Schrijver Prijs ISBN-nummer Uitgavedatum Uitgever Soort_kaft Aantal_bladzijden Voorraad

Text (30) Numeriek (Decimaal) Text (15) Datum/Tijd Text (30) Text (‘hardcover’, paperback’) Numeriek (Integer) Numeriek (Integer)

ja ja

Voor de tabel Bestelling geldt het volgende: Veldnaam BestellingID KlantID BoekID Datum Geleverd Betaald

Gegevenstype Numeriek (Integer) Numeriek (Integer) Numeriek (Integer) Datum/Tijd Datum/Tijd Ja/Nee

Sleutel pk fk fk

Verplicht ja ja ja ja

Pk = primary key, Fk = foreign key

De getallen tussen () wil zeggen het aantal karakters van het veld (veldlengte). Zoals we al eerder zagen, zijn er twee relaties bedacht. Er is een één-op-veel relatie tussen de tabellen Boek en Bestelling. De verbinding wordt gevormd door BoekID (primary key) in tabel Boek en BoekID (foreign key) in tabel Bestelling. Er is een één-op-veel relatie tussen de tabellen Klant en Bestelling. De verbinding wordt gevormd door KlantID (primary key) in tabel Klant en KlantID (foreign key) in tabel Bestelling. In deel 2 van deze lezing zullen we zien hoe wij de gegevens uit deze tabellen aan elkaar kunnen koppelen.

1.5 Aanmaken database We hebben de ontwerpfase achter de rug en we gaan nu de database aanmaken. Dat kan op een aantal manieren, ofwel met behulp van MS Access, ofwel met behulp van SQL. Aangezien SQL in het tweede deel van deze lezing aan de orde komt, doen we het eerst met behulp van MS Access. Start MS Access op en kies voor Lege Access-database.

(figuur 2)

Vervolgens wordt u gevraagd om de database een naam te geven. F:\website\website_nieuw\Downloads\Lezing databases en SQL.doc

blz. 8

Geef de database de naam Boekenwinkel. U kunt eventueel ook nog aangeven waar u de database wilt opslaan (bij Opslaan in:). Druk . vervolgens op Daarna verschijnt het volgende scherm:

(figuur 3)

Wij kiezen nu voor Tabel maken in ontwerpweergave. In het scherm dat vervolgens verschijnt gaan wij de velden aanmaken, zoals die gedefinieerd zijn in de ontwerpfase (Zie blz. 7).

(figuur 4)

Van KlantID maken we de primary key door op de veldnaam met de rechtermuisknop te klikken en de sleutel te selecteren.

(figuur 5)

(figuur 6)

Let er op dat achter Geïndexeerd staat Ja (Duplicaten Nee)! Geef bij de velden van het gegevenstype Tekst ook de Veldlengte in, zoals bepaald in het ontwerp. En vergeet ook niet om de verplichte velden aan te geven (bij Vereist). F:\website\website_nieuw\Downloads\Lezing databases en SQL.doc

blz. 9

(figuur 7)

Als alle velden met de bijbehorende Gegevenstype zijn aangemaakt, kunt u de tabel opslaan door te klikken op verschijnt.

(tweede van links) in de werkbalk. Het volgende menu Voer in Klant en druk op OK. Op deze wijze kunnen wij ook de andere twee tabellen aanmaken. Als dat gebeurd is, kunnen we relaties gaan leggen tussen de tabellen.

(figuur 8)

Kies in de werkbalk relaties

. Het volgende scherm verschijnt:


blz. 10

(figuur 9)

Voeg vervolgens de tabellen toe. U kunt alle tabellen in één keer selecteren door Bestelling te selecteren (met de linkermuis) toets, de SHIFT-toets ingedrukt houden en vervolgens de tabel Klant te selecteren. Kies dan Toevoegen en daarna Sluiten. Sleep de tabellen –voor het gemak- naar de positie op het scherm zoals afgebeeld in figuur 10.

(figuur 10)

We gaan eerst een relatie leggen tussen de tabel Klant en Bestelling. Klik in de tabel Klant op de KlantID en sleep dit veld naar KlantID in de tabel Bestelling terwijl u de linkermuisknop ingedrukt houdt. Als u de linkermuisknop los laat verschijnt het volgende scherm:


blz. 11

(figuur 11)

U ziet dat er een één-op-veel relatie wordt aangemaakt. U kunt nog aangeven of u een referentiële integriteit wilt afdwingen. Wat is referentiële integriteit? Als u Referentiële integriteit afdwingen selecteert, dan draagt u MS Access op om te waken over de structurering van uw gegevens. Als referentiële integriteit is afgedwongen, dan wordt de integriteit van uw gegevens verzekerd door de gemaakte relaties. U kunt dan geen gegevens in de tabel invoeren aan de veel-zijde van de één-opveel relatie, als de waarde van de refererende sleutel niet correspondeert met een waarde in de primaire sleutel. Zo kunt u geen bestelling invoeren als de klant niet bestaat. U kunt ook geen records aan de één-zijde van de één-op-veel relatie verwijderen, als records aan de veel-zijde naar dit record refereren. Dit zou het zogeheten weesrecord syndroom creëren, kindsrecords zonder ouder (bestelling zonder bijbehorende klant). Tenzij de optie Gerelateerde records trapsgewijs verwijderen is ingesteld, zou u eerst alle gerelateerde records aan de veel-zijde moeten verwijderen, voordat u een record aan de één-zijde zou mogen verwijderen. Wat houdt trapsgewijs bijwerken in? Gerelateerde velden trapsgewijs bijwerken is een optie bij referentiële integriteit. Als deze optie is ingeschakeld bij een relatie, en de primaire-sleutelwaarde van een record aan de één-zijde van de één-op-veel relatie wordt gewijzigd, dan zorgt MS Access voor een trapsgewijze aanpassing van de waarde in de refererende sleutel van de records aan de veel-zijde. Waarvoor dient de optie trapsgewijs verwijderen? Wees voorzichtig met het activeren van de optie Gerelateerde velden trapsgewijs verwijderen dan met Gerelateerde velden trapsgewijs bijwerken. De eerste optie zal alle kindrecords aan de veel-zijde verwijderen als u het ouderrecord aan de één-zijde verwijdert. Dit lost het probleem van het verwijderen van alle kindrecords voordat het ouderrecord kan worden verwijderd wel op, maar er zijn wel consequenties aan verbonden. Ook al heeft u de informatie zelf niet (meer) nodig, dan moet u er zeker van zijn dat de te verwijderen informatie niet op een andere plaats in de database nodig is. Vervolgens gaan we op dezelfde wijze een relatie leggen tussen de tabel Boek en de tabel Bestelling. Het eindresultaat ziet er als volgt uit:


blz. 12

(figuur 12)


blz. 13

Deel 2 2.1 Inleiding SQL SQL (spreek dit uit als de aparte letters S-Q-L, of als het Engelse sequel) is een afkorting van Structured Query Language, wat in het Nederlands zoveel betekent als ‘gestructureerde verzoektaal’. SQL is in de jaren 70 ontwikkeld en speciaal ontworpen om te kunnen communiceren met databases. In tegenstelling tot andere talen (natuurlijke talen zoals Nederlands of programmeertalen zoals C of Visual Bacic), kent SQL slechts enkele woorden. Dit is met opzet zo gedaan. SQL heeft slechts één doel: het verschaft een eenvoudige en efficiënte manier om gegevens uit een database te lezen en naar een database te schrijven.

2.2 Wat zijn de voordelen van SQL? SQL is geen taal van een bepaald merk dat wordt gebruikt door bepaalde databaseproducenten. Bijna elke grote databasetoepassing ondersteunt SQL, dus als u deze ene taal kent, kunt u met de meeste grote databases communiceren. SOL is gemakkelijk te leren. De paar instructies waar de taal uit bestaat, zijn allemaal duidelijke en begrijpelijke woorden, weliswaar in het Engels. Ondanks de eenvoud is SQL een zeer krachtige taal. Door de elementen van de taal handig te gebruiken, kunt u zeer complexe en geavanceerde databasebewerkingen uitvoeren. Let op: In veel databasemanagementsystemen (DBMS’en) is de standaard-SQL uitgebreid met extra instructies. Deze uitbreidingen zorgen voor extra functionaliteit of maken bepaalde operaties gemakkelijker uitvoerbaar. Dit is heel handig, maar de uitbreidingen zijn vaak heel specifiek voor een bepaald DBMS en worden meestal door slechts één producent ondersteund. Standaard-SQL wordt beheerd door de commissie ANSI-standaarden1 en ook vaak ANSISQL genoemd. Alle grote databaseprogramma’s, zelfs als ze hun eigen uitbreidingen hebben, ondersteunen ANSI-SQL. Individuele implementaties hebben hun eigen namen, bijvoorbeeld Transact-SQL voor SQL-server van Microsoft.

2.3 SQL-opdrachten SQL-opdrachten, ook wel Query’s genoemd, kunnen onderverdeeld worden in 3 categorieën, t.w.: Data Definition Language (DDL) Deze bevat opdrachten voor het maken en manipuleren van databases en tabellen. De DDL houdt zich bezig met de structuur van de database, de tabellen en de velden. De opdrachten zijn: • Create • Alter • Drop In deze lezing wordt aandacht besteed aan de opdrachten Create en Drop. Data Control Language (DCL) Deze bevat opdrachten om bijvoorbeeld de rechtenstructuur van de database te regelen (wie heeft toegang tot welke databases en tabellen). De opdrachten zijn: • Grant • Deny • Revoke Deze opdrachten worden in deze lezing niet behandeld.

1

ANSI = The American National Standards Institute


blz. 14

Data Manipulation Language (DML) Deze bevat opdrachten voor het verwerken van de gegevens die in de tabellen zijn opgenomen. De opdrachten zijn: • Select • Insert • Update • Delete In deze lezing komen deze opdrachten aan de orde.

2.4 Een tabel aanmaken Voor het aanmaken van een tabel gebruiken we de opdracht CREATE TABLE. Om een tabel te creëren moet u de volgende informatie opgeven: 1. De naam van de nieuwe tabel; 2. De namen en definities van de tabelkolommen, gescheiden door komma’s; 3. De locatie van de tabellen (niet in alle DBMS’en verplicht) Met de volgende SQL-instructie maakt u een tabel Klant: CREATE TABLE Klant ( KlantID CHAR(10), Naam CHAR(20), Adres CHAR(25), Postcode CHAR(7), ); (AanmaakTabelKlant in test.mdb)

In de bovenstaande code ziet u dat de tabelnaam direct achter de sleutelwoorden CREATE TABLE staat. De werkelijke tabeldefinitie (de namen van alle kolommen) staan tussen haakjes. Alle kolommen zijn door komma’s van elkaar gescheiden. In het voorbeeld zijn alle velden van het type CHAR, d.w.z. dat het tekstvelden zijn. Tussen haakjes is de lengte van het veld aangegeven.

2.5 Gegevens toevoegen Om gegevens toe te voegen aan een tabel gebruiken we de opdracht INSERT. Dit kan op verschillende manieren. • U kunt een enkele complete rij invoeren; • U kunt een gedeelte van een rij invoeren INSERT INTO Klant VALUES ("22", "Hendriksen", "De Lange Griet 22", "6932 NN"); (QueryToevoeg1 in test.mdb)

Met het bovenstaande voorbeeld voert u een nieuwe klant in de tabel Klant. De gegevens die in elke tabelkolom moeten worden opgeslagen, staan in het component VALUES. Voor elke kolom moet een waarde worden opgegeven. Als een kolom geen waarde heeft, moet u de waarde NULL gebruiken. De gegevens moeten worden ingevoerd in de volgorde waarin de kolommen in de tabeldefinitie zijn opgenomen. Het is veiliger om en daardoor ook wat lastiger om de INSERT-instructie als volgt te schrijven: INSERT INTO Klant (KlantID, Naam, F:\website\website_nieuw\Downloads\Lezing databases en SQL.doc

blz. 15

Adres, Postcode) VALUES ("22", "Hendriksen", "De Lange Griet 22", "6932 NN"); (QueryToevoeg2 in test.mdb)

In dit voorbeeld gebeurt precies hetzelfde als in de vorige INSERT-instructie, maar deze keer worden alle kolomnamen expliciet genoemd tussen de haakjes achter de tabelnaam. Als de rij wordt ingevoegd, zal het DBMS elk onderdeel in de lijst met kolommen plaatsen bij de bijbehorende waarde in de lijst VALUES. De eerste waarde in VALUES komt overeen met de als eerste opgegeven kolomnaam. De tweede waarde komt overeen met de tweede kolomnaam, enz. Omdat de kolomnamen ook worden gegeven, moeten de waarden in de lijst VALUES in dezelfde volgorde worden ingevoerd, zodat ze in de juiste kolommen terechtkomen. Zoals al is uitgelegd, is het beter om in een INSERT-instructie expliciet de namen van de tabelkolommen op te geven. Met deze syntaxis hoeft u niet alle kolommen te specificeren. Dit betekent dat u waarden kunt toekennen aan een paar kolommen, terwijl u andere kolommen buiten beschouwing laat. Zo is in het volgende voorbeeld de postcode weggelaten. INSERT INTO Klant (KlantID, Naam, Adres ) VALUES ("22", "Hendriksen", "De Lange Griet 22" ); (QueryToevoeg3 in test.mdb)

Let op: U kunt alleen kolommen in een INSERT-instructie overslaan als de tabeldefinitie dit toestaat. Er moet dan aan één van de volgende voorwaarden zijn voldaan: • In de kolom moeten NULL-waarden zijn toegestaan (kolommen zonder waarde); • In de tabeldefinitie is een standaardwaarde opgegeven. Dit betekent dat er automatisch een standaardwaarde wordt toegekend als u geen waarde invoert.

2.6 Gegevens wijzigen Met de instructie UPDATE kunt u gegevens bijwerken. U kunt UPDATE op twee verschillende manieren gebruiken: • U kunt specifieke rijen in een tabel bijwerken; • U kunt alle rijen in een tabel bijwerken Een UPDATE-instructie bestaat uit drie delen: • De tabel die moet worden bijgewerkt; • De kolomnaam of -namen en hun nieuwe waarde • De criteria waarmee bepaald wordt welke rijen moeten worden bijgewerkt. Een eenvoudig voorbeeld: UPDATE Klant SET Adres = "Nieuw adres 22" WHERE KlantID = "22"; (QueryWijzig1 in test.mdb)


blz. 16

De instructie UPDATE begint altijd met de naam van de kolom van de bij te werken tabel. In dit voorbeeld is dat de tabel Klant. De component SET wordt gebruikt om een nieuwe waarde aan een kolom te geven. De instructie UPDATE eindigt met een WHERE-component waarmee wordt bepaald welke rij moet worden bijgewerkt. Zonder deze component zouden alle rijen in de tabel het nieuwe adres worden ingevoerd. Voor het bijwerken van meerdere kolommen gebruikt u een iets andere syntaxis: UPDATE Klant SET Adres = "Nieuw adres 22", Postcode = "8800NG" WHERE KlantID = "22"; (QueryWijzig2 in test.mdb)

Wanneer u gegevens in meerdere kolommen bijwerkt, wordt er maar één SETcomponent gebruikt. Elke opdracht kolom = waarde wordt gescheiden door een komma, behalve bij de laatste kolom. In dit voorbeeld worden de kolommen Adres en Postcode van de Klant 22 bijgewerkt.

2.7 Gegevens verwijderen Met de instructie DELETE kunt u gegevens uit een tabel verwijderen. Er zijn twee manieren om DELETE toe te passen: • U kunt specifieke rijen uit een tabel verwijderen; • U kunt alle rijen uit een tabel verwijderen. Een voorbeeld: DELETE FROM Klant WHERE KlantID = "22"; (QueryVerwijder1 in test.mdb)

De instructie spreekt eigenlijk voor zich. U geeft de naam van de tabel op waaruit gegevens moeten worden verwijderd. De WHERE-component filtert de rijen die u wilt verwijderen. In dit voorbeeld worden de gegevens van klant 22 verwijderd. Als u geen WHERE-component zou gebruiken, zouden alle klanten uit de tabel worden verwijderd. Met DELETE kunt u geen kolomnamen opgeven of jokertekens gebruiken. DELETE verwijdert hele rijen en geen kolommen. Om specifieke kolommen te verwijderen gebruikt u de instructie UPDATE.

2.8 Gegevens opvragen De SQL-instructie die u waarschijnlijk het meest zult gebruiken, is de instructie SELECT. Het doel van deze instructie is informatie uit één of meer tabellen op te vragen. U moet tenminste twee dingen opgeven: • welke gegevens u wilt selecteren en • uit welke tabel u deze gegevens wilt selecteren. SELECT Naam FROM Klant; (SelectQuery1 in Boekenwinkel.mdb) In deze instructie wordt SELECT gebruikt om een enkele kolom met de naam Naam uit de tabel Klanten op te vragen. De gewenste kolomnaam staat rechts van het sleutelwoord SELECT. Na het sleutelwoord FROM staat de naam van de tabel waaruit de gegevens moeten worden opgehaald.


blz. 17

In deze eenvoudige SELECT-instructie zijn geen voorwaarden gesteld. De gegevens zijn niet gefilterd en niet gesorteerd. Met de volgende SELECT-instructie vraagt u vier kolommen op uit de tabel Klanten. SELECT Naam, Adres, Postcode, Plaats FROM Klant; (SelectQuery2 in Boekenwinkel.mdb)

Net als in het vorige voorbeeld vraagt u met de instructie SELECT gegevens uit de tabel Klant. In dit voorbeeld worden vier kolommen opgegeven, die van elkaar gescheiden zijn door een komma. Behalve dat u met SELECT één of meer kolommen uit een tabel kunt opvragen, is het ook mogelijk om alle kolommen op te vragen zonder ze allemaal apart te vermelden. Dit is mogelijk met de asterisk (*). Dit zogenaamde jokerteken gebruikt u in plaats van alle kolomnamen. SELECT * FROM Klant; (SelectQuery3 in Boekenwinkel.mdb)

2.8.1 Gegevens sorteren Met de component ORDER BY van de instructie SELECT kunt u de opgevraagde gegevens sorteren. SELECT * FROM Klant ORDER BY Naam; (QueryOrderBy1 in Boekenwinkel.mdb)

Deze instructie zorgt ervoor dat alle velden uit de tabel Klant worden gesorteerd op volgorde van Naam. Sorteren op meerdere kolommen is ook mogelijk. SELECT * FROM Klant ORDER BY Naam, Adres; (QueryOrderBy2 in Boekenwinkel.mdb)

Deze instructie zorgt ervoor dat alle velden uit de tabel Klant worden gesorteerd op volgorde van Naam en vervolgens op Adres. Dit betekent dat wanneer de naam vaker voorkomt, per naam ook nog eens op adres gesorteerd wordt. U sorteert meerdere kolommen door de kolomnamen gescheiden door een komma op te geven. Gegevens hoeven niet per se in oplopende alfabetische volgorde (van A tot Z) gesorteerd te worden. Hoewel dit de standaardsorteervolgorde is, kunt u de component ORDER BY ook gebruiken om gegevens in aflopend volgorde (van Z tot A) te sorteren. In dat geval moet u het sleutelwoord DESC, afkorting van DESCENDING, gebruiken. SELECT * FROM Klant ORDER BY Naam, Adres DESC; (QueryOrderBy3 in Boekenwinkel.mdb)


blz. 18

Deze instructie zorgt ervoor dat alle velden uit de tabel Klant worden gesorteerd op volgorde van Naam en vervolgens –in aflopende volgorde- op Adres. Wanneer u alle geselecteerde kolommen aflopend wilt sorteren, moeten alle kolommen hun eigen sleutelwoord DESC krijgen.

2.8.2 Gegevens filteren Binnen de instructie SELECT worden gegevens gefilterd door met WHERE specifieke zoekcriteria op te geven. De component komt meteen na de tabelnaam. SELECT * FROM Klant WHERE Naam = "Tiemens"; (SelectQuery4 in Boekenwinkel.mdb)

Het resultaat van deze instructie bevat alle kolommen uit de tabel Klant. Niet alle rijen worden opgevraagd, maar alleen de rijen waarvan de inhoud van het veld Naam gelijk is aan “Tiemens”. SELECT * FROM Klant WHERE Naam = "Tiemens" AND Plaats = "Zevenaar"; (SelectQuery5 in Boekenwinkel.mdb)

Deze instructie is gelijk aan de vorige, maar is uitgebreid met de operator AND. Het DBMS zal nu alleen de rijen tonen die aan beide voorwaarden voldoen. Onderstaand zijn alle operatoren weergegeven die bij component WHERE gebruikt kunnen worden: Operator = <> != < <= !< > >= !> BETWEEN IS NULL*

Beschrijving Gelijk aan Ongelijk aan Ongelijk aan Kleiner dan Kleiner dan of gelijk aan Niet kleiner dan Groter dan Groter dan of gelijk aan Niet groter dan Tussen twee opgegeven waarden Heeft de waarde NULL

AND OR IN NOT LIKE *Let op: NULL betekent geen waarde hebben. Dat is dus iets anders als een veld die de waarde 0 heeft!

2.8.3 Gegevens uit meerdere tabellen selecteren Een van de meest krachtige kenmerken van SQL is het vermogen om tabellen samen te voegen terwijl er gegevens worden opgevraagd. Het samenvoegen van tabellen wordt ook wel een join genoemd. Het is heel eenvoudig om een join te creëren. U moet alle te gebruiken tabellen opsommen en opgeven hoe ze met elkaar in verband staan. F:\website\website_nieuw\Downloads\Lezing databases en SQL.doc

blz. 19

SELECT Naam, Adres, Postcode, Plaats, BoekID, Datum_bestelling, Datum_geleverd FROM Klant, Bestelling WHERE Klant.KlantID = Bestelling.KlantID; (QueryTabellenKoppelen1 in Boekenwinkel.mdb)

De SELECT-instructie begint op dezelfde manier als de instructies die u tot dusver gewend was, namelijk met een opsomming van de kolommen die moeten worden opgehaald. Het verschil is dat de opgevraagde kolommen nu uit twee verschillende tabellen komen, te weten Klant en Bestelling. Achter de FROM-component zijn de tabellen weergegeven, gescheiden door een komma. De twee tabellen worden samengevoegd door middel van de WHERE-component, waarin de KlantID uit de tabel Klant wordt vergeleken met de KlantID uit de tabel Bestelling. De gebruikte kolomnamen zijn hier Klant.KlantID en Bestelling.KlantID. U moet hier de volledige kolomnaam gebruiken, omdat het DBMS anders niet weet naar welke kolom KlantId verwijst. Het lijkt vreemd dat er een WHERE-component nodig is om de tabellen samen te voegen, maar dit heeft een goede reden. Tabellen worden tijdens de uitvoering van een SELECTinstructie samengevoegd. Er staat niets in de definities van de databasetabellen waardoor het DBMS kan weten hoe het tabellen moet samenvoegen. U moet dit zelf doen. Als u twee tabellen samenvoegt, vergelijkt u eigenlijk elke rij in de eerste tabel met elke rij in de tweede tabel. De WHERE-component fungeert als filter om alleen die rijen op te halen die aan een bepaalde voorwaarde voldoen, in dit geval de joinvoorwaarde. Het resultaat van een join tussen twee tabellen waarbij geen voorwaarde is gesteld, levert een zogenaamd cartesisch product op. Het aantal opgehaalde rijen is het aantal rijen in de eerste tabel vermenigvuldigd met het aantal rijen in de tweede tabel. SELECT Naam, Adres, Postcode, Plaats, BoekID, Datum_bestelling, Datum_geleverd FROM Klant, Bestelling; (QueryCartesischProduct in Boekenwinkel.mdb)

Het soort join dat een cartesisch product oplevert, wordt ook wel een cross-join genoemd. De join die u hiervoor hebt gezien, wordt een EQUI-JOIN genoemd, een join die gebaseerd is op een vergelijking tussen twee tabellen. Deze join wordt ook wel INNERJOIN genoemd. In sommige DBMS’en, waaronder MS Access, ziet de syntaxis voor dit soort joins er iets anders uit. Zie het volgende voorbeeld: SELECT Naam, Adres, Postcode, BoekID, Datum_bestelling, Datum_geleverd FROM Klant INNER JOIN Bestelling ON Klant.KlantId = Bestelling.KlantID; (QueryInnerJoinCorrect in Boekenwinkel.mdb)

Deze SELECT-instructie ziet er hetzelfde uit als de vorige, maar de FROM-component is anders. De relatie tussen de twee tabellen wordt in deze component aangeduid met het sleutelwoord INNER JOIN. Met deze syntaxis wordt de joinvoorwaarde gegeven met behulp van de speciale component ON. De voorwaarde die aan ON wordt doorgegeven is dezelfde als anders aan WHERE zou worden doorgegeven. Uiteraard kunt u ook meerdere tabellen koppelen en daar bovendien extra voorwaarden aan verbinden. Een voorbeeld. SELECT Naam, Adres, Postcode, Titel, Schrijver, Datum_bestelling, Datum_geleverd FROM Klant, Bestelling, Boek WHERE Klant.KlantId = Bestelling.KlantID AND Bestelling.BoekID = Boek.BoekID AND Postcode = "6901NJ"; (QueryTabellenKoppelen2 in Boekenwinkel.mdb) F:\website\website_nieuw\Downloads\Lezing databases en SQL.doc

blz. 20

2.9 Een tabel verwijderen U kunt heel eenvoudig een tabel verwijderen met de instructie DROP TABLE. DROP TABLE klant (QueryDropTableKlant in test.mdb)

Met deze instructie verwijdert u de tabel Klant. Er volgt geen bevestiging en u kunt de actie ook niet ongedaan maken. De tabel wordt permanent uit de database verwijderd. Om te voorkomen dat u tabellen verwijderd die een relatie hebben met andere tabellen, kunt u de referentiële integriteit afdwingen bij het maken van relaties tussen tabellen (zie verder blz. 12).

2.10 Gereserveerde woorden van SQL De taal SQL bestaat uit sleutelwoorden. Dit zijn speciale woorden die u kunt gebruiken voor SQL-opdrachten. Zorg ervoor dat u deze woorden niet gebruikt om databases, tabellen, kolommen en andere databaseobjecten een naam te geven. Deze sleutelwoorden moet u dus als gereserveerd beschouwen. Onderstaand overzicht bevat de meest voorkomende gereserveerde woorden die te vinden zijn in grote DBMS’en. Let op het volgende: Elk DBMS beschikt over z’n eigen specifieke sleutelwoorden en niet alle in de tabel opgesomde sleutelwoorden worden gebruikt in alle DBMS’en. Veel DBMS’en hebben de lijst met gereserveerde SQL-woorden uitgebreid met termen die alleen in die implementaties worden gebruikt. De meeste van deze DBMS-specifieke sleutelwoorden zijn niet opgenomen in de tabel. Om zeker te zijn van toekomstige compatibiliteit en overdraagbaarheid is het verstandig geen van de in de tabel weergegeven gereserveerde woorden te gebruiken in de naamgeving van tabellen, kolommen, etc., zelfs niet als ze niet gereserveerd zijn in uw eigen DBMS. ABSOLUTE ACTION ACTIVE ADD AFTER AT AUTHORIZATION AUTO AUTOINC AVG BACKUP BEFORE BEGIN BETWEEN BIT BLOB BOOLEAN BOTH BREAK BROWSE BULK BY BYTES CACHE CASCADE CASCADED CASE

ALL ALLOCATE ALTER AND ANY CHECKPOINT CLOSE CLUSTERED COALESCE COLLATE COLUMN COMMENT COMMIT COMMITTED COMPUTE COMPUTED CONDITIONAL CONFIRM CONNECT CONNECTION CONSTRAINT CONSTRAINTS CONTAINING CONTAINS CONTAINSTABLE CONTINUE CONTROLROW


ARE AS ASC ASCENDING ASSERTION CUBE CURRENT CURSOR DATABASE DATE DATETIME DAY DBCC DEALLOCATE DEBUG DEC DECIMAL DECLARE DEFAULT DELETE DENY DESC DESCENDING DESCRIBE DISCONNECT DISK DISTINCT

blz. 21

CAST CATALOG CHAR CHARACTER CHECK DUMMY DUMP ELSE END ERRLVL ERROREXIT ESCAPE EXCEPT EXCEPTION EXEC EXECUTE EXISTS EXIT EXTERNAL EXTRACT FALSE FETCH FILE FILLFACTOR FILTER FLOAT FLOPPY FOR FOREIGN FOUND FREETEXT FREETEXTTABLE MONTH NAMES NATIONAL NCHAR NEXT NO NOCHECK NONCLUSTERED NONE NOT NULL NULLIF NUMERIC OF OFF OFFSETS ON ONCE ONLY OPEN OPTION OR ORDER OUTER OUTPUT OVER SHUTDOWN

CONVERT COUNT CREATE CROSS CSTRING FROM FULL FUNCTION GENERATOR GET GO GOTO GRANT GROUP HAVING HOLDLOCK HOUR IDENTITY IF IN INACTIVE INDEX INDICATOR INNER INPUT INSERT INT INTEGER INTERSECT INTERVAL INTO IS OVERFLOW PAD PAGES PARAMETER PARTIAL PASSWORD PERCENT PERM PERMANENT PIPE PLAN POSITION PRECISION PREPARE PRIMARY PRINT PRIOR PRIVILEGES PROC PROCEDURE PROCESSEXIT PROTECTED PUBLIC RAISERROR READ READTEXT TEXT


DISTRIBUTED DO DOMAIN DOUBLE DROP ISOLATION JOIN KEY KILL LANGUAGE LAST LEADING LEFT LENGTH LEVEL LIKE LINEND LOAD LOCAL LOGFILE LONG LOWER MANUAL MATCH MAX MERGE MESSAGE MIN MINUTE MIRROREXIT MODULE MONEY REAL REFERENCES REPLICATION RESERV RESERVING RESTORE RESTRICT RETAIN RETURN RETURNS REVOKE RIGHT ROLLBACK ROLLUP RULE SAVE SCHEMA SECOND SECTION SEGMENT SELECT SEQUENCE SET SETUSER SHADOW SHARED USE

blz. 22

SINGULAR SIZE SMALLINT SNAPSHOT SOME SORT SPACE SQL SQLCODE SQLERROR STABILITY STARTING STARTS STATISTICS SUBSTRING SUM SUSPEND TABLE TAPE TEMP TEMPORARY

TEXTSIZE THEN TIME TIMESTAMP TO TOP TRAILING TRAN TRANACTION TRANSLATE TRIGGER TRIM TRUE TRUNCATE UNCOMMITTED UNION UNIQUE UPDATE UPDATETEXT UPPER USAGE


USER USING VALUE VALUES VARCHAR VARIABLE VARYING VIEW VOLUME WAIT WAITFOR WHEN WHERE WHILE WITH WORK WRITE WRITETEXT YEAR ZONE

blz. 23

Lezing databases en SQL

Recommend Documents