3. Inleiding tot de taal SQL

In dit hoofdstuk presenteren we de eerste SQL-commando’s waarmee u een enkele tabel kunt aanmaken en beheren. We geven hier doorgaans een vereenvoudigde versie weer. De volledige syntaxis is te vinden in de Firebird-referentiegidsen (zie paragraaf 2.2).

Een database wordt gebruikt door mensen met uiteenlopende vaardigheden:

de databasebeheerder is doorgaans iemand die de taal SQL en databases beheerst. Hij is degene die de tabellen aanmaakt, aangezien deze handeling doorgaans slechts eenmalig wordt uitgevoerd. In de loop van de tijd kan het nodig zijn dat hij de structuur ervan aanpast. Een database is een verzameling tabellen die door relaties met elkaar zijn verbonden. Het is de databasebeheerder die deze relaties definieert. Hij is ook degene die rechten toekent aan de verschillende gebruikers van de database. Zo bepaalt hij bijvoorbeeld dat een bepaalde gebruiker het recht heeft om de inhoud van een tabel te bekijken, maar niet om deze te wijzigen.
De gebruiker van de database is degene die de gegevens tot leven brengt. Afhankelijk van de rechten die door de databasebeheerder zijn toegekend, zal hij gegevens toevoegen, wijzigen of verwijderen in de verschillende tabellen van de database. Hij zal deze gegevens ook analyseren om er informatie uit te halen die nuttig is voor de goede werking van het bedrijf, de overheid, enz.

In paragraaf 2.6 hebben we de editor SQL van de tool [IB-Expert] gepresenteerd. Dit is de tool die we gaan gebruiken. Laten we enkele punten nog eens op een rijtje zetten:

De editor SQL is te openen via de menuoptie [Tools/SQL Editor], of via de toets [F12]

Vervolgens verschijnt er een venster [SQL Editor] waarin we een opdracht SQL kunnen invoeren:

De bovenstaande schermafbeelding wordt vaak weergegeven met de onderstaande tekst:

SQL> select * from BIBLIO

3.1. De gegevenstypen van Firebird

Bij het aanmaken van een tabel moeten we aangeven welk gegevenstype een kolom in de tabel kan bevatten. Hieronder worden de meest voorkomende Firebird-typen weergegeven. Let wel: deze gegevenstypen kunnen per SGBD verschillen.

`SMALLINT`	geheel getal in het bereik [-32768, 32767]: 4
`INTEGER`	geheel getal in het domein [–2 147 483 648, 2 147 483 647]: -100
`NUMERIC(n,m) DECIMAL(n,m)`	reëel getal met n cijfers, waarvan m achter de komma NUMERIC(5,2): -100,23, +027,30
`FLOAT`	benaderd reëel getal met 7 significante cijfers: 10,4
`DOUBLE PRECISION`	reëel getal, afgerond op 15 significante cijfers: -100.89
`CHAR(N) CHARACTER(N)`	een tekenreeks van precies N tekens. Als de opgeslagen tekenreeks minder dan N tekens bevat, wordt deze aangevuld met spaties. CHAR(10): 'ANGERS ' (4 spaties aan het einde)
`VARCHAR(N) CHARACTER VARYING(N)`	reeks van maximaal N tekens VARCHAR(10): 'ANGERS'
`DATE`	een datum: '2006-01-09' (formaat YYYY-MM-DD)
`TIME`	een tijd: '16:43:00' (formaat HH:MM:SS)
`TIMESTAMP`	zowel datum als tijd: '2006-01-09 16:43:00' (formaat YYYY-MM-DD HH:MM:SS)

Met de functie CAST() kunt u indien nodig van het ene type naar het andere converteren. Om een waarde V, die is gedeclareerd als type T1, om te zetten naar het type T2, schrijft men: CAST(V,T2). De volgende typeconversies zijn mogelijk:

van getal naar tekenreeks. Deze typewijziging gebeurt impliciet en vereist geen gebruik van de functie CAST. Zo vereist de bewerking 1 + '3' geen conversie van het teken '3'. Het resultaat is het getal 4.
DATE, TIME, TIMESTAMP naar tekenreeksen en vice versa. Zo
TIMESTAMP naar TIME of DATE en omgekeerd

In een tabel kan een rij kolommen zonder waarde bevatten. Men zegt dan dat de waarde van de kolom de constante NULL is. Men kan de aanwezigheid van deze waarde testen met behulp van de operatoren

IS NULL / IS NOT NULL

3.2. Een tabel aanmaken

Om te ontdekken hoe je een tabel aanmaakt, beginnen we met het aanmaken van een tabel in de modus [Design] met IBExpert. Hiervoor volgen we de methode die wordt beschreven in paragraaf 2.3. Zo maken we de volgende tabel aan:

Deze tabel wordt gebruikt om de door een bibliotheek aangeschafte boeken te registreren. De velden hebben de volgende betekenis:

