در این مقاله آموزشی ما قصد داریم در سرور MySQL / MariaDB، نحوه ساخت یک دیتابیس "database" (که با عنوان schema هم شناخته میشود)، جداول "tables" (با انواع داده ها) را نشان خواهیم داد و همچنین نحوه انجام عملیات DML (مخفف Data Manipulation Language) روی داده ها (Data) را شرح خواهیم داد.
فرض میکنیم که شما از قبل پکیج های لازم را روی سیستم تان نصب کرده اید و همچنین دستور mysql_secure_installation را به منظور بهبود امنیت سرور دیتابیس تان اجرا کرده اید. در غیر اینصورت پست های زیر را برای نصب سرور MySQL / MariaDB دنبال کنید.
نصب آخرین ورژن دیتابیس MariaDB در Linux
در این مقاله ما به صورت اجمالی از MariaDB استفاده میکنیم، اما مفاهیم و دستورات مشخص شده اینجا، در MySQL هم اجرا میشوند.
ساخت Database, Table و کاربران مجاز
همانطور که میدانید، یک پایگاه داده را میتوان به صورت ساده به عنوان مجموعه ای سازمان یافته ای از اطلاعات تعریف کرد. به خصوص، MariaDB که یک سیستم مدیریت دیتابیس رابطه ای (RDMS) هست و برای اجرا کردن عملیات روی دیتابیس ها از زبان ساختاری درخواستی (query) استفاده میکند. علاوه بر این به خاطر بسپارید که MariaDB از عبارت های database و schema به جای یکدیگر استفاده میکند.
برای ذخیره کردن اطلاعات به صورت دائمی در یک دیتابیس، ما از جدول هایی استفاده میکنیم که ردیف هایی از اطلاعات را در خود ذخیره میکند. غالبا دو جدول یا بیشتر، به یکدیگر و با یک شیوه خاصی مربوط میشوند. که در حقیقت بخشی از سازمانیست که استفاده از دیتابیس های مرتبط را مشخص میکند.
ایجاد یک Database جدید
برای ایجاد یک دیتابیس جدید به نام BookstorDB به کنسول MariaDB با استفاده از دستور زیر وارد شوید. (از شما خواسته میشود که پسورد یوزر root را وارد نمایید)
به محض اینکه دیتابیس ایجاد شود شما نیاز خواهید داشت که حداقل 2 جدول در آن ایجاد کنید. اما ابتدا اجازه دهید که با مفهوم انواع اطلاعات (Data) آشنا شویم.
معرفی انواع Data در MariaDB
همانطور که قبلا توضیح دادیم جدول ها (Tables) در حقیقت اجزایی از دیتابیس هستند که ما اطلاعات دائمی خود را در آنها نگهداری خواهیم کرد. هر جدول شامل حداقل 2 فیلد یا بیشتر (همچنین با عنوان ستون (columns) نیز شناخته میشود) از یک نوع خاص از دیتایی (نوع اطلاعات) میباشد که این فیلدها میتوانند ذخیره کند.
رایجترین انواع Data در MariaDB به شرح زیر میباشد. (شما میتوانید لیست کامل انواع Data را از داکیومنت های آنلاین رسمی MariaDB مشاهده فرمایید)
Numeric (عددی)
BOOLEAN : مقدار 0 را به عنوان False و هر مقدار دیگری را به عنوان true در نظر میگیرد.
TINYINT : اگر با SIGNED استفاده شود رنج -128 تا 127 و در صورتیکه UNSIGNED استفاده شود رنج 0 تا 255 میباشد.
SMALLINT : اگر با SIGNED استفاده شود رنج -32768 تا 32767 میباشد و رنج UNSIGNED از 0 تا 65535 میباشد.
INT : اگر با UNSIGNED استفاده شود رنج 0 تا 4294967295 میباشد و در غیر اینصورت از رنج -2147483648 تا 2147483647 میباشد.
نکته : در TINYINT ,SMALLINT و INT به صورت پیش فرض SIGNED در نظر گرفته میشود.
DOUBLE(M, D) : که M در حقیقت تعداد کل رقم هاست و D تعداد رقم های بعد از علامت اعشار میباشد. که در حقیقت یک عدد متغییر و با دقت بالاتر را مشخص میکند. اگر UNSIGNED مشخص شده باشد مقدارهای منفی مجاز نیستند.
String (رشته ای)
VARCHAR(M) : این نوع از داده، رشته ای از متغییر طولی را ارائه میدهد که M در حقیقت حداکثر میزان طول ستون به واحد byte میباشد (65,535). در اکثر مواقع تعداد byte ها، مشخصه ای برای تعداد کاراکترهاست مگر برای یکسری کاراکترها که بیشتر از 3 بایت باشند. به طور مثال، حرف اسپانیایی "ñ" که یک کاراکتر به حساب میآید اما در حقیقت 2 بایت میباشد.
TEXT(M) : ستونی را ارائه میکند که حداکثر طول کاراکترها 65,535 میباشد. به هر حال همانطور که در VARCHAR(M) گفتیم حداکثر طول واقعی در صورت ذخیره کاراکترهای چند بایتی، کاهش پیدا خواهد کرد. اگر M مشخص شود، ستون به عنوان کوچکترین نوعی که بتواند این چنین تعدادی از کاراکترها را در خود ذخیره کند ایجاد میشود.
MEDIUMTEXT(M) و LONGTEXT(M) که شبیه به TEXT(M) هستند با این تفاوت که حداکثر طول مجاز کاراکترها به ترتیب 16,777,215 و 4,294,967,295 میباشند.
Date و Time (تاریخ و ساعت)
DATE : مشخص کننده تاریخ به فرمت YYYY-MM-DD
TIME : مشخص کننده ساعت به فرمت HH:MM:SS.sss (ساعت، دقیقه، ثانیه و میلی ثانیه)
DATETIME : ترکیبی از DATE و TIME به فرمت YYYY-MM-DD HH:MM:SS
TIMESTAMP : که برای تعریف لحظه ای که یک ردیف اضافه و یا آپدیت میشود، مورد استفاده قرار میگیرد.
بعد از اینکه انواع دیتا مرور کردیم، اکنون ما برای تشخیص اینکه چه نوعی از دیتا را برای اختصاص دادن به یک ستون در یک جدول نیاز داریم دید بهتری خواهیم داشت.
برای مثال، نام یک شخص به راحتی میتواند با VARCHAR(50) تعریف شود در حالیکه یک پست وبلاگ به نوع TEXT نیاز خواهد داشت. (که با توجه به نیاز خود M را انتخاب میکنیم)
ایجاد Table ها با Key های Primery و Foreign
قبل از اینکه به سراغ ایجاد جدول برویم، دو مفهوم اساسی و پایه درباره دیتابیس ها که مرتبط به هم میباشد، نیاز است آنها را مرور کنیم. (Key های Primery و Foreign)
یک Primery Key در حقیقت شامل مقداریست که به صورت Unique (منحصر به فرد) هر ردیف را در جدول متمایز میکند. از طرف دیگر یک Foreign Key برای ایجاد یک لینک بین دیتا در دو جدول مورد استفاده قرار میگیرد، و همچنین میتوان برای کنترل دیتایی که میتواند در جدولی که Foreign Key در آن قرار گرفته است ذخیره شود، استفاده کرد. هر دو Key های Primery و Forign عموما با نوع INT مشخص میشوند.
برای شفاف سازی، اجازه دهید از دیتابیس BookstorDB استفاده کنیم و دو جدول با نام های AuthorsTBL و BooksTBL همانند زیر ایجاد کنید. مشخصه NOT NULL برای این منظور استفاده میشود که مشخص کنیم فیلد مورد نظر حتما نیاز به یک مقدار دارد و نباید خالی باشد.
همچنین، AUTO_INCREMENT برای این منظور استفاده میشود که هر رکورد جدیدی که وارد جدول میشود باید مقدار آن یکی از قبلی بیشتر باشد و در حقیقت هر رکورد را یک مقدار از ستون Primery Key با نوع INT افزایش میدهد. در حقیقت فیلدی است که به صورت اتوماتیک در هر رکورد جدید مقدار آن یکی نسبت به فیلد قبلی بیشتر میباشد. (به صورت اتوماتیک افزایش میابد)
اکنون جلوتر میرویم و شروع به وارد کردن رکورد در جدول های AuthorsTBL و BooksTBL میکنیم.
انتخاب (Select)، وارد کردن (Inserting)، آپدیت (Update) و حذف ردیف ها (Row)
ما ابتدا به جدول AuthorsTBL دیتا وارد میکنیم. به این دلیل که قبل از وارد کردن رکوردی به جدول BooksTBL ما نیاز داریم که برای AuthorID مقدار تعریف کنیم.
کوئری زیر را از محیط کنسول MariaDB خود اجرا کنید.
بعد از آن، تمام رکوردهای جدول AuthorTBL را Select میکنیم. به خاطر داشته باشید که به ازای هر رکورد، نیاز به AuthorID خواهیم داشت تا بتوانیم کوئری insert برای جدول BooksTBL را ایجاد کنیم.
اگر شما بخواهید که یک رکورد را در یک زمان مشخصی پیدا کنید، شما میتوانید از عبارت WHERE برای مشخص کردن یک شرط به ازای یک ردیف استفاده کنید. به طور مثال :
همچنین شما میتوانید تمام رکوردها را به صورت همزمان select کنید.
اکنون با استفاده از AuthorID مربوطه برای match شدن با نویسنده هر کتاب، یک کوئری INSERT برای جدول BooksTBL ایجاد میکنیم. مقدار 1 در BookIsAvailable نشان دهنده این است که کتاب در انبار موجود است و 0 برعکس آن میباشد.
در این لحظه برای دیدن رکوردهای موجود در جدول BooksTBL یک SELECT خواهیم گرفت. سپس میخواهیم قیمت کتاب “The Alchemist” نوشته شده توسط Parham Nemati را UPDATE کنیم و دوباره آن رکورد خاص را SELECT کنیم.
توجه کنید که اکنون چگونه فیلد BookLastUpdated یک مقدار متفاوت را نمایش میدهد. همانطور که قبلا توضیح دادیم یک فیلد TIMESTAMP هنگامی که رکوردی وارد جدول شود یا به هنگام آخرین تغییر (Update) این فیلد مقداری را نمایش خواهد داد.
کوئری insert و update جدول در دیتابیس MySQL
به هنگام آپدیت کردن ردیف ها، ایده خوبیست که اول برای مشاهده رکوردهایی که قرار است آپدیت شوند یک SELECT بگیریم. زمانیکه ما قصد داریم یک ردیف مجزا را update کنیم، معمولا بعد از عبارت WHERE یک فیلد primary key استفاده میشود
اگر چه ما در این مقاله اقدام به حذف یک رکورد نمیکنیم اما شما در صورتی که دیگر نیاز به یک رکورد نداشتید میتوانید آنرا حذف کنید. برای مثال فرض کنید ما میخواهیم رکورد مربوط به کتاب "The Alchemist" را از BooksTBL حذف کنیم.
برای هذف بالا ما از عبارت DELETE به شکل زیر استفاده خواهیم کرد.
به هنگام UPDATE کردن ایده خوبیست که ابتدا یک SELECT به منظور مشاهده record یا record هایی که ممکن است DELETE به صورت بلقوه روی آنها اثر گذاشته باشد، داشته باشیم.
همچنین فراموش نکنید که عبارت WHERE را برای اعمال یک شرط (BookID=6) برای select کردن record خاص که قرار هست حذف شود اضافه کنید. در غیر اینصورت ریسک حذف کردن تمام ردیف ها در جدول را خواهید داشت.
اگر شما بخواهید که دو فیلد یا بیشتر را به همدیگر ارتباط دهید شما میتوانید از عبارت CONCAT استفاده کنید. برای مثال فرض کنید که ما میخواهیم نتیجه ای را داشته باشیم که شامل یک فیلد از نام کتاب و نویسنده آن به فرمت "The Alchemist (Parham Nemati)" و ردیف دیگر به همراه قیمت باشد.
برای انجام دادن هدف بالا ما نیاز به یک JOIN بین جدول های AuthorsTBL و BooksTBL روی فیلد مشترک هر دو جدول (AuthorID) خواهیم داشت.
همانطور که میتوانید مشاهده کنید CONCAT برای ما امکان متصل کردن چندین عبارت رشته ای که توسط کاما از هم مجزا شده اند را مهیا میکنند. همچنین توجه داشته باشید که ما از Description به منظور ارائه نتیجه تعریف این ارتباطات استفاده کردیم.
خروجی query بالا را در تصویر زیر مشاهده میکنید.
ایجاد کاربر برای دسترسی به دیتابیس BookstoreDB
استفاده از کاربر root به منظور اجرا کردن تمام عملیات DML در دیتابیس یک ایده بسیار بد است. برای جلوگیری کردن از این مورد ما میتوانیم یک حساب کاربری جدید در MariaDB ایجاد کنیم (که نام این کاربر را در اینجا bookstoreuser تعریف میکنیم) و تمام سطوح دسترسی لازم برای دیتابیس BookstorDB را به این user اختصاص میدهیم.
داشتن یک یوزر مجزا و اختصاصی برای هر دیتابیس از صدمه دیدن کل دیتابیس جلوگیری میکند و در نهایت تنها باعث در معرض خطر قرار گرفتن یک اکانت مجزا میشود.
ترفند های دیگر MySQL
برای پاک کردن خط فرمان MariaDB دستور زیر را تایپ و Enter را فشار دهید.
برای متوجه شدن از تنظیمات یک جدول خاص دستور زیر را استفاده میکنیم.
برای مثال
ستون های موجود در جدول دیتابیس را لیست میکند.
با مشاهده سریع فیلد BookIsAvailable متوجه خواهید شد که این فیلد مقدار NULL را نیز قبول میکند. در صورتیکه ما نخواهیم اجازه دهیم که این فیلد NULL باشد، باید همانند زیر تیبل را ALTER کنیم.
(میتوانید مجددا ستون را مشاهده کنید، عبارت YES که در تصویر بالا هایلایت شده اکنون باید NO باشد.)
در نهایت باید برای مشاهده همه دیتابیس ها باید روی سرور در کنسول MySQL دستورات زیر را تایپ کنید.
تمام دیتابیس های MySQL را به صورت زیر لیست میکند.
تصویر زیر نتیجه دستور بالا را بعد از دسترسی به خط فرمان MariaDB با یوزر Bookstoruser نمایش میدهد. (دقت کنید که این اکانت نمیتواند هیچ دیتابیس دیگری را غیر از BookstoreDB و information_schema (این دیتابیس برای همه کاربران در دسترس است) را مشاهده کند.)
خلاصه
در این مقاله ما درباره نحوه راه اندازی عملیات DML و چگونگی ایجاد یک دیتابیس، جدول و یوزرهای اختصاصی روی یک دیتابیس MariaDB توضیحاتی دادیم، علاوه بر این ترفندهای کمی را که ممکن است کارهای یک ادمین system/database را آسانتر کند با شما به اشتراک گذاشتیم.
در صورتی که درباره موضوع بالا هر گونه سوالی داشتید میتوانید در همین پست مطرح کنید.
فرض میکنیم که شما از قبل پکیج های لازم را روی سیستم تان نصب کرده اید و همچنین دستور mysql_secure_installation را به منظور بهبود امنیت سرور دیتابیس تان اجرا کرده اید. در غیر اینصورت پست های زیر را برای نصب سرور MySQL / MariaDB دنبال کنید.
نصب آخرین ورژن دیتابیس MariaDB در Linux
در این مقاله ما به صورت اجمالی از MariaDB استفاده میکنیم، اما مفاهیم و دستورات مشخص شده اینجا، در MySQL هم اجرا میشوند.
ساخت Database, Table و کاربران مجاز
همانطور که میدانید، یک پایگاه داده را میتوان به صورت ساده به عنوان مجموعه ای سازمان یافته ای از اطلاعات تعریف کرد. به خصوص، MariaDB که یک سیستم مدیریت دیتابیس رابطه ای (RDMS) هست و برای اجرا کردن عملیات روی دیتابیس ها از زبان ساختاری درخواستی (query) استفاده میکند. علاوه بر این به خاطر بسپارید که MariaDB از عبارت های database و schema به جای یکدیگر استفاده میکند.
برای ذخیره کردن اطلاعات به صورت دائمی در یک دیتابیس، ما از جدول هایی استفاده میکنیم که ردیف هایی از اطلاعات را در خود ذخیره میکند. غالبا دو جدول یا بیشتر، به یکدیگر و با یک شیوه خاصی مربوط میشوند. که در حقیقت بخشی از سازمانیست که استفاده از دیتابیس های مرتبط را مشخص میکند.
ایجاد یک Database جدید
برای ایجاد یک دیتابیس جدید به نام BookstorDB به کنسول MariaDB با استفاده از دستور زیر وارد شوید. (از شما خواسته میشود که پسورد یوزر root را وارد نمایید)
کد PHP:
[root@centos ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
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)]>
MariaDB [(none)]> CREATE DATABASE BookstoreDB;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]>
معرفی انواع Data در MariaDB
همانطور که قبلا توضیح دادیم جدول ها (Tables) در حقیقت اجزایی از دیتابیس هستند که ما اطلاعات دائمی خود را در آنها نگهداری خواهیم کرد. هر جدول شامل حداقل 2 فیلد یا بیشتر (همچنین با عنوان ستون (columns) نیز شناخته میشود) از یک نوع خاص از دیتایی (نوع اطلاعات) میباشد که این فیلدها میتوانند ذخیره کند.
رایجترین انواع Data در MariaDB به شرح زیر میباشد. (شما میتوانید لیست کامل انواع Data را از داکیومنت های آنلاین رسمی MariaDB مشاهده فرمایید)
Numeric (عددی)
BOOLEAN : مقدار 0 را به عنوان False و هر مقدار دیگری را به عنوان true در نظر میگیرد.
TINYINT : اگر با SIGNED استفاده شود رنج -128 تا 127 و در صورتیکه UNSIGNED استفاده شود رنج 0 تا 255 میباشد.
SMALLINT : اگر با SIGNED استفاده شود رنج -32768 تا 32767 میباشد و رنج UNSIGNED از 0 تا 65535 میباشد.
INT : اگر با UNSIGNED استفاده شود رنج 0 تا 4294967295 میباشد و در غیر اینصورت از رنج -2147483648 تا 2147483647 میباشد.
نکته : در TINYINT ,SMALLINT و INT به صورت پیش فرض SIGNED در نظر گرفته میشود.
DOUBLE(M, D) : که M در حقیقت تعداد کل رقم هاست و D تعداد رقم های بعد از علامت اعشار میباشد. که در حقیقت یک عدد متغییر و با دقت بالاتر را مشخص میکند. اگر UNSIGNED مشخص شده باشد مقدارهای منفی مجاز نیستند.
String (رشته ای)
VARCHAR(M) : این نوع از داده، رشته ای از متغییر طولی را ارائه میدهد که M در حقیقت حداکثر میزان طول ستون به واحد byte میباشد (65,535). در اکثر مواقع تعداد byte ها، مشخصه ای برای تعداد کاراکترهاست مگر برای یکسری کاراکترها که بیشتر از 3 بایت باشند. به طور مثال، حرف اسپانیایی "ñ" که یک کاراکتر به حساب میآید اما در حقیقت 2 بایت میباشد.
TEXT(M) : ستونی را ارائه میکند که حداکثر طول کاراکترها 65,535 میباشد. به هر حال همانطور که در VARCHAR(M) گفتیم حداکثر طول واقعی در صورت ذخیره کاراکترهای چند بایتی، کاهش پیدا خواهد کرد. اگر M مشخص شود، ستون به عنوان کوچکترین نوعی که بتواند این چنین تعدادی از کاراکترها را در خود ذخیره کند ایجاد میشود.
MEDIUMTEXT(M) و LONGTEXT(M) که شبیه به TEXT(M) هستند با این تفاوت که حداکثر طول مجاز کاراکترها به ترتیب 16,777,215 و 4,294,967,295 میباشند.
Date و Time (تاریخ و ساعت)
DATE : مشخص کننده تاریخ به فرمت YYYY-MM-DD
TIME : مشخص کننده ساعت به فرمت HH:MM:SS.sss (ساعت، دقیقه، ثانیه و میلی ثانیه)
DATETIME : ترکیبی از DATE و TIME به فرمت YYYY-MM-DD HH:MM:SS
TIMESTAMP : که برای تعریف لحظه ای که یک ردیف اضافه و یا آپدیت میشود، مورد استفاده قرار میگیرد.
بعد از اینکه انواع دیتا مرور کردیم، اکنون ما برای تشخیص اینکه چه نوعی از دیتا را برای اختصاص دادن به یک ستون در یک جدول نیاز داریم دید بهتری خواهیم داشت.
برای مثال، نام یک شخص به راحتی میتواند با VARCHAR(50) تعریف شود در حالیکه یک پست وبلاگ به نوع TEXT نیاز خواهد داشت. (که با توجه به نیاز خود M را انتخاب میکنیم)
ایجاد Table ها با Key های Primery و Foreign
قبل از اینکه به سراغ ایجاد جدول برویم، دو مفهوم اساسی و پایه درباره دیتابیس ها که مرتبط به هم میباشد، نیاز است آنها را مرور کنیم. (Key های Primery و Foreign)
یک Primery Key در حقیقت شامل مقداریست که به صورت Unique (منحصر به فرد) هر ردیف را در جدول متمایز میکند. از طرف دیگر یک Foreign Key برای ایجاد یک لینک بین دیتا در دو جدول مورد استفاده قرار میگیرد، و همچنین میتوان برای کنترل دیتایی که میتواند در جدولی که Foreign Key در آن قرار گرفته است ذخیره شود، استفاده کرد. هر دو Key های Primery و Forign عموما با نوع INT مشخص میشوند.
برای شفاف سازی، اجازه دهید از دیتابیس BookstorDB استفاده کنیم و دو جدول با نام های AuthorsTBL و BooksTBL همانند زیر ایجاد کنید. مشخصه NOT NULL برای این منظور استفاده میشود که مشخص کنیم فیلد مورد نظر حتما نیاز به یک مقدار دارد و نباید خالی باشد.
همچنین، AUTO_INCREMENT برای این منظور استفاده میشود که هر رکورد جدیدی که وارد جدول میشود باید مقدار آن یکی از قبلی بیشتر باشد و در حقیقت هر رکورد را یک مقدار از ستون Primery Key با نوع INT افزایش میدهد. در حقیقت فیلدی است که به صورت اتوماتیک در هر رکورد جدید مقدار آن یکی نسبت به فیلد قبلی بیشتر میباشد. (به صورت اتوماتیک افزایش میابد)
کد PHP:
MariaDB [(none)]> USE BookstoreDB;
MariaDB [(none)]> CREATE TABLE AuthorsTBL (
AuthorID INT NOT NULL AUTO_INCREMENT,
AuthorName VARCHAR(100),
PRIMARY KEY(AuthorID)
);
MariaDB [(none)]> CREATE TABLE BooksTBL (
BookID INT NOT NULL AUTO_INCREMENT,
BookName VARCHAR(100) NOT NULL,
AuthorID INT NOT NULL,
BookPrice DECIMAL(6,2) NOT NULL,
BookLastUpdated TIMESTAMP,
BookIsAvailable BOOLEAN,
PRIMARY KEY(BookID),
FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
);
کد PHP:
MariaDB [(none)]> USE BookstoreDB;
Database changed
MariaDB [BookstoreDB]>
MariaDB [BookstoreDB]> CREATE TABLE AuthorsTBL (
-> AuthorID INT NOT NULL AUTO_INCREMENT,
-> AuthorName VARCHAR(100),
-> PRIMARY KEY(AuthorID)
-> );
Query OK, 0 rows affected (0.26 sec)
MariaDB [BookstoreDB]>
MariaDB [BookstoreDB]> CREATE TABLE BooksTBL (
-> BookID INT NOT NULL AUTO_INCREMENT,
-> BookName VARCHAR(100) NOT NULL,
-> AuthorID INT NOT NULL,
-> BookPrice DECIMAL(6,2) NOT NULL,
-> BookLastUpdated TIMESTAMP,
-> BookIsAvailable BOOLEAN,
-> PRIMARY KEY(BookID),
-> FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
-> );
Query OK, 0 rows affected (0.71 sec)
MariaDB [BookstoreDB]>
انتخاب (Select)، وارد کردن (Inserting)، آپدیت (Update) و حذف ردیف ها (Row)
ما ابتدا به جدول AuthorsTBL دیتا وارد میکنیم. به این دلیل که قبل از وارد کردن رکوردی به جدول BooksTBL ما نیاز داریم که برای AuthorID مقدار تعریف کنیم.
کوئری زیر را از محیط کنسول MariaDB خود اجرا کنید.
کد PHP:
MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Mohammad Habili'), ('Masome Vahid'), ('Parham Nemati');
اگر شما بخواهید که یک رکورد را در یک زمان مشخصی پیدا کنید، شما میتوانید از عبارت WHERE برای مشخص کردن یک شرط به ازای یک ردیف استفاده کنید. به طور مثال :
کد PHP:
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Mohammad Habili';
کد PHP:
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
کد PHP:
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Mohammad Habili';
+----------+-----------------+
| AuthorID | AuthorName |
+----------+-----------------+
| 1 | Mohammad Habili |
+----------+-----------------+
1 row in set (0.05 sec)
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
+----------+-----------------+
| AuthorID | AuthorName |
+----------+-----------------+
| 1 | Mohammad Habili |
| 2 | Masome Vahid |
| 3 | Parham Nemati |
+----------+-----------------+
3 rows in set (0.00 sec)
MariaDB [BookstoreDB]>
اکنون با استفاده از AuthorID مربوطه برای match شدن با نویسنده هر کتاب، یک کوئری INSERT برای جدول BooksTBL ایجاد میکنیم. مقدار 1 در BookIsAvailable نشان دهنده این است که کتاب در انبار موجود است و 0 برعکس آن میباشد.
کد PHP:
MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
VALUES ('And Then There Were None', 1, 14.95, 1),
('The Man in the Brown Suit', 1, 23.99, 1),
('The Stand', 2, 35.99, 1),
('Pet Sematary', 2, 17.95, 0),
('The Green Mile', 2, 29.99, 1),
('The Alchemist', 3, 25, 1),
('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
کد PHP:
MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
-> VALUES ('And Then There Were None', 1, 14.95, 1),
-> ('The Man in the Brown Suit', 1, 23.99, 1),
-> ('The Stand', 2, 35.99, 1),
-> ('Pet Sematary', 2, 17.95, 0),
-> ('The Green Mile', 2, 29.99, 1),
-> ('The Alchemist', 3, 25, 1),
-> ('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
MariaDB [BookstoreDB]>
توجه کنید که اکنون چگونه فیلد BookLastUpdated یک مقدار متفاوت را نمایش میدهد. همانطور که قبلا توضیح دادیم یک فیلد TIMESTAMP هنگامی که رکوردی وارد جدول شود یا به هنگام آخرین تغییر (Update) این فیلد مقداری را نمایش خواهد داد.
کد PHP:
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
کد PHP:
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
| BookID | BookName | AuthorID | BookPrice | BookLastUpdated | BookIsAvailable |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
| 1 | And Then There Were None | 1 | 14.95 | 2017-10-07 10:21:13 | 1 |
| 2 | The Man in the Brown Suit | 1 | 23.99 | 2017-10-07 10:21:13 | 1 |
| 3 | The Stand | 2 | 35.99 | 2017-10-07 10:21:13 | 1 |
| 4 | Pet Sematary | 2 | 17.95 | 2017-10-07 10:21:13 | 0 |
| 5 | The Green Mile | 2 | 29.99 | 2017-10-07 10:21:13 | 1 |
| 6 | The Alchemist | 3 | 25.00 | 2017-10-07 10:21:13 | 1 |
| 7 | By the River Piedra I Sat Down and Wept | 3 | 18.95 | 2017-10-07 10:21:13 | 0 |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
7 rows in set (0.00 sec)
MariaDB [BookstoreDB]>
MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [BookstoreDB]>
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
+--------+---------------+----------+-----------+---------------------+-----------------+
| BookID | BookName | AuthorID | BookPrice | BookLastUpdated | BookIsAvailable |
+--------+---------------+----------+-----------+---------------------+-----------------+
| 6 | The Alchemist | 3 | 22.75 | 2017-10-07 10:48:35 | 1 |
+--------+---------------+----------+-----------+---------------------+-----------------+
1 row in set (0.00 sec)
MariaDB [BookstoreDB]>
به هنگام آپدیت کردن ردیف ها، ایده خوبیست که اول برای مشاهده رکوردهایی که قرار است آپدیت شوند یک SELECT بگیریم. زمانیکه ما قصد داریم یک ردیف مجزا را update کنیم، معمولا بعد از عبارت WHERE یک فیلد primary key استفاده میشود
اگر چه ما در این مقاله اقدام به حذف یک رکورد نمیکنیم اما شما در صورتی که دیگر نیاز به یک رکورد نداشتید میتوانید آنرا حذف کنید. برای مثال فرض کنید ما میخواهیم رکورد مربوط به کتاب "The Alchemist" را از BooksTBL حذف کنیم.
برای هذف بالا ما از عبارت DELETE به شکل زیر استفاده خواهیم کرد.
کد PHP:
MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;
همچنین فراموش نکنید که عبارت WHERE را برای اعمال یک شرط (BookID=6) برای select کردن record خاص که قرار هست حذف شود اضافه کنید. در غیر اینصورت ریسک حذف کردن تمام ردیف ها در جدول را خواهید داشت.
اگر شما بخواهید که دو فیلد یا بیشتر را به همدیگر ارتباط دهید شما میتوانید از عبارت CONCAT استفاده کنید. برای مثال فرض کنید که ما میخواهیم نتیجه ای را داشته باشیم که شامل یک فیلد از نام کتاب و نویسنده آن به فرمت "The Alchemist (Parham Nemati)" و ردیف دیگر به همراه قیمت باشد.
برای انجام دادن هدف بالا ما نیاز به یک JOIN بین جدول های AuthorsTBL و BooksTBL روی فیلد مشترک هر دو جدول (AuthorID) خواهیم داشت.
کد PHP:
MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
خروجی query بالا را در تصویر زیر مشاهده میکنید.
کد PHP:
MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
+---------------------------------------------------------+-----------+
| Description | BookPrice |
+---------------------------------------------------------+-----------+
| And Then There Were None (Mohammad Habili) | 14.95 |
| The Man in the Brown Suit (Mohammad Habili) | 23.99 |
| The Stand (Masome Vahid) | 35.99 |
| Pet Sematary (Masome Vahid) | 17.95 |
| The Green Mile (Masome Vahid) | 29.99 |
| The Alchemist (Parham Nemati) | 22.75 |
| By the River Piedra I Sat Down and Wept (Parham Nemati) | 18.95 |
+---------------------------------------------------------+-----------+
7 rows in set (0.00 sec)
MariaDB [BookstoreDB]>
ایجاد کاربر برای دسترسی به دیتابیس BookstoreDB
استفاده از کاربر root به منظور اجرا کردن تمام عملیات DML در دیتابیس یک ایده بسیار بد است. برای جلوگیری کردن از این مورد ما میتوانیم یک حساب کاربری جدید در MariaDB ایجاد کنیم (که نام این کاربر را در اینجا bookstoreuser تعریف میکنیم) و تمام سطوح دسترسی لازم برای دیتابیس BookstorDB را به این user اختصاص میدهیم.
کد PHP:
MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'YourPasswordHere';
MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON BookstoreDB.* to bookstoreuser@localhost;
MariaDB [BookstoreDB]> FLUSH PRIVILEGES;
کد PHP:
MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY '13631451';
Query OK, 0 rows affected (0.00 sec)
MariaDB [BookstoreDB]>
MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON BookstoreDB.* to bookstoreuser@localhost;
Query OK, 0 rows affected (0.02 sec)
MariaDB [BookstoreDB]>
MariaDB [BookstoreDB]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [BookstoreDB]>
ترفند های دیگر MySQL
برای پاک کردن خط فرمان MariaDB دستور زیر را تایپ و Enter را فشار دهید.
کد PHP:
MariaDB [BookstoreDB]> \! clear
کد PHP:
MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];
کد PHP:
MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
کد PHP:
MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| BookID | int(11) | NO | PRI | NULL | auto_increment |
| BookName | varchar(100) | NO | | NULL | |
| AuthorID | int(11) | NO | MUL | NULL | |
| BookPrice | decimal(6,2) | NO | | NULL | |
| BookLastUpdated | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| BookIsAvailable | tinyint(1) | YES | | NULL | |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)
MariaDB [BookstoreDB]>
با مشاهده سریع فیلد BookIsAvailable متوجه خواهید شد که این فیلد مقدار NULL را نیز قبول میکند. در صورتیکه ما نخواهیم اجازه دهیم که این فیلد NULL باشد، باید همانند زیر تیبل را ALTER کنیم.
کد PHP:
MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;
در نهایت باید برای مشاهده همه دیتابیس ها باید روی سرور در کنسول MySQL دستورات زیر را تایپ کنید.
کد PHP:
MariaDB [BookstoreDB]> SHOW DATABASES;
OR
MariaDB [BookstoreDB]> SHOW SCHEMAS;
کد PHP:
[root@centos ~]# mysql -u bookstoreuser -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
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)]>
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| BookstoreDB |
+--------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> SHOW SCHEMAS;
+--------------------+
| Database |
+--------------------+
| information_schema |
| BookstoreDB |
+--------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]>
خلاصه
در این مقاله ما درباره نحوه راه اندازی عملیات DML و چگونگی ایجاد یک دیتابیس، جدول و یوزرهای اختصاصی روی یک دیتابیس MariaDB توضیحاتی دادیم، علاوه بر این ترفندهای کمی را که ممکن است کارهای یک ادمین system/database را آسانتر کند با شما به اشتراک گذاشتیم.
در صورتی که درباره موضوع بالا هر گونه سوالی داشتید میتوانید در همین پست مطرح کنید.
کامنت