Bài giảng SQL Server Chương 3: Lê Thị Minh Nguyện (Chi tiết)

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

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

@ngayxh DATETIME

hàng DECLARE SET @ngayxh = „25-3-2014‟

Ví dụ 2:

int

DECLARE @TongSLDat SELECT @TongSLDat = SUM(SLDAT) FROM

CTDH

Ví dụ 3:

DECLARE @MinSLDat int, @MaxSLDat int SELECT @MinSLDat = MIN(SLDAT), @MaxSLDat = MAX(SLDAT), CTDH FROM

10/03/2015

1.Biến cục bộ

PRINT @Tên_biến | Biểu_thức_chuỗi

Xem giá trị hiện hành của biến Cú pháp: Ví dụ: DECLARE @MinSLDat int, @MaxSLDat int SELECT @MinSLDat = MIN(SLDAT), @MaxSLDat = MAX(SLDAT),

„Số lượng đặt thấp nhất là:‟

„Số lượng đặt cao nhất là:‟

5

FROM CTHDON PRINT PRINT @MinSLDat PRINT PRINT @MaxSLDat

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ô

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ô đó.

INSERT INTO NHACC („C01‟, „Nguyen Van A‟, ‟87 Ly Tu Trong‟,„0903.123456‟)

7

Ví dụ: SELECT * FROM NHACC ORDER BY TenNhaCC SELECT * FROM VATTU ORDER BY Tenvtu DESC GO

(thiếu từ khoá VALUES) thì các 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ô đó.

TenNhaCC (mặc dù vi phạm ràng buộc toàn vẹn trong INSERT (giả sử trùng khoá chính ở cột MaNCC) nhưng các lệnh SELECT bên trong lô này vẫn được thực hiện bình thường. INSERT INTO NHACC

Ví dụ: SELECT * FROM NHACC ORDER BY VALUES („C01‟, „Nguyen Van A‟, ‟87 Ly Tu Trong‟,„0903.123456‟) SELECT * FROM VATTU ORDER BY GO

Tenvtu DESC

10/03/2015

1.Biến cục bộ

Ví dụ:

DONDH

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.

DECLARE @NgayDH datetime SELECT @NgayDH = MAX(NGAYDH) FROM GO PRINT “ngay dat hang gan nhat: ” + convert(char(12), @ngaydh)

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

10/03/2015

2.Biến hệ thống

Tên biến

Dùng để trả về

kiểu trả 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

SANPHAM PHATRAM = PHANTRAM + 5 MAVTU like “TV%”

NHACC )

Ví dụ: UPDATE SET WHERE SELECT @@ROWCOUNT (Trả về tổng số mẩu tin có MAVTU bắt đầu bằng chữ 12

“TV” trong bảng VATTU)

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 <điều kiện>

• 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



 <điều kiện>

14

• 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 • Phép toán:  ,  ,  ,  ,  , , LIKE và BETWEEN

10/03/2015

Ví dụ

Lấy tất cả các cột của quan hệ kết quả

Tên bí danh

SELECT * FROM NHANVIEN WHERE PHG=5

SELECT MANV, HONV, TENLOT + TENNV as HOTEN FROM NHANVIEN WHERE PHG=5 AND PHAI=„Nam‟

Mở rộng

15

SELECT MANV, LUONG*1.1 AS „LUONG10%‟ FROM NHANVIEN WHERE PHG=5 AND PHAI=„Nam‟

Ví dụ

Loại bỏ các dòng trùng nhau

LUONG

SELECT DISTINCT LUONG FROM NHANVIEN WHERE PHG=5 AND PHAI=„Nam‟ SELECT LUONG FROM NHANVIEN WHERE PHG=5 AND PHAI=„Nam‟

16

30000 25000 38000

30000 25000 25000 38000

- Tốn chi phí - Người dùng muốn thấy

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

18

SELECT MANV, TENNV FROM NHANVIEN WHERE PHG NOT IN (4,5)

10/03/2015

Ví dụ

LIKE

Ký tự bất kỳ

Chuỗi bất kỳ

SELECT MANV, TENNV FROM NHANVIEN WHERE DCHI LIKE „Nguyen _ _ _ _‟

SELECT MANV, TENNV FROM NHANVIEN WHERE DCHI LIKE „Nguyen %‟

NOT LIKE

19

SELECT MANV, TENNV 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/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 <điều kiện> ORDER BY

21

 ASC: tăng (mặc định)  DESC: giảm

