<--  | Home  | Map  | Index  | Zoek

door Manuel Soriano <manu/at/europa3.com> Vertaald naar het Nederlands door: Samuel Derous <cyberprohet/at/linux.be> Inhoud: Inleiding De eerste stapjes Functies Overzicht van concepten Samenvatting

Inleiding tot SQL -- De installatie van PostgreSQL Kort: Deze korte cursus bestaat uit drie delen, in het eerste deel leggen we enkele basisbegrippen van SQL uiteen door gebruik te maken van een publieke database server, postgreSQL genaamd. In het tweede deel gaan we dieper op de SQL-commando's in. Het derde deel zal ons uiteindelijk enkele geavanceerde opties van SQL-commando's tonen, functies die specifiek tot PostgreSQL behoren en die voor onze programma's interessant kunnen zijn. Uiteindelijk zullen we een klein C programmaatje bekijken dat dit alles met elkaar verbindt. _________________ _________________ _________________

Inleiding

In deze korte inleiding zal ik enkel naar databases (DB) verwijzen. Er zijn andere organisatievormen van data, maar het zou buiten de doelstelling van deze cursus vallen om hen te bespreken.

	land -> staten -> Gemeentes

Maar nooit:

     land -> gemeentes

Als we deze tweede relatie tot stand wilden brengen, moesten we het schema herdefiniëren en het opnieuw compileren...

In een hiërarchische database, zijn de relaties tussen verschillende entiteiten statisch, en kunnen ze enkel gewijzigd worden door het schema van de DB aan te passen en het geheel opnieuw te compileren.

Het basis idee achter relationele databases is nu net om data te linken in de zoekinstantie, zonder dat het nodig is een statische link te creëren, door gebruik te maken van identifiers die toelaten dat één register met een ander wordt verbonden.

Wat ik zonet geschreven heb, heeft je waarschijnlijk hoofdpijn bezorgd. :)

Relationele databasemanagers hebben geen statische links nodig om er voor te zorgen dat we langs de hiërarchie van entiteiten kunnen afzakken, ze gebruiken daarentegen een unieke code die deze entiteiten identificeren, terwijl een tijdelijke relatie wordt aangemaakt op basis van het resultaat op een zoekopdracht.

De identificatie is niet meer dan een code. b.v.: mijn telefoonnummer is niet:

1234567

maar:

34 6 1234567

Het is duidelijk dat mijn telefoonnummer geïdentificeerd wordt door de landcode (34), de staatcode(6) en het eigenlijke toestelnummer (1234567).

• In het geheel van landen, is de code 34 (Spanje) uniek.
• In het geheel van staten, is de code 34-6 (Spanje/Valencia) uniek.
• In het geheel van toestellen is de code 34-6-1234567 (Spanje/ Valencia/ mijn telefoonnumer) uniek.

Laat me de beginselen uiteenzetten voor een eerste voorbeeld dat illustreert wat ik zonet gezegd heb:

Alle gemeentes hebben een code die tot een staat en een land behoren.
Alle staten hebben een code en behoren tot een land.
Alle landen hebben een code.
   
Om de gemeentes binnen een staat te vinden, maken we een relatie tussen de gemeente en de staat, door gebruik te maken van de codes van de gemeente en van het land. Om alle gemeentes in een land te vinden, maken we een relatie tussen de gemeente en het land door de landcode. Deze relaties zijn tijdelijk en bestaan enkel voor de periode van mijn zoekopdracht.

Dit is wat saai en lastig te begrijpen, maar ik hoop het concept van code en toebehoren duidelijker te maken aan de hand van enkele voorbeelden.

Wanneer ik de eerste zoekopdracht aan de DB manager doorgeef, retourneert het me alle gerelateerde data items. Maar welke data ontvang ik werkelijk? Voor de vereniging van land en gemeente, zal ik voor elk land de gerelateerde items van het land ontvangen.

