SQL Cursus - Deel 2

ArticleCategory:

SoftwareDevelopment

AuthorImage:

[Manuel Soriano]

TranslationInfo:

Original in fr Manuel Soriano

fr to en Dallas L. Gale?

en to nl Egon Willighagen

AboutTheAuthor:

Manuel is software consultant, met een passie voor Linux. Hij werkte al met computers toen een computer nog een kamer vulde en slecht 32 kilobyte geheugen bezat.

Abstract:

In deel twee van deze cursus bekijken we een aantal standaard commando's uit de SQL taal.

ArticleIllustration:

[Illustratie]

ArticleBody:[the article body]

Inleiding

Dit is het tweede deel van de SQL cursus. In dit deel zullen we ons richten op een aantal SQL commando's voor het aanmaken, veranderen en verwijderen van tabellen.

We zullen ons vooral richten op het SELECT commando, dat naar mijn oordeel het belangrijkste commando is.

Ik hoop dat u ook dit tweede deel leuk en interessant vindt.

Een tabel aanmaken

Zoals we in het eerste deel gezien hebben, is het commando voor het maken van tabellen CREATE in combinatie met TABLE. Het commando CREATE zelf, wordt gebruikt om meerdere objecten te maken:

Het CREATE commando geeft de database-server de opdracht iets aan te maken. We zullen later zien wat er gemaakt kan worden en hoe dit gedaan wordt.

Waar we vooral in geïnteresseerd zijn is het maken van een tabel:

Syntax

CREATE TABLE naam ( kolomnaam type [DEFAULT waarde] [NOT NULL], ...
[INHERITS (erft, ...)]
[CONSTRAINT CHECK (test), CHECK (test)] );

Waarbij:

naam: de naam is die wordt toegekend aan de tabel. Hiernaar wordt verwezen door ieder commando
kolomnaam: de naam van de kolom
type: het type van de data (mogelijke typen in MySQL zijn: varchar, char, int, date, time, timestamp, Postgres heeft andere typen die niet overeenkomen met de ANSI SQL standaard)
waarde: de standaard waarde is
INHERITS (erft): Dit is typisch voor Postgres. Het definiëerd overerving van eigenschappen van een andere tabel. De velden van de andere tabel worden hiermee ook in deze tabel aangemaakt.
CONSTRAINT (beperking): de beperkingen waaraan de waarde van een veld moet doen als deze veranderd, bestaat uit een aantal testen (CHECK).
CHECK: de voorwaarde waaraan de waarde van een veld moet voldoen.
Voorbeeld:
CREATE TABLE landen(
landcode integer NOT NULL,
naam varchar(30))
CONSTRAINT foute_landcode CHECK (landcode > 0 AND landcode < 154);

In dit voorbeeld is een tabel aangemaakt waarin een lijst van landen is opgeslagen. Elke keer als we een land toevoegen, moeten er aan de volgende voorwaarden voldaan worden:

Wat is NULL?

Wat betekent NULL eigenlijk? SQL werkt met twee toestanden, data en geen data. SQL maakt gebruik van NULL (=leegte) om onderscheid te maken tussen nul (0) en een string van een paar spaties, dat ook een leeg veld kan representeren. Een voorbeeld:

Stel, er is een tabel facturen gedefiniëerd met de volgende velden: klant, bedrag, factuur_datum, betaald_datum

Als ik een factuur invul, vul ik de velden klant, bedrag en factuur_datum. Maar het veld betaald_datum blijft leeg, ofwel NULL.

Op deze manier is het eenvoudig mogelijk de onbetaalde facturen te selecteren:

SELECT * FROM facturen WHERE betaald_datum IS NULL;

Terecht kan opgemerkt worden dat dit ook bereikt kan worden als nul (0) de waarde van het betaald_datum veld is. Maar dit heeft als nadeel dat het veld niet het type date kan hebben, en er dus geen functies gebruikt kunnen worden die werken met datums.

Voorbeeld bij het vullen van een veld met NULL:

INSERT INTO landen VALUES (15, NULL);
Of:
INSERT INTO landen (landcode) VALUES (27);

Het veld "landnaam ontbreekt, dit betekend dat de waarde van dit veld NULL wordt.

Een tabel veranderen

In PostgreSQL kan het veranderen van tabellen alleen door het toevoegen van nieuwe kolommen.

ALTER TABLE tabelnaam ADD kolomnaam type;

Waarbij:

