آموزش نحوه استفاده از Function های متعدد در MySQL و MariaDB - بخش 2

[Habili]

این مقاله دومین بخش از سری مقالات آموزش دستورات MariaDB/MySQL میباشد. برای بررسی بخش اول لینک زیر را مطالعه فرمایید.

آموزش MySQL/MariaDB فارسی برای مبتدیان - بخش 1


در بخش دوم از سری مقالات MySQL/MariaDB برای مبتدیان ما درباره نحوه محدود کردن تعداد ردیف هایی که توسط یک کوئری SELECT برگردانده میشود و همچنین درباره نحوه چیدن نتیجه ای که در اثر یک شرط مشخص در کوئری، توضیح خواهیم داد.

علاوه براین، ما درباره نحوه گروه بندی رکوردها و اجرای عملیات پایه ریاضیات روی فیلدهای عددی آموزش خواهیم داد. تمام این موارد در ایجاد یک اسکریپت SQL که میتوان برای ایجاد گزارشات مفید از آن استفاده کرد، به شما کمک خواهد کرد.


موارد پیش نیاز

برای شروع لطفا مراحل زیر را دنبال کنید.

1 - دیتابیس نمونه employees را دانلود کنید که شامل 6 جدول میباشد، که محتوای جداول 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 

نکته : اگر هنگام اکسترکت کردن فایل دیتابیس با خطای "tar (child): cannot run bzip2: No such file or directory" مواجه شدید به خاطر نصب نبودن bzip2 روی سیستم تان میباشد. با توجه به توزیع لینوکسی که دارید، یکی از دستورات زیر را جهت نصب bzip2 اجرا کنید.

# yum install bzip2
OR
# apt-get install bzip2 

2 - به کنسول MariaDB وارد شوید و دیتابیسی با نام employees ایجاد کنید.

[root@centos employees_db]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, 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)

MariaDB [(none)]> 

3 - همانند زیر دیتابیس meployees را به سرور MariaDB خود import کنید.

MariaDB [(none)]> source employees.sql 

1 الی 2 دقیقه منتظر بمانید تا دیتابیس مورد نظر لود شود. (در خاطر داشته باشید که ما در اینجا درباره رکوردهایی صحبت میکنیم که حجمشان 4M است.)

4 - در اینجا با لیست کردن جداول دیتابیس، از import شدن درست و کامل آن مطمئن شوید.

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.00 sec)

MariaDB [employees]> 

5 - یک اکانت خاص برای استفاده از دیتابیس employees ایجاد کنید. (میتوانید از هر user و password به دلخواه خودتان استفاده کنید.)

MariaDB [employees]> CREATE USER empadmin@localhost IDENTIFIED BY '123456';Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> GRANT ALL PRIVILEGES ON  employees.* to empadmin@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> exit
Bye 

اکنون با یوزر empadmin به کنسول MariaDB لاگین کنید.

[root@centos employees_db]# mysql -u empadmin -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, 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
MariaDB [employees]> 

قبل از ادامه مطمئن شوید که تمامی مراحلی که در بالا مطرح شد به درستی انجام شده باشند.



چیدن و محدود کردن تعداد ردیف ها (Rows) در مجموعه نتایج

جدول حقوق ها شامل تمام دریافتی های هر کارمند به همراه تاریخ شروع و پایان کار میباشد. ممکن است بخواهیم که حقوق کارمند شماره emp_no=10001 را در طی ماه ها مشاهده کنیم. که با این کار به سوالات زیر میتوان پاسخ دهیم.

• آیا این شخص هیچ گونه افزایش حقوقی داشته است؟
• و اگر داشته چه تاریخی بوده؟

برای این منظور query زیر را اجرا میکنیم.

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.05 sec)

MariaDB [employees]> 

اکنون اگر بخواهیم 5 افزایش حقوق آخر را مشاهده کنیم چه؟ ما میتوانیم از ORDER BY 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)

MariaDB [employees]> 

شما همچنین میتوانید 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.37 sec)

MariaDB [employees]> 

دقت کنید که این افراد در روز یکسانی به دنیا آمده اند، اما آنها با توجه به نام فامیل شان به صورت نزولی مرتب شده اند. شبیه به همین مورد نیز در مورد گروه زیر صادق است. (که تاریخ تولد آنها 1952 میباشد.)

شما میتوانید اطلاعات بیشتر درباره LIMIT را از اینجا مشاهده نمایید.



گروه بندی Record ها، MAX ,MIN ,AVG و ROUND

همانطور که قبلا اشاره کردیم جدول salaries شامل دریافتی های هر کارمند طی ماه های سال میباشد. علاوه بر LIMIT ما میتوانیم از عبارات MAX و MIN برای مشخص کردن زمانیکه maximum و minimum تعداد از کارمندان استخدام شده اند استفاده کنیم.

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.09 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]> 

بر اساس مجموعه نتایج بالا شما میتوانید حدس بزنید که کوئری زیر چه نتیجه ای را خواهد برگرداند.

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.00 sec)

MariaDB [employees]> 

اگر شما حدس میزنید که این query میانگین حقوق در طی ماه ها (که توسط AVG مشخص شده است) را که تا 2 اعشار رند شده است خواهد برگرداند، باید بگوییم که حق با شماست.

اگر بخواهید جمع حقوق ها که توسط کارمند گروه بندی میشود و 5 مورد اول آنرا مشاهده کنید می توانید از query زیر استفاده کنید.

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 (1.66 sec)

MariaDB [employees]> 

در query بالا حقوق ها توسط employee گروه بندی شده اند و سپس عملیات جمع اجرا میشود.



استفاده از تمام موارد بالا به صورت یکجا

خوشبختانه برای ایجاد کردن یک گزارش ما نیاز نداریم که بعد از یک کوئری، query دیگری اجرا کنیم. در عوض ما میتوانیم اسکریپتی را با یکسری از دستورات 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; 

خط هایی که با دو دش "--" آغاز شده اند لحاظ نمیشوند و کوئری های مجزا یکی بعد از دیگری اجرا میشوند. همچنین ما میتوانیم این اسکریپت را از طریق خط فرمان لینوکس نیز اجرا کنیم.

[root@centos employees_db]# 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

[root@centos employees_db]# mysql -u empadmin -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, 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 برگردانده میشود را آموزش دادیم. به محض اینکه آنها اصلاح میشوند چندین کوئری مجزا را میتوان در یک اسکریپت وارد کرد، تا آنرا آسانتر اجرا کرد و ریسک خطای انسانی را کم کرد.

در صورتی که هرگونه پیشنهاد یا سوالی درباره این پست دارید میتوانید در همینجا مطرح کنید. منتظر شنیدن نظرات ارزشمند شما هستیم.