تبیان، دستیار زندگی

زبان SQL تنها زبان استاندارد و جامع پیاده‌سازی، مدیریت، نگهداری و كار با بانكهای اطلاعاتی می‌باشد كه تقریباً توسط تمام بانكهای اطلاعاتی كوچك و بزرگ مانند Access، SQL Server، Oracle و DB2 پشتیبانی می‌شود. طراحان و افرادی كه بنوعی با بانكهای اطلاعاتی سروكار دارند و همچنین برنامه نویسانی كه از این بانكها استفاده می‌كنند هركدام باید تا اندازه‌‌ای با این زبان آشنایی داشته باشند. این مقاله كه در سه قسمت تهیه شده است می‌كوشد تا مفاهیم زبان SQL را در قالب یك مثال كاربردی بیان كند. هرچند كه مفاهیم بكار رفته در این مقاله در تمامی بانكهای اطلاعاتی قابل پیاده‌سازی می‌باشند ولی مثالهای ارائه شده در 2000 SQL Server مورد تست قرار گرفته‌اند.

بانك اطلاعاتی كه در این مقاله بعنوان مثال مورد استفاده قرار گرفته است بانك اطلاعاتی یك آموزشگاه می‌باشد كه شامل دو جدول بنامهای teachers و students می‌باشد. جدول اول اطلاعات اساتید و جدول دوم اطلاعات دانشجویان را در خود نگه می‌دارد. جدول اول دارای چهار فیلد زیر می‌باشد: name یا نام از نوع text، family یا فامیل از نوع text، age یا سن از نوع عدد، salary یا حقوق از نوع عدد.

جدول دانشجویان نیز شامل چهار فیلد می‌باشد، سه فیلد اول آن مشابه سه فیلد اول جدول اساتید می‌باشد و فیلد چهارم آن عبارتست از GPA یا معدل كه یك فیلد عددی است.

1- دستورهای SELECT و FROM :

هدف نهایی از دادن انبوه اطلاعات به كامپیوتر، جستجو و یافتن اطلاعات مفید می‌باشد. به این عمل یعنی جستجوی اطلاعات در بانك اطلاعاتی Query نیز گفته می‌شود. اكثر دستورات زبان SQL نیز در همین راستا مورد استفاده قرار می‌گیرند. در این بین مهمترین و پركاربردترین دستور را می‌توان دستور SELECT قلمداد كرد.این دستور جهت انتخاب یك یا چند فیلد از یك یا چند جدول مختلف مورد استفاده قرار می‌گیرد، فیلدهای انتخاب شده پس از اجرای query روی صفحه نمایش داده خواهند شد.

بعنوان مثال اگر در بانك اطلاعاتی فرضی خودمان بخواهیم اسم و فامیل تمام دانشجویان را مشاهده كنیم باید برنامه‌ای به شكل زیر بنویسیم:

SELECT name, family
FROM students

در این برنامه كه به زبان SQL استاندارد نوشته شده است از دو دستور SELECT و FROM استفاده شده است. دستور SELECT مشخص می‌كند كه چه فیلدهایی از جدول باید نمایش داده شوند و دستور FROM نیز مشخص كننده جدولی است كه قرار است اطلاعات از داخل آن استخراج شوند. حال اگر بخواهیم نام و فامیل تمام اساتید را ببینیم برنامه بالا را باید بصورت زیر تغییر دهیم:

SELECT name, family
FROM teachers

همان طوری كه از مثال‌های بالا نیز مشخص است جلوی دستور SELECT نام فیلدهایی نوشته می‌شوند كه قرار است نمایش داده شوند و بعنوان جداكننده نیز باید از كاراكتر كاما استفاده كرد. در صورتی كه بخواهیم تمام فیلدهای یك جدول را ببینیم می‌توانیم بجای نوشتن اسم تمام فیلدها فقط از یك كاراكتر ستاره استفاده كنیم. كاراكتر ستاره بمعنی تمام فیلدهای یك جدول می‌باشد. مثلاً دو دستور زیر با هم معادلند:

