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

قبل از یادگیری گرامر دستوری ساخت جدول ، باید منطق و اصول ساخت جدول را بیاموزید . جداول به سطرها (ROW) و ستون ها (COLUMN)  تقسیم میشوند . هر سطر یک رکورد از اطلاعات را در خود جای داده است.هر سطر چند فیلد دارد .

مثال : مثلا کارنامه مدارس را در نظر بگیرید .مثلا 3 ستون دروس ، نمره و تاریخ دارد و سطر های آن هر کدام شامل سه بخش هستند طبق دسته بندی ستون هایش .

سطر اول : ریاضی – 12 - 12/3/1384

سطر دوم : فیزیک – 14 - 16/3/1384

و بهمین ترتیب .پس هر سطر جدول را یک رکورد می نامیم . هر ستون جدول (فیلد) دارای یک نوع داده است . در مثال فوق ستون دروس از نوع string و ستون نمرات از نوع integer و ستون تاریخ از نوع date است . هنگام ساخت جدول که فیلد هایش را مشخص میکنید باید نوع داده ای فیلد ها نیز مشخص گردد. کد ساخت جدول بصورت زیر میباشد :

CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
... ) 

کد ساخت یک جدول نمونه :

CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)

 DROP TABLE : گاهی ما دلمون میخواد که بهر دلیلی از دست یک جدول در پایگاه داده خود راحت شویم . SQL این امکان رو برای ما فراهم کرده و ما میتونیم برای پاک کردنش از کد زیر استفاده کنیم .

DROP TABLE "table_name" 

مثال :

DROP TABLE customer.

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

TRUNCATE TABLE "table_name" 

مثال :

TRUNCATE TABLE customer.

 INSERT INTO : برای ورود اطلاعات داخل یک جدول دو راه وجود دارد. یکی اینکه رکورد ها را تک تک وارد کنیم (سطر به سطر) و یکی اینکه یکدفعه چندین سطر را وارد جدول کنیم . برای وارد کردن اطلاعات بصورت سطر به سطر با کد زیر عمل میکنیم .

INSERT INTO "table_name" ("column1", "column2", ...) VALUES ("value1", "value2", ...) 

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

Table Store_Information

و حالا ما میخواهیم یک رکورد(سطر) اطلاعات وارد جدول فروش کنیم . با مشخصات ذیل : لوس آنجلس – 10 ژانویه 1999 و فروش 900$ . بنابرین کد زیر را استفاده میکنیم.

INSERT INTO Store_Information (store_name, Sales, Date) VALUES ('Los Angeles', 900, 'Jan-10-1999') 

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

INSERT INTO "table_name" ("column1", "column2", ...) SELECT "column1", "column2", ... FROM "table1" 

توجه کنید که کد بالا ساده ترین حالت ممکن بود .جمله select شما می تواند دارای کلمات  where , Group by , Having باشد .

بطور مثال اگر ما جدولی داشته باشیم که بخواهیم اطلاعات فروش سال 1998 را بگیرد و هم اکنون جدول دیگری دارید که اطلاعات تمام سال های 1995 تا 2000 را شامل میشود و اگر ساختار دو جدول یکسان باشد باید تایپ کنید :

INSERT INTO Store_Information (store_name, Sales, Date) SELECT store_name, Sales, Date FROM Sales_Information WHERE Year(Date) = 1998 

در اینجا من اطلاعات را بر اساس یک تاریخ استخراج کردم و در جدول دیگری کپی کردم. اگر رابطه ها را در حالات دیگر بسازید کمی با این حالت فرق دارد . برای مثال در اوراکل تکه آخر کد بصورت زیر در میاید :

WHERE to_char(date,'yyyy')=1998 

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

UPDATE "table_name" SET "column_1" = [new value] WHERE {condition} 

مثال : ما جدول زیر را داریم .

Table Store_Information

مثلا ما متوجه میشویم که مقدار فروش فروشگاه لوس آنجلس در تاریخ 01/08/1999 در اصل 500$ بوده و باید این مقدار را در جدول تصحیح کنیم . برای اینکار از کد زیر استفاده میکنیم.

