Abschnitt 12 · Lektion 4
Daten abfragen
Auf einer Übersichtsseite sollen die neuesten Bücher zuerst stehen, nur drei davon, und eine Zahl
darüber. Mit filter und sort in JavaScript geht das. Nur holt das Programm dafür jede Zeile aus
der Tabelle, auch die, die es gleich wieder wegwirft.
Vier Wörter, die man ständig braucht
export const AUFBAU = `CREATE TABLE buecher (
id INTEGER PRIMARY KEY,
titel TEXT NOT NULL,
jahr INTEGER
);
INSERT INTO buecher (titel, jahr) VALUES
('Node in der Praxis', 2020),
('JavaScript von vorn', 2019),
('HTTP verstehen', 2017),
('Datenbanken im Alltag', 2014),
('Die lange Nacht des Debuggens', 2022),
('Streams und Puffer', 2016),
('Nachtschicht mit Node', 2023),
('Sicher ausliefern', 2009),
('Testen ohne Angst', 2021),
('Fehler lesen lernen', 2019);`; import { DatabaseSync } from "node:sqlite";
import { AUFBAU } from "./bestand.js";
const db = new DatabaseSync(":memory:");
db.exec(AUFBAU);
const jung = db.prepare("SELECT titel, jahr FROM buecher WHERE jahr >= ? ORDER BY jahr DESC").all(2020);
console.log("ab 2020, neueste zuerst:");
for (const buch of jung) console.log(` ${buch.jahr} ${buch.titel}`);
// LIMIT sagt wie viele, OFFSET sagt ab wo. Zusammen sind sie eine Seite.
const seite = db.prepare("SELECT titel FROM buecher ORDER BY titel LIMIT ? OFFSET ?").all(3, 3);
console.log("Seite 2 nach Titel sortiert:");
for (const buch of seite) console.log(` ${buch.titel}`); WHERE filtert, ORDER BY sortiert, LIMIT begrenzt, OFFSET überspringt. Mehr braucht eine
Übersicht mit mehreren Seiten nicht.
ORDER BY jahr DESC sortiert absteigend, ohne DESC aufsteigend. Für mehrere Spalten stehen sie
mit Komma hintereinander, etwa ORDER BY jahr DESC, titel.
LIMIT und OFFSET teilen das Ergebnis in Seiten. Seite eins ist LIMIT 3 OFFSET 0, Seite zwei
LIMIT 3 OFFSET 3, und so weiter. Ohne ORDER BY ist die Reihenfolge nicht zugesagt, und dann
kann derselbe Eintrag auf Seite eins und auf Seite zwei auftauchen.
Warum das die Datenbank macht und nicht JavaScript
Man könnte alles laden und in JavaScript filtern. Bei zehn Zeilen macht das keinen Unterschied, und deshalb fällt es lange nicht auf.
Bei hunderttausend Zeilen macht es einen sehr großen. Über JavaScript liest das Programm alle
hunderttausend Zeilen von der Platte und baut aus jeder ein Objekt im Arbeitsspeicher, nur um danach
99.990 davon wieder wegzuwerfen. Mit WHERE fragst du die Datenbank, und die liefert zehn.
Dazu blockiert das alles, denn node:sqlite arbeitet synchron. Solange der Prozess die
hunderttausend Objekte baut, nimmt er keine andere Anfrage an. Mit WHERE, ORDER BY und LIMIT
in der Abfrage bekommt JavaScript nur noch, was es wirklich zeigt.
all, get und run
export const AUFBAU = `CREATE TABLE buecher (
id INTEGER PRIMARY KEY,
titel TEXT NOT NULL,
jahr INTEGER
);
INSERT INTO buecher (titel, jahr) VALUES
('Node in der Praxis', 2020),
('JavaScript von vorn', 2019),
('HTTP verstehen', 2017),
('Datenbanken im Alltag', 2014),
('Die lange Nacht des Debuggens', 2022),
('Streams und Puffer', 2016),
('Nachtschicht mit Node', 2023),
('Sicher ausliefern', 2009),
('Testen ohne Angst', 2021),
('Fehler lesen lernen', 2019);`; import { DatabaseSync } from "node:sqlite";
import { AUFBAU } from "./bestand.js";
const db = new DatabaseSync(":memory:");
db.exec(AUFBAU);
// all: immer ein Array, auch bei null oder einem Treffer.
const alle = db.prepare("SELECT titel FROM buecher WHERE jahr = ?").all(2019);
console.log("all mit zwei Treffern: ", JSON.stringify(alle));
console.log("all ohne Treffer: ", JSON.stringify(db.prepare("SELECT titel FROM buecher WHERE jahr = ?").all(1900)));
// get: die erste Zeile oder undefined.
const eins = db.prepare("SELECT titel FROM buecher WHERE id = ?").get(3);
console.log("get mit Treffer: ", JSON.stringify(eins));
console.log("get ohne Treffer: ", JSON.stringify(db.prepare("SELECT titel FROM buecher WHERE id = ?").get(999)));
// run: keine Zeilen, nur die Bilanz.
const bilanz = db.prepare("UPDATE buecher SET jahr = jahr WHERE jahr = ?").run(2019);
console.log("run gibt die Bilanz: ", `changes ${bilanz.changes}`); Alle drei Methoden hängen an derselben vorbereiteten Anweisung. Welche passt, richtet sich danach, was zurückkommen soll.
all()gibt immer ein Array. Keine Treffer heißt leeres Array, wedernullnochundefined, und darüber lässt sich ohne Prüfung laufen.get()gibt die erste Zeile oderundefined. Für eine Abfrage über den Schlüssel ist das die richtige Wahl, und dasundefinedist der Fall, aus dem in der Bücher-API ein 404 wird.run()gibt keine Zeilen, nur die Bilanz mitchangesundlastInsertRowid. Es ist die Methode fürINSERT,UPDATEundDELETE.
get() auf eine Abfrage mit vielen Treffern ist kein Fehler, es liefert kommentarlos den
ersten. Fehlen Daten ohne erkennbaren Grund, steht oft ein get() da, wo ein all() hingehört.
Zählen, rechnen, gruppieren
export const AUFBAU = `CREATE TABLE buecher (
id INTEGER PRIMARY KEY,
titel TEXT NOT NULL,
jahr INTEGER
);
INSERT INTO buecher (titel, jahr) VALUES
('Node in der Praxis', 2020),
('JavaScript von vorn', 2019),
('HTTP verstehen', 2017),
('Datenbanken im Alltag', 2014),
('Die lange Nacht des Debuggens', 2022),
('Streams und Puffer', 2016),
('Nachtschicht mit Node', 2023),
('Sicher ausliefern', 2009),
('Testen ohne Angst', 2021),
('Fehler lesen lernen', 2019);`; import { DatabaseSync } from "node:sqlite";
import { AUFBAU } from "./bestand.js";
const db = new DatabaseSync(":memory:");
db.exec(AUFBAU);
// Vier Rechnungen in einer Abfrage, eine Zeile zurueck.
const zahlen = db.prepare("SELECT count(*) AS anzahl, min(jahr) AS aeltestes, max(jahr) AS neuestes, round(avg(jahr)) AS schnitt FROM buecher").get();
console.log(`${zahlen.anzahl} Bücher, ${zahlen.aeltestes} bis ${zahlen.neuestes}, im Schnitt ${zahlen.schnitt}`);
// GROUP BY macht aus einer Zeile je Buch eine Zeile je Jahrzehnt.
console.log("Bücher je Jahrzehnt:");
const gruppen = db.prepare(`SELECT (jahr / 10) * 10 AS jahrzehnt, count(*) AS anzahl
FROM buecher GROUP BY jahrzehnt ORDER BY jahrzehnt`).all();
for (const zeile of gruppen) console.log(` ${zeile.jahrzehnt}er: ${zeile.anzahl}`); count(*) zählt Zeilen, sum() summiert, avg() mittelt, min() und max() liefern den
kleinsten und den größten Wert. Diese Rechenfunktionen geben eine einzige Zeile zurück, auch über
eine Million Zeilen. Deshalb holt man sie mit get().
avg() liefert eine Kommazahl, bei zwei Büchern von 2019 und 2020 wäre es 2019.5. Im Beispiel wird
deshalb gleich in SQL mit round() gerundet, also dort, wo auch gerechnet wurde. Bei diesen zehn
Büchern käme auch ohne Rundung 2018 heraus.
Ohne AS heißt die Spalte in der Rückgabe wörtlich count(*). In der Zeile steht dann dieser
Schlüssel, und zahlen.anzahl ist undefined. Deshalb hat in den Beispielen jede gerechnete Spalte
ein AS mit einem Namen.
GROUP BY fasst Zeilen zusammen, und die Rechenfunktionen rechnen dann je Gruppe statt über alles.
Im Beispiel wird aus zehn Büchern eine Zeile je Jahrzehnt.
In der Aufgabe fragst du nach Anzahl, neuesten Büchern und Durchschnittsjahr des Bestands, jeweils mit einer eigenen Abfrage.
Zum Mitnehmen
WHERE, ORDER BY und LIMIT gehören in die Abfrage, sonst gibt die Datenbank jede Zeile heraus. all liefert immer ein Array, get die erste Zeile oder undefined, und eine gerechnete Spalte bekommt mit AS einen Namen.
Jetzt du
Basis Konto, kostenlosZu dieser Lektion gehört eine Aufgabe. Du schreibst den Code selbst, und nach jedem Lauf sagt dir eine Prüfliste, was schon stimmt.
Dafür brauchst du das Basis Konto. Es kostet nichts, und ein Passwort gibt es auch nicht.
In diesem Kurs läuft dein Code auf einem Server. Dafür hat das Basis Konto 1 Stunde im Monat, mehr Zeit gibt es mit dem Premium Konto.
Was in dieser Lektion steckt
-
Artikel mit 3 Beispielen zum Ausprobieren
Steht hier, ohne Konto lesbar.
-
Aufgabe, dein Code läuft auf einem Server
Öffnet sich mit dem Basis Konto.