Aprenda a usar varias funciones de MySQL y MariaDB-Parte 2

Aprenda a usar varias funciones de MySQL y MariaDB-Parte 2

Esta es la segunda parte de una serie de 2 artículos sobre los conceptos básicos de los comandos MariaDB/MySQL. Consulte nuestro artículo anterior sobre este tema antes de continuar.

  1. Aprenda los conceptos básicos de MySQL/MariaDB para principiantes-Parte 1

En esta segunda parte de MySQL/En la serie para principiantes de MariaDB, explicaremos cómo limitar el número de filas devueltas por una consulta SELECT. y cómo ordenar el conjunto de resultados en función de una condición determinada.

Además, Aprenderá a agrupar los registros y realizar manipulaciones matemáticas básicas en campos numéricos. Todo esto nos ayudará a crear un script SQL que podamos usar para producir informes útiles.

Requisitos previos

Para comenzar, siga estos pasos:

1.. Descargue la base de datos de muestra de empleados, que incluye seis tablas que constan de 4. millones de registros en total.

# wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2 # tar xjf employee_db-full-1.0.6.tar.bz2 # cd employee_db freestar.config.enabled_slots.push

2.. Ingrese al indicador MariaDB. y cree una base de datos llamada empleados.

# mysql-u root-p. Ingrese la contraseña: Bienvenido al monitor MariaDB. Los comandos terminan con; o \ g. Su ID de conexión MariaDB es 2 Versión del servidor: 10.1.14-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab y otros. Escriba ‘ayuda’; o ‘\ h’ para obtener ayuda. Escriba ‘\ c’ para borrar la declaración de entrada actual. MariaDB [(none)]>. CREATE DATABASE empleados;. Consulta OK, 1 fila afectada (0.00 seg)

3 .. Impórtelo a su servidor MariaDB de la siguiente manera:

MariaDB [(none)]>. source employee.sql.

Espere 1-2 minutos hasta que se cargue la base de datos de muestra (¡tenga en cuenta que estamos hablando de registros 4M. aquí!).

4.. Verifique que la base de datos se haya importado correctamente enumerando sus tablas:

MariaDB [empleados]>. USE empleados;. Base de datos cambiado MariaDB [empleados]>. MOSTRAR TABLAS;. +———————+ | Tables_in_employees | +———————+ | departamentos | | dept_emp | | dept_manager | | empleados | | sueldos | | títulos | +———————+ 6 filas en conjunto (0.02 seg)

5.. Cree una cuenta especial para usar con la base de datos de empleados. (siéntase libre de elegir otro nombre de cuenta y contraseña):

MariaDB [empleados]>. CREAR USUARIO [protegido por correo electrónico] IDENTIFICADO POR ’empadminpass’;. Consulta OK, 0 filas afectadas (0.03 seg) MariaDB [empleados]>. OTORGAR TODOS PRIVILEGIOS SOBRE empleados. * A [email protected];. Consulta OK, 0 filas afectadas (0.02 seg) MariaDB [empleados]>. FLUSH PRIVILEGES ;. Consulta OK, 0 filas afectadas (0.00 seg) MariaDB [empleados]>. salir. Adiós

Ahora inicie sesión como empadmin. al usuario en el indicador de Mariadb.

# mysql-u empadmin-p. Ingrese la contraseña: Bienvenido al monitor MariaDB. Los comandos terminan con; o \ g. Su ID de conexión MariaDB es 4 Versión del servidor: 10.1.14-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab y otros. Escriba ‘ayuda’; o ‘\ h’ para obtener ayuda. Escriba ‘\ c’ para borrar la declaración de entrada actual. MariaDB [(none)]>. USE empleados;. Lectura de la información de la tabla para completar los nombres de las tablas y columnas. Puede desactivar esta función para obtener un inicio más rápido con-A Database cambiado  Aprenda los comandos básicos de MySQL para principiantes  Aprenda los comandos básicos de MySQL para principiantes Aprenda los comandos básicos de MySQL para principiantes