Name	Type	Beperking	Betekenis
`ID`	`INTEGER`	`Primary Key`	`Identifiant du livre`
`TITRE`	`VARCHAR(30)`	`NOT NULL UNIQUE`	`Titre du livre`
`AUTEUR`	`VARCHAR(20)`	`NOT NULL`	`Son auteur`
`GENRE`	`VARCHAR(30)`	`NOT NULL`	`Son genre (Roman, Poésie, Policier, BD, ..)`
`ACHAT`	`DATE`	`NOT NULL`	`Date d'achat du livre`
`PRIX`	`NUMERIC6,2)`	`NOT NULL`	`Son prix`
`DISPONIBLE`	`CHAR(1)`	`NOT NULL`	`Est-il disponible ? O (oui), N (non)`

Deze tabel, die is aangemaakt met de tool IBEXPERT als wizard, had ook rechtstreeks kunnen worden aangemaakt met de opdrachten SQL. Om deze opdrachten te bekijken, hoeft u alleen maar het tabblad [DDL] van de tabel te raadplegen:

De code SQL waarmee de tabel [BIBLIO] is aangemaakt, is als volgt:

SET SQL DIALECT 3;

SET NAMES ISO8859_1;


CREATE TABLE BIBLIO (
    ID INTEGER NOT NULL,
    TITRE VARCHAR(30) NOT NULL,
    AUTEUR VARCHAR(20) NOT NULL,
   GENRE VARCHAR(30) NOT NULL,
   ACHAT DATE NOT NULL,
   PRIX NUMERIC(6,2) NOT NULL,
   DISPONIBLE  CHAR(1) NOT NULL
);

ALTER TABLE BIBLIO ADD CONSTRAINT UNQ1_BIBLIO UNIQUE (TITRE);
ALTER TABLE BIBLIO ADD CONSTRAINT PK_BIBLIO PRIMARY KEY (ID);

regel 1: eigenaar Firebird – geeft het gebruikte dialectniveau SQL aan
regel 2: Firebird-eigenaar – geeft de gebruikte tekenset aan
regels 6 - 14: standaard SQL: maakt de tabel BIBLIO aan door de naam en het type van elk van de kolommen te definiëren.
regel 16: standaard SQL: maakt een beperking aan die aangeeft dat de kolom TITRE geen duplicaten toestaat
regel 17: standaard SQL: geeft aan dat de kolom [ID] de primaire sleutel van de tabel is. Dit betekent dat twee rijen in de tabel niet dezelfde waarde voor ID mogen hebben. Dit lijkt sterk op de beperking [UNIQUE NOT NULL] voor de kolom [TITRE], en in feite had de kolom TITRE als primaire sleutel kunnen dienen. De huidige trend is om primaire sleutels te gebruiken die geen betekenis hebben en die worden gegenereerd door de SGBD.

De syntaxis van het commando [CREATE TABLE] is als volgt:

syntaxe

CREATE TABLE tabel (nom_colonne1 type_colonne1 contrainte_colonne1, nom_colonne2 type_colonne2 contrainte_colonne2, ..., nom_colonnen type_colonnen contrainte_colonnen, overige beperkingen)

action

maakt de tabel table aan met de aangegeven kolommen

`nom_colonnei`	naam van de aan te maken kolom i
`type_colonnei`	gegevenstype van kolom i: char(30) numeric(6,2) date timestamp ...
`contrainte_colonnei`	voorwaarde waaraan de gegevens in kolom i moeten voldoen. Hier volgen enkele voorbeelden: PRIMARY KEY: de kolom is een primaire sleutel. Dit betekent dat twee rijen in de tabel nooit dezelfde waarde in deze kolom hebben en dat bovendien een waarde in deze kolom verplicht is. Een primaire sleutel dient voornamelijk om een rij op unieke wijze te identificeren. NOT NULL : er zijn geen nulwaarden toegestaan in de kolom. UNIQUE : geen enkele waarde mag meerdere keren in de kolom voorkomen. CHECK (voorwaarde): de waarde in de kolom moet voldoen aan de voorwaarde.
`autres contraintes`	hier kunnen - beperkingen op meerdere kolommen: check(col1>col2) - beperkingen op vreemde sleutels

De tabel [BIBLIO] had ook kunnen worden opgebouwd met de volgende volgorde SQL:

CREATE TABLE BIBLIO (
    ID INTEGER NOT NULL PRIMARY KEY,
    TITRE VARCHAR(30) NOT NULL UNIQUE,
    AUTEUR VARCHAR(20) NOT NULL,
   GENRE VARCHAR(30) NOT NULL,
   ACHAT DATE NOT NULL,
   PRIX NUMERIC(6,2) NOT NULL,
   DISPONIBLE  CHAR(1) NOT NULL
);

Laten we dit eens laten zien. We nemen deze volgorde over in een SQL-editor (F12) om een tabel aan te maken die we [BIBLIO2] zullen noemen:

Na uitvoering moet de transactie worden bevestigd om het resultaat in de database te kunnen zien:

Zodra dit is gebeurd, verschijnt de tabel in de database:

Door op de naam te dubbelklikken, krijgt u toegang tot de structuur ervan:

We zien hier inderdaad de definitie die we voor de tabel [BIBLIO2] hebben gemaakt

3.3. Een tabel verwijderen

De opdracht SQL om een tabel te verwijderen luidt als volgt:

`syntaxe`	DROP TABLE tabel
`action`	Verwijdert [table]

Om de tabel [BIBLIO2] te verwijderen die we zojuist hebben aangemaakt, voeren we nu de volgende opdracht SQL uit:

en bevestigen we dit met [Commit]. De tabel [BIBLIO2] wordt verwijderd:

3.4. Een tabel vullen

We voegen een rij toe aan de tabel [BIBLIO] die we zojuist hebben aangemaakt:

Bevestig de toevoeging van de rij met [Commit] en klik vervolgens met de rechtermuisknop op de toegevoegde rij:

en vragen we, zoals hierboven weergegeven, om de ingevoegde regel naar het klembord te kopiëren in de vorm van een opdracht SQL INSERT. Open vervolgens een willekeurige teksteditor en plak (Paste) wat we zojuist hebben gekopieerd. We krijgen dan de volgende QZXW2HTMLP-code:

INSERT INTO BIBLIO (ID,TITRE,AUTEUR,GENRE,ACHAT,PRIX,DISPONIBLE) VALUES (1,'Candide','Voltaire','Essai','18-OCT-1985',140,'o');

De syntaxis van een SQL-insert-opdracht is als volgt:

`syntaxe`	insert into tabel [(colonne1, colonne2, ..)] waarden (waarde1, waarde2, ....)
`action`	voegt een rij (waarde1, waarde2, ..) toe aan table. Deze waarden worden toegewezen aan colonne1, colonne2, ... indien deze aanwezig zijn, anders aan de kolommen van de tabel in de volgorde waarin ze zijn gedefinieerd.

Om nieuwe rijen in de tabel [BIBLIO] in te voegen, voeren we de volgende opdrachten INSERT in de editor SQL in. Voer deze opdrachten één voor één uit en bevestig ze met [Commit]. Gebruik de knop [New Query] om naar de volgende opdracht INSERT te gaan.

insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (2,'Les fleurs du mal','Baudelaire','Poème','01-jan-78',120,'n');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (3,'Tintin au Tibet','Hergé','BD','10-nov-90',70,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (4,'Du côté de chez Swann','Proust','Roman','08-dec-78',200,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (5,'La terre','Zola','roman','12-jun-90',50,'n');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (6,'Madame Bovary','Flaubert','Roman','12-mar-88',130,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (7,'Manhattan transfer','Dos Passos','Roman','30-aug-87',320,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (8,'Tintin en Amérique','Hergé','BD','15-may-91',70,'o');

Nadat we de verschillende opdrachten SQL hebben bevestigd, krijgen we de volgende tabel:

3.5. Een tabel raadplegen

3.5.1. Inleiding

Typ in de editor SQL de volgende opdracht:

en voeren we deze uit. We krijgen het volgende resultaat:

Met de opdracht SELECT kunt u de inhoud van tabellen in de database raadplegen. Deze opdracht heeft een zeer uitgebreide syntaxis. We behandelen hier alleen de syntaxis waarmee één enkele tabel kan worden opgevraagd. Het gelijktijdig opvragen van meerdere tabellen komt later aan bod. De syntaxis van de opdracht SQL [SELECT] is als volgt:

syntaxe

SELECT [ALL|DISTINCT] [*|expression1 alias1, expression2 alias2, ...]

FROM table

action

geeft de waarden van expressioni weer voor alle rijen van de tabel. expressioni kan een kolom of een complexere uitdrukking zijn. Het symbool * verwijst naar alle kolommen. Standaard worden alle tabelrijen (ALL) weergegeven. Als DISTINCT aanwezig is, worden de geselecteerde identieke rijen slechts één keer weergegeven. De waarden van expressioni worden weergegeven in een kolom met de titel expressioni of aliasi, indien deze is gebruikt.

Voorbeelden:

SQL > select titre, auteur from biblio

SQL> select titre,prix from biblio

SQL> select titre TITRE_DU_LIVRE, prix PRIX_ACHAT from biblio

Hierboven hebben we aliassen (TITRE_DU_LIVRE, PRIX_ACHAT) gekoppeld aan de gevraagde kolommen.

3.5.2. Weergave van rijen die aan een voorwaarde voldoen

`syntaxe`	SELECT .... WHERE condition
`action`	alleen de regels die voldoen aan de voorwaarde condition worden weergegeven

Voorbeelden

SQL> select titre,prix from biblio where prix>100

SQL> select titre,prix,genre from biblio where genre='Roman'

Een van de boeken heeft het genre 'roman' en niet 'Roman'. We gebruiken de functie upper, die een tekenreeks in hoofdletters omzet, om alle romans te selecteren.

SQL> select titre,prix,genre from biblio where upper(genre)='ROMAN'

We kunnen voorwaarden combineren met behulp van logische operatoren

`AND`	ET logisch
`OR`	OU logica
`NOT`	Logische negatie

SQL> select titre,prix,genre from biblio where upper(genre)='ROMAN' and prix<100

SQL> select titre,genre from biblio

SQL> select titre,genre from biblio where upper(genre)='ROMAN' or upper(genre)='BD'

SQL> select titre,genre from biblio where not( upper(genre)='ROMAN' or upper(genre)='BD')

SQL> select titre,achat from biblio

SQL>select titre,achat from biblio where achat>'31-dec-1987'

SQL> select titre,prix from biblio where prix between 100 and 150

3.5.3. Regels weergeven in een bepaalde volgorde

Aan de bovenstaande syntaxis kan een clausule ORDER BY worden toegevoegd die de gewenste weergavevolgorde aangeeft:

`syntaxe`	SELECT .... ORDER BY expression1 [asc\|desc], expression2 [asc\|dec], ...
`action`	De regels die uit de selectie voortkomen, worden weergegeven in de volgorde van 1: oplopende volgorde (asc / ascending, de standaardinstelling) of aflopende volgorde (desc / descending) van expression1 2: bij gelijke waarden van expression1 wordt de weergave bepaald door de waarden van expression2 enz.

Voorbeelden:

SQL>select titre, genre,prix,achat from biblio order by achat desc

SQL>select titre, genre,prix,achat from biblio order by prix

SQL>select titre, genre,prix,achat from biblio order by genre desc

SQL >select titre, genre,prix,achat from biblio order by genre desc, prix

SQL>select titre, genre,prix,achat from biblio order by genre desc, prix desc

3.6. Rijen uit een tabel verwijderen

`syntaxe`	DELETE FROM table [WHERE condition]
`action`	verwijdert de regels van table en controleert daarbij of condition aanwezig is. Als deze laatste ontbreekt, worden alle regels verwijderd.

Voorbeelden:

SQL> select titre from biblio

De twee onderstaande opdrachten worden na elkaar uitgevoerd:

SQL> delete from biblio where titre='Candide'

SQL> select titre from biblio

3.7. De inhoud van een tabel wijzigen

`syntaxe`	update table set kolom1 = uitdrukking1, kolom2 = uitdrukking2, ... [where condition]
`action`	Voor de rijen van table die condition controleren (alle rijen als er geen voorwaarde is), krijgt colonnei de waarde expressioni.

Voorbeelden:

SQL> select genre from biblio

Alle soorten worden in hoofdletters geschreven:

SQL> update biblio set genre=upper(genre)

We controleren:

SQL> select genre from biblio

We geven de prijzen weer:

SQL> select genre,prix from biblio;

De prijs van de romans stijgt met 5%:

SQL> update biblio set prix=prix*1.05 where genre='ROMAN';

We controleren:

SQL> select genre,prix from biblio

3.8. Definitieve update van een tabel

Wanneer er wijzigingen in een tabel worden aangebracht, voert Firebird deze in feite uit op een kopie van de tabel. Deze wijzigingen kunnen vervolgens definitief worden gemaakt of ongedaan worden gemaakt met de commando's COMMIT en ROLLBACK.

`syntaxe`	COMMIT
`action`	maakt de updates die sinds de laatste COMMIT in de tabellen zijn aangebracht definitief.

`syntaxe`	ROLLBACK
`action`	maakt alle wijzigingen ongedaan die sinds de laatste COMMIT in de tabellen zijn aangebracht.

Remarque

Een COMMIT wordt impliciet uitgevoerd op de volgende momenten:

a) Bij het afsluiten van Firebird

b) Na elke opdracht die de structuur van de tabellen beïnvloedt: CREATE, ALTER, DROP.

Voorbeelden

In de editor SQL wordt de database in een bekende toestand gebracht door alle bewerkingen te valideren die sinds de laatste COMMIT of ROLLBACK zijn uitgevoerd:

SQL> commit

We vragen de lijst met effecten op:

SQL> select titre from biblio

Een titel verwijderen:

SQL> delete from biblio where titre='La terre'

Controle:

SQL> select titre from biblio

De titel is inderdaad verwijderd. Nu maken we alle wijzigingen ongedaan die sinds de laatste COMMIT / ROLLBACK zijn aangebracht:

SQL> rollback

Controle:

SQL> select titre from biblio

De verwijderde titel is weer terug. Laten we nu de prijslijst opvragen:

SQL> select prix from biblio

Laten we aannemen dat alle prijzen op nul zijn gezet.

SQL> update biblio set prix=0

Laten we de prijzen controleren:

SQL> select prix from biblio

Laten we de wijzigingen die aan de basis zijn aangebracht ongedaan maken:

SQL> rollback

en laten we de prijzen nogmaals controleren:

SQL> select prix from biblio

We hebben de oorspronkelijke prijzen teruggevonden.

3.9. Rijen uit de ene tabel toevoegen aan een andere tabel

Het is mogelijk om rijen uit de ene tabel aan een andere tabel toe te voegen, mits hun structuren compatibel zijn. Om dit te laten zien, maken we eerst een tabel [BIBLIO2] aan met dezelfde structuur als [BIBLIO].

In de database-explorer van IBExpert dubbelklikken we op de tabel [BIBLIO] om toegang te krijgen tot het tabblad [DDL]:

Op dit tabblad vindt u de lijst met opdrachten SQL waarmee de tabel [BIBLIO] kan worden gegenereerd. Kopieer de volledige code naar het klembord (CTRL-A, CTRL-C). Vervolgens roepen we een tool op met de naam [Script Executive] waarmee een lijst met opdrachten SQL kan worden uitgevoerd:

Er verschijnt een teksteditor waarin we (CTRL-V) de tekst kunnen plakken die we eerder in het klembord hadden geplaatst:

Een lijst met opdrachten wordt vaak een script genoemd. Met [Script Executive] kunnen we zo’n script uitvoeren, terwijl de editor SQL slechts de uitvoering van één opdracht tegelijk toestond. Met het huidige script SQL kunnen we de tabel [BIBLIO] aanmaken. Laten we ervoor zorgen dat het een tabel met de naam [BIBLIO2] aanmaakt. Hiervoor volstaat het om [BIBLIO] te veranderen in [BIBLIO2]:

SET SQL DIALECT 3;

SET NAMES ISO8859_1;

CREATE TABLE BIBLIO2 (
    ID          INTEGER NOT NULL,
    TITRE       VARCHAR(30) NOT NULL,
    AUTEUR      VARCHAR(20) NOT NULL,
    GENRE       VARCHAR(20) NOT NULL,
    ACHAT       DATE NOT NULL,
    PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
    DISPONIBLE  CHAR(1) NOT NULL
);

ALTER TABLE BIBLIO2 ADD CONSTRAINT UNQ1_BIBLIIO2 UNIQUE (TITRE);

ALTER TABLE BIBLIO2 ADD CONSTRAINT PK_BIBLIIO2 PRIMARY KEY (ID);

Laten we dit script uitvoeren met de onderstaande knop [Run Script]:

Het script wordt uitgevoerd:

en we kunnen de nieuwe tabel zien in de database-explorer:

Als we dubbelklikken op [BIBLIO2] om de inhoud te controleren, zien we dat deze leeg is, wat normaal is:

Met een variant van de opdracht SQL INSERT kun je rijen uit een andere tabel in een tabel invoegen:

`syntaxe`	INSERT INTO table1 [(colonne1, colonne2, ...)] SELECT kolom a, kolom b, ... FROM table2 WHERE condition
`action`	De rijen van table2 die condition controleren, worden toegevoegd aan table1. De kolommen colonnea, colonneb, ... van table2 worden in volgorde toegewezen aan kolom1, kolom2, ... van table1 en moeten daarom van een compatibel type zijn.

Laten we teruggaan naar de editor SQL:

en geven we de volgende opdracht SQL:

SQL> insert into BIBLIO2 select * from BIBLIO where upper(genre)='ROMAN'

waardoor in [BIBLIO2] alle regels van [BIBLIO] worden ingevoegd die bij een roman horen. Nadat de opdracht SQL is uitgevoerd, bevestigen we deze met een [Commit]:

SQL> commit

Laten we vervolgens de gegevens in de tabel [BIBLIO2] bekijken:

SQL> select * from BIBLIO2

3.10. Een tabel verwijderen

`syntaxe`	DROP TABLE table
`action`	verwijdert table

Voorbeeld: de tabel BIBLIO2 wordt verwijderd

SQL> drop table BIBLIO2

De wijziging wordt bevestigd:

SQL> commit

In de database-explorer wordt de weergave van de tabellen vernieuwd:

We zien dat de tabel [BIBLIO2] is verwijderd:

3.11. De structuur van een tabel wijzigen

`syntaxe`	ALTER TABLE table [ ADD nom_colonne1 type_colonne1 contrainte_colonne1] [ALTER nom_colonne2 TYPE type_colonne2] [DROP nom_colonne3] [ADD contrainte] [DROP CONSTRAINT nom_contrainte]
`action`	maakt het mogelijk om tabelkolommen toe te voegen (ADD), te wijzigen (ALTER) en te verwijderen (DROP). De syntaxis nom_colonnei type_colonnei contrainte_colonnei is dezelfde als die van CREATE TABLE. Men kan ook tabelbeperkingen toevoegen of verwijderen.

Voorbeeld: Laten we de volgende twee SQL-opdrachten achtereenvolgens uitvoeren in de SQL-editor

SQL > alter table biblio add nb_pages numeric(4), alter genre type varchar(30)

SQL> commit

Laten we in de database-explorer de structuur van de tabel [BIBLIO] controleren:

De wijzigingen zijn verwerkt. Laten we eens kijken hoe de inhoud van de tabel is veranderd:

SQL> select * from biblio

De nieuwe kolom [NB_PAGES] is aangemaakt, maar bevat geen waarden. Laten we deze kolom verwijderen:

SQL> alter table biblio drop nb_pages

SQL> commit

Laten we de nieuwe structuur van de tabel [BIBLIO] controleren:

De kolom [NB_PAGES] is inderdaad verdwenen.

3.12. De weergaven

Het is mogelijk om een gedeeltelijk overzicht van één of meerdere tabellen te krijgen. Een weergave gedraagt zich als een tabel, maar bevat zelf geen gegevens. De gegevens worden uit andere tabellen of weergaven gehaald. Een weergave biedt verschillende voordelen:

Een gebruiker is mogelijk alleen geïnteresseerd in bepaalde kolommen en rijen van een bepaalde tabel. Dankzij de weergave kan hij alleen die rijen en kolommen zien.
De eigenaar van een tabel wil mogelijk slechts beperkte toegang verlenen aan andere gebruikers. Met een weergave kan hij dat doen. De gebruikers die hij heeft geautoriseerd, hebben alleen toegang tot de weergave die hij heeft gedefinieerd.

3.12.1. Een weergave aanmaken

`syntaxe`	CREATE VIEW nom_vue AS SELECT kolom1, kolom2, ... FROM table WHERE condition [ WITH CHECK OPTION ]
`action`	maakt de weergave nom_vue aan. Dit is een tabel met als structuur kolom1, kolom2, ... van table en als rijen de rijen van table die voldoen aan condition (alle rijen als er geen voorwaarde is)
`WITH CHECK OPTION`	Deze optionele clausule geeft aan dat invoegingen en bijwerkingen in de weergave geen rijen mogen aanmaken die de weergave niet zou kunnen selecteren.

Opmerking De syntaxis van CREATE VIEW is in feite complexer dan hierboven weergegeven en maakt het met name mogelijk om een weergave te maken op basis van meerdere tabellen. Hiervoor volstaat het dat de query SELECT betrekking heeft op meerdere tabellen (zie volgend hoofdstuk).

Voorbeelden

We maken op basis van de tabel biblio een weergave die alleen de romans bevat (selectie van rijen) en alleen de kolommen titel, auteur en prijs (selectie van kolommen):

SQL> create view romans as select titre,auteur,prix from biblio where upper(genre)='ROMAN';

SQL> commit

In de database-explorer verversen we de weergave (F5). Er verschijnt een weergave:

We kunnen de bij de weergave behorende volgorde SQL achterhalen. Hiervoor dubbelklikken we op de weergave [ROMANS]:

Een weergave is als een tabel. Ze heeft een structuur:

en een inhoud:

Een weergave wordt gebruikt als een tabel. Je kunt er query's op uitvoeren. Hier zijn enkele voorbeelden om mee te spelen in de editor:

SQL> select * from romans

SQL> insert into biblio values (10,'Le père Goriot','Balzac','Roman','01-sep-91',200,'o')

Is de nieuwe roman zichtbaar in de weergave [ROMANS]?

SQL> select * from romans

Laten we nog iets anders dan een roman toevoegen aan de tabel [BIBLIO]:

SQL> insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (11,'Poèmes saturniens','Verlaine','Poème','02-sep-92',200,'o');

Laten we de tabel [BIBLIO] controleren:

SQL> select titre, auteur from BIBLIO

Laten we de weergave [ROMANS] eens bekijken:

SQL> select titre, auteur from ROMANS

Het toegevoegde boek staat niet in de weergave [ROMANS] omdat het geen upper(genre)='ROMAN' had.

3.12.2. Een weergave bijwerken

Het is mogelijk om een weergave bij te werken, net zoals bij een tabel. Alle tabellen waaruit de gegevens voor de weergave worden gehaald, worden door deze update beïnvloed. Hier volgen enkele voorbeelden:

SQL> insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (13,'Le Rouge et le Noir','Stendhal','Roman','03-oct-92',110,'o')

SQL> select * from romans

SQL> select titre, auteur from biblio

Er wordt een regel verwijderd uit de weergave [ROMANS]:

SQL> delete from ROMANS where titre='Le Rouge et le Noir'

SQL> select * from romans

SQL> select auteur, titre from BIBLIO

De verwijderde regel uit de weergave [ROMANS] is ook verwijderd uit de tabel [BIBLIO]. We verhogen nu de prijs van de boeken uit de weergave [ROMANS]:

SQL> update romans set prix=prix*1.05

We controleren dit in [ROMANS]:

SQL> select * from romans

Wat was de impact op de tabel [BIBLIO]?

SQL> select titre, auteur, prix from biblio

Ook in [BIBLIO] is het aantal romans inderdaad met 5% gestegen.

3.12.3. Een weergave verwijderen

`syntaxe`	DROP VIEW nom_vue
`action`	verwijdert de weergave met de naam

Voorbeeld

SQL> drop view romans

SQL> commit

In de database-explorer kun je de weergave (F5) vernieuwen om te zien dat de weergave [ROMANS] verdwenen is:

3.13. Gebruik van groepsfuncties

Er zijn functies die, in plaats van op elke rij van een tabel te werken, op groepen rijen werken. Dit zijn voornamelijk statistische functies waarmee we het gemiddelde, de standaardafwijking, enz. van de gegevens in een kolom kunnen berekenen.

`syntaxe1`	SELECT f1, f2, .., fn FROM table [ WHERE condition ]
`action`	berekent de statistische functies fi over alle tabelrijen en controleert daarbij op de eventuele condition.

syntaxe2

SELECT f1, f2, .., fn FROM table

[ WHERE condition ]

[ GROUP BY expr1, expr2, ..]

action

Het trefwoord GROUP BY zorgt ervoor dat de rijen van de tabel in groepen worden verdeeld. Elke groep bevat de rijen waarvoor de uitdrukkingen expr1, expr2, ... dezelfde waarde hebben.

Voorbeeld: GROUP BY genre plaatst boeken met hetzelfde genre in één groep. De clausule GROUP BY auteur,genre zou boeken met dezelfde auteur en hetzelfde genre in dezelfde groep plaatsen. De clausule WHERE condition verwijdert eerst de rijen uit de tabel die niet aan de voorwaarde voldoen. Vervolgens worden de groepen gevormd door de clausule GROUP BY. De functies fi worden vervolgens berekend voor elke groep rijen.

`syntaxe3`	SELECT f1, f2, .., fn FROM table [ WHERE condition ] [ GROUP BY expression] [ HAVING condition_de_groupe]
`action`	De clausule HAVING filtert de groepen die worden gevormd door de clausule GROUP BY. Deze is dus altijd gekoppeld aan de aanwezigheid van de clausule GROUP BY. Voorbeeld: GROUP BY genre HAVING genre!='ROMAN'

De beschikbare statistische functies fi zijn de volgende:

`AVG(expression)`	gemiddelde van uitdrukking
`COUNT(expression)`	aantal regels waarvoor uitdrukking een waarde heeft
`COUNT(*)`	totaal aantal rijen in de tabel
`MAX(expression)`	maximumwaarde van de uitdrukking
`MIN(expression)`	minimale waarde van de uitdrukking
`SUM(expression)`	som van de uitdrukking

Voorbeelden

SQL> select prix from biblio

Gemiddelde prijs? Maximale prijs? Minimale prijs?

SQL> select avg(prix), max(prix), min (prix) from biblio

SQL> select titre, prix,genre from biblio

Gemiddelde prijs van een roman? Maximale prijs?

SQL> select avg(prix) moyenne, max(prix) prix_maxi from biblio where upper(genre)='ROMAN'

Hoeveel BD?

SQL> select count(*) from biblio where upper(genre)='BD'

Hoeveel romans kosten minder dan 100 F?

SQL> select count(*) from biblio where upper(genre)='ROMAN' and prix<100

SQL> select genre, prix from biblio

Aantal boeken en gemiddelde prijs per boek voor boeken van hetzelfde genre?

SQL> select upper(genre) GENRE,avg(prix) PRIX_MOYEN,count(*) NOMBRE from biblio group by upper(genre)

Dezelfde vraag, maar dan alleen voor boeken die geen romans zijn:

SQL>
select upper(genre) GENRE,avg(prix) PRIX_MOYEN,count(*) NOMBRE
from biblio
group by upper(genre)
having upper(GENRE)!='ROMAN'

Dezelfde vraag, maar dan alleen voor boeken van minder dan 150 F:

SQL> 
select upper(genre) GENRE,avg(prix) PRIX_MOYEN,count(*) NOMBRE
from biblio
where prix<150
group by upper(genre)
having upper(GENRE)!='ROMAN'

Dezelfde vraag, maar we houden alleen de groepen over met een gemiddelde prijs per boek >100 F

SQL> 
select upper(genre) GENRE, avg(prix) PRIX_MOYEN,count(*) NOMBRE
from biblio
group by upper(genre)
having avg(prix)>100

3.14. Het SQL-script voor de tabel ' ' aanmaken

De taal SQL is een standaardtaal die met talrijke SGBD-scripts kan worden gebruikt. Om van het ene SGBD-script naar het andere te kunnen overschakelen, is het handig om een database of slechts bepaalde elementen daarvan te exporteren in de vorm van een SQL-script dat, wanneer het in een andere SGBD wordt uitgevoerd, de in het script geëxporteerde elementen kan herscheppen.

We gaan hier de tabel [BIBLIO] exporteren. Laten we de optie [Extract Metadata] nemen:

Hierboven is te zien dat je de database moet selecteren waarvan je elementen wilt exporteren. De optie start een wizard:

`1`	waar het script moet worden gegenereerd SQL: in een bestand (File) in het klembord (Clipboard) in de Script Executive-tool
`2`	bestandsnaam indien de optie [File] is geselecteerd
`3`	wat moet worden geëxporteerd
`4`	knoppen om de te exporteren objecten te selecteren (->) of deselecteren (<-)

Als we de volledige database zouden willen exporteren, zouden we de bovenstaande optie [Extract All] aanvinken. We willen alleen de tabel BIBLIO exporteren. Hiervoor selecteren we met [4] de tabel [BIBLIO] en met [2] geven we een bestand op:

Als we het hierbij laten, wordt alleen de structuur van de tabel [BIBLIO] geëxporteerd. Om de inhoud ervan te exporteren, moeten we het tabblad [Data Tables] gebruiken:

Laten we [1] gebruiken om de tabel [BIBLIO] te selecteren:

Gebruik [2] om het script SQL te genereren:

Laten we het aanbod accepteren. Hierdoor kunnen we het gegenereerde script bekijken in het bestand [biblio.sql]:

/******************************************************************************/
/****          Gegenereerd door IBExpert 17-06-2004 22-01-20        06 15:06:13 ****/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES ISO8859_1;

CREATE DATABASE 'D:\data\serge\travail\2005-2006\polys\sql\DBBIBLIO.GDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET ISO8859_1;



/******************************************************************************/
/****                                                                 Tabellen ****/
/******************************************************************************/



CREATE TABLE BIBLIO (
ID          INTEGER NOT NULL,
TITRE       VARCHAR(30) NOT NULL,
AUTEUR      VARCHAR(20) NOT NULL,
GENRE       VARCHAR(30) NOT NULL,
ACHAT       DATE NOT NULL,
PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 'Baudelaire', 'POèME', '1978-01-01', 120, 'n');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (3, 'Tintin au Tibet', 'Hergé', 'BD', '1990-11-10', 70, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (4, 'Du côté de chez Swann', 'Proust', 'ROMAN', '1978-12-08', 220.5, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (5, 'La terre', 'Zola', 'ROMAN', '1990-06-12', 55.13, 'n');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (6, 'Madame Bovary', 'Flaubert', 'ROMAN', '1988-03-12', 143.33, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (7, 'Manhattan transfer', 'Dos Passos', 'ROMAN', '1987-08-30', 352.8, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (8, 'Tintin en Amérique', 'Hergé', 'BD', '1991-05-15', 70, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (10, 'Le père Goriot', 'Balzac', 'Roman', '1991-09-01', 210, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (11, 'Poèmes saturniens', 'Verlaine', 'Poème', '1992-09-02', 200, 'o');

COMMIT WORK;



/******************************************************************************/
/****                                                     Unieke beperkingen ****/
/******************************************************************************/

ALTER TABLE BIBLIO ADD CONSTRAINT UNQ1_BIBLIO UNIQUE (TITRE);


/******************************************************************************/
/****                                                           Primaire sleutels ****/
/******************************************************************************/

ALTER TABLE BIBLIO ADD CONSTRAINT PK_BIBLIO PRIMARY KEY (ID);

regels 1 tot en met 3 zijn opmerkingen
de regels 5 tot en met 12 zijn eigen SQL van Firebird
de overige regels zijn standaard SQL-code die zou moeten kunnen worden uitgevoerd in een SGBD met de gegevenstypen die zijn gedeclareerd in de tabel BIBLIO.

Laten we dit script opnieuw uitvoeren binnen Firebird om een tabel BIBLIO2 aan te maken, die een kloon zal zijn van de tabel BIBLIO. Laten we hiervoor [Script Executive] (Ctrl-F12) gebruiken:

Laten we het zojuist gegenereerde script [biblio.sql] laden:

Laten we het aanpassen zodat alleen het gedeelte voor het aanmaken van de tabel en het invoegen van rijen overblijft. De tabel wordt hernoemd naar [BIBLIO2]:

CREATE TABLE BIBLIO2 (
    ID          INTEGER NOT NULL,
    TITRE       VARCHAR(30) NOT NULL,
    AUTEUR      VARCHAR(20) NOT NULL,
    GENRE       VARCHAR(30) NOT NULL,
    ACHAT       DATE NOT NULL,
    PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
    DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO BIBLIO2 (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 'Baudelaire', 'POèME', '1978-01-01', 120, 'n');
...

COMMIT WORK;

Laten we dit script uitvoeren:

We kunnen in de database-explorer controleren of de tabel [BIBLIO2] inderdaad is aangemaakt en of deze de verwachte structuur en inhoud heeft: