Uzziniet, kā izmantot vairākas MySQL un MariaDB funkcijas - 2. daļa


Šī ir otrā daļa no 2 rakstu sērijas par MariaDB/MySQL komandu būtisko būtību. Pirms turpināt, lūdzu, skatiet mūsu iepriekšējo rakstu par šo tēmu.

  1. Uzziniet MySQL/MariaDB pamatus iesācējiem - 1. daļa

Šajā MySQL/MariaDB iesācēju sērijas otrajā daļā mēs paskaidrosim, kā ierobežot SELECT vaicājuma atgriezto rindu skaitu un kā pasūtīt rezultātu kopu, pamatojoties uz noteiktu nosacījumu.

Turklāt mēs iemācīsimies grupēt ierakstus un veikt pamata matemātiskas manipulācijas ar ciparu laukiem. Tas viss palīdzēs mums izveidot SQL skriptu, ko mēs varam izmantot, lai izveidotu noderīgus pārskatus.

Lai sāktu, lūdzu, rīkojieties šādi:

1. Lejupielādējiet parauga datu bāzi darbinieki , kurā ir sešas tabulas, kurās kopā ir 4 miljoni ierakstu.

# 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. Ievadiet uzvedni MariaDB un izveidojiet datu bāzi ar nosaukumiem darbinieki:

# 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. Importējiet to savā MariaDB serverī šādi:

MariaDB [(none)]> source employees.sql

Pagaidiet 1-2 minūtes, līdz tiek ielādēta datu bāzes paraugs (paturiet prātā, ka šeit mēs runājam par 4M ierakstiem!).

4. Pārbaudiet, vai datu bāze ir importēta pareizi, uzskaitot tās tabulas:

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. Izveidojiet īpašu kontu, kuru izmantot darbinieku datu bāzē (izvēlieties citu konta nosaukumu un paroli):

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

Tagad Mariadb uzvednē piesakieties kā empadmin lietotājs.

# 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

Pirms turpināt, pārliecinieties, vai visas iepriekšminētajā attēlā aprakstītās darbības ir izpildītas.

Algu tabulā ir visi katra darbinieka ienākumi ar sākuma un beigu datumiem. Mēs varētu vēlēties apskatīt emp_no = 10001 algas laika gaitā. Tas palīdzēs atbildēt uz šādiem jautājumiem:

  1. Vai viņš/viņa ieguva paaugstinājumus?
  2. Ja jā, kad?

Lai uzzinātu, izpildiet šo vaicājumu:

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)

Ko darīt, ja mums jāaplūko pēdējie 5 paaugstinājumi? Mēs varam veikt ORDER BY from_date DESC. Atslēgvārds DESC norāda, ka mēs vēlamies kārtot rezultātu kopu dilstošā secībā.

Turklāt LIMIT 5 ļauj mums atgriezt tikai 5 labākās rezultātu kopas rindas:

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)

ORDER BY var izmantot arī ar vairākiem laukiem. Piemēram, pēc šī vaicājuma rezultātu kopa tiks sakārtota, ņemot vērā darbinieka dzimšanas datumu augošā formā (noklusējums) un pēc tam pēc uzvārdiem alfabētiskā dilstošā formā:

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)

Plašāku informāciju par LIMIT varat skatīt šeit.

Kā jau minējām iepriekš, tabulā algas ir iekļauti katra darbinieka ienākumi laika gaitā. Papildus LIMIT, mēs varam izmantot atslēgvārdus MAX un MIN, lai noteiktu, kad tika pieņemts darbā maksimālais un minimālais darbinieku skaits:

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)

Pamatojoties uz iepriekš minētajām rezultātu kopām, vai varat uzminēt, ko atgriezīs zemāk esošais vaicājums?

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)

Ja piekrītat, ka tā laika gaitā atgriezīs vidējo (kā to nosaka AVG) algu, noapaļojot līdz 2 zīmēm aiz komata (kā norāda ROUND), jums ir taisnība.

Ja mēs vēlamies apskatīt algu summu, kas sagrupēta pēc darbiniekiem, un atgriezt top 5, mēs varam izmantot šādu vaicājumu:

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)

Iepriekš minētajā vaicājumā algas tiek sagrupētas pēc darbiniekiem, un pēc tam tiek veikta summa.

Par laimi, lai izveidotu pārskatu, mums nav nepieciešams izpildīt vaicājumu pēc vaicājuma. Tā vietā mēs varam izveidot skriptu ar virkni SQL komandu, lai atgrieztu visas nepieciešamās rezultātu kopas.

Tiklīdz mēs izpildīsim skriptu, tas atgriezīs nepieciešamo informāciju bez turpmākas mūsu iejaukšanās. Piemēram, pašreizējā darba direktorijā izveidosim failu maxminavg.sql ar šādu saturu:

--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;

Rindas, kas sākas ar divām domuzīmēm, tiek ignorētas, un atsevišķi vaicājumi tiek izpildīti viens pēc otra. Mēs varam izpildīt šo skriptu vai nu no Linux komandrindas:

# 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

vai no MariaDB uzvednes:

# 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

Kopsavilkums

Šajā rakstā mēs esam paskaidrojuši, kā izmantot vairākas MariaDB funkcijas, lai precizētu rezultātu kopas, kuras atdod SELECT priekšraksti. Kad tie ir definēti, skriptā var ievietot vairākus atsevišķus vaicājumus, lai to vieglāk izpildītu un samazinātu cilvēcisku kļūdu risku.

Vai jums ir kādi jautājumi vai ieteikumi par šo rakstu? Jūtieties brīvi nomest mums piezīmi, izmantojot zemāk esošo komentāru veidlapu. Mēs ceram uz jums atbildi!