Chương 11. Truy vẫn dữ liệu thông qua SQL

TRƯỜNG CAO ĐẲNG SƯ PHẠM QUẢNG TRỊ KHOA CÔNG NGHỆ THÔNG TIN SQL - Structured Query Language - Ngôn ngữ truy vấn dữ liệu có cấu trúc. Cách viết SQL chuẩn có dấu ; cuối câu nhưng SQL trong FoxPro thì tuân theo luật viết lệnh của Foxpro Ví dụ: SQL chuẩn SELECT * FROM HSCANBO; SQL Foxpro SELECT *; FROM HSCANBO Các thuật ngữ: Table: Tệp CSDL Row: bản ghi Col: Trường dữ liệu Chương 11. Truy vẫn dữ liệu thông qua SQL 1. Tạo tệp CSDL và vào dữ liệu 2. Truy vấn dữ liệu đơn giản 3. Kết xuất tệp theo thứ tự hoặc bỏ bản ghi trùng 4. Các hàm SQL 5. Tính tổng bộ phận 6. JOIN và UNION: liên kết dữ liệu 7. Các câu truy vấn dữ liệu lồng nhau 8. Ghi kết quả vào tệp hay in ra máy in Chương 11. Truy vẫn dữ liệu thông qua SQL 1. Tạo tệp CSDL và vào dữ liệu 1.1. Tạo tệp CSDL Lệnh: CREATE TABLE ( [([,<thập phân>])][, [([,])] ...]) Giải thích: : tên của tệp CSDL muốn tạo : tên của trường thứ i [([,])]: Là kiểu và độ rộng của trường thứ i được quy định như sau: C(X)- Kiểu ký tự với độ dài X D - Kiểu ngày L - Kiểu Logic N(X,Y) - Kiểu số với độ rộng X và phần thập phân là Y. Chương 11. Truy vẫn dữ liệu thông qua SQL 1. Tạo tệp CSDL và vào dữ liệu 1.1. Tạo tệp CSDL Ví dụ: CREATE TABLE HSCB (MSCB C(4), HD C(18), TEN C(7), NSINH D, HSL N(4,2), TDVH N(2), GD L) CREA TABL HOSO (SBD N(4),HVT C(25)) CREA TABL FACHBT (SBD N(4), FACH C(2)) CREA TABL DIEMBT(FACH C(2),DIEM N(5,2)) Chương 11. Truy vẫn dữ liệu thông qua SQL 1. Tạo tệp CSDL và vào dữ liệu 1.2. Vào dữ liệu: Lệnh: INSERT INTO [()] VALUES () Giải thích: Lệnh sẽ chắp vào một bản ghi mới. Nếu không có tùy chọn <Ds trường> thì phải vào dữ liệu cho tất cả các trường, nếu có thì chỉ vào dữ liệu cho các trường được liệt kê trong danh sách. là danh sách các giá trị sẽ gán cho các trường tương ứng theo thứ tự ở và hoặc là thứ tự các trường trên tệp nếu không có tùy chọn <Ds trường>. Ví dụ: INSERT INTO HSCANBO (MSCB, HD, TEN) VALUES (‘NV01’,’TRAN VAN’,’DUNG’) INSERT INTO DIEMBT VALUES (‘X4’,9.5) Chương 11. Truy vẫn dữ liệu thông qua SQL 2. Truy vấn dữ liệu đơn giản 2.1. Chọn tệp CSDL và các trường 2.2. Chọn các bản ghi Chương 11. Truy vẫn dữ liệu thông qua SQL 2. Truy vấn dữ liệu đơn giản 2.1. Chọn tệp CSDL và các trường Lệnh: SELECT FROM Giải thích: Lệnh sẽ hiển thị lên màn hình nội dung của . Nếu tùy chọn <Ds trường> được thay bằng dấu * thì tất cả các trường đều được hiển thị, nếu viết tường minh thì chỉ các trường được liệt kê trong danh sách mới được hiển thị. Ví dụ: SELE * FROM HSCANBO SELE MSCB,HD,TEN,HSL FROM HSCANBO Chương 11. Truy vẫn dữ liệu thông qua SQL 2. Truy vấn dữ liệu đơn giản 2.2. Chọn các bản ghi Để chọn các bản ghi trong tệp CSDL thỏa mãn điều kiện nào đó ta thêm vào lệnh tùy chọn sau: WHERE Giải thích: - Với tuỳ chọn này được bổ sung vào lệnh thì chỉ có những bản ghi thỏa mãn điều kiện chọn mới được hiện ra. - có thể là một trong các dạng sau: * Hai biểu thức cùng loại (Số, Ký tự, Ngày) quan hệ với nhau bằng một trong các phép toán Logic sau: = bằng # khác > lớn hơn != khác khác >= lớn hơn hoặc bằng !> không lớn hơn <= nhỏ hơn hoặc bằng !< không nhỏ hơn Chương 11. Truy vẫn dữ liệu thông qua SQL 2. Truy vấn dữ liệu đơn giản 2.2. Chọn các bản ghi * IN (, , ...) có giá trị là một trong các thì thỏa mãn. * LIKE Ở vị trí của là dấu gạch dưới _ có nghĩa là thay thế cho một ký tự bất kỳ. Ở vị trí của là dấu phần trăm % có nghĩa là thay thế cho một xâu ký tự bất kỳ. * BETWEEN AND Giá trị của ở trong đoạn và * Các toán tử logic NOT, AND và OR Với các điều kiện chọn như trên ta có thể sử dụng các toán tử logic kết hợp với chúng. Chương 11. Truy vẫn dữ liệu thông qua SQL 2. Truy vấn dữ liệu đơn giản 2.2. Chọn các bản ghi Ví dụ: SELE * FROM HSCANBO WHERE HSL IN (2.97,3.62) SELE * FROM HSCANBO WHERE MSCB LIKE “NV__” SELE * FROM HSCANBO WHERE HD LIKE “%VAN%” SELE * FROM HSCANBO WHERE TDVH BETWEEN 8 AND 10 SELE * FROM HSCANBO WHERE MSCB LIKE “NV__” AND HSL>3 Chương 11. Truy vẫn dữ liệu thông qua SQL 3. Kết xuất tệp theo thứ tự hoặc bỏ bản ghi trùng 3.1 Kết xuất theo thứ tự: Muốn các bản ghi của một tệp CSDL xuất hiện theo một thứ tự nào đó ta sẽ bổ sung vào lệnh tuỳ chọn: ORDER BY [ASC|DESC][, [ASC|DESC]...] Giải thích: Các bản ghi sẽ xuất hiện theo thứ tự ưu tiên trường 1, trường 2, Nếu có tùy chọn ASC thì được sắp theo thứ tự tăng dần còn DESC là giảm dần. Ví dụ: SELE * FROM HSCANBO ORDER BY TEN, HD SELE * FROM HSCANBO ORDER BY HSL DESC Chương 11. Truy vẫn dữ liệu thông qua SQL 3. Kết xuất tệp theo thứ tự hoặc bỏ bản ghi trùng 3.2 Chỉ hiện một bản ghi có một giá trị trường: Muốn các bản ghi của một tệp CSDL có cùng một giá trị trường nào đó chỉ xuất hiện một bản ghi ta sẽ bổ sung vào tên của nó sau lệnh SELECT tuỳ chọn DISTINCT Ví dụ: SELECT DISTINCT HSL FROM HSCANBO Chương 11. Truy vẫn dữ liệu thông qua SQL 4. Các hàm SQL 4.1 Hàm COUNT() Đếm số lượng các bản ghi Các mẫu lệnh SELECT COUNT(*) FROM HSCANBO Đếm số lượng các bản ghi trong tệp HSCANBO SELECT COUNT(MSCB) FROM HSCANBO Đếm số lượng các bản ghi trong tệp HSCANBO có MSCB không rỗng. SELECT COUNT(DISTINCT HSL) FROM HSCANBO Đếm số lượng các loại hệ số lương trong tệp HSCANBO SELECT COUNT(MSCB) FROM HSCANBO WHERE HSL=3.62 Đếm số lượng các bản ghi trong tệp HSCANBO có HSL=3.62. Chương 11. Truy vẫn dữ liệu thông qua SQL 4. Các hàm SQL 4.2 Các hàm AVG(), MIN(), MAX(), SUM() Các hàm này được sử dụng tương tự như trên nhưng không sử dụng DISTINCT đối với MAX và MIN. Ví dụ: SELECT AVG(HSL), MAX(HSL), MIN(HSL) FROM HSCANBO Chương 11. Truy vẫn dữ liệu thông qua SQL 5. Tính tổng bộ phận Để tính tổng bộ phận chúng ta sẽ sử dụng hai tùy chọn GROUP BY và HAVING Mẫu lệnh SELECT KHOA, SUM(TONG) FROM BLUONG GROUP BY KHOA Tính tổng bộ phận của tổng lương theo từng phòng khoa. SELECT KHOA, SUM(HSL*290000) FROM BLUONG GROUP BY KHOA HAVING (HSL*290000)>600000 Tính tổng bộ phận của tổng lương theo từng phòng khoa với điều kiện HSL*290000>600000. Ngoài ra chúng ta có thể dùng kết hợp với tùy chọn WHERE SELECT KHOA, SUM(HSL*290000) FROM BLUONG WHERE PCTN>0 GROUP BY KHOA HAVING (HSL*290000)>600000 Chương 11. Truy vẫn dữ liệu thông qua SQL 6. JOIN và UNION: liên kết dữ liệu 6.1 Liên kết dữ liệu Để liên kết dữ liệu trên nhiều tệp CSDL ta sẽ mở rộng lệnh SELECT như sau: SELECT [*|.[, ....]] FROM [, ... ] WHERE * là bí danh của tệp i * : Đây là điều kiện để kết nối các tệp Chương 11. Truy vẫn dữ liệu thông qua SQL 6. JOIN và UNION: liên kết dữ liệu 6.1 Liên kết dữ liệu Mẫu lệnh SELECT HD.SHHD,HD.MSHH, DM.TENH, HD.SL,DM.DGIA FROM HOADON HD, DMHANG DM WHERE HD.MSHH=DM.MSHH Liên kết hai tệp HOADON.DBF và DMHANG.DBF với điều kiện các bản ghi bằng nhau về MSHH SELECT HS.SBD,HS.HVT, FA.FACH, DI.DIEM FROM HOSO HS, FACHBT FA, DIEMTHI DI WHERE HS.SBD=FA.SBD AND FA.FACH=DI.FACH Liên kết ba tệp HOSO.DBF, FACHBT.DBF và DIEMTHI.DBF với điều kiện các bản ghi của hai tệp HOSO.DBF, FACHBT.DBF bằng nhau về SBD và hai tệp FACHBT.DBF và DIEMTHI.DBF bằng nhau về FACH. Chương 11. Truy vẫn dữ liệu thông qua SQL 6. JOIN và UNION: liên kết dữ liệu 6.2 Bổ sung bản ghi: Với các tệp CSDL có cấu trúc giống nhau ta có thể nối liền nhau các bản ghi của nhiều tệp bằng tùy chọn UNION Mẫu lệnh: SELECT * FROM HOSO UNION SELECT * FROM HS1 UNION SELECT * FROM HS2 Chương 11. Truy vẫn dữ liệu thông qua SQL 7. Các câu truy vấn dữ liệu lồng nhau Các câu truy vấn có thể có một câu truy vấn phụ nằm trong tùy chọn WHERE. Như vậy kết quả của một câu truy vấn sẽ được sử dụng để thực hiện một câu truy vấn khác. Mẫu lệnh: SELECT * FROM DMHANG WHERE MSHH = (SELECT MSHH FROM HOADON WHERE SL>=50) Liệt kê các mặt hàng (đầy đủ các thuộc tính) mà đã bán trên mỗi hoá đơn với số lượng lớn hơn hoặc bằng 50. Chương 11. Truy vẫn dữ liệu thông qua SQL 8. Ghi kết quả vào tệp hay in ra máy in * Để đưa kết quả vào tệp ta đưa ra tuỳ chọn TO FILE * Để đưa kết quả ra máy in ta đưa ra tuỳ chọn TO PRINT Chương 11. Truy vẫn dữ liệu thông qua SQL