CREATE DATABASE, DROP und TRUNCATE in SQL

Problem

Das Lager will eine eigene Datenbank

Der Shop eröffnet ein Lager. Das Lager hat eigene Tabellen: Regale, Lieferungen, Bestandszählungen. Sie gehören nicht zur Website, und niemand will, dass ein Fehler im Lager die Bestellungen stoppt. Also bekommt das Lager eine eigene, getrennte Datenbank auf demselben Server.

Bisher hast du nur in der Datenbank des Shops gearbeitet. Jetzt siehst du das ganze Leben einer Datenbank: wie sie entsteht, wie du dich mit ihr verbindest, wie sie gefüllt und wie sie entfernt wird.

Die Idee

Ein Server, viele Datenbanken

Ein Datenbankserver (PostgreSQL, MySQL, SQL Server) enthält viele Datenbanken. Jede hat ihre eigenen Tabellen, ihre eigenen Benutzer und Berechtigungen. Eine neue Datenbank legt man mit CREATE DATABASE an:

-- PostgreSQL
CREATE DATABASE warehouse;
\c warehouse          -- psql verbindet sich mit der neuen Datenbank

-- MySQL
CREATE DATABASE warehouse;
USE warehouse;        -- die nächsten Abfragen laufen darin

Diese Befehle funktionieren in den Feldern hier nicht, weil die Seite SQLite benutzt. SQLite hat keinen Server: Eine Datenbank ist eine Datei. Eine neue Datenbank ist eine neue Datei: sqlite3 warehouse.db im Terminal legt sie an, wenn es sie nicht gibt. Deshalb hat SQLite kein CREATE DATABASE.

Welche Datenbank benutzt das Feld gerade? main ist die Datenbank des Shops:

SQLDatenbank: Shop

SELECT name FROM pragma_database_list;

Die Idee

Eine neue Datenbank in SQLite: ATTACH

Innerhalb einer schon offenen Verbindung hängt SQLite mit ATTACH eine weitere Datenbank an. Auf einem Computer schreibst du einen Dateinamen (ATTACH 'warehouse.db' AS warehouse), und die Datei wird angelegt, wenn es sie nicht gibt. Im Browser nehmen wir ':memory:': eine neue leere Datenbank im Arbeitsspeicher, die mit dem Feld verschwindet. Der Name nach AS ist der Name der Datenbank, und ihre Tabellen schreibst du mit ihm davor:

SQLDatenbank: Shop

ATTACH ':memory:' AS warehouse;

CREATE TABLE warehouse.shelves (
  id INTEGER PRIMARY KEY,
  code TEXT NOT NULL,
  product_id INTEGER NOT NULL
);
INSERT INTO warehouse.shelves (code, product_id) VALUES ('A1', 7), ('A2', 9);

SELECT name FROM pragma_database_list;

Die Tabelle shelves liegt in der Datenbank warehouse, nicht im Shop. Wie genau die Spalten in CREATE TABLE geschrieben werden, steht in der nächsten Lektion. Hier zählt, wohin sie gehören.

Sieh es dir an

Eine Abfrage über zwei Datenbanken

In SQLite und MySQL kann eine Abfrage aus zwei Datenbanken gleichzeitig lesen. Die Regale sind im Lager, die Namen und der Bestand im Shop:

SQLDatenbank: Shop

ATTACH ':memory:' AS warehouse;
CREATE TABLE warehouse.shelves (id INTEGER PRIMARY KEY, code TEXT NOT NULL, product_id INTEGER NOT NULL);
INSERT INTO warehouse.shelves (code, product_id) VALUES ('A1', 7), ('A2', 9);

SELECT s.code, p.name, p.stock
FROM warehouse.shelves s
JOIN main.products p ON p.id = s.product_id;

main.products ist die Tabelle des Shops. main. kann man weglassen: Bei einer Tabelle ohne Datenbanknamen sucht SQLite in der temporären Datenbank, dann in main und erst dann in den angehängten. In PostgreSQL geht das nicht: Eine Abfrage sieht nur die Datenbank, mit der sie verbunden ist. Dort werden getrennte Teile eines Systems in einer Datenbank in Schemas aufgeteilt (warehouse.shelves ist das Schema warehouse), und getrennte Datenbanken sind wirklich getrennt.

Probier es aus

Die Datenbank des Lagers

Lege die Datenbank warehouse an (im Arbeitsspeicher), darin eine Tabelle deliveries mit den Spalten id (Primärschlüssel), product_id und quantity (ganze Zahlen, Pflicht) und trage eine Lieferung ein: 40 Stück von Produkt 12.

SQLDatenbank: Shop

ATTACH ':memory:' AS warehouse;

CREATE TABLE warehouse.deliveries (
  id INTEGER PRIMARY KEY
);

Lösung

ATTACH ':memory:' AS warehouse;
CREATE TABLE warehouse.deliveries (
  id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL
);
INSERT INTO warehouse.deliveries (product_id, quantity) VALUES (12, 40);

Die Idee

