Trifulcas

Un ejemplo más

package com.trifulcas.country;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Pagos2006 {

	public static void main(String[] args) {
		// Calcula los pagos realizados en 2006 y muestralo por la consola
		// Paso 1: SQL me voy al workbench
		String sql = "SELECT sum(amount) total FROM payment where year(payment_date)=2006 group by year(payment_date)";

		// Paso 2, monto la conexión y toda la mandanga
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "");
			// Es lo mismo usar normal o preparado
			PreparedStatement st = con.prepareStatement(sql);
			// No añado parámetros, directo al resultset
			ResultSet rs = st.executeQuery();
			if (rs.next()) {
				Double total = rs.getDouble("total");
				System.out.println(total);
			} else {
				System.out.println("No hay pagos en esa fecha");
			}

		} catch (Exception ex) {
			System.out.println(ex.getMessage());

		}
	}

}

PreparedStatement

package com.trifulcas.country;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Scanner;

public class StatementPreparado {

	public static void main(String[] args) {
		// ¿Qué es un procedimiento preparado?
		// Es la manera habitual hoy en día de hacer statements
		// Hemos visto en los ejemplos que las sentencias sql
		// normalmente las combinamos con variables
		// p. ej. "insert into category(name) values ('"+categoria+"')"
		// Lo puedo concatenar como estoy haciendo hasta ahora
		// pero puedo utilizar en vez de eso PreparedStatements
		// que son statements a los que les puedo pasar variables
		// 1.- Claridad, yo separo el sql de los valores
		// 2.- Eficiencia. Cuando hago un procedimiento preparado, si se repite la BD ya tiene cacheado el esquema
		// 3.- Protección contra inyección sql. Yo no sé que tiene la variable, puede tener sentencias sql
		// ¿Cómo hacer un procedimiento preparado? Muy parecido a normal
		Scanner scanner = new Scanner(System.in);
		System.out.println("Introduzca el nombre del país");
		String pais = scanner.nextLine();
		try {
			 Class.forName("com.mysql.cj.jdbc.Driver");
			 Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "");
			 // Yo creo un sql y allá donde tengo el parámetro (donde metía la variable)
			 // Pongo un interrogante
			 String sql="insert into country (country) values(?)";
			 // En vez de Statement uso PreparedStatement
	         PreparedStatement psst=con.prepareStatement(sql);
	         // Añado los valores de los parámetros
	         // Posición, valor
	         psst.setString(1, pais);
	         // Ejecuto
	         int res=psst.executeUpdate();
	         System.out.println("Se han insertado "+res+" registros");
	         psst.close();
	         con.close();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}
	}

}

package com.trifulcas.country;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class AddActors {

	public static void main(String[] args) {
		Connection con = null;
		PreparedStatement st = null;
		Scanner scanner = new Scanner(System.in);
		System.out.println("Introduzca el número de actores");
		int cantidad = scanner.nextInt();
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "");
			String sql = "insert into actor (first_name,last_name) values (?,?);";
			st = con.prepareStatement(sql);
			for (int i = 1; i <= cantidad; i++) {
				System.out.println(sql);
				st.setString(1, "nombre"+i);
				st.setString(2, "apellido"+i);
				int res = st.executeUpdate(sql);
				System.out.println(res + " registros afectados");
			}
			st.close();
			con.close();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());

		} finally {
			try {
				st.close();
				con.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}

			scanner.close();
		}

	}

}

package com.trifulcas.country;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Scanner;

public class AddCategory {

	public static void main(String[] args) {
		Scanner scanner = new Scanner(System.in);
		System.out.println("Introduzca el nombre de la categoría");
		String categoria = scanner.nextLine();
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "");
			String sql = "insert into category (name) values(?)";
			PreparedStatement st = con.prepareStatement(sql);
			st.setString(1, categoria);
			System.out.println(st);
			int res = st.executeUpdate();
			System.out.println(res + " registros afectados");
			st.close();
			con.close();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}
	}

}

