Transcript AUT2008
Slide 1
פקודות לטיפול בנתונים
)(Data Manipulation
1
Slide 2
הפקודה : SELECT
• 2המשפטים הראשונים הינם חובה וארבעת הנוספים-רשות
2
Slide 3
משפט הSELECT -
•
•
•
•
•
•
מגדיר את שמות העמודות שיש לשלוף ולהציג
שמות של עמודות מטבלה אחת או יותר
ניתן להגדיר חישובים על עמודות
ניתן להגדיר פונקציות הפועלות על עמודה
(חישוב ממוצע של גיל)
ניתן להגדיר שם נוסף לעמודה
הסדר שבו שמות העמודות רשומות
הוא הסדר שבו הן תוצגנה
3
Slide 4
משפט WHERE
•
•
•
•
•
•
הגדרת תנאי לוגי לשליפת שורות
מכיל אופרטורים השוואתיים=, >, <, <>, =<, => :
מכיל אופרטורים לוגייםNOT, OR, AND :
בדיקת קיום ערך בתוך קבוצה סגורה של ערכים
ע”י שימוש ב IN -
בדיקת קיום ערך בתוך טווח ערכים ע”י שימוש
בBETWEEN -
בדיקת קיום מחרוזת תווים בתוך ערכים של עמודה
ע”י שימוש בLIKE -
4
Slide 5
משפטים נוספים בפקודת ה -
SELECT
• משפט GROUPED BY -מאפשר הקבצת שורות
שבהם מופיעים בעמודה/ות מסוימת/ות ערכים זהים
• משפט HAVING -מגדיר תנאי לוגי
אותו יקיימו השורות המקובצות בלבד
• משפט ORDER BY -מגדיר סדר המיון
שבו תוצגנה השורות הנשלפות
5
Slide 6
הפקודה Selectשל שפת SQLמול
פקודת Selectשל האלגברה הטבלאית
• הפקודה Selectשל שפת SQL
מכילה בתוכה את 3האופרטורים של
האלגברה הטבלאית:
•?
6
Slide 7
הפקודה Selectשל שפת SQLמול
פקודת Selectשל האלגברה הטבלאית
• Join, Project, Select
7
Slide 8
בסיס הנתונים
8
Slide 9
בסיס הנתונים
סטודנטים
9
STUDENTS
STUDENT_ID
NAME
CITY
סטודנט.מס
שם סטודנט
עיר
105
MOSHE
HAIFA
210
DAN
HAIFA
107
EYAL
TEL-AVIV
110
RAN
HAIFA
245
YOEL
HAIFA
240
AYELET
TEL-AVIV
200
DAVID
TEL-AVIV
310
TOVA
JERUSALEM
Slide 10
בסיס הנתונים
ציונים
Grades
STUDENT_ID
COURSE_ID
SEMESTER
TERM
GRADE
סטודנט.מס
קורס.מס
סמסטר
מועד
ציון
105
C-55
SUM2007
A
70
210
M-100
AUT2008
A
90
105
M-100
SUM2007
B
50
105
C-200
AUT2008
A
85
210
C-200
AUT2008
A
80
210
B-10
WIN2008
A
50
105
B-40
WIN2008
B
70
245
M-100
AUT2008
A
80
245
B-10
AUT2008
A
70
200
C-200
AUT2008
B
50
200
B-10
AUT2008
A
65
245
B-40
WIN2007
A
95
200
M-100
SUM2007
B
90
310
M-100
SUM2007
A
100
10
Slide 11
שליפת כל העמודות וכל השורות -טבלה אחת
11
Slide 12
שליפת שורות ועמודות מסוימות
12
Slide 13
שינוי שם עמודה
13
Slide 14
שליפת שורות ללא הצגת שורות
כפולות
14
Slide 15
בחירת שורות -בדיקת השוואה
15
Slide 16
בחירת שורות -בדיקת השוואה
16
Slide 17
בחירת שורות -בדיקת השוואה עם תנאי
בוליאני
17
Slide 18
בחירת שורות -בדיקת טווח ערכים רציף
18
Slide 19
בחירת שורות -בדיקת קיום ערך בתוך קבוצת
ערכים
19
Slide 20
בחירת שורות -בדיקת מחרוזת
Wild
20
Slide 21
בחירת שורות -בדיקת מחרוזת
21
Slide 22
(Calculated עמודות מחושבות
columns)
2008
‘AUT2008’
22
Slide 23
הוספת כיתוב קבוע
2008
’‘AUT2008
23
Slide 24
פונקציות מובנות )(Build-In Function
• פועלות על עמודה כל שהיא ומחזירות ערך יחיד
• אם רוצים שהפונקציה תתעלם מערכים כפולים
בעמודה נשתמש ב - Distinctלפני שם העמודה
• רשימת הפונקציות הינה:
24
Slide 25
תחביר הפקודה
25
Slide 26
דוגמאות
26
Slide 27
דוגמאות
2007
’‘SUM2007
27
Slide 28
מיון התוצאה )(Order By
28
Slide 29
שאילתות מקובצותGrouped Queries -
•
•
•
•
מאפשר הקבצה של שורות בעלות ערך זהה
הצגת שורה אחת עבור אוסף של שורות המכילות
אותו ערך בעמודות מסוימות
משפט ה - Havingמגדיר תנאי על השורות
המקובצות
כל שם עמודה המופיע במשפט Group Byחייב
להופיע קודם במשפט ה - Selectובאותו הסדר
29
Slide 30
- Grouped Queries שאילתות מקובצות
30
Slide 31
- Grouped שאילתות מקובצות
Queries
desc
31
Slide 32
שאילתות עם מספר טבלאות
•
•
•
•
מתבסס על הקשרים הלוגיים בין הטבלאות
הצירוף ) (Joinמתבצע ע”י הגדרת תנאי כל שהוא
בין העמודות של הטבלאות
התנאי יכול להיות :שוויון ) (Equi Joinאו
תנאי לוגי אחר )(Theta Join
מערכות מסחריות קובעות מגבלות לגבי מספר
הטבלאות המשתתפות 8 -
32
Slide 33
דוגמא
• הצג את שמות הסטודנטים ואת עיר מגוריהם
עבור כל הסטודנטים שלמדו בקורס c-200
33
Slide 34
שאילתות עם מספר טבלאות
INNER
34
Slide 35
שאילתות עם מספר טבלאות
35
Slide 36
שאילתות עם מספר טבלאות
36
Slide 37
סטודנטים בקורסים עם ציון ממוצע גבוה מציון הממוצע של הקורס
SELECT
course_name,student_id,student_name,c_avg.course_avg,avg(grade) as
student_avg
FROM grades g, students s, courses c, (SELECT
avg(grade) as course_avg FROM grades course_id,
GROUP BY course_id) c_avg
WHERE s.student_id = g.student_id
and c.course_id = g.course_id
and c_avg.course_id = g.course_id
GROUP BY course_name,student_id,student_name,c_avg.course_avg
HAVING avg(grade) > c_avg.course_avg
ORDER BY s.student_id,student_name
37
•
•
•
•
•
•
•
•
•
Slide 38
צירוף טבלאות ע”י Outer Join
• הצירוף הרגיל הנקרא גם Inner Joinמעביר לטבלת
התוצאה רק זוגות של שורות מ 2 -טבלאות עבורן
מתקיים תנאי הצירוף
• הצירוף החיצוני Outer Joinמעביר אל טבלת
התוצאה את כל השורות של אחת או שתי הטבלאות
המשתתפות בצירוף:
38
Slide 39
צירוף טבלאות ע”י Outer Join
• - Left Outer Joinלטבלת התוצאה עוברות כל
השורות מהטבלה השמאלית
• - Right Outer Joinלטבלה התוצאה עוברות כל
השורות מהטבלה הימנית
• - Full Outer Joinלטבלת התוצאה עוברות כל
השורות מ 2 -הטבלאות
39
Slide 40
צירוף טבלאות ע”י Outer Join -
תחביר הפקודה
40
Slide 41
צירוף טבלאות ע”י Outer Join -
דוגמא:
• הצג את רשימת ראשי המחלקה ,שם המחלקה
שבראשם הם עומדים ושמות הקורסים המוצעים ע”י
אותה מחלקה
41
Slide 42
צירוף טבלה אל עצמה
)(Reflexive Join, Self Join
• מאפשר התייחסות מספר פעמים אל אותה טבלה
42
Slide 43
תת שאילתא
•
•
•
•
•
•
יכולת הקינון מאפשרת לשפת SQLלהיקרא שפה מובנית
סדר ביצוע השאילתות הוא מתת השאילתא הפנימית ביותר ועד
לשאילתא הראשית
תת השאילתא מוקפת בסוגריים ומופיעה בתוך משפט WHERE
של השאילתא הראשית
תת השאילתא יוצרת טבלת תוצאה המכילה עמודה אחת בלבד או ערך
בודד
תת השאילתא אינה יכולה להכיל מיון
משפט WHEREשל תת-שאילתא יכול להכיל בעצמו תת שאילתא
נוספת -מבנה מקונן
43
Slide 44
תת שאילתא -דוגמא
44
Slide 45
תת שאילתא
• כאשר תת שאילתא מחזירה קבוצת ערכים ניתן לבדוק
אם הערכים של עמודה מהשאילתא הראשית מופיעים
בתוך קבוצת הערכים המוחזרים ע”י תת השאילתא
באמצעות האופרטור IN
45
Slide 46
תת שאילתא -דוגמאות
ומספר הקורס
46
Slide 47
תת שאילתא -דוגמאות
NAME, CITY
47
Slide 48
75 נקודות והציונים שלהם מעל4 סטודנטים שלא לקחו קורסים עם
SELECT student_id, student_name, course_name, points, grade •
students s, grades g, courses c
•
WHERE g.student_id=s.student_id
•
and c.course_id =g.course_id
•
and not exist (SELECT * from courses •
WHERE points=4)
and grade > 75
•
FROM
48
Slide 49
איחוד תוצאות של שאילתות
)(Union
49
Slide 50
חיתוך תוצאות של שאילתות )(Intersect
50
Slide 51
פקודות לעדכון בסיס הנתונים -הוספה של
שורה בודדת )(Single Row Insert
• מאפשרת הוספת שורה חדשה לטבלה קיימת
51
Slide 52
הוספה של שורה בודדת
• מאחר ואנו קובעים ערכים לכל העמודות ,ניתן גם:
52
Slide 53
(Multi-Row Insert) הוספת מספר שורות
חדשה
Using course_id
53
Slide 54
עדכון שורות )(Update
54
Slide 55
דוגמאות
.2007
’‘SUM2007
55
Slide 56
בטול שורות)(Delete
56
Slide 57
בטול שורות -דוגמאות
57
Slide 58
,ת.ז)
58
Slide 59
תרגילים
59
Slide 60
תרגילים
60
Slide 61
תרגילים
61
פקודות לטיפול בנתונים
)(Data Manipulation
1
Slide 2
הפקודה : SELECT
• 2המשפטים הראשונים הינם חובה וארבעת הנוספים-רשות
2
Slide 3
משפט הSELECT -
•
•
•
•
•
•
מגדיר את שמות העמודות שיש לשלוף ולהציג
שמות של עמודות מטבלה אחת או יותר
ניתן להגדיר חישובים על עמודות
ניתן להגדיר פונקציות הפועלות על עמודה
(חישוב ממוצע של גיל)
ניתן להגדיר שם נוסף לעמודה
הסדר שבו שמות העמודות רשומות
הוא הסדר שבו הן תוצגנה
3
Slide 4
משפט WHERE
•
•
•
•
•
•
הגדרת תנאי לוגי לשליפת שורות
מכיל אופרטורים השוואתיים=, >, <, <>, =<, => :
מכיל אופרטורים לוגייםNOT, OR, AND :
בדיקת קיום ערך בתוך קבוצה סגורה של ערכים
ע”י שימוש ב IN -
בדיקת קיום ערך בתוך טווח ערכים ע”י שימוש
בBETWEEN -
בדיקת קיום מחרוזת תווים בתוך ערכים של עמודה
ע”י שימוש בLIKE -
4
Slide 5
משפטים נוספים בפקודת ה -
SELECT
• משפט GROUPED BY -מאפשר הקבצת שורות
שבהם מופיעים בעמודה/ות מסוימת/ות ערכים זהים
• משפט HAVING -מגדיר תנאי לוגי
אותו יקיימו השורות המקובצות בלבד
• משפט ORDER BY -מגדיר סדר המיון
שבו תוצגנה השורות הנשלפות
5
Slide 6
הפקודה Selectשל שפת SQLמול
פקודת Selectשל האלגברה הטבלאית
• הפקודה Selectשל שפת SQL
מכילה בתוכה את 3האופרטורים של
האלגברה הטבלאית:
•?
6
Slide 7
הפקודה Selectשל שפת SQLמול
פקודת Selectשל האלגברה הטבלאית
• Join, Project, Select
7
Slide 8
בסיס הנתונים
8
Slide 9
בסיס הנתונים
סטודנטים
9
STUDENTS
STUDENT_ID
NAME
CITY
סטודנט.מס
שם סטודנט
עיר
105
MOSHE
HAIFA
210
DAN
HAIFA
107
EYAL
TEL-AVIV
110
RAN
HAIFA
245
YOEL
HAIFA
240
AYELET
TEL-AVIV
200
DAVID
TEL-AVIV
310
TOVA
JERUSALEM
Slide 10
בסיס הנתונים
ציונים
Grades
STUDENT_ID
COURSE_ID
SEMESTER
TERM
GRADE
סטודנט.מס
קורס.מס
סמסטר
מועד
ציון
105
C-55
SUM2007
A
70
210
M-100
AUT2008
A
90
105
M-100
SUM2007
B
50
105
C-200
AUT2008
A
85
210
C-200
AUT2008
A
80
210
B-10
WIN2008
A
50
105
B-40
WIN2008
B
70
245
M-100
AUT2008
A
80
245
B-10
AUT2008
A
70
200
C-200
AUT2008
B
50
200
B-10
AUT2008
A
65
245
B-40
WIN2007
A
95
200
M-100
SUM2007
B
90
310
M-100
SUM2007
A
100
10
Slide 11
שליפת כל העמודות וכל השורות -טבלה אחת
11
Slide 12
שליפת שורות ועמודות מסוימות
12
Slide 13
שינוי שם עמודה
13
Slide 14
שליפת שורות ללא הצגת שורות
כפולות
14
Slide 15
בחירת שורות -בדיקת השוואה
15
Slide 16
בחירת שורות -בדיקת השוואה
16
Slide 17
בחירת שורות -בדיקת השוואה עם תנאי
בוליאני
17
Slide 18
בחירת שורות -בדיקת טווח ערכים רציף
18
Slide 19
בחירת שורות -בדיקת קיום ערך בתוך קבוצת
ערכים
19
Slide 20
בחירת שורות -בדיקת מחרוזת
Wild
20
Slide 21
בחירת שורות -בדיקת מחרוזת
21
Slide 22
(Calculated עמודות מחושבות
columns)
2008
‘AUT2008’
22
Slide 23
הוספת כיתוב קבוע
2008
’‘AUT2008
23
Slide 24
פונקציות מובנות )(Build-In Function
• פועלות על עמודה כל שהיא ומחזירות ערך יחיד
• אם רוצים שהפונקציה תתעלם מערכים כפולים
בעמודה נשתמש ב - Distinctלפני שם העמודה
• רשימת הפונקציות הינה:
24
Slide 25
תחביר הפקודה
25
Slide 26
דוגמאות
26
Slide 27
דוגמאות
2007
’‘SUM2007
27
Slide 28
מיון התוצאה )(Order By
28
Slide 29
שאילתות מקובצותGrouped Queries -
•
•
•
•
מאפשר הקבצה של שורות בעלות ערך זהה
הצגת שורה אחת עבור אוסף של שורות המכילות
אותו ערך בעמודות מסוימות
משפט ה - Havingמגדיר תנאי על השורות
המקובצות
כל שם עמודה המופיע במשפט Group Byחייב
להופיע קודם במשפט ה - Selectובאותו הסדר
29
Slide 30
- Grouped Queries שאילתות מקובצות
30
Slide 31
- Grouped שאילתות מקובצות
Queries
desc
31
Slide 32
שאילתות עם מספר טבלאות
•
•
•
•
מתבסס על הקשרים הלוגיים בין הטבלאות
הצירוף ) (Joinמתבצע ע”י הגדרת תנאי כל שהוא
בין העמודות של הטבלאות
התנאי יכול להיות :שוויון ) (Equi Joinאו
תנאי לוגי אחר )(Theta Join
מערכות מסחריות קובעות מגבלות לגבי מספר
הטבלאות המשתתפות 8 -
32
Slide 33
דוגמא
• הצג את שמות הסטודנטים ואת עיר מגוריהם
עבור כל הסטודנטים שלמדו בקורס c-200
33
Slide 34
שאילתות עם מספר טבלאות
INNER
34
Slide 35
שאילתות עם מספר טבלאות
35
Slide 36
שאילתות עם מספר טבלאות
36
Slide 37
סטודנטים בקורסים עם ציון ממוצע גבוה מציון הממוצע של הקורס
SELECT
course_name,student_id,student_name,c_avg.course_avg,avg(grade) as
student_avg
FROM grades g, students s, courses c, (SELECT
avg(grade) as course_avg FROM grades course_id,
GROUP BY course_id) c_avg
WHERE s.student_id = g.student_id
and c.course_id = g.course_id
and c_avg.course_id = g.course_id
GROUP BY course_name,student_id,student_name,c_avg.course_avg
HAVING avg(grade) > c_avg.course_avg
ORDER BY s.student_id,student_name
37
•
•
•
•
•
•
•
•
•
Slide 38
צירוף טבלאות ע”י Outer Join
• הצירוף הרגיל הנקרא גם Inner Joinמעביר לטבלת
התוצאה רק זוגות של שורות מ 2 -טבלאות עבורן
מתקיים תנאי הצירוף
• הצירוף החיצוני Outer Joinמעביר אל טבלת
התוצאה את כל השורות של אחת או שתי הטבלאות
המשתתפות בצירוף:
38
Slide 39
צירוף טבלאות ע”י Outer Join
• - Left Outer Joinלטבלת התוצאה עוברות כל
השורות מהטבלה השמאלית
• - Right Outer Joinלטבלה התוצאה עוברות כל
השורות מהטבלה הימנית
• - Full Outer Joinלטבלת התוצאה עוברות כל
השורות מ 2 -הטבלאות
39
Slide 40
צירוף טבלאות ע”י Outer Join -
תחביר הפקודה
40
Slide 41
צירוף טבלאות ע”י Outer Join -
דוגמא:
• הצג את רשימת ראשי המחלקה ,שם המחלקה
שבראשם הם עומדים ושמות הקורסים המוצעים ע”י
אותה מחלקה
41
Slide 42
צירוף טבלה אל עצמה
)(Reflexive Join, Self Join
• מאפשר התייחסות מספר פעמים אל אותה טבלה
42
Slide 43
תת שאילתא
•
•
•
•
•
•
יכולת הקינון מאפשרת לשפת SQLלהיקרא שפה מובנית
סדר ביצוע השאילתות הוא מתת השאילתא הפנימית ביותר ועד
לשאילתא הראשית
תת השאילתא מוקפת בסוגריים ומופיעה בתוך משפט WHERE
של השאילתא הראשית
תת השאילתא יוצרת טבלת תוצאה המכילה עמודה אחת בלבד או ערך
בודד
תת השאילתא אינה יכולה להכיל מיון
משפט WHEREשל תת-שאילתא יכול להכיל בעצמו תת שאילתא
נוספת -מבנה מקונן
43
Slide 44
תת שאילתא -דוגמא
44
Slide 45
תת שאילתא
• כאשר תת שאילתא מחזירה קבוצת ערכים ניתן לבדוק
אם הערכים של עמודה מהשאילתא הראשית מופיעים
בתוך קבוצת הערכים המוחזרים ע”י תת השאילתא
באמצעות האופרטור IN
45
Slide 46
תת שאילתא -דוגמאות
ומספר הקורס
46
Slide 47
תת שאילתא -דוגמאות
NAME, CITY
47
Slide 48
75 נקודות והציונים שלהם מעל4 סטודנטים שלא לקחו קורסים עם
SELECT student_id, student_name, course_name, points, grade •
students s, grades g, courses c
•
WHERE g.student_id=s.student_id
•
and c.course_id =g.course_id
•
and not exist (SELECT * from courses •
WHERE points=4)
and grade > 75
•
FROM
48
Slide 49
איחוד תוצאות של שאילתות
)(Union
49
Slide 50
חיתוך תוצאות של שאילתות )(Intersect
50
Slide 51
פקודות לעדכון בסיס הנתונים -הוספה של
שורה בודדת )(Single Row Insert
• מאפשרת הוספת שורה חדשה לטבלה קיימת
51
Slide 52
הוספה של שורה בודדת
• מאחר ואנו קובעים ערכים לכל העמודות ,ניתן גם:
52
Slide 53
(Multi-Row Insert) הוספת מספר שורות
חדשה
Using course_id
53
Slide 54
עדכון שורות )(Update
54
Slide 55
דוגמאות
.2007
’‘SUM2007
55
Slide 56
בטול שורות)(Delete
56
Slide 57
בטול שורות -דוגמאות
57
Slide 58
,ת.ז)
58
Slide 59
תרגילים
59
Slide 60
תרגילים
60
Slide 61
תרגילים
61