UT2. Bases de datos relacionales

En esta unidad se estudiaran los conceptos fundamentales de las bases de datos relacionales, incluyendo su terminología, tipos de datos, claves primarias y ajenas, vistas, usuarios y privilegios, así como los lenguajes de descripción y control de datos (DDL y DCL).

Modelo de datos

Ya en la primera parte de la unidad se abordó el modelo de datos relacional, proporcionando una comprensión sólida de cómo se estructuran y organizan los datos en una base de datos relacional.

Para el diseño físico de una base de datos relacional, se suelen seguir tres etapas principales: el modelo conceptual, el modelo lógico y el modelo físico. Cada una de estas etapas tiene un propósito específico y contribuye a la creación de una base de datos eficiente y bien estructurada.

Modelo conceptual

El modelo conceptual es una representación abstracta de la base de datos que se centra en las entidades, sus atributos y las relaciones entre ellas. En esta fase, se identifican las entidades principales, sus atributos y las relaciones entre ellas. El modelo conceptual no se preocupa por cómo se implementarán estos elementos en una base de datos específica, sino que se enfoca en capturar la estructura lógica de la información. Para su representación, se utiliza comúnmente el diagrama entidad-relación, que muestra las entidades como rectángulos, los atributos como óvalos y las relaciones como rombos.

Ejemplo de diagrama entidad-relación

Modelo lógico

El modelo lógico es una representación más detallada de la base de datos, que se obtiene a partir del modelo conceptual. En esta fase, se definen las tablas, columnas, claves primarias y foráneas, así como las relaciones que formarán la base de datos relacional. El modelo lógico se centra en cómo se organizarán los datos desde el punto de vista relacional, pero sin depender todavía de un sistema de gestión de bases de datos (SGBD) específico.

Ejemplo de modelo lógico

Cliente	Producto	Compra
id_cliente PK nombre email	id_producto PK nombre precio	id_compra PK fecha id_cliente FK → Cliente(id_cliente) id_producto FK → Producto(id_producto)

Modelo físico

El modelo físico es la implementación concreta de la base de datos en un sistema de gestión de bases de datos (SGBD) específico, como MySQL, PostgreSQL, Oracle, entre otros. En esta fase, se traducen las tablas, columnas y relaciones definidas en el modelo lógico y conceptual a estructuras físicas que pueden ser almacenadas y gestionadas por el SGBD. El modelo físico incluye detalles específicos del SGBD, como tipos de datos, índices, particiones y otras optimizaciones que mejoran el rendimiento y la eficiencia del almacenamiento de datos.

Ejemplo de modelo físico

