เรียนรู้วิธีใช้ฟังก์ชันต่างๆ ของ MySQL และ MariaDB - ตอนที่ 2
นี่เป็นส่วนที่สองของชุดบทความ 2 บทความเกี่ยวกับความจำเป็นของคำสั่ง MariaDB/MySQL โปรดดูบทความก่อนหน้าของเราในหัวข้อนี้ก่อนดำเนินการต่อ
- เรียนรู้พื้นฐาน MySQL/MariaDB สำหรับผู้เริ่มต้น – ตอนที่ 1
ในส่วนที่สองของซีรีส์เริ่มต้น MySQL/MariaDB นี้ เราจะอธิบายวิธีจำกัดจำนวนแถวที่ส่งคืนโดยการสืบค้น SELECT และวิธีการจัดลำดับชุดผลลัพธ์ตามเงื่อนไขที่กำหนด
นอกจากนี้ เราจะได้เรียนรู้วิธีจัดกลุ่มบันทึกและดำเนินการจัดการทางคณิตศาสตร์ขั้นพื้นฐานในฟิลด์ตัวเลข ทั้งหมดนี้จะช่วยเราสร้างสคริปต์ SQL ที่เราสามารถใช้เพื่อสร้างรายงานที่เป็นประโยชน์ได้
ข้อกำหนดเบื้องต้น
ในการเริ่มต้น โปรดทำตามขั้นตอนเหล่านี้:
1. ดาวน์โหลดฐานข้อมูลตัวอย่าง พนักงาน ซึ่งประกอบด้วยหกตารางที่ประกอบด้วยทั้งหมด 4 ล้านบันทึก
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 และสร้างฐานข้อมูลชื่อ พนักงาน:
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 ของคุณดังนี้:
MariaDB [(none)]> source employees.sql
รอ 1-2 นาทีจนกว่าฐานข้อมูลตัวอย่างจะโหลด (โปรดจำไว้ว่าเรากำลังพูดถึงบันทึก 4M ที่นี่!)
4. ตรวจสอบว่าฐานข้อมูลนำเข้าอย่างถูกต้องโดยแสดงรายการตาราง:
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. สร้างบัญชีพิเศษเพื่อใช้กับฐานข้อมูลพนักงาน (คุณสามารถเลือกชื่อบัญชีและรหัสผ่านอื่นได้ตามใจชอบ):
MariaDB [employees]> CREATE USER empadmin@localhost IDENTIFIED BY 'empadminpass';
Query OK, 0 rows affected (0.03 sec)
MariaDB [employees]> GRANT ALL PRIVILEGES ON employees.* to empadmin@localhost;
Query OK, 0 rows affected (0.02 sec)
MariaDB [employees]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [employees]> exit
Bye
ตอนนี้เข้าสู่ระบบในฐานะผู้ใช้ empadmin ในพรอมต์ 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
ตรวจสอบให้แน่ใจว่าได้ทำตามขั้นตอนทั้งหมดที่ระบุไว้ในภาพด้านบนเรียบร้อยแล้วก่อนดำเนินการต่อ
การจัดลำดับและการจำกัดจำนวนแถวในชุดผลลัพธ์
ตารางเงินเดือนประกอบด้วยรายได้ทั้งหมดของพนักงานแต่ละคนพร้อมวันที่เริ่มต้นและวันที่สิ้นสุด เราอาจต้องการดูเงินเดือนของ emp_no=10001 เมื่อเวลาผ่านไป ซึ่งจะช่วยตอบคำถามต่อไปนี้:
- เขา/เธอได้รับการเพิ่มเงินเดือนหรือไม่?
- ถ้าเป็นเช่นนั้นเมื่อไหร่?
ดำเนินการค้นหาต่อไปนี้เพื่อค้นหา:
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)
แล้วถ้าเราต้องการดูการเพิ่มขึ้น 5 ครั้งล่าสุดล่ะ? เราสามารถ สั่งซื้อตาม from_date DESC ได้ คำหลัก DESC ระบุว่าเราต้องการจัดเรียงชุดผลลัพธ์จากมากไปน้อย
นอกจากนี้ LIMIT 5 ยังช่วยให้เราส่งกลับเฉพาะแถวบนสุด 5 ในชุดผลลัพธ์:
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 กับหลายฟิลด์ได้ ตัวอย่างเช่น แบบสอบถามต่อไปนี้จะจัดลำดับชุดผลลัพธ์ตามวันเกิดของพนักงานในรูปแบบจากน้อยไปหามาก (ค่าเริ่มต้น) จากนั้นตามด้วยนามสกุลในรูปแบบจากมากไปหาน้อยตามตัวอักษร:
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 ได้ที่นี่
การจัดกลุ่มบันทึก/MAX, MIN, AVG และ ROUND
ดังที่เราได้กล่าวไว้ก่อนหน้านี้ ตาราง เงินเดือน ประกอบด้วยรายได้ของพนักงานแต่ละคนในช่วงเวลาหนึ่ง นอกจาก LIMIT แล้ว เราสามารถใช้คำหลัก MAX และ MIN เพื่อพิจารณาว่าเมื่อใดจะจ้างพนักงานจำนวนสูงสุดและต่ำสุด:
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)
จากชุดผลลัพธ์ข้างต้น คุณสามารถเดาได้ไหมว่าข้อความค้นหาด้านล่างนี้จะส่งกลับค่าอะไร
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)
หากคุณยอมรับว่าจะส่งกลับค่าเฉลี่ย (ตามที่ระบุโดย AVG) เงินเดือนตามเวลาโดยปัดเศษเป็นทศนิยม 2 ตำแหน่ง (ตามที่ระบุโดย ROUND) แสดงว่าคุณพูดถูก
หากเราต้องการดูผลรวมของเงินเดือนที่จัดกลุ่มตามพนักงานและส่งคืนค่าสูงสุด 5 เราสามารถใช้แบบสอบถามต่อไปนี้:
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)
ในแบบสอบถามข้างต้น เงินเดือนจะถูกจัดกลุ่มตามพนักงาน จากนั้นจึงดำเนินการผลรวม
นำมารวมกันทั้งหมด
โชคดีที่เราไม่จำเป็นต้องเรียกใช้การสืบค้นครั้งแล้วครั้งเล่าเพื่อสร้างรายงาน แต่เราสามารถสร้างสคริปต์ที่มีชุดคำสั่ง SQL เพื่อส่งคืนชุดผลลัพธ์ที่จำเป็นทั้งหมดแทน
เมื่อเรารันสคริปต์แล้ว มันจะส่งคืนข้อมูลที่จำเป็นโดยที่เราไม่ต้องดำเนินการใดๆ เพิ่มเติม ตัวอย่างเช่น เรามาสร้างไฟล์ชื่อ 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;
บรรทัดที่ขึ้นต้นด้วยขีดกลางสองขีดจะถูกละเว้น และการสืบค้นแต่ละรายการจะถูกดำเนินการทีละรายการ เราสามารถรันสคริปต์นี้ได้จากบรรทัดคำสั่ง 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
หรือจากพรอมต์ 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
สรุป
ในบทความนี้ เราได้อธิบายวิธีใช้ฟังก์ชัน MariaDB หลายฟังก์ชันเพื่อปรับแต่งชุดผลลัพธ์ที่ส่งคืนโดยคำสั่ง SELECT เมื่อกำหนดแล้ว ก็สามารถแทรกการสืบค้นหลายรายการในสคริปต์เพื่อดำเนินการได้ง่ายขึ้น และเพื่อลดความเสี่ยงของข้อผิดพลาดของมนุษย์
คุณมีคำถามหรือข้อเสนอแนะเกี่ยวกับบทความนี้หรือไม่? โปรดส่งข้อความถึงเราโดยใช้แบบฟอร์มความคิดเห็นด้านล่าง เราหวังว่าจะได้ยินจากคุณ!