Ví dụ

MA_NVIEN

SODA

999887777 999887777 987987987 987987987 987654321 987654321 987654321

10 30 10 30 10 20 30

SELECT MA_NVIEN, SODA FROM PHANCONG ORDER BY MA_NVIEN DESC, SODA

22

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

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

 Ví dụ: để biết được danh sách các đơn đặt hàng gần đây

nhất.

qủa trả về của nó luôn đảm bảo chỉ là một giá trị đơn.

DONDH

DONDH SELECT MAX(NGAYDH) FROM Kết quả trả về: 2015-01-25 00:00:00 SELECT * FROM WHERE NGAYDH = “2015-01-25”

25

FROM DONDH)

 Kết hợp 2 câu truy vấn trên SELECT * FROM WHERE NGAYDH = (SELECT MAX(NGAYDH)

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

 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 DONDH FROM Convert(char(7), NgayDH, 21) = “2015-07” WHERE

 Kết quả trả về

SELECT

TenNCC, DienThoai

FROM

NHACC

truy vấn con dạng này

26

WHERE

MaNCC IN(“C01”, “C03”)

MaNCC C03 C01

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

SELECT TenNCC, DienThoai

FROM NHACC

WHERE MaNCC IN(SELECT MaNCC

27

FROM DONDH

WHERE Covert(char(7), NgayDH, 21) = “2015-01”)

3.2.Truy vấn lồng

Có 2 loại truy vấn lồng

• 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 phân cấp

• 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

 Lồng tương quan

28

10/03/2015

3.2.Truy vấn lồng

Lồng phân cấp

SELECT FROM WHERE

TenNCC, DienThoai NHACC 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

10/03/2015

Ví dụ

 Ví dụ 2: Danh sách các nhà cung cấp nào mà công ty

SELECT

TenNhaCC, DienThoai

FROM

NHACC

WHERE

MaNCC NOT IN (SELECT Distinct MaNCC

FROM DONDH)

 Hoặc

chưa bao giờ đặt hàng:

Lưu ý:

IN tương đương =ANY

SELECT

TenNhaCC, DienThoai

NOT IN tương đượng <>ALL

FROM

NHACC

31

WHERE

MaNCC <> ALL (SELECT Distinct MaNCC

FROM

DONDH)

Ví dụ

 Tìm những nhân viên không có thân nhân nào

Lồng phân cấp

SELECT MA_NVIEN FROM THANNHAN )

SELECT * FROM NHANVIEN WHERE MANV NOT IN (

Lồng tƣơng quan

SELECT * FROM NHANVIEN WHERE NOT EXISTS (

SELECT * FROM THANNHAN WHERE MANV=MA_NVIEN)

32

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

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

35 WHERE PHG=MAPHG AND TENPHG=‘Nghiên cứu’

3.4.Gom nhóm

Cú pháp

SELECT

FROM

WHERE <điều kiện>

Sau khi gom nhóm

GROUP BY

 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

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 PHANCONG FROM GROUP BY MA_NVIEN

MA_NVIEN ,HONV, TENNV, COUNT(*) AS SL_DA, SUM(THOIGIAN) AS TONG_TG PHANCONG, NHANVIEN MA_NVIEN=MANV

38

SELECT FROM WHERE GROUP BY MA_NVIEN, HONV, TENNV

10/03/2015

3.5.Điều kiện trên nhóm

Cú pháp

Cho biết những nhân viên tham gia từ 2 đề án

SELECT FROM WHERE <điều kiện> GROUP BY HAVING <điều kiện trên nhóm>

trở lên

PHANCONG

39

SELECT MA_NVIEN, COUNT(*) as SLDA FROM 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

40

SELECT TENPHG, AVG(LUONG) AS LUONG_TB FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG GROUP BY TENPHG HAVING AVG(LUONG) > 20000

40

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))

41

SELECT PHG, AVG(LUONG) AS LUONG_TB FROM NHANVIEN GROUP BY PHG HAVING AVG(LUONG) >= ALL (

SELECT AVG(LUONG) FROM NHANVIEN GROUP BY PHG)

Kết luận

SELECT FROM [WHERE <điều kiện>] [GROUP BY ] [HAVING <điều kiện trên nhóm>] [ORDER BY ]

42

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

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

46

bắt buộc phải có giá trị

10/03/2015

3.6.1.Thêm (insert)

Cú pháp (thêm nhiều dòng)

INSERT INTO ()

47 Ví dụ

