این مقاله دومین بخش از سری مقالات آموزش دستورات MariaDB/MySQL میباشد. برای بررسی بخش اول لینک زیر را مطالعه فرمایید.
آموزش MySQL/MariaDB فارسی برای مبتدیان - بخش 1
در بخش دوم از سری مقالات MySQL/MariaDB برای مبتدیان ما درباره نحوه محدود کردن تعداد ردیف هایی که توسط یک کوئری SELECT برگردانده میشود و همچنین درباره نحوه چیدن نتیجه ای که در اثر یک شرط مشخص در کوئری، توضیح خواهیم داد.
علاوه براین، ما درباره نحوه گروه بندی رکوردها و اجرای عملیات پایه ریاضیات روی فیلدهای عددی آموزش خواهیم داد. تمام این موارد در ایجاد یک اسکریپت SQL که میتوان برای ایجاد گزارشات مفید از آن استفاده کرد، به شما کمک خواهد کرد.
موارد پیش نیاز
برای شروع لطفا مراحل زیر را دنبال کنید.
1 - دیتابیس نمونه employees را دانلود کنید که شامل 6 جدول میباشد، که محتوای جداول 4 میلیون رکورد میباشد.
نکته : اگر هنگام اکسترکت کردن فایل دیتابیس با خطای "tar (child): cannot run bzip2: No such file or directory" مواجه شدید به خاطر نصب نبودن bzip2 روی سیستم تان میباشد. با توجه به توزیع لینوکسی که دارید، یکی از دستورات زیر را جهت نصب bzip2 اجرا کنید.
2 - به کنسول MariaDB وارد شوید و دیتابیسی با نام employees ایجاد کنید.
3 - همانند زیر دیتابیس meployees را به سرور MariaDB خود import کنید.
1 الی 2 دقیقه منتظر بمانید تا دیتابیس مورد نظر لود شود. (در خاطر داشته باشید که ما در اینجا درباره رکوردهایی صحبت میکنیم که حجمشان 4M است.)
4 - در اینجا با لیست کردن جداول دیتابیس، از import شدن درست و کامل آن مطمئن شوید.
5 - یک اکانت خاص برای استفاده از دیتابیس employees ایجاد کنید. (میتوانید از هر user و password به دلخواه خودتان استفاده کنید.)
اکنون با یوزر empadmin به کنسول MariaDB لاگین کنید.
قبل از ادامه مطمئن شوید که تمامی مراحلی که در بالا مطرح شد به درستی انجام شده باشند.
چیدن و محدود کردن تعداد ردیف ها (Rows) در مجموعه نتایج
جدول حقوق ها شامل تمام دریافتی های هر کارمند به همراه تاریخ شروع و پایان کار میباشد. ممکن است بخواهیم که حقوق کارمند شماره emp_no=10001 را در طی ماه ها مشاهده کنیم. که با این کار به سوالات زیر میتوان پاسخ دهیم.
برای این منظور query زیر را اجرا میکنیم.
اکنون اگر بخواهیم 5 افزایش حقوق آخر را مشاهده کنیم چه؟ ما میتوانیم از ORDER BY from_date DESC استفاده کنیم. کلمه DESC مشخص میکند که ما میخواهیم مجموعه نتایج را به ترتیب نزولی بچینیم.
علاوه براین، LIMIT 5 به ما این امکان را میدهد که تنها 5 ردیف اول را در مجموعه نتایج داشته باشیم.
شما همچنین میتوانید ORDER BY را با چندین فیلد استفاده کنید. به طور مثال کوئری زیر مجموعه نتایج را بر اساس تاریخ تولد کارمندان به شکل صعودی (پیش فرض) و سپس توسط نام فامیل آنها به فرمت نزولی حروف الفبایی مرتب خواهد کرد.
دقت کنید که این افراد در روز یکسانی به دنیا آمده اند، اما آنها با توجه به نام فامیل شان به صورت نزولی مرتب شده اند. شبیه به همین مورد نیز در مورد گروه زیر صادق است. (که تاریخ تولد آنها 1952 میباشد.)
شما میتوانید اطلاعات بیشتر درباره LIMIT را از اینجا مشاهده نمایید.
گروه بندی Record ها، MAX ,MIN ,AVG و ROUND
همانطور که قبلا اشاره کردیم جدول salaries شامل دریافتی های هر کارمند طی ماه های سال میباشد. علاوه بر LIMIT ما میتوانیم از عبارات MAX و MIN برای مشخص کردن زمانیکه maximum و minimum تعداد از کارمندان استخدام شده اند استفاده کنیم.
بر اساس مجموعه نتایج بالا شما میتوانید حدس بزنید که کوئری زیر چه نتیجه ای را خواهد برگرداند.
اگر شما حدس میزنید که این query میانگین حقوق در طی ماه ها (که توسط AVG مشخص شده است) را که تا 2 اعشار رند شده است خواهد برگرداند، باید بگوییم که حق با شماست.
اگر بخواهید جمع حقوق ها که توسط کارمند گروه بندی میشود و 5 مورد اول آنرا مشاهده کنید می توانید از query زیر استفاده کنید.
در query بالا حقوق ها توسط employee گروه بندی شده اند و سپس عملیات جمع اجرا میشود.
استفاده از تمام موارد بالا به صورت یکجا
خوشبختانه برای ایجاد کردن یک گزارش ما نیاز نداریم که بعد از یک کوئری، query دیگری اجرا کنیم. در عوض ما میتوانیم اسکریپتی را با یکسری از دستورات SQL ایجاد کنیم که تمام مجموعه نتایج مورد نیازمان را برگرداند.
به محض اینکه ما اسکریپت را اجرا کنیم، اطلاعات مورد نیاز بدون هیچ گونه مداخله اضافی نمایش داده خواهد شد. به طور مثال، فایلی به نام maxminavg.sql را در دایرکتوری جاری با محتوای زیر ایجاد میکنیم.
خط هایی که با دو دش "--" آغاز شده اند لحاظ نمیشوند و کوئری های مجزا یکی بعد از دیگری اجرا میشوند. همچنین ما میتوانیم این اسکریپت را از طریق خط فرمان لینوکس نیز اجرا کنیم.
یا از طریق کنسول MariaDB
خلاصه
در این پست ما نحوه استفاده از چندین فانکشن MariaDB به منظور اصلاح مجموعه نتایجی که توسط عبارات SELECT برگردانده میشود را آموزش دادیم. به محض اینکه آنها اصلاح میشوند چندین کوئری مجزا را میتوان در یک اسکریپت وارد کرد، تا آنرا آسانتر اجرا کرد و ریسک خطای انسانی را کم کرد.
در صورتی که هرگونه پیشنهاد یا سوالی درباره این پست دارید میتوانید در همینجا مطرح کنید. منتظر شنیدن نظرات ارزشمند شما هستیم.
آموزش 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
کد PHP:
# yum install bzip2
OR
# apt-get install bzip2
کد 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 (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)]>
کد PHP:
MariaDB [(none)]> source employees.sql
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]>
کد PHP:
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
کد 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 (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 زیر را اجرا میکنیم.
کد 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]>
علاوه براین، 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]>
شما همچنین میتوانید ORDER BY را با چندین فیلد استفاده کنید. به طور مثال کوئری زیر مجموعه نتایج را بر اساس تاریخ تولد کارمندان به شکل صعودی (پیش فرض) و سپس توسط نام فامیل آنها به فرمت نزولی حروف الفبایی مرتب خواهد کرد.
کد PHP:
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 تعداد از کارمندان استخدام شده اند استفاده کنیم.
کد PHP:
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]>
بر اساس مجموعه نتایج بالا شما میتوانید حدس بزنید که کوئری زیر چه نتیجه ای را خواهد برگرداند.
کد PHP:
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]>
اگر بخواهید جمع حقوق ها که توسط کارمند گروه بندی میشود و 5 مورد اول آنرا مشاهده کنید می توانید از query زیر استفاده کنید.
کد PHP:
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 دیگری اجرا کنیم. در عوض ما میتوانیم اسکریپتی را با یکسری از دستورات SQL ایجاد کنیم که تمام مجموعه نتایج مورد نیازمان را برگرداند.
به محض اینکه ما اسکریپت را اجرا کنیم، اطلاعات مورد نیاز بدون هیچ گونه مداخله اضافی نمایش داده خواهد شد. به طور مثال، فایلی به نام maxminavg.sql را در دایرکتوری جاری با محتوای زیر ایجاد میکنیم.
کد PHP:
--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;
خط هایی که با دو دش "--" آغاز شده اند لحاظ نمیشوند و کوئری های مجزا یکی بعد از دیگری اجرا میشوند. همچنین ما میتوانیم این اسکریپت را از طریق خط فرمان لینوکس نیز اجرا کنیم.
کد PHP:
[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
کد 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 (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 برگردانده میشود را آموزش دادیم. به محض اینکه آنها اصلاح میشوند چندین کوئری مجزا را میتوان در یک اسکریپت وارد کرد، تا آنرا آسانتر اجرا کرد و ریسک خطای انسانی را کم کرد.
در صورتی که هرگونه پیشنهاد یا سوالی درباره این پست دارید میتوانید در همینجا مطرح کنید. منتظر شنیدن نظرات ارزشمند شما هستیم.