Chuong III
TRUY VẤN (QUERY)
I. Khái niệm về truy vấn:
Là ngôn ngữ thao tác trên CSDL quan hệ
Truy vấn dùng để sắp xếp, tìm kiếm và kết xuất dữ liệu từ một hoặc nhiều bảng dựa vào liên kết giữa các bảng.
Microsoft Access cung cấp cho chúng ta 2 loại phương tiện để tạo Query:
Tạo bàng lệnh của ngôn ngữ SQL
Tạo Query bằng ví dụ QBE
1. Khái niệm:
I. Khái niệm về truy vấn:
Là ngôn ngữ thao tác trên CSDL quan hệ
Truy vấn dùng để sắp xếp, tìm kiếm và kết xuất dữ liệu từ một hoặc nhiều bảng dựa vào liên kết giữa các bảng.
Microsoft Access cung cấp cho chúng ta 2 loại phương tiện để tạo Query:
Tạo bàng lệnh của ngôn ngữ SQL
Tạo Query bằng ví dụ QBE
2. Các thành phần cơ bản của cửa sổ QBE
I. Khái niệm về truy vấn:
3.1 Select Query
3. Một số Query thông dụng:
3.2 Update Query
3.3 Append Query
3.4 Delete Query
3.5 Make - Table Query
3.6 Cross Tab Query
3.7 Find Duplicate Query
3.8 Find Unmatched Query
3.9 Union Query
3.10 Pass Through Query
3.11 Data Definition Query
I. Khái niệm về truy vấn:
Tối đa 32 bảng tham gia
Chú ý: Mỗi Query có:
Tối đa 255 trường
Kích thước tối đa của một bảng dữ liệu do truy vấn tạo ra là 1Gigabyte
Số trường dùng làm khoá sắp xếp tối đa là 10
Số truy vấn lồng nhau tối đa 50 cấp
Số ký tự tối đa trong ô của vùng lưới là 1024
Số ký tự tối đa trong tham số là 255
Số ký tự tối đa trong dòng lệnh SQL là 64000
II. Các thành phần cơ bản trong truy vấn:
1. Hằng:
Hằng là một đại lượng không đổi trong quá trình tính toán
Trong truy vấn có 3 loại hằng:
Hằng số: gồm các số. Ví dụ: 30 25.67 1000
Hằng chuỗi ký tự: gồm các chuỗi ký tự để trong cặp dấu nháy đôi " " Ví dụ: "tin học" ; "100"
Hằng ngày: là dữ liệu kiểu ngày được đặt trong cặp dấu nháy # # Ví dụ: #03/08/2009#
2. Kiểu:
Giống các kiểu dữ liệu của các trường đã được trình bày
trong chương II
II. Các thành phần cơ bản trong truy vấn:
3. Biến:
Access quản lí biến thông qua tên biến
Tên biến phải đặt trong cặp dấu ngoặc vuông: [Tên biến]
Có 2 loại biến:
Biến trường: khi tên biến giống như tên trường của bảng tham gia truy vấn
Biến tham số: khi tên biến không giống với tên trường của bảng tham gia truy vấn. Giá trị của biến sẽ được nhập vào khi thực hiện Query
Chú ý: Nếu có nhiều tên trường giống nhau trong các bảng bảng thì phảI chỉ rõ biến trường đó của Table nào theo cú pháp: [Tên Table]![Tên biến trường]
II. Các thành phần cơ bản trong truy vấn:
4. Biểu thức:
Có hai loại biểu thức:
Biểu thức logic: là biểu thức mà kiểu kết quả là Yes/No (True/False). Thường được sử dụng trong các trường hợp sau:
Thiết lập bộ lọc cho bảng
Thiết lâp điều kiện lọc để tạo mẫu hỏi
Biểu thưc tính toán được: được viết theo cú pháp
Nhãn:Biểu thức/trường
"Nhãn" : được sử dụng khi muốn đổi tên Biểu thức/trường
Ví dụ: (Q11)
Ví dụ: (Q04)
II. Các thành phần cơ bản trong truy vấn:
5. Ký tự đại diện:
II. Các thành phần cơ bản trong truy vấn:
6. Toán tử:
Các phép toán số học:
+ , - , * , / : Các phép toán cộng trừ nhân chia
^ : Phép tính luỹ thừa Ví dụ: 3^2=9
: Phép chia lấy phần nguyên Ví dụ: 72=3
Mod : Phép chia lấy phần dư Ví dụ: 7 mod 2=1
Các phép toán Logic: > >= < <= = <>
And Or Not
II. Các thành phần cơ bản trong truy vấn:
6. Toán tử:
Các hàm xử lý cơ sở dữ liệu:
Cú pháp chung:
(, [Điều kiện] )
Trong đó:
Tên hàm: là tên của hàm
Biểu thức: Là một biểu thức chuổi xác định tên trường dùng để tính toán và trả về kết quả.
Phạm vi: Là một biểu thức chuỗi xác định miền là việc
Điều kiện: Là một biểu thức chuỗi (có thể co hoặc không có) dùng để giới hạn phạm vi dữ liệu mà hàm phảI thực hiện
Lưu ý:
Các hàm cơ sở dữ liệu không tính toán trên giá trị null
Các đối số phải viết trong dấu nháy kép " "
Tên trường phải viết trong dấu ngoặc vuông [ ]
Hằng chuổi phải viết trong dấu nháy đơn ` `
III. Truy vấn chọn (Select Query)
Chọn ra các field nào đó để hiển thị từ một hay nhiều Table theo một điều kiện nào đó
Tạo thêm mới các field tính toán theo một biểu thức tính toán nào đó
Tổng hợp số liệu theo nhóm
1. Tạo truy vấn chọn bằng Wizard:
Anh, chi có thể theo dõi thông qua ví dụ
Ví dụ: Tạo truy vấn để hiển thị thông tin NGAY, LOAI, FIEU, MAHANG, SOLG, DGIA
Cách lập điều kiền trên dòng Criteria:
Điều kiện chọn lựa có thể là một biểu thức
Nhấn tổ hợp phím Shift + F2 để phóng to ô điều kiện
Điều kiện cùng dòng là phép và (and), điều kiện khác dòng là phép hoặc (or).
Chú ý: đối với các biểu thức dạng hằng:
Hằng chuỗi: nhập vào bình thường không phân biệt chữ hoa chữ thường.
Hằng số: chỉ nhập số không được đưa dấu phân cách hay ký hiệu tiền tệ vào.
Hằng ngày: nhập vào đặt trong cặp dấu nháy thăng # #
2. Tạo truy vấn chọn bằng Design view:
III. Truy vấn chọn (Select Query)
Cách tạo biểu thưc:
2. Tạo truy vấn chọn bằng Design view:
III. Truy vấn chọn (Select Query)
Cách 1:
Click vào dòng Field
Nhập biểu thức vào theo cú pháp:
[Tên bảng]![Tên trường]
Cách 2: Sử dụng công cụ do Access cung cấp
Anh, chi có thể theo dõi thông qua ví dụ
Ví dụ:
(Q4)
Mục đích:
6. Phân nhóm dữ liệu: (Total Query)
III. Truy vấn chọn (Select Query)
Phân dữ liệu thành từng nhóm và thực hiện một số phép tính lên từng nhóm mẫu tin như: tính tổng, tính giá trị trung bình.
Ví dụ 2.7: Tạo Query tính tổng số lần đã xuất hàng và tổng số tiền
Ví dụ 2.8: Tạo Query tính tổng số mặt hàng của từng loại hàng. Thông tin hiển thị: LoaiHang, Tổng số mặt hàng.
Ví dụ 2.9: Tạo Query tính tổng số lượng hàng xuất theo từng mặt hàng trong từng ngày. Thông tin hiển thị: Ngay, TenHang, Tổng số lượng.
Ví dụ 2.30: Tạo Query để chọn ra những mặt hàng có tổng số lượng hàng xuất >100.
Ví dụ 2.31: Tạo Query để tính tổng số lượng hàng xuất theo từng mặt hàng, chỉ tính tổng những phát sinh mà mỗi lần xuất có số lượng >100
6. Phân nhóm dữ liệu: (Total Query)
III. Truy vấn chọn (Select Query)
Mục đích:
6. Phân nhóm dữ liệu: (Total Query)
III. Truy vấn chọn (Select Query)
Cách thực hiện:
Phân dữ liệu thành từng nhóm và thực hiện một số phép tính lên từng nhóm mẫu tin như: tính tổng, tính giá trị trung bình.
Từ cửa sổ Design Query, click vào nút ? trên thanh công cụ hoặc vào menu View / Totals
Một số phép tính được cung cấp trong lưới của dòng Total
6. Phân nhóm dữ liệu: (Total Query)
III. Truy vấn chọn (Select Query)
a. Tính tổng của tất cả các mẩu tin:
Ví dụ 2.7: Tạo Query tính tổng số lần đã xuất hàng và tổng số tiền
Chú ý: Trong ô Total của mỗi trường, chon phương pháp tính tổng. Không được phép chọn Group By trên bất cứ trường nào.
6. Phân nhóm dữ liệu: (Total Query)
III. Truy vấn chọn (Select Query)
b. Tính tổng trên từng nhóm mẩu tin:
Qui định trường nào sẽ được tính theo nhóm (Group By) và trường nào sẽ được tính tổng.
Ví dụ 2.8: Tạo Query tính tổng số mặt hàng của từng loại hàng. Thông tin hiển thị: LoaiHang, Tổng số mặt hàng.
c. Tính tổng trên nhiều nhóm mẩu tin:
Muốn tính tổng trên nhiều nhóm mẩu tin ta chọn Group By trên nhiều trường. Thứ tự ưu tiên Group By: từ trái sang phải
Ví dụ 2.9: Tạo Query tính tổng số lượng hàng xuất theo từng mặt hàng trong từng ngày. Thông tin hiển thị: Ngay, TenHang, Tổng số lượng.
6. Phân nhóm dữ liệu: (Total Query)
III. Truy vấn chọn (Select Query)
d. Lập biểu thức chọn cho các trường dùng để nhóm sau khi tính tổng:
Để giới hạn kết quả sau khi tính tổng, ta sẽ lập biểu thức điều kiện trên dòng Criteria.
Ví dụ 2.30: Tạo Query để chọn ra những mặt hàng có tổng số lượng hàng xuất >100.
e. Lập biểu thức chọn giới hạn những mẩu tin trước khi tính tổng:
Thiết lập lựa chọn Where trên dòng Total đối với những trường muốn đặt biểu thưc chọn giới hạn số mẩu tin trước khi tính tổng.
Ví dụ 2.31: Tạo Query để tính tổng số lượng hàng xuất theo từng mặt hàng, chỉ tính tổng những phát sinh mà mỗi lần xuất có số lượng >100
Mục đích:
III. Truy vấn có tham số (Parameter Query)
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.
Cách thực hiện:
Tạo truy vấn mới (Design view)
Đưa các bảng cần thiết vào truy vấn
Drag các trường cần thiết vào vùng lưới
Gõ vào biểu thức có chứa tham số tại ô Criteria.
Lưu ý:
Tên tham số đặt giữa cặp dấu ngoặc vuông [ ]
Tên tham số cũng chính là chuỗi nhắc nhở, có thể chứa khoãng trăng và tối đa 255 ký tự
Ví dụ: Tạo Query để tìm thông tin về một nhân viên, với mã nhân viên là tham số được nhập vào khi thưc hiện truy vấn
III. Truy vấn có tham số (Parameter Query)
Ví dụ 1: Tạo Query để liệt kê tên hàng của một loại hàng nào đó, với tên loại hàng là tham số được nhập từ bàn phím khi thực hiện Query. Thông tin hiên thị: Tên loại hàng, tên hàng, đơn vị tính.
Ví dụ 3: Tạo Query để liệt kê thông tin chi tiết về các phát sinh từ ngày T1 đến ngày T2, với T1, T2 là các tham số được nhập từ bàn phím. Thông tin hiển thị: ngày, loại phát sinh, tên hàng, số lượng, đơn giá, thành tiền
Chú ý:
III. Truy vấn có tham số (Parameter Query)
Tên tham số không trù với tên trường
Có thể tạo truy vấn với nhiều điều kiện chọn (nhiều tham số)
Nội dung tham số nhập vào phảI là hằng
Có thể qui định kiểu dữ liệu cho từng tham số:
chọn menu Query / Parameter
IV. Truy vấn tham chiếu chéo (Crosstab Query)
Truy vấn Crosstab dùng để tổng hợp dữ liệu và trình bày theo dạng bảng
Cấu trúc một Crosstab, được minh hoạ bởi hình sau:
IV. Truy vấn tham chiếu chéo (Crosstab Query)
Trong đó:
Row 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,…
IV. Truy vấn tham chiếu chéo (Crosstab Query)
Cách tạo truy vấn Crosstab:
1. Tạo truy vấn Crosstab bằng Wizard:
Anh, chị có thể theo dõi qua ví dụ sau:
Ví dụ: Tạo Query để thống kê tổng số lượng hàng của từng mặt hàng trong từng ngày (H3.54)
2. Tạo truy vấn Crosstab bằng Design View:
(giáo trình trang 66)
IV. Truy vấn cập nhật (Update Query)
Update query dùng cập nhật dữ liệu cho một số trường nào đó trong CSDL
Update query là loại query hành động, làm thay đổi nội dung dữ liệu trên CSDL.
Cách tạo truy vấn cập nhật: (giáo trình trang 74)
Ví dụ: Tạo Query để tăng lương lên 10% cho tất cả các nhân viên
Ví dụ 1: Tạo Query để tăng lương lên 50.000 cho tất cả các nhân viên nam.