Analizamos bases de datos y el lenguaje SQL. (Parte 5 - conexiones y uniones) - "Proyecto Java de la A a la Z"

Un artículo de una serie sobre la creación de un proyecto Java (los enlaces a otros materiales se encuentran al final). Su objetivo es analizar tecnologías clave y el resultado es escribir un bot de Telegram.

• Tipos de relaciones en la base de datos.
  • Uno a muchos
  • Uno a uno (uno a uno)
  • Muchos a muchos
• Conexiones (Uniones)
  • UNIR INTERNAMENTE
  • UNIRSE A LA IZQUIERDA
  • ÚNETE DERECHO
  • Asegurar uniones
• Tarea

Hola a todos, futuros Seniors y Senioritas del software. Como dije en la parte anterior ( revisando los deberes ), hoy habrá material nuevo. Para aquellos que están especialmente ansiosos, he desenterrado una tarea interesante para que aquellos que ya lo saben todo y aquellos que no lo saben pero quieren buscarlo en Google puedan practicar y poner a prueba sus habilidades. Hoy hablaremos de tipos de conexiones y uniones.

Tipos de relaciones en la base de datos.

Para comprender qué son las relaciones, es necesario recordar qué es una clave externa. Para aquellos que lo olvidaron, bienvenidos al comienzo de la serie .

Uno a muchos

Recordemos nuestro ejemplo con países y ciudades. Está claro que una ciudad debe tener un país. ¿Cómo vincular un país a una ciudad? Es necesario adjuntar a cada ciudad un identificador único (ID) del país al que pertenece: esto ya lo hemos hecho. Esto se llama uno de los tipos de conexiones: uno a muchos (también sería bueno conocer la versión en inglés: uno a muchos). Parafraseando, podemos decir: varias ciudades pueden pertenecer a un mismo país. Así es como debes recordarlo: una relación de uno a muchos. Hasta aquí está claro, ¿verdad? Si no, aquí tenéis la primera imagen de Internet: Se ve que hay clientes y sus pedidos. Tiene sentido que un cliente pueda tener más de un pedido. Hay uno a muchos :) U otro ejemplo: Hay tres tablas: editor, autor y libro. Toda editorial que no quiere arruinarse y quiere tener éxito tiene más de un autor, ¿no crees? A su vez, cada autor puede tener más de un libro, de eso tampoco cabe duda. Y esto significa, nuevamente, la conexión de un autor con muchos libros, un editor con muchos autores . Hay muchos más ejemplos que se pueden dar. La dificultad en la percepción al principio tal vez sólo resida en aprender a pensar de manera abstracta: mirar desde afuera las mesas y su interacción.

Uno a uno (uno a uno)

Se puede decir que este es un caso especial de comunicación uno a muchos. Situación en la que un registro de una tabla está relacionado con un solo registro de otra tabla. ¿Qué ejemplos puede haber en la vida? Si excluimos la poligamia, entonces podemos decir que existe una relación uno a uno entre marido y mujer. Aunque incluso si decimos que la poligamia está permitida, cada esposa puede tener un solo marido. Lo mismo puede decirse de los padres. Cada persona puede tener sólo un padre biológico y una sola madre biológica. Relación explícita uno a uno. Mientras escribía esto se me ocurrió una idea: ¿por qué entonces dividir una relación uno a uno en dos registros en tablas diferentes, si ya tienen una relación uno a uno? La respuesta la encontré yo mismo. Estos registros también pueden estar vinculados a otros registros de otras maneras. ¿De qué estoy hablando? Otro ejemplo de conexiones uno a uno es el que existe entre el país y el presidente. ¿Es posible anotar todos los datos sobre el presidente en la tabla “país”? Sí, puedes, SQL no dice una palabra. Pero si piensas que el presidente también es una persona... Y puede que también tenga esposa (otra relación uno a uno) e hijos (otra relación uno a muchos) y entonces resulta que será necesario conectar al país con la esposa y los hijos del presidente…. Suena loco, ¿verdad? :D Puede haber muchos otros ejemplos de esta conexión. Además, en tal situación, puede agregar una clave externa a ambas tablas, a diferencia de una relación de uno a muchos.

Muchos a muchos