*SELECT
SELECT name, family, age, gpa

2- دستور WHERE :

دستور SELECT همان طوری كه گفته شد جهت انتخاب و نمایش تعدادی از فیلدهای جداول مورد استفاده قرار می‌گیرد. در این حالت تمام ركوردها نمایش داده خواهند شد، ولی در بیشتر موارد هدف از نوشتن Query نمایش ركوردهایی است كه دارای شرایط ویژه‌ای می‌‌باشند مثلاً نمایش مشخصات اساتیدی كه بیش از پنجاه سال سن دارند. دستور WHERE برای گذاشتن یك یا چند شرط به دستور SELECT مورد استفاده قرار می‌گیرد. با اضافه كردن شرط به دستور SELECT تعداد ركوردهای خروجی (پاسخ) معمولاً محدودتر می‌شود. بعنوان مثال اگر بخواهیم مشخصات اساتیدی كه بیش از چهل سال سن دارند را ببینیم باید برنامه‌ای بصورت زیر بنویسیم:

* SELECT
FROM teachers
WHERE age > 40

در این برنامه شرط age > 40 با استفاده از دستور WHERE به Query اضافه شده و باعث شده است تا فقط مشخصات اساتیدی نمایش داده شوند كه در این شرط صدق می‌كنند یعنی بیش از چهل سال سن دارند.

حال اگر بخواهیم مشخصات دانشجویانی را كه نام آنها علی می‌باشد و سن آنها نیز كمتر از پانزده سال است را ببینیم باید Query زیر را اجرا كنیم:

* SELECT
FROM students
WHERE age < 15 AND name = "ali"

كاربرانی كه با Access كار می‌كنند باید سطر آخر را به صورت زیر تغییر دهند:

WHERE family LIKE "*zadeh"

جهت جستجو كردن فیلدهایی كه دارای یك الگوی (Pattern) خاصی هستند باید از دستور LIKE و Wildcardها استفاده كرد. در مثال زیر نام و سن تمام اساتیدی كه اسم آنها با Pe شروع می‌شود، نمایش داده خواهد شد.

SELECT name, age
FROM teachers
WHERE name LIKE "Pe%"

3- دستور : ORDERBY

دستور ORDERBY جهت Sort كردن ركوردهای نمایش داده شده مورد استفاده قرار می‌گیرد. با این دستور می‌توان مشخص كرد كه ركوردهایی كه قرار است نمایش داده شوند برحسب كدام فیلد باید مرتب شوند. بعنوان مثال برای مشاهده كردن مشخصات اساتیدی كه سن آنها بیشتر از 29 سال می‌باشد و در ضمن لیست خروجی بترتیب اسم فامیل نیز مرتب شده باشد باید Query زیر را اجرا كرد.

* SELECT
FROM teachers
ORDERBY family

در این حالت افراد بترتیب اسم فامیل خود لیست خواهند شد. (از A تا Z) در صورتی كه بخواهیم ترتیب Sort شدن برعكس شود (از Z تا A) می‌توان پس از دستور ORDERBY از كلمه كلیدی DESC استفاده كرد، مانند مثال زیر

*SELECT
FROM teachers
ORDERBY DESC name

بعنوان مثال در Query زیر نام اساتیدی كه بیش از 30 سال دارند برحسب فامیل آنها مرتب می‌شود در صورتی كه چند استاد دارای اسم فامیل یكسانی باشند ملاك مرتب شدن، اسم كوچك آنها خواهد بود.

* SELECT
FROM teachers
WHERE age > 30
ORDERBY family, name

4- استفاده از توابع:

در دستور SELECT علاوه بر تعریف فیلدها می‌توان از عبارتهای ریاضی و یا توابع استاندارد SQL نیز استفاده كرد. بعنوان مثال اگر مالیات بردرآمد پنج درصد باشد Query زیر نام اساتید و مالیاتی را كه هر كدام می‌پردازند را مشخص می‌كند.