INSERT INTO THONGKE_PB(TENPHG, SL_NV, LUONG_TC)

TENPHG VARCHAR(20), SL_NV INT, LUONG_TC INT CREATE TABLE THONGKE_PB ( )

SELECT TENPHG, COUNT(MANV), SUM(LUONG) FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG GROUP BY TENPHG

48

10/03/2015

3.6.2.Xóa (delete)

Dùng để xóa các dòng của bảng

Cú pháp:

Ví dụ:

DELETE FROM [WHERE <điều kiện>]

DELETE FROM NHANVIEN WHERE MANV=„345345345‟

49

DELETE FROM NHANVIEN

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

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

• Không cho xóa • Xóa luôn những dòng có giá trị đang tham chiếu đến

– CASCADE

 Lệnh DELETE có thể gây ra vi phạm RB tham chiếu

51

• Đặt NULL cho những giá trị tham chiếu

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 <điều kiện>]

52

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

cả các dòng trong bảng sẽ bị cập nhật

 Nếu không chỉ định điều kiện ở mệnh đề WHERE, tất

• Không cho sửa • Sửa luôn những dòng có giá trị đang tham chiếu đến

– CASCADE

 Lệnh UPDATE có thể gây ra vi phạm RB tham chiếu

54

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, FROM

TyLe = CAST(PHANTRAM AS VARCHAR(3)) + ‘%’ VATTU

56

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

DONDH

FROM

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

101

Mm/dd/yy

102

yy.mm.dd

103

Dd/mm/yy

104

dd.mm.yy

105

dd-mm-yy

106

Dd mon yy

107

Mon dd, yy

108

Hh:mm:ss

109

Mon dd yyyy hh:mm:ss

110

mm-dd-yy

111

Yy/mm/dd

112

Yymmdd

113

Dd mon yyyy hh:mm:ss

114

Hh:mm:ss:mmm

21 hoặc 121

Yyyy-mm-dd hh:mi:ss.mmm

58

20 hoặc 120

Yyyy-mm-dd hh:mi:ss

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.

STR(Số_thực, Số_ký_tự[, Số_lẻ])

Cú pháp: 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

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

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ụ: DATEDIFF(DD,NGAYDH,NGAYGH) 64 SELECT MADH, SONGAY = FROM DONDH

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ỳ

 Ví dụ:

DATEPART(Don_vi, ngày)

SELECT SODH, THANG = DATEPART(MM, NGAYDH)

FROM DONDH

66

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:

WHEN THEN WHEN THEN … [ELSE ]

CASE END

68

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

“Tivi” “Tủ lạnh” “Loa thùng”

SELECT MAVTU, TenVTU, Loai = CASE LEFT(MAVTU,2) FROM

When “DD” THEN “Đầu DVD” When “VD” THEN “Đầu VCD” When “TV” THEN When “TL” THEN When “LO” THEN ELSE “chưa phân loại” End, DVTinh VATTU

70

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 <= 2 thì không giảm giá, Nếu số lượng hàng từ 3 đến 10 thì giảm 10%, Nếu số lượng hàng > 10 thì giảm 20%.

CTPXUAT

WHEN SLXUAT <=2 THEN DGXuat WHEN SLXUAT BETWEEN 3 AND 10 THEN DGXuat * 0.9 ELSE DGXuat*0.8

END

71

UPDATE SET DGXuat = CASE FROM WHERE AND

CTPXUAT, PXUAT CTPXUAT.SoPX = PXUAT.SoPX Convert(char(7), NgayXuat, 21) = “2007-02”

5.2. Cấu trúc IF

Cú pháp

Biểu_thức_luận_lý Câu_lệnh1|khối_lệnh1

IF ELSE

Câu_lệnh2|khối_lệnh2

72

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

Print “Danh sách các hàng hoá bán ra với số lượng lớn hơn 4” SELECT FROM

CTPXUAT.MAVT, TENVT, SLXUAT CTPXUAT, VATTU CTPXUAT.MaVT = VATTU.MaVT SLXUAT>4

73

BEGIN WHERE AND END ELSE Print “chưa bán hàng nào với số lượng lớn hơn 4”

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

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.

INT

@Songuyen @Songuyen = 100

Print “Số nguyên: ” + convert(char(3), @songuyen) SET @Songuyen = @Songuyen +1

DECLARE SET WHILE (@Songuyen < 110) BEGIN END

75 6.Cấu trúc lặp

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

