Giáo trình SQL Server 1

gaysinh 
Định nghĩa khoá chính (ràng buộc PRIMARY KEY) cho bảng NHANVIEN là cột 
MANV: 
ALTER TABLE nhanvien 
ADD 
CONSTRAINT pk_nhanvien PRIMARY KEY(manv) 
Định nghĩa khoá ngoài cho bảng NHANVIEN trên cột MADV tham chiếu đến cột 
MADV của bảng DONVI: 
ALTER TABLE nhanvien 
ADD 
CONSTRAINT fk_nhanvien_madv 
FOREIGN KEY(madv) REFERENCES donvi(madv) 
ON DELETE CASCADE 
ON UPDATE CASCADE 
Xoá bỏ ràng buộc kiểm tra số điện thoại của nhân viên 
ALTER TABLE nhanvien 
DROP CONSTRAINT CHK_NHANVIEN_DIENTHOAI 
Lưu ý: 
 • Nếu bổ sung thêm một cột vào bảng và trong bảng đã có ít nhất một bản ghi thì 
 cột mới cần bổ sung phải cho phép chấp nhận giá trị NULL hoặc phải có giá trị 
 mặc định. 
 • Muốn xoá một cột đang được ràng buộc bởi một ràng buộc hoặc đang được 
 tham chiếu bởi một khoá ngoài, ta phải xoá ràng buộc hoặc khoá ngoài trước sao 
 cho trên cột không còn bất kỳ một ràng buộc và không còn được tham chiếu bởi 
 bất kỳ khoá ngoài nào. 
 • Nếu bổ sung thêm ràng buộc cho một bảng đã có dữ liệu và ràng buộc cần bổ 
 sung không được thoả mãn bởi các bản ghi đã có trong bảng thì câu lệnh ALTER 
 TABLE không thực hiện được. 
3.3 Xoá bảng 
Khi một bảng không còn cần thiết , ta có thể xoá nó ra khỏi cơ sở dữ liệu bằng câu 
lệnh DROP TABLE. Câu lệnh này cũng đồng thời xoá tất cả những ràng buộc, chỉ 
mục, trigger liên quan đến bảng đó. 
 68 
Câu lệnh có cú pháp như sau: 
 DROP TABLE tên_bảng 
Trong các hệ quản trị cơ sở dữ liệu, khi đã xoá một bảng bằng lệnh DROP TABLE, 
ta không thể khôi phục lại bảng cũng như dữ liệu của nó. Do đó, cần phải cẩn thận khi 
sử dụng câu lệnh này. 
Câu lệnh DROP TABLE không thể thực hiện được nếu bảng cần xoá đang được tham 
chiếu bởi một ràng buộc FOREIGN KEY. Trong trường hợp này, ràng buộc 
FOREIGN KEY đang tham chiếu hoặc bảng đang tham chiếu đến bảng cần xoá phải 
được xoá trước. 
Khi một bảng bị xoá, tất cả các ràng buộc, chỉ mục và trigger liên quan đến bảng cũng 
đồng thời bị xóa theo. Do đó, nếu ta tạo lại bảng thì cũng phải tạo lại các đối tượng này. 
Ví dụ 3.10: Giả sử cột MADV trong bảng DONVI đang được tham chiếu bởi khoá 
ngoài fk_nhanvien_madv trong bảng NHANVIEN. Để xoá bảng DONVI ra khỏi cơ sở 
dữ liệu, ta thực hiện hai câu lệnh sau: 
Xoá bỏ ràng buộc fk_nhanvien_madv khỏi bảng NHANVIEN: 
ALTER TABLE nhanvien 
DROP CONSTRAINT fk_nhanvien_madv 
Xoá bảng DONVI: 
DROP TABLE donvi 
3.4 Khung nhìn 
Các bảng trong cơ sở dữ liệu đóng vai trò là các đối tượng tổ chức và lưu trữ dữ liệu. 
Như vậy, ta có thể quan sát được dữ liệu trong cơ sở dữ liệu bằng cách thực hiện các 
truy vấn trên bảng dữ liệu. Ngoài ra, SQL còn cho phép chúng ta quan sát được dữ liệu 
thông qua việc định nghĩa các khung nhìn. 
Một khung nhìn (view) có thể được xem như là một bảng “ảo” trong cơ sở dữ liệu có 
nội dung được định nghĩa thông qua một truy vấn (câu lệnh SELECT). Như vậy, một 
khung nhìn trông giống như một bảng với một tên khung nhìn và là một tập bao gồm 
các dòng và các cột. Điểm khác biệt giữa khung nhìn và bảng là khung nhìn không được 
xem là một cấu trúc lưu trữ dữ liệu tồn tại trong cơ sở dữ liệu. Thực chất dữ liệu quan 
sát được trong khung nhìn được lấy từ các bảng thông qua câu lệnh truy vấn dữ liệu. 
Hình 3.3 dưới đây minh hoạ cho ta thấy khung nhìn có tên DSSV được định nghĩa 
thông qua câu lệnh SELECT truy vấn dữ liệu trên hai bảng SINHVIEN và LOP: 
 69 
