Õppige kasutama MySQLi ja MariaDB mitut funktsiooni - 2. osa
See on teine osa kahest artiklist koosnevas seerias, mis käsitleb MariaDB/MySQL-i käskude põhitõdesid. Enne jätkamist lugege meie eelmist artiklit sellel teemal.
- Lugege algajatele MySQL/MariaDB põhitõdesid - 1. osa
MySQL/MariaDB algajate seeria teises osas selgitame, kuidas piirata SELECT päringu tagastatavate ridade arvu ja kuidas tellida tulemuste komplekt etteantud tingimuse alusel.
Lisaks õpime kirjeid rühmitama ja põhilisi matemaatilisi manipuleerimisi numbriväljadel. Kõik see aitab meil luua SQL-skripti, mida saaksime kasutada kasulike aruannete koostamiseks.
Alustamiseks toimige järgmiselt.
1. Laadige alla töötajate näidisandmebaas, mis sisaldab kuut tabelit, mis koosnevad kokku 4 miljonist kirjest.
# 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. Sisestage viip MariaDB ja looge andmebaas nimega töötajad:
# 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. Importige see oma MariaDB serverisse järgmiselt:
MariaDB [(none)]> source employees.sql
Oodake 1-2 minutit, kuni näidisandmebaas laaditakse (pidage meeles, et siin räägime 4M-i kirjetest!).
4. Veenduge, et andmebaas imporditi õigesti, loetledes selle tabelid:
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. Looge spetsiaalne konto, mida kasutada töötajate andmebaasis (valige julgelt teine konto nimi ja parool):
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
Nüüd logige empiadmini kasutajana sisse Mariadbi viipasse.
# 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
Enne jätkamist veenduge, et kõik ülaltoodud pildil kirjeldatud toimingud oleksid täidetud.
Palgatabel sisaldab iga töötaja kõiki sissetulekuid koos algus- ja lõppkuupäevadega. Võime soovida aja jooksul vaadata emp_no = 10001 palku. See aitab vastata järgmistele küsimustele:
- Kas ta sai tõsteid?
- Kui jah, siis millal?
Selle teadasaamiseks täitke järgmine päring:
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)
Mis siis saab, kui peame vaatama viimast 5 tõstet? Saame tellimuse TEGEMISEKS alates_kuupäev DESC. Märksõna DESC näitab, et soovime tulemuste komplekti sortida kahanevas järjekorras.
Lisaks võimaldab LIMIT 5 tagastada ainult tulemuste komplekti 5 ülemist rida:
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)
ORDER BY-d saab kasutada ka mitme väljaga. Näiteks tellib järgmine päring töötaja sünnikuupäeva põhjal kasvava (vaikesäte) ja seejärel perekonnanimede järgi tähestikulises kahanevas vormis:
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)
LIMITi kohta saate lisateavet vaadata siit.
Nagu me varem mainisime, sisaldab tabel palgad iga töötaja sissetulekut aja jooksul. Lisaks LIMIT-ile saame märksõnu MAX ja MIN kasutada selleks, et teha kindlaks, millal palgati töötajate maksimaalne ja minimaalne arv:
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)
Kas saate ülaltoodud tulemuste põhjal arvata, mida allpool olev päring tagastab?
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)
Kui nõustute, et see tagastab aja jooksul keskmise (nagu AVG täpsustas) palga, ümardatuna kahekümnendkohani (nagu tähistab ROUND), on teil õigus.
Kui soovime vaadata palgade summat töötaja järgi rühmitatuna ja tagastada 5 parimat, võime kasutada järgmist päringut:
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)
Ülaltoodud päringus rühmitatakse palgad töötaja järgi ja seejärel tehakse summa.
Õnneks pole meil vaja aruande koostamiseks päringut pärast päringut käivitada. Selle asemel saame kõigi vajalike tulemuste komplektide tagastamiseks luua skripti koos rea SQL-käskudega.
Kui skript on käivitatud, tagastab see vajaliku teabe ilma meie edasise sekkumiseta. Näiteks loome praeguses töökataloogis järgmise nimega fail nimega maxminavg.sql:
--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;
Kahe kriipsuga algavaid ridu eiratakse ja üksikud päringud täidetakse üksteise järel. Selle skripti saame käivitada kas Linuxi käsurealt:
# 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
või MariaDB viipast:
# 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
Kokkuvõte
Selles artiklis oleme selgitanud, kuidas kasutada mitmeid MariaDB funktsioone SELECT-lausete tagastatud tulemushulkade täpsustamiseks. Kui need on määratletud, saab skripti sisestada mitu üksikut päringut, et seda lihtsamalt täita ja vähendada inimlike eksimuste riski.
Kas teil on selle artikli kohta küsimusi või ettepanekuid? Heitke meile märkus, kasutades allolevat kommentaarivormi. Ootame teid huviga!