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

Esta es la segunda parte de una serie de 2 artículos sobre lo esencial de Mariadb / mysql comandos. Consulte nuestro artículo anterior sobre este tema antes de continuar.

1. Aprende conceptos básicos de MySQL/mariadb para principiantes - Parte 1

En esta segunda parte de la serie MySQL/Mariadb Beginner, explicaremos cómo limitar el número de filas devueltas por un SELECCIONAR consulta, y cómo ordenar el conjunto de resultados basado en una condición dada.

Además, aprenderemos cómo agrupar los registros y realizar una 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.

Requisitos previos

Para comenzar, siga estos pasos:

1. Descargar el empleados base de datos de muestra, que incluye seis tablas que consisten en 4 Millones de registros en total.

# wget https: // Launchpad.net/test-db/empleado-db-1/1.0.6/+descargar/empleados_db-full-1.0.6.alquitrán.BZ2 # TAR XJF Empleados_DB-FULL-1.0.6.alquitrán.BZ2 # CD Employes_DB 

2. Introducir el Mariada indicar y crear una base de datos nombrada empleados:

# mysql -u root -p Ingrese la contraseña: bienvenido al monitor Mariadb. Los comandos terminan con; o \ g. Su ID de conexión de Mariadb es la versión de 2 servidores: 10.1.14-mariadb mariadb servidor copyright (c) 2000, 2016, Oracle, Mariadb Corporation AB y otros. Escriba 'ayuda;' o '\ h' por ayuda. Escriba '\ c' para borrar la instrucción de entrada actual. Mariadb [(ninguno)]> Crear empleados de la base de datos; Consulta bien, 1 fila afectada (0.00 seg) 

3. Importarlo en su servidor MariadB de la siguiente manera:

Mariadb [(ninguno)]> Empleados de origen.sql 

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

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

Mariadb [empleados]> Usar empleados; Base de datos cambiada Mariadb [empleados]> Mostrar mesas; +---------------------+ | Tablas_in_employes | + ---------------------+ | departamentos | | Dept_emp | | Dept_manager | | empleados | | salarios | | títulos | + ---------------------+ 6 filas en el set (0.02 seg) 

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

Mariadb [empleados]> Crear usuario [correo electrónico protegido] identificado por 'empadminpass'; Consulta bien, 0 filas afectadas (0.03 seg) Mariadb [empleados]> Otorgar todos los privilegios a los empleados.* a [correo electrónico protegido]; Consulta bien, 0 filas afectadas (0.02 seg) Mariadb [empleados]> Privilegios de descarga; Consulta bien, 0 filas afectadas (0.00 seg) Mariadb [empleados]> salida Adiós 

Ahora inicie sesión como empadmin Usuario en el aviso 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 de Mariadb es 4 Versión del servidor: 10.1.14-mariadb mariadb servidor copyright (c) 2000, 2016, Oracle, Mariadb Corporation AB y otros. Escriba 'ayuda;' o '\ h' por ayuda. Escriba '\ c' para borrar la instrucción de entrada actual. Mariadb [(ninguno)]> Usar empleados; Lectura de la información de la tabla para completar los nombres de la tabla y la columna Puede desactivar esta función para obtener una inicio más rápida con -A -una base de datos cambiada 

Aprenda comandos básicos de MySQL para principiantes

Asegúrese de que todos los pasos descritos en la imagen de arriba se hayan completado 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 con el tiempo. Esto ayudará a responder las siguientes preguntas:

1. ¿Él / ella obtuvo algún aumento??
2. Si es así cuando?

Ejecute la siguiente consulta para averiguar:

Mariadb [empleados]> Seleccione * de los salarios donde emp_no = 10001 ordene de 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 el set ( 0.03 seg) 

Ahora, ¿qué pasa si necesitamos ver los últimos 5 aumentos?? Podemos hacer Orden de from_date Desc. El Descomunal La palabra clave indica que queremos ordenar el resultado establecido en orden descendente.

Además, Límite 5 nos permite devolver solo la parte superior 5 Filas en el conjunto de resultados:

Mariadb [empleados]> Seleccione * de los salarios donde emp_no = 10001 ordene desde from_date Desc límite 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 el set ( 0.00 seg) 

Consulta la tabla mysql por fecha de pedido

También puedes usar Ordenar con múltiples campos. Por ejemplo, la siguiente consulta ordenará el conjunto de resultados en función de la fecha de nacimiento del empleado en forma ascendente (el valor predeterminado) y luego por los apellidos en forma descendente alfabética:

Mariadb [empleados]> Seleccione Concat (Last_Name, ',', First_Name) como nombre, Género como Género, Hire_Date como "Fecha de contratación" de los empleados Orden por Birth_Date, Last_Name Desc Limit 10; +--------------------+--------+------------+ | Nombre | Género | Fecha de alquiler | +--------------------+--------+------------+| 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 el set (0.31 segundos) 

Consulta tabla mysql por fecha de nacimiento

