Bài giảng Cơ sở dữ liệu - Chương VI: Ngôn ngữ truy vấn SQL - Đỗ Thị Kim Thành

01/03/2011 1 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 GV: Đỗ Thị Kim Thành Email: kimthanh.do@gmail.com Web: DATABASE CƠ SỞ DỮ LIỆU GV: ĐỖ THỊ KIM THÀNH 2 Chương VI NGÔN NGỮ TRUY VẤN SQL 01/03/2011 2 GV: ĐỖ THỊ KIM THÀNH 3 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 4 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 01/03/2011 3 GV: ĐỖ THỊ KIM THÀNH 5 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 6 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, ] 01/03/2011 4 GV: ĐỖ THỊ KIM THÀNH 7 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 8 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( ) 01/03/2011 5 GV: ĐỖ THỊ KIM THÀNH 9 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 10 TRUY VẤN CƠ BẢN  Cú pháp:  : 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 SELECT FROM WHERE 01/03/2011 6 GV: ĐỖ THỊ KIM THÀNH 11 TRUY VẤN CƠ BẢN (tt)  SQL và ĐSQH SELECT FROM WHERE π σ  SELECT L FROM R WHERE C πL (σC(R)) GV: ĐỖ THỊ KIM THÀNH 12 CSDL MẪU 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) Cho lược đồ CSDL “quản lý đề án công ty” như sau 01/03/2011 7 GV: ĐỖ THỊ KIM THÀNH 13 VÍ DỤ SELECT * FROM NHANVIEN WHERE PHG=5 Lấy tất cả các cột của quan hệ kết quả 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 14 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)) 01/03/2011 8 GV: ĐỖ THỊ KIM THÀNH 15 MỆNH ĐỀ SELECT (TT) 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))) Tên bí danh GV: ĐỖ THỊ KIM THÀNH 16 MỆNH ĐỀ SELECT (TT) 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))) Mở rộng 01/03/2011 9 GV: ĐỖ THỊ KIM THÀNH 17 MỆNH ĐỀ SELECT (TT) SELECT LUONG FROM NHANVIEN WHERE PHG=5 AND PHAI=„Nam‟ LUONG 30000 25000 30000 38000 Loại bỏ các dòng trùng nhau LUONG 30 250 38 DISTINCT LUONG -Tốn chi phí GV: ĐỖ THỊ KIM THÀNH 18 VÍ DỤ SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE TENPHG=„Nghien cuu‟ AND PHG=MAPHG R1  NHANVIEN PHG=MAPHG PHONGBAN KQ  π MANV,TENNV (σTENPHG=„Nghien cuu‟ (R1)) 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) 01/03/2011 10 GV: ĐỖ THỊ KIM THÀNH 19 Mệnh đề WHERE SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE TENPHG=„Nghien cuu‟ AND PHG=MAPHG Biểu thức luận lý TRUE TRUE TRUE GV: ĐỖ THỊ KIM THÀNH 20 Mệnh đề WHERE (TT) SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG > 20000 AND LUONG < 30000 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 BETWEEN 20000 AND 30000 01/03/2011 11 GV: ĐỖ THỊ KIM THÀNH 21 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 22 Mệnh đề WHERE (TT) SELECT MANV, TENNV FROM NHANVIEN WHERE DC LIKE „Nguyễn_ _ _ _‟ 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 %‟ Ký tự bất kỳ Chuỗi bất kỳ 01/03/2011 12 GV: ĐỖ THỊ KIM THÀNH 23 Mệnh đề WHERE (TT) SELECT MANV, TENNV FROM NHANVIEN WHERE HONV LIKE „Nguyễn‟ NOTLIKE Hãy cho biết MANV và TENNV có họ (HONV) Nguyễn SELECT MANV, TENNV FROM NHANVIEN WHERE HOVN NOT LIKE „Nguyễn‟ Hãy cho biết MANV và TENNV không phải họ Nguyễn GV: ĐỖ THỊ KIM THÀNH 24 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‟ 01/03/2011 13 GV: ĐỖ THỊ KIM THÀNH 25 Mệnh đề WHERE (TT)  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 NULL GV: ĐỖ THỊ KIM THÀNH 26 Mệnh đề WHERE (TT) SELECT MANV, TENNV FROM NHANVIEN WHERE MA_NQL IS NULL 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 NOT NULL Hãy cho biết MANV và TENNV có người quản lý 01/03/2011 14 GV: ĐỖ THỊ KIM THÀNH 27 Mệnh đề FROM SELECT TENPHG, DIADIEM FROM PHONGBAN, DIADIEM_PHG WHERE MAPHG = MAPHG 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 AS PB, DIADIEM_PHG AS DD WHERE PB.MAPHG = DD.MAPHG GV: ĐỖ THỊ KIM THÀNH 28 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 01/03/2011 15 GV: ĐỖ THỊ KIM THÀNH 29 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 BETWEEN, ORDER BY, IS NULL GV: ĐỖ THỊ KIM THÀNH 30 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‟) 01/03/2011 16 GV: ĐỖ THỊ KIM THÀNH 31 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 32 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) 01/03/2011 17 GV: ĐỖ THỊ KIM THÀNH 33 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 34 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 01/03/2011 18 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 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 36 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 01/03/2011 19 GV: ĐỖ THỊ KIM THÀNH 37 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 38 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 ) 01/03/2011 20 GV: ĐỖ THỊ KIM THÀNH 39 – 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 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 GV: ĐỖ THỊ KIM THÀNH 40 MỆNH ĐỀ GROUP BY n h ó m Các thuộc tính GROUP BY: Q a a b b c c c c c d d d Chia các dòng thành các nhóm dựa trên tập thuộc tính chia nhóm Q Count(S) Q S a b c d 2 2 5 3 10 2 9 5 10 8 6 4 10 16 Câu SQL: Select Q, count(S) From NV Group by Q Quan hệ NV 18 50 Tương tự cho các hàm SUM, MIN, MAX, AVG 01/03/2011 21 GV: ĐỖ THỊ KIM THÀNH 41 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? SELECT PHAI, COUNT(MANV) AS SONV FROM NHANVIEN GROUP BY PHAI MỆNH ĐỀ GROUP BY 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”. GV: ĐỖ THỊ KIM THÀNH 42 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? SELECT MAPHG, COUNT(MANV) FROM NHANVIEN GROUP BY MAPHG MỆNH ĐỀ GROUP BY 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”. 01/03/2011 22 GV: ĐỖ THỊ KIM THÀNH 43 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 44 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 01/03/2011 23 GV: ĐỖ THỊ KIM THÀNH 45 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