Asegúrese de que se hayan completado todos los pasos descritos en la imagen anterior antes de continuar.

Ordenar y limitar el número de filas en el conjunto de resultados

La tabla de salarios contiene todos los ingresos de cada empleado con fechas de inicio y finalización. Es posible que deseemos ver los salarios de emp_no = 10001 a lo largo del tiempo. Esto ayudará a responder las siguientes preguntas:

  1. ¿Recibió algún aumento?
  2. Si es así, ¿cuándo?

Ejecute la siguiente consulta para averiguarlo:

MariaDB [empleados]>. SELECT * FROM salaries WHERE emp_no = 10001 ORDER BY from_date;. +——–+——–+————+————+ | emp_no | salario | from_date | to_date | +——–+——–+————+————+ | 10001 | 60117 | 1986-06-26 | 1987-06-26 | | 10001 | 62102 | 1987-06-26 | 1988-06-25 | | 10001 | 66074 | 1988-06-25 | 1989-06-25 | | 10001 | 66596 | 1989-06-25 | 1990-06-25 | | 10001 | 66961 | 1990-06-25 | 1991-06-25 | | 10001 | 71046 | 1991-06-25 | 1992-06-24 | | 10001 | 74333 | 1992-06-24 | 1993-06-24 | | 10001 | 75286 | 1993-06-24 | 1994-06-24 | | 10001 | 75994 | 1994-06-24 | 1995-06-24 | | 10001 | 76884 | 1995-06-24 | 1996-06-23 | | 10001 | 80013 | 1996-06-23 | 1997-06-23 | | 10001 | 81025 | 1997-06-23 | 1998-06-23 | | 10001 | 81097 | 1998-06-23 | 1999-06-23 | | 10001 | 84917 | 1999-06-23 | 2000-06-22 | | 10001 | 85112 | 2000-06-22 | 2001-06-22 | | 10001 | 85097 | 2001-06-22 | 2002-06-22 | | 10001 | 88958 | 2002-06-22 | 9999-01-01 | +——–+——–+————+————+ 17 filas en conjunto ( 0.03 seg)

Ahora, ¿qué pasa si necesitamos ver los últimos 5 aumentos? Podemos hacer ORDER BY from_date DESC. La palabra clave DESC. indica que queremos ordenar el conjunto de resultados en orden descendente.

Además, LIMIT 5. nos permite devolver solo los primeros 5. filas en el conjunto de resultados:

MariaDB [empleados]>. SELECT * FROM salaries WHERE emp_no = 10001 ORDER BY from_date LÍMITE DE DESC 5;. +——–+——–+————+———–+ | emp_no | salario | from_date | to_date | +——–+——–+————+————+ | 10001 | 88958 | 2002-06-22 | 9999-01-01 | | 10001 | 85097 | 2001-06-22 | 2002-06-22 | | 10001 | 85112 | 2000-06-22 | 2001-06-22 | | 10001 | 84917 | 1999-06-23 | 2000-06-22 | | 10001 | 81097 | 1998-06-23 | 1999-06-23 | +——–+——–+————+————+ 5 filas en conjunto ( 0.00 s)  Consultar tabla MySQL por fecha Orden  Consultar tabla MySQL por orden de fecha Consultar tabla MySQL por orden de fecha

También puede utilizar ORDER BY. con varios campos. Por ejemplo, la siguiente consulta ordenará el conjunto de resultados según la fecha de nacimiento del empleado en forma ascendente (la predeterminada) y luego por los apellidos en forma alfabética descendente:

MariaDB [empleados]>. SELECT CONCAT (last_name, ‘,’, first_name) AS Name, gender AS Gender, Hire_date AS “Fecha de contratación “DESDE los empleados ORDER BY birth_date, last_name DESC LIMIT 10;. +——————–+——–+————+ | Nombre | Género | Fecha de contratación | +——————–+——–+————+ | Whitcomb, Kiyokazu | M | 1988-07-26 | | Schaad, Ronghao | M | 1988-07-10 | | Remmele, Supot | M | 1989-01-27 | | Pocchiola, Jouni | M | 1985-03-10 | | Kuzuoka, Eishiro | M | 1992-02-12 | | Decaestecker, Moni | M | 1986-10-06 | | Wiegley, Mircea | M | 1985-07-18 | | Vendrig, Sachar | M | 1985-11-04 | | Tsukuda, Cedric | F | 1993-12-12 | | Tischendorf, Percy | M | 1986-11-10 | +——————–+——–+————+ 10 filas en conjunto (0.31 sec)  Consultar tabla MySQL por Fecha de nacimiento  Consultar tabla MySQL por fecha de nacimiento Consultar tabla MySQL por fecha de nacimiento

Puede ver más información sobre LIMIT. aquí.

Agrupación de registros/MAX, MIN, AVG y ROUND

Como mencionamos anteriormente, la tabla de salarios contiene los ingresos de cada empleado a lo largo del tiempo. Además de LIMIT. podemos utilizar las palabras clave MAX. y MIN. para determinar cuándo se contrató el número máximo y mínimo de empleados:

MariaDB [empleados]>. SELECT CONCAT (last_name, ‘,’, first_name) AS Name, MAX (B.salary) AS “Max. Salario” FROM empleados A UNIR salarios B EN A.emp_no = B.emp_no DONDE A.emp_no IN (10001, 10002, 10003) AGRUPAR POR A.emp_no;. +—————–+————-+ | Nombre | Max. salario | +—————–+————-+ | Facello, Georgi | 88958 | | Simmel, Bezalel | 72527 | | Bamford, Parto | 43699 | +—————–+————-+ 3 filas en conjunto (0.02 seg) MariaDB [empleados]>. SELECT CONCAT (last_name, ‘,’, first_name) AS Name, MIN (B.salary) AS” Min. Sary “FROM empleados A JOIN salarios B ON A.emp_no = B.emp_no DONDE A.emp_no IN (10001, 10002, 10003) GRUPO POR A.emp_no;. +—————–+————-+ | Nombre | Min. salario | +—————–+————-+ | Facello, Georgi | 60117 | | Simmel, Bezalel | 65828 | | Bamford, Parto | 40006 | +—————–+————-+ 3 filas en conjunto (0.00 seg)  Agrupación de registros MySQL usando palabras clave MAX y MIN  Agrupación de registros MySQL usando MAX y MIN Keywords Agrupación de registros MySQL usando MAX y MIN Keywords

Según los conjuntos de resultados anteriores, ¿puede adivinar lo que devolverá la consulta siguiente?

MariaDB [empleados]>. SELECT CONCAT (last_name, ‘, ‘, first_name) AS Name, ROUND (AVG (B.salary), 2) AS “Prom. salario” FROM empleados A UNIR salarios B ON A.emp_no = B.emp_no WHERE Ae mp_no IN (10001, 10002, 10003) GRUPO POR A.emp_no;. +—————–+————-+ | Nombre | Promedio salario | +—————–+————-+ | Facello, Georgi | 75388.94 | | Simmel, Bezalel | 68854.50 | | Bamford, Parto | 43030.29 | +—————–+————-+ 3 filas en conjunto (0.01 seg)

