Cơ sở dữ liệu - Chương 3: Truy vấn dữ liệu (query)

Nội dung text: Cơ sở dữ liệu - Chương 3: Truy vấn dữ liệu (query)

1. Chương 3 TRUY VẤN DỮ LIỆU (QUERY)
2. ? 1. Vấn Đề
3.  1. Muốn biết một hoá đơn bán hàng của khách hàng nào (tên khách hàng) và do nhân viên nào bán (tên nhân viên). Select Q QLBH
4.  2. Tìm tên và địa chỉ các khách hàng mua hàng trước ngày 1/1/2004 Select Q QLBH
5.  3. Ghép Hokh và TenKh thành cột họvàtên. Select Q QLBH
6.  4. Tính tiền chi tiết cho từng hóa đơn (SoHDB, MaMH, SoLuong, DonGiaBan, ThanhTien): Select Q QLBH
7.  5. Tính thành tiền cho từng hóa đơn (nhóm dữ liệu) Group by Sum QLBH
8. 6. Thống kê khách hàng mua hàng hóa nào với số lượng bao nhiêu? To Crosstab Q QLBH
9. 7. Tạo Table từ các thông tin tìm kiếm được. Ví dụ: ? Muốn tạo Table gồm các thuộc tính KHACHMUA(SoHDB,HoTenKH,HoTenNV) QLBH
10. 8. Sửa đơn giá bán của mặt hàng Cam lên 10%. 9. Xoá tất cả các mặt hàng do nhà cung cấp Bitis cung cấp. QLBH
11. 2. CÁC LOẠI TRUY VẤN
12. 2. CÁC LOẠI TRUY VẤN I. Nhóm truy vấn xử lý dữ liệu (không làm thay đổi dữ liệu trên table): ➢ Truy vấn chọn (Select Query)  Nhóm dữ liệu  Truy vấn có tham số ➢ Truy vấn chéo (Crosstab Query) II. Nhóm truy vấn hành động-Action Query(làm thay đổi dữ liệu trên Table): ➢ Truy vấn tạo bảng (Make-Table Query) ➢ Truy vấn cập nhật (Update Query) ➢ Truy vấn thêm (Append Query) ➢ Truy vấn xóa (Delete Query)
13. 2.1. Truy vấn chọn lựa (Select Query): 2.1.1. Công dụng: Chọn lựa, rút trích dữ liệu trên một số cột của một hay nhiều bảng theo một điều kiện nào đó (có thể có điều kiện hoặc không). Trên truy vấn chọn lựa, có thể: - Tính toán - Nhóm dữ liệu (Group) Điều kiện có thể tĩnh hoặc động. Khi điều kiện là động người ta còn gọi là truy vấn có tham số.
14. 2.1.2. Các bước tạo truy vấn lựa chọn1 (Select Query) B1. -> Query -> New->Design View -> OK B2. Đưa các bảng (table) hoặc các truy vấn (query) liên quan đến các dữ liệu truy vấn vào cửa sổ truy vấn bằng cách đúp vào tên bảng. ( mục Table trong lưới QBE) B3. Xác định tên các cột cần xuất hiện trong kết quả truy vấn bằng cách đúp vào tên trường. (mục Field trong lưới QBE)
15. Các bước tạo truy vấn lựa chọn2 (Select Query) B4. Khai báo các thành phần của lưới QBE: - Sort: - Ascending: Xếp dữ liệu tăng dần. - Descending: Xếp dữ liệu giảm dần. - Not sorted: Không sắp xếp. - Show: -  : Cho hiện cột. - : Không cho hiện cột.
16. Các bước tạo truy vấn lựa chọn3 (Select Query) (tt) - Criteria: - Điều kiện lọc dữ liệu (nếu có yêu cầu). Chú ý: - Điều kiện lọc đặt vào hàng Criteria và các hàng dưới. - Điều kiện hoặc giưã các cột: đặt trên những hàng khác nhau. - Điều kiện và giưã các cột: đặt trên cùng 1 hàng. - Dùng các toán tử. B5. Lưu bảng truy vấn: Ctrl + S hay File -> Save
17. Một số thao tác thường dùng - Xem trước kết quả truy vấn: (menu View -> Datasheet view) - Trở lại cửa sổ truy vấn: (menu View -> Design View) - Thực hiện một truy vấn: (menu Query -> Run)
18. 2.1.3. Ví dụ tạo truy vấn chọn lựa (Select Query)1 Tạo Query giải quyết các yêu cầu sau: 1. Muốn biết một hoá đơn bán hàng của khách hàng nào (tên khách hàng) và do nhân viên nào bán (tên nhân viên). 2. Liệt kê danh sách khách mua hàng (HọKH, TênKH, Địa chỉ, Điện thoại, Họ tên NV, Số HD bán, Ngày bán, Tên MH, Số lượng, Đơn giá), sắp xếp theo tênKH, nếu tên trùng nhau thì sắp xếp theo họ. 3. Lập danh sách nhân viên có giới tính là nữ 4. Tìm tên và địa chỉ các khách hàng có giới tính là nam mua hàng trước ngày 1/1/2004
19. 2.1.3. Ví dụ tạo truy vấn chọn lựa (Select Query)2 5.  Ghép Hokh và TenKh thành cột họvàtên. 6.  Tính tiền chi tiết cho từng hóa đơn (SoHDB, MaMH, SoLuong, DonGiaBan, ThanhTien)
20. ☺ Tạo một cột mới Tên cột mới : biểu thức Ví dụ1: Họ và tên: [HoKH] & [TênKH] Ví dụ 2: Thành tiền : [SoLuong]*[DonGiaBan]
21. ☺ Một số toán tử được sử dụng trong biểu thức truy vấn1 ➢Các toán tử so sánh: =, , >, =, =20 and <= 30
22. Một số toán tử được sử dụng trong biểu thức truy vấn2 ➢Các hàm ngày, tháng, năm: Ngày = 26/10/2004 Day([ngày]) = 26 Month([ngày]) = 10 Year([ngày]) = 2004 DatePart(‘q’, [Ngày]) = 4 (quý) DatePart(‘m’, [Ngày]) =10 (tháng) DatePart(‘yyyy’, [Ngày]) = 2004 (năm)
23. Một số toán tử được sử dụng trong biểu thức truy vấn3 ➢ Đúng với mẫu đưa ra: Like Ví dụ: Like“40*”: tất cả những chuỗi có hai ký tự đầu là 40. Like“???C*”: tất cả những chuỗi có ký tự thứ 4 là C. ➢ Các toán tử logic: and, or, not ➢ Các phép toán số học: +, -, *, /,^(lũythừa), Mod
24. Một số toán tử được sử dụng trong biểu thức truy vấn3 ➢Tên trường đặt giữa hai dấu ngoặc vuông: [tên trường] Ví dụ: [MaKH] ➢Dữ liệu kiểu ngày đặt giữa hai dấu # và #: #25/09/75#
25. Một số toán tử được sử dụng trong biểu thức truy vấn4 ➢Nối hai chuỗi ký tự: & Ví dụ: Nối trường HọKH và trường TênKH thành trường HọTenKH HoTenKH = [HoKH] & [TenKH]
26. Một số toán tử được sử dụng trong biểu thức truy vấn5 ➢Câu lệnh điều kiện: iif(điềukiện, kếtquảcủađiềukiệnđúng, kếtquảcủađiềukiệnsai) Vd1: iif([GioiTinh]=Yes, “NU”, “NAM”) Vd2: iif([DonGiaBan])>100000, [DonGiaBan]*1.2, [DonGiaBan])
27. 2.2. Nhóm dữ liệu (Group)
28. 2.2.1. NHÓM DỮ LIỆU Ý nghĩa-Cách thực hiện Ta có thể tính toán dữ liệu theo từng nhóm có tính chất tổng cộng, thống kê tổng hợp số liệu mà không quan tâm đến chi tiết số liệu bên trong. Cách thực hiện như sau: - View→Totals để chèn thêm dòng Total vào vùng lưới QBE. - Dùng các hàm tính toán ở dòng Total để thực hiện các phép tính cho các cột trong truy vấn:
29. Hàm Ý nghĩa Group by Nhóm dữ liệu trên cột này Avg Tính trung bình cho cột này (số) Sum Tính tổng Count Đếm tổng số Min Tìm giá trị nhỏ nhất Max Tìm giá trị lớn nhất First Tìm giá trị đầu tiên Last Tìm giá trị cuối cùng Expression Biểu thức tính toán từ các cột dữ liệu khác trong bảng Where Điều kiện lọc dữ liệu cho truy vấn Không được phép hiện cột này lên
30. 2.2.2. Ví dụ nhóm dữ liệu1 Vd1: Tính thành tiền cho từng hóa đơn (SoHD, ThanhTien) Vd2: Hãy liệt kê toàn bộ khách hàng và tổng số hoá đơn của mỗi khách hàng (MãKH, Tông số HD). ??? Nếu chỉ liệt kê (TênKH, Tổng số HD) thì điều gì xảy ra ?
31. 2.2.2. Ví dụ nhóm dữ liệu2 Vd3: Liệt kê mỗi khách hàng trong từng quí của năm 2004 đã mua bao nhiêu hàng hóa. Vd4: Liệt kê mỗi khách hàng nếu phân theo từng loại hàng hóa thì đã mua bao nhiêu hàng hóa trước ngày 1/1/2002.
32. 2.3. Tham số trong truy vấn Query có tham số
33. 2.3.1. Tham số trong truy vấn Thực hiện các truy vấn theo một điều kiện “động” nào đó. (do người dùng nhập) Ví dụ: Hiển thị các thông tin của nhân viên với mã nhân viên nhập vào từ bàn phím. ◼ Hiển thị các thông tin của nhân viên có mã số là 05. (điều kiện “tĩnh”)
34. 2.3.2. Cách tạo tham số trong truy vấn Nhập dòng thông báo nhắc nhập trong hai dấu ngoặc vuông [ và ] vào dòng điều kiện (Criteria) tại cột ta muốn lọc. Có thể dùng các toán tử, nhập nhiều tham số cho một hoặc nhiều cột. (như nhập điều kiện lọc tĩnh) Vd1: Hiển thị các thông tin của nhân viên với mã nhân viên nhập vào từ bàn phím. Vd2: Lập danh sách các khách hàng mua hàng trong khoảng thời gian nào đó do người dùng nhập vào. Query có tham số
35. 2.4. Tạo Truy vấn chéo (Crosstab Query)
36. 2.4.1. Truy vấn chéo (Crosstab Query): Sử dụng dạng truy vấn này khi muốn thống kê, tổng hợp các dữ liệu theo dạng nằm ngang dù các dữ liệu được lưu trong bảng dưới dạng từng dòng:
37. 2.4.1. Truy vấn chéo (Crosstab Query)2: Ví dụ 1: Thống kê khách hàng mua hàng hóa nào với số lượng bao nhiêu? Column heading Row heading Value QLBH
38. 2.4.2. Cách tạo truy vấn chéo: B1: New/Design view và chọn các Table chứa dữ liệu nguồn B2: Query/Crosstab Query: ◼ Khai báo các dòng: như Select Query ◼ Crosstab:  Row heading: cột này làm giá trị trên hàng.  Column heading: cột này làm giá trị trên cột  Value: cột này làm giá trị cho bảng  No shown: không cho hiện cột này. Ví dụ 1: Thống kê khách hàng mua hàng hóa nào với số lượng bao nhiêu?
39. Lưu ý: Có thể có nhiều cột làm Row heading, nhưng chỉ một cột duy nhất làm Column heading, một cột duy nhất làm Value. Ví dụ:
40. 2.4.3. Truy vấn chéo – Ví dụ Có thể có nhiều cột làm Row heading, nhưng chỉ một cột duy nhất làm Column heading, một cột duy nhất làm Value. Ví dụ:
41. 2.5. Tạo truy vấn hành động (Action Query)
42. 2.5.1. Truy vấn tạo bảng (Make-Table Query) Dùng Make-Table Query để: ◼ chép kết quả truy vấn của loại Select Query ra một Table mới. ◼ chép kết quả truy vấn của loại Crosstab Query ra một Table mới nhưng dưới dạng Select Query (Group). Chú ý: Bảng mới tạo ra không có khóa.
43. Các bước tạo truy vấn tạo bảng: (Make-Table Query): ◼ B1: Tạo như Select Query. ◼ B2: Query → Make-Table Query: 1. Gõ vào tên Table được tạo ra 2. Đặt Table mới này trong tập tin cơ sở dữ liệu hiện hành 3. Xác định xong 2. Đặt Table mới này bên trong một tập tin cơ sở dữ liệu khác Chọn Query→Run để thực hiện tạo table mới.
44. 2.5.2. Tạo truy vấn xoá (Delete Query) Dùng Delete Query để: ◼ Xóa các dòng dữ liệu thoả điều kiện nào đó trong một hay nhiều bảng. Cẩn thận: không thể phục hồi sau khi xoá.
45. Các bước tạo truy vấn xoá bảng: (Delete Query): B1. -> Query -> New->Design View -> OK B2. Chọn bảng (table) chứa dữ liệu muốn xóa. B3. Query → Delete Query: xuất hiện dòng Delete - Xoá các bản ghi: drag dấu * từ bảng vào lưới: từ Form xuất hiện ở hàng Delete. - Xoá có điều kiện: Double Click vào tên cột tham gia vào điều kiện: từ Where sẽ xuất hiện trên hàng Delete. Khai báo điều kiện như trong Select Query. Chọn Query→Run để thực hiện xoá các bản ghi.
46. 2.5.3. Tạo truy vấn thêm (Append Query) Thêm dữ liệu từ bảng này vào bảng khác.
47. Các bước tạo truy vấn thêm: (Append Query): B1: Tạo như Select Query. B2: Query → Append Query: 1. Gõ tên Table muốn chép dữ liệu đến (Table đích) 2. Nếu Table đích ở trong tập tin cơ sở dữ liệu hiện hành 3. Xác định xong 2. Nếu Table đích trong tập tin cơ sở dữ liệu khác Chọn Query→Run để thực hiện thêm dữ liệu.
48. 2.5.4. Tạo truy vấn sửa (Update Query) Các bước tạo truy vấn sửa: ◼ B1: Tạo như Select Query. ◼ B2: Query→Update Query: Xuất hiện dòng Update To ◼ Trong ô Update To của các thuộc tính muốn cập nhật: điền vào giá trị hay biểu thức dùng để thay đổi nội dung. ◼ Chọn Query→Run để thực hiện việc sửa.
49. Relationship bài toán quản lý khách hàng Crosstab Query