WHILE Biểu_thức_luận_lý BEGIN END Các_lệnh_khác

76

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

@Songuyen = 100

Break Print „So nguyen: ‟ + Convert(char(3), @songuyen) IF @Songuyen = 105 SET @Songuyen = @Songuyen +1 DECLARE @Songuyen int SET WHILE (@Songuyen < 110) BEGIN 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:

@Songuyen int @Songuyen = 99

CONTINUE

SET @Songuyen = @Songuyen + 1 IF @Songuyen = 105 Print ‘Số nguyên: ’ + Convert(char(3), @songuyen)

DECLARE SET WHILE (@Songuyen < 110) BEGIN END

78

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

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

 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.

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.

84 Khi khai báo cursor với kiểu dữ liệu tĩnh (STATIC) thì

dữ liệu trong cursor xem như chỉ đọc.

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.

mà chúng ta cần đọc dữ liệu.

 SELECT: dùng để chỉ đến các cột bên trong bảng

 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.

SELECT * FROM MATHANG

Cur_NhaCC CURSOR

DECLARE Cur_MAT_HANG CURSOR DYNAMIC FOR 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.

86

FROM NHACC

DECLARE FORWARD_ONLY STATIC READ_ONLY FOR SELECT *

10/03/2015

7.2.2. Mở Cursor

Tên_cursor

Cú pháp: OPEN 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

[INTO danh_sách_biến] Tên_cursor

 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.

88

 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à 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.

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

90

bộ nhớ

10/03/2015

7.3.Ví dụ

cung

SQL Server

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

cur_MatHang CURSOR

MAT_HANG

SELECT * FROM WHERE MaMH like „TV%‟ ORDER BY MaMH

cur_MatHang

-- Đọc tiếp dòng kế FETCH NEXT FROM cur_MatHang

92

cur_MatHang

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 DYNAMIC FOR  -- Mở cursor OPEN  -- Đọc dữ liệu FETCH NEXT FROM cur_MatHang WHILE @@FETCH_STATUS = 0 BEGIN END  -- Đóng cursor CLOSE DEALLOCATE cur_MatHang

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.

SOPN PNHAP

93

 -- 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 FROM  -- Mở cursor OPEN cur_Pnhap

7.3.Ví dụ

Dịch chuyển con trỏ cur_Pnhap vào @SoPN

cur_Pnhap INTO @Sopn

@@FETCH_STATUS = 0

 -- Đọc dữ liệu và cập nhật giá trị FETCH NEXT FROM WHILE BEGIN

SELECT

MaPN = @SoPN

@Tongtt = SUM(SLNhap*dongia) CTPNHAP

PNHAP Thanhtien = @TongTT Current OF cur_Pnhap// sopn=@SOPN

-- dịch con trỏ đến dòng kế tiếp FETCH NEXT FROM cur_Pnhap INTO @Sopn

94

FROM WHERE Print „dang cap nhat phieu nhap: ‟ + @SoPN UPDATE SET Where END  -- Đóng cursor CLOSE cur_Pnhap DEALLOCATE cur_Pnhap

FORWARD_ONLY

cur_Pnhap CURSOR

FOR

SELECT SOPN FROM

10/03/2015

Hoặc

PNHAP cur_Pnhap

0 = 0

cur_Pnhap INTO @Sopn

BREAK

CTPNHAP

IF @@FETCH_STATUS<>0 SELECT @Tongtg = SUM(SLNhap*dongia) FROM WHERE MaPN = @SoPN Print „dang cap nhat phieu nhap: ‟ + @SoPN UPDATE PNHAP SET Where

Thanhtien = @TongTT Current OF cur_Pnhap

FETCH NEXT FROM END

95 7.3.Ví dụ DECLARE @Sopn char(4), @TongTT Money DECLARE OPEN WHILE BEGIN CLOSE cur_Pnhap DEALLOCATE cur_Pnhap

7.3.Ví dụ

Câu lệnh SELECT

Tên_cursor

FETCH NEXT FROM [INTO danh_sách_biến]

Break

96 DECLARE Tên_cursor CURSOR {kiểu đọc | cập nhật dữ liệu} FOR --2. Mở cursor OPEN --3. Đọc dữ liệu và cập nhật giá trị WHILE 0=0 Begin IF @@FETCH_STATUS <> 0 --cập nhật dữ liệu trong cursor End --4. Đóng cursor CLOSE Tên_cursor DEALLOCATE Tên_cursor

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.