Kọ ẹkọ Bii o ṣe le Lo Awọn iṣẹ pupọ ti MySQL ati MariaDB - Apá 2
Eyi ni apakan keji ti iwe-nkan 2 kan nipa awọn pataki ti awọn aṣẹ MariaDB/MySQL. Jọwọ tọka si nkan ti tẹlẹ wa lori koko yii ṣaaju iṣaaju.
- Kọ ẹkọ Awọn ipilẹ MySQL/MariaDB fun Awọn Ibẹrẹ - Apá 1
Ninu apakan keji ti MySQL/MariaDB jara alakọbẹrẹ, a yoo ṣalaye bi a ṣe le ṣe idinwo nọmba ti awọn ori ila ti a pada nipasẹ ibeere SELECT, ati bii o ṣe le paṣẹ abajade ti a ṣeto ti o da lori ipo ti a fifun.
Ni afikun, a yoo kọ bi a ṣe le ṣe akojọpọ awọn igbasilẹ ati ṣe ifọwọyi mathimatiki ipilẹ lori awọn aaye nomba. Gbogbo eyi yoo ran wa lọwọ lati ṣẹda iwe afọwọkọ SQL ti a le lo lati ṣe awọn iroyin to wulo.
Lati bẹrẹ, jọwọ tẹle awọn igbesẹ wọnyi:
1. Ṣe igbasilẹ awọn oṣiṣẹ ibi ipamọ data apẹẹrẹ, eyiti o pẹlu awọn tabili mẹfa ti o ni awọn igbasilẹ miliọnu 4 lapapọ.
# 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. Tẹ iyara MariaDB ki o ṣẹda ipilẹ data ti a npè ni awọn oṣiṣẹ:
# 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. Gbe wọle si olupin MariaDB rẹ bi atẹle:
MariaDB [(none)]> source employees.sql
Duro fun iṣẹju 1-2 titi ti o fi rù ibi ipamọ data apẹẹrẹ (ṣakiyesi pe a n sọrọ nipa awọn igbasilẹ 4M nibi!).
4. Daju pe a ti gbe ibi-ipamọ data wọle daradara nipasẹ atokọ awọn tabili rẹ:
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. Ṣẹda akọọlẹ pataki kan lati lo pẹlu ibi ipamọ data awọn oṣiṣẹ (ni ọfẹ lati yan orukọ akọọlẹ miiran ati ọrọ igbaniwọle):
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
Bayi buwolu wọle bi olumulo empadmin sinu iyara Mariadb.
# 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
Rii daju pe gbogbo awọn igbesẹ ti a ṣe ilana ninu aworan loke ti pari ṣaaju ṣiṣe.
Tabili owo-ọya ni gbogbo awọn owo-ori ti oṣiṣẹ kọọkan ṣiṣẹ pẹlu awọn ọjọ ibẹrẹ ati ipari. A le fẹ lati wo awọn owo sisan ti emp_no = 10001 lori akoko. Eyi yoo ṣe iranlọwọ lati dahun awọn ibeere wọnyi:
- Njẹ o/gba eyikeyi igbega?
- Ti o ba ri bẹ, nigbawo?
Ṣiṣe ibeere atẹle lati wa:
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)
Bayi kini ti a ba nilo lati wo awọn igbega 5 tuntun? A le ṣe PATAKI NIPA from_date DESC. Koko ọrọ DESC tọka pe a fẹ lati to lẹsẹsẹ abajade ti a ṣeto ni aṣẹ sọkalẹ.
Ni afikun, LIMIT 5 gba wa laaye lati pada nikan awọn ori ila 5 to ga julọ ninu ṣeto abajade:
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)
O tun le lo Bere fun BY pẹlu awọn aaye pupọ. Fun apẹẹrẹ, ibeere atẹle yoo paṣẹ abajade ti a ṣeto ti o da lori ọjọ ibi ti oṣiṣẹ ni fọọmu ti o gòke (aiyipada) ati lẹhinna nipasẹ awọn orukọ ti o kẹhin ni fọọmu sọkalẹ abidi:
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)
O le wo alaye diẹ sii nipa LIMIT nibi.
Gẹgẹbi a ti mẹnuba tẹlẹ, tabili awọn owo-owo tabili ni awọn owo-wiwọle ti oṣiṣẹ kọọkan kọja akoko. Yato si LIMIT, a le lo awọn ọrọ-ọrọ MAX ati MIN lati pinnu nigba ti o pọju ati nọmba to kere julọ ti awọn oṣiṣẹ ti bẹwẹ:
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)
Da lori awọn ipilẹ abajade loke, ṣe o le gboju le won kini ibeere ti isalẹ yoo pada?
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)
Ti o ba gba pe yoo pada apapọ (gẹgẹ bi a ti ṣalaye nipasẹ AVG) owo sisan lori akoko ti o yika si awọn nomba eleemewa 2 (gẹgẹ bi a ti tọka si ROUND), o tọ.
Ti a ba fẹ lati wo apao awọn owo-iṣẹ ti a ṣajọ nipasẹ oṣiṣẹ ki o pada si oke 5, a le lo ibeere atẹle:
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)
Ninu ibeere ti o wa loke, awọn oṣiṣẹ ti ṣajọpọ nipasẹ oṣiṣẹ lẹhinna iye owo ti ṣe.
Ni akoko, a ko nilo lati ṣiṣe ibeere lẹhin ibeere lati ṣe ijabọ kan. Dipo, a le ṣẹda iwe afọwọkọ kan pẹlu lẹsẹsẹ awọn aṣẹ SQL lati da gbogbo awọn ipilẹ abajade to wulo pada.
Ni kete ti a ba ṣiṣẹ iwe afọwọkọ naa, yoo da alaye ti o nilo pada laisi idawọle siwaju si apakan wa. Fun apẹẹrẹ, jẹ ki a ṣẹda faili ti a npè ni maxminavg.sql ninu itọsọna iṣẹ lọwọlọwọ pẹlu awọn akoonu wọnyi:
--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;
Awọn oju ila ti o bẹrẹ pẹlu awọn dashes meji ni a ko bikita, ati pe awọn ibeere kọọkan ni a ṣe lẹẹkọọkan. A le ṣe iwe afọwọkọ yii boya lati laini aṣẹ Linux:
# 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
tabi lati tọ MariaDB:
# 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
Akopọ
Ninu nkan yii a ti ṣalaye bi a ṣe le lo ọpọlọpọ awọn iṣẹ MariaDB lati le ṣe atunṣe awọn ipilẹ abajade ti o pada nipasẹ awọn alaye SELECT. Ni kete ti wọn ti ṣalaye, ọpọlọpọ awọn ibeere kọọkan ni a le fi sii sinu iwe afọwọkọ kan lati ṣe ni irọrun diẹ sii ati lati dinku eewu ti aṣiṣe eniyan.
Ṣe o ni awọn ibeere tabi awọn imọran eyikeyi nipa nkan yii? Ni ominira lati sọ akọsilẹ wa silẹ ni lilo fọọmu asọye ni isalẹ. A n reti lati gbọ lati ọdọ rẹ!