Vier Arten zu entfernen

Vom Kleinsten zum Größten:

DELETE FROM t WHERE … entfernt die gewählten Zeilen. Ohne WHERE entfernt es alle, aber die Tabelle bleibt.
TRUNCATE TABLE t (PostgreSQL, MySQL, SQL Server) entfernt alle Zeilen auf einmal, bei einer großen Tabelle viel schneller als DELETE. In MySQL und SQL Server setzt es auch die Nummerierung von id zurück, in PostgreSQL nur mit TRUNCATE TABLE t RESTART IDENTITY. SQLite hat kein TRUNCATE: Dort macht DELETE FROM t ohne WHERE dasselbe auf eine schnelle Art.
DROP TABLE t entfernt die Tabelle selbst: die Zeilen, die Spalten, ihre Indizes. Danach ist t weg.
DROP DATABASE warehouse entfernt die ganze Datenbank mit allen Tabellen. In SQLite heißt das, die Datei zu löschen.

Hier leert DELETE die Tabelle der Aktionen, und DROP TABLE entfernt sie. Die letzte Abfrage hat nichts mehr zu lesen:

SQLDatenbank: Shop

CREATE TABLE promo_import (code TEXT, discount INTEGER);
INSERT INTO promo_import VALUES ('AUTUMN10', 10), ('BREAD5', 5);

DELETE FROM promo_import;
SELECT COUNT(*) AS rows_left FROM promo_import;

DROP TABLE promo_import;
SELECT COUNT(*) FROM promo_import;

Genauer, für Neugierige

DROP fragt nicht „bist du sicher?“ und kommt auch mit Strg+Z nicht zurück. In PostgreSQL funktioniert DROP DATABASE nicht, solange jemand mit der Datenbank verbunden ist, und in PostgreSQL und SQLite lässt sich ein DROP TABLE in einer Transaktion mit ROLLBACK zurücknehmen. In MySQL machen DROP und TRUNCATE von selbst ein COMMIT. Deshalb machst du vor einem DROP auf einer echten Datenbank zuerst eine Kopie (dafür ist die Lektion über die echte Website im Modul „Die Datenbank im Programm“ da).

Probier es aus

Eine Tabelle aus einem alten Import

Die Tabelle promo_import ist von einem Import von Aktionen übrig geblieben und wird nicht mehr gebraucht. Entferne sie samt den Daten, sodass das Skript nicht abbricht, wenn jemand sie schon entfernt hat.

SQLDatenbank: Shop

SELECT name FROM sqlite_master WHERE type = 'table';

Lösung

DROP TABLE IF EXISTS promo_import;

Übung

Leeren, aber behalten

Die Tabelle cart_log hält fest, was Kunden in den Warenkorb legen. Am Anfang jedes Monats wird sie geleert, bleibt aber für die neuen Einträge bestehen. Leere sie.

SQLDatenbank: Shop

SELECT * FROM cart_log;

Lösung

DELETE FROM cart_log;

Die Idee

Abhängen

Wenn du sie nicht mehr brauchst, wird die Datenbank mit DETACH abgehängt. Die Datei bleibt auf der Platte, und ihre Tabellen sind von dieser Verbindung aus nicht mehr sichtbar:

SQLDatenbank: Shop

ATTACH ':memory:' AS warehouse;
CREATE TABLE warehouse.shelves (id INTEGER PRIMARY KEY, code TEXT);
DETACH warehouse;

SELECT name FROM pragma_database_list;

Der ganze Weg einer Datenbank ist: anlegen (CREATE DATABASE, in SQLite eine neue Datei oder ATTACH), verbinden (\c, USE, ATTACH), Tabellen (CREATE TABLE, die nächste Lektion), Daten (INSERT, UPDATE, DELETE), Abfragen (SELECT) und zuletzt entfernen (DROP TABLE, DROP DATABASE). Das Projekt am Ende des Kurses geht den ganzen Weg mit dem Shop.

Test

Teste dich

1. In PostgreSQL bist du mit der Datenbank shop verbunden. Wie kommst du an die Tabellen der Datenbank warehouse?USE warehouse;Du öffnest eine neue Verbindung zu warehouse (in psql mit \c warehouse)SELECT * FROM warehouse.shelves

2. Welches entfernt die Zeilen einer Tabelle, lässt sie aber leer und bereit für neue stehen?DELETE ohne WHERE (oder TRUNCATE in PostgreSQL und MySQL)DROP DATABASEDROP TABLE

3. Ein Update-Skript führt DROP TABLE promo_import aus, aber die Tabelle gibt es nicht. Was passiert?NichtsDas Skript bricht mit einem Fehler ab; DROP TABLE IF EXISTS würde nicht abbrechenDie Tabelle wird geleert

4. Was ist eine Datenbank in SQLite?Ein Ordner mit einer Datei pro TabelleEine Datei (oder eine Datenbank im Arbeitsspeicher)Ein Prozess auf einem Server

Die Datenbank: von CREATE bis DROP

Cookies