Puede ver más información sobre LÍMITE aquí.

Grupos de registros / Max, Min, AVG y Round

Como mencionamos anteriormente, el salarios La tabla contiene los ingresos de cada empleado a lo largo del tiempo. Además LÍMITE, Podemos usar el Máximo y Mínimo Palabras clave para determinar cuándo se contrató el número máximo y mínimo de empleados:

Mariadb [empleados]> Seleccione Concat (Last_Name, ',', First_Name) como nombre, max (b.salario) como "max. salario "de los empleados A unir salarios b en un.emp_no = b.Emp_no donde un.Emp_no en (10001, 10002, 10003) grupo por un.emp_no; +-----------------+-------------+ | Nombre | Máximo. salario | +-----------------+-------------+| Facello, Georgi | 88958 | | Simmel, Bezalel | 72527 | | Bamford, Parto | 43699 | +-----------------+-------------+3 filas en el set (0.02 seg) Mariadb [empleados]> Seleccione Concat (Last_Name, ',', First_Name) como Nombre, Min (B.salario) como "min. salario "de los empleados A unir salarios b en un.emp_no = b.Emp_no donde un.Emp_no en (10001, 10002, 10003) grupo por un.emp_no; +-----------------+-------------+ | Nombre | Mínimo. salario | +-----------------+-------------+| Facello, Georgi | 60117 | | Simmel, Bezalel | 65828 | | Bamford, Parto | 40006 | +-----------------+-------------+3 filas en el set (0.00 seg) 

Agrupar los registros de MySQL usando palabras clave MAX y MIN

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

Mariadb [empleados]> Seleccione Concat (Last_Name, ',', First_Name) como Nombre, Round (AVG (B.salario), 2) como "avg. salario "de los empleados A unir salarios b en un.emp_no = b.Emp_no donde un.Emp_no en (10001, 10002, 10003) grupo por un.emp_no; +-----------------+-------------+ | Nombre | Aviso. salario | +-----------------+-------------+| Facello, Georgi | 75388.94 | | Simmel, Bezalel | 68854.50 | | Bamford, Parto | 43030.29 | +-----------------+-------------+3 filas en el set (0.01 seg) 

Si está de acuerdo en que devolverá el promedio (como se especifica por Aviso) salario con el tiempo redondeado a 2 decimales (como lo indica REDONDO), tienes razón.

Si queremos ver la suma de los salarios agrupados por el empleado y devolver la parte superior 5, Podemos usar la siguiente consulta:

Mariadb [empleados]> Seleccione Emp_NO, suma (salario) como salario del grupo de salarios por orden empr_no por salario Desc Límite 5; +--------+---------+ | Emp_no | Salario | +--------+---------+| 109334 | 2553036 | | 43624 | 2492873 | | 66793 | 2383923 | | 237542 | 2381119 | | 47978 | 2374024 | +--------+---------+5 filas en el set (2.22 segundos) 

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

Reuniendo todo

Afortunadamente, no necesitamos ejecutar una consulta después de la 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; -calculación máxima de salarios seleccione concat (last_name, ',', first_name) como nombre, max (b.salario) como "max. salario "de los empleados A unir salarios b en un.emp_no = b.Emp_no donde un.Emp_no en (10001, 10002, 10003) grupo por un.emp_no; -calculación de salarios mínimos seleccione Concat (Last_Name, ',', First_Name) como Nombre, Min (B.salario) como "min. salario "de los empleados A unir salarios b en un.emp_no = b.Emp_no donde un.Emp_no en (10001, 10002, 10003) grupo por un.emp_no; -promedios calculados, redondeados a 2 decimales, seleccione Concat (Last_Name, ',', First_Name) como Nombre, Round (AVG (B.salario), 2) como "avg. salario "de los empleados A unir salarios b en un.emp_no = b.Emp_no donde un.Emp_no en (10001, 10002, 10003) grupo por un.emp_no; 

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

# mysql -u empadmin -p < maxminavg.sql Introducir la contraseña:  Nombre Max. SALARY FACELLO, Georgi 88958 Simmel, Bezalel 72527 Bamford, Parto 43699 Nombre min. SALARY FACELLO, Georgi 60117 Simmel, Bezalel 65828 Bamford, Parto 40006 Nombre AVG. SALARY FACELLO, Georgi 75388.94 Simmel, Bezalel 68854.50 Bamford, Parto 43030.29 

o del aviso 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 de Mariadb es 4 Versión del servidor: 10.1.14-mariadb mariadb servidor copyright (c) 2000, 2016, Oracle, Mariadb Corporation AB y otros. Escriba 'ayuda;' o '\ h' por ayuda. Escriba '\ c' para borrar la instrucción de entrada actual. Mariadb [(ninguno)]> Fuente Maxminavg.sql Lectura de la información de la tabla para completar los nombres de la tabla y la columna Puede desactivar esta función para obtener una inicio más rápida con -A -una base de datos cambiada 

Script mysql para ejecutar comandos SQL

Resumen

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

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