tabelnaam de naam is van de tabel die veranderd wordt.
kolomnaam de naam is van de nieuwe kolom.
type het type is van de nieuwe kolom (zie CREATE TABLE ).
 

Data toevoegen aan een tabel

Nu gaan we data toevoegen aan een tabel. Deze data wordt als een "record" (VALUES) of als een set van records (SELECT) toegevoegd:

SYNTAX:
INSERT INTO tabel [(kolomnaam, kolomnaam, ...)] VALUES (waarde1, waarde2, ...)
of:
INSERT INTO tabel [(kolomnaam, kolomnaam, ...)] SELECT ....

Er zijn dus twee manieren om data toe te voegen. Of record voor record, of een set van records die het resultaat is van een selectie in een andere tabel.

Als we data toevoegen, vullen we ALTIJD alle kolommen. De kolommen waarvoor niet expliciet een waarde is opgegeven, krijgen de waarde NULL.

Als we in het commando niet specificeren welke kolommen gevuld gaan worden met een waarde ongelijk aan NULL, dan verwacht het database-programma voor alle kolommen een waarde. Een voorbeeld:

INSERT INTO landen VALUES (34, 'Spanje');
Het volgende kan dus niet:
INSERT INTO landen VALUES (34);
Maar het volgende mag weer wel:
INSERT INTO landen (landcode) VALUES (34);

Ik moet aanraden om altijd de kolomnamen op te geven zoals in het derde voorbeeld. Hiermee voorkom je foutmeldingen als je naderhand nieuwe kolommen aan een tabel toevoegt:


INSERT INTO landen VALUES (34, 'Spanje');

INSERT 18301 1

ALTER TABLE landen ADD aantal_inwoners integer

INSERT INTO landen VALUES (31, 'Nederland');

Deze laatste opdracht geeft nu een fout, omdat het aantal inwoners ontbreekt.

Opmerking

PostgreSQL wijkt hierin af van andere SQL database-programma's; het geeft geen foutmelding, maar vult het veld aantal_inwoners met een NULL waarde. Alle andere programma's geven wel een foutmelding.

Maar er is ook nog die andere vorm van INSERT die werkt met selecties. Dit type wordt vaak gebruikt om tijdelijke tabellen te maken, of om tabellen te maken waarop berekeningen uitgevoerd worden.

Het deel dat anders is, is het deel dat de records vult. Deze waarden komen uit het SELECT commando en kan meerdere records terug geven. De syntax van dit SELECT deel van het INSERT commando is hetzelfde als van SELECT zelf.

 

Data selecteren

Eindelijk zijn we zover! :-))

We hebben nu een paar belangrijke SQL commando's behandeld, maar SQL zonder het SELECT commando is als linux zonder LinuxFocus.

Het SELECT commando stelt ons in staat data te bekijken. Maar wel op een hele krachtige manier: zoekopdrachten, combineren van tabellen en berekeningen op de data zijn ook mogelijk.

Een voorbeeld dat alle gegevens in de tabel landen selecteert:

SELECT * FROM landen;
Een tweede voorbeeld:
SELECT a.landnaam, SUM(aantal_inwoners)
FROM landen a, provincies b, gemeenten c
WHERE b.landcode = a.landcode
AND (c.landcode = b.landcode
AND c.provincie_code = b.provincie_code)
AND aantal_inwoners IS NOT NULL
GROUP BY a.landnaam
ORDER BY sum ASC;

Dit verdient uitleg: ik heb het aantal inwoners opgevraagd van alle landen en dit van laag naar hoog op aantal inwoners laten sorteren.

Hiervoor is een kolom (aantal_inwoners) aan de tabel gemeenten toegevoegd.

