Data Manipulation Language – DML

פקודות DML אחראיות על שינוי בבסיס הנתונים. Insert, Delete, Update.

Insert

הוספת שורה חדשה.

INSERT INTO שם טבלה
VALUES (ערכים מופרדים בפסיקים על פי הסדר שלהם בטבלה)

OR

INSERT INTO שם טבלה (שמות העמודות שאליהן יכנסו הנתונים)
VALUES (ערכים מופרדים בפסיקים)

דוגמא 1

INSERT INTO [Order Details] 
VALUES (10248, 12, 23.2, 4, 0.15)

דוגמא 2

INSERT INTO [Order Details] 
    (Discount, Quantity, UnitPrice,ProductID,OrderID) 
VALUES (0.15, 4, 23.2, 12, 10248 )

הוספת רשומות מטבלה אחרת

אפשר להוסיף רשומות מטבלה אחרת.

INSERT INTO שם טבלה
Select query

דוגמא: הכנסת כל ההזמנות שבוצעו בשנת 1996 לטבלה חדשה בשם orders_96.

INSERT INTO orders_96 (order_id, customerid)
SELECT orderid,customerid
FROM orders WHERE OrderDate LIKE '%1996%'

הכנסת ערך NULL או ברירת מחדל

כאשר ערך לא ידוע או לא קיים עדיין אפשר להכניס ערך NULL במידה והגדרת הטבלה מאפשרת את זה או ערך ברירת מחדל.

INSERT [Order Details] 
VALUES (10248, 12, DEFAULT,DEFAULT, NULL )

פרמטר @@IDENTITY (T-SQL)

המערכת יכולה לתת מיספור אוטומטי לעמודות.

ערך המספור האוטומטי נשמר במשתנה גלובלי בשם: @@IDENTITY המאותחל כל חיבור ל-DB.

דוגמא, נוסיף מוביל חדש בשם Kangaroo לטבלת Shippers. העמודה ShipperID היא מסוג IDENTITY ולכן המערכת ולא אנחנו נותנים לה את הערך.

INSERT INTO Shippers 
VALUES (‘Kangaroo’, NULL)

-- קבלת הרשומה החדשה
SELECT * 
FROM Shippers 
WHERE ShipperID = @@IDENTITY

Update

עדכון רשומה. ניתן לעדכן מספר עמודות בו זמנית. אם לא מוסיפים תנאי where כל העמודות יתעדכנו.

UPDATE שם טבלה
SET שם עמודה = ערך חדש [,שם עמודה = ערך חדש ]
[WHERE תנאי]

UPDATE [Order Details] 
SET UnitPrice = UnitPrice * 2

אפשר להשתמש בתת שאילתא לעדכון הנתונים.

בדוגמא יעודכנו מוצרים שמחירם קטן מ-12. מחיר כל יחידה יהיה המחיר המקסימלי שנמצא בטבלת המוצרים והכמות בהזמנה תתעדכן לממוצע כמות המוצרים.

UPDATE [Order Details] 
SET UnitPrice = (SELECT MAX(UnitPrice) 
                 FROM Products), 
    Quantity = (SELECT AVG(Quantity) 
                FROM [Order Details]) 
WHERE UnitPrice < 12

Delete

מחיקת רשומה.

DELETE [FROM] שם טבלה
WHERE תנאי

מחיקת כל הרשומות בטבלה

DELETE FROM [Order Details]

מחיקת רשומות עם תנאי

DELETE FROM [Order Details] 
WHERE Quantity < 5

מחיקה על ידי תת שאילתא

DELETE FROM [Order Details] 
WHERE OrderID IN (SELECT OrderID 
                  FROM Orders 
                  WHERE DATEDIFF(YEAR,OrderDate,GETDATE()) > 2) 
       AND UnitPrice > 30

Truncate

Truncate היא פעולה שמחזיזרה את הטבלה למצב שלפני הזנת הנתונים לתוכה. היא מהירה יותר מ-delete ופועלת על כל הטבלה.

Truncate table authors

טרנזקציות ופקודות DML

טרנזקציה היא יחידה לוגית שמבצעת פקודת DML אחת או יותר ונסגרת בהצלחה או בכישלון.

ניתן לפתוח טרנזקציה בצורה מפורשת על ידי BEGIN TRANSACTION ולסגור טרנזקציה בכשלון על ידי ROLLBACK או בהצלחה על ידי COMMIT.

הטרנזקציה חשובה כי היא מאפשרת לנו התייחסות להצלחה או כישלון של פעולות מול ה-DB.

UPDATE -> נפתחה ונסגרה טרנזקציה

BEGIN TRAN -> נפתחה טרנזקציה
INSERT 
DELETE 
INSERT 
SELECT 
INSERT 
ROLLBACK > נסגרה טרנזקציה בכשלון

BEGIN TRAN > נפתחה טרנזקציה
UPDATE 
INSERT 
COMMIT -> נסגרה טרנזקציה בהצלחה

דוגמא לחשיבות של טרנזקציה.

נניח שיש לנו סוכנות פיננסית עם רשימה של לקוחות. בכל רבעון הסוכנות צריכה להוציא דוחות כספיים ללקוחות שלה. כדי לעשות את זה נדרשים הצעדים הבאים:

  1. מעבר על כל לקוח וחישוב הפעולות שהוא ביצע ברבעון.
  2. סיכום רבעוני של כל הפעולות.
  3. סיכום רווחים רבעוניים. וכן הלאה.