UPDATE Store_Information SET Sales = 500 WHERE store_name = "Los Angeles"  AND Date = "Jan-08-1999"

بعد از اجرای کد بالا مقادیر جدول بصورت زیر در میاید .

Table Store_Information

در جدول مذکور فقط یک رکورد بود که تاریخش Jan-08-1999 و نام فروشگاهش Los Angeles باشد . اگر لچند رکورد با این مشخصات وجود داشت نیز تمام آنها مقدارشان تغییر میکرد . در مثال فوق فوق یک رکورد وجود داشت با مشخصاتی که ما دادیم و فقط هم یک فیلدش را تغییر دادیم .

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

UPDATE TABLE "table_name" SET ("column_1", "column_2") = ([new value 1], [new value 2]) WHERE {condition}

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

DELETE FROM "table_name" WHERE {condition}

مثال : جدول زیر را داریم .

Table Store_Information

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

DELETE FROM Store_Information WHERE store_name = "Los Angeles"

نتیجه:

Table Store_Information

1384/02/23

نویسنده : حسين شرفي

این دستور نیز با دو جمله SQL کار میکند. در ابتدا تمام نتایج query اول را میگیرد و سپس از میان نتایج ، آنهایی را که در query دوم هستند حذف میکند .

قالب دستوری :

[SQL Statement 1]
MINUS
[SQL Statement 2]

 در اینجا میخواهیم تاریخ هایی را که در آنها فقط فروش در فروشگاه داشته این بیابیم.(فروش روی اینترنتی اصلا نمیخواهیم)

Table Store_Information

Table Internet Sales

SELECT Date FROM Store_Information MINUS SELECT Date FROM Internet_Sales

نتیجه :

1384/02/23

نویسنده : حسين شرفي

مانند دستور union  این دستور نیز با دو جمله SQL کار میکند . اما تفاوت اینجاست که دستور union  در اصل مانند یک OR  کار میکند و تمام رکوردهای غیر تکراری را بر میدارد . اما این دستور مانند AND کار میکند و رکورد های مشترک را بر میدارد .

قالب دستوری :

[SQL Statement 1]
INTERSECT
[SQL Statement 2]

مثال : جداول زیر را در نظر بگیرید . ما میخواهیم تمام تاریخ هایی را که در آن معامله ای انجام شده است استخراج کنیم . بنابرین از کد زیر استفاده میکنیم . تفاوت را با union مقایشه کنید .

Table Store_Information

Table Internet Sales

SELECT Date FROM Store_Information INTERSECT SELECT Date FROM Internet_Sales

نتیجه :

1384/02/23

نویسنده : حسين شرفي

هدف دستور  Union all نیز ترکیب کردن نتایج حاصل از دو query  با یکدیگر میباشد . اما فرقی که با union دارد این است که هنگام استفاده از union فقط مقادیر متمایز نشان داده میشود ، اما با union all تمام مقادیر برگردانده میشود.

قالب دستوری :

[SQL Statement 1]
UNION ALL
[SQL Statement 2]

مثال : جداول زیر را در نظر بگیرید . ما میخواهیم تمام تاریخ هایی را که در آن معامله ای انجام شده است استخراج کنیم . بنابرین از کد زیر استفاده میکنیم . تفاوت را با union مقایشه کنید .

Table Store_Information

Table Internet Sales

SELECT Date FROM Store_Information UNION ALL SELECT Date FROM Internet_Sales

نتیجه :

1384/02/19

نویسنده : حسين شرفي

هدف دستور Union ترکیب کردن نتایج حاصل از دو query  با یکدیگر میباشد. در این رابطه میتوان گفت که union چیزی شبیه به join میباشد . چون هر دوی آنها برای ایجاد رابطه بین اطلاعات جداول مختلف استفاده میشوند . یکی از محدودیت های union این است که این ستون های متناظر با هم باید از یک نوع داده اطلاعات باشند. همچنین هنگام استفاده ار union فقط مقدار های متمایز انتخاب میشوند .(مانند select Distinct ) .

