» آشنایی با زبان SQL + نکات مهم

مهرگان
 
 
 

آشنایی با زبان SQL + نکات مهم

3 مرداد 1391

آشنایی با زبان SQL + نکات مهم

تعریف بانک اطلاعاتی:

برای تعریف بانک اطلاعاتی از دستور زیر استفاده می شود؛

CREATE DATABASE DB-Name AUTHORIZATION DBA-Name;

DB-Name: نام بانک اطلاعاتی

DBA-Name: نام کاربر مجاز به دسترسی

جدول ها نیز به این صورت تعریف می شوند؛

CREATE TABLE Table-Name

( attribute domain NOT NULL,

attribute domain NOT NULL,

attribute domain,

... ... ...

PRIMARY KEY(attribute-name),

UNIQUE(attribute-name),

FOREIGN KEY(f-key)REFERENCES table

ON DELETE CASCADE,

ON UPDATE CASCADE,

CHECK(condition)

);

table-name: نام جدولی است که میخواهیم آن را ایجاد کنیم.

attribute domain : این خط برای تعریف صفتها(ستونها)ی جدول استفاده می شود. domain دامنه ی تغییر صفت را نشان می دهد. و عبارت NOT NULL نشان می دهد که این صفت نمی تواند تهی باشد.

PRIMARY KEY: کلید اصلی جدول را مشخص میکند. توجه داشته باشید که این صفت نمی تواند تهی یا تکراری داشته باشد.

UNIQUE: همانند کلید اصلی، نباید تکراری یا تهی باشد. و در بعضی اوقات بجای کلید اصلی استفاده می شود.

FOREIGN KEY: این دستور، کلید خارجی جدول را مشخص می کند که همان کلید اصلی در جدول دیگر است. و به منظور ارتباط دهی بین جدول ها مورد استفاده قرار می گیرد. کلید خارجی، همانند سایر کلید ها ممکن است یک صفت باشد و یا از ترکیب چند صفت ایجاد شده باشد.

دو دستور ON UPDATE CASCADE و ON DELETE CASCADE بیان می کنند که اگر کلید خارجی در جدول خودش حذف شد، یا تغییر کرد، در اینجا نیز مشابه همان انجام شود.

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

CREATE ASSERTION table-name

CHECK(condition);

تغییر نوع داده ها:

دستور زیر برای تغییر نوع داده های یک ستون یا تغییر دامنه ی تعریف آن است؛
ALTER TABLE table-name
MODIFY(column-name new-type);

توجه داشته باشید که تغییر نوع داده های جدول ممکن است باعث از بین رفتن برخی اطلاعات ذخیره شده شود. بنابراین اکثر نسخه های SQL از انجام این عمل جلوگیری می کنند. مگراینکه ستون مورد نظر فاقد داده باشد. در مورد تغییر دامنه ی تعریف متغییر نیز معمولا؛ به کاربر هشدار داده می شود و عواقب و مشکلات احتمالی که ممکن است بوجود بیاید تاکید می شود.

افزودن یک یا چند صفت به جدول:
این کار با دستور زیر قابل انجام است؛
ALTER TABLE table-name
ADD(column-name column-type);