CREATE TABLE Cliente (
    id_cliente INT PRIMARY KEY,
    nombre VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE Producto (
    id_producto INT PRIMARY KEY,
    nombre VARCHAR(100),
    precio DECIMAL(10,2)
);

CREATE TABLE Compra (
    id_compra INT PRIMARY KEY,
    fecha DATETIME,
    id_cliente INT,
    CONSTRAINT fk_cliente FOREIGN KEY (id_cliente) REFERENCES Cliente(id_cliente),
    CONSTRAINT fk_producto FOREIGN KEY (id_producto) REFERENCES Producto(id_producto)
);

Terminología del modelo relacional

En el modelo relacional, se utilizan varios términos clave para describir la estructura y organización de los datos en una base de datos. A continuación, se presentan algunos de los términos más importantes:

Entidad: Una entidad es un objeto o concepto del mundo real que se representa en la base de datos. Por ejemplo, un cliente, un producto o una compra pueden ser entidades.
Relación: Una relación es una asociación entre dos o más entidades. En el modelo relacional, las relaciones se representan mediante tablas que contienen filas y columnas.
Tabla (o relación): Una tabla es una colección de datos organizados en filas y columnas. Cada tabla representa una entidad o concepto del mundo real.
Fila (o tupla): Una fila es un conjunto de datos relacionados que representan una instancia específica de una entidad. Cada fila contiene valores para cada columna de la tabla.
Columna (o atributo): Una columna es una categoría de datos dentro de una tabla. Cada columna tiene un nombre y un tipo de datos asociado, que define el tipo de información que puede almacenarse en esa columna.
Clave primaria (PK): Una clave primaria es un atributo o conjunto de atributos que identifica de manera única a cada fila en una tabla. No puede haber dos filas con el mismo valor de clave primaria.
Clave foránea (FK): Una clave foránea es un atributo o conjunto de atributos en una tabla que hace referencia a la clave primaria de otra tabla. Se utiliza para establecer relaciones entre tablas.

SQL

SQL (Structured Query Language) es el lenguaje estándar utilizado para gestionar y manipular bases de datos relacionales. SQL permite realizar diversas operaciones, como la creación y modificación de tablas, la inserción, actualización y eliminación de datos, así como la consulta y recuperación de información. SQL se divide en varias categorías, entre las que se incluyen:

DDL (Data Definition Language): Se utiliza para definir y modificar la estructura de la base de datos, incluyendo la creación, modificación y eliminación de tablas e índices. Ejemplos de comandos DDL son CREATE, ALTER y DROP.
DML (Data Manipulation Language): Se utiliza para manipular los datos dentro de las tablas. Esto incluye la inserción, actualización y eliminación de registros. Ejemplos de comandos DML son INSERT, UPDATE y DELETE.
DQL (Data Query Language): Se utiliza para consultar y recuperar datos de la base de datos. El comando principal de DQL es SELECT.
DCL (Data Control Language): Se utiliza para controlar el acceso a los datos en la base de datos. Esto incluye la concesión y revocación de permisos a los usuarios. Ejemplos de comandos DCL son GRANT y REVOKE.

En esta unidad, no centraremos en el DDL y DCL, que son fundamentales para la definición y control de datos en una base de datos relacional.

Sintaxis básica de SQL

La sintaxis de SQL está compuesta por una serie de instrucciones que tienen diferentes parámetros, unos obligatorios y otros opcionales. Para la expliacion de la sintaxis, se utilizará la siguiente convención:

Símbolo	Descripción
Mayúsculas	Indican palabras reservadas del lenguaje.
Minúsculas	Indican elementos definidos por el usuario.
`[]`	Indica que el elemento es opcional.
`{}`	Indica que se debe elegir una opción.
`\|`	Indica una alternativa entre opciones.
`...`	Indica que el elemento puede repetirse.

Ejemplo de sintaxis SQL

CREATE TABLE nombre_tabla (
    nombre_columna tipo_dato [CONSTRAINT restricción] ...,
    ...
    [CONSTRAINT restricción_tabla]
);

Tenemos que destacar que cada instrucción SQL debe terminar con un punto y coma (;), que indica el final de la instrucción.

Case sensitive

SQL no es sensible a mayúsculas y minúsculas en las palabras reservadas, pero sí lo es en los nombres de tablas y columnas, dependiendo del sistema de gestión de bases de datos (SGBD) utilizado. Por ejemplo, en MySQL, los nombres de tablas son sensibles a mayúsculas y minúsculas en sistemas operativos que distinguen entre ellas (como Linux), mientras que en Windows no lo son. Es importante tener en cuenta estas diferencias al diseñar y consultar bases de datos para evitar errores inesperados.

Elemento	Windows (por defecto)	Linux (por defecto)
Nombre de BD	No distingue mayúsculas/minúsculas	Distingue mayúsculas/minúsculas
Nombre de tabla	No distingue mayúsculas/minúsculas	Distingue mayúsculas/minúsculas
Nombre de columna / alias	No distingue mayúsculas/minúsculas	No distingue mayúsculas/minúsculas
Cadenas en consultas (`WHERE`)	Depende de la `collation`	Depende de la `collation`

Recomendación

Para evitar problemas de compatibilidad y confusión, es recomendable utilizar un estilo consistente para los nombres de tablas y columnas, como todo en minúsculas o todo en mayúsculas, y evitar el uso de espacios o caracteres especiales. Normalmente, aquellas palabras reservadas de SQL se escriben en mayúsculas para diferenciarlas de los nombres definidos por el usuario que están en minúsculas.

Tipos de datos

Antes de pasar a la definición de tablas, es importante entender los tipos de datos que se pueden utilizar en una base de datos relacional. Los tipos de datos definen la naturaleza de los datos que se almacenan en una columna específica. A continuación, se presentan algunos de los tipos de datos más comunes:

Numéricos: INT, FLOAT, DOUBLE, DECIMAL
Cadenas de texto: CHAR, VARCHAR, TEXT
Fechas y horas: DATE, TIME, DATETIME, TIMESTAMP
Booleanos: BOOLEAN
Otros: BLOB, JSON, XML

En cuanto a los tipos de datos, es importante tener en cuenta que cada sistema de gestión de bases de datos (SGBD) puede tener su propia implementación y variaciones en los tipos de datos disponibles. Por lo tanto, es recomendable consultar la documentación específica del SGBD que se esté utilizando para obtener información detallada sobre los tipos de datos soportados.

En la siguiente tabla se muestran los tipos de datos más comunes en SQL para MYSQL y ORACLE:

Categoría	MySQL	Oracle	Notas
Enteros	TINYINT, SMALLINT, INT, BIGINT	NUMBER(p)	En Oracle, `NUMBER(p)` define precisión. Ej: `NUMBER(10)` ≈ INT.
Decimales	DECIMAL(p,s), NUMERIC(p,s)	NUMBER(p,s)	Ambos permiten precisión y escala.
Flotantes	FLOAT, DOUBLE	BINARY_FLOAT, BINARY_DOUBLE	MySQL usa FLOAT/DOUBLE; Oracle diferencia precisión binaria.
Caracteres	CHAR(n), VARCHAR(n)	CHAR(n), VARCHAR2(n)	En Oracle `VARCHAR` es sinónimo obsoleto de `VARCHAR2`.
Texto largo	TEXT, MEDIUMTEXT, LONGTEXT	CLOB, NCLOB	MySQL tiene variantes de TEXT; Oracle usa CLOB/NCLOB.
Binarios	BLOB, MEDIUMBLOB, LONGBLOB	BLOB, RAW(n), LONG RAW	Ambos soportan BLOB; Oracle también permite RAW.
Fecha/Hora	DATE, DATETIME, TIMESTAMP	DATE, TIMESTAMP, TIMESTAMP WITH TZ	En MySQL `DATE` incluye solo fecha; en Oracle incluye fecha y hora.
Hora	TIME	INTERVAL DAY TO SECOND	MySQL tiene `TIME`; Oracle usa INTERVALOS.
Booleanos	BOOLEAN (alias de TINYINT(1))	No nativo, se simula con NUMBER(1)	Oracle no tiene tipo BOOLEAN en tablas, solo en PL/SQL.
ID	AUTO_INCREMENT	SEQUENCE + TRIGGER o IDENTITY (>=12c)	MySQL usa `AUTO_INCREMENT`; Oracle usa SEQUENCE o `IDENTITY`.

Se puede consultar la documentación oficial de cada SGBD para obtener información más detallada sobre los tipos de datos y sus características específicas: MySQL, Oracle, PostgreSQL o MariaDB.

Nota

DECIMAL(5,2) -- 5 dígitos en total, 2 decimales (ejemplo: 120.99)

¿Que tipo de datos usar?

Una de las decisiones más importantes al diseñar una base de datos es elegir el tipo de datos adecuado para cada columna. Esta elección afecta el rendimiento, la integridad y la eficiencia del almacenamiento de datos. Ademas del tipo de datos, es importante considerar el tamaño y la precisión necesarios para cada columna. Por ejemplo, si se espera almacenar números enteros pequeños, se puede utilizar TINYINT en lugar de INT para ahorrar espacio de almacenamiento.

Cadenas de texto

Como hemos visto, existen varios tipos de datos para almacenar cadenas de texto, como CHAR, VARCHAR y TEXT. La elección entre estos tipos depende del tamaño y la naturaleza de los datos que se van a almacenar. Algunas consideraciones importantes son:

CHAR: Se utiliza para cadenas de longitud fija. Es adecuado cuando se sabe que todas las cadenas tendrán la misma longitud. Por ejemplo, para DNI empleariamos CHAR(9) (8 números más la letra).
VARCHAR: Se utiliza para cadenas de longitud variable. Es más flexible que CHAR, ya que solo utiliza el espacio necesario para almacenar la cadena real, más un pequeño overhead. Es adecuado para datos como nombres o direcciones, donde la longitud puede variar significativamente. Por ejemplo, para nombres de personas podríamos usar VARCHAR(50).
TEXT: Se utiliza para cadenas de texto de longitud variable y potencialmente muy larga. Es adecuado para almacenar grandes cantidades de texto, como descripciones o comentarios. Sin embargo, puede tener limitaciones en cuanto a la manipulación y el rendimiento en comparación con VARCHAR.

Advertencia

Si queremos ordenar o buscar en una columna, es mejor usar CHAR o VARCHAR, ya que TEXT puede tener limitaciones en cuanto a la manipulación y el rendimiento.

Nota

En MySQL, el tamaño máximo de VARCHAR es 65,535 bytes, pero este límite depende del conjunto de caracteres utilizado y del tamaño total de la fila. Por ejemplo, si se utiliza UTF-8, cada carácter puede ocupar hasta 3 bytes, lo que reduce el número máximo de caracteres que se pueden almacenar en una columna VARCHAR. TEXT puede almacenar hasta 65,535 bytes, pero no se puede indexar completamente, lo que puede afectar el rendimiento de las consultas. Y por último, CHAR puede almacenar hasta 255 caracteres.

Números

Los tipos de datos numéricos incluyen TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE y DECIMAL. La elección entre estos tipos depende de la naturaleza de los datos que se van a almacenar y de los requisitos de precisión. Algunas consideraciones importantes son:

TINYINT, SMALLINT, INT, BIGINT: Se utilizan para almacenar números enteros de diferentes rangos. La elección entre estos tipos depende del rango de valores que se espera almacenar. Por ejemplo, TINYINT es adecuado para valores pequeños (de -128 a 127), mientras que BIGINT es adecuado para valores muy grandes (de -9,223,372,036,854,775,808 a 9,223,372,036,854,775,807). "Nueve trillones doscientos veintitrés mil trescientos setenta y dos billones treinta y seis mil ochocientos cincuenta y cuatro millones setecientos setenta y cinco mil ochocientos siete." 😥
FLOAT, DOUBLE: Se utilizan para almacenar números de punto flotante. FLOAT es adecuado para valores con menor precisión, mientras que DOUBLE es adecuado para valores con mayor precisión. Sin embargo, ambos tipos pueden introducir errores de redondeo debido a la naturaleza de los números de punto flotante.
DECIMAL: Se utiliza para almacenar números con una precisión exacta, como valores monetarios. DECIMAL permite especificar la precisión total y la cantidad de decimales, lo que lo hace adecuado para aplicaciones financieras donde la exactitud es crucial.

Tipo de dato	MySQL (`SIGNED`)	MySQL (rango `UNSIGNED`)
TINYINT	-128 a 127	0 a 255
SMALLINT	-32,768 a 32,767	0 a 65,535
INT / INTEGER	-2,147,483,648 a 2,147,483,647	0 a 4,294,967,295
BIGINT	-9,223,372,036,854,775,808 a 9,223,372,036,854,775,807	0 a 18,446,744,073,709,551,615
DECIMAL(p,s)	Depende de la precisión (hasta 65 dígitos)	N/A
FLOAT (32 bits)	Aproximadamente ±1.17549e-38 a ±3.40282e+38	N/A
DOUBLE (64 bits)	Aproximadamente ±2.22507e-308 a ±1.79769e+308	N/A

Fechas y horas

Los tipos de datos para fechas y horas incluyen DATE, TIME, DATETIME y TIMESTAMP. La elección entre estos tipos depende de la naturaleza de los datos que se van a almacenar y de los requisitos de precisión. Algunas consideraciones importantes son:

DATE: Se utiliza para almacenar solo la fecha (año, mes y día). Es adecuado para datos como fechas de nacimiento o fechas de eventos. Por ejemplo, DATE almacena valores en el formato 'YYYY-MM-DD', como '2023-10-15'.
TIME: Se utiliza para almacenar solo la hora (horas, minutos y segundos). Es adecuado para datos como horarios de apertura o duración de eventos. Por ejemplo, TIME almacena valores en el formato 'HH:MM:SS', como '14:30:00'.
DATETIME: Se utiliza para almacenar tanto la fecha como la hora. Es adecuado para datos como marcas de tiempo de eventos o registros de actividad. Por ejemplo, DATETIME almacena valores en el formato 'YYYY-MM-DD HH:MM:SS', como '2023-10-15 14:30:00'.
TIMESTAMP: Similar a DATETIME, pero se utiliza para almacenar marcas de tiempo en formato UTC. Es adecuado para datos que requieren seguimiento de cambios y auditoría. Por ejemplo, TIMESTAMP almacena valores en el formato 'YYYY-MM-DD HH:MM:SS', pero se convierte automáticamente a la zona horaria del servidor.

Enumerados

En MySQL/MariaDB, el tipo de dato ENUM permite definir una columna que puede contener uno de varios valores predefinidos. Es útil para almacenar datos que tienen un conjunto limitado de opciones, como estados o categorías. Por ejemplo, se puede definir una columna estado que solo permita los valores 'activo', 'inactivo' o 'pendiente':

...
estado ENUM('activo', 'inactivo', 'pendiente')
...

Advertencia

Este tipo de datos no está definido en el estándar SQL, por lo que no es soportado por todos los SGBD. Esto produce problemas de portabilidad si se quiere migrar la base de datos a otro SGBD. Se recomienda usar restricciones CHECK en su lugar para asegurar la integridad de los datos.

El valor NULL

El valor NULL en SQL representa la ausencia de un valor o un valor desconocido. Es importante entender que NULL no es lo mismo que cero (0) o una cadena vacía (''). En SQL, NULL se utiliza para indicar que un campo no tiene ningún valor asignado.

Cuando se define una columna en una tabla, se puede especificar si esa columna permite valores NULL o no. Por defecto, las columnas permiten valores NULL, pero se puede cambiar este comportamiento utilizando la restricción NOT NULL.

DDL: Data Definition Language

El lenguaje de definición de datos (DDL) se utiliza para definir y modificar la estructura de una base de datos. Los comandos DDL más comunes son:

CREATE: Se utiliza para crear nuevas tablas, bases de datos, índices y otros objetos en la base de datos.
ALTER: Se utiliza para modificar la estructura de una tabla existente, como agregar, eliminar o modificar columnas.
DROP: Se utiliza para eliminar tablas, bases de datos, índices y otros objetos de la base de datos.

Instalación de MySQL

Para el desarrollo de las prácticas, se utilizará MySQL como sistema de gestión de bases de datos (SGBD). Puedes descargar e instalar MySQL Community Server desde su página oficial. Durante la instalación, asegúrate de configurar una contraseña segura para el usuario root, ya que este usuario tiene privilegios administrativos completos en la base de datos.

Para interactuar con MySQL, puedes utilizar la línea de comandos de MySQL o una herramienta gráfica como MySQL Workbench, que también está disponible en la página de descargas de MySQL. Puedes encontrar tutoriales y documentación en línea para ayudarte a instalar y configurar MySQL según tus necesidades.

Base de datos

Antes de crear tablas, es necesario crear una base de datos donde se almacenarán dichas tablas. Para crear una base de datos en MySQL, se utiliza el comando CREATE DATABASE seguido del nombre que se desea asignar a la base de datos. Por ejemplo, para crear una base de datos llamada mi_base_de_datos, se utilizaría la siguiente instrucción SQL:

CREATE [OR REPLACE] DATABASE [IF NOT EXISTS] mi_base_de_datos;

Podemos mostrar las bases de datos existentes con el comando:

SHOW DATABASES;

Y por otro lado, para eliminar una base de datos, se utiliza el comando DROP DATABASE seguido del nombre de la base de datos que se desea eliminar:

DROP DATABASE IF EXISTS mi_base_de_datos;

Una vez creada la base de datos, es necesario seleccionarla para poder trabajar con ella. Esto se hace con el comando USE seguido del nombre de la base de datos:

USE mi_base_de_datos;

Jerarquía de almacenamiento

La jerarquía de almacenamiento de datos se organiza en varios niveles, desde el más general hasta el más específico. A continuación, se describe esta jerarquía con independencia del SGBD utilizado:

Tablespace: Es una estructura física de almacenamiento que agrupa varios archivos de datos. Un tablespace puede contener múltiples bases de datos y es gestionado por el SGBD.
Base de datos: Es un contenedor lógico que agrupa varias tablas y otros objetos relacionados. Cada base de datos tiene su propio conjunto de tablas, vistas, índices y otros objetos.
Esquema (schema): Es una colección de objetos de base de datos, como tablas, vistas, procedimientos almacenados, etc., que pertenecen a un usuario específico. Un esquema ayuda a organizar y gestionar los objetos dentro de una base de datos. En MySQL, el esquema es sinónimo de base de datos.
Tabla: Es una estructura que almacena datos en filas y columnas. Cada tabla representa una entidad o concepto del mundo real y contiene datos relacionados.

Motores de almacenamiento

En los sistemas de gestión de bases de datos (SGBD) como MySQL, un motor de almacenamiento es el componente responsable de gestionar cómo se almacenan, recuperan y manipulan los datos en las tablas. MySQL soporta varios motores de almacenamiento, cada uno con sus propias características y ventajas. Algunos de los motores de almacenamiento más comunes en MySQL son:

InnoDB: Es el motor de almacenamiento predeterminado en MySQL. Soporta transacciones, claves foráneas y bloqueo a nivel de fila, lo que lo hace adecuado para aplicaciones que requieren integridad referencial y concurrencia.
MyISAM: Es un motor de almacenamiento más antiguo que no soporta transacciones ni claves foráneas. Es más rápido para operaciones de lectura, pero menos eficiente para operaciones de escritura y no garantiza la integridad referencial.

Bloqueo a nivel de fila vs. tabla

Bloqueo a nivel de fila: Permite que múltiples transacciones accedan y modifiquen diferentes filas de una tabla simultáneamente, mejorando la concurrencia y el rendimiento en entornos con muchas operaciones de escritura.
Bloqueo a nivel de tabla: Bloquea toda la tabla durante una operación de escritura, lo que puede reducir la concurrencia y el rendimiento en entornos con muchas operaciones de escritura.

Juego de caracteres y collation

Un juego de caracteres (character set) es un conjunto de caracteres que se utilizan para representar texto en una base de datos. Cada juego de caracteres tiene un conjunto específico de caracteres y un esquema de codificación asociado. Algunos juegos de caracteres comunes en MySQL son:

utf8mb4: Es un juego de caracteres que soporta todos los caracteres Unicode, incluyendo emojis y otros caracteres especiales. Es el juego de caracteres recomendado para la mayoría de las aplicaciones.
latin1: Es un juego de caracteres que soporta caracteres occidentales y es más eficiente en términos de almacenamiento para textos en idiomas como el inglés, español, francés, etc.
ascii: Es un juego de caracteres que soporta solo caracteres ASCII (caracteres básicos en inglés). Es el más eficiente en términos de almacenamiento, pero no es adecuado para textos en otros idiomas.

Una collation es un conjunto de reglas que determinan cómo se comparan y ordenan los caracteres en una base de datos. Cada juego de caracteres puede tener varias collations asociadas, que definen diferentes formas de comparar y ordenar los caracteres. Algunas collations comunes en MySQL son:

utf8mb4_general_ci: Es una collation que realiza comparaciones y ordenaciones de manera insensible a mayúsculas y minúsculas (case-insensitive) y no distingue entre acentos (accent-insensitive).
utf8mb4_bin: Es una collation que realiza comparaciones y ordenaciones basadas en el valor binario de los caracteres, lo que significa que es sensible a mayúsculas y minúsculas (case-sensitive) y distingue entre acentos (accent-sensitive).

Nota

Por defecto, MySQL utiliza el juego de caracteres latin1 y la collation latin1_swedish_ci. Sin embargo, es recomendable utilizar utf8mb4 y una collation adecuada para garantizar la compatibilidad con una amplia gama de caracteres y evitar problemas de codificación.

La siguiente sentencia SQL crea una base de datos llamada mi_base_de_datos con el juego de caracteres utf8mb4 y la collation utf8mb4_general_ci, y selecciona dicha base de datos para su uso:

CREATE DATABASE mi_base_de_datos
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci;

USE mi_base_de_datos;

Tablas

Creación de tablas

Para crear una tabla en una base de datos, se utiliza el comando CREATE TABLE. La sintaxis básica para crear una tabla es la siguiente:

CREATE [OR REPLACE] TABLE [base_de_datos.]nombre_tabla (
    columna1 tipo_dato1 [opciones],
    columna2 tipo_dato2 [opciones],
    ...
    [restricciones_tabla]
);

Por ejemplo, para crear una tabla llamada clientes con la columna nombre, se utilizaría la siguiente instrucción SQL:

CREATE TABLE clientes (
    nombre VARCHAR(100)
);

Debemos tener algunas consideraciones al crear tablas:

Nombres de tablas: Los nombres de tablas deben ser únicos dentro de una base de datos. No se deben utilizar espacios ni caracteres especiales en los nombres de tablas. Ademas, no se deben utilizar palabras reservadas de SQL como nombres de tablas. Es recomendable utilizar nombres descriptivos y consistentes para facilitar la comprensión y el mantenimiento de la base de datos

Algunos ejemplos de creacion de tablas con diferentes tipos de datos y restricciones:

CREATE TABLE productos (
    id_producto INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100) NOT NULL,
    precio DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    fecha_creacion DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE empleados (
    id_empleado INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    fecha_contratacion DATE NOT NULL,
    salario DECIMAL(10,2) CHECK (salario >= 0)
);

Las restricciones se detallan más adelante en la unidad.

Si queremos ver la estructura de una tabla, podemos usar el comando DESCRIBE:

DESCRIBE empleados;

Claves primaria

Una clave primaria (Primary Key, PK) es una columna o conjunto de columnas que identifica de manera única cada fila en una tabla. Las claves primarias tienen las siguientes características:

Unicidad: Cada valor de clave primaria debe ser único en la tabla. No puede haber dos filas con el mismo valor de clave primaria.
No nulo: Los valores de clave primaria no pueden ser NULL. Cada fila debe tener un valor válido para la clave primaria.
Índice: Las claves primarias crean automáticamente un índice único en la columna o columnas que las componen, lo que mejora el rendimiento de las consultas que utilizan la clave primaria.
Inmutabilidad: Los valores de clave primaria no deben cambiar una vez asignados. Cambiar el valor de una clave primaria puede causar problemas de integridad referencial en la base de datos.
Simplicidad: Es recomendable que las claves primarias sean simples y fáciles de manejar. Evitar el uso de claves compuestas (claves primarias que consisten en múltiples columnas) a menos que sea absolutamente necesario.
Auto-incremento: En muchos casos, las claves primarias se definen como columnas de tipo entero con la opción AUTO_INCREMENT, lo que permite que el SGBD genere automáticamente un valor único para cada nueva fila insertada en la tabla.
Elección del tipo de dato: Es importante elegir un tipo de dato adecuado para la clave primaria, considerando el rango de valores que se espera almacenar y el tamaño del almacenamiento. Por ejemplo, si se espera almacenar un gran número de filas, es recomendable utilizar BIGINT en lugar de INT.

Existen muchas formas de definir una clave primaria en una tabla. Dependiendo de dónde se coloque la restricción PRIMARY KEY, la sintaxis puede variar ligeramente. A continuación, se muestran algunos ejemplos de cómo definir una clave primaria en una tabla:

CREATE TABLE clientes (
    id_cliente INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100) NOT NULL
);

