Bài Giảng Query Eccess 2003

TRUY VẤN TRONG ACCESS
Gv. Trần Văn Nghi
Tables
Cơ sơ dữ liệu mức Logic: Lược đồ CSDL:
HoSoSV (MaSV,HotenSV,MaLop,Nu,NgaySinh,HoKhau)
MonHoc (MaMH, TenMh, SoTiet)
DiemMH (MaSV,MaMH,Diem)
Table  Fields,P.Key
Tables  Relations
Tables  Records
Tables  Queries
F.Back
QuERIES
Đặc điểm
Cửa sổ thao tác
Phân loại
Các bước chuẩn bị
Tạo truy vấn
20.T
1. Đặc điểm Của Query
BT
1. Đặc điểm Của Query
Cho phép nối kết dữ liệu tại nhiều bảng theo đúng yêu cầu mà ta quan tâm.
Cho phép lọc ra các dữ liệu thỏa mãn đúng các điều kiện mà ta cần xem xét.
Tạo được phép tính trên các Field và phép thống kê trên các Record
Cho phép lọc ra các dữ liệu thật sự có mặt tại thời điểm đang xét
Cho phép thực hiện tự động cập nhật dữ liệu lên bảng đúng như yêu cầu.
BT
2. Các cửa sổ thao tác
3. Phân loại
Truy vấn chọn lựa dữ liệu (Select Query)
Chọn lựa Không điều kiện (chọn lựa cột)
Chọn lựa Có điều kiện (chọn lựa dòng)
Truy vấn thống kê dữ liệu (Total Query)
Thống kê dữ liệu toàn phần
Thống kê dữ liệu theo nhóm
Thống kê Crosstab(thống kê theodòng/cột)
Truy vấn con (Sub Query)
Truy vấn DL đồng thời (Duplicate Query)
Truy vấn DL không có liên kết (Unmatch Q.)
Truy vấn nối kết bảng (Union Query)
Truy vấn hành động (Action Query)
Truy vấn có tham số (Parameter Query)
4. Các bước chuẩn bị
Trước khi tạo truy vấn cần trả lời các câu hỏi:
Dữ liệu cần hiển thị là của: Field nào? Bảng nào?  Để chọn bảng và Add Field khi tạo Query.
Dữ liệu cần hiển thị có là kết quả của phép tính trên các Field không? Nếu có thì phép tính đó là gì? Sử dụng hàm nào?  Để thực hiện thiết kế cột dữ liệu.
Dữ liệu cần hiển thị tại thời điểm đang xét đã có hay chưa? Nếu chưa có thì phải tạo Query để làm nguồn cung cấp dữ liệu chưa biết này.
Dữ liệu cần hiển thị có là kết quả của phép thống kê hay không? Nếu có thì phải sử dụng hàm gì để tạo ra dữ liệu thống kê này. Có thực hiện nhóm dữ liệu không ? Nếu có thì nhóm ở Field nào?
4. Các bước chuẩn bị
Trước khi tạo truy vấn cần trả lời các câu hỏi:
Dữ liệu cần hiển thị là của: Field nào? Bảng nào?  Để chọn bảng và Add Field khi tạo Query.
Dữ liệu cần hiển thị có là kết quả của phép tính trên các Field không? Nếu có thì phép tính đó là gì? Sử dụng hàm nào?  Để thực hiện thiết kế cột dữ liệu.
Dữ liệu cần hiển thị tại thời điểm đang xét đã có hay chưa? Nếu chưa có thì phải tạo Query để làm nguồn cung cấp dữ liệu chưa biết này.
Dữ liệu cần hiển thị có là kết quả của phép thống kê hay không? Nếu có thì phải sử dụng hàm gì để tạo ra dữ liệu thống kê này. Có thực hiện nhóm dữ liệu không ? Nếu có thì nhóm ở Field nào?
Hiển thị các sinh viên có điểm CSDL lớn nhất: QBE
Thông tin cần hiển thị: MaSV, HoTenSV, DienCSDLMax
Hiện tại chưa có điểm lớn nhất của môn CSDL
=>Tìm điểm lớn nhất của môn CSDL : Q1
Q1 Bảng: DiemMH
 Field: MaMH , DiemCSDLMax: Diem
 Điều kiện: MaMH=CSDL, hàm thống kê = Max
 Chỉ định nhóm DL : MaMH = Group By, Diem=Max