יש הרבה פעולות של קריאה מידע ושמירה של מידע אחר.

יכולות להיות תקלות שונות כמו פעולות שלא נכנסו לחישוב, סיכומים שלא נשמרים כמו שצריך ועוד. אנחנו רוצים להיות מודעים ולעקוב אחרי התקלות האלה.

דוגמא לשימוש בטרנזקציה.

נקח DB פשוט בשם School. אחת הטבלאות תהיה Course ויש לה שתי עמודות: CourseId, CourseName.

CREATE TABLE Course(
  CourseId   SMALLINT    NOT NULL;
  CourseName VARCHAR(40) NOT NULL
);

טרנזקציה – נקח את הקורס עם ה-ID הכי גבוה, נוסיף לזה 1 נוסיף קורס חדש לטבלת הקורסים עם ה-ID החדש. הפעולה מתבצעת בקריאה ל-COMMIT.

BEGIN TRANSACTION CourseAdd;

DECLARE @CrsId SMALLINT;

SELECT @CrsId = MAX(CourseId) + 1
FROM Course;

INSERT Course VALUES (@CrsId, 'Biology 101');

COMMIT TRANSACTION;

ביטול טרנזקציה – אם יש משהו שנכשל בתהליך אפשר לבטל את הפעולות שנעשו עם ROLLBACK.

START TRANSACTION;

INSERT Course VALUES (1, 'Biology 101');
INSERT Course VALUES (2, 'Computer Science 101');

ROLLBACK;

שילוב של פקודות COMMIT ו-ROLLBACK. במקרה שבדוגמא אנחנו מכניסים קורס חדש. אם יש כבר בטבלה קורס בשם כזה, נבטל את הפעולה.

BEGIN TRANSACTION InsertCourse;

DECLARE @CrsId SMALLINT;

SELECT @CrsId = MAX(CourseId) + 1
FROM Course;

INSERT Course VALUES (@CrsId, 'Biology 101');

IF (SELECT COUNT(CourseName)
   FROM Course
   WHERE CourseName = 'Biology 101') > 1
   ROLLBACK;
END IF;

COMMIT TRANSACTION;

אפשר ליצור בטקנזקציה מקומות שמירה, שאפשר יהיה לבטל עד אליהן במקרה הצורך ולא לבטל את כל הפעולה. הפקודה SAVEPOINT.

מה שקורה בדוגמא זה שהכנסת הקורס תתבטל, אבל הפקודה הראשונה תתבצע.

אפשר גם לבטל נקודת שמירה עם פקודה RELEASE SAVEPOINT SAVEPOINT_NAME.

BEGIN TRANSACTION InsertCourse;

DELETE FROM Course WHERE CourseId = 2;

SAVEPOINT SP1;

DECLARE @CrsId SMALLINT;

SELECT @CrsId = MAX(CourseId) + 1
FROM Course;

INSERT Course VALUES (@CrsId, 'Biology 101');

IF (SELECT COUNT(CourseName)
   FROM Course
   WHERE CourseName = 'Biology 101') > 1
   ROLLBACK TO SP1;
END IF;

COMMIT TRANSACTION;

תרגול DML – שאלות

יש ליצור טבלה על ידי העתקת הקוד הבא:

CREATE TABLE my_employees (
    id INt PRIMARY KEY ,
    name VARCHAR (50),
    title VARCHAR(50),
    deptid INT,
    salary MONEY DEFAULT 3500)
  • במידה והתקבלה הודעה כי הטבלה כבר קיימת יש לבצע DROP לטבלה: DROP TABLE my_employees ואז ליצור אותה מחדש.

שלוף את תיאור הטבלה

תרגול DML – תשובות

-- Table description
Sp_help my_employees

-- ex2
CREATE TABLE my_employees (
    id INt PRIMARY KEY,
    name VARCHAR (50),
    title VARCHAR(50),
    deptid INT,
    salary MONEY DEFAULT 3500)

-- ex3 - Table information
exec sp_help my_employees

-- ex5
insert into my_employees
values (1, 'Aviv Cohen', 'Clerk', 10, 4000)

-- ex6 
insert into my_employees
    (ID, [NAME], TITLE, DEPTID, SALARY)
values (2, 'Miriam levi', 'Sales Manager', 20, 3750)

-- ex7-ex10
BEGIN TRANSACTION AddEmployes;

insert into my_employees
values (3, 'AlON Romano', 'OperatiON Manager', 30, NULL)

insert into my_employees (ID, [NAME], DEPTID)
values (4, 'Baruch Nave', 30)

insert into my_employees
values (5, 'Danny SalomON', 'Sales Representative', 20, 7000)

COMMIT TRANSACTION;

select * from my_employees

-- ex11
update my_employees
set SALARY = 4500
where id = 2

-- ex12
update my_employees
set [name] = 'Keren Danino', DEPTID = 20
where id = 4

-- ex 14-ex16
BEGIN TRANSACTION AddEmployes;

update my_employees
set DEPTID = 10
where DEPTID = 30

delete from my_employees
where [name] = 'AlON Romano'

COMMIT TRANSACTION;

-- ex17
BEGIN TRANSACTION CopyEmployes;

insert into my_employees
select employeeID, FirstName, Title, NULL, 2500
from Employees where EmployeeID > 5

COMMIT TRANSACTION;

select * from Employees

ניווט במאמר

מאמרים אחרונים

Weekly Tutorial