Tijdens mijn eerste zoekopdracht wordt plotseling een nieuwe naamloze entiteit gecreëerd die een replica van landen en gemeentes bevat. Deze nieuwe entiteit verdwijnt opnieuw na het einde van mijn zoekopdracht.

Voordien waren we gewend om een reeks data een "bestand" te noemen. Deze bestaan uit registers, en elk register heeft een veld. Wel, in een relationele database is een "bestand" gekend als een tabel, en een tabel bevat rijen en elke rij bevat kolommen, dit is eigenlijk maar een kleine kosmetische verandering. ;-)

Het is ook nuttig om hier te vermelden dat sommige hiërarchische databasemanagers SQL introduceren als toegangstaal, maar dat is maar een detail. SQL is bijna een exclusief eigendom van relationele databasemanagers.

Om het gebruik van SQL te illustreren zullen we gebruik maken van de relationele databasemanager PostgeSQL. Het is niet geheel compatibel met de regels van SQL, maar het nadert het dicht genoeg voor onze doeleinden, en het is tevens een zeer goede manager voor de zwaardere taken.

Laat ik even kort het installatieproces beschrijven, daar het doel van dit artikel SQL zelf is. Download eerst de broncodes en alle patches die beschikbaar zijn van www.postgresql.org. Pak de bronnen (tar -zxvf) uit naar een directory, cd postgresql-6.3.

cd src

./configure --prefix=/the/desired/path

make all >& make.log &

tail -f make.log

export PATH=$PATH:/the/desired/path/pgsql/bin
export MANPATH=$MANPATH:/the/desired/path/pgsql/man
export PGLIB=/the/desired/path/pgsql/lib
export PGDATA=/the/desired/path/pgsql/data

initdb
createdb test
psql test
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL

type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: postgres

test=>

Dit is de prompt voor postgres, vanaf nu kunnen we commando's intypen.

test=>create table mytest (field1 varchar(10));
CREATE

mytest=>insert into mytest values ('hello');
INSERT number 1

mytest=>commit work;
NOTICE:EndTransactionBlock and not inprogress/abort state
END

mytest=>select * from mytest;
field1
------
hello
(1 row)

mytest=>drop table mytest;
DROP

mytest=>Ctrl-d

We hebben de SQL console al verlaten.

Als je problemen hebt met het correct compileren en installeren van Postgres95 bekijk dan het INSTALL bestand in de root directory van de uitgave.

Laat ik nog een commentaar leveren; een relationele databaseserver bestaat over het algemeen uit volgende delen:

1. Data toegangslaag
2. SQL verwerkingslaag
3. een SQL ontlederlaag
4. Een communicatielaag

Als clients verbinden we ons met de vierde laag; we verzenden SQL commando's die dan aan de ontlederlaag worden doorgegeven. Deze vertaalt de commando's en, bij afwezigheid van fouten, verzendt de verzoeken naar de tweede laag. Al het verwerken en zoeken gebeurt op dat niveau, uiteraard in samenwerking met de data toegangslaag: de data wordt verzameld en fouten worden aan de client meegedeeld via de communicatielaag. De SQL verwerkingslaag verwezenlijkt een dialoog met de client terwijl het de correcte overdracht van data en de beheersing van transacties en interrupts bewerkstelligt.

De eerste stapjes

Nu zal ik wat zojuist beschreven werd met een voorbeeld illustreren, laten we drie tabellen (of bestanden) bouwen:

File: landen.sql
create table landen (cod_Land integer, naam varchar(30));

insert into landen values (1, 'land 1');
insert into landen values (2, 'land 2');
insert into landen values (3, 'land 3');
commit work;

File: staten.sql
create table staten (cod_Staat int,
                     cod_land int,
                     nam_Staat varchar(30));

insert into staten values (1, 1, 'Staat 1, Land 1');
insert into staten values (2, 1, 'Staat 2, Land 1');
insert into staten values (1, 2, 'Staat 1, Land 2');
insert into staten values (2, 2, 'Staat 2, Land 2');
insert into staten values (1, 3, 'Staat 1, Land 3');
insert into staten values (2, 3, 'Staat 2, Land 3');
commit work;