Ya por el nombre, puedes adivinar de qué hablaremos. A menudo en la vida, y programamos nuestras vidas, hay situaciones en las que los tipos de conexiones anteriores no son suficientes para describir las cosas que necesitamos. Ya hemos hablado de editoriales, libros y autores. Hay tantas conexiones aquí... Cada publicación puede tener varios autores: una conexión de uno a muchos. Al mismo tiempo, cada autor puede tener varias editoriales (por qué no, el escritor publicó en un lugar, tuvo una pelea por dinero, se fue a otra editorial, por ejemplo). Y ésta es nuevamente una relación de uno a muchos. O esto: cada autor puede tener varios libros, pero cada libro también puede tener varios autores. Nuevamente, una relación de uno a muchos entre autor y libro, libro y autor. De este ejemplo podemos sacar una conclusión más formalizada: Quedó claro cómo configurar los tipos de conexión anteriores en SQL: simplemente pasamos el ID de esa a esos registros, que son muchos, ¿no? Un país proporciona su identificación como clave externa para muchas ciudades. ¿Qué hacer con las relaciones de muchos a muchos ? Este método no es adecuado. Necesitamos agregar otra tabla que conecte las dos tablas. Por ejemplo, vayamos a MySQL, creemos una nueva base de datos manytomany, creemos dos tablas, autor y libro, que contendrán solo nombres y sus ID: CREAR BASE DE DATOS manytomany; UTILIZAR muchos a muchos; CREAR TABLA autor (id INT AUTO_INCREMENT, nombre VARCHAR(100), CLAVE PRIMARIA (id)); CREAR TABLA libro (id INT AUTO_INCREMENT, nombre VARCHAR(100), CLAVE PRIMARIA (id)); Ahora creemos una tercera tabla que tendrá dos claves externas de nuestras tablas de autor y libro, y este enlace será único. Es decir, no será posible agregar un registro con las mismas claves dos veces: CREAR TABLA autores_x_libros (libro_id INT NOT NULL, id_autor INT NOT NULL, CLAVE EXTRANJERA (id_libro) REFERENCIAS libro(id), LLAVE EXTRANJERA (id_autor) REFERENCIAS autor (id), ÚNICO (book_id, autor_id)); Aquí utilizamos varias características nuevas que deben comentarse por separado:

• NOT NULL significa que siempre hay que rellenar el campo, y si no lo hacemos SQL nos lo indicará;
• UNIQUE dice que un campo o un conjunto de campos deben ser únicos en la tabla. Suele ocurrir que además del identificador único, un campo más debe ser único para cada registro. Y UNIQUE es responsable exactamente de este asunto.

Según mi práctica: al pasar de un sistema antiguo a uno nuevo, nosotros, como desarrolladores, debemos almacenar los ID del sistema anterior para trabajar con él y crear el nuestro. ¿Por qué crear el tuyo propio y no utilizar los antiguos? Es posible que no sean lo suficientemente únicos o que este enfoque para crear identificaciones ya no sea relevante y limitado. Para ello, hemos hecho que el antiguo nombre de identificación también sea único en la tabla. Para comprobar esto, debe agregar datos. Agregue un libro y un autor: NSERT INTO book (nombre) VALUES ("libro1"); INSERTAR EN VALORES del autor (nombre) ("autor1"); Ya sabemos por artículos anteriores que tendrán los ID 1 y 1. Por lo tanto, podemos agregar inmediatamente un registro a la tercera tabla: INSERTAR EN autores_x_libros VALORES (1,1); Y todo estará bien hasta que queramos repetir el último comando nuevamente: es decir, volver a escribir los mismos ID: El resultado será natural: un error. Habrá un duplicado. La entrada no quedará registrada. Así es como se creará una conexión de muchos a muchos... Todo esto es muy interesante e interesante, pero surge una pregunta lógica: ¿cómo obtener esta información? ¿Cómo combinar datos de diferentes tablas y obtener una respuesta? De esto es de lo que hablaremos en la siguiente parte))

Conexiones (Uniones)