Q2  Bảng: HoSoSV, DiemMH, Q1 (liên kết = Diem)
 Field: MaSV, HoTenSV, MaMH, DiemCSDLMax
 Điều kiện: DiemMH.MaMH=CSDL
Hiển thị các sinh viên có điểm CSDL lớn nhất: SubQuery
Thông tin cần hiển thị: MaSV, HoTenSV, DienCSDLMax
Bảng: HoSoSV, DiemMH
Field: MaSV, HoTenSV, DiemCSDLMax: Diem
 Điều kiện:
MaMH=CSDL,
Diem= (điểm max môn CSDL cho bởi Sub Query)
5. tạo truy vấn bằng qbe
Thao tác tạo nguồn cung cấp dữ liệu cho Query:
1. Create query in design view
= NewDesign View
2. Xác định dữ liệu cần hiển thị :
 Chọn bảng /Query => Add bảng Close
3. Kiểm tra liên kết giữa các bảng:
 Nếu thiếu liên kết => thêm bảng
 Tạo liên kết bổ sung hoặc chuyển đổi liên
kết sang Left Join nếu cần

Ex: Hiển thị danh sách sinh viên của các khoa
5. tạo truy vấn bằng qbe
Ex: Hiển thị danh sách sinh viên của các khoa
5. tạo truy vấn bằng qbe
Ex: Hiển thị danh sách sinh viên của các khoa
5.1 qbe – select Q. chọn cột
1. Tạo nguồn cung cấp dữ liệu cho Query.
2. Tạo cột dữ liệu có tên trùng với tên Field:
 Double Click tên Field trên bảng
3. Tạo tên mới cho cột:
 Click Field name  Tên mới: tên Field
4. Tạo dữ liệu là kết quả của phép tính Field:
 Tên mới: (biểu thức tính trên các Field)
5. Chỉ định không hiển thị dữ liêu trên cột:
 Show =  (unckeck)
6. Chỉ định xếp thứ tự các Record theo DLcột:
 Sort = Ascending / Descending
Ex: Hiển thị danh sách sinh viên của các khoa
5.1 qbe – select Q. chọn cột
Ex: Hiển thị danh sách sinh viên của các khoa
5.1 QBE – select q chọn dòng
1. Tạo nguồn cung cấp dữ liệu cho Query.
2. Tạo cột hiển thị DL theo yêu cầu của Query.
3. Thiết kế điều kiện đặt tại dòng Criteria hoặc Or của cột tương ứng.
 Các điều kiện đặt cùng dòng cho KQ = AND
 Các điều kiện đặt khác dòng cho KQ = OR
 Điều kiện = giá trị
= (toán tử + biểu thức)
= (toán tử + hàm chức năng(tên cột))
= (toán tử + Sub Query)
Trị chuỗi ký tự trong đk cần đặt trong “…”
Trị ngày trong đk cần đặt trong #../../….#

Ex: Hiển thị điểm CSDL và ACCB của SV tin học
5.1 QBE – select q chọn dòng
Ex: Hiển thị điểm CSDL và ACCB của SV tin học
5.1 Hàm & toán tử điều kiện
Các hàm thao tác trên DL kiểu TEXT: s = Field char
UCASE(s), LCASE(s), LEN(s), LEFT(s,n), RIGHT(s,n), MID(s,n,x)  Hàm IIF(đk, kq nếu đk đúng, kq nếu đk sai)
Toán tử so sánh kiểu CHAR: LIKE (so khớp), & (nối chuỗi)
LIKE “?x”, LIKE “*x”, LIKE “x*”, LIKE “*x*”
(với SQL chuẩn ký tự đại diện: ? = _ ,* = % , & =|| )
Các hàm thao tác trên kiểu NUMBER: n = Field number
ROUND(n,vị trí làm tròn), MOD(n,số chia), INT(n)
Các hàm thao tác trên DL kiểu DATE: d = Field date
MONTH(d), YEAR(d), DAY(d) , NOW(),
DATEPART(“d”, d), DATEPART(“m”, d), DATEPART(“q”, d) , DATEPART(“yyyy”, d). Trị ngày đặt trong #mm/dd/yy# (với SQL chuẩn trị ngày đặt trong {mm/dd/yy} )
Toán tử so sánh số và ngày: <, <=, =, >=, >, <>
BETWEEN min AND max, NOT BETWEEN min AND max
IN (giá trị, …), NOT IN (giá trị, …), IS NULL, IS NOT NULL
5.1 QBE – hàm & tt điều kiện
5.2 QBE – thống kê toàn phần
1. Tạo nguồn cung cấp dữ liệu cho Query.
2. Tạo cột hiển thị DL theo yêu cầu của Query.
3. Thiết kế chỉ định thống kê:
 Thực hiện: View Totals =
 Chỉ định hàm thống kê thích ứng cho cột
 Không dùng Group By cho bất kỳ cột nào
4. Thiết kế điều kiện trước thống kê nếu có:
 Tại cột đặt biểu thức điều kiện cần chỉ định
Total = Where

Ex: Hiển thị điểm Max, Min và Avg toàn khóa học
Hiển thị số SV có điểm đạt môn ACCB
5.2 QBE – thống kê toàn phần
Ex: Hiển thị điểm Max, Min và Avg toàn khóa học
Hiển thị số SV có điểm đạt môn ACCB
5.2 QBE – thống kê nhóm DL
1. Tạo nguồn cung cấp dữ liệu cho Query.
2. Tạo cột hiển thị DL theo yêu cầu của Query.
3. Thiết kế chỉ định thống kê:
 Thực hiện View  Total =
 Chỉ định Group By cho cột nhóm dữ liệu
 Chỉ định hàm thống kê cho cột tính trị TK
4. Thiết kế biểu thức điều kiện tại cột tương ứng:
 Cột chứa biểu thức điều kiện trước thống kê
cần chỉ định Total = Where
 Cột chứa điều kiện sau thống kê cần chỉ
định Total = hàm thống kê
Ex: Hiển thị điểm Max, điểm Min của các môn học
Hiển thị môn học có trên 20 SV có điểm đạt
5.2 QBE – thống kê nhóm DL
Ex: Hiển thị điểm Max, điểm Min của các môn học
Hiển thị môn học có trên 20 SV có điểm đạt
5.3 qbe – crosstab query
1. Thực hiện tạo Q.Total theo 2 thuộc tính nhóm:
 Chỉ định 2 cột nhóm dữ liệu:Total=Group By
 Chỉ định cột thống kê: Total = hàm thống kê
2. Thực hiện chỉ định Crosstab:
 Chọn: Query  Crosstab Query
 Chọn Crosstab = Row Heading và Column
Heading cho cột có chỉ định Group By
 Chọn Crosstab = Value cho cột có chỉ định
hàm thốngkê
3. Thiết kế cột chứa điều kiện trước thống kê:
 Chọn Total = Where cho cột này
Ex: Hiển thị số SV đạt theo lớp và theo môn học
Hiển thị số SV theo lớp, khoa và theo năm sinh
5.3 qbe – crosstab query
Ex: Hiển thị số SV đạt theo lớp và theo môn học
Hiển thị số SV theo lớp, khoa và theo năm sinh
5.4 qBE – sub query
Thường dùng để tạo tập trị cho biểu thức điều kiện:
=
(Select From Where <điều kiện>)
Các toán tử lấy giá trị:
IN (SELECT …) , NOT IN (SELECT …)
> ALL (SELECT …) , < ALL (SELECT …)
EXISTS (SELECT …) , NOT EXISTS (SELECT …)
UNIQUE (SELECT …)
EXISTS (Q) = True nếu có ít nhất 1 bộ của Q.
NOT EXISTS (Q) = True nếu Q rỗng.
UNIQUE (Q) = True nếu không có bộ trùng lặp trong Q
5.4 qBE – sub query
5.5 qbe – duplicate query
Truy vấn Duplicate:
Dùng để tìm các dòng trên bảng có cùng 1 trị tại cột a nhưng đồng thời có 2 trị khác nhau tại cột b
ADD bảng 2 lần vào vùng Datasource
Thực hiện tạo nối kết cho cột a giữa 2 bảng
Tạo cột hiển thị dữ liệu gồm:
.,.,.
Tạo điều kiện cho cột b:
(điều kiện cho . ) = m
(điều kiện cho . ) = n

