# Lektion 15 — PostgreSQL og SQL Christian Budtz — [chbu@dtu.dk](mailto: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. ```mermaid 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. ```sql 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](/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`: ```sql 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. ```sql 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 ```sql 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](/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](oevelser.md). --- # 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: ```sql 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` ```sql 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` ```sql 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](https://medcom.dk/standarder/tabeller/nationale-test-cpr-numre/). 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](/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](oevelser.md). --- ## Ø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](oevelser.md).