Daten einfügen, ändern und löschen

CREATE TABLE nutzer (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER DEFAULT 0);

INSERT INTO nutzer (name, ort) VALUES ('Mia', 'Berlin');
INSERT INTO nutzer (name, ort, punkte) VALUES ('Tom', 'Hamburg', 50), ('Zoe', NULL, 80);
INSERT INTO nutzer (id, name) VALUES (10, 'Ben');

SELECT * FROM nutzer;

CREATE TABLE a (n INTEGER);
CREATE TABLE b (n INTEGER);
INSERT INTO a VALUES (1), (2), (3), (4);
INSERT INTO b SELECT n * 10 FROM a WHERE n > 2;
SELECT * FROM b;

CREATE TABLE zaehler (wort TEXT PRIMARY KEY, anzahl INTEGER);
INSERT INTO zaehler VALUES ('a', 1);
INSERT INTO zaehler VALUES ('a', 1)
    ON CONFLICT(wort) DO UPDATE SET anzahl = anzahl + 1;
INSERT OR IGNORE INTO zaehler VALUES ('a', 99);
INSERT INTO zaehler VALUES ('b', 1)
    ON CONFLICT(wort) DO UPDATE SET anzahl = anzahl + 1;
SELECT * FROM zaehler ORDER BY wort;

CREATE TABLE konto (id INTEGER PRIMARY KEY, inhaber TEXT, stand REAL);
INSERT INTO konto VALUES (1,'Mia',100),(2,'Tom',50),(3,'Zoe',0);

UPDATE konto SET stand = stand + 25 WHERE id = 1;
UPDATE konto SET inhaber = 'Thomas', stand = 60 WHERE inhaber = 'Tom';
UPDATE konto SET stand = stand * 1.1 WHERE stand > 50;
SELECT * FROM konto;

CREATE TABLE logs (id INTEGER PRIMARY KEY, stufe TEXT, text TEXT);
INSERT INTO logs (stufe, text) VALUES ('info','a'),('fehler','b'),('info','c'),('debug','d');

DELETE FROM logs WHERE stufe = 'debug';
SELECT changes() AS geloescht;
DELETE FROM logs WHERE stufe IN ('info');
SELECT * FROM logs;

CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO t (name) VALUES ('x'), ('y') RETURNING id, name;
UPDATE t SET name = upper(name) WHERE id = 2 RETURNING *;