package com.trifulcas.country;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Scanner;

public class AddCountry {

	public static void main(String[] args) {
		Scanner scanner = new Scanner(System.in);
		System.out.println("Introduzca el nombre del país");
		String pais = scanner.nextLine();
		try {
			 Class.forName("com.mysql.cj.jdbc.Driver");
			 Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "");
			 String sql="insert into country (country) values(?)";
			 PreparedStatement st=con.prepareStatement(sql);
			 st.setString(1, pais);
	         int res=st.executeUpdate();
	         System.out.println("Se han insertado "+res+" registros");
	         st.close();
	         con.close();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}
	}

}

package com.trifulcas.country;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Scanner;

public class DeleteCountry {

	public static void main(String[] args) {
		Scanner scanner = new Scanner(System.in);
		System.out.println("Introduzca el id del país a eliminar");
		String pais = scanner.nextLine();
		try {
			 Class.forName("com.mysql.cj.jdbc.Driver");
			 Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "");
	          String sql="delete from country where country_id=?";
			 PreparedStatement st=con.prepareStatement(sql);
			 st.setString(1, pais);
	         int res=st.executeUpdate();
	         System.out.println("Se han eliminado "+res+" registros");
	         st.close();
	         con.close();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}

	}

}

package com.trifulcas.country;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

public class GetCountries {

	public static void main(String[] args) {
		// Buscar todos los países que tengan un texto determinado
		Scanner scanner = new Scanner(System.in);
		// Pido el texto
		System.out.println("Introduzca el texto a buscar");
		String texto = scanner.nextLine();
		try {
			// Hago lo estándar
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "");
			// ¿Qué sql necesito?
			String sql = "select * from country where country like ?";
			System.out.println(sql);
			PreparedStatement st = con.prepareStatement(sql);
			st.setString(1, '%'+texto+'%');
			// Aquí no modificamos sino que seleccionamos, por lo tanto usamos
			// executeQuery para ejecutar y un ResultSet para almacenar los resultados
			ResultSet rs = st.executeQuery();
			// Si yo hago un bucle recorro todos los registros
			while (rs.next()) {
				System.out.println(rs.getString("country"));
				System.out.println(rs.getDate("last_update"));
			}
			// Al tener el tipo TYPE_SCROLL_INSENSITIVE puedo ir a registros determinados

			st.close();
			con.close();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}
		scanner.close();
	}

}

Resultset

package com.trifulcas.country;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class GetCountries {

	public static void main(String[] args) {
		// Buscar todos los países que tengan un texto determinado
		Scanner scanner = new Scanner(System.in);
		// Pido el texto
		System.out.println("Introduzca el texto a buscar");
		String texto = scanner.nextLine();
		try {
			// Hago lo estándar
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "");
			Statement st = con.createStatement();
			// ¿QUé sql necesito?
			String sql = "select * from country where country like '%"+texto+"%'";
			System.out.println(sql);
			// Aquí no modificamos sino que seleccionamos, por lo tanto usamos
			// executeQuery para ejecutar y un ResultSet para almacenar los resultados
			ResultSet rs=st.executeQuery(sql);
			// rs es un cursor, puntero o flecha, como lo queramos llamar
			// a los registros de la base de datos
			rs.next(); // Esto me coloca el cursor en el primer elemento
			System.out.println(rs.getString(2)); // Esto me imprime el nombre del primer país
			rs.next(); // Esto me coloca el cursor en el primer elemento
			System.out.println(rs.getString("country")); // Esto me imprime el nombre del segundo país
			// Si yo hago un bucle recorro todos los registros
			while(rs.next()) {
				System.out.println(rs.getString("country")); 			
			}	
			st.close();
			con.close();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}
		scanner.close();
	}

}

