درس اول- SQL

مقدمه
درود٬ خوش‌ آمدید به دوره متوسطه  پی‌اچ‌پی٬ در این دوره تلاش خواهیم کرد که در ادامه دوره قبل «برنامه نویسی مقدماتی» با مروری بر آنچه که یاد گرفتیم، مفاهیم پیشرفته‌تری را بر داشته‌هایمان اضافه کنیم. تلاش ما در دوره مقدماتی، آشنایی با مفاهیم ابتدایی و پایه‌ای این زبان بود و در کنار آن مقداری هم به تکنیک‌های مورد استفاده در پی‌اچ‌پی پرداختیم. از زبان SQL گفتیم و در کد نوشته‌هایمان از آن استفاده کردیم. حال بنا داریم تا به مفاهیم پیشرفته‌ همراه جزییات بیشتر بپردازیم.

در این دوره بنا بر نیاز دانستن مفاهیم SQL ابتدا به سراغ آن رفته و دانش‌مان را در این حوزه تا حد پیشرفته گسترش خواهیم داد و بعد به مفاهیم عمیق‌تر پی‌اچ‌پی می‌پردازیم.

 

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

۱. ابتدایی‌ترین مسئله در بکارگیری پایگاه داده‌ها، شناخت و فهم دقیق ابزار‌های آن و آگاهی یافتن از نیاز برنامه در ساخت پایگاه است. این بدین معنی است که شما قبل از شروع به ساخت پایگاه باید الگوی آن را تعیین و بررسی کنید.

۲. برای ساخت پایگاه و متعلقاتش (جدول، ستون ...) باید از یک نظام نام گذاری که در زیر آورده می‌شود پیروی کرد:

• داشتن فقط حروف، اعداد و زیرین خط (underscore).
• شباهت نداشتن با کلمات از پیش تعریف شده در زبان
•  توجه به بزرگی یا کوچکی حروف
• محدود بودن به ۶۴ کاراکتر
• منحصر به فرد بودن در حوزه استفاده خود

۳. در زمان ساخت جدول باید نوع داده مورد استفاده در هر ستون را مشخص کرد. بطور کلی انواع داده‌ها در SQL به سه قسمت متن، اعداد و زمان تقسیم می‌شوند که هر کدام دارای زیر قسمت‌هایی هستند.

۴. هر ستون به غیر از نام و نوع می‌تواند دارای خصوصیت‌هایی باشد که نحوه عملکرد آن ستون را بیشتر مشخص می‌کند. این خصوصیت‌ها شامل:

• NOT NULL
• AUTO_INCREMENT
• PRIMARY KEY

و غیره هستند.

۵. برای دستیابی به پایگاه، نیاز به یک ارتباط دهنده دارید که در دوره مقدماتی به دو نوع آن یعنی: PHPMYSQL  و  COMMAND PROMPT  اشاره کردیم.

نکته: از اینجابه بعد فرض بر آن‌ست که شما مشکلی در استفاده از CMD برای ارتباط با پایگاه ندارید. پس اگر فکر می‌کنید هنوز ایرادی وجود دارد لطفا به درس‌های دوره مقدماتی برگشته و با یک مرور خود را آماده این قسمت کنید.

 

دستور عمل‌های ساخت و ارتباط با پایگاه
دستورات ساخت پایگاه:

CREATE DATABASE نام پایگاه    ;

USE نام پایگاه   ;

دستورات ساخت جدول:

CREATE  TABLE   نام جدول
(
نام ستون    نوع ستون   ,
نام ستون    نوع ستون   ,

نام ستون    نوع ستون  

...
);

SHOW TABLES ;

SHOW COLUMNS FROM نام جدول  ;

وارد کردن رکورد در جدول:

INSERT INTO نام جدول (ستون۱, ستون۲  …)

VALUES (مقدار۱, مقدار۲ …)

انتخاب داده‌ها در پایگاه:

SELECT نام ستون‌ها   FROM نام جدول

SELECT  *   FROM نام جدول 

استفاده از شرطی در پرس‌و‌جو:

SELECT   نام ستون‌ها FROM   نام جدول

WHERE   ستون مورد نظر     =  '  مقدار '

 

فاکتور LIKE  و NOT LIKE در پرس‌و‌جو
برای جستجو در میان ستون‌هایی که متن در اختیار دارند، می‌توانید با استفاده از این دو گزینه یک الگویی برای جستجو بسازید. دقت کنید:

SELECT  *  FROM  users

WHERE  username LIKE  '_e%'

دو علامت  _ و % در اینجا بکار گرفته شده که به شرح زیر هستند:

ــ : این علامت مشخص کننده تنها یک حرف است. یعنی مشخصا یک حرف که هر چی می‌تواند باشد.

% : این علامت مشخص کننده چندین حرف است.

اجازه بدهید با توضیح مثال بالا این مطلب را بیشتر شرح دهیم. معنی دستور بالا می‌شود:

از جدول  usersهمه داده‌ها را انتخاب کن، که در نام کاربری آنها حرف ابتدایی هر چه باشد، حرف دوم  eباشد و باقی هم هر چه که بود. حال خروجی این دستور می‌تواند مانند زیر باشد:

peyman, pejman , reza و یا هر کلمه‌ای که حرف دوم آنe  باشد.

NOT LIKE برعکس عمل می‌کند یعنی رکوردهایی را باز می‌گرداند که مقدار آنها در داده‌ها وجود نداشته باشند.

 

مرتب کردن داده‌های خروجی دستور‌ها
برای مرتب کردن داده‌ها از Order By  استفاده می‌کنیم.

SELECT * FROM users ORDER BY register_date  ASC

این دستور تمام داده‌های جدول را انتخاب کرده و آنها را با توجه به داده‌های ستون register_date بصورت صعودی مرتب می‌کند. برای نمایش نزولی باید ازDESC استفاده کرد.

 

محدود کردن خروجی پرس‌و‌جو
Limit  کلمه‌ای است که برای محدود کردن خروجی پرس‌و‌جو استفاده می‌شود:

SELECT * FROM USERS LIMIT 2

این دستور تنها دو رکورد را بر می‌گرداند.

SELECT * FROM USERS LIMIT 0  , 5

این دستور از رکورد 0 شروع کرده و 5 رکورد را نمایش می‌دهد.

SELECT first_name, last_name

FROM users ORDER BY

register_date DESC LIMIT 5;

این دستور هم ستون‌های نام و نام خانوادگی را انتخاب کرده، با توجه به تاریخ ثبت نام آنها را بصورت نزولی مرتب کرده و بعد ۵ داده آخر را نمایش می‌دهد. به این ترتیب ۵ نفر آخر که ثبت نام شده‌اند نمایش داده می‌شوند.

 

معرفی توابع SQL
در زمان انجام پرس‌و‌جو شما می‌توانید از توابعی بکار برید که کار پس‌و‌جو را بطور منظم‌تری و با توجه به نیاز شما کامل کنند. بطور مثال در دوره قبلی با تابعی بنام  NOW()آشنا شدیم که زمان حال که در سرور ثبت شده را بر‌می‌گرداند. و یا تابع  SHA1()که رمز را برای شما کدگذاری می‌کند. بیشتر توابع همراه با دستور SELECT و برای انجام کاری روی ستون‌های مورد انتخاب شما بکار می‌روند. بطور مثال فرض کنید که می‌خواهید نام کاربران را چاپ کنید اما می‌خواهید این کار با حروف بزرگ انجام شود:

SELECT UPPER(first_name) FROM users

خب، این تابع خروجی پرس‌و‌جو را به حروف بزرگ تبدیل می‌کند و بعد در اختیار شما قرار می‌دهد.

توابع معمولا بر اساس اثر گذاری خود به چند دسته تقسیم می‌شوند. این تقسیم بندی معمولا بر اساس خروجی تابع است و در صورت وجود ورودی (پارامتر) بر اساس عملی (تغییراتی) است که تابع بر روی پارامتر خود انجام می‌دهد. مثلا این تغییرات بر روی متن، حروف و یا اعداد می‌تواند باشد.

توابع متنی
این دست از توابع، متن را به عنوان پارامتر گرفته و عمل خود را انجام می‌دهند.

CONCAT(): این تابع عمل چسباندن دو کلمه یا متن را انجام می‌دهد. مثلا فرض کنید می‌خواهیم نام و نام خانوادگی را با هم بچسبانیم و بعد نمایش دهیم.

SELECT CONTACT(first_name, last_name ) FROM users;

LENGTH(): تعداد کاراکترهای داخل پارامتر را بر می‌گرداند:

SELECT LENGTH(last_name) FROM users;

TRIM(): فاصله‌ها را از قبل و بعد از پارامتر بر می‌دارد. در زبان پی‌اچ‌پی هم تابعی با همین نام وجود دارد که فاصله‌ها را حذف می‌کند.

البته توابع دیگری هم هستند که ما به همین‌ها بسنده می‌کنیم برای اطلاعات بیشتر می‌توانید به وب‌سایت W3Schools مراجعه کنید.

توابع اعدادی
از مهم‌تریت توابع اعدادی می‌توان به موارد زیر اشاره ‌کرد:

FORMAT( ): این تابع فرمت نمایش عدد را مشخص می‌کند. که هم می‌تواند اعداد را سه رقم سه رقم از هم جدا و در صورت استفاده از پرامتر دوم تعداد رقم اعشاری را هم تعیین کند.

FORMAT(23056.2) // 23,056.2

FORMAT(23056.2, 2) // 23,056.20

RAND( ): این تابع رفتار انتخاب تصادفی را دارد. مورد استفاده آن مثلا می‌تواند برای نمایش تصادفی داد‌ها باشد:

SELECT * FROM users

ORDER BY RAND();

ROUND( ): یک مقدار را بصورت ورودی گرفته و آن عدد را بصورت گرد شده نمایش می‌دهد.

 

توابع زمانی
همان‌طور که از نام این بخش پیداست این دست توابع اعمالی را بر روی داده‌های زمانی انجام می‌دهند.

DATE( ): این تابع تاریخ داده را بر می‌گرداند.

select date(register_date) from users limit 1; //  2012-12-15

HOUR( ): این تابع ساعت داده را بر می‌گرداند.

select hour(register_date) from users limit 1; // 9

MINUTE( )

select minute(register_date) from users limit 1; //58

DAYOFMONTH( )

select dayofmonth(register_date) from users limit 1; // 25

DAYNAME( )

select dayname(register_date) from users limit 1; // Saturday

CURDATE( )

select curdate() from users limit 1; // 2012-12-15

CURTIME( )

select curtime() from users limit 1; //  18:29:14

NOW( )

select now() from users limit 1;   //  2012-12-15 18:29:47

البته بعضی از این توابع نیاز به پارامتر ورودی دارند و بعضی باید بدون پارامتر استفاده شوند.

علاوه بر این توابع دو تابع مهم دیگر برای استفاده با زمان و تاریخ وجود دارند که قابلیت‌های بیشتری را برای تغییرات و یا شخصی سازی نحوه نمایش تاریخ و زمان دارند. این دو تابع عبارت هستند از DATE_FORMAT( ) و TIME_FORMAT( ) قابلیت فرمت دهی به تاریخ و زمان را دارند.

DATE_FORMAT( ) : این تابع قابلیت فرمت دهی هم به زمان و هم به تاریخ را دارا است و البته برای استفاده از این تابع باید یک پارامتر را به عنوان کلید فرمت دهی مشخص کنید. بطور مثال:

SELECT DATE_FORMANT(register_date, '%M %D, %Y') FROM users limit 1; // December 15th, 2012

این تابع دو پارامتر قبول می‌کند که ابتدایی همان زمان یا تاریخ است (زمان یا تاریخ را می‌توان از داده داخل پایگاه خواند مانند مثال بالا) و پارامتر بعدی الگوی فرمت دهنده است. همانطور که در مثال بالا می‌بینید، با استفاده از این تابع و داده ثبت شده در register_date فرمت با نام ماه، روز ماه و سال بدست آمده است.

برای مشاهده تمام این پارامترها که اجازه فرمت دهی به ما می‌دهند می‌توانید به این آدرس مراجعه کنید.

TIME_FORMAT( ): بر  خلاف تابع قبلی این تابع فقط قابلیت کار با زمان را دارا است اما نحوه کار به همان شکل است.

 

همانطور که گفتیم این درس بیشتر برای یادآوری گفته شده‌ها و آشنایی با مفاهیم گفته نشده در دوره مقدماتی بود. حال که اطلاعات کافی در مورد پایگاه داده‌ها بدست آوردیم وقت آن رسیده که به شیوه طراحی ابتدایی پایگاه بپردازیم. در درس بعدی به سراغ نحوه طراحی پایگاه قبل از اقدام به ساخت آن می‌رویم.