SELECT masv,hodem,ten, DATEDIFF(YY,ngaysinh,GETDATE()) AS tuoi,tenlop 
FROM sinhvien,lop 
WHERE sinhvien.malop=lop.malop 
 Hình 3.3 Khung nhìn DSSV với dữ liệu được lấy từ bảng SINHVIEN và LOP 
Khi khung nhìn DSSV đã được định nghĩa, ta có thế sử dụng câu lệnh SELECT để truy 
vấn dữ liệu từ khung nhìn như đối với các bảng. Khi trong câu truy vấn xuất hiện khung 
nhìn, hệ quản trị cơ sở dữ liệu sẽ dựa vào đinh nghĩa của khung nhìn để chuyển yêu cầu 
truy vấn dữ liệu liên quan đến khung nhìn thành yêu cầu tương tự trên các bảng cơ sở và 
việc truy vấn dữ liệu được thực hiện bởi yêu cầu tương đương trên các bảng. 
Việc sử dụng khung nhìn trong cơ sở dữ liệu đem lại các lợi ích sau đây: 
 • Bảo mật dữ liệu: Người sử dụng được cấp phát quyền trên các khung nhìn với 
 những phần dữ liệu mà người sử dụng được phép. Điều này hạn chế được phần nào 
 việc người sử dụng truy cập trực tiếp dữ liệu. 
 • Đơn giản hoá các thao tác truy vấn dữ liệu: Một khung nhìn đóng vai trò như là 
 một đối tượng tập hợp dữ liệu từ nhiều bảng khác nhau vào trong một “bảng”. Nhờ 
 70 
 vào đó, người sử dụng có thể thực hiện các yêu cầu truy vấn dữ liệu một cách đơn 
 giản từ khung nhìn thay vì phải đưa ra những câu truy vấn phức tạp. 
 • Tập trung và đơn giản hoà dữ liệu: Thông qua khung nhìn ta có thể cung cấp cho 
 người sử dụng những cấu trúc đơn giản, dễ hiểu hơn về dữ liệu trong cơ sở dữ liệu 
 đồng thời giúp cho người sử dụng tập trung hơn trên những phần dữ liệu cần thiết. 
 • Độc lập dữ liệu: Một khung nhìn có thể cho phép người sử dụng có được cái 
 nhìn về dữ liệu độc lập với cấu trúc của các bảng trong cơ sở dữ liệu cho dù các 
 bảng cơ sở có bị thay đổi phần nào về cấu trúc 
Tuy nhiên, việc sử dụng khung nhìn cũng tồn tại một số nhược điểm sau: 
 • Do hệ quản trị cơ sở dữ liệu thực hiện việc chuyển đổi các truy vấn trên khung 
 nhìn thành những truy vấn trên các bảng cơ sở nên nếu một khung nhìn được định 
 nghĩa bởi một truy vấn phức tạp thì sẽ dẫn đến chi phí về mặt thời gian khi thực hiện 
 truy vấn liên quan đến khung nhìn sẽ lớn. 
 • Mặc dù thông qua khung nhìn có thể thực hiện được thao tác bổ sung và cập nhật 
 dữ liệu cho bảng cơ sở nhưng chỉ hạn chế đối với những khung nhìn đơn giản. Đối 
 với những khung nhìn phức tạp thì thường không thực hiện được; hay nói cách khác 
 là dữ liệu trong khung nhìn là chỉ đọc. 
3.4.1 Tạo khung nhìn 
Câu lệnh CREATE VIEW được sử dụng để tạo ra khung nhìn và có cú pháp như sau: 
CREATE VIEW tên_khung_nhìn[(danh_sách_tên_cột)] 
AS 
câu_lệnh_SELECT 
Ví dụ 3.11: Câu lệnh dưới đây tạo khung nhìn có tên DSSV từ câu lệnh SELECT truy 
vấn dữ liệu từ hai bảng SINHVIEN và LOP 
CREATE VIEW dssv 
AS 
SELECT masv,hodem,ten, 
 DATEDIFF(YY,ngaysinh,GETDATE()) AS tuoi,tenlop 