قالب دستوری :

[SQL Statement 1]
UNION
[SQL Statement 2]

مثال : جداول زیر را در نظر بگیرید . ما میخواهیم تمام تاریخ هایی را که در آن معامله ای انجام شده است استخراج کنیم . بنابرین از کد زیر استفاده میکنیم .

Table Store_Information

Table Internet Sales

SELECT Date FROM Store_Information UNION SELECT Date FROM Internet_Sales

نتیجه :

توجه کنید که اگر ما از SELECT DISTINCT Date استفاده کنیم ، برای یکی یا هر دو عبارت، ما نتایج یکسانی را بدست خواهیم آورد .

1384/01/19

نویسنده : حسين شرفي

قبلا دیدیم که چطور اطلاعات را با ایجاد اتصال بین دو جدول استخراج کردیم . در این حالت می خواهیم اطلاعات را از جدولی انتخاب کنیم صرفنظر ازینکه آنها در جدول دیگر ارائه شده اند یا خیر . برای اینکار باید از outer join استفاده کنیم .

این دستور در دیتابیس ها ، وابسته به نوع دیتابیس است . بطور مثال در اوراکل ما باید یک علامت "(+)" در کلمه  where قرار دهیم .

Table Store_Information

Table Geography

جداول بالا موجود است و ما میخواهیم فروش تمام فروشگاه ها را که در جدول Geography آمده بدست آوریم . اگر مااز حالت join معمولی استفاده کنیم اطلاعات درستی بدست نمی آوریم ، چون مقدار New York را در جدول دوم به ما نشان نخواهد داد . چرا ؟ چون این مقدار در جدول اولی موجود نیست .

SELECT A1.store_name, SUM(A2.Sales) SALES FROM Georgraphy A1, Store_Information A2 WHERE A1.store_name = A2.store_name (+) GROUP BY A1.store_name

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

نتیجه :

نکته : وقتی که رکوردی در جدول دوم همتایش موجود نباشد NULL برگردانده میشود . در این حالت New York در جدول Store_Information موجود نمی باشد .

نویسنده : حسين شرفي

در این بخش برای یادگیری join نیاز به بسیاری از کلمات کلیدی ای داریم که در دروس قبل آموختیم. به دو جدول زیر توجه کنید .

Table Store_Information

Table Geography

چیزی که ما نیاز داریم اینه که میخواهیم فروش را در هر منطقه جغرافیایی بدست بیاریم . می بینیم که جدول Geography اطلاعات مناطق جغرافیایی را در بر دارد و جدول Store_Information اطلاعات فروش شهر ها را . برای بدست آوردن فروش در هر منطقه ، ما باید اطلاعات بدست آمده از هر منطقه را در هم ترکیب کنیم . با نگاهی به دو جدول می فهمیم که یک فیلد مشترک در هر دو موجود است و آن store_name میباشد که با یک کاما بهم وصل میشوند .

کد زیر را ببینید تا توضیحات را شرح دهیم .

SELECT A1.region_name REGION, SUM(A2.Sales) SALES FROM Geography A1, Store_Information A2 WHERE A1.store_name = A2.store_name GROUP BY A1.region_name

نتیجه :

دو خط اول کد به sql میگوید که دو فیلد را انتخاب کند . اولین خط فیلد region_name است از جدول Geography که نام مستعار REGION گرفته است . و دومین خط حاصل فیلد sales از جدول Store_Information که نام مستعار sales گرفته است . دقت کنید که چگونه مستعار های جدول در اینجا بکار گرفته شده . Geography بعنوان A1 مستعار شده و Store_information بنام A2  . بدون استفاده از مستعار ها خط اول بصورت زیر در میاید .

SELECT Geography.region_name REGION, SUMStore_Information.Sales) SALES

اساسا کار مستعار ها قابل درک کردن کد های طولانی SQL است . بخصوص هنگامی که چند جدول در کار است .

