Tin học văn phòng 1 - Bài 2: Truy vấn dữ liệu (Query)

BÀI 2: TRUY VẤN DỮ LIỆU (QUERY)Nội dung trình bàyCác loại truy vấnSelect QueryCrosstab QueryMake table QueryAppend QueryDelete QueryUpdate QueryGiới thiệu về QueryCó rất nhiều dạng yêu cầu xử lý dữ liệu như: 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. Query sẽ giải quyết những yêu cầu đó của người sử dụngcó 6 loại Query:SELECT QuerySelect 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 đó;SELECT QueryCSDL quản lý sinh viênSELECT QueryĐưa ra những thông tin sau: MaSV, hoten, ngaysinh, Monhoc, diemTB, xeploaiTrong đó: diemTB = avg([diem]) Xếp loại: nếu diemTB>=8 xếp loại giỏi nếu 8>diemTB và diemTB>=6.5: khá nếu 6.5>diemTB và diemTB>=5: khá Còn lại yếu Phân tích yêu cầu:Thông tin yêu cầu đòi hòi từ nhiều bảng khác nhau (bảng SINHVIEN với các cột: MASV, hoten, ngaysinh; bảng MONHOC với TENMH); bảng KETQUA với cột DIEM.Cột mới cần tạo: diemTB và xeploaiSELECT QueryBước 1: Tạo một Select query sử dụng Design View, SELECT QueryHoặc nhấn đúp chuột lên biểu tượng Create query in Design view trên màn hình:SELECT QueryBước 2: Chọn những bảng có chứa dữ liệu liên quan lên màn hình thiết kế query từ cửa sổ Show TableSELECT QueryBước 3: Khai báo những thông tin cần thiết cho query:Dòng Field: là nơi khai báo danh sách các thông tin (cột dữ liệu) của bảng kết quả.SELECT QueryCó 2 loại thông tin bài toán yêu cầu:thông tin có sẵn từ các trường trên CSDL như MASV, hoten, ngaysinh, monhoc thông tin phải được tính theo một biểu thức nào đó như: diemTB và xeploai (thông tin suy dẫn).Muốn hiển thị trường nào lên query, nhấn đúp chuột lên tên trường hoặc dùng chuột kéo tên chúng từ các bảng lên dòng Field.Với 2 cột mới: tạo như sau được tính bằng một trên dòng Field theo cú pháp như sau:SELECT QueryVí dụ:DiemTB: avg([diem])xeploai: if()Cách đưa vào tên mới: Gõ tên mới ngay trước tên trường. Đặt  dấu : giữa tên mới và tên trường (:)Các trường tham gia trong biểu thức phải đặt trong dấu [ ] : Dấu ngăn cáchSELECT QueryMột số thiết lập khác cho QueryDòng Sort: để thiết lập thứ tự sắp xếp dữ liệu trên Query.Muốn sắp xếp dữ liệu cho trường nào, thiết lập thuộc tính Sort cho trường ấy. Có 2 giá trị cho thuộc tính Sort: Ascending - sắp xếp tăng dần và Descending - sắp xếp giảm dần. Trường nào đứng trước sẽ được thứ tự sắp xếp trước.SELECT QueryDòng Show: để chỉ định hiển thị hay không hiển thị dữ liệu trường đó ra bảng kết quả. Nếu chọn (checked)- dữ liệu sẽ được hiển thị ra bảng kết quả.Lọc dữ liệu trên QueryQuery cung cấp một khả năng lọc dữ liệu khá hoàn chỉnh; Có thể lọc ra những dữ liệu theo những điều kiện phức tạp.Đặc biệt có thể chấp nhận những giá trị lọc là các tham biếnĐể lọc dữ liệu, bạn phải thiết lập điều kiện đặt lọc lên vùng Criteria của queries (trong chế độ đang thiết kế).Các điều kiện nằm trên cùng một dòng Criteria sẽ được nối với nhau bởi toán tử AND (và); mỗi dòng Criteria sẽ được nối với nhau bởi toán tử OR (hoặc)Lọc dữ liệu trên QueryVí dụ 1: Lọc ra những cán bộ là trưởng phòng có thực lĩnh ’Trong có thể chứa hằng (Nam) và các ký tự đại diện.Lọc dữ liệu trên QueryCó 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-HLọc dữ liệu trên QueryVí dụ 4: Lọc ra những cán bộ có 2,000,000>= Thuclinh >=1,000,000Cú pháp toán tử Between như sau: Between And Lọc dữ liệu trên QueryCác phép toán và biểu thức dùng để lập tiêu chuẩn chọn các bản ghiToán tử so sánh: =; >; =; Toán tử: Between And Phép toán Logic AND : hội các ĐK: >=[Từ điểm] And OR Biểu thức kết hợp các chuỗi ký tự: &Lọc dữ liệu trên QueryCảc ví dụ trên đều đặt lọc theo các tham trị (lọc cứng). Trong trường hợp sử dụng các tham biến vào điều kiện lọc, thực hiện như sau:[Gõ một lời nhắc trong ngoặc vuông] tại đúng vị trí tham số cần thiết lập.Ví dụ: Lọc ra danh sách các cán bộ phòng ban nào đó, làm như sau:Lọc dữ liệu trên QueryKhi thi hành query, một hộp thoại yêu cầu gõ vào tham số cần lọcTạo Select Query dùng hỏi -đáp dữ liệu (Query tham số)Tạo truy vấn mớiĐưa các bảng cần thiết vào truy vấnChọn các trường cần thiết trên dòng FieldNhập các câu hỏi với dữ liệu ứng với trường cần hỏi trên dòng CriteriaTạo Select Query dùng hỏi -đáp dữ liệuChú ý : Câu hỏi tuỳ đặt phải đặt trong dấu [ ] tối đa 255 kí tựCó thể đưa biểu thức hỏi kèm cùng các phép toán, biểu thức đã nói ở trên để tạo ra một điều kiện cụ thể:Tạo Select Query dùng hỏi -đáp dữ liệuCác dạng hiển thị truy vấnTruy vấn 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) Khi đang thiết kế truy vấn (truy vấn đang ở dạng Query Design) ta có thể chuyển sang các dạng khác bằng cách: Chọn menu View. Kết quả nhận được các mục của menu View mà 3 mục đầu là tiêu đề 3 dạng nói trên.  Muốn xem truy vấn ở dạng nào ta chọn mục tương ứng với dạng đó Sử dụng các hàm cho cột tính toán Hàm trên chuỗi ký tự Left: Trích một số ký tự bên trái của chuỗiLeft(, )Right: Trích một số ký tự bên phải của chuỗiRight(, )Mid: Trích một số ký tự tại vị trí bất kỳ trong chuỗiMid(, , )Trim: Cắt bỏ khoảng trắng dư thừa hai đầu chuỗiTrim()Month: Trả về giá trị tháng của biểu thức ngàyMonth()Day: Trả về giá trị ngày của biểu thức ngàyDay(<Tên field hoặc biểu thức ngày)Sử dụng các hàm cho cột tính toán Ví dụ:Liệt kê danh sách các sinh viên có ngày sinh trong tháng 4, thông tin gồm: Mã sinh viên, Họ tên sinh viên, Tên khoaTOTAL QueryTổng hợp – là phép xử lý dữ liệu khá phổ biến. Trong Access, query là một trong những công cụ xử lý khá tốt việc này. Total query là một điển hình. Hãy xét một số yêu cầu tổng hợp dữ liệu như sau:TOTAL QueryBướ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:TOTAL QueryBước 2: Tuỳ chọn Total query bằng cách mở thực đơn View | Total hoặc nhấn nút Total trên thanh công cụ;Bước 3: Thiết lập các tuỳ chọn Total cho các trường một cách phù hợp như sau: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.TOTAL QueryTOTAL QueryTotal query cung cấp một số phép tổng hợp khác như:Sum: Tính tổng cộngAvg: Tính giá trị trung bìnhMin: Tìm giá trị nhỏ nhấtMax: Tìm giá trị lớn nhấtCount: Đếm tổng số mẩu tinGroup By: Gom nhóm dữ liệuChọ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 Chú ý: Luôn luôn sắp xếp theo chiều tăng, nếu thay đổi thì chọn phần Sort và sắp xếp lạiTOTAL QueryVí 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ấtTOTAL QueryVí 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ênTOTAL QueryVí dụ 3: Kết hợp Sum và IIFThống kê tổng số sinh viên nam và tổng số sinh viên nữ theo từng khoaTOTAL QueryChọn hàm cần tính tương ứng với mỗi trường cần tính. Thực hiện phép tính theo từng nhóm trên các trường tính toán (có hàm Sum, Avg, ...) trên ô Total.Bài tập:CROSSTAB QueryTrên thực tế còn rất nhiều các yêu cầu tổng hợp khác mà Total query không thể đáp ứng được. Nhiều trong số đó như là:CROSSTAB QueryCấu trúc một Crosstab, được minh hoạ bởi hình sau:CROSSTAB QueryRow heading là 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 là 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 01 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,..CROSSTAB QueryCác bước để tạo một Crosstab query.Ví dụ tạo query đưa ra được bảng tổng hợp sau: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:CROSSTAB QueryCROSSTAB QueryBước 2: Ra lệnh chọn kiểu Crostab query bằng cách mở thực đơn 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:CROSSTAB QueryCụ thể:MAKE TABLE QueryDữ 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.MAKE TABLE QueryGiả sử đã tạo được một Select query đưa ra được bảng kết quả như sau:MAKE TABLE QueryCách làm như sau:Bước 1: mở select query đã tạo được ở chế độ Design View;Bước 2: ra lệnh tạo Make table query bằng cách mở thực đơn Queries | Make Table query, hộp thoại Make table xuất hiện:Hãy nhập vào tên bảng dữ liệu cần lưu vào ô Table Name: Chú ý:Nếu tên bảng nhập vào là mới, Access sẽ tự động tạo một bảng mới và sao chép toàn bộ dữ liệu mà query kết xuất được ra bảng này; Nếu tên nhập vào trùng một tên bảng đã có sẵn, khi thi hành Access sẽ xoá bảng cũ và điền vào dữ liệu mới (cần cân nhắc khi đặt tên bảng trùng tên bảng đã tồn tại).MAKE TABLE QueryBước 3: thi hành query để nhận kết quả bằng cách: Nếu query đang ở chế độ thiết kế, nhấn nút Run trên thanh công cụ;Hoặc nháy đúp chuột lên query cần thực hiệnKhi đó một hộp thoại cảnh báo việc bạn đang ra lệnh thi hành một query có thể làm thay đổi đến dữ liệu trên CSDL: Nhấn Yes để tiếp tục hoặc nhấn No để huỷ bỏ lệnh.Nếu trên CSDL đã tồn tại một bảng có cùng tên bảng bạn đã chỉ định cho query này lưu dữ liệu, Access sẽ hỏi bạn: Đã tồn tại bảng xxx trên CSDL rồi, nó sẽ bị xoá sạch khi query này thi hành, bạn có muốn tiếp tục không?MAKE TABLE QueryNhấn Yes để tiếp tục (tất nhiên bảng dữ liệu đó sẽ bị xoá và thay vào nội dung mới); nhấn No để huỷ bỏ.Cuối cùng một hộp thoại hỏi một lần cuối xem bạn có đồng ý dán xxx bản ghi vào bảng đã chỉ định hay không?Nhấn Yes để đồng ý, No để huỷ bỏ.Thi hành xong hãy mở bảng vừa tạo được để kiểm tra kết quả.Append QueryThêm một hay nhiều mẫu tin mới vào bảng dữ liệu từ một bảng khácMẫu tin mới thêm vào phải thỏa các yêu cầu kiểm tra nhập liệuAppend QueryBước 1: Trong cửa sổ Database, chọn thẻ Queries và nhấn chọn NewBướ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ệuAppend QueryBướ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 CriteriaAppend QueryDELETE QueryDelete 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 đó.Dưới đây là hướng dẫn tạo một Detele query xoá đi những cán bộ đến tuổi nghỉ hưu ra khỏi CSDL Quản lý lương cán bộ. Khi query này thi hành, danh sách các cán bộ đến tuổi hưu sẽ bị xoá khỏi bảng canboCách tạo query này như sau:DELETE QueryBước 1: Tạo một Select query như sau:DELETE QueryBước 2: đổi query hiện hành thành Delete query bằng cách mở thực đơn Queries | Delete QueryDELETE 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.UPDATE QueryUpdate 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:UPDATE QueryUPDATE QueryBướ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:UPDATE QueryQ&A