File: gemeentes.sql
create table gemeentes (cod_Land int,
                        cod_staat int,
                        cod_gemeente int,
                        nam_gemeente varchar(60));

insert into gemeentes values (1, 1, 1, 'Gemeente 1, Staat 1, Land 1');
insert into gemeentes values (2, 1, 1, 'Gemeente 2, Staat 1, Land 1');
insert into gemeentes values (3, 1, 1, 'Gemeente 3, Staat 1, Land 1');
insert into gemeentes values (1, 2, 1, 'Gemeente 1, Staat 2, Land 1');
insert into gemeentes values (2, 2, 1, 'Gemeente 2, Staat 2, Land 1');
insert into gemeentes values (3, 2, 1, 'Gemeente 3, Staat 2, Land 1');
insert into gemeentes values (1, 3, 1, 'Gemeente 1, Staat 3, Land 1');
insert into gemeentes values (2, 3, 1, 'Gemeente 2, Staat 3, Land 1');
insert into gemeentes values (3, 3, 1, 'Gemeente 3, Staat 3, Land 1');
insert into gemeentes values (1, 1, 2, 'Gemeente 1, Staat 1, Land 2');
insert into gemeentes values (2, 1, 2, 'Gemeente 2, Staat 1, Land 2');
insert into gemeentes values (3, 1, 2, 'Gemeente 3, Staat 1, Land 2');
insert into gemeentes values (1, 2, 2, 'Gemeente 1, Staat 2, Land 2');
insert into gemeentes values (2, 2, 2, 'Gemeente 2, Staat 2, Land 2');
insert into gemeentes values (3, 2, 2, 'Gemeente 3, Staat 2, Land 2');
insert into gemeentes values (1, 3, 2, 'Gemeente 1, Staat 3, Land 2');
insert into gemeentes values (2, 3, 2, 'Gemeente 2, Staat 3, Land 2');
insert into gemeentes values (3, 3, 2, 'Gemeente 3, Staat 3, Land 2');
insert into gemeentes values (1, 1, 3, 'Gemeente 1, Staat 1, Land 3');
insert into gemeentes values (2, 1, 3, 'Gemeente 2, Staat 1, Land 3');
insert into gemeentes values (3, 1, 3, 'Gemeente 3, Staat 1, Land 3');
insert into gemeentes values (1, 2, 3, 'Gemeente 1, Staat 2, Land 3');
insert into gemeentes values (2, 2, 3, 'Gemeente 2, Staat 2, Land 3');
insert into gemeentes values (3, 2, 3, 'Gemeente 3, Staat 2, Land 3');
insert into gemeentes values (1, 3, 3, 'Gemeente 1, Staat 3, Land 3');
insert into gemeentes values (2, 3, 3, 'Gemeente 2, Staat 3, Land 3');
insert into gemeentes values (3, 3, 3, 'Gemeente 3, Staat 3, Land 3');
commit work;

Een bestand met SQL-commando's kan als volgt vanuit pgsql uitgevoerd worden:

\i bestandsnaam

We zouden de commando's ook kunnen invoeren door simpelweg te knippen en te plakken.
Laten we nu eens zien welke gemeentes er beschikbaar zijn:

manu=> select * from gemeentes;

