Transcript Document 7679434

‫‪SQL‬‬
‫‪ SQL‬הינה השפה הדומיננטית לטיפול בבסיסי נתונים יחסיים‪ ,‬אך‬
‫לא קיים סטנדרט ‪ SQL‬יחיד‪(SQL92, SQL3 …)ׁ .‬‬
‫ניתן לחלק את פקודות ‪ SQL‬לארבע משפחות‪:‬‬
‫•פקודות להגדרת בסיס הנתונים (‪)Data Definition‬‬
‫•פקודות לטיפול בנתונים (‪)Data Manipulation‬‬
‫•פקודות בקרת גישה לבסיס הנתונים (‪)Data Access Control‬‬
‫•פקודות לטיפול בתנועות (‪)Transaction Management‬‬
‫שאילתות בסיסיות‬
‫שאילתת ‪ SQL‬פועלת על יחסים‪ ,‬ותוצאתה היא יחס‪.‬‬
‫השאילתה הבסיסית מורכבת משלושה רכיבים‪:‬‬
‫•פסוק ‪ : select‬מקביל לאופרטור ההטלה בא”י‪.‬‬
‫•פסוק ‪ : from‬מקביל למכפלה קרטזית בא”י‪ .‬הפסוק מפרט את‬
‫היחסים המשתתפים בהערכת הביטוי‪.‬‬
‫•פסוק ‪ : where‬מקביל לאופרטור הבחירה בא”י‪.‬‬
‫תכונות‬
‫יחסים‬
‫פרדיקט‬
‫‪select A1,A2,…An‬‬
‫‪from r1,r2,…rm‬‬
‫‪where P‬‬
‫פסוק זה שקול (כמעט) לשאילתת אלגברת היחסים‬
‫))‪A1,A2,…An(P (r1r2 ...  rm‬‬
‫השמטת פסוק ה ‪ where‬שקולה להשמטת פעולת הבחירה‬
‫ניתן להחליף את רשימת התכונות ‪ A1,A2,…An‬ב כוכבית (*) כדי‬
‫להציג את כל התכונות ביחסים המופיעים בפסוק ה ‪. from‬‬
‫מצא את שמות כל הסניפים ביחס ‪deposit‬‬
‫‪select branch-name‬‬
‫‪from deposit‬‬
‫בשאילתת ‪ SQL‬זו אין אלימינציה של ‪n‬יות חוזרות!!‬
‫סילוק ה‪n‬יות החוזרות מתקבל על ידי שימוש ב ‪distinct‬‬
‫‪select distinct branch-name‬‬
‫‪from deposit‬‬
‫פרדיקטים מורכבים‬
‫ במקום‬not ‫ או‬and, or ‫ משמשים המקשרים הלוגיים‬SQL ‫ב‬
.‫ של אלגברת יחסים‬ ‫ ו‬, ‫הסימנים המתמטיים‬
‫מצא את שמות הלקוחות שיש להם חשבון הלוואה בסניף כלשהו‬
‫ואת ערי מגוריהם‬
select distinct customer.customer-name,customer-city
from borrow,customer
where borrow.customer-name= customer.customer-name
‫מצא את שמות הלקוחות שיש להם חשבון הלוואה בסניף‬
‫ ואת ערי מגוריהם‬merkazi
select distinct customer.customer-name,customer-city
from borrow,customer
where branch_name = ‘merkazi’ and
borrow.customer-name= customer.customer-name
‫משתני ‪n‬יה ‪ -‬כינוי‬
‫‪ SQL‬שואלת את הסימון של משתני ‪n‬יה מתחשיב יחסים לפי‬
‫‪n‬יות‪.‬‬
‫מצא את שמות הלקוחות שיש להם חשבון חיסכון בסניף כלשהו‬
‫ואת ערי מגוריהם‪.‬‬
‫‪select distinct T.customer-name,customer-city‬‬
‫‪from deposit S, customer T‬‬
‫‪where S.customer-name = T.customer-name‬‬
‫‪scope‬‬
‫בתת‪-‬שאילתה ניתן להשתמש רק במשתני ‪n‬יה שהוגדרו בתת‪-‬השאילתה‬
‫עצמה או בכל שאילתה שמכילה אותה‪.‬‬
‫אם משתנה ‪n‬יה מוגדר גם לוקלית בתת‪-‬שאילתה וגם גלובלית‪ ,‬תופסת‬
‫ההגדרה הלוקלית‪.‬‬
‫התאמת מחרוזות‬
‫תבניות תווים מתוארות בעזרת שני תווים מיוחדים‪:‬‬
‫• תו האחוזים (‪ - )%‬מתאים לכל תת מחרוזת‪.‬‬
‫• קו תחתי ( _ ) ‪ -‬מתאים לכל תו בודד‪.‬‬
‫תבניות תוים הן ‪.case sensitive‬‬
‫התאמת תבניות ב‪ - SQL‬בעזרת אופרטור ההשוואה ‪like‬‬
‫מצא את שמות הלקוחות הגרים ברחוב ששמו כולל את תת‪-‬‬
‫המחרוזת ‘‪.’ha‬‬
‫‪select customer-name‬‬
‫‪from customer‬‬
‫’‪where street like ‘%ha%‬‬
‫חישובים על עמודות‬
‫ניתן לבצע חישובים נומריים על הערכים המופיעים ביחס‪ .‬ניתן להציג‬
‫תוצאה של חישוב כזה כ”עמודה מדומה”‪.‬‬
‫הדפס לכל חשבון חסכון‪ ,‬שסכום החיסכון שלו הוא מעל ‪0.001‬‬
‫מסך נכסי הסניף‪ ,‬את שם הלקוח‪ ,‬מספר החשבון‪ ,‬הסניף בו‬
‫מתנהל ואת היחס בין סכום החיסכון לסך הנכסים של הסניף‪.‬‬
‫‪select customer-name, account-number, branch_name, amount / assets‬‬
‫‪from deposit, branch‬‬
‫‪where deposit.branch-name = branch.branch-name‬‬
‫‪and amount / assets > 0.001‬‬
‫צירוף של שאילתות בסיסיות‬
‫פעולות ‪ minus, intersect, union‬שקולות ל ‪ - ,, ‬באלגברת‬
‫יחסים‪.‬‬
‫מצא את שמות הלקוחות שיש להם חשבון חיסכון או הלוואה או‬
‫שניהם בסניף ‘‪’aviv‬‬
‫‪(select distinct customer-name‬‬
‫‪from deposit‬‬
‫)’‪where branch-name=‘aviv‬‬
‫‪union‬‬
‫‪(select distinct customer-name‬‬
‫‪from borrow‬‬
‫)’‪where branch-name=‘aviv‬‬
‫מצא את שמות הלקוחות שיש להם גם חשבון חיסכון וגם הלוואה‬
‫בסניף ‘‪’aviv‬‬
‫כנ”ל‪ ,‬אך במקום ‪ union‬צ”ל ‪intersect‬‬
‫שאילתות עם תתי‪-‬שאילתות‬
‫שייכות לקבוצות‬
‫האופרטור ‪ in‬בודק לשייכות לקבוצה‪ .‬הקבוצה עשויה להיות קבועה‪ ,‬או‬
‫תוצאה של תת‪-‬שאילתה‪.‬‬
‫מצא את שמות הלקוחות שיש להם חשבון הלוואה וגם חשבון‬
‫חיסכון בסניף ‘‪.’Aviv‬‬
‫‪select distinct customer-name‬‬
‫‪from borrow‬‬
‫‪where branch-name=‘Aviv’ and‬‬
‫‪customer-name in (select customer-name‬‬
‫‪from deposit‬‬
‫)’‪where branch-name=‘Aviv‬‬
‫האופרטור ‪ not in‬פועל באופן דומה (למשל‪ ,‬מצא שמות הלקוחות שיש‬
‫להם חשבון הלוואה אך לא חשבון חיסכון בסניף ‘‪.)’Aviv‬‬
‫מצא את שמות הלקוחות שיש להם חשבון חיסכון באחד הסניפים‬
’Morag‘ ‫שבהם חוסך‬
select distinct T.customer-name
from deposit T, deposit S
where S.customer-name = ‘Morag’ and
S.branch-name=T. branch-name
‫השוואה לקבוצות‬
> some,  some, < some,  some, =some, some
> all,  all,
< all,  all, =all, all
‫מצא את רשימת הסניפים שערך נכסיהם גדול מערך נכסיו של‬
’Jaffa‘ ‫סניף כלשהו ב‬
select branch-name
from branch
where assets > some
(select assets
from branch
where branch-city=‘Jaffa’)
‫ מבצעים בדיקת האם קבוצה של‬not contains ‫ ו‬contains ‫הפסוקים‬
in, > some, > all ‫ (בניגוד ל‬.‫ערכים מכילה קבוצת ערכים אחרת‬
)‫ המשווים ערך בודד לקבוצת ערכים‬,‫ודומיהם‬
‫מצא את שמות הלקוחות שיש להם חשבונות חיסכון בכל הסניפים‬
. ’Jaffa‘ ‫הנמצאים בעיר‬
select distinct S.customer-name
from deposit S
where (select T.branch-name
from deposit T
where S.customer-name = T.customer-name)
contains
(select branch-name
from branch
where branch-city= ‘Jaffa’)
‫בדיקת יחסים ריקים‬
‫שאילתה כוללת (לא‬-‫ מאפשרת לבדוק האם התוצאה של תת‬SQL
) not exists ‫ (בהתאמה‬exists ‫יות באמצעות האופרטור‬n )‫כוללת‬
‫מצא את שמות הלקוחות שיש להם חשבון חיסכון וגם חשבון‬
.’Aviv‘ ‫הלוואה בסניף‬
select customer-name
from customer C
where exists (select *
from deposit
where deposit.customer-name = C.customer-name
branch-name=‘Aviv’)
and exists (select *
from borrow
where borrow.customer-name = C.customer-name
branch-name=‘Aviv’)
and
and
‫מצא את שמות הלקוחות שיש להם חשבונות חיסכון בכל הסניפים‬
.’Jaffa‘ ‫ב‬
select distinct S.customer-name
from deposit S
where
not exists (
(select branch-name
from branch
where branch-city = ‘Jaffa’)
minus
(select T. branch-name
from deposit T
where S.customer-name=T.customer-name) )
)Join( ‫צירופים‬
)Join( ‫ מאפשרת לבצע צירופים טבעיים ופעולות צירוף‬SQL •
‫אחרות‬
‫מצא את שמות הלקוחות שיש להם חשבון חיסכון בסניף כלשהו‬
.‫ואת ערי מגוריהם‬
select distinct customer-name,customer-city
from deposit inner join customer on
deposit.customer-name = customer.customer-name
‫ במקרה שלעמודות עליהן מתבצע הצירוף שמות‬,‫• לחילופין‬
‫ ניתן להשתמש בצירוף טבעי‬,‫זהים‬
select distinct customer-name,customer-city
from deposit natural inner join customer
)Join( ‫סוגי צירופים‬
•
•
•
•
inner join
left outer join
right outer join
full outer join
‫תנאי צירוף‬
• natural
• on <predicate>
• using (A1, A2, …An)
‫דוגמאות‬
Customername
Morag
Tamir
Avivi
Street
Pinkas
Allenby
Pinkas
Customercity
Rishon
Haifa
Rishon
Customername
Even
Tamir
Avivi
deposit
customer
Customername
Morag
Tamir
Avivi
Street
Pinkas
Allenby
Pinkas
Accountnumber
101
215
102
Customercity
Rishon
Haifa
Rishon
Accountnumber
null
215
102
balance
null
700
400
customer natural left outer join deposit
Customername
Morag
Tamir
Avivi
Even
Street
Pinkas
Allenby
Pinkas
Null
Customercity
Rishon
Haifa
Rishon
Null
Accountnumber
null
215
102
101
customer natural full outer join deposit
balance
null
700
400
500
balance
500
700
400
‫מיון התצוגה‬
.‫יות בתצוגה‬n‫ גורם למיון ה‬order by [asc/desc[
‫רשימה של שמות הלקוחות שיש להם חשבונות הלוואה בסניף‬
.‫’ ממוינת בסדר אלפביתי‬Aviv‘
select distinct customer-name
from borrow
where branch-name=‘Aviv’
order by customer-name
.‫ניתן לבצע מיון על שדות אחדים‬
select *
from borrow
order by amount desc, loan-number asc
‫הקבצה ופונקציות הקבצה‬
‫פונקציות על קבוצות של ‪n‬יות מתבצעות בעזרת פסוק ‪group by‬‬
‫‪ .‬התכונה‪/‬תכונות המופיעות בפסוק ‪ group by‬מגדירות קבוצות‬
‫של ‪n‬יות שערכיהן זהים בתכונות המצוינות‪.‬‬
‫פונקציות הקבצה‪:‬‬
‫‪avg‬‬
‫• ממוצע‪:‬‬
‫‪min‬‬
‫• מינימום‪:‬‬
‫‪max‬‬
‫• מקסימום‪:‬‬
‫‪sum‬‬
‫• סכום‪:‬‬
‫‪count‬‬
‫• ספירה‪:‬‬
‫פונקציות ההקבצה פועלות על קבוצה של ‪n‬יות‪ .‬תוצאת הפעולה‬
‫היא מספר יחיד לכל קבוצה‪.‬‬
‫מצא את יתרת החיסכון הממוצעת בכל סניף‬
select branch-name,avg(balance)
from deposit
group by branch-name
‫מצא את מספר בעלי חשבונות החיסכון בכל סניף‬
select branch-name,count( distinct customer-name)
from deposit
group by branch-name
.‫ מציין תנאי על קבוצות‬having ‫פסוק‬
1200‫מצא שמות הסניפים שממוצע יתרות החיסכון שלהם גדול מ‬
‫ואת ממוצע היתרות‬
select branch-name,avg(balance)
from deposit
group by branch-name
having avg(balance) > 1200
‫מצא את הסניפים שממוצע יתרות החיסכון שלהם מירבי‬
!!!‫אין לבצע הרכבה של פונקציות הקבצה‬
having(max(avg…))
select branch-name
from deposit
group by branch-name
having avg(balance)  all (select avg(balance)
from deposit
group by branch-name)
‫ פונקצית ההקבצה תפעל על‬, group by ‫במקרה של השמטת פסוק‬
‫היחס כולו‬
select avg(balance)
‫ממוצע יתרות החיסכון של כל‬
from deposit
‫החשבונות‬
‫ יחד סדר הביצוע‬having ‫ ופסוק‬where ‫כאשר מופיעים פסוק‬
:‫הוא‬
where
group by
having
‫מצא את ממוצע יתרות חשבונות החיסכון של כל החוסכים הגרים‬
‫ חשבונות חיסכון‬3 ‫’ ושיש להם לפחות‬Haifa’‫ב‬
select avg(balance)
from deposit,customer
where deposit.customer-name=customer.customer-name
and customer-city=‘Haifa’
group by deposit.customer-name
having count(distinct account-number)>2
‫עדכונים בבסיס הנתונים‬
‫‪ .1‬מחיקה‬
‫מבנה פקודת המחיקה‬
‫‪delete r‬‬
‫‪where P‬‬
‫פעולה זו מוחקת מהיחס ‪ r‬את ה‪n‬יות שעבורן הפרדיקט ‪ P‬נותן‬
‫ערך אמת‪.‬‬
‫מחק את כל ה‪n‬יות ביחס ‪borrow‬‬
‫‪delete borrow‬‬
‫מחק את כל חשבונות החיסכון של ‘‪’Tamir‬‬
‫‪delete borrow‬‬
‫’‪where customer-name=‘Tamir‬‬
‫ הוספה‬.2
:‫ניתן להוסיף מידע ליחס בשתי צורות‬
‫יה מפורשת‬n ‫• הוספה של‬
insert into deposit
values (‘hamerkaz’,55,’Cohen’,1500)
‫יות שהיא תוצאה של שאילתה‬n ‫•הוספת קבוצת‬
insert into deposit
select branch-name,loan-number,customer-name,200
from borrow
where branch-name=‘Aviv’
‫‪ .3‬עדכון‬
‫פקודת ‪ update‬מאפשרת לשנות חלק מהתכונות ב‪n‬יה‪.‬‬
‫ניתן לבחור את ה‪n‬יות לעדכון כמו במחיקה ובהוספה‪.‬‬
‫הוסף ‪ 5%‬לכל יתרות חשבונות החיסכון‬
‫‪update deposit‬‬
‫‪set balance=balance*1.05‬‬
‫הוסף ‪ 6%‬ליתרות חשבונות החיסכון מעל ‪ 1000‬ו ‪ 5%‬ליתרות‬
‫החיסכון בסכום מתחת ל ‪1000‬‬
‫‪update deposit‬‬
‫‪set balance=balance*1.06‬‬
‫‪where balance>1000‬‬
‫‪update deposit‬‬
‫‪set balance=balance*1.05‬‬
‫‪where balance1000‬‬
‫תצפיות‬
‫ מבנה הפקודה‬.‫ מאפשרת להגדיר תצפיות‬create view ‫פקודת‬
‫הוא‬
create view v as query
.‫ הוא שם התצפית‬v .‫ הוא ביטוי שאילתה חוקי‬query
‫ בהם יש להם‬,‫תצפית של שמות כל הלקוחות והסניפים‬
.‫חשבונות‬
create view all-customer as
(select branch-name,customer-name
from deposit )
union
(select branch-name,customer-name
from borrow )
.)update anomaly( ‫עדכון בתצפית עלולה לגרום לאנומלית עדכון‬
create view loan-info as
select branch-name,loan-number,customer-name
from borrow
Insert into loan-info
values (‘Aviv’,89,’Levy’)
‫יה‬n ‫מאפשרת לבצע הוספה של‬
‫הגדרת תבניות יחסים‬
‫‪ SQL DDL‬מאפיינת את היחסים בבה”נ‪:‬‬
‫•התבנית של כל יחס‪.‬‬
‫•תחום הערכים המתאים לכל תכונה‪.‬‬
‫•אוסף של אינדקסים לכל יחס‪.‬‬
‫•הרשאות לכל יחס‪.‬‬
‫•אילוצי שלמות‪.‬‬
‫•מבנה האכסון הפיסי של כל יחס על הדיסק‪.‬‬
‫)‪create table r ( A1 D1, A2 D2 ….,An Dn‬‬
‫‪ r‬הוא שם היחס ו ‪ Di‬הוא תחום התכונה ‪. Ai‬‬
‫מחיקת יחס מבה”נ מתבצעת באמצעות ‪drop table‬‬
‫‪drop table r‬‬