Tgħallem Kif tuża Diversi Funzjonijiet ta 'MySQL u MariaDB - Parti 2
Din hija t-tieni parti ta 'serje ta' 2 artikoli dwar l-essenzjali tal-kmandi MariaDB/MySQL. Jekk jogħġbok irreferi għall-artiklu preċedenti tagħna dwar dan is-suġġett qabel ma tipproċedi.
- Tgħallem MySQL/MariaDB Basics għal Jibdew – Parti 1
F'din it-tieni parti tas-serje tal-bidu MySQL/MariaDB, se nispjegaw kif nillimitaw in-numru ta 'ringieli rritornati minn mistoqsija SELECT, u kif tordna s-sett tar-riżultat ibbażat fuq kundizzjoni partikolari.
Barra minn hekk, se nitgħallmu kif niġbru r-rekords u nwettqu manipulazzjoni matematika bażika fuq oqsma numeriċi. Dan kollu se jgħinna noħolqu script SQL li nistgħu nużaw biex nipproduċu rapporti utli.
Biex tibda, jekk jogħġbok segwi dawn il-passi:
1. Niżżel il-kampjun tad-database ta' impjegati, li tinkludi sitt tabelli li jikkonsistu f'4 miljun rekord b'kollox.
# 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. Daħħal il-pront MariaDB u oħloq database bl-isem impjegati:
# 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. Importaha fis-server MariaDB tiegħek kif ġej:
MariaDB [(none)]> source employees.sql
Stenna 1-2 minuti sakemm titgħabba d-database tal-kampjun (żomm f'moħħok li qed nitkellmu dwar rekords 4M hawn!).
4. Ivverifika li d-database ġiet importata b'mod korrett billi telenka t-tabelli tagħha:
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. Oħloq kont speċjali biex tuża mad-database tal-impjegati (ħossok liberu li tagħżel isem u password ieħor tal-kont):
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
Issa idħol bħala utent empadmin f'Mariadb fil-pront.
# 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
Kun żgur li l-passi kollha deskritti fl-immaġni ta 'hawn fuq ikunu tlestew qabel ma tipproċedi.
It-tabella tas-salarji fiha d-dħul kollu ta’ kull impjegat bid-dati tal-bidu u tat-tmiem. Nixtiequ naraw is-salarji ta' emp_no=10001 maż-żmien. Dan jgħin biex iwieġeb il-mistoqsijiet li ġejjin:
- Ġib xi żidiet?
- Jekk iva, meta?
Esegwi l-mistoqsija li ġejja biex issir taf:
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)
Issa x'jiġri jekk irridu naraw l-aħħar 5 żidiet? Nistgħu nagħmlu ORDER BY from_date DESC. Il-kelma prinċipali DESC tindika li rridu nissortjaw is-sett tar-riżultat f'ordni dixxendenti.
Barra minn hekk, LIMIT 5 jippermettilna li nirritornaw biss l-aqwa 5 ringieli fis-sett tar-riżultati:
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)
Tista' wkoll tuża ORDER BY b'diversi oqsma. Pereżempju, il-mistoqsija li ġejja tordna s-sett tar-riżultat ibbażat fuq id-data tat-twelid tal-impjegat f'forma axxendenti (l-default) u mbagħad mill-kunjomijiet f'forma dixxendenti alfabetika:
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)
Tista' tara aktar informazzjoni dwar LIMIT hawn.
Kif semmejna qabel, it-tabella salarji fiha d-dħul ta’ kull impjegat matul iż-żmien. Minbarra LIMIT, nistgħu nużaw il-kliem kjavi MAX u MIN biex niddeterminaw meta n-numru massimu u minimu ta' impjegati ġew mikrija:
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)
Ibbażat fuq is-settijiet ta' riżultati ta' hawn fuq, tista' taqta' x'se terġa' lura l-mistoqsija ta' hawn taħt?
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)
Jekk taqbel li se tirritorna s-salarju medju (kif speċifikat minn AVG) matul iż-żmien imqarreb għal 2 deċimali (kif indikat minn ROUND), għandek raġun.
Jekk irridu naraw is-somma tas-salarji raggruppati skont l-impjegat u nirritornaw l-aqwa 5, nistgħu nużaw il-mistoqsija li ġejja:
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)
Fil-mistoqsija ta 'hawn fuq, is-salarji huma raggruppati skont l-impjegat u mbagħad titwettaq is-somma.
Fortunatament, m'għandniex bżonn li nħaddmu mistoqsija wara mistoqsija biex nipproduċu rapport. Minflok, nistgħu noħolqu skript b'serje ta 'kmandi SQL biex nirritornaw is-settijiet kollha ta' riżultati meħtieġa.
Ladarba nwettqu l-iskrittura, se terġa 'lura l-informazzjoni meħtieġa mingħajr aktar intervent min-naħa tagħna. Pereżempju, ejja noħolqu fajl bl-isem maxminavg.sql fid-direttorju tax-xogħol kurrenti bil-kontenut li ġej:
--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;
Linji li jibdew b'żewġ sing huma injorati, u l-mistoqsijiet individwali huma esegwiti wieħed wara l-ieħor. Nistgħu nwettqu dan l-iskritt jew mil-linja tal-kmand tal-Linux:
# 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
jew mill-pront ta' 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
Sommarju
F'dan l-artikolu spjegajna kif tuża diversi funzjonijiet MariaDB sabiex tirfina s-settijiet ta 'riżultati rritornati minn dikjarazzjonijiet SELECT. Ladarba jkunu ddefiniti, mistoqsijiet individwali multipli jistgħu jiddaħħlu fi skript biex iwettquha aktar faċilment u biex jitnaqqas ir-riskju ta 'żball uman.
Għandek xi mistoqsijiet jew suġġerimenti dwar dan l-artikolu? Ħossok liberu li tibgħatilna nota billi tuża l-formola tal-kummenti hawn taħt. Aħna ħerqana li nisimgħu mingħandek!