Kunskapsdokument - SQLite

I denna genomgång kommer jag ha en genomgång av användandet av en relationsdatabas. Den databas som jag fokuserar på är SQLite men de SQL-kommandon jag kommer använda mig av fungerar på flera andra databaser och fungerar de inte exakt på det sätt som de är skrivna så borde du enkelt kunna hitta det rätta sättet för just din databas.

Förberedelser

Denna genomgång bygger på att du redan har sett filmen där jag bygger en SQLitedatabas.

Utgångsläge

Sedan den tidigare genomgången (filmad tutorial) så har vi en databas med en tabell. Denna tabellen heter Car och innehåller data om en bil.

CREATE TABLE "car" (
	"regNr"	TEXT NOT NULL UNIQUE,
	"make"	TEXT NOT NULL,
	"model"	TEXT NOT NULL,
	"year"	INTEGER NOT NULL,
	"forSale"	INTEGER NOT NULL,
	PRIMARY KEY("regNr")
);

Kommentar för denna tabell är att kommandot UNIQUE inte behövs för regNr eftersom det är en primärnyckel. En primärnyckel blir UNIQUE automatiskt. Det gör inget att UNIQUE är markerat men det är inget krav.

Insert

För att kommunicera med databasen så skriver vi SQL-kommandon till databasen från en applikation vi skall bygga. Att det i applikationen DB Browser for SQLite finns formulär för inmatning hjälper oss inte när vi sedan skall bygga egna applikationer. Att lära oss att skriva SQL är viktigt.

Vi börjar med INSERT som gör att vi kan skapa poster i våra tabeller. Att lägga in poster kallas att populera en tabell.

INSERT INTO car ("regNr","make","model","year","forSale")
VALUES ('ABC123','Volvo','V70',2012,0);

1 row affected;

SQL är inte känsligt för radbrytningar så vi kan skriva

INSERT INTO car ("regNr","make","model","year","forSale") VALUES ('DEF456','Saab','93',1995,0);

eller på följande sätt

INSERT INTO car
("regNr","make","model","year","forSale")
VALUES
('DEF456','Saab','93',1995,0);

Välj det sätt som du tycker är enklast. Pratar du direkt mot databasen så kan det andra sättet vara enklast men om du skall skicka SQL-anrop från C# så skall SQL-satsen bäddas in i en sträng och då kan det andra sättet vara lite krångligt.
Vill du infoga flera rader på en gång så skriver du så här;

INSERT INTO car
("regNr","make","model","year","forSale")
VALUES
('GHI789','Volkswagen','Passat', 2019,0),
('BOSSE','BMW','520', 2017,1);

2 rows affected;

Primärnyckel

INSERT INTO car ("regNr","make","model","year","forSale")
VALUES ('ABC123','Volvo','V70',2012,0);

Result: UNIQUE constraint failed: car.regNr

Select

För att söka efter bilar i en tabell så använder vi kommandot SELECT

SELECT * FROM car;

SELECT betyder att vi skall hämta från tabellen car, * innebär att vi vill hämta alla kolumner. Svaret får vi i form av ett resultset som ser ut som en tabell men ofta kommer som en array, ett objekt eller som JSON/XML beroende på vilka hjälpklasser som vi använder för att kommunicera med databasen.

Hämta vissa kolumner

Eftersom databaser är byggda för att hantera större mängder av data så är det av stor vikt att vi kan skriva effektiva SQL-frågor så att vi endast får den data som vi vill ha i vårt svar. Vill vi endast ha en lista på regNr, make och model så frågar vi efter dessa kolumner så här;

SELECT regNr, make, model
FROM car;

WHERE

WHERE använder vi för att skapa villkor för vilka poster vi vill hämta.

Om vi vill lista alla bilar som inte är till salu så skriver vi på följande sätt;

SELECT *
FROM car
WHERE forSale = 0;

Vi kan skriva flera villkor, då använder vi AND eller OR på samma sätt som när vi skriver villkor i programmering. Här listar vi alla bilar som inte är till salu och tillverkade efter år 2000.

