Hiểu đơn giản thì Trigger là một stored procedure không có tham số. Trigger thực thi một cách tự động khi một trong ba câu lệnh Insert, Update, Delete làm thay đổi dữ liệu trên bảng có chứa trigger.
Cú pháp của Trigger
CREATE TRIGGER tên_trigger ON tên_bảng
FOR {DELETE, INSERT, UPDATE}
AS
câu_lệnh_sql
Trigger dùng làm gì ?
Trigger thường được sử dụng để kiểm tra ràng buộc (check constraints) trên nhiều quan hệ (nhiều bảng/table) hoặc trên nhiều dòng (nhiều record) của bảng.
Ngoài ra việc sử dụng Trigger để chương trình có những hàm chạy ngầm nhằm phục vụ nhưng trường hợp hữu hạn và thường không sử dụng cho mục đích kinh doanh hoặc giao dịch. Đọc thêm tại đây
Bài toán đặt ra.
Bạn có 2 bảng kho hàng và đặt hàng liên kết với nhau bởi mã hàng.
Khi người dùng đặt hàng hãy tự động cập nhật số lượng tồn trong bảng kho hàng.
Giải pháp
Khi người dùng đặt hàng ta chỉ có 3 loại thao tác chính với CSDL là : Insert, Delete, Update
Vậy chỉ cần tạo 3 trigger tương ứng là ok
Người dùng đặt hàng: Số lượng còn trong kho = Số lượng còn – Số lượt đặt
Người dùng hủy không đặt hàng nữa: Số lượng còn trong kho = Số lượng còn + Số lượt đặt
Người dùng cập nhật Số lượng đặt => Số lượng còn tăng giảm tùy ý
Vấn đề
Ở 2 trường hợp insert và delete ta thực hiện bình thường. Nhưng trong trường hợp update Số lượng hàng tồn sẽ sảy ra trong 3 trường hợp sau.
Số lượng đặt ban đầu = 5 sau đó tăng lên 10 => số lượng trong kho sẽ giảm 10 tương ứng
Số lượng đặt lúc này = 10 sau đó giảm xuống 3 => số lượng trong kho sẽ tăng 7 tương ứng
Tận dụng việc trong sql câu lệnh update = Insert new row To Delete old row cõ nghĩa là khi thực hiện update CSDL trong sql sẽ chạy việc insert dữ liệu mới trước sau đó sẽ xóa đi bảng cũ.
Giải quyết vấn đề
Tận dụng việc sử dụng Trigger luôn tồn tại 2 bảng inserted và deleted ta sẽ rút ra 1 công thức cập nhật trung trong mọi trường hợp
Cập nhật một số thông tin khác mà không liên quan đến số lượng
Xóa đơn đặt hàng
Source code bài toàn
Trigger thêm
Trigger Xóa
Trigger Sửa
/* cập nhật hàng trong kho sau khi đặt hàng hoặc cập nhật */
CREATE TRIGGER trg_DatHang ON tbl_DatHang AFTER INSERT AS
BEGIN
UPDATE tbl_KhoHang
SET SoLuongTon = SoLuongTon - (
SELECT SoLuongDat
FROM inserted
WHERE MaHang = tbl_KhoHang.MaHang
)
FROM tbl_KhoHang
JOIN inserted ON tbl_KhoHang.MaHang = inserted.MaHang
END
GO
/* cập nhật hàng trong kho sau khi cập nhật đặt hàng */
CREATE TRIGGER trg_CapNhatDatHang on tbl_DatHang after update AS
BEGIN
UPDATE tbl_KhoHang SET SoLuongTon = SoLuongTon -
(SELECT SoLuongDat FROM inserted WHERE MaHang = tbl_KhoHang.MaHang) +
(SELECT SoLuongDat FROM deleted WHERE MaHang = tbl_KhoHang.MaHang)
FROM tbl_KhoHang
JOIN deleted ON tbl_KhoHang.MaHang = deleted.MaHang
end
GO
/* cập nhật hàng trong kho sau khi hủy đặt hàng */
create TRIGGER trg_HuyDatHang ON tbl_DatHang FOR DELETE AS
BEGIN
UPDATE tbl_KhoHang
SET SoLuongTon = SoLuongTon + (SELECT SoLuongDat FROM deleted WHERE MaHang = tbl_KhoHang.MaHang)
FROM tbl_KhoHang
JOIN deleted ON tbl_KhoHang.MaHang = deleted.MaHang
END
Kết luận
Việc mà bạn sử dụng Trigger là không bắt buộc và chúng ta thường tưởng rằng vì thế mà chả ai dùng nó là hoàn toàn sai . Nhưng Trigger theo như mình tìm hiểu qua thì vẫn có rất nhiều nơi sẽ sử dụng nó vào mục đích riêng của họ.
Hiểu và trình bày được các phương thức, thuộc tính của các đối tượng truy xuất cơ sở dữ liệu, cách thức truy xuất, duyệt nội dung cơ sở dữ liệu; các đối tượng hiển thị nội dung các bản ghi trong cơ sở dữ liệu; các cú pháp lệnh truy xuất cơ sở dữ liệu
Dùng để lưu trữ các giá trị tạm thời trong quá trình tính toán
Biến phải có kiểu dữ liệu
Biến muốn sử dụng trong một batch phải khai báo trước
Gán giá trị cho biến Sử dụng lệnh SET hoặc SELECT
SET @Biến = Giá_trị
SET @a = 5
Select @Biến = Tên_Cột From Tên_Bảng
Select @ConLai = TonCuoiKy From TonKho Where MaVTu = 'VT010' And NamThang = '200402'
Xem giá trӏ hiện hành của biến
Khi có kết hợp với chuỗi, phải đổi kiểu dữ liệu sang kiểu chuỗi bằng hàm CAST hay CONVERT
Print 'Giá trị của @A ' + cast(@A as char(4))
Phạm vi hoạt động của biến: Một biến chỉ có phạm vi hoạt động cục bộ
Trong một Batch
Trong một Stored Procedure hay Trigger
DECLARE @Ngaydhgn DATETIME
SELECT @Ngaydhgn=MAX(NGAYDH)
FROM DONDH
GO
PRINT 'Ngày đặt hàng gần nhất: ' + CONVERT(CHAR(12),@Ngaydhgn)
GO
Biến hệ thống
Ý nghĩa sử dụng
Cung cấp các thông tin hệ thống
Phiên bản SQL Server
Số dòng dữ liệu vừa được xử lý bởi câu lệnh
Mã lỗi
Số lượng kết nối
Tình trạng cursor
Không cần khai báo
Biến do SQL Server định sẵn
Tên bắt đầu bởi @@
Một vài biến hệ thống thường dùng
@@RowCount
Tổng số mẩu tin được tác động của câu lệnh truy vấn gần nhất.
@@Error
Số mã lỗi của câu lệnh thực hiện gần nhất
Khi một câu lệnh thực hiện thành công thì giá trị là 0.
@@Fetch_Status
Trạng thái của việc đọc dữ liệu trong bảng theo cơ chế từng mẩu tin (cursor).
Khi đọc dữ liệu của mẩu tin thành công thì giá trị là 0.
Các toán tử
Toán tử số học
SELECT 'Hello' + ' ' + 'The World!'
SELECT 'Ngày đặt hàng D007 là: ' + CAST(NGAYDH AS CHAR(11))
FROM DONDH
WHERE SODH='D007'
Toán tử so sánh
SELECT * FROM VATTU
WHERE
(DVTINH='Bộ' AND PHANTRAM>10)
OR
(DVTINH='Cái' AND PHANTRAM>20)
Cấu trúc điều khiển
Cấu trúc rẽ nhánh IF…ELSE
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 ]
IF (SELECT COUNT(*) FROM CTPXUAT WHERE SLXUAT>4) > 0
BEGIN
PRINT 'Danh sách các hàng hóa bán với số lượng > 4'
SELECT CTPX.MAVTU, TENVTU, SLXUAT
FROM CTPXUAT CTPX INNER JOIN VATTU VT
ON VT.MAVTU=CTPX.MAVTU
WHERE SLXUAT>4
END
ELSE
PRINT 'Chưa bán hàng hóa nào với số lượng >4'
Cú pháp If Exists
IF EXISTS (Câu_lệnh_SELECT)
Câu_lệnh1 | Khối_lệnh1
[ELSE
Câu_lệnh2 | Khối_lệnh2]
IF EXISTS (SELECT * FROM CTPXUAT WHERE SLXUAT>4)
BEGIN
PRINT 'Danh sách các hàng hóa bán với số lượng > 4'
SELECT CTPX.MAVTU, TENVTU, SLXUAT
FROM CTPXUAT CTPX INNER JOIN VATTU VT ON VT.MAVTU=CTPX.MAVTU
WHERE SLXUAT>4
END
ELSE
PRINT 'Chưa bán hàng hóa nào với số lượng >4'
Cấu trúc lặp WHILE
WHILE Biểu_thức_luận_lý
BEGIN
Các_lệnh_lặp
END
DECLARE @Songuyen INT
SET @Songuyen=100
WHILE (@Songuyen<110)
BEGIN
PRINT 'Số nguyên : ' + CONVERT(CHAR(3), @Songuyen)
SET @Songuyen = @Songuyen + 1
END
Khái niệm về cursor
Các lệnh của SQL Server làm việc trên một nhóm nhiều mẩu tin
Cursor là cấu trúc giúp làm việc từng mẩu tin tҥi một thời điểm
Khai báo cursor như một câu lệnh SELECT
Có thể di chuyển giữa các mẩu tin trong cursor để làm việc
Có thể dùng cursor để cập nhật dữ liệu (Update, Delete)
Các bước sử dụng kiểu dữ liệu cursor
Định nghĩa biến kiểu cursor bằng lệnh DECLARE
Có hai loại cursor: Local, Global
Cách di chuyển mẩu tin trong cursor: Forward only, scroll
Cách quản lý dữ liệu của cursor: static, dynamic, keyset
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
Sử dụng biến @@Fetch_status Các lệnh Fetch và cấu trúc while
DECLARE cur_Vattu CURSOR
DYNAMIC
FOR
SELECT * FROM VATTU
Cú pháp Open
DECLARE cur_Vattu CURSOR
DYNAMIC
FOR
SELECT * FROM VATTU
OPEN cur_Vattu
Cú pháp FETCH
Absolute n: Đọc dòng thứ n trong cursor
Relative n: Đọc dòng thứ n kể từ vị trí hiện hành
FETCH [NEXT | PRIOR | FIRST | LAST
| ABSOLUTE n | RELATIVE n]
FROM Tên_cursor
[INTO Danh_sách_biến]
Ví dụ hoàn chỉnh
--1. Khai báo biến cursor
DECLARE cur_Vattu CURSOR KEYSET
FOR SELECT * FROM VATTU
WHERE MAVTU LIKE 'TV%'
ORDER BY MAVTU
--2. Mở cursor
OPEN cur_Vattu
--3. Đọc dữ liệu
FETCH NEXT FROM cur_Vattu
WHILE @@FETCH_STATUS = 0
BEGIN
-- Xử lý dòng mới vừa đọc được
-- Thực hiện đọc tiếp các dòng kế
FETCH NEXT FROM cur_Vattu
END
--4. Đóng cursor
CLOSE cur_Vattu
DEALLOCATE cur_Vattu
Các hàm chuyển đổi kiểu dữ liệu
Một hàm của SQL Server có thể sử dụng ở bất cứ đâu thay cho một giá trị cụ thể
Đổi một số thành chuỗi
STR (Số_thực, Số_ký_tự [, Số_lẻ])
Đổi kiểu dữ liệu
CAST (Biểu_thức AS Kiểu_dữ_liệu)
Đổi kiểu dữ liệu và định dạng
CONVERT (Kiểu_dữ_liệu, Biểu_thức [, Định_dạng])
Một số định dạng chuỗi ngày thông dụng
Các hàm ngày giờ
Cộng ngày
DATEADD (Đơn_vị, Con_số, Ngày_chỉ_định)
So sánh hai biến ngày
DATEDIFF (Đơn_vị, Ngày1, Ngày2)
Lấy tên ngày, tháng, năm
DATENAME (Đơn_vị, Ngày)
Thời điểm hiện hành
GETDATE()
Lấy một thành phần ngày, giờ trong biến ngày
DATEPART (Đơn_vị, Ngày)
Lấy ngày, tháng, năm của biến ngày
DAY (Ngày)
MONTH (Ngày)
YEAR (Ngày)
Các hàm toán học
Lấy trị tuyệt đối
ABS (Biểu_thức_số)
Hằng số Pi
PI()
Luỹ thừa
POWER (Biểu_thức_số, Số_mũ)
Lấy số ngẫu nhiên
RAND ([Số_nguồn])
Làm tròn số
ROUND (Biểu_thức_số, Vtrí_làm_tròn)
Dấu của kết quả biểu thức : SIGN (Biểu_thức_số)
Lấy căn bậc 2 : SQRT (Biểu_thức_số)
Các hàm xử lý chuỗi
Hàm viết hoa, thường
UPPER (Chuỗi), LOWER (Chuỗi)
Hàm cắt chuỗi
LEFT (Chuỗi nguồn, Số_ktự), RIGHT (Chuỗi nguồn, Số_ktự)
SUBSTRING (Chuỗi nguồn,Vị_trí,Số_ktự)
Hàm cắt khoảng trắng, tҥo chuỗi khoảng trắng
LTRIM (Chuỗi), RTRIM (Chuỗi), SPACE (N)
Hàm tҥo chuỗi lặp
REPLICATE (Chuỗi_lặp, N)
Chiều dài chuỗi
LEN (Chuỗi)
Đảo chuỗi
REVERSE (Chuỗi)
Tìm và thay thế chuỗi
REPLACE (Chuỗi nguồn, Chuỗi_tìm, Chuỗi_thay_thế)
Đổi từ số thành ký tự và ngược lại
CHAR (Số) , ASCII(Ký_tự)
Xử lý lỗi TRY…CATCH
Ý nghĩa : Thực hiện các lệnh trong khối TRY, nếu gặp lỗi sẽ chuyển qua xử lý bằng các lệnh trong khối CATCH
Cú pháp :
BEGIN TRY
{ các câu lệnh }
END TRY
BEGIN CATCH
{ các câu lệnh}
END CATCH
Các điểm cần lưu ý:
TRY và CATCH phải cùng lô xử lý
Sau khối TRY phải là khối CATCH
Có thể lồng nhiều cấp
Ví dụ
BEGIN TRY
SELECT * FROM BangKhongTonTai;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
END CATCH
Một số hàm ERROR thường dùng
ERROR_NUMBER() : Trả về mã số của lỗi
ERROR_MESSAGE() Trả về chuỗi lỗi
ERROR_SEVERITY() returns the error severity.
ERROR_STATE() returns the error state number.
ERROR_LINE() : Trả về dòng gây ra lỗi
ERROR_PROCEDURE() Trả về tên thủ tục/ trigger gây ra lỗi
mã thông báo lỗi do người dùng định nghĩa trước bằng sp_addmessage và được lưu trong sys.messages. Giá trị phải lớn hơn 50000.
chuỗi thông báo lỗi bất kỳ.
muc_do:
Số có giá trị từ 0
25 thể hiện mức độ nghiêm trọng của lỗi.
trang_thai: Số từ 1-127 để xác định vị trí lỗi khi sử dụng cùng một tbao_loi tại nhiều điểm khác nhau
cac_tham_so : Hỗ trợ cho các tbao_loi cần tham số
Ví dụ sử dụng RAISERROR
IF @nPhanTram NOT BETWEEN 0 AND 100
BEGIN
SET @sErrMsg = N'Tỉ lệ phầm trăm phải nằm trong đoạn [0,100]'
RAISERROR(@sErrMsg, 16, 1)
RETURN
END
Sử dụng cấu trúc điều khiển
Cho biết đơn giá xuất trung bình của hàng hóa “Đầu DVD Hitachi 1 đĩa” trong bảng CTPXUAT hiện giờ là bao nhiêu? Nếu lớn hơn 3,800,000 thì in ra “không nên thay đổi giá bán”, ngược lại in ra “đã đến lúc tăng giá bán”
--Cách 1
IF (
SELECT avg(c.dgxuat)
FROM ctpxuat c INNER JOIN vattu v ON v.mavtu = c.mavtu
WHERE v.tenvtu=N'Đầu DVD Hitachi 1 đĩa'
GROUP BY c.mavtu,v.tenvtu
) > =3800000
BEGIN
print 'không nên thay dổi giá bán'
END
ELSE
BEGIN
print 'đã đến lúc tăng giá bán'
END
--Cách 2
SELECT c.mavtu,v.tenvtu,avg(c.dgxuat)AS 'DG trungbinh',(
CASE
WHEN AVG(c.dgxuat)> =3800000
THEN 'không nên thay dổi giá bán'
ELSE
'đã đến lúc tăng giá bán'
END
) AS 'Ghi Chu'
FROM ctpxuat c INNER JOIN vattu v ON v.mavtu = c.mavtu
WHERE v.tenvtu=N'Đầu DVD Hitachi 1 đĩa'
GROUP BY c.mavtu,v.tenvtu
GO
Sử dụng hàm DATENAME để tính xem có đơn đặt hàng nào đã được lập vào ngày chủ nhật không? Nếu có thì in ra danh sách các đơn đặt hàng đó, ngược lại thì in ra chuỗi “Ngày lập các đơn đặt hàng đều là hợp lệ”
--Cach 1
DECLARE @SoNgay INT
SELECT @SoNgay=COUNT(d.ngaydh)
FROM dondh d
WHERE DATENAME(dw,d.ngaydh)='sunday'
IF @SoNgay>0
BEGIN
SELECT d.sodh, d.ngaydh, d.manhacc
FROM dondh d
WHERE DATENAME(dw,d.ngaydh)='sunday'
END
ELSE
PRINT 'Ngày lập các đơn đặt hàng đều hợp lệ'
GO
--Cach 2
IF EXISTS (SELECT * FROM dondh d WHERE DATENAME(dw,d.ngaydh)='sunday')
SELECT * FROM dondh d WHERE DATENAME(dw,d.ngaydh)='sunday'
ELSE
PRINT N'Các hóa đơn đặt hàng có ngày hợp lệ'
GO
Hãy cho biết đã có bao nhiêu phiếu nhập hàng cho đơn đặt hàng D001, nếu có thì in ra “Có xx số phiếu nhập hàng cho đơn đặt hàng D001”, ngược lại thì in ra “Chưa có nhập hàng nào cho D001”
DECLARE @SoPhieuDH INT
DECLARE @TenDH VARCHAR(4)
SET @TenDH='D001'
SET @SoPhieuDH=(
SELECT COUNT(p.sopn)
FROM pnhap p
WHERE p.sodh=@TenDH
)
IF @SoPhieuDH>0
PRINT N'Có '+CAST(@SoPhieuDH AS VARCHAR(3))+ N' số phiếu nhập hàng cho dơn đặt hàng D001'
ELSE
PRINT 'Chưa có đơn dặt hàng nào cho D001'
GO
Hãy cho biết đơn đặt hàng D001 đã có nhập đủ hàng chưa, nếu có thì in ra “Đã nhập đủ hàng cho đơn đặt hàng D001”, ngược lại thì in ra “
Chưa nhập đủ hàng cho đơn đặt hàng D001” Hãy cho biết vật tư TL90 đã có đặt hàng trong tháng 2/2009 chưa, nếu có thì in ra “Đã có đặt hàng với tổng số lượng đặt là xxx”, ngược lại thì in ra “Chưa có đặt hàng”
DECLARE @TongDonDH INT
DECLARE @MaVattu VARCHAR(4)
DECLARE @Thang INT
DECLARE @Nam INT
SET @MaVattu='TL90'
SET @Thang=2
SET @Nam=2009
IF EXISTS(
SELECT *
FROM ctdondh c INNER JOIN dondh d ON d.sodh = c.sodh
WHERE c.mavtu=@MaVattu AND MONTH (d.ngaydh)=@Thang AND YEAR(d.ngaydh)=@Nam
)
BEGIN
SET @TongDonDH=(
SELECT SUM(c.sldat)
FROM ctdondh c INNER JOIN dondh d ON d.sodh = c.sodh
WHERE c.mavtu=@MaVattu AND MONTH (d.ngaydh)=@Thang AND YEAR(d.ngaydh)=@Nam
)
PRINT N'Đã có đặt hàng với tổng số lựong dặt là '+CAST(@TongDonDH AS CHAR(3))
END
ELSE
PRINT N'Chưa có đặt hàng'
GO
Liệt kê danh sách các đơn đặt hàng trong bảng DONDH có bổ sung thêm cột hiển thị thứ trong tuần (bằng tiếng Việt) của ngày đặt hàng
SELECT dh.*, 'Thứ' =
(
CASE DATENAME(dw,dh.ngaydh)
WHEN 'MONDAY'
THEN N'Thứ Hai'
WHEN 'TUESDAY'
THEN N'Thứ Ba'
WHEN 'WEDNESDAY'
THEN N'Thứ Tư'
WHEN 'THURSDAY'
THEN N'Thứ Năm'
WHEN 'FRIDAY'
THEN N'Thứ Sáu'
WHEN 'SATURDAY'
THEN N'Thứ Bảy'
ELSE
N'Chủ Nhật'
END
)
FROM dondh AS dh
Tính tổng các số nguyên từ 1 đến 100
DECLARE @Songuyen INT
DECLARE @Tong INT
SET @Tong = 0
SET @Songuyen = 0
while (@Songuyen <= 100)
BEGIN
SET @Tong = @Tong + @Songuyen
SET @Songuyen = @Songuyen +1
END
print @Tong
Tính tổng chẵn và tổng lẻ của các số nguyên từ 1 đến 100
DECLARE @Songuyen INT
DECLARE @TongChan INT
DECLARE @TongLe INT
SET @TongChan = 0
SET @TongLe = 0
SET @Songuyen = 0
while (@Songuyen <= 100)
BEGIN
IF(@Songuyen % 2 = 0)
BEGIN
SET @TongChan = @TongChan + @Songuyen
END
ELSE
BEGIN
SET @TongLe = @TongLe + @Songuyen
END
SET @Songuyen = @Songuyen +1
END
print @TongChan
print @TongLe
Duyệt cursor và xử lý Hiển thị danh sách các thông tin trong bảng DONDH có thêm cột tổng số phiếu nhập
DECLARE DonDH_cur CURSOR
FOR
SELECT dd.sodh, dd.ngaydh, dd.manhacc,COUNT(p.sodh)AS 'tongphieunhap'
FROM DonDH dd LEFT JOIN PNhap p ON p.sodh=dd.sodh
GROUP BY dd.sodh,dd.ngaydh,dd.manhacc
DECLARE @sodh NCHAR(4), @ngaydh datetime, @manhacc NCHAR(3), @slnhap INT
OPEN DonDH_cur
FETCH NEXT FROM DonDH_cur INTO @sodh,@ngaydh,@manhacc,@slnhap
WHILE 0=0
BEGIN
IF @@FETCH_STATUS<>0
BREAK
ELSE
BEGIN
PRINT @sodh+' '+CAST(@ngaydh AS CHAR(6))+' '+@manhacc+' '+CAST(@slnhap AS CHAR)
END
FETCH NEXT FROM DonDH_cur INTO @sodh,@ngaydh,@manhacc,@slnhap
END
CLOSE DonDH_cur
DEALLOCATE DonDH_cur
GO
Tích hợp kết quả từ nhiều câu truy vấn SQL (STRUCTURED QUERY LANGUAGE)
Truy vấn dữ liệu trong sql thao tác này được sử dụng rất nhiều trong các hệ thống phần mềm hoặc website
Cú pháp mệnh đề UNION
SELECT danh_sach_cot_1
FROM Ten_bang_1
UNION
SELECT danh_sach_cot_2
FROM ten_bang_2
[ORDER BY ds_cot_sap_xep]
Ví dụ
SELECT mavtu,‘N’ as LOAI, sum(slnhap) as TONGSL
FROM CTNVT
GROUP BY mavtu
UNION
SELECT mavtu,‘X’ as LOAI, sum(slxuat) as TONGSL
FROM CTXVT
GROUP BY mavtu
Mệnh đề EXCEPT
Trả về những dòng có trong kết quả bên trái và không có trong kết quả bên phải
VD : Tìm những mặt hàng chưa xuất bao giờ
SELECT mavt FROM vattu
EXCEPT
SELECT mavt FROM ctpx
Mệnh đề INTERSECT
Trả về những dòng có trong kết quả của cả 2 bên
VD : Tìm những mặt hàng vừa có nhập, vừa có xuất
Select mavt from ctpn
INTERSECT
Select mavt from ctpx
GROUP BY Toán tử ROLLUP
ROLLUP, CUBE là phần mở rộng của mệnh đề GROUP BY
Sử dụng để phát sinh các tổng trung gian từ các cột trong group by
SELECT [cot,] ham_phan_nhom(cot). . .
FROM ten_bang
[WHERE dieu_kien]
[GROUP BY bieu_thuc_phan_nhom [WITH ROLLUP]]
[HAVING bieu_thuc_loc_nhom];
[ORDER BY cot]
Ví dụ toán tử ROLLUP
SELECT maphong, macongviec, SUM(mucluong) as Luong
FROM qlns.nhanvien
WHERE maphong < 60
GROUP BY maphong, macongviec WITH ROLLUP
Ví dụ toán tử CUBE
SELECT maphong, macongviec, SUM(mucluong)
FROM qlns.nhanvien
WHERE maphong < 60
GROUP BY maphong, macongviec WITH CUBE
Hàm GROUPING
Hàm GROUPING có thể sử dụng với CUBE hoặc ROLLUP.
Sử dụng hàm GROUPING để tìm những dòng tổng được thêm bổ sung do các toán tử CUBE hoặc ROLLUP.
Hàm GROUPING trả về giá tri 0 hoặc 1.
SELECT [cot,] ham_phan_nhom(cot) . ,
GROUPING(bieu_thuc)
FROM ten_bang
[WHERE dieu_kien]
[GROUP BY bieu_thuc_phan_nhom [WITH ROLLUP|CUBE]]
[HAVING bieu_thuc_loc_nhom]
[ORDER BY cot]
Ví dụ hàm GROUPING
SELECT maphong maph, macongviec macv,
SUM(mucluong),
GROUPING(maphong) nhom_maph,
GROUPING(macongviec) nhom_macv
FROM qlns.nhanvien
WHERE maphong < 50
GROUP BY maphong, macongviec WITH ROLLUP
Chép dữ liệu ra bảng khác
Sử dụng mệnh đề SELECT INTO
Chép dữ liệu ra bảng mới
Chỉ chạy được 1 lần, ở lần sau bảng đã tồn tại thì sẽ gây ra lỗi
Cú pháp
SELECT ds_cac_cot INTO ten_bang_moi
FROM ten_bang
Tổng hợp dữ liệu cuối nhóm
Mệnh đề COMPUTE, COMPUTE … BY
Sử dụng chung với các hàm thống kê SUM, COUNT, MAX, MIN, AVG
Với Compute By, phải có mệnh đề ORDER BY đi kèm
Cú pháp
SELECT ds_cac_cot
FROM ten_bang
[ORDER BY Ten_cot_phan_nhom]
COMPUTE COUNT|MIN|MAX|SUM|AVG (Ten_cot)
[BY Ten_cot_phan_nhom]
Sử dụng mệnh đề COMPUTE BY và các hàm tính toán để thống kê theo nhóm dữ liệu
Hiển thị các thông tin trong bảng CTPXUAT và bổ sung thêm cột thành tiền, sao cho có dòng thống kê tổng thành tiền ở từng nhóm phiếu xuất
Hiển thị trong bảng CTPNHAP các thông tin: mã vật tư, sốnhập hàng, số lượng nhập, đơn giá nhập và có dòng thống kê tổng số lượng, giá thấp nhất, giá cao nhất ở từng vật tư
Giải bài tập :
--Câu 1
SELECT cx.sopx,cx.mavtu,cx.slxuat,cx.dgxuat
FROM ctpxuat AS cx
ORDER BY cx.sopx
compute SUM(cx.dgxuat) BY cx.sopx
-- Câu 2
SELECT cn.mavtu,cn.slnhap,cn.sopn,cn.dgnhap
FROM ctpnhap AS cn
ORDER BY cn.mavtu
compute SUM(cn.slnhap),MIN(cn.dgnhap), MAX(cn.dgnhap) BY cn.mavtu
Để kết hợp các bảng dữ liệu với nhau, ngoài các phép nối và các toán tử tập hợp, SQL cung cấp một cách khác để trả lại dữ liệu từ nhiều bảng gọi là truy vấn con (subquery)
Vấn đề cần giải quyết
Cú pháp truy vấn con
Truy vấn con thi hành môt lần trước truy vấn chính (truy vấn cha).
Kết quả trả về từ truy vấn con được sử dụng trong câu truy vấn chính.
SELECT ds_cot
FROM bang
WHERE bieu_thuc toan_tu(SELECT bieu_thuc FROM bang)
Sử dụng truy vấn con
SELECT honv, tennv
FROM qlns.nhanvien
WHERE mucluong >(SELECT mucluong FROM qlns.nhanvien WHERE tennv = ‘Huyền')
Một số quy tắc khi sử dụng truy vấn con
Câu truy vấn con phải nằm trong cặp ngoặc đơn.
Đặt truy vấn con bên phải điều kiện so sánh.
Mệnh đề ORDER BY trong truy vấn con là không cần thiết ngoại trừ khi có sử dụng mệnh đề TOP.
Sử dụng các toán tử một dòng với các truy vấn con trả về một dòng và sử dụng các toán tử nhiều dòng với các truy vấn con trả về nhiều dòng.
Các dạng truy vấn con
Truy vấn con một dòng
Truy vấn con nhiều dòng
Truy vấn con một dòng
Trả về duy nhất 1 dòng
Sử dụng các toán tử so sánh một dòng
= Bằng
> Lớn hơn
>= Lớn hơn hoặc bằng
< Nhỏ hơn
<= Nhỏ hơn học bằng
<> Không bằng
Sử dụng hàm nhóm dữ liệu trong truy vấn con
Mệnh đề HAVING với truy vấn con
Các truy vấn con được thi hành trước tiên.
Kết quả được trả về cho mệnh đề HAVING trong truy vấn chính.
Câu lệnh sau sai ở chỗ nào?
Câu lệnh sau có trả về kết quả nào không?
Truy vấn con nhiều dòng
Trả về nhiều dòng
Sử dụng các toán tử so sánh nhiều dòng
IN Bằng một trong các giá trị
ANY Chỉ cần thỏa một trong các giá trị trả về bởi truy vấn con
ALL Phải thỏa tất cả các giá trị trả về bởi truy vấn con
Sử dụng toán tử ANY
Sử dụng toán tử ALL
Cẩn thận với giá trị NULL trong truy vấn con
Tìm những nhân viên không quản lý bất cứ người nào
ELECT nv.tennv
FROM qlns.nhanvien nv
WHERE nv.manhanvien NOT IN (SELECT qly.manguoiquanly FROM lns.nhanvien qly)
Phần mở rộng của Truy vấn con
Truy vấn con nhiều cột
Truy vấn con trong mệnh đề FROM
Sử dụng truy vấn con đơn trị (scalar subquery)
Viết truy vấn con kết hợp (Correlated subquery)
Sử dụng hàm EXISTS
Truy vấn con trả về nhiều cột
Liệt kê danh sách những nhân viên được quản lý bởi bởi cùng một người và làm trong cùng phòng với nhân viên có mã số là 143.
SELECT manhanvien, manguoiquanly, maphong
FROM qlns.nhanvien
WHERE (STR(manguoiquanly)+STR(maphong)) IN
(SELECT STR(manguoiquanly)+STR(maphong)
FROM qlns.nhanvien
WHERE manhanvien = 143)
AND manhanvien NOT IN (143)
Sử dụng truy vấn con trong mệnh đề FROM
SELECT a.tennv, a.mucluong,a.maphong, b.tb_luong
FROM qlns.nhanvien a,
(SELECT maphong,AVG(mucluong) tb_luong FROM qlns.nhanvien GROUP BY maphong) b
WHERE a.maphong = b.maphong AND a.mucluong > b.tb_luong;
Sử dụng truy vấn con trong mệnh đề FROM
Biểu thức truy vấn con đơn trị (A scalar subquery expression) là một truy vấn con trả về duy nhất một cột và một dòng.
Truy vấn con đơn trị có thể sử dụng:
Trong các điều kiện và biểu thức của CASE
Trong tất cả các mệnh đề của SELECT ngoại trừ GROUP BY
Ví dụ Truy vấn con đơn trị
Truy vấn con kết hợp
Truy vấn con kết hợp (Correlated subqueries) được sử dụng cho việc xử lý từng dòng. Mỗi truy vấn con sẽ được thi hành một lần ứng với mỗi dòng của truy vấn cha
Sử dụng hàm EXISTS
Hàm EXISTS kiểm tra tồn tại kết quả trả về từ câu truy vấn con.
Tìm thấy một dòng trong truy vấn con:
Kết thúc tìm kiếm trong truy vấn con
Trả về kết quả TRUE
Nếu chưa tìm thấy dòng nào:
Trả về FALSE
Tiếp tục tìm kiếm trong truy vấn con
Sử dụng câu truy vấn con (sub query)
Hiển thị danh sách các nhà cung cấp mà chưa có đơn đặt hàng
Hiển thị các đơn đặt hàng mà chưa có phiếu nhập
Hiển thị danh sách các đơn đặt hàng có tổng số lần nhập là lớn nhất
Giải bài tập :
--Câu 1
SELECT cc.*
FROM nhacc AS cc
WHERE
cc.manhacc NOT IN ( SELECT dh.manhacc FROM dondh AS dh )
--Câu 2
SELECT dh.*
FROM dondh AS dh
WHERE dh.sodh NOT IN (SELECT pn.sodh FROM pnhap AS pn)
--Câu 3
SELECT top 1 tmp.TongSLNhap AS 'TongSLNhap'
FROM dondh AS dh
LEFT JOIN
(
SELECT SUM(cn.slnhap) AS 'TongSLNhap',pn.sodh
FROM ctpnhap cn
LEFT JOIN pnhap pn ON cn.sopn = pn.sopn
GROUP BY pn.sodh
) AS tmp ON tmp.sodh = dh.sodh
ORDER BY TongSLNhap DESC
Sử dụng câu truy vấn kết hợp (correlative query)
Hiển thị danh sách các nhà cung cấp mà chưa có đơn đặt hàng
Hiển thị danh sách các nhà cung cấp gồm các cột: mã nhà cung cấp, tên nhà cung cấp và tổng số đơn đặt hàng
Hiển thị danh sách các nhà cung cấp gồm các cột: mã nhà cung cấp, tên nhà cung cấp, tổng số đơn đặt hàng và tổng số phiếu nhập
Giải bài tập :
-- Câu 1
SELECT cc.*
FROM nhacc AS cc
WHERE
cc.manhacc NOT IN
( SELECT dh.manhacc
FROM dondh AS dh
WHERE dh.manhacc = cc.manhacc
)
-- Câu 2
SELECT
cc.manhacc,
cc.tennhacc,
( SELECT COUNT(*)
FROM dondh AS dh
WHERE dh.manhacc = cc.manhacc
) AS 'TongDondh'
FROM nhacc AS cc
--Hiển thịdanh sách các nhà cung cấp gồm các cột:
--mã nhà cung cấp, tên nhà cung cấp,
--tổng số đơn đặt hàng và tổng số phiếu nhập
-- Câu 3
SELECT
cc.manhacc,
cc.tennhacc,
( SELECT COUNT(*)
FROM dondh AS dh
WHERE dh.manhacc = cc.manhacc
) AS 'TongDondh',
(
SELECT COUNT(*)
FROM pnhap AS pn
INNER JOIN dondh AS dh ON pn.sodh = dh.sodh
WHERE dh.manhacc = cc.manhacc
) AS 'TongPnhap'
FROM nhacc AS cc
Tìm hiểu và cách gom nhóm dữ liệu: Avg, Sum, Max, Min, Count, Distinct, Group By, Having
Gom nhóm dữ liệu
Các hàm nhóm dữ liệu là phép toán trên một tập các dòng để trả về một kết quả tương ứng mỗi nhóm
Các hàm nhóm dữ liệu
AVG ([DISTINCT|ALL] bieu_thuc)
COUNT ({*|[DISTINCT|ALL] bieu_thuc})
MAX ([DISTINCT|ALL] bieu_thuc)
MIN ([DISTINCT|ALL] bieu_thuc)
SUM ([DISTINCT|ALL] bieu_thuc)
Cú pháp các hàm nhóm dữ liệu
SELECT [cot,] ham_nhom_du_lieu(cot), ...
FROM bang
[WHERE dieu_kien]
[GROUP BY cot]
[ORDER BY cot]
Sử dụng các hàm AVG,SUM & MAX,MIN
AVG và SUM : dùng cho dữ liệu kiểu số
MIN and MAX : Có thể dùng cho phần lớn các kiểu dữ liệu
SELECT AVG(mucluong) as AVG_LLuong,
MAX(mucluong) as MAX_Luong,
MIN(mucluong) as MIN_Luong,
SUM(mucluong) as SUM_Luong
FROM qlns.nhanvien
WHERE macongviec LIKE ‘%TT%'
SELECT MIN(ngayvaolam), MAX(ngayvaolam)
FROM qlns.nhanvien
Sử dụng hàm COUNT
COUNT(*) Trả về số dòng có trong bảng.
COUNT(bieu_thuc) trả về số dòng khác null của bieu_thuc.
SELECT COUNT(*)
FROM qlns.nhanvien
WHERE maphong = 50
Sử dụng từ khóa DISTINCT
COUNT(DISTINCT bieu_thuc) Trả về số dòng khác nhau và khác NULL của bieu_thuc.
Ví dụ : Cho biết có bao nhiêu phòng ban khác nhau trong bảng NHANVIEN
SELECT COUNT(DISTINCT maphong)
FROM qlns.nhanvien;
Tạo các phân nhóm dữ liệu
Mệnh đề GROUP BY
SELECT cot, ham_nhom_du_lieu(cot)
FROM bang
[WHERE dieu_kien]
[GROUP BY bieu_thuc_phan_nhom]
[ORDER BY cot]
Sử dụng mệnh đề GROUP BY
Tất cả các cột trong danh sách của SELECT nếu không phải là hàm nhóm dữ liệu thì phải tồn tại trong mệnh đề GROUP BY
SELECT maphong, AVG(mucluong)
FROM qlns.nhanvien
GROUP BY maphong
Các cột trong GROUP BY không bắt buộc phải có trong danh sách của SELECT
SELECT AVG(mucluong)
FROM qlns.nhanvien
GROUP BY maphong
Tạo phân nhóm trên nhiều cột
SELECT maphong maph, macongviec, SUM(mucluong)
FROM qlns.nhanvien
GROUP BY maphong, macongviec
ORDER BY maphong, macongviec
Lưu ý sử dụng các hàm nhóm dữ liệu
Bất kỳ một cột hay biểu thức trong danh sách của SELECT không phải là hàm nhóm dữ liệu thì phải có mệnh đề GROUP BY
Không thể sử dụng mệnh đề WHERE để giới hạn bớt các nhóm.
Sử dụng mệnh đề HAVING để lọc bớt các nhóm.
Không được phép sử dụng các hàm nhóm dữ liệu trong mệnh đề WHERE.
Lọc bớt kết quả phân nhóm
Mệnh đề HAVING
Để giới hạn bớt các nhóm trả về :
Các dòng đã được phân nhóm.
Các hàm nhóm dữ liệu đã đưa vào.
Chỉ những nhóm thỏa điều kiện HAVING mới nằm trong tập kết quả trả về.
SELECT cotcolumn, ham_nho_du_lieu
FROM bang
[WHERE dieu_kien]
[GROUP BY bieu_thuc_nhom_du_lieu]
[HAVING dieu_kien_loc_nhom_du_lieu]
[ORDER BY cot]
Sử dụng mệnh đề HAVING
SELECT maphong, MAX(mucluong) as [Max Luong]
FROM qlns.nhanvien
GROUP BY maphong
HAVING MAX(mucluong)>=10.000.000
Bài tập
Hiển thị danh sách các nhà cung cấp gồm các cột: mã nhà cung cấp, tên nhà cung cấp và tổng số đơn đặt hàng. Lọc ra các nhà cung cấp nào có tổng số đơn đặt hàng lớn hơn 1
Hiển thị danh sách các thông tin trong bảng PNHAP có thêm cột tổng thành tiền, biết rằng Tổng thành tiền = Σ(sốlượng nhập x đơn giá nhập) của các mẩu tin chi tiết tương ứng trong bảng CTPNHAP
Hiển thị danh sách các phiếu xuất hàng gồm các cột: sốphiếu xuất và tổng thành tiền. Trong đó sắp xếp theo thứ tự tổng thành tiền giảm dần
Hiển thị danh sách các phiếu xuất hàng có tổng thành tiền lớn nhất
--Câu 1
SELECT cc.manhacc,cc.tennhacc,COUNT(dh.sodh) AS 'Tổng đơn đặt hàng'
FROM nhacc AS cc
LEFT JOIN dondh AS dh ON cc.manhacc = dh.manhacc
GROUP BY cc.manhacc,cc.tennhacc
HAVING COUNT(dh.sodh) > 1
--Câu 2
SELECT pn.sopn,pn.sodh,pn.ngaynhap,SUM(cn.dgnhap*cn.slnhap) AS 'Tổng tiền nhập'
FROM pnhap AS pn
LEFT JOIN ctpnhap AS cn ON pn.sopn = cn.sopn
GROUP BY pn.sopn,pn.sodh,pn.ngaynhap
--Câu 3
SELECT cx.sopx,SUM(cx.dgxuat*cx.slxuat) AS 'Tổng tiền xuất'
FROM pxuat AS px
LEFT JOIN ctpxuat AS cx ON px.sopx = cx.sopx
GROUP BY cx.sopx
ORDER BY SUM(cx.dgxuat*cx.slxuat) DESC
--Câu 4
SELECT cx.sopx,MAX(cx.dgxuat*cx.slxuat) AS 'Tổng thành tiền lớn nhất'
FROM pxuat AS px
LEFT JOIN ctpxuat AS cx ON px.sopx = cx.sopx
GROUP BY cx.sopx
Truy vấn dữ liệu từ nhiều bảng SQL (STRUCTURED QUERY LANGUAGE)
Tìm hiểu và cách sử dụng kết bảng: Kết bằng (EquiJoin), Kết không bằng (Non EquiJoin), Kết với chính mình (Self Join), Kết bằng mệnh đề Join
Các loại phép kết
Kết bằng (EquiJoin)
Kết không bằng (Non EquiJoin)
Kết với chính mình (Self Join)
Kết bằng mệnh đề Join
Phép kết bằng
Sử dụng điều kiện kết bằng trong mệnh đề WHERE
Nếu xuất hiện tên cột trùng nhau trong nhiều bảng thì bắt buộc phải sử dụng tên bảng hoặc bí danh bảng trước tên cột.
SELECT bang1.cot, bang2.cot
FROM bang1, bang2
WHERE bang1.cot1 = bang2.cot2
Phép kết bằng thực hiện như thế nào?
Kết quả nhận được từ phép kết bằng
SELECT nhanvien.manhanvien, nhanvien.tennv,nhanvien.maphong,
phong.maphong,phong.makhuvuc
FROM qlns.nhanvien, qlns.phong
WHERE nhanvien.maphong = phong.maphong;
Sử dụng bí danh cho bảng
Đơn giản hóa các câu truy vấn khi cần sử dụng tên bảng cho việc truy xuất các cột.
SELECT nv.manhanvien, nv.tennv, nv.maphong,ph.maphong, ph.makhuvuc
FROM qlns.nhanvien nv , qlns.phong ph
WHERE nv.maphong = ph.maphong;
Kết nhiều hơn hai bảng
Để kết n bảng, ta cần tối thiểu n-1 phép kết. Ví dụ để kết ba bảng, ta cần tối thiểu hai phép kết bảng.
Kết không bằng
Kết quả từ phép kết không bằng
SELECT nv.tennv, nv.mucluong, lcv.maloaicv
FROM qlns.nhanvien nv, qlns.loaicongviec lcv
WHERE nv.mucluong BETWEEN lcv.mucluong_min AND lcv.mucluong_max;
Kết với chính mình
SELECT nvien.honv+’ ‘+nvien.tennv + N' làm việc cho ' + nguoiqly.tennv as [Nhân viên và người QLý]
FROM qlns.nhanvien nvien, qlns.nhanvien nguoiqly
WHERE nvien.manguoiquanly = nguoiqly.manhanvien ;
Mệnh đề CROSS JOIN sẽ kết mỗi dòng của bảng 1 với tất cả các dòng của bảng 2
Mệnh đề ON trong phép kết JOIN
Tách biệt đều kiện kết với các điều kiện chọn lọc dữ liệu khác.
Các câu truy vấn trở nên dễ đọc hơn.
Sử dụng mệnh đề JOIN …ON…
SELECT nv.manhanvien, nv.tennv, nv.maphong,ph.maphong, ph.makhuvuc
FROM qlns.nhanvien nv JOIN qlns.phong ph
ON (nv.maphong = ph.maphong)
Kết nhiều hơn hai bảng
SELECT manhanvien, tenphong, tenkhuvuc, thanhpho
FROM qlns.nhanvien nv
JOIN qlns.phong ph
ON ph.maphong = nv.maphong
JOIN qlns.khuvuc kv
ON ph.makhuvuc = kv.makhuvuc
Phép kết trái (LEFT [OUTER] JOIN)
SELECT nv.honv, nv.tennv, nv.maphong, ph.tenphong
FROM qlns.nhanvien nv
LEFT OUTER JOIN qlns.phong ph
ON (nv.maphong = ph.maphong)
Phép kết phải (RIGHT [OUTER] JOIN)
SELECT nv.honv, nv.tennv, nv.maphong, ph.tenphong
FROM qlns.nhanvien nv
RIGHT OUTER JOIN qlns.phong ph
ON (nv.maphong = ph.maphong)
Phép kết đầy đủ (FULL [OUTER] JOIN)
SELECT nv.honv, nv.tennv, nv.maphong, ph.tenphong
FROM qlns.nhanvien nv
FULL OUTER JOIN qlns.phong ph
ON (nv.maphong = ph.maphong)
Sử dụng biểu thức CASE trong truy vấn
CASE Biểu_thức
WHEN Giá_trị_1 THEN Biểu_thức_kết_quả_1
WHEN Giá_trị_2 THEN Biểu_thức_kết_quả_2
...]
[ELSE Biểu_thức_kết_quả_N]
END
Giá trị 1, giá trị 2
Các giá trị cụ thể để so sánh bằng (=) với biểu thức
Biểu thức kết quả 1, biểu thức kết quả 2
Biểu thức sẽ được trả về khi việc so sánh của biểu thức bằng với các giá trị so sánh tương ứng
Ví dụ
SELECT 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 'BI' THEN 'Bia lon'
WHEN 'LO' THEN 'Loa thùng'
ELSE 'Chưa phân loại'
END,
MAVTU, TENVTU, DVTINH
FROM VATTU
ORDER BY LEFT(MAVTU, 2)
Biểu thức CASE dạng tìm kiếm
CASE
WHEN Bt_logic_1 THEN Biểu_thức_kết_quả_1
[WHEN Bt_logic_2 THEN Biểu_thức_kết_quả_2
...]
[ ELSE Biểu_thức_kết_quả_N]
END
Biểu thức logic1, biểu thức logic2
Các biểu thức luận lý dùng để thực hiện các phép so sánh trong biểu thức CASE.
Biểu thức kết quả 1, biểu thức kết quả 2
Biểu thức sẽ được trả về khi một trong các biểu thức luận lý so sánh có kết quả là đúng.
Ví dụ
SELECT GHICHU=
CASE
WHEN PHANTRAM <20 THEN 'Lời ít'
WHEN PHANTRAM BETWEEN 20 AND 40 THEN 'Lời nhiều'
ELSE 'Rất lời'
END,
TENVTU, DVTINH, PHANTRAM
FROM VATTU
ORDER BY PHANTRAM
Bài tập
Hiển thị danh sách các chi tiết phiếu xuất có thêm các cột tên vật tư, ngày xuất.Lọc theo số lượng xuất lớn hơn 5 và ngày xuất trong tháng 1/2009
Hiển thị danh sách các nhà cung cấp gồm các thông tin sau: mã nhà cung cấp, tên nhà cung cấp đã có đặt hàng. Chú ý: không được trùng lắp dữ liệu
Hiển thị danh sách các đơn đặt hàng gần đây nhất trong bảng DONDH
Hiển thị danh sách các nhà cung cấp mà chưa có đơn đặt hàng
-- Câu 1
SELECT cx.*,vt.tenvtu,px.ngayxuat
FROM ctpxuat AS cx
INNER JOIN vattu AS vt ON cx.mavtu = vt.mavtu
INNER JOIN pxuat AS px ON cx.sopx = px.sopx
WHERE cx.slxuat > 5 AND MONTH(px.ngayxuat) = 1
-- Câu 2
SELECT DISTINCT cc.manhacc,cc.tennhacc
FROM nhacc AS cc
INNER JOIN dondh AS dh ON cc.manhacc = dh.manhacc
-- Câu 3
SELECT *
FROM dondh AS dh
ORDER BY dh.ngaydh DESC
-- Câu 4
SELECT *
FROM nhacc AS cc
WHERE cc.manhacc NOT IN (SELECT dh.manhacc FROM dondh AS dh)
Tìm hiểu và học cách sử dụng: Select, Top, Alias, Distinct, Where
Khả năng của lệnh SELECT
Chọn cột
Chọn dòng
Kết bảng
Lệnh SELECT cơ bản
SELECT : xác định những cột gì
FROM : xác định bảng nào
SELECT *|{[DISTINCT] cot|bieu_thuc [bi_danh],...}
FROM bang
Một số lưu ý khi viết câu lệnh SQL
Câu lệnh SQL không phân biệt chữ hoa, chữ thường.
Câu lệnh SQL có thể viết trên 1 hoặc nhiều dòng.
Các từ khóa không được viết tắt hoặc tách ra thành nhiều dòng.
Các mệnh đề khác nhau nên đặt trên những dòng khác nhau
Các phép toán của biểu thức
Tạo các biểu thức với các kiểu dữ liệu số và ngày bằng các phép toán số học
+ Cộng
– Trừ
* Nhân
/ Chia
Sử dụng các phép toán số học
SELECT tennv, mucluong, mucluong + 300
FROM qlns.nhanvien
Khái niệm giá trị NULL
NULL là một giá trị chưa được xác định
NULL khác với zero hoặc giá trị rỗng
Các biểu thức số học có chứa giá trị NULL sẽ cho kết quả NULL
SELECT tennv,mucluong,phucap,12*(mucluong+phucap)
FROM qlns.nhanvien
Khái niệm bí danh của cột (Alias)
Sự đổi tên tiêu đề cho cột
Rất hữu ích cho các biểu thức tính toán
Xuất hiện ngay sau tên cột hoặc biểu thức, có thể thêm từ khóa AS giữa tên cột và bí danh
Yêu cầu phải đặt trong dấu nháy kép hoặc móc vuông nếu có chứa khoảng trắng, ký tự đặc biệt hoặc sử dụng chữ hoa
Sử dụng các bí danh cột
SELECT tennv AS ten, phucap pc
FROM qlns.nhanvien
SELECT tennv [Ten], mucluong*12 "Luong Mot Nam"
FROM qlns.nhanvien
Loại bỏ những dòng trùng nhau
SELECT maphong
FROM qlns.nhanvien
Theo mặc định thì kết quả hiểu thị của câu lệnh truy vấn là tất cả các dòng, bao gồm cả những dòng trùng nhau
SELECT DISTINCT maphong
FROM qlns.nhanvien
Loại bỏ những dòng trùng nhau
Chỉ thể hiện một số dòng Top
SELECT [TOP (expression) [PERCENT][ WITH TIES ]]
[danh_sach_cot]
FROM ...
Ví dụ : Cho biết tên và mức lương 3 nhân viên đầu tiên
SELECT TOP 3 tennv, mucluong
FROM qlns.nhanvien
Giới hạn các dòng trả về Where
Mệnh đề WHERE theo sau mệnh đề FROM.
SELECT *|{[DISTINCT] cot|bieu_thuc [bi_danh],...}
FROM bang
[WHERE dieu_kien]
Sử dụng mệnh đề WHERE
SELECT manhanvien,honv,tennv,maphong,macongviec,ngayvaolam
FROM qlns.nhanvien
WHERE maphong = 20
Bài tập
Hiển thị danh sách các thông tin trong bảng CTPNHAP có thêm cột thành tiền biết rằng Thành tiền = SLNhap * DgNhap
Hiển thị danh sách các nhà cung cấp trong bảng NHACC có cột địa chỉ ở quận 1 HCM, sắp xếp theo thứ tự họ tên tăng dần và số điện thoại giảm dần
Hiển thị danh sách các phiếu xuất có ngày xuất nằm trong khoảng [1/1/2009, 31/1/2009]
Hiển thị danh sách các chi tiết phiếu xuất có thêm các cột tên vật tư, ngày xuất. Lọc theo số lượng xuất lớn hơn 5 và ngày xuất trong tháng 1/2009
Hiển thị danh sách các nhà cung cấp gồm các thông tin sau: mã nhà cung cấp, tên nhà cung cấp đã có đặt hàng. Chú ý: không được trùng lắp dữ liệu
Hiển thị danh sách các đơn đặt hàng gần đây nhất trong bảng DONDH
Hiển thị danh sách các nhà cung cấp mà chưa có đơn đặt hàng
-- Chú ý : select * là lấy tất cả các cột trong bảng
-- Câu 1
SELECT *,(slnhap * dgnhap) AS 'Thành tiền'
FROM ctpnhap
-- Câu 2
SELECT *
FROM nhacc
WHERE nhacc.diachi LIKE N'%Q1 HCM%'
ORDER BY nhacc.tennhacc ASC,nhacc.dienthoai DESC
-- Câu 3
-- đinh dạng ngày là : MM/dd/yyyy
SELECT *
FROM pxuat
WHERE pxuat.ngayxuat BETWEEN '1/1/2009' AND '1/31/2009'
-- Câu 4
SELECT cx.*,vt.tenvtu,px.ngayxuat
FROM ctpxuat AS cx
INNER JOIN vattu AS vt ON cx.mavtu = vt.mavtu
INNER JOIN pxuat AS px ON cx.sopx = px.sopx
WHERE cx.slxuat > 5 AND MONTH(px.ngayxuat) = 1
-- Câu 5
SELECT DISTINCT cc.manhacc,cc.tennhacc
FROM nhacc AS cc
INNER JOIN dondh AS dh ON cc.manhacc = dh.manhacc
-- Câu 6
SELECT *
FROM dondh AS dh
ORDER BY dh.ngaydh DESC
-- Câu 7
SELECT *
FROM nhacc AS cc
WHERE cc.manhacc NOT IN (SELECT dh.manhacc FROM dondh AS dh)
ALTER TABLE vattu ADD
CONSTRAINT chk_phantram CHECK (phantram BETWEEN 0 AND 100 ),
CONSTRAINT u_tenvtu UNIQUE(tenvtu),
CONSTRAINT d_dvtinh DEFAULT '' FOR dvtinh
Khái niệm về mô hình quan hệ dữ liệu
Thể hiện mối quan hệ giữa các bảng trong CSDL
Có thể sử dụng để
Thiết lập mối quan hệ khoá ngoại (FOREIGN KEY)
Chỉnh sửa cấu trúc bảng
Chỉnh sửa thuộc tính bảng
Tạo bảng mới
Ví dụ mô hình quan hệ
ALTER TABLE ctpxuat
ADD CONSTRAINT fk_ctpxuat_vattu FOREIGN KEY(mavtu) REFERENCES vattu(mavtu)
ON DELETE CASCADE ON UPDATE CASCADE
-- có 4 loại CASCADE, No Action, Set Null, Set Default
Cùng tìm hiểu và cách tạo ra các đối tượng chính trong SQL: Database, Table, View …
Khái niệm về cơ sở dữ liệu
Database dùng để
Chứa các bảng, bảng ảo, thủ tục nội,…
Mỗi database có một danh sách các người dùng
Người dùng phải có quyền truy cập database
Có thể phân nhóm người dùng để cấp quyền
Tử phiên bản SQL Server 2000 hỗ trợ Application Role
Các database hệ thống Master, Model, Tempdb, msdb
Các database ví dụ AdventureWorks, AdventureWorkDWs
Các tập tin vật lý lưu trữ cơ sở dữ liệu
Một database bao gồm tối thiểu hai file
.mdf: lưu trữ các đối tượng trong database như table, view, …
Có thể bổ sung thêm các tập tin lưu trữ khác
Tổ chức tốt các tập tin lưu trữ giúp tăng tốc độ xử lý
.ldf: lưu trữ quá trình cập nhật/thay đổi dữ liệu
Hỗ trợ phục hồi dữ liệu
Hỗ trợ backup/restore dữ liệu
Các thông số về kích thước
Initial size
File growth
Maximum file size
Tạo database
USE master
GO
--Nếu đã tồn tại database QLBanHang rồi thì xóa đi và tạo mới
IF EXISTS (SELECT * FROM MASTER.sys.DATABASES WHERE NAME=N'QLBanHang')
--Xóa Database
DROP DATABASE QLBanHang
GO
--Tạo database mới
CREATE DATABASE QLBanHang
Khái niệm về bảng
Bảng dùng để lưu trữ các thông tin của một đối tượng trong thực tế
Gồm có các dòng và các cột
Bảng trong CSDL thường có khoá chính (primary key)
Các bảng thường liên hệ với nhau bằng các mối quan hệ
Bảng được tạo trong các Schema (mặc định là schema dbo)
Bảng có thể có các ràng buộc (constraint), trigger
Làm việc như một bảng thông thường sử dụng câu SELECT để xem dữ liệu Select * From vw_DonDH
Sử dụng INSERT/UPDATE để cập nhật dữ liệu
Chỉ có thể cập nhật vào một bảng
Để INSERT dữ liệu vào bảng, bảng ảo phải thỏa mãn các yêu cầu về khóa, ràng buộc khóa ngọai, các cột NOT NULL, các cột tính toán, order by, group by, distinct
Sử dụng Delete để xoá dữ liệu: bảng ảo tạo từ hai hay nhiều bảng không thể xoá
Có thể xây dựng các trigger trên bảng ảo
Cập nhật dữ liệu qua bảng ảo sử dụng trigger INSTEAD OF
Bảng ảo có nhiều hạn chế khi thực hiện cập nhật dữ liệu
Group By, Order By, Distinct
Thiếu cột khoá
Ràng buộc toàn vẹn
SQL Server cung cấp loại trigger INSTEAD OF
Cơ chế tương tự như trigger thông thường
Mở rộng khả năng cập nhật, tính toán dữ liệu, đặc biệt với bảng ảo
Xem thêm phần TRIGGER
Bài tập: Các đối tượng trong CSDL
Tạo các đối tượng trong CSDL sau:
Danh mục Vật Tư (VATTU)
Tên cột
Ý nghĩa
Kiểu
Độ rộng
MaVtu
Mã vật tư
Char
4
TenVTu
Tên vật tư
VarChar
100
DvTinh
Đơn vị tính
VarChar
10
PhanTram
Tỷ lệ phần trăm
Real
Danh mục Nhà Cung Cấp (NHACC)
Tên cột
Ý nghĩa
Kiểu
Độ rộng
MaNhaCC
Mã nhà cung cấp
Char
3
TenNhaCC
Tên nhà cung cấp
NVarChar
100
DiaChi
Địa chỉ
NVarChar
200
DienThoai
Điện thoại
NVarChar
20
go
create table nhacc
(
manhacc char(3),
tennhacc nvarchar(100) not null,
diachi nvarchar(200),
dienthoai nvarchar(20),
constraint pk_nhacc primary key(manhacc)
)
go
create table pnhap
(
sopn char(4) ,
ngaynhap datetime default getdate(),--Ngày ngập sẽ bằng ngày hiện tại
sodh char(4),
constraint pk_pnhap primary key(sopn)
)
go
create table pxuat
(
sopx char(4),
ngayxuat datetime default getdate(),--Ngày xuất bằng ngày hiện tại
tenkh nvarchar(100),
constraint pk_pxuat primary key(sopx)
)
Sử dụng câu lệnh INSERT INTO VALUES để thêm các mẩu tin vào bảng
INSERT INTO nhacc
VALUES('C01', N'Lê Minh Trí', N'54 Hậu Giang Q6 HCM' ,'8781024')
INSERT INTO nhacc
VALUES('C02', N'Trần Minh Thạch', N'145 Hùng Vương Mỹ Tho', '7698154')
INSERT INTO nhacc
VALUES('C03', N'Hồng Phương', N'154/85 Lê Lai Q1 HCM', '9600125')
INSERT INTO nhacc
VALUES('C04', N'Nhật Thắng', N'198/40 Hương Lộ 14 QTB HCM', '8757757')
INSERT INTO nhacc
VALUES('C05', N'Lưu Nguyệt Quế',N'178 Nguyễn Văn Luông Đà Lạt','7964251')
INSERT INTO nhacc
VALUES('C07',N'Cao Minh Trung', N'125 Lê Quang Sung Nha Trang', N'Chưa có')
GO
--Table Vattu
INSERT INTO vattu
VALUES('DD01',N'Đầu DVD Hitachi 1 đĩa',N'Bộ','40')
INSERT INTO vattu
VALUES('DD02', N'Đầu DVD Hitachi 3 đĩa', N'Bộ','40')
INSERT INTO vattu
VALUES('TL15', N'Tủ lạnh Sanyo 150 lit', N'Cái', '25')
INSERT into vattu
VALUES('TL90', N'Tủ lạnh Sanyo 90 lit', N'Cái', '20')
INSERT INTO vattu
VALUES('TV14', N'Tivi Sony 14 inches', N'Cái', '15')
INSERT INTO vattu
VALUES('TV21', N'Tivi Sony 21 inches', N'Cái', '10')
INSERT INTO vattu
VALUES('TV29' ,N'Tivi Sony 29 inches', 'Cái' ,'10')
INSERT INTO vattu
VALUES('VD01', N'Đầu VCD Sony 1 đĩa',N'Bộ', '30')
INSERT INTO vattu
VALUES('VD02',N'Đầu VCD Sony 3 đĩa', N'Bộ', '30')
GO
--Table Dondh
INSERT INTO dondh
VALUES('D001', '01/15/2009', 'C03')
INSERT INTO dondh
VALUES('D002', '01/30/2009', 'C01')
INSERT INTO dondh
VALUES('D003' ,'02/10/2009' ,'C02')
INSERT INTO dondh
VALUES('D004', '02/17/2009' ,'C05')
INSERT INTO dondh
VALUES('D005' ,'03/01/2009', 'C02')
INSERT INTO dondh
VALUES('D006', '03/12/2009' ,'C05')
GO
--Table Pnhap
INSERT INTO pnhap
VALUES('N001', '01/17/2009', 'D001')
INSERT INTO pnhap
VALUES('N002', '01/20/2009', 'D001')
INSERT INTO pnhap
VALUES('N003', '01/31/2009', 'D002')
INSERT INTO pnhap
VALUES('N004','02/15/2009', 'D003')
GO
--Table ctdondh
INSERT INTO ctdondh
VALUES('D001', 'DD01', '10')
INSERT INTO ctdondh
VALUES('D001' ,'DD02' ,'15')
INSERT INTO ctdondh
VALUES('D002' ,'VD02', '30')
INSERT INTO ctdondh
VALUES('D003' ,'TV14' ,'10')
INSERT INTO ctdondh
VALUES('D003' ,'TV29', '20')
INSERT INTO ctdondh
VALUES('D004' ,'TL90' ,'10')
INSERT INTO ctdondh
VALUES('D005' ,'TV14', '10')
INSERT INTO ctdondh
VALUES('D005', 'TV29' ,'20')
INSERT INTO ctdondh
VALUES('D006' ,'TV14', '10')
INSERT INTO CTDONDH
VALUES('D006' ,'TV29' ,'20')
INSERT INTO CTDONDH
VALUES('D006' ,'VD01' ,'20')
GO
--Table ctpnhap
INSERT INTO ctpnhap
VALUES('N001', 'DD01', '8' ,'2,500,000')
INSERT INTO ctpnhap
VALUES('N001' ,'DD02' ,'10' ,'3,500,000')
INSERT INTO ctpnhap
VALUES('N002', 'DD01', '2', '2,500,000')
INSERT INTO ctpnhap
VALUES('N002' ,'DD02' ,'5', '3,500,000')
INSERT INTO ctpnhap
VALUES('N003', 'VD02', '30', '2,500,000')
INSERT INTO ctpnhap
VALUES('N004', 'TV14', '5', '2,500,000')
INSERT INTO ctpnhap
VALUES('N004' ,'TV29' ,'12', '3,500,000')
GO
--Table pxuat
INSERT INTO pxuat
VALUES('X001', '01/17/2009', N'Nguyễn Ngọc Phương Nhi')
INSERT INTO pxuat
VALUES('X002', '01/25/2009', N'Nguyễn Hồng Phương')
INSERT INTO pxuat
VALUES('X003', '01/31/2009', N'Nguyễn Tuấn Tú')
GO
--Table ctpxuat
INSERT INTO ctpxuat
VALUES('X001', 'DD01', '2', '3,500,000')
INSERT INTO ctpxuat
VALUES('X002' ,'DD01', '1' ,'3,500,000')
INSERT INTO ctpxuat
VALUES('X002' ,'DD02', '5', '4,900,000')
INSERT INTO ctpxuat
VALUES('X003', 'DD01' ,'3' ,'3,500,000')
INSERT INTO ctpxuat
VALUES('X003' ,'DD02', '2', '4,900,000')
INSERT INTO ctpxuat
VALUES('X003' ,'VD02', '10' ,'3,250,000')
GO
--Table tonkho
INSERT INTO tonkho
VALUES('200901' ,'DD01', '0', '10', '6')
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200901', 'VD02', '0' ,'30' ,'10' )
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200902', 'DD01' ,'4' ,'0', '0')
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200902', 'DD02' ,'8' ,'0', '0' )
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200901' ,'DD02', '0', '15' ,'7' )
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200902', 'VD02', '20', '0' ,'0' )
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200902', 'TV14', '5', '0', '0')
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200902', 'TV29' ,'12' ,'0', '0' )
GO
Đối tượng bảng ảo (view)
Tạo view có tên vw_CTPNHAP bao gồm các thông tin sau: số phiếu nhập hàng, mã vật tư, số lượng nhập, đơn giá nhập, thành tiền nhập
Tạo view có tên vw_CTPNHAP_VT bao gồm các thông tin sau: số phiếu nhập hàng, mã vật tư, tên vật tư, số lượng nhập, đơn giá nhập, thành tiền nhập
Tạo view có tên vw_CTPNHAP_VT_PN bao gồm các thông tin sau: số phiếu nhập hàng, ngày nhập hàng, số đơn đặt hàng, mã vật tư, tên vật tư, số lượng nhập, đơn giá nhập, thành tiền nhập
Tạo view có tên vw_CTPNHAP_VT_PN_DH bao gồm các thông tin sau: số phiếu nhập hàng, ngày nhập hàng, số đơn đặt hàng, mã nhà cung cấp, mã vật tư, tên vật tư, số lượng nhập, đơn giá nhập, thành tiền nhập
Tạo view có tên vw_CTPNHAP_loc bao gồm các thông tin sau: số phiếu nhập hàng, mã vật tư, số lượng nhập, đơn giá nhập, thành tiền nhập. Và chỉ liệt kê các chi tiết nhập có số lượng nhập > 5
Tạo view có tên vw_CTPNHAP_VT_loc bao gồm các thông tin sau: số phiếu nhập hàng, mã vật tư, tên vật tư, số lượng nhập, đơn giá nhập, thành tiền nhập. Và chỉ liệt kê các chi tiết nhập vật tư có đơn vị tính là Bộ
Tạo view có tên vw_CTPXUAT bao gồm các thông tin sau: số phiếu xuất hàng, mã vật tư, số lượng xuất, đơn giá xuất, thành tiền xuất
Tạo view có tên vw_CTPXUAT_VT bao gồm các thông tin sau: số phiếu xuất hàng, mã vật tư, tên vật tư, số lượng xuất, đơn giá xuất
Tạo view có tên vw_CTPXUAT_VT_PX bao gồm các thông tin sau: số phiếu xuất hàng, tên khách hàng, mã vật tư, tên vật tư, số lượng xuất, đơn giá xuất
Sửa view vw_CTPXUAT_VT_PX để bổ sung thêm cột thành tiền xuất
SQL Server là một hệ quản trị cơ sở dữ liệu quan hệ (Relational Database Management System (RDBMS) ) sử dụng câu lệnh SQL (Transact-SQL) để trao đổi dữ liệu giữa máy Client và máy cài SQL Server
Khái niệm về cấu trúc vật lý
Máy chủ (Server)
Máy trạm (Client): là các máy tính được phép truy xuất các tài nguyên đã được chia sẻ trên mạng.
Dây cáp mạng (Cable hoặc Media): là một hệ thống dây cáp nối kết vật lý các máy tính, máy in lại với nhau
Dữ liệu chung (Shared data): là các tập tin, thư mục mà người sử dụng trong hệ thống mạng có thể truy xuất vào máy chủ từ các máy trạm
Khái niệm về các xử lý
Các xử lý trong một ứng dụng có thể chia làm hai loại xử lý trên máy trạm và xử lý trên máy chủ
Xử lý trên máy trạm
Đọc, cập nhật dữ liệu
Tính toán, hiển thị dữ liệu trên màn hình giao diện
Có thể sử dụng nhiều loại ngôn ngữ lập trình khác nhau: Java, C#…
Xử lý trên máy chủ Database Server
Xử lý các yêu cầu đọc/ghi dữ liệu
Quản lý đồng bộ dữ liệu giữa các yêu cầu đọc ghi từ nhiều máy trạm gửi tới
Các dịch vụ quản trị dữ liệu tự động theo định kỳ như backup/restore dữ liệu
Vì sao phát triển ứng dụng khách chủ?
Giảm chi phí
Chia sẻ tài nguyên phần cứng/phần mềm
Giảm chi phí bản quyền
Giảm chi phí nâng cấp, bảo trì, quản lý
Tốc độ nhanh: Các xử lý phức tạp có thể thực hiện tại server
Tính tương thích cao: Nhiều công cụ lập trình được hỗ trợ bởi phần mềm làm việc trên máy chủ
Lịch sử ra đời Microsoft SQL Server
1970: IBM giới thiệu ngôn ngữ SEQUEL
1987: IBM tích hợp phần mềm quản trị CSDL vào hệ điều hành OS2
1988: Hệ quản trị CSDL Ashton-Tate được MS kết hợp với Sybase giới thiệu
MS bắt đầu phát triển SQL Server trên nền Ashton-Tate và đưa vào WinNT Server sau đó
Các phiên bản được sử dụng của SQL Server: 4.2, 4.21, 6.0, 6.5, 7.0, 2000, 2005, 2008, 2010, 2012, 2012, 2014, 2016
OLTP và OLAP
OLTP Databases (online transaction processing): SQL Server cho phép nhiều người dùng thực hiện đồng thời các giao tác và dữ liệu thay đổi theo thời gian thực
OLAP Databases (online analytical processing): Công nghệ OLAP tổ chức và thống kê khối lượng lớn dữ liệu được dùng để phân tích, báo cáo, hổ trợ ra quyết định
mysite 
Trả lời