درآمدی بر زبان SQL (بخش سوم و پایانی)

درآمدی بر زبان SQL (بخش سوم و پایانی)

پس از معرفی پرکاربردترین عملگرها و دستورات به اصطلاح DML در زبان اس‌کیو‌ال، در این آموزش به معرفی دستوراتی می‌پردازیم که جهت تعریف اِسکیمای (ساختار) دیتابیس به کار می‌روند که اصطلاحاً به آن‌ها Data Definition Language یا به اختصار DDL می‌گویند.

دستور CREATE
از این دستور به منظور ایجاد یک دیتابیس جدید و یا آبجکت‌های مربوط به دیتابیس (مانند جداول، ایندکس‌ها، فانکشن‌ها، ویوها، تریگِرها و غیره) استفاده می‌شود که سینتکسی به صورت زیر دارا است:

CREATE DATABASE database_name;

به عنوان مثال، برای ایجاد دیتابیسی جدید تحت عنوان sokanacademy، از دستور CREATE به صورت زیر استفاده خواهیم کرد:

CREATE DATABASE sokanacademy;

پس از ایجاد یک دیتابیس جدید، جهت ذخیره‌سازی دیتای مد نظر خود نیاز به یکسری جداول داریم که برای ساخت یک جدول جدید هم می‌توان از دستور CREATE TABLE استفاده کرد. همان‌طور که می‌دانیم، هر جدولی از تعدادی سطر و ستون‌ تشکیل شده است و از همین روی در حین ایجاد  جداول می‌بایست تمامی اطلاعات مربوطه شامل نام هر ستون، دیتاتایپ (نوع داده‌ای) و اندازهٔ دیتایی که قرار است در هر ستون ذخیره شود و دیگر اطلاعات مربوطه را نیز مشخص کنیم. بدین ترتیب، جهت ساخت یک جدول، می‌توان از سینتَکس زیر استفاده کرد:

CREATE TABLE table_name (
    column_name1 datatype(size),
    column_name2 datatype(size),
    column_name3 datatype(size),
   ....
);

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

دیتاتایپ توضیح
CHAR جهت نگهداری اِسترینگ با طول ثابت استفاده می‌شود که مقدار ثابت آن باید داخل پرانتز مشخص گردد و این در حالی است که این نوع دیتاتایپت می‌تواند تا ۲۵۵ کاراکتر را درخود ذخیره سازد.
VARCHAR جهت نگهداری اِسترینگ با طول متغیر استفاده می‌شود بدین ترتیب که حداکثر طول اِسترینگ می‌بایست داخل پرانتز مشخص شود به طوری که این نوع دیتاتایپت می‌تواند تا ۲۵۵ کاراکتر را ذخیره کند.
TEXT جهت نگهداری اِسترینگ با حداکثر طول ۶۵۵۳۵ کاراکتر استفاده می‌شود.
INT به صورت پیش‌فرض جهت نگهداری اعداد در بازهٔ ۲۱۴۷۴۸۳۶۴۸- تا ۲۱۴۷۴۸۳۶۴۸ استفاده می‌شود ولی در صورت ذکر کیورد UNSIGNED هنگام تعریف یک فیلد، اعداد در دامنهٔ ۰ تا ۴۲۹۴۹۶۷۲۹۵ را نگهداری می‌کند (لازم به ذکر است که حداکثر تعداد ارقام قابل نگهداری می‌بایست در داخل پرانتز مشخص شود.)

TINYINT

به صورت پیش‌فرض جهت نگهداری اعداد در دامنه ۱۲۸- تا ۱۲۷ استفاده می‌شود ولی در صورت ذکر کیورد UNSIGNED هنگام تعریف یک فیلد، اعداد در دامنهٔ ۰ تا ۲۵۵ را نگهداری می‌کند (لازم به ذکر است که حداکثر تعداد ارقام قابل‌نگهداری می‌بایست در داخل پرانتز مشخص شود.)
FLOAT  جهت نگهداری اعداد اعشاری نه چندان بزرگ استفاده می‌شود.
DATE جهت نگهداری تاریخ با فرمت YYYY-MM-DD (سال-ماه-روز) استفاده می‌شود.
DATETIME جهت نگهداری ترکیبی از تاریخ و زمان با فرمت YYYY-MM-DD HH:MI:SS (سال-ماه-روز ساعت-دقیقه-ثانیه) استفاده می‌شود.

حال با توجه به توضیحات فوق قصد داریم جدولی به نام articles در دیتابیس sokanacademy ایجاد کنیم که برای انجام این کار می‌توان از دستور CREATE TABLE به صورت زیر استفاده کرد:

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    published DATETIME
);

که به عنوان خروجی دستور فوق یک جدول با نام articles که دارای چهار فیلد content ،title ،id و published است در دیتابیس sokanacademy ایجاد خواهد شد (همان‌طور که مشاهده می‌کنید فیلد id در جدول articles دارای دو اصطلاحاً Constraint است که عبارتند از Primary Key و Auto Increment که در ادامه به معرفی این دو مورد خواهیم پرداخت.) همچنین استفاده از کلیدواژگان NOT NULL برای فیلد title به این معنی است که هنگام ایجاد یک رکورد جدید، مقدار این فیلد نمی‌تواند خالی باشد.

Auto Increment 
معمولاً در هر جدول از دیتابیس فیلد id به صورت Auto Increment تعریف می‌شود که این کار باعث مقداردهی اتوماتیک این فیلد توسط DBMS در هنگام ایجاد یک رکورد جدید می‌گردد (برای آشنایی بیشتر با این اصطلاح می‌توانید به مقاله DBMS: درآمدی بر سیستم‌های مدیریت پایگاه‌داده مراجعه نمایید.) به طور معمول مقداردهی به این فیلد از عدد ۱ شروع می‌شود و با هر بار افزودن یک رکورد جدید به جدول، مقدار فیلد id رکورد جدید یک واحد از مقدار فیلد id رکورد قبلی بیشتر است. به عنوان مثال، فرض کنید می‌خواهیم دو مقاله به صورت زیر به جدول articles اضافه کنیم:

INSERT INTO articles (title, content, published)
VALUES ('What is a DataBase', 'This is a sample content.', NOW());

INSERT INTO articles (title, content, published)
VALUES ('SQL For Beginners', 'This is an other sample content.', NOW());

نتیجهٔ دو دستور فوق موجب می‌شود تا دو رکورد به شکل زیر به جدول articles اضافه گردد:

+----+--------------------+----------------------------------+---------------------+
| id | title              | content                          | published           |
+----+--------------------+----------------------------------+---------------------+
|  1 | What is a DataBase | This is a sample content.        | 2018-08-21 03:04:18 |
|  2 | SQL For Beginners  | This is an other sample content. | 2018-08-21 03:14:29 |
+----+--------------------+----------------------------------+---------------------+

و همان‌طور که مشاهده می‌کنید، مقدار فیلد id هر رکورد که به صورت خودکار مقداردهی شده به اندازه یک واحد از رکورد قبلی خود بیشتر است (دقت داشته باشید که در کوئری فوق از فانکشن از پیش تعریف شده در MySQL به نام ()NOW استفاده کرده‌ایم که تاریخ و زمان فعلی سیستم را برمی‌گرداند که جهت مقداردهی به فیلد published استفاده شده است.)

Primary Key 
کلید اصلی (Primary Key) یک شناسهٔ منحصربه‌فرد (Unique) مانند شمارهٔ ملی و یا شماره تلفن در هر جدول از دیتابیس‌های رابطه‌ای است به طوری که مقدار فیلدی که به عنوان Primary Key انتخاب شده است نمی‌تواند تکراری و یا خالی باشد. معمولاً در دیتابیس‌های رابطه‌ای فیلد id را به عنوان کلید اصلی هر جدول در نظر می‌گیرند تا بتوان با استفاده از این فیلد، هر موجودیت از جدول را برای انجام تَسک‌هایی همچون آپدیت یا حذف شناسایی کرد.

دستور DROP
در زبان SQL برای حذف آبجکت‌هایی مانند یک دیتابیس، جداول، ایندکس‌ها و یا ویوها از دستور DROP استفاده می‌کنیم:

DROP object object_name;

به عنوان مثال، می‌خواهیم جدول professors را از دیتابیس university که در بخش‌های قبلی این سری از آموزش‌ها به آن پرداخته بودیم حذف کنیم که برای انجام این کار ابتدا دیتابیس مورد نظر را با استفاده از دستور USE انتخاب کرده سپس از دستور DROP همانند زیر استفاده می‌کنیم:

USE university;
DROP TABLE professors;

دقت کنید که با دستور DROP کل ساختار جدول همراه با دیتای موجود در آن حذف خواهند شد. به عنوان مثالی دیگر، می‌خواهیم دیتابیس university را به طور کامل از DBMS خود حذف کنیم که برای انجام این کار می‌توان از دستور زیر استفاده کرد:

DROP DATABASE university;

که با دستور فوق، کل ساختار دیتابیس به همراه تمامی جداول و داده‌های موجود در آن حذف خواهند شد (لازم به ذکر است که از این دستور بایستی با احتیاط استفاده کرد چرا که داده‌های حذف شده قابلیت بازیابی ندارند.)

دستور ALTER TABLE
پس از آنکه یک جدول جدید ایجاد کردیم، ممکن است بنا بر نیازهای مختلفی که در حین توسعهٔ اپلیکیشن به وجود می‌آید مجبور به تغییر در ساختار برخی جداول شویم که برای این کار می‌توان از دستور ALTER TABLE استفاد کرد به طوری که این دستور اجازهٔ انجام عملیات زیر را بر روی یک جدول به دولوپرها می‌دهد:

- اضافه کردن یک ستون جدید به جدول با استفاده از کِلاز (بَند) ADD
- ایجاد تغییرات در یک ستون از جدول با استفاده از کِلاز MODIFY (به عنوان مثال، تغییر مقدار پیش‌فرض یک فیلد)
- حذف ستون‌های جدول با استفاده از کِلاز DROP

در ادامه، به ترتیب هر یک از سه حالت فوق را بررسی خواهیم کرد. جهت اضافه کردن یک فیلد جدید به جدول می‌توان از سینتکس زیر استفاده کرد:

ALTER TABLE table_name
ADD new_colum data_type column_constraint [AFTER existing_column];

دقت کنید که تمامی ستون‌های جدیدی که به جدول اضافه می‌شوند به صورت پیش‌فرض پس از آخرین ستون جدول قرار می‌گیرند ولی برای تغییر محل قرارگیری ستون جدید در جدول می‌توان از کِلاز اختیاری AFTER استفاده کرد. حال با این توضیحات فرض کنید می‌خواهیم یک فیلد جدید به نام created_at جهت ذخیرهٔ تاریخ و زمان ایجاد مقاله به جدول articles اضافه کنیم که برای این کار باید از دستور ALTER TABLE به صورت زیر استفاده کرد:

ALTER TABLE articles ADD created_at DATETIME NOT NULL DEFAULT NOW();

توجه کنید به دلیل اینکه قبل از اضافه کردن فیلد created_at، جدول articles دارای دو رکورد است و از آنجایی که فیلد created_at نمی‌تواند در هیچ رکوردی از جدول خالی باشد، بنابراین تاریخ و زمان فعلی سیستم را به عنوان مقدار دیفالت (پیش‌فرض) این فیلد در نظر گرفته‌ایم تا مقدار این فیلد را برای رکوردهایی از جدول که قبلاً وجود داشته‌اند به صورت اتوماتیک مقداردهی کند و بدین ترتیب از رخ دادن اِرور هنگام اجرای دستور فوق جلوگیری می‌کنیم.

