استخدام الدوال في قواعد بيانات MySQL

سنشرح في هذه الدرس كيفية تقليل عدد السجلات المُعادة من طلبية SELECT، وطريقة ترتيب الناتج بناءً على شرطٍ معيّن.
إضافةً إلى ما سبق، سنتعلم كيفية تجميع السجلات وإجراء عمليات حسابية أساسية على الحقول الرقمية، وكل ما سبق سيساعدنا على إنشاء سكربت SQL الذي سنستخدمه لإنشاء تقارير مفيدة.
إذا لم تكن لديك خلفية جيّدة حول قواعد بيانات MySQL فأنصحك بقراءة هذا الدّرس أوّلا

المتطلبات المسبقة

عليك اتباع الخطوات الآتية قبل المتابعة:

نزِّل قاعدة بيانات employees، التي تحتوي على ستة جداول تتضمن حوالي 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

ادخل إلى مِحَث MariaDB وأنشِئ قاعدة بيانات باسم employees:

# 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)

استورد قاعدة البيانات إلى خادوم MairaDB كالآتي:

MariaDB [(none)]> source employees.sql

انتظر دقيقةً أو دقيقتين إلى أن ينتهي استيراد قاعدة البيانات (أبقِ في بالك أننا نتحدث هنا عن أربعة ملايين سجل!).

تأكد من أنَّ استيراد قاعدة البيانات قد تمَّ بشكلٍ صحيح بعرض جداولها:

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)

أنشِئ حسابًا مخصصًا لاستخدام قاعدة بيانات employees (اختر اسمًا وكلمة مرور):

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

سجِّل دخولك الآن إلى مِحَث MariaDB بالمستخدم empadmin:

# 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

احرص على تنفيذ جميع الخطوات الست السابقة قبل إكمالك لقراءة هذا الدرس.

ترتيب وتقليل عدد السجلات المعروضة

جدول الرواتب salaries يحتوي على جميع واردات كل موظف مع تاريخ البداية والنهاية. ربما تود أن تعرض رواتب الموظف 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)

لكن ماذا لو أردنا معرفة آخر خمس زيادات؟ يمكننا أن نستخدم ORDER BY form_date DESC. تُشير الكلمة المحجوزة DESC إلى أنَّ الترتيب الذي نريد اتباعه هو الترتيب التنازلي.
إضافةً إلى ما سبق، يسمح لنا التعبير LIMIT 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

كما ذكرنا سابقًا، الجدول salaries يحتوي على رواتب الموظفين خلال فترات زمنية، وبجانب استخدام 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) للراتب مقرّبًا إلى منزلتين عشريتين (عبر ROUND)، فأنت مصيب.
إذا أردنا أن نعرف مجموع رواتب الموظفين وإعادة أوّل خمسة، فيمكننا حينها استعمال الطلبية الآتية:

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)

جمّعنا الرواتب في الطلبية السابقة عبر الموظف، ثم أجرينا عليها عملية SUM.

تجميع المعلومات السابقة في سكربت

لحسن الحظ، لا نحتاج إلى تشغيل طلبية تلو أخرى لإنشاء تقرير، وإنما نستطيع إنشاء سكربت فيه سلسلة من تعليمات 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;

الأسطر التي تبدأ بشرطتين -- هي تعليقات وسيتم تجاهلها. وستُنفَّذ الطلبيات تلو بعضها. ويمكننا تنفيذ هذا السكربت إما من سطر أوامر لينكس:

# 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. وبعد أن تكتب الاستعلامات لأول مرة، فيمكنك بكل بساطة وضعها داخل سكربت وتنفيذها بسهولة مما سيُقلِّل من الخطأ البشري عند كتابتها.
ترجمة -وبتصرّف- للمقال Learn How to Use Several Functions of MySQL and MariaDB – Part 2 لصاحبه Gabriel Cánepa.