مثلاً اگر بخواهیم ستونی به نام text و به فرمت (char(80 به جدول Book اضافه کنیم، دستور زیر را صادر می کنیم:

ALTER TABLE Book

ADD (text char(80));

وارد کردن داده ها:

وارد کردن داده ها در جدول بصورت سطری انجام می شود. (هر سطر در SQL یک رکورد نامیده می شود). توجه داشته باشید که ستون هایی که دارای قید NOT NULL هستند باید حتماً مقدار دهی شوند، اما بقیه اجباری نیست.
وارد کردن یک سطر به جدول با دستور زیر انجام می شود؛

INSERT INTO table-name

VALUES(field1,field2, ... );

به عنوان مثال اگر بخواهیم ستونهای s-name و s-number از جدول trust را در جدول book کپی کنیم، دستور را بصورت زیر می نویسیم؛

INSERT INTO book

SELECT s-name , s-number

FROM trust;

حذف داده ها:

به عنوان مثال اگر بخواهیم سطرهایی از جدول trust که ستونهای s-name و s-number در آنها خالی است را حذف کنیم، دستور آن را بصورت زیر می نویسیم؛

DELETE FROM trust

WHERE ((s-name IS NULL)AND(s-number IS NULL));

در برخی نرم افزارها امکان حذف جدولهای غیر تهی وجود ندارد. در این صورت ابتدا باید داده های جدول را کاملاً پاک کرده سپس دستور حذف جدول خالی را صادر کنیم.

تغییر داده ها در جدول:

تغییر داده ها در جدول با دستور UPDATE انجام می شود و شکل کلی آن بصورت زیر است؛

UPDATE table-name

SET attribute1=value1, attribute2=value2

WHERE ... ... ...

استخراج اطلاعات از پایگاه داده ها:

استخراج داده ها در SQL به وسیله ی عملگر قدرتمندی به نام SELECT انجام می شود که بصورت زیر تعریف می گردد؛

SELECT column1, column2, ...

FROM table1, table2, ...

WHERE ...

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

استفاده از عملگرهای رابطه ای در SQL:

عملگر IN:

مثال:

SELECT *

FROM table1

WHERE this-column IN(c1,c2,c3);

این Query، همه ی سطرهایی از جدول table1 که ستون this-column در آن، دارای مقداری باشد که در مجموعه ی (c1,c2,c3) قرار داشته باشد را بر می گرداند.

عملگر NOT:

مثال:

SELECT *

FROM table1

WHERE NOT(this-column='Ali');

این Query، همه ی سطرهایی از جدول table1 که ستون this-column در آن دارای مقدار 'Ali' نباشد را بر می گرداند.

عملگر AND:

مثال:

SELECT *

FROM table1

WHERE ((name='Mohammad') AND (Family='Sadat Shahabi'));

این Query، همه ی سطرهایی از جدول table1 که ستون name برابر با 'Mohammad' و ستون family برابر با 'Sadat Shahabi' باشد را بر می گرداند.

عملگر OR:

مثال:

SELECT *

FROM table1

WHERE ((name='Mohammad') OR ( name ='Sadegh'));

این Query، همه ی سطرهایی از جدول table1 که ستون name برابر با 'Mohammad' یا'Sadegh' باشد را بر می گرداند.

مقایسه ی عملرها در ACCESS:

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

شما می توانید از هرکدام از عملگر های SQL در شرط جستجو استفاده کنید.فقط در هنگام استفاده از عملگرها، قواعد زیر را بکار بگیرید:

نوع داده هایی که برای مقایسه استفاده می کنید باید با هم سازگار باشند. یعنی عدد باید با عدد مقایسه شود و متن با متن و ... . شما می توانید از توابع یا کلید واژه ها(مانند CAST) برای تبدیل موقت نوع داده ای به نوع داده ی دیگر استفاده کنید.
اگر داده های متنی را با هم مقایسه می کنید، نتیجه ی مقایسه به مجموعه ی کاراکتری فعلی که استفاده می کنید بستگی دارد.
اگر ارزش حاصل از مقایسه null باشد، نتیجه شناخته شده نیست. مقادیر null با سایر valueها سازگار نیستند. برای مثال، اگر شما برای پیدا کردن نامهایی که با M شروع می شوند جستجو انجام دهید، و بعضی از ردیفها شامل ارزش null باشند، آن ردیفها ظاهر نمی شوند و اهمیتی ندارد که از چه عملگر شرطی ای استفاده کرده باشید.

در ادامه، عملگرهای شرطی جستجو که برای SQL استاندارد تعریف شده اند بطور خلاصه آورده شده است؛

عملگر تساوی(=):

SELECT fname, lname

FROM table1

WHERE lname = 'Mohammad';

عملگر عدم تساوی(=! یا <>):

SELECT fname, lname

FROM table1

WHERE Date <> '1391/04/23'
عملگرهای بزگتر بودن یا کوچکتر بودن():

SELECT fname, lname

FROM table1

WHERE Date > '1391/04/23'

//---------------------------

SELECT fname, lname

FROM table1

WHERE Date = 'T'
عملگر کوچکتر یا مساوی بودن(=> و >!):

SELECT fname, lname

FROM table1

WHERE Date = all

(SELECT prise FROM book

WHERE Auther-name='Mohammad Shahabi')
عملگر EXIST یا NOT EXIST:

SELECT product_name

FROM products

WHERE EXISTS

(SELECT * FROM

orders, products

WHERE orders.prod_id

= products.prod_id)

حالا، به چند مثال از نحوه ی نوشتن Query دقت کنید؛

مثال1: نمایش ستون قیمت با %10 تخفیف:

SELECT kala-name, (price * 0.9)

FROM products

مثال2: نمایش نام و نام خانوادگی دانشجویان (به عنوان یک ستون،با جداکننده ی ویرگول) که نام خانوادگی آنها با A شروع نمی شود:

SELECT (lname + ', ' + fname)
FROM student

WHERE lname NOT LIKE 'A%';

مثال3: پیوند دادن دو جدول، و سپس مرتب سازی جدول حاصل:

SELECT sales.qty, titles.price
FROM sales INNER JOIN titles ON
sales.title_id = titles.title_id

ORDER BY
(sales.qty * titles.price)

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

SELECT lname, fname
FROM student
WHERE
((SUBSTRING(phone, 1, 3) = '021')OR(SUBSTRING(phone,1,4)='0151'))

*(توجه داشته باشید که تعیین شهر محل سکونت دانشجو، بر اساس پیش شماره تلفن شهر محل سکونت مشخص شده است).

مثال5: لیست شماره ی سفارشات ده روز گذشته به همراه تاریخ سفارش:

SELECT ord_num, ord_date
FROM sales
WHERE
(ord_date >=
DATEADD(day, -10, GETDATE()))

*(تابع ()GETDATET، تاریخ امروز را بر می گرداند. و تابع DATEADD، همانطور که در مثال بالا مشخص است، سه ورودی دارد. اولی مشخص می کند که کدام یک از بین روز، ماه،و سال قرار است اضافه شود. ورودی دوم مقداری که باید اضافه شود را نشان می دهد. که برای کم کردن مقدار، می توانیم عدد منفی وارد کنیم. و ورودی سوم یک تاریخ است. که در مثال بالا، تاریخ امروز به آن داده شده است).

مثال6: نحوه استفاده از دستور IIF:

("SELECT IIf(Code Between 98101 And 98199, “Local”, “Nonlocal

FROM Publishers

در این Query، دستور IIF مقداری که عملگر BETWEEN برمی گرداند را گرفته و بررسی میکند که اگر TRUE بود، "Local" را برمی گرداند. واگر FALSE بود، "Nonlocal" را.

تشریح برخی دستورات و عملگرها در Query نویسی:

دستور SELECT ... INTO:

گرامر دستور:

[SELECT field1[, field2[, ...]] INTO newtable [IN externaldatabase
FROM source

field1,field2: نام فیلدهایی که قرار است در جدول جدید کپی شود.

newtable: نام جدول جدیدی است که قرار است ایجاد شود. توجه کنید که این نام نباید تکراری باشد. که در این صورت خطا رخ می دهد.

externaldatabase: مسیر آدرس پایگاه داده خارجی است. برای مطالعه‌ی نحوه ی مسیر دهی، به توضیحات مربوط به عملگر شرطی IN مراجعه کنید.

source: نام جدول یا جدول مجازی ای که برخی رکورد های آن انتخاب شده است.

دستورINSERT INTO :
یک یا چند رکورد را به یک جدول اضافه می کند. این دستور به درخواست الحاق اشاره دارد(Append Query).
جزئیات:
شما می توانید از Query های ایجاد جدول برای دسترسی به رکورد ها، ایجاد پشتیبان یا کپی از جدول، و یا کپی برای ارسال به پایگاه داده ای دیگر استفاده کنید. و یا میتوانید آن را مبنایی برای گزارشاتی که داده ها را برای بازه زمانی مشخصی نمایش می دهند قرار دهید. برای مثال، شما می توانید با اجرای Queryهای make-table مشابه در هر ماه، یک گزارش ماهانه‌ی فروش ایجاد کنید.

توضیحات:

· شاید شما بخواهید یک کلید اولیه برای جدول جدید تعریف کنید.وقتی که شما یک جدول جدید ایجاد می کنید، فیلدهای جدول جدید، نوع و اندازه‌ی فیلدهای متناظر در جدول اصلی را به ارث می برند.

· برای افزودن داده ها به جدول جاری، به جای ایجاد Queryی الحاق(Append Query)، از دستور INSERT INTO استفاده کنید.

· برای اینکه قبل از اجرای Query الحاق بدانید کدام رکوردها قرار است انتخاب شوند، ابتدا نتیجه ی یک دستور SELECT با معیار گزینش مشابه را مورد بررسی قرار دهید.
گرامر دستور؛

درخواست الحاق رکوردهای چندگانه (append query):

[INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

درخواست الحاق تک رکورد (Single-record append query):

[([[...,]INSERT INTO target [(field1[, field2
([[...,]VALUES (value1[, value2

target: نام جدول یا جدول مجازی که رکوردها باید به آن اضافه شود.

field1,field2: نام فیلدهایی که مقادیر وروردی باید در انها قرار بگیرند.

externaldatabase: نام پایگاه داده خارجی.

source: نام پایگاه داده ای که قرار است داده های آن کپی شود.

tableExpression: نام جدول یا جدولهایی که داده های آنها قرار است کپی شود. این نام می تواند نام یک جدول باشد یا نام چند جدول که در این صورت منظور، ضرب داخلی یا پیوند طبیعی یا پیوند چپ یا پیوند راست یا ... ی آن جدولها است.

value1, value2: داده هایی که قرار است هر کدام در فیلد مرتبط به خودش قرار بگیرد. مقادیر مختلف فیلدها باید با کاما از هم جدا شوند. همچنین مقادیر رشته ای باید با ' '(qutation) از هم جدا شود.

جزئیات:

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

شما همچنین می توانید چندین رکورد را از یک جدول یا یک Query(جدول مجازی) انتخاب کرده و در جدولی دیگر قرار دهید.برای این کار می توانید به وسیله ی دستور SELECT ... FROM رکوردهایی که می خواهید کپی شود را انتخابی کنید.

جدولهای مبدا یا مقصد می توانند جدول اصلی یا مجازی(Query) باشند. در صورتی که از جدول مجازی استفاده می کنید، موتر جستجوی پایگاه داده مایکروسافت (Microsoft Jet database engine) این کار را در زمان اجرا انجام داده و نتیجه را به شما نشان می دهد.

اگر جدول شما دارای کلید اولیه دارد، حتماً تهی نبودن(NOT NULL) و یکتایی(UNIQUE) آن را بررسی کنید. چرا که موتور اکسس اجازه‌ی ذخیره ی آن رکورد را نخواهد داد.

عملگر INNER JOIN :

در صورتی که دو جدول در یک ستون مشترک دارای مقادیر یکسانی باشند، آن دو جدول را با هم ترکیب می کند و نتیجه در جدولی جدید قرار می گیرد.
گرامر دستور؛

FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field2

ضرب داخلی شامل بخشهای زیر است:

table1, table2: نام جدولهایی که قرار است با هم ترکیب شوند.

field1, field2: نام فیلدهایی است که قرار است با هم پیوند بخورند. این دو فیلد باید هم نوع باشند. اما هم نام بودن آنها الزامی نیست.

CompOPR: هر نوع عملگر مقایسه ای می تواند باشد.(>== 1000

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

همه ی Queryهایی که در Union شرکت می کنند باید تعداد فیلدها(ستونها)ی یکسانی داشته باشند. اما اندازه یا نوع ان لازم نیست حتماً یکسان باشد.

در دستورات Select تودرتو، از نام مستعار(Alias) فقط برای اولین دستور Select استفاده کنید. چون در بقیه موارد نادیده گرفته می شود.

استفاده از کلید واژه های اسنادی ALL ,DISTINCT, DISTINCTROW, TOP برای مشخص کردن برخی ویژگیهای محدود کننده برای خروجی:
گرامر؛

SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]]
FROM table

1-کلید واژه ALL:

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

دو دستور زیر معادل یکدیگرند؛

SELECT ALL *
FROM Employees
;ORDER BY EmployeeID

SELECT *
FROM Employees
;ORDER BY EmployeeID

2- کلید واژه ی DISTINCT:

رکورد های تکراری را حذف می کند. اگر خروجی دارای یک ستون باشد، مثلاً ستون LastName، در این صورت تمام فیلد های خروجی با هم تفاوت دارند.

SELECT DISTINCT LastName
;FROM Employees

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

خروجی Query ای که در آن از DISTINCT استفاده می شود، قابل بروز رسانی نیست. و تغییرات ایجاد شده توسط سایر کاربران را بطور متوالی منعکس نمی کند.

3- کلید واژه DISTINCTROW:

یعنی حذف کردن رکوردهای تکراری. توجه داشته باشید در صورتی یک رکورد تکراری به حساب می آید که تک تک فیلدهای آن با حداقل یک رکورد دیگر مشابه باشد.

به مثال زیر توجه کنید؛

SELECT DISTINCTROW CompanyName
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY CompanyName;

اگر عبارت DISTINCTROW را از درخواست بالا حذف کنید، نام شرکتها به تعداد سفارشهایی که داده اند تکرار می شود. این دستور در صورتی که تنها یک جدول در قسمت FROM آمده باشد، نادیده گرفته می شود.

4- کلید واژه TOP:

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

SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
;ORDER BY GradePointAverage DESC

اگر از شرط ORDER BY استفاده نشود، مطلقاً، 25 رکورد از بالای جدول انتخاب می شود که شرط WHERE در آنها صدق کند.

از کلمه ی کلیدی PERCENT نیز میتوانید برای نمایش درصدی از جدول خروجی استفاده نمایید؛

SELECT TOP 10 PERCENT
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
;ORDER BY GradePointAverage ASC

برای تسلط بیشتر به SQL در Access، مباحث زیر را مطالعه کنید؛

1- دستورات SELECT تو در تو

2- دستورات HAVING و GROUP BY و Nz

3- SQL Aggregate Function

4- عملگرها و مقایسه گرهای فیلدها در Access

تا این جا توضیحاتی کلی در مورد دستورات SQL داده شد تا خوانندگان با مقدمات کد نویسی به زبان SQL آشنا شوند. در بخش های بعد، ابتدا به نحوه ی ایجاد پایگاه داده در ACCESS و سپس به کد نویسی SQL می پردازیم.

موفق باشید

---

<
11 مرداد 1392 00:01

hasan

  • گروه کاربری: میهمان
  • تاریخ عوضیت: --
  • وضعيت:
 
لازم است مقداری آشنایی ابتدایی بدهید

 
برای مشاهده بهتر سایت از مرورگر فایرفاکس ، اُپرا و یا گوگل کروم استفاده نمایید