cod_land|cod_staat|cod_gemeente|nam_gemeente
-----------+---------+----------+----------------------------
1|        1|         1|Gemeente 1, Staat 1, Land 1
2|        1|         1|Gemeente 2, Staat 1, Land 1
3|        1|         1|Gemeente 3, Staat 1, Land 1
1|        2|         1|Gemeente 1, Staat 2, Land 1
2|        2|         1|Gemeente 2, Staat 2, Land 1
3|        2|         1|Gemeente 3, Staat 2, Land 1
1|        3|         1|Gemeente 1, Staat 3, Land 1
2|        3|         1|Gemeente 2, Staat 3, Land 1
3|        3|         1|Gemeente 3, Staat 3, Land 1
1|        1|         2|Gemeente 1, Staat 1, Land 2
2|        1|         2|Gemeente 2, Staat 1, Land 2
3|        1|         2|Gemeente 3, Staat 1, Land 2
1|        2|         2|Gemeente 1, Staat 2, Land 2
2|        2|         2|Gemeente 2, Staat 2, Land 2
3|        2|         2|Gemeente 3, Staat 2, Land 2
1|        3|         2|Gemeente 1, Staat 3, Land 2
2|        3|         2|Gemeente 2, Staat 3, Land 2
3|        3|         2|Gemeente 3, Staat 3, Land 2
1|        1|         3|Gemeente 1, Staat 1, Land 3
2|        1|         3|Gemeente 2, Staat 1, Land 3
3|        1|         3|Gemeente 3, Staat 1, Land 3
1|        2|         3|Gemeente 1, Staat 2, Land 3
2|        2|         3|Gemeente 2, Staat 2, Land 3
3|        2|         3|Gemeente 3, Staat 2, Land 3
1|        3|         3|Gemeente 1, Staat 3, Land 3
2|        3|         3|Gemeente 2, Staat 3, Land 3
3|        3|         3|Gemeente 3, Staat 3, Land 3
(27 rows)
manu=>

Er zijn 27 rijen en pgsql wacht nu een volgend commando af, probeer het volgende:

manu=> select * from landen, staten;

cod_land   |naam     |cod_staat|cod_land|nam_staat
-----------+---------+---------+-----------+------------------
1|land 1|           1|          1|Staat 1, Land 1
2|land 2|           1|          1|Staat 1, Land 1
3|land 3|           1|          1|Staat 1, Land 1
1|land 1|           2|          1|Staat 2, Land 1
2|land 2|           2|          1|Staat 2, Land 1
3|land 3|           2|          1|Staat 2, Land 1
1|land 1|           1|          2|Staat 1, Land 2
2|land 2|           1|          2|Staat 1, Land 2
3|land 3|           1|          2|Staat 1, Land 2
1|land 1|           2|          2|Staat 2, Land 2
2|land 2|           2|          2|Staat 2, Land 2
3|land 3|           2|          2|Staat 2, Land 2
1|land 1|           1|          3|Staat 1, Land 3
2|land 2|           1|          3|Staat 1, Land 3
3|land 3|           1|          3|Staat 1, Land 3
1|land 1|           2|          3|Staat 2, Land 3
2|land 2|           2|          3|Staat 2, Land 3
3|land 3|           2|          3|Staat 2, Land 3
(18 rows)

18 rijen??? We hebben drie landen en zes staten ingegeven, die allen één enkel land identificeren. Hoe is het dan mogelijk dat we 18 rijen krijgen?

Het laatste commando heeft een eenheid van twee tabellen uitgevoerd, we hebben een relatie gemaakt tussen de tabel met landen en de tabel met gemeentes, daar we niet gevraagd hebben sommige relaties niet op te nemen, gaf pgsql ons alle mogelijke rijen van landen die met alle rijen van staten zijn verenigd, i.e. 3 voor landen, maal 6 voor staten wat een totaal van 18 oplevert. Dit resultaat is duidelijk niet logisch en onbruikbaar, we hadden beter het volgende gedaan:

manu=> select * from landen, staten
manu-> where landen.cod_land = staten.cod_land;

cod_land|naam     |cod_staat|cod_land|nam_staat
-----------+---------+---------+-----------+------------------
1|land 1|        1|          1|Staat 1, Land 1
1|land 1|        2|          1|Staat 2, Land 1
2|land 2|        1|          2|Staat 1, Land 2
2|land 2|        2|          2|Staat 2, Land 2
3|land 3|        1|          3|Staat 1, Land 3
3|land 3|        2|          3|Staat 2, Land 3
(6 rows)

Zes rijen, dat lijkt er al wat meer op, of niet?

Ja, er zijn zes gemeentes en elke gemeente is in een land. Het is redelijk om een aantal rijen te krijgen die identiek zijn aan het aantal gemeentes, daar land een kwalificatievereiste van gemeentes is. We hebben zonet een relatie gemaakt tussen de tabel landen met de tabel gemeentes door de landcode. Herinner je dat landen een code hebben, en dat gemeentes een code van het land waartoe ze behoren hebben?

Waarom landen.cod_land = staten.cod_land?

De landcode in de tabel van landen is cod_land en in de tabel van gemeentes ook, daarom:

cod_land = cod_land

select * from landen, staten
where cod_land = cod_land;

ERROR:  Column cod_land is ambiguous

Het is ook mogelijk om aliassen voor kolommen te gebruiken:

manu=> select * from landen a, staten b
manu-> where a.cod_land = b.cod_land;

cod_land|naam     |cod_staat|cod_land|nam_staat
-----------+---------+---------+-----------+------------------
1|land 1|          1|          1|Staat 1, Land 1
1|land 1|          2|          1|Staat 2, Land 1
2|land 2|          1|          2|Staat 1, Land 2
2|land 2|          2|          2|Staat 2, Land 2
3|land 3|          1|          3|Staat 1, Land 3
3|land 3|          2|          3|Staat 2, Land 3
(6 rows)

Wat geeft de manager af?: cod_land, naam, cod_staat, cod_land en nam_staat.

Daar we gezocht hebben op "select * from landen, staten", waar * een joker is dat staat voor ALLES, kregen we de twee kolommen voor landen en de drie voor gemeentes. Nu zouden we graag wat specifieker zijn:

manu=> select a.cod_land, cod_staat, naam, nam_staat
manu-> from landen a, staten b
manu-> where a.cod_land = b.cod_land;

cod_land|cod_staat|naam     |nam_staat
-----------+---------+---------+------------------
1|        1|land    1|Staat 1, Land 1
1|        2|land    1|Staat 2, Land 1
2|        1|land    2|Staat 1, Land 2
2|        2|land    2|Staat 2, Land 2
3|        1|land    3|Staat 1, Land 3
3|        2|land    3|Staat 2, Land 3
(6 rows)

In het laatste commando vroegen we specifiek voor de landcode, de staatcode en de naam van het land en de staat. Merk op dat sommige namen van kolommen voldoen (a.cod_land) terwijl andere niet voldoen (nam_staat), dit komt doordat cod_land herhaald wordt in beide tabellen, terwijl nam_staat enkel voorkomt in staten.
Unieke kolomnamen hebben geen extra kwalificatievereisten nodig.

Laten we de dingen wat moeilijker maken:

manu=> select a.cod_land, cod_staat, naam, nam_staat
manu-> from landen a, staten b
manu-> where a.cod_land = b.cod_land
manu-> and a.cod_land = 3;

cod_land|cod_staat|name     |nam_staat
-----------+---------+---------+------------------
3|        1|land    3|Staat 1, Land 3
3|        2|land    3|Staat 2, Land 3
(2 rows)

Deze keer beperkten we de zoekopdracht tot alleen het land met nummer 3.

Functies

Hier volgt een voorbeeld van het gebruik van de rijteller-functie count():

select count(*) from gemeentes;

count
-----
27
(1 row)

Het geeft de waarde van het aantal rijen die de tabel van gemeentes bevatten, af;

manu=> select cod_land, count(*) from staten
manu-> group by cod_land;

cod_land|count
--------+-----
1       |    2
2       |    2
3       |    2
(3 rows)

Het geeft het aantal rijen af die de identieke landcode bevatten, dat is de reden voor het gebruik van cod_land.

Een nog beter voorbeeld:

manu=> select naam, count(*) from landen a, staten b
manu-> where a.cod_land = b.cod_land
manu-> group by naam;

naam     |count
---------+-----
land 1   |    2
land 2   |    2
land 3   |    2
(3 rows)

We krijgen nog steeds dezelfde drie rijen, maar de informatie die afgegeven wordt is helderder.

Wel, tot nu toe hebben we enkel nog maar een inleiding gegeven, een opwarmertje :-)

