Bài giảng Cập nhật dữ liệu

1Chương 5: CẬP NHẬT DỮ LIỆU Giáo viên: Tạ Thúc Nhu Khoa CNTT trường ĐH Lạc Hồng HỆ QUẢN TRỊ CSDL SQL SERVER SQL Server2 Nội dung 1. Thêm mẫu tin mới 2. Sửa nội dung mẫu tin 3. Xóa mẫu tin 4. Truy xuất dữ liệu cập nhật SQL Server3 I- THÊM MẪU TIN MỚI: 1. Thêm một mẫu tin mới 2. Thêm mẫu tin lấy từ các bảng khác 3. Thêm dữ liệu từ file vào cột Varbinary(Max) SQL Server4 1- Thêm một mẫu tin mới: Cú pháp: INSERT [()] VALUES () 2SQL Server5 Chú ý: Database Engine tự động cung cấp giá trị cho các cột • Có thuộc tính IDENTITY • Có khai báo Default. • Có kiểu Timestamp. • Có cho phép Null. • Hoặc là cột tính toán SQL Server6 2- Thêm mẫu tin lấy từ các bảng khác : Cú pháp: INSERT [()] Ví dụ: Lưu hóa đơn tháng 8/2005 vào HDLUU SQL Server7 Ví dụ: Tính tồn đầu tháng của mỗi mặt hàng: Dựa trên dữ liệu nhập xuất tồn tháng 8/2005 của các mặt hàng, hãy thêm các mẫu tin chứa tồn đầu kỳ của tháng 9/2005 cho bảng TONKHO SQL Server8 3.Thêm dữ liệu từ file vào cột Varbinary(Max): Hàm: OPENROWSET( BULK 'data_file' , SINGLE_BLOB) • Trả về bảng dữ liệu chỉ có một cột và một dòng chứa nội dung file dữ liệu. • Do đó, có thể sử dụng hàm này trong mệnh đề FROM Ví dụ: Thêm loại hàng mới và ghi nội dung file hình 'C:\KeoMut.jpg' vào cột Hinh kiểu Varbinary(Max). 3SQL Server9 Thêm mẫu tin mới có hình Ví dụ: Thêm loại hàng mới và ghi nội dung file hình 'C:\KeoMut.jpg' vào cột Hinh kiểu Varbinary(Max). INSERT LoaiHang (MaLH, TenLH, MoTa, Hinh) SELECT 3, N‘Kẹo mứt’, N'Desserts' , * FROM OPENROWSET( BULK 'C:\Keomut.jpg', SINGLE_BLOB) AS A SQL Server10 II- SỬA NỘI DUNG MẪU TIN 1. Thay đổi nội dung cột dữ liệu 2. Thay đổi nội dung cột kiểu varbinary(Max) 3. Thay đổi một phần dữ liệu với hàm .WRITE SQL Server11 1. Thay đổi nội dung cột dữ liệu Cú pháp : UPDATE SET = {|DEFAULT|NULL}[ ,...n ] [ FROM ] [ WHERE ] • Nếu không có mệnh đề WHERE thì tất cả mẫu tin đều bị thay đổi. Ví dụ: • có thể là phát biểu SELECT trả về một giá trị cập nhật cho từng mẫu tin. Ví dụ: • Mệnh đề FROM được sử dụng khi điều kiện chọn lựa mẫu tin hoặc dữ liệu cập nhật có liên quan đến các bảng khác. SQL Server12 Chọn mẫu tin cập nhật với mênh đề WHERE Ví dụ: 1. Tăng số tồn của các mặt hàng mã số loại hàng bằng 1 thêm 100 và tăng đơn giá của các mặt hàng đó thêm 10% 2. Tăng đơn giá của các mặt hàng có đơn giá thấp nhất lên 10%. 4SQL Server13 Biểu thức cập nhật là phát biểu SELECT Ví dụ: 1. Cập nhật cột TienHD của hóa đơn số 10248 2. Cập nhật TienHD của các hóa đơn bán trong ngày hiện hành. SQL Server14 Biểu thức cập nhật là phát biểu SELECT Ví dụ: Cập nhật số lượng nhập trong tháng 9/2005 của từng mặt hàng, dựa trên các phiếu giao hàng trong tháng 9/2005 SQL Server15 Dùng mệnh đề FROM Ví dụ: • Cập nhật số tồn của các mặt hàng đã ghi trên hóa đơn có số hóa đơn là 12345 SQL Server16 2. Thay đổi nội dung cột kiểu varbinary(Max) Cú pháp: SELECT * FROM OPENROWSET( BULK 'file_name', SINGLE_BLOB) AS Ví dụ: Thay thế hình của loại hàng có MaLH=1 với hình lưu trong file "C:\ThucUong.Bmp". 5SQL Server17 3. Thay đổi một phần dữ liệu với hàm .WRITE Cú pháp: UPDATE table_name SET .WRITE ( , @Offset , @Length ) [ FROM ] [ WHERE ] • column_name có kiểu varchar(max), nvarchar(max) haowcj varbinary(max) • Thay thế một phần dữ liệu bắt đầu tại @Offset (tính từ 0) với chiều dài @Length bằng giá trị của . SQL Server18 Ví dụ: Thay thế từ ‘coffees’ thành ‘Cà-fê trong chuỗi mô tả của loại hàng có MaLH = 1. SQL Server19 Một số yêu cầu đặc biệt: • Để xóa dữ liệu từ vị trí @Offset về cuối: đặt là NULL UPDATE LoaiHang SET MoTa .WRITE(NULL, 12, 8) WHERE MaLH = 1 ; • Để chèn giá trị biểu thức vào cuối: đặt @Offset là NULL UPDATE LoaiHang SET MoTa .WRITE (N' …', NULL, 0) WHERE MaLH = 1 ; • Để thay thế dữ liệu từ vị trí @Offset về cuối: Đặt @Length > kích thước dữ liệu hiện có. UPDATE LoaiHang SET MoTa .WRITE (N’…’ , 12, len(MoTa)) WHERE MaLH = 1 ; SQL Server20 III- XÓA MẪU TIN: 1. Dùng phát biểu Delete 2. Dùng phát biểu Truncate 6SQL Server21 1. Dùng phát biểu DELETE Cú pháp: DELETE [ FROM ] [WHERE ] • Nếu không có mệnh đề WHERE thì tất cả mẫu tin đều bị xóa. Ví dụ: Xóa tất cả các dòng trong CTHD DELETE CTHD Ví dụ: Xóa trong bảng CTHD, dòng chi tiết có số hóa đơn bằng 1 và mã mặt hàng bằng 5 DELETE CTHD WHERE SoHD = 1 And MaMH = 5 SQL Server22 Dùng mệnh đề FROM • Mệnh đề FROM được sử dụng khi điều kiện chọn lựa mẫu tin trên bảng bị xóa có liên quan đến các bảng khác. Ví dụ: 1. Xóa CTHD của các hóa đơn lập năm 2008 2. Xóa MatHang đơn giá thấp nhất. SQL Server23 3. Dùng phát biểu TRUNCATE Cú pháp: TRUNCATE TABLE SQL Server24 IV- TRUY XUẤT THÔNG TIN CẬP NHẬT Mục đích: • Kiểm tra xác nhận từ người dùng • Lưu trữ lại các dữ liệu cũ bị thay đổi • … 7SQL Server25 1. Table INSERTED và DELETED: SQL Server26 1- Bảng tạm: Inserted và Deleted Khi thực hiện các phát biểu cập nhật, SQL tự động tạo hai bảng tạm có cùng cấu trúc với bảng được cập nhật: • Bảng INSERTED: chứa các mẫu tin mới khi thực hiện lệnh Insert hoặc mẫu tin chứa dữ liệu đã sửa đổi khi thực hiện lệnh Update. • Bảng DELETED : chứa các mẫu tin bị xóa khi thực hiện lệnh Delete hoặc mẫu tin chứa dữ liệu cũ khi thực hiện lệnh Update. Các table này sẽ bị xóa khi kết thúc thực hiện phát biểu cập nhật. SQL Server27 a) Khi Insert mẫu tin mới INSERTED 100 SL DGBanMaLHSoHD 10249 209 CTHD 100 30 10 23 12 56 42 10 30 SL 20910249 DGBanMaLHSoHD 10249 10249 10248 10248 10248 10246 10246 10246 38 82 96 41 104 52 52 41 DELETED SL DGBanMaLHSoHD Thêm SQL Server28 b) Khi Delete mẫu tin INSERTED SL DGBanMaLHSoHD 20100910249 CTHD 30 10 23 12 56 42 10 30 SL DGBanMaLHSoHD 10249 10249 10248 10248 10248 10246 10246 10246 38 82 96 41 104 52 52 41 DELETED 100 SL DGBanMaLHSoHD 10249 209 8SQL Server29 c) Khi Update mẫu tin INSERTED 10 SL DGBanMaLHSoHD 10249 209 CTHD 10 30 10 23 12 56 42 10 30 SL 20910249 DGBanMaLHSoHD 10249 10249 10248 10248 10248 10246 10246 10246 38 82 96 41 104 52 52 41 DELETED 100 SL DGBanMaLHSoHD 10249 209 SQL Server30 2. Mệnh đề OUTPUT : • Trả về thông tin lưu trong bảng INSERTED và DELETED • Lưu thông tin đó vào một Table để sử dụng cho mục đích khác. Cú pháp: OUTPUT { column_name | expression } [ [AS] ] [ ,...n ] [ INTO output_table [ ( column_list ) ] ] • column_name: Tên cột trên bảng INSERTED hoặc DELETED • Có thể dùng dấu (*) để chọn hết các cột • expression: thêm cột biểu thức. SQL Server31 2.1. Sử dụng OUTPUT trong phát biểu INSERT: INSERT table_name [ ( column_list ) ] OUTPUT [ INTO output_table [ ( table_column_list ) ] ] { VALUES ( expression_list ) | } Ví dụ: Thêm một mẫu tin mới cho bảng CTHD và sử dụng OUTPUT trả về 3 cột trong bảng INSERTED: SL, DGBan và thành tiền = SL * DGBan. INSERT CTHD OUTPUT INSERTED.SL, INSERTED.DGBan, INSERTED.SL* INSERTED.DGBan AS ThanhTien VALUES( 10249, 9, 100, 20 ) Ví dụ: Trả về tất cả các cột của bảng INSERTED. INSERT CTHD OUTPUT INSERTED.* VALUES( 10249, 9, 100, 20 ) SQL Server32 2.2 Sử dụng OUTPUT trong phát biểu UPDATE: UPDATE table_name SET column_name = { expression | DEFAULT | NULL } [,...n ] OUTPUT [ INTO output_table [ ( table_column_list ) ] ] [ FROM ] [ WHERE search_conditions ] Trong đó: • : danh sách cột trên bảng INSERTED hoặc DELETED hoặc table_source trong mệnh đề FROM 9SQL Server33 Ví dụ: Cập nhật số tồn trong table MatHang của các mặt hàng đã ghi trên hóa đơn có số hóa đơn là 12345. Yêu cầu trả về bảng chứa SoHD, MaMH, SoTon trong bảng INSERTED và SoTon trong bảng DELETED. UPDATE MatHang SET SoTon = SoTon – SL OUTPUT CTHD.SoHD, INSERTED.MaMH, DELETED.SoTon AS STCu, INSERTED.SoTon AS STMoi FROM MatHang mh INNER JOIN CTHD ON CTHD.MaMH = mh.MaMH WHERE SoHD = 12345 SQL Server34 2.3 Sử dụng OUTPUT trong phát biểu DELETE: Cú pháp: DELETE table_name OUTPUT [ INTO output_table [ ( table_column_list ) ] ] [ FROM tables_list ] [ WHERE search_conditions ] • Ví dụ: Xóa MatHang có đơn giá thấp và xuất lại bảng DELETED. DELETE MatHang OUTPUT DELETED.* WHERE DonGia = (SELECT Min(DonGia) FROM MatHang) SQL Server35 Ví dụ: • Xóa trong bảng CTHD, dòng chi tiết có số hóa đơn bằng 1 và mã mặt hàng bằng 5. • Lưu mẫu tin bị xóa vào bảng CTHDLuu CREATE CTHDLuu (SoHD Int, MaMH Int, SL Float, DGBan Money) Go DELETE CTHD OUTPUT DELETED.* INTO CTHDLuu WHERE SoHD = 1 And MaMH = 5