Bài giảng Cơ sở dữ liệu - Chương 5: SQL - Lưu Huỳnh Châu Pha

an’ , ‘Tuyen’ , ‘635635635’ , ’12/30/1952’ , ’98 HV’ , ‘Nam’ , ‘37000’ , 4) 
INSERT INTO NHANVIEN(HONV, TENLOT, TENNV, MANV, DCHI) 
VALUES ( ‘Le’ , ‘Van’ , ‘Tuyen’ , ‘635635635’ , NULL ) 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
109 
Lệnh INSERT (tt) 
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ị 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
110 
Lệnh INSERT (tt) 
Cú pháp (thêm nhiều dòng) 
INSERT INTO () 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
111 
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 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
112 
Lệnh DELETE 
Dùng để xóa các dòng của bảng 
Cú pháp 
DELETE FROM 
[ WHERE ] 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
113 
Ví dụ 
DELETE FROM NHANVIEN 
WHERE HONV= ‘Tran’ 
DELETE FROM NHANVIEN 
WHERE MANV= ‘345345345’ 
DELETE FROM NHANVIEN 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
114 
Ví dụ 24 
DELETE FROM NHANVIEN 
WHERE PHG IN ( 
	 SELECT MAPHG 
	 FROM PHONGBAN 
	 WHERE TENPHG= ‘Nghien cuu’ ) 
Xóa đi những nhân viên ở phòng ‘Nghien cuu’ 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
115 
Lệnh DELETE (tt) 
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 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
116 
Lệnh DELETE (tt) 
TENNV 
HONV 
NGSINH 
DCHI 
PHAI 
LUONG 
PHG 
Tung 
Nguyen 
12/08/1955 
638 NVC Q5 
Nam 
40000 
5 
Hung 
Nguyen 
09/15/1962 
Ba Ria VT 
Nam 
38000 
5 
333445555 
987987987 
MANV 
MA_NQL 
888665555 
333445555 
TENLOT 
Thanh 
Manh 
Hang 
Bui 
07/19/1968 
33 NTH Q1 
Nu 
38000 
4 
999887777 
987654321 
Ngoc 
Nhu 
Le 
07620/1951 
219 TD Q3 
Nu 
43000 
4 
987654321 
888665555 
Quynh 
Vinh 
Pham 
11/10/1945 
450 TV HN 
Nam 
55000 
1 
888665555 
NULL 
Van 
SODA 
THOIGIAN 
MA_NVIEN 
10 
10.0 
333445555 
20 
20.0 
888665555 
30 
20.0 
987654321 
1 
20.0 
453453453 
Tam 
Tran 
07/31/1972 
543 MTL Q1 
Nu 
25000 
5 
453453453 
333445555 
Thanh 
Quang 
Tran 
04/08/1969 
980 LHP Q5 
Nam 
25000 
4 
987987987 
987654321 
Hong 
10 
35.0 
987987987 
30 
5.0 
987987987 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
117 
Lệnh DELETE (tt) 
TENNV 
HONV 
NGSINH 
DCHI 
PHAI 
LUONG 
PHG 
Tung 
Nguyen 
12/08/1955 
638 NVC Q5 
Nam 
40000 
Hung 
Nguyen 
09/15/1962 
Ba Ria VT 
Nam 
38000 
333445555 
987987987 
MANV 
MA_NQL 
888665555 
333445555 
TENLOT 
Thanh 
Manh 
Hang 
Bui 
07/19/1968 
33 NTH Q1 
Nu 
38000 
4 
999887777 
987654321 
Ngoc 
Nhu 
Le 
07620/1951 
219 TD Q3 
Nu 
43000 
4 
987654321 
888665555 
Quynh 
Vinh 
Pham 
11/10/1945 
450 TV HN 
Nam 
55000 
1 
888665555 
NULL 
Van 
Tam 
Tran 
07/31/1972 
543 MTL Q1 
Nu 
25000 
5 
5 
5 
453453453 
333445555 
Thanh 
Quang 
Tran 
04/08/1969 
980 LHP Q5 
Nam 
25000 
4 
987987987 
987654321 
Hong 
NULL 
NULL 
NULL 
05/22/1988 
333445555 
Nghien cuu 
5 
NG_NHANCHUC 
MA_NVIEN 
01/01/1995 
06/19/1981 
987987987 
888665555 
TENPHG 
MAPHG 
Dieu hanh 
4 
Quan ly 
1 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
118 
Lệnh 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 ] 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
119 
Ví dụ 
UPDATE NHANVIEN 
SET NGSINH= ’08/12/1965’ 
WHERE MANV= ‘333445555’ 
DELETE NHANVIEN 
SET LUONG=LUONG*1.1 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
120 
Ví dụ 25 
Với đề án có mã số 10, hãy thay đổi nơi thực hiện đề án thành ‘Vung Tau’ và phòng ban phụ trách là phòng 5 
UPDATE DEAN 
SET DIADIEM_DA= ’Vung Tau’ , PHONG=5 
WHERE MADA=10 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
121 
Lệnh 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 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
122 
Nội dung chi tiết 
Giới thiệu 
Định nghĩa dữ liệu 
Truy vấn dữ liệu 
Cập nhật dữ liệu 
Khung nhìn 
Định nghĩa 
Truy vấn 
Cập nhật 
Chỉ mục 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
123 
Khung nhìn 
Bảng là một quan hệ được tổ chức lưu trữ vật lý trong CSDL 
Khung nhìn cũng là một quan hệ 
Không được lưu trữ vật lý (bảng ảo) 
Không chứa dữ liệu 
Được định nghĩa từ những bảng khác 
Có thể truy vấn hay cập nhật thông qua khung nhìn 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
124 
Khung nhìn (tt) 
Tại sao phải sử dụng khung nhìn? 
Che dấu tính phức tạp của dữ liệu 
Đơn giản hóa các câu truy vấn 
Hiển thị dữ liệu dưới dạng tiện dụng nhất 
An toàn dữ liệu 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
125 
Định nghĩa khung nhìn 
Cú pháp 
Bảng ảo này có 
Danh sách thuộc tính trùng với các thuộc tính trong mệnh đề SELECT 
Số dòng phụ thuộc vào điều kiện ở mệnh đề WHERE 
Dữ liệu được lấy từ các bảng ở mệnh đề FROM 
CREATE VIEW AS 
DROP VIEW 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
126 
Ví dụ 
CREATE VIEW NV_P5 AS 
	 SELECT MANV, HONV, TENLOT, TENVN 
	 FROM NHANVIEN 
	 WHERE PHG=5 
