6. Moment06 - Databasteori & SQL

Databaser är ett strukturerat sätt att lagra datamängder, vanligtvis stora sådana. I denna kursen så gäller att använda datalagring som stöd till våra webbapplikationer och då har jag valt att använda databaser, det skulle gå att lagra data i andra filformat som .txt, .xml eller .json men det är lika bra att ta tjuren vid hornen och lära sig det bästa alternativet för större datasamlingar, allt för att inte bli begränsad senare.

Först så kommer vi jobba med databasmodellering vilket innebär att vi ritar upp hur databasen skall se ut för att kunna lagra data på ett tydligt sätt.

När vi sedan kan modellera en databas är det dags att börja jobba med databasen. I detta moment skall vi lära oss att skapa databasen, populera den och sedan ställa frågor och manipulera det data som lagras i databasen.

Detta är ett teoritungt moment så det vi kommer dela upp den i två delar. Uppgiften är också tvådelad men vi jobbar med samma databas så det blir mer en stor uppgift fast i två mindre delar.

6.1 Terminologi

För att lösa detta moment behöver vi lära oss terminiologin för databaser.

I en relationsdatabas så måste det finnas tabeller som på något sätt har en relation mellan varandra.

I varje tabell finns det rader, varje rad kallas post, och kolumner som kallas fält. Varje fält måste definieras där man talar om vilken datatyp och eventuell storlek som skall användas för detta fält.

Index skapas för att snabba på sökningar i en databas. För enklare databaser är det inte så viktigt att skapa egna index.

Ett fält, eller en kombination av fält, i varje tabell används som primärnyckel, primary key. Primärnycklar används för att unikt identifiera en post och är ansvarig för att det inte finns två poster med samma nyckel. En primärnyckel är en typ av index.

Tabellerna kopplas ihop genom att det som är primärnyckel i en tabell finns med som kopplande värde i en annan tabell, detta kallas då för främmande nyckel, foreign key.

När en databas skall skapas så bygger definitionerna av tabeller, fält och index upp ett schema. Varje schema lagras i en egen databas.

Vi använder frågespråket SQL - Structured Query Language för att en klient skall kunna kommunicera med databasen.

Vyer är ett sätt att skapa en SQL-fråga som ligger i databasen. Istället för att skicka en lång fråga från tex php-sidan så kan vi göra ett anrop till en skapad vy som ger samma svar.

6.2 Databasmodellering

För att kunna översätta verkligheten till ett databasschema så använder vi oss av databasmodellering. Databasmodelleringen sker i flera steg och vi börjar med att skapa ett ER-diagram.

6.2.1 ER-diagram

Vi visar hur ett ER-diagram fungerar genom ett exempel. Vi tänker oss att skapa ett väldigt enkelt bibliotekssystem där en person skall kunna låna en bok. I den första versionen har vi satt begränsningen till att det bara får finnas ett exemplar av en enda bok.

Alla ER-diagram har jag ritat i diagrams.net som du troligtvis har använt tidigare i någon av mina kurser. Se till att koppla diagrams.net till din Google Drive så att dina modeller sparas.

Vi börjar med att identifiera det "saker" som skall finnas i vårt system, i detta fall så kan vi se att det bör finnas böcker och låntagare, alltså någon som kan låna en bok. Vi väljer att skriva dessa entitetstyper i singular och ramar in dem i en rektangel. Vi väljer också att använda "Person" istället för "Låntagare" då vi tror detta är ett bättre namn.

Vi ritar nu ut sambanden mellan våra entiteter. Sambandet binds ihop med hjälp av en utdragen romb eller en diamantform som det ofta benämns som. I detta fallet så är vårt samband mellan Bok och Person möjligheten att låna en bok. Vi kallar sambandet "Låna".

Det finns olika sambandstyper, dessa blir intressanta sedan när vi skall skapa tabeller av vårt ER-diagram. De tre sambanden vi har att välja på är följande;

• 1:1 - Ett till ett
  Här kan vi visa på att en en person kör en bil, det går inte att fler kör samma bil vid ett givet tillfälle. En bil körs också av en person.
• 1:N - Ett till många
  En bil kan bara ägas av en person, men en person kan äga många bilar.
• N:M - Många till många
  Här blir exemplet att en person kan äga många hus och ett hus kan ägas av många personer.

I vårt exempel så kan vi se att en person kan låna många böcker men en specifik bok kan bara lånas av en person, vid ett specifikt tillfälle. Det innebär att vi behöver ange sambandstypen 1:N. Vi sätter 1 vid personen och N vid boken eftersom "1 person kan låna flera böcker".

Dags att kika på vilka attribut (ett attribut är en egenskap som är kopplad till en entitet eller ett samband) som skall kopplas till våra entiteter. Vi ringar in attributen och drar streck till den entitet, eller det samband, som den tillhör.

Vi måste välja något attribut i varje entitet som skall kunna identifiera en post på något unikt sätt. När vi har valt detta attribut så stryker vi under det. Ibland är det enkelt att välja rätt attribut, ibland är det svårare. Personnummer, isbn-nummer, lånekortsnummer och liknande attribut är tacksamma att använda. Ibland kan man behöva hjälp av flera attribut för att skapa en unik kombination. För sambandstypen "Låna" hade det behövts tre attribut (lånare, boken och datum) för att få det unikt (nästan helt säkert), då kan det vara enklare att skapa ett id som endast har i uppgift att vara en unik identifierare.

Det vi har gjort nu är minimum av vad man måste kunna för att modellera en databas. Nu skall vi kika vidare på hur man gör om det till en datamodell.

6.2.2 Datamodell

ER-diagrammet används för att få en bättre förståelse för verkligheten, nu skall vi se hur vi kan få in detta i en datamodell vilket sedan blir grunden för våra tabeller i databasen.

Alla datamodeller är skapade i Vertabelo som är en grym tjänst på nätet för att bygga upp datamodeller och även skapa script för att skapa databaser. Detta kommer vi använda oss av under resten av denna kurs. För att skapa ett eget konto på Vertabelo så kolla på kunskapsdokument: Vertabelo.

