נושאים במסדי נתונים ת – אווירי תעשייה 5 שיעור 1 שיעור : נושאי הלימוד לה
רואי זרחיה [email protected] תעשייה אווירית – נושאי במסדי נתוני שיעור 51 שיעור נושאי הלימוד להיו: אופרטורי שמיועדי לשימוש ע קבוצות ותתי שאילתות פעולת UNION טבלאות מסוג VIEW PL/SQL רואי זרחיה 2009כל הזכויות שמורות © 1 רואי זרחיה [email protected] תעשייה אווירית – נושאי במסדי נתוני – SUB QUERIES (10תתי שאילתות תזכורת תזכורת :תת שאילתא היא שאילתא הנמצאת בתו שאילתא אחרת ,כ שתוצאת השאילתא הפנימית משמשת להערכת/חישוב השאילתא החיצונית. קיימי שני סוגי של תתי שאילתות: (1תתי שאילתות במשפטי WHEREו HAVING א( תתי שאילתות המחזירות ער יחיד – למדנו בשיעור קוד ב( תתי שאילתות המחזירות אוס& ערכי – למדנו בשיעור קוד (2תתי שאילתות במשפטי .FROM (2תתי שאילתות במשפטי :FROM צורת השימוש: SELECT …. ;FROM (Sub-Query) AS NewName חובה לבצע את פעולת הכינוי נחזור לדוגמא מתחילת השיעור :שמות האנשי שגרי בעיר של Ben Leviהפע לא כולל BENעצמ': מתבצעת כא' מכפלה קרטזית SELECT Name City FROM Students S, ( SELECT Students FROM Name = 'Ben Levi') AS ben WHERE ;)'( S.City = ben.City ) AND (Name <> 'Ben Levi WHERE הסבר :תת השאילתא תחזיר טבלה שתכיל ערכי ,וע טבלה זו נבצע מכפלה קרטזית לטבלת סטודנטי ועל המכפלה נפעיל את תנאי ה .where רואי זרחיה 2009כל הזכויות שמורות © 2 רואי זרחיה [email protected] תעשייה אווירית – נושאי במסדי נתוני UNION (11 באלגברת היחסי למדנו על פקודת UNIONכאשר רצינו לאחד תוצאות של טבלאות בעלי סכמות זהות )תואמות איחוד( ,על אותו עיקרו' נראה אי נוכל להפעיל את פקודת UNIONעל טבלאות ב :SQL דוגמא :נניח שטבלת הסטודנטי הייתה קיימת לנו ג באוניברסיטת בר איל' וג באוניברסיטת תלאביב ושר החינו היה רוצה לקבל את רשימת כל הסטודנטי בשני המוסדות )ת.ז + .ש( עבור סטודנטי שאינ גרי בירושלי: סכמת הסטודנטי בבראיל : Department MA CS StudentCity RG BB StudentName a b StudentID 1 2 סכמת הסטודנטי בתלאביב: Age 22 24 City TA TA Department PH MA SName c d Faculty ES ES SID 3 4 הבעיה הנראית לעי' היא ששתי הסכמות כא' שונות ואילו על מנת לאחד נתוני נצטר סכמות זהות ולכ' נצטר להפעיל את השאילתות הבאות: StudentID AS ID , StudentName AS Name SELECT BarIlanStudents FROM ;’StudentCity <> ‘Jerusalem WHERE UNION Name a b c d SID AS ID , SName AS Name SELECT TelAvivStudents FROM ;’City <> ‘Jerusalem WHERE ID 1 2 3 4 רואי זרחיה 2009כל הזכויות שמורות © 3 רואי זרחיה [email protected] תעשייה אווירית – נושאי במסדי נתוני נוכל לראות שפעולת ) UNIONכנ"ל חיתו וחיסור( מתבצעת בצורה הבאה: ][Query INTERSECT ][Query ][Query UNION ][Query ][Query MINUS ][Query חשוב לציי' שקיימת פקודה נוספת הנקראת UNION ALLשמטרתה לאחד 2טבלאות מבלי להסיר את הכפולי. Distinct SName SELECT Students FROM UNION ALL Distinct SName SELECT Students FROM רואי זרחיה 2009כל הזכויות שמורות © 4 רואי זרחיה [email protected] תעשייה אווירית – נושאי במסדי נתוני – VIEWS (12טבלאות מדומות VIEWהוא טבלה מדומה המגדירה שאילתת משתמש ,זהו בעצ מבנה נוס& שעדיי' לא למדנו הנכנס תחת הגדרת DDLונקרא טבלה מדומה. השוני של טבלה מדומה מטבלה רגילה: (1טבלה מדומה אינה מכילה ערכי בתוכה. VIEW (2הינה רק הגדרה במבנה של שאילתת SQLרגילה הנשמרת בבסיס הנתוני. (3הטבלה ממומשת ע"י הפעלת השאילתא רק בעת הצור. יתרונות: (1יכולת הצגה פשוטה יותר של נתוני שוני למשתמשי שוני. (2רמה גבוהה של בקרה ואבטחת מידע :מונע גישה לנתוני ולטבלאות עצמ' ומאפשר גישה רק ל ) VIEWבמקו למנוע גישה של אד מסוי מטבלאות מסוימות אפשר לאפשר לגשת רק ל VIEWמסוימי(. (3נית' להתייחס ל VIEWולפנות אליו כאילו היה טבלה אמיתית. (4אי' בזבוז של מקו כאשר משתמשי ב VIEWכי הרי ה DBMSשומר שאילתא ולא נתוני. (5עדכו' של טבלאות לא מצרי לבצע עדכו' של ה ) VIEWיתבצע אוטומטית בעת הריצה(. (6מאפשר למעבד השאילתות רמה גבוהה יותר של אופטימיזציה. חסרונות: (1מחושב בכל ריצה מחדש. (2א הטבלה עליה נשע' ה VIEWנמחקת ,ה VIEWהול לאיבוד ג כ'. רואי זרחיה 2009כל הזכויות שמורות © 5 רואי זרחיה [email protected] תעשייה אווירית – נושאי במסדי נתוני : שעליו נבצע את הדוגמאות עבור הפעולות לשיעור זהDB נגדיר טבלאות חדשות ב StudentID 1 2 3 4 5 6 7 StudentID 1 2 3 4 5 6 7 6 Students Name City Avi Cohen Ramat-Gan Avi Levin Ramat-Gan Ben Levi Tel-Aviv Chen Levin Tel-Aviv Debi Dvir Givatiim Zvi Kaner Givatiim Haim Itzhak Jerusalem Grades CourseID TestGrade 281 82 281 67 281 75 281 84 281 94 281 82 281 90 Department Computers Math Math Computers Math Computers Computers ExeGrade 85 84 88 87 86 88 83 © כל הזכויות שמורות2009 רואי זרחיה • רואי זרחיה [email protected] תעשייה אווירית – נושאי במסדי נתוני דוגמא: הצג את שמות הסטודנטי ואת עיר מגוריה עבור סטודנטי שלמדו במחלקה למדעי המחשב: Name, City Students S ;”S.Department = “computers City Ramat-Gan Tel-Aviv Givatiim Jerusalem SELECT FROM WHERE Name Avi Cohen Chen Levin Zvi Kaner Haim Itzhak זו הייתה שליפה פשוטה ,א כעת בואו נניח שראש המחלקה למדעי המחשב מרי /כל יו את אותה השאילתא א לפעמי הוא רוצה להוסי& מספר תנאי על שאילתא זו – לש כ קיימת לנו אפשרות לשמור טבלה זו כטבלה מדומה ז"א שנשמור את פקודת ה SQLתחת הש CompStudentsבצורה הבאה: CREATE VIEW CompStudents AS { SELECT Name, City FROM Students S WHERE ;”S.Department = “computers } כעת קיי לנו VIEWבש CompStudentsשע"י כל הרצה שלו נקבל את רשימת הסטודנטי למדעי המחשב. כעת נניח שראש המחלקה רוצה לסנ' מתו רשימה זו רק את הסטודנטי שגרי בתל אביב ,הוא יוכל לבנות את השאילתא הבאה: * CompStudents ;”City = “Tel-Aviv SELECT FROM WHERE ובעקבות זאת )למרות שרשמנו * ב SELECTהראשי( תתקבל התוצאה הבאה: City Tel-Aviv Name Chen Levin יוצגו 2העמודות מה VIEWהמתאימות לתנאי שהתווס&. רואי זרחיה 2009כל הזכויות שמורות © 7 רואי זרחיה [email protected] תעשייה אווירית – נושאי במסדי נתוני מאחורי הקלעי בעצ מתבצעת השאילתא הבאה: * CompStudents SELECT FROM ;”City = “Tel-Aviv WHERE Name, City Students S ”S.Department = “computers AND ;”City = “Tel-Aviv SELECT FROM WHERE מכא נוכל להבי את השלבי של בניית טבלה מדומה: (1 הגדרת טבלה מדומה חדשה )הגדרת השאילתא ויצירתה ב .(DB (2 הפעלת שאילתא רגילה הפונה כחלק מהשליפה לטבלה המדומה. (3 ה DBMSיוצר את השאילתא המשולבת )כמו שראינו בדוגמא האחרונה(. (4 מופעלת השאילתא המשולבת ומוחזרת התוצאה. הרכבת תצפיות: נית' להגדיר הרכבה של תצפיות ,ז"א נית' להגדיר תצפית הבנויה על בסיס תצפית אחרת: CREATE VIEW BestTalmidim AS ( * Select תצפית From Talmidim ) נוכל להשתמש בתצפית בביצוע JOINכאילו היא הייתה טבלה רגילה * Talmidim T , Courses C T.ID = C.ID רואי זרחיה 2009כל הזכויות שמורות © SELECT FROM WHERE 8 רואי זרחיה [email protected] תעשייה אווירית – נושאי במסדי נתוני חוקי שיש לזכור בעבודה ע תצפיות: .1התצפית תפסיק לפעול א בוצעה מחיקה של הטבלה עלייה היא בנויה. .2התצפית תפסיק לפעול א שונו שמות העמודות בטבלה עלייה היא בנויה .ז"א שהתצפית מושפעת משינויי סכמה א איני מושפעת משינויי .DATA .3נית' להוסי&/לעדכ'/למחוק שורות מתו תצפית קיימת בתנאי שהתצפית בנויה על טבלה אחת בלבד )ז"א תצפית ללא פעולות צירו& ,איחוד ,חיתו וכד'(. .4הרעיו' העומד מאחורי תצפית טובה הוא שנית' לשלו& מידע רלוונטי באופ' מחזורי מבלי להתייחס למורכבות בסיס הנתוני הקיי ומבלי צור לחזור ולכתוב את השאילתא בכל פע שנרצה לשלו&. .5תמיד נית' להוסי& עוד תנאי לתצפית קיימת בכדי לקבל תשובה ספציפית יותר וע"י כ בעצ נוכל להתאי תצפיות למנהלי שוני וללקוחות שוני וכ נוכל ליצור מידור ואבטחה טובי יותר. ביצועי שינוי ב טבלה דר" :VIEW .1שינוי של ערכי דר VIEWיגרור שינויי של טבלאות הבסיס. .2הקו המנחה הוא שלא נית' לפנות לער בטבלה שלא מיוצג ע"י .VIEW רואי זרחיה 2009כל הזכויות שמורות © 9 רואי זרחיה [email protected] תעשייה אווירית – נושאי במסדי נתוני שילוב של טבלאות משולבות ע תתשאילתא: לדוגמא :הגדר טבלה מדומה בש BestGradesשתציג את מס' הסטודנט והציו' שלו במבח' עבור כל הסטודנטי שקיבלו במבח' הסופי ציו' גבוה מהציו' הגבוה ביותר שסטודנט כלשהוא קיבל בתרגיל בכיתה. )Max(ExeGrade ;)Grades CREATE VIEW BestGrades AS SELECT StudentID, TestGrade FROM Grades G WHERE TestGrade > ( SELECT FROM BestGrades StudentID TestGrade 5 94 7 90 על אותו משקל נוכל להשתמש בצירו& בטבלאות מדומות ובכ ליצור יתרו' עצו :למשתמש נדמה כי הוא עובד מול טבלה אחת בעוד שבפועל מאחורי הקלעי יכולות להתבצע פעולות צירו& חישובי והתניות על מספר טבלאות שונות. הגדרת סכמת ה :VIEW נוכל להגדיר את שמות העמודות של ה VIEWהחדש שנרצה ליצור. לדוגמא: StudentsAvgGrade(Name, ID, AvgGrade) AS S.StudentName, S.StudentID, (TestGrade + ExeGrade)/2 ;Grades רואי זרחיה 2009כל הזכויות שמורות © CREATE VIEW SELECT FROM 10 רואי זרחיה [email protected] תעשייה אווירית – נושאי במסדי נתוני PL/SQL (12 PL/SQLמאפשר לנו להשתמש בכלי תכנות בשילוב ע פקודות ) SQLלולאות ,תנאי ,פונקציות וכד'( בעבודה ע"ג ORACLEובכ מפשט את העבודה למול בסיס הנתוני. א( פקודת SELECTבשילוב משתני DECLARE v_sname ;)VARCHAR2(10 v_rating ;)NUMBER(3 BEGIN SELECT sname, rating INTO v_sname, v_rating FROM Sailors WHERE ;'sid = '112 ;END נוכל לראות שע"י הרצת פקודת SELECTזו נוכל להרי /שאילתא ולהכניס את תוצאותיה לתו משתני, שמאוחר יותר נוכל לפנות ולהשתמש בערכי המאוחסני בה. הערה :שאילתא זו חייבת להחזיר שורה אחת בלבד כי הרי מטרתה היא לאחס' את המידע בתו המשתני שהוגדרו ולא להחזיר טבלת תשובות) .א השאילתא תחזיר פחות/יותר משורה אחת תצא הודעת שגיאה(. ב( פקודת IF >If <cond >then <command >elseif <cond2 >then <command2 else ><command3 ;end if רואי זרחיה 2009כל הזכויות שמורות © 11 רואי זרחיה [email protected] תעשייה אווירית – נושאי במסדי נתוני ג( פקודת LOOP DECLARE i ;number := 1 BEGIN FOR i IN 1..10 LOOP ;)INSERT INTO number VALUES(i ;END LOOP ;END ד( יצירת והפעלת פונקציות ופרוצדורות כשנרצה להרי /אוס& פקודות או מספר שאילתות אחת אחרי השנייה ,נוכל להשתמש באחד מהמנגנוני היעלי הקיימי בשפת :SQLהכוונה היא לשימוש בפונקציות או פרוצדורות. ע"י שימוש באחד מכלי אלו ,אנו בעצ יוצרי רוטינה במסד הנתוני ,שמרגע יצירתה ועד מחיקתה נוכל להפעיל אותה בצורה פשוטה ע"י קריאה לרוטינה ,כמו כ' נוכל להפעיל אותה מספר פעמי רב בכל זמ' שרק נרצה. יצירת פרוצדורה CREATE [OR REPLACE] PROCEDURE procedure_name ])[(parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, . . . AS { ;PL/SQL Block } זהו מבנה פשוט של פרוצדורה ,נוכל לראות שמלבד הגדרת ש הפרוצדורה החדשה ,נוכל ג להגדיר את הפרמטרי החיצוניי שפרוצדורה זו יכולה לקבל מהאפליקציה העוטפת אותה )הכנסת משתני חיצוניי לתו הפרוצדורה( ,כאשר המש הקוד הינו בעצ אוס& פקודות )בלוק של פקודות( לביצוע. רואי זרחיה 2009כל הזכויות שמורות © 12 רואי זרחיה [email protected] תעשייה אווירית – נושאי במסדי נתוני יצירת פונקציה פונקציה הינה כלי כמעט זהה לפרוצדורה ע הבדל אחד חשוב ,הפונקציה מחזירה ער. )CREATE [or replace] FUNCTION rating_message(rating IN NUMBER RETURN VARCHAR2 AS קלט לפונקציה = מספר BEGIN פלט הפונקציה = מחרוזת { IF rating > 7 THEN ;'return 'You are great ELSIF rating >= 5 THEN ;'return 'Not bad ELSE ;'return 'Pretty bad ;END IF } ;END ה( יצירת טריגרי טריגר הוא פעולה שמסד הנתוני צרי לבצע כאשר בוצעה פעולה ששינתה ער או מבנה במסד הנתוני עצמו ,הטריגרי ה בעצ פרוצדורות מיוחדות שנרצה להפעיל כאשר מישהו ביצע פעולה שגררה שינוי במסד הנתוני. לדוגמא: (1נגדיר טריגר שיופעל כאשר תתבצע הכנסה של שורה חדשה לתו מסד הנתוני ,כאשר מטרת הטריגר הינה לבדוק הא הערכי המוכנסי תקיני. (2כאשר מוכנס סטודנט חדש לבסיס הנתוני ,נבדוק את תארי הלידה שלו ואז א גילו מעל 20נכניס אותו לטבלת סטודנטי וא גילו מתחת ל 20נכניס אותו לטבלת צעירי. גו& הטריגר בעצ מכיל אוס& פקודות ) (PL/SQLכאשר ישנ מספר סוגי רב של טריגרי ,א הנפוצי ה מסוג :עדכו' ,מחיקה והוספה. רואי זרחיה 2009כל הזכויות שמורות © 13 רואי זרחיה [email protected] תעשייה אווירית – נושאי במסדי נתוני עצמו ,הטריגרי ה בעצ פרוצדורות מיוחדות שנרצה להפעיל כאשר מישהו ביצע פעולה שגררה שינוי ו( יצירת אינדקסי יצירת אינדקס בטבלה קיימת מאפשרת לנו לאתר שורות מידע במהירות וביעילות רבה יותר )לדוגמא: כשנכתוב שאילתא ובתנאי ה Whereנרצה להתייחס לעמודת Nameשאינה מפתח בטבלה ,כמות הזמ' שיקח לעבור על עמודה זו יהיה נמו בהרבה א עמודת Nameתוגדר כבעלת אינדקס(. יצירת אינדקס: ש האינדקס החדש CREATE [ UNIQUE ] INDEX index_name ) ON table_name ( col1, col2, …, colN ש העמודה בטבלה שעליה יתווס& האינדקס רואי זרחיה 2009כל הזכויות שמורות © ש הטבלה שעליה נרצה להוסי& אינדקס חדש 14 רואי זרחיה [email protected] תעשייה אווירית – נושאי במסדי נתוני מחיקת אינדקס: DROP INDEX index_name נית' ליצור אינדקסי על עמודה אחת או מספר עמודות כאשר כל אינדקס מקבל ש ייחודי משלו, מומל /לשי אינדקסי על עמודות שכמות החיפושי עליה הינה גבוהה. המשתמש שירצה לבצע שליפה מבסיס הנתוני אינו רואה את האינדקס וזאת כי האינדקס נמצא מאחורי הקלעי ) (DDLומטרתו היא שיפור ביצועי השליפות )חשוב לדעת שביצוע פעולת UPDATE לטבלה הכוללת אינדקסי תיקח זמ' רב יותר מאשר טבלה ללא אינדקסי וזאת בשל העובדה שיש צור לעדכ' ג את האינדקסי עצמ בעת עדכו' הטבלה(. אינדקס מסוג Unique Indexהינו אינדקס על עמודה שבה לא יכולי להיות 2ערכי בעלי ער זהה. לדוגמא :נוכל ליצור אינדקס לטבלת Studentsעבור עמודת Nameוזאת בכדי שנוכל לשלו& סטודנטי לפי שמות בצורה מהירה ויעילה יותר: CREATE INDEX StudentNameIndex )ON Students (Name א שמות הסטודנטי היו יושבי בשתי עמודות שונות )ש פרטי וש משפחה( היינו יכולי ליצור את האינדקס כ: CREATE INDEX StudentNameIndex )ON Students (LastName, FirstName רואי זרחיה 2009כל הזכויות שמורות © 15 רואי זרחיה [email protected] תעשייה אווירית – נושאי במסדי נתוני ז( יצירת סמכויות – :Authority בהנחה ומספר אנשי עובדי ע"ג מסד הנתוני שלנו ,נוכל להגדיר את שמות המשתמשי שאנו רוצי לתת לה סמכויות לכניסה ולניהול בסיס הנתוני שלנו. יצירת משתמש חדש: נוכל ליצור משתמש חדש במערכת ולזהותו ע"י שימוש בסיסמא שתוגדר } CREATE USER user_name IDENTIFIED { by password CREATE USER roei IDENTIFIED by 12345 סיסמת הכניסה של רואי ל DB מחיקת משתמש קיי: DROP USER user_name לאחר שהקצנו את האנשי שיכולי לעבוד על מסד הנתוני שלנו )מת' סמכויות( נוכל להגדיר את ההרשאות או התפקידי של כל אחד מה ,ז"א מה מותר ואסור לכל אחד מהאנשי לבצע. הגדרת הרשאות ):(privilege מת' הרשאה לביצוע פעולה או פעולות על מסד הנתוני עבור משתמש מסוי: סוג ההרשאה ] GRANT privilege TO user [ WITH ADMIN OPTION GRANT connect TO roei מת' הרשאת התחברות ל DBלמשתמש רואי REVOKE privilege FROM user הגדרת תפקיד ):(role תפקיד הינו אוס& של הרשאות בודדות תחת כובע אחד )הרשאות למתכנת ,למנהל וכד'(: הגדרת תפקיד בחברה מסוג "מתכנת" והקצאת הרשאות ספציפיות עבור מתכנת בחברה סוג תפקיד Create role programmer_role Grant select, insert on customer to programmer_role ] GRANT role TO { user | role } [ WITH ADMIN OPTION מת' סט הרשאות של מתכנת למשתמש דני GRANT programmer_role TO dani } REVOKE role FROM { user | role רואי זרחיה 2009כל הזכויות שמורות © 16
© Copyright 2024