SELECT family, name, salary*5/100
FROM teachers

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

SELECT COUNT (name), SUM (Salary)
FROM teachers

5- Queryهای چند جدولی:

بعنوان مثال اگر بخواهیم لیست اساتید و دانشجویانی كه دارای ارتباط فامیلی هستند را ببینیم می‌توانیم از Query زیر استفاده بكنیم:

* SELECT
FROM students, teachers
WHERE students.family = teachers. Family

و یا اگر بخواهیم لیست اساتیدی را مشاهده كنیم كه سن آنها از سن برخی از دانشجویان كمتر است، می‌توانیم Query زیر را اجرا كنیم:

SELECT teachers.family, teachers.name
FROM students, teachers
WHERE teachers.age < students.age

6- كلام آخر:

سلام به شما دوست بزرگوار

از اطلاعاتی که در زمینه SQL در اختیار ما قرار دادین بسیار ممنون و سپاسگذارم ....

بنده هیچ آشنایی حتی مختصر در این زمینه ندارم!!! انشاءالله به طر کامل متن شما رو میخونم و سوالات خودمو میپرسم.

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

موفق و موید باشید

یا حق

شاخصها و پیوندها در SQL Server

بهینه ساز پرس و جوی سرویس دهنده SQL می‌تواند برای اجرای پیوندها از میان 3 استراتژی یكی را انتخاب كند: پیوند حلقه‌ای تودرتو (nested-loop)، پیوند ادغامی (merge) و پیوند hash، در این مقاله به شرح دو استراتژی اول یعنی حلقه‌ای تودرتو و ادغامی می‌پردازیم. در هر روش، جدولهایی را كه می‌خواهید پیوند دهید (یا زیر مجموعه‌هایی از آنها كه با شرط WHERE محدود كرده‌اید) ورودی‌های پیوند هستند. اگر پرس و جوی شما علاوه بر پیوند، شرط WHERE را نیز شامل شود، سرویس دهنده SQL ممكن است قبل از یافتن سطرهای موردنظر در دومین جدول شرط WHERE را بكار ببرد. بعنوان مثال پرس و جوی 1 از بانك اطلاعاتی Northwind را در نظر بگیرید.

 :Query 1
SELECT LastName, FirstName, OrderID, OrderDate
FROM Employees e JOIN Orders o
ON e.EmployeeID = o.EmployeeID

در پرس و جوی 1 سرویس دهنده SQL تمام سطرها را در جدول كارمندان و سفارشات امتحان می‌كند و ورودی‌های پیوند تمام سطرهای جدولها هستند. بهرحال، طبق آنچه كه پرس و جوی 2 نشان می‌دهد، اگر شما عبارت SELECT را با دو شرط WHERE تعریف كنید، ورودیهای پیوند دیگر تمام سطرهای جدولها نیستند.

 :Query 2
SELECT LastName, FirstName, OrderID, OrderDate
FROM Employees e JOIN Orders o
ON e.EmployeeID = o.EmployeeID
WHERE OrderDate < "1996-12-01" AND LastName < "D"

حلقه‌های تودرتو

حتی اگر پرس وجوی شما بیش از 2 جدول را پیوند دهد، سرویس دهنده SQL عمل پیوند را از طریق پیوند تنها دو ورودی در یك زمان اجرا می‌كند و هر پیوند در یك پرس و جو ممكن است از استراتژی پیوند متفاوتی استفاده كند. آسانترین نوع پیوند – و نوعی از پیوند كه اكثر افراد هنگام عملیات پیوند بدان فكر می‌كنند – پیوند حلقه تودرتوست  می‌توانید تصور كنید كه سرویس دهنده SQL روی دو ورودی عمل می‌كند حتی اگر آنها آرایه‌هایی در یك زبان پیشرفته مانند C یا Basic باشند. سرویس دهنده SQL هر سطر یك ورودی را با تمام سطرهای ورودی دیگر مقایسه می‌كند تا تطابق بین سطرها را بیابد.

