اگر شما از پایگاه داده Mysql استفاده می کنید، ضروری است که با دستورات mysql آشنا باشید. در این آموزش ما در مورد دستور select در MySQL/MariaDB توضیح خواهیم داد.
1. نمونه دستور اولیه select
ابتدا برای کانکت شدن به خط فرمان mysql، از سیستم خود دستورات زیر را انجام دهید:
سپس تمام دیتابیس های موجود را مشاهده کنید:
دیتابیسی که شما می خواهید برروی آن کارکنید را انتخاب کنید. در این مثال من دیتابیس thegeekstuff را انتخاب کرده ام، که جدول employee در آن وجود دارد، که به عنوان مثالی برای تمام دستورات select در این آموزش استفاده خواهم کرد:
موراد استفاده اولیه دستور select برای نمایش ردیف ها از یک جدول است. دستور زیر تمام ردیف های جدول employee را نمایش خواهد داد.
و یا می توانید ستون های خاصی را انتخاب کنید:
2. انتخاب از جداول dual – جدول مجازی
Dual یک جدول مجازی است. این واقعا وجود ندارد. اما شما می توانید این جدول را برای انجام تست هایی استفاده کنید. برای مثال شما می توانید یک جدول dual را برای اجرای عملیات حساب و ریاضی استفاده کنید.
شما می توانید جدول dual را برای نمایش تاریخ و زمان نیز استفاده کنید. Now() در mysql شبیه به تابع sysdate() در اوراکل است.
هنگامیکه شما جدول خاصی ندارید mysql در نظر می گیرد که شما از dual می خواهید استفاده کنید. مثال زیر همانند مثال بالاست فقط از گزینه ی from dual استفاده نکرده است.
3. شرط اولیه WHERE برای محدود کردن رکوردها
بجای نمایش تمام رکورد ها از یک جدول شما می توانید آنرا با شرط where محدود سازید:
همانند علامت بزرگتر< در بالا شما از علامت کوچکتر > و یا مساوی = و یا نامساوی != نیز استفاده کنید.
4. عبارات ریاضی در شرط where
مثال قبل رکورد های محدودی را بر اساس شرط هایی نشان می دهد. این مثال توضیح می ده که چگونه با استفاده از مقدارهای رشته ای محدود کنیم.
مثال زیر تمام کارمندانی را که متعلق به دپارتمان technology هستند را نشان می دهد.
لطفا توجه کنید که این رشته ها حساس به حروف بزرگ و کوچک نیست. بنابراین دستور زیر دقیقا دستور بالاست.
شما همچنین می توانید از != برای نمایش تمام کارمندانی که متعلق به دپارتمان technology نیستند استفاده کنید:
شما می توانید از علامت % در لغت مورد نظر استفاده کنید. دستور زیر تمام کارمندانی را که نام آنها با JOHN شرع می شود را نمایش می دهد:
دستور زیر تمام کارمندانی را که انتهای نام آنها Smith است را نمایش می دهد:
شما می توانید همچنین از % در دو طرف انتها و ابتدای رشته نیز استفاده کنید.
5. ترکیب شرط WHERE با استفاده از OR یا AND
شما می توانید همچنین از OR ، AND و یا NOT در شرط where برای ترکیب چند شرط استفاده کنید.مثال زیر تمام کارمندانی را در دپارتمان technology هستند و حقوق آنها salary>=6000 است زا نمایش می دهد.
در زیر نیز همانند بالا دستور با شرط OR استفاده شده است.
6. ترکیب مقدارهای ستون با استفاده از Concat در select
شما می توانید از تابعCONCAT در دستور select برای ترکیب مقدار ستون از چندین ستون استفاده کنید و برای مثال name و department را با هم ترکیب کنید.
7. شمارش تعداد رکوردها
با استفاده از count(*) در دستور select تعداد رکوردها را شمارش کنید.
8. Group by در دستور select
دستور Group by رکورد ها را براساس شرط خاصی گروه بندی خواهد کرد. مثال زیر تعداد تمام کارمندان در هر دپارتمان را نمایش می دهد.
توجه کنید که زمانی که شما از group by استفاده می کنید، شما هم چنین می توانید از Having برای محدود کردن رکوردها استفاده کنید. در مثال زیر تنها دپارتمان هایی را نشان خواهد داد که کارمندان آن بیشتر از 1 هستند.
9. تعریف Alias با استفاده از AS
بجای نمایش نام ستون استفاده شده در جدول شما می توانید از نامی که می خواهید برای نمایش ستون استفاده کنید.
در مثال زیر نام واقعی ستون ،IDاست که به جای آن ما از EMPID استفاده کرده ایم.
لطفا توجه کنید که کلمه ی کلیدی AS اختیاری است. مثال زیر همان مثال بالا را بدون استفاده از کلمه کلیدی as نشان داده است.
10. Left Join در دستور Select
در مثال زیر دستور select دو جدول employee و depRTMENT را ترکیب می کند. برای ترکیب این دو از نام های ستون های این دو جدول استفاده می کند. ستون Location در خروجی از جدول department نشان داده شده است.
شما می توانید از آلیاس نام در دستور goin استفاده کنید. در این مثال من از E برای جدول employee و از D برای جدول department استفاده می کنم.
11. Performance Analysis با استفاده از EXPLAIN
هنگامیکه درخواست select کند است، یا کاری انجام می دهد که شما نمی فهمید، از دستور EXPLAIN استفاده کنید، که جزئیات بیشتری را برای شما توضیح خواهد داد. این ممکن است برای تست کارایی دستور select شما مفید باشد.
12. استفاده از select برای INDEX
زمانی که یک select query اجرا می کنید، و دو جدول را joinکرده اید، mysql تصمیم خواهد گرفت که چگونه هر ایندکس در دسترسی روی جدول تاثیر می گذارد. در زیر چند راه برای سروکار داشتن با اینکس ها در دستورselect مثال زدها یم.
USE_INDEX(list_of_indexes) - این برای یکی از ایندکس های بخصوص رکورد ها از جدول استفاده خواهد شد.
IGNORE INDEX(list_of_indexes) - این ایندکس های خاصی را برای کوئری گرفتن رکوردها از جدول استفاده خواهد کرد.
FORCE INDEX(index_name) - این mysql را مجبور خواهد کرد برای گرفتن ایندکس ها حتی زمانی که mysql فکر می کند که راه بهتر و سریعتری برای کوئری گرفتن وجود دارد.
قبل از اینکه شما تصمیم بگیرید از هر کدام از دستورهای بالا استفاده کنید، شما واقعا تاثیر این دستورات را خواهید فهمید. همچنین اگر شما از این دستورات استفاده نکنید دستور شما بسیار کند اجرا خواهد شد.
مثال های زیر mysql را مجبور به استفاده از employee_emp_nm_idx برای این کوئری میکند.
برای نمایش تمام ایندکس های دردسترس در جدول خاصی از دستور show index استفاده کنید.
13. مرتب کردن رکوردها با استفاده از دستور ORDER BY
نکته : desc به صورت نزولی مرتب می کند. اگر چیزی استفاده نکنید به صورت صعودی مرتب خواهد شد. رکوردهای زیر به ترتیب حروف الفبا بر اساس ستون dept مرتب شده اند.
لطفا توجه کنید که به صورت پیش فرض به صورت صعودی مرتب خواهد شد. اگر شما می خواهید به صورت نزولی مرتب کنید باید از desc استفاده کنید.
شما همچنین می توانید چندین ستون را مرتب کنید.
14. محدود کردن تعداد رکوردها
بجای نمایش تمام رکوردهای مورد جستجو می توانید تنها چند رکورد ابتدا یا انتها و یا بازه ی مشخصی را نمایش دهید.
فرمت limit:
در مثال زیر از رکورد شماره 0 تا رکورد شماره 3 نمایش داده خواهد شد.
در زیر از رکورد شماره 1 الی رکورد شماره 3 نمایش داده شده است.
شما همچنین می توانید start_record را حذف کنید. به عبارت دیگر شروع را از رکورد 0 در نظر می گیرد. در مثال زیر ما تنها یک مقدار بخصوصی را در نظر داریم. بنابراین ابتدای رکورد با 0 شرع خواهد شد.
15. محدود کردن تعداد رکوردها با OFFSET
فرمت limit offset:
شما می توانید از کلمه ی کلیدی OFFSET استفاده کنید، جایی که شما رکورد ابتدایی را بعد از کلمه ی offset مشخص کرده اید. در زیر در کل 3 رکورد نمایش داده خواهد شد. از آنجا که offset 1 می باشد، از دومین رکورد شروع خواهد شد.
16. گرفتن مقدارهای unique از یک ستون
برای نمایش مقدارهایی unique از یک ستون از از عبارت DISTINCT استفاده کنید. مثال زیر تمام مقدار های unique از ستون dept از جدول employee را نمایش می دهد.
17. جمع تمام مقدارهای یک ستون
برای اضافه کردن مقدارها از یک ستون از تابع SUM() استفاده کنید. مثال زیر جمع حقوق را برای تمام کارمندانی که در دپارتمان technology هستند را نمایش خواهد داد.
18. میانگین تمام مقدارهای یک ستون
برای میانگین مقدارهایی از یک ستون از تابع AVG() استفاده کنید. مثال زیر میانگین حقوق هرکدام از دپارتمانها را نمایش خواهد داد. این مثال Group by را با تابع AVG() ترکیب می کند.
19. دستور SELECT در SELECT
مثال زیر مثال خیلی درستی نیست. هیچ دلیلی برای استفاده از این راه وجود ندارد. اما این به شما نشان می دهد که شما می توانید از دستور select استفاده کنید. در این مثال AS ACTION یک نام آلیاس را را به subquery اختصاص خواهد داد.
20. ذخیره ی خروجی دستور select در یک فایل
با استفاده از SELECT INTO شما می توانید خروجی دستور select را به یک فایل بفرستید. به جای نمایش خروجی برروی صفحه، مثال زیر خروجی دستور را به فایل /tmp/employee.txt ارسال خواهد کرد.
شما می توانید همچنین خروجی را با استفاده از کاما توسط FIELDS TERMINATED BY همانند مثال زیر جدا کنید.
21. اجرای یک پروسیجر برروی مجموعه داده
شما می توانید یک پروسیجر mysql را که داده ها را از خروجی دستور select می گیرد و پروسس می کند، استفاده کنید. مثال زیر پروسیجر salary_report() را روی خروجی دستور select اجرا خواهد کرد.
22. نمایش یک رکورد تصادفی از یک جدول
با استفاده از دستور rand شما می توانید یک رکورد تصادفی از جدول را نمایش دهید.
اگر همان دستور را بار دیگر اجرا کنید به شما رکورد دیگری خواهد داد.
شما می توانید زمان کنونی را با استفاده از تابع now() به آن پاس دهید .
23. دستور select High Priority
زمانی که شما از کلمه ی کلیدی high_priority استفاده می کنید، یک بیان اولویت بالا برای دستورselect برای هر آپدیتی در جدول می باشد. دقت کنید که زمانی که اسن دستور را استفاده می کنید، دستور ممکن است به دلیل آپدیت به کندی اجرا شود. این را تنها در جایی استفاده کنید که به رکوردی سریعا نیاز دارید.
24. Consistent read در دستور select
اگر شما یک consistent read می خواهید که هنگامیکه شما ردیف هایی را از یک جدول انتخاب کرده اید، نمی خواهید هیچ پروسس دیگری مقدارهای جدول را تغییر دهید، شما نیاز دارید که یک share lock mode را برروی جدولی که درحال خواندن آن هستنید اجرا کنید.
دستور زیر اجازه session های دیگر mysql را برای تغییر رکوردها را نمی دهد.
لطفا توجه کنید که شما می توانید همچنین FOR UPDATE را مثل زیر، برای بلاک کردن sessionهایی از “SELECT … LOCK in SHARE MODE” را انجام دهد تا اینکه این تراکنش به اتمام برسد.
25. ذخیره ی MySQL query در یک text و یا فایل CSV
Mysql مکانیزمی را برای نوشتن خروجی دستور در یک فایل برروی سرور فراهم می کند. با استفاده از گزینه ی INTO OUTFILE، امکان ایجاد یک فایل csv که با کاما جدا شده است را فراهم می کند. که به راحتی با استفاده از برنامه ی spreadsheet همانند openoffice و یا excel یا هر برنامه دیگر قابل خواندن است.
دستور بالا 3 ستون را برمی گرداند که می توانیم آنرا در فایل /tmp/orders.txt ذخیره کنیم:
این دستور فایلی با جداکننده ی tab در هر خط ایجاد می کند. برای تغییر آن می توان به صورت زیر دستور را استفاده کرد.
در این مثال هر فیلد با دابل کوت " محصور شده است و فیلدها توسط کاما جدا شده اند و هر ردیف در یک خط توسط \n از خط دیگر جدا شده است. نمونه ی خروجی شبیه زیر می باشد.
همچنین پیشنهاد میشود مقالات زیر را مطالعه کنید.
آموزش کامل نصب آخرین ورژن MariaDB 10 در لینوکس CentOS/RHEL - Debian/Ubuntu
آموزش نصب MySQL 5.7.9 در RedHat/CentOS 7-6-5 و Fedora 21-23
آموزش دستورات MySQL (بخش اول)
دستورات بک آپ گیری و ریستور دیتابیس MySQL در لینوکس
نحوه تغییر پسورد Root دیتابیس MySQL یا MariaDB در Linux
نحوه ریکاوری/ریست پسورد Root در MySQL یا MariaDB در Linux
نحوه اجرای مستقیم دستورات MySQL در ترمینال لینوکس
فعال کردن قابلیت Auto completion و Syntax highlighting در MySQL/MariaDB
تغییر مسیر Defualt ذخیره دیتای MySQL در لینوکس
نکات حیاتی کلاستر کردن دیتابیس MySQL
15 نکته مفید برای بهینه سازی و بهبود عملکرد دیتابیس MySQL/MariaDB در Linux
آموزش MySQL (Master-Slave) Replication در RHEL,CentOS,Fedora
نحوه ایجاد، حذف و مدیریت سطوح دسترسی کاربران در MySQL/MariaDB
با آرزوی موفقیت ...
1. نمونه دستور اولیه select
ابتدا برای کانکت شدن به خط فرمان mysql، از سیستم خود دستورات زیر را انجام دهید:
کد:
# mysql -u root -p Password:
سپس تمام دیتابیس های موجود را مشاهده کنید:
کد:
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | thegeekstuff | | crm | | bugzilla | +--------------------+ 8 rows in set (0.00 sec)
کد:
mysql> USE thegeekstuff; Database changed mysql> DESC employee;
موراد استفاده اولیه دستور select برای نمایش ردیف ها از یک جدول است. دستور زیر تمام ردیف های جدول employee را نمایش خواهد داد.
کد:
mysql> SELECT * FROM employee; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Thomas | Sales | 5000 | | 200 | Jason | Technology | 5500 | | 300 | Sanjay | Technology | 7000 | | 400 | Nisha | Marketing | 9500 | | 500 | Randy | Technology | 6000 | | 501 | Ritu | Accounting | NULL | +-----+--------+------------+--------+ 6 rows in set (0.01 sec)
و یا می توانید ستون های خاصی را انتخاب کنید:
کد:
mysql> SELECT name, salary FROM employee; +--------+--------+ | name | salary | +--------+--------+ | Thomas | 5000 | | Jason | 5500 | | Sanjay | 7000 | | Nisha | 9500 | | Randy | 6000 | | Ritu | NULL | +--------+--------+ 6 rows in set (0.00 sec)
2. انتخاب از جداول dual – جدول مجازی
Dual یک جدول مجازی است. این واقعا وجود ندارد. اما شما می توانید این جدول را برای انجام تست هایی استفاده کنید. برای مثال شما می توانید یک جدول dual را برای اجرای عملیات حساب و ریاضی استفاده کنید.
کد:
mysql> SELECT 2+3 FROM DUAL; +-----+ | 2+3 | +-----+ | 5 | +-----+ 1 row in set (0.00 sec)
کد:
mysql> SELECT NOW() FROM DUAL; +---------------------+ | now() | +---------------------+ | 2013-09-14 09:15:35 | +---------------------+ 1 row in set (0.00 sec)
کد:
mysql> SELECT 2+3; +-----+ | 2+3 | +-----+ | 5 | +-----+ 1 row in set (0.00 sec) mysql> SELECT NOW(); +---------------------+ | now() | +---------------------+ | 2013-09-14 09:16:45 | +---------------------+ 1 row in set (0.00 sec)
3. شرط اولیه WHERE برای محدود کردن رکوردها
بجای نمایش تمام رکورد ها از یک جدول شما می توانید آنرا با شرط where محدود سازید:
کد:
mysql> SELECT * FROM employee WHERE salary > 6000; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 300 | Sanjay | Technology | 7000 | | 400 | Nisha | Marketing | 9500 | +-----+--------+------------+--------+ 2 rows in set (0.00 sec)
همانند علامت بزرگتر< در بالا شما از علامت کوچکتر > و یا مساوی = و یا نامساوی != نیز استفاده کنید.
کد:
mysql> SELECT * FROM employee WHERE salary < 6000; mysql> SELECT * FROM employee WHERE salary SELECT * FROM employee WHERE salary >= 6000; mysql> SELECT * FROM employee WHERE salary = 6000; mysql> SELECT * FROM employee WHERE salary != 6000;
4. عبارات ریاضی در شرط where
مثال قبل رکورد های محدودی را بر اساس شرط هایی نشان می دهد. این مثال توضیح می ده که چگونه با استفاده از مقدارهای رشته ای محدود کنیم.
مثال زیر تمام کارمندانی را که متعلق به دپارتمان technology هستند را نشان می دهد.
کد:
mysql> SELECT * FROM employee WHERE dept = 'Technology'; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 200 | Jason | Technology | 5500 | | 300 | Sanjay | Technology | 7000 | | 500 | Randy | Technology | 6000 | +-----+--------+------------+--------+ 3 rows in set (0.00 sec)
کد:
mysql> SELECT * FROM employee WHERE dept = 'TECHNOLOGY';
کد:
mysql> SELECT * FROM employee WHERE dept != 'TECHNOLOGY';
شما می توانید از علامت % در لغت مورد نظر استفاده کنید. دستور زیر تمام کارمندانی را که نام آنها با JOHN شرع می شود را نمایش می دهد:
کد:
mysql> SELECT * FROM employee WHERE name LIKE 'JOHN%';
دستور زیر تمام کارمندانی را که انتهای نام آنها Smith است را نمایش می دهد:
کد:
mysql> SELECT * FROM employee WHERE name LIKE '%SMITH';
شما می توانید همچنین از % در دو طرف انتها و ابتدای رشته نیز استفاده کنید.
کد:
mysql> SELECT * FROM employee WHERE name LIKE '%JOHN%';
5. ترکیب شرط WHERE با استفاده از OR یا AND
شما می توانید همچنین از OR ، AND و یا NOT در شرط where برای ترکیب چند شرط استفاده کنید.مثال زیر تمام کارمندانی را در دپارتمان technology هستند و حقوق آنها salary>=6000 است زا نمایش می دهد.
کد:
mysql> SELECT * FROM employee WHERE dept = 'TECHNOLOGY' AND salary >= 6000; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 300 | Sanjay | Technology | 7000 | | 500 | Randy | Technology | 6000 | +-----+--------+------------+--------+ 2 rows in set (0.00 sec)
کد:
mysql> SELECT * FROM employee WHERE dept = 'TECHNOLOGY' OR salary >= 6000; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 200 | Jason | Technology | 5500 | | 300 | Sanjay | Technology | 7000 | | 400 | Nisha | Marketing | 9500 | | 500 | Randy | Technology | 6000 | +-----+--------+------------+--------+ 4 rows in set (0.00 sec)
6. ترکیب مقدارهای ستون با استفاده از Concat در select
شما می توانید از تابعCONCAT در دستور select برای ترکیب مقدار ستون از چندین ستون استفاده کنید و برای مثال name و department را با هم ترکیب کنید.
کد:
mysql> SELECT ID, CONCAT(NAME, ' FROM ', DEPT) AS NAME, SALARY FROM employee; +-----+------------------------+--------+ | id | name | salary | +-----+------------------------+--------+ | 100 | Thomas from Sales | 5000 | | 200 | Jason from Technology | 5500 | | 300 | Sanjay from Technology | 7000 | | 400 | Nisha from Marketing | 9500 | | 500 | Randy from Technology | 6000 | | 501 | Ritu from Accounting | NULL | +-----+------------------------+--------+ 6 rows in set (0.00 sec)
7. شمارش تعداد رکوردها
با استفاده از count(*) در دستور select تعداد رکوردها را شمارش کنید.
کد:
mysql> SELECT COUNT(*) FROM employee; +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec)
8. Group by در دستور select
دستور Group by رکورد ها را براساس شرط خاصی گروه بندی خواهد کرد. مثال زیر تعداد تمام کارمندان در هر دپارتمان را نمایش می دهد.
کد:
mysql> SELECT DEPT, COUNT(*) FROM employee GROUP BY DEPT; +------------+----------+ | dept | count(*) | +------------+----------+ | Accounting | 1 | | Marketing | 1 | | Sales | 1 | | Technology | 3 | +------------+----------+ 4 rows in set (0.00 sec)
کد:
mysql> SELECT COUNT(*) AS CNT, DEPT FROM employee GROUP BY DEPT HAVING CNT > 1; +-----+------------+ | CNT | dept | +-----+------------+ | 3 | Technology | +-----+------------+ 1 row in set (0.00 sec)
9. تعریف Alias با استفاده از AS
بجای نمایش نام ستون استفاده شده در جدول شما می توانید از نامی که می خواهید برای نمایش ستون استفاده کنید.
در مثال زیر نام واقعی ستون ،IDاست که به جای آن ما از EMPID استفاده کرده ایم.
کد:
mysql> SELECT ID AS EMPID, NAME AS EMPNAME, DEPT AS DEPARTMENT FROM employee; +-------+---------+------------+ | EMPID | EMPNAME | DEPARTMENT | +-------+---------+------------+ | 100 | Thomas | Sales | | 200 | Jason | Technology | | 300 | Sanjay | Technology | | 400 | Nisha | Marketing | | 500 | Randy | Technology | | 501 | Ritu | Accounting | +-------+---------+------------+ 6 rows in set (0.00 sec)
لطفا توجه کنید که کلمه ی کلیدی AS اختیاری است. مثال زیر همان مثال بالا را بدون استفاده از کلمه کلیدی as نشان داده است.
کد:
mysql> SELECT id empid, name empname, dept department FROM employee;
10. Left Join در دستور Select
در مثال زیر دستور select دو جدول employee و depRTMENT را ترکیب می کند. برای ترکیب این دو از نام های ستون های این دو جدول استفاده می کند. ستون Location در خروجی از جدول department نشان داده شده است.
کد:
mysql> SELECT employee.*, department.location FROM employee LEFT JOIN department ON ( employee.dept = department.dept ); +-----+--------+------------+--------+----------+ | id | name | dept | salary | Location | +-----+--------+------------+--------+----------+ | 100 | Thomas | Sales | 5000 | USA | | 200 | Jason | Technology | 5500 | USA | | 300 | Sanjay | Technology | 7000 | India | | 400 | Nisha | Marketing | 9500 | India | | 500 | Randy | Technology | 6000 | UK | | 501 | Ritu | Accounting | NULL | USA | +-----+--------+------------+--------+----------+
شما می توانید از آلیاس نام در دستور goin استفاده کنید. در این مثال من از E برای جدول employee و از D برای جدول department استفاده می کنم.
کد:
mysql> SELECT E.*, d.location FROM employee AS E LEFT JOIN department AS D ON ( e.dept = d.dept );
11. Performance Analysis با استفاده از EXPLAIN
هنگامیکه درخواست select کند است، یا کاری انجام می دهد که شما نمی فهمید، از دستور EXPLAIN استفاده کنید، که جزئیات بیشتری را برای شما توضیح خواهد داد. این ممکن است برای تست کارایی دستور select شما مفید باشد.
کد:
mysql> EXPLAIN SELECT E.*, D.LOCATION FROM employee AS E LEFT JOIN DEPARTMENT AS D ON ( E.DEPT = D.DEPT ); +----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------+ | 1 | SIMPLE | PS | ALL | NULL | NULL | NULL | NULL | 6 | | | 1 | SIMPLE | P | eq_ref | PRIMARY | PRIMARY | 3 | acme.E.dept | 1 | | +----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------+ 2 rows in set (0.00 sec)
12. استفاده از select برای INDEX
زمانی که یک select query اجرا می کنید، و دو جدول را joinکرده اید، mysql تصمیم خواهد گرفت که چگونه هر ایندکس در دسترسی روی جدول تاثیر می گذارد. در زیر چند راه برای سروکار داشتن با اینکس ها در دستورselect مثال زدها یم.
USE_INDEX(list_of_indexes) - این برای یکی از ایندکس های بخصوص رکورد ها از جدول استفاده خواهد شد.
IGNORE INDEX(list_of_indexes) - این ایندکس های خاصی را برای کوئری گرفتن رکوردها از جدول استفاده خواهد کرد.
FORCE INDEX(index_name) - این mysql را مجبور خواهد کرد برای گرفتن ایندکس ها حتی زمانی که mysql فکر می کند که راه بهتر و سریعتری برای کوئری گرفتن وجود دارد.
قبل از اینکه شما تصمیم بگیرید از هر کدام از دستورهای بالا استفاده کنید، شما واقعا تاثیر این دستورات را خواهید فهمید. همچنین اگر شما از این دستورات استفاده نکنید دستور شما بسیار کند اجرا خواهد شد.
مثال های زیر mysql را مجبور به استفاده از employee_emp_nm_idx برای این کوئری میکند.
کد:
mysql> SELECT * FROM employee FORCE INDEX (EMPLOYEE_EMP_NM_IDX) WHERE NAME LIKE 'JOHN%';
کد:
mysql> SHOW INDEX FROM PROFILES; +----------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | employee | 0 | PRIMARY | 1 | id | A | 156 | NULL | NULL | | BTREE | | | employee | 0 | employee_emp_nm_idx | 1 | name | A | 156 | NULL | NULL | | BTREE | | +----------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
13. مرتب کردن رکوردها با استفاده از دستور ORDER BY
نکته : desc به صورت نزولی مرتب می کند. اگر چیزی استفاده نکنید به صورت صعودی مرتب خواهد شد. رکوردهای زیر به ترتیب حروف الفبا بر اساس ستون dept مرتب شده اند.
کد:
mysql> SELECT * FROM employee ORDER BY DEPT; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 501 | Ritu | Accounting | NULL | | 400 | Nisha | Marketing | 9500 | | 100 | Thomas | Sales | 5000 | | 200 | Jason | Technology | 5500 | | 300 | Sanjay | Technology | 7000 | | 500 | Randy | Technology | 6000 | +-----+--------+------------+--------+ 6 rows in set (0.01 sec)
کد:
mysql> SELECT * FROM employee ORDER BY DEPT DESC; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 200 | Jason | Technology | 5500 | | 300 | Sanjay | Technology | 7000 | | 500 | Randy | Technology | 6000 | | 100 | Thomas | Sales | 5000 | | 400 | Nisha | Marketing | 9500 | | 501 | Ritu | Accounting | NULL | +-----+--------+------------+--------+ 6 rows in set (0.00 sec)
کد:
mysql> SELECT * FROM employee ORDER BY DEPT, SALARY DESC;
14. محدود کردن تعداد رکوردها
بجای نمایش تمام رکوردهای مورد جستجو می توانید تنها چند رکورد ابتدا یا انتها و یا بازه ی مشخصی را نمایش دهید.
فرمت limit:
کد:
LIMIT start_record, number_of_records
در مثال زیر از رکورد شماره 0 تا رکورد شماره 3 نمایش داده خواهد شد.
کد:
mysql> SELECT * FROM employee LIMIT 0,3; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Thomas | Sales | 5000 | | 200 | Jason | Technology | 5500 | | 300 | Sanjay | Technology | 7000 | +-----+--------+------------+--------+ 3 rows in set (0.00 sec)
در زیر از رکورد شماره 1 الی رکورد شماره 3 نمایش داده شده است.
کد:
mysql> SELECT * FROM employee LIMIT 1,3; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 200 | Jason | Technology | 5500 | | 300 | Sanjay | Technology | 7000 | | 400 | Nisha | Marketing | 9500 | +-----+--------+------------+--------+ 3 rows in set (0.00 sec)
کد:
mysql> SELECT * FROM employee LIMIT 3; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Thomas | Sales | 5000 | | 200 | Jason | Technology | 5500 | | 300 | Sanjay | Technology | 7000 | +-----+--------+------------+--------+ 3 rows in set (0.00 sec)
15. محدود کردن تعداد رکوردها با OFFSET
فرمت limit offset:
کد:
LIMIT number_of_records OFFSET start_record
شما می توانید از کلمه ی کلیدی OFFSET استفاده کنید، جایی که شما رکورد ابتدایی را بعد از کلمه ی offset مشخص کرده اید. در زیر در کل 3 رکورد نمایش داده خواهد شد. از آنجا که offset 1 می باشد، از دومین رکورد شروع خواهد شد.
کد:
mysql> SELECT * FROM employee LIMIT 3 OFFSET 1 +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 200 | Jason | Technology | 5500 | | 300 | Sanjay | Technology | 7000 | | 400 | Nisha | Marketing | 9500 | +-----+--------+------------+--------+ 3 rows in set (0.00 sec)
16. گرفتن مقدارهای unique از یک ستون
برای نمایش مقدارهایی unique از یک ستون از از عبارت DISTINCT استفاده کنید. مثال زیر تمام مقدار های unique از ستون dept از جدول employee را نمایش می دهد.
کد:
mysql> SELECT DISTINCT DEPT FROM employee; +------------+ | dept | +------------+ | Sales | | Technology | | Marketing | | Accounting | +------------+
17. جمع تمام مقدارهای یک ستون
برای اضافه کردن مقدارها از یک ستون از تابع SUM() استفاده کنید. مثال زیر جمع حقوق را برای تمام کارمندانی که در دپارتمان technology هستند را نمایش خواهد داد.
کد:
mysql> SELECT SUM(SALARY) FROM employee WHERE DEPT = 'TECHNOLOGY'; +-------------+ | sum(salary) | +-------------+ | 18500 | +-------------+ 1 row in set (0.01 sec)
18. میانگین تمام مقدارهای یک ستون
برای میانگین مقدارهایی از یک ستون از تابع AVG() استفاده کنید. مثال زیر میانگین حقوق هرکدام از دپارتمانها را نمایش خواهد داد. این مثال Group by را با تابع AVG() ترکیب می کند.
کد:
mysql> SELECT DEPT,AVG(SALARY) FROM employee GROUP BY DEPT; +------------+-------------+ | dept | avg(salary) | +------------+-------------+ | Accounting | NULL | | Marketing | 9500.0000 | | Sales | 5000.0000 | | Technology | 6166.6667 | +------------+-------------+ 4 rows in set (0.03 sec)
19. دستور SELECT در SELECT
مثال زیر مثال خیلی درستی نیست. هیچ دلیلی برای استفاده از این راه وجود ندارد. اما این به شما نشان می دهد که شما می توانید از دستور select استفاده کنید. در این مثال AS ACTION یک نام آلیاس را را به subquery اختصاص خواهد داد.
کد:
mysql> SELECT * FROM (SELECT * FROM employee) AS ACTION WHERE ID
20. ذخیره ی خروجی دستور select در یک فایل
با استفاده از SELECT INTO شما می توانید خروجی دستور select را به یک فایل بفرستید. به جای نمایش خروجی برروی صفحه، مثال زیر خروجی دستور را به فایل /tmp/employee.txt ارسال خواهد کرد.
کد:
mysql> SELECT * INTO OUTFILE '/tmp/employee.txt' FROM employee; Query OK, 6 rows affected (0.00 sec) # cat /tmp/employee.txt 100 Thomas Sales 5000 200 Jason Technology 5500 300 Sanjay Technology 7000 400 Nisha Marketing 9500 500 Randy Technology 6000 501 Ritu Accounting \N
شما می توانید همچنین خروجی را با استفاده از کاما توسط FIELDS TERMINATED BY همانند مثال زیر جدا کنید.
کد:
mysql> SELECT * INTO OUTFILE '/tmp/employee1.txt' FIELDS TERMINATED BY ',' FROM employee; Query OK, 6 rows affected (0.00 sec) # cat /tmp/employee1.txt 100,Thomas,Sales,5000 200,Jason,Technology,5500 300,Sanjay,Technology,7000 400,Nisha,Marketing,9500 500,Randy,Technology,6000 501,Ritu,Accounting,\N
21. اجرای یک پروسیجر برروی مجموعه داده
شما می توانید یک پروسیجر mysql را که داده ها را از خروجی دستور select می گیرد و پروسس می کند، استفاده کنید. مثال زیر پروسیجر salary_report() را روی خروجی دستور select اجرا خواهد کرد.
کد:
mysql> SELECT ID, SALARY FROM employee PROCEDURE SALARY_REPORT();
22. نمایش یک رکورد تصادفی از یک جدول
با استفاده از دستور rand شما می توانید یک رکورد تصادفی از جدول را نمایش دهید.
کد:
mysql> SELECT * FROM employee ORDER BY RAND() LIMIT 1; +-----+-------+------------+--------+ | id | name | dept | salary | +-----+-------+------------+--------+ | 200 | Jason | Technology | 5500 | +-----+-------+------------+--------+ 1 row in set (0.00 sec)
اگر همان دستور را بار دیگر اجرا کنید به شما رکورد دیگری خواهد داد.
کد:
mysql> SELECT * FROM employee ORDER BY RAND() LIMIT 1; +-----+--------+-------+--------+ | id | name | dept | salary | +-----+--------+-------+--------+ | 100 | Thomas | Sales | 5000 | +-----+--------+-------+--------+ 1 row in set (0.00 sec)
کد:
mysql> SELECT * FROM employee ORDER BY RAND(NOW()) LIMIT 1; +-----+-------+-----------+--------+ | id | name | dept | salary | +-----+-------+-----------+--------+ | 400 | Nisha | Marketing | 9500 | +-----+-------+-----------+--------+ 1 row in set (0.00 sec)
23. دستور select High Priority
زمانی که شما از کلمه ی کلیدی high_priority استفاده می کنید، یک بیان اولویت بالا برای دستورselect برای هر آپدیتی در جدول می باشد. دقت کنید که زمانی که اسن دستور را استفاده می کنید، دستور ممکن است به دلیل آپدیت به کندی اجرا شود. این را تنها در جایی استفاده کنید که به رکوردی سریعا نیاز دارید.
کد:
mysql> SELECT HIGH_PRIORITY * FROM employee WHERE ID = 100;
24. Consistent read در دستور select
اگر شما یک consistent read می خواهید که هنگامیکه شما ردیف هایی را از یک جدول انتخاب کرده اید، نمی خواهید هیچ پروسس دیگری مقدارهای جدول را تغییر دهید، شما نیاز دارید که یک share lock mode را برروی جدولی که درحال خواندن آن هستنید اجرا کنید.
دستور زیر اجازه session های دیگر mysql را برای تغییر رکوردها را نمی دهد.
کد:
mysql> SELECT * FROM employee WHERE ID = 100 LOCK IN SHARE MODE;
25. ذخیره ی MySQL query در یک text و یا فایل CSV
Mysql مکانیزمی را برای نوشتن خروجی دستور در یک فایل برروی سرور فراهم می کند. با استفاده از گزینه ی INTO OUTFILE، امکان ایجاد یک فایل csv که با کاما جدا شده است را فراهم می کند. که به راحتی با استفاده از برنامه ی spreadsheet همانند openoffice و یا excel یا هر برنامه دیگر قابل خواندن است.
کد:
SELECT order_id,product_name,qty FROM orders
دستور بالا 3 ستون را برمی گرداند که می توانیم آنرا در فایل /tmp/orders.txt ذخیره کنیم:
کد:
SELECT order_id,product_name,qty FROM orders
این دستور فایلی با جداکننده ی tab در هر خط ایجاد می کند. برای تغییر آن می توان به صورت زیر دستور را استفاده کرد.
کد:
SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
کد:
"1","Tech-Recipes sock puppet","14.95" "2","Tech-Recipes chef's hat","18.95" ...
همچنین پیشنهاد میشود مقالات زیر را مطالعه کنید.
آموزش کامل نصب آخرین ورژن MariaDB 10 در لینوکس CentOS/RHEL - Debian/Ubuntu
آموزش نصب MySQL 5.7.9 در RedHat/CentOS 7-6-5 و Fedora 21-23
آموزش دستورات MySQL (بخش اول)
دستورات بک آپ گیری و ریستور دیتابیس MySQL در لینوکس
نحوه تغییر پسورد Root دیتابیس MySQL یا MariaDB در Linux
نحوه ریکاوری/ریست پسورد Root در MySQL یا MariaDB در Linux
نحوه اجرای مستقیم دستورات MySQL در ترمینال لینوکس
فعال کردن قابلیت Auto completion و Syntax highlighting در MySQL/MariaDB
تغییر مسیر Defualt ذخیره دیتای MySQL در لینوکس
نکات حیاتی کلاستر کردن دیتابیس MySQL
15 نکته مفید برای بهینه سازی و بهبود عملکرد دیتابیس MySQL/MariaDB در Linux
آموزش MySQL (Master-Slave) Replication در RHEL,CentOS,Fedora
نحوه ایجاد، حذف و مدیریت سطوح دسترسی کاربران در MySQL/MariaDB
با آرزوی موفقیت ...
کامنت