آموزش SQL server

[s@ba]

http://uc-njavan.ir/images/4wvwody79pye964ll2qs.png



با آموزش sql در خدمت شما عزیزان هستم که البته این آموزش مقدمه ایست برای آموزش asp.net . لازمه هر سایت خوبی یک بانک اطلاعاتی خوب هست و البته مشخصه که در یک سایت خوب نمی شه از یک بانک اطلاعاتی ساده مثل(Microsoft Office Access) استفاده کنید و بهتره از بانک هایی مثل sql استفاده کنید.
برای شروع ابتدا باید بسته نرم افزاری Microsoft SQL Server 2000 رو روی سیستم نصب کنید البته تنها قسمت Enterprise Manager رو نصب کنید کافی است …

بعد از اون از این مسیر\Developer\MSDE\setup قسمت (MSDN)رو باید به صورت دستی نصب کنید قبل از اینکه شروع کنید یه تنظیماتی هست که باید روی نرم افزاری اعما ل بشه تا بتونید راحت با اون کارکنید:
از مسیرEnterprise Manager start\all program\Microsoft SQL Server\ رو اجرا کنید و در قسمت console root\microsoft sql server روی زیر شاخه sql server group راست کلیک کنید و گزینه new SQL server registeratio… رو انتخاب کنید تا یک رجیستر مناسب با سیستم خودتون بسازید در مرحله دوم ساخت رجسیتربه جای نام server نام کامپیوتر خودتون رو وارد کنید(control panel\system\computer name\full computer name) و بقیه مراحل رو به صورت پیش فرض بزارید باقی بمونه
حالا باید یک زیر شاخه به شاخهsql server group به این صورت اضافه شده باشه (local)(windows nt)
خوب اگه در همین زیر شاخه گزینه databases رو باز کنید می تونید بانک های اطلاعاتی پیش فرض رو ببینید که البته بانکی که ما می سازیم هم در همین شاخه قرار می گیره
روش ساخت بانک اطلاعاتی در sql server:
در sql برای هر بانک اطلاعاتی دو فایل ذخیره می شه
۱٫فایلی با پسوند .mdf که شامل اطلاعات اصلی بانک می باشد
۲٫ فایلی با پسوند .ldf که فایل log و گزارشات تغییرات در بانک می باشد
برای ساخت بانک باید یاکد بنویسید یا اینکه ویزاردی عمل کنید پیشنهاد می کنم هردو راه رو تجربه کنید
۱٫محیط کد نویسی sql رو از این مسیر باز کنید
start\all program\Microsoft SQL Server\ Query Analyzer
یاEnterprise Manageàtools\Query Analyzer
و این کد رو وارد کنید

نام بانک شماCreate database

On
(name =’class’,filename=’c:\class.mdf’ ,size=2MB,maxsize=10MB ,filegrowth=2MB)
Log on
(name =’class’,filename=’c:\class.ldf’ ,size=2MB,maxsize=10MB ,filegrowth=2MB)

برای اجرا شدن کد دکمهf5 یا Execute Query از نوار ابزار رو بزنید
نتیجه: ساخته شدن بانک اطلاعاتی در مسیری که وارد کردید و اسم بانکتون به زیر شاخهdatabase در console root اضافه می شه و میتونید اسم اون رو ببینید
۲٫ روش دوم : میتونیددر قسمت cosole root رو گزینه database راست کلیک کرد و گزینه new database رو بزنید و همه این گزینه ها که در کد ها مشخص شده رو به صورت دستی وارد کنید.یک بانک با نام class بسازید تا بتونیم در آموزش های بعد با مثال با هم پیش بریم


http://uc-njavan.ir/images/vd3qdhxdgnb52v22qguy.jpg


(میزان رشد فایل = وقتی اطلاعات بانکتون از اون سایز اولیه بیشتر می شه چه قدر به سایز بانکتون اضافه بشه مثلا ۲MB .میتونه به صورت در صد هم باشه(۲۰ %)(زیاد مهم نیست)

[s@ba]

http://uc-njavan.ir/images/hmdrc1jlxqu3mbn72z.png


پایگاه های داده پیش فرض در sql
اگه یادتون باشه جلسه قبل دیدیم باساختن یک رجیستر جدید در sql server در زیرشاخه data base چند بانک اطلاعاتی پیش فرض ساخته شده بود .گرچه بانک های پیش فرض معمولا زیاد به کارنمی روند اما دونستن کاربرد و کارهرکدام از این بانک ها هم خالی از لطف نیست.
پایگاه دادهmaster: برای اجرای sql بسیار مهم است . اطلاعات مهمی مثل پیغام های خطا ، اطلاعات ورود به سیستم ، رویه های ذخیره شده و … در آن ذخیره شده است.
پایگاه داده model: هنگام ایجاد یک بانک داده جدید یک کپی ازاین بانک ایجاد میشود و هرشی موجود در بانک اطلاعاتی model در بانک اطلاعاتی جدید نیز وجود خواهد داشت.
نکته: هرشی که به بانک اطلاعاتی model اضافه کنید به طور اتوماتیک به تمام بانک های اطلاعاتی که بعد از این ایجاد می کنید اضافه می شود.
پایگاه داده msdb: برای تعیین برنامه های زمان بندی نگهداری سیستم و ثبت تاریخچه نسخه پشتیبان به کار میرود.
پایگاه داده tempdb: محلی برای ترکیب و مرتب سازی و سایر اعمالی که به فضای ذخیره سازی موقت نیاز دارد و با هربار اجرای sql به هنگام میشود و هنگامی که sql سرویس دهی خود را قطع می کند اطلاعات درون پایگاه نیز از بین می رود.
اشیاء یک بانک اطلاعاتی
هربانک اطلاعاتی دارای اشیائی است،ایجاد بانک اطلاعاتی برای برآورده کردن اهداف تجاری مستلزم ایجاد و کارکردن با آن اشیا است.که به اختصار معمول ترین آنها را توضیح میدهیم(میتونید با باز کردن بانک اطلاعاتی خودتون در consool root–> data base این اشیا رو ببینید)
table: حاوی تمام اطلاعات موجود در بانک اطلاعاتی است.
view: یک جدول مجازی است که محتویات آن توسط یک تقاضا مشخص میشود وممکن است از چند جدول به دست بیاید.
stored proctuer : (رویه های ذخیره شده ) مجموعه ای از دستورات sql است که کاربر یک بار آنها را می نویسد و بارها و بارها آن را فراخوانی و اجرا می کند.
roles: کاربرانی را با نیاز های دستیابی یکسان دسته بندی می کند.
rules : روش استانداری برای محدود کردن مقادیر در یک ستون هستند در واقع مقرراتی را به ستون ها اعمال می کند.
defult : وقتی مقادیری برای ستون ها در نظر گرفته نمی شود پبش فرض ها را برای ستون ها در نظر میگیرد.
user difine data type : انواع داده ای که توسط برنامه نویسان نوشته شده و در کنار انواع اولیه مورد استفاده قرار می گیرد.
user difine function: مجموعه از دستورات sql توسط برنامه نویس نوشته می شود و مانند توابع مورد استفاده قرار می گیرد.
full text categori : حاوی تعدادی index است که برای تصحیح و تسریع در جستجوی جداول و دیدها استفاده می شود.
برای تغییر و حذف کامل پایگاه داده ای که ساخته اید می توانید هم به صورت ویزاردی عمل کنید و هم از دو دستور زیر استفاده کنید.
تغییر پایگاه داده:
روش اول:راست کلیک روی نام بانک مورد نظر و گزینه properties و تغییر گزینه های مورد نظر
روش دوم: نوشتن این کد در محیط Query Analyzer

نام بانک اطلاعاتی مورد نظر alter database
مقدار جدید = صفت یا ویژگی modify
از این دستور برای تغییر در ساختار فیزیکی بانک استفاده میشود که هرکدام از ویژگی هایی که در دستور ایجاد بانک داشتیم را به طور جداگانه تغییر می دهد
حذف پایگاه داده:
روش اول: delete
روش دوم: نوشتن کد زیر

نام پایگاه داده drop database
با اجرای این دستور فایلهای ldf و mdf حذف می شود . می توان در این دستور نام یک بانک یا چند بانک را ذکر کرد.

drop database class,text

[s@ba]

http://uc-njavan.ir/images/6dtmf77p2e1son7edt.png


در ادامه بحث آموزشمون باید به تعریف و معرفی انواع داده ای در SQL serever بپردازیم . در sql نیز مانند سایر زبان های برنامه نویسی باید نوع داده ای که در بانک (فیلد های جدول)ذخیره می شود را تعیین نمود.این انواع مشخص می کند هر نوع داده با چه فرمتی و با چه حجمی ذخیره شود.
انواع داده ها یsql server :

کاراکتری با طول ثابت ۸۰۰۰بایت-هرکاراکتر۱بایت char
متنی یا کاراکتری کاراکتری با فرمت یونیکد با طول ثابت ۴۰۰۰بایت-هر کاراکتر ۲ بایت
nchar
رشته کاراکتر با طول حداکثر۸هزار کاراکترvarchar
رشته کاراکتر با فرم یونیکد با فرم متغییرحداکثر ۴ هزار کاراکترnvarchar
کد گذاری نشده با طول متغییر حداکثر ۱میلیون کاراکتر(متن طولانی)text
کد گذاری شده با طول متغییر حداکثر ۱میلیون کاراکترntext
مقادیر تاریخ و زمان که تا ۳۰۰۰ ثانیه وقت داردdatetime
تاریخمقادیر تاریخ و زمان که به ازای ۱ دقیقه وقت داردsmalltime
مقدارهای صفر و یکbitعددیداده عددی بسیار کوچم معادل ۰ تا ۲۵۵tiny int
مقادیر کوچک معادل ۲ بایتsmall int
داده صحیح معادل ۴ بایتint
داده صحیح بزرگ معادل ۸ بایتbig int
مقادیر اعشاری با دقت مضاعف معادل ۸ بایت تا ۱۵ رقم اعشارflout
مقادیر اعشاری تک دقتی معادل ۴ بایت ۲۴رقم اعشارreal
اعداد صحیح در مبنای دیگرnumerialا
اعداد در مبنای ۱۰ با دقت ۳۸ رقم اعشارdecimal
مقادیر پولی معادل ۱۵ و ۴ بایت تا ۱۵ رقم صحیحmony
پولداده باینری با طول ثابت حداکثر ۸۰۰۰binery
باینریداده باینری با طول متغییر حداکثر ۸۰۰۰varbinery
(عکس را تبدیل به کد کرده و ذخیره می کند) با طول متغییر حداکثر ۲ گیگا بایتimage
درج مقادیر تاریخ و زمان تفاوت آن با datetime در این است که این نوع را زمانی به کار میبریم که می خواهیم تاریخ جاری سیستم به صورت مقادیر منحصر به فرد در جدول ذخیره شودtimestamp



ایجاد جدول:
جدول مهمترین شی بانک اطلاعاتی برای ذخیره اطلاعات است. به دو روش زیر ایجاد می شود:
۱٫ راست کلیک روی شی table در consoolroot و انتخاب گزینه new table
2. نوشتن کد زیر د رمحیط Quary analyzer

نام جدول craete table
(…..،نوع داده ای فیلد ۲ نام فیلد۲، نوع داده ای فیلد ۱ نام فیلد ۱)
مثال:
یک جدول با نام student در بانک اطلاعاتی (که در مثال های قبل ایجاد کردیم)class ایجاد کنید که دارای فیلد های زیر باشد:
نام دانش آموز از نوع varchar
نام خانوادگی دانش آموز varchar
شماره دانش آموز از نوع int
شهر دانش آموز varchar
تاریخ تولد از نوع date time
نمره از نوع int
سن دانش آموز int
ایمیل دانش آموز varchar

create table student
)
, std_name varchar(20),std_family varchar(20),std_num int
city varchar(20), birthday datetime , grade int , age int,email varchar
(

در دستور tabel می توانیم علاوه بر تعریف نام و نوع فیلد ها از پارامتر های زیر هم استفاده کنیم:
primary key: جهت تعیین فیلد کلید اصلی
null |not null : جهت تعیین null پذیری یک فیلد
identity : جهت شمارش و افزایش یک فیلد به طور خودکار به این صورت:

(مقدار افزایش,مقدار اولیه )identity
check : جهت ایجاد محدودیت هایی برای ورود مقادیر فیلد (عملگرهای مقایسه ای ، منطقی ، between و like و in در این دستور به کار میرود)

[تعریف الگو]like
جانشین یک حرف =؟
جانشین یک محدوده از کارکتر ها= [ - ]
جانشین ۱ یا چند کاراکتر=%
(محدوده)in
نکته : مهمترین کاربرد فیلد های کلید اصلیprimary key ایجاد ارتباط بین جداول می باشد
مثال:
جدول student را با شرایط زیر ایجاد کنید:
نمره بین ۰ و ۲۰ باشد
سن کمتر از ۱۵ نباشد
نام حتما وارد شود(null پذیر نباشد)
فیلد شماره دانش آموزی کلید اصلی باشد و از ۱۰۰ شروع شده و یک واحد یک واحد به طور خودکار اضافه شود
شهر دانش آموز یکی از شهر های اصفهان ، تهران و شیراز باشد
ایمیل آن شبیه الگوی mail@yahoo.com باشد

create table student
)
std_name varchar(20) notnull
(std_family varchar(20,
(std_num int primary key identity(100,1,
(city varchar(20,
birthday datetime ,
grade int ,
age int,
,(email varchar(20
,(chek (grade between 0 and 20
,(chek (age >15
,(‘city in (‘esfahan’,’shiraz’,'tehran
[???.????@%]chek email like
(
دستورات تغییر جداول
جهت تغییر ساختار های جدول و فیلد های آن از دستورات زیر استفاده می کنیم
افزودن فیلد جدید:

نوع فیلد نام فیلد add نام جدول alter table
تغییر نوع و ویژگی های فیلد (ستون):

نوع جدید فیلد نام فیلد alter column نام جدول alter table
حذف کامل یک فیلد:

نام فیلد drop column نام جدول alter table
حذف فیزیکی و کامل یک جدول:

نام جدول drop table
درج داده در جدول:
۱٫ نوشتن کد زیر:

(…، نام فیلد ۲ ، نام فیلد ) insert [into] student
(…، مقدار فیلد ۲ ، مقدار فیلد ۱) value
2.راست کلیک روی نام جدول مورد نظر و گزینه open table\return all row
مثال:

(insert into student (std_name,std_family
(‘ value(‘ali’,'ahmadi
نکته : ترتیب نام هر فیلد متناظر برای هر مقدار باید رعایت شود و در صورتی که فیلد ها بعد از نام جدول ذکر نشود در عبارتvalue تمام فیلد ها باید به ترتیب جدول مقدار دهی شود.
نکته: می توان این دستور را طوری نوشت که تمام فیلد ها با مقادیر پیش فرض پر شوند:

defult values نام جدول insert into
پیشنهاد من اینکه که سعی کنید sql server را با مثال و تمرین یاد بگیرد

[s@ba]

http://uc-njavan.ir/images/ubo9b78ffaacsgbs401.png



در این جلسه از آموزشمون قراره پرکاربرد ترین و مهمترین دستورات SQL را آموزش بدهم.پس سعی کنید خوب یاد بگیرید( البته مثل همیشه همراه با مثال و تمرین )
انواع Query یا پرس وجو


۱.پرس و جوی عمومی (انتخاب همه اطلاعات جدول)

نام جدول select * from

مثال:

select * from student

تمامی رکورد های جدول دانش اموز را نمایش می دهد.
۲٫پرس و جوی فیلد های خاصی از جدول

نام جدول from …,نام فیلد۲ , نام فیلد۱ select
3.انتخاب فیلد های خاص و نمایش آنها با نام های جدید در خروجی

نام جدول from …, ‘نام جدید برای فیلداول ‘ as نام فیلد۱ select
4.مرتب سازی پرس وجو

نام فیلد orther by نام جدول from نام فیلد ها select

پرس جو براساس نام فیلدی که در جلوی orther by نوشته شده است مرتب می شود.
۵٫استفاده از عملگر های منطقی در شرط نمایش orther by

شرط ۲ (and/or/not ) شرط۱ where نام فیلد orther by نام جدول select * from
6.نمایش query با حذف مقادیر تکراری

…. نام جدول from …, نام فیلد select distinct
7.استفاده از litrall یا رشته همراه با پرس وجو

…. نام جدول from …. , نام فیلد ۱, ‘ select ‘litrall1
8. استفاده از عملگر like
توسط عملگر like می توانیم یک الگو یا شکل کلی برای ارزیابی مقادیری که می خواهیم تعریف کنیم که در صورت مطابقت با الگو نمایش داده شوند.

‘الگو’ where like [نام فیلد orther by] نام جدول select * from
9.استفاده از gruop by
هرگاه بخواهیم نتیجه نمایش پرس وجو بر اساس یک فیلد دسته بندی شود نام آن را در جلوی عبارت gruop by می نویسیم. توجه کنید نام فیلد جلوی gruop by حتما باید در جلوی دستورselect نوشته شود ودر صورت استفاده از فیلد های دیگر باید از توابع جمعی استفاده کنیم.
نکته:همیشه و همیشه هنگامی که از توابع جمعی استفاده می کنیم حتما باید از کلمه gruop by استفاده شود.
توابع جمعی:
()sum: جمع
()avg: میانگین
() max: پیدا کردن بزرگترین مقدار
()min: پیدا کردن کوچکترین مقدار
() cuont: شمارش تعداد

نام فیلد group by نام جدول select * from

مثال:
جمع نمرات هر دانش آموز با سن بالای ۱۵ (دسته بندی بر اساس نام و فامیل)

select std_name , std_family,sum(grade) from student
where age>15 gruop by std_name,std_family
10.استفاده از group by اعمال شرط having بر روی آن
برای شرط دسته بندی رکورد ها استفاده می شود. عبارت having شرط نمایش مقادیر دسته ها را مشخص می کند و بعد از دسته بندی شدن اعمال می شود ولی شرط جلوی where روی تمامی رکورد ها و قبل از دسته بندی شدن اعمال می شود.

شرط having نام فیلد group by نام جدول select * from

مثال:
جمع نمرات دانش اموزانی بالای ۱۵ سال که جمع نمرا ت آنها از ۱۶ بیشتر شده است .

select std_name , std_family,sum(grade) from student
where age>15 gruop by std_name,std_family having sum(*)>16

نکته : معمولا having هرشرطی را با توابع جمعی بیان می کند در غیر این صورت فقط می توان در شرط از نام فیلدی استفاده کرد که در جلوی عبارت group by نوشته شده است.
۱۱.استفاده ا ز عملگر in
جهت تعیین شرط با محدوده ای از مقادیر(عملیات in شبیه عملگر or می باشد)

(‘رشته ۳′,’رشته ۲′,’رشته ۱′) in نام فیلد where نام جدول select * from
12.استفاده از عملگر top:
هرگاه بخواهیم حداکثر تعداد رکورد های نمایشی را در خروجی نمایش دهیم از این عملگر استفاده می کنیم.

select top 5 grade from student

5نمره اول جدول دانش آموز را برمی گرداند.
تذکر:برای نمایش درصدری از رکورد ها در خروجی بعد از کلمه top ابتدا عدد و سپس کلمه percent را می نویسم .

select top 5 percent grade from student
5درصد اول نمرات جدول دانش آموز را برمی گرداند.
۱۳٫برای بررسی مقادیر فیلد ها با مقدار null در شرط نمایش رکورد ها

is null/is not null نام فیلد where نام جدول select * from
14.استفاده از عبارت comput by:
توسط این عبارت یک عملیات را روی رکورد های دسته بند ی شده توسط توابع جمعی انجام می دهیم.هنگام استفاده از این عملگر حتما باید عبارت orther by را نوشته و فیلد جلوی comput حتماباید جلوی orther آمده باشد.

شرط where نام جدول select * from
نام فیلد orther by
نام فیلد دسته بندی شده by توابع جمعی compute
تفاوت gruop by و comput by :
comput نتیجه عملیات روی رکورد های دسته بندی شده را به صورت تفکیک شده نمایش می دهد.
۱۵٫استفاده از عبارت into:
نتیجه پرس وجو در یک جدول موقت ذخیره میشود که نام آن جدول حتما باید ذکر شود.نشانه یک جدول موقت عبارت# می باشد.جدول موقت می تواند توسط دستور select مقادیر خود را نمایش دهد.

…. نام جدول اصلی from نام جدول موقت select * into
ایجاد جستجوی های فرعی :
هر گاه قسمت where یک دستور select با یکی از عملگر های زیر select دیگری را بنویسیم یک پرس و جو یفرعی ایجاد کرده ایم.
in: بررسی وجود مقادیر یک فیلد در یک مجموعه جواب که خروجی select فرعی است.

(دستورات پرس و جوی فرعی ) inنام فیلد where نام جدول select * from
any: مقادیر یک فیلد توسط عملگر “=” با مقدار جواب select داخلی مقایسه می شود.

(دستورات پرس و جوی فرعی )= نام فیلد where نام جدول select * from
all:مقادیر یک فیلد توسط عملگرهای “=<>” با مقدار جواب select داخلی مقایسه می شود.

(دستورات پرس و جوی فرعی ) =/</> نام فیلد where نام جدول select * from
exists/not exists:وجود مقادیر یک فیلد یا عدم وجود آن که بامقدار جواب select داخلی مقایسه می شود.

(دستورات پرس و جوی فرعی ) exists/not exists نام فیلد where نام جدول select * from

مثال:
ابتدا جداول زیر را بسازید.
جدول فروشنده(saller):
با فیلد های:saller_ name,saller _num
جدول محصولات(product):
با فیلد های:pro_ name,pro _num
جدول فروش(salle):
با فیلد های:saller_ num,pro _num,number
1.نام فروشندگانی که یکی از کالاهایی که احمدی فروخته است را فروخته اند نمایش داده شود.

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

select saller_name from saller
where saller.saller_num=salle.saller num
(این دستور ارتباط بین جداول را ابتدا چک می کند)
and pro _num in
select pro_num from salle )
where saller.saller_num=salle.saller num and
(’saller_name=’ahmadi
و در آخر سعی کنید این دو تمرین را حل کنید تا در جلسه بعد پاسخ خودتون و پاسخ من روباهم مقایسه کنیم.

۲٫نام فروشندگانی که هیچ کالای نفروخته اند نمایش داده شود.
۳٫کد کالا هایی که بیش از یک بار فروخته شده است.

[s@ba]

http://uc-njavan.ir/images/zaclcqiuz9l4sg1qsadi.png


یکی از مهمترین بخش های هر بانک اطلاعاتی و یا بهتر بگم بزرگترین مزیت یک بانک اطلاعاتی نسبت به جدول های ساده امکان برقراری ارتباط بین جدول ها ی آن است.
مزیت های برقراری ارتباط بین جداول:
۱٫می توان با استفاده از چند جدول گزارش هایی را تولید کرد .
۲٫ اشتباهات در ورود اطلاعات را کاهش داد.
۳٫ تسریع در ورود و بازیابی اطلاعات
۴٫موجب ایجاد یکپارچگی خواهد شد.

برای تعیین رابطه بین جداول باید از فیلد های مشترک استفاده کرد.فیلدی که براساس آن بین جداول ارتباط برقرار می شود در جدول اصلی کلید اصلی و در جدول فرعی کلید خارجی می نامیم.
روش اول برای ایجاد ارتباط:
باز کردن جدول در حالتDeisgn– راست کلیک و انتخاب گزینه relationships
انتخاب گزینه new و انتخاب فیلد کلید اصلی از هردو جدول اصلی و فرعی و انتخاب تنظیمات مناسب از گزینه های پایین پنجره
روش دوم برای ایجاد ارتباط:
عملگر های join
- cross join : توسط این نوع جوین لیستی از تمام رکورد های جدول فرعی تولید می شود به طوریکه هر رکورد جدول اصلی با رکورد جدول فرعی نظیر به نظیر ارتباط دارد.(ارتباط یک به یک)

نام جدول ۲ cross join نام جدول ۱ from فیلد های جدول اصلی select
- iner join :توسط این عملگر رکورد های از هر دو جدول طوری واکشی می شود که مقادیر غیر null مرتبط را نمایش می دهد.(نوعی ارتباط یک به چند)

شرط on نام جدول ۲ iner join نام جدول ۱ from فیلد های جدول اصلی select
مثال:(فیلد شماره دانش آموزی به عنوان کلید اصلی در نظر گرفته شده است)

select student .std_name,student.std_family from
student inerjoin register on
student.std_num = register . std_num
-outer join
که شامل left,right,full می شود:
در این نوع جوین همه رکورد های مرتبط با جدول اصلی که در جدول فرعی وجود دارد حتی مقادیر null واکشی می شود.
left outer join : تمام رکورد های جدول اصلی را برمی گرداند و رکورد های مرتبط با جدول اصلی در جدول فرعی نمایش داده می شود و فیلدهایی که درجدول اصلی وجود دارد و در جدول فرعی وجود ندارد، null می شود.
right outer join : تمام رکورد های جدول فرعی را برمی گرداند و رکورد های مرتبط با جدول اصلی در جدول فرعی نمایش داده میشود و فیلد های که در جدول اصلی وجود ندارد ولی در جدول فرعی وجود دارد، null می شود.
full outer join: همه رکورد ها را برمی گرداند. در صورتی که یک رکورد از دو جدول نظیری نداشته باشد،null می شود.

جدول ۱ from نام فیلد ها select
شرط on جدول۲ left| right|full] outer join ]

ایجاد view
دید یا نما مجموعه ای از رکورد های بانک است که جهت دسترسی کابران بانک دسته بندی می شود. از مهمترین مزایای دید امنیت داده ها و عدم دسترسی کاربران غیر مجازبه داده ها و تسریع دسترسی به داده ها است. view یک جدول مجازی است.

روش اول:
بر روی شی view در بانک اطلاعاتی راست کلیک کرده و گزینه new view را انتخاب می کنید.

روش دوم:

as نام دید create view
دستورات selelct
نکته۱: تمامی دستورات( DDL(alter-drop-create که روی جدول ها انجام می شود برای دید های نیز قابل انجام است.
نکته ۲: استفاده از دستور orther by و compute by در select تشکیل دهنده view غیر مجاز است مگر اینکه کلمه top را به کارببریم.
نکته ۳: استفاده از عبارت select into در دیدها غیر مجاز است.
مثال:
ایجاد یک دید از جدول دانش آموز فقط۵رکورد مرتبط شده بر اساس شماره که به ۳ ختم نشوند.

create view v_std as
select top 5 std_name,std _family , std_num
from student inerjoin register on
student.std_num = register.std_num
‘where std_num not like ‘%3

آشنایی با (transact sql (t.s
مجموعه ای از دستورات sql که همه با هم اجرا می شوند یا لغو میشوند می تواند شامل if ، for،select و… باشد.
ساختار کلی:

نام begin transaction
دستورات مورد نظر
commit transaction
مثال:
۲نمره به نمره دانش آموزان درس ریاضی در قالب t.s اضافه کنید.

begin transaction t1
update student set grade = grade+2
where std_num in
(’select std_num from register where lessen_name=’math )
commit transaction
در مقابل commit دستور rollback transaction رو داریم که همه دستورات مربوط به t.s را لغو می کند و بانک را به حالت قبل از اجرای آن برمی گرداند.
علاوه براین دو دستور(rollback و commit) دستور save transaction را نیز داریم که یک نقطه ذخیره در داخل t.s تعیین می کند. بعد از این کلمه نام یک قسمت از برنامه را می آوریم.بعد از آوردن کلمهsave transactiom معمولا با دستور rollback به نقطه ذخیره برمی گردیم.(به اولین نقطه)

begin transaction t1
.
.
.
save transaction 1
svae transaction 2
rollback transaction
commim transaction
بعد از رسیدن به rollback به اولین نقطه ذخیره (save transaction1) برمی گردد.

[s@ba]

http://uc-njavan.ir/images/tvugjctmq2wbfxm4huky.png


در آموزش قبلی نحوه ایجاد یک tran saction یا همونt.sql رو آموزش دادیم و اما ادامه آموزش…

تعریف متغییر در transaction sql ها
متغییر ها توسط دستور declare تعریف میشوند و توسط دستور set یا select مقدار دهی میشوند. به این صورت:

نوع داده نام متغییر@ declare
نکته: متغییر های محلی که با declare تعریف میشوند باید با علامت @ شروع شوند.
مثال(مقدار دهی متغییر با set):
پرس وجویی بنویسید که در آن نام تمام کسانی که شامل حرف a می شود برگردانده شود.

declare @name varchar
‘%set @name = ‘%a
select std_name from student
where std_name like @name
مقدار دهی با select: این دستور حتما باید یک مقدار واحد را برگرداند.

declare @ave int
select @avg = ave(age) from student
print @avg
نکته: بادستور print میتوان محتویات یک متغییر را نمایش داد.
ساختارهای شرطی (if ,else):
شکل کلی if ها در sql به صورت زیر میباشد.

شرط یا همان دستورات if
begin
دستورات
end
else
begin
دستورات
end
مثال:دستوری بنویسید که در صورت بزرگ بودن مجموع نمرات دانش آموز احمدی از ۱۰۰ پیغام مناسب نمایش دهد و اگر نبود رکورد های مربوط به max و min نمرات نمایش داده شوند.

if (select sum(grade) from student
‘ where std_family =’ahmadi
(group by std_family
‘print ’sum grade of ahmadi is more than 100
else
select min(grade),max(grade) from student
‘where std_family=’ahmadi
group by std_family

ساختار تکرار:
شکل کلی دستور به این صورت است:

شرایط یا دستورات while
begin
دستورات
end
دستور break , continue:
break: خروج از حلقه -دستورات حلقه دیگر اجرا نمیشود و کنترل برنامه برمی گردد به اولین دستور بعد از end
continue: دستورات حلقه به ازای مقدار بعدی حلقه اجرا می شود و برنامه برمی گردد به اولین دستور حلقه

مثال:

دستوری بنویسید که هربار ۲ نمره به نمره دانش اموزان اضافه کند و اگر max آن بیشتر از ۱۸ شد دیگر اضافه نکند.(شرط میانگین نمرات کوچکتر از ۱۲ باشد)
while (select ave(grade) from student ) <12
begin
update student set grade =grade+2
if (select avg(grade) from student) >18
break
end
توابع رشته ای در sql :
تبدیل کاراکتر به کد اسکی-خروجی یک عدد است.

(asci(char
تبدیل کد اسکی به کاراکتر-خروجی که کاراکتر است.

(char(int
موقعیت یک زیر رشته در رشته ای دیگر-خروجی یک عدد است.

(رشته,زیر رشته)char index
مقایسه دو رشته-خروجی یک عدد است-اگر خروجی تابع عدد ۳ بود به ان معناست که هر دو رشته نا برابر اند و اگر خروجی عدد ۴ بود رشته ها برابر و مساوی هستند.

(diffrence(str1,str2
از نقطه شروع به تعدادی که ما تعیین می کنیم از سمت چپ رشته یک زیر رشته برمی گرداند.

(محل شروع یک عدد,left(str
از نقطه شروع به تعدادی که ما تعیین می کنیم از سمت راست رشته یک زیر رشته برمی گرداند.

(محل شروع یک عدد,right(str
طول رشته را برمی گرداند.

(len(str
تبدیل رشته به حروف کوچک

(lower(str
تبدیل رشته به حروف بزرگ

(upper(str
حذف فضاهای خالی یک رشته از سمت چپ

(trim(str
حذف فضاهای خالی رشته از سمت راست

(rtrim(str
جایگزین کردن رشته دوم به جای رشته اول

(رشته دوم,رشته اول,رشته اصلی)replace
تطابق دادن یک رشته با یک الگو-خروجی یک عدد است اگر عدد صفر بود رشته مانند الگو می باشد.

(رشته والگو)pat index
تبدیل یک عدد به رشته عددی

(str(int
یک زیر رشته از نقطه شروع به طول دلخواه برمی گرداند.

(طول رشته ,نقطه شروع ,رشته اصلی)substring
معکوس یک رشته را برمی گرداند.

(reverse(str
نکته : در توابع رشته ای تمام توابعی که ورودی آنها یک رشته است می تواند به جای رشته مقداریک فیلد درآن قرار گیرد.
مثال:
دستوری بنویسید که چهار کاراکتر اول فیلد شماره دانش آموزی یا با مقادیر ۱ تا ۴ جایگزین کند.
update student set
(’std_num = replase(std_num,left(std_num,4),’1234
توابع تاریخ وزمان:
اضافه کردن قسمتی از تاریخ(ماه یا روز یا سال) به تاریخ

(مقدار تاریخ,تعداد,dateadd(month/date/year
تاریخ جاری سیستم را برمی گرداند.

()get date
تفاوت دو تاریخ

(تاریخ۲,تاریخ۱,قسمتی از تاریخ) date diff
برگرداندن نام قستی از تاریخ مثلا نام ماه یا روز

(تاریخ,قسمتی از تاریخ)date name
برگرداندن قسمت روز تاریخ

(تاریخ)day
برگرداندن قسمت ماه تاریخ

(تاریخ)month
برگرداندن سال تاریخ

(تاریخ)year
تاریخ جاری سیستم به فرم بین الملل

() getutedate

بازم پیشنهاد می کنم که تمرین کنید sqlتنها با تمرین و حل کردن مثال یاد گرفته میشه!

[s@ba]

http://uc-njavan.ir/images/tk3rxj5l7dyb95zsq1d.png

روال های ذخیره شده stored procedure
به مجموعه دستورات t.sql که شامل تعریف متغییر ها ، عملیات روی آنها و دستورات ورودی و خروجی میشود s.p گفته می شود. هر روال ذخیره شده یک شی است که می تواند سیستمی یا غیر سیستمی باشد.روال های سیستمی قبلا تعریف شده اند و با ایجاد یک بانک اطلاعاتی جدید به آن بانک اضافه می شوند و ما فقط در T.sql ها آنها را فراخوانی می کنیم ولی روال های غیر سیستمی توسط خود کاربر تعریف شده و همانند نوع سیستمی می توانند بارها و بارها فرخوانی شوند.
http://www.taktemp.com/wp-includes/js/tinymce/plugins/wordpress/img/trans.gif
ایجاد یک روال ذخیره شده:

پارامتر های ورودی نام روال create procedure
‍‌Output] as ] نوع پارامتر ها
دستورات select
پارامتر های ورودی روال ها با علامت @ و نوع داده ای تعریف می شوند و پارامتر های خروجی بعد از پارامتر های ورودی با علامت @ و نوع داده ای و کلمه کلیدی output می آیند.
مثال: (پارامتر های خروجی)

create proceduer grdesum @sum int output
as
select @sum = sum(grade) from student

موقع فراخوانی جمع نمرات به عنوان خروجی برگردانده می شود.
(پارامتر های ورودی )

create std_info @name nvarchar(20) , @family nvarchar
as
select std_name , std_family, age from student
where std_name = @name and std_famil=@family
در موقع فراخوانی اطلاعات دانش آموزانی برگردانده می شود که برابر با مقادیر فرستاده شده می باشد.

فراخوانی proceduer
اگر در قطعه کد t.sql دستور فراخوانی اولین دستور باشد می توان فقط نام پروسیجر را نوشت ولی در صورتی که اولین دستور نباشد باید حتما عبارت exec یا execute را همراه آن نوشت.

فراخوانی روال هایی که ورودی دارند:

مقدار فرستاده شده برای روال=نام پارامترورودی @ نام پروسیجر exec
مثال:

‘exec std_info @name=’anis’,@family=’barmar
فراخوانی روال ها بدون ورودی:

نام پروسیجر exec
توابع تعریف شده کاربرuser difine function-udf-
تابع مجموعه از t.sql هاست که یک بار نوشته می شود و چند باز فراخوانی می شود ولی تفاوت آن با پروسیجرهای قبلی در این است که حتما و حداقل یک خروجی را دارد و باید یک مقدار را به عنوان خروجی برگرداند.

نوع خروجی returns (پارامتر ها) نام تابع create function
as
begin
….
نام متغییر خروجی return
end
روش فراخوانی :

‘نامی برای نمایش’ as (پارامتر ها)نام تابع .نام مالک.نام بانک select
مثال : تابعی بنویسید که یک عدد را دریافت و فاکتوریل آن را حساب کند.

( create function fact (@num int
returns int
as
begin
declare @sum int
set @sum =1
declare @counter
set @counter = 0
while @counter<= @num
begin
set @counter = @counter+1
set @sum = @sum *@counter
end
return @sum
end
فراخوانی:

’select class.dbo.fact(3) as ‘fact
نکته:dbo یک مالک عمومی است.
توابع جدولی یا inline table:
این نوع تابع نیز حتما خروجی دارد و می توان بارها و بارها از آن استفاده کرد اما تفاوت ان با تابع قبلی در این است که توابع جدولی مجموعه ای از رکورد های جدول را برمی گرداند.
ایجاد یک تابع جدولی:

return table (نام پارامتر ها)نام تابع create function
as
(دستورات rreturn (select
فراخوانی توابع جدولی

(پارامتر ها)نام تابع select * from
مثال:اطلاعات دانش آموزانی را برگرداند که معلمشان همان معلمی باشد که به عنوان ورودی به تابع فرستاده می شود.

create function std_info(@teacher varchar) returns table
as
return (select student.std_name,student.std_family,register.teach er
where student.std_num =register.std_num
(and register.teacher = @teacher
روش فراخوانی:

(’select * from std_info(‘ahmadi
Trigger :
نوعی روال ذخیره شده است که در مواقع خاص اجرا می شود مثلا در زمان درج داده جدید یا ویرایش داده ها اجرا می شود . توجه کنید که trigger هیچ گاه فراخوانی نمی شود و بلکه اتوماتیک اجرا میشود(البته در زمانی که مشخص می کنید) توسط دستور زیر ایجاد میشود.

نام تریگر create trigger
نام جدولی که تریگر روی ان عمل می کند on
for insert/update/delete
as
دستوراتی بعد از عمل مشخص شده انجام میشود
نکته: به جای کلمه for می توان از کلمه after , inetred af نیز استفاده کردو کلمه after پیش فرض است.
after: بعد از انجام موفقیت آمیز عملیات (insert/update/delete)
for: در همان لحظه اجرا می شود.
ineterd of: ترگیر را به یک قطعه کد T.SQl عادی تبدیل می کند و مانند یک پروسیجر معمولی تریگر را می سازد.
ایجاد یک تریگر به صورت ویزاردی:
راست کلیک روی جدول مورد نظر/گزینهall taks/mange trigger و نوشتن کد های مورد نظر
مثال:

create trigger std_trigger
on student
for insert/update
as
if (select max(grade) from student) >20
‘print ‘a range is not valid
نتیجه : در لحظه ورود یا تغییر داده ها اگر بزرگترین نمره در جدول از ۲۰ بیشتر بود پیغام مناسب را نمایش می دهد.
مثال ۲: تریگری بنویسید که بعد از درج داده جدید در جدول sudent اگر داده ها بیشتر از ۱۰ رکورد شد یک پیغام خطا چاپ کند و رکورد های درج شده را حذف کند.

create trigger isert_trigger
on student
for insert
as
if (select count(*) from student) >10
begin
‘print ‘you cannot insert
rollback
end

[s@ba]

http://uc-njavan.ir/images/j20x2rirz6mwf5brdu4.png





دستور raiserror در تریگرها
توسط این دستور یک پیغام خطا تولید میشود که در جدول sys_message در بانک اطلاعاتی master (جدول پیغام های سیستمی ) ذخیره می شود.
روش استفاده :


(حالت ،شدت،”متن خطا “)raiserror
متن خطا:یا متن یا شماره پیغامی از پیغام های سیستمی که در جدول پیغام های سیستمی ذخیره شده
شدت:عددی بین ۱ تا ۲۵ برای پیام هایی که خودمان پیاده می کنیم می توانیم یک عدد بین ۱ تا ۱۸ بدهیم
حالت: عددی است بین ۱ تا ۱۲۷ که اطلاعاتی راجع به حالت خطا را نگه می دارد
تابع()update در تریگر
در ازای عملیات های insert و update یک مقدار بولین برمی گرداند که اگر true باشد یعنی آن ستون تغییر کرده است.
روش استفاده:

… (نام ستون یا فیلد)if update
نکته :این تابع فقط در بدنه تریگر ها استفاده می شود.
مثال: تریگری بنویسید که اجازه ندهد فیلد نام تغییر کند.

create trigger name_std
on student
after upfate , insert
as
(if update (std_name
begin
(raiserror(“you can not change name”,10,2
rollback
end
مفهوم جداول deleted ,inserted
دستورات DML که باعث اجرای تریگر می شودبا ۲ جدول موقت inserted و deleted کار می کند که این دو جدول به صورت اتوماتیک ایجاد و مقدار دهی می شوند ما می توانیم از این جداول برای تست کردن عملیات های DML قبل از اعمال روی جداول استفاده کنیم.
در جدول deleted یک نسخه از تمام رکورد های حذف شده در اثر دستورات delete یا update را نگه می دارد و جدول inserted یک نسخه از تمام رکورد های جدید اضافه شده بر اثر عملیات های insert , update را نگه می دارد دقت کنید که عمل update هردو جدول را مقدار دهی می کند.
ما از طریق نوشتن دستورات join جداول موقت را با جداول اصلی می توانیم مقادیری را که قبلا حذف شده بود یا تازه درج شده را ببینم:
مثال:تریگری بنویسید که بعد از عملیات درج رکورد های درج شده جدید جدول insert را نمایش دهد.

create trigger inset_trigger
on student
for insert
as
select student.std_num ,inserted.std_num
from inserted,student
where student.std_num=inserted.std_num
تابع drop برای حذف تریگر

نام تریگر drop trigger
تابع alter برای تغییر تریگر

نام تریگری که میخواهیم تغییر بدهیم alter trigger
نام جدول on
….. for
…..as
یکی از موارد on,forیا as تغییر می کند.



http://uc-njavan.ir/images/zzfzv5qzqsowmis6hoc.png



موفق باشید