›

Giáo trình Lập trình Cơ sở dữ liệu - Chương 3: Lập trình với Cơ sở dữ liệu - Lê Thị Minh Nguyện

pdf 50 trang huongle 3320
Download
Bạn đang xem 20 trang mẫu của tài liệu "Giáo trình Lập trình Cơ sở dữ liệu - Chương 3: Lập trình với Cơ sở dữ liệu - Lê Thị Minh Nguyện", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pdfgiao_trinh_lap_trinh_co_so_du_lieu_chuong_3_lap_trinh_voi_co.pdf

Nội dung text: Giáo trình Lập trình Cơ sở dữ liệu - Chương 3: Lập trình với Cơ sở dữ liệu - Lê Thị Minh Nguyện

  1. 10/03/2015 TRƢỜNG CAO ĐẲNG CÔNG NGHỆ THÔNG TIN THÀNH PHỐ HỒ CHÍ MINH CHƢƠNG 3. LẬP TRÌNH VỚI CƠ SỞ DỮ LIỆU GV: Lê Thị Minh Nguyện Email: leminhnguyen@itc.edu.vn NỘI DUNG  Biến cục bộ  Biến hệ thống  Các câu lệnh truy vấn dữ liệu  Các hàm thường dùng  Cấu trúc điều khiển  Cấu trúc lặp  Biến kiểu dữ liệu cursor 2 1
  2. 10/03/2015 1.Biến cục bộ Biến cục bộ: dùng để lưu trữ các giá trị tạm thời trong quá trình tính toán các xử lý bởi sau khi thoát khỏi chương trình hoặc tắt máy thì giá trị các biến này không còn trong bộ nhớ nữa.  Cú pháp: DECLARE @Tên_biến kiểu_dữ_liệu[, ]  Ví dụ: DECLARE @ten_ncc varchar(50), @ngayxh DATETIME  Gán giá trị cho biến: . Dùng SET hoặc SELECT cùng với phép gán (= ) 3 1.Biến cục bộ Ví dụ 1: để gán giá trị ngày 25/3/2002 vào biến ngày xuất hàng DECLARE @ngayxh DATETIME SET @ngayxh = „25-3-2014‟ Ví dụ 2: DECLARE @TongSLDat int SELECT @TongSLDat = SUM(SLDAT) FROM CTDH Ví dụ 3: DECLARE @MinSLDat int, @MaxSLDat int SELECT @MinSLDat = MIN(SLDAT), @MaxSLDat = MAX(SLDAT), FROM CTDH 2
  3. 10/03/2015 1.Biến cục bộ Xem giá trị hiện hành của biến Cú pháp: PRINT @Tên_biến | Biểu_thức_chuỗi Ví dụ: DECLARE @MinSLDat int, @MaxSLDat int SELECT @MinSLDat = MIN(SLDAT), @MaxSLDat = MAX(SLDAT), FROM CTHDON PRINT „Số lượng đặt thấp nhất là:‟ PRINT @MinSLDat PRINT „Số lượng đặt cao nhất là:‟ PRINT @MaxSLDat 5 1.Biến cục bộ Phạm vi hoạt động của biến . Trong Transaction-SQL phạm vi hoạt động của biến chỉ nằm trong một thủ tục nội tại (stored procedure) hoặc một lô (batch) chứa các câu lệnh mà biến đã được khai báo bên trong đó. . Lô được xem như một nhóm tập hợp của một hoặc nhiều câu lệnh Transaction-SQL sẽ được biên dịch đồng thời cùng lúc tại SQL Server. 6 . Một từ khoá GO chỉ định kết thúc 1 lô 3
  4. 10/03/2015 1.Biến cục bộ Do các câu lệnh trong một lô được biên dịch tại SQL Server vì thế khi có ít nhất 1 lệnh bên trong lô có lỗi về cú pháp lúc biên dịch thì hệ thống sẽ không có lệnh nào được thực thi bên trong lô đó. Ví dụ: SELECT * FROM NHACC ORDER BY TenNhaCC INSERT INTO NHACC („C01‟, „Nguyen Van A‟, ‟87 Ly Tu Trong‟,„0903.123456‟) SELECT * FROM VATTU ORDER BY Tenvtu DESC (thiếu từ khoá VALUES) thì các 7 GO lệnh SELECT bên trong lô này không được thực hiện. 1.Biến cục bộ Đối với các lỗi khi thực hiện (run-time) bên trong 1 lô nếu trường hợp các lỗi vi phạm ràng buộc toàn vẹn dữ liệu thì hệ thống SQL Server chỉ ngưng lại câu lệnh gây lỗi và thực hiện tiếp các lệnh bên trong lô đó. Ví dụ: (mặc dù vi phạm ràng buộc toàn vẹn SELECT * FROM NHACC trong INSERT (giả sử trùng khoá chính ở cột MaNCC) nhưng các lệnh ORDER BY TenNhaCC SELECT bên trong lô này vẫn được thực hiện bình thường. INSERT INTO NHACC VALUES („C01‟, „Nguyen Van A‟, ‟87 Ly Tu Trong‟,„0903.123456‟) SELECT * FROM VATTU ORDER BY Tenvtu DESC GO 4
  5. 10/03/2015 1.Biến cục bộ Ví dụ: DECLARE @NgayDH datetime SELECT @NgayDH = MAX(NGAYDH) FROM DONDH GO PRINT “ngay dat hang gan nhat: ” + convert(char(12), @ngaydh) GO HT sẽ báo lỗi vì có thêm từ khoá GO ở giữa 2 lệnh SELECT và PRINT. Bởi vì khi đó các lệnh này được chia làm 2 lô và lô thứ hai sẽ không hiểu biến @ngaydh đã được khai báo trong lô thứ 1. 2.Biến hệ thống Các biến hệ thống trong SQL Server luôn bắt đầu bằng 2 chữ @@. Giá trị mà chúng ta đang lưu trữ do hệ thống SQL cung cấp. Người lập trình không can thiệp trực tiếp để gán giá trị vào các biến hệ thống. 10 5
  6. 10/03/2015 2.Biến hệ thống Tên biến kiểu trả Dùng để trả về về connections số nguyên Tổng số các kết nối vào SQL Server từ khi nó được khởi động Error số nguyên số mã lỗi của câu lệnh thực hiện gần nhất. Khi một lệnh thực hiện thành công thì biến này có giá trị là 0 Language chuỗi Tên ngôn ngữ mà hệ thống SQL đag sử dụng. Mặc định là US_English RowCount số nguyên Tổng số mẩu tin được tác động vào câu lệnh truy vấn gần nhất ServerName chuỗi Tên của máy tính cục bộ được cài đặt trong SQL Server ServiceName chuỗi Tên dịch vụ kèm theo bên dưới SQL Server Fetch_Status số nguyên Trạng thái của việc đọc dữ liệu trong bảng theo cơ chế dòng mẩu tin (cursor). Khi dữ liệu đọc mẩu tin thành công thì biến này có giá trị là 0 Version chuỗi Phiên bản, ngày của phẩm SQL Server và loại CPU 2.Biến hệ thống Ví dụ: SELECT * FROM NHACC SELECT @@ROWCOUNT (Trả về tổng số mẩu tin đang hiện có trong bảng NHACC ) Ví dụ: UPDATE SANPHAM SET PHATRAM = PHANTRAM + 5 WHERE MAVTU like “TV%” SELECT @@ROWCOUNT (Trả về tổng số mẩu tin có MAVTU bắt đầu bằng chữ “TV” trong bảng VATTU) 12 6
  7. 10/03/2015 3.Các câu lệnh truy vấn dữ liệu Truy vấn cơ bản Truy vấn lồng Hàm kết hợp Gom nhóm Điều kiện trên nhóm Cập nhật dữ liệu 13 3.1.Truy vấn cơ bản Gồm 3 mệnh đề SELECT FROM WHERE . • Tên các cột cần được hiển thị trong kết quả truy vấn . • Tên các bảng liên quan đến câu truy vấn . • Biểu thức boolean xác định dòng nào sẽ được rút trích • Nối các biểu thức: AND, OR, và NOT 14 • Phép toán: , , , , , , LIKE và BETWEEN 7
  8. 10/03/2015 Ví dụ Lấy tất cả các cột của quan hệ kết quả SELECT * FROM NHANVIEN Tên bí danh WHERE PHG=5 SELECT MANV, HONV, TENLOT + TENNV as HOTEN FROM NHANVIEN WHERE PHG=5 AND PHAI=„Nam‟ Mở rộng SELECT MANV, LUONG*1.1 AS „LUONG10%‟ FROM NHANVIEN 15 WHERE PHG=5 AND PHAI=„Nam‟ Ví dụ Loại bỏ các dòng trùng nhau SELECT LUONG SELECT DISTINCT LUONG FROM NHANVIEN FROM NHANVIEN WHERE PHG=5 AND WHERE PHG=5 AND PHAI=„Nam‟ PHAI=„Nam‟ LUONG LUONG 30000 25000 - Tốn chi phí 30000 25000 - Người dùng 25000 16 38000 muốn thấy 38000 8
  9. 10/03/2015 Ví dụ BETWEEN SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG>=20000 AND LUONG<=30000 SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG BETWEEN 20000 AND 30000 NOT BETWEEN SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG NOT BETWEEN 20000 AND 30000 17 Ví dụ IN SELECT MANV, TENNV FROM NHANVIEN WHERE PHG IN (4,5) SELECT MANV, TENNV FROM NHANVIEN WHERE PHG = 4 OR PHG=5 NOT IN SELECT MANV, TENNV FROM NHANVIEN WHERE PHG NOT IN (4,5) 18 9
  10. 10/03/2015 Ví dụ LIKE Ký tự bất kỳ SELECT MANV, TENNV FROM NHANVIEN WHERE DCHI LIKE „Nguyen _ _ _ _‟ Chuỗi bất kỳ SELECT MANV, TENNV FROM NHANVIEN WHERE DCHI LIKE „Nguyen %‟ NOT LIKE SELECT MANV, TENNV 19 FROM NHANVIEN WHERE HONV NOT LIKE „Nguyen‟ Ví dụ NULL SELECT MANV, TENNV FROM NHANVIEN WHERE MA_NQL IS NULL SELECT MANV, TENNV FROM NHANVIEN WHERE MA_NQL IS NOT NULL 20 10
  11. 10/03/2015 Ví dụ Dùng để hiển thị kết quả câu truy vấn theo một thứ tự nào đó Cú pháp SELECT FROM WHERE ORDER BY . ASC: tăng (mặc định) . DESC: giảm 21 Ví dụ SELECT MA_NVIEN, SODA FROM PHANCONG ORDER BY MA_NVIEN DESC, SODA MA_NVIEN SODA 999887777 10 999887777 30 987987987 10 987987987 30 987654321 10 987654321 20 987654321 30 22 11
  12. 10/03/2015 3.2.Truy vấn lồng Chỉ một câu lệnh truy vấn lựa chọn (SELECT) được lồng vào các câu lệnh truy vấn khác nhằm thực hiện các truy vấn tính toán phức tạp. Khi sử dụng đến truy vấn lống chúng ta cần lưu tâm đến một vài yếu tố sau: . Cần mở và đóng ngoặc đơn cho câu lệnh truy vấn con. . Chúng ta chỉ được phép tham chiếu đến tên một cột hoặc một biểu thức sẽ trả về giá trị trong truy vấn con. . Kết quả truy vấn con có thể trả về là một giá trị đơn lẻ hoặc một danh sách các giá trị. 23 . Cấp độ lồng nhau của các truy vấn con bên trong SQL Server là không giới hạn. 3.2.Truy vấn lồng Mệnh đề WHERE của câu truy vấn cha . . So sánh tập hợp thường đi cùng với một số toán tử • = , =, • IN, NOT IN • ALL • ANY hoặc SOME . Kiểm tra sự tồn tại • EXISTS • NOT EXISTS 24 12
  13. 10/03/2015 3.2.Truy vấn lồng  Truy vấn con trả về một giá trị đơn: là truy vấn mà kết qủa trả về của nó luôn đảm bảo chỉ là một giá trị đơn.  Ví dụ: để biết được danh sách các đơn đặt hàng gần đây nhất. SELECT MAX(NGAYDH) FROM DONDH Kết quả trả về: 2015-01-25 00:00:00 SELECT * FROM DONDH WHERE NGAYDH = “2015-01-25”  Kết hợp 2 câu truy vấn trên SELECT * FROM DONDH WHERE NGAYDH = (SELECT MAX(NGAYDH) 25 FROM DONDH) 3.2.Truy vấn lồng  Truy vấn con trả về danh sách các giá trị: trả về của nó là danh sách các giá trị hay còn gọi là một tập hợp các phần tử. Toán tử IN sẽ được sử dụng để so sánh truy vấn con dạng này  Ví dụ 1: để biết nhà cung cấp nào mà công ty đã đặt hàng trong tháng 01/2015. SELECT MaNCC FROM DONDH WHERE Convert(char(7), NgayDH, 21) = “2015-07”  Kết quả trả về SELECT TenNCC, DienThoai MaNCC FROM NHACC C03 26 C01 WHERE MaNCC IN(“C01”, “C03”) 13
  14. 10/03/2015 3.2.Truy vấn lồng Đâu đảm bảo rằng trong tháng 01/2002 công ty chỉ đặt hàng cho 2 nhà cung cấp C01 và C03. Do đó để luôn luôn có được danh sách họ tên các nhà cung cấp mà công ty đã đặt trong tháng 01-2002 chúng ta thực hiện truy vấn con sau: SELECT TenNCC, DienThoai FROM NHACC WHERE MaNCC IN(SELECT MaNCC FROM DONDH 27 WHERE Covert(char(7), NgayDH, 21) = “2015-01”) 3.2.Truy vấn lồng Có 2 loại truy vấn lồng . Lồng phân cấp • Mệnh đề WHERE của truy vấn con không tham chiếu đến thuộc tính của các quan hệ trong mệnh đề FROM ở truy vấn cha • Khi thực hiện, câu truy vấn con sẽ được thực hiện trước . Lồng tương quan • Mệnh đề WHERE của truy vấn con tham chiếu ít nhất một thuộc tính của các quan hệ trong mệnh đề FROM ở truy vấn cha • Khi thực hiện, câu truy vấn con sẽ được thực hiện nhiều lần, mỗi lần tương ứng với một bộ của truy vấn cha 28 14
  15. 10/03/2015 3.2.Truy vấn lồng Lồng phân cấp SELECT TenNCC, DienThoai FROM NHACC WHERE MaNCC IN (SELECT MaNCC FROM DONDH WHERE Convert(char(7), NgayDH, 21) = “2015-01”) 29 3.2.Truy vấn lồng Lồng tương quan SELECT TenNCC, DienThoai FROM NHACC WHERE EXISTS (SELECT * FROM DONDH WHERE Covert(char(7), NgayDH, 21) = “2007-01” AND NHACC.MaNCC = DONDH.MaNCC) 30 15
  16. 10/03/2015 Ví dụ  Ví dụ 2: Danh sách các nhà cung cấp nào mà công ty chưa bao giờ đặt hàng: SELECT TenNhaCC, DienThoai FROM NHACC WHERE MaNCC NOT IN (SELECT Distinct MaNCC FROM DONDH)  Hoặc Lưu ý: SELECT TenNhaCC, DienThoai IN tương đương =ANY NOT IN tương đượng ALL (SELECT Distinct MaNCC 31 FROM DONDH) Ví dụ  Tìm những nhân viên không có thân nhân nào SELECT * FROM NHANVIEN Lồng phân cấp WHERE MANV NOT IN ( SELECT MA_NVIEN FROM THANNHAN ) SELECT * FROM NHANVIEN WHERE NOT EXISTS ( SELECT * Lồng tƣơng quan FROM THANNHAN WHERE MANV=MA_NVIEN) 32 16
  17. 10/03/2015 3.3.Hàm kết hợp COUNT . COUNT(*) đếm số dòng . COUNT( ) đếm số giá trị khác NULL của thuộc tính . COUNT(DISTINCT ) đếm số giá trị khác nhau và khác NULL của thuộc tính MIN MAX SUM AVG Các hàm kết hợp được đặt ở mệnh đề SELECT 33 Ví dụ 34 17
  18. 10/03/2015 Ví dụ Tìm tổng lương, lương cao nhất, lương thấp nhất và lương trung bình của các nhân viên SELECT SUM(LUONG), MAX(LUONG), MIN(LUONG), AVG(LUONG) FROM NHANVIEN Cho biết số lượng nhân viên của phòng „Nghiên cứu‟ SELECT COUNT(*) AS SL_NV FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG AND TENPHG=‘Nghiên cứu’ 35 3.4.Gom nhóm Cú pháp SELECT FROM WHERE GROUP BY Sau khi gom nhóm . Mỗi nhóm các bộ sẽ có cùng giá trị tại các thuộc tính gom nhóm 36 18
  19. 10/03/2015 Ví dụ Cho biết số lượng nhân viên của từng phòng ban SELECT PHG, COUNT(*) AS SL_NV FROM NHANVIEN GROUP BY PHG SELECT TENPHG, COUNT(*) AS SL_NV FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG 37 GROUP BY TENPHG Ví dụ Với mỗi nhân viên cho biết mã số, họ tên, số lượng đề án và tổng thời gian mà họ tham gia SELECT MA_NVIEN, COUNT(*) AS SL_DA, SUM(THOIGIAN) AS TONG_TG FROM PHANCONG GROUP BY MA_NVIEN SELECT MA_NVIEN ,HONV, TENNV, COUNT(*) AS SL_DA, SUM(THOIGIAN) AS TONG_TG FROM PHANCONG, NHANVIEN WHERE MA_NVIEN=MANV 38 GROUP BY MA_NVIEN, HONV, TENNV 19
  20. 10/03/2015 3.5.Điều kiện trên nhóm Cú pháp SELECT FROM WHERE GROUP BY HAVING Cho biết những nhân viên tham gia từ 2 đề án trở lên SELECT MA_NVIEN, COUNT(*) as SLDA FROM PHANCONG 39 GROUP BY MA_NVIEN HAVING COUNT(*) >= 2 Ví dụ Cho biết những phòng ban (TENPHG) có lương trung bình của các nhân viên lớn lơn 20000 SELECT PHG, AVG(LUONG) AS LUONG_TB FROM NHANVIEN GROUP BY PHG HAVING AVG(LUONG) > 20000 SELECT TENPHG, AVG(LUONG) AS LUONG_TB FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG GROUP BY TENPHG HAVING AVG(LUONG) > 20000 40 40 20
  21. 10/03/2015 Ví dụ Tìm những phòng ban có lương trung bình cao nhất SELECT PHG, AVG(LUONG) AS LUONG_TB FROM NHANVIEN GROUP BY PHG HAVING MAX(AVG(LUONG)) SELECT PHG, AVG(LUONG) AS LUONG_TB FROM NHANVIEN GROUP BY PHG HAVING AVG(LUONG) >= ALL ( SELECT AVG(LUONG) 41 FROM NHANVIEN GROUP BY PHG) Kết luận SELECT FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ] 42 21
  22. 10/03/2015 3.6.Cập nhật dữ liệu Thêm (insert) Xóa (delete) Sửa (update) 43 3.6.1.Thêm (insert) Dùng để thêm 1 hay nhiều dòng vào bảng Để thêm dữ liệu . Tên quan hệ . Danh sách các thuộc tính cần thêm dữ liệu . Danh sách các giá trị tương ứng Cú pháp (thêm 1 dòng) INSERT INTO ( ) VALUES ( ) 44 22
  23. 10/03/2015 Ví dụ INSERT INTO NHANVIEN(HONV, TENLOT, TENNV, MANV) VALUES („Le‟, „Van‟, „Tuyen‟, „635635635‟) INSERT INTO NHANVIEN(HONV, TENLOT, TENNV, MANV, DCHI) VALUES („Le‟, „Van‟, „Tuyen‟, „635635635‟, NULL) INSERT INTO NHANVIEN VALUES („Le‟, „Van‟, „Tuyen‟, „635635635‟, ‟12/30/1952‟, ‟98 HV‟, „Nam‟, „37000‟, 4) 45 3.6.1.Thêm (insert) Nhận xét . Thứ tự các giá trị phải trùng với thứ tự các cột . Có thể thêm giá trị NULL ở những thuộc tính không là khóa chính và NOT NULL . Câu lệnh INSERT sẽ gặp lỗi nếu vi phạm RBTV • Khóa chính • Tham chiếu • NOT NULL - các thuộc tính có ràng buộc NOT NULL bắt buộc phải có giá trị 46 23
  24. 10/03/2015 3.6.1.Thêm (insert) Cú pháp (thêm nhiều dòng) INSERT INTO ( ) 47 Ví dụ CREATE TABLE THONGKE_PB ( TENPHG VARCHAR(20), SL_NV INT, LUONG_TC INT ) INSERT INTO THONGKE_PB(TENPHG, SL_NV, LUONG_TC) SELECT TENPHG, COUNT(MANV), SUM(LUONG) FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG GROUP BY TENPHG 48 24
  25. 10/03/2015 3.6.2.Xóa (delete) Dùng để xóa các dòng của bảng Cú pháp: DELETE FROM [WHERE ] Ví dụ: DELETE FROM NHANVIEN WHERE MANV=„345345345‟ DELETE FROM NHANVIEN 49 Ví dụ Xóa đi những nhân viên ở phòng „Nghien cuu‟ DELETE FROM NHANVIEN WHERE PHG IN ( SELECT MAPHG FROM PHONGBAN WHERE TENPHG=„Nghien cuu‟) 50 25
  26. 10/03/2015 3.6.2.Xóa (delete) Nhận xét . Số lượng số dòng bị xóa phụ thuộc vào điều kiện ở mệnh đề WHERE . Nếu không chỉ định điều kiện ở mệnh đề WHERE, tất cả các dòng trong bảng sẽ bị xóa . Lệnh DELETE có thể gây ra vi phạm RB tham chiếu • Không cho xóa • Xóa luôn những dòng có giá trị đang tham chiếu đến – CASCADE • Đặt NULL cho những giá trị tham chiếu 51 3.6.3. Sửa (update) Dùng để thay đổi giá trị của thuộc tính cho các dòng của bảng Cú pháp: UPDATE SET = , = , [WHERE ] 52 26
  27. 10/03/2015 Ví dụ UPDATE NHANVIEN SET NGSINH=‟08/12/1965‟ WHERE MANV=„333445555‟ UPDATE NHANVIEN SET LUONG=LUONG*1.1 53 3.6.3. Sửa (update) Nhận xét . Những dòng thỏa điều kiện tại mệnh đề WHERE sẽ được cập nhật giá trị mới . Nếu không chỉ định điều kiện ở mệnh đề WHERE, tất cả các dòng trong bảng sẽ bị cập nhật . Lệnh UPDATE có thể gây ra vi phạm RB tham chiếu • Không cho sửa • Sửa luôn những dòng có giá trị đang tham chiếu đến – CASCADE 54 27
  28. 10/03/2015 4.Các làm thường dùng Các hàm chuyển đổi kiểu dữ liệu Các hàm về ngày 55 4.1.Các hàm chuyển đổi kiểu dữ liệu Hàm CAST: chuyển đổi một biểu thức nào đó sang một kiểu dữ liệu mong muốn. Cú pháp: CAST(Biểu_thức AS kiểu_dữ_liệu) Ví dụ: SELECT MaVTU, TenVT, TyLe = CAST(PHANTRAM AS VARCHAR(3)) + ‘%’ FROM VATTU 56 28
  29. 10/03/2015 4.1.Các hàm chuyển đổi kiểu dữ liệu Hàm CONVERT: chuyển đổi một biểu thức nào đó sang một kiểu dữ liệu bất kỳ mong muốn nhưng có thể theo một định dạng nào đó. Cú pháp: CONVERT (Kiểu_dữ_liệu, Biểu_thức[, định_dạng]) Ví dụ: SELECT SoHD, CONVERT(char(10),NgayHD, 103) AS NGAYHD FROM DONDH 57 Bảng chuyển mô tả viết tắt của đơn vị STT Định dạng năm (yyyy) Hiển thị dữ liệu 1 101 Mm/dd/yy 2 102 yy.mm.dd 3 103 Dd/mm/yy 4 104 dd.mm.yy 5 105 dd-mm-yy 6 106 Dd mon yy 7 107 Mon dd, yy 8 108 Hh:mm:ss 9 109 Mon dd yyyy hh:mm:ss 10 110 mm-dd-yy 11 111 Yy/mm/dd 12 112 Yymmdd 13 113 Dd mon yyyy hh:mm:ss 14 114 Hh:mm:ss:mmm 58 15 21 hoặc 121 Yyyy-mm-dd hh:mi:ss.mmm 16 20 hoặc 120 Yyyy-mm-dd hh:mi:ss 58 58 29
  30. 10/03/2015 4.1.Các hàm chuyển đổi kiểu dữ liệu Hàm STR: chuyển đổi kiểu dữ liệu số sang kiểu dữ liệu chuỗi. Phải đảm bảo đủ khoảng trắng để chứa các ký số khi chuyển sang kiểu dữ liệu chuỗi. Cú pháp: STR(Số_thực, Số_ký_tự[, Số_lẻ]) Ví dụ: SELECT TenVT, SLNhap = STR(SLNhap, 5) + „ ‟ + DVTinh FROM VATTU, CTPNHAP WHERE VATTU.MaVT = CTPNHAP.MaVT 59 4.1.Các hàm chuyển đổi kiểu dữ liệu  Hàm ASCII: ASCII(string) Hàm trả về mã ASCII của ký tự đầu tiên bên trái của chuỗi đối số  Hàm CHAR: CHAR(ascii_code) Hàm trả về ký tự có mã ASCII tương ứng với đối số  Hàm CHARINDEX: CHARINDEX(string1,string2[,start]) Hàm trả về vị trí đầu tiên tính từ vị trí start tại đó chuỗi string1 xuất hiện trong chuỗi string2.  Hàm LEFT: LEFT(string,number) Hàm trích ra number ký tự từ chuỗi string tính từ phía bên trái 60 30
  31. 10/03/2015 4.2.Các hàm trên dữ liệu kiểu chuỗi  Hàm LEN: LEN(string) Hàm trả về độ dài của chuỗi string  Hàm LOWER : LOWER(string) Hàm có chức năng chuyển chuỗi string thành chữ thường, kết quả được trả về cho hàm  Hàm UPPER: UPPER(string) Chuyển chuỗi string thành chữ hoa  Hàm LTRIM: LTRIM(string) Cắt bỏ các khoảng trắng thừa bên trái chuỗi string  Hàm NCHAR: NCHAR(code_number) Hàm trả về ký tự UNICODE có mã được chỉ định 61 4.2.Các hàm trên dữ liệu kiểu chuỗi  Hàm REPLACE: REPLACE(string1,string2,string3) Hàm trả về một chuỗi có được bằng cách thay thế các chuỗi string2 trong chuỗi string1 bởi chuỗi string3.  Hàm REVERSE: REVERSE(string) Hàm trả về chuỗi đảo ngược của chuỗi string.  Hàm RIGHT: RIGHT(string, number) Hàm trích ra number ký tự từ chuỗi string tính từ phía bên phải.  Hàm RTRIM: RTRIM(string) Cắt bỏ các khoảng trắng thừa bên phải của chuỗi string. 62 31
  32. 10/03/2015 4.2.Các hàm trên dữ liệu kiểu chuỗi  Hàm SPACE: SPACE(number) Hàm trả về một chuỗi với number khoảng trắng.  Hàm SUBSTRING: SUBSTRING(string, m, n) Trích ra từ n ký tự từ chuỗi string bắt đầu từ ký tự thứ m.  Hàm UNICODE : UNICODE(UnicodeString) Hàm trả về mã UNICODE của ký tự đầu tiên bên trái của chuỗi UnicodeString. 63 4.4.Các hàm về ngày  DATEDIFF: trả về 1 số nguyên khoảng cách của hai ngày theo một đơn vị thời gian bất kỳ DATEDIFF(don_vi, ngay1, ngay2) . Đơn vị • DW: trả về ngày trong tuần • DD: trả về ngày • MM: trả về tháng • QQ: trả về quý • YYYY: trả về năm  Ví dụ: SELECT MADH, SONGAY = DATEDIFF(DD,NGAYDH,NGAYGH) 64 FROM DONDH 32
  33. 10/03/2015 4.4.Các hàm về ngày  DATENAME: trả về một chuỗi thời gian đại diện của 1 ngày chỉ định theo một đơn vị thời gian bất kỳ DATENAME(Don_vi, ngay)  Ví dụ: SELECT MADH, THU = DATENAME(DW, NGAYDH) FROM DONDH 65 4.4.Các hàm về ngày  GETDATE: trả về ngày giờ hiện hành của hệ thống GETDATE()  DATEPART: trả về 1 số nguyên chỉ định thời gian đại diện của 1 ngày theo một đơn vị thời gian bất kỳ DATEPART(Don_vi, ngày)  Ví dụ: SELECT SODH, THANG = DATEPART(MM, NGAYDH) FROM DONDH 66 33
  34. 10/03/2015 5.Cấu trúc điều khiển Cấu trúc Case Cấu trúc IF 67 5.1. Cấu trúc Case  Cho phép kiểm tra điều kiện và xuất thông tin theo từng trường hợp  Cú pháp: CASE WHEN THEN WHEN THEN [ELSE ] END 68 34
  35. 10/03/2015 5.1. Cấu trúc Case Ví dụ 1: Cho biết họ tên các nhân viên đã đến tuổi về hưu (nam 60 tuổi, nữ 55 tuổi) SELECT HONV, TENNV FROM NHANVIEN WHERE YEAR(GETDATE()) – YEAR(NGSINH) >= ( CASE PHAI WHEN 'Nam' THEN 60 WHEN 'Nu' THEN 55 END ) 69 5.1. Cấu trúc Case Ví dụ 2: hiển thị danh sách các sản phẩm có trong bảng SANPHAM theo từng loại hàng SELECT MAVTU, TenVTU, Loai = CASE LEFT(MAVTU,2) When “DD” THEN “Đầu DVD” When “VD” THEN “Đầu VCD” When “TV” THEN “Tivi” When “TL” THEN “Tủ lạnh” When “LO” THEN “Loa thùng” ELSE “chưa phân loại” End, DVTinh FROM VATTU 70 35
  36. 10/03/2015 5.1. Cấu trúc Case Ví dụ 3: Giảm giá bán hàng trong tháng 2-2007 theo quy tắc sau: Nếu số lượng hàng 10 thì giảm 20%. UPDATE CTPXUAT SET DGXuat = CASE WHEN SLXUAT <=2 THEN DGXuat WHEN SLXUAT BETWEEN 3 AND 10 THEN DGXuat * 0.9 ELSE DGXuat*0.8 END FROM CTPXUAT, PXUAT WHERE CTPXUAT.SoPX = PXUAT.SoPX AND Convert(char(7), NgayXuat, 21) = “2007-02” 71 5.2. Cấu trúc IF Cú pháp IF Biểu_thức_luận_lý Câu_lệnh1|khối_lệnh1 ELSE Câu_lệnh2|khối_lệnh2 72 36
  37. 10/03/2015 5.2. Cấu trúc IF Ví dụ: Cho biết vật tư nào đã bán ra với số lượng nhiều hơn 4 không? Nếu có thì hiển thị danh sách đó ra, ngược lại thì thông báo chưa bán vật tư nào nhiều hơn 4 IF (SELECT COUNT(*) FROM CTPXUAT WHERE SLXUAT>4)>0 BEGIN Print “Danh sách các hàng hoá bán ra với số lượng lớn hơn 4” SELECT CTPXUAT.MAVT, TENVT, SLXUAT FROM CTPXUAT, VATTU WHERE CTPXUAT.MaVT = VATTU.MaVT AND SLXUAT>4 END ELSE Print “chưa bán hàng nào với số lượng lớn hơn 4” 73 6.Cấu trúc lặp Cú pháp: WHILE Biểu_thức_luận_lý BEGIN Các_lệnh_lặp END 74 37
  38. 10/03/2015 6.Cấu trúc lặp Ví dụ 1: Để in ra 10 số nguyên dương bắt đầu từ 100. DECLARE @Songuyen INT SET @Songuyen = 100 WHILE (@Songuyen < 110) BEGIN Print “Số nguyên: ” + convert(char(3), @songuyen) SET @Songuyen = @Songuyen +1 END 75 6.Cấu trúc lặp WHILE Biểu_thức_luận_lý BEGIN Các_lệnh_nhóm_lặp_1 [IF Biểu_thức_lặp_Tiếp CONTINUE ] [IF Biểu_thức_thoát BREAK ] Các_lệnh_nhóm_lặp_2 END Các_lệnh_khác 76 38
  39. 10/03/2015 6.Cấu trúc lặp  Từ khoá BREAK lồng vào cấu trúc WHILE để có thể kết thúc việc lặp của các lệnh bên trong vòng lặp DECLARE @Songuyen int SET @Songuyen = 100 WHILE (@Songuyen < 110) BEGIN Print „So nguyen: ‟ + Convert(char(3), @songuyen) IF @Songuyen = 105 Break SET @Songuyen = @Songuyen +1 END 77 6.Cấu trúc lặp Thực hiện giống ví dụ trước, nhưng muốn in sót số nguyên 105. Chúng ta sử dụng cấu trúc lặp WHILE như sau: DECLARE @Songuyen int SET @Songuyen = 99 WHILE (@Songuyen < 110) BEGIN SET @Songuyen = @Songuyen + 1 IF @Songuyen = 105 CONTINUE Print ‘Số nguyên: ’ + Convert(char(3), @songuyen) END 78 39
  40. 10/03/2015 7.Kiểu dữ liệu cursor Giới thiệu Các bước sử dụng kiểu dữ liệu cursor Ví dụ 79 7.1.Giới thiệu  CSDL quan hệ thường làm việc trên dữ liệu của nhiều dòng mẩu tin – còn gọi là các bộ mẩu tin. Lệnh SELECT kết quả luôn trả về nhiều mẩu tin hơn là một mẩu tin.  Tuy nhiên trong thực tế, có những trường hợp cần xử lý vấn đề trên một mẩu tin hoặc trên nhiều mẩu tin cùng thời gian với hình thức tính toán khác nhau. Để đáp ứng được yêu cầu này SQL Server tạo ra một kiểu dữ liệu đó chính là kiểu cursor. 80 40
  41. 10/03/2015 7.2.Các bước sử dụng kiểu cursor Biến kiểu cursor: định nghĩa biến kiểu cursor bằng lệnh DECLARE. Mở Cursor : sử dụng lệnh OPEN để mở ra cursor đã định nghĩa trước đó. Đọc và xử lý trên từng dòng dữ liệu bên trong cursor Đóng cursor: bằng lệnh CLOSE và 81 DEALLOCATE. 7.2.1. Biến kiểu cursor Cú pháp DECLARE Tên_cursor CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | DYNAMIC | KEYSET] [READ_ONLY | SCROLL_LOCK] FOR Câu_lệnh SELECT [FOR UPDATE [OF danh_sách_cột_n]]  Trong đó: . Tên cursor: tên của biến kiểu cursor . LOCAL | GLOBAL: phạm vi hoạt động của biến cursor. 82 . FORWARD_ONLY: đọc dữ liệu trong cursor theo chiều đi tới duyệt từ đầu mẫu tin đầu tiên đến mẫu tin cuối cùng. 41
  42. 10/03/2015 7.2.1. Biến kiểu cursor  STATIC: đọc dữ liệu bên trong cursor tĩnh. Khi đó nếu những người dùng khác có thay đổi bên dưới dữ liệu gốc thì các thay đổi đó sẽ không được cập nhật tự động trong dữ liệu của cursor. Bởi vì khi đó dữ liệu trong cursor chính là dữ liệu của bảng tạm đã được hệ thống sao chép và lưu trữ trong CSDL tempdb của hệ thống khi định nghĩa cursor.  DYNAMIC: dùng chỉ định dữ liệu trong cursor là động. Khi đó việc cập nhật dữ liệu trong bảng cơ sở bởi những người dùng khác sẽ được cập nhật tự động trong dữ liệu cursor có kiểu là DYNAMIC. 83 7.2.1. Biến kiểu cursor  KEYSET: hoạt động giống với kiểu DYNAMIC, các thay đổi dữ liệu trên các cột không là khoá chính trong bảng cơ sở bởi những người dùng khác sẽ được cập nhật trong dữ liệu cursor. Tuy nhiên đối với mẩu tin vừa thêm mới hoặc các mẩu tin đã bị huỷ bỏ bởi những người dùng khác sẽ không được hiển thị trong dữ liệu cursor có kiểu là KEYSET.  READ_ONLY: chỉ định dữ liệu trong cursor chỉ đọc nhằm hạn chế việc sửa đổi dữ liệu bên trong cursor. Khi khai báo cursor với kiểu dữ liệu tĩnh (STATIC) thì84 dữ liệu trong cursor xem như chỉ đọc. 42
  43. 10/03/2015 7.2.1.Biến kiểu cursor  SCROLL_LOCK: chỉ định hệ thống SQL Server tự động khoá các dòng mẩu tin cần phải thay đổi giá trị hoặc huỷ bỏ bên trong bảng nhằm bảo đảm các hành động cập nhật luôn thành công.  SELECT: dùng để chỉ đến các cột bên trong bảng mà chúng ta cần đọc dữ liệu.  Danh sách các cột cập nhật: chỉ định danh sách tên các cột sẽ được phép thay đổi giá trị trong cursor. 85 7.2.1. Biến kiểu cursor Ví dụ 1: để định nghĩa một biến cursor chứa toàn bộ các dòng dữ liệu bên trong bảng MAT_HANG, các dòng dữ liệu trong cursor cho phép được cập nhật. DECLARE Cur_MAT_HANG CURSOR DYNAMIC FOR SELECT * FROM MATHANG Ví dụ 2: Định nghĩa một biến cursor chứa toàn bộ các dòng dữ liệu bên trong bảng NHACC, các dữ liệu trong cursor chỉ được phép đọc và việc đọc dữ liệu trong cursor chỉ theo một chiều đi tới. DECLARE Cur_NhaCC CURSOR FORWARD_ONLY STATIC READ_ONLY 86 FOR SELECT * FROM NHACC 43
  44. 10/03/2015 7.2.2. Mở Cursor Cú pháp: OPEN Tên_cursor Trong đó: Tên cursor: tên của biến cursor đã được định nghĩa trước đó bằng lệnh DECLARE Ví dụ: Mở các cursor đã định nghĩa ở ví dụ 1 trên. Chúng ta sử dụng lệnh OPEN như sau: OPEN cur_MAT_HANG 87 7.2.3.Đọc và xử lý dữ liệu trong cursor FETCH [Next | Prior | First | Last |Absolute n | Relative n] FROM Tên_cursor [INTO danh_sách_biến]  Trong đó: . Next, Prior, First, Last: dùng để đọc dữ liệu kế tiếp, trước, đầu, sau cùng. . Absolute: dữ liệu chính xác thứ n trong cursor. N>0 chỉ định việc đọc dữ liệu tại dòng thứ n đếm từ dòng đầu tiên, n<0 dùng chỉ định việc đọc dữ liệu tại dòng thứ n được đếm ngược từ dòng cuối trở lên. . Relative: dùng chỉ định việc đọc dữ liệu tại một dòng tương đối so với dòng dữ liệu hiện hành. N là 88 một số nguyên có thể dương có thể âm để chỉ định theo chiều tới hoặc lui so với dòng dữ liệu hiện hành. 44
  45. 10/03/2015 7.2.3.Đọc và xử lý dữ liệu trong cursor  FETCH: đọc dữ liệu trong cursor được phép di chuyển tới lui, qua lại các dòng mẩu tin bên trong cursor tuỳ thích. 89 7.2.4.Đóng cursor Cú pháp: CLOSE Tên_cursor DEALLOCATE Tên_cursor Trong đó . CLOSE giải phóng các dòng dữ liệu tham chiếu bên trong cursor. . DEALLOCATE giải phóng thật sự biến cursor ra khỏi bộ nhớ 90 45
  46. 10/03/2015 7.3.Ví dụ SQL Server cung cấp một biến hệ thống @@FETCH_STATUS dùng để kiểm tra trình trạng đọc dữ liệu thành công hay thất bại. Giá trị trả về 0 khi việc đọc dữ liệu là thành công. Cho lược đồ quan hệ như sau: . MAT_HANG(MaMH, TenMH, DVT, MaNCC) . PNHAP(MaPN, NgayNhap, ThanhTien) . CTPNHAP(MaMH, MaPN, SLNhap, DonGia) 91 7.3.Ví dụ  Đọc dữ liệu cursor của bảng MAT_HANG chỉ đọc các vật tư là Tivi  Khai báo biến cursor DECLARE cur_MatHang CURSOR DYNAMIC FOR SELECT * FROM MAT_HANG WHERE MaMH like „TV%‟ ORDER BY MaMH  Mở cursor OPEN cur_MatHang  Đọc dữ liệu FETCH NEXT FROM cur_MatHang WHILE @@FETCH_STATUS = 0 BEGIN Đọc tiếp dòng kế FETCH NEXT FROM cur_MatHang END 92  Đóng cursor CLOSE cur_MatHang DEALLOCATE cur_MatHang 46
  47. 10/03/2015 7.3.Ví dụ Cập nhật dữ liệu cho cột ThanhTien trong bảng PNHAP bằng cách duyệt qua từng phiếu nhập, tính ra trị giá nhập của từng phiếu căn cứ vào số lượng nhập và đơn giá nhập của từng vật tư trong bảng CTPNHAP, sau cùng cập nhật vào cột ThanhTien.  Khai báo biến cursor, các biến cục bộ DECLARE @Sopn char(4), @TongTT Money DECLARE cur_Pnhap CURSOR FORWARD_ONLY FOR SELECT SOPN FROM PNHAP 93  Mở cursor OPEN cur_Pnhap 7.3.Ví dụ Dịch chuyển con trỏ  Đọc dữ liệu và cập nhật giá trị cur_Pnhap FETCH NEXT FROM cur_Pnhap INTO @Sopn vào @SoPN WHILE @@FETCH_STATUS = 0 BEGIN SELECT @Tongtt = SUM(SLNhap*dongia) FROM CTPNHAP WHERE MaPN = @SoPN Print „dang cap nhat phieu nhap: ‟ + @SoPN UPDATE PNHAP SET Thanhtien = @TongTT Where Current OF cur_Pnhap// sopn=@SOPN dịch con trỏ đến dòng kế tiếp FETCH NEXT FROM cur_Pnhap INTO @Sopn END  Đóng cursor 94 CLOSE cur_Pnhap DEALLOCATE cur_Pnhap 47
  48. 10/03/2015 7.3.Ví dụ DECLARE @Sopn char(4), @TongTT Money DECLARE cur_Pnhap CURSOR FORWARD_ONLY FOR SELECT SOPN FROM PNHAP OPEN cur_Pnhap Hoặc WHILE 0 = 0 BEGIN FETCH NEXT FROM cur_Pnhap INTO @Sopn IF @@FETCH_STATUS [INTO danh_sách_biến] IF @@FETCH_STATUS <> 0 Break cập nhật dữ liệu trong cursor End 4. Đóng cursor 96 CLOSE Tên_cursor DEALLOCATE Tên_cursor 48
  49. 10/03/2015 Kết luận Khi nào cần sử dụng cursor? Khi nào chúng ta cần sử dụng kiểu dữ liệu cursor trong Transaction-SQL để giải quyết các vấn đề: . SQL Server là một hệ quản trị CSDL quan hệ (Relational Database Management System) do đó chúng ta nên chọn giải pháp làm việc trên các bộ mẩu tin. . Khi cần giải quyết vấn đề cập nhật dữ liệu thì luôn ưu tiên chọn các hướng giải quyết trên bộ mẩu tin bởi vì khi đó làm cho các bộ xử lý được nhanh hơn. . Sau cùng là hướng giải quyết theo kiểu cursor là giải pháp sau cùng nhất để chọn lựa khi không còn giải pháp nào97 tốt hơn 98 49
  50. 10/03/2015 TRƢỜNG CAO ĐẲNG CÔNG NGHỆ THÔNG TIN THÀNH PHỐ HỒ CHÍ MINH 50