”Structured Query Language” eller SQL helt enkelt är ett frågespråk som hanterar kommunikation med databaser. Även om det är en standard så finns det dialekter som bara fungerar mot vissa databaser.
I detta inlägg tänker jag gå igenom det som fungerar mot samtliga, och mot slutet titta på några exempel på mer komplexa frågor.
SQL används för att öppna upp kommunikation mellan databas och klientmjukvara. Det enda som är specifikt för databastypen och klienten är själva uppkopplingen eller anslutningen. När det väl finns en etablerad kanal mellan klient och databas är SQL det enda man behöver.
Databasen kan vara allt från Microsoft SQL och Postgresql/PostGIS till Accessdatabaser och SpatiaLite. Klienten kan vara hela program med inbyggd funktionalitet som QGIS till webbskript i PHP.
Grundkommandon
För det första: SQL är inte känsligt för stora och små bokstäver, även om man brukar skriva vissa kommandon med versaler! För det andra: Det är praxis, men inte ett krav, att avsluta ett SQL kommando med semikolon.
- SELECT – hämtar data från databasen
- UPDATE – redigerar innehåll i databasen
- DELETE – raderar poster från databasen
- INSERT INTO – lägger till nya poster i databasen
- CREATE TABLE – skapar en ny tabell
- DROP TABLE – raderar en tabell
(Det finns ytterligare kommandon för att exempelvis skapa nya databaser, men jag hoppar över dessa här.)
När man ger ett kommando så skall man även referera till vilken tabell man skall hämta, uppdatera eller radera data från. Detta görs med FROM tabellnamn.
Om vi börjar med SELECT och vi vill hämta alla poster från tabellen Data så blir SQL kommandot:
SELECT * FROM Data;
Är det endast vissa kolumner vi vill ha tillbaka i svaret så anges dessa i stället för *. Är det flera kolumner så sätter man ett komma mellan dessa:
SELECT pos1, pos2, pos7 FROM Data;
Vill man sedan lägga på ett filter eller villkor så gör man det med WHERE:
SELECT pos1, pos2, pos7 FROM Data WHERE pos3 > 1000;
Villkor kan hantera vanlig algebra där man använder =, <>, <, >, <= och >= men även exempelvis LIKE för att testa mot ett mönster. Kombinationer av villkor kan användas med AND och OR.
Vill man att resultatet skall komma i en speciell ordning så gör man det med kommandot ORDER BY följt av ett attribut och eventuellt DESC. Om man inte lägger till DESC så kommer posterna att filtreras i nummer/bokstavsordning i stigande ordning. Om man anger flera attribut att sortera efter så sorteras ordningen efter det första först och därefter i den ordning som de är angivna (separerade med komma).
SELECT * FROM Data ORDER BY pos1, pos2;
Skall man skriva till databasen så behöver man VALUES, vilket är en lista med data som skrivs till tabellen. Man kan ange vilka kolumner som data skall skrivas till före VALUES kommandot och då skall listan med värden motsvara den angivna ordningen. Om inga kolumner anges så skrivs data till kolumnerna i tur och ordning med början på den första.
INSERT INTO Data (pos1, pos2, pos7) VALUES ('text', 10, 3.14);
Lägg märke till att det är skillnad på text och tal. Tal har för övrigt decimalpunkt och inte decimalkomma av naturliga skäl.
Om man inte skriver data till vissa kolumner så ges dessa värdet NULL, vilket kan vara bra att känna till om databasen exempelvis inte tillåter NULL i vissa kolumner. Databasen kan även tänkas uppdatera en del kolumner automatiskt, exempelvis ett ID-nummer.
Att redigera eller uppdatera en post görs på motsvarande sätt med UPDATE. Man använder kommandot SET och tilldelar kolumner värden där dessa skall uppdateras. WHERE används för att tala om vilken eller vilka poster som skall uppdateras.
UPDATE Data SET pos1='Ny Text' WHERE pos2=10;
Man kan uppdatera flera kolumner samtidigt genom att lägga till kolumn=värde för dessa och separera med ett komma. Om man vill uppdatera samtliga poster med de nya värdena så kan man strunta i WHERE… Om man å andra sidan råkar glömma WHERE i en stor databas…? (Gör inte det!)
Att radera rader är inte svårare än att ange villkor för vilken/vilka rader som skall raderas.
DELETE FROM Data WHERE pos1='Ny Text';
På samma sätt som för UPDATE så kan man radera alla poster genom att utesluta WHERE.
PostGIS
När man klär på en databas möjligheten att hantera geometrier så introducerar man flera nya typer av kolumner och möjliga värden för dessa. När det gäller exempelvis PostGIS så kan man även skicka kommandon till databasen som tar hand om geometrier som skrivs på ett lite enklare sätt och lagrar dessa på ett effektivt sätt.
INSERT INTO Geodata (geom, namn) VALUES ( ST_GeomFromText('POINT(453000 6401000)',3006), 'Jönköping');
Kommandot ovan skriver en ny post med data i kolumnerna geom och namn och tilldelar dessa en koordinat i SWEREF99TM och texten Jönköping. Värdet 3006 är valfritt, men om man inte anger vilket referenssystem koordinaterna är angivna i så antas att det är tabellens standard som gäller.
Nu lagras positionen inte i klartext utan i ett mera ”binärt” format, men man kan använda ST kommandon när man läser från databasen också.
SELECT ST_AsText(geom), namn WHERE id=42;
Detta returnerar texten POINT(453000 6401000) från databasen (förutsatt att den tidigare posten fick id nummer 42).
När man nu har en geometrikolumn i databasen så finns det fler ST kommandon man kan använda i samma SQL sats.
- ST_X(geom) – hämtar X komponenten av geometrin
- ST_Y(geom) – hämtar Y komponenten av geometrin
- ST_AsText(ST_Transform(geom, 4326)) – hämtar geometrin, omvandlar till WGS84 och returnerar som Well Known Text.
- ST_SRID(geom) – vilket koordinatsystem är angivet
- ST_Dimension(geom) – ger 0 för punkter, 1 för linjer och 2 för polygoner
Det finns bokstavligen hundratals ST kommandon att använda och det går att låta PostGIS göra väldigt mycket av det arbete som man kanske annars behövt flera stycken processverktyg till i GIS-programmet.
Nackdelen är att man sällan har så omfattande databaser eller frågor att det går snabbare att komponera ett anpassat SQL kommando för ändamålet än att exempelvis bygga en processmodell i ett grafiskt verktyg. Själva processen går däremot så gott som alltid snabbare om man låter databasen göra jobbet.