package com.trifulcas.country;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class GetCountries {

	public static void main(String[] args) {
		// Buscar todos los países que tengan un texto determinado
		Scanner scanner = new Scanner(System.in);
		// Pido el texto
		System.out.println("Introduzca el texto a buscar");
		String texto = scanner.nextLine();
		try {
			// Hago lo estándar
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "");
			Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			// ¿Qué sql necesito?
			String sql = "select * from country where country like '%"+texto+"%'";
			System.out.println(sql);
			// Aquí no modificamos sino que seleccionamos, por lo tanto usamos
			// executeQuery para ejecutar y un ResultSet para almacenar los resultados
			ResultSet rs=st.executeQuery(sql);
			// rs es un cursor, puntero o flecha, como lo queramos llamar
			// a los registros de la base de datos
			rs.next(); // Esto me coloca el cursor en el primer elemento
			System.out.println(rs.getString(2)); // Esto me imprime el nombre del primer país
			rs.next(); // Esto me coloca el cursor en el primer elemento
			System.out.println(rs.getString("country")); // Esto me imprime el nombre del segundo país
			// Si yo hago un bucle recorro todos los registros
			while(rs.next()) {
				System.out.println(rs.getString("country")); 			
			}	
			// Al tener el tipo TYPE_SCROLL_INSENSITIVE puedo ir a registros determinados
			rs.first();
			System.out.println(rs.getString("country")); // Esto me imprime el nombre del primer país
			rs.last();
			System.out.println(rs.getString("country")); // Esto me imprime el nombre del último país
			
			rs.absolute(4);
			System.out.println(rs.getString("country")); // Esto me imprime el nombre del último país
			
			st.close();
			con.close();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}
		scanner.close();
	}

}

Lo habitual es hacerlo así:

ResultSet rs=st.executeQuery(sql);
			// Si yo hago un bucle recorro todos los registros
			while(rs.next()) {
				System.out.println(rs.getString("country")); 			
			}

Soluciones ejercicios

package com.trifulcas.country;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Scanner;

public class AddCategory {

	public static void main(String[] args) {
		Scanner scanner = new Scanner(System.in);
		System.out.println("Introduzca el nombre de la categoría");
		String categoria = scanner.nextLine();
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "");
			Statement st = con.createStatement();
			String sql = "insert into category (name) values ('" + categoria + "')";
			System.out.println(sql);
			int res = st.executeUpdate(sql);
			System.out.println(res + " registros afectados");
			st.close();
			con.close();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}
	}

}

package com.trifulcas.country;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class UpdatePayments {

	public static void main(String[] args) {
			try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "");
			Statement st = con.createStatement();
			
			String sql = "update payment set amount=amount+0.5";
			System.out.println(sql);
			int res = st.executeUpdate(sql);
			System.out.println(res + " registros afectados");
			st.close();
			con.close();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}
	}

}

package com.trifulcas.country;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Scanner;

public class AddActors {

	public static void main(String[] args) {
		Scanner scanner = new Scanner(System.in);
		System.out.println("Introduzca el número de actores");
		int cantidad = scanner.nextInt();
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "");
			Statement st = con.createStatement();
			for (int i = 1; i <= cantidad; i++) {
				String sql = "insert into actor (first_name,last_name) values ('actor"+i+"','actor"+i+"');";
				System.out.println(sql);
				int res = st.executeUpdate(sql);
				System.out.println(res + " registros afectados");
			}
			st.close();
			con.close();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}
	}

}

Ejemplos JDBC

package com.trifulcas.country;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Scanner;

public class AddCountry {

	public static void main(String[] args) {
		Scanner scanner = new Scanner(System.in);
		System.out.println("Introduzca el nombre del país");
		String pais = scanner.nextLine();
		try {
			 Class.forName("com.mysql.cj.jdbc.Driver");
			 Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "");
	         Statement st=con.createStatement();
	         int res=st.executeUpdate("insert into country (country) values('"+pais+"')");
	         System.out.println("Se han insertado "+res+" registros");
	         st.close();
	         con.close();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}
	}

}

package com.trifulcas.country;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Scanner;