SELECT *
FROM car
WHERE forSale = 0
AND year > 2000;

Genom att byta AND mot OR så frågar vi istället efter alla bilar som inte är till salu eller som är tillverkade efter år 2000.

SELECT *
FROM car
WHERE forSale = 0
OR year > 2000;

ORDER BY

Ofta vill vi kunna sortera det resultset som vi får tillbaka från databasen. Det gör vi med hjälp av ORDER BY.

SELECT *
FROM car
ORDER BY make;

Resultatet är sorterat på bilmärket och i bokstavsordning från A - Ö. Detta kallas Ascending och förkortas ASC och är default. Vill vi ha omvänd sorteringsordning så använder vi oss av DESC som står för decending.
Vill vi sortera på flera olika saker så talar vi om i vilken ordning resultatet skall sorteras, tex.

SELECT *
FROM car
ORDER BY make ASC, year DESC;

Nu gör inte detta någon skillnad i vårt exempel eftersom vi inte har bilar från samma märke. Vi testar att lägga till några nya Volvos.

INSERT INTO car
("regNr","make","model","year","forSale")
VALUES
('AAA111','Volvo','740', 1992,1),
('BBB111','Volvo','940', 1995,1),
('CCC111','Volvo','245', 1982,1);

3 rows affected;

Hämtar vi nu alla bilar sorterade på make så blir frågan så här;

SELECT *
FROM car
ORDER BY make;

Om jag nu sorterar först på make och sedan på year så ser frågan ut så här;

SELECT *
FROM car
ORDER BY make DESC, year ASC;

Det jag vill visa är att det är bra att bli duktig på SQL för att få rätt data, i rätt ordning så att vi slipper hantera det för mycket i vår applikation.

SQL-funktioner

Det finns inbyggda funktioner i SQL som vi kan använda för att kunna göra beräkningar så att dessa kommer i vårt resultset. Om jag vill räkna ut hur många bilar det finns av varje bilmärke i vår tabell så skriver jag följande kod.

SELECT make AS bilmärke, COUNT(make) AS antal
FROM car
GROUP BY make
ORDER BY antal DESC;

Vi kan jobba vidare med samma typ av fråga och kolla hur många bilar av varje märke som är till salu. Då grupperar vi också på forSale, tänk på att grupperingen sker i den ordningen som kolumnerna är skrivna. I detta fall så spelar ordningen ingen roll för resultatet.

SELECT make AS bilmärke, COUNT(make) AS antal, forSale
FROM car
GROUP BY make, forSale
ORDER BY antal DESC;

Om vi nu bara vill visa alla bilar som är till salu så behöver vi använda kommandot HAVING. HAVING fungerar på samma sätt som WHERE fungerar fast HAVING måste vi använda om vi har använt någon funktion.

SELECT make AS bilmärke, COUNT(make) AS antal, forSale
FROM car
GROUP BY make, forSale
HAVING forSale = 1
ORDER BY antal DESC;

Vi måste inte visa kolumnen forSale i vårt resultset även om vi använder det som villkor. I vårt exempel är det ju ganska meningslös information eftersom vi i HAVING har meddelat att vi endast är intresserade av bilar som är till salu.

SELECT make AS bilmärke, COUNT(make) AS antal
FROM car
GROUP BY make, forSale
HAVING forSale = 1
ORDER BY antal DESC;

Vi kan också bygga strängar för att göra vårt resultset färdigt att skriva ut i vår applikation.

SELECT 'Antal bilar av märket ' || make || ' till försäljning: ' || COUNT(make) || 'st.' as text
FROM car
GROUP BY make, forSale
HAVING forSale = 1
ORDER BY COUNT(make) DESC;

