MySQL we MariaDB-iň birnäçe funksiýasyny nähili ulanmalydygyny öwreniň - 2-nji bölüm
Bu, MariaDB/MySQL buýruklarynyň esaslary barada 2 makalaly seriýanyň ikinji bölümi. Dowam etmezden ozal bu mowzukdaky öňki makalamyza serediň.
- Täze başlanlar üçin MySQL/MariaDB esaslaryny öwreniň - 1-nji bölüm
MySQL/MariaDB başlangyç seriýasynyň bu ikinji bölüminde, SELECT talaplary bilen yzyna gaýtarylan hatarlaryň sanyny nädip çäklendirmelidigini we berlen şert esasynda netijäni nädip sargyt etmelidigini düşündireris.
Mundan başga-da, ýazgylary nädip toparlamalydygyny we san meýdanlarynda esasy matematiki manipulýasiýa etmegi öwreneris. Bularyň hemmesi peýdaly hasabat taýýarlamak üçin ulanyp boljak SQL skriptini döretmäge kömek eder.
Başlamak üçin şu ädimleri ýerine ýetiriň:
1. Jemi 4 million ýazgydan ybarat alty tablisany öz içine alýan işgärleri nusga maglumat bazasyny göçürip alyň.
# 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. MariaDB haýyşyny giriziň we işgärler atly maglumat bazasyny dörediň:
# 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. MariaDB serweriňize aşakdaky ýaly import ediň:
MariaDB [(none)]> source employees.sql
Mysal maglumatlar bazasy ýüklenýänçä 1-2 minut garaşyň (bu ýerde 4M ýazgylar hakda gürleşýändigimizi ýadyňyzdan çykarmaň!).
4. Maglumatlar bazasynyň tablisalaryny görkezmek bilen dogry getirilendigini barlaň:
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. Işgärler bazasy bilen ulanmak üçin ýörite hasap dörediň (başga bir hasap adyny we paroly saýlap bilersiňiz):
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
Indi Mariadb soragyna empadmin ulanyjy hökmünde giriň.
# 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
Dowam etmezden ozal ýokardaky suratda görkezilen ädimleriň hemmesiniň ýerine ýetirilendigine göz ýetiriň.
Aýlyk tablisasynda her işgäriň başlangyç we gutarýan senesi bilen ähli girdejileri bar. Wagtyň geçmegi bilen emp_no=10001 aýlyk haklaryny görmek isläp bileris. Bu aşakdaky soraglara jogap bermäge kömek eder:
- Ol ýokarlandymy?
- Eger şeýle bolsa, haçan?
Muny bilmek üçin aşakdaky soragy ýerine ýetiriň:
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)
Indi iň soňky 5 ýokarlanmany görmeli bolsa näme etmeli? DESC senesinden sargyt edip bileris. DESC açar söz, netijäni aşakdaky tertipde tertiplemek isleýändigimizi görkezýär.
Mundan başga-da, LIMIT 5 netijeler toplumynda diňe iň gowy 5 hatary yzyna gaýtarmaga mümkinçilik berýär:
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)
Şeýle hem ORDER BY-i birnäçe meýdan bilen ulanyp bilersiňiz. Mysal üçin, aşakdaky talap işgäriň doglan senesine baglylykda ýokarlanýan görnüşde (deslapky), soňra bolsa elipbiý boýunça aşak düşýän görnüşde familiýalary görkezer:
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)
LIMIT hakda has giňişleýin maglumaty şu ýerden görüp bilersiňiz.
Öň hem belläp geçişimiz ýaly,
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)
Aboveokardaky netijeler toplumyna esaslanyp, aşakdaky talapyň nämä gaýdyp geljekdigini çaklap bilersiňizmi?
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)
Wagtyň geçmegi bilen ortaça (AVG bilen görkezilişi ýaly) 2 aýlyga (ROUND tarapyndan görkezilişi ýaly) yzyna gaýtaryljakdygyna razy bolsaňyz, dogry aýdýarsyňyz.
Işgärler tarapyndan toparlanan aýlyklaryň jemini görmek we ilkinji 5-ligi yzyna gaýtarmak islesek, aşakdaky soragy ulanyp bileris:
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)
Aboveokardaky talapda aýlyk haklary işgärler tarapyndan toparlanýar we soňra jemi ýerine ýetirilýär.
Bagtymyza, hasabat taýýarlamak üçin talapdan soň talap işlemeli däl. Munuň ýerine, ähli zerur netijeler toplumyny yzyna gaýtarmak üçin SQL buýruklary bilen bir skript döredip bileris.
Scriptazgyny ýerine ýetirenimizden soň, goşmaça goşulyşmazdan zerur maglumatlary yzyna gaýtaryp berer. Mysal üçin, häzirki iş katalogynda aşakdaky mazmunly maxminavg.sql atly bir faýl döredeliň:
--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;
Iki çyzgydan başlanýan setirlere ähmiýet berilmeýär we aýratyn talaplar biri-birine ýerine ýetirilýär. Bu skripti Linux buýruk setirinden hem ýerine ýetirip bileris:
# 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
ýa-da MariaDB haýyşyndan:
# 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
Gysgaça mazmun
Bu makalada, SELECT jümleleri bilen yzyna gaýtarylan netijeler toplumyny arassalamak üçin birnäçe MariaDB funksiýasyny nädip ulanmalydygyny düşündirdik. Ony kesgitlänlerinden soň, has aňsat ýerine ýetirmek we adam ýalňyşlygy howpuny azaltmak üçin skriptde birnäçe aýratyn soraglar goýulyp bilner.
Bu makala barada soraglaryňyz ýa-da teklipleriňiz barmy? Aşakdaky düşündiriş formuny ulanyp, bize bellik goýup bilersiňiz. Sizden eşitmäge sabyrsyzlyk bilen garaşýarys!