Dit kan zo gedaan worden:
CREATE TABLE gemeenten (landcode int, 
provincie_code int,
gemeente_code int,
gemeentenaam varchar(60),
aantal_inwoners int);
INSERT INTO gemeenten VALUES (1, 1, 1, 'Land 1, Provincie 1, Gemeente 1', 4532);
INSERT INTO gemeenten VALUES (2, 1, 1, 'Land 2, Provincie 1, Gemeente 1', 7832);
INSERT INTO gemeenten VALUES (3, 1, 1, 'Land 3, Procincie 1, Gemeente 1', 4129);
INSERT INTO gemeenten VALUES (1, 2, 1, 'Land 1, Provincie 2, Gemeente 1', 76529);
INSERT INTO gemeenten VALUES (2, 2, 1, 'Land 2, Provincie 2, Gemeente 1', 9782);
INSERT INTO gemeenten VALUES (3, 2, 1, 'Land 3, Provincie 2, Gemeente 1', 852);
INSERT INTO gemeenten VALUES (1, 3, 1, 'Land 1, Provincie 3, Gemeente 1', 3433);
INSERT INTO gemeenten VALUES (2, 3, 1, 'Land 2, Provincie 3, Gemeente 1', 7622);
INSERT INTO gemeenten VALUES (3, 3, 1, 'Land 3, Provincie 3, Gemeente 1', 2798);
INSERT INTO gemeenten VALUES (1, 1, 2, 'Land 1, Provincie 1, Gemeente 2', 7789);
INSERT INTO gemeenten VALUES (2, 1, 2, 'Land 2, Provincie 1, Gemeente 2', 76511);
INSERT INTO gemeenten VALUES (3, 1, 2, 'Land 3, Provincie 1, Gemeente 2', 98);
INSERT INTO gemeenten VALUES (1, 2, 2, 'Land 1, Provincie 2, Gemeente 2', 123865);
INSERT INTO gemeenten VALUES (2, 2, 2, 'Land 2, Provincie 2, Gemeente 2', 886633);
INSERT INTO gemeenten VALUES (3, 2, 2, 'Land 3, Provincie 2, Gemeente 2', 982345);
INSERT INTO gemeenten VALUES (1, 3, 2, 'Land 1, Provincie 3, Gemeente 2', 22344);
INSERT INTO gemeenten VALUES (2, 3, 2, 'Land 2, Provincie 3, Gemeente 2', 179);
INSERT INTO gemeenten VALUES (3, 3, 2, 'Land 3, Provincie 3, Gemeente 2', 196813);
INSERT INTO gemeenten VALUES (1, 1, 3, 'Land 1, Provincie 1, Gemeente 3', 491301);
INSERT INTO gemeenten VALUES (2, 1, 3, 'Land 2, Provincie 1, Gemeente 3', 166540);
INSERT INTO gemeenten VALUES (3, 1, 3, 'Land 3, Provincie 1, Gemeente 3', 165132);
INSERT INTO gemeenten VALUES (1, 2, 3, 'Land 1, Provincie 2, Gemeente 3', 10640);
INSERT INTO gemeenten VALUES (2, 2, 3, 'Land 2, Provincie 2, Gemeente 3', 65120);
INSERT INTO gemeenten VALUES (3, 2, 3, 'Land 3, Provincie 2, Gemeente 3', 1651462);
INSERT INTO gemeenten VALUES (1, 3, 3, 'Land 1, Provincie 3, Gemeente 3', 60650);
INSERT INTO gemeenten VALUES (2, 3, 3, 'Land 2, Provincie 3, Gemeente 3', 651986);
INSERT INTO gemeenten VALUES (3, 3, 3, 'Land 3, Provincie 3, Gemeente 3', NULL);
COMMIT WORK;

We kunnen dit niet doen met ALTER TABLE, maar we moeten hiervoor UPDATE gebruiken. Maar dat heb ik niet uitgelegd. Dus gebruik simpel "knippen & plakken" dat altijd werkt en ook nu het gewenste resultaat geeft :-))

Nu kunnen we onze QUERY uitvoeren, en het resultaat van deze zoekopdracht zou moeten zijn:

landnaam |    sum
---------+-------
land 1 | 705559
land 2 |1212418
land 3 |2804018
(3 rows)
We kunnen dit controleren met de volgende zoekopdracht:

SELECT sum(aantal_inwoners) FROM gemeenten WHERE landcode = 1;

Het resultaat:
    sum
-------
791986
(1 row)

!!!!!! Dat is nogal een verschil !!!!!!

Als we goed naar de landen tabel kijken, blijkt provincie drie te ontbreken. We kunnen deze toevoegen met de volgende commando's:

INSERT INTO landen VALUES (3, 1, 'Provincie 3, Land 1');
INSERT INTO landen VALUES (3, 2, 'Provincie 3, Land 2');
INSERT INTO landen VALUES (3, 3, 'Provincie 3, Land 3');
En als we dan het commando herhalen, krijgen we:
landnaam |    sum
---------+-------
land 1 | 791986
land 2 |1872205
land 3 |3003629

De derde staat ontbreekt eigenlijk ook nog bij land 2 en land 3.

Als u het niet meer kan volgen, bedenk dan dat het samenvoegen van tabellen EXACT die data meeneemt waarvoor de voorwaarden precies overeenkomen.

