Bài giảng Cơ sở dữ liệu - Bài 4: Ngôn ngữ SQL truy vấn cơ sở dữ liệu - Thiều Quang Trung

 BÀI 4 
NGÔN NGỮ SQL TRUY VẤN CSDL 
 GV Th.S. Thiều Quang Trung 
 Trường Cao đẳng Kinh tế đối ngoại 
Nội dung 
 1 • Câu truy vấn tổng quát 
 2 • Toán tử sử dụng trong truy vấn 
 3 • Các dạng câu truy vấn 
 4 • Các hàm tổng hợp nhóm 
 5 • Ví dụ các câu hỏi truy vấn 
 GV Thiều Quang Trung 2 
 Ngôn ngữ truy vấn SQL 
• Là ngôn ngữ chuẩn, có cấu trúc dùng để truy 
 vấn và thao tác trên CSDL quan hệ. 
• Câu truy vấn tổng quát: 
 SELECT [DISTINCT] danh_sách_cột | hàm 
 FROM danh sách các quan hệ (hay bảng, table) 
 [WHERE điều_kiện] 
 [GROUP BY danh_sách_cột_gom_nhóm] 
 [HAVING điều_kiện_trên_nhóm] 
 [ORDER BY cột1 ASC | DESC, cột2 ASC | DESC, ] 
 GV Thiều Quang Trung 3 
Các toán tử sử dụng trong truy vấn 
 . Toán tử so sánh: 
 • =,>,=, 
 • BETWEEN 
 • IS NULL, IS NOT NULL 
 • LIKE (%,_) 
 • IN, NOT IN 
 • EXISTS, NOT EXISTS 
 • SOME, ALL, ANY 
 . Toán tử logic: AND, OR. 
 . Các phép toán: +, - ,* , / 
 . Các hàm xử lý ngày (DAY( )), tháng (MONTH( )), năm 
 (YEAR( )) 
 GV Thiều Quang Trung 4 
Các dạng câu truy vấn 
. 5 dạng câu SELECT: 
 • SELECT đơn giản 
 • SELECT có mệnh đề ORDER BY 
 • SELECT lồng (Subquery) 
 • SELECT gom nhóm (GROUP BY) 
 • SELECT gom nhóm có điều kiện HAVING 
 GV Thiều Quang Trung 5 
Phân loại câu truy vấn 
 . Các hàm tổng hợp nhóm cơ bản sử dụng với 
 câu truy vấn: 
 • COUNT( ) 
 • SUM( ) 
 • MAX( ) 
 • MIN( ) 
 • AVG( ) 
 GV Thiều Quang Trung 6 
Ví dụ các câu hỏi truy vấn 
Bài tập: Cho lược đồ CSDL “quản lý đề án công ty” như 
sau: 
 NHANVIEN (MaNV, HoTen, Phai, Luong,NTNS, 
 Ma_NQL, MaPH) 
 PHONGBAN (MaPH, TenPH, TRPH) 
 DEAN (MaDA, TenDA, Phong, NamThucHien) 
 PHANCONG (MaNV, MaDA, ThoiGian) 
 GV Thiều Quang Trung 7 
Ví dụ các câu hỏi truy vấn 
NHANVIEN 
 MANV HOTEN NTNS PHAI MA_NQL MaPH LUONG 
 001 Vuong Ngoc Quyen 22/10/1957 Nu QL 3.000.000 
 002 Nguyen Thanh Tung 09/01/1955 Nam 001 NC 2.500.000 
 003 Le Thi Nhan 18/12/1960 Nu 001 DH 2.500.000 
 004 Dinh Ba Tien 09/01/1968 Nam 002 NC 2.200.000 
 005 Bui Thuy Vu 19/07/1972 Nam 003 DH 2.200.000 
 006 Nguyen Manh Hung 15/09/1973 Nam 002 NC 2.000.000 
 007 Tran Thanh Tam 31/07/1975 Nu 002 NC 2.200.000 
 008 Tran Hong Minh 04/07/1976 Nu 004 NC 1.800.000 
 GV Thiều Quang Trung 8 
PHONGBAN 
 MAPH TENPH TRPH 
 QL Quan Ly 001 
 DH Dieu Hanh 003 
 NC Nghien Cuu 002 
DEAN 
 MADA TENDA PHONG NamThucHien 
 TH001 Tin hoc hoa 1 NC 2002 
 TH002 Tin hoc hoa 2 NC 2003 
 DT001 Dao tao 1 DH 2004 
 DT002 Dao tao 2 DH 2004 
 GV Thiều Quang Trung 9 
 PHANCONG 
