SQL datadefinitietaal ► We kunnen er het schema van de database mee bepalen: metadata toevoegen, wijzigen en verwijderen uit een database. ► Basiscommando's: CREATE : toevoegen van metagegevens DROP : verwijderen van metagegevens ALTER : wijzigen van metagegevens
CREATE : een tabel creëren CREATE TABLE tablename ( fieldname1 type1 additionalinfo1, fieldname2 type2 additionalinfo2, ... fieldnameN typeN additionalinfoN, additionaltableinfo); Bij elk veld kan additionele informatie worden gegeven: ► NOT NULL voor velden die geen NULL waarden mogen bevatten. ► PRIMARY KEY voor een veld dat de primary key is. ► Default-waarden met DEFAULT. ► GENERATED ALWAYS AS voor berekende velden (vanaf SQL-2003) – in vroegere versies van Firebird COMPUTED BY (). ► REFERENCES voor de definitie van een foreign key. Met A INTEGER REFERENCES B(C) is A een foreign key voor de primary key C van tabel B. Er kan ook additionele tabelinformatie worden opgegeven, nodig wanneer er sleutels voorkomen met meervoudige attributen: ► PRIMARY KEY(column1, column2,...) voor de primaire sleutel. ► FOREIGN KEY (column1, column2,...) REFERENCES othertable (...) voor de definitie van een vreemde sleutel.
CREATE : een tabel creëren Voorbeelden: CREATE TABLE wagen ( nummerplaat CHAR(6) NOT NULL PRIMARY KEY, merk VARCHAR(10) DEFAULT 'Onbekend' NOT NULL, kleur VARCHAR(10) DEFAULT 'Onbekend' NOT NULL, prijs INTEGER NOT NULL, eigenaar VARCHAR(30) REFERENCES chauffeur(naam)); CREATE TABLE wagen ( nummerplaat CHAR(6) NOT NULL, merk VARCHAR(10) DEFAULT 'Onbekend' NOT NULL, kleur VARCHAR(10) DEFAULT 'Onbekend' NOT NULL, prijs INTEGER NOT NULL, eigenaar VARCHAR(30), PRIMARY KEY(nummerplaat), FOREIGN KEY (eigenaar) REFERENCES chauffeur);
Datatypes ► INTEGER: nummers zonder decimaal. Vaak verschillende INTEGER types: SMALLINT (-32768 tot 32767), INTEGER (-2147483648 tot 2147483647), BIGINT (-263 tot 263-1). ► DECIMAL: nummers met decimaal. Gedefiniëerd als DECIMAL(p,s) waarbij p het totale aantal cijfers is en s het aantal cijfers na de komma. Zo kan DECIMAL(4,2) de volgende getallen bevatten: 0.43 ; 34.00 ; 23.78 ; 1.18 ; ... Wanneer je probeert 23.78342 op te slaan, wordt in feite 23.78 opgeslagen. Intern als een soort integer opgeslagen. Soms wordt ook het type NUMERIC hiervoor gebruikt. ► REAL: nummers met een decimaal. De namen FLOAT en DOUBLE PRECISION worden soms gebruikt. Het eerste voor een 32 bit getal, het tweede voor een 64 bit. De precisie gebruikt voor de opslag van de getallen hangt af het getal zelf. In dezelfde kolom kan dus zowel 23.78 als 23.78342 worden opgeslagen. ► VARCHAR voor de opslag van strings met variabele lengte. Met VARCHAR(5) kan dus zowel '' als 'AAAA' als 'AAAAA' worden opgeslagen. 'AAAAAA' kan er niet mee worden opgeslagen. Soms wordt ook CHAR VARYING of CHARACTER VARYING gebruikt. ► CHAR voor de opslag van strings met vaste lengte. Alle strings van het type CHAR(5) zijn exact 5 karakters lang, bijvoorbeeld ' ', 'AAAA ' of 'AAAAA'. Wanneer je er een kortere string in opslaat wordt de string met spaties verlengd tot de juiste lengte. Soms ook CHARACTER genoemd. ► DATE voor een datum. De standaard schrijft voor de opslag van een datum en tijd de volgende datatypes voor: TIMESTAMP voor datum+tijd, DATE voor datum alleen en TIME voor tijd alleen. ► BIT vor de opslag van een aantal bits. Met BIT(2) kan '00', '01', '10' of '11' worden opgeslagen. Wordt niet altijd ondersteund. ► Sinds SQL99 bestaat er ook een BOOL of BOOLEAN datatype: true of false.
CREATE : andere definities CREATE [UNIQUE] [ ASC[ENDING] | [DESC[ENDING] ] INDEX indexname ON tablename (col1 [,col2,...]); CREATE DOMAIN domainname [AS] datatype [DEFAULT value] [NOT NULL] [CHECK search-condition]; search-condition = VALUE operator value | VALUE [NOT] BETWEEN value1 AND value2 | VALUE [NOT] LIKE value | VALUE [NOT] IN (value1[, value2,...]) | VALUE IS [NOT] NULL bvb. CHECK ((VALUE IS NULL) OR (VALUE>10 AND VALUE<100));
CREATE TRIGGER en CREATE PROCEDURE CREATE DATABASE | SCHEMA 'file-specification' USER 'gebruiker' PASSWORD 'paswoord'; CREATE ROLE rolnaam; CREATE SEQUENCE generatornaam;
ALTER : een tabel wijzigen Erg gevarieerde syntax. Belangrijkste: ALTER TABLE tablename ADD fieldname type additionalinfo; ALTER TABLE tablename ALTER COLUMN id TO nr; ALTER TABLE tablename ALTER COLUMN nr POSITION 4; ALTER TABLE tablename ALTER COLUMN nr TYPE VARCHAR(20);
→ het laatste alleen in een aantal gevallen (wanneer geen gegevensverlies) ALTER TABLE tablename DROP field1 [, field2,...];
→ alleen mogelijk wanneer niet gebruikt in constraints, triggers,... ALTER TABLE table ADD [CONSTRAINT naam] PRIMARY KEY (field1 [, field2,...]; ALTER TABLE table ADD [CONSTRAINT naam] FOREIGN KEY (field1 [, field2,...]) REFERENCES othertable; ALTER TABLE table ADD [CONSTRAINT naam] UNIQUE (field1 [, field2,...]; ALTER TABLE table ADD [CONSTRAINT naam] CHECK search-condition; ALTER TABLE tablename DROP CONSTRAINT naam;
Wanneer ALTER niet volstaat
1. Voeg eerst een nieuw tijdelijk veld toe met de gewenste eigenschappen maar met een andere naam. 2. Gebruik het UPDATE commando om de gegevens van het oude veld over te brengen naar het nieuwe. Gebruik eventueel omzettingsfuncties zoals CAST. 3. Na controle op de gekopiëerde data kan je het oude veld verwijderen. 4. Hernoem nu het tijdelijke veld naar de originele naam.
ALTER : andere definities
ALTER INDEX indexname INACTIVE | ACTIVE; ALTER DOMAIN domainname TYPE newtype; ALTER TRIGGER en ALTER PROCEDURE ALTER SCHEMA of ALTER DATABASE
DROP DROP DROP DROP DROP DROP DROP
TABLE naam; DOMAIN naam; INDEX naam; TRIGGER naam; PROCEDURE naam; VIEW naam;
DROP DATABASE;
Oefeningen 1. Schrijf alle commando's om de database te creëren. 2. Wijzig het veld SALARY van een numeriek veld naar een karakterveld. 3. Wijzig het veld SSN van een CHAR(9) naar een numeriek veld en het veld LNAME van VARCHAR(30) naar VARCHAR(25). 4. Maak van ADDRESS een berekend veld. 5. Voeg aan de tabel EMPLOYEE de velden PHONE en EMAIL toe van het type VARCHAR(15) en VARCHAR(200). 6. Indexeer beide nieuwe velden. Het veld EMAIL moet uniek zijn. 7. Voor de liefhebbers: schrijf triggers/procedures die nodig zijn om het veld EMAIL correct te formatteren: steeds met een @ erin. Vooraan een aantal karakters en achteraan eveneens met minimaal één punt erin. 8. Voeg een veld toe EMAILOK (ja/nee veld). Indien men probeert een foutief emailadres op te slaan, zet je dit veld op nee, standaard staat het op ja.
