Unidad 3.5 - DML Ejecución de instrucciones de manipulación

3.3.1 Introducción

Base de datos de ejemplo: employees

En este tema, vamos a trabajar con la base de datos employees, que es una base de datos de ejemplo que puedes descargar y utilizar para practicar.

La base de datos employees contiene varias tablas relacionadas, pero para este tema nos centraremos en las tablas employees, departments, y salaries.

¿Cómo obtener la base de datos employees?

Puedes descargar la base de datos employees de MySQL desde aquí. Esta base de datos se utiliza comúnmente para ejemplos y tutoriales, y contiene información sobre empleados, salarios, departamentos y títulos, lo que la hace ideal para practicar.

En la página puede enncontrar un archivo comprimido que contiene la base de datos en formato SQL. Descomprímelo y carga el archivo SQL en tu servidor MySQL utilizando phpMyAdmin o cualquier otra herramienta de gestión de bases de datos.

En el caso de estar utilizando docker, recomendamos subir previamente los archivos a una carpeta compartida entre el contenedor y el host. Una vez cargados, nos conectamos al servidor e importamos los datos utilizando el cliente mysql:

Para conectarnos al servidor MySQL en el contenedor, utilizamos el siguiente comando: 

docker exec -it <nombre_del_contenedor> bash
Donde <nombre_del_contenedor> es el nombre del contenedor que ejecuta MySQL. Una vez dentro del contenedor, podemos importar la base de datos utilizando el siguiente comando:

cd /ruta/a/los/archivos
mysql -uroot -p -t < employees.sql

Si todo va bien deberías ver un mensaje de éxito indicando que la base de datos se ha importado correctamente:

$> mysql -uroot -p -t < employees.sql
+-----------------------------+
| INFO                        |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
+------------------------+
| INFO                   |
+------------------------+
| storage engine: InnoDB |
+------------------------+
+---------------------+
| INFO                |
+---------------------+
| LOADING departments |
+---------------------+
+-------------------+
| INFO              |
+-------------------+
| LOADING employees |
+-------------------+
+------------------+
| INFO             |
+------------------+
| LOADING dept_emp |
+------------------+
+----------------------+
| INFO                 |
+----------------------+
| LOADING dept_manager |
+----------------------+
+----------------+
| INFO           |
+----------------+
| LOADING titles |
+----------------+
+------------------+
| INFO             |
+------------------+
| LOADING salaries |
+------------------+

Buenas prácticas en DML (INSERT, UPDATE, DELETE)

Antes de entrar en los ejemplos de código, es fundamental entender las buenas prácticas a la hora de utilizar instrucciones DML:

  1. Siempre utilizar consultas parametrizadas: Esto ayuda a prevenir ataques de SQL Injection.
  2. Comprobar si los datos fueron correctamente insertados, actualizados o eliminados: Siempre debemos verificar si nuestras operaciones se realizaron correctamente.
  3. Utilizar transacciones: Para garantizar la integridad de los datos, especialmente cuando trabajamos con múltiples operaciones de INSERT, UPDATE o DELETE.
  4. Manejo de errores adecuado: Es importante manejar cualquier error que pueda surgir durante la ejecución de las consultas para evitar fallos inesperados.

Operaciones DML:

INSERT: Cómo insertar datos de manera eficiente

La instrucción INSERT se utiliza para agregar nuevos registros a una tabla. Aquí te mostramos cómo insertar datos en la tabla employees.

Ejemplo de INSERT con MySQLi (orientado a objetos) y procedimental:

Vamos insertar un nuevo empleado en la tabla employees. Los datos del empleado son los siguientes:

  • Código: 99999
  • Nombre: Juan
  • Apellido: Pérez
  • Fecha de nacimiento: 1990-01-01
  • Fecha de contratación: 2025-03-30
  • Genero: M

Primero lo vemos con MySQLi y luego con PDO.

Ejemplo de INSERT

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<?php
// Crear conexión
$conexion = new mysqli("localhost", "root", "", "employees");

// Comprobar conexión
if ($conexion->connect_error) {
    die("Conexión fallida: " . $conexion->connect_error);
}

// Preparar y ejecutar el INSERT
$id = 99999;
$nombre = "Juan";
$apellido1 = "Pérez";
$fecha_contratacion = "2025-03-30";
$fecha_nacimiento = "1990-01-01";
$genero = "M";

$query = "INSERT INTO employees (emp_no, birth_date, first_name, last_name, hire_date, gender) 
        VALUES (?, ?, ?, ?, ?, ?)";

