Transcript TRUY VẤN (QUERY) I KHÁI NIỆM TRUY VẤN Truy vấn là một công cụ cho phép đặt câu hỏi với dữ liệu trong.

TRUY VẤN
(QUERY)
I KHÁI NIỆM TRUY VẤN
Truy vấn là một công cụ cho phép đặt câu hỏi với dữ
liệu trong bảng dữ liệu trong CSDL.
Ví dụ:
Cho 2 bảng dữ liệu:
KHOHANG (MAHANG, TENHANG, GIA)
BANHANG(MAHANG, TENKHACH, SOLUONG,
NGAYMUA).
Hãy hiển thị những khách hàng mua hàng trong
tháng 7 bao gồm các thông tin: MAHANG, TENHANG,
GIA, TENKHACH.
1. Các loại truy vấn trong Access
 Select Query : Truy vấn chọn
 Crosstab Query : Truy vấn tham khỏa chéo (Thể hiện
dòng và cột)
 Action Query : Truy vấn hành động gồm
 Truy vấn tạo bảng (make table Query )
 Truy vấn nối (append Query )
 Truy vấn cập nhật ( Update Query )
 Truy vấn xóa dữ liệu ( Delete Query )
SQL Query : Truy vấn được viết bởi ngôn ngữ SQL.
Pass throught Query : Gởi các lệnh đến một CSDL SQL
như Microsoft SQL server.
2. Sự cần thiết của truy vấn
sử dụng công cụ truy vấn thì có thể thực hiện được các yêu cầu sau:
Sự lựa chọn các trường cần thiết.
Lựa chọn những bản ghi.
Sắp xếp thứ tự các bản ghi.
Lấy dữ liệu chứa trên nhiều bảng khác nhau trong CSDL.
Thực hiện các phép tính.
Làm nguồn dữ liệu cho một biểu mẫu (Form), báo cáo
(report) hoặc một truy vấn khác (Query ).
Thay đổi dữ liệu trong bảng.
3. CÁC CHẾ ĐỘ HIỂN THỊ TRUY VẤN
Cửa sổ thiết kế truy vấn (Design view).
Màn hình truy vấn chứa hai phần, phần thứ nhất chứa các
bảng (hoặc truy vấn) tham gia truy vấn, phần thứ hai gọi là
vùng lưới QBE (Query By Example).
Cửa sổ hiển thị truy vấn (DataSheet view).
Sử dụng chế độ này để xem kết quả.
Cửa sổ lệnh SQL (SQL view).
Sử dụng chế độ này để xem mã lệnh của truy vấn đang tạo
4. CÁCH TẠO TRUY VẤN
Từ cửa sổ Database, click vào đối tượng Queries.
Chọn nút New.
Chọn Design View,
chọn OK
chọn các bảng tham gia vào
truy vấn và nhấn nút Add
Sao đó chọn close
Đưa các trường từ các bảng vào tham gia truy vần bằng cách kéo các
trường và thả vào hàng Field trong vùng lưới QBE.
Trong hàng Sort: Sắp
xếp dữ liệu (nếu có)
Trong hàng Criteria
đặt tiêu chuẩn (nếu có)
 Chú ý
