Apreneu a utilitzar diverses funcions de MySQL i MariaDB - Part 2
Aquesta és la segona part d'una sèrie de 2 articles sobre l'essencial de les ordres MariaDB/MySQL. Consulteu el nostre article anterior sobre aquest tema abans de continuar.
- Aprèn els conceptes bàsics de MySQL/MariaDB per a principiants - Part 1
En aquesta segona part de la sèrie per a principiants de MySQL/MariaDB, explicarem com limitar el nombre de files retornades per una consulta SELECT i com ordenar el conjunt de resultats en funció d'una condició determinada.
A més, aprendrem a agrupar els registres i a realitzar manipulacions matemàtiques bàsiques en camps numèrics. Tot això ens ajudarà a crear un script SQL que podem utilitzar per produir informes útils.
Per començar, seguiu aquests passos:
1. Baixeu la base de dades d'exemple empleats, que inclou sis taules que consten de 4 milions de registres 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. Introduïu la sol·licitud de MariaDB i creeu una base de dades anomenada empleats:
# 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. Importeu-lo al vostre servidor MariaDB de la següent manera:
MariaDB [(none)]> source employees.sql
Espereu 1-2 minuts fins que es carregui la base de dades de mostra (tingueu en compte que aquí parlem de registres de 4M!).
4. Verifiqueu que la base de dades s'ha importat correctament enumerant les seves taules:
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. Creeu un compte especial per utilitzar-lo amb la base de dades dels empleats (no dubteu a triar un altre nom de compte i contrasenya):
MariaDB [employees]> CREATE USER [email IDENTIFIED BY 'empadminpass'; Query OK, 0 rows affected (0.03 sec) MariaDB [employees]> GRANT ALL PRIVILEGES ON employees.* to [email ; Query OK, 0 rows affected (0.02 sec) MariaDB [employees]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) MariaDB [employees]> exit Bye
Ara inicieu la sessió com a usuari empadmin al 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
Assegureu-vos que tots els passos descrits a la imatge anterior s'han completat abans de continuar.
La taula salarial conté tots els ingressos de cada empleat amb dates d'inici i finalització. És possible que vulguem veure els sous de emp_no=10001 al llarg del temps. Això ajudarà a respondre les preguntes següents:
- Va rebre augments?
- Si és així, quan?
Executeu la consulta següent per esbrinar:
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)
I ara què passa si hem de veure els últims 5 augments? Podem fer ORDER BY from_date DESC. La paraula clau DESC indica que volem ordenar el conjunt de resultats en ordre descendent.
A més, LIMIT 5 ens permet tornar només les 5 primeres files del conjunt de resultats:
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)
També podeu utilitzar ORDER BY amb diversos camps. Per exemple, la consulta següent ordenarà el conjunt de resultats en funció de la data de naixement de l'empleat en forma ascendent (per defecte) i després pels cognoms en forma alfabètica descendent:
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)
Podeu veure més informació sobre LIMIT aquí.
Com hem esmentat anteriorment, la taula salaris conté els ingressos de cada empleat al llarg del temps. A més de LIMIT, podem utilitzar les paraules clau MAX i MIN per determinar quan es va contractar el nombre màxim i mínim d'empleats:
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)
A partir dels conjunts de resultats anteriors, podeu endevinar què tornarà la consulta següent?
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 accepteu que retornarà el salari mitjà (tal com especifica AVG) al llarg del temps arrodonit a 2 decimals (tal com indica ROUND), teniu raó.
Si volem veure la suma dels salaris agrupats per empleat i retornar els 5 primers, podem utilitzar la consulta següent:
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, els sous s'agrupen per empleat i després es realitza la suma.
Afortunadament, no necessitem executar consulta rere consulta per produir un informe. En canvi, podem crear un script amb una sèrie d'ordres SQL per retornar tots els conjunts de resultats necessaris.
Un cop executem l'script, retornarà la informació requerida sense més intervenció per part nostra. Per exemple, creem un fitxer anomenat maxminavg.sql al directori de treball actual amb el contingut següent:
--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;
Les línies que comencen amb dos guions s'ignoren i les consultes individuals s'executen una darrere l'altra. Podem executar aquest script des de la línia d'ordres 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 des del missatge 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)]> 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
Resum
En aquest article hem explicat com utilitzar diverses funcions MariaDB per refinar els conjunts de resultats retornats per les sentències SELECT. Un cop s'han definit, es poden inserir múltiples consultes individuals en un script per executar-lo amb més facilitat i reduir el risc d'error humà.
Tens alguna pregunta o suggeriment sobre aquest article? No dubteu a enviar-nos una nota mitjançant el formulari de comentaris a continuació. Esperem amb interès tenir notícies teves!