$stmt = $conexion->prepare($query);
$stmt->bind_param("isssss", $id, $fecha_nacimiento, $nombre, $apellido1, $fecha_contratacion, $genero);
$stmt->execute();

echo "Empleado insertado correctamente";

// Cerrar la conexión
$stmt->close();
$conexion->close();
?>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?php
// Crear conexión
$conexion = mysqli_connect("localhost", "root", "", "employees");

// Comprobar conexión
if (!$conexion) {
    die("Conexión fallida: " . mysqli_connect_error());
}

// Preparar y ejecutar el INSERT
$id = 99999;
$nombre = "Juan";
$apellido1 = "Pérez";
$fecha_contratacion = "2025-03-30";
$fecha_nacimiento = "1990-01-01";
$genero = "M";

$query = "INSERT INTO employees (emp_no, birth_date, first_name, last_name, hire_date, gender) 
        VALUES ($id, '$fecha_nacimiento', '$nombre', '$apellido1', '$fecha_contratacion', '$genero')";
// Ejecutar la consulta
if (mysqli_query($conexion, $query)) {
    echo "Empleado insertado correctamente";
} else {
    echo "Error al insertar: " . mysqli_error($conexion);
}

// Cerrar la conexión
mysqli_close($conexion);
?>

En el ejemplo anterior, con MySQLi (orientado a objetos) hemos utilizado una consulta preparada para evitar SQL Injection. La función bind_param se utiliza para vincular los parámetros a la consulta. Veamos uno a uno los métodos utilizados y sus parámetros:

  • $connection->prepare($query): Prepara la consulta SQL para su ejecución. La consulta $query contiene marcadores de posición (?) que serán reemplazados por los valores reales más adelante. Este método devuelve un objeto de declaración ($stmt de tipo stament) que se utilizará para ejecutar la consulta.
  • $stmt->bind_param("isssss", $id, $fecha_nacimiento, $nombre, $apellido1, $apellido2, $fecha_contratacion, $genero): Vincula los parámetros a la consulta preparada. El primer argumento es una cadena que indica el tipo de cada parámetro:
    • s: string
    • d: double
    • i: integer
    • b: blob (binary data)
  • $stmt->execute(): Ejecuta la consulta preparada con los parámetros vinculados. Este método devuelve true si la ejecución fue exitosa o false en caso contrario.
  • $stmt->affected_rows: Devuelve el número de filas afectadas por la última consulta ejecutada. En este caso, nos dirá cuántos registros fueron insertados. Podemos utilizarlo para verificar si la inserción fue exitosa.
  • $conexion->close(): Cierra la declaración y libera los recursos asociados a ella.

Ahora vemos cómo insertar el mismo empleado utilizando PDO.

Ejemplo de INSERTcon PDO

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?php
// Crear conexión
$dsn = "mysql:host=localhost;dbname=employees;charset=utf8mb4";
$usuario = "root";
$contraseña = "";
$conexion = new PDO($dsn, $usuario, $contraseña);
// Configurar el modo de error de PDO
$conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Preparar y ejecutar el INSERT    
$id = 99999;
$nombre = "Juan";
$apellido1 = "Pérez";
$fecha_contratacion = "2025-03-30";
$fecha_nacimiento = "1990-01-01";
$genero = "M";
$query = "INSERT INTO employees (emp_no, birth_date, first_name, last_name, hire_date, gender) 
        VALUES (:id, :fecha_nac, :nombre, :apellido1, :fecha_contratacion, :genero)";
$stmt = $conexion->prepare($query);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->bindParam(':fecha_nac', $fecha_nacimiento, PDO::PARAM_STR);
$stmt->bindParam(':nombre', $nombre), PDO::PARAM_STR);
$stmt->bindParam(':apellido1', $apellido1, PDO::PARAM_STR);
$stmt->bindParam(':fecha_contratacion', $fecha_contratacion, PDO::PARAM_STR);
$stmt->bindParam(':genero', $genero, PDO::PARAM_STR);
$stmt->execute();
echo "Empleado insertado correctamente";
// Cerrar la conexión
$conexion = null;
?>