MANV MADA THOIGIAN 
 001 TH001 30,0 
 001 TH002 12,5 
 002 TH001 10,0 
 002 TH002 10,0 
 002 DT001 10,0 
 002 DT002 10,0 
 003 TH001 37,5 
 004 DT001 22,5 
 004 DT002 10,0 
 006 DT001 30,5 
 007 TH001 20,0 
 007 TH002 10,0 
 008 DT002 12,5 
 GV Thiều Quang Trung 10 
 Câu hỏi có toán tử BETWEEN, IS NULL và mệnh đề 
 ORDER BY 
Câu hỏi 1: Sử dụng =,>,>=, Danh sách các nhân viên sinh trong 
khoảng từ năm 1970 đến 1975? 
 Select MaNV, HoTen From NhanVien 
 where Year(NTNS)>=1970 AND Year(NTNS)<=1975 
 Câu hỏi 2: Sử dụng BETWEEN, ORDER BY. Danh sách các nhân viên 
 sinh trong khoảng từ năm 1970 đến 1975? Sắp xếp theo mức lương 
 giảm dần. 
 Select * From NhanVien where Year(NTNS) BETWEEN 1970 and 
 1975 ORDER BY Luong DESC 
Câu hỏi 3: Sử dụng IS NULL. Cho biết những nhân viên không có người 
quản lý trực tiếp? (không chịu sự quản lý trực tiếp của người nào) 
 Select MaNV, HoTen, NTNS, Ma_NQL from NhanVien where 
 Ma_NQL is Null 
 GV Thiều Quang Trung 11 
 Câu hỏi có toán tử SO SÁNH IN & NOT IN 
Câu hỏi 4: Sử dụng Is Not Null. Cho biết những nhân viên có người 
quản lý trực tiếp?Thông tin hiển thị gồm: mã nhân viên, họ tên, mã 
người quản lý. 
 Câu hỏi 5: Sử dụng IN (so sánh với một tập hợp giá trị cụ thể). Cho biết 
 họ tên nhân viên thuộc phòng „NC‟ hoặc phòng „DH‟? 
Câu hỏi 6: Sử dụng IN (so sánh với một tập hợp giá trị chọn từ câu 
SELECT khác). Cho biết họ tên nhân viên tham gia đề án “Tin học hóa 
1” ? 
 GV Thiều Quang Trung 12 
 Câu hỏi có toán tử SO SÁNH IN & NOT IN 
Câu hỏi 7: Cho biết mã số, họ tên, ngày tháng năm sinh của những 
nhân viên đã tham gia đề án? 
Câu hỏi 8: Sử dụng NOT IN. Cho biết mã số, họ tên, ngày tháng năm 
sinh của những nhân viên không tham gia đề án nào? 
Gợi ý cho mệnh đề NOT IN: thực hiện câu truy vấn “tìm nhân viên có 
tham gia đề án (dựa vào bảng PhanCong)”, sau đó lấy phần bù. 
 Câu hỏi 9: Cho biết tên phòng ban không chủ trì các đề án triển khai 
năm 2005? Gợi ý: thực hiện câu truy vấn “tìm phòng ban chủ trì các đề 
án triển khai năm 2005”, sau đó lấy phần bù. 
 GV Thiều Quang Trung 13 
 Câu hỏi có toán tử SO SÁNH LIKE 
Câu hỏi 10: so sánh chuỗi = chuỗi. Liệt kê mã nhân viên, ngày tháng 
năm sinh, mức lương của nhân viên có tên “Bui Thuy Vu”? 
Câu hỏi 11: Sử dụng LIKE (%: thay thế 1 chuỗi ký tự). Tìm những nhân 
viên có họ Nguyễn. 
Câu hỏi 12: Tìm những nhân viên có tên Lan. 
Câu hỏi 13: Tìm những nhân viên có tên lót là “Văn”. 
 Câu hỏi 14: Sử dụng LIKE ( _: thay thế 1 ký tự bất kỳ). Tìm những nhân 
viên tên có tên „Nguyễn La_‟ (ví dụ Lam, Lan) 
 GV Thiều Quang Trung 14 
 Câu hỏi có hàm gộp COUNT,SUM,MAX,MIN,AVG 
a) Sử dụng các hàm COUNT, SUM, MIN, MAX, 
AVG trên 1 nhóm lớn (trên toàn bộ quan hệ): 
Câu hỏi 15: Tính số nhân viên của công ty. 
Câu hỏi 16: Tính số lượng nhân viên quản lý trực tiếp nhân viên 
khác. 
Câu hỏi 17: Tìm mức lương lớn nhất, mức lương trung bình, tổng 
lương của công ty. 
Câu hỏi 18: Cho biết nhân viên có mức lương lớn nhất 
Câu hỏi 19: Cho biết nhân viên có mức lương trên mức lương 
trung bình của công ty. 
 GV Thiều Quang Trung 15 