Det första vi skall göra är att göra om varje entitet till en tabell och låta attributen till entiteterna bilda kolumner. Vi behöver också fundera på om våra samband behöver bli egna tabeller eller om det är möjligt att lägga detta som attribut i redan befintliga tabeller. I vårt exempel kommer vi behöva skapa en tabell även för lånesambandet eftersom det skall lagra data om när en bok lånades ut, när den skall vara tillbaka och när den faktiskt är återlämnad.

Som du ser i vår datamodell så anger vi attributet, datatyp samt vilken typ av nyckel det är. Det går också att ange om ett visst attribut får innehålla "null", i vårt exempel har jag missat det, men attributet återlämnad (date) borde absolut vara null. Detta rättar vi senare i exemplet.

Detta borde räcka och i Vertabelo så kan man här välja att exportera ett sql-script som kan skapa denna databasen i en databashanterare. Skriptet kollar vi på i senare moment.

6.2.2.1 Fördjupning

Eftersom vi körde övningen på tavlan så hade vi olika funderingar och diskussioner på olika lösningar.

1. Det första var att bryta ut författare och språk eftersom detta är data som annars behöver dubbellagras. När ett visst författarnamn skall skrivas många gånger så är risken stor att det någon gång stavas fel vilket innebär att det blir svårt att söka efter namn som är felstavade. Att ta beslut kring hur vi skall göra med data som eventuellt dubbellagras kallas för normalisering och och det finns en hel mängd regler kring hur vi skall, och inte skall, göra i dessa fall. Är du intresserad så sök på normaliseringsregler/normalformer, annars tar jag endast upp detta med de elever där de kan underlätta arbetet.
2. Ibland kan det vara moraliskt tveksamt att låta känsligt data vara nycklar, i detta fall gäller det främst personnummer. Ett sätt att komma runt detta är att skapa id som en identifierare för varje tabell. Detta id har ett "nonsensvärde" vilket innebär att det inte spelar någon roll om värdet är 7, 19 eller 299, allt värdet gör är att unikt identifiera en post och koppla ihop värden mellan tabeller. Eftersom vi vill ha en enhetlig struktur på varje tabell så skapar vi sådana här id som primärnyckel för alla tabeller.

Vi kikar på hur vårt ER-diagram skulle se ut med en sådan lösning.

Och så här ser datamodellen ut. Här ser du också att jag har döpt om id för varje tabell till tabellensNamnId för att det skall vara konsekvent och tydligt vilket id som är vilket. Kolla på tabellen Bok som exempel, de tre första fälten är alla id:n, den första är primärnyckel för tabellen Bok och de följande två är främmande nycklar från tabellerna Språk och Författare. För att förenkla kopplingen senare så väljer jag här att låta språkId heta samma sak i bägge tabellerna.

Ytterligare en sak som vi pratade om var att istället för att låna en viss bok så lånar man ett exemplar av en bok som då givetvis kan finnas i flera exemplar på biblioteket. För att lösa detta behöver vi skapa en ny entitet, vi gör ändringen både i ER-diagrammet.

När vi gör samma ändring i datamodellen och kontrollerar denna så kommer vi märka att vi får ett antal varningar.

I Vertabelo kommer vi nu få ett gäng varningar, alla dessa varningar bygger på det svenska språket. Eftersom jag gick igenom alla lager där det är viktigt att använda rätt teckenkodning så förstår ni hur lätt det blir fel, därför ändrar vi hela vår datamodell till engelska.

Rättning

I samband med att vi jobbade med senare moment så hittade jag lite fel i vår slutliga modell, vi lånar just nu böcker, men i den senaste modellen så skall vi låna ett exemplar av en bok, vilket innebär att PersonBook borde kopplas till Copy istället för Book. Nu gör jag om kopplingen, men jag döper inte om tabellen i detta fall. För att det skall vara maximalt konsekvent borde tabellen istället heta PersonCopy men det låter inte så bra så det struntar jag i. PersonBook beskriver bättre vad den gör, men allt som ändras på vägen får konsekvenser och om vi har flera sådana här missar så blir till slut modellerna helt inkonsekventa och det blir svårt att få ihop projektet. Hittar vi inga fler misstag så kan vi leva med det, skulle fler konstigheter dyka upp får vi kanske se över helheten en gång till och backa lite och göra om.

Så här skall de uppdaterade modellerna se ut, först ER-diagrammet.

Och sedan datamodellen.

Egentligen skulle jag också vilja ändra namn på tabellerna, jag vill att tabellnamnen skall börja med en gemen, alltså liten bokstav, men det får vara denna gången.

6.2.3 Uppgift

6.3 Praktik & SQL

Det var den rent teoretiska delen av databasbygget. Här kan det vara smart att stanna till en stund extra för att kolla att vi har fått med oss alla delar. Om vi gör en bra databasmodell första gången så behöver vi kanske inte komma tillbaka hit allt för många gånger och förändra.

Nu är det dags att börja med det praktiska arbetet med själva databasen.

6.3.1 SQL

SQL, Structured Query Language, är ett standardiserat programspråk för att hämta och modifiera data i en relationsdatabas. Vi kommer lära oss SQL genom att fortsätta jobba med vårt exempel. Du måste behärska SQL på den nivån att du kan skapa de SQL-frågor du behöver till dina projekt, men det är inget måste att du lär dig allt utantill, även om det kommer spara dig en hel del tid. Jag skall visa på några sätt du kan ta fram den SQL du behöver för att bygga projekten utan att skriva SQL själv.
Men som allt annat, kan du skriva det på egen hand så sparar du tid och i webbranschen kan vi enkelt använda den gamla klyschan Tid är pengar.

6.3.2 Importera databas

Vi har redan byggt upp en databas i verktyget Vertabelo (xml-filen till biblioteket finns här) och där finns det en trevlig funktion som gör att vi kan exportera hela databasen som sql-kommandon vilket gör att vi på ett enkelt sätt kan importera denna till vår databashanterare.

Vi kikar lite på sql-koden, det kan vara kul att förstå vad som händer.

