Fundamentos y Comandos Básicos de MySQL

MySQL es uno de los sistemas de gestión de bases de datos relacionales (RDBMS) de código abierto más populares. Este documento explora los conceptos fundamentales, la sintaxis SQL y los comandos esenciales para interactuar con MySQL, cubriendo desde la definición de datos hasta la manipulación y consulta.

Conceptos Clave de Bases de Datos

Para comprender MySQL, es crucial familiarizarse con algunos términos fundamentales:

Término	Descripción Completa	Abreviatura
Base de Datos	Almacén organizado para persistir datos de manera estructurada.	DB
Sistema de Gestión de Bases de Datos	Software encargado de administrar y manipular las bases de datos.	DBMS
SQL	Lenguaje de programación estandarizado para operar bases de datos relacionales.	Structured Query Language

Conexión a un Servidor MySQL

Para interactuar con una instancia de MySQL, se utiliza el cliente de línea de comandos. El formato general para la conexión es el siguiente:

mysql -h [dirección_ip_servidor] -P [puerto] -u [usuario] -p

Por ejemplo, para conectarse a un servidor local con el usuario 'root' y el puerto predeterminado:

mysql -h 127.0.0.1 -P 3306 -u root -p

Después de ejecutar el comando, se le solicitará la contraseña.

Bases de Datos Relacionales (RDBMS)

Un RDBMS se basa en el modelo relacional, donde los datos se organizan en tablas interconectadas. Sus principales características incluyen:

Almacenamiento de datos en tablas con un formato uniforme, facilitando el mantenimiento.
Manipulación mediante SQL, un lenguaje estandarizado y fácil de usar.

Sintaxis General de SQL

Al escribir sentencias SQL en MySQL, siga estas pautas:

Las sentencias pueden abarcar una o varias líneas y deben terminar con un punto y coma (;).
El uso de espacios y sangrías mejora la legibilidad.
MySQL no distingue entre mayúsculas y minúsculas para las palabras clave SQL, aunque se recomienda usar mayúsculas para estas.
Los comentarios se pueden añadir de las siguientes formas:
- Línea única: -- comentario o # comentario (específico de MySQL).
- Múltiples líneas: /* comentario de varias líneas */.

Clasificación de Sentencias SQL

Categoría	Nombre Completo	Propósito
DDL	Data Definition Language	Define objetos de la base de datos (bases de datos, tablas, columnas).
DML	Data Manipulation Language	Inserta, actualiza y elimina datos en las tablas.
DQL	Data Query Language	Consulta registros en las tablas de la base de datos.
DCL	Data Control Language	Gestiona usuarios y sus permisos de acceso a la base de datos.

Sentencias DDL (Data Definition Language)

Operaciones con Bases de Datos

1. Consultar Bases de Datos:

Listar todas las bases de datos disponibles: ``` SHOW DATABASES;
Mostrar la base de datos actualmente en uso: ``` SELECT DATABASE();

2. Crear una Base de Datos:

CREATE DATABASE [IF NOT EXISTS] nombre_base_datos [DEFAULT CHARACTER SET juego_caracteres] [COLLATE regla_ordenamiento];

Es común usar utf8mb4 como juego de caracteres predeterminado. La cláusula IF NOT EXISTS previene un error si la base de datos ya existe.

3. Eliminar una Base de Datos:

DROP DATABASE [IF EXISTS] nombre_base_datos;

La cláusula IF EXISTS previene un error si la base de datos no existe.

4. Seleccionar una Base de Datos para Usar:

USE nombre_base_datos;

Operaciones con Tablas - Consultas

1. Listar todas las tablas en la base de datos actual:

SHOW TABLES;

2. Describir la estructura de una tabla:

DESC nombre_tabla;

3. Mostrar la sentencia CREATE TABLE para una tabla específica:

SHOW CREATE TABLE nombre_tabla;

Operaciones con Tablas - Creación

La sentencia CREATE TABLE define una nueva tabla con sus columnas y tipos de datos:

CREATE TABLE nombre_tabla (
   columna1 tipo_dato [COMMENT 'comentario_columna1'],
   columna2 tipo_dato [COMMENT 'comentario_columna2'],
   ...
   columnaN tipo_dato [COMMENT 'comentario_columnaN']
   ) [COMMENT 'comentario_tabla'];

Tenga en cuenta que no hay coma después de la última definición de columna.

Ejemplo: Crear una tabla para usuarios de TI.

CREATE TABLE usuarios_ti (
       id INT COMMENT 'Identificador único',
       nombre VARCHAR(50) COMMENT 'Nombre del usuario',
       edad INT COMMENT 'Edad del usuario',
       genero CHAR(1) COMMENT 'Género del usuario'
   ) COMMENT 'Tabla de usuarios de TI';

Tipos de Datos de Columna

MySQL ofrece varios tipos de datos para diferentes necesidades:

Tipos Numéricos:

Tipo	Descripción
TINYINT	Entero muy pequeño (-128 a 127 o 0 a 255 UNSIGNED).
SMALLINT	Entero pequeño.
MEDIUMINT	Entero de tamaño medio.
INT	Entero estándar.
BIGINT	Entero grande.
FLOAT	Número de punto flotante de precisión simple.
DOUBLE	Número de punto flotante de precisión doble.
DECIMAL(M,D)	Número decimal preciso (M total de dígitos, D decimales).

Tipos de Cadena:

Tipo	Descripción
CHAR(L)	Cadena de longitud fija (L caracteres), se rellena con espacios.
VARCHAR(L)	Cadena de longitud variable (hasta L caracteres).
TINYTEXT	Texto muy pequeño (hasta 255 caracteres).
TEXT	Texto pequeño (hasta 65,535 caracteres).
MEDIUMTEXT	Texto mediano (hasta 16 MB).
LONGTEXT	Texto grande (hasta 4 GB).
ENUM('v1','v2',...)	Enumeración, un valor de una lista predefinida.
SET('v1','v2',...)	Conjunto, cero o más valores de una lista predefinida.

Tipos de Fecha y Hora:

Tipo	Descripción
DATE	Fecha (YYYY-MM-DD).
TIME	Hora (HH:MM:SS).
YEAR	Año (YYYY).
DATETIME	Fecha y hora (YYYY-MM-DD HH:MM:SS).
TIMESTAMP	Fecha y hora con rango limitado, útil para marcas de tiempo automáticas.

Ejemplo: Crear una tabla de empleados.

CREATE TABLE empleados (
       id_empleado INT COMMENT 'Identificador de empleado',
       numero_trabajo VARCHAR(10) COMMENT 'Número de trabajo del empleado',
       nombre VARCHAR(10) COMMENT 'Nombre',
       genero CHAR(1) COMMENT 'Género',
       edad TINYINT UNSIGNED COMMENT 'Edad (sin signo)',
       dni CHAR(18) COMMENT 'Número de Identificación Personal',
       fecha_contratacion DATE COMMENT 'Fecha de ingreso'
   ) COMMENT 'Tabla de Empleados';

Operaciones con Tablas - Modificación

Utilice ALTER TABLE para modificar la estructura de una tabla.

1. Añadir una columna:

ALTER TABLE nombre_tabla ADD nombre_columna tipo_dato(longitud) [COMMENT 'comentario'] [restricción];

Ejemplo: Añadir la columna 'apodo' a la tabla empleados.

ALTER TABLE empleados ADD apodo VARCHAR(20) COMMENT 'Apodo del empleado';

2. Modificar el tipo de dato de una columna:

ALTER TABLE nombre_tabla MODIFY nombre_columna nuevo_tipo_dato(longitud);

3. Cambiar el nombre y/o tipo de dato de una columna:

ALTER TABLE nombre_tabla CHANGE antiguo_nombre_columna nuevo_nombre_columna tipo_dato(longitud) [COMMENT 'comentario'] [restricción];

4. Eliminar una columna:

ALTER TABLE nombre_tabla DROP nombre_columna;

Ejemplo: Eliminar la columna 'apodo' de la tabla empleados.

ALTER TABLE empleados DROP apodo;

5. Renombrar una tabla:

ALTER TABLE antiguo_nombre_tabla RENAME TO nuevo_nombre_tabla;

Ejemplo: Renombrar la tabla empleados a personal.

ALTER TABLE empleados RENAME TO personal;

Operaciones con Tablas - Eliminación

1. Eliminar una tabla:

DROP TABLE [IF EXISTS] nombre_tabla;

Ejemplo: Eliminar la tabla usuarios_ti.

DROP TABLE IF EXISTS usuarios_ti;

2. Truncar una tabla (eliminar todos los datos y reiniciar el auto incremento):

TRUNCATE TABLE nombre_tabla;

Al truncar una tabla, todos sus datos son eliminados permanentemente.

Sentencias DML (Data Manipulation Lenguage)

Las sentencias DML se utilizan para insertar, actualizar y eliminar registros en las tablas de la base de datos.

Insertar Datos (INSERT)

1. Insertar datos en columnas específicas:

INSERT INTO nombre_tabla (columna1, columna2, ...) VALUES (valor1, valor2, ...);

Ejemplo:

INSERT INTO personal (id_empleado, numero_trabajo, nombre, genero, edad, dni, fecha_contratacion) 
   VALUES (1, 'EMP001', 'Laura Pérez', 'F', 25, '123456789012345678', '2020-01-15');

2. Insertar datos en todas las columnas (el orden de los valores debe coincidir con el orden de las columnas):

INSERT INTO nombre_tabla VALUES (valor1, valor2, ...);

Ejemplo:

INSERT INTO personal VALUES (2, 'EMP002', 'Carlos Ruiz', 'M', 30, '987654321098765432', '2019-05-20');

3. Inserción de múltiples registros:

INSERT INTO nombre_tabla (columna1, columna2, ...) VALUES 
   (valor1_1, valor1_2, ...), 
   (valor2_1, valor2_2, ...), 
   (...);

Ejemplo:

INSERT INTO personal VALUES 
   (3, 'EMP003', 'Ana Gómez', 'F', 28, '112233445566778899', '2021-03-10'),
   (4, 'EMP004', 'Juan Castro', 'M', 35, '223344556677889900', '2018-11-01');

Consideraciones:

Los valores de cadena y fecha deben ir entre comillas.
Los datos insertados deben respetar los tipos y tamaños de las columnas.

Modificar Datos (UPDATE)

UPDATE nombre_tabla SET columna1 = valor1, columna2 = valor2, ... [WHERE condición];

Importante: Si se omite la cláusula WHERE, todos los registros de la tabla serán actualizados.

Ejemplo: Actualizar el nombre y género del empleado con ID 1.

UPDATE personal SET nombre = 'Sofia López', genero = 'F' WHERE id_empleado = 1;

Ejemplo: Actualizar la fecha de contratación de todos los empleados.

UPDATE personal SET fecha_contratacion = '2022-01-01';

Eliminar Datos (DELETE)

DELETE FROM nombre_tabla [WHERE condición];

Importante: Sin la cláusula WHERE, todos los registros de la tabla serán eliminados.

Ejemplo: Eliminar empleados cuyo género sea 'F'.

DELETE FROM personal WHERE genero = 'F';

Ejemplo: Eliminar todos los empleados.

DELETE FROM personal;

Tenga en cuenta que DELETE no puede eliminar el valor de una columna específica; para eso, use UPDATE y establezca el valor en NULL o un valor predeterminado.

Sentencias DQL (Data Query Language)

Las sentencias DQL se utilizan para consultar y recuperar registros de la base de datos. La palabra clave principal es SELECT.

Sintaxis General de Consulta

SELECT
       lista_columnas
   FROM
       nombre_tabla
   [WHERE
       condición_filtro]
   [GROUP BY
       columnas_agrupamiento]
   [HAVING
       condición_grupo]
   [ORDER BY
       columnas_ordenamiento]
   [LIMIT
       parámetros_paginación];

Consultas Básicas

1. Seleccionar columnas específicas:

SELECT columna1, columna2, ... FROM nombre_tabla;

2. Seleccionar todas las columnas:

SELECT * FROM nombre_tabla;

3. Asignar alias a columnas:

SELECT columna1 AS alias1, columna2 AS alias2 FROM nombre_tabla;

La palabra clave AS es opcional.

4. Eliminar registros duplicados (DISTINCT):

SELECT DISTINCT columna FROM nombre_tabla;

Consultas Condicionales (WHERE)

La cláusula WHERE filtra los registros según una condición.

SELECT lista_columnas FROM nombre_tabla WHERE condición;

Operadores de Comparación:

Operador	Función
>	Mayor que
>=	Mayor o igual que
<	Menor que
<=	Menor o igual que
=	Igual a
<> o !=	Distinto de
BETWEEN...AND...	En un rango (incluye límites).
IN(...)	Valor presente en una lista.
LIKE patrón	Coincidencia de patrones (% para cero o más caracteres, _ para un solo carácter).
IS NULL	Es nulo.
IS NOT NULL	No es nulo.

Operadores Lógicos:

Operador	Función
AND o &&	Ambas condiciones deben ser verdaderas.
OR o \|\|	Al menos una condición debe ser verdadera.
NOT o !	Invierte la condición.

Funciones de Agregación

Estas funciones operan sobre un conjunto de valores de una columna y devuelven un único valor.

Función	Propósito
COUNT(columna)	Cuenta el número de filas o valores no nulos en una columna.
MAX(columna)	Devuelve el valor máximo.
MIN(columna)	Devuelve el valor mínimo.
AVG(columna)	Calcula el promedio.
SUM(columna)	Calcula la suma.

Sintaxis:

SELECT funcion_agregacion(columna) FROM nombre_tabla;

Los valores NULL no son incluidos en las operaciones de las funciones de agregación.

Consultas Agrupadas (GROUP BY)

Agrupa filas que tienen los mismos valores en una o más columnas en un conjunto de resumen.

SELECT lista_columnas FROM nombre_tabla [WHERE condición] GROUP BY columnas_agrupamiento [HAVING condición_grupo];

Diferencias entre WHERE y HAVING:

WHERE filtra filas antes de la agrupación; HAVING filtra grupos después de la agrupación.
WHERE no puede usar funciones de agregación; HAVING sí.

El orden de ejecución es: WHERE > Funciones de Agregación > HAVING.

Normalmente, las columnas seleccionadas después de un GROUP BY deben ser columnas de agrupación o funciones de agregación.

Consultas Ordenadas (ORDER BY)

Ordena los resultados de una consulta.

SELECT lista_columnas FROM nombre_tabla ORDER BY columna1 tipo_orden1, columna2 tipo_orden2;

Tipos de Ordenamiento:

ASC: Ascendente (predeterminado).
DESC: Descendente.

Para múltiples columnas, el orden se aplica secuencialmente: si los valores de la primera columna son iguales, se usa la segunda para ordenar.

Consultas Paginadas (LIMIT)

Limita el número de filas retornadas por una consulta.

SELECT lista_columnas FROM nombre_tabla LIMIT índice_inicio, número_registros;

El índice_inicio es base cero. Se calcula como (número_página - 1) * registros_por_página. Si se omite el índice_inicio, se asume 0.

La cláusula LIMIT es una extensión específica de MySQL para paginación.

Orden de Ejecución de Sentencias DQL

Las cláusulas de una sentencia SELECT se ejecutan en el siguiente orden lógico:

FROM
JOIN (si aplica)
ON (si aplica)
WHERE
GROUP BY
Funciones de Agregación
HAVING
SELECT (proyección de columnas)
DISTINCT (si aplica)
ORDER BY
LIMIT

Sentencias DCL (Data Control Language)

DCL se usa para gestionar los usuarios de la base de datos y sus permisos.

Gestión de Usuarios

1. Consultar usuarios:

USE mysql;
SELECT user, host FROM user;

2. Crear un nuevo usuario:

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

hostname puede ser 'localhost' o '%' para cualquier host.

3. Modificar la contraseña de un usuario:

ALTER USER 'nombre_usuario'@'hostname' IDENTIFIED WITH mysql_native_password BY 'nueva_contraseña';

El método de autenticación mysql_native_password es común, pero puede variar según la versión de MySQL.

4. Eliminar un usuario:

DROP USER 'nombre_usuario'@'hostname';

Control de Permisos

Permisos comunes:

Permiso	Descripción
ALL PRIVILEGES	Todos los permisos.
SELECT	Leer datos.
INSERT	Insertar nuevas filas.
UPDATE	Modificar filas existentes.
DELETE	Eliminar filas.
ALTER	Modificar la estructura de tablas.
DROP	Eliminar bases de datos/tablas/vistas.
CREATE	Crear bases de datos/tablas.

1. Consultar permisos de un usuario:

SHOW GRANTS FOR 'nombre_usuario'@'hostname';

2. Otorgar permisos:

GRANT lista_permisos ON base_datos.tabla TO 'nombre_usuario'@'hostname';

Se pueden especificar múltiples permisos separados por comas. Use *.* para todos los objetos.

3. Revocar permisos:

REVOKE lista_permisos ON base_datos.tabla FROM 'nombre_usuario'@'hostname';

4. Recargar la tabla de permisos (necesario después de cambios de permisos):

FLUSH PRIVILEGES;

Funciones de MySQL

MySQL ofrece una rica colección de funciones incorporadas para manipular datos.

Funciones de Cadena

Función	Propósito
CONCAT(s1, s2, ...sn)	Concatena varias cadenas en una sola.
LOWER(str)	Convierte una cadena a minúsculas.
UPPER(str)	Convierte una cadena a mayúsculas.
LPAD(str, n, pad)	Rellena el lado izquierdo de `str` con `pad` hasta una longitud de `n`.
RPAD(str, n, pad)	Rellena el lado derecho de `str` con `pad` hasta una longitud de `n`.
TRIM(str)	Elimina espacios en blanco al principio y al final de una cadena.
SUBSTRING(str, inicio, longitud)	Extrae una subcadena de `str`, comenzando en `inicio` por `longitud` caracteres.

Ejemplo: Rellenar a la izquierda el número de trabajo en la tabla personal.

UPDATE personal SET numero_trabajo = LPAD(numero_trabajo, 5, '0');

Funciones Numéricas

Función	Propósito
CEIL(x)	Devuelve el entero más pequeño, mayor o igual que `x` (redondeo hacia arriba).
FLOOR(x)	Devuelve el entero más grande, menor o igual que `x` (redondeo hacia abajo).
MOD(x, y)	Devuelve el resto de la división de `x` entre `y`.
RAND()	Devuelve un número de punto flotante aleatorio entre 0 y 1.
ROUND(x, y)	Redondea `x` a `y` posiciones decimales.

Ejemplo: Generar un código de verificación aleatorio de seis dígitos.

SELECT LPAD(ROUND(RAND() * 1000000), 6, '0');

Funciones de Fecha y Hora

Función	Propósito
CURDATE()	Devuelve la fecha actual.
CURTIME()	Devuelve la hora actual.
NOW()	Devuelve la fecha y hora actuales.
YEAR(fecha)	Extrae el año de una fecha.
MONTH(fecha)	Extrae el mes de una fecha.
DAY(fecha)	Extrae el día del mes de una fecha.
DATE_ADD(fecha, INTERVAL expr tipo)	Añade un intervalo a una fecha/hora.
DATEDIFF(fecha1, fecha2)	Calcula la diferencia en días entre dos fechas.

Ejemplo: Calcular los días de servicio de los empleados y ordenarlos.

SELECT nombre, DATEDIFF(CURDATE(), fecha_contratacion) AS dias_servicio 
FROM personal ORDER BY dias_servicio DESC;

Funciones de Flujo de Control

Función	Propósito
IF(condición, valor_verdadero, valor_falso)	Retorna `valor_verdadero` si la condición es verdadera, de lo contrario `valor_falso`.
IFNULL(valor1, valor2)	Retorna `valor1` si no es nulo, de lo contrario `valor2`.
CASE WHEN [cond1] THEN [res1] ... ELSE [defecto] END	Evalúa una serie de condiciones y retorna el resultado de la primera verdadera.
CASE [expr] WHEN [val1] THEN [res1] ... ELSE [defecto] END	Compara `expr` con una serie de valores y retorna el resultado correspondiente.

Ejemplo: Clasificar la dirección de trabajo de los empleados en "Ciudad Principle" o "Otras Ciudades".

SELECT
       nombre,
       (CASE direccion_trabajo 
           WHEN 'Madrid' THEN 'Ciudad Principal' 
           WHEN 'Barcelona' THEN 'Ciudad Principal' 
           ELSE 'Otras Ciudades' 
       END) AS clasificacion_direccion
   FROM personal;

Restricciones (Constraints)

Las restricciones son reglas aplicadas a las columnas de una tabla para asegurar la validez e integridad de los datos.

Restricción	Descripción	Palabra Clave
NOT NULL	La columna no puede contener valores `NULL`.	NOT NULL
UNIQUE	Todos los valores en la columna deben ser únicos.	UNIQUE
PRIMARY KEY	Clave primaria, identifica de forma única cada fila. Implica `NOT NULL` y `UNIQUE`.	PRIMARY KEY
DEFAULT	Asigna un valor predeterminado si no se especifica uno al insertar.	DEFAULT
CHECK	Asegura que los valores en la columna cumplen una condición específica (disponible desde MySQL 8.0.16).	CHECK
FOREIGN KEY	Establece un enlace entre dos tablas, garantizando la integridad referencial.	FOREIGN KEY
AUTO_INCREMENT	Genera automáticamente un valor incremental para la columna (generalmente en claves primarias).	AUTO_INCREMENT

Las restricciones se pueden definir al crear o modificar una tabla.

Ejemplo: Tabla usuarios con varias restricciones.

CREATE TABLE usuarios (
       id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Clave primaria',
       nombre VARCHAR(10) NOT NULL UNIQUE COMMENT 'Nombre de usuario (único y no nulo)',
       edad INT CHECK(edad > 0 AND edad < 120) COMMENT 'Edad (entre 1 y 119)',
       estado CHAR(1) DEFAULT '1' COMMENT 'Estado (predeterminado "1")',
       genero CHAR(1) COMMENT 'Género'
   ) COMMENT 'Tabla de Usuarios';

Restricciones de Clave Foránea (FOREIGN KEY)

Una clave foránea establece una relación entre una columna de una tabla (tabla hija) y la clave primaria de otra tabla (tabla padre).

Sintaxis para añadir una clave foránea al crear una tabla:

CREATE TABLE nombre_tabla (
       columna_id_hija TIPO,
       ...,
       [CONSTRAINT] [nombre_fk] FOREIGN KEY (columna_id_hija) REFERENCES tabla_padre(columna_id_padre)
   );

Sintaxis para añadir una clave foránea a una tabla existente:

ALTER TABLE nombre_tabla ADD CONSTRAINT nombre_fk FOREIGN KEY (columna_id_hija) REFERENCES tabla_padre(columna_id_padre);

Ejemplo: Añadir una clave foránea id_departamento a la tabla personal que referencia la tabla departamentos.

ALTER TABLE personal ADD CONSTRAINT fk_personal_departamentos FOREIGN KEY (id_departamento) REFERENCES departamentos(id_dep);

Sintaxis para eliminar una clave foránea:

ALTER TABLE nombre_tabla DROP FOREIGN KEY nombre_fk;

Ejemplo:

ALTER TABLE personal DROP FOREIGN KEY fk_personal_departamentos;

Acciones de Eliminación/Actualización en Claves Foráneas

Definen cómo se comporta la tabla hija cuando se modifican o eliminan registros en la tabla padre.

Acción	Descripción
NO ACTION	Impide la eliminación/actualización en la tabla padre si existen registros hijos relacionados. (Igual que RESTRICT).
RESTRICT	Impide la eliminación/actualización en la tabla padre si existen registros hijos relacionados. (Igual que NO ACTION).
CASCADE	Cuando se elimina/actualiza un registro en la tabla padre, se eliminan/actualizan automáticamente los registros hijos correspondientes.
SET NULL	Cuando se elimina/actualiza un registro en la tabla padre, la clave foránea en la tabla hija se establece en `NULL` (requiere que la columna de la clave foránea permita nulos).
SET DEFAULT	Establece la clave foránea en un valor predeterminado si el registro padre cambia (no soportado por InnoDB).

Ejemplo con CASCADE:

ALTER TABLE personal ADD CONSTRAINT fk_personal_departamentos 
   FOREIGN KEY (id_departamento) REFERENCES departamentos(id_dep) 
   ON UPDATE CASCADE ON DELETE CASCADE;

Ejemplo con SET NULL:

ALTER TABLE personal ADD CONSTRAINT fk_personal_departamentos 
   FOREIGN KEY (id_departamento) REFERENCES departamentos(id_dep) 
   ON UPDATE SET NULL ON DELETE SET NULL;

Consultas Multi-Tabla

Las consultas multi-tabla permiten obtener información de dos o más tablas relacionadas.

Producto Cartesiano: Es la combinación de cada fila de la primera tabla con cada fila de la segunda tabla. En consultas multi-tabla, es esencial filtrar el producto cartesiano para obtener resultados significativos.

Tipos de Consultas Multi-Tabla:

Consultas de Unión (JOIN): Combinan filas de dos o más tablas basándose en una columna relacionada.
- INNER JOIN: Retorna solo las filas que tienen coincidencias en ambas tablas.
- LEFT (OUTER) JOIN: Retorna todas las filas de la tabla izquierda y las filas coincidentes de la tabla derecha.
- RIGHT (OUTER) JOIN: Retorna todas las filas de la tabla derecha y las filas coincidentes de la tabla izquierda.
- SELF JOIN: Una tabla se une consigo misma.
Subconsultas (Subqueries): Una consulta anidada dentro de otra consulta.

INNER JOIN

Recupera solo las filas donde hay una coincidencia en ambas tablas, es decir, la intersección.

Sintaxis Implícita (antigua):

SELECT lista_columnas FROM tabla1, tabla2 WHERE condición_unión;

Ejemplo: Obtener el nombre del empleado y el nombre de su departamento.

SELECT p.nombre, d.nombre_dep 
FROM personal p, departamentos d 
WHERE p.id_departamento = d.id_dep;

Sintaxis Explícita (recomendada):

SELECT lista_columnas FROM tabla1 [INNER] JOIN tabla2 ON condición_unión;

Ejemplo: (Mismo resultado que el anterior)

SELECT p.nombre, d.nombre_dep 
FROM personal p INNER JOIN departamentos d ON p.id_departamento = d.id_dep;

OUTER JOIN

1. LEFT [OUTER] JOIN:

Retorna todos los registros de la tabla izquierda, y los registros coincidentes de la tabla derecha. Si no hay coincidencia, devuelve NULL para las columnas de la tabla derecha.

SELECT lista_columnas FROM tabla1 LEFT [OUTER] JOIN tabla2 ON condición_unión;

Ejemplo: Obtener todos los datos de los empleados y la información de su departamento, incluso si no tienen departamento asignado.

SELECT p.*, d.nombre_dep 
FROM personal p LEFT OUTER JOIN departamentos d ON p.id_departamento = d.id_dep;

2. RIGHT [OUTER] JOIN:

Retorna todos los registros de la tabla derecha, y los registros coincidentes de la tabla izquierda. Si no hay coincidencia, devuelve NULL para las columnas de la tabla izquierda.

SELECT lista_columnas FROM tabla1 RIGHT [OUTER] JOIN tabla2 ON condición_unión;

Ejemplo: Obtener todos los departamentos y los empleados asignados a ellos, incluso si no hay empleados en un departamento.

SELECT d.*, p.* 
FROM personal p RIGHT OUTER JOIN departamentos d ON p.id_departamento = d.id_dep;

SELF JOIN

Una tabla se une consigo misma. Requiere el uso de alias para diferenciar las dos instancias de la tabla.

SELECT lista_columnas FROM tabla AS alias1 JOIN tabla AS alias2 ON condición;

Ejemplo: Consulta de empleados y sus gerentes (asumiendo que manager_id es una clave foránea a id_empleado en la misma tabla personal).

SELECT empleado.nombre AS NombreEmpleado, gerente.nombre AS NombreGerente
FROM personal AS empleado JOIN personal AS gerente ON empleado.id_gerente = gerente.id_empleado;

Ejemplo (con LEFT JOIN para incluir empleados sin gerente):

SELECT empleado.nombre AS NombreEmpleado, gerente.nombre AS NombreGerente
FROM personal AS empleado LEFT JOIN personal AS gerente ON empleado.id_gerente = gerente.id_empleado;

Consultas UNION

Combina los resultados de dos o más sentencias SELECT en un único conjunto de resultados.

SELECT lista_columnas FROM tablaA ...
UNION [ALL]
SELECT lista_columnas FROM tablaB ...;

Consideraciones:

Las sentencias SELECT deben tener el mismo número de columnas y tipos de datos compatibles.
UNION elimina filas duplicadas del resultado final.
UNION ALL incluye todas las filas, incluso las duplicadas.

Ejemplo: Obtener empleados con salario inferior a 5000 y empleados mayores de 50 años.

SELECT id_empleado, nombre, salario FROM personal WHERE salario < 5000
UNION ALL
SELECT id_empleado, nombre, salario FROM personal WHERE edad > 50;

Subconsultas

Una subconsulta es una sentencia SELECT anidada dentro de otra sentencia SQL (SELECT, INSERT, UPDATE, DELETE).

Ejemplo:

SELECT * FROM tabla1 WHERE columna = (SELECT columna FROM tabla2 WHERE condición);

Las subconsultas se clasifican según el número de valores que devuelvan y su posición dentro de la consulta principal.

Tipos de Subconsultas:

Subconsulta Escalar: Devuelve un único valor (una fila, una columna).
Subconsulta de Columna: Devuelve una única columna, pero múltiples filas.
Subconsulta de Fila: Devuelve una única fila, pero múltiples columnas.
Subconsulta de Tabla: Devuelve múltiples filas y múltiples columnas.

Subconsulta Escalar

Retorna un único valor. Se usa comúnmente en la cláusula WHERE con operadores de comparación como =, <>, >, >=, <, <=.

Ejemplo: Obtener la información de los empleados del departamento de "Ventas".

-- Paso 1: Obtener el ID del departamento 'Ventas'
SELECT id_dep FROM departamentos WHERE nombre_dep = 'Ventas';

-- Paso 2: Usar el ID en una subconsulta
SELECT * FROM personal WHERE id_departamento = (SELECT id_dep FROM departamentos WHERE nombre_dep = 'Ventas');

Subconsulta de Columna

Retorna una columna con múltiples valores. Se usa con operadores como IN, NOT IN, ANY, SOME, ALL.

Operador	Descripción
IN	El valor está presente en la lista de la subconsulta.
NOT IN	El valor no está presente en la lista de la subconsulta.
ANY / SOME	Compara un valor con cualquiera de los valores en la lista de la subconsulta.
ALL	Compara un valor con todos los valores en la lista de la subconsulta.

Ejemplo: Obtener empleados de los departamentos de "Marketing" o "Desarrollo".

SELECT * FROM personal 
WHERE id_departamento IN (SELECT id_dep FROM departamentos WHERE nombre_dep IN ('Marketing', 'Desarrollo'));

Ejemplo: Obtener empleados cuyo salario es mayor que el salario de CUALQUIER empleado del departamento de "Soporte".

SELECT * FROM personal 
WHERE salario > ANY (SELECT salario FROM personal WHERE id_departamento = (SELECT id_dep FROM departamentos WHERE nombre_dep = 'Soporte'));

Ejemplo: Obtener empleados cuyo salario es mayor que el salario de TODOS los empleados del departamento de "Contabilidad".

SELECT * FROM personal 
WHERE salario > ALL (SELECT salario FROM personal WHERE id_departamento = (SELECT id_dep FROM departamentos WHERE nombre_dep = 'Contabilidad'));

Subconsulta de Fila

Retorna una única fila con múltiples columnas. Se usa con operadores de comparación como =, <>, IN, NOT IN.

Ejemplo: Obtener empleados con el mismo salario y gerente que 'María López'.

SELECT * FROM personal 
WHERE (salario, id_gerente) = (SELECT salario, id_gerente FROM personal WHERE nombre = 'María López');

Subconsulta de Tabla

Retorna múltiples filas y múltiples columnas. Se usa típicamente en la cláusula FROM (tratando el resultado como una tabla derivada) o con operadores IN.

Ejemplo: Obtener empleados que tienen la misma ocupación y salario que 'Pedro Sánchez' o 'Luisa Flores'.

SELECT * FROM personal 
WHERE (ocupacion, salario) IN (SELECT ocupacion, salario FROM personal WHERE nombre IN ('Pedro Sánchez', 'Luisa Flores'));

Ejemplo: Obtener la información de los empleados que ingresaron después de '2010-01-01' y sus respectivos departamentos.

SELECT p.*, d.nombre_dep
FROM (SELECT * FROM personal WHERE fecha_contratacion > '2010-01-01') AS p
LEFT JOIN departamentos AS d ON p.id_departamento = d.id_dep;

Sentencias de Práctica de MySQL

A continuación, se presentan ejemplos de sentencias DDL, DML y DQL para practicar, utilizando una estructura de tabla simplificada para empleados y departamentos.

-- DDL: Preparación de Datos
CREATE TABLE departamentos (
   id_dep INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID de Departamento',
   nombre_dep VARCHAR(50) NOT NULL UNIQUE COMMENT 'Nombre del Departamento'
) COMMENT 'Tabla de Departamentos';

INSERT INTO departamentos (nombre_dep) VALUES ('Recursos Humanos'), ('Ventas'), ('Marketing'), ('Desarrollo'), ('Soporte');