Câu hỏi có MỆNH ĐỀ GROUP BY 
b) Sử dụng các hàm COUNT, SUM, MIN, MAX, 
AVG trên từng nhóm nhỏ: mệnh đề GROUP BY 
. Chia các dòng thành các nhóm nhỏ dựa trên tập 
 thuộc tính chia nhóm. 
. Thực hiện các phép toán trên nhóm: Count, 
 Sum, Min, Max, AVG. 
 GV Thiều Quang Trung 16 
 Giải thích MỆNH ĐỀ GROUP BY 
Quan hệ NV Chia các dòng thành các 
 Q S nhóm dựa trên tập thuộc 
 a 10 
 a 2 tính chia nhóm 
 b 9 
 Q 
 b 5 Count(S) Tương tự cho các 
 c 10 a 2 hàm SUM, MIN, 
 c 8 b 2 MAX, AVG 
 c 6 c 5 
 nhóm c 4 d 3 
 c 10 
 d 16 
 d 18 Câu SQL: 
 d 50 Select Q, count(S) 
 From NV 
Các thuộc tính GROUP BY: Q 
 Group by Q 
 GV Thiều Quang Trung 17 
 Câu hỏi có MỆNH ĐỀ GROUP BY 
Câu hỏi 20: Cho biết số lượng nhân viên theo từng phái? 
Do cột phái có 2 giá trị “nam” và “nữ”, trường hợp này ta chia 
bảng NhanVien thành 2 nhóm nhỏ. Thuộc tính chia nhóm là 
thuộc tính “Phai”. 
Câu hỏi 21: Cho biết số lượng nhân viên theo từng phòng? 
Do cột MaPH có 3 giá trị “NC” và “DH” và “QL”, trường hợp này ta chia 
bảng nhân viên thành 3 nhóm nhỏ. Thuộc tính chia nhóm là thuộc tính 
“MaPH”. 
Tương tự: cho biết tổng lương của mỗi phòng, cho biết mức lương thấp 
nhất của từng phòng, mức lương cao nhất, mức lương trung bình của 
từng phòng 
 GV Thiều Quang Trung 18 
 Câu hỏi có MỆNH ĐỀ GROUP BY 
Câu hỏi 22: Cho biết tên phòng và số lượng nhân viên theo từng phòng? 
 Giống câu 21 nhưng bổ sung thêm bảng PhongBan để lấy tên 
 phòng. Thuộc tính chia nhóm là (TenPH) thay cho MaPH. 
 Câu hỏi 23: Với mỗi phòng, cho biết số lượng nhân viên theo 
 từng phái? 
 Do cột MaPH có 3 giá trị “NC” và “DH” và “QL”, mỗi phòng chia 
 nhỏ theo từng phái: 2 nhóm “Nam” và “Nữ”, trường hợp này ta 
 chia bảng nhân viên thành 6 nhóm nhỏ. Như vậy, tập thuộc tính 
 chia nhóm cho câu truy vấn là (MaPH, Phai). 
 GV Thiều Quang Trung 19 
 Câu hỏi có MỆNH ĐỀ GROUP BY 
Câu hỏi 24: Đếm số đề án của từng nhân viên tham gia? 
- Do cột MaNV có 7 giá trị “NV001”,”NV008” (không có nhân 
viên “005”), trường hợp này ta chia bảng PhanCong thành 7 
nhóm nhỏ. Với mỗi nhóm nhỏ (MaNV), ta đếm số đề án 
(count(MADA)) tham gia. Thuộc tính chia nhóm là thuộc tính 
“MaNV”. 
- Tương tự: tính tổng số giờ làm việc của mỗi nhân viên (SUM), 
thời gian làm việc thấp nhất của mỗi nhân viên (MIN), thời gian 
làm việc lớn nhất của mỗi nhân viên (MAX), thời gian làm việc 
trung bình, 
 Câu hỏi 25: Cho biết mã, tên nhân viên và số đề án mà n/v đã tham 
 gia? 
 GV Thiều Quang Trung 20 
 Câu hỏi có MỆNH ĐỀ HAVING 
. Lọc kết quả theo điều kiện, sau khi đã gom nhóm 
. Điều kiện của HAVING là điều kiện về các hàm 
 tính toán trên nhóm (Count, Sum, Min, Max, AVG) 
 và các thuộc tính trong danh sách GROUP BY. 
 Câu hỏi 26: Cho biết những nhân viên tham gia từ 2 đề án trở lên? 
 Câu hỏi 27: Cho biết mã phòng ban có trên 4 nhân viên? 
 GV Thiều Quang Trung 21 
GV Thiều Quang Trung 22