Estructura de tablas y tipos de datos de MySQL

Introducción

En este laboratorio, exploraremos los fundamentos de las estructuras de tablas y los tipos de datos de MySQL. Comprender cómo crear, modificar y administrar tablas es crucial para cualquier persona que trabaje con bases de datos. Aprenderemos sobre diferentes tipos de datos en MySQL, cómo elegir el tipo de dato adecuado para tus necesidades y cómo realizar operaciones básicas de tablas. Al final de este laboratorio, tendrás experiencia práctica en la creación de tablas, la definición de columnas con tipos de datos adecuados y la modificación de estructuras de tablas.

Objetivos

Al completar este laboratorio, podrás:

• Comprender los tipos de datos principales de MySQL y cuándo utilizarlos
• Crear tablas con definiciones de columnas adecuadas
• Modificar estructuras de tablas existentes
• Eliminar tablas cuando ya no sean necesarias
• Ver y entender los metadatos de las tablas

Comprender los tipos de datos de MySQL

En este paso, exploraremos los tipos de datos de MySQL más comúnmente utilizados. Comprender los tipos de datos es crucial porque elegir el tipo de dato adecuado para tus columnas afecta tanto la integridad de los datos como el rendimiento de la base de datos.

Dado que en este paso se involucran muchos comandos SQL, recomendamos utilizar la terminal web. Haz clic en la pestaña "Terminal" para abrirla; funciona igual que la terminal de escritorio.

Comencemos conectándonos a MySQL:

sudo mysql -u root

Ahora que estamos conectados, creemos una nueva base de datos para nuestros experimentos:

CREATE DATABASE store;
USE store;

Examinemos las principales categorías de tipos de datos de MySQL:

1. Tipos numéricos:

   • INT: Para números enteros
   • DECIMAL: Para números decimales precisos
   • FLOAT/DOUBLE: Para números decimales aproximados

2. Tipos de cadena:

   • VARCHAR: Para cadenas de longitud variable
   • CHAR: Para cadenas de longitud fija
   • TEXT: Para texto largo

3. Tipos de fecha y hora:

   • DATE: Para fechas (YYYY-MM-DD)
   • TIME: Para horas (HH:MM:SS)
   • DATETIME: Para fecha y hora

Creemos una tabla simple que demuestre estos diferentes tipos de datos:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    description TEXT,
    weight FLOAT,
    in_stock BOOLEAN,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Desglosemos esta estructura de tabla:

• id: Un entero autoincremental que sirve como clave primaria
• name: Una cadena de longitud variable que no puede ser NULL
• price: Un número decimal preciso con 10 dígitos en total y 2 decimales
• description: Un campo de texto para descripciones más largas
• weight: Un número de punto flotante para valores decimales aproximados
• in_stock: Un campo booleano (VERDADERO/FALSO)
• created_at: Almacena automáticamente la marca de tiempo de creación
• last_updated: Se actualiza automáticamente cuando el registro cambia

Para ver la estructura de nuestra tabla:

DESCRIBE products;

Deberías ver una salida como esta:

+--------------+---------------+------+-----+---------------------+-------------------------------+
| Field        | Type          | Null | Key | Default             | Extra                         |
+--------------+---------------+------+-----+---------------------+-------------------------------+
| id           | int(11)       | NO   | PRI | NULL                | auto_increment                |
| name         | varchar(100)  | NO   |     | NULL                |                               |
| price        | decimal(10,2) | NO   |     | NULL                |                               |
| description  | text          | YES  |     | NULL                |                               |
| weight       | float         | YES  |     | NULL                |                               |
| in_stock     | tinyint(1)    | YES  |     | NULL                |                               |
| created_at   | datetime      | YES  |     | current_timestamp() |                               |
| last_updated | timestamp     | NO   |     | current_timestamp() | on update current_timestamp() |
+--------------+---------------+------+-----+---------------------+-------------------------------+
8 rows in set (0.001 sec)

Creación de tablas con restricciones

En este paso, aprenderemos sobre las restricciones de tabla y cómo ayudan a mantener la integridad de los datos. Crearemos una estructura de tabla más compleja que demuestre varios tipos de restricciones.

Creemos dos tablas relacionadas para entender las relaciones y las restricciones:

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(200),
    active BOOLEAN DEFAULT TRUE
);

CREATE TABLE inventory_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    sku VARCHAR(20) NOT NULL UNIQUE,
    item_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL CHECK (quantity >= 0),
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

Examinemos las restricciones que hemos utilizado:

1. PRIMARY KEY (clave primaria): Asegura la identificación única de cada registro.
2. UNIQUE (único): Evita valores duplicados en una columna.
3. NOT NULL (no nulo): Asegura que una columna no pueda contener valores NULL.
4. CHECK (comprobación): Valida los datos antes de que se inserten.
5. FOREIGN KEY (clave foránea): Asegura la integridad referencial entre tablas.
6. DEFAULT (valor por defecto): Proporciona un valor predeterminado si no se especifica ninguno.

Para ver la estructura de la tabla con las restricciones:

SHOW CREATE TABLE inventory_items;

Esto mostrará la declaración completa de CREATE TABLE, incluyendo todas las restricciones:

MariaDB [store]> SHOW CREATE TABLE inventory_items;

<!-- Sample output -->

CREATE TABLE `inventory_items` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) DEFAULT NULL,
  `sku` varchar(20) NOT NULL,
  `item_name` varchar(100) NOT NULL,
  `quantity` int(11) NOT NULL CHECK (`quantity` >= 0),
  `unit_price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`item_id`),
  UNIQUE KEY `sku` (`sku`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `inventory_items_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci

Modificación de la estructura de la tabla