FROM sinhvien,lop 
WHERE sinhvien.malop=lop.malop 
và nếu thực hiện câu lệnh: 
SELECT * FROM dssv 
 71 
ta có được kết quả như sau: 
Nếu trong câu lệnh CREATE VIEW, ta không chỉ định danh sách các tên cột cho khung 
nhìn, tên các cột trong khung nhìn sẽ chính là tiêu đề các cột trong kết quả của câu lệnh 
SELECT. Trong trường hợp tên các cột của khung nhìn đươc chỉ định, chúng phải có 
cùng số lượng với số lượng cột trong kết quả của câu truy vấn. 
Ví dụ 3.12: Câu lệnh dưới đây tạo khung nhìn từ câu truy vấn tương tự như ví dụ trên 
nhưng có đặt tên cho các cột trong khung nhìn: 
CREATE VIEW dssv(ma,ho,ten,tuoi,lop) 
AS 
SELECT masv,hodem,ten, DATEDIFF(YY,ngaysinh,GETDATE()),tenlop 
FROM sinhvien,lop 
WHERE sinhvien.malop=lop.malop 
và câu lệnh: 
SELECT * FROM dssv 
 72 
Khi tạo khung nhìn với câu lệnh CREATE VIEW, ta cần phải lưu ý một số nguyên tắc sau: 
 • Tên khung nhìn và tên cột trong khung nhìn, cũng giống như bảng, phải tuân theo 
 qui tắc định danh 
 • Không thể qui định ràng buộc và tạo chỉ mục cho khung nhìn 
 • Câu lệnh SELECT với mệnh đề COMPUTE ... BY không được sử dụng để định 
 nghĩa khung nhìn. 
Phải đặt tên cho các cột của khung nhìn trong các trường hợp sau đây: 
 Trong kết quả của câu lệnh SELECT có ít nhất một cột được sinh ra bởi một biểu 
thức (tức là không phải là một tên cột trong bảng cơ sở) và cột đó không được đặt tiêu đề. 
 Tồn tại hai cột trong kết quả của câu lệnh SELECT có cùng tiêu đề cột. 
Ví dụ 3.13: Câu lệnh dưới đây là câu lệnh sai do cột thứ 4 không xác định được tên cột 
CREATE VIEW tuoisinhvien 
AS 
SELECT masv,hodem,ten,DATEDIFF(YY,ngaysinh,GETDATE()) 
FROM sinhvien 
3.4.2 Cập nhật, bổ sung và xoá dữ liệu thông qua khung nhìn 
Đối với một số khung nhìn, ta có thể tiến hành thực hiện các thao tác cập nhập, bổ sung 
và xoá dữ liệu. Thực chất, những thao tác này sẽ được chuyển thành những thao tác 
tương tự trên các bảng cơ sở và có tác động đến những bảng cơ sở. 
Về mặt lý thuyết, để có thể thực hiện thao tác bổ sung, cập nhật và xoá, một khung nhìn 
trước tiên phải thoả mãn các điều kiện sau đây: 
 • Trong câu lệnh SELECT định nghĩa khung nhìn không được sử dụng từ khoá 
 DISTINCT, TOP, GROUP BY và UNION. 
 • Các thành phần xuất hiện trong danh sách chọn của câu lệnh SELECT phải là các 
 cột trong các bảng cơ sở. Trong danh sách chọn không được chứa các biểu thức tính 
 toán, các hàm gộp. 
Ngoài những điều kiện trên, các thao tác thay đổi đến dữ liệu thông qua khung nhìn còn 
phải đảm bảo thoả mãn các ràng buộc trên các bảng cơ sở, tức là vẫn đảm bảo tính toàn 
vẹn dữ liệu. Ví dụ dưới đây sẽ minh hoạ cho ta thấy việc thực hiện các thao tác bổ sung, 
cập nhật và xoá dữ liệu thông qua khung nhìn. 
Ví dụ 3.14: Xét định nghĩa hai bảng DONVI và NHANVIEN như sau: 
 73 