CREATE TABLE productos (
    id_producto INT NOT NULL AUTO_INCREMENT,
    nombre VARCHAR(100) NOT NULL,
    precio DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id_producto)
);

CREATE TABLE empleados (
    id_empleado INT,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    fecha_contratacion DATE NOT NULL,
    salario DECIMAL(10,2) CHECK (salario >= 0),
    CONSTRAINT pk_empleados PRIMARY KEY (id_empleado)
);

CREATE TABLE ordenes (
    id_orden INT,
    id_cliente INT,
    fecha DATETIME NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id_orden, id_cliente) -- Clave primaria compuesta
);

Todas las formas son correctas y producen el mismo resultado. La elección de una u otra forma depende de las preferencias personales y del estilo de codificación utilizado en el proyecto.

Modificación de tablas

Para modificar la estructura de una tabla existente, se utiliza el comando ALTER TABLE. La sintaxis básica para modificar una tabla es la siguiente:

ALTER TABLE [base_de_datos.]nombre_tabla
    {ADD columna tipo_dato [opciones] [FIRST | AFTER nombreColumna] |
     DROP columna |
     MODIFY columna tipo_dato [opciones] |

     ADD CONSTRAINT restricción_tabla};

Si queremos añadir una columna a una tabla usaremos ADD, por ejemplo:

ALTER TABLE clientes
    ADD email VARCHAR(100);

FIRST y AFTER se utilizan para especificar la posición de la nueva columna en la tabla. Si no se especifica ninguna de estas opciones, la nueva columna se añadirá al final de la tabla.

Si lo que queremos es modificar una columna existente, usaremos MODIFY, por ejemplo:

ALTER TABLE clientes
    MODIFY nombre VARCHAR(150);

Nota

Modificar una columna implica volver a definir el tipo de dato y las opciones de la columna.

En el caso de que queramos eliminar una columna, usaremos DROP, por ejemplo:

ALTER TABLE clientes
    DROP email;

Podemos cambiar el nombre de una tabla utilizando RENAME TO, por ejemplo:

ALTER TABLE [base_de_datos.]nombre_tabla
    RENAME TO nuevo_nombre_tabla;

Para cambiar el nombre de una columna, usaremos CHANGE, por ejemplo:

ALTER TABLE clientes
    CHANGE email email_nuevo VARCHAR(100);

Nota

En el comando CHANGE, es necesario especificar el nombre actual de la columna seguido del nuevo nombre y el tipo de dato.

Borrado de tablas

Para eliminar una tabla de una base de datos, se utiliza el comando DROP TABLE. La sintaxis básica para eliminar una tabla es la siguiente:

