Chương 8 SQL nâng cao

Chương 8. SQL nâng cao 8 - 1 Chương 8 SQL NÂNG CAO - Lý thuyết: 2 tiết - Thực hành: 2 tiết Chương 8. SQL nâng cao 8 - 2 8.1 Phép toán SET 8.2 Các hàm thời gian 8.3 Mệnh đề GROUP BY nâng cao 8.4 Câu lệnh DML and DDL mở rộng SQL NÂNG CAO Chương 8. SQL nâng cao 8 - 3 - Kết hợp kết quả hai hay nhiều câu truy vấn thành phần thành một kết quả duy nhất (truy vấn ghép). - Các phép toán tập hợp có cùng thứ tự ưu tiên 8.1 Phép toán SET Chương 8. SQL nâng cao 8 - 4 - Các phép toán tập hợp 8.1 Phép toán SET Lấy kết quả có trong câu truy vấn thứ nhất mà không có trong câu truy vấn thứ hai (sau toán tử MINUS). MINUS Lấy phần giao giữa các kết quả của nhiều câu truy vấn. INTERSET Kết hợp kết quả của nhiều câu truy vấn với nhau, kể cả các mẫu tin trùng nhau cũng được giư˜ lại. UNION ALL Kết hợp kết quả của nhiều câu truy vấn với nhau, nếu có nhiều mẫu tin trùng nhau thì chỉ giữ lại một mẫu tin. UNION Diễn giảiTên phép toán Chương 8. SQL nâng cao 8 - 5 - Phép toán UNION Hợp nhiều câu truy vấn sau khi đã loại bỏ trùng lặp.  Số lượng các cột và kiểu dữ liệu phải giống nhau trong các câu lệnh trong câu truy vấn.  Thực hiện trên tất cả các cột được chọn.  Giá trị NULL không được bỏ qua khi kiểm tra tính trùng lặp.  Toán tử IN có thứ tự ưu tiên cao hơn phép toán UNION.  Mặc định sắp xếp tăng dần cho cột đầu tiên trong mệnh đề SELECT. 8.1 Phép toán SET Chương 8. SQL nâng cao 8 - 6 - Phép toán UNION Ví dụ: Hiển thị lịch sử công việc của tất cả các nhân viên. Mỗi nhân viên chỉ xuất hiện một lần. (Job_history) SELECT Emp_id, Job_id FROM Emp UNION SELECT Emp_id, Job_id FROM Job_History 8.1 Phép toán SET SELECT Empid, Jobid FROM Job_History SELECT Empid, Jobid FROM Emp Chương 8. SQL nâng cao 8 - 7 - Phép toán UNION ALL Hợp từ nhiều câu truy vấn kể cả sự trùng lặp.  Không sắp xếp mặc định tăng dần như UNION.  Không thể dùng từ khóa DISTINCT trong các câu lệnh select thành phần. 8.1 Phép toán SET Chương 8. SQL nâng cao 8 - 8 - Phép toán UNION ALL Ví dụ: Hiển thị lịch sử công việc của tất cả các nhân viên. SELECT Emp_id, Job_id, Dept_id FROM Emp UNION ALL SELECT Emp_id, Job_id, Dept_id FROM Job_History ORDER BY Emp_id 8.1 Phép toán SET Chương 8. SQL nâng cao 8 - 9 - Phép toán INTERSECT Trả về kết quả là phần chung giữa kết quả của hai câu truy vấn thành phần.  Số lượng các cột và kiểu dữ liệu phải giống nhau trong các câu lệnh truy vấn.  Giá trị NULL không được bỏ qua khi kiểm tra tính trùng lặp 8.1 Phép toán SET Chương 8. SQL nâng cao 8 - 10 - Phép toán INTERSECT Ví dụ: Hiển thị những nhân viên mà trong quá khứ họ đã từng làm công việc giống như công việc hiện tại. SELECT Emp_id, Job_id FROM emp INTERSECT SELECT Emp_id, Job_id FROM job_history 8.1 Phép toán SET Chương 8. SQL nâng cao 8 - 11 - Phép toán MINUS Trả về kết quả trong câu truy vấn thứ nhất mà không có trong câu truy vấn thứ hai.  Số lượng các cột và kiểu dữ liệu phải giống nhau trong các câu lệnh truy vấn.  Tất cả các cột trong mệnh đề WHERE phải có trong mệnh đề SELECT 8.1 Phép toán SET Chương 8. SQL nâng cao 8 - 12 - Phép toán MINUS Ví dụ: Hiển thị những nhân viên chưa bao giờ thay đổi công việc. SELECT Emp_id FROM emp MINUS SELECT Emp_id FROM job_history 8.1 Phép toán SET Chương 8. SQL nâng cao 8 - 13 - Một số nguyên tắc chung trên các phép toán tập hợp  Các biểu thức trong mệnh đề SELECT phải cùng số lượng và kiểu dữ liệu.  Dùng dấu ngoặc đơn để thay đổi trình tự (ưu tiên) thực hiện.  Mệnh đề ORDER BY có thể xuất hiện chỉ cuối câu lệnh. 8.1 Phép toán SET Chương 8. SQL nâng cao 8 - 14 - Hàm CURRENT_DATE Trả về ngày hiện tại trong phần thời gian khu vực CURRENT_DATE - Biểu thức EXTRACT Trả về giá trị của trường ngày tháng từ một biểu thức ngày tháng hay giá trị khoảng thời gian. EXTRACT(expr) 8.2 Các hàm thời gian Chương 8. SQL nâng cao 8 - 15 - Biểu thức EXTRACT Ví dụ: Lấy giá trị tháng trong cột Hiredate. SELECT firstname, Hireday, EXTRACT(MONTH FROM Hireday) FROM emp WHERE Manager_id = 100 8.2 Các hàm thời gian Chương 8. SQL nâng cao 8 - 16 - Hàm TO_YMINTERVAL Chuyển đổi chuỗi ký tự kiểu CHAR, VARCHAR2, NCHAR hay NVARCHAR thành giá trị kiểu INTERVAL YEAR TO MONTH. TO_YMINTERVAL(char) 8.2 Các hàm thời gian Chương 8. SQL nâng cao 8 - 17 - Hàm TO_YMINTERVAL Ví dụ: Lấy thông tin cột Hireday và Hireday cộng thêm 1 năm 2 tháng. SELECT Hireday, Hireday + TO_YMINTERVAL('01-02') AS HIRE_DATE_YMININTERVAL FROM EMP WHERE Dept_id = 20 8.2 Các hàm thời gian Chương 8. SQL nâng cao 8 - 18 8.3.1 Toán tử ROLLUP và CUBE - Toán tử ROLLUP Đưa ra một tập hợp kết quả bao gồm các hàng được gộp theo nhóm và những giá trị tổng hợp thành phần (Subtotal) 8.3 Mệnh đề GROUP BY nâng cao Chương 8. SQL nâng cao 8 - 19 8.3.1 Toán tử ROLLUP và CUBE - Toán tử ROLLUP SELECT [column,] group_function(column). . . FROM table [WHERE condition] [GROUP BY [ROLLUP] group_by_expression] [HAVING having_expression] [ORDER BY column] 8.3 Mệnh đề GROUP BY nâng cao Chương 8. SQL nâng cao 8 - 20 8.3.1 Toán tử ROLLUP và CUBE - Toán tử ROLLUP Ví dụ: Tạo bảng tổng hợp lương mỗi nhóm công việc trong một phòng ban và tất cả các phòng ban. SELECT Deptid, Jobid, SUM(Salary) FROM Emp GROUP BY ROLLUP(Deptid, Jobid) 8.3 Mệnh đề GROUP BY nâng cao Chương 8. SQL nâng cao 8 - 21 8.3.1 Toán tử ROLLUP và CUBE - Toán tử CUBE Đưa ra bảng giá trị tổng hợp dạng bảng báo cáo. SELECT [column,] group_function(column). . . FROM table [WHERE condition] [GROUP BY [CUBE] group_by_expression] [HAVING having_expression]; [ORDER BY column] 8.3 Mệnh đề GROUP BY nâng cao Chương 8. SQL nâng cao 8 - 22 8.3.1 Toán tử ROLLUP và CUBE - Toán tử CUBE Ví dụ: Tạo bảng tổng hợp lương dạng bảng báo cáo. SELECT Dept_id, Job_id, SUM(Salary) FROM Emp GROUP BY CUBE(Dept_id, Job_id) 8.3 Mệnh đề GROUP BY nâng cao Chương 8. SQL nâng cao 8 - 23 8.3.2 Hàm GROUPING - Xác định mức tổng hợp của một tổng hợp thành phần. - Nhận biết một giá trị NULL trong biểu thức của một hàng kết quả là từ bảng cơ sở hay là do toán tử ROLLUP(CUBE) tạo ra. SELECT [column,] group_function(column) . ., GROUPING(expr) FROM table [WHERE condition] [GROUP BY [ROLLUP][CUBE] groupbyexpr] [HAVING having_expression] [ORDER BY column] 8.3 Mệnh đề GROUP BY nâng cao Chương 8. SQL nâng cao 8 - 24 8.3.2 Hàm GROUPING - Có thể được dùng với toán tử CUBE hoặc ROLLUP - Có thể tạo ra kết quả tổng hợp trong hàng. - Trả về giá trị 0 hoặc 1giúp cho chúng ta hiểu được một giá trị tổng hợp đa˜ đạt được như thế nào.  Giá trị 0 có nghĩa là biểu thức được sử dụng để tính giá trị tổng hợp và giá trị NULL là từ bảng cơ sở.  Giá trị 1 có nghĩa là biểu thức không được sử dụng để tính giá trị tổng hợp và giá trị NULL là do toán tử ROLLUP hoặc CUBE tạo ra. 8.3 Mệnh đề GROUP BY nâng cao Chương 8. SQL nâng cao 8 - 25 8.3.2 Hàm GROUPING Ví dụ: Tạo bảng tổng hợp lương cho các phòng ban SELECT Deptid, Jobid, Job, SUM(Salary), GROUPING(Deptid) Gdept, GROUPING(Jobid) Gjob FROM emp GROUP BY ROLLUP(Deptid, Jobid) 8.3 Mệnh đề GROUP BY nâng cao Chương 8. SQL nâng cao 8 - 26 8.3.3 Mệnh đề GROUPING SETS - Là sự mở rộng của mệnh đề GROUP BY. - Cho phép xác định nhiều nhóm dư˜ liệu giúp cho việc phân tích dư˜ liệu đa chiều dê˜ dàng hơn. - Có thể định nghĩa nhiều hàm GROUPING trong cùng một câu truy vấn. - Oracle Server tính tất cả các hàm GROUPING chỉ định trong mệnh đề GROUPING SETS và kết hợp kết quả của các hàm GROUPING riêng lẻ với phép toán UNION ALL 8.3 Mệnh đề GROUP BY nâng cao Chương 8. SQL nâng cao 8 - 27 8.3.3 Mệnh đề GROUPING SETS SELECT [column,] group_function(column)… FROM table [WHERE condition] [GROUP BY GROUPING SETS gr_expr] [HAVING having_expression] [ORDER BY column] 8.3 Mệnh đề GROUP BY nâng cao Chương 8. SQL nâng cao 8 - 28 8.3.3 Mệnh đề GROUPING SETS Ví dụ: Tạo bảng tổng hợp lương theo hai nhóm là (Deptid, Jobid) và (Jobid, Managerid). SELECT Deptid, Jobid, Managerid, AVG(Salary) FROM Emp GROUP BY GROUPING SETS ((Depid, Joid), (Jobid, Managerid)) 8.3 Mệnh đề GROUP BY nâng cao Chương 8. SQL nâng cao 8 - 29 8.3.3 Mệnh đề GROUPING SETS Câu lệnh truy vấn trong ví dụ trên có thể được viết lại như sau: SELECT Deptid, Jobid, Null As ManagerId, Avg(Salary) FROM Emp GROUP BY Deptid, Jobid UNION ALL SELECT Null As Deptid, Jobid, Managerid, Avg(Salary) FROM Emp GROUP BY Jobid, managerid 8.3 Mệnh đề GROUP BY nâng cao Chương 8. SQL nâng cao 8 - 30 8.3.4 Cột ghép - Là một tập hợp các cột, mỗi cột coi như một đơn vị. - Nhóm các cột trong dấu ngoặc đơn để Oracle server xem các cột đó như một đơn vị trong quá trình tính toán với toán tử ROLLUP hay CUBE. Ví dụ ROLLUP (a, (b,c) , d); (b,c) là một cột ghép. 8.3 Mệnh đề GROUP BY nâng cao Chương 8. SQL nâng cao 8 - 31 8.3.4 Cột ghép Ví dụ Tạo bảng tổng hợp lương theo từng phòng ban, công việc và người quản lý. SELECT Deptid, Jobid, Managerid, SUM(Salary) FROM Emp GROUP BY ROLLUP( Deptid, (Jobid, Managerid)) 8.3 Mệnh đề GROUP BY nâng cao Chương 8. SQL nâng cao 8 - 32 8.4.1 Câu lệnh chèn nhiều bảng - Chèn một hay nhiều hàng vào trong nhiều bảng. - Thường được sử dụng trong hệ thống kho dư˜ liệu để chuyển dư˜ liệu từ một hay nhiều nguồn sang một tập hợp các bảng đích. - Khả năng cải tiến thực hiện trong cơ sở dư˜ liệu. 8.4 Câu lệnh DML and DDL mở rộng Chương 8. SQL nâng cao 8 - 33 8.4.1 Câu lệnh chèn nhiều bảng INSERT [ALL] [FIRST] [WHEN condition THEN] [insert_into values] [ELSE] [insert_into values] [insert_into values] (subquery) 8.4 Câu lệnh DML and DDL mở rộng Chương 8. SQL nâng cao 8 - 34 8.4.1 Câu lệnh chèn nhiều bảng - Chèn không điều kiện INSERT ALL INTO Sal_History VALUES(EmpId, hireday, salary) INTO Mgr_History VALUES(EmpId, Mgr, Salary) SELECT Empid, Hireday, Salary, ManagerId Mgr FROM Emp WHERE Empid>200 8.4 Câu lệnh DML and DDL mở rộng Chương 8. SQL nâng cao 8 - 35 8.4.1 Câu lệnh chèn nhiều bảng - Chèn có điều kiện (ALL) INSERT ALL WHEN Sal>10000 THEN INTO Sal_History VALUES(EmpId, Date, sal) WHEN Mgr>200 THEN INTO Mgr_History VALUES(EmpId, Mgr, Sal) SELECT EmpId, Hireday Date, Salary Sal, Managerid Mgr FROM Emp WHERE Empid>200 8.4 Câu lệnh DML and DDL mở rộng Chương 8. SQL nâng cao 8 - 36 8.4.1 Câu lệnh chèn nhiều bảng - Chèn có điều kiện (FIRST) INSERT FIRST WHEN Sal>25000 THEN INTO Special_Sal VALUES(DepId, Sal) WHEN Date LIKE (‘%00%’) THEN INTO Date_His_00 VALUES(DeptId, Date) WHEN Date LIKE (‘%99%’) THEN INTO Date_His_99 VALUES(DeptId, Date) SELECT DeptId, Hireday Date, Salary Sal, ManagerId Mgr FROM Emp GROUP BY Deptid 8.4 Câu lệnh DML and DDL mở rộng Chương 8. SQL nâng cao 8 - 37 8.4.1 Câu lệnh chèn nhiều bảng - Chèn có điều kiện (FIRST) INSERT FIRST WHEN Sal>25000 THEN INTO Special_Sal VALUES(DepId, Sal) WHEN Date LIKE (‘%00%’) THEN INTO Date_His_00 VALUES(DeptId, Date) WHEN Date LIKE (‘%99%’) THEN INTO Date_His_99 VALUES(DeptId, Date) ELSE INTO Date_His VALUES(DeptId, Date) SELECT DeptId, Hireday Date, Salary Sal, ManagerId Mgr FROM Emp 8.4 Câu lệnh DML and DDL mở rộng Chương 8. SQL nâng cao 8 - 38 8.4.2 Bảng ngoài - Bảng ngoài là những bảng chỉ cho phép đọc - Dữ liệu được lưu trữ trong một tệp tin bên ngoài cơ sở dữ liệu. - Sử dụng câu lệnh CREATE TABLE. - Có thể truy vấn dữ liệu bằng ngôn ngữ SQL nhưng không thể sử dụng DML và không tạo được các chỉ mục. 8.4 Câu lệnh DML and DDL mở rộng Chương 8. SQL nâng cao 8 - 39