Õppige kasutama MySQLi ja MariaDB mitut funktsiooni - 2. osa


See on teine u200bu200bosa kahest artiklist koosnevas seerias, mis käsitleb MariaDB/MySQL-i käskude põhitõdesid. Enne jätkamist lugege meie eelmist artiklit sellel teemal.

  1. Lugege algajatele MySQL/MariaDB põhitõdesid - 1. osa

MySQL/MariaDB algajate seeria teises osas selgitame, kuidas piirata SELECT päringu tagastatavate ridade arvu ja kuidas tellida tulemuste komplekt etteantud tingimuse alusel.

Lisaks õpime kirjeid rühmitama ja põhilisi matemaatilisi manipuleerimisi numbriväljadel. Kõik see aitab meil luua SQL-skripti, mida saaksime kasutada kasulike aruannete koostamiseks.

Alustamiseks toimige järgmiselt.

1. Laadige alla töötajate näidisandmebaas, mis sisaldab kuut tabelit, mis koosnevad kokku 4 miljonist kirjest.

# 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. Sisestage viip MariaDB ja looge andmebaas nimega töötajad:

# 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. Importige see oma MariaDB serverisse järgmiselt:

MariaDB [(none)]> source employees.sql

Oodake 1-2 minutit, kuni näidisandmebaas laaditakse (pidage meeles, et siin räägime 4M-i kirjetest!).

4. Veenduge, et andmebaas imporditi õigesti, loetledes selle tabelid:

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. Looge spetsiaalne konto, mida kasutada töötajate andmebaasis (valige julgelt teine u200bu200bkonto nimi ja parool):

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

Nüüd logige empiadmini kasutajana sisse Mariadbi viipasse.

# 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

Enne jätkamist veenduge, et kõik ülaltoodud pildil kirjeldatud toimingud oleksid täidetud.

Palgatabel sisaldab iga töötaja kõiki sissetulekuid koos algus- ja lõppkuupäevadega. Võime soovida aja jooksul vaadata emp_no u003d 10001 palku. See aitab vastata järgmistele küsimustele:

  1. Kas ta sai tõsteid?
  2. Kui jah, siis millal?

Selle teadasaamiseks täitke järgmine päring:

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)

Mis siis saab, kui peame vaatama viimast 5 tõstet? Saame tellimuse TEGEMISEKS alates_kuupäev DESC. Märksõna DESC näitab, et soovime tulemuste komplekti sortida kahanevas järjekorras.

Lisaks võimaldab LIMIT 5 tagastada ainult tulemuste komplekti 5 ülemist rida:

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-d saab kasutada ka mitme väljaga. Näiteks tellib järgmine päring töötaja sünnikuupäeva põhjal kasvava (vaikesäte) ja seejärel perekonnanimede järgi tähestikulises kahanevas vormis:

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)

LIMITi kohta saate lisateavet vaadata siit.

Nagu me varem mainisime, sisaldab tabel palgad iga töötaja sissetulekut aja jooksul. Lisaks LIMIT-ile saame märksõnu MAX ja MIN kasutada selleks, et teha kindlaks, millal palgati töötajate maksimaalne ja minimaalne arv:

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)

Kas saate ülaltoodud tulemuste põhjal arvata, mida allpool olev päring tagastab?

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)

Kui nõustute, et see tagastab aja jooksul keskmise (nagu AVG täpsustas) palga, ümardatuna kahekümnendkohani (nagu tähistab ROUND), on teil õigus.

Kui soovime vaadata palgade summat töötaja järgi rühmitatuna ja tagastada 5 parimat, võime kasutada järgmist päringut:

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)

Ülaltoodud päringus rühmitatakse palgad töötaja järgi ja seejärel tehakse summa.

Õnneks pole meil vaja aruande koostamiseks päringut pärast päringut käivitada. Selle asemel saame kõigi vajalike tulemuste komplektide tagastamiseks luua skripti koos rea SQL-käskudega.

Kui skript on käivitatud, tagastab see vajaliku teabe ilma meie edasise sekkumiseta. Näiteks loome praeguses töökataloogis järgmise nimega fail nimega maxminavg.sql:

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

Kahe kriipsuga algavaid ridu eiratakse ja üksikud päringud täidetakse üksteise järel. Selle skripti saame käivitada kas Linuxi käsurealt:

# 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

või MariaDB viipast:

# 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

Kokkuvõte

Selles artiklis oleme selgitanud, kuidas kasutada mitmeid MariaDB funktsioone SELECT-lausete tagastatud tulemushulkade täpsustamiseks. Kui need on määratletud, saab skripti sisestada mitu üksikut päringut, et seda lihtsamalt täita ja vähendada inimlike eksimuste riski.

Kas teil on selle artikli kohta küsimusi või ettepanekuid? Heitke meile märkus, kasutades allolevat kommentaarivormi. Ootame teid huviga!