Bài giảng Query - Bảng vấn tin

1 BÀI 3: QUERY - BẢNG VẤN TIN 1.1 KHÁI NIỆM Query là công cụ khai thác và xử lý dữ liệu của Microsoft Access, nó có thể đáp ứng các nhu cầu tra cứu về dữ liệu của các bảng dữ liệu thuộc cơ sở dữ liệu. Dạng Query thường được sử dụng nhiều nhất là Select Query (Bảng vấn tin lựa chọn). Bằng loại Query này, ta có thể tham khảo dữ liệu từ nhiều bảng dữ liệu khác nhau của cơ sở dữ liệu, có thể phân tích và hiệu chỉnh dữ liệu ngay trên bảng vấn tin hay sử dụng nó làm cơ sở cho một công việc khác. Bảng kết quả vấn tin được gọi là Dyna Set (Dynamic Set). Một Dyna set giống như một bảng dữ liệu nhưng không phải là một bảng dữ liệu thật sự, nó chỉ là cách tham khảo (cách nhìn động - Dynamic View) đối với dữ liệu được lấy từ nhiều bảng khác nhau mà thôi. 1.2 TẠO MỚI Ví dụ 3.1: Tạo query gồm các field : Số báo danh , Họ Tên , Toán, lý , Hóa , Văn , Tổng Điểm , Lớp Bước 1: Tại cửa sổ Database, chọn Object là Queries , bấm New => hiện hộp thoại New Query. Chọn Design View, Ok => hội thoại Show Table. Bước 2: Xác định các Table/Query tham gia vấn tin tại hộp thoại Show Table. Các nút chọn: Table :Nếu xác định các Table tham gia vấn tin Queries : Nếu xác định các Query tham gia vấn tin Both : Nếu xác định có cả Table và Query tham gia vấn tin. Bước 3: Xác định quan hệ giữa các Table/Query nếu các Table/Query chưa có mối quan hệ Bước 4: Xác định các nội dung của query Bước4.1: Xác định các Field có trong Query z Đối với các field có sẵn ( SoBD, Toan, …) : Kéo mouse tại Field muốn chọn và thả vào vị trí đích (cách này có thể giúp trong trường hợp muốn chèn thêm 1 Field vào một vị trí nào đó) hoặc nhấp kép chuột tại filed muốn chọn. z Đối với Field tính toán thì nhập trực tiếp vào biểu thức. HoTen : Ho & “ “ & Ten Tong : Toan + Ly + Hoa + Van 2 Bước 4.2: Xác định chỉ tiêu sắp xếp tại dòng Sort Ascending: Sắp theo thứ tự tăng dần Descending: Sắp theo thứ tự giảm dần Not Sorted: Không sắp xếp Ví dụ 3.2 : Sắp xếp theo thứ tự tăng dần của Tong Ví dụ 3.3 :Sắp xếp tăng dần theo Toán, và tăng dần theo tổng điểm Chú ý: Nếu chọn nhiều chỉ tiêu sắp xếp thì thứ tự ưu tiên từ trái sang phải. Bước 4.3: Xác định ẩn hiện nội dung các Field tại dòng Show. Ví dụ 3.4: Sắp xếp tăng dần theo Lớp và tăng dần theo Tổng điểm Bước 4.4: Đặt điều kiện lọc dữ liệu tại các dòng Criteria, OR... Ví dụ 3.5 : Lọc các học sinh thuộc lớp 10A1 3 Ví dụ 3.6 : Lọc các học sinh có điểm các môn học đều >4 Ví dụ 3.7 : lọc các học sinh có ít nhất 1 môn có điểm dưới 5 - Các học sinh thuộc Lớp 10A2 và Tổng > 32 - Các học sinh có Tổng>=20 và Tổng < 30 - Các học sinh thuộc Lớp 10A1 hoặc 10A2 - Các học sinh có môn Toán > môn lý - Các học sinh có ngày sinh sau năm 1973 - Các học sinh có năm sinh là 1974 (*/*/1974) - Các học sinh sinh trong tháng 9 Bước 4.4 : Hiển thị một số Record theo lựa chọn Top Values. All: Hiện tất cả các Record của Query 5: Hiện 5 record đầu tiên của Query 25: Hiện 25 record đầu tiên của Query 100: Hiện 100 record đầu tiên của Query 5%: Hiện 5% số record đầu tiên trên tổng số record của Query. 25%: Hiện 25% số record đầu tiên trên tổng số record của Query. Ví dụ 3.8 : tạo query chứa 5 học sinh có tổng điểm cao nhất - Sắp xếp theo thứ tự giảm dần của Tổng Điểm - Chọn Top Values = 5 Bước 5 : Thay đổi các thuộc tính của Field Chọn Field, Chọn View – Properties hoặc bấm vào nút Bước 6: Lưu Query File - Save (hoặc Ctrl + S) - Đặt tên cho Query (Nếu lưu lần đầu tiên). Chú ý: Tên của Query tối đa 64 ký tự và có thể có khoảng trống ở giữa. Bước 7 : Xem kết quả của một Query Tại cửa sổ Database : Chọn Query cần mở, bấm nút Open hoặc nhấp kép vào tên query Tại cửa sổ Design: bấm vào nút hoặc 4 1.3 Phép toán 1.3.1 Các phép toán số học Ký hiệu Tên Cú pháp Nội dung + Cộng A+B - Trừ A-B * Nhân A*B / Chia A/B ^ Lũy thừa A^B \ Phép chia nguyên A\B Lấy thương nguyên trong phép chia A cho B MOD Phép đồng dư A MOD B Lấy số dư trong phép chia A cho B 20 \ 8 = 2 20 Mod 8 =4 Chú ý: Thứ tự ưu tiên trên các phép toán là lũy thừa, nhân, chia, phép chia nguyên, cộng trừ. Ngoài ra có thể sử dụng các dấu () để thay đổi thứ tự ưu tiên. 1.3.2 Phép toán ghép chuỗi Cú pháp: & Công dụng:Ghép vào . Chú ý: Nếu thay phép toán & bằng phép toán + thì có thể cho kết quả sai trong một số trường hợp. Ví dụ: HOTEN: HO & " " & TEN 1.3.3 Các phép toán so sánh Ký hiệu Nội dung = Bằng > Lớn hơn < Nhỏ hơn >= Lớn hơn hoặc bằng <= Nhỏ hơn hoặc bằng Khác (không bằng) Chú ý: Kết quả của một phép toán so sánh là một giá trị kiểu logic (True / False) - So sánh các giá trị kiểu ngày: Theo quy tắc là ngày sau thì lớn hơn ngày trước. - So sánh các giá trị kiểu chuỗi: Việc so sánh 2 giá trị kiểu chuỗi được Access tiến hành theo quy tắc sau: Đầu tiên lấy 2 ký tự đầu bên trái đem so với nhau, ký tự nào đứng trước trong bảng mã ANSI thì kết luận chuỗi đó nhỏ hơn; nếu 2 ký tự đó như nhau thì lại lấy tiếp ký tự kế bên phải của 2 chuỗi đem so với nhau và cứ thế tiếp tục... Lưu ý: Trong quá trình so sánh Access không phân biệt chữ IN và chữ thường. 5 1.3.4 Các phép toán logic: Dùng để xử lý các giá trị logic a. NOT A NOT A True False False True b. AND A B A AND B True True True True False False False True False False False False c. OR A B A OR B True True True True False True False True True False False False Chú ý: Thứ tự ưu tiên của các phép toán logic là NOT, AND, OR. Ngoài ra có thể sử dụng các dấu () để thay đổi thứ tự ưu tiên. Áp dụng cho việc đặt điều kiện trong Query: z Điều kiện đặt ở nhiều cột trên cùng một dòng thì có thể hiểu theo phép toán AND (Điều kiện đồng thời xảy ra). Ví dụ 3.9 : Hiển thị những hóa đơn được lập do nhân viên số 4 hoặc số 5 lập. Show table: HOA DON, NHAN VIEN Field MAHD MANV HO TÊN NGAYLAPHD Criteria 4 Or 5 Ví dụ 3.10 : Hiển thị những hóa đơn được lập sau ngày 15/7/1993 và trước ngày 15/8/1993 Show table: HOA DON, NHAN VIEN Field MAHD MANV HO TÊN NGAYLAPHD Sort Criteria ># 7/15/93 # and <#8/15/93# 6 1.3.5 Phép đối sánh mẫu Cú pháp: LIKE Công dụng: So sánh chuỗi dữ liệu với mẫu dữ liệu được chỉ tiêu chỉ sau LIKE. Cho kết quả bằng True nếu giá trị đem đối chiếu thỏa mãn mẫu dữ liệu, ngược lại cho kết quả bằng False. Các ký tự đại diện (Wildcard charaters) sử dụng trong mẫu dữ liệu: *: đại diện cho nhiều ký tự ?: đại diện cho 1 ký tự #: đại diện cho 1 chữ số (từ 0 đến 9) Ví dụ: Trong file CSDL HOADON.MDB đã có table KHACHHANG để quản lý các công ty khách hàng tại các nơi. Hãy tạo Query hiển thị tên các công ty khách hàng là Công ty Xây dựng tại TP Hồ Chí Minh. Show table: Khach hang Field TenCty Dienthoai Thanhpho Criteria LIKE "* Xây dựng*" "TP HCM" ÁP DỤNGVỀ LIKE: Trong file HOADON. MDB hãy tạo các Query sau 1. Bao gồm tên công ty khách hàng, địa chỉ, điện thoại, thành phố của các công ty có địa chỉ thuộc đường Trần Hưng Đạo. 2. Hiện các hóa đơn lập vào tháng 12 năm 1993 bao gồm: mã HĐ, tên công ty khách hàng, thành phố, ngày lập hợp đồng, tên sản phẩm, đơn vị tính và số lượng. 1.3.6 Phép toán BETWEEN...AND... Cú pháp:Between And (>= and ) Công dụng: Cho kết quả bằng True nếu giá trị đem so sánh nằm trong giới hạn giữa và , ngược lại cho kết quả bằng False. Ví dụ: Hiển thị những hóa đơn được lập từ ngày 15/7/93 đến ngày 15/8/93. Show table: HOA DON Field MAHD NGAYLAPHD Criteria Between #7/15/93# AND #8/15/93# 1.3.7 7. Phép toán IN Cú pháp: IN (, ,...) Công dụng: Cho kết qủa bằng True nếu giá trị cần so sánh bằng một trong các giá trị: <giá trị 1>, ,..., ngược lại sẽ cho kết quả bằng False. Ví dụ: Trong file KQTHI.MDB hãy tạo Query hiển thị những học sinh thuộc các lớp 10A1, 10A2 và 10A3. 7 Show table: DANH SACH Field MAHS HOTEN: HO+" "+TEN LOP Criteria In("10A1", "10A2","10A3") 1.4 Hàm số thông dụng 1.4.1 Hàm về số ABS (N) : Trị tuyệt đối của n ROUND ( N, M) (M>=0) : làm tròn với M số lẻ SQR ( N ) căn bậc 2 của n 1.4.2 Hàm về ngày YEAR(ngày ) năm MONTH ( ngày) :tháng DAY ( ngày ) : ngày WEEKDAY(Ngày ) : thứ ( chủ nhật = 1) DatePart("q", Ngày ) : quí (q: quarter) Date() :ngày hiện tại Now() : ngày giờ hiện tại 1.4.3 Hàm về chuỗi Len (s) : chiều dài của chuỗi s Left ( s , n ) : n ký tự bên trái của s Right ( s , n ) : n ký tự bên phải của s Mid ( s , m , n ) : n ký tự thuộc s tính từ vị trí m Val ( Chuỗi số ) : số LEN ( “BIEN HOA”) = 8 LEFT (“BIEN HOA” , 2) = BI RIGHT (“BIEN HOA” , 2 ) = OA MID (“BIEN HOA” ,2 , 3 ) = IEN 1.4.4 Hàm iif I I F ( Điều kiện , BT1 , BT2 ) VD. 3.10 : SoBD, Hoten, Toan, Ly, Hoa, Van, Tong, Ketqua, Lop Ket qua : Đậu nếu tong>=20, còn lại là rớt Ketqua : iif (Tong >=20 , “Đậu” , “Rớt”) VD.3.11 : SoBD, Hoten, Toan, Ly, Hoa, Van, TBinh, XepLoai, Lop Tbinh : (toan + ly + hoa + van)/4 XepLoai : iif ( Tbinh >= 8 , “Giỏi”, iif ( Tbinh >=7 , “Khá” , iif(Tbinh >=5 , “Trung bình” , “Kém” ))) VD3.12 : SoBD, Hoten, Toan, Ly, Hoa, Van, Tong, Ketqua, Lop 8 Ket qua : Đậu nếu điểm của tất cả các môn >=5, còn lại là rớt Ketqua : iif ( toan>=5 and ly>=5 and hoa>=5 and van>=5 , “Đậu”,”Rớt”) VD3.13 : SoBD, Hoten, Toan, Ly, Hoa, Van, Tong, Ketqua, Lop Ket qua : Đậu nếu Tong>=20 và có nhiều nhất là 2 môn dưới 5, còn lại là rớt KetQua : iif ( tong>=20 and ( iif( toan<5 , 1 , 0) + iif( ly<5 , 1 , 0) + iif( hoa<5 , 1 , 0) + iif( van<5 , 1 , 0)) <=2 , “Đậu” ,”Rớt” ) BÀI TẬP: Trong file CSDL KQTHI.MDB hãy tạo các Query sau: 1. Query: KETQUATHI bao gồm: số báo danh, họ tên HS, lớp học, ngày sinh, giới tính, điểm thi từng môn, tổng điểm và kết quả xếp loại. Biết rằng: - Tổng điểm = Toán + Lý + Hóa + Văn - Kết quả xếp loại đánh giá cho từng học sinh như s au: + Xuất sắc: Nếu tổng điểm >=35 và các môn >=8 + Giỏi: Nếu tổng điểm >=32 và các môn >=7 + Khá: Nếu tổng điểm >=28 và các môn >=6 + Trung Bình: Nếu tổng điểm >=20 và các môn >=4 + Trượt: những HS còn lại. 2. Hãy sắp xếp danh sách học sinh sau khi đã xếp loại như ở query KETQUATHI theo thứ tự từng lớp học, trong cùng lớp thì sắp theo thứ tự Xếp loại, cùng xếp loại thì sắp theo tổng điểm giảm dần. 3. Query: HSTBINH để lên danh sách những HS đạt loại trung bình trong kỳ thi. 4. Query: HS DIEM LIET để lên danh sách những HS bị điểm liệt trong kỳ thi (tức là có ít nhất 1 môn có điểm dưới 3). 5. Query: HS DIEM CAO để lên danh sách đúng 3 học sinh có tổng điểm cao nhất trong kỳ thi. 6. Query: XEPHOCLUC để xếp loại học lực cho những HS đã đậu trong kỳ thi bao gồm họ tên, lớp học, điểm trung bình chung và xếp loại học lực. Biết rằng: - Điểm trung bình thực = tổng điểm 4 môn/4 - Điểm ưu tiên tính như sau: học sinh các lớp có ký tự thứ 3 là A thì được cộng 0.3, các lớp có ký tự thứ 3 là F thì cộng 0.5 và các lớp ký tự thứ 3 là N thì cộng 0.15. - Điểm trung bình chung = Điểm TB thực + Điểm ưu tiên - Xếp loại học lực HS được tính như sau: o Học lực loại A: Nếu điểm trung bình chung >8 và thi đạt loại khá trở lên. o Học lực loại B: Nếu điểm trung bình chung > 6.5 o Học lực loại C: những HS còn lại. 9 BÀI TẬP Tạo Query đơn giản 1. Tạo Query HOA DON 1 chứa các filed MaHD,LoaiPhieu,NgayLapHD, TenSP, Donvitinh, Dongia, Soluong, Thanh Tien. Với Thanh Tien = Soluong* Dongia và định dạng theo các yêu cầu sau : Format là fixed, decimal places là 2 và caption là Thành Tiền. Các tên field còn lại đều có Caption là tiếng việt. 2. Tạo Query BAO CAO 1 chứa các field MaKH,NgayNV, TenCty, Thanhpho, TenSP, Donvitinh, Dongia. Soluong,Thanhtien, LoaiPhieu. Với Thanh tien và định dạng tương tự câu 1. Sắp xếp theo thứ tự tăng dần của maKH. 3. Tạo Query BAO CAO 2 chứa các field MaNV, TenNV. TenSP, Donvitinh, Dongia, Soluong, ThanhTien. Với TenNV là họ và tên của nhân viên, Thanhtien và định dạng tương tự câu 1. Sắp xếp theo thứ tự tăng dần của MaNV, Nếu MaNV trùng nhau thì sắp theo thứ tự giảm dần của số lượng. Thực hiện các yêu cầu sau 1. Tạo các Query có các field giống như Query HOADON 1 nhưng chỉ xuất hiện các record thỏa yêu cầu. − Tạo Query HOA DON 11 sao cho chỉ xuất hiện các record có MaHD = 00001. − Tạo Query HOA DON 12 sao cho chỉ xuất hiện các record có TenSP = rượu. − Tạo Query HOA DON 13 sao cho chỉ xuất hiện các record có SoLuong > 20. − Tạo Query HOA DON 14 sao cho chỉ xuất hiện các record có MaHD là 00002 hoặc 00003. − Tạo Query HOA DON 15 sao cho chỉ xuất hiện các record có TenSP là rượu hoặc nem. − Tạo Query HOA DON 16 sao cho chỉ xuất hiện các record có SoLuong > 20 hoặc <10. − Tạo Query HOA DON 17 sao cho chỉ xuất hiện các record có SoLuong > =10 và <20. − Tạo Query HOA DON 18 sao cho chỉ xuất hiện các record có MaHD là 00004 và SoLuong > 15. − Tạo Query HOA DON 19 sao cho chỉ xuất hiện các record có TenSP là rượu hoặc gia vị và SoLuong > 15. − Tạo Query HOA DON 20 sao cho chỉ xuất hiện các hóa đơn được lập trong năm 2003. − Tạo Query HOA DON 21 sao cho chỉ xuất hiện các hóa đơn được lập trong tháng 9 năm 2003. − Tạo Query HOA DON 22 sao cho chỉ xuất hiện các hóa đơn được lập trong quí 1 năm 2003. 2. Tạo các query có các field giống như query BAO CAO 1 nhưng chỉ xuất hiện các record thỏa yêu cầu. − Query BAO CAO 11 sao cho chỉ xuất hiện các record có ThanhPho là Hà Nội. − Query BAO CAO 12 sao cho chỉ xuất hiện các record có DonViTinh là Chai. − Query BAO CAO 13 sao cho chỉ xuất hiện các record có MaKH là KH002. − Query BAO CAO 14 sao cho chỉ xuất hiện các record có MaKH là KH004 hoặc KH005. − Query BAO CAO 15 sao cho chỉ xuất hiện các record có TenSP là Táo hoặc Nem. − Query BAO CAO 16 sao cho chỉ xuất hiện các record có ký tự đầu của TenSP là C và SoLuong > 10. − Query BAO CAO 17 sao cho chỉ xuất hiện các record có DonViTinh là Kg và SoLuong > 20. − Query BAO CAO 18 sao cho chỉ xuất hiện các record có ThanhPho là Khánh Hòa, MaKH là KH003 và SoLuong >= 15. − Query BAO CAO 19 sao cho chỉ xuất hiện các record có TenSP là Táo hoặc có SoLuong < 10. 10 3. Tạo các Query có các field giống như Query BAOCAO 2 nhưng chỉ xuất hiện các record thỏa yêu cầu. − Query BAO CAO 21 sao cho chỉ xuất hiện các record có họ là Nguyễn. − Query BAO CAO 22 sao cho chỉ xuất hiện các record có Ngay NV trước ngày 01/04/2001 − Query BAO CAO 23 sao cho chỉ xuất hiện các record có Nữ = -1. − Query BAO CAO 24 sao cho chỉ xuất hiện các record có MaNV là 1 hoặc 4. − Query BAO CAO 25 sao cho chỉ xuất hiện các record có tên là Ngọc hoặc Nga. − Query BAO CAO 26 sao cho chỉ xuất hiện các record sau cho trong tên có chữ Ngọc và NgayNV sau ngày 29/03/2000. Thực Hiện Tính Toán 1. Tạo các Query dựa vào query HOA DON 1 với Thanh Tien được tính như sau : − Query T- HOA DON 1 với Thanh Tien = SoLuong * Dongia. Nhưng nếu SoLuong > 30 thì giảm 10% và ngược lại thì không giảm. − Query T-HOADON 2 với Thanh Tien = Soluong * Dongia. Nhưng nếu MaHD = 00001 thì giảm 10%. MaHD = 00002 thì giảm 20% ngược lại thì không giảm. − Query T-HOADON 3 với Thanh Tien = SoLuong * Dongia. Nhưng nếu SoLuong < 5 thì tăng 5%, SoLuong <10 thì tăng 8%, SoLuong < 20 thì tăng 10%, SoLuong <25 thì tăng 15% còn lại tăng 20%. − Query T-HOADON 4 với Thanh Tien = SoLuong * Dongia. Thêm vào field thuế theo yêu cầu sau : nếu MaHD = 00004 và TepSP là Nem thì Thue = Thanh Tien * 10%, ngược lại thuế =0. − Query T-HOADON 5 với Thanh Tien = SoLuong * Dongia. Thêm vào field thuế theo yêu cầu sau : nếu MaHD = 00004 và TepSP là Bơ thì Thue = 10% Thanh Tien MaHD = 00006 và TepSP là Rượu thì Thue = 15% Thanh Tien Ngược lại Thue = 20% Thanh Tien − Query T-HOADON 6 với Thanh Tien = SoLuong * Dongia. Thêm vào field thuế theo yêu cầu sau : nếu DonViTinh là kg hoặc Chai thì Thuế = 10% ThanhTien, ngược lại Thuế = 0 − Query T-HOADON 7 với thanh Tien = SoLuong * Dongia. Thêm vào filed thuế theo yêu cầu sau : nếu ký tự bên trái của DonViTinh là C hoặc K thì Thuế = 10% Thanh Tien ký tự bên phải của DonViTinh là G thì Thuế = 15% Thanh Tien ngược lại Thuế = 0