آموزش پایگاه داده پیشرفته – آشنایی با ۲۱ دستور پرکاربرد اس کیو ال (SQL)
یکی از سیستم‌هایی که در دنیای کامپیوتر بسیار به کار می‌آید، پایگاه داده است. اگر در یک سیستم، مجموعه‌ای از داده‌ها (هر نوع داده‌ای) وجود داشته باشد به آن سیستم یک پایگاه داده می‌گویند. در ادامه قصد داریم نحوه مدیریت یک پایگاه داده پیشرفته را برای شما شرح دهیم. قبل از آن شما را با انواع پایگاه داده و معروف‌ترین پایگاه داده‌های مورد استفاده آشنا خواهیم کرد. همچینن درباره دستوراتی که می‌توانید با آن‌ها به دستکاری پایگاه داده بپردازید نیز صحبت خواهیم کرد.
فهرست
پایگاه داده چیست؟
پایگاه داده، ساختاری است که ذخیره داده، اعمال تغییرات در داده‌ها و بازیابی آن‌ها را تسهیل می‌کند. برای هر یک از این کارها، عملگرهایی تعریف شده است به که شما اجازه دستکاری داده‌ها را می‌دهد. شما می‌توانید با نصب یک سیستم مدیریت پایگاه داده (Database Management System) به ایجاد و مدیریت پایگاه داده بپردازید. در واقع شما باید پرسوجوهای مناسبی را برای یافتن داده‌های مور نظر خود در این سیستم اجرا کنید. پایگاه داده، به صورت یک فایل یا مجموعه‌ای از فایل‌ها ذخیره میشود. اطلاعات موجود در پایگاه داده ممکن است به رکوردهایی (Record) تقسیم شود. رکوردها نیز خود به فیلدها (field) تقسیم می‌شوند. فیلدها، واحد‌هایی هستند که اطلاعات مربوط به جنبه خاصی از موجودیت تعریف شده در پایگاه داده را در خود ذخیره می‌کنند.
رکوردها نیز در جداول ذخیره می‌شوند و در واقع با استفاده از این جداول می‌توان به رابطه بین فیلدهای مختلف پی برد. اگر چه می‌توان به هر مجموعه‌ای از داده‌ها که روی کامپیوتر ذخیره شده است اصطلاح پایگاه داده را نسبت داد، اما در واقع یک پایگاه داده باید توانایی جستجو، دسته‌بندی، انتخاب و پاک کردن داده‌ها را برای شما فراهم کند. در واقع دستوراتی در نظر گرفته شود که بتوانید با استفاده از آن‌ها به دستکاری پایگاه داده بپردازید. تا اینجا، مقداری با مفهوم پایگاه داده آشنا شدید.
اگر می‌خواهید با پایگاه داده و جنبه‌های مختلف آن به خوبی آشنا شوید، می‌توانید از مجموعه آموزش پایگاه داده که در فرادرس موجود است استفاده کنید:
نحوه عملکرد پایگاه داده پیشرفته
رکوردها و فایل‌های موجود در پایگاه داده باید سازمان دهی شده باشند تا کاربر بتواند به راحتی اطلاعات را بازیابی کند. استفاده از پرس‌وجو (Query)، متداول ترین راهی است که کاربران برای بازیابی اطلاعات از آن استفاده می‌کنند. فرض کنید در پایگاه داده شما اطلاعات مربوط به دانشجویان ذخیره شده است و شما می‌خواهید نام و نام خانوادگی دانشجویانی که معدل بالای ۱۸ دارند را پیدا کنید. اینجا همان جایی است که پایگاه داده به کمک شما می‌آید. اگر پرس‌وجوی مناسبی را برای پیدا کردن این دانشجویان بنویسید، سیستم مدیریت پایگاه داده قادر به نمایش داده‌های مورد نظر شما خواهد بود.
فرض کنید پایگاه داده‌ای از اطلاعات دانشجویان در اختیار شما قرار دارد. اگر بخواهید دانشجویان هم شهری را در دانشکده‌های برق و عمران پیدا کنید چه؟ با اجرای پرس‌وجوی صحیح، سیستم مدیریت پایگاه داده با ایجاد ارتباط بین جداولی که حاوی رکوردهای دانشجویان این دو دانشکده هستند، می‌تواند داده‌های مورد نظر شما را نمایش دهد.
بازیابی صحیح داده‌ها برای شرکت‌هایی که روی داده‌های کاربران خود پردازش‌های مختلفی را انجام می‌دهند بسیار مهم است. شرکت‌هایی مانند فروشگاه‌های آنلاین خرید و فروش علاقه دارند تا رفتار کاربران را طبق داده‌هایی که از آن‌ها در پایگاه داده ذخیره شده است پیش‌بینی کنند. بنابراین این شرکت‌ها افراد متخصصی را استخدام می‌کنند که مهارت خوبی در کار با پایگاه‌های داده داشته باشند و بتوانند اطلاعات به سرعت بازیابی کنند. پس همانطور که گفتیم برای استفاده از پایگاه داده پیشرفته باید تسلط خوبی روی عملگرها و نوشتن پرس‌وجو داشته داشته باشید که در ادامه این مطلب به آن‌ها خواهیم پرداخت.
انواع پایگاه داده پیشرفته
انواع مختلفی از پایگاه داده وجود دارد. مهم‌ترین انواع پایگاه داده را در این قسمت معرفی خواهیم کرد.
۱. پایگاه داده متمرکز
در این نوع از پایگاه داده، داده‌ها در یک محیط متمرکز ذخیره می‌شوند. بنابراین کاربران نیازی ندارند تا داده‌ها را مکان‌های مختلف بازیابی کنند. این پایگاه‌های داده، احراز هویت را برای کاربران خود فراهم می‌کند تا آن‌ها به صورت امن به داده‌ها دسترسی داشته باشند. یک مثال از پایگاه داده متمرکز، پایگاه داده کتابخانه است که با دسترسی به سیستم‌های کتابخانه و احراز هویت می‌توانید به داده‌های مرتبط به کتاب‌ها دسترسی پیدا کنید.
۲. پایگاه داده توزیع شده
بر خلاف پایگاه داده متمرکز، در پایگاه داده توزیع شده داده‌ها در پایگاه‌های داده مختلف یک سازمان ذخیره می‌شوند. این سیستم‌ها از طریف لینک‌های ارتباطی به یکدیگر متصل هستند. ایجاد ارتباط بین سیستم‌های مختلف، دسترسی کاربران به داده‌ها و بازیابی آن‌ها را ساده کرده است. پایگاه داده آپاچی کاساندرا (Apache Cassandra) نمونه‌ای از پایگاه داده توزیع شده محسوب میشود.
۳. پایگاه داده رابطه ای
یکی از متداول‌ترین انواع پایگاه داده، پایگاه داده رابطه‌ای است. پیکربندی این پایگاه داده بر اساس مدل رابطه‌ای انجام شده است. داده‌های موجود در پایگاه داده رابطه‌ای در جداول ذخیره می‌شوند. در پایگاه داده رابطه‌ای از زبان پرس‌وجوی ساخت یافته (Structed Query Language) برای مرتب کردن و دستکاری داده‌ها استفاده میشود. در هر جدول پایگاه داده رابطه‌ای، یک کلید تعریف میشود که وجود این کلید باعث تمایز آن جدول از سایر جداول خواهد شد. موارد زیر، مثال‌هایی از پایگاه‌های داده رابطه‌ای هستند:
- مای اس کیو ال
- ماکروسافت اس کیو ال سرور
- اوراکل
برای آشنایی با انواع پایگاه داده می‌توانید به صفحات زیر مراجعه کنید.
- آموزش اس کیو ال سرور SQL Server – مقدماتی
- آموزش پایگاه داده مای اس کیو ال MySQL
- آموزش اوراکل – مدیریت بانک اطلاعاتی با Oracle
- آموزش مقدماتی آپاچی کاساندرا Apache Cassandra
۴. پایگاه داده نواس کیو ال (NoSQL)
ذخیره داده‌ها در این پایگاه داده به شیوه‌های مختلف انجام میشود و برای مدیریت حجم بزرگی از داده‌ها مناسب است. این نوع از پایگاه داده پیشرفته، در پاسخ به نیازهای کاربران مدرن ایجاد شد.
۵. پایگاه داده ابری
این نوع از پایگاه داده، در یک محیط مجازی ایجاد شده و روی پلتفرم‌های ابری اجرا میشود. از سرویس‌های مختلف رایانش ابری مانند SaaS برای دسترسی کاربران به داده‌های موجود در پایگاه داده ابری استفاده میشود. پلتفرم‌های ابری زیادی وجود دارند اما بهترین آن‌ها عبارت‌اند از:
- وب سرویس آمازون (Amazon Web Service)
- مایکروسافت آژور (Microsoft Azure)
- گوگل کلود اس کیو ال (Google Cloud SQL)
۶. پایگاه داده شی گرا
این پایگاه داده از مدل شی گرا برای ذخیره سازی داده‌ها استفاده می‌کند. در این پایگاه داده، داده‌ها به عنوان شی ذخیره شده و نمایش داده می‌شوند. مفاهیم شی گرایی در این پایگاه داده مشابه شی گرایی در زبان‌های برنامه نویسی مانند جاوا است.
۷. پایگاه داده سلسه مراتبی
داده‌های موجود در این پایگاه داده در قالب والد – فرزند ذخیره می‌شوند. هر رکورد فرزند موجود در پایگاه داده، تنها یک والد دارد. اما هر والد می‌تواند صاحب چندین فرزند باشد. ساختار پایگاه داده رابطه‌ای را در شکل زیر مشاهده کنید:
۸. پایگاه داده شبکه ای
نوع دیگری از پایگاه داده پیشرفته که داده‌های در آن به صورت نودهای به هم متصل ذخیره می‌شوند. بر خلاف مدل سلسله مراتبی، هر رکورد می‌تواند چندین فرزند و والد داشته باشد. ساختار این پایگاه داده مانند یک گراف است.
۹. پایگاه داده شخصی
اگر یک کاربر، اطلاعات خود را روی سیستم شخصی‌اش ذخیره کند، در واقع یک پایگاه داده شخصی را برای خود ایجاد کرده است.
۱۰. پایگاه داده سازمانی
سازمان‌های بزرگ از این پایگاه داده برای مدیریت حجم بزرگی از داده‌ها استفاده می‌کنند. این پایگاه داده به شرکت‌ها کمک می‌کند تا بهره‌وری خود را افزایش دهند. چنین پایگاه داده‌ای، دسترسی هم زمان چندین کاربر به داده‌ها را فراهم می‌کند.
۱۱. پایگاه داده عملیاتی
این نوع از پایگاه داده، در لحظه ساخته و بروزرسانی میشود. این نوع از پایگاه داده برای مدیریت داده‌های روزانه و انجام عملیات‌های لازم روی آن‌ها، توسط کسب‌وکارها استفاده میشود. از این پایگاه داده برای ترکیب تراکنش‌های روزانه استفاده میشود.
معرفی دستورات پیشرفته SQL برای مدیریت یک پایگاه داده
در این قسمت قصد داریم دستورات پیشرفته اس کیو ال را برای بهره برداری از یک پایگاه داده رابطه‌ای توضیح دهیم. همانطور که قبلاً گفتیم، از دستورات اس کیو ال برای نوشتن پرسوجوهای لازم به منظور بازیابی اطلاعات از یک پایگاه داده رابطه‌ای استفاده میشود.
اگر با دستورات مقدماتی اس کیو ال آشنایی ندارید، پیشنهاد می‌کنیم به لینک زیر در فرادرس سری بزنید.
در ادامه مهم‌ترین دستورات پیشرفته اس کیو ال را معرفی خواهیم کرد.
۱. نمایش کلید اصلی (Primary Key)
هر جدول می‌تواند یک کلید اصلی داشته باشد. مقادیر ستون کلید اصلی نباید Null باشند. پرس‌وجوی زیر، لیستی از کلیدهای اصلی را باز می‌گرداند:
‘SELECT * from Sys.Objects WHERE Type=’PK
۲. نمایش کلید یکتا ( (Unique Key
کلید یکتا، ستونی از جدول است که تمامی مقادیر آن با یکدیگر متفاوت هستند. یک جدول می‌توان یک یا چند ستون یکتا داشته باشد. در ستون یکتا، یکی از مقادیر می‌تواند Null باشد. با استفاده از دستور زیر می‌توان به کلیدهای یکتا دسترسی پیدا کرد:
‘SELECT * FROM Sys.Objects WHERE Type=’uq
۳. نمایش کلیدهای خارجی (Foreign Key)
کلید خارجی برای اتصال یک جدول به جدول دیگر استفاده میشود. کلید خارجی یک جدول، کلید اصلی در جدولی دیگر محسوب میشود. با استفاده از پرس‌وجوی زیر می‌توان به کلیدهای خارجی دست پیدا کرد:
‘SELECT * FROM Sys.Objects WHERE Type=’f
۴. نمایش تریگرها (Trigger)
تریگر، مجموعه‌ای از دستورات از پیش نوشته شده است. این دستورات می‌توانند با توجه به یک رخداد خاص اجرا شوند. مثلاً می‌توان تریگر را طوری نوشت که با رخ دادن یکی از اعمال Insert، Update یا Delete، تریگر اجرا شود. برای مشاهده تریگرها، باید پرس‌وجوی زیر را اجرا کنید:
‘SELECT * FROM Sys.Objects WHERE Type=’tr
۵. نمایش جداول داخلی
جداول داخلی، جداولی هستند که توسط خود سیستم مدیریت پایگاه داده ایجاد می‌شوند. محتوای این جداول توسط شما قابل دستکاری نیست. اما می‌توانید این جداول را با استفاده از پرس‌وجوی زیر مشاهده کنید:
‘SELECT * FROM Sys.Objects WHERE Type=’it
۶. نمایش لیستی از رویه‌ها (Procedures)
رویه، مجموعه‌ای از پرس‌وجو‌ها محسوب میشود. می‌توان در هر زمانی که نیاز داشتیم، رویه را فراخوانی کنیم تا دستورات موجود در آن اجرا شوند. می‌توان با نوشتن پرس‌وجوی زیر، به لیستی از رویه‌های تعریف شده دسترسی پیدا کرد:
‘SELECT * FROM Sys.Objects WHERE Type=’p
۷. جابجایی مقادیر دو ستون موجود در یک جدول با یکدیگر
گاهی نیاز دارید تا مقادیر موجود در دو ستون مختلف یک جدول را با یکدیگر جابجا کنید. برای این کار می‌توانید از پرس‌وجوی زیر استفاده کنید:
UPDATE Customers SET Zip=Phone, Phone=Zip
در مثال بالا، مقادیر ستون Zip به ستون Phone منتقل شده و مقادیر ستون Phone نیز به ستون Zip منتقل شده‌اند.
۸. انتخاب n رکورد بالای جدول
فرض کنید جدول شما شامل هزاران رکورد است (به هر سطر جدول یک رکورد گفته میشود) اما شما تنها می‌خواهید ۲۵ رکورد اول را مشاهده کنید. برای این کار باید از پرس‌وجوی زیر استفاده کنید:
;SELECT TOP 25 FROM Customers WHERE Customer_ID<>NULL
پرس‌وجوی بالا، ۲۵ رکورد ابتدایی جدول Customers را به شما نمایش خواهد داد.
۹. پرس و جوهای شرطی
فرض کنید می‌خواهید مواردی مانند سن یا قیمت به شرط کمتر بودن از یک عدد مشخص، برای شما بازگردانده شود. در مثال زیر می‌خواهیم لیست مشتریانی که قیمت کالاهای خریداری شده توسط آن‌ها از ۵۰ کمتر است را به دست بیاوریم. برای این کار، پرس‌وجوی زیر را روی جداول مشتری و سفارش انجام می‌دهیم:
SELECT Name FROM Customers WHERE EXISTS
(SELECT Item FROM Orders
(WHERE Customers.ID = Orders.ID AND Price < 50
۱۰. کپی کردن مقادیر یک جدول در یک جدول جدید
فرض کنید می‌خواهید رکوردهایی را که از سال ۲۰۱۸ به جدول خود اضافه کرده‌اید، به جدول دیگری اضافه کنید. برای این کار می‌توانید از پرس‌وجوی زیر استفاده کنید:
INSERT INTO Yearly_Orders
SELECT * FROM Orders
WHERE Date<=1/1/2018
در واقع از دستور insert into می‌توانید برای کپی کردن مقادیر جدول خود به جداول دیگر استفاده کنید.
۱۱. ایجاد View
فرض کنید می‌خواهید نتایچ پرس‌وجوی خود را به صورت جدولی مشاهده کنید. برای این کار می‌توانید یک View ایجاد کنید. View جدولی است مانند جداول دیگر، یعنی دارای سطر و ستون‌هایی است. با این تفاوت که تنها مقادیر خاصی که مدنظر شماست و در پرس‌وجو به آن‌ها اشاره کرده‌اید آمده است.
به مثال زیر نگاه کنید:
CREATE VIEW Failing_Students AS
SELECT S_NAME, Student_ID
FROM STUDENT
;WHERE GPA > 4;
در این مثال از پایگاه داده پیشرفته، یک View به نام Failing_Students ایجاد کرده‌ایم که شامل دو ستون S_Name (نام دانشجو) و Student_ID (شماره دانشجویی) است.
۱۲. بازیابی View
فرض کنید قصد مشاهده View ساخته شده در مرحله قبل را دارید. برای این کار می‌توانید دستور زیر را اجرا کنید:
;SELECT * FROM Failing_Students
۱۳. بروزرسانی View
فرض کنید می‌خواهید یک View به نام Product List را بروزرسانی کنید. پرس‌وجوی زیر، این View را با توجه به دستورات نوشته شده در آن بروزرسانی می‌کند، یا اگر این View از قبل ایجاد نشده باشد، آن را ایجاد خواهد کرد:
CREATE OR REPLACE VIEW [ Product List] AS
SELECT ProductID, ProductName, Category
FROM Products
;WHERE Discontinued = No
۱۴. حذف View
برای حذف یک View از دستور Drop استفاده میشود. به پرس‌وجوی زیر نگاه کنید:
;DROP VIEW V1
۱۵. نمایش لیستی از View ها
اگر بخواهید لیستی از viewهای ساخته شده را مشاهده کنید، می‌توانید از پرس‌وجوی زیر استفاده کنید:
;SELECT * FROM My_Schema.views
۱۶. مرتب سازی داده ها با استفاده از دستور Order By
فرض کنید می‌خواهید نتایج شما به صورت صعودی، نزولی یا بر اساس حروف الفبا نمایش داده شود. برای این کار باید از دستور order by استفاده کنید.
به مثال زیر نگاه کنید:
SELECT EMP_ID, LAST_NAME FROM EMPLOYEE
;WHERE CITY = ‘Seattle’ ORDER BY EMP_ID
توجه داشته باشید که order by باید همواره در انتهای پرس‌وجو بیاید.
اگر داده‌های شما از نوع رشته باشد، دستور order by آن‌ها بر اساس حروف الفبا مرتب خواهد کرد. اگر داده‌های شما از نوع عدد باشد، با استفاده از دستور order by می‌توانید آن‌ها را به صورت صعودی یا نزولی مرتب کنید. مثلاً اگر بخواهید نتایج به صورت صعودی مرتب شوند،باید عبارت asc را به انتهای پرس‌وجوی خود اضافه کنید، مانند مثال زیر:
SELECT EMP_ID, LAST_NAME FROM EMPLOYEE_TBL
;WHERE CITY = ‘INDIANAPOLIS’ ORDER BY EMP_ID asc
اما اگر بخواهید نتایج به صورت نزولی مرتب شوند، باید عبارت desc را به انتهای پرس‌وجو اضافه کنید.
۱۷. مرتب سازی داده ها با استفاده از دستور Group By
با استفاده از این دستور می‌توان نتایج را بر اساس یکی از ستون‌های جدول گروه بندی کرد.. برای مثال، در پرس‌وجوی زیر نتایج بر اساس نام گروه بندی خواهند شد:
SELECT Name, Age FROM Patients WHERE Age > 40
;GROUP BY Name, Age ORDER BY Name
دستور group by همواره باید به دنبال where و قبل از order by بیاید.
۱۸. نمایش تعداد
فرض کنید می‌خواهید تعداد محصولات را از جدول استخراج کنید. برای این کار می‌توانید از پرس‌وجوی زیر استفاده کنید:
SELECT COUNT(ProductID)
;FROM Products
۱۹. محاسبه جمع
فرض کنید می‌خواهید جمع مقادیر یک ستون را محاسبه کنید. برای مثال در پرس‌وجوی زیر جمع حقوق کارمندان پایین سی سال محاسبه شده است:
;SELECT SUM(Salary)FROM Employee WHERE Emp_Age < 30
۲۰. محاسبه معدل
از دستور avg می‌توانید برای محاسبه معدل استفاده کنید. به مثال زیر نگاه کنید که در آن متوسط قیمت کالاها محاسبه شده است:
;SELECT AVG(Price)FROM Products
۲۱. بازیابی مقادیر غیرتکراری از یک ستون
فرض کنید می‌خواهید در نتایج خود از مقادیر تکراری خبری نباشد. با استفاده از دستور distinct در پرس‌وجوی خود می‌توانید تنها نتایج غیرتکراری را به دست آورید:
SELECT DISTINCT ID FROM Customers
کلام پایانی درباره پایگاه داده پیشرفته
در این مطلب، به معرفی دستورات پیشرفته اس کیو ال پرداختیم. اگر می‌خواهید پایگاه داده را به صورت حرفه‌ای و پیشرفته بیاموزید، سری به فرادرس بزنید. فرادرس، مرجعی برای آموزش آنلاین است و مجموعه‌ای از بهترین دوره‌های آموزشی درباره پایگاه داده و برنامه نویسی در آن قرار دارد.