Analizamos bases de datos y el lenguaje SQL. (Parte 3) - "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.

• revisando la tarea
  • Agregar una clave principal
  • Agregar Moldavia
  • Crear la tabla de ciudades
  • Agregar una clave externa a las ciudades
  • Parte extra. Pruebas
• SELECCIONAR declaración
  • DONDE parámetro
  • ORDENAR POR parámetro
  • Parámetro GRUPO POR
• Tarea
• Conclusión

Hola señoras y señores, sigamos hablando de bases de datos, SQL y otras cosas. El material de hoy contendrá en parte teoría y en parte práctica. Permítanme recordarles que la última vez hablamos sobre cómo configurar todo, cómo crear una base de datos, una tabla y obtener datos de ella. Es hora de ver si algo funcionó con la detección remota. En mi opinión, la mitad de esto se podría haber hecho sólo basándose en el artículo anterior. Resultó que para ensamblar correctamente una aplicación y hacer que todo sea más o menos hermoso, es necesario hablar de bases de datos, y es necesario dedicar mucho tiempo a hablar de ellas.

revisando la tarea

Un gran respeto a todos los que completaron con éxito las tareas. Esto significa que entiendes que sólo tú lo necesitas y sólo te ayuda. Para aquellos que han descuidado mi tarea, permítanme recordarles la condición:

1. Debe agregar una clave principal (CLAVE PRIMARIA) del campo ID al esquema de la tabla de países.
2. Agregue otro país a la tabla de países: Moldavia.
3. Según el esquema del artículo anterior, crea una tabla ciudad, que contendrá todos los campos que se describen. Los nombres de los campos serán los siguientes: id, nombre, id_país, población.
4. Agregue una clave principal a la tabla de ciudades.
5. Agregue una clave externa a la tabla de ciudades.

Para comenzar, usemos la primera parte del artículo anterior y vayamos a la terminal de la base de datos.

Agregar una clave principal

Puede agregar una clave principal (PRIMARY KEY) de dos maneras: inmediatamente al crear una tabla o después de su creación, usando ALTER TABLE.

Clave principal durante la creación de la tabla

Como ya hemos creado una tabla, y sin eliminarla no podremos mostrar este enfoque dentro de esta base de datos, simplemente crearemos una base de datos de prueba temporal en la que haremos todo. Ingresemos los siguientes comandos:

• crear una nueva base de datos:

  $CREAR BASE DE DATOS prueba;

• cree una tabla agregando una clave principal:

  $ CREAR TABLA país(id INT, nombre VARCHAR(30), CLAVE PRIMARIA (id));

En general, nada complicado. Luego de declarar las variables, se agrega la siguiente parte PRIMARY KEY (id) , donde se pasa entre paréntesis el nombre del campo que será la clave primaria. Y veamos cómo ha cambiado el esquema de la tabla: $ DESC país; Como puede ver, el valor PRI ha aparecido en el campo Clave para la entrada de identificación .

Clave principal después de la creación de la tabla

Como dije antes, la primera clave después de crear una tabla se puede asignar usando ALTER TABLE . Ejecutaremos este ejemplo en nuestra base de datos de ciudades :

• vayamos a nuestra base de datos desde la de prueba:

  $USE ciudades;

• Comprobemos que definitivamente estamos en nuestra base de datos (debería haber otro campo allí: población). Para ello escribimos:

  $ población DESC;

Todo está correcto, la mesa es nuestra. Escribamos lo siguiente:

$ ALTER TABLE país AGREGAR CLAVE PRIMARIA (id);

• y compruébalo inmediatamente con el comando:

  $DESC país;

Como puedes ver en la imagen, todo está correcto, el valor PRI está exactamente donde debería estar. Por cierto, trabajamos con una base de datos de prueba. Ahora necesitamos eliminarlo: ¿por qué necesitamos saturar el servidor, verdad? Para hacer esto, usamos un comando bastante conocido: $ DROP DATABASE test;

Agregar Moldavia

Primero debemos decidir qué grabaremos. Nuestro próximo ID será 4. El nombre será Moldavia y su población es 3550900. Por lo tanto, ejecutamos el comando INSERT INTO que ya conocemos: $ INSERT INTO country VALUES (4, 'Moldavia', 3550900); Y comprobamos si este valor está exactamente en la base de datos: $ SELECT * FROM country WHERE id = 4; En la solicitud de datos, determiné inmediatamente en qué campo se buscaría, por lo que solo obtuvimos un registro, que es lo que necesitábamos.

Crear la tabla de ciudades