DROP TABLE [IF EXISTS] [base_de_datos.]nombre_tabla;

Como el borrado de una tabla implica la eliminación de los registros, debemos tener en cuenta que en una base de datos relacional, las tablas pueden estar relacionadas entre sí mediante claves foráneas. Si intentamos eliminar una tabla que está siendo referenciada por otra tabla a través de una clave foránea, el SGBD puede impedir la eliminación para mantener la integridad referencial. En estos casos, es necesario eliminar primero las relaciones de clave foránea antes de poder eliminar la tabla.

Restricciones

Las restricciones son reglas que se aplican a las columnas de una tabla para garantizar la integridad y validez de los datos almacenados. Este tipo de restricciones se definen al momento de crear o modificar una tabla. A continuación, se describen algunas de las restricciones más comunes mediante la sintaxis SQL:

CREATE TABLE [db.]tabla (
    campo1 tipo_dato1 [NOT NULL | NULL] [DEFAULT valor],
    ...,

    [CONSTRAINT nombre_restriccion]
    [ENGINE = Innodb] -- motor de ejecución
);

Las restricciones pueden aplicarse a nivel de columna o a nivel de tabla. A nivel de columna, las restricciones se definen directamente en la definición de la columna. A nivel de tabla, las restricciones se definen al final de la definición de la tabla utilizando la palabra clave CONSTRAINT.

Recomendación

Para mantener una estructura clara y legible, usaremos la definición de restricciones:

a nivel de columna, para restricciones simples como DEFAULT, AUTO_INCREMENT y NOT NULL.
mediante CONSTRAINT, para restricciones más complejas como PRIMARY KEY, FOREIGN KEY, CHECK y UNIQUE.

Clave única

La restricción UNIQUE se utiliza para garantizar que los valores en una columna o conjunto de columnas sean únicos en la tabla. Esto significa que no puede haber dos filas con el mismo valor en la columna o combinación de columnas que tienen la restricción UNIQUE. A diferencia de una clave primaria, una tabla puede tener múltiples restricciones UNIQUE y se permiten valores NULL en las columnas con esta restricción.

CREATE TABLE usuarios (
    id_usuario INT AUTO_INCREMENT,
    nombre VARCHAR(100),
    email VARCHAR(100) UNIQUE,

    CoNSTRAINT pk_usuarios PRIMARY KEY (id_usuario),
    CONSTRAINT uq_email UNIQUE (email) -- El email debe ser único
);

La creación de un campo con la restricción UNIQUE crea automáticamente un índice único en esa columna, lo que mejora el rendimiento de las consultas que buscan valores únicos.

Clave foránea o ajena

Una clave foránea (Foreign Key, FK) es una columna o conjunto de columnas en una tabla que establece una relación con la clave primaria de otra tabla. Las claves foráneas se utilizan para mantener la integridad referencial entre dos tablas, asegurando que los valores en la columna de la clave foránea correspondan a valores existentes en la columna de la clave primaria de la tabla referenciada.

Aunque puede definirse de varias formas, la sintaxis básica que usaremos para definir una clave foránea es la siguiente:

CONSTRAINT nombre_restriccion FOREIGN KEY (columna_fk)
    REFERENCES tabla_referenciada (columna_pk)
        [ON DELETE {CASCADE | SET NULL | NO ACTION | SET DEFAULT}] -- acción al borrar
        [ON UPDATE {CASCADE | SET NULL | NO ACTION | SET DEFAULT}] -- acción al actualizar

Por ejemplo, si tenemos una tabla ordenes que contiene una columna id_cliente que hace referencia a la columna id_cliente en la tabla clientes, podemos definir la clave foránea de la siguiente manera:

CREATE TABLE ordenes (
    id_orden INT AUTO_INCREMENT,
    id_cliente INT,
    fecha DATETIME,
    total DECIMAL(10,2),

    CONSTRAINT pk_ordenes PRIMARY KEY (id_orden),
    CONSTRAINT fk_cliente FOREIGN KEY (id_cliente) REFERENCES clientes (id_cliente)
);

Propagación de acciones

Las acciones ON DELETE y ON UPDATE definen el comportamiento que se debe seguir cuando se elimina o actualiza un registro en la tabla referenciada. Las opciones disponibles son:

CASCADE: Si se elimina o actualiza un registro en la tabla referenciada, se eliminarán o actualizarán automáticamente los registros correspondientes en la tabla que contiene la clave foránea.
SET NULL: Si se elimina o actualiza un registro en la tabla referenciada, los valores de la clave foránea en la tabla que contiene la clave foránea se establecerán en NULL.
NO ACTION: No se permite la eliminación o actualización del registro en la tabla referenciada si existen registros relacionados en la tabla que contiene la clave foránea. Esta es la opción predeterminada si no se especifica ninguna acción.
SET DEFAULT: Si se elimina o actualiza un registro en la tabla referenciada, los valores de la clave foránea en la tabla que contiene la clave foránea se establecerán en su valor por defecto.
RESTRICT: Similar a NO ACTION, pero la verificación se realiza inmediatamente cuando se intenta eliminar o actualizar el registro en la tabla referenciada.

Nota

La diferencia principal entre NO ACTION y RESTRICT es el momento en que se realiza la verificación de integridad referencial. Con NO ACTION, la verificación se realiza al final de una transacción, mientras que con RESTRICT, la verificación se realiza inmediatamente.

Supongamos el siguiente escenario:

CREATE TABLE estudiantes (
    dni CHAR(9),
    nombre VARCHAR(100) NOT NULL,

    CONSTRAINT pk_estudiante PRIMARY KEY (dni)
);