جهت تغییر برخی از ویژگی‌های یک ستون یا فیلد از جدول (مانند NOT NULL ،UNIQUE و Data Type) می‌توان از کِلازی تحت عنوان MODIFY همراه دستور ALTER TABLE به صورت زیر استفاده کرد:

ALTER TABLE table_name MODIFY column_definition;

دقت داشته باشید که ویژگی‌های ستونی از جدول را باید ویرایش کنیم که حاوی هیچ دیتایی نباشند چرا که اگر آن ستون در هنگام ویرایش دارای هر نوع داده‌ای باشد، ممکن است استفاده از دستور فوق منجر به از دست رفتن همیشگی اطلاعات شود! به عنوان مثال، فرض کنید که دیتاتایپ یک ستون VARCHAR است و ما می‌خواهیم آن را به INT تغییر دهیم که بر این اساس، DBMS سعی می‌کند تمامی دیتاهای موجود در آن ستون را از نوع VARCHAR به INT تغییر دهد که اگر در خِلال انجام این کار با شکست مواجه شود، ممکن است از مقدار دیفالت برای آن ستون استفاده کند و ممکن است چنین رویکردی دقیقاً آن چیزی نباشد که ما انتظارش را داریم. به عنوان مثالی از ویرایش ویژگی‌های یک ستون، فرض کنید می‌خواهیم ویژگی NOT NULL را به فیلد content از جدول articles اضافه کنیم که برای این کار می‌توان از دستور زیر استفاده کرد:

ALTER TABLE articles MODIFY content TEXT NOT NULL;

و در نهایت برای حذف یک ستون از جدول می‌توان از دستور ALTER TABLE به صورت زیر استفاده کرد:

ALTER TABLE table_name DROP column_name;

برای حذف ستون created_at از جدول articles هم می‌توان به صورت زیر عمل کرد:

ALTER TABLE articles DROP created_at;

که در نتیجهٔ دستور فوق فیلد created_at به همراه تمامی دیتاهای ذخیره شده در آن از جدول حذف خواهند شد. ذکر این نکته نیز خالی از لطف نیست که با یک دستور می‌توان چندین فیلد را هم‌زمان به جدول اضافه و یا از آن حذف کرد بدین ترتیب که می‌توان چندین کلاز DROP و یا ADD را در حالی که با کاما (,) از یکدیگر جدا شده‌اند، در دستور ALTER TABLE به صورت زیر استفاده کرد:

ALTER TABLE table_name DROP column_name, DROP colum_name, ...;

در پایان لازم به ذکر است که در این سری از آموزش‌های مرتبط با زبان #اس‌کیو‌ال سعی شد تا مخاطب با مقدمات این زبان آشنا شود و در صورت مطالعهٔ مباحث پیشرفته‌تر زبان SQL، بتواند درک بهتری نسبت به آن‌ها داشته باشد. جهت دسترسی به دو آموزش ابتدایی از این سری می‌توانید به لینک‌های زیر مراجعه نمایید:

درآمدی بر زبان SQL (بخش اول)
- درآمدی بر زبان SQL (بخش دوم)

هچنین جهت آشنایی بیشتر با مفاهیم کلی #دیتابیس، می‌توانید به مقالات زیر مراجعه نمایید:

درآمدی بر مفهوم Database و انواع Data Model
DBMS: درآمدی بر سیستم‌های مدیریت پایگاه‌داده
درآمدی بر انواع مختلف دیتابیس‌های NoSQL

حال نوبت به نظرات شما می‌رسد. از دید شما کاربردی‌ترین منابع برای یادگیری زبان SQL کدامند؟ نظرات، دیدگاه‌ها و تجربیات خود را با دیگر کاربران سکان آکادمی به اشتراک بگذارید.