Hiển thị các SV cùng có cả 2 điểm ACCB và CSDL
5.5 qbe – duplicate query
Hiển thị các SV cùng có cả 2 điểm ACCB và CSDL
5.6 qbe – unmatch query
Truy vấn Unmatch:
Dùng để tìm các dòng trên bảng cung cấp quan hệ nhưng không có dòng nối kết tương ứng trên bảng nhận quan hệ
Chọn và ADD bảng cung cấp quan hệ
Tạo cột hiển thị dữ liệu
Tạo điều kiện cho cột cung cấp quan hệ:
NOT IN (Select
From
Where <điều kiện chon dòng cho BQH)


Hiển thị SV chưa có điểm môn nào cả
5.6 qbe – unmatch query
Hiển thị SV chưa có điểm môn nào cả
5.8 qBE – action query
Truy vấn Action:
Dùng để tạo ra sự thay đổi dữ liệu trên bảng. Bao gồm:
Cập nhật Record : UPDATE Query
 Thực hiện Query  Update Query
 Chọn cột cần thay đổi DL  Thiết kế biểu thức cập
nhật dữ liệu trong ô Update To
 Thiết kế cột chứa điều kiện chọn dòng cần đổi dữ liệu
Xóa Record : DELETE Query
 Thực hiện Query  Delete Query
 Thiết kế cột chứa điều kiện chọn dòng cần xóa dữ liệu
Tạo bảng : MAKE TABLE Query
 Thực hiện Query  Make Table Query
 Nhập tên bảng  OK
 Thiết kế cột cho bảng mới và cột chứa đk chọn dòng
Cần nhớ lệnh View = xem kết quả, lện Run tạo kết quả
5.8 qBE – action query
Cần nhớ lệnh View = xem kết quả, lện Run tạo kết quả
5.9 qBE – parameter query
Truy vấn Parameter:
Dùng để tạo truy vấn có điều kiện nhập từ bàn phím khi chạy Query . Biểu thức điều kiện được thiết kế như là:
= [ Nhập trị cần kiểm tra: ]
= + [ Nhập trị so sánh: ]

6. tạo truy vấn bằng sql
Cú pháp cơ bản:
SELECT , AS , …
FROM [], …
WHERE
<điều kiện liên kết> AND <điều kiện chọn dòng>…
[ GROUP BY , … ]
[ HAVING <điều kiện sau thống kê>, … ]
[ ORDER BY ASC/DESC, …] ;
Có thể chỉ định:
SELECT * …;  hiển thị tất cả các cột
SELECT DISTINCT … ; hiển thị các dòng khác nhau
SELECT TOP n … ;  hiển thị n dòng đầu tiên
Biểu thức =
Biểu thức =  / 
Biểu thức = (tên cột)
6.1 SqL – select Q. chọn cột

Ex: Hiển thị danh sách sinh viên của các khoa
SQL: hiển thị danh sach SV tại các khoa
SELECT MaSV , HoTenSV, TenKhoa
FROM HOSOSV H, DMLOP L, DMKHOA K
WHERE H.MaLop = L.MaLop
AND L.MaKhoa= K.MaKhoa
ORDER BY TenKhoa;
6.1 SqL – select Q Chọn dòng

