Bài giảng Tin học ứng dụng trong kinh doanh - Bài 3: Truy vấn dữ liệu - Thiều Quang Trung

 BÀI 3 
 TRUY VẤN DỮ LIỆU 
 GV Th.S. Thiều Quang Trung 
Trường Cao đẳng Kinh tế Đối ngoại 
 Nội dung 
1 • Giới thiệu về truy vấn dữ liệu 
 • Các phép toán và hàm sử dụng 
2 trong truy vấn 
3 • Các loại truy vấn dữ liệu 
 GV. Thiều Quang Trung 2 
 Giới thiệu về truy vấn dữ liệu 
• Các dạng yêu cầu xử l{ dữ liệu: 
 – Trích - lọc - hiển thị dữ liệu; 
 – Tổng hợp - thống kê; 
 – Thêm - bớt - cập nhật thay đổi dữ liệu. 
 GV. Thiều Quang Trung 3 
 Giới thiệu về truy vấn dữ liệu 
• Các loại truy vấn dữ liệu trong MS Access: 
 – Select Query 
 – Total Query 
 – Crosstab Query 
 – Make table Query 
 – Append Query 
 – Delete Query 
 – Update Query 
 GV. Thiều Quang Trung 4 
 Giới thiệu về truy vấn dữ liệu 
• Các bước tạo truy vấn dữ liệu: 
 – Bước 1. Chọn Queries/New. 
 – Bước 2. Chọn Design view. 
 – Bước 3. Chọn các bảng. 
 – Bước 4. Mở menu Query/Chọn loại truy vấn. 
 – Bước 5. Chọn trường và tiêu chuẩn cần lấy. 
 – Bước 6. Chạy Query. 
 – Bước 7. Đặt tên cho Query. 
 GV. Thiều Quang Trung 5 
 Giới thiệu về truy vấn dữ liệu 
• Màn hình tạo truy vấn dữ liệu (query): 
 GV. Thiều Quang Trung 6 
 Giới thiệu về truy vấn dữ liệu 
• Query có thể hiện thị theo các dạng sau: 
 – Design View (dạng thiết kế) 
 – SQL View (câu lệnh SQL tương ứng) 
 – Datasheet View (bảng kết quả của truy vấn) 
 GV. Thiều Quang Trung 7 
 Giới thiệu về truy vấn dữ liệu 
• Cấu trúc cơ bản của query: 
 – Field: tên trường cần lấy 
 – Table: tên bảng của trường cần lấy 
 – Sort: sắp xếp dữ liệu 
 – Show: xác định trường được chọn hiển thị 
 – Criteria: điều kiện chọn dữ liệu hiển thị 
 – Or: kết hợp điều kiện chọn dữ liệu hiển thị 
 GV. Thiều Quang Trung 8 
Các phép toán dùng trong truy vấn 
• Các toán tử so sánh: =, >, =, 
• Các phép toán logic: 
 – Phủ định : NOT 
 – Kết hợp (Và) : AND 
 – Lựa chọn (Hoặc) : OR 
 GV. Thiều Quang Trung 9 
Các phép toán dùng trong truy vấn 
• Các toán tử BETWEEN, IN và LIKE 
• Between: xác định một khoảng giá trị 
 – Ví dụ : Between 2015 and 2016 
• IN: xác định theo danh sách các giá trị 
 – Ví dụ: IN(5,7,9). 
• LIKE: so sánh gần đúng với dữ liệu kiểu chuỗi 
 – Ví dụ: LIKE “K???” ; LIKE “K*” 
 – Dấu hoa thị (*): Thay thế cho một nhóm k{ tự. 
 – Dấu hỏi (?): Thay thế cho một k{ tự. 
 GV. Thiều Quang Trung 10 
 Các hàm dùng trong truy vấn 
• Hàm xử l{ giá trị ngày: 
 – Date(): Trả về ngày tháng của hệ thống 
 – Day(Date): Trả về ngày của giá trị Date 
 – Month(Date): Trả về tháng của giá trị Date 
 – Year(Date): Trả về Năm của giá trị Date 
 – DateValue(Date): chuyển đổi chuỗi Date thành 
 dạng giá trị ngày tháng năm 
 – DateDiff("yyyy",Date1,Date2): tính khoảng thời 
 gian từ mốc Date1 đến Date2, đổi ra năm 
 GV. Thiều Quang Trung 11 
 Các hàm dùng trong truy vấn 
• Hàm xử l{ chuỗi: 
 – Left(Xâu,N): Trả về N k{ tự bên trái của xâu văn bản 
 – Right(Xâu,N): Trả về N k{ tự bên phải của xâu văn bản 
 – Mid(Xâu,N,M): Hàm trả về N k{ tự bắt đầu từ vị trí M 
 – Upcase(Xâu): Hàm chuyển đổi xâu k{ tự thành chữ in hoa 
 – Trim(Xâu): Hàm loại bỏ k{ tự trắng bên trái và bên phải 
 của xâu k{ tự 
 GV. Thiều Quang Trung 12 
Các hàm dùng trong truy vấn 
• Hàm xử l{ thống kê: 
 – Sum:Tính tổng các giá trị của trường trong nhóm 
 – Avg:Tính tổng trung bình các giá trị của trường trong 
 nhóm 
 – Min: Tìm giá trị nhỏ nhất trong nhóm 
 – Max: Tìm giá trị lớn nhất trong nhóm 
 – Count: Đếm số bản ghi trong nhóm 
 GV. Thiều Quang Trung 13 
Các hàm dùng trong truy vấn 
• Hàm điều kiện: 
 – IIF(điều kiện, giá trị 1, giá trị 2) 
- Tính toán: 
 – +: Phép cộng. 
 – - : Phép trừ. 
 – *: Phép nhân. 
 – /: Phép chia. 
 – ^: Luỹ thừa. 
 – &: Nối chuỗi văn bản. 
 GV. Thiều Quang Trung 14 
 SELECT Query 
• Select query là loại truy vấn dùng trích - lọc - 
 kết xuất dữ liệu từ nhiều bảng khác nhau từ 
 CSDL ra một bảng kết quả 
• Ví dụ: 
 – Đưa ra thông tin chi tiết sinh viên của một lớp; 
 – Đưa ra thông tin chi tiết về các hoá đơn bán ra trong ngày 
 hôm nay; 
 – Đưa ra doanh thu bán hàng của một tháng nào đó; 
 GV. Thiều Quang Trung 15 
 SELECT Query 
• Ví dụ 1: cơ sở dữ liệu quản l{ đơn đặt hàng: 
 GV. Thiều Quang Trung 16 
 SELECT Query 
• Query: trích xuất thông tin chi tiết về các đơn hàng 
 đặt trong ngày hôm nay ? 
 GV. Thiều Quang Trung 17 
 SELECT Query 
• Query: trích xuất thông tin chi tiết về các đơn hàng 
 đặt trong ngày hôm nay ? 
 → Truy vấn liên quan đến mấy bảng ? 
 GV. Thiều Quang Trung 18 
 SELECT Query 
• Query: trích xuất thông tin chi tiết về các đơn hàng 
 đặt trong ngày hôm nay ? 
 → Thông tin (cột) nào cần hiển thị ? 
 GV. Thiều Quang Trung 19 
 SELECT Query 
• Query: trích xuất thông tin chi tiết về các đơn hàng 
 đặt trong ngày hôm nay ? 
 → Điều kiện nào để lọc dữ liệu ? 
 GV. Thiều Quang Trung 20 
 SELECT Query 
• Query: trích xuất thông tin chi tiết về các đơn hàng 
 đặt trong ngày hôm nay ? 
 → Kết quả câu lệnh SQL là: 
 SELECT DONDH.SODH, DONDH.NGAYDH, CTDONDH.MAHG, 
 MATHANG.TENHANG, CTDONDH.SLDAT, CTDONDH.GIADH, 
 CTDONDH.THANHTIEN 
 FROM MATHANG INNER JOIN (DONDH INNER JOIN 
 CTDONDH ON DONDH.SODH = CTDONDH.SODH) ON 
 MATHANG.MAHANG = CTDONDH.MAHG 
 WHERE (((DONDH.NGAYDH) Like Date())); 
 GV. Thiều Quang Trung 21 
 Lọc dữ liệu trên Query 
• Cách lọc dữ liệu: thiết lập điều kiện tại dòng Criteria 
 (trong chế độ Design View) 
• Có thể lọc theo những điều kiện phức hợp 
• Có thể chấp nhận những giá trị lọc là các tham biến 
• Các điều kiện đặt trên cùng một dòng Criteria sẽ được nối 
 với nhau bởi toán tử AND 
• Các điều kiện đặt trên mỗi dòng Criteria sẽ được nối với 
 nhau bởi toán tử OR 
 GV. Thiều Quang Trung 22 
 Lọc dữ liệu trên Query 
• Ví dụ 1: Lọc ra những nhân viên có lương thực lĩnh 
 trong khoảng từ 1000000 đến 2000000 
• Cú pháp toán tử Between: 
 Between And 
 GV. Thiều Quang Trung 23 
 Lọc dữ liệu trên Query 
• Ví dụ 2: Lọc ra khách hàng có tên là Hà (tên chỉ là 
 một phần của trường TENKH) 
 GV. Thiều Quang Trung 24 
 Lọc dữ liệu trên Query 
• Trường hợp sử dụng tham biến trong điều kiện lọc 
 thì đặt câu nhắc trong cặp dấu *+ 
• Khi run, hộp thoại 
 xuất hiện như hình: 
 GV. Thiều Quang Trung 25 
 Lọc dữ liệu trên Query 
• Một số ví dụ minh hoạ toán tử LIKE: 
 – Like ‘Nguyễn*’ → lọc ra những người họ nguyễn; 
 – Like ‘*/*/1996’ → lọc ra những người sinh năm 1996; 
 – Like ‘12/*/*’ → lọc ra những người sinh tháng 12; 
 – Like ‘*/*/199?’ - lọc ra những người sinh từ năm 1990 
 đến năm 1999; 
 – Like ‘[TH]*’ → lọc ra những người có họ bắt đầu là chữ T 
 hoặc chữ H 
 – Like ‘[H-T]*’ → lọc ra những người có họ bắt đầu là một 
 trong các chữ từ chữ H đến chữ T trong bảng chữ cái 
 GV. Thiều Quang Trung 26 
 TOTAL Query 
• Tổng hợp – là phép xử l{ dữ liệu khá phổ biến. Ví dụ đếm 
 tổng số nhân viên theo loại chức vụ 
 GV. Thiều Quang Trung 27 
 TOTAL Query 
• Bước 1: Tạo một select query với đầy đủ các thông tin liên 
 quan bảng tổng hợp: 
 GV. Thiều Quang Trung 28 
 TOTAL Query 
• Bước 2: Tuz chọn Total query bằng cách mở View | Total 
 hoặc nhấn nút Total trên thanh công cụ; 
• Bước 3: Thiết lập các tuz chọn Total cho các trường một 
 cách phù hợp 
• Vì bài toán yêu cầu tổng hợp thông tin theo từng loại chức 
 vụ, nên Total của trường Tenchucvu là Group By; 
• Trường canboID dùng để đếm số cán bộ từng chức vụ, nên 
 chọn phép tổng hợp là Count. 
 GV. Thiều Quang Trung 29 
TOTAL Query 
 GV. Thiều Quang Trung 30 
 TOTAL Query 
• Total query cung cấp một số phép tổng hợp khác như: 
 – Sum: Tính tổng cộng 
 – Avg: Tính giá trị trung bình 
 – Min: Tìm giá trị nhỏ nhất 
 – Max: Tìm giá trị lớn nhất 
 – Count: Đếm tổng số mẩu tin 
 – Group By: Gom nhóm dữ liệu 
• Chọn Group By: Phân nhóm và sắp xếp các mẫu tin 
 trong từng nhóm theo các trường phân nhóm. 
 – Nhóm là dãy các bản ghi có giá trị như nhau trên các trường 
 phân nhóm 
 GV. Thiều Quang Trung 31 
 TOTAL Query 
• Ví dụ 1: 
 – Cho biết học bổng nhỏ nhất của từng khoa gồm: 
 Mã khoa, Học bổng nhỏ nhất 
 GV. Thiều Quang Trung 32 
 TOTAL Query 
• Ví dụ 2: 
 – Cho biết tổng số sinh viên của từng khoa gồm: 
 Mã khoa, Tên khoa, Tổng số sinh viên 
 GV. Thiều Quang Trung 33 
 TOTAL Query 
• Ví dụ 3: Kết hợp Sum và IIF 
 – Thống kê tổng số sinh viên nam và tổng số sinh 
 viên nữ theo từng khoa 
 GV. Thiều Quang Trung 34 
 CROSSTAB Query 
• Thực tế còn nhiều yêu cầu tổng hợp khác mà Total query 
 không thể đáp ứng được, ví dụ: 
 GV. Thiều Quang Trung 35 
 CROSSTAB Query 
• Cấu trúc một Crosstab, được minh hoạ bởi hình sau: 
 GV. Thiều Quang Trung 36 
 CROSSTAB Query 
• Row heading: tiêu đề các dòng, có chứa các giá trị 
 của các trường nào đó làm tiêu chí thống kê. 
 – Mỗi Crosstab phải có tối thiểu 1 trường làm Row heading; 
• Column heading: tiêu đề các cột, có chứa các giá trị 
 của một trường nào đó làm tiêu chí thống kê. 
 – Mỗi Crosstab chỉ có duy nhất 1 trường làm Column 
 heading; 
• Value là vùng dữ liệu tổng hợp (là các con số). 
 – Chỉ có duy nhất một trường làm Value, 
 – Tương ứng với nó là một phép tổng hợp hoặc: đếm, tính 
 tổng, tính trung bình cộng, max, min,.. 
 GV. Thiều Quang Trung 37 
 CROSSTAB Query 
• Các bước để tạo một Crosstab query. 
 – Ví dụ tạo query đưa ra được bảng tổng hợp sau: 
 GV. Thiều Quang Trung 38 
 CROSSTAB Query 
– Bước 1: Tạo một select query với đầy đủ các trường có 
 liên quan đến phép tổng hợp (không thừa và cũng không 
 thể thiếu một trường nào) như sau: 
 GV. Thiều Quang Trung 39 
 CROSSTAB Query 
– Bước 2: Chọn kiểu Crostab query bằng cách mở Queries 
 |Crosstab Query; 
– Bước 3: Thiết lập các thuộc tính Total cũng như Crosstab 
 cho các trường phù hợp như sau: 
 GV. Thiều Quang Trung 40 
 CROSSTAB Query 
• Cụ thể: 
 GV. Thiều Quang Trung 41 
 MAKE TABLE Query 
• Dữ liệu trong các bảng luôn thay đổi 
• Để lưu lại bảng kết quả của một query tại một thời điểm 
 nào đó, Access đưa ra giải pháp lưu dữ liệu kết quả query 
 ra một bảng (Table) để lưu trữ lâu dài bởi một MAKE TABLE 
 query. 
• Các bước tạo Make table query 
 – Bước 1: Tạo một Select query hoặc một Total query đưa 
 ra được bảng kết quả cần lưu trữ; 
 – Bước 2: Ra lệnh tạo Make table query từ query đang 
 thiết kế và thi hành để thu được bảng kết quả như mong 
 muốn. 
 GV. Thiều Quang Trung 42 
 Append Query 
• Thêm một hay nhiều mẫu tin mới vào bảng 
 dữ liệu từ một bảng khác 
• Mẫu tin mới thêm vào phải thỏa các yêu cầu 
 kiểm tra nhập liệu 
 GV. Thiều Quang Trung 43 
 Append Query 
• Bước 1: Trong cửa sổ Database, chọn thẻ 
 Queries và nhấn chọn New 
• Bước 2: Trong cửa sổ Show Table, chọn bảng 
 chứa dữ liệu nguồn. 
• Bước 3: Trong cửa sổ thiết kế Query, chọn 
 thực đơn Query Append Query và chọn 
 bảng cần thêm dữ liệu 
 GV. Thiều Quang Trung 44 
 Append Query 
• Bước 4: 
 – Kéo các trường bổ sung lên dòng field 
 – Đặt điều kiện chọn các mẫu tin cần thêm ở dòng 
 Criteria 
 GV. Thiều Quang Trung 45 
Append Query 
 GV. Thiều Quang Trung 46 
 DELETE Query 
• Delete Query là một loại Action Query (query hành động). 
 Nó có thể gây thay đổi dữ liệu trong CSDL. Trong trường hợp 
 này, Delete query dùng để xoá các bản ghi từ CSDL thoả mãn 
 những điều kiện nào đó. 
• Ví dụ tạo một Detele query xoá những nhân viên đến tuổi 
 nghỉ hưu ra khỏi CSDL Quản l{ lương nhân viên. Khi query 
 này thi hành, danh sách các nhân viên đến tuổi hưu sẽ bị xoá 
 khỏi bảng canbo 
• Cách tạo query này như sau: 
 GV. Thiều Quang Trung 47 
 DELETE Query 
• Bước 1: Tạo một Select query như sau: 
 GV. Thiều Quang Trung 48 
 DELETE Query 
• Bước 2: đổi query hiện hành thành Delete query 
 bằng cách mở Queries | Delete Query 
 GV. Thiều Quang Trung 49 
 DELETE Query 
• Để thi hành việc xoá cán bộ, nhấn nút Run trên thanh công 
 cụ. Khi đó một hộp thoại cảnh báo xuất hiện: 
• Nhấn Yes để đồng { xoá đi các bản ghi (bản ghi đã xoá không 
 thể phục hồi lại được); nhấn No để huỷ bỏ lệnh. 
 GV. Thiều Quang Trung 50 
 UPDATE Query 
• Update query dùng cập nhật dữ liệu một số trường nào đó 
 trong CSDL. Giống với Delete query, Update query là loại 
 query hành động, làm thay đổi nội dung dữ liệu trên CSDL. 
• Dưới đây là một ví dụ sử dụng Update query để tính giá trị 
 cho cột luongchinh (lương chính) là một trường mới được 
 thêm vào bảng canbo. 
• Bước 1: Tạo một Select query và có chứa bảng canbo và 
 chuyển thành Update query bằng cách mở thực đơn 
 Queries | Update query; hộp thoại thiết kế query như sau: 
 GV. Thiều Quang Trung 51 
UPDATE Query 
 GV. Thiều Quang Trung 52 
 UPDATE Query 
• Bước 2: Thiết lập các trường cần cập nhật dữ liệu bằng 
 cách: 
 – Chọn tên trường cần cập nhầt dữ liệu ở dòng Field; 
 – Gõ vào biểu thức tính giá trị cho trường đó ở dòng 
 Update To; 
 – Gõ vào điều kiện để tính toán nếu cần ở dòng Criteria. 
 – Với yêu cầu trên, luongchinh = hesoluong * 290000 cửa 
 sổ thiết kế query sẽ như sau: 
• Để thi hành việc xoá cán bộ, nhấn nút Run trên thanh công 
 cụ. Khi đó một hộp thoại cảnh báo xuất hiện: 
 GV. Thiều Quang Trung 53 
UPDATE Query 
 GV. Thiều Quang Trung 54 
GV. Thiều Quang Trung 55