DB - Lecture 13,14
Download
Report
Transcript DB - Lecture 13,14
مهدی ایل بیگی
دانشگاه پیام نور دماوند
•
دستورات تعريف دادهها
•
ي
دستورات مجازشمار
•
دستورات پردازش دادهها
•
دستورات پردازش دادهها به طور ادغامشدني
•
دستورات نوشتن ماژول و رويه
•
دستورات كنترل جامعيت
•
دستورات كنترل تراكنشها
2
• تعريف ففف شف ففما :ب ف فرای تعراف ففف شف ففمای ادرا ف ف در SQLاز دسف ففتورات زاف ففر اسف ففت اده م ف ف
نماییم.
CREATE SCHEMA Schema-Name
AUTHORIZATION USER
{base–table definition , view definition , grant} operation
• base–tableج ففدول ه ففای اش ف م در ش ففمای م ففورد ن ففر ه ف ند و viewدی ففدهای
مورد نیاز در شمای مورد ن ر م باشد.
• Grant-Operationبرای تعراف سطح دسترس ی اربران به جداول م باشد.
3
:SQL1 • انواع داده ها در
• CHARACTER[(length)]
• INTEGER
• DECIMAL [(precision[,scale])]
= یک رشته ازCharacter[10] :مثال
10 اراکترها به طول
= یک عددDecimal (5,2) :مثال
پنج رقمی با دقت دقیقا دو رقم اعشار
• SMALLINT
• DOUBLE PRECISION
• REAL
• FLOAT [(precision)]
• NUMERIC [(precision[,scale])]
= یک عدد اعشاری باFloat (5) :مثال
دقت حداقل پنج رقم اعشار
4
:• ایجاد یک جدو ل
CREATE TABLE table-name
(column-definition[,column-definition]…
[,primary-key- definition]
[,foreign-key- definition[,foreign-key- definition]…]);
: در زار دستورات ایجاد جدول شعبه بانک با سه ستون آورده شده:مثال
create table branch
(branch_name
char(15),
branch_city char(30),
assets
integer,
primary key (branch_name))
5
:• دستور حذف یک جدو ل
DROP TABLE table-name
:• دستور ایجاد یک دید
CREATE VIEW Viewname [(Column-name(s))]
AS Subquery...
[WITH [CASCADE | LOCAL] CHECK OPTION];
:• مثال
CREATE VIEW NH_CUST AS
SELECT FirstName, LastName, Phone
FROM CUSTOMER
WHERE STATE = ‘NH’;
6
• دستور مجاز شماری :با اين دستور حق انجام يك يا بيش از يك عمل به كاربر (كاربراني) داده ميشود
یا از آن ها این حق پس گرفته م شود:
GRANT , REVOKE
• مثال :واگذاری حق درج ،حذف و بهنگام سازی بر روی جدول مشتری ها:
GRANT INSERT, DELETE, UPDATE
ON CUSTOMER
; TO Tyson, Keith, David
مثال :لغو کردن حق بهنگام سازی ستون شناسه مشتری در جدول مشتری ها:
7
REVOKE UPDATE
)ON CUSTOMER (CustID
; FROM Tyson, Keith, David
• دستور بازااب :
SELECT [ALL | DISTINCT] item(s)-list
FROM table(s)-name
])[WHERE condition(s
])[GROUP BY column(s
])[HAVING conditions(s
• مثال :1نام تمام شعب بانک را که وام م دهند بدست آوراد.
• ب ففا اس ففت اده از م ففه distinctدر ج ففدول اس ففام
ن یجه ،نام تکراری ظاهر نمی شود.
• بااا اا ااازاده ا ا allدر ج اادوس اا ااامی ن ی ا
نام های تکراری نیز می توانند بیایند.
8
1. select distinct branch_name
from loan
2. select ALL branch_name
from loan
• مثال :1همه سطرها و ستون های جدول وام را بازااب کنید.
* select
from loan
• نکته :کرکتر * وقتی در مقابل Selectبیاید بمعنی تمام ستون ها م باشد.
• مث ففال :2ش ففماره وام ،ن ففام ش ففعبه وام دهن ففد و 100براب ففر مق ففدار وام را بف فرای تم ففام وام ه ففای داده ش ففده
بدست آوراد.
select loan_number, branch_name, amount 100
from loan
• نکته :در عبارت Selectم توان از عبارت های ح اب *, /, +و – است اده نمود.
9
• توابع جمعي (گروهي):
– :COUNTتعداد مقادير اسكالر را به دست ميدهد.
– :SUMحاشل جمع مقادير اسكالر در يك ستون را به دست ميدهد.
– :AVGميانگين مقادير اسكالر در يك ستون را به دست ميدهد.
– :MAXبيشترين مقدار اسكالر در يك ستون را به دست ميدهد.
– :MINكمترين مقدار اسكالر در يك ستون را به دست ميدهد.
10
• مثال :1میانگين موجودی افراد در شعبه ’ ‘Perryridgeرا بدست آوراد.
)select avg (balance
from account
’where branch_name = ‘Perryridge
• مثال :2تعداد ل مشتری ها را در جدول مشتری بدست آوراد.
)*( select count
from customer
• مثال :3تعداد تمام سپرده گذاران را محاسبه نمایید.
)select count (distinct customer_name
from depositor
• نکته :بدلیل اینکه یک فرد م تواند در چنفد بانفک سفپرده داشفته باشفد و بایفد فقف یفک بفار در شفمارش
افراد ح اب شود از مه Distinctکه باعث م شود تمام سطرهای هم مقدار یک بار در جفدول
خروجم ظاهر شوند ،است اده کرده ایم.
11
• مثففال :4بففايترين و پففايين تففرين نمففره در در COM222در تففرم دوم 79-80را بازيففابي
ميكند.
)SELECT MIN(GRADE), MAX(GRADE
FROM STCOT
’WHERE TR=‘2
AND
’YRYR=’78-79
AND
;’COID=‘COM222
12
• مثفال بفرای اسفت اده از :Order Byنفام تمفام اففرادی را کفه از شفعبه ’ ‘Perryridgeوام گرفتفه
اند را بدست آوراد و آن ها را بر اسا ترتیب حروف ال با بصورت نزو لم مرتب نمایید.
select distinct customer_name
from borrower, loan
where borrower loan_number = loan.loan_number and
’branch_name = ‘Perryridge
order by customer_name desc
• نکتففه :اگففر بهففواهیم محتواففات یففک سففتون را بترتیففب نزو لففم مرتففب نمففاییم از دسففتور Order Byبففه
انضفمام مفه descکففه مه فف ) (Descendاسففت اسفت اده مف نمفاییم .اگففر بهفواهیم محتواففات
یک ستون را بترتیب سعودی مرتب نماییم از مه ascکه مه ف ) (Ascendاست است اده مف
نماییم.
13
• امکففان Likeو :Unlikeبففا ايففن دو امكففان ميتففوان دادههففاي مففورد ن ففر را بففا دادن يففك رشففته كففاراكتري
به عنوان نشانوند ج تجو و بيان شراي مورد ن ر ،بازيابي كرد.
• مثال :1مشخصات استاداني را بدهيد كه نام آن ها با ARشروع شده باشد.
* SELECT
FROM PROF
;’WHERE PRNAME LIKE ‘AR%
• نکته :کرکتر ’ ‘%هر رشته ای را matchم کند و کرکتر ’ ‘-هر کرکتر را matchم کند.
• مثال :2نام تمام مشتریان کفه آدر خیابفان آن هفا بفا یفک کرکتفر اختیفاری آغازشفده باشفد و بعفد از ایفن
کرکتر ،مه Mainبیاید و ادامه آدر اختیاری باشد را بدست آوراد:
Select customer_name
from customer
’where customer_street like ‘-Main%
14
• ميتوان با امكان IS NULLوجود هيچ مقدار در يك ستون را ت ت كرد.
• مث ففال :ش ففماره دانش ففاوياني را بدهي ففد ك ففه نم ففره آن ه ففا در در SOC333در ت ففرم دوم 78-79هن ففوز
اعالم نشده است؟
SELECT STID
FROM STCOT
’WHERE COID=‘SOC333
AND
’TR=‘2
AND
’YRYR=’78-79
AND
15
GRADE IS NULL
• بف ففا امكف ففان ،UNIONم يتف ففوان عم لف ففرد عم گف ففر UNIONجبف ففري را برنامهسف ففازي كف ففرد .حاشف ففل ايف ففن
عم لففرد ،جففدولي اسففت كففه سففطرهاي تلفراري نففدارد .اگففر از گزيففدار ALLاسففت اده شففود ،سي ففتم ديگففر
سطرهاي تلراري را در شورت وجود ،حذف نميكند.
• مثففال :نففام تمففام مشففتری هففا را کففه سففپرده گففذاری کففرده انففد یااا وام گرفتففه انففد و یااا جففزه هففر دو دس فته
ه ند بازااب نمایید.
)(select customer_name from depositor
]union [ALL
)(select customer_name from borrower
• با امكان ،Intersectميتوان عم لرد عم گر Intersectجبري را برنامهسازي كرد.
• مثال :نام تمام مشتری ها را که هم سپرده گذاری کرده اند و هم وام گرفته اند مشخص نمایید.
)(select customer_name from depositor
Intersect
)(select customer_name from borrower
16
• با اين امكفان ميتفوان سفطرهاي جفدول را ح فب مقفادير يفك سفتون سفاده (شف ت سفاده)
گروهبندي كرد به نحوي كه در هر گروه ،مقدار آن ستون يل ان باشد.
• مثال :میانگين نمرات برای هر در را محاسبه نمایید.
SELECT COID, AVG(GRADE) AS AVGGR
FROM STCOT
;GROUP BY COID
• نکتففه :بففا اسففت اده از امکففان ASعم کففرد عم گففر Renameرا م ف تففوان پیففاده سففازی
نمف ف ففود .در مثف ف ففال فف ف ففو نف ف ففام سف ف ففتون میف ف ففانگين نم ف ف فرات بف ف ففا اسف ف ففت اده از امکف ف ففان ،ASبف ف ففه
AVGGRتغیير داده شده است.
17
• مثال :2تعداد ل سپرده گذاران را در هر شعبه مشخص نمایید.
)select branch_name, count (distinct customer_name
from depositor, accoun
where depositor.account_number = account.account_number
group by branch_name
• نکته :1ستون (واژگ ) آورده شده در دستور ( selectخارج از توابع جمعم) حتما باید در لی ت واژگف
های مقابل Group Byآورده شود.
• نکته :2در ،SQL1امکان Group Byرا نمی توان در پرسش فرعم ) (Subqueryکفه در واقفع
queryهای تودرتو ه ند بکار برد.
• نکتففه :3وقتففی نففام چنففد جففدول در مقابففل دسففتور Fromبیایففد در واقففع ج ففتجو در جففدولم کففه برابففر بففا
حاش ض ففرب ففارتزان تم ففام ای ففن ج ففداول ه ففا اس ففت ،ش ففورت م ف گي ففرد .ب ففا ای ففن امک ففان م ف ت ففوان عم ک ففرد
عم گ ففر Joinرا پی ففاده س ففازی نم ففود زا فرا ابت ففدا حاش ض ففرب دو ج ففدول را بدس ففت م ف آورا ففم و س ف س ب ففا
شرط ت اوی بين مقادیر ستون های هم نام در دو جدول درواقع دو جدول را به هم پیوند م دهیم.
18
در واقفع
• با اين امكان ميتوان شرط (شرايطي) ناظر به گروهم از سطرها را اعالن كرد .نقش ايفن امكفان
هم ففان نق ففش WHEREدر س ففطر اس ففت ول ففم ب ففا ای ففن امک ففان در واق ففع بف فرای گروه ففم از س ففطرها ش ففرط
گذاشته م شود .توجه داشته باشيد كه HAVINGهميشه با GROUP BYميآيد.
• مثففال :1شففماره در هففااي را بدهيففد كففه در تففرم دوم 78-79كمتففر از 10دانشففاو در آن هففا ناففت نففام كففرده
باشند.
SELECT STCOT.COID
FROM STCOT
’WHERE TR=‘2’ AND YRYR=’78-79
GROUP BY COID
HAVING COUNT(*) < 10
• مث ففال :2ن ففام تم ففام ش ففعنی را ک ففه می ففانگين موج ففودی ح ففاب ه ففای آن ه ففا بیش ففتر از 1200اس ففت بدس ففت
آوراد.
)select branch_name, avg (balance
from account
group by branch_name
having avg (balance) > 1200
19
:شكل ك ي اين امكان چنين است
•
Scalar-expression [NOT] BETWEEN
Scalar-expression AND Scalar-expression
و15 بففين90-89 در تففرم اولHIS444 شففماره دانشففاوياني را بدهيففد كففه نمففره آن هففا در در:1• مثففال
. باشد19
SELECT STCOT.STID
FROM STCOT
WHERE TR=‘1’ AND YRYR=’89-90’ AND
GRADE BETWEEN ’15’ AND ’19’
. م باشند10000 تا9000 شماره وام ها را بدهید که مب غ آن ها بين:2• مثال
select loan_number
from loan
where amount between 90000 and 100000
20
.• پرسش فرعم پرسش ی است که درون پرسش دیگر (پرسش بيرون ) نوشته شود
. نام مشتریان را بدهید که هم وام گرفته اند و هم سپرده گذاری کرده اند:1• مثال
select distinct customer_name
from borrower
where customer_name in
(select customer_name from depositor)
. نام مشتریان را بدهید که وام گرفته اند ولم سپرده گذاری نکرده اند:2• مثال
select distinct customer_name
from borrower
where customer_name not in
(select customer_name from depositor )
21
‘ وام گرفتففه انففد و در ایففن شففعبه سففپردهPerryridge‘ نففام مشففتریان را بدهیففد کففه از شففعبه:3• مثففال
.گذاری نيز کرده اند
select distinct customer_name
from borrower, loan
where borrower.loan_number = loan.loan_number and
branch_name = ‘Perryridge’ and
(branch_name, customer_name ) in
(select branch_name, customer_name
from depositor, account
where depositor.account_number =
account.account_number )
درونف فSelect مف ف ت ففوان قبف ففل از، تف ففک عنص ففری باش ففد، اگ ففر مجموع ففه ج ففواب پرس ففش درونف ف:• نکت ففه
.>) است اده نمود,<, =,…( م تقیما از عم گرهای مقای ه ای
22
• اگر مجموعه جواب پرسش درون ،تک عنصری نباشد ،مف تفوان قبفل از Selectدرونف م فتقیما از
عم گرهای مقای ه ای (… )>,<, =,است اده نمود بشرط که بعد از آن ها مات someیا all
آورده شود .البته از someو allم توان در پرسش های تودرتوی دیگر نيز است اده نمود.
• مث ففال :ن ففام ش ففعنی را بدهی ففد ک ففه دارا ف آن ه ففا از بر ففم ش ففعب ب ففانک ک ففه در ش ف ر Brooklynه ف ند
بیشتر است.
select branch_name
from branch
where assets > some
(select assets
from branch
)’where branch_city = ‘Brooklyn
• نکته :در پر وجفوی ففو اگفر بجفای someاز allاسفت اده شفود نفام شفعنی بازگردانفده مف شفود کفه
دارا آن ها از تمام شعب موجود در ش ر Brooklynه ند ،بیشتر است.
23
• امکان Existsمعادل با سور وجودی در جبر رابطه ای م باشد.
• مثال :نام مشتریان را بدهید که هم وام گرفته اند و هم سپرده گذاری کرده اند.
select distinct customer_name
from borrower
where exists
(select customer_name
from depositor
where
)borrower.customer_name = depositor.customer_name
• نکت ففه :1در پ ففر وج ففوی ف ففو اگ ففر بج ففای existsاز not existsاس ففت اده ش ففود ن ففام مش ففتریان
بازگردانده م شود که وام گرفته اند ولم در هیچ بانک سپرده گذاری نکرده اند.
• نکت ففه :2بای ففد توج ففه ش ففود ک ففه در SQLی ففک پ ففر و ج ففو را ب ففه ش ففورت ه ففای مهت ف م ف ت ففوان نوش ففت.
(مانند مثال فو )
24
• دستورات عم يات ذخيرهسازي:
– ب ف ف ف ف فراي انج ف ف ف ف ففام عم ي ف ف ف ف ففات س ف ف ف ف فهگانه ذخيرهس ف ف ف ف ففازي س ف ف ف ف ففه دس ف ف ف ف ففتور INSERT ،DELETEو
UPDATEوجود دارد.
• دستور :Updateشکل ک م این دستور:
UPDATE table-name
SET assignment-commalist
])[WHERE Condition(s
• دستور :Deleteشکل ک م این دستور:
DELETE
FROM table-name
])[WHERE Cond(s
25
• مثال :تعداد واحد در هاي عم ي را يك واحد كاهش دهيد.
UPDATE COT
SET CREDIT=CREDIT-1
;’WHERE COTYPE = ‘p
• مثال :در هاي دانشاوي با شماره 78110555را در ترم دوم سال 78-79حذف كنيد.
DELETE
FROM STCOT
’WHERE STID = ‘78110555
AND
’TR=‘2
AND
;’YRYR=’78-79
26
: این دستور دو شکل ک م دارد:Insert • دستور
1. INSERT INTO table-name
Values(one row);
2. INSERT INTO table-name
Subquery;
. تعدادي سطر در جدول درج ميشوند،در این فرمت
.< را در جدول درج نمائيد78110888,COM888,2,78-79,12> اطالعات درس ي:1• مثال
INSERT INTO STCOT
VALUES <‘78110888’, ‘COM888’, 2, 78-79, 12>;
200 وام گرفت ففه ان ففد ح ففاب ب ففا موج ففودیPerryridge بف فرای تم ففام ک ففان ک ففه از ش ففعبه:2• مث ففال
.ایجاد نمایید
insert into account
select loan_number, branch_name, 200
from loan
where branch_name = ‘Perryridge’
27
-1نارويهاي است.
-2داراي كمففال سففاختاري اسففت( .یعنففی تمففام اعمففال جبففر رابطففه ای را م توانففد پیففاده سففازی
نماید)
-3زبان استانده سي تمهاي رابطهاي موجود است.
-4تمام انواع دادهاي ساده را دارد.
-5عم گرهاي ب يار قوي دارد.
-6يادگيري آن ساده است.
-7استقالل دادهاي را تامين ميكند.
-8هم به شورت م تقل و هم به شورت ادغامشده قابل است اده است.
28
.1دستور تعريف دادهها
.8دستور حذف جدو ل
.2انواع دادهاي جدید
.9تعريف جدول موقت
.3دستور تعريف ميدان
.10دستور بازيابي
.4دستور تغيير ميدان
.11امكانات جامعيتي
.5دستور حذف ميدان
.12امكانات ايمني
.6دستور ايجاد جدول
SQL .13پويا
.7دستور تغيير جدول
29
• دستور Exceptمعادل با عم گر Minusدر جبر رابطه ای است.
• مثال :شماره دانشاواان را بدهید که در ترم اول 90-89انتهاب واحد نکرده اند.
SELECT STT.STID
FROM STT
EXCEPT
(SELECT STCOT.STID
FROM STCOT
’WHERE TR = ‘1
AND
)’YRYR = ’89-90
30
• در ای ف ففن دو عم گ ف ففر thetaم ف ف توان ف ففد هراف ففک از عم گرهف ففای مقای ف ففه ای (> و < و = و )...
باشد.
• مث ففال :ن ففام ش ففعنی را بدهی ففد ک ففه دارا ف آن ه ففا از تم ففام ش ففعنی ک ففه در ش ف ر Brooklyn
ه ند ،بیشتر باشد.
select branch_name
from branch
where assets > all
(select assets
from branch
)’where branch_city = ‘Brooklyn
31
• فرمت ک م دستور پیوند در SQL2بصورت زار است:
Table-name [NATRUAL][join-type] JOIN table-name
])[ON conditional-expression | USING (column-list
• گونه های اش م که در ق مت join-typeم توان نوشت:
INNER, lEFT[OUTER], RIGHT[OUTER], FULL[OUTER], UNION
• مثال:
loan inner join borrower on
loan.loan_number = borrower.loan_number
• اگر گونه پیوند تصراح نشود ،پیش فرض همان INNERاست.
• نکت ففه :گون ففه ای دیگ ففر از پیون ففد وج ففود دارد ک ففه بن ففام پیون ففد ر ففرب م ف باش ففد ک ففه مع ففادل ب ففا عم گ ففر ر ففرب ففا رتزان
گ ترش یافته است .فرم ک م این دستور به شکل زار است:
• دستور فو معادل است با دستورات زار:
32
R2
CROSS JOIN
R1
*SELECT R1.*, R2.
FROM
;R1, R2
• عم گر تق یم در SQLوجود ندارد ولم م توان آن را شایه سازی کرد.
• مثال :نام تهیه کنندگان را بدهید که تمام قطعات را تولید م کنند.
)
SELECT S.Sname
33
FROM S
WHERE NOT EXISTS
(SELECT * FROM P
WHERE NOT EXISTS
(SELECT * FROM SP
WHERE
• معف ف ففادل اسف ف ففت بف ف ففا :تهیف ف ففه کننف ف ففدگان کف ف ففه
SP.S# = S.S#
قطعففه ای وجففود نداشففته باشففد کففه تهیفه
AND
نکرده باشند.
;))SP.P# = P.P#
• امکان اااع ج ااام ی :ك ففاربر ميتوان ففد زم ففان وارسف ف ي جامعي ففت پايگ ففاه را (براس ففا مح ففدوديت ه ففا و قواعف فد
دادهشده) خود مشخص كند .اربر ميتواند درخواست كند كه اين وارس ي بالفاشف ه باشفد يفا بفا تفاخير.
براي اين من ور از امكان زير است اده ميشود:
)SET CONSTRAINTS (constraint-name
}{DEFERRED | IMMEDIATE
• نکت ففه :گزان ففه Deferredباع ففث م ف ش ففود ت ففا مح ففدودیت ه ففا را بص ففورت پوا ففا ه ففر زم ففان ک ففه نی ففاز اس ففت
اعمففال کنففیم .مففثال در دسففتور Commitتفراکنش م ف تففوان همففه محففدودیت هففای Deferredرا بففه
Immediateتبدیل و درن یجه اعمال کرد.
• امکاناااع ای ا :كففاربر ميتوانففد امتيففاز دسف يابي بففه اشففياه تعريفشففده در رشففما را بففه من ففور انجففام عمففل
مشخص ،به كاربر ديگري بدهد.
• براي اعطاي امتياز از دستور GRANTو براي لغو آن از دستور REVOKEاست اده ميشود.
34
•
•
•
•
•
•
•
•
•
•
35
امتيازهااي كه يك كاربر ميتواند به يك كاربر ديگر بدهد:
:USAGEاجازه است اده از يك ميدان
:SELECTاجازه دس يابي به تمام ستون هاي يك جدول
) :INSERT(Xاجازه درج يك مقدار در ستون Xاز يك جدول
:INSERTاجازه درج تمام ستون هاي يك جدول
) :UPDATE(Xاجازه بهنگامسازي ستون Xاز يك جدول
:UPDATEاجازه بهنگامسازي تمام ستون هاي يك جدول
:DELETEاجازه حذف سطر(ها) از يك جدول
) :REFRENCES(Xاج ف ف ففازه ارج ف ف ففاع ب ف ف ففه س ف ف ففتون Xاز ي ف ف ففك ج ف ف ففدول در تعري ف ف ففف
محدوديت هاي جامعيتي.
:REFRENCESاج ف ففازه ارج ف ففاع ب ف ففه تم ف ففام س ف ففتون ه ف ففا از ي ف ففك ج ف ففدول در تعري ف ففف
محدوديت هاي جامعيتي.
• در SQL2اربر م تواند در حين اجرای برنامه یک پر و جو را آماده نماید و با است اده از
دستورات زار اجرا کند .به این امکان SQLپواا م گواند.
;EXEC SQL PREPARE SQLCODE FROM: SQLSTATEMENT
;EXEC SQL EXECUTE SQLCODE
•
•
•
•
•
36
در دستورات فو :
SQLSTATEMENTمتغيففری اسففت از نففوع کرکتففر بطففول مشففخص ،کففه در زبففان ميزبففان
تعراف شده و حکمی به زبان SQLدر آن قرار م گيرد.
SQLCODEمتغي ففری اس ففت از مح ففی SQLک ففه ش ففورت امدای ففل ش ففده حک ففم SQLرا
بهود م گيرد.
دستور PREPAREحکم مبدأ به SQLرا بصورت کد مقصد قابل اجرا م کند.
دستور ،EXECUTEحکم مورد ن ر را اجرا م کند.
.1تعريف نوع داده مجرد)(Abstract Data Type
.2تعريف زيرنوع (برای ایجاد م وم س
ه مراتب و ورانت برای ADTها)
.3امكان تعريف زيرجدول و زبرجدول
.4امكان ارثبري تعراف ستونهاي جدول جديد از ستونهاي جدول نامدار موجود
.5رويهاي شدن زبان (با ارافه کردن احکام ان اب ،شرط ،ح قه و)...
.6رهانا ()Trigger
.7نوشتن رويه
SQL .8ادغامشده (ادغام احکام SQLدر یک زبان ميزبان)
.9واس سطح فراخوان () :)Call-Level Interface (CLIم توان بفا فراخفوان روال
ها از ،CLIدرخواست های بازااب داده ها را تن یم کرد.
.10دستورات كنترل تراكنشها (مانند)Start, Commit, Rollback :
37
• تعريف :قاعده (محدوديت) يا قواعدي است كه قبل يا بعد از بفروز يفك رويفداد در پايگفاه
دادهه ففا (معم ففوي ي ففك عم ففل تغييردهن ففده داد هه ففا) باي ففد اعم ففال ش ففود .اي ففن قاع ففده در س ففطح
برنامهسففازي ،بففه شففورت يففك رويففه از پففيش تعريفشففده ()Stored Procedure
اسفت كفه بففه طفور شففرطي يفا غيرشففرطي ،قبفل يففا بعفد از انجففام يفك عمففل در پايگفاه دادههففا،
به طور اتوماتيك اجرا ميشود.
• مزايا
– اجراي آن تحت كنترل متمركز سي تم و ن ارت مدير پايگاه دادهها است.
– بففا وجففود آن ديگففر نيففازي ني ففت كففه محففدوديت هففاي مففورد ن ففر جداگانففه در هففر برنامفه
كاربردي اعمال شوند و كنترل هاي يزم انجام شود.
– براي معماري Client/Server DBب يار مناسب است.
38
.1اعمال قواعد جامعيت از جم ه قواعد موسوم به .Business Rule
.2اعمال قواعد ايمني مبتني بر مقادير.
.3درج ركوردهاي نات عم يات در فايل نات تراكنش ها.
.4توليد نسخهاي از دادههاي ذخيرهشده در يك سايت ديگر.
.5در ان شففار اتوماتيففك عم يففات در پايگففاه دادههففاي توزاعشففده وقتففی کففه بففا تکنیففک نسففخه
سازی ،چند نسخه از داده ها در سایت های مهت ف ذخيره شده باشند.
.6در بهنگامسازي ديدها در سي تمهاي رابطهاي.
39
: رهانا را با دستور زار تعراف م نماییمSQL3 در
•
CREATE TRIGGER name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | DELETE | UPDATE OF column-commalist}
On TABLE_NAME
[REFERENCING {NEW | OLD | NEW-TABLE | OLD
TABLE} AS name]
WHEN condition(s)
[FOR EACH {ROW | STATEMENT}]
40
• تعري ففف :ب ففه بي ففان س ففاده عم ففي اس ففت ك ففه تغيي ففري در پايگ ففاه دادهه ففا ايج ففاد كن ففد .اي ففن عم ففل ك ففه طبع ففا ب ففه
شففورت يففك برنامففه يففا بهش ف ي از يففك برنامففه اسففت ،يففا بايففد بففه تمففامي اج فرا شففود و يففا اشففال اج فرا نش فود و
ميگوييم كه تراكنش خاشيت تجزيهناپذيري دارد.
• ی ففک تف فراکنش ب ففا دس ففتور BEGIN TRANSACTIONآغ ففاز مف ف ش ففود و در ش ففورت ک ففه ب ففا
موفقیففت بففه پایففان نرسففد دسففتور ABORTاجفرا خواهففد شففد و دسففتور COMMITنشففان دهنففده
انجام موفقیت آميز تراکنش م باشد.
• خواص تراکنش:
– تجزيهناپذيري ( :)Atomicityتراکنش یا باید تماما اجرا شود و یا نباید اجرا گردد.
– سازگاري ( :)Consistencyپایگاه داده پس از انجام تراکنش باید در ورعیت سازگار قرار گيرد.
– جففدااي (ان فراد :)Isolationتففا زمففان کففه تراکنشف ی اتمففام نیافتففه تغیيفرات حاشففل از آن از دیففد دیگففر
تراکنش ها مه م ماند.
– مانفااي (دوام :)Durabilityتغیيفرات حاشفل از اجفرای یفک تفراکنش فبیح ،نبایفد در هفیچ شفورت
از بين برود (با است اده از تکنیک های ترمیم پایگاه داده)
41
• ديد در مدل رابطهاي نفوعي رابطفه اسفت ،بنفابراين ميتفوان آن را بفه كمفك يفك عبفارت جبفر
رابطهاي يا ح اب رابطهاي به شكل زير تعريف كرد:
View name= Relational expression
• دید در :SQL
]))CREATE VIEW View-name [(Column-name(s
AS Subquery
.
.
.
;][WITH [CASCADE | LOCAL] CHECK OPTION
43
• مثال :دیدی را که شامل دانشاواان رشته رااض ی و فيزیک باشد ایجاد نمایید.
)CREATE VIEW MAPHSTUD(STNUM, STLEV, STAREA
AS SELECT STID, STDEG, STMJR
FROM STT
’WHERE STMJR=‘Math’ OR STMJR=‘Phys
;WITH LOCAL CHECK OPTION
• نکته :1با است اده از گزانه CHECKدر واقع قواعد جامعیتی را م توان بر روی دید اعمال نمود.
• نکتف ف ففه :2اگ ف ف ففر از گزان ف ف ففه LOCALقب ف ف ففل از CHECKاسف ف ففت اده نم ف ف ففاییم ،ق ف ف ففوانين جامعی ف ف ففت (م ف ف ففثال
محدودیت های آورده شده در مقابل )WHEREبر روی همان دید بصفورت مح فم اعمفال مف شفود.
در شففورت کففه از گزانففه CASCADEاسففت اده گففردد ،قواعففد جففامعیتی بففر روی دیففد جففاری و تمففام
دیدها که روی این دید تعراف شده باشد اعمال م شود.
• نکته :3عبفارت SELECTدر دسفتورات ایجفاد ،Viewاجرا ف نی فت ب کفه اعالنف مف باشفد .یعنفی
بففا ایففن دسففتور هففیچ داده ای بازاففاب نمففی شففود و فقف امکففان اسففت بفرای مشففخص کففردن محففدوده داده
ای ( )Data Scopeاربر.
44
• در SQLیک دید با دستورات زار حذف م گردد:
}DROP VIEW View-name {restrict | cascade
• نکته :اگر از گزانه restrictاست اده نماییم فق دیدی کفه نفام آن آورده شفده حفذف
مف ف شف ففود .در شف ففورت کف ففه از گزانف ففه cascadeاسف ففت اده گف ففردد ،دیف ففد جف ففاری و تمف ففام
دیدها که روی این دید تعراف شده باشد حذف م گردند.
• مثال:
;DROP VIEW MAPHSTUD CASCADE
45
• تامينكننده محي انتزاعي براي كاربران سطح خارجي
• تامينكننده پويااي باي در تعريف پايگاه توس كاربر
• ت يلكننده واس كاربر برنامهساز با پايگاه
• امكففاني اسففت بفراي كوتاهنوا ف ي يففا ماكرونوا ف ي پرسففش هففا (مف تففوان دیففد پیییففده ای را یففک بفار تعراففف
کرد و در پر و جو های مهت از آن است اده نمود)
• تامينكننده اشتراك دادهاي
• تامينكننده نوعي مكاني م خودكار ايمني دادهها (بدلیل عدم دسترس ی به همه داده ها)
• تامينكننده استقالل دادهاي منطقي( .مثال با تبدیل یک جدول بفه دو جفدول و حفذف جفدول اولیفه ،مف
ت ففوان از دو ج ففدول درس ففت ش ففده ی ففک دی ففد مانن ففد ج ففدول اولی ففه ایج ففاد ک ففرد ت ففا ب فرای دی ففدها ک ففه ب ففر روی
جدول اولیه ایجاد شده اند مشک م ایجاد نشود)
• امكان تعريف Data-Objectبا اندازههاي مهت ف (مثال تمام رابطه مبنا یا ق متی از آن)
46
• ايج ففاد فزونك ففاري در سي ففتم ب فراي انج ففام تب ففديل خ ففارجي/ادراك ففي و احيان ففا خ ففارجي/خ ففا رجي.
مففثال دس ففتور بازاففاب اطالع ففات ک ففه بففر اس ففا دیففد ش ففادر م ف شففود بای ففد ابتففدا ب ففه دس فتور
بازا ففاب ب ففر روی ی ففک ج ففدول مبن ففا تب ففدیل ش ففود و س ف س ای ففن پ ففر و ج ففو ب ففا تب ففدیل خ ففا رجم
ادرا ف بففه یففک پففر و جففو در سففطح ادرا ف تبففدیل گففردد و ایففن باعففث فزونکففاری در تبففدیل
خففارجم ادرا ف خواهففد شففد .زمففان کففه بففر روی یففک دیففد دیففدهای دیگففری تعراففف م ف شففود
فزونکاری برای تبدیل خارجم/خارجم هم خواهیم داشت.
• ع ففدم امك ففان انج ففام عم ي ففات ذخيرهس ففازي در ب ففياري از گون فههاي دي ففد و در ن يج ففه ايج ففاد
محدوديت براي كاربر.
47
• چ ففون دي ففد خ ففود ن ففوعي رابط ففه اس ففت ،پ ففس ب فراي بازي ففابي از دي ففد ه ففم ي ففك عب ففارت جب ففري ي ففا
ح ف ف ففابي مينوا ف ف ففيم .در SQLاز همف ف ففان دسف ف ففتور SELECTب ف ف فرای بازاف ف ففاب از دیف ف ففد
است اده م کنیم.
• مثال بازيابي از يك ديد با است اده از دستورات :SQL
CREATE VIEW V1
AS SELECT STID, STDEG
FROM STT
’WHERE STPROG=‘Math
SELECT STID
FROM V1
;’WHERE STDEG = ‘bs
48
• ب فراي انج ففام عم ي ففات ذخيرهس ففازي در دي ففد از هم ففان دس ففتورات س فهگانه UPDATE ،INSERTو
DELETEاسف ففت اده ميشف ففود .در تمف ففام دیف ففدها ایف ففن عم یف ففات را نمیتف ففوان نجف ففام داد .ديف ففدها از ن ف ففر
پذيرش عم يات ذخيرهسازي دو دستهاند:
• دیدهای پذیرا:
.1ديدهاي گزينش ي
.2ديد گزينش – پرتوي داراي ك يد رابطه مبنا
.3ديد پيوندي CK-CK
.4ديد پيوندي CK-FK
.5ديد حاشل اجتماع ،اشتراك و ت ارل دو رابطه
• دیدهای ناپذیرا:
.1ديد پرتوي /گزينش ي-پرتوی فاقد ك يد رابطه مبنا
.2ديد پيوندي (NotKey-NotKey) NK-NK
.3ديد پيوندي FK-FK
.4ديد حاوي ش ت مجازي
.5ديد حاشل تق يم
49
.1دید گزانش ی :ديد گزينش ي حاشل عم گر گزينش در يك رابطه است.
مثال :دیدی که مشخصات دانشاواان ارشناس ی ارشد را دربرگيرد تعراف کنید.
Create View Select-View
* AS Select
;’From STT WHERE STDEG = ‘ms
• عم یات حذف و بهنگام سازی در این دید بدونه مشکل انجام م شود.
• عمل درج در این دید قابل انجام است ولم دو مشکل ممکن است بوجود آید:
.1ممکففن اسففت ففاربر سففطری را درج کنففد کففه قففبال در جففدول مبنففا وجففود داشففته باشففد .اگففر یففد ایففن
سففطر تک فراری نباشففد ناسففازگاری در داده هففا بوجففود م ف آیففد و اگففر یففد ایففن سففطر موجففود باش فد
عم یات درج رد خواهد شد.
.2اگففر ففاربر بهواهففد سففطری بففا مقففدار ’ ‘bsب فرای فی ففد STDEGدرج کنففد ،سی ففتم بایففد ای ففن
WITH
درج را رد کنف ففد زا ف فرا بف ففا ش ف فرای داده شف ففده در تعراف ففف دیف ففد تعف ففارض دارد .گزانف ففه
CHECK OPTIONبرای درخواست این وارس ی از سی تم م باشد.
50
.2ديففد گ ففزينش -پرت ففوي داراي ك يففد رابط ففه مبن ففا :در دي ففد ”گففزينش-پرت ففوي داراي ك ي ففد رابطففه مبن ففا“ ع ففالوه ب ففر
گففزينش تاپففل هففااي از رابطففه مبنففا ،عم گففر پرتففو نيففز اعمففال شففده اسففت .ايففن نففوع ديففد را اشففطالحا ديففد
داراي ك يد با تاپل هاي ناقص ميگوئيم.
مثال:
create view seprov as
select STID, STDEG, STDEID
from STT
’WHERE STDEID = ‘D111’ OR STDEID = ‘D333
نکتففه :در ایففن نففوع دیففد نيففز عم یففات ذخيففره سففازی را م ف تففوان انجففام داد ولففم بففا درج یففک تاپففل در ایففن دی فد بففه
دلیففل اینکففه ایففن تاپففل نففاقص اسففت و تعففدادی از شف ت هففا را نففدارد ،بایففد در جففدول مبنففا بجففای شف ت
های موجود مقدار NULLنوشت (البته درشورت که ش ت مورد ن ر هیچ مقدار پذیر باشد).
51
.3دیففد پیونففدی :CK-CKحاشففل پيونففد دو رابطففه روي ك يففد كانديففد مشففترك آن هففا اسففت
و در عم يات ذخيرهسازي مشل ي ندارد.
.4ديففد پيونففدي :CK-FKایففن دیففد حاشففل پيونففد روي ك يففد كانديففد يففك رابطففه و ك يففد
خارجي رابطه ديگر اسفت .ايفن ديفد در حفذف مشفكل دارد ،زيفرا بفا حفذف يفك تاپفل از ايفن
دي ففد ،در هري ففك از دو رابط ففه مبن ففا ،ي ففك تاپ ففل ح ففذف ميش ففود و چنانی ففه ك ففاربر بهواه ففد
محتففواي ديففد خففود را نمففا ش دهففد ،تاپففل هففاي ديگففر هففم از ديففد او حففذف ميشففوند كففه
درخواست نلرده است.
.5ديد حاشل اجتماع ،اشتراك و ت ارل دو رابطه در عم يات ذخيرهسفازي مشفل ي نفدارد،
بفه شفرط آنلففه سي فتم بتوانفد تشففخيص دهفد كفه عمففل درخواستشفده ،در كففداميك از
دو رابطه مبنا انجام شود.
52
.1
.2
.3
.4
.5
.6
53
بففروز عاررففه جففانني در خففود ديففد (مففثال بففا درج یففک سففطر در دیففدی کففه حاشففل پیونففد دو جففدول اسففت،
دو سففطر جدیففد در جففداول مبنففا ایجففاد مف شففود کففه ممکففن اسففت در بازاففاب مجففدد دیففد ،باعففث ایجففاد
تاپل های حشو شود)
بففروز عاررففه جففانني در ديففدهاي ديگففر (مففثال اگففر از یففک دیففد سففطری حففذف شففود و ایففن حففذف باعففث
تغیيففر جففدول مبنففا بشففود کففه بفرای دیففد دیگففری مففورد اسففت اده باشففد عاررففه جففاننی در دیففدهای دیگففر
بوجود آمده)
نقففق قاعففده جامعيففت (ایجففاد هففیچ مقففدار بفرای شف تی کففه هففیچ مقففدار پففذیر نی ففت یففا نقففق یکتففا
ید اش م)
بروز فزونكاري در سي تم (مثال :تبدیل یک عمل واحد مثل حفذف ،بفه چنفدین عمفل بفر روی جفداول
مبنا در دید پيوندي )CK-FK
تغييففر ماهيففت عمففل درخواسففت شففده (مففثال درخواسففت حففذف یففک تاپففل در یففک دیففد بفرای اینکففه باعفث
بروز حذف اطالعات م ید نشود ،به درخواست بهنگام سازی تبدیل م شود)
تعففدد تبففديالت و مشففكل تصففميمگيري (مففثال وقتففی کففه بفرای تبففدیل درخواسففت ففاربر چنففدین راه مطففر
باشد ،تصمیم گيری برای سی تم مشکل م شود)
.1ديففد پرتففوي يففا گزينشف ي-پرتففوی فاقففد ك يففد رابطففه مبنففا :بففدلیل عففدم وجففود یففد اش ف م نمففی تفوان عم یففات
ذخيره سازی را انجام داد.
.2دید پیوندی :FK-FKاین دید دارای عوارض جاننی برای عم یات ذخيره سازی است.
.3دیففد پیونففدی :NK-NKایففن دیففد ،دیففدی اسففت کففه حاشففل پیونففد روی ش ف ت مشففتر غيففر یففد از دو
رابطه است .این دید دارای عوارض جاننی م باشد.
.4دید حاوی ش ت مجازی :این دید یک یا چند ش ت مجفازی (محاسفبه شفده) دارد (ماننفد میفانگين) ،بفه
همين دلیل عم یات ذخيره سازی در این دید غيرممکن است.
.5دید حاشل از تق یم :این دید بدلیل اینکه از تق یم دو رابطه بر هفم ایجفاد مف شفود در ن یجفه حفذف
یففک سففطر از ایففن دیففد باعففث حففذف تعففدادی سففطر از رابطففه مق ففوم مف شففود در بهنگففام سففازی نيففز ایفن
مشکل وجود دارد .عم یات درج بدلیل اینکه رابطه مق وم ع یه را ندارام عمال ناممکن است.
54
.1عبارت تعريفكننده محدوده ديد ،يك عبارت معتبر SELECTباشد.
.2در عبارت SELECTگزانه DISTINCTوجود نداشته باشد.
.3در كالز ،FROMعبارت ،SELECTفق يك جدول وجود داشته باشد.
.4جدول قيدشده در كالز ،FROMيك جدول مبنا يا يك ديد قابل بهنگامسازي باشد.
.5در item-listعبارت ،SELECTستون هاي مورد ن ر بايد در جدول مبنا متناظر باشد.
.6در عبارت SELECTنبايد كالز GROUP BYو HAVINGوجود داشته باشد.
.7در كففالز WHEREعبففارت ،SELECTنبايففد عبففارتي حففاوي كففالز FROMباشففد ،بففه گون فهاي
ك ففه در آن ب ففه هم ففان ج ففدولي ارج ففاع داده ش ففده باش ففد ك ففه در ك ففالز FROMقب ففي ب ففه آن ارج ففاع ش ففده
است.
55