Bài 3: Truy vấn – Query

* Hệ quản trị Cơ sở dữ liệu MS Access KHOA CÔNG NGHỆ THÔNG TIN Bài 3: Truy vấn – Query * Bài 3: Truy vấn – Query 1. Các khái niệm cơ bản 2. Công cụ tạo truy vấn QBE 3. Các thao tác với Query 4. Thiết kế truy vấn * 1. Các khái niệm cơ bản Giới thiệu đối tượng Query Vai trò của CSDL là tổ chức lưu trữ và cung cấp công cụ hỗ trợ quản lý và thao tác dữ liệu (những thao tác thường gặp là tìm kiếm và cập nhật thông tin) Query là một trong những công cụ được sử dụng để quản lý thông tin (Query là công cụ giúp thực hiện những yêu cầu của người dùng) Lựa chọn các Query cần thiết Lựa chọn các mẩu tin, sắp xếp theo thứ tự nào đó. Tham khảo dữ liệu của nhiều Table Thực hiện phép tính toán. Tạo dữ liệu cho các Form, Report, các Query khác Thực hiện những thay đổi trong Table * 1. Các khái niệm cơ bản Đối tượng Query của Microsoft Access Là một đối tượng quan trọng của MS Access Nó được quản lý trong mục Queries của cửa sổ Database Là công cụ cho phép truy vấn và xử lý dữ liệu: Trích - lọc - hiển thị dữ liệu, Tổng hợp - thống kê, Cập nhật (thêm- sửa- xóa) dữ liệu từ các Table. * 1. Các khái niệm cơ bản Các loại truy vấn: Selct Query (Chọn lọc): Sử dụng khá phổ biến để trích dữ liệu từ 1 hay nhiều Table. Ngoài ra còn có thể cho phép tính toán và kết nhóm. Append Query: Query kết nối hai bảng cùng cấu trúc để thêm thông tin từ một bảng vào bảng khác Update Query: Query cập nhật dữ liệu với truy vấn này có thể sửa đổi dữ liệu trên một hay nhiều bảng * 1. Các khái niệm cơ bản Các loại truy vấn: CrossTab Query (Tham khảo chéo): Dùng để tổng hợp dữ liệu theo chủng loại (Tổng số, s.lượng, tb) Loại này hữu hiệu trong việc phân tích dữ liệu. Delete Query: Xoá các bản ghi của một hay nhiều bảng Make Table Query: Truy vấn tạo bảng, nó ghi kết quả thành một bảng mới Paremeter Query (Tham số) Tạo một Query mà khi khởi động cần một hay nhiều tham số để tạo ra kết quả. Tham số được ghi dưới dạng một tên biến * Bài 3: Truy vấn – Query 1. Các khái niệm cơ bản 2. Công cụ tạo truy vấn QBE 3. Các thao tác với Query 4. Thiết kế truy vấn * 2. Công cụ tạo truy vấn QBE Để tạo truy vấn có 2 cách khác nhau: Truyền thống: tự viết nội dung truy vấn thông qua câu lệnh SQL Sử dụng công cụ hỗ trợ truy vấn do Access cung cấp mà không cần biết đến các lệnh SQL -> công cụ QBE QBE (Query By Example) Là một công cụ đồ họa giúp tạo nhanh các truy vấn dữ liệu * 2. Công cụ tạo truy vấn QBE Các thành phần trong màn hình QBE Vùng chứa bảng: nơi chứa bảng dữ liệu nguồn cho truy vấn Vùng lưới QBE: được tổ chức theo dòng và cột. Mỗi cột chứa 1 trường hoặc một biểu thức tính toán. Mỗi dòng sẽ có ý nghĩa khác nhau tùy theo từng loại Query Vùng hiển thị các bảng trong truy vấn Vùng lưới * 2. Công cụ tạo truy vấn QBE Các thành phần trong màn hình QBE * 2. Công cụ tạo truy vấn QBE Các chế độ hiển thị của truy vấn: Cửa sổ thiết kế truy vấn (Design view). Trong chế độ này, người sử dụng có thể tạo, sửa chữa một truy vấn nào đó Cửa sổ hiển thị truy vấn (DataSheet view). Sử dụng chế độ này để xem kết quả Hiển thị câu lệnh SQL tương ứng * Bài 3: Truy vấn – Query 1. Các khái niệm cơ bản 2. Công cụ tạo truy vấn QBE 3. Các thao tác với Query 4. Thiết kế truy vấn * 3. Các thao tác với Query Tạo mới một truy vấn: Để thực hiện một truy vấn đều phải thực hiện qua những bước cơ bản sau B1a: Kích hoạt thẻ Queries, ấn nút New, chọn Design View Dùng Wizard: Ít dùng vì hạn chế trên một số Query * 3. Các thao tác với Query Tạo mới một truy vấn: Để thực hiện một truy vấn đều phải thực hiện qua những bước cơ bản sau B1a: Kích hoạt thẻ Queries, ấn nút New, chọn Design View B1b: hoặc kích đúp vào biểu tượng Creat query in Design View Dùng Wizard: Ít dùng vì hạn chế trên một số Query * 3. Các thao tác với Query Tạo mới một truy vấn: B2: Chọn bảng chứa dữ liệu liên quan lên màn hình thiết kế query từ cửa sổ Show Table * 3. Các thao tác với Query Tạo mới một truy vấn: B2: Chọn bảng chứa dữ liệu liên quan lên màn hình thiết kế query từ cửa sổ Show Table * 3. Các thao tác với Query Tạo mới một truy vấn: B3a: Khai báo các lựa chọn trên màn hình Design View (nhấn đúp lên tên trường để chọn) B3b: hoặc chuyển sang màn hình SQL View, viết câu lệnh SQL * 3. Các thao tác với Query Tạo mới một truy vấn: B4: Thi hành query , xem kết quả bằng cách ấn vào biểu tượng trên thanh công cụ, hoặc kích phải chuột lên Query đang thiết kế rồi chọn B5: Lưu query (Ctrl+ S) * 3. Các thao tác với Query Xóa một trường trong truy vấn: * 3. Các thao tác với Query Tạo các trường tính toán Định nghĩa: Là trường “ ảo” trong một câu hỏi, giá trị của trường này được tính toán dựa trên một hay nhiều trường trong các bảng. Ví dụ: Họ tên nhân viên ~ HoNV + TenNV Điểm trung bình = Tổng điểm / số môn Cú pháp: : * 3. Các thao tác với Query Tạo các trường tính toán với QBE * Bài 3: Truy vấn – Query 1. Các khái niệm cơ bản 2. Công cụ tạo truy vấn QBE 3. Các thao tác với Query 4. Thiết kế truy vấn * 4. Thiết kế truy vấn Thực hiện chọn lọc, rút trích, thống kê dữ liệu từ một hay nhiều table/query Ví dụ Một hệ thống thông tin thực hiện việc quản lý lương cho các cán bộ, CSDL được xây dựng có cấu trúc như sau : * 4. Thiết kế truy vấn Để quản lý được hệ thống phải đặt ra rất nhiều các yêu cầu Đưa ra bảng lương cán bộ với những thông tin sau: canboID, hoten, ngaysinh, tencv, luongchinh, phucapcv, thuclinh. Trong đó: luongchinh = hesoluong * 290000 Thuclinh = luongchinh + phucapcv Sau khi đưa ra bảng lương, thực hiện các công việc chọn lọc trích rút thông tin như: Tìm những cán bộ là trưởng phòng có thực lĩnh ,=,=, Between … and … : trong khoảng. Ví dụ Between 1 and 10: trong khoảng từ 1 đến 10. Các toán tử logic: And, Or, Like, Not Like In(giá trị 1, giá trị 2,….) IS Null: Trường không chứa giá trị IS Not Null: Trường chứa giá trị Các ký tự thay thế: Ký tự “*” : Thay thế một nhóm ký tự bất kỳ. Ký tự “?” : Thay thế 1 ký tự. Lưu ý: nếu điều kiện là kiều ngày thì giá trị ngày phải để trong dấu #: Ví dụ: >= #01/05/1997# * 4. Thiết kế truy vấn Select Query VD2: Tìm ra các nhân viên 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. Có 2 ký tự đại diện: Ký tự * để biểu diễn bất kỳ giá trị nào hoặc thay thế cho 1 xâu Ký tự ? để biểu diễn một ký tự bất kỳ * 4. Thiết kế truy vấn Select Query Một số ví dụ minh họa 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; * 4. Thiết kế truy vấn Select Query VD5: Lọc ra những cán bộ có 2,000,000>= Thuclinh >=1,000,000: Toán tử BETWEEN để lọc ra các giá trị nằm trong một khoảng nào đó.Thường giải quyết các yêu cầu lớn hơn hoặc bằng bao nhiêu và nhỏ hơn hoặc bằng bao nhiêu? Cú pháp toán tử Between như sau: Between And * 4. Thiết kế truy vấn Select Query Toán tử IN cho kết quả đúng khi một trong các giá trị có mặt trong danh sách. Cú pháp: IN (Danh sách giá trị) VD: Tìm danh sách cán bộ có mã cán bộ là 1, 2 hoặc 5 * 4. Thiết kế truy vấn Select Query Các hàm toán học: exp(x) cho ex Log(x)= logarit cơ số e của x Sqr(x):hàm tính căn bậc 2 của x. Các hàm lượng giác: Atn(x):tính ảctan của x. Tan(x):tính Tang của x Sin(x):tính Sin của x Cos(x):tính cos của x * 4. Thiết kế truy vấn Select Query Các hàm dùng cho chuỗi: Len(biểu thức chuỗi): cho số kĩ tự của chuỗi Vd: len(“sinhvien”)=8 Left(s,n): kết quả cho một chuỗi nhỏ trong chuỗi s lấy từ trái sang phải và có n kí tự(n là số nguyên) VD: left(“hoang pho”,5)=hoang Right(s,n): kết quả cho một chuỗi nhỏ trong chuỗi s lấy từ phải sang trái và có n kí tự(n là số nguyên) Mid(s,n,m): kết quả cho một chuỗi nhỏ trong chuỗi s và lấy bắt đầu từ vị trí n và lấy m kí tự . Vd: mid(“happy new year”,7,3)=new * 4. Thiết kế truy vấn Select Query Các hàm xử lý ngày tháng Dữ liệu ngày phải đặt trong dấu #: ví dụ: # 04/12/83 # Hàm Day(ngày): cho giá trị ngày. Hàm Month(ngày): cho giá trị tháng . Hàm Year(ngày): cho giá trị năm . Hàm Weekday(ngày): cho giá trị thứ của ngày. Hàm Date( ): cho giá trị ngày hệ thống. * 4. Thiết kế truy vấn PARAMETER QUERY Truy vấn tham số, chấp nhận giá trị lọc là tham biến. Muốn thiết lập giá trị đặt lọc nào là tham biến bạn làm như sau: [Gõ một lời nhắc trong ngoặc vuông] tại vị trí tham số cần thiết lập. VD6: Lọc ra danh sách các cán bộ phòng ban nào đó Khi thi hành query, một hộp thoại yêu cầu gõ vào tham số cần lọc: * 4. Thiết kế truy vấn TOTAL QUERY: Tổng hợp dữ liệu Xét yêu cầu tổng hợp dữ liệu sau: * 4. Thiết kế truy vấn TOTAL QUERY: Tổng hợp dữ liệu Hướng dẫn cách tạo Total Query: Bướ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: * 4. Thiết kế truy vấn TOTAL QUERY: Tổng hợp dữ liệu Bướ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. * 4. Thiết kế truy vấn TOTAL QUERY: Tổng hợp dữ liệu * 4. Thiết kế truy vấn CROSSTAB QUERY: Truy vấn tham chiếu chéo Tạo Crosstab Quey để tổng kết dữ liệu theo một dạng bảng tính. Query này có thể biểu diễn số lượng lớn dữ kiện theo dạng thức dễ đọc. Ngoài ra có thể là dữ kiện cơ sở cho các báo biểu Một số ví dụ minh họa kết quả truy vấn dạng này : * 4. Thiết kế truy vấn CROSSTAB QUERY: Truy vấn tham chiếu chéo Cấu trúc một Crosstab: 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,.. * 4. Thiết kế truy vấn CROSSTAB QUERY: Truy vấn tham chiếu chéo Các bước tạo một Crosstab Query: Tạo một Query bằng Design View Bật Table Name : MenuView\Table Name Bật Total : Menu View\ Total. Xác định loại Query: Menu Query\CrossstabQuery Chọn trường làm tiêu đề dòng Tại dòng Total: Bắt buộc chọn phép toán Group by cho ít nhất một trường Tại dòng Crosstab: Chọn Row heading * 4. Thiết kế truy vấn CROSSTAB QUERY: Truy vấn tham chiếu chéo Các bước tạo một Crosstab Query: Chọn trường làm tiêu đề cột Tại dòng Total: Bắt buộc chọn phép toán Group by Tại dòng Crosstab: Chọn Column heading Chọn trường tính giá trị Tại dòng Total: Chọn phép toán thích hợp Tại dòng Crosstab: Chọn Value Chỉ được phép chọn 1 field có dòng CrossTab là Value * 4. Thiết kế truy vấn CROSSTAB QUERY: Truy vấn tham chiếu chéo VD: Tạo query đưa ra bảng tổng hợp sau: Các trường liên quan đến truy vấn : Tên phòng ban Tên chức vụ Số lượng nhân viên * 4. Thiết kế truy vấn CROSSTAB QUERY: Truy vấn tham chiếu chéo VD: Tạo query đưa ra 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: * 4. Thiết kế truy vấn CROSSTAB QUERY: Truy vấn tham chiếu chéo VD: Tạo query đưa ra bảng tổng hợp sau: Bước 2: Ra lệnh chọn kiểu Crosstab 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: * 4. Thiết kế truy vấn Make Table Query Select Query và Total Query luôn đưa ra một bảng kết quả, đó là giá trị tức thời, mới nhất được lấy ra từ CSDL tại thời điểm truy vấn. Tại thời điểm khác khi thi hành query đó, rất có thể chúng ta không thu lại được bảng kết quả như tại thời điểm 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 nào đó, trong Access có một cách là đưa 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. * 4. Thiết kế truy vấn Make Table Query Tạo một Make Table Query để lưu trữ kết quả ra một bảng dữ liệu theo 2 bước chính: Bước 1: Tạo một Select/Total Query để đưa ra bảng kết quả tổng hợp các dữ liệu 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. * 4. Thiết kế truy vấn Make Table Query Ví dụ về Make Table Query: Giả sử đã tạo được một Select query đưa ra được bảng kết quả như sau Bài toán đặt ra: Đưa toàn bộ kết quả của query đang hiển thị ra một bảng mới có tên tblbangluong2008 * 4. Thiết kế truy vấn Make Table Query Cá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). * 4. Thiết kế truy vấn Make Table Query Cách làm như sau: Bước 3: thi hành query để nhận kết quả Khi đó 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: 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 ??? 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? * 4. Thiết kế truy vấn Make Table Query Cách làm như sau: Cuối cùng một hộp thoại hỏi một lần cuối xem có đồng ý đưa số bản ghi vào bảng đã chỉ định (bảng vừa tạo ra) 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ả. * 4. Thiết kế truy vấn Delete Query Delete Query là một loại Action Query (query hành động) nghĩa là loại truy vấn có khả năng gây ra những 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 một số điều kiện nào đó. Ví dụ: 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 tblcanbo * 4. Thiết kế truy vấn Delete Query Bước 1: Tạo một Select query như sau: * 4. Thiết kế truy vấn Delete Query Bước 2: đổi query hiện hành thành Delete query bằng cách mở thực đơn Queries | Delete Query * 4. Thiết kế truy vấn Delete 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. Phải cẩn trọng trước khi quyết định lệnh xoá dữ liệu * 4. Thiết kế truy vấn Update query Update query là loại query hành động, nó cũng làm thay đổi nội dung dữ liệu trên CSDL. Dùng để cập nhật một số trường nào đó trong CSDL. Ví dụ: Sử dụng Update Query để thay đổi lương chính cho cán bộ. Trước đây lương chính = [hesoluong] *290000, lương cơ bản thay đổi tăng lên 540000. Sử dụng truy vấn để thực hiện việc thay đổi này. * 4. Thiết kế truy vấn Update query Bước 1: Tạo một query và có chứa bảng tblBangLuong2008 (tblcanbo có chứa thêm trường lương chính ) và chuyển thành Update query bằng cách mở thực đơn Queries | Update query * 4. Thiết kế truy vấn Update query Bướ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 * 540000 cửa sổ thiết kế query sẽ như sau: * 4. Thiết kế truy vấn Update query Để thi hành việc cập nhật dữ liệu, nhấn biểu tượng Run trên thanh công cụ. Khi đó một hộp thoại cảnh báo sẽ xuất hiện: Nhấn Yes để đồng ý cập nhật dữ liệu (dữ liệu sau khi đã cập nhật không thể phục hồi lại được); nhấn No để huỷ bỏ lệnh. Phải cẩn trọng trước khi quyết định lệnh này. * 4. Thiết kế truy vấn Append Query (Truy vấn nối dữ liệu) Tạo truy vấn chọn và đưa bảng dữ liệu vào để nối với bảng khác tham gia truy vấn. Chọn Queries/Append query Trong mục Table name: Chọn bảng cần nối vào và chọn OK. Chọn Current Database: CSDL hiện thời Another Database: nối bảng trong CSDL khác. Trong vùng lưới của truy vấn tại hàng Field: Đưa các trường của bảng gốc vào. Trong hàng Append to: Đưa các trường tương ứng của bảng cần nối và đặt điều kiện nếu cần thiết * Hệ quản trị Cơ sở dữ liệu MS Access CÂU HỎI?