پرس و جوی 1 سعی دریافتن سطرهایی دارد كه با ستون EmployeeID تطبیق داشته باشد. بنابراین با استراتژی پیوند حلقه تودرتو، سرویس دهنده SQL باید تمام مقادیر EmployeeID در یك جدول را با تمام مقادیر EmployeeID در جدول دیگر مقایسه كند.

بدترین قسمت سناریو برای یك پیوند حلقه تودرتو زمانی است كه هیچ شاخصی نتواند به سرویس دهنده SQL دریافتن سطرهای منطبق در بین ورودیها و همچنین یافتن سطرهایی كه در هر شرط WHERE صدق می‌كند، كمك نماید. در ین حالت، ورودیها كل سطرهای جدولها هستند. بهینه ساز پرس و جو جدولی را بعنوان جدول خارجی انتخاب می‌كند و در ابتدا به سطرهای آن دستیابی می‌یابد. بیایید فرض كنیم كه جدول خارجی دارای P1 صفحه و R1 سطر باشد. دومین جدول كه جدول داخلی است P2 صفحه دارد. سرویس دهنده SQL باید تمام صفحات را از جدول خارجی بخواند؛ و برای هر سطر تعریف شده در هر صفحه باید تمام صفحات را از جدول داخلی بخواند. برای یافتن تعداد صفحاتی كه سرویس دهنده SQL برای خواندن و ارائه نتیجه نیاز دارد، می‌توانید از فرمول زیر استفاده كنید :

P1 + R1 * P2

شاخصها می‌توانند در بهبود عملكرد یك پیوند حلقه تودرتو به طرق مختلف نقش داشته باشند. بزرگترین حسن این شاخصها اغلب زمانی است كه شما یك شاخص كلاستری روی ستون پیوند یكی از جدولها داشته باشید. وجود یك شاخص كلاستری روی ستون پیوند غالباً مشخص می‌كند كه سرویس دهنده SQL چه جدولی را بعنوان جدول داخلی انتخاب می‌كند. اگر جدول داخلی دارای شاخص كلاستری باشد، سرویس دهنده SQL نیاز به جستجو در میان كل سطرهای آن جدول را ندارد. شاخص كلاستری، سرویس دهنده SQL را مستقیماً به سوی سطرهایی در جدول داخلی هدایت می‌كند كه دارای مقدار ستون پیوند بوده كه سطرهای جاری در جدول خارجی را تطبیق می‌دهد. بنابراین در آن فرمول، به جای عبارت R1 ´ P2 كه نشان می‌دهد سرویس دهنده SQL به تمام P2 صفحه دستیابی پیدا می‌كند، می‌توانید P2 را با دستیابی 2 یا 3 صفحه‌ای جایگزین كنید بسته به اینكه شاخص كلاستری چند Level دارد. بنابراین در مورد مثالی با 200 صفحه و 400 سطر در جدول خارجی و 100 صفحه در جدول داخلی نتیجه 3*4000+200 یا 200،12 صفحه خوانده شده است – یك پیشرفت بزرگ بالای 400,000 صفحه می‌باشد.

هنوز هم آن 4000 سطر در محاسبه نتیجه را بزرگتر از حد انتظار خواهد كرد. در این حالت، تمامی 4000 سطر در جدول خارجی بخشی از نتیجه هستند كه موجب 4000 بار رجوع به جدول داخلی می‌شود. یك راه دیگر برای كاهش تعداد صفحات بدست آمده كاهش اندازه ورودیهای خارجی است. علاوه بر كنترل شاخص كلاستری روی ستون پیوند، ابتدا بهینه ساز سعی می‌كند جدولها را با ورودیهای كوچكتر پیوند دهد. در پرس و جوی 1، جدول كارمندان دارای یك شاخص كلاستری روی ستون پیوند یعنی EmployeeID هستند اما این جدول نیز بطور نمایشی كوچكتر از جدول سفارشات است. جدول كارمندان تنها 9 سطر دارد و جدول سفارشات 830 سطر. در پرس و جوی 1، اگر بهینه ساز یك پیوند حلقه تودرتو را انتخاب كند، از جدول كوچكتر كارمندان به عنوان ورودی خارجی استفاده می‌كند بگونه‌ای كه تنها 9 بار به جدول سفارشات رجوع خواهد داشت.