public class DeleteCountry {

	public static void main(String[] args) {
		Scanner scanner = new Scanner(System.in);
		System.out.println("Introduzca el id del país a eliminar");
		String pais = scanner.nextLine();
		try {
			 Class.forName("com.mysql.cj.jdbc.Driver");
			 Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "");
	         Statement st=con.createStatement();
	         int res=st.executeUpdate("delete from country where country_id="+pais);
	         System.out.println("Se han eliminado "+res+" registros");
	         st.close();
	         con.close();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}

	}

}

package com.trifulcas.country;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Scanner;

public class UpdateCountry {

	public static void main(String[] args) {
		Scanner scanner = new Scanner(System.in);
		System.out.println("Introduzca el id del país a modificar");
		String id = scanner.nextLine();
		System.out.println("Introduzca el nuevo nombre");
		String pais = scanner.nextLine();
		try {
			 Class.forName("com.mysql.cj.jdbc.Driver");
			 Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "");
	         Statement st=con.createStatement();
	         int res=st.executeUpdate("update country set country='"+pais+"' where country_id="+id);
	         System.out.println("Se han modificado "+res+" registros");
	         st.close();
	         con.close();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}

	}

}

Ejemplo JDBC

package com.trifulcas.testconexion;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestConectar {

	public static void main(String[] args) {
		System.out.println("Hola que tal?");
		try {
			// Registrar la clase jdbc, imprescindible
			Class.forName("com.mysql.cj.jdbc.Driver");
			// Aquí creamos la conexión. Tiene tres parámetros:
			// url o cadena de conexión
			// usuario
			// contraseña
			// Analicemos un poco la cadena: jdbc:mysql://localhost:3306/sakila
			// jdbc es el protocolo de conexión (driver)
			// mysql la base de datos a la que me conecto
			// localhost la url del servidor
			// 3306 el puerto
			// sakila la base de datos a la que me qiero conectar
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "");
			System.out.println("Conexión correcta");
			// A partir de aquí ya podemos lanzar sentencias sql al servidor de la base de
			// datos
			// Lo primero que necesitamos es un statement (java.sql.statement)
			Statement stmt = con.createStatement();
			// Un statement nos permite lanzar sentencias sql
			// Sentencia de inserción, no tenemos datos para recuperar
			stmt.execute("insert into country(country) values ('Beluchistán')");

			// Si queremos acceder a los datos de la base de datos usamos
			// executeQuery y un ResultSet que nos guarda el resultado de la consulta
			// Para poder recorrer los valores que se retornan

			// Ejecuto la sentencia SQL y guardo el resultado en un ResultSet (que tenemos
			// que importar)
			ResultSet rs = stmt.executeQuery("SELECT * FROM country");
			// Recorremos el resultset con next(). Cuando se llega al final next devuelve
			// falso
			// Porque no hay un siguiente registro
			rs.next();
			System.out.println("ID: " + rs.getInt(1));
			System.out.println("País: " + rs.getString(2));
			while (rs.next()) {
				// Accedemos a cada una de las columnas, como Java es un lenguaje tipado
				// Tenemos que escoger el get adecuado al tipo de la columna
				// En este caso la primera columna es el id, un int
				// Y la segunda es el country, un string
				System.out.println("ID: " + rs.getInt(1));
				System.out.println("País: " + rs.getString(2));
				System.out.println("ID: " + rs.getInt("country_id"));
				System.out.println("País: " + rs.getString("country"));
			}

			// cerramos todo
			rs.close();
			stmt.close();
			con.close();
		} catch (Exception e) {

			System.out.println(e.getMessage());
		}

	}

}

Conexión base de datos

Para conectarnos desde java a las bases de datos tenemos varias opciones. Empezaremos con la más sencilla y en días sucesivos veremos otras más complejas.

En un proyecto Java podemos añadir el conector mysql. Primero lo tenemos que descargar, si queréis lo podéis hacer desde aquí:

https://github.com/juanpablofuentes/JavaNetmind/tree/main/MiPrimerProyecto/src

https://dev.mysql.com/downloads/connector/j/

Botón derecho guardar archivo mysql connector

Creamos un nuevo proyecto (para no mezclar) y añadimos este jar a las librerías de nuestro proyecto. Un manual:

https://www.knowprogram.com/jdbc/connect-mysql-database-eclipse/

Una vez lo tengamos añadido podemos conectarnos a nuestra base de datos con la cadena de conexión, de manera parecida a como lo hacemos en el workbench, indicando la url del servidor, la base de datos, y el usuario y la contraseña:

	Class.forName("com.mysql.cj.jdbc.Driver");
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "");

A partir de aquí podemos acceder a la base de datos:


Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select * from actor");
			while (rs.next()) {
				System.out.println(rs.getInt(1) + "  " + rs.getString(2) + "  " + rs.getString(3));
			}
			rs = stmt.executeQuery("select * from actor");
			while (rs.next()) {
				System.out.println(
						rs.getInt("actor_id") + "  " + rs.getString("first_name") + "  " + rs.getString("last_name"));
			}

			int result=stmt.executeUpdate("insert into actor (first_name, last_name) values ('Eva','Pi')");  
			System.out.println(result+" filas afectadas");

			PreparedStatement ps = con.prepareStatement("select * from actor where first_name like ?");
			ps.setString(1, "%ar%");
			rs = ps.executeQuery();
			while (rs.next()) {
				System.out.println(
						rs.getInt("actor_id") + "  " + rs.getString("first_name") + "  " + rs.getString("last_name"));
			}

Solución ejercicio completo

Base de datos

