La base de datos: de CREATE a DROP

En resumen: CREATE DATABASE crea una base de datos nueva en un servidor, mientras que DELETE, TRUNCATE, DROP TABLE y DROP DATABASE eliminan filas, todas las filas, una tabla o la base de datos entera.

Problema

El almacén quiere su propia base de datos

La tienda abre un almacén. El almacén tiene sus propias tablas: estantes, entregas, recuentos de existencias. No forman parte del sitio web, y nadie quiere que un error del almacén detenga los pedidos. Así que el almacén recibe su propia base de datos, separada, en el mismo servidor.

Hasta ahora solo has trabajado en la base de datos de la tienda. Ahora verás toda la vida de una base de datos: cómo se crea, cómo te conectas a ella, cómo se llena y cómo se elimina.

La idea

Un servidor, muchas bases de datos

Un servidor de bases de datos (PostgreSQL, MySQL, SQL Server) contiene muchas bases de datos. Cada una tiene sus propias tablas, sus propios usuarios y permisos. Una base de datos nueva se crea con CREATE DATABASE:

-- PostgreSQL
CREATE DATABASE warehouse;
\c warehouse          -- psql se conecta a la base nueva

-- MySQL
CREATE DATABASE warehouse;
USE warehouse;        -- las consultas siguientes se ejecutan en ella

Estos comandos no funcionan en los campos de aquí, porque el sitio usa SQLite. SQLite no tiene servidor: una base de datos es un archivo. Una base nueva es un archivo nuevo: sqlite3 warehouse.db en la terminal lo crea si no existe. Por eso SQLite no tiene CREATE DATABASE.

¿Qué base de datos usa ahora el campo? main es la base de datos de la tienda:

SQLbase de datos: tienda

SELECT name FROM pragma_database_list;

SELECT name FROM pragma_database_list;

▶ EjecutarRestaurar la consultaBase nueva

La idea

Una base nueva en SQLite: ATTACH

Dentro de una conexión que ya está abierta, SQLite adjunta otra base de datos con ATTACH. En una computadora escribes un nombre de archivo (ATTACH 'warehouse.db' AS warehouse) y el archivo se crea si no existe. En el navegador usamos ':memory:': una base nueva y vacía en memoria que desaparece con el campo. El nombre después de AS es el nombre de la base de datos, y sus tablas se escriben con él delante:

SQLbase de datos: tienda

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;

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;

▶ EjecutarRestaurar la consultaBase nueva

La tabla shelves está en la base warehouse, no en la tienda. Cómo se escriben exactamente las columnas en CREATE TABLE está en la lección siguiente. Aquí importa a dónde van.

Míralo

Una consulta entre dos bases de datos

En SQLite y MySQL una consulta puede leer de dos bases de datos a la vez. Los estantes están en el almacén, y los nombres y las existencias en la tienda:

SQLbase de datos: tienda

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;

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;

▶ EjecutarRestaurar la consultaBase nueva

main.products es la tabla de la tienda. main. se puede omitir: para una tabla sin nombre de base de datos SQLite busca en la base temporal, luego en main y solo después en las adjuntas. En PostgreSQL esto no funciona: una consulta ve solo la base de datos a la que está conectada. Allí las partes separadas de un sistema se dividen en esquemas dentro de una base (warehouse.shelves es el esquema warehouse), y las bases de datos separadas están realmente separadas.

Pruébalo

La base de datos del almacén

Crea la base de datos warehouse (en memoria), en ella una tabla deliveries con las columnas id (clave primaria), product_id y quantity (números enteros, obligatorias) y registra una entrega: 40 unidades del producto 12.

SQLbase de datos: tienda

ATTACH ':memory:' AS warehouse;

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

ATTACH ':memory:' AS warehouse; CREATE TABLE warehouse.deliveries ( id INTEGER PRIMARY KEY );

Comprobar▶ Ejecutar

La idea

Cuatro formas de quitar

De lo más pequeño a lo más grande:

• DELETE FROM t WHERE … quita las filas elegidas. Sin WHERE quita todas, pero la tabla se queda.
• TRUNCATE TABLE t (PostgreSQL, MySQL, SQL Server) quita todas las filas de una vez, mucho más rápido que DELETE en una tabla grande. En MySQL y SQL Server también reinicia la numeración de id, y en PostgreSQL solo con TRUNCATE TABLE t RESTART IDENTITY. SQLite no tiene TRUNCATE: allí DELETE FROM t sin WHERE hace lo mismo de forma rápida.
• DROP TABLE t quita la tabla misma: las filas, las columnas, sus índices. Después t ya no existe.
• DROP DATABASE warehouse quita la base de datos entera con todas sus tablas. En SQLite esto es borrar el archivo.

Aquí DELETE vacía la tabla de promociones, y DROP TABLE la quita. La última consulta no tiene nada que leer:

SQLbase de datos: tienda

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;

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;

▶ EjecutarRestaurar la consultaBase nueva

Más precisamente, para curiosos

DROP no pregunta «¿seguro?» y no se deshace con Ctrl+Z. En PostgreSQL DROP DATABASE no funciona mientras alguien esté conectado a la base, y en PostgreSQL y SQLite un DROP TABLE dentro de una transacción se puede deshacer con ROLLBACK. En MySQL DROP y TRUNCATE hacen un COMMIT por su cuenta. Por eso, antes de un DROP en una base de datos real, primero haces una copia (para eso está la lección sobre el sitio real en el módulo «La base de datos en el programa»).

Pruébalo

Una tabla de una importación antigua

La tabla promo_import quedó de una importación de promociones y ya no hace falta. Elimínala junto con los datos, de modo que el script no se detenga si alguien ya la quitó.

SQLbase de datos: tienda

SELECT name FROM sqlite_master WHERE type = 'table';

SELECT name FROM sqlite_master WHERE type = 'table';

Comprobar▶ Ejecutar

Ejercicio

Vaciar, pero conservar

La tabla cart_log registra lo que los clientes ponen en el carrito. Al principio de cada mes se vacía, pero se queda para los registros nuevos. Vacíala.

SQLbase de datos: tienda

SELECT * FROM cart_log;

SELECT * FROM cart_log;

Comprobar▶ Ejecutar

La idea

Desadjuntar

Cuando ya no la necesitas, la base de datos se desadjunta con DETACH. El archivo se queda en el disco, y sus tablas ya no se ven desde esta conexión:

SQLbase de datos: tienda

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

SELECT name FROM pragma_database_list;

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

▶ EjecutarRestaurar la consultaBase nueva

El camino completo de una base de datos es: crearla (CREATE DATABASE, en SQLite un archivo nuevo o ATTACH), conectarse (\c, USE, ATTACH), tablas (CREATE TABLE, la lección siguiente), datos (INSERT, UPDATE, DELETE), consultas (SELECT) y por último eliminar (DROP TABLE, DROP DATABASE). El proyecto del final del curso recorre todo el camino con la tienda.

Test

Ponte a prueba

1. En PostgreSQL estás conectado a la base de datos shop. ¿Cómo llegas a las tablas de la base warehouse?

Abres una conexión nueva a warehouse (en psql con \c warehouse)USE warehouse;SELECT * FROM warehouse.shelves

2. ¿Cuál quita las filas de una tabla pero la deja vacía y lista para filas nuevas?

DELETE sin WHERE (o TRUNCATE en PostgreSQL y MySQL)DROP DATABASEDROP TABLE

3. Un script de actualización ejecuta DROP TABLE promo_import, pero la tabla no existe. ¿Qué pasa?

NadaEl script se detiene con un error; DROP TABLE IF EXISTS no se detendríaLa tabla se queda vacía

4. ¿Qué es una base de datos en SQLite?

Una carpeta con un archivo por tablaUn archivo (o una base de datos en memoria)Un proceso en un servidor

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: Clave primaria y clave foránea · JOIN: dos tablas en una consulta · INSERT: insertar filas · DELETE: borrar filas
• Continúa en: La panadería, etapa 1: la base de datos y las tablas · La panadería, etapa 6: el informe, la copia y DROP
• Ejercicios sobre el tema: Tablas y reglas