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

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


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

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


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

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

1 - دیتابیس نمونه employees را دانلود کنید که شامل 6 جدول میباشد، که محتوای جداول 4 میلیون رکورد میباشد.
کد PHP:
# 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 اجرا کنید.
کد PHP:
# yum install bzip2
OR
# apt-get install bzip2 
2 - به کنسول MariaDB وارد شوید و دیتابیسی با نام employees ایجاد کنید.
کد PHP:
[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 
(c20002017OracleMariaDB Corporation Ab and others.

Type 'help;' or '\h' for helpType '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE employees;
Query OK1 row affected (0.00 sec)

MariaDB [(none)]> 
3 - همانند زیر دیتابیس meployees را به سرور MariaDB خود import کنید.
کد PHP:
MariaDB [(none)]> source employees.sql 
1 الی 2 دقیقه منتظر بمانید تا دیتابیس مورد نظر لود شود. (در خاطر داشته باشید که ما در اینجا درباره رکوردهایی صحبت میکنیم که حجمشان 4M است.)

4 - در اینجا با لیست کردن جداول دیتابیس، از import شدن درست و کامل آن مطمئن شوید.
کد PHP:
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 به دلخواه خودتان استفاده کنید.)
کد PHP:
MariaDB [employees]> CREATE USER empadmin@localhost IDENTIFIED BY '123456';Query OK0 rows affected (0.00 sec)

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

MariaDB [employees]> FLUSH PRIVILEGES;
Query OK0 rows affected (0.00 sec)

MariaDB [employees]> exit
Bye 
اکنون با یوزر empadmin به کنسول MariaDB لاگین کنید.
کد PHP:
[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 
(c20002017OracleMariaDB Corporation Ab and others.

Type 'help;' or '\h' for helpType '\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]> 
basic-mysql-commands-beginners-1-png

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



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

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

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


برای این منظور query زیر را اجرا میکنیم.
کد PHP:
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 ردیف اول را در مجموعه نتایج داشته باشیم.
کد PHP:
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]> 
query-mysql-table-date-order-2-png

شما همچنین میتوانید ORDER BY را با چندین فیلد استفاده کنید. به طور مثال کوئری زیر مجموعه نتایج را بر اساس تاریخ تولد کارمندان به شکل صعودی (پیش فرض) و سپس توسط نام فامیل آنها به فرمت نزولی حروف الفبایی مرتب خواهد کرد.
کد PHP:
MariaDB [employees]> SELECT CONCAT(last_name', 'first_name) AS  Namegender AS Gender,  hire_date AS "Hire date" FROM employees ORDER  BY birth_datelast_name DESC LIMIT 10;
+--------------------+--------+------------+
Name               Gender Hire date  |
+--------------------+--------+------------+
WhitcombKiyokazu M      1988-07-26 |
SchaadRonghao    M      1988-07-10 |
RemmeleSupot     M      1989-01-27 |
PocchiolaJouni   M      1985-03-10 |
KuzuokaEishiro   M      1992-02-12 |
DecaesteckerMoni M      1986-10-06 |
WiegleyMircea    M      1985-07-18 |
VendrigSachar    M      1985-11-04 |
TsukudaCedric    F      1993-12-12 |
TischendorfPercy M      1986-11-10 |
+--------------------+--------+------------+
10 rows in set (0.37 sec)

MariaDB [employees]> 
query-mysql-table-birth-date-3-png

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

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



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

همانطور که قبلا اشاره کردیم جدول salaries شامل دریافتی های هر کارمند طی ماه های سال میباشد. علاوه بر LIMIT ما میتوانیم از عبارات MAX و MIN برای مشخص کردن زمانیکه maximum و minimum تعداد از کارمندان استخدام شده اند استفاده کنیم.
کد PHP:
MariaDB [employees]> SELECT CONCAT(last_name', 'first_name) AS  NameMAX(B.salary) AS "Max. salary" FROM employees A JOIN salaries B ON  A.emp_no B.emp_no WHERE A.emp_no IN (100011000210003GROUP BY  A.emp_no;
+-----------------+-------------+
Name            Maxsalary |
+-----------------+-------------+
FacelloGeorgi |       88958 |
SimmelBezalel |       72527 |
BamfordParto  |       43699 |
+-----------------+-------------+
3 rows in set (0.09 sec)

MariaDB [employees]> SELECT CONCAT(last_name', 'first_name) AS  NameMIN(B.salary) AS "Min. salary" FROM employees A JOIN salaries B ON  A.emp_no B.emp_no WHERE A.emp_no IN (100011000210003GROUP BY  A.emp_no;
+-----------------+-------------+
Name            Minsalary |
+-----------------+-------------+
FacelloGeorgi |       60117 |
SimmelBezalel |       65828 |
BamfordParto  |       40006 |
+-----------------+-------------+
3 rows in set (0.00 sec)

MariaDB [employees]> 
grouping-mysql-records-using-max-min-keywords-4-png

بر اساس مجموعه نتایج بالا شما میتوانید حدس بزنید که کوئری زیر چه نتیجه ای را خواهد برگرداند.
کد PHP:
MariaDB [employees]> SELECT CONCAT(last_name', 'first_name) AS  NameROUND(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 (1000110002,  10003GROUP BY A.emp_no;
+-----------------+-------------+
Name            Avgsalary |
+-----------------+-------------+
FacelloGeorgi |    75388.94 |
SimmelBezalel |    68854.50 |
BamfordParto  |    43030.29 |
+-----------------+-------------+
3 rows in set (0.00 sec)

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

اگر بخواهید جمع حقوق ها که توسط کارمند گروه بندی میشود و 5 مورد اول آنرا مشاهده کنید می توانید از query زیر استفاده کنید.
کد PHP:
MariaDB [employees]> SELECT emp_noSUM(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 را در دایرکتوری جاری با محتوای زیر ایجاد میکنیم.
کد PHP:
--Select database
USE employees;
--
Calculate maximum salaries
SELECT CONCAT
(last_name', 'first_name) AS NameMAX(B.salary) AS  "Max. salary" FROM employees A JOIN salaries B ON A.emp_no B.emp_no  WHERE A.emp_no IN (100011000210003GROUP BY A.emp_no;
--
Calculate minimum salaries
SELECT CONCAT
(last_name', 'first_name) AS NameMIN(B.salary) AS  "Min. salary" FROM employees A JOIN salaries B ON A.emp_no B.emp_no  WHERE A.emp_no IN (100011000210003GROUP BY A.emp_no;
--
Calculate averagesround to 2 decimal places
SELECT CONCAT
(last_name', 'first_name) AS NameROUND(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 (100011000210003GROUP BY A.emp_no
create-script-series-sql-commands-4-1-png

خط هایی که با دو دش "--" آغاز شده اند لحاظ نمیشوند و کوئری های مجزا یکی بعد از دیگری اجرا میشوند. همچنین ما میتوانیم این اسکریپت را از طریق خط فرمان لینوکس نیز اجرا کنیم.
کد PHP:
[root@centos employees_db]# mysql -u empadmin -p < maxminavg.sql 
Enter password
Name    Maxsalary
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
کد PHP:
[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 
(c20002017OracleMariaDB Corporation Ab and others.

Type 'help;' or '\h' for helpType '\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 
mysql-script-run-queries-5-png


خلاصه

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

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