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

Nội dung text: Bài giảng Cơ sở dữ liệu - Chương 6: SQL nâng cao

1. 9/5/2016 CƠ SỞ DỮ LIỆU SQL NÂNG CAO GIÁ TRỊ NULL Định nghĩa • Null là giá trị đặc biệt, không phải là hằng số, mà là giá trị không biết Đặc trưng • 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 Tổng quan hệ thống cơ sở dữ liệu Ví dụ – • Liệt kê danh sách nhân viên không có lãnh đạo trực tiếp SELECT * FROM EMPLOYEE WHERE supervisorSSN IS NULL Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] TOÁN TỬ AS Toán tử AS trong SQL • Đượ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 đó Ví dụ Tổng quan hệ thống cơ sở •dữ liệuLiệt kê danh sách nhân viên và tên lãnh đạo trực tiếp – 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 Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] 1
2. 9/5/2016 TRUY VẤN LỒNG Định nghĩa • 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ó Đặc trưng • 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 Tổng quan hệ thống cơ sở dữ liệu – • 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ơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] TRUY VẤN LỒNG Ví dụ • 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’ SELECT p.* FROM PROJECT p, EMPLOYEE e, WORKSON w WHERE p.ProNum = w.PNum AND w.ESSN = e.ESSN AND e.EName = N’Lê Duy Anh’ Tổng quan hệ thống cơ sở dữ liệu – ? Độ phức tạp của truy vấn Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] TRUY VẤN LỒNG Ví dụ • 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’ SELECT * FROM PROJECT WHERE PNumber IN (SELECT w.PNum FROM WORKSON w, EMPLOYEE e WHERE w.ESSN=e. ESSN Tổng quan hệ thống cơ sở dữ liệu AND e.EName = N’Lê Duy Anh’) – ? Độ phức tạp của truy vấn Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] 2
3. 9/5/2016 TRUY VẤN LỒNG Ví dụ • 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’ SELECT * FROM PROJECT WHERE PNumber IN (SELECT w.PNum FROM WORKSON w WHERE w.ESSN IN (SELECT e.ESSN FROM EMPLOYEE e Tổng quan hệ thống cơ sở dữ liệu – WHERE e.EName = N’ ’) ) ? Độ phức tạp của truy vấn Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] TRUY VẤN LỒNG Hàm EXISTS trong SQL • Đượ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 Ví dụ • 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 SELECT EName Tổng quan hệ thống cơ sở dữ liệu – FROM EMPLOYEE E WHERE NOT EXISTS (SELECT * FROM WOKRSON WHERE E.ESSN=ESSN) Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] TRUY VẤN LỒNG Hàm EXISTS trong SQL • Đượ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 Ví dụ • 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 Tổng quan hệ thống cơ sở dữ liệu SELECT EName – FROM EMPLOYEE WHERE ESSN NOT IN (SELECT ESSN FROM WOKRSON) Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] 3
4. 9/5/2016 BẢNG KẾT TRONG SQL Giới thiệu • 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ổng quan hệ thống cơ sở dữ liệu – tử INNER JOIN, OUTER JOIN đi kèm điều kiện kết tương ứng Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] BẢNG KẾT Ví dụ • 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: SELECT EName, ESalary FROM EMPLOYEE, DEPARTMENT WHERE DNum = DNumber AND DName=N’Phòng Nghiên cứu’ Tổng quan hệ thống cơ sở dữ liệu • Sử dụng bảng kết: – SELECT EName, ESalary FROM EMPLOYEE JOIN DEPARTMENT ON DNum = DNumber WHERE DName=N’Phòng Nghiên cứu’ Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] BẢNG KẾT Ví dụ SELECT EName, ESalary FROM EMPLOYEE, DEPARTMENT WHERE DNum = DNumber AND DName=N’Phòng Nghiên cứu’ SELECT EName, ESalary Tổng quan hệ thống cơ sở dữ liệu – FROM EMPLOYEE JOIN DEPARTMENT ON DNum = DNumber WHERE DName=N’Phòng Nghiên cứu’ Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] 4
5. 9/5/2016 BẢNG KẾT Ví dụ • 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: SELECT e.EName as ‘Employee’, s.EName as ‘Supervisor’ FROM EMPLOYEE e, EMPLOYEE s WHERE e.supervisorSSN = s.ESSN Tổng quan hệ thống cơ sở dữ liệu • Sử dụng bảng kết: – SELECT e.EName as ‘Employee’, s.EName as ‘Supervisor’ FROM EMPLOYEE e JOIN EMPLOYEE s ON e.supervisorSSN = s.ESSN Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] BẢNG KẾT Ví dụ SELECT e.EName as ‘Employee’, s.EName as ‘Supervisor’ FROM EMPLOYEE e, EMPLOYEE s WHERE e.supervisorSSN = s.ESSN Tổng quan hệ thống cơ sở dữ liệu SELECT e.EName as ‘Employee’, – s.EName as ‘Supervisor’ FROM EMPLOYEE e JOIN EMPLOYEE s ON e.supervisorSSN = s.ESSN Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] BẢNG KẾT Ví dụ • 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 SELECT e.EName as ‘Employee’, s.EName as ‘Supervisor’ Tổng quan hệ thống cơ sở dữ liệu FROM EMPLOYEE e JOIN EMPLOYEE s – ON e.supervisorSSN = s.ESSN • 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? Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] 5
6. 9/5/2016 BẢNG KẾT Ví dụ • 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: SELECT e.EName as ‘Employee’, s.EName as ‘Supervisor’ FROM EMPLOYEE e JOIN EMPLOYEE s ON e.supervisorSSN = s.ESSN Tổng quan hệ thống cơ sở dữ liệu – • Sử dụng bảng kết ngoài SELECT e.EName as ‘Employee’, s.EName as ‘Supervisor’ FROM EMPLOYEE e LEFT JOIN EMPLOYEE s ON e.supervisorSSN = s.ESSN Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] 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 Tổng quan hệ thống cơ sở dữ liệu – Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] BẢNG KẾT Ví dụ • 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 SELECT PNumber, DNumber, EName, EBirthdate FROM ((PROJECT JOIN DEPARTMENT ON dNum=DNumber) JOIN EMPLOYEE ON mgrSSN=ESSN) WHERE PLocation = N’TP Hồ Chí Minh’ Tổng quan hệ thống cơ sở dữ liệu – Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] 6
7. 9/5/2016 CÁC HÀM THỐNG KÊ TRONG SQL Các hàm thống kê (kết hợp) • 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 Ví dụ • Cho biết tổng lương, lương trung bình của nhân viên trong công ty Tổng quan hệ thống cơ sở dữ liệu SELECT SUM(ESalary) AS ‘Tổng lương’, – AVG(ESalary) AS ‘Lương trung bình’ FROM EMPLOYEE Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] CÁC HÀM THỐNG KÊ TRONG SQL Ví dụ • 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 SELECT SUM(E.ESalary), MAX(E.ESalary), MIN(E.ESalary) FROM (EMPLOYEE E JOIN WORKSON W ON E.ESSN=W.ESSN) WHERE W.PNUM = 1 Tổng quan hệ thống cơ sở dữ liệu – Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] MỆNH ĐỀ GOM NHÓM GROUP BY và HAVING trong SQL • 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 Tổng quan hệ thống cơ sở dữ liệukhi đã gom – Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] 7
8. 9/5/2016 MỆNH ĐỀ GOM NHÓM Ví dụ • 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ọ SELECT DNum, COUNT(*), AVG(ESalary) FROM EMPLOYEE GROUP BY DNum Tổng quan hệ thống cơ sở dữ liệu – Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] MỆNH ĐỀ GOM NHÓM Ví dụ • 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 đó SELECT p. PNumber, p. PName, COUNT(*) FROM PROJECT p, WORKSON w WHERE p.PNumber = w.PNum GROUP BY p. PNumber, p.PName Tổng quan hệ thống cơ sở dữ liệu – Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] MỆNH ĐỀ GOM NHÓM Ví dụ • 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 đó 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 Tổng quan hệ thống cơ sở dữ liệu – Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] 8
9. 9/5/2016 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 ] Tổng quan hệ thống cơ sở dữ liệu – Cơ sở dữ liệu Ths. Trịnh Hoàng Nam, [email protected] 9