{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Crear tablas y escribir en ellas\n", "\n", "CREATE, INSERT, UPDATE, DELETE y los cuatro autoincrementos, con las restricciones que la base sí cumple.\n", "\n", "Cuaderno de soluciones del capítulo 11 de **SQL desde cero**, de Miss Yera.\n", "\n", "Corre de arriba abajo. Si lo abres en Google Colab no necesitas instalar nada.\n", "\n", "Capítulo completo: https://missyera.com/guias/sql-desde-cero/crear-y-modificar/\n", "\n", "Este es el cuaderno de **soluciones**. Trae el código de cada ejercicio, la\n", "explicación de la trampa y la respuesta del quiz. Si vienes del cuaderno de\n", "práctica sin haberlo intentado, vuelve 🙂" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Antes de empezar\n", "\n", "Se baja la base y se deja lista una función `q()` que corre las consultas." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import sqlite3\n", "import urllib.request\n", "\n", "import pandas as pd\n", "\n", "urllib.request.urlretrieve(\"https://missyera.com/static/datasets/tienda.db\", \"tienda.db\")\n", "con = sqlite3.connect(\"tienda.db\")\n", "\n", "def q(sql):\n", " \"\"\"Corre las sentencias del bloque y devuelve la ultima como tabla.\n", "\n", " Parte por sentencias igual que la consola de SQLite, porque un bloque puede\n", " traer varias y un CREATE TRIGGER lleva punto y coma dentro de su cuerpo.\n", " \"\"\"\n", " resultado = None\n", " trozo = \"\"\n", " for linea in sql.splitlines(keepends=True):\n", " trozo += linea\n", " if sqlite3.complete_statement(trozo):\n", " if trozo.strip():\n", " cur = con.execute(trozo.strip())\n", " resultado = (pd.DataFrame(cur.fetchall(),\n", " columns=[d[0] for d in cur.description])\n", " if cur.description else None)\n", " trozo = \"\"\n", " if trozo.strip():\n", " cur = con.execute(trozo.strip())\n", " resultado = (pd.DataFrame(cur.fetchall(),\n", " columns=[d[0] for d in cur.description])\n", " if cur.description else None)\n", " con.commit()\n", " return resultado\n", "\n", "q(\"SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Nueve capítulos leyendo. Hoy escribimos 🛠️\n", "\n", "Y sí, da un poquito de miedo, porque hasta ahora lo peor que podía pasar era\n", "un número mal y ahora lo peor que puede pasar es borrar algo. Por eso este\n", "capítulo tiene más avisos que los otros, y por eso el primero es este:\n", "**todo lo que sigue corre sobre una copia de `tienda.db`, no\n", "sobre la original.** Antes de escribir en una base que le importa a\n", "alguien, haz una copia. Siempre. Es un `Ctrl+C` y te salva el\n", "trabajo.\n", "\n", "Vamos a crear una tabla de promociones, llenarla, cambiarla y borrarla, y en\n", "el camino van a saltar siete errores de verdad.\n", "\n", "Y contéstate una antes de tocar nada: **¿qué pasaría en tu trabajo si borraras una tabla sin querer?** Si la respuesta te da un poco de frío, ya entendiste por qué este capítulo empieza pidiéndote una copia 🛟" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Crear una tabla" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "CREATE TABLE promociones (\n", " id INTEGER PRIMARY KEY,\n", " nombre TEXT NOT NULL,\n", " descuento REAL NOT NULL DEFAULT 0,\n", " canal TEXT,\n", " activa INTEGER NOT NULL DEFAULT 1,\n", " creada TEXT NOT NULL DEFAULT (DATE('now'))\n", ");\n", "PRAGMA table_info(promociones);\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Cada línea de esas es una decisión, y todas se toman una sola vez pero se\n", "pagan durante años. Vamos por partes.\n", "\n", "- 🔑 `PRIMARY KEY`: la columna que identifica la fila. No se\n", "repite, no puede ser nula, y en SQLite además se llena sola.\n", "\n", "- 🚫 `NOT NULL`: aquí no se admite \"no se sabe\". Después del\n", "capítulo 4 sabes bien por qué esto vale oro.\n", "\n", "- 🎁 `DEFAULT`: qué poner si no dices nada. Un\n", "`DEFAULT 0` en un descuento es mucho mejor que un nulo, porque un\n", "cero se puede sumar.\n", "\n", "- 📛 El orden de las columnas: primero la clave, después lo obligatorio,\n", "después lo opcional. Nadie te obliga y se lee muchísimo mejor.\n", "\n", "Fíjate en `canal`, que es la única sin `NOT NULL`: es\n", "a propósito, porque una promo puede valer para todos los canales y ahí el nulo\n", "significa algo. **Un nulo con significado está bien; un nulo porque nadie\n", "lo pensó, no.**" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "La columna que se numera sola\n", "\n", "| PostgreSQL | `id INT GENERATED ALWAYS AS IDENTITY -- antes se usaba SERIAL` |\n", "|---|---|\n", "| MySQL | `id INT AUTO_INCREMENT PRIMARY KEY` |\n", "| SQL Server | `id INT IDENTITY(1,1) PRIMARY KEY` |\n", "| SQLite | `id INTEGER PRIMARY KEY -- ya se numera sola, sin decir nada` |\n", "\n", "Cuatro palabras distintas para lo mismo, y esta es de las que más se busca. En SQLite, INTEGER PRIMARY KEY (con INTEGER completo, no INT) ya se autoincrementa; la palabra AUTOINCREMENT existe y hace otra cosa que vemos al final del capítulo." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Los tipos, al crear la tabla\n", "\n", "| PostgreSQL | `TEXT o VARCHAR(n), NUMERIC(10,2), BOOLEAN, DATE` |\n", "|---|---|\n", "| MySQL | `VARCHAR(n) con n obligatorio, DECIMAL(10,2), BOOLEAN que en realidad es TINYINT(1), DATE` |\n", "| SQL Server | `NVARCHAR(n) o NVARCHAR(MAX), DECIMAL(10,2), BIT porque no hay BOOLEAN, DATE` |\n", "| SQLite | `TEXT sin longitud, REAL o NUMERIC, INTEGER 0 y 1 porque no hay BOOLEAN, y tampoco hay DATE` |\n", "\n", "Lo de BOOLEAN es de las cosas que más despistan: solo PostgreSQL tiene uno de verdad. En los otros tres es un número disfrazado, y por eso en tienda.db la columna es_principal es un 0 o un 1 y no un true." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Meter filas" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "INSERT INTO promociones (nombre, descuento, canal)\n", "VALUES ('Bienvenida bodega', 0.10, 'WhatsApp');\n", "SELECT id, nombre, descuento, canal, activa FROM promociones;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "No puse el `id` y salió 1: eso es la clave autoincremental\n", "trabajando. Tampoco puse `activa` y salió 1, que es el\n", "`DEFAULT`.\n", "\n", "Se pueden meter varias de una vez, y así es muchísimo más rápido que una por\n", "una." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "INSERT INTO promociones (nombre, descuento, canal) VALUES\n", " ('Fin de mes', 0.15, 'Web'),\n", " ('Mayorista 2026', 0.20, NULL),\n", " ('Recuperacion', 0.25, 'Tienda');\n", "SELECT id, nombre, descuento, canal FROM promociones ORDER BY id;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Ese `NULL` escrito a mano en Mayorista 2026 es el nulo con\n", "significado del que hablábamos: la promo vale para cualquier canal." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "INSERT INTO promociones (nombre)\n", "VALUES ('Sin nada mas');\n", "SELECT id, nombre, descuento, canal, activa FROM promociones WHERE nombre = 'Sin nada mas';\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Con el nombre solo alcanza, porque todo lo demás tiene `DEFAULT` o\n", "admite nulos. **Siempre escribe la lista de columnas** entre\n", "paréntesis, aunque el motor te deje omitirla: el día que alguien añada una\n", "columna en el medio, un `INSERT` sin lista mete los datos corridos y\n", "no da error.\n", "\n", "Ahora rompamos algo a propósito." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "try:\n", " q(\"\"\"\n", " INSERT INTO promociones (nombre, descuento) VALUES (NULL, 0.5);\n", " \"\"\")\n", "except Exception as e:\n", " print(f'{type(e).__name__}: {e}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y la queja que tiene que salir es esta:\n", "\n", "```\n", "IntegrityError: NOT NULL constraint failed: promociones.nombre\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*NOT NULL constraint failed*. Y esa es exactamente la idea 🙌 Una\n", "restricción es una promesa que la base te obliga a cumplir, y prefieres mil veces\n", "un error al meter el dato que descubrir tres meses después que tienes\n", "cuatrocientas filas sin nombre." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Cambiar filas, y el WHERE que hay que escribir primero" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "UPDATE promociones SET descuento = 0.30 WHERE nombre = 'Recuperacion';\n", "SELECT id, nombre, descuento FROM promociones ORDER BY id;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Una fila cambiada. Ahora mira esto, y mira bien porque es el error que más\n", "caro sale en toda la vida de alguien que trabaja con datos." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "UPDATE promociones SET activa = 0;\n", "SELECT COUNT(*) AS apagadas FROM promociones WHERE activa = 0;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Cinco. Todas. Se me olvidó el `WHERE` y apagué la tabla entera, sin\n", "error, sin confirmación, sin \"¿estás segura?\" 🫠\n", "\n", "Con cinco filas se arregla; con la tabla de precios de una empresa un viernes\n", "a las siete, no. La costumbre que te recomiendo, y que yo uso siempre:\n", "\n", "- 1️⃣ Escribe primero el `SELECT` con ese mismo\n", "`WHERE` y mira qué filas salen.\n", "\n", "- 2️⃣ Si son las que querías, cambia el `SELECT` por el\n", "`UPDATE`.\n", "\n", "- 3️⃣ Y si la base tiene transacciones, envuélvelo en una. Eso es el capítulo\n", "12.\n", "\n", "`DELETE` tiene exactamente el mismo peligro y exactamente el mismo\n", "remedio." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "DELETE FROM promociones WHERE nombre = 'Sin nada mas';\n", "SELECT COUNT(*) AS quedan FROM promociones;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## El peligro que no es tuyo: la inyección SQL" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Todo lo de arriba lo escribes tú, a mano, y sabes qué hace. El problema\n", "empieza cuando la consulta se arma con algo que escribió otra persona: lo que\n", "alguien tecleó en un buscador, en un formulario, en una app.\n", "\n", "Imagina que tu programa arma la consulta pegando texto:" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "-- MAL. Nunca. Ni una vez.\n", "consulta = \"SELECT * FROM clientes WHERE nombre = '\" + lo_que_escribio + \"'\"\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Si alguien escribe `x' OR '1'='1`, la consulta que llega a la base\n", "es `WHERE nombre = 'x' OR '1'='1'`, que es verdadera para todas las\n", "filas y devuelve la tabla entera. Con un poco más de imaginación se borran\n", "tablas. Eso es una **inyección SQL**, y lleva veinticinco años\n", "siendo de las formas más comunes de reventar un sistema 💀\n", "\n", "La solución es una sola y es igual en los cuatro motores: **consultas\n", "parametrizadas**. Se escribe un hueco en el SQL y el valor viaja aparte,\n", "así que el motor nunca lo interpreta como código." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "# Python con SQLite, y el hueco es la interrogación\n", "cursor.execute(\"SELECT * FROM clientes WHERE nombre = ?\", (lo_que_escribio,))\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "El símbolo del hueco cambia: `?` en SQLite y en MySQL,\n", "`$1` o `%s` en PostgreSQL según la librería, y\n", "`@nombre` en SQL Server. Lo que no cambia es la regla: **los\n", "valores nunca se pegan al texto de la consulta**.\n", "\n", "Y si escribes SQL solo para analizar, esto igual te toca: el día que armes\n", "una consulta con un nombre de columna que viene de una lista desplegable, ya\n", "estás en este terreno 🕵️‍♀️" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Las otras tres promesas: UNIQUE, CHECK y las claves foráneas" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "try:\n", " q(\"\"\"\n", " CREATE TABLE canales (\n", " codigo TEXT PRIMARY KEY,\n", " nombre TEXT NOT NULL UNIQUE\n", " );\n", " INSERT INTO canales VALUES ('WEB', 'Web'), ('WSP', 'WhatsApp');\n", " INSERT INTO canales VALUES ('WEB2', 'Web');\n", " \"\"\")\n", "except Exception as e:\n", " print(f'{type(e).__name__}: {e}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y la queja que tiene que salir es esta:\n", "\n", "```\n", "IntegrityError: UNIQUE constraint failed: canales.nombre\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`UNIQUE` es \"este valor no se repite\". Es lo que impide tener el\n", "mismo canal dos veces con códigos distintos, que es como nacen los reportes\n", "donde Web aparece partido en dos filas." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "try:\n", " q(\"\"\"\n", " CREATE TABLE cupones (\n", " id INTEGER PRIMARY KEY,\n", " codigo TEXT NOT NULL,\n", " descuento REAL NOT NULL CHECK (descuento > 0 AND descuento <= 0.5)\n", " );\n", " INSERT INTO cupones (codigo, descuento) VALUES ('POLLITO10', 0.10);\n", " INSERT INTO cupones (codigo, descuento) VALUES ('TODOGRATIS', 0.99);\n", " \"\"\")\n", "except Exception as e:\n", " print(f'{type(e).__name__}: {e}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y la queja que tiene que salir es esta:\n", "\n", "```\n", "IntegrityError: CHECK constraint failed: descuento > 0 AND descuento <= 0.5\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`CHECK` es una regla de negocio metida en la base: aquí, que ningún\n", "cupón pase del 50%. Se escribe igual en los cuatro motores y se usa poquísimo,\n", "y es una pena, porque una regla en la base la cumple todo el mundo y una regla\n", "en el código la cumple solo el programa que se acordó.\n", "\n", "Y ahora la más importante de las tres, que es la que conecta tablas." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "CREATE TABLE visitas (\n", " id INTEGER PRIMARY KEY,\n", " id_cliente INTEGER NOT NULL REFERENCES clientes(id),\n", " fecha TEXT NOT NULL\n", ");\n", "INSERT INTO visitas (id_cliente, fecha) VALUES (999999, '2026-06-24');\n", "SELECT id, id_cliente, fecha FROM visitas;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y entró 😳\n", "\n", "El cliente 999999 no existe. Le puse a la columna un\n", "`REFERENCES clientes(id)`, que es justamente la promesa de que ahí\n", "solo van clientes que existen, y SQLite la aceptó igual." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "PRAGMA foreign_keys;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Cero. **SQLite trae las claves foráneas APAGADAS por defecto**,\n", "y hay que encenderlas en cada conexión. Es la peor sorpresa del motor y viene de\n", "que cuando se añadieron, encenderlas de fábrica habría roto las bases que ya\n", "existían." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "try:\n", " q(\"\"\"\n", " PRAGMA foreign_keys = ON;\n", " INSERT INTO visitas (id_cliente, fecha) VALUES (888888, '2026-06-25');\n", " \"\"\")\n", "except Exception as e:\n", " print(f'{type(e).__name__}: {e}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y la queja que tiene que salir es esta:\n", "\n", "```\n", "IntegrityError: FOREIGN KEY constraint failed\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Ahora sí. Y acuérdate de esto cuando te preguntes de dónde salen los 24\n", "pedidos sin cliente que llevamos arrastrando desde el capítulo 4: de una base\n", "donde la promesa estaba escrita y no estaba encendida 🕳️" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Las claves foráneas, ¿se cumplen?\n", "\n", "| PostgreSQL | `siempre, no se pueden apagar` |\n", "|---|---|\n", "| MySQL | `sí con InnoDB; con el viejo MyISAM se aceptan y se IGNORAN en silencio` |\n", "| SQL Server | `siempre` |\n", "| SQLite | `apagadas de fábrica: PRAGMA foreign_keys = ON en cada conexión` |\n", "\n", "Los dos de los extremos son los peligrosos y por motivos parecidos: la restricción está escrita, se ve en el CREATE TABLE, y no hace nada. Si heredas una base y ves huérfanos, esto es lo primero que hay que mirar." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Cambiar una tabla que ya existe" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "ALTER TABLE promociones ADD COLUMN observacion TEXT;\n", "SELECT id, nombre, observacion FROM promociones ORDER BY id LIMIT 2;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Añadir una columna es lo único que se hace igual y sin drama en los cuatro\n", "motores. Las filas que ya estaban se quedan con `NULL`, que es la\n", "razón por la que una columna nueva casi nunca puede ser\n", "`NOT NULL` sin darle un `DEFAULT`." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "ALTER TABLE promociones RENAME COLUMN observacion TO nota;\n", "ALTER TABLE promociones DROP COLUMN nota;\n", "SELECT COUNT(*) AS columnas FROM pragma_table_info('promociones');\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Volvimos a las seis columnas del principio. Renombrar y borrar columnas\n", "llegó tarde a SQLite: `RENAME COLUMN` en la 3.25 y\n", "`DROP COLUMN` en la 3.35, o sea 2021. En una SQLite anterior, ninguna\n", "de las dos existe.\n", "\n", "Y hay una cosa que SQLite directamente no sabe hacer." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "try:\n", " q(\"\"\"\n", " ALTER TABLE promociones ALTER COLUMN descuento TYPE NUMERIC(4,2);\n", " \"\"\")\n", "except Exception as e:\n", " print(f'{type(e).__name__}: {e}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y la queja que tiene que salir es esta:\n", "\n", "```\n", "OperationalError: near \"ALTER\": syntax error\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Cambiarle el tipo a una columna\n", "\n", "| PostgreSQL | `ALTER TABLE t ALTER COLUMN c TYPE NUMERIC(10,2)` |\n", "|---|---|\n", "| MySQL | `ALTER TABLE t MODIFY c DECIMAL(10,2)` |\n", "| SQL Server | `ALTER TABLE t ALTER COLUMN c DECIMAL(10,2)` |\n", "| SQLite | `no se puede: hay que crear la tabla nueva, copiar los datos y renombrar` |\n", "\n", "Tres sintaxis parecidas y una imposible. El baile de SQLite es CREATE TABLE nueva, INSERT INTO nueva SELECT de la vieja, DROP de la vieja y ALTER TABLE nueva RENAME TO vieja. Se hace mucho y por eso conviene pensar los tipos antes." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Crear una tabla a partir de una consulta" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "CREATE TABLE clientes_lima AS\n", "SELECT id, nombre, ciudad, segmento\n", "FROM clientes\n", "WHERE ciudad = 'Lima';\n", "SELECT COUNT(*) AS copiados FROM clientes_lima;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Los 15 clientes de Lima, copiados a una tabla nueva en tres líneas. Esto lo\n", "uso muchísimo para trabajar tranquila: te haces tu copia, la rompes todo lo que\n", "quieras y la borras.\n", "\n", "El primo de esto es meter el resultado de una consulta en una tabla que ya\n", "existe." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "CREATE TABLE resumen_canal (\n", " canal TEXT PRIMARY KEY,\n", " pedidos INTEGER NOT NULL,\n", " soles REAL NOT NULL\n", ");\n", "INSERT INTO resumen_canal (canal, pedidos, soles)\n", "SELECT canal, COUNT(*), ROUND(SUM(monto), 2)\n", "FROM pedidos\n", "GROUP BY canal;\n", "SELECT * FROM resumen_canal ORDER BY soles DESC;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`INSERT INTO ... SELECT`, sin `VALUES`. Así se arman las\n", "tablas de resumen que alimentan un dashboard: se calculan una vez de noche y de\n", "día se leen, en vez de rehacer el `GROUP BY` cada vez que alguien\n", "abre el reporte 📊" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Crear una tabla con el resultado de un SELECT\n", "\n", "| PostgreSQL | `CREATE TABLE nueva AS SELECT ...` |\n", "|---|---|\n", "| MySQL | `CREATE TABLE nueva AS SELECT ...` |\n", "| SQL Server | `SELECT ... INTO nueva FROM ... -- no tiene CREATE TABLE AS` |\n", "| SQLite | `CREATE TABLE nueva AS SELECT ...` |\n", "\n", "Tres iguales y SQL Server con lo suyo, que además pone el nombre de la tabla nueva en medio de la consulta y se lee raro la primera vez." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Limpiar y borrar" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "UPDATE clientes_lima\n", "SET nombre = UPPER(TRIM(nombre));\n", "SELECT id, nombre FROM clientes_lima ORDER BY id LIMIT 3;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Ese es el `UPDATE` que arregla de verdad la suciedad del capítulo 5. Hasta ahora limpiábamos en cada consulta; aquí se limpia una vez y ya. Es\n", "mejor y es más peligroso, las dos cosas: si te equivocas de fórmula, el dato\n", "original ya no está." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "DELETE FROM clientes_lima WHERE segmento = 'Bodega';\n", "SELECT COUNT(*) AS quedan FROM clientes_lima;\n", "\"\"\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "DELETE FROM clientes_lima;\n", "SELECT COUNT(*) AS quedan FROM clientes_lima;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Sin `WHERE`, `DELETE` vacía la tabla pero la deja ahí,\n", "con sus columnas y sus restricciones. En los otros tres motores hay una forma más\n", "rápida de hacer eso mismo." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "try:\n", " q(\"\"\"\n", " TRUNCATE TABLE resumen_canal;\n", " \"\"\")\n", "except Exception as e:\n", " print(f'{type(e).__name__}: {e}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y la queja que tiene que salir es esta:\n", "\n", "```\n", "OperationalError: near \"TRUNCATE\": syntax error\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Vaciar una tabla entera\n", "\n", "| PostgreSQL | `TRUNCATE TABLE t -- y se puede deshacer dentro de una transacción` |\n", "|---|---|\n", "| MySQL | `TRUNCATE TABLE t -- NO se puede deshacer` |\n", "| SQL Server | `TRUNCATE TABLE t` |\n", "| SQLite | `no existe: DELETE FROM t` |\n", "\n", "TRUNCATE es más rápido que DELETE porque no va fila por fila, y por eso mismo en MySQL no hay vuelta atrás. Dos motores con la misma palabra y consecuencias distintas es de lo peor que te puede pasar un lunes." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "DROP TABLE clientes_lima;\n", "SELECT COUNT(*) AS existe FROM sqlite_master WHERE name = 'clientes_lima';\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`DELETE` vacía, `DROP` desaparece. Los tres verbos en\n", "una línea para que no se mezclen nunca:\n", "\n", "- 🧹 `DELETE FROM t WHERE ...`: quita las filas que digas.\n", "\n", "- 🚿 `TRUNCATE TABLE t`: quita todas, rapidísimo, y no está en\n", "SQLite.\n", "\n", "- 💣 `DROP TABLE t`: quita la tabla." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## El AUTOINCREMENT de SQLite, que no es lo que parece" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Te dije arriba que `INTEGER PRIMARY KEY` ya se numera solo y que\n", "la palabra `AUTOINCREMENT` hace otra cosa. Esta es la otra cosa." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "CREATE TABLE sin_auto (id INTEGER PRIMARY KEY, cosa TEXT);\n", "INSERT INTO sin_auto (cosa) VALUES ('a'), ('b'), ('c');\n", "DELETE FROM sin_auto WHERE cosa = 'c';\n", "INSERT INTO sin_auto (cosa) VALUES ('d');\n", "SELECT id, cosa FROM sin_auto ORDER BY id;\n", "\"\"\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "CREATE TABLE conteo (id INTEGER PRIMARY KEY AUTOINCREMENT, cosa TEXT);\n", "INSERT INTO conteo (cosa) VALUES ('a'), ('b'), ('c');\n", "DELETE FROM conteo WHERE cosa = 'c';\n", "INSERT INTO conteo (cosa) VALUES ('d');\n", "SELECT id, cosa FROM conteo ORDER BY id;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Sin `AUTOINCREMENT`, la `d` se quedó con el 3 que había\n", "dejado libre la `c`. Con `AUTOINCREMENT`, se fue al 4 y el\n", "3 no vuelve nunca.\n", "\n", "¿Y eso importa? Muchísimo, si ese `id` salió alguna vez de la base:\n", "en una factura, en un enlace, en un correo. Reutilizar un identificador\n", "significa que dos cosas distintas tuvieron el mismo número en momentos\n", "distintos, y desenredar eso es una tarde perdida 🫠\n", "\n", "Los otros tres motores nunca reutilizan por defecto, así que si vienes de\n", "ellos, esto de SQLite te va a sorprender." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Ejercicios" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Siete. Estos escriben, así que van sobre tu copia 💛" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 1. Tu propia tabla\n", "\n", "Crea una tabla de visitas comerciales con id automático,\n", "cliente obligatorio, fecha obligatoria, un comentario opcional y un resultado que\n", "por defecto sea \"pendiente\"." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "CREATE TABLE visitas_comerciales (\n", " id INTEGER PRIMARY KEY,\n", " id_cliente INTEGER NOT NULL REFERENCES clientes(id),\n", " fecha TEXT NOT NULL,\n", " comentario TEXT,\n", " resultado TEXT NOT NULL DEFAULT 'pendiente'\n", ");\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "No lleva salida porque crear una tabla no devuelve nada: si no dice nada, fue\n", "bien. La comprobación es `PRAGMA table_info(visitas_comerciales)`.\n", "\n", "Y acuérdate de encender las claves foráneas, o ese\n", "`REFERENCES` es un adorno." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2. El resumen por ciudad, guardado\n", "\n", "Arma una tabla con clientes, pedidos y soles por ciudad,\n", "calculada con lo del capítulo 7." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "CREATE TABLE resumen_ciudad AS\n", "SELECT c.ciudad,\n", " COUNT(DISTINCT c.id) AS clientes,\n", " COUNT(p.id) AS pedidos,\n", " ROUND(SUM(p.monto), 2) AS soles\n", "FROM clientes c\n", "LEFT JOIN pedidos p ON p.id_cliente = c.id\n", "GROUP BY c.ciudad;\n", "SELECT * FROM resumen_ciudad ORDER BY soles DESC;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "ciudad clientes pedidos soles\n", "-------- -------- ------- ---------\n", "Chiclayo 25 212 115979.43\n", "Piura 24 167 107165.17\n", "Arequipa 22 168 101432.51\n", "Cusco 17 112 64966.23\n", "Trujillo 17 113 64150.66\n", "Lima 15 104 63748.53\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Ojo con una cosa que `CREATE TABLE AS` no hace: la tabla nueva\n", "**no hereda claves ni restricciones**, solo los datos y los nombres\n", "de columna. Si esa tabla va a vivir, créala a mano y llénala con\n", "`INSERT INTO ... SELECT`." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 3. Limpiar los nombres de una vez\n", "\n", "Sobre una copia de clientes, arregla los dieciocho nombres\n", "sucios del capítulo 5 y comprueba que quedaron cero." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "CREATE TABLE clientes_limpios AS SELECT * FROM clientes;\n", "UPDATE clientes_limpios SET nombre = TRIM(nombre) WHERE nombre <> TRIM(nombre);\n", "SELECT COUNT(*) AS todavia_sucios FROM clientes_limpios WHERE nombre <> TRIM(nombre);\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "todavia_sucios\n", "--------------\n", "0\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Ese `WHERE nombre <> TRIM(nombre)` del `UPDATE` no\n", "hace falta para que funcione, y lo pongo igual por costumbre: así el\n", "`UPDATE` toca 18 filas y no 120, y si algo sale mal el destrozo es\n", "más chico." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4. El CHECK que te habría salvado\n", "\n", "Crea una tabla de pedidos nuevos donde el monto no pueda ser\n", "negativo ni nulo, y compruébalo intentando meter uno de -50." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "CREATE TABLE pedidos_nuevos (\n", " id INTEGER PRIMARY KEY,\n", " fecha TEXT NOT NULL,\n", " monto REAL NOT NULL CHECK (monto >= 0)\n", ");\n", "INSERT INTO pedidos_nuevos (fecha, monto) VALUES ('2026-06-24', -50);\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "IntegrityError: CHECK constraint failed: monto >= 0\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Un `CHECK` de tres palabras que impide para siempre una categoría\n", "entera de errores. Si `pedidos` lo hubiera tenido con\n", "`NOT NULL`, no tendríamos los 27 montos vacíos del capítulo 4." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 5. Añadir una columna con valor por defecto\n", "\n", "Añádele a tu tabla de promociones una columna de prioridad\n", "que valga 3 para todas las que ya existen." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "ALTER TABLE promociones ADD COLUMN prioridad INTEGER NOT NULL DEFAULT 3;\n", "SELECT id, nombre, prioridad FROM promociones ORDER BY id;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "id nombre prioridad\n", "-- ----------------- ---------\n", "1 Bienvenida bodega 3\n", "2 Fin de mes 3\n", "3 Mayorista 2026 3\n", "4 Recuperacion 3\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Con `DEFAULT`, una columna nueva sí puede ser\n", "`NOT NULL`: el motor rellena las filas viejas con el valor por\n", "defecto. Sin él, ese mismo `ALTER` da error en los cuatro\n", "motores." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 6. El UPDATE con la red puesta\n", "\n", "Sube un 5% el descuento de las promos de Web, mirando antes\n", "a quién le va a tocar." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT id, nombre, descuento FROM promociones WHERE canal = 'Web';\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "id nombre descuento\n", "-- ---------- ---------\n", "2 Fin de mes 0.15\n", "```" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "UPDATE promociones SET descuento = descuento + 0.05 WHERE canal = 'Web';\n", "SELECT id, nombre, descuento FROM promociones WHERE canal = 'Web';\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "id nombre descuento\n", "-- ---------- ---------\n", "2 Fin de mes 0.2\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Primero mirar, después cambiar. Es un segundo más y es la diferencia entre\n", "un martes normal y un martes de esos.\n", "\n", "Y fíjate en el `descuento = descuento + 0.05`: en un\n", "`UPDATE`, el lado derecho ve el valor viejo. Puedes calcular a partir\n", "de lo que ya había, y eso vale para cualquier columna de la misma fila." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 7. Escríbelo para los cuatro\n", "\n", "Sin ejecutar: la misma tabla de promociones, en los cuatro\n", "motores." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "-- PostgreSQL\n", "CREATE TABLE promociones (\n", " id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n", " nombre VARCHAR(80) NOT NULL,\n", " descuento NUMERIC(4,2) NOT NULL DEFAULT 0,\n", " activa BOOLEAN NOT NULL DEFAULT TRUE,\n", " creada DATE NOT NULL DEFAULT CURRENT_DATE\n", ");\n", "\n", "-- MySQL\n", "CREATE TABLE promociones (\n", " id INT AUTO_INCREMENT PRIMARY KEY,\n", " nombre VARCHAR(80) NOT NULL,\n", " descuento DECIMAL(4,2) NOT NULL DEFAULT 0,\n", " activa BOOLEAN NOT NULL DEFAULT TRUE,\n", " creada DATE NOT NULL DEFAULT (CURRENT_DATE)\n", ");\n", "\n", "-- SQL Server\n", "CREATE TABLE promociones (\n", " id INT IDENTITY(1,1) PRIMARY KEY,\n", " nombre NVARCHAR(80) NOT NULL,\n", " descuento DECIMAL(4,2) NOT NULL DEFAULT 0,\n", " activa BIT NOT NULL DEFAULT 1,\n", " creada DATE NOT NULL DEFAULT CAST(GETDATE() AS DATE)\n", ");\n", "\n", "-- SQLite\n", "CREATE TABLE promociones (\n", " id INTEGER PRIMARY KEY,\n", " nombre TEXT NOT NULL,\n", " descuento REAL NOT NULL DEFAULT 0,\n", " activa INTEGER NOT NULL DEFAULT 1,\n", " creada TEXT NOT NULL DEFAULT (DATE('now'))\n", ");\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Cuatro veces la misma tabla y no hay ni una línea idéntica en las cuatro.\n", "Este es el capítulo donde más se separan, y por eso es también donde más se\n", "copia mal de internet: buscas \"crear tabla SQL\", te sale la de MySQL, la pegas\n", "en Postgres y no arranca 🫠\n", "\n", "Lo que sí es igual en los cuatro: las palabras `CREATE TABLE`,\n", "`NOT NULL`, `DEFAULT`, `PRIMARY KEY`,\n", "`UNIQUE`, `CHECK` y `REFERENCES`. La estructura\n", "se comparte; los tipos y el autoincremento, no." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## La clave foránea que no guarda nada" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Antes de cerrar el capítulo, la diferencia más cara entre SQLite y los otros tres motores. Y no avisa de ninguna manera 🚪" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### La trampa\n", "\n", "Creas la tabla con su clave foránea, como manda el capítulo. Insertas un pedido de un cliente que no existe, esperando que la base te frene.\n", "\n", "```\n", "CREATE TABLE pedidos (\n", " id INTEGER PRIMARY KEY,\n", " id_cliente INTEGER REFERENCES clientes(id),\n", " monto REAL\n", ");\n", "\n", "INSERT INTO pedidos VALUES (99999, 88888, 10);\n", "-- entra sin una sola queja\n", "```\n", "\n", "**Qué está mal**\n", "\n", "El cliente 88888 no existe y el pedido entró igual 🚪 En SQLite las claves foráneas **están apagadas por defecto**. El `REFERENCES` se guarda, se lee, sale en el esquema, y no hace absolutamente nada.\n", "\n", "Se enciende por conexión, no por base: `PRAGMA foreign_keys = ON`, y hay que ponerlo **cada vez que te conectas**. Con eso encendido, el mismo INSERT devuelve *FOREIGN KEY constraint failed*, que es lo que esperabas desde el principio.\n", "\n", "Y ojo con lo que pasa después: encenderlo no revisa lo que ya está dentro. Los huérfanos que entraron mientras estaba apagado siguen ahí, y solo los encuentras buscándolos a mano con un LEFT JOIN.\n", "\n", "Esto es de SQLite y no de SQL: PostgreSQL, MySQL y SQL Server aplican la clave foránea desde el primer día. Es la diferencia más cara de las que tiene SQLite, porque te deja aprender con una red que no está puesta." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Comprueba que lo tienes\n", "\n", "Vas a corregir el monto de una venta y escribes UPDATE ventas SET monto = 480.37 y le das a ejecutar. ¿Qué acaba de pasar?\n", "\n", "a) Todas las filas de la tabla tienen ahora ese monto\n", "\n", "b) No pasó nada, falta el WHERE y el motor lo rechaza\n", "\n", "c) Se actualizó solo la primera fila\n", "\n", "d) Da error porque falta decir qué fila\n", "\n", "---\n", "\n", "**La correcta es la a.**\n", "\n", "*b)* Ningún motor rechaza un UPDATE sin WHERE. Es sintaxis válida.\n", "\n", "*c)* Sin WHERE no hay ninguna fila privilegiada: le toca a todas.\n", "\n", "*d)* El motor entiende perfectamente lo que pediste. El problema es que era lo que pediste.\n", "\n", "La costumbre que salva: escribir el WHERE primero y el UPDATE después, o probarlo como SELECT antes." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Lo que te llevas" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "- 💾 Antes de escribir en una base que le importa a alguien, copia. Todo este\n", "capítulo corrió sobre una copia.\n", "\n", "- 🔑 El autoincremento se llama distinto en los cuatro:\n", "`IDENTITY` en PostgreSQL y SQL Server (con sintaxis distinta),\n", "`AUTO_INCREMENT` en MySQL, y en SQLite basta\n", "`INTEGER PRIMARY KEY`.\n", "\n", "- 🛡️ `NOT NULL`, `DEFAULT`, `UNIQUE` y\n", "`CHECK` se escriben igual en los cuatro y son las promesas que la\n", "base sí cumple.\n", "\n", "- 🕳️ Las claves foráneas vienen APAGADAS en SQLite. Está escrita la promesa y\n", "no se cumple, y así nacen los huérfanos.\n", "\n", "- 💥 `UPDATE` y `DELETE` sin `WHERE` tocan\n", "toda la tabla, sin aviso. Escribe el `SELECT` primero.\n", "\n", "- 🧱 `ALTER TABLE ADD COLUMN` va igual en los cuatro; cambiar el\n", "tipo de una columna no se puede en SQLite.\n", "\n", "- 🧹 `DELETE` vacía, `TRUNCATE` vacía rápido y no está\n", "en SQLite, `DROP` desaparece la tabla.\n", "\n", "Y si de todo el capítulo te llevas una sola frase, que sea esta:" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Antes de escribir en una base que le importa a alguien, haz una copia.\n", "Siempre." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y todo esto de decidir qué columna existe, de qué tipo y qué se hace con\n", "lo que falta tiene un nombre cuando se acuerda con un cliente antes de\n", "empezar: contrato de datos. Está en el\n", "[libro de machine learning](https://missyera.com/guias/machine-learning-desde-cero/) 🤝\n", "\n", "En el capítulo 11 vienen las transacciones, que es la red de seguridad de\n", "todo lo que acabamos de hacer, y el UPSERT, que es \"insértalo, y si ya está,\n", "actualízalo\". Ahí los cuatro motores se separan otra vez y feo.\n", "\n", "Que tengas lindo día! 🌸" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---\n", "\n", "Ese era el capítulo 11 de **SQL desde cero**. El texto completo, con las salidas de cada bloque, está en https://missyera.com/guias/sql-desde-cero/crear-y-modificar/\n", "\n", "Que tengas lindo día! 🌸" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "name": "python", "version": "3.11" } }, "nbformat": 4, "nbformat_minor": 5 }