SQL Cursus - Deel 2

ArticleCategory:

AuthorImage:

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:

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:

• gebruikers: CREATE USER
• tabellen: CREATE TABLE
• indices: CREATE INDEX
• projecties: CREATE VIEW

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:

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:

• De landcode kan niet NULL zijn. Als dit geprobeerd wordt, geeft de database client een foutmelding:
  

  ExecAppend: Fail to add null value in not null attribute landcode
     

• De landcode moet groter zijn dan 0 en kleiner dan 154. Als er een landcode opgegeven wordt die hieraan niet voldoet, volgt de foutmelding:
  

  ExecAppend: rejected due to CHECK constraint foute_landcode
  

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);

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:

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:

INSERT INTO tabel [(kolomnaam, kolomnaam, ...)] VALUES (waarde1, waarde2, ...)

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');

INSERT INTO landen VALUES (34);

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:

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;

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.

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)

    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');

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;

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;

        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 ...] 

manu= >SELECT landcode FROM landen;
    landcode
- -----------
          1
          2
          3
(3 rows)          
          

manu=> SELECT landcode AS landnummer FROM landen;
   landnummer
- -----------
       1
       2
       3
(3 rows)
          

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

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:

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')

WHERE kolom LIKE '%Klaas%'

WHERE kolom LIKE 'Klaas%'

WHERE kolom LIKE '%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

UPDATE tabel SET kolom-1 = uitdrukking-1 
                 [, kolom-i = uitdrukking-i] 
       [WHERE voorwaarde] 

Het DELETE commando

DELETE FROM tabel
       [WHERE voorwaarde] 

Referenties

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