Lite tips och tricks innan vi avslutar detta avsnitt. Den sista SQL-frågan är inte helt enkel att få till då det skall öppnas och stängas strängar samt konkatenera (slå ihop) med kolumner från tabellen. Även om du behöver bygga denna strängen i din applikation så testa gärna SQL-satsen i databasen, tex i DB Browser for SQLite, så att du kan läsa av felmeddelandet som databasen ger ifall det blir ett fel. Detta felmeddelande är lite svårare att fånga upp i din applikation, resultatet brukar då bli att det blir ett error (odefinierat vilket) och/eller att du får tillbaka ett tomt resultset. Börja då alltid att kika i databasen om du kan få bättre hjälp där.

SQL är i grunden ett språk som är case insensitive vilket innebär att det inte gör skillnad på gemener/versaler. Att jag skriver nyckelord i mina SQL-satser med versaler beror bara på att öka läsbarheten. Den sista frågan vi har jobbat med kan jag lika gärna skriva på följande sätt;

select 'antal bilar av märket ' || make || ' till försäljning: ' || count(make) || 'st.' as text
from car
group by make, forsale
having forsale = 1
order by count(make) desc;

eller såhär

SELECT 'ANTAL BILAR AV MÄRKET ' || MAKE || ' TILL FÖRSÄLJNING: ' || COUNT(MAKE) || 'ST.' AS TEXT
FROM CAR
GROUP BY MAKE, FORSALE
HAVING FORSALE = 1
ORDER BY COUNT(MAKE) DESC;

Jag använder också ofta radbrytningar för att ytterligare öka läsbarheten men ibland är det smidigare att bygga upp SQL-frågan från applikationen som en enda sträng utan radbrytningar vilket går lika bra.

SELECT 'Antal bilar av märket ' || make || ' till försäljning: ' || COUNT(make) || 'st.' as text FROM car GROUP BY make, forSale HAVING forSale = 1 ORDER BY COUNT(make) DESC;

DELETE

För att ta bort en post ur vår tabell så skriver vi på följande sätt

DELETE FROM car
WHERE regNr = "ABC123";

1 row affected;

Precis som tidigare kan vi skapa villkor på olika sätt. Vill vi ta bort alla bilar före år 2000 som är av märket Volvo så skriver vi så här;

DELETE FROM car
WHERE make = "Volvo"
AND year < 2000;

3 rows affected;

Glöm inte av villkoret, om vi bara skriver

DELETE FROM car;

3 rows affected;

så kommer alla bilar i vår tabell att tas bort. Om det är det du vill göra så är det bra, blir det så för att du har missat ett villkor så är det mindre bra.

Just nu har jag inga bilar kvar i min databas. Jag återskapar dem.

INSERT INTO car
("regNr","make","model","year","forSale")
VALUES
('ABC123','Volvo','V70', 2012,0),
('DEF456','Saab','93', 1995,0),
('GHI789','Volkswagen','Passat', 2019,0),
('BOSSE','BMW','520', 2017,1),
('AAA111','Volvo','740', 1992,1),
('BBB111','Volvo','940', 1995,1),
('CCC111','Volvo','245', 1982,1);

7 rows affected;

UPDATE

Det har blivit dags att kolla på vår sista del i CRUD (Create, Read, Update, Delete) som är de fyra grundfunktionerna när det gäller hantering av lagrad data.

Om vi vill göra vår bil med registreringsnummer ABC123 tillgänglig för försäljning så skriver vi

UPDATE car
SET forSale = 1
WHERE regNr = "ABC123";

1 row affected;

Vi kan ändra flera värden på samma sätt

UPDATE car
SET forSale = 0, model ="V70 D2", year = 2011
WHERE regNr = "ABC123";

1 row affected;

Vi kan med villkor uppdatera flera olika poster. Här gör vi alla Volvobilar tillgängliga till försäljning.

UPDATE car
SET forSale = 1
WHERE make = "Volvo";

4 row affected;

Skulle vi råka glömma skriva villkor så uppdateras alla poster.

UPDATE car
SET forSale = 1;

7 rows affected;

Kraftfullt och användbart i rätt tillfälle men farligt om det blir fel.

Två tabeller