•
Mỗi truy vấn có:
•
Tối đa là 32 bảng tham gia.
•
Tối đa là 255 trường.
•
Kích thước tối đa của bảng dữ liệu (do truy 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 cấp.
•
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.
Xem kết quả của truy vấn.
Tại cửa sổ Database chọn tên truy vấn rồi chọn Open, hoặc trong khi thiết kế
truy vấn thực hiện lệnh View/datasheet View
Đổi tiêu đề cột trong truy vấn.
Đổi tên tiêu đề cột trong truy vấn mục đích là làm cho bảng kết xuất
dễ đọc hơn (Trừ khi đã quy định thuộc tính Caption).
•
•
•
Mở truy vấn ở chế độ Design View
Chọn vào bên trái ký tự đầu tiên của tên trường trong vùng lưới QBE
Gõ vào tên mới, theo sau là dấu 2 chấm (:).
Che dấu hay thể hiện các trường trong Dynaset
Tại hàng Show ứng với trường cần che dấu chúng ta không chọn mặt dù
nó vẫn tồn tại, vẫn tham gia truy vấn.
Mối quan hệ giữa thuộc tính của trường trong truy vấn và trong bảng
các trường trong truy vấn kế thừa tất cả các thuộc tính của trường
trong bảng làm nguồn dữ liệu.
Nếu không quy định lại trong truy vấn, các trường trong Dynaset hoặc
snapshot luôn kế thừa các thuộc tính của bảng làm nguồn dữ liệu.
Nếu thay đổi thiết kế trong bảng làm nguồn dữ liệu và thay đổi thuộc
tính của các trường thì thuộc tính này cũng được thay đổi trong truy
vấn.
Tuy nhiên, nếu quy định lại các thuộc tính cho các trường trong truy vấn
thì các thuộc tính của các trường trong bảng làm nguồn dữ liệu không
thay đổi.
II. THIẾT KẾ TRUY VẤN CHỌN (Select Query)
1. Định nghĩa truy vấn chọn
là loại truy vấn được chọn lựa, rút trích dữ liệu từ các bảng dữ liệu
thỏa mãn một hoặc nhiều điều kiện nào đó.
2. Lập phép chọn trong truy vấn
Chọn một nhóm các mẩu tin thỏa mãn một điều kiện nào đó
Phép toán
Ví dụ
Ý nghĩa
<
>
>=
<>
=
<#20/10/99#
>#10/10/98#
>= #05/05/90#
<>#01/01/99#
= #10/10/97#
Trước ngày 20/10/99
Sau ngày 10/10/98
Sau và trong ngày 05/05/90
Khác ngày 01/01/99
Trong ngày 10/10/97
Between .... and
...
Between #1/2/97# and #1/7/97#
Từ ngày 1/2/97 đến 1/7/97
Ví dụ:
Cho 2 bảng dữ liệu
1. Dslop(Malop, Tenlop, Nganh_hoc, khoahoc)
2. Dssv(Masv, malop, hotensv, ngaysinh, quequan, giotinh, hocbong)
Tạo một truy vấn để hiển thị danh sách những sinh viên có ngaysinh
trong khoảng thời gian từ 05/05/75 đến 05/05/79 bao gồm các trường:
Tenlop, Hotensv, Ngaysinh, nganh_hoc.
Trong hàng Criteria của trường Ngaysinh:
Between #05/05/75# and #05/05/79#
 Ký tự thay thế
Ký tự *
: Thay thế một nhóm ký tự bất kỳ.
Ký tự ?
: Thay thế 1 ký tự.
Ký tự [ ]
: Thay thế các ký tự trong ngoặc vuông.
Ký tự !
: Phủ định.
Ký tự -
: Từ ký tự đến ký tự.
Ví dụ
Cho 2 bảng dữ liệu:
1.
Dslop(Malop, Tenlop, Nganh_hoc, khoahoc)
2.
Dssv(Masv, malop, hotensv, ngaysinh, quequan, giotinh, hocbong)
Tạo một truy vấn để hiển thị danh sách những sinh viên có Tenlop bắt
đầu là "T" bao gồm các trường: Tenlop, Hotensv, Ngaysinh, nganh_hoc.
Trong hàng Criteria của trường Tenlop: Like "T*"
Chọn các bản ghi không phù hợp với một giá trị nào đó
Dùng toán tử Not
Ví dụ: Tạo một truy vấn để hiển thị danh sách những sinh viên có Tenlop
không bắt đầu là "T" bao gồm các trường: Tenlop, Hotensv, Ngaysinh,
nganh_hoc.
Trong hàng Criteria của trường Tenlop: NOT Like "T*"
Định nhiều tiêu chuẩn trong lựa chọn
Dùng phép “Và” và phép “Hoặc” trong một trường
Muốn quy định nhiều tiêu chuẩn trong cùng một trường, chúng ta phải
sử dụng toán tử AND (và ) cùng toán tử OR (hoặc).
Ví dụ: Tạo một truy vấn để hiển thị danh sách những sinh viên:
có Họ là "Nguyễn" và Tên "Thuỷ" bao gồm các trường: Tenlop,
Hotensv, Ngaysinh.
Tạo truy vấn chọn và đưa 2 bảng dslop và dssv vào tham gia truy vấn
Trong hàng Criteria của trường Hotensv : Like "Nguyễn *" and "* Thuỷ"
Chọn các bản ghi có chứa có giá trị
Phép toán
Ý nghĩa
IS NULL
Trường không chứa giá trị
IS NOT NULL
Trường có chứa giá trị
Ví dụ: Tạo một truy vấn để hiển thị danh sách những sinh viên không có học
bổng bao gồm các trường: Tenlop, Hotensv, hocbong
Trong hàng Criteria của trường hocbong: IS NULL
Chọn các bản ghi thuộc danh sách các giá trị nào đó
Chúng ta có thể sử dụng phép toán IN (Danh sách giá trị)
Ví dụ: Tạo một truy vấn để hiển thị danh sách những sinh viên: thuộc lớp "Tin
K23" hoặc "Tin K24" hoặc "Tin K25" bao gồm các trường: Tenlop, Hotensv,
hocbong.
Trong hàng Criteria của trường Tenlop: IN(“Tin k23”, “Tin k24”, “Tin K25”)
Tạo trường kiểu biểu thức
Ví dụ
Cho 2 bảng dữ liệu:
1. Dssv( Masv, hotensv, ngaysinh, quequan, gioitinh)
2. Dsdiem( Masv, mamon, diem_lan1, diem_lan2)
Tạo truy vấn để hiển thị các thông tin: Hotensv, mamon,
diem_lan1, diem_lan2, dtb, trong đó dtb=(diem_lan1+
diem_lan2*2)/3.
3. Phân nhóm dữ liệu( truy vấn nhóm)
Mục đích: phân dữ liệu thành từng nhóm để thực hiện một số
phép toán trên từng nhóm mẩu tin đó, như: tính tổng, tính giá trị
trung bình…
Cách thực hiện:
Chọn
Xuất hiện
Một số hàm thường được sử dụng trong truy vấn nhóm:
Phép toán
Ý nghĩa
Group by
Sum
Avg
Min
Max
Count
First
Last
Where
Expression
Phân nhóm dữ liệu
Tính tổng các giá trị của một trường
Tính giá trị trung bình của một trường
Tính giá trị nhỏ nhất của một trường
Tính giá trị lớn nhất của một trường
Đếm số giá trị khác rỗng có trong một trường
Giá trị của trường ở bản ghi đầu tiên trong bảng
Giá trị của trường ở bản ghi cuối cùng trong bảng
Giới hạn điều kiện khi tính tổng
Trường kiểu biếu thức
Tính tổng của tất cả các mẩu tin:
Dùng để đếm tổng số mẩu tin của một bảng dữ liệu
Ví dụ: tính tổng số môn học và tổng số tín chỉ của bảng
MONHOC trong CSDL SINHVIEN
1.
2.
3.
4.
5.
6.
Tạo truy vấn mới
Đưa bảng monhoc vào truy vấn
Chọn trường MSMH, SOTC vào lưới
Click nút  trên thanh công cụ
Trong ô total của MSMH chọn count
Trong ô total của SOTC chọn sum
Tính tổng trên một hoặc nhiều nhóm mẩu tin:
Chọn group by trên một hoặc nhiều trường, các trường còn lại
chọn sum hoặc count(tùy theo yêu cầu)
Ví dụ: tính tổng số sinh viên của từng lớp theo từng khoa trong CSDL SINHVIEN
1.
2.
3.
4.
5.
Tạo truy vấn mới
Đưa bảng các bảng vào truy vấn
Chọn trường vào lưới
Chọn Group by trên trường TENKHOA, TENLOP
Chọn count trên trường MSSV
Đặt điều kiện cho trường sau khi tính tổng / giới hạn trước khi tính tổng:
 Lập biểu thức điều kiện ngay trên dòng Criteria của cột được tính tổng
để giới hạn kết quả khi tính tổng(tính tổng trước, kiểm tra điều kiện sau)
 Để giới hạn những mẩu tin trước khi tính tổng; chọn “where” trên dòng
total và lập điều kiện trên dòng Criteria
Ví dụ: liệt kê các sinh viên theo yêu cầu sau:
1. Đăng ký 3 môn học trở lên
2. Trong niên khóa 2003-2004
•
•
•
•
•
•
•
Thực hiên:
Tạo truy vấn mới
Đưa bảng các bảng vào truy vấn
Chọn trường vào lưới
Chọn Group by trên trường MSSV, HOTEN,
Chọn count trên trường MSMH
Gỏ biểu thức điều kiện tại ô criteria MSMH
Chọn where trên dòng total của NIENKHOA
II. THIẾT KẾ TRUY VẤN CÓ THAM SỐ (PARAMETER QUERY)
Mổi lần thực thi truy vấn, người dùng phải truyền vào tham số
Điều kiện sẽ thay đổi theo tham số đó
Kết quả nhận được là khác nhau, tùy vào giá trị tham số
Cách thực hiện:
1. Tạo truy vấn mới
2. Đưa bảng các bảng vào truy vấn
3. Chọn trường vào lưới
4. Tại dòng field hoặc dòng criteria, nhập vào biểu thức có chứa tham số:
+ Tên tham số giữa 2 dấu “[ ]”, không được trùng với tên trường
+ Có thể tạo nhiều tham số



Ví dụ: Tạo query tìm thông tin của một sinh viên dựa vào MSSV
Quy định kiểu dữ liệu cho tham số:
1. Trên menu database chọn query/parameter
2. Khai báo tên tham số(không trùng với tên trường) và chọn kiểu dữ liệu
Ví dụ: Tạo query tìm thông tin của một sinh viên có năm sinh sau một ngày sinh
được nhập vào
Khai báo
tên biến &
kiểu dữ liệu
Nhập điều kiện vào dòng criteria
của cột ngày sinh, biến “ngay sinh”
đã được khai báo đặt trong dấu [].
Chọn
Kết quả thực hiện:
Nhập vào ngày
“20/12/1980”
Nếu nhập vào không đúng kiểu
dữ liệu thì sẽ nhận được thông
báo lổi
II. TRUY VẤN THAM CHIẾU CHÉO (CROSSTAB QUERY)
<> Dùng để tổng hợp dữ liệu có tính chất thống kê.
<> Chuyển các dòng dữ liệu trong bảng thành các cột.
Ví dụ: Thống kê điểm của sinh viên theo từng môn học mà sinh viên đó đăng ký
Hàng
Cột
1.Cấu trúc của tham chiếu chéo(crosstab):
Row heading(tiêu đề dòng): chứa
giá trị của trường nào đó cần
thống kê,phải có tổi thiểu 1
trường.
Column heading(tiêu đề cột): chứa
giá trị của trường nào đó cần thống
kê, chỉ có duy nhất 1 trường làm tiêu
đề cột
Value: vùng dữ liệu tổng hợp, chỉ có duy nhất 1 trường làm
value, trên đó có thể thực hiện được các phép toán: tính tổng,
trung bình, max, min…
2.Tạo tham chiếu chéo(crosstab) bằng wizard:
Từ cửa sổ queries,
chọn new,
xuất hiện hộp thoại
Chọn crosstab query
wizard
Chọn trường
làm tiêu đề
dòng
Chọn bảng dữ
liệu làm dữ liệu
nguồn cho truy
vấn
Tiếp theo:
Các phép toán
Chọn trường
làm tiêu đề
cột
Chọn trị số
muốn tính toán
Thêm cột tính
tổng các trị số
Đặt tên cho
query và kết
thúc
3.Tạo tham chiếu chéo(crosstab) bằng Design View:
Tạo truy vấn mới
Nhấp vào biểu tượng query type
và chọn crosstab query
Khi đó dưới lưới query xuất hiện
dòng Crosstab khi đó tiến hành
chọn cột nào cần làm tiêu đề cột,
dòng theo yêu cầu và thao tác
bình thường như với query
select(truy vấn chọn)
II. TRUY VẤN HÀNH ĐỘNG
Là loại truy vấn có thể làm thay đổi dữ liệu từ các bảng làm nguồn dữ liệu cho nó
Các loại truy vấn hành động:
1. Truy vấn tạo bảng:
•Tạo 1 bảng mới có cấu trúc hoặc cả dữ liệu lấy từ 1 hoặc nhiều bảng dữ liệu nguồn
•Dữ liệu trong bảng mới nầy là kết quả của truy vấn tại thời điểm thi hành câu truy vấn
Cách thực hiện
Tạo truy vấn mới
Đưa các bảng cần thiết vào
Chọn Make-table query
Chọn các trường đưa vào vùng lưới, nhập các điều kiện(nếu có)
Đặt tên cho
bảng sẽ được
tạo ra
2. Truy vấn xóa(delete query):
•Cho phép xóa một hoặc nhiều mẫu tin trong các bảng thỏa một điều kiện do ta qui định
•Dữ liệu bị xóa không thể phục hồi
Ví dụ: Tạo query xóa thông tin của sinh viên với mã số sinh viên được nhập vào.
Tạo truy vấn mới
Đưa các bảng cần thiết vào
Chọn Delete query
Trên dòng delete chọn:
+From: Chỉ ra cột bị xóa từ bảng nào
+where:Chỉ ra điều kiện xóa.
Chọn trường có dấu “ * ” trong bảng sinh viên
2. Truy vấn cập nhật(Update query):
•Cập nhật dữ liệu cho 1 loạt mẫu tin(record) 1 cách nhanh chóng chính xác.
Tạo truy vấn mới
Đưa các bảng cần thiết vào và các
trường liên quan vào lưới query
Chọn Update query
Trên dòng update to của mổi trường
nhập vào giá trị mới hay biểu thức
tính toán cho trường đó và đặt các
điều kiện cần thiết vào dòng criteria
Ví dụ: Tạo Query để tăng số tín chỉ của các môn tin
học dưới 3 tín chỉ lên 1 tín chỉ