Naučite kako koristiti nekoliko funkcija MySQL-a i MariaDB-a - 2. dio
Ovo je drugi dio serije od dva članka o osnovama naredbi MariaDB/MySQL. Prije nastavka pogledajte naš prethodni članak na ovu temu.
- Naučite osnove MySQL/MariaDB za početnike - 1. dio
U ovom drugom dijelu MySQL/MariaDB početničkih serija objasnit ćemo kako ograničiti broj redaka koji se vraćaju upitom SELECT i kako poredati skup rezultata na temelju zadanog uvjeta.
Uz to ćemo naučiti grupirati zapise i izvoditi osnovne matematičke manipulacije na numeričkim poljima. Sve će nam to pomoći u stvaranju SQL skripte koju možemo koristiti za stvaranje korisnih izvješća.
Za početak slijedite ove korake:
1. Preuzmite uzorak baze podataka zaposlenih , koja uključuje šest tablica koje se sastoje od ukupno 4 milijuna zapisa.
# 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 upit MariaDB i stvorite bazu podataka s imenom zaposlenici:
# 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 poslužitelj na sljedeći način:
MariaDB [(none)]> source employees.sql
Pričekajte 1-2 minute dok se uzorak baze podataka ne učita (imajte na umu da ovdje govorimo o 4M zapisima!).
4. Provjerite je li baza podataka ispravno uvezena popisom njezinih tablica:
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. Izradite poseban račun koji ćete koristiti s bazom podataka o zaposlenicima (slobodno odaberite drugo ime računa i lozinku):
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
Sada se prijavite kao korisnik empadmina 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
Prije nastavka provjerite jesu li svi koraci navedeni na gornjoj slici dovršeni.
Tablica plaća sadrži sve prihode svakog zaposlenika s datumima početka i završetka. Možda bismo željeli vremenom vidjeti plaće od emp_no = 10001 . To će vam pomoći odgovoriti na sljedeća pitanja:
- Je li dobio povišicu?
- Ako da, kada?
Izvršite sljedeći upit da biste saznali:
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)
A što ako trebamo pogledati najnovijih 5 povišenja? MOŽEMO NARUČITI od_date DESC. Ključna riječ DESC označava da želimo sortirati skup rezultata u padajućem redoslijedu.
Uz to, LIMIT 5 omogućuje nam vraćanje samo prvih 5 redaka 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 s više polja. Na primjer, sljedeći upit poredat će skup rezultata na temelju datuma rođenja zaposlenika u uzlaznom obliku (zadani), a zatim prema prezimenima u abecednom silaznom 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 pogledati ovdje.
Kao što smo ranije spomenuli, tablica plaće sadrži prihode svakog zaposlenika tijekom vremena. Osim LIMIT-a, pomoću MAX i MIN možemo odrediti kada je angažiran maksimalan i minimalan 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 temelju gornjih skupova rezultata, možete li pogoditi što će vratiti upit u nastavku?
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 navodi AVG) plaću s vremenom zaokruženu na 2 decimale (kako je naznačeno u OKRUGLI), u pravu ste.
Ako želimo pregledati zbroj plaća grupiranih po zaposlenicima i vratiti top 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 plaće se grupiraju po zaposlenicima, a zatim se izvodi zbroj.
Srećom, za izradu izvješća ne trebamo pokretati upit za upitom. Umjesto toga, možemo stvoriti skriptu s nizom SQL naredbi za vraćanje svih potrebnih skupova rezultata.
Jednom kada izvršimo skriptu, vratit će potrebne podatke bez daljnjeg posredovanja s naše strane. Na primjer, stvorimo datoteku nazvanu 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;
Redci koji počinju s dvije crtice se zanemaruju, a pojedinačni upiti izvršavaju se jedan za drugim. Ovu skriptu možemo izvršiti bilo iz naredbenog retka Linuxa:
# 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 upita 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
Sažetak
U ovom smo članku objasnili kako koristiti nekoliko MariaDB funkcija za pročišćavanje skupova rezultata koje su vratili SELECT izrazi. Nakon što definiraju, u skriptu se može umetnuti više pojedinačnih upita radi lakšeg izvršavanja i smanjenja rizika od ljudskih pogrešaka.
Imate li pitanja ili prijedloga u vezi s ovim člankom? Slobodno nam dodajte bilješku pomoću obrasca za komentare u nastavku. Radujemo se vašem javljanju!