Sužinokite, kaip naudotis keliomis „MySQL“ ir „MariaDB“ funkcijomis - 2 dalis
Tai yra antroji 2 straipsnių serijos apie esminius „MariaDB/MySQL“ komandų dalis. Prieš tęsdami, perskaitykite mūsų ankstesnį straipsnį šia tema.
- Išmokite „MySQL/MariaDB“ pagrindų pradedantiesiems - 1 dalis
Šioje „MySQL/MariaDB“ pradedančiųjų serijos dalyje paaiškinsime, kaip apriboti eilučių, kurias grąžina SELECT užklausa, skaičių ir kaip užsakyti rezultatų rinkinį pagal tam tikrą sąlygą.
Be to, sužinosime, kaip grupuoti įrašus ir atlikti pagrindinius matematinius manipuliavimus skaitiniais laukais. Visa tai padės mums sukurti SQL scenarijų, kurį galime naudoti naudingoms ataskaitoms kurti.
Norėdami pradėti, atlikite šiuos veiksmus:
1. Atsisiųskite pavyzdinę duomenų bazę darbuotojai , kurią sudaro šešios lentelės, iš viso sudarytos iš 4 milijonų įrašų.
# 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. Įveskite „MariaDB“ eilutę ir sukurkite duomenų bazę, pavadintą darbuotojai:
# 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. Importuokite jį į savo „MariaDB“ serverį taip:
MariaDB [(none)]> source employees.sql
Palaukite 1-2 minutes, kol bus įkelta pavyzdinė duomenų bazė (nepamirškite, kad čia kalbame apie 4M įrašus!).
4. Patikrinkite, ar duomenų bazė buvo teisingai importuota, pateikdami lenteles:
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. Sukurkite specialią paskyrą, kurią naudosite su darbuotojų duomenų baze (nedvejodami pasirinkite kitą paskyros pavadinimą ir slaptažodį):
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
Dabar prisijunkite kaip „empadmin“ vartotojas į „Mariadb“ raginimą.
# 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
Prieš tęsdami įsitikinkite, kad visi aukščiau pateiktame paveikslėlyje aprašyti veiksmai buvo atlikti.
Atlyginimų lentelėje pateikiamos visos kiekvieno darbuotojo pajamos su pradžios ir pabaigos datomis. Galbūt norėsime peržiūrėti emp_no = 10001 atlyginimus laikui bėgant. Tai padės atsakyti į šiuos klausimus:
- Ar jis/ji gavo pakėlimų?
- Jei taip, kada?
Norėdami sužinoti, atlikite šią užklausą:
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)
Ką daryti, jei mums reikia peržiūrėti naujausius 5 pakėlimus? Galime atlikti UŽSAKYMĄ BY__data DESC. DESC raktinis žodis rodo, kad norime surūšiuoti rezultatų rinkinį mažėjančia tvarka.
Be to, LIMIT 5 leidžia mums grąžinti tik 5 geriausias rezultatų rinkinio eilutes:
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)
Taip pat galite naudoti ORDER BY su keliais laukais. Pvz., Pagal šią užklausą rezultatų rinkinys bus surūšiuotas pagal darbuotojo gimimo datą didėjimo forma (numatytasis nustatymas), o tada pagal pavardes abėcėlės tvarka mažėjančia forma:
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)
Čia galite peržiūrėti daugiau informacijos apie LIMIT.
Kaip jau minėjome anksčiau, lentelėje atlyginimai pateikiamos kiekvieno darbuotojo pajamos per tam tikrą laiką. Be LIMIT, mes galime naudoti raktinius žodžius MAX ir MIN, kad nustatytume, kada buvo priimtas didžiausias ir mažiausias darbuotojų skaičius:
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)
Remdamiesi aukščiau pateiktais rezultatų rinkiniais, ar galite atspėti, ką pateiks žemiau pateikta užklausa?
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)
Jei sutinkate, kad jis grąžins vidutinį (kaip nurodo AVG) atlyginimą per laiką, suapvalintą iki 2 skaitmenų po kablelio (kaip nurodoma ROUND), esate teisus.
Jei norime peržiūrėti pagal darbuotojus sugrupuotas atlyginimų sumas ir grąžinti 5 geriausius, galime naudoti šią užklausą:
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)
Pirmiau pateiktoje užklausoje atlyginimai grupuojami pagal darbuotojus ir tada atliekama suma.
Laimei, norint sukurti ataskaitą, nereikia vykdyti užklausos po užklausos. Vietoj to galime sukurti scenarijų su SQL komandų serija, kad grąžintume visus reikalingus rezultatų rinkinius.
Kai įvykdysime scenarijų, jis grąžins reikalingą informaciją be tolesnių mūsų įsikišimų. Pavyzdžiui, sukurkime failą pavadinimu maxminavg.sql dabartiniame darbo kataloge su tokiu turiniu:
--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;
Eilučių, prasidedančių dviem brūkšneliais, nepaisoma, o atskiros užklausos vykdomos viena po kitos. Šį scenarijų galime vykdyti naudodami „Linux“ komandinę eilutę:
# 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
arba iš „MariaDB“ raginimo:
# 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
Santrauka
Šiame straipsnyje mes paaiškinome, kaip naudoti kelias „MariaDB“ funkcijas, norint patikslinti rezultatų rinkinius, kuriuos grąžina SELECT sakiniai. Apibrėžus, scenarijuje gali būti įterptos kelios atskiros užklausos, kad būtų lengviau jį vykdyti ir kad būtų sumažinta žmogaus klaidų rizika.
Ar turite klausimų ar pasiūlymų dėl šio straipsnio? Nedvejodami meskite mums pastabą naudodami toliau pateiktą komentaro formą. Lauksime jūsų!