En la parte anterior, te preparé para que entendieras de inmediato qué son las uniones y dónde usarlas. Porque estoy profundamente convencido de que tan pronto como llegue la comprensión, todo se volverá muy simple de inmediato y todos los artículos sobre uniones serán claros como los ojos de un bebé :D En términos generales y en general, las uniones obtienen el resultado de varias tablas por medio de un JOIN (unirse desde inglés unirse). Y eso es todo...) Y para unirse, es necesario especificar el campo mediante el cual se unirán las tablas. El diablo no da tanto miedo como lo pintan, ¿verdad?) A continuación, hablaremos sobre qué tipos de uniones existen y cómo usarlas. Hay muchos tipos de uniones y no los consideraremos todos. Sólo aquellos que realmente necesitamos. Por eso no nos interesan uniones tan exóticas como Cross y Natural. Lo olvidé por completo, debemos recordar un matiz más: las tablas y los campos pueden tener alias : seudónimos. Se utilizan convenientemente para uniones. Por ejemplo, puedes hacer esto: SELECT * FROM table1; si la consulta utilizará con frecuencia la tabla1, entonces puede darle un alias: SELECT* FROM table1 as t1; o incluso más fácil de escribir: SELECT * FROM table1 t1; y luego, más adelante en la consulta, será posible usar t1 como alias para esta tabla.

UNIR INTERNAMENTE

La unión más común y sencilla. Dice que cuando tengamos dos tablas y un campo por el cual se pueda unir, se seleccionarán todos los registros cuyas relaciones existan en las dos tablas. Era difícil decirlo de alguna manera. Veamos un ejemplo: agreguemos un registro a la base de datos de nuestras ciudades. Una entrada para ciudades y otra para países: $ INSERT INTO country VALUES(5, "Uzbekistán", 34036800); y $ INSERT INTO ciudad (nombre, población) VALUES("Tbilisi", 1171100); Hemos agregado un país que no tiene ciudad en nuestra tabla y una ciudad que no está asociada con un país en nuestra tabla. Entonces, INNER JOIN se dedica a emitir todos los registros para aquellas conexiones que están en dos tablas. Así es como se ve la sintaxis general cuando queremos unir dos tablas tabla1 y tabla2: SELECT * FROM table1 t1 INNER JOIN table2 ON t1.id = t2.t1_id; y luego se devolverán todos los registros que tengan una relación en las dos tablas. Para nuestro caso, cuando queramos recibir información de países junto con ciudades, el resultado será así: $ SELECT * FROM city ci INNER JOIN country co ON ci.country_id = co.id; Aquí, aunque los nombres son iguales, se puede ver claramente que primero vienen los campos de ciudades, luego los campos de países. Pero las dos entradas que agregamos arriba no están ahí. Porque así es exactamente como funciona INNER JOIN.

UNIRSE A LA IZQUIERDA

Hay casos, y con bastante frecuencia, en los que no estamos satisfechos con la pérdida de campos de la tabla principal debido a que no hay ningún registro para ello en la tabla adyacente. Para esto sirve una UNIÓN IZQUIERDA. Si en nuestra solicitud anterior especificamos IZQUIERDA en lugar de INNER, agregaremos otra ciudad en la respuesta: Tbilisi: $ SELECT * FROM city ci LEFT JOIN country co ON ci.country_id = co.id; Hay una nueva entrada sobre Tbilisi y todo lo relacionado con el país está en nulo . Así es como se usa a menudo.

ÚNETE DERECHO

Aquí habrá una diferencia con LEFT JOIN en que todos los campos no se seleccionarán a la izquierda, sino a la derecha en la conexión. Es decir, no se tomarán ciudades, sino todos los países: $ SELECT * FROM city ci RIGHT JOIN country co ON ci.country_id = co.id; Ahora está claro que en este caso no habrá Tbilisi, sino Uzbekistán. Algo como eso…))

Asegurar uniones

Ahora quiero mostrarles una imagen típica que los jóvenes preparan antes de una entrevista para convencerlos de que entienden la esencia de las uniones: aquí todo se muestra en forma de conjuntos, cada círculo es una tabla. Y esos lugares donde se pinta son aquellas partes que se mostrarán en SELECCIONAR. Miremos:

• INNER JOIN es solo la intersección de conjuntos, es decir, aquellos registros que tienen conexiones con dos tablas: A y B;
• LEFT JOIN son todos los registros de la tabla A, incluidos todos los registros de la tabla B que tienen una intersección (conexión) con A;
• RIGHT JOIN es exactamente lo opuesto a LEFT JOIN: todos los registros de la tabla B y los registros de A que tienen una relación.

