Bài giảng Cơ sở dữ liệu - Chương 3: SQL nâng cao

9/3/2018 1 SQL NÂNG CAO CƠ SỞ DỮ LIỆU Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u GIÁ TRỊ NULL • Null là giá trị đặc biệt, không phải là hằng số, mà là giá trị không biết Định nghĩa • Các phép toán số học liên quan tới Null cho giá trị là Null • Các phép toán so sánh liên quan tới Null cho giá trị là Unknown • Phép toán so sánh IS NULL và IS NOT NULL Đặc trưng • Liệt kê danh sách nhân viên không có lãnh đạo trực tiếp Ví dụ SELECT FROM WHERE EMPLOYEE supervisorSSN IS NULL * Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u TOÁN TỬ AS • Được sử dụng để đổi tên cột hiển thị trong phần kết quả của câu truy vấn mà không ảnh hưởng đến bản thân các quan hệ trong mệnh đề WHERE của câu truy vấn đó Toán tử AS trong SQL • Liệt kê danh sách nhân viên và tên lãnh đạo trực tiếp Ví dụ SELECT E.EName AS ‘Tên nhân viên’, S.EName AS ‘Tên lãnh đạo trực tiếp’ FROM EMPLOYEE E, EMPLOYEE S WHERE E.supervisorSSN=S.ESSN 9/3/2018 2 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u TRUY VẤN LỒNG • Câu lệnh truy vấn chứa câu lệnh truy vấn khác bên trong các thành phần của nó Định nghĩa • Kết quả trả về của câu truy vấn này được sử dụng trong một mệnh đề của câu truy vấn khác • Kết quả trả về của một câu truy vấn có thể là một giá trị đơn hoặc một quan hệ với nhiều bộ, nhiều thuộc tính • Một số toán tử so sánh: IN, NOT IN, ANY, SOME, ALL Đặc trưng Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u TRUY VẤN LỒNG • Cho biết danh sách các dự án có sự tham gia của nhân viên tên là ‘Lê Duy Anh’ Ví dụ SELECT FROM WHERE PROJECT p, EMPLOYEE e, WORKSON w p.ProNum = w.PNum AND w.ESSN = e.ESSN AND e.EName = N’Lê Duy Anh’ p.* ? Độ phức tạp của truy vấn Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u TRUY VẤN LỒNG • Cho biết danh sách các dự án có sự tham gia của nhân viên tên là ‘Lê Duy Anh’ Ví dụ ? Độ phức tạp của truy vấn (SELECT w.PNum FROM WORKSON w, EMPLOYEE e WHERE w.ESSN=e. ESSN AND e.EName = N’Lê Duy Anh’) SELECT * FROM PROJECT WHERE PNumber IN 9/3/2018 3 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u TRUY VẤN LỒNG • Cho biết danh sách các dự án có sự tham gia của nhân viên tên là ‘Lê Duy Anh’ Ví dụ ? Độ phức tạp của truy vấn (SELECT w.PNum FROM WORKSON w WHERE w.ESSN IN ) SELECT * FROM PROJECT WHERE PNumber IN (SELECT e.ESSN FROM EMPLOYEE e WHERE e.EName = N’’) Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u TRUY VẤN LỒNG • Được sử dụng để kiểm tra kết quả của câu truy vấn lồng xem có rỗng hay không Hàm EXISTS trong SQL • Cho biết tên các nhân viên chưa tham gia bất kỳ dự án nào của công ty Ví dụ SELECTEName FROM EMPLOYEE E WHERE NOT EXISTS (SELECT * FROM WOKRSON WHERE E.ESSN=ESSN) Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u TRUY VẤN LỒNG • Được sử dụng để kiểm tra kết quả của câu truy vấn lồng xem có rỗng hay không • Có thể được viết lại bằng cách sử dụng toán tử so sánh IN Hàm EXISTS trong SQL • Cho biết tên các nhân viên chưa tham gia bất kỳ dự án nào của công ty Ví dụ SELECTEName FROM EMPLOYEE WHERE ESSN NOT IN (SELECT ESSN FROM WOKRSON) 9/3/2018 4 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BẢNG KẾT TRONG SQL • Phép kết thể hiện thông qua việc sử dụng nhiều quan hệ trong mệnh đề FROM và các điều kiện kết tương ứng trong mệnh đề WHERE • Phép kết được thực hiện ngay trong mệnh đề FROM thông qua việc sử dụng các toán tử INNER JOIN, OUTER JOIN đi kèm điều kiện kết tương ứng Giới thiệu Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BẢNG KẾT • Hiển thị tên và mức lương của các nhân viên trong phòng ‘Nghiên cứu’ • Sử dụng phép nhân chéo và phép chọn: • Sử dụng bảng kết: Ví dụ SELECTEName, ESalary FROM EMPLOYEE, DEPARTMENT WHERE DNum = DNumber AND DName=N’Phòng Nghiên cứu’ SELECTEName, ESalary FROM EMPLOYEE JOIN DEPARTMENT ON DNum = DNumber WHERE DName=N’Phòng Nghiên cứu’ Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BẢNG KẾT Ví dụ SELECTEName, ESalary FROM EMPLOYEE, DEPARTMENT WHERE DNum = DNumber AND DName=N’Phòng Nghiên cứu’ SELECTEName, ESalary FROM EMPLOYEE JOIN DEPARTMENT ON DNum = DNumber WHERE DName=N’Phòng Nghiên cứu’ 9/3/2018 5 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BẢNG KẾT • Với mỗi nhân viên hãy hiển thị tên của người đó và tên của lãnh đạo trực tiếp của họ • Sử dụng phép nhân chéo và phép chọn: • Sử dụng bảng kết: Ví dụ SELECTe.EName as ‘Employee’, s.EName as ‘Supervisor’ FROM EMPLOYEE e, EMPLOYEE s WHERE e.supervisorSSN = s.ESSN SELECT e.EName as ‘Employee’, s.EName as ‘Supervisor’ FROM EMPLOYEE e JOIN EMPLOYEE s ON e.supervisorSSN = s.ESSN Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BẢNG KẾT Ví dụ SELECTe.EName as ‘Employee’, s.EName as ‘Supervisor’ FROM EMPLOYEE e, EMPLOYEE s WHERE e.supervisorSSN = s.ESSN SELECT e.EName as ‘Employee’, s.EName as ‘Supervisor’ FROM EMPLOYEE e JOIN EMPLOYEE s ON e.supervisorSSN = s.ESSN Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BẢNG KẾT • Với mỗi nhân viên hãy hiển thị tên của người đó và tên của lãnh đạo trực tiếp của họ • Tuy nhiên, trong bảng EMPLOYEE có tất cả 21 bộ với 15 bộ có giá trị khác NULL tại thuộc tính supervisor ứng với 15 bộ trả về bởi câu truy vấn • Làm thế nào để hiển thị hết đầy đủ 21 bộ của quan hệ EMPLOYEE theo yêu cầu đề bài? Ví dụ SELECT e.EName as ‘Employee’, s.EName as ‘Supervisor’ FROM EMPLOYEE e JOIN EMPLOYEE s ON e.supervisorSSN = s.ESSN 9/3/2018 6 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BẢNG KẾT • Với mỗi nhân viên hãy hiển thị tên của người đó và tên của lãnh đạo trực tiếp của họ • Sử dụng bảng kết trong: • Sử dụng bảng kết ngoài Ví dụ SELECT e.EName as ‘Employee’, s.EName as ‘Supervisor’ FROM EMPLOYEE e JOIN EMPLOYEE s ON e.supervisorSSN = s.ESSN SELECT e.EName as ‘Employee’, s.EName as ‘Supervisor’ FROM EMPLOYEE e LEFT JOIN EMPLOYEE s ON e.supervisorSSN = s.ESSN Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BẢNG KẾT Ví dụ SELECT e.EName as ‘Employee’, s.EName as ‘Supervisor’ FROM EMPLOYEE e LEFT JOIN EMPLOYEE s ON e.supervisorSSN = s.ESSN Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BẢNG KẾT • Với mỗi dự án tại ‘TP. Hồ Chí Minh’, liệt kê số hiệu dự án, số hiệu đơn vị quản lý dự án, và tên, ngày sinh của trưởng đơn vị quản lý dự án Ví dụ SELECT PNumber, DNumber, EName, EBirthdate FROM ((PROJECT JOIN DEPARTMENT ON dNum=DNumber) JOIN EMPLOYEE ON mgrSSN=ESSN) WHERE PLocation = N’TP Hồ Chí Minh’ 9/3/2018 7 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u CÁC HÀM THỐNG KÊ TRONG SQL • Các hàm số học được thực hiện theo từng nhóm • Các hàm MAX, MIN, AVG, SUM thực hiện trên một thuộc tính số học • Hàm COUNT thực hiện trên một hoặc nhiều thuộc tính Các hàm thống kê (kết hợp) • Cho biết tổng lương, lương trung bình của nhân viên trong công ty Ví dụ SELECTSUM(ESalary) AS ‘Tổng lương’, AVG(ESalary) AS ‘Lương trung bình’ FROM EMPLOYEE Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u CÁC HÀM THỐNG KÊ TRONG SQL • Cho biết tổng lương, mức lương cao nhất, mức lương thấp nhất, và của các nhân viên tham gia dự án có mã số là 1 Ví dụ SELECTSUM(E.ESalary), MAX(E.ESalary), MIN(E.ESalary) FROM (EMPLOYEE E JOIN WORKSON W ON E.ESSN=W.ESSN) WHERE W.PNUM = 1 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u MỆNH ĐỀ GOM NHÓM • Gom các bộ trong quan hệ thành từng nhóm theo một (số) thuộc tính • Các bộ có giá trị bằng nhau tại (các) thuộc tính gom nhóm sẽ được xếp ở gần nhau • Điều kiện để chọn các nhóm được đặt trong mệnh đề HAVING • Các hàm kết hợp được áp dụng cho các nhóm sau khi đã gom GROUP BY và HAVING trong SQL 9/3/2018 8 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u MỆNH ĐỀ GOM NHÓM • Với mỗi phòng ban hãy cho biết mã số phòng ban, số nhân viên thuộc quyền quản lý của phòng ban đó, và mức lương trung bình của họ Ví dụ SELECT DNum, COUNT(*), AVG(ESalary) FROM EMPLOYEE GROUP BY DNum Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u MỆNH ĐỀ GOM NHÓM • Với mỗi dự án, cho biết mã số dự án, tên dự án, số nhân viên tham gia dự án đó Ví dụ SELECT p. PNumber, p. PName, COUNT(*) FROM PROJECT p, WORKSON w WHERE p.PNumber = w.PNum GROUP BY p. PNumber, p.PName Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u MỆNH ĐỀ GOM NHÓM • Với mỗi dự án có nhiều hơn hai nhân viên tham gia, cho biết mã số dự án, tên dự án, và số nhân viên tham gia dự án đó Ví dụ SELECT p.PNumber, p.PName, COUNT(*) FROM PROJECT p, WORKSON w WHERE p.PNumber = w.PNum GROUP BY p.PNumber, p.PName HAVING COUNT(*) > 2 9/3/2018 9 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u CÂU TRUY VẤN SQL TỔNG QUÁT Công thức chung của câu truy vấn SQL SELECT FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ]