آموزش SQLserver (بخش دوم)

خلاصه سازی داده ها

هنگامی که دو جدول در یک پیوند یک به چند سهیم هستند. وسیله محرک Query مقادیری از سطر را از یک طرف برای ارتباط دادن سطرها در طرفهای دیگری تکرار می کند. بعضی مواقع آن دقیقاً چیزی است که شما می خواهید، اما اغلب شما می خواهید تکرار کردن سطرها را از چندین طریق دسته بندی یا خلاصه سازید. در این درس، ما به دو روش برای انجام آن که عبارتند از کلید واژه DISTINCT و شرط GROUP BY نگاه خواهیم کرد.

شناختن SELECT DISTINCT
یکی از اهداف طرح پایگاه داده ارتباطی برداشتن وابستگی داده ها می باشد. اما بیشتر پایگاه داده به طور ضروری مقادیر واقعی در چندین سطر را شامل خواهد شد. یک جدولی که شامل اطلاعات آدرس مشتری می باشد برای مثال احتمالاً کد ایالتی و کشوری برای چندین سطر خواهد داشت که نه اشتباه و نه تکراری می باشد. نظر به اینکه هر کد ایالتی نسبتی از یک مشتری می باشد. به همین نحو یک جدول از چندین جهت از یک ارتباط یک به چند ممکن است هر مقدار کلید خارجی معلوم که چندین بار تکرار شده را داشته باشد. آن برای یک ارتباط یکپارچه از پایگاه داده لازم می باشد. اگر چه این تکرار می تواند بعضی مواقع نتایج Query را نامطلوب سازد. یک جدول مشتری با 1000 سطر با 90 درصد مشتری از کالیفرنیا، Query زیر کد CA را 9000 بار خواهد باز گرداند که اصلاً یک نتیجه مفیدی نمی باشد.
SELECT State From Customer
کلید واژه Distinct در این موقعیت شما را کمک می کند. Distinct که درست بعد از SELECT قرار می گیرد به SQL Server دستور داده که سطرهایی چندگانه در قرارگیری نتایج را حذف نماید. بنابراین Query زیر هر کد ایالتی را فقط یک بار باز می گرداند به طور وضوح لیستی که شما جستجو می کنید.
SELECT DISTINCT State From Customer
راهنمایی: همتای کلید واژه Distinct ، All می باشد که SQL Server را برای بازگرداندن همه سطرها آگاه می سازد خواه آن واحد باشد یا خیر. از موقعی که این یک عملکرد پیش فرض از یک عبارت SELECT می باشد All به طور معمول استفاده نمی گردد. اما شما ممکن تصمیم بگیرید به در برداشتن آن اگر ساختار دستور Query را بیشتر قابل فهم سازید.

استفاده کردن از SELECT DISTINCT
کلید واژه Distinct می تواند در عبارت SQL از Query Distinct یا به وسیله تنظیمات Properties از Query مشخص گردد.

ایجاد کردن Distinct Query با استفاده از قاب دیاگرام
1- Query Designer را برای جدول Oils به وسیله کلیک راست کردن نام جدول در قاب Details باز کرده روی جدول Open رفته و همه سطرهای بازگشتی را انتخاب می کنیم.
2- قاب دیاگرام را به وسیله کلیک کردن روی دکمه قاب دیاگرام در نوار ابزار Query Designer نشان می دهیم.
3- دکمه Add Table را کلیک می کنیم. Query Designer کادر محاوره ای Add Table را نشان می دهد.
4- Plantparts را در لیست جدول انتخاب می کنیم و سپس Add را کلیک می کنیم. Query Designer جدول را به Query اضافه می کند.
5- Close را برای بستن کادر محاوره ای کلیک می کنیم.
6- دکمه قاب SQL را در نوار ابزار Query Designer کلیک می کنیم. قاب SQL ، Query Designer را نشان می دهد.
7- علامت * بعد از کلید واژه SELECT را حذف می کنیم.

8- دکمه قاب SQL را در نوار ابزار Query Designer کلیک می کنیم (OK را اگر Query Designer یک متن خطا درباره ساختار دستور عبارت SELECT نشان می دهد کلیک می کنیم). Query Designer قاب SQL را پنهان می سازد.
مهم: زمانی که شما Query Designer را باز می کنید حالت SQL پیش فرض معمولاً SELECT* می باشد. ستون ویژه در قاب دیاگرام به سبب اینکه آنها به لیست ستون اضافه می شوند انتخاب می گردند. مایکروسافت این را یک ویژگی در نظر می گیرد.
9- در قاب دیاگرام فقط ستون Plantpart را از جدول Plantparts برای نشان دادن انتخاب می کنیم.

10- دکمه Run را برای اجرای مجدد Query کلیک می کنیم. Query Designer هر مقدار Plantpart را چندین بار لیست می کند.

11- در یک ناحیه خالی از قاب دیاگرام کلیک راست کرده و Properties را انتخاب می کنیم. Query Designer کادر محاوره ای Properties را نشان می دهد.

12- گزینه مقادیر Distinct را انتخاب می کنیم.

13- Close را برای بستن کادر محاوره ای کلیک می کنیم.
14- دکمه Run را برای اجرای مجدد Query کلیک می کنیم. Query Designer هر مقدار را فقط یک بار نشان می دهد.

ایجاد کردن یک SELECT DISTINCT با استفاده از قاب SQL
1- قاب دیاگرام را پنهان ساخته و قاب SQL را نشان می دهیم.
2- عبارت SELECT موجود را با متن زیر جایگزین می کنیم.
SELECT DISTINCT Plant Types Plant Type
FROM Oils INNER Join
Plant Types ON Oils Plant Type ID=Plant Types Plant Type ID

3- دکمه Run را برای اجرا مجدد Query کلیک می کنیم. Query Designer مقدار PlantType متمایز که به وسیله جدول Oils بازگشت شده را نشان می دهد.


شناختن GROUP BY
کلید واژه Distinct امر می کند. SQL Server را به بازگرداندن سطرهای واحد نظر به اینکه شرط GROUP BY ، SQL Server را به ترکیب سطرها با مقادیر یکسان در ستون یا ستونهای مشخص شده در شرط در داخل سطر تکی امر می کند.
مهم: هر ستونی که در شرط GROUP BY شامل می باشد باید در خروجی Query شامل گردد.
شرط GROUP BY اغلب با یک aggregate Function استفاده می گردد. یک aggregate Function محاسبات در مجموعه از مقادیر را به انجام می رساند و یک نتیجه تک مقداری را باز می گرداند. رایج ترین گزینه های به هم پیوسته استفاده شده در پرس و جوهایGROUP BY ، MIN می باشد که کوچکترین مقادیر را در مجموعه باز می گرداند. MAX که بزرگترین مقادیر در مجموعه را باز می گرداند و COUNT که تعدادی از مقادیر را در یک مجموعه باز می گرداند.

استفاده کردن از GROUP BY
شرط GROUP BY می تواند مشخص شود با استفاده کردن از هر یک از قابها در Query Designer ، اما قابهای SQL و Grid بیشترین کنترل را فراهم می سازد.

ایجاد کردن یک GROUP BY Query با استفاده از قاب Grid
1- قاب SQL را پنهان ساخته و قاب Grid را نشان می دهیم.

2- ستون OilName را برای Query اضافه می کنیم.

3- دکمه Group By را در نوار ابزار Query Designer کلیک می کنیم. Query Designer یک ستون Group By برای شبکه اضافه می کند و هر دوی مقادیر را برای Group By قرار می دهد.

4- سل Group By را برای تغییر دادن سطر OilName به Count تغییر می دهیم.

5- دکمه Run را برای اجرا مجدد Query کلیک می کنیم. Query Designer تعدادی از Oils را برای هر PlantType نشان می دهد.


ایجاد کردن یک Group By Query با استفاده از قاب SQL
1- قاب شبکه را پنهان ساخته و قاب SQL را نشان می دهیم.

2- عبارت SELECT موجود را با متن زیر جایگزین می کنیم.
SELECT Plant parts Plant part Count (Oils Oil Name) As Number Of Oils
FROM Oils INNER Join
Plant parts ON Oils Plant part ID=Plant parts Plant part ID
GROUP BY Plant parts Plant part

3- دکمه Run را برای اجرای مجدد Query کلیک می کنیم. Query Designer مقدار oils برای هر Plantpart نشان می دهد.

استفاده کردن از شرط HAVING
شرط Having سطرهای بازگردانده شده به وسیله شرط GROUP BY را از همان راهی که یک شرط Where سطرهای بازگردانده شده را به وسیله شرط SELECT محدود می سازد. هر دو شرط Where و Having می تواند در یک عبارت SELECT شامل شود که شرط Where قبل از گروه بندی علمیات و شرط Having بعد از آن به کار برده می شود. ساختار دستور شرط Having همانند شرط Where می باشد به استثنای اینکه شرط Having می تواند شامل یکی از توابعهای به هم پیوسته باشد که در لیست ستونها از شرط SELECT قرار می گیرد. اگر چه شما باید توابع به هم پیوسته را تکرار کنید. برای مثال شرط Having که در حالت زیر استفاده شده صحیح می باشد.
SELECT Plant parts Plant part Count (Oils Oil Name) As Number Of Oils
FROM Oils INNER Join
Plant parts ON Oils Plant part ID=Plant Parts Plant part ID
GROUP BY Plant parts Plant part
HAVING Count (Oils Oil Name)>3
اگر چه شما نمی توانید از اسم مستعار برای تابع Count در شرط Having استفاده کنید. بنابراین شرط Having زیر درست نخواهد بود.
HAVING Number Of Oils>3

ایجاد کردن یک Query با استفاده از HAVING در قاب Grid
1- قاب SQL را پنهان ساخته و قاب Grid را نمایش می دهیم.

2- 5Add> را برای سل مورد نظر از ستون oil Name قرار می دهیم.

3- دکمه Run در نوار ابزار Query Designer را برای اجرای مجدد Query کلیک می کنیم.


ایجاد کردن یک Query با استفاده از HAVING در قاب SQL
1- قاب Grid را پنهان ساخته و قاب SQL را نشان می دهیم.

2- شرط Having را برای HAVING (Count (Oils Oil Name)<5) تغییر می دهیم.

3- دکمه Run در نوار ابزار Query Designer را برای اجرای مجدد Query کلیک می کنیم. Query Designer فقط آن Plantparts که Oils اشتراکی کمتر از 5 دارد را نشان می دهد.

آموزش SQLserver (بخش اول)

معرفی SQL و دستورات عمومی آن
توسط SQL میتوان درون یک بانک اطلاعاتی پرس و جو کرده (Query) و نتیجه را برگرداند. بانک اطلاعاتی شامل آبجکتی به نام جدول (Table) میباشد.
رکوردها در بانکهای اطلاعات در جداول ذخیره میگردند.
جدول شامل سطر و ستون میباشند.
در زیر میتوان یک جدول را مشاهده کرد :
Last Name First Name Address City
مثال:
Select Last name from persons
پر کاربرد ترین دستورات SQL شامل موارد زیر است :
SELECT استخراج یک داده از بانک اطلاعاتی
UPDATE به روز رسانی یک داده درون بانک
DELETE پاک کردن یک داده از بانک اطلاعاتی
INSERT وارد کردن یک داده جدید به بانک اطلاعاتی
همچنین در SQL میتوان داده هایی نیز تعریف کرد :
CREATE TABLE ایجاد یک جدول جدید
ALTER TABLE تغییر دادن یک جدول
DROP TABLE پاک کردن یک جدول
CREATE INDEX ایجاد یک اندیس
(کلید جستجو) DROP INDEX پاک کردن یک اندیس

SQL و Active Server Pages - ASP :
SQL یکی از قسمتهای خیلی مهم ASP میباشد زیرا در ASP برای کار با بانکهای اطلاعاتی از SQL استفاده میشود. و توسط تکنولوژی ADO میتوان از SQL در ASP استفاده کرد.
دستور SELECT :
SELECT column-name(s) FROM table-name
مثال :
نام جدول Persons است
Last Name First Name Address City
Hasani Ali Esfahan



دستور Select همراه با شرط

SELECT column FROM table WHERE column condition value
عملگر ها در SQL:
عملگر مفهوم
= معادل بودن
<> برابر نبودن
> بزرگتر


دستور DISTINCT (جداسازی) :

SELECT DISTINCT column-name(s) FROM table-name
مثال :
نام جدول Order است Company Order Number
sega 3412


--------------------------------------------------------------------------------
دستور Order By :

برای مرتب کردن سطرها
نام جدول Order است Company Order Number
sega 3412
ABS Shop 5678
w3s 3212
W3S 6778
مثال :
SELECT Company , OrderNumber


دستور Insert :

INSERT INTO Table-Name ( Column1 , Column2) VALUES

مثال :
DELETE FROM Person WHERE Lastname=’hasani’ نام جدول Persons است last name First name Adress city
alian hasani NO 40 Esfahan
Hasani Ali No 15 Tehran
نتیجه :
last name First name Adress city
alian hasani NO 40 Esfahan
SEGA
W3S
Trio


--------------------------------------------------------------------------------
دسترسی به یک پایگاه داده از یک صفحه ASP :

١-ایجاد یک ADO Connection به یک پایگاه داده
2-باز کردن Connection پایگاه داده
3-ایجاد یک
ADO Record Set 4-باز کردن
Record set 5-گرفتن داده هایی که نیاز داریم از
Record Set 6-بستن
Record Set 7-بستن
Connection


--------------------------------------------------------------------------------
1-ایجاد یک ADO Connection به یک پایگاه داده

١-1- روش DSN-LESS C:/InetPub/wwwroot/nor.mdb مسیر فایل نمونه
< % var conn=Server. CreateObject ( “ADODB.
Connection”) Conn. Provider=” Microsoft . Jet . OLEDB.4.0” Conn.Open
(“C:/InetPub/wwwroot/nor.mdb”) % >


--------------------------------------------------------------------------------
2- روش ODBC

-2- روش ODBC : در این روش ابتدا باید یک ODBD Connection به Data Base ایجاد کنیم و سپس از طریق ADO به فایل DSN به طریق زیر Connect کنیم.
< % var conn=Server. CreateObject

--------------------------------------------------------------------------------

ایجاد یک ODBC Connection به پایگاه داده MS Access :

١- وارد شدن به ODBC از Control Panel
2- انتخاب
System DSN 3- کلیک کردن روی دکمه ADD
4- انتخاب Microsoft Access Driver و کلیک کردن روی دکمه
Finish 5- در مرحله بعد کلیک کردن بر روی دکمه Select و تعیین محل پایگاه داده
6- دادن یک نام در قسمت
Data Source Name 7- کلیک کردن روی دکمه OK برای اینکه قادر باشیم اطلاعات یک پایگاه داده را بخوانیم اطلاعات باید ابتدا در Record Set ، Load شوند. بنابر این بعد از ساختن یک Connection باید یک Record Set ایجاد کنیم.
مثال :
نام Data Base = nor.mdb
نام جدول =
Customer C:/Inetpub/wwwroot/nor.mdb Data Base مسیر فایل < % conn= Server. CreateObject ( “ADODB.Connection”)
Connection ایجاد
conn.Provider=”Micrisoft.Jet.OLEDB.4.0” conn . Open (“C:/WebData/ nor.mdb”) Connection بازکردن rs=Server. CreateObject (“ADODB.Recordset”)
Record Set ایجاد rs. Open(“Customer”, conn) Record Set بازکردن % > در این مثال تمام محتویات جدول Customer به rs ریخته میشود.
حال میخواهیم در انتخاب محتویاتی از Customer که میخواهند به rs انتقال داده شوند از دستور SQL استفاده کنیم.

