TIN HỌC ỨNG DỤNG TRONG KINH DOANH 2
Chương 3 TRUY VẤN DỮ LIỆU - QUERY
Giảng Viên: ThS. Nguyễn Minh Thành Email: thanhnm@itc.edu.vn Tel : 0908.348.469
NỘI DUNG
1. Giới thiệu 2. Tạo truy vấn 3. Toán tử và hàm trong Access. 4. Tạo vấn tin trên nhiều bảng 5. Truy vấn dùng tham số. 6. Truy vấn dùng phép trừ. 7. Truy vấn gom nhóm. 8. Truy vấn chéo. 9. Truy vấn con. 10. Truy vấn với các hành động.
2
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
1. GIỚI THIỆU
1.1. Tại sao phải dùng truy vấn
3
Trong thực tế việc tham khảo thông tin rất có nhiều dạng khác nhau và thường là khác với dạng dữ liệu nhập vào. Hơn nữa việc khai thác số liệu ở bảng có nhiều khuyết điểm, do đó ta dùng query sẽ thấy tiện lợi hơn nhiều. Do đó, người ta phải sử dụng một công cụ để truy vấn thông tin đó là query. Sử dụng bảng truy vấn trong những việc sau: - Lựa chọn các trường và các mẩu tin cần thiết - Xếp sắp thứ tự mẩu tin theo trường quy định - Tham khảo dữ liệu trên nhiều bảng dữ liệu - Sử dụng bảng vấn tin làm cơ sở cho biểu mẫu hay báo cáo - Thực hiện những thay đổi trong bảng dữ liệu ươ
ữ ệ ấ ng 3. Truy v n d li u – Query 28/08/2013 MaMH: 701006 Ch
1. GIỚI THIỆU
1.2. Các loại truy vấn thông dụng : Có nhiều loại truy vấn, nhưng thông dụng nhất là các loại sau:
1. Select Query (Truy vấn chọn số liệu): được sử dụng phổ biến nhất, dùng để:
Chọn lọc các mẩu tin
Thêm các trường mới là kết quả thực hiện các phép tính trên các trường của bảng nguồn.
Đưa vào các điều kiện tìm kiếm, lựa chọn
2. Update Query (Truy vấn cập nhật): dùng để sửa đổi đồng loạt nhiều mẩu tin của một hay nhiều bảng
4
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
1. GIỚI THIỆU
1.2. Các loại truy vấn thông dụng
3. Delete Query (Truy vấn xoá số liệu): xoá một nhóm các mẩu tin từ một hay nhiều bảng
4. Crosstab Query (Truy vấn tham chiếu chéo): kết nhóm số liệu theo chủng loại và hiển thị số liệu dưới hình thức của một bảng tính kèm theo số liệu tổng hợp ngang và dọc
5. Append Query (Truy vấn thêm): Dùng để thêm (nối) các bảng ghi vào phần cuối của một bảng.
6. Make Table Query (Truy vấn tạo bảng ): cho tạo một bảng mới từ số liệu của Query
5
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
2. TẠO TRUY VẤN
2.1. Cách tạo truy vấn
Thường dùng hai cách tạo truy vấn ứng với hai lựa chọn: in Design view và by using wizard
By using wizard: truy vấn được xây dựng bằng công cụ wizard của Access, ta chỉ việc trả lời các câu hỏi theo yêu cầu
In Design view: truy vấn được thiết kế tuỳ theo mục đích của người sử dụng. Ta thường chọn cách này. Cách thực hiện như sau:
6
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
2. TẠO TRUY VẤN
Bước 1: Trong cửa sổ Database, chọn trang Queries, chọn nút New, hộp thoại New Query xuất hiện
Cửa sổ Database
Nút New
Trang Queries
Chọn Design View, OK, xuất hiện cửa sổ như sau
7
7
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
2. TẠO TRUY VẤN
Bước 2: chọn nguồn dữ liệu cho truy vấn
Cửa sổ Show Table cho chọn các bảng (Tables), các truy vấn (Queries) hoặc chọn cả bảng và truy vấn để làm nguồn dữ liệu cho truy vấn sắp tạo
Chọn nguồn dữ liệu phù hợp, chọn Add. Sau khi chọn xong tất cả, chọn Close để trở về cửa sổ Select Query
8
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
2. TẠO TRUY VẤN
Mô tả cửa sổ Select Query Phần trên: chứa các bảng và truy vấn đã chọn làm nguồn Phần dưới: gọi là lưới thiết kế QBE (Query By Example) dùng chứa các trường của truy vấn mới cần xây dựng
nhấn F6 để di chuyển giữa 2 phần
9
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
9
2. TẠO TRUY VẤN Mô tả cửa sổ Select Query
Field: chứa tên trường hoặc biểu thức làm kết quả truy vấn Table: ghi tên nguồn dữ liệu của trường được chọn tại dòng field
Show: chọn trường có hiển thị hay không
Sort: chọn cách sắp xếp dữ liệu của trường
Criteria: biểu thức tiêu chuẩn chọn lọc dữ liệu
10
ữ ệ ươ ấ
Or: điều kiện hoặc của biểu thức tiêu chuẩn chọn lọc dữ liệu (nếu có) 28/08/2013 MaMH: 701006 Ch
ng 3. Truy v n d li u – Query
2. TẠO TRUY VẤN
Bước 3: Thiết kế truy vấn theo yêu cầu Đưa các trường vào truy vấn: nhấn giữ chuột kéo trường thích hợp thả vào cột của lưới thiết kế, hoặc nhắp đúp tại tên trường
Ghi chú: Cách thêm trường biểu thức: Chọn một ô trống trên hàng Field, gõ tên trường biểu thức, dấu : (hai chấm), biểu thức. Ví dụ: Tongdiem: [Diemtoan]+[Diemly]+[Diemhoa]
Bước 4: Lập tiêu chuẩn lựa chọn (điều kiện tìm kiếm) Trong ô Criteria của trường cần lập biểu thức, gõ biểu thức Ví dụ: gõ vào L1C (tìm khách ở loại phòng L1C)
Lưu truy vấn vừa tạo : Gọi lệnh File / Save
11
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
2. TẠO TRUY VẤN
2.2. Thực hiện truy vấn
Trong cửa sổ thiết kế truy vấn
Gọi lệnh Query / Run (hoặc chọn biểu tượng trên thanh công cụ)
Hoặc chọn biểu tượng View
Hoặc chọn Design View
Design View
12
12
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
2. TẠO TRUY VẤN
2.3. Hiệu chỉnh truy vấn Trong cửa sổ Database, chọn tên truy vấn, chọn nút Design để mở cửa sổ thiết kế. Thực hiện hiệu chỉnh theo yêu cầu
nút Design
13
13
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
3. TOÁN TỬ VÀ HÀM TRONG ACCESS.
Toán tử toán học +, -, *, /, luỹ thừa (^) Thương số nguyên (chia lấy phần nguyên): \ Số dư (chia lấy phần dư): Mod Toán tử logic
Not: cho kết quả ngược lại (Not đúng= sai, Not sai= đúng) And (và): cho kết quả đúng khi tất cả điều kiện đúng, nếu một điều kiện nào đó sai thì cho kết quả sai Or (hoặc): cho kết quả đúng khi có ít nh t ấ một điều kiện đúng, nếu tất cả điều kiện sai thì cho kết quả sai
14
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
3. TOÁN TỬ VÀ HÀM TRONG ACCESS.
Toán tử so sánh
<, <=, >, >=, =, <> (không bằng)
Toán tử so sánh đặc biệt
Between ... And ... (Nằm trong khoảng)
• Vd: [slg] Between 150 And 200
Like (gần giống):
• (*) Tổ hợp bất kỳ - “Anh Ban” Like “*B*” • (?) Ký tự bất kỳ - “Access” Like “???e*” • (#) Ký số bất kỳ - 2001 Like “##0#”: True
15
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
3. TOÁN TỬ VÀ HÀM TRONG ACCESS.
16
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
3. TOÁN TỬ VÀ HÀM TRONG ACCESS.
17
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
3. TOÁN TỬ VÀ HÀM TRONG ACCESS.
Toán tử so sánh đặc biệt
Null, Not null: kiểm tra dữ liệu đã có hay chưa
• Vd: Để tìm các thí sinh thiếu Ngày sinh, ta đưa giá trị Null vào ô Criteria của trường [Namsinh]
• Vd: Để tìm các khách hàng đã có địa chỉ, ta đưa giá trị Not null vào ô Criteria của trường [Diachi]
18
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
3. TOÁN TỬ VÀ HÀM TRONG ACCESS.
Toán tử ghép nối
& (nối chuỗi)
• “Đà” & “ Nẵng”: “Đà Nẵng” • 123 & 456: 123456
• 100 & “năm cô đơn”: 100 năm cô đơn
• Date() & “Nhâm Ngọ”: 14/6/2002 Nhâm Ngọ
• “Vĩ tuyến” & 90/2 & “ngày đêm”: Vĩ tuyến 45 ngày đêm
+ (cộng với)
ế
ả
• “Con thuyền” + “không bến”: Con thuyền không bến • 1001 + “đêm” : Type mismatch (k t qu sai)
19
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
3. TOÁN TỬ VÀ HÀM TRONG ACCESS.
Hằng trị (constants)
True/ False/ Null: hằng trị đúng/ sai/ rỗng
Dấu rào (Delimiter)
“...”: Rào giá trị chuỗi
• Vd: “43 Thanh Thuỷ - Đà Nẵng”
[ ... ] : Rào tên biến
• Vd: [SoLuong] * [DonGia]
#../../..#: Rào giá trị ngày
• #23/04/2000#
20
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
VÍ DỤ
Điều kiện cùng thoả mãn/hoặc thoả mãn
Các điều kiện viết cùng trên dòng Criteria là các điều kiện đồng thời thoả mãn
Ví dụ: để tìm các khách ở loại phòng “L1C” và đến trong tháng 6, trên dòng Criteria, ta ghi
Điều kiện ghi trên cùng 1 dòng
Kết quả
21
21
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
VÍ DỤ
Các điều kiện viết trên dòng Criteria và dòng Or là các điều kiện không cần đồng thời thoả mãn
Ví dụ: để tìm các khách ở loại phòng “L1C” hoặc đến trong tháng 6, trên dòng Criteria và dòng Or, ta ghi
Điều kiện ghi trên dòng Criteria và dòng Or
22
Kết quả
22
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
VÍ DỤ
Phép so sánh Is Null và Is Not Null Is Null được dùng để hiển thị những mẩu tin mà trên trường chỉ định không có dữ liệu. Is Not Null thì ngược lại Ví dụ: cho bảng HOCVIEN có nội dung như sau:
Hiện thị những học viên chứa có năm sinh
23
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
VÍ DỤ
3.5. Toán tử In Toán tử IN để xác định giá trị trong một danh sách chỉ định Ví dụ: Hiển thị những học viên ở các lớp KT1, KT2 và KT4
Hoặc
24
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
VÍ DỤ
Phép AND và OR - Mệnh đề OR được viết trên 2 dòng(một hoặc nhiều cột) - Mệnh đề AND được viết trên một dòng(nhiều cột) Ví dụ 1:Hiển thị những mặt hàng có đơn giá >=5000 và số lượng >=100
Ví dụ 2: Hiển thị những mặt hàng có đơn giá >=5000 hoặcsố lượng >=100
25
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
VÍ DỤ
Ví dụ 3:Hiển thị những mặt hàng có đơn giá >=5000 hoặc đơn giá <=1000
Hoặc
26
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
3. TOÁN TỬ VÀ HÀM TRONG ACCESS.
Hàm thao tác trên dữ liệu kiểu Text
• LEFT(chuỗi,n): Trích n ký tự bên trái chuỗi.
• RIGHT(chuỗi,n): Trích n ký tự bên phải chuỗi.
• MID(chuỗi, vị trí, n): Rút trích n ký tự kể từ vị trí chỉ định
• LEN(chuỗi): Trả về giá trị là chiều dài chuỗi
• LCASE(chuỗi): Chuyển thành chuỗi thường
• UCASE(chuỗi): Chuyển thành chuỗi hoa
• TRIM(chuỗi): cắt bỏ khoảng trắng 2 đầu
• LTRIM(chuỗi): Cắt bỏ khoảng trắng bên trái
• RTRIM(chuỗi): cắt bỏ khoảng trắng bên phải.
27
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
3. TOÁN TỬ VÀ HÀM TRONG ACCESS.
Hàm thao tác trên dữ liệu kiểu date • Trong việc quản lý dữ liệu, việc thống kê dữ liệu theo thời gian là một thao tác khá thường xuyên. Các hàm liên quan đến thời gian
• Datepart(“định dạng”, biểu thức ngày): Cho kết qủa theo phần định dạng của biểu thưc ngày Trong đó, ký hiệu phần định dạng mang một trong các giá trị: “y”: ngày trong năm (day of year) “w”: ngày trong tuần (weekday) “d”: ngày trong tháng (Day of month) “m”: tháng trong năm (month) “q”: quí (quarter) “yyyy”: năm (year) “ww”: tuần trong năm (week of year) “h”: giờ “n”: phút “s”:giây
28
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
3. TOÁN TỬ VÀ HÀM TRONG ACCESS.
• Day(dữ liệu trích) = DATEPART(“d”,dữ liệu trích) • Month(Dữ liệu trích) = DATEPART(“m”,dữ liệu trích) • Year(Dữ liệu trích) = DATEPART(“yy”,dữ liệu trích) • Date(): lấy ngày của hệ thống • Now(): lấy ngày giờ của hệ thống • WeekDay(biểu thức ngày): cho kết quả là số thứ tự của biểu
thức ngày. Theo mặc định 1 là chủ nhật, 2 là thứ 2…
• DateDiif(“Định dạng”, biểu thức ngày BĐ, biểu thức ngày KT):
Cho khoảng giữa hai chuỗi ngày theo phần định dạng.
• DateAdd(“định dạng”, n, biểu thức ngày): cộng thêm n đơn vị
theo phần định dạng vào biểu thức ngày.
• Hour(Biểu thức giờ): cho kết quả là giờ của biểu thức giờ • Minute(Biểu thức giờ): cho kết quả là phút của biểu thức giờ • Second(Biểu thức giờ): cho kết quả là giây của biểu thức giờ
29
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
3. TOÁN TỬ VÀ HÀM TRONG ACCESS.
Ví dụ : Hiện thị những học viên sinh trước 1986
Hoặc
30
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
3. TOÁN TỬ VÀ HÀM TRONG ACCESS.
Ví dụ : Lọc những hoá đơn tháng 10
Hoặc
Hoặc
31
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
3. TOÁN TỬ VÀ HÀM TRONG ACCESS.
Những tính toán khác
Hàm IIF(<Ðiều kiện>,
Nếu <Ðiều kiện> nhận giá trị true thì hàm trả về
lại trả về
Ví dụ 1: Giả sử bảng dữ liệu học viên có các trường: MaHV, HoTen,
Toan, Van. Ta muốn tính toán thêm các cột TB và XepLoai
Ô xếp loại có nội dung như sau: XepLoai:IIF(TB>=8,”gioi”,IFF(TB>=6.5,”kha”,IFF(TB>=5,”TB”,”YEU”)))
32
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
3. TOÁN TỬ VÀ HÀM TRONG ACCESS.
Ví dụ : Cho bảng
LUONG(MaNV,HoTen,MaPB,MucLuong,NgayCong)
Tính tiền thưởng theo yêu cầu:
Ngaycong>=20: thưởng 40000
Ngaycong>=25: thưởng 100000
Thuong: IIF(NgayCong>=25,100000,IFF(NgayCong>=20,40000,0))
33
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
3. TOÁN TỬ VÀ HÀM TRONG ACCESS.
3.6. Các thông báo lỗi
“Can’t have aggregate function in WHERE clause”
Không thể chỉ định hàm thống kê trong mệnh đề WHERE (Criteria)
“You can’t set criteria before you add a field or expression to the field row”. Lỗi xuất hiện khi bạn chỉ định một tiêu chuẩn vào cột mà chưa có chỉ định tên trường(hay biểu thức trường) vào dòng field
“Data type mismatch in critetia expression” Sai về kiểu dữ liệu trong biểu thức tiêu chuẩn, chẳng hạn như so sánh một trường số number với tiêu chuẩn là chuỗi
34
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
4.TẠO VẤN TIN TRÊN NHIỀU BẢNG
Thông thường trong các bảng vấn tin thường lấy dữ liệu trên nhiều bảng dữ liệu. Ví dụ: ta muốn xem điểm của một học viên ở lớp nào (Trong chương 2). Thì ít nhất ta phải lấy hai bảng là HOCVIEN và DIEM - Muốn tạo bảng vấn tin lấy dữ liệu trên bảng,ta chỉ định các bảng cần thiết với lưu ý là phải bảo đảm các mối quan hệ giữa chúng.
35
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
4.TẠO VẤN TIN TRÊN NHIỀU BẢNG
4.1. Các loại liên kết - Liên kết chặt (Inner Join hay Equi Join): là kiểu liên kết mà dữ liệu hiển thị trong bảng vấn tin là những mẩu tin có quan hệ với nhau phải tồn tại trên cả hai bảng
- Liên kết không chặt (Outer Join): là kiểu liên kết mà dữ liệu được đưa vào bảng vấn tin là tất cả những mẩu tin của bảng thứ nhất (ngay cả khi nó không có mẩu tin tương ứng ở bảng thứ 2) và những mẩu tin của bảng thứ 2 có tương ứng trong bảng thứ nhất
- Tự liên kết (Self Join): là kiểu liên kết của một bảng dữ liệu với chính nó (phải thêm bảng dữ liệu này 2 lần)
36
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
4.TẠO VẤN TIN TRÊN NHIỀU BẢNG
4.2. Chỉ định mối liên kết giữa 2 bảng - Trong nhiều trường hợp, khi ta thêm các bảng dữ liệu thì sẽ hiển thị các mối quan hệ đã khai báo từ cửa sổ thiết lập quan hệ.
- Ngay cả trong trường hợp, hai bảng chưa thiết lập mối quan hệ với nhau thì Access cũng cố thiết lập mối quan hệ giữa hai trường cùng tên, có kiểu dữ liệu thích hợp.
- Muốn chỉ định loại liên kết (Join Type), ta nhấp cào mối qua hệ thì xuất hiện hộp thoại sau:
37
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
4.TẠO VẤN TIN TRÊN NHIỀU BẢNG
38
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
4.TẠO VẤN TIN TRÊN NHIỀU BẢNG
- Quan hệ chặt: Hiển thị những mẩu tin có giá trị bằng nhautrên cả 2 trường quan hệ của 2 bảng.
- Quan hệ không chặt thứ 1: Hiển thị tất cả các mẩu tin trên bảng HoSoKH cho dù không có mẩu tin tương ứng trên bảng PhieuThu và những mẩu tin của bảng PhieuThu có mẩu tin với bảngHoSoKH
trên bảng HoSoKH và những mẩu
- Quan hệ không chặt thứ 2: Hiển thị tất cả các mẩu tin trên bảng PhieuThu cho dù nó không có mẩu tin tương tin của ứng bảngHoSoKH có mẩu tin với bảngPhieuThu
39
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
4.TẠO VẤN TIN TRÊN NHIỀU BẢNG
4.3. Các ví dụ a. Liên kết chặt Truy xuất những mẩu tin từ một bảng dữ liệu có mẩu tin tương ứng trong bảng thứ 2 mới đưa vào truy vấn. Ví dụ: Cho 2 bảng
40
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
4.TẠO VẤN TIN TRÊN NHIỀU BẢNG
Vấn tin hiển thị thông tin khách hàng gồm các trường: SoPT, Ngay, MaKH, TenKH, SoTien
41
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
4.TẠO VẤN TIN TRÊN NHIỀU BẢNG
b. Ví dụ về liên kết không chặt Ví dụ in ra những khách hàng, kể cả những khách hàng không có phiếu thu. Ta phải sử dụng kiểu liên kết không chặt từ HosoKH ->PhieuThu và chỉ định lưới thiết kế
42
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
4.TẠO VẤN TIN TRÊN NHIỀU BẢNG
Kết quả:
43
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
4.TẠO VẤN TIN TRÊN NHIỀU BẢNG
Lưu ý: Khi 2 bảng không có mối liên kết thì sẽ tạora sự lặp lại dữ liệu. Chẳng hạn bảng thứ 1 có n mẩu tin, bảng thứ 2 có m mẩu tin thì bảng kết quả có m x n mẩu tin.
44
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
4.TẠO VẤN TIN TRÊN NHIỀU BẢNG
Giả sử có truy vấn sau:
Kết quả:
45
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
5.TRUY VẤN DÙNG THAM SỐ
-Là truy vấn mà tiêu chuẩn tính toán được thay đổi cho mỗi lần chạy. - Vấn tin dạng này rất thuận lợi để làm cơ sở cho mẫu biểu hay báo biểu. - Tạo bảng vấn tin có tham số: muốn tạo bảng vấn tin có tham số, ta tạo tương tự như bảng vấn tin bình thường như đã nêu ở trên, chỉ có một sựkhác biệt là ta cho xuất hiện một thông báo chắc người dùng nậhp dữ liệu khi chạy bảng vấn tin. - Muốn vậy ta chỉ định một tham số. - Một tham số (parameter) là một chuỗi các ký tự bao trong ngoặc vuông, không trùng với một trong các trường của bảng dữ liệu đang được sử dụng.
46
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
5.TRUY VẤN DÙNG THAM SỐ
Ví du 1 : Hiển thị phiếu thu của khách hàng chỉ định khi chạy bảng vấn tin
Khi ta chạy bảng vấn tin này thì sẽ yêu cầu nhập giá trị để làm tiêu chuẩn lọc mã khách hàng(giả sử A02):
47
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
5.TRUY VẤN DÙNG THAM SỐ
- Ngoài ra ta có thể dùng LIKE để so sánh mộtvài ký tự đầu giống nhau, chẳng hạn ta có thể viết lại. - Khi đó ta nhập A vào khung Parameter sẽ được hiểu là A* Hoặc ta có thể viết dòng tiêu chuẩn: LIKE [nhap ma KH: ], thì khi đó ta phải nhập A*
Hoặc ta có thể viết dòng tiêu chuẩn: LIKE [nhap ma KH: ], thì khi đó ta phải nhập A*
48
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
5.TRUY VẤN DÙNG THAM SỐ
49
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
5.TRUY VẤN DÙNG THAM SỐ
Lưu ý: Khi gõ một tên trong cặp ngoặc vuông để cung cấp giá trị làm tiêu chuẩn hay tính toán mà không trùng với các trường của các bảng dữ liệu đang dùng thì nó hiểu đó là một tham số.
50
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM
Để tạo vấn tính dạng thống kê, gom nhóm: Từ cửa sổ thiết kế View/Total hay biểu tượng để thêm dòng Total trên khung thiết kế
51
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM
6.1. Các phép thống kê •SUM: Tính tổng một trường số chỉ định •COUNT: Đếm số mẩu tin đã có dữ liệu trên trường số chỉ định •AVG: Tính trung bình một trường số chỉ định •MIN: Tính giá trị nhỏ nhất của trường số chỉ định •MAX: Tính giá trị lớn nhất của trường số chỉ định •FIRST: Trị đầu tiên của trường chỉ định •LAST: Trị cuối cùng của trường chỉ định •STDDEV: Độ lệnh chuẩn của trường số chỉ định
52
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM
6.1. Các phép thống kê •VAR: Phương sai của trường số chỉ định •GROUP BY: Chỉ định trường phân nhóm •WHERE: Chỉ địng trường chứa tiêu đề lựa chọn •Expression: Chỉ định expression trên dòng Total khi biểu thức trên
dòng field của cột đó đã là một biểu thức mang tính thống kê, chẳng
hạn như TongTien:Sum(SoTien), SLHV:count(MaHV)
53
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM
Ví dụ: Cho bảng HOADON
54
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM
Ví dụ: Cho bảng HOADON
55
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM
Trong trường hợp này có thể dùng Group By thay cho where. Tuy nhiên hình thức kết quả có khác nhau. Hình thức where thì không được bậc show, trong khi đó Group by bậc hay không bậc đều được cả. Nếu bậc thì có kết quả sau:
56
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM
6.2. Thống kê theo nhóm Một yêu cầu khác thường được sử dụng là dữ liệu được thống kê theo nhóm dựa trên một (vài) trường chỉ định
57
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM
Chỉ định nhiều
6.3. trường Group By trường hợp muốn Trong phân nhóm theo nhiều trường thì ta chỉ định Group By trên nhiều trường. Khi đó chỉ những mẩu tin nào có giá trị giống nhau trên tất cả các trường đó mới được xem là cùng nhóm
58
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM
Ví dụ 2:Giả sử trong MaLop có 2 ký tự đầu là khóa học, 2 ký tự tiếp theo là ngành học, ký tự thứ 5 là số thứ tự lớp (chẳng hạn 05KT1, 04KT2,…) Cho bảng HOCVIEN sau:
59
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM
60
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM
6.4. Khai báo giới hạn trong tính toán theo nhóm Ở đây ta cần phân biệt tiêu chuẩn tác động đến trước hay sau khi tính tổng cộng theo phân nhóm.
a) Giới hạn trước khi cộng nhóm: - Cách thức này sẽ lọc những thông tin thỏa mãn rồi mới thống kê. Ta chỉ định Where trong ô Total, chỉ định tiêu chuẩn vào ô Criteria của trường này (nhưng không đánh dấu hiển thị cột này)
61
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM
Ví dụ: Giả sử cho bảng ChiTietHD như sau:
62
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM
63
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM
b)Giới hạn sau khi cộng nhóm: - Cách này sẽ thống kê tất cả các mẫu tin trong bảng, và chỉ hiển thị những dòng kết quả thỏa mãn điều kiện chỉ định. - Ở cách này ta chỉ Sum vào ô Total và tiêu chuẩn vào ô Criteria của cùng một trường. Ví dụ: Hiển thị những mặt hàng có tổng số lượng <20
64
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM
65
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM 6.5. Chỉ định Expression - Trong đa số trường hợp, ta chỉ định phép toán thống kê trên dòng Total. - Ở đây ta chỉ định phép toán thống kê trên dòng Field rồi chỉ định expression trên dòng Total
66
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM 6.5. Chỉ định Expression - Trong đa số trường hợp, ta chỉ định phép toán thống kê trên dòng Total. - Ở đây ta chỉ định phép toán thống kê trên dòng Field rồi chỉ định expression trên dòng Total
67
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM
6.6. Những tính toán hỗ trợ khác
Hiển thị thông tin: MaKH, Q1, Q2, Q3, Q4
68
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM
6.7. Hàm kết hợp Cú pháp chung:
Tên hàm(“Tên field”,”tên table/query”[,”điều kiện”})
Công dụng:
DAVG: Tính trung bình DCOUNT: Đếm DFIRST: Cho giá trị tìm thấy đầu tiên DLAST: Cho giá trị tìm thấy cuối cùng DLOOKUP: Trả về giá trị tìm thấy DMAX: Tìm giá trị lớn nhất DMIN: Tìm giá trị nhỏ nhất DSUM: Tính tổng giá trị trên field
69
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
6.TRUY VẤN GOM NHÓM
6.8. Các thông báo lỗi -Nếu ta chỉ định một phép thống kê (SUM, AVG…) mà không bậc dòng Total thì sẽ thông báo lỗi “you tried to execute a query that doesn’t include the specified expression “…” as part of an aggregrate function”
-Khi ta chỉ định Where trên dòng Total thì không được bậc Show của cột đó. Nếu bậc thì Access sẽ báo lỗi “Microsoft Access can’t display the field for which you enter where in the Total row..”
70
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
7.TRUY VẤN CHÉO
7.1. Tạo truy vấn CrossTab bằng Design view + Từ cửa sổ Database, chọn Design View để hiển thị khung thiếtkế như đã đề cập + Gọi Query/ CrossTab để chỉ loại bảng vấn tin
+ Chỉ định Group by vào các trường phânnhóm và chỉ định phép toán vào trường thống kê + Chỉ định Row Heading vào các trường cần làm tiêu đề dòng, Column Heading vào (một) trường làm tiêu đề cột và Value trường thống kê vào khung thiết kế.
71
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
7.TRUY VẤN CHÉO
72
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
7.TRUY VẤN CHÉO
73
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
7.TRUY VẤN CHÉO
Thông báo lỗi: “to create a Crosstab query, you must specify one more Row Heading optio, one Column Heading option and one value option”.
Để tạo mộ CrossTab Query, ta chỉ định một hay nhiều trường làm Row Heading, nhưng chỉ được chỉ định một trường làm Column Heading và một trường là value, nghĩa là không được phép chỉ định nhiều trường làmColumn Heading hay Value.
74
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
7.TRUY VẤN CHÉO
7.2. Tạo bằng cách dùng Wizard - Bước 1: Từ cửa sổ Database, chọn New xuất hiện hộp thoại
75
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
7.TRUY VẤN CHÉO
76
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
7.TRUY VẤN CHÉO
77
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
7.TRUY VẤN CHÉO
78
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
7.TRUY VẤN CHÉO
79
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
7.TRUY VẤN CHÉO
Bước 6:
80
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
7.TRUY VẤN CHÉO
81
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
8. CÁC TRUY VẤN KHÁC
8.1. Truy vấn tự liên kết (Self-join)
- Khi muốn tạo truy vấn thỏa mãn đồng thời 2 giá trị trên 2 mẩu tin của cùng một trường. -Ví dụ tìm hoá đơn vừa chứa mặt hàng “X83” và “X92” (hai mặt hàng nêu ra trên cùng hoá đơn). Ta sử dụng liên kết SelfJoin: Mở bảng dữ liệu 2 (hay nhiều) lần, chỉ định trường chung và chỉ định các giá trị lần lượt vào các trường cùng tên trường nhưng tên các bảng được đặt lần lượt thêm các cố 1, 2, 3…
82
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
8. CÁC TRUY VẤN KHÁC
83
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
8. CÁC TRUY VẤN KHÁC
84
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
8. CÁC TRUY VẤN KHÁC
85
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
8. CÁC TRUY VẤN KHÁC
8.2. Truy vấn không có trong - Find Unmatched Query
86
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
8. CÁC TRUY VẤN KHÁC
8.3. Truy vấn trùng - Find Duplicates Query
87
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
8. CÁC TRUY VẤN KHÁC 8.4. Truy vấn phối hợp
Trong thực tế có thể có những yêu cầu mà không dùng một bảng vấn tin sẽ khá phức tạp và rất khó kiểm soát. Khi đó ta dùng phối hợp những bảng vấn tin. Ví dụ: Cho bảng ChiTietHD,HOSOKH và HOADON như sau
88
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
8. CÁC TRUY VẤN KHÁC
Yêu cầu: Hiển thị những khách hàng không mua mã hàng “X83” Thực hiện qua 2 bước - Bước 1: Tạo bảng vấn tin lọc những khách hàng có mua mã hàng “X83” (đặt tên bảng vấn tin là M83)
89
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
8. CÁC TRUY VẤN KHÁC
90
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
9.TRUY VẤN CON
Ví dụ 1: Cho bảng HOCVIEN(MaHV, HoTen, MNgaySinh, Phai, MaLop) Yêu cầu 1: Hiển thị những học viên gồmcác thông tin: MaHV, Hoten, NgaySinh, MaLop
SELECT MaHV, Hoten, NgaySinh, MaLop FROM HOCVIEN
Yêu cầu 2: Hiển thị những học viên có MaLop là “KT1”, thông tin gồm MaHV, Hoten, NgaySinh, MaLop
SELECTMaHV, Hoten, NgaySinh, MaLop FROMHOCVIEN WHERE MaLop = “KT1”
91
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
9.TRUY VẤN CON
- Bảng vấn tin con được dùng như một tiêu chuẩn lọc dữ liệu (trongmột yêu cầu thực tế). Chẳng hạn như hiển thị những nhân viêncó mức lương lớn hơn mức lương trung bình của các nhân viên trong cơ quan, hay hiển thị những học viên có điểm toán cao nhất của từng lớp … Ví dụ 2:Cho bảng LUONG(MaNV, HoTen, MaBP, ChucVu, MucLuong, NgayCong) Yêu cầu: Hiển thị những nhân viên có lương trung bình cao nhất
92
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
9.TRUY VẤN CON
93
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
10.TRUY VẤN VỚI CÁC HÀNH ĐỘNG Ngoài các bảng vấn tin dùng để xem dữ liệu còn có các bảng vấn tin dùng để cập nhật, thêm, xoá, sửa dữ liệu… được gọi là bảng vấn tin hành động.
94
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
10.TRUY VẤN VỚI CÁC HÀNH ĐỘNG
10.1. Truy vấn cập nhật (Update Query)
Các bước xây dựng truy vấn cập nhật
Chọn nguồn dữ liệu (các bảng hoặc truy vấn khác) Chọn lệnh Query / Update Query, hiện thêm hàng Update to trong lưới QBE Đặt biểu thức vào ô Update To của trường cần sửa Criteria: ghi điều kiện tương ứng
95
95
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
10.TRUY VẤN VỚI CÁC HÀNH ĐỘNG
Ví dụ: trong hình dưới, khi thực hiện Query thì đơn giá mặt hàng Gạo sẽ giảm 1000
96
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
10.TRUY VẤN VỚI CÁC HÀNH ĐỘNG
10.2. Truy vấn xoá (Delete Query)
Chọn nguồn dữ liệu (các bảng hoặc truy vấn khác) Chọn lệnh Query / Delete Query, hiện thêm hàng Delete trong lưới QBE Kéo dấu * của bảng muốn xoá vào dòng Field, từ khoá From xuất hiện trên dòng Delete
Kéo trường dùng để lập điều kiện vào dòng Field, từ khoá Where xuất hiện trên dòng Delete
97
97
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
10.TRUY VẤN VỚI CÁC HÀNH ĐỘNG
Ví dụ: trong hình dưới, khi thực hiện Query thì các mẩu tin có Số hoá đơn là H01/KD sẽ bị xoá
98
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
10.TRUY VẤN VỚI CÁC HÀNH ĐỘNG
10.3 Truy vấn Thêm (Append Query)
Ví dụ: Thêm những mẩu tin có số tiền<500000 từ HoaDon vào PhieuThu - Bước 1: Từ cửa sổ Database, chọn New/ Design View và chỉ định cung cấp dữ liệu qua khung Show Table. Ở đây ta chọn bảng HoaDon - Bước 2: gọi menu Query/Append Query và chỉ định tên bảng dữ liệu vào ô Table Name, rồi OK
99
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
10.TRUY VẤN VỚI CÁC HÀNH ĐỘNG
Bước 3: Trong lưới thiết kế cần chỉ định những trường cần sao chép sang, thông thường ta phải chỉ định tên trường làm khoá chính của bảng nhận dữ liệu. Nếu trường chỉ định trên lưới thiết kế tồn tại trên bảng nhận dữ liệu thì nó tự động đưa vào. Nếu trường đó không có trường tương ứng ta phải chỉ định vào.
100
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
10.TRUY VẤN VỚI CÁC HÀNH ĐỘNG
-Bước 4: Khi ta thực hiện chạy (run) câu vấn tin này sẽ xuất hiện thông báo nhắc nhở
-Nếu ta chọn Yes thì sẽ chấp nhận thêm 4 dòng vào bảng PhieuThu. Tuy nhiên không phảI lúc nào cũng thành công. Nếu không thành công thì sẽ xuất hiện
101
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
10.TRUY VẤN VỚI CÁC HÀNH ĐỘNG
10.4. Make Table Query Ta có thể dùng bảng vấn tin để tạo một bảng dữ liệu, chẳng hạn như để lưu số liệu đã có. Muốn vậy ta thao tác như sau: -Bước 1: Từ cửa sổ Database, chọn New/ Design View và chỉ định cung cấp dữ liệu. - Bước 2: gọi menu Query/ Make Table Query và chỉ định tên bảng dữ liệu vào ô Table Name, rồi OK
- Bước 3: chỉ định các trường vào dòng Field
102
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
10.TRUY VẤN VỚI CÁC HÀNH ĐỘNG
Ví dụ: Giả sử có 2 bảng HocVien(MaHV, HoTen, NgaySinh, Lop) và KetQua(MaHV,MaMH, Diem) Tạo bảng dữ liệu bảng tên “DiemTB” từ 2 bảng dữ liệu đã có Bước 1: Tạo bảng vấn tin có nội dung như sau
103
ươ ữ ệ ấ 28/08/2013 MaMH: 701006 Ch ng 3. Truy v n d li u – Query
10.TRUY VẤN VỚI CÁC HÀNH ĐỘNG
- Bước 2: Gọi menu Query/ Make Table Query rồi gõ tên vào ô Table Name
-Bước 3: Thực hiện câu vấn tin này sẽ tạo một bảng mới có tên DiemTB
104
Lưu ý: Trong Append Querythì bảng nhận dữ liệu là bảng đã có (nếu chưa có thì thông báo lỗi), còn trong Make – Table Query thì bảng nhận dữ liệu là chưa có (nếu có thì nhắc nhở ghi chồng lên hay không). ươ
ữ ệ ấ ng 3. Truy v n d li u – Query 28/08/2013 MaMH: 701006 Ch
