Bài giảng Cơ sở dữ liệu - Chương 6: Ngôn ngữ truy vấn SQL

Nội dung text: Bài giảng Cơ sở dữ liệu - Chương 6: Ngôn ngữ truy vấn SQL

1. 01/03/2011 TRUNG TÂM DÀO TẠO CÁC VẤN ĐỀ KINH TẾ HIỆN ĐẠI TRƯỜNG ĐẠI HỌC CHU VĂN AN DATABASE CƠ SỞ DỮ LIỆU GV: Đỗ Thị Kim Thành Email: kimthanh.do@gmail.com Web: Chương VI NGÔN NGỮ TRUY VẤN SQL GV: ĐỖ THỊKIM THÀNH 2 1
2. 01/03/2011 GIỚI THIỆU  Ngôn ngữ khai báo • Cài đặt dựa trên ĐSQH  Chuẩn hóa cho các hệ quản trị CSDL quan hệ • Được phát triển bởi IBM (1970s) • Các phiên bản chuẩn ANSI/ISO – SQL – 86 (SQL1) – SQL – 92 (SQL2) – SQL – 99 (SQL3) – SQL – 2000, 2005, 2008  Nhiều phiên bản cài đặt • SQL Server • Oracle GV: ĐỖ THỊKIM THÀNH 3 GIỚI THIỆU  SQL hỗ trợ . Ngôn ngữ định nghĩa dữ liệu (DDL) • Mức quan niệm: CREATE SCHEMA, TABLE, • Mức ngoài: CREATE VIEW, GRANT, • Mức trong: CREATE INDEX, CLUSTER, . Ngôn ngữ thao tác dữ liệu (DML) • Truy vấn: SELECT • Cập nhật: INSERT, DELETE, UPDATE . Ngôn ngữ khai báo • Ràng buộc toàn vẹn • Phân quyền và bảo mật • Điều khiển giao tác GV: ĐỖ THỊKIM THÀNH 4 2
3. 01/03/2011 TRUY VẤN DỮ LIỆU  Truy vấn dữ liệu là ngôn ngữ rút trích dữ liệu thỏa một số điều kiện nào đó  Dựa trên các phép toán đại số quan hệ + một số mở rộng  Hỗ trợ các truy vấn: • Cơ bản: chọn, chiếu, kết • 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ố kiểu truy vấn khác  Ngôn ngữ truy vấn dữ liệu 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ệ. GV: ĐỖ THỊKIM THÀNH 5 CẤU TRÚC . 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: ĐỖ THỊKIM THÀNH 6 3
4. 01/03/2011 CÁC TOÁN TỬ VÀ PHÉP TOÁ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: +, - ,* , / GV: ĐỖ THỊKIM THÀNH 7 CÁC HÀM CƠ BẢN . 5 hàm cơ bản: • COUNT( ) • SUM( ) • MAX( ) • MIN( ) • AVG( ) . Các hàm xử lý ngày tháng năm • Ngày: DAY( ) • Tháng: MONTH( ) • Năm: YEAR( ) GV: ĐỖ THỊKIM THÀNH 8 4
5. 01/03/2011 PHÂN LOẠI CÂU SELECT  SELECT đơn giản  SELECT có mệnh đề ORDER BY  SELECT lồng (câu SELECT lồng câu SELECT khác)  SELECT gom nhóm (GROUP BY)  SELECT gom nhóm (GROUP BY) có điều kiện HAVING GV: ĐỖ THỊKIM THÀNH 9 TRUY VẤN CƠ BẢN  Cú pháp: SELECT FROM WHERE . : tên các cột cần được hiển thị trong kết quả truy vấn . : tên các bảng (quan hệ) liên quan đến câu truy vấn . • Biểu thức logic xác định dòng nào sẽ được rút trích • Nối các biểu thức: dùng toán tử logic • Các phép toán và toán tử so sánh GV: ĐỖ THỊKIM THÀNH 10 5
6. 01/03/2011 TRUY VẤN CƠ BẢN (tt)  SQL và ĐSQH π SELECT  FROM σ WHERE SELECT L FROM R πL (σC(R)) WHERE C GV: ĐỖ THỊKIM THÀNH 11 CSDL MẪU Cho lược đồ CSDL “quản lý đề án công ty” như sau NHANVIEN (MANV, HONV, TENLOT, TENNV, NS, DC, PHAI, LUONG, MA_NQL, PHG) PHONGBAN (MAPHG, TENPHG, TRPHG, NG_NHANCHUC) PHANCONG (MA_NVIEN, SODA, THOIGIAN) THANNHAN (MA_NVIEN, TENTN, PHAI, NS, QUANHE) DEAN (MADA, TENDA, DDIEM_DA, PHONG) DIADIEM_PHG (MAPHG, DIADIEM) GV: ĐỖ THỊKIM THÀNH 12 6
7. 01/03/2011 VÍ DỤ SELECT * Lấy tất cả các cột của FROM NHANVIEN quan hệ kết quả WHERE PHG=5 MANV HONV TENLOT TENNV NS DC PHAI LUONG MA_NQL PHG 123 Nguyễn Thanh Tùng 8/12/1985 NVC, Q5 Nam 40000 745 5 189 Nguyễn Mạnh Hùng 15/9/1962 VT Nam 38000 123 5 248 Lê Thị Hoa 4/9/1978 TN Nữ 36000 123 5 σPHG=5 (NHANVIEN) GV: ĐỖ THỊKIM THÀNH 13 MỆNH ĐỀ SELECT SELECT MANV, HOTEN, TENLOT, TENNV FROM NHANVIEN WHERE PHG=5 AND PHAI=„Nam‟ MANV HONV TENLOT TENNV 123 Nguyễn Thanh Tùng 189 Nguyễn Mạnh Hùng πMANV,HONV,TENLOT,TENNV (σPHG=5^PHAI=„Nam‟ (NHANVIEN)) GV: ĐỖ THỊKIM THÀNH 14 7
8. 01/03/2011 MỆNH ĐỀ SELECT (TT) Tên bí danh SELECT MANV, HONV AS HO, TENLOT AS „TEN LOT‟, TENNV AS TEN FROM NHANVIEN WHERE PHG=5 AND PHAI=„Nam‟ MANV HO TEN LOT TEN 123 Nguyễn Thanh Tùng 189 Nguyễn Mạnh Hùng ρMANV, HO, TEN LOT, TEN (πMANV, HONV, TENLOT, TENNV (σPHG=5 ^ PHAI=„Nam‟ (NHANVIEN))) GV: ĐỖ THỊKIM THÀNH 15 MỆNH ĐỀ SELECT (TT) Mở rộng SELECT MANV, HONV+ „‟+ TENLOT + „‟ +TENNV AS HOTEN FROM NHANVIEN WHERE PHG=5 AND PHAI=„Nam‟ MANV HOTEN 123 Nguyễn Thanh Tùng 189 Nguyễn Mạnh Hùng ρMANV,HOTEN (πMANV,HONV+TENLOT+TENNV (σPHG=5^PHAI=„Nam‟ (NHANVIEN))) GV: ĐỖ THỊKIM THÀNH 16 8
9. 01/03/2011 MỆNH ĐỀ SELECT (TT) Loại bỏ các dòng trùng nhau SELECT LUONGDISTINCT LUONG FROM NHANVIEN WHERE PHG=5 AND PHAI=„Nam‟ LUONGLUONG 3000030000 2500025000 -Tốn chi phí 3800030000 38000 GV: ĐỖ THỊKIM THÀNH 17 VÍ DỤ Cho biết MANV và TENNV làm việc ở phòng „Nghien cứu‟ NHANVIEN (MANV, HONV, TENLOT, TENNV, NS, DC, PHAI, LUONG, MA_NQL, PHG) PHONGBAN (MAPHG, TENPHG, TRPHG, NG_NHANCHUC) R1  NHANVIEN PHG=MAPHG PHONGBAN KQ  π MANV,TENNV (σTENPHG=„Nghien cuu‟ (R1)) SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE TENPHG=„Nghien cuu‟ AND PHG=MAPHG GV: ĐỖ THỊKIM THÀNH 18 9
10. 01/03/2011 Mệnh đề WHERE SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE TENPHG=„Nghien cuu‟ AND PHG=MAPHG TRUE Biểu thức luận lý TRUE TRUE GV: ĐỖ THỊKIM THÀNH 19 Mệnh đề WHERE (TT) BETWEEN Hãy cho biết MANV và TENNV có lương trong khoảng tử 20000 đến 30000 SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG > 20000 AND LUONG < 30000 SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG BETWEEN 20000 AND 30000 GV: ĐỖ THỊKIM THÀNH 20 10
11. 01/03/2011 Mệnh đề WHERE (TT) NOT BETWEEN Hãy cho biết MANV và TENNV có lương không nằm trong khoảng tử 20000 đến 30000 SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG NOTBETWEEN 20000 AND 30000 GV: ĐỖ THỊKIM THÀNH 21 Mệnh đề WHERE (TT) LIKE Hãy cho biết MANV và TENNV có địa chỉ trong đó có từ Nguyễn SELECT MANV, TENNV FROM NHANVIEN WHERE DC LIKE „Nguyễn_ _ _ _‟ SELECT MANV, TENNV Ký tự bất kỳ FROM NHANVIEN WHERE DC LIKE „Nguyễn %‟ Chuỗi bất kỳ GV: ĐỖ THỊKIM THÀNH 22 11
12. 01/03/2011 Mệnh đề WHERE (TT) NOTLIKE Hãy cho biết MANV và TENNV có họ (HONV) Nguyễn SELECT MANV, TENNV FROM NHANVIEN WHERE HONV LIKE „Nguyễn‟ Hãy cho biết MANV và TENNV không phải họ Nguyễn SELECT MANV, TENNV FROM NHANVIEN WHERE HOVN NOT LIKE „Nguyễn‟ GV: ĐỖ THỊKIM THÀNH 23 Mệnh đề WHERE (TT) Ngày giờ Hãy cho biết MANV và TENNV có ngày sinh nằm trong khoảng tử 1955-12-08 đến 1966-07-19 SELECT MANV, TENNV FROM NHANVIEN WHERE NS BETWEEN „1955-12-08‟ AND „1966-07-19‟ „1955-12-08‟ YYYY-MM-DD „17:30:00‟ „12/08/1955‟ MM/DD/YYYY „05:30 PM‟ „December 8, 1955‟ „1955-12-08 17:30:00‟ GV: ĐỖ THỊKIM THÀNH 24 12
13. 01/03/2011 Mệnh đề WHERE (TT) NULL  Sử dụng trong truờng hợp • Không biết (value unknown) • Không thể áp dụng (value inapplicable) • Không tồn tại (value withheld)  Những biểu thức tính toán có liên quan đến giá trị NULL sẽ cho ra kết quả là NULL • x có giá trị là NULL • x + 3 cho ra kết quả là NULL • x + 3 là 1 biểu thức không hợp lệ trong SQL  Những biểu thức so sánh có liên quan đến giá trị NULL sẽ cho ra kết quả là UNKNOWN • x = 3 cho ra kết quả là UNKNOWN • x = 3 là 1 so sánh không hợp lệ trong SQL GV: ĐỖ THỊKIM THÀNH 25 Mệnh đề WHERE (TT) NULL Hãy cho biết MANV và TENNV không có người quản lý SELECT MANV, TENNV FROM NHANVIEN WHERE MA_NQL IS NULL Hãy cho biết MANV và TENNV có người quản lý SELECT MANV, TENNV FROM NHANVIEN WHERE MA_NQL IS NOT NULL GV: ĐỖ THỊKIM THÀNH 26 13
14. 01/03/2011 Mệnh đề FROM Tên bídanh PHONGBAN(TENPHG, MAPHG,TRPHG, NG_NHANCHUC) DIADIEM_PHG(MAPHG, DIADIEM) Hãy cho biết địa điểm, tên phòng của từng phòngban SELECT TENPHG, DIADIEM FROM PHONGBAN, DIADIEM_PHG WHERE MAPHG = MAPHG SELECT TENPHG, DIADIEM FROM PHONGBAN AS PB, DIADIEM_PHG AS DD WHERE PB.MAPHG = DD.MAPHG GV: ĐỖ THỊKIM THÀNH 27 BETWEEN, ORDER BY, IS NULL Câu hỏi 13: Sử dụng =,>,>=, Danh sách các nhân viên sinh trong khoảng từ năm 1978 đến 1983? SELECT MANV, HONV, TENLOT, TENNV FROM NHANVIEN WHERE YEAR(NS)>=1978 AND YEAR(NS)<=1983 GV: ĐỖ THỊKIM THÀNH 28 14
15. 01/03/2011 BETWEEN, ORDER BY, IS NULL Câu hỏi 14: Sử dụng BETWEEN, ORDER BY. Danh sách các nhân viên sinh trong khoảng từ năm 1978 đến 1983? Sắp xếp theo mức lương giảm dần. SELECT * FROM NHANVIEN WHERE YEAR(NS) BETWEEN 1978 AND 1983 ORDER BY LUONG DESC GV: ĐỖ THỊKIM THÀNH 29 SO SÁNH IN & NOT IN Câu hỏi 15: 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‟? SELECT DISTINCT HOTEN FROM NHANVIEN WHERE MAPHG IN („NC‟,‟DH‟) GV: ĐỖ THỊKIM THÀNH 30 15
16. 01/03/2011 SO SÁNH IN & NOT IN Câu hỏi 16: 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 thuộc phòng „NC‟ hoặc phòng „DH‟? SELECT HONV, TENLOT, TENNV FROM NHANVIEN WHERE MAPH IN (SELECT MAPHG FROM PHONGBAN WHERE MAPHG=„NC‟ OR MAPHG=„DH‟) GV: ĐỖ THỊKIM THÀNH 31 SO SÁNH IN & NOT IN Câu hỏi 17: : 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ù SELECT MANV, HOTEN, NS FROM NHANVIEN WHERE MANV NOT IN (SELECT MANV FROM PHANCONG) GV: ĐỖ THỊKIM THÀNH 32 16
17. 01/03/2011 SO SÁNH IN & NOT IN Câu hỏi 18: 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ù. SELECT TENPH FROM PHONGBAN WHERE MAPHG NOT IN (SELECT DISTINCT PHONG FROM DEAN WHERE NAMTHUCHIEN=2005) GV: ĐỖ THỊKIM THÀNH 33 HÀM COUNT,SUM,MAX,MIN,AVG 1. 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 19: Tính số nhân viên của công ty SELECT COUNT(MANV) AS SONV FROM NHANVIEN GV: ĐỖ THỊKIM THÀNH 34 17
18. 01/03/2011 HÀM COUNT,SUM,MAX,MIN,AVG 1. 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 20: Tính số lượng nhân viên quản lý trực tiếp nhân viên khác SELECT COUNT (DISTINCT MA_NQL) FROM NHANVIEN GV: ĐỖ THỊKIM THÀNH 35 HÀM COUNT,SUM,MAX,MIN,AVG 1. 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 21: 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 SELECT MAX(LUONG), AVG(LUONG), SUM(LUONG) FROM NHANVIEN GV: ĐỖ THỊKIM THÀNH 36 18
19. 01/03/2011 HÀM COUNT,SUM,MAX,MIN,AVG 1. 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 22: Cho biết nhân viên có mức lương lớn nhất SELECT HOTEN FROM NHANVIEN WHERE LUONG = (SELECT MAX (LUONG) FROM NHANVIEN ) GV: ĐỖ THỊKIM THÀNH 37 MỆNH ĐỀ GROUP BY Câu hỏi 23: 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. SELECT HOTEN FROM NHANVIEN WHERE LUONG > (SELECT AVG(LUONG) FROM NHANVIEN ) GV: ĐỖ THỊKIM THÀNH 38 19
20. 01/03/2011 MỆNH ĐỀ GROUP BY 2. 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 như: . COUNT : thực hiện phép đếm . SUM : tính tổng . MIN : lấy giá trị nhỏ nhất . MAX : lấy giá trị lớn nhất . AVG : lấy giá trị trung bình GV: ĐỖ THỊKIM THÀNH 39 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 tính chia nhóm a 2 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) Các thuộc tính GROUP BY: Q From NV Group by Q GV: ĐỖ THỊKIM THÀNH 40 20
21. 01/03/2011 MỆNH ĐỀ GROUP BY 2. Sử dụng các hàm COUNT, SUM, MIN, MAX, AVG trên từng nhóm nhỏ: mệnh đề GROUP BY Câu hỏi 23: 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”. SELECT PHAI, COUNT(MANV) AS SONV FROM NHANVIEN GROUP BY PHAI GV: ĐỖ THỊKIM THÀNH 41 MỆNH ĐỀ GROUP BY 2. Sử dụng các hàm COUNT, SUM, MIN, MAX, AVG trên từng nhóm nhỏ: mệnh đề GROUP BY Câu hỏi 24: Cho biết số lượng nhân viên theo từng phòng? Do cột MAPHG 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 “MAPHG”. SELECT MAPHG, COUNT(MANV) FROM NHANVIEN GROUP BY MAPHG GV: ĐỖ THỊKIM THÀNH 42 21
22. 01/03/2011 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. GV: ĐỖ THỊKIM THÀNH 43 MỆNH ĐỀ GROUP BY Câu hỏi 25: Cho biết những nhân viên tham gia từ 2 đề án trở lên? SELECT MANV, COUNT(MADA) AS SODATG FROM PHANCONG GROUP BY MANV HAVING COUNT(MADA) >=2 GV: ĐỖ THỊKIM THÀNH 44 22
23. 01/03/2011 MỆNH ĐỀ GROUP BY Câu hỏi 26: Cho biết số nhân viên có mã phòng ban có trên 4 nhân viên? SELECT MAPHG, COUNT(MANV) FROM NHANVIEN GROUP BY MAPHG HAVING COUNT(MANV) > 4 GV: ĐỖ THỊKIM THÀNH 45 23