En este paso, aprenderemos cómo modificar tablas existentes utilizando comandos ALTER TABLE. Esta es una tarea común cuando los requisitos de tus datos cambian con el tiempo.

Modifiquemos nuestra tabla products para agregar algunas columnas nuevas y modificar las existentes:

1. Agregar una nueva columna:

ALTER TABLE products
ADD COLUMN manufacturer VARCHAR(100) AFTER name;

Este comando agrega una nueva columna llamada manufacturer después de la columna name.

2. Modificar una columna existente:

ALTER TABLE products
MODIFY COLUMN description VARCHAR(500) NOT NULL DEFAULT 'No description available';

Este comando cambia la columna description a una cadena de longitud variable con una longitud máxima de 500 caracteres. También establece un valor predeterminado de 'No description available' para las nuevas filas.

3. Renombrar una columna:

ALTER TABLE products
CHANGE COLUMN weight product_weight DECIMAL(8,2);

Este comando renombra la columna weight a product_weight y cambia su tipo de dato a un número decimal con 8 dígitos en total y 2 decimales.

4. Eliminar una columna:

ALTER TABLE products
DROP COLUMN in_stock;

Este comando elimina la columna in_stock de la tabla.

Agreguemos un índice compuesto para mejorar el rendimiento de las consultas:

ALTER TABLE products
ADD INDEX idx_name_manufacturer (name, manufacturer);

Este comando crea un índice compuesto en las columnas name y manufacturer.

Para ver todos los cambios que hemos realizado:

DESCRIBE products;
SHOW INDEX FROM products;

Deberías ver la estructura de la tabla actualizada y los índices:

MariaDB [store]> DESCRIBE products;
+----------------+---------------+------+-----+--------------------------+-------------------------------+
| Field          | Type          | Null | Key | Default                  | Extra                         |
+----------------+---------------+------+-----+--------------------------+-------------------------------+
| id             | int(11)       | NO   | PRI | NULL                     | auto_increment                |
| name           | varchar(100)  | NO   | MUL | NULL                     |                               |
| manufacturer   | varchar(100)  | YES  |     | NULL                     |                               |
| price          | decimal(10,2) | NO   |     | NULL                     |                               |
| description    | varchar(500)  | NO   |     | No description available |                               |
| product_weight | decimal(8,2)  | YES  |     | NULL                     |                               |
| created_at     | datetime      | YES  |     | current_timestamp()      |                               |
| last_updated   | timestamp     | NO   |     | current_timestamp()      | on update current_timestamp() |
+----------------+---------------+------+-----+--------------------------+-------------------------------+
8 rows in set (0.001 sec)

MariaDB [store]> SHOW INDEX FROM products;
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table    | Non_unique | Key_name              | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| products |          0 | PRIMARY               |            1 | id           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| products |          1 | idx_name_manufacturer |            1 | name         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| products |          1 | idx_name_manufacturer |            2 | manufacturer | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.000 sec)

Trabajar con información de tablas

En este paso, exploraremos diversas formas de recuperar información sobre nuestras tablas y su estructura.

Primero, echemos un vistazo a la información de estado de las tablas:

SHOW TABLE STATUS FROM store;

Este comando muestra información sobre cada tabla en la base de datos, incluyendo:

• Motor de almacenamiento (Storage engine)
• Formato de fila (Row format)
• Número de filas
• Longitud promedio de fila
• Longitud de los datos
• Longitud del índice

Para ver todas las tablas en nuestra base de datos:

SHOW TABLES;

Para ver información detallada sobre una columna específica:

SHOW FULL COLUMNS FROM products;

Esto proporciona información adicional sobre cada columna, incluyendo:

• Tipo de columna
• Collation
• Privilegios
• Comentarios

Para ver todos los índices en una tabla:

SHOW INDEX FROM products;

También podemos obtener información sobre nuestras tablas de la base de datos INFORMATION_SCHEMA:

SELECT
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    AVG_ROW_LENGTH,
    DATA_LENGTH,
    INDEX_LENGTH
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'store';

Eliminación de tablas y limpieza

En este último paso, aprenderemos cómo eliminar tablas de forma segura cuando ya no se necesiten. Esta es una habilidad importante, pero se debe utilizar con precaución, ya que eliminar una tabla elimina permanentemente todos sus datos.

Antes de eliminar una tabla, es una buena práctica:

1. Verificar que estás en la base de datos correcta.
2. Comprobar si la tabla existe.
3. Asegurarse de tener una copia de seguridad si es necesario.

Comencemos comprobando nuestra base de datos y tablas actuales:

SELECT DATABASE();
SHOW TABLES;

Para eliminar una tabla de forma segura, podemos utilizar la cláusula IF EXISTS:

DROP TABLE IF EXISTS inventory_items;

Ten en cuenta que primero debemos eliminar las tablas con restricciones de clave foránea. Si intentáramos eliminar primero la tabla categories, obtendríamos un error porque inventory_items la referencia.

Ahora podemos eliminar las tablas restantes:

DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS products;

Para verificar que las tablas se han eliminado:

SHOW TABLES;

Deberías ver un conjunto vacío, lo que indica que todas las tablas se han eliminado.

Resumen

En este laboratorio, hemos cubierto los aspectos esenciales del trabajo con tablas y tipos de datos de MySQL:

1. Comprender y utilizar diferentes tipos de datos de MySQL.
2. Crear tablas con restricciones adecuadas.
3. Modificar estructuras de tablas utilizando ALTER TABLE.
4. Recuperar metadatos e información de las tablas.
5. Eliminar tablas de forma segura cuando ya no se necesiten.

Estas habilidades forman la base para trabajar de manera efectiva con bases de datos MySQL.