SQL: hiển thị các SV Tin Học sinh trong thập niên 90 tại các Khoa
SELECT MaSV , HoTenSV, TenKhoa
FROM HOSOSV H, DMLOP L, DMKHOA K
WHERE H.MaLop = L.MaLop
AND L.MaKhoa= K.MaKhoa
AND K.TenKhoa=“Tin Học”
AND Year(NgaySinh) between 1990 and 1999
ORDER BY TenKhoa;
6.2 SqL – thống kê toàn phần
SELECT (tên cột) AS ,
(biểu thức) AS …
FROM [ [BD]
WHERE <điều kiện liên kết bảng>
AND <điều kiện trước thống kê> … ;
Điều kiện :
…


SQL: hiển thị tổng số SV có điểm đạt của môn ACCB
SELECT Count(MaSV) as SoSVDatACCB
FROM DIEMMH
WHERE MaMH=“ACCB”
AND Diem>=5;
SQL: hiển thị điểm có hệ số lớn nhất của khóa học
SELECT Max(Diem*HeSo) as DiemHSMax
FROM DMMonHoc M, DIEMMH D
WHERE M.MaMH=D.MaMH ;
6.2 SqL – thống kê nhóm dl
SELECT , …
(tên cột) AS …
FROM [ [BD]
WHERE <điều kiện liên kết bảng>
AND <điều kiện trước thống kê> …
GROUP BY , …
HAVING <điều kiện sau thống kê> …;
Điều kiện :
…
…


6.2 SqL – thống kê nhóm dl

SQL: hiển thị số SV có điểm đạt môn CSTH của mỗi khoa
SELECT TenKhoa, Count(MaSV) as SoSVDatCSTH
FROM HoSoSV H, DIEMMH D, DMLOP L, DMKHOA K
WHERE H.MaSV=D.MaSV
AND H.MaLop=L.MaLop
AND L.MaKhoa=K.MaKhoa
AND MaMH=“CSTH”
AND Diem>=5
GROUP BY TenKhoa ;
6.2 SqL – thống kê nhóm dl

SQL: hiển thị các môn học có số SV có điểm đạt từ 20 sinh viên trở lên
SELECT MaMH, Count(MaSV) as SoSVDat
FROM DIEMMH
WHERE Diem >=5
GROUP BY MaMH
HAVING Count(MaSV)>=20;
6.3 sql – crosstab query
TRANSFORM (tên cột TK) as X
SELECT ,…
FROM [BD1], [BD2]
WHERE =
AND <điều kiện trước thống kê>
GROUP BY
PIVOT ;


Ex: Hiển thị số SV đạt theo lớp và theo môn học
Hiển thị số SV theo lớp, khoa và theo năm sinh
6.3 sql – crosstab query
SQL: hiển thị số SV đạt theo lớp và theo môn học
TRANSFORM Count(H.MaSV) as X
SELECT MaLop
FROM HoSoSV H, DIEMMH D
WHERE H.MaSV=D.MaSV
AND Diem>=5
GROUP BY MaLop
PIVOT MaMH;
6.3 sql – crosstab query
SQL: hiển thị số SV theo lớp, khoa và theo năm sinh
TRANSFORM Count(H.MaSV)
SELECT L.MaLop, K.TenKhoa
FROM HOSOSV H, DMLOP L, DMKHOA K
WHERE H.MaLop=L.MaLop
AND L.MaKhoa=K.MaKhoa
GROUP BY L.MaLop, L.TenKhoa
PIVOT Year(NgaySinh);
6.4 SqL – sub query
Hiển thị điểm các SV có hộ khẩu tại Tp.HCM

Select MaSV, MaMH, Diem
From DIEMMH
Where MaSV IN (Select MaSV From HOSOSV Where HoKhau Like “*HCM”);
6.4 SqL – sub query
NVIEN (CMND,HoTen,NgaySinh, DịaChi, Phai, Luong, MaPB)
THANNHAN(CMNDNV, HoTenTN, DiaChiTN)
PBAN(MaPB, TenPB, CMNDTrP, NgayQDTrP)
Hiển thị họ tên các trưởng phòng có ít nhất 1 thân nhân

Select HoTen From NVIEN N
Where
Exists(Select * From THANNHAN T Where N.CMND=T.CMNDNV)
And Exists(Select * From PBAN P Where N.CMND=P.CMNDTrP );
6.5 sql – duplicate query
Truy vấn Duplicate:
Dùng để tìm các dòng trên bảng có cùng 1 trị tại cột a nhưng đồng thời có 2 trị khác nhau tại cột b
SELECT X., X. as T1,
Y. as T2
FROM X , Y
WHERE X = Y
AND X.=m
AND Y.=n ;
SQL: hiển thị các SV đều có điểm đạt cả 2 môn ACCB và CSDL
SELECT X.MaSV, X.Diem as DiemACCB,
Y.DIEM as DiemCSDL
FROM DIEMMH X, DIEMMH Y
WHERE X.MaSV=Y.MaSV
AND X.MaMH="ACCB"
AND Y.MaMH="CSDL"
AND X.Diem>=5
AND Y.Diem>=5;
6.6 sql – unmatch query
Truy vấn Unmatch:
Dùng để tìm các dòng trên bảng cung cấp quan hệ nhưng không có dòng nối kết tương ứng trên bảng nhận quan hệ
SELECT X.,,
FROM X , Y
WHERE X = Y
AND X.<điều kiện chọn dòng> AND …
AND X. NOT IN
(Select
From
Where <điều kiện>) ;
6.6 sql – unmatch query
SQL: hiển thị các SV khoa Quản Trị chưa có điểm môn ACCB

SELECT H.MaSV, HoTenSV, MaLop
FROM HOSOSV H, DIEMMH D
WHERE H.MaSV=D.MaSV
AND H.MaSV Like “??QT*”
AND H.MaSV NOT IN
(SELECT MaSV FROM DIEMMH WHERE
MaMH=“ACCB”);
6.6 sql – unmatch query
SQL: hiển thị các SV chưa có điểm môn nào cả

SELECT MaSV, HoTenSV, MaLop
FROM HOSOSV
WHERE MaSV NOT IN
(SELECT MaSV FROM DIEMMH);
6.7 sql – union query
Truy vấn Union:
Dùng để tập họp dữ liệu của 2 bảng không có kết nối cùng hiển thị trên 1 Datasheet nhưng đảm bảo dữ liệu không bị lặp.
SELECT X. , ”” as ,…
FROM
UNION
SELECT “” , Y.,…
FROM ;
6.7 sql – union query
HOSOKH(MaKH, HoTenKH, DiaChi)
HOADON(SoHD, NgayHD, SoTienHD, MaKH)
PHIEUTHU(SoPT, NgayPT, SoTienPT, MaKH)
SQL: hiển thị các hóa đơn và phiểu thu của các khách hàng
SELECT SoHD, “” as SoPT, NgayHD as Ngay,
SoTienHD as SoTien, MaKH FROM HOADON
UNION
SELECT “” , SoPT, NgayPT,
SoTienPT, MaKH FROM PHIEUTHU;
6.8 sql – action query
Truy vấn Action:
Dùng để tạo ra sự thay đổi dữ liệu trên bảng. Bao gồm:
Cập nhật Record : UPDATE
SET =, = , …
[WHERE <điều kiện> … ];
Update DIEMMH Set Diem=Diem+1
Where MaMH=“ACCB” And MaSV Like “??TH*”;
Xóa Record : DELETE FROM
[WHERE <điều kiện> … ];
Delete From DIEMMH
Where MaMH=“ACCB” And MaSV Like “??TH*”;
Tạo bảng :
CREATE TABLE (, ,…)
AS SELECT … FROM … WHERE… ;
Create Table DIEMACCB MaSV, DiemACCB As
Select MaSV,Diem From DIEMMH Where MaMH=“ACCB”
6.8 sql – action query
Thêm dữ liệu vào cuối bảng :
INSER INTO
( , , … )
SELECT , ….
FROM ,….
WHERE <điều kiện chọn dữ liệu> AND … ;
Inser Into SVTPHCM MaSV, HoTenSV, Nu, HoKhau
Select MaSV, HoTenSV, Nu, HoKhau
From HoSoSV Where HoKhau Like “*HCM”;
6.9 sql – parameter query
Truy vấn Parameter:
Dùng để tạo truy vấn có điều kiện nhập từ bàn phím khi chạy Query . Biểu thức điều kiện được thiết kế như là:
= [ Nhập trị cần kiểm tra: ]
= + [ Nhập trị so sánh: ]

Hiển thị Họ tên SV của môn học X có điểm >=n với X và n nhập từ bàn phím khi chạy Query
SELECT H.MaSV, HoTenSV, Diem
FROM HoSoSV H, DiemMH D
WHERE H.MaSV=D.MaSV
AND MaMH=[Nhập mã môn học cần xem: ]
AND Diem>=[Nhập diểm nhỏ nhất cần xem: ];