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 la serie para principiantes de MySQL/MariaDB, explicaremos cómo limitar el número de filas devueltas por una consulta SELECT y cómo ordenar el conjunto de resultados según una condición determinada.

Además, aprenderemos cómo agrupar los registros y realizar manipulación matemática básica en campos numéricos. Todo esto nos ayudará a crear un script SQL que podamos usar para producir informes útiles.

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 employees_db-full-1.0.6.tar.bz2
# cd employees_db

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

# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE employees;
Query OK, 1 row affected (0.00 sec)

3. Importarlo a su servidor MariaDB de la siguiente manera:

MariaDB [(none)]> source employees.sql

Espere entre 1 y 2 minutos hasta que se cargue la base de datos de muestra (tenga en cuenta que estamos hablando de 4 millones de registros).

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

MariaDB [employees]> USE employees;
Database changed
MariaDB [employees]> SHOW TABLES;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.02 sec)

5. Cree una cuenta especial para usar con la base de datos de empleados (no dude en elegir otro nombre de cuenta y contraseña):

MariaDB [employees]> CREATE USER [email protected] IDENTIFIED BY 'empadminpass';
Query OK, 0 rows affected (0.03 sec)

MariaDB [employees]> GRANT ALL PRIVILEGES ON  employees.* to [email protected];
Query OK, 0 rows affected (0.02 sec)

MariaDB [employees]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> exit
Bye

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

# mysql -u empadmin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

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

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 u003d 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 [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date;
+--------+--------+------------+------------+
| emp_no | salary | 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 rows in set (0.03 sec)

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 las 5 filas superiores en el conjunto de resultados:

MariaDB [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date DESC LIMIT 5;
+--------+--------+------------+------------+
| emp_no | salary | 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 rows in set (0.00 sec)

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 [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, gender AS Gender,  hire_date AS "Hire date" FROM employees ORDER BY birth_date, last_name DESC LIMIT 10;
+--------------------+--------+------------+
| Name               | Gender | Hire date  |
+--------------------+--------+------------+
| 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 rows in set (0.31 sec)

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

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

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS "Max. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Max. salary |
+-----------------+-------------+
| Facello, Georgi |       88958 |
| Simmel, Bezalel |       72527 |
| Bamford, Parto  |       43699 |
+-----------------+-------------+
3 rows in set (0.02 sec)

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS "Min. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Min. salary |
+-----------------+-------------+
| Facello, Georgi |       60117 |
| Simmel, Bezalel |       65828 |
| Bamford, Parto  |       40006 |
+-----------------+-------------+
3 rows in set (0.00 sec)

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

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS "Avg. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Avg. salary |
+-----------------+-------------+
| Facello, Georgi |    75388.94 |
| Simmel, Bezalel |    68854.50 |
| Bamford, Parto  |    43030.29 |
+-----------------+-------------+
3 rows in set (0.01 sec)

Si acepta que devolverá 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 5 primeros, podemos utilizar la siguiente consulta:

MariaDB [employees]> SELECT emp_no, SUM(salary) AS Salary FROM salaries GROUP BY emp_no ORDER BY Salary DESC LIMIT 5;
+--------+---------+
| emp_no | Salary  |
+--------+---------+
| 109334 | 2553036 |
|  43624 | 2492873 |
|  66793 | 2383923 |
| 237542 | 2381119 |
|  47978 | 2374024 |
+--------+---------+
5 rows in set (2.22 sec)

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

Afortunadamente, no necesitamos ejecutar una consulta tras otra para generar 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:

--Select database
USE employees;
--Calculate maximum salaries
SELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS "Max. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
--Calculate minimum salaries
SELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS "Min. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
--Calculate averages, round to 2 decimal places
SELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS "Avg. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY 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 < maxminavg.sql
Enter password: 
Name	Max. salary
Facello, Georgi	88958
Simmel, Bezalel	72527
Bamford, Parto	43699
Name	Min. salary
Facello, Georgi	60117
Simmel, Bezalel	65828
Bamford, Parto	40006
Name	Avg. salary
Facello, Georgi	75388.94
Simmel, Bezalel	68854.50
Bamford, Parto	43030.29

o desde el indicador MariaDB:

# mysql -u empadmin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> source maxminavg.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Resumen

En este artículo, hemos explicado cómo usar varias funciones MariaDB para refinar los conjuntos de resultados devueltos por las declaraciones 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!