Bài giảng Hệ quản trị dữ liệu Access - Chương 3: Thao tác dữ liệu với Queries - Nguyễn Văn Trãi

Thạc sĩ Nguyễn Văn Trãi LOGO Chương 3: Thao tác dữ liệu với Queries Ths.Nguyễn Văn Trãi Chương 3: Thao tác dữ liệu với Queries 1. Tổng quan 2. Các loại truy vấn Ths.Nguyễn Văn Trãi 1. Tổng quan • Truy vấn (Queries) được sử dụng để lọc dữ liệu; thực hiện các phép tính với dữ liệu; thêm, sửa hoặc xóa dữ liệu ở các bảng. • Một truy vấn (Query) giải quyết một yêu cầu trên cơ sở dữ liệu. • Kết quả trả về là dữ liệu hoặc hoạt động diễn ra trên cơ sở dữ liệu đó. Ths.Nguyễn Văn Trãi Cửa sổ thiết kế truy vấn Vùng chứa nguồn dữ liệu của truy vấn Vùng lưới thiết kế của truy vấn 1. Tổng quan Ths.Nguyễn Văn Trãi Cửa sổ thiết kế truy vấn 1. Tổng quan • Nguồn dữ liệu của một truy vấn là các bảng dữ liệu hoặc là các truy vấn khác có chứa dữ liệu cần xử lý. • Lưới thiết kế truy vấn là nơi chứa các trường cần hiển thị trong kết quả của truy vấn và các trường đặt điều kiện để xử lý dữ liệu. Ths.Nguyễn Văn Trãi Các thành phần có trong lưới thiết kế truy vấn: 1. Tổng quan • Field: Chứa tên trường cần hiển thị trong kết quả hoặc tên trường đặt điều kiện. • Table: Chứa tên nguồn của các trường tương ứng ở mục Field. • Sort: Dùng để sắp xếp dữ liệu. • Show: Cho phép các trường ở mục Field hiển thị hay không hiển thị trong kết quả truy vấn. • Criteria: Nơi chứa giá trị điều kiện của trường đặt điều kiện tương ứng. • Or: Nơi chứa giá trị điều kiện hoặc của trường đặt điều kiện tương ứng. Ths.Nguyễn Văn Trãi 2. Các loại truy vấn a. Truy vấn lọc dữ liệu b. Truy vấn tham số c. Truy vấn thống kê d. Truy vấn tham chiếu chéo e. Truy vấn hành động Ths.Nguyễn Văn Trãi a. Truy vấn lọc dữ liệu Là loại truy vấn dùng để trích lọc dữ liệu theo điều kiện cho trước. VD: Lọc danh sách sinh viên lớp 13CNTT1. Thiết kế: - B1: Thẻ Create → Nhóm Other → Query Design Ths.Nguyễn Văn Trãi a. Truy vấn lọc dữ liệu Thiết kế: - B2: + Chọn nguồn dữ liệu tại Show Table → Add. + Close đóng Show Table Ths.Nguyễn Văn Trãi a. Truy vấn lọc dữ liệu Thiết kế: - B3: Đưa trường cần hiển thị trong kết quả truy vấn và trường đặt điều kiện vào lưới thiết kế. Ths.Nguyễn Văn Trãi a. Truy vấn lọc dữ liệu Thiết kế: - B4: Đặt điều kiện cần lọc dữ liệu tại Criteria của trường đặt điều kiện tương ứng. Ths.Nguyễn Văn Trãi a. Truy vấn lọc dữ liệu Thiết kế: - B5: + Lưu, đặt tên cho truy vấn. + Chọn View hoặc Run trong thẻ Design để xem thử kết quả truy vấn. Ths.Nguyễn Văn Trãi Quy định dấu rào Dấu rào Môi trường sử dụng “ ” Rào chuỗi [ ] Rào tên trường # # Rào ngày/tháng/năm Ths.Nguyễn Văn Trãi Toán tử Ý nghĩa Null Rỗng, không có dữ liệu Is Chính là Not Không phải, phủ định And Và Or Hoặc Một số toán tử thông dụng Ths.Nguyễn Văn Trãi Một số toán tử thông dụng Toán tử Ý nghĩa > Lớn hơn >= Lớn hơn hoặc bằng < Nhỏ thua <= Nhỏ thua hoặc bằng Khác Ths.Nguyễn Văn Trãi • LIKE “”: Lọc dữ liệu hao hao giống giá trị tương đối. Vd: Lọc dssv họ Lê Một số toán tử thông dụng Ths.Nguyễn Văn Trãi  Có 2 ký tự đại diện là:  Ký tự * để biểu diễn bất kỳ giá trị nào;  Ký tự ? để biểu diễn một ký tự bất kỳ.  Ký tự # thay thế cho 1 ký tự số Một số ví dụ minh hoạ toán tử LIKE:  Like „Nguyễn*‟ - lọc ra những người họ nguyễn. 6 ký tự đầu là Nguyễn, các ký tự còn lại là thoải mái;  Like „*Đức*‟ - lọc ra những người có Họ hoặc Đệm hoặc Tên là Đức;  Like „*/*/1980‟ - lọc ra những người sinh năm 1980;  Like „*/11/*‟ - lọc ra những người sinh tháng 11;  Like „*/*/198?‟ - lọc ra những người sinh từ năm 1980 đến năm 1989;  Like „[A-H]*‟ – lọc ra những người có tên bắt đầu từ A-H Toán tử LIKE “” Một số toán tử thông dụng Ths.Nguyễn Văn Trãi • Between And :Lọc dữ liệu trong khoảng đến Vd: Lọc ra những sinh viên có năm sinh từ 1980 đến 1985. Một số toán tử thông dụng Ths.Nguyễn Văn Trãi Một số hàm thông dụng Các hàm xử lý dữ liệu kiểu chuỗi: • LEFT(st, n): kết quả trả về là n ký tự đầu tiên của chuỗi st. Vd: Left(“Việt Tiến”,4)  “Việt” • RIGHT(st, n): kết quả trả về là n ký tự cuối của chuỗi st. Vd: Right(“Việt Tiến”,4)  “Tiến” • MID(st, m, n): kết quả trả về là n ký tự nằm trong chuỗi st tính từ vị trí thứ m. vd: Mid(“Trường Việt Tiến”,8,4)  “Việt” Ths.Nguyễn Văn Trãi Một số hàm thông dụng Các hàm xử lý dữ liệu kiểu ngày: • DATE(): kết quả trả về là ngày tháng năm hiện tại của hệ thống. • DAY(ngày/tháng/năm): kết quả trả về là giá trị ngày. Vd: Day(#02/09/2015#)  02 • MONTH(ngày/tháng/năm): kết quả trả về là giá trị tháng. Vd: Month(#02/09/2015#)  09 • YEAR(ngày/tháng/năm): kết quả trả về là giá trị năm. Vd: Year(#02/09/2015#)  2015 Ths.Nguyễn Văn Trãi Một số hàm thông dụng Một số hàm tính toán: • SUM(các tham số): Tính tổng các tham số • AVG(các tham số): Tính trung bình cộng các tham số • COUNT(các tham số): Đếm các tham số • MAX(các tham số): Lấy giá trị lớn nhất trong các tham số • MIN(các tham số): Lấy giá trị nhỏ nhất trong các tham số • IIF(biểu thức đk, giá trị đúng, giá trị sai): Nếu biểu thức đk đúng thì kết quả trả về là giá trị đúng, ngược lại là giá trị sai. Ths.Nguyễn Văn Trãi b. Truy vấn tham số Là truy vấn lọc dữ liệu với điều kiện lọc được nhập vào từ bàn phím mỗi khi mở truy vấn xem kết quả. Thiết kế truy vấn tham số tương tự như thiết kế truy vấn lọc dữ liệu, chỉ khác ở cách đặt điều kiện. Tại nơi đặt điều kiện của truy vấn tham số ta không gõ điều kiện trực tiếp vào đây, mà ta thay thế bằng hộp thoại yêu cầu nhập điều kiện. Hộp thoại này được tạo bằng cách: gõ vào nơi đặt điều kiện cặp dấu ngoặc vuông [ ], trong cặp dấu này ta gõ lời yêu cầu nhập điều kiện. Ths.Nguyễn Văn Trãi VD: Lọc danh sách sinh viên của một lớp bất kỳ. b. Truy vấn tham số Ths.Nguyễn Văn Trãi Khi xem kết quả truy vấn sẽ xuất hiện hộp thoại Nhập lớp, ta gõ lớp cần lấy danh sách  OK. Gõ lớp cần lấy danh sách b. Truy vấn tham số Ths.Nguyễn Văn Trãi c. Truy vấn thống kê Là loại truy vấn dùng phân tích, thống kê số liệu. VD: Thống kê số lượng sinh viên ở mỗi lớp. Để thiết kế được truy vấn thống kê ta cần xác định các thông tin sau: + Trường nhóm (nếu có) + Trường tính toán, thống kê. + Trường đặt điều kiện (nếu có) Ths.Nguyễn Văn Trãi c. Truy vấn thống kê Ví dụ: Thống kê số lượng sinh viên ở mỗi lớp. + Trường nhóm: MALOP + Trường tính toán, thống kê: MASV + Trường đặt điều kiện: Không có Ths.Nguyễn Văn Trãi c. Truy vấn thống kê Thiết kế: B1: Thẻ Create → Nhóm Other → Query Design B2: Chọn nguồn dữ liệu tại Show Table → Add. B3: Đưa trường tính toán thống kê, trường đặt điều kiện (nếu có), trường nhóm (nếu có) vào lưới thiết kế. Ths.Nguyễn Văn Trãi c. Truy vấn thống kê Thiết kế: B4: Thẻ Design → chọn Totals Chọn phép toán cho các trường tại dòng Total tương ứng. Ths.Nguyễn Văn Trãi c. Truy vấn thống kê Thiết kế: B5: Đặt điều kiện cần lọc dữ liệu để tính toán thống kê tại Criteria của trường đặt điều kiện tương ứng (nếu có). - Lưu, đặt tên cho truy vấn. Ths.Nguyễn Văn Trãi c. Truy vấn thống kê Các phép toán có tại Totals: Phép toán Công dụng Sum Tính tổng Count Đếm Avg Tính giá trị trung bình First Giá trị đầu tiên Ths.Nguyễn Văn Trãi c. Truy vấn thống kê Các phép toán có tại Totals: Phép toán Công dụng Last Mẫu tin cuối Group By Nhóm dữ liệu Where Đặt điều kiện Min Giá trị nhỏ nhất Max Giá trị lớn nhất Ths.Nguyễn Văn Trãi Truy vấn tham chiếu chéo Dùng thống kê số liệu chi tiết theo từng chủng loại. VD: Thống kê số lượng sinh viên theo từng lớp và từng giới tính. Ths.Nguyễn Văn Trãi d. Truy vấn tham chiếu chéo Để làm được truy vấn tham chiếu chéo ta cần xác định các thông tin sau: + Trường làm tiêu đề hàng (trường nhóm đầu tiên) + Trường làm tiêu đề cột (trường nhóm thứ hai) + Trường tính toán + Trường đặt điều kiện (nếu có) Ths.Nguyễn Văn Trãi d. Truy vấn tham chiếu chéo VD: Thống kê số lượng sinh viên theo từng lớp và từng giới tính. + Trường làm tiêu đề hàng: MALOP + Trường làm tiêu đề cột: PHAI + Trường tính toán: MASV + Trường đặt điều kiện: Không có Ths.Nguyễn Văn Trãi Thiết kế: B1: Thẻ Create → Nhóm Other → Query Design B2: Chọn nguồn dữ liệu tại Show Table → Add. B3: Đưa trường làm tiêu đề hàng, tiêu đề cột, tính toán thống kê, trường đặt điều kiện (nếu có) vào lưới thiết kế. d. Truy vấn tham chiếu chéo Ths.Nguyễn Văn Trãi Thiết kế: B4: Thẻ Design → Crosstab Chọn phép toán tương ứng cho các trường tại dòng Total. Tại dòng Crosstab: + Chọn Row heading cho trường làm tiêu đề hàng. + Chọn Column heading cho trường làm tiêu đề cột. + Chọn Value cho trường tính toán + Trường đặt điều kiện để trống. d. Truy vấn tham chiếu chéo Ths.Nguyễn Văn Trãi Thiết kế: B5: Đặt điều kiện cần lọc dữ liệu để tính toán thống kê tại Criteria của trường đặt điều kiện tương ứng (nếu có). Sau đó Lưu, đặt tên cho truy vấn. d. Truy vấn tham chiếu chéo Ths.Nguyễn Văn Trãi Là loại truy vấn xử lý dữ liệu trực tiếp tại các bảng. Gồm các loại truy vấn:  Truy vấn tạo bảng  Truy vấn nối bảng  Truy vấn cập nhật dữ liệu  Truy vấn xóa dữ liệu e. Truy vấn hành động Ths.Nguyễn Văn Trãi e. Truy vấn hành động Truy vấn tạo bảng: Là truy vấn lọc dữ liệu từ cơ sở dữ liệu, sau đó tạo bảng mới lưu dữ liệu đã lọc. VD: Dùng truy vấn tạo bảng lưu danh sách sinh viên lớp 13CNTT1. Ths.Nguyễn Văn Trãi Truy vấn tạo bảng: Thiết kế: B1: Thẻ Create → Nhóm Other → Query Design B2: Chọn nguồn dữ liệu tại Show Table → Add. B3: Đưa trường cần hiển thị trong kết quả truy vấn và trường đặt điều kiện vào lưới thiết kế. Sau đó đặt điều kiện cần lọc dữ liệu tại Criteria của trường đặt điều kiện tương ứng. e. Truy vấn hành động Ths.Nguyễn Văn Trãi Truy vấn tạo bảng: Thiết kế: B4: Thẻ Design → Make Table Đặt tên bảng mới cần tạo → OK e. Truy vấn hành động Ths.Nguyễn Văn Trãi Truy vấn tạo bảng: Thiết kế: B5: Lưu, đặt tên truy vấn. Thẻ Design → Run → Yes để cho kết quả. Lưu ý: Kết quả truy vấn nằm trong đối tượng Tables e. Truy vấn hành động Ths.Nguyễn Văn Trãi Truy vấn nối bảng: Là truy vấn nối dữ liệu của một bảng vào một bảng khác có cùng cấu trúc. VD: Dùng truy vấn nối dữ liệu bảng 13CNTT1 vào bảng 13CNTT2. e. Truy vấn hành động Ths.Nguyễn Văn Trãi Truy vấn nối bảng: Thiết kế: B1: Thẻ Create → Nhóm Other → Query Design e. Truy vấn hành động Ths.Nguyễn Văn Trãi Truy vấn nối bảng: B2: Chọn nguồn dữ liệu tại Show Table → Add. - Chọn Close đóng Show Table e. Truy vấn hành động Ths.Nguyễn Văn Trãi Truy vấn nối bảng: B3: Đưa trường cần nối dữ liệu và trường đặt điều kiện (nếu có) vào lưới thiết kế. - Đặt điều kiện cần lọc dữ liệu tại Criteria của trường đặt điều kiện tương ứng (nếu có). e. Truy vấn hành động Ths.Nguyễn Văn Trãi Truy vấn nối bảng: B4: Trên thẻ Design chọn Append - Chọn tên bảng cần nối thêm dữ liệu vào (bảng đích) - Nhấp OK e. Truy vấn hành động Ths.Nguyễn Văn Trãi Truy vấn nối bảng: B5: Lưu, đặt tên truy vấn. - Thẻ Design → Run → chọn Yes để cho kết quả. Lưu ý: Kết quả truy vấn nằm bảng đích. e. Truy vấn hành động Ths.Nguyễn Văn Trãi Truy vấn cập nhật dữ liệu: Là truy vấn bổ sung, chỉnh sửa dữ liệu của một bảng. VD: Dùng truy vấn cập nhật nơi sinh là Đà Nẵng cho sinh viên có mã số sinh viên là TC002. e. Truy vấn hành động Ths.Nguyễn Văn Trãi Truy vấn cập nhật dữ liệu: Thiết kế: B1: Trong thẻ Create → Nhóm Other → Query Design e. Truy vấn hành động Ths.Nguyễn Văn Trãi e. Truy vấn hành động Truy vấn cập nhật dữ liệu: B2: Chọn nguồn dữ liệu tại Show Table → Add. - Chọn Close đóng Show Table Lưu ý: Nguồn dữ liệu là bảng cần bổ sung hoặc sửa đổi dữ liệu. Ths.Nguyễn Văn Trãi Truy vấn cập nhật dữ liệu: B3: Đưa trường cần bổ sung hoặc sửa đổi dữ liệu và trường đặt điều kiện (nếu có) vào lưới thiết kế. - Đặt điều kiện cần lọc dữ liệu tại Criteria của trường đặt điều kiện tương ứng (nếu có). e. Truy vấn hành động Ths.Nguyễn Văn Trãi Truy vấn cập nhật dữ liệu: B4: Trên thẻ Design chọn Update e. Truy vấn hành động - Tại dòng Update to của trường cần bổ sung sửa đổi dữ liệu ở lưới thiết kế ta nhập vào giá trị cần bổ sung hoặc biểu thức cần thay đổi dữ liệu. Ths.Nguyễn Văn Trãi Truy vấn cập nhật dữ liệu: B5: Lưu, đặt tên truy vấn. - Thẻ Design → Run → chọn Yes để cho kết quả. Lưu ý: Kết quả truy vấn nằm bảng làm nguồn dữ liệu của truy vấn. e. Truy vấn hành động Ths.Nguyễn Văn Trãi Truy vấn xóa dữ liệu: Là truy vấn dữ liệu của một bảng. VD: Dùng truy vấn xóa tất cả sinh viên lớp 11ĐCN1 trong bảng SINHVIEN e. Truy vấn hành động Ths.Nguyễn Văn Trãi Truy vấn xóa dữ liệu: Thiết kế: B1: Trong thẻ Create → Nhóm Other → Query Design e. Truy vấn hành động Ths.Nguyễn Văn Trãi e. Truy vấn hành động Truy vấn xóa dữ liệu: B2: Chọn nguồn dữ liệu tại Show Table → Add. - Chọn Close đóng Show Table Lưu ý: Nguồn dữ liệu là bảng cần xóa dữ liệu. Ths.Nguyễn Văn Trãi Truy vấn xóa dữ liệu: B3: Đưa trường cần đặt điều kiện xóa dữ liệu vào lưới thiết kế. - Đặt điều kiện cần lọc dữ liệu để xóa tại Criteria. e. Truy vấn hành động Ths.Nguyễn Văn Trãi Truy vấn xóa dữ liệu: B4: Trên thẻ Design chọn Delete e. Truy vấn hành động Ths.Nguyễn Văn Trãi Truy vấn xóa dữ liệu: B5: Lưu, đặt tên truy vấn. - Thẻ Design → Run → chọn Yes để cho kết quả. Lưu ý: Kết quả truy vấn nằm bảng làm nguồn dữ liệu của truy vấn. e. Truy vấn hành động