آموزش SQL

[SherlockHolmes]

Sql چیست؟!
Sql سر فصل Structured Query Language هست!که در دهه 70 توسط تیم تحقیقاتی مرکز سن خوزه IBM برای کار با RDBMS ابداعی IBM به نام System R بوجود آمد!
این زبان بعد ها به صورت استاندارد کار با دیتا بیس ها از طرف ANSI پذیرفته شد! البته بعد ها دیتا بیس های مختلف هر کدوم انشعاب های خاص خودشون رو برای این زبان ابداع کردند که البته همه ی این زبان های منشعب در دستورات عمومی با SQL استاندارد ANSI یکسان هستند!دستوران SQL به دو دسته تقسیم میشوند!
1: Data Manipulation Language _ DML
دستورات این دسته برای کوئری گرفتن از دیتابیس همچنین انجام امور اصلی بر روی رکوردهاست!DML شامل این دستورات میشود:
SELECT
DELETE
UPDATE
INSET INTO
2: DDL _ Data Definition Language
دستوراتی برای بوجود آوردن جدول ها ست کردن رابطه ها (RelationShips) ست کردن ایندکس ها و ...
در RDBMS ]ای امروز اکثر این دستورات رو عناصر ویژوال از چشمان شما دور نگه داشته اند ولی دونستن این که در پشت پرده چه اتفاقی داره می افته مسما خالی از لطف نخواهد بود!!
از دستورات DDL هم میشه این ها رو نام برد!!
CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
ROP INDEX

توی این تاپیک دستورات ساده و عمومی ولی پر کاربرد زبان sql رو بررسی خواهیم کرد!!!
در ابتدا اگر یتا بیس NorthWind رو روی کامپیوترتون ندارید از اینجا (http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en) دریافتش کنید!
نکته:زبان sql یک زبان استاندارد است و در تمام دیتا بیسها زبان استاندارد استخراج اطلاعات است!ما در این تاپیک از sql Server2000 و محیط Query Analyser استفاده خواهیم کرد!


توی این تاپیک ما دستورات ساده و عمومی ولی پر کاربرد زبان sql رو بررسی خواهیم کرد!!!
در ابتدا اگر دیتا بیس NorthWind رو روی کامپیوترتون ندارید از اینجا (http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en) دریافتش کنید!

نکته 1:تقریبا 60 تا 70 درصد آموزش ها نوشته شده و موجوده!!اپس احتمالا ما توی این تاپیک تا آخر آموزش SQL رو خواهیم داشت!و نگرانی از این بابت نیست!!

نکته2:زبان sql یک زبان استاندارد است و در تمام دیتا بیسها زبان استاندارد استخراج اطلاعات است!ما در این تاپیک من به دلیل آشنایی بیشتر با MsSQl از Sql Server2000 و محیط Query Analyser استفاده خواهم کرد!ولی دستورات گفته شده

نکته3:ادیتور های زبان Sql به بزرگی و کوچیکی دستورات حساس نیستند!ولی معمولا دستورات Sql تماما بزرگ نوشته میشوند!!پس به این نکته هم دقت داشته باشید!

نکته 4:من توی این آموزش مبنا رو بر این گذاشتم که شما اصول دیتا بیس رو بلدید پس این آموزش ها از صفر شروع نمیشند!

[SherlockHolmes]

دستور SELECT
دستور سلکت یکی از مهم ترین و اولیه ترین دستورات در زبان sql است!برای استخراج همه یا بخشی از یک جدول!!
در هنگامی که ما از دستور SELECT استفاده میکنیم!!دیتا بیس جدولی موقتی (Result-set) با توجه به کدی که شما نوشته اید بوجود میاره که شما میتونید از اون جدول که مسلما کوچکتر از جدول اصلی هم هست !!کار کنید!
خوب سراغ جدول Categories میریم!که محتویاتش رو در عکس زیر میبینید!!
http://my.blackmice.com/imagecenter/full/2/1144127077.gif

صورت مسئله ما اینه که میخوایم اسم Category هامون رو به عنوان مثال در یک Drop down list پر کنیم!!ما توی این مسئله فقط به اسم Category ها احتیاج داریم!!پس فقط اون ها رو انتخاب میکنیم!!


SELECT CategoryName
FROM Categories




نتیجه رو میبینید:
http://my.blackmice.com/imagecenter/full/2/1144127573.gif

انتخاب چند ستون در دستور SELECT

حالا صورت مسئله رو به این صورت تقییر میدیم!که ما میخوایم توی همون Drop Down List اسم ها رو نشون بدیم!!ولی به عنوان Value اسم ها میخوایم از Id ایشون استفاده کنیم!!
کوئری به این صورت خواهد بود!




SELECT CategoryId , CategoryName
FROM Categories


اینم نتیجه:


http://my.blackmice.com/imagecenter/full/2/1144127980.gif

انتخاب یک جدول!!
اگر شما بخواهید یک جدول رو با کلیه اطلاعاتش برگردونید میتونید از عبارت * استفاده کنید!!



SELECT *
FROM Categories


نتیجه:

http://my.blackmice.com/imagecenter/full/2/1144127077.gif
همونطور که میبینید کل جدول به عنوان Result-set برگردونده شد!

نکته:سعی کنید تا وقتی واقعا نیاز نشده از SELECT * استفاده نکنید!!چون به دلیل حجم بالایی از اطلاعات که بر میگردونه ممکنه باعث کندی و حتی هنگ کردن برنامه در صورت زیاد بودن حجم اطلاعات درون تیبل بشه!در هر صورت فراموش نکنید که زبان sql برای این بوجود اومده که بتونید اطلاعات رو از درون جداول استخراج کنید!!نه اینکه جداول رو از توی دیتابیس!!

[SherlockHolmes]

Sorting Data
سورت کردن دیتا ها یا مرتب کردنشون یکی از مسائلیه که بی اهمیت به نظر میرسه!اما از مسائلیه که دقت شما و حرفه ای بودن شما رو در برنامه نهایی برای چشمان تیز بین ثابت میکنه!Sort کردن دیتا ها مثل بقیه کارها در Sql بسیار ساده است!اما اول یینیم که سورت کردن دیتا ها در کجا ها به درد میخوره!!
حتما شما هم تا حالا به سایتهایی برخوردید که امکان سورت کردن اطلاعات بر اثاث المان های مختلف رو بهتون میده!!نمونه دم دست همین صفحه ی لیست کاربران (http://my.blackmice.com/memberlist.php)در همین سایت هست!شما وقتی روی عبارت نوشته ها کلیک میکنید یوزر ها بر اثاث تعداد پستشون Sort میشن!
اما چگونه؟!
توی پست بالا گفتیم چطور میشه داده ها رو استخراج کرد!!اما چطور میشه اطلاعات رو Sort کرد؟!
به سادگی تمام
در پست قبل یاد گرفتیم که چطور دیتاهایی که بهشون نیاز داریم رو استخراج کنیم!!


SELECT CompanyName FROM Shippers

و نتیجه این شد:

http://my.blackmice.com/imagecenter/full/2/1144215078.gif


همونطور که میبینید سطر ها بدون ترتیب خاصی نمایش داده شده اند!شاید توی دیتا های کوچک اهمیت سورتینگ خیلی مشخص نباشه ولی اگه مثلا تعداد سطر های Result-set 40 تا بود پیدا کردن شلخته بودن دیتا ها واقعا میتونست آزار دهنده باشه!!ولی من اینجا بخاطر فهم ساده تر به همین دیتا های کوچیک اکتفا کردم!
دستور ORDER BY باعث سورت شدن دیتا ها میشه!!
مثال بالا رو به این صورت تغییر میدیم!


SELECT CompanyName FROM Shippers
ORDER BY CompanyName


همون طور که میبینید بعد از ORDER BY نام فیلدی که میخواهیم اطلاعات بر اثاث اون ها سورت بشه رو میاریم!!
ونتیجه اینطوری میشه!!
http://my.blackmice.com/imagecenter/full/2/1144215398.gif

فرموش نکنید که میشه دیتا ها بر اثاث فیلدی که SELECT نشده هم سورت کرد!!یعنی الزاما شما مجبور نیستید برای اینکه مشتری ها رو بر اثاث Id ایشون سورت میکنید حتما آی دیتون رو هم SELECT کنید!

Sorting by Column Position
Sort های چند گانه!!
شما میتونید اطلاعات دریافتیتون رو بر اثاث چند فیلد Sort کنید!این به چه درد میخوره؟!!فرض کنید از شما خواسته شده که مشتری ها رو بر اثاث کشور Sort کنید تا بشه آمار دقیق رو بدست آورد!اما شما وقتی بر اثاث کشور دیتا هاتون رو Sort میکنید.ممکنه تعداد زیادی از مشتری ها مثلا کشورشون آمریکا باشه و این مورد میتونه باعث نا منظم بودن دیتا ها بشه!!
با Sort چند گانه میهش این مشکل رو حل کرد!
به Query زیر توجه کنید:

SELECT ContactName , Country,City FROM Customers
ORDER BY Country,City


نتیجه این میشه:
http://my.blackmice.com/imagecenter/full/2/1144299469.gif

همونطور که میبینید دیتا ها اول بر اثاث کشور سورت شدن!و وقتی دیتا هایی در کشور با هم مشترک بودن بر اثاث شهر و بعد در صورت وجود چند مشتری در یک شهر بر اثاث اسم مشتری!
فقط توجه داشته باشید که اگه المان اول داده ی تکراری نداشته باشه مسلماMultiple Sorting مفهومی نخواهد داشت!!

حالا بیاید یه تکنیک کوچولو رو هم با هم ببینیم:


SELECT ContactName , Country , City FROM Customers
ORDER BY 2 ,3 ,1

نتیجه:

http://my.blackmice.com/imagecenter/full/2/1144299469.gif

نتیجه با کوئری بالایی تفاوتی نداره در حقیقت شما توی این کوئری گفتید که بر اثاث فیلد دوم و سوم و اول SELECT اطلاعات رو سورت کن!!(فراموش نکنید که شماره فیلد های SELECT در این تکنیک مورد استفاده شده نه فیلدهای سوم و اول جدول)
چرا این روش؟خوب این روش باعث میشه که اسم فیلد ها رو ننویسید و درصد داشتن غلط املایی به صفر میرسه!!
اما معایب:خوب مسلما اگه بخوایم بر اثاث فیلدهای غیر SELECT شده سورت کنیم این امکان رو در این تکنیک نداریم!!در ضمن با اضافه کردن یک فیلد به فیلدهایی که میخوایم SELECT شون کنیم مسلما این شماره ها بهم خواهد ریخت!!و اگه فراموش کنیم شماره ها رو تصحیح کنیم مسلما نتایج عجیب غریبی رو خواهیم گرفت!!
در هر صورت روش اول امن تر خواهد بود!!

نزولی یا صعودی؟!
به این صفحه (http://www.shop.ir/browse.jsp?categoryId=3)دقت کنید!لیست دوربین ها بر اثاث قیمت Sort شده است!!خوب ما هم لیست محصولاتمون رو بر اثاث قیمت سورت میکنیم!!

Use NorthWind
SELECT ProductName , UnitPrice , UnitsOnOrder FROM Products
ORDER BY UnitPrice


http://my.blackmice.com/imagecenter/full/2/1144300562.gif

به عنوان اولین نتیجه محصول Geitost با قیمت 2.5000 میبینم!!حالا مشکل اینجاست که چطوری میتونی محصولات رو از گران به ارزان سورت کنیم؟! مثل سایت بالا؟!
به سادگی هرچه تمام تر:



Use NorthWind
SELECT ProductName , UnitPrice , UnitsOnOrder FROM Products
ORDER BY UnitPrice DESC


http://my.blackmice.com/imagecenter/full/2/1144300664.gif

عبارت DESC باعث نزولی شدن نتایج میشه!!
همونطور که میبینید این دفعه محصول Côte de Blaye با قیمت 263.5000 رو در اول محصولات دیده میشه!!عکس DESC هم ASC هست!ولی به ندرت ازش استفاده میشه چون خود SQL معمولا به صورت پیشفرض دیتا ها رو به صورت ASC سورت میکنه!!

[SherlockHolmes]

تا به اینجا ما هرچی اطلاعات بازیافت کردیم بر اثاث فیلد ها بوده!!اما حتما متوجه شدید که ما گاها نیاز داریم که رکوردهایی رو که بازیافت میکنیم محدود تر باشند!حتما این نکته رو میدونید که هرچه دیتا ست بزرگتر باشه ریسورس بیشتری مصرف میشه و در مهندسی نرم افزار باید همیشه بیشترین بازده با کمترین ریسورس بدست بیاد!!علاوه بر اون هنگامی که ریسورس بالایی برای هر کوئری مصرف بشه مسلما هنگام بالا رفتن ریکوئست ها ممکنه افت سرعت یا حتی کرش کردن برنامه پیش بیاد!نکته بعد اینه که ما باید بتونیم که تا اونجایی که ممکنه کدها و مسائل مربوط به دیتا رو در خود دیتا بی س حل کنیم!!و کار رو به CLient نکشونیم!! در بعضی صورت مسئله ها اصولا همه ی دیتا ها به هیچ دردی نمیخورند!به این مسئله توجه کنید!! مدیر یک شرکت می خواد بدونه چند تا مشتری از کشور آلمان داشته!!در راه هایی که ما تا حالا رفتیم ما فقط میتونستیم بگیم که مشتری ها رو بیار و بر اثاث کشور Sort کن!و برای این درخواست مدیر یا باید به روش های Client Base رجوع میکردیم یا اینکه......یا اینکه از WHERE استفاده کنیم!!
WHERE
به نظر WEHRE یکی از ساده ترین و دوست داشتنی ترین دستورات SQL هست!!شما با چند عبارت کوچیک میتونید دیتا های خیلی جالبی رو از بین حتی ملیون ها رکورد استخراج کنید!!
مثال بالا رو ما اینطوری حل میکنیم

SELECT ContactName , CompanyName ,Country , City FROM Customers
WHERE Country = 'Germany'
ORDER BY City



http://my.blackmice.com/imagecenter/full/2/1144396056.gif

نکته ای که باید بهش توجه کنید علامت ' هست که در زبان SQL برای مشخص کردن رشته کار برد داره!!مسلما اگه شما این کوئری را اینگونه اجرا کنید:




SELECT ContactName , CompanyName ,Country , City FROM Customers
WHERE Country = Germany
ORDER BY City



با این پیغام مواجه خواهید شد!!(البته در Query Analyser)

http://my.blackmice.com/imagecenter/full/2/1144396238.gif


در درس بعد بیشتر در باره WHERE بحث خواهیم کرد!

[SherlockHolmes]

عملگرهای مقایسه ای در Sql
خوب همون طور که در Where دیدید!! عمگر ها در این دستور نقشی اساسی دارن!!توی عکس زیر عملگر های Sql رو میبینید!

http://my.blackmice.com/imagecenter/full/2/1144478552.gif

فکر کنم همه چیز واضحه!!فقط به یک نکته توجه کنید.فرض کنید میخواید کلیه مشتری های شرکت NorthWind رو که شماره فاکس ندارن پیدا کنید!!در اولین نظر کوئری اینگونه به نظر میرسه!!


USE NorthWind
SELECT * FROM Customers
WHERE Fax = NULL


نتیجه این کوئری هیچ Result ای نیست!!هیچ اروری هم داده نمیشه!!و همه چیز درست به نظر میرسه!!ولی شما میدونید که خیلی از مشتری ها شماره ی فکسی ندارن و مسلما فیلد فکس بعضی از مشتری ها NULL هست!!اینجاست که آدم در فرط استیصال به این نتجیه میرسه که "خدای من باگ در Sql!!!" در حالی که در این وسط فقط یک اشتباه روی داده!!



USE NorthWind
SELECT * FROM Customers
WHERE Fax IS NULL


در ضورت اجرای این کوئری 22 رکورد به عنوان نتیجه برگردانده میشود!!
پس این نکته رو همیشه در ذهنتون داشته باشید!
در مورد بقیه اپراتور ها هم همه چیز واضح به نظر میرسه اما یک مثال خالی از لطف نیست!!
ما میخوایم محصولات شرکت NorthWind ای رو پیدا کنیم که قیمتشون کمتر از 21.3500 باشه!!و خود این قیمت هم شامل لیست برگردانده شده بشه!



use northWind
SELECT * FROM Products
WHERE UnitPrice <= 21.3500
ORDER BY UnitPrice DESC


http://my.blackmice.com/imagecenter/full/2/1144478962.gif

حاصل 44 رکورد هست!!

[SherlockHolmes]

خوب بعد از یاد گیری WHERE بهتره یک مقدار عمیق تر وارد WHERE و مبحث فیلترینگ دیتا ها بشیم!
بریم سر کلمات کلیدی دستور WEHRE

1:AND
فرض کنید ما میخوایم شرط های بیشتری برای فیلترینگ دیتا هامون بزاریم!!فرض کنید ما لیست مشتری هایی رو میخوایم بدست بیاریم که کشورشون آمریکاست و شماره فکسشون رو ارائه ندادن!!(قبول دارم کوئری مسخره ایه ولی قبول کنید که مثال خوبیه!!)
کوئری به صورت زیر خواهد بود:





Use NorthWind
SELECT * FROM Customers
WHERE Country = 'usa' AND fax IS NULL



http://my.blackmice.com/imagecenter/full/2/1144563924.gif

شرکت NotrhWind چهار تا مشتری آمریکایی داره که شماره فکسی از خودشون ارائه نداده اند!!

OR
عبارت OR
حتما حدس میزنید کار OR چیه !!
فرض کنید ما میخواهیم مشخصات کارمندانی که ساکن کشور آمریکا و آلمان هستند بدست بیاریم!!در دروسی که تا حالا یاد گرفتیم این کار ممکن نبود!!اما با استفاده از اپراتور OR میتونیم به راحتی این کار رو بکنیم!! اپراتور OR امکان WHERE های چند گانه رو میسر میسازه!!
به مثال زیر دقت کنید!!(البته یه مقدار با اون چیزی که گفتم متفاوته ولی میتونه مثال خوبی باشه!!

SELECT ContactName , Country FROM Customers
WHERE Country = 'Spain' OR Country = 'UK'
ORDER BY COUNTRY

http://my.blackmice.com/imagecenter/full/2/1144564357.gif

کوئری بالا 12 تا رکورد برمیگردونه!!

شما حتی میتونید از هردو این اپراتور ها یعنی AND و OR استفاده کرد!!البته فراموش نکنید که اپراتور AND همیشه در اولویت قرار داره!یعنی نفسر SQL همیشه اول میره سراغ AND و بعد به سراغ OR میاد!برای اینکه مشکلات عجیب و غریب پیش نیاد فراموش نکنید که از پرانتز استفاده کنید.چون با این کار هم کد بعد ها برای خودتون قابل فهم تره!!هم از مشکلاتی که امکان داره مفسر SQL بوجود بیاره جلوگیری میکنه!
به مثال زیر توجه کنید:مجموع یوزر هایی رو میخوایم که کشورشون یا اسپانیا باشه یا انگلستان و فکسشون خالی باشه!!


SELECT ContactName , Country , Fax FROM Customers
WHERE (Country = 'Spain' OR Country = 'UK' ) AND Fax IS NULL
ORDER BY COUNTRY


http://my.blackmice.com/imagecenter/full/2/1144564612.gif

خوب حالا پرانتز ها رو ور میداریم تا ببینیم چه اتفاقی می افته!!


SELECT ContactName , Country , Fax FROM Customers
WHERE Country = 'Spain' OR Country = 'UK' AND Fax IS NULL
ORDER BY COUNTRY


http://my.blackmice.com/imagecenter/full/2/1144573256.gif

همونطور که میبیند نتیجه کوئری اینو نشون میده که یوزر هایی آورده شدن که یا اهل اسپانیا هستند یا اهل انگلستان هستند و شماره فکسشون خالی هست!!


IN



SELECT ContactName , Country FROM Customers
WHERE Country IN ('Spain' , 'UK')
ORDER BY Country


http://my.blackmice.com/imagecenter/full/2/1144564357.gif
به کوئری بالا نگاه کنید همچنین به نتیجه اش!!
بله درست فکر کردید نتیجه دقیقا همونه که بالا در مورد OR مثال زدیم!!همونطور که میبیند IN تقریبا خلاصه شده و قابل درک تر از OR های متوالیه!!یکی دیگر از مزایای IN این هست که میشه درش از یک کوئری تو در تو استفاده کرد!!(در مورد کوئری های تو در تو بعد ها صحبت خواهیم کرد)

NOT
تقریبا NOT هم قابل حدس زدنه!!بهتره با یک مثال به بحث در موردش بپردازیم!!ما میخوایم کلیه مشتری که کشورشون آمریکا نیست بر گردونیم!!
کوئریمون به این صورت میشه:





USE NorthWind
SELECT ContactName , Country , Region
FROM Customers
WHERE NOT Region IS NULL AND NOT Country = 'usa'



http://my.blackmice.com/imagecenter/full/2/1144574640.gif

مجموع افرادی که Region اش نال نباشه همچنین کشورش آمریکا نباشه!!
به نظر همه چیز واضح میشه!!

در ضمن کوئری زیر باکوئری بالا یکسانه!!و البته مسملما NOT خیلی قابل فهم تر از <> هست!!


USE NorthWind
SELECT ContactName , Country , Region
FROM Customers
WHERE NOT Region IS NULL AND Country <> 'usa'





فراموش نکنید که NOT یه اپراتور مقایسه ای نیست!!یعنی شما اگر کوئری تون رو اینطوری بنویسید:



Use NorthWind
SELECT * FROM Customers
WHERE Country NOT 'USA'



با ارور زیر روبرو خواهید شد!!



Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'USA'.



چرا از NOT استفاده کنیم؟!شاید در مثال بالا خیلی با قدرت NOT آشنا نشیم ولی با تلفیق NOT با یک IN میتونیم با کمترین میزان کد یک کوئری خیلی جالب بگیریم!!

توجه:NOT در MySql ساپرت نمیشه.

[Mojgan110]

SELECT يك گزينه ي CONTAIN هم ميگيره .. فكر كنم مال Full Text Search باشه ..

اون هم بگو ... مرسي.

[SherlockHolmes]

همونطور که میبینید پستها مال دو هفته پیشه!!یعنی آماده است و من تقریبا ادیتشون میکنم و ظاهرشون میکنم!!http://qsmile.com/qsimages/200.gif
بنا بر این ترجیح میدم طبق روال پیش بریم!!ولی همینو بگم که Contains تا اونجایی که من میدونم استاندارد نیست و مخصوص زبان TSQL هست!که از ایندکسر ها و کرزر های داخلی SQL SERVER استفاده میکنه!صد البته سرعتش بالاتره!!ولی رایج ترین کار برای جستجو همون WHERE LIKE هستش!!فرموش نکنید که اگه میخواید از CONTAINS استفاده کنید باید اون تیبل رو Full-text Index Table بکنید!یعنی باید تکستهای توی دیتا بیستون رو ایندکس کنید!!

[SherlockHolmes]

جلسه چهارم!! (http://my.blackmice.com/showthread.php?p=152032#post152032)http://www.tuningtalk.com/forum/images/smiles/crazy.gif
فقط چون اسکرول صفحه کمر شکن شده بود!!»خصوصا واسه خودم!!پستهای اضافی رو پاک کردم!!که در همینا از دوستان پوزش میطلبم!! http://www.smileygenerator.us/community/html/emoticons/bawl.gif

[SherlockHolmes]

جلسه پنجم!! (http://my.blackmice.com/showthread.php?p=154209#post154209)http://www.tuningtalk.com/forum/images/smiles/crazy.gif

[SherlockHolmes]

جلسه 6!! (http://my.blackmice.com/showthread.php?p=154254#post154254)http://www.tuningtalk.com/forum/images/smiles/crazy.gif

[iMohsen]

پس مابقی اینا چی شد !؟

منتظریما !؟
نکته :
من میخواستم کتاب بخرم در مورد این mysql اینجا رو دیدم بیخیال شدم ..!