CREATE TABLE matriculas (
    dni_estudiante CHAR(9),
    asignatura VARCHAR(100),
    curso INT,

    CONSTRAINT pk_matricula PRIMARY KEY (dni_estudiante, asignatura, curso),
    CONSTRAINT fk_estudiante FOREIGN KEY (dni_estudiante) REFERENCES estudiantes (dni)
);

Donde la tabla estudiantes contiene:

dni	nombre
12345678A	Juan Pérez
87654321B	María Gómez
11223344C	Luis Fernández

y la tabla matriculas contiene:

dni_estudiante	asignatura	curso
12345678A	Matemáticas	1
12345678A	Física	1
87654321B	Química	1

Si intentamos eliminar el estudiante con DNI 12345678A, MySQL nos devolverá un error de integridad referencial, ya que existen matrículas asociadas a ese estudiante. Para eliminar el estudiante y todas sus matrículas asociadas, podemos definir la clave foránea con la opción ON DELETE CASCADE.

De igual manera, si queremos que al actualizar el DNI de un estudiante, se actualicen automáticamente todas las matrículas asociadas a ese estudiante, podemos utilizar la opción ON UPDATE CASCADE. La definición de la tabla matriculas quedaría de la siguiente manera:

CREATE TABLE matriculas (
    dni_estudiante CHAR(9),
    asignatura VARCHAR(100),
    curso INT,

    CONSTRAINT pk_matricula PRIMARY KEY (dni_estudiante, asignatura, curso),
    CONSTRAINT fk_estudiante FOREIGN KEY (dni_estudiante) REFERENCES estudiantes (dni)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

¿Es posible establecer como ON DELETE SET NULL la restricción fk_estudiante? ¿Por qué?

No, no es posible establecer ON DELETE SET NULL en la restricción fk_estudiante porque la columna dni_estudiante en la tabla matriculas es parte de la clave primaria y no puede contener valores nulos.

Validación de datos

Las restricciones CHECK se utilizan para validar los datos que se insertan o actualizan en una tabla. Estas restricciones permiten definir condiciones que los valores de una columna o conjunto de columnas deben cumplir para ser aceptados en la tabla. Si un valor no cumple con la condición definida en la restricción CHECK, el SGBD rechazará la operación de inserción o actualización y devolverá un error.

CREATE TABLE empleados (
    id_empleado INT AUTO_INCREMENT,
    nombre VARCHAR(100),
    email VARCHAR(100),
    fecha_contratacion DATE,
    salario DECIMAL(10,2),

    CONSTRAINT pk_empleados PRIMARY KEY (id_empleado),
    CONSTRAINT ck_salario CHECK (salario >= 0) -- El salario debe ser mayor o igual a 0
);

De igual forma, podemos usar la restricción de verificación para comparar valores entre varias columnas:

CREATE TABLE productos (
    id_producto INT AUTO_INCREMENT,
    nombre VARCHAR(100),
    precio DECIMAL(10,2),
    stock INT,

    CONSTRAINT pk_empleados PRIMARY KEY (id_producto),
    -- El precio y el stock deben ser mayores o iguales a 0
    CONSTRAINT chk_precio_stock CHECK (precio >= 0 AND stock >= 0) 
);

Entre un rango (BETWEEN)

La cláusula BETWEEN se utiliza en las restricciones CHECK para verificar si un valor se encuentra dentro de un rango específico. La sintaxis básica para utilizar BETWEEN en una restricción CHECK es la siguiente:

CONSTRAINT nombre_restriccion CHECK (columna BETWEEN valor_inferior AND valor_superior)

Ambos valores, el inferior y el superior, son inclusivos. Por ejemplo, si queremos asegurarnos de que la columna edad en una tabla personas esté entre 0 y 120, podemos definir la restricción CHECK de la siguiente manera:

CREATE TABLE personas (
    id_persona INT AUTO_INCREMENT,
    nombre VARCHAR(100),
    edad INT,

    CONSTRAINT pk_personas PRIMARY KEY (id_persona),
    CONSTRAINT chk_edad CHECK (edad BETWEEN 0 AND 120) -- La edad debe estar entre 0 y 120
);

Conjunto de valores (IN)

La cláusula IN se utiliza en las restricciones CHECK para verificar si un valor pertenece a un conjunto específico de valores. La sintaxis básica para utilizar IN en una restricción CHECK es la siguiente:

CONSTRAINT nombre_restriccion CHECK (columna IN (valor1, valor2, valor3, ...))

Por ejemplo, si queremos asegurarnos de que la columna genero en una tabla usuarios solo contenga los valores 'M' (masculino), 'F' (femenino) o 'O' (otro), podemos definir la restricción CHECK de la siguiente manera:

CREATE TABLE usuarios (
    id_usuario INT AUTO_INCREMENT,
    nombre VARCHAR(100),
    genero CHAR(1),

    CONSTRAINT pk_usuarios PRIMARY KEY (id_usuario),
    CONSTRAINT chk_genero CHECK (genero IN ('M', 'F', 'O')) -- El género debe ser 'M', 'F' o 'O'
);

NULL y NOT NULL

La propiedades NULL y NOT NULL se utilizan para controlar si una columna puede contener valores NULL o no.

NOT NULL se utiliza para garantizar que una columna no pueda contener valores NULL. Esto significa que cada fila en la tabla debe tener un valor válido para esa columna. Si se intenta insertar o actualizar una fila con un valor NULL en una columna que tiene la restricción NOT NULL, el SGBD rechazará la operación y devolverá un error.

CREATE TABLE clientes (
    id_cliente INT  AUTO_INCREMENT,
    nombre VARCHAR(100) NOT NULL, -- El nombre no puede ser NULL
    email VARCHAR(100) NOT NULL, -- El email no puede ser NULL

    CONSTRAINT pk_clientes PRIMARY KEY (id_cliente)
);

Por defecto, las columnas permiten valores NULL, por lo que si no se especifica la restricción NOT NULL, la columna podrá contener valores NULL.

Gestión de restricciones

Para añadir o borrar una restricción en una tabla , se utiliza el comando ALTER TABLE junto con la cláusula ADD CONSTRAINT y DROP CONSTRAINT.

La sintaxis básica para añadir una restricción es la siguiente:

ALTER TABLE nombre_tabla
    ADD CONSTRAINT nombre_restriccion tipo_restriccion (columnas);

La sintaxis básica para eliminar una restricción es la siguiente:

ALTER TABLE nombre_tabla
    DROP CONSTRAINT nombre_restriccion;

Si queremos ver las restricciones de una tabla, podemos usar el comando SHOW CREATE TABLE:

SHOW CREATE TABLE nombre_tabla;

Advertencia

Si añadimos una restricción tipo CHECK con datos existentes en la tabla, MySQL NO validará los datos existentes. Solo se validarán los nuevos datos que se inserten o actualicen después de añadir la restricción.

Por otro lado, si añadimos una restricción tipo UNIQUE, NULL, NOT NULL, PRIMARY KEY o FOREIGN KEY con datos existentes en la tabla, MySQL validará TODOS los datos existentes. Si algún dato no cumple con la restricción, MySQL devolverá un error y no se añadirá la restricción.

Vistas

Una vista es una tabla virtual que se crea a partir de una consulta SQL. Las vistas permiten simplificar consultas complejas, mejorar la seguridad al restringir el acceso a ciertos datos y proporcionar una capa de abstracción sobre las tablas subyacentes.

Las vistas se crean utilizando el comando CREATE VIEW. La sintaxis básica para crear una vista es la siguiente:

CREATE VIEW nombre_vista AS
    consulta_sql;

Por ejemplo, si tenemos una tabla empleados y queremos crear una vista que muestre solo los nombres y correos electrónicos de los empleados, podemos hacerlo de la siguiente manera:

CREATE VIEW vista_empleados AS
    SELECT nombre, email
    FROM empleados;

Dado que no se han estudiando las consultas SQL, regresaremos a las vistas en unidades posteriores.

DCL: Data Control Language

El lenguaje de control de datos (DCL) se utiliza para gestionar los permisos y privilegios de los usuarios en una base de datos.

Usuarios

En un sistema de gestión de bases de datos (SGBD) como MySQL, los usuarios son entidades que tienen acceso a la base de datos y pueden realizar diversas operaciones según los privilegios que se les hayan otorgado.

Para crear un nuevo usuario en MySQL, se utiliza el comando CREATE USER. La sintaxis básica para crear un usuario es la siguiente:

CREATE USER 'nombre_usuario'@'host' IDENTIFIED BY 'contraseña';

El host especifica desde qué host o dirección IP el usuario puede conectarse al servidor de base de datos. Por ejemplo, si queremos crear un usuario llamado usuario1 que pueda conectarse desde cualquier host, utilizaremos el símbolo % como comodín:

CREATE USER 'usuario1'@'%' IDENTIFIED BY 'mi_contraseña_segura';

Si queremos crear un usuario que solo pueda conectarse desde el mismo servidor donde está alojada la base de datos, utilizaremos localhost como host:

CREATE USER 'usuario2'@'localhost' IDENTIFIED BY 'otra_contraseña_segura';

Información

Podemos ver los usuarios existentes en MySQL utilizando la siguiente consulta SQL:

SELECT user, host FROM mysql.user;

Si queremos cambiar la contraseña de un usuario existente, se utiliza el comando ALTER USER. La sintaxis básica para cambiar la contraseña de un usuario es la siguiente:

ALTER USER 'nombre_usuario'@'localhost' IDENTIFIED BY 'nueva_contraseña';

Y por último, para eliminar un usuario, se utiliza el comando DROP USER. La sintaxis básica para eliminar un usuario es la siguiente:

DROP USER 'nombre_usuario'@'localhost';

Advertencia

Cuando trabajamos con usuario siempre debemos especificar el host (aunque se puede omitir en algunos casos). En MySQL, un usuario se identifica de manera única por la combinación de su nombre y el host desde el cual se conecta. Por ejemplo, usuario1@localhost y usuario1@'%' son dos usuarios diferentes.

Privilegios

Los privilegios son permisos específicos que controlan qué acciones puede realizar un usuario en la base de datos.

Los privilegios pueden ser otorgados a nivel global (para todas las bases de datos), a nivel de base de datos (para una base de datos específica), a nivel de tabla (para una tabla específica) o a nivel de columna (para una columna específica).

Los privilegios más comunes en MySQL son:

SELECT[(columna)]: Permite leer datos de una tabla. Si se especifica una columna, el privilegio solo se aplica a esa columna.
INSERT: Permite insertar nuevos datos en una tabla.
UPDATE[(columna)]: Permite modificar datos existentes en una tabla. Si se especifica una columna, el privilegio solo se aplica a esa columna.
DELETE: Permite eliminar datos de una tabla.
CREATE: Permite crear nuevas bases de datos y tablas.
DROP: Permite eliminar bases de datos y tablas.
ALTER: Permite modificar la estructura de tablas existentes.
ALL PRIVILEGES: Otorga todos los privilegios disponibles.

Para otorgar privilegios a un usuario, se utiliza el comando GRANT. La sintaxis básica para otorgar privilegios es la siguiente:

GRANT privilegio1, privilegio2, ...
    ON [base_de_datos.]tabla
    TO 'nombre_usuario'@'localhost' [WITH GRANT OPTION];

La cláusula WITH GRANT OPTION permite al usuario otorgar los mismos privilegios a otros usuarios. Es recomendable utilizar esta opción con precaución, ya que puede llevar a una escalada de privilegios no deseada.

GRANT SELECT, INSERT
    ON empresa.empleados
    TO 'nombre_usuario'@'localhost' WITH GRANT OPTION;

Y si queremos otorgar privilegios para un base de datos completa:

GRANT ALL PRIVILEGES
    ON empresa.*
    TO 'nombre_usuario'@'localhost';

Si queremos ver los privilegios otorgados a un usuario, se utiliza el comando SHOW GRANTS. La sintaxis básica para ver los privilegios de un usuario es la siguiente:

SHOW GRANTS FOR 'nombre_usuario'@'localhost';

Por último, para revocar privilegios de un usuario, se utiliza el comando REVOKE. La sintaxis básica para revocar privilegios es la siguiente:

REVOKE privilegio1, privilegio2, ...
    ON [base_de_datos.]tabla
    FROM 'nombre_usuario'@'localhost';

REVOKE INSERT
    ON empresa.empleados
    FROM 'nombre_usuario'@'localhost';

Roles

Un rol es un conjunto de privilegios que se pueden asignar a uno o más usuarios. Los roles facilitan la gestión de privilegios al permitir agrupar varios privilegios bajo un solo nombre.

Para crear un rol en MySQL, se utiliza el comando CREATE ROLE. La sintaxis básica para crear un rol es la siguiente:

CREATE ROLE 'nombre_rol';

Por ejemplo, para crear un rol llamado gestores, utilizaremos la siguiente instrucción SQL:

CREATE ROLE 'gestores';

Para otorgar privilegios a un rol, se utiliza el comando GRANT de la misma manera que se hace con los usuarios. La sintaxis básica para otorgar privilegios a un rol es la siguiente:

GRANT privilegio1, privilegio2, ...
    ON [base_de_datos.]tabla
    TO 'nombre_rol';

Para asignar un rol a un usuario, se utiliza el comando GRANT como si fuera un privilegio:

GRANT 'nombre_rol' TO 'nombre_usuario'@'localhost';

Si queremos ver los permisos de un rol, se utiliza el comando SHOW GRANTS. La sintaxis básica para ver los privilegios de un rol es la siguiente:

SHOW GRANTS FOR 'nombre_rol';

y finalmente, para eliminar un rol, se utiliza el comando DROP ROLE. La sintaxis básica para eliminar un rol es la siguiente:

DROP ROLE 'nombre_rol';

Para revocar un rol de un usuario, se utiliza el comando REVOKE. La sintaxis básica para revocar un rol de un usuario es la siguiente:

REVOKE 'nombre_rol'
    FROM 'nombre_usuario'@'localhost';

Actividades

A2.1 FLOAT vs DECIMAL

Utilizando la aplicación web myCompiler, visualiza la perdida de precisión al usar el tipo de dato FLOAT en lugar de DECIMAL para representar números con decimales.:

CREATE TABLE demo (
    valor_float  FLOAT,
    valor_decimal DECIMAL(20,10)
);

INSERT INTO demo VALUES (0.1 + 0.2, 0.1 + 0.2);

SELECT 
    valor_float, 
    CAST(valor_float AS DECIMAL(20,18)) AS valor_float_extendido,
    -- CAST se usa para forzar el tipo de dato introducido a FLOAT
    valor_decimal
FROM demo;

A2.2 Tipo de datos

Elige el tipo de datos mas adecuado de MySQL para cada una de las siguientes situaciones:

Almacenar la edad de una persona.
Almacenar el nombre completo de una persona.
Almacenar el precio de un producto con dos decimales.
Almacenar la fecha y hora de un evento.
Almacenar el estado (activo/inactivo) de un usuario.
Almacenar la posición en un ranking de 1 a 10.
Almacenar una descripción larga de un producto.
Almacenar el número de identificación de un empleado.

A2.3 Instalación de MySQL

Instala MySQL Community Server y MySQL Workbench en tu ordenador siguiendo las instrucciones que aparecen en la página oficial. Configura una contraseña segura para el usuario root durante la instalación. Una vez instalado, abre MySQL Workbench e intenta conectarte a tu servidor MySQL local utilizando el usuario root y la contraseña que has configurado.

A2.4 Crear base de datos

Crea una base de datos llamada empresa con el juego de caracteres utf8mb4 y la collation utf8mb4_general_ci. Selecciona dicha base de datos para su uso. Muestra las bases de datos existentes y verifica que la base de datos empresa ha sido creada correctamente.

A2.5 Clave primaria única vs compuesta

Define dos situaciones en la que seria mas adecuado usar una clave primaria compuesta en lugar de una clave primaria única. Justifica tu respuesta.
Define las tablas con las claves primarias adecuadas para cada situación.

A2.6 Crear tablas

Crea la base de datos cine con las siguientes tablas:

peliculasactorespeliculas_actores

id_pelicula: Clave primaria, entero, auto-incremento.
titulo: Cadena de texto.
director: Cadena de texto.
fecha_estreno: Fecha (Sin hora).
duracion: Entero (minutos).

id_actor: Clave primaria, entero, auto-incremento.
nombre: Cadena de texto.
fecha_nacimiento: Fecha (Sin hora).
nacionalidad: Cadena de texto.
genero: Cadena de texto (M|F).

Tabla intermedia para la relación muchos a muchos entre películas y actores:

id_pelicula
id_actor
Clave primaria compuesta por (id_pelicula, id_actor).

A2.7 Modificar tablas

Modifica las tablas creadas en la actividad anterior para añadir las siguientes columnas:

Añade una columna genero a la tabla peliculas para almacenar el género de la película (cadena de texto).
Añade una columna email a la tabla actores para almacenar el correo electrónico del actor (cadena de texto, único) después de la columna nombre.
Añade una columna fecha_registro a la tabla actores para almacenar la fecha y hora en que el actor fue registrado en la base de datos (fecha y hora).
Añade una columna rol a la tabla peliculas_actores para almacenar el rol del actor en la película (cadena de texto).
Elimina la columna nacionalidad de la tabla actores.
Modifica la columna duracion de la tabla peliculas para que sea de tipo FLOAT en lugar de INT.
Añade una columna rating a la tabla peliculas para almacenar la calificación de la película (decimal con 2 decimales) al final de la tabla.
Cambia el nombre de la tabla peliculas_actores a cast.
Muestra la estructura de las tres tablas para verificar que los cambios se han realizado correctamente.

A2.8 Borrar tablas

Borra las tablas creadas en la actividad A2.6 en el orden correcto para evitar problemas de integridad referencial.

A2.9 NetflixDB

Netflix quiere crear una base de datos para almacenar información sobre su catálogo de películas, los actores que participan en ellas, los usuarios que las valoran y las valoraciones que reciben.

Se necesita crear cuatro tablas principales:

Películas
Actores
Usuarios
Valoraciones

Tu tarea es definir el tipo de dato adecuado para cada campo y añadir restricciones apropiadas (PK, FK, UNIQUE, CHECK, NOT NULL, DEFAULT).

A continuación se muestran ejemplos de los datos que contendrá cada tabla.

PeliculasActoresUsuariosValoraciones

id	titulo	año_estreno	duracion_min	categoria
1	El Irlandés	2019	209	Drama
2	Stranger Things: Temporada 1	2016	50	Ciencia Ficción
3	Roma	2018	135	Drama

id	nombre	nacionalidad	fecha_nacimiento
1	Robert De Niro	Estados Unidos	1943-08-17
2	Millie Bobby Brown	Reino Unido	2004-02-19
3	Yalitza Aparicio	México	1993-12-11

id	nombre_usuario	email	pais	fecha_registro
1	juan23	juan23@gmail.com	España	2022-05-14
2	laura_mx	laura_mx@hotmail.com	México	2023-01-10
3	alex_usa	alex_usa@yahoo.com	Estados Unidos	2021-12-30

id	usuario_id	pelicula_id	puntuacion	comentario	fecha_valoracion
1	1	1	5	Excelente película	2023-04-12
2	2	3	4	Muy buena historia	2023-07-22
3	3	2	3	Un poco lenta	2024-02-10

Ten en cuenta las siguientes consideraciones:

Todos los id son auto-incrementales.
La puntuación debe estar entre 1 y 5.
El email del usuario debe ser único.
La duración de la película debe ser un valor positivo.
No se se permiten valores nulos.
Un usuario puede valorar una misma película solo una vez.
En la tabla de valoraciones, cuando se borre un usuario o una película, se deben borrar todas las valoraciones asociadas a dicho usuario o película.
Pais tiene un valor por defecto "Desconocido".
Categoria solo puede tener los valores "Drama", "Comedia", "Acción", "Ciencia Ficción" o "Documental".
El año de estreno debe ser mayor a 1900.

Una vez definidas las tablas, realiza las siguientes modificaciones:

Añade una columna director a la tabla peliculas para almacenar el nombre del director (cadena de texto).
Añade una columna genero a la tabla actores para almacenar el género del actor (M|F).
Añade una columna fecha_nacimiento a la tabla usuarios para almacenar la fecha de nacimiento del usuario (fecha sin hora).
Modifica la columna duracion_min de la tabla peliculas para que sea de tipo FLOAT en lugar de INT.
Cambia el nombre de la tabla valoraciones a reviews.
Muestra la estructura de las cuatro tablas para verificar que los cambios se han realizado correctamente.
Borra las tablas creadas en el orden correcto para evitar problemas de integridad referencial.

A2.10 Gestión de usuarios y privilegios

Crea un usuario llamado analista que pueda conectarse desde cualquier host con la contraseña Analista2025!.
Otorga al usuario analista los privilegios SELECT y INSERT en todas las tablas de la base de datos empresa.
Crea un rol llamado reportes y otórgale el privilegio SELECT en todas las tablas de la base de datos empresa.
Asigna el rol reportes al usuario analista.
Muestra los privilegios otorgados al usuario analista y al rol reportes.
Revoca el privilegio INSERT del usuario analista en la base de datos empresa.
Conéctate a MySQL Workbench utilizando el usuario analista.
Elimina el rol reportes y el usuario analista.
Verifica que el rol y el usuario han sido eliminados correctamente.
Conectate a MySQL usando la terminal o línea de comandos con el usuario root.