Bài giảng Cơ sở dữ liệu - Chương 4: Ngôn ngữ SQL (Structured Query Language)

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 Ví dụ - Lồng phân cấp SELECT MANV, TENNV FROM NHANVIEN WHERE PHG IN 	( 	SELECT MAPHG 	FROM DIADIEM_PHG 	WHERE DIADIEM=‘TP HCM’ ) (1, 5) SELECT MANV, TENNV FROM NHANVIEN, DIADIEM_PHG WHERE DIADIEM=‘TP HCM’ AND PHG=MAPHG Ví dụ 7 Tìm những nhân viên không có thân nhân nào Ví dụ 8 Tìm những nhân viên có lương lớn hơn lương của ít nhất một nhân viên phòng 4 Ví dụ 9 Tìm những nhân viên có lương lớn hơn lương của tất cả nhân viên phòng 4 Ví dụ 10 Tìm những trưởng phòng có tối thiểu một thân nhân Ví dụ - Lồng tương quan SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG SELECT MANV, TENNV FROM NHANVIEN WHERE EXISTS ( 	SELECT * 	FROM PHONGBAN 	WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG ) Ví dụ 6 Tìm nhân viên có người thân cùng tên và cùng giới tính Ví dụ 7 Tìm những nhân viên không có thân nhân nào Ví dụ 8 Tìm những nhân viên có lương lớn hơn lương của ít nhất một nhân viên phòng 4 Ví dụ 10 Tìm những trưởng phòng có tối thiểu một thân nhân Nhận xét IN và EXISTS IN IN Thuộc tính ở mệnh đề SELECT của truy vấn con phải có cùng kiểu dữ liệu với thuộc tính ở mệnh đề WHERE của truy vấn cha EXISTS Không cần có thuộc tính, hằng số hay biểu thức nào khác đứng trước Không nhất thiết liệt kê tên thuộc tính ở mệnh đề SELECT của truy vấn con Những câu truy vấn có = ANY hay IN đều có thể chuyển thành câu truy vấn có EXISTS Phép chia trong đại số quan hệ Sử dụng NOT EXISTS để biểu diễn SELECT R1.A, R1.B, R1.C FROM R R1 WHERE NOT EXISTS ( 	SELECT * 	FROM S 	WHERE NOT EXISTS ( 	SELECT * 	FROM R R2 	WHERE R2.D=S.D AND R2.E=S.E 	AND R1.A=R2.A AND R1.B=R2.B AND R1.C=R2.C )) Ví dụ 11 Tìm tên các nhân viên được phân công làm tất cả các đồ án Tìm tên các nhân viên mà không có đề án nào là không được phân công làm Tập bị chia(R1): PHANCONG(MA_NVIEN, SODA) Tập chia(S): DEAN(MADA) Tập kết quả: KQ(MA_NVIEN) Kết KQ với NHANVIEN để lấy ra TENNV Ví dụ 11 (tt) Tìm tên các nhân viên được phân công làm tất cả các đồ án Nội dung chi tiết Giới thiệu Định nghĩa dữ liệu Truy vấn dữ liệu Truy vấn cơ bản Tập hợp, so sánh tập hợp và truy vấn lồng Hàm kết hợp và gom nhóm Một số dạng truy vấn khác Cập nhật dữ liệu Khung nhìn (view) Chỉ mục (index) 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 Ví dụ 12 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 Ví dụ 13 Cho biết số lượng nhân viên của phòng ‘Nghien cuu’ Ví dụ 14 Cho biết số lượng nhân viên của từng phòng ban Gom nhóm Cú pháp Sau khi gom nhóm Mỗi nhóm các bộ sẽ có cùng giá trị tại các thuộc tính gom nhóm SELECT FROM WHERE GROUP BY Ví dụ 14 Cho biết số lượng nhân viên của từng phòng ban Ví dụ 15 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 Ví dụ 16 Cho biết những nhân viên tham gia từ 2 đề án trở lên bị loại ra Điều kiện trên nhóm Cú pháp SELECT FROM WHERE GROUP BY HAVING Ví dụ 16 Cho biết những nhân viên tham gia từ 2 đề án trở lên Ví dụ 17 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 Nhận xét Mệnh đề GROUP BY Các thuộc tính trong mệnh đề SELECT (trừ những thuộc tính trong các hàm kết hợp) phải xuất hiện trong mệnh đề GROUP BY Mệnh đề HAVING Sử dụng các hàm kết hợp trong mệnh đề SELECT để kiểm tra một số điều kiện nào đó Chỉ kiểm tra điều kiện trên nhóm, không là điều kiện lọc trên từng bộ Sau khi gom nhóm điều kiện trên nhóm mới được thực hiện Nhận xét (tt) Thứ tự thực hiện câu truy vấn có mệnh đề GROUP BY và HAVING (1) Chọn ra những dòng thỏa điều kiện trong mệnh đề WHERE (2) Những dòng này sẽ được gom thành nhiều nhóm tương ứng với mệnh đề GROUP BY (3) Áp dụng các hàm kết hợp cho mỗi nhóm (4) Bỏ qua những nhóm không thỏa điều kiện trong mệnh đề HAVING (5) Rút trích các giá trị của các cột và hàm kết hợp trong mệnh đề SELECT Ví dụ 18 Tìm những phòng ban có lương trung bình cao nhất Ví dụ 19 Tìm 3 nhân viên có lương cao nhất Ví dụ 12 Tìm tên các nhân viên được phân công làm tất cả các đồ án Nội dung chi tiết Giới thiệu Định nghĩa dữ liệu Truy vấn dữ liệu Truy vấn cơ bản Tập hợp, so sánh tập hợp và truy vấn lồng Hàm kết hợp và gom nhóm Một số dạng truy vấn khác Cập nhật dữ liệu Khung nhìn (view) Chỉ mục (index) Điều kiện kết ở mệnh đề FROM Kết bằng Kết ngoài SELECT FROM R1 [INNER] JOIN R2 ON WHERE SELECT FROM R1 LEFT|RIGHT [OUTER] JOIN R2 ON WHERE Ví dụ 20 Tìm mã và tên các nhân viên làm việc tại phòng ‘Nghien cuu’ Ví dụ 21 Tìm họ tên các nhân viên và tên các đề án nhân viên tham gia nếu có Ví dụ 22 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) Ví dụ 23 Cho biết họ tên các nhân viên và năm về hưu Kết luận SELECT FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ] 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 Thêm (insert) Xóa (delete) Sửa (update) Khung nhìn (view) Lệnh 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 Lệnh INSERT (tt) Cú pháp (thêm 1 dòng) INSERT INTO () VALUES () Ví dụ INSERT INTO NHANVIEN(HONV, TENLOT, TENNV, MANV) VALUES (‘Le’, ‘Van’, ‘Tuyen’, ‘635635635’) INSERT INTO NHANVIEN VALUES (‘Le’, ‘Van’, ‘Tuyen’, ‘635635635’, ’12/30/1952’, ’98 HV’, ‘Nam’, ‘37000’, 4) INSERT INTO NHANVIEN(HONV, TENLOT, TENNV, MANV, DCHI) VALUES (‘Le’, ‘Van’, ‘Tuyen’, ‘635635635’, NULL) 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ị Lệnh INSERT (tt) Cú pháp (thêm nhiều dòng) INSERT INTO () 	 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 Lệnh DELETE Dùng để xóa các dòng của bảng Cú pháp DELETE FROM [WHERE ] Ví dụ DELETE FROM NHANVIEN WHERE HONV=‘Tran’ DELETE FROM NHANVIEN WHERE MANV=‘345345345’ DELETE FROM NHANVIEN Ví dụ 24 Xóa đi những nhân viên ở phòng ‘Nghien cuu’ 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 Lệnh DELETE (tt) 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 453453453 333445555 Thanh Quang Tran 04/08/1969 980 LHP Q5 Nam 25000 4 987987987 987654321 Hong 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 ] Ví dụ UPDATE NHANVIEN SET NGSINH=’08/12/1965’ WHERE MANV=‘333445555’ UPDATE NHANVIEN SET LUONG=LUONG*1.1 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 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 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 (view) Định nghĩa Truy vấn Cập nhật 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 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 Đị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 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 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)) 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)) 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ậ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’