--------------------------------------------------------------------------------

ساختن یک Connection و Record Set و به کار بردن SQL :

< % set conn=Server. CreateObject (“ADODB .Connection”
conn. Provider=”Microsoft.jet.OLEDB.4.0” conn.
Open ( “C:/Inetpub/wwwroot/nor.mdb”) (Record Se


--------------------------------------------------------------------------------

گرفتن داده هایی که نیاز داریم از Record Set :

بعد از اینکه Record Set را باز کردیم میتوانیم به داده هایی که نیاز داریم دسترسی داشته باشیم.
مثال :
دسترسی به فیلد name از جدول rs rs(name


--------------------------------------------------------------------------------

چاپ داده ها :

مثال : چاپ فیلد name از جدول rs Response.write(rs(n



بستن Recordset و Connection :

- برای بستن Recordset
rs.close() - برای بستن Connection

قسمت چهارم

در ادامه مطالب آموزش SQL حال به بحث دیدها (view) می پردازیم. دید در اصل یک جدول مجازی است که محتوی آن توسط یک پرس و جو تعریف می گردد. همانند جدول دید هم دارای سطر و ستونهایی می باشد. می توان به موارد زیر به عنوان مزایای دید اشاره کرد :

دیدها به کاربران اجازه میدهند تا بر روی داده هایی که نیاز دارند متمرکز شوند. بنابر این داده های غیر ضروری میتوان از دید خارج کرد. دیدها امنیت داده ها را نیز افزایش میدهند چراکه کاربر فقط داده هایی را می بیند که در دید وجود دارند.

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

و ....

ایجاد دید :

وقتی دیدی را ایجاد میکنید نام آن می بایست در بین نام جداول و دیگر دیدهایی که کاربر مورد نظر آنها را ساخته , یکتا باشد.در SQL Server 7.0 شما امکان ایجاد شاخص بر روی دیدها را نداشتید , لیکن این امکان در SQL 2000 اضافه شده است.

مراحل ایجاد یک دید :

1- erprise Manager را باز کرده و بر روی Databases کلیک کرده و پایگاه داده ای را که می خواهید دید در آن ایجاد کنید . باز کنید.

2- روی Views کلیک راست کرده و سپس گزینه New View... را انتخاب کنید.

3- در پنجره بعدی کلیک سمت راست کرده و گزینه Add Table... را انتخاب کنید.

4- دکمه های Table و یا Views جدول و یا دید های مورد نظر را انتخاب نمایید. و بر روی دکمه Add کلیک نمایید. این کار را برای تمام جداول و یا دیدهای مورد نظر تکرار کنید و سپس بر روی دکمه Close کلیلک نمایید.

در قسمت Column از پانل مشبک , ستونهایی را که میخواهید در دید به آنها ارجاع نمایید انتخاب کنید. اگر میخواهید ستونی در مجموعه نتیجه دیده شود گزینه Output متناظر با آن می بایستی حتما تیک داشته باشد.

در ستون Criteria شرط را بنویسید. چند شرط را میتوانید در ستونهای OR تکرار کنید. برای گروه بندی برروی ستون Criteria کلیک سمت راست کرده و گزینه Group By را انتخاب نمایید. در اینصورت شما قادر خواهید بود از توابعی همچون Sum استفاده کنید.

تذکر : اگر Group by را انتخاب کرده باشید. تمام شرایط که در ستون Criteria بنویسید به عنوان شرایط Having در نظر گرفته میشوند. برای اینکه این محدودیتها به شرط Where اضافه گردند , بر روی پانل مشبک متناظر کلیک کرده و از لیست مورد نظر گزینه where را بجای Group by انتخاب کنید.

تذکر 2 : در ستون Alias شما میتوانید یک اسم مستعار برای این ستون در نظر بگیرید.

برای دیدن نتایج دید بر روی علامت (!) کلیک نمایید.

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