I många databaser så använder vi fler än en tabell. Nu tänker jag bygga ut denna databas med en tabell där vi lagrar personer. Tanken är att koppla ihop dessa tabeller så att det går att ta reda på vem som äger en specifik bil och även lista alla bilar som en person äger.

CREATE TABLE "person" (
	"personId"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"firstname"	TEXT NOT NULL,
	"familyname" TEXT NOT NULL,
	"city"	TEXT NOT NULL
);

I tabellen person skapar jag fyra attribut;

1. personId, lagrar unikt id för personen, det är en primärnyckel och den kommer skapas automatiskt med en räknare (autoincremtent)
2. firstname, lagrar personens förnamn som text
3. familyname, lagrar personens efternamn som text
4. city, lagrar personens hemort som text

I denna tabell har jag valt att primärnyckeln skall vara ett personId, det kunde ha varit ett personnummer, ett medlemsnummer eller något annat, det viktigaste är att det är unikt. I tabellen car så valde vi att låta regNr vara primärnyckeln. Vad som är lämpligast att använda som primärnyckel delar programmerare i två läger, antingen tar vi det som redan är unikt och låter detta vara primärnyckeln i våra tabeller. Detta gäller tex personnr, registreringnr, medlemsnr, kontonr mm. Nackdelen till detta är två; för det första så är ofta denna information av lite känslig karaktär vilket gör att den skall användas lite försiktigt, den andra orsaken är att värdet i denna nyckel tenderar att vara ganska lång. Det är skillnad på personId = 1 eller personnr = “20020220-4020”, speciellt när vi nu skall börja koppla ihop våra tabeller. Personligen använder jag oftast ett “nonsensvärde” (ett värde som inte innehåller relevant information) till mina nycklar, tex personId, carId osv. Bygger jag en enklare applikation med få tabeller eller lite data så kan jag absolut tänka mig att göra på något annat sätt. När jag vet vad jag gör och varför jag gör det på detta sätt så har jag inga problem med att bryta mot mina principer.

Vi populerar (lägger till) tabellen med några personer

INSERT INTO person (personId, firstname, familyname, city)
VALUES
(NULL, 'Anna', 'Andersson', 'Alingsås'),
(NULL, 'Bosse', 'Bus', 'Vårgårda'),
(NULL, 'Cilla', 'Cillasson', 'Lerum');

3 rows affected;

Lägg märke till NULL som värde för personId. Anger vi NULL så kommer AUTOINCREMENT se till att den posten får nästa id automatiskt.

Om vi ändå skall ange personId till NULL så kan vi strunta i att skriva in detta, det kommer skapas automatiskt ändå. Viktigt att notera här att vi måste ha lika många attribut inom parentesen som vi senare inte antal värden.

INSERT INTO person (firstname, familyname, city)
VALUES  ('Dennis', 'D', 'Alingsås');

1 row affected;

Om vi skulle ange ett värde för personId så kommer vi ställa om den automatiska räknaren och ge den ett nytt värde att utgå ifrån. Kolla på detta exempel.

INSERT INTO person (personId, firstname, familyname, city)
VALUES
(10, 'Elsa', 'Eskilsdotter', 'Floda'),
(NULL, 'Freddie', 'Fred', 'Borås');

2 rows affected;

Här ser du att det blir ett hopp i räknaren för personId från 4 till 10. Räknaren fortsätter sedan från 11 och uppåt. Detta innebär att vi får ett hål i vår räknare, det spelar oftast ingen roll och kommer fungera likadant ifall vi tar bort en person. Mitt bästa tips är att om vi har skapat en räknare så låt den få ta hand om numreringen utan vår påverkan. Det finns en tabell som heter “sqlite_sequence” i din databas som innehåller värdet på den senaste nyckeln. Den kan du ändra om du tex vill att startvärdet för personId skall vara 100 tex. Det är vanligt att man gör detta på medlemsnummer eller fakturanummer för att inte få allt för låga värden. Du kan naturligtvis ändra den till vad du vill och om den skall användas och har ett värde som redan finns som nyckel i din tabell kommer du få ett error när du skall lägga till en ny post.

