Naučite se uporabljati več funkcij MySQL in MariaDB - 2. del
To je drugi del dvočlanske serije o bistvih ukazov MariaDB/MySQL. Pred nadaljevanjem si oglejte naš prejšnji članek o tej temi.
- Spoznajte osnove MySQL/MariaDB za začetnike - 1. del
V tem drugem delu začetniške serije MySQL/MariaDB bomo razložili, kako omejiti število vrstic, ki jih vrne poizvedba SELECT, in kako razvrstiti nabor rezultatov glede na dani pogoj.
Poleg tega se bomo naučili združevanja zapisov in izvajanja osnovnih matematičnih manipulacij na številskih poljih. Vse to nam bo pomagalo ustvariti skript SQL, ki ga bomo lahko uporabili za pripravo koristnih poročil.
Za začetek sledite tem korakom:
1. Prenesite vzorčno bazo podatkov zaposlenih , ki vključuje šest tabel, sestavljenih iz skupno 4 milijonov zapisov.
# 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. Vnesite poziv MariaDB in ustvarite zbirko podatkov z imenom 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. Uvozite ga v strežnik MariaDB, kot sledi:
MariaDB [(none)]> source employees.sql
Počakajte 1-2 minuti, da se vzorčna baza podatkov naloži (ne pozabite, da tukaj govorimo o 4M zapisih!).
4. Preverite, ali je bila baza podatkov pravilno uvožena, tako da navedete njene tabele:
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. Ustvarite poseben račun, ki ga želite uporabljati z bazo podatkov o zaposlenih (lahko izberete drugo ime računa in geslo):
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
Zdaj se prijavite kot uporabnik empadmina v poziv 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
Pred nadaljevanjem se prepričajte, da so bili vsi koraki, opisani na zgornji sliki, zaključeni.
Tabela plač vsebuje vse dohodke vsakega zaposlenega z začetnim in končnim datumom. Morda si bomo želeli sčasoma ogledati plače emp_no = 10001 . To vam bo pomagalo odgovoriti na naslednja vprašanja:
- Je dobil kakšno povišanje?
- Če da, kdaj?
Izvedite naslednjo poizvedbo, če želite izvedeti:
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)
Kaj pa, če si moramo ogledati zadnjih 5 povišanj? Lahko naredimo ORDER BY from_date DESC. Ključna beseda DESC označuje, da želimo razvrščati nabor rezultatov v padajočem vrstnem redu.
Poleg tega vam LIMIT 5 omogoča, da vrnemo le prvih 5 vrstic v naboru rezultatov:
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 lahko uporabite tudi z več polji. Na primer, naslednja poizvedba bo niz rezultatov razvrstila glede na rojstni datum zaposlenega v naraščajoči obliki (privzeto) in nato po priimkih v abecednem padajoči obliki:
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)
Več informacij o LIMIT si lahko ogledate tukaj.
Kot smo že omenili, tabela plače vsebuje dohodke vsakega zaposlenega skozi čas. Poleg LIMIT lahko s ključnimi besedama MAX in MIN določimo, kdaj je bilo najeto največje in najmanjše število 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)
Ali lahko na podlagi zgornjih naborov rezultatov uganete, kaj bo vrnila spodnja poizvedba?
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)
Če se strinjate, da bo sčasoma vrnila povprečno (kot določa AVG) plačo, zaokroženo na dve decimalni mesti (kot označuje ROUND), imate prav.
Če želimo videti vsoto plač, razvrščenih po zaposlenih, in vrniti top 5, lahko uporabimo naslednjo poizvedbo:
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)
V zgornjem vprašanju so plače razvrščene po zaposlenih in nato izvedena vsota.
Na srečo nam za izdelavo poročila ni treba izvajati poizvedbe za poizvedbo. Namesto tega lahko ustvarimo skript z vrsto ukazov SQL za vrnitev vseh potrebnih nizov rezultatov.
Ko izvedemo skript, bo vrnil zahtevane podatke brez nadaljnjega posredovanja z naše strani. Ustvarimo na primer datoteko z imenom maxminavg.sql v trenutnem delovnem imeniku z naslednjo vsebino:
--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;
Vrstice, ki se začnejo z dvema pomišljajema, se prezrejo, posamezne poizvedbe pa se izvajajo ena za drugo. Ta skript lahko izvedemo bodisi iz ukazne vrstice 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
ali iz poziva 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
Povzetek
V tem članku smo razložili, kako uporabiti več funkcij MariaDB za izboljšanje naborov rezultatov, ki jih vrnejo stavki SELECT. Ko so definirani, lahko v skript vstavite več posameznih poizvedb za lažje izvajanje in zmanjšanje tveganja za človeške napake.
Imate vprašanja ali predloge glede tega članka? Sprostite nam opombo s pomočjo spodnjega obrazca za komentar. Veselimo se vaših odzivov!