Lektion 15 — PostgreSQL og SQL

Christian Budtz — chbu@dtu.dk

Program i dag

• Opsamling: tabel, nøgler, SELECT, WHERE, INSERT
• Quiz: forberedelsen
• Gennemgang: Neon og CREATE TABLE
• Quiz: tabel og typer
• Øvelse 1: Patienttabellen
• Gennemgang: UPDATE og DELETE
• Quiz: WHERE før skrivning
• Øvelse 2: Ret, slet, og de fem testpatienter
• Øvelse 3: Gruppens tabel

Pauser lægges ind undervejs.

Læringsmål i dag

Efter lektionen skal du kunne:

• forklare tabel, række, kolonne, primærnøgle og fremmednøgle
• oprette patients med SERIAL, VARCHAR til CPR og PRIMARY KEY
• hente, indsætte, rette og slette en række i Neons SQL-editor
• prøve WHERE med SELECT, før UPDATE og DELETE, og tjekke tabellen med \d
• forklare, hvorfor listen i server.js glemmer, og tabellen husker
• oprette gruppens database i Neon og dele connection-stringen som en hemmelighed

Opsamling — forberedelsen

Listen glemmer. Tabellen husker.

Stop serveren fra lektion 13, og start den igen. Patienterne, I POST'ede, er væk — listen bor i serverens hukommelse. Gør det samme efter en INSERT i en tabel, og rækken er der stadig. Databasen er et andet program, der gemmer den.

I dag skriver I til tabellen fra SQL-editoren. Routen kender den endnu ikke.

flowchart LR
  subgraph laptop [Jeres bærbare]
    express[Express] --> liste[Liste i hukommelsen]
    editor[SQL-editor i browseren]
  end
  subgraph neon [Neon]
    patients[Tabel patients]
  end
  editor -->|SQL| patients

Række og kolonne

En tabel har navngivne kolonner og et vilkårligt antal rækker. Én række er én patient. Kolonnerne er de felter, rækken altid har.

I SQLBolt hed tabellen movies. Kolonnerne var titel, år og instruktør. Det er samme form som patientlisten: cpr og navn.

De tre sætninger fra SQLBolt

SELECT henter kolonner. WHERE beholder de rækker, betingelsen passer på. INSERT INTO med kolonnenavne lægger en ny række i.

SELECT title, year
FROM movies
WHERE year = 1995;

INSERT INTO movies (title, year, director)
VALUES ('Toy Story', 1995, 'John Lasseter');

Tekst står i enkelte anførselstegn. Hver sætning slutter med semikolon.

Nøgler

En primærnøgle udpeger én række. To rækker må ikke have den samme værdi, og værdien må ikke mangle.

En fremmednøgle er en kolonne, der peger på primærnøglen i en anden tabel. maalinger.patient_id peger på patients.id, så en måling kan findes igen på den patient, den hører til. I dag opretter I kun patients.

Hvad vi ikke tager i dag

Joins og en anden tabel venter, til gruppen har to slags rækker. server.js skal blive stående. At app.get forbinder til Neon og kalder tabellen, er lektion 17.

Quiz — forberedelsen

Quiz!

Gå til /quiz og indtast koden fra tavlen.

Lektion 15: forberedelse — tabel, SELECT, WHERE, INSERT og nøgler.

Gennemgang — tabellen i PostgreSQL

Neon og SQL-editoren

Neon kører PostgreSQL på en server. SQL-editoren i browseren er jeres klient: den sender sætningen, og databasen svarer med rækker. Det er client-server fra lektion 11 — bare med SQL i stedet for HTTP.

Projektet lektion15 har databasen neondb. Tjek øverst i editoren, at det er den, du står i. \dt viser tabellerne. \d patients viser kolonner og nøgler.

Første forespørgsel efter en pause tager et par sekunder. Databasen går i dvale efter fem minutter uden brug og vågner selv.

Opret tabellen