حال بخط سوم کد اصلی توجه کنید ،یعنی کلمه where  . اینجا جائیست که join کارش معلوم میشود . در این حالت ما میخواهیم مطمئن شویم که محتوای "store_name"  در جدول Geography با مقادیر آن در جدول Store_Information نظیر میشود و راهش اینه که آنها را برابر هم قرار دهیم . کلمه where لازمه که مطمئن شویم که اطلاعات درست بدست میاید . بدون یک کلمه where درست ، یک نتیجه join دکارتی بدست میاید . نتیحه دکارتی در اصل ترکیبی از تمام حالات بوجود آمده از دو جدول را ارائه میکند . یعنی نتیجه برابر میشه با  4 x 4 = 16

1384/02/19

نویسنده : حسين شرفي

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

بطور خلاصه نام مستعار ستون برای سامان دادن به خروجی استفاده میشود . یعنی نام بالای ستون ها در خروجی قابل تغییر است .

نوع دوم نام مستعار جداول است : با نوشتن نام مستعار درست بعد از نام جدول بعد از From انجام میشود. راحتی استفاده از این کلمه کلیدی در هنگام استفاده از دو جدول در استخراج جداول است که اطلاعات توسط کلمه join  متحد و سپس استخراج می گردد .قبل از اینکه به Join برسیم بیایید نگاهی به alias بیندازیم .

SELECT "table_alias"."column_name1" "column_alias" FROM "table_name" "table_alias"

بطور خلاصه هر دو نوع مستعار ها بعد از آیتمی میایند که نام مستعارش هستند . البته با یک فاصله .

مثال: جدول زیر را در نظر بگیرید .

Table Store_Information

SELECT A1.store_name Store, SUM(A1.Sales) "Total Sales" FROM Store_Information A1 GROUP BY A1.store_name

نتیجه

1384/02/19

نویسنده : حسين شرفي

یکی دیگر از چیزهایی که ممکنه در استخراج اطلاعات نیاز داشته باشیم گزیدن اطلاعات بر اساس یک مقیاس است . بطور مثال ممکنه بخواهیم فروشگاه هایی که بیش از 1500 دلار فروش دارند را استخراج کنیم . بجای استفاده از where ما باید از HAVING استفاده کنیم که برای فانکشن های جمعی استفاده میشه . Having معمولا در انتهای جمله query شما استفاده میشود . جمله ای که having دارد میتواند group by هم داشته باشد .

SELECT "column_name1", SUM("column_name2") FROM "table_name" GROUP BY "column_name1" HAVING (arithematic function condition)

مثال: جدول زیر را در نظر بگیرید .

Table Store_Information

SELECT store_name, SUM(sales) FROM Store_Information GROUP BY store_name HAVING SUM(sales) > 1500

نتیجه

1384/02/19

نویسنده : حسين شرفي

 حالا ما بسوی فانکشن های جمع بندی میریم . یادتون هست که از فانکشن SUM برای محاسبه کل فروش استفاده کردیم ؟ حالا اگر بخواهیم کل فروش فروشگاه های هر شهر را بدست آوریم چه باید بکنیم ؟ اینجا 2 چیز لازم است. اول باید نام فروشگاه ها را مانند کل فروش جمع ببندیم و سپس باید مطمئن شویم که تمام فروش ها طبق نام فروشگاه ها باید دسته بندی شود .

SELECT "column_name1", SUM("column_name2") FROM "table_name" GROUP BY "column_name1"

مثال: جدول زیر را در نظر بگیرید .

Table Store_Information

SELECT store_name, SUM(Sales) FROM Store_Information GROUP BY store_name

نتیجه

کلمه کلیدی Group By وقتی استفاده میشود که ما در حال انتخاب چند ستون هستیم و حداقل یک عملگر محاسباتی در عبارت select داریم . در این زمان ما باید تمام ستونهای دیگر را گروه کنیم.

1384/02/18

نویسنده : حسین شرفی

یکی دیگر از فانکشن ها COUNT است . این به ما اجازه میدهد تا تعداد سطرهای یک جدول را بشماریم . گرامر دستوری بصورت زیر است .

SELECT COUNT("column_name") FROM table_name

مثال: شمردن سطر های جدول فروش :

Table Store_Information