En el ejemplo anterior, hemos utilizado PDO para conectarnos a la base de datos y ejecutar la consulta. La función bindParam se utiliza para vincular los parámetros a la consulta. A continuación, te explicamos los métodos utilizados:

  • new PDO($dsn, $usuario, $contraseña): Crea una nueva instancia de la clase PDO y establece la conexión a la base de datos. El parámetro $dsn contiene la información de conexión (host, nombre de la base de datos y codificación de caracteres).
  • $conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION): Configura el modo de error de PDO para lanzar excepciones en caso de errores. Esto es útil para manejar errores de manera más efectiva.
  • $conexion->prepare($query): Prepara la consulta SQL para su ejecución. La consulta $query contiene marcadores de posición (:nombre, :apellido, etc.) que serán reemplazados por los valores reales más adelante. Este método devuelve un objeto de declaración ($stmt) que se utilizará para ejecutar la consulta.
  • $stmt->bindParam(':nombre', $nombre, $data_type): Vincula el parámetro :nombre a la variable $nombre. Esto permite que el valor de $nombre se utilice en la consulta preparada.
    • PDO::PARAM_INT: Especifica que el parámetro es un entero. Otros tipos de datos son PDO::PARAM_STR para cadenas y PDO::PARAM_BOOL para booleanos. Para fechas se utiliza PDO::PARAM_STR.
  • $stmt->execute(): Ejecuta la consulta preparada con los parámetros vinculados. Este método devuelve true si la ejecución fue exitosa o false en caso contrario.
  • $stmt->rowCount(): Devuelve el número de filas afectadas por la última consulta ejecutada. En este caso, nos dirá cuántos registros fueron insertados. Podemos utilizarlo para verificar si la inserción fue exitosa.
  • null: Cierra la conexión a la base de datos al establecer la variable $conexion a null. Esto libera los recursos asociados a la conexión.
  • errorInfo(): Devuelve información sobre el error ocurrido durante la ejecución de la consulta. Si no hubo errores, devuelve un array con información sobre la última operación realizada.

UPDATE: Cómo actualizar datos correctamente

La instrucción UPDATE se utiliza para modificar los registros existentes en una tabla. A continuación, te mostramos cómo actualizar el salario de un empleado. Veremos que las funciones y métodos utilizados en el ejemplo son similares a los utilizados en el INSERT.

En este ejemplo vamos a actualizar el salario del empleado con employee_id igual a 99999. Vamos a modificar el apellido a "González".

Ejemplo de UPDATE con MySQLi (orientado a objetos) y procedimental:

Ejemplo de UPDATE con MySQLi

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
// Crear conexión
$conexion = new mysqli("localhost", "root", "", "employees");

// Comprobar conexión
if ($conexion->connect_error) {
    die("Conexión fallida: " . $conexion->connect_error);
}

// Preparar y ejecutar el UPDATE
$apellido = "González";
$empleado_id = 99999;

$query = "UPDATE employees SET last_name = ? WHERE emp_no = ?";
$stmt = $conexion->prepare($query);
$stmt->bind_param("si", $apellido, $empleado_id);
$stmt->execute();

echo "Salario actualizado correctamente";

// Cerrar la conexión
$stmt->close();
$conexion->close();
?>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
// Crear conexión
$conexion = mysqli_connect("localhost", "root", "", "employees");

// Comprobar conexión
if (!$conexion) {
    die("Conexión fallida: " . mysqli_connect_error());
}

// Preparar y ejecutar el UPDATE
$apellido = "González";
$empleado_id = 1;

$query = "UPDATE employees SET last_name = $apellido WHERE emp_no = $empleado_id";

if (mysqli_query($conexion, $query)) {
    echo "Salario actualizado correctamente";
} else {
    echo "Error al actualizar: " . mysqli_error($conexion);
}

// Cerrar la conexión
mysqli_close($conexion);
?>

Al igual que en el punto anteior, el ejemplo procedimental utiliza una consulta SQL sin parámetros, lo que puede ser vulnerable a SQL Injection. En cambio, el ejemplo orientado a objetos utiliza una consulta preparada con parámetros vinculados, lo que es más seguro. Puedes investigar como utiliazar consultas preparadas con MySQLi procedimental en la documentación oficial.

Utilzando PDO el código sería el siguiente:

Ejemplo de UPDATE con PDO

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
<?php
// Crear conexión
$dsn = "mysql:host=localhost;dbname=employees;charset=utf8mb4";
$usuario = "root";
$contraseña = "";
$conexion = new PDO($dsn, $usuario, $contraseña);
// Configurar el modo de error de PDO
$conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Preparar y ejecutar el UPDATE    
$apellido = "González";
$empleado_id = 99999;
$query = "UPDATE employees SET last_name = :salario_nuevoapellido WHERE emp_no = :empleado_id";
$stmt = $conexion->prepare($query);
$stmt->bindParam(':apellido', $apellido);
$stmt->bindParam(':emp_no', $empleado_id);
$stmt->execute();
echo "Salario actualizado correctamente";
// Cerrar la conexión
$conexion = null;
?>

DELETE: Cómo eliminar registros de manera segura