CREATE VIEW TONGLNG_SLNV_PB AS 
	 SELECT MAPHG, TENPB, COUNT (*) AS SLNV, 	 SUM (LUONG) AS TONGLNG 
	 FROM NHANVIEN, PHONGBAN 
	 WHERE PHG=MAPHG 
	 GROUP BY TENPHG 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
127 
Truy vấn trên khung nhìn 
Tuy không chứa dữ liệu nhưng có thể thực hiện các câu truy vấn trên khung nhìn 
SELECT TENNV 
FROM NV_P5 
WHERE HONV LIKE ‘Nguyen’ 
NV_P5 ← π MANV,HONV, TENLOT, TENNV ( σ PHG=5 (NHANVIEN)) 
π TENNV ( σ HONV=‘Nguyen’ (NV_P5)) 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
128 
Truy vấn trên khung nhìn (tt) 
Có thể viết câu truy vấn dữ liệu từ khung nhìn và bảng 
SELECT HONV, TENVN, TENDA, THOIGIAN 
FROM NV_P5, PHANCONG, DEAN 
WHERE MANV=MA_NVIEN AND SODA=MADA 
NV_P5 ← π MANV,HONV, TENLOT, TENNV ( σ PHG=5 (NHANVIEN)) 
TMP ← NV_P5 MANV=MA_NVIEN PHONGBAN SODA=MADA DEAN 
π TENNV,TENDA,THOIGIAN (TMP) 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
129 
Cập nhật trên khung nhìn 
Có thể dùng các câu lệnh INSERT, DELETE và UPDATE cho các khung nhìn đơn giản 
Khung nhìn được xây dựng trên 1 bảng và có khóa chính của bảng 
Không thể cập nhật dữ liệu nếu 
Khung nhìn có dùng từ khóa DISTINCT 
Khung nhìn có sử dụng các hàm kết hợp 
Khung nhìn có mệnh đề SELECT mở rộng 
Khung nhìn được xây dựng từ bảng có RB trên cột 
Khung nhìn được xây dựng từ nhiều bảng 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
130 
Cập nhật trên khung nhìn (tt) 
Sửa lại họ cho nhân viên mã ‘123456789’ ở phòng 5 là ‘Pham’ 
UPDATE NV_P5 
SET HONV=‘Pham’ 
WHERE MANV= ‘123456789’ 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
131 
Nội dung chi tiết 
Giới thiệu 
Định nghĩa dữ liệu 
Truy vấn dữ liệu 
Cập nhật dữ liệu 
Khung nhìn 
Chỉ mục 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
132 
Chỉ mục 
Chỉ mục trên thuộc tính A là một cấu trúc dữ liệu làm cho việc tìm kiếm mẫu tin có chứa A hiệu quả hơn 
SELECT * 
FROM NHANVIEN 
WHERE PHG=5 AND PHAI= ‘Nu’ 
Bảng NHANVIEN có 10.000 bộ 
Có 200 nhân viên làm việc cho phòng 5 
Đọc 10.000 bộ 
Đọc 200 bộ 
Đọc 70 bộ 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
133 
Chỉ mục (tt) 
Cú pháp 
Ví dụ 
CREATE INDEX ON () 
CREATE INDEX PHG_IND ON NHANVIEN(PHG) 
CREATE INDEX PHG_PHAI_IND ON NHANVIEN(PHG, PHAI) 
DROP INDEX 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
134 
Chỉ mục (tt) 
Nhận xét 
Tìm kiếm nhanh trong trường hợp so sánh với hằng số và phép kết 
Làm chậm đi các thao tác thêm, xóa và sửa 
Tốn chi phí 
Lưu trữ chỉ mục 
Truy xuất đĩa nhiều 
Chọn lựa cài đặt chỉ mục hợp lý??? 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
135 
Ví dụ 
Xét quan hệ 
PHANCONG(MA_NVIEN, SODA, THOIGIAN) 
Giả sử 
PHANCONG được lưu trữ trong 10 block 
Chi phí để đọc toàn bộ dữ liệu của PHANCONG là 10 
Trung bình một nhân viên tham gia 3 đề án và một đề án có khoảng 3 nhân viên làm 
Dữ liệu được trải đều trong 10 block 
Chi phí để tìm một nhân viên hay một đề án là 3 
Khi sử dụng chỉ mục 
Chi phí đọc hay cập nhật chỉ mục 
Thao tác thêm cần 2 lần truy xuất đĩa 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
136 
Ví dụ (tt) 
Giả sử có 3 thao tác được thực hiện thường xuyên 
Q1 
Q2 
Q3 
SELECT SODA, THOIGIAN 
FROM PHANCONG 
WHERE MA_NVIEN= ‘123456789’ 
SELECT MANV 
FROM PHANCONG 
WHERE SODA=1 AND THOIGIAN=20.5 
INSERT INTO PHANCONG 
VALUES ( 123456789’ , 1, 20.5) 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
137 
Ví dụ (tt) 
Bảng so sánh chi phí 
Thao tác 
Không có chỉ mục 
 Chỉ mục trên MA_NVIEN 
 Chỉ mục trên SODA 
 Chỉ mục trên cả 2 thuộc tính 
Q1 
Q2 
Q3 
10 
10 
2 
4 
10 
4 
10 
4 
4 
4 
4 
6 
2 + 8p1 + 8p2 
4 + 6p2 
4 + 6p1 
6 - 2p1 – 2p2 
Chí phí TB 
Khoảng thời gian thực hiện Q1 là p1 
Khoảng thời gian thực hiện Q2 là p2 
Khoảng thời gian thực hiện Q3 là 1 - p1 - p2 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
138 
Bài tập về nhà 
Bài tập 
6.1.1 đến 6.1.4 / 252 - 253 
6.2.1 đến 6.2.3 / 262 - 263 
6.3.1 đến 6.3.3 / 274 - 275 
6.3.7 đến 6.3.10 / 275 - 276 
6.4.1, 6.4.2, 6.4.6, 6.4.7 / 284 - 285 
6.5.1, 6.5.2 / 290 - 291 
6.7.1 đến 6.7.6 / 311 -312 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
139