Nu har vi två tabeller, car och person. Det finns en tjänst som heter Vertabelo som hjälper oss när vi designar en databasmodell. Jag kommer återkomma till denna tjänst alldeles strax, men så här ritar Vertabelo upp den databas som vi har byggt så här långt. (För att importera en databas i Vertabelo så kan du i DB Browser for SQLite i menyn “File->Export” välja att exportera hela databasen som ett sql-script.)

Bild: Databasmodell

Det vi vill göra nu är att koppla car och person så att vi kan få reda på vilken person som äger en bil. Detta kan göras på tre sätt, allt beroende på hur vi vill att kopplingen skall göras.

1. En person kan äga en eller ingen (0, 1) bil. Då kan vi lägga till regNr i tabellen person.
2. En bil kan ägas av en eller ingen (0, 1) person. Då kan vi lägga till personId i tabellen car.
3. En bil kan ägas av många (N) personer och en person kan äga (N) många bilar. Då behöver vi skapa en kopplingstabell mellan tabellerna där vi kan lagra regNr och personId för att koppla ägandet.

I just detta fallet så har trafikverket bestämt att endast en person får äga en viss bil. Därför skall vi i detta tillfället gå på alternativ 2 ovan.
Vi behöver nu lägga till ett attribut till tabellen car så att vi kan lagra vem som äger en bil. Den koden skriver vi så här;

ALTER TABLE car
  ADD personId INTEGER NULL;

Så här ritas det ut i Vertabelo.

Bild: Databasmodell

Nu gäller det att skriva in ett personId i car för att kunna koppla vem som äger vilken bil. Jag har också lagt till möjligheten att värdet i car.personId (tabellen.attributet) skall kunna vara NULL. I vanliga fall är detta inte tillåtet då en bil alltid måste ägas av någon. Men det är smidigt för oss att sätta denna till NULL innan vi har fyllt den med bra data. Vill vi inte att den skall kunna vara NULL så kan vi ändra det i efterhand.

Jag ändrar enligt följande, 2 bilar skall ägas av 1 person. 3 personer äger var sin bil. Övriga bilar är utan ägare.

UPDATE car SET personId = 1 WHERE regNr = "ABC123";
UPDATE car SET personId = 1 WHERE regNr = "AAA111";
UPDATE car SET personId = 2 WHERE regNr = "BOSSE";
UPDATE car SET personId = 4 WHERE regNr = "GHI789";
UPDATE car SET personId = 10 WHERE regNr = "CCC111";

INNER JOIN

Om jag nu vill få fram en lista på vem som äger vilken bil måste jag använda kommandot INNER JOIN.

SELECT c.regNr, c.make, c.model, p.*
FROM car c INNER JOIN person p ON c.personId = p.personId;

Att notera;

• Kolla här hur jag använder mig av alias för car (c) och person (p) för att kunna hålla ner SQL-satsens längd och göra frågan tydligare.
• Kopplingen görs genom “FROM tabell1 INNER JOIN tabell2 ON <>”. Attributen som skall kopplas behöver inte heta samma sak, men det förenklar.
• Jag förstärker med att tala om från vilken tabell som ett visst attribut skall hämtas. Attributet regNr finns bara i tabellen car så egentligen behöver jag inte förtydliga detta men personId finns i bägge tabellerna så om jag missar att förstärka det attributet så kommer jag få ett felmeddelande som talar om “ambiguous column name” vilket innebär att databasen inte vet vilket attribut som skall hämtas då det finns flera med samma namn.
• p.* innebär att alla kolumner från tabellen p (person) skall hämtas.

INNER JOIN innebär alltså att vi bara hämtar de poster som har en godkänd koppling till den andra tabellen.

OUTER JOIN

Det kan hända att vi vill ställa frågor och få med data även om det inte finns en koppling, jag skall visa med två exempel.

Exempel 1

Jag vill få en lista på alla bilar som finns i vår databas, jag vill också se vem som äger en viss bil, om någon äger den.