اگر شما دارای شرط WHERE باشید كه جدول خارجی را شامل می‌شود، تعداد سطرهای تعریف شده پایین می‌آید و سرویس دهنده SQL كمتر نیاز به مراجعه به جدول داخلی را خواهد داشت. اگر پرس و جوی 1 را طوری تغییر دهید كه شامل یك شرط WHERE در جدول سفارشات باشد، همانگونه كه در پرس و جوی 3 نشان داده شده، طرح پرس و جو تغییر می‌كند.

: Query 3
SELECT LastName, FirstName, OrderID, OrderDate
FROM Employees e JOIN Orders o
ON e.EmployeeID = o.EmployeeID
WHERE OrderDate < "1996-12-01"

حالا، تنها 121 سطر در جدول سفارشات بخشی از نتیجه هستند، آن جدول كوچكتر كه با شاخص كلاستری روی ستون پیوند جدول كارمندان تركیب شده این مفهوم را می‌رساند كه بهینه ساز حالا جدول كارمندان را به عنوان جدول داخلی انتخاب می‌كند. سرویس دهنده SQL از پیوند حلقه تودرتو استفاده خواهد كرد چرا كه شاخص كلاستری باعث می‌شود سرویس دهنده SQL سریعاً سطرهای منطبق شده را در جدول داخلی بیابد.

FIGURE 1: Query plan for Query 3
|..Nested Loops(Inner Join, OUTER REFERENCES:([o].[EmployeeID]))
|..Clustered Index Scan(OBJECT:([northwind].[dbo].[Orders].[PK_Orders]
AS [o]),
WHERE:([o].[OrderDate] < "Dec 1 1996 12:00AM"))
|..Clusterd Index
Seek(OBJECT:([northwind].[dbo].[Employees].[PK_Employees] AS [e]),
SEEK:([e].[EmployeeID]=[o].[EmployeeID]) ORDERED FORWARD)

اسكن شاخص كلاستری در جدول خارجی شبیه اسكن یك جدول است زیرا هیچیك از شاخصهای موجود نمی‌تواند دستیابی به جدول خارجی را سرعت ببخشد. شرط WHERE در ستون OrdrerDate تعداد سطرهای برگردانده شده و تعداد دفعاتی كه سرویس دهنده SQL باید به جدول داخلی دستیابی داشته باشد تا تعیین كند كدامیك مقدار OrderDate قابل قبولی دارند. در نهایت، طرح پرس و جو نشان می‌دهد كه سرویس دهنده SQL از یك شاخص كلاستری برای جستجوی جدول داخلی استفاده می‌كند زیرا این شاخص روی ستونی است كه سرویس دهنده SQL برای یافتن سطرهای منطبق استفاده می‌كند.

ادغام

در پیوند حلقه تودرتو، شاخص ستون پیوند در مورد جدول خارجی بی‌فایده است. بهرحال، زمانی كه شما پرس و جوها و جداول را تنظیم می‌كنید، ممكن است همیشه ندانید كه كدام جدول داخلی و كدام خارجی است، بنابراین باید در هر دو جدول ورودی شاخصهای كلاستری را روی ستونهای پیوند ایجاد كرد. زمانی كه هر دو ورودیهای پیوند روی ستون پیوند مرتب سازی می‌شوند، سرویس دهنده SQL می‌تواند از پیوند ادغامی استفاده كند، درست مانند موردی كه هر دو جدول دارای شاخصهای كلاستری روی ستون پیوند باشند.پیوند ادغامی را می‌توان همچون تركیب دویست مرتب سازی شده از مقادیر تصور كرد. فرض كنید دارای دو سری از اطلاعات پیمانكاری هستید. یك سری شامل قراردادهای مهم می‌باشد كه هر پیمانكاری آن را امضا كرده است و دومین سری توصیف هریك از پروژه‌هایی است كه پیمانكار بر روی آن كار می‌كند بنابراین، شما اساساً نیاز به یك پل ارتباطی میان این دو سری اطلاعات دارید.پرس و جوی 1 را در نظر بگیرید: اگر جدول كارمندان و جدول سفارشات در ستون EmployeeID شاخصهای كلاستری داشته باشند، سرویس دهنده SQL می‌تواند پیوند ادغامی را اجرا كند. شبه كد مربوطه در مورد اجرای ادغامی سرویس دهنده SQL چیزی شبیه این عبارات خواهد بود:

GET one Orders row and one Employees row
;(DO(until one input is empty
IF EmployeeID values are equal
Return values from both rows
GET next Orders row
ELSE IF Orders.EmployeeID <> Employees.EmployeeID
GET next Employees row
ELSE GET next Orders row

بهینه ساز پرس و جو معمولاً استراتژی پیوند ادغامی را زمانی انتخاب می كند كه هر دو ورودیها قبلاً در ستون پیوند مرتب شده باشند. اگر هر دو ورودی قبلاً مرتب شده باشند، در صورتی كه پیوند یك به چند باشد استفاده از I/O كمتری برای پردازش پیوند ادغامی ضروریست. پیوند ادغامی چند به چند (M:N) به جای كنار گذاشتن سطرها كه معمولاً انجام می‌دهد، آنها را در یك جدول موقتی ذخیره می‌كند. اگر داده‌ها شامل مقادیر تكراری از هر دو ورودی باشند، هنگامی كه سرویس دهنده SQL هر مقدار تكراری را از اولین ورودی پردازش می‌كند، دومین ورودی باید به ابتدای مقادیر تكراری در جدول موقت بازگردد. بهرحال، در اكثر موارد، سرویس دهنده SQL از پیوند ادغامی استفاده نخواهد كرد مگر اینكه حداقل یكی از ستونهای پیوند Unique باشد.در اینجا مثالی از پیوند دو جدول یكی با شاخص و دیگری بدون شاخص Unique آورده شده است.

 لیست 1 كپی‌هایی از جدول سفارشات و جدول جزئیات سفارشات در بانك اطلاعاتی Northwind ایجاد می‌كند و یك شاخص كلاستری در OrderID هر دو جدول می‌سازد. زمانی كه شما ابتداً این جدولها را پیوند می‌دهید و طرح پرس و جو را به نمایش می‌گذارید، خواهید دید كه سرویس دهنده SQL پیوند حلقه تودرتو را انتخاب می‌كند. اگرچه ستون OrderID در جدول سفارشات Unique است، اما در صورتی كه Unique بودن را در تعریف شاخص مشخص نكنید، بهینه ساز متوجه نخواهد شد كه مقادیر كلیدی Unique هستند. بنابراین هنگامی كه مجدداً شاخص كلاستری را در جدول سفارشات می‌سازید و مشخص كنید كه آن شاخص باید Unique باشد، طرح پرس و جوی اصلاح شده نشان می‌دهد كه سرویس دهنده SQL از یك پیوند ادغامی استفاده می‌كند. در برخی از حالتها، بهینه ساز سرویس دهنده SQL ممكن است به دلیل به صرفه بودن تصمیم بگیرد یكی از ورودیها را قبل از پیوند مرتب كند و بعد پیوند ادغامی را اجرا نماید. اگر لیست 1 را كمی تغییر دهید به گونه‌ای كه شاخص اولیه كه در جدول جزئیات سفارشات ساخته می‌شود كلاستری نباشد، طبق آنچه كه لیست 2 نمایش می‌دهد، طرح پرس و جو عمل مرتب سازی را قبل از پیوند ادغامی نشان می‌دهد.