Si está de acuerdo en que lo hará devuelva el salario promedio (según lo especificado por AVG. a lo largo del tiempo redondeado a 2 decimales (como lo indica ROUND. , tiene razón.

Si queremos ver la suma de los salarios agrupados por empleado y devolver los primeros 5. podemos utilizar la siguiente consulta:

MariaDB [empleados]>. SELECT emp_no, SUM (salario) AS Salario FROM salarios GROUP BY emp_no ORDER BY Sary DESC LIMIT 5;. +——–+———+ | emp_no | Salario | +——–+———+ | 109334 | 2553036 | | 43624 | 2492873 | | 66793 | 2383923 | | 237542 | 2381119 | | 47978 | 2374024 | +——–+———+ 5 filas en conjunto (2.22 seg)

En la consulta anterior, los salarios se agrupan por empleado y luego se realiza la suma.

Uniendo todo

Afortunadamente, no necesitamos ejecutar consulta tras consulta para producir un informe. En su lugar, podemos crear un script con una serie de comandos SQL para devolver todos los conjuntos de resultados necesarios.

Una vez que ejecutemos el script, devolverá la información requerida sin más intervención de nuestra parte. Por ejemplo, creemos un archivo llamado maxminavg.sql. en el directorio de trabajo actual con el siguiente contenido:

–Seleccione la base de datos USE empleados;–Calcular los salarios máximos SELECCIONAR CONCAT (apellido, ‘,’, primer_nombre) AS Nombre, MAX (B.salario) AS “Salario máximo” DE los empleados A ÚNETE a los salarios B ON A.emp_no = B.emp_no DONDE A.emp_no IN (10001, 10002, 10003) GRUPO POR A.emp_no;–Calcular los salarios mínimos SELECCIONAR CONCAT (apellido, ‘,’, primer nombre) AS Nombre, MIN (B.salario) AS “Salario mínimo” DE los empleados A ÚNETE a los salarios B ON A.emp_no = B.emp_no DONDE A.emp_no IN (10001, 10002, 10003) GRUPO POR A.emp_no;–Calcular promedios, redondear a 2 lugares decimales SELECCIONAR CONCAT (apellido, ‘,’, primer nombre) AS Nombre, ROUND (AVG (B.salario), 2) AS “Salario medio” DE los empleados A ÚNETE a los salarios B EN A. emp_no = B.emp_no DONDE A.emp_no IN (10001, 10002, 10003) GRUPO POR A.emp_no;

Las líneas que comienzan con dos guiones se ignoran y las consultas individuales se ejecutan una tras otra. Podemos ejecutar este script desde la línea de comandos de Linux:

# mysql-u empadmin-p . Ingresar contraseña:. Nombre Max. salario Facello, Georgi 88958 Simmel, Bezalel 72527 Bamford, Parto 43699 Nombre Mín. salario Facello, Georgi 60117 Simmel, Bezalel 65828 Bamford, Parto 40006 Nombre Prom. salario Facello, Georgi 75388.94 Simmel, Bezalel 68854.50 Bamford, Parto 43030.29

o desde el indicador MariaDB:

# mysql-u empadmin-p. Ingrese la contraseña: Bienvenido al monitor MariaDB. Los comandos terminan con; o \ g. Su ID de conexión MariaDB es 4 Versión del servidor: 10.1.14-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab y otros. Escriba ‘ayuda’; o ‘\ h’ para obtener ayuda. Escriba ‘\ c’ para borrar la declaración de entrada actual. MariaDB [(none)]>. source maxminavg.sql. Lectura de la información de la tabla para completar los nombres de las tablas y columnas. Puede desactivar esta función para Obtenga un inicio más rápido con-A Database change Script MySQL para ejecutar comandos SQL  Script MySQL para ejecutar comandos SQL Script MySQL para ejecutar SQL Comandos

Resumen

En este artículo hemos explicado cómo usar varias funciones MariaDB para refinar los conjuntos de resultados devueltos por las sentencias SELECT. Una vez que se han definido, se pueden insertar varias consultas individuales en un script para ejecutarlo más fácilmente y reducir el riesgo de error humano.

¿Tiene alguna pregunta o sugerencia sobre este artículo? No dude en enviarnos una nota utilizando el formulario de comentarios a continuación. ¡Esperamos con interés escuchar de usted!