CREATE TABLE donvi 
( 
madv INT PRIMARY KEY, 
endv NVARCHAR(30) NOT NULL, 
dienthoai NVARCHAR(10) NULL, 
) 
CREATE TABLE nhanvien 
( 
manv NVARCHAR(10) PRIMARY KEY, 
hoten NVARCHAR(30) NOT NULL, 
ngaysinh DATETIME NULL, 
diachi NVARCHAR(50) NULL, 
madv INT FOREIGN KEY 
ON DELETE CASCADE 
ON UPDATE CASCADE 
REFERENCES donvi(madv) 
) 
Giả sử trong hai bảng này đã có dữ liệu như sau: 
 Bảng DONVI 
 Bảng NHANVIEN 
Câu lệnh dưới đây định nghĩa khung nhìn NV1 cung cấp các thông tin về mã nhân 
viên, họ tên và mã đơn vị nhân viên làm việc: 
 74 
CREATE VIEW nv1 
AS 
SELECT manv,hoten,madv FROM nhanvien 
Nếu ta thực hiện câu lệnh 
INSERT INTO nv1 VALUES('NV04','Le Thi D',1) 
Một bản ghi mới sẽ được bổ sung vào bảng NHANVIEN và dữ liệu trong bảng này sẽ là: 
 Bản ghi 
 mới 
Thông qua khung nhìn này, ta cũng có thể thực hiện thao tác cập nhật và xoá dữ liệu. 
Chẳng hạn, nếu ta thực hiện câu lệnh: 
DELETE FROM nv1 WHERE manv='NV04' 
Thì bản ghi tương ứng với nhân viên có mã NV04 sẽ bị xoá khỏi bảng NHANVIEN 
Nếu trong danh sách chọn của câu lệnh SELECT có sự xuất hiện của biểu thức tính toán 
đơn giản, thao tác bổ sung dữ liệu thông qua khung nhìn không thể thực hiện được. Tuy 
nhiên, trong trường hợp này thao tác cập nhật và xoá dữ liệu vấn có thể có khả năng 
thực hiện được (hiển nhiên không thể cập nhật dữ liệu đối với một cột có được từ một 
biểu thức tính toán). 
Ví dụ 3.15: Xét khung nhìn NV2 được định nghĩa như sau: 
CREATE VIEW nv2 
AS 
SELECT manv,hoten,YEAR(ngaysinh) AS namsinh,madv 
FROM nhanvien 
Đối với khung nhìn NV2, ta không thể thực hiện thao tác bổ sung dữ liệu nhưng có thể 
cập nhật hoặc xoá dữ liệu trên bảng thông qua khung nhìn này. Câu lệnh dưới đây là 
không thể thực hiện được trên khung nhìn NV2 
INSERT INTO nv2(manv,hoten,madv) 
VALUES('NV05','Le Van E',1) 
Nhưng câu lệnh: 
UPDATE nv2 SET hoten='Le Thi X' WHERE manv='NV04' 
 75 
hoặc câu lệnh 
DELETE FROM nv2 WHERE manv='NV04' 
lại có thể thực hiện được và có tác động đối với dữ liệu trong bảng NHANVIEN Trong 
trường hợp khung nhìn được tạo ra từ một phép nối (trong hoặc ngoài) trên nhiều bảng, 
ta có thể thực hiện được thao tác bổ sung hoặc cập nhật dữ liệu nếu thao tác này chỉ có 
tác động đến đúng một bảng cơ sở (câu lệnh DELETE không thể thực hiện được trong 
trường hợp này). 
Ví dụ 3.16: Với khung nhìn được định nghĩa như sau: 
CREATE VIEW nv3 
AS 
SELECT manv,hoten,ngaysinh, diachi,nhanvien.madv AS noilamviec, donvi.madv, 
tendv, dienthoai 
FROM nhanvien FULL OUTER JOIN donvi ON nhanvien.madv=donvi.madv 
Câu lệnh: 
INSERT INTO nv3(manv,hoten,noilamviec) 
VALUES('NV05','Le Van E',1) 
sẽ bổ sung thêm vào bảng NHANVIEN một bản ghi mới. 
Hoặc câu lệnh: 
INSERT INTO nv3(madv,tendv) VALUES(3,'P. Ke toan') 
bổ sung thêm vào bảng DONVI một bản ghi do cả hai câu lệnh này chỉ có tác động đến 
đúng một bảng cơ sở. 
Câu lệnh dưới đây không thể thực hiện được do có tác động một lúc đến hai bảng cơ sở. 
INSERT INTO nv3(manv,hoten,noilamviec,madv,tendv) 
VALUES('NV05','Le Van E',1,3,'P. Ke toan') 
3.4.3 Sửa đổi khung nhìn 
Câu lệnh ALTER VIEW được sử dụng để định nghĩa lại khung nhìn hiện có nhưng 
không làm thay đổi các quyền đã được cấp phát cho người sử dụng trước đó. 
Câu lệnh này sử dụng tương tự như câu lệnh CREATE VIEW và có cú pháp như sau: 
ALTER VIEW tên_khung_nhìn [(danh_sách_tên_cột)] 
AS 
Câu_lệnh_SELECT 
Ví dụ 3.17: Ta định nghĩa khung nhìn như sau: 
 76 
