SQL manipulatietaal ► We kunnen er data mee toevoegen, wijzigen en verwijderen uit een database. ► Basiscommando's: INSERT : toevoegen van gegevens DELETE : verwijderen van gegevens UPDATE : wijzigen van gegevens
INSERT syntax : 2 mogelijkheden Eén rij data invoegen: INSERT INTO table [(columns)] VALUES (value_list); INSERT INTO chauffeur VALUES ('Jan Deconinck', '30 Nov 1979'); INSERT INTO chauffeur (naam, geboortedatum) VALUES ('Els Degroot','18 May 1983');
Data toevoegen vanuit een andere tabel: INSERT INTO table1 [(columns)] SELECT matching_columns FROM table2; INSERT INTO FAVKLEUR (naam, favkleur) SELECT naam, kleur FROM chauffeur JOIN wagen ON naam=eigenaar;
INSERT en automatische velden Automatisch genummerd veld: niet in SQL standaard. Verschillende oplossingen naargelang de leverancier. Een gewoon auto-increment veld kan niet gebruikt worden in transacties. Beter: een sequence (Oracle - vroeger generator genaamd in Interbase/Firebird): buiten transacties! Sinds Firebird 2.1 (SQL 99 compliant): INSERT INTO table (ID, ...)
VALUES (NEXT VALUE FOR sequence, ....);
Voorheen: SELECT GEN_ID(naamgenerator,1) FROM RDB$DATABASE; Geeft je een gegarandeerd uniek nummer. Met een trigger (PSQL) kan je er bovendien voor zorgen dat het geautomatiseerd wordt meegegeven indien de gebruiker het niet mee geeft: CREATE TRIGGER naamtrigger FOR table ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (new.ID IS NULL) THEN new.ID=GEN_ID(naamgenerator,1); END IDENTITY: werkt als een SEQUENCE achter de schermen. Indien portabiliteit belangrijk: geen van alle gebruiken!
INSERT en defaults Velden gedefiniëerd met een default waarde: default alleen bij INSERT en dan nog uitsluitend wanneer de naam van dat veld niet gebruikt wordt in dat INSERT statement. Wanneer merk en kleur bvb allebei 'Onbekend' hebben als default waarde, dan wordt deze waarde ingevoerd met onderstaand statement: INSERT INTO wagen (nummerplaat) VALUES ('AAA456');
Het volgende zal proberen NULL waarden in te voeren: INSERT INTO wagen (nummerplaat, merk, kleur) VALUES ('AAA456', NULL, NULL);
INSERT en triggers Triggers automatiseren. Bvb om velden automatisch te bevolken, invoer van velden te controleren etc. Bijvoorbeeld: CREATE TRIGGER trgnaam1 FOR table ACTIVE BEFORE INSERT POSITION 0 AS begin IF (new.NAME IS NULL) THEN EXIT; IF (strlen(new.NAME)>1) THEN new.NAME=upper(substring(new.NAME FROM 1 FOR 1)) || lower(substring(new.NAME FROM 2)); ELSE new.NAME=upper(new.NAME); end
INSERT : andere Procedures zijn erg vergelijkbaar met triggers wat betreft syntax. Procedures kunnen opgeroepen worden wanner je dat wenst, triggers wanneer iets “gebeurt” in de databank. Triggers en procedures zijn interessant wanneer je via verschillende applicaties dezelfde data wil aanspreken. Voor berekende velden kan je zelf geen waarde invoeren: de data ervoor wordt berekend op basis van andere velden. Checks controleren de integriteit van een bepaalde kolom. Bvb een check op een geboortejaar zou kunnen vermijden dat een jaar voor 1900 wordt ingevoerd. Domains: bewaken integriteit van velden, maar zijn niet tabelgebonden. Een domain in Firebird kan checks bevatten en een defaultwaarde. Triggers, procedures, checks en domains vind je in de SQL standaard. Berekende velden niet.
UPDATE commando UPDATE wordt gebruikt om de waarden van velden in bestaande rijen te wijzigen. Zoals bij INSERT is het niet mogelijk de waarden van velden in verschillende tabellen in één commando te wijzigen. UPDATE table SET columnname=...; UPDATE wagen SET kleur='Blauw'; → kleur van alle wagens naar Blauw UPDATE table SET columnname1=... WHERE columnname2=...; UPDATE wagen SET kleur='Blauw' WHERE eigenaar='Tom Jansen'; → enkel kleur van wagen(s) Tom Jansen naar Blauw UPDATE wagen SET kleur='Blauw' WHERE kleur='Groen'; → enkel kleur van groene wagens naar Blauw
De SET clausule SET columnname1=value [, columnname2=value] SET columnname=NULL De value in bovenstaande statement dient één van de volgende te zijn: ► Een constante waarde (bijvoorbeeld 99): SET columnname=99; ► De naam van een ander veld binnen dezelfde tabel, op voorwaarde dat het van hetzelfde type is: SET columnname1=columnname2; ► Een expressie: bijvoorbeeld iets als SET columnname=columnname+10; ► Een bepaalde server-gerelateerde waarde; bijvoorbeeld de huidige datum of tijd; Bvb. CURRENT_TIMESTAMP, CURRENT_TIME,... ► Een SQL functie aanroep; of eventueel een user defined function aanroep indien bestaand; bijvoorbeeld SET columnname=UPPER(columnname).
Waardes switchen tussen twee velden Wanneer je twee kolommen wil wisselen dien je een derde kolom van hetzelfde type te gebruiken. UPDATE table SET column1=column2, column2=column1;
→ waarden van column2 gekopiëerd naar zowel column1 als column2 UPDATE table SET column3=column1, column1=column2, column2=column3; UPDATE table SET
column2=column2 + column1, column1=column2 – column1, column2=column2 – column1;
→ uitzonderlijk voor numerieke velden
UPDATE triggers CREATE TRIGGER trgnaam2 FOR table ACTIVE BEFORE UPDATE POSITION 0 AS ...
→ ook mogelijk: CREATE TRIGGER trgnaam2 FOR table ACTIVE BEFORE UPDATE OR INSERT POSITION 0 AS ...
→ in oudere versies met een procedure op lossen
CREATE PROCEDURE zetnaam (NAME VARCHAR(50)) RETURNS (RETURNNAME VARCHAR(50)) AS begin IF (NAME IS NULL) THEN EXIT; IF (strlen(NAME)>1) THEN RETURNNAME=upper(substring(NAME FROM 1 FOR 1)) || lower(substring(NAME FROM 2)); ELSE RETURNNAME=upper(NAME); end CREATE TRIGGER trgnaam1 FOR table ACTIVE BEFORE INSERT POSITION 0 AS begin new.NAME = EXECUTE zetnaam(new.NAME); end
DELETE commando DELETE wordt gebruikt voor het verwijderen van ganse rijen uit een tabel. Ook met DELETE kunnen slechts rijen in één tabel tegelijkertijd verwijderd worden. DELETE FROM table WHERE clausule;
INSERT, UPDATE en DELETE met VIEWS? Om te kunnen antwoorden op die vraag moeten we weten wanneer views precies gemaakt worden: ► wanneer het view gecreëerd wordt? ► wanneer het view wordt opgevraagd? Meestal het tweede. Dan nog zijn er beperkingen: ► de primary key moet in het view zitten; ► velden die niet in het view zitten moeten ofwel NULL kunnen zijn ofwel moeten ze een defaultwaarde hebben.
Oefeningen 1. Wijzig het geslacht M naar m. 2. Geef iedereen een loonopslag van 10%. 3. Geef iedereen die minder verdient dan 40000 een loonopslag van 10%, de anderen een loonopslag van 5%. 4. Voeg voor iedereen een extra rij in de tabel WORKS_ON in voor projectnummer 30 en met 0 aantal gewerkte uren. 5. Verwijder de net toegevoegde rijen. 6. Voeg aan de tabel EMPLOYEE twee velden toe: STREET, CITY en STATELETTERS (via Flamerobin GUI). Vul ze met de gewenste gegevens: gebruik daarvoor de ingebouwde functies POSITION, SUBSTRING en TRIM (of eventueel andere naar keuze). Zie voor meer documentatie wiki.firebirdsql.org. 7. Geef het veld FNAME de naam LASTNAME en het veld LNAME de naam FIRSTNAME. Verwissel daarna de waarden uit de twee kolommen.