Overzicht van concepten

We hebben tot nog toe maar enkele basisconcepten van SQL besproken.. Het meest relevante hier is het concept van SQL zelf. We werken niet langer met concrete data, maar met data-entiteiten. Een data-entiteit is een abstract concept van databases. Gesimplificeerd zou het begrepen kunnen worden als "GEEF ENKEL EEN GEDEELTE AF VAN ALLES WAT BESCHIKBAAR IS".

We hebben verschillende commando's gezien:

CREATE TABLE	Dit commando maakt een tabel met zijn kolommen aan.
DROP TABLE	Dit verwijdert een tabel
SELECT	Dit commando is de basis van SQL, het laat ons toe een tijdelijke tabel te maken die enkel de noodzakelijke data items bevat. SELECT kan functies of complexe verklaringen, maar ook sub_selects, als parameters opnemen. select count(*) from staten where cod_land in (select cod_land from landen); count ----- 27 (1 row)
BEGIN WORK	Dit is een ander fundamenteel commando. Het vertelt de DB manager om alle veranderingen sinds BEGIN WORK af te geven. In onze specifieke DB manager markeert BEGIN WORK de initialisatie van een transactie, in andere managers wordt het begin van een transactie gemarkeerd door het eerste commando dat iets in de database aanpast. In PostgreSQL zullen alle commando's die data aanpassen meteen uitgevoerd worden, tenzij het voorafgegaan wordt door een BEGIN WORK. OPMERKING: commando's die het schema van een database aanpassen, voeren een COMMIT WORK uit, wat er voor zorgt dat als een transactie geopend wordt, en zo'n commando wordt uigevoerd, onze transactie meteen gesloten wordt, en het onmogelijk wordt om een ROLLBACK WORK te lanceren. Terwijl een gebruiker een open transactie heeft, kan hij aangeven welke toegangstype door andere gebruikers voor zijn data toepasbaar is: Aangepaste data Originele data voorafgegaan door de transactie Blokkeer toegang tot data
COMMIT WORK	Dit sluit een transactie af terwijl de geïntroduceerde aanpassingen worden toegepast. Het commando ROLLBACK WORK retourneert de data in eeen staat voorafgaand aan de huidige transactie.

Het concept van transacties is zeer belangrijk daar het ons toelaat om naar een vorige toestand te gaan in geval van een fout. Laten we deze toepassing eens proberen, eerst een "rollback work" om enige voorgaande transacties af te sluiten:

manu=> select * from landen;

cod_landen|naam
-----------+---------
          1|land 1
          2|land 2
          3|land 3
         (3 rows)

Er zijn drie rijen,

begin work; 

Laat de transactie beginnen

insert into landen values (5, 'land Niet Waar'); 

We hebben een rij toegevoegd, laten we ons nu verzekeren dat alle rijen er nog zijn

manu=> select * from landen;
cod_land|name
--------+----------------
       1|land 1
       2|land 2
       3|land 3
       5|Land Niet Waar
      (4 rows)
      

De andere rijen zijn er nog. Volgende

rollback work; 

Dit zorgt ervoor dat we uit de transactie gaan.

manu=> select * from landen;
cod_land|name
--------+---------
       1|land 1
       2|land 2
       3|land 3
     (3 rows)
     

Na het controleren van het aantal rijen, merken we dat het terugging naar de originele drie rijen.

Samenvatting

Ondanks onze soms droge stijl, hebben we toch een inleiding in SQL kunnen geven en een relationele database kunnen installeren.
SQL laat ons toe een abstractielaag voor onze data op te bouwen en laat ons toe ze te controleren volgens onze noden.

Vanuit wat we tot nog toe geleerd hebben, zou iemand kunnen vragen: hoe gebruik ik SQL binnen een toepassing?

Het antwoord zal stap voor stap aan u onthuld worden, in ons derde artikel zullen we een korte C toepassing bespreken die gebruik maakt van SQL.

2004-03-04, generated by lfparser version 2.43