آموزش جامع SQL Server بصورت تصويري و مرحله به مرحله

[Borna66]

آموزش جامع SQL Server 2005 بصورت تصويري و مرحله به مرحله

دوستان مي‌خوام در اين تاپيك در مورد SQL Server 2005 صحبت بكنم. هرچند خودم تجربه‌‌ي آنچناني ندارم. ولي ميخوام هر چي هست رو به قول معروف Share كنم! و البته به كمك شما دوستان نيز نياز مبرم دارم.

در ضمن اين تاپيك، پرسش و پاسخ در مورد SQL Server نيست. پس لطفاً در اين تاپيك سئوالي ارسال نكنيد. چون توالي پست‌هاي آموزشي رو به هم خواهد ريخت و باعث سردرگمي كاربران خواهد شد.

سرفصل‌هايي كه مورد نظرم هست به صورت زير هستش:

- مقدمه‌اي بر SQL Server 2005
- ديتابيس (Database)
- جدول (Tables)
- فهرست (Index)
- دستور SELECT
- توابع تجميعي (Aggregate Functions)
- دستورات دستكاري جداول (Tables Manipulate Commands)
- توابع از پيش ساخته شده (Built In Functions)
- متغير‌ها و دستورات شرطي T-SQL
- توابع (Functions)
- ماژول‌ها (Procedures)
- دستور Trigger
- دستور Transaction
- قفل كردن ركوردها (Locking)
- كنترل خطا (Error Handling)
- دستور Cursor

گردآورنده: طه / Borna66

گروه مديريت كل باشگاه

روزگار خوش

[Borna66]

خب! اميدوارم بدونيدSQL Server چيه! كلاً دو جور ديتابيس داريم. ديتابيس‌هاي Desktop و ديتابيس‌هاي Client/Server.

ديتابيس‌هاي معروف Desktop عبارتند از:

Access – dBase

ديتابيس‌هاي Client/Server معروف هم عبارتند از:

DB2 – Oracle - MS SQLServer –MySQL

مهمترين ويژگي ديتابيس‌هاي Client/Server اين هستش كه كاربر بطور مستقيم به ديتابيس دسترسي نداره. يعني بايد از طريق يك سرويس واسط دستور خودش رو براي اجرا به سيستم بده. در حالي كه در ديتابيس‌هاي Desktop اينگونه نيست و همين خصوصيت باعث ميشه كه ديتابيس‌هاي Client/Server نسبت به ديتابيس‌هاي Desktop امنيت بالاتري داشته باشند.

براي اينكه بفهميم SQL Server 2005 روي يك كامپيوتر نصب شده يا نه، اكثراً دنبال برنامه Management Studio مي‌گرديد. در حالي كه وجود برنامه Management Studio نه شرط لازم و نه شرط كافي براي وجود سرويس SQL Server 2005 روي يك سيستم مي‌باشد. روش درست براي اطمينان از وجود SQL Server 2005 در يك سيستم، چك كردن مسير زير است:

Control Panel>Administrative Tools>Services>SQL Server (MSSQLServer)

يعني سرويسي به نام (SQL Server (MSSQLServer بايد در ليست سرويس‌هاي ويندوز موجود باشد.

مواردي در خصوص نصب SQL Server 2005

اگر بخواهيد SQL Server 2005 را روي كامپيوتر شخصي خودتون نصب كنيد، توجه داشته باشيد كه اگر ويندوزتون XP هست نمي‌تونيد نسخه Enterprise رو روي سيستمتون نصب كنيد و بايد از نسخه‌هاي Standard و يا Developer استفاده كنيد. ضمناً ويندوز XP شما حتماً بايد SP2 به بالا باشد.
ولي اگر بخواهيد SQL Server 2005 رو براي شركت و يا سازماني نصب كنيد، بايد نسخه Enterprise رو روي Windows Server 2003 با SP1 به بالا نصب شود.
فراموش نكنيد كه قبل از نصب SQL Server 2005 حتماً DotNet Framework 2.0 (و بالاتر) روي سيستمون نصب شده باشه. براي ديدن Minimum Requirement‌ها و Windows Compatibility ورژن‌هاي مختلف SQL Server 2005 ميتونيد اين صفحه رو ببيند.

كلاً در يك كامپيوتر مي‌توان تعدد 64 سرويس SQL Server 2005 نصب كرد. ولي حداكثر 4 عدد instance را ميتوان نامگذاري و تعريف نمود.

در هنگام نصب SQL Server 2005 به يكسري اصطلاحات برميخوريد كه شايد بعضي از اونها رو ندونيد. من به اختصار اونها رو توضيح ميدم:

Reporting Service
اگر در زمان نصب SQL Server 2005 اين گزينه فعال شود، بخش گزارشگيري خودكار روي سيستم شما نصب خواهد شد. اين سرويس امكاناتي را براي ايجاد گزارش از بانك هاي اطلاعاتي مختلف در اختيارتان قرار مي دهد. گزارشهاي ايجاد شده توسط اين سرويس Web-enabled هستند و قابليت پخش روي انواع دستگاهها را دارند . شما مي توانيد اين گزارشات را با فرمتهاي مختلف Excel و Word و PDF و Html و ... ايجاد كنيد .

Analysis Service
قابليت پردازش هاي تجزيه و تحليلي آنلاين (OLAP) با سرعت بالا، تجزيه وتحليل پيشرفته براي مجموعه ديتابيس هاي پيچيده و بزرگ با استفاده از راههای متعدد ذخيره سازي اطلاعات. در حقيقت اگر بخواهيم روي ديتابيس‌هاي خيلي بزرگ گزارشگيري كنيم، پروسه ساخت گزارش خيلي كند است. براي اين منظور يك مخزن اطلاعات جنبي در نظر گرفته مي‌شود و گزارشات از روي آن تهيه مي‌شود. نتيجه گزارش بر روي يك فضاي جداگانه قرار مي‌گيرد و چنانچه اطلاعات تغيير كند، تغييرات ايجاد شده بصورت اتوماتيك بر روي گزارشات اعمال مي‌شود.
فرض كنيم كه پليس 110 يك نرم افزار در اختيار دارد كه توسط آن آمار جرائم را نگهداري مي كند. در اين آمار، پليس اطلاعات مربوط به مكان و زمان وقوع جرم و نوع جرم مثلا دزدي را نگهداري مي كند. پس از مدتي پليس مي تواند اطلاعات مربوط به دزدي در يك مكان خاص را بررسي كند. مثلا پليس در بررسي و تحليل اطلاعات خود پي مي برد كه در فلان محله خاص بين ساعت 8 الي 10 صبح هيچ مورد دزدي گزارش نشده است بنابراين مي تواند نيروهاي خود را در آن زمان خاص در آن محله خاص كاهش داده و در جاي ديگري كه امكان وقوع دزدي بيشتر است استفاده كند.

Notification Service
اين سرويس محيطي براي ايجاد برنامه هايي است كه پيامهايي را ايجاد و ارسال ميكنند. از اين سرويس مي توانيد براي ايجاد و ارسال پيامهاي شخصي و زمانبندي شده به هزاران يا ميليونها شخص يا دستگاههاي گوناگون استفاده نماييد.
مثلاً تصور کنید که تعدادی کاربر در حال اتصال به یک بانک حاوی اطلاعات مربوط به ارزش سهام در بورس هستند. از آن‌جایی که ممکن است قیمت سهام هر شرکت یا مؤسسه برای تعدادی از کاربران از اهمیت زیادی برخوردار باشد، می‌توان این سیستم را طوری تنظیم کرد تا هرگاه ارزش سهام خاصی که مورد نظر هر کاربر است تغییر کرد، به صورت اتوماتیک به وی اعلام شود. کاربر هم می‌تواند این تغییرات را برروی برنامه کاربردی خود، تلفن همراه (در قالب Windows Messenger ،(SMS و یا ایمیل به‌صورت مرتب دریافت و مشاهده کند.

Integration Services
اين سرويسها يك پلت فرم هستند كه راه حل هايي براي ايجاد يكپارچگي اطلاعات با سرعت بالا ارائه مي كنند و شامل بسته هاي نرم افزاري پردازش extract, transform, and load براي data warehousing است.

SQL Server Agent
به بيان ساده، اين سرويس مسئول دفتر SQL SERVER است. مثلاً در صورت تعريف توسط ادمين، هر شب ساعت 10 از اطلاعات كپي پشتيبان مي‌گيرد.

Authentication Mode
چنانچه گزينه‌ي Windows Authentication Mode انتخاب گردد، دسترسي كاربران و كلمه عبور آنان به
SQL Server بر اساس وجود و درستي آن‌ها در ويندوز انجام مي‌گيرد. ولي اگر Mixed Mode انتخاب شود بايد براي دسترسي به SQL Server مجدداً نام كاربري و سطح دسترسي تعريف كرد.

Collation
شمايي در SQL Server كه نحوه Sort حروف الفباي يك زبان خاص و همچنين نحوه Compare كردن حروف الفباي آن زبان را در بر مي‌گيرد.
تنظیمات collation به صورت پیش فرض از تنظیمات سیستم عامل گرفته می شود. ودر صورتی که سیستم عامل شما در بخش Regional settings دارای تنظیمات لازم فارسی باشد، نیاز به تغییر در این بخش ندارید.

گردآورنده: طه / Borna66

[Borna66]

قبل از ادامه مبحث، به يه نكته كوچيك كه يادم رفت در پست قبليم بگم، اشاره كنم. تلفظ صحيح SQL ، بصورت سيكوئل است. اين تلفظ چيزي است كه من در فيلم‌هاي آموزشي به لهجه آمريكايي شنيدم. حالا مطلب قبلي رو ادامه ميدم.

پس از نصب SQL Server 2005 حالا ميخواهيم به سرور وصل بشيم و تغييراتي را اعمال كنيم. براي اين كار از نرم‌افزاري به نام SQL Server Management Studio (كه در صورت انتخاب، همراه با سرويس SQL Server 2005 نصب مي‌شود) استفاده مي‌كنيم. همونطور كه قبلاً هم گفتم، وجود اين برنامه در كامپيوتر دليلي بر وجود سرويس SQL Server روي آن كامپيوتر نيست. چون مي‌توان اين برنامه را بدون نصب سرويس SQL Server 2005 نصب نمود و همچنين مي‌توان سرويس SQL Server 2005 را بدون نصب اين برنامه، نصب نمود.

دسترسي به اين برنامه، از مسير زير امكان‌پذير است:
Start>All Programs>Microsoft SQL Server 2005>SQL Server Management Studio

و يا اينكه در Command Prompt تايپ كنيد sqlwb

كه پس از اجرا، پنجره‌اي شبيه عكس زير را خواهيد ديد.

براي وصل شدن به يك سرور بايد نام آن سرور را بدانيد. نام سرور مي‌تواند به اشكال مختلف به شرح زير باشد:

OrionComputer : كه در حقيقت نام كامپيوتر مي‌باشد.

OrionComputer\InstanceName: همانطور كه قبلاً گفتم، مي‌توان در يك كامپيوتر، چند Instance از SQL Server داشت. InstanceName نام آن Instance در كامپيوتر OrionComputer ميباشد.

172.150.12.54 : كه همان IP سرور مي‌باشد.

172.150.12.54\InstanceName : دقيقاً همانند مورد دوم است. فقط در اينجا به جاي نام كامپيوتر، IP آن را ذكر مي‌كنيم.

(Local) : براي دسترسي به كامپيوتر Local به كار مي‌رود كه البته مي‌توان به جاي (Local) از نقطه نيز استفاده كرد (مانند شكل فوق)

(Local)\InstanceName : دقيقاً مانند مواردي كه در بالا ذكر شد.

AAA : ميشه براي هر سرويس SQL Server به اصطلاح يك Alias تعريف كرد. و من در اين مثال فرض كردم Alias سرور من، AAA هست!


پس از وارد كردن نام سرور، بايد تكليف قسمت Authentication را روشن كرد. اگر در هنگام نصب SQL Server نحوه Authentication Mode رو روي Windows Authentication گذاشته باشيد و به خودتون هم دسترسي داده باشيد، در اينجا هم Windows Authentication رو وارد كنيد و چون شناسايي شما توسط ويندوز انجام ميشه، در اينجا لازم به وارد كردن يوزرنيم و پسورد نيست. اما اگر حالت Mixed Mode رو انتخاب كرده باشيد، بايد نام كاربري و كلمه عبور را وارد كنيد. توجه داشته باشيد كه Authentication Mode رو بعد از نصب سرويس SQL Server هم ميشه بصورت دستي تغيير داد.

حالا اگر همه چيز درست باشد، شما با كليك بر روي دكمه Connect به سرور وصل ميشيد و بسته به نوع دسترسي، مي‌توانيد اطلاعات را مشاهده كرده و يا تغييراتي اعمال كنيد.

پس از ورود به سرور از قسمت Object Browser مي‌توانيد به ديتابيس‌هاي موجود دسترسي پيدا كنيد. (شكل زير). البته موارد ديگري مانند Security,Server Objects,Replication و ... وجود دارند كه ما در اينجا به آن‌ها اشاره نمي‌كنيم.

در شكل بالا، چهار Database وجود دارد: AdventureWorks و AdventureWorksDW و Northwind و Pubs. كه دو مورد اول از نمونه ديتابيس‌هاي ورژن 2005 هستند و دو مورد دوم يعني Northwind و Pubs از نمونه ديتابيس‌هاي ورژن 2000 هستند. ما در آينده و براي يادگيري، احتياج زيادي به ديتابيس Pubs داريم.

براي ساخت يك Database جديد، روي عبارت Databases كليك راست كنيد و بر روي New Database… كليك كنيد. (شكل زير)

سپس صفحه‌اي مانند شكل زير باز مي‌شود كه بايد خصوصيات ديتابيس را در آن ذكر كنيد. براي ساخت يك ديتابيس ساده تنها نياز به ذكر نام آن ديتابيس است.

سپس بايد نوع فهرست‌گذاري براي كلمات متن تعيين گردد. اگر عنوان Use Full Text Indexing فعال گردد، براي تمام كلمات ايندكس ساخته خواهد شد و در زمان جستجوي كلمات، به سرعت متني كه داراي آن كلمه باشد، پيدا شده و ارائه مي‌گردد. بديهي است بايد در اين حالت برخي دستورات، متناسب با اين حالت تغيير يابد. مثلاً اگر در حالت عادي دستور جستجوي كلمه "Test" بدين شرح باشد:

[PHP] SELECT * FROM authors WHERE address LIKE '%Test%'[/PHP]

در حالت فعال بودن Full Text Indexing، بايد دستور فوق بصورت زير نوشته شود:

[PHP] SELECT * FROM authors WHERE CONTAINS(address,'*Test*')[/PHP]

هر ديتابيس حداقل داراي دو نوع فايل به نام‌هاي زير مي‌باشد:
- Master Data File يا همان فايل mdf كه در حقيقت فايل اطلاعات اصلي ديتابيس است.
- Log Data File يا همان فايل ldf كه در حقيقت فايل History دستورات است.

در زمان تعريف هر يك از فايل‌ها مي‌توان از طريق Initial Size مقدار اوليه حجم فايل را تخمين زد. معمولاً مي‌گويند سايز اوليه Logfile برابر 35 درصد فايل mdf باشد كه البته با توجه به نوع ديتابيس ميتواند متفاوت باشد.
در قسمت AutoGrowth مي‌توان مقدار افزايش حجم فايل را پس از رسيدن به حداكثر حجم تعريف شده، تعيين كرد. ضمناً در همين قسمت مي‌توان حداكثر حجم مجاز فايل را تعيين كرده و يا اينكه حداكثر حجم مجاز را نامحدود تعريف نمود.

پس از آن مي‌توان محل ذخيره كردن فايل را در قسمت Path تعيين كرد.

براي انجام هر كاري در SQL Server يك دستور به نام T-SQL صادر و اجرا مي‌شود. مثلاً در زمان ايجاد ديتابيس جديد كه از طريق منوي New Database انجام مي‌شود، يك سري دستور TSQL اتوماتيك ايجاد شده و براي سرور ارسال مي‌گردد. براي ديدن و يا احتمالاً اصلاح اين دستورات، ميتوانيد روي دكمه Script كه در بالاي اكثر پنجره‌هاي SQL Server 2005 وجود دارد كليك كنيد. (شكل زير)

SQL Server در هر لحظه فقط قادر به اجراي يك دستور است. پس از اجراي دستور، History آن دستور در LogFile ذخيره مي‌شود. اين فايل داراي كاربردهاي زيادي است كه مختصراً به چند مورد اشاره مي‌شود:
فرض كنيد در پايان هر روز از اطلاعات Backupگيري مي‌شود. ولي در وسط روز هارد ديسك با مشكل مواجه مي‌شود. اگر فايلهاي Master و Log روي دو هاردديسك جداگانه نگهداري شده باشند، ميتوان ابتدا فايل اصلي را از Backup شب گذشته بازخواني كرد و سپس با كمك دستورات موجود در LogFile از زمان Backup تا كنون، نسبت به بازسازي فايل اصلي اقدام كرد.
يكي ديگر از كاربردهاي LogFile در Replication است. مثلاً فرض كنيد بنا به دليلي لازم است دو نسخه از فايل اصلي بر روي دو سرور جداگانه در شهرهاي مختلف نگهداري شود. به روز رساني همزمان و Online فايلهاي اصلي دو سرور نياز به پهناي باند وسيعي دارد. در حالي كه ميتوان در پايان روز، تنها دستورات اجرا شده روزانه، از روي LogFile براي سرور مقابل ارسال شده و در آنجا دستورات اجرا شده و فايل اصلي بروزرساني شود. از اين روش مي‌توان براي ساخت فايل Mirror در دو مكان مختلف استفاده كرد.
بنابراين توصيه ميشه محل نگهداري فايلهاي mdf و ldf جداگانه و بر روي دو هارد ديسك جدا باشد، مگر اينكه از تكنيك Raid براي نگهداري اطلاعات استفاده شود كه توضيح آنها در سواد بنده نمي‌گنجد!

در قسمت صفحه New Database منويي به نام Option وجود دارد (شكل زير) كه بعضي از آنها را به اختصار توضيح ميدم.

Recovery Model
اگر حالت Simple انتخاب شود فايل Log بصورت مينيموم ثبت خواهد شد و اكثر دستورات Log نخواهند شد. در اين حالت سرعت عمليات افزايش يافته ولي در صورت خرابي اطلاعات، امكان بازيابي وجود ندارد. اگر Buck-Logged انتخاب گردد، در اين حالت از تمامي دستورات Log گرفته خواهد شد. اما در دستورات Bulk بصورت مينيموم Log گرفته مي‌شود. در صورت انتخاب Full، از تمام دستورات حتي Bulkها بصورت كامل Log مي‌گيرد.

Compatibility Level
خود SQL Server 2005 بصورت اتوماتيك فايلهاي SQL Server 2000 را به 2005 تبديل مي‌كند. ولي تمام امكانات SQL Server 2005 بر روي فايلهاي تبديل شده قابل استفاده نمي‌باشد. مگر اينكه در اين قسمت، حالت SQL Server 2005(90) انتخاب گردد.

بقيه‌ي موارد را مي‌توانيد با زدن دكمه HELP در MSDN مطالعه كنيد.

گردآورنده: طه / Borna66

[Borna66]

Tableها و DataTypeها جدول (Table)

براي ساخت يك جدول بايد ابتدا Database مربوطه انتخاب شده و سپس بر روي قسمت Table كليك راست كرده و گزينه New Table انتخاب شود. (شكل زير)

سپس در سمت راست صفحه پنجره ساخت Table ظاهر مي‌شود. (شكل زير) كه من در آن سه فيلد با Datatypeهاي مختلف ساختم.

براي ساخت جدول بايد ابتدا فيلدها يا همان ستون‌هاي جدول تعريف شود. فيلد‌هاي داراي DataTypeهاي مختلفي هستند كه به اختصار اونها رو توضيح ميدم:

Binary Data
اين نوع فيلد‌ها براي نگهداري اطلاعات بصورت بايناري مانند تصاوير مناسب هستند و شامل چهار نوع به شرح زير مي‌باشد:

Binary
اين نوع فيلد‌ها، از 1 تا 8000 بايت را در خود جاي مي‌دهند.

Var Binary
اين نوع فيلد‌ها هم از 1 تا 8000 بايت را در خود جاي مي‌دهند. (متغير)

Image
اين نوع فيلد‌ها از 1 تا حداكثر 2 گيگابايت را مي‌توانند ذخيره كنند. فرق اين نوع داده‌ها با دو نوع قبلي اين است كه در دو نوع قبلي، اطلاعات در خود ركورد ثبت مي‌شوند ولي در اين نوع داده‌ها، اطلاعات در يك Page ذخيره مي‌شود و به جايش در ركورد، يك پوينتر 16 بايتي ذخيره مي‌شود. اين نوع فيلد‌ها در SQL 10 حذف شده و به جايش بايد از VarBinary استفاده كرد.

VarBinary(Max)
اين نوع Datatype در SQL 2005 معرفي شده و تقريباً همانند داده‌هاي Image هستند.

Character Data
اين نوع فيلد براي نگهداري عبارات و يا حروف ASCII مي‌باشد. در اين نوع فيلد‌ها، براي نگهداري هر حرف، يك بايت اشغال مي‌شود و لذا نياز به Collation براي تعيين زبان اطلاعات مي‌باشد. اين نوع فيلد‌ها هم چهار نوع هستند:

Char
اين نوع فيلد‌ها، اطلاعات متني با طول ثابت از 1 تا حداكثر 8000 حرف را در خود ذخيره مي‌كنند.

VarChar
اين نوع فيلد‌ها اطلاعات متني با طول متغير از 1 تا حداكثر 8000 حرف را در خود ذخيره مي‌كنند. فرق بين Char و VarChar در اين است كه در Char، طول رشته ثابت است. يعني اگر يك فيلد را از نوعChar(20) معرفي كرده و در آن كلمه Orion را قرار دهيم، عين 20 حرف استفاده خواهد شد. يعني 5 كاراكتر اول را كلمه مربوطه اشغال كرده و 15 كاراكتر باقي‌مانده، Blank خواهند بود. اما در VarChar اينگونه نيست.

Text
در اين نوع داده‌ها، اطلاعات از 1 تا 2 مگا حرف ذخيره مي‌شود. اين نوع داده همانند Image و VarBinary(MAX)در خود ركورد ذخيره نمي‌شوند. بلكه توسط يك پوينتر به جاي ديگري اشاره مي‌كنند. اين نوع داده در SQL 10 حذف شده و بجاي آن‌ها ازVarChar(MAX) استفاده مي‌شود.

VarChar(Max)
در اين نوع داده‌ها، اطلاعات از 1 تا 2 مگا حرف ذخيره مي‌شود و مكانيزم آن هم بصورت پوينتري مي‌باشد.

Unicode Data

اين نوع فيلد‌ها براي نگهداري متون Unicode بوده و براي نگهداري هر حرف، از دو بايت استفاده مي‌شود. پس مسلماً نسبت به نوع داده‌هاي كاراكتري، حافظه بيشتري را به خود اختصاص مي‌دهد و در ضمن كمي هم كندتر است. اين نوع فيلد‌ها، احتياج به Collation ندارند.

nChar
در اين نوع داده، اطلاعات از 1 تا حداكثر 4000 حرف با طول ثابت ذخيره مي‌شود.

nVarChar
در اين نوع داده، اطلاعات از 1 تا حداكثر 4000 بايت با طول متغير ذخيره مي‌شود.

nText
در اين نوع داده، از 1 تا 1 مگا حرف ذخيره مي‌شود. مكانيزم آن هم بصورت Pointer مي‌باشد.

nVarChar(MAX)
در اين نوع داده از 1 تا 1 مگا حرف ذخيره مي‌شود. مكانيزم آن هم بصورت Pointer مي‌باشد.

Integer Data
اين نوع فيلد براي نگهداري اعداد صحيح و بدون اعشار استفاده مي‌گردد و داراي 4 نوع به شرح زير است. در ضمن اين نوع فيلد‌ها رتبه يك سرعت در نوع فيلد‌هاي عددي را دارد.

TinyInt
يك بايت را اشغال مي‌كند و مي‌تواند از 0 تا 255 را در خود ذخيره كند.

SmallInt
يك عدد دو بايتي است و مي‌تواند از 32767 منفي تا 32767 مثبت را در خود ذخيره كند.

Int
يك عدد چهار بايتي است كه مي‌تواند اعداد بين مثبت و منفي 2 ميليارد را در خود ذخيره كند.

BigInt
يك عدد 8 بايتي است كه مي‌تواند اعداد بين مثبت و منفي 4 ميليارد را در خود ذخيره كند.

Money Data
اين نوع فيلد براي نگهداري اعداد غير صحيح با تعداد ارقام اعشار ثابت 4 رقمي استفاده مي‌شود. اين نوع فيلد‌ها، داراي رتبه دوم سرعت در بين انواع داده‌ي عددي است.

SmallMoney
يك عدد 4 بايتي است كه مي‌تواند 6 رقم صحيح و 4 رقم اعشار را در خود ذخيره كند.

Money
يك عدد 8 بايتي است كه مي‌تواند 15 رقم صحيح و 4 رقم اعشار را در خود ذخيره كند.

Approximate Data
اين نوع فيلد‌ها براي نگهداري اعداد غير صحيح با تعداد ارقام اعشار متغير و يا تخمـينـي استفاده مي‌شود. اين نوع داده‌ها رتبه سوم سرعت در بين انواع داده‌هاي عددي دارند و استفاده از آنها به دليل كندي، توصيه نمي‌گردد. مگر در مواقع لزوم.

Real
يك عدد 4 بايتي است كه اعداد بصورت تواني از 10 نگهداري مي‌شوند. (تعداد اعشار مشخص نيست)

Float
يك عدد 8 بايتي كه اعداد بصورت تواني از 10 نگهداري مي‌شوند. (تعداد اعشار مشخص نيست)

Decimal
اين نوع فيلد براي نگهداري اعداد اعشاري با تعداد اعشار مشخص استفاده مي‌گردد. اين نوع فيلد‌ها بسيار كند بوده و استفاده از آنها توصيه نمي‌گردد. فرمت اينگونه داده‌ها به شكل زير است:

[PHP]Decimal (Precision,Scale)[/PHP]

كه در آن Precision به معناي تعداد كل رقم‌هاي عدد و Scale تعداد ارقام اعشار را مشخص مي‌كند. مثلاً اگر فيلدري بصورتDeciaml(6,2) تعريف شود، حداكثر آن برابر 9999.99 مي‌باشد.

Date/Time
اين نوع فيلد‌ها براي نگهداري تاريخ ميلادي و ساعت استفاده مي‌شود و براي تاريخ شمسي كاربرد ندارد.

SmallDateTime
اين نوع فيلد، 4 بايتي است و از سال 1900 تا 2079 را با دقت هزارم ثانيه ذخيره مي‌كند.

DateTime
اين نوع فيلد، 8 بايتي است و از سال 1700 تا 9999 را با دقت هزارم ثانيه ذخيره مي‌كند.


Other DataTypes

برخي از انواع فيلد‌هاي خاص را در اين قسمت به اختصار توضيح ميدم:

Bit
يك فيلد دو بيتي است و مي‌تواند 0 و 1 و Null را ذخيره كند.كاربرد آن در زمان‌هايي است كه دو حالت وجود داشته باشد. مانند جنسيت زن و مرد.

TimeStamp
در اين فيلد 8 بايتي، تايم لحظه‌اي اجراي دستور نگهداري مي‌شود و كاربرد آن كنترل بروزرساني همزمان (Concurrency) اطلاعات توسط چند كاربر است. البته در تعداد ركوردهاي پايين به كار نمي‌آيد و بيشتر زماني مورد نياز است كه تعداد ركوردها خيلي زياد باشد مثلاً 100 ميليون ركورد!

UniqueIdentifier
اين فيلد 16 بايتي، به ما كدي Unique يا تك مي‌دهد كه به اصطلاح GUID مي‌گويند. يكي از كاربردهاي آن در Replication است.

SQL_Variant
اين نوع فيلد براي نگهداري انواع داده استفاده مي‌شود و نوع آن با توجه به اولين مقداري كه در آن قرار مي‌گيرد تعيين خواهد شد. چون نوع و حجم فيلد مشخص نيست، لذا تنها يك اشاره‌گر 16 بايتي در آن قرار گرفته و داده اصلي در فايل جداگانه نگهداري مي‌شود. استفاده از اين نوع فيلد، توصيه نمي‌گردد.

‍Cursor
اين فيلد مربوط به كنترل Cursor است كه در آينده توضيح ميدم.

Table
اين نوع فيلد را هم در زمان آموزش Functionها خواهيد آموخت

XML
اين فيلد بيشتر براي انتقال اطلاعات و دستورات تحت web استفاده مي‌شود و شامل انواع MetaDataهاي مختلف است. اين فيلد در SQL 2005 معرفي گرديد.


ادامه مبحث Tableها رو درپست بعد ياد مي‌گيريد!

گردآورنده: طه / Borna66

[Borna66]

در هنگام تعريف فيلد‌هاي (ستون‌هاي) يك جدول، صفحه‌اي با عنوان Column Properties در قسمت پايين صفحه ظاهر مي‌شود (شكل زير) كه از طريق اين فرم، ميشه مشخصات بيشتري از اون فيلد خاص رو تعريف كرد كه من در اينجا پر كاربرد‌ترين‌ها رو توضيح ميدم.

گزينه Allow Nulls كه البته در كنار Data Type هم مي‌توان آنرا تعيين كرد، مشخص مي‌كند كه آيا آن فيلد خاص مي‌تواند مقدار Null (يا همان هيچي!) را بپذيرد يا خير.

يكي ديگر از اين موارد، مشخص كردن مقدار اوليه‌ي فيلد مورد نظر هستش. براي اين كار در مقابل عبارت Default Value or Binding مقدار اوليه رو وارد مي‌كنيد. مشخص كردن مقدار اوليه به اين درد مي‌خورد كه مقدار فيلد ما مثلاً در هنگام Insert يك ركورد جديد به جدول، در صورت مشخص نبودن مقدار، Null نشود.

اگر با بانك اطلاعاتي Access آشنا باشيد، يكي از انواع داده‌ها، Auto Number بود كه در DataTypeهاي SQLServer وجود نداشت. براي پياده‌سازي AutoNumber به اين صورت عمل مي‌كنيم: اگر روي علامت مثبت كنار Identity Specification كليك كنيم، سه گزينه‌ي ديگر نمايش داده خواهند شد. (شكل زير)

اگر براي فيلد‌هاي عددي غير اعشاري، Is Identity برابر Yes شود، در اينصورت اين فيلد بصورت اتوماتيك در هنگام درج ركورد‌هاي جديد، اضافه خواهد شد. مقدار شروع در قسمت Identity Seed و ميزان افزايش در هر دفعه، در قسمت Identity Increment قرار خواهد گرفت. ضمناً براي استفاده از اين امكان، بايد در قسمت Default Value مقدار پيش‌فرض تعيين نگردد و حتماً Allow Null هم نباشد.

چنانچه فيلد از نوع Unicode مثل nchar استفاده مي‌شود، بايد كد استاندارد زبان آن مشخص شود. براي اين كار كليد انتخاب Collation را زده و از دو حالت SQL Collation و Windows Collation بهتر است Windows Collation را انتخاب كرده و بعد زبان Arabic و حالت Dictionary Sort انتخاب شود (شكل زير).

اگر ما 2 فيلد a و b از نوع int تعريف كنيم و فيلد سومي به نام c را هم تعريف كنيم و سپس بر روي دكمه مثبت Computed Column Specification كليك كنيم، ميتوانيم در قسمت Formula عبارت a+b را وارد كنيم (شكل زير). با اين كار مقدار C هميشه برابر با a+b خواهد بود. اگر گزينه Is Persisted برابر No باشد، اصلاً فضايي براي فيلد C در جدول در نظر گرفته نخواهد شد و همواره اين مقدار محاسبه مي‌گردد. اما اگر Is Persisted براي Yes باشد، مقدار C در جدول ذخيره مي‌شود.

حالا براي ذخيره جدول، كافيه فرم فعلي رو ببنديد. سپس ازتون سئوال ميكنه كه آيا ميخواهيد جدول ذخيره بشه؟ و اگر شما Yes رو بزنيد، از شما اسم جدول پرسيده مي‌شود. اسم جدول را وارد كرده و Ok را بزنيد.

قبلاً هم به اين مورد اشاره كردم كه SQL براي انجام هر كاري يك دستور T-SQL را توليد و اجرا مي‌كند. مثلاً براي ساخت يك جدول (يا هر چيز ديگر) مي‌توان مستقيم از دستورات SQL استفاده كرد. براي اينكار در محيط Management Studio روي دكمه New Query كليك كرده (سمت چپ، بالا) و دستورات را وارد كنيد و سپس Execute را كليك كنيد.
اما حالا كه شما جدول رو بصورت Visual ساختيد، باز هم مي‌تونيد ببينيد كه خود SQLServer از چه دستوراتي براي ساخت جدول استفاده كرده. براي اين كار پس از اينكه جدول رو ذخيره كرديد، در قسمت Object Browser روي آن كليك راست كرده و مسير زير را انتخاب كنيد:

Script Table as > CREATE to > New Query Editor Window

پس از آن، كدي كه خود SQL Server براي ساخت جدول مورد نظر، توليد كرده را مي‌توانيد ديده و يا ذخيره كنيد.

در پست بعدي در مورد Constraintها صحبت خواهم كرد.


گردآورنده: طه / Borna66

[Borna66]

محدود كننده‌هاي فيلد (Constraint)

بايد براي بعضي از فيلد‌ها، بر اساس نياز، تعاريف و محدود كننده‌هاي خاصي در نظر گرفته شود كه در اين قسمت، به شرح آنها مي‌پردازم.

Unique Constraint
بعضي از فيلدها، كليد نيستند (كليدها را كمي پايين‌تر توضيح ميدم)، اما بايد غير تكراري باشند. مثلاً فرض كنيد آدرس ايميل، جزو كليد نيست اما بايد Unique يا واحد باشد. براي اين كار از Unique Indexها استفاده مي‌كنيم كه در درس‌هاي بعدي به اونها مي‌پردازم.

Default Constraint
وقتي در هنگام ساخت جدول و تعيين نوع فيلدها، مقدار Default Value به يك فيلد بدهيم، در حقيقت داريم نوعي محدوديت اعمال مي‌كنيم. محدوديت ما اين است كه اگر كاربر در هنگام درج يا به روزرساني مقدار آن فيلد را وارد نكرد، مقدار Default Value به جاي آن قرار بگيرد.

Null ability Constraint
اگر در هنگام ساخت جدول و تعيين نوع فيلدها، مقدار Allow Null آن را غير فعال كنيم، در حقيقت به SQL Server مي‌گوييم كه اين مقدار نمي‌تواند مقدار Null بگيرد. پس در هنگام درج يا به روزرساني، اگر كاربر مقدار فلان فيلد خاص را وارد نكرد، جلوي انجام عمليات گرفته مي‌شود.

Primary Key Constraint
تعريف فيلد كليد اصلي، يكي از مهمترين محدوديت‌ها مي‌باشد. فيلد كليد، مقدار تكراري قبول نمي‌كند و بيشتر براي تفكيك و جستجوي ركورد‌ها مورد استفاده قرار مي‌گيرد.
براي تعريف يكي از فيلد‌ها به عنوان كليد اصلي، بايد در زمان ايجاد جدول، بر روي نام فيلد كليك راست كرده و گزينه Set Primary Key زده شود. بعد از اين كار، علامت كليد در كنار فيلد مورد نظر ظاهر مي‌شود. ضمناً اين امكان وجود دارد كه همزمان دو يا چند فيلد بعنوان كليد اصلي تعريف شوند. براي اينكار دقيقاً مثل وقتي كه مي‌خواهيد در ويندوز دو يا چند فايل يا فولدر را انتخاب كنيد، كليد Ctrl را نگه داشته و روي فيلد‌هاي مورد نظرتون كليك كنيد و سپس روي يكي از آن‌ها كليك راست كرده و گزينه Set Primary Key را انتخاب كنيد.

ضمناً توجه داشته باشيد كه فيلد كليد اصلي هيچ وقت Null نمي‌پذيرد. پس ستون Allow Nulls را براي اين فيلد علامت نزنيد.

Foreign Key Constraint
براي ارتباط بين جداول استفاده مي‌شود و در اصل Relational Integrity را فراهم مي‌كند. به عبارت ساده‌تر، كليد خارجي همان مقدار كليد اصلي از جدول ديگر است. توجه داشته باشيد كه مقدار يك كليد خارجي ميتواند تكراري باشد. به عنوان مثال دو جدول Titles و Publishers كه يكي مشخصات كتاب‌ها و ديگري مشخصات ناشران را ذخيره مي‌كند را فرض كنيد. در جدول Titles (كتاب‌ها) يك فيلد به نام pub_id وجود دارد كه در اصل كليد خارجي براي فيلد pub_id موجود در جدول Publishers مي‌باشد. وجود اين كليد به اين خاطر است كه ما بتوانيم ناشر يك كتاب را پيدا كنيم.

فيلد‌هاي كليد خارجي، سه نوع محدوديت را ايجاد مي‌كنند:
-اجازه حذف ركوردي از جدول پدر نداريم كه فرزندي داشته باشد.
-اجازه insert ركورد در جدول فرزند با كد پدري كه در جدول پدر موجود نيست را نداريم.
-مقدار كليد پدري را كه داراي فرزند هست را نمي‌توانيم تغيير دهيم

براي ساختن كليد خارجي، پس از ساخت Tableها، بر روي Table فرزند و در قسمت Keys كليك راست كرده و گزينه New Foreign Key انتخاب گردد.

سپس فرمي همانند زير ظاهر مي‌شود:

حالا قبل از اينكه مشخص كنيم، كليد خارجي ما از كدام فيلد و از كدام جدول به وجود مي‌آيد، به بعضي از مواردي كه در اين فرم هست، مي‌پردازم.
اگر Enforce For Replication فعال باشد، تمام محدوديت‌هايي كه بالاتر به آن‌ها اشاره كردم، در زمان Replication هم رعايت خواهد شد.
چنانچه گزينه Enforce Foreign Key Constraint فعال باشد، محدوديت جلوگيري از اضافه شدن ركورد به جدول فرزند در صورتي كه مقدار آن در جدول پدر نباشد، رعايت مي‌گردد. در صورت غير فعال بودن هم، اين محدوديت به حساب نخواهد آمد.
در قسمت INSERT and UPDATE Specification ميتوان قوانين و محدوديت‌هاي حذف و اضافه و بروزرساني را تغيير داد. به عنوان مثال، Delete Rules داراي چهار حالت زير است:
1-اگر No Action باشد، اجازه حذف ركورد پدر را در صورتي كه فرزندي از آن پدر موجود باشد، نمي‌دهد.
2-اگر Cascade باشد، در اين حالت، مي‌توان ركورد پدري را حذف نمود. اما بصورت اتوماتيك تمام فرزندهاي آن هم حذف خواهند شد.
3-اگر Set Null باشد، باز هم اجازه حذف ركورد پدر داده مي‌شود، اما فيلد مرتبط در جدول فرزند، در صورتي كه داراي شرط Allow Null باشد، Null خواهد شد.
4-اگر Set Default باشد، باز هم اجازه حذف ركورد پدر داده مي‌شود. اما فيلد مرتبط در جدول فرزند، در صورتي كه مقدار Default داشته باشد، برابر مقدار پيش فرض خواهد شد.


حالا بايد مشخص كنيم كه پدر از كدام جدول و از كدام فيلد خواهد آمد. براي اين كار بر روي دكمه كنار گزينه Tables and Columns Specification كليك كنيد. با اين كار فرمي همانند شكل زير ظاهر مي‌شود:

كه در اين مثال، بايد اطلاعات به صورت زير تكميل گردد:

بعد از اينكه اطلاعات را تكميل گرديد، فرم‌ها را ببنيد تا از شما در مورد ذخيره كردن تغييرات سئوال بپرسد. سپس روي گزينه Keys در قسمت Object Browser كليك راست كرده و گزينه Refresh را كليك كنيد تا آخرين تغييرات را براي شما نمايش دهد. اگر كارها درست پيش رفته باشد بايد كليد خارجي بصورت زير ساخته شده باشد:

Check Constraint

كاربردش براي وقتي است كه بخواهيم براي يك فيلد، يك سري محدوديت خاص تعريف كنيم. مثلاً بگوييم فلان فيلد عددي، بايد حتماً از 10 بزرگ‌تر باشد و در غير اينصورت در هنگام ورود اطلاعات، كاربر با خطا مواجه گردد.
براي به وجود آوردن يك Check Constraint روي علامت Plus جدول مورد نظرتون كليك كنيد و روي گزينه Constraints كليك راست كرده و گزينه New Constraint را انتخاب كنيد. (شكل زير)

سپس فرمي مانند زير باز مي‌شود.

در اينجا و در قسمت Expression، بايد عبارت محدود كننده رو وارد كنيد. مثلاً در اين مثال ميگيم كه فيلد Price بزرگتر از 10 باشه. پس بايد در قسمت Expression عبارت Price>10 رو وارد كرده و دكمه Close رو بزنيد و تمام فرم‌هاي باز غير از Object Browser را ببنديد تا از ذخيره شدن تغييرات اطمينان حاصل بكنيد. حالا اگر در Object Browser، بر روي Constraints كليك راست كرده و گزينه Refresh را انتخاب كنيد، شكلي همانند زير را خواهيد ديد كه نشان دهنده‌ي اين است كه Constraint ما ساخته شده است.

گردآورنده: طه / Borna66

[Borna66]

ايندكس‌ها - (Index) ها

ما ميتونيم جدول‌ها رو با توجه به فيلد يا فيلد‌هاي كليد اصلي مرتب كنيم. ولي معمولاً براي جستجو و گزارش‌گيري به جدول مرتب شده بر اساس فيلد‌هاي مختلف كه ممكنه كليد هم نباشند احتياج داريم. براي اين كار از Indexها استفاده مي‌كنند.

در زمان ساخت Indexها بايد به تعريف‌هاي زير توجه كرد:

Clustered VS None Clustered Index
چنانچه ايندكس در داخل فايل اصلي جدول ساخته بشه، بهش Clustered Index ميگن. براي هر جدول فقط ميتونيم يك عدد Clustered Index داشته باشيم. ايندكسهاي Non Clustered هم بصورت جداگانه ذخيره مي‌شن. براي هر جدول ميتونيم 249 تا ايندكس از اين مدل داشته باشيم. اگر جدول ما كليد اصلي داشته باشه، بصورت اتوماتيك يك ايندكس از نوع Clustered براي فيلد(هاي) كليد ساخته مي‌شود.

Ascending/Descending
ترتيب ايندكس‌ها را ميتوان صعودي يا نزولي قرار داد. اگر نوع ترتيب مشخص نباشه، بصورت پيش فرض حالت صعودي يا Ascending در نظر گرفته مي‌شود.

Unique
ميتوان مشخص كرد كه ايندكس Unique باشد يا خير. در صورت Unique بودن ايندكس، در حقيقت Unique Constraint هم در جدول اِعمال مي‌شود.

Single Column vs Multi Column Index
مي‌توانيم يك ايندكس رو بر اساس چند فيلد درست كنيم. اگر جدول بر اساس يك فيلد مرتب باشد، به آن Single Column ميگن و اگر بر اساس چند فيلد باشه، Multi Column ميگن.


براي ساخت يك ايندكس، ابتدا در قسمت Object Browser و در جدول مورد نظر، به قسمت Indexes بريد. سپس روي گزينه Indexes كليك راست كرده و گزينه New Index را انتخاب كنيد.

پنجره‌اي مانند شكل زير نمايش داده خواهد شد.

در اين صفحه حداقل بايد نام ايندكس، نوع آن و اينكه بر روي كدام فيلد يا فيلد‌ها ساخته مي‌شود، مشخص شود. نوع ايندكس مي‌تواند Clustered يا Nonclustered و يا Primary XML باشد. پس از آن مي‌توان غير تكراري بودن ايندكس را با تيك زدن گزينه Unique تعريف كرد. در ادامه با زدن دكمه Add فيلد يا فيلد‌هاي ايندكس را مشخص مي‌كنيم. در صورتي كه ايندكس ما از چند فيلد تشكيل شده باشد، مي‌توانيم ترتيب آنها را با كليد‌هاي Move Up و Move Down مشخص كنيم.

برخي موارد موجود در قسمت Option:

Ignore Duplicate Values: تنها در صورتي اين آيتم قابل انتخاب شدن است كه ايندكس ما Unique باشد. فرض كنيد بخواهيد تعدادي ركورد را بصورت Bulk به ديتابيس اضافه كنيم. اگر اين گزينه غير فعال باشد، سيستم با مشاهده اولين ركورد تكراري، عمليات را متوقف كرده و پيغام خطا مي‌دهد. اما اگر اين گزينه فعال باشد، از ثبت ركورد تكراري جلوگيري شده ولي عمليات متوقف نشده و مابقي ركوردها به ديتابيس اضافه مي‌شوند.

Automatically Recomputed Statistics: هر دستوري كه در براي SQL ارسال مي‌شود، يك نرم‌افزار به نام Optimizer روش اجراي دستور را با توجه به آمار و اطلاعات موجود، تحليل و بررسي كرده و سپس يك Execution Plan براي SQL Server ارسال مي‌كند. چنانچه اين ويژگي فعال باشد، SQL Server در زمان ساخت ايندكس، آمار و اطلاعات مورد نياز Optimizer را خواهد ساخت و در غير اينصورت بايد بصورت دستي، آمار مورد نظر ساخته شود.

Use row lock when accessing the index: در صورت انتخاب اين گزينه، در زمان ساخت ايندكس، تنها همان ركوردي كه در حال استفاده براي ايندكس سازي است، قفل مي‌شود و كاربران اجازه كار با ساير ركوردها را دارند.

User page lock when accessing the index: مانند قسمت قبل اگر اين ويژگي فعال باشد، تنها همان Page كه ايندكس در حال كار با آن است قفل شده و كاربران مي‌توانند با ساير Pageها كار كنند.
در صورتي كه هيچ كدام از دو گزينه فوق انتخاب نشوند، Table Lock انجام مي‌شود.

Set Fill Factor: يك مقدار درصدي مي‌باشد. اگر 100 باشد، Pageهاي جدول كاملاً پر مي‌شوند. بنابراين احتمال بروز Page Split در Table افزايش مي‌يابد كه باعث كند شدن عمليات insert و update و افزايش سرعت select مي‌شود. اما اگر مثلاً 1 باشد، بيشترين فضاي خالي كه توسط SQL محاسبه مي‌شود به Pageهاي Table اختصاص داده مي‌شود و احتمال بروز Page Split كاهش مي‌يابد و سرعت insert و update افزايش يافته، اما چون داده‌ها در Pageهاي بيشتري ذخيره مي‌شوند، سرعت جستجو كم مي‌شود. در دو زمان بهتر است كه اين مقدار برابر با 100 باشد: يكي زماني كه اطلاعات ما فقط خواندني باشند يعني فقط زماني كه از اطلاعات گزارش‌گيري كنيم و ديگر زماني كه كليد اصلي ما AutoIncrement باشد.

Include Columns
طول فيلد‌هاي شركت كننده در ايندكس حداكثر مي‌تواند 900 بايت باشد. پس اگر دو فيلد از نوع char به طول 500 داشته باشيم، نمي‌توانيم ايندكسي رو روي اين دو فيلد بسازيم. پس مجبوريم فيلد‌هاي مازاد رو با روش ديگري به ايندكس اضافه كنيم. با انتخاب اين گزينه، صفحه‌اي براي تعريف كليد‌هاي خارج از محدوده ايندكس ظاهر خواهد شد.



عمليات‌هاي مربوط به Index
اگر بر روي يك ايندكس كليك راست بكنيم، منويي مانند شكل زير ظاهر مي‌شود:

Rebuild: اين گزينه براي ساخت مجدد ايندكس و حذف فضاهاي خالي و سازماندهي مجدد ايندكس به كار مي‌رود. در صورت بالا بودن آمار تغييرات جدول، استفاده از اين امكان هر چند روز توصيه مي‌شود.

Reorganize: اين آيتم، ايندكس را از نو نمي‌سازد ولي تا حد امكان فضاهاي خالي را از بين برده و ايندكس را نسبتاً سازماندهي مي‌كند. اين روش نسبت به روش قبل از سرعت بالاتري برخوردار است.

Disable: براي غير فعال كردن ايندكس به كار مي‌رود.


نكته:

معمولاً دو نوع سيستم داريم:
-سيستم‌هاي OLTP: در اين نوع سيستم‌ها، تراكنش‌هاي زيادي انجام مي‌شود. منظور از تراكنش، عمليات Update,Delete,Insert مي‌باشد. در اين نوع سيستم‌ها هرچه تعداد ايندكس‌ها بيشتر باشد، كارايي و سرعت كاهش خواهد يافت. چون بايد به ازاي هر تراكنش، ايندكس‌ها هم تغيير پيدا كنند.
-سيستم‌هاي OLAP: در اين سيستمها، عمليات گزارشگيري بيشتر است و تراكنش كم انجام مي‌شود و يا اصلاً انجام نمي‌شود. در اين سيستم‌ها افزايش تعداد ايندكس‌ها باعث افزايش سرعت و كارايي مي‌شود.
بعضي از سيستم‌ها هم تركيبي هستند كه بايد در انتخاب نوع و تعداد ايندكسها تعادل برقرار شود.


گردآورنده: طه / Borna66

[Borna66]

اين ديتابيس Pubs اصلاً چيه؟! خب اول يه كمي با ديتابيس Pubs آشنا بشيم و ببينيم كه اصلاً چي هست. همونطور كه گفتم اين ديتابيس يكي از ديتابيس‌هاي نمونه ارائه شده در SQL 2000 هستش و چون مثال خوبيه من از اين ديتابيس براي ادامه مباحث استفاده مي‌كنم.
اين ديتابيس يك ديتابيس ساده جهت نگهداري اطلاعات كتاب‌ها، نويسندگان، ناشران و ... مي‌باشد
دياگرام زير، جدول‌هاي اصلي اين ديتابيس و رابطه‌هاي بين آن‌ها را نمايش مي‌دهد.

جدول Publishers
در اين جدول مشخصات ناشرين كتاب‌ها ذخيره مي‌شود. كليد اصلي آن Pub_id مي‌باشد و فيلدهاي Pub_name و City و State و Country به ترتيب نام ناشر، شهر ناشر، ايالت ناشر و كشور ناشر را نشان مي‌دهد. اين جدول يك ارتباط يك به چند با جدول Titles دارد.

جدول Titles
در اين جدول مشخصات كتاب‌ها نگهداري مي‌شود. كليد اصلي آن Title_id مي‌باشد و فيلد‌هايي كه ما با آنها سر و كار خواهيم داشت عبارتند از title و type و pub_id و price كه به ترتيب، نام كتاب، نوع كتاب، شماره ناشر و قيمت كتاب مي‌باشد.

جدول Stores
اين جدول مشخصات كتاب‌فروشي‌ها را دربر دارد. به دليل اينكه يك كتاب‌فروشي ارتباطش با جدول Titles يك ارتباط چند به چند است، پس مي‌بايست يك جدول واسط براي آنها در نظر گرفت كه اين جدول واسط همان جدول Sales است.

جدول Sales
اين جدول، اطلاعات فروش كتاب‌ها را در بر مي‌گيرد. كليد اصلي آن متشكل شده از كليد اصلي جدول titles و جدول Stores و يك فيلد ديگر به نام ord_num است كه همان شماره سفارش مي‌باشد. ديگر فيلد‌ مورد نظر ما qty است كه تعداد كتاب فروخته شده در هر فروش را نشان مي‌دهد.

جدول Authors
در اين جدول اطلاعات نويسندگان ذخيره مي‌شود. كليد اصلي آن au_id مي‌باشد و ديگر فيلدهاي آن هم از روي نامشان مشخص هستند.

جدول TitleAuthor
به دليل اينكه جدول Author با جدول Titles يك ارتباط چند به چند دارد، (يعني يك نويسنده مي‌تواند يك يا چند كتاب نوشته باشد و يك كتاب مي‌تواند حاصل كار يك يا چند نويسنده باشد) مي‌‌بايست يك جدول واسط براي آنها در نظر بگيريم كه جدول TitleAuthor همين جدول واسط است و كليد اصلي خود را از دو جدول Titles و Authors ميگيرد.


خب از پست بعدي SELECT رو شروع ميكنم...

گردآورنده: طه / Borna66

[Borna66]

دستور SELECT

فرم كلي دستور select بصورت زير هستش:

[SELECT field_1[,field_n]
 FROM table_1 
 [INNER [or LEFT or RIGHT or FULL or CROSS] 
JOIN ON table_2]  [WHERE condition] 
 [GROUP BY field_1 [,field_n]]  
[HAVING condition]  [ORDER BY field_1 [, field_n]]

[

اين دستور فقط خروجي برمي‌گرداند كه مي‌تواند صفر، يك و يا چند ركورد، را برگرداند.
من براي نمونه‌ها و مثال‌ها از ديتابيس Pubs كه قبلاً در موردش صحبت كردم، استفاده مي‌كنم.
حالا به موارد زير توجه كنيد:

SELECT * FROM titles

اين دستور، تمامي فيلدها و ركوردهاي موجود در جدول titles را نمايش خواهد داد.

=======================

SELECT title,price FROM titles

اين دستور، نام و قيمت تمام كتاب‌هاي موجود در جدول titles را نمايش خواهد داد.

=======================

SELECT titles.title,titles.price FROM titles

نتيجه اين دستور، نتيجه‌ي همون دستور قبليه. اما گاهي وقت‌ها پيش مياد كه نام فيلد‌هايي از دو جدول متفاوت يكي باشند. اينجوري ما فيلد رو متمايز ميكنيم و وقتي ميگيم titles.price يعني فيلد price از جدول titles

=======================

SELECT title AS Field1 ,price AS Field2 FROM titles

نتيجه‌ي اجراي اين دستور هم مثل دستور بالاست. منتها ما اينجا براي هر فيلد، يك Alias تعريف كرديم. يعني در خروجي اين دستور ديگه خبري از title و price نيست. بلكه عناوين Field1 و Field2 جايگزين آنها شدند.

=======================

SELECT * FROM titles WHERE price>10

اين دستور، تمامي كتاب‌هايي كه قيمتشون از 10 بيشتر هست رو ليست ميكنه.

=======================

SELECT * FROM titles WHERE price>10 AND type='Psychology'

اين دستور، تمامي كتاب‌هاي روان‌شناسي كه داراي قيمت بالاي 10 (دلار) هستند، را نمايش مي‌دهد.

=======================

SELECT * FROM titles WHERE price BETWEEN 10 AND 20

اين دستور تمامي كتاب‌هايي كه قيمت آن‌ها بين 10 و 20 دلار باشند را ليست مي‌كند. البته مي‌توان به جاي عملگر between از تركيب عملگرهاي بزرگتر و كوچكتر هم استفاده كرد.

=======================

SELECT * FROM titles ORDER BY price

اين دستور، تمامي كتاب‌ها رو بر اساس قيمت ليست مي‌كند. البته بصورت پيش‌فرض، بصورت صعودي (از كوچك به بزرگ) مرتب مي‌كنه. اگر خودمون بخواهيم مشخص كنيم كه نحوه مرتب كردن صعودي باشد يا نزولي بايد از ASC يا DESC استفاده كرد.
يعني اگر دستور بالا رو بصورت زير بنويسيم، ركوردها بصورت صعودي بر اساس فيلد قيمت مرتب ميشن:

SELECT * FROM titles ORDER BY price ASC

اما اگر بخوايم نزولي مرتب بشن، بايد دستور رو به شكل زير تغيير بديم:

SELECT * FROM titles ORDER BY price DESC

خب. يه چيز جالب اينه كه شما الان نميدونيد اين دستورها رو بايد كجا وارد كنيد! پس بذاريد قبل از ادامه‌ي مبحث، يه توضيحي در اين مورد بدم.
وقتي وارد محيط Management Studio ميشيد، بالا سمت چپ دكمه‌اي به نام New Query وجود داره كه بايد اون رو كليك كنيد. (شكل زير)

نكته‌ي مهم بعدي اينه كه بايد ديتابيس مورد نظرمون رو مشخص كنيم. در همون ابتدا معمولاً ديتابيس master بصورت انتخاب شده هست و اگر به همين صورت دستورات بالا رو وارد كنيد با خطا مواجه ميشيد. پس شما بايد در اينجا ديتابيس Pubs رو انتخاب كنيد. (شكل زير)

حالا ميتونيد از محيطي كه در اختيارتون گذاشته شده استفاده كرده و دستورات خودتون رو وارد كنيد. (شكل زير)

براي اجراي دستورات بايد از دكمه Execute يا كليد F5 استفاده كنيد. وقتي دستور را اجرا مي‌كنيد، نتيجه دستور در قسمت پايين دستور به نمايش در خواهند آمد. (شكل زير)

فعلاً همين‌ها رو تمرين كنيد تا آموزش هاي بعد!

گردآورنده: طه / Borna66

[Borna66]

Join يا پيوند چيست؟!

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

برای پيوند دادن جدول ها ابتدا بايد چند مفهوم زير را بشناسيم كه البته قبلاً بصورت ضمني به اونها اشاره شده :

1. کليد اصلی : فيلد کليد اصلی در يک جدول ، فيلدی است که شرايط زير را داشته باشد :
   
   1. مقدار آن برای هر نمونه رکورد ( سطر ) منحصر به فرد و غير تکراری باشد . به عبارت ديگرهيچ 2 رکوردی در يک جدول در اين فيلد مقدار يکسان نداشته باشد . کليد اصلی وجه تمايز دو نمونه رکورد مختلف در يک جدول است .
   2. طول مقادير آن حدامکان کوتاه باشد .
   
   

مثال : فيلد Title_ID در جدول Titles کليد اصلیست . هيچ دو كتابي نمی توانند دارای Title_IDيکسان باشند .

1. کليد خارجی : کليد خارجی ، فيلدی است که در يک جدول، کليد اصلی و در جدول ديگر، به تنهايی کليد اصلی نباشد . از کليد خارجی برای ارتباط يک به چند دو جدول با هم استفاده می‌شود.

برای ارتباط بين جدول ها بايد شرط های زير برقرار باشد . بايد قبل از طراحی پايگاه داده و جدول های آن، موارد زير را جهت ارتباط جدول های مورد نظر رعايت کرد .

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

براي مثال ، دو جدول زير رو در نظر بگيريد.

INNER JOIN:
یک INNER JOIN رکوردهای مختلفی را از جداول متفاوت با یکدیگر ادغام میکند و این عمل را از طریق تست کردن مقادیر فیلدهایی انجام میدهد که در جداول مورد نظر عمومیت دارند. ساده ترین فرم INNER JOIN بشکل زیر است :

SELECT fieldName1, fieldName2, …

FROM table1 INNER JOIN table2

ON table1.fieldName = table2.fieldName

که در آن قسمت بعد از عبارت ON، فیلدهایی را نشان میدهد از دو جدول با یکدیگر مقایسه میشوند تا معین شود چه رکوردهایی با یکدیگر ادغام میشوند. برای مثال، دستور زير، را در نظر بگيريد:

SELECT * FROM

tbl_user INNER JOIN tbl_info

ON tbl_user.userid = tbl_info.userid

اين دستور دو جدول tbl_user و tbl_info را در يكديگر ادغام كرده و نتيجه به شكل زير خواهد شد. در اين مثال ركوردهايي در خروجي مي‌آيند كه مقدار فيلد userid در هر دو جدول سابقه داشته باشد. خروجي دستور فوق به شكل زير است:

Left Join:

اين دستور نيز مشابه قبلي عمل مي‌كند. با اين تفاوت كه اگر ركوردي در جدول tbl_user وجود داشته باشد ولي در جدول tbl_info سابقه نداشته باشد، اين ركورد نيز نشان داده مي‌شود ولي فيلد‌هاي مشابه از جدول tbl_info آن برابر Null خواهد بود.

SELECT * FROM
tbl_user LEFT JOIN tbl_info

ON tbl_user.userid = tbl_info.userid

كه نتيجه‌ي اجراي آن به شكل زير خواهد بود:

Right Join:

مانند Left Join است منتها بر عكس آن. يعني اگر ركوردي در جدول tbl_info وجود داشته باشد ولي در جدول tbl_user سابقه نداشته باشد، اين ركورد نيز نشان داده مي‌شود ولي فيلد‌هاي مشابه از جدول tbl_user آن برابر Null خواهد بود.

SELECT * FROM
tbl_user RIGHT JOIN tbl_info

ON tbl_user.userid = tbl_info.userid

كه نتيجه‌ي آن به شكل زير است:

Full Join:

اين دستور تمام ركوردهاي دو جدول tbl_user و tbl_info را در هم ادغام كرده و در صورتي كه هر كدام از جدول‌ها در جدول ديگر سابقه نداشته باشد، فيلدهاي آن را Null نشان مي‌دهد.

SELECT * FROM

tbl_user FULL JOIN tbl_info

ON tbl_user.userid = tbl_info.userid

نتيجه اجراي آن به شكل زير است:

Cross Join:

نتیجه این Join تمام ترکیباتی است که از قرار گرفتن هر سطر از جدول اولیدر کنار تمام سطرهای جدول دومی بدست می آید(همانند حاصلضرب دکارتی).

SELECT * FROM

tbl_user CROSS JOIN tbl_info

نتيجه‌ي اجراي آن به شكل زير خواهد بود:

حالا چند تا تمرين خودتون حل كنيد!

تمرين- دستوري بنويسيد كه ليست نام ناشران و عنوان كتاب‌هاي منتشر شده توسط آن ناشر را نشان دهد.؟

گردآورنده: طه / Borna66