CREATE TABLE personal (
   id_empleado INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Identificador de Empleado',
   num_empleado VARCHAR(10) UNIQUE COMMENT 'Número de Empleado',
   nombre VARCHAR(50) NOT NULL COMMENT 'Nombre Completo',
   genero CHAR(1) COMMENT 'Género (M/F)',
   edad TINYINT UNSIGNED CHECK (edad BETWEEN 18 AND 65) COMMENT 'Edad',
   dni CHAR(10) UNIQUE COMMENT 'DNI',
   direccion_trabajo VARCHAR(100) COMMENT 'Dirección de Trabajo',
   fecha_ingreso DATE COMMENT 'Fecha de Ingreso',
   salario DECIMAL(10, 2) COMMENT 'Salario Mensual',
   id_departamento INT COMMENT 'ID del Departamento',
   id_gerente INT COMMENT 'ID del Gerente',
   CONSTRAINT fk_personal_departamentos FOREIGN KEY (id_departamento) REFERENCES departamentos(id_dep) ON DELETE SET NULL,
   CONSTRAINT fk_personal_gerente FOREIGN KEY (id_gerente) REFERENCES personal(id_empleado) ON DELETE SET NULL
) COMMENT 'Tabla de Personal';

-- DML: Inserción de Datos en la tabla personal
INSERT INTO personal (num_empleado, nombre, genero, edad, dni, direccion_trabajo, fecha_ingreso, salario, id_departamento, id_gerente) VALUES
('EMP001', 'Ana García', 'F', 30, '12345678A', 'Madrid', '2015-03-01', 5500.00, 2, NULL), -- Ventas
('EMP002', 'Juan Pérez', 'M', 25, '87654321B', 'Barcelona', '2018-07-10', 4800.00, 2, 1), -- Ventas, Gerente Ana
('EMP003', 'Luisa Martínez', 'F', 35, '11223344C', 'Sevilla', '2012-01-20', 6200.00, 4, NULL), -- Desarrollo
('EMP004', 'Pedro Sánchez', 'M', 28, '55667788D', 'Madrid', '2019-11-05', 4900.00, 3, 3), -- Marketing, Gerente Luisa
('EMP005', 'Marta Gómez', 'F', 40, '99001122E', 'Barcelona', '2010-09-15', 7000.00, 4, NULL), -- Desarrollo
('EMP006', 'Jorge Ruiz', 'M', 22, '33445566F', 'Sevilla', '2021-02-28', 3500.00, 1, 3), -- RRHH, Gerente Luisa
('EMP007', 'Elena Díaz', 'F', 33, '77889900G', 'Madrid', '2017-06-01', 5800.00, 2, 1), -- Ventas, Gerente Ana
('EMP008', 'Ricardo Castro', 'M', 45, '22113344H', 'Barcelona', '2008-04-01', 7500.00, 4, 5), -- Desarrollo, Gerente Marta
('EMP009', 'Sofía Vargas', 'F', 29, '66554433I', 'Madrid', '2020-08-12', 5100.00, 3, 3), -- Marketing, Gerente Luisa
('EMP010', 'Pablo Torres', 'M', 38, '00998877J', 'Sevilla', '2014-10-01', 6000.00, 5, NULL); -- Soporte

-- DML: Ejemplos de Modificación y Eliminación
UPDATE personal SET genero = 'M' WHERE id_empleado = 6; -- Modificar género
DELETE FROM personal WHERE id_empleado = 2; -- Eliminar un empleado

-- DQL: Consultas Básicas
SELECT nombre, num_empleado, edad FROM personal; -- Seleccionar columnas específicas
SELECT * FROM personal; -- Seleccionar todas las columnas
SELECT direccion_trabajo AS CiudadOficina FROM personal; -- Alias para columna
SELECT DISTINCT direccion_trabajo FROM personal; -- Valores únicos de columna

-- DQL: Consultas Condicionales (WHERE)
SELECT * FROM personal WHERE edad = 35; -- Empleados con edad 35
SELECT * FROM personal WHERE salario < 5000; -- Salario menor a 5000
SELECT * FROM personal WHERE dni IS NULL; -- DNI nulo
SELECT * FROM personal WHERE dni IS NOT NULL; -- DNI no nulo
SELECT * FROM personal WHERE edad != 28; -- Edad diferente de 28
SELECT * FROM personal WHERE edad BETWEEN 20 AND 30; -- Edad entre 20 y 30
SELECT * FROM personal WHERE genero = 'F' AND edad < 30; -- Género F y edad menor a 30
SELECT * FROM personal WHERE edad IN (25, 30, 40); -- Edad 25, 30 o 40
SELECT * FROM personal WHERE nombre LIKE 'J%'; -- Nombres que empiezan con J
SELECT * FROM personal WHERE dni LIKE '%A'; -- DNI que termina con A

-- DQL: Funciones de Agregación
SELECT COUNT(*) FROM personal; -- Total de empleados
SELECT COUNT(dni) FROM personal; -- Total de empleados con DNI (excluye NULL)
SELECT AVG(edad) FROM personal; -- Edad promedio
SELECT MAX(salario) FROM personal; -- Salario máximo
SELECT MIN(salario) FROM personal; -- Salario mínimo
SELECT SUM(salario) FROM personal WHERE direccion_trabajo = 'Madrid'; -- Suma de salarios en Madrid

-- DQL: Consultas Agrupadas (GROUP BY)
SELECT genero, COUNT(*) FROM personal GROUP BY genero; -- Conteo por género
SELECT genero, AVG(salario) FROM personal GROUP BY genero; -- Salario promedio por género
SELECT direccion_trabajo, COUNT(*) FROM personal WHERE edad < 40 GROUP BY direccion_trabajo HAVING COUNT(*) >= 2; -- Direcciones con al menos 2 empleados menores de 40

