Lærðu hvernig á að nota nokkrar aðgerðir MySQL og MariaDB - Part 2
Þetta er seinni hluti af tveggja greina röð um grundvallaratriði MariaDB/MySQL skipana. Vinsamlegast skoðaðu fyrri grein okkar um þetta efni áður en þú heldur áfram.
- Lærðu MySQL/MariaDB grunnatriði fyrir byrjendur – Part 1
Í þessum seinni hluta MySQL/MariaDB byrjenda röð, munum við útskýra hvernig á að takmarka fjölda lína sem skilað er af SELECT fyrirspurn og hvernig á að raða niðurstöðusettinu út frá tilteknu ástandi.
Að auki munum við læra hvernig á að flokka skrárnar og framkvæma grunn stærðfræðilega meðferð á tölusviðum. Allt þetta mun hjálpa okkur að búa til SQL forskrift sem við getum notað til að búa til gagnlegar skýrslur.
Til að byrja skaltu fylgja þessum skrefum:
1. Sæktu starfsmenn sýnishornið, sem inniheldur sex töflur sem samanstanda af 4 milljón færslum alls.
# 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. Sláðu inn MariaDB hvetja og búðu til gagnagrunn sem heitir starfsmenn:
# 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. Flyttu það inn á MariaDB netþjóninn þinn sem hér segir:
MariaDB [(none)]> source employees.sql
Bíddu í 1-2 mínútur þar til sýnishornsgagnagrunnurinn er hlaðinn (hafðu í huga að við erum að tala um 4M færslur hér!).
4. Staðfestu að gagnagrunnurinn hafi verið fluttur inn á réttan hátt með því að skrá töflur hans:
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. Búðu til sérstakan reikning til að nota með gagnagrunni starfsmanna (velkomið að velja annað reikningsnafn og lykilorð):
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
Skráðu þig nú inn sem empadmin notandi í Mariadb hvetja.
# 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
Gakktu úr skugga um að öllum skrefunum sem lýst er í myndinni hér að ofan hafi verið lokið áður en þú heldur áfram.
Launataflan inniheldur allar tekjur hvers starfsmanns með upphafs- og lokadögum. Við gætum viljað skoða laun emp_no=10001 með tímanum. Þetta mun hjálpa til við að svara eftirfarandi spurningum:
- Fékk hann/hún einhverjar hækkanir?
- Ef svo er, hvenær?
Framkvæmdu eftirfarandi fyrirspurn til að komast að því:
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)
Hvað ef við þurfum að skoða nýjustu 5 hækkanir? Við getum pantað fyrir frá_degi DESC. DESC lykilorðið gefur til kynna að við viljum raða niðurstöðusettinu í lækkandi röð.
Að auki gerir LIMIT 5 okkur kleift að skila aðeins efstu 5 línunum í niðurstöðusettinu:
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)
Þú getur líka notað ORDER BY með mörgum reitum. Til dæmis mun eftirfarandi fyrirspurn raða niðurstöðusettinu út frá fæðingardegi starfsmanns í hækkandi formi (sjálfgefið) og síðan eftir eftirnöfnum í stafrófsröð lækkandi formi:
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)
Þú getur skoðað frekari upplýsingar um LIMIT hér.
Eins og við nefndum áðan inniheldur laun taflan tekjur hvers starfsmanns yfir tíma. Fyrir utan LIMIT getum við notað MAX og MIN leitarorðin til að ákvarða hvenær hámarks- og lágmarksfjöldi starfsmanna var ráðinn:
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)
Byggt á ofangreindum niðurstöðusettum, geturðu giskað á hvað fyrirspurnin hér að neðan mun skila?
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)
Ef þú samþykkir að það skili meðallaunum (eins og tilgreint er af AVG) yfir tíma, námunduð að 2 aukastöfum (eins og gefið er til kynna með UMFERÐ), þá hefurðu rétt fyrir þér.
Ef við viljum skoða summan af launum flokkuð eftir starfsmanni og skila efstu 5, getum við notað eftirfarandi fyrirspurn:
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)
Í ofangreindri fyrirspurn eru laun flokkuð eftir starfsmanni og síðan er summan framreidd.
Sem betur fer þurfum við ekki að keyra fyrirspurn eftir fyrirspurn til að framleiða skýrslu. Í staðinn getum við búið til handrit með röð af SQL skipunum til að skila öllum nauðsynlegum niðurstöðusettum.
Þegar við höfum keyrt handritið mun það skila nauðsynlegum upplýsingum án frekari afskipta af okkar hálfu. Til dæmis skulum við búa til skrá sem heitir maxminavg.sql í núverandi vinnuskrá með eftirfarandi innihaldi:
--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;
Línur sem byrja á tveimur strikum eru hunsaðar og einstakar fyrirspurnir eru framkvæmdar hver á eftir annarri. Við getum framkvæmt þetta handrit annað hvort frá Linux skipanalínunni:
# 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
eða frá MariaDB hvetjunni:
# 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
Samantekt
Í þessari grein höfum við útskýrt hvernig á að nota nokkrar MariaDB aðgerðir til að betrumbæta niðurstöðusett sem skilað er af SELECT yfirlýsingum. Þegar þær hafa skilgreint er hægt að setja margar einstakar fyrirspurnir inn í handrit til að framkvæma það auðveldara og til að draga úr hættu á mannlegum mistökum.
Hefur þú einhverjar spurningar eða tillögur um þessa grein? Ekki hika við að senda okkur athugasemd með því að nota athugasemdareyðublaðið hér að neðan. Okkur hlakkar til að heyra frá þér!