1 / 51

SQL Structured Query Language

SQL Structured Query Language. בתרגולים הקודמים. מושגי יסוד פעולות ב- SQL שינוי תוכן מסד נתונים DML שינוי מבנה מסד נתונים DDL שאילתות ב- SQL WHERE האופרטור LIKE ערכים חסרים NULL. נושאי התרגול. שאילתות ב- SQL פונקציות סטטיסטיות GROUP BY HAVING ALIASING SUBQUERIES כמתים

ramirezc
Download Presentation

SQL Structured Query Language

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Structured Query Language DBMS - 236363, שפות שאילתה: SQL

  2. בתרגולים הקודמים • מושגי יסוד • פעולות ב-SQL • שינוי תוכן מסד נתונים DML • שינוי מבנה מסד נתונים DDL • שאילתות ב-SQL • WHERE • האופרטור LIKE • ערכים חסרים NULL DBMS - 236363, DDL+DML: SQL

  3. נושאי התרגול • שאילתות ב-SQL • פונקציות סטטיסטיות • GROUP BY • HAVING • ALIASING • SUBQUERIES • כמתים • EXISTS • טריגרים DBMS - 236363, DDL+DML: SQL

  4. דוגמא של מסד נתונים - תזכורת מסד נתונים של ספרייה • Ordered • Cust_Id • Book_Id • Order_Date • Books • Book_Id • Book_Name • Year • Max_Time • Faculty • Pages • Customers • Cust_Id • Cust_Name • Faculty • Borrowed • Cust_Id • Book_Id • From_Date • To_Date DBMS - 236363, שפות שאילתה: SQL

  5. פונקציות סטטיסטיות • ב- SQL ישנן הפונקציות הסטטיסטיות הבאות: • MIN – מינימום • MAX – מקסימום • AVG – ממוצע • SUM – סכום • COUNT – מספר הרשומות • כל אחת מפונקציות אלה פועלת על קבוצת ערכים ומחזירה ערך אחד. DBMS - 236363, שפות שאילתה: SQL

  6. דוגמא • דוגמא: חשב את מספר העמודים הממוצע והמקסימלי בין כל הספרים: • פתרון: SELECT AVG(Pages), MAX(Pages) FROM Books; DBMS - 236363, שפות שאילתה: SQL

  7. דוגמא – המשך SELECT AVG(Pages), MAX(Pages)FROM books; DBMS - 236363, שפות שאילתה: SQL

  8. דוגמא – המשך SELECT AVG(Pages), MAX(Pages)FROM books; DBMS - 236363, שפות שאילתה: SQL

  9. פונקציות סטטיסטיות – המשך • ניתן לחשב פונקציות סטטיסטיות רק על חלק מרשומות הטבלה ע"י שימוש באופציה WHERE. • לדוגמא: SELECT COUNT (Book_Name) FROM Books WHERE Year = 1998; DBMS - 236363, שפות שאילתה: SQL

  10. פונקציות סטטיסטיות – המשך SELECT COUNT(Book_name) FROM Books WHERE Year=1998 DBMS - 236363, שפות שאילתה: SQL

  11. דוגמא – המשך • Book_Name: COUNT DBMS - 236363, שפות שאילתה: SQL

  12. פעולות סטטיסטיות על ערכי NULL • כל הפונקציות הסטטיסטיות מתעלמות מערכי NULL. • יוצא מן הכלל:COUNT(*). ההבדל בין count לבין פונקציות סטטיסטיות אחרות הוא ש-count תחזיר 0 כאשר הוא תקבל אף ערך והפונקציות הסטטיסטיות האחרות יחזירו null. לכן פונקציות סטטיסטיות שמקבלות עמודה של null יחזירו null (הן מתעלמות מערכי ה-null ומקבלות כלום, לכן מחזירות null) פרט ל-count שתחזיר 0 (ו-count(*) תחזיר את מספר השורות) DBMS - 236363, שפות שאילתה: SQL

  13. פעולות סטטיסטיות על ערכי NULL Borrowed: SELECT MAX(To_Date) FROM Borrowed; יחזיר NULL SELECT COUNT(To_Date) FROM Borrowed; יחזיר 0 SELECT COUNT(*) FROM Borrowed; יחזיר 1 DBMS - 236363, שפות שאילתה: SQL

  14. פונקציות סטטיסטיות – המשך • דוגמא: שליפת מספר הספרים בכל שנה • פתרון שגוי: SELECT Year, COUNT(Book_Id) FROM Books; • לא חוקי! (הרבה ערכים של Year, ערך אחד של COUNT). DBMS - 236363, שפות שאילתה: SQL

  15. קיבוץ – GROUP BY • הפעלת פונקציות סטטיסטיות על קבוצות של רשומות. • המשך הדוגמא (תיקון): SELECT Year, COUNT(Book_Id) FROM Books GROUP BY Year; • לכל ערך של Year, ה-COUNT מחושב בנפרד. DBMS - 236363, שפות שאילתה: SQL

  16. דוגמא – המשך(GROUP BY) SELECT Year, COUNT(Book_Id) FROM Books GROUP BY Year; DBMS - 236363, שפות שאילתה: SQL

  17. דוגמא – המשך(GROUP BY) SELECT Year, COUNT(Book_Id) FROM Books GROUP BY Year; DBMS - 236363, שפות שאילתה: SQL

  18. קיבוץ – המשך • דוגמא שגויה אחרת: SELECT Faculty, COUNT(Book_Id) FROM Books GROUP BY Year; • לא חוקי! לאחר הקיבוץ לפי Year, בכל קבוצה יכולים להיות ערכי Faculty שונים. • כלל: בנוסף לפעולות סטטיסטיות, מותר לשלוף רק שדות לפיהם מתבצע הקיבוץ (וביטויים). DBMS - 236363, שפות שאילתה: SQL

  19. בחירה לאחר קיבוץ - HAVING • האופציה HAVING condition: בחירת חלק מהקבוצות המתקבלות מ-GROUP BY . • התנאי condition: פונקציות סטטיסטיות, שדות לפיהם מתבצע הקיבוץ, ביטויים. DBMS - 236363, שפות שאילתה: SQL

  20. דוגמא (HAVING) • דוגמא: מה מחזירה השאילתה הבאה? SELECT Year, COUNT(Book_Id) FROM Books GROUP BY Year HAVING AVG(Pages) > 400; • הסבר: לאחר הקיבוץ לפי Year, מבין כל הקבוצות נבחרות כאלה שבהן ממוצע מספרי העמודים גדול מ- 400. DBMS - 236363, שפות שאילתה: SQL

  21. דוגמא – המשך (HAVING) SELECT Year, COUNT(Book_Id) FROM Books GROUP BY Year HAVING AVG(Pages) > 400; DBMS - 236363, שפות שאילתה: SQL

  22. AVG(Pages) > 400 Year COUNT(Book_Id) 1988 1 2001 1 1985 1 דוגמא – המשך (HAVING) SELECT Year, COUNT(Book_Id) FROM Books GROUP BY Year HAVING AVG(Pages) > 400; DBMS - 236363, שפות שאילתה: SQL

  23. בחירה: WHEREלעומת HAVING • WHERE: בחירת רשומות לפני הקיבוץ • HAVING: בחירת קבוצותאחרי הקיבוץ. • דוגמא: בין כל הספרים עם יותר מ-200 עמודים, חשב בכל שנה את מס' הספרים שיצאו לאור,בתנאי שבממוצע מספר העמודים באותה שנה גדול מ- 400. DBMS - 236363, שפות שאילתה: SQL

  24. דוגמא – המשך • דוגמא: בין כל הספרים עם יותר מ-200 עמודים, חשב בכל שנה את מס' הספרים שיצאו לאור, בתנאי שבממוצע מספר העמודים באותה שנה גדול מ- 400. SELECT Year, COUNT(Book_Id) FROM Books WHERE Pages > 200 GROUP BY Year HAVING AVG(Pages) > 400; DBMS - 236363, שפות שאילתה: SQL

  25. דוגמא – המשך SELECT Year, COUNT(Book_Id) FROM Books WHERE Pages > 200 GROUP BY Year HAVING AVG(Pages) > 400; Books: WHERE Pages > 200 DBMS - 236363, שפות שאילתה: SQL

  26. דוגמא – המשך SELECT Year, COUNT(Book_Id) FROM Books WHERE Pages > 200 GROUP BY Year HAVING AVG(Pages) > 400; DBMS - 236363, שפות שאילתה: SQL

  27. דוגמא – המשך • הסבר (שלבים): • WHERE: בחירת הרשומות של הספרים שמכילים יותר מ- 200 עמודים. • GROUP BY: קיבוץ הרשומות לקבוצות, כך שבכל קבוצה יש לכל הרשומות (הספרים) אותו Year. • פונ' סטטיסטיות: חישוב מספר העמודים הממוצע בכל קבוצה, ואת מס' הספרים בכל קבוצה. • HAVING: בחירת הקבוצות בהן הממוצע הוא לפחות 400. DBMS - 236363, שפות שאילתה: SQL

  28. דוגמא נוספת • דוגמא: מה עושה שאילתה זו? SELECT Cust_Name, Customers.Cust_Id, COUNT(Book_Id) FROM Customers LEFT OUTER JOIN Ordered ON (Customers.Cust_Id = Ordered. Cust_Id) GROUP BY Customers.Cust_Id, Cust_Name; DBMS - 236363, שפות שאילתה: SQL

  29. דוגמא – המשך (OUTER JOIN) SELECT Customer.Cust_Id, Cust_Name, Book_Id FROM Customer LEFT OUTER JOIN Ordered ON( Customer.Cust_Id = Ordered. Cust_Id ); SELECT Customers.Cust_Id, Cust_Name, COUNT(Book_Id) FROM Customers LEFT OUTER JOIN Ordered ON( Customers.Cust_Id = Ordered. Cust_Id ) GROUP BY Customers.Cust_Id, Cust_Name; DBMS - 236363, שפות שאילתה: SQL

  30. דוגמא – המשך • תשובה: השאילתה מחזירה לכל לקוח ID, שם ומס' הספרים שהזמין. • תוצאה: • מה הייתה התוצאה ללא ה-OUTER JOIN? DBMS - 236363, שפות שאילתה: SQL

  31. ALIAS: שליפה מאותה טבלה פעמיים SELECT DISTINCT C1.Cust_Name FROM Customers C1, Customers C2 WHERE C1. Cust_Name = C2. Cust_Name AND C1.Cust_Id <> C2.Cust_Id; • שאלה: מה מחזירה השאילתה? • תשובה: שמות כל הלקוחות כך שיש בספרייה עוד לקוח עם אותו שם. DBMS - 236363, שפות שאילתה: SQL

  32. תתי-שאילתות – SUBQUERIES • מוטיבציה: התנאי ב-WHERE מכיל ביטוי שלא ידוע מראש, אלא תלוי בתוכן המסד. • דוגמא: שליפת כל שמות הספרים שיצאו לאור באותה שנה כמו ספר מס' 1112. SELECT Book_Name FROM Books WHERE Year = (SELECT Year FROM Books WHERE Book_Id = 1112); DBMS - 236363, שפות שאילתה: SQL

  33. תתי-שאילתות – המשך • תת-שאילתה יכולה לשלוף כמה ביטויים. • אפשר להשוות לערכים של כל הביטויים האלה בו זמנית. • דוגמא: שליפת כל הספרים שיצאו לאור באותה שנה ונמצאים באותה פקולטה כמו ספר מס' 1112. SELECT Book_Name FROM Books WHERE (Year, Faculty) = (SELECT Year, Faculty FROM Books WHERE Book_Id = 1112); DBMS - 236363, שפות שאילתה: SQL

  34. תתי-שאילתות – המשך • תת שאילתה יכולה להופיע בחלק ה-From. • תת השאילתה חייבת לקבל Alias. • דוגמא: שליפת מספרי הספרים שהוזמנו אחרי 20-0ct-98 ע"י לקוח 12345. SELECT Book_Id FROM(SELECT * FROM Ordered Where Order_Date > '20-0ct-98') O WHERE O. Cust_Id = 1234; DBMS - 236363, שפות שאילתה: SQL

  35. כמתים • מוטיבציה: השוואת ערך בודד לקבוצת ערכים • בהינתן ערך x וקבוצה A, נרצה לבטא: • x גדול מכל הערכים ב-A (yA:x>y) • x קטן מערך כלשהו ב-A (yA:x<y) • xA (yA:x=y) • כמתים ב-SQL: • ANY -- קיים • ALL -- לכל DBMS - 236363, שפות שאילתה: SQL

  36. כמתים – המשך • הכימות נעשה ע"י כתיבת המילה ANY או ALL אחרי סימן ההשוואה • דוגמא: שליפת שמות ספרים בעלי יותר עמודים מכל הספרים בפקולטה למדעי המחשב. SELECT Book_Name FROM Books WHERE Pages >ALL (SELECT Pages FROM Books WHERE Faculty = 'CS'); DBMS - 236363, שפות שאילתה: SQL

  37. כמתים – המשך (אופרטור שייכות) • ניתן לממש אופרטור שייכות באמצעות כמתים: • =ANYIN • <>ALLNOT IN • דוגמא: SELECT Book_Name, Year FROM Books WHERE Year = ANY (SELECT Year FROM Books WHERE Faculty = ‘MED’); SELECT Book_Name, Year FROM Books WHERE Year IN (SELECT Year FROM Books WHERE Faculty = 'MED'); DBMS - 236363, שפות שאילתה: SQL

  38. תתי-שאילתות מקוננות • ניתן לקונן תתי-שאילתות • דוגמה: כל הפקולטות שמחזיקות ספרים שהזמין לקוח בשם ליאור אדרי: SELECT Faculty FROM Books WHERE Book_Id IN (SELECT Book_Id FROM Ordered WHERECust_Id IN (SELECT Cust_Id FROM Customers WHERECust_Name = 'Lior Edri')); DBMS - 236363, שפות שאילתה: SQL

  39. תתי-שאילתות – המשך • תתי-שאילתות לעומת JOIN • דוגמא: נכתוב את אותה שאילתה ללא שימוש בתתי שאילתות SELECT B.Faculty FROM Books B, Customers C, Ordered O WHERE B.Book_Id = O.Book_Id AND C.Cust_Id = O. Cust_Id AND C.Cust_Name = 'Lior Edri'; • שאלה: איזו מהן יותר יעילה? • תשובה: תלוי באופטימיזציה בשרת, בד"כ הפתרון עם תתי-השאילתות יעיל יותר DBMS - 236363, שפות שאילתה: SQL

  40. תתי-שאילתות קשורות הדדית • מוטיבציה: יש קשר דו-כיווני בין הפרמטרים של השאילתה החיצונית והפנימית • דוגמה: החזירו את מספרי הספרים ,שמות הספרים וזמן ההשאלה עבור ספרים שניתן להשאיל אותם לתקופה ארוכה מהממוצע באותה פקולטה SELECT Book_Id, Book_Name, Max_Time FROM Books B WHERE Max_Time > (SELECT AVG(Max_Time) FROM Books WHERE Faculty = B.Faculty); DBMS - 236363, שפות שאילתה: SQL

  41. אופרטור EXISTS • EXISTS הוא אופרטור בוליאני שפועל על תת-שאילתה. • מחזיר True אם תת-השאילתה מחזירה ערך כלשהו, False אם אינה מחזירה דבר. • דוגמה: שליפת כל שמות הלקוחות שהזמינו ספר כלשהו SELECT Cust_Name FROM Customers C WHERE EXISTS (SELECT * FROM Ordered WHERECust_Id = C.Cust_Id); DBMS - 236363, שפות שאילתה: SQL

  42. מבטים – VIEWS • מוטיבציה: להתייחס לפלט של שאילתה כאל טבלה נפרדת (בלי ליצור אותה פיזית). • בפועל: השאילתה שמגדירה את המבט נשמרת במסד הנתונים ומופעלת כל פעם מחדש. • אינטואיציה: המבט מהווה מעין "חלון" דרכו אפשר להסתכל על חלק ממסד הנתונים. • מטרה: הסתרת מידע, פישוט שאילתות... DBMS - 236363, DDL+DML: SQL

  43. מבטים - המשך • יצירת מבט: CREATE VIEW view_name AS SELECT…; • דוגמא: CREATE VIEW CSBooks AS SELECT Book_Id, Book_Name, Max_Time FROM Books WHERE Faculty = 'CS'; • ב-CSBooksמוסתרות כל העמודות של Books פרט ל- Book_Id, Book_Name, Max_Time וכל השורות פרט לספרים של מדעי מחשב. • בשאילתה אסור שיופיע ORDER BY. DBMS - 236363, DDL+DML: SQL

  44. Book_Id Book_Name Max_Time 1111 Database Systems 7 1112 Database Systems 14 CSBooks 1113 Database Systems 7 2222 Database And Knowledge 1 דוגמא – המשך Books: DBMS - 236363, DDL+DML: SQL

  45. מבטים – המשך (שאילתות) • על מבט ניתן לבצע שאילתות ממש כאילו היה טבלה. • דוגמה: שליפת כל הספרים של מדעי מחשב שמשך ההשאלה שלהם הוא 7 ימים. SELECT Book_Name FROM CSBooks WHERE Max_Time = 7; DBMS - 236363, DDL+DML: SQL

  46. מבטים – המשך (מחיקה) • מחיקת מבט: DROP VIEW • דוגמה: DROP VIEW CSBooks; • לטבלה שעליה מבוסס המבט לא קורה כלום. DBMS - 236363, DDL+DML: SQL

  47. Triggers • Custom code that fires upon database updates • Used for: • Integrity testing (throws exception) • Propagating updates among relations • A trigger language has two components: • A mapping of a function to update events • A programming language for functions • Example: PL/SQL combines SQL with ordinary program controls (conditions, loops, etc.)

  48. Example 1 2 4 6 5 3 CREATE TABLE Node( idint PRIMARY KEY, color text ) CREATE TABLE Edge( srcint REFERENCES Node(id), tgtint REFERENCES Node(id), PRIMARY KEY (src,tgt) ) • Right now, we cannot add any edge before we add the adjacent node to the Node relation • We would like to allow an easier behavior: whenever an edge is added, its nodes are added with the default black color

  49. Trigger Example A mapping of a function to update events CREATE TRIGGER edge_trigger BEFORE INSERT ON Edge FOR EACH ROW EXECUTE PROCEDURE add_missing_nodes(); Function declaration CREATE OR REPLACE FUNCTION add_missing_nodes() RETURNS TRIGGER AS $$ BEGIN IF (NEW.src NOT IN (SELECT id FROM Node)) THEN INSERT INTO Node VALUES(NEW.src,'black'); END IF; IF (NEW.tgt NOT IN (SELECT id FROM Node)) THEN INSERT INTO Node VALUES(NEW.tgt,'black'); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; PL/SQL code

  50. Some Built-In Variables • NEW: new tuple (insert/update) • OLD: old tuple (update/delete) • Trigger info: • TG_NAME, TG_WHEN, TG_OP, TG_TABLE_NAME

More Related