Crear / Modificar / Eliminar tablas en MySQL

Una de las tareas frecuentes durante el proceso de desarrollo de un proyecto es el cambio de la estructura de la base de datos. Esta guía pretende mostrar como crear, modificar y eliminar tablas de una base de datos MySQL; no obstante las sentencias implicadas en estas operaciones son extensas y complejas y abordarlas en su totalidad sería engorroso y poco práctico. Para más información remítase al sitio oficial de MySQL

Crear Tabla

Crear la tabla hotel con la siguiente especificación:

• – Identificador: entero sin signo, autoincrementable, llave primaria
• – nombre: longitud variable, 100 carácteres como máxima longitud y no nulo
• – activo: booleano, no nullo, 1 valor por defecto
• – url: longitud variable, 255 carácteres como máxima longitud, índice único

CREATE TABLE hotel (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    descripcion TEXT,
    activo TINYINT(1) DEFAULT '1' NOT NULL,
    url VARCHAR(255),
    UNIQUE INDEX url_idx (url)) DEFAULT CHARACTER SET utf8
    COLLATE utf8_general_ci ENGINE = InnoDB;

Nótese que hemos especificado codificación UTF-8 y motor de almacenamiento InnoDB para esta tabla.

Crear la tabla trabajador con la siguiente especificación:

• – Identificador: entero sin signo, autoincrementable, llave primaria
• – nombre: longitud variable, 100 carácteres como máxima longitud y no nulo
• – activo: booleano, no nullo, 1 valor por defecto

CREATE TABLE trabajador (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    activo TINYINT(1) DEFAULT '1' NOT NULL,
    hotel_id INT UNSIGNED ) DEFAULT CHARACTER SET utf8
    COLLATE utf8_general_ci ENGINE = InnoDB;

Ver especificación de una tabla

DESC hotel;

Ver índices de una tabla

SHOW INDEX FROM hotel;

Agregar columna

Agregar la columna código a la tabla trabajador

ALTER TABLE trabajador ADD COLUMN codigo CHAR(11) NOT NULL;

Por defecto la sentencia ADD COLUMN añade la columna al final de la tabla si desea agregar la columna en una posición especifíca use la sentencia AFTER, por ejemplo: agregar la columna identificación personal a la tabla trabajador después de la columna código

ALTER TABLE trabajador ADD COLUMN id_personal CHAR(11) NOT NULL AFTER codigo;

Cambiar la codificación a una columna

A pesar de que la tabla ha sido creada con codificación UTF-8 por defecto no todas las columnas necesitan tener este tipo de codificación, por ejemplo los más probable es que el código del trabajador esté formado por carácteres ASCII por tanto modificaríamos la sentencia anterior a:

ALTER TABLE trabajador MODIFY COLUMN codigo CHAR(11) 
 CHAR SET 'ascii' COLLATE 'ascii_general_ci' NOT NULL;

Nota: Recuerde que una columna UTF-8 requiere más memoria y procesamiento que una columna ASCII por tanto así estaremos contribuyendo a mejorar el rendimiento de nuestra aplicación

Modificar definición de una columna

Restringir los valores de hotel_id a valores no nulos en la tabla trabajador

ALTER TABLE trabajador MODIFY COLUMN hotel_id INT UNSIGNED NOT NULL;

Crear índice

El código del trabajador debe ser único

CREATE UNIQUE INDEX trabajador_codigo_idx USING BTREE
ON trabajador(codigo);

Crear relación

Aquí se establece la relación entre trabajador y hotel (Un hotel puede tener muchos trabajadores y un trabajador pertenece a un solo hotel). Cuando se elemina un hotel se eliminan todos sus trabajadores

ALTER TABLE trabajador ADD CONSTRAINT trabajador_hotel_id_hotel_id
FOREIGN KEY (hotel_id) REFERENCES hotel(id) ON DELETE CASCADE;

Renombrar columna

Cambiar el nombre de la columna url a slug de la tabla hotel y restringir los valores a no nulos y cadena vacía como valor por defecto

ALTER TABLE hotel CHANGE COLUMN url slug VARCHAR(255) NOT
NULL DEFAULT '';

Renombrar tabla

ALTER TABLE trabajador RENAME TO trabajadores;

Eliminar tabla

DROP TABLE tabla1[,tabla2, ...]

Eliminar índice

ALTER TABLE DROP INDEX trabajador_codigo_idx;

Eliminar columna

Eliminar la columna código de la tabla trabajadores

ALTER TABLE trabajadores DROP COLUMN codigo;

Lecturas recomendadas

– help create table
– help alter table
– help drop table

Administrar MySQL desde la línea de comandos, 14 (18)

• Cómo instalar MySQL 8.0 en Ubuntu 20.04
• Como instalar MySQL 8.0 en Debian
• Cómo instalar MariaDB en Alpine Linux
• Cómo instalar MariaDB en NetBSD
• MySQL 8.0, cambiar contraseña de root
• ¿Cómo mostrar variables del sistema en MySQL?
• Deshabilitar innodb engine en mysql
• Administrar servidor MySQL – Básico
• Motores de almacenamiento disponibles/habilitados en MySQL
• MySQL: Recuperar la clave (password) de root
• MySQL Administración de usuarios
• MySQL ejecutar script SQL
• Copias de seguridad (Backup) en MySQL
• Crear / Modificar / Eliminar tablas en MySQL
• Connectarse a MySQL desde la LAN
• Character set disponibles en MySQL
• MySQL no puede escribir en el dir /tmp
• Cómo instalar MySQL 5.7 en CentOS 7