I SQL-editoren på lektion15:

CREATE TABLE patients (
  id SERIAL PRIMARY KEY,
  cpr VARCHAR(10) NOT NULL UNIQUE,
  navn VARCHAR(200) NOT NULL
);

SERIAL nummererer id selv, fra 1 og op. PRIMARY KEY på id gør den til rækkens identitet. UNIQUE på cpr siger, at samme CPR ikke må findes to gange.

CPR er tekst

Max Test Berggren har CPR 0107729995. Som INTEGER bliver det til 107729995 — et tal har ikke et foranstillet 0. Nancys 2512489996 kan slet ikke gemmes: integer out of range. INTEGER går kun til ca. 2,1 milliarder. CPR er et ID, ikke et tal man regner med. VARCHAR gemmer tegnene, som de står.

INSERT INTO patients (cpr, navn)
VALUES ('0107729995', 'Max Test Berggren');

id er udeladt. SERIAL sætter den.

Hent den række, du lige lagde i

SELECT id, cpr, navn
FROM patients
WHERE cpr = '0107729995';

SELECT * henter alle kolonner. Skriv kolonnenavnene, når du vil se, at cpr stadig har det foranstillede 0.

Quiz — tabel og typer

Quiz!

Gå til /quiz og indtast koden fra tavlen.

Lektion 15: tabel — CREATE TABLE, SERIAL, VARCHAR og \d.

Øvelser

Øvelse 1: Patienttabellen

Opret patients i jeres projekt lektion15. Læg to rækker i, og hent dem med WHERE.

• SQL-editoren står på lektion15
• cpr er VARCHAR
• server.js er urørt

Detaljerne står i øvelsesarket.

Pause

Gennemgang — ret og slet

Prøv WHERE med SELECT først

UPDATE og DELETE rammer hver række, som WHERE passer på. Uden WHERE rammer de hele tabellen.

Skriv betingelsen som en SELECT, og se, at det er den ene række, du mener:

SELECT id, cpr, navn
FROM patients
WHERE cpr = '2512489996';

Først når den viser Nancy, og kun Nancy, bruger du samme WHERE til at skrive.

UPDATE

UPDATE patients
SET navn = 'Nancy Test Berggren'
WHERE cpr = '2512489996';

SET er de kolonner, der ændres. De andre kolonner bliver stående. Kør SELECT med samme WHERE bagefter, og læs navnet.

DELETE

DELETE FROM patients
WHERE cpr = '0000000000';

Rækken er væk. En ny SELECT med samme WHERE skal give nul rækker. DELETE uden WHERE tømmer tabellen.

De fem testpatienter

Numrene er MedComs test-CPR. De må kun bruges i undervisning. Tabellen har de samme felter som listen i lektion 7: cpr og navn. Password hører ikke til i tabellen.

CPR	Navn
2512489996	Nancy Ann Test Berggren
2911829996	Kirsten Test Berggren
0107729995	Max Test Berggren
1509819996	Brita Test Berggren
3103979995	Anders Test Jensen

Quiz — WHERE før skrivning

Quiz!

Gå til /quiz og indtast koden fra tavlen.

Lektion 15: ret og slet — UPDATE, DELETE og WHERE.

Øvelser

Øvelse 2: Ret, slet, og de fem

Ret et navn, slet en række, I selv har lagt i ved en fejl, og sørg for, at de fem testpatienter ligger i patients.

Detaljerne står i øvelsesarket.

Øvelse 3: Gruppens database

Øvelse 1 og 2 var jeres egne databaser. Nu vælger I én: ét gruppemedlem opretter projektets database på sin konto. Opret patients med de fem og én tabel til gruppens projekt. Skriv fremmednøglen på papir, hvis I har to slags rækker.

Ejeren deler connection-stringen fra Connect. Den er et password: aldrig i git, aldrig på et screenshot.

Det er den database, server.js forbinder til i lektion 17.

Detaljerne står i øvelsesarket.