460 likes | 701 Views
SQL : שינוי מסד הנתונים- פעולות DDL , DML. פעולות ב- SQL : שאילתות שינוי תוכן מסד נתונים (עדכון, הוספה של רשומות, וכו'), DML – Data Manipulation Language שינוי מבנה מסד נתונים (יצירה, מחיקה של טבלאות, וכו'), DDL – Data Definition Language. עדכון – UPDATE. UPDATE tablename
E N D
SQL: שינוי מסד הנתונים-פעולותDDL , DML פעולות ב-SQL: • שאילתות • שינוי תוכן מסד נתונים (עדכון, הוספה של רשומות, וכו'), DML – Data Manipulation Language • שינוי מבנה מסד נתונים (יצירה, מחיקה של טבלאות, וכו'), DDL – Data Definition Language DBMS - 236363, DDL+DML: SQL
עדכון – UPDATE UPDATE tablename SET column-assignment-list WHERE conditional-expression ; DBMS - 236363, DDL+DML: SQL
עדכון – UPDATE • מטרה: עדכון ערכים ברשומות קיימות במסד הנתונים. • אפשרות ראשונה: השמה של ערכים קבועים בעמודות מסוימות בכל הרשומות. • דוגמא: העברת כל הספרים לספרייה מרכזית, והגבלת משך ההשאלה לשבוע ימים. • UPDATE Books SET Max_Time = 7, Faculty = 'GEN'; DBMS - 236363, DDL+DML: SQL
דוגמא - המשך Books: DBMS - 236363, DDL+DML: SQL
דוגמא - תוצאה UPDATE BooksSET Max_Time = 7, Faculty = 'GEN': DBMS - 236363, DDL+DML: SQL
UPDATE (המשך...) • עדכון חלק מהרשומות: הוספת תנאי WHERE condition • דוגמא: הגבלת משך ההשאלה לשבוע ימים לספרים השייכים לספריית מדעי המחשב. UPDATE Books SET Max_Time = 7 WHERE Faculty = 'CS'; DBMS - 236363, DDL+DML: SQL
דוגמא - המשך Books: DBMS - 236363, DDL+DML: SQL
דוגמא - תוצאה UPDATE Books SET Max_Time = 7 WHERE Faculty = 'CS': DBMS - 236363, DDL+DML: SQL
UPDATE (המשך...) • אפשרות נוספת: השמה של ביטויים. • דוגמה: הארכת משך ההשאלה לכל הספרים ביום אחד. UPDATE Books SET Max_Time = Max_Time + 1; DBMS - 236363, DDL+DML: SQL
דוגמא - המשך Books: DBMS - 236363, DDL+DML: SQL
דוגמא - תוצאה UPDATE Books SET Max_Time = Max_Time + 1: DBMS - 236363, DDL+DML: SQL
UPDATE – שימוש בתתי-שאילתות • הערך החדש יכול להיות תוצאה של תת-שאילתה • דוגמא: לכל ספר יש לעדכן את משך ההשאלה למקסימלי באותה פקולטה. UPDATE Books SET Max_Time = (SELECT MAX(Max_Time) FROM Books B WHERE B.Faculty = Books.Faculty); DBMS - 236363, DDL+DML: SQL
MAX דוגמא - המשך Books: DBMS - 236363, DDL+DML: SQL
דוגמא – תוצאה UPDATE Books SET Max_Time = (SELECT MAX(Max_Time) FROM Books B WHERE B.Faculty = Books.Faculty); DBMS - 236363, DDL+DML: SQL
UPDATE – המשך • הערות: • כל החישובים בשאילתא מתבצעים, כביכול, על "העתק" זמני של הטבלה שאינו מתעדכן בזמן ביצוע ההוראה. • כלומר, עדכון של רשומה אינו משפיע על החישובים הבאים שמתבצעים עבור הרשומות הבאות. DBMS - 236363, DDL+DML: SQL
הוספה - INSERT INSERT INTO tablename [( column-list )] VALUES ( constant-list ) ; DBMS - 236363, DDL+DML: SQL
הוספה - INSERT • מטרה: הוספת רשומות חדשות • אפשרות ראשונה: הוספת רשומה אחת נתונה • דוגמא: הוספת לקוח לטבלת Customers. INSERT INTO Customers VALUES(78901, 'Roy Peled', 'EE'); DBMS - 236363, DDL+DML: SQL
דוגמה - המשך Customers: DBMS - 236363, DDL+DML: SQL
דוגמא - תוצאה INSERT INTO Customers VALUES (78901, 'Roy Peled', 'EE'): DBMS - 236363, DDL+DML: SQL
INSERT – המשך • ניתן לאתחל רק חלק מהשדות. • דוגמא: INSERT INTOCustomers(Cust_Id,Cust_Name) VALUES(78901, 'Roy Peled'); • הערות: • שדות לא מאותחלים (Faculty) יקבלו ערך NULL. • אם בעמודה Faculty אסור שיופיע NULL, ההוראהINSERT אינה חוקית, ולכן היא לא תתבצע. DBMS - 236363, DDL+DML: SQL
דוגמה - המשך Customers: DBMS - 236363, DDL+DML: SQL
דוגמא - תוצאה INSERT INTO Customers(Cust_Id, Cust_Name) VALUES (78901, 'Roy Peled'): DBMS - 236363, DDL+DML: SQL
INSERT – שימוש בשאילתות • הוספת רשומות המתקבלות כתוצאה של שאילתה. • דוגמא: הכנסת כל הלקוחות שהזמינו ספרים לטבלהReaders . INSERT INTO Readers( Id, Name) (SELECT Cust_Id, Cust_Name FROM Customers C WHERE EXISTS (SELECT * FROM Ordered WHERE Cust_Id = C.Cust_Id)); • הוראה זו חוקית רק אם טבלה Readers קיימת. DBMS - 236363, DDL+DML: SQL
Cust_Id Cust_Name Faculty Cust_Id Book_Name Order_Date 12345 Moshe Cohen CS 12345 Database Systems 14-Oct-2002 23456 Avi Barak EE 45678 Anatomy 24-Oct-2002 34567 Avi Barak MED 12345 Database And Knowledge 30-Oct-2002 45678 Lior Edri EE 45678 Electronic Circuits 12-Oct-2002 56789 Moshe Cohen EE 67890 Moshe Cohen EE SELECT Cust_Id, Cust_Name FROM Customers C WHERE EXISTS (SELECT * FROM Ordered WHERE Cust_Id = C.Cust_Id): Cust_Id Cust_Name 12345 Moshe Cohen 45678 Lior Edri דוגמה - המשך Ordered: Customers: Readers: DBMS - 236363, DDL+DML: SQL
INSERT – המשך • INSERT מתבצעת על "העתק" זמני של הטבלה. • דוגמא: INSERT INTO Readers (SELECT * FROM Readers); • הוראה זו לא לא תכנס ללולאה אינסופית אלא רק תכפיל את הטבלה. DBMS - 236363, DDL+DML: SQL
Readers: Cust_Id Cust_Name Faculty 12345 Moshe Cohen CS 45678 Lior Edri EE Cust_Id Cust_Name Faculty 12345 Moshe Cohen CS 45678 Lior Edri EE תוצאה INSERT INTO Readers (SELECT * FROM Readers); Readers: 12345 Moshe Cohen CS 45678 Lior Edri EE DBMS - 236363, DDL+DML: SQL
מחיקה – DELETE DELETE FROM tablename WHERE conditional-expression DBMS - 236363, DDL+DML: SQL
מחיקה – DELETE • ההוראה DELETE מוחקת רשומות מתוך הטבלה. • בהוראה זו צריך רק להגדיר אלו רשומות רוצים למחוק. • דוגמה: מחיקת כל ההזמנות. DELETE FROM Ordered; • הטבלה עצמה אינה נמחקת, אך היא נותרת ריקה. DBMS - 236363, DDL+DML: SQL
DELETE – המשך (WHERE) • אופציה WHERE מאפשרת לבחור אלו רשומות ימחקו מהטבלה. • דוגמה: מחיקת כל ההזמנות של הלקוח בעל מספר מזהה 12345. DELETE FROM Ordered WHERE Cust_Id = 12345; DBMS - 236363, DDL+DML: SQL
דוגמה – המשך Ordered: DELETE FROM Ordered WHERE Cust_Id = 12345; Ordered: DBMS - 236363, DDL+DML: SQL
DELETE – המשך • בתוך התנאי אפשר להשתמש בביטויים בוליאניים יותר מורכבים ובשאילתות. • דוגמה: ביטול כל ההזמנות של אנשים שאינם מופיעים בטבלת הלקוחות. DELETE FROM Ordered WHERE NOT EXISTS (SELECT * FROM Customers WHERE Cust_Id = Ordered.Cust_Id); DBMS - 236363, DDL+DML: SQL
דוגמה - המשך Customers: Ordered: DBMS - 236363, DDL+DML: SQL
דוגמא - תוצאה Ordered: DBMS - 236363, DDL+DML: SQL
יצירת טבלה – CREATE TABLE • יצירת טבלה ריקה: CREATE TABLE table (column_name column_type [(length)] [NOT NULL],…); • יצירת טבלה המכילה תוצאה של שאילתה: CREATE TABLE table AS SELECT…; DBMS - 236363, DDL+DML: SQL
דוגמא • CREATE TABLE EXAMPLE (AB VARCHAR(15), CD INTEGER NOT NULL, EF DECIMAL(5), GH DECIMAL(7,2)); DBMS - 236363, DDL+DML: SQL
טיפוסים ב- PostgreSQL • VARCHAR (n) – מחרוזת • BOOLEAN – ערך בוליאני • DATE – תאריך • INTEGER – מספר שלם • DECIMAL [(p,s)] – מספר עם דיוק עשרוני נתון • … DBMS - 236363, DDL+DML: SQL
CREATE TABLE – דוגמא • דוגמא: יצירת טבלה חדשה בשם CSBooks שמכילה את כל הספרים השייכים לפקולטה ‘CS’. CREATE TABLE CSBooks AS SELECT Book_Id, Book_Name FROM Books WHERE Faculty = 'CS'; DBMS - 236363, DDL+DML: SQL
דוגמה - המשך Books: DBMS - 236363, DDL+DML: SQL
דוגמה - תוצאה CSBooks: DBMS - 236363, DDL+DML: SQL
CSBooks: Id Name 1111 Database Systems 1112 Database Systems 1113 Database Systems 2222 Database And Knowledge CREATE TABLE – המשך • הסכמה של הטבלה החדשה: • סוגי העמודות נקבעים ע"י השאילתא שבהוראת ה-CREATE. • את שמות העמודות בטבלה החדשה אפשר לשנות: • דוגמא: CREATE TABLE CSBooks(Id, Name) AS SELECT Book_Id, Book_Name FROM Books WHERE Faculty = 'CS'; DBMS - 236363, DDL+DML: SQL
מחיקת טבלה – DROP TABLE • דוגמה: מחיקת הטבלה CSBooks. DROP TABLE CSBooks; DBMS - 236363, DDL+DML: SQL
מבטים – VIEWS • מוטיבציה: להתייחס לפלט של שאילתה כאל טבלה נפרדת (בלי ליצור אותה פיזית). • בפועל: השאילתה שמגדירה את המבט נשמרת במסד הנתונים ומופעלת כל פעם מחדש. • אינטואיציה: המבט מהווה מעין "חלון" דרכו אפשר להסתכל על חלק ממסד הנתונים. • מטרה: הסתרת מידע, פישוט שאילתות... DBMS - 236363, DDL+DML: SQL
מבטים - המשך • יצירת מבט: 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
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
מבטים – המשך (שאילתות) • על מבט ניתן לבצע שאילתות ממש כאילו היה טבלה. • דוגמה: שליפת כל הספרים של מדעי מחשב שמשך ההשאלה שלהם הוא 7 ימים. SELECT Book_Name FROM CSBooks WHERE Max_Time = 7; DBMS - 236363, DDL+DML: SQL
מבטים – המשך (מחיקה) • מחיקת מבט: DROP VIEW • דוגמה: DROP VIEW CSBooks; • לטבלה שעליה מבוסס המבט לא קורה כלום. DBMS - 236363, DDL+DML: SQL