-- Created by Vertabelo (http://vertabelo.com)
-- Last modification date: 2018-12-02 15:49:14.818

-- tables
-- Table: Author
CREATE TABLE Author (
    authorId int NOT NULL AUTO_INCREMENT,
    author varchar(50) NOT NULL,
    CONSTRAINT Author_pk PRIMARY KEY (authorId)
);

-- Table: Book
CREATE TABLE Book (
    bookId int NOT NULL AUTO_INCREMENT,
    authorId int NOT NULL,
    languageId int NOT NULL,
    ISBN varchar(13) NOT NULL,
    title varchar(100) NOT NULL,
    CONSTRAINT Book_pk PRIMARY KEY (bookId)
);

-- Table: Copy
CREATE TABLE Copy (
    copyId int NOT NULL AUTO_INCREMENT,
    bookId int NOT NULL,
    serialNumber int NOT NULL,
    active bool NOT NULL,
    CONSTRAINT Copy_pk PRIMARY KEY (copyId)
);

-- Table: Language
CREATE TABLE Language (
    languageId int NOT NULL AUTO_INCREMENT,
    language varchar(30) NOT NULL,
    CONSTRAINT Language_pk PRIMARY KEY (languageId)
);

-- Table: Person
CREATE TABLE Person (
    personId int NOT NULL AUTO_INCREMENT,
    personalIdentityNumber varchar(12) NOT NULL,
    name varchar(50) NOT NULL,
    address varchar(50) NOT NULL,
    loneCardNr int NOT NULL,
    CONSTRAINT Person_pk PRIMARY KEY (personId)
);

-- Table: PersonBook
CREATE TABLE PersonBook (
    personBookId int NOT NULL AUTO_INCREMENT,
    personId int NOT NULL,
    copyId int NOT NULL,
    dateForLone timestamp NOT NULL,
    dateForReturn date NOT NULL,
    dateReturned date NULL,
    CONSTRAINT PersonBook_pk PRIMARY KEY (personBookId)
);

-- foreign keys
-- Reference: Book_Author (table: Book)
ALTER TABLE Book ADD CONSTRAINT Book_Author FOREIGN KEY Book_Author (authorId)
    REFERENCES Author (authorId);

-- Reference: Book_Language (table: Book)
ALTER TABLE Book ADD CONSTRAINT Book_Language FOREIGN KEY Book_Language (languageId)
    REFERENCES Language (languageId);

-- Reference: Copy_Book (table: Copy)
ALTER TABLE Copy ADD CONSTRAINT Copy_Book FOREIGN KEY Copy_Book (bookId)
    REFERENCES Book (bookId);

-- Reference: PersonBook_Copy (table: PersonBook)
ALTER TABLE PersonBook ADD CONSTRAINT PersonBook_Copy FOREIGN KEY PersonBook_Copy (copyId)
    REFERENCES Copy (copyId);

-- Reference: PersonBook_Person (table: PersonBook)
ALTER TABLE PersonBook ADD CONSTRAINT PersonBook_Person FOREIGN KEY PersonBook_Person (personId)
    REFERENCES Person (personId);

-- End of file.

Då går vi kort igenom den kod som har genererats från Vertabelo.

-- En kodrad som börjar med -- hanteras som en kommentar och kommer inte att köras.
Varje SQL-sats som exekveras avslutas med semikolon (;), det innebär att du kan skriva kod på en rad eller på flera rader, allt som finns innan ett semikolon kommer att köras.

På raderna 6-10 skapas tabellen Author, nyckelkommandot här är CREATE TABLE, sedan listas de attribut som finns i tabellen, sedan anges vilken datatyp attributet har samt om det är tillåtet att lagra NULL i attributet eller inte. Vi har valt att ge våra primärnycklar ett automatiskt ID, detta sköts av "AUTO_INCREMENT", vilket innebär att det är en räknare som hela tiden räknar upp det värdet automatiskt. När alla attribut är skapade anges vilket attribut som skall vara primärnyckel.
Övriga tabeller skapas på samma sätt.

Från rad 61 skapas kopplingarna mellan tabellerna, här anges vilka tabeller som skall kopplas ihop och vilka attribut det är som kopplar ihop tabellerna.

All den kod som nu är genererad som ett SQL-skript kan vi naturligtvis skapa själva eller med hjälp av något program som jobbar med databaser. Vi kommer använda oss av Vertabelo för att skapa våra databasmodeller men när vi sedan skall kommunicera med databasservern så kommer vi jobba med en webbapplikation som heter phpMyAdmin och som installeras ihop med webbservern. I denna kurs behöver du inte lära dig att skriva all denna kod men du behöver kunna skapa tabeller, attribut, nycklar och kopplingar för dina databaser till dina applikationer. Hur du gör det är inte lika viktigt.

6.3.3 phpMyAdmin

Det har blivit dags att gå igenom phpMyAdmin för att börja jobba med databasen.

Det finns flera olika sätt att jobba mot en databas, denna kurs bygger på MySQL och då finns det ett ypperligt webbverktyg som följer med installationen av din webbserver. Detta verktyg finns också tillgängligt på de flesta webbhotell när vi senare skall använda databaser på en extern webbserver. phpMyAdmin når du via XAMPP's kontrollpanel där du kan klicka på Admin-knappen för MySQL eller så når du den direkt via http://localhost/phpmyadmin/.

6.3.3.1 Importera databasen till mySQL

För att importera databasen behövs förutom skriptet ovan även två kommandon köras.

6.3.3.2 create

Create används för att skapa en databas, eller en tabell.

CREATE DATABASE Library;

6.3.3.3 use

När du har loggat in i en databashanterare så har du åtkomst till alla databaser kopplade till din användare. Du måste tala om exakt vilken databas du vill jobba med. För detta ändamål använder du "use".

USE Library;

6.3.3.4 kör skriptet

Nu är det bara att kopiera hela skriptet och köra det från SQL-läget i phpMyAdmin, förhoppningsvis får vi nu inga error och då har vi en databasstruktur importerad med sex tabeller som just nu är helt utan poster.

6.4 SQL - Modifiera data

Nu har vi en fungerande databas, hos mig heter den Library. Nu skall vi kika på ett antal sql-kommandon som är bra att kunna. Naturligtvis finns det många fler kommandon än de som anges nedan, men då hänvisar jag till andra referenser, tex sql-cheat-sheet (sqltutorial.org). Här fokuserar jag på saker som du behöver kunna för att komplettera det som görs automatiskt i phpMyAdmin.

6.4.1 Drop

Drop används för att ta bort en tabell eller en databas.

DROP TABLE Person;

DROP DATABASE Library;

6.4.2 Insert

Insert använder vi för att populera, alltså lägga in data i en tabell.

INSERT INTO Person (`personId`, `personalIdentityNumber`, `name`, `address`, `loneCardNr`)
VALUES (NULL, '197511031234', 'Johan Hällgren', 'Mölnlycke', 310);

INSERT INTO Person (personId, personalIdentityNumber, name, address, loneCardNr)
VALUES (NULL, '197511031234', 'Johan Hällgren', 'Mölnlycke', 310);

Det som händer här är att vi med nyckelordet INSERT talar om att vi skall lagra något i tabellen Person, som ligger i databasen Library (om vi redan har valt databas med use Library; så kan vi skippa att anropa databasen). Vi visar också vilka attribut som skall få värden, här är det viktigt att attributnamn och värden har samma ordning i den första parentesen och i parentesen efter "VALUES", sker inte detta kommer data röras ihop och i värsta fall så får vi ett error.
Kolla på värdet för personId som är satt till NULL, det innebär att vi inte skriver in något värde, istället kommer databasen ge detta attribut ett automatiskt värde, i detta fall ett inkrementerat värde, alltså ett nummer högre än värdet på posten innan.

Det går att skriva denna kod lite kortare, vilket vi kommer kika på senare, det kan ibland vara skönt när vi sedan skall skicka koden från php. Här gäller det dock att vara noga att vi har skrivit attributen i samma ordning som de är registrerade i tabellen. Felmarginalen här är noll, så var säker på vad du gör. Eventuella felmeddelanden som genereras i databasen kommer du inte se när du kodar php, det enda som händer är att det inte fungerar som du vill. För att testa dina sql-satser så behöver du köra dem direkt i databasen för att kunna läsa av eventuella felmeddelande om/när det uppstår.

6.4.3 Delete

Delete är ett kommando som gör precis vad vi tror, det tar bort en, eller flera poster, i en tabell.

DELETE FROM Person WHERE personId = 1;

I koden ovan så tar vi bort posten som har personId = 1, vi kan välja att ta bort flera poster genom att t.ex. ta bort alla personer som bor i Mölnlycke.

DELETE FROM Person WHERE address = 'Mölnlycke';

Skriver du utan villkor (WHERE) så tar du bort alla personer i databasen.

Var försiktig med delete, så länge du inte använder transaktionshantering så går det inte att återskapa några borttagna poster.

Transaktionshantering ligger utanför denna kursen men handar kort om att flera händelser som hänger ihop något sätts körs i en serie. Om någon av SQL-satserna inte kan köras av någon anledning så rullar man tillbaka alla transaktionerna vilket gör att databasens poster återställs som det var innan vi körde transaktionen.

6.4.4 Update

Update används för att uppdatera en, eller flera, poster.

UPDATE Person
SET name = 'Anna Andersson', address = 'Alingsås'
WHERE personId = 1;

Här väljer jag att uppdatera namn och adress på den personen som har personId = 1. Precis som när vi använde oss av DELETE så är det viktigt att komma ihåg villkoret (WHERE) annars kommer alla användare få ändrad namn och adress.

Lägg också märke till att jag här skriver SQL-satsen över fyra rader, det kan ofta vara enklare att läsa på det sättet när man skall se en SQL-sats för sig själv. När vi senare skickar den inbyggd från PHP så är det ofta smidigare att skriva ihop den på en rad.

6.4.5 Säkerhetskopiera

I phpMyAdmin finns möjligheten exportera hela databasen som ett sql-script vilket innebär att alla tabeller, nycklar, relationer, andra strukturer samt tabellernas innehåll kan exporteras och enkelt läggas in i en annan databashanterare eller säkerhetskopieras som en fil.

6.5 SQL - söka efter data

Jag har skapat 5 poster till tabellen Person i min databas. Vill du importera dem till din databas så är skriptet här:

INSERT INTO Person VALUES
(NULL, '197511031234', 'Johan Hällgren', 'Mölnlycke', 310),
(NULL, '198001011111', 'Anna Andersson', 'Göteborg', 311),
(NULL, '199012241414', 'Tom Tén', 'Mölndal', 351),
(NULL, '200110191234', 'Klas Sten', 'Göteborg', 354),
(NULL, '199912319999', 'Yngve Sen', 'Mölnlycke', 355);

INSERT INTO Person (`personId`, `personalIdentityNumber`, `name`, `address`, `loneCardNr`) VALUES
(NULL, '197511031234', 'Johan Hällgren', 'Mölnlycke', 310),
(NULL, '198001011111', 'Anna Andersson', 'Göteborg', 311),
(NULL, '199012241414', 'Tom Tén', 'Mölndal', 351),
(NULL, '200110191234', 'Klas Sten', 'Göteborg', 354),
(NULL, '199912319999', 'Yngve Sen', 'Mölnlycke', 355);

Att fylla en tabell med data kallas att populera en tabell/databas. Ibland vill vi ha lite större testmängder i våra databaser än enstaka rader som vi själva producerar. Antingen så skapar vi något eget script som slumpar detta data innan vi lägger in det i databasen eller så använder vi någon tjänst, online eller fristående applikation, som gör just detta åt oss. Två av de som jag själv använder vid tillfälle är generatedata.com och mockaroo.com. Testa dem gärna, de genererar lite olika typer av data olika bra.

6.5.1 Söka med *

Nu skall vi lära oss att söka i vår tabell Person.

SQL-skript för söka efter poster

SELECT * FROM Person;

Utskrift

* (stjärna) betyder här att vi hämtar alla attribut från tabellen Person.

Om vi bara vill ha personer som kommer från 'Mölnlycke' i vårt resultat skriver vi så här;

SQL_skript för att hämta poster baserat på villkor

SELECT *
FROM Person
WHERE address = 'Mölnlycke';

Att skriva på tre rader eller en enda rad spelar ingen roll, allt som skrivs innan semikolon kommer köras som ett SQL-kommando.

SELECT * FROM Person WHERE address = 'Mölnlycke';

Resultatet blir då så här;

Utskrift

SQL-skript för att söka poster med flera villkor

Vi kan slå ihop flera argument för att specificera vår lista. Då är det AND som skall användas;

SELECT *
FROM Person
WHERE address = 'Mölnlycke'
AND loneCardNr < 350;

Resultatet blir då så här;

Utskrift

Vi kan naturligtvis också använda OR som operator;

SELECT *
FROM Person
WHERE address = 'Mölnlycke'
OR loneCardNr < 350;

Resultatet blir då så här;

Utskrift

6.5.2 Välj vilka attribut som skall hämtas

Det är ju möjligt att hämta alla attribut och sedan bestämma mha php vad som skall visas eller användas, men att hämta mer än vad man behöver är ju onödigt. Bättre då att lära sig hur man hämtar bara det man vill ha.

SQL-skript för att välja attribut som skall visas

SELECT loneCardNr, name, address
FROM Person;

Den sökningen ger detta resultat;

Utskrift

Dags att sortera svaret...

SELECT loneCardNr, name, address
FROM Person
ORDER BY name ASC;

... med resultatet ....

Utskrift

ASC står för ascending och är 1-9 eller A-Ö, motsatsen är DESC som står för descending.

6.6 SQL - söka från flera tabeller

Att söka från en tabell har bara en viss mängd av svårighet, det blir klurigare när vi skall slå ihop flera tabeller för att ställa frågor. För att du själv skall kunna testa de exempel jag går igenom här så kan det vara bra att ha populerat databasen.

Om du saknar själva databasen så finns den under avsnitt 6.3.2.

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Databas: `Library`
--

--
-- Dumpning av Data i tabell `Author`
--
INSERT INTO `Author` (`authorId`, `author`) VALUES
(1, 'Lars Kepler'),
(2, 'Anders Jallai');

--
-- Dumpning av Data i tabell `Language`
--
INSERT INTO `Language` (`languageId`, `language`) VALUES
(1, 'Svenska'),
(2, 'Engelska'),
(3, 'Tyska'),
(4, 'Franska');

--
-- Dumpning av Data i tabell `Book`
--
INSERT INTO `Book` (`bookId`, `authorId`, `languageId`,
    `ISBN`, `title`) VALUES
(1, 1, 1, '9789170018831', 'Paganinikontraktet'),
(2, 1, 2, '9780007478262', 'The Nightmare'),
(3, 1, 3, '9783785724286', 'Paganinis Flucht'),
(4, 1, 1, '9789100136758', 'Stalker'),
(5, 1, 2, '9789100136741', 'Stalker'),
(6, 2, 1, '9789198131550', 'I rikets tjänst'),
(7, 2, 1, '9789198131505', 'Bilderbergmötet'),
(8, 2, 1, '9789198131536', 'Spionen på FRA 2.0'),
(9, 2, 1, '9789174610222', 'Landsförädaren');

--
-- Dumpning av Data i tabell `Copy`
--
INSERT INTO `Copy` (`copyId`, `bookId`, `serialNumber`,
    `active`) VALUES
(1, 1, 1, 1),
(2, 1, 2, 1),
(3, 1, 3, 1),
(4, 1, 4, 1),
(5, 1, 5, 1),
(6, 2, 1, 1),
(7, 2, 2, 1),
(8, 2, 3, 1),
(9, 2, 4, 1),
(10, 3, 1, 1),
(11, 3, 2, 1),
(12, 3, 3, 1),
(13, 4, 1, 1),
(14, 5, 1, 1),
(15, 5, 2, 1),
(16, 5, 3, 1),
(17, 5, 4, 0),
(18, 5, 5, 1),
(19, 6, 1, 1),
(20, 6, 2, 1),
(21, 6, 3, 1),
(22, 7, 1, 1),
(23, 7, 2, 1),
(24, 7, 3, 1),
(25, 7, 4, 1),
(26, 7, 5, 1),
(27, 8, 1, 1),
(28, 8, 2, 1),
(29, 9, 1, 1);


--
-- Dumpning av Data i tabell `Person`
--
INSERT INTO `Person` (`personId`, `personalIdentityNumber`,
    `name`, `address`, `loneCardNr`) VALUES
(1, '197511031234', 'Johan Hällgren', 'Mölnlycke', 310),
(2, '198001011111', 'Anna Andersson', 'Göteborg', 311),
(3, '199012241414', 'Tom Tén', 'Mölndal', 351),
(4, '200110191234', 'Klas Sten', 'Göteborg', 354),
(5, '199912319999', 'Yngve Sen', 'Mölnlycke', 355);

--
-- Dumpning av Data i tabell `PersonBook`
--
INSERT INTO `PersonBook` (`personBookId`, `personId`,
    `copyId`, `dateForLone`, `dateForReturn`, `dateReturned`)
    VALUES
(1, 1, 4, '2015-02-09 20:12:55', '2015-03-02', NULL),
(2, 1, 4, '2015-02-09 20:13:21', '2015-03-15', NULL),
(3, 1, 21, '2015-02-18 20:13:51', '2015-03-05', '2015-03-06'),
(4, 2, 15, '2015-03-15 20:15:25', '2015-02-02', NULL);

6.6.1 Join

Join är nyckelordet för att slå ihop tabeller, via nycklar, och på det sättet få sammansatta resultat.

6.6.1.1 INNER JOIN

Inner join används när vi bara vill ha resultat som kopplas. Ett exempel är en lista på alla böcker samt författare.

SQL-skript för att koppla tabeller med INNER JOIN

SELECT a.author, b.title
FROM Book b
INNER JOIN author a
ON b.authorId = a.authorId
ORDER BY a.author ASC, b.title ASC;

Lägg märke till att jag har skapat alias för tabellerna för att kunna skriva en kortare kod. På rad 2 och 3 så talar jag om att jag vill skapa alias b för tabellen Book och alias a för tabellen Author.
Frågan ger följande resultat.

Utskrift

6.6.1.2 OUTER JOIN

Med outer join så hämtas och data som inte fullt ut är kopplat mellan tabeller. Jag visar på exempel i tre steg.

Steg 1. Jag vill veta hur många böcker det finns av varje språk i vårt biblioteket. Först tar vi fram alla böcker och vilket språk de är skrivna på.

SQL-skript för att koppla två tabeller

SELECT l.language, b.title
FROM Language l
INNER JOIN Book b
ON b.languageId = l.languageId;

Inga konstigheter med frågan, resultatet blir följande;

Utskrift

Steg 2. Nu skall vi gruppera antalet böcker på språk och sedan räkna hur många det finns av varje språk.

SQL-skript för att koppla två tabeller

SELECT l.language Language, COUNT(b.title) Quantity
FROM Language l
INNER JOIN Book b
ON b.languageId = l.languageId
GROUP BY l.language
ORDER BY Quantity DESC;

Här behövs det kanske en förklaring på några saker, på rad 1 döper jag om bägge kolumnerna i svaret till "Language" respektive "Quantity", detta gör jag mest för att det skall vara snyggare och enklare att jobba med när det kommer till php. Hade jag inte döpt om den beräknade kolumnen hade jag behövt ropa på ett index som heter "COUNT(b.title)" i php och det vill jag helst inte.
På rad 5 talar jag om vad som skall grupperas, och i detta fall vill jag gruppera på Language. Om jag inte grupperar så kan jag heller inte räkna på någon kolumn.
På rad 6 talar jag om att jag vill sortera listan efter antal böcker på skrivet språk från högst till lägst.
Resultatet blir så här.

Utskrift

Jättefin lista, men det skrivs inte ut att det finns 0 böcker på franska, i detta fallet har jag ju faktiskt skapat möjlighet i vårt bibliotek att lagra franska böcker. Ett annat exempel då detta skulle vara intressant är när jag vill skriva ut en lista på alla våra registrerade låntagare och hur många böcker de har lånat senaste halvåret. Vill jag bara ha ut de som lånat böcker så funkar INNER JOIN, men vill jag ha en lista på alla måste jag istället använda OUTER JOIN. Vi testar och ser hur det skulle se ut i vårt exempel.

SQL-skript för att koppla två tabeller

SELECT l.language Language, COUNT(b.title) Quantity
FROM Language l
LEFT OUTER JOIN Book b
ON b.languageId = l.languageId
GROUP BY l.language
ORDER BY Quantity DESC;

Den enda skillnaden ser vi på rad 3, här står det LEFT OUTER JOIN istället för INNER JOIN och det betyder att vi även skall ta med de okopplade värdena från tabellen Language, som i detta fall anses som den vänstra tabellen, den kommer ju först. Hade vi velat skriva ut okopplade värden från den högra tabellen, i detta fall Book, så hade resultatet blivit exakt som när vi körde INNER JOIN.
Resultatet blir då.

Utskrift

Jag förstår att detta blir lite rörigt. Det är mycket nytt, vi skall både skrapa på ytan samtidigt som ni behöver kunna vissa saker. Mest lär ni er genom att hamna i en situation där du måste skriva en viss SQL-sats och sedan prova dig fram eller leta efter tips i något referensverk, t.ex. SQL Quick Reference from W3Schools eller någon av de cheat-sheet jag länkat tidigare. Vi kommer i en tutorial jobba med att bygga upp en databas och sedan skapa de SQL-satser som behövs, så det kommer mer tillfällen att träna på detta. Tveka heller inte att fråga.

Genom att veta exakt vad du vill ha för resultat och i vilka tabeller datat hittas och hur det hänger ihop så bygger du upp även de mest komplexa frågorna. Det viktigaste är att ha koll på databasstruken.

6.6.2 Vyer

En vy fungerar som en virtuell tabell. Om vi ofta ställer en SQL-fråga som använder data från flera olika tabeller så blir denna SQL-fråga ofta ganska så stor, då kan vi välja att skapa en vy istället, vilket gör att vyn innehåller SQL-frågan och vi kan skicka en enklare SQL-sats till databasen. Vi visar med ett exempel.

Vi tar exemplet ovan där vi listade antalet böcker på varje språk, den SQL-satsen var på 6 rader, det går naturligtvis att skriva väldigt mycket mer avancerade sql-satser, t.ex. lista alla utlånade böcker som ännu inte är tillbakalämnade och ge alla information om de enskilda böckerna inklusive språk och författarens namn, men vi nöjer oss med ett enklare exempel.

Vi börjar med att skapa vyn, det innebär att vi lagrar sql-kommandot i databasen.

CREATE VIEW LanguageQuantity AS
SELECT l.language Language, COUNT(b.title) Quantity
FROM Language l
LEFT OUTER JOIN Book b
ON b.languageId = l.languageId
GROUP BY l.language
ORDER BY Quantity DESC;

Samma SQL-sats som tidigare förutom första raden där vi talar om att sql-satsen skall skapa en vy. Denna vyn kommer nu ligga i databasen och när vi vill ställa en fråga till vyn så skriver vi bara följande kod.

SELECT * FROM LanguageQuantity;

Det som då händer är att resultatet kommer se likadant ut som tidigare.

Utskrift

Det går naturligtvis att ge vyn vissa argument också....

SELECT * FROM LanguageQuantity WHERE Quantity > 0;

.... med resultatet....

Utskrift

Det gick ju smidigt. Innebär detta att vi alltid skall skapa vyer för varje SQL-sats som skall användas i ett projekt? Nej, använd det där det behövs. Vem som bestämmer när det behövs? Det gör du själv. Fundera på vilket som är smidigast och lös problemet på det sättet. Har du en väldigt avancerad SQL-sats som dessutom tar väldigt långt tid att exekveras så kan du dessutom addera ett index till denna vyn för att göra exekveringen snabbare. Det är överkurs i detta sammanhang men kan vara bra att veta.

6.7 Uppgift

6.8 Koppla PHP till databasen

Dags att kika lite på hur det fungerar när vi skall koppla databasen till de PHP-applikationer som vi skall bygga.

6.8.1 PDO (PHP Data Objects)

Det finns olika sätt att via PHP koppla sig mot en MySQL-databas. Om du använder äldre webbapplikationer så används ofta gränsnitten mysql eller mysqli. mysql var den första varianten och den är vidareutvecklad till mysqli, i står för improved. Fördelen med mysql(i) är att de är förhållandevis enkla att jobba med, nackdelen är att de har sämre säkerhet och fungerar bara på databasen mysql. Vill du använda en annan databas så måste du använda andra funktioner som kan se annorlunda ut och funderar lite annorlunda. Från och med PHP version 7 så är mysql-funktionerna dessutom avstängda.

PHP PDO, PHP Data Object, är ett gränssnitt som är objektorienterat, det har högre inbyggd säkerhet och det jobbar på samma sätt mot olika databaser vilket innebär att du som utvecklare inte behöver bry dig om databasen är mySQL, SQLServer, Oracle eller något annat utan kan använda dessa funktioner på samma sätt.

PHP blir mer och mer objektorienterat som språk och PDO är då en lämplig teknik för att kommunicera med databaser. PDO erbjuder bättre säkerhet och inbyggd felhantering.

PDO har höjt säkerheten genom att man först förebeder objektet för den SQL-sats som skall köras och sedan kör man denna SQL-sats på ett kontrollerat sätt. Detta kallas för prepared statements och höjer säkerheten för kommunikationen med databasen. Vi kommer i nästa moment kika på hur man kan skicka skadlig SQL-kod till databasen i syfte att förstöra eller hämta mer information än vad som var tanken, en av dessa faror kallas för SQL_injection. PDO har också inbyggd funktionalitet för transaktionshantering vilket innebär att man sätter upp ett antal SQL-satser som skall köras. Om alla dessa kan köras utifrån de förutsättningar som man har bestämt så genomförs förändringarna i databasen. Om någon av SQL-satserna inte kan genomföras så rullar man tillbaka alla händelser så att inget påverkas i databasen.

6.8.2 Uppkoppling mot databasen

Här följer några exempel på den kod som används för att koppla upp ett PDO-objekt mot databasen.

<?php
$pdo = new PDO("mysql:host=url_till_databasen;dbname=databasens_namn", 'användarnamn', 'lösenord');
?>

Detta är det enklaste sättet och med det som är absolut nödvändigt för att kunna kommunicerar med databasen. Anropet har tre parametrar;

1. Adress och namn på databasen
2. Användarnamn
3. Lösenord

Ibland vill man lägga till några fler inställningar till uppkopplingen. Här lägger vi till en fjärde parameter som innehåller en array med flera olika inställningar.

<?php
$pdo = new PDO("mysql:host=url_till_databasen;dbname=databasens_namn", 'användarnamn', 'lösenord', array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'", PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES, false);));
?>

• PDO::MYSQL_ATTR_INIT_COMMAND - används för att sätta UTF8 som teckenkod för uppkopplingen.
• PDO::ATTR_ERRMODE - ställ in att error skall rapporteras.
• PDO::ERRMODE_EXCEPTION - kastar exceptions
• PDO::ATTR_EMULATE_PREPARES - en inställning som talar om hur PDO skall hantera prepared statements.

Här förstår du också att det finns många fler inställningar att göra, men för det vi skall göra i denna kursen så räcker detta långt.

Som du märker är det inte det enklaste att skriva denna koden och den är inte speciellt snyggt uppställd eller lätt att varesig följa eller ändra. Därför brukar jag rekommendera att skriva informationen på ett annat sätt.

<?php
# Variabler
$host = "localhost";      // Den server där databasen ligger
$user = "root";           // Ditt användarnamn
$pwd  = "";               // Ditt lösenord (XAMPP -> "", vissa andra webbservrar -> "root")
$db   = "databasensnamn"; // Databasen vi vill jobba mot

# dsn - data source name
$dsn = "mysql:host=$host;dbname=$db";

# Inställningar som körs när objektet skapas
$options  = array(
  PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'",
  PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_EMULATE_PREPARES, false);

# Skapa objektet eller kasta ett fel
try {
  $pdo = new PDO($dsn, $user, $pwd, $options);
}
catch(Exception $e) {
    die('Could not connect to the database:<br/>'.$e);
}
?>

Många fler rader kod men tydligare uppställt, lättare att följa och ändra samtidigt som $user och $pwd kommer bli maskade när vi senare skall visa koden via CSource och det är ju nog så viktigt.

Vad som är nytt i denna koden jämfört med de tidigare exemplen är att om det inte fungerar att skapa en databasuppkoppling av någon anledning så kastas ett fel och ett meddelande skrivs ut till användaren.

Denna koden brukar jag lägga i en egen fil databaseConnection.php eller så lägger jag in all kod för uppkopplingen i en funktion som är lätt att anropa och på det sättet kan jag gömma koden så att den inte syns hela tiden.

6.8.3 Ställ fråga mot databas

-- Created by Vertabelo (http://vertabelo.com)

-- Tar bort databasen om den redan finns
DROP DATABASE IF EXISTS user_db;

-- Skapar databasen, ta bort om databasen redan finns
CREATE DATABASE user_db;

-- Väljer rätt databas
USE user_db;

-- Skapar tabellen
CREATE TABLE user (
   userId int NOT NULL AUTO_INCREMENT,
   username varchar(30) NOT NULL,
   password varchar(30) NOT NULL,
   CONSTRAINT user_pk PRIMARY KEY (userId)
);

-- Populerar databasen
INSERT INTO `user` (`userId`, `username`, `password`) VALUES
(1, 'admin', 'admin'),
(2, 'user', 'user'),
(3, 'johan', 'johan');

Nu när vi har ett databasobjekt är det detta objekt vi skall jobba med för att skapa och köra SQL-frågor.

Med PDO så höjer vi säkerheten genom att dela upp frågeställningen i två delar;

• prepare (förbereder)
• execute (binder parametrar och kör frågan)

Detta gör vi för att hindra användaren att på ett felaktigt sätt manipulera databasen, sk SQL-injection.

Vi kika på hur vi skriver koden för att skicka ett SQL-kommando till databasen.

<?php
require_once('databasuppkopplingen.php');
$sql = "SELECT * FROM user";
$stm = $pdo->prepare($sql);
$stm->execute();
$res = $stm->fetchAll(PDO::FETCH_ASSOC);
?>

<?php
# Variabler
$host = "localhost";      // Den server där databasen ligger
$user = "root";           // Ditt användarnamn
$pwd  = "";               // Ditt lösenord (XAMPP -> "", vissa andra webbservrar -> "root")
$db   = "user_db";        // Databasen vi vill jobba mot

# dsn - data source name
$dsn = "mysql:host=$host;dbname=$db";

# Inställningar som körs när objektet skapas
$options  = array(
  PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'",
  PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_EMULATE_PREPARES, false);

# Skapa objektet eller kasta ett fel
try {
  $pdo = new PDO($dsn, $user, $pwd, $options);
}
catch(Exception $e) {
    die('Could not connect to the database:<br/>'.$e);
}
?>

Variabeln $stm är ett objekt av klassen PDO Statement och är objektet av den sql-fråga som vi skapar på rad 3 och som vi förbereder på rad 4. Just att köra prepare gör att vi kan dra nytta av inbyggd säkerhet för att undvika sabotage av vår databas.

På rad 5 så exekveras frågan och på rad 6 hämtar vi ut vårt resultset från databasen och lagrar detta i variabeln $res. Vi använder argumentet PDO::FETCH_ASSOC för att välja hur våra index skall namnges i arrayen. I detta fallet så får indexet samma namn som attributet i sql-frågan, jag tycker att det blir lättare att arbeta med det om man vet att username lagras i index username och inte med ett sifferindex som kan vara 0, 1 eller något annat. I just detta exempel skulle det fått värdet 1 eftersom det är den andra kolumnen i vårt resultset.

Kanske läge att förklara vad som händer på rad 2. Jag har valt att lägga databasuppkopplingen i en egen fil så kan jag läsa in den på de sidor där den behövs. Funktionen require_once() används för att säkerställa att denna filen bara läses in en gång, skulle man inkludera samma fil från flera olika delar av programmet, vilket man riskerar att göra i större filer, så ser denna funktionen till att den inte läses in igen om den redan finns inläst. require_once() och include_once() gör egentligen samma sak, förutom när det blir något fel med inläsningen och ett error skall genereras, då skickar include/include_once en varning medan require/require_once stoppar scriptet och skickar ett "fatal error". I detta fallet så vill vi ju inte fortsätta scriptet om filen med databasinformation inte finns.

Om vi vill skriva ut innehållet i variabeln $res så gör vi det med print_r() och får följande utskrift. Glöm inte echo "<pre>"; om du vill få en mer uppställd utskrift.

Array
(
    [0] => Array
        (
            [userId] => 1
            [username] => admin
            [password] => admin
        )
    [1] => Array
        (
            [userId] => 2
            [username] => user
            [password] => user
        )
    [2] => Array
        (
            [userId] => 3
            [username] => johan
            [password] => johan
        )
)

Här ser du att vi får en tvådimensionell array som vi enklast loopar igenom med foreach(). För att skriva ut innehållet i $res så att det blir läsligt så kan du skriva på detta sätt.

<?php
# Create the table from resultset
$table ="<table><tr><th>userId</th><th>username</th><th>password</th></tr>";
foreach ($res as $row) {
  $table .= "<tr>";
  $table .= "<td>{$row['userId']}</td>";
  $table .= "<td>{$row['username']}</td>";
  $table .= "<td>{$row['password']}</td>";
  $table .= "</tr>\n";
}
$table .= "</table>";
echo $table;
?>

Detta kodexempel kommer ge följande utskrift.

6.8.4 Ställ fråga mot databas med parameter

Ofta kopplar vi ju frågorna mot databasen utifrån vilken information vi vill fråga efter, att alltid fråga efter alla användare i vårt system är ju inte det enda vi vill göra. Om vi vill fråga efter en specifik användare så skriver vi följande kod.

<?php
# Om vi hämtar data från ett formulär
#$username = trim($_POST['username']);
$username = 'admin';

$sql = "SELECT * FROM user WHERE username = :username;";
$stm = $pdo->prepare($sql);
$stm->execute(array('username' => $username));
$res = $stm->fetch(PDO::FETCH_ASSOC);

echo "<pre>";
print_r($res);
echo "</pre>";

echo "<p>Du har angivit användarnamn: " .$res['username']."</p>";
?>

Skillnaden i detta exempel mot det som du har sett tidigare är;

Parameter i SQL-satsen markeras med :, mitt tips är att inte krångla till det utan att låta alla variabler/parameterar som gör samma sak heta samma. När det gäller användarnamn så heter attributet username i tabellen user, i formuläret heter variabeln 'username' ($_POST['username']), parametern heter :username och den variabeln som jag skapar för att hämta data från formuläret heter $username. Det blir enklare att få ihop allt om vi enkelt använder rätt namn.

Alla parametrar kopplas när vi kör execute genom att skapa en array med de parametrar som skall användas. Här kan du ha många parametrar om det är en specifik fråga.

Vet jag att frågan bara skall ge ett svar så kan jag använda mig av fetch() istället för fetchAll() vilket innebär att jag istället för en tvådimensionell array som svar få en enkel array. På det sättet behöver jag inte loopa igenom utan kan använda arrayen direkt.

Detta kodexempel kommer ge följande utskrift.

Array
  (
      [userId] => 1
      [username] => admin
      [password] => admin
  )
  

6.8.5 Insert, update och delete

När vi skickar andra SQL-satser till databasen för att skapa, uppdatera eller ta bort poster så skiljer det sig inte så mycket från det vi tidigare har gjort. Den största skillnaden är att vi inte får något resultset tillbaka som svar.

<?php
$sql = "INSERT INTO user (userId, username, password) VALUES (NULL, 'new', 'new');";
$stm = $pdo->prepare($sql);
$stm->execute();
$lastId = $pdo->lastInsertId();

echo "<p>Du har lagt till en användare med id: ".$lastId."</p>";
?>

Det som är mest intressant här är att vi frågar vårt pdo-objekt vilket id den nya användaren har fått. Detta fungerar endast om vi redan har talat om att det skall vara en automatisk räknare så att databasen själv tar hand om detta.

Detta kodexempel kommer ge följande utskrift.

6.9 Inlämningsuppgift