Usando el diagrama del primer artículo sobre la base de datos, obtenemos la información necesaria sobre la tabla. Contendrá los siguientes campos:

• id - identificador único;
• nombre — nombre de la ciudad;
• country_id — clave externa del país;
• población - población de la ciudad.

Es un poco estresante escribir una identificación única cada vez, ¿no crees? Me gustaría dejar esto a las autoridades de MySQL . Y existe tal manera: INCREMENTO AUTOMÁTICO . Necesitamos agregar esto al campo digital, y si no pasamos explícitamente los valores, el propio MySQL aumentará el ID en uno en comparación con el anterior. Por lo tanto, crear una tabla se verá así: $ CREATE TABLE ciudad (id INT AUTO_INCREMENT, nombre VARCHAR(30), country_id INT, población INT, PRIMARY KEY (id)); Veamos el diagrama de la tabla para ver si todo se hizo correctamente: $ DESC ciudad; Como puede ver en el diagrama de la tabla, tenemos una nueva descripción para el campo de identificación: auto_increment. Entonces hicimos todo bien. Comprobemos los datos en una tabla completamente configurada. Para hacer esto, haremos la última parte de la tarea: la clave externa.

Agregar una clave externa a las ciudades

Para una clave externa habrá este comando: $ ALTER TABLE ciudad AGREGAR CLAVE EXTRANJERA (id_país) REFERENCIAS país(id); Y comprobemos inmediatamente qué está mal con el esquema de la tabla: ¿ha cambiado en el transcurso de una hora? $DESC ciudad;

Parte extra. Pruebas

Olvidé agregarlo a la tarea: complete los datos que estaban en la captura de pantalla de la primera parte. Lo olvidé, así que ahora lo haré yo mismo. Y para aquellos que estén interesados, pueden hacerlo ustedes mismos sin mí y luego lo comprobaremos ;) Estaban Jarkov, Kiev, Minsk, Odessa, Voronezh y también agregaremos Chisinau. Pero esta vez no transmitiremos ID, los omitiremos: $ INSERT INTO city (nombre, country_id, población) VALUES ('Kharkov', 1, 1443000), ('Kyiv', 1, 3703100), ('Minsk' , 3, 2545500), ('Odessa', 1, 1017699), ('Voronezh', 2, 1058261), ('Kishinev', 4, 695400); Como puede ver, puede realizar varias entradas simultáneamente usando un comando INSERT INTO. Algo útil, recuerda) E inmediatamente veamos qué hay en la tabla: $ SELECT * FROM city; AUTO_INCREMENT : funcionó exactamente como queríamos. Todos los archivos de identificación están completos, aunque no los enviamos. Una clave externa es algo dependiente. Para comprobar si funciona correctamente, puede intentar escribir una clave externa que no exista en la tabla externa. Digamos que decidimos que id = 5 es Kazajstán. Pero en realidad no está en la tabla de países. Y para comprobar que la base de datos jura, agregue la ciudad - Astana: $ INSERT INTO city (nombre, country_id, población) VALUES ('Astana', 5, 1136156); Y naturalmente obtenemos el error: ahora la clave externa se asegura de que no intentemos asignar un país a la ciudad que no esté en nuestra base de datos. Esta parte de la tarea se puede considerar completada; adelante a la nueva :)

SELECCIONAR declaración

Bueno, ya no todo parece tan aterrador, ¿verdad? Me gustaría señalar una vez más que para los desarrolladores de Java, el conocimiento de la base de datos es imprescindible. Sin una base de datos no puedes ir a ninguna parte. Sí, ya quiero empezar a redactar una solicitud, estoy de acuerdo. Pero es necesario. Así que seguiremos así. Usando la declaración SELECT, recuperamos datos de la base de datos. Es decir, esta es una operación típica de DML (¿ya has olvidado qué es?...))) Vuelve a leer los artículos ANTES). ¿Cuáles son los beneficios de las bases de datos relacionales? Tienen una gran funcionalidad para agregar y recuperar datos. Para esto se utiliza la declaración SELECT. Parecería que no puede haber nada complicado en ello, ¿verdad? Pero resulta que todavía queda mucho por entender. Es importante para nosotros comprender los conceptos básicos a partir de los cuales podemos construir. La consulta más simple con una declaración SELECT es seleccionar todos los datos de una tabla. Realmente me gustó la descripción de la wiki sobre exactamente en qué orden deben ir los operadores en una consulta SELECT, así que la copiaré descaradamente aquí:

SELECT
  [DISTINCT | DISTINCTROW | ALL]
  select_expression,...
FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula}]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]

Aquí puedes ver que no puedes poner primero el operador GROUP BY y luego el operador WHERE. Hay que recordar esto para que luego no haya resentimiento por errores que no están claros de dónde vienen. $SELECCIONAR * DE ciudad; Pero extraer todos los datos claramente no es divertido para nosotros. Esto es exactamente lo mismo si quisiéramos clavar clavos con un microscopio [1] , [2] . Dado que la base de datos realiza operaciones de filtrado, clasificación y agregación mucho más rápido que el código Java, es mejor dejar este asunto en manos de la base de datos. Por tanto, al complicar las tareas abriremos nuevas funcionalidades.

DONDE parámetro

Para filtrar una selección, se utiliza la palabra WHERE . Esto debe interpretarse de la siguiente manera: SELECT * FROM tablename (selecciona todos los campos de la tabla tablename) WHERE talbe_row = 1 (donde en los registros el campo table_row es igual a 1). Es importante señalar que el orden de las palabras clave en la consulta es importante. No puede escribir WHERE a =1 FROM nombre_tabla SELECT *. Para el idioma ruso esto está bien y para algunos puede que no parezca tan malo, pero para SQL esto es inaceptable. Escribimos la siguiente consulta: $ SELECT * FROM ciudad DONDE country_id = 1; Y elegimos ciudades ucranianas. No está mal, ¿verdad? ¿Y si queremos no sólo a Ucrania, sino también a Bielorrusia? Para ello, podemos enumerar la colección de valores que puede tomar el campo: $SELECT * FROM city WHERE country_id IN(1, 3); Y ya tenemos ciudades de dos países en respuesta. ¿Qué pasa si hay varias condiciones para filtrar? ¿Digamos que queremos ciudades con una población de más de dos millones? Para hacer esto, use las palabras OR y AND : $ SELECT * FROM ciudad DONDE country_id IN (1, 3) AND población > 2000000; Genial, pero ¿qué pasa si necesitamos agregar una condición más: buscar nombres a través de una expresión regular (no describiré expresiones regulares aquí: aquí hay una persona que hizo esto "brevemente" en 4 partes )? Por ejemplo, recordamos cómo se escribe una ciudad, pero no completamente... Para hacer esto, puede agregar la palabra clave LIKE en la expresión de filtrado : $ SELECT * FROM city WHERE country_id IN (1, 3) AND población > 2000000 O nombre COMO “%hark%”; Y así conseguimos también Jarkov. Como resultado, podemos decir que nuestra búsqueda resultó muy buena. Pero me gustaría ordenar no por ID, sino por población, pero ¿cómo? Sí, muy sencillo...

ORDENAR POR parámetro

Usando ORDER BY, podemos ordenar los registros que recibimos por un campo específico. Ordena tanto números como cadenas. Ampliemos la consulta anterior, ordenemos por población, agregando ORDEN POR población: $ SELECT * FROM city WHERE country_id IN (1, 3) AND población > 2000000 OR nombre COMO “%hark%” ORDEN POR población; Como podemos ver, la clasificación se produjo en orden natural, es decir, en orden ascendente. ¿Y si queremos lo contrario? Para hacer esto, necesita agregar la palabra DESC: $ SELECCIONAR * DESDE la ciudad DONDE country_id EN (1, 3) Y población > 2000000 O nombre COMO “%hark%” ORDENAR POR población DESC; Ahora la clasificación se basa en la reducción de la población. Y la base de datos lo hace muy rápidamente: no se puede comparar ningún Collections.sort . Ahora ordenemos por fila, por nombre en orden inverso: $ SELECCIONAR * DESDE ciudad DONDE country_id EN (1, 3) Y población > 2000000 O nombre COMO “%hark%” ORDENAR POR nombre DESC;

Parámetro GRUPO POR

Se utiliza para agrupar registros por campos específicos. Esto generalmente es necesario para usar funciones agregadas... ¿Qué son las funciones agregadas?)) Tiene sentido agrupar por algunos campos si son iguales para diferentes registros. Veamos lo que esto significa usando nuestro ejemplo. Digamos que las ciudades tienen claves externas: identificaciones de países. Entonces, el ID es el mismo para ciudades del mismo país. Por lo tanto, puede tomar y agrupar registros por ellos: $ SELECT country_id, COUNT(*) FROM city GROUP BY country_id; Pero sin agregar funciones parece un poco deslucido, debes admitir. Por tanto, veamos algunas de las funciones más comunes:

• COUNT: número de registros, se puede usar sin agrupar, se usa como COUNT(*) . En el caso de agrupar por algún campo - COUNT(groupped_field);
• MAX: encuentra el valor máximo para un campo específico;
• MIN: encuentra el valor mínimo para un campo específico;
• SUMA: encuentra la suma de un campo específico;
• AVG: encuentra el valor promedio.

En general, estas funciones se pueden utilizar sin agrupar, solo así se mostrará un solo campo. Probémoslos para la población de nuestra ciudad: $ SELECT COUNT(*) FROM city; Lo que pidieron fue lo que obtuvieron. Sólo el número de registros. A veces esto es útil. Por ejemplo, si necesitamos saber el número de artículos de un determinado autor. No es necesario sacarlos de la base de datos y contarlos. Simplemente puedes usar COUNT(). $ SELECCIONE PROMEDIO (población) DE la ciudad; $ SELECCIONE MIN(población) DE la ciudad; Y aquí es donde entra en vigor la agrupación. Por ejemplo, la tarea es conseguir la ciudad más pequeña del país. ¿Ya sabes cómo hacer esto? Pruébelo usted mismo y luego observe: $ SELECT country_id as Country, MIN(poblation) FROM city WHERE GROUP BY country_id; Hasta ahora sólo vemos el documento de identidad del país, pero no importa: la próxima vez lo haremos todo. Y ya hay un resultado y obtuvimos lo que queríamos: la ciudad más pequeña del país con ID = 1. El resto de las funciones serán las mismas. Es importante tener en cuenta que rastrillar todos los campos hasta * cuando se utiliza agrupación y agregación no funcionará. Piénsalo ;)

Tarea

Según los resultados de los artículos anteriores, está claro que se están haciendo los deberes, así que continuemos)) Sí, todos los que hagan los deberes seguirán poniendo “+” en los comentarios. Para mí es importante que el tema de los deberes te resulte interesante para poder seguir haciéndolo en el futuro. Sí, leo tus comentarios regularmente. Por supuesto, respondo con menos frecuencia. Vi que pidieron dar problemas SQL más difíciles. Hasta que aprendamos las uniones, no habrá problemas interesantes, por lo que habrá aquellos que necesito para obtener más material.

Tareas:

Comprenda el operador HAVING y escriba una consulta de ejemplo para las tablas de nuestro ejemplo. Si necesitas agregar algunos campos o más valores para que quede más claro, agrégalos. Si alguien quiere, escriba su solución de ejemplo en los comentarios: de esta manera también puedo comprobarla si tengo tiempo.
1. Instale MySQL Workbench para trabajar con la base de datos a través de la interfaz de usuario. Creo que ya hemos practicado suficiente trabajando desde la consola. Conéctese a la base de datos. Si utiliza otra cosa para trabajar con la base de datos, no dude en omitir esta tarea. Aquí y más usaré sólo MySQL Workbench.
2. Escribir solicitudes para recibir utilizando nuestros datos:
   1. el país más pequeño/más poblado;
   2. número promedio de habitantes del país;
   3. número medio de habitantes en países cuyos nombres terminan en “a”;
   4. el número de países con una población de más de cuatro millones;
   5. ordenar los países por número decreciente de habitantes;
   6. ordenar los países por nombre en orden natural.

Conclusión

Hoy discutimos en detalle la tarea de la última lección. Es más, lo considero importante tanto para los que no lo hicieron como para los que sí lo hicieron. Para el primero, esta es una oportunidad para encontrar la respuesta y para el segundo, para compararla con su resultado. Suscríbase a mi cuenta de GitHub para mantenerse actualizado sobre los cambios en el proyecto. Mantendré toda la base del código allí. Todo sucederá en esta organización . A continuación, analizamos la declaración SELECT. Él es el más importante para nosotros. Es a través de él que pasarán todas las solicitudes de datos y debemos entenderlo. Lo más importante es recordar el orden en que se agregan los parámetros (DÓNDE, ORDENAR POR, AGRUPAR POR, etc.). Sí, no dije todo lo que era posible, pero no me propuse ese objetivo. Sí, sé que ya estás ansioso por escribir una solicitud. Ten paciencia, esto es todo lo que necesitas. Tanto para el proyecto como para tu crecimiento profesional. Mientras espera, asegúrese de que Git ya le resulte familiar. Lo usaré por defecto, como una herramienta conocida. Gracias a todos por leer. En el próximo artículo hablaremos sobre conexiones y uniones de bases de datos. Ahí es donde estarán las tareas interesantes))

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