Laten we eens kijken naar het eerste deel van deze voorwaarde: WHERE: b.cod_country = a.cod_country.

Deze voorwaarde houdt in dat de data van records uit de tabellen landen en provincies wordt samengevoegd als (en dan alleen!) als de landcode overeenkomt.

Stel de volgende code. Voor deze code niet uit, het is alleen ter illustratie.

CREATE TABLE landen (landcode integer, landnaam 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;
En de volgende de provincie tabel:
CREATE TABLE provincies (provincie_code, int, 
landcode int,
provincienaam varchar(30));
INSERT INTO provincies VALUES (1, 1, 'Provincie 1, Land 1');
INSERT INTO provincies VALUES (2, 1, 'Provincie 2, Land 1');
INSERT INTO provincies VALUES (1, 2, 'Provincie 1, Land 2');
INSERT INTO provincies VALUES (2, 2, 'Provincie 2, Land 2');
INSERT INTO provincies VALUES (1, 3, 'Provincie 1, Land 3');
INSERT INTO provincies VALUES (2, 3, 'Provincie 2, Land 3');
COMMIT WORK;
Zoals je ziet, ontbreekt voor elk land de derde provincie in de provincie tabel. Die gegevens zijn echter wel in de landen tabel opgenomen. Omdat de provincie_code 3 ontbreekt in de provincie tabel, kan deze niet overeenkomen met de provincie_code 3 in de gemeenten tabel. En dus wordt deze niet meegenomen.
        AND (c.landcode = b.landcode
AND c.provincie_code = b.provincie_code)

De provincie_code komt wel in de gemeenten tabel voor, maar NIET in de provincies tabel.

Aan iedereen die dit niet kan volgen: neem een aspirine en ga een eindje wandelen met de hond (als u geen hond heeft, ga dan een eindje wandelen zonder hond). Haal onderweg diep adem en, als u weer terug bent, begint u van voor af aan met het voorbeeld.

Het is namelijk erg belangrijk dat u begrijpt hoe het samenvoegen van tabellen gebeurd. Zonder goede kennis hiervan kan het bedenken van selecties onverwachte resultaten geven.

Oke, nou kunnen we gauw een nivootje hoger en beginnen met de syntax van het SELECT commando.

SELECT [DISTINCT] kolomnaam-1 [AS kolomalias-1] {, kolomnaam-i [AS kolomalias-i]} 
[INTO TABLE tabelnaam]
[FROM lijst_van_tabellen]
[WHERE voorwaarden]
[GROUP BY veldnaam-1 {, veldnaam-i....}]
[ORDER BY veldnaam-1 [ASC | DESC ] [USING optie1 ] {, nom-attribute-i...}]
[UNION {ALL} SELECT ...]
Stap voor stap:
DISTINCT: Hiermee kunnen dubbele records voorkomen worden.
kolomnaam-1, kolomnaam-i: welke kolom we willen selecteren uit de tabellen in de lijst_van_tabellen
AS kolomalias-1, kolomalias-i: een alias voor de kolomnaam, een voorbeeld:
manu= >SELECT landcode FROM landen;
landcode
- -----------
1
2
3
(3 rows)
Nu met een alias:
manu=> SELECT landcode AS landnummer FROM landen;
landnummer
- -----------
1
2
3
(3 rows)
INTO TABLE: maakt het mogelijk om het resultaat in een andere tabel te plaatsen (zie INSERT ... SELECT...)
FROM lijst_van_tabellen: lijst van tabellen waaruit de data gehaald wordt
WHERE voorwaarden: verenigings- en selectievoorwaarden
GROUP BY: maakt het mogelijk te groeperen binnen de data in een kolom
ORDER BY: maakt het mogelijk te sorteren op de data in een kolom: ASC in oplopende volgorde, DESC in aflopende volgorde en USING als de kolom waarop gesorteerd moet worden niet bestaat
UNION ALL SELECT: Hiermee zijn twee selecties samen te voegen, mits de twee tabellen evenveel kolommen hebben.

We hebben al gezien dat het SELECT commando niet alleen data kan selecteren, maar de gegevens ook kan veranderen:


SELECT SUM(salaris * 1.1) - SUM(salaris) AS geld_nodig FROM werknemers;

Deze selectie berekent de hoeveelheid geld die nodig is om de werknemers 10% salarisverhoging te geven.

Laten we eens kijken naar de alle functies die beschikbaar zijn:

COUNT(): geeft het aantal rijen (records) die niet NULL zijn
SUM(): geeft de som van alle getallen in deze kolom
AVG(): geeft het gemiddelde van alle getallen in deze kolom
MIN(): geeft het laagste getal van alle getallen in deze kolom
MAX(): geeft het hoogste getal van alle getallen in deze kolom
FLOAT(int): maakt van een integer een FLOAT8, FLOAT(12345)
FLOAT4(int): maakt van een integer een FLOAT4, FLOAT4(12345)
INT(float): maakt een INT van een FLOAT4, INT(123.456)
LOWER(tekst): maakt van alle hoofdletters kleine letters
UPPER(tekst): maakt van alle letters hoofdletters
LPAD(tekst, long, char): plakt aan de linker kant long characters char van de tekst text
RPAD(tekst, long, char): plakt aan de rechter kant long characters char van de tekst text
LTRIM(tekst, char): verwijderd aan de linker kant van een tekst alle tekens van het character char
RTRIM(tekst, char): verwijderd aan de rechter kant van een tekst alle tekens van het character char
POSITION(string IN tekst): geeft de positie van tekst in string
SUBSTR(tekst,van[,tot]): geeft de tekst in text vanaf positie van tot het eind van de string of tot tot als deze gegeven is
DATETIME(datum, tijd): vertaald een datetime type naar een datum (JJJJ-MM-DD) en een tijd (UU:MM)

Dit zijn enkele van de functies die in SQL beschikbaar zijn. Deze zijn beschreven in ANSI SQL, en maken deel uit van Postgress95.

 

Details van WHERE

We hebben hiervoor al een paar voorbeelden gezien hoe we het WHERE commando voor het SELECT commando kunnen gebruiken:

AND kolom = waarde

Hieronder staat een aantal voorbeelden hoe we verschillende voorwaarden kunnen combineren:

AND, OR, NOT, IN, IN ALL, =, !=, > <, (SELECT....), LIKE en de haakjes zijn belangrijk. Een voorbeeld:

WHERE 
kolom IN (SELECT DISTINCT kolom FROM tabel WHERE ....)
kolom IN ('waarde1','waarde2','waarde3',...)
(kolom = 'waarde' and kolom = 'andere_waarde' OR kolom != 'waarde')
!= betekent hetzelfde als NOT EQUAL

LIKE staat het gebruikt van jokers (% in SQL) in een tekst toe:
WHERE kolom LIKE '%Klaas%'

Het %-teken is de joker, en zorgt in het voorbeeld ervoor dat elke string die als substring "Klaas" heeft, voldoet.
WHERE kolom LIKE 'Klaas%'

is waar als de waarde van kolom begint met "Klaas"
WHERE kolom LIKE '%Klaas'

is waar als de waarde van kolom eindigt op "Klaas"

Er is geen plaats om hier alle mogelijkheden van WHERE te beschrijven. Het enige beperking is de verbeelding van de programmeur (of van het SQL programma).

We verlaten het SELECT commando en bekijken nog kort de twee andere commando's.

 

Het UPDATE commando

Het UPDATE commando maakt het maken van veranderingen mogelijk, bepaald door de condities in het WHERE commando

SYNTAX:
UPDATE tabel SET kolom-1 = uitdrukking-1 
[, kolom-i = uitdrukking-i]
[WHERE voorwaarde]
Waarin:
tabel: de naam is van de te veranderen tabel (slechts één tabel per keer)
kolom: de kolom waarvan de waarden veranderd moeten worden
uitdrukking: de waarde die de kolom moet krijgen. Dit kan statisch zijn of het resultaaat van een functie.
voorwaarde: de voorwaarde waaraan voldaan moet worden voordat er verandert kan worden. Hier gelden dezelfde regels als bij SELECT
 

Het DELETE commando

Het DELETE commando maakt het mogelijk een of meer records in een tabel te wissen.

SYNTAX
DELETE FROM tabel
[WHERE voorwaarde]
Waarin:
tabel: de tabel waarvan records gewist kunnen worden. (slechts één tabel per keer)
voorwaarde: de voorwaarde waaraan voldaan moet worden, voordat de record gewist kan worden. Hier gelden dezelfde regels als bij SELECT.

NB: zonder de WHERE optie worden ALLE gegevens in de tabel gewist!

Referenties

Introductie tot SQL: PostgreSQL installeren, eerste artikel in deze serie.