Naučite kako koristiti nekoliko funkcija MySQL-a i MariaDB-a - 2. dio
Ovo je drugi dio serije od 2 članka o osnovama MariaDB/MySQL komandi. Molimo pogledajte naš prethodni članak o ovoj temi prije nego što nastavite.
- Naučite osnove MySQL/MariaDB za početnike – 1. dio
U ovom drugom dijelu MySQL/MariaDB serije za početnike, objasnit ćemo kako ograničiti broj redova koje vraća upit SELECT i kako naručiti skup rezultata na osnovu datog uslova.
Osim toga, naučit ćemo kako grupirati zapise i izvoditi osnovnu matematičku manipulaciju na numeričkim poljima. Sve ovo će nam pomoći da kreiramo SQL skriptu koju možemo koristiti za izradu korisnih izvještaja.
Preduvjeti
Za početak, slijedite ove korake:
1. Preuzmite uzorak baze podataka zaposlenih, koja uključuje šest tabela koje se sastoje od 4 miliona zapisa ukupno.
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. Unesite prompt MariaDB i kreirajte bazu podataka pod nazivom zaposleni:
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. Uvezite ga na svoj MariaDB server na sljedeći način:
MariaDB [(none)]> source employees.sql
Pričekajte 1-2 minute dok se baza podataka uzoraka ne učita (imajte na umu da ovdje govorimo o 4M zapisa!).
4. Provjerite da li je baza podataka ispravno uvezena navođenjem njenih tabela:
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. Kreirajte poseban račun za korištenje sa bazom podataka zaposlenih (slobodno odaberite drugo ime računa i lozinku):
MariaDB [employees]> CREATE USER empadmin@localhost IDENTIFIED BY 'empadminpass';
Query OK, 0 rows affected (0.03 sec)
MariaDB [employees]> GRANT ALL PRIVILEGES ON employees.* to empadmin@localhost;
Query OK, 0 rows affected (0.02 sec)
MariaDB [employees]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [employees]> exit
Bye
Sada se prijavite kao empadmin korisnik u Mariadb prompt.
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
Uvjerite se da su svi koraci navedeni na gornjoj slici završeni prije nego što nastavite.
Redosled i ograničavanje broja redova u skupu rezultata
Tabela plata sadrži sva primanja svakog zaposlenog sa datumom početka i završetka. Možda bismo željeli vidjeti plate emp_no=10001 tokom vremena. Ovo će vam pomoći da odgovorite na sljedeća pitanja:
- Da li je on/ona dobila povišicu?
- Ako da, kada?
Izvršite sljedeći upit da saznate:
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)
Šta ako trebamo vidjeti posljednjih 5 povišica? Možemo izvršiti ORDER BY from_date DESC. Ključna riječ DESC označava da želimo sortirati skup rezultata u opadajućem redoslijedu.
Dodatno, LIMIT 5 nam omogućava da vratimo samo gornjih 5 redova u skupu rezultata:
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)
Također možete koristiti ORDER BY sa više polja. Na primjer, sljedeći upit će poredati skup rezultata na osnovu datuma rođenja zaposlenika u rastućem obliku (zadano), a zatim prema prezimenima u abecednom opadajućem obliku:
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)
Više informacija o LIMITu možete vidjeti ovdje.
Grupisanje zapisa/MAX, MIN, AVG i ROUND
Kao što smo ranije spomenuli, tabela plate sadrži prihode svakog zaposlenog tokom vremena. Osim LIMIT, možemo koristiti MAX i MIN ključne riječi da odredimo kada je angažovan maksimalni i minimalni broj zaposlenih:
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)
Na osnovu gornjih skupova rezultata, možete li pogoditi šta će upit u nastavku vratiti?
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)
Ako se slažete da će vratiti prosječnu (kako je navedeno AVG) platu tokom vremena zaokruženu na 2 decimale (kao što je naznačeno ROUND), u pravu ste.
Ako želimo vidjeti zbir plata grupisanih po zaposlenima i vratiti prvih 5, možemo koristiti sljedeći upit:
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)
U gornjem upitu plate se grupišu po zaposlenima i zatim se vrši zbir.
Donosimo sve zajedno
Srećom, ne moramo pokretati upit za upitom da bismo napravili izvještaj. Umjesto toga, možemo kreirati skriptu sa nizom SQL naredbi za vraćanje svih potrebnih skupova rezultata.
Nakon što izvršimo skriptu, ona će vratiti tražene informacije bez naše daljnje intervencije. Na primjer, napravimo datoteku pod nazivom maxminavg.sql u trenutnom radnom direktoriju sa sljedećim sadržajem:
--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;
Redovi koji počinju s dvije crtice se zanemaruju, a pojedinačni upiti se izvršavaju jedan za drugim. Ovu skriptu možemo izvršiti bilo iz Linux komandne linije:
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
ili iz MariaDB prompta:
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
Sažetak
U ovom članku smo objasnili kako koristiti nekoliko MariaDB funkcija kako bismo precizirali skupove rezultata koje vraćaju SELECT izjave. Kada se definišu, više pojedinačnih upita može se umetnuti u skriptu kako bi se lakše izvršilo i smanjio rizik od ljudske greške.
Imate li pitanja ili prijedloga o ovom članku? Slobodno nam pošaljite poruku koristeći formu za komentare ispod. Radujemo se Vašem odgovoru!