SELECT COUNT(store_name) FROM Store_Information

نتیجه

count(store_name)

4

COUNT و DISTINCT را میتوان برای بدست آوردن مقادیر خاص جدول، با هم در یک عبارت نوشت . مثلا اگر ما بخواهیم تعداد فروش های ویژه جدول را بشماریم باید تایپ کنیم :

SELECT COUNT(DISTINCT store_name) FROM Store_Information

نتیجه:

Count(DISTINCT store_name)

3

1384/02/18

نویسنده : حسين شرفي

از وقتی ما در حال کار با اعداد هستیم سوالی که در مرحله دوم برای ما پیش میاید اینه که آیا میشه اعمال حسابی روی این اعداد انجام داد ؟ مثلا میانگین اعداد رو بگیریم .جواب مثبته .SQL چندین عملگر ریاضی داره از جمله SUM  و AVG .

SELECT function type(column_name") FROM table_name

مثال: اگر ما بخواهیم جمع کل فروش های جدول را بدست آوریم بصورت زیر عمل میکنیم.

Table Store_Information

SELECT SUM(Sales) FROM Store_Information

نتیجه

SUM  : Sales

$2750

1384/02/18

نویسنده : حسين شرفي

تا کنون دیدیم که چگونه اطلاعات را از جداول استخراج می کردیم . حال برای مرتب سازی اطلاعات بر حسب حروف یا اعداد بر روی یکی از فیلد ها ازین کلمه استفاده میکنیم .

علامات [] در اطراف where بدین معناست که میتوانید where را بکار نبرید. اما اگر بکار بردید حتما باید قبل از order باشد . ASC به معنای صعودی بودن (a to z)و DESC به معنای نزولی بودن است (z to a).پیش فرضش ACS است.

همچنین این نیز امکان پذیرست که مرتب سازی را بر مبنای بیش از یک ستون انجام دهید .برای این منظور بخش مرتب سازی کد بالا بصورت زیر در میاید :

ORDER BY "column_name1" [ASC, DESC], "column_name2" [ASC, DESC

مثال :فرض کنید جدولی مانند زیر داریم . نتیحه را طبق دستور داده شده مشاهده کنید .

Table Store_Information

SELECT * FROM Store_Information ORDER BY Sales DESC
نتیجه

1384/02/17

نویسنده : حسين شرفي

این کلمه نیز با کلمه where بکار میرود . اساسا like به شما اجازه میدهد که جستجویی را بر مبنای یک پیش زمینه ای که دارید انجام دهید . (مثلا انتخاب رکوردهایی که یکی از فیلدهایش با کلمه خاصی شروع میشود .)

SELECT "column_name" FROM "table_name" WHERE "column_name" LIKE {PATTERN}

روش ساخت پترن بکمک مثال:

'ABC%' : تمام کلماتی که با ABC شروع میشوند.

'%XYZ' : تمام کلماتی که به XYZ خاتمه می یابند.

'%AN%' : تمام کلماتی که شامل عبارت AN هستند .

مثال :فرض کنید جدولی مانند زیر داریم . نتیحه را طبق دستور داده شده مشاهده کنید .

Table Store_Information

SELECT * FROM Store_Information WHERE store_name LIKE '%AN%'

نتیجه

1384/02/17

نویسنده : حسین شرفی

BETWEEN :

همانطور که کلمه IN به ما کمک میکند تا محدوده انتخاب را به یک یا چند مقدار محدود کنیم ، کلمه BETWEEN  کمک میکند تا محدوده را یک بازه تعریف کنیم . در این حال تمام مقادیری که بین value1 و value2 باشند انتخاب میشوند .

SELECT "column_name" FROM "table_name" WHERE "column_name" BETWEEN 'value1' AND 'value2'

مثال :فرض کنید جدولی مانند زیر داریم .رکوردهایی که تاریخ آنها بین January 6, 1999  و January 10, 1999 است را میخواهیم .

Table Store_Information

SELECT * FROM Store_Information WHERE Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999'

نتیجه

1384/02/17

نویسنده : حسين شرفي