Chương III: Query

Chương III: Query BÀI GIẢNG CƠ SỞ DỮ LIỆU CHƯƠNG III: QUERY I. Khái niệm query. Dùng để trả lời các câu hỏi có tính chất tức thời. - Bộ hỏi được dùng để lựa chọn các bản ghi (record), cập nhật các bảng và bổ sung các bản ghi mới vào bảng. - Dùng làm nguồn dữ liệu cho các biểu mẫu, báo cáo. - Tạo lập, cập nhật CSDL. Cách mở cửa sổ query Từ cửa sổ Database chọn Queries Chọn vào dòng: II. Các loại Query. - Truy vấn chọn (Select Query): Thực hiện việc tìm kiếm (kể cả tính toán). - Truy vấn Crosstab: Thực hiện thống kê trên CSDL. - Truy vấn hành động (Action Query): Cho phép cập nhật CSDL gồm bổ sung, loại bỏ hay thay đổi. - Truy vấn định nghĩa dữ liệu, truy vấn hợp. - Truy vấn Pass Through: Gửi lệnh SQL tới CSDL qua ODBC (ví dụ như CSDL SQL server) trên server để thao tác dữ liệu trên đó. III. Các hằng, biến, biểu thức, toán tử và hàm. 3.1. Hằng. - Là đại lượng không đổi trong quá trình tính toán bao gồm: - Hằng số: gồm các số (500, 400). - Hằng ký tự, chuỗi: chuỗi ký tự được đặt trong dấu nháy kép (“0001”, “CT2424”, “Hello”). - Hằng ngày: dữ liệu kiểu ngày đặt trong cặp dấu # (#20/07/2007#). - Hằng đặt biệt: Null, True, False, Yes,No. 3.2. Biến. - Được xác định thông qua tên biến, kiểu dữ liệu và đặt trong dấu [ ] gồm: - Biến trường: biến có tên giống như tên trường, giá trị của biến là giá trị của trường. Hai Table có cùng trường viết theo cú pháp: [Table]![tên biến trường]. - Biến tham số: tên biến không giống bất cứ tên trường nào, giá trị được nhập từ bàn phím. 3.3. Biểu thức. - Là tập hợp các toán tử và toán hạn có hai dạng: - Biểu thức logic: trả về True/False được dùng làm điều kiện trong dòng Criteria. - Biểu thức tính toán được: tính toán giá trị cho một trường mới từ các trường đã có. Toán tử Mô tả Ví dụ + Cộng hai toán hạng [lương]+[phụ cấp] - Trừ hai toán hạng Date-30 -(tt đơn) Thay đổi dấu -12345 * Nhân hai toán hạng [hệ số]*[lương cbản] / Chia 1 t/h với 1 t/h khác 15.2/12.55 \ Chia 1 sn với 1 sn khác. 5\2 Mod Trả về số dư phép chia với một sn. 5 Mod 2 ^ Nâng lũy thừa một toán hạng (số mũ) 4^3 3.4. Toán tử số học. 3.5. Toán tử logic. Toán tử Cú pháp Ví dụ And Bt1 And Bt2 True nếu cả hai là True (27) Or Bt1 Or Bt2 False nếu cả hai False (27) Not Not Bt False nếu Bt True Not(2 7): True Xor Bt1 Xor Bt2 True nếu Bt1, Bt2 có giá trị khác nhau True Xor False=True 3.5. Các hàm thông dụng. - Các hàm xữ lý cơ sở dữ liệu. Cú pháp: (“[biểu thức]”, “phạm vi”, “điều kiện”). + Tên hàm: tên hàm CSDL. + Biểu thức: 1 trường, bảng, điều khiển, hằng chàm… +Phạm vi: tên bảng, truy vấn hoặc câu SQL. + Điều kiện: giới hạn phạm vi dữ liệu. IIF Cấu trúc: iif(đk,bt1,bt2). Nếu đk đúng thực hiện bt1, đk sai thực hiện bt2. Có thể lồng nhiều hàm iif với nhau: Ví dụ: [luong]*(IIf([MA]="A",7/100, (IIf([MA]="C",4/100,0)))) Davg Tính giá trị trung bình của một trường theo điều kiện cho trước. Ví dụ: tính điểm trung bình thi lần 1 của sinh viên SV001. =Davg(“[ketqua1]”, “dangky”, “[Mssv]=‘SV001’”) Tính điểm trung bình lần 1 của tất cả SV =Davg(“[ketqua1]”, “dangky”) Dcount Đếm số mẫu tin trong 1 bảng theo ĐK cho trước và không đếm các mẫu tin có giá trị Null. Ví dụ: đếm số mẫu tin trong bảng đăng ký. =Dcount(“*”, “dangky”). Đếm số môn học mà SV sv001 đăng ký. =Dcount(“*”, “dangky”, “[mssv]=‘sv001’”). Dlookup Tìm giá trị của một trường theo điều kiện cho trước. Ví dụ: tìm kiếm kết quả lần 1 của môn học MH001. =Dlookup(“[ketqua1]”, “dangky”, “[msmh]=‘MH001’”). Dmin, Dmax, Dsum Tìm giá trị nhỏ nhất, lớn nhất, tính tổng của một trường theo điều kiện cho trước. Chú ý:trong trường hợp điều kiện cho trước là một giá trị được lấy từ các điều khiển ta làm như sau: =DLookUp("[ketqua1]",“dangky","[msm h]= ‘ " & [Combo0].[Value] & “ ‘ ") Một số hàm khác Mỗi người tìm 50 hàm. IV. Select Queries. 4.1. Tạo query. Bước 1: Từ cửa sổ DataBase của Access chọn Query/New/Design View/Ok. - Hoặc từ cửa sổ Database, trong mục Object chọn Query -> Create Query in Design View. - Nếu Query mới tạo thì cửa sổ ShowTable tự động mở, nếu không ta chọn vào nút để hiển thị cửa sổ ShowTable. Chọn các Table cần thiết Thêm vào cửa sổ  Chú ý: mỗi Query có: – Tối đa 32 bảng tham gia. – Tối đa 255 trường. – Kích thước tối đa của bảng dữ liệu try vấn tạo ra là 1 Gigabyte. – Số trường dùng làm khóa sắp xếp tối đa là 10. – Số truy vấn lồng nhau tối đa là 50. – Số ký tự tối đa trong ô của vùng lưới là 1024. – Số ký tự tối đa trong dòng lệnh SQL là 64000. – Số ký tự tối đa trong tham số là 255. 4.1. Tạo Query (tt). Bước2: Từ cửa sổ Show Table chọn các bảng dữ liệu hoặc các truy vấn liên quan đến câu hỏi, bằng cách Chọn tên bảng (hoặc tên truy vấn) -> Add. - Ví dụ câu hỏi liên quan đến sinh viên và khoa ta có cửa sổ. Field: Các trường liên quan đến truy vấn. Có thể xuất hiện ở kết quả hoặc điều kiện tìm kiếm. Trường kết xuất có dạng <Tên trường kết xuất>: Table: Bảng chứa trường được lựa chọn. Sort: Sắp thứ tự kết quả theo thứ tự tăng hoặc giảm. Show: Trường xuất hiện ở kết quả hiện hoặc ẩn. Criteria: Điều kiện chọn có dạng: <Phép toán quan hệ> Hoặc <Biểu thức> Hai biểu thức trên có thể viết trên một dòng sẽ tổ hợp thành điều kiện and. Or: Tìm kiếm theo điều kiện Or. Có thể thêm dòng. Ví dụ Bước 3: Lựa chọn các trường cần hiển thị trong kết quả của truy vấn hoặc liên quan đến các điều kiện tìm kiếm. Sau đó, soạn thảo các điều kiện tìm kiếm trong phần Criteria. Bước 4: Thực hiện truy vấn bằng một trong hai cách sau: Cách 1: Chọn biểu tượng ! trên thanh công cụ. Cách 2: Trên thanh menu chọn Query\Run. Bước 5: Ghi lại kết quả truy vấn (nếu muốn) bằng cách: Cách 1: Chọn biểu tượng đĩa mềm trên thanh công cụ. Cách 2: Trên thanh menu chọn File\Save. •Ví dụ tìm những sinh viên học trong khoa có tên “ANH_VAN” hoặc “TU_NHIEN” Kết quả 4.2. một số thao tác cơ bản với các trường trong Query. - Thay đổi thứ tự, thêm xóa các trường. - Đổi tên tiêu đề cột, tạo trường mới. 4.3. định thứ tự sắp xếp. - Sắp xếp nhanh dùng các nút lệnh trên thanh công cụ. - Tạo sắp xếp sẳn: click vào ô sort và chọn hướng sứp xếp. 4.4. Cách lập biểu thức. 4.4.1. biểu thức dạng hằng. - Hằng chuỗi nhập bình thường không phân biệt hoa thường. - Dạng số, tiền tệ, tự động không được đưa ký hiệu đại diện vào. - Dạng ngày có hai dấu # bao quanh. 4.4.2. các phép toán được dùng trong biểu thức. - Toán tử toán học. - Toán tử logic. - Toán tử Like: muốn sử dụng các ký tự đại diện (Text/ Date/Time) Ký hiệu Ý nghĩa * Thay thế một số ký tự bất kỳ ? Hoặc# Thay thế một ký tự tại vị trí xuất hiện [ ] Thay thế các ký tự nằm trong ngoặc ! Thay thế các ký tự khác ký tự sau ! - Từ ký tự đến ký tự. Ví dụ Tìm những nhân viên có mã ký tự đầu tiên bất kỳ, ký tự thứ 2 khác A,B,C,D. Kết quả Tìm những nhân viên sinh vào quý 1 Tìm những nhân viên sinh vào tháng 12… Tìm những nhân viên có họ nguyễn. Liệt kê các nhân viên có trong phòng tài vụ… 4.5. Total Queries. - Tạo một truy vấn mới - Chuyển từ truy vấn chọn thông thường sang truy vấn tính toán bằng cách chọn biểu tượng totals trên thanh công cụ hoặc trên thanh menu chọn View -> Totals. Trong mục Total của mỗi cột có những lựa chọn sau: - Group by: Dùng để chỉ ra trường nào là trường phân nhóm. - Sum: Tính tổng. - Avg: Tính trung bình. - Min: Tìm giá trị nhỏ nhất. - Max: Tìm giá trị lớn nhất. - Count: Đếm. - StDev: Tìm độ lệch chuẩn. - Var: Tìm phương sai. - First: Tìm bản ghi đầu tiên. - Last: Tìm bản ghi cuối cùng. - Expression: Một biểu thức trong tính toán, đi kèm với một tính toán bằng hàm thư viện khác. - Where: Điều kiện lọc các bản ghi tham gia vào tính toán. Ví dụ Tính tổng số nhân viên của từng đơn vị. Tính tổng số nam và tổng số nữ của từng đơn vị Tìm hệ số lớn nhất và hs nhỏ nhất của từng đơn vị. Tìm hs trung bình của từng đv. Tính tổng số môn học và tổng số tính chỉ của môn học đó. Filed Tongsomon: msmh Tongsotc: sotc Table Monhoc Monhoc Total Count sum 4.5.2. Tính tổng trên từng nhóm mẫu tin. - Tạo truy vấn mới. - Đưa các bảng cần thiết vào truy vấn. - Đưa các trường cần thiết vào vùng lưới. - Chọn Total. - Trong ô Total: - Chọn groupBy cho trường làm khóa chính để nhóm. - Chọn phép tính tổng cho các trường còn lại. - Chuyển ang Datasheet View để xem kết quả. Ví dụ Tính tổng số giáo viên của từng khoa. Filed Khoa: tenkhoa Sogiaovien:msgv Table Khoa Giaovien Total Group by count 4.5.3. Tính tổng trên nhiều nhóm mẫu tin. - Chọn Group By trên nhiều trường. - Ưu tiên từ trái qua phải. - Ví dụ: tính tổng số sv của từng lớp theo từng khoa. Filed Tenkhoa tenlop Sosinhvien:mssv Table Khoa lop sinhvien Total Group by Group by count Đặt điều kiện cho các trường sau khi tính tổng  Tìm những người có đăng ký 3 môn học trở lên. Filed Mssv Hotensv Tongsomon:msmh Table Sinhvien Sinhvien Dangky Total Group by Group by count Criterial >=3 4.5.4. Lập biểu thức chọn giới hạn những mẫu tin trước khi tính tổng. - Tạo truy vấn. - Đưa các bảng vào truy vấn. - Đưa các trường vào vùng lưới. - Chọn Total - Thiết lập ô Total thành Where đối với trường muốn đặt biểu thức giới hạn. - Gõ biểu thức tại ô Criteria Ví dụ: Liệt kê sv đăng ký 3 môn trở lên trong niên khóa 2003-2004. Filed Mssv Hotensv Tong:msmh nienkhoa Table Sinhvien Sinhvien Dangky dangky Total Group by Group by count Where Criterial >=3 “2003-2004” Chú ý: – Trường có chọn Where thì không thể hiển thị kết quả được. – Trong đa số trường hợp đặt điều kiện lọc trước và sau khi tính tổng sẽ cho kết quả khác nhau. Ví dụ: tìm điểm trung bình của SV có mã số “001” Điếm số sinh viên nữ trong lớp. 4.6. Truy vấn tham số(Parameter Queries) Cho phép thực hiện một truy vấn nhiều lần nhưng mỗi lần thực hiện với một tiêu chuẩn lựa chọn khác nhau. - Tạo truy vấn mới. - Đưa các bảng cần thiết vào truy vấn. - Đưa các trường cần thiết vào vùng lưới. - Tại ô Field – Criteria gõ vào biểu thức có chứa tham số. Lưu ý: - Tên tham số nằm giữa hai dấu ngoặc vuông. - Tên tham số cũng chính là chuỗi nhắc nhở: tên tham số có khoảng trắng chiều dài không quá 255 ký tự. Tìm những sinh viên thuộc khoa “AV” Khi Run ta thấy xuất hiện bảng: Những SV có điểm >7 được 10 điểm Khi Run Kết quả Liệt kê thông tin về năm sinh của sv với năm sinh nhập từ bàn phím. Filed Mssv Hotensv phai ngaysinh Table Sinhvien Sinhvien Sinhvien Sinhvien Criterial Namsinh=year([n gaysinh]) Tìm những sinh viên sinh nam n1 có đăng ký m2 môn học trở lên. Filed Mssv Hotensv phai msmh ngaysinh Table Sinhvien Sinhvien Sinhvien Dangky Sinhvien Total Group by Group by Group by Count Group by Criterial Namsinh= year([ngay sinh]) >=[somon] Liệt kê số nhân viên trong từng đơn vị, mã đv nhập từ bàn phím Tìm những người có số năm công tác lớn hơn một số nhập từ bàn phím Tìm các nhân viên có mã nv bắt đầu bằng ký tự nhập vào từ bàn phím Sinh viên thi môn m1 có số điểm từ d1 đến d2 điểm. Thông tin về môn m1 do giáo viên thuộc khoa KTCN dạy. Liệt kê thông tin về các sinh viên thuộc khoa K1, học môn học M2, với số điểm lớn nhất của hai lần thi từ D3 trở lên. Chú ý: ta có thể tạo truy vấn tham số với nhiều điều kiện chọn. Nội dung các tham số nhập vào chỉ có thể là hằng, không thể là biểu thức. Bạn có thể quy định kiểu dữ liệu cho từng tham số: trong Design View chọn Query- parameter Ví dụ: tìm nhân viên có năm sinh và ký tự đầu của Manv nhập tự bàn phím 4.7. CrossTab Queries. Dùng để tổng hợp dữ liệu và trình bàytheo dạng cô động. Cách tạo truy vấn CrossTab – Tạo truy vấn mới. – Đưa các bảng tham gia truy vấn vào. – Đưa các trường cần thiết vào vùng lưới. – Chọn Query/ CrossTab trên vùng lưới xuất hện dòng Total và Crosstab Tại trường dùng làm tiêu đề cột chọn Group By cho ô Total, Column heading cho ô Crosstab. Tại trường dùng làm tiêu đề dòng chọn Group By cho ô Total, Row Heading cho ô Crosstab. Tại trường dùng tính giá trị chọn phép toán tương ứng cho ô Total, Value cho ô Crosstab. Ví dụ: tính tổng số nam và nữ trong từng khoa. Kết quả Chú ý: – Trong truy vấn Crosstab chỉ có một trường dùng làm tiêu đề cột. – Có thể có nhiều tiêu đề dòng. – Chỉ có một trường giá trị. Row Heading Coulumn Heading Value Vi du: Tạo query để tính kết quả trung bình điểm thi lần 1 của môn tin học theo sinh viên từng khoa. Mskhoa Tenkhoa MSMH Ketqua1 tenMH Khoa Khoa monhoc Dangky Monhoc Group By Group By Group By Avg Where Row Heading Row Heading Column Heading Value Like “Tin hoc” Tạo Quyery để thống kê số lượng sinh viên Nam và Nữ theo từng lớp Query1. Mslop Phai phai lop Sinhvien Sinhvien Group By Group By Count Row Heading Column Heading Value  Tuy nhiên nếu ta dùng Query trên kq là:  Để hiển thị được Nam và Nữ ta phải dùng thêm một query thứ hai với nguồn là query vừa tạo đặt (Nam: -1, Nu: 0) cho tiêu đề cột.  Ví dụ Query trên ta lưu là Query1 ta có: Mslop Nam: -1 Nữ: 0 Query1 Query1 Query1 Một số ví dụ Thống kê kết quả học tập lần 1 của các sinh viên theo từng niên khóa. Thống kê số lượng sinh viên trong các lớp thuộc khoa “kinh tế và quản trị kinh doanh” Thống kê số lượng sinh viên học các môn tin học theo từng khoa 4.7. Action Queries.  Khi thực hiện truy vấn chọn, muốn thay đổi giá trị của mẫu tin nào đó, chuyển sang Datashet View sửa đổi trên kết quả.  Trong trường hợp muốn thay đổi hàng loạt mẫu tin ta dùng truy vấn hành động.  Do tính chất nguy hiểm của truy vấn này nên Access thường đưa ra thông báo. Để tắt chứ năng này: tool/Option/Tab Edit/Find tắt Action Query 4.7.1. Make Table Queries. Select và total Queries cho ta một bảng giá trị tức thời. Mà tại thời điểm khác có thể không thu được kết quả như trước. Muốn lưu lại bảng kết quả của một Query tại một thời điểm ta dùng Make Table Query. Các bước tạo: Tạo truy vấn mới. Đưa các bảng cần thiết vào truy vấn. Chọn Query/ Make Table. Đặt tên bảng mới. Nếu muốn tạo bảng trong một CSDL mới chọn Another Database. Thực hiện các lựa chọn cần thiết để tạo bảng mới. Chọn lệnh Run hoặc chọn biểu tượng Run trên thanh công cụ. Đặt tên cho Table mới Table mới đặt ở CSDL hiện hành Table mới đặt ở CSDL khác Chú ý: – Khi chọn lệnh Run một hộp thoại cảnh báo xuất hiện. – Nhấn Yes để thực hiện, NO để hủy bỏ. Nếu đã tồn tại bảng cùng tên Access sẽ thông báo. Nếu chọn Yes Access sẽ lưu nội dung mới vào bảng cũ bị xóa . Một hộp thoại sẽ xuất hiện một lần nửa xem bạn có đồng ý hay không. Nếu chọn Yes bảng sẽ được tạo thật sự. Ví dụ: tạo bảng mới gồm những SV thuộc khoa “av” 4.7.2. Delete Queries Là một Action Query. Xóa một loạt các mẫu tin trong bảng thỏa điều kiện nào đó. Tránh được các sai xót khi đi tìm xóa các mẫu tin. Hầu hết các truy vấn xóa chỉ xóa trên một bảng dù các nhiều bảng tham gia truy vấn. (có hai bảng có quan hệ “một – nhiều” chỉ có các mẫu tin bên nhiều bị xóa). Lưu ý:Trong một số trường hợp truy vấn xóa có thể xóa các mẫu tin trong bảng không tham gia vào truy vấn. Cách tạo: – Tạo truy vấn mới. – Đưa các bảng vào. – Chọn Query/ Delete Query. – Đưa các trường vào vùng lưới Trường dấu * xuất hiện từ From tại ô Delete Trường khác xuất hiện từ Where tại ô Delete. Định các điều kiện lọc Chuyển sang Datasheet View để xem trước các mẫu tin sẽ bị xóa. Chọn lện Run để thực hiện truy vấn. Xuất hiện hộp thoại cảnh báo: Chọn Yes để chấp nhận, No hủy. Ví dụ: xóa khoa tự nhiên. 4.7.3. Append Queries.  Thêm các mẫu tin thỏa điều kiện nào đó từ bảng này sang bảng khác.  Tạo truy vấn: – Tạo truy vấn mới . – Đưa các bảng cần thiết vào truy vấn. – Chọn Query/ Append. – Drag các trường muốn nối vào vùng lưới. – Chọn các trường dùng để làm điều kiện lọc. – Định các điều kiện lọc. 4.7.4. Update Queries. Là một Action Query. Cập nhật dữ liệu cho một loạt các mẫu tin một cách nhanh chóng. Cách tạo: – Tạo truy vấn mới. – Đưa các bảng vào truy vấn. – Chọn Query/ Update Query. – Đưa các trường muốn cập nhật vào vùng lưới. Đưa các trường dùng để làm điều kiện lọc. Trong ô Update To của trường muốn cập nhật nhập vào giá trị mới hay biểu thức tính toán cho trường đó. Thực hiẹn truy vấn. Ví dụ: những sinh viên thi lại lần hai có số điểm từ 4 đến 4.5 sẽ được cho đậu. V. Truy vấn SQL. 5.1. Cách mở cửa sổ thiết kế truy vấn SQL . Bước 1: Từ cửa sổ Database, trong mục Objects chọn đối tượng Querys -> Create query in Design view. Sau bước này cửa sổ Show tables xuất hiện (như trong phần thiết kế truy vấn QBE). Bước 2: Đóng cửa sổ Show table này lại, trên menu chọn View -> SQL View. Cửa sổ thiết kế SQL xuất hiện, và chúng ta soạn câu lệnh SQL trong cửa sổ này. Bước 3: Để thực hiện câu lệnh SQL chọn biểu tượng ! trên thanh công cụ. 4.2 Cú pháp của câu lệnh SQL. SELECT [DISTINCT|ALL]{*| <danh sách các thuộc tính>| [AS ]} FROM [] [, …] [WHERE ] [GROUP BY <danh sách tên cột dùng để gộp nhóm>] [HAVING ] [ORDER BY ] Trong đó: -Biểu thức cột: là tên của cột hoặc biểu thức. -Tên bảng:là tên của bảng hoặc khung nhìn. - Bí danh: là tên viết tắt của tên bảng. -GROUP BY dùng để nhóm các bản ghi có cùng giá trị trong danh sách tên cột thành một nhóm. - HAVING dùng để lọc các nhóm thỏa mãn điều kiện lựa chọn. -ORDER BY: qui định thứ tự các bản ghi trong kết quả trả ra. Câu hỏi trắc nghiệm 1.Hệ quản trị CSDL Access cho phép bạn xây dựng CSDL dựa trên mô hình dữ liệu nào? a. AMô hình dữ liệu mạng b. Mô hình dữ liệu phân cấp c. Mô hình dữ liệu quan hệ 2.Một CSDL trong hệ quản trị Access bao gồm các đối tượng nào? a. Bảng, Báo cáo, Truy vấn b. Macro,Trang truy cập dữ liệu, Module, Bảng, Truy vấn, Báo cáo, Biểu mẫu. c. Mẫu biểu, Bảng, Macro, Truy vấn d. Module, Truy vấn, báo cáo, CSDL, Macro, Trang truy cập dữ liệu, Mẫu biểu 3.Để có kết quả tổng hợp như hình dưới đây khi thực hiện Query, ta phải thiết kế loại Query nào? – Delete Query – Appen Query – Parameter Query – Crostab Query Hoten Bia Nuoc ngot ruou Hoang thi Lan 500000 600000 400000 Le huynh hoa 750000 250000 300000 Khi kích vào biểu tượng trên thanh công cụ, trên khung lưới của Query xuất hiện dòng nào? – Delete – Append To – Total – Update To Khi thực hiện loại Query nào thì xuất hiện hộp thoại yêu cầu nhập liệu: – Select Query – Append Query – Parameter Query – Delete Query