CREATE VIEW viewlop 
AS 
SELECT malop,tenlop,tenkhoa 
FROM lop INNER JOIN khoa ON lop.makhoa=khoa.makhoa 
WHERE tenkhoa='Khoa Vật lý’ 
và có thể định nghĩa lại khung nhìn trên bằng câu lệnh: 
ALTER VIEW view_lop 
AS 
SELECT malop,tenlop,hedaotao 
FROM lop INNER JOIN khoa ON lop.makhoa=khoa.makhoa 
WHERE tenkhoa='Khoa Công nghệ thông tin' 
3.4.4 Xoá khung nhìn 
Khi một khung nhìn không còn sử dụng, ta có thể xoá nó ra khỏi cơ sở dữ liệu thông qua 
câu lệnh: 
DROP VIEW tên_khung_nhìn 
Nếu một khung nhìn bị xoá, toàn bộ những quyền đã cấp phát cho người sử dụng trên 
khung nhìn cũng đồng thời bị xoá. Do đó, nếu ta tạo lại khung nhìn thì phải tiến hành 
cấp phát lại quyền cho người sử dụng. 
Ví dụ 3.18: Câu lệnh dưới đây xoá khung nhìn VIEW_LOP ra khỏi cơ sở dữ liệu 
DROP VIEW view_lop 
 77 
Bài tập chương 3 
 1. Sử dụng câu lệnh CREATE TABLE để tạo các bảng trong cơ sở dữ liệu như sơ 
 đồ dưới đây (bạn tự lựa chọn kiểu dữ liệu cho phù hợp) 
 2. Bổ sung ràng buộc thiết lập giá trị mặc định bằng 1 cho cột SOLUONG và bằng 
 0 cho cột MUCGIAMGIA trong bảng CHITIETDATHANG 
 3. Bổ sung cho bảng DONDATHANG ràng buộc kiểm tra ngày giao hàng và ngày 
 chuyển hàng phải sau hoặc bằng với ngày đặt hàng. 
 4. Bổ sung ràng buộc cho bảng NHANVIEN để đảm bảo rằng một nhân viên chỉ có 
 thể làm việc trong công ty khi đủ 18 tuổi và không quá 60 tuổi. 
 5. Với các bảng đã tạo được, câu lệnh: 
 DROP TABLE nhacungcap 
 có thể thực hiện được không? Tại sao? 
 6. Cho khung nhìn được định nghĩa như sau: 
 CREATE VIEW view_donhang 
 AS 
 78 
SELECT dondathang.sohoadon, makhachhang, manhanvien, ngaydathang, 
ngaygiaohang, ngaychuyenhang, noigiaohang,mahang, giaban,soluong,mucgiamgia 
FROM dondathang INNER JOIN chitietdathang 
ON dondathang.sohoadon = chitietdathang.sohoadon 
a. Có thể thông qua khung nhìn này để bổ sung dữ liệu cho bảng DONDATHANG 
được không? 
b. Có thể thông qua khung nhìn này để bổ sung dữ liệu cho bảng CHITIETDATHANG 
được không? 
 7. Với khung nhìn được định nghĩa như sau: 
CREATE VIEW view_donhang 
AS 
SELECT dondathang.sohoadon, makhachhang, manhanvien, ngaydathang, 
ngaygiaohang, ngaychuyenhang, noigiaohang, mahang, giaban * soluong as thanhtien, 
mucgiamgia 
FROM dondathang INNER JOIN chitietdathang 
ON dondathang.sohoadon = chitietdathang.sohoadon 
a. Có thể thông qua khung nhìn này để xoá hay cập nhật dữ liệu trong bảng 
DONDATHANG được không? 
b. Có thể thông qua khung nhìn này để cập nhật dữ liệu trong bảng 
CHITIETDATHANG được không? 
 79