SELECT c.regNr, c.make, c.model, c.year, p.firstname || " " || p.familyname || ", " || p.city as owner
FROM car c LEFT OUTER JOIN person p ON c.personId = p.personId
ORDER BY regNr;

Exempel 2

Jag vill ta fram en lista på alla personer som finns i vår databas och jag vill skriva hur många bilar varje person äger samt sortera listan på hur många bilar personerna äger.

SELECT p.firstname, p.familyname, p.city, COUNT(c.regNr) AS antal
FROM person p LEFT OUTER JOIN car c ON p.personId = c.personId
GROUP BY p.personId
ORDER BY antal DESC;

Relationer

Är det detta som menas med relationer i en relationsdatabas? Ja och nej. Det finns en typ av relation mellan våra två tabeller men det finns för tillfället ingen kontroll på att denna relationen verkligen fungerar. Låt mig ta ett några exempel.

Om jag i en bil matar in att den skall ägas av en person med personId = 7, vad händer då? Någon sådan person finns inte i tabellen person.
Eller om jag tar bort personen med personId = 1, vad händer då med hennes bilar? För att få kontroll på dessa händelser så behöver jag skapa en fysisk relation mellan mina tabeller, i den relationen så kan jag sätta vissa regler för vad som skall hända när jag ändrar data som påverkar en annan tabell genom relationen. Om vi kikar vidare på personen med personId = 1, om den personen skall tas bort vad som skall då hända med bilar som är kopplade till den personen. Jag ser tre alternativ, 1. Vi tar bort den bilen som är kopplad till personen, 2. Vi sätter värdet NULL som attribut för personId i tabellen car för de bilar som påverkas, 3. Du får inte ta bort en person som äger en bil, ändra ägandet innan du får ta bort denna personen.

Vilket av alternativen är rätt? Det beror på vilken regel som skall gälla. Databasen och applikationen som du bygger skall ju följa de regler som någon har beställt.

Hur bygger vi nu dessa relationer? För SQLite finns det här en begränsning som gör att vi inte kan lägga till detta på en befintlig tabell. Det innebär att vi måste skapa detta när vi skapar vår tabell, vilket iof är bra eftersom vi redan från början behöver planera och skapa vår databas med eftertanke. Vill du lära dig mer om hur man planerar sin databasmodellering så finns det en länk till ett avsnitt från kursen Webbserverprogrammering01 längst ner på denna sidan.

När jag har lagt till relationen i Vertabelo så kommer databasmodellen se ut så här.

Bild: Databasmodell

“gaffeln” visar att en person kan äga flera bilar.

SQL genererat från Vertabelo som du kan köra i SQLite för att skapa dina tabeller. Om tabellen redan finns så kommer den behöva tas bort först. SQL-kommandot DROP TABLE car IF EXISTS; löser det.

När du tar bort tabellen så kommer naturligtvis all data försvinna så det ger minst jobb att tänka till tidigt i utvecklingsprocessen för att slippa göra om senare.

CREATE TABLE IF NOT EXISTS car (
    regNr text NOT NULL CONSTRAINT car_pk PRIMARY KEY,
    make text NOT NULL,
    model text NOT NULL,
    year integer NOT NULL,
    forSale integer NOT NULL,
    personId integer,
    CONSTRAINT AK_0 UNIQUE (regNr),
    CONSTRAINT car_person FOREIGN KEY (personId)
    REFERENCES person (personId)
    ON DELETE RESTRICT
);

-- Table: person
CREATE TABLE IF NOT EXISTS person (
    personId integer NOT NULL CONSTRAINT person_pk PRIMARY KEY AUTOINCREMENT,
    firstname text NOT NULL,
    familyname text NOT NULL,
    city text NOT NULL
);

Att skapa en databas och tabeller i Vertabelo hjälper dig en hel del i ditt arbete. I kunskapsdokument: Vertabelo finns information om hur du kan skapa en egen användare och hur du kan arbeta med Vertabelo.