Opi käyttämään useita MySQL: n ja MariaDB: n toimintoja - osa 2
Tämä on toinen osa 2-artikkelista sarjaa, joka käsittelee MariaDB/MySQL-komentojen olennaisia osia. Katso edellinen artikkelimme tästä aiheesta ennen jatkamista.
- Opi MySQL/MariaDB-perusteet aloittelijoille - osa 1
MySQL/MariaDB-aloittelijasarjan tässä toisessa osassa selitämme, kuinka rajoitetaan SELECT-kyselyn palauttamien rivien määrää ja kuinka tilataan tulosjoukko tietyn ehdon perusteella.
Lisäksi opitaan ryhmittelemään tietueet ja suorittamaan matemaattinen peruskäsittely numeerisissa kentissä. Kaikki tämä auttaa meitä luomaan SQL-komentosarjan, jonka avulla voimme tuottaa hyödyllisiä raportteja.
Aloita seuraavasti:
1. Lataa työntekijät -näytetietokanta, joka sisältää kuusi taulukkoa, joissa on yhteensä 4 miljoonaa tietuetta.
# 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. Anna MariaDB-kehote ja luo työntekijöiden niminen tietokanta:
# 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. Tuo se MariaDB-palvelimellesi seuraavasti:
MariaDB [(none)]> source employees.sql
Odota 1-2 minuuttia, kunnes näytetietokanta ladataan (pidä mielessä, että puhumme 4M-tietueista täällä!).
4. Varmista, että tietokanta on tuotu oikein, luetteloimalla sen taulukot:
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. Luo erityinen tili työntekijöiden tietokantaan (valitse vapaasti toinen tilin nimi ja salasana):
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
Kirjaudu nyt empadmin-käyttäjänä Mariadb-kehotteeseen.
# 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
Varmista ennen jatkamista, että kaikki yllä olevassa kuvassa kuvatut vaiheet on suoritettu.
Palkka-taulukko sisältää kunkin työntekijän kaikki tulot alkamis- ja päättymispäivillä. Saatamme haluta tarkastella emp_no = 10001 -palkkioita ajan myötä. Tämä auttaa vastaamaan seuraaviin kysymyksiin:
- Saiko hän korotuksia?
- Jos on, milloin?
Suorita seuraava kysely selvittääksesi:
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)
Entä jos meidän on tarkasteltava viimeisimpiä 5 korotusta? Voimme tehdä ORDER BY from_date DESC. DESC-avainsana osoittaa, että haluamme lajitella tulosjoukon laskevassa järjestyksessä.
Lisäksi LIMIT 5 antaa meille mahdollisuuden palauttaa vain 5 parasta riviä tulosjoukossa:
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)
Voit myös käyttää ORDER BY useita kenttiä. Esimerkiksi seuraava kysely järjestää tulosjoukon työntekijän syntymäpäivän perusteella nousevassa muodossa (oletus) ja sitten sukunimien mukaan aakkosjärjestyksessä laskevassa muodossa:
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)
Voit tarkastella lisätietoja LIMITista täältä.
Kuten aiemmin mainitsimme, palkat -taulukko sisältää kunkin työntekijän tulot ajan mittaan. LIMITin lisäksi voimme käyttää MAX- ja MIN-avainsanoja sen määrittämiseen, milloin suurin ja pienin työntekijöiden määrä palkattiin:
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)
Voitko arvata yllä olevien tulosjoukkojen perusteella, mitä alla oleva kysely palauttaa?
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)
Jos hyväksyt, että se palauttaa keskimääräisen (AVG: n määrittelemän) palkan ajan myötä pyöristettynä kahteen desimaaliin (kuten ROUND osoittaa), olet oikeassa.
Jos haluamme tarkastella palkkojen summaa työntekijöiden mukaan ja palauttaa 5 parasta, voimme käyttää seuraavaa kyselyä:
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)
Yllä olevassa kyselyssä palkat ryhmitellään työntekijän mukaan ja suoritetaan sitten summa.
Onneksi meidän ei tarvitse suorittaa kyselyä kyselyn jälkeen raportin tuottamiseksi. Sen sijaan voimme luoda komentosarjan, jossa on joukko SQL-komentoja, palauttamaan kaikki tarvittavat tulosjoukot.
Kun komento on suoritettu, se palauttaa vaaditut tiedot ilman lisätoimenpiteitä. Luodaan esimerkiksi tiedosto maxminavg.sql nykyiseen työhakemistoon, jonka sisältö on seuraava:
--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;
Kahden viivan alkavat rivit jätetään huomioimatta, ja yksittäiset kyselyt suoritetaan yksi toisensa jälkeen. Voimme suorittaa tämän komentosarjan joko Linux-komentoriviltä:
# 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
tai MariaDB-kehotteesta:
# 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
Yhteenveto
Tässä artikkelissa olemme selittäneet, kuinka useita MariaDB-funktioita voidaan käyttää SELECT-lauseiden palauttamien tulosjoukkojen tarkentamiseen. Kun ne on määritelty, komentosarjaan voidaan lisätä useita yksittäisiä kyselyitä, jotta se voidaan suorittaa helpommin ja vähentää inhimillisten virheiden riskiä.
Onko sinulla kysyttävää tai ehdotuksia tästä artikkelista? Voit vapaasti pudottaa meille muistiinpanon alla olevan kommenttilomakkeen avulla. Me odotamme yhteydenottoasi!