CREATE TABLE `cliente` (
  `idcliente` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nombre` varchar(145) DEFAULT NULL,
  `dni` varchar(45) DEFAULT NULL,
  `mail` varchar(45) DEFAULT NULL,
  `password` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idcliente`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `empleado` (
  `idempleado` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nombre` varchar(145) DEFAULT NULL,
  `dni` varchar(45) DEFAULT NULL,
  `mail` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idempleado`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `llamada` (
  `idllamada` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `idcliente` int(10) unsigned DEFAULT NULL,
  `idempleado` int(10) unsigned DEFAULT NULL,
  `fechahora` datetime DEFAULT NULL,
  `duracion` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`idllamada`),
  KEY `fk_cliente_idx` (`idcliente`),
  KEY `fk_empleado_idx` (`idempleado`),
  CONSTRAINT `fk_cliente` FOREIGN KEY (`idcliente`) REFERENCES `cliente` (`idcliente`) ON UPDATE NO ACTION,
  CONSTRAINT `fk_empleado` FOREIGN KEY (`idempleado`) REFERENCES `empleado` (`idempleado`) ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Datos de prueba:

INSERT INTO `cliente` (`idcliente`, `nombre`, `dni`, `mail`, `password`) VALUES
(1, 'Juan Perez', '12345678A', 'juan.perez@example.com', 'password123'),
(2, 'Maria Lopez', '23456789B', 'maria.lopez@example.com', 'password456'),
(3, 'Carlos Garcia', '34567890C', 'carlos.garcia@example.com', 'password789'),
(4, 'Ana Fernandez', '45678901D', 'ana.fernandez@example.com', 'password012'),
(5, 'Luis Martinez', '56789012E', 'luis.martinez@example.com', 'password345'),
(6, 'Elena Gomez', '67890123F', 'elena.gomez@example.com', 'password678'),
(7, 'Pedro Sanchez', '78901234G', 'pedro.sanchez@example.com', 'password901'),
(8, 'Sofia Ramirez', '89012345H', 'sofia.ramirez@example.com', 'password234'),
(9, 'Miguel Torres', '90123456I', 'miguel.torres@example.com', 'password567'),
(10, 'Laura Morales', '01234567J', 'laura.morales@example.com', 'password890');

INSERT INTO `empleado` (`idempleado`, `nombre`, `dni`, `mail`) VALUES
(1, 'Alejandro Rodriguez', '11223344A', 'alejandro.rodriguez@example.com'),
(2, 'Beatriz Sanchez', '22334455B', 'beatriz.sanchez@example.com'),
(3, 'Cristina Martinez', '33445566C', 'cristina.martinez@example.com'),
(4, 'David Fernandez', '44556677D', 'david.fernandez@example.com'),
(5, 'Eva Gonzalez', '55667788E', 'eva.gonzalez@example.com'),
(6, 'Francisco Lopez', '66778899F', 'francisco.lopez@example.com'),
(7, 'Gabriela Herrera', '77889900G', 'gabriela.herrera@example.com'),
(8, 'Hector Ruiz', '88990011H', 'hector.ruiz@example.com'),
(9, 'Irene Castillo', '99001122I', 'irene.castillo@example.com'),
(10, 'Javier Ortega', '00112233J', 'javier.ortega@example.com');

INSERT INTO `llamada` (`idllamada`, `idcliente`, `idempleado`, `fechahora`, `duracion`) VALUES
(1, 1, 2, '2024-01-01 09:00:00', 15),
(2, 3, 4, '2024-01-01 10:30:00', 30),
(3, 2, 1, '2024-01-01 11:00:00', 20),
(4, 5, 3, '2024-01-02 14:00:00', 25),
(5, 6, 5, '2024-01-02 15:45:00', 10),
(6, 4, 6, '2024-01-03 08:15:00', 35),
(7, 7, 8, '2024-01-03 09:45:00', 40),
(8, 8, 7, '2024-01-04 12:30:00', 50),
(9, 9, 9, '2024-01-04 13:15:00', 5),
(10, 10, 10, '2024-01-05 16:00:00', 45);

INSERT INTO `llamada` (`idllamada`, `idcliente`, `idempleado`, `fechahora`, `duracion`) VALUES
(11, 1, 3, '2024-01-05 17:30:00', 20),
(12, 2, 4, '2024-01-06 08:45:00', 15),
(13, 3, 5, '2024-01-06 09:15:00', 25),
(14, 4, 6, '2024-01-07 11:30:00', 30),
(15, 5, 7, '2024-01-07 12:00:00', 40),
(16, 6, 8, '2024-01-08 14:45:00', 50),
(17, 7, 9, '2024-01-08 15:00:00', 10),
(18, 8, 10, '2024-01-09 16:15:00', 35),
(19, 9, 1, '2024-01-09 17:00:00', 45),
(20, 10, 2, '2024-01-10 09:00:00', 20),
(21, 1, 3, '2024-01-10 09:45:00', 15),
(22, 2, 4, '2024-01-11 10:30:00', 25),
(23, 3, 5, '2024-01-11 11:00:00', 30),
(24, 4, 6, '2024-01-12 12:30:00', 40),
(25, 5, 7, '2024-01-12 13:15:00', 50),
(26, 6, 8, '2024-01-13 14:00:00', 10),
(27, 7, 9, '2024-01-13 14:45:00', 35),
(28, 8, 10, '2024-01-14 15:30:00', 45),
(29, 9, 1, '2024-01-14 16:15:00', 20),
(30, 10, 2, '2024-01-15 17:00:00', 15),
(31, 1, 3, '2024-01-15 08:45:00', 25),
(32, 2, 4, '2024-01-16 09:15:00', 30),
(33, 3, 5, '2024-01-16 10:00:00', 40),
(34, 4, 6, '2024-01-17 11:00:00', 50),
(35, 5, 7, '2024-01-17 11:45:00', 10),
(36, 6, 8, '2024-01-18 12:30:00', 35),
(37, 7, 9, '2024-01-18 13:15:00', 45),
(38, 8, 10, '2024-01-19 14:00:00', 20),
(39, 9, 1, '2024-01-19 14:45:00', 15),
(40, 10, 2, '2024-01-20 15:30:00', 25);

INSERT INTO `empleado` (`idempleado`, `nombre`, `dni`, `mail`) VALUES
(11, 'Karla Paredes', '10293847K', 'karla.paredes@example.com'),
(12, 'Luis Dominguez', '56473829L', 'luis.dominguez@example.com');


INSERT INTO `cliente` (`idcliente`, `nombre`, `dni`, `mail`, `password`) VALUES
(11, 'Natalia Herrera', '11223344K', 'natalia.herrera@example.com', 'password112'),
(12, 'Roberto Diaz', '22334455L', 'roberto.diaz@example.com', 'password223');

INSERT INTO `llamada` (`idllamada`, `idcliente`, `idempleado`, `fechahora`, `duracion`) VALUES
(41, 1, 2, '2023-12-15 10:30:00', 20),
(42, 2, 3, '2023-11-20 14:00:00', 30),
(43, 3, 4, '2023-10-25 16:45:00', 15),
(44, 4, 5, '2023-09-30 08:00:00', 25),
(45, 5, 6, '2023-08-10 09:15:00', 40),
(46, 6, 7, '2023-07-05 11:30:00', 35),
(47, 7, 8, '2023-06-15 13:45:00', 50),
(48, 8, 9, '2023-05-20 15:00:00', 10),
(49, 9, 10, '2023-04-25 17:15:00', 45),
(50, 10, 1, '2023-03-30 19:30:00', 20),
(51, 11, 2, '2022-12-15 10:30:00', 15),
(52, 12, 3, '2022-11-20 14:00:00', 25),
(53, 1, 4, '2022-10-25 16:45:00', 30),
(54, 2, 5, '2022-09-30 08:00:00', 40),
(55, 3, 6, '2022-08-10 09:15:00', 50),
(56, 4, 7, '2022-07-05 11:30:00', 10),
(57, 5, 8, '2022-06-15 13:45:00', 35),
(58, 6, 9, '2022-05-20 15:00:00', 45),
(59, 7, 10, '2022-04-25 17:15:00', 20),
(60, 8, 1, '2022-03-30 19:30:00', 15);

Consultas

-- Empleados sin llamadas
select empleado.* from empleado left join llamada on empleado.idempleado=llamada.idempleado
where idllamada is null;

select * from empleado where idempleado not in (select idempleado from llamada);

-- Total de llamadas por empleado

select empleado.*, count(idllamada) total
 from empleado left join llamada on empleado.idempleado=llamada.idempleado
 group by idempleado;
 
-- Total de llamadas por cliente

select cliente.*, count(idllamada) total
 from cliente left join llamada on cliente.idcliente=llamada.idcliente
 group by idcliente;
 
-- Cliente con la llamada de mayor duración

select distinct cliente.*, duracion from
cliente join llamada on cliente.idcliente=llamada.idcliente
order by duracion desc
limit 1;

-- bien hecho

select distinct cliente.*, duracion from
cliente join llamada on cliente.idcliente=llamada.idcliente
where duracion=(select max(duracion) from llamada);

-- Total de llamadas por año

select year(fechahora) anyo, count(idllamada) total
from llamada
group by anyo;

Grandes inventos de la humanidad

Ver esta publicación en Instagram

Una publicación compartida de Elle Cordova (@ellecordova)

Ejemplos procedimientos almacenados

-- Dentro de sql lo que tenemos es dos maneras
-- de incorporar programación: funciones y procedimientos
-- ¿Para qué existen? Porque hay veces que queremos hacer cosas
-- Y el SQL normal es insuficiente
-- Para tareas de mantenimiento, de control, de lógica de negocio

-- Los procedimientos almacenados se utilizan normalmente para realizar
-- operaciones que van más allá de devolver un resultado
-- Tienen una sintaxis un poco más compleja que las funciones
-- https://proyectoa.com/anadir-y-usar-procedimiento-almacenado-stored-procedure-en-mysql-server/
-- https://www.dolthub.com/blog/2024-01-17-writing-mysql-procedures/

-- Para empezar tenemos parámetros de entrada (IN) y parámetros de salida (OUT)
-- que se definen en el inicio
-- Ejemplo: (IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
-- Cuando llamo al procedimiento almacenado (call) pasaré 2 parámetros y recibiré uno

call film_in_stock(1,1,@total);
select @total;

-- Luego dentro del procedimiento almacenado tengo las mismas opciones
-- que en las funciones. Podemos programar de verdad
-- Tenemos variables, estructuras de control (if) bucles (while)...

CREATE PROCEDURE fibonacci(n int, out answer int)
BEGIN 
    DECLARE i int default 2;
    DECLARE p, q int default 1;
    SET answer = 1;
    WHILE i < n DO
        SET answer = p + q;
        SET p = q;
        SET q = answer;
        SET i = i + 1;
    END WHILE;
END;

call fibonacci(10,@numero);
select @numero;

-- Ejemplo con utilidad
-- Imaginemos que queremos de dar de alta un cliente
-- Antes del cliente tenemos que tener una dirección
-- Esa dirección tiene que tener una ciudad
-- Esa ciudad tiene que tener un país
-- Spain, logroño, carmen medrano 11, pepito perez
-- Para hacer esto tengo que hacer una serie de comprobaciones
-- Y si no de inserciones
-- Esto con un procedimiento almacenado va muy bien

CREATE DEFINER=`root`@`localhost` PROCEDURE `alta_cliente`(
in p_country varchar(50),
in p_city varchar(50),
in p_address varchar(50),
in p_first_name varchar(50),
in p_last_name varchar(50),
out p_customer_id int
)
BEGIN
declare v_country_id int;
declare v_city_id int;
declare v_address_id int;
declare v_customer_id int;

-- Busco el pais que se llame como el parámetro
select country_id into v_country_id from country
where country=p_country;
-- O existe o no existe. Si no existe, lo inserto
IF v_country_id is null then
 insert into country(country) values (p_country);
 select last_insert_id() into v_country_id;
END IF;
-- Al llegar aquí en v_country_id tengo el id del país que me han
-- pasado como parámetro. Bien porque existía y he recuperado ese valor
-- Bien porque no existía, lo he insertado, y he recuperado el id
-- del registro insertado

-- Con la ciudad hacemos algo parecido

select city_id into v_city_id from city
where city=p_city and country_id=v_country_id;
 
IF v_city_id is null then
 insert into city(city,country_id)
 values (p_city,v_country_id);
 select last_insert_id() into v_city_id;
END IF;

-- Igual que antes, al llegar aquí en v_city-id tengo el id de la ciudad

select address_id into v_address_id from address
where address=p_address and city_id=v_city_id;
 
IF v_address_id is null then
 insert into address(address,city_id)
 values (p_address,v_city_id);
 select last_insert_id() into v_address_id;
END IF;

select customer_id into v_customer_id from customer
where first_name=p_first_name and last_name=p_last_name and address_id=v_address_id;
 
IF v_customer_id is null then
 insert into customer(first_name,last_name,address_id,store_id)
 values (p_first_name,p_last_name, v_address_id,1);
 select last_insert_id() into v_customer_id;
END IF;
 
select v_customer_id into p_customer_id;
END

-- ejemplo de uso

call alta_cliente('Spain','Barcelona','Agla 6','Pepito','Perez',@customer_id);
select @customer_id;

call alta_cliente('Spain','Barcelona','Sant Antoni 3','Ana','Pi',@customer_id);
select @customer_id;

call alta_cliente('Trubulandia','Trubulú','Sant Antoni 3','Ana','Pi',@customer_id);
select @customer_id;

Trifulcas

Power BI

Scripts con bash

Minimal APIs

WordPress

Copilot

Estadística con Python