Jifunze Jinsi ya Kutumia Kazi Kadhaa za MySQL na MariaDB - Sehemu ya 2
Hii ni sehemu ya pili ya mfululizo wa makala 2 kuhusu mambo muhimu ya maagizo ya MariaDB/MySQL. Tafadhali rejelea nakala yetu iliyotangulia juu ya mada hii kabla ya kuendelea.
- Jifunze Misingi ya MySQL/MariaDB kwa Wanaoanza - Sehemu ya 1
Katika sehemu hii ya pili ya mfululizo wa wanaoanza wa MySQL/MariaDB, tutaeleza jinsi ya kuweka kikomo idadi ya safu mlalo zilizorejeshwa na swali CHAGUA, na jinsi ya kuagiza seti ya matokeo kulingana na hali fulani.
Zaidi ya hayo, tutajifunza jinsi ya kuweka rekodi katika vikundi na kufanya upotoshaji wa msingi wa hisabati kwenye sehemu za nambari. Yote haya yatatusaidia kuunda hati ya SQL ambayo tunaweza kutumia kutoa ripoti muhimu.
Ili kuanza, tafadhali fuata hatua hizi:
1. Pakua sampuli ya hifadhidata ya wafanyakazi, inayojumuisha majedwali sita yenye rekodi milioni 4 kwa jumla.
# 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. Weka kidokezo cha MariaDB na uunde hifadhidata iliyoitwa wafanyikazi:
# 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. Iingize kwenye seva yako ya MariaDB kama ifuatavyo:
MariaDB [(none)]> source employees.sql
Subiri dakika 1-2 hadi hifadhidata ya sampuli ipakie (kumbuka tunazungumza juu ya rekodi za 4M hapa!).
4. Thibitisha kuwa hifadhidata ililetwa kwa usahihi kwa kuorodhesha majedwali yake:
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. Fungua akaunti maalum ya kutumia na hifadhidata ya wafanyikazi (jisikie huru kuchagua jina la akaunti na nenosiri lingine):
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
Sasa ingia kama mtumiaji wa empadmin kwenye haraka ya 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
Hakikisha hatua zote zilizoainishwa kwenye picha hapo juu zimekamilika kabla ya kuendelea.
Jedwali la mishahara lina mapato yote ya kila mfanyakazi aliye na tarehe za kuanza na mwisho. Tunaweza kutaka kuona mishahara ya emp_no=10001 baada ya muda. Hii itasaidia kujibu maswali yafuatayo:
- Je, alipata nyongeza yoyote?
- Kama ndivyo, lini?
Tekeleza swali lifuatalo ili kujua:
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)
Sasa vipi ikiwa tunahitaji kutazama matoleo 5 ya hivi punde? Tunaweza kufanya ORDER BY kuanzia_date DESC. Neno kuu la DESC linaonyesha kuwa tunataka kupanga matokeo yaliyowekwa kwa mpangilio wa kushuka.
Zaidi ya hayo, LIMIT 5 huturuhusu kurudisha safu 5 za juu pekee katika seti ya matokeo:
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)
Unaweza pia kutumia ORDER BY na sehemu nyingi. Kwa mfano, swali lifuatalo litaagiza matokeo yaliyowekwa kulingana na tarehe ya kuzaliwa ya mfanyakazi katika fomu ya kupanda (chaguo-msingi) na kisha kwa majina ya mwisho katika fomu ya kushuka kialfabeti:
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)
Unaweza kutazama habari zaidi kuhusu LIMIT hapa.
Kama tulivyotaja awali, jedwali la mishahara lina mapato ya kila mfanyakazi kwa muda. Kando na LIMIT, tunaweza kutumia manenomsingi MAX na MIN kubainisha wakati idadi ya juu na ya chini kabisa ya wafanyakazi iliajiriwa:
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)
Kulingana na seti za matokeo hapo juu, unaweza kukisia swali lililo hapa chini litarejesha nini?
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)
Ikiwa unakubali kwamba itarejesha wastani (kama ilivyobainishwa na AVG) baada ya muda kuongezwa hadi desimali 2 (kama ilivyoonyeshwa na ROUND), uko sahihi.
Ikiwa tunataka kuangalia jumla ya mishahara iliyopangwa na mfanyakazi na kurudisha 5 bora, tunaweza kutumia hoja ifuatayo:
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)
Katika swali lililo hapo juu, mishahara imepangwa na mfanyakazi na kisha jumla inafanywa.
Kwa bahati nzuri, hatuhitaji kutekeleza hoja baada ya swali ili kutoa ripoti. Badala yake, tunaweza kuunda hati yenye mfululizo wa amri za SQL ili kurudisha seti zote za matokeo muhimu.
Mara tu tunapofanya hati, itarudisha habari inayohitajika bila kuingilia kati zaidi kwa upande wetu. Kwa mfano, hebu tuunde faili inayoitwa maxminavg.sql katika saraka ya sasa ya kufanya kazi na yaliyomo yafuatayo:
--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;
Mistari inayoanza na deshi mbili hupuuzwa, na hoja za kibinafsi hutekelezwa moja baada ya nyingine. Tunaweza kutekeleza hati hii kutoka kwa safu ya amri ya 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
au kutoka kwa haraka ya 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
Muhtasari
Katika nakala hii tumeelezea jinsi ya kutumia vitendaji kadhaa vya MariaDB ili kuboresha seti za matokeo zilizorejeshwa na taarifa SELECT. Baada ya kufafanua, hoja nyingi za kibinafsi zinaweza kuingizwa kwenye hati ili kuitekeleza kwa urahisi zaidi na kupunguza hatari ya makosa ya kibinadamu.
Je, una maswali au mapendekezo kuhusu makala hii? Jisikie huru kutuandikia barua kwa kutumia fomu ya maoni hapa chini. Tunatarajia kusikia kutoka kwako!