Koyi Yadda ake Amfani da Ayyuka da yawa na MySQL da MariaDB - Kashi na 2
Wannan shine kashi na biyu na jerin jigo na 2 game da mahimman abubuwan umarnin MariaDB/MySQL. Da fatan za a duba labarinmu na baya kan wannan batu kafin ci gaba.
- Koyi tushen MySQL/MariaDB don Masu farawa - Kashi na 1
A cikin wannan kashi na biyu na jerin farawa na MySQL/MariaDB, za mu yi bayanin yadda ake iyakance adadin layuka da aka mayar da tambayar SELECT, da kuma yadda ake odar sakamakon da aka saita bisa ga yanayin da aka bayar.
Bugu da ƙari, za mu koyi yadda ake haɗa bayanan da yin amfani da ilimin lissafi na asali akan filayen lambobi. Duk wannan zai taimaka mana wajen ƙirƙirar rubutun SQL wanda za mu iya amfani da shi don samar da rahotanni masu amfani.
Don farawa, da fatan za a bi waɗannan matakan:
1. Zazzage ma'auni na ma'aikata, wanda ya ƙunshi tebur shida da ke ɗauke da bayanan miliyan 4 gabaɗaya.
# 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. Shigar da gaggawar MariaDB kuma ƙirƙirar bayanan ma'aikata mai suna:
# 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. Shigo da shi cikin uwar garken MariaDB kamar haka:
MariaDB [(none)]> source employees.sql
Jira 1-2 mintuna har sai an ɗora bayanan samfurin (ku tuna muna magana game da rikodin 4M a nan!).
4. Tabbatar cewa an shigo da ma'ajin bayanai daidai ta hanyar jera teburan sa:
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. Ƙirƙiri asusu na musamman don amfani da bayanan ma'aikata (jin daɗin zaɓar wani suna da kalmar sirri):
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
Yanzu shiga azaman mai amfani da empadmin cikin sauri 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
Tabbatar cewa an kammala duk matakan da aka zayyana a hoton da ke sama kafin a ci gaba.
Tebur na albashi ya ƙunshi duk kuɗin shiga na kowane ma'aikaci tare da kwanakin farawa da ƙarewa. Muna iya son duba albashin emp_no=10001 na tsawon lokaci. Wannan zai taimaka amsa tambayoyi masu zuwa:
- Shin ya/ta sami ƙarin girma?
- Idan haka ne, yaushe?
Yi wannan tambaya don ganowa:
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)
Yanzu menene idan muna buƙatar duba sabbin tashe 5? Zamu iya yin ORDER BY daga_date DESC. Mabuɗin DESC yana nuna cewa muna son tsara sakamakon da aka saita a cikin tsari mai saukowa.
Bugu da ƙari, LIMIT 5 yana ba mu damar dawo da manyan layuka 5 kawai a cikin saitin sakamako:
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)
Hakanan zaka iya amfani da ORDER BY tare da filaye da yawa. Misali, tambaya mai zuwa za ta ba da odar sakamakon da aka saita dangane da ranar haifuwar ma'aikaci a sigar hawan hawan (tsoho) sannan ta sunayen karshe a sigar saukowar haruffa:
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)
Kuna iya duba ƙarin bayani game da LIMIT anan.
Kamar yadda muka ambata a baya, tebur albashi ya ƙunshi kuɗin shiga na kowane ma'aikaci a kan lokaci. Bayan LIMIT, za mu iya amfani da kalmomin MAX da MIN don tantance lokacin da aka ɗauki mafi girma da mafi ƙarancin adadin ma'aikata:
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)
Dangane da saitin sakamako na sama, za ku iya tunanin menene tambayar da ke ƙasa za ta dawo?
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)
Idan kun yarda cewa zai dawo da matsakaicin matsakaicin (kamar yadda AVG ya kayyade) akan lokaci wanda aka zagaya zuwa adadi 2 (kamar yadda aka nuna ta ROUND), kuna da gaskiya.
Idan muna son duba jimlar albashin da ma'aikaci ya tara kuma mu mayar da manyan 5, za mu iya amfani da tambaya mai zuwa:
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)
A cikin tambayar da ke sama, ma'aikaci ya tattara albashi sannan a yi jimlar.
Abin farin ciki, ba ma buƙatar gudanar da tambaya bayan tambaya don samar da rahoto. Madadin haka, zamu iya ƙirƙirar rubutun tare da jerin umarnin SQL don dawo da duk saitin sakamako masu mahimmanci.
Da zarar mun aiwatar da rubutun, zai dawo da bayanan da ake buƙata ba tare da ƙarin tsangwama daga ɓangarenmu ba. Misali, bari mu ƙirƙiri fayil mai suna maxminavg.sql a cikin kundin adireshin aiki na yanzu tare da abubuwan ciki masu zuwa:
--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;
Layukan da suka fara da dashes biyu ba a kula da su, kuma ana aiwatar da kowane tambayoyin ɗaya bayan ɗaya. Za mu iya aiwatar da wannan rubutun ko dai daga layin umarni na 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
ko daga MariaDB mai sauri:
# 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
Takaitawa
A cikin wannan labarin mun bayyana yadda ake amfani da ayyuka na MariaDB da yawa don daidaita saitunan sakamakon da aka dawo da bayanan SELECT. Da zarar sun fayyace, ana iya shigar da tambayoyin mutum da yawa a cikin rubutun don aiwatar da shi cikin sauƙi da kuma rage haɗarin kuskuren ɗan adam.
Kuna da wasu tambayoyi ko shawarwari game da wannan labarin? Jin kyauta don sauke mana bayanin kula ta amfani da fom ɗin sharhi a ƙasa. Muna jiran ji daga gare ku!