Mụta otu esi eji ọtụtụ ọrụ MySQL na MariaDB - Akụkụ 2
Nke a bụ akụkụ nke abụọ nke usoro isiokwu 2 gbasara ihe dị mkpa nke iwu MariaDB/MySQL. Biko rụtụ aka na edemede anyị gara aga gbasara isiokwu a tupu ịga n'ihu.
- Mụta MySQL/MariaDB Basics for Beginners – Part 1
N'akụkụ nke abụọ nke usoro mmalite MySQL/MariaDB, anyị ga-akọwa otu esi amachi ọnụ ọgụgụ ahịrị ndị ajụjụ SELECT weghachiri, yana otu esi atụpụta nsonaazụ dabere na ọnọdụ enyere.
Na mgbakwunye, anyị ga-amụta ka esi achịkọta ndekọ ahụ wee rụọ ọrụ mgbakọ na mwepụ bụ isi na mpaghara ọnụọgụ. Ihe ndị a niile ga-enyere anyị aka ịmepụta script SQL nke anyị nwere ike iji wepụta akụkọ bara uru.
Iji malite, biko soro usoro ndị a:
1. Budata ndị ọrụ nchekwa data sample, nke gụnyere tebụl isii nwere ndekọ nde anọ na mkpokọta.
# 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. Tinye ngwa ngwa MariaDB wee mepụta nchekwa data aha ndị ọrụ:
# 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. Bubata ya na sava MariaDB gị dị ka ndị a:
MariaDB [(none)]> source employees.sql
Chere 1-2 nkeji ruo mgbe a na-ebufe nchekwa data sample (buru n'uche na anyị na-ekwu maka ndekọ 4M ebe a!).
4. Nyochaa na ebubatara nchekwa data nke ọma site na ịdepụta tebụl ya:
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. Mepụta akaụntụ pụrụ iche iji na nchekwa data ndị ọrụ (enwere onwe gị ịhọrọ aha akaụntụ na paswọọdụ ọzọ):
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
Banye ugbu a dị ka onye ọrụ empadmin n'ime ngwa ngwa 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
Gbaa mbọ hụ na emechaala usoro niile akọwapụtara na onyonyo a tupu ịga n'ihu.
Tebụl ụgwọ ọnwa nwere ego niile nke onye ọrụ ọ bụla nwere ụbọchị mmalite na njedebe. Anyị nwere ike ịchọ ịhụ ụgwọ ọnwa nke emp_no=10001 ka oge na-aga. Nke a ga-enyere aka ịza ajụjụ ndị a:
- O nwekwaghị ịrị elu ọ bụla?
- Ọ bụrụ otu a, olee mgbe?
Mee ajụjụ a ka ịchọpụta:
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)
Ugbu a gịnị ma ọ bụrụ na anyị kwesịrị ilele ihe ọhụrụ 5 na-ebuli? Anyị nwere ike ime ORDER site na_ụbọchị DESC. Isi okwu DESC na-egosi na anyị chọrọ ịhazi nsonaazụ atọrọ n'usoro na-agbada.
Na mgbakwunye, LIMIT 5 na-enye anyị ohere ịlaghachi naanị ahịrị 5 kacha elu na nsonaazụ nsonaazụ:
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)
Ị nwekwara ike iji ORDER BY nwere ọtụtụ ubi. Dịka ọmụmaatụ, ajụjụ a na-esote ga-atụnye nsonaazụ atọrọ dabere na ụbọchị ọmụmụ onye ọrụ n'ụdị ịrịgo (nke ndabara) wee jiri aha ikpeazụ n'ụdị mgbada mkpụrụedemede:
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)
Ị nwere ike ịlele ozi ndị ọzọ gbasara LIMIT ebe a.
Dịka anyị kwuru na mbụ, tebụl ụgwọ ọnwa nwere ego onye ọrụ ọ bụla na-enweta n'ime oge. E wezụga LIMIT, anyị nwere ike iji mkpụrụokwu MAX na MIN chọpụta mgbe goro ndị ọrụ kacha na kacha nta:
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)
Dabere na usoro rịzọlt dị n'elu, ị nwere ike ịma ihe ajụjụ a ga-alaghachi?
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)
Ọ bụrụ n'ikwenye na ọ ga-eweghachite ụgwọ ọnwa nkezi (dị ka AVG siri kọwaa) n'ime oge agbagoro na decimals 2 (dị ka ROUND gosipụtara), ị ziri ezi.
Ọ bụrụ na anyị chọrọ ịlele nchikota nke ụgwọ ọnwa nke onye ọrụ chịkọtara wee weghachi 5 kacha elu, anyị nwere ike iji ajụjụ a:
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)
N'ajụjụ a dị n'elu, a na-achịkọta ụgwọ ọnwa site n'aka onye ọrụ wee mechaa nchikota.
Ọ dabara nke ọma, anyị adịghị mkpa ka anyị gbaa ajụjụ mgbe a jụrụ ajụjụ iji wepụta mkpesa. Kama, anyị nwere ike ịmepụta edemede nwere usoro iwu SQL iji weghachi ihe niile dị mkpa.
Ozugbo anyị mezuru edemede ahụ, ọ ga-eweghachi ozi achọrọ n'etinyeghị aka ọzọ n'akụkụ anyị. Dịka ọmụmaatụ, ka anyị mepụta faịlụ aha ya bụ maxminavg.sql na ndekọ ọrụ ugbu a yana ọdịnaya ndị a:
--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;
A na-eleghara ahịrị ndị na-amalite na dashes abụọ anya, a na-emezukwa ajụjụ ndị ahụ n'otu n'otu. Anyị nwere ike mebe edemede a site na ahịrị iwu 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
ma ọ bụ site na ngwa ngwa 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
Nchịkọta
N'ime edemede a, anyị akọwarala otu esi eji ọtụtụ ọrụ MariaDB iji mezie nsonaazụ nsonaazụ nke nkwupụta SELECT weghachiri. Ozugbo ha kọwapụtara, enwere ike itinye ọtụtụ ajụjụ n'otu n'otu n'ime edemede iji mee ya ngwa ngwa yana belata ohere nke njehie mmadụ.
Ị nwere ajụjụ ma ọ bụ aro ọ bụla gbasara akụkọ a? Enwere onwe gị ịhapụ ndetu anyị site na iji ụdị nkọwa n'okpuru. Anyị na-atụ anya ịnụ gị!