La instrucción DELETE se utiliza para eliminar registros de una tabla. A continuación, te mostramos cómo eliminar un empleado de la base de datos. Para ello vamos a eliminar el empleado creado en el ejemplo anterior.

Ejemplo de DELETE con MySQLi (orientado a objetos) y procedimental:

En este ejemplo vamos a elimnar el empleado con employee_id igual a 99999.

Ejemplo DELETE con MySQLi

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php
// Crear conexión
$conexion = new mysqli("localhost", "root", "", "employees");

// Comprobar conexión
if ($conexion->connect_error) {
    die("Conexión fallida: " . $conexion->connect_error);
}

// Preparar y ejecutar el DELETE
$empleado_id = 99999;

$query = "DELETE FROM employees WHERE emp_no = ?";
$stmt = $conexion->prepare($query);
$stmt->bind_param("i", $empleado_id);
$stmt->execute();

echo "Empleado eliminado correctamente";

// Cerrar la conexión
$stmt->close();
$conexion->close();
?>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php
// Crear conexión
$conexion = mysqli_connect("localhost", "root", "", "employees");

// Comprobar conexión
if (!$conexion) {
    die("Conexión fallida: " . mysqli_connect_error());
}

// Preparar y ejecutar el DELETE
$empleado_id = 99999;

$query = "DELETE FROM employees WHERE emp_no = $empleado_id";

if (mysqli_query($conexion, $query)) {
    echo "Empleado eliminado correctamente";
} else {
    echo "Error al eliminar: " . mysqli_error($conexion);
}

// Cerrar la conexión
mysqli_close($conexion);
?>

En el ejemplo anterior, hemos utilizado una consulta preparada con parámetros vinculados para eliminar un empleado de la tabla employees. Al igual que en los ejemplos anteriores, el ejemplo orientado a objetos es más seguro que el procedimental. Utilizando PDO el código sería el siguiente:

Ejemplo DELETE con PDO

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
<?php
// Crear conexión
$dsn = "mysql:host=localhost;dbname=employees;charset=utf8mb4";
$usuario = "root";
$contraseña = "";
$conexion = new PDO($dsn, $usuario, $contraseña);
// Configurar el modo de error de PDO
$conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Preparar y ejecutar el DELETE    
$empleado_id = 99999;
$query = "DELETE FROM employees WHERE emp_no = :empleado_id";
$stmt = $conexion->prepare($query);
$stmt->bindParam(':empleado_id', $empleado_id);
$stmt->execute();
echo "Empleado eliminado correctamente";
// Cerrar la conexión
$conexion = null;
?>

Ejercicios prácticos:

A continuación se presentan una serie de ejercicios prácticos para que puedas poner en práctica lo aprendido en esta unidad. Son ejercicios simples y directos, al final del tema ya realizaremos ejercicios más complejos.

En estos ejercicios tendrás que buscar un código libre para el empleado y el departamento. Puedes utilizar la función MAX(emp_no) para obtener el último código de empleado y sumarle 1. Para el departamento puedes utilizar la función MAX(dept_no) para obtener el último código de departamento y sumarle 1. Más adelante cuando veamos consultas veremos como elconseguir el código libre desde la propia aplicación.

  1. Ejercicio 1: Verifica que la base de datos employees se ha importado correctamente. Puedes hacerlo utilizando VSCode, phpMyAdmin o cualquier otra herramienta de gestión de bases de datos.
  2. Ejercicio 2: Crea un nuevo departamento en la tabla departments con datos de ejemplo: "Formación". Bucca en la base de datos un código libre para el departamento, por ejemplo 99999.
    • Pista: Puedes utilizar la función MAX(dept_no) para obtener el último código de departamento y sumarle 1.
  3. Ejercicio 3: Verifica que los ejemplos de código funcionan correctamente. Ten en cuenta que las base de datos pueden haber sufrido algún cambio.
  4. Ejercicio 4: Inserta un nuevo empleado en la tabla employees con datos inventados, deberás buscar primero un código libre para el empleado. Puedes utilizar la función MAX(emp_no) para obtener el último código de empleado y sumarle 1.
  5. Ejercicio 5: Modifica el nombre y la fecha de nacimiento del empleado que acabas de insertar.
  6. Ejercicio 6: Elimina el empleado y el departamento que has creado anteriormente. Verifica que se han eliminado correctamente.
  7. Ejercicio 7: Ahora intenta crear un departamento con un código repetido, intenta actualizar un departamento que no existe o eliminar un departamento que tenga empleados asignados. Todas estas operaciones deberían fallar. Verifica que se han producido los errores esperados, analiza los mensajes de error y piensa como debería actuar la aplicación ante estos errores.