اطلاعیه

بستن
No announcement yet.

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

بستن
X
 
  • فیلتر کردن
  • زمان
  • نمایش
Clear All
پست های جدید

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

    این مقاله دومین بخش از سری مقالات آموزش دستورات 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-for-Beginners-1.png مشاهده: 1 حجم: 19.5 کیلو بایت

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



    چیدن و محدود کردن تعداد ردیف ها (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-By-Date-Order-2.png مشاهده: 1 حجم: 9.8 کیلو بایت

    شما همچنین میتوانید 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-by-Birth-Date-3.png مشاهده: 1 حجم: 15.9 کیلو بایت

    دقت کنید که این افراد در روز یکسانی به دنیا آمده اند، اما آنها با توجه به نام فامیل شان به صورت نزولی مرتب شده اند. شبیه به همین مورد نیز در مورد گروه زیر صادق است. (که تاریخ تولد آنها 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 مشاهده: 1 حجم: 20.1 کیلو بایت

    بر اساس مجموعه نتایج بالا شما میتوانید حدس بزنید که کوئری زیر چه نتیجه ای را خواهد برگرداند.
    کد 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 a script with a series of SQL commands-4-1.png مشاهده: 1 حجم: 31.7 کیلو بایت

    خط هایی که با دو دش "--" آغاز شده اند لحاظ نمیشوند و کوئری های مجزا یکی بعد از دیگری اجرا میشوند. همچنین ما میتوانیم این اسکریپت را از طریق خط فرمان لینوکس نیز اجرا کنیم.
    کد 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-to-Run-Queries-5.png مشاهده: 1 حجم: 37.1 کیلو بایت


    خلاصه

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

    در صورتی که هرگونه پیشنهاد یا سوالی درباره این پست دارید میتوانید در همینجا مطرح کنید. منتظر شنیدن نظرات ارزشمند شما هستیم.
    آخرین ویرایش توسط Habili; در تاریخ/ساعت 06-13-2018, 11:05 PM.

درباره انجمن منطقه لینوکسی ها

انجمن منطقه لینوکسی ها با هدف ارتقاء سطح علمی کاربران در سطح جهانی و همچنین کمک به بالا بردن سطح علمی عمومی در زمینه های تخصصی فوق پایه گذاری شده است. انجمن منطقه لینوکسی ها از طریق کارشناسان و متخصصان پاسخگوی سوالات گوناگون کاربران مبتدی یا پیشرفته میباشد تا حد امکان تلاش شده که محیطی متنوع و کاربر پسند و به دور از هرگونه حاشیه جهت فعالیت کاربران در این انجمن ایجاد شود. لذا ما به صورت مستمر برای پیشرفت کمی و کیفی محتوی و اطلاعات انجمنمان میکوشیم که این برای ما ارزشمند و حائز اهمیت است. کلیه حقوق،اطلاعات و مقالات در این انجمن متعلق به سایت منطقه لینوکسی ها میباشد، و هرگونه نسخه برداری بدون ذکر منبع مورد پیگرد قانونی خواهد شد.

شبکه های اجتماعی

ایمیل مدیریت

Habili@linux-zone.org

در حال انجام ...
X