Bài giảng Tin học quản lý - Chương 7: Tạo Query

Nội dung text: Bài giảng Tin học quản lý - Chương 7: Tạo Query

1. Nội dung 1. Giớithiệu Chương 7: 2. Các loạivấntin 3. Vấntin tựđộng TẠO QUERY 4. Thiếtkế vấntin Khoa Tin Học Quản Lý Tin Học Quản Lý 1 Khoa Tin Học Quản Lý Tin Học Quản Lý 2 Giớithiệu Ví dụ về query • Query (vấn tin) dùng để trích lọcmộtphầndữ liệu – hãy tìm các sách phân loại là tin học theo một điềukiện – Hãy in ra danh sách các sinh viên cĩ điểm trung bình tích luỹ <4.0 •Tạoramộtbảng dữ liệumới ứng với điềukiện – Hãy tìm ra nhân viên nào cĩ ngày sinh trong tháng này •Chỉnh sửa (thêm, xố, sửa)dữ liệuthoả một điều – Hàng tháng tính lương cho nhân viên kiện – Xố hàng hố mà khơng cịn sản xuất nữa • Phân tích thống kê tổng hợpdữ liệu –Nhập dữ liệu từ 1 bảng tạm vào bảng chính – Thơng kế trong cơng ty cĩ bao nhiêu người nam, nữ Khoa Tin Học Quản Lý Tin Học Quản Lý 3 Khoa Tin Học Quản Lý Tin Học Quản Lý 4 1.1 Màn hình vấn tin Menu Query & Các biểutượng VẤN TIN Run : thực hiện vấn tin Show table : thêm Các loại vấn tin CÁC VẤN TIN ĐANG CĨ bảng vào vấn tin TRONG CSDL Select, Crosstab, Make-Table, Update, Append, Delete Khoa Tin Học Quản Lý Tin Học Quản Lý 5 Khoa Tin Học Quản Lý Tin Học Quản Lý 6 1
2. Các loại vấn tin Tạo query tự động • Select • Create query by using wizard Tìm kiếm, hiển thị, điều chỉnh dữ liệu từ 1 hoặc nhiều bảng „Chọn table cần tạo query và các cột cần lấy dữ liệu • Crosstab Sử dụng để phân tích thống kê dữ liệu từ bảng • Make Table Vấn tin tạo ra bảng mới • Update Vấn tin cho phép tự động điều chỉnh nội dung bảng. • Append Vấn tin thực hiện thêm mẫu tin mới vào bảng. • Delete Vấn tin xĩa mẫu tin của bảng Khoa Tin Học Quản Lý Tin Học Quản Lý 7 Khoa Tin Học Quản Lý Tin Học Quản Lý 8 Chọn kiểu thể hiện Đặt tên cho query Khoa Tin Học Quản Lý Tin Học Quản Lý 9 Khoa Tin Học Quản Lý Tin Học Quản Lý 10 Lưới QBE Lưới QBE •Khi chọn tạo vấn tin bằng Design View người sử dụng sẽ mơ tả vấn tin trên một cơng cụ gọi là Vấn tin Vấn tin lưới QBE. SELECT CROSSTAB •Lưới QBE là khác nhau theo loại vấn tin đang thiết kế (Select, Crosstab, Make Table v/v) UPDATE Khoa Tin Học Quản Lý Tin Học Quản Lý 11 Khoa Tin Học Quản Lý Tin Học Quản Lý 12 2
3. 2. VẤN TIN SELECT 2.1 Vấn tin đơn giản • Field •Chỉ sử dụng 1 bảng Tên vùng •Hiện thơng tin một số vùng của bảng • Table Các bảng sử dụng Tên bảng trong vấn tin • Sort Ví dụ Thứ tự Hiện danh sách nhân viên • Show Hiện gồm họ, tên, ngày sinh. /Khơng hiện Thứ tự danh sách theo • Criteria ABC của tên Điều liện lọc Khoa Tin Học Quản Lý Tin Học Quản Lý 13 Khoa Tin Học Quản Lý Tin Học Quản Lý 14 Lưu vấn tin (Save) Kết quả thực hiện vấn tin LƯỚI QBE Nhập tên vấn tin - Bấm OK Khoa Tin Học Quản Lý Tin Học Quản Lý 15 Khoa Tin Học Quản Lý Tin Học Quản Lý 16 Vấn tin sử dụng nhiều bảng Tạovấn tin trong Design View •Hiện thơng tin một số vùng của bảng này và một số 1. CHỌN HAI BẢNG vùng thuộc bảng khác NHANVIEN VÀ •Giữa hai bảng cĩ liên kết PHONGBAN Ví dụ 2. BẤM NÚT ADD Hiện danh sách nhân viên gồm họ, tên, ngày sinh, tên phịng ban . Thứ tự danh sách theo ABC của tên nhanvien phongban Khoa Tin Học Quản Lý Tin Học Quản Lý 17 Khoa Tin Học Quản Lý Tin Học Quản Lý 18 3
4. Khoa Tin Học Quản Lý Tin Học Quản Lý 19 Khoa Tin Học Quản Lý Tin Học Quản Lý 20 2.3 Vấn tin cĩ vùng tính tốn •Hiện thơng tin một số vùng của bảng và một số vùng khác được tính trên giá trị các vùng đã cĩ. Ví dụ Hiệnbảng lương gồm họ, tên, hệ số lương, lương, phụ cấp Thứ tự danh sách theo từng phịng Vùng đã cĩ sẵn Khơng cĩ nhưng biết : lương = hệ số lương x 540000 ho, ten, hsluong phụ cấp = lương x phụ cấp chức vụ Khoa Tin Học Quản Lý Tin Học Quản Lý 21 Khoa Tin Học Quản Lý Tin Học Quản Lý 22 Tiếp tục phân tích Tiếptục phân tích Hiện bảng lương gồm Cách viết cơng thức họ, tên, hệ số lương, lương, phụ cấp Tiêu đề cột : cơng thức tính Thứ tự danh sách theo từng phịng lương = hệ số lương x 350000 ho, ten, hsluong thuộc bảng nhanvien Lương : nhanvien ! hsluong * 350000 Lương : [Nhanvien]![Hsluong]*350000 lương = hệ số lương x 350000 phụ cấp = lương x phụ cấp chức vụ (thuộc bảng chucvu) phụ cấp = lương x phụ cấp chức vụ (thuộc bảng chucvu) Vấn tin sẽ cần hai bảng : nhanvien, chucvu Phụ cấp : lương * chucvu ! Hesopc Hai bảng này phải cĩ liên kết với nhau Phụ cấp: [Lương]*[chucvu]![hesopc] Khoa Tin Học Quản Lý Tin Học Quản Lý 23 Khoa Tin Học Quản Lý Tin Học Quản Lý 24 4
5. TIÊU ĐỀ CỘT : CƠNG THỨC NHẬP CƠNG THỨC VÀO ĐÂY RỒI BẤM PHÍM OK BIỂU TƯỢNG EXPRESSION BUILDER ĐỂ TẠO CƠNGTHỨC Khoa Tin Học Quản Lý Tin Học Quản Lý 25 Khoa Tin Học Quản Lý Tin Học Quản Lý 26 Mộtsố nội dung thường gặp trong cơng thức •Chuổi: trong dấu nháy kép chứcvụ :”PP” • Tên vùng [phongban]![diachi] • Ghép nhiều vùng chuổi [nhanvien]![ho] & “ “ & [nhanvien]![ten] • Phép tốn trên nhiều vùng [xuatkho]![soluong] * [xuatkho]![gia] • Phép tốn trên nhiều vùng, hằng [nhanvien]![hsluong] * 350000 •Sử dụng dấu# chokiểu ngày – Between #1/10/2004# and #25/10/2004# LƯU Ý : KHƠNG THỂ THAY ĐỔI NỘI DUNG Ở 2 VÙNG TÍNH TỐN LÀ VÙNG LUONG, PHUCAP Khoa Tin Học Quản Lý Tin Học Quản Lý 27 Khoa Tin Học Quản Lý Tin Học Quản Lý 28 Sử dụng hàm IIF 2.4 Điềukiệnlọcvàsắpthứ tự • IIF(điều kiện, trị thứ 1, trị thứ 2) • Hàng criteria của lưới QBE •nếu điều kiện đúng sẽ nhận trị thứ 1 Tìm người cĩ hệ số lương lớn hơn 4 •nếu điều kiện sai sẽ nhận trị thứ 2 •Cĩ thể sử dụng nhiểu hàm IIF lồng nhau VÍ DỤ Hiện ra chữ NAM hoặc NỮ ở phần giới (dù phái là 0,1) GIỚI : IIF(NHANVIEN!PHAI=0, “NAM”, “NỮ”) Chú ý: nếu trong regional chọn là vietnamese thi phần list separator chọn là chấm phẩy (;) và dấu phẩy (,) trong điều kiện dổi thành dấu chấm phẩy (;) Khoa Tin Học Quản Lý Tin Học Quản Lý 29 Khoa Tin Học Quản Lý Tin Học Quản Lý 30 5
6. Điềukiệnlọc Sắpthứ tự • Thứ tự theo một vùng nào đĩ • Ngang : AND - Dọc : OR Chọn mục Sort ở cột đĩ là Ascending/Descending •Cĩ thể hiện hoặc khơng (mục Show) nội dung vùng đặt điều kiện • Thứ tự theo nhiều vùng Vùng viết trước là khĩa chính, vùng viết sau là khĩa phụ. Khi đĩ •Between giá trị and giá trị sắp thứ tự theo khĩa chính, khi nào giá trị ở cột khĩa chính bằng Xem ví dụ minh họa nhau mới sử dụng khĩa phụ để định thứ tự mẫu tin. VÍ DỤ 1: HIỆN DANH SÁCH BAN GIÁM ĐỐC • Ví dụ VÍ DỤ 2: HIỆN DANH SÁCH CÁC TRƯỞNG, PHĨ PHỊNG Vấn tin hiện danh sách trình bày theo thứ tự tăng dần VÍ DỤ 3: HIỆN DANH SÁCH CÁC NHÂN VIÊN CAO TỪ 1,6 M ĐẾN 1,7M theo ABC của tên. Nếu hai người cùng tên thì xét thêm VÍ DỤ 4: HIỆN BẢNG LƯƠNG NHÂN VIÊN CỦA PHỊNG TỔ CHỨC , PHỊNG thứ tự tăng dần của họ TÀI VỤ, PHỊNG KINH DOANH TIẾP THI Khoa Tin Học Quản Lý Tin Học Quản Lý 31 Khoa Tin Học Quản Lý Tin Học Quản Lý 32 2.5 Vấn tin cĩ tham số • Tham số là biến số, chỉ được cung cấp cụ thể khi thực hiện vấn tin. • Lúc thiết kế vấn tin, ta khơng biết giá trị thực của tham số. • Cách viết tham số : một câu hỏi nội dung vấn tin đặt Cột 1 là HoTen:Rtrim(Ho)& “ “ & Ten trong cặp dấu ngoặc vuơng [ ] Cột 2 là Phái: IIF(phai=0,”Nam”, “Nữ”) •Khi thực hiện vấn tin cĩ tham số, ACCESS sẽ lần lượt Cột 3 là Ngaysinh hỏi giá trị từng tham số để người sử dụng nhập vào. Cột 4 là tình trạng gia đình Sau khi nhập xong các tham số kết quả vấn tin mới xuất Gia dinh:IIF(CoGiaDinh, “Cĩ”, “Khơng”) hiện Khoa Tin Học Quản Lý Tin Học Quản Lý 33 Khoa Tin Học Quản Lý Tin Học Quản Lý 34 2.6 Sử dụng hàm trong cơng thức 2.7 Vấn tin tổng hợp • Hàm DAY(ngày) – Lấyphần ngày trong ngày/tháng/năm1-31 • Hàm MONTH(ngày) TÍNH TỔNG – Lấyphần tháng trong ngày/tháng/năm1-12 NGÀY CƠNG CỦA • Hàm YEAR(ngày) MỖI NHĨM – Lấyphầnnăm trong ngày/tháng/năm 100-9999 TÍNH TRUNG BÌNH NGÀY • Hàm WEEKDAY(ngày) CƠNG CỦA – Cho giá trị thứ trong tuần MỖI NHĨM –1 -chủ nhật đến7-thứ bảy • Hàm DATE() MUỐN TỔNG HỢP THEO NHĨM – Cho ngày hiệntạilấytừđồng hồ của máy tính • Hàm Now() : lấy ngày hiệntại • Dùng dấu# để phân biệt ngày Khoa Tin Học Quản Lý Tin Học Quản Lý 35 Khoa Tin Học Quản Lý Tin Học Quản Lý 36 6
7. 2.7 Vấn tin tổng hợp Group By : khĩa phân nhĩm Kiểu dữ liệu có thể sử dụng Hàm Ý nghĩa • Tạo ra các vấn tin tổng hợp theo được nhĩm – khơng cịn chi tiết từng mẫu Sum Tổng nội dung của một cột Number, Date, Currency tin Avg Trung bình của một cột Number, Date, Currency • Sử dụng dịng Total Text, Number, Date, Min Giá trị nhỏ nhất trong cột Currency • Các giá trị ở mục Total là Text, Number, Date, Max Giá trị lớn nhất trong cột •Group By chỉ cột khĩa để phân Currency Đếm số mẫu tin, Count Tất cả các kiểu nhĩm không đếm nếu ô có trị Null •Các trị khác để chỉ cơng thức First Mẫu tin đầu tiên Tất cả các kiểu tính Last Mẫu tin cuối Tất cả các kiểu Khoa Tin Học Quản Lý Tin Học Quản Lý 37 Khoa Tin Học Quản Lý Tin Học Quản Lý 38 Chỉ in những phịng cĩ số lượng nhân viên lớn hơn 10 THÊM DỊNG TOTAL Khoa Tin Học Quản Lý Tin Học Quản Lý 39 Khoa Tin Học Quản Lý Tin Học Quản Lý 40 2.8 Vấn tin cập nhật (Update) 2.8 Vấn tin cập nhật (Update) Vấn tin cập nhật Field/Table : Tên vùng / thuộc bảng Update To : Giá trị hoặc cơng thức tính cho vùng THỰC HIỆN VIỆC THAY ĐỔI Criteria : Điều kiện điều chỉnh chỉ chỉnh khi NỘI DUNG CỦA MỘT HOẶC điều kiện đúng NHIỀU VÙNG LƯU Ý : VÌ LÀ VẤN TIN CẬP NHẬT, TA CHỈ ĐƯAVÀO LƯỚI NHỮNG VÙNG PHẢI CẬP NHẬT. (Xem ví dụ) Khoa Tin Học Quản Lý Tin Học Quản Lý 41 Khoa Tin Học Quản Lý Tin Học Quản Lý 42 7
8. 2.9 Vấn tin xĩa (Delete) Vấn tin Cross-Tab •Thống kê theo chức vụ số người nam, hay nữ • Thơng kế theo chức vụ về độ tuổi trung bình Field/Table : tên trường và điều kiện Delete : chọn trị Where Criteria : điều kiện để xĩa mẫu tin Khoa Tin Học Quản Lý Tin Học Quản Lý 43 Khoa Tin Học Quản Lý Tin Học Quản Lý 44 2.10 Vấn tin Cross-Tab 2.10 Vấn tin Cross-Tab • Dịng Crosstab : Row Heading/Column Heading/Value •Tạo phân tích thống kê cĩ dạng như sau COLUMN HEADING •Kết hợp với dịng Total : Group By/hàm thống kê Nam Nữ BGĐ 21 ROW CH1 20 25 GIÁ TRỊ HEADING CẦN TÍNH CH2 30 12 PHC 3 6 PKD 7 4 Khoa Tin Học Quản Lý Tin Học Quản Lý 45 Khoa Tin Học Quản Lý Tin Học Quản Lý 46 Vấn tin Cross-Tab Ví dụ •bảng độ tuổi trung bình thống kê theo chức vụ và phái •Thống kê theo 1 cơng thức tính Expression •Nếu cĩ điều kiện thống kê dùng : where • Các hàm thường dùng – Count : đếm – Max/min : phần tử lớn nhất/ nhỏ nhất – Sum : tính tổng giá trị – Agv: tính trung bình Khoa Tin Học Quản Lý Tin Học Quản Lý 47 Khoa Tin Học Quản Lý Tin Học Quản Lý 48 8
9. Vấn tin Cross-Tab Thống kế số người theo trình độ và phái Khoa Tin Học Quản Lý Tin Học Quản Lý 49 Khoa Tin Học Quản Lý Tin Học Quản Lý 50 Vấn tin Cross-Tab 2.11 Vấn tin thêm mẫu tin (Append) Thống kế số người theo trình độ và phái •Lấy mẫu tin của một bảng để đưa vào một bảng khác BẢNG 1 BẢNG 2 aaa aaa aaa aaa aaa bbb bbb bbb bbb bbb ccc ccc ccc ccc ccc ddd ddd ddd ddd ddd aaa aaa aaa aaa aaa bbb bbb bbb bbb bbb ccc ccc ccc ccc ccc ddd ddd ddd ddd ddd Khoa Tin Học Quản Lý Tin Học Quản Lý 51 Khoa Tin Học Quản Lý Tin Học Quản Lý 52 2.11 Vấn tin thêm mẫu tin (Append) 2.11 Vấn tin thêm mẫu tin (Append) •Hạn chế số cột (field) •Cĩ điều kiện (ví dụ :bắt đầu với ký tự a) BẢNG 1 BẢNG 2 BẢNG 1 BẢNG 2 111 aaa aaa aaa xxx aaa aaa aaa aaa aaa aaa aaa 222 bbb bbb bbb yyy bbb bbb 333 acc acc acc zzz acc acc bbb bbb bbb bbb bbb 444 ddd ddd ddd kkk ddd ddd acc acc acc acc acc ddd ddd ddd ddd ddd aaa aaa aaa aaa aaa bbb bbb bbb bbb bbb aaa aaa aaa aaa aaa ccc ccc ccc ccc ccc acc acc acc acc acc ddd ddd ddd ddd ddd Khoa Tin Học Quản Lý Tin Học Quản Lý 53 Khoa Tin Học Quản Lý Tin Học Quản Lý 54 9
10. 2.11 Vấn tin thêm mẫu tin (Append) Các bướcthựchiện • Khi thiết kế vấn tin - chọn bảng nào ? 1. Chọn lệnh tạo vấn tin mới –Chọn bảng 1 – là bảng cung cấp mẫu 2. Đưa bảng-cho vào lưới tin. 3. Chọn loại vấn tin Append, bước này sẽ làm •Nội dung lưới xuất hiện hộp thoại hỏi tên bảng-nhận. –Chứa các vùng dữ liệu gửi sang bảng 2. 4. Trả lời hộp thoại –Cĩ thể đặt điều kiện để chỉ cho bảng 2 những mẫu tin thỏa điều kiện. Khoa Tin Học Quản Lý Tin Học Quản Lý 55 Khoa Tin Học Quản Lý Tin Học Quản Lý 56 Table Name : tên bảng-nhận ) Phongban2 : bảng cho (đơn vị mới) Phongban : bảng - nhận (đơn vị cũ) Field/Table vùng của phongban2 sẽ được mang sang bảng phongban Current : chọn nếu bảng nhận cùng chung cơ sở dữ liệu với bảng-cho Append To vùng nhận thơng tin Another : chọn nếu bảng nhận khác cơ sở dữ liệu với bảng- ở bảng phongban cho. Phải cho tên cơ sở dữ liệu chứa bảng nhận. Khoa Tin Học Quản Lý Tin Học Quản Lý 57 Khoa Tin Học Quản Lý Tin Học Quản Lý 58 Chú ý !!! • Khi thêm mẫu tin mới phải khơng vi phạm Vấn tin này chỉ cung cấp 3 vùng mã phịng, những quy tắc đã biết : tên phịng, địa chỉ cho bảng phongban – Khĩa chính : khơng được trùng – Khơng vi phạm quy tắc về tính nhất quán dữ liệu – Khơng vi phạm những quy định về dạng dữ liệu, kiểm tra dữ liệu Khoa Tin Học Quản Lý Tin Học Quản Lý 59 Khoa Tin Học Quản Lý Tin Học Quản Lý 60 10
11. Make table query Make table query •Tạo ra bảng mới cĩ dữ liệu được lọc từ CSDL • Cách thực hiện •Bảng mới tạo ra được tồn tài trong CSDL –Tương tự như select query –Khi thực hiện chọn kiểu query là make table query Khoa Tin Học Quản Lý Tin Học Quản Lý 61 Khoa Tin Học Quản Lý Tin Học Quản Lý 62 Cách thực hiện •Nhập tên của bảng mới cần tạo •Các bước cịn lại tương tự như select query Khoa Tin Học Quản Lý Tin Học Quản Lý 63 Khoa Tin Học Quản Lý Tin Học Quản Lý 64 Các loại query •bảng độ tuổi trung bình thống kê theo chức vụ và phái • Tính lương nhân viên • Xố lương nhân viên trong bảng nhân viên Khoa Tin Học Quản Lý Tin Học Quản Lý 65 Khoa Tin Học Quản Lý Tin Học Quản Lý 66 11