-- DQL: Consultas Ordenadas (ORDER BY)
SELECT * FROM personal ORDER BY edad ASC; -- Ordenar por edad ascendente
SELECT * FROM personal ORDER BY fecha_ingreso DESC; -- Ordenar por fecha de ingreso descendente
SELECT * FROM personal ORDER BY edad ASC, salario DESC; -- Ordenar por edad ASC, luego salario DESC

-- DQL: Consultas Paginadas (LIMIT)
SELECT * FROM personal LIMIT 5; -- Los primeros 5 empleados
SELECT * FROM personal LIMIT 5, 5; -- 5 empleados a partir del sexto (página 2, 5 registros por página)

-- DQL: Ejercicios combinados
-- 1. Empleados femeninos con edades 28, 30, 35 o 40 años
SELECT * FROM personal WHERE genero = 'F' AND edad IN (28, 30, 35, 40);

-- 2. Empleados masculinos, nombre de 3 caracteres, y edad entre 25 y 45 (ambos inclusive)
SELECT * FROM personal WHERE genero = 'M' AND LENGTH(nombre) = 3 AND edad BETWEEN 25 AND 45;

-- 3. Conteo de empleados masculinos y femeninos menores de 50 años
SELECT genero, COUNT(*) FROM personal WHERE edad < 50 GROUP BY genero;

-- 4. Nombre y edad de empleados menores o iguales a 35 años, ordenados por edad ascendente y luego por fecha de ingreso descendente
SELECT nombre, edad FROM personal WHERE edad <= 35 ORDER BY edad ASC, fecha_ingreso DESC;

-- 5. Los 3 primeros empleados masculinos con edad entre 20 y 40, ordenados por edad ascendente y luego por nombre ascendente
SELECT * FROM personal WHERE genero = 'M' AND edad BETWEEN 20 AND 40 ORDER BY edad ASC, nombre ASC LIMIT 3;

-- DQL: Consultas Multi-tabla y Subconsultas (ejemplos)
-- INNER JOIN: Empleados y sus departamentos
SELECT p.nombre, d.nombre_dep 
FROM personal p INNER JOIN departamentos d ON p.id_departamento = d.id_dep;

-- LEFT JOIN: Todos los empleados y su departamento (si tienen)
SELECT p.nombre, p.salario, d.nombre_dep
FROM personal p LEFT JOIN departamentos d ON p.id_departamento = d.id_dep;

-- SELF JOIN: Empleados y el nombre de su gerente
SELECT p.nombre AS Empleado, g.nombre AS Gerente
FROM personal p LEFT JOIN personal g ON p.id_gerente = g.id_empleado;

-- Subconsulta escalar: Empleados del departamento de 'Desarrollo'
SELECT * FROM personal WHERE id_departamento = (SELECT id_dep FROM departamentos WHERE nombre_dep = 'Desarrollo');

-- Subconsulta de columna: Empleados en 'Ventas' o 'Marketing'
SELECT * FROM personal WHERE id_departamento IN (SELECT id_dep FROM departamentos WHERE nombre_dep IN ('Ventas', 'Marketing'));

-- Subconsulta de fila: Empleados con el mismo salario y dirección de trabajo que 'Ana García'
SELECT * FROM personal WHERE (salario, direccion_trabajo) = (SELECT salario, direccion_trabajo FROM personal WHERE nombre = 'Ana García');

-- Subconsulta de tabla: Empleados contratados después de 2017 y sus departamentos
SELECT p_filtrado.nombre, p_filtrado.fecha_ingreso, d.nombre_dep
FROM (SELECT * FROM personal WHERE fecha_ingreso > '2017-12-31') AS p_filtrado
LEFT JOIN departamentos d ON p_filtrado.id_departamento = d.id_dep;

Etiquetas: MySQL SQL DDL DML dql

Publicado el 7-16 08:01

Friki Work

Fundamentos y Comandos Básicos de MySQL

Conceptos Clave de Bases de Datos

Conexión a un Servidor MySQL

Bases de Datos Relacionales (RDBMS)

Sintaxis General de SQL

Clasificación de Sentencias SQL

Sentencias DDL (Data Definition Language)

Operaciones con Bases de Datos

Operaciones con Tablas - Consultas

Operaciones con Tablas - Creación

Tipos de Datos de Columna

Operaciones con Tablas - Modificación

Operaciones con Tablas - Eliminación

Sentencias DML (Data Manipulation Lenguage)

Insertar Datos (INSERT)

Modificar Datos (UPDATE)

Eliminar Datos (DELETE)

Sentencias DQL (Data Query Language)

Sintaxis General de Consulta

Consultas Básicas

Consultas Condicionales (WHERE)

Funciones de Agregación

Consultas Agrupadas (GROUP BY)

Consultas Ordenadas (ORDER BY)

Consultas Paginadas (LIMIT)

Orden de Ejecución de Sentencias DQL

Sentencias DCL (Data Control Language)

Gestión de Usuarios

Control de Permisos

Funciones de MySQL

Funciones de Cadena

Funciones Numéricas

Funciones de Fecha y Hora

Funciones de Flujo de Control

Restricciones (Constraints)

Restricciones de Clave Foránea (FOREIGN KEY)

Acciones de Eliminación/Actualización en Claves Foráneas

Consultas Multi-Tabla

Tipos de Consultas Multi-Tabla:

INNER JOIN

OUTER JOIN

SELF JOIN

Consultas UNION

Subconsultas

Tipos de Subconsultas:

Subconsulta Escalar

Subconsulta de Columna

Subconsulta de Fila

Subconsulta de Tabla

Sentencias de Práctica de MySQL

Etiquetas populares