Después de todo esto, esta imagen debería quedar clara))

Tarea

¡Esta vez las tareas serán muy interesantes y todos aquellos que las resuelvan con éxito pueden estar seguros de que están listos para comenzar a trabajar en el lado SQL! Las tareas no están masticadas y fueron escritas para estudiantes de secundaria, por lo que no será fácil ni aburrido para ti :) Te daré una semana para que hagas las tareas tú mismo y luego publicaré un artículo separado con un análisis detallado. de la solución a las tareas que te di.

La tarea real:

1. Escriba un script SQL para crear la tabla 'Estudiante' con los siguientes campos: id (clave principal), nombre, apellido, correo electrónico (único).
2. Escriba un script SQL para crear la tabla 'Libro' con los siguientes campos: id, título (id + título = clave principal). Vincule 'Estudiante' y 'Libro' con una relación de 'Libro' uno a muchos de 'Estudiante'.
3. Escriba un script SQL para crear la tabla 'Profesor' con los siguientes campos: id (clave principal), nombre, apellido, correo electrónico (único), asunto.
4. Vincule 'Estudiante' y 'Profesor' con una relación de 'Estudiante' de muchos a muchos Profesor.
5. Seleccione 'Estudiante' que tenga 'oro' en su apellido, por ejemplo 'Sid oro v', 'V oro novsky'.
6. Seleccione de la tabla 'Estudiante' todos los apellidos ('apellido') y el número de sus repeticiones. Considere que hay homónimos en la base de datos. Ordenar por cantidad en orden descendente. Debe tener un aspecto como este:

   apellido	cantidad
   petrov	15
   Ivánov	12
   Sidorov	3

7. Seleccione los 3 nombres más repetidos de 'Estudiante'. Ordenar por cantidad en orden descendente. Debe tener un aspecto como este:

   nombre	cantidad
   Alejandro	27
   serguéi	10
   Pedro	7

8. Seleccione 'Estudiantes' que tengan mayor número de 'Libro' y 'Profesor' asociados. Ordene por cantidad en orden descendente. Debe tener un aspecto como este:

   Apellido del profesor	Apellido del estudiante	cantidad del libro
   petrov	Sidorov	7
   Ivánov	Herrero	5
   petrov	kankava	2>

9. Seleccione el 'Profesor' que tenga la mayor cantidad de 'Libros' de todos sus 'Estudiantes'. Ordenar por cantidad en orden descendente. Debe tener un aspecto como este:

   Apellido del profesor	cantidad del libro
   petrov	9
   Ivánov	5

10. Seleccione 'Profesor' cuyo número de 'Libro' para todos sus 'Estudiantes' esté entre 7 y 11. Ordenar por cantidad en orden descendente. Debe tener un aspecto como este:

    Apellido del profesor	cantidad del libro
    petrov	once
    Sidorov	9
    Ivánov	7

11. Imprima todos los 'apellidos' y 'nombres' de todos los 'Profesores' y 'Estudiantes' con el campo 'tipo' (estudiante o profesor). Ordenar alfabéticamente por 'apellido'. Debe tener un aspecto como este:

    apellido	tipo
    Ivánov	alumno
    kankava	maestro
    Herrero	alumno
    Sidorov	maestro
    petrov	maestro

12. Agregue una columna de 'tasa' a la tabla 'Estudiante' existente, que almacenará el curso en el que se encuentra actualmente el estudiante (valor numérico del 1 al 6).
13. Este artículo no es obligatorio, pero será una ventaja. Escriba una función que revise todos los 'Libros' y genere todos los 'títulos' separados por comas.

Conclusión

La serie sobre la base de datos se ha prolongado un poco. Aceptar. Sin embargo, hemos recorrido un largo camino y como resultado ¡surgimos con conocimiento del asunto! Gracias a todos por leer, les recuerdo que todos los que quieran seguir adelante y seguir el proyecto deben crear una cuenta en GitHub y suscribirse a mi cuenta :) Más por venir: hablemos de Maven y Docker. Gracias a todos por leer. Repito una vez más: el que camina dominará el camino ;)

Al principio de este artículo encontrará una lista de todos los materiales de la serie.